【运维工程师必知必会】——MySql基础

一、SQL语句

1.分类

DDL（data definition language）数据定义语言（create、alter、drop），管理基础数据。例如：库、表 #运维要熟练，开发也要熟练

DCL（data control language）数据控制语言（grant、revoke、commit、rollback）用户授权，权限收回，数据提交，回滚等。 #运维要熟练运用

DML（data manipulation language）数据操作语言（select、insert、delete、update）针对表里的数据进行操作、记录。 #开发要熟练，运维要了解

二、命令详解

1.修改数据库密码

1）库外修改

1 #将原密码：rootpwd 修改为：test123
2 [root@mysql-db01 ~]# mysqladmin -uroot -prootpwd password test123

2）库内修改

1 #登录数据库
2 [root@mysql-db01 ~]# mysql -uroot -ptest123
3 #将root密码修改为test456
4 mysql> update mysql.user set password = password('test456') where user = 'root' and host = 'localhost';
5 #刷新系统授权表
6 mysql> flush privileges;

2.库的基础操作

1）创建数据库

1 #语法
2 create database <数据库名>
3 #创建一个库名为test1的库
4 mysql> create database test1;

2）查看所有库

1 #查库
2 mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test1              |#之前创建的test1库
+--------------------+

3）查看建库语句

1 #语法
2 show create database <数据库名>G
3 #查看test1的建库语句
4 mysql> show create database test1G
*************************** 1. row ***************************
       Database: test1
Create Database: CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */  #默认字符集是utf-8

4）指定字符集建库

1 #语法
2 create database <数据库名> character set <字符集> collate <全称>;
3 #创建数据库：test2，字符集为：gbk
4 mysql> create database test2 character set gbk collate gbk_chinese_ci;
5 #查看test2的建库语句
6 mysql> show create database test2G
*************************** 1. row ***************************
       Database: test2
Create Database: CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET gbk */ #默认字符集是gbk

5）查找指定数据库

1 #语法
2 show databases like <条件>;
3 #查找test库
4 mysql> show databases like 'test';
+-----------------+
| Database (test) |
+-----------------+
| test            |
+-----------------+
5 #查找test1库
6 mysql> show databases like 'test1';
+------------------+
| Database (test1) |
+------------------+
| test1            |
+------------------+
7 #模糊匹配，查找带te的库
8 mysql> show databases like '%te%';
+-----------------+
| Database (%te%) |
+-----------------+
| test            |
| test1           |
| test2           |
+-----------------+

6）切换数据库

1 #语法
2 use <数据库名>
3 #切换到test1
4 mysql> use test1;
5 Database changed
6 #切换到test2
7 mysql> use test2
8 Database changed

7）查看当前所在库

1 #查看当前所在库
2 mysql> select database();
+------------+
| database() |
+------------+
| test2      |
+------------+
3 #切换到test1
4 mysql> use test1
5 Database changed
6 #查看当前所在库
7 mysql> select database();
+------------+
| database() |
+------------+
| test1      |
+------------+

8）查看库内的表

 1 #查看test1库中的表
 2 mysql> show tables;
 3 #库里没有表
 4 Empty set (0.00 sec)
 5 #切换到mysql库
 6 mysql> use mysql
 7 #查看mysql库中的表
 8 mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
 9 #模糊匹配，查找带有slave的表名
10 mysql> show tables like '%slave%';
+---------------------------+
| Tables_in_mysql (%slave%) |
+---------------------------+
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
+---------------------------+

9）查询用户

1 mysql> select user,host from mysql.user;
+------+------------+
| user | host       |
+------+------------+
| root | 127.0.0.1  |
| root | ::1        |
|      | localhost  |
| root | localhost  |
|      | mysql-db01 |
| root | mysql-db01 |
+------+------------+

10）删除用户

1 #语法
2 drop user <user> <host>;
3 #删除root连接IPv6的用户
4 mysql> drop user root@'::1';
5 #查询用户
6 mysql> select user,host from mysql.user;
+------+------------+
| user | host       |
+------+------------+
| root | 127.0.0.1  |
|      | localhost  |
| root | localhost  |
|      | mysql-db01 |
| root | mysql-db01 |
+------+------------+

****** 当 drop 无法删除的时候 ******

1 #语法
2 delete from mysql.user where user='<用户>' and host='<主机>';
3 #删除root连接IPv6的用户
4 mysql> delete from mysql.user where user='root' and host='::1';
+------+------------+
| user | host       |
+------+------------+
| root | 127.0.0.1  |
|      | localhost  |
| root | localhost  |
|      | mysql-db01 |
| root | mysql-db01 |
+------+------------+

