[toc]

建库

1
2
3
4
5
# 需求
库名：linux50 字符集：utf8 校验规则：utf8_general_ci

# 创建数据库
create database if not exists linux50 charset utf8 collate utf8_general_ci;

建表

  • 建表一

    1
    2
    3
    4
    5
    6
    7
    8
    9
    create table linux50.student(
    sno int(20) unsigned auto_increment comment '学号(主键)',
    sname varchar(5) not null comment '学生姓名',
    sage tinyint(3) unsigned not null comment '学生年龄',
    ssex enum('0','1') not null default '1' comment '学生性别(1是男，0是女)默认为男',
    sbirthday datetime null comment '学生生日',
    class varchar(3) not null comment '学生班级',
    primary key(sno)
    ) charset utf8;

  • 建表二

    1
    2
    3
    4
    5
    6
    create table linux50.course(
    cno int(20) unsigned auto_increment comment '课程号(主键)',
    cname varchar(4) not null comment '课程名称',
    tno int(3) zerofill not null comment '教师编号',
    primary key(cno)
    )charset utf8;

  • 建表三

    1
    2
    3
    4
    5
    6
    create table linux50.score(
    sno int(20) unsigned comment '学号(主键)',
    cno int(20) unsigned comment '课程号(主键)',
    mark float(4,1) not null comment '成绩',
    primary key(cno,sno)
    ) charset utf8;

  • 建表四

    1
    2
    3
    4
    5
    6
    7
    8
    9
    create table linux50.teacher(
    tno int(3) zerofill unsigned auto_increment comment '教师编号（主键）',
    tname varchar(5) not null comment '教师姓名',
    tage tinyint(3) unsigned not null comment '教师年龄',
    tsex enum('0','1') not null default '1' comment '教师性别（1是男，0是女）默认为男）',
    prof varchar(5) not null comment '教师职称',
    depart varchar(5) not null comment '教师部门',
    primary key(tno)
    ) charset utf8;

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 表一：插入数据
insert linux50.student(sname,sage,ssex,sbirthday,class)
value('徐导',20,'1','2018-11-27 22:13:03',1),
('曾导',18,'1','2018-11-27 22:13:03',1),
('李导',25,'1','2018-11-27 22:13:03',2);

# 表二：插入数据
insert linux50.course(cname,tno)
value('英语',1),
('语文',2),
('数学',3);

# 表三：插入数据
insert linux50.score(sno,cno,mark)
value(1,1,'90.0'),
(2,1,'10.0'),
(3,1,'60.0'),
(1,2,'90.0'),
(2,2,'99.5'),
(3,2,'80.0'),
(1,3,'80.5'),
(2,3,'60.0'),
(3,3,'88.0');

# 表四：插入数据
insert linux50.teacher(tname,tage,tsex,prof,depart)
value('曾志高翔',18,'1','教学总监','语言系'),
('徐亮伟',50,'1','讲师','文学系'),
('李永宜',80,'1','助教','科学系');


示例

  1. 将自己班级小组所有人员信息插入到student表中（数据自定义）

    1
    2
    3
    4
    5
    6
    7
    8
    insert linux50.student(sname,sage,ssex,sbirthday,class)
    value('徐导',20,'1','2018-11-27 22:13:03',1),
    ('曾导',18,'1','2018-11-27 22:13:03',1),
    ('李导',25,'1','2018-11-27 22:13:03',2),
    ('翁子坤',33,'1','2018-11-27 22:13:03',3),
    ('苏仕团',44,'1','2018-11-27 22:13:03',3),
    ('贺猛',55,'1','2018-11-27 22:13:03',3),
    ('贺庆文',18,'1','2018-11-27 22:13:03',3);

  2. 将曾导、徐导、李导信息插入教师表中（数据自定义）

    1
    2
    3
    4
    5
    6
    7
    insert linux50.teacher(tname,tage,tsex,prof,depart)
    value('曾志高翔',18,'1','教学总监','语言系'),
    ('徐亮伟',50,'1','讲师','文学系'),
    ('李永宜',80,'1','助教','科学系'),
    ('徐导',20,'1','教务处主任','文学系'),
    ('曾导',18,'1','事务处处长','艺术系'),
    ('李导',25,'1','英语讲师','体育系');

  3. 将数学、语文、英语学科插入到课程表中（数据自定义）

    1
    2
    3
    4
    insert linux50.course(cname,tno)
    value('英语',1),
    ('语文',2),
    ('数学',3);

  4. 将分数插入到成绩表中（数据自定义）

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    insert linux50.score(sno,cno,mark)
    value(1,1,'90.0'),
    (2,1,'10.0'),
    (3,1,'60.0'),
    (1,2,'90.0'),
    (2,2,'99.5'),
    (3,2,'80.0'),
    (1,3,'80.5'),
    (2,3,'60.0'),
    (3,3,'88.0');

查询练习

  1. 查询student表中的所有记录的sname、ssex和class列。

    1
    2
    select sname,ssex,class
    from linux50.student;

  2. 查询教师所有的单位即不重复的depart列。

    1
    2
    select depart
    from linux50.teacher;

  3. 查询student表的所有记录。

    1
    2
    select *
    from linux50.student;

  4. 查询score表中成绩在60到80之间的所有记录。

    1
    2
    3
    4
    select sno,cno,mark
    from linux50.score
    where mark>60
    and mark<80;

  5. 查询score表中成绩为85，86或88的记录。

    1
    2
    3
    4
    5
    select sno,cno,mark
    from linux50.score
    where mark=85
    or mark=86
    or mark=88;

  6. 查询student表中1班或性别为“女”的同学记录。

    1
    2
    3
    4
    select *
    from linux50.student
    where class=1
    or ssex=0;

  7. 以class降序查询Student表的所有记录。 

    1
    2
    3
    select *
    from linux50.student
    order by sno desc;

  8. 以cno升序、mark降序查询Score表的所有记录

    1
    2
    3
    4
    select *
    from linux50.score
    order by cno
    and mark desc;

  9. 查询2班的学生人数。

    1
    2
    3
    4
    select class,count(class)
    from linux50.student
    where class=2
    group by class;

  10. 查询”曾志高翔“教师任课的学生成绩。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 传统连接
    select course.tno,teacher.tname,course.cname,score.mark
    from course,teacher,score
    where course.tno=teacher.tno
    and course.cno=score.cno
    and course.cno='1';

    # 内连接
    select course.tno,teacher.tname,course.cname,score.mark
    from course join teacher join score
    on course.tno=teacher.tno
    and course.cno=score.cno
    where course.cno='1';

  11. 查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    # 传统连接
    select course.cname,student.sname,student.ssex,score.mark,teacher.tname,teacher.prof,teacher.depart
    from student,course,teacher,score
    where student.sno=score.sno
    and course.cno=score.cno
    and course.tno=teacher.tno
    and course.cname='语文';

    # 内连接
    select course.cname,student.sname,student.ssex,score.mark,teacher.tname,teacher.prof,teacher.depart
    from student join course join teacher join score
    on student.sno=score.sno
    and course.cno=score.cno
    and course.tno=teacher.tno
    where course.cname='语文';

  12. 把11题查出的成绩按照降序排序。

    1
    2
    3
    4
    5
    6
    7
    select course.cname,student.sname,student.ssex,score.mark,teacher.tname,teacher.prof,teacher.depart
    from student join course join teacher join score
    on student.sno=score.sno
    and course.cno=score.cno
    and course.tno=teacher.tno
    where course.cname='语文'
    order by score.mark desc;