SQL查询
/*终于到了select查询了，，，，，，*/--一.执行顺序：
-- →→→from→→→where→→→group by→→→having→→→数据计算（包括统计函数）→→→distinct→→→union→→→order by
-- 二.书写顺序
--→→→distinct→→→数据计算（包括统计函数）→→→from→→→where→→→group by→→→having→→→order by→→→union
select num,course,sum(score) from grade group by course; -- group by先执行,然后sum，
--这里采用《SQL:the complete reference》作为参考
--乱七八糟的表太多，创建一个新的实验数据库
create database SQLcompleteReference1 default character set utf_8 --collate utf8_bin--设置默认字符集为utf8，校对规则为utf8_bin，且校对规则不需指定，这里不再需要,且MySQL5.6已经改成 utf8_general_ci;默认字符集对于一个响应的校对规则 ---在IDE下无法设置默认字符集,需在MySQL命令行下执行，另外应在my.ini文件里面设置默认的字符集为utf8便省去此处的字符集设置
create database SQLcompleteReference
show charset
show databases
drop database SQLcompleteReference1
use SQLcompleteReference
CREATE TABLE PRODUCTS
(MFR_ID CHAR(3) NOT NULL,
PRODUCT_ID CHAR(5) NOT NULL,
DESCRIPTION VARCHAR(20) NOT NULL,
PRICE DECIMAL(9,2) NOT NULL,
QTY_ON_HAND INTEGER NOT NULL,
PRIMARY KEY (MFR_ID, PRODUCT_ID));
CREATE TABLE OFFICES
(OFFICE INTEGER NOT NULL,
CITY VARCHAR(15) NOT NULL,
REGION VARCHAR(10) NOT NULL,
MGR INTEGER,
TARGET DECIMAL(9,2),
SALES DECIMAL(9,2) NOT NULL,
PRIMARY KEY (OFFICE));
CREATE TABLE SALESREPS
(EMPL_NUM INTEGER NOT NULL,
CHECK (EMPL_NUM BETWEEN 101 AND 199),
NAME VARCHAR(15) NOT NULL,
AGE INTEGER,
REP_OFFICE INTEGER,
TITLE VARCHAR(10),
HIRE_DATE DATE NOT NULL,
MANAGER INTEGER,
QUOTA DECIMAL(9,2),
SALES DECIMAL(9,2) NOT NULL,
PRIMARY KEY (EMPL_NUM),
FOREIGN KEY (MANAGER)
REFERENCES SALESREPS(EMPL_NUM)
ON DELETE SET NULL,
FOREIGN KEY WORKSIN (REP_OFFICE)
REFERENCES OFFICES(OFFICE)
ON DELETE SET NULL);
CREATE TABLE CUSTOMERS
(CUST_NUM INTEGER NOT NULL,
COMPANYVARCHAR(20) NOT NULL,
CUST_REP INTEGER,
CREDIT_LIMIT DECIMAL(9,2),
PRIMARY KEY (CUST_NUM),
FOREIGN KEY HASREP (CUST_REP)
REFERENCES SALESREPS(EMPL_NUM)
ON DELETE SET NULL);
CREATE TABLE ORDERS
(ORDER_NUM INTEGER NOT NULL,
CHECK (ORDER_NUM > 100000),
ORDER_DATE DATE NOT NULL,
CUST INTEGER NOT NULL,
REP INTEGER,
MFR CHAR(3) NOT NULL,
PRODUCT CHAR(5) NOT NULL,
QTY INTEGER NOT NULL,
AMOUNT DECIMAL(9,2) NOT NULL,
PRIMARY KEY (ORDER_NUM),
FOREIGN KEY PLACEDBY (CUST)
REFERENCES CUSTOMERS(CUST_NUM)
ON DELETE CASCADE,
FOREIGN KEY TAKENBY (REP)
REFERENCES SALESREPS(EMPL_NUM)
ON DELETE SET NULL,
FOREIGN KEY ISFOR (MFR, PRODUCT)
REFERENCES PRODUCTS(MFR_ID, PRODUCT_ID)
ON DELETE RESTRICT);
ALTER TABLE OFFICES
ADD CONSTRAINT HASMGR
FOREIGN KEY (MGR) REFERENCES SALESREPS(EMPL_NUM)
ON DELETE SET NULL;
--- 插入数据
delete from orders;
delete from customers;
update offices set mgr=null;
delete from salesreps;
delete from offices;
delete from products;
commit;
---
---PRODUCTS
---
INSERT INTO PRODUCTS VALUES('REI','2A45C','Ratchet Link',79.00,210);
INSERT INTO PRODUCTS VALUES('ACI','4100Y','Widget Remover',2750.00,25);
INSERT INTO PRODUCTS VALUES('QSA','XK47 ','Reducer',355.00,38);
INSERT INTO PRODUCTS VALUES('BIC','41627','Plate',180.00,0);
INSERT INTO PRODUCTS VALUES('IMM','779C ','900-LB Brace',1875.00,9);
INSERT INTO PRODUCTS VALUES('ACI','41003','Size 3 Widget',107.00,207);
INSERT INTO PRODUCTS VALUES('ACI','41004','Size 4 Widget',117.00,139);
INSERT INTO PRODUCTS VALUES('BIC','41003','Handle',652.00,3);
INSERT INTO PRODUCTS VALUES('IMM','887P ','Brace Pin',250.00,24);
INSERT INTO PRODUCTS VALUES('QSA','XK48 ','Reducer',134.00,203);
INSERT INTO PRODUCTS VALUES('REI','2A44L','Left Hinge',4500.00,12);
INSERT INTO PRODUCTS VALUES('FEA','112','Housing',148.00,115);
INSERT INTO PRODUCTS VALUES('IMM','887H ','Brace Holder',54.00,223);
INSERT INTO PRODUCTS VALUES('BIC','41089','Retainer',225.00,78);
INSERT INTO PRODUCTS VALUES('ACI','41001','Size 1 Wiget',55.00,277);
INSERT INTO PRODUCTS VALUES('IMM','775C ','500-lb Brace',1425.00,5);
INSERT INTO PRODUCTS VALUES('ACI','4100Z','Widget Installer',2500.00,28);
INSERT INTO PRODUCTS VALUES('QSA','XK48A','Reducer',177.00,37);
INSERT INTO PRODUCTS VALUES('ACI','41002','Size 2 Widget',76.00,167);
INSERT INTO PRODUCTS VALUES('REI','2A44R','Right Hinge',4500.00,12);
INSERT INTO PRODUCTS VALUES('IMM','773C ','300-lb Brace',975.00,28);
INSERT INTO PRODUCTS VALUES('ACI','4100X','Widget Adjuster',25.00,37);
INSERT INTO PRODUCTS VALUES('FEA','114','Motor Mount',243.00,15);
INSERT INTO PRODUCTS VALUES('IMM','887X ','Brace Retainer',475.00,32);
INSERT INTO PRODUCTS VALUES('REI','2A44G','Hinge Pin',350.00,14);
commit;
---
---OFFICES
---
INSERT INTO OFFICES VALUES(22,'Denver','Western',null,300000.00,186042.00);
INSERT INTO OFFICES VALUES(11,'New York','Eastern',null,575000.00,692637.00);
INSERT INTO OFFICES VALUES(12,'Chicago','Eastern',null,800000.00,735042.00);
INSERT INTO OFFICES VALUES(13,'Atlanta','Eastern',null,350000.00,367911.00);
INSERT INTO OFFICES VALUES(21,'Los Angeles','Western',null,725000.00,835915.00);
commit;
---
---SALESREPS
---
INSERT INTO SALESREPS VALUES (106,'Sam Clark',52,11,'VP Sales','2006-06-14',null,275000.00,299912.00);
INSERT INTO SALESREPS VALUES (109,'Mary Jones',31,11,'Sales Rep','2007-10-12',106,300000.00,392725.00);
INSERT INTO SALESREPS VALUES (104,'Bob Smith',33,12,'Sales Mgr','2005-05-19',106,200000.00,142594.00);
INSERT INTO SALESREPS VALUES (108,'Larry Fitch',62,21,'Sales Mgr','2007-10-12',106,350000.00,361865.00);
INSERT INTO SALESREPS VALUES (105,'Bill Adams',37,13,'Sales Rep','2006-02-12',104,350000.00,367911.00);
INSERT INTO SALESREPS VALUES (102,'Sue Smith',48,21,'Sales Rep','2004-12-10',108,350000.00,474050.00);
INSERT INTO SALESREPS VALUES (101,'Dan Roberts',45,12,'Sales Rep','2004-10-20',104,300000.00,305673.00);
INSERT INTO SALESREPS VALUES (110,'Tom Snyder',41,null,'Sales Rep','2008-01-13',101,null,75985.00);
INSERT INTO SALESREPS VALUES (103,'Paul Cruz',29,12,'Sales Rep','2005-03-01',104,275000.00,286775.00);
INSERT INTO SALESREPS VALUES (107,'Nancy Angelli',49,22,'Sales Rep','2006-11-14',108,300000.00,186042.00);
commit;
---
--- OFFICE MANAGERS
---
UPDATE OFFICES SET MGR=108 WHERE OFFICE=22;
UPDATE OFFICES SET MGR=106 WHERE OFFICE=11;
UPDATE OFFICES SET MGR=104 WHERE OFFICE=12;
UPDATE OFFICES SET MGR=105 WHERE OFFICE=13;
UPDATE OFFICES SET MGR=108 WHERE OFFICE=21;
commit;
---
--- CUSTOMERS
---
INSERT INTO CUSTOMERS VALUES(2111,'JCP Inc.',103,50000.00);
INSERT INTO CUSTOMERS VALUES(2102,'First Corp.',101,65000.00);
INSERT INTO CUSTOMERS VALUES(2103,'Acme Mfg.',105,50000.00);
INSERT INTO CUSTOMERS VALUES(2123,'Carter & Sons',102,40000.00);
INSERT INTO CUSTOMERS VALUES(2107,'Ace International',110,35000.00);
INSERT INTO CUSTOMERS VALUES(2115,'Smithson Corp.',101,20000.00);
INSERT INTO CUSTOMERS VALUES(2101,'Jones Mfg.',106,65000.00);
INSERT INTO CUSTOMERS VALUES(2112,'Zetacorp',108,50000.00);
INSERT INTO CUSTOMERS VALUES(2121,'QMA Assoc.',103,45000.00);
INSERT INTO CUSTOMERS VALUES(2114,'Orion Corp.',102,20000.00);
INSERT INTO CUSTOMERS VALUES(2124,'Peter Brothers',107,40000.00);
INSERT INTO CUSTOMERS VALUES(2108,'Holm & Landis',109,55000.00);
INSERT INTO CUSTOMERS VALUES(2117,'J.P. Sinclair',106,35000.00);
INSERT INTO CUSTOMERS VALUES(2122,'Three Way Lines',105,30000.00);
INSERT INTO CUSTOMERS VALUES(2120,'Rico Enterprises',102,50000.00);
INSERT INTO CUSTOMERS VALUES(2106,'Fred Lewis Corp.',102,65000.00);
INSERT INTO CUSTOMERS VALUES(2119,'Solomon Inc.',109,25000.00);
INSERT INTO CUSTOMERS VALUES(2118,'Midwest Systems',108,60000.00);
INSERT INTO CUSTOMERS VALUES(2113,'Ian & Schmidt',104,20000.00);
INSERT INTO CUSTOMERS VALUES(2109,'Chen Associates',103,25000.00);
INSERT INTO CUSTOMERS VALUES(2105,'AAA Investments',101,45000.00);
commit;
---
---ORDERS
---
INSERT INTO ORDERS VALUES (112961,'2007-12-17',2117,106,'REI','2A44L',7,31500.00);
INSERT INTO ORDERS VALUES (113012,'2008-01-11',2111,105,'ACI','41003',35,3745.00);
INSERT INTO ORDERS VALUES (112989,'2008-01-03',2101,106,'FEA','114',6,1458.00);
INSERT INTO ORDERS VALUES (113051,'2008-02-10',2118,108,'QSA','XK47',4,1420.00);
INSERT INTO ORDERS VALUES (112968,'2007-10-12',2102,101,'ACI','41004',34,3978.00);
INSERT INTO ORDERS VALUES (113036,'2008-01-30',2107,110,'ACI','4100Z',9,22500.00);
INSERT INTO ORDERS VALUES (113045,'2008-02-02',2112,108,'REI','2A44R',10,45000.00);
INSERT INTO ORDERS VALUES (112963,'2007-12-17',2103,105,'ACI','41004',28,3276.00);
INSERT INTO ORDERS VALUES (113013,'2008-01-14',2118,108,'BIC','41003',1,652.00);
INSERT INTO ORDERS VALUES (113058,'2008-02-23',2108,109,'FEA','112',10,1480.00);
INSERT INTO ORDERS VALUES (112997,'2008-01-08',2124,107,'BIC','41003',1,652.00);
INSERT INTO ORDERS VALUES (112983,'2007-12-27',2103,105,'ACI','41004',6,702.00);
INSERT INTO ORDERS VALUES (113024,'2008-01-20',2114,108,'QSA','XK47',20,7100.00);
INSERT INTO ORDERS VALUES (113062,'2008-02-24',2124,107,'FEA','114',10,2430.00);
INSERT INTO ORDERS VALUES (112979,'2007-10-12',2114,102,'ACI','4100Z',6,15000.00);
INSERT INTO ORDERS VALUES (113027,'2008-01-22',2103,105,'ACI','41002',54,4104.00);
INSERT INTO ORDERS VALUES (113007,'2008-01-08',2112,108,'IMM','773C',3,2925.00);
INSERT INTO ORDERS VALUES (113069,'2008-03-02',2109,107,'IMM','775C',22,31350.00);
INSERT INTO ORDERS VALUES (113034,'2008-01-29',2107,110,'REI','2A45C',8,632.00);
INSERT INTO ORDERS VALUES (112992,'2007-11-04',2118,108,'ACI','41002',10,760.00);
INSERT INTO ORDERS VALUES (112975,'2007-10-12',2111,103,'REI','2A44G',6,2100.00);
INSERT INTO ORDERS VALUES (113055,'2008-02-15',2108,101,'ACI','4100X',6,150.00);
INSERT INTO ORDERS VALUES (113048,'2008-02-10',2120,102,'IMM','779C',2,3750.00);
INSERT INTO ORDERS VALUES (112993,'2007-01-04',2106,102,'REI','2A45C',24,1896.00);
INSERT INTO ORDERS VALUES (113065,'2008-02-27',2106,102,'QSA','XK47',6,2130.00);
INSERT INTO ORDERS VALUES (113003,'2008-01-25',2108,109,'IMM','779C',3,5625.00);
INSERT INTO ORDERS VALUES (113049,'2008-02-10',2118,108,'QSA','XK47',2,776.00);
INSERT INTO ORDERS VALUES (112987,'2007-12-31',2103,105,'ACI','4100Y',11,27500.00);
INSERT INTO ORDERS VALUES (113057,'2008-02-18',2111,103,'ACI','4100X',24,600.00);
INSERT INTO ORDERS VALUES (113042,'2008-02-20',2113,101,'REI','2A44R',5,22500.00);
commit;
---开始练习
use SQLcompleteReference
--列出销售点，销售点的销售目标和实际销量
select city,target,sales from offices
-- 查看Office表的情况
select * from offices
-- 列出东部地区销售点的销售目标和销售额
select city,target,sales
from offices
where region='Eastern' -- 大小写敏感
-- 列出东部地区其销售量超过了销售目标的销售点，以城市的字母顺序排序
select city,target,sales
from offices
where region='Eastern' and target5000000;
-- 列出销售人员，他们的定额，和他们的经理 select * from salesreps;
select name,quota,manager
from salesreps
-- 列出每个销售点的地点，地区和销售量 select * from offices;
drop table if exists girls,boys,parents -- 删除不相干的表
select city,region,sales from offices
--- 列出每个销售点所在的城市，地区和超过/低于销售目标的数量
select city,region,(sales-target)
from offices;
-- 显示每个产品货物清单的总价 select * from products;
select product_id,description,(price*qty_on_hand) from products
-- 显示提高销售人员的定额达到他们迄今为止销售量的3%后的结果 select * from salesreps;
select name,quota+(0.03*sales) as new_quota,sales from salesreps;--不可以将3%直接加入运算，需转换成小数制
-- 列出每个销售人员的名字和并将受雇年和月分开两列
select name,year(hire_date),month(hire_date)
from salesreps
-- 列出每个城市的销售量,中间用’has sales of‘连接select * from offices;
select city,'has sales of',sales from offices --正确显示，三列分别为city,has sales of(列的内容全为hai sales of),sales
select city,'has sales of' sales from offices--只有两列,分别为city,sales(列的内容全为has sales of)，值得注意和活用
--ANSI/ISO SQL标准规定select语句要么可以选择所有字段，要么有一个选择列表，但不能同时拥有，然而许多SQL产品都将*看成选择列表的一个元素，
select *,(sales-target) from offices;-- 此例中选择出了表中的所有项，并将（sales-target）列在最后一列
--列出所有销售点经理的雇员号
select mgr from offices;
select distinct mgr from offices; --去除查询结果中的重复元组
--显示所有销售量超过销售目标的销售点 select * from offices;
select city ,sales,target
from offices
where sales>target
--显示雇员号为105的雇员名字,销售量和定额select * from salesreps
select name,sales,quota
from salesreps
where empl_num = 105
--显示被雇员号为104管理的雇员
select name,title,manager
from salesreps
where manager =104 --nuLl值和非104被排除
--显示被Bob Smith管理的雇员
select name,title,manager
from salesreps
-- where manger in (select manager from salesreps where name ='Bob Smith')--manager少了a一直报错
where manager in (select manager from salesreps where name ='Bob Smith')-- 写法一
-- 写法二 where manager = (select manager from salesreps where name ='Bob Smith')
-- 写法三 where manager like (select manager from salesreps where name ='Bob Smith')
--求出在2006年以前被雇佣的销售人员select * from salesreps
select name,age,hire_date,'is earlier than','2006' the_year
from salesreps
where hire_date 2000
union -- union操作默认自动消除重复记录，若要包含重复的记录，需加all参数
select distinct mfr,product from orders where amount >30000
order by 1,2--union的运算顺序在having之后，order by 之前
--- 多个union
select * from a
union
( select * from b
union
select * from c);
--括号指示应先执行哪个UNION操作
/*事实上，如果语句中所有的UNION消除了重复的记录,或者都保留了重复的记录，那么它们的执行顺序并不重要
即UNION遵循交换律,如果同时涉及到union和union all，计算的顺序是需要考虑的
*/
use sqlcompletereference
-- 多表查询
--- 两表查询,FROM a,b WHERE a.m=b.n
SELECT a.x,b.y
FROM a,b WHERE a.m=b.n
-- 列出所有的订单，显示订单号和数额，下订单的客户的名字和信用额度 select *from orders;select* from customers
select o.order_num,o.qty,c.company,c.credit_limit
from customers c ,orders o
where o.cust=c.cust_num
-- 列出每名销售人员和他们工作的城市和地区select *from salesreps;select* from offices
select r.name,o.region,o.city
from salesreps r,offices o
where r.rep_office=o.office;
-- 列出每个销售点及其经理的名字和头衔
selecto.office,r.name,r.title
from salesreps r,offices o
where o.mgr=r.empl_num;
-- 列出每个销售点及其经理的名字和头衔,且销售目标超过600,000美元的销售点
select offices.office,salesreps.name,salesreps.title,offices.target
from salesreps,offices
where offices.mgr=salesreps.empl_num
and target >600000
---用JOIN的方法,
SELECT a.x,b.y
FROM a JOIN b ON a.m=b.n
--- 列出每名销售人员和他们工作的城市和地区select *from salesreps;select* from offices
select name,region,city
from salesreps
join offices on salesreps.rep_office=offices.office;
--- 列出每个销售点及其经理的名字和头衔
select office,name,title
from salesreps
join offices on
offices.mgr=salesreps.empl_num
--- 列出每个销售点及其经理的名字和头衔,且销售目标超过600,000美元的销售点
use sqlcompletereference;
select office,name,title,target
from salesreps
join offices on
offices.mgr=salesreps.empl_num
and target>600000
---多个匹配字段
--- 列出所有订单，显示金额和产品说明select * from products;select * from orders
select orders.order_num,orders.amount,products.description
from orders
join products
on orders.product=products.product_id
and products.mfr_id=orders.mfr
-- 自然连接
--- 两个表间最自然的连接会是一个基于出现在两个表中的所有字段名的等连接
SELECT ORDER_NUM,AMOUNT,DESCRIPTION
FROM ORDERS NATURAL JOIN PRODUCTS;--语句告诉DBMS根据两个表中有着相同名称的所有字段连接ORDERS表和PRODUCTS表
--- 也可以显式地指定匹配的字段
SELECT ORDER_NUM,AMOUNT,DESCRIPTION
FROM ORDERS JOIN PRODUCTS
USING (MFR,PRODUCT);
-- 三个表或更多表的查询
--- 列出超过25,000美元的订单，包括取得订单销售人员的名字和下订单的客户的名字 select * from orders;select*from customers;select * from salesreps
select orders.order_num,orders.order_date,orders.amount,salesreps.name,customers.company
from orders,customers,salesreps
where orders.cust=customers.cust_num
and orders.rep=salesreps.empl_num
and orders.amount>25000
--- JOIN的方式
select orders.order_num,orders.order_date,orders.amount,salesreps.name,customers.company
from orders
join customers on orders.cust=customers.cust_num
join salesreps on orders.rep=salesreps.empl_num
where orders.amount>25000
--- 列出价值超过25,000元的订单，显示下订单的客户名和负责那位客户的销售人员的名字
select orders.order_num,orders.order_date,orders.amount,salesreps.name,customers.company
from orders,customers,salesreps
where orders.cust=customers.cust_num
and customers.cust_rep=salesreps.empl_num
and orders.amount>25000
--- 列出价值超过25000美元的订单，显示下订单的客户名，负责客户的销售人员的名字和销售人员工作的销售点
select orders.order_num,orders.order_date,orders.amount,salesreps.name,offices.city
from orders,customers,salesreps,offices
where orders.cust=customers.cust_num
and customers.cust_rep=salesreps.empl_num
and salesreps.rep_office=offices.office
and orders.amount>25000
-- 非主键外键相关的等连接
--- 找出一名新的销售人员在被雇用的当天收到的订单select * from orders;select*from salesreps
select salesreps.name,salesreps.hire_date,orders.order_date,order_num
from orders,salesreps
where order_date=hire_date
-- 不等连接
--- 列出所有销售人员和销售点的组合，其中销售人员的定额大于销售点的销售目标，select*from offices;select*from salesreps
select name,city,target as rep_target,quota as office_target
from offices,salesreps
where target>quota
and offices.office=salesreps.rep_office
-- 选择某个表中的全部字段，SELECT * 在多表查询中的使用
--- 列出销售人员表的所有信息和他们工作的地点
select salesreps.* ,offices.city
from salesreps,offices
where offices.office=salesreps.rep_office;
-- 自连接
--- 列出销售人员及其经理的名字
select employee.name,manager.name as leader
from salesreps as employee,salesreps as manager
where employee.manager=manager.empl_num
order by leader
--- 列出其销售定额比其经理的定额高的销售人员
select salesreps.name,salesreps.quota,manager.name as manager,manager.quota as quota_M -- 不可以省略主表的前缀，否则会产生字段混淆，报错
from salesreps,salesreps as manager
where salesreps.manager=manager.empl_num --不可以省略表之间的内连接，否则会出现无意义且重复的数据
and salesreps.quota>manager.quota-- 不可以省略主表的前缀，否则会产生字段混淆，报错,且不可以以别名来进行比较和计算
--- 列出与其经理不在一个销售地点工作的销售人员，显示他们的名字和他们工作的销售地点
select salesreps.name,offices.city,offices_M.city as rep_officeM
from salesreps,offices,salesreps as manager,offices as offices_M
where salesreps.manager=manager.empl_num
and salesreps.rep_office=offices.office
and offices_M.office=manager.rep_office
and salesreps.rep_officemanager.rep_office
---用join的方式
--
--
--
--
--
--
-- 编辑器自动FC,丢失好多进度
--
--
--
--
--
--
--
--
--- 列出销售人员和他们工作所在的城市
select name,city
from salesreps left join offices
on salesreps.rep_office=offices.office
--- 列出每个销售点和在销售点工作的人员
select city,name
from offices left outer join salesreps
on salesreps.rep_office=offices.office
-- 汇总查询
---AVG平均值
--- 求出销售人员的平均销售量和平均销售定额select*from salesreps;select*from orders
select avg(sales),avg(quota)
from salesreps
--- 求销售人员的销售量占销售定额的百分比的平均值
select 'sale/quota' as property,100*avg(sales/quota) as percent
from salesreps
--- 求制造商ACI生产的的产品的平均价格select * from products
select mfr_id,avg(price)
from products
where mfr_id='aci'
--- 计算Acme Mfg所下订单的平均数额select * from customers
--- 更正表中因'Acme Mfg.'多了一个点而查不到数据的问题
updatecustomers set company ='Acme Mfg' where cust_num=2103--- ★★★★update没有from★★★★★
select 'Acme Mfg'as name,avg(amount)
from orders
where cust in (select cust_num from customers where company='Acme Mfg')
--- SUM求和use sqlcompletereference
--- 求所有销售人员的总销售量和总定额
select sum(sales),sum(quota)
from salesreps
--- 求Bill Adams获得的订单总和
select sum(amount)
from orders
where rep in (select empl_num from salesreps where name='Bill Adams')
--- 查找最小值和最大值
--- 求销售代表们被分配的最小和最大定额select*from salesreps;
select min(quota),max(quota)
from salesreps
--- 求数据库中最早产生的订单select*from orders
select min(order_date) fromorders
select min(order_date),order_num from orders---非函数项列将自动取第一个数组的对应属性的对应值
select order_num,order_date,qty,amount from orders where order_date in min(order_date)
-- 求出最大(销售量占销售定额的百分比)
SELECT MAX(100*sales/quota)
FROM salesreps
-- 计数函数COUNT()
-- count(CONSTRAINT)的效率和结果都相同，都会选出所有的行数
-- COUNT(列名)会选出非空的列数，即NULL值不计数
-- 如果某个表上Count（*)用的比较多时，考虑在一个最短的列建立一个单列索引，会极大的提升性能
-- 求客户数
SELECT COUNT(*)
FROM customers
-- 求销售人员中销售量超过定额的人员数量
SELECT COUNT(*)
FROM salesreps
WHERE sales>quota
-- 求数值超过25,000的订单数目
SELECT COUNT(*)
FROM orders
WHERE amount>25000
-- 小测试 单表select出来的数据有重复行的情况下，再进行union操作，会不会去除单表select结果中的重复行
CREATE TABLE test1(id INT PRIMARY KEY,-- 创建表的时候字段之间使用‘,’分隔而不是结束的';'
name VARCHAR(10)
);
CREATE TABLE test2(id INT PRIMARY KEY,-- 创建表的时候字段之间使用‘,’分隔而不是结束的';'
name2 VARCHAR(10)
);
insert into test1 VALUES(1,'maxjenna'),(2,'linmingmei'),(3,'brite'),(4,'yitiao'),(5,'maxjenna'); -- INSERT语句插入的数据之间分别用()和','分隔
insert into test2 VALUES(1,'fucker'),(2,'linjiesheng'),(3,'kakesaki'),(4,'linmingmei'),(5,'fucker') -- INSERT语句插入的数据之间分别用()和','分隔
altertable test2 changename2 name varchar(15);
show CREATE TABLE test1
select * from test1;
SELECT * FROM test2;
select NAME from test1
union
SELECT name from test2
-- 结果是可以，union不仅消除两表之间的重复记录，而且删除单表查询中产生的重复记录
-- 求出平均订单数额，总订单数额，平均订单数额占客户信用额的百分比以及平均订单数额占销售人员定额的百分比SELECT * FROM orders;SELECT * FROM customers;SELECT * FROM salesreps
SELECT avg(amount),sum(amount),100*avg(amount/credit_limit),100*AVG(amount/quota)
FROM orders INNER JOIN customers
on orders.CUST=customers.CUST_NUM
INNER JOIN salesreps
ON orders.REP=salesreps.EMPL_NUM
-- 消除重复记录 SQL2标准中，所有字段函数中皆可使用DISTINCT
-- 计算销售人员共有几种不同的头衔select * from salesreps
SELECT COUNT(DISTINCT title)
FROM salesreps
-- 计算有多少个销售点其销售人员的销售量超过了定额SELECT * from offices;SELECT * FROM salesreps
SELECT COUNT(DISTINCT rep_office)
FROM salesreps
WHERE SALES>QUOTA
-- GROUP BY分组函数
-- 1,先按分组字段将数据进行分组
-- 2,按照计算办法将分组数据进行计算得需要的数据
-- GROUP BY 分组字段
-- 求出平均订单大小SELECT * FROM orders
SELECT AVG(amount)
FROM orders
-- 求出每名销售人员的平均订单大小
SELECT rep,AVG(amount)
FROM orders
GROUP BY REP;
-- 求出每个销售点分配定额的最大值和最小值
SELECT REP_OFFICE,max(quota),min(quota)
FROM salesreps
GROUP BY REP_OFFICE
-- 求出每个销售点分配的人员数量
SELECT REP_OFFICE,COUNT(EMPL_NUM)
FROM salesreps
GROUP BY REP_OFFICE
-- 每个销售人员取得的客户数量SELECT * from customers
SELECT cust_rep,'has',COUNT(cust_num),'customer(s)'
FROM customers
GROUP BY cust_rep
-- 多个分组字段
-- 计算每名销售人员的每名客户的订单总额SELECT * FROM customers;SELECT * FROM orders;SELECT * FROMsalesreps
SELECT salesreps.`NAME`,customers.COMPANY,SUM(amount)
FROM customers
INNER JOIN salesrepsON salesreps.EMPL_NUM=customers.CUST_REP
INNER JOIN orders ON customers.CUST_NUM=orders.CUST
GROUP BY salesreps.`NAME`,customers.COMPANY
-- 按销售人员的字母排序，相同时按照客户名的字母排序
ORDER BY salesreps.`NAME`,customers.COMPANY
/*分组查询的约束
SELECT (group by)C,constrantA,f(B)---分组查询中的选择项可以是一个常量A，“汇总组中记录生成一个值的字段函数”，分组字段（在组中的每一个记录有相同的值）；或者上面三个的组合
FROM X,Y,Z
WHERE X.f=Y.g=Z.h
GROUP BY C,M,N
*/
-- 计算每名销售人员的订单总和
SELECT `NAME`,SUM(amount)
FROM salesreps INNER JOIN orders
ON salesreps.EMPL_NUM=orders.REP
GROUP BY `NAME`
/*ANSI/ISO 标准规定：在GROUP BY中两个NULL值将被归于同一个组*/
-- HAVING子句>>>分组搜索条件
-- 求出其订单总和超过30,000的销售人员的平均订单大小 SELECT * FROM orders;SELECT*FROM salesreps
SELECT AVG(AMOUNT)
FROM orders
GROUP BY rep
HAVING SUM(amount)>30000
-- 对具有两个或更多个销售人员的销售点，显示工作地点和在此工作的所有销售人员的总定额和总销售量SELECT * FROM offices;SELECT * FROM salesreps
SELECT city,SUM(QUOTA),SUM(salesreps.SALES)
FROM salesreps INNER JOIN offices ON salesreps.REP_OFFICE=offices.OFFICE
GROUP BY REP_OFFICE
HAVING COUNT(*) >= 2
-- 显示订单总量超过其现货量75%的每项产品的单价，现货量和订单总量SELECT * FROM products;SELECT * FROM orders;
SELECT products.PRODUCT_ID,products.PRICE,SUM(orders.QTY),products.QTY_ON_HAND
FROM products INNER JOIN orders on products.PRODUCT_ID=orders.PRODUCT
GROUP BY orders.PRODUCT
HAVING SUM(orders.QTY)>0.75*products.QTY_ON_HAND
ORDER BY products.QTY_ON_HAND DESC
-- 子查询
-- 1.子查询的SELECT子句总是有一个选择项
-- 2.子查询不使用ORDER BY
-- WHERE子句中的子查询
-- 列出定额小于全公司销售目标10%的销售人员
SELECT salesreps.`NAME`,salesreps.QUOTA,SUM(quota)
FROM salesreps
WHERE salesreps.QUOTA(SELECT SUM(salesreps.QUOTA) FROM salesreps WHERE salesreps.REP_OFFICE=o.OFFICE)
SELECT office,rep_sum_quota,of_target
from
(SELECT rep_office,sum(salesreps.QUOTA) as rep_sum_quota
FROM salesreps GROUP BY rep_office)
as a
INNER JOIN
(SELECT offices.OFFICE,offices.TARGET as of_target FROM offices)
as b
ON a.rep_office=b.office
WHERE of_target>rep_sum_quota
-- --------------------------------- 子查询搜索条件----------------------------------
-- -------子查询比较测试
-- -------子查询组成员测试
-- -------存在性测试
-- -------限定性比较测试
-- 子查询中加不加distinct参数结果都一样，不影响外查询结果。。（有即可，不在乎个数）
--
--
--
--
--
-- 编辑器没有自动保存,丢失好多进度
--
--
--
--
--
--
--
-- 组成员测试（IN）
-- 列出2008年1月和6月之间订购制造商ACI生产的产品号以4100开头的部件的所有客户 SELECT * FROM orders;SELECT * FROM customers
SELECT customers.COMPANY
FROM customers
WHERE customers.CUST_NUM IN (SELECT a.cust FROM orders AS a
WHERE a.order_date BETWEEN '2008-01-01' AND '2008-06-31'
AND
a.mfr='ACI'
AND
a.product LIKE '4100%')
-- 存在测试（EXISTS）
-- EXISTS条件并不真正使用子查询的结果
-- 仅仅测试子查询是否产生任何结果
-- 测试返回结果为TRUE/FALSE
-- 当子查询的表大于外查询的表时，使用EXISTS效果效率更高，可以参照索引。而‘in’不参照索引进行判断，用于外表大，子查询的表小的情况 ★★★★★★★★★
-- 求出取得的订单大于或等于25,000元的产品 SELECT * FROM orders;SELECT * FROM products
use sqlcompletereference
SELECT products.DESCRIPTION
FROM products
WHERE EXISTS (SELECT * FROM orders
WHERE amount > 25000
AND orders.PRODUCT=products.PRODUCT_ID
AND orders.MFR=products.MFR_ID
)
-- 列出Sue Smith的某些客户，这些客户没有订购超过3,000元的订单SELECT * FROM customers;SELECT * FROM salesreps;SELECT * FROM orders
/*SELECT orders.CUST
FROM orders
WHERE orders.AMOUNT .55*b.TARGET/*) as c*/
/*WHERE*/AND
salesreps.REP_OFFICE=a.office)
-- 限定测试（SOME和ALL)
-- SOME（取代了ANY）▲▲▲▲▲▲▲
/*与六个算数比较符（=，,=）连用;
用于把一个测试值和由子查询产生的一个字段的数据值相比较;
比较测试值和字段中的每个数值（一次一个）;
如果有一个比较产生的TRUE结果，则整个比较测试产生TRUE结果;
若子查询结果为空，则SOME测试返回FALSE
*/
-- 列出一个取得过超过其销售目标10%的订单的业务员
SELECT salesreps.`NAME`
FROM salesreps
WHERE 0.1*salesreps.QUOTA< SOME (SELECT orders.AMOUNT
FROM orders
WHERE orders.REP=salesreps.EMPL_NUM)
-- 关联子查询
-- 列出年龄超过40岁且 其管理的销售人员中有一位的销售量超过定额 的销售经理 SELECT * FROM salesreps
SELECT age,empl_num,NAME FROM salesreps
WHERE age >40
AND
empl_num in (
SELECT DISTINCT manager FROM salesreps
WHERE salesreps.SALES>quota)
-- HAVING子句中的子查询
-- 列出对ACI生产的产品，其取得的平均订单大小超过了总的平均大小的销售人员。 SELECT * FROM orders GROUP BY mfr;SELECT * FROM products
SELECT salesreps.`NAME`,avg(amount)
FROM salesreps,orders
WHERE salesreps.EMPL_NUM=orders.REP
AND orders.MFR='ACI'
GROUP BY salesreps.`NAME`
HAVING avg(amount)>(SELECT avg(amount) FROM orders)
-- 对请求稍加改变，在HAVING子句中的子查询变成一个关联子查询
-- 列出ACI生产的产品，其取得的平均订单大小至少与平均订单大小一样大的销售人员
-- SQL2标准扩展的功能
-- CASE条件选择
/*依据客户信用额度对示例数据库中的客户做一个A/B/C分析，其中
A类是信用额度超过60,000美元的那些客户
B类是信用额度超过30,000美元的那些客户
C类客户是不满足A,B类条件的客户*/
SELECT customers.COMPANY,
CASE WHEN customers.CREDIT_LIMIT >60000 THEN 'A'
WHEN customers.CREDIT_LIMIT >30000 THEN 'B'
ELSE 'C'
END AScredit_rating
FROM customers
/*生成所有销售点的一个列表，显示它们经理的名字和它们所在的城市和州。示例数据库不包含州名*/
SELECT offices.OFFICE,offices.MGR,salesreps.`NAME`,offices.CITY,
CASE offices.CITY WHEN 'New York' THEN 'New York State'
WHEN 'Chicago'THEN 'Illinois'
WHEN 'Atlanta'THEN 'Georgia'
WHEN 'Los Angeles' THEN 'California'
WHEN 'Denver'THEN 'Colorado'
END AS state
FROM offices,salesreps
WHERE salesreps.EMPL_NUM=offices.MGR
-- 加深对连接操作的理解，先将两表按选定的规则进行自然连接，后筛选列 SELECT * FROM offices,salesreps WHERE empl_num=mgr
/*列出销售人员和他们的定额，如果一名销售人员还没有分配销售定额，那么列出其迄今为止的销售量，如果销售量也是NULL则应列成0 */
SELECT salesreps.`NAME`,CASE WHEN(salesreps.QUOTA IS NOT NULL) THEN salesreps.QUOTA
WHEN(salesreps.SALES IS NOT NULL) THEN salesreps.SALES
ELSE 0
END AS xiaoshouliang
FROM salesreps
-- COALESCE表达式
/*有条件的消除数据库中的NULL值（有条件的创建NOT NULL 值）*/
-- /*列出销售人员和他们的定额，如果一名销售人员还没有分配销售定额，那么列出其迄今为止的销售量，如果销售量也是NULL则应列成0 */
SELECT salesreps.`NAME`,COALESCE(salesreps.QUOTA,salesreps.SALES,0)
FROM salesreps;
-- CAST列数据类型转换
SELECT 'NAME',CAST(rep_office AS CHAR),CAST(hire_date AS CHAR)
FROM salesreps
-- NULLIF表达式
/*有条件的创造NULL值*/
--
SELECT city,SUM(salesreps.sales)
FROM offices,salesreps
WHERE office = NULLIF(REP_OFFICE,0) -- 将REP_OFFICE为0的值转换为NULL
GROUP BY city
/*通常使用NULLIF()函数可以防止除零错误。
如果变量b为0，则下面的表达式将生成错误，但是，可以使用NULLIF()函数，这样如果变量b的值为0，它会返回NULL，而不是错误
select a/b
select a/nullif(b,0)*/
-- 记录值结构
-- 列出ACI-41002部件的所有订单的订单号、数量和数额
SELECT orders.ORDER_NUM,orders.QTY,orders.AMOUNT
FROM orders
WHERE (orders.MFR,orders.PRODUCT) = ('ACI','41002')
-- 列出最高单价产品的所有订单的订单号和取得日期
SELECT orders.ORDER_NUM,orders.ORDER_DATE
FROM orders
WHERE (orders.MFR,orders.PRODUCT) IN
(SELECT products.MFR_ID,products.PRODUCT_ID
FROM products
WHERE products.PRICE =(SELECT MAX(products.price) FROM products)
)
-- 记录值比较
('ACI','41002',54) < ('REI','2A44R',5) -- 基于第一个字段进行比较
('ACI','41002',54) < ('ACI','41003',35)-- 基于第二个字段进行比较
('ACI','41002',54) < ('ACI','41002',54)-- 基于第三个字段进行比较
-- 表值表达式 (在SQL语句中指定一个数据值表)
-- 表值结构
-- 把三个销售点添加到OFFICES表中
INSERT INTO offices
VALUES (23,'San Diego','Western',108,0.00),
(24,'Seattle','Western',104,0.00),
(14,'Boston','Eastern',NULL,0.00);
-- 将销售点插入表中，数据通过子查询的方式插入
INSERT INTO offices
VALUES(23,'San Diego','Western',108,0.00),
(24,'Seattle','Western',(SELECT manager FROM salesreps WHERE empl_num =105),0.00);
-- (SELECT 14,'Boston',region,mgr,0.00 FROM offices WHERE office=12 );
-- 查询表达式（连接JOIN,并UNION,差EXCEPT,交INTERSECT)
-- 与JOIN和UNION的用法相同
-- MYSQL没有EXCEPT,INTERSECT
-- ORACLE的EXCEPT方言为‘MINUS’
-- FROM子句中的查询表达式
-- 显示其信用额度超过50,000美元的所有客户的名字和订单总和。
SELECT company,TOT_ORDERs
FROM customers,(SELECT cust,SUM(amount) AS TOT_ORDERS
FROM orders
GROUP BY CUST) A
WHERE CREDIT_LIMIT>50000
AND
CUST_NUM=cust;
页:
[1]