数据库基础与MySQL程序设计

数据库基础与MySQL程序设计

本文是笔者经整理后的MySQL公选课笔记，此前笔者没有任何数据库基础。或多舛误，敬请谅解。

一. MySQL中的数据库组成

1.1 数据库对象

数据库（dataset)

表（table）

视图（View）：虚拟的表，在显示上整合了多个表的数据，便于查询。可参考视图介绍。

主键（primary key）：用以唯一确定一个实体的属性

外键（foreign key）：一个表中，用以和另一个表构建关系的属性。一个表的外键通常是另一个表的主键

事务：逻辑上相互关联、一致完成的数据库操作。具有原子性（最小的有意义的操作）、一致性（都执行或都不执行）、隔离性（事务之间没有必然的逻辑关联）、持久性（对数据库产生持久的影响）。详情参考数据库事务的简单介绍。

锁（锁定机制）：同时对数据进行并发操作时的处理方案，常见的有行级锁（不可同时操作同一行）、表级锁（不可同时操作同一张表）。

1.2 MySQL对象

索引（index）：专门用于查找的字段，会相比较于正常字段，耗费更大的存储空间，但SELECT查找速度更快。

数据结构

1. 整数类型：tinyint, smallint, mediumint, int, bigint
2. 浮点数类型：float(M,D), double(M,D)
3. 定点数类型：dec(M,D)或decimal(M,D)
4. 位类型：bit, binary
5. 时间类型：date, time(fsp), daytetime, timestamp
6. 字符串类型：char, varchar
7. 枚举类型：enum
8. 集合：set

约束：对表中某个属性的要求

1. primary key
2. not null
3. unsigned：针对数的存储，可以扩大正数存储范围
4. zerofill: 位数不够用0来凑
5. AUTO_INCREMENT: 自增列
6. DEFAULT value: 不指定时用value填充

存储引擎：用以存储数据的文件系统

1. InnoDB：支持事务、行级锁、外键约束、索引。数据存储在硬盘上。
2. MEMORY：存储在内存中，访问快但功能简单（上述三种都不支持）。仅用于存储临时数据。
3. Archive：用于存储大量历史数据，拥有较高压缩比，占用空间小，但访问性能差。
4. MyISAM：读取性能快，存储空间小，常用于日志存储。

详情见MySQL四大引擎。查询本机MySQL支持的引擎，可键入下述命令：

SHOW ENGINES

存储过程：对一系列SQL操作的封装和预编译，相较于函数，不要求有返回值。

函数：为返回值而生的存储过程，用于进行复杂运算。

触发器：依赖于某张表的、起到约束作用的存储过程。

二. SQL语言基础

2.1 DDL (Data Definition Language): CREATE, DROP, ALTER

2.1.1 CREATE：创建数据库、表

创建数据库：

CREATE DATABASE name;

查询已有数据库：

SHOW DATABASES;

创建表举例：

create table course(
	courseid int primary key,
	name varchar(45),
	studentid int,
	foreign key(studentid) references students(id))
	engine=InnoDB
	default charset=utf8;

其中engine和default charset可省略，省略时默认如上。

对任意属性，应按如下格式定义：

[name] [type] [constraints]

对外键的定义则按如下格式：

foreign key(本列中的属性名) references 被关联的另一个表(另一个表中对应的属性名) ON DELETE/UPDATE 外键约束；

其中“ON DELETE/UPDATE 外键约束“表示”在父表[删除]/[更新]时执行外键约束。注意定义外键的表是子表，而触发约束的是父表。常见外键约束包括：

1. cascade：同步删除/更新
2. No action或restrict：不允许父表进行任何操作
3. set null

上述格式在ALTER语句中也适用。

查询表的定义：

DESC tablename;

查询表的详细定义语句：

SHOW CREATE TABLE tablename;

创建视图：

create view viewname as 
select col1,col2 from tablename where 条件

2.1.2 DROP: 删除数据库、表

删除表：

DROP TABLE tablename;

删除数据库：

DROP DATABASE name;

删除视图：

DROP VIEW ViewName;

2.1.3 ALTER: 修改表定义

此处只探讨表的修改。

基本格式：

ALTER TABLE [修改类型] [修改内容]

ADD 添加：

ALTER table 表名 ADD [COLUMN] 列名 数据类型 位置

