MySQL 基础篇

MySQL 基础篇

---

---

一、MySQL 概述

1.数据库相关概念

2.主流的关系型数据库管理系统

3.关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
特点：
1.使用表存储数据，格式统一，便于维护
2.使用SQL语言操作，标准统一，使用方便

4.数据模型

在一个数据库管理系统可以创建多个数据库，在一个数据库可以创建多个表。

二.SQL

1.SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。
2.SQL语句可以使用空格/缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4.注释：
·单行注释：- - 注释内容或 # 注释内容（MySQL特有）
·多行注释：/注释内容/

2.SQL分类

3.DDL

DDL-数据库操作

提示：方括号里面的内容都是可选的，必要时可写，非必要可不写。

• 创建操作演示：
  
  提示：IF NOT EXISTS如果存在则创建，不存在则不创建，因此不会像上图一样报错。



提示：一般字符集要默认为utf8mb4
• 删除操作演示
  执行“drop database test”：
  
  若依然执行删除数据库test“drop database test”的指令，则会报错：
  
  若不想报错，则加上if exists，此时不会报错，无论test存在与否：
  
• 使用操作演示：
  在此切换到itcast数据库来使用：
  
• 查询数据库操作演示：
  
  提示：要注意在最后面记得加上（）。
提示：切换数据库操作：先use，在select

DDL-表操作-查询

DDL-表操作-创建

提示：[...]为可选参数，最后一个字段后面没有逗号

• 创建表演示：
  
  提示：创建表前要注意是否在想要创建表的数据库中，保险做法每次创建前用use语句选择数据库。
  
• 查询表结构
  
• 展示创建表的SQL语句：
  
  提示：展示的语句最后一句有不是我们编写的“ENGINE=InnoDB”此为存储引擎；“DEFAULT CHARSET=utf8mb4”此为默认的字符集，“COLLATE=utf8mb4_0900_ai_ci”也是默认的。

DDL-表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

• 数值类型
  
  注意：age TINYINT UNSIGNED代表在表中age属性是无符号范围的TINYINT类型。 score double（4,1）代表一共四位数，有一位小数，例如100.0。
• 字符串类型
  注意：char（10）、varchar（10）数字代表其最大字符数是多长，超出十个字符将会报错。在char（10）就算只有一个字符也会占用十个字符的空间，其他九个字符用空格代表；varchar（10）一个字符就占用一个字符的空间，十个字符，就占用十个字符的空间，他会根据你所存储的字符计算字符占用的空间。这就是变长字符串。两者性能是char比较好，因为varchar在存储过程中需要计算存储的字符所占用的空间。
  
• 字符串类型
  

案例

DDL-表操作-修改

案例：为emp表增加一个新的字段”昵称”为nickname,类型为varchar(20)

案例：将emp表的nickname字段修改为username,类型为varchar(30)

案例：将emp表的字段username删除

案例：将emp表的表名修改为employee

DDL-表操作-删除

案列
drop table if exists tb_user;

注意：if exists可写可不写，写了就不用管表存不存在，都不会报错。
truncate table employee;

注意：删除指定表，并重新创建该表，目的是为了删除数据。但一般用drop比较多。
注意：在删除表时，表中的全部数据也会被删除。

DDL-总结

4.DML

DML-介绍

DML英文全称是Data Manipulation Language(数据操作语宫)，用来对数据库中表的数据记录进行增删改操作。
√添加数据（INSERT）
√修改数据（UPDATE)
√删除数据（DELETE)

DML-添加数据

注意：
·插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
·字符串和日期型数据应该包含在引号中。
·插入的数据大小，应该在字段的规定范围内。
·插入多条数据用“，”隔开。

DML-修改数据

注意：
·修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
·插入多条数据用“，”隔开。

DML-删除数据

注意：
·DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
·DELETE语句不能删除某一个字段的值（可以使用UPDATE）。
·插入多条数据用“，”隔开。

DML-总结

5.DQL

DQL-介绍

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。
查询关键字：SELECT

DQL-语法

DQL-基本查询

例1

例2

例3


