数据库语句系统总结--持续更新

数据库系统总结 （持续更新）

---

---

1. 数据库分类

• 关系型数据库:

  • 基于关系模型组织的数据库，其数据以表格的形式存储，行代表记录（数据集中的实体），列代表字段（实体的属性）。

  • 表与表之间存在关系,一对一 ,一对多,多对多

  • MySQL, Oracle, SQLServer, DB2, SQLite

• 非关系型数据库:

  • 不遵循传统的关系模型，它们使用不同的数据模型来存储数据，如键值对（Key-Value）、文档（Document）、列族（Column Family）、图（Graph）等。非关系型数据库通常不强制要求数据的表结构，具有更高的灵活性和可扩展性。

  • Redis, HBase, MongeDB

2. DDL语句

2.1 DDL语句操作数据库

# 1.查看所有数据库
show databases;

# 2.创建数据库
# 方式一:采用默认码表
create database [if not exists]数据库名
# 方式二:才用指定码表
create database 数据库名 character 'utf8';
create database 数据库名 charset 'utf8';

# 3.修改数据库码表
alter database 数据库名 charset 'utf8';

# 4.删除数据库
drop database 数据库名;

# 5.查看当前使用的数据库
select database();

# 6.查看当前数据库码表
show create database 数据库名;

# 7.切换数据库
use 数据库名;

2.2 DDL操作数据表

/*
1. 单表约束

  - 主键约束, primary key 特点: 非空, 唯一, 且一般结合 auto_increment 自动增长 一起使用.

  - 唯一约束, unique

  - 非空约束, not null

  - 默认约束, default

2. 多表约束

  - 外键约束 foreign key
*/
# 1. 显示当前数据库下所有表
show tables;

# 2. 创建数据表
# 2.1 创建表的时候就加约束
create table 表名(
    列名 字段类型(int, float, varchar(n)) 约束,
    列名 字段类型(int, float, varchar(n)) 约束,
    ......
    列名 字段类型(int, float, varchar(n)) 约束
);

# 2.2 创建表以后添加加约束
alter table 表名 add primary key(列名);
alter table 表名 modify 列名 数据类型 primary key;

alter table 表名 modify 列名 数据类型 约束;
alter table teacher modify id int auto_increment; 

alter table teacher modify phone varchar(11) unique ;       # 添加默认约束
alter table teacher add unique (phone);                     # 效果同上.

# 3.查看表结构
desc 表名;

# 4. 修改表名
alter table 旧表名 rename 新表名;
rename table 旧表名 to 新表名;

2.3 DDL操作字段

# 1. 给表添加字段
 alter table 表名 add 字段名 数据类型 [约束]; 
 
# 2.给表修改字段
alter table 表名 modify 字段名 数据类型 [约束]; 

# 3.删除表字段
alter table 表名 drop 字段名;

3. DML语句

3.1 插入数据(增加)

# 添加单条格式: insert into 表名(列1, 列2...) values(值1, 值2...);

# 上述格式的变形写法: insert into 表名 values(值1, 值2...); 
# 不写列了, 默认是全列名, 即: 后边的值要全写, 和列的个数, 顺序一致.

# 添加多条格式: insert into 表名(列1, 列2...) values(值1, 值2...), (值1, 值2...), (值1, 值2...)...;
# 添加多条格式: insert into 表名 values(值1, 值2...), (值1, 值2...), (值1, 值2...)...;

3.2 修改表中数据

# 非常非常非常重要: 修改 或者 删除数据的时候一定一定一定要写where条件, 不写就是操作所有.
# 格式: update 表名 set 列名=值, 列名=值 where 条件;

3.3 删除表中数据

# 格式: delete from 表名 where 条件;
# 非常非常非常重要: 修改 或者 删除数据的时候一定一定一定要写where条件, 不写就是操作所有.

3.4 拓展(备份表)

# 情况1: 备份表不存在.      student(源表) => student_tmp(备份表)
# 格式: create table 备份表名 select * from 源表 where 条件;
create table student_tmp select * from student;

# 细节: 备份表主要是备份 字段和数据的, 不是备份 约束的.
desc student;
desc student_tmp;

