Mysql查询总结

mysql

第一部分:准备数据

1 创建学生表


create table student (
    student_number varchar(20) primary key,
    student_name varchar(20) not null,
    student_sex varchar(20) not null default "men",
    check(student_sex='men' OR student_sex='women'),
    check(student_number>99 AND student_number<200),
    student_birthday datetime,
    class varchar(20)
);


扩展(check约束)
student_number 大于99,小于200
student_sex 值为 men 或者 women

2 插入数据


insert into student values('101','zhao-san','women','1992-01-01','95034');
insert into student values('102','wan-san','men','1993-01-01','95033');
insert into student values('103','sun-san','women','1994-01-01','95033');
insert into student values('104','li-san','men','1996-01-01','95033');
insert into student values('105','zhou-san','women','1991-04-01','95033');
insert into student values('106','fen-san','women','1991-11-01','95033');
insert into student values('107','chen-san','women','1994-06-02','95033');
insert into student values('108','chu-san','women','1951-06-01','95033');

3 创建教师表


create table teacher(
    teacher_number varchar(20) primary key,
    teacher_name varchar(20) not null,
    teacher_sex varchar(20) not null,
    teacher_birthday datetime,
    prof varchar(20) not null,
    depart varchar(20) not null
);

4 插入数据


insert into teacher values('201','kobe','men','1978-4-1','legend','basketball3');
insert into teacher values('202','james','men','1988-4-4','actor','basketball2');
insert into teacher values('203','jordan','women','1988-4-4','legend','basketball4');
insert into teacher values('204','durant','women','1981-4-4','legends','basketball1');
insert into teacher values('205','paul','men','1983-11-4','defense','basketball5');
insert into teacher values('206','yao','men','1983-11-4','defense','basketball7');
insert into teacher values('207','yi','men','1983-11-4','defense','basketball8');
insert into teacher values('208','allen','men','1983-11-4','defense','basketball9');

5 创建课程表


create table course(
    course_number varchar(20) primary key,
    course_name varchar(20) not null,
    teacher_number varchar(20) not null,
    foreign key (teacher_number) references teacher(teacher_number)
);

6 插入数据


insert into course values('303-1','english','201');
insert into course values('303-2','math','202');
insert into course values('303-3','python','203');
insert into course values('303-4','java','204');
insert into course values('303-5','c++','205');
insert into course values('303-6','auto','206');

7 创建成绩表


create table score(
    student_number varchar(20) not null,
    course_number varchar(20) not null,
    degree decimal,
    foreign key (student_number) references student(student_number),
    foreign key (course_number) references course(course_number),
    primary key(student_number,course_number)
);

# 联合主键: primary key(student_number,course_number)

8 插入数据


insert into score values('101','303-3','88');
insert into score values('102','303-4','89');
insert into score values('103','303-3','87');
insert into score values('104','303-3','86');
insert into score values('107','303-5','99');
insert into score values('107','303-2','92');
insert into score values('105','303-1','100');
insert into score values('106','303-1','82');
insert into score values('102','303-2','87');
insert into score values('101','303-6','95');

第二部分 : 查询练习

1 查询student表中所有记录

select * from student;

查询结果
mysql> select * from student;
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 102            | wan-san      | men         | 1993-01-01 00:00:00 | 95033 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 104            | li-san       | men         | 1996-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
| 106            | fen-san      | women       | 1991-11-01 00:00:00 | 95033 |
| 107            | chen-san     | women       | 1994-06-02 00:00:00 | 95033 |
| 108            | chu-san      | women       | 1951-06-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
8 rows in set (0.01 sec)

2 查询select 表中所有记录的student_number,student_sex及class字段(列)。

select student_name,student_sex,class from student;

查询结果
mysql> select student_name,student_sex,class from student;
+--------------+-------------+-------+
| student_name | student_sex | class |
+--------------+-------------+-------+
| zhao-san     | women       | 95034 |
| wan-san      | men         | 95033 |
| sun-san      | women       | 95033 |
| li-san       | men         | 95033 |
| zhou-san     | women       | 95033 |
| fen-san      | women       | 95033 |
| chen-san     | women       | 95033 |
| chu-san      | women       | 95033 |
+--------------+-------------+-------+
8 rows in set (0.00 sec)

3 查询教师表:查询教师所有不重复的单位(depart列 去重)

select distinct depart from teacher;

# distinct 排除重复

查询结果
mysql> select distinct depart from teacher;
+-------------+
| depart      |
+-------------+
| basketball3 |
| basketball2 |
| basketball4 |
| basketball1 |
| basketball5 |
| basketball7 |
| basketball8 |
| basketball9 |
+-------------+
8 rows in set (0.00 sec)

