国产限制级一区,国产97在线视频

第1章了解MySql

1.1 數(shù)據(jù)庫(kù)基礎(chǔ)

1.1.1 什么是數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù) db(datebase)是保存有組織的數(shù)據(jù)的容器。我們直接使用的如MySQL其實(shí)不是數(shù)據(jù)庫(kù)而是數(shù)據(jù)庫(kù)軟件 dbms:datebase Management System)。我們不直接訪問數(shù)據(jù)庫(kù)而是通過數(shù)據(jù)庫(kù)軟件來訪問數(shù)據(jù)庫(kù)。

1.1.2 表

某種特定數(shù)據(jù)類型的結(jié)構(gòu)化清單，

數(shù)據(jù)庫(kù)中每張表的名字是唯一的。

1.1.3 列和數(shù)據(jù)類型

理解為鍵名

1.1.4 行

每條數(shù)據(jù)

1.1.5 主鍵

主鍵本身是一列(或一組列)，其值能夠唯一區(qū)分表中每行
表可以沒有主鍵
若有主鍵則必須滿足一下2個(gè)條件
- 任意兩行不能由相同的主鍵
- 每行必須有一個(gè)主鍵，主鍵不能為NULL
主鍵的最好習(xí)慣
- 不更新主鍵列中的值
- 不重用主鍵列的值
- 不在主鍵中使用可能會(huì)更改的值

1.2 什么是SQL

SQL(Structured Query Language)結(jié)構(gòu)化查詢語(yǔ)言。是由一個(gè)標(biāo)準(zhǔn)委員會(huì)制定維護(hù)的，基本上通用于市面上的DBMS，但不同的DBMS的SQL語(yǔ)法會(huì)略有不同。

第2章 MySQL簡(jiǎn)介

2.1 什么是MySQL

各種吹牛

2.1.1 客戶機(jī)—服務(wù)器軟件

簡(jiǎn)單的CS模型

2.1.2 MySQL版本

羅列了各版本的區(qū)別

2.2 MySQL工具

在這里下載MySQL腳本按照附錄B的方法導(dǎo)入數(shù)據(jù)庫(kù)。

導(dǎo)入之后數(shù)據(jù)庫(kù)結(jié)構(gòu)為：

mysql> SHOW COLUMNS FROM customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+

mysql> SHOW COLUMNS FROM orderitems;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| order_num  | int(11)      | NO   | PRI | NULL    |       |
| order_item | int(11)      | NO   | PRI | NULL    |       |
| prod_id    | char(10)     | NO   | MUL | NULL    |       |
| quantity   | int(11)      | NO   |     | NULL    |       |
| item_price | decimal(8,2) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

mysql> SHOW COLUMNS FROM orders;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| order_num  | int(11)  | NO   | PRI | NULL    | auto_increment |
| order_date | datetime | NO   |     | NULL    |                |
| cust_id    | int(11)  | NO   | MUL | NULL    |                |
+------------+----------+------+-----+---------+----------------+

mysql> SHOW COLUMNS FROM productnotes;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| note_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| prod_id   | char(10) | NO   |     | NULL    |                |
| note_date | datetime | NO   |     | NULL    |                |
| note_text | text     | YES  | MUL | NULL    |                |
+-----------+----------+------+-----+---------+----------------+

mysql> SHOW COLUMNS FROM products;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| prod_id    | char(10)     | NO   | PRI | NULL    |       |
| vend_id    | int(11)      | NO   | MUL | NULL    |       |
| prod_name  | char(255)    | NO   |     | NULL    |       |
| prod_price | decimal(8,2) | NO   |     | NULL    |       |
| prod_desc  | text         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

mysql> SHOW COLUMNS FROM vendors;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| vend_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| vend_name    | char(50) | NO   |     | NULL    |                |
| vend_address | char(50) | YES  |     | NULL    |                |
| vend_city    | char(50) | YES  |     | NULL    |                |
| vend_state   | char(5)  | YES  |     | NULL    |                |
| vend_zip     | char(10) | YES  |     | NULL    |                |
| vend_country | char(50) | YES  |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+

第3章使用MySQL

3.1 鏈接MySQL

// 鏈接mysql
~$ mysql -u root -p

// 創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE my_first_sql;

// 展示該服務(wù)器的所有數(shù)據(jù)庫(kù)
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crashcourse        |
| my_first_sql       |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

// 打開數(shù)據(jù)庫(kù)
USE my_first_sql;
Database changed

// 展示當(dāng)前數(shù)據(jù)庫(kù)里面的表
SHOW TABLES;

// 展示指定表的所有列
SHOW COLUMNS FROM my_first_sql;

第4章檢索數(shù)據(jù)

4.1 SELECT語(yǔ)句

SELECT 語(yǔ)句需要2個(gè)必要信息從哪里查找和查找什么。

4.2 檢索單個(gè)列

關(guān)鍵字用大寫表，數(shù)據(jù)庫(kù)，行列名用小寫。

SELECT prod_name FROM products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

4.3 檢索多個(gè)列

SELECT cust_id, cust_zip, cust_email FROM customers;
+---------+----------+---------------------+
| cust_id | cust_zip | cust_email          |
+---------+----------+---------------------+
|   10001 | 44444    | ylee@coyote.com     |
|   10002 | 43333    | NULL                |
|   10003 | 42222    | rabbit@wascally.com |
|   10004 | 88888    | sam@yosemite.com    |
|   10005 | 54545    | NULL                |
+---------+----------+---------------------+

4.4 檢索所有列

SELECT * FROM products;

4.5 檢索不同的行

// 找出不同vend_id的行
SELECT DISTINCT vend_id FROM products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+

// 找出vend_id 和 prod_price不同的行
SELECT DISTINCT vend_id, prod_price FROM products;
+---------+------------+
| vend_id | prod_price |
+---------+------------+
|    1001 |       5.99 |
|    1001 |       9.99 |
|    1001 |      14.99 |
|    1003 |      13.00 |
|    1003 |      10.00 |
|    1003 |       2.50 |
|    1002 |       3.42 |
|    1005 |      35.00 |
|    1005 |      55.00 |
|    1002 |       8.99 |
|    1003 |      50.00 |
|    1003 |       4.49 |
+---------+------------+

4.6 限制結(jié)果

LIMIT可以獲取指定序列的行

// 從第1行開始取2行
SELECT vend_id 
FROM vendors 
LIMIT 1, 2;

+---------+
| vend_id |
+---------+
|    1002 |
|    1003 |
+---------+

// LIMIT后只有一個(gè)數(shù)字 代表取前5行
SELECT vend_id 
FROM vendors 
LIMIT 5;

+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1004 |
|    1005 |
+---------+

// 如果滿足條件的行數(shù)不足，則只會(huì)返回不足的結(jié)果
SELECT vend_id 
FROM vendors 
LIMIT 5 5;

+---------+
| vend_id |
+---------+
|    1006 |
+---------+

SELECT vend_id 
FROM vendors 
LIMIT 10 5;

Empty set   // 無結(jié)果

4.7 使用完全限定的表名

以下幾種寫法~~完全等效~~

SELECT crashcourse.vendors.vend_id 
FROM crashcourse.vendors;

SELECT vendors.vend_id 
FROM crashcourse.vendors;

SELECT vend_id 
FROM crashcourse.vendors;

SELECT crashcourse.vendors.vend_id 
FROM vendors;

SELECT vendors.vend_id 
FROM vendors;

SELECT vend_id 
FROM vendors;

第5章排序檢索數(shù)據(jù)

5.1 排序數(shù)據(jù)

