MySQL查询语句大全

Mysql查询语句

一、单表查询

1.select from基本子句的使用

1.1 为字段取别名

select 项的原名 as 别名

例如：

select studentno as '学号',phone as  '手机号'  from student

1.2使用distinct过滤记录

• 如果希望一个列表没有重复值，可以利用distinct从结果集中除去重复行。

• 例如：在score表中查询期末成绩有高于80分的学生的学号，不管有几门课的成绩高于95，只要有一门就可以显示，利用distinct可将重复行除去。

select distinct studentno from score where final>80;

2.使用where子句过滤结果集

2.1 在使用where时，需要使用一些比较运算符来确定查询的条件

• 例如：查询入学成绩在800分以上的学生的学号和姓名

select studentno,sname,phone from student where entrance>800;

2.2 带in关键字查询

• in关键字可以判断某个字段的值是否在指定的集合中，也可以使用not in关键字查询不在某取值范围内的记录行数据。

• 例如：查询学号分别为18122221324，18122210009的成绩信息。

 select * from score where studentno in('18122221324','18122210009');

2.3 带between and的范围查询

• 使用between 搜索条件相当于使用and连接两个比较条件；

• 检索条件指定排除某个范围的值，一般可以利用not between关键字来实现。

• 例如：查询选修课程号为c05109的学生学号和期末成绩，并且要求平时成绩在80-95分。

select studentno,final from score where courseno='c05109' and daily between 80 and 95;	

2.4 带like字符匹配查询

• like可以实现模糊查询，有两种通配符：‘%’、‘_’

• '%'可以匹配一个或多个字符，可以代表任意长度的字符串，长度可以为0；

• '-'只匹配一个字符。

• 例如：在student表查询所有姓何的学生的姓名和Email。

select sname,Email from student where sname like '何%';

2.5 用is null关键字查询空值

• 一个字段是空值或者不是空值，要表示为is null或is not null

• 例如：查询成绩表期末成绩为空的学生信息

 select * from score where final is null;

2.6 带and或or的多条件查询

• and 需要满足所有的条件；

• or 只要符合多条件中的一个，记录就会被搜索出来。

• 例如：查询计算机学院的具有高级职称教师的教师号、姓名和从事的专业。

• select teacherno,tname,major from teacher where department='计算机学院' and (prof='教授' or prof='副教授');
  

3.使用order by 对结果集排序

• （1）默认情况下，结果集按照升序排序，也可以在输出项的 后面加上关键字desc来实现降序输出;

• （2）对含有null值的列进行排序时，如果是按升序排列，null值将出现在最前面，如果是按降序排列，null值将出现在最后;

• （3）order by可以同时指定多个排序项；

• （4）order by不可以使用text、blob、longtext和mediumblob等类型的列。

• 例如：在score查询期末成绩大于60分的学生信息，并先按照课程号升序，再按照期末成绩降序排序。

select * from score where final>60 order by courseno,final desc;

4.group by和having子句的使用

4.1 使用group by来分组

• 单独使用group by，查询结果只显示每组的一条记录。

4.2 group by 和group_concat()函数一起使用

• 使用group by和group_concat()函数查询，可以将每个组中的所有字段值都显示出来。
• 例如：查询course表，查询选学该门课程的学生学号。

 select courseno,group_concat(studentno) from score group by courseno;

4.3 group by 和 having一起使用

• having经常与group by一起使用，尽管having也可以不带group by。having在where筛选后要进一步的筛选。
• 例如：查询选课在3门以上且各门课程期末成绩高于75分的学生的学号及其总成绩，查询结果按总成绩降序列出。

	-> select studentno as '学号', sum(daily+final) as '总分'
    -> from score
    -> where final>75
    -> group by studentno
    -> having count(*)>=3
    -> order by sum(daily+final) desc;

5.用limit限制查询结果的数量

• limit用来限制查询结果的数量，可以指定查询结果从那条记录开始显示，还可以指定一共显示多少条记录。limit可以指定初始位置，也可以不指定初始位置;
• limit要查询中间部分的结果，要定义两个参数，参数1时开始取的第一条记录编号（注意在总查询结果中，第一条记录编号为0）；参数2是要查询记录的个数
• 例如：查询score表中，期末成绩高于85，从编号2开始，查询5条记录。

