目录
前言
mysql自用笔记复盘
一、数据库
什么是数据库?
- 数据库:DataBase(DB),是存储和管理数据的仓库。
- 数据库管理系统:DataBase ManagementSystem(DBMS),操纵和管理数据库的大型软件。
- SQL:Structured QueryLanguage,操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准
官网下载地址:MySQL官网下载地址
1.1 MySQL安装
1.1.1系统修复
检测并添加你系统中mysql需要的一些底层类库
1.1.2 软件安装
将软件解压,然后把文件夹复制到
D:\dev
结构如下(注意:目录不能有空格 不能有中文)1.1.3 设置环境变量
新建系统变量,添加
MYSQL_HOME=mysql安装目录
编辑环境变量,在path中添加
%MYSQL_HOME%/bin
1.1.4 配置文件(已完成)
mysql的安装目录配置文件
my.ini
(端口号、字符集编码)[mysqld] # 设置3306端口 port=3306 # 允许最大连接数 max_connections=200 # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统 max_connect_errors=10 # 服务端使用的字符集默认为UTF8 character-set-server=utf8mb4 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # 默认使用“mysql_native_password”插件认证 default_authentication_plugin=mysql_native_password # 配置时区 default-time_zone='+8:00' [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4 [client] # 设置mysql客户端连接服务端时默认使用的端口 port=3306 default-character-set=utf8mb4
1.1.5 创建服务并启动
以
管理员身份
运行dos命令行, 并执行下面三个命令:# 注意调整下面数据库配置文件的位置 mysqld --install mysql --defaults-file="D:\dev\mysql-8.0.31-winx64\my.ini" # 初始化 mysqld --initialize-insecure # 启动服务 net start mysql # 设置root用户的密码 mysqladmin -u root password root
1.1.6 访问测试
dos命令行, 并执行下面命令:
# 本地登录 mysql -uroot -proot # 退出 exit
1.1.7 停止并卸载mysql
以
管理员身份
运行dos命令行,这两个命令用于卸载mysql服务,当需要重装mysql之前再执行net stop mysql mysqld --remove mysql
MySQL-企业开发使用方式
mysql -u用户名 -p密码[-h数据库服务器IP地址 -P端口号]
1.2 数据模型
关系型数据库(RDBMS):建立在关系模型基础上,由多张相互连接的二维表组成的数据库
特点
- 使用表存储数据,格式统一,便于维护
- 使用SQL语言操作,标准统一,使用方便,可用于复杂查询
1.3 SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
二、DDL、DML、DQL
2.1 概述
DDL 英文全称是 Data Definition Language,数据定义语言,用来定义数据库对象(数据库、表)。
注意事项
上述语法中的database,也可以替换成schema。如:createschema db01;
2.2 DDL表操作
约束
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确性、有效性和完整性。
约束 描述 关键字 非空约束 限制该字段值不能为null not null 唯一约束 保证字段的所有数据都是唯一、不重复的 unique 主键约束 主键是一行数据的唯一标识,要求非空且唯一 primary key 默认约束 保存数据时,如果未指定该字段值,则采用默认值 default 外键约束 让两张表的数据建立连接,保证数据的一致性和完整性 foreign key
数据类型
MySQL中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型。
数值类型
字符串类型
日期时间类型
查询
- 查询当前数据库所有表: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]表名;
注意事项
在删除表时,表中的全部数据也会被删除
2.3 DML
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。
- 添加数据(INSERT)
- 修改数据(UPDATE
- 删除数据(DELETE)
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,..);
注意事项
- 插入数据时,指定的字段顺序需要与值的顺序是-一对应的
- 字符串和日期型数据应该包含在引号中
- 插入的数据大小,应该在字段的规定范围内
update语法
修改数据:update 表名 set 字段名1=值1,字段名2=值2,….[where 条件];注意事项
修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
delete语法
删除数据:delete from 表名[where 条件];注意事项
- DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据
- DELETE 语句不能删除某一个字段的值(如果要操作,可以使用UPDATE,将该字段的值置为NULL)
2.4 DQL
DQL英文全称是Data QueryLanguage(数据查询语言),用来查询数据库表中的记录。
关键字:SELECT
语法
select
字段列表
from
表名列表
where
条件列表group by分组字段列表
having
分组后条件列表order by
排序字段列表
limit
分页参数
- 基本查询
- 条件查询(where)
- 分组查询(group by)
- 排序查询(order by)
- 分页查询(limit)
数据准备:
-- 员工管理 create table 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 '入职时间', create_time datetime not null comment '创建时间', update_time datetime not null comment '修改时间' ) comment '员工表'; INSERT INTO emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time) VALUES (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',now(),now()), (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',now(),now()), (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',now(),now()), (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',now(),now()), (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',now(),now()), (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',now(),now()), (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',now(),now()), (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',now(),now()), (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',now(),now()), (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',now(),now()), (11,'luzhangke','123456','鹿杖客',1,'11.jpg',2,'2007-02-01',now(),now()), (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',2,'2008-08-18',now(),now()), (13,'fangdongbai','123456','方东白',1,'13.jpg',1,'2012-11-01',now(),now()), (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',now(),now()), (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',now(),now()), (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',now(),now()), (17,'chenyouliang','12345678','陈友谅',1,'17.jpg',NULL,'2015-03-21',now(),now()), (18,'zhang1','123456','张一',1,'2.jpg',2,'2015-01-01',now(),now()), (19,'zhang2','123456','张二',1,'2.jpg',2,'2012-01-01',now(),now()), (20,'zhang3','123456','张三',1,'2.jpg',2,'2018-01-01',now(),now()), (21,'zhang4','123456','张四',1,'2.jpg',2,'2015-01-01',now(),now()), (22,'zhang5','123456','张五',1,'2.jpg',2,'2016-01-01',now(),now()), (23,'zhang6','123456','张六',1,'2.jpg',2,'2012-01-01',now(),now()), (24,'zhang7','123456','张七',1,'2.jpg',2,'2006-01-01',now(),now()), (25,'zhang8','123456','张八',1,'2.jpg',2,'2002-01-01',now(),now()), (26,'zhang9','123456','张九',1,'2.jpg',2,'2011-01-01',now(),now()), (27,'zhang10','123456','张十',1,'2.jpg',2,'2004-01-01',now(),now()), (28,'zhang11','123456','张十一',1,'2.jpg',2,'2007-01-01',now(),now()), (29,'zhang12','123456','张十二',1,'2.jpg',2,'2020-01-01',now(),now());
2.4.1 条件查询select字段列表from 表名where 条件列表
-- =================== 条件查询 ====================== -- 1. 查询 姓名 为 杨逍 的员工 select * from emp where name = '杨逍'; -- 2. 查询在 id小于等于5 的员工信息 select * from emp where id <= 5; -- 3. 查询 没有分配职位 的员工信息 -- 判断 null , 用 is null select * from emp where job is null; -- 4. 查询 有职位 的员工信息 -- 判断 不是null , 用 is not null select * from emp where job is not null ; -- 5. 查询 密码不等于 '123456' 的员工信息 select * from emp where password <> '123456'; select * from emp where password != '123456'; -- 6. 查询入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息 select * from emp where entrydate between '2000-01-01' and '2010-01-01' ; -- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息 select * from emp where (entrydate between '2000-01-01' and '2010-01-01') and gender = 2; -- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息 select * from emp where job = 2 or job = 3 or job = 4; select * from emp where job in (2,3,4); -- 9. 查询姓名为两个字的员工信息 select * from emp where name like '__'; -- 10. 查询姓 '张' 的员工信息 ---------> 张% select * from emp where name like '张%'; -- 11. 查询姓名中包含 '三' 的员工信息 select * from emp where name like '%三%';
2.4.2 聚合函数
- 基本查询
- 条件查询(where)
- 分组查询(group by)
- 排序查询(order by)
- 分页查询(limit)
注意事项
- null值不参与所有聚合函数运算
- 统计数量可以使用:count(*)、count(常量)、推荐使用count(*)、count(字段)
2.4.3 分组查询
where与having区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
-- =================== 分组查询 ====================== -- 聚合函数 -- 1. 统计该企业员工数量 -- count -- A. count(字段) select count(id) from emp; select count(job) from emp; -- null值不参与聚合函数运算 -- B. count(*) select count(*) from emp; -- C. count(值) select count(1) from emp; -- 2. 统计该企业员工 ID 的平均值 select avg(id) from emp; -- 3. 统计该企业最早入职的员工的入职日期 select min(entrydate) from emp; -- 4. 统计该企业最近入职的员工的入职日期 select max(entrydate) from emp; -- 5. 统计该企业员工的 ID 之和 select sum(id) from emp; -- 分组 -- 1. 根据性别分组 , 统计男性和女性员工的数量 -- count select gender , count(*) from emp group by gender; -- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位 -- count select job ,count(*) from emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;
2.4.4 排序查询
- ASC:升序(默认值)
- DESC:降序
注意事项
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。-- =================== 排序查询 ====================== -- 1. 根据入职时间, 对员工进行升序排序 -- 排序条件 select * from emp order by entrydate asc ; -- 默认升序, asc可以省略的 select * from emp order by entrydate ; -- 2. 根据入职时间, 对员工进行降序排序 select * from emp order by entrydate desc; -- 3. 根据 入职时间 对公司的员工进行 升序排序 , 入职时间相同 , 再按照 ID 进行降序排序 select * from emp order by entrydate asc , id desc ; -- 练习 : 员工管理列表查询 , 根据最后操作时间, 进行倒序排序 -- 条件 : name , gender , entrydate select * from emp where name like '%张%' and gender = 1 and entrydate between '2000-01-01' and '2010-01-01' order by update_time desc;
2.4.5 分页查询
注意事项
- 起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySOL中是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10
-- =================== 分页查询 ====================== -- 1. 查询第1页员工数据, 每页展示10条记录 select * from emp limit 0,10; select * from emp limit 10; -- 2. 查询第2页员工数据, 每页展示10条记录 select * from emp limit 10,10; -- 公式 : 页码 ---> 起始索引 -------> 起始索引 = (页码 - 1) * 每页记录数
2.4.6 案例
-- 练习 : 员工管理列表查询 , 根据最后操作时间, 进行倒序排序 -- 条件 : name , gender , entrydate select * from emp where name like '%张%' and gender = 1 and entrydate between '2000-01-01' and '2010-01-01' order by update_time desc limit 0,10 ;
函数:
- if(表达式,tvalue,fvalue):当表达式为true时,取值value;
- 当表达式为false时,取值fvalueRcase expr when value1 then result1 [when value2 then value2 ...l lelse result] end
-- 男性与女性员工的人数统计 (1 : 男性员工 , 2 : 女性员工) -- 函数: if(条件表达式 , t , f) select if(gender = 1, '男性员工' , '女性员工') '性别', count(*) '人数' from emp group by gender; -- 员工职位信息 -- count -- 函数: case when ... then ... when ... then ... else ... end -- 函数: case ... when ... then ... when ... then ... else ... end select (case when job = 1 then '班主任' when job = 2 then '讲师' when job = 3 then '教研主管' when job = 4 then '学工主管' else '无职位' end ) '职位', count(*) from emp group by job; select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '教研主管' when 4 then '学工主管' else '无职位' end ) '职位', count(*) from emp group by job;
2.5 多表设计
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
数据准备
-- 部门管理 create table tb_dept( id int unsigned primary key auto_increment comment '主键ID', name varchar(10) not null unique comment '部门名称', create_time datetime not null comment '创建时间', update_time datetime not null comment '修改时间' ) comment '部门表'; -- 员工管理(带约束) 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 unsigned comment '部门ID', create_time datetime not null comment '创建时间', update_time datetime not null comment '修改时间' ) comment '员工表'; insert into tb_dept (id, name, create_time, update_time) values (1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()), (4,'就业部',now(),now()),(5,'人事部',now(),now()); INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()), (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()), (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()), (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()), (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()), (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()), (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()), (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()), (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()), (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()), (11,'luzhangke','123456','鹿杖客',1,'11.jpg',1,'2007-02-01',1,now(),now()), (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',1,'2008-08-18',1,now(),now()), (13,'fangdongbai','123456','方东白',1,'13.jpg',2,'2012-11-01',2,now(),now()), (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()), (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()), (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()), (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());
2.5.1 外键
添加外键alter table tb_emp add constraint tb_emp_fk _dept_idforeign key(dept_id)referencestb_dept (id);
注:
1. 物理外键
概念:使用 foreign key 定义外键关联另外一张表。
缺点:
- 影响增、删、改的效率(需要检查外键关系)
- 仅用于单节点数据库,不适用与分布式、集群场景。
- 容易引发数据库的死锁问题,消耗性能。
2. 逻辑外键(推荐)
概念:在业务层逻辑中,解决外键关联。
- 通过逻辑外键,就可以很方便的解决上述问题。
2.5.2 一对一
案例:用户 与 身份证信息 的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
数据准备效果
-- ===========================================一对一===================================== create table tb_user( id int unsigned primary key auto_increment comment 'ID', name varchar(10) not null comment '姓名', gender tinyint unsigned not null comment '性别, 1 男 2 女', phone char(11) comment '手机号', degree varchar(10) comment '学历' ) comment '用户信息表'; insert into tb_user values (1,'白眉鹰王',1,'18812340001','初中'), (2,'青翼蝠王',1,'18812340002','大专'), (3,'金毛狮王',1,'18812340003','初中'), (4,'紫衫龙王',2,'18812340004','硕士'); create table tb_user_card( id int unsigned primary key auto_increment comment 'ID', nationality varchar(10) not null comment '民族', birthday date not null comment '生日', idcard char(18) not null comment '身份证号', issued varchar(20) not null comment '签发机关', expire_begin date not null comment '有效期限-开始', expire_end date comment '有效期限-结束', user_id int unsigned not null unique comment '用户ID', constraint fk_user_id foreign key (user_id) references tb_user(id) ) comment '用户信息表'; insert into tb_user_card values (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1), (2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2), (3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3), (4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);
2.5.3 多对多
案例:学生 与 课程的关系
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
数据准备-- ======================================多对多============================= create table tb_student( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', no varchar(10) comment '学号' ) comment '学生表'; insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104'); create table tb_course( id int auto_increment primary key comment '主键ID', name varchar(10) comment '课程名称' ) comment '课程表'; insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop'); create table tb_student_course( id int auto_increment comment '主键' primary key, student_id int not null comment '学生ID', course_id int not null comment '课程ID', constraint fk_courseid foreign key (course_id) references tb_course (id), constraint fk_studentid foreign key (student_id) references tb_student (id) )comment '学生课程中间表'; insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);
2.5.4 案例
具体数据的设置
-- 分类表 create table category( id int unsigned primary key auto_increment comment '主键ID', name varchar(20) not null unique comment '分类名称', type tinyint unsigned not null comment '类型 1 菜品分类 2 套餐分类', sort tinyint unsigned not null comment '顺序', status tinyint unsigned not null default 0 comment '状态 0 禁用,1 启用', create_time datetime not null comment '创建时间', update_time datetime not null comment '更新时间' ) comment '菜品及套餐分类' ; -- 菜品表 create table dish( id int unsigned primary key auto_increment comment '主键ID', name varchar(20) not null unique comment '菜品名称', category_id int unsigned not null comment '菜品分类ID', price decimal(8, 2) not null comment '菜品价格', image varchar(300) not null comment '菜品图片', description varchar(200) comment '描述信息', status tinyint unsigned not null default 0 comment '状态, 0 停售 1 起售', create_time datetime not null comment '创建时间', update_time datetime not null comment '更新时间' ) comment '菜品'; -- 套餐表 create table setmeal( id int unsigned primary key auto_increment comment '主键ID', name varchar(20) not null unique comment '套餐名称', category_id int unsigned not null comment '分类id', price decimal(8, 2) not null comment '套餐价格', image varchar(300) not null comment '图片', description varchar(200) comment '描述信息', status tinyint unsigned not null default 0 comment '状态 0:停用 1:启用', create_time datetime not null comment '创建时间', update_time datetime not null comment '更新时间' )comment '套餐' ; -- 套餐菜品关联表 create table setmeal_dish( id int unsigned primary key auto_increment comment '主键ID', setmeal_id int unsigned not null comment '套餐id ', dish_id int unsigned not null comment '菜品id', copies tinyint unsigned not null comment '份数' )comment '套餐菜品关系';
2.5.5 多表查询
数据准备
-- 部门管理 create table tb_dept( id int unsigned primary key auto_increment comment '主键ID', name varchar(10) not null unique comment '部门名称', create_time datetime not null comment '创建时间', update_time datetime not null comment '修改时间' ) comment '部门表'; insert into tb_dept (id, name, create_time, update_time) values(1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()), (4,'就业部',now(),now()),(5,'人事部',now(),now()); -- 员工管理 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 教研主管, 5 咨询师', entrydate date comment '入职时间', dept_id int unsigned comment '部门ID', create_time datetime not null comment '创建时间', update_time datetime not null comment '修改时间' ) comment '员工表'; INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()), (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()), (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()), (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()), (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()), (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()), (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()), (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()), (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()), (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()), (11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()), (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()), (13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()), (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()), (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()), (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,now(),now()), (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());
- 多表查询:指从多张表中查询数据
- 笛卡尔积:笛卡尔乘积是指在数学中,两个集合(A集合 和 B集合)的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
连接查询
内连接:相当于查询A、B交集部分数据
外连接
- 左外连接:查询左表所有数据(包括两张表交集部分数据)
- 右外连接:查询右表所有数据(包括两张表交集部分数据)
子查询
2.5.6 内连接
-- ============================= 内连接 ========================== -- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现) select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id; -- 起别名 select e.name, d.name from tb_emp e , tb_dept d where e.dept_id = d.id; -- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现) select tb_emp.name,tb_dept.name from tb_emp join tb_dept on tb_emp.dept_id = tb_dept.id;
2.5.7 外连接
-- =============================== 外连接 ============================ -- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接) select e.name, d.name from tb_emp e left join tb_dept d on e.dept_id = d.id; -- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接) select e.name,d.name from tb_emp e right join tb_dept d on e.dept_id = d.id; select e.name, d.name from tb_dept d left join tb_emp e on e.dept_id = d.id;
2.5.8 子查询
- 标量子查询:子查询返回的结果为单个值
- 列子查询:子查询返回的结果为一列
- 行子查询:子查询返回的结果为一行
- 表子查询:子查询返回的结果为多行多列
标量子查询
- 子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式
- 常用的操作符:=、<>、>、>=、<、<=
列子查询
- 子查询返回的结果是一列(可以是多行)
- 常用的操作符:in、notin等
行子查询
- 子查询返回的结果是一行(可以是多列)
- 常用的操作符:=、<>、in、not in
表子查询
- 子查询返回的结果是多行多列,常作为临时表
- 常用的操作符:in
-- ========================= 子查询 ================================ -- 标量子查询 -- A. 查询 "教研部" 的所有员工信息 -- a. 查询 教研部 的部门ID - tb_dept select id from tb_dept where name = '教研部'; -- b. 再查询该部门ID下的员工信息 - tb_emp select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部'); -- B. 查询在 "方东白" 入职之后的员工信息 -- a. 查询 方东白 的入职时间 select entrydate from tb_emp where name = '方东白'; -- b. 查询在 "方东白" 入职之后的员工信息 select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白'); -- 列子查询 -- A. 查询 "教研部" 和 "咨询部" 的所有员工信息 -- a. 查询 "教研部" 和 "咨询部" 的部门ID - tb_dept select id from tb_dept where name = '教研部' or name = '咨询部'; -- b. 根据部门ID, 查询该部门下的员工信息 - tb_emp select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部'); -- 行子查询 -- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ; -- a. 查询 "韦一笑" 的入职日期 及 职位 select entrydate,job from tb_emp where name = '韦一笑'; -- b. 查询与其入职日期 及 职位都相同的员工信息 ; -- 方式一 select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '韦一笑') and job = (select job from tb_emp where name = '韦一笑'); select * from tb_emp where entrydate = '2007-01-01' and job=2; -- 方式二 select * from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name = '韦一笑'); -- 表子查询 -- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门名称 -- a. 查询入职日期是 "2006-01-01" 之后的员工信息 select * from tb_emp where entrydate > '2006-01-01'; -- b. 查询这部分员工信息及其部门名称 - tb_dept select e.* , d.name from (select * from tb_emp where entrydate > '2006-01-01') e , tb_dept d where e.dept_id = d.id;
2.5.9 多表查案例
-- ================================================== 多表查询案例 ============================================= -- 数据准备 : -- 分类表 create table category( id int unsigned primary key auto_increment comment '主键ID', name varchar(20) not null unique comment '分类名称', type tinyint unsigned not null comment '类型 1 菜品分类 2 套餐分类', sort tinyint unsigned not null comment '顺序', status tinyint unsigned not null default 0 comment '状态 0 禁用,1 启用', create_time datetime not null comment '创建时间', update_time datetime not null comment '更新时间' ) comment '分类' ; -- 菜品表 create table dish( id int unsigned primary key auto_increment comment '主键ID', name varchar(20) not null unique comment '菜品名称', category_id int unsigned not null comment '菜品分类ID', price decimal(8, 2) not null comment '菜品价格', image varchar(300) not null comment '菜品图片', description varchar(200) comment '描述信息', status tinyint unsigned not null default 0 comment '状态, 0 停售 1 起售', create_time datetime not null comment '创建时间', update_time datetime not null comment '更新时间' ) comment '菜品'; -- 套餐表 create table setmeal( id int unsigned primary key auto_increment comment '主键ID', name varchar(20) not null unique comment '套餐名称', category_id int unsigned not null comment '分类id', price decimal(8, 2) not null comment '套餐价格', image varchar(300) not null comment '图片', description varchar(200) comment '描述信息', status tinyint unsigned not null default 0 comment '状态 0:停用 1:启用', create_time datetime not null comment '创建时间', update_time datetime not null comment '更新时间' )comment '套餐' ; -- 套餐菜品关联表 create table setmeal_dish( id int unsigned primary key auto_increment comment '主键ID', setmeal_id int unsigned not null comment '套餐id ', dish_id int unsigned not null comment '菜品id', copies tinyint unsigned not null comment '份数' )comment '套餐菜品中间表'; -- ================================== 导入测试数据 ==================================== -- category insert into category (id, type, name, sort, status, create_time, update_time) values (1, 1, '酒水饮料', 10, 1, '2022-08-09 22:09:18', '2022-08-09 22:09:18'); insert into category (id, type, name, sort, status, create_time, update_time) values (2, 1, '传统主食', 9, 1, '2022-08-09 22:09:32', '2022-08-09 22:18:53'); insert into category (id, type, name, sort, status, create_time, update_time) values (3, 2, '人气套餐', 12, 1, '2022-08-09 22:11:38', '2022-08-10 11:04:40'); insert into category (id, type, name, sort, status, create_time, update_time) values (4, 2, '商务套餐', 13, 1, '2022-08-09 22:14:10', '2022-08-10 11:04:48'); insert into category (id, type, name, sort, status, create_time, update_time) values (5, 1, '经典川菜', 6, 1, '2022-08-09 22:17:42', '2022-08-09 22:17:42'); insert into category (id, type, name, sort, status, create_time, update_time) values (6, 1, '新鲜时蔬', 7, 1, '2022-08-09 22:18:12', '2022-08-09 22:18:28'); insert into category (id, type, name, sort, status, create_time, update_time) values (7, 1, '汤类', 11, 1, '2022-08-10 10:51:47', '2022-08-10 10:51:47'); -- dish insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (1,'王老吉', 1, 6.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/41bfcacf-7ad4-4927-8b26-df366553a94c.png', '', 1, '2022-06-09 22:40:47', '2022-06-09 22:40:47'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (2,'北冰洋', 1, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4451d4be-89a2-4939-9c69-3a87151cb979.png', '还是小时候的味道', 1, '2022-06-10 09:18:49', '2022-06-10 09:18:49'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (3,'雪花啤酒', 1, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/bf8cbfc1-04d2-40e8-9826-061ee41ab87c.png', '', 1, '2022-06-10 09:22:54', '2022-06-10 09:22:54'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (4,'米饭', 2, 2.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/76752350-2121-44d2-b477-10791c23a8ec.png', '精选五常大米', 1, '2022-06-10 09:30:17', '2022-06-10 09:30:17'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (5,'馒头', 2, 1.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/475cc599-8661-4899-8f9e-121dd8ef7d02.png', '优质面粉', 1, '2022-06-10 09:34:28', '2022-06-10 09:34:28'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (6,'老坛酸菜鱼', 5, 56.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4a9cefba-6a74-467e-9fde-6e687ea725d7.png', '原料:汤,草鱼,酸菜', 1, '2022-06-10 09:40:51', '2022-06-10 09:40:51'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (7,'经典酸菜鮰鱼', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/5260ff39-986c-4a97-8850-2ec8c7583efc.png', '原料:酸菜,江团,鮰鱼', 1, '2022-06-10 09:46:02', '2022-06-10 09:46:02'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (8,'蜀味水煮草鱼', 5, 38.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a6953d5a-4c18-4b30-9319-4926ee77261f.png', '原料:草鱼,汤', 1, '2022-06-10 09:48:37', '2022-06-10 09:48:37'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (9,'清炒小油菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/3613d38e-5614-41c2-90ed-ff175bf50716.png', '原料:小油菜', 1, '2022-06-10 09:51:46', '2022-06-10 09:51:46'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (10,'蒜蓉娃娃菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4879ed66-3860-4b28-ba14-306ac025fdec.png', '原料:蒜,娃娃菜', 1, '2022-06-10 09:53:37', '2022-06-10 09:53:37'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (11,'清炒西兰花', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/e9ec4ba4-4b22-4fc8-9be0-4946e6aeb937.png', '原料:西兰花', 1, '2022-06-10 09:55:44', '2022-06-10 09:55:44'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (12,'炝炒圆白菜', 6, 18.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/22f59feb-0d44-430e-a6cd-6a49f27453ca.png', '原料:圆白菜', 1, '2022-06-10 09:58:35', '2022-06-10 09:58:35'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (13,'清蒸鲈鱼', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c18b5c67-3b71-466c-a75a-e63c6449f21c.png', '原料:鲈鱼', 1, '2022-06-10 10:12:28', '2022-06-10 10:12:28'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (14,'东坡肘子', 5, 138.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a80a4b8c-c93e-4f43-ac8a-856b0d5cc451.png', '原料:猪肘棒', 1, '2022-06-10 10:24:03', '2022-06-10 10:24:03'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (15,'梅菜扣肉', 5, 58.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/6080b118-e30a-4577-aab4-45042e3f88be.png', '原料:猪肉,梅菜', 1, '2022-06-10 10:26:03', '2022-06-10 10:26:03'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (16,'剁椒鱼头', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/13da832f-ef2c-484d-8370-5934a1045a06.png', '原料:鲢鱼,剁椒', 1, '2022-06-10 10:28:54', '2022-06-10 10:28:54'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (17,'馋嘴牛蛙', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/7a55b845-1f2b-41fa-9486-76d187ee9ee1.png', '配料:鲜活牛蛙,丝瓜,黄豆芽', 1, '2022-06-10 10:37:52', '2022-06-10 10:37:52'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (18,'鸡蛋汤', 7, 4.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c09a0ee8-9d19-428d-81b9-746221824113.png', '配料:鸡蛋,紫菜', 1, '2022-06-10 10:54:25', '2022-06-10 10:54:25'); insert into dish (id, name, category_id, price, image, description, status, create_time, update_time) values (19,'平菇豆腐汤', 7, 6.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/16d0a3d6-2253-4cfc-9b49-bf7bd9eb2ad2.png', '配料:豆腐,平菇', 1, '2022-06-10 10:55:02', '2022-06-10 10:55:02'); -- setmeal insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (1, 4, '商务套餐A', 20.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/21a5ed3a-97f6-447a-af9d-53deabfb5661.png', '2022-06-10 10:58:09', '2022-06-10 10:58:09'); insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (2, 4, '商务套餐B', 22.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8d0075f8-9008-4390-94ca-2ca631440304.png', '2022-06-10 11:00:13', '2022-06-10 11:11:37'); insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time) values (3, 3, '人气套餐A', 49.00, 1, '', 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8979566b-0e17-462b-81d8-8dbace4138f4.png', '2022-06-10 11:11:23', '2022-06-10 11:11:23'); -- setmeal_dish insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (1, 1, 1, 1); insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (2, 1, 4, 1); insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (3, 1, 11, 1); insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (4, 2, 2, 1); insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (5, 2, 4, 1); insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (6, 2, 9, 1); insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (7, 3, 2, 1); insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (8, 3, 6, 1); insert into setmeal_dish (id, setmeal_id, dish_id, copies) values (9, 3, 5, 1); -- 需求: -- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 . -- 表: dish , category -- SQL: select d.name, d.price, c.name from dish d, category c where d.category_id = c.id and d.price < 10; -- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品, 展示出菜品的名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来). -- 表: dish , category -- SQL: select d.name, d.price, c.name from dish d left join category c on d.category_id = c.id where d.price between 10 and 50 and d.status = 1; -- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 . -- 表: dish , category -- SQL: select c.name, max(d.price) from dish d, category c where d.category_id = c.id group by c.name; -- 4. 查询各个分类下 菜品状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 . -- 表: dish , category -- SQL: select c.name, count(*) from dish d, category c where d.category_id = c.id and d.status = 1 group by c.name having count(*) >= 3; -- 5. 查询出 "商务套餐A" 中包含了哪些菜品 (展示出套餐名称、价格, 包含的菜品名称、价格、份数). -- 表: setmeal , setmeal_dish , dish -- SQL: select s.name, s.price, d.name, d.price, sd.copies from setmeal s, setmeal_dish sd, dish d where s.id = sd.setmeal_id and sd.dish_id = d.id and s.name = '商务套餐A'; -- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格). -- 表: dish -- SQL: -- a. 计算 菜品平均价格 select avg(price) from dish; -- b. 查询出低于菜品平均价格的菜品信息 select * from dish where price < (select avg(price) from dish);
2.6 事务
事务是一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作 要么同时成功,要么同时失败。
注意事项
- 默认MySOL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务
2.6.1 四大特性
-- ==================================== 事务 =========================== -- 开启事务 start transaction ; -- 删除部门 delete from tb_dept where id = 2; -- 删除部门下的员工 delete from tb_emp where dept_id == 2; -- 提交事务 commit; -- 回滚事务 rollback ; select * from tb_dept; select * from tb_emp;
2.7 索引(根据树形结构)
索引(index)是帮助数据库 高效获取数据 的数据结构
2.7.1 索引结构MySOL数据库支持的索引结构有很多,如:Hash索引、B+Tree索引、Ful-Text索引等。我们平常所说的索引,如果没有特别指明,都是指默认的 B+Tree 结构组织的索引。
2.7.2 语法注意事项
- 主键字段,在建表时,会自动创建主键索引。
- 添加唯一约束时,数据库实际上会添加唯一索引。
-- ================================ 索引 ============================= select * from tb_sku where sn = '100000003145008'; -- 14s select count(*) from tb_sku; create index idx_sku_sn on tb_sku(sn); -- 创建 : 为tb_emp表的name字段建立一个索引 . create index idx_emp_name on tb_emp(name); -- 查询 : 查询 tb_emp 表的索引信息 . show index from tb_emp; -- 删除: 删除 tb_emp 表中name字段的索引 . drop index idx_emp_name on tb_emp;