SQL命令
SQL语法
数据库操作
数据表
数据操作

INSERT INTO 插入
SELECT 选取数据
WHERE 指定查询条件
UPDATE 修改数据
DELETE 删除数据
LIKE 模糊匹配
TOP 限制返回数据数量
ORDER BY 排序
GROUP BY 分组
DISTINCT 删除重复记录

SQL约束

NULL
DEFAULT 默认约束
UNIQUE 唯一约束
Primary Key
Foreign Key
CHECK 检测约束
INDEX 索引

JOIN 联合表（合并列）

1、INNER JOIN（默认）
2、LEFT JOIN
3、RIGHT JOIN
4、FULL JOIN
5、SELF JOIN
6、CROSS JOIN

UNION和UNION ALL （合并行）
ALTER TABLE语句
TRUNCATE TABLE 清空表
视图

1、CREATE VIEW 创建
2、修改
3、删除

HAVING子句
SQL事务

事务属性：
事务执行流程：

临时表的使用
克隆表（复制表）
SQL 子查询
AUTO_INCREMENT 自动增长序列
SQL注入

学习自C语言中文网SQL教程笔记，该笔记为速查笔记，学习还是看原教程文章：http://c.biancheng.net/sql/

SQL命令

SQL 是关系型数据库的标准语言，SQL关键字不区分大小写

SQL语句分为以下三种类型：
DML: Data Manipulation Language 数据操纵语言
DDL: Data Definition Language 数据定义语言
DCL: Data Control Language 数据控制语言

1、数据定义语言

CREATE #创建表，视图或对象
ALTER  #修改现有的数据库。比如表，记录
DROP  #删除整个表，视图或数据库中其他对象

2、数据处理语言

SELECT #检索记录
INSERT #插入一条记录
UPDATE #修改记录
DELETE #删除记录

3、数据控制语句

GRANT #向用户分配权限
REVOKE #收回用户权限

SQL语法

1、SELECT 查数据

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

2、UPDATE 更新数据库中数据

UPDATE table_name SET column1=value1, column2=value2,...,columnN=valueN [WHERE CONDITION]

3、DELETE 从数据库中删除数据

DELETE FROM table_name WHERE {CODITION};

4、CREATE TABLE 创建新的数据表

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    ...
    columnN datatype,
    PRIMARY KEY(one or more columns)
);

5、ALTER TABLE 修改数据表，修改数据表字段

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_type};

ALTER TABLE table_nae RENAME TO new_table_name;

6、DROP TABLE 删除数据表

DROP TABLE table_name;

7、CREATE DATABASE 创建新的数据库

CREATE DATABASE database_name;

8、INSERT INTO 向数据表中插入新的数据

INSERT INTO table_name(column1,column2,...,columnN) VALUES(value1,value2,...,valueN);

9、CREATE INDEX 创建索引

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

10、DROP INDEX 删除索引

ALTER TABLE table_name DROP INDEX index_name;

数据库操作

#创建数据库
CREATE DATABASE test;
#查看数据库
SHOW DATABASES;
#删除数据库
DROP DATABASE test;
#选择数据库
USE test;

数据表

# 创建数据表
CREATE TABLE website(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    url VARCHAR(30) DEFAULT '',
    age TINYINT UNSIGNED NOT NULL,
    uv FLOAT DEFAULT '0',
    country CHAR(3) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
);  

# 查看数据表结构
DESC website;

# 删除表
DROP TABLE website;

# 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
# or
RENAME old_table_name TO new_table_name;

注意事项：

单引号：当字段的数据类型是 char,varchar，插入的value必须用单引号，这里的插入的其他数据，包括整型的数据，可以也可以用单引号
反引号：我们给表名起名字，可能会与sql 语法的关键词冲突因此，表名与字段/属性名字统一用反引号引起来
对于数据表的主键例如 id ,因为他是整型integer ，不用引起来

数据操作

INSERT INTO 插入

# 指定列插入数据
INSERT INTO table_name(column1,column2,column3,...,columnN) VALUES(value1,value2,value3,...,valueN);
#or
INSERT INTO table_name VALUES(value1,value2,...,valueN);# 需要全部字段的值

# 一个表填充另一个表(另一个表中必须有一组字段和当前表的字段是匹配的)
INSERT INTO first_table_name[(column1,...,columnN)] SELECT column1,...,columnN FROM second_table_name [WHERE condition]

SELECT 选取数据

SELECT column1,...,columnN FROM table_name WHERE conditions;
#WHERE子句是可选的

SELECT可以结合以下子句一起使用：

