MySQL

最新推荐文章于 2023-04-28 09:05:17 发布

paradise smile

最新推荐文章于 2023-04-28 09:05:17 发布

阅读量1.4k

点赞数 3

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_46557333/article/details/122076002

版权

第1课了解SQL

1.数据库(database)：

数据库是以某种组织的方式存储的数据集合，就相当于一个文件柜。

2.数据库软件(DBMS)：数据库管理系统

数据库是通过DBMS创建和操纵的容器

3.表：可以用来存储某种特定类型的数据。存储在表中的数据是同一种类型的数据或清单。

表中的名字是唯一的。

4.模式:关于数据库和表的布局及特性的信息。

5.表由列组成

6.数据类型的作用：

（1）数据类型限定了可存储在列中的数据种类

（2）数据类型还帮助正确的分类数据，并在优化磁盘使用方面起重要作用。

7.表中的任何列都可以作为主键，需要满足的条件：
（1）任意两行都不具有相同的主键值

（2）每一行都必选具有一个主键值(主键列不允许为控制NULL)

（3）主键列中的值不允许修改或更新

（4）主键值不能重复使用（如果某行从表中删除，他的主键不能赋给以后的新行）

最好的方式就是动手实践。

第2课检索数据

Select语句：

它的用途是从一个或多个表中检索信息。

2.1检索单个列：

SELECT prod_name FROM Products;

2.2检索多个列：

就是在检索多个列的基础上，在列名之间加上逗号。

2.3检索所有列：

select * from 表名。

2.4检索不同的值：

关键字distinct 不同的 Select distinct 列名 from 表名

Distinct 关键字作用于所有列，不仅仅是跟在其后的那一列。

2.5限制结果：

MySQL:需要使用limit子句。

Select prod_name from products limit 5;

Select prod_name from products limit 5 offset 5;

Limit指定返会的行数 Offset指定从哪开始

第1个被检索的行是第0行，而不是第一行。

注释：注释使用–（两个连字符）嵌在行内

也可以使用（#）但这种形式有些DBMS不支持

第3课排序检索数据

3.1排序数据

子句:

SQL语句由子句构成，有些子句时必须的，有些子句是可选的，一个子句通常由一个关键字加上所提供的数据组成

order by 子句的位置：

在指定一条order by子句时，应该保证它是select语句中最后一条子句。如果不是最后的子句，将会出错

3.2按多个列排序

要按多个列排血，只须指定这些列名，列名之间用逗号分开即可

select prod_id,prod_price,price_name 
from Products
ORDER BY prod_price,prod_name;

3.3 按照位置排序

order by不仅支持多个列排序，还支持按相对位置进行排序

select prod_id,prod_price,prod_name 
from products
order by 2,3;

3.4指定排序方向

数据排序默认为升序，如果要进行降序排序，必须指定DESC关键字

SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price DESC;

打算用多个列排序

SELECT prod_id, prod_price, prod_name 
FROM Products
ORDER BY prod_price DESC, prod_name;

DESC关键字只对应到直接位于其前面的列名

如果想在多个列上进行降序排序，必须对每一列指定DCES关键字

第4课过滤数据

4.1 使用where子句

在select语句中，数据根据where子句中指定的搜索条件进行过滤

SELECT prod_name, prod_price 
FROM Products 
WHERE prod_price = 3.49;

where子句的位置

在同时使用ORDER BY 和WHERE子句的同时，应让ORDER BY 子句位于WHERE子句之后。

4.2 WHERE 子句操作符

操作符说明

= 等于 > 大于

<> 不等于 >= 大于等于

！= 不等于！> 不大于

< 小于 <= 小于等于

！< 不小于 BETWEEN 指定的两个值之间

IS NULL 为NULL值

4.2.1 检查单个值

SELECT prod_name,prod_price 
FROM Products
WHERE prod_pirce < 10;

SELECT prod_name,prod_price 
FROM Products
WHERE prod_pirce <= 10;

4.2.2不匹配检查

SELECT vend_id,prod_name
FROM Products 
WHERE vend_id <> 'DLL01';

单引号用来限定字符串

如果将值与字符串类型的列进行比较，就需要限定引号

用来与数值列进行比较就不需要使用引号

SELECT vend_id,prod_name
FROM Products 
WHERE vend_id != 'DLL01';

4.2.3范围值检查

要检查某个范围的值，可以使用BETWEEN操作符

SELECT prod_name, prod_pirce
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。

4.2.4 空值检查

NULL:无值，它与字段包含0、空字符串或仅仅包含空格不同

WHERE子句中的IS NULL子句可用来检查具有NULL值的列。

