MySQL运维实战教程（3）如何使用SQL语句进行数据的增删改查

一、SQL语言介绍

SQL是结构化查询语言的英文单词缩写，用于存取、查询、更新、管理数据库系统中的数据。SQL是关系型数据库应用语言的标准，从推出以来先后有SQL 86\89\92\99等标准，其中最重要的是至今仍然在遵循的SQL 92和SQL 99两个标准。

SQL语言根据功能可以分为以下类型：

· 数据库定义语言（DDL）：对数据库、表、索引进行管理的语句，如CREATE、DROP、ALTER等

· 数据库操纵语言（DML）：对数据进行增删改查的语句，如INSERT、UPDATE、DELETE、SELECT（也有把SELECT定义为DQL的）

· 数据库控制语言（DCL）：控制用户权限的语句，如GRANT、REVOKE、COMMIT、ROLLBACK等

· 数据查询语言（DQL）：对数据进行查询的语句，即SELECT（SELECT通常也可以归类为DML）

二、SQL的执行过程

客户端连接MySQL并提交SQL语句时，需要经过Server层进行一系列的处理才能最终执行，包括连接器、查询缓存、分析器、优化器、执行器，这些组件的作用如下：

· 连接器

客户端做任何操作前的第一步必须是先连接到数据库，所以这时候先经过连接器模块，而这个连接器通常就是mysql客户端了。连接成功后如果客户端没有后续操作，则连接处于Sleep空闲状态。执行show processlist命令时Command 列显示为Sleep就表示该客户端为空闲连接。

· 查询缓存

MySQL收到客户端发起的查询请求后会先判断是否在查询缓存中有执行过这条语句，之前执行过的语句及其结果会缓存在内存中。如果能够直接在缓存中找到就会被直接返回给客户端，提升效率。如果不在查询缓存中才会开始继续后面的执行阶段。执行完成后的结果也会被存入查询缓存中。

但是通常都不会使用查询缓存，因为查询缓存的失效非常频繁，只要有对一个表进行任何更新，这个表上所有的查询缓存都会被清空，这样对于更新压力大的数据库来说查询缓存的命中率会非常低。除非业务有使用静态表，很长时间才会更新一次（比如系统配置表）。从MySQL 8.0开始也取消了查询缓存功能。

· 解析器

该步骤主要SQL是否存在语法问题，如果语法错误就会出现You have an error in your SQL syntax的提示，一般语法错误会提示第一个出现错误的位置，所以关注的是紧接“use near”的内容。

· 优化器

当表里面有多个索引的时候，由优化器决定使用哪个索引；或者在一个语句使用join进行多表关联时决定各个表的连接顺序。优化器阶段完成后就是最后的执行器阶段了。

· 执行器

真正开始执行SQL

三、MySQL客户端的使用

当完成MySQL部署后，可以使用mysql命令行工具或其他第三方工具进行管理（比如SQLyog、Navicat等，这里不对第三方工具的使用做介绍）

1、mysql客户端工具常用选项

-u：指定登录MySQL的用户名，不写的话默认是使用root用户

-p：小写，用于填写登录MySQL用户的密码，如果无密码的话可不加该选项，该选项后面跟的密码不能用空格来隔开，如果在该选项后加了空格，系统会认为空格后面的字符是数据库的库名

-h：指定MySQL服务器的IP，连接本地服务器的话可不加该选项

-S：指定socket文件路径，比如在my.cnf中的[mysqld]模块中定义了socket路径，但是使用mysql客户端和mariadb客户端连接数据库时，检查的socket文件路径不一样，这样有可能导致无法连接的情况，用该选项可以手动指定

-e：直接执行后面指定的命令然后退出客户端，用于非交互式操作

-P：大写，指定MySQL端口号，不加该选项的话默认连接3306端口

-N：查询结果不显示列名

-B：使用TAB键作为默认分隔符，否则输出会自动添加"|" 符号作为分隔符，如果使用EXCEL导入的时候就会不方便

-H：查询结果以HTML形式输出

-X：查询结果以XML形式输出

2、mysql客户端使用示例

mysql -uroot -p #在交互窗口输入用户密码
mysql -u root -p123456 #直接指定了root用户密码，不需要再通过交互窗口输入，注意-p后面不要有空格
mysql -uroot -p123456 -h192.168.0.100 -P3307
mysql -uroot -p123456 -h192.168.0.100 -P3307 -e "create database test;"

四、MySQL DDL语句介绍

1、CREATE语句

使用该语句可以创建数据库、表和索引