11）用户授权

1 #语法
2 grant <权限> on <数据库>.<表> to <用户>@<主机> identified by <密码>;
3 #创建一个user用户密码为123456
4 mysql> grant all on *.* to user@'10.0.0.%' identified by '123456';
5 #检查用户是否创建成功
6 mysql> select user,host from mysql.user;
+------+------------+
| user | host       |
+------+------------+
| user | 10.0.0.%   |
| root | 127.0.0.1  |
|      | localhost  |
| root | localhost  |
|      | mysql-db01 |
| root | mysql-db01 |
+------+------------+

3.表的基础操作

1）建表语法

1 create table student(                      #<== create  table 表示创建表的固定关键字，student为表名
2 id int(4) not null,                        #<== 学号列，数字类型，长度为4，不为空值。
3 name char(20) not null,                    #<== 名字列，定长字符类型，长度20，不为空值。
4 age tinyint(2)  NOT NULL default '0',      #<== 年龄列，很小的数字类型，长度为2，不为空，默认为0值。
5 dept varchar(16)  default NULL             #<== 系别列，变长字符类型，长度16，默认为空。
6 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;    #<== 引擎和字符集，引擎默认为innodb，字符集，继承库的latin1

数字类型

列类型	需要的存储量
TINYINT	1字节
SMALLNT	2个字节
MEDIUMINT	3个字节
INT	4个字节（4294967296）
BIGINT	8个字节
FLOAT（X）	4如果X <=24 或 8 如果25<=x <=53
FLOAT	4个字节

日期类型

列类型	需要的存储量
DATE	3字节
DATETIME	8个字节
TIMESTAMP	4个字节
TIME	3个字节
YEAR	1个字节

字符串类型

列类型	需要的存储量
CHAR（M）	M字节，1<=M<=255字节
VARCHAR（M）	L+1字节，在此L<=M和1<=M<=255
TINYBLOB,TINYTEXT	L+1字节，在此L< 2 ^ 8
BLOB,TEXT	L+2字节，在此L< 2 ^ 16

CHAR 和 VARCHAR的区别

值	CHAR（4）	存储需求	VARCHAR	存储需求
‘ ‘	’ ‘	4个字节	‘ ‘	1个字节
’ab’	‘ab ‘	4个字节	‘ ab’	3个字节
‘abcd’	‘abcd’	4个字节	‘ abcd’	5个字节
‘abcdefgh’	‘abcd’	4个字节	‘abcd’	5个字节

2）创建索引

主键索引：要求列的内容是唯一的。

 1 #使用test1库
 2 mysql> use test1
 3 #创建主索引
 4 mysql> CREATE TABLE `student` (
 5     ->   `id` int(4) NOT NULL auto_increment,
 6     ->   `name` char(20) NOT NULL,
 7     ->   `age` tinyint(2) NOT NULL DEFAULT '0',
 8     ->   `dept` varchar(16) DEFAULT NULL,
 9     ->    primary key(id)
10     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果在建表的时候，没有创建索引可以使用alter来创建索引:

1 #增加索引
2 mysql> alter table student add primary key(id);
3 #查看索引
4 mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 #将索引改为自增
6 mysql> alter table student change id id int(4) auto_increment;

普通索引：一个表内可以创建多个。

1 #创建普通索引
2 mysql> CREATE TABLE `student1` (
3     ->   `id` int(4) NOT NULL auto_increment,
4     ->   `name` char(20) NOT NULL,
5     ->   `age` tinyint(2) NOT NULL DEFAULT '0',
6     ->   `dept` varchar(16) DEFAULT NULL,
7     ->    primary key(id),
8     ->    KEY index_name(name)
9     -> );

联合索引：多个表创建一个索引，加大唯一性

1 #创建联合索引
2 create index ind_name_dept on student(name,dept);
3 #根据前八个字符来创建联合索引
4 create index ind_name_deptnew on student(name(8),dept(8));
5 #查看索引
6 mysql> show index from student;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY          |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | ind_name_dept    |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | ind_name_dept    |            2 | dept        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | ind_name_deptnew |            1 | name        | A         |           0 |        8 | NULL   |      | BTREE      |         |               |
| student |          1 | ind_name_deptnew |            2 | dept        | A         |           0 |        8 | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

唯一索引：生产使用不多，但是针对登录账号可以选择

1 #创建唯一索引
2 mysql> create unique index uni_index_name on student(name);
3 #查看索引
4 mysql> show index from student;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY          |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | uni_index_name   |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | ind_name_dept    |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | ind_name_dept    |            2 | dept        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | ind_name_deptnew |            1 | name        | A         |           0 |        8 | NULL   |      | BTREE      |         |               |
| student |          1 | ind_name_deptnew |            2 | dept        | A         |           0 |        8 | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

问题1：既然索引可以加快查询速度，那就给所有列建索引吧？

解答：因为索引不但占用存储空间，而且更新数据时还需要维护索引数据的，因此，索引是一把双刃剑，并不是越多越好，例如：数十到数百行的小表上无需建立索引，插入更新频繁，读取比较少的表要尽量不建立索引。

问题2：需要在哪些列上创建索引才能加快查询速度呢？

解答：select user,host from mysql.user where password=…,索引一定要创建在where后的条件上，而不是select后的选择数据的列上，另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如，男女性别列唯一值少，不适合建立索引。

创建索引知识小结：

索引类似书籍的目录，会加快查询数据的速度。
要在表的列（字段）上创建索引。
索引会加快查询的速度，但是也会影响更新的速度，因为更新要维护索引数据。
索引列并不是越多越好，要在频繁查询的表语句where后的条件列上创建索引。
小表或重复值很多的列上可以不创建索引。要在大表以及重复值少的条件列上创建索引。
多个联合索引有前缀生效特性。
当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引。
索引从工作方式区分，有主键，唯一，普通索引。
索引类型会有BTREE（默认）和hash（适合做缓存（内蕴数据库））等

创建索引相关命令小结：

创建主键索引

1 alter table student change id id int primary key auto_increment;

删除主键索引（主键列不能自增）

1 alter table student drop primary key;

创建普通索引：

1 alter table student add index index_dept(dept);

根据列的前n个字符创建普通索引：

1 create index index_dept on student(dept(8));

根据多个列创建联合普通索引：

1 create index ind_name_dept on student(name,dept);

根据多个列的前n个字符创建联合普通索引：

1 create index ind_name_dept on student(name(8),dept(10));

创建唯一索引：

1 create unique index uni_ind_name on student(name);

查看索引：

1 desc student;
2 show index from student;

删除普通索引与唯一索引：

1 alter table student drop index index_dept;
2 drop index index_dept on student;

查看表记录唯一值的数量：

1 select count(distinct user) from mysql.user;
2 select count(distinct (user,host)) from mysql.user;

3）增删改表的字段

1 #创建一个练习用的表
2 mysql> CREATE TABLE `test` (
3     -> `id` int(4) NOT NULL AUTO_INCREMENT,
4     -> `name` char(20) NOT NULL,
5     -> PRIMARY KEY (`id`)
6     -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

查看表的信息

1 #查看表
2 mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| test            |
+-----------------+
3 #查看表结构
4 mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

增加一个字段

1 #增加sex这个字段
2 mysql> alter table test add sex char(4);
3 #查看增加的字段
4 mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

插入一个字段

1 #在name后面增加一个age字段
2 mysql> alter table test add age int(4) after name;
3 #查看字段
4 mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(4)   | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

在第一行插入字段

1 #在第一行加入一个QQ字段
2 mysql> alter table test add qq varchar(15) first
3 #查看字段
4 mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| qq    | varchar(15) | YES  |     | NULL    |                |
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | int(4)      | YES  |     | NULL    |                |
| sex   | char(4)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

4）修改字段类型

1 #把age字段类型改成char4
2 mysql> alter table test modify age char(4) after name;
3 #查看字段类型
4 mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| qq    | varchar(15) | YES  |     | NULL    |                |
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | char(4)     | YES  |     | NULL    |                |
| sex   | char(4)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

5）修改字段名

1 #讲age字段名改为testage
2 mysql> alter table test change age testage char(4) after name;
3 #查看字段
4 mysql> desc test;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| qq      | varchar(15) | YES  |     | NULL    |                |
| id      | int(4)      | NO   | PRI | NULL    | auto_increment |
| name    | char(20)    | NO   |     | NULL    |                |
| testage | char(4)     | YES  |     | NULL    |                |
| sex     | char(4)     | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

6）插入数据

1 #语法
2 insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]
3 #添加id为1，名字为zzgx的数据到test表中
4 mysql> insert into test(id,name) values(1,'zzgx');
5 #查询表中内容
6 mysql> select * from test;
+------+----+------+---------+------+
| qq   | id | name | testage | sex  |
+------+----+------+---------+------+
| NULL |  1 | zzgx | NULL    | NULL |
+------+----+------+---------+------+

7）批量插入多条数据

1 #插入数据，id：3 name：zuma和id：4 name：kaka
2 mysql> insert into test(id,name) values(3,'zuma'),(4,'kaka');
3 #查看表中数据
4 mysql> select * from test;
+------+----+------+---------+------+
| qq   | id | name | testage | sex  |
+------+----+------+---------+------+
| NULL |  1 | zzgx | NULL    | NULL |
| NULL |  3 | zuma | NULL    | NULL |
| NULL |  4 | kaka | NULL    | NULL |
+------+----+------+---------+------+

8）删除数据

1 #删除id为3的数据
2 mysql> delete from test where id=3;
3 #查看表中数据
4 mysql> select * from test;
+------+----+------+---------+------+
| qq   | id | name | testage | sex  |
+------+----+------+---------+------+
| NULL |  1 | zzgx | NULL    | NULL |
| NULL |  4 | kaka | NULL    | NULL |
+------+----+------+---------+------+

9）查表语句

1 #查询test表里前两行数据
2 mysql> select * from test limit 2;
+------+----+------+---------+------+
| qq   | id | name | testage | sex  |
+------+----+------+---------+------+
| NULL |  1 | zzgx | NULL    | NULL |
| NULL |  4 | kaka | NULL    | NULL |
+------+----+------+---------+------+

1 #升序查询
2 mysql> select * from test where id>3 or name='zuma' order by id;
+------+----+------+---------+------+
| qq   | id | name | testage | sex  |
+------+----+------+---------+------+
| NULL |  4 | kaka | NULL    | NULL |
+------+----+------+---------+------+

1 #按id倒叙查询
2 mysql> select * from test where id>3 or name='zuma' order by id desc;
+------+----+------+---------+------+
| qq   | id | name | testage | sex  |
+------+----+------+---------+------+
| NULL |  4 | kaka | NULL    | NULL |
+------+----+------+---------+------+

10）连表查询

首先需要创建三个表：

 1 #第一张表
 2 mysql> create table student(
 3     ->      Sno int(10) NOT NULL COMMENT '学号',
 4     ->      Sname varchar(16) NOT NULL COMMENT '姓名',
 5     ->      Ssex char(2) NOT NULL COMMENT '性别',
 6     ->      Sage tinyint(2)  NOT NULL default '0' COMMENT '学生年龄',
 7     ->      Sdept varchar(16)  default NULL  COMMENT '学生所在系别', 
 8     ->      PRIMARY KEY  (Sno) ,
 9     ->      key index_Sname (Sname)
10     ->      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

1 #第二张表
2 mysql> create table course(
3     ->      Cno int(10) NOT NULL COMMENT '课程号',
4     ->      Cname varchar(64) NOT NULL COMMENT '课程名',
5     ->      Ccredit tinyint(2) NOT NULL COMMENT '学分',
6     ->      PRIMARY KEY  (Cno) 
7     ->      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

1 #第三张表
2 mysql> CREATE TABLE `SC` (
3     ->         SCid int(12) NOT NULL auto_increment COMMENT '主键',
4     ->        `Cno` int(10) NOT NULL COMMENT '课程号',
5     ->        `Sno` int(10) NOT NULL COMMENT '学号',
6     ->        `Grade` tinyint(2) NOT NULL COMMENT '学生成绩',
7     ->        PRIMARY KEY  (`SCid`)
8     ->      ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

1 #查看刚才创建的三张表
2 mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| course          |
| sc              |
| student         |
+-----------------+

1 #往student表中插入数据
2 mysql> INSERT INTO student values(0001,'宏志','男',30,'计算机网络');
3 mysql> INSERT INTO student values(0002,'王硕 ','男',30,'computer');
4 mysql> INSERT INTO student values(0003,'oldboy','男',28,'物流管理');
5 mysql> INSERT INTO student values(0004,'脉动','男',29,'computer');
6 mysql> INSERT INTO student values(0005,'oldgirl','女',26,'计算机科学与技术');
7 mysql> INSERT INTO student values(0006,'莹莹','女',22,'护士');
8 #查询数据
9 mysql> select * from student;
+-----+---------+------+------+--------------------------+
| Sno | Sname   | Ssex | Sage | Sdept                    |
+-----+---------+------+------+--------------------------+
|   1 | 宏志    | 男   |   30 | 计算机网络               |
|   2 | 王硕    | 男   |   30 | computer                 |
|   3 | oldboy  | 男   |   28 | 物流管理                 |
|   4 | 脉动    | 男   |   29 | computer                 |
|   5 | oldgirl | 女   |   26 | 计算机科学与技术         |
|   6 | 莹莹    | 女   |   22 | 护士                     |
+-----+---------+------+------+--------------------------+

1 #往course表中插入数据
2 mysql> INSERT INTO course values(1001,'Linux中高级运维',3);
3 mysql> INSERT INTO course values(1002,'Linux高级架构师',5);
4 mysql> INSERT INTO course values(1003,'MySQL高级Dba',4);
5 mysql> INSERT INTO course values(1004,'Python运维开发',4);
6 mysql> INSERT INTO course values(1005,'Java web开发',3);
7 #查询数据
8 mysql> select * from course;
+------+----------------------+---------+
| Cno  | Cname                | Ccredit |
+------+----------------------+---------+
| 1001 | Linux中高级运维      |       3 |
| 1002 | Linux高级架构师      |       5 |
| 1003 | MySQL高级Dba         |       4 |
| 1004 | Python运维开发       |       4 |
| 1005 | Java web开发         |       3 |
+------+----------------------+---------+

 1 #往SC表中插入数据
 2 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0001,1001,4);
 3 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0001,1002,3);
 4 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0001,1003,1);
 5 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0001,1004,6);
 6 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0002,1001,3);
 7 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0002,1002,2);
 8 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0002,1003,2);
 9 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0002,1004,8);
