SQL语言基础（二）

SQL语言，是结构化查询语言(Structured Query Language)的简称。
SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；
是各种数据库厂商提供的数据库均支持的ISO标准，同时也是数据库脚本文件的扩展名。

---

续SQL语言基础（一）

DQL

1. 查询语法

   - select [distinct] [列名，列名...] *  from 表名 where 条件;       # * 全表查询显示，[distinct]可选择去重，[列名]只查询对应列
   - select 列名 as 列别名  from 表名 as 表别名 where 条件;           #多表或复杂查询时可以使用别名
   # 注意
   - 多列去重时只有所有列都相同时才会去重，有一列不同就不会去重
   - select 列名，列名+xxx from 表名;                                # 对于列的显示进行运算，不影响原表数据
   

2. 条件查询

   比较运算符	> < = <= >= <> !=	大于、小于、等于、大于（小于）等于、不等于
   	BETWEEN … AND …	显示在某一区间的值（含头含尾）
   	IN（set）	显示在IN列表中的值
   	LIKE “XX”	模糊查询，LIKE 语句中，%号代表灵=零个或多个任意字符，_代表一个字符
   	IS NULL / IS NOT NULL	判断是否为空
   逻辑运算符	and	多个条件同时成立时为真
   	or	多个条件任意成立为真
   	not	条件不成立时为真

   - select * from 表名 where 列名2 = "xx";                         #查询列名2的值等于"xx"的行
   - select * from 表名 where not (列名2 = "xx");                   #查询列名2的值不等于"xx"的行
   - select * from 表名 where 列名2 <> "xx";                        #查询列名2的值不等于"xx"的行
   - select * from 表名 where 列名2 >= 100 and 列名 <= 1000;        #查询列名2的值在100到1000之间的行
   - select * from 表名 where 列名2 between 100 and 1000;           #查询列名2的值在100到1000之间的行
   - select * from 表名 where 列名2 = 100 or 列名 = 1000;           #查询列名2的值在100到1000之间的行
   - select * from 表名 where 列名2 in (100,1000);                  #查询列名2的值在100到1000之间的行
   - select * from 表名 where 列名2 like '_香%';                    #查询列名2的第二个字符是“香”的行  
   - select * from 表名 where 列名2 like '____';                    #查询列名2是4个字符的行
   

3. 排序查询

   - 通过order by 语句，可以将查询出的结果进行排序。(数字和字母可以排序，汉字排序没有实际意义，也没有使用场景)
   - select * from 表名 order by 排序字段 ASC | DESC;（ASC 升序 默认方式，DESC 降序）
   - select * from 表名 order by 主要排序字段 [ASC | DESC], 次要排序字段 [ASC | DESC];    #排序字段可以由多个字段共同决定，在主要条件排序完成后，还有值相同的情况时，取决于次要字段 
   

4. 聚合查询

   - 利用数据库内置的聚合函数完成聚合查询
   - select count(*) from 表名;             #整表统计行数（只要该行不全为NULL就会计数）
   - select count(列名/字段名) from 表名;    #只对表中的某一列做行数统计
   - select max（字段名）,min(字段名) from 表名； 
   

   聚合函数	作用
   count（）	统计指定列不为NULL的记录行数
   sum()	计算指定列的数值和，如果指定列数据类型不为数值，那么计算结果为0
   max()	计算指定列的最大值，如果指定列为字符串类型，那使用字符串排序运算
   min()	计算指定列的最小值，如果指定列为字符串类型，那使用字符串排序运算
   avg()	计算指定列的平均值，如果指定列数据类型不为数值，那么计算结果为0
   …	…

5. 分组查询

   - 分组查询是指使用group by 字句对查询的信息进行分组
   - 格式：
        select 字段1,字段2... from 表名 group by 分组字段 having 分组条件;
   -   分组操作中的having 子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
   # 注意
   - 1)having是在分组后对数据进行过滤，where是在分组前对数据进行过滤。
   - 2)having后面可以使用分组函数（统计函数），where后面不可以使用分组函数。
   - 3)分组后会有多个按组分配的临时表。
   

6. SQL执行顺序

   from > where > group by > 聚合函数 > having > select > order by > limit

   分组后：select 后只能跟分组字段和聚合函数

   - select 字段名1，聚合函数 from 表名 group by 字段名1 having （select 后跟的字段） 条件;

7. 分页查询

   - 由于数据量很大，显示器无法一次性显示全部数据，需要将数据进行分页显示。
   - select 字段1，字段2... from 表名 limit M,N;
   - M：表示从第几行数据开始，N:表示显示多少行数据
   

8. 跨表抽取注入

   - insert into 表2 select 字段名（列名） from 表1 where 条件 ;      #从表1抽取数据插入表2
   

多表操作

1. 表与表之间的关系

   处理数据表一对多或者多对多的对应关系