SELECT prod_name 
FROM Procducts
WHERE prod_price IS NULL;

第5课高级数据过滤

5.1组合WHERE子句

SQL允许使用多个WHERE子句，即以AND子句或者OR子句的方式使用

操作符：
用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符

5.1.1 AND操作符

and操作符通过不止一个列进行过滤

SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

AND:用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行

可以增加多个过滤条件，每个条件间都要使用AND关键字

5.1.2 OR操作符

它指示DBMS检索匹配任一条件的行

在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来

SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

OR: WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。

5.1.3求值顺序

由于AND在求值过程中优先级更高

此问题的解决方法是使用圆括号对操作符进行明确的分组

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BR001')
	AND prod_price >= 10;

5.2 IN操作符

IN操作符用来指定条件范围，范围中的每一个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值.

SELECT prod_name, prod_price 
FROM Products
WHERE vend_id IN('DLL01','BRS01')
ORDER BY prod_name;

IN: WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当

IN操作符的优点:

IN操作符的语法更清楚，更直观
在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理
IN操作符一般比一组操作符执行得更快
IN的最大的优点是可以包含其他SELECT语句，能够更动态的建立WHERE子句

5.3 NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。

SELECT prod_name 
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

第6课用通配符进行过滤

6.1 LIKE操作符

利用通配符，可以创建比较特定数据的搜索模式

通配符：用来匹配值的一部分的特殊字符。

搜索模式：由字面值、通配符或者两者组合构成的搜索条件

为了在搜索子句中使用通配符，必须使用LIKE操作符。

LIKE操作符知识DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较

谓词：操作符何时不为操作符？它作为谓词的时候，LIKE是谓词而不是操作符

***通配符搜索只能用在文本字段(字符串)，非文本数据类型字符不能使用通配符搜索。

6.1.1 百分号(%)通配符

在搜索串中，%表示任何字符出现任意次数。

SELECT prod_id, prod_name 
FROM Products
WHERE prod_name LIKE 'Fish%';

区分大小写

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。

SELECT prod_id, prod_name 
FROM Products
WHERE prod_name LIKE '%bean bag%';

'%'除了能够匹配一个或多个字符串，还能匹配0个字符，但不能匹配NULL。

6.1.2 下划线(_)通配符

下划线（_）只匹配单个字符

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '% inch teddy bear';

6.2 使用通配符的技巧

不要过度使用通配符。如果其他操作符能达到相同的目的，用该使用其他操作符。
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。这样搜索起来是最慢的
仔细注意通配符的位置，如果放错地方，可能不会返回想要的数据。

第7课创建计算字段

7.1计算字段

存储在表中的数据都不是应用程序所需要的。

我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化

计算字段是运行时在SELECT语句内创建的

7.2拼接字段

此操作符可以用（+）或者两个竖杠（||）表示

在MySQL和MariaDB中，必须使用特殊的函数

SQL Sever 使用+号

DB2、Oracle、PostgreSQL和SQLite使用||

使用加号

SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;

使用MySQL或MariaDB是使用的语句

SELECT CONCAT(vend_name, '(',vend_country,')')
FROM Vendors
ORDER BY vend_name;

去除空格RTRIM()函数

TRIM函数

RTRIM函数:去除字符串右边的空格

LTRIM函数:去除字符串左边的空格

TRIM函数：去除左右两边的空格

SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') 
FROM Vendors
ORDER BY vend_name;

使用别名

别名是一个字段或值的替换名。别名用AS关键字赋予

SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') 
AS vend_title
FROM Vendors
ORDER BY vend_name;

7.3执行算术计算

计算字段的另一常见的用途就是对检索出的数据进行算术计算

SELECT prod_id,
	quantity,
    item_price,
    quantity * item_price AS expanded_price
FROM ORDERITEMS
WHERE order_num = 20008;

第8课使用函数处理数据

8.1 函数

SQL函数不是可移植的。

特定的SQL实现编写的代码在其他实现中可能不能用

8.2使用函数

大多数SQL实现支持以下类型的函数

用于处理文本字符串(如删除或填充值，转换值为大写或小写)的文本函数
用于在数值数据上进行算术操作(如返回绝对值，进行代数运算)的数值函数
用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差，检查日期有效性)的日期和时间函数
用于生成美观好懂的输出内容的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）
返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数

8.2.1 文本处理函数

常用的文本处理函数：

函数说明

**upper()**函数转化为大写

left() 返回字符串左边的字符

length() 返回字符串的长度

lower() 将字符串转为小写

LTRIM() 去掉字符串左边的空格

RIGHT() 返回字符串右边的字符

RTRIM() 去掉字符串右边的空格

