SQL—结构化查询语言

1.SQL简介

SQL是一种结构化查询语言，是专门为数据库而建立的操作命令集，是一门数据库语言。

对于数据库的交互操作离不开SQL，SQL为我们管理数据库提供了极大的方便。

1.1 数据库用例构建

注释：本文所有的SQL程序，都是基于文末的自建数据库进行，大家可以自行下载获取。

• 下载文末的create.txt、populate.txt文件
• 在MySQL命令行客户端中运行“CREATE DATABASE sample;”，创建sample数据库
• 在MySQL命令行客户端中运行“USE sample;”，转至sample数据库进行后续操作
• 在MySQL命令行客户端中运行create.txt中的SQL语句，创建数据表
• 在MySQL命令行客户端中运行populate.txt中的SQL语句，将数据导入sample数据库
• 在MySQL命令行客户端中运行“SHOW TABLES;”，查看数据表创建情况
• 在MySQL命令行客户端中运行“select * from Customers;select * from orderitems;select * from orders;select * from products;select * from vendors;”，查看数据导入情况
  

2.SQL总览

SQL语言的讲解可以从定义、语法规则、数据类型、SQL语法、函数、高级查询、视图这七个方面展开，但是我们需要知道SQL语言的核心与难点是解决数据库数据查询的问题，数据查询也是我们最常用的服务，所以我们要重点掌握这一核心技术！

3.SQL详解

3.1数据类型

SQL能够处理的数据类型包括以上十几种，这些数据都可以以表格的形式存储于数据库中，但是数据库的海纳百川是EXCEL无法比拟的，这也是为什么公司数据的存储用数据库的一个重要原因。

3.2 语法规则

语法规则是编写SQL语句必须遵守的硬规则，如果没有遵守，则会运行出错；所以我们对SQL程序的检查首先从它是否满足语法规则开始。

3.3 函数

函数多用于对查询到的数据进行处理，与SQL语句结合使用，所以SQL语言还具有一定的数据分析能力。

--求均值
SELECT AVG(列名) FROM 表名;
--记录条数
SELECT COUNT(列名) FROM 表名;
--获取查询到的第一条记录
SELECT FIRST(列名) FROM 表名;
--获取查询到的最后一条记录
SELECT LAST(列名) FROM 表名;
--获取查询到的最大记录
SELECT MAX(列名) FROM 表名;
--获取查询到的最小记录
SELECT MIN(列名) FROM 表名;
--获取字段总和
SELECT SUM(列名) FORM 表名;

--分组计算
SELECT 列名,aggregate_function(列名)
FROM 表名
WHERE 条件
GROUP BY 列名;

--分组计算并筛选符和条件的记录
SELECT 列名,aggregate_function(列名)
FROM 表名
WHERE 条件
GROUP BY 列名
HAVING aggergate_function(列名) 操作符 值;

--将字段值大写
SELECT UCASE(列名) FROM 表名;
--将字段值小写
SELECT LCASE(列名) FROM 表名;
--提取字段值
SELECT MID(列名,起始位,长度) FROM 表名;
--获取字段值长度
SELECT LEN(列名) FROM 表名;
--对字段值进行四舍五入
SELECT ROUND(列名,小数位数) FROM 表名;
--获取当前时间
SELECT NOW() FROM 表名;
--以规定格式显示字段
SELECT FORMAT(列名,格式) FROM 表名;

3.4 SQL语法

SQL语法讲解的是SQL语言中的保留关键字，也是我们进行数据库操作的直接工具。

SQL语法包括数据定义语言(DDL)，主要用于数据库、数据表的新建、修改、更新、删除；以及索引、主键、外键的创建、删除；它并非对数据的直接操作。

SQL语法中的数据操作语言(DML)，是对于数据的直接操作，包括数据的增删改查，其中"查"是核心问题。

子句、属性词都是服务于数据操作语言(DML)的一些保留关键字，以进行更复杂的数据操作。

通配符是与LIKE关键字结合使用的，用于模糊查找。

---

DELETE 语句

DELETE语句用于删除表中的行

DELTETE FROM 表名 WHERE [条件];

--删除orders表中的所有记录
DELETE FROM orders;
DELETE * FROM orders;

--删除orders表中order_num为20005的记录
DELETE FROM orders WHERE order_num = 20005;

---

UPDATE语句

UPDATE语句用于修改表中的数据

UPDATE 表名 SET 列名=新值 WHERE [条件];

--更新某一行中的一列
UPDATE customers SET cust_country = 'CHAIN' WHERE cust_name = Fun4All;

-- 更新某一行中的若干列
UPDATE customers SET cust_country = 'CHAIN',cust_state = 'MI' WHERE cust_name = 'Kids Place';

-- 用一表数据更新另一表数据
UPDATE customers SET A.cust_zip = B.order_date FROM customers A,orders B WHERE A.cust_id  = B.cust_id;

---

INSERT INTO 语句

INSERT INTO语句用于向表格中插入新的行

--为所有列插入数据
INSERT INTO 表名 VALUES(值1，值2，...);

--为若干列插入数据
INSERT INTO 表名 (列名1，列名2，...) VALUES(值1，值2，...);

--为所有列插入数据
INSERT INTO orderitems VALUES(20005,1,'BRO3',254,11.99);

--为若干列插入数据
INSERT INTO orderitems (order_item,quantity) VALUES(3,199);

---

SELECT语句

SELECT语句用于从表中选取数据

