###basic sql query
select (distinct,用于去重) target-list from ralation-list where qulificationtarget-list 目标ralation-list 表qualifica 条件参考关系验算部分复制代码
先排序后去重 表连接： 1.做笛卡尔乘积 2.筛选不符合查询条件的元组 3.删除不要的元组 4.有distinct，进行去重排序
select s.sid,s.snamefrom sailor s,r1 rwhere s.sid=r.sid and r.bid=103 and s.age<35复制代码
find sailors who have raserved at least one boat
select sid,s.snamefrom sailor swhere sid in (select distinct sid from r1); //加不加distinct对结果的寓意无影响或者select s.sid,s.snamefrom sailor s ,r1 rwhere s.sid=r.sid;复制代码
模糊查询
select s.snamefrom sailor swhere s.sname like 'B%B'复制代码
find sailors who have booked a green boat or a red boat
1.select s.sid,s.sname,b.color,b.bidfrom sailor s,r1 r ,boat bwhere s.sid=r.sid and r.bid=b.bid //进行连表查询and (b.color='blue' or b.color='red');//船的颜色是红色或者绿色2.select s.sid,s.sname,b.color,b.bidfrom sailor s,r1 r ,boat bwhere s.sid=r.sid and r.bid=b.bidand (b.color='blue')union //并集select s.sid,s.sname,b.color,b.bidfrom sailor s,r1 r ,boat bwhere s.sid=r.sid and r.bid=b.bidand (b.color='red')复制代码
union ：used to caluclate the union of any two union-caopatible sets of tuples except：集合差操作 intersect：can be used to compute the intersection of any two union-compatible sets of tuples
find sailors who have reserve red boat and blue boat1.2.select s.sid,s.sname,b.color,b.bidfrom sailor s,r1 r ,boat bwhere s.sid=r.sid and r.bid=b.bidand (b.color='blue')intersect //交集，但是mysql并不支持select s.sid,s.sname,b.color,b.bidfrom sailor s,r1 r ,boat bwhere s.sid=r.sid and r.bid=b.bidand (b.color='red')复制代码
find sailors' name who have reserved boat #103
- 非关联嵌套子查询
select s.sid,s.sname from sailor swhere sid in(select r.sid from reverse r where bid = '103') //同一个人可能多次订购了同一条船，这样产生出来的结果会有重复可以考虑这个：select s.sid,s.sname from sailor swhere sid in(select distinct r.sid from reverse r where bid ='103')//非关联嵌套子查询，做一次可以反复使用，里面和外面没啥关系复制代码
- 关联嵌套子查询
select s.sname form sailors swhere exist (select * from reserves r where r.bid=103 and s.sid=b=sid)//对sailors 中每一条元组做扫描，要找的这条元组存在于这个子查询中：bid是103，同时sid就是传进去的sid ,相当于两重循环，里面的子查询每次都不能重用复制代码
3.子查询
select s.sidfrom sailor s,boat b,r1 rwhere s.sid=r.sid and b.bid=r.bid and b.color='green' //找那些预定绿色船的水手id and s.sid in //并且这些id在下下面的范围内 (select s2.sid from sailor s2,boat b2 ,r1 r2 where s2.sid=r2.sid and b2.bid=r2.bid and b2.color='red') //找那些预定了红色船的水手复制代码
思考题： find name of sailors who have reserved boat #103 and reserved only one time
find ids of boats which are reversed by only one sailor
select bidfrom r1 r// r 预定表where bid not in (select bid from r1 r2 where r2.sid != r.sid)// 除了这个水手之外其他水手定的船的集合并拿到id复制代码
find sailors whose rating is greater than that of some sailor called horatio:
select sidfrom sailorwhere rating >any(select rating from sailor where sname ='guppy')复制代码
###Division in sql find sailors who have reserved all boats
- 集合差操作
// mysql5.5 这样写会报错，应该是不支持exceptselect s.snamefrom sailor swhere not exists ((select bid from boat ) except (select r.bid from r1 r where r.sid=b.sid)) //查找船的bid，除了这个水手预定的船，如果这些船不存在，那么该水手预定了全部的船复制代码
这个不太明白，得好好想想
select s.snamefrom sailor swhere not exists (select b.bid from boat b where not exists (select r.bid from r1 r where r.bid=b.bid and r.sid=s.sid))复制代码
聚集函数运算
- count(*) 关系里面有多少条元组
- count([distinct] A) 关系中有多少（不重复的）A
- sum([distinct] A)
- avg([distinct] A)
- max(A)
- min(A) A is single column
select count(*) from sailors复制代码
select count(distinct rating)// there is many sailors named bob,find the //number of the distinct raringfrom sailorwhere sname='bob';复制代码
select avg(age)// 计算级别为10 的水手的平均年龄from sailorwhere rating=10;复制代码
select avg(distinct age)// 级别为10 的水手不同年龄值的平均值from sailorwhere rating=10;复制代码
select sname //查询级别最高的水手的姓名from sailorwhere rating=(select max(s.rating) from sailor s );复制代码
find name and age of the oldest sailors
select sid,sname,agefrom sailorwhere age=(select max(age) from sailor);//使用嵌套子查询先计算最大的年龄是多少，外层查询查找年龄等于这个最老年龄的水手的信息复制代码
####groupBy sometimes we want to apply aggregrate operators to each of serveral groups tuples.
select [distinct] target-list from ralation-list where qualification group by grouping-list having group-qualification // 用having子句对group by后得到的每个分组做筛序
- 先对from 中出现的表做笛卡尔乘积
- 用where 中条件进行筛选
- 用groupinglist进行分组
- 用having字句做检查
- 最经过having筛选的组，按照select 进行结果计算，每个组得到一条结果 select 子句和having 子句中出现的属性必须是分组属性集的子集
the target-list contails:
- atribute names
- terms with aggregate operations //首先根据qualification 对结果进行筛选，然后根据group by 条件，将group by 值相同的元组分成一组，再在每个组上进行select查询（属性或者聚集函数）
ep:find the youngest sailor for each rating level
select rating, min(age),snamefrom sailorgroup by rating;复制代码
find age of the youngest sailor with age>=18 for each rating with at leat 2 such sailors
select s.rating,min(s.age) as minage //最后，在每个分组上做这些selectfrom sailor s // 首先做自连接where s.age>=18 // 要找的是年龄>=18的，先根据这个条件做筛选group by s.rating //然后根据rating做分组having count(*)>1; //但这些rating需要满足这个条件，at least 2```有了group by ，select 和having中的聚集函数在group上做计算，没有group by就在整个表上做计算for each red boat,find the number of reservation for this boat````sqlselect boat.bid,count(*) as numberfrom boat,r1where boat.bid=r1.bid and color='red' // 做连接并筛选group by r1.bid; // grouping over a join of two relations 复制代码
what do we get if we remove color='red' from where clause and add a having clause with this condition?
考虑下面这种写法
select boat.bid,count(*) as numberfrom boat,r1where boat.bid=r1.bidgroup by r1.bidhaving boat.color='red';复制代码
unknown column boat.color in having caluse select 和 having 子句中出现的属性在 group by 中出现的属性的子集
正确的是下面写法
select b.bid,count(*) as numberfrom boat b,r1 rwhere b.bid=r.bidgroup by b.bidhaving b.bid in (select bid from boat where color='red');或者select b.bid,count(*) as numberfrom boat b,r1 rwhere b.bid=r.bidgroup by b.bid,b.colorhaving b.color='red';复制代码
再看一个例子： find age of youngest sailor with age>18, for each rating with at least 2 sailors(of any age)
select rating,sname,min(age)from sailorwhere age>18group by ratinghaving 1<(select count(*) from sailor s where s.rating=s.rating);复制代码
find those ratings for whick the average age is the minimum over all ratings
select temp.ratingfrom ((select s.rating,avg(s.age) as avgage from sailor s group by s.ating) as temp) //where temp.avgage=(select min(avgage) from temp);复制代码
null
空值不是零，空值是不知道 例如：
select * from sailor where rating>8;// rating 为null 的元组会被过滤掉，null不是0，null是不知道（关系）复制代码
CAST 表达式
change the expression to the target data type
CASE 表达式
table: Machines(type,year,hours_used,accidents)
find the rate of the accidents of 'chain saw' in the whole accidents
select sum(case when type='chain saw' then accidents else 0e0 end )/sum(accidents)from machines;复制代码
find the average accident rate of every kind of machine
select type,case when sum(hours_used)>0 then //some machines may not be used sum(accidents)/sum(hours_used) else null end as accident_rate // 以上计算在分组上做计算from machinesgroup by type;复制代码
如果采用下面这种：
select type,sum(accidents)/sum(hours_used)from machinesgroup by typehaving sum(hours_used)>0 // 如果设备的hour_used是null，会在结果中被忽略复制代码
####子查询 select、from、where中多存在子查询
- 标量子查询： 查询结果是一个值（可以出现value的时候都可以出现标量子查询） find the department whose average bonus is higher than average salary:
SELECT deptname,locationFROM departmentWHERE (SELECT avg(bonus) // 对department中的每条元组做扫描，相当于二重循环 FROM emp WHERE emp.deptno=department.deptno) > //每次扫描，将外层的deptno传入 (SELECT avg(salary) FROM emp WHERE emp.deptno=department.deptno)复制代码
list the deptno,department,and the max saluary of all departments located in new york:
SELECT d.deptno,d.location,max(salary)FROM department d,emp eWHERE d.deptno=e.deptnoAND d.location='newYork'GROUP BY d.deptno;复制代码
或者
SELECT d.deptno,d.location,(SELECT max(salary) FROM emp e WHERE e.deptno=d.deptno) as maxSalary //关联嵌套子查询FROM department dWHERE d.location='newYork';复制代码
- 表表达式： 查询结果是一个表（出现在from字句） the rasult of a sub-query is a table,it can be used in the place where a table can occur.
SELECT startyear,avg(temp.pay)FROM (SELECT name,bonus+salary as pay, year(startdate) as startyear FROM emp ) as tempGROUP BY temp.startyear复制代码
find the department whose total payment is greater than 200000
SELECT temp.deptno,temp.payFROM (SELECT deptno ,sum(salary)+sum(bonus) AS pay FROM emp GROUP BY deptno)AS tempWHERE pay>20000;复制代码
- 公共表表达式： 公共表表达式是一个临时视图 with clause can be used to define the common table face,it defines a temporary view.
find the department who has the highest total payment
WITH payroll(deptno,totalpay) AS (Select deptno,sum(salary)+sum(bonus) FROM emp GROUP BY deptno)SELECT deptnoFROM payrollWHERE totalpay=(SELECT max(totalpay) FROM payroll)复制代码
find department pairs, in which the first department's average salary is more than two times of the second one'd
WITH deptavg(deptno, avgsal) AS (SELECT deptno, avg(salary) AS avgsal FROM emp GROUP BY deptno) // 公共表表达式的语法SELECT d1.deptno, d1.avgsal, d2.deptno, d2.avgsalFROM deptavg d1, // 和自己做自连接 deptavg d2WHERE d1.avgsal > 2 * d2.avgsal;// 连接筛选条件复制代码
JOIN
有下面这两张表： Person： Id_P LastName FirstName Address City 0 adams john oxford 1 bush george london 2 carter thomas paris
Orders： Id_O OrderNo Id_P 1 77895 2 2 44678 2 3 22456 0 4 24562 0 5 34764 9
使用下面这段代码，可以查找谁订购了产品，且他的信息
SELECT person.id,firstName,lastName,orderNoFROM person,ordersWHERE person.id=orders.id_p复制代码
JOIN: 如果表中有至少一个匹配，则返回行
除了上面的方法，我们也可以使用join关键字获得相同的效果：
SELECT person.id,person.firstName,person.lastName,orders.orderNoFROM person(inner)JOIN orders //INNER JOIN 与 JOIN 是相同的ON person.id=orders.id_p;复制代码
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
SELECT person.id,person.firstName,person.lastName,orders.orderNoFROM personLEFT JOIN ordersON person.id=orders.id_p;复制代码
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
SELECT person.id,person.firstName,person.lastName,orders.orderNoFROM personRIGHT JOIN ordersON person.id=orders.id_p;复制代码
FULL JOIN: 只要其中一个表中存在匹配，就返回行
SELECT person.id,person.firstName,person.lastName,orders.orderNoFROM personfull JOIN orders // mysql 并不支持ON person.id=orders.id_p;复制代码