【MySQL】MySQL数据库的进阶使用

一、MySQL基本查询

1.对表内容进行Create(增加)

1.1 insert语句的使用

1. 之前我们所学的都是DDL语句，接下来所学的才是真正的DML语句。 插入数据的sql语句就是insert into table_name (column1, column2, ……) values (data1, data2, ……)，values左边的括号不加时，默认代表对表的所有列进行插入，不忽略任何一列，加上括号时，可以自己指定某些列进行插入，但值得注意的是如果某些列没有default约束，你还将其忽略进行数据插入的话，则插入数据的操作一定会失败。values右边的括号个数表示向表中插入几行的数据，括号中用逗号分隔开来的数据分别一 一对应表中的列字段。

在这里插入图片描述

2. 在插入数据时，如果遇到主键冲突或唯一键冲突，可能导致数据插入失败，此时有一种同步更新操作的语法可以保证，当数据插入失败时，可以更新为新的数据进行插入。 其实就是在原有的insert sql语句后面加上on duplicate key update column1=data1, column2=data2……即可。 当update的数据和表中冲突时，如果冲突的数据和要更新的数据相同，则表中原有的冲突数据并不会发生什么变化，sql语句的返回结果也就是0 row affected。如果冲突的数据和要更新的数据不同，则会先删除表中原有的冲突数据，然后在新插入要更新的数据，sql语句返回的结果就是2 row affected;如果update的数据和表中不冲突的话，则该语句的作用和普通的insert语句一样，sql语句的返回结果就是1 row affected; MySQL有一个函数叫做row _count()，用于统计最近一次的sql操作所影响的行数。

在这里插入图片描述

3. 除了insert冲突时，我们使用update更新这样的语法外，我们也可以直接使用replace into进行表中数据的替换，当表中有冲突数据的时候，则删除冲突数据然后再插入，没有冲突数据时，则直接插入。

在这里插入图片描述

1.2 插入查询结果（删除表中的重复记录）

1. insert除了直接插入数据外，还支持插入select查询到的结果，如果要删除表中重复的记录，我们想要让这个操作是原子的。 那我们就可以新建一个no_duplicate_table，然后查询duplicate_table中不重复的数据，也就是在select查询时，添加关键字distinct，将查询到的结果插入到表no_duplicate_table中，使用的sql语句为：insert into no_duplicate_table (id, name) select distinct * from duplicate_table;其中的(id, name)可加可不加，不加时，则默认是全列插入。 插入后no_duplicate_table中的值便是不重复的数据了，此时我们只要对表进行rename即可，这样就相当于删除表中的重复记录。rename的过程一定是原子的。

在这里插入图片描述

2.对表内容进行Retrieve(读取)

1. retrieve是取回的意思，在MySQL中可以认为是读取操作，MySQL中查询数据的操作，也就是R操作最为频繁，同样也是需要重点学习的操作，在R操作里面，最典型的SQL语句就是select语句，用于查询表中的数据。 下面是select的语法

在这里插入图片描述

2. 实际中非常不建议使用全列查询，因为这需要显示表的所有数据，而部分的数据可能此时并不在内存中，则mysqld服务还需要磁盘IO来加载表的剩余数据，降低MySQL查询的性能，同时全列查询还无法使用索引来优化查询过程，因为索引只能提升部分数据的查询，查询的数据一旦涉及到索引中没有包含的列字段，则此时就无法使用B+索引结构来优化查询的速度，数据库系统只能遍历整个表的所有行来进行查找，这会大大降低查询速度。 除此之外，实际公司使用的MySQL数据库，存储的数据最少也几百万条记录打底，一旦全列查询，则查询结果会疯狂刷屏到显示器上，看也没法看，而且还有可能导致mysqld服务卡死，所以平常我们自己敲一些简单的数据库sql，可以用一下，等进入公司之后，一定不要用全列查询。 指定列查询的字段顺序是可以自定义的，不用和表中的列字段顺序保持一致。

在这里插入图片描述

3. 查询的字段也可以是表达式，表达式里面也可以混合列字段进行查询

在这里插入图片描述

还可以通过as来为查询的字段指定别名，as是可以省略的，我这里加上了。 如果select查询的结果有重复数据，则还可以在select后面加上distinct关键字，进行查询结果的去重。 如果select后面有多列，则会进行多列字段的去重处理，也就是当多列字段同时重复时，才会去重。如果只有一列，那就只针对该列进行去重。

在这里插入图片描述

在这里插入图片描述

