Mysql第四章：mysql语法练习和sql优化

目录

1：mysql常用语法练习

2：查询语句（select）

3:排序语句(order by)

4：单一过滤语句(where)

5:组合条件过滤语句（where + 操作符）

6:通配符过滤语句（like）

7:字段拼接求和

8:数据处理函数

9:汇总函数(avg max min count)

10:数据分组（group by   having）

11:子查询

12：连接查询

13:视图 （视图不包含数据，修改表数据，视图也会改变）

13.1:为什么使用视图

13.2:创建视图

14:存储过程

14.1：什么是存储过程

14.2：存储过程的优缺点

14.3：案例示范

15:触发器

15.1:什么是触发器

15.2:使用案例

16:事务

17:sql优化

 17.1：explain解析

---

---

1：mysql常用语法练习

首先默认表数据如下：

2：查询语句（select）

select * from user //查询所有数据
select name,age from user //查询指定字段
select distinct address from user// 由于地址信息有深圳，河南相同。查询结果为去重复
select * from user LIMIT 3,2 //从第3行开始查询2条数据。 查询结果是id是4，5的数据

3:排序语句(order by)

select * from user ORDER BY age //默认asc是a-z 数字1-9 升序，此处显示结果是1991-2020
select * from user ORDER BY age desc//desc是z-a,9-1 降序  此处显示结果是2020-1991
select * from user ORDER BY age desc,name //首先age降序2020-1991，然后name升序a-z

4：单一过滤语句(where)

//单一条件过滤语句
select * from user where name='bb' //等于 
select * from user  where age='2013-03-02'//等于 
select * from user where name <> 'bb' // 不等于 查询名字不等于bb的数据
select * from user where name != 'bb' // 不等于 查询名字不等于bb的数据
select * from user where age >'2013-03-02' //大于 适用于日期和数值类型
select * from user where age >='2013-03-02' //大于等于 适用于日期和数值类型
select * from user where age <'2013-03-02' //小于 适用于日期和数值类型
select * from user where age <='2013-03-02' //小于等于 适用于日期和数值类型
select * from user where age BETWEEN '2013-03-02' AND '2020-03-02'//介于两者之间
select * from user where age is null //空值判断
select * from user where age is not null //非空判断

5:组合条件过滤语句（where + 操作符）

//组合条件过滤
select * from user  where name='bb' and address='河南' //and 两个条件都需套满足
select * from user where name='bb' OR address='深圳' //or 条件只需满足一个即可 
select * from user where (address='河南' or address='深圳') and name='bb'//and运算优先级高和or混用的时候要加括号
select * from user where name in ('bb','cc','李四')//in操作符，与or操作符一致
select * from user where name not in ('bb','cc','李四') //not in操作符，与in相反

6:通配符过滤语句（like）

//通配符过滤（通配符效率比较低  尽量不用在开始的位置）
select * from user where address LIKE '%南%' //包含有南的地址信息
select * from user where address like '河%1' //以河开始 1结束的地址信息

7:字段拼接求和

//拼接字段
select *,CONCAT(name,'(',address,')','0000') as name_address from user //CONCAT函数用来拼接字段name(address)0000,并且将字段别名为name_address
//查询所有的字段和sid*score 并且命名为求和
select  *,sid*score as 求和 from score

8:数据处理函数

//数据处理函数
select *,UPPER(name) as 大写 from user where address ='河南' //字段转大写
select *,LOWER(name) as 小写 from user where address ='河南' //字段转小写
select *,LEFT(name,3) from user  //字段截取3位
//日期处理函数
select * from user where age='1991-03-02'
select * from  user where DATE(age)='1991-03-02'//匹配年月日
select * from  user where YEAR(age) BETWEEN '1991' and '2010'   //匹配年 
select * from  user where MONTH(age)='03'   //匹配月
select * from  user where DAY(age)='02'   //匹配日

9:汇总函数(avg max min count)

select  * from score
select AVG(score) from score where student_id=1 //学号1的学生的平均分
select MAX(score) from score where student_id=1  //学好1的学生的最高分
select MIN(score) from score where student_id=1  //学好1的学生的最低分
select COUNT(*) from score //查询行数，所有行 跟count（1）差不多
select COUNT(1) from score //查询行数，所有行 使用到主键索引  比较快
select COUNT(score) from score //查询行数  忽略字段为null的行 该列没有索引会比较慢

