1. 概述

数据库的基础操作主要分为六块。

• DQL:Query，select
• DML: Manual，insert update delete
• DDL:Define，create drop alter
• DCL: Control，grant revoke commit rollback
• 函数:字符串函数、数字函数、日期函数
• 运算符：算术运算符、比较运算符、逻辑运算符、位运算符

2. 详述

SQL语言分为五个部分：
• 数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
• 数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：
  1) INSERT：增加数据
  2) UPDATE：修改数据
  3) DELETE：删除数据
• 数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：
  1) CREATE：创建数据库对象
  2) ALTER：修改数据库对象
  3) DROP：删除数据库对象
• 数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问 数据库的权限，其主要包括：
  1) GRANT：授予用户某种权限
  2) REVOKE：回收授予的某种权限
• 事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：
  1) START TRANSACTION：开启事务
  2) COMMIT：提交事务
  3) ROLLBACK：回滚事务
  4) SET TRANSACTION：设置事务的属性

3. 创建数据库表

#这是一个单行注释
-- 这也是单行注释
/*
多行
注释
*/

create table t_student(
	snum int(6),
	sname varchar(5),
	sex char(1),
	age int(3),
	enterdate date,
	class varchar(6),
	email varchar(15)
);


desc t_student;

select * from t_student;
show create table t_student;

4. 增加数据库记录

select * from t_student;

insert into t_student values (1, '小明' ,'男', 18, '2023-2-12',3,'3303@qq.com');
insert into t_student values (10001011, '小明' ,'男', 18, '2023-2-12',3,'3303@qq.com');
insert into t_student values (2, '小明' ,'男', 18, '2023/2/12',3,'3303@qq.com');
insert into t_student values (1, '小王' ,"男", 18, '2023.2.12',3,'3303@qq.com');
insert into t_student values (9, '小李' ,'女', 17, now(),3,'3303@qq.com');
insert into t_student (snum,enterdate,sex) values (9, now(),"女");


select * from t_student;

5. 修改和删除数据库记录

select * from t_student;

-- 修改表中数据 都是不区分大小写的,内容插可区分大小写，但查不区分
#全表
update t_student set sex = '女';
update t_student set sex = '男' where snum = 9;
UPDATE T_STUDENT SET SEX = '男' where snum = 1;
update t_student set class = 'PYTH1' where snum = 1;
update t_student set class = "pyth1" where snum=2;
update t_student set sname = 'nope' where class = 'pyth1';
#限制字段

select * from t_student;
delete from t_student where snum = 2;

6. 修改和删除数据库表

select * from t_student;

alter table t_student add score double(5,2);
update t_student set score = 123.5678 where snum = 9;

update t_student set snum = 1 where enterdate = '2023-02-12';


alter table t_student add first_column int(6) first;
alter table t_student add special_column int(6) after sex;

alter table t_student drop first_column;


alter table t_student modify score float(4,1);
alter table t_student change score score1 double (5,1);

drop table t_student;

7.非外键约束

create table t_student_1(
	snum int(6) primary key AUTO_INCREMENT,
	sname varchar(5) not null,
	sex char(1) default '男' check(sex='男' || sex='女'),
	age int(3) check(age>=18 and age<=50),
	enterdate date,
	class varchar(6),
	email varchar(15) unique
	constraint pk_stu primary key (sno),  -- pk_stu 主键约束的名字
	constraint ck_stu_sex check (sex = '男' || sex = '女'),
  constraint ck_stu_age check (age >= 18 and age <= 50),
  constraint uq_stu_email unique (email)

);
-- •	学号是主键 = 不能为空 +  唯一 
-- •	姓名不能为空 
-- •	性别默认值是男 
-- •	Email唯一 
select * from t_student_1;


drop table t_student;
create table t_student(
	sno int(6),
	sname varchar(5),
	sex char(1),
	age int(3),
	enterdate date,
	class varchar(6),
	email varchar(15)
);

alter table t_student add constraint pk_stu primary key (sno) ; -- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 修改自增条件(由于自增必须先设置主键，所以才有了上面第21行)
alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50);
alter table t_student add constraint uq_stu_email unique (email);

-- 查看表结构：
desc t_student;
	
