数据库原理与应用3

MySQL的数据查询

1.简单查询

（1）简单无条件查询

select cusid ,custname from customer:

select  *  from  customer:

（2）简单条件查询

select cusid ,custname from customer where custcity = "beijing":
select  *  from  custome where  custcity = "beijing":

select  *  from  custome where  custcity  is null:

（3）使用 between ....  and ...的查询

select pdid  from  product  where  pdprice  10 and 50;

相当于  select  pdid  from  product  where   pdprice >=10 and  <= 50;

（4）字符串匹配查询

select custphone  from  customer  where  custname (not) like  'liqi';

select custphone  from  customer  where  custname  like  '_qi';

select custphone  from  customer  where  custname  like  'li%';

（5）使用IN的查询

select  *  from  customer  where  custcity  in ('beijing','guangzhou');

select  *  from  customer  where  custcity  ='beijing'  or 'custcity='guangzhou;

select  *  from  customer  where  custcity   in ('beijing','guangzhou');

（6）存储查询结果

select  custD,  custname,custphone into  BY_customer from  customer;

（7）查询结果的排序

select * from product order by PdSuppler ,pdPrice DESC;

2.连接查询

（1）一般连接查询

select  customer.custID,custname,orderID,orderaddress from  customer,orders where customer.custID = orders.custID;

select  customer.custID,custname,orderID,orderaddress from  customer,orders where customer.custID = orders.custID and  custcity = 'beijing';

select  orders.orderID,custname,orderaddress  from  customer inner join orders  on customer.custID=orders.custID where  custcity ='beijing';

（2）外连接查询

select  customer.custID,custname,orderID,orderaddress from  customer left (right,full) join orders on customer.custID=orders.custID;

3.分组及汇总查询

（1）一般汇总查询

select  count(*)  as  ordernumber,sum(orderamount)  as  sumamount  from  orders;

（2）使用GROUP BY 的分组汇总查询

select  pdID ,sum(quantity) as total  from  orderdetail  group by pdID;

select  pdID ,sum(quantity) as total  from  orderdetail  group by sets  (pdID,(pdID,orderID,quantity));

4.嵌套查询

（1）内外层不相关的嵌套查询

1）.返回单值的子查询

select custname,custphone from customer where custID= (select custID from  orders where orderID='D201600152365');

select custname,custphone from customer where custID in  (select custID from  orders where orderID='D201600152365');

select custname,custphone from customer where orderamount>(select orderamount from  orders where orderID='D201600152365');

2）.返回多值的子查询

select * from  product where pdprice >all(select pdprice from  product where pdsupplier='tianjing');

select * from  product where pdprice <any(select pdprice from  product where pdsupplier='tianjing');

select * from  product where pdprice =any(select pdprice from  product where pdsupplier='tianjing');

（2）内外层互相关的嵌套查询 

select * from order A  where orderamount = (select max(orderamount ) from order B where B.custID=A.custID )  order by orderID;