MySQL数据模型
关系型数据库(RDBMS):建立在关系模型基础上,由多张相互连接的二维表组成的数据库。
特点:
使用表存储数据,格式统一,便于维护
使用SQL语言操作,标准统一,使用方便,可用于复杂查询
SQL简介
SQL:一门操作关系型数据库的编程语言,定义操作所有关系型数据库的统一标准。
通用语法
SQL语句可以单行或多行书写,以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写。
注释:
1.单行注释:-- 注释内容 或 # 注释内容(MySQL特有)
2.多行注释: /* 注释内容 */
SQL
分类 | 全称 | 说明 |
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
DDL
数据库操作
查询所有数据库:show databases;
查询当前数据库:select database/schema();
使用数据库:use 数据库名 ;
创建数据库:create database/schema [ if not exists ] 数据库名 ;
删除数据库:drop database/schema [ if exists ] 数据库名 ;
表操作
create table 表名(
字段1 字段类型 [ 约束 ] [ comment 字段1注释 ] ,
......
字段n 字段类型 [ 约束 ] [ comment 字段n注释 ]
) [ comment 表注释 ] ;
约束
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确性、有效性和完整性。
约束 | 描述 | 关键字 |
非空约束 | 限制该字段值不能为null | not null |
唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key (auto_increment自增) |
默认约束 | 保存数据时,如果未指定该字段值,则采用默认值 | default |
外键约束 | 让两张表的数据建立连接,保证数据的一致性和完整性 | foreign key |
-- 创建student表包含id, name, birthday字段
create table tb_student(
id int primary key auto_increment,
name varchar(20) unique not null ,
birthday varchar(30)
);
查询当前数据库所有表:show tables;
查询表结构:desc 表名;
查询建表语句:show create table 表名;
添加字段:alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
修改字段类型:alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型:alter table 表名 change 旧字段名 新字段名 类型 (长度) [comment 注释] [约束];
删除字段:alter table 表名 drop column 字段名;
修改表名: rename table 表名 to 新表名;
删除表:drop table [ if exists ] 表名;
DML
Insert
指定字段添加数据:insert into 表名 (字段名1, 字段名2) values (值1, 值2);
全部字段添加数据:insert into 表名 values (值1, 值2, ...);
批量添加数据(指定字段):insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);
批量添加数据(全部字段):insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);
1.插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
2.字符串和日期型数据应该包含在引号中。
3.插入的数据大小,应该在字段的规定范围内。
-- 插入数据,所有的字段名都写出来 insert into student(id【主键自增】, name, birthday, sex, address) values (null, '蔡徐坤', '2023-09-05', '鸡', '广州'); -- 插入数据,插入所有字段可以不写字段名 insert into student values (null, '真爱粉', '2023-09-05', '无', '深圳'); -- 插入部分数据,往学生表中添加 部分数据 insert into student(name, address) values ('小黑子', '中国纽约');
Update
修改数据:update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ] ;
修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
-- 不带条件修改数据,将所有的性别改成女 update student set sex = '女'; -- 带条件修改数据,将id号为1的学生性别改成男 update student set sex = '男' where id = 1; -- 一次修改多个列,把id为3的学生,生日改成1988-08-08,address改成北京 update student set birthday = '1988-08-08',address = '广州' where id = 3;
Delete
删除数据:delete from 表名 [ where 条件 ];
1.DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
2.DELETE 语句不能删除某一个字段的值(如果要操作,可以使用UPDATE,将该字段的值置为NULL)。
-- 带条件删除数据,删除id为3的记录 delete from student where id = 3; -- 不带条件删除数据,删除表中的所有数据 delete from student;
DQL
基本查询
查询多个字段:select 字段1, 字段2, 字段3 from 表名;
查询所有字段(通配符):select * from 表名;
设置别名:select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
去除重复记录:select distinct 字段列表 from 表名;
* 号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)。
-- 查询student表中所有字段 select id, name, age, sex, address, math, english from student; -- 查询所有字段,使用*代表所有列 select * from student; -- 查询student表中的 name 和 sex 列 select name, sex from student; -- 查询sudent表中name 和 sex 列, -- name列的别名为 姓名,sex列的别名为 性别 select name as '姓名', sex 性别 from student; -- 取别名时AS关键字可以省略 -- 查询address列并且结果不出现重复的address select distinct address from student;
条件查询
条件查询:select 字段列表 from 表名 where 条件列表 ;
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between ... and ... | 在某个范围之内(含最小、最大值) |
in(...) | 在in之后的列表中的值,多选一 |
like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
is null | 是null |
逻辑运算符 | 功能 |
and 或 && | 并且 (多个条件同时成立) |
or 或 || | 或者 (多个条件任意一个成立) |
not 或 ! | 非 , 不是 |
-- 查询math分数大于80分的学生 select id, name, age, sex, address, math, english from student where math >= 80; -- 查询english分数小于等于90分的学生 select id, name, age, sex, address, math, english from student where english <= 90; -- 查询age不等于20岁的学生 select id, name, age, sex, address, math, english from student where age != 20; -- 查询英语成绩不是null的 select id, name, age, sex, address, math, english from student where english is not null ; -- 逻辑运算符 -- 查询age大于35且性别为男的学生(两个条件同时满足) select id, name, age, sex, address, math, english from student where age > 35 and sex = '男'; -- 查询age大于35或性别为女的学生(两个条件其中一个满足) select * from student where age > 35 or sex = '女'; -- 查询id是1或3或5的学生 select id, name, age, sex, address, math, english from student where id in(1, 3, 5); -- in: 在...里面,只要是满足()里面的数据都可以 -- 查询id是1或3或5的学生 -- 查询id不是1或3或5的学生 select id, name, age, sex, address, math, english from student where id not in(1, 3, 5); -- 范围: BETWEEN 值1 AND 值2 -- 表示从值1到值2范围,包头又包尾 -- 查询english成绩大于等于75,且小于等于90的学生 select * from student where english between 75 and 90; -- 注意: between 值1 and 值2, 小的写前面大的写后面 -- 模糊查询like -- 查询姓马的学生: 第一个是马,后面无所谓 select * from student where name like '马%'; -- 查询姓名中包含'德'字的学生 select id, name, age, sex, address, math, english from student where name like '%德%'; -- 查询姓马,且姓名有三个字的学生 select * from student where name like '马__';
分组查询
聚合函数
介绍:将一列数据作为一个整体,进行纵向计算。
语法:select 聚合函数(字段列表) from 表名 ;
函数 | 功能 |
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
注意
null值不参与所有聚合函数运算。
统计数量可以使用:count(*) count(字段) count(常量),推荐使用count(*)。
分组查询:
select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];
where和having区别
1.执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
2.判断条件不同:where不能对聚合函数进行判断,而having可以。
注意
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
执行顺序: where > 聚合函数 > having 。
-- 查询男女各多少人 -- 1.按照性别分为男女两组 -- 2.统计每组的人数 select sex, count(sex) from student group by sex; -- 查询年龄大于25岁的人,按性别分组,统计每组的人数 select sex, count(sex) from student where age > 25 group by sex; -- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据 -- 并只显示性别人数大于2的数据分组后的条件, -- 分组后的条件使用having select sex, count(sex) from student where age > 25 group by sex having count(sex) > 2; -- 查询男女每组的数学总分 select sex, sum(math) from student group by sex; -- 查询男女每组的数学平均分 select sex, avg(math) from student group by sex; -- 查询男女每组的数学最高 select sex, max(math) from student group by sex; -- 查询男女每组的数学低 select sex, min(math) from student group by sex; -- 查询男女每组的数学最高分和英语最高分 select sex, max(math), max(english) from student group by sex;
排序查询
select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 … ;
ASC:升序(默认值)
DESC:降序
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
-- 排序 -- 单列排序 -- 查询所有数据,使用年龄升序排序 select * from student order by age asc ; -- 查询所有数据,使用年龄降序排序 select * from student order by age desc ; -- 组合排序 -- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序 select id, name, age, sex, address, math, english from student order by age desc , math desc ;
分页查询
:select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
1.起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
2.分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
3.如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
-- 查询学生表中数据,跳过前面2条,显示6条 select id, name, age, sex, address, math, english from student limit 2, 6; -- 假设我们一每页显示5条记录的方式来分页,SQL语句如下: -- 第一页: 跳过0条, 获取5条 select id, name, age, sex, address, math, english from student limit 5; -- 如果跳过的条数是0,可以省略 -- 第二页: 跳过5条, 获取5条 select id, name, age, sex, address, math, english from student limit 5, 5; -- 第三页: 跳过10条, 获取5条 select id, name, age, sex, address, math, english from student limit 10, 5;
函数
if(表达式, tvalue, fvalue):当表达式为true时,取值tvalue;当表达式为false时,取值fvalue
case expr when value1 then result1 [when value2 then value2 ...] [else result] end
-- 案例2 -- 男性与女性员工的人数统计 (1: 男性员工, 2: 女性员工) -- 函数: if(条件表达式 , true显示的值, false显示的值) select if(gender = 1, '男性', '女性'), count(gender) from emp group by gender; -- 案例3 -- 员工职位信息 -- 函数: case ... when ... then ... when ... then ... else ... end select (case job when 1 then 'UI' when 2 then 'java' when 3 then '前端' when 4 then '测试' else '运维' end ), count(job) from emp group by job;
多表设计
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
一对多(多对一)
一对多关系实现:在数据库表中多的一方,添加字段【外键】,来关联一的一方的主键。
-- 创建表时指定
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);
-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
-- 员工表(已有) create table tb_emp ( id int unsigned primary key auto_increment comment 'ID', username varchar(20) not null unique comment '用户名', password varchar(32) default '123456' comment '密码', name varchar(10) not null comment '姓名', gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女', image varchar(300) comment '图像', job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管', entrydate date comment '入职时间', dept_id int, 【外键关联tb_dept】 create_time datetime not null comment '创建时间', update_time datetime not null comment '修改时间' ) comment '员工表';-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
-- 部门表(需要设计) create table tb_dept( id int primary key auto_increment, name varchar(10) not null , create_time datetime, update_time datetime );
一对一
案例: 用户 与 身份证信息 的关系
关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率
实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
-- 用户基本信息表(需要设计) create table tb_user( id int primary key auto_increment, name varchar(20) not null , gender tinyint unsigned not null , phone char(11), degree varchar(5) ); -- 用户身份信息表((需要设计) create table tb_user_card( id int primary key auto_increment, nationality varchar(20) not null , birthday date , idcard char(18) not null , issued varchar(20) not null , expire_begin date, expire_end date, user_id int unique 【外键关联tb_user】 );-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
多对多
案例: 学生 与 课程的关系
关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
-- 学生表(已有) create table tb_student ( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', no varchar(10) comment '学号' ) comment '学生表'; -- 课程表(已有) create table tb_course ( id int auto_increment primary key comment '主键ID', name varchar(10) comment '课程名称' ) comment '课程表'; -- 学生课程中间表(需要设计) create table tb_student_course( id int primary key auto_increment, student_id int, 【外键关联tb_student】 course_id int 【外键关联tb_course】 );-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
多表查询
多表查询: 指从多张表中查询数据
笛卡尔积: 笛卡尔乘积是指在数学中,两个集合(A集合 和 B集合)的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
内连接
隐式内连接:select 字段列表 from 表1 , 表2 where 条件 ... ;
显式内连接:select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;
-- A.查询员工的姓名, 及所属的部门名称 (隐式内连接实现) select * from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id; -- B.查询员工的姓名, 及所属的部门名称 (显式内连接实现) select * from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;
外连接
左外连接:select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;
右外连接:select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;
-- A.查询员工表所有员工的姓名, 和对应的部门名称 (左外连接) select * from tb_emp e left join tb_dept d on e.dept_id = d.id; select * from tb_dept d right join tb_emp e on e.dept_id = d.id; -- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接) select * from tb_dept d left join tb_emp e on d.id = e.dept_id; select * from tb_emp e right join tb_dept d on d.id = e.dept_id;
子查询
介绍:SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
形式:select * from t1 where column1 = ( select column1 from t2 … );
子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。
分类
标量子查询:子查询返回的结果为单个值
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式
常用的操作符:= <> > >= < <=
列子查询:子查询返回的结果为一列
子查询返回的结果是一列(可以是多行)
常用的操作符:in 、not in等
行子查询:子查询返回的结果为一行
子查询返回的结果是一行(可以是多列)。
常用的操作符:= 、<> 、in 、not in
表子查询:子查询返回的结果为多行多列
子查询返回的结果是多行多列,常作为临时表
常用的操作符:in
-- 标量子查询 -- A.查询 "教研部" 的所有员工信息 select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from tb_emp where dept_id = (select id from tb_dept where name = '教研部'); -- B.查询在 "方东白" 入职之后的员工信息 select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白'); -- 列子查询 -- A.查询 "教研部" 和 "咨询部" 的所有员工信息 select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from tb_emp where job in (select id from tb_dept where name in ('教研部', '咨询部')); -- 行子查询 -- A.查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息; select * from tb_emp where (job, entrydate) = (2, '2007-01-01'); select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from tb_emp where (job, entrydate) = (select job, entrydate from tb_emp where name = '韦一笑') and name != '韦一笑'; -- 表子查询 -- A.查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息 select * from (select * from tb_emp where entrydate > '2006-01-01') temp left join tb_dept on temp.dept_id = tb_dept.id;
事务
delete from tb_dept where id = 1;
delete from tb_emp where dept_id = 1;
如果删除部门成功了,而删除该部门的员工时失败了,就造成了数据的不一致。
事务 是一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作 要么同时成功,要么同时失败。
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
开启事务:start transaction; / begin ;
提交事务:commit;
回滚事务:rollback;
start transaction; #begin delete from tb_dept where id = 1; delete from tb_emp where dept_id = 1; commit ; start transaction; delete from tb_dept where id = 2; # delete from tb_emp where dept_id == 2; 【错误】 rollback ;
四大特性【重点】
原子性
事务是不可分割的最小单元,要么全部成功,要么全部失败
一致性
事务完成时,必须使所有的数据都保持一致状态
提交后修改表数据与事务中数据一致
隔离性
数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
在没有提交情况下不对表数据操作
持久性
事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
索引
索引(index)是帮助数据库 高效获取数据 的 数据结构 。
优点
提高数据查询的效率,降低数据库的IO成本。
通过索引列对数据进行排序,降低数据排序的成本,降低CPU消耗。
缺点
索引会占用存储空间。
索引大大提高了查询效率,同时却也降低了insert、update、delete的效率。
MySQL数据库支持的索引结构有很多,如:Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引,如果没有特别指明,都是指默认的 B+Tree 结构组织的索引。
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
show index from 表名;
drop index 索引名 on 表名;
注意事项
主键字段,在建表时,会自动创建主键索引。
添加唯一约束时,数据库实际上会添加唯一索引。
select * from system_user where user_uuid = '4357abc4-be4d-448d-a1bc-f7d22c1610bc'; -- 4 s 860 ms create index uuid_index on system_user(user_uuid); -- 创建索引 select * from system_user where user_uuid = '4357abc4-be4d-448d-a1bc-f7d22c1610bc'; -- 19 ms show index from system_user; drop index uuid_index on system_user;