SQL读书笔记之《SQL必知必会》

最近看了《SQL必知必会》这本书，以MySQL为主整理一下，方便以后查阅~

1.select.
2.distinct
3.limit
4.注释
5.order by
6.升序、降序
8.模糊匹配
10.拼接
15.分组
17.连接
18.组合查询
19.insert
20.insert select
29.存储过程
30.事务处理

一、基础知识

1.数据库(database)：一个以某种有组织的方式存储的数据集合。
2.数据库管理系统(DBMS)：数据库软件。
注意： 区分database和DBMS
3.模式(schema)：关于数据库和表的布局及特性的信息。
定义了数据如何存储、存储什么样的数据以及数据如何分解等信息，数据库和表都有模式。
4.数据类型限定了可存储在列中的数据类型，数据类型还帮助正确地分类数据，并在优化磁盘使用方面起重要的作用。
5.主键(primary key)：一列（或一组列），其值能够唯一标识表中每一行。

主键要满足的要求：多列时，所有列的组合必须唯一，但单个列可以不唯一
1）任两行都不相同 2）主键列非NULL 3）主键列的值不能修改、更新 4）主键值不能重用

6.SQL(Structured Query Language)：结构化查询语言。
7.关键字(keyword)：作为SQL组成部分的保留字。

二、查询数据

1.SELECT
SQL语句中的关键字不区分大小写，多条SQL语句必须以分号（;）分隔。

1）检索一列
SELECT name
FROM Customer;

2）检索多列
SELECT id,name,age
FROM Customer;

3)检索所有列
SELECT *
FROM Customer;

**2.DISTINCT** 使用DISTINCT关键字，只返回不同的值。 DISTINCT关键字，必须直接放在列名的前面，DISTINCT关键字作用于所有的列。

SELECT DISTINCT age
FROM Customer;

3.LIMIT
适用于MySQL、SQLite、MariaDB等数据库。
LIMIT指定返回的行数，OFFSET指定从第几行开始，下标从0开始。

SELECT name
FROM Customer
LIMIT 3 OFFSET 4;

在MySQL环境下，最后一句可以简化成limit 4,3

举例理解：原数据如下

uid	uname
1	XL
2	ZT

若SELECT * FROM user LIMIT 1 OFFSET 0;查出来的是 1 XL;
若SELECT * FROM user LIMIT 1 OFFSET 1;查出来的是 2 ZT;

4.注释
1）行内注释：使用两个连字符（–）嵌在行内

SELECT name
FROM Customer; -- 这是注释

2）单行注释：在一行的还是使用井字符（#），此种形式很少支持

# 这是注释

3）多行注释：注释从（/*）开始，到（*/）结束

5.ORDER BY 排序
ORDER BY子句放在SELECT语句中的最后

1)单列排序
SELECT id,name,age
FROM Customer
ORDER BY name;

2)多列排序
SELECT id,name,age
FROM Customer
ORDER BY name,age;

3)相对位置
SELECT id,name,age
FROM Customer
ORDER BY 2,3;

6.ASC 升序 DESC 降序
默认是ASC升序，且DESC只应用到直接位于其前面的列名。
如果想要在多个列上进行降序排列，必须对每一列制定DESC关键字。

SELECT id,name,age
FROM Customer
ORDER BY name DESC,age;

7.WHERE
使用WHERE子句进行过滤。注意：null和0、空字符串不同！
IN ：用于匹配一组值
BETWEEN：在两个值之间
NOT ：操作符用于否定一个条件
AND、OR：用于连接多个过滤条件，优先处理 AND，当涉及到多个 AND 和 OR 时，应用 () 来决定优先级。

8.LIKE 后的通配符
1）%表示任何字符出现任意次数，注意：%不会匹配为NULL的行

为了找到所有以Wang开头的用户
SELECT *
FROM Customer
WHERE name LIKE 'Wang%';

2）_表示匹配单个字符

SELECT *
FROM Customer
WHERE id LIKE '1_';

3）[]用来制定一个字符集，只有Access和SQL Server支持
此通配符可以用脱字符^来否定

找出以J或M开头的用户
SELECT *
FROM Customer
WHERE id LIKE '[JM]%';

Access中%、_、^要用 *、？、！替代

9.AS取别名

10.Concat 拼接
Concat()函数适用于MySQL，其他数据库用+、||，具体不做说明了

SELECT Concat(id, '(' , name, ')' )
FROM Customer；

11.TRIM 去空格
TRIM( )去除首尾空格，LTRIM( )去除左边空格，RTRIM( )去除右边空格

12.日期相关函数

函 数	说 明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

