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