MySQL基础命令总结

MySQL版本：

0：设置root密码

$ /usr/local/mysql/bin/mysqladmin -u root password "admin" > SET PASSWORD FOR root@'localhost' = PASSWORD('admin123');

0 1	$ /usr/local/mysql/bin/mysqladmin -u root password "admin" > SET PASSWORD FOR root@'localhost' = PASSWORD('admin123');

1：修改用户密码

$ /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;

0 1 2

$ /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：删除多余用户

> drop user ''@'localhost'; > delete from mysql.user where user='' and host='lamp';

0 1	> drop user ''@'localhost'; > delete from mysql.user where user='' and host='lamp';

3：建立数据库

> 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; #相关字符集

0 1 2 3 4

> 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：删除数据库

> drop database abc; > drop database if exists eof;

0 1	> drop database abc; > drop database if exists eof;

5：查看当前数据库

> select database();

0	> select database();

6：建立用户且授权

> 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在本地的权限

0 1 2 3

> 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：回收用户权限

> 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'

0 1 2

> 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：建数据表

> 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 );

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：查看表结构

> desc stu; > show columns from stu;

0 1	> desc stu; > show columns from stu;

10：查看建表语句

> show create table student\G (root@localhost) [d01]> show create table student\G *************************** 1. row *************************** Table: student Create 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=utf8

0 1 2 3 4 5 6 7 8 9 10 11

> show create table student\G (root@localhost) [d01]> show create table student\G *************************** 1. row ***************************        Table: student Create 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=utf8

文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-command-summary/

11：删除表

> drop table stu;

0	> drop table stu;

12：删除主键

(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)

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

(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：增加主键

(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: student Create 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=utf8 1 row in set (0.00 sec) (root@localhost) [d01]> show index from student\G *************************** 1. row *************************** Table: student Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: student Non_unique: 1 Key_name: index_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: 11 Packed: NULL Null: Index_type: BTREE Comment: 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: stu Create 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=utf8 1 row in set (0.00 sec)

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140

(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: student Create 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=utf8 1 row in set (0.00 sec) (root@localhost) [d01]> show index from student\G *************************** 1. row ***************************         Table: student    Non_unique: 0      Key_name: PRIMARY Seq_in_index: 1   Column_name: id     Collation: A   Cardinality: 0      Sub_part: NULL        Packed: NULL          Null:    Index_type: BTREE       Comment: Index_comment: *************************** 2. row ***************************         Table: student    Non_unique: 1      Key_name: index_name Seq_in_index: 1   Column_name: name     Collation: A   Cardinality: 0      Sub_part: 11        Packed: NULL          Null:    Index_type: BTREE       Comment: 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: stu Create 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=utf8 1 row in set (0.00 sec)

文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/mysql-command-summary/