大一下学期学习了课程“数据库应用技术”，本文是期末时对本课程的总结与归纳，同时便于以后复查。

一、表

1. 删除表记录 drop table ……

drop table if exists abc;
show tables;

2. 创建表 create table ( …… )

2.1 新建表

create database xscj;
use xscj;
-- default（默认值）例：
create table Course
(
    课程号 char(3) not null primary key,
    课程名 char(16) not null,
    开课学期 tinyint(1)  not null default 1,
    学分 tinyint(1) null
) engine = innodb;
-- 外键例：
create table Teacher
(
    教工号 char(2) not null primary key,
    姓名 char(5) not null,
    性别 char(1) not null,
    课程号 char(3) not null,
    foreign key(课程号) references Course(课程号)
) engine = innodb;
-- 复合主键例：
create table Score
(
    学号 char(7) not null,
    课程号 char(3) not null,
    成绩 int not null,
    primary key(学号, 课程号)
) engine = innodb;

2.2 复制现存表

2.2.1 只复制结构 create table …… like ……

create table Student2 like Student;

2.2.2 既复制结构也复制数据 create table …… as (select …… from ……)

create table Student2 as (
    select * from Student
);

3. 插入记录 insert into …… values ……

insert into Score
values
    ('2018001', '001', '68'),
    ('2018003', '001', '80');

insert into Score (学号, 课程号, 成绩)
values('2018004', '001', '95');

4. 更改表字段名（列名）alter table …… [change / rename column / modify]

更改 Student 表的“学号”字段名为“号学”。

alter table Student
    change 学号 号学 char(7) not null;

alter table Student
    rename column 号学 to 学号;

更改 Student 表的“学号”列的数据类型为“integer”。（若表中该列原有的数据类型与将要修改的列类型冲突，则无法修改）

alter table Student
    modify 学号 integer not null;

5. 增减列数 alter table …… [add / drop column]

P26-E.g. 3.5：在 xscj 数据库的 xs 表中，增加“奖学金等级”一列，并将表中的“姓名”列删除。

alter table xs
    add 奖学金等级 tinyint null,
    drop column 姓名;

6. 更新数据 update …… set …… (where ……)

Exp 21-3：将学生表中李丽的出生日期修改为2002-6-5。

update Student
set 出生日期 = '2002-6-5'
where 姓名='李丽';

Exp 21-10：将软件工程专业的学生的程序设计基础这门课成绩加上5分。

update score
set 成绩=成绩+5
where 学号 in (select 学号 from student where 专业='软件工程') and 课程号=(select 课程号 from course where 课程名='程序设计基础');

二、查询

1. Select

1.1 连表查询

Exp 21-4：查询高等数学成绩大于90分的所有学生的信息。

select *
from Student, Score, Course
where
    Course.课程号=Score.课程号 and
    Student.学号=Score.学号 and
    课程名='高等数学' and
    成绩>90;

Exp 21-6：求数据库应用技术中分数最高的学生姓名。

select 姓名, Max(成绩)
from Student, Score, Course
where
    Student.学号=Score.学号 and
    Course.课程号=Score.课程号 and
    课程名='数据库应用技术';

1.2 别名 …… as ……

Exp 21-7：查询高等数学成绩比程序设计基础成绩高的所有学生的学号。

select s1.学号
from score as s1, score as s2, course as c1, course as c2
where
    s1.`课程号`=c1.`课程号` and
    c1.`课程名`='高等数学' and
    s2.`课程号`=c2.`课程号` and
    c2.`课程名`='程序设计基础' and
    s1.`学号`=s2.`学号` and
    s1.`成绩`>s2.`成绩`;

2. Like (% 与 _)

Exp 21-8：查询姓林的老师的人数。

select count(*)
from Teacher
where 姓名 like '林%';

3. Order by

3.1 普通排序

Exp 21-16：将学生的情况按平均分排序。

select *, avg(成绩)
from Student, Score
where Student.学号=Score.学号
group by Student.学号
order by avg(成绩) desc;

3.2 Limit

limit 5 -- 前5行
limit 3,5 -- 从第4行开始的后5行

4. Group by & having

当一个查询语句同时出现 where, group by, having, order by 时，执行顺序是：

