SQL知识梳理

一、增删改

1.创建数据库

CREATE DATABASE dbname;

2.删除数据库

DROP DATABASE dbname;

3.对已存在数据库创建备份

BACKUP DATABASE dbname
TO DISK = 'filepath';

4.备份数据库中自上一次完整的数据库备份后发生变化的部分

BACKUP DATABASE dbname
TO DISK = 'filepath'
WITH DIFFERENTIAL;

5.数据库中创建新表

DROP TABLE IF EXITES tbname;
CREATE TABLE tbname (
    column type,
    column type,
    ...
);

6.用旧表数据创建新表

CREATE TABLE new_table_name AS 
    SELECT column1, column2,...
    FROM  existing_table_name
    WHERE ...;

7.删除表

DROP TABLE tbname;

8.删除表中数据，不删除表结构

TRUNCATE TABLE tbname;

9.mysql中修改表信息的规则
修改表的列名：

ALTER TABLE tbname change old_column_name new_column_name type;

修改表的列的类型：

ALTER TABLE tbname modify column_name type;

删除表某一列：

ALTER TABLE tbname DROP column_name;

在x列后添加一列：

ALTER TABLE tbname ADD column_name type AFTER column_x;

修改表名：

ALTER TABLE tbname RENAME new_tbname;

二、约束Constraints
约束可以是列层次，也可以是表层次。约束用于限制可以放入表中的数据的类型。这确保了表中数据的准确性和可靠性。如果约束与数据操作之间有任何冲突，则该操作将被中止。

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

1.NOT NULL保证列没有空值

CREATE TABLE persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    Age int
);

当表已经存在时，给Age列添加NOT NULL约束

ALTER TABLE persons MODIFY Age int NOT NULL;
2.UNIQUE - 保证一列的值是去重的
CREATE TABLE persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    Age int,
    UNIQUE (ID)
);

当表已经存在时，给ID列添加UNIQUE约束

ALTER TABLE persons ADD UNIQUE (ID);

在多列上创建UNIQUE约束，为其创建了索引index:

ALTER TABLE persons ADD CONSTRAINT UC_persons UNIQUE (ID,LastName);

删掉UNIQUE约束=删掉其索引Index:

ALTER TABLE person DROP INDEX UC_persons;

3.PRIMARY KEY
主键是NOT NULL和UNIQUE的组合，唯一地识别表中的每一条记录,
① 创建表时：

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    Age int,
    PRIMARY KEY (ID)
);

以多字段作为主键：
In the example there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

② 表已存在时:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

以多列为主键:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
```sql
Note: 如果你使用ALTER TABLE来添加一个主键，主键列必须被声明为不包含NULL值（当表第一次被创建时）。
③ 删除主键
```sql
ALTER TABLE Persons
DROP PRIMARY KEY;

4.FOREIGN KEY
FOREIGN KEY是一个表中的一个字段(或字段的集合)，它引用另一个表中的PRIMARY KEY。
具有外键的表称为子表，具有主键的表称为引用表或父表。
FOREIGN KEY约束可以防止无效数据被插入到外键列中，因为它必须是父表中包含的一个值

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

注：Person是父表，Orders是子表
重命名外键:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

当表存在时，创建外键:

ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

5.CHECK – 确保一列中的数值满足一个特定的条件

CHECK(condition)

① 建表时:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

给多列加CHECK：

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

② 给已存在表加CHECK:

ALTER TABLE Persons
ADD CHECK (Age>=18);

给多列加CHECK：

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

③ 删除CHECK:

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

6.DEFAULT – 设置默认值
① 建表时设置默认值

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

② 给已存在表设置默认值

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

③ 删除默认值

ALTER TABLE Persons
ALTER City DROP DEFAULT;

7.CREATE INDEX – 用于非常快速地创建和检索数据库中的数据
索引用于比其他方法更快地从数据库中检索数据。用户不能看到索引，它们只是用来加速搜索/查询。更新有索引的表要比更新没有索引的表花费更多的时间(因为索引也需要更新)。因此，只在频繁搜索的列上创建索引。
括号里放多个列的话，是create an index on a combination of columns；
① 创建索引：允许重复值

