1. MySQL概述
1.1 数据库相关概念
名称 | 全称 | 简称 |
---|---|---|
数据库 | 存储数据的仓库,数据是有组织的进行存储 | DataBase (DB) |
数据库管理系统 | 操作和管理数据库的大型软件 | DataBase Management System (DBMS) |
SQL | 操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准 | Structured Query Language (SQL) |
1.2 MySQL数据库
2.SQL
2.1 SQL通用语法
-
SQL语句可以单行或多行书写,以分号结尾
-
SQL语句可以使用空格/或者缩进来增强语句的可读性
-
MySQL数据库的语句不区分大小写,关键字建议使用大写
-
注释:
-
单行注释:-- 注释内容 或 # 注释内容(MySQL特有)
-
多行注释:/* 注释内容 */
-
2.2 SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户,控制数据库访问权限 |
3.DDL语句(数据定义语句)
3.1 DDL-数据库操作
1.查询
查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();
2.创建
CREATE DATABASE [IF NOT EXISTS]数据库名[DEFAULT CHARSET字符集][COLLATE排序规则];
3.删除
DROP DATABASE[IF EXISTS]数据库名;
4.使用
USE 数据库名;
3.2 DDL-表操作-查询
1.查看当前数据库所有表
SHOW TABLES;
2.查询表结构
DESC 表名;
3.查询指定表的建表语句
SHOW CREATE TABLE 表名;
3.3 DDL-表操作-创建
CREATE TABLE 表名( 字段1 字段1类型[COMMENT 字段1注释], 字段2 字段2类型[COMMENT 字段2注释], 字段3 字段3类型[COMMENT 字段3注释], …… 字段n 字段n类型[COMMENT 字段n注释] )[COMMENT 表注释];
3.4 DDL-表操作-数据类型
MySQL中数据类型很多,主要分为三类:数值类型,字符串类型,日期时间类型。
1.1 数值类型
tip:精度表示总共的位数,标度表示小数点后的位数
1.2 字符串类型
1.3 日期时间类型
1/案例(创建表)
create table emp( id int comment'编号', workno varchar(10) comment'员工工号', name varchar(10) comment '员工姓名', gender char(1) comment'性别', age tinyint unsigned comment'年龄', idcard char(18) comment'身份证号', entrydate date comment'入职时间' ) comment'员工表';
3.5DDL-表操作-修改
1.添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释] [约束];
1/案例(增加字段)
为emp表增加一个新的字段“昵称”为nickname,类型为varchar(20)
alter table emp add nickname varchar(20) comment'昵称';
3.6DDL-表操作-修改
1.修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
2.修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
##1/案例(修改字段)
将emp表的nickname字段修改为username,类型为varchar(30)
alter table emp change nickname username varchar(30) comment'用户名';
1.删除字段
ALTER TABLE 表名 DROP 字段名;
1/案例(删除字段)
将emp表字段username删除
alter table emp drop username;
2.修改表名
ALTER TABLE 表名 RENAME TO 新表名;
1/案例
将emp表名修改为employee
alter table emp rename to employee;
3.7DDL -表操作-删除
1.删除表
DROP TABLE [IF EXISTS] 表名;
2.删除指定表,并重新创建该表(相当于格式化)
TRUNCATE TABLE 表名;
3.8 DDL-总结
1.DDL-数据库操作
SHOW DATABASES; CREATE DATABASE 数据库名; USE 数据库名; SELECT DATABASES(); -- 查看当前处于哪一个数据库当中 DROP DATABASE 数据库名;
2.DDL-表操作
SHOW TABLES; CREATE TABLE 表名(字段 字段类型,字段 字段类型); DESC 表名; SHOW CREATE TABLE 表名; ALTER TABLE 表名 ADD/MODIFY/CHANGE/DROP/RENAME TO …; DROP TABLE 表名;
4.DML语句(数据库操作语言)
-
DML-介绍
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增删改操作。
-
添加数据(INSERT)
-
修改数据(UPDATE)
-
删除数据(DELETE)
4.1 DML-添加数据
1.给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,…) VALUES(值1,值2,…);
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,…);
[!NOTE]
出入数据时,指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应包含在引号中。
插入的数据大小,应该在字段的规定范围内。
4.2 DML-查看表数据
SELECT * FROM 表名;
[!IMPORTANT]
注意添加内容的时候如果中文报错需要修改编码
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8;
4.3 DML-修改数据
UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,…[WHERE 条件];
[!NOTE]
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
2/案例
-- 修改id为1的数据,将name修改为itheima update employee set name = 'itheima' where id = 1; -- 修改id为1的数据,将name修改为 小昭 ,gender 修改为女 update employee set name = '小昭' ,gender = '女' where id = 1; -- 将所有的员工入职日期修改为2008-01-01 update employee set entrydate = '2008-01-01';
4.4 DML-删除数据
DELETE FROM 表名 [WHERE 条件];
[!NOTE]
注意:
DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据
DELETE 语句不能删除某一个字段的值(可以使用UPDATE)。
2/案例
-- 删除gender为女的员工 delete from employee where gender = '女'; -- 删除所有员工 delete from employee;
4.5 DML-总结
1.添加数据
INSERT INTO 表名 (字段1,字段2,…) VALUES(值1,值2,…) [,(值1,值2,…)…];
2.修改数据
UPDATE 表名 SET 字段1 = 值1,字段2 = 值2 [WHERE 条件];
3.删除数据
DELETE FROM 表名 [WHERE 条件];
5.DQL语句(数据查询语言)
-
DQL-介绍
DQL英文全称是Data Qeury Language(数据查询语言),数据查询语言,用来查询数据库中表的记录。
查询关键字:SELECT
-
DQL-语法
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
-
基本查询
-
条件查询(WHERE)
-
聚合函数(count, max, min, avg, sum)
-
分组查询(GROUP BY)
-
排序查询(ORDER BY)
-
分页查询(LIMIT)
5.1 DQL-基本查询
1.查询多个字段
SELECT 字段1,字段2,字段3… FROM 表名;
SELECT * FROM 表名;
2.设置别名
SELECT 字段1 [AS 别名1],字段2[AS 别名2] … FROM 表名;
3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
3/案例
-- 基本查询 -- 1.查询指定字段 name,workno,age 返回 select name,workno,age from emp; -- 2.查询所有字段返回 select id,workno,name,gender,age,idcard,workaddress,entry from emp; select * from emp; -- 3.查询所有员工地址,起别名 select workaddress as '工作地址' from emp; select workaddress '工作地址' from emp; -- 4.查询公司员工上班地址(不要重复) select distinct workaddress '工作地址' from emp;
5.2 DQL-条件查询
1.语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
2.条件
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN…AND… | 在某个范围之内(含最小,最大值) |
IN(…) | 在in之后的列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意一个成立) |
NOT 或 ! | 非,不是 |
3/案例
-- 条件查询 -- 1.查询年龄等于88的员工 select * from emp where age=88; -- 2.查询年龄小于20的员工信息 select * from emp where age <20; -- 3.查询没有身份证号的员工信息 select * from emp where idcard is null; -- 4.查询有身份证号的员工信息 select * from emp where idcard is not null; -- 5.查询年龄不等于88的员工信息 select * from emp where age != 88; select * from emp where age <> 88; -- 6.查询年龄在15岁(包含)到20岁(包含)之间的员工信息 select * from emp where age >= 15 and age <= 20; select * from emp where age >= 15 && age <= 20; select * from emp where age between 15 and 20; -- 注意: between后面接最小值,and后面接最大值 -- 7.查询性别女且年龄小于25岁的员工信息 select * from emp where gender = '女' and age < 25; -- 8.查询年龄等于18 或 20 或40 的员工信息 select * from emp where age in (18,20,40); -- 9.查询姓名为两个字的员工信息 select * from emp where name like '__'; -- 10.查询身份证号最后一位是x的员工信息 select * from emp where idcard like '%x'; select * from emp where idcard like '________________x';
5.3 DQL-聚合函数
1.介绍
将一列数据作为一个整体,进行纵向计算
2.常见的聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
3.语法
SELECT 聚合函数(字段列表) FROM 表名;
[!WARNING]
注意:null 值不参与所有聚合函数运算.
3/案例
-- 聚合函数 -- 1.统计该企业员工数量 select count(*) from emp; -- 2.统计该企业员工的平均年龄 select avg(age) from emp; -- 3.统计该企业员工的最大年龄 select max(age) from emp; -- 4.统计该企业员工的最小年龄 select min(age) from emp; -- 5.统计西安地区员工的年龄之和 select sum(age) from emp where workaddress = '西安';
5.4 DQL-分组查询
1.语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
2.where与having区别
-
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
-
判断条件不同:where不能对聚合函数进行判断,而having可以。
3/案例
-- 分组查询 -- 1.根据性别分组,统计男性员工 和 女性员工的数量; select gender ,count(*) from emp group by gender; -- 2.根据性别分组,统计男性员工 和 女性员工的平均年龄; select gender,avg(age) from emp group by gender; -- 3.查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址; select workaddress,count(*) address_count from emp where age<45 group by workaddress having address_count >= 3;
[!CAUTION]
注意:
执行顺序:where > 聚合函数 > having。
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
5.5 DQL-排列查询
1.语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
2.排序方式
-
ASC: 升序(默认值)
-
DESC: 降序
[!CAUTION]
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
3/案例
-- 排序查询 -- 1.根据年龄对公司员工进行升序排序 select * from emp order by age asc; -- 2.根据入职时间,对员工进行降序排序 select * from emp order by entrydate desc; -- 3.根据年龄对公司员工进行升序排序,年龄相同,再按照入职时间进行降序排序 select * from emp order by age asc,entrydate desc;
5.6 DQL -分页查询
1.语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
[!CAUTION]
注意:
起始索引从0开始,起始索引 = (查询页码 -1) * 每页显示记录数。
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10。
3/案例
-- 分页查询 -- 1.查询第1页员工数据,每页展示10条记录 select * from emp limit 0,10; -- 2.查询第2页员工数据,每页展示10条记录 【起始索引 = (查询页码-1) * 每页显示记录数】 select * from emp limit 10,10;
5.7 DQL-案例练习
-- ---------------------------DQL 语句练习------------------------------- -- 1.查询年龄为20,21,22,23岁的女性员工信息。 select * from emp where gender = '女' and age in(20,21,22,23); -- 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;
5.8 DQL-执行顺序
-
FROM
-
WHERE
-
GROUP BY / HAVING
-
SELECT
-
ORDER BY
-
LIMIT
5.9 DQL-总结
1.DQL语句
SELECT 字段列表 字段名 [AS] 别名 FROM 表名 WHERE 条件列表 > >= < <= = <> like between…and in and or GROUP BY 分组字段列表 分组之前过滤 HAVING 分组后条件列表 分组之后过滤 ORDER BY 排序字段列表 升序 ASC ,降序 DESC LIMIT 分页参数 起始索引(从0开始),每页展示记录数
6.DCL语句(数据控制语言)
-
DCL-介绍
DCL英文全称是Data Control Language(数据控制语言),用来管理数据库,控制数据库访问权限。
6.1 DCL-用户管理
1.查询用户
USE mysql; SELECT * FROM user;
2.创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
4.删除用户
DROP USER '用户名'@'主机名';
[!IMPORTANT]
注意:
主机名可以使用%通配。
这类SQL开发人员操作的比较少,主要是DBA(Database Administrator 数据库管理员)使用。
-- 创建用户 itcast,只能够在当前主机localhost访问,密码123456 create user 'itcast'@'localhost' idendified by '123456'; -- 创建用户heima,可以在任意主机访问该数据库,密码123456 create user 'heima'@'%' identified by '123456'; -- 修改用heima的访问密码为1234; alter user 'heima'@'%' identified with mysql_native_password by '1234'; -- 删除itcast@localhost用户 drop user 'itcast'@'localhost';
6.2 DCL-权限控制
1.MySQL中定义了很多种权限,但是常用的就以下几种。
权限 | 说明 |
---|---|
ALL ,ALL PRIVILIEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
1.查询权限
SHOW GRANTS FOR '用户名'@'主机名';
2.授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3.撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
[!IMPORTANT]
注意:
多个权限之间使用逗号分隔
授权时,数据库名和表名可以使用*进行通配,代表所有
-- 查询权限 show grants for 'heima'@'%'; -- 授予权限 grant all on itcast.* to 'heima'@'%'; -- 撤销权限 revoke all on itcast.* from 'heima'@'%';
6.3 DCL-总结
1.用户管理
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码'; DROP USER '用户名'@'主机名';
2.权限控制
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
7.函数
函数 是指一段可以直接被另一段程序调用的程序或代码。
7.1 函数-字符串函数
函数 | 功能 |
---|---|
CONCAT | 字符串拼接,将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,strat,len) | 返回从字符串str从start位置起的len个长度的字符串 |
SELECT 函数(参数);
-- CONCAT SELECT CONCAT('HELLO'); -- LOWER SELECT LOWER('HELLO'); -- UPPER SELECT UPPER('hello'); -- LPAD SELECT LPAD('HELLO',6,'_'); -- RPAD SELECT RPAD('HELLO',6,'_'); -- TRIM SELECT TRIM(' HELLO MYSQL'); -- SUBSTRING SELECT SUBSTRING('HELLO MYSQL',1,6);
4/案例
-- 1.由于业务需求变更,企业员工的工号,统一为5位数,目前不足5位数的全部在前面补0.比如:1号员工的工号应该是00001 update emp set workno = lpad(wporkno,5,'0');
7.2 函数-数值函数
常见的数值函数如下:
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 想下取整 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0-1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数 |
-- 数值函数 -- ceil select ceil(1.1); -- floor select floor(2.4); -- mod select mod(3,2); -- rand select rand(); -- round select round(2.345,2);
4/案例
通过数据库的函数,生成一个六位数的随机验证码。
select lpad(round(rand()*1000000,0),6,'0');
7.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() select curdate(); -- curtime() select curtime(); -- NOW() select now(); -- year(date) select year(); -- month,year,day select month(now()); select year(now()); select day(now()); -- date_add select date_add(now,interval 70 day); -- datediff select datediff('2024-02-05','2026-02-06');
4/案例
-- 查询所有员工的入职天数,并根据入职天数倒序排序 select name,datediff(curdate(),entrydate) entrydays from emp order by entrys asc ;
7.4 函数-流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率
函数 | 功能 |
---|---|
IF(value,t,f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [val1] THEN [res1] … ELSE [default] END | 如果val1位true,返回res1,…否则返回default默认值 |
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END | 如果expr的值等于val1,返回res1,…否则返回default默认值 |
-- case when then else end -- 需求:查询emp表的员工姓名和工作地址(北京/上海----->一线城市,其他------->二线城市) select name, (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' from emp;
4/案例
-- 案例:统计班级各个学员的成绩,展示规则如下: -- >= 85,展示优秀 -- >= 60,展示及格 -- 否则,展示不及格 create table score( id int comment'ID', name varchar(20) comment'姓名', math int comment'数学', english int comment'英语', chinese int comment'语文' )comment'学员成绩'; insert into score(id, name, math, english, chinese) values (1,'Tom',67,88,95),(2,'Rose',23,66,90),(3,'Jack',56,98,76); select id, name, (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格'end) as '数学', (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格'end) as '英语', (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格'end) as '语文' from score;
7.5 函数-总结
1.字符串函数
CONCAT,LOWER,UPPER,LPAD,RPAD,TRIM,SUBSTRING
2.数值函数
CEIL,FLOOR,MAD,RAND,ROUND
3.日期函数
CURDATE,CURTIME,NOW,YEAR,MONTH,DAY,YEAR,DATE_ADD,DATEDIFF
4.流程函数
IF,IFNULL,CASE […] WHEN … ELSE … END
8.约束
8.1 约束分类
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.1版本后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张图的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
约束是作用于表中字段上的,可以再创建表/修改表的时候添加约束。
8.2 常见约束
约束条件 | 关键字 |
---|---|
主键 | PRIMARY KEY |
自动增长 | AUTO_INCREMENT |
不为空 | NOT NULL |
唯一 | UNIQUE |
逻辑条件 | CHECK |
默认值 | DEFAULT |
1. 例
例子:
create table user( id int primary key auto_increment, name varchar(10) not null unique, age int check(age > 0 and age < 120), status char(1) default '1', gender char(1) );
8.3 外键约束
添加外键:
CREATE TABLE 表名( 字段名 字段类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ); ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名); -- 例子 alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);
删除外键: ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
删除/更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与RESTRICT一致) |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键在子表中的记录 |
SET NULL | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(要求该外键允许为null) |
SET DEFAULT | 父表有变更时,子表将外键设为一个默认值(Innodb不支持) |
更改删除/更新行为: ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;
9.多表查询
9.1 多表查询分类
多表查询分类:
连接查询:
内连接:
相当于查询A、B交集部分
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据 右外连接:查询右表所有数据,以及两张表交集部分数据 自连接:当前表与自身的连接查询,自连接必须使用表别名
子查询:
9.2 连接查询-内连接
语法:
-- 隐式内连接: select 字段列表 from 表1, 表2 where 条件…; -- 显示内连接: select 字段列表 from 表1 [inner] join 表2 on 连接条件…;
内连接演示:
-- 内连接演示: -- 1.查询每一个员工的姓名,及关联的部门名称(隐式内连接) -- 表结构: emp,dept -- 连接条件:empt.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.查询每一个员工的姓名,及关联的部门名称(显示内连接)……inner join …… on…… 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;
9.3 连接查询-外连接
语法:
-- 左外连接语法格式: select 字段列表 from 表1 left [outer] join 表2 on 条件…; -- 右外连接语法格式: select 字段列表 from 表2 right [outer] join 表2 on 条件……;
外连接演示:
-- 外连接演示: -- 1.查询emp表中所有数据,和相对应的部门信息(左外连接) -- 表结构emp dept -- 连接条件:empt.dept_id=dept.id select e.*,d.name from emp e left join dept d on e.dept_id=d.id; -- 2.查询dept表中所有数据,和对应的员工信息(右外连接) select d.*,e.name from emp e right join dept d on e.dept_id=d.id; -- 右外连接可以改成左外连接 select d.*,e.name from dept d left join emp e on e.dept_id=d.id;
9.4连接查询-自连接
语法:
select 字段列表 from 表A 别名A join 表A 别名B on 条件……; -- 自连接查询,可以是内连接查询,也可以是外连接查询。
10.事务
事务是一组操作的集合,事务会把所有操作看作为一个整体一起向系统提交货撤销操作请求(同时成功/失败)
基本操作:
方式一:
-- 1.查询张三账户余额 select * from account where name ='张三'; -- 2.将张三账户余额 -1000 update account set money = money -1000 where name='张三'; -- 此语句出错后张三钱减少但是李四钱没有增加 -- 模拟sql语句错误 -- 3. 将李四账户余额+1000 update account set money = money + 1000 where name = '李四'; -- 查看事务提交方式 SELECT @@AUTOCOMMIT; -- 设置事务提交方式,1为自动提交,0为手动提交,该设置只对当前会话有效 SET @@AUTOCOMMIT = 0; -- 提交事务 COMMIT; -- 回滚事务 ROLLBACK; -- 设置手动提交后上面代码改为: select * from account where name = '张三'; update account set money = money - 1000 where name = '张三'; update account set money = money + 1000 where name = '李四'; commit;
方式二 :
-- 开启事务 START TRANSACTION 或 BEGIN TRANSACTION; -- 提交事务 COMMIT; -- 回滚事务 ROLLBACK; -- 示例: start transaction; select * from account where name = '张三'; update account set money = money - 1000 where name = '张三'; update account set money = money + 1000 where name = '李四'; commit;
start transaction; select * from account where name = '张三'; update account set money = money - 1000 where name = '张三'; update account set money = money + 1000 where name = '李四'; commit;
四大特性ACID
-
原子性(Atomicity):事务是不可分割的最小操作但愿,要么全部成功,要么全部失败
-
一致性(Consistency):事务完成时,必须使所有数据都保持一致状态
-
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
-
持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
并发事务
问题 | 描述 |
---|---|
脏读 | 一个事务读到另一个事务还没提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是再插入数据时,又发现这行数据已经存在 |
并发事务隔离级别:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable Read(默认) | × | × | √ |
Serializable | × | × | × |
-
√表示在当前隔离级别下该问题会出现
-
Serializable 性能最低;Read uncommitted 性能最高,数据安全性最差
查看事务隔离级别: SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别: SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
SESSION 是会话级别,表示只针对当前会话有效,GLOBAL 表示对所有会话有效