时间戳

首页 > 技术文章 > SQL知识笔记

SQL知识笔记

Rosaany 2021-09-11 16:47 原文

image

目录

1.SQL类别语句

  1. DDL（data definition language）

创建数据库和表、修改表结构

  1. DML（data manipulation language）

数据表中数据的增删改操作

  1. DQL

数据表中数据的查询操作

  1. DCL

事务提交/回滚等

1.1 DDL操作数据库与表

1.1.1创建操作数据库

create database db character set utf8; # 创建一个db数据库指定字符集

1.1.2查看/选择数据库

use db; 切换数据库

select database(); 查看正在使用的数据库

show database; 查看有哪些数据库

show create database db; 查看一个数据库的定义信息

1.1.3 删除数据库

drop database db; 删除某个数据库

1.1.4 创建表

CREATE TABLE 表名( 字段名称1 字段类型（长度）, 字段名称2 字段类型);

create table A like B;创建一个表结构与B相同的A表

1.1.5 查看表

show tables; 查看当前数据库中所有的表

desc A; 查看表A结构

show create table A;查看创建表的SQL语句

1.1.6 删除表

drop table if exists A; 表A存在才删除

1.1.7 修改表

rename table A to B; 将表A名字改为B

alter table A add a1 varchar(20); 为表A添加一个新字段a1

alter table A modify a1 varchar(50); 修改表A字段a1类型

alter table A change a1 a2 varchar(100); 更改命名表A字段a1为a2

alter table A drop a2; 删除表A的a2字段

1.1.8 SQL约束

对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中

常见约束

约束名 约束名关键字 特点 作用
主键 primary key 不可重复 唯一 非空 用来表示数据库中的每一条记录（用来唯一标识数据表中的一条记录）
唯一 unique 某一列不予许为空
非空 not null 表中的某一列的值不能重复（对null不做唯一的判断
外键 foreign key
  • 默认值

用来指定某列的默认值

create table A(a4 varchar(20) default 'nn') 为表A添加一个默认值为'nn'的字段a4

1.1.8.1 主键约束

create table A(a1 int primary key) 创建一个带主键的表（写法1）

create table A(a1 int,primary key(a1))创建一个带主键的表（写法2）

alter table A add primary key(a1);创建表后再指定主键（写法3）

alter table A drop primary key; 删除表A的主键

  • 主键自增

create table A(a1 int primary key auto_increment) 为表A创建一个主键自增字段a1

create table A(a1 int primary key auto_increment)auto_increment=100;默认auto_increment的开始值为1，修改编号从100开始

  • delete和truncate对自增长的影响

删除表中所有数据有两种方式

清空表数据的方式 特点
delete 只是删除表中所有数据，对自增没有影响
truncate 整个表删除掉，然后再创建一个新表，自增主键重新计算
1.1.8.2 唯一约束

create table A(a2 varchar(20) not null) 为表A添加一个值不能唯一的字段a2

1.1.8.3 非空约束

create table A(a3 varchar(20) not null) 为表A添加一个值不能为空的字段a3

1.1.8.4 外键约束

主键：数据表A有一列可以作为唯一标识一条记录（主表）
外键：数据表A有一列指向另外一张数据表B的主键（从表）

  • 新建表时添加外键

语法：

[constraint] [外键约束名称] foreign key (外键字段名）references 主表(主键字段名)

create table A(
    A_id int primary key auto_increment, 
    B_id int, 
    constraint B_fk foreign key(B_id) references B(A_id)
);
-- 创建一个主表A，新建字段A_id为表A的主键，字段B_id为外键（对应从表B的主键）需使用constraint语句指定，需要注意的是外键字段类型要和主表的主键字段类型保持一致
  • 已有表添加外键

alter table 从表 add [constraint] [外键约束名称] foreign key (外键字段名) references 主表(主键字段名)

  • 删除外键约束

alter table 从表 drop foreign key 外键约束名称

1.1.8.5 外键约束的注意事项
  1. 从表外键类型必须与主表主键类型一致，否则创建失败
  2. 添加数据时，应该先添加主表中的数据
  3. 删除数据时，应该先删除从表中的数据
1.1.8.6 级联删除操作

如果想实现删除主表数据的同时，也删除掉从表数据，可以使用级联删除操作

on delete cascade

1.2 DML操作表中的数据

1.2.1 插入数据

insert into 表名 （字段名1，字段名2...） values(字段值1，字段值2...);

1.2.2 修改数据

update 表名 set 列名=值 [where 条件表达式：字段名 = 值]

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

出现上述报错，可能是没带条件修改导致，可以解除安全模式

输入：SET SQL_SAFE_UPDATES = 0

1.2.3 删除数据

delete from 表名 [where 字段名=值] 【不推荐】一条条记录删除，效率低

truncate table A; 【推荐】先删除表A，再创建一张表A

1.3 DQL 查询表中数据

  • 执行顺序

from + join --> where --> group by --> having --> select (窗口函数) --> order by --> limit

1.3.1 简单查询

select a1 from A 查询表A中a1字段的所有数据

1.3.2 条件查询

select 列名 from 表名 where 条件表达式

运算符

运算符 说明
>,<,<=,>=,=,<>,!= 大于，小于，小于（大于）等于，等于，不等于，不等于
between..and...（not between...and...) 显示在某一区间的值 (>=min && <= max)
in（集合)，not in (不在集合) 集合表达多个值，使用逗号分隔
like '%aa%' 模糊查询（包括aa）
is null，is not null 查询某一列为null的值，不能写=null
<=> 比较两个值是否为null，是则其值为1，否则为0
RLIKE, REGEXP 正则式匹配