10 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0003,1001,4);
11 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0003,1002,4);
12 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0003,1003,2);
13 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0003,1004,8);
14 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0004,1001,1);
15 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0004,1002,1);
16 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0004,1003,2);
17 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0004,1004,3);
18 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0005,1001,5);
19 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0005,1002,3);
20 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0005,1003,2);
21 mysql> INSERT INTO SC(Sno,Cno,Grade)  values(0005,1004,9);
22 #查询数据
23 mysql> select * from SC;
+------+------+-----+-------+
| SCid | Cno  | Sno | Grade |
+------+------+-----+-------+
|    1 | 1001 |   1 |     4 |
|    2 | 1002 |   1 |     3 |
|    3 | 1003 |   1 |     1 |
|    4 | 1004 |   1 |     6 |
|    5 | 1001 |   2 |     3 |
|    6 | 1002 |   2 |     2 |
|    7 | 1003 |   2 |     2 |
|    8 | 1004 |   2 |     8 |
|    9 | 1001 |   3 |     4 |
|   10 | 1002 |   3 |     4 |
|   11 | 1003 |   3 |     2 |
|   12 | 1004 |   3 |     8 |
|   13 | 1001 |   4 |     1 |
|   14 | 1002 |   4 |     1 |
|   15 | 1003 |   4 |     2 |
|   16 | 1004 |   4 |     3 |
|   17 | 1001 |   5 |     5 |
|   18 | 1002 |   5 |     3 |
|   19 | 1003 |   5 |     2 |
|   20 | 1004 |   5 |     9 |
+------+------+-----+-------+

