MySQL必会基础命令

Contents

1 启动与停止MySQL
2 MySQL登录方法
3 设置及更改MySQ root密码
4 数据库的备份与还原
5 创建与显示数据库
6 删除库及表
- 6.1 删除库
7 创建与删除用户
8 数据库授权
9 表的相关操作

启动与停止MySQL

单实例操作方法

第一种启动方式

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>

<span class="line">[root@MySQL ~]<span class="comment"># sed -i 's#/usr/local#/application#g' /application/mysql/bin/mysqld_safe</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># /application/mysql/bin/mysqld_safe &</span></span>
<span class="line">[1] 2869</span>
<span class="line">[root@MySQL ~]<span class="comment"># 150317 22:09:19 mysqld_safe Logging to '/application/mysql/data/MySQL.err'.</span></span>
<span class="line">150317 22:09:19 mysqld_safe Starting mysqld daemon with databases from /application/mysql/data</span>
<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>
<span class="line">COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME</span>
<span class="line">mysqld  3085 mysql   10u  IPv4  25607      0t0  TCP *:mysql (LISTEN)</span>

第二种启动方式

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12

<span class="line">[root@MySQL ~]<span class="comment"># cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># vim /etc/init.d/mysqld </span></span>
<span class="line">[root@MySQL ~]<span class="comment"># sed -n 46,47p /etc/init.d/mysqld </span></span>
<span class="line">basedir=/application/mysql/</span>
<span class="line">datadir=/application/mysql/data/</span>
<span class="line">[root@MySQL ~]<span class="comment"># chmod +x /etc/init.d/mysqld </span></span>
<span class="line">[root@MySQL ~]<span class="comment"># /etc/init.d/mysqld start</span></span>
<span class="line">Starting MySQL.. SUCCESS! </span>
<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>
<span class="line">COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME</span>
<span class="line">mysqld  3376 mysql   10u  IPv4  26230      0t0  TCP *:mysql (LISTEN)</span>
<span class="line">[root@MySQL ~]<span class="comment"># chkconfig mysqld on</span></span>

多实例启动方式

第一种启动方式

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>

<span class="line">[root@MySQL ~]<span class="comment"># mysqld_safe --defaults-file=/data/3306/my.cnf &</span></span>
<span class="line">[1] 7010</span>
<span class="line">[root@MySQL ~]<span class="comment"># 150317 22:22:46 mysqld_safe Logging to '/data/3306/mysql_oldboy3306.err'.</span></span>
<span class="line">150317 22:22:46 mysqld_safe Starting mysqld daemon with databases from /data/3306/data</span>

<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>
<span class="line">COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME</span>
<span class="line">mysqld  7732 mysql   12u  IPv4  16735      0t0  TCP *:mysql (LISTEN)</span>

第二种启动方式

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>

<span class="line">[root@MySQL ~]<span class="comment"># chmod +x /data/3306/mysql </span></span>
<span class="line">[root@MySQL ~]<span class="comment"># /data/3306/mysql start</span></span>
<span class="line">Starting MySQL...</span>
<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>
<span class="line">COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME</span>
<span class="line">mysqld  6184 mysql   12u  IPv4  15526      0t0  TCP *:mysql (LISTEN)</span>

MySQL关闭方式

第一种关闭方式

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12

<span class="line">[root@MySQL ~]<span class="comment"># /application/mysql/bin/mysqld_safe &</span></span>
<span class="line">[1] 4417</span>
<span class="line">[root@MySQL ~]<span class="comment"># 150317 22:30:48 mysqld_safe Logging to '/application/mysql/data/MySQL.err'.</span></span>
<span class="line">150317 22:30:48 mysqld_safe Starting mysqld daemon with databases from /application/mysql/data</span>

<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>
<span class="line">COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME</span>
<span class="line">mysqld  4633 mysql   10u  IPv4  27506      0t0  TCP *:mysql (LISTEN)</span>
<span class="line">[root@MySQL ~]<span class="comment"># mysqladmin -uroot -p123456 shutdown</span></span>
<span class="line">150317 22:30:55 mysqld_safe mysqld from pid file /application/mysql/data/MySQL.pid ended</span>
<span class="line">[1]+  Done                    /application/mysql/bin/mysqld_safe</span>
<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>

第二种停止方式

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>

<span class="line">[root@MySQL ~]<span class="comment"># /etc/init.d/mysqld start</span></span>
<span class="line">Starting MySQL.. SUCCESS! </span>
<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>
<span class="line">COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME</span>
<span class="line">mysqld  3664 mysql   10u  IPv4  26620      0t0  TCP *:mysql (LISTEN)</span>
<span class="line">[root@MySQL ~]<span class="comment"># /etc/init.d/mysqld stop</span></span>
<span class="line">Shutting down MySQL. SUCCESS! </span>
<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>

第三种停止方式

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>

<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>
<span class="line">COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME</span>
<span class="line">mysqld  3376 mysql   10u  IPv4  26230      0t0  TCP *:mysql (LISTEN)</span>
<span class="line">[root@MySQL ~]<span class="comment"># pkill mysqld</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>

多实例关闭方式

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11

<span class="line">[root@MySQL ~]<span class="comment"># mysqld_safe --defaults-file=/data/3306/my.cnf &</span></span>
<span class="line">[1] 7777</span>
<span class="line">[root@MySQL ~]<span class="comment"># 150317 22:38:14 mysqld_safe Logging to '/data/3306/mysql_oldboy3306.err'.</span></span>
<span class="line">150317 22:38:14 mysqld_safe Starting mysqld daemon with databases from /data/3306/data</span>

