MySQL基础（数据库，表创建；单表，多表，嵌套查询，事务，索引）
/*表结构定义 DDL */
-- 创建数据库
create database if not exists db01;
-- 切换数据库
use db01;
-- 创建表
create table if not exists tb_emp(
-- 字段名 字段类型 字段约束 字段注释说明
id int primary key auto_increment comment '用户表ID，唯一标识',
username varchar(20) not null unique comment '用户名',
password varchar(32) not null default '123456' comment '密码',
name varchar(10) not null comment '用户姓名',
gender tinyint unsigned not null comment '用户性别 /*1:男 2:女 */',
icon varchar(300) comment '图片访问路径',
job tinyint unsigned comment '职位： 1班主任 2讲师 3学工主管 4教研主管',
entry_data date not null comment '入职日期',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '更新时间'
)comment '员工信息表';
-- 字段约束共有5种
-- 主键约束 primary key auto_increment
-- 非空约束 not null
-- 唯一约束 unique
-- 默认约束 default
-- 外键约束 foreign key
-- 查看数据库中所有的表
show tables;
-- 查看表结构
desc db01.tb_emp;
-- 查看数据表建表语句
show create table db01.tb_emp;
-- 添加字段: alter table 表名 add 字段名 类型(长度)[comment 注释] [约束];
alter table tb_emp add QQ varchar(12) comment 'QQ号' not null;
-- 修改字段类型: alter table 表名 modify 字段名 新数据类型(长度);
alter table tb_emp modify QQ varchar(13);
-- 修改字段名和字段类型: alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];
alter table tb_emp change QQ QQqq varchar(12);
-- 删除字段: alter table 表名 drop column 字段名;
alter table tb_emp drop column QQqq;
-- 修改表名: rename table 表名 to 新表名;
rename table tb_emp to tb_emps;
-- 删除表结构
drop table if exists tb_emp;
/*DML数据操作语言*/
-- insert语句
-- 指定字段添加数据: insert into 表名(字段名1, 字段名2) values (值1,值2);
-- 全部字段添加数据: insert into 表名 values (值1,值2,...);
insert into tb_emp (id, username, password, name, gender, icon, job, entry_data, create_time, update_time)
values (null,'yizi1111',default,'伊兹1111',1,'yizi.jpg',1,'2023-08-12',now(),now());
/*null是因为ID是自增 default 是因为password使用默认值 */
-- 批量添加数据(指定字段): insert into 表名(字段名1,字段名2) values (值1,值2),(值1,值2);
-- 批量添加数据(全部字段) : insert into 表名 values (值1,值2,...),(值1,值2,...);
-- update语句
-- 修改数据: update 表名 set 字段名1= 值1,字段名2= 值2,....[ where 条件];
update tb_emp set username = 'yizi02',name = '伊兹02' ,update_time = now() where id = 3;
update tb_emp set entry_data = '2023-08-13';
-- delete语句
-- 删除数据: delete from 表名 [where 条件];
delete from tb_emp where id = 2;
/*DQL数据查询语句*/
-- select语句
-- 基本查询 条件查询 分组查询 排序 分页
/*
select
字段列表
from
表名列表
where
条件列表
group by分组字段列表
having
分组后条件列表
order by排序字段列表
limit
分页参数
*/
/*准备测试数据*/
INSERT INTO tb_emp (id, username, password, name, gender, icon, job, entry_data, create_time, update_time)
VALUES
(1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),
(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),
(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),
(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33', '2022-10-27 16:36:01'),
(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),
(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:05'),
(17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33', '2022-10-27 16:36:07'),
(18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
(19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
(20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
(21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
(22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
(23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
(24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
(25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
(26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
(27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
(28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
(29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');
-- 基本查询
-- 查询多个字段: select 字段1,字段2,字段3 from 表名)
-- 查询所有字段(通配符 * ): select * from 表名;4
/*不建议使用通配符，影响效率，不直观*/
-- 设置别名: select 字段1 [ as 别名1],字段2 [as 别名2] from 表名;
select id, username, password, name as '姓名', gender, icon, job, entry_data, create_time, update_time from tb_emp;
-- 去除重复记录: select distinct 字段列表 from 表名;
select job from tb_emp;
select distinct job from tb_emp;
-- 条件查询
/*
比较运算符 功能
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
<>或!= 不等于
between ... and 在某个范围之内(含最小、最大值)
in(...) 在in之后的列表中的值，多选一
like 占位符 模糊匹配(_匹配单个字符，%匹配任意个字符)
is null 是null
逻辑运算符 功能
and 或 && 并且(多个条件同时成立)
or 或 || 或者(多个条件任意一个成立)
not 或 ! 非,不是
*/
select * from tb_emp where job is null ;
select * from tb_emp where job in (2,3,4);
select * from tb_emp where name like '__'; /*查询名字是两个字的员工*/
select * from tb_emp where name like '张%'; /*查询姓张的员工*/
-- 分组查询 （group by 字段 having 条件） /*聚合函数（count max min avg sum）*/
select count(*) from tb_emp;/*获取数据量*/ /*注意：count（*）推荐使用（MySQL做了优化）；（null值不参与所有聚合函数的运算*/
/*依据性别分组并统计男女各自的数量*/
select if(gender=1,'男性员工','女性员工') as '性别', count(*) as '数量' from tb_emp group by gender; /*允许返回的数据是 【分组字段】 和 【聚合函数】*/
/*入职时间在'2015-01-01'（包含）以前的员工，并对其结果根据职位分组，获取员工数量大于2的职位*/
select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位'end ) as 'work',count(*) as mounts
from tb_emp where entry_data <= '2015-01-01'
group by job having count(*) > 2;
-- 排序 order by 字段 [asc,desc] asc 升序默认的 desc降序
select * from tb_emp order by entry_data desc ;
/*先按入职时间升序排列，再按修改时间降序排列*/
select * from tb_emp order by entry_data asc ,update_time desc;
-- 分页 limit 起始索引，查询记录数 （注意起始索引 从0开始）
/*起始索引计算公式：起始索引 = （页码-1）* 每页记录数 */
select * from tb_emp order by entry_data asc ,update_time desc limit 0,5 ;
/*
多表设计：
1. 一对一
2. 一对多(多对一) 【部门】及【员工】表设计
3. 多对多
*/
-- 2. 一对多(多对一) 【部门】及【员工】表设计
create database if not exists db03;
use db03;
/*部门表*/
create table if not exists 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 if not exists tb_emp(
-- 字段名 字段类型 字段约束 字段注释说明
id int primary key auto_increment comment '用户表ID，唯一标识',
username varchar(20) not null unique comment '用户名',
password varchar(32) not null default '123456' comment '密码',
name varchar(10) not null comment '用户姓名',
gender tinyint unsigned not null comment '用户性别 /*1:男 2:女 */',
icon varchar(300) comment '图片访问路径',
job tinyint unsigned comment '职位： 1班主任 2讲师 3学工主管 4教研主管',
entry_data date not null 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, icon, job, entry_data,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());
-- 添加外键约束
-- alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (字段名);
alter table tb_emp add constraint tb_emp_fk_dept_id foreign key (dept_id) references tb_dept(id);
-- 物理外键:（目前项目开发中一般不建议使用）
-- 概念: 使用 foreign key 定义外键关联另外一张表。
-- 缺点:
-- 影响增、删、改的效率(需要检查外键关系)仅用于单节点数据库，不适用与分布式、集群场景容易引发数据库的死锁问题，消耗性能。
-- 逻辑外键 （推荐使用）
-- 概念: 在业务层逻辑中，解决外键关联。
-- 通过逻辑外键，就可以很方便的解决上述问题
/* 1. 一对一*/
-- 案例:用户与身份证信息的关系
-- 关系:一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
-- 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
create database if not exists db04;
use db04;
-- ===========================================一对一=====================================
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);
/*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);
/*多表查询*/
-- ===============================数据准备==================================
create database if not exists db05;
use db05;
-- 部门管理
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());
/*多表查询*/
select * from tb_emp,tb_dept; -- （返回笛卡尔积）
select * from tb_emp,tb_dept where tb_dept.id = tb_emp.id;
/*
连接查询
内连接:相当于查询A、B交集部分数据
外连接
左外连接:查询左表所有数据(包括两张表交集部分数据)
右外连接:查询右表所有数据(包括两张表交集部分数据)
子查询
*/
-- 内连接:相当于查询A、B交集部分数据
-- 隐式内连接: select 字段列表 from 表1，表2 where 条件
select * from db05.tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id ;
-- 显式内连接: select 字段列表 from 表1 [inner] join 表2 on 连接条件...
select emp.name as '员工姓名',dep.name as '部门名称'
from db05.tb_emp emp inner join tb_dept dep
on emp.dept_id = dep.id;
-- 外连接
-- 左外连接:查询左表所有数据(包括两张表交集部分数据)
-- 左外连接: select 字段列表 from 表1 left [ outer] join 表2 on 连接条件...;
select emp.name as '员工姓名',dep.name as '部门名称'
from db05.tb_emp emp left join tb_dept dep
on emp.dept_id = dep.id;
-- 右外连接:查询右表所有数据(包括两张表交集部分数据)
-- 右外连接: select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ...;
select emp.name as '员工姓名',dep.name as '部门名称'
from db05.tb_emp emp right join tb_dept dep
on emp.dept_id = dep.id;
/*子查询*/
/*
介绍: SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
形式: select * from tl where column1 = ( select column1 from t2 ... );
子查询外部的语句可以是 insert / update / delete / select 的任何一个，最常见的是 select。
分类：
标量子查询:子查询返回的结果为单个值
子查询返回的结果是单个值 (数字、字符串、日期等)，
最简单的形式常用的操作符: > < <= >=
1.查询教研部所有的员工信息
2.查询’东方白‘入职以后的所有员工
列子查询:子查询返回的结果为一列
行子查询:子查询返回的结果为一行
子查询返回的结果是一行(可以是多列)
常用的操作符:=、<>、in 、not in
表子查询:子查询返回的结果为多行多列
子查询返回的结果是多行多列，常作为临时表
常用的操作符:in
*/
/*标量子查询:子查询返回的结果为单个值
子查询返回的结果是单个值 (数字、字符串、日期等)，
最简单的形式常用的操作符: > < <= >=
*/
-- 1.查询教研部所有的员工信息
select * from db05.tb_emp where tb_emp.dept_id = (select id from db05.tb_dept where tb_dept.name = '教研部');
-- 2.查询’方东白‘入职以后的所有员工
select * from db05.tb_emp where tb_emp.entrydate >= (select entrydate from db05.tb_emp where tb_emp.name = '方东白');
/*行子查询:子查询返回的结果为一行
子查询返回的结果是一行(可以是多列)
常用的操作符:=、<>、in 、not in*/
-- 1.查询与“韦一笑”的入职日期及职位都相同的员工
select entrydate,job from db05.tb_emp where name = '韦一笑';
select * from db05.tb_emp where entrydate = '2007-01-01' and job = 2;
select * from db05.tb_emp where (entrydate ,job)= ('2007-01-01',2);/*等价上一句*/
select * from db05.tb_emp where (entrydate ,job)= (select entrydate,job from db05.tb_emp where name = '韦一笑');
/*表子查询:子查询返回的结果为多行多列
子查询返回的结果是多行多列，常作为临时表
常用的操作符:in*/
-- 查询入职时间是‘2006-01-01’之后的员工及其部门信息
select * from db05.tb_emp where entrydate > '2006-01-01';
select te.* ,td.name
from (select * from db05.tb_emp where entrydate > '2006-01-01') te
inner join tb_dept td on te.dept_id = td.id;
/*练习*/
/*数据准备*/
create database if not exists db06;
use db06;
-- 分类表
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元 的菜品的名称 、价格 及其 菜品的分类名称 .
select d.name,d.price,c.name from dish d join category c on c.id = d.category_id where d.price < 10;
-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
select d.name, d.price, c.name
from dish d
left join category c on c.id = d.category_id
where d.price between 10 and 50
and d.status = 1;
-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
select max(price) from dish;
select c.name,max(d.price) from dish d join category c on c.id = d.category_id group by c.name;
-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
select c.name, count(*) mounts
from dish d
join category c on c.id = d.category_id
where d.status = 1
group by c.name
having mounts >= 3;
-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
select id from setmeal where name = '商务套餐A';
select s.name,s.price, dish_id,copies from setmeal_dish sd join setmeal s on sd.setmeal_id = s.id where setmeal_id = (select id from setmeal where name = '商务套餐A');
select sd.name, sd.price, d.name, d.price, sd.copies
from dish d
join (select s.name, s.price, dish_id, copies
from setmeal_dish sd
join setmeal s on sd.setmeal_id = s.id
where setmeal_id = (select id from setmeal where name = '商务套餐A')) sd on d.id = sd.dish_id;
-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
select avg(price) from dish;
select * from dish where price < (select avg(price) from dish);
/*事务*/
-- 介绍
-- 概念
-- 事务 是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作 要么同时成功，要么同时失败。
/*
事务控制
开启事务: start transaction; / begin ;
提交事务: commit;
回滚事务: rollback;
*/
start transaction;
delete from db05.tb_dept where id = 2;
delete from db05.tb_emp where dept_id = 2;
commit;
rollback;
select * from tb_dept;
-- 事务四大特性（ACID）Atomicity Consistency lsolation Durability
/*
原子性：Atomicity
事务是不可分割的最小单元，
要么全部成功，要么全部失败
一致性：Consistency
事务完成时，必须使所有的数据都
保持一致状态
隔离性：Isolation
数据库系统提供的隔离机制，保
证事务在不受外部并发操作影响的独立环境下运行
持久性：Durability
事务一旦提交或回滚，它对数据
库中的数据的改变就是永久的
*/
-- 数据库优化
/*索引*/ /*优化查询速度*/
/*
1 概念：
索引 (index)是帮助数据库 高获取数据的 据结构
2 优缺点：
优点
1 提高数据查询的效率，降低数据库的IO成本。
2 通过索引列对数据进行排序，降低数据排序的成本，
3 降低CPU消耗。
缺点
1 索引会占用存储空间
2 索引大大提高了查询效率，同时却也降低了insert.update、delete的效率（数据发生变化，需要重新维护索引的结构）
3 索引数据结构：
MySQL数据库支持的索引结构有很多，
如: Hash索引、B+Tree索引、Full-Text索引等。
我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。
4.语法
创建索引
create [unique] index 索引名 on 表名(字段名,);···
查看索引
show index from 表名
删除索引
drop index 索引名 on 表名;
*/
-- 创建索引
-- create [unique] index 索引名 on 表名(字段名,);···
create index idx_emp_name on tb_emp(name);
-- 查看索引
-- show index from 表名
show index from tb_emp;
-- 删除索引
-- drop index 索引名 on 表名;
-- 注意事项
-- 主键字段，在建表时，会自动创建主键索引
-- 添加唯一约束时，数据库实际上会添加唯一索引。
*/
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
