SQL补充 | MelodyYu

SQL的特征

大小写不敏感
需要以;结尾
注释:
- 单行注释: – 注释内容 (– 后面一定要有一个空格) # 注释内容，可以不加空格
- 多行注释:/* 注释内容 */

SQL语言的分类

DDL 数据定义
DML 数据操作
DCL 数据控制
DQL 数据查询

DDL库管理

查看当前使用的数据库

select database();

查看有哪些表(的先选择数据库)

show tables

删除表

drop table 表名称;

drop table if exists 表名称;

创建表

create table 表名称(
	列名称 列类型,
    列名称 列类型，
    .....
)
-- 列类型有
int         -- 整数
float       -- 浮点数
varchar(长度)-- 文本，长度为数字，做最大长度限制
date        -- 日期类型
timestamp   -- 时间戳类型

DML

数据插入insert

基础语法:

1 2	insert into 表[(列1,列2,....,列N)] values(值1,值2,....,值N)[(值1,值2,....,值N),(值1,值2,....,值N),....,(值1,值2,....,值N)] # []代表可选

数据删除 delete

基础语法:

1 2	delete from 表名称 [where 条件判断]; # 条件判断:列操作符值

数据更新 update

基础语法:

1	update 表名 set 列=值 [where 条件判断];

DQL 数据查询

查询基础

SQL中，通过select关键字开头的SQL语句，来进行数据的查询,查询也可以指定条件

eg：

CREATE table student(
	id int,
	name VARCHAR(20),
	age int
	);
INSERT INTO student VALUES(10001,'周杰伦',31),(10002,'王力鸿',33),(10003,'林俊杰',35),(10004,'张学友',36),(10005,'刘德华',30);
# 查询id 和 name 两个列
select id,name from student;
# 查询全部列
select id,name,age from student;
select * from student;
# 查询id，name两个列，年龄小于33
select id,name from student where age<33;

模糊匹配

SQL模式

在使用SQL模式时，不能使用=或!= 而使用like或not like比较运算符。

语法: SELECT 字段 FROM 表 WHERE 某字段 Like 条件

SQL 提供两种匹配模式:
- 百分号(%):表示任意个或多个字符，可以匹配任意类型和长度的字符
  
  示例:
  1
  2
  3
  4
  select * from character where name like '%刘%';
  # 匹配姓名为"刘"类型的数据
  select * from character where name like '%马%梅%';
  # 只能匹配姓名为"...马...梅..."的数据
- 下划线(_)：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句(可以代表一个中文字符)
  
  示例:
  1
  2
  select * from character where name like '_三_';
  # 匹配"x三x"类型的数据，前后只有一个字符

正则模式

mysql正则表达式仅仅是sql语言的一个子集，可以匹配基本的字符、字符串。

1 2	select * from name where name regexp 'jack'; # 可以检索处name中所有包含jack的行

. 匹配除了\n之外的任意单个字符
^ 匹配字符串开始位置，如查询所有行李的人
1
select name from table where name regexp '^李';
$ 匹配字符串结束位置，如查询人名结尾的情况
进行OR匹配

为搜索两个串之一（或者这个串，或者为另外一个串），使用|。|作为OR操作符，表示匹配其中之一。可以给出两个以上的OR条件。
1
2
select * from products where pro_id REGEXP '1000|2000';
# 这样就1000和2000都能匹配并返回，当然，使用多个|就可以匹配多个串
[ ] 匹配任何单一字符，是另一种形式的OR语句，可缩写的OR语句

例如，匹配范围：[0123456789]可以匹配0到9，[1-4][4-9]也是合法的范围。此外，范围不一定只是数值的，[a-z]匹配任意字母字符。
[^….] 匹配不包含在[ ]的字符

匹配特殊字符使用\进行转义

>（1）\\-  表示查找-
>（2）\\.  表示查找.
>（3）\\f  表示换页
>（4）\\n 表示换行
>（5）\\r  表示回车
>（6）\\t  表示制表
>（7）\\v  表示纵向制表
>注意:匹配\本身，需要使用\\\

匹配字符类

