select 字段1，字段2 from 表1，表2 where 字段 group by 分组字段 having 字段 order by 字段 ASC|DESC limit m,n
一、查询所有
select * from student;
select name as 姓名 from student;
select name 姓名 from student;
二、条件查询
1.查询学生id等于1
selet * from student where student_id;
2.查询name字段为null
selet * from student where name is null;
3.查询name字段不为null
select * from student where name is not null;
4.查询name字段为空字符串
select * from student where name ='';
5.查询name字段不为空字符串且不为null
select * from student where name !='';
6.查询city字段存在河南省和山东省的数据
select * from where city in('山东省','河南省');
7.查询age在25到30之间
select * from student where age between 25 and 30;
8.查询age不在20到30之间
select * from student where age not between 20 and 30;
9.查询第一个字是郭的数据
select * from student where name like '郭%';
10查询第一个字是郭的两个字得数据
select * from student where name like '郭_';
11查询存在郭的字得数据
select * from student where name like '%郭%';
三、分组查询
1.分组性别
select sex,count(*) from student group by sex;
2.分组性别前条件查询
select sex,count(*) from student where name !='李白' group by sex;
3.分组性别后条件查询
select sex,count(*) from student group by sex having count(*)>5;
4.查询后分组然后再条件查询
select sex,count(*) from student where name !='李白' group by sex having count(*)>5;
四、排序查询
1.降序排序
select * from student order by salary desc;
2.降序后再升序排序
select * from student order by salary desc,bonus asc;
五、聚合函数
count(*) sum(salary) avg(salary) min(salary) max(salary)
六、连接查询
1.内连接
mysql> select * from class join student on class.class_id=student.class_id; +----------+----------+------------+----------+--------+------+--------+ | class_id | caption | student_id | class_id | name | sex | gender | +----------+----------+------------+----------+--------+------+--------+ | 1 | 三年一班 | 1 | 1 | 巨炮 | m | 1 | | 1 | 三年一班 | 2 | 1 | 钢弹 | m | 1 | | 2 | 三年二班 | 3 | 2 | 七次郎 | m | 1 | | 2 | 三年二班 | 4 | 2 | 米线 | m | 0 | | 3 | 三年三班 | 5 | 3 | 辣条 | m | 0 | +----------+----------+------------+----------+--------+------+--------+ 5 rows in set (0.00 sec)
2.左连接
mysql> select * from class left join student on class.class_id=student.class_id; +----------+----------+------------+----------+--------+------+--------+ | class_id | caption | student_id | class_id | name | sex | gender | +----------+----------+------------+----------+--------+------+--------+ | 1 | 三年一班 | 1 | 1 | 巨炮 | m | 1 | | 1 | 三年一班 | 2 | 1 | 钢弹 | m | 1 | | 2 | 三年二班 | 3 | 2 | 七次郎 | m | 1 | | 2 | 三年二班 | 4 | 2 | 米线 | m | 0 | | 3 | 三年三班 | 5 | 3 | 辣条 | m | 0 | | 4 | 四年级 | NULL | NULL | NULL | NULL | NULL | +----------+----------+------------+----------+--------+------+--------+ 6 rows in set (0.00 sec)
3.右连接
mysql> select * from class right join student on class.class_id=student.class_id; +----------+----------+------------+----------+--------+------+--------+ | class_id | caption | student_id | class_id | name | sex | gender | +----------+----------+------------+----------+--------+------+--------+ | 1 | 三年一班 | 1 | 1 | 巨炮 | m | 1 | | 1 | 三年一班 | 2 | 1 | 钢弹 | m | 1 | | 2 | 三年二班 | 3 | 2 | 七次郎 | m | 1 | | 2 | 三年二班 | 4 | 2 | 米线 | m | 0 | | 3 | 三年三班 | 5 | 3 | 辣条 | m | 0 | | NULL | NULL | 12 | NULL | NULL | m | NULL | | NULL | NULL | 11 | 5 | 骁龙 | m | NULL | | NULL | NULL | 13 | 6 | 梦琳 | w | NULL | +----------+----------+------------+----------+--------+------+--------+ 8 rows in set (0.00 sec)
4.自然连接（两张表有相同字段名）
mysql> select * from class natural join student; +----------+----------+------------+--------+------+--------+ | class_id | caption | student_id | name | sex | gender | +----------+----------+------------+--------+------+--------+ | 1 | 三年一班 | 1 | 巨炮 | m | 1 | | 1 | 三年一班 | 2 | 钢弹 | m | 1 | | 2 | 三年二班 | 3 | 七次郎 | m | 1 | | 2 | 三年二班 | 4 | 米线 | m | 0 | | 3 | 三年三班 | 5 | 辣条 | m | 0 | +----------+----------+------------+--------+------+--------+ 5 rows in set (0.00 sec)
5.联合查询（表和表的结构相同）
mysql> select * from student union select * from student_1; +------------+----------+--------+------+--------+ | student_id | class_id | name | sex | gender | +------------+----------+--------+------+--------+ | 1 | 1 | 巨炮 | m | 1 | | 2 | 1 | 钢弹 | m | 1 | | 3 | 2 | 七次郎 | m | 1 | | 4 | 2 | 米线 | m | 0 | | 5 | 3 | 辣条 | m | 0 | | 12 | NULL | NULL | m | NULL | | 11 | 5 | 骁龙 | m | NULL | | 13 | 6 | 梦琳 | w | NULL | | 0 | 1 | 小龙 | m | NULL | +------------+----------+--------+------+--------+ 9 rows in set (0.00 sec)
七、子查询
1.标量子查询
select * from score where number > (select avg(number) from score);
2.列子查询
mysql> select * from student where sex in (select sex from student where sex='m'|| sex='w'); +------------+----------+--------+------+--------+ | student_id | class_id | name | sex | gender | +------------+----------+--------+------+--------+ | 1 | 1 | 巨炮 | m | 1 | | 2 | 1 | 钢弹 | m | 1 | | 3 | 2 | 七次郎 | m | 1 | | 4 | 2 | 米线 | m | 0 | | 5 | 3 | 辣条 | m | 0 | | 12 | NULL | NULL | m | NULL | | 11 | 5 | 骁龙 | m | NULL | | 13 | 6 | 梦琳 | w | NULL | +------------+----------+--------+------+--------+ 8 rows in set (0.00 sec)
mysql> select * from student where student_id > any (select student_id from student where name in ('巨炮','钢弹','辣条')); +------------+----------+--------+------+--------+ | student_id | class_id | name | sex | gender | +------------+----------+--------+------+--------+ | 2 | 1 | 钢弹 | m | 1 | | 3 | 2 | 七次郎 | m | 1 | | 4 | 2 | 米线 | m | 0 | | 5 | 3 | 辣条 | m | 0 | | 12 | NULL | NULL | m | NULL | | 11 | 5 | 骁龙 | m | NULL | | 13 | 6 | 梦琳 | w | NULL | +------------+----------+--------+------+--------+ 7 rows in set (0.00 sec)
mysql> select * from student where student_id > all (select student_id from student where name in ('巨炮','钢弹','辣条')); +------------+----------+------+------+--------+ | student_id | class_id | name | sex | gender | +------------+----------+------+------+--------+ | 12 | NULL | NULL | m | NULL | | 11 | 5 | 骁龙 | m | NULL | | 13 | 6 | 梦琳 | w | NULL | +------------+----------+------+------+--------+ 3 rows in set (0.00 sec)
3.行子查询
select * from student where (student_id,name) = (select student_id,name from student where student_id=1);
4.表子查询
select * from (select * from student) as b where student_id>12;
八、常用
1.过滤重复
select distinct * from order_info limit 5;
2.随机读取几条数据
select * from order_info order by rand() limit 5;
3.主键自增从1000开始
alter table student auto_increment = 1000;