查看数据库
show databases;show schemas;
(不区分大小写)
SQL语句通常被分为四大类：
DDL 数据定义语言，用来定义数据库对象（数据库、表、字段）
DML 数据操作语言，用来对数据库表中的数据进行增删改
DQL 数据查询语言，用来查询数据库中表的记录
QCL 数据控制语言，用来创建数据库用户，控制数据库的访问权限
创建数据库
create database db01;
create database if not exists db01;（数据库存在就不用创建也不会报错）
查看当前正在使用的数据库
select database();
使用数据库
use db01;
删除数据库db01
drop database db01；
drop database if exists db01；（如果数据库存在就删除数据库；不存在不执行）
在数据库中创建表（tb_user表名）
create table tb_user(
id int comment ‘ID,唯一标识’,
username varchar(20) comment ‘用户名’,
name varchar(10) comment ‘姓名’,
age int comment ‘年龄’,
gender char(1) comment ‘性别’
)comment ‘用户表’;
约束
非空约束：限制该字段值不能为null（not null）
唯一约束：保证字段的所有数据都是唯一、不重复的（unique）
主键约束：主键是一行数据的唯一标识，要求非空白且唯一（primary key）
默认约束：保存数据时，如果未指定该字段值，则采用默认值（default）
外键约束：让两张表的数据建立连接，保证数据的一致性和完整性（foreign key）
在数据库中创建表（tb_user表名[约束]）
create table tb_user(
id int primary key comment ‘ID,唯一标识’,
username varchar(20) not null comment ‘用户名’,
name varchar(10) not null comment ‘姓名’,
age int comment ‘年龄’,
gender char(1) default '男’comment ‘性别’
//没有输入默认为男生
)comment ‘用户表’;
date YYYY-MM-DD
datetime YYYY-MM-DD HH:MM:SS
查看当前数据库下的表
show tables;
查看指定表结构
desc tb_user;
查看数据库的建表语句
show create table tb_user;
创建tb_emp表
create table tb_emp (
id int 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 tb_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());
DDL:修改表结构
修改：为表tb_emp添加字段 qq varchar(11)
alter table tb_emp add qq varchar(11) comment ‘QQ’;
修改：修改tb_emp字段类型 qq varchar(13)
alter table tb_emp modify qq varchar(13) comment ‘QQ’;
修改：修改tb_emp字段名 qq为qq_num varchar(13)
alter table tb_emp change qq qq_num varchar(13) comment ‘QQ’;
修改：删除tb_emp的qq_num字段
alter table tb_emp drop column qq_num;
修改：将tb_emp表名修改为emp
rename table tb_emp to emp;
删除：删除tb_emp
drop table if exists tb_emp;
DML:更新数据
将tb_emp表的ID为1的员工姓名name字段更新为’张三’
update tb_emp set name=‘张三’,update_time=now() where id=1;
将tb_emp表的所有员工的入职日期更新为’2010-01-01’
update tb_emp set entrydate=‘2010-01-01’,update_time=now();
DML:删除数据----delete
删除tb_emp表中ID为1的员工
delete from tb_emp where id=1;
删除tb_emp表中的所有员工
delete from tb_emp；
DQL-基本查询
查询指定字段 name,entrydate 并返回
select name,entrydate from tb_emp ;
查询返回所有字段
方式一: 推荐 , 效率高 . 更直观
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp;
方式二:
select * from tb_emp;
查询所有员工的 name,entrydate, 并起别名(姓名、入职日期) — as 关键字可以省略
select name as ‘姓名’ ,entrydate as ‘入职日期’ from tb_emp ;
select name ‘姓名’ ,entrydate ‘入职日期’ from tb_emp ;
查询员工有哪几种职位(不要重复) – distinct
select distinct job from tb_emp;
select * from tb_emp where id = 1;
DQL-条件查询
1.查询 姓名 为 杨逍 的员工
select * from tb_emp where name = ‘杨逍’;
2.查询在 id小于等于5 的员工信息
select * from tb_emp where id <= 5;
3. 查询 没有分配职位 的员工信息 – 判断 null , 用 is null
select * from tb_emp where job is null;
4. 查询 有职位 的员工信息 – 判断 不是null , 用 is not null
select * from tb_emp where job is not null ;
5. 查询 密码不等于 ‘123456’ 的员工信息
select * from tb_emp where password <> ‘123456’;
select * from tb_emp where password != ‘123456’;
6. 查询入职日期 在 ‘2000-01-01’ (包含) 到 ‘2010-01-01’(包含) 之间的员工信息
select * from tb_emp where entrydate between ‘2000-01-01’ and ‘2010-01-01’ ;
7. 查询 入职时间 在 ‘2000-01-01’ (包含) 到 ‘2010-01-01’(包含) 之间 且 性别为女 的员工信息
select * from tb_emp where (entrydate between ‘2000-01-01’ and ‘2010-01-01’) and gender = 2;
8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from tb_emp where job = 2 or job = 3 or job = 4;
select * from tb_emp where job in (2,3,4);
9. 查询姓名为两个字的员工信息
select * from tb_emp where name like ‘__’;
10. 查询姓 ‘张’ 的员工信息 ---------> 张%
select * from tb_emp where name like ‘张%’;
11. 查询姓名中包含 ‘三’ 的员工信息
select * from tb_emp where name like ‘%三%’;
DQL-分组查询
聚合函数
–1. 统计该企业员工数量 – count
– A. count(字段)
select count(id) from tb_emp;
select count(job) from tb_emp; – null值不参与聚合函数运算
– B. count(常量)
select count(‘A’) from tb_emp;
– C. count()
select count() from tb_emp;
– 2. 统计该企业员工 ID 的平均值
select avg(id) from tb_emp;
– 3. 统计该企业最早入职的员工的入职日期
select min(entrydate) from tb_emp;
– 4. 统计该企业最迟入职的员工的入职日期
select max(entrydate) from tb_emp;
– 5. 统计该企业员工的 ID 之和
select sum(id) from tb_emp;
DQL-分组查询
– 1. 根据性别分组 , 统计男性和女性员工的数量 – count()
select gender , count() from tb_emp group by gender;
– 2. 先查询入职时间在 ‘2015-01-01’ (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位 – count
select job ,count() from tb_emp where entrydate <= ‘2015-01-01’ group by job having count() >= 2;
DQL-排序查询
- 根据入职时间, 对员工进行升序排序 – 排序条件
select * from tb_emp order by entrydate asc ; – 默认升序, asc可以省略的
select * from tb_emp order by entrydate ;
– 2. 根据入职时间, 对员工进行降序排序
select * from tb_emp order by entrydate desc;
– 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 ID 进行降序排序
select * from tb_emp order by entrydate asc , id desc ;
DQL-分页查询
分页查询：select 字段列表 from 表名 limit 起始索引,查询记录数;
–1.从起始索引0开始查询员工数据，每页每页展示5条记录
select * from tb_emp limit 0,5;
–2. 查询第1页员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;
select * from tb_emp limit 5;
– 3. 查询第2页员工数据, 每页展示5条记录
select * from tb_emp limit 5,5;
– 4. 查询第3页员工数据, 每页展示5条记录
select * from tb_emp limit 10,5;
（3-1）5
– 公式 : 页码 —> 起始索引 -------> 起始索引 = (页码 - 1) * 每页记录数
案例1:按需求完成员工管理的条件分页查询-根据输入条件，查询第一页数据，每页展示10条记录
输入条件：
姓名：张
性别：男
入职时间：2000-01-01 2010-01-01
select * from tb_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 ;
**案例2-1：根据需求，完成员工性别信息的统计(1 : 男性员工 , 2 : 女性员工)-count()**
– 函数: if(条件表达式 , t , f)
select if(gender = 1, ‘男性员工’ , ‘女性员工’) ‘性别’,count() from tb_emp group by gender;
案例2-2：根据需求，完成员工职位信息的统计
– 函数: case 表达式 when 值1 then 结果1 when 值2 then 结果2 … else … end
select (case when job = 1 then ‘班主任’ when job = 2 then ‘讲师’ when job = 3 then ‘教研主管’ when job = 4 then ‘学工主管’ else ‘无职位’ end ) ‘职位’,count() from tb_emp group by job;
多表查询
数据准备
create database db03;
use db03;
创建部门管理表（父表）
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 教研主管, 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_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’,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_dept;
select * from tb_emp;
– 多表查询
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
内连接
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;
外连接
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;
子查询
标量子查询
– 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 (3,2);
合并
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 = ‘2007-01-01’ and job=2;
合并
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,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;
多表查询案例
数据准备 :
– 分类表
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);
事务
– 开启事务
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;
索引
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;
2497
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