# 情况2: 备份表存在.        student(源表) => student_tmp(备份表)
# 格式: insert into 备份表名 select * from 源表 where 条件;
insert into student_tmp select * from student;

# 清空备份表的数据.
truncate student_tmp;

# 查看备份后的效果.
select * from student_tmp;


# 模拟误删 源表.
# 1. 删除源表数据.
truncate student;
# 2. 查看源表数据, 没了.
select * from student;
# 3. 从备份表导入数据到源表.
insert into student select * from student_tmp;
# 4. 查询源表, 数据来了.
select * from student;

4. DQL语句

• 比较运算符, >, <, >=, <=, !=, <>
• 逻辑运算符.and, 逻辑与, 并且的意思, 要求条件都要满足, 即: 有false(假)则整体为false
  or, 逻辑或, 或者的意思, 只要满足任意1个条件即可, 即: 有true(真)则整体为true
  not, 逻辑非, 取反的意思, 以前是true, 取反后是false, 以前是false取反后是true
• 模糊查询. 格式: like ‘内容’ _代表任意的1个字符, %代表任意的多个字符
• 范围查询, 格式: in (…), not in (…), between … and …
• 非空查询. is null 为空, is not null 不为空.

4.1 简单查询

# 1. 查询表中所有数据
select * from 表名;

#2: 查询指定列的信息.
select 字段名,字段名,字段名 from 表名;

# 3: 别名查询, 注意: 只是在显示的时候, 有别名, 表中的字段名, 并没有发生任何的变化.
select pid as '商品id', pname as '商品名', price as '商品价格' from product as p;
# 细节: as 可以省略不写.
select pid '商品id', pname '商品名', price '商品价格' from product p;

# 4: 去重查询.  去重的意思是: 重复的数据保留1个, 不是重复的全部删除.
select distinct price from product;         # 只根据 price 去重
select distinct pname, price from product;  # 把 pname 和 price当做1个整体, 进行去重.   即:  联想,5000 和 雷神,5000 不是相同数据.

4.2 完整查询语句(包括分组, 聚合, 组后, 分页)

• 单表查询完整语句

select
    [distinct] 列名1, 列名2 as 别名, 列名3...
from
    数据表名
where
    组前筛选
group by
    分组字段1, 分组字段2...
having
    组后筛选
order by
    排序的列1, 列2 [asc | desc]
limit
    起始索引, 数据条数;

• 条件查询

/*
格式:
    select * from 数据表名 where 条件;
where后的条件可以写什么:
    比较运算符: >, >=, <, <=, =, !=, <>
    范围筛选:
        in (值1, 值2...);
        not in (值1, 值2...);
        between 值1 and 值2;      # 包左包右
    模糊查询:
        like '_值';      _ 只能代表任意的1个字符
        like '%值';      % 可以代表任意的多个字符.
    非空查询:
        is null;        # 是否为空
        is not null;    # 是否不为空.
    逻辑运算符:
        and     逻辑与, 并且的意思, 要求条件都要满足, 有False则整体为False.
        or      逻辑或, 或者的意思, 只要满足任意1个条件即可, 有True则整体为True.
        not     逻辑非, 取反的意思, True => False,  False => True
*/

• 聚合查询

/*
聚合查询介绍:
    概述:
        之前我们学的查询都是 "横向"查询, 那如果我们要对某列值做 纵向查询, 这个时候, 就需要用到: 聚合(Aggregate)函数了.
    分类:
        max()   某列的 最大值
        min()   某列的 最小值
        sum()   某列的 和
        avg()   某列的 平均值
        count() 统计表的数据总条数, 或者某列的非空值的个数.
*/


-- 问题: count(1), count(列), count(*)的区别是什么?
-- 答案: 区别1: 是否统计null值.
               count(1), count(*): 会统计null值.
               count(列): 不会统计null值, 即: 只统计非空值的个数.
-- 		区别2: 效率问题.
   			   count(主键列) > count(1) > count(*) > count(列)

• 分组查询

