超全的SQL语句总结（2）

超全的SQL语句总结（2）

SQL查询语句（五）

1. 查询所有字段的所有数据

SELECT * 查询效率是最低的
SELECT * FROM 表名

2. 指定查询字段,只显示查询字段的数据

SELECT id,name FROM 表名;
查询结果,默认使用的是表结构中的字段名
可以给字段名设定别名
SELECT id as 序号,name as 姓名 FROM 表名;

3. 约束条件 WHERE 的查询

对于字段的约束条件,WHERE针对的都是字段的约束
SELECT * FROM user2 WHERE age > 25;
可以使用逻辑运算符,实现多个条件的查询
SELECT * FROM user2 WHERE sex=‘女’ AND age < 25 AND age > 20;

4. 子查询

将其他查询的结果,作为当前插叙的条件
查询,与 张三 是同一个城市的人员信息
第一步,应该先知道 张三 的所在城市
SELECT city FROM user2 WHERE name = ‘张三’;
第二步,应该查询,所在城市,匹配 张三 所在城市信息的员工
SELECT * FROM user2 WHERE city = ‘北京’;
实际项目中,不可能,执行两次操作,先查询一个结果,再按照这个结果,编辑第二条SQL语句
应该是将两个查询语句,结合为一条查询语句
SQL语句2,北京,这个数据结果,是SQL语句1,的执行结果
可以将 北京 使用 SQL语句1 来替换
但是 SQL语句的程序的执行顺序, = 等号 之后,必须是一个数据结果,不能是一个 SQL语句
需要使用 ( ) 将 = 等号 之后的 SQL语句 包裹,表示先执行( ) 中的程序
之后再执行 SQL语句 , 此时 = 等号之后,就是 ( ) 中 SQL语句的执行结果,也就是 ‘北京’ 这个内容
SELECT * FROM user2 WHERE city = (SELECT city FROM user2 WHERE name = ‘张三’);
这样 将 SQL语句2 的执行结果 作为 SQL语句1 的查询条件,这样的SQL语句,就称为子查询
查找,与 张三部门相同,城市相同,的女性员工信息
查询 张三 的城市 部门
查询城市
SELECT city FROM user2 WHERE name = ‘张三’;
查询部门
SELECT dep FROM user2 WHERE name = ‘张三’;
查询语句
SELECT * FROM user2 WHERE sex = ‘nv’ AND city = (SELECT city FROM user2 WHERE name = ‘赵志江’)
AND dep = (SELECT dep FROM user2 WHERE name = ‘张三’);
总结:子查询就是先写好查询条件的SQL语句,将写好的SQL语句,写在最终的查询语句中,使用( )包裹,表示先执行

5. 模糊查询

SELECT * FROM 表名 WHERE 字段 LIKE ‘%或_配合关键词’;
模糊查询真的是字段中的数据,进行模糊查询
LIKE 之后的模糊查询内容 , 必须是 ’ ’ 字符串形式 , 必修使用 引号包裹
SQL语句的模糊查询,理论上只能是匹配字符串,如果要匹配数字数值,高版本的MySQL数据库也是支持的,只是特殊情况下,有可能会查询出错误结果
查询姓名中,姓赵的员工信息
SELECT * FROM user2 WHERE name LIKE ‘赵%’;
查询姓名中,带有 的 这个字 的员工信息
SELECT * FROM user2 WHERE name LIKE ‘%的%’;

6. 分组查询 以及 聚合函数

将 自定字段的 数据 按照 数值内容进行分组,同一个分组中默认就显示一条数据
但是 一个 分组 中,实际存储的是n条信息
SELECT * FROM user2 GROUP BY city;

7. 聚合函数