<span class="line">[root@MySQL ~]<span class="comment"># mysqladmin -uroot -p3306 -S /data/3306/mysql.sock shutdown</span></span>
<span class="line">150317 22:38:17 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended</span>

<span class="line">[1]+  Done                    mysqld_safe --defaults-file=/data/3306/my.cnf</span>
<span class="line">[root@MySQL ~]<span class="comment"># </span></span>
<span class="line">[root@MySQL ~]<span class="comment"># lsof -i :3306</span></span>

MySQL登录方法

单实例登录

语法格式： mysql –u[user] –p

此处含有隐藏内容，需要正确输入密码后可见！

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
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

<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456</span></span>
<span class="line">Welcome to the MySQL monitor.  Commands end with ; or \g.</span>
<span class="line">Your MySQL connection id is 2</span>
<span class="line">Server version: 5.5.32 MySQL Community Server (GPL)</span>

<span class="line">Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.</span>

<span class="line">Oracle is a registered trademark of Oracle Corporation and/or its</span>
<span class="line">affiliates. Other names may be trademarks of their respective</span>
<span class="line">owners.</span>

<span class="line">Type <span class="string">'help;'</span> or <span class="string">'\h'</span> <span class="keyword">for</span> help. Type <span class="string">'\c'</span> to clear the current input statement.</span>

<span class="line">mysql></span>
<span class="line">```bash</span>

<span class="line"><span class="comment">### 单实例远程登录</span></span>

<span class="line">**语法格式：**</span>
<span class="line">	mysql –u[user] –p 此处含有隐藏内容，需要正确输入密码后可见！
            
            
            
            
            
            
            
            
            
            
             –h[hostname/ip]</span>

<span class="line">```bash</span>
<span class="line">[root@MySQL-Client ~]<span class="comment"># mysql -uroot -p123456 -h10.10.10.100</span></span>
<span class="line">[root@MySQL-Client ~]<span class="comment"># mysql -uroot -p123456 -hmysql --也可以指定hostname方式链接</span></span>
<span class="line">Welcome to the MySQL monitor.  Commands end with ; or \g.</span>
<span class="line">Your MySQL connection id is 6</span>
<span class="line">Server version: 5.5.32 MySQL Community Server (GPL)</span>

<span class="line">Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.</span>

<span class="line">Oracle is a registered trademark of Oracle Corporation and/or its</span>
<span class="line">affiliates. Other names may be trademarks of their respective</span>
<span class="line">owners.</span>

<span class="line">Type <span class="string">'help;'</span> or <span class="string">'\h'</span> <span class="keyword">for</span> help. Type <span class="string">'\c'</span> to clear the current input statement.</span>

<span class="line">mysql></span>

多实例登录

语法格式： mysql –u[user] –p[passwod] –S [sock path]

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14

<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p3306 -S /data/3306/mysql.sock </span></span>
<span class="line">Welcome to the MySQL monitor.  Commands end with ; or \g.</span>
<span class="line">Your MySQL connection id is 5</span>
<span class="line">Server version: 5.5.32-log Source distribution</span>

<span class="line">Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.</span>

<span class="line">Oracle is a registered trademark of Oracle Corporation and/or its</span>
<span class="line">affiliates. Other names may be trademarks of their respective</span>
<span class="line">owners.</span>

<span class="line">Type <span class="string">'help;'</span> or <span class="string">'\h'</span> <span class="keyword">for</span> help. Type <span class="string">'\c'</span> to clear the current input statement.</span>

<span class="line">mysql></span>

远程登录多实例

语法格式： mysql –u[user] –p[passwod] –S [sock path] –P[port]

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15

<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456 -h10.10.10.10 -P3306</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456 -hMySQL -P3306 --也可以指定hostname方式链接</span></span>
<span class="line">Welcome to the MySQL monitor.  Commands end with ; or \g.</span>
<span class="line">Your MySQL connection id is 10</span>
<span class="line">Server version: 5.5.32-log Source distribution</span>

<span class="line">Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.</span>

<span class="line">Oracle is a registered trademark of Oracle Corporation and/or its</span>
<span class="line">affiliates. Other names may be trademarks of their respective</span>
<span class="line">owners.</span>

<span class="line">Type <span class="string">'help;'</span> or <span class="string">'\h'</span> <span class="keyword">for</span> help. Type <span class="string">'\c'</span> to clear the current input statement.</span>

<span class="line">mysql></span>

设置及更改MySQ root密码

没有密码设置密码

语法格式： mysqladmin –u[user] password ‘

此处含有隐藏内容，需要正确输入密码后可见！

’

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19

<span class="line">[root@MySQL ~]<span class="comment"># /etc/init.d/mysqld start</span></span>
<span class="line">Starting MySQL.. SUCCESS! </span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456</span></span>
<span class="line">ERROR 1045 (28000): Access denied <span class="keyword">for</span> user <span class="string">'root'</span>@<span class="string">'localhost'</span> (using password: YES)</span>
<span class="line">[root@MySQL ~]<span class="comment"># mysqladmin -uroot password '123456'</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456</span></span>
<span class="line">Welcome to the MySQL monitor.  Commands end with ; or \g.</span>
<span class="line">Your MySQL connection id is 3</span>
<span class="line">Server version: 5.5.32 MySQL Community Server (GPL)</span>

<span class="line">Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.</span>

<span class="line">Oracle is a registered trademark of Oracle Corporation and/or its</span>
<span class="line">affiliates. Other names may be trademarks of their respective</span>
<span class="line">owners.</span>