注意：起别名，as 可以省略
例4

DQL-条件查询（WHERE）

案例 -条件查询
-1.查询年龄等于88的员工

-2.查询年龄小于28的员工信息

-3.查询年龄小于等于20的员工信息

-4.查询没有身份证号的员工信息

-5.查询有身份证号的员工信息

-6.查询年龄不等于88的员工信息

不等于用！=或<>
-7.查询年龄在15岁（包含）到28岁（包含）之同的员工

BETWEEN AND既包含最小值也包含最大值
-8.查面性别为女 且年龄小于25岁的员工信息

-9.查询年龄等于18或20或40的员工信息

-10.查询姓名为两个字的员工信息

-11.查询身份证号最后一位为x的员工信息

DQL-聚合函数（count、max、min、avg、sum）

注意：NULL值是不参与聚合函数的计算。

案例-聚合函数
-1.统计该企业员工数量

-2.统计该企业员工的平均年龄

-3.统计该企业员工的最大年龄

-4.统计该企业员工的最小年龄

-5.统计西安地区员工年龄之和

DQL-分组查询（GROUP BY）

注意： ·分组通常配合聚合来操作。
·执行顺序：where>聚合函数>having。
·分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

案例-分组香询
-1.根据性别分组，统计男性员工和女性员工的数量

-2.根据性别分组，统计男性员工和女性员工的平均年龄

-3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
第一步：

第二步：

第三步：起别名

DQL-排序查询（ORDER BY）

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

案例-排序查询
-1.根据年龄对公司的员工进行升序排序


注意：asc是可以省略的
-2.根据入职时间，对员工进行降序排序

-3.根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序

DQL-分页查询（LIMIT）

注意
·起始索引从0开始，！！起始索引=（查询页码-1）·每页显示记录数！！。
·分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
·如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

案例–分页查面
-1.查询第1页员工数据，每页展示10条记录


注意：若查询从第一也开始，那么0可以省略不写。
-2.查询第2页员工数据，每页展示10条记录

DQL-案例练习

1.查询年龄为20，21，22，23岁的员工信息。

2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工。

为了看起来更加清晰，可以在年龄那一块儿加上小括号，执行效果是一样的。

3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。

4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

5.查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
错误示范：Order by必须在Limit之前，Limit必定是在最后的。

正确：

DQL-执行顺序

通过起别名的方式来验证。

SELEC是在WHERE之后的，所以WHERE使用SELECT的别名会报错。

DQL-总结

6.DCL

DCL-介绍

