MySQL 数据库

MySQL 数据库

概念

1. 数据：所谓数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储和处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂了。
2. 数据库：数据库（Database，DB）指的是以一定格式存放、能够实现多个用户共享、与应用程序彼此独立的数据集合。
3. 数据库管理系统：数据库管理系统（Database Management System，DBMS）是用来定义和管理数据的软件。如何科学的组织和存储数据，如何高效的获取和维护数据，如何保证数据的安全性和完整性，这些都需要靠数据库管理系统完成。目前，比较流行的数据库管理系统有：Oracle、MySQL、SQL Server、DB2等。
4. 数据库应用程序：数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，如学生管理系统、人事管理系统、图书管理系统等。
5. 数据库管理员：数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。
6. 最终用户：最终用户（User）指的是数据库应用程序的使用者。用户面向的是数据库应用程序（通过应用程序操作数据），并不会直接与数据库打交道。
7. 数据库系统：数据库系统（Database System，DBS）一般是由数据库、数据库管理系统、数据库应用程序、数据库管理员和最终用户构成。其中DBMS是数据库系统的基础和核心。

Mysql的优点

1. 开放源代码
2. 跨平台
3. 轻量级
4. 成本低
   总体来说，MySQL是一款开源的、免费的、轻量级的关系型数据库，其具有体积小、速度快、成本低、开放源码等优点，其发展前景是无可限量的。

SQL语言：

1. 数据查询语言（DQL）：DQL主要用于数据的查询，其基本结构是使用select子句，from子句和where子句的组合来查询数据
2. 数据操作语言（DML）：DML主要用于对于数据库中的数据进行新增、修改、删除的操作，包括insert、update、delete
3. 数据定义语言（DDL）：DDL主要针对是数据库对象进行创建、修改、删除操作，包括create、alter、drop
4. 数据控制语言（DCL）：DCL用来授予或回收访问数据库的权限，包括grant、revoke
5. 事务控制语言（TCL）：TCL用于数据库的事务管理，包括：start transaction、commit、rollback、set transaction

建表

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

-- 建立一张用来存储学生信息的表
-- 字段包括学号、姓名、性别、年龄、入学日期、班级、email等信息
-- 创建数据库表：
create TABLE t_student(
	sno int(6), -- 6显示长度
	sname VARCHAR(10),-- 10个字符
	sex char(1),-- 1个字符
	age int(3),
	enterdate DATE,
	classname VARCHAR(10),
	email VARCHAR(50)
);

-- 查看表的结构,展示表的字段详细信息
desc t_student;
-- 查看表中数据
select * from t_student;