逻辑运算符

运算符 说明
and && 多个条件同时成立
Or || 多个条件任一成立
Not 不成立，取反

2. 核心查询（一）

本节涉及的内容主要有：排序、聚合、分组、多表、合并、子查询

2.1 排序

select 字段名 from 表名 [where 字段=值] order by 字段名 [asc/desc] 其中asc默认值表示升序排序，desc表示降序

select * from A order by a1 desc, a2 desc; 组合排序，对字段a1降序再对字段a2降序

2.2 聚合

目的：把多个值（某一列的值，纵向查询）进行计算，返回一个单一的值。（聚合函数会把null忽略），比如求个数、求和、求平均值等

select 聚合函数(字段名) from 表名;

聚合函数 作用
count(字段) 统计指定列不为null的记录行数
sum(字段) 计算指定列的数值和
max(字段) 计算指定列的最大值
min(字段) 计算指定列的最小值
avg(字段) 计算指定列的平均值

2.3 分组

分组往往和聚合结合着一起使用，分组后对各自组内进行聚合统计。

select 分组字段/聚合函数 from 表名 group by 分组字段 [having条件];

where 与 having的区别

过滤方式 特点
where where 进行分组前过滤，后面不能写 聚合函数
having haing 进行分组后过滤，后面可以写 聚合函数

2.4 多表

查询2张表及以上

  • 笛卡尔积

select 字段名 from 表1，表2; 交叉连接查询

  • 内连接查询

    1. 隐式内连接

      笛卡尔积使用基础上，再加上where条件过滤

      select 字段名 from 表1，表2 where 连接条件;

    2. 显示内连接

      select 字段名 from 左表 [inner] join 右表 on 条件 inner 可以省略

  • 外连接查询

    • 左连接

      SELECT 字段名 FROM 左表 LEFT JOIN 右表 ON 条件以左表为基准取匹配右表

    • 右连接

      SELECT 字段名 FROM 左表 LEFT JOIN 右表 ON 条件以右表为基准取匹配左表

好习惯：小表在前，大表在后

on与where的区别

In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?

  • 如果右表与左表中的记录没有实际匹配，则从右表中on条件返回一条记录，并将所有字段设置为NULLS，然后WHERE再过滤。实际上，ON将不满足其条件的每个字段替换为NULL。
  • 尽管结果相同，但“ ON”将首先进行联接，然后检索联接集的数据。检索速度更快，负载也更少。
    但是使用“ WHERE”会导致首先获取两个结果集，然后过滤条件。所以我们应该了解什么是首选

2.5 合并查询

  • UNION

    union 用于合并两个或多个select语句的结果集，并消除重复行

    小结：

    1. 选择的列数必须相同
    2. 所选列的数据类型也必须相同
    3. 列的名称不必相同
    4. 在重复检查期间，null值不会被忽略

  • UNION ALL

    union all用于合并两个或多个select语句的结果集，但不会消除重复行

union all简单的将结果合并后返回，在效率上要比union快很多

2.6 子查询

一条select查询语句的结果，作为另一条selet语句的一部分

  • 子查询特点
    • 子查询需放在括号内
  • 子查询常见分类
    • where型：将子查询结果 作为父查询的比较条件
    • from型：将子查询的结果 作为一张表，提供给父层查询使用
    • exists型：子查询是一个单列多行，父层查询使用 in 函数，包含子查询的结果

SELECT 查询字段 FROM 表 WHERE 字段=（子查询） 子查询的结果作为查询条件

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;子查询的结果作为一张表

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）; 子查询结果是单列多行

