《sql必知必会》

浏览数：34 / 时间：2015年06月12日

主键：
1）任意两行都不具有相同的主键值：
2）每个行都必须具有一个或多个主键值（主键列不允许NULL值）
3）主键列中的值不允许修改或更新；
4）主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）
在指定一条ORDER BY 子句里，应保证它是SELECT语句中最后一条子句。
按多个列排序：
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price, prod_name;
指定排序方向：
数据库默认的排序方式为升序排序；
为了进行降序排序，可以指定DESC关键字。
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;
如果想在多个列上进行降序排序，必须对列指定DESC关键字。
拼接字段：
Mysql用法：concat函数用于拼接，关键字AS赋予别名

mysql> select concat ( vend_name, ‘(‘, vend_country, ‘)‘) as vend_title
　　-> from Vendors

　　-> order by vend_name;

　　+--------------------------------+

　　| vend_title |

　　+--------------------------------+

　　| Bear Emporium(USA) |

　　| Bears R Us(USA) |

　　| Doll House Inc.(USA) |

　　| Fun and Games(England) |

　　| Furball Inc.(USA) |

　　| Jouets et ours(France) |

　　+--------------------------------+

　　6 rows in set (0.00 sec)
执行算术运算
mysql> select prod_id, quantity, item_price, quantity*item_price as expanded_price
　　-> from OrderItems

　　-> where order_num = 20008;

　　+---------+----------+------------+----------------+

　　| prod_id | quantity | item_price | expanded_price |

　　+---------+----------+------------+----------------+

　　| RGAN01 | 5 | 4.99 | 24.95 |

　　| BR03 | 5 | 11.99 | 59.95 |

　　| BNBG01 | 10 | 3.49 | 34.90 |

　　| BNBG02 | 10 | 3.49 | 34.90 |

　　| BNBG03 | 10 | 3.49 | 34.90 |

　　+---------+----------+------------+----------------+

　　5 rows in set (0.02 sec)
与SQL语句不一样，SQL函数是不可移植的。
聚集函数(aggregate function) 运行在行组上（行，或者记录）上，计算和返回单个值的函数。

函数说明

AVG() 返回某列的平均值

COUNT() 返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

注：表格里虽然某列某列，实质是作用于行上的，“对于该列的所有行的平均值，行数，最大值，最小值，和”
mysql> SELECT prod_price
　　-> FROM Products;

　　+------------+

　　| prod_price |

　　+------------+

　　| 5.99 |

　　| 8.99 |

　　| 11.99 |

　　| 3.49 |

　　| 3.49 |

　　| 3.49 |

　　| 4.99 |

　　| 9.49 |

　　| 9.49 |

　　+------------+

　　9 rows in set (0.00 sec)

　　mysql> select avg(prod_price) as avg_price

　　-> from Products;

　　+-----------+

　　| avg_price |

　　+-----------+

　　| 6.823333 |

　　+-----------+

　　1 row in set (0.00 sec)

mysql> SELECT AVG(prod_price) AS avg_price

　　-> from Products

　　-> where vend_id = ‘DLL01‘;

　　+-----------+

　　| avg_price |

　　+-----------+

　　| 3.865000 |

　　+-----------+

　　1 row in set (0.00 sec)

　　mysql> SELECT COUNT(*)

　　-> FROM Products;

　　+----------+

　　| COUNT(*) |

　　+----------+

　　| 9 |

　　+----------+

　　1 row in set (0.00 sec)

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

　　mysql> SELECT COUNT(cust_email) AS num_cus

　　-> FROM Customers;

　　+---------+

　　| num_cus |

　　+---------+

　　| 3 |

　　+---------+

　　1 row in set (0.02 sec)

　　mysql> SELECT MAX(prod_price) AS max_price

　　-> FROM Products;

　　+-----------+

　　| max_price |

　　+-----------+

　　| 11.99 |

　　+-----------+

　　1 row in set (0.00 sec)

注：可以观察发现，聚集函数通过对所有行或者所有非空行或者符合where条件的所有（非空）行进行操作，结果集为只有一行数据。如果跟结果集为多行的查询一起，则会很怪异。如下：

