第三章 模糊查询与分组查询(一)

1. 练习

#创建表并加约束
create table category(
	categoryId int primary key,
	categoryName varchar(20) not null
)

create table good(
	goodId int primary key auto_increment,
	goodName varchar(10) not null,
	price decimal(5,2),
	produceDate date not null,
	address varchar(20) default '郑州',
	categoryId int,
	constraint foreign_key foreign key (categoryId) references category(categoryId)
)

#插入数据
insert into category(categoryId,categoryName)
value 
(1001,'食品'),
(1002,'日用'),
(1003,'洗漱')

insert into good(goodName,price,produceDate,categoryId)
value
('面',10.5,'2022-7-13',1001),
('米',15.5,'2022-7-14',1001),
('馒头',1.5,'2022-7-13',1001),
('扫帚',5.5,'2022-7-15',1002),
('盆',10.5,'2022-6-13',1003),
('肥皂',6.5,'2022-7-10',1003)

#查询
SELECT * from good;
select categoryId,categoryName from category

2. 数据定义语言(DDL)

        主要是用来修改数据结构用的，增加/减少/修改 字段(属性)  添加各种约束

#添加新的字段

alter table 表名 add 列名<类型>;

#添加新的字段 位置在第一列

alter table 表名 add 列名<类型> first;

#将列名1添加到列名2的后面

alter table 表名 add 列名1<类型> after 列名2;

#修改字段名和类型

alter table 表名 change column  <旧列名> <新列名> <新列类型>;

#修改列名的类型

alter table 表名 modify <列名><类型>;

#删除列

alter table 表名 drop <列名>;

#给表重命名

alter table 表名 rename <新表名>

#设置字符集

alter table 表名 character set <字符集名>;

#移动列的位置  将列名a移动到列名b的后边

alter table 表名 modify <列名a>(列类型)  after <列名b>; 

#给id添加自增约束

alter table 表名 modify id int(5) auto_increment;

#修改表中的字段不为空/或者为空

alter table 表名 modify 字段名 not null/null;

#添加表的字段自增主键   修改则将add 改成 modify

alter table 表名 add column 字段名  类型  auto_increment not null,add primary key;

#添加唯一约束

alter table 表名 add constraint 约束名(uk_字段名) unique(字段名);

#删除表的字段

alter table 表名 drop column 字段;

#删除自增主键约束

alter table 表名 modify id int;

#删除表的主键约束，要删除自增约束才可以删除主键约束

alter table 表名 drop primary key;

#添加外键约束  

alter table 表名 add constraint 外键名  foregin key(引用id) references 被引用表名(被引用id) 

#添加主键约束

alter table 表名 add constraint 约束名 primary key (主键列名)

#删除外键约束

alter table 表名  drop foregin key 外键名

------可以通过show create table 表名   来查看外键名

3. 增删改语句

3.1 insert 语句

insert [into] 表名[(colimn1.column2,column3,...)]

value/values (value1,value2,value3,...);

                可以省略列名，但需要按顺序插入所有列，值和列要一一对应。

 #一次录入多条数据

insert [into] 表名[(colimn1.column2,column3,...)]

value/values (value1,value2,value3,...),

value/values (value1,value2,value3,...),

value/values (value1,value2,value3,...)

#在innodb存储引擎下，会自增断层

insert  [into]  (table_name)  [(colimn1.column2,column3,...)]

select  value1,value2,...  union

select  value1,value2,...  

3.2 update 语句

update table_name

set

        column1={ },column2 = { },...

where <表达式>  

 3.3 delete 删除语句

delete from table_name where <表达式>

 删除表中的数据：

delete : 根据条件删除，逐行删除，自增值不会重置；

truncate：整表删除，无法设定条件，效率高，自增列会重新计数，无法恢复数据；无法直接删除有外键引用的表。

#删除数据

#以数据行为单位进行逐行删除，删除速度较慢

#安全，可以记录日志，需要时可以通过日志进行恢复

delete from 表 [where 条件]

#truncate 截断、截取

#直接删除整张表中的数据，相当于先把物理表删除再创建一个新表

#执行效率高，但是无法设定条件

#自增列会重新计数

truncate table 表名

 3.4 级联

涉及到有关主外键关联的数据表：

①删除数据时：先删除外键表，再删主键表

②录入数据时：先录入主键，再录入外键

③更新数据；

级联：当更新（删除）主键表中的数据时，外键表中引用的数据会自动更新(删除)

cascade：删除主键时，自动删除外键表中引用的数据，修改时自动更新外键表中引用的数据。

# on delete CASCADE : 开启 删除 级联

# on update cascade: 开启 更新 级联

alter table emp

add constraint fk_deptno foreign key (deptno)

references dept(deptno)