1. 对 from 选中的表执行 where 对数据做筛选，返回第1个结果集。
2. 针对第1个结果集使用 group by 分组，返回第2个结果集。
3. 针对第2个结果集中的每一组数据执行 select，有几组就执行几次，返回第3个结果集。
4. 针对第3个结果集执行 having 进行筛选，返回第4个结果集。
5. 针对第4个结果集进行 order by 排序，最后输出结果。

Exp 21-5：查询平均成绩大于85分的同学的学号、姓名和平均成绩。

select Student.学号, Student.姓名, avg(成绩) as 平均成绩
from Student, Score, Course
where Course.课程号=Score.课程号 and Student.学号=Score.学号
group by Student.学号
having 平均成绩>85;

Exp 21-9：查询学生人数超过4人的老师姓名和课程名。

select Teacher.姓名, Teacher.课程号
from Student, Teacher, Course, Score
where
    Student.学号=Score.学号 and
    Score.课程号=Course.课程号 and
    Teacher.课程号=Course.课程号
group by Teacher.姓名
having count(Teacher.姓名)>4;

5. 多表查询

5.1 见 Select

5.2 in

Exp 21-15：查询没有选修林丽老师课程的学生学号和姓名。

select 学号, 姓名
from Student
where 学号 not in (
    select 学号
    from Score
    where 课程号 in (
        select 课程号
        from Teacher
        where 姓名='林丽'
    )
);

6. 多表+子查询+分组

Exp 21-18：查询学生的平均成绩，按所在专业的人数降序排列。

select *, avg(成绩) as 平均成绩
from Student, Score, (
    select 专业, count(专业) as 人数
    from Student
    group by 专业
) as 专业人数
where Student.专业=专业人数.专业 and Student.学号=Score.学号
group by Student.专业
order by 专业人数.人数 desc;

7. 视图

7.1 创建视图 create view …… as ……

P68-E.g. 4.69：创建 xscj 数据库上的 xs_kc 视图，包括计算机专业各学生的学号、选修的课程号及成绩。要保证对该视图的修改都符合(with check option)专业名为计算机这个条件。

-- or replace 为替换已有同名视图。
create or replace view xscj.cj_kc
    as
    select xs.学号, 课程号, 成绩
    from xscj.xs, xscj.xs_kc
    where xs.学号=xs_kc.学号 and
        xs.专业名='计算机'
    with check option;

7.2 插入数据、修改数据

见表插入与删除。

7.3 修改视图

与 create view 类似。

三、其它

1. 索引

1.1 在原有表格增加索引 create index …… on ……

create index Student_index
on Student(学号(5) asc, 课程号 desc)
using btree;
-- 以上创建复合索引，外括号中为索引列，(5)为限制长度，asc升序为默认，desc为降序，btree为默认索引类型

1.2 ？修改表并添加索引 alter table …… add index ……

alter table Student
    add index Student_index using btree (姓名);
-- 括号中为索引列
alter table Student
    add primary key;

1.3 新建表时创建索引

create table Student
(
    学号 char(7) not null,
    姓名 char(5) not null,
    性别 char(1) not null,
    专业 char(10) null,
    出生日期 date not null,
    -- 下方创建两个索引
    primary key(学号),
    index rq(出生日期)
) engine = innodb;

1.4 查看与删除索引

show index from Student;
-- 上查看，下删除
drop index Student_index on Student;
alter table Student
    drop index Student_index;
alter table Student
    drop primary key;

2. 空值问题

2.1 null, not null, 表定义默认值为（可）空值

2.2 空值的比较 <=>

都为空值或相同，返回真值；否则返回假值。

3. 触发器 create trigger …… after delete on …… for each row ……

Exp 21-20：创建触发器，当删除学生表中的学生时，将成绩表中对应的学生成绩删除。

delimiter $$
create trigger Student_delete after delete
    on Student for each row
begin
    delete from Score where 学号=old.学号;
end$$
delimiter ;

4. 参照完整性 foreign key(列名) references

Exp 21-7：创建带有参照动作CASCADE的表book3，所有book3表中的书号都必须出现在book表中。

create table book
(
    书号  char(20) primary key,
    书名  char(20),
    购买时间 date,
    借出时间 date,
    foreign key(书号)
        references book_2(书号)
            on update cascade
);