sql语句复习整理

1.基础

创建

create database（数据库）/table（表） 名称（
结构描述；
）

显示

show 结构名

数据表操作

增 insert into 表名 values xxxxxxxxx
删 delete from 表名 xxxx
改 update 表名 set 字段=xxx
查 select from 表名 xxx

查看数据表

desc 表名；

修改表名

rename table 表名 to 新表名

改变字段

• 增 alter table （表名） add [column] 字段名 字段类型 [字段属性] [字段位置]
  字段位置 :
  第一个字段：first
  某个字段后：after 已经存在字段名

• 删 alter table 表名 drop 字段名;

• 改

  • 改字段名 alter table 表名 change 原字段名 新字段名 字段类型 [字段属性] [位置]
  • 改字段的相关信息 alter table 表名 modify 字段名 字段类型 [字段属性] [位置]；

字段类型

• 整数类型
  例
  • age int unsigned, # unsigned，表示无符号（从0开始）
• 小数类型
  • （浮点型）
    • 单精度：float，使用4个字节存储，精度范围为6-7位有效数字
    • 双精度：double，使用8个字节存储，精度范围为14-15位有效数字
    • 浮点数超过精度范围会自动进行四舍五入（所以在查询时，有时会发现数值跟存入时不一致）
• 字符串类型
  • 变长型：varchar(L) L是指字符而不是字节
    • L指定的是最大存储的数据长度
    • L最大值理论是65535
• 时间日期类型

  • 时间戳：timestamp，基于格林威治时间的时间（1970年1月1日0点0分0秒）记录

    • MySQL中时间戳表现形式不是秒数，而是年月日时分秒格式
    • YYYY-MM-DD HH:II::SS 或者 YYYYMMDDHHIISS 形式
    • timestamp使用4个字节存储
    • timestamp的特点是所对应的记录不论哪个字段被更新，该字段都会更新到当前时间（比如需要记录操作时间的字段）注意：在MySQL8以后，取消了timestamp的默认自动更新，如果需要使用，需要额外使用属性：
      on update current_timestamp
      例如: alter table 表名 字段名 timestamp on update current_timestamp;

  • 日期：date，用来记录年月日信息

    • 存储日期的格式为：YYYY-MM-DD

数据操作

• 新增数据 insert into 表名 (字段列表) values(字段列表对应的值顺序列表)
• 查看数据 select */字段列表 from 表名;
  • 后接where进行条件查询
• 更新数据 update 表名 set 字段 = 新值[,字段 = 新值] [where条件筛选];
• 删除数据 delete from 表名 [where条件];（删除不可逆）

2.属性

空属性（NULL）

• 默认是允许为Null值
• 不允许为空：Not Null

默认值属性（Default）

• 默认情况下基本为Null
• 主动设置后，默认值改变

主键（primary key）

主键：用来保证整张表中对应的字段永远不会出现重复数据

• 主键在一张表中只能有一个
• 主键的另外一个特性是能够提升主键字段作为查询条件的效率（索引）
• 主键不能为空：Not Null（默认）
• 逻辑主键：数据没有具体业务意义，纯粹是一种数值数据
  • 逻辑主键通常是整数：int
  • 逻辑主键目的是方便检索和数据安全（不暴露数据真实信息）
• 复合主键：多个字段共同组成不能重复的数据
  • primary key(字段1,字段2,…字段N)
  • 联合主键使用不多，一般也不会超过2个字段

自动增长（auto_increment）

自增长：被修饰的字段在新增时，自动增长数据

• 自增长只能是整数类型，而且对应的字段必须是一个索引（通常逻辑主键）
• 一张表只能有一个自动增长
• 自增长数据可以理解为一种默认值，如果主动给值，那么自动增长不会触发
• 自增长由两个变量控制
  • 初始值：auto_increment_offset，默认是1
  • 步长：auto_increment_increment，默认值也是1
  • 查看自增长控制：show variables like 'auto_increment%';
    例如: id int primary key auto_increment（设置id为主键，并且主键自增长）

唯一键（unique key）

唯一键：用来维护数据的唯一性

• 一个表中可以有多个唯一键
• 唯一键与主键的区别在于唯一键允许数据为Null（而且Null的数量不限）
• 唯一键与主键一样，可以提升字段数据当做条件查询的效率（索引）
• 复合唯一键：多个字段共同组成
  • unique key(字段1,字段2,…字段N)
  • 一般不会出现，最多2个字段组成