2. 外键约束

   - 主表中的主键列 控制 从表中的外键
   - alter table 从表 add ( [constraint] [外键名称] ) foreign key (从表外键字段名) reference 主表(主表的主键);    # 创建外键约束
   - alter table 从表 drop foreign key 外键名;                                                                 # 删除外键约束   
   # 注意：
   创建外键约束时，加上外键名后才可以对外键进行删除
   

3. 一对多数据操作

   - 插入：
     主表（一）可以随意插入数据，从表（多）插入数据需要受主表限制
   - 删除：
     主表的数据如果受到从表依赖，则不能删除，从表的数据可以随意删除
   

4. 多表查询-交叉连接查询

    - 查询得出多个表的乘积（笛卡尔集），实际意义并不大，只是包含了可能需要的数据
    - select * from 表1,表2; 
    - 按行表1的每一行拼接表2的每一行  
   

5. 多表查询-内连接查询（两张表的交集）

    - 隐式内连接：
    - 交叉连接查询的尾部 增加 where 条件，即 select * from 表1,表2 where 条件1 and 条件2;
    - 显式内连接：
    - select * from 表1 inner join 表2 on 条件; 
   

6. 多表查询-外连接

   - 关键字 outer join
   - 左外连接
   - 以左表为主，左表全部输出，右表来做对应，有对应关系的输出，没有的补NULL
   - select * from A left outer join B on 条件；
   - 右外连接
   - 以右表为主，右表全部输出，左表来做对应，有对应关系的输出，没有的补NULL
   - select * from A right outer join B on 条件；
   

7. 多表查询-子查询

   - 查询过程中在select内部嵌套select就是子查询
   - 嵌套查询结果为一个值
   - select * from table007 where xxx = (select 字段 from XX where 条件);
   - 嵌套查询结果为一个临时表
   - select * from 表1, (select * from 表2 where 条件) where 条件;
   - 嵌套查询结果为多个值
   - select * from 表1 where 表1.字段1 in (select 表2.字段2 from 表2 where 条件1 or 条件2)
   

索引

1. 概念以及分类

   根据存储类型不同，分成树型索引和哈希索引

   根据用途不同分为：普通索引、唯一性索引、主键索引（创建主键时会自动创建主键索引）

2. 普通索引

    # 创建索引(三种方式)
    - create index 索引名 on 表（字段/列名）;
    - alter table 表名 add index 索引名(字段/列名);
    - create table 表名(
        ID INT NOT NULL, 
        user varchar(20), 
        INDEX index_ID(ID) 
    ); 
   

3. 唯一索引

   - 三种创建方式
   - 方式一：直接创建
   - create unique index 索引名 on 表名(字段名);
   - 方式二：修改表结构方式添加索引
   - alter table 表名 add unique [索引名] (字段名);
   - 方式三：建表时直接指定
   - create table 表名(
        ID int not null,
        username varchar(20) not null,
        unique [索引名] (字段名) 
   ); 
   

4. 查看索引

   - 查看表中所有索引
   - show index from 表名;
   - 查看数据库所有索引
   - select * from mysql.`innodb_index_stats` a where a.`database_name`='数据库名';
   - 查看某一表索引
   - select * from mysql.`innodb_index_stats` a where a.`database_name`='数据库名' and a.`table_name` like '%表名%'; 
   

5. 删除索引

   # 两种删除方式 
   - drop index 索引名 on 表名;
   - alter table 表名 drop index 索引名;
   

6. 索引注意事项

   # 虽然索引可以加快查询速度，提高Mysql的处理性能，但是过多地使用索引也会造成弊端：
    - 创建索引和维护索引需要耗费时间，这种时间随着数据量增加而增加。
    - 除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就更大。
    - 当对表中的数据进行增加、删除和修改时，索引也需要动态的维护，这样就降低了数据维护速度。
    -  对于那些在查询中很少使用或者参考的列不应该创建索引。因为这些列很少使用到，因此有无索引并不能提高查询速度。相反，由于增加索引，反而降低了数据维护速度，增大了空间占用。           
   

开窗函数

1. Mysql8.0中增加了对开窗函数的支持

2. 开窗函数的语法规则

    <开窗函数> over (
    [partition by <列清单>]    #依据<列字段>分组，可以不加，不加会对全表进行排序并打编号
    order by <排序用列清单>     #依据<排序列>排序
    ); 
   

3. Mysql中支持的开窗函数有很多eg：row_number(),rank(),dense_rank()

   • eg中每个函数功能都是用于返回结果集的分组内每行的排名

     • 区别：
     • row_number() 不管排名是否变化总是按照顺序1，2，3，4…排列
     • rank() 会出先并列名次的情况，重复名次计数直接跳过
     • dense_rank() 并列名次，并且不会直接跳过重复名次计数