/*
分组查询介绍:
    分组查询 = 按照分组字段, 把整表数据 分成n个组, 然后在各个组内做 聚合操作等.
    格式:
        select 分组字段, 聚合函数 from 数据表名 where 组前筛选 group by 分组的列 having 组后筛选;
    细节:
        1. 根据谁分组, 就根据谁查询. 即: 分组查询的查询列只能出现 分组字段 + 聚合函数.
        2. 扩展: 如果某列值 和 分组字段是一对一的关系(一一对应的), 则: 分组的时候即使没写这个字段, 也可以加上.
           例如: 公司id 和 公司名一一对应, 我们按照公司id分组, 但是查询的时候, 可以把公司名这一列也加上.
        3. 面试题: having 和 where的区别是什么?
            where:  组前筛选, 后边不能跟聚合函数.
            having: 组后筛选, 后边可以跟聚合函数.
*/

• 分页查询

/*
分页查询介绍:
    概述:
        就是一次性的从数据表中, 获取指定条数的数据, 就叫: 分页查询.
    好处:
        1. 降低服务器的压力.
        2. 降低浏览器端的压力.
        3. 提高用户体验.
    格式:
        limit 起始索引, 数据条数;
    细节:
        1. SQL中, 表的每条数据都是有自己的编号(也叫: 下标, 角标, 索引)的, 且编号是从 0 开始的.
        2. 要搞明白分页, 核心是理解如下的4个名词:
            数据的总条数:     count(主键列)
            每页的数据条数:   产品经理, 项目经理, 你定.
            每页的起始索引:   (当前页数 - 1) * 每页的数据条数
            总页数:          (总条数 + 每页的数据条数 - 1) / 每页的数据条数         注意: 这里是整除, 即: 不要余数或者小数位.
                            (23 + 5 - 1) / 5 = 5
                            (25 + 5 - 1) / 5 = 5
                            (26 + 5 - 1) / 5 = 6
*/

4.3 多表查询

4.3.1 建表语句中的外键约束

外键约束介绍:
    概述:
        它是用来保证属于的 完整性 和 一致性的.
    添加外键约束的格式:
        场景1: 建表时添加.
            在外表中写: [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名);
        场景2: 建表后添加.
            alter table 外表名 add [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名);
    删除外键约束:
         alter table 外表名 drop foreign key 外键约束名;
    特点:
        外表的外键列 不能出现 主表的主键列 没有的数据.

4.3.2 多表查询

• 交叉查询

# 格式: select * from A, B;
# 结果: 两张表的笛卡尔积, 即: 表A的总数 * 表B的总数
select * from 表1, 表2;

• 连接查询

一. 内连接  --  两个表的交集
   1.显示内连接
     select * from 表1 inner join 表2 on 关联条件 where.....;   其中的inner可以省略不写
   2.隐式内连接
     select * from 表1,表2 where 关联条件;
二. 外连接
   1.左外连接
   	 select * from 表1 left outer join 表2 on 关联条件 where.....;  其中outer可以省略不写
   	 结果是: 左边的全集 + 两表的交集  不匹配的用null填充
   2.右外连接
     select * from 表1 right outer join 表2 on 关联条件 where.....;  其中outer可以省略不写

• 子查询

/*
概述:
    一个SQL语句的查询条件 需要依赖 另一个SQL语句的查询结果, 这种写法: 就称之为子查询.
    里边的叫: 子查询.  外边的叫: 父查询(主查询)
格式:
                主查询 | 父查询               子查询
    select * from 表名 where 列名 in (select 列名 from 表名...);
*/

• 自关联(自连接)查询

/*
概述:
    自己跟自己关联,表中的一列数据跟另一列数据存在关系
格式:
     select * 
     from 表名 as 别名1 
     join 表名 as 别名2 on 别名1.列名 = 别名2.列名
     .....
     join 表名 as 别名n on 别名(n-1).列名 = 别名n.列名
              
*/

4.3.3 拓展 case when

/*
格式1:
    case
        when 条件1 then 值1
        when 条件2 then 值2
        ...
        else 值n
    end [as 别名]

格式2: 语法糖, 如果操作的是同1个列, 且都是等于的判断, 则语法格式可以写成如下的样子.
    case 字段名
        when 结果1 then 值1
        when 结果2 then 值2
        ...
        else 值n
    end [as 别名]

*/

