什么是SQL?
SQL是Structured Query Language的缩写,意思是结构化查询语言,是一种在数据库管理系统(Relational Database Management System, RDBMS)中查询数据,或通过RDBMS对数据库中的数据进行更改的语言
常见的RDBMS有:
- Oracle Database:甲骨文公司的RDBMS
- SQL Server :微软公司的RDBMS
- DB2:IBM 公司的RDBMS
- PostgreSQL:开源的RDBMS
- MySQL :开源的RDBMS
注:不同RDBMS的SQL语言略有不同
执行原理
用户在客户端通过SQL语言,将需要的数据和对数据进行的操作的请求发送给RDBMS,RDBMS 根据该语句的内容返回所请求的数据,或者对存储在数据库中的数据进行更新。
根据对RDBMS 赋予的指令种类的不同,SQL 语句可以分为以下三类:
-
DDL(Data Definition Language,数据定义语言)
- CREATE: 创建数据库和表等对象
- DROP: 删除数据库和表等对象
- ALTER: 修改数据库和表等对象的结构
-
DML(Data Manipulation Language,数据操纵语言)
- SELECT:查询表中的数据
- INSERT:向表中插入新数据
- UPDATE:更新表中的数据
- DELETE:删除表中的数据
-
DCL(Data Control Language,数据控制语言)
- COMMIT: 确认对数据库中的数据进行的变更
- ROLLBACK: 取消对数据库中的数据进行的变更
- GRANT: 赋予用户操作权限
- REVOKE: 取消用户的操作权限
DDL(Data Definition Language,数据定义语言)
创建数据库(CREATE)
CREATE DATABASE shop;
创建表(CREATE)
CREATE TABLE Product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
每一列的数据类型(后述)是必须要指定的,数据类型包括:
- INTEGER 整数型
- NUMERIC ( 全体位数, 小数位数)
- CHAR 定长字符串
- VARCHAR 可变长字符串
- DATE 日期型
删除表(DROP)
DROP TABLE Product;
更新表(ALTER)
-- 在表中增加一列(ADD COLUMN)
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
-- 在表中删除一列(DROP COLUMN)
ALTER TABLE Product DROP COLUMN product_name_pinyin;
-- 变更表名(RENAME)
RENAME TABLE Poduct to Product;
DML(Data Manipulation Language,数据操纵语言)
基本语句
数据查询(SELECT)
-- 查询出所有的列
SELECT * FROM Product;
-- 从表中查询出需要的列
SELECT product_id, product_name, purchase_price
FROM Product;
-- 为列设定别名(AS)
SELECT product_id AS id,
product_name AS name,
purchase_price AS “价格”
FROM Product;
-- 将查询出的一列指定为常数
SELECT '2009-02-24' AS date, product_id, product_name
FROM Product;
-- 从查询结果中删除重复行(DISTINCT)
SELECT DISTINCT product_type
FROM Product;
查询条件(WHERE)
SELECT product_name, product_type
FROM Product;
WHERE product_type = '衣服';
算数运算符
- 加 +
- 减 -
- 乘 *
- 除 /
SELECT product_name, sale_price, sale_price * 2 AS "sale_price_x2"
FROM Product;
比较运算符
- 等于 =
- 不等于 <>
- 大于 >
- 大于等于 >=
- 小于 <
- 小于等于 <=
SELECT product_name, product_type, regist_date
FROM Product
WHERE regist_date < '2009-09-27';
-- 将算数运算符和比较运算符结合使用
SELECT product_name, sale_price, purchase_price
FROM Product
WHERE sale_price - purchase_price >= 500;
-- 不能对NULL使用比较运算符,正确的方法是
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NULL;
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NOT NULL;
逻辑运算符
-
NOT
(也就是sale_price<1000)
SELECT product_name, product_type, sale_price
FROM Product
WHERE NOT sale_price >= 1000;
-
AND
AND运算符在其两侧的查询条件都成立时整个查询条件才成立,其意思相当于“并且”。
SELECT product_name, purchase_price
FROM Product
WHERE product_type = '厨房用具'
AND sale_price >= 3000;
-
OR
运算符在其两侧的查询条件有一个成立时整个查询条件都成立,其意思相当于“或者”。
SELECT product_name, purchase_price
FROM Product
WHERE product_type = '厨房用具'
OR sale_price >= 3000;
分组聚合(GROUP BY)
- 常用的五个聚合函数:
- COUNT: 计算表中的记录数(行数)
- SUM: 计算表中数值列中数据的合计值
- AVG: 计算表中数值列中数据的平均值
- MAX: 求出表中任意列中数据的最大值
- MIN: 求出表中任意列中数据的最小值
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
-- 计算某一列的行数(不包含NULL)
SELECT COUNT(purchase_price)
FROM Product;
-- 计算删除重复数据后的行数
SELECT COUNT(DISTINCT product_type)
FROM Product;
-- SUM/AVG函数只能对数值类型的列使用,而MAX/MIN函数原则上可以适用于任何数据类型的列
SELECT MAX(regist_date), MIN(regist_date)
FROM Product;
-
GROUP BY和WHERE并用时SELECT语句的执行顺序:
FROM → WHERE → GROUP BY → SELECT
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
- 为聚合结果指定条件(HAVING)
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;
数据排序(ORDER BY)
-
子句的书写顺序
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
-
子句的执行顺序:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;
- 升序(ASC)或降序(DESC) 注意:默认升序
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;
数据插入(INSERT)
-- 包含列清单
INSERT INTO Product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
-- 省略列清单
START TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
COMMIT;
-- 从其他表中复制数据
INSERT INTO ProductCopy (product_id, product_name, product_type,sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product;
数据删除(DELETE)
-- 清空表
DELETE FROM Product;
-- 指定删除对象(搜索型DELETE)
DELETE FROM Product
WHERE sale_price >= 4000;
数据更新(UPDATE)
-- 更新整列
UPDATE Product
SET regist_date = '2009-10-10';
-- 指定条件的更新(搜索型UPDATE)
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
-- 多列更新
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
进阶语句
视图
注意:定义视图时不能使用ORDER BY子句
-- 创建视图(CREATE VIEW)
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
-- 使用视图
SELECT product_type, cnt_product
FROM ProductSum;
-- 删除视图(DROP VIEW)
DROP VIEW ProductSum;
子查询(一次性视图)
-- 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
FROM
( SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type
) AS ProductSum;
-- 在WHERE子句中使用标量子查询
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
-- 关联子查询
SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type = P2.product_type
GROUP BY product_type);
注意:
- 能够使用常数或者列名的地方,无论是SELECT 子句、GROUP BY 子句、HAVING 子句,还是ORDER BY 子句,几乎所有的地方都可以使用标量子查询。
- 这里起到关键作用的就是在子查询中添加的WHERE 子句的条件。该条件的意思就是,在同一商品种类中对各商品的销售单价和平均单价进行比较。
函数
函数大致可以分为以下几种。
- 算术函数(用来进行数值计算的函数)
- 字符串函数(用来进行字符串操作的函数)
- 日期函数(用来进行日期操作的函数)
- 转换函数(用来转换数据类型和值的函数)
- 聚合函数(用来进行数据聚合的函数)
-
算数函数
ABS (数值) —— 绝对值
MOD (被除数, 除数) —— 求余
ROUND (对象数值, 保留小数的位数) —— 四舍五入
-
字符串函数
CONCAT (字符串1, 字符串2, 字符串3) —— 拼接
LENGTH (字符串) —— 字符串长度
LOWER (字符串) —— 小写
UPPER (字符串) —— 大写
REPLACE (对象字符串,替换前的字符串,替换后的字符串) —— 替换
SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)—— 截取
-
日期函数
CURRENT_DATE —— 当前日期
CURRENT_TIME —— 当前时间
CURRENT_TIMESTAMP —— 当前的日期和时间
EXTRACT (日期元素 FROM 日期)
-
转换函数
CAST(转换前的值 AS 想要转换的数据类型)—— 类型转换
COALESCE (数据1,数据2,数据3……) —— 将NULL转换为其他值
谓词
-
LIKE
-
BETWEEN
-
IS NULL和IS NOT NULL
-
EXIST和NOT EXIST
-
IN和 NOT IN
注意:在使用IN 和NOT IN 时是无法选取出NULL 数据的。
CASE表达式
SELECT product_name,
CASE WHEN product_type = '衣服'
THEN CONCAT('A:', product_type)
WHEN product_type = '办公用品'
THEN CONCAT('B:', product_type)
WHEN product_type = '厨房用具'
THEN CONCAT('C:',product_type)
ELSE NULL
END AS abc_product_type
FROM Product;
表的加减法
- 表的加法(UNION)
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
通过UNION 进行并集运算时可以使用任何形式的SELECT 语句,WHERE、GROUP BY、HAVING 等子句都可以使用,但是ORDER BY 只能在最后使用一次。
注意:UNION会删去两个表中的重复记录。如果想保留重复记录,可以在UNION后面加ALL
- 选取表中的公共部分(INTERSECT)
MySQL不支持INTERSECT
- 表的减法(EXCEPT)
MySQL不支持EXCEPT
以列为单位对表进行联结(JOIN)
- 内联结(INNER JOIN)
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id;
像这样使用联结运算将满足相同规则的表联结起来时,WHERE、GROUP BY、HAVING、ORDER BY 等工具都可以正常使用.
- 外联结(OUTER JOIN)
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP
LEFT OUTER JOIN Product AS P
ON SP.product_id = P.product_id;
- 三张以上的表的联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,
P.sale_price,IP.inventory_quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';
DCL(Data Control Language,数据控制语言)
创建事务(START TRANSACTION) - 提交处理(COMMIT)
START TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
COMMIT;
取消处理(ROLLBACK)
START TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
ROLLBACK;