举例如下：

ALTER table student ADD gender enum('m','f') NOT NULL AFTER student_ID

也可以添加主键：

alter table scores add primary key(course_ID,student_ID);

这里我们以course_ID和student_ID两个属性为一个复合主键。

也可以添加外键约束：

alter table 表名 add foreign key(本表外键名) references 主表名(主表外键名) on ...

DROP 删除（某一列）：

alter table 表名 drop 列名;

删除外键约束：

alter table 表名 drop foreign key 外键约束名

MODIFY 修改某一列定义：

ALTER TABLE 表名 MODIFY 列名 数据类型 位置

RENAME 重命名：

alter table 表名 rename 新名

修改引擎：

alter table name engine=the_name_of_engine

2.2 DML: INSERT(增), DELETE(删), UPDATE(改), SELECT(查)

插入数据：

INSERT INTO 表名(列名,...) VALUES(),(),...

例如：

INSERT INTO student(name) VALUES('zhang'),('qian')

删除整个实体（一行）：

DELETE FROM 表名 where 条件

例如：

DELETE FROM student where ID=1

特别地，删除表中全部数据，但不删除表的定义：

DELETE FROM 表名

更新（修改）数据：

UPDATE 表名 SET 列名=值 where 条件

查询数据

基本查询：

SELECT 字段,... from 表名;

降序查询举例：

SELECT * FROM student ORDER BY ID DESC;

升序查询举例：

SELECT * FROM student ORDER BY ID ASC;

查询前4行：

SELECT * FROM student LIMIT 4;

查询第1、3行：

SELECT * FROM student LIMIT 1,3;

利用聚合函数查询：

常用聚合函数包括：sum(), count(), max(), min(), avg()

举例：

select count(*) from student;

意即计数student表已有总行数。也可以用as关键词为该列设置别名：

select count(*) as num from student;

这样查询到的字段名就会变成num。

分类聚合（group by）举例：

select class,count(*) from student group by class

即按照student表中class的属性的不同，分类统计行数。也可在分类聚合后，对所有数据再进行一次聚合，例如：

select class,count(*) from student group by class

下面讨论带条件限制（where/having）的查询：

​ where在分类前筛选数据，having筛选分类后的数据。having只应在聚合时使用。having用法举例如下：

select count(*) from scores group by course_ID having course_ID=1;

实际上，这将统计所有course_ID为1的数据的条数。在这个逻辑下，下面两个语句与上面的例子等价：

select count(*) from scores where course_ID=1 group by course_ID;
select count(*) from scores where course_ID=1;

通过where进行跨表查询的方式，叫作内连接。内连接中各表地位均等。与内连接相对的被称为外连接，外连接又分为左连接和右连接。所谓“左”“右”是就两个表的地位而言的，其本质是查询“右”表的全部数据，同时以“左”表数据做补充。也就是说，在外连接中，右表的地位高于左表。左连接举例如下：

select students.fullname,semester_grades.*
	from semester_grades left join students
	on students.ID=semester_grades.student_ID;

在上面的例子里，右表是semester_grades，左表是students，其结果包含了semester_grades的所有数据，同时根据ID查询学生名字。如果名字非空，则会出现在fullname列；若名字为空，则fullname列数据显示为NULL。对外连接而言，on后的条件起到匹配而非筛选数据的作用。此外，对两个表间的内连接而言，下面两种表达是等价的：

--写法1
select courses.course_name,scores.score 
	from courses,scores 
	where scores.course_ID=courses.course_ID;

--写法2
select courses.course_name,scores.score 
	from courses join scores 
	on scores.course_ID=courses.course_ID;

但从语法上讲，后一种用join的内连接才算是真正的内连接。当连接模式复杂起来时（比如下面的嵌套连接），应当用join。

连接可以嵌套进行，且内连接和外连接之间可嵌套。譬如，成立下面的例子：

select 
	`universities`.`is_211` AS `是否为211`,
	`normal_majors`.`major_name` AS `专业名称`,
	`major_groups`.`lowest_score` AS `专业组最低分`
from 
	`normal_majors` 
	join `major_groups` 	
		on `normal_majors`.`major_group_ID` = `major_groups`.`ID`
	left join `universities` 
		on `normal_majors`.`university` = `universities`.`fullname` 