4 查询score表中成绩在80-90之间的所有记录


(1)select * from score where degree between 80 and 90;

查询区间 between 。。。 and 。。。

查询结果
mysql> select * from score where degree between 80 and 90;
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 101            | 303-3         |     88 |
| 102            | 303-2         |     87 |
| 102            | 303-4         |     89 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
| 106            | 303-1         |     82 |
+----------------+---------------+--------+
6 rows in set (0.00 sec)

# 另一种方式:直接运算符比较
(2)select * from score where degree > 80 and degree < 90;

5 查询score表中成绩为 86、87、88的所有记录

表示或者的关系的 in

select * from score where degree in(86,87,88);

查询结果
mysql> select * from score where degree in(86,87,88);
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 101            | 303-3         |     88 |
| 102            | 303-2         |     87 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
+----------------+---------------+--------+
4 rows in set (0.00 sec)

6 查询student表中为'95034'班或者 student_sex为'women'的所有记录

or表示或者

select * from student where class='95034' or student_sex='women';

查询结果
mysql> select * from student where class='95034' or student_sex='women';
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
| 106            | fen-san      | women       | 1991-11-01 00:00:00 | 95033 |
| 107            | chen-san     | women       | 1994-06-02 00:00:00 | 95033 |
| 108            | chu-san      | women       | 1951-06-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
6 rows in set (0.00 sec)

7 以class字段为标准按照降序查询student记录(默认升序)

降序查询
select * from student  order by class desc;

mysql> select * from student  order by class desc;
+----------------+--------------+-------------+---------------------+-------+
| student_number | student_name | student_sex | student_birthday    | class |
+----------------+--------------+-------------+---------------------+-------+
| 101            | zhao-san     | women       | 1992-01-01 00:00:00 | 95034 |
| 102            | wan-san      | men         | 1993-01-01 00:00:00 | 95033 |
| 103            | sun-san      | women       | 1994-01-01 00:00:00 | 95033 |
| 104            | li-san       | men         | 1996-01-01 00:00:00 | 95033 |
| 105            | zhou-san     | women       | 1991-04-01 00:00:00 | 95033 |
+----------------+--------------+-------------+---------------------+-------+
8 rows in set (0.00 sec)


升序查询
# 两种形式
select * from student order by class;
select * from student order by class asc;

8 以course_number升序,degree降序查询score表中所有记录


select * from score order by course_number asc,degree desc;

查询结果
mysql> select * from score order by course_number asc,degree desc;
+----------------+---------------+--------+
| student_number | course_number | degree |
+----------------+---------------+--------+
| 105            | 303-1         |    100 |
| 106            | 303-1         |     82 |
| 107            | 303-2         |     92 |
| 102            | 303-2         |     87 |
| 101            | 303-3         |     88 |
| 103            | 303-3         |     87 |
| 104            | 303-3         |     86 |
| 102            | 303-4         |     89 |
| 107            | 303-5         |     99 |
| 101            | 303-6         |     95 |
+----------------+---------------+--------+
10 rows in set (0.00 sec)

9 查询student表中 '95034'班的人数


统计 count
select count(*) from student where class='95034';

查询结果
mysql> select count(*) from student where class='95034';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

10 查询score表中的最高分的学生学号及课程号(子查询或者排序)


最大值  max

select student_number,course_number from score where degree=(select max(degree) from score);

mysql> select student_number,course_number from score where degree=(select max(degree) from score);
+----------------+---------------+
| student_number | course_number |
+----------------+---------------+
| 105            | 303-1         |
+----------------+---------------+
1 row in set (0.00 sec)

推荐阅读更多精彩内容

  • 一,表关系的练习测试 请创建如下表关系,并建立相关约束 一,创建表结构数据: 创建的话肯定先创建没有关联的表,老师...
    Kevin_Luo阅读 1,301评论 0 0
  • 就业班开办初衷课程学习同学进度跟踪(持续更新)就业班同学转行成功经验分享就业班BI案例介绍 测试题共分为三部分,s...
    大石兄阅读 1,445评论 1 3
  • 内容梗概   本文将通过一组数据表及其数据查询案例,来讲解关于(1)对表和字段使用别名(2)联表查询(3)聚合函数...
    果汁凉茶丶阅读 300评论 0 0
  • 2018年7月11日笔记 1.新建数据库 设有一个数据库,包括四个表:学生表(student)、课程表(cours...
    潇洒坤阅读 1,306评论 0 1
  • 哈里·基恩想和新教练何塞·穆里尼奥建立一种“牢固的关系”,这将有助于托特纳姆更上一层楼。 凯恩在4-2战胜奥林匹亚...
    疯狂SPORTS阅读 5,164评论 0 5