1. 数据库管理系统DBMS 是数据库系统的核心
2. 数据库属于C/S架构、Web属于B/S架构
3. 数据库登录(默认用户名root、密码为空)
mysql.exe -h localhost -u root -p
4. 数据模型
4.1 概念模型
典型的是实体联系模型E-R模型（Entity-Relationship）
实体、属性、联系
4.2 逻辑模型
层次模型、网状模型、关系模型（二维表结构）、面向对象模型
4.3 物理模型
5. 数据库的三级模式结构是指：数据库是由模式、外模式、内模式三级构成
6. 数据库设计步骤
需求分析、概念结构设计（E-R图）、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护
7. SQL：Structured Query Language 所有关系型数据库都支持SQL
语言组成：DDL、DML、DCL
************************************************************************************************************************************************************************************************************************
8. MySQL函数
8.1 聚合函数==>COUNT() SUM() AVG() MAX() MIN() 常用于分组聚合查询
SELECT COUNT(*) FROM tb_student;
SELECT AVG(score) FROM tb_score;
8.2 数学函数==>ABS() FLOOR() RAND() TRUNCATE(x, y) SQRT()
SELECT ABS(-5), ABS(8), ABS(-8.1);
SELECT SQRT(2); //计算平方根
SELECT FLOOR(-5.1), FLOOR(5.1);
SELECT RAND();
SELECT TRUNCATE(589.123456, 4);
8.3 字符串函数==>UPPER() LOWER() LEFT() RIGHT() SUBSTRING()
SELECT SUBSTRING('dsfkljasdklfj', 2, 5);
8.4 日期和时间函数==> CURDATE() CURTIME() NOW() YEAR(NOW()) YEAR(CURDATE())
8.5 其他函数==>IF(expr, v1, v2) IFNULL(v1, v2)
SELECT studentNo, courseNo, score, IF(score>85, '优秀'，'一般');
************************************************************************************************************************************************************************************************************************
9. 数据库命令
9.1
SHOW DATABASES; 查看所有数据库
\s 查看数据库基本信息
9.2 创建数据库
CREATE DATABASE IF NOT EXISTS db_school
CHARACTER SET UTF8 //修改数据库字符集
COLLATE utf8_general_ci; //指定字符集的校对规则
9.3 选择default数据库
USE db_school;
9.4 修改数据库字符集
ALTER DATABASE db_school
CHARACTER SET gb2312
COLLATE gb2312_chinese_ci;
9.5 删除数据库
DROP DATABASE IF EXISTS db_school; //数据库删除之后，之前设置该数据库上的管理员权限并不会删除，应该手动删除
10. 数据库中的表 及 命令
10.1 查看表（SHOW）
SHOW TABLES; //查看当前数据库表
SHOW TABLES FROM db_company; //查看非当前数据库表
SHOW COLUMNS FROM tb_stu; //查看表结构，可视效果比较好
SHOW CREATE TABLE tb_stu; //查看更详细的表结构，可以看到存储引擎和字符编码
SHOW ENGINES; //显示所有存储引擎信息
10.2 创建表（CREATE）
CREATE TABLE tb_stu(
stuNo CHAR(10),
stuName VARCHAR(20) NOT NULL,
sex CHAR(2),
bir DATE,
native VARCHAR(20),
nation VARCHAR(10) DEFAULt '汉',
classNo CHAR(6) REFERENCES tb_class(classNo),
CONSTRAINT PK_stu PRIMARY KEY(stuNo)
)ENGINE=InnoDB;
10.3 修改表 （ALTER）
10.3.1 向表中添加字段
ALTER TABLE tb_stu ADD id1 INT NOT NULL; //默认添加到最后一行
ALTER TABLE tb_stu ADD id1 INT NOT NULL FIRST, ADD id2 TINYINT AUTO_INCREMENT AFTER stu_name; //添加多个字段
10.3.2 修改表中的字段
CHANGE: 名称、类型必须一起改， 默认值填写了才改，不写就没有默认值， 可改顺序。
ALTER TABLE tb_stu2 CHANGE id idtest INT NOT NULL DEFAULT '18' AFTER stu_name;
ALTER：只能对默认值的修改、设置、删除
ALTER TABLE tb_stu2 ALTER id SET DEFAULT '10'; //修改、设置默认值
ALTER TABLE tb_stu2 ALTER id DROP DEFAULT; //删除默认值
MODIFY：除了不能改列名，其他功能和CHANGE一样
ALTER TABLE tb_stu2 MODIFY id SMALLINT NOT NULL FIRST;
10.3.3 删除表中字段
ALTER TABLE tb_stu2 DROP id;
10.4 重命名表
ALTER TABLE tb_stu2 RENAME TO tb_stu3; //修改一个表名
RENAME TABLE tb_stu1 TO tb_stu2; //另一种方式
RENAME TABLE tb_stu1 TO tb_stu2, tb_score1 TO tb_score2; //另一种方式可重命名多个表
10.5 删除表（DROP）
DROP TABLE IF EXIST tb_stu2;
11. 数据完整性约束
11.1 实体完整性约束
关系的主属性不能取空值，即主键和候选键在关系属性中所对应的属性都不能取空值
11.1.1 主键约束（MySql自动对该表创建索引）
列级完整性约束
CREATE TABLE tb_stu(
stuNo INT(10) PRIMARY KEY AUTO_INCREMENT,
。。。。。。
)ENGINE = InnoDB;
表级完整性约束
CREATE TABLE tb_stu2(
stuNo INT(10) AUTO_INCREMENT,
。。。。。。
PRIMARY KEY(stuNo)
)ENGINE = InnoDB;
11.1.2 完整性约束的命名
对完整性约束命名后，才能对其约束进行增删改操作。
目前MySQL只能给表级完整性约束命名，因此尽量定义表级完整性约束
CREATE TABLE tb_stu2(
stuNo INT(10) AUTO_INCREMENT,
。。。。。。
CONSTRAINT PK_stu PRIMARY KEY(stuNo)
)ENGINE = InnoDB;
11.1.3 候选键约束
CREATE TABLE tb_class(
classNo CHAR(6),
className VARCHAR(20) NOT NULL,
。。。。。。
CONSTRAINT PK_class PRIMARY KEY(classNo),
CONSTRAINT UQ_class UNIQUE(className)
)Engine = InnoDB;
11.2 参照完整性约束
CREATE TABLE tb_stu(
stuNo CHAR(10),
。。。。。。
classNum CHAR(6),
CONSTRAINT PK_stu PRIMARY KEY(stuNo),
CONSTRAINT FK_stu FOREIGN KEY(classNum) REFERENCES tb_class(classNo)
)ENGINE = InnoDB;
11.3 用户定义完整性约束
11.3.1 设置非空约束
略
11.3.2 CHECK约束
列级：
略
表级：
CREATE TABLE tb_course(
courseNo CHAR(6),
courseName CHAR(20),
credit INT,
courseHour INT,
term CHAR(2),
priorCourse CHAR(6),
CONSTRAINT PK_course PRIMARY KEY(courseNo),
CONSTRAINT FK_course FOREIGN KEY(priorCourse) REFERENCES tb_course(courseNo),
CONSTRAINT CK_course CHECK(credit = courseHour/16)
)Engine = InnoDB;
11.4 更新完整性约束
11.4.1 删除约束
11.4.1.1 删除外键约束
ALTER TABLE tb_score DROP FOREIGN KEY FK_score; //注意，删除外键约束时，外键名没有()
11.4.1.1 删除主键约束
因为一个表只有一个主键或者一个复合主键，因此删除主键不用填写主键命名
ALTER TABLE tb_stu DROP PRIMARY KEY;
11.4.1.1 删除候选键约束
删除候选键约束，实际上删除的是唯一性索引
ALTER TABLE tb_class DROP INDEX className;
11.4.2 添加约束
11.4.2.1 添加主键约束
添加有约束命名的主键约束：
ALTER TABLE tb_stu ADD CONSTRAINT PK_stu PRIMARY KEY(stuNo);
添加没有约束命名的主键约束：
ALTER TABLE tb_stu ADD PRIMARY KEY(stuNo);
11.4.2.1 添加外键约束
添加有约束命名的外键约束：
ALTER TABLE tb_score ADD CONSTRAINT FK_score1 FOREIGN KEY(stuNo) REFERENCES tb_stu(stuNo);
添加没有约束命名的外键约束：
ALTER TABLE tb_score ADD FOREIGN KEY(stuNo) REFERENCES tb_stu(stuNo);
11.4.2.1 添加候选键约束
添加有约束命名的候选键约束：
ALTER TABLE tb_class ADD CONSTRAINT UQ_class UNIQUE(className);
添加没有约束命名的候选键约束：
ALTER TABLE tb_class ADD UNIQUE(className);
************************************************************************************************************************************************************************************************************************
12. 单表查询
12.1 选择字段
SELECT * FROM tb_stu; //所有字段
SELECT DISTINCT department FROM tb_class; //去掉重复数据
SELECT classNo, department, className FROM tb_class; //某些字段
SELECT stuName, YEAR(NOW()) - YEAR(bir) FROM tb_stu; //选择计算后的值
SELECT stuName AS '姓 名', YEAR(NOW()) - YEAR(bir) 年龄 FROM tb_stu; //字段取别名， AS可省略
12.2 选择指定记录 WHERE子句
12.2.1 比较大小
SELECT courseName, credit FROM tb_course WHERE courseHour >= 48;
SELECT courseName, credit FROM tb_course WHERE NOT courseHour < 48;
SELECT courseName, credit FROM tb_course WHERE courseHour <> 48;
SELECT courseName, credit FROM tb_course WHERE NOT courseHour = 48;
12.2.2 带范围查询 BETWEEN...AND
SELECT stuName, sex, bir FROM tb_stu WHERE bir BETWEEN '1997-01-01' AND '1997-12-31';
SELECT stuName, sex, bir FROM tb_stu WHERE bir NOT BETWEEN '1997-01-01' AND '1997-12-31';
12.2.3 带IN的集合查询
SELECT * FROM tb_stu WHERE native IN('北京', '天津', '上海');
SELECT * FROM tb_stu WHERE native NOT IN('北京', '天津', '上海');
12.2.4 带LIKE的字符串匹配查询（通配符% 和 _）
%：代表任意长度包括长度为0
_： 代表代表任意单个字符
SELECT * FROM tb_stu WHERE stuNo LIKE '2013110201'; //精准匹配学号
SELECT * FROM tb_stu WHERE stuName LIKE '王%'; //查询姓王的
SELECT * FROM tb_stu WHERE stuName NOT LIKE '王%'; //查询不姓王的
SELECT * FROM tb_stu WHERE stuName LIKE '王__'; //查询姓王,名字是三个字的
SELECT * FROM tb_course WHERE courseName LIKE '%#_%' ESCAPE '#'; //_作为匹配内容，必须用ESCAPE指定换码符，但换码符不能是\
12.2.5 IS NULL和IS NOT NULL
SELECT * FROM tb_course WHERE priorCourse IS NULL; //不能改为=NULL
SELECT * FROM tb_course WHERE priorCourse IS NOT NULL; //不能改为!=NULL
12.2.6 带AND或OR的多条件查询（AND优先级高于OR，可用括号改变优先级）
SELECT * FROM tb_course WHERE credit >= 3 AND courseHour > 32;
SELECT * FROM tb_stu WHERE native = '北京' OR native = '上海';
SELECT * FROM tb_stu WHERE native IN ('北京', '上海'); //IN的速度优于OR
SELECT * FROM tb_stu WHERE (native = '北京' OR native = '湖南') AND nation != '汉' AND sex = '男';
12.3 对查询结果排序
NULL将作为最小值处理
SELECT * FROM tb_stu ORDER BY stuName ASC; //名字升序排列，默认ASC，这里ASC可不写
SELECT * FROM tb_score WHERE score > 85 ORDER BY stuNo ASC, score DESC; //升序ASC，降序DESC
12.4 限制查询结果的数量
SELECT * FROM tb_score ORDRE BY score DESC LIMIT 4, 5; //查询成绩排名5到10位的成绩信息， 注意偏移量是0 Base
13. 分组聚合查询
13.1 使用聚合函数查询
SELECT COUNT(stuNo) FROM tb_stu; //查询学生总人数
SELECT COUNT(DISTINCT stuNo) FROM tb_score; //查询有课程成绩的学生总人数, DISTINCT 写在SELECT后面则会重复统计，应该写在COUNT函数内
SELECT AVG(score) FROM tb_score WHERE courseNo = '21001'; //查询课程号为21001的课程平均分
13.2 分组聚合查询
SELECT courseNo, COUNT(stuNo) FROM tb_score GROUP BY courseNo; //查询各个课程号及相应选课人数， 因为courseNo是GROUP BY的对象，所以SELECT后面要有courseNo，否则显示不明确
SELECT stuNo, COUNT(courseNo), AVG(score), MAX(score) FROM tb_score GROUP BY stuNo; //查询每个学生的选课门数，平均分和最高分
SELECT stuNo, COUNT(courseNo), AVG(score), MAX(score) FROM tb_score GROUP BY stuNo HAVING AVG(score) > 80; //查询平均分在80以上的每个学生的选课门数，平均分和最高分
SELECT stuNo, COUNT(courseNo) FROM tb_score WHERE score > 88 GROUP BY stuNo HAVING COUNT(score) >=2; //查询至少有2门课程成绩大于88分的学生学号和大于88分的课程数
WHERE子句过滤基本表或视图，基于特定值；HAVING子句则作用于分组后的每个组，用于过滤分组，基于分组聚合值，不是特定值
SELECT AVG(score) FROM tb_score HAVING AVG(score) > 80; //没有GOURP BY则将分组汇总， AVG(score) > 80写在HAVING里，写在WHERE中运行报错！因为是基于分组聚合值，不是特定值，所以写在HAVING里
14. 连接查询
14.1 交叉连接 （CROSS JOIN）
将表1的每一行与表2的每一行分别连接，产生很多无意义数据，应避免使用
SELECT COUNT(*) FROM tb_stu; SELECT COUNT(*) FROM tb_course; SELECT COUNT(*) FROM tb_stu CROSS JOIN tb_course;//交叉链接查询总数是表1总数X表2总数，中有很多无意义值；CROSS JOIN可用，替换
SELECT * FROM tb_stu, tb_course; //基本不用，使用时用WHERE过滤减少返回结果集的数量
14.2 内链接 （INNER JOIN）
14.2.1 等值于非等值连接
SELECT tb_stu.*, className FROM tb_stu, tb_class WHERE tb_stu.classNo = tb_class.classNo AND department = '会计学院';
SELECT tb_stu.*, className FROM tb_stu INNER JOIN tb_class ON tb_stu.classNo = tb_class.classNo WHERE department = '会计学院'; //查询会计学院所有同学的信息和班级名
PS：最好使用INNER JOIN...ON...便于显示表达内连接的表和表间的连接条件
内连接是系统默认连接，所以可以省略INNER
SELECT tb_stu.stuNo, stuName, score FROM tb_stu, tb_score, tb_course WHERE tb_stu.stuNo = tb_score.stuNo AND tb_score.courseNo = tb_course.courseNo AND courseName = '程序设计';
SELECT tb_stu.stuNo, stuName, score FROM tb_stu INNER JOIN tb_score INNER JOIN tb_course ON tb_stu.stuNo = tb_score.stuNo AND tb_score.courseNo = tb_course.courseNo WHERE courseName = '程序设计';
SELECT a.stuNo, stuName, score FROM tb_stu AS a INNER JOIN tb_score AS b INNER JOIN tb_course AS c ON a.stuNo = b.stuNo AND b.courseNo = c.courseNo WHERE courseName = '程序设计';
14.2.2 自连接
自己连接自己
SELECT * FROM tb_course AS c1 INNER JOIN tb_course AS c2 ON c1.credit = c2.credit WHERE c2.courseName = '数据库';
|| 推演
V
SELECT c1.* FROM tb_course AS c1 INNER JOIN tb_course AS c2 ON c1.credit = c2.credit WHERE c2.courseName = '数据库' AND c1.courseName != '数据库';
14.2.3 自然连接
SELECT a.stuNo, stuName, courseNo, score FROM tb_stu a INNER JOIN tb_score b ON a.stuNo = b.stuNo;
^
|| 等价
v
SELECT a.stuNo, stuName, courseNo, score FROM tb_stu a NATURAL JOIN tb_score b;
14.3 外连接（OUTER JOIN）
14.3.1 左外连接 （左表为基表）
SELECT a.*, courseNo, score FROM tb_stu a LEFT OUTER JOIN tb_score b ON a.stuNo = b.stuNo;
tb_stu是左/基表， tb_score是右/参考表
查询结果除了显示自然连接能够匹配的数据，也会显示基表没有匹配的数据，例如有一个学生没有选课和成绩，会将课程和成绩显示为NULL
14.3.2 右外连接（右表为基表）
参考左外连接
SELECT a.*, courseNo, score FROM tb_stu a RIGHT OUTER JOIN tb_score b ON a.stuNo = b.stuNo;
例如有一个学生没有选课和成绩，但是因为右表为基表, 则不会显示没有选课和成绩的那个学生的信息
15. 子查询
15.1 带IN关键字
略
15.2 带比较运算符
略
15.3 带EXISTS关键字（难理解）
EXISTS含义：EXISTS构造查询子句时，如果子查询结果集不为空，EXISTS返回TRUE，此时外查询语句进行查询；如果子查询结果集为空，则EXISTS返回的结果为FALSE，此时外查询语句将不进行查询。
SELECT stuName FROM tb_stu a WHERE EXISTS (SELECT * FROM tb_score b WHERE a.stuNo = b.stuNo AND courseNo='31002'); //查询选修了课程号为‘31002’的学生姓名
SELECT stuName FROM tb_stu a WHERE NOT EXISTS (SELECT * FROM tb_score b WHERE a.stuNo = b.stuNo AND courseNo='31002'); //查询没有选修课程号为‘31002’的学生姓名
SELECT stuName FROM tb_stu x WHERE NOT EXISTS(SELECT * FROM tb_course c WHERE NOT EXISTS(SELECT * FROM tb_score WHERE stuNo = x.stuNo AND courseNo = c.courseNo)); //查询选择了所有课程的学生姓名 <==> 查询学生姓名，没有一门课程是他没有选的
16. 联合查询（UNION）
************************************************************************************************************************************************************************************************************************
17. 数据更新
17.1 插入数据（INSERT|REPLACE INTO）
17.1.1 插入完整的数据记录
完整插入命令，前后手动匹配
INSERT INTO tb_stu (stuNo, sex, stuName, bir, native, nation, classNum) VALUES('2014210104', '男', '王玲啊', '1998-02-22', '重庆', '汉', 'CS1401');
简写插入命令，严格按照表结构顺序插入，不用填写column_list()
INSERT INTO tb_stu VALUES('2014210103', '王玲', '女', '1998-02-21', '安徽', '汉', 'CS1401');
17.1.2 为表的指定字段插入数据
没有填写值的字段，必须允许值为NULL或者有默认值
INSERT INTO tb_stu (stuNo, sex, stuName, bir, classNum) VALUES('2014210105', '女', '王玲吧', '1998-02-23', 'CS1401');
17.1.3 同时插入多条数据记录
INSERT INTO tb_stu VALUES('2014210108', '王玲', '女', '1998-02-21', '安徽', '汉', 'CS1401'),
('2014210106', '王玲1', '女', '1998-02-24', '安徽', '汉', 'CS1401'),
('2014210107', '王玲2', '女', '1998-02-21', '安徽', '汉', 'CS1401');
17.1.4 插入查询结果
INSERT INTO tb_stu2 SELECT * FROM tb_stu WHERE sex = '男';
17.1.5 使用REPLACE语句插入表数据
如果插入一条主键或者候选键相同的字段，用INSERT会插入失败，因为主键和候选键必须唯一。
这种情况下用REPLACE替换INSERT，那么将原来的记录删除，在插入新的记录
REPLACE INTO tb_stu2 VALUES('2014210103', '王琳琳', '女', '1998-03-21', '成都', '回', 'CS1401');
17.2 修改数据记录（UPDATE SET）
17.2.1 修改特定数据记录
必须用WHERE指定哪条特定记录，否则将全部更新！见17.2.2
UPDATE tb_stu2 SET stuName='王林', sex='女' WHERE stuNo = '2014210103';
17.2.2 修改所有数据记录
UPDATE tb_stu2 SET stuName='王林', sex='女';
17.2.3 带子查询的修改
略
17.3 删除数据记录（DELETE FROM）
17.3.1 删除特定数据记录
必须用WHERE指定哪条特定记录，否则将全部更新！见17.3.3
DELETE FROM tb_stu2 WHERE stuNo='2014210108';
17.3.2 带子查询的删除
略
17.3.3 删除所有数据记录（删除数据后保留表定义：）
逐条删除表记录：
DELETE FROM tb_stu2;
效率更高的TRUNCATE：先DROP TABLE tb_stu2; 再CREATE TABLE tb_stu2.........;
TRUNCATE tb_stu2;
************************************************************************************************************************************************************************************************************************
18. 索引
18.1 概述
18.1.1 查询优化，全文搜索和索引式搜索
18.1.2 索引按性质分类
普通索引：索引列值可以为NULL或重复值
唯一性索引UNIQUE： 索引值不能重复，但可以为NULL
主键索引：唯一性索引的一种，列值除了不能重复，也不能为NULL
聚簇索引：索引顺序是数据存储的物理存储顺序，一个表只能有一个聚簇索引，solidDB和InnoDB可以支持聚簇索引
全文索引：只能建立再VCHAR或TEXT的列上，只能在MyISAM数据库引擎创建全文索引
18.1.3 按使用分类
单列索引：一个索引只在一个表的一个列上
组合索引：一个索引建立在一个表的多个列上
18.2 查看数据表上所建立的索引
下列查看索引等价：
SHOW INDEX FROM tb_stu \G;
SHOW INDEX FROM db_school.tb_stu \G;
SHOW INDEX FROM tb_stu FROM db_school \G;
*************************** 1. row ***************************
Table: tb_score #索引所在表
Non_unique: 0 #该索引是否不是唯一索引
Key_name: PRIMARY #索引名称
Seq_in_index: 1
Column_name: stuNo #建立索引的列名
Collation: A #说明是何种排序的索引A:ASC升序；D:DESC降序
Cardinality: 10
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
。。。。。。
18.3 创建索引
除了普通索引用 INDEX(col_name) 创建外，在MySQL中主键、唯一性是创建表时系统默认创建
注意用CREATE INDEX ... ON 和 用ALTER TABLE ... ADD的语法区别
18.3.1 使用CREATE TABLE创建索引
不创建任何索引的TABLE：
CREATE TABLE tb_stu2(
stuNo CHAR(10) NOT NULL,
stuName CHAR(20),
sex CHAR(2),
bir DATE,
native CHAR(20),
nation CHAR(10),
classNum CHAR(6)
)Engine = InnoDB;
mysql> SHOW INDEX FROM tb_stu2 \G
Empty set (0.00 sec) #查询不到任何索引信息
创建普通索引：
CREATE TABLE tb_stu1(
stuNo CHAR(10) NOT NULL,
stuName CHAR(20),
sex CHAR(2),
bir DATE,
native CHAR(20),
nation CHAR(10),
classNum CHAR(6),
INDEX(stuName)
)Engine = InnoDB;
创建唯一性索引：
CREATE TABLE tb_stu2(
stuNo CHAR(10) NOT NULL UNIQUE,
stuName CHAR(20),
sex CHAR(2),
bir DATE,
native CHAR(20),
nation CHAR(10),
classNum CHAR(6)
)Engine = InnoDB;
创建主键索引：
创建PRIMARY KEY时默认创建主键索引
CREATE TABLE tb_score1(
stuNo CHAR(10),
courseNo CHAR(5),
CONSTRAINT PK_socre PRIMARY KEY(stuNo, courseNo),
CONSTRAINT FK_socr1 FOREIGN KEY(stuNo) REFERENCES tb_stu(stuNo),
CONSTRAINT FK_socr2 FOREIGN KEY(courseNo) REFERENCES tb_course(courseNo)
)Engine = InnoDB;
18.3.2 使用CREATE INDEX创建索引（在已有表上）
创建普通索引：
CREATE INDEX INDEX_stu ON tb_stu2(stuName);
创建基于字段值前缀字符的普通索引：
CREATE INDEX INDEX_course ON tb_course(courseName(3) DESC);
创建组合索引：
CREATE INDEX INDEX_stu1 ON tb_stu2(stuName, stuNo DESC);
18.3.3 使用ALTER TABLE创建索引（在已有表上）
建立普通索引：
ALTER TABLE tb_stu2 ADD INDEX index_stuName(stuName);
建立主键和唯一性索引参考第三章，系统自动创建
18.4 删除索引
18.4.1 使用DROP INDEX ...ON语句删除索引
DROP INDEX INDEX_stu ON tb_stu2;
18.4.2 使用ALTER TABLE...DROP INDEX语句删除索引
ALTER TABLE tb_stu2 DROP INDEX index_stuName;
18.5 进一步说明
18.5.1 过多使用索引的问题
会降低更新表中数据（INSERT、UPDATE、DELETE）的速度；
增加磁盘存储空间
18.5.2 使用索引建议
在频繁（INSERT、UPDATE、DELETE）的表上，尽量少创建索引
数据量小的表最好不建索引
使用组合索引时，严格遵循最左前缀法则
在查询表达式中经常使用、有较多不同值的字段上建立索引。例如sex只有男女选项，不建索引
对VARCHAR，CHAR这样的字符变量建立索引，最好使用前几个字符简历索引而不是全部字段建索引以提高效率
************************************************************************************************************************************************************************************************************************
19 视图VIEW
19.1 概述
视图与数据库的区别：
VIEW是一张虚拟表，是建立在对数据库中真实存在的表的查询基础上的
VIEW是由SQL语句定义，行、列来源于定义视图查询所引用的真是表（基表、源表）或基表的计算值，在引用视图时动态生成
VIEW不是以数据集的形式存在于数据库中，它所对应的数据是存储在视图所引用的真是表中
VIEW本身不存储数据
视图的优点：
用于集中各个表中分散的数据，方便查询
简化查询语句
重用SQL语句。若基表中的数据被更新等操作，VIEW使用的是更新后的数据，因此应用程序可重用之前的SQL语句
通过权限设置，屏蔽基表权限，开启VIEW权限达到保护数据安全的目的
多用户共享所需数据
可以通过视图重新定义检索出的数据格式，并组织到其他应用中输出
19.2 创建视图
创建视图v_stu，并且之后在v_stu上的每次修改都必须满足sex=‘男’
CREATE OR REPLACE VIEW v_stu AS SELECT * FROM tb_stu WHERE sex = '男' WITH CHECK OPTION;
创建v_score_avg，并按照stuNo排序
CREATE OR REPLACE VIEW v_score_avg AS SELECT stuNo, AVG(score) FROM tb_score GROUP BY stuNo;
19.3 删除视图
类似于DROP DATABASE... 和 DROP TABLE...
DROP VIEW IF EXISTS v_stu;
19.4 修改视图定义
类似于ALTER TABLE...
ALTER VIEW v_stu AS SELECT stuNo, stuName, classNum FROM tb_stu WHERE sex = '男' AND nation = '汉' WITH CHECK OPTION;
CREATE OR REPLACE VIEW v_stu AS SELECT stuNo, stuName, classNum FROM tb_stu WHERE sex = '男' AND nation = '汉' WITH CHECK OPTION;
19.5 查看视图定义
类似于SHOW CREATE TABLE ... 和 SHOW COLUMNS FROM ...
SHOW CREATE VIEW v_stu\G
SHOW COLUMNS FROM v_stu\G
19.6 更新试图数据
如同更新表中数据，略
19.7 查询视图数据
SELECT * FROM v_stu;
类似于查询TABLE，略
19.8 进一步说明
需要权限
视图数目没有限制
视图可以嵌套
如果查询视图使用ORDER BY子句，且创建视图的SELECT中也有ORDER BY，那么视图查询的ORDER BY会被SELECT的ORDER BY 覆盖
视图不能索引、也不能有触发器和默认值
VIEW可以和TABLE联表查询
如果视图由多个条件或者联表查询组成，那么可能占用很多计算机资源。需要测试性能
************************************************************************************************************************************************************************************************************************
20. 触发器
20.1 触发器
具体而言，触发器就是MySQL响应INSERT、UPDATE、DELETE语句而自动执行的MySQL语句。其他mysql不支持
20.2 创建触发器
CREATE TRIGGER tb_stu_insert——trigger AFTER INSERT ON tb_stu FOR EACH ROW SET @str = 'add a stu!';
查看TRIGGER
SHOW TRIGGERS;
INSERT INTO tb_stu VALUES('2018110105', '张晓勇', '男', '1997-12-11', '山西', '汉', 'AC1301');
SELECT @str;
+-------------+
| @str |
+-------------+
| add a stu! |
+-------------+
20.3 删除触发器
DROP TRIGGER IF EXISTS tb_stu_insert;
20.4 使用触发器（NEW和OLD虚拟表的使用）
20.4.1 INSERT 触发器
CREATE TRIGGER tb_stu_insert_trigger AFTER INSERT ON tb_stu FOR EACH ROW SET @str = NEW.stuNo;
20.4.1 UPDATE 触发器
CREATE TRIGGER tb_stu_delete_trigger AFTER DELETE ON tb_stu FOR EACH ROW SET @str = OLD.stuNo;
20.4.1 DELETE 触发器
CREATE TRIGGER tb_stu_update_trigger BEFORE UPDATE ON tb_stu FOR EACH ROW SET NEW.nation = OLD.native;
20.5 进一步说明
************************************************************************************************************************************************************************************************************************
21. 事件EVENT
EVENT 与 TRIGGER的区别：事件是基于特定时间周期触发来执行任务；触发器是基于某个表所产生的事件触发
21.1 事件
查看事件调度器：
SHOW VARIABLES LIKE 'EVENT_SCHEDULER'; 或者
SHOW VARIABLES LIKE 'EVENT%'; 或者
SELECT @@EVENT_SCHEDULER;
若时间调度器未启用，则开启：
SET GLOBAL EVENT_SCHEDULER = 1; 或者
SET GLOBAL EVENT_SCHEDULER = TRUE;
21.2 创建事件
DELIMITER $$
CREATE EVENT IF NOT EXISTS event_insert
ON SCHEDULE EVERY 1 MONTH
STARTS CURDATE() + INTERVAL 1 MONTH
ENDS '2019-12-31'
DO
BEGIN
IF YEAR(CURDATE()) < 2013 THEN
INSERT INTO tb_stu VALUES(NULL, '张晓勇', '男', '1997-12-11', '山西', '汉', 'AC1301');
END IF;
END$$
查看事件：
SHOW EVENTS;
21.3 修改事件
关闭事件：
ALTER EVENT event_insert DISABLE;
重新开启事件：
ALTER EVENT event_insert ENABLE;
重命名事件名：
ALTER EVENT event_insert RENAME TO e_insert;
21.4 删除事件
DROP EVENT IF EXISTS event_name;
************************************************************************************************************************************************************************************************************************
22. 存储过程与存储函数
22.1 存储过程
22.1.1 创建存储过程
DELIMITER !!
CREATE PROCEDURE IF NOT EXISTS sp_update_sex(IN sno CHAR(10), IN ssex CHAR(2))
BEGIN
UPDATE tb_stu SET sex = ssex WHERE stuNo = sno;
END!!
22.1.2 存储过程体
22.1.2.1 局部变量
DECLARE sno CHAR(10); #不带默认值
DECLARE sno CHAR(10) DEFAULT '2018110105'; #带默认值
22.1.2.2 SET语句
SET sno = '2018110105';
22.1.2.3 SELECT INTO语句
22.1.2.4 流程控制语句
22.1.2.5 游标
22.1.3 调用存储过程
CALL sp_update_sex('2018110105', '男');
22.1.4 删除存储过程
DROP PROCEDURE IF EXISTS sp_update_sex;
22.2 存储函数
22.2.1 创建存储函数
CREATE FUNCTION IF NOT EXISTS fn_search(sno CHAR(10))
RETURNS CHAR(2)
DETERMINISTIC
BEGIN
DECLARE SSEX CHAR(2);
SELECT sex INTO SSEX FROM tb_stu WHERE sno = stuNo;
IF SSEX IS NULL THEN
RETURN (SELECT '没有该学生');
ELSE IF SSEX = '女' THEN
RETURN (SELECT '女');
ELSE
RETURN (SELECT '男');
END IF;
END$$
22.2.1 调用存储函数
SELECT funcName('2018110105');
22.2.1 删除存储函数
DROP FUNCTION IF EXISTS funcName;
************************************************************************************************************************************************************************************************************************
23. 访问控制与安全管理
************************************************************************************************************************************************************************************************************************
24. 备份与恢复
************************************************************************************************************************************************************************************************************************
书中例子备份：
CREATE TABLE tb_class(
classNo CHAR(6),
className VARCHAR(20) NOT NULL,
department VARCHAR(30) NOT NULL,
grade SMALLINT,
classNum TINYINT,
CONSTRAINT PK_class PRIMARY KEY(classNo),
CONSTRAINT UQ_class UNIQUE(className)
)Engine = InnoDB;
INSERT INTO tb_class VALUES('AC1301', '会计13-1班', '会计学院', 2013, 35),
('AC1302', '会计13-2班', '会计学院', 2013, 35),
('CS1401', '计算机14-1班', '计算机学院', 2014, 35),
('IS1301', '信息系统13-1班', '信息学院', 2013, NULL),
('IS1401', '信息系统14-1班', '信息学院', 2014, 30);
CREATE TABLE tb_course(
courseNo CHAR(6),
courseName CHAR(20),
credit INT,
courseHour INT,
term CHAR(2),
priorCourse CHAR(6),
CONSTRAINT PK_course PRIMARY KEY(courseNo),
CONSTRAINT FK_course FOREIGN KEY(priorCourse) REFERENCES tb_course(courseNo),
CONSTRAINT CK_course CHECK(credit = courseHour/16)
)Engine = InnoDB;
INSERT INTO tb_course VALUES('11003', '管理学', 2, 32, '2', NULL),
('11005', '会计学', 3, 48, '3', NULL),
('21001', '计算机基础', 3, 48, '1', NULL),
('21002', 'OFFICE高级应用', 3, 48, '2', '21001'),
('21004', '程序设计', 4, 64, '2', '21001'),
('21005', '数据库', 4, 64, '4', '21004'),
('21006', '操作系统', 4, 64, '5', '21001'),
('31001', '管理信息系统', 3, 48, '3', '21004'),
('31002', '信息系统_分析与设计', 2, 32, '4', '31001'),
('31005', '项目管理', 3, 48, '5', '31001');
CREATE TABLE tb_stu(
stuNo CHAR(10),
stuName CHAR(20) NOT NULL,
sex CHAR(2),
bir DATE,
native CHAR(20),
nation CHAR(10) DEFAULT '汉',
classNum CHAR(6),
CONSTRAINT PK_stu1 PRIMARY KEY(stuNo),
CONSTRAINT FK_stu2 FOREIGN KEY(classNum) REFERENCES tb_class(classNo)
)ENGINE = InnoDB;
INSERT INTO tb_stu VALUES('2013110101', '张晓勇', '男', '1997-12-11', '山西', '汉', 'AC1301'),
('2013110103', '王一敏', '女', '1996-03-25', '河北', '汉', 'AC1301'),
('2013110201', '江山', '女', '1996-09-17', '内蒙古', '锡伯', 'AC1302'),
('2013110202', '李明', '男', '1997-01-14', '广西', '壮', 'AC1302'),
('2013310101', '黄菊', '女', '1995-09-30', '北京', '汉', 'IS1301'),
('2013310103', '吴昊', '男', '1995-11-18', '河北', '汉', 'IS1301'),
('2014210101', '刘涛', '男', '1997-04-30', '湖南', '侗', 'CS1401'),
('2014210102', '郭志坚', '男', '1997-02-21', '上海', '汉', 'CS1401'),
('2014310101', '王林', '男', '1996-10-09', '河南', '汉', 'IS1401'),
('2014310102', '李怡然', '女', '1996-12-31', '辽宁', '汉', 'IS1401');
CREATE TABLE tb_score(
stuNo CHAR(10),
courseNo CHAR(6),
score FLOAT CHECK(score >= 0 AND score <= 100),
CONSTRAINT PK_score PRIMARY KEY(stuNo, courseNo),
CONSTRAINT FK_score1 FOREIGN KEY(stuNo) REFERENCES tb_stu(stuNo),
CONSTRAINT FK_score2 FOREIGN KEY(courseNo) REFERENCES tb_course(courseNo)
)Engine = InnoDB;
INSERT INTO tb_score VALUES('2013110101', '11003', 90),
('2013110101', '21001', 86),
('2013110103', '11003', 89),
('2013110103', '21001', 88),
('2013110201', '11003', 78),
('2013110201', '21001', 92),
('2013110202', '11003', 82),
('2013110202', '21001', 85),
('2013310101', '21004', 83),
('2013310101', '31002', 68),
('2013310103', '21004', 80),
('2013310103', '31002', 76),
('2014210101', '21002', 93),
('2014210101', '21004', 89),
('2014210102', '21002', 95),
('2014210102', '21004', 88),
('2014310101', '21001', 79),
('2014310101', '21004', 80),
('2014310102', '21001', 91),
('2014310102', '21004', 87);
CREATE TABLE S(
SNO CHAR(5),
SNAME VARCHAR(20) NOT NULL,
STATUS SMALLINT,
CITY VARCHAR(20),
CONSTRAINT PK_s PRIMARY KEY(SNO),
CONSTRAINT UQ_s UNIQUE(SNAME),
CONSTRAINT CK_s CHECK(CITY != 'London' OR STATUS = 20)
)Engine = InnoDB;
CREATE TABLE P(
PNO CHAR(5),
PNAME VARCHAR(15),
COLOR VARCHAR(10) CHECK(COLOR IN('Red', 'Yellow', 'Green', 'Blue')),
WEIGHT FLOAT,
CONSTRAINT PK_p PRIMARY KEY(PNO)
)Engine = InnoDB;
CREATE TABLE SP(
SNO CHAR(5),
PNO CHAR(5),
QTY INT,
CONSTRAINT PK_sp PRIMARY KEY(SNO, PNO),
CONSTRAINT FK_sp1 FOREIGN KEY(SNO) REFERENCES S(SNO),
CONSTRAINT FK_sp2 FOREIGN KEY(PNO) REFERENCES P(PNO)
)Engine = InnoDB;
INSERT INTO S VALUES('S1', 'Smith', 20, 'London'),
('S2', 'Jones', 10, 'Paris'),
('S3', 'Blake', 30, 'Paris'),
('S4', 'Clark', 20, 'London'),
('S5', 'Adams', 30, 'Athens'),
('S6', 'Brown', NULL, 'New York');
INSERT INTO P VALUES('P1', 'Nut', 'Red', 12),
('P2', 'Bolt', 'Green', 17),
('P3', 'Screw', 'Blue', 17),
('P4', 'Screw', 'Red', 14),
('P5', 'Cam', 'Blue', 12),
('P6', 'Cog', 'Red', 19);
INSERT INTO SP VALUES('S1', 'P1', 200),
('S1', 'P4', 700),
('S1', 'P5', 400),
('S2', 'P1', 200),
('S2', 'P2', 200),
('S2', 'P3', 500),
('S2', 'P4', 600),
('S2', 'P5', 400),
('S2', 'P6', 800),
('S3', 'P3', 200),
('S3', 'P4', 500),
('S4', 'P2', 300),
('S4', 'P5', 300),
('S5', 'P1', 100),
('S5', 'P6', 200),
('S5', 'P2', 100),
('S5', 'P3', 200),
('S5', 'P5', 400);
阅读(2345) | 评论(0) | 转发(0) |