WHERE：指明筛选条件
ORDER BY：按某个字段对结果进行排序
GROUP BY：结合聚合函数，根据一个或多个列对结果集进行分组
HAVING：通常和GROUP BY 联合使用，用来过滤由GROUP BY子句返回的结果集

WHERE 指定查询条件

SELECT column1,...,columnN FROM table_name WHERE condition

condition条件中可用：
比较运算符：>, <, = 等
逻辑运算符：AND, OR等
模糊匹配：LIKE, NOT LIKE 等

# 例
SELECT id, name, url, uv FROM website WHERE uv>80 AND name LIKE '%o%'

UPDATE 修改数据

UPDATE table_name SET column1=value1,...,column=valueN WHERE [condition];

DELETE 删除数据

DELETE FROM table_name WHERE [condition]

# 例
DELETE FROM website WHERE id = 6;# 删除指定行
DELETE FROM website; #删除所有记录

LIKE 模糊匹配

SELECT FRMO table_name WHERE column LIKE 'pattern'

微信图片_20230211152925.png

SELECT * FROM website WHERE uv LIKE '59%'; #以59开头的记录

TOP 限制返回数据数量

并不是所有的数据库都支持 TOP 子句，有些数据库使用其它的等价语句来替代，例如：

MySQL 使用 LIMIT 子句获取指定数量的记录；
Oracle 使用 ROWNUM 子句获取指定数量的记录

SELECT TOP number|percent column_name(s) FROM table_name WHERE [condition]

# 例
SELECT TOP 3 * FROM website;

ORDER BY 排序

默认情况下，大部分数据库将查询结果按照升序排序

SELECT column_list FROM table_name [WHERE condition] [ORDER BY column1,column2,...,columnN] [ASC | DESC];
# ASC 关键字表示升序，DESC 关键字表示降序

SELECT * FROM website ORDER BY age, uv;

GROUP BY 分组

SELECT column1,...,columnN FROM table_name WHERE [condition] GROUP BY column1,...,columnN ORDER BY column1,...,columnn
# 顺序不可颠倒

SUM( ) 指定字段的值进行求和
COUNT( ) 计算某个分组内数据的条数；
AVG( )指定字段的值求平均数。

# 例
SELECT country, TRUNCATE(SUM(uv), 2) AS total FROM website GROUP BY country;

DISTINCT 删除重复记录

UNIQUE 和 DISTINCT 的效果是等价的，都用来消除结果集中的重复记录( UNIQUE 是一种老式的语法，Oracle 数据库曾经使用过)

SELECT DISTINCT column1,...,columnN FROM table_name WHERE [condition]

SQL约束

约束（Constraint）是指表的数据列必须强行遵守的规则，这些规则用于限制插入表中的数据类型

约束可以是列级别，也可以是表级别，常用约束有：NOT NULL, DEFAULT, UNIQUE, PRIMARY Key, FOREIGN Key, CHECK, INDEX

1、创建约束：

CREATE TABLE 创建数据表时指定约束
ALTER TABLE 创建数据表后增加约束

2、删除约束

# 删除EMPLOYEES 表中的主键
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

# Oracle 可以使用 DROP PRIMARY KEY 关键字删除主键约束
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

NULL

NOT NULL 关键字表示不允许该字段为空值, 创建表时，默认为运行NULL

不能使用 =、<、> 等比较运算符来检测 NULL 值，而必须使用 IS NULL 或者 IS NOT NULL 关键字来检测 NULL 值

# 筛选时
SELECT  id, name, age, uv,country FROM website WHERE url IS NOT NULL;

# 已创建的表修改约束
ALTER TABLE website MODIFY url VARCHAR(30) NOT NULL

DEFAULT 默认约束

ALTER TABLE website MODIFY uv FLOAT DEFAULT '10.0';

# 删除默认约束
ALTER TABLE website ALTER COLUMN uv DROP DEFAULT;

UNIQUE 唯一约束

PRIMARY KEY = UNIQUE + NOT NULL

一张表可以包含多个 UNIQUE 字段，但是只能有一个主键。

# 将 UNIQUE 约束添加到 alexa 字段
ALTER TABLE website MODIFY alexa INT UNSIGNED NOT NULL UNIQUE;

# 多个字段添加 UNIQUE 约束
ALTER TABLE website ADD CONSTRAINT myUniqueConstraint UMIQUE(alexa, url);

# 删除UNIQUE约束
ALTER TABLE website DROP CONSTRAINT myUniqueConstraint;
# MYSQL 删除UNIQUE约束
ALTER TABLE website DROP INDEX myUniqueConstraint;

Primary Key

当主键包含多个字段时，又称为复合键（Composite Primary Key）