开始连表查询

1 #连表查询
2 mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno;
+-----+---------+----------------------+-------+
| Sno | Sname   | Cname                | Grade |
+-----+---------+----------------------+-------+
|   1 | 宏志    | Linux中高级运维      |     4 |
|   2 | 王硕    | Linux中高级运维      |     3 |
|   3 | oldboy  | Linux中高级运维      |     4 |
|   4 | 脉动    | Linux中高级运维      |     1 |
|   5 | oldgirl | Linux中高级运维      |     5 |
|   1 | 宏志    | Linux高级架构师      |     3 |
|   2 | 王硕    | Linux高级架构师      |     2 |
|   3 | oldboy  | Linux高级架构师      |     4 |
|   4 | 脉动    | Linux高级架构师      |     1 |
|   5 | oldgirl | Linux高级架构师      |     3 |
|   1 | 宏志    | MySQL高级Dba         |     1 |
|   2 | 王硕    | MySQL高级Dba         |     2 |
|   3 | oldboy  | MySQL高级Dba         |     2 |
|   4 | 脉动    | MySQL高级Dba         |     2 |
|   5 | oldgirl | MySQL高级Dba         |     2 |
|   1 | 宏志    | Python运维开发       |     6 |
|   2 | 王硕    | Python运维开发       |     8 |
|   3 | oldboy  | Python运维开发       |     8 |
|   4 | 脉动    | Python运维开发       |     3 |
|   5 | oldgirl | Python运维开发       |     9 |
+-----+---------+----------------------+-------+