order by `normal_majors`.`possible_ranking`;

事实上，from后面的语句，可以看成是在构造新的表，而这个构造的步骤是顺序执行的。在上面的例子中，先用内连接得到了同时含有normal_majors和major_groups表中所有字段的新表，再用外连接，在保留刚才的新表的基础上再拼接上universities这个表。从实体的角度上看，from语句所得到的表与normal_majors和major_groups共有的实体一一对应。

若想在查询的同时进行统计，并将结果作为单独一列，可以使用case表达式，如下：

select student.student_name, 
    case 
        when scores.score>85 then '优秀'
        when scores.score>60 then '及格'
        else '不及格'
    end as score_level
from scores,student
where scores.student_ID=student.student_ID;

可以看到，整个case表达式作为被查找的一个属性，与被查找的其它属性相并列。

2.3 DCL：GRANT(赋权), REVOKE(收回),FLUSH(刷新)

2.3.1 用户的查询、创建和删除

MySQL服务器相关的数据都存储在名为mysql的数据库中，其中有表user，包含了当前服务器上所有用户的信息。因此，可以用以下查询语句查询当前服务器所有用户的主机IP、用户名和密码密文：

USE mysql;
select Host,`user`,authentication_string from `user`;

创建用户：

create user zhangxn identified by "Zhang*";

也可以指定创建本机用户：

create user zhangxn@localhost identified by "...";

也可以指定ip：

create user zhangxn@'192.168.1.100' identified by "..."; 

也可以不限制是否远程登录：

create user zhangxn@% identified by "..."

修改用户名：

rename oldname to newname

修改密码：

1. 修改当前登陆用户密码：

   set password = '123'
   

2. 修改其它用户的密码：

   ALTER USER username IDENTIFIED BY '123'
   

删除用户：

DROP USER username;

查询当前登陆用户：

SELECT USER();

2.3.2 权限管理

所谓权限，实际就是上述SQL语句关键词如create、drop、select等对应的权限。所谓权限管理，就是控制用户能使用的语句包含哪些。

查询username的权限：

show grants for username;

查询当前用户权限：

show grants;

授权：

GRANT 权限
ON 授权范围
to 用户名

常见权限类型包括：

1. 定义权限：create alter drop
2. 操作权限：insert delete update select
3. 全部权限：all
4. 无权限：usage

譬如，我想将整个被称为academic的数据库的select和update权限赋给用户oscar，可以用如下语句：

grant select,update on academic.* to oscar;

注意：赋权的单位是表，所以想把整个数据库都赋权给用户时，需要用通配符*表示所有的表。

收回权限：

revoke 权限
on 范围
to 用户名

刷新权限设置：

flush previleges

2.4 SQL结构化程序设计基础

​ DDL、DML、DCL只是独立的语句和命令，本身不具备结构化特征。下面我们介绍如何实现结构化程序设计。

2.4.1 关于变量

​ MySQL中，除仅能在定义存储过程时使用的局部变量外，用户变量没有作用域限制。定义普通的用户变量可使用如下语句：

set @var:=0;

其中:=也可以用=，个人认为:=更好，可以与判断符号区分开。在定义存储过程时也可使用局部变量。由于存储过程是预编译的，因此局部变量是强类型的，且必须有默认值：

declare var int default 0;

2.4.2 控制流语句

​ 控制流语句应当仅在存储过程等预编译对象中使用（有时case会出现在select中，那仅是简单的case表达式，不是控制流语句）。所有的控制流语句都要“有始有终”——以“关键词”开始，以“end 关键词”结束。

分支控制：if, case

if语句举例如下：

if i=1 then set res:=1;
else set res:=0;
end if;

注意控制流语句和表达式的区别：每一个分支都要以分号结尾。而在实际的存储过程编写中，为了避免没有写完就被执行，通常需要先用delimiter声明一个新的分隔符来替代分号。我们将在存储过程的编写中看到这一点。

case语句举例如下：

case
	when n=1 then set res=1;
	when n=2 then set res=0;
	else set res=9;
end case;

特别地，上述代码还可以写成如下形式：

case n
	when 1 then set res=1;
	when 2 then set res=0;
	else set res=9;
end case;

