MySQL基础命令总结

Read

MySQL版本：

Server version: 5.7.21-20-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.21-29.26, Revision 1702aea, wsrep_29.26

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密码

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

SourceByrd's Weblog-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)

SourceByrd's Weblog-https://note.t4x.org/database/mysql-command-summary/

SourceByrd's Weblog-https://note.t4x.org/database/mysql-command-summary/SourceByrd's Weblog-https://note.t4x.org/database/mysql-command-summary/

Today in History

March

2014每天定时重新启动、关机命令

申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!

Today in History

Login