数据库复杂语句Word下载.docx

1、emp表3.1、表之间的关联关系数据库中的表一般存在三种关联关系 1、一对一2、一对多、多对一3、多对多表之间的的关联关系，通过“外键”来体现，外键的作用就是建立表之间的关联关系，外键一般在多方表。一个表可以有多个“外键“用Navicat 8 for Mysql 设计外键，操作如图： 1、右键-选择设计表2、选择创建栏位3、添加如下选项4、选择外键，按如下步骤填写外键信息5、填写完外键信息选保存此刻dept表、emp表就建立的一对多的关联关系。关联关系是为了保证数据库中数据的完整性3.2、多表查询如果查询使用一个以上的表就成为多表查询在Navicat8中写SQL语句，则需使用查询窗体 如图所示

2、：1、 右键单击查询2、 查询编辑选项中即可写SQL语句3.2.1、多表查询基本语法多表查询语法如下:SELECT DISTINCT * | 表名.列名1 别名1.列明 ，表名.列名2，别名2.列明 ,FROM 表名称1 别名1，表名称2 别名2，WHERE 条件sORDER BY 排序字段 ASC|DESC，排序字段 ASC|DESC同时查询dept、emp表SELECT * FROM dept,emp;查询emp表的记录数SELECT COUNT（*） FROM emp查询dept表的记录数SELECT COUNT（*） FROM dept dept表4条记录 emp表7条记录在使用多表时

3、会产生笛卡尔乘积，4 * 7 = 28 条记录，这些记录中许多是重复信息，所以在查询多表时要使用关联字段作为条件，去掉笛卡尔乘积在dept表和emp表中关联的字段是： dept表的deptid字段与emp表的deptid，而且emp的deptid字段的值都来自dept的deptid，即dept.deptid = emp.empid去掉笛卡尔乘积的查询为：SELECT * FROM dept,emp WHERE dept.deptid = emp.deptid使用多表查询，建议使用别名查询员工编号，员工姓名，员工性别，员工年龄，员工地址，所属部门名称 |- 此查询包含了员工表中的信息，部门表中的

4、信息，故使用多表查询SELECT B.empid,B.empname,B.empsex,B.empage,B.empaddress,A.deptname FROM dept A, emp BWHERE A.deptid = B.deptid3.2.2、左、右连接查询在多表查询中还有一种查询方式，称为内连接查询，语法如下：FROM 表名称1 别名1 INNER JOIN表名称2 别名2，ON 关联条件查询部门表和员工表信息SELECT * FROM dept INNER JOIN emp ON dept.deptid = emp.deptid 内连接查询其实就是去掉笛卡尔乘积的多表关联查询，注意

5、：只查询关联字段，没有关联的不查询左连接查询SELECT * FROM dept LEFT JOIN empON dept.deptid = emp.deptid以左边的表作为标准，无论关联与否左边表都查询右连接查询FROM dept RIGHT JOIN emp以右边的表作为标准，无论关联与否右边表都查询3.3、组函数及分组统计、如需查询员工表中的性别为“男”或“女”的员工个数，则需要SQL语句中分组统计功能。1、 分组在SQL中用“GROUP BY”语法如下：SELECT DISTINCT * | 列名1 别名1 ，列名2，别名2 ,FROM 表名称1 别名1GROUP BY 分组条件注意

6、：group by 的位置以性别为标准对员工进行分组SELECT empsex FROM emp GROUP BY empsex单独使用分组条件时，以什么字段作为分组条件，那么查询字段就必须是什么字段2、 SQL中常用的统计函数COUNT（） : 全部的记录数MAX（） : 最大值MIN（） : 最小值AVG（） : 平均数SUN（） : 求和查询员工总数SELECT COUNT（1） FROM empcount（1）中填写“1”有助于提高查询效率查询员工年龄的最大值SELECT MAX（empage） 年龄最大值 FROM emp查询员工年龄最小值SELECT MIN（empage） 年龄最

7、小值查询员工年龄的平均值SELECT AVG（empage） 年龄的平均值查询所有部门人数之和SELECT SUM（deptnum） 部门人数之和 FROM dept 分组函数中使用的通常都是数值类型的字段，或日期类型字段3、 分组统计的综合应用查询员工信息表中“男“、”女“员工的个数 分析： |- 以性别作为标准进行分组 |- 使用COUNT（）统计函数SELECT empsex 性别, COUNT（empid） 员工人数 FROM empGROUP BY empsex查询每个部门的员工人数分析： |- 查询使用到了多表 |- 以部门编号作为分组标准SELECT A.deptid 部门编号,

8、 COUNT（B.empid） 部门人数GROUP BY A.deptid查询每个部门的员工的平均年龄 |- 使用AVG（）统计函数, AVG（B.empage） 部门平均年龄在使用分组的时候，不能出现分组条件和分组函数之外的字段4、HAVING的使用HAVING是在分组的基础上进行筛选，语法如下：GROUP BY 分组条件 HAVING 分组条件查询员工平均年龄在22岁之上所属部门编号 分析 |- 以部门编号作为分组标准 |- 分组后，条件筛选使用AVG（）函数SELECT deptid FROM emp WHERE trueGROUP BY deptidHAVING AVG（empage）

9、 22小结： 只在某列中出现重复，才需要分组，否则没有意义3.4、分页语句如果数据特别多，一次在页面中展示不完，我们就需要用到分页展示数据，这就需要分页的sql语句mysql分页的关键字为： LIMIT X，Y X为起始的记录数（排除前几条记录开始）， Y为每次展示的记录个数（分页标准）语法如下：LIMIT X, Y查询员工表中的前两条记录SELECT * FROM emp LIMIT 0, 2查询员工表中的第3、4条记录SELECT * FROM emp LIMIT 2, 2查询员工表中的第5、6条记录SELECT * FROM emp LIMIT 4, 2查询员工表中的第7、8条记录SELECT * FROM emp LIMIT 6, 2综上mysql中的分页语句为：LIMIT （当前页-1） * 分页标准）, 分页标准第一页的记录数SELECT * FROM emp LIMIT （1-1）*2）, 2（1-1）*2 一般会在程序中直接算出结果，放入该位置即可第二页的记录数SELECT * FROM emp LIMIT （2-1）*2）, 2（2-1）*2 一般会在程序中直接算出结果，放入该位置即可4 总结 5 预习任务6 作业