SUBSTR()或SUBSTRING() 提取字符串的组成部分

SOUNDEX() 返回字符串的SOUNDEX值

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green')

发音类似的单词也会被检索

8.2.2 日期和时间处理函数

MySQL可以使用名为YEAR()的函数从日期中提取年份

SELECT order_num 
FROM Orders
WHERE YEAR(order_date) = 2020;

8.2.3 数值处理函数

函数说明

ABS() 返回一个数的绝对值

COS() 返回一个角度的余弦

EXP() 返回一个数的指数值

PI() 返回圆周率的值

SIN() 返回角度的正弦

SQRT() 返回一个数的平方根

TAN() 返回一个数的正切

第9课汇总数据

9.1 聚集函数

使用聚集函数，SQL查询可以用于检索数据，比如：

确定表中行数
获得表中某些行的和
找出表列的最大值、最小值、平均值

函数说明

AVG() 返回某列的平均值

COUNT() 返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

9.1.1 AVG()函数

AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值

SELECT AVG(prod_price) AS avg_price
FROM Products;

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

AVG()函数忽略列值为NULL的行

9.1.2 COUNT()函数

count()函数有两种使用方法：

使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

SELECT COUNT(*) AS num_cust
FROM Customers;

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

9.1.3 MAX()函数

MAX()返回指定列中的最大值。MAX()要求指定列名

SELECT MAX(prod_price) AS max_price
FROM Products;

MAX()函数忽略列值为NULL的行

9.1.4 MIN()函数

MIN()函数返回指定列的最小值

SELECT MIN(prod_price) AS min_price 
FROM Products;

MIN()函数忽略列值为NULL的行

9.1.5 SUM()函数

SUM()函数用来返回指定列值的和(总计)

SELECT SUM(quantity) AS item_ordered
FROM OrderItems
WHERE order_num = 20005;

SELECT SUM(item_price * quantity) AS total_price
FROM ORDERItems
WHERE order_num = 20005;

SUM()函数忽略列值为NULL的行

9.2 聚集不同值

对所有行执行计算，指定ALL参数或不指定参数(因为ALL是默认行)
只包含不同的值，指定DISTINCT参数

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

9.3组合聚集函数

SELECT语句可以根据需要包含多个聚集函数

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;

第10课分组数据

10.1数据分组

使用分组可以将数据分为多个逻辑组。对每个组进行聚集计算。

10.2创建分组

分组是使用SELECT语句的GROUP BY子句建立的

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

使用GROUP BY子句的一些规定：

GROUP BY 子句可以含任意数目的列，因而可以对分组进行嵌套，更细致的进行数据分组
如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。在建立分组时，指定的所有列都一起计算。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能别名。
大多数SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)
除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，他们将分为一组
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句中给出

10.3 过滤分组

WHERE过滤行，而HAVING过滤分组

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

过滤是基于分组聚集值，而不是特定行的值

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

WHERE 和HAVING子句可以同时使用

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

10.4 分组和排序

order by GROUP BY

对产生的输出排序对行分组，但输出可能不是分组的顺序

任意列都可以使用(甚至非选择的列也可以使用) 只能够使用选择列或者表达式列，而且必须使用每个选择列表达式

不一定需要如果与聚集函数一起使用列(或表达式),则必须使用

提示：不要忘记ORDER BY

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要依赖GROUP BY排序数据。

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;

SELECT order_num, count(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

10.5 SELECT 子句顺序

子句说明是否必须使用

SELECT 要返回的列或表达式是

FROM 从中检索数据的表仅在从表中选择数据时使用

WHERE 行级过滤否

GROUP BY 分组说明仅在按组计算聚集时使用

HAVING 组级过滤否

ORDER BY 输出排序顺序否

第11课使用子查询

11.1子查询

SQL还允许创建子查询(subquery)，即嵌套在其他查询中的查询。

11.2利用子查询进行过滤

SELECT order_num 
FROM OrderItems
WHERE prod_id = 'RGAN01';

SELECT cust_id 
FROM Orders
WHERE order_num IN(20007,20008)

SELECT cust_id 
FROM Orders
WHERE order_num IN (SELECT order_num
                   FROM OrderItems
                   WHERE prod_id = 'RGAN01');

在SELECT语句中，子查询总是从内先外处理。

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 = 'RGAN01'));

作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误

11.3 作为计算字段使用查询

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

order是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每一个顾客执行一次。

比较Order表中cust_id和当前正从Customers表中检索的cust_id

第12课联结表

12.1 联结

SQL最强大的功能之一就是能在数据查询的执行中联结。

联结是利用SQL的SELECT能执行的最中重要的操作