4. where子句是select在查询时常用的一个筛选条件，当where条件判断为真时，select在会将查询结果显示出来，下面我们通过多个使用案例，来熟悉where条件的使用以及逻辑运算符的使用。

在这里插入图片描述

MySQL中判断是否相等一般用=，还有一个比较鸡肋的东西就是<=>，=是NULL不安全的，<=>是NULL安全的，其实这里的安全不安全指的就是NULL在作为判断条件时候的判断结果，一般判断是否等于null，我们都使用的是is null和is not null，大部分情况下都不会讲null来作为判断条件，所以<=>的使用频率还是比较低的。判断两个值是否相当的=使用频率比较高。

在这里插入图片描述

英语不及格的同学及英语成绩 ( < 60 )

在这里插入图片描述

语文成绩在 [80, 90] 分的同学及语文成绩

between and逻辑判断条件正好也是闭区间，所以上下两种查询sql语句的作用是等价的

在这里插入图片描述

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

在这里插入图片描述

姓孙的同学 及 孙某同学

在这里插入图片描述

语文成绩好于英语成绩的同学

在这里插入图片描述

总分在 200 分以下的同学

注意：where条件中可以使用表达式，但where条件中不能出现别名！

在这里插入图片描述

语文成绩 > 80 并且不姓孙的同学

在这里插入图片描述

孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

在这里插入图片描述

5. order by子句可以将select查询出来的结果进行排序显示。

同学及数学成绩，按数学成绩升序显示

如果order by子句不加asc或desc，则默认是asc升序排序

在这里插入图片描述

同学及 qq 号，按 qq 号排序显示

在这里插入图片描述

查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

多字段进行排序时，排序的优先级随书写顺序，优先级依次向后降低

在这里插入图片描述

查询同学及总分，由高到低

因为关键字执行的顺序是：from，select，order by，所以总分这个别名可以出现在order by子句中。同时order by子句中也可以出现表达式。

在这里插入图片描述

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

在这里插入图片描述

6. 对未知表进行查询时，最好进行分页显示，这样可以避免表中数据过大时，导致查询全表数据致使数据库卡死。

推荐使用limit+offset的方式来分页显示数据，因为这样的语义更加明确一些，如果你不想用，也可以使用limit 数字,数字的方式来分页显示数据。

在这里插入图片描述

左右两种对表数据进行分页显示的效果是一样的，推荐使用右边这种

在这里插入图片描述

将总成绩大于200分的同学先进行降序排序，然后再进行分页显示，每页2条数据

在这里插入图片描述

3.对表内容进行Update(更新)

1. update用于更改表中某一行或者多行的数据，值得注意的是，在使用update对表中数据进行更新的时候，如果不跟上where子句进行数据筛选的话，则update会对表中所有的行进行某一列字段值的更新，因为where子句可以筛选出符号条件的行，对于符合条件的行进行update数据更新，这才是合理的做法。 update时，后面也可以跟where子句，order by子句，limit子句，这些子句的作用无非就是对数据作行级别的筛选，一般limit会和order by子句配合使用，因为直接使用limit筛选出来的行并不具有顺序性，也就是说直接显示出来的结果顺序是未定义的，我们不应该依赖这个顺序，而应该利用order by子句返回定义好的顺序。子句返回具体的行数据之后，就可以对这些行数据作某一列字段数据的更新。

在这里插入图片描述

将孙悟空同学的数学成绩变更为 80 分

在这里插入图片描述

将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

在这里插入图片描述

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

在这里插入图片描述

将所有同学的语文成绩更新为原来的 2 倍

如果你不用where、order by、limit等子句做行数据的筛选，则默认对表中的所有行数据进行列字段值的更新

在这里插入图片描述

4.对表内容进行Delete(删除)

4.1 delete from语句的使用

1. delete语句可以对where，order by，limit等子句的筛选结果进行删除。

在这里插入图片描述

删除孙悟空同学的考试成绩

在这里插入图片描述

删除总成绩倒数第一名的同学

limit配合order by子句便可筛选出倒数第一名的同学

在这里插入图片描述

4.2 truncate和delete from的区别

面试官灵魂一问: MySQL 的 delete、truncate、drop 有什么区别?

下面的回答都是摘自上面的文章，上面文章总结的很详细，可以去看看