唯一键能够弥补主键只有一个的特性（不限定数据量）
唯一键使用的位置应该要确保该字段数据会用作数据检索条件

字段描述（comment）

描述：comment，是用文字描述字段的作用的

• comment代表的内容是对字段的描述

  • 例如
    • stu_id varchar(10) not null comment ‘学号’,

• 如果涉及到字符集（中文）一定要在创建表之前设置好客户端字符集（否则会出现描述乱码）

3.数据库设计三大范式

第一范式

1NF，数据字段设计时必须满足原子性（即数据字段不可再被拆分）

• 1NF要求字段数据是不需要拆分就可以直接应用
• 如果数据使用的时候需要进行拆分那么就违背1NF

拆分字段为多列的方式

使用SUBSTRING_INDEX(str,delim,count) 拆分后的字段名 语句
– str: 被分割的字符串; delim: 分隔符; count: 分割符出现的次数

第二范式

第二范式：2NF，字段设计不能存在部分依赖

• 部分依赖：首先表存在复合主键，其次有的字段不是依赖整个主键，而只是依赖主键中的一部分
  

• 部分依赖解决：让所有非主属性都依赖一个候选关键字

  • 最简单方式：取消复合主键（一般选用逻辑主键替代，但是本质依然是复合主键做主），所有非主属性都依赖主属性（逻辑主键）
  • 正确方式：将部分依赖关系独立成表

第三范式

第三范式：3NF，字段设计不能存在传递依赖

非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列m既依赖于全部主键，又依赖于非主键列n的情况。

• 传递依赖：字段某个非主属性不直接依赖主属性，而是通过依赖某个其他非主属性而传递到主属性之上

不能出现下图所示的情况

• 传递依赖解决：让依赖非主属性的字段与依赖字段独立成表

4.表关系

一对一

一对一关系：一张表中的一条记录与另外一张表中有且仅有一条记录有关系

• 一对一关系通常是用来将一张原本就是一体的表拆分成两张表
  • 频繁使用部分：常用字段
  • 不常使用部分：生僻字段
  • 使用相同的主键对应
• 一对一关系设计较多使用在优化方面

步骤
1、一张表的数据字段较多且数据量较大
2、表中有部分字段使用频次较高，而另一部分较少使用
3、将常用字段和不常用字段拆分成两张表，使用同样的主键对应

一对一关系设计

• 将常用字段取出，与学号组合成一张常用表
• 将不常用字段取出，与学号组合成一张不常用表
• 表与表数据对应关系：基于学号（唯一）是一对一关系
  
  

一对多

一对多关系：也叫多对一关系，一张表中的一条记录与另外一张表的多条记录对应，反过来另外一张表的多条记录只能对应当前表的一条记录

• 一对多关系是实体中非常常见的一种关系，实体设计时也应用非常多
• 一对多关系的核心解决方案是如何让记录能够正确匹配到另外表中的数据
  • 一表设计：一表记录在另外一张表中有多条记录，所以无法记录多个字段（违背1NF）
  • 多表设计：多表记录在另外一张表中只有一条记录，可以设置字段记录对应的主属性（通常主键）

步骤
1、确定实体间的关系为一对多（多对一）关系
2、在多表中增加一个字段记录一表中对应的主属性

• 基于新的讲师表与学科表产生了关联关系（多对一）
• 基于讲师表可以知道讲师所属学科
• 基于学科ID可以统计出不同学科的讲师数量

多对多

多对多关系：一张表中的一条记录对应另外一个表中多条记录，反过来一样

• 多对多关系在实体中是最常见的关系
• 多对多关系是无法在自身表中维护对应表关系的（违背1NF），需要通过第三方表来实现将多对多关系变成多个多对一关系
  • 设计一个中间表：记录两张表之间的对应关系（主属性）
  • 中间表与其他表都是多对一的关系

步骤
1、确定实体间的关系为多对多关系
2、设计中间表，记录两张表的对应关系

5.高级SQL语句

增

1.批量插入

全字段批量插入
insert into 表名 values(值列表1),(值列表2),…(值列表N);
部分字段批量插入（注意字段默认值）
insert into 表名 (字段列表) values (值列表1),(值列表2),…(值列表N);