-- 创建数据库
CREATE DATABASE school;

-- 使用数据库
USE school;

-- 创建学生信息表
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    gender ENUM('Male', 'Female'),
    enrollment_date DATE
);

2、ALTER语句

对数据库结构进行操作

-- 添加新列，默认添加在表的最后一列
ALTER TABLE students ADD COLUMN email VARCHAR(100);

-- 修改列数据类型
ALTER TABLE students MODIFY COLUMN age TINYINT;

-- 删除一列
ALTER TABLE students DROP COLUMN email;

-- 重命名列，如将test_col修改为new_test_col
ALTER TABLE test_table CHANGE old_col new_col varchar(10)

-- 重命名表
ALTER TABLE old_table RENAME TO new_table

3、DROP语句

删除数据库或表

-- 删除表
DROP TABLE students;

-- 删除数据库
DROP DATABASE school;

4、TRUNCATE语句

清空表内所有数据

TRUNCATE TABLE students;

五、MySQL DML语句介绍

DML语句是在数据库日常开发工作中使用最多的语句，包含了INSERT、DELETE、UPDATE、SELECT四种操作，也就是常说的增删改查操作

1、INSERT语句

实现MySQL数据的插入

#如果插入数据时没有指明需要添加的字段，则按照表结构字段先后顺序进行添加
INSERT INTO table_name VALUES (1,'tanglu',1986-12-11,3800)

#如果插入数据时指明字段，则按照指明的字段进行添加
INSERT INTO table_name (id,name,age) VALUES （11,'tom',20）

#一次插入多行数据
INSERT INTO table_name (id,name,age) VALUES （11,'tom',20）,（15,'jerry',25）

#将其它表的查询结果插入到指定表中用于复制数据，两张表字段需要一致
INSERT INTO table_name1 (id,name,age) SELECT id,name,age FROM table_name2 [where age>20];

#通过函数插入数据
INSERT INTO table2 VALUES（1,'tanglu',NOW()）  

#如果字段设有默认值，可以直接插入默认值
INSERT INTO table2 VALUES（1,'tanglu',default）

2、DELETE语句

相比drop table和truncate两种物理清除方式，delete是逻辑方式的逐行删除，磁盘空间和自增的值不会立即释放

# DELETE FROM 表名称 WHERE 列=值;
DELETE FROM student WHERE age=10； #删除student表中age列值为10的数据 
DELETE FROM student ；#删除student表中所有数据 ，危险操作

3、UPDATE语句

对MySQL数据进行更新

#UPDATE table1 SET 字段1=值1,[字段2=值2] WHERE 条件判断
UPDATE student SET age=21 WHERE age=10;  #where条件一定不能忘，不然就全表更新了
UPDATE test_table SET name='tanglu',age=18 WHERE name='linuxe'  #一次更新多个字段的值

六、MySQL DCL语句介绍

DCL语句用于控制数据库中用户的访问权限，包含授权和撤销操作。在生产环境中通常不会把数据库root账号用于业务使用，这也是为什么安装好MySQL后root用户只能本地登录。正确的做法是为每个应用单独创建并分配合理的权限，通常仅分配DML权限。各权限和相关作用解释见下表

1、用户权限的查看

在MySQL中一个完整的用户名是"'username'@'source'"的形式，当发现登录用户的权限与期望不符时通常都是登录用户正在通过其他账号进行身份验证，比如'root'@'localhost' 和'root'@'127.0.0.1'实际是2个用户，同理理'tanglu'@'192.168.1.%'和'tanglu'@'192.168.1.100'也是2个用户

#通过查看mysql库user表可以查看数据库上所有用户及其权限
mysql > select * from mysql.user  

mysql > show grants #查看当前用户权限
mysql > show grants for 'root'@'%'   #查看指定用户权限
mysql > show grants for current_user()  #查看当前用户权限
mysql > SELECT CURRENT_USER();  #查看当前登录用户

2、创建MySQL用户

使用CREATE USER命令创建用户，注意用户名和主机都需要写上，如果仅有用户名的话则主机名自动使用%，代表所有主机都有权限。创建用户后由于还未进行任何的权限设置，此时用户只能登陆数据库但无法进行任何操作

mysql > create user tanglu@localhost identified by '123456'; 
mysql > create user tanglu@'%' identified by '123456'; 

3、对MySQL用户授权

