参考文章：

深入浅出SQL读书笔记

1 select进阶

1.1 使用case表达进行update

UPDATE my_table
SET new_column = 
CASE
      WHEN column1 = somevalue1  THEN newValue1
      WHEN column2 = somevalue2  THEN newValue2
      ELSE newValue3
END;

1.2 使用orderBy

SELECT title,category
FROM movie_table
WHERE
category = 'family'
ORDER BY title ASC ,purchasedTime DESC;

1.3 SUM , GROUP BY

SELECT SUM(sales)
FROM cookie_sales
GROUP BY first_time
ORDER BY SUM(sales) DESC;

1.4 DISTINCT

//算出每个女孩卖饼干的天数
SELECT COUNT(DISTANCT sale_date) // distinct 是关键字，所以不需要加()
FROM cookie_sales;

1.5 LIMIT 查询结果的数量

//只返回第二条记录
SELECT first_name , SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales) DESC
LIMIT 1,1;//表示从第一条数据开始，显示一条数据
//如果是LIMIT 2： 表示只显示两条记录

2 多张表

2.1 SUBSTRING_INDEX

SELECT * FROM my_contacts
WHERE gender= 'F'
AND status = 'single'
AND state = 'MA'
AND seeking LIKE '%single M%'
AND birthday > '1950-08-28'
AND birthady < '1960-08-28'
AND SUBSTRING_INDEX(interests,',',1) = 'animals' //「1」表示寻找的对象为第一个逗号的内容；如果改为2，它会取第二个逗号前的内容

2.2 外键

外键可以未NULL,外键不要求唯一，外键为NULL，表示在父表中没有相符的主键。

2.2.1外键约束 CONSTAINT

//创建带有外键的表
CREATE TABLE interests(
 int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 interest VARCHAR(50) NOT NULL,
 contact_id INT NOT NULL,
 CONSTAINT my_contacts_contact_id // 这部分称为contraint，它的命名方式告知我们键的来源表(my_contacts)，键的名称（contact_id），还能说明它是一个外键
 FOREIGN KEY (contact_id) // 括号中的列名就代表外键，可以随意命名
 REFERENCES my_contacts (contact_id) // my_contact表示外键的来源，contact_id表示在另外一张表中的名称
)

2.3 junction table

在多表对多表之间，需要一个中间桥梁来存储所有women_id和shoe_id,从而把关系简化为一对多。这个中间桥梁就是junction table，它用来存储两个相关表的主键。

当遇到多表对多表时，一定要创建中间表吗？

答：是的，如果两个表具有多对多的关系，结果只会造成重复组，违反了第一范式。

第一范式(1NF)

数据列只包含具有原子性的值（yellow,blue,white不能整个作为value）

没有重复的数据组

举例：下标的数据不具有原子性，一列只能包含一种颜色

toy_id	toy	colors
2	kite	white,yellow,blue
5	yoyo	white,yellow

仍然非1NF,因为各个颜色列都存储了相同分类中的数据——都是varchar类型的玩具颜色

toy_id	toy	color1	color2
2	kite	white	yellow
5	yoyo	blue	yellow

符合1NF

table_1: // toy_id作为主键

toy_id	toy
2	kite
5	yoyo

table_2: // 使用toy_id和color值组成唯一的主键

toy_id	color
5	white
5	blue
6	yellow

2.4 快速表达函数依赖

T.x -> T.y 可以解释成：在关系表T中，y列函数依赖于x列。

2.5 部分函数依赖

部分函数依赖指的是：非主键的列依赖于组合主键的某个部分；

2.6 传递函数依赖

如果改变任何非键列可能造成其他列的改变，即为传递依赖。

2.7 第二范式

符合第一范式

没有部分函数依赖

?? ： ==只要所有列都是主键的一部分或者表中有唯一主键列符合1NF的表也会符合2NF。==

2.8 第三范式

符合2NF

没有传递函数依赖

3 联接

3.1 AS

AS 就是把来自另外一张表中的结果填入新表中

CREATE TABLE WORK
(
 id INT(11) NOT NULL AUTO_CREMENT PRIMARY KEY,
 profession varchar(20)

) AS
SELECT profession FROM my_contact
GROUP BY profession
ORDER BY profession;

如果两张表里的列名称不一样呢？

