一、基本格式
#sql92语法:
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....
#sql99语法:
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....
#两种语法中,除了group by和limit之外,其他位置都可以声明子查询
二、条件运算符
#1. 算术运算符: + - * / div % mod
# 在SQL中,+没有连接的作用,就表示加法运算。此时,会将字符串转换为数值(隐式转换)
SELECT 100 + '1' FROM DUAL;
# 在Java语言中,结果是:1001、在SQL语言中,结果是:101。
SELECT 100 + 'a' FROM DUAL;
#此时将'a'看做0处理,结果是:100
SELECT 100 + NULL FROM DUAL;
# null值参与运算,结果为null
SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,
100 + 2 * 5 / 2,100 / 3, 100 DIV 0 # 分母如果为0,则结果为null
FROM DUAL;
# 取模运算: % mod
SELECT 12 % 3,12 % 5, 12 MOD -5,-12 % 5,-12 % -5
FROM DUAL;
#练习:查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;
#2. 比较运算符
#2.1 = <=> <> != < <= > >=
# = 的使用
SELECT 1 = 2,1 != 2,1 = '1',1 = 'a',0 = 'a' #字符串存在隐式转换。如果转换数值不成功,则看做0
FROM DUAL;
SELECT 'a' = 'a','ab' = 'ab','a' = 'b' #两边都是字符串的话,则按照ANSI的比较规则进行比较。
FROM DUAL;
SELECT 1 = NULL,NULL = NULL # 只要有null参与判断,结果就为null
FROM DUAL;
SELECT last_name,salary,commission_pct
FROM employees
#where salary = 6000;
WHERE commission_pct = NULL; #此时执行,不会有任何的结果
# <=> :安全等于。 记忆技巧:为NULL而生。
SELECT 1 <=> 2,1 <=> '1',1 <=> 'a',0 <=> 'a'
FROM DUAL;
SELECT 1 <=> NULL, NULL <=> NULL
FROM DUAL;
#练习:查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;
SELECT 3 <> 2,'4' <> NULL, '' != NULL,NULL != NULL
FROM DUAL;
#2.2
#① IS NULL \ IS NOT NULL \ ISNULL
#练习:查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;
#或
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);
#练习:查询表中commission_pct不为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#或
SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;
#② LEAST() \ GREATEST
SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
FROM DUAL;
SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;
#③ BETWEEN 条件下界1 AND 条件上界2 (查询条件1和条件2范围内的数据,包含边界)
#查询工资在6000 到 8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
#where salary between 6000 and 8000;
WHERE salary >= 6000 && salary <= 8000;
#交换6000 和 8000之后,查询不到数据
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 8000 AND 6000;
#查询工资不在6000 到 8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#where salary < 6000 or salary > 8000;
#④ in (set)\ not in (set)
#练习:查询部门为10,20,30部门的员工信息
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20 or department_id = 30;
WHERE department_id IN (10,20,30);
#练习:查询工资不是6000,7000,8000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);
#⑤ LIKE :模糊查询
# % : 代表不确定个数的字符 (0个,1个,或多个)
#练习:查询last_name中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
#练习:查询last_name中以字符'a'开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';
#练习:查询last_name中包含字符'a'且包含字符'e'的员工信息
#写法1:
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法2:
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
# _ :代表一个不确定的字符
#练习:查询第3个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
#练习:查询第2个字符是_且第3个字符是'a'的员工信息
#需要使用转义字符: \
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';
#或者 (了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';
#⑥ REGEXP \ RLIKE :正则表达式
SELECT 'shkstart' REGEXP '^shk', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;
SELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '[ab]'
FROM DUAL;
#3. 逻辑运算符: OR || AND && NOT ! XOR
# or and
SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20;
#where department_id = 10 and department_id = 20;
WHERE department_id = 50 AND salary > 6000;
# not
SELECT last_name,salary,department_id
FROM employees
#where salary not between 6000 and 8000;
#where commission_pct is not null;
WHERE NOT commission_pct <=> NULL;
# XOR :追求的"异"
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;
#注意:AND(&&)的优先级高于OR(||)
#4. 位运算符: & | ^ ~ >> <<
SELECT 12 & 5, 12 | 5,12 ^ 5
FROM DUAL;
SELECT 10 & ~1 FROM DUAL;
#在一定范围内满足:每向左移动1位,相当于乘以2;每向右移动一位,相当于除以2。
SELECT 4 << 1 , 8 >> 1
FROM DUAL;
三、去重、排序和分页
--去重 关键字:distinct
select distinct 字段名 from 表格名;
--排序 关键字:order by
select 字段 from 表格名 order by 字段 排序方式;
-- 多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
select 字段 from 表格名 order by 字段1 排序方式,字段2 排序方式;
/*如果从低到高,从小到大,则后面添加asc 因为默认使用,所以可以不添加
如果从高到低,从大到小,则后面添加desc
注意:asc和desc要添加在列名到后面
列的别名只能在order by中使用 */
--分页查询 关键字:LIMIT
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询识录数;
/*起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10 */
四、单行函数
4.1基本函数
函数
|
用法
|
ABS(x)
|
返回
x
的绝对值
|
SIGN(X)
|
返回
X
的符号。正数返回
1
,负数返回
-1
,
0
返回
0
|
PI()
|
返回圆周率的值
|
CEIL(x)
,
CEILING(x)
|
返回大于或等于某个值的最小整数
|
FLOOR(x)
|
返回小于或等于某个值的最大整数
|
LEAST(e1,e2,e3…)
|
返回列表中的最小值
|
GREATEST(e1,e2,e3…)
|
返回列表中的最大值
|
MOD(x,y)
|
返回
X
除以
Y
后的余数
|
RAND()
|
返回
0~1
的随机值
|
RAND(x)
|
返回
0~1
的随机值,其中
x
的值用作种子值,相同的
X
值会产生相同的随机数
|
ROUND(x)
|
返回一个对
x
的值进行四舍五入后,最接近于
X
的整数
|
ROUND(x,y)
|
返回一个对
x
的值进行四舍五入后最接近
X
的值,并保留到小数点后面
Y
位
|
TRUNCATE(x,y)
|
返回数字
x
截断为
y
位小数的结果
|
SQRT(x) |
返回
x
的平方根。当
X
的值为负数时,返回
NULL
|
RADIANS(x)
|
将角度转化为弧度,其中,参数
x
为角度值
|
DEGREES(x)
|
将弧度转化为角度,其中,参数
x
为弧度值
|
4.2三角函数
函数
|
用法
|
SIN(x)
|
返回
x
的正弦值,其中,参数
x
为弧度值
|
ASIN(x)
|
返回
x
的反正弦值,即获取正弦为
x
的值。如果
x
的值不在
-1
到
1
之间,则返回
NULL
|
COS(x)
|
返回
x
的余弦值,其中,参数
x
为弧度值
|
ACOS(x)
|
返回
x
的反余弦值,即获取余弦为
x
的值。如果
x
的值不在
-1
到
1
之间,则返回
NULL
|
TAN(x)
|
返回
x
的正切值,其中,参数
x
为弧度值
|
ATAN(x)
|
返回
x
的反正切值,即返回正切值为
x
的值
|
ATAN2(m,n)
|
返回两个参数的反正切值
|
COT(x)
|
返回
x
的余切值,其中,
X
为弧度值
|
4.3对数函数
函数 | 用法 |
POW(x,y)
,
POWER(X,Y)
|
返回
x
的
y
次方
|
EXP(X)
|
返回
e
的
X
次方,其中
e
是一个常数,
2.718281828459045
|
LN(X)
,
LOG(X)
|
返回以
e
为底的
X
的对数,当
X <= 0
时,返回的结果为
NULL
|
LOG10(X)
|
返回以
10
为底的
X
的对数,当
X <= 0
时,返回的结果为NULL
|
LOG2(X)
|
返回以
2
为底的
X
的对数,当
X <= 0
时,返回
NULL
|
4.4进制间的转换
函数 | 用法 |
BIN(x)
|
返回
x
的二进制编码
|
HEX(x)
|
返回
x
的十六进制编码
|
OCT(x)
|
返回
x
的八进制编码
|
CONV(x,f1,f2) |
返回
f1
进制数变成
f2
进制数
|
4.5字符串函数
函数 | 用法 |
ASCII(S)
|
返回字符串
S
中的第一个字符的
ASCII
码值
|
CHAR_LENGTH(s)
|
返回字符串
s
的字符数。作用与
CHARACTER_LENGTH(s)
相同
|
LENGTH(s)
|
返回字符串
s
的字节数,和字符集有关
|
CONCAT(s1,s2,......,sn)
|
连接
s1,s2,......,sn
为一个字符串
|
CONCAT_WS(x,
s1,s2,......,sn)
|
同
CONCAT(s1,s2,...)
函数,但是每个字符串之间要加上
x
|
INSERT(str, idx, len,
replacestr)
|
将字符串
str
从第
idx
位置开始,
len
个字符长的子串替换为字符串
replacestr
|
REPLACE(str, a, b)
|
用字符串
b
替换字符串
str
中所有出现的字符串
a
|
UPPER(s)
或
UCASE(s)
|
将字符串
s
的所有字母转成大写字母
|
LOWER(s)
或
LCASE(s)
|
将字符串
s
的所有字母转成小写字母
|
LEFT(str,n)
|
返回字符串
str
最左边的
n
个字符
|
RIGHT(str,n)
|
返回字符串
str
最右边的
n
个字符
|
LPAD(str, len, pad)
|
用字符串
pad
对
str
最左边进行填充,直到
str
的长度为
len
个字符
|
RPAD(str ,len, pad)
|
用字符串
pad
对
str
最右边进行填充,直到
str
的长度为
len
个字符
|
LTRIM(s)
|
去掉字符串
s
左侧的空格
|
RTRIM(s)
|
去掉字符串
s
右侧的空格
|
TRIM(s)
|
去掉字符串
s
开始与结尾的空格
|
TRIM(s1 FROM s)
|
去掉字符串
s
开始与结尾的
s1
|
TRIM(LEADING s1
FROM s)
|
去掉字符串
s
开始处的
s1
|
TRIM(TRAILING s1
FROM s)
|
去掉字符串
s
结尾处的
s1
|
REPEAT(str, n)
|
返回
str
重复
n
次的结果
|
SPACE(n)
|
返回
n
个空格
|
STRCMP(s1,s2)
|
比较字符串
s1,s2
的
ASCII
码值的大小
|
SUBSTR(s,index,len)
|
返回从字符串
s
的
index
位置其
len
个字符,作用与
SUBSTRING(s,n,len)
、
MID(s,n,len)
相同
|
LOCATE(substr,str)
|
返回字符串
substr
在字符串
str
中首次出现的位置,作用于
POSITION(substr
IN str)
、
INSTR(str,substr)
相同。未找到,返回
0
|
ELT(m,s1,s2,…,sn)
|
返回指定位置的字符串,如果
m=1
,则返回
s1
,如果
m=2
,则返回
s2
,如
果
m=n
,则返回
sn
|
FIELD(s,s1,s2,…,sn)
|
返回字符串
s
在字符串列表中第一次出现的位置
|
FIND_IN_SET(s1,s2)
|
返回字符串
s1
在字符串
s2
中出现的位置。其中,字符串
s2
是一个以逗号分
隔的字符串
|
REVERSE(s)
|
返回
s反转后的字符串
|
NULLIF(value1,value2)
|
比较两个字符串,如果
value1
与
value2
相等,则返回
NULL
,否则返回
value1
|
4.6日期和时间函数
4.6.1获取日期、时间
函数
|
用法
|
CURDATE()
,
CURRENT_DATE()
|
返回当前日期,只包含年、 月、日
|
CURTIME()
,
CURRENT_TIME()
|
返回当前时间,只包含时、 分、秒
|
NOW()
/ SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /
LOCALTIMESTAMP()
|
返回当前系统日期和时间
|
UTC_DATE()
|
返回
UTC
(世界标准时间)
日期
|
UTC_TIME()
|
返回
UTC
(世界标准时间)
时间
|
4.6.2日期与时间戳的转换
函数 | 用法 |
UNIX_TIMESTAMP()
|
以
UNIX
时间戳的形式返回当前时间。
SELECT UNIX_TIMESTAMP() -
>1634348884
|
UNIX_TIMESTAMP(date)
|
将时间
date
以
UNIX
时间戳的形式返回。
|
FROM_UNIXTIME(timestamp)
|
将
UNIX
时间戳的时间转换为普通格式的时间
|
4.6.3获取月份、星期、星期数、天数等函数
函数 | 用法 |
YEAR(date) / MONTH(date) / DAY(date)
|
返回具体的日期值
|
HOUR(time) / MINUTE(time) / SECOND(time)
|
返回具体的时间值
|
MONTHNAME(date)
|
返回月份:
January
,
...
|
DAYNAME(date)
|
返回星期几:
MONDAY
,
TUESDAY.....SUNDAY
|
WEEKDAY(date)
|
返回周几,注意,周
1
是
0
,周
2
是
1
,。。。周日是
6
|
QUARTER(date)
|
返回日期对应的季度,范围为
1
~
4
|
WEEK(date)
,
WEEKOFYEAR(date)
|
返回一年中的第几周
|
DAYOFYEAR(date)
|
返回日期是一年中的第几天
|
DAYOFMONTH(date)
|
返回日期位于所在月份的第几天
|
DAYOFWEEK(date)
|
返回周几,注意:周日是
1
,周一是
2
,。。。周六是
7
|
4.6.4时间和秒钟转换的函数
函数
|
用法
|
TIME_TO_SEC(time)
|
将
time
转化为秒并返回结果值。转化的公式为:
小时
*3600+
分钟 *60+秒
|
SEC_TO_TIME(seconds)
|
将
seconds
描述转化为包含小时、分钟和秒的时间
|
4.6.5计算日期和时间的函数
函数
|
用法
|
DATE_ADD(datetime, INTERVAL expr type)
,
ADDDATE(date,INTERVAL expr type)
|
返回与给定日期时间相差
INTERVAL
时
间段的日期时间
|
DATE_SUB(date,INTERVAL expr type)
,
SUBDATE(date,INTERVAL expr type)
|
返回与
date
相差
INTERVAL
时间间隔的
日期
|
上述函数中type的取值
常见的数值函数
常见的流程函数
五、聚合函数
函数 | 功能 |
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
SELECT 聚合函数(字段列表) FROM 表名; --注意:null值不参与所有聚合函数运算
分组查询
1. 语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
2.where与having区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
六、多表查询
--笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合和 B集合的所有组合情况。
--(在多表查询时,需要消除无效的笛卡尔积)
-- 多表查询
select * from emp , dept where emp.dept_id = dept.id;
6.1内连接:查询表A、表B交集部分数据
--隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件…;
--显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件…;
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
-- 1. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
-- 2. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) INNER JOIN ... ON ...
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
6.2外连接:左外、右外、满外
--左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…;
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
--右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件…;
相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
--满外连接(MySQL不支持)
SELECT 字段列表 FROM 表1 FULL [OUTER] JOIN 表2 ON 条件…;
返回所有符合条件的数据
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
-- 1. 查询emp表的所有数据, 和对应的部门信息(左外连接)
select e.*, d.name from emp e left outer 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;
-- 3. 查询所有满足条件的数据(满外连接)
select d.*, e.* from emp e full outer join dept d on e.dept_id = d.id;
6.3自连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B where 条件…;
--自连接查询,可以是内连接查询,也可以是外连接查询。
--当前表与自身的连接查询,自连接必须使用表别名
-- 自连接
-- 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;
6.4联合查询-union, union all
- UNION:会执行去重操作
- UNION ALL:不会执行去重操作(效率高)
7种JOIN的实现:
# 中图:内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
# 左上图:左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
# 右上图:右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# 左中图:
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;
# 右中图:
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# 左下图:满外连接
# 方式1:左上图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# 方式2:左中图 UNION ALL 右上图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# 右下图:左中图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
SQL99语法的新特性1:自然连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`,然后进行`等值连接`。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
#11. SQL99语法的新特性2:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
#拓展:
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
SELECT 字段列表 FROM 表A …
UNION [ALL]
SELECT 字段列表 FROM 表B …;
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
七、子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:WHERE之后、FROM之后、SELECT 之后
7.1标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),这种子查询成为标量子查询。
常用的操作符:= <> > >= < <=
-- 标量子查询
-- 查询 "销售部" 的所有员工信息
-- a. 查询 "销售部" 部门ID
select id from dept where name = '销售部';
-- b. 根据销售部部门ID, 查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
7.2列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:
-- 列子查询
-- 1. 查询 "销售部" 和 "市场部" 的所有员工信息
-- a. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';
-- b. 根据部门ID, 查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
-- 2. 查询比 财务部 所有人工资都高的员工信息
-- a. 查询所有 财务部 人员工资
select id from dept where name = '财务部';
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 = '财务部') );
-- 3. 查询比研发部其中任意一人工资高的员工信息
-- a. 查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
-- b. 比研发部其中任意一人工资高的员工信息
select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = '研发部') );
7.3行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:=、<>、IN、NOT IN
-- 行子查询
-- 1. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
-- a. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';
-- b. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
7.4表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符: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 = '宋远桥' );
-- 2. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- a. 入职日期是 "2006-01-01" 之后的员工信息
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 ;
多表查询案例
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;
-- 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;
-- 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;
-- 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
select e.* , s.grade , s.losal, s.hisal 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;
-- 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
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 > ( 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. 查询指定部门平均薪资 1
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;
-- b. 查询低于本部门平均工资的员工信息
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;
select count(*) from emp where dept_id = 1;
-- 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 ;
八、常见DCL(控制语言)命令
1.查询用户
USE mysql;
SELECT * FROM user;
2. 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 创建用户 itcast , 只能够在当前主机localhost访问, 密码123456;
create user 'itcast'@'localhost' identified by '123456';
-- 创建用户 heima , 可以在任意主机访问该数据库, 密码123456 ;
create user 'heima'@'%' identified by '123456';
3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
-- 修改用户 heima 的访问密码为 1234 ;
alter user 'heima'@'%' identified with mysql_native_password by '1234';
4.删除用户
DROP USER '用户名'@'主机名';
-- 删除itcast@localhost用户
drop user 'itcast'@'localhost';
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
eg:show grants for 'heima'@'%';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
eg:grant all on itcast.* to 'heima'@'%';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
eg:REVOKE all on itcast.* to 'heima'@'%';
二、数据库创建、修改与删除
--创建
Create database 数据库名;
--使用某个数据库
Use 数据库名;
--查看有哪些数据库
show databases;
--查看指定数据库下保存的数据表
show tables from 数据库名;
--查询当前使用的数据库
SELECT DATABASE();
--查询当前数据库所有表
SHOW TABLES;
--查询表结构
DESC 表名;
--查询指定表的建表语句
SHOW CREATE TABLE 表名;
--更改数据库字符集
SHOW CREATE DATABASE 数据库名;
ALTER DATABASE 数据库名 CHARACTER SET 'utf8';
#1.4 删除数据库
#方式1:如果要删除的数据库存在,则删除成功。如果不存在,则报错
DROP DATABASE mytest1;
SHOW DATABASES;
#方式2:推荐。 如果要删除的数据库存在,则删除成功。如果不存在,则默默结束,不会报错。
DROP DATABASE IF EXISTS mytest1;
DROP DATABASE IF EXISTS mytest2;
#2. 如何创建数据表
USE atguigudb;
SHOW CREATE DATABASE atguigudb; #默认使用的是utf8
SHOW TABLES;
四、创建某个表
Create table 表名 (
列名1 int auto_increment primary key,
列名2 varchar(字符个数) not null,
列名3 date null
);
--每个列名和每个列名用逗号隔开,最后一个则不需要
命令名
含义 命令名 含义 命令名 含义 Not null
不能为空
Null
可以为空
auto_increment
自动递增
primary key
设置为主键
五、插入数据
1.给指定字段添加数据
INSERT INTO 数据库名.表名(列名1,列名2,列名3) VALUES (数值1,数值2,数值3);
2.给全部字段添加数据
INSERT INTO 数据库名.表名 VALUES (值1,值2,……);
3.批量添加数据
INSERT INTO 数据库名.表名(列名1,列名2) VALUES (数值1,数值2),(数值1,数值2),(数值1,数值2);
INSERT INTO 数据库名.表名 VALUES (值1,值2,……),(值1,值2,……),(值1,值2,……);
命令 | 含义 |
Default | 帮助我们后面递增数字 |
六、改变表格
--增加一个列(添加字段)
Alter table 数据库名.表名 add 列名 数据类型 默许条件;
--修改数据类型
Alter Table 数据库名.表名 modify 字段名 新数据类型(长度);
--修改字段名和字段类型
ALTER TABLE 数据库名.表名 change 旧字段名 新字段名 类型(长度);
--删除字段
ALTER TABLE 数据库名.表名 DROP 字段名;
--修改表名
ALTER TABLE 数据库名.表名 RENAME TO 新表名;
--修改数据
Update 数据库名.表格名 set 值 [where 条件];
--注意:若不跟where条件,则会更新整个表,使用需谨慎
UPDATE egg.eggs_record SET sold = '2022-06-06' WHERE id = 3;
--删除数据库某个值
Delete from 数据库名.表格名 where 条件;
--删除数据库某个表格
Drop table 数据库名.表格名;
--删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
--删除数据库
Drop database 数据库名;
数值类型
字符串类型
日期类型
九、约束
9.1概念目的
约束是作用于表中字段上的规则,用于限制存储在表中的数据。:保证数据库中数据的正确、有效性和完整性。
9.2约束创建方式
- 创建表时规定约束(通过 CREATE TABLE 语句)
- 表创建之后通过 ALTER TABLE 语句规定约束
9.3约束类型
查看某个表已有的约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';
9.3.1非空约束-- not null
(1)默认,所有的类型的值都可以是NULL,包括INT、FLOAT等数据类型
(2)非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空
(3)一个表可以有很多列都分别限定了非空
(4)空字符串''不等于NULL,0也不等于NULL
--建表时
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL
);
--建表后
alter table 表名称 modify 字段名 数据类型 not null;
删除非空约束
alter table 表名称 modify 字段名 数据类型 NULL;
--去掉not null,相当于修改某个非注解字段,该字段允许为空
或
alter table 表名称 modify 字段名 数据类型;
--去掉not null,相当于修改某个非注解字段,该字段允许为空
9.3.2唯一性约束--unique
(1)同一个表可以有多个唯一约束。
(2)唯一约束可以是某一个列的值唯一,也可以多个列组合的值唯一。
(3)唯一性约束允许列值为空。
(4)在创建唯一约束的时候,如果不给唯一约束命名,就默认和列名相同。
(5)MySQL会给唯一约束的列上默认创建一个唯一索引。
--建表时
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名) --此处字段名可多个组合
);
--建表后
--方式1:
alter table 表名称 add unique key(字段列表);
--方式2:
alter table 表名称 modify 字段名 字段类型 unique;
--删除唯一性约束
添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同;如果是组合列,那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
#查看都有哪些约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';
ALTER TABLE USER DROP INDEX 唯一约束名;
9.3.3主键约束 --primary key
主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值。
(1)一个表只有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建。
(2)主键约束对应着表中的一列或者多列(复合主键)
(3)如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复。
(4)MySQL的主键名总是PRIMARY,就算自己命名了主键约束名也没用。
(5)当创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高)。如果删除主键约束了,主键约束对应的索引就自动删除了。
(6)需要注意的一点是,不要修改主键字段的值。因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性。
--建表时
create table 表名称(
字段名 数据类型 PRIMARY KEY,
字段名 数据类型
);
或
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名)
);
--建表后
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);
--删除主键约束(实际开发中,不会去删除表中的主键约束!)
alter table 表名称 drop primary key;
9.3.4自增列--AUTO_INCREMENT
(1)一个表最多只能有一个自增长列
(2)当需要产生唯一标识符或顺序值时,可设置自增长
(3)自增长列约束的列必须是键列(主键列,唯一键列)
(4)自增约束的列的数据类型必须是整数类型
(5)如果自增列指定了0 和null,会在当前最大值的基础上自增;如果自增列手动指定了具体值,直接赋值为具体值。
--建表时
create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null
);
开发中,一旦主键作用的字段上声明有AUTO_INCREMENT,则我们在添加数据时,就不要给主键对应的字段去赋值了。
当我们向主键(含AUTO_INCREMENT)的字段上添加0 或 null时,实际上会自动的往上添加指定的字段的数值
--建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;
--删除自增约束
--去掉auto_increment相当于删除
alter table 表名称 modify 字段名 数据类型;
9.3.5外键约束--FOREIGN KEY
(1)从表的外键列,必须引用/参考主表的主键或唯一约束的列
为什么?因为被依赖/被参考的值必须是唯一的
(2)在创建外键约束时,如果不给外键约束命名,默认名不是列名,而是自动产生一个外键
(例如student_ibfk_1;),也可以指定外键约束名。
(3)创建表时就指定外键约束的话,先创建主表,再创建从表
(4)删表时,先删从表(或先删除外键约束),再删除主表
(5)当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖
该记录的数据,然后才可以删除主表的数据
(6)在“从表”中指定外键约束,并且一个表可以建立多个外键约束
(7)从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样,逻辑意义一致。如果类
型不一样,创建子表时,就会出现错误“ERROR 1005 (HY000): Can't create
--建表时
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY(从表的某个字段) references 主表名(主表字段)
);
--(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致,逻辑意义也一样
--(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样,也可以不一样
eg:
--主表
create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
--从表
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
--emp表的deptid和和dept表的did的数据类型一致,意义都是表示部门的编号
);
说明:
(1)主表dept必须先创建成功,然后才能创建emp表,指定外键成功。
(2)删除表时,先删除从表emp,再删除主表dept
--建表后
一般情况下,表与表的关联都是提前设计好了的,因此,会在创建表的时候就把外键约束定义好。不过,如果需要修改表的设计(比如添加新的字段,增加新的关联关系),但没有预先定义外键约束,那么,就要用修改表的方式来补充定义。
ALTER TABLE 从表名
ADD [CONSTRAINT 约束名] FOREIGN KEY (从表字段) REFERENCES 主表名(引用字段);
eg:
ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);
--删除外键约束
(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
(2)第二步查看索引名和删除索引。(注意,只能手动删除)
--查看某个表的索引名
SHOW INDEX FROM 表名称;
ALTER TABLE 从表名 DROP INDEX 索引名;
9.3.5.1约束等级
9.3.5.2约束等级的设置
-- 约束等级的设置
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];
alter table emp add foreign key (dept_id) references dept(id)
on update cascade on delete cascade ;
alter table emp add foreign key (dept_id) references dept(id)
on update set null on delete set null ;
9.3.6 检查约束 --check
注意:MySQL5.7 不支持CHECK约束,MySQL8.0支持CHECK约束。
--建表时
CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary > 2000)
);
此时,添加salary 字段时,小于2000的数据将报错失败
--建表后
ALTER TABLE 表名 ADD CHECK (salary > 2000)
9.3.7 默认值约束--DEFAULT
--创建表
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) DEFAULT 2000
);
此时当添加的数据有salary,将为添加的值,若没有,则默认为2000
--建表后
ALTER TABLE test12 MODIFY salary DECIMAL(8,2) DEFAULT 2500;
删除约束
ALTER TABLE test12 MODIFY salary DECIMAL(8,2);
十、视图
10.1理解
① 视图,可以看做是一个虚拟表,本身是不存储数据的。视图的本质,就可以看做是存储起来的SELECT语句
② 视图中SELECT语句中涉及到的表,称为基表
③ 针对视图做DML操作,会影响到对应的基表中的数据。反之亦然。
④ 视图本身的删除,不会导致基表中数据的删除。
⑤ 视图的应用场景:针对于小型项目,不推荐使用视图。针对于大型项目,可以考虑使用视图。
⑥ 视图的优点:简化查询; 控制数据的访问
--视图优点
1.操作简单
将经常使用的查询操作定义为视图,可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系,也不需要关心数据表之间的业务逻辑和查询条件,而只需要简单地操作视图即可,极大简化了开发人员对数据库的操作。
2. 减少数据冗余
视图跟实际数据表不一样,它存储的是查询语句。所以,在使用的时候,我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据,不占用数据存储的资源,减少了数据冗余。
3. 数据安全
MySQL将用户对数据的 访问限制 在某些数据的结果集上,而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性 。视图相当于在用户和实际的数据表之间加了一层虚拟表。同时,MySQL可以根据权限将用户对数据的访问限制在某些视图上,用户不需要查询数据表,可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。
4. 适应灵活多变的需求
当业务系统的需求发生变化后,如果需要改动数据表的结构,则工作量相对较大,可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。
5. 能够分解复杂的查询逻辑
数据库中如果存在复杂的查询逻辑,则可以将问题进行分解,创建多个视图获取数据,再将创建的多个视图结合起来,完成复杂的查询逻辑。
10.2视图相关命令
--创建视图
CREATE OR REPLACE VIEW 视图名称
AS
查询语句;
--查看视图
SHOW TABLES;
--查看视图的结构
DESCRIBE vu_emp1;
--查看视图的属性信息
SHOW TABLE STATUS LIKE '视图名';
--查看视图的详细定义信息
SHOW CREATE VIEW 视图名;
--删除视图
DROP VIEW 视图名;
--核心思想和表是一样的
十一、存储过程
事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
预备工作
-- 数据准备
create table account(
id int auto_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 = '李四';
方式一:修改提交方式为手动
1.查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit =0; --设置为手动提交
--1为自动提交,0为手动提交
2.转账操作流程
-- 转账操作 (张三给李四转账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 = '李四';
注意:若出现 Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
原因:mysql的safe-updates模式中,如果where后跟的条件不是主键,就会出现这种错误
两种方式:
- where后面加上主键的条件
- 更改模式:SET SQL_SAFE_UPDATES = 0;
建议更改数据库模式
如果想要提高数据库安全等级,可以在恢复回原有的设置,执行命令:
SET SQL_SAFE_UPDATES = 1;
当任务完成后,则可以手动提交事务
COMMIT;
当任务报错,则可以回滚事务
ROLLBACK;
方式二(不用修改事物的提交方式)
1.开启事务
start transaction;
2.转账操作流程
-- 转账操作 (张三给李四转账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 = '李四';
当任务完成后,则可以手动提交事务
COMMIT;
当任务报错,则可以回滚事务
ROLLBACK;
事务的四大特性
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
事务隔离级别
目的:解决并发带来的问题
mysql默认为Repeatable Read,oracle默认为Read committed
由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED I REPEATABLE READ I SERIALIZABLE }
eg1:set session transaction isolation level read uncommitted ;
eg2:set session transaction isolation level repeatable read ;
注意:事务隔离级别越高,数据越安全,但是性能越低。