循环控制包含while（对应C中while）、repeat until（对应C中do while）和loop（死循环，需搭配leave，相当于C语言中while(1)搭配break）。示例可见MySQL循环语句举例

2.4.3 存储过程

存储过程的定义：

存储过程是与预编译过程，所以在语法上，它包含了一个完整的SQL片段。举例如下：

CREATE PROCEDURE `count_procedure`(in n int, out res int)
BEGIN
	declare i int default 1;
    case
		when n+i=1 then set res=1;
		when n+i=2 then set res=2;
		else set res=9;
    end case;
END

可见，BEGIN和END之间的片段是完整的，因此用分号做分隔。

存储过程的调用通过call实现：

set @res = 0;
call course_select.count_procedure(5, @res);

由此可见，存储过程之所以被称之为“过程”而区别于函数，是因为它本身不可被视为值存入变量中，而仅仅是对一系列操作的封装。函数则恰恰与之相反，它专为计算而生，而不应以数据操纵为目的。实际上，由于特性声明的存在，我们无法通过函数修改数据。

2.4.4 函数

本质上，聚合中所用到的count()等都属于函数。我们首先探讨如何定义函数。其基本结构如下：

DELIMITER $$
CREATE FUNCTION funcname(param TYPE) RETURNS TYPE 特性声明
BEGIN
    -- 一组SQL语句，与PROCEDURE完全相似，但以返回值的求解为目的
    RETURN return_value;
END $$
DELIMITER ;

这里面的特性声明包括三种：

1. DETERMINISTIC：确定性的，即无论何时何地调用此函数，对于相同的输入，总会给出相同的输出。若声明此特性，则会将每次调用的结果存入缓存，从而加快下次调用的速度。
2. NO SQL：不依赖任何数据操纵，同时不要求具有确定性。
3. READS SQL DATA：仅允许读取数据，同时因此不具有不确定性。

个人理解：这里真正起关键作用的，仅仅是是否声明DETERMINISTIC，它决定数据是否被存入缓存。而后两者更多地是为了程序阅读清晰，和提醒程序员不要用函数进行数据操纵。

举例如下：

DELIMITER $$
CREATE FUNCTION `detect_func`(n INT) RETURNS int
    DETERMINISTIC
BEGIN
	DECLARE standard,return_val INT DEFAULT 0;
    SET standard:=2;
    IF n>=standard THEN SET return_val:=1;
    ELSE SET return_val:=0;
    END IF;
    RETURN return_val;
END $$

可以调用该函数如下：

select course_select.detect_func(1);

2.4.5 触发器（Trigger）

我们可以用外键约束父表和从表，以进行简单的关联操作。但对一些复杂的操作，则需要在某一张表（通常也是所谓的“主表”）上自定义触发器。譬如，对成绩表score，我希望在插入数据前，检查student表中是否存在该学生，若不存在则向student表插入学生ID，则可以定义触发器如下：

DELIMITER $$
CREATE TRIGGER `BEFORE_INSERT_SECURITY` 
	BEFORE INSERT 
	ON `scores` 
	FOR EACH ROW 
BEGIN
	DECLARE isexist int default 0;
	select count(*) from student 
		where student.student_ID=NEW.student_ID 
        INTO isexist;
    IF isexist=0 
		THEN INSERT INTO student(student_ID) values(NEW.student_ID);
	END IF;
END $$
DELIMITER ;

有以下几点需要注意：

1. 触发器是表所附属的，所以理所当然地要用on指明所属的表。
2. 触发器在操作瞬间触发，因此存在操作前数据和操作后数据之分，这用OLD和NEW来区分。譬如，NEW.student_ID将引用操作前scores.student_ID的值，而OLD.student_ID则引用操作后的新值。
3. 触发器中不可进行查询操作。

三. 关系型数据库理论

3.1 范式（NF）

数据库设计准则，一般考虑三个范式：

1. 第一范式（1NF）：关系模式R（即表）的所有属性都是不可分割的

2. 第二范式（2NF）：任意关系模式必须含有主键；关系模式含有多个属性组成的主键时，所有非主属性都依赖于全部主键。

3. 第三范式（3NF）：不应存在传递依赖，即多个非主键属性依赖于某一个非主键属性，该非主键属性又依赖于主键属性。也就是说，所有属性与主键的依赖关系应当是唯一的。