德州市市政工程建设总公司网站,互联网公司起名,快照不更新怎么办,网站建设类课题的研究方法Mysql版本#xff1a;8.0.26 可视化客户端#xff1a;sql yog 目录一、七大子句顺序二、演示2.1 from语句2.2 on子句2.3 where子句2.4 group by子句2.4.1 WITHROLLUP#xff0c;加在group by后面2.4.2 是否可以按照多个字段分组统计#xff1f;2.4.3 分组统计时#xff0c… Mysql版本8.0.26 可视化客户端sql yog 目录一、七大子句顺序二、演示2.1 from语句2.2 on子句2.3 where子句2.4 group by子句2.4.1 WITHROLLUP加在group by后面2.4.2 是否可以按照多个字段分组统计2.4.3 分组统计时select后面字段列表的问题2.5 having子句2.6 order by子句2.7 limit子句提示以下是本篇文章正文内容下面案例可供参考
一、七大子句顺序
(1) from: 从哪些表中筛选。
(2) inner l left | right … on: 关联多表查询时去除笛卡尔积
(3) where: 从表中筛选的条件
(4) group by: 分组依据
(5) having: 在分组统计结果中再次筛选 (with rollup)
(6) order by: 排序
(7) limit: 分页 注意必须按照 (1) - (7) 的顺序编写子句。 二、演示
测试数据准备如下 ①创建数据表 t_department: CREATE TABLE t_department (did int NOT NULL AUTO_INCREMENT COMMENT 部门编号,dname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 员工名称,description varchar(200) DEFAULT NULL COMMENT 员工简介,PRIMARY KEY (did),UNIQUE KEY dname (dname)
) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci②创建数据表t_employee CREATE TABLE t_employee (eid int NOT NULL AUTO_INCREMENT COMMENT 员工编号,ename varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 员工姓名,salary double NOT NULL COMMENT 薪资,commission_pct decimal(3,2) DEFAULT NULL COMMENT 奖金比例,birthday date NOT NULL COMMENT 出生日期,gender enum(男,女) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 男 COMMENT 性别,tel char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 手机号码,email varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 邮箱,address varchar(150) DEFAULT NULL COMMENT 地址,work_place set(北京,深圳,上海,武汉) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 北京 COMMENT 工作地点,hiredate date NOT NULL COMMENT 入职日期,job_id int DEFAULT NULL COMMENT 职位编号,mid int DEFAULT NULL COMMENT 领导编号,did int DEFAULT NULL COMMENT 部门编号,PRIMARY KEY (eid),KEY job_id (job_id),KEY did (did),KEY mid (mid),CONSTRAINT t_employee_ibfk_1 FOREIGN KEY (job_id) REFERENCES t_job (jid) ON DELETE SET NULL ON UPDATE CASCADE,CONSTRAINT t_employee_ibfk_2 FOREIGN KEY (did) REFERENCES t_department (did) ON DELETE SET NULL ON UPDATE CASCADE,CONSTRAINT t_employee_ibfk_3 FOREIGN KEY (mid) REFERENCES t_employee (eid) ON DELETE SET NULL ON UPDATE CASCADE,CONSTRAINT t_employee_chk_1 CHECK ((salary 0)),CONSTRAINT t_employee_chk_2 CHECK ((hiredate birthday))
) ENGINEInnoDB AUTO_INCREMENT28 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci③创建数据表t_job CREATE TABLE t_job (jid int NOT NULL AUTO_INCREMENT COMMENT 职位编号,jname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 职位名称,description varchar(200) DEFAULT NULL COMMENT 职位简介,PRIMARY KEY (jid),UNIQUE KEY jname (jname)
) ENGINEInnoDB AUTO_INCREMENT13 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci2.1 from语句
功能 表示从某个表中筛选数据 案例查询t_department表的所有欣喜 代码演示如下
select *
from t_department #表示从某个表中筛选数据2.2 on子句
特点
1on必须配合join使用
2on后面只写关联条件 所谓关联条件是两个表的关联字段的关系 例如t_employee表和t_department表关联,t_employee.didt_department.did 3有n张表关联就有n-1个关联条件 两张表关联就有1个关联条件 三张表关联就有2个关联条件 案例#查询员工的编号姓名职位编号职位名称部门编号部门名称需要t_employee员工表t_department部门表t_job职位表。 代码如下示例
SELECT eid,ename,t_job.job_id,t_job.job_name, t_department.did,t_department.dname
FROM t_employee INNER JOIN t_department INNER JOIN t_job
ON t_employee.did t_department.did AND t_employee.job_id t_job.job_id;2.3 where子句
功能 在查询结果中筛选 案例#查询所有男员工的姓名和部门名称 代码演示如下
SELECT ename,dname
FROM t_department RIGHT JOIN t_employee ON t_employee.didt_department.did
WHERE t_employee.gender男;2.4 group by子句
功能 分组依据 案例#查询每一个部门的平均薪资 代码演示如下
SELECT dname,AVG(salary) AS 平均薪资
FROM t_department RIGHT JOIN t_employee ON t_department.didt_employee.did
GROUP BY t_department.dname; 案例#查询每一个部门所有男员工的平均薪资 SELECT t_employee.did,dname,AVG(salary) AS 平均薪资
FROM t_employee RIGHT JOIN t_department ON t_department.didt_employee.did
WHERE gender男
GROUP BY t_employee.did;2.4.1 WITHROLLUP加在group by后面
WITHROLLUP在group分组字段的基础上再进行统计数据。 案例#按照部门统计人数并合计总数 代码演示如下
SELECT IFNULL(did,合计) AS 部门编号 , COUNT(*) AS 人数 FROM t_employee GROUP BY did WITH ROLLUP;2.4.2 是否可以按照多个字段分组统计 案例#分别统计查询每一个部门男、女员工的平均薪资 代码演示如下
SELECT t_employee.did,dname,gender,AVG(salary) AS 平均薪资
FROM t_employee RIGHT JOIN t_department ON t_department.didt_employee.did
GROUP BY t_employee.did,gender;2.4.3 分组统计时select后面字段列表的问题 案例统计每个部门的人数 代码演示如下
SELECT eid,ename, did, COUNT(*) FROM t_employee; ❌ 分析不符合案例需求案例需求只要统计各部门的总人数而不是统计总人数况且加上count(*)【count()它的功能是统计记录数,又是分组函数即函数执行完后得到结果的行数可能会变少有可能是1行也可能是几行】如果不加分组条件此查询语句会查询所有的人数带返回第一个员工的员工编号和姓名以及部门编号。 代码改善如下
SELECT eid,ename, did, COUNT(*) FROM t_employee GROUP BY did; ❌ 分析虽然加了group by did即按部门编号分组但整个查询语句返回的”eid“和”ename“等字段下的记录有歧义如下所示 红圈勾出的记录是在表达部门编号为1的部门下有14个员工编号为1名为孙洪亮的员工吗显然不对不符合逻辑古往今来没有十四胞胎且一模一样的人。 正确代码如下
SELECT did, COUNT(*) FROM t_employee GROUP BY did; 分组统计时select后面只写和分组统计有关的字段其他无关字段不要出现否则会引起歧义 2.5 having子句
功能 在分组统计结果中再次筛选 案例#分别统计查询每一个部门男、女员工的平均薪资只显示平均薪资在10000元以上的记录 代码演示如下
SELECT t_employee.did,dname,gender,AVG(salary) AS 平均薪资
FROM t_employee RIGHT JOIN t_department ON t_department.didt_employee.did
GROUP BY t_employee.did,gender
HAVING 平均薪资10000;where和having的区别别? where是针对原表的原始数据筛选后面不能接分组函数(avg,sum,count,max,min等 having是针对分组统计结果的再次筛选后面可以接分组函数还可以使用统计结果的别名
2.6 order by子句
功能 升序和降序默认是升序 asc代表升序【从小到大】 desc 代表降序【从大到小】 案例#查询所有员工的姓名和薪资按照薪资从高到低排序 代码演示如下
SELECT ename,salary
FROM t_employee
ORDER BY salary DESC; #默认是升序降序要加desc升序可以加asc案例#查询所有员工的姓名和薪资、出生日期按照薪资从高到低排序 。如果薪资相同的按照出生日期从小到大。 代码演示如下
SELECT ename,salary,birthday
FROM t_employee
ORDER BY salary DESC,birthday ASC;2.7 limit子句 功能分页显示结果。 注意 limit m,n n表示最多该页显示几行 m表示从第几行开始取记录第一个行的索引是0 m (page-1)×n page表示第几页 每页最多显示5条n5 第1页page1m (1-1)*5 0; limit 0,5 第2页page2m (2-1)*5 5; limit 5,5 第3页page3m (3-1)*5 10; limit 10,5
每页显示20条n20 第6页page6m (6-1)*20limit 100,20 案例#查询员工信息按照每页显示5条的规则查询第1页 代码演示如下
SELECT *
FROM t_employee
LIMIT 0,5;案例#查询员工信息按照每页显示5条的规则查询第2页 代码演示如下
SELECT *
FROM t_employee
LIMIT 10,5;