DCL英文全称是Data Control Language(数据控制语言），用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户

• 创建用户
  
  原用户表：
  刷新后：
  
  目前只创建了这个用户，并没有给这个用户给予任何权限：
  
  %代表任意主机
  
  

• 修改密码
  
  

• 删除用户
  
  

注意：
·主机名可以使用%通配。
·这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。

DCL-权限控制

MySOL中定义了很多种权限，但是常用的就以下几种：

• 查询权限
  

• 授予权限
  目前heima就能访问到的数据库：
  
  进行授权后所能访问到的数据库：
  
  
  

• 撤销权限
  
  
  

注意：
·多个权限之间，使用逗号分隔。
·授权时，数据库名和表名可以使用*进行通配，代表所有。*.*代表所有数据库的所有表的所有权限。。

DCL-总结

三.函数

函数是指一段可以直接被另一段程序调用的程序或代码。

1.字符串函数

CONCAT:拼接

LOWER(str):小写

UPPER(str):大写

LPAD(str,n,pad):左填充

RPAD(str,n,pad):右填充

TRUM(str):去除头部和尾部的空格，中间的空格不去除

SUBSTRING(str,start,len):截取

索引值从1开始

案例

执行语句前：

执行语句后：

2.数值函数

CEIL(x)：向上取整

注意：只要小数部分不是0则一直向上取整。

FLOOR(x)：向下取整

MOD(x,y)：返回x/y的模（余数）

RAND()：返回0~1内的随机数

ROUND(x,y)：求参数x的四舍五入的值，保留y位小数

案例

但是还有点不对，如下可能只有5位，0~1存在如0.019255，因此要达到六位数补0即可。

修改：

前面补0的情况：

3.日期函数

CURDATE()：返回当前日期

CURTIME()：返回当前时间

NOW()：返回当前日期和时间

YEAR(date)：获取指定date的年份

MONTH(date)：获取指定date的月份

DAY(date)：获取指定date的日期

DATE_ADD(date, INTERVAL expr type)：返回一个日期/时间值加上一个时间间隔expr后的时间值

DATEDIFF(date1,date2)：返回起始时间date1和结束时间date2之间的天数

注意：就学时间天数的时候是：第一个时间减去第二个时间。

案例

首先查询入职天数：

接下来再对其进行排序

4.流程函数

IF(value , t , f)

一般来说value为true还是false是条件表达式来表示。

IFNULL(value1 , value2)

注意：这里的NULL指的是是否为NULL值

返回ok，因为ok是一个字符串，不为空。

传递的是一个空串，返回的也是一个空串，这也不为空。

因为传递的是一个NULL值，所以会返回默认值。

CASE WHEN [val1] THEN [res1] … ELSE[ default] END

需求： 查询emp表的员工姓名和工作地址（北京/上海—> 一线城市，其他—> 二线城市）

• 首先查询emp表的员工姓名和工作地址
  
• 实现北京/上海—> 一线城市，其他—> 二线城市
  

CASE[expr ] WHEN [val1 ] THEN [res1] … ELSE[default] END

案例

原始表：


执行：

5.小结

四.约束

1.概述

（1）概念：

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

（2）目的：

保证数据库中数据的正确、有效性和完整性。

（3）分类：

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

2.演示

注意：多个约束之间用空格分开即可。

create table user(
id int primary key auto_increment comment'主键',
name varchar(10) not null unique comment'姓名',
age int check（age>0 && age<=120）comment'年龄',
status char(1)default'1' comment'状态',
gender char(1) comment'性别'
）comment'用户表';

• 验证id自增

insert into user(name,age,status,gender) 
values （'Tom1',19,'1','男'),（'Tom2',25,'8','男'）;

insert into user(name,age,status,gender)values （'Tom3',19,'1','男');

• name的非空和唯一

insert into user(name,age,status,gender) values (null,19,'1','男')
insert into user(name,age,status,gender) values（'Tom3',19,'1','男')

注意：此时会报错，因为name不能为NULL。

这里是唯一约束起了作用，name为Tom3不唯一。

• age
  年龄正确插入：

insert into user(name,age,status,gender） values （'Tom4',80,'1','男'）

此时Tom4的自增id为5，是因为前面插入的错误数据已经向数据库申请了一个4，所以此条正确插入数据id自增就为5。

年龄错误插入：

insert into user(name,age,status,gender)values（'Tom5',-1,'1','男');

insert into user(name,age,status,gender)values（'Tom5',121,'1','男');

• status默认值
  我们传递了，它将会使用我们传递的值，如果没有传递值，那么就会返回它的默认值。

insert into user(name,age,gender)values（'Tom5',120,'男');

3.外键约束

概念

注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

• 准备数据：

create table dept(
id int auto_increment comment 'ID'primaryjkey,
name varchar(58)not nul1 comment'部门名称'
)comment‘部门表”；

INSERT INTO dept（id,name）
VALUES（1,'研发部'）,（2,'市场部'）,（3,'财务部'）,（4,'销售部'）,（5,'总经办'）;