<span class="line">Type <span class="string">'help;'</span> or <span class="string">'\h'</span> <span class="keyword">for</span> help. Type <span class="string">'\c'</span> to clear the current input statement.</span>

<span class="line">mysql></span>

多实例没有密码设置密码

语法格式： mysqladmin –u[user] password ‘

此处含有隐藏内容，需要正确输入密码后可见！

’ –S [sock path]

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19

<span class="line">[root@MySQL ~]<span class="comment"># /data/3307/mysql  start</span></span>
<span class="line">Starting MySQL...</span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456 -S /data/3307/mysql.sock </span></span>
<span class="line">ERROR 1045 (28000): Access denied <span class="keyword">for</span> user <span class="string">'root'</span>@<span class="string">'localhost'</span> (using password: YES)</span>
<span class="line">[root@MySQL ~]<span class="comment"># mysqladmin -uroot password '123456' -S /data/3307/mysql.sock </span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456 -S /data/3307/mysql.sock </span></span>
<span class="line">Welcome to the MySQL monitor.  Commands end with ; or \g.</span>
<span class="line">Your MySQL connection id is 3</span>
<span class="line">Server version: 5.5.32 Source distribution</span>

<span class="line">Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.</span>

<span class="line">Oracle is a registered trademark of Oracle Corporation and/or its</span>
<span class="line">affiliates. Other names may be trademarks of their respective</span>
<span class="line">owners.</span>

<span class="line">Type <span class="string">'help;'</span> or <span class="string">'\h'</span> <span class="keyword">for</span> help. Type <span class="string">'\c'</span> to clear the current input statement.</span>

<span class="line">mysql></span>

有密码更改密码

语法格式： mysqladmin –u[user] –p’

此处含有隐藏内容，需要正确输入密码后可见！

’password ‘

此处含有隐藏内容，需要正确输入密码后可见！

’

<span class="line">1</span>

<span class="line">[root@MySQL ~]<span class="comment"># mysqladmin -uroot -p'123456' password linux</span></span>

多实例有密码更改密码

语法格式： mysqladmin –u[user] –p’

此处含有隐藏内容，需要正确输入密码后可见！

’password ‘

此处含有隐藏内容，需要正确输入密码后可见！

’ –S [sock path]

<span class="line">1</span>

<span class="line">[root@MySQL ~]<span class="comment"># mysqladmin -uroot -p'3306' password ‘123456’ -S /data/3306/mysql.sock</span></span>

SQL语句更改密码方式

<span class="line">1</span>
2

<span class="line">mysql> update mysql.user <span class="built_in">set</span> password=password(<span class="string">"123456"</span>) <span class="built_in">where</span> user=<span class="string">'root'</span>;</span>
<span class="line">mysql> flush privileges;</span>

单实例密码丢失更改密码

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9

<span class="line">[root@MySQL ~]<span class="comment"># /etc/init.d/mysqld stop</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysqld_safe --skip-grant-tables --user=mysql &</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql</span></span>
<span class="line">mysql> update mysql.user <span class="built_in">set</span> password=password(<span class="string">'123456'</span>) <span class="built_in">where</span> user = <span class="string">'root'</span> and host = <span class="string">'localhost'</span>;</span>
<span class="line">mysql> flush privileges;</span>
<span class="line">mysql> quit</span>
<span class="line">[root@MySQL ~]<span class="comment"># mysqladmin -uroot -p123456 shutdown</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># /etc/init.d/mysqld start</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456</span></span>

多实例密码丢失更改密码

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>

<span class="line">[root@MySQL ~]<span class="comment"># killall mysqld</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysqld_safe --defaults-file=/data/3310/my.cnf --skip-grant-table &</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p -S /data/3310/mysql.sock</span></span>
<span class="line">mysql> update mysql.user <span class="built_in">set</span> password=password(<span class="string">'123456'</span>) <span class="built_in">where</span> user = <span class="string">'root'</span> and host = <span class="string">'localhost'</span>;</span>
<span class="line">mysql> flush privileges;</span>
<span class="line">mysql> quit</span>
<span class="line">[root@MySQL ~]<span class="comment"># /data/3310/mysql restart</span></span>
<span class="line">[root@MySQL ~]<span class="comment"># mysql -uroot -p123456 -S /data/3310/mysql.sock</span></span>

数据库的备份与还原

<span class="line">1</span>

<span class="line">[root@MySQL ~]<span class="comment"># mysqldump -uroot -p123456 -S /data/3307/mysql.sock -B db >/root/msyql.bak</span></span>

-B和不加-B

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

