SQL语句总结（将平常用到的SQL语句总结）

1、查找如 2018-07-08 19:20:30 中的年份

SELECT DATE_FORMAT(datetime,'%Y') FROM TestTable;

2、截取字段，截取年月日前面的年，但是必须把表中这个字段改为varchar，不能为datetime

UPDATE `table01` SET `datetime`=LEFT(`datetime`,4)

3、查询表中每年每个市的采购总金额

SELECT A.* FROM 
   (SELECT year AS year,
    area AS area,
    SUM(amountMoney) AS money
    FROM table01 GROUP BY year,area ORDER BY money DESC) A 

4、查询表中每年某个市的采购总金额

SELECT A.* FROM (
    SELECT year as year,
    area as area,
    SUM(amountMoney) as money
    from table01 group by year,area HAVING area='深圳市' order by money DESC
)A

5、查询表中某年某个市的采购总金额

select A.* from (select year as year,
    area as area,
    sum(amountMoney) as money,
    from table02 group by year,area having area='深圳市' and year='2019' order by DESC
)A

//或者直接写成下面这样
select year,city,sum(amountMoney) as money 
from table01 group by city,year having year=#{year} 
order by money desc

6、创建表

create table StudentScore (
    Code int(11) not null,
    Class varchar(50) not null,
    Score int(11) not null,
    primary key (Class)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

7、删除表

drop table if exists StudentScore;

8、添加外键约束

alter table studentsocre 
    add constraint FK123 foreign key(Code) references student(Code);

9、查询学生信息表中，手机号码为空，或者以“138”开头的记录

select * from student where PhoneNumber is null or PhoneNumber like '138%'

10、求总分在150-200分之间的学生个数

select count(Code) from (select Code from StudentScore 
    group by Code having sum(Score) between 150 and 200) as a;

11、找出每门课都大于80分的学生姓名（约定每一个学生都有成绩记录）

select Name from Student a where not exists (
    select Distinct b.Code from StudentScore b where b.Score<=80 and a.Code=b.Code);

12、求没有参加“数据结构”课程考试的学生姓名列表

select Name from Student a where not exists (
    select * from StudentScore b where Class='数据结构' and a.Code=b.Code);

12、查找统计

如有表 temp，数据如下：

time	result
2018-05-19	success
2018-05-19	success
2018-05-19	fail
2018-05-19	fail
2018-05-20	success
2018-05-20	fail
2018-05-20	fail

需要查询得到如下结果：

time	success	fail
2018-05-19	2	2
2018-05-20	1	2

sql语句可以如下写：

//方法一
select time,sum(case when result='success' then 1 else 0 end)'success',
            sum(case when result='fail' then 1 else 0 end)'fail' 
from temp group by time

//方法二
select s.time,s.success,f.failure from
(select time,count(*) as 'success' from temp where result='success' group by time)s,
(select time,count(*) as 'fail' from temp where result='fail' group by time)f
where s.time = f.time