数据库基础篇笔记-黑马

基础篇

数据库的相关介绍

数据库的好处
  1. 可以持续化数据到本地
  2. 可以实现结构化查询，方便管理
数据库相关概念
  1. DB：数据库，保存一组有组织的数据的容器（Data Base）
  2. DBMS：数据库管理系统，又称为数据库管理软件（产品），用于管理 数据库DB 中的数据（Data Base Manage System）
  3. SQL：结构化查询语言，用于和 数据库管理系统DBMS 通讯的语言
数据库的特点
  1. 将数据存储到表中，表再放入到数据库中
  2. 一个数据库可以有多张表，每个表有一个唯一的名字，用来标识自己
  3. 表具有一些特性（列），这些特性定义了数据在表中如何存储，类似 JAVA的类
  4. 表由列组成，我们也成为字段。所有表是由一个或多个列组成，每一列类似 JAVA中的属性
  5. 表中的数据是按行存储的，类似 JAVA中的对象
DBMS分为两类

基于共享文件系统的DBMS，例如Access

基于客户机——服务器的DBMS，例如MySQL，Oracle，SqlSever

MySQL常见的命令

  1. 查看当前所有的数据库： show databases;

  2. 打开指定的数据库：use 库名;

  3. 查看当前数据库的所有表：show tables;

  4. 查看其他数据库的所有表：show tables from 库名;

  5. 创建表：create table 表名(

    ​ 列名1 数据类型1,

    ​ 列名2 数据类型1,

    ​ ......

    ​ 列名n 数据类型n

    );

    注意列名唯一，数据类型可以相同

  6. 查看表的结构：desc 表名;

  7. mysql5.6.5之后更新root密码操作：update mysql.user set authentication_string=PASSWORD('你的新密码') where USER='root';

    mysql5.6.5之前更新root密码操作：update user set Password=PASSWORD('你要重置的新密码') where user='root';

    更新后记得刷新权限表：flush privileges;

    上边的操作都是登录mysql之后才可以执行的

MySQL语法规范

  1. 不区分大小写，但建议关键字大写，表名、列名小写

  2. 每条命令最好以分号结尾

  3. 每条命令根据需要可进行缩进、换行

  4. 注释方式：

    单行注释： #注释内容

    单行注释： --注释内容

    多行注释： /* 注释内容 */

SQL分类

image-20220618161719051

DDL语句--数据库、表、字段操作

DDL-数据库操作

  1. 查询

    查询所有数据库

    SHOW DATABASES;

    查询当前数据库

    SELECT DATABSE();

  2. 创建

    CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]; #中括号里的是可加可不加的，书写时不要写中括号

  3. 删除

    DROP DATABASE [IF EXISTS] 数据库名;

  4. 使用数据库

    USE 数据库名;
DDL-表操作-查询

  1. 查询当前数据库所有表

    SHOW TABLES;

  2. 查询表的结构

    DESC 表名;

    效果如下：

    image-20220618170047042

  3. 查询指定的建表语句

    SHOW CREATE TABLE 表名

    查询效果，即最初创建表 ‘表名’ 时，是如何创建的

    image-20220618165843955
DDL-表操作-建表
CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	字段3 字段3类型 [COMMENT 字段3注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]	#注意最后一个不加 逗号
)[COMMENT 表注释];

示例：

image-20220618165546196

DDL-表操作-数据类型
  1. 数值型

image-20220618170527946

精度：暂时记为整数位数

标度：暂时记为小数位数

  1. 字符串类型

    image-20220618171124617

  2. 日期时间类型

    image-20220618171520407

DDL-表操作-修改

  1. 添加字段

    ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

  2. 修改字段数据类型

    ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

  3. 修改字段名和字段类型

    ALERT TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释][约束];

  4. 修改表名

    ALTER TABLE 表名 RANAME 新表名;
DDL-表操作-删除

  1. 删除表

    DROP TABLE [IF EXISTS] 表名;

  2. 删除指定的表，并重新创建该表

    TRUNCATE TABLE 表名;

