SQL基础教程（第2版）笔记整理

花了一段时间把SQL基础教程（第2版）看完，并把笔记整理好。

数据定义语言（Data Define Language）

数据操作语言（Data Manipulation Language）

数据控制语言（Data Control Language）

表的创建

CREATE TABLE 表名

(列名数据类型完整性约束);

表的删除与更新

DROP TABLE 表名; -- 删除表

ALTER TABLE 表名 ADD 列名数据类型; -- 增加列名

ALTER TABLE 表名 DROP列名; -- 删除列名

ALTER TABLE 表名 MODIFY 旧列名数据类型; -- 修改列名

ALTER TABLE 表名 CHANGE 新列名数据类型; -- 修改列名

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT; -- 删除默认

ALTER TABLE 表名 RENAME TO 新表名; -- 修改表名

ALTER TABLE 表名 MODIFY 列名数据类型 COMMENT ''； --添加备注

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

数据的插入

INSERT语句

INSERT INTO <表名> ( 列1 ,列2 , 列3 , …… ) VALUES ( 值1 , 值2 , 值3 , …… );

数据的删除

保留数据表，仅删除全部数据行的 DELETE 语句

DELETE FROM < 表名 >;

TRUNCATE < 表名 >;

删除部分数据行的搜索型 DELETE

DELETE FROM < 表名 > WHERE < 条件 >;

数据的更新

改变表中数据的 UPDATE 语句

UPDATE < 表名 > SET < 列名 > = < 表达式 > WHERE < 条件 >;

数据的查询

查询标准语句格式：

SELECT <列名> FROM <表名>;

查询所有列：* 代表所有

SELECT * FROM <表名>;

为列设定别名：AS（设定汉语别名时需要必须用双引号（"）括起来）

SELECT product_id AS id, product_name AS name FROM product;

从结果中去掉重复行：DISTINCT （关键字只能用在第一个列名之前）

SELECT DISTINCT product_type AS p_type, product_name FROM product;

聚合函数中使用DISTINCT

SELECT COUNT(DISTINCT product_type) FROM Product;

常数的查询：（ NULL 也被视为一类数据）

表达式：SELECT <常数值> AS <显示字段名> FROM product;

SELECT ' 商品 ' AS string, 38 AS number, '2009-02-24' AS date,

product_id, product_name

FROM product;

根据 WHERE 语句来选择记录：

SELECT <列名> FROM <表名> WHERE <条件>

注释的书写方法:

1行注释

书写在“--”之后，只能写在同一行。

多行注释

书写在“/*”和“*/”之间，可以跨多行。

算数运算符

+ - * /（加减乘除）

注意：所有包含 NULL 的计算，结果肯定是 NULL

SQL语句中也可以使用运算表达式：

SELECT product_name, sale_price, sale_price * 2 AS "sale_price_x2" FROM Product;

比较运算符（使用比较运算符时一定要注意不等号和等号的位置。）

= 相等 <> 不等于

>= 大于等于 <= 小于等于

> 大于 < 小于

字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。

逻辑运算符

NOT 、 AND 、OR

多个查询条件进行组合时，需要使用 AND 运算符或者 OR 运算符。

AND 运算符的优先级高于 OR 运算符。想要优先执行 OR 运算符时可以使用括号。

GROUP BY：（对表进行分组）

使用聚合函数和 GROUP BY 子句时需要注意以下4点。

① 只能写在 SELECT 子句之中

② GROUP BY 子句中不能使用 SELECT 子句中列的别名

③ GROUP BY 子句的聚合结果是无序的

④ WHERE 子句中不能使用聚合函数（SELECT子句和HAVING子句以及ORDER BY子句可以）

当聚合键中包含 NULL 时，也会将NULL 作为一组特定的数据

GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序

FROM → WHERE → GROUP BY → SELECT

GROUP BY表达式

SELECT < 列名 1>, < 列名2>, < 列名3>, ……

FROM < 表名 >

WHERE <条件>

GROUP BY <列名 1>, < 列名2>, < 列名3>, ……;

HAVING子句（HAVING 子句必须写在 GROUP BY 子句之后）

WHERE 子句 = 指定行所对应的条件

HAVING 子句 = 指定组所对应的条件

SELECT < 列名 1>, < 列名 2>, < 列名 3>, ……

