#过滤数据（where having）
SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >=4
GROUP BY vend_id
HAVING COUNT(*) >=2;
#过滤数据（group by having）
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >=2;
#排序
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;
/*
select 子句顺序
select
from
where
group by
having
order by
limit
select 子句执行顺序
from
where
group by
having
select
order by
limit
*/
#子查询
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'
));
#作为计算字段使用子查询
SELECT cust_name,cust_state,
(SELECT COUNT(*) FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
#创建联结
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.`vend_id`
#等值联结
SELECT vend_name,prod_name,prod_price
FROM vendors,products;
#内联结inner join on
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
/*
自然联结：R的每一项*S的每一项
内联结：保留复合ON条件的，不符合的都筛掉
左外联结：左边都保留，右边符合ON条件
右外联结：左边符合ON条件，右边都保留
全外联结：左右都保留，没有的为null
*/
#多表联结
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 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';
#左外联结
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
#右外联结
SELECT customers.cust_id,orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
#全外联结（MYSQL不支持）
SELECT customers.cust_id,orders.order_num
FROM customers FULL OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
#使用带聚集函数的联结
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;
#组合查询 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';
#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';
#对组合查询进行排序
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;
#插入数据（不带表名）
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_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT
cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
#从一个表复制到另一个表（Mysql）
CREATE TABLE custcopy AS
SELECT * FROM customers;
#更新数据
UPDATE customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
#更新多个列
UPDATE customers
SET
cust_name = 'zhangzhangchenchu'
cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
#删除某个列的值（设置为NULL）
UPDATE customers
SET cust_email = NULL
WHERE cust_id = '1000000005';
#删除某一行
DELETE FROM customers
WHERE cust_id = '1000000006';
#创建表 NULL可以省略
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);
#指定默认值 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);
#更新表增加列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
#更新表删除列
ALTER TABLE vendors
DROP COLUMN vend_phone;
#删除表
DROP TABLE custcopy;
#创建视图
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';
#用视图重新格式化检索出来的数据(mysql行不通)
CREATE VIEW vendorlocations AS
SELECT RTRIM(vend_name) + ' ('+ RTRIM(vend_country) + ')'
AS vend_title
FROM vendors;
SELECT *
FROM vendorlocations
#用视图过滤不想要的数据
CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;
SELECT *
FROM customeremaillist;
#使用视图与计算字段
CREATE VIEW orderitemsexpanded AS
SELECT prod_id,
quantity,
item_price,
order_num,
quantity*item_price AS expanded_price
FROM orderitems;
SELECT *
FROM orderitemsexpanded
WHERE order_num = 20008;
19章 使用存储过程
20章 管理事务处理
21章使用游标
之后再学习