聚合函数一般是配合分组查询使用的
是对分组中,所有的数据,执行的函数聚合函数一共有5个
COUNT(字段) 查询分组中的数据数量
MIN(字段) 查询分组中的所有数据的最小值
MAX(字段) 查询分组中的所有数据的最大值
AVG(字段) 查询分组中的所有数据的平均值
SUM(字段) 查询分组中的所有数据的数值和
查询 每个城市中,最年轻的员工信息
每个城市中 — 要按照 城市 字段 进行分组
最年轻的 — age字段 的最小值
SELECt MIN(age) , id , name , city , sex , pay FROM user2 GROUP BY city;
说明 SELECT 和 FROM 之间设定的内容,就是你查询结果,显示的内容
实际项目中,应该设定什么,应该显示什么,要根据不同的项目而定,目前,我们看什么顺眼就写什么
只要是符合语法规范就可以
分组查询和聚合函数
分组查询和聚合函数是后台管理项目中,常用的查询方式
例如: 查询 每个部门的平均薪资,平均年龄,最高薪资,最大年龄等等信息
都是使用分组查询和聚合函数的形式
一些数据分析,往往也是使用类似的语法形式
例如: 90后 最喜欢男演员
00后 最喜欢听的歌曲
关键点是分清楚 分组的字段 和 聚合函数的字段
每个城市的,员工的平均年龄
分组是 city 字段 聚合函数是 age 字段
SELECT id,name,sex,city, AVG(age) FROM user2 GROUP BY city;
可以定义别名
SELECT id,name,sex,city, AVG(age) as 平均年龄 FROM user2 GROUP BY city;
每个城市的,员工的平均薪资
分组是 city 字段 聚合函数是 pay 字段
SELECT city , AVG(pay) as 平均薪资 FROM user2 GROUP BY city;
每个部门的,员工的平均薪资
分组是 dep 字段 聚合函数是 pay 字段
SELECT dep , AVG(pay) as 平均薪资 FROM user2 GROUP BY dep;
每个部门的,员工的平均年龄
分组是 dep 字段 聚合函数是 age 字段
SELECT dep , AVG(age) as 平均年龄 FROM user2 GROUP BY dep;
每个部门的,员工的人数 — 查询数据数量,一般使用 id字段 因为id字段是唯一不重复的数据
分组是 dep 字段 聚合函数是 id 字段
SELECT dep , COUNT(id) as 员工人数 FROM user2 GROUP BY dep;
分清: GROUP BY 后 分组查询的字段
使用哪个聚合函数
聚合函数()中的字段
聚合函数也可以独立使用,不和分组查询使用,那么就是求所有数据的聚合函数结果
所有员工的平均薪资和平均年龄
SELECT AVG(pay) , AVG(age) FROM user2;
没有分组,就是对所有数据执行 聚合函数
分组查询,聚合函数,WHERE约束条件的配合使用
查询,每个部门,18岁以下员工的数量
此时查询就要有约束条件,18岁以下,作为约束条件 WHERE age < 18
每个部门,就是按照部门分组
员工数量, COUNT(id)
SELECT dep,COUNT(id) as 人数 FROM user2 WHERE age < 18 GROUP BY dep;
如果没有数据,是 empty 不会显示这个分组
查询,每个部门,年龄在 20-30 岁之间(包括20和30岁),员工的人员数量
SELECT dep , COUNT(id) as 人数 FROM user2 WHERE age >= 20 AND age <= 30 GROUP BY dep;

8. 对于聚合函数的约束条件

HAVING 只能限定聚合函数的约束条件
WHERE 只能限定字段的约束条件
HAVING 写在 GROUP BY 之后
HAVING 聚合函数(字段) 约束条件
查询,每个部门,年龄在 20-30 岁之间(包括20和30岁),员工的人员数量
只显示人员数量,大于10人的部门
SELECT dep , COUNT(id) as 人数 FROM user2 WHERE age >= 20 AND age <= 30 GROUP BY dep HAVING COUNT(id) > 10;
查询 每个部门 的平均薪资
SELECT dep , AVG(pay) as ‘平均薪资’ FROM user2 GROUP BY dep;
每个部门,年龄是 20至30岁员工的凭据薪资
给 年龄 字段 添加 约束条件
SELECT dep , AVG(pay) as ‘平均薪资’ FROM user2 WHERE age>=20 AND age<=30 GROUP BY dep;
每个部门,年龄是 20至30岁员工的凭据薪资,只显示平均薪资在 12000 以上的部门
给 平均薪资 这个 聚合函数 添加 约束条件
SELECT dep , AVG(pay) as ‘平均薪资’ FROM user2 WHERE age>=20 AND age<=30 GROUP BY dep HAVING AVG(pay) > 12000;