mysql> SELECT prod_name, MAX(prod_price) AS price

　　-> FROM Products;

　　+-------------------+-------+

　　| prod_name | price |

　　+-------------------+-------+

　　| 8 inch teddy bear | 11.99 |

　　+-------------------+-------+

　　1 row in set (0.00 sec)

　　（注：上面的是错误的用法，具体对比看下面的全表，就能发现错误）

　　mysql> SELECT prod_name, prod_price

　　-> FROM Products;

　　+---------------------+------------+

　　| prod_name | prod_price |

　　+---------------------+------------+

　　| 8 inch teddy bear | 5.99 |

　　| 12 inch teddy bear | 8.99 |

　　| 18 inch teddy bear | 11.99 |

　　| Fish bean bag toy | 3.49 |

　　| Bird bean bag toy | 3.49 |

　　| Rabbit bean bag toy | 3.49 |

　　| Raggedy Ann | 4.99 |

　　| King doll | 9.49 |

　　| Queen doll | 9.49 |

　　+---------------------+------------+

　　9 rows in set (0.01 sec)
又一例子
mysql> select vend_id, count(*) as num_prods

-> from Products;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

| BRS01 | 9 |

+---------+-----------+

1 row in set (0.00 sec)

mysql> select vend_id

-> from Products;

+---------+

| vend_id |

+---------+

| BRS01 |

| BRS01 |

| BRS01 |

| DLL01 |

| DLL01 |

| DLL01 |

| DLL01 |

| FNG01 |

| FNG01 |

+---------+

9 rows in set (0.00 sec)

接上注又注：但是结果集同为一行的聚集函数作为查询则不会出错（即组合不同的聚集函数）

接上注又再注：又一个错误的用法，集合==行？

mysql> SELECT prod_name, prod_price

-> FROM Products

-> WHERE prod_price = MAX(prod_price);

ERROR 1111 (HY000): Invalid use of group function

mysql> SELECT prod_name, prod_price

-> FROM Products

-> WHERE MAX(prod_price);

ERROR 1111 (HY000): Invalid use of group function

WHY？
聚集不同值：
对所有的行执行计算，指定ALL参数或不给参数（ALL为默认）
只不含不同的值，指定DISTINCT参数
分组：

mysql> SELECT cust_id, COUNT(cust_name) AS COUNT

-> FROM Customers

-> Where cust_name = ‘Fun4All‘;

+------------+-------+

| cust_id | COUNT |

+------------+-------+

| 1000000003 | 2 |

+------------+-------+

1 row in set (0.01 sec)

mysql> SELECT cust_id, COUNT(cust_name) AS count

-> FROM Customers

-> WHERE cust_name = ‘Fun4All‘

-> GROUP BY cust_id;

+------------+-------+

| cust_id | count |

+------------+-------+

| 1000000003 | 1 |

| 1000000004 | 1 |

+------------+-------+

2 rows in set (0.01 sec)

