- A+
所属分类:Database
MySQL版本:
0 |
Server version: 5.7.21-20-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.21-29.26, Revision 1702aea, wsrep_29.26 |
MySQL基础命令总结:
0:设置root密码
01 $ /usr/local/mysql/bin/mysqladmin -u root password "admin"> SET PASSWORD FOR root@'localhost' = PASSWORD('admin123');1:修改用户密码
012 $ /usr/local/mysql/bin/mysqladmin -uroot -padmin123 password admin> SET PASSWORD = PASSWORD('admin');> update mysql.user set authentication_string=password('admin') where user='root' and Host = 'localhost'; # > flush privileges;2:删除多余用户
01 > drop user ''@'localhost';> delete from mysql.user where user='' and host='lamp';3:建立数据库
01234 > create database abc;> create database if not exists eof;> create database t4x default character set gbk collate gbk_chinese_ci;> create database t4x default character set utf8 collate utf8_general_ci;> show charset; #相关字符集4:删除数据库
01 > drop database abc;> drop database if exists eof;5:查看当前数据库
0 > select database();6:建立用户且授权
0123 > CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; #建立用户jeffrey,localhost> GRANT ALL ON d01.* TO 'jeffrey'@'localhost'; #给新建立的用户jiffrey设置在数据库d01拥有所有权限> GRANT ALL PRIVILEGES ON d01.* to 'abc'@'localhost' identified by 'password'; #建立用户abc,且授权abc拥有d01的所有权限> show grants for 'jeffrey'@'localhost'; #查看jeffrey在本地的权限7:回收用户权限
012 > REVOKE INSERT ON d01.* FROM 'jeffrey'@'localhost';> show grants for 'jeffrey'@'localhost';GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `d01`.* TO 'jeffrey'@'localhost'8:建数据表
0 > create table stu( id int(4) not null, name char(20) not null, age tinyint(2) NOT NULL default '0', dept varchar(16) default NULL );9:查看表结构
01 > desc stu;> show columns from stu;10:查看建表语句
01234567891011 > show create table student\G(root@localhost) [d01]> show create table student\G*************************** 1. row ***************************Table: studentCreate Table: CREATE TABLE `student` (`id` int(4) NOT NULL AUTO_INCREMENT,`name` char(20) NOT NULL,`age` tinyint(2) NOT NULL DEFAULT '0',`dept` varchar(16) DEFAULT NULL,PRIMARY KEY (`id`),KEY `index_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf811:删除表
0 > drop table stu;12:删除主键
01234567891011121314151617181920212223242526272829 (root@localhost) [d01]> desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | int(4) | NO | PRI | NULL | auto_increment || name | char(20) | NO | MUL | NULL | || age | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | | NULL | |+-------+-------------+------+-----+---------+----------------+(root@localhost) [d01]> alter table student change id id int; #有自增必须删除自增(root@localhost) [d01]> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | char(20) | NO | MUL | NULL | || age | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+(root@localhost) [d01]> alter table student drop primary key; #删除主键(root@localhost) [d01]> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | | NULL | || name | char(20) | NO | MUL | NULL | || age | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)13:增加主键
0123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140 (root@localhost) [d01]> alter table student change id id int primary key auto_increment;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0(root@localhost) [d01]> desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | char(20) | NO | MUL | NULL | || age | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | | NULL | |+-------+-------------+------+-----+---------+----------------+(root@localhost) [d01]> alter table student change id id int primary key;Query OK, 0 rows affected (0.28 sec)Records: 0 Duplicates: 0 Warnings: 0(root@localhost) [d01]> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | char(20) | NO | MUL | NULL | || age | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+(root@localhost) [d01]> alter table student drop index index_name;(root@localhost) [d01]> alter table student add index index_name(name);(root@localhost) [d01]> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | char(20) | NO | | NULL | || age | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+4 rows in set (0.02 sec)(root@localhost) [d01]> create index index_name on student(name(11));Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0(root@localhost) [d01]> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | char(20) | NO | MUL | NULL | || age | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+(root@localhost) [d01]> show create table student\G*************************** 1. row ***************************Table: studentCreate Table: CREATE TABLE `student` (`id` int(11) NOT NULL,`name` char(20) NOT NULL,`age` tinyint(2) NOT NULL DEFAULT '0',`dept` varchar(16) DEFAULT NULL,PRIMARY KEY (`id`),KEY `index_name` (`name`(11))) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)(root@localhost) [d01]> show index from student\G*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 2. row ***************************Table: studentNon_unique: 1Key_name: index_nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: 11Packed: NULLNull:Index_type: BTREEComment:Index_comment:(root@localhost) [d01]> create index ind_name_dept on student(name,dept);(root@localhost) [d01]> drop index ind_name_dept on student;(root@localhost) [d01]> create index ind_name_dept on student(name(11),dept(10));(root@localhost) [d01]> create unique index uni_ind_dept on student(dept);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0(root@localhost) [d01]> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | char(20) | NO | MUL | NULL | || age | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | UNI | NULL | |+-------+-------------+------+-----+---------+-------+(root@localhost) [d01]> CREATE TABLE employee (id int(10),name char(20),phone int(12));(root@localhost) [d01]> INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (d01.employee) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER(root@localhost) [d01]> alter table employee change id id int primary key;(root@localhost) [d01]> select * from stu;+----+------+-----+------+| id | name | age | dept |+----+------+-----+------+| 1 | abc | 12 | eee || 2 | tom | 13 | fff |+----+------+-----+------+2 rows in set (0.00 sec)(root@localhost) [d01]> delete from stu;Query OK, 2 rows affected (0.03 sec)(root@localhost) [d01]> select * from stu;Empty set (0.00 sec)(root@localhost) [d01]> show create table stu\G*************************** 1. row ***************************Table: stuCreate Table: CREATE TABLE `stu` (`id` int(11) NOT NULL,`name` char(20) NOT NULL,`age` tinyint(2) NOT NULL DEFAULT '0',`dept` varchar(16) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)