9. 排序方式

ORDER BY 字段 排序方式
排序方式有两种:
ASC 升序 小—大 默认排序方式,可以不写
DESC 降序 大—小 必须要写

10. 分页查询

LIMIT 参数1,参数2
参数1 : 查询数据的起始索引下标
参数2 : 每页显示的数据数量
参数1公式: (当前页数-1)*参数2每页数据数量

总结: SQL查询语句

必须严格按照顺序,来设定代码内容
每部分可以定义的语法内容,也是有严格的限制的
SELECT 定义SQL查询语句的关键词
字段 或者 聚合函数 定义查询结果中,显示的内容字段和聚合函数
可以通过 as 给显示字段定义别名
FROM 表名 定义SQL语句语句,执行的表名,表结构
WHERE 字段的约束条件 设定对于字段的约束条件,支持比较运算符和逻辑运算符
LIKE ‘%_关键词’ 模糊查询
以及 子查询 (另一个SQL查询语句)
GROUP BY 字段 分组查询,将字段中的数据按照数值分组
数值相同的定义在一个分组中
默认只显示一条结果,实际上分组中存储的是多条数据
HAVING 聚合函数的约束条件 聚合函数是针对分组中数据,执行函数效果
HAVING 是专门针对 聚合函数的结果,设定约束条件
ORDER BY 字段 排序方式 按照设定的字段,以及排序方式,来显示查询结果
ASC 升序 小—大 默认是的排序方式
DESC 降序 大—小
LIMIT 参数1,参数2 按照设定的参数,来执行分页显示
参数1 : 分页显示,起始数据的索引下标
计算公式 (当期页数-1)*参数2
参数2 : 分页显示,每页显示的数据数量

联合查询(六)

说明

一. 什么是联合查询?
之前的查询都是一个表结构中的数据查询
所谓的联合查询,是查询时,同时查询多个表结构的数据…

二. 为什么要有联合查询?
存储数据时,有很多的文本内容,是会重复使用,重复存储的
例如: 省市县 性别 公司内的部门 等等
存储文字,会比较占用存储空间,存储数字会占用更少的存储空间
为了节省存储空间,实际项目中,这样重复的文字内容,会单独存储在一个表结构中
实际的表结构中,存储这个文字对应的id主键值,也就是一个数值数字
等到查询时,将多个表结构联合查询,最终显示为对应的文字内容
存 — 存的是数
显示 — 显示的是字
为的就是节省存储空间

PS : 说一下题外话
前端 的 css 样式 追求的是 可维护性和可重复性
css样式要便于修改,便于重复使用
js 程序代码 追求的是 更好的交互效果
后端程序 PHP,java等
追求的是 程序执行的效率,越快越好
数据库 MySQL MongoDB等
追求的是 存储时占用的内存空间越小越好
为了上述效果,各种程序都会把自己写的更加复杂
每种追求 也就是 我们后期程序优化的方向和目的
联合查询,不要求大家非常熟练的掌握代码,只要求大家能看懂代码
能了解清楚,这些代码的意义是什么,就可以
在执行联合查询之前,要建立专门的联合查询的表结构,这样的表结构,与普通的表结构不同
制作一个demo案例
将 user2 表结构 中 性别,城市,部门 三个字段,使用联合查询语法形式来进行存储和查询

1. 需要创建所谓的 父表 这个父表 是 存储 文字使用的表结构

往往只有两个字段 id主键值字段 和 对应的文字内容字段
父表 也叫做 公共表 标准表 基本表…
创建性别父表
性别数值就三个,男,女,保密
CREATE TABLE IF NOT EXISTS sexF(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT ‘id主键值字段’,
sex ENUM(‘男’,‘女’,‘保密’) NOT NULL DEFAULT ‘保密’ COMMENT ‘存储性别字段,只能输入男,女,保密,默认值是保密’
)ENGINE innoDB CHARSET utf8 COMMENT ‘存储性别内容的父表’;
向父表中,存储数据信息
就3个数据信息,男,女,保密
INSERT INTO sexF (sex) VALUES (‘男’);
INSERT INTO sexF (sex) VALUES (‘女’);
INSERT INTO sexF (sex) VALUES (‘保密’);
INSERT INTO sexF (sex) VALUES (‘男’) , (‘女’) , (‘保密’);