12.1.1关系表

关系表的设计就是要把信息分解成多个表，一类数据一个表。

各个表通过某些共同的值相关联(关系数据库)

关系数据库的好处：

信息不重复，不会浪费时间和空间
如果供应商信息变动，可以只更新Vendors表中的单个记录，相关表中的数据不用改动
由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单。

12.1.2 为什么使用联结

将数据分解为多个表能够更有效的存储，更方便的处理，并且可伸缩性更好。

如果数据存储在多个表中，如何检索数据

答案是：使用联结联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结

12.2 创建联结

创建联结只需要指定要联结的所有表以及关联他们的方式即可

SELECT vend_name, prod_name, prod_price
FROM Vendors,Products
WHERE Vendors.vend_id = Products.vend_id;

12.2.1 WHERE 子句的重要性

在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。

笛卡儿积：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

12.2.2 内联结

目前为止使用的联结称为等值联结，它基于两个表之间的相等测试。这种联结也称为内联结。

SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

12.2.3 联结多个表

SQL不限制一条SELECT语句中可以联结的表的数目。

创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

SELECT 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 = 20007;

使用联结可以有效的替代子查询

子查询：

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN(SELECT cust_id
                FROM Orders
                WHERE order_num IN(SELECT ordre_num 
                                  FROM OrderItems
                                  WHERE prod_id = 'RGAN01'));

联结：

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 = 'RGAN01';

第13课创建高级联结

13.1 使用表别名

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 = 'RGAN01';

需要注意的是，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

13.2 使用不同类型的联结

其他三种联结：自联结、自然联结、外联结

13.2.1 自联结

假如要给与Jim Jones同一公司的所有顾客发一封邮件。

方法1:子查询

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                  FROM Customers
                  WHERE cust_contact = 'Jim Jones');

使用联结进行查询：

SELECT c1.cust_id,c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

此查询中需要的两个表实际上是相同的表，因此Customer表在FROM子句中出现了两次。

where首先联结两个表，然后按第二个表中的cust_contact过滤数据，返回所需要的数据。

用自联结而不用子查询

自联结通常作为外部语句，用来替代从相同表中检索数据的子查询语句。虽然最终的结果是相同的，但是许多DBMS处理联结远比处理子查询快得多。

13.2.2 自然联结

标准的联结返回所有数据，相同的列甚至多次出现。

自然联结排除多次出现，使每一个列只返回一次

自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符(SELECT *)，而对其他表的列使用明确的子集完成。

SELECT C.*，O.order_num,O.order_date,
		OI.prod_id,OI.quantity,OI.item_price
FROM Customer 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 = 'RGAN01';

13.2.3 外联结

外联结：许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。

使用内联结检索所有顾客及其订单：

SELECT Customers.cust_id, Order.order_num
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;

使用外联结检索没有订单顾客在内的所有顾客

SELECT Customer.cust_id, Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customer.cust_id = Orders.cust_id;

与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表)

SELECT Customer.cust_id,Orders.order_num
FROM Customers
RIGHT OUTER JOIN Order ON Customers.cust_id = Orders.cust_id;

左联结和右联结的唯一的差别就是所关联的表的顺序。

13.3使用带聚集函数的联结

要检索所有顾客及每个顾客所下的订单数

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

聚集函数也可以方便地与其他联结一起使用

SELECT Customers.cust_id 
COUNT(Order.order_num) AS num_id
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

13.4 使用联结和联结条件

注意所使用的联结类型，一般我们使用内联结，但使用外联结也有效
关于确切的联结语法，应该查看相关文档
保证使用正确的联结条件，否则会返回不正确的数据
应该总是提供联结条件，否则会得出笛卡尔积
在一个联结中可以使用多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试他们前分别测试每个联结。这会使故障排除更为简单。

第14课组合查询

14.1 组合查询

多数SQL查询只包含从一个或多个表中返回数据的单挑SELECT语句

但是，SQL也允许执行多个查询(多个select语句)，并将结果作为一个查询结果集返回。

这些组合查询通常称为并(union)或复合查询

主要有两种情况需要使用组合查询：

在一个查询中从不同的表中返回结构数据
对一个表执行多个查询，按一个查询返回数据

组合查询等价于多个WHERE条件

14.2 创建组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可以给出多条SELECT语句，将他们的结果组合成一个结果集。

14.2.1 使用UNION

使用UNION很简单，所要做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION

例：获取IL，In,Mi等州的所有顾客的报表，还想包括不管位于哪个州的所有的Fun4ALL

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN('IL','IN','MI');

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'

组合：

SELECT cust_name, cust_contct, cust_email
FROM Customers
WHERE cust_state IN('IL','IN','MI')
UNION
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4ALL';

