sql 复杂查询
1、题目：
1、实现：
#查询数据库表结构
select * from information_schema.COLUMNS as subject_name,tb_score where Table_name = 'tb_score';
select
名字,
column_name as '科目' ,
case
when column_name = "语文" and 60 > `语文` then '不及格'
when column_name = "语文" and `语文` >= 60 and 80 > `语文` then '及格'
when column_name = "语文" and `语文` >= 80 then '优秀'
when column_name = "数学" and 60 > `数学` then '不及格'
when column_name = "数学" and `数学` >= 60 and 80 > `数学` then '及格'
when column_name = "数学" and `数学` >= 80 then '优秀'
else '其他'
end as '成绩'
from information_schema.COLUMNS as subject_name,tb_score
where Table_name = 'tb_score' and (column_name = "数学" or column_name = "语文")
Mysql 的基本语句
#创建一个数据库
--create database j1902;
#显示数据库
--show databases;
#删除表数据库
--drop database j1902;
#使用数据库
--use j1902;
--------------------------------------------
#创建一张表
--create table student(
-> id int,
-> name varchar(10)
-> ,age int,
-> score double);
#显示表属性
--desc student;
#删除表
--drop table student;
#显示表
--show tables;
#添加属性
--alter table student add column stuId int;
#删除属性
--alter table student drop stuId;
#把id放在student表的第一行
--alter table student add id int first;
#修改属性的名称
--alter table student change score javaScore double;
#修改属性的类型
--alter table student modify stuId varchar(30);
#重命名表名
--alter table student rename teacher;
--rename table teacher to student;
#删除属性
--alter table student drop stuId;
---------------------------------
#向表里面添加数据
--insert into student values(1001,"老王",18,90);
--insert into student(id,name,age) values(1001,"翠王",18);
--insert into student values(4,"翠花",60,20),(5,"小盆友",90,20);
#查询数据
--select * from student;
#修改
--update student set javaScore=88.8 where name="翠花";
--update student set javaScore=60 where javaScore is null;
-- update student set age=20,javaScore=98 where id=1002;
#删除
--delete from student where name="翠花";
--delete from student;
# 给表字段添加一个唯一性约束
-- ALTER TABLE student ADD unique(`id_card`);
# 删除表中某个字段的唯一性约束
-- ALTER TABLE student DROP INDEX id_card;
# 修改表 AUTO_INCREMENT 的值
-- ALTER TABLE student AUTO_INCREMENT= 9;
mysql 的FIND_IN_SET(str,strList)查询
MySql时间相关查询
# 获取今日凌晨时间
UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE))
# 获取昨日凌晨时间
UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY)
# 获取明日凌晨时间
UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) + INTERVAL 1 DAY)
# 获取的是时间戳
# 时间戳转换成datetime, datetime也就是我们说的日期格式 年-月-日 时-分-秒
FROM_UNIXTIME();
# datetime转时间戳
UNIX_TIMESTAMP();
# 测试
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE))) 今日;
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY)) 昨日;
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) + INTERVAL 1 DAY)) 明日;
# 结果:
2019-11-29 00:00:00 // 今日
2019-11-28 00:00:00 // 昨日
2019-11-30 00:00:00 // 明日
concat(year(now()),'-01-01')//当前年份的第一天
concat(year(now()),'-12-31')//当前年份的最后一天
select now()
#2019
select DATE_FORMAT(now(),'%Y')
#2019-01-01
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);
#2019-01-01 00:00:00
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL dayofyear(now())-1 DAY))
#2019-01-01 00:00:00
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(concat(year(now()),'-01-01') AS DATE)))
#2019-12-31 00:00:00
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(concat(year(now()),'-12-31') AS DATE)))
#2019-12-31
select concat(year(now()),'-12-31')
根据表不同的字段，关联不同的表
根据表不同的字段，关联不同的表
一、decode
select a.*,decode(a.type,0,(select b.Stem from XTEL_Exer_ChoiceQuestion b where b.id=a.id)) from XTEL_Exercises a
---decode中只能有一列
二、case ... when ...
SELECT
CASE WHEN a.type=0 THEN
(SELECT b.Stem FROM XTEL_Exer_ChoiceQuestion b WHERE a.id=b.id)
end case ,a.*
from XTEL_Exercises a
三、UNION ALL
SELECT b.id,b.name a join b
on a.id=b.id
where a.type = 0
UNION ALL
SELECT c.id,c.name FROM a join c
on a.id=c.id
AND a.type = 1
注意： 如果 c.name的字段为 NULL 时
SELECT b.id,b.name as name a join b
on a.id=b.id
where a.type = 0
UNION ALL
SELECT c.id, NULL as name FROM a join c
on a.id=c.id
AND a.type = 1
注意：在我们实际的查询中，decode，case...when明显不满足要求，
decode，case...when关联表都只能查询一列，实际中我们用到的比较少。
个人更倾向于 union all 。
虽然 union all 是全关联查询，但是如果是根据字段的不同来关联查询，
如果不是所有的字段都需要查询一遍的话，可是很好的一种选择。
四、IF 根据字段不同来查询 （只能查询一列）
现有一张A表：
字段有，leave_id， type，reason， user_id， （分别是主键，对象类型，请假原因，请假人id）
---------------------------------------------------------------
B表：
字段有，dept_id， manag_name，
---------------------------------------------------------------
C表：
字段有，yee_id， eey_name，
select
if(type=1,(select manag_name from B where dept_id=A.user_id),
(select eey_name from C where yee_id =A.user_id)
) as `姓名`
if(type=1,'管理','员工') as `对象类型`
v.reason as `请假原因`
from A
mybatis 查询by List ids
mapper 里面：
List<User> getByIds(@Param("ids") List<Long> ids);
mapper.xml 里面：
select u.* from user u
where u.id IN
<foreach collection="ids" item="id" index="index" open="(" close=")" separator=",">
#{id}
</foreach>
查询出来10条数据，但是mybatis 只返回 3条数据，
原因： 查询的结果，有部分是完全相同的，
应该加一个字段使得结果不完全相同，比如加一个唯一键id (coupon_id)
九、mybatis 传入某个字段判断，获取不同表的某个字段的数据
description: 当传入的参数 catalog 是否为空，不传（为空），就用tb_sku表某个的字段，
如果不为空，则从另外一张表获取到某个字段，此处查询出来的结果不能为多个，所以最好加一个limit 0,1
进行限制，不考虑查询结果，limit先保证查询结果不会出错。
select s.*,
(case when #{catalogId} is null then s.minimum_qty
else
(select wcp.minimum_qty from `tb_catalog_prod` wcp where
wcp.prod_id = s.prod_id and wcp.catalog_id = #{catalogId} limit 0,1
)
end
) as minimum_qty
from tb_sku s
where prod_id = #{prodId} and is_delete = 0
十、sql 查询到的数据插入或者更新到另外一个表中
更新到另外要给表中
UPDATE `user` u SET u.phone =
(SELECT a.phone FROM luck_auth.`account` a WHERE u.user_id=a.user_id)
WHERE u.phone IS NULL
插入到另外要给表中
INSERT INTO `tz_category_lang`(category_id,lang,category_name) SELECT category_id,0,category_name FROM `tz_category`;
十一、mybatis ，mysql批量条件插入
sql语句
INSERT INTO user (id, phone,create_time,remark)
select * from (
SELECT 1 id, 1 phone, NOW() create_time,'1' remark FROM DUAL
UNION ALL
SELECT 1 id, 2 phone, NOW() create_time,'2' remark FROM DUAL
UNION ALL
SELECT 1 id, 3 phone, NOW() create_time,'3' remark FROM DUAL
) a where not exists (select phone from user b where a.phone = b.phone)
mybatis实现
INSERT INTO user (id, phone,create_time,result)
select * from (
<foreach collection="list" item="item" separator=" UNION ALL " >
SELECT #{item.id} id, #{item.phone} phone, #{item.createTime} create_time,
#{item.result} result FROM DUAL
</foreach>
) a where not exists (select phone from user b where a.phone = b.phone)
188
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