CREATE INDEX index_name
ON table_name (column1, column2, ...);

② 创建去重索引：不允许重复值

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

③ 删除索引

ALTER TABLE table_name
DROP INDEX index_name;

二、查

一、查询数据SELECT
语法：select column_name from table_name。使用 select 选取列数据，返回的结果是无序的。
1.单列查询

SELECT column FORM table； 

2.多列查询

SELECT column1, column2 FORM table；

3.查所有列

SELECT * FROM table；

4.去重查询：多用于查看种类-DISTINCT关键词

SELECT DISTINCT column1, column2 FORM table；

5.限制返回行数-LIMIT关键词

SELECT * FROM student limit 2； 

6.排序
（1）单列排序

SELECT column FORM table ORDER BY column1；

（2）多列排序

SELECT column FORM table ORDER BY column1, column2；

有先后顺序，先排 列1，再将列1中数值相同、并列在一起的数值按 列2 排序；
（3）升序-ASC关键词
不写关键词默认是升序;

SELECT column FORM table ORDER BY column1 ASC;

（4）降序-DESC关键词

SELECT column FORM table ORDER BY column1 DESC;

二、过滤数据WHERE

SELECT column1, column2, ...
FROM table
WHERE condition;
WHERE condition1 AND condition2 AND condition3 ...;
WHERE condition1 OR condition2 OR condition3 ...;
WHERE NOT condition;

1.单条件过滤

SELECT * FROM table WHERE SName = 'Tom'；

（注：非数值型数据要加引号）
2.多条件过滤

SELECT * FROM table WHERE condition1 AND condition2
SELECT * FROM table WHERE condition1 OR condition2

3.范围值过滤
查找价格在50~100（包括两端）之间的两种写法：

SELECT * FROM table WHERE Price<=100 AND Price>=50;
SELECT * FROM table WHERE Price Between 50 AND 100;

4.筛选部分数据

SELECT column (s)
FROM table 
WHERE column IN (value1, value2, ...);

SELECT column (s)
FROM table 
WHERE column IN (SELECT STATEMENT);

或NOT IN 筛选除（）之外的数据
三、模糊查找-通配符
模糊查找：有一部数据不知道。比如查一个人名字，只知道他姓李。那就查找所有姓李的人。其中，通配符用于代替不确定的那部分数据。

语法：

SELECT * FROM table WHERE name LIKE '' /NOT LIKE '';

注：模糊查找是LIKE,不是等号=

1. %通配符可以代表一个或多个字符
   “abc%” 以abc开头，任意字符结尾的数据
   ‘%abc’ 以abc结尾，任意字符开头的数据
   ‘%abc%’ 任意字符开头和结尾，中间包含abc

查找姓李的人，名字不一定是几个字：

SELECT * FROM table WHERE name LIKE '李%';

‘%雷‘是查找到雷就结束了，如果名字是xx雷空格空格，就无法查到，解决办法是改为’%雷%‘,结尾多加一个%来获取可能存在的空格。

2. _通配符只能代表一个字符
   找韩梅梅
   ‘韩_ _’√
   ‘韩 _’ ×
   ‘韩%’√

注：通配符虽然方便，但运行速度比过滤慢，非必要尽量不用。

三、数据类型及数据处理函数

一、数据类型
1、数值型
1.INTEGER
定义：整数，只能储存整数
用法：INTEGER, INTEGER(10),其中10为数字最大宽度
2.DECIMAL
定义：定点型，储存固定位数的小数
用法：DECIMAL(M,D)
M-整个数字最长位数，默认10
D-小数部分位数，默认0
例，DECIMAL(10,2)的含义是：小数部分2位，整数部分 8位（10- 2），如果多出位数会四舍五入，短了小数点用0补齐；
注意：DECIMAL适用场景是精确计算，比如金额等；如果用float会丢失精度，原因在于二进制要想表示十进制的数就会存在误差，比如用二进制来表示0.1,根本无法精确表示,但是这不是bug,是个feature，标准就是这样定的。（详细原因参考https://blog.csdn.net/u011305680/article/details/80264508）

