关联查询

一、分组与限制

分组过滤

语法：select 字段 from 表 where 条件 group by 字段 having 条件

分组过滤having和where类似，都是用于条件判断；

区别在于：where是最核心的条件判断； 但having智能用在分组和的条件判断

#分组过滤：
#统计60、70、90号部门的最高工资
#思路：
#1).	确定分组依据（department_id）
select department_id from t_employees group by department_id;
#2).	对分组后的数据，过滤出部门编号是60、70、90信息
select department_id from t_employees group by department_id having department_id in(60,70,90);
#3).	max()函数处理
select department_id,max(salary) from t_employees group by department_id having department_id in(60,70,90);

结果限制

#限定查询  限定查询出来的条数  SELECT 列名 FROM 表名 [LIMIT 起始行，查询行数]()
#应用场景：分页查询
#查询表中前五名员工的所有信息
select * from t_employees limit 0,5;

#查询表中从第四条开始，查询 10 行
select * from t_employees limit 3,10;

#分页： 每页5条   第1页：0,5   第2页：5，5  第3页：10，5
#算法： 起始行=（当前页-1）*每页条数

编写顺序

select 字段 from 表 where 条件 group by 字段 having 条件 order by 字段 limit 起始行，条数

二、子查询

概述：会出现两个查询语句；一个查询的结果，是另一个查询的条件；这个结果就是子查询

单行单列

子查询的结果为单行单例；是最基本，也是最常用的一种子查询

#子查询--单行单列
#查询工资大于Bruce 的员工信息
#1.先查询到 Bruce 的工资（一行一列）
select salary from t_employees where FIRST_NAME='Bruce';  #子查询
#2.查询工资大于 Bruce 的员工信息
select * from t_employees where salary>6000;
#3.组合
select * from t_employees where salary>(select salary from t_employees where FIRST_NAME='Bruce');

多行单列

子查询的结果是多行单列的应用，往往有两种情况：1.枚举查询 2. all| any关键字

#枚举的多行单列：   where 字段 in(值1，值2..)
#查询与名为'King'同一部门的员工信息
#思路：
#1.	先查询 'King' 所在的部门编号(多行单列)
select department_id from t_employees where LAST_NAME='King';
#2.	再查询80、90号部门的员工信息
select * from t_employees where department_id in(80,90);
#3.组合
select * from t_employees where department_id in(select department_id from t_employees where LAST_NAME='King');


#any|all的使用  any-匹配部分   all-匹配所有
#工资高于60部门所有人的信息   
#1.查询 60 部门所有人的工资（多行单列）
select salary from t_employees where DEPARTMENT_ID=60;
#2.查询高于 60 部门所有人的工资的员工信息（高于所有-高于最高的）
select * from t_employees where salary > ALL(select salary from t_employees where DEPARTMENT_ID=60);
#。查询高于 60 部门的工资的员工信息（高于部分-高于最低的）
select * from t_employees where salary > ANY(select salary from t_employees where DEPARTMENT_ID=60);

多行多列

子查询的结果是一张虚拟表，继续当成表来使用即可

SELECT 列名 FROM（子查询的结果集）WHERE 条件;

#多行多列的子查询
#查询员工表中工资排名前 5 名的员工信息
#思路：
#1.	先对所有员工的薪资进行排序（排序后的临时表）
select * from t_employees order by salary desc;
#2.	再查询临时表中前5行员工信息
select * from (select * from t_employees order by salary desc) temp limit 0,5;

#简化写法：
select * from t_employees order by salary desc limit 0,5;

三、关联查询

合并查询

合并查询，需要合并多张表；在平时应用中不常用；合并的表字段数量要一致，否则会有问题

应用场景：多张表结构一致，并进行汇总才用到； 例如：多个班级学生信息汇总

#创建t1表和t2表
#合并查询
#合并两张表的结果，去除重复记录
select * from t1 UNION select * from t2;

#合并两张表的结果，不去除重复记录（显示所有）
select * from t1 UNION ALL select * from t2;

表连接查询

表连接查询分为：内连接和外连接

内连接：两张表中能够匹配上的记录则显示出来；匹配不上的，则不显示

外连接分为左外和右外：

左外：以左表为标准，所有记录都会显示；如果右表记录匹配不上则显示为null

右外：以右表为标准，所有记录都会显示；如果左表记录匹配不上则显示为null

#创建teacher表和couse表
#内连接：
select * from teacher t,couse c where t.id=c.t_id;       #MySQL专用
select * from teacher t INNER JOIN couse c ON t.id=c.t_id;   #统一标准

#左外连接
select * from teacher t LEFT JOIN couse c ON t.id=c.t_id;   