10:数据分组（group by   having）

//group by数据分组  having过滤分组
 select student_id,AVG(score),MAX(score),MIN(score) from score GROUP BY student_id HAVING    AVG(score)>60

11:子查询

 
 //子查询,查询结果是其他查询的条件,查询学生表的学生id,根据学生id查询分数
 select * from score where student_id in (select sid from students)

12：连接查询

select * from a    JOIN b on a.id=b.a_id //内链接（JOIN） 交集

select * from a  INNER  JOIN b on a.id=b.a_id //内链接（inner join） 交集

select * from a LEFT  JOIN b on a.id=b.a_id  //左链接  左边查询全部数据，关联右边

select * from a RIGHT   JOIN b on a.id=b.a_id//右连接  右边查询全部数据，关联左边

select id from a 
UNION 
select address from b
//UNION求并集 除虫重复

select id from a 
UNION all
select address from b
//UNION all求并集 不去重复

13:视图 （视图不包含数据，修改表数据，视图也会改变）

13.1:为什么使用视图

1:重用复杂的sql，便于运维

2:使用表的部分组成字段而不是全部的sql，使用表的一部分

3:保护数据，通过视图访问部分数据，而不是直接查询所有的表。

写了一个很复杂的sql查询出来了结果，这些结果是很多各表

13.2:创建视图

-- 查询表数据
select * from students

--  选取单表数据或者多表联合查询的数据 创建视图，一般为多表复杂sql创建视图 用来复用sql
CREATE VIEW students_view as
select sid,sname,ssex from students

-- 查询视图 视图不能创建索引
select * from students_view
-- 带条件的视图查询 mysql会把查询封装到 创建视图的sql的where语句中  也就是select sid,sname,ssex from students where sid=1
select  * from students_view where sid=1
-- 视图也有order by 当创建视图的sql有order by的时候，视图order by 会被覆盖
select  * from students_view ORDER BY sid 

14:存储过程

14.1：什么是存储过程

存储过程是sql的封装，将复杂的sql封装成一个方法，可以有输入和输出参数，给外部调用。实现较为复杂的逻辑。

14.2：存储过程的优缺点

优点：

1:性能高：将不同的sql封装，比单条sql执行效率高，连接多次数据库。只需连接数据库一次，存储过程在创建的时候直接编译，而sql语句每次使用都要编译，提高执行效率

2:简单：复杂的sql封装，只管调用。外部简单，类似于java的方法封装一样

缺点：

1:对于专业技能姚要求高，简单的sql不必要封装成为存储过程，复杂的才需要，所以对于技术要求高

2:移植能力较差，维护麻烦

14.3：案例示范

--1:创建存储过程PROCEDURE1
CREATE PROCEDURE PROCEDURE1()
BEGIN
select * FROM students;
END;


--2:创建存储过程PROCEDURE2 
--这里先解释一下delimiter //是什么意思
--mysql客户端中分隔符默认是分号（；），所以如果不指定一个特殊的分隔符，可能会编译失败
--上面语句将分隔符改为//，直到遇到下一个//才表示语句结束，这样可以保证创建语句完整。

delimiter // 
CREATE PROCEDURE PROCEDURE2()
BEGIN
select * FROM students;
END//
delimiter ;

--3:调用存储过程procedure1
CALL procedure1
--4:调用存储过程procedure2
CALL procedure2



--5:创建含有参数和返回值的存储过程

CREATE PROCEDURE PROCEDURE2(

OUT a DECIMAL(8,2),    #输出参数 （out 参数名字 参数类型）
OUT b DECIMAL(8,2),   #输出参数 （out 参数名字 参数类型）
OUT c DECIMAL(8,2),   #输出参数 （out 参数名字 参数类型）
in  d INT             #输入参数 （in 参数名字 参数类型）
)
BEGIN 

select avg(score) INTO a  #into 绑定输出参数 
from score where student_id=d  #直接饮用输入参数d
GROUP BY student_id;
select max(score) INTO b  #into 绑定输出参数 
from score where student_id=d   #直接饮用输入参数d
GROUP BY student_id;
select min(score) INTO c  #into 绑定输出参数 
from score where student_id=d  #直接饮用输入参数d
GROUP BY student_id;

END;



//调用含有输入输出参数的存储过程
call PROCEDURE2(@a,@b,@c,3);
//查询结果集
SELECT @a,@b,@c

