深入浅出SQL

数据和表:保存所有东西的地方

数据库是保存表和其他相关SQL结构的容器。
数据库内的信息组成了
数据库由表构成。
是在数据库中包含数据的结构,由组成。
把对象属性分类,某一类信息是表中的一
表的包含了表中某个对象的所有的信息。
是存储在表中的一块数据。是一组能够描述某个事物的列的集合。列和行构成了表。
字段(field) 也常用来代称 记录(record)也常交替使用。

  • 创建数据库
    CREATE DATABASE gregs_list;
  • 使用数据库
    USE gregs_list;
  • 设定表:CREATE TABLE语句
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE my_contacts
    (
    last_name VARCHAR(30) NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    email VARCHAR(50),
    birthday DATE
    );

数据类型:

  • CHAR/CHARACTER
  • DEC/DECIMAL
  • DATETIME/TIMESTAMP
  • VARCHAR
  • DATE
  • BLOB
  • INT/INTEGER

检查创建的表:
DESC my_contacts;
删除表:
DROP TABLE my_contacts;
DROP TABLE会删除你的表和表里面所有的数据!
插入数据到表中:
INSERT INTO your_table (column_name1, column_name2, ...) VALUES ( 'value1', 'value2',...);
注意:数值类型不要加上单引号。
查询数据:
SELECT * FROM my_contacts;
用DEFAULT值填满空白列的值。

SELECT语句:取得精美包装里的数据

SELECT * FROM my_contacts WHERE first_name = 'Anne';
数据类型中,VARCHAR, CHAR, BLOB, DATE, TIME需要单引号,数字类的类型,DEC和INT则不需引号。
单引号是特殊字符,需要加上转义字符\。
不要使用双引号,因为你的SQL语句日后会搭配其他编程语言。在编程语言中使用””表示”从这里开始是SQL语句”,这样单引号才会被视为SQL语句的一部分,而不是其他编程语言的一部分。

SELECT特定列来限制结果数量并加快结果呈现。

  • AND 同时满足条件
  • OR 只要满足一项
  • IS NULL用IS NULL找到NULL
    SELECT * FROM easy_drinks WHERE NOT main IS NULL;

LIKE和通配符

  • % 表示任意数量的未知字符的替身
  • _ 只是一个未知字符的替身

BETWEEN等于使用<=和>=

IN和NOT IN
NOT反转查询结果,取得相反的值。
NOT一定要紧接在WHERE后面,和AND或OR一期使用时,则要直接接在AND或OR的后面。
SELECT drink_name FROM drink_info WHERE NOT carbs BETWEEN 3 AND 5;

SELECT date_name from black_book WHERE NOT date_name LIKE 'A%' AND NOT date_name LIKE 'B%';

DELETE和UPDATE:改变是件好事

DELETE

DELETE子句可以和WHERE子句搭配使用,使用方式和SELECT与WHERE的搭配方式一样。

1
2
3
DELETE FROM clown_info
WHERE
activities = 'dancing';

DELETE的规则:

  • DELETE不能删除单一列中的值或表中某一列的所有值
  • DELETE可用于删除一行或多行,根据WHERE子句而定
  • 下一段语句可以删除表中的每一行:
    DELETE FROM your_table;

除非你可以非常确定WHERE只会删除你打算删除的行,否则都应该用SELECT确认情况。

UPDATE

  1. 使用UPDATE,你可以改变单一列或所有列的值。在SET子句中加入更多column = value组,其间以逗号分隔:
    1
    2
    3
    4
    UPDATE your_table
    SET first_column = 'newvalue',
    second_column = 'another_value'
    WHERE column_name = somevalue;

聪明的表设计:为什么要规范化?

存放的数据量取决于数据的使用方式。

表都是关于关系的

创建表时可供遵循的步骤:

  1. 挑出事物,挑出你希望描述的某样事物。(什么是你希望表说明的主要事物呢?)
  2. 列出一份关于那样事物的信息列表,这些信息都是使用表时的必要信息。(你将如何使用这张表?)
  3. 使用信息列表,把关于那样事物的综合信息拆分成小块信息,以便用于组织表。(如何才能轻松的查询这张表?)

原子性数据

