mysql-> select * from mysql_test.cust
-> where cust_sex='M';
mysql-> select * from mysql_test.cust
-> where cust_id between 903 and 912;
mysql-> select * from mysql_test.cust
-> where cust_id in(903,906,908);
mysql-> select * from mysql_test.cust
-> where cust_contact is null; //is not null
mysql-> select studentNo,studentName
-> from tb_student
-> where studentNo in(select studentNo from tb_score where score>80); //通过外键联系起来
mysql-> select cust_address,cust_sex,count(*) as '人数' //mysql 5.5版本的group by后面的字段必须和select的字段一致
-> from mysql_test.cust
-> group by cust_address,cust_sex;
mysql-> select cust_address,cust_sex,count(*) as '人数' //汇总
-> from mysql_test.cust
-> group by cust_address,cust_sex
-> with rollup;
mysql-> select cust_name,cust_address
-> from mysql_test.cust
-> group cust_name,cust_address
-> having count(*)<=3;
mysql-> select cust_name,cust_sex from mysql_test.cust
-> order by cust_name desc,cust_sex desc;
mysql-> select cust_id,cust_name from mysql_test.cust
-> order by cust_id
-> limit 4,3; //默认从0开始 另一种形式: limit 3 offset 4;
练习:
(1) select * from 图书
where 单价 between 50 and 60
order by 出版社,单价;
(2) select 书名,借阅时间
from 图书,借阅,读者
where 姓名='王明' and 读者.借书证号=借阅.借书证号 and 借阅.图书编号=图书.图书编号;
(3) select 出版社,max(单价),min(单价),avg(单价)
from 图书
group by 出版社;