FROM < 表名 >

GROUP BY < 列名 1>, < 列名 2>, < 列名 3>, ……

HAVING < 分组结果对应的条件 >

例子：

SELECT product_type, AVG(sale_price)

FROM Product

GROUP BY product_type

HAVING AVG(sale_price) >= 2500;

ORDER BY 子句（默认升序，DESC降序，ASC升序）（可以使用 SELECT 子句中定义别名）

SELECT < 列名 1>, < 列名 2>, < 列名 3>, ……

FROM < 表名 >

ORDER BY < 排序基准列 1>, < 排序基准列 2>, ……

子句的书写顺序

1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →

5. HAVING 子句 → 6. ORDER BY 子句

LIMIT 1,3

函数

函数大致可以分为以下几种：

● 聚合函数（用来进行数据聚合的函数）

COUNT ：计算表中的记录数（行数）

SELECT COUNT(列名) FROM Product; （下列函数表达式相同）

SUM ：计算表中数值列中数据的合计值

AVG ：计算表中数值列中数据的平均值

MAX ：求出表中任意列中数据的最大值

MIN ：求出表中任意列中数据的最小值

● 算术函数（用来进行数值计算的函数）

ABS 绝对值函数:ABS( 数值 )

SELECT m, ABS(m) AS abs_col FROM SampleMath;

MOD 求余函数:MOD( 被除数，除数 )

ROUND 四舍五入函数:ROUND( 对象数值，保留小数的位数 )

● 字符串函数（用来进行字符串操作的函数）

|| 拼接字符串函数:字符串 1 || 字符串 2

(MYSQL中使用:CONCAT(str1, str2))

LENGTH 字符串长度函数:LENGTH(str)

MySQL 中的LENGTH 这样以字节为单位的函数进行计算时

● 日期函数（用来进行日期操作的函数）

CURRENT_DATE 当前日期函数：CURRENT_DATE

SELECT CURRENT_DATE;

CURRENT_TIME 当前时间函数：CURRENT_TIME

EXTRACT 当前日期和时间函数：EXTRACT( 日期元素 FROM 日期 )

● 转换函数（用来转换数据类型和值的函数）

LOWER小写转换函数：LOWER( 字符串 )

REPLACE 字符串的替换函数：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

SUBSTRING 字符串的截取函数：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

UPPER 大写转换函数：UPPER( 字符串 )

CAST 类型转换函数：CAST （转换前的值 AS 想要转换的数据类型）

COALESCE 将 NULL 转换为其他值函数：COALESCE( 数据 1 ，数据 2 ，数据 3 …… )

谓词

谓词包括以下：

● LIKE （模糊匹配）

SELECT * FROM product LIKE ‘%s’; （后面匹配）s%’ （前面匹配） ‘%s%’（中间匹配）

‘_s_’ （三位且中间是s的） ‘_s’ （两位且结尾是s的） ‘s_’ （两位且开头是s的）

● BETWEEN （范围查询）

SELECT * FROM product WHERE sale_price BETWEEN 100 AND 1000;

● IS NULL 、 IS NOT NULL （判断是否为 NULL）

SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NULL;

● IN 、 NOT IN（ OR 的简便用法）

SELECT product_name, purchase_price FROM Product WHERE purchase_price IN (320, 500, 5000);

● EXISTS

CASE表达式

-- 不写默认为NULL，尽量写上

-- END不能省略

SELECT product_name,

CASE WHEN product_type = ' 衣服 '

THEN 'A ： ' | | product_type

WHEN product_type = ' 办公用品 '

THEN 'B ： ' | | product_type

WHEN product_type = ' 厨房用具 '

THEN 'C ： ' | | product_type

ELSE NULL

END AS abc_product_type

FROM Product;

例子：

SELECT SUM(CASE WHEN product_type = ' 衣服 '

THEN sale_price ELSE 0 END) AS sum_price_clothes,

SUM(CASE WHEN product_type = ' 厨房用具 '

THEN sale_price ELSE 0 END) AS sum_price_kitchen,

SUM(CASE WHEN product_type = ' 办公用品 '

THEN sale_price ELSE 0 END) AS sum_price_office

FROM Product;

集合运算

表的加法—— UNION（并集）

SELECT product_id, product_name, sale_price FROM Product