DML语句--表中的数据增删改

DML-添加数据

  1. 给指定字段添加数据

    INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); #前边有多少字段，后边要有多少数据，前边的字段不一定包含这张表的所有字段

  2. 给全部字段添加数据

    INSERT INTO 表名 VALUES (值1, 值2, ...);#此语法，如果对应表有 n 个字段，则values后的值，必须有 n 个且与各个字段对应

  3. 批量添加数据

    INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES(值1, 值2, ...)(值1, 值2, ...)(值1, 值2, ...); #此语法可以对应 1，变成插入多条

    INSERT INTO 表名 VALUES (值1, 值2, ...)(值1, 值2, ...)(值1, 值2, ...); #此语句对应 2，变成插入多条

注意！

  • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
  • 字符串和日期类型数据应该包含在引号中。
  • 插入的数据大小，应该在字段规定的范围内。
DML-修改数据
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件]; #后面只可以跟 0 或 1个条件
DML-删除数据
DELETE FROM 表名 [WHERE 条件]; #不可以只删除某个字段的值，如性别，条件只可以为 0 或 1 个

DQL--查询数据库中表的记录

SELECT 字段列表
FROM 表名
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数

DQL语句的执行顺序：

​ 先 FROM 确定表名，再WHERE初步筛选出结果，再将初步筛选的结果根据GROUP BY进行分组

​ 然后SELECT选择返回哪些字段的内容，最后再ORDER BY， LIMIT

DQL-基本查询
  1. 查询多个字段
SELECT 字段1, 字段2, 字段3,... FROM 表名; # 返回该表上述字段的所有值
SELECT * FROM 表名;	# *表示所有字段
  1. 设置别名
SELECT 字段2 [AS 别名2], 字段2 [AS 别名2], ... FROM 表名; #其中 AS 可以省略，即直接跟别名，别名需要包含在引号内
  1. 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
DQL-条件查询
  1. 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
  1. 条件

image-20220620085751393

# IN 的使用
WHERE age IN(18,19,20); # 只要age满足 IN后的列表中任意一个即可被查找到

#LIKE 的使用，有两种的占位符，- 和 %，一个 - 仅占一个字符， % 占用任意多个
WHERE idcard like '__'; # 找idcard 长度为 2 的
WHERE idcard like '%123456%'; #可以查找 idcard 中间某段为123456的
DQL-聚合函数
  1. 介绍：将一列数据看作一个整体，进行竖着计算
  2. 常见的聚合函数
image-20220620094305399
  1. 语法
SELECT 聚合函数(字段列表) FROM 表名;

注意：null值不参与聚合运算

DQL-分组查询

将查询结果以某依据分成若干组，在对结果进行操作

  1. 语法
SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

# 例子
select workaddress, count(*) from emp where age < 45 group by workaddress;
#如果使用group by的话，select 后跟的字段要与 group by 后面的一致， 否则没有意义

# 注意！
select gender, count(gener) from emp;
select gender, count(*) from emp;
# 都只会返回一行两列，内容是第一个出现的 1行1列为：第一个出现的gender 1行2列为：gender不为null的记录条数
  1. where 与 having 的区别
  • 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是依据 ‘分组字段名’ 对满足where的结果进行分组
  • 判断条件不同：where不可以对聚合函数进行判断，而having可以。对于这一点的理解：使用聚合函数后，结果中会出现一个新的列，即聚合函数列，比如使用count后，结果中会显示count列。having是对结果进行分组的，结果中有count列，因此可以对聚合函数进行判断

注意：

  • 执行顺序 where > 聚合函数 > having
  • 分组查询的字段一般为聚合函数字段和分组字段，查询其他字段无意义
DQL-排序查询
  1. 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式, 字段2 排序方式...;
  1. 排序方式
  • ASC：升序（默认值）
  • DESC：降序

对应单词，asc -- ascend， desc -- descend

注意：如果OREDER BY 后跟多个字段，则只要前面的字段相同时，才会根据后一个字段排序