一小块无法或不应分割的信息。
规则一:具有原子性数据的列中不会有多个类型相同的值
规则二:具有原子性数据的表中不会有多个存储同类数据的列

让数据具有原子性是创建一个规范化表的第一步。

规范化表的优点:

  1. 规范化表中没有重复的数据,可以减小数据的大小
  2. 因为查找的数据较少,你的查询会更为快速

第一范式 FIRST NORMAL FORM
每个数据行必须包含具有原子性的值。
每个数据行必须有独一无二的识别项,人称主键。

  • 主键不可以为NULL
  • 插入新记录时必须指定主键值
  • 主键必须简洁
  • 主键值不可以被修改

SHOW CREATE TABLE my_contacts;

1
2
3
ALTER TABLE my_contacts
ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (contact_id);

ALTER:改写历史

  • CHANGE 可同时改变现有列的名称和数据类型
  • MODIFY 修改现有列的数据类型或位置
  • ADD 在当前表中添加一列,可自选类型
  • DROP 从表中删除某列
1
2
ALTER TABLE projekts
RENAME TO project_list;
1
2
3
ALTER TABLE project_list
CHANGE COLUMN descriptionofproj proj_desc VARCHAR(100),
CHANGE COLUMN contractoronjob con_name VARCHAR(30);
1
2
ALTER TABLE project_list
MODIFY COLUMN proj_desc VARCHAR(120);
1
2
ALTER TABLE project_list
DROP COLUMN start_date;

字符串函数

  • SUBSTRING(your_string, start_position, length)
  • UPPER(your_string)和LOWER(your_string)
  • REVERSE(your_string)
  • LTRIM(your_string)和RTRIM(your_string)
  • LENGTH(your_string)

    SELECT进阶:以新视角看你的数据

    CASE

    1
    2
    3
    4
    5
    6
    7
    8
    9
    UPDATE movie_table
    SET category =
    CASE
    WHEN drama = 'T' THEN 'drama'
    WHEN comedy = 'T' THEN 'comedy'
    WHEN action = 'T' THEN 'action'
    WHEN cartoon = 'T' AND rating = 'G' THEN 'family'
    ELSE 'misc'
    END;

ORDER BY

!”#$%&’()*+,-./0123:;<=>?@abcd[]^_`abcd{|}~

按多列排序 降序

1
2
3
SELECT title, category, pruchased
FROM movie_table
ORDER BY category, purchased DESC;

SUM能为我们加总

1
2
3
SELECT SUM(sales)
FROM cookie_sales
WHERE first_name = 'Nicole';

利勇GROUP BY完成分组加总

1
2
3
4
SELECT first_name, SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales) DESC;

AVG搭配GROUP BY

1
2
3
SELECT first_name, AVG(sales)
FROM cookie_sales
GROUP BY first_name;

MIN和MAX

COUNT计算天数

DISTINCT选出不同的值

1
2
3
SELECT DISTINCT sales_date
FROM cookie_sales
ORDER BY sale_date;

LIMIT查询结果的数量

LIMIT 0,4
查询结果起始处,查询返回结果的数量

多张表的数据库设计:拓展你的表

表应该是为了节省精力而设计。别为了客户设计不良的表而执意改善查询。

我们需要把不符合原子性的列移入新的表

模式(schema)用于表达数据库的结构,包括表和列,还有各种他们之间相互连接的方式。

如何从一张表变成两张表

  1. 移出兴趣列并把它存储至专属表。
  2. 添加足以识别my_contacts表中每个人的兴趣的列。我们需要独一无二的列来连接一切。
    可以把my_contacts表中的主键值作为interests表中的一列。外键FOREIGN KEY可告知兴趣属于my_contacts表中的哪个人。

外键是表中的某一列,它引用到另一个表的主键。

外键二三事:

  • 外键使用的主键也被称为父键(parent key)
  • 主键所在的表又被称为父表(parent table)
  • 外键能用于确认一张表中的行与另一张表中的行相对应。
  • 外键的值可以是NULL,及时主键值不可为NULL.
  • 外键值不需唯一——事实上,外键同城都没有唯一性。

插入外键列的值必须已经存在于父表的来源列中,这是引用完整性(referential integrity)
外键不一定必须是父表的主键,单必须有唯一性。

创建带有外键的表

