SQL语句

最新推荐文章于 2024-10-16 21:44:45 发布

墨斗先生爱编程

最新推荐文章于 2024-10-16 21:44:45 发布

阅读量103

点赞数

分类专栏：笔记 mysql 数据库文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_44744442/article/details/130326869

版权

笔记同时被 3 个专栏收录

15 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

mysql

1 篇文章 0 订阅

订阅专栏

一、通用语法

1、SQL通用语法

SQL语句可以单行或者多行书写，以分号结尾。(不同第三方可以设置)
可以使用空格和缩进来增加语句的可读性。
MySQL中SQL不区分大小写，一般关键字大写，数据库名、表名、列名小写。

2、注释方式

单行注释1

-- show databases;

单行注释2

# show databases;

多行注释

/* 
	多行注释
	show databases;
*/

3、语句分类

Client端操作MySQL数据库，实质就是对数据的增删改查
- 增：C – create 增加
- 查：R – retrieve 获取、查询
- 改：U – update 更新
- 删：D – delete 删除
SQL语句按照功能分类，对比EXCEl的操作
- 新建EXCEL --> 新建sheet页(excel默认新建3个空sheet以供使用) --> 处理数据
- 新建Database --> 新建表 --> 增删改查数据
  1. DDL语句：数据定义语言
  2. DML语句：数据操纵语言
  3. DQL语句：数据查询语言
  4. DCL语句：数据控制语言

二、DDL语句

Data Definition Language 数据定义语言

1、操作数据库

1.1、创建数据库

创建指定名称的数据库

CREATE DATABASE db1;

创建指定名称的数据库，并且指定字符集(一般指定utf8)

CREATE DATABASE db2 CHARACTER SET utf8;

1.2、查看/选择数据库

切换数据库

-- 切换数据库db1到db2
USE db2；

查看当前使用的数据库

SELECT DATABASE();

查询MySQL中的数据库

SHOW DATABASES;

查看数据库定义信息

SHOW CREATE DATABASE db1;

1.3、修改数据库

修改数据库字符集

-- 把数据库db1的字符集修改为utf8
ALTER DATABASE db1 CHARACTER SET utf8;

1.4、删除数据库

删除某某数据库

-- 删除数据库db1
DROP TABLE db1;

2、操作数据表

2.1、创建表

语法格式
- 最后一列不能加逗号

CREATE TABLE 表名(
	字段名称1 字段类型(长度),
	······
	字段名称n 字段类型(长度) 
)

快速创建一个表机构相同的表(复制表结构)

-- 根据db1创建db2
CREATE TABLE 新建表名 LIKE 被复制表名;

2.2、查看表

查看当前数据库中所有表名

SHOW TABLES;

查看数据表的结构

DESC 表名;

查看创建表的SQL语句

SHOW CREATE TABLE 表名;

2.3、删除表

删除某表

DROP TABLE 表名;

判断表是否存在，如果存在则删除，否则就不删除

DROP TABLE IF EXISTS 表名;

2.4、修改表

修改表名

RENAME TABLE 旧表名 TO 新表名;

表中增加字段

ALTER TABLE 表名 ADD 字段名称 字段类型(长度);

修改表中字段的数据类型或长度

ALTER TABLE 表名 MODIFY 字段名称 字段类型(长度);

修改列名称

ALTER TABLE 表名 CHANGE 旧列名 新列名 字段类型(长度);

删除列

ALTER TABLE 表名 DROP 列名;

3、SQL约束

作用：对表中的数据进一步的限制，从而保证数据的正确性、有效性、完整性。违反约束的不正确数据，将无法插入到表中
约束是针对字段的

3.1、主键约束

特点：不可重复、唯一、非空
作用：用来表示数据库中的每一条记录(用来唯一标识数据表中的一条记录)
增加主键语法格式

-- 格式一，写在对应字段出
字段名 字段类型 PRIMARY KEY
-- 格式二，写在所有字段最下面
PRIMARY KEY(字段名)
-- 格式三，独立的语句
ALTER TABLE 表名 ADD PRIMARY KEY(字段名)