// 未排序
SELECT order_item, prod_id
FROM orderitems;

+------------+---------+
| order_item | prod_id |
+------------+---------+
|          1 | ANV01   |
|          2 | ANV02   |
|          4 | ANV03   |
|          4 | FB      |
|          1 | FB      |
|          1 | FC      |
|          1 | JP2000  |
|          2 | OL1     |
|          3 | SLING   |
|          3 | TNT2    |
|          1 | TNT2    |
+------------+---------+

// 以order_item排序
SELECT order_item, prod_id
FROM orderitems
ORDER BY order_item;

+------------+---------+
| order_item | prod_id |
+------------+---------+
|          1 | ANV01   |
|          1 | FB      |
|          1 | FC      |
|          1 | TNT2    |
|          1 | JP2000  |
|          2 | ANV02   |
|          2 | OL1     |
|          3 | TNT2    |
|          3 | SLING   |
|          4 | FB      |
|          4 | ANV03   |
+------------+---------+

// 以order_item，和prod_id進(jìn)行排序。前者相同的情況下會(huì)用后者排序。
SELECT order_item, prod_id
FROM orderitems
ORDER BY order_item, prod_id;

+------------+---------+
| order_item | prod_id |
+------------+---------+
|          1 | ANV01   |
|          1 | FB      |
|          1 | FC      |
|          1 | JP2000  |
|          1 | TNT2    |
|          2 | ANV02   |
|          2 | OL1     |
|          3 | SLING   |
|          3 | TNT2    |
|          4 | ANV03   |
|          4 | FB      |
+------------+---------+

5.3 指定排序方向

倒序排序

SELECT order_item, prod_id
FROM orderitems
ORDER BY order_item DESC, prod_id;

+------------+---------+
| order_item | prod_id |
+------------+---------+
|          4 | ANV03   |
|          4 | FB      |
|          3 | SLING   |
|          3 | TNT2    |
|          2 | ANV02   |
|          2 | OL1     |
|          1 | ANV01   |
|          1 | FB      |
|          1 | FC      |
|          1 | JP2000  |
|          1 | TNT2    |
+------------+---------+

SELECT order_item, prod_id
FROM orderitems
ORDER BY order_item DESC, prod_id DESC;

+------------+---------+
| order_item | prod_id |
+------------+---------+
|          4 | FB      |
|          4 | ANV03   |
|          3 | TNT2    |
|          3 | SLING   |
|          2 | OL1     |
|          2 | ANV02   |
|          1 | TNT2    |
|          1 | JP2000  |
|          1 | FC      |
|          1 | FB      |
|          1 | ANV01   |
+------------+---------+

// 找到最貴的物品
SELECT prod_price 
FROM products
ORDER BY prod_price DESC
LIMIT 1;
+------------+
| prod_price |
+------------+
|      55.00 |
+------------+

第6章過濾數(shù)據(jù)

6.1 使用WHERE子句

WHERE 操作符

操作符	說明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN … AND ...	兩值之間
IS NULL	判空

SELECT vend_id, prod_name
FROM products
WHERE vend_id = 1002;

+---------+-----------+
| vend_id | prod_name |
+---------+-----------+
|    1002 | Fuses     |
|    1002 | Oil can   |
+---------+-----------+

SELECT vend_id, prod_name
FROM products
WHERE vend_id < 1002;

+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1001 | .5 ton anvil |
|    1001 | 1 ton anvil  |
|    1001 | 2 ton anvil  |
+---------+--------------+


SELECT vend_id, prod_name
FROM products
WHERE vend_id <= 1002;

+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1001 | .5 ton anvil |
|    1001 | 1 ton anvil  |
|    1001 | 2 ton anvil  |
|    1002 | Fuses        |
|    1002 | Oil can      |
+---------+--------------+

SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1002;

+---------+----------------+
| vend_id | prod_name      |
+---------+----------------+
|    1001 | .5 ton anvil   |
|    1001 | 1 ton anvil    |
|    1001 | 2 ton anvil    |
|    1003 | Detonator      |
|    1003 | Bird seed      |
|    1003 | Carrots        |
|    1005 | JetPack 1000   |
|    1005 | JetPack 2000   |
|    1003 | Safe           |
|    1003 | Sling          |
|    1003 | TNT (1 stick)  |
|    1003 | TNT (5 sticks) |
+---------+----------------+


// 兩者相同
SELECT prod_id, prod_name
FROM products
WHERE vend_id >= 1002 AND vend_id <= 1004;

SELECT vend_id, prod_name
FROM products
WHERE vend_id BETWEEN 1002 AND 1004;

+---------+----------------+
| vend_id | prod_name      |
+---------+----------------+
|    1003 | Detonator      |
|    1003 | Bird seed      |
|    1003 | Carrots        |
|    1002 | Fuses          |
|    1002 | Oil can        |
|    1003 | Safe           |
|    1003 | Sling          |
|    1003 | TNT (1 stick)  |
|    1003 | TNT (5 sticks) |
+---------+----------------+

SELECT cust_id, cust_email
FROM customers
WHERE cust_email IS NULL;

+---------+------------+
| cust_id | cust_email |
+---------+------------+
|   10002 | NULL       |
|   10005 | NULL       |
+---------+------------+

// 在通過過濾選擇出不具有特定值的行時(shí)，不會(huì)返回NULL值。猜測(cè)：NULL只能被 IS NULL匹配到
SELECT cust_id, cust_email
FROM customers
WHERE cust_email != 'ylee@coyote.com';

+---------+---------------------+
| cust_id | cust_email          |
+---------+---------------------+
|   10003 | rabbit@wascally.com |
|   10004 | sam@yosemite.com    |
+---------+---------------------+

第7章數(shù)據(jù)過濾

7.1 組合WHERE語(yǔ)句

用AND OR 進(jìn)行組合，一下代碼等價(jià)。

SELECT prod_id, prod_name
FROM products
WHERE vend_id >= 1002 AND vend_id <= 1004;

SELECT vend_id, prod_name
FROM products
WHERE vend_id BETWEEN 1002 AND 1004;

AND 的優(yōu)先級(jí)高于 OR用()來進(jìn)行組合運(yùn)算

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+


SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Safe           |      50.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+

猜測(cè)：AND操作可以把通過率低的條件放在前面，OR操作可以把通過率高的條件放在前面。這樣可以減少運(yùn)算次數(shù)，提高性能。

7.2 IN操作符

以下SQL語(yǔ)句等價(jià)

SELECT prod_name, prod_price, vend_id
FROM products
WHERE vend_id IN (1001, 1002, 1003)

SELECT prod_name, prod_price, vend_id
FROM products
WHERE vend_id = 1001 OR vend_id = 1002 OR vend_id = 1003;

7.3 NOT操作符

NOT對(duì)結(jié)果取反

一下SQL語(yǔ)句等價(jià)

SELECT prod_name, prod_price, vend_id
FROM products
WHERE NOT vend_id != 1001;

SELECT prod_name, prod_price, vend_id
FROM products
WHERE vend_id = 1001;

常用寫法：找到不匹配項(xiàng)目

SELECT prod_name, prod_price, vend_id
FROM products
WHERE NOT vend_id IN(1001, 1002);

+----------------+------------+---------+
| prod_name      | prod_price | vend_id |
+----------------+------------+---------+
| Detonator      |      13.00 |    1003 |
| Bird seed      |      10.00 |    1003 |
| Carrots        |       2.50 |    1003 |
| JetPack 1000   |      35.00 |    1005 |
| JetPack 2000   |      55.00 |    1005 |
| Safe           |      50.00 |    1003 |
| Sling          |       4.49 |    1003 |
| TNT (1 stick)  |       2.50 |    1003 |
| TNT (5 sticks) |      10.00 |    1003 |
+----------------+------------+---------+