13.数值相关函数

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

14.汇总数据函数
聚集函数

函 数	说 明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

注意：
AVG() 会忽略 NULL 行。
COUNT(列名）会忽略NULL行，COUNT(*)不会忽略。

15.GROUP BY 分组

• 除了聚集计算函数外，SELECT语句中的每一列都必须在GROUP BY字句中给出。
  换句话说，任何没有出现在group by子句中的属性如果出现在select子句中的话就只能出- 现在聚集函数内部，否则这样的查询就是错误的。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
• HAVING过滤分组

SELECT age, COUNT(*) AS num
FROM Customer
WHERE age > 18
GROUP BY age
HAVING COUNT(*) >= 2;

16.子查询
子查询中只能返回一个列。

17.JOIN 联结
用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON。
1）自然连接
常见的是内连接。内连接又称等值连接，使用 INNER JOIN 关键字。
内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列；内连接属于自然连接。

2）自连接
一般比子查询的效率高。

SELECT c1.id,c1.name,c1.contact
FROM Customers as c1, Customers as c2
WHERE c1.name = c2.name
AND c2.contact = 'Jim'

3）外连接
左外连接LEFT OUTER JOIN，右外连接RIGHT OUTER JOIN

检索所有顾客的订单信息，包括还没有订单信息的顾客。
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.curt_id

18.UNION 组合查询

• UNION ALL会保留重复行，UNION默认去除重复行。
• 连接的每个查询必须包含相同的列、表达式或者聚集函数。
• 组合查询中只能包含一个ORDER BY子句，并且必须位于语句的最后。

19.INSERT
一般一次只能插入一条

INSERT INTO Customers (name, age)
VALUES('ZMM', 18);

20.INSERT SELECT
一次可以插入查询到的多条

INSERT INTO Customers (name, age)
SELECT name,age
FROM Customers2;

21.将一个表的内容复制到一个新表
SELECT INTO将数据复制到一个新表，但MySQL不适用

> SELECT * INTO newCustomers FROM Customers;

MySQL写法：
CREATE TABLE Customerscopy AS
SELECT * FROM Customers;

22.UPDATE
update set

UPDATE Customers
SET name = 'ZM'
WHERE id =1;

23.DELETE

DELETE FROM Customers
WHERE id = 1;

TRUNCATE TABLE 可以清空表，它的速度更快，因为不记录数据的变动。

24.CREATE TABLE 创建表
默认值DEFAULT，设置主键PRIMARY KEY

CREATE TABLE Customers
(
	id			INT						NOT NULL,
	name	VARCHAR(30)	NOT NULL,
	age		INT						NOT NULL	DEFAULT 18
	PRIMARY KEY('id');
);

25.ALTER TABLE 更改表

1)添加列
ALTER TABLE Customers
ADD contact VARCHAR(20);
2)删除列
ALTER TABLE Customers
DROP COLUMN contact ;

26.DROP TABLE 删除表

DROP TABLE Customers;

27.RENAME TABLE 重命名表

RENAME TABLE `Customers` TO `Customer`;

<div id="视图"></div> 

28.视图
视图是虚拟的表，本身不包含数据，包含的是一个查询。

1）创建视图
CREATE VIEW myview AS
SELECT name,age
FROM Customers;

2）删除视图
DROP VIEW myview ;

29.存储过程
简单来说，存储过程就是为以后使用而保存的一条或多条SQL语句。
三个主要好处：简单、安全、高性能

语法
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 …] 过程体
DELIMITER //
CREATE PROCEDURE myproc(OUT s int)
BEGIN
SELECT COUNT(*) INTO s FROM students;
END
//
DELIMITER ;
分隔符
MySQL默认以";“为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个”//“之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。
参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,"分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT:该值可在存储过程内部被改变，并可返回
INOUT:调用时指定，并且可被改变和返回
过程体
过程体的开始与结束使用BEGIN与END进行标识。

30.事务处理

• 事物（transaction）：一组SQL语句
• 回退（rollback）：撤销指定SQL语句的过程
• 提交（commit）：将未存储的SQL语句结果写入数据库表
• 保留点（savepoint）：实物处理中设置的临时占位符，可以对它发布回退
  注意：不能回退create、drop操作

MySQL事务处理块的开始：
START TRANSACTION
...
MySQL创建占位符：
SAVEPOINT delete1；

MySQL 的事务提交默认是隐式提交，也就是每执行一条语句就会提交一次。
当出现START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。
如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
 ...
SAVEPOINT delete1
 ...
ROLLBACK TO delete1
...
COMMIT

31.游标
32.触发器