>（1）[:alnum:]  任意字母和数字（同[a-zA-Z0-9]）
>（2）[:alpha:]   任意字符（同[a-zA-A]）
>（3）[:blank:]   空格和制表符（同[\\t]）
>（4）[:digit:]    任意数字（同[0-9]）
>（5）[:lower:]   任意小写字母（同[a-z]）
>（6）[:upper:]  任意大写字母（同[A-Z]）
>（7）[:space:]  包括空格在内的任意空白字符（同 [\\f\\n\\t\\r\\v]）
>（8）[:cntrl:]    ASCII控制字符（ASCII 0到31和127）
>（9）[:graph:]  与["print:]相同，但不包括空格
>（10）[:print:]   任意可打印字符
>（11）[:punct:] 既不在 [:alnum:] 又不在 [:cntrl:] 中的任意字符
>（12）[:xdigit:] 任意十六进制数字（同 [a-fA-F0-9]）

匹配多个示例，关于重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于 {`1`, }）
？	0个或1个匹配（等于 {`0`, `1`}）
{n}	指定数目的匹配
{n, }	不少于指定数目的匹配
{n ,m}	匹配数目的范围（m不超过`255`）

定位符

>^     文本的开始
>$    文本的末尾
>[[:<:]] 词的开始
>[[:>:]] 词的结尾

like contact 模糊查询

contact(str1,str2,…)函数返回结果为连接参数产生的字符串

1 2	select * from role where name like contact("%","三","%"); # 即匹配姓名为“唐三”，“唐三藏”等类型的数据数据；

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，从MYSQL4.1开始引入。

子查询在主查询之前一次执行完成
子查询的结果被主查询使用
注意:
- 子查询要包含在括号内
- 讲子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
  
  (按内查询是否被执行多次，从内查询返回的结果条目数来区分单行和多行子查询)

分组聚合

分组聚合应用场景非常多，如:统计班级中，男生和女生的人数。这种需求就需要:

按性别分组
统计每个组的人数

这就称之为分组聚合

一个sql中可以写多个聚合

基础语法:

select 字段|聚合函数 from 表 [where 条件] group by 列
# 聚合函数有:
- sum(列) 求和
- avg(列) 平均
- min(列) 求最小值
- max(列) 求最大值
- count(列|*) 求数量

注意:非聚合函数中，group by 中有谁，select中才能写谁

排序分页

结果排序

如果没有使用排序操作，默认情况下查询返回的数据是按照添加数据的顺序显示的。

使用order by关键字，指定某个列进行排序，(列的别名只能再order by中使用，不能在where中使用)语法:

select 列|聚合函数|* from 表 where ...
group by...
order by... [ASC|desc]
# ASC 升序，desc 降序 不选择默认升序排列

使用limit的好处:

约束返回结果的数量可以减少数据表的网络传输量 ，也可以提升查询效率 。

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据是唯一的，将不再对第二列进行排序。

结果分页排序

使用limit关键字，对查询结果进行数量限制或分页显示

注意:limit子句必须放在整个select语句的最后;在 MySQL 中，LIMIT子句不支持使用算术表达式

1 2	select * from student limit 10,5; # 表示从第十条开始向后取五条

Having与where的区别

having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合

在查询过程中执行顺序：from>where>group（含聚合）>having>order>select。

JOIN关联

查询除了使用1张表外，可能会同时使用多张表，常见的多表查询方式有:

from 多表

基础语法:
1
select ... from 表1 as 别名1，表2 as 别名2,...,表N[where 链接条件]
直接再from中写多个表，通过as可以给出表别名

直接查询两个表时我们会发现结果会产生笛卡尔积:

要改变查询结果，只需要再表名后面加上where关键字，进行条件判断即可;同时查询结果中也有一些列是我们不需要的，只需要再select中选择我们需要查询的列即可:
inner join

内关联，由上面的from多表我们可以看出，查询结果会出现笛卡尔积，尽管可以通过where进行条件过滤但性能不太好；使用内关联能有效改善性能，语法：
1
select ... from 表1 as 别名1 [inner] join 表2 as 别名2 on 连接条件;
因此上述查询语句可改为:
1
SELECT s.*,c.name FROM stu as s INNER JOIN class as c on s.class_id=c.id;
内关联本质上就是求交集的过程，若两个表中存在没有交集的数据，那么查询结果中就不会显示

outer join

如果想解决没有交集的数据，可以使用外关联来显示内关联失败的数据，语法:

select ... from 表1 as 别名1 (left|right) [outer] join 表2 as 别名2 on 连接条件;
# outer 关键字可以省略
# 外关联分左右，必须选择left join 左外关联或者 right join 右外关联，二选一
# 左 右 关联的区别是，以谁为核心

拿上面的查询代码来说 left join 就是以stu表为核心，不管class表里面有没有与之匹配的数据，在结果中都会显示stu表中的数据，而对应的class表中的列将会显示null，right join 则反之。

函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在sql语句中实现不同的条件选择。MySQL中的流程处理函数主要包括if()、IFNULL()、CASE()函数

函数	用法
F(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 …. [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 …. [ELSE 值n] END	相当于Java的switch…case…