2.蠕虫复制：从已有表中复制数据直接插入到另外一张表（同一张表）

insert into 新表名 [(字段列表)] select 字段列表 from 被复制的表名;（把被复制的表的数据，复制到新表下）

3.主键冲突：在数据进行插入时，包含主键指定，而主键在数据表已经存在

解决方案

忽略冲突：保留原始记录
insert ignore into 表名 [(字段列表)] values(值列表);
冲突更新：冲突后部分字段变成更新
insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值[,字段=新值…];
冲突替换：先删除原有记录，后新增记录
replace into 表名 [(字段列表)] values(值列表); # 效率没有insert高（需要检查是否冲突）

(一般我们都给主键设置自增长，插入含有主键的数据时，给它设置为null，让mysql自行生成主键)

删

1.删除数据表

清空数据：将表中的所有数据清除，并且将表的所有状态回到原始状态**（先删除表，再重新创建表）**
truncate 表名

查

1.字段起别名

字段名 （as） 别名 【as可以省略】

2.FROM关键字（数据的来源）

• 单表数据源：数据源就是一张表 from 表名
• 多表数据源：数据来源是多张表（逗号分隔） from 表名1,表名2,...表名N
• 子查询数据源：数据来源是一个查询结果 from (select 字段列表 from 表名) as 别名（把（）里查询出来的数据看做一个新表，给它起别名）
  • 数据源要求必须是一个表
  • 如果是查询结果必须给起一个表别名
• 数据表也可以指定别名
  • 表名 （as） 别名

3.运算符

• 比较运算符
  • >（大于）、<（小于）、=（等于）、>=（大于等于）、<=（小于等于）、<>（不等于）
  • between A and B：A和B之间（A小于B），包括A和B本身（数值比较）
  • in (数据1,数据2,…数据N)：在列举的数据之中
  • like ‘pattern’：像上面样的，用于字符串比较
    • _：单下划线，匹配对应位置的一个任意字符（ab_：ab开头+一个字符，匹配abc，ab1，但不能匹配abcd）
    • %：匹配当前位置往后任意数量任意字符（ab%：ab开头+任意数量任意字符，匹配abc，ab1，abcd）
• 逻辑运算符
  • and（逻辑与）、or（逻辑或）、not（逻辑非）
• null运算符
  • is null（为空）、is not null（不为空）

4.group by子句

分组统计，根据某个字段将所有的结果分类，并进行数据统计分析

• group by子句一定是出现在where子句之后（如果同时存在）
• 分组统计可以进行统计细分：先分大组，然后大组分小组
• 分组统计需要使用统计函数
  • group_concat()：将组里的某个字段全部保留
  • any_value()：不属于分组字段的任意一个组里的值
  • count()：求对应分组的记录数量
    • count(字段名)：统计某个字段值的数量（NULL不统计）
    • count(*)：统计整个记录的数量（较多）
  • sum()：求对应分组中某个字段是和
  • max()/min()：求对应分组中某个字段的最大/最小值
  • avg()：求对应分组中某个字段的平均值
    例如
    select count(*/字段名),字段A from 表名 group by 字段B;
    (
    字段A是select所要查询显示的字段之一
    字段B是被分组的字段
    )

注意 :group by语句，一定要跟count（）一起。因为group by是对count所记录的数值进行分组

• 只有count（）没有group by的话，只会先统计总数，在分组时，只会显示第一种组名
• 若只有group by而没有count（）的话，会进行分组，但是不会有数量显示

5.having语句（能用where解决问题的地方绝不使用having）

having子句：类似于where子句，是用来进行条件筛选数据的

• having子句本身是针对分组统计结果进行条件筛选的
• having子句必须出现在group by子句之后（如果同时存在）
• having针对的数据是在内存里已经加载的数据
• having几乎能做where能做的所有事，但是where却不一定
  • 字段别名（where针对磁盘数据，那时还没有）
  • 统计结果（where在group by之前）
  • 分组统计函数（having通常是针对group by存在的）

注意
1、having也是用于数据筛选的，但是本质是针对分组统计，如果没有分组统计，不要使用having进行数据筛选
2、能用where解决问题的地方绝不使用having

• where针对磁盘读取数据，源头解决问题
• where能够限制无效数据进入内存，内存利用率较高，而having是针对内存数据筛选==

6.ORDER BY 语句