#右外连接
select * from teacher t right JOIN couse c ON t.id=c.t_id; 

#三表关联案例：
#查询所有员工工号、名字、部门名称、部门所在国家ID
#员工工号、名字-员工表； 部门名称-部门表  国家ID-地区表
select e.employee_id,e.first_name,d.DEPARTMENT_NAME,l.COUNTRY_ID from t_employees e 
INNER JOIN t_departments d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID 
INNER JOIN t_locations l ON d.LOCATION_ID=l.LOCATION_ID;

四、DML操作

DML是数据操作语言，针对表中的增删改的操作； 查询则叫做DQL

添加

语法1：insert into 表名(字段1，字段2..) values(值1，值2…) —常用

语法2：insert into 表名 values(值1，值2…) 必须和表字段一一对应，且全部字段要添加数据

#添加一条工作岗位信息
insert into t_jobs(job_id,job_title,min_salary,max_salary) values('AD_IT','IT_PRO',10000,20000);  #推荐
insert into t_jobs values('AD_XS','XSY',5000,15000);  #注意：字段必须按顺序，且写全

修改

语法： update 表名 set 字段=值，字段2=值2 where 条件

注意：如果没有写where则匹配所有记录都进行了修改

#修改编号为100 的员工的工资为 25000
update t_employees set salary=25000 where EMPLOYEE_ID=100;

#修改编号为135 的员工信息岗位编号为 ST_MAN，工资为3500
update t_employees set JOB_ID='ST_MAN',salary=3500 where EMPLOYEE_ID=135;

删除

语法： delete from 表 where 条件

注意：必须要加where，否则删除表的所有记录

#删除编号为135 的员工
delete from t_employees where employee_id=135;

#删除姓Peter，并且名为 Hall 的员工
delete from t_employees where first_name='Peter' and last_name='Hall';

#清除数据表
TRUNCATE table teacher;  #类似delete from teacher  区别：TRUNCATE 删除表再重新创建

五、数据表操作

数据类型

建表前，需要先弄清除数据库的数据类型分类，可以分为：数值类型，日期类型，字符串类型

这些类型重点记住：int，double(M,D), date,datetime,varchar(长度)

创建表

#创建表
create table sub(
	 id int,
   name varchar(30),
   hour int
)
insert into sub(id,name,hour) values(1,'java',60);
insert into sub(id,name,hour) values(2,'h5',55);
select * from sub;

数据表操作

#表字段的操作：    ALTER TABLE 表名 操作;
#在课程表基础上添加gradeId 列
alter table sub add gradeId int;

#修改课程表中课程名称长度为10个字符
alter table sub MODIFY name varchar(10);

#删除课程表中 gradeId 列
alter table sub drop gradeId;

#修改课程表中 hour 列为 h
alter table sub change hour h int;

#修改课程表的sub 为 subs
alter table sub rename subs;

#删除subs表
drop table subs;

六、约束

问题：在往已创建表中新增数据时，可不可以新增两行相同列值得数据？]()

如果可行，会有什么弊端？

答：可以创建；弊端：无法识别唯一的一条记录； 造成数据的读写肯定会出问题； 需要进行约束

实体完整性约束

一条记录代表着一个实体对象，也是就每条记录都需要唯一性约束

  1. 主键约束

    表中每条记录的唯一标识，不代表业务逻辑； 注意：主键不能为null

  2. 唯一约束

    主键约束就是唯一约束，区别在于唯一约束设置到除id以外的字段；且允许为null

  3. 主键自增长

    主键自增长，默认从1开始，每次添加都+1；添加数据更方便

#主键约束：
create table sub(
	id int primary key auto_increment,  #主键约束，不能为null   自增长
  name varchar(20) UNIQUE,  #唯一约束
  hour int 
)
select * from sub;
insert into sub(name,hour) values('java',60);
insert into sub(name,hour) values('h5',55);

域完整性约束

域完整性约束是针对字段中的单元格的值的范围的限制

create table sub(
	id int primary key auto_increment,  
  name varchar(20) UNIQUE not null,  #非空约束
  hour int default 30   #默认值为30
)
select * from sub;
insert into sub(name) values('java');
insert into sub(name,hour) values('h5',55);

外键约束

两张表中的单元格的值的范围限制； 这两张表一张作为主表，一张作为从表

主表：关联字段的主键所在表为主表；关联字段的非主键所在表则是从表

外键约束规则：（注意：外键约束需要设置到从表中）

创建时，先创建主表；添加数据时，先添加主表数据

删除时，先删除从表或数据；再删除主表或数据

#外键约束
drop table teacher;  #主表
drop table course;   #从表

