Hyper の 个人博客

交流技术，分享生活

0%

数据库操作（三）—— 核心篇

发表于 更新于 分类于 ， 阅读次数：
本文字数： 4.3k 阅读时长 ≈ 4 分钟

核心操作 第 14 - 21 章

子查询 subquery

1、处理顺序：从内向外

1
2
3
4
5
6
7
SELECT cust-name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                     FROM orderitems
                                     WHERE prod_id = 'TNT2'));

2、格式化：分解多行 适当缩进

3、基本思想：将硬编码转换为子查询，减少代码量

  • 列必须匹配：SELECT 子句和 WHERE 子句中的列数量相同

  • 最常见：用于 WHERE 子句的 IN 操作符和填充计算列

4、相关子查询 correlated subquery

  • 使用完全限定列名，指定调用 customers 表中该列名（否则默认自身比较，相当于自检）；若非完全限定引用一个具有二义性的列名，将返回错误

1
WHERE orders.cust_id = customers.cust.id;

  • 注意限制有歧义性的列名

联结表

1、关系

  • 主键 primary key：唯一标识

  • 外键 foreign key：某个表的一列，包含另一表主键值

2、可伸缩性 scale well：可适应不断增加的工作量并良好运作

3、创建联结（等值联结 equijoin）

1
2
3
4
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend.id = products.vend.id
ORDER BY vend_name, prod_name;

  • 笛卡尔积：由没有联结条件的表关系返回的结果（def 离散）

    • 因而需保证所有联结都有 WHERE 子句
    • 交叉联结 cross join

4、* 内部联结

1
2
3
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend.id = products.vend_id;

  • 联结条件：使用特定的 ON 子句给出

5、实例：相对子查询优化效率（具体问题具体分析）

1
2
3
4
5
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
	AND orderitems.order_num = orders.order_num
	AND prod_id = 'TNT2';

高级联结

1、使用表别名

1
2
3
4
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS
vend_title
FROM vendors
ORDER BY vend_name;

作用：

  • 缩短语句

  • 允许单条 SELECT 多次使用相同表，如：

1
2
3
4
5
SELECT cust_name, cust_contact
FROM customers AS a, orders AS i, orderitems AS ai
WHERE a.cust_id = i.cust_id
	AND ai.order_num = i.order_num
	AND prod_id = 'TNT2';

2、自联结

  • 作为外部语句来替代从相同表中检索使用的子查询

1
2
3
4
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
	AND p2.prod_id = 'DTNTR';

注：实际问题，效率需具体分析，不一定优于子查询

1
2
3
4
5
SELECT p1.prod_id, p1.prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                FROM products
                WHERE prod_id = 'DTNTR');

3、自然联结

  • 排除多次出现的列，仅使每个列返回一次（仅选择唯一列）

    • 通常对表使用通配符 SELECT *
1
2
3
4
5
6
SELECT c.*, o.order_num, o.order_date,
		oi.prod_id, oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'FB';

4、外部联结 OUTER JOIN （较少使用）

  • 联结包含了没有关联行的行

  • 类型：左外部联结 右外部联结

    • 使用时需要用 RIGHT / LEFT 关键字指定行范围（从右 / 左侧的表中选择所有行）
      • mysql 不支持 *= 等一系列简化操作符！
    • 可通过颠倒 FROM / WHERE 子句互换使用

5、带聚集函数的联结

组合查询

1、并 union：执行多个查询，将结果作为单个查询结果集返回

  • 使用情景：单个查询从多个表返回类似数据；对单个表多个查询，按单个返回

  • 多个 WHERE 子句的单条查询可以达到相同的作用

  • 使用于较为复杂过滤条件

2、UNION 规则

  • 由两条及以上 SELECT 语句构成

  • 每个查询包含相同表达式

  • 列数据类型兼容

3、功能

  • 自动去重（默认）

    • 返回所有匹配行：UNION ALL

  • 与 ORDER BY 排序：必须出现在最后一条 SELECT 语句后

1
2
3
4
5
6
7
8
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN(1001, 1002)
ORDER BY vend_id, prod_price;

全文本搜索

1、支持的数据库引擎：MyISAM（InnoDB 不支持）

2、使用全文本搜索：随数据改变不断索引被搜索的列

  • FULLTEXT ()：索引列

    • 不要在导入数据使用，降低导入速率

  • Match ()：指定被搜索的列

    • 使用值必须与 FULLTEXT () 中定义相同
    • 多个列索引，其次序与 FULLTEXT () 中保持一致

  • Against ()：指定要使用的搜索表达式

    • 文本中，靠前的行的给定词等级值高于后继
    • 对多个搜索项，包含更多匹配词的行等级值更高

  • 索引的搜索速度相当快