--获取某些字段
SELECT 列名 FROM 表名;
--获取全部记录
SELECT * FROM 表名;

--对获取的字段去重
SELECT DISTINCT 列名 FROM 表名;

--按条件查询数据表
SELECT 列名 FROM 表名 WHERE 列名 运算符 值;
SELECT 列名 FROM 表名 WHERE 条件1 AND 条件2;
SELECT 列名 FROM 表名 WHERE 条件1 OR 条件2;
SELECT 列名 FROM 表名 WHERE 列名 IS NULL;
SELECT 列名 FROM 表名 WHERE 列名 IS NOT NULL;

--对查询到的数据分组
SELECT 列名 FROM 表名 ORDER BY 列名;
SELECT 列名 FROM 表名 ORDER BY 列名 DESC;
SELECT 列名 FROM 表名 ORDER BY 列名1 DESC,列名2 ASC;
SELECT 列名 FROM 表名 WHERE 列名 LIKE 模式;
SELECT 列名 FROM 表名 WHERE 列名 NOT LIKE 模式;
SELECT 列名 FROM 表名 WHERE 列名 IN (value1,value2,...);
SELECT 列名 FROM 表名 WHERE 列名 BETWEEN value1 AND value2;

--获取前N个数据记录
SELECT TOP 个数 列名 FROM 表名;

--重命名数据表
SELECT 列名 FROM 表名 AS 新表名;
--重命名字段
SELECT 列名 AS 新列名 FROM 表名;

--两表查询
SELECT 列名 FROM 表名1,表名2 WHERE 条件;

--数据表复制
SELECT 列名 INTO 新表名 [IN externaldatabase] FROM 旧表名;

SELECT 字段 （查询字段）
FROM 表名（查询表名）
WHERE 条件（筛选条件）
GROUP BY 字段（对结果分组）
ORDER BY 字段（按字段排序）
HAVING 条件（过滤分组条件）
LIMIT NUM （限制函数）;

CREATE语句

CREATE语句可以用于创建数据库、数据表

--创建数据库
CREATE DATABASE 数据库名;

--创建数据表
CREATE TABLE 表名1
(
列名称1 数据类型(size),
列名称2 数据类型(size),
列名称3 数据类型(size) NOT NULL,
列名称4 数据类型(size) DEFAULT 值,
列名称5 数据类型(size) AUTO_INCREMENT,
CHECK (列名称>0),
UNIQUE(列名称3),
PRIMARY KEY (列名称4),
FOREIGN KEY (列名4) REFERENCES 表名2(列名4),
...
);

DROP语句

DROP语句可以删除索引、表和数据库

--删除索引
DROP INDEX index_name ON table_name;
DROP INDEX table_name.index_name;
DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX index_name;
 
--删除数据表
DROP TABLE 表名;

--删除数据库
DROP DATABASE 数据库名;

ALTER语句

ALTER TABLE语句用于在已有的表中添加、修改或删除行

--添加字段
ALTER TABLE table_name ADD column_name datatype;

--删除字段
ALTER TABLE table_name DROP COLUMN column_name;

--修改字段数据类型
ALTER TABLE table_name ALTER COLUMN column_name datatype;

3.5 高级查询

高级查询包括组合查询、子查询、联结表；用于实现逻辑更复杂的数据查询。

--组合查找
SELECT 列名1 FROM 表名1 UNION SELECT 列名2 FROM 表名2;
SELECT 列名1 FROM 表名1 UNION ALL SELECT 列名2 FROM 表名2;

--子查找
SELECT 列名1 FROM 表名1 WHERE 列名2 = (SELECT 列名3 FROM 表名2 WHERE 条件);

--联结表：多表查找
SELECT 列名 FROM 表名1 INNER JOIN 表名2 ON 条件;
SELECT 列名 FROM 表名1 LEFT JOIN 表名2 ON 条件;
SELECT 列名 FROM 表名1 RIGHT JOIN 表名2 ON 条件;
SELECT 列名 FROM 表名1 FULL JOIN 表名2 ON 条件;

3.6 视图

视图相当于查询数据的可视化。
视图包含行和列，就像一个真实的表，但数据库的设计和结构并不会受到视图中的函数、WHERE或JOIN语句的影响。

CREATE VIEW语句

CREATE VIEW语句用于创建视图

CREATE VIEW view_name AS SELECT 列名 FROM 表名 WHERE 条件;

DROP VIEW view_name;

UPDATE view_name SET 列名 = 值 WHERE 条件;

INSERT INTO view_name VALUES(value1,value2,...);

DELETE FROM view_name WHERE 条件;

4.事务

将若干SQL语句打包在一起，共同执行一个完整的任务，这就是事务，相当于多条SQL语句的集合。

• 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
• 事务用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
• 事务用来管理INSERT、UPDATE、DELETE语句

事务具有以下特性：

• 原子性：一个事务要么全部被执行，要么全部不执行
• 一致性：在事务开始之前和事务结束之后，数据库的完整性未被破坏
• 隔离性：数据库允许多个事务同时对其数据进行读写和修改的能力
• 持久性：事务处理结束后，对数据的修改是永久的。

事务控制语句：

• BEGIN：开始一个事务
• ROLLBACK：回滚事务
• COMMIT：提交事务
• SET AUTOCOMMIT = 0：禁止自动提交
• SET AUTOCOMMIT = 1：启动自动提交

5.附件下载

SQL操作用例