DQL-分页查询
  1. 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录条数;

注意

  • 起始索引从 0 开始，起始索引 = （查询页码 - 1）* 每页显示记录数（查询记录条数）
  • 分页查询是数据库的方言，不同的数据库管理系统语法不一样
  • 如果查询的是第一页数据，则起始索引 0 可以省去

DCL-数据控制语言

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

DCL-管理用户
  1. 查询用户
USE mysql;	#因为mysql数据库的所有用户信息都是存储在 mysql库中的 user表
SELECT * FROM user;
  1. 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; #主机名是指，在哪一个主机上，这个用户可以访问mysql,@两边不可以用空格隔开
CREATE USER '用户名'@'%' IDENTIFIED BY '密码'; #表示在任意主机上都可以访问， % 在mysql中代表，任意字符
  1. 修改用户名密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码'; #@两边不可以用空格隔开
  1. 删除用户
DROP USER '用户名'@'主机名'; #@两边不可以用空格隔开
DCL-权限控制

MySQl中定义了很多用户权限，主要有以下几种

image-20220623165158631
  1. 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
  1. 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; # 数据库名.表名写成 *.*表示所有库，所有表，数据库名.*, 表示该数据库所有表
  1. 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意

  • 多个权限之间，使用逗号分隔
  • 授权时，数据库名和表名可以使用 * 进行通配，代表所有

函数

字符串函数

image-20220623170746458

函数演示

SELECT CONCAT('Hello', 'World', '!');
# 结果 HelloWorld！
SELECT LOWER('ABCADdasaw');
# 结果 abcaddasaw
SELECT UPPER('ABCADdasaw');
# 结果 ABCADDASAW
SELECT LPAD('123', 10, 'ab');
# 结果 abababa123， 注意最后一次填充只填充了 'a'
SELECT RPAD('123', 10, 'ab');
# 结果 123abababa， 注意最后一次填充只填充了 'a'
SELECT TRIM('   Hello     ');
# 结果 Hello
SELECT SUBSTRING('Hello World', 1, 7);
# 结果 Hello W

练习：由于业务需求，企业员工的工号，统一为5位数，目前不足五位数的全部在前面补0。比如：1号员工的工号为 00001。

#用此语句对 emp表进行更新
UPDATE emp SET workno = LPAD(workno, 5 '0');

数据值函数

image-20220623172747780

函数演示

SELECT CEIL(1.5);
# 结果 2
SELECT FLOOR(1.5);
# 结果 1
SELECT MOD(5, 2);
# 结果 1
SELECT RAND();
# 结果 0~1的随机数
SELECT ROUND(1.12345678, 3);
# 结果 1.123
SELECT ROUND(RAND(), 3);
# 结果 0~1的随机数, 保留 3 位小数

#生成 6 位随机验证码
SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0');

日期函数

image-20220623173823146

函数演示

SELECT CURDATE();
# 结果 当前日期	xxxx-xx-xx
SELECT CURTIME();
# 结果 当前时间	xx-xx-xx
SELECT NOW();
# 结果 当前的日期和时间 xxxx-xx-xx xx-xx-xx
SELECT YEAR(NOW());
# 结果 当前年份	xxxx
SELECT MONTH(NOW());
# 结果 当前月份	没有前补0，即如果是6月，返回6
SELECT DAY(NOW());
# 结果 当前日	同样没有前补0
SELECT DATE_ADD(date, INTERVAL expr type);
# 结果 date + expr 后的日期
SELECT DATEDIFF(date1, date2);
# 结果 date1 - date2 之间的天数，如果date1 < date2则返回为负数

案例：查询emp表中的员工，按照入职天数倒序排序

SELECT name, DATEDIFF(CURDATE(), entrydate) FROM emp ORDER BY DATEDIFF(CURDATE(), entrydate) desc;

结果

image-20220623180216611

流程控制函数

image-20220623181102690

函数演示