-- 查看建表语句
show create table t_student;
/*
CREATE TABLE `t_student` (
  `sno` int DEFAULT NULL,
  `sname` varchar(10) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

数据库表列的类型：

1. 整数类型：显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示
2. 浮点数类型：与整数类型不一样的是，浮点数类型的宽度不会自动扩充。
   score double(4,1)–小数部分为1位，总宽度4位，并且不会自动扩充。
3. 字符串类型：CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。
4. 时间和日期类型：DATE:YYYY-MM-DD DATETIME:YYYY-MM-DD HH:MM:SS

增删改查数据

-- 在t_student数据表中加入数据：
SELECT * from t_student;

INSERT into t_student VALUES(1,'张三','男',18,'2022-5-8','计算机1班','13@163.com');
INSERT into t_student VALUES(10010011,'张三','男',18,'2022-5-8','计算机1班','13@163.com');
INSERT into t_student VALUES(2,'张三','男',18,'2022.5.8','计算机1班','13@163.com');
INSERT into t_student VALUES(2,'张三','男',18,'2022/5/8','计算机1班','13@163.com');
INSERT into t_student VALUES(3,'张三','男',18,NOW(),'计算机1班','13@163.com');
INSERT into t_student VALUES(4,'按键上的开奖号','男',18,CURRENT_DATE(),'计算机1班','13@163.com');
INSERT into t_student
(sno,sname,enterdate)
VALUES
(6,'按键上的开奖号',SYSDATE()),
(7,'按键上的开奖号2',SYSDATE());

-- 修改表中的数据
UPDATE t_student set sex = '女' WHERE sno = '1';
UPDATE t_student set classname = 'python01' WHERE sno = '1';
UPDATE t_student set classname = 'PYTHON01' WHERE sno = '3';
UPDATE t_student set age = 30 WHERE classname = 'PYTHON01' ;# 查询的时候是不区分大小写的

-- 删除操作
DELETE FROM t_student WHERE sno = 2;

修改表结构

-- 查看数据
select * from t_student;

-- 修改表结构
-- 增加一列
ALTER TABLE t_student
add score double(5,2) ; -- 5:总位数，2，小数位数

update t_student set score = 123.456874 where sno = 5;

-- 删除一列
ALTER TABLE t_student
DROP score;

-- 增加一列，放在最前面
ALTER TABLE t_student
add score double(5,2) first; 

-- 增加一列，放在sex后面
ALTER TABLE t_student
add score double(5,2) after sex; 

-- 修改一列
alter table t_student
MODIFY score float(4,1); -- modify修改的是列的类型的定义，不修改列的名字

-- 修改列的名字
alter table t_student
CHANGE score score1 DOUBLE(5,1);-- change 修改列的名字和类型

-- 删除表
DROP TABLE t_student;

表的完整性约束

非外键约束

-- 建立一张用来存储学生信息的表
-- 字段包括学号、姓名、性别、年龄、入学日期、班级、email等信息
-- 创建数据库表：
/*
1. 学号是主键，不能为空，唯一，主键的作用：可以通过主键查到唯一的一条记录
2. 主键如果是整形，那么需要自增
3. 姓名不能为空
4. email唯一
5. 性别默认是男
6. 性别只能是男或女
7. 年龄只能在18-50之间
*/
-- 列级约束
create TABLE t_student(
	sno int(6) PRIMARY KEY auto_increment, -- 6显示长度
	sname VARCHAR(10) not null,-- 10个字符
	sex char(1) DEFAULT '男' CHECK(sex='男'|| sex = '女'),-- 1个字符
	age int(3) check(age <= 50 and age >=18),
	enterdate DATE,
	classname VARCHAR(10),
	email VARCHAR(50) UNIQUE
);

-- 表级约束
create TABLE t_student(
	sno int(6) auto_increment, -- 6显示长度
	sname VARCHAR(10) not null,-- 10个字符
	sex char(1) default '男',-- 1个字符
	age int(3),
	enterdate DATE,
	classname VARCHAR(10),
	email VARCHAR(50),
	CONSTRAINT pk_stu PRIMARY KEY (sno),-- 主键约束的名字
	CONSTRAINT ck_stu_sex check(sex='男'||sex='女'),
	constraint ck_stu_age check(age >=18 and age <= 50),
	constraint up_stu_email unique(email)
);

drop table t_student;
create TABLE t_student(
	sno int(6) , -- 6显示长度
	sname VARCHAR(10) not null,-- 10个字符
	sex char(1) default '男',-- 1个字符
	age int(3),
	enterdate DATE,
	classname VARCHAR(10),
	email VARCHAR(50)
);
-- 在创建表以后添加约束
alter table t_student add CONSTRAINT  pk_stu primary key (sno);-- 主键约束
alter table t_student modify sno int(6) auto_increment;-- 修改自增条件
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 up_stu_email unique(email);

desc t_student;

总结：

1. 主键约束：主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。主键可以是单个字段，也可以是多个字段组合。对于单字段主键的添加可使用表级约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。
2. 非空约束：非空约束（NOT NULL，缩写NK）规定了一张表中指定的某个字段的值不能为空（NULL）
3. 唯一约束：唯一约束（UNIQUE，缩写UK）比较简单，它规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的。
4. 检查约束：查约束（CHECK）用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。MySQL8开始支持检查约束。
5. 默认值约束 ：默认值约束（DEFAULT）用来规定字段的默认值
6. 字段值自动增加约束：自增约束（AUTO_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束）。

