数据库相关概念
数据库(DataBase,DB),存储数据的仓库,数据是有组织的存储。
数据库管理系统(DataBase Management System,DBMS),操纵和管理数据库的大型软件。
SQL(Structured Query Language),操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准。
MySQL数据库
MySQL数据库是关系型数据库。
关系型数据库(RDBMS),建立在关系模型基础上,由多张相互连接的二维表组成的数据库。特点:
1.使用表存储数据,格式统一,便于维护
2.使用SQL语言操作,标准统一,使用SQL
SQL
SQL通用语法
1.SQL语句可以单行或者多行书写,以分号结尾。
2.SQL语句可以使用空格/缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
4.MySQL注释:
- 单行注释:--注释内容 或 # 注释内容
- 多行注释:/* 注释内容 */
SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Denfinition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
DDL
数据类型
MySQL中数据类型主要分为三类,数值类型、字符串类型、日期时间类型。
数据库操作
1.查询
查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE ();
2.创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
create database Student;# 创建一个Students数据库
create database if not exists Student;# 若Student已存在,则不用再创建;若不存在,则创建
create database Student default charset utf8mb4;# 创建一个Student数据库并指定字符集为utf8mb4
3.删除
DROP DATABASE [IF EXISTS] 数据库名;
drop database if exists Student;# 若Student不存在,则不删除;若存在,则删除
4.使用
USE 数据库名;
表操作
1.查询
查询当前数据库所有表
SHOW TABLES;
查询表结构
DESC 表名;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
2.创建
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
......
字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];
注意:最后一个字段没有逗号
例如,创建如下一个表
id | name | major | grade |
---|---|---|---|
1 | 张三 | 计算机科学与技术 | 2022 |
2 | 李四 | 石油工程 | 2020 |
3 | 王五 | 应用数学 | 2024 |
create table students(
id int comment '编号',
name varchar(50) comment '姓名',
major varchar(50) comment '专业',
grade int comment '年级'
)comment '学生表';
3.修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
alter table students add student_id int comment '学号';
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
alter table students modify student_id varchar(10);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
alter table students change id student_id int;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
4.删除
删除字段
ALTER TABLE 表名 DROP 字段名;
删除表
DROP TABLE [IF EXISTS] 表名;
删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
注意:删除表时,表中全部数据也会被删除。
DML
添加数据(INSERT)
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, ...);
注意:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。
修改数据(UPDATE)
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件];
update students set student_id = 2022060233 where id = 1;
注意:修改语句的条件可以有,也可以没有。如果没有条件,修改的是整张表的数据。
删除数据(DELETE)
DELETE FROM 表名 [WHERE 条件];
注意:
- 删除语句的条件可以有,也可以没有。如果没有条件,删除的是整张表的数据。
- 删除语句不能删除某一个字段的值(使用UPDATE语句)。
DQL
查询关键字:SELECT
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数
执行顺序 :FROM -> WHERE -> GROUP BY -> SELECT -> HAVING / ORDER BY -> LIMIT
基本查询
1.查询多个字段
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;
2.设置别名
SELECT 字段1 [AS] 别名1, 字段2 [AS] 别名2, ... FROM 表名; # AS可省略
3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
条件查询(WHERE)
SELECT 字段列表 FROM 表名 WHERE 条件列表;
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN...AND... | 在某个范围之内(含最大、最小值) |
IN(...) | 在in之后的列表中的值,多选一 |
LIKE 占位符 | 模糊匹配('_':匹配单个字符,'%':匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意成立一个) |
NOT 或 ! | 非,不是 |
聚合函数(COUNT,MAX,MIN,AVG,SUM)
SELECT 聚合函数(字段列表) FROM 表名;
将一列数据作为一个整体,进行纵向计算。
函数 | 功能 |
---|---|
COUNT | 统计数量 |
MAX | 最大值 |
MIN | 最小值 |
AVG | 平均值 |
SUM | 求和 |
分组查询(GROUP BY)
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组过滤后条件];
WHERE与HAVING的区别:
- 执行实际不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,但having可以。
排序查询(ORDER BY)
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式:
- ASC:升序(默认值)
- DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段值进行排序。
分页查询(LIMIT)
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:
- 起始索引从0开始,起始索引=(查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10。
DCL
管理用户
1.查询用户
USE mysql;
SELECT * FROM user;
2.创建用户
CRENTE USER '用户名'@'主机名' IDENTIFIED BY '密码';
3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码';
4.删除用户
DROP USER '用户名'@'主机名';
权限控制
MySQL中常用的权限
权限 | 说明 |
---|---|
ALL,ALL PRIVILEGES | 所有权限 |
SELLECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
1.查询权限
SHOW GRANTS FOR '用户名'@'主机名';
2.授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3.撤销权限
REVOKE 权限列表 ON 数据库名. 表名 FROM '用户名'@'主机名';
函数
函数是指一段可以被直接调用的程序和代码。
字符串函数
函数 | 功能 |
---|---|
CONCAT(S1, S2, ..., Sn) | 字符串拼接,将S1,S,...,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个长度的字符串 |
数值函数
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x, y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x, y) | 求参数x的四舍五入的值,保留y位小数 |
日期函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1, date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
函数 | 功能 |
---|---|
IF(value, t, f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1, value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [val1] THEN [res1] ...ELSE [defult] END | 如果val1为true,返回res1,...否则返回default默认值 |
CASE [expr] WHEN [val1] THEN [res1] ...ELSE [defult] END | 如果expr的值等于val1,返回res1,...否则返回default默认值 |
约束
约束时作用于表中字段上的规则,用于限制存储在表中的数据。其目的是保证数据库中数据的正确性、有效性和完整性。常见的约束有以下几种:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束 | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREING KEY |
约束演示
案例
字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
---|---|---|---|---|
id | ID唯一标识 | int | 主键,并且自动增长 | PRIMARY KEY, AUTO_INCREMENT |
name | 姓名 | varchar(10) | 不为空,并且唯一 | NOT NULL, UNIQUE |
age | 年龄 | int | 大于0,并且小于等于120 | CHECK |
statues | 状态 | char(1) | 如果没有指定该值,默认1 | DEFAULT |
gender | 性别 | char(1) | 无 | \ |
create table user(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check ( age>0 and age<=120 ) comment '年龄',
statues char(1) default '1' comment '状态',
gender char(1) comment '性别'
)comment '用户表';
外键约束
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。具有外键的表称为子表,外键关联的表称为父表。
添加外键
CREATE TABLE 表名(
字段 数据类型,
......
[CONSTRAINT] [外键名] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
删除/更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父亲中删除/更新对应记录时,首先检查记录是否有对应外键,如果有,则不允许删除/更新。 |
RESTRICT | 当在父亲中删除/更新对应记录时,首先检查记录是否有对应外键,如果有,则不允许删除/更新。 |
CASCAND | 当在父亲中删除/更新对应记录时,首先检查记录是否有对应外键,如果有,则也允许删除/更新。 |
SET NULL | 当在父亲中删除对应记录时,首先检查记录是否有对应外键,如果有则设置子表中该外键值为null(要求允许外键为null)。 |
SET DDEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(innodb不支持)。 |
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) [ON UPDATE 行为] [ON DELETE 行为];
多表查询
多表查询就是指从多张表中查询数据。
那么我们要执行多表查询,就只需要使用逗号分隔多张表即可,如:
select * from emp , dept;
查询结果中包含了表emp所有的记录与表dept所有记录的所有组合情况,这种现象称之为笛卡尔积。
笛卡尔积: 笛卡尔乘积是指在数学中,两个集合 A 集合 和 B 集合的所有组合情况。
在多表查询中,我们是需要消除无效的笛卡尔积,只剩下有效的数据,只保留两张表关联部分的数据。
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
一对多(多对一)
案例: 部门 与 员工的关系
关系: 一个部门对应多个员工,一个员工对应一个部门
实现: 在多的一方建立外键,指向一的一方的主键
多对多
案例: 学生 与 课程 的关系
关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
一对一
案例: 用户 与 用户 详情的关系
关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
多表查询分类
连接查询
内连接
内连接查询的是两张表的交集部分。
隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。
自连接
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
而对于自连接查询,可以是内连接查询,也可以是外连接查询。
联合查询-union,union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
注意:
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:
- WHERE之后
- FROM之后
- SELECT之后
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符:= <> > >= < <=
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:= 、<> 、IN 、NOT IN
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
事务
概述
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
比如: 张三给李四转账1000块钱,张三银行账户的钱减少1000,而李四银行账户的钱要增加 1000。 这一组操作就必须在一个事务的范围内,要么都成功,要么都失败。事先规定两人均有2000元。
正常情况: 转账操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少1000, 而李四增加1000, 转账成功 :
- 查询张三账户余额
- 张三账户余额-1000
- 李四账户余额+1000
异常情况: 转账操作, 也是分为以下三步来完成 , 但在执行第三步时报错了, 这样就导致张三减少1000块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。
- 查询张三账户余额
- 张三账户余额-1000
- (出现异常,导致下面的命令无法实施)
- 李四账户余额+1000
为了解决上述的问题,就需要通过数据的事务来完成,我们只需要在业务逻辑执行之前(手动)开启事务,执行完毕后(手动)提交事务。如果执行过程中报错,则(手动)回滚事务,把数据恢复到事务开始之前的状态。
注意: 默认MySQL的事务是自动提交的,也就是说,当执行完一条DML语句时,MySQL会立即隐式的提交事务。
事务操作
数据准备
#如事先自己设置了account表,将其先删除
drop table if exists account;#创建account表
create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余额'
) comment '账户表';#向表里面添加数据
insert into account(name, money) VALUES ('张三',2000), ('李四',2000);
未控制事务
1. 测试正常情况
# 1. 查询张三余额
select * from account where name = '张三';
# 2. 张三的余额减少1000 (使用更新语句update来写)
update account set money = money - 1000 where name = '张三';
# 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的!
2. 测试异常情况
在测试之前,我们先把数据恢复到每人两千的情况下。
update account set money = 2000 where name = '张三' or name = '李四';
#勾选命令执行的时候要从上往下走嗷,不然可能会出现数据错误的情况
# 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 = '李四';
然后再次执行上述的SQL语句(出错了… 这句话不符合SQL语法,执行就会报错),检查最终的数据情况, 发现数据在操作前后不一致了。
控制事务一
1. 查看/设置事务提交方式
#以下命令仅针对当前窗口有效,即console窗口,数据表的窗口是没有反应的
SELECT @@autocommit ;#查看事务的自动提交方式,看是否是自动提交,是1则自动提交,是0则手动提交
#可以通过set命令将提交方式改成手动的
SET @@autocommit = 0 ;
2. 提交事务
#得commit后,数据才会在数据表的窗口有反应
COMMIT;
3. 回滚事务
#为了不让事务的数据出错,当我们输出命令输入错误的时候,使用rollback回滚,就不会对数据造成影响
ROLLBACK;
注意:上述的这种方式,我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。
控制事务二
再实现下面三个命令前,先把自动提交给打开,将“SET @@autocommit = 1 ;”输入进去即可。
1. 开启事务
START TRANSACTION 或 BEGIN ;
2. 提交事务
COMMIT;
3. 回滚事务
ROLLBACK;
案例:
-- 开启事务(这条命令相当于为手动提交)
start transaction;
-- 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 = '李四';
#由于 start transaction这条命令是提交方式变成手动提交,因此在这里刷新数据表的时候,是不会看到数据有所变化的
报错后查询数据表,结果如下:
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
rollback;
事务四大特性
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
- 上述就是事务的四大特性,简称ACID。
并发事务问题
比较常见由脏读、不可重复读和幻读。
1.脏读
含义:一个事务读到另外一个事务还没有提交的数据。
比如:B读取到了A未提交的数据。
2. 不可重复读
含义:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
比如:事务A两次读取同一条记录,但是读取到的数据却是不一样的。
3. 幻读
含义:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 “幻影”。
事务隔离级别
为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。主要有以下几种:
1. 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
2. 设置事务隔离级别
#以下命令均不区分大小写!
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
查看与设置事务隔离级别案例:
# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
#设置事务隔离级别为read uncommitted
set session transaction isolation level read uncommitted ;# 再次查看事务隔离级别,看是否改成了read uncommitted
SELECT @@TRANSACTION_ISOLATION;
#设置事务隔离级别为repeatable read
set session transaction isolation level repeatable read ;# 再次查看事务隔离级别,看是否改成了repeatable read
SELECT @@TRANSACTION_ISOLATION;