SQL 规定，主键长度不能超过 900 个字节。Oracle 规定，主键不能超过 32 个字段。

# 未建表
CREATE TABLE website(
    id INT NOT NULL AUTO_INCREMENT, # 定义为主键
    name VARCHAR(20) NUT NULL,
    url VARCHAR(30),
    age INT UNSIGNED NOT NULL,
    alexa INT UNSIGNED NOT NULL,
    uv FLOAT DEFAULT '0',
    country CHAR(3) NUT NULL,
    PRIMARY KEY (id);
)

# 已建表
ALTER TABLE website ADD PRIMARY KEY(id);

# 主键包含多个字段
ALTER TABLE website ADD CONSTRAINT PK_CUSTID PRIMARY KEY (id, url);

# 删除主键
ALTER TABLE website DROP PRIMARY KEY;

Foreign Key

# 创建user表
CREATE TABLE user(
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(30) NOT NULL,
    password VARCHAR(30) NOT NULL,
    email VARCHAR(30) NOT NULL,
    cellphone CHAR(11),
    ip CHAR(15) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE `order`(
    oid INT NOT NULL AUTO_INCREMENT,
    uid INT REFERENCES user(id),
    order_no CHAR(11) NOT NULL,
    price DECIMAL(6,1) DEFAULT '0.0',
    creat_time DATETIME NOT NULL, 
    pay_time DATETIME NOT NULL,
    PRIMARY KEY(oid)
);
# order是 SQL 中的关键字，用作表名时需要使用反引号

# 已创建表
ALTER TABLE `order` ADD FOREIGN KEY (uid) REFERENCES user (id);

# 删除外键
ALTER TABLE `order` DROP FOREIGN KEY;

CHECK 检测约束

用来限制字段的取值范围。可以在 CHECK 约束中添加限制条件，只有满足这些条件的值才允许进入该字段。

1、创建表

CREATE TABLE website(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    age TINYINT UNSIGNED NOT NULL CHECK(age<=25);
    ...
)

CREATE TABLE website (
    id      INT              NOT NULL   AUTO_INCREMENT,
    name    VARCHAR(20)      NOT NULL,
    url     VARCHAR(30),
    age     TINYINT UNSIGNED NOT NULL,
    alexa   INT UNSIGNED     NOT NULL,
    uv      FLOAT                       DEFAULT '0',
    country CHAR(3)          NOT NULL ,
    PRIMARY KEY (id),
    CONSTRAINT myCheck CHECK(age<=25 AND alexa<=10000)
);
# myCheck 为自定义的约束名字。

2、已创建表

# 单个字段
ALTER TABLE website MODIFY age TINYINT UNSIGNED NOT NULL CHECK(age<=25);

# 多个字段
ALTER TABLE website ADD CONSTRAINT myCheck CHECK(age<=25 AND alexa<=10000);

3、删除约束

ALTER TABLE website DROP CONSTRAINT myCheck;

INDEX 索引

CREATE INDEX index_name ON table_name(column1,...,columnN)

# 针对 name 字段创建索引，用以提高检索姓名时的效率
CREATE INDEX myIndex ON website(name);

# 删除索引
ALTER TABLE website DROP INDEX myIndex;

JOIN 联合表（合并列）

SQL JOIN 子句用于将两个或者多个表联合起来进行查询

SELECT table1.column1, table2.column2... FROM table1 JOIN table2 ON table1.common_column1 = table2.common_column2

# 只有满足table1.common_column1 = table2.common_column2的记录才会合并为一行

# 例
SELECT table1.id, table1.name, table2.amount, table2.date FROM `table1` JOIN `table2` ON table1.id = table2.customer_id WHERE amount>1515 ORDER BY amount;
# 先ON后WHERE

1、INNER JOIN（默认）

返回两个表都满足条件的记录

2、LEFT JOIN

返回左表满足条件的记录

3、RIGHT JOIN

返回右表满足条件的记录

4、FULL JOIN

返回其中一个表存在满足条件的记录
（有点数据库不支持，例如MySQL，可以使用UNION ALL将LEFT JOIN和RIGHT JOIN结合起来）

# mysql
SELECT id, name, amount, date FROM customers LEFT JOIN orders ON customers.id =orders.customer_id 
UNION ALL
SELECT id, name, amount, date FROM customers RIGHT JOIN orders ON customers.id=orders.customers_id

5、SELF JOIN

将一个表连接到自身，就像该表是两个表一样，为了区分两个表，在SQL语句中需要至少重命名一个表。

SQL 并没有 SELF JOIN 关键字，而是使用 WHERE 子句来达到自连接的目的。

SELECT a.id, a.name, a.salary,b.name AS this_ame, b.salary AS this_salary FROM customer AS a, customer AS b WHERE a.salary<b.salary;

SELF JOIN 以右表为主，它先将左表中的每一行与右表中的第一行进行比较，然后再将左表中的每一行与右表中的第二行进行比较

6、CROSS JOIN

交叉连接，从两个或者多个连接表中返回记录集的笛卡尔积（左表的每一行与右表的每一行合并）

SELECT table1.column1, table2.column2... FROM table1 CROSS JOIN table2;

# or

SELECT table1.column1, table2.column2... FROM table1, table2;

UNION和UNION ALL （合并行）

UNION 用于合并两个或者多个 SELECT 语句的结果集，默认会过滤掉结果集中重复的记录，只保留一条，且按默认规则对结果集进行排序

UNION ALL 只是对结果集进行简单的合并，不会过滤重复的记录，也不会进行排序

SELECT column1, column2... FROM table1, table2 [WHERE condition1]

UNION/UNION ALL

SELECT column1,column2... FROM table1, table2 [WHERE condition2]

每个 SELECT 语句都必须拥有相同数量的字段；
不同 SELECT 语句的对应字段必须拥有相似的类型
不同 SELECT 语句的字段名不需要相同，SQL 会将第一个 SELECT 语句的字段名作为结果集的字段名

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 data_type;

# 添加 NOT NULL 约束
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

# 添加UNIQUE约束
ALTER TABLE table_name ADD CONSTRAINT myUniqueConstraint UNIQUE(column1, column2...);

# 使用ALTER TABLE 添加CHECK约束
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK(condition);

# 添加主键约束
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(column1, column2...);

# 删除UNIQUE约束
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;
## MySQL时使用以下语句
ALTER TABLE table_name DROP INDEX MyUniqueConstraint;

# 删除主键约束
ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;
## MySQL时使用以下语句
ALTER TABLE table_name DROP PRIMARY KEY;

TRUNCATE TABLE 清空表

DROP TABLE 用来删除表，包括删除该表的数据、结构、索引、触发器、约束等所有信息
TRUNCATE TABLE 仅仅删除表的所有记录，后续仍然可以使用该表
不带 WHERE 子句的 DELETE FROM 语句同样可以达到清空表的效果，但是 TRUNCATE TABLE 使用的系统资源和日志资源更少，因此比 DELETE FROM 更加快速
TRUNCATE TABLE 还能重置具有自动递增（AUTO_INCREMENT）属性的字段，而 DELETE FROM 却不具备该功能

TRUNCATE TABLE  table_name;

视图

1、CREATE VIEW 创建

视图（View）是一个由 SELECT 查询所定义出来的虚拟表

CREATE VIEW view_name AS SELECT column1, column2... FROM table_name WHERE [condition];

创建视图其实就是给 SELECT 查询起了一个名字而已，并不会真的执行 SELECT 语句,只有当使用视图时，数据库引擎才会真正执行 SELECT 查询，产生一个结果集

2、修改

（INSERT、UODATE、DELETE）
为了使得修改操作在视图中同步呈现，需要在创建视图时增加WITH CHECK OPTION子句。

要求

通过视图修改的数据必须呈现到视图中
视图中的数据必须满足基础表的要求

使用INSERT语句向视图中插入的数据必须满足WHERE子句的设定条件（UODATE、DELETE类似）

# 创建视图
CREATE VIEW customers_view AS SELECT id, name, age, address, salary FROM customers WHERE salary>2000 WITH CHECK OPTION; 

# 向视图插入数据
INSERT INTO customers_view(name, age, address, salary) VALUES('Tom', 30, 'MP', 3300.00);

3、删除

DROP VIEW view_name;

HAVING子句

通常与 GROUP BY 子句一起使用，用来过滤 GROUP BY 子句返回的分组结果集

GROUP BY 子句通常和聚合函数（COUNT()、SUM()、AVG()、MIN()、MAX() 等）一起使用来对结果进行分组，而 WHERE 子句不能和聚合函数一起使用，因此有了HAVING。

WHERE 子句在分组操作之前起作用，HAVING 子句在分组操作之后起作用。

SELECT column1, column2... 
FROM table1, table2 
WHERE [conditions] 
GROUP BY column1, column2... HAVING [conditions] 
ORDER BY column1, column2...

SQL事务

一条SQl语句称为一次基本操作，将若干条SQl语句“打包”在一起，共同执行一个完整的任务，就是事务。

一旦某个 SQL 语句执行失败，整个事务就失败了。事务失败后需要回滚所有的 SQL 语句。

事务属性：

原子性：所有语句，要么都成功要么都失败
一致性：数据必须完全符合所有预设规则
隔离性：数据库允许多个并发事务同时对其数据进行读写修改的能力。隔离分为不同级别，包括未提交、读提交、可重复读和串行化
持久性：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失

事务执行流程：

各种数据库对事务的支持细节不完全相同，以MySQL为例。
BEGIN或者START TRANSACTION：开始事务
COMMIT：提交事务
ROLLBACK：回滚事务
SAVEPOINT：在事务内部设置回滚标记点
RELEASE SAVEPOINT：删除回滚标记点
ROLLBACK TO：将事务回滚到标记点

事务控制命令仅能与 DML 类别的 SQL 命令一起使用，包括 INSERT、UPDATE、DELETE 和 SELECT，在创建或者删除表时不能使用事务，因为这些操作在数据库中是自动提交的。

# 开始事务
BEGIN;
# or
START TRANSACTION 

# 提交事务
COMMIT;
# 提交事务意味着真正执行事务包含的 SQL 语句，并把对数据库的修改写入到磁盘上的物理数据库中。意味着事务结束且执行成功

BEGIN;
INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Hardik', 27, 'Bhopal', 8500.00 );
COMMIT;

# 回滚
BEGIN;
DELETE FROM CUSTOMERS WHERE ID=4;
DELETE FROM CUSTOMERS WHERE ID=5;
ROLLBACK;

# 设置标记点
SAVEPOINT point_name;

# 回滚到标记点
ROLLBACK TO point_name;

BEGIN;
DELETE FROM CUSTOMERS WHERE ID=4;
SAVEPOINT sp;
DELETE FROM CUSTOMERS WHERE ID=5;
ROLLBACK TO sp;
# 只有 ID 为 4 的用户被删除，ID 为 5 的用户依然留在数据库中

临时表的使用

临时表的概念由 SQL Server 引入，用来存储和处理中间结果

临时表在会话期间创建，会话结束后自动被删除，和普通表一样可以执行各种操作

CREATE TEMPORARY TABLE table_name(
    column1 datatype;
    column2 datatype;
    ...
    columnN datatype;
    PRIMARY KEY(one or more columns)
);

当使用 SHOW TABLES 命令查看数据库中的表时，临时表将不会被显示。

如果希望在会话期间删除临时表，则可以使用 DROP TABLE 命令：

DROP TABLE table_name;

克隆表（复制表）

步骤：

执行SHOW CREATE TABLE table_name：获取创建表的CREATE TABLE语句
CREATE TABLE语句中的表名改成新表的名字，然后执行该语句
如果希望复制表中的数据，可以私用INSERT INTO和SELECT命令

# 旧表数据复制到新表
INSERT INTO new_table(column1, column2, column3, column4) SELECT column1, column2, column3, column4 FROM old_table;

SQL 子查询

将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件

子查询常用在 WHERE 子句和 FROM 子句后边

# WHERE子句后面
SELECT column_name FROM table1 WHERE column_name operator (SELECT column_name FROM table1/table2 [WHERE])

SELECT * FROM customers WHERE salary>(SELECT salary FROM customers WHERE name='komal');

SELECT * FROM customers WHERE id IN (SELECT id FROM customers WHERE salary>4500) AND age>25;

# FROM子句后面
SELECT column_name FROM (SELECT column_name FROM table1/table2 [WHERE] AS temp_table_name) WHERE condition;

AUTO_INCREMENT 自动增长序列

AUTO_INCREMENT 会在新记录插入表中时生成一个唯一的整数，这些整数是自动增长的，用户还可以指定增长的步长（默认为 1）

一个表中只能有一个字段使用 AUTO_INCREMENT 约束
在插入数据或者更新数据时，一般将 AUTO_INCREMENT 字段留空

# 获取AUTO_INCREMENT 最后的值（mysql）
SELECT LAST_INSERT_ID();

# 对现有序列重新编号
## 重新编号时必须十分小心，您应该检查您的表是否和另一个表关联
ALTER TABLE table_name DROP id;
ALTER TABLE table_name ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id);

# 从特定值开始增长（mysql）
CREATE TABLE table_name(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100 PRIMARY KEY (id)
    ...
);

# or

ALTER TABLE table_name  AUTO_INCREMENT = 100;

SQL注入

SQL 注入是一种代码渗透技术，是最常用的网络黑客技术之一，通过网页输入框将恶意 SQL 代码提交给服务器是最常见的 SQL 注入方式之一。

通常使用模式匹配（Pattern Matching），借助正则表达式来校验用户输入的数据，几乎每种编程语言都提供了模式匹配函数。