外键约束

外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

-- 先创建父表：班级表：
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','r804');
insert into t_class values(null,'java002','r416');
insert into t_class values(null,'大数据001','r102');

-- 可以一次性添加多条数据
insert into t_class values(null,'java001','r804'),(null,'java002','r416'),(null,'大数据001','r102');

-- 添加外键约束
-- 外键约束，只有表级约束，没有列级约束
create TABLE t_student(
	sno int(6) PRIMARY KEY auto_increment, -- 6显示长度
	sname VARCHAR(10) not null,-- 10个字符
	classno int(4), -- 取值参考t_class表中的cno字段，
	constraint fk_stu_classno foreign key (classno) references t_class(cno)
);
-- 创建表以后添加外键约束
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno);

delete from t_class a where a.cno = 1;
insert into t_student values(null,'李四',1),(null,'张三',2),(null,'王五',3);

-- 删除班级2：如果直接删除的话肯定不能，因为有外键约束
-- 加入外键策略
-- 策略1：no action 不允许操作
-- 通过sql来完成：
-- 先把班级2的学生对应的班级改为null，再删除班级2
update t_student set classno = null where classno = 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 (classno) 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：set null 置空操作
-- 先删除之前的外键约束，再重新添加外键约束
alter table t_student drop foreign key fk_stu_classno;
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno) on update set null on delete set null;

update t_class set cno = 3 where cno=8;
update t_student set classno = 8 where classno = 1;

desc t_student;

-- 注意：
-- 策略2 级联操作和策略3的删除操作可以混着使用
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno) on update cascade on delete set null;

-- 场合：
-- 删除朋友圈，点赞和留言都删除  -- 级联
-- 解散班级：学生班级置空 

DDL和DML的一些其他操作

create TABLE t_student(
	sno int(6) PRIMARY KEY auto_increment, -- 6显示长度
	sname VARCHAR(10) not null,-- 10个字符
	sex char(1) DEFAULT '男' CHECK(sex='男'|| sex = '女'),-- 1个字符
	age int(3) check(age <= 50 and age >=18),
	enterdate DATE,
	classname VARCHAR(10),
	email VARCHAR(50) UNIQUE
);
-- 添加数据
insert into t_student
VALUES(1,'张三','男',20,'2020-9-1','python01','123@163.com');
insert into t_student
VALUES(2,'李四','男',30,'2020-9-1','python01','ls3@163.com');
INSERT INTO t_student (sname,enterdate) VALUES('菲菲','2021-9-6');
insert into t_student
VALUES(null,'名','男',20,'2020-9-1','python01','m3@163.com');
insert into t_student
VALUES(default,'gang','男',20,'2020-9-1','python01','g3@163.com');

-- 添加一张表：快速添加
create table t_student2
as 
select * from t_student;-- 结构和数据跟t_student都是一直的

-- 快速添加：结构和t_student都是一直的
create table t_student3
as 
select * from t_student where 1 = 2;

select * from t_student3;

-- 只要部分列，部分数据
create table t_student4
as 
select sno,sname,age from t_student where sno = 2;
select * from t_student4;

-- 删除数据
delete from t_student ;
truncate table t_student;

delete和truncate的区别：
从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：

1. DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL。
2. DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，TRUNCATE操作的效率更高。
3. DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚。
4. DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected rows：0”。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；TRUNCATE操作则会重新从1开始自增。

DQL-查询操作

单表查询

select * from emp; -- *显示所有数据
-- 显示部分列
select empno,ename,sal from emp;
-- 显示部分行：where子句
select * from emp where sal > 2000;
-- 显示部分列，部分行
select empno,ename,sal from emp where sal > 2000;

