SQL知识合集

插入-insert

insert into t_order (goods_name,goods_count) VALUES ("小本","2")

批量插入：各个值之间用逗号隔开

insert into t_order (goods_name,goods_count) VALUES ('小ja','3'),('小卡','4');

更新-update

update t_order set goods_name='haha' where id=12

批量更新：各个值之间用逗号隔开

update t_order set goods_name='销售',goods_count='3' where id=62

如果更新所有的字段的话就不需要where

删除-delete

DELETE from t_order where goods_count='3'

查询-select

select 
  字段列表
from
  表名列表
where
 条件列表
group by
  分组列表
having
 分组后的条件列表
order by
 排序字段列表
limit
 分页

• 基本查询
• 条件查询（where）
• 聚合函数（count、max、min、avg、sum)
• 分组查询（group by）
• 排序查询（order by）
• 分页查询（limit）

💇

• DISTINCT关键字将查询出来的重复数据进行去重

• as进行设置别名

select DISTINCT goods_count as 'count' from t_order;

条件：

>	大于
>=	大于等于
<	小于
<=	小于等于
<>或!=	不等于
between and	在某个范围之内（含最大、最小）
in（…）	在in之后的列表中的值，多选一
like	模糊匹配（_匹配单个字符，%匹配任意字符）
IS NULL	是null
and 或 &&	并且
OR	或
not	非，不是

📦

查询商品价格等于400的数据:

select * from  t_order where goods_price=400;

查询商品价格等于629的数据:

select * from  t_order where goods_price<=629;

查询商品价格不等于629的数据:

select * from  t_order where goods_price<>629;

查询没有商品id的信息：

select * from  t_order where goods_id is null;

查询有商品id的信息：

select * from  t_order where goods_id is not null;

查询价格在600-800（包含600、800）

select * from  t_order where goods_price BETWEEN 600 and 800;

---

select * from  t_order where user_id=150 and goods_id=2;
select * from  t_order where goods_id=3 or goods_id=2;
select * from  t_order where goods_id in(1,2,5); 

查询姓名为两个字的员工

select * from  t_order where goods_name LIKE '__';//两个下划线

保证最后字段以pro结束

select * from  t_order where goods_name LIKE '%pro';

聚合函数:聚合函数不计算null值

count

select count(*) FROM t_order

avg

计算平均价格

select avg(goods_price) FROM t_order

max、min：最大值、最小值

select min(goods_price) FROM t_order

sum:对价格求和

select sum(goods_price) FROM t_order where user_id=150

分组查询

select * from 表名 where group by 分组字段名 [having 分组后过条件] 

where 和 having之间的区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与where条件，不参与分组；而having是分组之后对结果进行过滤
• 判断条件不同：where不能对聚合函数进行判断，而having可以

根据性别分组，统计男员工和女员工的数量

select count(*),gender from emp group by gender

根据性别进行分组，统计男性员工和女性员工的平均年龄

select avg(age),gender from emp group by gender

查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

select addr from emp where age<45 group by addr having count(*) >=3;

注意

• 执行顺序: where>聚合函数>having
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义

排序

排序方式：

• ASC：升序
• DESC：降序

根据年龄对公司的员工进行升序排序

select * from emp order by age asc;
select * from emp order by age desc; //降序

根据年龄对公司的员工进行升序排序，年龄相同，在按照入职时间进行降序排序

select * from emp order by age asc,addr_time desc

分页查询 -Limit

select 字段列表 from 表名 limit 起始索引，查询记录数  

注意：

• 起始索引从0开始，起始索引=（查询页码-1）*每页显示的记录数
• 分页查询是数据库方言，不同的数据有不同的实现，mysql是limit
• 如果查询的是第一页的数据，起始索引可以省略，直接简写为limit10

查询第1页员工数据，每页展示10条记录

select * from emp limit 0,10

查询第二页的员工数据，每页展示10条记录

select * from emp limit 10,10

.PNG)

select * from emp where age in(20,21,22,23);
select * from emp where gender='男' and age between 20 and 40 and name like "___"//姓名是三个字
select gender,count(*) from emp where age<60 group by gender;
select * from emp where age<=35 order by age asc,work_time desc;
select * from emp where age between 20 and 40 and gender='男' order by age asc,,work_time desc limit 0,5;

函数

字符串函数

函数	功能
concat
lower
upper
lpad
rpad
trim
substring

select CONCAT('hello,','mama');
select LOWER('CDDDDD')
select UPPER('ssafc')
select LPAD('01',4,'-') //将-填充到左边保证长度为4
select RPAD('01',4,'-')
select TRIM(' ff dfss ')
select SUBSTRING('dsds',1,3) //截取到的是dsd

案列：由于业务需求的变更，企业员工的工号统一为5位数，目前不足5位数的全部在前面补0。比如1号员工应该为00001

update emp set workno=lpad(workno,5,'0');

update t_order set goods_id=lpad(goods_id,3,'1');

数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand()	返回0-1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

select ceil(3.2) //4
select FLOOR(3.2) //3
select MOD(3,4) //3
select RAND()//生成数是0~1
select ROUND(2.43,1) //2.4

案例：通过数据库的函数，生成一个六位数的随机验证码

select RPAD(ROUND(RAND()*1000000,0),6,'0')

日期函数👀

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定的date的年份
month(date)	获取指定的date的月份
day(date)	获取指定的date的日
date_add()
datediff()

select CURDATE(); //2022-06-14
select CURTIME(); //15:18:44
select now(); //2022-06-14 15:19:02

select YEAR(now())
select month(now())
select day(now())

select DATE_ADD(NOW(),INTERVAL 2 day) //向后推2天
select DATEDIFF(NOW(),'2022-1-12') //两个日期的差数：第一个日期-第二个日期

案列：查询所有员工的入职，并根据入职天数，并根据入职天数倒序排序

select name,datediff(curdate(),entrydate) from emp order by datediff(curdate(),entrydate) desc

流程函数

流程函数也是很常用的一类函数，可以在sql语句中实现条件筛选，从而提高语句效率

select if(true,'ok','error')   //ok

select IFNULL('ok','default')  //ok
select IFNULL(null,'default')  //default

case when then else end

需求：查询emp表的员工姓名和工作地址（北京/上海—>）

select name,
(case addr when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' 
from emp

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

目的：保证数据库中数据的正确，有效性和完整性

约束	描述	关键字
非空约束	该字段不能为空	not null
唯一约束	保证该字段的所有数据都是唯一，不重复	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据是，如果未指定该字段的值，则采用	default
检查约束	保证字段值满足某一个条件	check
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	foreign key

补充

POSITION()函数
语法: POSITION(substr IN str)
返回字符串str中第一次出现子字符串substr的位置。

SELECT position(‘a’ IN ‘nanana’); # 2