CREATE TABLE WORK
(
 id INT(11) NOT NULL AUTO_CREMENT PRIMARY KEY,
 mc_prof varchar(20)

) AS
SELECT profession AS mc_prof FROM my_contact // AS 用来命名别名
GROUP BY profession
ORDER BY profession;

3.2 内联接

3.2.1 笛卡尔积/交叉积/交叉联接(cross join)

CROSS JOIN返回两张表的每一行相乘的结果

假设有两张表，一张玩具表t，一张孩子表b；

SELECT t.toy ,b.boy
RROM toys AS t
CROSS JOIN
boys AS b;

如果t表有5条记录，b表有4条记录，那么最终的结果就有5 X 4 = 20条记录；

3.2.2 相等连接（equijoin）

==内连接就是通过查询中的条件移除了某些结果数据行后的交叉连接。==

解释：有下面两张表：

职业表：

profession
prof_id
profession

联系人表:

my_contacts
contact_id
last_name
first_name
phone
email
gender
prof_id
zip_code

现在有下面的sql语句：

SELECT mc.last_name,
mc.first_name,
p.profession
FROM my_contacts AS mc
INNER JOIN
profession AS p
ON mc.prof_id = p.prof_id; //只有当prof_id相同时才联结

结果：

last_name	first_name	profession
Everett	Joan	artist
Singh	Paul	chef

3.2.3 不等联接（non-equition）

boys表：

boy_id	boy	toy_id
1	Davey	3
2	Bobby	5

toy表：

boy_id	toy
1	hula hoop
2	balsa glider

下面语句能够查出每个男孩没有的玩具：

SELECT boys.boy,toys.toy
FROM boys 
INNER JOIN
toys
ON boys.toy_id <> toys.toy_id
ORDER BY boys.boy;

3.2.4 自然联接(natural join)

自然联接只有在联接的列在两张表中的名称相同时才会有用

boys表：

boy_id	boy	toy_id
1	Davey	3
2	Bobby	5

toy表：

boy_id	toy
1	hula hoop
2	balsa glider

toy_id在两张表中的名称一样;

如果我们想知道每个男孩拥有什么玩具：

SELECT boys.boy , toys.toy
FROM boys
NATURAL JOIN
toys;

结果：

boy	toy
Richie	hula hoop
Beaver	balsa glider

4 子查询

把甲查询的结果作为乙查询的输入

SELECT some_column,another_column
FROM table
WHERE column = (SELECT column FROM table);

举例：

SELECT last_name,first_name
FROM my_contacts
WHERE zip_code  = （SELECT zip_code FROM zip_code WHERE city = 'Memphis' AND state = 'TN'）

4.1 关联子查询

在非关联子查询中，内层查询先被RDBMS解释，然后才输出到外层查询。关联子查询是指内层查询的解析需要依赖外层查询的结果；

比如：计算my_contacts里每个人各有几个兴趣，然后返回具有三项兴趣的人

SELECT mc.first_name,mc.last_name
FROM my_contacts AS mc
WHERE 3 = (SELECT COUNT(*) FROM contact_interest WHERE contact_id = mc.contact_id);

必须先有contact_id才能进行内层查询；

4.3 和NOT EXIST搭配

SELECT mc.first_name firstname,mc.last_name lastname,
FROM my_contacts mc
WHERE NOT EXIST
（SELECT * FROM job_current jc WHERE mc.contact_id = jc.contact_id）;

例子：返回每个人的电子邮件地址的查询，电子邮件的主人至少拥有一项兴趣，单其在job_current表中没有相应记录。

SELECT mc.email FROM my_contact mc 
WHERE
EXIST
(SELECT * FROM contct_interest ci WHERE mc.contact_id = ci.contact_id)
AND
NOT EXIST
(SELECT * FROM job_current jc WHERE mc.contact_id = jc.contact_id);

外联接，自联接与联合

4.1 左外联接（LEFT OUTER JOIN）

接收左表的所有行，并用这些行与右表中的行匹配，当左表和右表具有一对多关系时，左外联接特别有用。左联接一定会提供数据行，无论是否能在另一张表中找出相匹配的行。 NULL表示在右表中找不到与左表相符的记录。

举例:

toy表：

toy_id	toy
1	hula hoop
2	balsa glider
3	toy soldiers

girl：

girl_id	girl	toy_id
1	Jane	3
2	Sally	4
3	Cindy	1

SELECT g.girl,t.toy 
FROM toys t //left table
LEFT OUTER JOIN girls g //right table
ON g.toy_id = t.toy_id;