#先创建主表   创建时先创建主表及添加数据
create table teacher(
	 id int primary key auto_increment,
   name varchar(20)
);
insert into teacher(name) values('凤姐');
insert into teacher(name) values('芙蓉');
select * from teacher;

#再创建从表，并设置外键约束
create table course(
	 id int primary key auto_increment,
   name varchar(30),
   t_id int,  #外键  从表中的关联字段是外键
   #CONSTRAINT 标记名  FOREIGN KEY(外键名) REFERENCES 主表(主键)
   CONSTRAINT fk FOREIGN KEY(t_id) REFERENCES teacher(id)   #外键约束
);
select * from course;
insert into course(name,t_id) values('h5',1);  #添加时t_id只能时主键中存在的1或2的主键值

#删除时，先删从表或数据，再删主表或数据

七、事务

概述

事务是原子性操作(不可分割的整体)；处在事务中的多个SQL语句，要么都成功，则提交；要么都失败，则回滚

事务的边界

事务的边界，就是事务开启或结束的过程

开始： 启动事务 start transaction； 开启事务后，则进入事务中；中间可执行SQL语句

执行多条SQL语句

结束：（提交或回滚）

提交：多条SQL语句都执行成功，则提交； commit；

回滚：如果有任意SQL语句失败，则回滚，回滚到未发生的状态； rollback；

原理

开启事务后，相当于开辟了缓冲区(回滚段);所有SQL指令的执行都是在缓冲区中进行；如果都成功了，则执行提交，意味着提交到数据库中永久写入；如果有失败的，则执行回滚(清除缓冲区)

事务特性

事务特性：ACID; 原子性，一致性，隔离性，持久性

原子性：事务操作要么都成功，要么都失败

一致性：事务的成功或失败，最终完成的状态是一致的(例如，转账的总和不变)

隔离性：处在事务中，与外界的操作是完全隔离的；除非进行了提交或回滚

持久性：进行了提交，则永久性写到了数据库中

应用

#事务的操作   ---模拟转账功能
#zs和ls都有1000块；zs转200给ls
create table account(
	 id int primary key auto_increment,
   name varchar(20),
   money int
)
insert into account(name,money) values('zs',1000);
insert into account(name,money) values('ls',1000);
select * from account;

start TRANSACTION;  #开启事务

update account set money=money-200 where name='zs';

#中途出现异常，则会跳到回滚语句

update account set money=money+200 where name='ls';

commit;  #上述的SQL操作无异常，则进入提交

rollback;  #如果有一条语句出现异常，则会跳到回滚

八、总结与作业

总结

作业

#作业1
#1 查询2张表的数据，使用内连接和左外连接

#2 查询部门101和105部门所有员工工资，并按照降序排列

#3 查询部门101和105所有员工工资，并且按照工资降序排列，如果部门中工资相同，则按照性别排列（女在前）

#4 查询部门名称为主公的所有员工信息

#5 查询102部门的所有员工人数

#6 查询105部门的平均工资

#7 查询103部门中工资最高的和最低的

#8 查询每一个部门的工资总和并按照降序排列

#9 查询每一个部门的工资总和,要求只显示总工资大于90w的部门信息

 
#作业2
#1、 查询Student表中的所有记录的Sname、Ssex和Class列。
 
#2、 查询教师所有的单位即不重复的Depart列。

#3、 查询Student表的所有记录。

#4、 查询Score表中成绩在60到80之间的所有记录。

#5、 查询Score表中成绩为85，86或88的记录。

#6、 查询Student表中“95031”班或性别为“女”的同学记录。

#7、 以Class降序查询Student表的所有记录。

#8、 以Cno升序、Degree降序查询Score表的所有记录。

#9、 查询“95031”班的学生人数。

晨考

晨考：
student表
   id ,uname,sex ,birth ,department ,address 
(901,'张老大', '男',1985,'计算机系', '北京市海淀区'),
(902,'张老二', '男',1986,'中文系', '北京市昌平区'),
(903,'张三', '女',1990,'中文系', '湖南省永州市'),
(904,'李四', '男',1990,'英语系', '辽宁省阜新市'),
(905,'王五', '女',1991,'英语系', '福建省厦门市'),
(906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
score表
stu_id ,c_name ,grade 
(901, '计算机',98),
(901, '英语', 80),
(902, '计算机',65),
(902, '中文',88),
(903, '中文',95),
(904, '计算机',70),
(904, '英语',92),
(905, '英语',94),
(906, '计算机',90),
(906, '英语',85);

#1. 两张表根据学生ID进行内连接

#2. 分数降序排列，且只显示第2到第4名成绩信息

#1
select * from student s inner join score sc on s.id=sc.stu.id;
#2
select * from score order by grade desc limit 1,3;