启动与停止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>
创建指定字符集的数据库
<span class="line">1</span>
<span class="line">create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;</span>
<span class="line">1</span>
<span class="line">create database oldboy_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;</span>
<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>
企业生产环境是如何授权用户权限?
对于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的等比较危险的权限
<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>