14.2.2 UNION规则

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
UNION中的每一个查询必须包含相同的列、表达式或聚集函数(不过，没个列不需要以相同的次序列列出)
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的类型

14.2.3 包含或取消重复的行

使用UNION，重复的行会被自动取消，如果想返回所有的匹配行，可以使用UNION ALL而不是UNION

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

14.2.4 对组合查询结果排序

在用UNION组合查询时，只能使用一条ORDER BY子句，他必须位于最后一条SELECT语句之后

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN('IL','IN','MI')
UNION
SELECT cust_name,cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name,cust_contact;

ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上DBMS将用它来排序所有SELECT语句返回的所有结果。

第15课插入数据

15.1数据的插入

INSERT用来将行插入到数据库表，插入的方式有几种形式：

插入完整的行
插入行的一部分
插入某些查询的结果

15.1.1 插入完整的行

使用INSERT语法，指定表名和插入到新行中的值

INSERT INTO Customers
VALUES(1000000006,'Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);

各列必须以他们在表定义中出现的次序填充

这种方式虽然简单，但并不安全，高度依赖于表中列的定义次序，还依赖于其容易获取的次序信息。即使可以得到这种次序信息，也不能保证各列在下一次表结构变动后保持完全相同的次序。

编写INSERT语句更安全的方法:

INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state,
                     cust_zip,cust_country,cust_contact,cust_email)
VALUES(100000006,'toy land','123 any street','new york','ny','11111','USA',NULL,NULL);

这种写法的有点在于即使表结构改变，这条INSERT语句任然能正确工作。

15.1.2 插入部分行

使用这种语法还可以省略列，这表示可以只给某些列提供值，给其他列不提供值。

INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES(1000000006,'toy land','123 Any Street','New York','NY','1111','USA');

省略列：如果表的定义允许，则可以在INSERT操作中省略某些列。如果表中不允许有NULL值或者默认值，这时却省略了表中的值，DBMS就会产生错误信息，相应的行不能成功插入，省略的列必须满足以下条件：

该列定义为允许NULL值
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

15.1.3 插入检索出的数据

INSERT还存在另一种形式，可以利用它将SELECT语句的结果插入表中，这就是所谓的INSERT SELECT

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;

INSERT SELECT 中SELECT语句可以包含WHERE子句，以过滤插入的数据。

插入多行

INSERT通常只插入一行。要插入多行，必须执行多个INSERT语句

INSERT SELECT 是一个例外，他可以用一条INSERT插入多行，不管SELECT语句返回多少行，都将被INSERT插入。

15.2 从一个表复制到另一个表

要将一个表的内容复制到一个全新的表(运行中创建的表)，可以使用CREATE SELECT语句（有的DBMS可以覆盖已存在的表，这依赖于所使用的具体的DBMS）

CREATE TABLE CustCopy AS SELECT * FROM Customers;

SQL Sever可以这样写：

SELECT * INTO CustCopy FROM Customers;

在使用SELECT INTO时，需要知道一些事情：

任何SELECT选项和子句都可以使用，包括WHERE 和GROUP BY
可利用联结从多个表插入数据
不管从多少个表中检索数据，数据都只能插入到一个表中

第16课更新和删除数据

16.1 更新数据

更新数据可以使用UPDATE语句，有两种方式使用UPDATE：

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

基本的UPDATE语句的三部分组成：

要更新的表
列名和他们的新值
确定要更新哪些行的过滤条件

UPDATE Customers
SET cust_email = ‘kim@thetoystore.com’
WHERE cust_id = 10000000005;

更新多个列

UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com'
WHERE cust_id = 1000000006;

在UPDATE语句中使用子查询

UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据

要删除某列的值，可以设置它为NULL

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = 100000000005;

16.2 删除数据

要删除表中的数据，可以使用DELETE语句，有两种使用DELETE的方式:

从表中删除特定的行
从表中删除所有行

DELETE FROM Customers
WHERE cust_id = 1000000006;

如果省略WHERE子句，那么它将删除表中的每一个顾客。

DELETE不需要列名或通配符。DELETE删除整行而不是删除列。要删除指定的列，可以使用UPDATE语句

删除表的内容而不是表

DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身

更快的删除

如果想从表中删除所有行，不要使用DELETE.可以使用TRUNCATE TABLE语句，他完成相同的工作，而速度更快。

16.3 更新和删除的指导原则

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句
保证每一个表都有主键，尽可能像WHERE子句那样使用它
在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

第17课创建和操纵表

17.1 创建表

创建表的两种方式：

