Bigdata Development Database_Study_02（多表查询，外键，事务）

CREATE TABLE IF NOT EXISTS  `student` (
     id smallint unsigned ,
     name varchar(20),
     sex varchar(1),
     birth year,
     departmetn varchar(20)
     );

unsigned 表示无符号的数字，让 int 只有正数部分

INSERT INTO student VALUES(902, '张老二', '男', 1986, '中文系'),
     (903, '张三', '女', 1991, '中文系');

以 , 分隔可以一次插入多条数据

ALTER TABLE student ADD COLUMN address varchar(40) AFTER sex;

默认添加在最后一列，使用 AFTER 和 BEFORE 可以指定位置



INSERT INTO teacher VALUES ('王五', DEFAULT);

DEFAULT 表示使用默认值

所有的表都必须拥有一列，作为每条记录的唯一标识，所以这一列必须具有两个特性，NOT NULL 和 UNIQUE，叫做：主键 PRIMARY KEY。



CREATE TABLE teacher (
     id int  PRIMARY KEY AUTO_INCREMENT,
     name varchar(20)
     );

AUTO_INCREMENT 列不需要赋值，会在上一行的基础上自增 1，只能用在主键上，和 PRIMARY KEY 一起使用。

INSERT INTO teacher VALUES(10, '王五');
设置值会进行替换

INSERT INTO teacher VALUES(NULL, '王五');
插入数据传 NULL，会自增 1。

程序内部的计数器，只会加不会减。


CREATE TABLE teacher (
     id int AUTO_INCREMENT,
     PRIMARY KEY(id)
     );

通过这种方式可以设置联合主键，可以指定多列，每一列的值单独可以重复，组合起来不能重复。

CREATE TABLE teacher (
     name varchar(20),
     department varchar(20),
     PRIMARY KEY(name, department)
     );

子查询：
SELECT name FROM department WHERE id=(SELECT department_id FROM student WHERE name='张三');

查询条件是另外的一个查询结果

连接查询：把多张表连接一起进行查询，查询结果可以包含多张表中的内容

    eg：查询 院系 id = 1 的专业的名称和对应的学生名称

    内连接：

    外连接：
        左连接：
        右连接：

SELECT student.name, department.name FROM student inner join department WHERE department.id = 1 AND student.department_id = department.id;

多表查询，列名前要加表名做区分

SELECT s.name, d.name FROM student as s inner join department as d ON d.id = 1 AND s.department_id = d.id;

可以使用别名，简化表名的使用

SELECT s.name, d.name FROM student as s, department as d WHERE d.id = 1 AND s.department_id = d.id;

不写 inner join，使用逗号分隔也可以


数据库中两张表之间的如果有关系：

    一对一：

    一对多：需要在 “多”的表 增加 “一” 的主键
        eg：一个课程对应于多个老师，
            需要在 老师 表中增加一列是 课程表 的主键

    多对多：需要新建一张表维护“多对多”的关系，里边包含两张表的主键
        eg：学生和课程是多对多的关系，
            一个学生可以学习多门课程，
            一门课程可以被多个学生学习，

            创建选课表，维护学生和课程的关系

当前表中的其它表的主键，我们可以设置为外键，外键可以增加约束，设置一些级联操作，修改具有关系的一组表的中的某个的时候，也对其它的表产生修改。

外键和级联操作实际开发并不常用，互联网企业，需要变更非常快，数据库的表设计也需要经常地修改，使用外键和级联操作让修改变得难以操作，并且也会拖慢 SQL 的执行效率。数据的维护从数据库转到业务逻辑层，有程序员负责维护。

索引：主要使用提高查询效率的 Index
    通常我们会在 WHERE 条件对应的列增加一个索引

CREATE INDEX 索引名 ON 表名(列名);
CREATE INDEX student_course_score ON student_course(score);

可以为多列设置一个索引
CREATE INDEX student_course_score_course_id ON student_course(score, course_id);

过多的使用索引，会降低插入和修改的效率，除了修改表还需要修改索引。


事物：类似于 Java 的同步机制，让多个 SQL 语句一次执行。


触发器：执行某个操作的时候，自动调用提前设置好的方法

存储过程：写代码完成一系列的复杂操作

多表查询

CREATE TABLE IF NOT EXISTS `student`(
     id int PRIMARY KEY AUTO_INCREMENT,
     name varchar(20)
     );

CREATE TABLE IF NOT EXISTS `teacher`(
     id int PRIMARY KEY AUTO_INCREMENT,
     name varchar(20),
     course_id int
     );

CREATE TABLE IF NOT EXISTS `course`(
     id int PRIMARY KEY AUTO_INCREMENT,
     name varchar(20)
     );

CREATE TABLE IF NOT EXISTS `student_course`(
     id int PRIMARY KEY AUTO_INCREMENT,
     student_id int,
     course_id int
     );



INSERT INTO student VALUES(NULL, '蝙蝠侠'),
    (NULL, '蝙蝠侠'),
    (NULL, '超人'),
    (NULL, '钢铁侠'),
    (NULL, '雷神'),
    (NULL, '蜘蛛侠');

INSERT INTO course VALUES(NULL, '语文'),
    (NULL, '数学'),
    (NULL, '英语');

INSERT INTO teacher VALUES(NULL, '张三', 1),
    (NULL, '李四', 1),
    (NULL, '王五', 2),
    (NULL, '赵六', 3);


INSERT INTO student_course VALUES(NULL, 1, 1, 90),
    (NULL, 1, 2, 90),
    (NULL, 1, 3, 80),
    (NULL, 2, 2, 70),
    (NULL, 3, 3, 70);


查询 蝙蝠侠 所学的 课程名

SELECT c.name FROM course as c, student_course as sc, student as s WHERE s.name='蝙蝠侠' AND sc.student_id = s.id AND c.id = sc.course_id;

查询 学数学 人的名字

SELECT s.name FROM student as s, course as c, student_course as sc WHERE c.name='数学' AND sc.course_id = c.id AND s.id = sc.student_id;

查询 蝙蝠侠 每门课程的名字和成绩

ELECT c.name, sc.score FROM course as c, student_course as sc, student as s WHERE s.name='蝙蝠侠' AND sc.student_id = s.id AND c.id = sc.course_id;

事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性 ：有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier；把事务回滚到标记点；