ORDER BY 语句用于根据指定的列对结果集进行排序。（默认按照升序对记录进行排序）

select 字段列表/* from 表名 where 条件 order by 字段别名1 asc/desc, 字段别名2 asc/desc

 1.asc 升序，可以省略，是数据库默认的排序方式
 2.desc 降序，跟升序相反。

7.limit子句

limit子句：限制数据的获取数量（记录数）

• limit子句必须在order by子句之后（如果同时存在）

• limit限制数量的方式有两种

  • limit 数量：限制获取的数量（不保证一定能获取到指定数量）
  • limit 起始位置,数量：限制数据获取的位置以及数量（分页）(起始位置不包括自身，比如limit2,1就是说从第二条开始的一条数据，不算自身，第三条数据)
    - 例如

  select * from 表名 limit 数字A；（查询前A条数据）
  select * from 表名 limit 数字A，数字B；（查询第A+1条，到第B条数据）

6.多表操作

1.联合查询

联合查询：union，是指将多个查询结果合并成一个结果显示

• 联合查询是针对查询结果的合并（多条select语句合并）
• 联合查询语法

select 查询
	union 查询选项
select 查询
...

• 联合查询要求：联合查询是结果联合显示
  • 多个联合查询的字段结果数量一致
  • 联合查询的字段来源于第一个查询语句的字段（即 两张表有数据的字段名一定要相同）
  • 想要进行where查询的话，需要两条select语句都用where，单独一条用where只能作用于一条
  • 如果想要对单独select的结果进行排序，需要将需要排序的select指令进行括号包裹（括号里使用order by）order by必须配合limit才能生效（limit一个足够大的数值即可）

2.连接查询

连接查询：join，将两张表依据某个条件进行数据拼接

• join左右各一张表：join关键字左边的表叫左表，右边的表叫右表
• 连接查询的结果都是记录会保留左右表的所有字段（字段拼接）
  • 具体字段数据依据查询需求确定
  • 表字段冲突需要使用表别名和字段别名区分
• 不同的连表有不同的连接方式，对于结果的处理也不尽相同
• 连接查询不限定表的数量，可以进行多表连接，只是表的连接需要一个一个的连（A join B join C …）

2.1外连接（是用主表数据去匹配从表数据，倘若从表没有数据，则会生成一条null数据）

• 外连接分为两种
  • 左外连接（左连接）：left join
  • 右外连接（右连接）：right join
• 外连接有主表和从表之分
  • 左连接：左表为主表
  • 右连接：右表为主表
• 外连接是将主表的记录去匹配从表的记录
  • 匹配成功保留
  • 匹配失败（全表）：也保留，只是从表字段置空

2.1.1左连接

SELECT 左表的字段 as 别名A ,右表的字段 as 别名B（防止冲突起别名） FROM 左（主）表名 （别名） LEFT JOIN 右（从）表名 （别名） ON 条件;

2.1.2右链接

SELECT 左表的字段 as 别名A,右表的字段 as 别名B（防止冲突起别名） FROM 左（从）表名 （别名）right JOIN 右（主）表名 （别名） ON 条件;

2.2内连接

内连接：[inner] join，将两张表根据指定的条件连接起来，严格连接

• 内连接是将一张表的每一条记录去另外一张表根据条件匹配
  • 匹配成功：保留连接的数据
  • 匹配失败：都不保留（倘若，主表或者从表有一条数据对应不上（比如主表有数据，但是从表没对应的）就不会产生查询结果（数据））
• 内连接语法： 左表 join 右表 on 连接条件

7.遇到的坑

7.1.merge into语句 学习

• 语法
  merge into 【表1】using 【表2】 on 【关联条件】when matched then [满足关联条件执行的语句】 when not matched then [不满足关联条件执行的语句]

• 背景
  merge into 【表1】using 【表2】 on 【关联条件】when matched then [满足关联条件执行的语句（update xxxx）】 when not matched then [不满足关联条件执行的语句 （insert xxxx）]

这是在我存储过程里用到的语句，本意是没有的数据直接插入，根据特定字段确认是同一笔（有关联性的），不插入，而是去update更新他，结果两条都插入了

• 坑点
  on 后面的关联条件不要使用可能为null的字段
  在on子句，where子句，Merge或case的when子句中，任何值和null比较的结果都是false
  原文出处

• 解决方式
  我是使用了nvl语句nvl（xxx，‘’）