多数DBMS都具有交互式创建和管理数据库表的工具
表也可以直接用SQL语句操纵

用程序创建表，可以使用SQL的CREATE TABLE语句

17.1.1 表创建基础

利用CREATE TABLE 创建表，必须给出下列信息：

新表的名字，在关键字CREATE TABLE之后给出
表的列名和定义，用逗号分隔
有的DBMS还要求指定表的位置

CREATE TABLE Products
(
    prod_id          CHAR(10)           NOT NULL,
    vend_id          CHAR(10)           NOT NULL,
    prod_name        CHAR(254)          NOT NULL,
    prod_price       DECIMAL(8,2)       NOT NULL,
    prod_desc        VARCHAR(1000)      NULL
);

17.1.2 使用NULL值

NULL值就是没有值或缺值，允许NULL值的列也允许在插入行时不给出该列的值

CREATE TABLR Orders
(
    order_num   INTEGER  NOT NULL,
    order_date  DATETIME NOT NULL,
    cust_id    CHAR(10)  NOT NULL 
);

CREATE TABLE Vendors
(
    vend_id   CHAR(10)  NOT NULL,
    vend_name CHAR(50)  NOT NULL,
    vend_address CHAR(50) ,
    vend_city   CHAR(50)  ,
    vend_state   CHAR(50) ,
    vend_zip     CHAR(50) ,
    vend_country  CHAR(50) 
);

17.1.3 指定默认值

默认值在CREATE TABLE 语句中的定义中用关键字DEFAULT指定

CREATE TABLE OrderItems
(
    order_num    INTEGER  NOT NULL,
    order_item    INTEGER NOT NULL,
    prod_id     CHAR(10)   NOT NULL,
    quantity  INTEGER  NOT NULL DEFAULT 1,
    item_price DECIMAL(8,2)  NOT NULL
);

默认值经常用于日期或时间戳列

MySQL用户指定DEFAULT CURRENT_DATE()

17.2 更新表

更新表定义可以使用ALTER TABLE语句

使用ALTER时需要考虑的事情：

理想情况不要在表中包含数据时对其进行更新。应该在表的设计过程充分考虑未来的可能的需求，避免今后对表的结构做大改动
所有的DBMS都允许给现有的表增加列，不过对所有增加列的数据类型(以及NULL和DEFAULT的使用)有所限制
许多DBMS不允许删除或更改表中的列
多数DBMS允许重新命名表中的列
许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制

ALTER语法：

在ALTER TABLE 之后要更改的表名
列出要做哪些更改

ALTER TABLE Vendors
ADD vend_phone CHAR(10)

ALTER TABLE Vendors
DROP COLUMN vend_phone;

复杂的表结构更改一般需要手动删除过程，他涉及一下步骤

用新的列布局删除创建一个新表
使用insert into 语句从旧表中复制数据到新表
检验包含所需数据的新表
重命名旧表
用旧表原来的名字重命名新表
根据需要，重新创建触发器、存储过程、索引和外键

17.3 删除表

删除表（删除整个表而不是其内容）非常简单使用DROP TABLE语句即可：

DROP TABLE CustCopy;

17.4 重命名表

每一个DBMS对表重命名的支持有所不同。

MYSQL用户使用RENAME语句

第18课视图

18.1 视图

视图只包含使用时动态检索数据的查询

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 = 'RGAN01';

我们把整个查询包装成一个名为ProductCustomers的虚拟表

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

ProductCustomers是一个视图，作为视图，他不包含任何列或数据，包含的是一个查询

18.1.1为什么使用视图

重用SQL语句
简化复杂的SQL语句
使用表的一部分而不是整个表
保护数据
更改数据格式和表示

18.1.2 视图的规则和限制

常见的限制与规范：

视图必须唯一命名
对于可以创建的视图数目没有限制
创建视图，必须具有足够的访问权限
视图可以嵌套
许多DBMS禁止在视图查询中使用ORDER语句
有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名
视图不能索引，也不能有关联的触发器或默认值
有些DBMS把视图作为只读查询，这表示可以从视图检索数据，但不能将数据写回底层表
有些DBMS允许创建这样的视图，他不能进行导致行不再属于视图的插入或更新

18.2创建视图

CREATE VIEW语句

18.2.1利用视图简化复杂的联结

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;

SELECT cust_name. cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

18.2.2 用视图重新格式化检索出的数据

SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors;

18.2.3 用视图过滤不想要的数据

CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name,cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

18.2.4使用视图与计算字段

SELECT prod_id,
	quantity,
	item_price,
	quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

转换为视图

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 = 20008;

第19课使用存储过程

19.1 存储过程

存储过程就是为以后使用而保存的一条或多条SQL语句

