学习自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),借助正则表达式来校验用户输入的数据,几乎每种编程语言都提供了模式匹配函数。

最后编辑:2023年02月15日 ©著作权归作者所有