-- 起别名：
select empno 员工编号,ename 姓名, sal 工资 from emp;
-- 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 '涨薪后',deptno from emp where sal < 2500;

select empno,ename,sal,comm,sal+comm 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;-- 升序可以不写
select * from emp order by sal  desc;-- desc降序
select * from emp order by sal  asc, deptno desc;-- 在工资升序的情况下，deptno按照降序排列

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 < '1982-12-25';

-- where 子句+逻辑运算符 and 
select * from emp where sal > 1500 and sal < 3000;
select * from emp where sal > 1500 and sal < 3000 order by sal;
select * from emp where sal between 1500 and 3000 order by sal;-- [1500,3000]
select * from emp where sal > 1500 && sal < 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);
select * from emp where job in ('MANAGER','PRESIDENT');

-- 模糊查询 % 代表任意多个字符
-- 查新名字中带A的员工
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;
Select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >= 1500;
Select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >= 1500);

函数

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

-- 函数的分类：
-- 单行函数
-- 1.字符串函数
select ename,length(ename),substr(ename,2,3) from emp;-- substr(ename,2,3),截取从2开始截取长度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 10/3,10%3,mod(10,3) ;
-- 3.日期与时间函数
select * from emp;
select CURDATE(),CURTIME(); -- 年月日，时分秒
select now(),SYSDATE(),sleep(3),now(),SYSDATE();-- now(),SYSDATE():年月日时分秒，
insert into emp values(9999,'lili','salesman',7698,now(),1000,null,30);
-- now()可以表示年月日时分秒，但是插入的时候还是要参考表的结构

-- 4. 流程函数
-- if相关
select empno,ename,sal,if(sal >= 2500,'高薪','低薪') '薪资等级' from emp;
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;

-- 多行函数：max(sal),min(sal),count(sal),sum(sal),avg(sal)
 -- 除了多行函数，都是单行函数