19.2 为什么要使用存储过程

通过把处理封装在一个易用的单元中，可以简化复杂的操作
由于不要求反复建立一系列处理步骤，因而保证了数据的一致性
上一点的延伸就是防止错误。需要执行的步骤越多，出错的肯能性就越大。防止错误保证了数据的一致性
简化对变动的管理。如果表名、列名或业务逻辑有变化，那么只需要更改存储过程的代码。
上一点的延伸就是安全性。通过存储限制对基础数据的访问，减少了数据讹误的机会。
因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。
存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

存储过程：简单、安全、高性能

19.3 执行存储过程

执行存储过程的SQL语句很简单，即EXECUTE.

EXECUTE接受存储过程名和需要传递给它的任何参数

EXECUTE AddNewProduct('JTSO1',
                      'Stuffed Eiffel Tower',
                      6.49,
                      'Plush stuffed toy with the text La Tour Eiffel in red white and blue'
);

存储过程所完成的工作：

验证传递的数据，保证所有4个参数都有值
生成用作主键的唯一ID
将新产品插入Products表，在合适的列中存储生成的主键和传递的数据

19.4 创建存储过程

sql sever版本

CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;

DECLARE语句声明了一个名为@cnt的局部变量，然后在SELECT语句中使用这个变量，让它包含COUNT()函数返回的值

最后，用RETURN @cnt语句将计数返回给调用程序

调用SQL Sever的例子

DECLARE @ReturnValue INT
EXECUTE @ReturnValue = MailingListCount;
SELECT @ReturnValue;

声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用SELECT语句显示返回的值。

第20课管理事务处理

如何使用COMMIT和ROLLBACK语句管理事务处理

20.1 事务处理

事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包括不完整的操作结果。

关于事务处理的几个术语：

事务（transaction）指一组SQL语句
回退(rollback)指撤销指定SQL语句的过程
提交(commit)指将未存储的SQL语句结果写入数据库表
保留点(savepoint)指事务处理中设置的临时占位符，可以对它发布回退

可以回退哪些语句

事务处理用来管理INSERT UPDATE DELETE语句。不能回退SELECT CREATE DROP 语句

20.2 控制事务处理

管理事务的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时该回退，何时不该回退。

Mysql代码

START TRANSACTION
COMMIT TRANSACTION

通常COMMIT用于保存更改，ROLLBACK用于撤销

20.2.1 使用ROLLBACK

DELETE FROM Orders;
ROLLBACK;

sql的ROLLBACK命令用来回退SQL语句

20.2.2 使用COMMIT

一般的SQL语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交，即提交操作是自动进行的。

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

20.2.3 使用保留点

要支持回退部分事务，必须在事务处理块中的合适位置放置占位符，如果需要回退，就可以回退到某个占位符。这个占位符被称为保留点

创建保留点，MYSQL可以使用SAVEPOINT语句

SAVEPOINT delete1;

每个保留点都要去能够标识它的唯一名字,以便在回退的时候，DBMS知道回退到何处。

ROLLBACK TO delete1;

第21课使用游标

21.1 游标

需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。

游标是一个存储在DBMS服务器上的数据库查询，他不是一条SELECT语句，而是被该语句检索出来的结果集。

游标常见的选项和特征：

能够标记游标为只读，使数据能读取，但不能更新和删除
能控制可以执行的定向操作
能标记某些列为可编辑的，某些列位不可编辑的
规定范围。使游标对创建它的特定请求或对所有请求可访问
指示DBMS对检索出的数据进行复制，使数据在游标打开和访问期间不变化

21.2 使用游标

21.2.1创建游标

使用DECLARE语句创建表，DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

mysql版本

DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;

21.2.2 使用游标

使用OPEN CURSOR 语句打开游标

OPEN CURSOR CustCursor

用FETCH语句访问游标数据

DECLARE TYPE CustCursor IS REF CURSOR
RETURN Customers % ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN 
OPEN CustCursor;
FETCH CustCursor INTO CustRecord;
CLOSE CustCursor;
END;

21.2.3 关闭游标

CLOSE CustCursor

第22课高级SQL特性

22.1 约束

管理如何插入或处理数据库的规则

22.1.1 主键

表中任意列只要满足以下条件，都可以用于主键：

任意两行的主键值都不相同
每行都具有一个主键值（即列中不允许NULL值）
包含主键值的列从不修改或更新
主键值不能重用。如果从表中删除某行，其主键值不分配给新行。

CREATE TABLE Vendors(
vend_id   CHAR(10)    NOT NULL PRIMARY KEY,
vend_name  CHAR(10)    NOT NULL,
vend_address CHAR(10)    NULL
);

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY(vend_id);