2. 创建 城市 父表

城市的数值是5个,意思意思
北京 上海 广州 重庆 天津
CREATE TABLE IF NOT EXISTS cityF(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT ‘id主键值字段’,
city VARCHAR(10) NOT NULL COMMENT ‘存储城市字段’
)ENGINE innoDB CHARSET utf8 COMMENT ‘存储城市内容的父表’;
向父表中,存储数据信息
就5个数据信息,北京,上海,广州,重庆,天津
INSERT INTO cityF (city) VALUES (‘北京’);
INSERT INTO cityF (city) VALUES (‘上海’);
INSERT INTO cityF (city) VALUES (‘广州’);
INSERT INTO cityF (city) VALUES (‘重庆’);
INSERT INTO cityF (city) VALUES (‘天津’);

3. 创建部门父表

部门的数值是4个,意思意思
JAVA PHP WEB UI/UE
CREATE TABLE IF NOT EXISTS depF(
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT ‘id主键值字段’,
dep VARCHAR(10) NOT NULL COMMENT ‘存储部门字段’
)ENGINE innoDB CHARSET utf8 COMMENT ‘存储部门内容的父表’;
向父表中,存储数据信息
就4个数据信息,JAVA PHP WEB UI/UE
INSERT INTO depF (dep) VALUES (‘JAVA’);
INSERT INTO depF (dep) VALUES (‘PHP’);
INSERT INTO depF (dep) VALUES (‘WEB’);
INSERT INTO depF (dep) VALUES (‘UI/UE’);

4. 创建子表,也就是存储父级 id主键值 数值的 表结构

联合查询的 子表 与 一遍的表结构不同
下列子表的创建,与正常的普通表结构不同
之前 sex city dep 是 存储文字内容
现在 要 存储 父表的 id主键值
原始 存储的是文字 联合查询 存储的是 数值
男 – 1 女 – 2 保密 – 3
北京 – 1 上海 – 2 广州 – 3 重庆 – 4 天津 – 5
JAVA – 1 PHP – 2 WEB – 3 UI/UE – 4
字段的数据类型也要做修改
数据类型必须与 存储父表的 id主键值字段 的数据类型相同
数据类型和属性,必须相同 包括 TINYINT 数据类型 和 UNSIGNED 属性
约束条件,例如 NOT NULL DEFAULT 等可以不同,根据需求而定
联合查询,子表中,对于字段,还要添加 外键 约束
表示 当前 子表中的数据,和哪个父表相互关联
添加了外键的子表,父表,父表中的数据,不能任意的更新,父表也不能任意的删除
为了就是确保父表和子表的数据的一致性和安全性
要修改父表,必修就使用专门的特殊的SQL语句,确保父表子表的数据,同时更新
总之,父表要更新数据,子表必须同时自动更新数据
如果父表要删除,必须要先取消与子表的关联
CREATE TABLE user3(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,
name VARCHAR(30) NOT NULL COMMENT ‘员工姓名’,
age TINYINT UNSIGNED DEFAULT 18 COMMENT ‘员工年龄,默认18岁’,
sexSon TINYINT UNSIGNED DEFAULT 3 COMMENT ‘员工性别,1-男,2-女,3-保密,默认值是3-保密’,
citySon TINYINT UNSIGNED COMMENT ‘员工所在城市,1-北京,2-上海,3-广州,4-重庆,5-天津’,
depSon TINYINT UNSIGNED COMMENT ‘员工所在部门,1-JAVA,2-PHP,3-WEB,4-UI/UE’,
pay FLOAT(8,2) DEFAULT 5000 COMMENT ‘员工薪资’,
FOREIGN KEY (sexSon) REFERENCES sexF(id),
FOREIGN KEY (citySon) REFERENCES cityF(id),
FOREIGN KEY (depSon) REFERENCES depF(id)
)ENGINE = InnoDB CHARSET = UTF8 COMMENT = ‘员工信息表’;

5. 写入数据