1
2
3
4
5
6
7
8
CREATE TABLE interests (
int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
interest VARCHAR(50) NOT NULL,
contact_id INT NOT NULL,
CONSTRAINT my_contacts_contact_id_fk //约束的名字
FOREIGN KEY (contact_id) //括号中的列名就代表外键,可以随意命名
REFERENCE my_contact (contact_id) //外键的来源以及在另一张表中的名称
);

一对一,一对多,多对多,找出数据所属的模式后,设计多张表的关系——设计数据库模式,也就变得简单了。

数据模式:一对一

连接线为单纯的实线,表示连接意见事物与另一件事物。例如雇员表和工资表。
我们其实不会经常用到一对一的表。通常,把一对一的数据留在主表更合理,但是某些情况如下:

  1. 抽取数据或许能让你更快速的查询。例如,如果大多数时候你只需要查询SSN,就可以查询较小的SSN表。
  2. 如果有列包含还不知道的值,可以单独存储这一列,以免主要表中出现NULL。
  3. 我们可能希望某些数据不要太常被访问。隔离这些数据即可管制访问次数。以员工表为例,他们的薪资信息最好另存为一张表。
  4. 如果有一大块数据,例如BLOB类型,这段数据或许存为另一张表会更好。

    数据模式:一对多

    A表中的某一条记录可以对应到B表中的多条记录,但B表中的某一条记录只能对应到A表中的某一条记录。
    连接线应该带有黑色箭头俩表示一对多的连接关系。
    比如profession表和my_contacs表。

    数据模式:多对多

    许多女士拥有许多双鞋。
    连接线的两端都带有黑色箭头,代表连接许多事物与许多事物。
    在两个多对多的表之间需要一个中间桥梁来存储所有的woman_id与shoe_id,从而把关系简化为一对多。这个中间桥梁就是所谓的junction table(连接表),用来存储两个相关表的主键

在my_contacts表中,每一个人有多项兴趣,但每一个兴趣可能属于多个人,所以这种关系属于多对多的模式。

数据越规范,以查询取得数据也就越容易,对联接(join)也越有帮助。

有两列以上组成的键称为组合键,就是由多个数据列构成的主键,组合各列后形成具有唯一性的键。

表中的数据列本身对其他列也有关系。这是了解第二范式与第三范式的关键。

当某列的数据必须随着另一列的数据的改变而改变时,表示第一列函数依赖于第二列。
速记符号:
T.x -> T.y
在关系表中,y列函数依赖于x列。

部分函数依赖:非主键列依赖于组合主键的某个部分。

传递函数依赖:任何非键列与另一个非键列有关联。
举个例子:
courses
course_id
course_name
instructor
instructor_phone

在表中加入主键列有助于达成2NF。
第二范式的重点就是表的主键如何与其他数据产生关系。

第二范式:又称2NF
规则一:先符合1NF
规则二:没有部分函数依赖性。

任何具有人工主键且没有组合主键的表都符合2NF。

第三范式:又称3NF
规则一:先符合2NF;
规则二:没有传递函数依赖性。

联接与多张表的操作:不能单独存在吗?

查询的三种乐趣

同时(几乎同时啦)CREATE, SELECT, INSERT

  1. CREATE TABLE,然后利用SELECT进行INSERT

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE profession
    (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    profession VARCHAR(20)
    );
    INSERT INTO profession (profession)
    SELECT profession FROM my_contacts
    GROUP BY profession
    ORDER BY profession;
  2. 利用SELECT 进行CREATE TABLE,然后ALTER以添加主键

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE profession AS
    SELECT profession FROM my_contacts
    GROUP BY profession
    ORDER BY profession;
    ALTER TABLE profession
    ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (id);
  3. 同一时间CREATE, SELECT, INSERT

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE profession
    (
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    profession VARCHAR(20)
    ) AS
    SELECT professioin FROM my_contacts
    GROUP BY profession
    ORDER BY profession;

AS能把SELECT的查询结果填入新表中。
列的别名

1
2
3
4
5
6
7
8
CREATE TABLE profession
(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
mc_prof VARCHAR(20)
) AS
SELECT profession AS mc_prof FROM my_contacts
GROUP BY mc_prof
ORDER BY mc_prof;