select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;
-- 多行函数会自动忽略null值
select max(comm),min(comm),count(comm),sum(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 count(*) from emp;
-- 方式2
select 1 from dual;
select count(1) from emp;

group by

select * 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,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 avg(sal) > 2000 order by deptno desc;

-- 统计各个岗位的平均工资，除了manager
-- 方法1：
select job,avg(sal) from emp where job != 'MANAGER' group by job;
-- 方法2：
select job,avg(job) from emp group by job having job != 'MANAGER';
-- where 在分组前进行过滤的，having在分组后进行过滤

多表查询

交叉连接，自然连接，内连接查询

-- 查询员工的编号，姓名，部门编号,部门名称：
-- 多表查询
-- 交叉连接 cross join
select * from emp cross join dept;
select * from emp join dept;-- mysql中cross可以省略不写，oracle不可以

-- 自然连接：natural join:
-- 优点：自动匹配所有的同名列，同名列只展示一次，简单
-- 缺点：查询字段的时候，没有指定字段所属的数据库表，效率低
select emp.empno,emp.ename,emp.sal,dept.deptno,dept.dname,dept.loc
from emp
natural join dept;

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

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

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

-- 多表连接查询的类型：1. 交叉连接 cross join， 2. 自然连接 natural join  3. 内连接 inner join using子句 4. 内连接 on子句
select * 
from emp e
INNER JOIN dept d
on(e.deptno = d.deptno)
where e.sal > 3500;

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

外连接

-- inner join on子句:显示的是所有匹配的信息
select *
from emp e
inner join dept d
on e.deptno = d.DEPTNO;

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

-- 外连接，除了显示匹配的数据之外，还可以显示不匹配的数据，outer可以省略不写
-- 左外连接 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
right outer join dept d
on e.deptno = d.DEPTNO
union -- 并集，去重，效率低
select *
from emp e
left outer join dept d
on e.deptno = d.DEPTNO;

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

-- mysql中对集合操作支持比较弱，只支持并集操作，交集，差集不支持（oracle中支持）

三表查询

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

select e.empno,e.ename,e.sal,e.deptno,d.DNAME,s.grade
from emp e
right join dept d
on e.deptno = d.DEPTNO
join salgrade s
on e.sal BETWEEN s.losal and s.hisal
order by e.sal;

自连接

-- 查询员工的编号，姓名，上级编号，上级姓名
select e1.empno 员工编号,e1.ename 员工姓名,e2.empno 领导编号,e2.ename 领导名称 from emp e1
left join emp e2
on e1.mgr = e2.empno;

92语法的多表查询

-- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称：
select empno,ename,sal,e.deptno,dname from emp e,dept d;
-- 相当于99语法中的cross join 笛卡尔积，没有意义
select empno,ename,sal,e.deptno,dname from emp e,dept d
where e.deptno = d.DEPTNO;
-- 相当于99语法中的natural join

-- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称，工资大于2000的员工：
select empno,ename,sal,e.deptno,dname from emp e,dept d
where e.deptno = d.DEPTNO
and e.sal > 2000;

-- 查询员工的姓名，岗位，上级编号，上级名称（自连接）：
select e1.empno,e1.job,e1.mgr,e2.ename from 
emp e1,emp e2
where e1.mgr = e2.ename;
-- 查询员工的编号，姓名，薪水，部门编号，部门名称，薪水等级
select e.empno,e.ename,e.sal,e.deptno,d.DNAME,s.grade from
emp e, dept d, salgrade s
where e.deptno = d.DEPTNO
and e.sal >= s.losal
and e.sal <= s.hisal;

-- 总结：
-- 1. 92语法麻烦
-- 2. 92语法中表的连接条件和筛选条件是放在一起的，没有分开
-- 3. 99语法中提供了更多的查询连接类型：cross,natural,inner,outer

子查询

不相关子查询

单行子查询

-- 查询所有比“CLARK” 工资高的员工信息
-- 1. 查询clark工资
select sal from emp where ename = 'CLARK'; 
-- 2. 查询所有工资比2450高的员工信息
select * from emp where sal > 2450;
-- 两次查询解决问题--》效率低，第二个命令依托于第一个命令，第一个命令的结果提供给第二个命令使用，但是
-- 因为第一个命令的结果可能不确定要改，所以第二个命令也会导致修改
-- 将步骤1和步骤2合并
select * from emp where sal > (select sal from emp where ename = 'CLARK');
-- 一个命令解决问题--》效率高：先执行子查询，在执行外查询
-- 一个子查询可以独立运行，称为不相关子查询

-- 单行子查询：
-- 查询工资高于平均工资的雇员名字和工资：
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');

多行子查询

-- 多行子查询：
-- 1. 查询部门20中职务同部门10的雇员一样的雇员 信息
select * from emp where deptno = 20 and job in (select job from emp where deptno = 10);
select * from emp where deptno = 20 and job = any(select job from emp where deptno = 10);
-- 2. 查询工资比所有的“salesman”都高的雇员的编号，名字和工资
-- 单行子查询
select empno,ename,sal 
from emp
where sal > (select max(sal) from emp where job = 'SALESMAN');
-- 多行子查询
select empno,ename,sal 
from emp
where sal > all(select sal from emp where job = 'SALESMAN');
-- 3. 查询工资低于任意一个“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';

相关子查询

-- 1. 查询最高工资的员工（不相关子查询）
Select * from emp where sal = (select max(sal) from emp);

-- 2. 查询本部门最高工资的员工（相关子查询）
-- 方法1：通过不相关子查询
select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
union
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
union
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30);
-- 缺点：语句比较多，具体到底有多少个部门未知

-- 方法2：相关子查询
select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno);

-- 3. 查询工资高于其所在岗位的平均工资的那些员工（相关子查询）
select * from emp e where sal >= (select avg(sal) from emp where job = e.job);

数据库对象

事务

