1. 创建数据库

CREATE DATABASE IF NOT EXISTS abcdefg DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

CREATE DATABASE IF NOT EXISTS abcdefg1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 创建数据表

USE abcdefg1;

DROP TABLE IF EXISTS abc;

CREATE TABLE abc(

id int(11) NOT NULL,

name varchar(40) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. 插入数据

INSERT INTO abc VALUES ('1', 'winter');

INSERT INTO abc VALUES ('2', 'winter');

INSERT INTO abc VALUES(3,'winter'),(4,'winter');

4. 清空数据表

TRUNCATE abc;

abc为表名

5. where 6. group by

select * from monthfenwei where yearmonth = #{yearmonth}

select FLOOR(avg(aqi)) as aqiAvg,province_name as provincename

from china_day

where pdate=#{date}

group by provincename

一行一行的判断

SELECT province_name,floor(AVG(aqi)) AS aqiAvg,pdate

FROM china_hour_copy1

WHERE pdate = '2022-5-25 22:00:00'

GROUP BY province_name ASC;

7 ${} 、 #{}、 ‘%’ #{} ‘%’

必须采用${}的情况

select prov_str as provincename,city_str as cityname,date_str as day,api_zs_str as aqi from ${provincename} where date_str = #{date}

select prov_str as provincename,city_str as cityname,date_str as day,api_zs_str as aqi

from ${provincename}

order by ${cityname}

采用：

select * from china_hour_copy1

where province_name like '%' #{provincename} '%'

like CONCAT('%',#{provincename},'%')

https://blog.csdn.net/cmjimmy/article/details/108202647

不采用：

select * from china_hour_copy1

where province_name like '%${provincename}%'

------#{province_name} 占位符，有效防止SQL注入。不需要考虑参数类型可以使用value或其它名称。

------${province_name} 拼接符，不能防止SQL注入。必须考虑参数类型只可以使用value

8 like

------like------% _ [] [-] [^]

%三% ，%为任意0个或多个字符，把含有三的记录全部找出来

_三_ ，_为任意单个字符，三个字，中间字为三

[012]三, []为匹配0三，1三，2三，但不是012三。

即[0-2],[-]为范围 0 1 2

[^012] ，[^]为非,排除0 1 2 3的记录

= != and or like

9 having

from where group by select 之后的对分组之后的结果进一步过滤

如，查询每个班中（group by），人数大于3人(count)的班级。

正确写法 select结束后having

select count(1) as n from classes group by classname

having n>2

显示n一列

select id from classes group by classname

having count(1) >2

不显示count(1)一列

错误写法

select count(1) as n from classes group by classname

where count(1)>2

10 order by

having之后是order by执行

order by id desc

两列排序

order by id, n；

order by id desc, n desc

select * from china_month_data where cityname=#{cityname}

order by year_name asc, month_name asc;

11 limit a, n

a 指的是索引，从0开始

n 指的是取记录条数

limit 0,2 从第1条记录开始，查询2条记录

12 CASE WHEN

https://blog.csdn.net/yongfeng929/article/details/73733028

https://blog.csdn.net/pksport/article/details/120726276

互换班级：将1班的学生转入进2班，将2班的学生转入进1班。

case when 表达式

then 输出

when 表达式

then 输出

ELSE 输出

end

13 聚集函数 CASE WHEN

select * from monthfenwei

<where>

and cityname = #{cityname}

</if>

<if test="season != null"> and season = #{season} </if>

</where>

14 JOIN

from 表1 join 表2 新生成的一个表

from 表1 join 表2 on 表1.cid=表2.cid 新生成的一个表

(1)不加on条件，不提倡。where可以实现，但是过滤的时间不对，where是 n*m条中过滤，是需要优化的。即加on,先做对比

表1stu 5 条数据，表2classes 2条数据；

表1 join 表2 为 5*2=10条数据

如果join后，有相同的字段，则stu.id classes.id

(2)加on条件

join后生成的是5条数据

select ts.season_name,cm.*

from china_month_data cm left join time_season ts 两个表

on cm.season_code=ts.season_code 能使两个表连接起来的字段

where cm.cityname=#{cityname}

order by cm.year_name asc, cm.season_code asc;

15 Left JOIN和right join

left join和join的区别

left join 必须有on条件，

left join 在join的基础上，会进一步检查左表的数据是否都包含在新生成的表中。

是，与join没区别

不，由于有on，因此是有不包含的行的，这些行尽管超出了条件范围。但也应加入新表，数据用null填充。不会遗漏掉左表任一一条数据。

FORM A left join B 也就是from B right join A

16 多表join left join

from A join B on ~ join C on~ join D ON

每一个join on 是一个组合

使用场景

查询同学A的班主任名称

查询哪些同学还没有分班。

select * from stu left join class on classid=id where classid

错误写法where classid=null

正确写法where classid is null

正确写法where classid=''

查询员工中高于经理的员工

Student(sid,sname,sage,ssex)

Course(cid,cname,tid)

Teacher(tid,tname)

SC(sid,cid,score)

成绩表

CREATE TABLE SC(

sid varchar(11) CHARACTER SET utf8 NOT NULL,

cid varchar(40) CHARACTER SET utf8 DEFAULT NULL,

score int(3) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO SC values ('01','01',90),('01','02',100),('02','01',80),('02','02',70),('03','01',60),('03','02',70),('04','01',50),('04','02',40);

学生表

drop table if exists student;

CREATE TABLE student(

sid varchar(11) character set utf8 not null,

sname varchar(11) character set utf8 not null,

primary key(sid)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO student values ('01','a'), ('02','b'), ('03','c'), ('04','d');

题目1：查询01课程比02课程成绩高的学生信息和课程分数

select s.*,sc1.cid as '课程01',sc1.score,sc2.cid as '课程02',sc2.score from SC as sc1 join SC sc2 on sc1.cid='01' and sc2.cid='02' and sc1.sid=sc2.sid join Student as s on sc1.sid = s.sid where sc1.score>sc2.score;

题目2：查询平均成绩大于等于60分的学生信息和平均成绩

select s.*,avg(sc.score) from SC as sc join Student as s on sc.sid=s.sid group by sc.sid;

select s.*,avg(sc.score) as avgscore from SC as sc join Student as s on sc.sid=s.sid group by sc.sid where avgscore>60;

题目3 查询后一天温度比前一天高的日期

select w2.date from weather w1 join weather w2 on w1.id+1=w2.id where w1.temperature < w2.temperature;

17 子查询

join on where 也可以用子查询

from student as s join class as c on c.manager='小黄' and s.classid = c.id;

或者

from student as s join class as c on s.classid = c.id where c.manager='小黄' ;

先执行子查询，再判断

select * from student where classid = (select id from class where manager='蓝');

drop table if exists classes;

CREATE TABLE classes(

cid varchar(11) character set utf8 not null,

manager varchar(11) character set utf8 not null,

primary key(cid)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO student values ('1','蓝'), ('02','樊'), ('03','马');

查询班主任为蓝所带的学生

查询班主任为蓝和樊所带的学生

1. where 子查询

select s.* from student as s where s.classid = (select c.id from classes as c where c.manager = '蓝' )

select s.* from student as s where s.classid in (select c.id from classes as c where c.manager in ('蓝','樊') )

select s.* from student as s where (s.classid, '蓝' ) in (select c.id,c.manager from classes as c where c.manager in ('蓝','樊') )

2. from 子查询

在 from 里面的子查询必须有别名

select student.*

from student join (select * from classes where manager="蓝") as C on C.cid=student.cid;

3. select 子查询

题

每个班学号最大的学生信息

错误 select max(id),name from student group by classid

因为最大的学号是对的，但是对应的名字不对。

正确 select max(id),name from student group by classid having max(id) = id;

正确 select name from student where id in (

select max(id) from student group by classid

)

18 UPDATE 更新

UPDATE MYTABLE

SET sname='aa' where log_time between '2019-01-01' and '2019-01-31'

UPDATE mytable join histable on mytable.url = histable.url