(注：GROUP BY对COUNT函数起到很重要的作用，COUNT根据GOUP BY的分组来计算）
GROUP BY 子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集。
过滤分组
WHERE 过滤行，而HAVING过滤分组
使用HAVING和WHERE
HAVING与WHERE非常类似，如果不指定GROUP BY，则大多数DBMS将把它们作为相同的东西对待。应该仅在GROUP BY 子句结合才使用HAVING，而WHERE子句用于标准的行级过滤。

SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

(SELECT COUNT(*)

作为计算字段使用子查询
看一个经典段子，好好品味一下：
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS
orders
FROM Customers
ORDER BY cust_name;
结果：
cust_name cust_state orders
------------------------------ --------------- -----------
Fun4All IN 1
Fun4All AZ 1
Kids Place OH 0
The Toy Store IL 1
Village Toys MI 2
该子查询对检索出的每个客户执行一次。在此例中，该子查询执行了5次，因为检索出了5个客户。
笛卡儿积（cartesian product）由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

mysql> SELECT vend_name

-> FROM Vendors;

+-----------------+

| vend_name |

+-----------------+

| Bears R Us |

| Bear Emporium |

| Doll House Inc. |

| Furball Inc. |

| Fun and Games |

| Jouets et ours |

+-----------------+

6 rows in set (0.00 sec)

mysql> SELECT prod_name

-> FROM Products;

+---------------------+

| prod_name |

+---------------------+

| 8 inch teddy bear |

| 12 inch teddy bear |

| 18 inch teddy bear |

| Fish bean bag toy |

| Bird bean bag toy |

| Rabbit bean bag toy |

| Raggedy Ann |

| King doll |

| Queen doll |

+---------------------+

9 rows in set (0.01 sec)

mysql> SELECT vend_name, prod_name, prod_price

-> FROM Vendors, Products;

+-----------------+---------------------+------------+

| vend_name | prod_name | prod_price |

+-----------------+---------------------+------------+

| Bears R Us | 8 inch teddy bear | 5.99 |

| Bear Emporium | 8 inch teddy bear | 5.99 |

| Doll House Inc. | 8 inch teddy bear | 5.99 |

| Furball Inc. | 8 inch teddy bear | 5.99 |

| Fun and Games | 8 inch teddy bear | 5.99 |

| Jouets et ours | 8 inch teddy bear | 5.99 |

| Bears R Us | 12 inch teddy bear | 8.99 |

| Bear Emporium | 12 inch teddy bear | 8.99 |

| Doll House Inc. | 12 inch teddy bear | 8.99 |

| Furball Inc. | 12 inch teddy bear | 8.99 |

| Fun and Games | 12 inch teddy bear | 8.99 |

| Jouets et ours | 12 inch teddy bear | 8.99 |

| Bears R Us | 18 inch teddy bear | 11.99 |

| Bear Emporium | 18 inch teddy bear | 11.99 |

| Doll House Inc. | 18 inch teddy bear | 11.99 |

| Furball Inc. | 18 inch teddy bear | 11.99 |

| Fun and Games | 18 inch teddy bear | 11.99 |

| Jouets et ours | 18 inch teddy bear | 11.99 |

| Bears R Us | Fish bean bag toy | 3.49 |

| Bear Emporium | Fish bean bag toy | 3.49 |

| Doll House Inc. | Fish bean bag toy | 3.49 |

| Furball Inc. | Fish bean bag toy | 3.49 |

| Fun and Games | Fish bean bag toy | 3.49 |

| Jouets et ours | Fish bean bag toy | 3.49 |

| Bears R Us | Bird bean bag toy | 3.49 |

| Bear Emporium | Bird bean bag toy | 3.49 |

| Doll House Inc. | Bird bean bag toy | 3.49 |

| Furball Inc. | Bird bean bag toy | 3.49 |

| Fun and Games | Bird bean bag toy | 3.49 |

| Jouets et ours | Bird bean bag toy | 3.49 |

| Bears R Us | Rabbit bean bag toy | 3.49 |

| Bear Emporium | Rabbit bean bag toy | 3.49 |

| Doll House Inc. | Rabbit bean bag toy | 3.49 |

| Furball Inc. | Rabbit bean bag toy | 3.49 |

| Fun and Games | Rabbit bean bag toy | 3.49 |

| Jouets et ours | Rabbit bean bag toy | 3.49 |

| Bears R Us | Raggedy Ann | 4.99 |

| Bear Emporium | Raggedy Ann | 4.99 |

| Doll House Inc. | Raggedy Ann | 4.99 |

| Furball Inc. | Raggedy Ann | 4.99 |

| Fun and Games | Raggedy Ann | 4.99 |

| Jouets et ours | Raggedy Ann | 4.99 |

| Bears R Us | King doll | 9.49 |

| Bear Emporium | King doll | 9.49 |

| Doll House Inc. | King doll | 9.49 |

| Furball Inc. | King doll | 9.49 |

| Fun and Games | King doll | 9.49 |

| Jouets et ours | King doll | 9.49 |

| Bears R Us | Queen doll | 9.49 |

| Bear Emporium | Queen doll | 9.49 |

| Doll House Inc. | Queen doll | 9.49 |

| Furball Inc. | Queen doll | 9.49 |

| Fun and Games | Queen doll | 9.49 |

| Jouets et ours | Queen doll | 9.49 |

+-----------------+---------------------+------------+

54 rows in set (0.01 sec)

mysql> SELECT vend_name, Vendors.vend_id, Products.vend_id, prod_name

-> FROM Vendors, Products

-> WHERE Vendors.vend_id = Products.vend_id;

+-----------------+---------+---------+---------------------+

| vend_name | vend_id | vend_id | prod_name |

+-----------------+---------+---------+---------------------+

| Bears R Us | BRS01 | BRS01 | 8 inch teddy bear |

| Bears R Us | BRS01 | BRS01 | 12 inch teddy bear |

| Bears R Us | BRS01 | BRS01 | 18 inch teddy bear |

| Doll House Inc. | DLL01 | DLL01 | Fish bean bag toy |

| Doll House Inc. | DLL01 | DLL01 | Bird bean bag toy |

| Doll House Inc. | DLL01 | DLL01 | Rabbit bean bag toy |

| Doll House Inc. | DLL01 | DLL01 | Raggedy Ann |

| Fun and Games | FNG01 | FNG01 | King doll |

| Fun and Games | FNG01 | FNG01 | Queen doll |

+-----------------+---------+---------+---------------------+

9 rows in set (0.00 sec)

mysql> SELECT cust_id, cust_name, cust_contact

-> FROM Customers;

+------------+---------------+--------------------+

| cust_id | cust_name | cust_contact |

+------------+---------------+--------------------+

| 1000000001 | Village Toys | John Smith |

| 1000000002 | Kids Place | Michelle Green |

| 1000000003 | Fun4All | Jim Jones |

| 1000000004 | Fun4All | Denise L. Stephens |

| 1000000005 | The Toy Store | Kim Howard |

+------------+---------------+--------------------+

5 rows in set (0.01 sec)
联结多个表
对于多表联结，外键相等作为WHERE条件是最基本的过滤条件，（因为除外键相等的这部分“行”外，都是联结所带来的无效的“行”）——自然联结
自联结
看一个很屌的例子，充分说明了自联结的好处。

说明：这个例子找到cust_contact为‘Jim Jones’的客户的cust_name。妙在cust_name是可以重名的，即两个客户可以叫同一个名字。这个例子就是在查询某个cust_name的信息，而cust_name是从cust_contact处得来。如果不用自联结，where cust_contact = ‘Jim Jones‘得到的结果集是完全不一样的。
UNION
利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集。
UNION的使用：只要在每条SELECT语句之间放上关键字UNION。

mysql> 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‘;

+---------------+--------------------+-----------------------+

| cust_name | cust_contact | cust_email |

+---------------+--------------------+-----------------------+

| Village Toys | John Smith | [email protected] |

| Fun4All | Jim Jones | [email protected] |

| The Toy Store | Kim Howard | NULL |

| Fun4All | Denise L. Stephens | [email protected] |

+---------------+--------------------+-----------------------+

4 rows in set (0.01 sec)

UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
插入更新删除

mysql> INSERT INTO Customers(cust_id,

-> cust_name,

-> cust_address,

-> cust_city,

-> cust_state,

-> cust_zip,

-> cust_country,

-> cust_contact,

-> cust_email)

-> VALUES(‘1000000006‘,‘Toy Land‘,‘123 Any Street‘,

-> ‘New York‘,

-> ‘NY‘,

-> ‘11111‘,

-> ‘USA‘,

-> NULL,

-> NULL);

Query OK, 1 row affected (0.03 sec)

mysql> UPDATE Customers

-> SET cust_email = ‘[email protected]‘

-> WHERE cust_id = ‘1000000005‘;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> DELETE FROM Customers

-> WHERE cust_id = ‘1000000006‘;

Query OK, 1 row affected (0.00 sec)

mysql> SELECT *

-> FROM Customers

-> WHERE cust_id = ‘1000000006‘;

Empty set (0.00 sec)

来自为知笔记(Wiz)

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

《sql必知必会》

标签： des style log com 使用 http src si it des style log com 使用 http src si it

《sql必知必会》

相关文章

随机文章

您可能还喜欢

您可能还喜欢

最新图文

您可能还喜欢

您可能还喜欢

文摘排行

文章排行

推荐文章

图文排行

推荐图文

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和