mysql查询练习(一)

一、条件准备:
    新建四个表:

    1、学生表:Student
    
    mysql> create table student(
    -> sno varchar(20) primary key,      #学生ID
    -> sname varchar(20) not null,        #学生名字
    -> ssex varchar(10) not null,        #学生性别
    -> sbirthday datetime,                #学生生日
    -> class varchar(20)                #所在班级
    -> );
    Query OK, 0 rows affected (0.15 sec)


    
    2、教师表:Teacher
    
    mysql>  create table teacher(
    ->  tno varchar(20) primary key,        #教师编号
    -> tname varchar(20) not null,            #教师名字    
    -> tsex varchar(20) not null,            #教师性别        
    -> tbirthday datetime,                    #教师出生
    -> prof varchar(20) not null,            #教师职称
    -> depart varchar(20) not null            #教师所在部门
    -> );
    Query OK, 0 rows affected (0.09 sec)
    
    
    3、课程表:Course
    
    mysql> create table course(
    -> cno varchar(20) primary key,                    #课程ID
    -> cname varchar(20) not null,                    #课程名称
    -> tno varchar(20) not null,                    #老师编号
    -> foreign key(tno) references teacher(tno)        #外键约束
    -> );
    Query OK, 0 rows affected (0.04 sec)    
    
    
    4、成绩表:Score
    
    mysql> create table score(
    -> sno varchar(20) not null,                    #学生ID
    -> cno varchar(20) not null,                    #课程ID
    -> degree decimal,                                #成绩
    -> foreign key(sno) references student(sno),    #学生ID外键约束
    -> foreign key(cno) references course(cno),        #课程ID外键约束
    -> primary key(sno,cno)
    -> );
    Query OK, 0 rows affected (0.10 sec)
    
    
    往数据表中添加数据:
    
    1、添加学生信息
    insert into student values('101','a1','boy','1977-10-01','95033');
    insert into student values('107','a2','girl','1978-9-11','95034');
    insert into student values('102','a3','boy','1977-8-12','95033');
    insert into student values('103','a4','girl','1978-10-01','95034');
    insert into student values('104','a5','boy','1977-10-11','95033');
    insert into student values('105','a6','girl','1977-9-12','95034');
    insert into student values('106','a7','boy','1977-8-01','95033');
    
    2、添加教师信息
    insert into teacher values('804','b1','man','1958-12-02','t0','computer');
    insert into teacher values('805','b2','woman','1958-12-12','t1','computer1');
    insert into teacher values('806','b3','man','1958-12-02','t2','computer2');
    insert into teacher values('807','b4','woman','1958-12-12','t3','computer3');
    
    3、添加课程信息
    insert into course values('3-105','english','805');
    insert into course values('3-106','english1','804');
    insert into course values('3-107','english2','806');
    insert into course values('3-108','english3','807');
    
    4、添加学生成绩
    insert into score values('101','3-105','86');
   insert into score values('101','3-106','70');
   insert into score values('102','3-107','75');
   insert into score values('102','3-108','60');
   insert into score values('103','3-105','59');
   insert into score values('103','3-106','99');
   insert into score values('104','3-107','48');
   insert into score values('104','3-108','81');
   insert into score values('105','3-105','82');
   insert into score values('105','3-106','84');
   insert into score values('106','3-107','78');
   insert into score values('106','3-108','10');
   insert into score values('107','3-105','100');
   insert into score values('107','3-106','68');
    
