mysql 查询语句

查询语句：

（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;

 

子查询