多表查询：
1、表联结 就是一种查询的机制，用来在一个select语句中关联多个表进行查询，称为联结 需要查询出所有商品以及对应的供应商信息？ 供应商名称，商品名称，商品价格 select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id order by vend_name 2、使用where进行表的联结查询外，还可以使用另外一种联结方式，join select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id; 上面这个sql就是使用了 join 的语法，进行了两个表的联结，在 on 后面 去定义了 联结的条件。联结方式：
内部联结： where， inner join(join) 自联结 : 是在一个sql中，用当前这个表，连接自己这个表进行关联查询 外部联结： left join，right join保留小数位：
使用round函数； 例如：平均值保留两位小数： select sc.sid,sname,round(avg(score),2) as avg_score from sc,student where sc.sid = student.sid group by sc.sid,sname having avg_score >= 60;sql_mode
sql_mode是MySQL数据库中的一个环境变量 定义了mysql应该支持的sql语法，数据校验等 可以通过 select @@sql_mode; 查看当前数据库使用的sql_mode 查看当前数据库的 sql_mode select @@sql_mode; 修改sql_mode 1. 在当前数据库中进行修改(服务器重启后失败) set @@sql_mode= 'xxx' 2. 修改配置文件 my.cnf sql_mode = 'xxxx' 修改完成后要重启mysql服务 brew services stop mysql@5.7值含义：
关于ONLY_FULL_GROUP_BY是否开启的建议: 1. 建议开启,符合SQL标准 2. 在mysql中有any_value(field)函数,允许返回非分组字段.(和关闭only_full_group_by模式相同)case…when
CASE...When select sc.cid, c.cname, max(sc.score) as '最高分', min(sc.score) as '最低分', round(avg(sc.score),2) as '平均分', count(sc.cid) as '选修人数', sum(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / count(sc.cid) as '及格率', sum(CASE WHEN sc.score >= 70 and sc.score < 80 THEN 1 ELSE 0 END) / count(sc.cid) as '中等率', sum(CASE WHEN sc.score >= 80 and sc.score < 90 THEN 1 ELSE 0 END) / count(sc.cid) as '优良率', sum(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / count(sc.cid) as '优秀率' from sc join course as c on sc.cid = c.cid group by sc.cid,c.cname order by '选修人数' desc,sc.cid; 18.按各科平均成绩进行排序，并显示排名， Score 重复时保留名次空缺 -- 按照各学科进行分组,计算平均成绩 select cid,round(avg(score),2) as avg_sc from sc group by cid; +------+--------+ | cid | avg_sc | +------+--------+ | 01 | 64.50 | | 02 | 72.67 | | 03 | 68.50 | +------+--------+ -- 按照各学科的平均成绩,做自联结,进行比较 mysql> select s1.* ,s2.* -> from -> (select cid,round(avg(score),2) as avg_sc from sc group by cid) as s1 -> join -> (select cid,round(avg(score),2) as avg_sc from sc group by cid) as s2; +------+--------+------+--------+ | cid | avg_sc | cid | avg_sc | +------+--------+------+--------+ | 01 | 64.50 | 01 | 64.50 | | 02 | 72.67 | 01 | 64.50 | | 03 | 68.50 | 01 | 64.50 | -- | 01 | 64.50 | 02 | 72.67 | | 02 | 72.67 | 02 | 72.67 | -- | 03 | 68.50 | 02 | 72.67 | -- | 01 | 64.50 | 03 | 68.50 | | 02 | 72.67 | 03 | 68.50 | | 03 | 68.50 | 03 | 68.50 | +------+--------+------+--------+ select s1.* ,s2.* from (select cid,round(avg(score),2) as avg_sc from sc group by cid) as s1 join (select cid,round(avg(score),2) as avg_sc from sc group by cid) as s2 on s1.avg_sc >= s2.avg_sc; +------+--------+------+--------+ | cid | avg_sc | cid | avg_sc | +------+--------+------+--------+ | 01 | 64.50 | 01 | 64.50 | 01 | 02 | 72.67 | 01 | 64.50 | 01 | 03 | 68.50 | 01 | 64.50 | 01 | 02 | 72.67 | 02 | 72.67 | 02 | 02 | 72.67 | 03 | 68.50 | 03 | 03 | 68.50 | 03 | 68.50 | 03 +------+--------+------+--------+ 6 rows in set (0.00 sec) -- 按照s2进行分组,统计s1的平均分出现的次数, select s2.cid,s2.avg_sc,count(distinct s1.avg_sc) as rank from (select cid,round(avg(score),2) as avg_sc from sc group by cid) as s1 join (select cid,round(avg(score),2) as avg_sc from sc group by cid) as s2 on s1.avg_sc >= s2.avg_sc group by s2.cid,s2.avg_sc order by rank +------+--------+------+ | cid | avg_sc | rank | +------+--------+------+ | 02 | 72.67 | 1 | | 03 | 68.50 | 2 | | 01 | 64.50 | 3 | +------+--------+------+ -- 当修改完数据之后 update sc set score = 104.0 where sid = 01 and cid = 01; insert into sc values('07','04',62.0); ---- +------+--------+------+ | cid | avg_sc | rank | +------+--------+------+ | 02 | 72.67 | 1 | | 01 | 68.50 | 2 | | 03 | 68.50 | 2 | | 04 | 62.00 | 3 | +------+--------+------+ 19.按各科平均成绩进行排序，并显示排名， Score 重复时不保留名次空缺 select cid,round(avg(score),2) as avg_sc from sc group by cid order by avg_sc desc; +------+--------+ | cid | avg_sc | +------+--------+ | 02 | 72.67 | | 03 | 68.50 | | 01 | 64.50 | +------+--------+ -- @i 是sql中定义变量的意思 select b.cid,b.avg_sc,@i := @i+1 as rank from (select @i := 0) as a, (select cid,round(avg(score),2) as avg_sc from sc group by cid order by avg_sc desc) as b; -- 同上一题,修改完数据后 +------+--------+------+ | cid | avg_sc | rank | +------+--------+------+ | 02 | 72.67 | 1 | | 01 | 68.50 | 2 | | 03 | 68.50 | 3 | | 04 | 62.00 | 4 | +------+--------+------+ 4 rows in set (0.00 sec)IF和Case when
4. 下表是每个课程class_id对应的年级(共有primary、middle、high三个),以及某种比率rate mysql> select * from mst_class; +----------+---------+------+ | class_id | grade | rate | +----------+---------+------+ | abc123 | primary | 70% | | abc123 | middle | 65% | | abc123 | high | 72% | | hjkk86 | primary | 69% | | hjkk86 | middle | 63% | | hjkk86 | high | 74% | +----------+---------+------+ select class_id, max(CASE WHEN grade = 'primary' THEN rate ELSE 0 END) as 'primary', max(CASE WHEN grade = 'middle' THEN rate ELSE 0 END) as 'middle', max(CASE WHEN grade = 'high' THEN rate ELSE 0 END) as 'high' from mst_class group by class_id; +----------+---------+--------+------+ | class_id | primary | middle | high | +----------+---------+--------+------+ | abc123 | 70% | 65% | 72% | | hjkk86 | 69% | 63% | 74% | +----------+---------+--------+------+ 2 rows in set (0.01 sec) -- 使用IF() select class_id, max(IF(grade = 'primary',rate,0)) as 'primary', max(IF(grade = 'middle',rate,0)) as 'middle', max(IF(grade = 'high',rate,0)) as 'high' from mst_class group by class_id;事务：
事务（Transaction）是由⼀系列对系统中数据进⾏访问与更新的操作所组成的⼀个程序执⾏逻辑单元。 事务的语法 事务的特性 事务并发问题 事务隔离级别 不同隔离级别的锁的情况(了解) 隐式提交(了解) 语法： 1. start transaction;/ begin; 2. commit; 使得当前的修改确认 3. rollback; 使得当前的修改被放弃 特性： 1. 原⼦性（Atomicity） 事务的原⼦性是指事务必须是⼀个原⼦的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只允许出现两种状态之⼀。 全部执⾏成功 全部执⾏失败 事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发⽣⼀样。也就是说事务是⼀个不可分割的整体，就像化学中学过的原⼦，是物质构成的基本单位。 2. ⼀致性（Consistency） 事务的⼀致性是指事务的执⾏不能破坏数据库数据的完整性和⼀致性，⼀个事务在执⾏之前和执⾏之后，数据库都必须处以⼀致性状态。⽐如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。 3. 隔离性（Isolation） 事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各⾃完整的数据空间。⼀个事务内部的操作及使⽤的数据对其它并发事务是隔离的，并发执⾏的各个事务是不能互相⼲扰的。 隔离性分4个级别，下⾯会介绍。 4. 持久性（Duration） 事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么⼀定能够将其恢复到事务成功结束后的状态。 问题： 脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。 不可重复读：同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同⼀数据，事务 B 在事务A多次读取的过程中，数据作了更新并提交，导致事务A多次读取同⼀数据时，结果 不⼀致。 幻读：重复查询的过程中，数据就发⽣了量的变化（insert， delete）。事务的隔离级别：
查看当前隔离级别： select @@tx_isolation; 设置当前会话中的事务隔离级别： set session transaction isolation level read uncommitted;删除：
1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。 drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。 2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。 注意:truncate 不能删除行数据,要删就要把表清空。 3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存 以便进行进行回滚操作。 truncate与不带where的delete ：只删除数据，而不删除表的结构（定义） 4、truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。 如果要删除表定义及其数据，请使用 drop table 语句。 5、对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。 6、执行速度，一般来说: drop> truncate > delete。 7、delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。 truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。C](这里写自定义目录标题)通过日期计算年龄：
-- SELECT em.* FROM employee AS em WHERE TIMESTAMPDIFF( YEAR, em.birthday, CURDATE()) < 40; TIMESTAMPDIFF( YEAR, em.birthday, CURDATE()) -- 计算年龄