INSERT user3(name,age,sexSon,citySon,depSon,pay)
VALUES
(‘王卓’,21,3,2,2,15000),
(‘Andy’,27,1,3,3,9000),
(‘半世浮夸’,22,1,4,2,6000),
(‘梁晓洁’,17,2,1,4,8000),
(‘Eric’,23,3,1,1,9000),
(‘echo不开心’,21,1,1,1,15000),
(‘心竹’,22,2,5,2,18000),
(‘执念’,20,1,1,2,19000),
(‘张伟’,20,1,1,2,15000),
(‘大西子’,22,1,2,3,5000),
(‘Mr.Shahim^~’,19,1,3,2,12000),
(‘风度’,25,3,4,1,10000),
(‘城府’,28,1,3,4,5000),
(‘那般’,18,2,1,2,20000),
(‘早安霖’,25,1,1,2,18000),
(‘大脸猫’,20,2,1,2,19000),
(‘张志远’,27,1,2,3,9000),
(‘芛清’,20,2,4,4,8000),
(‘温暖’,20,2,3,3,20000),
(‘追寻’,38,1,1,4,5000);
此时与子表关联的父表,无法直接删除,会报错
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
表示有一个 外键 正在关联着当前这个表结构
如果要想删除父表,要先删除子表

6. 准备工作结束,来定义联合查询语句

SELECT s.id , s.name , s.age , sf.sex , cf.city,df.dep,s.pay FROM user3 as s
INNER JOIN sexF as sf
ON s.sexSon = sf.id
INNER JOIN cityF as cf
ON s.citySon = cf.id
INNER JOIN depF as df
ON s.depSon = df.id
ORDER BY s.id ;
联合查询 语法:
SELECT 来源.字段名 , 来源.字段名… FROM 子表 as 别名
INNER JOIN 父表 as 别名
ON 子表.字段 = 父表.字段;

解释说明:

1, 子表和父表,最好要定义别名,也可以不定义别名就直接使用原始表名

2, 表示字段来源时 语法应该是
表名.字段名 表示 这个字段的来源是 哪个表结构
因为子表和父表中,有可能都想用名称的字段
因此必须要定义 字段来源
可以是 表名.字段( user3.字段 ) 也可以是 别名.字段(s.字段)
如果要是给表结构定义了别名,就只能使用 别名.字段,不能使用 表名.字段
别名可以任意定义,只要能看懂就行,最好是使用英文,不要使用中文
别名可以后定义,先使用
子表的别名 在 FROM 子表 后通过 as 来定义
父表的别名 在 INNER JOIN 父表 后通过 as 来定义

3, INNER JOIN 称为外链接
作用是导入一个父表与当前子表进行联合查询
INNER JOIN sexF as sf
导入一个 表名是 sexF 的父表与当前子表表结构,同时进行联合查询
as 是给当前父表定义 别名

4, ON 表示 父表 与 子表 的关联关系
告诉SQL语句,子表中的那个字段,存储的是父表当中的那个字段
ON 子表.字段 = 父表.字段
子表中的sexSon字段 存储的 是 父表id字段的数据
ON s.sexSon = sf.id

5, SELECT 和 FROM 之间,定义查询显示内容
s.id , s.name , s.age , sf.sex , cf.city , df.dep , s.pay
s.id , s.name , s.age , s.pay 来源是 子表
sf.sex , cf.city , df.dep 来源是 父表
定义的内容,一定是有的字段来源是子表,有的字段来源是父表
子表中存储的是父表的id主键值,是 数
如果要变成 字, 只能是 父表的字段,存储的才是文字
联合查询,虽然查询的是子表,但是现实的内容,有些来源是父表
子表存储的是父表id主键值,这样的字段
联合查询时,要查询显示的是父表的字段

6, 最终显示,默认不是按照 子表的id主键值顺序显示,是按照父表id主键值顺序显示

7, 如果有多个父表参与联合查询
就要写多套 INNER JOIN … ON 语法
之间使用空格间隔

8, 如果需要定义 WHERE字段约束条件 GROUP BY分组查询 HAVING聚合函数约束条件 ORDER BY 排序方式 LIMIT 分页显示
这些定义,要定义在 所有的 联合查询语句之后

—联合查询,只要搞清楚,子表,父表,显示的父表哪个字段,父表和子表字段的关联就可以了