1. 概述
1.1 数据库相关概念
名称 | 全称 | 简称 |
---|---|---|
数据库 | 存储数据的仓库,数据是有组织的进行存储 | DataBase(DB) |
数据库管理系统 | 操纵和管理数据库的大型软件 | DataBase Management System(DBMS) |
SQL | 操作关系型数据库的编程语言,定义了一套操作关系型数据库的统一标准 | Structured Query Language(SQL) |
我们可以通过SQL来操作数据库管理系统,然后再通过数据库管理系统来操纵数据库以及数据库中的数据。
主流的关系型数据库管理系统
1.2 数据模型
关系型数据库(RDBMS)
概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库。
特点:
- 使用表存储数据,格式统一,便于维护。
- 使用SQL语言操作,标准统一,使用方便。
数据模型: 一个数据库管理系统可以管理多个数据库,一个数据库也可以管理多张数据表,而基于表进行存储的数据库就称为关系型数据库。
2. SQL
2.1 SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用空格或缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 注释:
- 单行注释:-- 注释内容 或 # 注释内容(MySQL特有)
- 多行注释:/* 注释内容 */
2.2 SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户,控制数据库的访问权限 |
2.3 DDL
2.3.1 数据库操作
查询所有数据库:show databases;
查询当前数据库:select databases();
创建数据库:create database [if not exists] database_name;
删除数据库:drop database [if exists] database_name;
使用数据库:use database_name;
2.3.2 表操作-创建&查询
创建数据表:create table table_name(column_name column_type);
例如我们在 itcast 数据库中创建 tb_user 数据表:
mysql> use itcast;
mysql> create table tb_user (
-> id int,
-> name varchar(50),
-> age int,
-> gender varchar(1)
-> );
查询当前库所有表:show tables;
查询表结构:desc table_name;
查询指定表的建表语句:show create table table_name;
2.3.3 数据类型及案例
MySQL中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期和时间类型。
1. 数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Bytes | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 Bytes | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8388608,8388607) | (0,16777215) | 大整数值 |
INT或INTEGER | 4 Bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT | 8 Bytes | (-9223372036854775808,9223372036854775807) | (0,18446744073709551615) | 极大整数值 |
FLOAT | 4 Bytes | (-3.402823466E+38,-1.175494351E-38),0,(1.175494351E-38,3.402823466351E+38) | 0,(1.175494351E-38,3.402823466E+38) | 单精度浮点数值 |
DOUBLE | 8 Bytes | (-1.7976931348623157E+308,-2.2250738585072014E-308),0,(2.2250738585072014E-308,1.7976931348623157E+308) | 0,(2.2250738585072014E-308,1.7976931348623157E+308) | 双精度浮点数值 |
DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值 |
2. 字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 Bytes | 定长字符串 |
VARCHAR | 0-65535 Bytes | 变长字符串 |
TINYBLOB | 0-255 Bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 Bytes | 短文本字符串 |
BLOB | 0-65535 Bytes | 二进制形式的长文本数据 |
TEXT | 0-65535 Bytes | 长文本数据 |
MEDIUMBLOB | 0-16777215 Bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16777215 Bytes | 中等长度文本数据 |
LONGBLOB | 0-4294967295 Bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4294967295 Bytes | 极大文本数据 |
3. 日期和时间类型
类型 | 大小 | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 Bytes | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 Bytes | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 Bytes | 1901 至 2155 | YYYY | 年份值 |
DATETIME | 8 Bytes | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 Bytes | 1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
案例:
mysql> create table emp (
-> id int,
-> workno varchar(10),
-> name varchar(10),
-> gender char(1),
-> age tinyint unsigned,
-> idcard char(18),
-> entrydate date
-> );
2.3.4 表操作-修改&删除
添加字段:alter table table_name add column_name column_type;
修改数据类型:alter table table_name modify column_name new_column_type;
修改字段名和字段类型:alter table table_name change old_column_name new_column_name column_type;
删除字段:alter table table_name drop column_name;
修改表名:alter table table_name rename to new_table_name;
删除表:drop table [if exists] table_name;
删除指定表,并重新创建该表:truncate table table_name;
2.4 DML
2.4.1 插入
给指定字段添加数据:insert into table_name (column_name_1,column_name_2,...) values (value_1,value_2,...);
给全部字段添加数据:insert into table_name values (value_1,value_2,...);
批量添加数据:
insert into table_name (column_name_1,column_name_2,...) values (value_1,value_2,...),(value_1,value_2,...),...;
insert into table_name values (value_1,value_2,...),(value_1,value_2,...),...;
2.4.2 更新和删除
修改数据:update table_name set column_name_1 = value_1,column_name_2 = value_2,... [where condition];
删除数据:delete from table_name [where condition];
2.5 DQL
编写顺序:
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
2.5.1 基础查询
查询多个字段:
select column_name_1,column_name_2,column_name_3,... from table_name;
select * from table_name;
设置别名:select column_name [[as] alias] from table_name;
去除重复记录:select distinct column_name from table_name;
2.5.2 条件查询
1. 语法:select column_name from table_name where condition;
2. 条件:
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between…and… | 在某个范围之内(含最小、最大值) |
in(…) | 在 in 之后的列表中的值,多选一 |
like 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
is null | 是NULL |
逻辑运算符 | 功能 |
---|---|
and 或 && | 并且(多个条件同时成立) |
or 或 || | 或者(多个条件任意一个成立) |
not 或 ! | 非,不是 |
2.5.3 聚合函数
1. 介绍: 将一列数据作为一个整体,进行纵向计算。
2. 常见聚合函数:
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
3. 语法:select 聚合函数(column_name) from table_name;
4. 注意: null值不参与所有聚合函数的运算。
2.5.4 分组查询
1. 语法:select column_name from table_name [where condition] group by 分组字段名 [having 分组后过滤条件];
2. where与having区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
3. 注意
- 执行顺序:where -> 聚合函数 -> having。
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
2.5.5 排序查询
1. 语法: select column_name from table_name order by 字段1 排序方式1, 字段2 排序方式2;
2. 排序方式
- ASC:升序(默认值)
- DESC:降序
3. 注意: 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
2.5.6 分页查询
1. 语法: select column_name from table_name limit 起始索引,查询记录数;
2. 注意:
- 起始索引从 0 开始,起始索引 = (查询页码 - 1) * 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
2.5.7 案例练习
# 1. 查询年龄为20,21,22,23岁的女性员工信息。
select * from emp where age in (20,21,22,23) and gender = '女';
# 2. 查询性别为男,并且年龄在20-40岁以内的姓名为三个字的员工。
select * from emp where gender = '男' and (age between 20 and 40) and name like '___';
# 3. 统计员工表中,年龄小于60岁的,男性和女性员工的人数。
select gender, count(*) from emp where age < 60 group by gender;
# 4. 查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序。
select name, age from emp where age <= 35 order by age asc, entrydate desc;
# 5. 查询性别为男,并且年龄在20-40岁以内的前5个员工信息,并对查询结果按年龄进行升序排序,年龄相同按入职时间升序排序。
select * from emp where gender = '男' and (age between 20 and 40) order by age asc, entrydate asc limit 5;
2.5.8 执行顺序
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
select
字段列表
order by
排序字段列表
limit
分页参数
2.6 DCL
2.6.1 用户管理
查询用户:use mysql; select * from user;
创建用户:create user '用户名'@'主机名' identified by '密码';
修改用户密码:alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
删除用户:drop user '用户名'@'主机名';
# 1. 创建用户 itcast,只能够在当前主机localhost访问,密码为 123456
create user 'itcast'@'localhost' identified by '123456';
# 2. 创建用户 heima,可以在任意主机访问该数据库,密码为 123456
create user 'heima'@'%' identified by '123456';
# 3. 修改用户 heima 的访问密码为 1234
alter user 'heima'@'%' identified with mysql_native_password by '1234';
# 4. 删除 'itcast'@'localhost' 用户
drop user 'itcast'@'localhost';
注意:
- 主机名可以使用%通配。
- 这类SQL开发人员操作的比较少,主要是DBA(Database Administrator 数据库管理员)使用。
2.6.2 权限控制
MySQL中定义了很多种权限,但是常用的就以下几种:
权限 | 说明 |
---|---|
all,all privileges | 所有权限 |
select | 查询数据 |
insert | 插入数据 |
update | 修改数据 |
delete | 删除数据 |
alter | 修改表 |
drop | 删除数据库/表/视图 |
create | 创建数据库/表 |
查询权限:show grants for '用户名'@'主机名';
授予权限:grant 权限列表 on database_name.table_name to '用户名'@'主机名';
撤销权限:revoke 权限列表 on database_name.table_name from '用户名'@'主机名';
注意:
- 多个权限之间,使用逗号分隔。
- 授权时,数据库名和表名可以使用 * 进行通配,代表所有。
3. 函数
函数: 是指一段可以直接被另一段程序调用的程序或代码。
3.1 字符串函数
MySQL中内置了很多字符串函数,常用的几个如下:
函数 | 功能 |
---|---|
concat( s 1 , s 2 , . . . , s n s_1,s_2,...,s_n s1,s2,...,sn) | 字符串拼接,将 s 1 , s 2 , . . . , s n s_1,s_2,...,s_n s1,s2,...,sn拼接成一个字符串 |
lower(str) | 将字符串str全部转为小写 |
upper(str) | 将字符串str全部转为大写 |
lpad(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
rpad(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
trim(str) | 去掉字符串头部和尾部的空格 |
substring(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
展示:
-- concat
mysql> select concat('Hello',' MySQL');
+--------------------------+
| concat('Hello',' MySQL') |
+--------------------------+
| Hello MySQL |
+--------------------------+
-- lower
mysql> select lower('Hello');
+----------------+
| lower('Hello') |
+----------------+
| hello |
+----------------+
-- upper
mysql> select upper('Hello');
+----------------+
| upper('Hello') |
+----------------+
| HELLO |
+----------------+
-- lpad
mysql> select lpad('01',5,'_');
+------------------+
| lpad('01',5,'_') |
+------------------+
| ___01 |
+------------------+
-- rpad
mysql> select rpad('01',5,'_');
+------------------+
| rpad('01',5,'_') |
+------------------+
| 01___ |
+------------------+
-- trim
mysql> select trim(' Hello MySQL ');
+-------------------------+
| trim(' Hello MySQL ') |
+-------------------------+
| Hello MySQL |
+-------------------------+
-- substring
mysql> select substring('Hello MySQL',1,5);
+------------------------------+
| substring('Hello MySQL',1,5) |
+------------------------------+
| Hello |
+------------------------------+
案例练习:
-- 由于业务需求变更,企业员工的工号,统一为5位数,目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00001。
update emp set workno = lpad(workno, 5, '0');
3.2 数值函数
常见的数值函数如下:
函数 | 功能 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x,y) | 返回x/y的模 |
rand() | 返回0~1内的随机数 |
round(x,y) | 求参数x的四舍五入的值,保留y位小数 |
展示:
-- ceil
mysql> select ceil(1.1);
+-----------+
| ceil(1.1) |
+-----------+
| 2 |
+-----------+
-- floor
mysql> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
| 1 |
+------------+
-- mod
mysql> select mod(7,4);
+----------+
| mod(7,4) |
+----------+
| 3 |
+----------+
-- rand
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.47892660319705177 |
+---------------------+
-- round
mysql> select round(2.345,2);
+----------------+
| round(2.345,2) |
+----------------+
| 2.35 |
+----------------+
案例练习:
-- 通过数据库的函数,生成一个六位数的随机验证码。
mysql> select lpad(round(rand() * 1000000), 6, '0');
+---------------------------------------+
| lpad(round(rand() * 1000000), 6, '0') |
+---------------------------------------+
| 837806 |
+---------------------------------------+
3.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之间的天数 |
展示:
-- curdate()
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2023-04-23 |
+------------+
-- curtime()
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 22:07:44 |
+-----------+
-- now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-04-23 22:08:02 |
+---------------------+
-- year
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2023 |
+-------------+
-- month
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 4 |
+--------------+
-- day
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 23 |
+------------+
-- date_add
mysql> select date_add(now(),interval 70 day);
+---------------------------------+
| date_add(now(),interval 70 day) |
+---------------------------------+
| 2023-07-02 22:10:24 |
+---------------------------------+
-- datediff
mysql> select datediff('2023-7-10',now());
+-----------------------------+
| datediff('2023-7-10',now()) |
+-----------------------------+
| 78 |
+-----------------------------+
案例练习:
-- 查询所有员工的入职天数,并根据入职天数倒序排序。
select name, datediff(curdate(), entrydate) entrydays from emp order by entrydays desc;
3.4 流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率。
函数 | 功能 |
---|---|
if(value,t,f) | 如果value为true,则返回t,否则返回f |
ifnull(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
case when [val1] then [res1] … else [default] end | 如果value1为true,返回res1,…,否则返回default默认值 |
case [expr] when [val1] then [res1] … else [default] end | 如果expr的值等于val1,返回res1,…,否则返回default默认值 |
-- if
mysql> select if(false,'ok','error');
+------------------------+
| if(false,'ok','error') |
+------------------------+
| error |
+------------------------+
-- ifnull
mysql> select ifnull(null,'ok');
+-------------------+
| ifnull(null,'ok') |
+-------------------+
| ok |
+-------------------+
-- case when then else end
-- 需求:查询emp表的员工姓名和工作地址(北京/上海 --> 一线城市,其他 --> 二线城市)
select
name,
case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地址'
from emp;
案例练习:
-- 统计班级各个学员的成绩,展示的规则如下:
-- >= 85,展示优秀
-- >= 60,展示及格
-- 否则,展示不及格
select
id,
name,
case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' '数学',
case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' '英语',
case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' '语文'
from score;
4. 约束
4.1 概述
1. 概念: 约束是作用于表中字段上的规则,用于限制存储在表中的数据。
2. 目的: 保证数据库中数据的正确、有效性和完整性。
3. 分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | not null |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | default |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | check |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | foreign key |
4. 注意: 约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
4.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 '用户表';
4.3 外键约束
概念:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
添加外键:
create table table_name(column_name column_type, ..., [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名);
alter table table_name add constraint 外键名称 foreign key(外键字段名) references 主表(主表字段名);
删除外键:alter table table_name drop foreign key 外键名称;
4.4 外键删除更新行为
行为 | 说明 |
---|---|
no action | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与restrict一致) |
restrict | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与no action一致) |
cascade | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
set null | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null)。 |
set default | 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)。 |
语法:alter table table_name add constraint 外键名称 foreign key(外键字段名) references 主表(主表字段名) on update XXX on delete XXX;
5. 多表查询
5.1 多表关系介绍
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 案例:部门与员工的关系
- 关系:一个部门对应多个员工,一个员工对应一个部门
- 实现:在多的一方建立外键,指向一的一方的主键
- 多对多
- 案例:学生与课程的关系
- 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
- 一对一
- 案例:用户与用户详情的关系
- 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
5.2 多表查询概述
概述: 指从多张表中查询数据。
笛卡尔积: 笛卡尔乘积是指在数学中,集合A和集合B的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
多表查询分类:
- 连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接:
- 左外连接:查询左表所有数据,以及两张表交集部分数据
- 右外连接:查询右表所有数据,以及两张表交集部分数据
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
- 子查询
5.3 内连接
隐式内连接:select column_name from table_name1, table_name2 where condition;
显式内连接:select column_name from table_name1 [inner] join table_name2 on condition;
5.4 外连接
左外连接:select column_name from table_name1 left [outer] join table_name2 on condition;
右外连接:select column_name from table_name1 right [outer] join table_name2 on condition;
5.5 自连接
查询语法:
select column_name from table_name 别名A, table_name 别名B where condition;
select column_name from table_name 别名A [left/right] join table_name 别名B on condition;
注意:自连接查询,可以是内连接查询,也可以是外连接查询。
5.6 联合查询union
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
select column_name from table_nameA ...
union [all]
select column_name from table_nameB ...;
注意:
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
5.7 子查询介绍
概念:SQL语句中嵌套着SELECT语句,称为嵌套查询,又称子查询。
语法:select * from table_name1 where column_name1 = (select column_name2 from table_name2);
注意:子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个。
- 根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
- 根据子查询位置,分为:where之后、from之后、select之后。
5.8 标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符:= <> > >= < <=
5.9 列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN、NOT IN、ANY、SOME、ALL
操作符 | 描述 |
---|---|
in | 在指定的集合范围之内,多选一 |
not in | 不在指定的集合范围之内 |
any | 子查询返回列表中,有任意一个满足即可 |
some | 与any等同,使用some的地方都可以使用any |
all | 子查询返回列表的所有值都必须满足 |
5.10 行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:=、<>、IN、NOT IN
5.11 表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
5.12 多表查询案例
-- 1. 查询员工的姓名、年龄、职位、部门信息(隐式内连接)
select e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;
-- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)
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;
-- 3. 查询拥有员工的部门ID、部门名称
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;
-- 4. 查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
-- 5. 查询所有员工的工资等级
select e.*, s.grade from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
-- 6. 查询‘研发部’所有员工的信息及工资等级
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. 查询‘研发部’员工的平均工资
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';
-- 8. 查询工资比‘灭绝’高的员工信息
select * from emp where salary > ( select salary from emp where name = '灭绝' );
-- 9. 查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );
-- 10. 查询低于本部门平均工资的员工信息
select * 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. 查询所有学生的选课情况,展示出学生名称,学号,课程名称
select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid and c.id = sc.courseid;
6. 事务
6.1 事务简介
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
注意: 默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
6.2 事务操作
查看事务提交方式:select @@autocommit;
设置事务提交方式:set @@autocommit = 0/1;(0为手动提交,1为自动提交)
开启事务:start transaction 或 begin;
提交事务:commit;
回滚事务:rollback;
6.3 事务四大特性(ACID)
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
6.4 并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另一个事务还没有提交的数据。 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。 |
6.5 事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | T | T | T |
Read committed | F | T | T |
Repeatable Read(默认) | F | F | T |
Serializable | F | F | F |
查看事务隔离级别:select @@transaction_isolation;
设置事务隔离级别:set [session|global] transaction isolation level { read uncommitted | read committed | repeatable read | serializable };
7. 基础篇总结
恭喜您,经过概述、SQL、函数、约束、多表查询、事务的练习后,您已成功超越80%的MySQL玩家啦!!!
接下来请前往 进阶篇 开始新的学习吧!