三、mysql优化

优化大纲：

1、硬件层面优化

1.1 数据库物理机采购：

1.2 服务器硬件配置调整

1.2.1 服务器BIOS调整：

1.2.2 阵列卡调整：

2、软件层优化

2.1 操作系统层面优化

2.1.1 操作系统及MySQL实例选择

2.1.2 文件系统层优化

2.1.3 linux内核参数优化

2.2 mysql数据库层面优化

2.2.1 my.cnf参数优化

2.2.2 关于库表的设计规范

2.2.3 SQL语句的优化

3、网站集群架构上的优化

4、流程，制度，安全优化

SQL语句优化

1、抓慢查询SQL语句方法

a、救火：紧急且重要：show full processlist;（登录数据库现场抓，连续执行2次，超过2秒）

mysql –uroot –poldboy –S /data/3306/mysql.sock –e “show full processlist;”|egrep –vi “sleep”

b、未雨绸缪：重要不紧急，记录意思分析慢查询日志。

配置参数记录慢查询语句：

long_query_time = 2 ### 超过2秒，记录到Slow Log里。

log_queries_not_using_indexes ### 没有走索引的语句，记录到Slow Log里。

log-slow-queries = /data/3306/slow.log ### Slow Log 文件。

min_examined_row_limit ### 记录结果集大于指定值的SQL语句，5.6-5.7都可以使用