3.FLOAT/DOUBLE(REAL)
定义：浮点型，储存不固定位数的小数
用法：FLOAT/DOUBLE(REAL)

2、文本型（区分大小写）

1.CHAR
定义：定长字符串
用法：CHAR(10)

2.VARCHAR
定义：可变长字符串
用法：VARCHAR(100)

注意：此处的长度单位为字符个数，而不是字节的长度；
两者的区别：CHAR型不足位的时候要补空位；VARCHAR是动态分配，不存在不足位。

3、日期型
date/time/datetime/timestamp
date 日期“2023-08-18”
time 时间“12:56:45”
datetime 日期时间“2023-08-18 12:56:45”
timestamp 自动存储修改时间，自1970年1月1日00:00:00开始

注：如果定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。
二、数据处理（函数）
函数表达式 = 函数名 + （参数）

1、数值型处理函数（参数为数值型）

1.绝对值函数 ABS ABS (-3) = 3
2.平方根函数 SQRT SQRT (4) = 2
3.指数函数 EXP EXP (4) = e^4
4.四舍五入函数 ROUND ROUND (1.234, 2) = 1.23
5.圆周率函数 PI PI () = π

2、文本型处理函数（参数为文本型）

1.字符长度 CHARLENTH CHARLENTH ( ‘数据’ ) = 2
2.字节数 LENTH LENTH ( ‘数据’ ) = 4
3.去除右边空值 RTRIM RTRIM ( ‘HOME ’ ) = ‘HOME’
4.去除左边空值 LTRIM lTRIM ( ’ HOME’ ) = ‘HOME’
5.字母转换为大写 UPPER UPPER ( ‘home ’ ) = ‘HOME’
6.字母转换为小写 LOWER LOWER ( ‘HOME’ ) = ‘home’
7.截取左边n个字符 LEFT(str,n)
8.截取右边n个字符 RIGHT(str,n)
9.从第n个字符截取到第m个字符 SUBSTR(str,n,m) SUBSTR(‘world’,2,4)=‘orl’
注：不写m，默认截取到最后一个字符
10.拼接字符串 CONCAT(str1,str2) CONCAT (‘hello’,‘world’) =hellloworld

11. 按指定间隔符号拼接字符串 CONCAT_WS(separator,str1,str2,…) CONCAT_WS(’,’,‘hello’,‘world’) =hello,world
    12.分组拼接字符GROUP_CONCAT(DISTINCT col ORDER BY col ASC/DESC SEPARATOR ‘指定间隔符号’)

3、日期型处理函数(参数为日期型)

1.获取具体的日期段 YEAR/MONTH/DATE YEAR( ‘2022-1-1’ ) = ‘2022’
2.获取具体的时间段 HOUR/MINUTE/SECOND HOUR( ‘18:01:16’ ) = ‘18’
3.获取月份名称 MONTHNAME MONTHNAME( ‘2022-1-1’ ) = ‘JAN’
4.获取当前日期时间 NOW NOW() = SYSTERMTIME
6.获取当前日期 CURDATE CURDATE()
7.获取当前时间 CURTIME CURTIME()
8.时间增加 DATE_ADD DATE_ADD( now(),INTERVAL 1 YEAR)
或date_format(now(),‘%Y-%m-%d’/‘%Y-%m-%d-%H-%i-%s’) + interval 1 year/1month/1day/1 hour/1 minute/1second
9.时间减少 DATE_SUB DATE_SUB( now(),INTERVAL 1 YEAR)
或date_format(now(),‘%Y-%m-%d’) – interval 1 day

四、常用函数（聚合、条件、窗口）

一、聚合函数
1、 用处
聚合函数常和分组计算group by语句结合使用，对数据分组后执行计算并为每组返回唯一值。