3、查询扩展

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

  • 步骤

    • 先进行全文本搜索
    • 检查匹配行，判定有用词（与给定词相关词）
    • 运用有用词和原给定词再次进行全文本搜索

  • 文本行数越多，查询扩展结果越好

4、布尔文本搜索

1
2
3
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE)

匹配词 heavy，但排除任何包含以 rope 开始的词

  • 布尔方式 boolean mode

    • 提供：匹配词 排斥词 排列提示 表达式分组 etc.
    • 没有定义 FULLTEXT 索引也可使用

  • 全文本布尔操作符（P128）

    • 如 + /- ：包含 / 排除
    • < >：包含，并减少 / 增加等级值
    • -：取消一个
    • *：词尾通配符

5、默认注意事项

  • 短词（<=3 个字符的词）从索引中被排除

  • 内建的非用词（stopword）被忽略

  • 出现行数高于 50% 的词作为非用词忽略

  • 少于三行的文本不返回结果

  • 忽略词中的单引号

  • 中文、日文等不具有词分隔符的语言无法正确返回结果

数据插入 INSERT

1、插入完整行

  • 每组值用一对圆括号括起来，以逗号分隔

  • 多使用 cust_id 明确插入，可在表结构改变后继续发挥作用

  • INSERT 一般无输出结果

  • 必须给出 VALUES 的正确数目；不提供列名则必须对每列给出一个值

1
2
3
4
5
6
INSERT INTO customers(cust_name,
                     cust_contact,
                     cust_country)
VALUES('Peter',
      NULL,
      'UK');

  • 省略列：NULL

    • 表的定义中给出默认值：不给定值时使用

  • 提高整体性能：用 INSERT LOW_PRIORITY INTO 降低插入的优先级

2、插入多行

  • 使用多条 INSERT 语句（可一次性提交）

3、插入检索所得数据

1
2
3
4
5
6
7
8
9
10
INSERT INTO customers(cust_name,
                     cust_contact,
                     cust_country)
VALUES('Peter',
      NULL,
      'UK');
SELECT cust_name,
		cust_contact, 
		cust_country
FROM custnew;

其中，列名不一定匹配，仅作顺序填充

  • 可包含 WHERE 子句以过滤插入数据

更新和删除数据

1、更新：UPDATE 语句

  • 不省略 WHERE 语句，避免更新表中所有行

  • 语句组成：要更新的表名 列名及其新值 要更新行的条件

1
2
3
4
UPDATE IGNORE customers
SET cust_email = 'balabala@qq.com',
	cust_name = 'bob'
WHERE cust_id = 10005;

  • 更新多个列：逗号分隔（最后一行除外）

  • IGNORE 关键字：在发生错误时依旧更新

  • 设为 NULL：删除某列值

2、删除：DELETE 语句

  • 不省略 WHERE 语句

  • 语句组成：要删除的表名 要删除行的条件

    • 注：即便删除表中所有行，DELETE 不执行删除表操作
    • 删除所有行使用 TRUNCATE TABLE

注：

  • 两者使用前最好先用 SELECT 语句测试，保证过滤的记录正确

  • 在使用强行引用完整数据库的更删时，仅可操作与其他表无关的数据行

创建和操纵表

1、创建：CREATE TABLE 语句

  • 需给出信息：无冲突表名 列名及定义

  • NULL、NOT NULL：允许 / 阻止插入没有值的列

    • 空串‘ ’视为一有效值

  • 主键可在创建时或创建后定义

  • AUTO_INCREMENT：自动增量

    • 每表仅一个列允许，以作为主键值

  • DEFAULT 数值：指定默认值

    • 仅可为常量，mysql 不支持函数

  • ENGINE=InnoDB（MyISAM，etc.）：指定所用引擎

    • 外键不可跨引擎调取

2、更新表：ALTER TABLE

  • 常用于定义外键

1
2
3
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_order
FOREIGN KEY (order_num) REFERENCES orders (order_num);

  • 更改表步骤

    • 用新布局创建新表
    • INSERT SELECT 复制数据
    • 检验新表，重命名或删除旧表，命名新表
    • 重新创建触发器、索引等

3、删除表：DROP TABLE

4、重命名表：RENAME TABLE

1
RENAME TABLE table1 TO table2
相关文章：