mysql查询语句复习小结

SQL查询语句基本语法：

select 字段列表

  from 表名|视图列表

    [where 条件表达式1]

    [group by 属性名1 [having 条件表达式2]]

    [order by 属性名2 [asc|desc]]

一、单表查询

   1、使用*查询表中所有字段

        eg：select * from student; -- 查询所有学生信息

        

   2、查询指定字段

       eg：select name,age from student; -- 查询所有学生的姓名和年龄

       

   3、查询指定记录

       使用where关键字，语法格式为：where  条件表达式

       eg：select name 姓名,age 年龄 from student where id = 20150706801; -- 查询学号为20150706801的学生的姓名和年龄

       

二、关联查询

   1、使用join关键字关联查询

        ①内连接(inner join) ：连接两张表，连接条件使用on关键字，内连接只会显示匹配的数据记录

           eg：select a.name 姓名,b.subject 科目,b.score 分数 from student a inner join score b on a.id = b.sid; -- 查询学生姓名、科目、分数

                    

        ②外连接：外连接包括左连接和右连接

          左连接(left join)：返回左表中所有记录以及右表中符合连接条件的所有记录

          eg：select a.name 姓名,b.subject 科目,b.score 分数 from student a left join score b on a.id = b.sid; -- 使用左连接查询学生姓名、科目、分数

          

          右连接(right join)：返回右表中所有记录以及左表中符合连接条件的所有记录

          eg：select a.name 姓名,b.subject 科目,b.score 分数 from student a right join score b on a.id = b.sid; -- 使用右连接查询学生姓名、科目、分数

          

      注：内外链接区别：内连接只会显示匹配的数据记录，外连接例如左连接会把左边表中所有记录显示出来，即使在右边表中没有匹配记录也会显示左表的数据，右连接反之。

   2、使用表和表之间相同id关联查询

       这种关联方式和内连接一样，只会显示出匹配的数据

       eg：select a.name 姓名,b.subject 科目,b.score 分数 from student a,score b where a.id = b.sid;

       

三、使用聚合函数查询

     count() 统计记录的条数

     avg() 求平均值

     sum() 求和

     max() 求最大值

     min() 求最小值

     eg：select count(*) 学生总数 from student; -- 查询有多少学生

           select avg(age) 平均年龄 from student; -- 查询学生平均年龄 

           select max(age) 最大年龄 from student; -- 查询学生的最大年龄

           select min(age) 最小年龄 from student; -- 查询学生的最小年龄

四、子查询

     eg：select st.name , sc.score from student st,score sc where st.id = sc.sid and sc.score = (select max(score) from score);-- 查询成绩最高的学生姓名和成绩

     

五、合并查询结果

     使用union关键字合并查询结果，语法格式如下：

     select 字段列表1 from table1 union [all] select 字段列表2 from table2

     eg：select name 姓名,sex 性别,tel 电话 from student union select name,sex,phone from teacher;

     注：union和union all的区别，union会去掉重复的记录，在结果集合并后悔对新产生的结果集进行排序运算，效率稍低，union all直接合并结果集，如果确定没有重复记录，建议使用union all

六、为表和字段添加别名

   使用 as 关键字，as关键字可省略

   1、给表添加别名

     eg：select s.name,s.age from student as s where s.sex='男'; -- 查询所有男生的姓名和年龄

           select s.name,s.age from student s where s.sex='男'; -- 查询所有男生的姓名和年龄

   2、给字段添加别名

     eg： select name as 姓名,age as 年龄 from student;

            select name 姓名,age 年龄 from student; -- as可以省略

七、使用in关键字查询指定范围的记录

       eg：select s.name,s.age,s.tel from student s where s.age in (20,21); -- 查询年龄为20或者21的学生姓名和电话

             select s.name,s.age,s.tel from student s where s.age not in (20,21); -- 查询年龄不为20、21的学生姓名和电话

             select s.name,s.age,s.tel from student s where s.age=20 or s.age=21; -- 查询年龄为20或者21的学生姓名和电话  in查询可以转换为用or查询

八、使用between...and...查询

       eg：select s.name from student s,score sc where s.id=sc.sid and sc.score between 80 and 90; -- 查询分数在80分到90分之间的学生姓名

             上句可以写成 select s.name from student s,score sc where s.id=sc.sid and sc.score >=80 and sc.score <=90;

九、模糊查询

        通配符%：匹配任意长度字符，包括零字符

        通配符_：匹配任意一个字符

        eg：select s.name from student s where s.name like '张%'; -- 查询出所有姓张的同学

               

              select s.name from student s where s.name like '_芷%'; -- 查询姓名中第二个字是芷的同学

              

十、查询空值

       数据库里的空有两种情况：null或者是空字符串，如下图

       

        eg：select s.name,s.tel from student s where tel is not null; 

              结果如下：

              select s.name,s.tel from student s where tel is not null and tel =' ';

              结果如下： 

十一、使用and|or关键字多条件查询

       eg：select s.name,s.age from student s where s.age=21 and s.sex = '女'; -- 查询年龄为21岁的女同学

             

             select s.name,s.age,s.sex from student s where s.age=21 or s.sex = '女'; -- 查询年龄为21岁或者是性别为女的学生

             

十二、查询结果去掉重复记录

        使用distinct关键字

        eg：select distinct s.age from student; -- 查询学生的年龄并去掉重复记录

十三、对查询结果排序

        使用order by来排序，语法格式：order by 排序字段 desc|asc

         desc 降序，大—>小

         asc 升序，小—>大，可省略不写，默认为升序

        eg：select st.name,sc.subject,sc.score from score sc,student st where sc.sid = st.id order by sc.score desc; -- 查询学生成绩并按照成绩降序排列

十四、分组查询

        使用group by进行分组，过滤条件使用having关键字

        eg：select st.name 姓名,avg(sc.score) 平均分 from student st,score sc where st.id = sc.sid group by st.name having avg(sc.score)>70;  -- 查询平均成绩在70分以上的学生姓名和平均成绩

十五、使用limit限制查询记录数量

        eg：select * from student limit 5; -- 显示前5条

              select * from student limit 2,5; -- 实际上查出来的是第3条到第6条

附：使用表如下：

学生表 student                                 

CREATE TABLE `student` (
`id` varchar(11) NOT NULL,
`name` varchar(5) DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`tel` varchar(11) DEFAULT NULL
);

成绩表 score                                      

CREATE TABLE `score` (
`sid` varchar(11) DEFAULT NULL,
`classid` varchar(3) DEFAULT NULL,
`subject` varchar(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL
);

教师表 teacher                                 

CREATE TABLE `teacher` (
`id` varchar(3) DEFAULT NULL,
`classid` varchar(3) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`sex` varchar(4) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL
);