1、范围查询
between and 查询连续范围内的数据，包含左右边间，是一个闭区间
between and 必须从小值到大值进行范围划分，否则没有数据
in 查询不连续区间内的数据，in （数据1， 数据2 。。。。。）
格式：select 列名 from 表名 where 字段名 in （数据1， 数据2， 数据3 。。。。）
# 范围查询
#betweenand连续范围查询
# 需求: 查找,价格在200-2000范围内的所有商品,包含起始和终止位置,是一个闭区间[]
SELECT*FROMproductWHEREpriceBETWEEN200AND2000;
#in不连续范围查询
# 需求:查找价格是200,800,2000,5000的所有商品的全部信息
SELECT*FROMproductWHEREpriceIN(200,800,2000,5000);
# 回顾之前的题目 查询在安徽和北京的同学
#SELECT*FROMstudent_infoWHEREhometowninnot('安徽','北京');
# 练习:
#1/查询价格在600-2000范围内的所有商品的名称和价格
#between只能从小到大设置范围
SELECTpname, priceFROMproductWHEREpriceBETWEEN600AND2000;
#SELECTpname, priceFROMproductWHEREpriceBETWEEN2000AND600;
#2/查询名字为真维斯,杰克琼斯以及香奈儿的所有商品
SELECT*FROMproductWHEREpnameIN('真维斯','杰克琼斯','香奈儿');
2、逻辑查询
逻辑运算符
and 逻辑与 同真即真
or 逻辑或 同假即假
not 逻辑非 非真即假，非假即真
# 逻辑运算符
#and逻辑与? 同真即真
#or逻辑或? 同假即假
#not逻辑非? 真变假,假变真
# 如果条件成立,我们定义其结果为True
# 如果条件不成立,我们定义其结果为False
#TrueandTrue?? True真
#TrueandFalse?? False假
#FalseandTrue?? False假
#FalseandFalse?? False假
#TrueorTrue?? True真
#TrueorFalse?? True真
#FalseorTrue?? True真
#FalseorFalse?? False假
#notFalse?? True真
#notTrue?? False假
# 第一题not((Trueand4>5)or3>2) ??? False
# 需求: 查询价格在200-2000之间的所有商品,如果需要包含边界,则需要使用大于等于和小于等于
SELECT*FROMproductWHEREprice <=2000ANDprice >=200;
# 需求:查询价格不再200-2000范围内的所有商品信息
SELECT*FROMproductWHERENOT(price <=2000ANDprice >=200);
# 方法二:
SELECT*FROMproductWHEREprice >2000ORprice <200;
3、模糊查询
like关键字，可以进行模糊查询:根据指定规则进行查询
%：代表0个或多个字符
_：代表一个字符
格式： select 列名 from 表名 where 字段 like 规则；
# 模糊查询:根据指定规则进行查询,满足规则将被筛选出来,不满足规则将隐藏
#LIKE关键字可以进行模糊查询
# 通配符: %代表任意多个字符(可以是0可以是n) _代表任意一个字符(有且只有一个)
# 格式:select列名from表名where字段like'规则';
# 需求:查询名字以"斯"结尾的所有商品
SELECT*FROMproductWHEREpnameLIKE'%斯';
# 需求,查询名字以"海"开头的所有商品
SELECT*FROMproductWHEREpnameLIKE'海%';
# 需求:查询名字中带有"霸"的所有商品
SELECT*FROMproductWHEREpnameLIKE'%霸%';
# 需求:查询名字是两个字的所有商品
SELECT*FROMproductWHEREpnameLIKE'__';
# 查询名字是三个字符,且结尾为斯的商品信息
SELECT*FROMproductWHEREpnameLIKE'__斯';
# 字符: 每一个符号,或者文字,占用一个字符
# 字节: 空间单位,每个符号或者文字占用的字节数不相同,比如 a b 占用一个字节 汉字 占用三个字节
#charvarchar后边的括号内填写的是字符数
4、非空查询
is null 判断是否为空
is not null 判断是否不为空
格式： select 列名 from 表名 where 字段名 is （not） null；
# 我们怎样判断空值?
# 格式:select 列名from表名where字段名isnull;(筛选出字段为空的记录)
# 格式:select 列名from表名where字段名isnotnull;(筛选出字段不为空的记录)
# 需求: 删除pid =10的category_id 的值
UPDATEproductSETcategory_id =NULLWHEREpid =10;
# 需求: 查询所有商品中,category_id为空的商品
#null是不能和其他数据类型进行比较的
# 如果需要判断当前数据是否为空值,则使用isnull判断
SELECT*FROMproductWHEREcategory_idISNULL;
# 需求:查询所有商品中category_id不为空的商品
SELECT*FROMproductWHEREcategory_idISNOTNULL;
SELECT*FROMproductWHERENOT(category_idISNULL);
# 查询所有商品中category_id为空 且 pname 不为空的商品(加上括号易于阅读,并且不容易出错)
SELECT*FROMproductWHERE(category_idISNULL)AND(pnameISNOTNULL);
5、排序查询
order by：排序的关键字，可以构建指定字段，指定规则的排序
格式： select 列名 from 表名 where 条件 order by 字段名 排序规则(asc 升序、 desc 降序)；
如果按照多个字段进行排序，先按照排在前边的字段进行排序，如果排序值相同，则按照后边的规则排序。
# 排序查询
#orderby
#asc升序desc降序
# 格式:select类名from表名where条件orderby字段名 排序规则(asc/desc)
# 需求:查询所有商品,并按照价格进行排序(降序排列)
SELECT*
FROM
product
ORDERBY
priceDESC;
# 需求:查询所有商品中,价格大于2000的商品并按照升序进行排列.
# 如果需要升序排列,则不需要使用ASC,默认就是升序排列
SELECT*FROMproductWHEREprice >2000ORDERBYpriceASC;
# 可不可以对两个字段进行排序呢?
# 按照多个字段规则进行排序,则先按照最前面的规则排序,如果排序过程中,值相同,则按照后边的规则进行排序
# 需求:按照categroy_id进行升序排列,如果categroy_id相同,则按照价格进行降序排列
SELECT*FROMproductORDERBYcategory_idASC, priceDESC;
# 需求:按照价格进行降序排列,如果价格相同,则按照category_id进行升序排列
SELECT*FROMproductORDERBYpriceDESC, category_id;
# 文本型数据排序规则
# 没有数据 < 有数据
# 排序按照编码表顺序排序 排在前边的小 排在后边的大0-9依次递增 < A-Z 依次递增 < a - z 依次等
# 文本型数据比较大小按位比较,第一位进行比较如果值大则大,值小则小,如果相同,则比较第二位
#'9'>'20'
#'小2'>'99999999'
#'a'>'Z'
练习题
# 需求:
#1/查询(select)所有产品中,名字是两个字符(namelike'__'),并且(and)价格大于200的(price >200)商品,查询完成后按照价格降序排列(orderbypricedesc)
SELECT*FROMproductWHEREpnameLIKE'__'ANDprice >200ORDERBYpriceDESC;
#2/查询(select)所有商品中大于2000(price >2000) 或者(or)小于200(price <200)的商品中categroy_id(category_idisnotnull)不为空的所有记录
SELECT*FROMproductWHERE(price >2000ORprice <200)ANDcategory_idISNOTNULL;
#3/查询(select)所有上商品中,价格与category_id都不为空(priceisnotnullandcategory_idisnotnull),且价格不再(200.400.600.800)范围内的商品(pricenotin(200,400,600,800))
SELECT*FROMproductWHERE(category_idISNOTNULL)AND(priceISNOTNULL)ANDpriceNOTIN(200,400,600,800);
# 这种写法是错误的,书写逻辑为,判断category_id和price使用逻辑与运算得到的结果是否为null,并不是判断这两个字段的值是否为null
#SELECT*FROMproductWHERE(category_idANDprice)ISNOTNULLANDpriceNOTIN(200,400,600,800);
6、聚合函数
将整列数据进行聚合，计算成一个值的函数，如最大值，最小值，计数，求和，平均值等。
count 计数
sum 求和
max 最大值
min 最小值
avg 平均值
# 聚合查询 调用聚合函数:函数名(参数)
# 格式:select 聚合函数(字段名/*) from 表名;
# count 计数
# 需求:获取当前学员的总数
# 使用count(*)的时候,必须当前记录中所有字段都为空才能被忽略
SELECT count(*) FROM product; # 13
# 使用聚合函数会忽略控制,在计算category_id的数量的时候没有统计空值
SELECT count(category_id) FROM product; # 12
# 查询 price 和category_id都不为空的数据的个数 如下写法错误!
# SELECT count(category_id, price) FROM product;
# sum 求和
# 需求:将所有商品都购买一个一共需要多少钱
# sum 求和一样忽略了空值
SELECT sum(price) FROM product;
# max 最大值
# 需求:所有商品中最贵的商品价格是多少?
SELECT max(price) FROM product;
# min 最小值
# 需求,所有商品中最便宜的商品价格是多少?
SELECT min(price) FROM product;
# avg 平均值
# 需求,所有商品的平均价格是多少
SELECT avg(price) FROM product;
# 练习: 计算当前最大值与平均值的差,和最小值与平均值的差
SELECT max(price) - avg(price), min(price) - avg(price) FROM product;
# 聚合函数可不可以以使用在条件语句中呢?
# 不能讲聚合函数使用在where语句中
# SELECT * FROM product WHERE price > count(price);
7、分组查询
group by ：将记录按照指定字段分成多组，字段相同的内容分为一组
格式：select 分组字段/聚合函数/其他函数 from 表名 group by 分组字段 having 条件
having
在group by 分组之后，不能使用where进行条件查询，只能通过having进行条件筛选
在having中可以使用聚合函数，但是在where 中不可以使用
在linux 中默认开启了group by 严格模式，在select后边不能使用除了分组字段外的其他字段
# 分组:就是根据指定的字段进行分类,如果类别相同则分为一组
# 需求:查询当前产品中每一类商品各有多少个
SELECTcategory_id,count(*)FROMproductGROUPBYcategory_id;
# 需求:查询当前商品中,每一类商品的平均价格是多少?
SELECTcategory_id, avg(price)FROMproductGROUPBYcategory_id;
# 需求:查询每一类商品的最大价格是多少?
SELECTcategory_id, max(price)FROMproductGROUPBYcategory_id;
# 在分组的同时,可以进行条件筛选
# 需求,查询出商品种类超过两种的商品类型
# 在分组过后不能使用where进行数据筛选
# 在分组后,或者说在groupby后边只能使用having 不能使用where 同时,在having后边可以使用聚合函数.
# 聚合,一般都是配合分组进行使用的
SELECTcategory_id,count(*)FROMproductGROUPBYcategory_idHAVINGcount(*) >=2;
# 根据多个字段进行分组
# 根据多个字段分组的原则就是,两个字段同时相等,则分为一组,如果有一个字段不相同,也不能分为一组
SELECTcategory_id, priceFROMproductGROUPBYcategory_id, price;
#groupby严格模式
# Expression #3 ofSELECTlistisnotinGROUPBYclauseandcontains nonaggregated column'bigdata_db.product.pname'whichisnotfunctionally dependentoncolumnsinGROUPBYclause; thisisincompatible with sql_mode=only_full_group_by
# linux中默认开启groupby严格模式:在select 后边不能出现除分组字段外的其他字段,但是可以使用聚合函数
SELECTcategory_id, price, pnameFROMproductGROUPBYcategory_id, price;
# 拓展 查看当前每一类商品的所有商品名称
# 在查询数据时,每一个组中有多条记录,在查询过程中,不能使用除分组字段外的其他字段进行显示,因为对应记录较多,不知道该获取哪一条记录
SELECTcategory_id, group_concat(pname)FROMproductGROUPBYcategory_id;
8、分页查询
分页查询就是将查询到的数据按照一定的规则截取其中的一部分
格式：select 字段 from 表名 limit m , n
m:当前也开始索引的位置
n：当前页所要展示的条目数
# 分页查询
# 格式:select 字段名from表名limitm, n;
# m 是开始索引的位置, 计算开始索引的时候从0开始计算
# n 每页展示的条目数量
# 需求:查询所有的商品数据,并展示前三条数据
SELECT*FROMproductLIMIT0,3;
# 如果从开头开始展示,可以将起始位置进行省略
SELECT*FROMproductLIMIT3;
# 需求:查询所有商品数据,并展示第5-7条数据
SELECT*FROMproductLIMIT4,3;
# 需求:分页展示商品信息,每页3条数据,直到将数据完全展示完为止
# m:（当前页-1）*每页显示条数
# n : 每页展示的条目数
SELECT*FROMproductLIMIT0,3; # 第一页
SELECT*FROMproductLIMIT3,3; # 第一页
SELECT*FROMproductLIMIT6,3; # 第一页
练习
# 练习:
#1/查询(select)所有商品类别中(groupbycategory_id)平均价格大于1500的类别(havingavg(price) >1500)
SELECTcategory_id, avg(price)FROMproductGROUPBYcategory_idHAVINGavg(price) >1500;
#2/查询(select)所有商品中价格最高(orderbyprice )的三种(limit3)商品
# 只有在分组时,select后边使用的字段才受限制
SELECT*FROMproductORDERBYpriceDESCLIMIT3;
#3/对商品进行分类,获取商品种类最多的一个类别
# 在orderby中可以使用聚合函数排序,同时,也可以使用分组字段进行排序,但是在分组后,orderby不能出现除分组字段外的其他字段
SELECTcategory_id,count(*)FROMproductGROUPBYcategory_idORDERBYcount(*)DESCLIMIT1;
9、外键索引
如果数据表含有从属关系，比如部门和员工， 教室和桌椅，班级和同学就可以使用一对多的制表关系
一的一方定义为主表， 多的一方定义为从表
在多的一方添加一列，作为外键约束 ，引用少的一方的主键
如果主表中的主键被应用，则该条数据无法被删除
如果从表中的外键找不到相同值的主键，则无法被插入
# 创建分类表
CREATETABLEcategory1
(
cidVARCHAR(32) PRIMARY KEY,
cnameVARCHAR(100)#分类名称
);
# 商品表
# 外键约束的添加格式
# 在创建时在最下方添加外键约束:constraint(约束) foreign key(外键字段) reference(参考) 主表(主键)
CREATETABLEproducts
(
pidVARCHAR(32) PRIMARY KEY,
nameVARCHAR(40),
priceDOUBLE,
category_idVARCHAR(32),
CONSTRAINT FOREIGN KEY (category_id) REFERENCES category1 (cid) # 添加约束
);
# 添加商品数据
# Cannot addorupdatea child row: a foreign key constraint fails (`bigdata_db`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category1` (`cid`))
# 当前我们想要插入商品信息时,由于外键绑定的关系,我们没有添加对应id的类别,不能进行添加
# 如果外键的值为null则不校验主表中的主键id
# 结论:从表中添加的外键的值,一定要在主表中的id里存在,否则就会报错,当然可以插入null 此时不对主表校验.
#INSERTINTOproductsVALUES(1,'服装',15,1);
INSERTINTOproductsVALUES(1,'服装',15,NULL);
# 如果想要添加从表数据,先添加主表相对应的id的内容
INSERTINTOcategory1VALUES(1,'服装');
# 在主表中存在该id后可以添加从表信息
INSERTINTOproductsVALUES(2,'风衣',150,1);
# 删除主表数据 id为1的类别
# [23000][1451] Cannotdeleteorupdatea parent row: a foreign key constraint fails (`bigdata_db`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category1` (`cid`))
# 为了保证数据的完整性,如果当前主表中的数据已经被从表引用,则主表中的该数据无法被删除
#DELETEFROMcategory1WHEREcid =1;
# 所以我们需要先删除从表中的信息
DELETEFROMproductsWHEREpid =2;
# 再删除主表中的信息
DELETEFROMcategory1WHEREcid =1;
10、多表查询
内连接：交集，在查询过程中，保留左右两侧共有的记录
左连接：差集，在查询过程中，保留左侧表全部数据，以及右侧表可以匹配到左表的数据
右连接：差集，在查询过程中，保留右侧表全部数据，以及左侧表可以匹配到右侧表的数据
# 创建hero表
CREATETABLEhero
(
hidINTPRIMARY KEY,
hnameVARCHAR(255),
kongfu_idINT
);
# 创建kongfu表
CREATETABLEkongfu
(
kidINTPRIMARY KEY,
knameVARCHAR(255)
);
INSERTINTOheroVALUES(1,'鸠摩智',9),(3,'乔峰',1),(4,'虚竹',4),(5,'段誉',12);
INSERTINTOkongfuVALUES(1,'降龙十八掌'),(2,'乾坤大挪移'),(3,'猴子偷桃'),(4,'天山折梅手');
# 内连接:交集运算,左表中存在的数据,右表中也存在则被保存
SELECT*FROMhero INNERJOINkongfuonhero.kongfu_id= kongfu.kid;
# inner可以被省略
SELECT*FROMheroJOINkongfuonhero.kongfu_id= kongfu.kid;
# 左 外链接 左表中所有的数据都被保存下来,右表中只有能够匹配左表的数据被保留
SELECT*FROMhero LEFT OUTERJOINkongfuONhero.kongfu_id= kongfu.kid;
# outer 可以被省略
SELECT*FROMhero LEFTJOINkongfuONhero.kongfu_id= kongfu.kid;
# 右 外连接 右表中所有的数据都被保留下来,左表中只有能够匹配右表的数据被保留
SELECT*FROMhero RIGHT OUTERJOINkongfuONhero.kongfu_id= kongfu.kid;
# outer 可以被省略
SELECT*FROMhero RIGHTJOINkongfuONhero.kongfu_id= kongfu.kid;
# 所有的表,和字段可以其别名,但是如果起了别名,则只能使用别名,不能使用原有的名称
# 以内连接为例
# 字段名称和表名,可以使用as关键字,进行重命名
# 字段名称,重命名后,查询出来的内容的列名将会发生改变
# 表名重命名后,我们可以使用别名调用表中的字段,或者使用表
# 无论是表名还是字段名,起别名后,原来的名称都不能使用了
SELECTh.hnameAShero_name, k.knameASkongfu_nameFROMheroAShJOINkongfuASkONh.kongfu_id= k.kid;
#as可以被省略,但是初学阶段不建议省略,增加可读性,方便日后复习
SELECTh.hnamehero_name, k.knamekongfu_nameFROMhero hJOINkongfu kONh.kongfu_id= k.kid;
11、子查询
子查询就是select中嵌套一个select语句
select 查询，可以作为一个值，一个数据序列， 也可以作为一个表出现
子查询可以单独执行，如果子查询单独执行出现报错那放到其他语句中一样会报错
# 子查询:在一个select语句中,嵌套了另外一个一个select语句
#select可以查询出什么?
# 需求:获取所有商品的平均价格
SELECTavg(price)FROMproduct; #select获取的是一个值
# 需求:获取所有商品的名称
SELECTpnameFROMproduct; #select获取的是一列数据 (或者说一个向量数据)
# 需求:获取所有商品的全部信息
SELECT*FROMproduct; #select获取的是一个表 (或者说获取的是一个矩阵)
# 需求:获取产品价格大于平均价格的所有商品的信息
SELECT*FROMproductWHEREprice > (SELECTavg(price)FROMproduct);
# 需求: 获取和categroy_id=1的商品价格相同的所有商品
# 结构: 获取商品信息 价格in(类别为1的所有商品的价格)
SELECT*FROMproductWHEREpriceIN(SELECTpriceFROMproductWHEREcategory_id ='c001');
# 需求: 获取所有的数据,价格大于平均值 且category_id 不等于'c001'(仅了解 不要求掌握)
SELECT*FROM(SELECT*FROMproductWHEREcategory_id !='c002')AScWHEREprice > (SELECTavg(price)FROMproduct);
练习
# 练习:
#1/ 获取所有商品中大于平均价格50块钱以上的商品的全部信息
SELECT*FROMproductWHEREprice > (SELECTavg(price)FROMproduct) +50;
#2/ 查询商品中不属于c002类别商品价格的所有商品(所有商品的价格不能与c002类别商品重复)
# 查询c002的所有商品的价格
SELECTpriceFROMproductWHEREcategory_id ='c002';
# 查询不属于上述价格范围的商品
SELECT*FROMproductWHEREpriceNOTIN(SELECTpriceFROMproductWHEREcategory_id ='c002');
# 子查询可以拆分书写,并且 子查询一定是可以单独执行