第8章用通配符進(jìn)行過濾

8.1 LIKE操作符

8.1.1 百分號(hào)（%）通配符

%能匹配任意數(shù)量的任意字符

%通配符不區(qū)分大小寫，同時(shí)%不會(huì)匹配到NULL、

// 此時(shí)不區(qū)分大小寫
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'Jet%';

+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+



// 而=匹配是區(qū)分大小寫的
SELECT prod_name
FROM products
WHERE prod_name = 'JERPACK 1000';

Empty set (0.00 sec)

8.1.2 下劃線（_）通配符

_能匹配一個(gè)任意字符。

// _不能匹配到 .5 ton anvil
SELECT prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+

// %不能匹配到 .5 ton anvil
SELECT prod_name
FROM products
WHERE prod_name LIKE '% ton anvil';

+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+

8.2 使用通配符的技巧

出于性能的考慮盡量少使用通配符
盡量把通配符的搜索靠后
注意通配符位置的爭(zhēng)取性

第9章用正則表達(dá)式進(jìn)行搜索

9.2 使用MySQL正則表達(dá)式

9.2.1 基本字符匹配

正則是不是對(duì)內(nèi)容進(jìn)行全匹配的，只有內(nèi)容中有一段值匹配即可

'.' 代表任意一個(gè)字符

正則默認(rèn)不區(qū)分大小寫，若需要可用BINARY關(guān)鍵字

SELECT prod_name
FROM products
WHERE prod_name REGEXP '000';

+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

// '.' 代表任意一個(gè)字符 因此下面匹配的是長(zhǎng)度至少為12的列
SELECT prod_name
FROM products
WHERE prod_name REGEXP '............';

+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| JetPack 1000   |
| JetPack 2000   |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

9.2.2 進(jìn)行OR匹配

'|' 表示或類似SELECT 中的OR語(yǔ)句

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

9.2.3 匹配幾個(gè)字符之一

'[x|y|z]'用[和]括起字符表示可以為x或y或z，代替全匹配的.

PS:[x|y|z]可以縮寫為[xyz]

同時(shí)可以用^{來否定該集合(注意與定位符}區(qū)分)

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2 ton'
ORDER BY prod_name;

+---------------+
| prod_name     |
+---------------+
| 1 ton anvil   |
| 2 ton anvil   |
| JetPack 1000  |
| TNT (1 stick) |
+---------------+

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[12] ton'
ORDER BY prod_name;

+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[^12] ton'
ORDER BY prod_name;

+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
+--------------+

9.2.4 匹配范圍

[a-z]代表小寫字母集合

猜測(cè):拼接是從當(dāng)前數(shù)據(jù)庫(kù)編碼表里的編碼順序去匹配的。

[z-a]會(huì)報(bào)錯(cuò)invalid character range

[a-Z]會(huì)報(bào)錯(cuò)invalid character range

[A-z]則會(huì)匹配[A-Z] | [[]^-`] | [a-z]

SELECT prod_name
FROM products
WHERE prod_name REGEXP BINARY '[A-z] anvil'
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP BINARY '[A-Z] | [[]^-`] | [a-z] anvil'
ORDER BY prod_name;

9.2.5 匹配特殊字符

用\\進(jìn)行匹配

如果要匹配.應(yīng)該要寫\\.

同理如果想匹配\應(yīng)該要寫\\\

SELECT prod_name
FROM products
WHERE prod_name REGEXP BINARY '\\.';

+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
+--------------+

9.2.6 匹配字符表

感覺不實(shí)用

類	說明
[:alnum:]	任意字母和數(shù)字(同[a-zA-Z0-9])
[:alpha:]	任意字符(同[a-zA-Z])
[:blank:]	空格和制表(同[\t])
[:cntrl:]	ASCII控制字符(ASCII 0到31和127)
[:digit:]	任意數(shù)字(同[0-9])
[:graph:]	與[:print:]相同，但不包括空格
[:lower:]	任意小寫字母(同[a-z])
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在內(nèi)的任意空白字符(同[\f\n\r\t\v])
[:upper:]	任意大寫字母(同[A-Z])
[:xdigit:]	任意十六進(jìn)制數(shù)字(同[a-fA-F0-9])

9.2.7 匹配多個(gè)實(shí)例

類	說明
*	0個(gè)或多個(gè)匹配
+	1個(gè)或多個(gè)匹配(等于{1,})
?	0個(gè)或1個(gè)匹配(等于{0,1}) {n} 指定數(shù)目的匹配
{n,}	不少于指定數(shù)目的匹配
{n,m}	匹配數(shù)目的范圍(m不超過255)

下述語(yǔ)句用? 修飾了s

SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)';

+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

匹配連在一起的4個(gè)數(shù)字

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9]{4}';

+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

9.2.8 定位符

元字符	說明
^	文本的開始
$	文本的結(jié)尾
[[:<:]]	詞的開始
[[:>:]]	詞的結(jié)尾

檢索所有以數(shù)字開頭的產(chǎn)品名

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9.]';

+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| JetPack 1000   |
| JetPack 2000   |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+


SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9.]';

+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+

第10章創(chuàng)建計(jì)算字段

10.1 計(jì)算字段

有的數(shù)據(jù)不靠輸入，而是靠其他數(shù)據(jù)計(jì)算出來的，我們可以用計(jì)算字段處理。

10.2 拼接字段

用Concat()函數(shù)進(jìn)行拼接，以下語(yǔ)句把vendors中的vend_name和vend_country進(jìn)行拼接

SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors;

+-------------------------------------------+
| Concat(vend_name, '(', vend_country, ')') |
+-------------------------------------------+
| Anvils R Us(USA)                          |
| LT Supplies(USA)                          |
| ACME(USA)                                 |
| Furball Inc.(USA)                         |
| Jet Set(England)                          |
| Jouets Et Ours(France)                    |
+-------------------------------------------+

Trim()：可以去除串左右的空格

RTrim(): 可以去除串右側(cè)的空格

LTrim(): 可以去除串左側(cè)的空格

使用方法如下

SELECT Concat(Trim(vend_name), '(', Trim(vend_country), ')')
FROM vendors;

別名：使用AS關(guān)鍵字賦予計(jì)算值別名

SELECT Concat(Trim(vend_name), '(', Trim(vend_country), ')') 
AS vend_title
FROM vendors;

+------------------------+
| vend_title             |
+------------------------+
| Anvils R Us(USA)       |
| LT Supplies(USA)       |
| ACME(USA)              |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
+------------------------+

10.3 執(zhí)行算術(shù)計(jì)算

SELECT prod_id, 
       quantity, 
       item_price, 
       quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+

第11章使用數(shù)據(jù)處理函數(shù)

11.2.1 文本處理函數(shù)

SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors;

+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| Anvils R Us    | ANVILS R US      |
| LT Supplies    | LT SUPPLIES      |
| ACME           | ACME             |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
+----------------+------------------+

常用文本處理函數(shù)

函數(shù)	說明
Left()	返回串左邊的字符
Length()	返回串的長(zhǎng)度
Locate()	找出串的一個(gè)子串
Lower()	將串轉(zhuǎn)換為小寫
LTrim()	去掉串左邊的空格
Right()	返回串右邊的字符
RTrim()	去掉串右邊的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	將串轉(zhuǎn)換為大寫

