SQL

收集

mysql查询结果添加序列号

引用自
https://blog.csdn.net/yiluoak_47/article/details/15338553
感谢

set @r=0;
select @r:=@r+1 as i, sc.* from sc;	

初级

SELECT 选择

SELECT column1, column2, ...
FROM table_name;

SELECT * FROM table_name;

SELECT DISTINCT 选择某列中不重复的值

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT COUNT 计数

SELECT COUNT(column) FROM table_name;

# 与DISTINCT搭配使用，选择不重复的数据计数
SELECT COUNT(DISTINCT column) FROM table_name;
# 注意，不可以使用 SELECT DISTINCT COUNT(column)，这样是无效的

WHERE 条件语法

WHERE不能以聚合函数为条件：

# 这种写法会报错：
SELECT * FROM table_name
WHERE column=MAX(column);

# 正确写法如下：
SELECT * FROM table_name
WHERE column=
(SELECT MAX(column) FROM table_name);

SELECT column1, column2, ...
FROM table_name
WHERE condition;

WHERE中的运算符

WHERE子句中可以使用以下运算符：

AND, OR, NOT

AND

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

案例

SELECT * FROM students 
WHERE NOT age = 27 
AND (name = 'Ja' OR name = 'Bob');

ORDER BY 按关键字排序

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
# ASC为升序，DESC为降序，默认按升序排序

案列

按多列排序，从左到右的先后顺序

SELECT * FROM students ORDER BY age DESC, id DESC;

INSERT INTO 按照行 插入数据

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

# 如果要为所有列都增加values，则不需要输入列名，但注意列和值要一一对应：
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

NULL 缺失值

注意：理解NULL值与零值或包含空格的字段不同是非常重要的。具有NULL值的字段是在记录创建期间留空的字段！
必须使用 IS NULL 或者 IS NOT NULL 来检测NULL值：

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

UPDATE 更新现有数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

注意SQL UPDATE 语句中的 WHERE 子句！
WHERE子句指定哪些记录需要更新。如果省略WHERE子句，所有记录都将更新！

在使用UPDATE和DELETE的时候遇到一个报错：

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

# 执行以下语句，解除安全模式：
SET SQL_SAFE_UPDATES = 0
# 如若想要恢复，执行
SET SQL_SAFE_UPDATES = 1

DELETE 删除现有数据

DELETE FROM table_name
WHERE condition;

注意SQL DELETE 语句中的 WHERE 子句！
WHERE子句指定需要删除哪些记录。如果省略了WHERE子句，表中所有记录都将被删除！

高级

SELECT TOP 限制展示的数据量

在MySQL中，没有这个语句，对应的语句为 LIMIT：

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

# LIMIT 2 ，表示只展示结果的前2行数据

LIKE

在WHERE子句中使用LIKE运算符来搜索列中的指定模式。
有两个通配符与LIKE运算符一起使用：

# Wildcards运算符：
％ - 百分号表示零个，一个或多个字符
_  - 下划线表示单个字符

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

# 当然，也可以使用 NOT LIKE语句

IN

IN运算符允许您在WHERE子句中指定多个值。
IN运算符是多个OR条件的简写。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

# 也可以使用 NOT IN

BETWEEN

BETWEEN运算符选择给定范围内的值。值可以是数字，文本或日期。
BETWEEN运算符是包含性的：包括开始和结束值。
它是一个双边闭括号的形式 []

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

# 当日，NOT BETWEEN也是可以的

请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！
在一些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
在一些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
在一些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。
因此，请检查您的数据库是如何处理 BETWEEN 操作符！

SQL 通用数据类型

注释：在不同的数据库中，相同的数据类型可能有不同的名称。即使名字相同，大小和其他细节也可能不同！请随时检查文件!

JOIN 连接两张表

类似vlookup()，JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段
四种类型：

INNER JOIN：#（交集） 如果表中有至少一个匹配，则返回行
LEFT JOIN：# 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：# 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：# 只要其中一个表中存在匹配，则返回行

# 以INNER JOIN 为例：
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

实现多个表格的方法:

SELECT * 
FROM ((table_name1
INNER JOIN table_name2 ON table_name1.column=table_name2.column)
INNER JOIN table_name3 ON table_name1.column=table_name3.column)

在MySQL中，不支持 FULL JOIN 或者说 FULL OUTER JOIN，实现方式为使用
LEFT JOIN + UNION + RIGHT JOIN：