SELECT IF(true, 'OK', 'ERROR');
# 结果 OK
SELECT IF(false, 'OK', 'ERROR');
# 结果 ERROR
SELECT IFNULL('', 'default');
# 结果 default
SELECT IFNULL('OK', 'default');
# 结果 OK
SELECT CASE WHEN true THEN 'dawd' WHEN true THEN 'dasdd' ELSE 'ghhdhd' END;
# 结果 dawd
SELECT CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END;
# expr是列名，当列内容 = val1返回 res1，当列内容 = val2 返回 res2，如果都不相同，返回default
SELCET CASE workaddress WHEN '北京' or '上海' THEN '一线城市' ELSE '二线城市' END;

约束

  1. 概念：约束是作用于表中字段的规则，用于限制存储在表中的数据。
  2. 目的：保证数据库中数据的正确、有效性和完整性。
  3. 分类：
image-20220625220830895

AUTO_INCREMENT 自动增长

案例演示：创建一张表，AUTO_INCREMENT满足下列条件

image-20220625221927557 
# 案例代码
CREATE TABLE tb_user(
    id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
    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 '性别'
);

# 另一个案例
create table volunteers(
	id int PRIMARY KEY auto_increment COMMENT 'id',
	name varchar(5) not null comment '姓名',
	age int CHECK(age > 0 && age <= 120) COMMENT '年龄',
	job varchar(5) check(job = '主席'|| job = '部长' || job = '部员' || job = '退休') COMMENT '职位', #####注意此行check写法
	dept_id int check(dept_id <= 5) COMMENT '所属部门id'
) COMMENT '志愿者信息';

外键约束

概念：用来让两张表之间建立连接，从而保证数据的一致性和完整性。

语法：

  1. 在创建表的时候
CREATE TABLE 表名(
	字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键表名) REFERENCES 主表(主表列名)
);
  1. 表创建完成后再添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

#例子
ALTER TABLE volunteers ADD CONSTRAINT f_key FOREIGN KEY ( dept_id ) REFERENCES dept ( id ); ##这个是可以运行的，括号不可以省略
  1. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;	#表名是在哪个表创建的此外键的表 （子表）

当外键的主表发生 删除/更新操作时，与之绑定的子表对应的列如何变化

image-20220625233506878

语法

ALTER TABLE volunteers ADD CONSTRAINT f_key FOREIGN KEY ( dept_id ) REFERENCES dept ( id ) [ON UPDATE 行为][ON DELETE 行为]
#当主表进行操作时，子表会执行的操作

多表关系介绍

  1. 一对多关系（多对一）

    • 案例：部门与员工之间的关系

    • 关系：一个员工只能属于一个部门，一个部门对应多个员工

    • 实现：在多的一方建立外键，指向一的一方的主键

      image-20220626105204096

  2. 多对多关系

    • 案例：学生 与 课程 的关系
    • 关系：一个学生可以选多门课程，一个课程也可以拥有多个学生
    • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
    image-20220626105447569

  3. 一对一关系

    • 案例：用户 与 用户详情的关系
    • 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，详情字段放入另一张表中，以提升操作效率
    • 实现：在任意一方加入外键，关联另一方的主键，并且外键为唯一的（UNIQUE）

image-20220626105701555image-20220626105737357

image-20220626105701555image-20220626105737357

多表查询概述

概述：从多张表中查找数据

SELECT * FROM 表名1, 表名2, ...; # 查询表1、表2、...
# 但是这样查询会出现问题，即笛卡尔乘积现象，如果表1有 10条数据，表2有12条数据，只查询表1和表2的话，结果会有 10 * 12 = 120条

笛卡尔积：笛卡尔乘积是指在数学中，A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

image-20220626113838333

可以通过增加where条件消除

SELECT * FROM 表1, 表2 WHERE 表1.列名 = 表2.列名;
多表查询的分类
  • 连接查询
    • 内连接：相当于查询A、B交集部分数据
    • 外连接
      • 左外连接：查询左表所有数据，以及两张表的交集部分数据
      • 右外连接：查询右表所有数据，以及两张表的交集部分数据
    • 自连接：当前表与自身的连接查询，自连接必须使用表的别名（即当前只有一张表，这个表需要与自身连接进行查询）
  • 子查询