以上创建的新表还是会有名为profession的列,而非mc_prof。

表名和列名都可以有别名,也可以省略AS.

1
2
3
4
SELECT profession mc_prof
FROM my_contacts mc
GROUP BY mc_prof
ORDER BY mc_prof;

关于内联接的二三事

交叉联接:CROSS JOIN返回两张表的每一行相乘的结果。

1
2
3
4
SELECT t.toy, b.boy
FROM toys AS t
CROSS JOIN
boys AS b;

或者可以省略CROSS JOIN

1
2
SELECT toys.toy, boys.boy
FORM toys, boys;

问:为什么需要交叉联接?
答:因为当我们乱玩联接时可能意外造成交叉联接。知道交叉联接的存在有益于找出修正联接的方式。交叉联接有事可用于检测RDBMS软件及其配置的运行速度。运行交叉联接所需的时间可以轻易的检测与比较出速度较慢的查询。

内联接:就是通过查询中的条件移出了某些结果数据行后的交叉联接。利用条件判断中的比较运算符结合两张表的记录。

相等联接(equijoin)

boy_id boy toy_id
1 Davey 3
2 Bobby 5
3 Beaver 2
4 Richie 1
toy_id toy
1 hula hoop
2 balsa glider
3 toy soldiers
4 harmonica
5 baseball cards
1
2
3
4
5
SELECT boys.boy, toys.toy
FROM boys
INNER JOIN
toys
ON boys.toy_id = toys.toy_id;

查询结果如下
| boy | toy |
|—|—|
| Richie | hula hoop |
| Beaver | balsa glider |
| Davey | toy soldiers |
| Bobby | baseball cards |

不等联接(non-equijoin)

测试不相等性的内联接
我们可以找出每个男孩没有的玩具

1
2
3
4
5
6
SELECT boys.boy, toys.toy
FROM boys
INNER JOIN
toys
ON boys.toy_id <> toys.toy_id
ORDER BY boys.boy;

自然联接(natural join)

自然联接只有在联接的列在两张表中的名称都相同时才会有用。自然联接会识别出每个表里的相同名称并返回相符的记录。

子查询:查询中的查询

子查询,是被另一个查询包围的查询,也可称为内层查询。

1
2
3
4
5
6
7
8
9
SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc
NATURAL JOIN
my_contacts AS mc
WHERE jc.title IN
(
SELECT title
FROM job_listings
);

子查询规则

  • 子查询都是单一SELECT语句。
  • 子查询总是位于小括号里。
  • 子查询没有属于自己的分号。
  • 子查询可能出现在查询中的四个地方:SELECT子句、选出COLUMN LIST作为其中一列、FROM子句、HAVING子句中。
  • 子查询能与INSERT、DELETE、UPDATE、SELECT一起使用。

作为预选取列的子查询

1
2
3
4
5
SELECT mc.first_name, mc.last_name,
(SELECT state
FROM zip_code
WHERE mc.zip_code = zip_code) AS state
FROM my_contacts mc;

非关联子查询

如果子查询可以独立运行且不会引用外层查询的任何结果,即称为非关联子查询。

1
2
3
4
5
6
7
SELECT mc.first_name, mc.last_name, jc.salary
FROM
my_contacts AS mc NATURAL JOIN job_current AS jc
WHERE
jc.salary > (SELECT jc.salary
FROM my_contacts mc NATURAL JOIN job_current jc
WHERE email = 'andy@weatherorama.com');

有多个值的非关联子查询

IN, NOT IN

1
2
3
SELECT  mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings);

关联子查询

内层查询的解析需要依赖外层查询的结果。

1
2
3
4
5
6
7
SELECT  mc.first_name, mc.last_name
FROM my_contacts AS mc
WHERE
3 = (
SELECT COUNT(*) FROM contacts_interest
WHERE contact_id = mc.contact_id
);

关联子查询的用法,是找出所有外层查询结果里不存在于关联表里的记录。
EXISTS, NOT EXISTS

任何子查询能实现的事都能以相同类型的联接来实现。

外联接、自联接与联合:新策略

LEFT OUTER JOIN 会匹配左表中的每一行及右表中符合条件的行