主键设计思路
- 思路一：通常针对业务设计表主键
- 思路二：主键是给数据库和程序使用的，跟最终的业务无关，所以主键没有业务意义，只要能保证不重复即可。
删除表主键

自增主键
- 如果人工处理主键，容易造成重复或者空值，所以可以通过插入数据时，数据库自动生成主键的值。
增加自增主键语法格式

-- 自增默认从1开始
字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

-- 指定自增起始值
CREATE TABLE 表名(
	
)AUTO_INCREMENT = $VALUE

3.2、非空约束

特点：某一列不允许为空
语法格式

字段名 字段类型 not null

3.3、唯一约束

特点：表中某一列的值不能重复(对null不做唯一判断)
语法格式

字段名 字段类型 unique

主键约束与唯一约束的区别
1. 主键约束，唯一且不能为空(主键约束 = 非空约束 + 唯一约束)
2. 唯一约束，唯一但是可以为空
3. 一个表中只能有一个主键，但是可以有多个唯一约束

3.4、外键约束

外键：在从表中与主表的主键对应的那个字段
外键约束：让两张表之间产生一个对应关系，从而保证主从表的引用完整性
多表关系中的主表和从表
- 主表：主键id所在的表，约束别人的表
- 从表：外键所在的表中，被约束的表
语法格式
- 新建表时添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字 段名)

已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);

删除外键约束

ALTER TABLE 从表 DROP FOREIGN KEY 外键约束名称

注意事项
- 从表外键类型必须与主表主键类型一致，否则创建失败
- 添加数据时，应先添加主表中的数据
- 删除数据时，应该先删除从表中的数据

3.5、默认值约束

特点：用来指定某列的默认值
语法格式

字段名 字段类型 DEFAULT 默认值

三、DML语句

Data Manipulation Language 数据操纵语言

1、插入数据

1.1、插入全部字段

语法格式

-- 不写字段名
INSERT INTO 表名 VALUES(字段值1，字段值2···，字段值n);
-- 写所有字段名
INSERT INTO 表名（字段1，字段2···，字段n） VALUES(字段值1，字段值2···，字段值n);

1.2、插入指定字段

语法格式

INSERT INTO 表名（字段1，字段2，字段3） VALUES(字段值1，字段值2，字段值3);

注意
- 值与字段必须对应，个数相同，数据类型相同
- 值的数据大小，必须在字段指定的长度范围内
- varchar，char，date类型的值必须使用单引号包裹
- 如果插入空值，可以忽略不写，或者插入null

2、更改数据

2.1、不带条件

语法格式

UPDATE 表名 SET 列名 = 值

慎用此种方法，表中数据全部都更新

2.2、带条件

语法格式

UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,···，列名n = 值n WHERE 条件1 = 值

3、删除数据

3.1、删除所有数据

DELETE
- 语法格式

DELETE FROM 表名;

不推荐该种方法，实际操作是根据数据条数，逐条删除，效率低

TRUNCATE
- 语法格式

TRUNCATE TABLE 表名;

推荐该种方法，实际操作是先删除整张表，然后再重新创建一张一模一样的表，效率高

3.2、删除指定数据

语法格式

DELETE FROM 表名 WHERE 条件 = 值

四、DCL语句

Data Control Language 数据控制语言

五、DQL语句

详见

DQL语句

六、窗口函数

1、基本概念

窗口函数也叫分析函数，即处理相对复杂的报表统计分析场景
窗口可以理解为记录集合，窗口函数也就是满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口中执行函数，窗口大小都是固定的，这种属于静态窗口；不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。
语法格式

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

OVER 是关键字，用来指定函数执行的窗口范围，包含的三个分析子句。三个子句均是需要则写，不需要可以省略。如果全部省略，则意味着窗口包含满足where 条件的所有行，窗口函数基于所有行进行计算
样例

sum(A) over(partition by B order by C rows between D1 and D2)

avg(A) over(partition by B order by C rows between D1 and D2)

···

A：需要被加工的字段名称
B：分组的字段名称
C：排序的字段名称
D：计算的行数范围

