查询语句:
(like相当于=号,not like相当于“不等”号)但使用通配符或_时不能使用=号
mysql> select *from member where address like 'beijing%'; (%为通配符)
mysql> select *from member where name like 'zhan_san'; (_为任意字符)
Is null为空值,is not null 是不能为空值
例mysql> select * from famliy where phone is null;
mysql> select * from famliy where phone is not null;
mysql> select *from member where id in(1,2);
mysql> select *from member where name not in('zhangsan','lisi');
between and 可以判断某个字段是否在指定范围内
mysql> select *from member where age between 30 and 50; (包含30和50)
mysql> select *from member where age not between 30 and 50;
个人认为between and就相当于>= <=
And为‘并’,满足所有条件 ; or为‘或’,满足其中一个条件即可。
mysql> select *from famliy where name like 'chenb' and sex like 'man';
mysql> select *from famliy where name like 'chenb' or age like 34;
mysql> select *from famliy where age like 27 and sex like 'man' or age like 34;
group by 分组查询
mysql> select name,id,age from member group by age;
mysql> select sex,count(name) from member group by sex;
mysql> select sex,count(name) from member group by sex having count(name) >5;
group_concat()和group by 一起使用
mysql> select sex,group_concat(name) from member group by sex;
group by和with rollup一起使用 (with rollup求和)
mysql> select sex,count(name) from member group by sex with rollup;
mysql> select sex,group_concat(name) from member group by sex with rollup;
查询member表下所有记录,但只显示前两行
mysql> select *from member limit 2;
mysql> select *from member limit 2,3; (2是起始位置,显示3行)
集合函数查询
Count()函数用来统计记录的条数
mysql> select count(name) from member;
mysql> select sex,count(name) from member group by sex;
sum()函数是用来求和的
mysql> selectid,sum(score) from member where id;
mysql> selectid,sum(score) from member group by id;
avg()函数是求平均值的函数
mysql> select avg(yuwen) from chengji;
max()函数是求最大值得函数
min()函数是求最小值的函数
连接查询
内连接查询方式
mysql> select member.name,famliy.sex,member.age from member,famliy where member.id=famliy.id;
外连接查询方式
Select 属性名列表 from 表1 left|right join 表2
On 表1.属性名1=表2.属性名2;
mysql> select member.name,famliy.sex,member.age from member right join famliy onmember.id=famliy.id;
子查询