快捷键:
FN + → 到行尾
FN + ← 到行首
Shift + Fn + 左方向键 选中这行
Shift + 上下键,选中多行 然后运行
Ctrl+R 运行
复制当前行：Ctrl+D
除了数字类型 其他类型都要加''引号
MySQL教程
一.MySQL的下载和安装
1.1下载
官网：https://www.mysql.com/
下载：https://dev.mysql.com/downloads/mysql/
企业版和社区版有什么区别？
① 企业版只包含稳定后的工程，社区版包含所有的Mysql的最新功能。
② 社区版不提供官方技术支持。
③ 社区版免费，企业版收费。
1.2安装
-
使用管理员模式打开命令行，如果之前安装过MySQL，执行 sc delete mysql。如果删除不了，打开任务管理器结束掉MySQL的进程。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-927mgMmp-1622937857762)(MySQL教程.assets/image-20210316132259067.png)]
-
解压安装包，配置环境变量（解压要求：路径不允许有中文名，路径不允许太深）
-
解压mysql-5.7.17-winx64.zip压缩包（64位系统），解压后的目录结构如下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-APmUbB43-1622937857764)(MySQL教程.assets/image-20210316132616519.png)]
-
配置环境变量MYSQL_HOME以及path
为什么配置MYSQL_HOME?
- 方便引用，当你的目录很长时，不需要输入很长的路径串，只需要输入%MYSQL_HOME%。
- 当MySQL路径改变时，只需要改变MYSQL_HOME的值，否则的话，任何使用绝对路径的地方都要修改，如果有遗漏，可能造成系统崩溃。
- 第三方软件会引用约定好的数据库路径，而这个变量名就叫做MYSQL_HOME。
为什么配置PATH?
- 使你的mysql的bin目录下的命令能够在全局使用。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kECMD9z7-1622937857765)(MySQL教程.assets/image-20210316133007209.png)]
配置如下：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eDuh6Jyu-1622937857766)(MySQL教程.assets/image-20210316133219059.png)]
-
-
使用管理员模式打开命令行，进入到mysql的bin目录下。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bDx1oiil-1622937857767)(MySQL教程.assets/image-20210316143119165.png)]
-
执行命令：mysqld install MySQL --defaults-file=“D:\apps\mysql-5.7.17-winx64\my-default.ini”（自己实际解压的位置）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9oe1lt5f-1622937857769)(MySQL教程.assets/image-20210316143713087.png)]
-
启动mysql服务：net start mysql
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-viEbKxrj-1622937857770)(MySQL教程.assets/image-20210316143941322.png)]
如果出现如下问题：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6q5mCZLd-1622937857771)(MySQL教程.assets/image-20210316143810278.png)]
执行如下命令：
mysqld --install
mysqld --initialize
再次启动数据库服务：
net start mysql
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D2vM8qXR-1622937857772)(MySQL教程.assets/image-20210316143842016.png)]
-
修改mysql的初始密码
-
Mysql在启动后会初始化一个默认密码，该密码在mysql的安装目录data/xxx.err结尾的文件中。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jm5fW352-1622937857772)(MySQL教程.assets/image-20210316144117221.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uh3UtvbH-1622937857774)(MySQL教程.assets/image-20210316144219110.png)]
-
使用命令行登录mysql服务
- mysql -uroot -p 回车 然后输入默认密码。
- 修改密码，执行。
- 输入quit退出，再次登录测试密码是否修改成功。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TkK8FdLc-1622937857774)(MySQL教程.assets/image-20210316144618749.png)]
-
-
安装navicat，使用图形化界面操作数据库。
解压navicat的压缩包，点击安装程序进行安装。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1tXmd4gM-1622937857775)(MySQL教程.assets/image-20210317225645918.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PpR3nilv-1622937857775)(MySQL教程.assets/image-20210317225656540.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fEPYQ7YW-1622937857776)(MySQL教程.assets/image-20210317225709199.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Lg2HQLsp-1622937857777)(MySQL教程.assets/image-20210317225723473.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vdb6EEV9-1622937857777)(MySQL教程.assets/image-20210317225733043.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R1ikRDXz-1622937857778)(MySQL教程.assets/image-20210317225740572.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PH0q1kts-1622937857779)(MySQL教程.assets/image-20210317225757104.png)]
安装完成后，使用破解程序破解软件。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Et66tYGQ-1622937857779)(MySQL教程.assets/image-20210317225840475.png)]
这时可以打开navicat进行数据库的连接测试。
点击左上角的文件，选择新建连接，选择MySQL。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HEngY8kj-1622937857780)(MySQL教程.assets/image-20210318160909848.png)]
输入连接名和数据库密码，连接名可以任意起。点击确定后，连接创建成功，这时可以启动连接查看数据库里的内容。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PVrZnVw0-1622937857780)(MySQL教程.assets/image-20210318161132977.png)]
双击test,或者右键选择打开连接，当它变成绿色，数据库就连接成功了。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ESH9wg7l-1622937857781)(MySQL教程.assets/image-20210318161219032.png)]
二.数据库的引入和介绍
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kcx7gN80-1622937857782)(MySQL教程.assets/image-20210316150016548.png)]
2.1 什么是数据库
数据库，顾名思义就是用来存储数据的仓库，有了这个仓库，我们可以直接在里面查找数据，就像你每天查询你支付宝里的余额，这个余额数据就是从数据库里调出来的。有人可能会问，为什么数据不能存储在我的电脑EXCEL,或者WORD文档里呢?这就体现了数据库的优势，数据库在存储大量数据的同时，还能允许很多人同时使用里面的数据。
数据库中的数据是按照一定格式进行存储的，用户可以对其中的数据进行增删改查等操作。
2.2 为什么是数据库
计算机中存储数据的方式很多，比较常见的有word,excel,ppt等，那么为什么要用数据库呢？
假设有如下场景：现在要求你做一个检索电影信息的网站，那么你的电影信息存在哪里呢？假设存储在普通文件中，请考虑如下需求：
-
查找哪些电影主演是成龙？
-
如果一个文件里出现两条成龙主演的电影，那么我们如何确定这两个成龙是不是一个人？
-
如果有人把电影上映年份修改成201y这种无效数据该怎么办？
-
一部电影有很多主演，该如何存储？
-
当多个用户同时操作同一文件中的数据时如何处理？
作为网站的开发者，这些问题显示都是要注重的，所以我们引入数据库，数据库的各种完整性约束能够保证数据的有效性，完整性，正确性，一致性。
2.3 数据库的种类
数据库可分为三种
-
关系型数据库
关系型数据库，一般使用表来存储数据，数据表的结构类似于EXCEL表，存在这种数据库中的数据都是有关系的。比较常见的关系型数据库有MySQL，SqlServer,Oracle，MariaDB(linux系统默认数据库)。数据间的关系有如下三种：
学生信息，每个人的学号和姓名都是一对一的关系；
班级信息，每个班级可以拥有很多学生，但是一个学生只能属于一个班级，典型的一对多关系。
学生选课信息，每个学生可以选很多课程，一门课程也可以多个学生选择，典型的多对多关系。
-
非关系型数据库（NoSQL) 存储的数据之间没有关系
如果我们要存储的数据是一篇文章，或者是一张图片，就没办法存储在关系型数据库中，这时就需要使用非关系型数据库进行存储，常用的有MongoDB，HBase，CouchDB。
-
键值（key-value）数据库 Reids
数据都以键值对的形式存储，例如Apache Cassandra，Dynamo，LevelDB。可用于存储会话数据和购物车数据。
三.MySQL的入门
MySQL是数据库管理系统，用于对多个数据库进行统一管理，它使用一种叫做SQL（Structured Query Language）的语言来擦操作数据库中的数据，以此来满足我们对数据不同的需求。
3.1数据库 数据库管理系统和数据库系统
当我们使用navicat成功连接到本地数据库时，有的同学会发现下面的情况。
这个时候有的同学就会提出疑问，我们连接成功的test是什么？是数据库吗？如果test是数据库，那么下面的四个东东是什么？用下面的图来解释大家的疑问。
图中的粉色框代表我们的计算机，黄色框代表我们的MySQL,也就是数据库管理系统，绿色框对应的是真正 用来存储数据的数据库。
3.2 SQL
3.2.1 SQL的基本数据类型
标粗的比较常用:int (10) char date int可以后面加括号指定最大长度
| 类型 | 描述 |
|---|---|
| int | 整型，和java中的int类型一样； |
| double | 浮点型，例如double(5,2)表示该列存放的数据最多是5位，而且其中必须有2位小数，它的最大值是：999.99； |
| decimal | 浮点型，用于保存对准确精度有重要要求的值，例如与金钱有关的数据。 |
| char (120) | 固定长度字符串类型，如果没有指定长度，char默认长度是255，2^8-1如果存储的字符没有达到指定长度，mysql将会在其后面用空格补足到指定长度； |
| varchar(120) | 可变长度字符串类型，它的长度可以由我们自己指定且必须指定，它能保存数据长度的最大值是65535，2^16-1.如果存储的字符没有达到指定的长度，不会补足到指定长度； |
| tinytext | 可变长度字符串类型，其存储范围是28-1B； |
| text | 也是可变长度字符串类型，其存储范围是216-1B； |
| mediumtext | 也是可变长度字符串类型，其存储范围是224-1B； |
| longtext | 也是可变长度字符串类型，其存储范围是232-1B； |
| tinyblob | 是可变长度二进制类型，其存储范围是28-1B； |
| blob | 是可变长度二进制类型，其存储范围是216-1B； |
| mediumblob | 也是可变长度二进制类型，其存储范围是224-1B； |
| longblob | 也是可变长度二进制类型，其存储范围是232-1B； |
| date | 日期类型，格式为yyyy-MM-dd，只有年月日，没有时分秒； |
| time | 时间类型，格式为hh:mm:ss，只有时分秒，没有年月日； |
| timestamp | 时间戳类型，格式为：yyyy-MM:dd hh:mm:ss，年月日，时分秒都有。 |
char和varchar有什么区别？
char和varchar都是字符串类型，char的长度是固定的，不会随着字符串长度的变化而变化。varchar的长度不是固定的，可以根据字符串的长度而改变，但最长不能超过它的阈值65535
3.2.2 SQL的基本语法
SQL的基本语法主要可以分为四种， 不用记,只是对语法的分类,
- 数据查询语言DQL（对数据的查询，主要指SELECT语句）
- 数据操纵语言DML（对数据的增加删除和修改）
- 数据定义语言DDL（对结构进行操作的语言，比如对库结构，表结构等）
- 数据控制语言DCL（用来授予或回收访问数据库的某种特权,对事务操作，比如事务的提交或者回滚）
这四种类型囊括了对数据库的所有操作，下面我们由外向内学习对数据库的操作语法。
3.2.2.1 使用navicat执行SQL语句
1.选中初始库中的一个库（例如mysql库），连接，连接方式双击或者右键选择打开数据库。选中后变成绿色。
-
右键查询，选择新建查询，这时候就可以在里面输入SQL语句了。
3.SQL语句执行方式：选中要执行的SQL语句，右键选择[运行已选择的]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tFtlFC6U-1622937857782)(MySQL教程.assets/image-20210318163250899.png)]
注意：创建的查询要及时ctrl+s，不要把你自己写的代码丢掉
-
CRUD
CREATE 增加
RETRIEVE 检索
UPDATE 更新
DROP/DELETE 删除
3.2.2.2 对数据库的基本操作（DDL）
-
使用默认的字符集和排序规则创建数据库
#这是注释的第一种方式(SQL语法关键字不区分大小写，但是建议关键字大写，库名等自己定义的字段名小写) -- 这是注释的第二种方式(在每一句完整的SQL后加上分号，保持良好的代码习惯) -- 语法如下： CREATE DATABASE 数据库名; -- 实例： CREATE DATABASE myfir_db; -- 执行后在左侧的空白处点击右键，选择刷新，就可以看到创建的数据库myfir_db了。右键选择数据库属性，就可以看到这个库的默认编码集。 -
使用指定的字符集和排序规则创建数据库
-- 语法如下： CREATE DATABASE 数据库名 CHARACTER SET 字符集名; -- 实例：常用的字符集就是UTF8,能够支持中文。 CREATE DATABASE mysec_db CHARACTER SET UTF8;- 创建好自己的数据库以后请转到自己的库里进行操作。
-
查看当前数据库服务器下所有数据库
-- 语法如下： SHOW DATABASES; -- 这条语句会在结果窗口中展示已有的数据库 -
查看创建数据库的语句
-- 语法如下: SHOW CREATE DATABASE 数据库名; -- 这条语句会在结果窗口中展示创建该库的语句 前提是该库存在。 CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ -- /*!40100是什么东东？ -- 这句话是系统默认添加的，意思是当你的数据库版本大于4.0.1时会默认执行后边的语句。 -
删除数据库
-- 语法如下: DROP DATABASE 数据库名; -- 这条语句可以删除指定的数据库 前提是该库存在。 -
查看当前使用的数据库
-- 语法如下: SELECT DATABASE(); -- 这条语句会在结果窗口中展示正在使用的数据库 -
切换数据库
-- 语法如下：(当我们在执行SQL语句时，默认是在当前库下操作的，但是可以通过这条语句选择其他数据库) USE 数据库名称;
3.2.2.3 对表结构的基本操作（DDL）
-
创建表
-- 语法如下：CREATE TABLE 表名(字段名 字段类型,字段名 字段类型,字段名 字段类型)[CHARACRER SET 字符集];-- 创建数据表时，字符集的设置是可省略的，如果不设置，默认与所在的库保持一致。CREATE TABLE stu_info(age INT,name VARCHAR(255),gender VARCHAR(255)); -
查看表结构
-- 语法如下：DESC 表名;-- 描述表中的字段以及对应的属性 -
查看当前数据库下所有的表
-- 语法如下：SHOW TABLES; -
查看创建表的语句
-- 语法如下：SHOW CREATE TABLE 表名; -
修改表[不允许使用 可能会造成数据错误]
-- 1.添加列ALTER TABLE 表名 ADD 列名 列类型;-- 2.修改列的类型ALTER TABLE 表名 MODIFY 列名 列类型;-- 3.删除列ALTER TABLE 表名 DROP 列名：-- 4.修改列名称ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型; -
删除表
-- 1.DROP TABLE 表名;-- 直接删除表数据和表结构。-- 2.TRUNCATE TABLE 表名;-- 删除表中数据，但是会保留表结构。
3.2.2.4 表中的约束
为了保证数据库中数据的正确性，一致性，更好的对数据进行管理，要在插入数据之前，对表中的字段进行约束，比如：身份证号不能为空，也不能重复等等。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-64Itcbui-1622937857783)(D:\ChenTyporNote\typroImg\image-20210603090301286.png)]
-
非空约束
在建表的时候设置字段为 not null,这样在插入数据的时候这个字段必须赋值。
CREATE TABLE test1(idnum INT,name VARCHAR(255) NOT NULL);INSERT INTO test1 VALUES(1,'ZS'); -- 运行正确INSERT INTO test1 VALUES(2,NULL); -- 报错 -
唯一性约束
建表时设置字段为unique，这个字段具有唯一性，不能重复，但是可以为null
方式一：列级定义，对单独的某列进行定义CREATE TABLE test2(idnum INT UNIQUE,name VARCHAR(255));INSERT INTO test2 VALUES(1,'zs'); -- 运行正确INSERT INTO test2 VALUES(1,'zs'); -- 报错，idnum重复。方式二：表级定义，可以对多个列进行组合定义。CREATE TABLE test3(idnum INT,name VARCHAR(255),UNIQUE(idnum,name))INSERT INTO test3 VALUES(1,'ZS'); -- 运行正确INSERT INTO test3 VALUES(1,'LS'); -- 运行正确INSERT INTO test3 VALUES(1,'ZS'); -- 运行错误，因为idnum和name同时重复。 -
主键约束:不能为null,不能重复 与unique有什么区别
什么是主键？主键是为了保证数据的唯一性而存在的，在实际生产中，每个表都需要设置主键，以防止出现数据的冗余以及查找的二义性。主键不能重复，一个表只能有一个主键。
主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。
方式一：列级定义，对单独的某列进行定义CREATE TABLE test4(idnum INT PRIMARY KEY,name VARCHAR(255));INSERT INTO test4 VALUES(1,'ZS'); -- 正确INSERT INTO test4 VALUES(NULL,'ZS');-- 错误，主键不能为NULLINSERT INTO test4(name) VALUES('ZS');-- 错误，主键必须赋值INSERT INTO test4 VALUES(1,'LS'); -- 错误，主键不能重复方式二：表级定义，对多个列进行组合定义。CREATE TABLE test5(idnum INT,name VARCHAR(255),PRIMARY KEY(idnum,name))INSERT INTO test5 VALUES(1,'zs'); -- 正确INSERT INTO test5 VALUES(1,'ls'); -- 正确，主键(idnum,name)没有重复INSERT INTO test5 VALUES(NULL,'ls');-- 错误，idnum不能为null-- 在MySQL中，提供了一个自增的数字来自动生成主键值，就是auto_increment,不需要我们自己维护，从1开始自动生成，按1递增。规定：自增必须应用在主键上。CREATE TABLE test6(idnum INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255)) -
外键约束
我们说过，数据库中的表之间都是有关系的，那么它们之间的关系怎么来确定又是怎么来维护的？这就用到了外键。外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。
-- 先创建一个课程表。CREATE TABLE course(courId INT PRIMARY KEY,courName VARCHAR(255));-- 创建学生选课表CREATE TABLE choose(stuId INT,courId INT,FOREIGN KEY(courId) REFERENCES course(courId));REFERENCES:映射外键和父表中的主键映射 有了外键引用之后，表分为父表和子表课程表：父表学生表：子表创建先创建父表删除先删除子表数据插入先插入父表数据
3.2.2.5 对单表数据的操作
-
插入数据INSERT(DML)
-- 插入部分列值INSERT INTO 表名(列名,列名,列名) VALUES(值,值,值),(值，值，值);-- 注意：前面的列名必须和后边的值对应，例如第一个列名为年龄，第一个值也必须是年龄。-- 插入所有列值INSERT INTO 表名(列名,列名,列名) VALUES(值,值,值);-- 注意：这时侯需要把所有列名写进去，很麻烦，所以用下面的语法代替INSERT INTO 表名 VALUES(值,值,值)-- 这个时候，插入值的顺序必须和表中列的顺序一致。#注意点：插入的数据应与字段的数据类型相同 数据的大小应该在列的长度范围内 在values中列出的数据位置必须与被加入的列的排列位置相对应。 除了数值类型外，其它的字段类型的值必须使用引号引起。 如果要插入空值，可以不写字段，或者插入 null. 对于自动增长的列在操作时，直接插入null值即可. -
更新数据UPDATE(DML)
-- 语法如下：UPDATE 表名 SET 列名=列值 WHERE 列名=列值;-- UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'WHERE LastName = 'Wilson'; 把LastName为Wilson的对应数据，城市改为NanJing，地址改为Zhongshan 23-- UPDATE Person SET City = 'BeiJing'WHERE LastName = 'Wilson' AND FirstName='Smith'; -
删除数据DELETE(DML)
-- 语法如下：DELETE语句用于删除表中的行。DELETE FROM 表名称 WHERE 列名称 = 值;-- 实例：DELETE FROM Person WHERE City='BeiJing' -- 删除城市为北京的数据DELETE FROM Person; -- 删除Person表中的所有数据-- DELETE 和 TRUNCATE 的区别 delete是一行一行删除 truncate是将表结构销毁，在重新创建表结构. 如果数据比较多，truncate的性能高。 delete是受事务控制. 可以回滚数据. truncate是不受事务控制. 不能回滚. -
查找数据SELECT(DQL)
-- 语法如下:SELECT 列名 FROM 表名 [WHERE 列 运算符 值];-- []中的部分指的是在查询过程中要满足的条件，如果没有条件的SQL语句是下面这样的，也是最简单的。查询products表中的商品名。SELECT name FROM products;查询products表中的所有数据。SELECT * FROM products;-- 当查询过程中需要加条件时，则需要用到[]中的部分，示例如下查询Products中价格大于100的商品名。SELECT name FROM products WHERE price>100;查询商品名为感悟的所有数据。SELECT * FROM products WHERE name='感悟';-- 那么，[]条件中的运算符除了常用的大于小于不等于外 还有哪些呢？1.AND...OR查找价格大于100并且数量小于150的商品信息。2.BETWEEN...AND(相当于>= AND <=)查找价格在50到150之间的商品信息。3.IN / NOT IN 可以进行多个单值的比较。查找价格为100，150，120的商品信息。4.LIKE / NOT LIKE 进行模糊匹配 使用占位符 _表示占用一个字符 %表示匹配多个字符。查找书名以java开头的商品信息。5.null值的操作 IS NULL / IS NOT NULL查找价格为Null的商品信息。查找价格不为Null的商品信息。 -
ORDER BY 排序
-- 有要求按照字段排序时，需要用到ORDER BY,在WHERE后使用 默认升序ASC 也可加关键字DESC降序,一般按照数值的字段进行排序。示例：查找商品信息，并按照价格升序排序。SELECT * FROM products ORDER BY price;SELECT * FROM products ORDER BY price ASC;查找数量大于50的商品信息，并按照数量的降序排序。SELECT * FROM products ORDER BY pnum DESC;查找商品信息，并按照价格的降序，数量的升序排序。SELECT * FROM products ORDER BY price DESC,pnum ASC; -
DISTINCT
-- 用于完成数据的去重，加在SELECT的后边。示例：查找一共有多少商品种类SELECT DISTINCT category FROM products; -
LIMIT
-- LIMIT用于限制输出的数据条数示例：按价格排序，输出前两名SELECT * FROM products ORDER BY price LIMIT 2;select * from tableName limit i,n# tableName：表名# i：为查询结果的索引值(默认从0开始)，当i=0时可省略i# n：为查询结果返回的数量# i与n之间使用英文逗号","隔开# limit n 等同于 limit 0,n -
别名表示
-- 有时候表名或者字段名太长，难以理解，这时候可以给它起一个别名。使用 AS ，也可省略SELECT price AS 价格 FROM products;-- 给字段起别名SELECT a1.price FROM products a1; -- 给表起别名，取其中的字段时要加上别名。 -
聚合函数
思考一下，我们之前做的所有操作，是不是拿出的数据都是以行为单位？根据条件找到对应的行，再拿出你要的字段。那么如果我需要拿出商品表中价格的最大值或者最小值呢？用之前的方法是明显不能解决的。这就用到了聚合函数。count：统计指定列不为NULL的记录行数；-- 查找商品表中有多少条记录SELECT COUNT(1) FROM products;SELECT COUNT(*) FROM products;-- 统计商品表中价格大于50的有多少条记录SELECT COUNT(*) FROM products WHERE price>50;sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；-- 统计所有商品的数量之和。SELECT SUM(pnum) FROM products;max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；-- 查找商品表中价格的最大值喝最小值。SELECT MAX(price) FROM products;avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；-- 统计所有商品的平均价格SELECT SUM(pnum*price)/SUM(pnum) FROM products; -
分组聚合
看到这个名字，就知道一定和刚才使用的聚合函数有关==》分组和聚合是密不可分的。如果现在我有如下需求，商品表中的信息，按照类别分类，取出每一个类别中价格最高的商品价格。价格最高？MAX()一定要用了，但是对Prodcuts表直接用吗？分组语法如下：SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING 条件 -- 分组操作中的having子名是用于在分组后对数据进行过滤的，作用类似于where条件-- 举个栗子对商品分类别统计，求出每一种类商品的总数量SELECT category,SUM(pnum) FROM products GROUP BY category;对商品分类别统计，求出每一种类商品的总数量，数量要大于100SELECT category,SUM(pnum) FROM products GROUP BY category HAVING SUM(pnum)>100;
单表练习题
-- 第一部分CREATE TABLE `products` ( `id` INT AUTO_INCREMENT, `name` VARCHAR(40) , `price` DOUBLE , `category` VARCHAR(40) , `pnum` INT(11) , `description` VARCHAR(255) , PRIMARY KEY (`id`));INSERT INTO `products` VALUES (NULL,'感悟',100,'励志',100,'一次心灵的鸡汤');INSERT INTO `products` VALUES(NULL,'java与模式',67,'计算机',200,'让你的编程，从些不一样');INSERT INTO `products` VALUES(NULL,'java并发编程实战',190,'计算机',49,'实战大于理论');INSERT INTO `products` VALUES(NULL,'设计模式解析',88,'计算机',86,'头脑风暴');INSERT INTO `products` VALUES(NULL,'搭地铁游上海',28,'生活百科',120,'一次不一样的旅行');INSERT INTO `products` VALUES(NULL,'时空穿行',65,'科技',87,'这是一本好书');INSERT INTO `products` VALUES(NULL,'中国国家地理',45,'生活百科',100,'了解你生活的国家');INSERT INTO `products` VALUES(NULL,'欧洲', NULL,'生活',200,'你梦中向往的地方');INSERT INTO `products` VALUES(NULL,'网管员必备宝典',35,'计算机',120,'上网新手必备书籍');1.查询出所有商品信息2.查询出所有商品的名称，价格，类别及数量信息3.查询出所有的商品类别4.查询出所有商品的名称及价格，将所有商品价格加105.查询出每一个商品的总价及名称6.查询所有计算机类商品信息7.查询出商品价格大于90的商品信息8.查询出商品总价大于10000的商品信息9.查询出价格在100-200之间的商品信息10.查询出商品价格是65,100或190的商品信息11.查询出商品的名称中包含java的商品信息。12.查询出书名是两个字的商品信息13.查询出商品价格不为null商品信息14.查询出所有商品，并根据价格进行升序排序15.查询出所有商品，根据数量进行升序排列，如果数量相同，根据价格进行降序排列16.统计商品表中共有多少条记录17.统计商品表中价格大于50的有多少条记录18.统计有多少商品19.统计所有商品的总价值20.统计所有商品的平均价格21.统计出记录中price的平均值22.统计出商品表中price最大与最小值23.对商品分类别统计，求出每一种类商品的总数量24.对(id<8)商品分类别统计，求出每一种类商品的总数量，数量要大于100-- 答案1.查询出所有商品信息SELECT * FROM products;2.查询出所有商品的名称，价格，类别及数量信息SELECT name,price,category,pnum FROM products;3.查询出所有的商品类别SELECT DISTINCT category FROM products;4.查询出所有商品的名称及价格，将所有商品价格加10SELECT name,price+10 FROM products;5.查询出每一个商品的总价及名称SELECT name,IFNULL(price,0)*IFNULL(pnum,0) FROM products;6.查询所有计算机类商品信息SELECT * FROM products WHERE category='计算机';7.查询出商品价格大于90的商品信息SELECT * FROM products WHERE price>90;8.查询出商品总价大于10000的商品信息SELECT * FROM products WHERE IFNULL(price,0)*IFNULL(pnum,0)>10000;9.查询出价格在100-200之间的商品信息SELECT * FROM products WHERE price BETWEEN 100 AND 200;10.查询出商品价格是65,100或190的商品信息SELECT * FROM products WHERE price IN (65,100,190);11.查询出商品的名称中包含java的商品信息。SELECT * FROM products WHERE name LIKE '%java%';12.查询出书名是两个字的商品信息SELECT * FROM products WHERE name LIKE '__';13.查询出商品价格不为null商品信息SELECT * FROM products WHERE price IS NOT NULL;14.查询出所有商品，并根据价格进行升序排序SELECT * FROM products ORDER BY price ASC;15.查询出所有商品，根据数量进行升序排列，如果数量相同，根据价格进行降序排列SELECT * FROM products ORDER BY pnum,price DESC;16.统计商品表中共有多少条记录SELECT COUNT(*) FROM products;17.统计商品表中价格大于50的有多少条记录SELECT COUNT(*) FROM products WHERE price>50;18.统计有多少商品SELECT SUM(pnum) FROM products;19.统计所有商品的总价值SELECT SUM(price) FROM products;20.统计所有商品的平均价格SELECT SUM(price*pnum)/SUM(pnum) FROM products;21.统计出记录中price的平均值SELECT AVG(price) FROM products;22.统计出商品表中price最大与最小值SELECT MAX(price),MIN(price) FROM products;23.对商品分类别统计，求出每一种类商品的总数量SELECT category,SUM(pnum) FROM products GROUP BY category;24.对(id<8)商品分类别统计，求出每一种类商品的总数量，数量要大于100SELECT category,SUM(pnum) FROM products WHERE id<8 GROUP BY category HAVING SUM(pnum)>100;-- 第二部分create table student( id int, name varchar(20), chinese float, english float, math float);insert into student(id,name,chinese,english,math) values(1,'行哥',89,78,90);insert into student(id,name,chinese,english,math) values(2,'潘金莲',67,53,95);insert into student(id,name,chinese,english,math) values(3,'凤姐',87,78,77);insert into student(id,name,chinese,english,math) values(4,'旺财',88,98,92);insert into student(id,name,chinese,english,math) values(5,'白小黑',82,84,67);insert into student(id,name,chinese,english,math) values(6,'白小黄',55,85,45);insert into student(id,name,chinese,english,math) values(7,'范蹦蹦',75,65,30);25.查询表中所有学生的信息。26.查询表中所有学生的姓名和对应的英语成绩。27.过滤表中重复数据。28.统计每个学生的总分。29.在所有学生总分数上加10分特长分。30.使用别名表示学生分数。31.查询姓名为行哥的学生成绩32.查询英语成绩大于90分的同学33.查询总分大于200分的所有同学34.查询英语分数在 80－90之间的同学。35.查询数学分数为89,90,91的同学。36.查询所有姓白的学生英语成绩。37.查询数学分>80并且语文分>80的同学38.查询英语>80或者总分>200的同学39.对数学成绩排序后输出40.对总分排序后，然后再按数学从高到低的顺序输出41.对姓白的学生成绩排序输出-- 答案25.查询表中所有学生的信息。SELECT * FROM student;26.查询表中所有学生的姓名和对应的英语成绩。SELECT name,english FROM student;27.过滤表中重复数据。SELECT DISTINCT * FROM student;28.统计每个学生的总分。SELECT chinese+english+math FROM student;29.在所有学生总分数上加10分特长分。SELECT chinese+english+math+10 FROM student;30.使用别名表示学生分数。SELECT chinese AS ch,math AS mt,english AS en FROM student;31.查询姓名为行哥的学生成绩SELECT * FROM student WHERE name='行哥';32.查询英语成绩大于90分的同学SELECT * FROM student WHERE english>90;33.查询总分大于200分的所有同学SELECT * FROM student WHERE (chinese+english+math)>200;34.查询英语分数在 80－90之间的同学。SELECT * FROM student WHERE english BETWEEN 80 AND 90 ;35.查询数学分数为89,90,91的同学。SELECT * FROM student WHERE math IN (89,90,91);36.查询所有姓白的学生英语成绩。SELECT * FROM student WHERE name LIKE '白%';37.查询数学分>80并且语文分>80的同学SELECT * FROM student WHERE math>80 AND chinese>80;38.查询英语>80或者总分>200的同学SELECT * FROM student WHERE english>80 OR (chinese+english+math)>200;39.对数学成绩排序后输出SELECT * FROM student ORDER BY math;40.对总分排序后，然后再按数学成绩从高到低的顺序输出SELECT * FROM student ORDER BY (chinese+english+math),math DESC;41.对姓白的学生成绩排序输出SELECT * FROM student WHERE name LIKE '白%' ORDER BY (chinese+english+math)DESC;
3.2.3 SQL中的函数 加粗为重要函数
-
字符串函数
ASCII(s) 返回字符串s的首字母的ASCII码
SELECT ASCII('Abcd'); -- 65SELECT ASCII('abcd'); -- 97CHARACTER_LENGTH(s) 返回字符串s的长度
SELECT CHARACTER_LENGTH("hello SQL"); -- 9SELECT CHARACTER_LENGTH(name) FROM products;CONCAT(s1,s2,…sn) 将多个字符串拼接一个字符串
SELECT CONCAT('HELLO','WORLD','我爱','郭老师');SELECT CONCAT(name,category) FROM products;CONCAT_WS(x,s1,s2…sn) 将多个字符串拼接成一个字符串，中间以x作为分隔符。
SELECT CONCAT('--',name,category) FROM products;FIELD(s,s1,s2…sn) 返回第一个字符串 s 在字符串列表(s1,s2…)中的位置
SELECT FIELD("c", "a", "b", "c", "d", "e");FORMAT(x,n) 函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。
SELECT FORMAT(250500.5634, 2);LOCATE(s1,s) 从字符串 s 中获取 s1 的开始位置
SELECT LOCATE('b', 'abc');SELECT LOCATE('a','hahaha');LCASE(s) 将字符串 s 的所有字母变成小写字母
SELECT LCASE('HELLO WORLD');REVERSE(s) 将字符串s的顺序反过来
SELECT REVERSE('abc') -
数字函数
ABS(x) 返回 x 的绝对值
SELECT ABS(-1);AVG(x) 返回表达式的平均值
SELECT AVG(price) FROM products;MOD(x,y) 返回x除以y后的余数。
SELECT MOD(10,3); -
日期函数
ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期
SELECT ADDDATE('20121201',4);SELECT ADDDATE('20210328',-5);CURRENT_DATE() 返回当前日期
SELECT CURRENT_DATE();CURRENT_TIME() 返回当前时间
SELECT CURRENT_TIME();CURRENT_TIMESTAMP() 返回当前日期和时间
SELECT CURRENT_TIMESTAMP();DATEDIFF(d1,d2) 计算d1,d2之间的时间间隔
SELECT DATEDIFF(CURRENT_DATE(),'1840-01-01')DATE() 从日期或者日期表达式中提取日期值。
SELECT DATE('2010-01-30 10:12:13')DATE_FORMAT(d,f) 按照f要求的格式显示日期d
SELECT DATE_FORMAT('2017-08-10','%Y/%b/%D') -
高级函数
IFNULL(expr1,expr2),如果expr1不为null，则返回expr1，否则返回expr2
SELECT IFNULL(NULL,10);SELECT IFNULL(10,NULL);IF(expr,v1,v2) 如果expr成立，则返回v1，否则返回v2
SELECT IF(1>2,'返回1','返回2');ISNULL(expression) 判断表达式是否为空
SELECT ISNULL('HELLO BRO');not like not in
3.2.3 多表数据关联操作
-
交叉连接
Select * from A cross join B;Select * from A,B;实现原理：笛卡尔积[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IgDrVcCG-1622937857784)(MySQL教程.assets/wps1.png)]
在进行多表连接的时候，不要使用这种语法，如果两表都有一万数据，拼接出来就有一个亿的数据，而其中会出现大量的冗余无效数据。
-
内连接 造成数据缺失
内连接就是两张表同时都满足一定的条件的的结果 把无效数据过滤掉
语法一：SELECT * FROM A INNER JOIN B ON 连接条件;语法二：SELECT * FROM A,B WHERE 条件[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zNSiLN1Q-1622937857784)(MySQL教程.assets/image-20210331115453866.png)]
-
外连接：左外连接，右外连接，全外连接 最常用
-
左外连接
以左表为主，左表的数据全部保留。SELECT * FROM A LEFT JOIN B ON 连接条件;[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wRnA6Mjv-1622937857785)(MySQL教程.assets/image-20210331115656547.png)]
-
右外连接
以右表为主，右表的数据全部保留。SELECT * FROM A RIGHT JOIN B ON 连接条件;[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jnGTyEMR-1622937857785)(MySQL教程.assets/image-20210331115737442.png)]
-
全外连接 MySQL不支持全外连接的语法
-
-
联合查询
我们经常会碰到这样的应用，两个表的数据按照一定的查询条件查询出来以后，需要将结果合并到一起显示出来，这个时候就需要用到union和union all关键字来实现这样的功能，使用该关键字时，两个查询语句查询的字段数必须相同，且字段名应相同，否则查出来的数据没有意义，如果查询字段的数据类型不同，会进行数据类型的转换。
语法： SELECT 字段 FROM A WHERE 条件 Union SELECT 字段 FROM B WHERE 条件-- UNION可以消除重复记录不去重,用union all SELECT 字段 FROM A WHERE 条件 Union all SELECT 字段 FROM B WHERE 条件 -
子查询
子查询：放在外面的查询语句称为父查询，放在里面查询称为子查询。
-- 有时候我们通过一次查询并不能查找到需要的数据，这就需要进行子查询，也就是嵌套查询。 SELECT * FROM A WHERE 字段 判断符 [查询条件是另一条查询语句] SELECT * FROM orders WHERE customer_id=(SELECT id FROM customer WHERE name='zhangsan');
SQL语句的执行顺序
确定找哪个表,
1、FROM table1 left join table2 on 将table1和table2中的数据产生笛卡尔积，生成Temp12、JOIN table2 所以先是确定表，再确定关联条件3、ON table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp24、WHERE 对中间表Temp2产生的结果进行过滤 产生中间表Temp35、GROUP BY 对中间表Temp3进行分组，产生中间表Temp46、HAVING 对分组后的记录进行聚合 产生中间表Temp57、SELECT 对中间表Temp5进行列筛选，产生中间表 Temp68、DISTINCT 对中间表 Temp6进行去重，产生中间表 Temp79、ORDER BY 对Temp7中的数据进行排序，产生中间表Temp810、LIMIT 对中间表Temp8进行分页，产生中间表Temp9
多表练习题
第一部分-- 部门表 CREATE TABLE DEPT( DEPTNO INT PRIMARY KEY, -- 部门编号 DNAME VARCHAR(14) , -- 部门名称 LOC VARCHAR(13) ) ; -- 部门地址INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');-- 员工表CREATE TABLE EMP ( EMPNO INT PRIMARY KEY, -- 员工编号 ENAME VARCHAR(10), -- 员工名称 JOB VARCHAR(9), -- 工作 MGR DOUBLE, -- 直属领导编号 HIREDATE DATE, -- 入职时间 SAL DOUBLE, -- 工资 COMM DOUBLE, -- 奖金 DEPTNO INT, -- 部门号 FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)); INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); -- 工资等级表CREATE TABLE SALGRADE ( GRADE INT, -- 工资等级 LOSAL DOUBLE, -- 最低工资 HISAL DOUBLE ); -- 最高工资INSERT INTO SALGRADE VALUES (1,700,1200);INSERT INTO SALGRADE VALUES (2,1201,1400);INSERT INTO SALGRADE VALUES (3,1401,2000);INSERT INTO SALGRADE VALUES (4,2001,3000);INSERT INTO SALGRADE VALUES (5,3001,9999);-- 简单1、查找部门30中员工的详细信息。2、找出从事clerk工作的员工的编号、姓名、部门号。3、检索出奖金多于基本工资的员工信息。4、检索出奖金多于基本工资60%的员工信息。5、找出10部门的经理、20部门的职员 的员工信息。6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。7、找出获得奖金的员工的工作。8、找出奖金少于100或者没有获得奖金的员工的信息。9、找出姓名以A、B、S开始的员工信息。10、找到名字长度为7个字符的员工信息。11、名字中不包含R字符的员工信息。12、返回员工的详细信息并按姓名排序。13、返回员工的信息并按员工的工作年限降序排列。14、返回员工的信息并按工作降序工资升序排列。15、计算员工的日薪(按30天)。16、找出姓名中包含A的员工信息。-- 中等1、返回拥有员工的部门名、部门号。(dept,emp) 2、工资水平多于smith的员工信息。3、返回员工和所属经理的姓名。（自连接）4、返回 雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。(在日期类型可以直接比较)5、返回员工姓名及其所在的部门名称。6、返回从事clerk工作的员工姓名和所在部门名称。7、返回部门号及其本部门的最低工资。8、返回销售部(sales)所有员工的姓名。9、返回工资多于平均工资的员工。10、返回与SCOTT从事相同工作的员工。11、返回与30部门员工工资水平相同的员工姓名与工资。12、返回工资高于30部门所有员工工资水平的员工信息。13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。14、返回员工的姓名、所在部门名及其工资。15、返回员工的详细信息。(包括部门名)16、返回员工工作及其从事此工作的最低工资。17、返回不同部门经理的最低工资。18、计算出员工的年薪，并且以年薪排序。19、返回工资处于第四级别的员工的姓名。-- 简单答案1、查找部门30中员工的详细信息。#1.条件:部门号为30SELECT * FROM emp WHERE DEPTNO=30;2、找出从事clerk工作的员工的编号、姓名、部门号。#1.先找到条件，再找出对应的字段SELECT EMPNO,ENAME,DEPTNO FROM emp WHERE JOB='CLERK';3、检索出奖金多于基本工资的员工信息。#1.条件COMM>SALSELECT * FROM emp WHERE COMM>SAL;4、检索出奖金多于基本工资60%的员工信息。#1.条件COMM>(SAL*06)SELECT * FROM emp WHERE COMM>(SAL*0.6);5、找出10部门的经理、20部门的职员 的员工信息。#1.条件部门号10并且是经理 或者 部门号20并且是员工SELECT * FROM emp WHERE DEPTNO=10 AND JOB='MANAGER' OR DEPTNO=20 AND JOB='CLERK';6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。#1.在上题的条件后再加一个条件SELECT * FROM emp WHERE DEPTNO=10 AND JOB='MANAGER' OR DEPTNO=20 AND JOB='CLERK' OR JOB!='MANAGER' AND JOB!='CLERK' AND SAL>2000;7、找出获得奖金的员工的工作。#1.奖金大于0SELECT DISTINCT JOB FROM emp WHERE COMM>0;8、找出奖金少于100或者没有获得奖金的员工的信息。#1.奖金小于100或者为NULLSELECT * FROM emp WHERE COMM<100 OR COMM IS NULL;9、找出姓名以A、B、S开始的员工信息。#1.LIKESELECT * FROM emp WHERE ENAME LIKE 'A%' OR ENAME LIKE 'B%' OR ENAME LIKE 'S%';10、找到名字长度为7个字符的员工信息。#1.CHACATER_LENGTH()SELECT * FROM emp WHERE CHARACTER_LENGTH(ENAME)=7;#LIKESELECT * FROM emp WHERE ENAME LIKE '_______';11、名字中不包含R字符的员工信息。#NOT LIKESELECT * FROM emp WHERE ENAME NOT LIKE '%R%';12、返回员工的详细信息并按姓名排序。#ORDER BY SELECT * FROM emp ORDER BY ENAME;13、返回员工的信息并按员工的工作年限降序排列。#按照年限降序===按照入职日期的升序SELECT * FROM emp ORDER BY hiredate;14、返回员工的信息并按工作降序工资升序排列。SELECT * FROM emp ORDER BY JOB DESC,SAL ASC;15、计算员工的日薪(按30天)。SELECT FORMAT(sal/30,2) FROM emp ;16、找出姓名中包含A的员工信息。SELECT * FROM emp WHERE ENAME LIKE '%A%';--中等答案1、返回拥有员工的部门名、部门号。(dept,emp) #1.部门名和部门号需要在dept和emp表中拿SELECT DISTINCT emp.DEPTNO,dept.DNAME FROM emp JOIN dept ON emp.DEPTNO=dept.DEPTNO;2、工资水平多于smith的员工信息。#1.找出smith的工资水平所在区间的最高薪水SELECT a1.hisal FROM emp JOIN salgrade a1 ON emp.SAL BETWEEN a1.losal AND a1.hisal AND emp.ENAME='smith';#2.找出高于这个薪水的员工信息SELECT * FROM emp WHERE sal>(SELECT a1.hisal FROM emp JOIN salgrade a1 ON emp.SAL BETWEEN a1.losal AND a1.hisal AND emp.ENAME='smith')3、返回员工和所属经理的姓名。（自连接）#1.emp表和自己连接，表a作为经理表，表b作为员工表SELECT b.ename AS 员工,a.ename AS 经理 FROM emp a JOIN emp b ON a.empno=b.mgr;4、返回雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。(在日期类型可以直接比较)#1.在上题的前提下加一个条件SELECT b.ename AS 员工,a.ename AS 经理 FROM emp a JOIN emp b ON a.empno=b.mgr AND b.hiredate>a.hiredate;5、返回员工姓名及其所在的部门名称。#1.根据部门号连接员工表和部门表SELECT a.ENAME,b.DNAME FROM emp a JOIN dept b ON a.DEPTNO=b.DEPTNO;6、返回从事clerk工作的员工姓名和所在部门名称。#1.在上题的前提下加一个条件SELECT a.ENAME,b.DNAME FROM emp a JOIN dept b ON a.DEPTNO=b.DEPTNO AND a.JOB='clerk';7、返回部门号及其本部门的最低工资。#1.按部门号分组，求最低工资SELECT DEPTNO,MIN(SAL) FROM emp GROUP BY DEPTNO;8、返回销售部(sales)所有员工的姓名。#1.找出销售部的部门号SELECT DEPTNO FROM dept WHERE DNAME='SALES';#2.根据部门号找到员工信息SELECT * FROM emp WHERE DEPTNO=(SELECT DEPTNO FROM dept WHERE DNAME='SALES')9、返回工资多于平均工资的员工。#1.算出平均工资SELECT AVG(SAL) FROM emp;#2.找出工资大于平均工资的员工SELECT * FROM emp WHERE SAL>(SELECT AVG(SAL) FROM emp)10、返回与SCOTT从事相同工作的员工。#1.找出SCOTT从事的工作SELECT JOB FROM emp WHERE ENAME='scott';#2.找出与SCOTT从事相同工作的员工SELECT * FROM emp WHERE JOB=(SELECT JOB FROM emp WHERE ENAME='scott') AND ENAME!='scott'11、返回与30部门员工工资水平相同的员工姓名与工资。#1.找出30部门所在的工资水平SELECT DISTINCT grade FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a1.DEPTNO=30;#2.找出其他部门的薪资水平SELECT DISTINCT a2.grade,a1.ENAME,a1.SAL FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a1.DEPTNO!=30;#3.找出其他部门和30部门薪资水平相同的员工SELECT * FROM(SELECT DISTINCT a2.grade,a1.ENAME,a1.SAL FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a1.DEPTNO!=30)a3 WHERE a3.grade IN (SELECT DISTINCT grade FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a1.DEPTNO=30)12、返回工资高于30部门所有员工工资水平的员工信息。#1.找出30部门所在的最高工资水平SELECT MAX(grade) FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a1.DEPTNO=30;#2.找出其他部门的薪资水平SELECT DISTINCT a2.grade,a1.ENAME,a1.SAL FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a1.DEPTNO!=30;#3.找出其他部门大于30部门薪资水平的员工SELECT * FROM(SELECT DISTINCT a2.grade,a1.ENAME,a1.SAL FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a1.DEPTNO!=30)a3 WHERE a3.grade > (SELECT MAX(grade) FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a1.DEPTNO=30)13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。#1.先根据部门号分组，找出每个部门的员工总数SELECT DEPTNO,COUNT(*)AS 员工总数 FROM emp GROUP BY DEPTNO;#2.关联部门表，找到部门名和所在位置SELECT a1.DEPTNO,a1.员工总数,a2.DNAME,a2.LOC FROM (SELECT DEPTNO,COUNT(*)AS 员工总数 FROM emp GROUP BY DEPTNO)a1JOIN dept a2 ON a1.DEPTNO=a2.DEPTNO;#一步的写法SELECT a1.DEPTNO,COUNT(*),a2.DNAME,a2.LOC FROM emp a1 JOIN dept a2 ON a1.DEPTNO=a2.DEPTNO GROUP BY a1.DEPTNO;14、返回员工的姓名、所在部门名及其工资。#1.根据部门号连接员工表和部门表SELECT a1.ENAME,a1.SAL,a2.DNAME FROM emp a1 JOIN dept a2 ON a1.DEPTNO=a2.DEPTNO;15、返回员工的详细信息。(包括部门名)#和上题一样SELECT a1.*,a2.DNAME FROM emp a1 JOIN dept a2 ON a1.DEPTNO=a2.DEPTNO;16、返回员工工作及其从事此工作的最低工资。#1.按工作分组SELECT JOB,MIN(SAL) FROM emp GROUP BY JOB;17、返回不同部门经理的最低工资。#1.先找出经理，再按部门分组SELECT DEPTNO,MIN(SAL) FROM emp WHERE JOB='MANAGER' GROUP BY DEPTNO;18、计算出员工的年薪，并且以年薪排序。#1.薪水*12SELECT SAL*12 FROM emp ORDER BY (SAL*12)DESC;19、返回工资处于第四级别的员工的姓名。#1.连接条件，员工表的薪资在薪资表的最低和最高薪水区间内并且薪资表的级别为4SELECT a1.ENAME,a2.grade FROM emp a1 JOIN salgrade a2 ON a1.SAL BETWEEN a2.LOSAL AND a2.HISAL AND a2.grade=4;第二部分-- 1.学生表 Student(SId,Sname,Sage,Ssex)-- SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别-- 2.课程表 Course(CId,Cname,TId) --CId --课程编号,Cname 课程名称,TId 教师编号-- 3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名-- 4.成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吴兰' , '1992-03-01' , '女');insert into Student values('07' , '郑竹' , '1989-07-01' , '女');insert into Student values('09' , '张三' , '2017-12-20' , '女');insert into Student values('10' , '李四' , '2017-12-25' , '女');insert into Student values('11' , '李四' , '2017-12-30' , '女');insert into Student values('12' , '赵六' , '2017-01-01' , '女');insert into Student values('13' , '孙七' , '2018-01-01' , '女');create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');create table Teacher(TId varchar(10),Tname varchar(10))insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');create table SC(SId varchar(10),CId varchar(10),score decimal(18,1))insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩3.查询在 SC 表存在成绩的学生信息4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) 4.1 查有成绩的学生信息5.查询「李」姓老师的数量6.查询学过「张三」老师授课的同学的信息7.查询没有学全所有课程的同学的信息8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息9 🚹9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息10.查询没学过"张三"老师讲授的任一门课程的学生姓名11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩14.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列16.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比18.查询每门课程被选修的学生数19.查询出只选修两门课程的学生学号和姓名20.查询男生、女生人数21.查询名字中含有「风」字的学生信息22.查询同名同性学生名单，并统计同名人数23.查询 1990 年出生的学生名单24.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列25.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩26.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数28.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数29.查询不及格的课程30.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名31.求每门课程的学生人数32.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩33.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩36.统计每门课程的学生选修人数（超过 5 人的课程才统计）。37.检索至少选修两门课程的学生学号38.查询选修了全部课程的学生信息39.查询各学生的年龄，只按年份来算-- 答案1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数#1 找出选择了01课的学生信息SELECT * FROM sc WHERE Cid=01;#2 找出选择了02课的学生信息SELECT * FROM sc WHERE Cid=02;#3 两个结果关联，找出课程1和课程2都选了的学生 并找出满足条件的数据SELECT a1.Sid,a1.Cid,a1.Score,a2.Cid,a2.Score FROM (SELECT * FROM sc WHERE Cid=01)a1JOIN(SELECT * FROM sc WHERE Cid=02)a2ON a1.Sid=a2.Sid AND a1.score>a2.score;2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩#1.按照学生编号分组，找出平均分大于60的学生成绩和对应编号SELECT Sid,AVG(score) AS avg_sc FROM sc GROUP BY Sid HAVING AVG(score)>=60;#2.通过学号和学生信息表关联，找出对应学生姓名。SELECT a1.Sid,a2.Sname,a1.avg_sc FROM (SELECT Sid,AVG(score) AS avg_sc FROM sc GROUP BY Sid HAVING AVG(score)>=60)a1JOIN student a2 ON a1.Sid=a2.Sid;3.查询在 SC 表存在成绩的学生信息#1.找出有成绩的学号信息，并进行去重。SELECT DISTINCT Sid FROM sc;#2.进行子查询，在student表中满足Sid在上面结果中的数据。SELECT * FROM student WHERE Sid IN(SELECT DISTINCT Sid FROM sc);4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )#1.查询学生编号，选课总数，和总成绩SELECT Sid,COUNT(Cid)AS cou_count,SUM(score)AS cou_sum FROM sc GROUP BY Sid;#2.外连接，保留学生表的所有信息SELECT a1.Sname,a1.SId,a2.cou_count,a2.cou_sum FROM student a1 LEFT JOIN (SELECT Sid,COUNT(Cid)AS cou_count,SUM(score)AS cou_sum FROM sc GROUP BY Sid) a2ON a1.SId=a2.Sid;5.查询「李」姓老师的数量SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%';6.查询学过「张三」老师授课的同学的信息#1.找到张三老师的工号SELECT Tid FROM teacher WHERE Tname='张三'#2.根据张三老师的工号找到他教的课程号SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三')#3.找出成绩表中02课程有成绩的学号SELECT Sid FROM sc WHERE Cid IN(SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三'))#4.根据找到的学号，找出学生信息SELECT * FROM student WHERE Sid IN (SELECT Sid FROM sc WHERE Cid IN(SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三')))7.查询没有学全所有课程的同学的信息#1.查找所有课程一共有几门SELECT COUNT(*) FROM course;#2.查找成绩表中每个学生学习的课程数，并和总课程数对比，找出没有学全的。SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(*)<(SELECT COUNT(*) FROM course)#3.根据找到的学号，拿出学生信息SELECT * FROM student WHERE Sid IN (SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(*)<(SELECT COUNT(*) FROM course));8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息#1.找出学号为01的同学选的课程SELECT Cid FROM sc WHERE Sid=1;#2.选出至少1门和01同学所学课程相同的同学的学号SELECT DISTINCT Sid FROM sc WHERE Cid IN (SELECT Cid FROM sc WHERE Sid=1)AND Sid!='01';#3.根据上边拿出来的学号，找到对应的学生信息SELECT * FROM student WHERE Sid IN (SELECT DISTINCT Sid FROM sc WHERE Cid IN (SELECT Cid FROM sc WHERE Sid=1)AND Sid!='01')9.查询和"01"号的同学学习的课程 完全相同的其他同学的信息#1.找出01号同学学的课程SELECT Cid FROM sc WHERE Sid=1;#2.找出和01同学学的课程不同的同学学号SELECT Sid FROM sc WHERE Cid NOT IN(SELECT Cid FROM sc WHERE Sid=1);#3.排除以上的学生SELECT * FROM sc WHERE Sid NOT IN(SELECT Sid FROM sc WHERE Cid NOT IN(SELECT Cid FROM sc WHERE Sid=1))#4.找出选课数量和01同学相同的学生学号SELECT Sid FROM (SELECT * FROM sc WHERE Sid NOT IN(SELECT Sid FROM sc WHERE Cid NOT IN(SELECT Cid FROM sc WHERE Sid=1)))t1GROUP BY Sid HAVING COUNT(t1.Cid)=(SELECT COUNT(t2.Cid) FROM (SELECT Cid FROM sc WHERE Sid=1)t2 )#5.根据学号找出学生信息SELECT * FROM student WHERE Sid IN (SELECT Sid FROM (SELECT * FROM sc WHERE Sid NOT IN(SELECT Sid FROM sc WHERE Cid NOT IN(SELECT Cid FROM sc WHERE Sid=1)))t1GROUP BY Sid HAVING COUNT(t1.Cid)=(SELECT COUNT(t2.Cid) FROM (SELECT Cid FROM sc WHERE Sid=1)t2 ))10.查询没学过"张三"老师讲授的任一门课程的学生姓名#1.查找张三老师的工号SELECT Tid FROM teacher WHERE Tname='张三';#2.查找张三老师教的课程SELECT Cid FROM course WHERE Tid = (SELECT Tid FROM teacher WHERE Tname='张三');#3.查找学过张三老师课程的学生SELECT DISTINCT Sid FROM sc WHERE Cid IN (SELECT Cid FROM course WHERE Tid = (SELECT Tid FROM teacher WHERE Tname='张三'));#4.根据学号找到学生信息SELECT * FROM student WHERE Sid IN (SELECT DISTINCT Sid FROM sc WHERE Cid IN (SELECT Cid FROM course WHERE Tid = (SELECT Tid FROM teacher WHERE Tname='张三')));11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩#1.查找所有不及格的信息SELECT * FROM sc WHERE score<60;#2.根据学号分组，拿出学号和平均成绩SELECT a1.Sid,AVG(a1.score) FROM (SELECT * FROM sc WHERE score<60)a1GROUP BY a1.Sid#3.根据学号找到姓名SELECT a3.Sname,a4.* FROM student a3 JOIN (SELECT a1.Sid,AVG(a1.score) FROM (SELECT * FROM sc WHERE score<60)a1GROUP BY a1.Sid) a4 ON a3.Sid=a4.Sid;12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息#1.找出学了01课程并且分数小于60的学生SELECT * FROM sc WHERE Cid='01' AND score<60;#2.根据学号找到学生的信息SELECT a1.* FROM student a1 JOIN (SELECT * FROM sc WHERE Cid='01' AND score<60)a2ON a1.Sid=a2.Sid;13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩#1.先查出学号和对应的平均成绩SELECT Sid,AVG(score)AS avg_sc FROM sc GROUP BY Sid;#2.用成绩表进行左外连接，拼接平均分SELECT sc.*,a1.avg_sc FROM sc LEFT JOIN (SELECT Sid,AVG(score)AS avg_sc FROM sc GROUP BY Sid)a1ON sc.Sid=a1.Sid ORDER BY a1.avg_sc DESC;14.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列SELECT MAX(score)AS 最高分 ,MIN(score)AS 最低分,AVG(score)AS 平均分,SUM(IF(score>60,1,0))/COUNT(*) AS 及格率,SUM(IF(score>70,1,0))/COUNT(*) AS 中等率,SUM(IF(score>80,1,0))/COUNT(*) AS 优良率,SUM(IF(score>90,1,0))/COUNT(*) AS 优秀率,Cid AS 课程号,COUNT(*)AS 选修人数FROM sc GROUP BY Cid ORDER BY 选修人数 DESC, 课程号;16.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比#1.按科目分组 用每个分数段的人数除以总人数SELECT Cid,SUM(IF(score<60,1,0))/COUNT(*) AS '0-60',SUM(IF(score>=60 AND score<70,1,0))/COUNT(*) AS '60-70',SUM(IF(score>=70 AND score<85,1,0))/COUNT(*) AS '70-80',SUM(IF(score>=85 AND score<100,1,0))/COUNT(*) AS '85-100' FROM sc GROUP BY Cid;#2.根据Cid，找到课程的名称SELECT a1.Cname,a2.* FROM Course a1 JOIN (SELECT Cid,SUM(IF(score<60,1,0))/COUNT(*) AS '0-60',SUM(IF(score>=60 AND score<70,1,0))/COUNT(*) AS '60-70',SUM(IF(score>=70 AND score<85,1,0))/COUNT(*) AS '70-80',SUM(IF(score>=85 AND score<100,1,0))/COUNT(*) AS '85-100' FROM sc GROUP BY Cid) a2ON a1.Cid=a2.Cid;18.查询每门课程被选修的学生数#1.按课程分组SELECT Cid,Count(*) FROM sc GROUP BY Cid;19.查询出只选修两门课程的学生学号和姓名#1.按学号分组，统计选修了两门课的学生学号SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(*)=2;#2.根据学号找到学生的姓名SELECT Sid,Sname FROM student WHERE Sid IN (SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(*)=2)20.查询男生、女生人数#1.根据性别分组 查出男生女生人数SELECT Ssex,COUNT(*) FROM student GROUP BY Ssex;21.查询名字中含有「风」字的学生信息#1.模糊查询SELECT * FROM student WHERE Sname LIKE '%风%';22.查询同名同性学生名单，并统计同名人数#1.按照性别和姓名分组，统计人数SELECT Ssex,Sname,COUNT(*) FROM student GROUP BY Ssex,Sname;23.查询 1990 年出生的学生名单#1.查找学生出生的年份SELECT DATE_FORMAT(Sage,'%Y'),Sname FROM student WHERE DATE_FORMAT(Sage,'%Y')='1990'24.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列#1.按课程号分组 查找平均成绩SELECT Cid,AVG(score)AS avg_score FROM sc GROUP BY Cid ORDER BY avg_score DESC,Cid;25.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩#1.根据学号分组，先拿到学号和平均成绩SELECT Sid,AVG(score) FROM sc GROUP BY Sid HAVING AVG(score) >= 85;#2.和学生信息表join,拿出学生姓名SELECT a1.Sname,a2.* FROM student a1 JOIN (SELECT Sid,AVG(score) FROM sc GROUP BY Sid HAVING AVG(score) >= 85) a2ON a1.SId=a2.Sid;26.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数#1.先根据课程名 找到对应的课程号SELECT Cid FROM course WHERE Cname='数学';#2.根据课程号找到对应的学生学号以及分数SELECT Sid,score FROM sc WHERE Cid=(SELECT Cid FROM course WHERE Cname='数学') AND score<60;#3.根据学号找到对应的学生姓名SELECT a1.Sname,a2.* FROM student a1 JOIN (SELECT Sid,score FROM sc WHERE Cid=(SELECT Cid FROM course WHERE Cname='数学') AND score<60) a2 ON a1.Sid=a2.Sid;28.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数#1.把所有分数大于70的课程查找出来SELECT * FROM sc WHERE score>70;#2.和学生表连接，查找学生姓名SELECT a1.Sname,a2.* FROM student a1 JOIN (SELECT * FROM sc WHERE score>70) a2ON a1.Sid=a2.Sid;#3.和课程表连接，查找课程名SELECT a4.Sname,a3.Cname,a4.score FROM course a3 JOIN (SELECT a1.Sname,a2.* FROM student a1 JOIN (SELECT * FROM sc WHERE score>70) a2ON a1.Sid=a2.Sid)a4ON a3.CId=a4.Cid;29.查询不及格的课程SELECT * FROM sc WHERE score<60;30.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名#1.先查找课程号1且分数80以上的学号SELECT Sid FROM sc WHERE Cid='01' AND score>80;#2.根据学号找到学生姓名SELECT Sid,Sname FROM student WHERE Sid IN(SELECT Sid FROM sc WHERE Cid='01' AND score>80)31.求每门课程的学生人数#1.按课程号分组，COUNT() 统计人数SELECT Cid,COUNT(*) FROM sc GROUP BY Cid;32.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩#1.先确定张三老师的工号SELECT Tid FROM teacher WHERE Tname='张三';#2.找到张三老师教的课程SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三');#3.根据课程号找到最高成绩SELECT MAX(score) AS max_sc FROM sc WHERE Cid=(SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三'))#4.根据最高成绩找到学号SELECT Sid,score FROM sc WHERE score=(SELECT MAX(score) AS max_sc FROM sc WHERE Cid=(SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三')))#5.根据学号找到学生信息SELECT a1.Sname,a2.score FROM student a1 JOIN (SELECT Sid,score FROM sc WHERE score=(SELECT MAX(score) AS max_sc FROM sc WHERE Cid=(SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三'))))a2 ON a1.Sid=a2.Sid;33.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩#1.先确定张三老师的工号SELECT Tid FROM teacher WHERE Tname='张三';#2.找到张三老师教的课程SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三');#3.根据课程号找到最高成绩SELECT MAX(score) AS max_sc FROM sc WHERE Cid=(SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三'))#4.根据最高成绩找到学号SELECT Sid,score FROM sc WHERE score IN(SELECT MAX(score) AS max_sc FROM sc WHERE Cid=(SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三')))#5.根据学号找到学生信息SELECT a1.Sname,a2.score FROM student a1 JOIN (SELECT Sid,score FROM sc WHERE score=(SELECT MAX(score) AS max_sc FROM sc WHERE Cid=(SELECT Cid FROM course WHERE Tid=(SELECT Tid FROM teacher WHERE Tname='张三'))))a2 ON a1.Sid=a2.Sid;36.统计每门课程的学生选修人数（超过 5 人的课程才统计）。#1.按课程号分组，加一个count超过5人的条件SELECT Cid,COUNT(*) FROM sc GROUP BY Cid HAVING COUNT(*)>5;37.检索至少选修两门课程的学生学号#1.按学号分组，加一个count>2的条件SELECT Sid,COUNT(*) FROM sc GROUP BY Sid HAVING COUNT(*)>2;38.查询选修了全部课程的学生信息#1.先查出一共有几门课程SELECT COUNT(*) FROM course;#2.按学号分组，加一个count等于课程数的条件SELECT Sid,COUNT(*) FROM sc GROUP BY Sid HAVING COUNT(*)=(SELECT COUNT(*) FROM course)39.查询各学生的年龄，只按年份来算#1.先查看表里的日期格式，取出其中的年份,然后做一个日期减法SELECT Sname,DATE_FORMAT(CURRENT_DATE(),'%Y')-DATE_FORMAT(Sage,'%Y') FROM student ;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rYiIH2iz-1622937857786)(D:\ChenTyporNote\typroImg\image-20210603165221090.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2Rplwp39-1622937857787)(D:\ChenTyporNote\typroImg\image-20210603165349271.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-auR6rkYD-1622937857787)(D:\ChenTyporNote\typroImg\image-20210603174952793.png)]
3.3 事务
3.3.1 什么是事务
数据库中的事务是指对数据库执行一批操作，这些操作最终要么全部执行成功，要么全部
失败，不会存在部分成功的情况。
举个例子
⽐如A用户给B用户转账100操作，过程如下：
1.从A账户扣100
2.给B账户加100
如果在事务的支持下，上⾯最终只有2种结果：
-
操作成功：A账户减少100；B账户增加100
-
操作失败：A、B两个账户都没有发⽣变化
如果没有事务的支持，可能出现错：A账户减少了100，此时系统挂了，导致B账户没有加
上100，A账户凭空少了100
3.3.2事务的几个特性（ACID）
-
原子性 (Atomicity)
事务的整个操作就像原子操作一样，最终要么全部成功，要么全部失败，这个原子性是从最终结果来看的，从最终结果看这个过程是不可再分的。
-
一致性(Consistency)
一个事务必须使数据库从一个一致性状态转换到另一个一致性状态。
所谓一致性，指的是数据处于一个有意义的状态，最常见的例子就是转账，如果从A账户转入B账户一笔钱，A账户减少的同时B账户必须增加相同钱数，这样我们称之为有意义的。从实际的业务逻辑来说，就是最终结果和程序员期望的结果是一致的，完全符合的。
-
隔离性(Isolation)
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不行互相干扰。
-
持久性(Durability)
一个事务一旦提交 commit，他对数据库中数据的改变就是永久性的。当事务提交之后，数据会持久化到硬盘，修改是永久性的。
3.3.3 MySQL中事务的操作
Mysql中的事务分为显式事务和隐式事务。
Mysql中默认是隐式事务，执行insert,update,delete操作的时候，数据库自动开启事务，提交或回滚事务。
是否开启显式事务是有autocommit控制的。
3.3.4 隐式事务
事务自动开启，提交和回滚，通过查看变量autocommit判断是否开启了自动提交
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3NCIKxKe-1622937857788)(D:\ChenTyporNote\typroImg\image-20210603093338904.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YzQKItqR-1622937857789)(MySQL教程.assets/image-20210402174825621.png)]
3.3.5 显式事务
事务需要手动开启，提交或回滚，由开发者自己控制。
-- 方式一 把自动提交变量设置为OFF，手动提交。SET AUTOCOMMIT=0; 或者off-- 方式一 把自动提交变量设置为1 on，自动提交。SET AUTOCOMMIT=1; 或者on执行事务|回滚事务COMMIT|ROLLBACK/**测试**/-- 创建一个表CREATE TABLE test1(a int);SELECT * FROM test1;-- 设置自动提交变量为OFFSET AUTOCOMMIT=0;-- 向表中插入数据INSERT INTO test1 VALUES(1);INSERT INTO test1 VALUES(2);-- 查找数据 此时应该是查找不到的，因为还没有进行提交。SELECT * FROM test1;-- 提交COMMIT;-- 再次查找 此时能够查找到数据。SELECT * FROM test1;-- 向表中插入数据INSERT INTO test1 VALUES(3);INSERT INTO test1 VALUES(4);-- ROLLBACK 回滚ROLLBACK;-- 此时3和4应该没有插入到表中。SELECT * FROM test1;-- 最后记得将自动提交变量设置回来。SET AUTOCOMMIT=1;
-- 方式二 通过 START TRANSACTION 开启一个事务 遇到commit结束事务,后面的都是自动提交,两个结合必须使用执行事务|回滚事务COMMIT|ROLLBACK /**测试**/-- 创建一个表CREATE TABLE test2(a int);SELECT * FROM test2;-- 开启一个事务START TRANSACTION;-- 向表中插入数据INSERT INTO test2 VALUES(1);-- 查找数据 此时应该是查找不到的，因为还没有进行提交。SELECT * FROM test2;-- 提交 COMMIT;-- 当提交以后，这次事务就结束了，如果想测试ROLLBACK，需要开启一次新的事务START TRANSACTION;INSERT INTO test2 VALUES(2);COMMIT;
3.3.6 SAVEPOINT关键字
在事务中我们执行了一大批操作，可能我们只想回滚部分数据，怎么做呢?我们可以将一大批操作分为几个部分，然后指定回滚某个部分，可以用savepoint来实现，下面演示效果：-- 创建一个表用来测试CREATE TABLE test3( a INT);-- 开启事务START TRANSACTION;-- 插入一条数据INSERT INTO test3 VALUES(1);-- 设置一个保存点SAVEPOINT save1;-- 再插入一条数据INSERT INTO test3 VALUES(2);-- 回滚到save1;ROLLBACK TO save1;-- 提交COMMIT;-- 此时表中的数据只有1。SELECT * FROM test3;
3.3.7 事务中可能出现的问题:了解
-
脏读 读脏数据
张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，
事务B正在读取张三的工资，读取到张三的工资为8000。
随后，
事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，
事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hdDcAFuZ-1622937857789)(MySQL教程.assets/image-20210406100524742.png)]
-
不可重复读
一个事务前后多次读取同一条数据，但前后数据不一致。
事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qki396eQ-1622937857790)(MySQL教程.assets/image-20210406100836578.png)]
-
幻读
指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例如：
目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，
事务B插入一条工资也为5000的记录。
这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。
幻读和不可重复读的区别：
同样的条件，你读取过的数据，再次读取出来发现值不一样了，幻读的重点在于新增或者删除： 同样的条件，第 1 次和第 2 次读出来的记录数不一样。
3.4 视图:view 本质是一张虚拟表,不会在表中出现.不能通过视图修改原始表的数据
3.4.1 背景
电商公司领导说：给我统计一下：当月订单总金额、订单量、男女订单占比等信息，我们 啪啦啪啦写了一堆很复杂的sql，然后发给领导。 这样一大片sql，发给领导，效果会好吗？ 如果领导只想看其中某个数据，还需要修改你发来的sql，领导以后想新增其他的统计指 标，你又会发送一大坨sql给领导，对于领导来说这个sql看起来很复杂，难以维护。 实际上领导并不关心你是怎么实现的，他关心的只是这些指标，并且方便查看、查询，而你却把复杂的实现都发给了领导。
又或者一个表里有几个字段属于高度机密，不能被用户所查看，但是用户需要查看部分字段，这个时候怎么办？
那我们有什么办法隐藏这些细节，只暴露简洁的结果呢？
3.4.2 使用
-- 创建视图的语法CREATE VIEW AS 查询语句;-- 查询姓名中包含A字符的员工名、部门号、工作信息CREATE VIEW myv1 AS SELECT ENAME,DEPTNO,JOB FROM emp WHERE ENAME LIKE '%A%';-- 查询myv1中的数据SELECT * FROM myv1;-- 修改视图 可以不用上面的创建语法,有这个视图就替换,没有就创建CREATE OR REPALCE VIEW 视图名 AS 查询语句。-- 删除视图DROP VIEW 视图名1 [,视图名2] [,视图名3]-- 查看已经创建的视图show table status where comment='view';或着select * from 视图名;
3.4.3 好处
1、安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2、性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3、灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
3.5 存储过程 :就是方法(函数)
3.5.1 使用说明
存储过程是数据库的一个重要的对象，可以封装SQL语句集，可以用来完成一些较复杂的业务逻辑，并且可以入参出参(类似于java中的方法的书写)。
创建时会预先编译后保存，用户后续的调用都不需要再次编译。
//把editUser类比成一个存储过程public void editUser(User user,String username){ String a = "nihao"; user.setUsername(username);}main(){ User user = new User(); editUser(user,"张三"); user.getUseranme(); //java基础还记得不}
大家可能会思考，用sql处理业务逻辑还要重新学，我用java来处理逻辑（比如循环判断、循环查询等）不行吗？那么，为什么还要用存储过程处理业务逻辑呢？
优点： 在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。 执行速度快，存储过程经过编译之后会比单独一条一条执行要快。 减少网络传输流量。 方便优化。缺点： 过程化编程，复杂业务处理的维护成本高。 调试不便 不同数据库之间可移植性差。-- 不同数据库语法不一致！
3.5.2 基本使用
-- 官方参考网址https://dev.mysql.com/doc/refman/5.6/en/sql-statements.htmlhttps://dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html-- 声明结束符delimiter $$ -- 语法结构CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body -- proc_parameter参数部分，可以如下书写： [ IN | OUT | INOUT ] param_name type -- type类型可以是MySQL支持的所有类型 -- routine_body(程序体)部分，可以书写合法的SQL语句 BEGIN ... END-- 简单演示-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突用delimiter把结束符改为除了;号的其他字符,最后要改回来调用这个函数用calldelimiter $$CREATE PROCEDURE hello_procedure ()BEGIN SELECT 'hello procedure';END $$delimiter ;delimiter //CREATE PROCEDURE hello_procedure ()BEGIN SELECT 'hello procedure';END //delimiter ;call hello_procedure();
3.5.3 变量及赋值
局部变量
用户自定义，在begin/end块中有效
语法：声明变量 declare var_name type [default var_value];举例：declare nickname varchar(32);
-- set赋值create procedure sp_var01()begin declare nickname varchar(32) default 'hangge'; set nickname = 'hanghang'; -- set nickname := 'SF'; select nickname;end$$
-- into赋值delimiter $$create procedure sp_var_into()begin declare emp_name varchar(32) default 'hangge' ; declare emp_no int default 0; select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839; select emp_no,emp_name;end$$
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LOzp26YZ-1622937857790)(D:\ChenTyporNote\typroImg\image-20210603114734720.png)]
用户变量
用户自定义，当前会话（连接）有效。set后面不能跟select
定义了函数,执行后才能生效
语法： @var_name不需要提前声明，使用即声明
-- 赋值delimiter $$create procedure sp_var02()begin set @nickname = 'ZS'; -- set nickname := 'SF';end$$call sp_var02() $$select @nickname$$ --可以看到结果
会话变量
由系统提供，当前会话（连接）有效
语法：@@session.var_name
show session variables; -- 查看会话变量select @@session.unique_checks; -- 查看某会话变量set @@session.unique_checks = 0; --修改会话变量
全局变量
由系统提供，整个mysql服务器有效
语法：@@global.var_name
-- 查看全局变量中变量名有char的记录show global variables like '%char%'; -- 查看全局变量character_set_client的值select @@global.character_set_client;
3.5.4 入参出参
-- 语法in | out | inout param_name type
举例
-- IN类型演示delimiter $$create procedure sp_param01(in age int)begin set @user_age = age;end$$call sp_param01(10) $$select @user_age$$删除这个函数,sql不能修改函数,只能删除后重写函数drop procedure 函数名;
-- OUT类型，只负责输出！-- 需求：输出传入的地址字符串对应的部门编号。delimiter $$create procedure sp_param02(in loc varchar(64),out dept_no int(11))begin select d.deptno into dept_no from dept d where d.loc = loc; --此处强调，要么表起别名，要么入参名不与字段名一致end$$delimiter ;--测试set @dept_no = 100;call sp_param01('DALLAS',@dept_no);select @dept_no;
-- INOUT类型 delimiter $$create procedure sp_param03(inout name varchar)begin set name = concat('hello' ,name);end$$delimiter ;set @user_name = 'NIANHANG';call sp_param03(@user_name);select @user_name;
3.5.5 流程控制-判断
if
-- 语法IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list]END IF
举例：
-- 前置知识点：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unitselect timestampdiff(year,e.hiredate,now()) from emp e where e.empno = '7499';-- 需求：入职年限<=38是新手 >38并且<=40老员工 >40元老delimiter $$create procedure sp_hire_if()begin declare result varchar(32); if timestampdiff(year,'2001-01-01',now()) > 40 then set result = '元老'; elseif timestampdiff(year,'2001-01-01',now()) > 38 then set result = '老员工'; else set result = '新手'; end if; select result;end$$delimiter ;
case 行转列,列转行
此语法是不仅可以用在存储过程，查询语句也可以用！
-- 语法：可写条件判断 when后 END CASE后加分号 只写一个caseCASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list]END CASE;
-- 需求：入职年限年龄<=38是新手 >38并 <=40老员工 >40元老delimiter $$create procedure sp_hire_case()begin declare result varchar(32); declare message varchar(64); case when timestampdiff(year,'2001-01-01',now()) > 40 then set result = '元老'; set message = '老爷爷'; when timestampdiff(year,'2001-01-01',now()) > 38 then set result = '老员工'; set message = '油腻中年人'; else set result = '新手'; set message = '萌新'; end case; select result;end$$delimiter ;
3.5.6 流程控制-循环
loop
-- 语法[begin_label:] LOOP statement_listEND LOOP [end_label]案例,test是循环名test:loop if caseend loop test
需要说明，loop是死循环，需要手动退出循环，我们可以使用leave来退出。
可以把leave看成我们java中的break；与之对应的，就有iterate（继续循环）——类比java的continue
--需求：循环打印1到10-- leave控制循环的退出delimiter $$create procedure sp_flow_loop()begin declare c_index int default 1; declare result_str varchar(256) default '1'; cnt:loop if c_index >= 10 then leave cnt; end if; set c_index = c_index + 1; set result_str = concat(result_str,',',c_index); end loop cnt; select result_str;end$$-- iterate + leave控制循环delimiter $$create procedure sp_flow_loop02()begin declare c_index int default 1; declare result_str varchar(256) default '1'; cnt:loop set c_index = c_index + 1; set result_str = concat(result_str,',',c_index); if c_index < 10 then iterate cnt; end if; -- 下面这句话能否执行到？什么时候执行到？ 当c_index < 10为false时执行 leave cnt; end loop cnt; select result_str; end$$
693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
