mysql查询_MySQL常用查询

单表查询

①查询所有     *

mysql> select * from student;

②查询选中字段记录

mysql> select s_name from student;

③条件查询          where

mysql> select s_name from student where s_id<5;

④查询后为字段重命名   as

mysql> select s_name as 名字 from student;

⑤模糊查询     like

%匹配多个字符

mysql> select s_name as 姓名 from student where s_name like '李%';

_匹配一个字符

mysql> select s_name as 姓名 from student where s_name like '李_';

mysql> select s_name as 姓名 from student where s_name like '李__';

⑥排序(默认升序)  order by  以某个字段为主进行排序

升序  asc (asc可以不写)

mysql> select * from student order by sc_id asc;

降序  desc

mysql> select * from student order by sc_id desc;

⑦限制显示数据数量   limit

limit 只接一个数字n时表示显示前面n行

mysql> select * from student limit 5;

limit 接两个数字m，n时表示显示第m行之后的n行

mysql> select * from student limit 2,4;

⑧常用聚合函数

mysql> select * from details;

最大值  max

mysql> select max(age) from details;

最小值 min

mysql> select min(age) from details;

求和 sum

mysql> select sum(age) from details;

平均值 avg

mysql> select avg(age) from details;

四舍五入 round

mysql> select round(avg(age)) from details;

统计  count

mysql> select count(address) from details;

⑨分组查询  group by    筛选条件使用having，having后接条件必须是select后存在的字段

mysql> select age,count(age) from details group by age having age>30;

以age为组统计每个age的人数最后筛选出age大于30的

2、子查询   也叫嵌套查询

mysql> select * from details where age>(select avg(age) from details);

查询所有age大于平均年龄的信息

3、关联查询

①内连接    inner join

无条件内连接  又称笛卡尔连接

mysql> select * from student inner join college;

有条件内连接  在无条件基础上on接条件

mysql> select * from student inner join college on sc_id=c_id;

②外连接

左外连接    left join

以左表为基准，右表没有对应数据以null填充，多余数据去除

mysql> select * from tb1 left join tb2 on id=t_id;

mysql> select * from tb2 left join tb1 on id=t_id;

右外连接   right join

以右表为基准，左表没有对应数据以null填充，多余数据去除

mysql> select * from tb1 right join tb2 on id=t_id;

mysql> select * from tb2 right join tb1 on id=t_id;

派生表必须命名 as

mysql> select * from (select * from details where age>30) as a left join student on d_id=s_id;