SELECT * FROM students LEFT JOIN teachers ON students.id=teachers.id
union
SELECT * FROM students RIGHT JOIN teachers ON students.id=teachers.id;

自连接

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

案例：

SELECT * FROM spiders.students

SELECT A.name AS A_name, A.id AS A_id, A.age AS A_age, B.age
FROM spiders.students A, spiders.students B
WHERE A.id / 80 < B.age

解释：在同一张表中，分成A 与 B两个阵营，在这里，把A里面的id / 80 的值与B里面的age比较大小，即把B.age里的所有值独立出来循环对比一遍：
所有id分别除以80，一一对比23，再一一对比27，再一一对比29，这样就得到结果

UNION 组合两个或更多SELECT语句的结果集

UNION中的每个SELECT语句必须具有相同的列数
这些列也必须具有相似的数据类型
每个SELECT语句中的列也必须以相同的顺序排列

简单来说就是上下叠加选中的各个列

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

# 默认情况下，UNION 运算符选择一个不同的值。如果允许重复值，请使用 UNION ALL
# UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名

另外，还有个子句（亦即运算法）与 UNION 子句非常相像：
SQL INTERSECT 子句：用于组合两个 SELECT 语句，但是只返回两个 SELECT 语句的结果中都有的行。

MySQL不支持INTERSECT，实现方法参见：
https://blog.csdn.net/mine_song/article/details/70184072
感谢

SELECT INTO 从一个表复制数据，把数据插入另一个新表中

SELECT column_name(s)

INTO newtable [IN externaldb]

FROM table1;

MySQL中不支持SELECT INTO语句，可用如下语句代替;

# 备份到另一个table
CREATE TABLE new_table
(SELECT * FROM old_table)

# 复制怎么办呢？我感觉没有意义，插入新的列一般都是有关联的，不可能直接随意插入一列，用JOIN就好了吧

INSERT INTO SELECT 按行插入复制的数据

INSERT INTO table2 (column_name(s))
SELECT column_name(s)
FROM table1;

DROP 通过使用 DROP 语句，可以轻松地删除索引、表和数据库

DROP INDEX 删除索引

MySQL中：
ALTER TABLE table_name DROP INDEX index_name

TRUNCATE TABLE 只删除数据，不删除TABLE

TRUNCATE TABLE table_name

DROP TABLE 删除TABLE

DROP TABLE table_name

DROP DATABASE 删除DATABASE

DROP DATABASE database_name

CREATE TABLE

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

ALTER TABLE 在现有表中添加、删除或修改列。

# 添加列：
ALTER TABLE table_name
ADD column_name datatype

# 删除列：
ALTER TABLE table_name
DROP COLUMN column_name

# 更改现有列的格式类型：
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

AUTO_INCREMENT 自动增值

Auto-increment 会在新记录插入表中时生成一个唯一的数字

CREATE TABLE spiders.auto_test
(
ID int NOT NULL AUTO_INCREMENT,
NAME varchar(255),
Age int,
PRIMARY KEY(ID) # 注意，KEY和括号之间不要留空格，否则会报错
)

# 插入一行数据测试
INSERT INTO spiders.auto_test (NAME, Age)
VALUES ('qwe', 111)

结果ID自动更新一个数字

如果要改变ID的起始数字（默认为1）：

ALTER TABLE table_name AUTO_INCREMENT=100

更新后，原有的数据保持不变，但是新插入的数据以100开始：

CREATE VIEW 创建视图

视图可以理解为按要求从数据库中拉取出来的表格，并且会随数据库的变化而更新

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE OR REPLACE VIEW 更新视图

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

DROP VIEW 删除视图

DROP VIEW view_name

DATE 函数

MySQL 使用下列数据类型在数据库中存储日期或时间值：
DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
YEAR - 格式：YYYY 或 YY

IS NULL & IS NOT NULL 查找缺失值

# 查找某一列是NULL值的行数据
SELECT * FROM table_name
WHERE columns IS NULL

# 查找某一列不是NULL值得行数据
SELECT * FROM table_name
WHERE columns IS  NOT NULL

进阶

Aliases 别名

SQL 别名用于为表或表中的列提供临时名称。
SQL 别名通常用于使列名更具可读性。
SQL 一个别名只存在于查询期间。

# 列的 SQL Alias 语法
SELECT column_name AS alias_name # 这里把AS替换成空格是一样的
FROM table_name;
=
SELECT column_name alias_name
FROM table_name;

