第三章 模糊查询与分组查询 ② 代码
参考代码：
1.增删改语句 0715 【classinfo/student】
删除 —级联 —update语句 —查询 —设置别名
select * from classInfo;
select * from student;
delete from classInfo
insert into classinfo
(className)
select 'aaa01' UNION
select 'aaa02'
insert into student
(name,sex,address,clsId)
select '张三','男','河南',5 union
select '李四','女','河北',5 union
select '王五','女','河北',5
select * from classInfo;
select * from student;
#delete:1.可以带条件删除
2.可以整表删除
3.删除数据自增列不会重置
4.可以从主表中删除没有被关联的数据
#truncate:1.只能整表删除(清空表)
2.能够重置自增列
3.有外键关联的情况下，对于被从表关联的主表无法清空
#条件删除
delete from student where id = 4
#整删除
delete from student
#删除主表中没有被关联的数据
delete from classInfo where classId = 4
#truncate table 表名字
truncate table student
#有外键约束的主表，不能使用truncate清空
truncate table classInfo
=======================
#有外键关联的表，必须先删除从表，再删除主表
#创建表
drop table classinfo
create table classInfo
(
classId int primary key auto_increment,
className varchar(20) not null
)
select * from classInfo;
drop table student
create table student
(
id int primary key auto_increment,
name varchar(20),
sex char(1),
address varchar(20),
clsId int,
foreign key (clsId)
REFERENCES classInfo(classId)
on delete CASCADE #删除级联
on update CASCADE #修改级联
)
insert into classinfo
(className)
select 'aaa01' UNION
select 'aaa02'
insert into student
(name,sex,address,clsId)
select '张三','男','河南',1 union
select '李四','女','河北',1 union
select '王五','女','河北',2
select * from student;
select * from classInfo;
#测试级联修改
update classinfo
set classId = 20
where classId = 2
#测试级联删除
delete from classInfo where classId = 1
=============================
#查询测试
#查询列
#1.指定列名查询
select id,name,sex from student
#2.查询所有
select * from student;
#3.通过表设置别名，指定列查询
select s.id,s.name,s.sex,s.* from student s
#4.查询常量列,并设置常量列的列名
select id,
name,
sex,
'仁和路小学' school,
'少先队员' as 身份
from student
#为列起别名: 1.列加空格后面写别名 2.as语法起别名
select id 学号,name 姓名,sex as 性别 from student
#针对列做运算
select id+10000 新学号,name 姓名 from student
2.去重查询/带条件修改/条件删除 【category/goods】
去重查询 —条件查询 —条件修改— 条件删除
#创建分类表
create table category
(
categoryId int primary key,
categoryName varchar(50) not null
)
select * from category;
insert into category
(categoryId,categoryName)
VALUES
(1,'食品'),
(2,'饮品'),
(3,'日用品')
create table goods
(
goodsId int primary key auto_increment,
goodsName varchar(200) not null,
price decimal(5,1) not null,
produceDate date not null,
address varchar(200) default '郑州',
categoryId int,
foreign key (categoryId)
references category(categoryId)
)
insert into goods
(goodsName,price,produceDate,address,categoryId)
values
('面包',2.5,'2018-01-01','郑州',1),
('红茶',5,'2015-02-02','武汉',2),
('鸡蛋',1,'2021-02-02','洛阳',1),
('绿茶',2.5,'2005-02-02','漯河',2),
('咖啡',20,'2019-02-02','广州',2),
('洗衣粉',18,'2001-02-02','广州',3),
('香皂',4,'2001-02-02','武汉',3),
('奶茶',9,'2022-02-02','郑州',2),
('饺子',32,'2022-03-02','郑州',1),
('辣条',0.5,'2001-02-08','洛阳',1)
#测试去重查询
select * from goods;
#查询商品的生产地
select distinct address from goods;
#去重查询商品所属分类？？
#条件查询
#where 列 关系运算符 值 逻辑运算符 其他关系运算
#关系运算符: >,>=,<,<=,==,!=,<> 不等于
#逻辑运算: and &&(逻辑与), or || (逻辑或),not (逻辑非)
select * from goods;
#查询商品编号大于5的,商品名字，价格，和产地
select #1.操作
goodsName,price,address #4.查询哪些列
from goods #2.针对哪个表
where goodsId>5 #3.筛选条件
#查询商品名字是鸡蛋的所有商品信息
select
*
from goods
where goodsName = '鸡蛋'
#查询生产日期是2017年以后的，并且产地是郑州的商品
select
*
from goods
where produceDate>'2017-01-01' and address='郑州'
#查询商品分类是食品，或者价格小于10元的，并且产地不是郑州的商品
SELECT
*
from goods
where (categoryId = 1 or price<10) and address!='郑州'
#范围判断:编号是1-3之间，生产日期 2010到2018
select
*
from goods
where goodsId between 1 and 3 #包含开始和结束
select
*
from goods
where producedate between '2010-01-01' and '2018-01-01'
#不连续的范围: 查询编号是1，3，5
SELECT
*
from goods
where goodsId in (1,3,5)
select
*
from goods
where goodsid=1 or goodsid= 3 or goodsId = 5
#不连续的范围: 查询编号不是1，3，5
SELECT
*
from goods
where not goodsId in (1,3,5)
select
*
from goods
where not (goodsid=1 or goodsid= 3 or goodsId = 5)
select
*
from goods
where goodsid!=1 and goodsid!=3 and goodsId!=5
#带条件修改:将价格小于5元的郑州生产的商品，价格加5元,并且生产日期改为今天
update goods
set price=price+5,
produceDate = now()
where price<5 and address='郑州'
select * from goods
where price<5 and address='郑州'
#select now()
#条件删除
#删除产地不是郑州的饮品并且价格高于20的商品
delete from goods where address!='郑州' and price>10 and categoryId = 2
select * from goods where address!='郑州' and price>10 and categoryId = 2
3.案例演练
#创建分类表
create table category
(
categoryId int primary key,
categoryName varchar(50) not null
)
select * from category;
insert into category
(categoryId,categoryName)
VALUES
(1,'食品'),
(2,'饮品'),
(3,'日用品')
create table goods
(
goodsId int primary key auto_increment,
goodsName varchar(200) not null,
price decimal(5,1) not null,
produceDate date not null,
address varchar(200) default '郑州',
categoryId int,
foreign key (categoryId)
references category(categoryId)
)
insert into goods
(goodsName,price,produceDate,address,categoryId)
values
('面包',2.5,'2018-01-01','郑州',1),
('红茶',5,'2015-02-02','武汉',2),
('鸡蛋',1,'2021-02-02','洛阳',1),
('绿茶',2.5,'2005-02-02','漯河',2),
('咖啡',20,'2019-02-02','广州',2),
('洗衣粉',18,'2001-02-02','广州',3),
('香皂',4,'2001-02-02','武汉',3),
('奶茶',9,'2022-02-02','郑州',2),
('饺子',32,'2022-03-02','郑州',1),
('辣条',0.5,'2001-02-08','洛阳',1)
===========================================
#查询价格大于5元的商品
#查询价格大于10元的并且产地是广州的商品
#查询商品类型是食品，或者价格小于5元的商品
#查询价格不小于5的商品
#查询产地不是广州的
#带条件修改
#1. 将产地是郑州的并且是食品的商品都上涨1元钱,并且生产日期改为当前日期
#2. 将价格小于5的并且是饮品的商品产地改为新乡
#带条件删除
#1. 删除生产日期小于2005-01-01的商品
===========================================
#查询价格大于5元的商品
select * from goods
where price > 5 ;
#查询价格大于10元的并且产地是广州的商品
select * from goods
where price > 10 and address = '广州';
#查询商品类型是食品，或者价格小于5元的商品
select * from goods
where categoryId = 1 or price < 5;
#查询价格不小于5的商品
select * from goods
where price >= 5;
#查询产地不是广州的
select * from goods
#where address != '广州';
where address not in ('广州');
#带条件修改
#1. 将产地是郑州的并且是《食品》的商品都上涨1元钱,并且生产日期改为当前日期
#先查找（该步可省略）
select * from goods
where address = '郑州' and categoryId = 1 ;
#修改数据
update goods
set price=price+1,
produceDate=now()
where address = '郑州' and categoryId = 1;
#校验（该步可省略）
select * from goods
where address = '郑州' and categoryId = 1 ;
#2. 将价格小于5的并且是饮品的商品产地改为新乡
#先查找（该步可省略）
select * from goods
where price < 5 and categoryId = 2 ;
#修改数据
update goods
set address = '新乡'
where price < 5 and categoryId = 2 ;
#校验（该步可省略）
select * from goods
where price < 5 and categoryId = 2 ;
#带条件删除
#1. 删除生产日期小于2005-01-01的商品
#先查找（该步可省略）
select * from goods
where produceDate < '2015-01-01' ;
#修改数据
delete from goods
where produceDate < '2015-01-01' ;
#校验（该步可省略）
select * from goods
where produceDate < '2015-01-01' ;
select * from goods;
4.模糊查询 like/正则表达式 regexp/多列排序 order by asc/desc
模糊匹配 —正则表达式 — 排序
参考代码：
insert into goods
(goodsName,price,produceDate,address,categoryId)
values
('面包康',2.5,'2018-01-01','郑州',1),
('面粉',5,'2015-02-02','武汉',1),
('米粉渣',1,'2021-02-02','洛阳',1),
('螺丝粉',2.5,'2005-02-02','漯河',1)
select * from goods
#模糊查询:不精确
#1.like
# a. % : 匹配任意长度的任意内容
# b. _ : 匹配一个长度的任意内容
#查询以面开头的所有商品
select * from goods where goodsName like '面%'
#查询包含粉的所有商品
select * from goods where goodsName like '%粉%'
#查询以面开头两个字的商品
select * from goods where goodsName like '面_'
#查询以面开头三个字的商品
select * from goods where goodsName like '面__'
#2.正则表达式
#查询以面开头的所有商品
select * from goods where goodsName REGEXP '^面'
#排序: 升序，降序
#关键字:order by asc(升序:默认操作)/desc(倒序)
#查询商品，按价格从低到高显示
select * from goods
order by price ASC
#查询商品，按价格从高到低显示
select * from goods
order by price desc
#查询产地是武汉的商品，按降序排列
SELECT
*
from goods
where address='武汉'
order by price desc
#多列排序
#查询商品，按生产日期升序排序，生产日期相同再按价格降序
SELECT
*
from goods
order by produceDate asc,price desc
5.limit 0716
参考代码：
#truncate table :清空表，可以让自增列重置
select * from goods
#limit:返回有限的若干条记录(前几条)
#语法: limit 开始位置,条数。开始位置不写的话，默认从0开始取之后的特定条数
#取前3条
select *
from goods
order by goodsId
limit 0,3
#获取第一条之后的3条
select *
from goods
limit 1,3
#获取第四条之后的5条
select *
from goods
limit 4,5
#用程序实现分页，要考虑的问题
#1.要访问第几页的数据 2.每页要显示几条
#3.每页的开始位置= （页码-1）*条数
#共10条记录，如果每页显示3条数据，会显示几页？ 3+1
#查询场景:
#1.访问第1页，每页3条
select *
from goods
order by goodsId
limit 0,3 # (1-1)*3 = 0
#2.访问第2页，每页3条
select *
from goods
order by goodsId
limit 3,3 #(2-1)*3 = 3
#3.访问第3页，每页3条。如何计算第3页的开始位置？
select *
from goods
order by goodsId
limit 6,3 # (3-1)*3 = 6
#取第4页
select *
from goods
order by goodsId
limit 9,3 # (4-1)*3 = 6
#查询价格的最低的前四条数据
#查询生产日期最新的一条数据
#每页4条，按照价格降序，查询第二页的数据
#查询价格的最低的前四条数据
select * from goods
order by price
limit 0,4 ;
select * from goods;
#查询生产日期最新的一条数据
select * from goods
order by produceDate desc
limit 0,1 ;
select * from goods;
#每页4条，按照价格降序，查询第二页的数据
select * from goods
order by price desc
limit 4,4 ;
6.sum/avg/max/min/count
#汇总函数/聚合函数：将一堆数据放在一起通过特定运算返回一个结果
#sum(列):求某列的和
#avg(列):求某列的平均值
#max(列):求某列的最大值
#min(列):求某列的最小值
#count(列名/*):求记录数
#查询所有商品的单价总和
select sum(price) as 价格和
from goods
#查询所有商品的平均价格
select avg(price) as 平均价格
from goods
#查询所有商品的最大价格
select max(price)
from goods
#查询所有商品的最小价格
select min(price)
from goods
#查询商品记录数
#count(*):把所有列作为整体来数数
select count(*) as 记录数
from goods
#根据某个列计数
#count(列名):如果列中数据为null，则不会被计数
select count(price)
from goods
select * from goods
update goods
set price = null
where goodsId = 1
#查询产地在郑州商品总个数，最大价格，最小价格，价格和，价格平均
select * from goods where address='郑州';
select count(address) as 产地总数,
max(price) as 最大价格,
min(price) as 最小价格,
sum(price) as 价格和,
avg(price) as 平均价格
from goods where address='郑州';
7.分组汇总 【exam】
参考代码：
#创建学生成绩表
create table exam
(
examId int primary key auto_increment,
stuName varchar(20),
courseName varchar(20),
score int
)
insert into exam
(stuName,courseName,score)
select '张三','语文',80 union
select '张三','数学',85 union
select '张三','英语',70 union
select '李四','语文',66 union
select '李四','数学',89 union
select '李四','英语',23 union
select '王五','语文',89 union
select '王五','英语',65 union
select '赵六','数学',99
#分组汇总
#分组:group by 列名
#注意:分组查询结构，只能有分组列和汇总结果。不能有其他列
#1.查询每个班级的人数
select * from student
SELECT
clsId 班级 ,count(*) 班级人数 #分组之后，进行汇总
from student
group by clsId #根据班级编号分组
#2.查询每个产地的商品数
select
address 产地 ,count(*) 商品数
from goods
group by address
#3.查询每个产地商品的最高价格,最低价格
#4.查询学生成绩中，每个学生的各科成绩和
#5.查询学生成绩中，每个科目的最高分和最低分
#3.查询每个产地商品的最高价格,最低价格
select address 产地,
max(price) 最高价格, #汇总结果1
min(price) 最低价格 #汇总结果2
from goods
group by address;
#创建学生成绩表
create table exam
(
examId int primary key auto_increment,
stuName varchar(20),
courseName varchar(20),
score int
);
#插入数据
insert into exam
(stuName,courseName,score)
select '张三','语文',80 union
select '张三','数学',85 union
select '张三','英语',70 union
select '李四','语文',66 union
select '李四','数学',89 union
select '李四','英语',23 union
select '王五','语文',89 union
select '王五','英语',65 union
select '赵六','数学',99 ;
#4.查询学生成绩中，每个学生的各科成绩和
select stuName 姓名,
sum(score) #汇总结果1
from exam
group by stuName ;
#5.查询学生成绩中，每个科目的最高分和最低分
select stuName 姓名,
max(score) 成绩最大值, #汇总结果1
min(score) 成绩最小值 #汇总结果2
from exam
group by stuName ;
#---------------------------------------------
#多列分组:
#查询每个班级的男女生人数
# 1班 男
# 1班 女
# 2班 男
# 2班 女
SELECT
clsId 班级,sex 性别,count(*) 人数
from student
group by clsId,sex #多列分组,班级,性别
/*
#查询每个班级的人数
select
clsId,count(*)
from student
group by clsId
#查询男女生(性别)分别有几人
select
sex,count(*)
from student
group by sex
*/
#分组数据过滤:having(针对分组数据过滤，用法和where类似)
#查询某个产地的商品个数大于2的记录
SELECT
address 产地,count(*) 商品个数
from goods
group by address
SELECT
address 产地,count(*) 商品个数
from goods
group by address
#where count(*)>2 #where不能针对分组后的数据过滤
having count(*)>=2
#分组汇总，然后过滤
#1.查询一下学生总分大于250的学生
#2.查询某个班级男生人数大于1的记录
#分组汇总，然后过滤
#1.查询一下学生总分大于210的学生（exam表）
select * from exam;
select stuName 姓名,
sum(score) 学生总成绩
from exam
group by stuName ;
select stuName 姓名,
sum(score) 学生总成绩
from exam
group by stuName
having sum(score) >210 ;
#2.查询某个班级男生人数大于1的记录 (student表)
select * from student;
select clsId 班级,
count(sex)
from student
group by clsId,sex;
select clsId 班级,
sex 性别,
count(sex)
from student
group by clsId,sex
having sex='男' and count(sex)>1;
8.课前测 case when…then…end 0718
#创建学生成绩表
create table exam
(
examId int primary key auto_increment,
stuName varchar(20),
courseName varchar(20),
score int
);
#插入数据
insert into exam
(stuName,courseName,score)
select '张三','语文',80 union
select '张三','数学',85 union
select '张三','英语',70 union
select '李四','语文',66 union
select '李四','数学',89 union
select '李四','英语',23 union
select '王五','语文',89 union
select '王五','英语',65 union
select '赵六','数学',99 ;
select * from exam;
#1.统计所有学生的总分平均分
SELECT
stuName 姓名,sum(score) 总分,avg(score) 平均分
from exam
#2.统计每个学生所有成绩的总分，平均分
SELECT
stuName 姓名,sum(score) 总分,avg(score) 平均分
from exam
group by stuName #分组
#3.查询参加了两门以上考试的学生
#a.确定每个学生考了几门(就是数数每个学生有几个分数)
#b.过滤考了两门以上的学生
SELECT
stuName 姓名,count(score) 考试门数
from exam
group by stuName
HAVING 考试门数>2 #having对分组之后的数据进行过滤
#统计每门课程的平均分，查询平均分在60分以上的课程
select
courseName 课程,avg(score) 平均分
from exam
group by courseName
having 平均分>60
#求输入的成绩的优良中差
#case when 列数据条件表达式 then 满足条件返回的结果 else 不满足条件返回的结果 end
# if(score>=90){ 优秀} else if {}
select
stuName 姓名,
courseName 科目,
score 分数,
case when score>=90 then '优秀'
when score>=80 then '良好'
when score>=60 then '中等'
else '差'
end as '等级'
from exam
select * from goods
#如果价格>10 贵 >5 中等 其他 便宜
select goodsName 商品名,
price 价格,
case when price>10 then '贵'
when price>5 then '中等'
else '便宜'
end as '等级'
from goods
9.行列转换
#行列转换，就是行数据转为列数据
select * from exam;
SELECT
stuName 姓名,
courseName 课程,
score 分数,
case when courseName='语文' then score end 语文, #当前行的课程是语文，则将语文成绩填到语文列
case when courseName='数学' then score end 数学,
case when courseName='英语' then score end 英语
from exam
SELECT
stuName 姓名,
max(case when courseName='语文' then score end) 语文, #当前行的课程是语文，则将语文成绩填到语文列
max(case when courseName='数学' then score end) 数学,
max(case when courseName='英语' then score end) 英语
from exam
group by 姓名
10.等值连接/内连接
#等值连接/内连接: 两个表都能对应上的数据才会被查询出来
#1.where条件实现等值连接 2.inner join
#使用inner join 实现等值连接(内连接)
#查询姓名，性别，班级名称
SELECT
s.name,s.sex,c.className
from student s
inner join classInfo c
on s.clsId = c.classId
#查询商品名字，价格，商品的分类名称
#inner join 等值连接
SELECT
g.goodsName,g.price,c.categoryName
from goods g
join category c #inner join 关联其他表
on g.categoryId = c.categoryId # on 后面写关联条件
#where 等值连接
SELECT
#g.*,c.*
g.goodsName,g.price,g.address,c.categoryName
from goods g,category c #多个表用逗号分隔
where g.categoryId = c.categoryId #where 后面写关联条件
#比如:添加一个新商品，但是新商品还没有分类
# 添加新分类，还没有商品关联这个分类
select * from goods;
select * from category;
insert into goods
(goodsName,price,produceDate,address,categoryId)
select '飞船',1000,'2000-01-01','太空基地',null UNION
select '导弹',200,'2019-01-01','军事基地',null
insert into category
(categoryId,categoryName)
select 4,'厨房用品' UNION
select 5,'五金用品'
11.外连接 左连接 右连接
#外连接: 非等值连接，某个表的数据会全部查询出来，不管另外一个表是否有等值数据
#左外连接(left outer join)，右外连接(right outer join)
#左外连接:
#1.以左表为主，左表数据全部显示
#2.右表匹配左表，匹配上的显示，匹配不上的不显示
#用内连接会把商品中的飞船和导弹，过滤掉。也会把分类中的五金用品和厨房用品过滤掉。
select
g.*,c.*
from goods g
join category c
on g.categoryId = c.categoryId
#左外连接
SELECT
g.* , c.*
from goods g #写在前边的表就成为左表
left join category c #写在后面的表称为右表
on g.categoryId = c.categoryId
#右外连接:
#1.以右表为主，右表数据全部显示
#2.左表匹配右表，匹配上的显示，匹配不上的不显示
SELECT
g.*,c.*
from goods g #左表
right join category c #右表
on g.categoryId = c.categoryId
#交叉连接 cross join (不写条件就是笛卡尔积)
select
g.*,c.*
from goods g
cross join category c
12.演练 四表 内/外链接 【classInfo/studentInfo/courseInfo/examInfo】
#创建班级表
create table classInfo
(
classId int primary key auto_increment,
className varchar(20)
)
select * from classInfo;
insert into classInfo
(className)
values
('AAA01'),
('AAA02')
#创建学生表
create table studentInfo
(
studentId int primary key auto_increment,
name varchar(20) not null,
sex char(1) not null,
birthday date,
province varchar(20) default '河南',
classId int,
foreign key (classId)
references classInfo(classId)
)
select * from studentInfo;
insert into studentInfo
(name,sex,birthday,province,classId)
values
('张三','男','2002-01-01','湖北',1),
('李四','女','2003-01-05','河南',2),
('王五','男','2010-03-01','湖北',1),
('赵六','男','2009-01-08','河南',2),
('孙琪','女','2001-09-01','湖北',1)
#创建课程表
create table courseInfo
(
courseId int primary key auto_increment,
courseName varchar(20) not null
)
select * from courseInfo;
insert into courseInfo
(courseName)
values
('html'),
('java'),
('sql')
#创建成绩表
create table examInfo
(
examId int primary key auto_increment,
studentId int not null,
courseId int not null,
score int,
foreign key (studentId)
REFERENCES studentInfo(studentId), #外键对应的类型必须一致
foreign key (courseId)
REFERENCES courseInfo(courseId)
)
select * from examInfo;
select * from studentInfo;
insert into examInfo
(studentId,courseId,score)
VALUES
(1,1,90),
(1,2,70),
(1,3,65),
(2,1,88),
(2,2,67),
(2,3,55),
(3,1,69),
(3,3,45)
#前提:2个班级 5个学生 3门课程 8个成绩
# 其中3个学生参加了考试有成绩，2个学生没有参加考试，没有成绩
#1.请查询有成绩的所有学生的姓名，考试科目，和对应的分数
# 意图:就是查询在成绩表有记录的学生，所以要用内连接
SELECT
s.name,c.courseName,e.score
from studentinfo s,courseInfo c, examinfo e
where s.studentId = e.studentId
and c.courseId = e.courseId
select
s.name,c.courseName,e.score
from studentInfo s
join examInfo e
on s.studentId = e.studentId
join courseInfo c
on e.courseId = c.courseId
#2.请查询所有学生的姓名，考试科目和对应的分数
# 意图:查询所有学生，也就是不管有没有成绩都要查询出来，所以用外连接
SELECT
s.name,c.courseName,e.score
from studentInfo s
left join examinfo e
on s.studentId = e.studentId
left join courseinfo c
on e.courseId = c.courseId
#问题:这两个查询，哪个用内连接，哪个用外连接
// A code block
var foo = 'bar';
1.增删改语句 0715
1.1区分delect、truncate、drop
1 delect语句(只删除表中数据，不删除表)
注意：有外键关联的表，必须先删除从表，在删除主表
2 truncate语句(只删除整个表中数据，不删除表)
注意：有外键关联的表，必须先删除从表，在删除主表
3drop 删除表本身
从这里开始，运行结果只展示主要的部分，代码尽可能的展示全部、多+注释解析（关键步骤前都会+注释）。
代码的运行和调试步骤太多，截图整理成笔记的话时间开销太大。不然我可能会跟不上课程进度的。。。。。。改进方法ing。。。
代码如下：
#删除/查询 0715
select * from classinfo;
select * from student;
desc classinfo;
desc student;
insert into classinfo
(className) #注意；这里直接使用select..union..select..;不需要再写values
select 'aaa01' union
select 'bbb01' ;
select * from classinfo;
desc classinfo;
insert into student
(name,sex,address,clsId)
select '张三','男','河南',1 union
select '李四','女','河北',1 union
select '王五','女','河北',2 ;
select * from student;
desc student;
#delete 可带条件删除也可整表删除，删除数据自增不会重置
delete from student where id=4;
#delete from student; #只能先删除从表中的数据再删主表的数据
select * from student;
desc student;
#truncate 只能整表删除 能够重置自增列
truncate table student; #truncate 截断 #只能先删除从表的数据再删主表的数据
truncate table classinfo;
select * from classinfo;
desc classinfo;
select * from student;
desc student;
#drop删除表（表本身） 包括表中所有的数据
#有外键关联的表，必须先删除从表，在删除主表
drop table student; #删除创建的student表
drop table classinfo; #上方的表名为classinfo，下方为classInfo
#创建classinfo表
create table classInfo #创建新的classInfo表
(
classId int primary key auto_increment,
clsName varchar(20) not null
);
select * from classInfo;
desc classInfo;
#创建student表
create table student #创建新的student表
(
id int primary key auto_increment,
name varchar(20),
sex char(1),
address varchar(20),
clsId int, #注意最后一个逗号在这里
foreign key(clsId)
references classInfo(classId)
on delete cascade #删除级联 cascade 级联
on update cascade #修改级联
);
select * from student;
desc student;
insert into classInfo
(clsName)
select 'aaa01' union
select 'bbb01';
select * from classInfo;
desc classInfo;
insert into student
(name,sex,address,clsId)
select '张三','男','河南',1 union
select '李四','女','河北',1 union
select '王五','女','河北',2 ;
select * from student;
desc student;
#测试级联修改
update classInfo
set classId=20
where classId=2
select * from classInfo; #classId=20
desc classInfo;
select * from student; #clsId=20
desc student;
#测试级联删除
delete from classInfo where classId=1;
select * from classInfo; #classId=20
desc classInfo;
#查询测试
#查询列
#1.指定列明查询
select id,name,sex from student;
#查询所有
select * from student;
#通过表设置别名，指定列查询
select s.id,s.name,s.sex,s.* from student s;
#表中原不存在，可以清晰的查看表格中的数据（花哨的表面操作：只能在控制台展示运行结果时根据设定显示，原表中的数据不会发生改变）
select * from student;
#查询常量列，并设置常量列的列名
select id,
name,
sex,
'仁和小学' school, #也挺花哨的
'少先队员' as 身份
from student
select * from student;
#为列起别名：1.列加空格后面写别名 2.as语法起别名
select id 学号,name 姓名,sex as 性别 from student;
#针对列做运算
select id+10000 新学员,name 姓名 from student;
2.去重查询/带条件修改/条件删除
条件修改
update goods
set price=price+5,
produceDate=now() #now() 今天的年月日
where price < 5 and address = '郑州';
代码如下：
#删除之前存在的表
drop table good; #注意删除顺序
drop table category;
#检验是否删除成功
select * from good;
select * from category;
#创建category表 并插入数据
create table category
(
categoryId int primary key,
categoryName varchar(50) not null
);
#插入数据
insert into category
values
(1,'食品'),
(2,'饮料'),
(3,'日用品');
select * from category;
#创建goods表 并插入数据
create table goods
(
goodsId int primary key auto_increment,
goodsName varchar(200) not null,
price decimal(5,1) not null,
produceDate date not null,
address varchar(200) default '郑州',
categoryId int,
foreign key(categoryId)
references category(categoryId)
);
insert into goods
(goodsName,price,produceDate,address,categoryId)
values
('面包',2.5,'2018-01-01','郑州',1),
('红茶',5,'2015-02-02','武汉',2),
('鸡蛋',1,'2021-02-02','洛阳',1),
('绿茶',2.5,'2005-02-02','漯河',2),
('咖啡',20,'2019-02-02','广州',2),
('洗衣粉',18,'2001-02-02','广州',3),
('香皂',4,'2001-02-02','武汉',3),
('奶茶',9,'2022-02-02','郑州',2),
('饺子',32,'2022-03-02','郑州',1),
('辣条',0.5,'2001-02-08','洛阳',1) ;
select * from goods;
#测试去重查询
select distinct address from goods; #查询商品的生产地
#去重查询商品所属类别??
#条件查询
#where 列 关系运算符 值 逻辑运算符 其他关系运算
#关系运算符: >,>=,<,<=,==,!=,<> 不等于
#逻辑运算: and &&(逻辑与), or || (逻辑或),not (逻辑非)
#查询商品编号大于5的商品名字、价格和产地
select
goodsName,price,address
from goods
where goodsId > 5;
#查询名字是鸡蛋的所有商品信息
select
*
from goods
where goodsName = '鸡蛋';
#查询商品信息是2017年以后的，且产地是郑州的商品
select
*
from goods
where produceDate > '2017-01-01' and address = '郑州' ;
#范围判断:编号是1-3之间(#生产日期 2010到2018)
select
*
from goods
where goodsId between 1 and 3;
#where produceDate between '2010-01-01' and '2018-01-01';
#不连续的范围：
#查询编号是1、3、5的商品
select
*
from goods
#where goodsId = 1 or goodsId = 3 or goodsId = 5;
where goodsId not in (2,4,6,7,8,9,10);
-- where goodsId in (1,3,5);
#查询编号是不是1、3、5的商品
select
*
from goods
where goodsId != 1 and goodsId != 3 and goodsId != 5 ;
#带条件修改:
#将价格小于5元的郑州生产的商品，价格加5元,并且生产日期改为今天
-- #原始数据
-- select price,produceDate from goods
-- where price < 5 and address = '郑州';
update goods
set price=price+5,
produceDate=now() #now() 今天的年月日
where price < 5 and address = '郑州';
#校验（一个数小于5大于0然后+5的话，肯定现在>5）
select * from goods
where price < 5 and address = '郑州'; #因为最小的价格+5变大了，所以查询不到
select * from goods
where address = '郑州'; #因为地址不变，这一步的结果可以对比修改数据之前的数据
#条件删除
#删除产地不是郑州的《饮品》并且价格高于10的商品
#先查看满足的商品
select * from goods
where address != '郑州' and price > 10 and categoryId = 2;
#删除
delete from goods #注意： 这里不加 * ！！！！！！
where address != '郑州' and price > 10 and categoryId = 2;
#校验
select * from goods
where address != '郑州' and price > 10 and categoryId = 2;
3.案例演练
补全以下代码：
#创建分类表
create table category
(
categoryId int primary key,
categoryName varchar(50) not null
)
select * from category;
insert into category
(categoryId,categoryName)
VALUES
(1,'食品'),
(2,'饮品'),
(3,'日用品')
create table goods
(
goodsId int primary key auto_increment,
goodsName varchar(200) not null,
price decimal(5,1) not null,
produceDate date not null,
address varchar(200) default '郑州',
categoryId int,
foreign key (categoryId)
references category(categoryId)
)
insert into goods
(goodsName,price,produceDate,address,categoryId)
values
('面包',2.5,'2018-01-01','郑州',1),
('红茶',5,'2015-02-02','武汉',2),
('鸡蛋',1,'2021-02-02','洛阳',1),
('绿茶',2.5,'2005-02-02','漯河',2),
('咖啡',20,'2019-02-02','广州',2),
('洗衣粉',18,'2001-02-02','广州',3),
('香皂',4,'2001-02-02','武汉',3),
('奶茶',9,'2022-02-02','郑州',2),
('饺子',32,'2022-03-02','郑州',1),
('辣条',0.5,'2001-02-08','洛阳',1)
===========================================
#查询价格大于5元的商品
#查询价格大于10元的并且产地是广州的商品
#查询商品类型是食品，或者价格小于5元的商品
#查询价格不小于5的商品
#查询产地不是广州的
#带条件修改
#1. 将产地是郑州的并且是食品的商品都上涨1元钱,并且生产日期改为当前日期
#2. 将价格小于5的并且是饮品的商品产地改为新乡
#带条件删除
#1. 删除生产日期小于2005-01-01的商品
===========================================
代码如下：
-- #===========================================
-- #查询价格大于5元的商品
--
-- #查询价格大于10元的并且产地是广州的商品
--
-- #查询商品类型是食品，或者价格小于5元的商品
--
-- #查询价格不小于5的商品
--
-- #查询产地不是广州的
--
-- #带条件修改
-- #1. 将产地是郑州的并且是食品的商品都上涨1元钱,并且生产日期改为当前日期
--
-- #2. 将价格小于5的并且是饮品的商品产地改为新乡
--
-- #带条件删除
-- #1. 删除生产日期小于2005-01-01的商品
--
-- #===========================================
#查询价格大于5元的商品
select * from goods
where price > 5 ;
#查询价格大于10元的并且产地是广州的商品
select * from goods
where price > 10 and address = '广州';
#查询商品类型是食品，或者价格小于5元的商品
select * from goods
where categoryId = 1 or price < 5;
#查询价格不小于5的商品
select * from goods
where price >= 5;
#查询产地不是广州的
select * from goods
#where address != '广州';
where address not in ('广州');
#带条件修改
#1. 将产地是郑州的并且是《食品》的商品都上涨1元钱,并且生产日期改为当前日期
#先查找（该步可省略）
select * from goods
where address = '郑州' and categoryId = 1 ;
#修改数据
update goods
set price=price+1,
produceDate=now()
where address = '郑州' and categoryId = 1;
#校验（该步可省略）
select * from goods
where address = '郑州' and categoryId = 1 ;
#2. 将价格小于5的并且是饮品的商品产地改为新乡
#先查找（该步可省略）
select * from goods
where price < 5 and categoryId = 2 ;
#修改数据
update goods
set address = '新乡'
where price < 5 and categoryId = 2 ;
#校验（该步可省略）
select * from goods
where price < 5 and categoryId = 2 ;
#带条件删除
#1. 删除生产日期小于2005-01-01的商品
#先查找（该步可省略）
select * from goods
where produceDate < '2015-01-01' ;
#修改数据
delete from goods
where produceDate < '2015-01-01' ;
#校验（该步可省略）
select * from goods
where produceDate < '2015-01-01' ;
select * from goods;
运行结果：
4.模糊查询 like/正则表达式 regexp/多列排序 order by asc/desc
运行结果：
代码如下：
#删除goods表中存在的数据
#delete from goods; #只删除数据,不删除表的格式.delect不重置自增列
truncate table goods; #只删除数据,不删除表的格式，truncate重置自增
select * from goods;
#插入数据
insert into goods
(goodsName,price,produceDate,address,categoryId)
values
('面包康',2.5,'2018-01-01','郑州',1),
('面粉',5,'2015-02-02','武汉',1),
('米粉渣',1,'2021-02-02','洛阳',1),
('螺丝粉',2.5,'2005-02-02','漯河',1) ;
select * from goods ;
#1.模糊匹配 ：不精确 like
# % ：匹配任意长度的任意内容
# _ : 匹配一个长度的任意内容
#查询以面开头的所有商品
select * from goods where goodsName like '面%';
#查询包含粉的所有商品
select * from goods where goodsName like '%粉%';
#查询以面开头两个字的商品
select * from goods where goodsName like '面_';
#查询以面开头的三个字的商品
select * from goods where goodsName like '面__';#注意：__为两个下划线
#2正则表达式 regexp:正则表达式
#查询以面开头的所有商品
select * from goods where goodsName regexp '^面';
#3.排序 ：升序 降序
#关键字：order by 列名 asc(升序：默认操作)/desc (倒序)
#查询商品，按价格从低到高显示
select * from goods
order by price asc;
#查询商品，按价格从高到低显示
select * from goods
order by price desc;
#查询地址是武汉的商品，按降序排序
select * from goods
where address = '武汉'
order by goodsId desc ;
#多行排序
#查询商品，按生产日期升序排序，生产日期相同再按价格降序排序
select * from goods
order by produceDate asc,price desc ;
5.课前测 0716
#创建数据库
create database 70716_db1 #今日份命名
default character set utf8mb4 #设置字符集
default collate utf8mb4_general_ci #设置排列规则
#创建表
create table edu
(
eduId int primary key auto_increment,
eduName char(20) not null
);
select * from edu;
create table emp
(
empld int primary key auto_increment,
empName char(20) not null,
sex char(1),
hireDate date not null,
phone varchar(12) unique,
eduId int,
foreign key(eduId) references edu(eduId)
);
select * from emp;
#插入数据
insert into edu
(eduId,eduName)
values
(1,'大专'),
(2,'本科'),
(3,'研究生');
insert into emp
(empName,sex,hireDate,phone,eduId)
values
('张三','男','2017-01-01','13112323451',1),
('李四','女','2020-01-01','13112323452',2),
('王五','男','2016-01-01','13112323453',3),
('赵六','女','2016-01-01','13112323454',1),
('孙奇','女','2016-01-01','13112323455',2);
select * from edu;
select * from emp;
select * from emp where hireDate<'2018-01-01';
select * from emp where eduId=1 or eduId=3;
delete from emp where empName='张三' or empName='李四';
select * from emp;
6.limit
代码补充：
#查询价格的最低的前四条数据
select * from goods
order by price
limit 0,4 ;
select * from goods;
#查询生产日期最新的一条数据
select * from goods
order by produceDate desc
limit 0,1 ;
select * from goods;
#每页4条，按照价格降序，查询第二页的数据
select * from goods
order by price desc
limit 4,4 ;
7.sum/avg/max/min/count
代码如下：
#sum(列):求某列的和
#avg(列):求某列的平均值
#max(列):求某列的最大值
#min(列):求某列的最小值
#count(列名/*):求记录数
#查询所有商品的单价总和
select sum(price) as 价格和
from goods
#查询所有的商品的平均价格
select avg(price) as 平均价格
from goods
#查询所有商品的最大价格
select max(price) as 最大价格
from goods
#查询所有商品的最小价格
select min(price) as 最小价格
from goods
#查询商品的记录数 count(*)所有列作为整体来计数
select count(*)
from goods
#根据商品的某列计数 count(列名)
select count(price)
from goods
#打开表的设计表，然后手动调节价格可为空
#更新修改数据
update goods
set price=null
where goodsId=1;
select count(price)
from goods;
#查询产地在郑州商品总个数，最大价格，最小价格，价格和，价格平均
select * from goods where address='郑州';
select count(address) as 产地总数,
max(price) as 最大价格,
min(price) as 最小价格,
sum(price) as 价格和,
avg(price) as 平均价格
from goods where address='郑州';
8.分组汇总
代码如下：
#创建表
create table classInfo
(
classId int primary key auto_increment,
className varchar(20) not null
)
create table student
(
id int primary key auto_increment,
name varchar(20),
sex char(1),
address varchar(20),
clsId int, #班级编号
foreign key (clsId)
REFERENCES classInfo(classId)
on delete CASCADE #删除级联
on update CASCADE #修改级联
)
#插入数据
insert into classinfo
(className)
select 'aaa01' UNION
select 'aaa02';
insert into student
(name,sex,address,clsId)
select '张三','男','河南',1 union
select '李四','女','河北',1 union
select '王五','女','河北',2 ;
select * from classInfo;
select * from student;
#分组汇总
#分组:group by 列名
#注意:分组查询结构，只能有分组列和汇总结果(一列或多列)。不能有其他列
#1.查询每个班级的人数
select
clsId 班级,count(*) 班级人数
from student
group by clsId; #根据班级编号分组
select
stuName 姓名 ,count(*) 学科数 #分组之后，进行汇总
from exam
group by stuName; #根据学生姓名分组
#2.查询每个产地的商品数
select
address 产地 ,count(*) 商品数
from goods
group by address;
#3.查询每个产地商品的最高价格,最低价格
select address 产地,
max(price) 最高价格, #汇总结果1
min(price) 最低价格 #汇总结果2
from goods
group by address;
#创建学生成绩表
create table exam
(
examId int primary key auto_increment,
stuName varchar(20),
courseName varchar(20),
score int
);
#插入数据
insert into exam
(stuName,courseName,score)
select '张三','语文',80 union
select '张三','数学',85 union
select '张三','英语',70 union
select '李四','语文',66 union
select '李四','数学',89 union
select '李四','英语',23 union
select '王五','语文',89 union
select '王五','英语',65 union
select '赵六','数学',99 ;
#4.查询学生成绩中，每个学生的各科成绩和
select stuName 姓名,
sum(score) #汇总结果1
from exam
group by stuName ;
#5.查询学生成绩中，每个科目的最高分和最低分
select stuName 姓名,
max(score) 成绩最大值, #汇总结果1
min(score) 成绩最小值 #汇总结果2
from exam
group by stuName ;
参考代码：
#---------------------------------------------
#多列分组:
#查询每个班级的男女生人数
# 1班 男
# 1班 女
# 2班 男
# 2班 女
SELECT
clsId 班级,sex 性别,count(*) 人数
from student
group by clsId,sex #多列分组,班级,性别
/*
#查询每个班级的人数
select
clsId,count(*)
from student
group by clsId
#查询男女生(性别)分别有几人
select
sex,count(*)
from student
group by sex
*/
#分组数据过滤:having(针对分组数据过滤，用法和where类似)
#查询某个产地的商品个数大于2的记录
SELECT
address 产地,count(*) 商品个数
from goods
group by address
SELECT
address 产地,count(*) 商品个数
from goods
group by address
#where count(*)>2 #where不能针对分组后的数据过滤
having count(*)>=2
#分组汇总，然后过滤
#1.查询一下学生总分大于250的学生
#2.查询某个班级男生人数大于1的记录
代码如下：
#创建表
create table classInfo
(
classId int primary key auto_increment,
className varchar(20) not null
)
create table student
(
id int primary key auto_increment,
name varchar(20),
sex char(1),
address varchar(20),
clsId int, #班级编号
foreign key (clsId)
REFERENCES classInfo(classId)
on delete CASCADE #删除级联
on update CASCADE #修改级联
)
#插入数据
insert into classinfo
(className)
select 'aaa01' UNION
select 'aaa02';
insert into student
(name,sex,address,clsId)
select '张三','男','河南',1 union
select '李四','女','河北',1 union
select '王五','女','河北',2 ;
select * from classInfo;
select * from student;
#分组汇总
#分组:group by 列名
#注意:分组查询结构，只能有分组列和汇总结果(一列或多列)。不能有其他列
#1.查询每个班级的人数
select
clsId 班级,count(*) 班级人数
from student
group by clsId; #根据班级编号分组
select
stuName 姓名 ,count(*) 学科数 #分组之后，进行汇总
from exam
group by stuName; #根据学生姓名分组
#2.查询每个产地的商品数
select
address 产地 ,count(*) 商品数
from goods
group by address;
#3.查询每个产地商品的最高价格,最低价格
select address 产地,
max(price) 最高价格, #汇总结果1
min(price) 最低价格 #汇总结果2
from goods
group by address;
#创建学生成绩表
create table exam
(
examId int primary key auto_increment,
stuName varchar(20),
courseName varchar(20),
score int
);
#插入数据
insert into exam
(stuName,courseName,score)
select '张三','语文',80 union
select '张三','数学',85 union
select '张三','英语',70 union
select '李四','语文',66 union
select '李四','数学',89 union
select '李四','英语',23 union
select '王五','语文',89 union
select '王五','英语',65 union
select '赵六','数学',99 ;
#4.查询学生成绩中，每个学生的各科成绩和
select stuName 姓名,
sum(score) #汇总结果1
from exam
group by stuName ;
#5.查询学生成绩中，每个科目的最高分和最低分
select stuName 姓名,
max(score) 成绩最大值, #汇总结果1
min(score) 成绩最小值 #汇总结果2
from exam
group by stuName ;
#多列分组
#表中学生太少，再插入一倍
insert into student
(name,sex,address,clsId)
select '张三2','男','河南',1 union
select '李四2','女','河北',1 union
select '王五2','女','河北',2 ;
select * from student;
#查询每个班级的男女生人数
# 1班 男
# 1班 女
# 2班 男
# 2班 女
select clsId 性别,
sex 性别,
#count(sex)
count(*)
from student
group by clsId,sex ;
select * from goods;
#分组数据过滤：having(针对分组数据过滤，用法和where类似)
#查询某个产地的商品种类个数大于等于2的记录
select address 产地,
count(*) 商品种类数
from goods
group by address;
select address 产地,
count(*) 商品种类
from goods
group by address
#where count(*) >= 2; #报错！ #where不能针对分组后的数据过滤
select address 产地,
count(*) 商品种类
from goods
group by address
having count(*) >= 2;
-- #分组汇总，然后过滤
-- #1.查询一下学生总分大于210的学生
-- #2.查询某个班级男生人数大于1的记录
#分组汇总，然后过滤
#1.查询一下学生总分大于210的学生（exam表）
select * from exam;
select stuName 姓名,
sum(score) 学生总成绩
from exam
group by stuName ;
select stuName 姓名,
sum(score) 学生总成绩
from exam
group by stuName
having sum(score) >210 ;
#2.查询某个班级男生人数大于1的记录 (student表)
select * from student;
select clsId 班级,
count(sex)
from student
group by clsId,sex;
select clsId 班级,
sex 性别,
count(sex)
from student
group by clsId,sex
having sex='男' and count(sex)>1;
9.课前测 0718
#创建学生成绩表
create table exam
(
examId int primary key auto_increment,
stuName varchar(20),
courseName varchar(20),
score int
);
#插入数据
insert into exam
(stuName,courseName,score)
select '张三','语文',80 union
select '张三','数学',85 union
select '张三','英语',70 union
select '李四','语文',66 union
select '李四','数学',89 union
select '李四','英语',23 union
select '王五','语文',89 union
select '王五','英语',65 union
select '赵六','数学',99 ;
select * from exam;
select stuName 姓名,
sum(score) 总分,
avg(score) 平均分
from exam
group by stuName;
select * from exam;
select stuName 姓名,
count(score) 考试科目数
from exam
group by stuName
having count(score)>=2;
select * from exam;
select courseName 科目,
avg(score) 科目平均分
from exam
group by courseName
having avg(score)>=60;
select * from exam;
运行结果如下：
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/118111.html