create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(58)not null comment'姓名',
age int comment'年龄',
job varchar(20)comment'职位',
salary int comment'薪资',
entrydate date comment'入职时间',
managerid int comment'直属领导ID',
dept_id int comment'部门ID'
)comment'员工表'；

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) 
VALUES(1,'金庸',66,'总裁', 20000, '2000-01-01', nu11,5）, 
      (2,'张无忌',20,'项目经理', 12500,'2005-12-05',1,1）, 
      (3,'杨道',33,'开发', 8400, '2000-11-03',2,1）, 
      (4,'韦一笑',48,'开发', 11000, '2002-02-05',2,1）, 
      (5,'常遇春',43, '开发',10500,'2004-09-07', 3,1）, 
      (6,'小昭',19,'程序员鼓励师', 6600,'2004-10-12', 2,1);

语法

alter table emp add constraint fk_emp_dept_id 
foreign key (dept_id) references dept(id);

注意：foreign key +先子后父，先非主后主

alter table emp drop foreign key fk_emp_dept_id;

4.外键删除更新行为

注意：前两个为默认行为。

• cascade
  
  更新：
  
  
  删除：
  
  
• set null
  删除：
  
  

5.小结

五.多表查询

1.多表关系介绍

概述

一对多（多对一)

多对多

create table student(
id int auto_increment primary key comment'主键ID',
name varchar(10) comment'姓名',
no varchar(10)comment'学号'
）comment'学生表'；

insert into student
values (nul1,'黛绮丝','2000100101'),
       (nul1,'谢逊','2000100102'),
       (nul1,'般天正','2000100103'),
       (nul1,'韦一笑','2000100104');

create table course(
id int auto_increment primary key comment'主键ID',
name varchar(10) comment'课程名称'
）comment‘课程表'；

insert into course
values (null,'Java'), 
       (null,'PHP'), 
       (null,'MySQL'), 
       (null,'Hadoop');

create table student_course(
id int auto_increment comment'主键'primary key,
studentid int not null comment'学生ID',
courseid int not null comment'课程ID',
constraint fk_courseid foreign key (courseid) references course (id),
constraint fk_studentid foreign key (studentid) references student (id)
)comment'学生课程中间表';

insert into student_course 
values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

演示

一对一

create table tb_user(
id int auto_increment primary key comment'主键ID',
name varchar(10)comment'姓名',
age int comment'年龄',
gender char(1)comment'1:男,2:女',
phone char(11)comment'手机号'
）comment'用户基本信息表';

create table tp_user_edu(
id int auto_increment primary key comment'主键ID',
degree varchar(20)comment'学历',
major varchar(50)comment'专业',
primaryschool varchar(50)comment'小学',
middleschool varchar(50)comment'中学',
university varchar(50)comment'大学',
userid int unique comment'用户ID',
constraint fk_userid foreign key (userid) references tb_user(id)
）comment'用户教育信息';

insert into tb_user(id, name, age, gender, phone) 
values(nu11，'黄渤',45，'1','18800001111'),
      (nu11,'冰冰',35,'2','18800082222'),
      (nu11,'码云',55，'1','18800088888'),
      (nu11，'李彦宏',58,'1','18888889999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)
values(nul1,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
      (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
      (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
      (nulL,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);I

2.多表查询概述

概述：指从多张表中查询数据

演示

笛卡尔积

共102条记录（17*6）

消除无效笛卡尔积

共16条记录

多表查询分类

连接查询

内连接

–内连接演示
–1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
–表结构：emp，dept
–连接条件:emp.dept_id=dept.id

select emp.name , dept.name 
from emp , dept
where emp.dept_id = dept.id ;

起别名（效果一样）：
select e.name , d.name
from emp e, dept d
where e.dept_id = d.id;

如果已经为表限定别名，那么就不能用其表名来限定条件。

–2.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）–INNERJ0IN…ON…
—表结构：emp，dept
–连接条件：emp.dept_id =dept.id

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

inner 可以省略。
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

外连接

–外连接演示
–1.查询emp表的所有数据，和对应的部门信息(左外连接)
–表结构：emp,dept
–连接条件：emp.dept_id=dept.id

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

–2.查询dept表的所有数据，和对应的员工信息（右外连接）

select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

自连接

---

–自连接
–1.查询员工及其所属领导的名字
–表结构：emp

select a.name , b.name 
from emp a , emp b 
where a.managerid = b.id;

–2.查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来
–表结构：emp a，emp b

select a.name'员工'，b.name'领导'
from emp a left join emp b on a.managerid =b.id;

联合查询union, union all

-- union all , union
--1.将薪资低于5080的员工，和年龄大于58岁的员工全部查询出来。
select * from emp where salary < 5000;

select * from emp where age > 50;

union all:直接对查询结果进行合并。

select * from emp where salary < 5000
union all
select * from emp where age > 50; 

union:对合并后的结果去重。

select * from emp where salary < 5000
union
select * from emp where age > 50; 

注意

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
• union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。
  

子查询介绍

标量子查询（子查询结果为单个值）

• 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
• 常用的操作符：=、<>、>、>=、<、<=
  

--标量子查询
--1，查询“销售部”的所有员工信息
--a.查询“销售部”部门ID
select id 
from dept 
where name ='销售部';

--b.根据销售部部门ID，查询员工信息
select * 
from emp 
where dept_id = 4;

--c.=合并两条查询
select * from emp 
where dept_id =
(select id 
from dept 
where name ='销售部');

a. b.

c.

-2.查询在“方东白”入职之后的员工信息
--a.查询方东白的入职日期
select entrydate 
from emp 
where name ='方东白';

--b.查询指定入职日期之后入职的员工信息
select * 
from emp 
where entrydate > '2009-02-12';

--c.合并两条查询
select * 
from emp 
where entrydate > 
(select entrydate 
from emp 
where name ='方东白');

a.

b.

c.

列子查询（子查询结果为一列）

• 子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
• 常用的操作符：IN、NOT IN、ANY、SOME、ALL
  

--列子查询
--1.查询“销售部”和“市场部”的所有员工信息
--a.查询“销售部”和“市场部”的部门ID
select id from dept where name ='销售部'or name ='市场部门';

--b.根据部门ID，查询员工信息
select * from emp where dept_id in (2,4);

--c.合并
select * from emp where dept_id in
(select id from dept where name ='销售部'or name ='市场部门');

a.
> b.

c.

--2.查询比财务部所有人工资都高的员工信息
--a、查询所有财务部人员工资
seLect id from dept where name ='财务部'

select salary from emp where dept_id = 4;

select salary from emp where dept_id = 
(seLect id from dept where name ='财务部');

--b.比财务部所有人工资都高的员工信息
select *from emp where salary >all 
(select salary from emp where dept_id =
(select id from dept where name =‘财务部'));

a.



b.

--3.查询比研发部其中任意一人工资高的员工信息
--a.查询研发部所有人工资
select salary from emp where dept_id =
(select id from dept where name ='研发部');

--b.比研发部其中任意一人工资高的员工信息
select * from emp where salary > any
(select salary from emp where dept_id=
(select id from dept where name ='研发部'));

--any也可以换为some，效果一样
select * from emp where salary > some
(select salary from emp where dept_id=
(select id from dept where name ='研发部'));

a.

b.

行子查询（子查询结果为一行)

• 子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
• 常用的操作符：=、<>、IN、NOTIN
  

--行子查询
--1.查询与“张无忌”的薪资及直属领导相同的员工信息；
--a.查询“张无忌”的薪资及直属领导
select salary,managerid from emp where name ='张无忌';

--b.查询与“张无忌”的薪资及直属领导相同的员工信息；
select * from emp where salary I= 12500 and managerid = 1;
--另一种写法
select * from emp where (salary,managerid) = (12500,1);

--c.合并
select * from emp where(salary,managerid)=
(select salary,managerid from emp where name = '张无忌');

a.

b.

c.

表子查询（子查询结果为多行多列）

• 子查询返回的结果是多行多列，这种子查询称为表子查询。
• 常用的操作符：IN

--表子查询
--1.查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
--a.查询“鹿杖客”，“宋远桥”的职位和薪资
select job,salary from emp where name ='鹿杖客'or name ='宋远桥';

--b.查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
select *from emp where (job,salary) in
(select job,salary from emp where name='鹿杖客'or name='宋远桥');

a.

b.

--2.查询入职日期是“2886-81-81”之后的员工信息，及其部门信息
--a.入职日期是“2006-81-81”之后的员工信息
select * from emp where entrydate > '2006-01-01';

--b.查询这部分员工，对应的刻门信息;
select e.*, d.* 
from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

a.
b.

4.练习1

create table salgrade(
grade int,
losal int,
hisal int
）comment‘薪资等级表'；

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

①查询员工的姓名、年龄、职位、部门信息。

--1、查询员工的姓名、年龄、职位、部门信息（隐式内连接）
--表:emp，dept
--连接条件：emp.dept_id =dept.id
select e.name , e.age , e.job , d.name 
from emp e , dept d 
where e.dept_id = d.id;

②查询年龄小于30岁的员工姓名、年龄、职位、部门信息。

--2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
--表：emp，dept
-- 连接条件：emp.dept_id = dept.id
select e.name , e.age , e.job , d.name 
from emp e inner join dept d on e.dept_id = d.id 
where e.age < 30;

③查询拥有员工的部门ID、部门名称。

--3.查询拥有员工的部门ID、部门名称
--表:emp，dept
--连接条件：emp.dept_id = dept.id
select distinct d.id , d.name 
from emp e , dept d 
where e.dept_id = d.id;

④查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。

--4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来
--表：emp，dept
--连按条件：emp.dept_id = dept.id
--外连接
select e.*, d.name 
from emp e left join dept d on e.dept_id = d.id 
where e.age > 40 ;

⑤查询所有员工的工资等级。

--5.查询所有员工的工资等级
-- 表:emp ,salgrade
--连接条件:emp.salary >= salgrade.losal and emp.salary<=salgrade.hisal 
--或 e.salary between s.losal and s.hisal;
select e.* , s.grade 
from emp e , salgrade s 
where e.salary >= s.losal and e.salary <= s.hisal;

--另解：
select e.*, s.grade , s.losal, s.hisal 
from emp e , salgrade s 
where e.salary between s.losal and s.hisal;

--检验范围是否正确：
select e.* , s.grade , s.losal, s.hisal 
from emp e , salgrade s 
where e.salary >= s.losal and e.salary <= s.hisal;

⑥查询“研发部”所有员工的信息及工资等级。

-6.查询“研发部”所有员工的信息及 工资等级
--表：emp,salgrade，dept
--连接条件:emp.salary between salgrade.losal and salgrade.hisal,emp.dept_id=dept.id
--查询条件：dept.name='研发部'
select e.*, s.grade 
from emp e , dept d , salgrade s 
where e.dept_id = d.id and ( e.salary between s.losal and s.hisal) and d.name ='研发部';

⑦查询“研发部”员工的平均工资。

--7.查询“研发部”员工的平均工资
--表:emp，dept
--连接条件： emp.dept_id = dept.id
--查询条件：dept.name='研发部'
select avg(e.salary)
from emp e,dept d 
where e.dept_id=d.id and d.name ='研发部';

⑧查询工资比“灭绝”高的员工信息。

--8.查询工资比“灭绝”高的员工信息。
--a.查询“灭绝”的薪资
select salary from emp where name ='灭绝';
--b.查询比她工资高的员工数据
select * from emp where salary > 8500;
--c.合并
select * 
from emp 
where salary >
(select salary from emp where name ='灭绝');

⑨查询比平均薪资高的员工信息。

--9.查询比平均薪资高的员工信息
--a，查询员工的平均薪资
select avg(salary) from emp;
b.查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );

⑩查询低于本部门平均工资的员工信息。

--10.查询低于本部门平均工资的员工信息
--a.查询指定部门平均薪资
select avg(el.salary) from emp el where el.dept_id = 1;
select avg(e1.salary) from emp el where e1.dept_id = 2;
--b.查询低于本部门平均工资的员工信息
select *,(select avg(e1.salary)from emp e1 where e1.dept_id = e2.dept_id)'平均'
from emp e2 
where e2.salary < 
( select avg(e1.salary) 
  from emp e1 
  where e1.dept_id = e2.dept_id );

⑪查询所有的部门信息，并统计部门的员工人数。

11.查询所有的部门信息，并统计部门的员工人数
select d.id,d.name,(select count(*)from emp e where e.dept_id =d.id)'人数'
from dept d;

⑫查询所有学生的选课情况，展示出学生名称，学号，课程名称

--12.查询所有学生的选课情况，展示出学生名称，学号，课程名称
--表:student,course,student_course
--连接条件：student.id=student_course.studentid,course.id =student_course.courseid
select s.name , s.no , c.name 
from student s , student_course sc , course c 
where s.id = sc.studentid and sc.courseid = c.id ;

5.小结

六.事务

1.简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

2.操作演示

数据准备

--数据准备
create table account(
id intTauto_increment primary key comment'主键ID',
name varchar(10)comment'姓名',
money int comment'余额'
）comment'账户表'；
insert into account(id,name,money)VALUES(null,'张三',2000),(null,'李四',2000);

--恢复数据
update account set money =2000 where name ='张三'or name ='李四';

• 转账操作

--转账操作（张三给李四转账1000）
--1.直询张三账户余额
select*from account where name ='张三';
--2.将张三账户余额-1000
update account set money = money-1000 where name ='张三';
--3.将李四账户余额+1000
update account set money =money + 1000 where name='李四';

• 程序抛出异常

--转账操作（张三给李四转账1888）
--1.查询张三账户余额
select *from account where name ='张三';
--2.将张三账户余额-1008
update account set money = money-1000 where name ='张三';
程房抛出异常。
--3.将李四账户余额+1008
update account set money =money + 1000 where name ='李四'；

事务操作

方式一、手动提交

• 转账正常操作。

select @@autocommit;
set @@autocommit =0;--设置为手动提交


--转账操作（张二给李四转账1000）
--1. 查询张三账户余额
select *from account where name ='张三';
--2.将张三账户余颜-1008
update account set money =money-1000 where name ='张三';
--3.将李四账户余额+1088
update account set money =money +1000 where name ='李四';


--提交事务
commit;

1.将自动提交改为手动提交。


2.选择这三条语句进行执行。

此时我们还没有进行手动提交，所以这时的数据并没有改变。

3.提交事务

--恢复数据
update account set money =2000 where name ='张三'or name ='李四';

commit;

注意：由于是手动提交，要实现恢复数据执行update语句之后，还要执行才能实现真正的恢复数据。

• 程序抛出异常

--转账操作（张二给李四转账1000）
--1. 查询张三账户余额
select *from account where name ='张三';
--2.将张三账户余颜-1008
update account set money =money-1000 where name ='张三';
程序执行报错...
--3.将李四账户余额+1088
update account set money =money +1000 where name ='李四';


--回滚事务
rollback;

1.执行转账操作

2.一旦出现错误，我们不要提交事务，而是要回滚事务。

回滚后数据与原来的相比较没有变化。

方式二、自动提交

• 转账正常操作。

select @@autocommit;
set @@autocommit =1;--设置为自动提交


-方式二
--转账操作（张三给李四转账1000）
start transaction ;
|I
--1.查询张三账户余额
select*from account where name ='张三';
--2.将张三账户余额-1000
update account set money = money -1000 where name ='张三';
程序执行报错...
--3.将李四账户余颜+1880
update account set money =money +1000 where name ='李四';

--提交事务
commit;
--回滚事务
rollback;

1.将手动提交改为自动提交。

2.选择这4条语句进行执行。


3.回滚事务

3.四大特性ACID

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
  

4.并发事务问题

脏读

不可重复读

幻读

5.并发事务演示及隔离级别

Session指的是当前会话的隔离级别。
注意：事务隔离级别越高，数据越安全，但是性能越低。

改为Read uncommitted
先set session transaction isolation level read uncommitted ;再select @@transaction_isolation;

--查看事务隔离级别
select @@transaction_isolation;
--设置事务隔离级别
set session transaction isolation level read uncommitted ;

Read uncommitted：读未提交

性能最高，安全性最差。
脏读问题：

Read committed：读与提交

解决脏读问题：

不可重复读问题：
在一个事物中查询的数据不一致。

Repeatable Read(默认)：可重复读

解决不可重复读问题：
事务1：

事务2：

幻读问题：

Serializable：串行化

级别最高，性能最差。
解决幻读问题：
事务1：

事务2：

6.小结

基础篇小结