image-20220626114436396
内连接

内连接是查找两张表相交的部分

内连接语法：

  1. 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
# 可以在FROM，给表起别名，但是注意，起别名后，此语句只能以别名的形式操作表
# 如语句
SELECT e.name, d.name FROM emp e, dept d where e.dept_id = d.id;
  1. 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
# INNER 关键字可以省略
外连接
  1. 左外连接语法
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
# 查询效果，显示查询的所有属于左表的内容，同时显示与右表相交的右表信息，如果左表中某条的信息不与右表相交，则对应右表信息字段显示为 null
  1. 右外连接语法
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
#查询效果与左表相反，可以通过改变位置，用左外连接实现右外连接
自连接

自连接就是把一张表当成两张不同的表使用，可以是内连接查询，也可以是外连接查询

语法：

SELECT 字段名列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

案例：比如在下表中查询员工的领导是谁，但是员工并没有直接存储领导的名称，只存储了id，这个时候通过自连接查询找打对应的领导名字

image-20220626200104755

SELECT e.name as '员工', m.name as '领导' FROM emp e, emp m where e.managerid = m.id;

#
多表查询-联合查询union

对于union查询就是把多次查询的结果合并起来，形成一个新的表

SELECT 字段列表 FROM 表A
UNION [ALL]			## ALL 如果省略，则会自动删除重复的结果
SELECT 字段列表 FROM 表B;
UNION [ALL]
...

#注意 联合查询每个查询的 列的数目 必须相同，列名会以第一次查询的命名	(列类型最好相同，否则将以第一个表的确定)
子查询
  • 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
#子查询外部的语句可以是 INSERT/UPDATE/DELETE/SELECT 中的任意一个

  • 根据子查询的结果不同，分为：

    • 标量子查询（子查询结果为单个值，数字、日期、字符串等）
    • 常用的操作符： = <> > >= < <=
    # 需求：查询销售部的所有员工信息, dept 表存储部门信息，emp表存储员工信息
# a.查询销售部的 id
SELECT id FROM dept WHERE dept_name = '销售部';
# b.根据销售部的 id 查询员工信息
SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE dept_name = '销售部');
# 第二句即为子查询
# 因为 SELECT id FROM dept WHERE name = '销售部' ，返回结果只有一行一列，所以称为标量子查询
    • 列子查询（子查询结果为一列）
    • 常用的操作符：IN、NOT IN 、ANY、SOME、ALL
    image-20220628090434438 
    # 1.查询 ”销售部“ 和 ”市场部“ 的所有员工信息
# a.查询 ”销售部“ 和 ”市场部“ 的 id
SELECT id FROM dept WHERE dept_name = '销售部' or dept_name = '市场部';
# b.根据 id 查找对应员工的信息
SELECT * FROM emp WHERE id IN (SELECT id FROM dept WHERE dept_name = '销售部' or dept_name = '市场部');
#第二句的返回有有 两行一列，所以也叫列子查询

# 2.查询 比财务部所有人工资都高的员工信息
-- a.查询财务部所有人的工资
SELECT id FROM dept WHERE dept_name = '财务部';

SELECT salary FROM emp WHERE id = (SELECT id FROM dept WHERE dept_name = '财务部');
-- b.查询工资大于财务部所有人的员工信息
SELECT * FROM emp WHERE salary > ALL(SELECT salary FROM emp WHERE id = (SELECT id FROM dept WHERE dept_name = '财务部'));

# 此处有疑问：ALL是否可以直接替换成 聚合函数MAX，直接替换是错的（已尝试）
    • 行子查询（子查询结果为一行）
    • 常用的操作符： = 、<>、IN、NOT IN
    -- 1.查询与 张无忌的工资及直属领导相同的员工信息
-- a.查询 张无忌的工资及其直属领导id
SELECT salary,managerid FROM emp WHERE name = '张无忌';