# 取当前行和前面两行
rows between 2 preceding and current row 
# 包括本行和之前所有的行
rows between unbounded preceding and current row
# 包括本行和之后所有的行
rows between current row and unbounded following
# 包括本行和前面三行
rows between 3 preceding and current row
# 从前三行和下一行，总共五行
rows between 3 preceding and 1 following

# 当 order by 后面缺少窗口从句条件，窗口规范默认是
rows between unbounded preceding and current row
# 当 order by 和窗口从句都缺失，窗口规范默认是
rows between unbounded preceding and unbounded following

2、聚合类窗口函数

聚合类窗口函数即使用聚合函数+over子句
窗口函数与聚合函数区别
- 聚合函数是将多条记录聚合为一条，即多合一；窗口函数是每条记录都会执行，有几条记录执行后，结果还是几条，即多到多。
- 分组：聚合函数记录按照字段进行分组，窗口函数在不同的分组上分别执行
- 排序：聚合函数按照某字段进行排序，窗口函数将按照排序后的记录顺序进行编号。如果单独使用，数据范围则是整个表的数据行
- 窗口：进行函数分析时要处理的数据范围，属于当前分区的一个子集，通过用来作为滑动窗口使用。

2.1、累计求和

例子
- 订单信息表 user_trade
  - 支付金额 pay_amount
  - 支付时间 pay_time

-- 查询2018-2019年每月的支付总额和当年累积支付总额
SELECT 
	a.year,
	a.month,
	a.pay_amount,
	sum(a.pay_amount) over(partition by a.year order by a.month)
FROM
(
SELECT 
	year(pay_time) year,
	month(pay_time) month,
	sum(pay_amount) pay_amount
FROM user_trade
WHERE year(pay_time) in ('2018','2019')
GROUP BY year(pay_time),month(pay_time)
)a;

2.2、移动平均

例子

-- 查询2019年每个月的近三月移动平均支付金额
SELECT
	a.month,
	a.pay_amount,
	avg(a.pay_amount) over(order by a.month rows between 2 preceding and current row)
FROM
(
SELECT 
	month(pay_time) month,
	sum(pay_amount) pay_amount
FROM user_trade
WHERE year(pay_time) = '2019'
GROUP BY month(pay_time)
)a;

2.3、最值

例子

-- 查询出每四个月的最大月总支付金额
SELECT 
	a.month,
	a.pay_amount,
	max(a.pay_amount) over(order by a.month rows between 3 preceding and current row)
FROM
(
SELECT
	substr(pay_time,1,7) month,
	sum(pay_amount) pay_amount
FROM user_trade
GROUP BY substr(pay_time,1,7)
)a;

3、专有窗口函数

3.1、排序函数

row_number() over(partition by A order by B)
- 查询出来的每一行记录生成一个序号，依次排序且不会重复(1,2,3,4,5····)
rank() over(partition by A order by B)
- over子句中排序字段值相同的序号是一样的，后面的字段不同的序号跳过相同的排名号排下一个(1,1,1,4,4,6····)
dense_rank() over(partition by A order by B)
- over子句后排序的字段值相同的序号是一样的，后面的字段不同的序号按顺序排下一个值(1,1,1,1,2,2,2,3,3······)
例子

-- 根据购买商品品类数用户排名
SELECT 
	user_name,
	count(distinct goods_category) category_count,
	row_number() over(order by count(distinct goods_category)) order1,
	rank() over(order by count(distinct goods_category)) order2,
	dense_rank() over(order by count(distinct goods_category)) order3
FROM user_trade
WHERE substring(pay_time,1,7) = '2020-01'
GROUP BY user_name;

结果样式

图片(1).png

3.2、分组排序

语法格式

ntile(n) over(partition by A order by B)

**n：**切分的片数
**A：**分组的字段名称
**B：**排序的字段名称
不支持ROWS BETWEEN
例子

-- 查询出2022年支付金额排名前30%的所有用户
-- 前30%，即分10组，取前三组
SELECT
	a.user_name,
	a.pay_amount,
	a.level