事务（Transaction）是用来维护数据库完整性的，它能够保证一系列的MySQL操作要么全部执行，要么全不执行。
概念：事务（Transaction）指的是一个操作序列，该操作序列中的多个操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位，由DBMS（数据库管理系统）中的事务管理子系统负责事务的处理。
特性：事务处理可以确保除非事务性序列内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的序列，可以简化错误恢复并使应用程序更加可靠。
事务的ACID特性：

1. 原子性（Atomicity）事务中的所有操作可以看做一个原子，事务是应用中不可再分的最小的逻辑执行体。
2. 一致性（Consistency）事务执行的结果必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。
3. 隔离性（Isolation）各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。也就是说：并发执行的事务之间既不能看到对方的中间状态，也不能相互影响。
4. 持久性（Durability）事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库，即使数据库出现故障，提交的数据也应该能够恢复。但如果是由于外部原因导致的数据库故障，如硬盘被损坏，那么之前提交的数据则有可能会丢失。

-- 创建账户表
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语句是一个事务，所以上面的操作执行了两个事务

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;

-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库中的真实数据
-- 事务隔离级别
-- 查看默认的事务隔离级别，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; -- 设置序列化，解决脏读，不可重复读，幻读

事务并发问题

1. 脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
2. 不可重复读：在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
3. 幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
   不可重复读与幻读的区别：
   不可重复读的重点是修改，幻读的重点在于新增或删除
   解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

视图

视图（view）是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表
视图本质上就是：一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句
视图的好处：

1. 简化用户操作：视图可以使用户将注意力集中在所关心地数据上，而不需要关心数据表的结构、与其他表的关联条件以及查询条件等。
2. 对机密数据提供安全保护：有了视图，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据（如，敏感字段“salary”）出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能

-- 创建单表视图
create or replace view myview01 
as 
select empno,ename,job,deptno
from emp
where deptno = 20
with check option;
-- 查看视图：
select * from myview01;

-- 在视图中插入数据
insert into myview01(empno,ename,job,deptno) values(9999,'lili','CLERK',20);
insert into myview01(empno,ename,job,deptno) values(8888,'nana','CLERK',30);
insert into myview01(empno,ename,job,deptno) values(7777,'feifei','CLERK',30); -- > 1369 - CHECK OPTION failed 'mytestdb.myview01'

-- 创建/替换多表视图
create or replace view myview02
as 
select e.empno,e.ename,e.sal,d.deptno,d.dname
from emp e
join dept d
on  e.deptno = d.deptno
where sal > 2000;

Select * from myview02;

-- 创建统计视图
create or replace view myview03
as
select e.deptno, d.dname,avg(sal),min(sal),max(sal),count(1)
from emp e
join dept d
using(deptno)
group by e.deptno;
select * from myview03;

-- 创建基于视图的视图
create or replace view  myview04
as 
select * from myview03 where deptno = 20;
select * from myview04;

存储过程

概念：存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。
优点：

1. 提高执行性能。存储过程执行效率之所以高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。
2. 可减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。
3. 将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了

-- 定义一个没有返回值的存储过程
-- 实现：模糊查询
select * from emp where ename like '%A%'; 

create procedure myprocedure01(name varchar(10))
begin 
	if name is null or name = "" then 
		select * from emp;
	else
		select * from emp where ename like concat('%',name,'%');
	end if;
end;

-- 删除存储过程
drop procedure myprocedure01;

-- 调用存储过程
call myprocedure01(null);
call myprocedure01('A');

-- 定义一个有返回值的存储过程
-- 实现模糊查询
-- 参数前面的in可以省略不写
-- found_rows() mysql中定义的一个函数，作用返回函数查询结果条数
create procedure myprocedure02(in name varchar(10),out num int(3))
begin 
	if name is null or name = "" then 
		select * from emp;
	else
		select * from emp where ename like concat('%',name,'%');
	end if;
	select found_rows() into num;
end;

-- 调用存储过程
call myprocedure02(null,@num);
select @num;

call myprocedure02('R',@aaa);
select @aaa;