SQL原生语句

SQL原生语句

创建数据库

CREATE DATABASE db;

创建表

CREATE TABLE stu(id int primary key, name char(10), classid int);

插入数据

INSERT INTO stu(id, name, classid) VALUE (4, "招新4", 10), (5, "招新5", 20), (6, "招新6", 10);

清空数据表

TRUNCATE TABLE stu;

起始

一条完整的SQL语句所包含的

select ... from ... where ... group by ... having ... order by ... limit ...

语句	作用	顺序	说明	补充	备注
select	输出	4	查询结果输出字段
from	获取数据	1	查询到的表
where	过滤	2	对结果进行条件过滤	AND,OR,!=	从from中接收的信息
group by	分组	3	对结果按照字段进行分组	如果遇到Select则输出每一组的第一行，可以和聚集函数放在一起
having	过滤	5	作用与 group by 之后的过滤条件	写在语句group by之后
order by	排序	6	DESC ASC
limit	限定个数	7

Group By

• 将查询到的结果按照指定的字段进行分组，将分组后的数据的第一行数据进行输出，具体输出的信息通过 Select 指定的字段

版本5.7出现问题解决

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

若还不行可尝试执行如下sql

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

count(1)

• 输出当前分组内的数据的数量，相当于每一行数据都新增一个字段 tmp，值为 1

SELECT `url`, count(1) FROM page_permission GROUP BY `url`

count(对应字段)

• 如果字段中有null，则为 null 的数据不会被统计进count的数量中，这个是与Count(1)的唯一的区别

SELECT `url`, count(`name`) FROM page_permission GROUP BY `url`

count(DISTINCT 对应字段)

• 在distinct 指定的字段中进行去重操作后的数量，null也会被当作一种值类型参与到不同结果的统计

SELECT `url`, count(DISTINCT `name`) FROM page_permission GROUP BY `url`

多列的分组

• 多个条件作用

SELECT `url` FROM page_permission GROUP BY `url`, `name`

聚集函数

COUNT()

• 计数

SUM()

• 求和

MAX()

• 最大值

MIN()

• 最小值

AVG()

• 平均值

GROUP_CONCAT()

• 字符串集合

having

作用在 group by 之后的过滤

SELECT room_num, COUNT(1) as n FROM account_base GROUP BY `room_num` HAVING n > 5

如果参与having过滤的结果不需要输出，则可以把聚合函数放在having中执行

SELECT room_num FROM account_base GROUP BY `room_num` HAVING COUNT(1) > 5

order by

排序

SELECT  id, room_num, COUNT(1) as n FROM account_base GROUP BY `room_num` HAVING n > 5 ORDER BY room_num

limit

切片

SELECT  id, room_num, COUNT(1) as n FROM account_base GROUP BY `room_num` HAVING n > 5 ORDER BY room_num limit 4, 10

第二节

case when end

用在select中，根据条件生成一列新的数据，如果没有指定 as 这当前列名会以当前查询语句作为列名，一般使用 case 的时候使用 as 重命名当前列名，通过判断语句可以得到当前列将会输出的内容，也可以与聚合函数一起控制数据

语句

case 
    when 表达式
        then 输出
    when 表达式
        then 输出
    else
        输出
end

简单的case函数

SELECT id, code,
(
CASE code_type
    WHEN 1 then "这是1"
    WHEN 0 then "这是0"
    ELSE "啥也不是"
END
) as A
FROM code_base

case搜索函数

SELECT id, parent_code,
(
CASE 
    WHEN code_type = 1 then "这是1"
    WHEN code_type = 0 then "这是0"
    ELSE "啥也不是"
END
) as A
FROM code_base

case then 和 聚集函数

SELECT name, 
MAX((
CASE stage
	WHEN "基础" THEN `source`
	ELSE NULL
END
) ) as "基础",
MAX((
CASE stage
	WHEN "爬虫" THEN `source`
	ELSE NULL
END
) ) as "爬虫",
MAX((
CASE stage
	WHEN "SQL" THEN `source`
	ELSE NULL
END
)) as "SQL"
FROM stu GROUP BY `name`

Join

连接：做两个表之间的关系

FROM 表A A join 表B B on 条件 WHERE
select * from 表A A join 表B B on a.id = b.id;

join

如果没有 on 判断条件，join会将 表A 和 表B 的所有数据进行乘级拼装

left join

左连查询