SELECT 查询字段 FROM 表 WHERE （字段1,字段2） IN （子查询）;子查询结果是多列多行

好习惯：子查询起别名

2.7 limit 限制返回行数

select 字段1，字段2... from 表名 limit offset, length;offset 起始行数，从0开始记数，如果省略，则默认为0, length 返回的行数

3.核心查询（二）

3.1 窗口函数

窗口函数对一组查询执行类型聚合的操作。但是不同的是，聚合函数操作将查询的行分组，从而得到单个结果行，窗口函数为每个查询行生成一条记录。

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口

语法：

函数名（[expr]） over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>)

语法分解：

函数(如：sum(), max(), row_number()...) + 窗口子句(over函数)

over() 内部参数：

1. PARTITION BY col_name
2. ORDER BY col_name asc|desc
3. ROWS between 窗口子句 and 窗口子句
窗口子句 备注
PRECEDING 往前 n preceding 从当前行向前n行
FOLLOWING 往后 n following 从当前行向后n行
CURRENT ROW 当前行
UNBOUNDED 起点
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING 表示该窗口最后面的行（终点）
  • 排序类
排序函数 备注
ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位 1 2 2 4
DENSE_RANK() 生成数据项在分组中的排名，排名相等在名次中不会留下空位。1223
NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值, 等频切片
  • 聚合类
统计函数 备注
COUNT(col) 统计各分组内个数
SUM(col) 统计各分组内合计
MIN(col) 统计各分组内最小值
MAX(col) 统计各分组内最大值
AVG(col) 统计各分组内平均值
  • 偏移类
偏移函数 备注
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值 从当前行下移几行的值
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 从当前行上移几行的值
first_value(col, DEFAULT) 取分组内排序后，截止到当前行，第一个值
last_value(col, DEFAULT) 取分组内排序后，截止到当前行,最后一个值

4.索引

4.1 什么是索引

  • "索引"是存储引擎快速找到一条记录的一种数据结构 --《高性能Mysql》
  • 索引可以提升查询速度（大量数据下效果明显）

但是索引也不是建立越多越好，如果把索引比作是目里，有一本书有100页，50页是目录，试问这本书还有人愿意看吗？

  • 数据量小的表不需要建立所以，建立会增加额外的索引开销
  • 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
  • 更多的索引意味着也需要更多的控件（索引占用磁盘空间）

4.2 常见索引分类

  • 从索引的存储结构划分：B Tree索引、Hash索引、fulltext全文索引、R Tree索引
  • 从应用层次划分：主键索引、唯一索引、普通索引、复合索
  • 从索引的键值（字段）类型划分：主键索引、辅助索引（二级索引）
  • 从索引数据和内容数据逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）
索引名称 说明
主键索引 (primary key) 主键是一种唯一性索引,每个表只能有一个主键, 用于标识数据表中的每一条记录
唯一索引 (unique) 唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一.
普通索引 (index) 最常见的索引,作用就是 加快对数据的访问速度

5.视图

5.1 什么是视图

  1. 视图是一种虚拟表。
  2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
  3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
  4. 视图向用户提供基表数据的另一种表现形式

5.2 视图的作用

  • 权限控制时可以使用
    • 比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用
  • 简化复杂的多表查询
    • 视图，本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
    • 视图主要就是为了简化多表的查询

create view 视图名 [column_list] as select语句;

  • view: 表示视图
  • column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与
  • SELECT语句中查询的属性相同
  • as : 表示视图要执行的操作
  • select语句: 向视图提供数据内容

create view Aasname as select * from A;创建一个名为Aasname的视图，视图指向表A

5.3 视图与表的区别

  • 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
  • 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列 经过计算得到的结果,不允许更新）
  • 删除视图，表不受影响，而删除表，视图不再起作用

6. 总结补充

  1. 还有上面没有列出但经常用的函数：

ifnull为空值设置一个默认值

if条件语句

case when...then.. end条件语句

round保留小数位

concact合并函数

想了解MySQL方面更多的函数：https://www.w3schools.com/mysql/mysql_ref_functions.asp

  1. 日期函数：

DATE_FORMAT()

DATEDIFF()日期相減

MONTH() 获取月份（如果以年/月/日作为分组，分组后select日期字段可以完整保留，这也是日期与其它非日期字段分组的特别之处。）

  1. 统计分析having写法

select .. from .. group by ... having count(*) > n即可过滤组内统计>n时

结合一些文档，把SQL/MySQL常用知识点汇总总结起来，或多或少还有遗漏，但不要紧，日后遇到再补充，以便本文日后当笔记查阅。

推荐阅读