1
2
3
4
SELECT g.girl, t.toy
FROM girls g
LEFT OUTER JOIN toys t
ON g.toy_id = t.toy_id;

差别是:外连接一定会提供数据行,无论该行能否再另一个表中找出相匹配的行。
左外联接的结果集中的NULL 表示右表中没有找到左边中相符的记录。

右外联接与左外联接完全一样,除了它是用右表与左表比对。

自引用外键

表示它是引用同一张表内另一列的键。自引用外键是个处于其他目的而用于同一张表的主键。
改用自联接(self-join)来模拟联接两张表的效果,自联接能把单一表当成两张具有完全相同的信息的表来进行查询。

1
2
3
4
SELECT c1.name, c2.name AS boss
FROM clown_info c1
INNER JOIN clown_info c2
ON c1.boss_id = c2.id;

UNION的使用限制

UNION只能接受一个ORDER BY且必须位于语句末端。这是因为UNION已经把多个SELECT语句的查询结果串联起来并分组了。
UNION ALL会返回列的所有内容。

从联合创建表

1
2
3
4
CREATE TABLE my_union AS
SELECT title FROM job_current UNION
SELECT title FROM job_desired
UNION SELECT title FROM job_listings;

INTERSECT交集
只会返回同时在第一个与第二个查询中的列。
EXCEPT差集返回只出现在第一个查询,而不在第二个查询中的列。

把子查询转换为联接

可使用INNER JOIN替代包含子查询的WHERE子句。

1
2
3
SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings);

转换为

1
2
3
4
SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURAL JOIN my_contacts AS mc
INNER JOIN job_listings jl
ON jc.title = jl.title;

把自联接变成子查询

1
2
3
4
SELECT c1.name,c2.name AS boss
FROM clown_info c1
INNER JOIN clown_info c2
ON c1.boss_id = c2.id

变身后

1
2
3
4
SELECT c1.name,
(SELECT name FROM clown_info
WHERE c1.boss_id = id) AS boss
FROM clown_info c1;

  1. 子查询出现在SELECT的选取列表中
  2. 子查询依赖外层查询的结果才能取得正确的boss_id,所以它是关联子查询。

    约束、视图与事务:人多手杂,数据库受不了

    检查约束:加入CHECK

    CHECK约束限定允许插入某个列的值。它与WHERE子句都使用相同的条件表达式。
    1
    2
    3
    4
    5
    CREATE TABLE piggy_bank
    (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    coin CHAR(1) CHECK (coin IN ('P', 'N', 'D', 'Q'))
    )

MYSQL里,无法以CHECK强化数据完整性。

为性别列设定检查约束

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

创建视图 VIEW

文件可能被修改,把查询存储在数据库内才是更好的方式。

1
2
3
4
5
CREATE VIEW web_designers 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;

视图也被称为虚拟表
视图的好处:

  1. 视图把复杂查询简化为一个命令,二带来轻松的生活。
  2. 即使一直改变数据库结构,也不会破坏依赖表的应用程序。
  3. 创建视图也可以隐藏读者无需看到的信息。

可更新视图就是可以改变底层表的视图。
带有CHECK OPTION的视图,如过不符合WHERE条i就,插入或者更新操作即被拒绝。

视图使用完毕:DROP VIEW pb_dimes;

事务

事务是一群可完成一组工作的SQL语句。
在事务过程中,如果所有步骤无法不受干扰地完成,则不该完成任何单一步骤。
ACID:

  • ATOMICITY:原子性
  • CONSISTENCY:一致性
  • ISOLATION:隔离性
  • DURABILITY:持久性
1
2
3
START TRANSACTION;
COMMIT;
ROLLBACK;

安全性:保护你的资产

保护用户账号

添加新用户

GRANT语句给用户授权

`GRANT SELECT ON clown_info TO elsie;

REVOKE撤销权限

REVOKE DELETE ON chores FROM sleepy CASCADE;
都回被撤销
REVOKE DELETE ON chores FROM sleepy RESTRICT;
两方权限被保留,root用户会收到错误提示。

创建角色

CREATE ROLE date_entry;
GRANT SELECT, INSERT ON some_table TO date_entry;
GRANT date_entry TO doc;
DROP ROLE date_entry;

WITH ADMIN OPTION的角色

管理员权限

0%