第十五章 联结表
联结表是SQL最强大的功能之一
-
关系表
- 把一类信息放在一个表中，不同的表通过“关系”相互关联
- 分解数据为多个表能更有效的存储、处理和更大的可伸缩性
-
外键
- 一个表的主键放在另外一个表的其中一列，就是另外一个表中的外键
-
联结
- 联结多个表返回一组输出
- 关系表是物理实体，联结表是临时存在与查询的执行当中
-
维护引用的完整性
- 外键必须是另外一个表中主键的合法值
-
创建联结
- 规定要联结的所有表
- 规定各个表如何关联
- SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
- 联结两个表时，实际上是把表1中的每一行跟表2中的每一行匹配，where子句作为过滤条件
-
笛卡尔积
- 联结两个表时，如果没有过滤条件，检索的结果是笛卡尔积，就是表1中的每一行匹配表2中的每一行，明显这不会是我们想要的结果，所以不要忘记where子句
-
内部联结：
- 上面所说的联结是基于两个表的相等测试，也叫内部联结
- 内部联结可以用专门的语法来指定
- FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id
- 跟上面使用where子句的效果一致
-
联结多个表
- 可以联结的表数量没有限制
- 联结的表太多会影响性能，不要联结不必要的表
联结有时可以代替子查询，同样效果的SQL操作一般存在多种方法，性能可能会受操作类型、数据量、索引或键以及其他的一些条件影响，需要结合实际情况多做实验才能找出最优的方法。
第十六章 创建高级联结
-
表别名
- 可以给表起别名，FROM user AS u WHERE u.id=1;
- 表别名跟列别名不一样，表别名不会返回到请求端
-
不同类型的联结
- 自联结（单条 SELECT 语句中不止一次引用相同的表）
- 自然联结（每个列只返回一次，我们用到内部联结的基本上都是自然联结）
- 外部联结（需要包含没有关联行的那些行，比如下面的需求）
对每个客户下了多少订单进行计算，包括没有下过单的客户
列出所有产品以及订购数量，包括没有人订购的产品
计算平均销售规模，包括没有下过单的客户
没有下过单的客户在客户表中，但是不在订单表中，检索结果需要包含这些用户就要用到外部联结
例子1：检索所有客户及其订单（内部联结有订单才会被检索出来）
SELECT customer.cust_id,order.order_num FROM customers INNER JOIN orders ON customer.cust_id = orders.cust_id;例子2：检索所有客户，包括没有订单的客户
SELECT customer.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customer.cust_id = orders.cust_id;LEFT：表示选择OUTER JOIN 左边的表的所有行
RIGHT：表示选择 OUTER JOIN 右边的表的所有行
例子2要检索出所有客户的订单数，客户表在 OUTER JOIN 的左边，所以明显是用 LEFT
-
带聚集函数的联结
- 内部联结和外部联结都可以带上聚集函数
第十七章 组合查询
把多个 SELECT 结果作为单个查询结果集返回（称为并或者复合查询）
-
使用到组合查询的两种情况：
- 在单个查询中从不同的表返回类似结构的数据
- 对单个表执行多个查询，按单个查询返回数据
- 多个 WHERE 条件和组合查询可以完成同样的工作
-
在两个 SELECT 语句中间使用 UNION 就可以输出集合结果
- 使用 UNION 的规则：
- 最少有两条 SELECT 语句组成，语句之间需要用 UNION 关键字分隔
- 每个查询必须包含相同的列、表达式或者聚集函数
- 列数据必须兼容（可以隐含转换的类型，比如不同的数值类型、不同的日期类型）
- UNION 默认是消除重复行的，如果要不取消，要用 UNION ALL 关键字
- 使用 UNION 的规则：
-
对组合查询结果排序
- 在最后一个 SELECT 语句使用 ORDER BY，就是对全部结果进行排序
第十八章 全文本搜索
-
并不是所有的引擎都支持全文搜索
- MyISAM 支持
- InnoDB 不支持（现在项目中用到的基本都是InnoDB）
-
索引
- 索引是一种数据结构
- 索引是把一个表中的列的值存储在一个数据结构中
- 最常用于索引的数据结构是 B-Tree
- 哈希表也是一种索引的数据结构（只适合于查询相等的查询）
- R-Tree和位图也可以作为索引的数据结构
B-Tree索引是有序的，会把作为索引列的值按顺序排列，同时索引也存储了对应行的指针，根据指针可以获取到其他列的数据
-
创建索引
- CREATE INDEX name_index ON employee (employee_name);
-
创建联合索引
- CREATE INDEX name_index ON employee (employee_name, employee_age);
索引就好比书的目录一样，把表中某一列的值作为索引达到快速搜索的目的
-
使用索引的缺点
- 索引会占用空间，表越大，索引就越大
- 性能损失，值更新的时候，索引也会更新
原则上某列在查询中经常被使用，就在该列上创建索引
全文本搜索必须索引被搜索的列
-
启用全文搜索支持
- 创建表的时候加上 FULLTEXT(note_text) 开启全文本搜索
-
进行全文本搜索
- Match() 指定被搜索的列
- Against() 指定要使用的搜索表达式
- SELECT note_text FROM productnotes WHERE Match(note_text) Against('xiaoming');
第十九章 插入数据
INSERT INTO ... VALUES ...
- 插入完整的行
- 插入行的一部分
- 插入多行
INSERT INTO 表名(列1, 列2, ...) VALUES (列1值, 列2值, ...), (列1值, 列2值, ...); - 插入某些查询的结果
- INSERT 和 SELECT 语句的结合，从另外一张表中导入数据:
- INSERT INTO customer(cust_id, cust_contact, cust_email, cust_name) SELECT cust_id, cust_contact, cust_email, cust_name FROM custnew;
INSERT INTO 表名(列1, 列2, ...) VALUES (列1值, 列2值, ...);
- 列名和值一一对应
- INSERT 可以省略某些列
- 该列允许为NULL
- 该列有默认值
INSERT LOW_PRIORITY INTO 可以降低 INSERT 语句的优先级，也适用于 UPDATE 和 DELETE 语句
第二十章 更新和删除数据
-
更新数据 UPDATE
更新特定行
更新所有行
UPDAET customers SET cust_name = 'xiaoming', cust_age = 27 WHERE cust_id = 10005;更新多条数据其中一条出错时，默认会回滚全部值，要继续执行的话加上 IGNORE 关键字
UPDATE IGNORE customers ...删除某个列的值，可以更新为 NULL
-
删除数据 DELETE
删除特定行
删除所有行
DELETE FROM customers WHERE cust_id = 10006;如果是清空整个表，使用 TRUNCATE TABLE，速度更快，实际是删除原来的表再重新创建一个，而不是一行行删除
第二十一章 创建和操纵表
-
创建表
- 创建的表必须不存在
CREATE TABLE customers IF NOT EXISTS ( cust_id int NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL, cust_address char(50) NULL, cust_city char(50) NULL, cust_email char(50) NULL, PRIMARY KEY(cust_id) ) ENGINE=InnoDB; -
主键
- 可以创建由多个列组成的主键，多个列的组合是唯一的就可以（什么情况会用到？）
PRIMARY KEY(cust_id, cust_item) -
使用 AUTO_INCREMENT
- 自动增量
- 每个表只有一个 AUTO_INCREMENT 列
- AUTO_INCREMENT 列必须被索引，比如成为主键
- SELECT last_insert_id() 可以返回最后一个 AUTO_INCERMENT 值
-
指定默认值
count int NOT NULL DEFAULT 1,
-
引擎类型
- CREATE TABLE 语句以 ENGINE=InnoDB 结束，指定引擎类型
- MySQl 具有多种引擎
- InnoDB 可靠的事务处理引擎，不支持全文搜索
- MEMORY 功能同MyISAM，但是数据存储在内存而不是磁盘，速度很快，适用于临时表
- MyISAM 性能极高的引擎，支持全文搜索，但不支持事务处理
-
外键不能跨引擎
- 使用一个引擎的表不能引用使用不同引擎的表的外键
-
更新表
- ALTER TABLE （表存储数据之后一般不应该被更新）
- 添加一列
- ALTER TABLE vendors ADD vend_phone CHAR(20);
- 删除一列
- ALTER TABLE vendors DROP COLUMN vend_phone;
- ALTER TABLE 常见的用途是定义外键
- ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREGIN KEY (prod_id) REFERENCES customers (cust_id);
-
复杂的表结构更改涉及到以下步骤：
- 创建一个新的表
- 使用 INSERT SELECT 语句从旧表导入数据到新表
- 检验新表的数据
- 删除旧表
- 用旧表的名字重命名新表
- 根据需要，重新创建触发器、存储过程、索引和外键
-
删除表
- DROP TABLE customers2;
- 永久删除该表，没有确认，也不能撤销
-
重命名表
- REMANE TABLE
- REMANE TABLE backup_customers TO customers;