2、 常用的聚合函数
COUNT()，SUM()，AVG()，MIN()，MAX()。
COUNT()：计数，返回每组的行数,也会返回有NULL值的列，可用于数字和字符列。
SUM()：返回每组数值的总和，忽略NULL值，仅用于数字列。
AVG()：返回每组数值的平均值，忽略NULL值，仅用于数字列。
MIN()：返回每组数据的最小值，忽略NULL值，可用于数字、字符和日期时间列。
MAX()：返回每组数据的最大值，忽略NULL值，可用于数字、字符和日期时间列。

3、注意
聚合函数不能用在where语句中，需要用在having语句中进行过滤。
原因是SQL语句执行顺序为from 、on 、join 、where 、group by(开始使用select中的别名，后面的语句中都可以使用)、 聚合函数、having 、select 、distinct 、order by、limit，
聚合函数是在分组之后进行计算，而分组是where语句过滤完数据后才进行分组，因此如果在where语句中使用聚合函数，还未分组无法进行统计计算，程序会报错。

二、 条件判断-case when
1、用处
利用现有字段，根据条件语句，生成新字段。

2、语法

①
case id when 0 then '学生'
        when 1 then '学生'
        when 2 then '老师'
        else '其他'
        end as identification

②
Case when id in (0,1) then '学生'
When id = 2 then '老师'
Else '其他'
end as identification

注意：end关键词不可省

3、使用场景
① case when可用于select后 生成新字段:

select id, quantity
case when quantity >30 then 1
     when quantity = 30 then 2
     else 3
end as QuantityText
from orders;

② case when可以写在分组group by后按新字段分组，注意case when用在group by后不可以使用字段别名，即到end关键字结束，无as new_colname。
③ case when可以用在聚合函数中。比如统计每个学生考试通过的学科数，可写为：

select
id，
name,
count(distinct case when score>= 60 then subject end) as total_pass_subject
from scores
    where date>=’2022-01-01’
    group by id,name;

④ case when可以写在order by后

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

说明：将用户按城市排序，如果城市为空按国家排序。

三.条件判断-if函数
1、用处
根据条件判断，更改原始列数据或者新增列。if比case when执行用时更短一点。

2、语法
If（expression，v1,v2） 表达式为真，返回v1, 否则返回v2
例：
① id不是偶数且姓名不以M开头的职员bonus等于其薪水，否则为0：

select employee_id,
if(employee_id % 2 != 0 and left(name,1) != 'M',salary,0) as bonus
from Employees
order by employee_id；

② 将性别列的所有m和f对调：

set sex = if(sex='m','f','m');

四、窗口函数
1、用处
窗口函数与聚合函数类似，它也会对数据分组之后进行计算，但它不是为每组返回一个值，而是会为分组中的每条记录返回特定值。
窗口函数只能用在select 语句中。它不会用在group by,也不会用在聚合函数中。
窗口函数可以减少表连接。

2、语法：
函数名（）over (partition by col1,col2 order by col3 desc/asc, col4 desc/asc) as new_col

例，统计2022年每个用户最近一次登录的记录：

select id,
    name,
    login_time
from (select id,
        name,
        login_time
    row_number() over (partition by id,name order by login_time desc) as rank
    from user_login
    where login_time >= ‘2022-01-01’
）t
where rank = 1;

partition by 表示对所有记录按照用户的id和name进行分组，具有相同id和name的每组用户的记录按登录时间login_time降序排列，最新的记录会排在最前面，并通过窗口函数rou_number()over为同组所有记录返回排序值，最新的记录返回1，次新的记录返回2…最后在外层查询中使用where rank = 1，即得到所有用户最近一次登录的记录。

3、 常见窗口函数
窗口函数

4、MySQL排序窗口函数的区别
ROW_NUMBER()：按照顺序进行排序（1、2、3…）
RANK()：并列排序，会跳过重复的序号（1、1、3…）
DENSE_RANK()：并列排序，不会跳过重复的序号（1、1、2…）

打赏作者