使用GRANT命令对用户进行授权，如果对同一用户进行多次grant操作，这些权限属于增加而不是覆盖。使用grant命令虽然可以创建用户并同时完成授权，但并不规范，在后续版本会被弃用。正确的做法是先创建用户再进行授权。授权可以细化到对库、表、列，支持通配符。需要注意的是MySQL会把localhost和127.0.0.1当做不同的地址，所以要对本机授权的话这两个地址都要写上

mysql > grant all privileges on DATABASE.TABLE to 'user'@'host' 'identified by 'PASSWORD';
mysql > grant all privileges on *.* to 'tanglu'@'%' 'identified by '123456' with grant option; #with grant option代表该用户可以将自己权限授予其它用户

#创建用户并完成授权的标准方法
mysql > CREATE USER 'tanglu'@'172.27.%' IDENTIFIED BY '123456';
mysql > GRANT SELECT ON *.* TO 'tanglu'@'172.27.%';

4、用户权限变更语句

如果使用的是GRANT、REVOKE等账户管理语句，MySQL会立即刷新权限表并加载到内存中生效。如果通过DML语句修改了用户权限表（mysql.user），则需要执行FLUSH PRIVILEGES刷新授权

#使用mysqladmin修改密码
mysqladmin -uroot -p'OLD_PASSWORD' password 'NEW_PASSWORD'

#使用alter语句修改密码
alter user 'tanglu'@'192.168.1.100' identified by 'new_password'

#使用set password修改密码
mysql > set password=password('NEW_PASSWORD')  #普通用户也可以这样修改自己的密码
mysql > set password for 'USER'@'localhost'=password('NEW_PASSWORD')

5、使用revoke命令取消用户权限。通常先show grants看下用户的权限，然后把权限复制过来撤销。

revoke all privileges from 'USERNAME'@'%';

6、使用drop user命令删除用户

mysql > drop user 'tanglu'@'localhost';

七、MySQL DQL语句介绍

DQL语句用于查询指定列的数据，也可以实现对查询结果进行运算等操作，由于 SELECT 也可以单独分为DQL，且使用最为复杂，通常要结合许多子句和函数，所以整体查询还会受到书写顺序的影响。MySQL 常用子句有 WHERE、GROUP BY 、HAVING、ORDER BY、LIMIT等，如果一个SQL同时存在多个子句，则SQL语句的执行顺序为FROM --> JOIN...ON --> WHERE --> GROUP BY --> HAVING --> SELECT --> DISTINCT --> ORDER BY --> LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num  #顺序5
FROM player JOIN team ON player.team_id = team.team_id  #顺序1
WHERE height > 1.80                     #顺序2
GROUP BY player.team_id                    #顺序3
HAVING num > 2                        #顺序4
ORDER BY num DESC                       #顺序6
LIMIT 2                             #顺序7

1、SELECT语句示例

# 查询表中所有列的所有数据
SELECT * FROM table1

# 查询指定列的数据，列的顺序可以自定义
SELCET name,age FROM student;

# 查询时使用AS关键词为列起别名，AS关键词通常也可以忽略不写
# 别名可以使用中文，如果别名存在空格等符号可以使用双引号
#  由于语句执行顺序，别名不能在where中使用，只能在order by中使用
SELECT student_id AS id,name FROM student;
SELECT student_id id,name FROM student;
SELECT student_id "student id",name FROM student;
SELECT student_id "学号",name FROM student;

# 对查询数据进行运算，如下对age列进行了一个运算，并另存为age2字段
SELECT last_name,first_name,age,age + 10 AS age2 FROM student;

# 查询常数，如果SELECT查询了非表中的字段，那么就会用查询的内容作为列名，每一列也会自动填充该内容进行输出
# 如下查询结果就会出现tanglu列，并且每一行的内容也都是tanglu
SELECT 'tanglu',id,name from student;

# 查询的时候可以为表起别名，一旦为表设置了别名就必须使用别名进行查询。比如为employees表设置别名为emp，为department起别名为dept
SELECT emp.employee_id,dept.department_id * FROM employees emp,department dept 
WHERE emp.department_id = dept.department+id

2、SELECT 子句介绍

· WHERE

用于条件查询，可以对语句所要处理的数据进行过滤，除了数值类型，还支持对时间类型数据进行过滤。过滤条件有=、<=>（安全等于，支持和NULL进行对比，如果使用=和NULL进行比较的话结果永远都是NULL）、>、<、>=、<=、!=、AND、OR。如有多个过滤条件时需要注意顺序，AND优先于OR，可以通过括号来改变优先级

SELECT * FROM family WHERE name = 'tanglu';  
SELECT * FROM student WHERE age>10 AND height > 100;