insert into t_student values (1,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (10010010,'李四','男',18,'2022-5-8','软件1班','124@126.com');

select * from t_student;

8.外键约束

create table t_class(
cno int(4) primary key auto_increment,
cname varchar(10) not null,
room char(4)
)

insert into t_class values (null,'java001','r803');
insert into t_class values (null,'java002','r416');
insert into t_class values (null,'大数据001','r103');

insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');

select * from t_class;

drop table t_student;

create table t_student(
	snum int(6) primary key auto_increment,
	sname varchar(5) not null,
	class int(4),
	constraint fk_stu_classno foreign key(class) references t_class(cno) -- 法1
);

insert into t_student values (null,'张三','1'),(null,'李晓明','2'),(null,'阳光男孩','3');

select * from t_student;

alter table t_student add constraint fk_stu_classno foreign key(class) references t_class(cno); -- 法2

9.外键策略

-- 先删从表再删主表 不然会删不掉
drop table t_student;
drop table t_class;

create table t_class(
cno int(4) primary key auto_increment,
cname varchar(10) not null,
room char(4)
);

create table t_student(
	snum int(6) primary key auto_increment,
	sname varchar(5) not null,
	class int(4),
	constraint fk_stu_classno foreign key(class) references t_class(cno) -- 法1
);

insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
insert into t_student values (null,'张三','1'),(null,'李晓明','2'),(null,'阳光男孩','3');

select * from t_class;
select * from t_student;

-- 删除有外键约束的父表记录，策略1为no action，不允许.也即只能先将子表相关记录置空，然后再删父表记录，
update t_student set class = null where class = 2;
delete from t_class where cno =2;

-- 策略2为cascade级联操作（全影响）,先删外键约束，再新建外键约束
alter table t_student drop foreign key fk_stu_classno;
alter table t_student add constraint fk_stu_classno foreign key(class) references t_class(cno) on update cascade on delete cascade;

update t_class set cno = 5 where cno = 3;
delete from t_class where cno = 5;

-- 策略3为setnull，也即动了以后，别的相关的全变成null。
alter table t_student drop foreign key fk_stu_classno;
alter table t_student add constraint fk_stu_classno foreign key(class) references t_class(cno) on update set null on delete set null;

update t_class set cno = 8 where cno = 1;

-- 注意：可混用。比如
alter table t_student add constraint fk_stu_classno foreign key(class) references t_class(cno) on update cascade on delete set null;

10.杂项-快速拷贝表

-- 结构√数据√
create table t_student2
as
select * from t_student;

select * from t_student2;

-- 结构√数据×
create table t_student3
as
select * from t_student where 1=2;

select * from t_student3;

-- 结构√部分列数据√
create table t_student4
as 
select snum,sname from t_student where snum = 2;
select * from t_student4;


-- 清空数据的两条相似操作：delete和truncate（区别-面试题）
delete from t_student;-- 不填where xxx 意思是 全删。 一条一条删数据。
truncate table t_student; -- 重新创表，效率高 

11.表的准备

create table DEPT(  
  DEPTNO int(2) not null,  
  DNAME  VARCHAR(14),  
  LOC    VARCHAR(13)  
);  

alter table DEPT  
  add constraint PK_DEPT primary key (DEPTNO); 
        
create table EMP  
(  
  EMPNO    int(4) primary key,  
  ENAME    VARCHAR(10),  
  JOB      VARCHAR(9),  
  MGR      int(4),  
  HIREDATE DATE,  
  SAL      double(7,2),  
  COMM     double(7,2),  
  DEPTNO   int(2)  
);  

alter table EMP  
  add constraint FK_DEPTNO foreign key (DEPTNO)  
  references DEPT (DEPTNO);  
        
create table SALGRADE  
(  
  GRADE int primary key,  
  LOSAL double(7,2),  
  HISAL double(7,2)  
);  
create table BONUS  
(  
  ENAME VARCHAR(10),  
  JOB   VARCHAR(9),  
  SAL   double(7,2),  
  COMM  double(7,2)  
);  

insert into DEPT (DEPTNO, DNAME, LOC)  
values (10, 'ACCOUNTING', 'NEW YORK');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (20, 'RESEARCH', 'DALLAS');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (30, 'SALES', 'CHICAGO');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (40, 'OPERATIONS', 'BOSTON');  


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  


insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (1, 700, 1200);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (2, 1201, 1400);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (3, 1401, 2000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (4, 2001, 3000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (5, 3001, 9999);  


select * from DEPT;

select * from emp;

select * from SALGRADE;  

select * from BONUS;  

12.单表查询

-- 对emp表查询：
select * from emp; -- *代表所有数据
-- 显示部分列：
select empno,ename,sal from emp;
-- 显示部分行：where子句
select * from emp where sal > 2000;
-- 显示部分列，部分行：
select empno,ename,job,mgr from emp where sal > 2000;

-- 起别名：
select empno 员工编号,ename 姓名,sal 工资 from emp; -- as 省略，''或者""省略了
-- as alias 别名
select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
select empno as '员工编号',ename as "姓名",sal as 工资 from emp;
-- > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '编号,ename as "姓 名",sal as 工资 from emp' at line 1
-- 错误原因：在别名中有特殊符号的时候，''或者""不可以省略不写
select empno as 员工 编号,ename as "姓 名",sal as 工资 from emp;

-- 算术运算符：
select empno,ename,sal,sal+1000 as '涨薪后',deptno from emp where sal < 2500;
select empno,ename,sal,comm,sal+comm from emp;  -- ？？？null和0不一样，null+任何值=null事实上不应该

-- 去重操作：
select job from emp;
select distinct job from emp;
select job,deptno from emp;
select distinct job,deptno from emp; -- 对后面的所有列组合 去重 ，而不是单独的某一列去重

-- 排序：
select * from emp order by sal; -- 默认情况下是按照升序排列的
select * from emp order by sal asc; -- asc 升序，可以默认不写
select * from emp order by sal desc; -- desc 降序
select * from emp order by sal asc ,deptno desc; -- 在工资升序的情况下，deptno按照降序排列

13.where子句

-- 查看emp表：
select * from emp;

-- where子句：将过滤条件放在where子句的后面，可以筛选/过滤出我们想要的符合条件的数据：
-- where 子句 + 关系运算符
select * from emp where deptno = 10;
select * from emp where deptno > 10;
select * from emp where deptno >= 10;
select * from emp where deptno < 10;
select * from emp where deptno <= 10;
select * from emp where deptno <> 10; -- 跟下面的不等于一样
select * from emp where deptno != 10;
select * from emp where job = 'CLERK'; 
select * from emp where job = 'clerk'; -- 默认情况下不区分大小写 
select * from emp where binary job = 'clerk'; -- binary区分大小写
select * from emp where hiredate < '1981-12-25';

-- where 子句 + 逻辑运算符：and 

select * from emp where sal > 1500 and sal < 3000;  -- (1500,3000)
select * from emp where sal > 1500 && sal < 3000; 
select * from emp where sal > 1500 and sal < 3000 order by sal;
select * from emp where sal between 1500 and 3000; -- [1500,3000]


-- where 子句 + 逻辑运算符：or

select * from emp where deptno = 10 or deptno = 20;
select * from emp where deptno = 10 || deptno = 20;
select * from emp where deptno in (10,20); -- 这个的意思是 集合，看下面就知道了。集合元素就是10和20
select * from emp where job in ('MANAGER','CLERK','ANALYST');


-- where子句 + 模糊查询：
-- 查询名字中带A的员工  -- %代表任意多个字符 0,1,2，.....
select * from emp where ename like '%A%' ;
-- -任意一个字符
select * from emp where ename like '__A%' ;


-- 关于null的判断：
select * from emp where comm is null;
select * from emp where comm is not null;

select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or  and > or
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500); 
select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;

14. 单行多行函数

-- 函数举例：
select empno,ename,lower(ename),upper(ename),sal from emp;
-- 函数的功能：封装了特定的一些功能，我们直接拿过来使用，可以实现对应的功能
-- 函数作用：为了提高select的能力
-- 注意：函数没有改变数据自身的值，而是在真实数据的上面进行加工处理，展示新的结果而已。

select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;
-- 函数的分类：
-- lower(ename),upper(ename) ：改变每一条结果，每一条数据对应一条结果  -- 单行函数
-- max(sal),min(sal),count(sal),sum(sal),avg(sal):多条数据，最终展示一个结果  -- 多行函数

-- 单行函数包含：
-- 1.字符串函数
select ename,length(ename),substring(ename,2,3) from emp;
-- substring字符串截取，2:从字符下标为2开始，3：截取长度3    （下标从1开始）
-- 2.数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表 
select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入;  -- 如果没有where条件的话，from dual可以省略不写
select ceil(sal) from emp;
select 10/3,10%3,mod(10,3) ;
-- 3.日期与时间函数 
select * from emp;
select curdate(),curtime() ; -- curdate()年月日 curtime()时分秒
select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒
-- now()与sysdate的差异：睡3秒后，now()不更新，sysdate()更新，这是因为sysdate()是函数执行的时间

insert into emp values (9999,'lili','SALASMAN',7698,now(),1000,null,30);
-- now()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构的
desc emp;

-- 4.流程函数
-- if相关
select empno,ename,sal,if(sal>=2500,'高薪','低薪') as '薪资等级' from emp; -- if-else 双分支结构
select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- 如果comm是null，那么取值为0 -- 单分支
select nullif(1,1),nullif(1,2) from dual; --  如果value1等于value2，则返回null，否则返回value1  
-- case相关：
-- case等值判断
select empno,ename,job,
case job 
 when 'CLERK' then '店员'
 when 'SALESMAN'  then '销售'
 when 'MANAGER' then '经理'
 else '其他'
end '岗位',
sal from emp;

-- case区间判断:
select empno,ename,sal,
case 
 when sal<=1000 then 'A'
 when sal<=2000 then 'B'
 when sal<=3000 then 'C'
 else 'D'
end '工资等级',
deptno from emp;


-- 5.JSON函数  

-- 6.其他函数
select database(),user(),version() from dual;

-- 7.多行函数（1-6都是单行函数）
select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
select * from emp;
-- 多行函数自动忽略null值
select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
-- max(),min(),count()针对所有类型   sum(),avg() 只针对数值型类型有效
select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;

-- count --计数   
-- 统计表的记录数：方式1：
select * from emp;
select count(ename) from emp;
select count(*) from emp;

-- 统计表的记录数：方式2
select 1 from dual;
select 1 from emp;
select count(1) from emp;

15. group by 和 having

select * from emp;
select deptno from emp;
select avg(sal) from emp;

select deptno,avg(sal) from emp;-- 不科学！

select deptno,avg(sal) from emp group by deptno; -- 字段和多行函数不可以同时使用,除非这个字段属于分组
select deptno,avg(sal) from emp group by deptno order by deptno desc; -- 还可以加个排序

-- 统计各个岗位的平均工资
select job,avg(sal) from emp group by job;
select job,lower(job),avg(sal) from emp group by job;


-- 统计各个部门的平均工资 ,只显示平均工资2000以上的  - 分组以后进行二次筛选 having
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;
-- 统计各个岗位的平均工资,除了MANAGER
-- 方法1：
select job,avg(sal) from emp where job != 'MANAGER' group by job;
-- 方法2：
select job,avg(sal) from emp group by job having job != 'MANAGER' ;
-- where在分组前进行过滤的，having在分组后进行后滤。

小结1-单表查询总结

• select column, group_function(column) from table [where condition] [group by group_by_expression] [having group_condition] [order by column]; 顺序固定，不可以改变顺序。

• 执行顺序：from–where – group by– select - having- order by

• 小练习：

select * from emp;
select * from dept;

select job , min(sal) from emp where sal<2000 group by job;
select job , min(sal) from emp group by job having min(sal)<2000;

select job , deptno, avg(sal) from emp group by job,deptno having avg(sal)>1200 order by deptno;

select deptno,count(deptno),avg(sal) from emp group by deptno having count(deptno)<4 order by deptno;

select deptno,max(sal) from emp group by deptno having max(sal)>=3000 order by deptno;

16. 多表查询-内连接

-- 多表查询 ：
-- 1.交叉连接：cross join
select * from emp
cross join dept; -- 14*4 = 56条 笛卡尔乘积 ： 没有实际意义，有理论意义, 不常用。

select * from emp
join dept; -- cross 可以省略不写，mysql中可以，oracle中不可以


-- 2.自然连接：natural join 
-- 优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单
select * 
from emp
natural join dept;

select empno,ename,sal,dname,loc 
from emp
natural join dept;

-- 缺点： 查询字段的时候，没有指定字段所属的数据库表，效率低
-- 解决： 指定表名：
select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno
from emp
natural join dept;

-- 缺点：表名太长
-- 解决：表起别名
select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
from emp e
natural join dept d;

-- 自然连接 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列：
-- 解决： 3.内连接 - using子句：
select *
from emp e
inner join dept d -- inner可以不写 。-- 这里不能写natural join了 ,这里是内连接
using (deptno) -- 记得写括号，虽然不咋用这个using就是了

-- using缺点：关联的字段，必须是同名的 
-- 解决： 内连接 - on子句：
select * 
from emp e
inner join dept d
on (e.deptno = d.deptno);

-- 多表连接查询的类型： 1.交叉连接  cross join  2. 自然连接  natural join  
-- 3. 内连接 - using子句   4.内连接 - on子句
-- 综合看：内连接中， on子句用得最多。

select * 
from emp e
inner join dept d
on (e.deptno = d.deptno)
where sal > 3500; -- 还能再加一个where子句！

-- 条件：
-- 1.筛选条件  where  having
-- 2.连接条件 on,using,natural 
-- SQL99语法 ：筛选条件和连接条件是分开的

-- inner join - on子句： 显示的是所有匹配的信息（不匹配的完全没显示出来。）
select * 
from emp e
inner join dept d
on e.deptno = d.deptno;

17.多表查询-外连接

select * from emp;
select * from dept;
-- 问题：
-- 1.40号部分没有员工，没有显示在查询结果中
-- 2.员工scott没有部门，没有显示在查询结果中

-- 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据

-- 左外连接： left outer join   -- 左面的那个表的信息，即使不匹配也可以查看出效果
select * 
from emp e
left outer join dept d
on e.deptno = d.deptno;



-- 右外连接： right outer join   -- 右面的那个表的信息，即使不匹配也可以查看出效果
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;

-- 全外连接  full outer join -- 这个语法在mysql中不支持，在oracle中支持 -- 展示左，右表全部不匹配的数据 
-- scott ，40号部门都可以看到
select * 
from emp e
full outer join dept d
on e.deptno = d.deptno;


-- 解决mysql中不支持全外连接的问题：

select * 
from emp e
left outer join dept d
on e.deptno = d.deptno
union -- 两个之间加了一个union，表示两张表的合在一起，数据的并集，去重（此处指完全匹配的重复去掉）。效率低
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;

select * 
from emp e
left outer join dept d
on e.deptno = d.deptno
union all-- 加了一个 all 并集 不去重 效率高
select * 
from emp e
right outer join dept d
on e.deptno = d.deptno;

-- mysql中对集合操作支持比较弱，只支持并集操作，交集，差集不支持（oracle中支持）
-- outer可以省略不写

18.多表联查

-- ☆三表连查
-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
select * from emp;
select * from dept;
select * from salgrade;


select e.ename,e.sal,e.empno,e.deptno,d.dname,s.* from emp e 
right outer join dept d on e.deptno = d.deptno -- 外连接是因为 可能存在deptno为空但是要连起来放出来的情况。此处e.xxx=y.lll，字段名称可以是不同的。
inner join salgrade s on e.sal between s.losal and s.hisal; -- 内连接是因为不存在salgrade为空。就可以直接连起来。
-- 但是需要加一个on子句，而不是用交叉连接、自然连接或using
-- 为什么？交叉连接肯定不对。自然连接查了相同字段发现没有，又变成交叉连接，肯定也不对。using是只查指定字段，但是本来就没有相同的，所以也不对。最后就剩on子句。刚刚提到on子句是因为俩不重名，用on e.xxx=y.lll解决了，其实on就是一个条件。

19.自关联

-- 查询员工的编号、姓名、上级编号,上级的姓名
select * from emp;

select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

-- 左外连接：（为了避免没有领导的员工不在这张大表中显示的情况。）
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
from emp e1
left outer join emp e2
on e1.mgr = e2.empno;

注：92语法比此处99语法少了很多内置的连接类型，但大部分都可以通过where进行手动限制。

20.单行子查询

-- 单行子查询：
-- 查询工资高于平均工资的雇员名字和工资。
select ename,sal from emp where sal > (select avg(sal) from emp); -- 因为后面这个不是定值，也得查出来。后面这个查出来是一个值，所以叫“单行”

-- 查询和CLARK同一部门且比他工资低的雇员名字和工资。
select ename,sal from emp where deptno = (select deptno from emp where ename = 'CLARK') 
and 
sal < (select sal from emp where ename = 'CLARK');

-- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息  
select * from emp where job = (select job from emp where ename = 'SCOTT') 
and 
hiredate < (select hiredate from emp where ename = 'SCOTT');

21.多行子查询

-- 多行子查询：
-- 【1】查询【部门20中职务同部门10的雇员一样的】雇员信息。
-- 查询雇员信息
select * from emp;
-- 查询部门20中的雇员信息
select * from emp where deptno = 20;-- CLERK,MANAGER,ANALYST
-- 部门10的雇员的职务：
select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK
-- 查询部门20中职务同部门10的雇员一样的雇员信息。
select * from emp 
where deptno = 20 
and job in (select job from emp where deptno = 10)
-- > Subquery returns more than 1 row
select * from emp 
where deptno = 20 
and job = any(select job from emp where deptno = 10)


-- 【2】查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
-- 查询雇员的编号、名字和工资
select empno,ename,sal from emp
-- “SALESMAN”的工资：
select sal from emp where job = 'SALESMAN'
-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
-- 多行子查询：
select empno,ename,sal 
from emp 
where sal > all(select sal from emp where job = 'SALESMAN');
-- 单行子查询：
select empno,ename,sal 
from emp 
where sal > (select max(sal) from emp where job = 'SALESMAN');


-- 【3】查询工资低于任意一个“CLERK”的工资的雇员信息。  
-- 查询雇员信息
select * from emp;
-- 查询工资低于任意一个“CLERK”的工资的雇员信息
select * 
from emp
where sal < any(select sal from emp where job = 'CLERK')
and job != 'CLERK'

-- 单行子查询：
select * 
from emp
where sal < (select max(sal) from emp where job = 'CLERK')
and job != 'CLERK'

22.相关子查询

-- 相关子查询不能先运行子句再运行前面那句，因为本身是相关的。
select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno

-- 练习：查询工资高于其所在岗位平均工资的员工（相关子查询）
select ename 姓名,job 岗位, sal 工资 , (select avg(sal) from emp where job = e.job) 平均工资  from emp e where sal >= (select avg(sal) from emp where job = e.job) order by deptno;-- 等号是怕一个部门只有一个人

23.事务

-- 创建账户表：
create table account(
        id int primary key auto_increment,
        uname varchar(10) not null,
        balance double
);

-- 查看账户表：
select * from account;
-- 在表中插入数据：
insert into account values (null,'丽丽',2000),(null,'小刚',2000);

-- 丽丽给小刚 转200元：
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- 默认一个DML语句是一个事务，所以上面的操作执行了2个事务。

update account set balance = balance - 200 where id = 1;
update account set balance = balance2 + 200 where id = 2;

-- 必须让上面的两个操作控制在一个事务中：

-- 手动开启事务：
start transaction;

update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;

-- 手动回滚：刚才执行的操作全部取消：
rollback;
-- 手动提交：
commit;

-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

事务的并发问题：脏读（感觉是读了缓存）、不可重复读（需锁一行）、幻读（需锁表）
解决并发问题的4种手段（事务隔离级别）。

-- 查看默认的事务隔离级别  MySQL默认的是repeatable read  
select @@transaction_isolation;  
-- 设置事务的隔离级别   （设置当前会话的隔离级别）
set session transaction isolation level read uncommitted;  
set session transaction isolation level read committed;  
set session transaction isolation level repeatable read;  
set session transaction isolation level serializable;  

start transaction ;
select * from account where id = 1;