Soundex()能用來匹配相同發(fā)音的串

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y. Lie');

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

11.2.2 日期和時(shí)間處理函數(shù)

常用的日期和時(shí)間處理函數(shù)

函數(shù)	說明
AddDate()	增加一個(gè)日期(天、周等)
AddTime()	增加一個(gè)時(shí)間(時(shí)、分等)
CurDate()	返回當(dāng)前日期
CurTime()	返回當(dāng)前時(shí)間
Date()	返回日期時(shí)間的日期部分
DateDiff()	計(jì)算兩個(gè)日期之差
Date_Add()	高度靈活的日期運(yùn)算函數(shù)
Date_Format()	返回一個(gè)格式化的日期或時(shí)間串
Day()	返回一個(gè)日期的天數(shù)部分
DayOfWeek()	對(duì)于一個(gè)日期，返回對(duì)應(yīng)的星期幾
Hour()	返回一個(gè)時(shí)間的小時(shí)部分
Minute()	返回一個(gè)時(shí)間的分鐘部分
Month()	返回一個(gè)日期的月份部分
Now()	返回當(dāng)前日期和時(shí)間
Second()	返回一個(gè)時(shí)間的秒部分
Time()	返回一個(gè)日期時(shí)間的時(shí)間部分
Year()	返回一個(gè)日期的年份部分

// 選出所有 2005年9月份的訂單
SELECT *
FROM orders
WHERE YEAR(order_date) = '2005' AND MONTH(order_date) = '09';

11.2.3 數(shù)值處理函數(shù)

常用數(shù)值處理函數(shù)

函數(shù)	說明
Abs()	返回一個(gè)數(shù)的絕對(duì)值
Cos()	返回一個(gè)角度的余弦
Exp()	返回一個(gè)數(shù)的指數(shù)值
Mod()	返回操作數(shù)的余數(shù)
Pi()	返回圓周率
Rand()	返回一個(gè)隨機(jī)數(shù)
Sin()	返回一個(gè)角度的正弦
Sqrt()	返回一個(gè)數(shù)的平方根
Tan()	返回一個(gè)角度的正切

第12章匯總數(shù)據(jù)

12.1 聚集函數(shù)

SQL聚集函數(shù)

函數(shù)	說明
AVG()	返回某列的平均值
COUNT()	返回某列的行數(shù)
MAX()	返回某列的最大值
MIN()	返回某列值之和
SUM()	返回某列值之和

AVG()

SELECT AVG(prod_price) AS avg_price
FROM products;

+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

COUNT()

如果COUNT()指定列名若值為NULL的話會(huì)被忽略

SELECT COUNT(*) AS order_2005_09
FROM orders
WHERE YEAR(order_date) = '2005' AND MONTH(order_date) = '09';

+---------------+
| order_2005_09 |
+---------------+
|             3 |
+---------------+

12.1.3 MAX()函數(shù)

SELECT MAX(prod_price) AS max_price
FROM products;

+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+

12.1.4 MIN()函數(shù)

SELECT MIN(prod_price) AS min_price
FROM products;

+-----------+
| min_price |
+-----------+
|      2.50 |
+-----------+

12.1.5 SUM()函數(shù)

SELECT SUM(quantity * item_price) AS items_ordered
FROM orderitems
WHERE order_num = 20005;

+---------------+
| items_ordered |
+---------------+
|        149.87 |
+---------------+

12.2 聚焦不同值

DISTINCT關(guān)鍵字用于聚焦不同值，與之對(duì)應(yīng)的是ALL關(guān)鍵字。ALL 關(guān)鍵字是默認(rèn)的不需特殊指定。

// 無指定關(guān)鍵字
SELECT AVG(prod_price) AS avg_price
FROM products;

+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

// 指定ALL關(guān)鍵字結(jié)果和無指定相同
SELECT AVG(ALL prod_price) AS avg_price
FROM products;

+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

// DISTINCT 關(guān)鍵字只會(huì)統(tǒng)計(jì)不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products;

+-----------+
| avg_price |
+-----------+
| 17.780833 |
+-----------+

12.3 組合聚集函數(shù)

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;

+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+

第13章分組數(shù)據(jù)

13.2 創(chuàng)建分組

猜測(cè)執(zhí)行順序?yàn)椋?/p>

FROM products選擇表
GROUP BY vend_id把表分成4組
對(duì)每組進(jìn)行SELECT vend_id, COUNT(*) AS num_prods操作

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

// 使用 WITH ROLLUP 關(guān)鍵字可以再對(duì)分組結(jié)果進(jìn)行匯總
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+

13.3 過濾分組

WHERE操作的對(duì)象是行，如要操作分組需要使用HAVING關(guān)鍵字

// 找出具有2個(gè)以上訂單的客戶
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING orders >= 2;

+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

// 找出至少有2件價(jià)格不低于10的產(chǎn)品的供應(yīng)商
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

13.4 分組和排序

// 檢索總計(jì)訂單價(jià)格大于等于50的訂 單的訂單號(hào)和總計(jì)訂單價(jià)格
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50;

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+

// 進(jìn)行排序
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING ordertotal >= 50
ORDER BY ordertotal;

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+

13.5 SELECT子句順序

子句	說明
SELECT	要返回的列或者表達(dá)式
FROM	從中檢索數(shù)據(jù)的表
WHERE	行級(jí)過濾
GROUP BY	分組說明
HAVING	組級(jí)過濾
ORDER BY	輸出排序順序
LIMIT	要檢索的行數(shù)

第14章使用子查詢

要查詢所有購(gòu)買過TNT2的用戶的信息

檢索包含物品TNT2的所有訂單的編號(hào)。
檢索具有前一步驟列出的訂單編號(hào)的所有客戶的ID。
檢索前一步驟返回的所有客戶ID的客戶信息。

SELECT order_num 
FROM orderitems
WHERE prod_id = 'TNT2';

+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

SELECT cust_id 
FROM orders
WHERE order_num IN (20005, 20007);

+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+


SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (10001, 10004);

+---------+----------------+
| cust_id | cust_name      |
+---------+----------------+
|   10001 | Coyote Inc.    |
|   10004 | Yosemite Place |
+---------+----------------+

拼接寫法

SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (SELECT cust_id 
                  FROM orders 
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'TNT2'));
                                      
+---------+----------------+
| cust_id | cust_name      |
+---------+----------------+
|   10001 | Coyote Inc.    |
|   10004 | Yosemite Place |
+---------+----------------+

計(jì)算每個(gè)客戶的訂單數(shù)目

SELECT cust_id, 
       cust_name,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_id;

+---------+----------------+--------+
| cust_id | cust_name      | orders |
+---------+----------------+--------+
|   10001 | Coyote Inc.    |      2 |
|   10002 | Mouse House    |      0 |
|   10003 | Wascals        |      1 |
|   10004 | Yosemite Place |      1 |
|   10005 | E Fudd         |      1 |
+---------+----------------+--------+

WHERE orders.cust_id = customers.cust_id
可改為
WHERE cust_id = customers.cust_id
結(jié)果相同
表名的獲取遵從就近原則

第15章聯(lián)結(jié)表

15.2 創(chuàng)建聯(lián)結(jié)

// 找出所有的產(chǎn)品并標(biāo)注出他們的生產(chǎn)商
SELECT   vend_name, prod_name, prod_price
FROM     vendors, products
WHERE    vendors.vend_id = products.vend_id // 當(dāng)出現(xiàn)二異性時(shí)，必須使用完整列名
ORDER BY vend_name, prod_name;

+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+