结果：

girl	toy
Cindy	hula hoop
NULL	balsa glider
Jane	toy soldiers

4.2 右外联接（RIGHT OUTER JOIN）

右外联接根据右表评估；

4.3 取得多张表的内容UNION

UNION 只能接受一个ORDER BY且必须位于语句末端

UNION 中每个SELECT 语句中列的数量必须一致。不可以由第一条语句选取了一列，由其他语句却选择了两列；

SELECT title FROM job_current
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listings
ORDER BY title;

使用UNION ALL 将不会过滤掉重复的：

SELECT title FROM job_current
UNION ALL
SELECT title FROM job_desired
UNION ALL
SELECT title FROM job_listings
ORDER BY title;

从联合创建表：

CREATE TABLE my_union //新表名称
AS
SELECT title FROM job_current 
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listings;

4.4 INTERSECT 与 EXCEPT

INTERSECT 交集

EXCEPT 差集

4.5 子查询和联接

5.多人合作时数据库管理

5.1 检查约束：加入CHECK

CREATE TABLE piggy_bank{
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    coin CHAR(1) CHECK(coin IN('P','N','D','Q'))
}

在MySQL中，虽然你可在创建表时加上约束，但是不会有什么帮助，MySQL只会忽略它。

添加性别约束：

ALTER TABLE my_contacts
ADD CONSTRAINT CHECK gender IN('M','F');

5.2 创建视图

CREATE VIEW web_designer AS
SELECT mc.first_name,mc.last_name,mc.phone,mc.email
FROM my_contacts mc
NATURAL JOIN job_desired jd
WHERE jd.title = 'Web Designer';

查看视图内容（把它看成一张表）：

SELECT * FROM web_designers;

删除视图：

DROP VIEW web_designer;

6 事务

事务是一群可完成一组工作的SQL语句。

6.1 经典ACID检测

A : ATOMICITY 原子性，事务里的每个步骤都必须完成，否则只能都不完成。
C : CONSISTENCY 一致性。
I : ISOLATION 隔离性。
D : DURABILITY 持久性。

6.2 如何让事务在MySql下运作

在MySql下使用事务前，你需要先采用正确的==存储引擎（storage engine）==。

START TRANSACTION;
SELECT * FROM piggy_bank;
UPDATE piggy_bank set coin = 'Q' where coin = 'P';
SELECT * FROM piggy_bank;
ROLLBACK;

在未commit之前都是可以回滚的；

7 安全性

默认情况下，第一位用户——根用户具有数据库操作权利。

SET PASSWORD FOR 'root'@'localhost = PASSWORD('b1dclll'); //localhost可选择性不加

添加新用户：

CREATE USER elise IDENTIFY BY '552622023';

授予权限：

GRANT SELECT ON my_contacts TO elise;

撤销权限：

REVOKE SELECT ON my_contact FROM elise;

不允许权利下发：

REVOKE GRANT OPTION //移除grand option权限
ON DELETE ON my_contacts
FROM happy,sleepy ; // 用户happy与sleepy还可以进行delete

Sqlite相关知识

深入浅出SQL读书笔记

1 select进阶

1.1 使用case表达进行update

1.2 使用orderBy

1.3 SUM , GROUP BY

1.4 DISTINCT

1.5 LIMIT 查询结果的数量

2 多张表

2.1 SUBSTRING_INDEX

2.2 外键

2.2.1外键约束 CONSTAINT

2.3 junction table

2.4 快速表达函数依赖

2.5 部分函数依赖

2.6 传递函数依赖

2.7 第二范式

2.8 第三范式

3 联接

3.1 AS

3.2 内联接

3.2.1 笛卡尔积/交叉积/交叉联接(cross join)

3.2.2 相等连接（equijoin）

3.2.3 不等联接（non-equition）

3.2.4 自然联接(natural join)

4 子查询

4.1 关联子查询

4.3 和NOT EXIST搭配

外联接，自联接与联合

4.1 左外联接（LEFT OUTER JOIN）

4.2 右外联接（RIGHT OUTER JOIN）

4.3 取得多张表的内容UNION

4.4 INTERSECT 与 EXCEPT

4.5 子查询和联接

5.多人合作时数据库管理

5.1 检查约束：加入CHECK

5.2 创建视图

6 事务

6.1 经典ACID检测

6.2 如何让事务在MySql下运作

7 安全性