聚合函数里边也可以套 case when

4.4 窗口函数

/*
窗口函数介绍:
    概述:
        窗口函数也叫 开窗函数, 它是MySQL8.X的新特征, 主要是用于 做分组排名, 聚合, 获取指定值的.
       
       
        其中用到最多的函数就是:
           排序相关的函数: row_number(), rank(), dense_rank(), ntile(n)
       
        除此以外, 还有其他的一些窗口函数, 例如:
            聚合函数相关:
                sum() over(...)
                max() over(...)
                min() over(...)
                count() over(...)
                avg() over(...)
            其它函数相关:
                lag(), lead(), first_value(), last_value()
    精髓:
        窗口函数 = 给表新增1列, 至于新增的内容是什么, 取决于和什么函数一起用.
   
   
   排序类函数介绍:
        假设初始数据为: 100, 90, 90, 80, 则三个排序函数结果为:
            row_number(): 1, 2, 3, 4
            rank():       1, 2, 2, 4
            dense_rank(): 1, 2, 2, 3
    格式:
        窗口函数 over(partition by 分组字段 order by 排序的列 [asc | desc])
*/

一. lag()函数详解      -- 它允许你访问结果集中当前行的前一行的数据。

	lag(expression [, offset [, default]]) over (  
    	[PARTITION BY 分组字段 ] ORDER BY 排序的列 [ASC|DESC], ...  
    )
-- expression：你想要从前一行中获取的列名或表达式。
-- offset：（可选）一个正整数，表示你想要向前查看的行数。如果不指定，默认为 1，即查看前一行。
-- default：（可选）如果当前行没有足够的前置行（例如，在结果集的第一行或分区内的第一行），则返回此默认    值。如果不指定，则返回 NULL。
-- PARTITION BY 子句（可选）：将结果集分成多个分区，在每个分区内独立应用 LAG() 函数。
-- ORDER BY 子句：定义 LAG() 函数查找前一行数据的顺序。这对于 LAG() 函数来说是必需的，因为它需要知    道哪一行是“前一行”。

二. lead()函数详解    -- 它允许你访问结果集中当前行之后指定行数的数据。
    
    lead(expression [, offset [, default]]) over (  
    	[PARTITION BY 分组字段 ] ORDER BY 排序的列 [ASC|DESC], ...  
    )
-- expression：你想要从后续行中获取的列名或表达式。
-- offset：（可选）一个正整数，表示你想要向后查看的行数。如果不指定，默认为 1，即查看后一行。
-- default：（可选）如果当前行没有足够的后续行（例如，在结果集的最后一行或分区内的最后一行），则返回此    默认值。如果不指定，则返回 NULL。
-- PARTITION BY 子句（可选）：将结果集分成多个分区，在每个分区内独立应用 LEAD() 函数。
-- ORDER BY 子句：定义 LEAD() 函数查找后续行数据的顺序。这对于 LEAD() 函数来说是必需的，因为它需要    知道哪一行是“后一行”。

三. first_value()函数详解    -- 它用于在数据的一个分区内按照指定的排序顺序返回该分区中的第一个值。

    FIRST_VALUE(column_name) OVER (  
           PARTITION BY 分组字段  
           ORDER BY 排序的列 [ASC|DESC]  
    )

-- column_name：你想要从每个分区或窗口中取出的那列的名称。
-- PARTITION BY partition_column_name：定义了结果集中哪些行会被视为一个分组或“窗口”来进行计算。如    果省略，整个结果集将被看作一个大的分区。
-- ORDER BY sort_column_name [ASC|DESC]：定义了在每个分区内部如何对行进行排序，以便确定哪个值      是“第一个”。ASC 表示升序（默认），DESC 表示降序。

四. last_value()函数详解     -- 它用于在数据的一个分区内按照指定的排序顺序返回该分区中的最后一个值。

总结

• 本文将数据库语句进行了分类整理，并附上代码演示与注释，文章会持续更新数据库中新的函数跟特殊用法，让我们一起加油，成为SqlBoy。