猜測(cè)：如果FROM存在多張表就是對(duì)多張表的笛卡爾積進(jìn)行查詢。若vendors表中出現(xiàn)因?yàn)檩斎脲e(cuò)誤造成id重復(fù)的話則上述結(jié)果會(huì)出現(xiàn)同一件產(chǎn)品出現(xiàn)2次的情況。

進(jìn)行如下測(cè)試

// 這樣寫會(huì)報(bào)錯(cuò)因?yàn)関end_id存在二義性
SELECT   vend_name, prod_name, prod_name, vend_id
FROM     vendors, products
ORDER BY vend_name, prod_name;

// 輸出笛卡爾積
SELECT   vend_name, prod_name, prod_name
FROM     vendors, products
ORDER BY vend_name, prod_name;
+----------------+----------------+----------------+
| vend_name      | prod_name      | prod_name      |
+----------------+----------------+----------------+
| ACME           | .5 ton anvil   | .5 ton anvil   |
| ACME           | 1 ton anvil    | 1 ton anvil    |
| ACME           | 2 ton anvil    | 2 ton anvil    |
| ACME           | Bird seed      | Bird seed      |
| ACME           | Carrots        | Carrots        |
| ACME           | Detonator      | Detonator      |
| ACME           | Fuses          | Fuses          |
| ACME           | JetPack 1000   | JetPack 1000   |
| ACME           | JetPack 2000   | JetPack 2000   |
| ACME           | Oil can        | Oil can        |
| ACME           | Safe           | Safe           |
| ACME           | Sling          | Sling          |
| ACME           | TNT (1 stick)  | TNT (1 stick)  |
| ACME           | TNT (5 sticks) | TNT (5 sticks) |
| Anvils R Us    | .5 ton anvil   | .5 ton anvil   |
| Anvils R Us    | 1 ton anvil    | 1 ton anvil    |
| Anvils R Us    | 2 ton anvil    | 2 ton anvil    |
| Anvils R Us    | Bird seed      | Bird seed      |
| Anvils R Us    | Carrots        | Carrots        |
| Anvils R Us    | Detonator      | Detonator      |
| Anvils R Us    | Fuses          | Fuses          |
| Anvils R Us    | JetPack 1000   | JetPack 1000   |
| Anvils R Us    | JetPack 2000   | JetPack 2000   |
| Anvils R Us    | Oil can        | Oil can        |
| Anvils R Us    | Safe           | Safe           |
| Anvils R Us    | Sling          | Sling          |
| Anvils R Us    | TNT (1 stick)  | TNT (1 stick)  |
| Anvils R Us    | TNT (5 sticks) | TNT (5 sticks) |
| Furball Inc.   | .5 ton anvil   | .5 ton anvil   |
| Furball Inc.   | 1 ton anvil    | 1 ton anvil    |
| Furball Inc.   | 2 ton anvil    | 2 ton anvil    |
| Furball Inc.   | Bird seed      | Bird seed      |
| Furball Inc.   | Carrots        | Carrots        |
| Furball Inc.   | Detonator      | Detonator      |
| Furball Inc.   | Fuses          | Fuses          |
| Furball Inc.   | JetPack 1000   | JetPack 1000   |
| Furball Inc.   | JetPack 2000   | JetPack 2000   |
| Furball Inc.   | Oil can        | Oil can        |
| Furball Inc.   | Safe           | Safe           |
| Furball Inc.   | Sling          | Sling          |
| Furball Inc.   | TNT (1 stick)  | TNT (1 stick)  |
| Furball Inc.   | TNT (5 sticks) | TNT (5 sticks) |
| Jet Set        | .5 ton anvil   | .5 ton anvil   |
| Jet Set        | 1 ton anvil    | 1 ton anvil    |
| Jet Set        | 2 ton anvil    | 2 ton anvil    |
| Jet Set        | Bird seed      | Bird seed      |
| Jet Set        | Carrots        | Carrots        |
| Jet Set        | Detonator      | Detonator      |
| Jet Set        | Fuses          | Fuses          |
| Jet Set        | JetPack 1000   | JetPack 1000   |
| Jet Set        | JetPack 2000   | JetPack 2000   |
| Jet Set        | Oil can        | Oil can        |
| Jet Set        | Safe           | Safe           |
| Jet Set        | Sling          | Sling          |
| Jet Set        | TNT (1 stick)  | TNT (1 stick)  |
| Jet Set        | TNT (5 sticks) | TNT (5 sticks) |
| Jouets Et Ours | .5 ton anvil   | .5 ton anvil   |
| Jouets Et Ours | 1 ton anvil    | 1 ton anvil    |
| Jouets Et Ours | 2 ton anvil    | 2 ton anvil    |
| Jouets Et Ours | Bird seed      | Bird seed      |
| Jouets Et Ours | Carrots        | Carrots        |
| Jouets Et Ours | Detonator      | Detonator      |
| Jouets Et Ours | Fuses          | Fuses          |
| Jouets Et Ours | JetPack 1000   | JetPack 1000   |
| Jouets Et Ours | JetPack 2000   | JetPack 2000   |
| Jouets Et Ours | Oil can        | Oil can        |
| Jouets Et Ours | Safe           | Safe           |
| Jouets Et Ours | Sling          | Sling          |
| Jouets Et Ours | TNT (1 stick)  | TNT (1 stick)  |
| Jouets Et Ours | TNT (5 sticks) | TNT (5 sticks) |
| LT Supplies    | .5 ton anvil   | .5 ton anvil   |
| LT Supplies    | 1 ton anvil    | 1 ton anvil    |
| LT Supplies    | 2 ton anvil    | 2 ton anvil    |
| LT Supplies    | Bird seed      | Bird seed      |
| LT Supplies    | Carrots        | Carrots        |
| LT Supplies    | Detonator      | Detonator      |
| LT Supplies    | Fuses          | Fuses          |
| LT Supplies    | JetPack 1000   | JetPack 1000   |
| LT Supplies    | JetPack 2000   | JetPack 2000   |
| LT Supplies    | Oil can        | Oil can        |
| LT Supplies    | Safe           | Safe           |
| LT Supplies    | Sling          | Sling          |
| LT Supplies    | TNT (1 stick)  | TNT (1 stick)  |
| LT Supplies    | TNT (5 sticks) | TNT (5 sticks) |
+----------------+----------------+----------------+

15.2.2 內(nèi)部聯(lián)結(jié)

使用 INNER JOIN 和ON組成內(nèi)部聯(lián)結(jié)。這種寫法,此種寫法與WHERE一致

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
| ACME        | Detonator      |      13.00 |
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
+-------------+----------------+------------+

15.2.3 聯(lián)結(jié)多個(gè)表

// 查找訂單20005里面的商品和其供應(yīng)商的信息。
SELECT order_num, prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
      AND orderitems.prod_id = products.prod_id
      AND order_num = 20005;
      
+-----------+----------------+-------------+------------+----------+
| order_num | prod_name      | vend_name   | prod_price | quantity |
+-----------+----------------+-------------+------------+----------+
|     20005 | .5 ton anvil   | Anvils R Us |       5.99 |       10 |
|     20005 | 1 ton anvil    | Anvils R Us |       9.99 |        3 |
|     20005 | TNT (5 sticks) | ACME        |      10.00 |        5 |
|     20005 | Bird seed      | ACME        |      10.00 |        1 |
+-----------+----------------+-------------+------------+----------+

第16章創(chuàng)建高級(jí)聯(lián)結(jié)

16.1 使用表別名

1.可以縮短sql語(yǔ)句

SELECT cust_name, cust_contact
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 = 'TNT2';