这里定义相同的列为主键，但使用的是CONSTRAINT语法。

22.1.2 外键

外键是表中的一列，其值必须列在另一表的主键中

CREATE TABLE Orders
(
    order_num   INTEGER NOT NULL PRIMARY KEY,
    order_date DATETIME NOT NULL,
    cust_id  ChAR(10)    NOT NULL REFERENCES Customers(cust_id)
);

其中的表定义使用了REFERENCES关键字，他表示cust_id中任何值都必须是Customers表中的cust_id中的值

相同的工作也可以在ALTER TABLE语句中用CONSTRAINT语法来完成：

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY(cust_id) REFERENCES Customers(cust_id);

外键有助于防止意外删除

21.1.3 唯一约束

唯一约束用来保证一列中的数据是唯一的

与主键的区别：

表可以包含多个唯一约束，但每个表只允许一个主键
唯一约束列可以包含NULL值
唯一约束列可以修改或更新
唯一约束列的值可以重复使用
与主键不一样，唯一约束不能用来定义外键

使用UNIQUE关键字

22.1.4 检查约束

检查最小值或最大值
指定范围
只允许特定的值

CREATE TABLE OrderItems(
order_num  INTEGER NOT NULL,
    order_item INTEGER NOT NULL,
    prod_id  CHAR(10)   NOT NULL,
    quantity  INTEGER   NOT NULL CHECK(quantity > 0),
    item_price  MONEY NOT NULL
);

检查名为gender的列只包含M或F

ADD CONSTRAINT CHECK(gender LIKE '[MF]');

22.2 索引

索引用CREATE INDEX语句创建

CREATE INDEX prod_name_ind
ON Products(prod_name);

索引必须唯一命名

索引名为prod_name_ind ON用来指定被索引的表，而索引中包含的列在表名后的圆括号中给出。

paradise smile

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

MySQL

第1课 了解SQL

第2课 检索数据

2.1检索单个列：

2.2检索多个列：

2.3检索所有列：

2.4检索不同的值：

2.5限制结果：

第3课 排序检索数据

3.1排序数据

子句:

order by 子句的位置：

3.2按多个列排序

3.3 按照位置排序

3.4指定排序方向

打算用多个列排序

第4课 过滤数据

4.1 使用where子句

where子句的位置

4.2 WHERE 子句操作符

4.2.1 检查单个值

4.2.2不匹配检查

4.2.3范围值检查

4.2.4 空值检查

第5课 高级数据过滤

5.1组合WHERE子句

5.1.1 AND操作符

5.1.2 OR操作符

5.1.3求值顺序

5.2 IN操作符

5.3 NOT操作符

第6课 用通配符进行过滤

6.1 LIKE操作符

6.1.1 百分号(%)通配符

6.1.2 下划线(_)通配符

6.2 使用通配符的技巧

第7课 创建计算字段

7.1计算字段

7.2拼接字段

7.3执行算术计算

第8课 使用函数处理数据

8.1 函数

8.2使用函数

8.2.1 文本处理函数

8.2.2 日期和时间处理函数

8.2.3 数值处理函数

第9课 汇总数据

9.1 聚集函数

9.1.1 AVG()函数

9.1.2 COUNT()函数

9.1.3 MAX()函数

9.1.4 MIN()函数

9.1.5 SUM()函数

9.2 聚集不同值

9.3组合聚集函数

第10课 分组数据

10.1数据分组

10.2创建分组

10.3 过滤分组

10.4 分组和排序

10.5 SELECT 子句顺序

第11课 使用子查询

11.1子查询

11.2利用子查询进行过滤

11.3 作为计算字段使用查询

第12课 联结表

12.1 联结

12.1.1关系表

12.1.2 为什么使用联结

12.2 创建联结

12.2.1 WHERE 子句的重要性

12.2.2 内联结

12.2.3 联结多个表

第13课 创建高级联结

13.1 使用表别名

13.2 使用不同类型的联结

13.2.1 自联结

13.2.2 自然联结

13.2.3 外联结

13.3使用带聚集函数的联结

第1课了解SQL

第2课检索数据

第3课排序检索数据

第4课过滤数据

第5课高级数据过滤

第6课用通配符进行过滤

第7课创建计算字段

第8课使用函数处理数据

第9课汇总数据

第10课分组数据

第11课使用子查询

第12课联结表

第13课创建高级联结

第14课组合查询

第15课插入数据

第16课更新和删除数据

第17课创建和操纵表

第18课视图

第19课使用存储过程

第20课管理事务处理

第21课使用游标

第22课高级SQL特性