MySQL常用sql

数据库设计三范式
- 每个字段必须保证原子性（不可再分）	
- 所有的字段必须和主键相关
- 不能存储重复的数据

SQL语句

insert into demo(id) value(1); # 添加
insert into demo(id) values (2),(3); # 批量添加
//
update demo set id = 11 where id = 1; # 更新
//
delete from demo where id = 1; # 删除
//
select id,name from demo; # 查询
// mysql 分页
select field from demo limit m; # 查询前m条数据
select field from demo limit m,n; # 从第（m+1）条开始查询，偏移量为（n-1）。越往后分页LIMIT语句的偏移量越大，速度也越慢 
select field from shop_user where id >= (
	# 覆盖查询：索引查询的语句中只包含了那个索引列
	select id from shop_user limit 10000,1
) limit 10;

# oracle批量添加
insert all
	into demo(id) values(2)
	into demo(id) values(3)
select 1 from dual;
# oracle分页
SELECT * FROM ( 
	SELECT A.*, ROWNUM RN FROM (
    	select * from sys_log where 1=1 
    ) A WHERE ROWNUM <= 10 
) WHERE RN >= 0

表结构修改

# 在student表中的age字段后面添加一个 grade 字段，类型为varchar，不能为null
ALTER TABLE student ADD grade varchar(2) not null COMMENT '该字段的中文备注' AFTER age;
# 将student表中的grade字段重命名为grade2
ALTER TABLE student CHANGE grade grade2 varchar(2);
# 将student表中的grade2字段的类型改为int
ALTER TABLE student MODIFY grade2 int(2);
# 删除student表中的grade2字段
ALTER TABLE student DROP grade2;

数据拷贝

CREATE TABLE table1 SELECT * FROM table2; -- 创建表1，同时拷贝表2的结构和数据
CREATE TABLE table1 LIKE table2; -- 创建表1，同时拷贝表2的结构
INSERT INTO table1 (name,price) SELECT name,price FROM table2; -- 拷贝表2的指定字段并添加到表1

连接查询

• 内连接

# 自动选择数据量较少的表作为主表，遍历主表中的数据依次关联从表进行查询，只有符合on条件的数据才会被返回
select * from classes c inner join student s on ( c.classes_num = s.classes_num)

• 外连接（左）

# 以左表为主表，遍历主表中的每条数据并依次关联从表进行查询，符合on条件的数据会被返回
# 若主表中的某条数据与从表中的所有数据都不符合on条件则只会返回一条数据，该数据只包含主表数据，从表数据全为空
select * from classes c left join student s on ( c.classes_num = s.classes_num)

分组

需求：用户购买商品后生成订单

# 统计每个用户下订单量和订单金额
# 筛选出订单量大于100
select 
	t.user_id as userId,
	COUNT(1) as orderCount,
	SUM(t.price) as orderSum
from test_order t 
	GROUP BY t.user_id HAVING orderCount > 100
	ORDER BY orderCount desc;

# 统计每个地市下用户订单量和订单金额
select 
	t1.city,
	COUNT(1) as orderCount,
	SUM(t.price) as orderSum	
from test_user t1 INNER JOIN test_order t2 on t1.id = t2.user_id
	GROUP BY t1.city
	ORDER BY orderCount desc;

# 统计每个地市下每个渠道的订单量和订单金额
# 方案1
select 
	t1.city,
	t1.channel,
	COUNT(1) as orderCount,
	SUM(t2.price) as orderSum	
from test_user t1 INNER JOIN test_order t2 on t1.id = t2.user_id
	GROUP BY t1.city,t1.channel
	ORDER BY orderCount desc;
# 方案2
select 
	t1.city as "地市",
	SUM(case when t1.channel = '政企' then 1 else 0 end) as '政企（订单量）',
	SUM(case when t1.channel = '政企' then t2.price else 0 end) as '政企（订单金额）'
	SUM(case when t1.channel = '电渠' then 1 else 0 end) as '电渠（订单量）',
	SUM(case when t1.channel = '政企' then t2.price else 0 end) as '电渠（订单金额）'
from test_user t1 INNER JOIN test_order t2 on t1.id = t2.user_id
	GROUP BY t1.city
	ORDER BY orderCount desc;

按照商品分类查询商品

# 查询商品分类的商品
SELECT
	gc.id AS 商品分类主键,
	gc.title AS 商品分类名称,
	gc.cascade_id AS 商品分类层级,
	g.title AS 商品名称 
FROM
	goods_category gc
	LEFT JOIN goods g ON gc.id = g.category_id

# 查询一级商品分类（包含自己）下的所有商品
SELECT
	gc.id AS 商品分类主键,
	gc.title AS 商品分类名称,
	gc.cascade_id AS 商品分类层级,
	g.title AS 商品名称 
FROM
	goods_category gc
	LEFT JOIN goods g ON gc.id = g.category_id
WHERE gc.cascade_id like '1%'

# 查询一级商品分类（不包含自己）下的所有商品
SELECT
	gc.id AS 商品分类主键,
	gc.title AS 商品分类名称,
	gc.cascade_id AS 商品分类层级,
	g.title AS 商品名称 
FROM
	goods_category gc
	LEFT JOIN goods g ON gc.id = g.category_id
WHERE gc.cascade_id like '1,%'

按照（天、月）范围查询并分组

-- %Y-%m
-- %Y-%m-%d
SELECT
	count( cc.id ) AS count,
	date_format( cc.create_time, '%Y-%m' ) AS createTime 
FROM
	prod_info cc 
WHERE
	date_format( cc.create_time, '%Y-%m' ) BETWEEN '2021-02' AND '2021-11'
GROUP BY
	date_format( cc.create_time, '%Y-%m' ) 
ORDER BY
	createTime ASC;

按照当日、本周、本月查询

-- 天
SELECT * FROM table_name WHERE to_days(createtime) = to_days(now());
-- 周
SELECT * FROM table_name WHERE YEARWEEK(date_format(createtime,'%Y-%m-%d')- INTERVAL 1 DAY) = YEARWEEK(now());
-- 上周
SELECT * FROM table_name WHERE YEARWEEK(date_format(createtime,'%Y-%m-%d')) = YEARWEEK(now())-1;  
-- 月
SELECT * FROM table_name WHERE date_format(createtime,'%Y-%m') = date_format(now(),'%Y-%m')

查询昨天、近7天的数据

-- 昨天
SELECT cc.name FROM co_company cc WHERE TO_DAYS(NOW()) - TO_DAYS(cc.create_time) = 1;
-- 近7天
SELECT cc.name FROM co_company cc WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(cc.create_time);

查询结果集中的指定字段整合成一条数据输出

select GROUP_CONCAT(distinct pc.`name` SEPARATOR ',') from prod_info pi where pi.company_id = 49