2.可以對(duì)同一張表引用多次（在products表中查詢出‘DTNTR’的生產(chǎn)商的所有產(chǎn)品）

// 不使用表別名
SELECT prod_id, prod_name, vend_id
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR')
                 
// 使用表別名
SELECT p1.prod_id, p1.prod_name, p1.vend_id
FROM products as p1, products as p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR'

+---------+----------------+---------+
| prod_id | prod_name      | vend_id |
+---------+----------------+---------+
| DTNTR   | Detonator      |    1003 |
| FB      | Bird seed      |    1003 |
| FC      | Carrots        |    1003 |
| SAFE    | Safe           |    1003 |
| SLING   | Sling          |    1003 |
| TNT1    | TNT (1 stick)  |    1003 |
| TNT2    | TNT (5 sticks) |    1003 |
+---------+----------------+---------+

16.2 使用不同類型的聯(lián)結(jié)

16.2.1 自聯(lián)結(jié)

// 找出和DTNTR相同生產(chǎn)商的產(chǎn)品
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';
            
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

16.2.3 外部聯(lián)結(jié)

關(guān)于內(nèi)部聯(lián)結(jié)與外部聯(lián)結(jié)的區(qū)別

內(nèi)部聯(lián)結(jié) = A ∩ B

外部聯(lián)結(jié) = X ∪ (A ∩ B) = X X為被LEFT 或 RIGHT 標(biāo)記的表

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

16.3 使用待聚集函數(shù)的聯(lián)結(jié)

// 計(jì)算所有已購(gòu)買過產(chǎn)品的用戶的 購(gòu)買量
SELECT customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

// 計(jì)算所有用戶的購(gòu)買量
SELECT customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

第17章組合查詢

17.1 組合查詢

組合查詢用以返回多種匹配結(jié)果的并集

17.2 創(chuàng)建組合查詢

17.2.1 使用UNION

// 查找出價(jià)格小于等于5，以及供應(yīng)商為1001和1002的所有物品
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);

// 結(jié)果同上
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5 OR vend_id IN(1001, 1002);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

17.2.2 UNION規(guī)則

UNION用來分割SELECT語(yǔ)句
每個(gè)查詢必須包含相同的列、表達(dá)式或聚集函數(shù)
列數(shù)據(jù)類型必須兼容:類型不必完全相同，但必須是DBMS可以

隱含地轉(zhuǎn)換的類型(例如，不同的數(shù)值類型或不同的日期類型)

17.2.3 包含或取消重復(fù)的行

如果多個(gè)SELECT匹配到了同一行,UNION默認(rèn)會(huì)去除重復(fù)行。

如果允許重復(fù)出現(xiàn)則用UNION ALL代替UNION。

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);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

17.2.4 對(duì)組合查詢結(jié)果排序

排序語(yǔ)句出現(xiàn)在最后一條SELECT語(yǔ)句之后

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;

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | FC      |       2.50 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+

第18章全文本搜索

18.2.2 進(jìn)行全文本搜索

// 兩種語(yǔ)句效果相同

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('rabbit');

SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

18.2.3 使用查詢擴(kuò)展

在使用文本搜索查詢到結(jié)果之后，在用結(jié)果內(nèi)的詞進(jìn)行查詢

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);

Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.

Customer complaint:Sticks not individually wrapped, too easy to mistakenly detonate all at once.Recommend individual wrapping.Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
Please note that no returns will be accepted if safe opened using explosives.
Customer complaint: rabbit has been able to detect trap, food apparently less effective now.

Customer complaint:Circular hole in safe floor can apparently be easily cut with handsaw.Matches not included, recommend purchase of matches or detonator (item DTNTR).

18.2.4 布爾文本搜索

MySQL支持全文本搜索的另外一種形式，稱為布爾方式(boolean

mode)。以布爾方式，可以提供關(guān)于如下內(nèi)容的細(xì)節(jié):

要匹配的詞;
要排斥的詞(如果某行包含這個(gè)詞，則不返回該行，即使它包含其他指定的詞也是如此);
排列提示(指定某些詞比其他詞更重要，更重要的詞等級(jí)更高);
表達(dá)式分組;
另外一些內(nèi)容。

// 匹配包含'heavy'的行
SELECT onte_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEN MODE);

Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.

Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.

// 匹配包含'heavy'且不包含'rope*'的行
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.

全文本布爾操作符

布爾操作符	說明
N/A(無符號(hào))	包含，詞必須存在
+	包含，詞必須存在
-	排除，次必須不出現(xiàn)
>	包含，且增加等級(jí)值
<	包含，且減少等級(jí)值
()	把詞組成表達(dá)式
~	取消一個(gè)詞的排序值
*	詞尾通配符
""	定義一個(gè)短語(yǔ)

舉例操作

// 搜索包含rabbit 和 bait的行
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

// 搜索包含rabbit和bait中至少一個(gè)的行
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('rabbit bait' IN BOOLEAN MODE);

// 搜索包含rabbit bait的行
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

// 匹配rabbit和carrot，增加前者等級(jí)，降低后者等級(jí)
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);

// 搜索safe和combination，降低后者等級(jí)
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

18.2.5 全文本搜索的使用說明

在索引全文本數(shù)據(jù)時(shí)，短詞被忽略且從索引中排除。短詞定義為那些具有3個(gè)或3個(gè)以下字符的詞(如果需要，這個(gè)數(shù)目可以更改)。
MySQL帶有一個(gè)內(nèi)建的非用詞(stopword)列表，這些詞在索引全文本數(shù)據(jù)時(shí)總是被忽略。如果需要，可以覆蓋這個(gè)列表(請(qǐng)參閱MySQL文檔以了解如何完成此工作)。
許多詞出現(xiàn)的頻率很高，搜索它們沒有用處(返回太多的結(jié)果)。因此，MySQL規(guī)定了一條50%規(guī)則，如果一個(gè)詞出現(xiàn)在50%以上的行中，則將它作為一個(gè)非用詞忽略。50%規(guī)則不用于IN BOOLEAN MODE。
如果表中的行數(shù)少于3行，則全文本搜索不返回結(jié)果(因?yàn)槊總€(gè)詞或者不出現(xiàn)，或者至少出現(xiàn)在50%的行中)。
忽略詞中的單引號(hào)。例如，don't索引為dont。
不具有詞分隔符(包括日語(yǔ)和漢語(yǔ))的語(yǔ)言不能恰當(dāng)?shù)胤祷厝谋舅阉鹘Y(jié)果。
如前所述，僅在MyISAM數(shù)據(jù)庫(kù)引擎中支持全文本搜索。

第19章插入數(shù)據(jù)

19.1 數(shù)據(jù)插入

使用INSERT插入(或添加)行到數(shù)據(jù)庫(kù)表中。有以下幾種插入方式。

插入完整行
插入行的一部分
插入多行
插入某些查詢的結(jié)果

19.2 插入完整的行

key和nullable==False的Column可以省略

// 不安全的寫法(嚴(yán)重依賴Column的順序)
INSERT INTO Customers
VALUES(NULL, 
       'Pep E. LaPew',
       '100 Main Street', 
       'Log Angeles', 
       'CA', 
       '90046', 
       'USA', 
       NULL, 
       NULL)
       
// 更安全的寫法，同時(shí)更繁瑣的寫法
INSERT INTO customers(cust_name, 
                      cust_address, 
                      cust_city, 
                      cust_state, 
                      cust_zip, 
                      cust_country, 
                      cust_contact, 
                      cust_email)