<span class="line">[root@MySQL ~]<span class="comment"># grep -vE "#|\/|^$|--" bak_b.sql </span></span>
<span class="line">USE `oldboy`;</span>
<span class="line">DROP TABLE IF EXISTS `student`;</span>
<span class="line">CREATE TABLE `student` (</span>
<span class="line">  `id` int(4) NOT NULL AUTO_INCREMENT,</span>
<span class="line">  `name` char(20) NOT NULL,</span>
<span class="line">  `age` tinyint(2) NOT NULL DEFAULT <span class="string">'0'</span>,</span>
<span class="line">  `dept` varchar(16) DEFAULT NULL,</span>
<span class="line">  PRIMARY KEY (`id`),</span>
<span class="line">  KEY `index_dept` (`dept`),</span>
<span class="line">  KEY `index_name` (`name`(8))</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8;</span>
<span class="line">LOCK TABLES `student` WRITE;</span>
<span class="line">UNLOCK TABLES;</span>
<span class="line">DROP TABLE IF EXISTS `<span class="built_in">test</span>`;</span>
<span class="line">CREATE TABLE `<span class="built_in">test</span>` (</span>
<span class="line">  `id` int(4) NOT NULL AUTO_INCREMENT,</span>
<span class="line">  `name` char(20) NOT NULL,</span>
<span class="line">  PRIMARY KEY (`id`)</span>
<span class="line">) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;</span>
<span class="line">LOCK TABLES `<span class="built_in">test</span>` WRITE;</span>
<span class="line">INSERT INTO `<span class="built_in">test</span>` VALUES (1,<span class="string">'oldboy'</span>),(2,<span class="string">'oldgirl'</span>),(3,<span class="string">'inca'</span>),(4,<span class="string">'zuma'</span>),(5,<span class="string">'kaka'</span>);</span>
<span class="line">UNLOCK TABLES;</span>

逻辑备份： 把数据库里的数据以SQL语句的方式导出来，然后还原的时候相当于执行了导出来的SQL的语句

恢复数据库

<span class="line">1</span>

<span class="line">[root@mysql ~]<span class="comment"># mysql -uroot -poldboy -S /data/3310/mysql.sock < bak_b.sql</span></span>

创建与显示数据库

创建数据库

语法格式： create dtabase [db_name];

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

<span class="line">mysql> show databases;</span>
<span class="line">+--------------------+</span>
<span class="line">| Database           |</span>
<span class="line">+--------------------+</span>
<span class="line">| information_schema |</span>
<span class="line">| mysql              |</span>
<span class="line">| performance_schema |</span>
<span class="line">+--------------------+</span>
<span class="line">3 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> create database db;</span>
<span class="line">Query OK, 1 row affected (0.00 sec)</span>

<span class="line">mysql> show databases;</span>
<span class="line">+--------------------+</span>
<span class="line">| Database           |</span>
<span class="line">+--------------------+</span>
<span class="line">| information_schema |</span>
<span class="line">| db                 |</span>
<span class="line">| mysql              |</span>
<span class="line">| performance_schema |</span>
<span class="line">+--------------------+</span>
<span class="line">4 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

查看db库的字符集,默认你数据库是什么字符集，创建出来的库就是什么字符集，当然字符集也可以指定。

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
7

<span class="line">mysql> show create database db;</span>
<span class="line">+----------+---------------------------------------------------------------+</span>
<span class="line">| Database | Create Database                                               |</span>
<span class="line">+----------+---------------------------------------------------------------+</span>
<span class="line">| db       | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET latin1 */ |</span>
<span class="line">+----------+---------------------------------------------------------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

创建指定字符集的数据库

创建GBK字符集数据库：

<span class="line">1</span>

<span class="line">create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;</span>

创建UTF8数据库：

<span class="line">1</span>

<span class="line">create database oldboy_utf8 CHARACTER SET utf8  COLLATE utf8_general_ci;</span>

创建一个名为db_gbk的GBK字符集数据库

<span class="line">1</span>
2

<span class="line">mysql> create database db_gbk default character <span class="built_in">set</span> gbk collate gbk_chinese_ci;</span>
<span class="line">Query OK, 1 row affected (0.00 sec)</span>

查看一个库是什么字符集

语法格式： show create database [db_name];

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
7

<span class="line">mysql> show create database db_gbk;</span>
<span class="line">+----------+----------------------------------------------------------------+</span>
<span class="line">| Database | Create Database                                                |</span>
<span class="line">+----------+----------------------------------------------------------------+</span>
<span class="line">| db_gbk   | CREATE DATABASE `db_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |</span>
<span class="line">+----------+----------------------------------------------------------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

查看可以设定的字符集

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
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

<span class="line">mysql> show character <span class="built_in">set</span>;</span>
<span class="line">+----------+-----------------------------+---------------------+--------+</span>
<span class="line">| Charset  | Description                 | Default collation   | Maxlen |</span>
<span class="line">+----------+-----------------------------+---------------------+--------+</span>
<span class="line">| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |</span>
<span class="line">| dec8     | DEC West European           | dec8_swedish_ci     |      1 |</span>
<span class="line">| cp850    | DOS West European           | cp850_general_ci    |      1 |</span>
<span class="line">| hp8      | HP West European            | hp8_english_ci      |      1 |</span>
<span class="line">| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |</span>
<span class="line">| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |</span>
<span class="line">| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |</span>
<span class="line">| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |</span>
<span class="line">| ascii    | US ASCII                    | ascii_general_ci    |      1 |</span>
<span class="line">| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |</span>
<span class="line">| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |</span>
<span class="line">| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |</span>
<span class="line">| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |</span>
<span class="line">| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |</span>
<span class="line">| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |</span>
<span class="line">| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |</span>
<span class="line">| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |</span>
<span class="line">| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |</span>
<span class="line">| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |</span>
<span class="line">| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |</span>
<span class="line">| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |</span>
<span class="line">| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |</span>
<span class="line">| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |</span>
<span class="line">| cp866    | DOS Russian                 | cp866_general_ci    |      1 |</span>
<span class="line">| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |</span>
<span class="line">| macce    | Mac Central European        | macce_general_ci    |      1 |</span>
<span class="line">| macroman | Mac West European           | macroman_general_ci |      1 |</span>
<span class="line">| cp852    | DOS Central European        | cp852_general_ci    |      1 |</span>
<span class="line">| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |</span>
<span class="line">| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |</span>
<span class="line">| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |</span>
<span class="line">| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |</span>
<span class="line">| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |</span>
<span class="line">| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |</span>
<span class="line">| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |</span>
<span class="line">| binary   | Binary pseudo charset       | binary              |      1 |</span>
<span class="line">| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |</span>
<span class="line">| cp932    | SJIS <span class="keyword">for</span> Windows Japanese   | cp932_japanese_ci   |      2 |</span>
<span class="line">| eucjpms  | UJIS <span class="keyword">for</span> Windows Japanese   | eucjpms_japanese_ci |      3 |</span>
<span class="line">+----------+-----------------------------+---------------------+--------+</span>
<span class="line">39 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

显示数据库

语法格式 show databases [db_name]

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11

<span class="line">mysql> show databases;</span>
<span class="line">+--------------------+</span>
<span class="line">| Database           |</span>
<span class="line">+--------------------+</span>
<span class="line">| information_schema |</span>
<span class="line">| db                 |</span>
<span class="line">| db_gbk             |</span>
<span class="line">| mysql              |</span>
<span class="line">| performance_schema |</span>
<span class="line">+--------------------+</span>
<span class="line">5 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

查看指定数据库

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
7

<span class="line">mysql> show databases like <span class="string">'db'</span>;</span>
<span class="line">+---------------+</span>
<span class="line">| Database (db) |</span>
<span class="line">+---------------+</span>
<span class="line">| db            |</span>
<span class="line">+---------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

匹配查找

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>

<span class="line">mysql> show databases like <span class="string">'db%'</span>;</span>
<span class="line">+----------------+</span>
<span class="line">| Database (db%) |</span>
<span class="line">+----------------+</span>
<span class="line">| db             |</span>
<span class="line">| db_gbk         |</span>
<span class="line">+----------------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

显示数据库中的表

语法格式 show tables [db_name]

查看指定库存在的表

<span class="line">1</span>
2

<span class="line">mysql> show tables from db;</span>
<span class="line">Empty <span class="built_in">set</span> (0.00 sec)</span>

查看当前库存在的表

<span class="line">1</span>
2
3
4

<span class="line">mysql> use db</span>
<span class="line">Database changed</span>
<span class="line">mysql> show tables;</span>
<span class="line">Empty set (0.00 sec)</span>

查看其他相关

显示当前所在数据库

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17

<span class="line">mysql> select database();</span>
<span class="line">+------------+</span>
<span class="line">| database() |</span>
<span class="line">+------------+</span>
<span class="line">| NULL       |</span>
<span class="line">+------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> use db</span>
<span class="line">Database changed</span>
<span class="line">mysql> select database();</span>
<span class="line">+------------+</span>
<span class="line">| database() |</span>
<span class="line">+------------+</span>
<span class="line">| db         |</span>
<span class="line">+------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

查看当前数据库版本

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
7

<span class="line">mysql> select version();</span>
<span class="line">+-----------+</span>
<span class="line">| version() |</span>
<span class="line">+-----------+</span>
<span class="line">| 5.5.32    |</span>
<span class="line">+-----------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

查看当前的用户

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
7

<span class="line">mysql> select user();</span>
<span class="line">+----------------+</span>
<span class="line">| user()         |</span>
<span class="line">+----------------+</span>
<span class="line">| root@localhost |</span>
<span class="line">+----------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

企业里怎么创建数据库呢？

根据开发的程序确定字符集（建议UTF8）
编译时候指定字符集，例如：

<span class="line">1</span>
2
3

<span class="line">-DDEFAULT_CHARSET=utf8 \</span>
<span class="line">-DDEFAULT_COLLATION=utf8_general_ci \</span>
<span class="line">-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \</span>

编译的时候没有指定字符集或者制定了和程序不同的字符集，如何解决？

指定字符集创建数据库即可。

<span class="line">1</span>
2

<span class="line">create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;    <span class="comment">#创建GBK字符集数据库：</span></span>
<span class="line">create database oldboy_utf8 CHARACTER SET utf8  COLLATE utf8_general_ci;        <span class="comment">#创建UTF8数据库</span></span>

删除库及表

删除库

语法格式 drop databases [db_name]

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13

<span class="line">mysql> show databases like <span class="string">'db'</span>;</span>
<span class="line">+---------------+</span>
<span class="line">| Database (db) |</span>
<span class="line">+---------------+</span>
<span class="line">| db            |</span>
<span class="line">+---------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

mysql> drop database db;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases like 'db';
Empty set (0.00 sec)

创建与删除用户

创建用户

语法格式 create user ‘[user]’@’[hostname]’ IDENTIFIED BY ‘

此处含有隐藏内容，需要正确输入密码后可见！

’;

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<span class="line">mysql> select user,host from mysql.user;</span>
<span class="line">+------+-----------+</span>
<span class="line">| user | host      |</span>
<span class="line">+------+-----------+</span>
<span class="line">| root | 127.0.0.1 |</span>
<span class="line">| root | localhost |</span>
<span class="line">+------+-----------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> create user <span class="string">'db'</span>@<span class="string">'localhost'</span> identified by <span class="string">'123456'</span>;</span>
<span class="line">Query OK, 0 rows affected (0.03 sec)</span>

<span class="line">mysql> flush privileges;</span>
<span class="line">Query OK, 0 rows affected (0.00 sec)</span>

<span class="line">mysql> select user,host from mysql.user;</span>
<span class="line">+------+-----------+</span>
<span class="line">| user | host      |</span>
<span class="line">+------+-----------+</span>
<span class="line">| root | 127.0.0.1 |</span>
<span class="line">| db   | localhost |</span>
<span class="line">| root | localhost |</span>
<span class="line">+------+-----------+</span>
<span class="line">3 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

删除用户

语法格式 drop user ‘[user]’@’[hostame]’

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19
20
21

<span class="line">mysql> select user,host from mysql.user;</span>
<span class="line">+------+-----------+</span>
<span class="line">| user | host      |</span>
<span class="line">+------+-----------+</span>
<span class="line">| root | 127.0.0.1 |</span>
<span class="line">| db   | localhost |</span>
<span class="line">| root | localhost |</span>
<span class="line">+------+-----------+</span>
<span class="line">3 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> drop user <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span>
<span class="line">Query OK, 0 rows affected (0.00 sec)</span>

<span class="line">mysql> select user,host from mysql.user;</span>
<span class="line">+------+-----------+</span>
<span class="line">| user | host      |</span>
<span class="line">+------+-----------+</span>
<span class="line">| root | 127.0.0.1 |</span>
<span class="line">| root | localhost |</span>
<span class="line">+------+-----------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

SQL语句删除用户

如drop删除不了（一般是特殊字符或大写），可以用下面的方式删除

语法格式： delete from mysql.usr where user = ‘[user]’ and host = ‘[hostname]’

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

<span class="line">mysql> select user,host from mysql.user;</span>
<span class="line">+------+-----------+</span>
<span class="line">| user | host      |</span>
<span class="line">+------+-----------+</span>
<span class="line">| root | MySQL     |</span>
<span class="line">| root | localhost |</span>
<span class="line">+------+-----------+</span>
<span class="line">4 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> drop user <span class="string">'root'</span>@<span class="string">'MySQL'</span>;</span>
<span class="line">Query OK, 0 rows affected (0.00 sec)</span>

<span class="line">mysql> select user,host from mysql.user;</span>
<span class="line">+------+-----------+</span>
<span class="line">| user | host      |</span>
<span class="line">+------+-----------+</span>
<span class="line">| root | MySQL     |</span>
<span class="line">| root | localhost |</span>
<span class="line">+------+-----------+</span>
<span class="line">4 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> delete from mysql.user <span class="built_in">where</span> user = <span class="string">'root'</span> and host = <span class="string">'MySQL'</span>;</span>
<span class="line">Query OK, 1 row affected (0.00 sec)</span>

<span class="line">mysql> select user,host from mysql.user;</span>
<span class="line">+------+-----------+</span>
<span class="line">| user | host      |</span>
<span class="line">+------+-----------+</span>
<span class="line">| root | localhost |</span>
<span class="line">+------+-----------+</span>
<span class="line">3 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

数据库授权

查看用户被赋予的权限

语法格式 show grats for ‘[user]’@’[hostname]’;

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>

<span class="line">mysql> show grants <span class="keyword">for</span> <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| Grants <span class="keyword">for</span> db@localhost                                                                                   |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| GRANT USAGE ON *.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span> IDENTIFIED BY PASSWORD <span class="string">'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'</span> |</span>
<span class="line">| GRANT ALL PRIVILEGES ON `db`.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span> WITH GRANT OPTION                                      |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

以不同的方式显示

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>

<span class="line">mysql> show grants <span class="keyword">for</span> <span class="string">'db'</span>@<span class="string">'localhost'</span>\G;</span>
<span class="line">*************************** 1. row ***************************</span>
<span class="line">Grants <span class="keyword">for</span> db@localhost: GRANT USAGE ON *.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span> IDENTIFIED BY PASSWORD <span class="string">'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'</span></span>
<span class="line">*************************** 2. row ***************************</span>
<span class="line">Grants <span class="keyword">for</span> db@localhost: GRANT ALL PRIVILEGES ON `db`.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span> WITH GRANT OPTION</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

添加用户授权

grant	all	on dbname.*	to username@localhost	identified by ‘passwd’
授权命令	对应权限	目标：库和表	用户名和客户端主机	用户密码

语法格式 GRANT ALL ON db1.* TO ‘jeffrey’@’localhost’;

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18

<span class="line">mysql> show grants <span class="keyword">for</span> <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| Grants <span class="keyword">for</span> db@localhost                                                                                   |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| GRANT USAGE ON *.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span> IDENTIFIED BY PASSWORD <span class="string">'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'</span> |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>
<span class="line">mysql> grant select on db.* to <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span>
<span class="line">Query OK, 0 rows affected (0.00 sec)</span>

<span class="line">mysql> show grants <span class="keyword">for</span> <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| Grants <span class="keyword">for</span> db@localhost                                                                                   |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| GRANT USAGE ON *.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span> IDENTIFIED BY PASSWORD <span class="string">'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'</span> |</span>
<span class="line">| GRANT SELECT ON `db`.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span>                                                                |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

移除用户权限

语法格式 revoke select on [db_name].[table_name] from ‘[user]’@’[hostname]’;

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19

<span class="line">mysql> show grants <span class="keyword">for</span> <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| Grants <span class="keyword">for</span> db@localhost                                                                                   |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| GRANT USAGE ON *.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span> IDENTIFIED BY PASSWORD <span class="string">'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'</span> |</span>
<span class="line">| GRANT SELECT ON `db`.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span>                                                                |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> revoke select on db.* from <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span>
<span class="line">Query OK, 0 rows affected (0.00 sec)</span>

<span class="line">mysql> show grants <span class="keyword">for</span> <span class="string">'db'</span>@<span class="string">'localhost'</span>;</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| Grants <span class="keyword">for</span> db@localhost                                                                                   |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">| GRANT USAGE ON *.* TO <span class="string">'db'</span>@<span class="string">'localhost'</span> IDENTIFIED BY PASSWORD <span class="string">'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'</span> |</span>
<span class="line">+-----------------------------------------------------------------------------------------------------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

创建一个和root等价的管理员授权方法

<span class="line">1</span>
2

<span class="line">grant all privileges on *.* to system@<span class="string">'localhost'</span> identified by <span class="string">'oldboy123'</span> with grant option;</span>
<span class="line">flush privileges;</span>

企业生产环境是如何授权用户权限？

博客，CMS等产品如何授权？

对于WEB连接也难怪乎授权尽量采用最小化原则，很多开源软件都是WEB界面安装，在安装期间除了select,insert,update,delete4个权限外，还需要create,drop等比较危险的权限

<span class="line">1</span>

<span class="line">mysql> grant select,insert,update,delete,create,drop on blog.* to <span class="string">'blog'</span>@<span class="string">'localhost'</span> identified by <span class="string">'123456'</span>;</span>

常规情况下授权select,insert,update,delete4个权限即可，有的开源软件，例如：discuz还需要create,drop的等比较危险的权限

生成数据库表后要收回create,drop授权

<span class="line">1</span>

<span class="line">mysql> revoke create,drop on blog.* from <span class="string">'blog'</span>@<span class="string">'localhost'</span>;</span>

生产环境针对主库（写为主读为辅）用户的授权：

普通环境：本机：LNMP,LAMP环境护具库授权

<span class="line">1</span>

<span class="line">mysql> grant all privileges on blog.* to ‘blog’@’localhost’ identified by ‘123456’;</span>

应用服务器和数据库服务器不在一个主机上的授权

<span class="line">1</span>

<span class="line">mysql> grant all privileges on blog.* to ‘blog’@’10.0.0.%’ identified by ‘123456’;</span>

严格的授权：重视安全，忽略了方便

<span class="line">1</span>

<span class="line">mysql> grant select on blog.* to ‘blog’@’10.0.0.%’ identified by ‘123465’;</span>

生产环境从库（只读）用户的授权

<span class="line">1</span>

<span class="line">mysql> grant select on blog.* to ‘blog’@’10.0.0.%’ identified by ‘123456’;</span>

说明：这里表示给10.0.0.0/24的用户blog管理blog数据库的所有表（*表示所有表）只读权限（select）,密码为123456

表的相关操作

创建表

建表的基本命令语法：
create table <表名> (
 <字段名1> <类型1> ,
…
<字段名n> <类型n>);
<!--类型N--><!--字段名N--><!--类型1--><!--字段名1--><!--表名-->

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>

<span class="line">CREATE TABLE `<span class="built_in">test</span>` (</span>
<span class="line">  `id` int(4) NOT NULL,</span>
<span class="line">  `name` char(20) NOT NULL,</span>
<span class="line">  `age` tinyint(2) NOT NULL DEFAULT <span class="string">'0'</span>,</span>
<span class="line">  `dept` varchar(16) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8 <span class="comment">#创建时指定字符集</span></span>

索引类似文件系统的inode,记录类似文件系统的block, 索引要创建再where条件列上，而非要查询的内容列，才能加快查询速度

主键索引：类似身份证唯一标示表内的一条记录

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>

<span class="line">create table <span class="built_in">test</span>(</span>
<span class="line">id int(4) not null AUTO_INCREMENT,</span>
<span class="line">name char(20) not null,</span>
<span class="line">age tinyint(2)  NOT NULL default <span class="string">'0'</span>,</span>
<span class="line">dept varchar(16)  default NULL,</span>
<span class="line">primary key(id), <span class="comment">#主键索引，记录唯一</span></span>
<span class="line">KEY index_name (name) <span class="comment">#普通索引，记录可能不唯一</span></span>
<span class="line">);</span>

删除表

语法格式 drop tables [table_name]

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13

mysql> show tables from db;
+--------------+
| Tables_in_db |
+--------------+
| test         |
+--------------+
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

<span class="line">mysql> show tables from db;</span>
<span class="line">Empty <span class="built_in">set</span> (0.00 sec)</span>

查看表的内容

命令语法： select <字段1，字段2…> from <表名> where <表达式>

其中select fom where是不能随便改的

<span class="line">1</span>

<span class="line">mysql> select *  from oldboy.test;</span>

条件查询

<span class="line">1</span>

<span class="line">mysql> select id,name from <span class="built_in">test</span> whee name=<span class="string">"ansheng"</span>;</span>

多个条件查询

<span class="line">1</span>

<span class="line">mysql> select id,name from <span class="built_in">test</span> <span class="built_in">where</span> name=<span class="string">'ansheng'</span> or id=5;</span>

排序

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
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

<span class="line">mysql> select id,name from <span class="built_in">test</span> order by id asc;</span>
<span class="line">+----+---------+</span>
<span class="line">| id | name    |</span>
<span class="line">+----+---------+</span>
<span class="line">|  1 | oldboy  |</span>
<span class="line">|  2 | oldgirl |</span>
<span class="line">|  3 | inca    |</span>
<span class="line">|  4 | zuma    |</span>
<span class="line">|  5 | kaka    |</span>
<span class="line">+----+---------+</span>
<span class="line">5 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> select id,name from <span class="built_in">test</span> order by id desc;</span>
<span class="line">+----+---------+</span>
<span class="line">| id | name    |</span>
<span class="line">+----+---------+</span>
<span class="line">|  5 | kaka    |</span>
<span class="line">|  4 | zuma    |</span>
<span class="line">|  3 | inca    |</span>
<span class="line">|  2 | oldgirl |</span>
<span class="line">|  1 | oldboy  |</span>
<span class="line">+----+---------+</span>
<span class="line">5 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> select id,name from <span class="built_in">test</span> <span class="built_in">where</span> id >2 order by id desc; </span>
<span class="line">+----+------+</span>
<span class="line">| id | name |</span>
<span class="line">+----+------+</span>
<span class="line">|  5 | kaka |</span>
<span class="line">|  4 | zuma |</span>
<span class="line">|  3 | inca |</span>
<span class="line">+----+------+</span>
<span class="line">3 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

多表查询

<span class="line">1</span>

<span class="line">mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC <span class="built_in">where</span> student.Sno=SC.Sno and course.Cno=SC.Cno order by student.Sname;</span>

查看表建立结构

<span class="line">1</span>
2

<span class="line">mysql> desc student;</span>
<span class="line">mysql> show columns from student;</span>

查看建立的表语句（可以看索引及创建表的相关信息）

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10

<span class="line">mysql> show create table db.test\G</span>
<span class="line">*************************** 1. row ***************************</span>
<span class="line">       Table: <span class="built_in">test</span></span>
<span class="line">Create Table: CREATE TABLE `<span class="built_in">test</span>` (</span>
<span class="line">  `id` int(4) NOT NULL,</span>
<span class="line">  `name` char(20) NOT NULL,</span>
<span class="line">  `age` tinyint(2) NOT NULL DEFAULT <span class="string">'0'</span>,</span>
<span class="line">  `dept` varchar(16) DEFAULT NULL</span>
<span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

更改表名称

语法格式 rename table tb_name to new_tb_name

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11
12
13
14
15
16
17
18
19

<span class="line">mysql> use db</span>
<span class="line">Database changed</span>
<span class="line">mysql> show tables;</span>
<span class="line">+--------------+</span>
<span class="line">| Tables_<span class="keyword">in</span>_db |</span>
<span class="line">+--------------+</span>
<span class="line">| <span class="built_in">test</span>         |</span>
<span class="line">+--------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

<span class="line">mysql> rename table <span class="built_in">test</span> to linux;</span>
<span class="line">Query OK, 0 rows affected (0.08 sec)</span>
<span class="line">mysql> show tables;</span>
<span class="line">+--------------+</span>
<span class="line">| Tables_<span class="keyword">in</span>_db |</span>
<span class="line">+--------------+</span>
<span class="line">| linux        |</span>
<span class="line">+--------------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

第二种改名方法

<span class="line">1</span>

<span class="line">mysql> alter table kkk rename to <span class="built_in">test</span>;</span>

往表里插入数据

按规矩指定所有列名，并且每列都插入值

语法格式 insert into tb_name(col_name) values(‘character’);

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10

<span class="line">mysql> insert into <span class="built_in">test</span>(id,name) values(1,<span class="string">'natasha'</span>);</span>
<span class="line">Query OK, 1 row affected (0.00 sec)</span>

<span class="line">mysql> select * from <span class="built_in">test</span>;</span>
<span class="line">+----+---------+-----+------+</span>
<span class="line">| id | name    | age | dept |</span>
<span class="line">+----+---------+-----+------+</span>
<span class="line">|  1 | natasha |   0 | NULL |</span>
<span class="line">+----+---------+-----+------+</span>
<span class="line">1 row <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

由于ID列为自增的，所以，可以只在name列插入值

<span class="line">1</span>
<span class="line">2</span>
<span class="line">3</span>
<span class="line">4</span>
<span class="line">5</span>
<span class="line">6</span>
<span class="line">7</span>
<span class="line">8</span>
9
10
11

<span class="line">mysql> insert into <span class="built_in">test</span>(name) values(<span class="string">'harry'</span>);</span>
<span class="line">Query OK, 1 row affected, 1 warning (0.00 sec)</span>

<span class="line">mysql> select * from <span class="built_in">test</span>;</span>
<span class="line">+----+---------+-----+------+</span>
<span class="line">| id | name    | age | dept |</span>
<span class="line">+----+---------+-----+------+</span>
<span class="line">|  1 | natasha |   0 | NULL |</span>
<span class="line">|  0 | harry   |   0 | NULL |</span>
<span class="line">+----+---------+-----+------+</span>
<span class="line">2 rows <span class="keyword">in</span> <span class="built_in">set</span> (0.00 sec)</span>

如果不指定列，就要按规矩为每列都插入恰当的值

<span class="line">1</span>

<span class="line">insert into <span class="built_in">test</span> values(3,’inca’)</span>

批量插入数据方法

<span class="line">1</span>
2

<span class="line">insert into <span class="built_in">test</span> values(4,<span class="string">'zuma'</span>),(5,<span class="string">'kaka'</span>);</span>
<span class="line">insert into `<span class="built_in">test</span>` VALUES (1,<span class="string">'oldboy'</span>),(2,<span class="string">'oldgirl'</span>),(3,<span class="string">'inca'</span>),(4,<span class="string">'zuma'</span>),(5,<span class="string">'kaka'</span>);</span>

删除表中的数据

清空表内容

<span class="line">1</span>
2

<span class="line">mysql> truncate table <span class="built_in">test</span>;</span>
<span class="line">mysql> delete from <span class="built_in">test</span>;</span>

删除字段：

<span class="line">1</span>

<span class="line">mysql> ALTER TABLE table_name DROP field_name;</span>

发表于：Sql

2016-07-11

# Sql

复制链接