· LIKE、NOT LIKE子句：该子句用于实现字符串的模糊匹配查询（非字符串类型不行），通常还需要结合通配符过滤，%代表任意多个任意字符，_代表任意单个字符。需要注意如果%出现在匹配条件的最前面，不会走索引

SELECT * FROM family WHERE name LIKE '%lu%'; 
SELECT * FROM student WHERE name LIKE 'tang%' ;

· IN、NOT IN子句：该子句用于集合查询，如果某个字段的where条件有很多个，用IN语句以集合的形式过滤，书写更简便、直观，同样也支持and、or等逻辑判断符

SELECT salary FROM employee WHERE salary IN (4000,5000,6000);
SELECT * FROM student_table WHERE age NOT IN (18,19,20);

· BETWEEN...AND、NOT BETWEEN ...AND子句：该子句实现带范围的条件过滤，可以理解为>=与<=的组合，常用于过滤日期或者数字

SELECT salary FROM employee WHERE salary BETWEEN 5000 AND 10000;

· IS NULL、IS NOT NULL：判断字段是否为NULL，NULL代表未知值，并不是空字符串。如果使用=来进行判断，那么NULL与其他值进行计算式结果一定为NULL，所以是不准确的，需要使用IS NULL或者<=>

SELECT * FROM student WHERE price IS NULL;
SELECT * FROM student WHERE price IS NOT NULL;

· REGEXP：使用正则表达式进行匹配

SELECT  * FROM student WHERE name REGEXP '^tang'

· DISTINCT

去除查询结果中的重复行，类似于Linux系统的uniq命令。该字句需要放在 SELECT 子句的最前面，作用于所有选定的列，而不是单独作用于某一列

SELECT distinct name FROM zhangsan; 

# 如果想要所有不同的 id 和 name 组合，使用 DISTINCT 可以确保整个行的唯一性
select DISTINCT name,age from student_table;  #对name和age列的查询结果去重

# 如果只希望 name 列的值是唯一的，可以使用 GROUP BY，但这需要在聚合查询中应用：
SELECT name, MAX(age) FROM student GROUP BY name;

· ORDER BY

对查询结果按照指定字段进行升序（ASC）或者降序（DESC）排序，如果指定了多个字段进行排序则是按照书写的顺序排序，每个字段也需要单独指定是采用升序还是降序进行操作。如果没有使用ORDER BY子句去指定排序，MySQL也会默认使用主键列进行排序，如果存在相同数据会随机排序，由于排序会带来较大的性能消耗，所以尽量按照联合索引的字段顺序去进行排序，这样就可以利用联合索引树里的数据有序性。

# 语法示例
# SELECT column1,column2,... FROM table_name WHERE ... ORDER BY column1 ASC/DESC;

select * from student_table order by age asc;   #根据age列的数据进行asc正序排序,asc为默认值也可以不写
select * from student_table order by age desc;  #根据age列的数据进行倒序排序
select * from student_table order by age desc,price desc;  #多个字段排序时用逗号分割，这里根据age列排序后，如果该列有重复值再根据price列进行排序

· LIMIT

指定查询结果所需返回的数据行数，常和ORDER BY语句结合使用实现分页查询。LIMIT后面可以跟1个或者2个数字，如果只有一个数字，那么代表了要提取的数量；如果有两个数字，第一个数字代表从多少行之后开始取，默认0代表从第1行开始取，第二个数字代表取回的行数，例如LIMIT 3,10代表从第4行开始取10条数据（前面数值越大查询效率越低，因为这个代表了要丢弃很多数据才能进行分页，比如 LIMIT 1000000,10这样的语句，数据库并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次）；从8.0开始LIMIT支持使用OFFSET表示偏移量的写法，当使用OFFSET来表示偏移量后，LIMIT后面只能有一个数字来表示要提取的数量，offset后面的数字则表示第几行。对于主键自增的表在进行分页优化时可以考虑把limit转换为位置查询

select * from test LIMIT 3   #提取前三条数据
select * from test LIMIT 2,3;  #从第二行之后开始取3条数据，即3，4，5行
select * from test LIMIT 3 offset 2;  #从第二行之后开始取3条数据，即3，4，5行

select * from student_table order by age desc limit 100; 
select * from student_table order by age desc limit 100,10;  #从100行开始取10行数据
SELECT id,name from employees limit 0,20;         #每页显示20条数据，此时显示第1页
SELECT id,name from employees limit 20,20;        #每页显示20条数据，此时显示第2页