VALUES('Pep E. LaPew',
       '100 Main Street', 
       'Log Angeles', 
       'CA', 
       '90046', 
       'USA', 
       NULL, 
       NULL)

19.3 插入多個(gè)行

INSERT INTO customers(cust_name, 
                      cust_address, 
                      cust_city, 
                      cust_state, 
                      cust_zip, 
                      cust_country)
VALUES('Pep E. LaPew',
       '100 Main Street', 
       'Log Angeles', 
       'CA', 
       '90046', 
       'USA'),
      ('M. Martian', 
       '42 Galaxy Way', 
       'New York', 
       'NY', 
       '11213', 
       'USA');

19.4 插入檢索出的數(shù)據(jù)

INSERT INTO customers(cust_id, 
                      cust_contact, 
                      cust_email, 
                      cust_name, 
                      cust_address, 
                      cust_city, 
                      cust_state, 
                      cust_zip, 
                      cust_country)
SELECT cust_id, 
       cust_contact, 
       cust_email, 
       cust_name, 
       cust_address, 
       cust_city, 
       cust_state, 
       cust_zip, 
       cust_country
FROM custnew;

第20章更新和刪除數(shù)據(jù)

20.1 更新數(shù)據(jù)

為了更新(修改)表中的數(shù)據(jù)，可使用UPDATE語(yǔ)句?？刹捎脙煞N方式使用UPDATE:

更新表中特定行
更新表中所有行。

// 更新單列
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

// 更新多列
UPDATE customers
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

20.2 刪除數(shù)據(jù)

為了從一個(gè)表中刪除(去掉)數(shù)據(jù)，使用DELETE語(yǔ)句?？梢詢煞N方式使用DELETE:

從表中刪除特定的行;
從表中刪除所有行。

DELETE FROM customers
WHERE cust_id = 10006;

第21章創(chuàng)建和操縱表

21.1 創(chuàng)建表

MySQL不僅用于表數(shù)據(jù)操縱，而且還可以用來執(zhí)行數(shù)據(jù)庫(kù)和表的所有操作，包括表本身的創(chuàng)建和處理。一般有兩種創(chuàng)建表的方法:

使用具有交互式創(chuàng)建和管理表的工具(如第2章討論的工具);
表也可以直接用MySQL語(yǔ)句操縱。

21.1.1 表創(chuàng)建基礎(chǔ)

為利用CREATE TABLE創(chuàng)建表，必須給出下列信息:

新表的名字，在關(guān)鍵字CREATE TABLE之后給出;
表列的名字和定義，用逗號(hào)分隔。

CREATE TABLE custnew(
cust_id         int         NOT NULL AUTO_INCREMENT,
cust_name       char(50)    NOT NULL,
cust_address    char(50)    NULL,
cust_city       char(50)    NULL,
cust_state      char(5)     NULL,
cust_zip        char(10)    NULL,
cust_country    char(50)    NULL,
cust_email      char(50)    NULL,
PRIMARY KEY (cust_id)
)ENGINE=InnoDB;

21.1.3 主鍵再介紹

主鍵可以是多個(gè)Colomn的組合

CREATE TABLE ordernew
(
    order_num       int         NOT NULL,
    order_item      int         NOT NULL,
    prod_id         char(10)    NOT NULL,
    quantity        int         NOT NULL,
    item_price decimal(8,2)     NOT NULL,
    PRIMARY KEY (order_num, order_item)
)ENGINE=INNODB

21.1.4 使用AUTO_INCREMENT

一般用來實(shí)現(xiàn)主鍵自增

21.1.5 指定默認(rèn)值

用DEFAULT

CREATE TABLE ordernew
(
    order_num       int         NOT NULL,
    order_item      int         NOT NULL,
    prod_id         char(10)    NOT NULL,
    quantity        int         NOT NULL DEFAULT 1,
    item_price decimal(8,2)     NOT NULL,
    PRIMARY KEY (order_num, order_item)
)ENGINE=INNODB

21.1.6 引擎類型

以下是幾個(gè)需要知道的引擎:

InnoDB是一個(gè)可靠的事務(wù)處理引擎,它不支持全文本搜索;
MEMORY在功能等同于MyISAM，但由于數(shù)據(jù)存儲(chǔ)在內(nèi)存(不是磁盤) 中，速度很快(特別適合于臨時(shí)表);
MyISAM是一個(gè)性能極高的引擎，它支持全文本搜索(參見第18章)，但不支持事務(wù)處理。

21.2 更新表

ALTER TABLE關(guān)鍵字用來更新表

// 增加vend_phone列
ALTER TABLE vendors
ADD vend_phone CHAR(20);

// 刪除vend_phone列
ALTER TABLE vendors
DROP COLUMN vend_phone

21.3 刪除表

// 刪除整張表
DROP TABLE customer;

21.4 重命名表

RENAME TABLE customers TO backup_customers;

第22章使用視圖

22.1 視圖

視圖是虛擬的表。與包含數(shù)據(jù)的表不一樣，視圖只包含使用時(shí)動(dòng)態(tài)
檢索數(shù)據(jù)的查詢。

可以把查詢結(jié)果包裝成一個(gè)視圖,然后對(duì)視圖進(jìn)行表操作.

22.2 使用視圖

視圖用CREATE VIEW語(yǔ)句來創(chuàng)建。
使用SHOW CREATE VIEW viewname;來查看創(chuàng)建視圖的語(yǔ)句。
用DROP刪除視圖，其語(yǔ)法為DROP VIEW viewname;。
更新視圖時(shí)，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的視圖不存在，則第2條更新語(yǔ)句會(huì)創(chuàng) 建一個(gè)視圖;如果要更新的視圖存在，則第2條更新語(yǔ)句會(huì)替換原有視圖。

22.2.1 使用視圖簡(jiǎn)化復(fù)雜的聯(lián)結(jié)

// 創(chuàng)建productcustomers表
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

// 從productcustomers表中查詢數(shù)據(jù)
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

22.2.2 用視圖重新格式化檢索出的數(shù)據(jù)

// 原始代碼
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

// 使用視圖代替
CREATE VIEW vendorlocations AS
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT *
FROM vendorlocations;

+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+

22.2.3 用視圖過濾不想要的數(shù)據(jù)

CREATE VIEW customermaillist AS 
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

SELECT *
FROM customermaillist;

22.2.4 使用視圖與計(jì)算字段

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
|     20005 | ANV01   |       10 |       5.99 |          59.90 |
|     20005 | ANV02   |        3 |       9.99 |          29.97 |
|     20005 | TNT2    |        5 |      10.00 |          50.00 |
|     20005 | FB      |        1 |      10.00 |          10.00 |
+-----------+---------+----------+------------+----------------+

第23章使用存儲(chǔ)過程

23.3.2 創(chuàng)建存儲(chǔ)過程

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

CALL productpricing();

+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+

23.3.3 刪除存儲(chǔ)過程

DROP PROCEDURE productpricing;

23.3.4 使用參數(shù)

OUT類似引用傳遞

IN類似值傳遞

/** 獲取產(chǎn)品的最低價(jià),最高價(jià),和平均價(jià) **/