FROM
(
SELECT
	user_name,
	sum(pay_amount) pay_amount,
	ntile(10) over(order by sum(pay_amount) desc) level
FROM user_trade
WHERE year(pay_time) = '2022'
GROUP BY user_name
) a
where a.level in(1,2,3)

4、偏移分析函数

语法格式

-- 向前偏移
lag(exp_str,offset,defval) over(partion by A order by B)
-- 向后偏移
lead(exp_str,offset,defval) over(partion by A order by B)

exp_str：字段名称
offset：偏移量，默认值为1
defval：默认值，如果不指定默认值，则返回NULL
示例

-- 查询支付时间间隔超过100天的用户数
SELECT 
	count(distinct user_name)
FROM
(
SELECT 
	user_name,
	pay_time,
	lead(pay_time) over(partition by user_name order by pay_time) lead_dt
FROM user_trade
) a
WHERE a.lead_dt is not null
  AND datediff(a.lead_dt,a.pay_time) > 100;

七、索引

提高查询效率的一种手段，会影响条件查询和排序
索引是针对字段的，需要添加到字段上
索引在大量数据场景下效果明显
优点
- 大大提高查询速度
- 显著减少查询和排序的时间
缺点
- 表中数据进行增删改时，索引要同时进行维护，数据量越大，维护时间越长

1、分类

1.1、按存储结构分类

BTree索引
Hash索引
fulltext全文索引
RTree索引

1.2、按应用层次分类

主键索引
唯一索引
普通索引
复合索引

1.3、按键值(字段)类型分类

主键索引
辅助索引(二级索引)

1.4、按数值逻辑和内容逻辑分类

聚集索引(聚簇索引)
非聚集索引(非聚簇索引)

2、几种主要索引

2.1、主键索引(PRIMARY KEY)

一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL
添加主键的时候，会自动创建主键索引，主键索引是一种唯一性索引
根据主键进行where条件查询，效率高
语法格式

-- 新建表时直接添加主键索引
CREATE TABLE 表名(
	字段 字段类型 primary key
)

-- 已存在表添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(列名)

2.2、唯一索引(UNIQUE)

索引列的所有值都只能出现一次，必须唯一
唯一索引可以保证数据记录的唯一性
该索引只是为了避免数据重复，不是为了提高访问效率
语法格式

-- 新建表时直接添加唯一索引
CREATE TABLE 表名(
	字段 字段类型(长度)
	UNIQUE[索引名称](列名)
)

-- 已存在表添加唯一索引
CREATE UNIQUE INDEX 索引名 on 表名(列名)
ALTER TABLE 表名 ADD UNIQUE 索引名(列名)

2.3、普通索引(NORMAL INDEX)

普通索引唯一任务就是加快对数据的访问速度
选择作为普通索引的字段，要是经常出现在查询条件中和排序条件中的字段
普通索引对数据没有唯一、非空的强制要求
语法格式

CREATE INDEX 索引名 ON 表名(列名)
ALTER TABLE 表名 ADD INDEX 索引名(列名)

2.4、删除索引

由于索引会占用一定的磁盘空间，因此，为了避免影响数据库的性能，应该及时删除不再使用的索引
语法格式

ALTER TABLE 表名 DROP INDEX 索引名

八、视图

1、概念

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

2、作用

控制权限，可以指定展示的字段，不想展示的字段则可以隐藏
简化复杂的多表查询
- 视图本身就是一个查询SQL，可以把一次复杂的查询构建成一张视图，用户只要查询视图就可以获取想要的信息，而不是每次都编写复杂的sql
- 简化多表查询，把相同的部分或者几个表关联的构成视图，然后在进行使用

3、使用

语法格式

CREATE [OR REPLACE] VIEW 视图名 [字段列表] AS SELECT 语句;

view：视图
字段列表：可选参数，表示属性清单，指定视图中个属性的名称，默认情况下，与SELECT 语句中查询的属性相同
as：表示视图要执行的操作
select 语句：需要构建视图的sql

4、视图与表的区别

视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
通过视图不能改变表中的数据
删除视图，表不受影响；删除表，视图不在起作用

墨斗先生爱编程

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录