SELECT * FROM student LIMIT 2000000,10；   #耗费大量资源
SEKECT * FROM student WHERE id>2000000 LIMIT 10;  #利用主键将LIMIT转换为位置查询

LIMIT语句常见优化思路

#优化前，在 type、 name、 create_time 字段上加组合索引，但是性能依然存在问题
SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog'  ORDER  BY create_time  LIMIT  10000000, 10;


#优化后，将上一页的最大值当成参数作为查询条件，这样可以让查询时间基本固定，不会随着数据量的增长而发生变化
SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog' AND create_time > '2017-03-16 14:00:00' ORDER BY create_time limit 10;

· GROUP BY

按照指定的字段对数据进行分组，实现统计的功能。如GROUP BY age就是将age字段进行分组，相同年龄的数据会分到一个组里，有多少个不同年龄就分多少组；又或者统计“每个部门的平均工资”“每个用户总共购买了多少次商品”“一张学生表中有多少个男生”。GROUP BY子句必须配合AVG()、MAX()、COUNT()、SUM() 等聚合函数使用。GROUP BY中声明的字段可以不出现在SELECT子句中（语法没问题，但是不易理解），但是SELECT子句包含的非聚合函数字段则必须声明在GROUP BY中。如果SELECT查询的字段非GROUP BY中声明，在Oracle下会报错，MySQL不报错但是是错误的结果。

#统计每个部门的平均工资，先用AVG计算出平均工资，然后用部门id列进行分组统计
SELECT depratment_id,AVG(salary) FROM employees GROUP BY department_id

#统计每个大陆人口总和，对人口字段进行聚合，然后用大陆字段分组显示，得到各大陆人口数
SELECT continent,sum(population) FROM world GROUP BY continent

#统计各个job_id下的员工人数
SELECT job_id,COUNT(*) FROM employees GROUP BY job_id

#对多列数据进行分组统计，最终实现的效果就是将同样年龄的人分组后再对体重分组
SELECT age,height FROM student GROUP BY age,height;

#查询每个主管手下员工的最低工资，其中最低工资不得低于6000，没有主管的员工不用查询
SELECT manager_id,MIN(salary) FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) >=6000;

#查询所有部门的名字、location_id、员工的数量和平均工资，注意count函数后使用了具体的字段，是为了避免空值被统计
SELECT department_name,location_id,count(epmloyee_id),AVG(salary) FROM departments LEFT JOIN employees ON department.department_id = employees.department_id GROUP BY department_name,location_id

· HAVING

HAVING 用于对聚合函数处理过的数据进行过滤，通常在 GROUP BY 后使用。由于HAVING在SQL语句中的执行顺序靠后，如果过滤条件不包含聚合函数，那么优先使用WHERE提前滤掉不需要的数据，后面的GROUP BY操作涉及到的数据更少，性能更好

select age,count(*) FROM student group by age having age >18 ;

#错误写法
SELECT department_id,MAX(salary) FROM employees 
WHERE MAX(salary) >10000 
GROUP BY department_id

#正确写法
SELECT department_id,MAX(salary) FROM employees 
GROUP BY department_id 
HAVING MAX(salary) >10000

· AS

在对某列数据使用函数或者表达式进行计算后，为了让最终结果易读，通常会使用AS子句定义一个别名。别名也受语句执行顺序的影响，定义好的别名可以用于ORDER BY子句，但是不能用于WHERE子句，详情见最开头的SQL语句执行顺序

#列出所有电影的ID、名字和销售总额，销售总额以百万为单位
SELECT id,title,(Domestic_sales+International_sales)/1000000 AS totle_sales FROM movies JOIN Boxoffice ON movies.id=Boxoffice.Movie_id	

#将concat拼接出来的字段命名为company_info
SELECT CONCAT(company,address) AS company_info FROM student

#AS可以省略不写，比如这里将address定义了别名addr，CONCAT后的两个字段定义了别名company_info   
SELECT address addr,concat(company,address) company_info FROM student;

· UNION、UNION ALL

两者都是对多个SELECT的结果进行合并，在合并时需要保证字段一致，最终实现了满外连接的效果。二者区别在于UNION会对结果集去重，而UNION ALL不会（但是效率会提升很多，所以一般推荐使用UNION ALL）。如果明确知道合并数据后的结果不存在重复数据，或者不需要去除重复数据，尽量使用UNION ALL语句

select * FROM world.city where countrycode='CHN' 
union all 
select * FROM world.city where countrycode='USA'