-- b.查询 与”张无忌“工资及直属领导相同的员工信息
SELECT * FROM emp WHERE salary = 12500 and managerid = 1;
# 为了使用子查询，上述语句可以写为
SELECT * FROM emp WHERE (salary, managerid) = (12500, 1);
#对应子查询语句为
SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary,managerid FROM emp WHERE name = '张无忌');
    • 表子查询（子查询结果为多行多列）
    • 常用的操作符：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') as e left join dept as d on e.dept_id = d.id;

  • 根据子查询的位置，分为：WHERE之后、FROM之后、SELECT之后。

练习

image-20220628214134766

USE test;
-- 1
SELECT emp.`name`,emp.age, emp.job, dept.name FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;
-- 2
SELECT e.`name`,e.age, e.job, dept.name FROM (SELECT * FROM emp WHERE age < 30) e LEFT JOIN dept ON e.dept_id = dept.id;
-- 3
SELECT b.id, b.name FROM dept b WHERE b.id IN (SELECT DISTINCT dept_id FROM emp);
-- 4
SELECT e.*,d.name FROM (SELECT * FROM emp WHERE age > 40) e LEFT JOIN dept d ON e.dept_id = d.id;
# 5.空
# 6.空
-- 7
SELECT AVG(salary) FROM emp WHERE dept_id = (SELECT id FROM dept WHERE 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 使用GROUP BY 不够简洁，可以使用自连接
SELECT e.name, d.dept_id FROM emp e, (SELECT dept_id, AVG(salary) av_s FROM emp GROUP BY dept_id ORDER BY dept_id ASC) d WHERE e.salary < d.av_s && e.dept_id = d.dept_id;
-- 11 SELECT后使用子查询会更简洁
SELECT dept.*, e.c FROM (SELECT dept_id, count(dept_id) c FROM emp GROUP BY dept_id) e, dept WHERE e.dept_id = dept.id;

事务

事务简介

事务时一组操作的集合，它是一个不可分割的工作单位，事务会把操作作为一个整体向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。例如转账操作，从账户 A 转出 x 元到账户 B，首先会查询 A 中余额是否超过 x ，如果超过则 A 余额减x， B余额加x。

事务操作

  1. 查看/设置事务提交方式
SELECT @@autocommit;	//查看	@@表示系统变量
SET @@autocommit = 0;	//设置为 0 不自动提交， 1 自动提交
  1. 提交事务
COMMIT;
  1. 回滚事务
ROLLBACK;
  1. 开启事务
START TRANSACTION 或 BEGIN;

使用事务的两种方式

  • 方式1：通过SET @@autocommit 设置为手动提交，再配合commit提交事务，rollback回滚事务
  • 方式2：不需要设置@@autocommit，使用前先 START TRANSACTION 或 BEGIN 开启事务，再配合commit，rollback使用

事务的四大特性

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

并发事务问题

问题

  • 脏读：一个事务读到另一个事务还没提交的事务
  • 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称为不可重复读（因为两次查询之间，并发的事务对数据进行了更新）
  • 幻读：一个事务按照条件查找数据时，没有对应行的数据，但是在插入时，又发现这行数据已经存在，好像出现了“幻影”

事务隔离级别

√ 表示此隔离级别下可能发生的问题

-- 查看隔离级别的方法
SELECT @@TRANSACTION_ISOLATION;

-- 设置事务的隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

-- SESSION 代表当前客户端的窗口有效		GLOBAL代表所有客户端的窗口有效

进阶篇

存储引擎

MySQl体系结构

  • 连接层

    最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

  • 服务层

    第二层架构主要是完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。

  • 引擎层

    存储引擎真正的负责了MySQl中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要选取合适的存储引擎。

  • 存储层

    主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

  1. 创建表时，指定特定的存储引擎
CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]
)ENGINE = INNODB [COMMENT 注释];
  1. 查看当前数据库支持的存储引擎
SHOW ENGINES;
posted @ 2022-07-27 19:58  伍六柒-  阅读(92)  评论(0)  编辑  收藏  举报