SQL

###basic sql query

select (distinct,用于去重) target-list from ralation-list where qulification
target-list         目标
ralation-list       表
qualifica           条件
参考关系验算部分
复制代码

先排序后去重 表连接： 1.做笛卡尔乘积 2.筛选不符合查询条件的元组 3.删除不要的元组 4.有distinct，进行去重排序

select s.sid,s.sname
from sailor s,r1 r
where s.sid=r.sid and r.bid=103 and s.age<35
复制代码

find sailors who have raserved at least one boat

select sid,s.sname
from sailor s
where sid in (select distinct sid from r1); //加不加distinct对结果的寓意无影响

或者
select s.sid,s.sname
from sailor s ,r1 r
where s.sid=r.sid;

复制代码

模糊查询

select s.sname
from sailor s
where 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.bid
from sailor s,r1 r ,boat b
where 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.bid
from sailor s,r1 r ,boat b
where s.sid=r.sid and r.bid=b.bid
and (b.color='blue')
union       //并集
select s.sid,s.sname,b.color,b.bid
from sailor s,r1 r ,boat b
where s.sid=r.sid and r.bid=b.bid
and (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 boat
1.


2.
select s.sid,s.sname,b.color,b.bid
from sailor s,r1 r ,boat b
where s.sid=r.sid and r.bid=b.bid
and (b.color='blue')
intersect   //交集，但是mysql并不支持
select s.sid,s.sname,b.color,b.bid
from sailor s,r1 r ,boat b
where s.sid=r.sid and r.bid=b.bid
and (b.color='red')
复制代码

find sailors' name who have reserved boat #103

1. 非关联嵌套子查询

select s.sid,s.sname from sailor s
where sid in(select r.sid from reverse r where bid = '103') //同一个人可能多次订购了同一条船，这样产生出来的结果会有重复
可以考虑这个：
select s.sid,s.sname from sailor s
where sid in(select  distinct r.sid from reverse r where bid ='103')
//非关联嵌套子查询，做一次可以反复使用，里面和外面没啥关系
复制代码

2. 关联嵌套子查询

select s.sname form sailors s
where exist (select * from reserves r where r.bid=103 and s.sid=b=sid)
//对sailors 中每一条元组做扫描，要找的这条元组存在于这个子查询中：
bid是103，同时sid就是传进去的sid ,相当于两重循环，里面的子查询每次都不能重用
复制代码

3.子查询

select s.sid
from sailor s,boat b,r1 r
where 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 bid
from 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 sid
from sailor
where   rating >any(select rating from sailor where sname ='guppy')
复制代码

###Division in sql find sailors who have reserved all boats

1. 集合差操作

// mysql5.5 这样写会报错，应该是不支持except
select s.sname
from sailor s
where not exists ((select bid from boat ) except (select r.bid from r1 r   where r.sid=b.sid)) //查找船的bid，除了这个水手预定的船，如果这些船不存在，那么该水手预定了全部的船
复制代码

这个不太明白，得好好想想

select s.sname
from sailor s
where 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))
复制代码

聚集函数运算

1. count(*) 关系里面有多少条元组
2. count([distinct] A) 关系中有多少（不重复的）A
3. sum([distinct] A)
4. avg([distinct] A)
5. max(A)
6. 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 raring
from sailor
where sname='bob';
复制代码

select avg(age)// 计算级别为10 的水手的平均年龄
from sailor
where rating=10;
复制代码

select avg(distinct age)// 级别为10 的水手不同年龄值的平均值
from sailor
where rating=10;
复制代码

select sname //查询级别最高的水手的姓名
from sailor
where rating=(select max(s.rating) from sailor s );
复制代码

find name and age of the oldest sailors

select sid,sname,age
from sailor
where 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后得到的每个分组做筛序

1. 先对from 中出现的表做笛卡尔乘积
2. 用where 中条件进行筛选
3. 用groupinglist进行分组
4. 用having字句做检查
5. 最经过having筛选的组，按照select 进行结果计算，每个组得到一条结果 select 子句和having 子句中出现的属性必须是分组属性集的子集

the target-list contails:

1. atribute names
2. terms with aggregate operations //首先根据qualification 对结果进行筛选，然后根据group by 条件，将group by 值相同的元组分成一组，再在每个组上进行select查询（属性或者聚集函数）

ep:find the youngest sailor for each rating level

select rating, min(age),sname
from sailor
group 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    //最后，在每个分组上做这些select
from 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
````sql
select boat.bid,count(*) as number
from boat,r1
where 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 number
from boat,r1
where boat.bid=r1.bid
group by r1.bid
having boat.color='red';
复制代码

unknown column boat.color in having caluse select 和 having 子句中出现的属性在 group by 中出现的属性的子集

正确的是下面写法

select b.bid,count(*) as number
from boat b,r1 r
where b.bid=r.bid
group by b.bid
having b.bid in (select bid from boat  where color='red');

或者
select b.bid,count(*) as number
from boat b,r1 r
where b.bid=r.bid
group by b.bid,b.color
having 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 sailor
where age>18
group by rating
having 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.rating
from ((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 machines
group by type;
复制代码

如果采用下面这种：

select type,sum(accidents)/sum(hours_used)
from machines
group by type
having sum(hours_used)>0  // 如果设备的hour_used是null，会在结果中被忽略
复制代码

####子查询 select、from、where中多存在子查询

1. 标量子查询： 查询结果是一个值（可以出现value的时候都可以出现标量子查询） find the department whose average bonus is higher than average salary:

SELECT deptname,location
FROM department
WHERE (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 e
WHERE d.deptno=e.deptno
AND 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 d
WHERE  d.location='newYork';
复制代码

2. 表表达式： 查询结果是一个表（出现在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 temp
GROUP BY temp.startyear
复制代码

find the department whose total payment is greater than 200000

SELECT temp.deptno,temp.pay
FROM (SELECT deptno ,sum(salary)+sum(bonus) AS pay
        FROM emp 
        GROUP BY deptno)AS temp
WHERE pay>20000;
复制代码

3. 公共表表达式： 公共表表达式是一个临时视图 with clause can be used to define the common table expression.in 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 deptno
FROM payroll
WHERE 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.avgsal
FROM deptavg d1,    // 和自己做自连接
     deptavg d2
WHERE 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,orderNo
FROM person,orders
WHERE person.id=orders.id_p
复制代码

JOIN: 如果表中有至少一个匹配，则返回行

除了上面的方法，我们也可以使用join关键字获得相同的效果：

SELECT person.id,person.firstName,person.lastName,orders.orderNo
FROM person
(inner)JOIN orders //INNER JOIN 与 JOIN 是相同的
ON person.id=orders.id_p;
复制代码

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

SELECT person.id,person.firstName,person.lastName,orders.orderNo
FROM person
LEFT JOIN orders
ON person.id=orders.id_p;
复制代码

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

SELECT person.id,person.firstName,person.lastName,orders.orderNo
FROM person
RIGHT JOIN orders
ON person.id=orders.id_p;
复制代码

FULL JOIN: 只要其中一个表中存在匹配，就返回行

SELECT person.id,person.firstName,person.lastName,orders.orderNo
FROM person
full JOIN orders // mysql 并不支持
ON person.id=orders.id_p;
复制代码

转载于:https://juejin.im/post/5b7ada56e51d4538da22d454