数据库中运算

select 1+1;
SELECT (1+1)*3;

时间函数

now() 当前时间

select now();

year() 年

使用year()函数的时候，需要传入日期

select YEAR("2020-10-10");

返回当前的时间

select YEAR(NOW());

查询学生表中1995出生的学生信息；

SELECT * from students where year(birthday) = 1995;

year(birthday) 会把具体的日期换成年份显示；

查询1995，1997，2011 年出生的学生；

select * from students where year(birthday) in (1995,1997,2011);

month() 月份

传入一个日期，返回这个日期对应的月份

SELECT MONTH(NOW());

查询1月份出生的学生信息；

select * from students where month(birthday) = 1;

查询1月份-6月份之间出生的同学

select * from students where month(birthday) between 1 and 6;
select * from students where month(birthday) <= 6;

day() 月份中天

select DAY(now())

Hour() 小时

SELECT HOUR(NOW());

Minute() 分钟

select MINUTE(now());

Second() 秒

SELECT SECOND(NOW());

查询出生日期中为 月份为1-6月 并且在 12：00-18：00 之间出生的同学；

select * from students 
where month(birthday) between 1 and 6 
and
hour(birthday) between 12 and 18;

Date_Format() 格式转换

select date_format(now(),"%H-%i-%s");

select * from students 
where date_format(birthday,"%m") between 1 and 6 
and
DATE_FORMAT(birthday,'%H') between 12 and 18;

datediff() 计算日期差值

传入两个值，计算两个时间之间相差的天数

SELECT DATEDIFF(NOW(),"1991-10-01");

1991-10-01 到今天总过了 多少天；

查询最近10天出生的学生信息；

select * from students where datediff(now(),birthday) <=10;

• now() 当前时间
• birthday 数据库中表里面的具体的时间
• datediff() 计算两个时间之间

date_add() 更加灵活的时间操作

1. 15个月前的那天

   select date_add(now(),INTERVAL -15 month );

   

2. 2天后

   select date_add(now(),interval 2 day);

   

3. 查询最近15个月出生的学生信息；

   select * from students where birthday >= date_add(now(), interval -15 month);

   思维导图

   

   作业

   查询heros 表

4. 按照hp_max 从大到小排序，查询 上架时间（birthdate）不为null 的英雄信息 ```sql select from (select from heros order by hp_max desc) as tmp where not birthdate is null;

SELECT * FROM heros where not birthdate is null order by hp_max desc;

2. 查询2015，2016年度上架的所有英雄
```sql
select * from heros where birthdate like "2015%" or birthdate like "2016%";
select * from heros where YEAR(birthdate) in (2015,2016);

1. 查询 上架时间（birthdate）为null的影响，按照hp_max进行从大到小的排序；

   SELECT * FROM heros WHERE birthdate IS NULL ORDER BY hp_max DESC ;

2. 查询 姓名为三个字，按照 hp_5s_max 从大到小，hp_max从小到大进行排序;

   SELECT * FROM heros WHERE NAME LIKE "___" ORDER BY hp_5s_max DESC,hp_max ASC;

3. 查询hp_max最高的英雄信息；

   select hp_max from heros order by hp_max desc limit 1;
   select * from heros where hp_max = (select hp_max from heros order by hp_max desc limit 1);

4. 查询hp_max最低的英雄信息；

   select * from heros where hp_max = (select hp_max from heros order by hp_max limit 1);

5. 查询hp_max第二高的英雄信息； ```sql — 1. 找到第二高的值 select hp_max from heros where hp_max < (select hp_max from heros order by hp_max desc limit 1) order by hp_max desc limit 1;

— 2. 根据值找人 select * from heros where hp_max = ( select hp_max from heros where hp_max < (select hp_max from heros order by hp_max desc limit 1) order by hp_max desc limit 1 );

8. 查询hp_max第二低的英雄信息；
---
查询students表
1. 查询 2020年以后出生的学生
```sql
select * from students where year(birthday) > 2020;

1. 查询成绩最高的学生，并按id进行降序排序； ```sql — 1. 找到最高分 select score from students order by score desc limit 1;

— 2. 根据最高分找人 select * from students where score = (select score from students order by score desc limit 1) order by id desc;

3. 查询语文分数最高的学生；
```sql
-- 1. 先找语文最高分
select score from students where course = "语文" order by score desc limit 1;
-- 2. 根据分数找对应的人
select * from students where course = "语文" and 
score = (select score from students where course = "语文" order by score desc limit 1);

1. 查询数学成绩最低的学生；