2、explain语句检查索引执行情况

1 mysql> explain select * from student where Sname='oldboy'G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: index_Sname
          key: index_Sname
      key_len: 50
          ref: const
         rows: 1
        Extra: Using index condition

1 mysql> explain select SQL_NO_CACHE * from student where Sname='oldboy'G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: index_Sname
          key: index_Sname
      key_len: 50
          ref: const
         rows: 1
        Extra: Using index condition

3、对需要建索引的条件列建立索引

生产场景，大表高峰期不能建立索引，例如：300玩记录。

4、分析慢查询SQL的工具mysqlsla（每天早很发邮件给核心开发、运维、CTO）

5、按天切割慢查询日志，如果并发太大可以按小时，去重分析后发给大家。

a．mv然后flush进程。

b．cp复制，然后利用>清空。

c．定时任务 mv /data/3306/slow.log /opt/$(date +%F)_slow.log

d．mysqladmin –uroot –poldboy –S /data/3306/mysql.sock flush-logs

6、日常优化

dba、总监、开发、CTO联合解决。

索引优化

1）白名单机制 – 百度，项目开发，DBA暗语，减少上线后的慢SQL数量；

抓出慢SQL

配置my.cnf

long_query_time = 2

log_queries_not_using_indexes

log-slow-queries = /data/3306/slow.log

min_examined_row_limit

按天轮询：slow-log

2）慢查询日志分析工具 -----mysqlsla 或 pt-query-digest（推荐）；

3）每天晚上0点定时分析慢查询，发到核心开发，DBA分析，及高级运维，CTO的邮箱里；

DBA分析给出优化建议 à核心开发确认更改 à DBA线上操作处理；

4）定期使用pt-duplicate-key-checker检查并删除重复的索引；

定期使用pt-index-usage工具检查并删除使用频率很低的索引；

5）使用pt-online-schema-change来完成大表的ONLINE DDL 需求；

6）有时候mysql会使用错误的索引，对于这种情况使用USE INDEX;

7）使用explain及set profile 优化SQL语句；

8）打的复杂的SQL语句拆分成多个小的SQL语句；

子查询，JOIN连表查询，某个表4000万条记录；

9）数据库是存储数据的地方，但是不是计算数据的地方；

对于数据计算、应用处理、都要拿到前端应用解决、禁止在数据库上处理；

10）使用连接（JOIN）来代替子查询；

11）避免在整个表上使用count(*)，他可能锁住整张表；

12）多表连接查询时，把结果集小的表作为驱动表；

13）多表联接并且有排序时，排序字段必须是驱动表里的，负责排序列无法用到索引；

14）尽量去掉”IN” ”OR” “<>”

15）类似分页功能的SQL，建议先用主键关联，然后返回结果集，效率会高很多；

【开源是一种精神，分享是一种美德】

— By GoodCook

— 笔者QQ：253097001

— 欢迎大家随时来交流

—原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。