# 表的 SQL Alias 语法
SELECT column_name(s)
FROM table_name AS alias_name; # 这里把AS替换成空格是一样的
=
SELECT column_name(s)
FROM table_name  alias_name;

SQL约束

约束是作用于数据表中列上的规则，用于限制表中数据的类型。约束的存在保证了数据库中数据的精确性和可靠性。
约束有列级和表级之分，列级约束作用于单一的列，而表级约束作用于整张数据表。

NOT NULL 约束

保证列中数据不能有 NULL 值

DEFAULT 约束

提供该列数据未指定时所采用的默认值

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255) DEFAULT 'Sandnes'
)

# 当表已被创建时，如需在 "City" 列创建 DEFAULT 约束，请使用下面的 SQL：
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

# 撤销Default
ALTER TABLE Persons
ALTER City DROP DEFAULT

撤销DEFAULT之后，被撤销的那个字段是NOT NULL的，需要重新设置格式

# 创建一个新TABLE
CREATE TABLE sqltest.checkk (
id int not null auto_increment,
name varchar(255),
adress varchar(255),
age int,
PRIMARY KEY(id),
);
INSERT INTO sqltest.checkk (age)
VALUES (101);
SELECT * FROM sqltest.checkk;

结果是可以允许NULL的

# 如果这时加入一个DFAULT 然后再删除，此字段就变成NOT NULL了：
ALTER TABLE sqltest.checkk
ALTER adress SET DEFAULT 'lcimlicm';
ALTER TABLE sqltest.checkk
ALTER adress DROP DEFAULT;
INSERT INTO sqltest.checkk (age)
VALUES (10);
SELECT * FROM sqltest.checkk1;

# 报错：Error Code: 1364. Field 'adress' doesn't have a default value
# 此时只能ALTER TABLE + MODIFY COLUMN重新设置格式了：
ALTER TABLE sqltest.checkk
MODIFY COLUMN adress varchar(255);
INSERT INTO sqltest.checkk (age)
VALUES (10);
SELECT * FROM sqltest.checkk;
# 成功

UNIQUE 约束

保证列中的所有数据各不相同, 请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT unique_name UNIQUE (P_Id)

# 如果用UNIQUE(test1, test2, ...) 这样，效果是保证这些列不可以同时重复，比如test1有'AAA'这个数据，再次插入'AAA'也可以，只要其他列没有同时出现过。
)

# 撤销约束
ALTER TABLE table_name
DROP INDEX unique_name

主键约束 PRIMARY KEY：

唯一标识数据表中的行/记录,PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束

REATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

注释：在上面的实例中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的。

# 撤销主键设置
ALTER TABLE table_name
DROP PRIMARY KEY

外键约束 FOREIGN KEY：

唯一标识其他表中的一条行/记录
此表的某个字段的值，必须存在于另一个表的某个字段里，这就是外键约束

CREATE TABLE table1
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES table2(P_Id)
# table1中P_id的值，必须要在table2中P_id字段里存在，要不然就不能插入数据
)

如果添加外键遇到报错：Error Code: 1215. Cannot add foreign key constraint，先检查两个字段的数据类型是不是完全一致，不一致则用ALTER TABLE + MODIFY COLUMN更改即可

CHECK 约束 MySQL不可用：

有两种解决发放，参见：

https://blog.csdn.net/qq_39521554/article/details/78681681
感谢

索引：

用于在数据库中快速创建或检索数据
索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度
索引的创建与删除不会对表中的数据产生影响。

# 基本语法：
CREATE INDEX index_name 
ON table_name

# 单列索引：
CREATE INDEX index_name
ON table_name (column_name);

# 唯一索引：不止用于提升查询性能，还用于保证数据完整性。唯一索引不允许向表中插入任何重复值
CREATE UNIQUE INDEX index_name
ON table_name (column_name);

# 聚簇索引：在表中两个或更多的列的基础上建立。其基本语法如下所示
CREATE INDEX index_name
on table_name (column1, column2);

# 删除索引：删除索引时应当特别小心，数据库的性能可能会因此而降低或者提高
DROP INDEX table_name.index_name;

创建单列索引还是聚簇索引，要看每次查询中，哪些列在作为过滤条件的 WHERE 子句中最常出现。
如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列，那么聚簇索引就是最好的选择。

什么时候应当避免使用索引？

尽管创建索引的目的是提升数据库的性能，但是还是有一些情况应当避免使用索引。下面几条指导原则给出了何时应当重新考虑是否使用索引：