二、查询练习
    1、查询student表的所有记录
    
    mysql> select * from student;
        +-----+-------+------+---------------------+-------+
        | sno | sname | ssex | sbirthday           | class |
        +-----+-------+------+---------------------+-------+
        | 101 | a1    | boy  | 1977-10-01 00:00:00 | 95033 |
        | 102 | a3    | boy  | 1977-08-12 00:00:00 | 95033 |
        | 103 | a4    | girl | 1978-10-01 00:00:00 | 95034 |
        | 104 | a5    | boy  | 1977-10-11 00:00:00 | 95033 |
        | 105 | a6    | girl | 1977-09-12 00:00:00 | 95034 |
        | 106 | a7    | boy  | 1977-08-01 00:00:00 | 95033 |
        | 107 | a2    | girl | 1978-09-11 00:00:00 | 95034 |
        +-----+-------+------+---------------------+-------+
        7 rows in set (0.00 sec)

    2、查询student表中的所有记录的sname、ssex和class列
    
    mysql> select sname,ssex,class from student;
        +-------+------+-------+
        | sname | ssex | class |
        +-------+------+-------+
        | a1    | boy  | 95033 |
        | a3    | boy  | 95033 |
        | a4    | girl | 95034 |
        | a5    | boy  | 95033 |
        | a6    | girl | 95034 |
        | a7    | boy  | 95033 |
        | a2    | girl | 95034 |
        +-------+------+-------+
        7 rows in set (0.01 sec)

    3、查询教师所有的单位即不重复的depart列
    
    不重复使用:distinct
    
    mysql> select distinct depart from teacher;
            +-----------+
            | depart    |
            +-----------+
            | computer  |
            | computer1 |
            +-----------+
            2 rows in set (0.00 sec)

    4、查询score表中成绩在60到80之间的所有记录
    
    之间使用:between。。。and。。。
    
    mysql> select * from score where degree between 60 and 80;
        +-----+-----+--------+
        | sno | cno | degree |
        +-----+-----+--------+
        | 101 | 805 |     70 |
        | 102 | 806 |     75 |
        | 102 | 807 |     60 |
        | 106 | 806 |     78 |
        | 107 | 807 |     68 |
        +-----+-----+--------+
        5 rows in set (0.00 sec)
        
        
    mysql> select * from score where degree > 60 and degree < 80;
        +-----+-----+--------+
        | sno | cno | degree |
        +-----+-----+--------+
        | 101 | 805 |     70 |
        | 102 | 806 |     75 |
        | 106 | 806 |     78 |
        | 107 | 807 |     68 |
        +-----+-----+--------+
        4 rows in set (0.00 sec)

    5、查询score表中成绩为81、85或88的记录
    
    mysql> select * from score where degree = 85 or degree = 86 ;
        +-----+-----+--------+
        | sno | cno | degree |
        +-----+-----+--------+
        | 101 | 804 |     86 |
        +-----+-----+--------+
        1 row in set (0.00 sec)
        
    mysql> select * from score where degree in (85,86);
        +-----+-----+--------+
        | sno | cno | degree |
        +-----+-----+--------+
        | 101 | 804 |     86 |
        +-----+-----+--------+
        1 row in set (0.00 sec)


    6、查询student表中“95033”班或性别为“女”的同学记录
    
    mysql> select * from student where ssex = 'girl' or class = '95033';
        +-----+-------+------+---------------------+-------+
        | sno | sname | ssex | sbirthday           | class |
        +-----+-------+------+---------------------+-------+
        | 101 | a1    | boy  | 1977-10-01 00:00:00 | 95033 |
        | 102 | a3    | boy  | 1977-08-12 00:00:00 | 95033 |
        | 103 | a4    | girl | 1978-10-01 00:00:00 | 95034 |
        | 104 | a5    | boy  | 1977-10-11 00:00:00 | 95033 |
        | 105 | a6    | girl | 1977-09-12 00:00:00 | 95034 |
        | 106 | a7    | boy  | 1977-08-01 00:00:00 | 95033 |
        | 107 | a2    | girl | 1978-09-11 00:00:00 | 95034 |
        +-----+-------+------+---------------------+-------+
        7 rows in set (0.01 sec)


    7、以class降序查询student表的所有记录
    
    order by
    
    降序:desc
    mysql> select * from student order by class desc;
        +-----+-------+------+---------------------+-------+
        | sno | sname | ssex | sbirthday           | class |
        +-----+-------+------+---------------------+-------+
        | 103 | a4    | girl | 1978-10-01 00:00:00 | 95034 |
        | 105 | a6    | girl | 1977-09-12 00:00:00 | 95034 |
        | 107 | a2    | girl | 1978-09-11 00:00:00 | 95034 |
        | 101 | a1    | boy  | 1977-10-01 00:00:00 | 95033 |
        | 102 | a3    | boy  | 1977-08-12 00:00:00 | 95033 |
        | 104 | a5    | boy  | 1977-10-11 00:00:00 | 95033 |
        | 106 | a7    | boy  | 1977-08-01 00:00:00 | 95033 |
        +-----+-------+------+---------------------+-------+
        7 rows in set (0.00 sec)
        
    升序:asc
    mysql> select * from student order by class asc;
        +-----+-------+------+---------------------+-------+
        | sno | sname | ssex | sbirthday           | class |
        +-----+-------+------+---------------------+-------+
        | 101 | a1    | boy  | 1977-10-01 00:00:00 | 95033 |
        | 102 | a3    | boy  | 1977-08-12 00:00:00 | 95033 |
        | 104 | a5    | boy  | 1977-10-11 00:00:00 | 95033 |
        | 106 | a7    | boy  | 1977-08-01 00:00:00 | 95033 |
        | 103 | a4    | girl | 1978-10-01 00:00:00 | 95034 |
        | 105 | a6    | girl | 1977-09-12 00:00:00 | 95034 |
        | 107 | a2    | girl | 1978-09-11 00:00:00 | 95034 |
        +-----+-------+------+---------------------+-------+
        7 rows in set (0.00 sec)
    
    
    8、以con升序、degree降序查询score表的所有记录
    
    mysql> select * from score order by cno asc,degree desc;
        +-----+-----+--------+
        | sno | cno | degree |
        +-----+-----+--------+
        | 107 | 804 |    100 |
        | 101 | 804 |     86 |
        | 105 | 804 |     82 |
        | 103 | 804 |     59 |
        | 103 | 805 |     99 |
        | 105 | 805 |     84 |
        | 101 | 805 |     70 |
        | 106 | 806 |     78 |
        | 102 | 806 |     75 |
        | 104 | 806 |     48 |
        | 104 | 807 |     81 |
        | 107 | 807 |     68 |
        | 102 | 807 |     60 |
        | 106 | 807 |     10 |
        +-----+-----+--------+
        14 rows in set (0.00 sec)


    9、查询“95033”班的学生人数
    
    统计:count
    
    mysql> select count(*) from student where class='95033';
        +----------+
        | count(*) |
        +----------+
        |        4 |
        +----------+
        1 row in set (0.06 sec)


    10、查询score表中的最高分的学生学号和课程号。(子查询或者排序)
    
    mysql> select sno,cno from score where degree=(select max(degree) from score); 
        +-----+-----+
        | sno | cno |
        +-----+-----+
        | 107 | 804 |
        +-----+-----+
        1 row in set (0.00 sec)

      此条查询分两步:1、先找到最高分,2、再找最高分的sno和cno

       
       mysql> select sno,cno,degree from score order by degree desc limit 0,1; 
            +-----+-----+--------+
            | sno | cno | degree |
            +-----+-----+--------+
            | 107 | 804 |    100 |
            +-----+-----+--------+
            1 row in set (0.00 sec)
            
        
        解析:limit 0,1      #提取表单中的1条数据,从0位置开始

    

发布了34 篇原创文章 · 获赞 0 · 访问量 1016
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览