UNION

SELECT product_id, product_name, sale_price FROM Product2

ORDER BY sale_price;

注意事项① —— 作为运算对象的记录的列数必须相同

注意事项② —— 作为运算对象的记录中列的类型必须一致

注意事项③ —— 可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次

选取表中公共部分—— INTERSECT（交集）

SELECT product_id, product_name, sale_price FROM Product

INTERSECT

SELECT product_id, product_name, sale_price FROM Product2

ORDER BY sale_price;

包含重复行的集合运算—— ALL 选项

SELECT product_id, product_name FROM Product

UNION ALL

SELECT product_id, product_name FROM Product2;

记录的减法—— EXCEPT

SELECT product_id, product_name FROM Product

EXCEPT

SELECT product_id, product_name FROM Product2

ORDER BY product_id;

联结

联结（ JOIN ）就是将其他表中的列添加过来，进行“添加列”的集合运算。

UNION 是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位

进行的。

将两张表进行内联结

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, 

P.sale_price

FROM ShopProduct AS SP

JOIN Product AS P ON SP.product_id = P.product_id;

内联结要点① —— FROM 子句

第一点要注意的是，之前的 FROM 子句中只有一张表，而这次我们同时使用了 ShopProduct 和 Product 两张表。

FROM ShopProduct AS SP INNER JOIN Product AS P

使用关键字 INNER JOIN 就可以将两张表联结在一起了。 SP 和 P

分别是这两张表的别名，但别名并不是必需的。在 SELECT 子句中直接使用 ShopProduct 和 product _ id 这样的表的原名也没有关系，但由于表名太长会影响 SQL 语句的可读性，因此还是希望大家能够习惯使用别名。

内联结要点② —— ON 子句

第二点要注意的是 ON 后面的联结条件。

进行内联结时必须使用 ON 子句，并且要书写在 FROM 和 WHERE 之间。

内联结要点③ —— SELECT 子句

第三点要注意的是，在 SELECT 子句中指定的列。

使用联结时 SELECT 子句中的列需要按照“ < 表的别名 > . < 列名 > ”的格式进行书写。

外联结 —— OUTER JOIN

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P

ON SP.product_id = P.product_id;

外联结中使用 LEFT 、 RIGHT 来指定主表。使用二者所得到的结果完全相同。

交叉联结 —— CROSS JOIN

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name

FROM ShopProduct AS SP CROSS JOIN Product AS P;

事务

事务就是需要在同一个处理单元中执行的一系列更新处理的集合

事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

要点：

1.在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

2.事务处理可以用来维护数据库的完整性，保证SQL语句要么全部执行，要么全部不执行。

3.事务用来管理 insert,update,delete 语句

事务控制语句：

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier；把事务回滚到标记点；

SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

MYSQL事务

BEGIN;

UPDATE sc SET Grade = 81 WHERE Grade = 80;

DELETE FROM student WHERE Sname = '陈冬';

INSERT INTO student VALUE(9521105, '陈冬', '男', 19, '数学系');

UPDATE student SET Sage = 20 WHERE Sname = '陈冬';

COMMIT;

复杂查询

视图（将经常使用的 SELECT 语句做成视图。）

创建视图的 CREATE VIEW 语句

CREATE VIEW 视图名称 (< 视图列名 1>, < 视图列名 2>, …… )

删除视图

DROP VIEW 视图名称

例子：

CREATE VIEW ProductSum (product_type, cnt_product)

SELECT product_type, COUNT(*)

FROM Product

GROUP BY product_type;

子查询

子查询作为内层查询会首先执行。

SELECT product_type, cnt_product

FROM ( SELECT product_type, COUNT(*) AS cnt_product

FROM Product GROUP BY product_type ) -- 先执行FROM子句中的SELECT子句

AS ProductSum;

标量子查询就是返回单一值的子查询，绝对不能返回多行结果。

能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是ORDER BY 子句，只要使用单一值的几乎所有的地方都可以使用。

SELECT product_id, product_name, sale_price,

(SELECT AVG(sale_price) FROM Product) -- 标量子查询作为列名

AS avg_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);

posted @ 2019-02-28 14:19 咸鱼也是有梦想的阅读(997) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

咸鱼也是有梦想的

SQL基础教程（第2版）笔记整理

公告