CREATE PROCEDURE productpricing1(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT MIN(prod_price)
    INTO pl
    FROM products;
    SELECT MAX(prod_price)
    INTO ph
    FROM products;
    SELECT AVG(prod_price)
    INTO pa
    FROM products;
END;



CALL productpricing1(@pricelow, @pricehigh, @priceaverage)


SELECT @pricelow;               // 2.50
SELECT @pricehigh;              // 55.00
SELECT @priceaverage;           // 16.13

SELECT @pricelow, @pricehigh, @priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+


/** 計(jì)算某個(gè)訂單的總價(jià) **/
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT SUM(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;
END;



CALL ordertotal(20005, @total);

SELECT @total;

+--------+
| @total |
+--------+
| 149.87 |
+--------+

23.3.5 建立智能存儲(chǔ)過程

即邏輯判斷

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)

BEGIN 
    DECLARE total DECIMAL(8,2);
    DECLARE taxrate INT DEFAULT 6;
    
    SELECT SUM(item_price * quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;
    
    IF taxable THEN
        SELECT total+(total/100*taxrate) INTO total;
    END IF;
    
    SELECT total INTO ototal;
    
END
    

CALL ordertotal(20005, 0, @total);
SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+

CALL ordertotal(20005, 1, @total);
SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+

第24章使用游標(biāo)

24.1 游標(biāo)

用來對(duì)SELECT結(jié)果進(jìn)行分頁(yè)

24.2 使用游標(biāo)

DECLARE命名游標(biāo)

OPEN打開游標(biāo)

COLSE關(guān)閉游標(biāo)

FETCH用游標(biāo)檢索數(shù)據(jù)

第25章使用觸發(fā)器

25.1 觸發(fā)器

可以給表添加觸發(fā)器,在DELETE,INSERT,UPDATE語(yǔ)句執(zhí)行時(shí)觸發(fā)某些操作.

第26張管理事務(wù)處理

26.1 事務(wù)處理

在使用事務(wù)和事務(wù)處理時(shí)，有幾個(gè)關(guān)鍵詞匯反復(fù)出現(xiàn)。下面是關(guān)于事務(wù)處理需要知道的幾個(gè)術(shù)語(yǔ):

事務(wù)(transaction)指一組SQL語(yǔ)句;
回退(rollback)指撤銷指定SQL語(yǔ)句的過程;
提交(commit)指將未存儲(chǔ)的SQL語(yǔ)句結(jié)果寫入數(shù)據(jù)庫(kù)表;
保留點(diǎn)(savepoint)指事務(wù)處理中設(shè)置的臨時(shí)占位符(place-holder)，你可以對(duì)它發(fā)布回退(與回退整個(gè)事務(wù)處理不同)。

26.2 控制事務(wù)處理

STAR TRANSACTION來標(biāo)記事務(wù)的開始

26.2.1 使用ROLLBACK

ROLLBACK來撤銷INSERT,UPDATE,DELETE語(yǔ)句

SELECT * FROM orderitems;
START TRANSACTION;
DELETE FROM orderitems;
SELECT * FROM orderitems;

ROLLBACK;
SELECT * FROM orderitems;

26.2.2 使用COMMIT

COMMIT用來提交操作,只有在START和COMMIT之間的語(yǔ)句均不報(bào)錯(cuò).這之間的語(yǔ)句才會(huì)提交.

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

26.2.3 使用保留點(diǎn)

SAVEPOINT delete1;
ROLLBACK TO delete1;

26.2.4 更改默認(rèn)的提交行為

SET autocommit = 0;

第28章安全管理

pass

第29章數(shù)據(jù)庫(kù)維護(hù)

pass

第30章改善性能

pass

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

《MySQL必知必會(huì)》學(xué)習(xí)筆記

第1章 了解MySql

1.1 數(shù)據(jù)庫(kù)基礎(chǔ)

1.1.1 什么是數(shù)據(jù)庫(kù)

1.1.2 表

1.1.3 列和數(shù)據(jù)類型

1.1.4 行

1.1.5 主鍵

1.2 什么是SQL

第2章 MySQL簡(jiǎn)介

2.1 什么是MySQL

2.1.1 客戶機(jī)—服務(wù)器軟件

2.1.2 MySQL版本

2.2 MySQL工具

第3章 使用MySQL

3.1 鏈接MySQL

第4章 檢索數(shù)據(jù)

4.1 SELECT語(yǔ)句

4.2 檢索單個(gè)列

4.3 檢索多個(gè)列

4.4 檢索所有列

4.5 檢索不同的行

4.6 限制結(jié)果

4.7 使用完全限定的表名

第5章 排序檢索數(shù)據(jù)

5.1 排序數(shù)據(jù)

5.3 指定排序方向

第6章 過濾數(shù)據(jù)

6.1 使用WHERE子句

第7章 數(shù)據(jù)過濾

7.1 組合WHERE語(yǔ)句

7.2 IN操作符

7.3 NOT操作符

第8章 用通配符進(jìn)行過濾

8.1 LIKE操作符

8.1.1 百分號(hào)（%）通配符

8.1.2 下劃線（_）通配符

8.2 使用通配符的技巧

第9章 用正則表達(dá)式進(jìn)行搜索

9.2 使用MySQL正則表達(dá)式

9.2.1 基本字符匹配

9.2.2 進(jìn)行OR匹配

9.2.3 匹配幾個(gè)字符之一

9.2.4 匹配范圍

9.2.5 匹配特殊字符

9.2.6 匹配字符表

9.2.7 匹配多個(gè)實(shí)例

9.2.8 定位符

第10章 創(chuàng)建計(jì)算字段

10.1 計(jì)算字段

10.2 拼接字段

10.3 執(zhí)行算術(shù)計(jì)算

第11章 使用數(shù)據(jù)處理函數(shù)

11.2.1 文本處理函數(shù)

11.2.2 日期和時(shí)間處理函數(shù)

11.2.3 數(shù)值處理函數(shù)

第12章 匯總數(shù)據(jù)

12.1 聚集函數(shù)

12.1.3 MAX()函數(shù)

12.1.4 MIN()函數(shù)

12.1.5 SUM()函數(shù)

12.2 聚焦不同值

12.3 組合聚集函數(shù)

第13章 分組數(shù)據(jù)

13.2 創(chuàng)建分組

13.3 過濾分組

13.4 分組和排序

13.5 SELECT子句順序

第14章 使用子查詢

第15章 聯(lián)結(jié)表

15.2 創(chuàng)建聯(lián)結(jié)

15.2.2 內(nèi)部聯(lián)結(jié)

15.2.3 聯(lián)結(jié)多個(gè)表

第16章 創(chuàng)建高級(jí)聯(lián)結(jié)

16.1 使用表別名

16.2 使用不同類型的聯(lián)結(jié)

16.2.1 自聯(lián)結(jié)

16.2.3 外部聯(lián)結(jié)

16.3 使用待聚集函數(shù)的聯(lián)結(jié)

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

第1章了解MySql

第3章使用MySQL

第4章檢索數(shù)據(jù)

第5章排序檢索數(shù)據(jù)

第6章過濾數(shù)據(jù)

第7章數(shù)據(jù)過濾

第8章用通配符進(jìn)行過濾

第9章用正則表達(dá)式進(jìn)行搜索

第10章創(chuàng)建計(jì)算字段

第11章使用數(shù)據(jù)處理函數(shù)

第12章匯總數(shù)據(jù)

第13章分組數(shù)據(jù)

第14章使用子查詢

第15章聯(lián)結(jié)表

第16章創(chuàng)建高級(jí)聯(lián)結(jié)

第17章組合查詢

第18章全文本搜索

第19章插入數(shù)據(jù)

第20章更新和刪除數(shù)據(jù)

第21章創(chuàng)建和操縱表

第22章使用視圖

第23章使用存儲(chǔ)過程

第24章使用游標(biāo)

第25章使用觸發(fā)器

第26張管理事務(wù)處理

第28章安全管理

第29章數(shù)據(jù)庫(kù)維護(hù)

第30章改善性能