select * from score where final>60 limit 2,5;

二、聚合函数查询

• count() ：统计记录的条数；
• sum()：计算字段的值的总和；
• avg()：可以求出表中某个字段取值的平均值；
• max()：可以求出表中某个字段取值的最大值；
• min()：可以求出表中某个字段取值的最小值。

三、多表连接

1.内连接

• 内连接（inner join）：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。相当于查询A、B交集部分数据。
• 例如：查询选修课程号为c05109的学生的学号、姓名和期末成绩。

  ->  select student.studentno as '学号',student.sname as '姓名',score.final as '期末成绩'
    -> from student
    -> inner join score
    -> on student.studentno=score.studentno
    -> where score.courseno='c05109';

2.外连接

2.1 左外连接

• 左外连接（left outer join）是指将左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并在右表的相应列中添加null值。即：查询左表所有数据，以及两张表交集部分的数据。
• 例如：利用左外连接查询学生的学号，姓名，平时成绩和期末成绩。

mysql> select student.studentno as '学号',sname as '姓名',daily as'平时成绩',final as '期末成绩'
    -> from student
    -> left join score
    -> on student.studentno=score.studentno
    -> ;

2.2 右外连接

• 右外连接（right outer join）是指如果右侧表中的行与左侧表中的行不匹配，将为结果集中来及左侧表的所有列分配null值。即：查询右表所有数据，以及两张表交集部分数据。
• 例如：利用右连接方式查询教师的排课情况。

mysql> select teacher.teacherno,tname,major,courseno
    -> from teacher right join teach_course
    -> on teacher.teacherno = teach_course.teacherno;

2.3 连接多个表

• 对于3个以上关系表的连接查询，一般遵循下列规则：连接n个表至少需要n-1个连接条件，以避免笛卡尔积的出现。
• 例如：查询18级学生的学号、姓名、课程号、期末成绩及学分

mysql> select student.studentno,sname,cname,final,period/16
    -> from score join student on student.studentno=score.studentno
    -> join course on score.courseno=course.courseno
    -> where substring(student.studentno,1,2)='18';

2.4 合并多个结果集

• union操作符可以将多个select语句的返回结果组合到一个结果集中。使用union合并两个查询结果集时，所有查询中的列数和列的顺序必须相同且数据类型必须兼容。
• 例如：利用student表创建student01，将student01和student表的部分查询结果合并。

mysql>  create table student01 as
    -> select studentno,sname,phone from student;


mysql> select studentno,sname,phone from student01
    -> where phone like '%131%'
    -> union
    -> select studentno,sname,phone from student
    -> where phone like '%136%';

四、子查询

子查询可以报错in、not in、any、all、exists、not exists等逻辑运算符，也可以包含比较运算符，如 = 、!= 、>、<等。

根据子查询的结果又可以将mysql子查询分为4种类型。

（1）返回一个表的子查询时表子查询。

（2）返回带有一个或多个值的一行的子查询时行子查询。

（3）返回一行或多行，但每行只有一个值的时列子查询。

（4）只返回一个值的是标量子查询。从定义上讲，每个标量子查询都是一个列子查询和行子查询。

1.带比较运算符的子查询

例如：查询期末成绩比选修该课程平均期末成绩低的学生的学号，课程号和期末成绩。

mysql> select studentno,courseno,final
    -> from score as a
    -> where final <(select avg(final)
    -> from score as b
    -> where a.courseno=b.courseno
    -> group by courseno);

2.带in关键字的子查询

例如：获取期末成绩中含有高于93分的学生的学号、姓名、电话、和Email。

mysql> select studentno,sname,phone,Email
    -> from student
    -> where studentno in (select studentno
    -> from score
    -> where final>93);

3.带exists关键字的子查询

例如：查询student表中是否存在2001年12月12日以后出生的学生，如果存在，输出学生的学号、姓名、生日和电话。

mysql> select studentno,sname,birthdate,phone
    -> from student where exists(
    -> select * from student
    -> where birthdate > '2001-12-12');