一.了解SQL
database 数据库
数据库软件称为数据库管理系统(DBMS Data Base Management System )
table 表
column 列
row 行
primary key 主键:唯一标识表中每行的这个列(或这几个列)称为主键。
表中的任何列都可以作为主键,只要它满足以下条件:
- 任意两行都不具有相同的主键值
- 每一行都必须具有一个主键值(主键列不允许有NULL)
- 主键列中的值不允许修改或者更新
- 主键值不能重用(如果某行从表中删除,它的主键不能赋给以后的新行)
几乎所有重要的DBMS都支持SQL(Structured Query Language)
二.检索数据
检索单个列
SELECT prod_name
FROM Products;
prod_name
-------------------
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Raggedy Ann
King doll
Queen doll
检索多个列
SELECT prod_id, prod_name, prod_price
FROM Products;
prod_id prod_name prod_price
BNBG01 Fish bean bag toy 3.4900
BNBG02 Bird bean bag toy 3.4900
BNBG03 Rabbit bean bag toy 3.4900
BR01 8 inch teddy bear 5.9900
BR02 12 inch teddy bear 8.9900
BR03 18 inch teddy bear 11.9900
RGAN01 Raggedy Ann 4.9900
RYL01 King doll 9.4900
RYL02 Queen dool 9.4900
检索所有列
SELECT *
FROM Products;
检索不同的值
SELECT vend_id
FROM Products;
vend_id
----------
BRS01
BRS01
BRS01
DLL01
DLL01
DLL01
DLL01
FNG01
FNG01
使用DISTINCT关键字,指示数据库只返回不同的值
SELECT DISTINCT vend_id
FROM Products;
vend_id
----------
BRS01
DLL01
FNG01
SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;
prod_name
-------------------
Rabbit bean bag toy
Raggedy Ann
King doll
Queen doll
三.排序检索数据
SELECT prod_name
FROM Products
ORDER BY prod_name;
prod_name
--------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
Bird bean bag toy
Fish bean bag toy
King doll
Queen doll
Rabbit bean bag toy
Raggedy Ann
按多个列排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
prod_id prod_price prod_name
------- ---------- --------------------
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
RGAN01 4.9900 Raggedy Ann
BR01 5.9900 8 inch teddy bear
BR02 8.9900 12 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR03 11.9900 18 inch teddy bear
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;
prod_id prod_price prod_name
------- ---------- --------------------
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
RGAN01 4.9900 Raggedy Ann
BR01 5.9900 8 inch teddy bear
BR02 8.9900 12 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR03 11.9900 18 inch teddy bear
指定排序方向
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;
prod_id prod_price prod_name
------- ---------- --------------------
BR03 11.9900 18 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR02 8.9900 12 inch teddy bear
BR01 5.9900 8 inch teddy bear
RGAN01 4.9900 Raggedy Ann
BNBG01 3.4900 Fish bean bag toy
BNBG02 3.4900 Bird bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
用多个列排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;
prod_id prod_price prod_name
------- ---------- --------------------
BR03 11.9900 18 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR02 8.9900 12 inch teddy bear
BR01 5.9900 8 inch teddy bear
RGAN01 4.9900 Raggedy Ann
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
四.过滤数据
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;
prod_name prod_price
------------------- ----------
Fish bean bag toy 3.49
Bird bean bag toy 3.49
Rabbit bean bag toy 3.49
where子句操作符
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;
五.高级数据过滤
and操作符(用在WHERE子句中的关键字,用来指示检索满足所有给定条件的行)
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
prod_id prod_price prod_name
------- ---------- --------------------
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
or操作符(WHERE子句中使用的关键字,用来表示检索匹配任一给定条件的行)
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;
prod_name prod_price
------------------- ----------
Fish bean bag toy 3.4900
Bird bean bag toy 3.4900
Rabbit bean bag toy 3.4900
8 inch teddy bear 5.9900
12 inch teddy bear 8.9900
18 inch teddy bear 11.9900
Raggedy Ann 4.9900
提示:and操作符比or操作符优先级高,可以使用圆括号对操作符进行明确分组
in操作符(WHERE子句中用来指定要匹配值的清单的关键字,功能与OR相当)
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;
prod_name prod_price
------------------- ----------
12 inch teddy bear 8.9900
18 inch teddy bear 11.9900
8 inch teddy bear 5.9900
Bird bean bag toy 3.4900
Fish bean bag toy 3.4900
Rabbit bean bag toy 3.4900
Raggedy Ann 4.9900
not操作符(WHERE子句中用来否定其后条件的关键字)
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
prod_name
------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
King doll
Queen doll
六.用通配符进行过滤
like操作符
百分号(%)通配符(%表示任何字符出现任意次数)
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
prod_id prod_name
------- ------------------
BNBG01 Fish bean bag toy
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';
prod_id prod_name
-------- --------------------
BNBG01 Fish bean bag toy
BNBG02 Bird bean bag toy
BNBG03 Rabbit bean bag toy
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';
prod_id prod_name
-------- --------------------
BR02 12 inch teddy bear
BR03 18 inch teddy bear
方括号([])通配符
SELECT custcontact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
cust_contact
-----------------
Jim Jones
John Smith
Michelle Green
七.创建计算字段
在MYSQL中,拼接两个字段
SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;
-----------------------------------------------------------
Bear Emporium (USA )
Bears R Us (USA )
Doll House Inc. (USA )
Fun and Games (England )
Furball Inc. (USA )
Jouets et ours (France )
SELECT Concat(RTRIM(vend_name), ' (',RTRIM(vend_country), ')')
FROM Vendors
ORDER BY vend_name;
使用别名
SELECT Concat(vend_name, ' (', vend_country, ')')
AS vend_title
FROM Vendors
ORDER BY vend_name;
执行算术计算
SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;
prod_id quantity item_price
---------- ----------- ---------------------
RGAN01 5 4.9900
BR03 5 11.9900
BNBG01 10 3.4900
BNBG02 10 3.4900
BNBG03 10 3.4900
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.9900 24.9500
BR03 5 11.9900 59.9500
BNBG01 10 3.4900 34.9000
BNBG02 10 3.4900 34.9000
BNBG03 10 3.4900 34.9000
SQL算术操作符
八.使用函数处理数据
文本处理函数
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
vend_name vend_name_upcase
--------------------------- ----------------------------
Bear Emporium BEAR EMPORIUM
Bears R Us BEARS R US
Doll House Inc. DOLL HOUSE INC.
Fun and Games FUN AND GAMES
Furball Inc. FURBALL INC.
Jouets et ours JOUETS ET OURS
![](https://i-blog.csdnimg.cn/blog_migrate/e3711580a90f486be25d0a59e7cc14a8.png)
![](https://i-blog.csdnimg.cn/blog_migrate/bccbe0b28545eea672932e34c4ea0f3c.png)
九.汇总数据
SQL聚集函数
AVG()函数(通过对表中行数计数并计算其列值之和,求得该列的平均值)
SELECT AVG(prod_price) AS avg_price
FROM Products;
avg_price
-------------
6.823333
说明:AVG()函数忽略列值为NULL的行
COUNT()函数(COUNT()函数进行计数)
SELECT COUNT(*) AS num_cust
FROM Customers;
num_cust
--------
5
SELECT COUNT(cust_email) AS num_cust
FROM Customers;
num_cust
--------
3
MAX()函数(返回指定列中的最大值)
SELECT MAX(prod_price) AS max_price
FROM Products;
max_price
----------
11.9900
提示:对非数值数据使用 MAX() 虽然 MAX()一般用来找出最大的数值或日期值,但许多(并非所有)DBMS允许将它用来返回任意列中的最大值,包括返回文本列中的最 大值。在用于文本数据时,MAX()返回按该列排序后的最后一行。 MAX()函数忽略列值为NULL的行。
MIN()函数(返回指定列的最小值)
SELECT MIN(prod_price) AS min_price
FROM Products;
min_price
----------
3.4900
提示:对非数值数据使用 MAX() 虽然 MAX()一般用来找出最小的数值或日期值,但许多(并非所有)DBMS允许将它用来返回任意列中的最小值,包括返回文本列中的最小值。在用于文本数据时,MAX()返回按该列排序后的最前面的行。 MAX()函数忽略列值为NULL的行。
SUM()函数(用来返回指定列值的和)
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;
items_ordered
----------
200
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
avg_price
-----------
4.2400
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
---------- --------------- --------------- ---------
9 3.4900 11.9900 6.823333
十.分组数据
创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
vend_id num_prods
------- ---------
BRS01 3
DLL01 4
FNG01 2
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
cust_id orders
---------- -----------
1000000001 2
![](https://i-blog.csdnimg.cn/blog_migrate/df06bb286d94d8f4e5feff63ed6c95bb.png)
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;
order_num items
--------- -----
20006 3
20007 5
20008 5
20009 3
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
order_num items
--------- -----
20006 3
20009 3
20007 5
20008 5
SELECT子句顺序
十一.使用子查询
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';
order_num
-----------
20007
20008
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');
cust_id
----------
1000000004
1000000005
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN ('1000000004','1000000005');
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'));
输出:
cust_name cust_contact
----------------------------- --------------------
Fun4All Denise L. Stephens
The Toy Store Kim Howard
十二.联结表
创建联结
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
vend_name prod_name prod_price
-------------------- -------------------- ----------
Doll House Inc. Fish bean bag toy 3.4900
Doll House Inc. Bird bean bag toy 3.4900
Doll House Inc. Rabbit bean bag toy 3.4900
Bears R Us 8 inch teddy bear 5.9900
Bears R Us 12 inch teddy bear 8.9900
Bears R Us 18 inch teddy bear 11.9900
Doll House Inc. Raggedy Ann 4.9900
Fun and Games King doll 9.4900
Fun and Games Queen doll 9.4900
内联结
上面使用的联结称为等值连接,它基于两个表之间的相等测试。这种联结也称为内联结(inner join)。其实,可以对这种联结使用稍微不同的语法,明确指定联结的类型。下面的 SELECT语句返回与前面例子完全相同的数据:
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
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;
prod_name vend_name prod_price quantity
--------------- ------------- ---------- --------
18 inch teddy bear Bears R Us 11.9900 50
Fish bean bag toy Doll House Inc. 3.4900 100
Bird bean bag toy Doll House Inc. 3.4900 100
Rabbit bean bag toy Doll House Inc. 3.4900 100
Raggedy Ann Doll House Inc. 4.9900 50
十三.创建高级联结
使用表别名
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';
使用不同类型的联结
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';
cust_id cust_name cust_contact
-------- -------------- --------------
1000000003 Fun4All Jim Jones
1000000004 Fun4All Denise L. Stephens
SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
外联结
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
cust_id order_num
---------- ---------
1000000001 20005
1000000001 20009
1000000002 NULL
1000000003 20006
1000000004 20007
1000000005 20008
SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;
十四.组合查询
组合查询
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');
cust_name cust_contact cust_email
----------- ------------- ------------
Village Toys John Smith sales@villagetoys.com
Fun4All Jim Jones jjones@fun4all.com
The Toy Store Kim Howard NULL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
cust_name cust_contact cust_email
----------- ------------- ------------
Fun4All Jim Jones jjones@fun4all.com
Fun4All Denise L. Stephens dstephens@fun4all.com
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
----------- ----------- ----------------
Fun4All Denise L. Stephens dstephens@fun4all.com
Fun4All Jim Jones jjones@fun4all.com
Village Toys John Smith sales@villagetoys.com
The Toy Store Kim Howard NULL
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';
cust_name cust_contact cust_email
----------- ------------- ------------
Village Toys John Smith sales@villagetoys.com
Fun4All Jim Jones jjones@fun4all.com
The Toy Store Kim Howard NULL
Fun4All Jim Jones jjones@fun4all.com
Fun4All Denise L. Stephens dstephens@fun4all.com
十五.数据插入
数据插入
INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
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);
插入部分行
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',
'11111',
'USA')
插入检索出的数据
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;
从一个表复制到另一个表
CREATE TABLE CustCopy AS
SELECT * FROM Customers;
十六.更新和删除数据
更新数据
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';
删除数据
DELETE FROM Customers
WHERE cust_id = '1000000006';
十七.创建和操作表
创建表
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
);
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
)
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
ALTER TABLE Vendors
DROP COLUMN vend_phone;
删除表
DROP TABLE CustCopy;
重命名表
RENAME oldname newname
十八.使用视图
视图
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';
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
为什么使用SQL
十九.管理事务处理
BEGIN TRANSACTION
...
COMMIT TRANSACTION
START TRANSACTION
...
SET TRANSACTION
...
使用ROLLBACK
DELETE FROM Orders;
ROLLBACK;
使用COMMIT
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION
使用保留点
SAVEPOINT delete1;
ROLLBACK TO delete1;