1. delete属于DML语句，在执行时会经过事务，它会先将删除的数据缓存到rollback segement，事务commit之后会立即生效。 对于delete from table_name，在InnoDB里面，delete并不会真的将数据删除，而只是给删除的数据搞了一个标记位，表示该数据不可见，但该数据对应的磁盘文件所占用的空间并不会释放，在下一次向表中插入数据时，这部分空间可以被重新利用，删除数据则会被新的数据覆盖掉。 对于delete from table_name，在MyISAM里面，删除数据所在的磁盘文件空间会被立即释放。 对于delete from table_name where xxx 带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间。 无论是InnoDB还是MyISAM存储引擎，如果想要在delete数据之后，释放磁盘空间，则可以执行optimize table table_name语句。 delete 操作是一行一行删除数据的，并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，生成的大量日志也会占用磁盘空间

2. truncate属于DDL语句，执行时并不会经过事务，三大类SQL语句，只有DML才会经过事务(InnoDB)，与delete不同的是，truncate只能对整表的数据进行操作，不能像 DELETE 一样针对部分数据操作。 执行truncate时，删除数据并不会放到rollback segement中，执行后立即生效，如果不备份数据的话，则无法找回被删除的数据。 无论是InnoDB还是MyISAM，执行truncate后，会立即释放磁盘空间，删除表中的所有数据，直接释放数据页。 小心使用 truncate，尤其没有备份的时候，如果误删除线上的表，记得及时联系中国民航，订票电话：400-806-9553

3. drop也属于DDL语句，与truncate一样，如果没有备份直接删除数据的话，则也无法找回。 小心使用drop和truncate，这是两个很危险的指令，要删表跑路的兄弟，请在订票成功后在执行操作！订票电话：400-806-9553

可以这么理解，一本书，delete是把目录撕了，truncate是把书的内容撕下来烧了，drop是把书烧了

在这里插入图片描述

在这里插入图片描述

5.聚合函数（括号内加distinct进行去重）

1. 能被聚合的列字段必须是数字，否则是没有意义的。count函数除外，count括号内的字段可以是数字，列字段名，通配符等等，因为count只负责统计表中记录(表中的一行数据成为记录)的个数，所以count比较特殊，其他的四个聚合函数括号内的字段只能是值为数字的列字段名，否则是没有意义的。

在这里插入图片描述

统计班级共有多少同学

在这里插入图片描述

统计本次考试的数学成绩分数个数

在这里插入图片描述

统计数学成绩总分

在这里插入图片描述

统计平均总分。返回英语最高分。返回 > 70 分以上的数学最低分。

在这里插入图片描述

2. 下面这样的sql语句是不符合语法支持的，如果最小成绩可能出现多个的话，则name列字段也会有多个，但min作聚合后的列字段只能有一个值，这明显就会有问题，select无法显示出二维的行列式结构了就，所以这样的语法MySQL一定是不支持的。 如果想要支持，则应该使用select name, min(math) from exam_result where math > 70 group by name;此条SQL语句执行时，关键字的优先级顺序为from>where>group by>select>，其实就是先对数据作where条件的筛选，然后对筛选出来的数据进行分组，分组时按照name的不同来进行分组，分组之后，对分组后的结果进行聚合统计，然后对于每组的行数据进行部分列字段的显示，此时就只显示两列字段，一个是name，一个是聚合统计结果min(math)

在这里插入图片描述

6.group by子句的使用（配合having进行分组聚合统计之后的条件筛选）

1. 一般来说group by通常配合聚合函数来使用，以便进行分组聚合统计。

在这里插入图片描述

下面是oracle 9i的经典测试表。

如何显示每个部门的平均工资和最高工资

在这里插入图片描述

显示每个部门的每种岗位的平均工资和最低工资 先以部门的不同将emp中的数据分为三组，然后在每个组内部再按照岗位的不同进行细分组，然后对最终细分的组内进行聚合统计，然后将聚合统计结果显示出来。

在这里插入图片描述

显示平均工资低于2000的部门和它的平均工资

像上面的需求与前面的就不同了，他有了一个筛选条件，在group by这里，通常使用having来作为筛选条件，当分组聚合统计结束后，会使用having进行统计结果的筛选，最后在select显示筛选后的聚合结果列字段以及其他列字段。

在这里插入图片描述

7.笔试面试题

牛客：SQL228 批量插入数据

在这里插入图片描述

牛客：SQL202 找出所有员工当前薪水salary情况

在这里插入图片描述

牛客：SQL195 查找最晚入职员工的所有信息

在这里插入图片描述

牛客：SQL196 查找入职员工时间排名倒数第三的员工所有信息