on delete cascade on update cascade;#级联设置

4. 查询

4.1 语法：

select  列名1,列名2

from 表名

#可选

where 条件表达式

group by 列名

having 条件表达式

order by 列名

limit  ?,?  分页

 说明：

①查询表的列时，可直接写列名，顺序自定义

select `name`,age,address,sex from student;

②可以使用 select * 查询所有列，查询效率低

select * from student;

③列和表都可以起别名

select s.`name`,s.age+3 from student s;

④查询中支持做运算   👆

 4.2 去重查询

   去除查询结果中重复的数据

select distinct  列名 from 表名

4.3 条件查询where语句

SQL语句中的条件表达式：查询条件，判断条件，修改条件

条件运算符：= > < >= 和 !=

算术运算符：+ - * / %（mod) 不支持：++  --  复合赋值 += -=

逻辑运算符：and(&&) , not(!) , or(||)

#查询价格大于5元的商品
select goodsName 商品名,price 价格 from goods where price > 5;
#查询价格大于10元的并且产地是广州的商品
select goodsName 商品名,price 价格,address 产地 from goods where price > 10 && address = '广州';
#查询商品类型是食品，或者价格小于5元的商品
select
	g.goodsName,g.price,c.categoryName
from goods g,category c
where
	(g.categoryId = c.categoryId and c.categoryName = '食品')
	or (g.price < 5 and g.categoryId = c.categoryId);
#查询价格不小于5的商品
select 
	goodsName,price
from goods
where price >=5;
#查询产地不是广州的
select 
	*
from goods
where address != '广州';
#带条件修改
#1. 将产地是郑州的并且是食品的商品都上涨1元钱,并且生产日期改为当前日期
update goods g,category c
	set g.price = price +1,g.produceDate = now()
#select *
#from goods g,category c
where 
	g.address = '郑州' and g.categoryId = c.categoryId and c.categoryName = '食品';
#2. 将价格小于5的并且是饮品的商品产地改为新乡
update goods g,category c
set g.address = '新乡'
#select *
#from goods g,category c
where
	g.price <5 and g.categoryId = c.categoryId and c.categoryName = '饮品';

 #范围查询：between and

#查询商品编号是2-9之间的数据
SELECT
*
from goods
where goodsId between 2 and 9 #包含开始和结束
#where goodsId>=2 and goodsId<=9

#范围查询：in 的使用（用于不连续的条件）

#查询商品是面包和鸡蛋的记录
SELECT
    *
from goods
where goodsName in ('面包','鸡蛋')
#where goodsName = '面包' or goodsName = '鸡蛋'

4.4 模糊查询

4.4.1 like

与'%'结合使用：匹配任意长度任意内容

与'_'结合使用：匹配一个长度任意内容

'%a' //以a结尾的数据

'a%' //以a开头的数据

'%a%' //含有a的数据,三位且中间字母是a的

'_a' //两位且结尾字母是a的

'a_' //两位且开头字母是a的

%：表示任意 0 个或多个字符,可匹配任意类型和长度的字符,有些情况下若是中文,需使用两 个百分号（%%）表示；

_：表示任意单个字符，匹配单个任意字符，它常用来限制表达式的字符长度语句。

4.4.2 正则通配符

正则模式REGEXP操作符

“.”         匹配任意单个的字符

“[ ]”        匹配在[]内的任意一个字符

[^]         匹配不在[]内的任意一个字符

“ * ”       匹配零个或多个在它前面的字符

“+”         匹配+前面的字符1次或多次

“{n}”       匹配前面的字符至少n次

“^”         匹配文本的开始字符

“$”         匹配文本的结尾字符

#商品名字带有‘面’的商品
select *
from goods
where goodsName like '%面%'

#商品名字以‘面’字开头且只有两个字的商品
select *
from goods
where goodsName like '面_'


#用正则表达式查询，以面开头的记录
select
*
from goods
where goodsName REGEXP '^面'

4.5 排序操作

order by 列名

asc(默认) 升序 

desc 降序

#按照商品编号降序呈现
select
*
from goods
order by goodsId desc 
#查询商品，按价格从小到达排序
SELECT
*
from goods
order by price asc
#多字段排序:按照价格升序，如果价格相同，生产日期降序排序
SELECT
*
from goods
order by price,produceDate desc

4.6 分页查询limit(结合排序使用)

        每次我们直接 select * from ** 会将数据全部查询出来，如果一张表数据量比较大，此时 容易爆炸，使用分页操作可以只查一部分的数据。

#查询前三条记录
SELECT
*
from goods
limit 3  #起始位置0可以省略


#获取4，5，6数据
SELECT
*
from goods
limit 3,3


#按照价格升序排序进行分页
select * from goods
order by price asc
limit 1,3