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;