在通过入职时间排序的时候必须要加distinct去重，因为有可能存在相同入职时间的员工，如果不去重，排序出来的倒数第三入职时间和倒数第一，倒数第二是同一入职时间，所以必须去重，然后将筛选出来的真正的倒数第三的入职时间作为where子句的筛选条件，找出表中所有该入职时间的所有员工。

在这里插入图片描述

牛客：SQL201 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

在这里插入图片描述

牛客：获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary，输出结果按照dept_no升序排列(请注意，同一个人可能有多条薪水情况记录)

这道题是一个多表查询的题，可以等下面学完之后，返回头来做这个题，做题目时，注意SQL关键字的执行优先级。from>where>select>order by

在这里插入图片描述

牛客：从titles表获取按照title进行分组

在这里插入图片描述

力扣：182. 查找重复的电子邮箱

在这里插入图片描述

力扣：595. 大的国家

在这里插入图片描述

力扣：177. 第N高的薪水

面试题：SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select> distinct > order by > limit

二、MySQL内置函数

1.日期函数

1. 下面是一些比较常见的日期函数，除此之外还有很多其他的日期函数，可以自己再查一查，这里就只讲这一部分的日期函数。

在这里插入图片描述

下面是日期函数的使用样例，没什么难度，看一下就好

在这里插入图片描述

2. 日期类型一般可以用在记录生日的字段，date类型可以存储日期，time类型可以存储时间，datetime类型可以存储日期+时间数据。

在这里插入图片描述

下面是日期类型的两个使用案例

在这里插入图片描述

在这里插入图片描述

2.字符串函数

在这里插入图片描述

charset可以显示参数字段所使用的字符编码格式，concat可以将多个字符串参数连接在一起

在这里插入图片描述

instr可以查看要查询在子串是否在被查询的字符串当中，如果在则返回下标，不在返回0，ucase用于将字符串中的每个英文字符转为大写，lcase用于将字符串中的每个英文字符转为小写，length可以求出字符串所占用的字节数。

在这里插入图片描述

在这里插入图片描述

如果要一长串的显示信息，则可以使用concat将列字段和其他字符串连接在一起，然后进行select显示

在这里插入图片描述

replace可以在第一个参数中查找第二个参数的位置，查找到后用第三个参数进行替换。replace不会更改数据的存储，仅仅只是在select显示层面上进行更改。

在这里插入图片描述

substring可以用来截取子串，第一个参数代表要被截取的string，第二个参数代表从哪个下标位置开始截取，第三个参数代表截取的长度。substring不会更改数据的存储，仅仅只是在select显示层面上进行更改

在这里插入图片描述

ltrim和rtrim只删除字符串左或右的空格，如果字符串中间有空格，则并不会删除，trim就是ltrim+rtrim的作用，直接删除左右两边的空格。

在这里插入图片描述

3.数学函数

abs用于求绝对值，bin可以求出数的二进制表示，hex求出十六进制表示形式，conv用于将第一个参数从第二个参数所代表的进制转换为第三个参数所代表的进制形式。

在这里插入图片描述

format用于格式化小数形式，可以指定保留小数的位数是多少。mod用于取模，除了正整数能取模之外，负数也可以取模，负数取模从形式上来看分两种情况，一种是第一个参数是负数，一种是第二个参数是负数，但实际计算的时候，我们可以通过扩大第二个参数整数倍的方式然后再加上一个数等于第一个参数，而此时加上的那个数就是mod的结果。 例如10mod-3，-3扩大-3倍然后加1就等于10，则取模结果为1。-10mod3，3扩大-3整数倍然后加-1就等于10，则取模结果为-1.需要注意的是，在扩大整数倍时，一定要接近最后等于的数字，但不能超过这个数字，比如-10mod3时，3不能扩大-4倍然后加上2，这样是不行的，因为不够接近-10.

在这里插入图片描述

rand可以返回0到1.0之间的随机数。

在这里插入图片描述

关于取整，常见的取整方式可以分为四种，零向取整，向上取整，向下取整，四舍五入取整。 取整的结果可以看下面的案例，并不难，看过之后就明白了。

在这里插入图片描述

4.其他函数

下面是MySQL中一些常见的其他函数，可以自己看一下使用案例。

在这里插入图片描述

在这里插入图片描述

牛客：SQL245 查找字符串中逗号出现的次数

在这里插入图片描述

三、MySQL复合查询

1.基本查询回顾

查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

在这里插入图片描述

按照部门号升序而雇员的工资降序排序

在这里插入图片描述

使用年薪进行降序排序

在这里插入图片描述

显示工资最高的员工的名字和工作岗位