--6:删除存储过程
drop PROCEDURE PROCEDURE2

15:触发器

15.1:什么是触发器

触发器主要是来监控表的，当表中的数据发生变化的时候，update，delete，insert的时候，自动触发触发器。

触发器的特点：

1:触发器名字唯一（每个表最多有6个触发器，在增删改*2=6）

2:触发器是监控指定的表，跟表关联(视图和临时表不支持触发器)

3:触发器监控update，delete，insert操作

4:触发器分为在操作之前和之后执行

15.2:使用案例

#触发器t2   监控a表，在a表删除数据之前，在b表添加两条数据
CREATE TRIGGER t2 AFTER DELETE on a
 FOR EACH ROW
 BEGIN
INSERT INTO b (address,a_id) VALUES ('删除数据触发器1',111);
INSERT INTO b (address,a_id) VALUES ('删除数据触发器2',111);

END

16:事务

START TRANSACTION;#开启事务
select * from a;
DELETE from a where id=1;
select * from a;
ROLLBACK;#回滚事务


START TRANSACTION ;#开启事务
select * from a;
DELETE from a where id=1;
select * from a;
COMMIT; #提交事务

17:sql优化

创建成绩表

CREATE TABLE `score` (
  `sid` int(10) NOT NULL AUTO_INCREMENT,
  `student_id` int(10) DEFAULT NULL COMMENT '学生id',
  `course_id` int(10) DEFAULT NULL COMMENT '课程id',
  `score` decimal(10,2) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`sid`),
  KEY `student_id_index` (`student_id`) USING BTREE COMMENT '学生id索引',
  KEY `course_id_index` (`course_id`) USING BTREE COMMENT '课程id索引',
  KEY `score_index` (`score`) USING BTREE COMMENT '成绩索引'
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据如下：

首先在Mysql第三章：存储引擎（MyISAM和Innodb） 第三章中知道了各种锁、mvcc和索引的数据结构 然后来学习sql优化

 17.1：explain解析

列名	描述	重点分析
id	id标识符	数值不同，大的值优先，比如有1、2。2的值优先查询
select_type	查询类型	SIMPLE:简单查询 PRIMARY:外层查询 等等
table	查询的表名字	查询的表的名字，有的时候不是如上图<derived2>是衍生的意思
partitions	分区
type	查询类型	对表的查询方式： 常用的类型有： ALL（全表）、index（索引）、range（范围）、 ref（非唯一索引）、eq_ref（唯一索引）、const（主键）、system（主键）、NULL（从左到右，性能从差到好）
possible_key	可能使用到的索引	可以有好几个
key	实际使用到的索引	用到的索引
key_len	索引的长度	key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
ref	引用	那些列被引用，或者是常量
rows	函数	mysql估算的找到所需的记录所需要读取的行数，不准确
filtered	过滤	存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)
Extra	额外的描述

1：id（选择标识符）

id相同则从上到下

id不同，大的优先

举例说明：

2：select_type（查询类型）

示查询中每个select子句的类型

(1) SIMPLE(简单的：SELECT，不使用UNION或子查询等)

(2) PRIMARY(主要的：子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION(联合：UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED(派生表： 的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

3：table（查询表名）

有的时候是表名，有的时候不是如上图<derived2>是衍生的意思

4：type（查询类型）

此字段表示从表中查询到目标行需要的类型

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行（性能最差）

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，索引范围扫描，常用于<、<=、>、>=、between等操作

ref:使用非唯一索引,或者join的查询

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: const/system出现在根据主键primary key或者 唯一索引 unique index 进行的查询

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

5：possible_key（可能使用的索引key）

6：key（使用的索引）

7：key_len（索引的字节数）

（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

8：ref（）

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

9：rows（查询行数，不准确）

mysql估算的找到所需的记录所需要读取的行数

10：filtered（存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)）

11：Extra（额外描述）

mysql解释查询的详细信息

Using Index	表示索引覆盖，不会回表查询
Using Where	表示进行了回表查询
Using Index Condition	表示进行了ICP优化
Using Flesort	表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果
Using temporary	使用临时文件

文件排序

 

Using temporary：MySQL需要使用临时表来存储结果集，常见于排序和分组查询。当缓冲区满了之后，就会把数据存入临时文件。

使用filesort和temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。