-小的数据表不应当使用索引；
-需要频繁进行大批量的更新或者插入操作的表；
-如果列中包含大数或者 NULL 值，不宜创建索引；
-频繁操作的列不宜创建索引

看看这篇关于INDEX的文章:
http://www.yuanrengu.com/index.php/2017-01-13.html
感谢

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

完全克隆一张表

# 第一步：
SHOW CREATE TABLE old_table

邮件CREATE TABLE字段里的内容，选择Copy Row(unquoted)

defaultt, CREATE TABLE `defaultt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT 'Lim',
  `adress` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# 删掉开头的表名，然后把CREATE TABLE后面的表名改为新表名，并且去掉自带的引号（其他引号不用管），更改为:
CREATE TABLE 'new_table' (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT 'Lim',
  `adress` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

这样就完成了格式复制，如果要插入数据：

INSERT INTO new_table (SELECT * FROM old_table)
# Done

HAVING

本质的区别就是where筛选的是数据库表里面本来就有的字段，而having筛选的字段是从前筛选的字段筛选的

关于WHERE和HAVING的区别：
where>聚合函数(sum,min,max,avg,count)>having

注意事项 ：
1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

where和having都可以使用的场景：

select goods_price,goods_name from sw_goods where goods_price>100

原因：goods_price作为条件也出现在了查询字段中。

只可以使用where，不可以使用having的情况：

select goods_name,goods_number from sw_goods where goods_price>100

原因：goods_price作为筛选条件没有出现在查询字段中，所以就会报错。
having的原理是先select 然后从select出来的进行筛选。而where是先筛选在select。

只可以使用having，不可以使用where的情况：

select goods_category_id,avg(good_price) as ag from sw_goods group by goods_category having ag>1000

https://blog.csdn.net/jdjh1024/article/details/76647866
https://www.cnblogs.com/ljf-Sky/p/9024683.html
感谢

TEMPORARY

创建临时表，临时表有时候对于保存临时数据非常有用。有关临时表你需要知道的最重要的一点是，它们会在当前的终端会话结束后被删除。

# 创建
CREATE TEMPORARY TABLE table_name;

# 删除
DROP TABLE table_name;

函数

UPPER() , LOWER() 全部大写 ，全部小写

# 将name字段，全部大写输出
SELECT UPPER(name) FROM spiders.teachers

# 将name字段，全部小写输出
SELECT LOWER(name) FROM spiders.teachers

ROUND(column, n)

column为需要操作的字段
n为需要保留的小数位数（四舍五入）

# 可以使用 ORDER BY RAND() 来对一组记录进行随机化排列，如下所示：
SELECT * FROM table_name ORDER BY RAND()

NOW() 返回当前系统日期+时间

RAND() 产生 0 至 1 之间的随机数

CONCAT() 连接各个字段

SELECT CONCAT(name, '-', id, '-', age) name_age_id FROM spiders.students

IFNULL(column, n) 对缺失值的处理

column表示操作的字段
n表示如果此字段出现NULL值，则返回n值

REPLACE(column, origin-str, new-str)

column 需要操作的字段
origin-str 需要被替换的字符串
new-str 用于替换的字符串

UPDATE spiders.students SET name=replace(name, 'Limckos', 'JKLJKLJKL');

TRIM( ), RTRIM( ), LTRIM( ) 移除空格

------------------------

Tips

所有字符需要用单引号或者双引号标记出来（尽量使用单引号）

多个SQL语句时需要以 ; 符号结尾

使用 USE database_name，切换DATABASE

关于几个字符串的区别

1、CHAR。
CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充，所以在读取的时候可能要多次用到trim()。

2、VARCHAR。
存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

3、TEXT。
text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。

4、NCHAR、NVARCHAR、NTEXT。
这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

所以一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar
在Design的时候应该尽量使用nvarchar来存储数据．只有在你确保该字段不会保存中文的时候，才采用varchar来存储的时候也不会有问题

它们的区别概括成：
CHAR，NCHAR 定长，速度快，占空间大，需处理
VARCHAR，NVARCHAR，TEXT 不定长，空间小，速度慢，无需处理
NCHAR、NVARCHAR、NTEXT 处理Unicode码

问题

WHERE Discontinued=0? (查出终止状态为否的记录)?
UNIQUE 唯一约束，如何约束多列分别不能重复？

引用自： https://www.w3cschool.cn/sql
感谢