在这里插入图片描述

显示工资高于平均工资的员工信息

在这里插入图片描述

显示每个部门的平均工资和最高工资

在这里插入图片描述

显示平均工资低于2000的部门号和它的平均工资

在这里插入图片描述

显示每种岗位的雇员总数，平均工资

在这里插入图片描述

2.多表查询

1. 像上面我们所学到的所有查询，比如带where子句，order by子句，group by子句，having子句，分页显示limit的查询都是单表查询，而我们一般查询的数据可能不止来自一个表，很有可能来自多个表，所以需要多表查询。

显示雇员名、雇员工资以及所在部门的名字

from后面跟着两个表，则先将两个表作笛卡尔积，但笛卡尔积后的表会有很多行数据是多余的，因为雇员只可能在一个部门里面，所以emp.deptno必须和dept.deptno保持一致，所以笛卡尔积之后还需要where条件筛选出合理的记录。

在这里插入图片描述

显示部门号为10的部门名，员工名和工资

在这里插入图片描述

显示各个员工的姓名，工资，及工资级别

在这里插入图片描述

显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno）

像上面这样的需求，其实就需要自连接，可以使用多表查询的方式，也可以选择子查询的方式

在这里插入图片描述

3.嵌套查询(子查询)

1. 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。 通过嵌入到其他sql语句中的select语句的返回结果，子查询又可以细分为单行子查询，多行子查询，多列子查询，子查询除了可以用在where子句充当筛选条件外，还可以用在from子句充当临时表，作笛卡尔积。

显示SMITH同一部门的员工

在这里插入图片描述

查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，部门名字，但是不包含10自己的工作岗位

在这里插入图片描述

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

在这里插入图片描述

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

在这里插入图片描述

查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

在这里插入图片描述

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

在这里插入图片描述

查找每个部门工资最高的人的姓名、工资、部门、最高工资

在这里插入图片描述

显示每个部门的信息（部门名，编号，地址）和人员数量

在这里插入图片描述

2. 合并查询就是将多个select的查询结果合并到一起，union在合并时，会自动去掉重复的行（两个所有列字段完全重复的行），union all不会去掉重复的行。

union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

将工资大于2500或职位是MANAGER的人找出来

在这里插入图片描述

union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

将工资大于2500或职位是MANAGER的人找出来

在这里插入图片描述

4.实战OJ

查找所有员工入职时候的薪水情况

在这里插入图片描述

SQL204 获取所有非manager的员工emp_no

在这里插入图片描述

SQL205 获取所有员工当前的manager

在这里插入图片描述

四、MySQL内外连接

1.内连接

1. 内连接实际就是先根据on的条件对表的连接结果作筛选，所以关键字的优先级为from>on>join，因为作笛卡尔积之前，要指定表的连接条件，让两个表在真正连接时，有目的的连接。 有人可能会有疑问，为什么不先连接，然后再作on条件的筛选呢？如果是这样的话，效率肯定要低，因为需要先连接，然后再筛选，这需要两步，而先on再join的话，只需要一步即可，直接带着筛选条件进行连接即可。

在这里插入图片描述

显示SMITH的名字和部门名称

在这里插入图片描述

2.外连接（左侧表完全显示 或 右侧表完全显示）

1. 在表连接时，如果一个表必须完全显示，则我们说这是外连接，当左侧表完全显示时，我们称是左外连接，右侧表完全显示时，我们称是右外连接。

在这里插入图片描述

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

在这里插入图片描述

对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来

在这里插入图片描述

列出部门名称和这些部门的员工信息，同时列出没有员工的部门

在这里插入图片描述

3.实战OJ

178. 分数排名

SQL中关键字的执行顺序是from>where>group by>select>order by，分组之后会执行聚合函数，然后进行select显示，最后进行order by排序，在给列字段取别名时，要加单引号，防止别名与MySQL中的关键字冲突。 这道题的思路是将两个表作笛卡尔积，比当前分数大于等于的所有分数进行去重后的个数，即为当前分数的排名，比如分数有3.5 3.65 4.0 3.85 4.0 3.65，则3.5的排名应该是第四名，因为大于等于3.5的数字只有4.0 3.85 3.65 3.5这四个数字，所以3.5的排名就是第四名。 在笛卡尔积之后，只保留b表成绩大于等于a表的成绩的一行记录，然后以a表的id进行分组，这样每组内a表的成绩score就是唯一的，而组内b的成绩去重之后的记录个数就是a表成绩的排名。

在这里插入图片描述

在这里插入图片描述