#第二课 检索数据
#2.2 检索单个列
SELECT prod_name
FROM products;
#2.3 检索多个列
SELECT prod_id, prod_name, prod_price #检索多个列时,列名之间以逗号分隔;
FROM products;
#2.4 检索所有列
SELECT * FROM customers;
SELECT * FROM orderitems;
SELECT * FROM orders;
SELECT * FROM products;
SELECT * FROM vendors;
#2.5 检索不同的值
SELECT DISTINCT vend_id, prod_name
FROM products;
#由于指定的两列(vend_id, prod_name)不完全相同,因此所有的行都会被检索出来;
#2.6 限制结果
SELECT prod_name
FROM products
LIMIT 5 OFFSET 5; #返回从第5行起的5行数据; 第一个数字是检索的行数,第二个数字是指从哪儿开始;
# 注意:第一个被检索的行是第0行
#2.7使用注释
注释的三种形式:1.使用2个连字符 --SELECT
2.使用井号键 #SELECT
3.使用/* SELECT */ 这种方式常用语给代码加注释
#第三课 排序检索数据
#3.1 排序数据
SELECT prod_name
FROM products
ORDER BY prod_name; #ORDER BY应该保证它是SELECT语句中的最后一条字句;
#3.2 按多个列排序
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name;
#3.3 按列的位置排序
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY 2,3;
#3.4 指定排序方向
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC;
#多个列排序 此语句只对prod_price进行降序排序,对prod_name仍采用升序排序
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;
#多个列排序 如果想在多个列上进行降序排序,必须对每一列指定DESC关键字
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name DESC;
#第四课 过滤数据
#4.1 使用WHERE字句
SELECT prod_name,prod_price
FROM products
WHERE prod_price = 3.49;
#4.2 WHERE字句操作符
= 等于;<> or != 不等于;< 小于;<= 小于等于;!< 不小于; BETWEEN 在指定的两个值之间;IS NULL 为NULL值;
#4.2.1 检查单个值
SELECT prod_name,prod_price
FROM products
WHERE prod_price < 10;
#4.2.2 不匹配检查
SELECT vend_id,prod_name
FROM products
WHERE vend_id <> 'DLL01'; #单引号用来限定字符串
#4.2.3 范围值检查
SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
#4.2.4 空值检查
SELECT prod_name
FROM products
WHERE prod_price IS NULL;
SELECT *
FROM customers
WHERE cust_email IS NULL;
#第五课 高级过滤数据
#5.1 组合WHERE字句
#5.1.1 AND操作符
SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4 AND prod_id = 'BNBG01'; #可以多次使用AND来增加筛选条件
#5.1.2 OR操作符
SELECT prod_name,prod_price,vend_id
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
#5.1.3 求值顺序
# WHERE与OR 操作符同时存在时,一般优先处理AND操作符
SELECT prod_name,prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;
#此时优先处理AND操作符,WHERE字句等同于 WHERE vend_id = 'DLL01' OR (vend_id = 'BRS01' AND prod_price >= 10);
#解决方法为加上圆括号(),如下:
SELECT prod_name,prod_price
FROM products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;
#5.2 IN操作符
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN ( 'DLL01','BRS01')
ORDER BY prod_name;
# IN操作符可以完成与OR相同的功能,相比OR,IN有如下优点:
#1.在有很多合法选项时,IN操作符的语法更清楚、直观;
#2.在与其他AND和OR操作符组合使用IN时,求值顺序更容易管理;
#http://3.IN操作符一般比一组OR操作符执行得更快;
#http://4.IN的最大优点是可以包含其他SELECT语句,能够更动态地建立WHERE字句。第11课会对此详细介绍;
#5.3 NOT操作符 WHERE字句中用来否定其后条件的关键字
SELECT *
FROM products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
#也可以用<>操作符来完成,如下
SELECT *
FROM products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;
#第六课 用通配符进行过滤
#6.1 LIKE操作符
#为了在搜索字句中使用通配符,必须使用LIKE操作符;
#通配符搜索只能用于文本字段(字符串),非文本数据类型字段不能使用通配符搜索;
#6.1.1 百分号(%)通配符 %表示任何字符出现任意次数,可以是0,1或者多个字符;
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'Fish%'; #所有以词Fish起头的产品
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '%bean bag%';
#注意NULL,%不能查找出值为NULL的值, WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行;
#6.1.2下划线(_)通配符 只用来匹配单个字符
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '__ inch teddy bear'; #有2个下划线
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '% inch teddy bear';
#6.13方括号([])通配符
#[]表示必须匹配通配符中的一个字符 如:
#找出所有名字以J或者M起头的联系人:
SELECT *
FROM customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
#第7课 创建计算字段
#7.1 计算字段的概念
#7.2 拼接字段
SELECT *
FROM vendors
#使用操作符加号(+)或两个竖杠(||) Acess和SQL Server使用+号;DB2,Oracle,PostgreSQL,SQLite等使用||;
# SELECT vend_name +'(' + vend_country + ')'
# FROM vendors
# ORDER BY vend_name;
# RTRIM()函数为去掉值右边的所有空格,LTRIM()函数为去掉值左边的所有空格,TRIM()函数为去掉值两边的所有空格;
# SELECT RTRIM(vend_name) +'(' + RTRIM(vend_country) + ')'
# FROM vendors
# ORDER BY vend_name;
#在MySQL或者MariaDB时需要使用Concat,如下:
SELECT Concat(vend_name,'(' ,vend_country , ')') as pinjie
FROM vendors
ORDER BY vend_name;
#使用别名
#别名用关键字 AS 赋予,也可省略 AS;
#7.3 执行算术计算 加减乘除 对应于 +,-,*,/
SELECT *
FROM orderitems
WHERE order_num = 20008;
SELECT prod_id,quantity,item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20008;
#第8课 使用函数处理数据
#不同数据库支持的函数各有不同,同一函数效果不同数据库也可能有不同的表达方式,具体需要查阅文档
#8.2 使用函数
#8.2.1 文本处理函数
#UPPER()将本文转化为大写
SELECT vend_name,UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
#8.2.2 日期和时间处理函数
#Mysql中可使用名为YEAR()的函数从日期中提取年份
SELECT *
FROM orders
WHERE YEAR(order_date) = 2012;
#8.2.3 数值处理函数
#常见的数值处理函数包括如下:
# ABS() 返回一个数的绝对值
# COS() 返回一个角度的余弦
#其他函数请查阅文档
#第9课 汇总数据
#9.1 聚集函数
#9.1.1 AVG()函数 返回某列的平均值
SELECT AVG(prod_price) AS avg_price
FROM products;
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';
#AVG()函数只能用来确定特定数值列的平均值,列名为函数参数
#为了获得多个列的平均值,必须使用多个AVG()函数
#AVG()函数忽略列值为NULL的行
#9.1.2 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()函数 返回某列的最大值
SELECT MAX(prod_price) AS max_price
FROM products;
#9.1.4 MIN()函数 返回某列的最小值
SELECT MIN(prod_price) AS max_price
FROM products;
#9.1.5 SUM()函数 返回某列值之和
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
SELECT SUM(quantity*item_price) AS total_price
FROM orderitems
WHERE order_num = 20005;
#9.2 聚集不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 'DLL01';
#DISTINT不能用于COUNT(*)
#9.3 组合聚集函数
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_mas,
AVG(prod_price) AS price_avg
FROM products;
#第10课 分组数据
#10.2 创建分组
SELECT *
FROM products;
SELECT vend_id,prod_price,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id,prod_price;
#GROUP BY有许多重要的规定,包括:
#除了聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY字句中给出
#GROUP BY字句必须出现在WHERE字句之后,ORDER BY字句之前
#更多请详见P86
#10.3 过滤分组 利用HAVING字句
SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
#HAVING和WHERE的差别:
#一种可能的解释是:WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤
SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;
#10.4 分组和排序
SELECT order_num,COUNT(*) AS items
FROM orderitems
GROUP BY order_num
HAVING COUNT(*) >= 3;
#要按订购物品的数目排序输出,需要添加ORDER BY字句,如下:
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.2 利用子查询进行过滤
#假如要列出订购物品RGAN01的所有顾客,可遵循如下步骤:
#1.检索包含物品RGAN01的所有订单的编号;
#2.检索具有前一步骤列出的订单编号的所有顾客的ID;
#3.检索前一步骤返回的所有顾客ID的顾客信息;
#前两步代码如下:
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 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'));
#11.3作为计算字段使用子查询
SELECT *
FROM customers;
SELECT *
FROM orders;
#假如需要显示customers表中每个顾客的订单总数,操作如下:
#1.从customers表中检索顾客列表;
#2.对于检索出的每个顾客,统计其在orders表中的订单数目;
SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = '1000000001';
#要对每个顾客执行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;
#注意orders.cust_id=customers.cust_id,在列名前加上表名,以免混淆列名;
#第12课 联结表
#12.2 创建联结
#等值联结:
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id;
#12.2.1 WHERE字句的重要性
#小心笛卡尔积
SELECT vend_name,prod_name,prod_price
FROM vendors,products;
#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 联结多个表
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;
#第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 自联结
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';
#13.2.2 自然联结
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';
#13.2.3 外联结
SELECT * FROM customers;
SELECT * FROM orders;
#检索包括没有订单顾客在内的所有顾客:
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
#使用OUTER JOIN语法时,必须使用RIGHT或者LEFT来指定包括其所有行的表
#(RIGHT 指出的是OUTER JOIN右边的表 包括所有的行,LEFT则指左边)
#13.3 使用带聚集函数的联结
SELECT customers.cust_id,
COUNT(orders.order_num) AS num_222
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
#与外联结使用
SELECT customers.cust_id,
COUNT(orders.order_num) AS num_222
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
#第14课 组合查询
#主要有两种情况需要使用组合查询:
#1.在一个查询中从不同的表返回结构数据
#2.对一个表执行多个查询,按一个查询返回数据
#14.2 创建组合查询
#14.2.1 使用UNION
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';
#使用UNION组合查询:
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';
#如下为使用多条WHERE字句而不是UNION的相同查询:
SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN ('IL','IN','MI')
OR cust_name = 'Fun4All';
#14.2.2 UNION规则
#P126
#每个查询必须包含相同的列、表达式或聚集函数
#14.2.3 包含或取消重复的行
#使用UNION ALL会把重复的行也检索出来
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 对组合查询结果排序
#组合查询时只能有一条ORDER BY字句,且位于最后一条
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;
第二部分:在数据库中创建表及输入数据:
CREATE TABLE Customers
(
cust_id char(10) NOT NULL ,
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_contact char(50) NULL ,
cust_email char(255) NULL
);
####################################################
CREATE TABLE OrderItems
(
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
);
####################################################
CREATE TABLE Orders
(
order_num int NOT NULL ,
order_date datetime NOT NULL ,
cust_id char(10) NOT NULL
);
####################################################
CREATE TABLE Products
(
prod_id char(10) NOT NULL ,
vend_id char(10) NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL
);
####################################################
CREATE TABLE Vendors
(
vend_id char(10) NOT NULL ,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL
);
####################################################
-- Define primary keys
-- -------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);
-- -------------------
-- Define foreign keys 定义外键
-- -------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);
####################################################
-- Populate Customers table
-- ------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)