目录
1.MySQL数据库概述
1.12000年，推出MyISAM开放源代码；2003年，正式集成InnoDB引擎；2006年，SUN公司收购了MySQL公司；2008年，Oracle公司收购SUN
1.2MySQL的COMMAND窗口；MySQL图形管理工具-Workbench；MySQL图形管理工具-Navicat；MySQL图形管理工具-PhpMyAdmin
2.MySQL的安装(Install MySQL)
2.1卸载MySQL:NO1-->点击控制面板→卸载程序，卸载掉所有相关的MySQL软件;NO2.-->删除安装目录下C:\Program Files的 MySQL文件夹;NO3.-->删除目录下C:\ProgramData（是一个隐藏文件夹）的MySQL文件夹;NO4.-->删除注册表HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\EventLog\Application下的MySQL文件夹
3.MySQL的配置
3.1My.ini文件的位置:在“C: \ProgramData\ MySQL\MySQL Server 5.7\data”目录下。
3.2My.ini重要的设置:
port客户端和服务端的端口号默认的端口号为3306
default-character-set客户端字符集如果要在数据表中正确地显示中文，必须将其设置为UTF8。
character-set-server服务器端字符集如果要在数据表中正确地显示中文，必须将其设置为UTF8。
default-storage-engine默认的存储引擎为InnoDB。
3.3查询默认存储引擎:SHOW VARIABLES LIKE ‘%storage_engine%’;
3.4DOS窗口启动和关闭MySQL服务
m在C:\WINDOWS\System32文件下以管理员身份运行 “cmd”命令。
启动MySQL服务NET START MySQL57
关闭MySQL服务NET STOP MySQL57
4.E-R模型
5.创建数据库
5.1创建数据库SQL语句：CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database_name；
database_name：创建的数据库名。
IF NOT EXISTS：在创建数据库前加上一个判断，只有该数据库目前尚不存在时才执行CREATE DATABASE操作。
5.2查看数据库语法：SHOW DATABASES;
5.3选择数据库：USE database_name;
5.4删除数据库：DROP DATABASE database_name;
6. 数据类型
6.1二进制类型： BINARY(binary)备注：BINARY系列字符串系列可以存储二进制数据，如图片、音乐等文件。
6.2二进制类型：BLOB(blob)备注：如果需要存储大量二进制数据，如电影等视频文件，则选择BLOB系列字符串类型。
7. 创建数据表
7.1创建数据表SQL语句
CREATE TABLE table_name (
col_name 1 data_type1，
col _name 2 data_type 2，
︙
col_name n data_type n
）
说明：
l table_name 为创建的数据表的表名l col_name 为创建的字段名l data_type 为要创建的字段的数据类型。7.2 查看表结构语句DESCRIBE table_name ;7.3 查看所有数据表SHOW TABLES;
7.4复制表结构
CREATE TABLE table_name1 LIKE table_name2;
说明：如果是复制其他数据库的表结构，在table_name2前加上数据库的名称：
例：将数据库db_library中的reader表结构复制到当前数据库，命名为表结构t_readerr；
CREATE TABLE t_reader LIKE db_library.reader;
7.5删除表
DROP TABLE table_name;
8. 修改数据表
8.1修改表名ALTER TABLE old_table_name RENAME new_table_name;
8.2增加字段ALTER TABLE table_name ADD col_name1 data_type [FIRST|AFTER col_name2];
8.3删除字段ALTER TABLE table_name DROP col_name
8.4修改字段的数据类型ALTER TABLE table_name MODIFY col_name1 data_type
8.5修改字段的名称
ALTER TABLE table_name
CHANGE old_col_name new_col old_data_type
8.6修改字段的顺序
ALTER TABLE table_name
MODIFY col_name1 data_type FIRST|AFTER col_name2
9. 数据表约束-非空与默认值
9.1创建新表时设置
CREATE TABLE table_name
(
col_name data_type NOT NULL｜DEFAULT ……
);
9.2非空约束-设置已存在的表
ALTER TABLE table_name MODIFY col_name data_type NOT NULL|DEFAULT;
10.数据表约束-唯一键与自增
10.1创建新表时设置 tips:auto_increment
CREATE TABLE table_name
(
col_name data_type UNIQUE|AUTO_INCREMENT,
……
);
10.2设置已存在的表
ALTER TABLE table_name MODIFY col_name data_type UNIQUE|AUTO_INCREMENT;
10.3删除唯一键
ALTER TABLE table_name DROP INDEX index_name；
11.数据表约束-主键与外键
11.1单字段主键
创建新表设置
CREATE TABLE table_name(
col_name data_type PRIMARY KEY,
……);
为已存在的表设置
ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY(col_name);
删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
11.2多字段主键
创建新表设置
CREATE TABLE table_name(
col_name data_type ,
……
[CONSTRAINT constraint_name] PRIMARY KEY (col_name1, col_name2……));
为已存在的表设置
ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY(col_name1, col_name2……);
11.3外键-创建新表时设置
CREATE TABLE table_name
(
col_name data_type,
……
[CONSTRAINT constraint_name] FOREIGN KEY(col_name1) REFERENCES table_name(col_name2)
);
11.4外键-为已存在的表设置
ALTER TABLE table_name1 ADD [CONSTRAINT constraint_name] FOREIGN KEY(col_name1) REFERENCES table_name2(col_name2)
11.5删除外键:ALTER TABLE table_name1 DROP FOREIGN KEY constraint_name
11.6外键与主键的约束
RESTRICT当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。(restrict)
CASCADE从父表删除或更新行时自动删除或更新子表中匹配的行。
SET NULL当从父表删除或更新行时，设置子表中与之对应的外键列为NULL。如果外键列没有指定NOT NULL限定词，这就是合法的。
NO ACTION不采取动作，如果有一个相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和RESTRICT一样。
12.关系代数运算
13.简单查询
语法格式
SELECT col_name1,col_name2,…col_namen
FROM table_name;
14.条件查询
14.1条件查询:语法：
SELECT col_name1,col_name2,…col_namen
FROM table_name
WHERE condition
LIMIT 【offset,】row_count
说明：
offset:指定初始位置（MySQL中默认第1条记录的偏移量为0，第二条记录的偏移量的值为1，依次类推），row_count:指定查询的行数
14.2LIKE关键字
“ %” 通配符的使用：匹配任意字符“_” 通配符的使用：匹配单个字符
15.统计函数与分组排序
15.1语法格式
SELECT founction(col_name)
FROM table_name
WHERE condition
GROUP BY col_name
HAVING condition
ORDER BY col_name1 [ASC|DESC], col_name2 [ASC|DESC],…,
col_namen [ASC|DESC];
说明：ASC:升序排序(默认) DESC:降序排序
例子：在数据表t_reader中查询男女读者的平均年龄，并且按平均年龄的降序排列。
SELECT AVG(YEAR(CURDATE())-YEAR(reader_birthday)) AS 平均年龄,reader_sex
FROM t_reader
GROUP BY reader_sex
ORDER BY 1 DESC;
16.内连接查询1（自连接）
自连接实例
查询至少借阅了ISBN为“978-7-5611-8921-4”和“978-7-5106-4661-4”两本书的读者编号。
SELECT a.reader_id
FROM t_borrow_record a INNER JOIN t_borrow_record b
ON a.reader_id=b.reader_id
WHERE a.ISBN='978-7-5611-8921-4' AND b.ISBN='978-7-5106-4661-4';
17.内连接查询2（等值/不等连接）
17.1内连接查询--等值查询
INNER JOIN使用比较运算符“=”匹配每个表共有列的列值，查询结果仅包含符合连接条件与筛选条件的行。
SELECT tableA.col_name1, tableB.col_name2
FROM tableA INNER JOIN tableB
ON tableA.col_nameX= tableB. col_nameX
等值连接实例：查询女读者的所有的借阅信息
SELECT t_borrow_record.*
FROM t_reader INNER JOIN t_borrow_record
ON t_reader.reader_id= t_borrow_record.reader_id
WHERE t_reader.reader_sex=‘女’;
17.2内连接查询—不等查询
INNER JOIN使用“BETWEEN”或除“=”的比较运算符 匹配每个表相似列的列值，查询结果仅包含符合连接条件与筛选条件的行。
SELECT tableA.col_name1, tableB.col_name2
FROM tableA INNER JOIN tableB
ON tableA.col_nameX BETWEEN tableB. col_nameM AND tableB. col_nameN
不等连接查询实例
SELECT a.reader_id,a.reader_name,a.reader_birthday,b.age_group
FROM t_reader a INNER JOIN t_age_group b
ON TIMESTAMPDIFF(year,a.reader_birthday,CURDATE())
BETWEEN b.age_min and b.age_max;
17.3TIMESTAMPDIFF(year,a.reader_birthday,CURDATE())
注：上述函数为时间差函数
TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
返回日期datetime_expr1和datetime_expr2之间的整数差。其结果的单位由interval参数给出。该参数必须是DAY、WEEK、MONTH、YEAR等其中一个。
CURDATE（）当前日期函数。(timestampdiff)
18.外连接查询
18.1左外连接(LEFT [OUTER] JOIN)：在表关系的笛卡尔积记录中，除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录。
SELECT col_name1,col_name2,…col_namen
FROM table_name1 LEFT JOIN table_name2
ON join_condition;
左外连接查询实例
查询已经借出却没有归还书的借阅信息。
SELECT a.*,b.*
FROM t_borrow_record a LEFT JOIN t_return_record1 b
ON a.borrow_id=b.borrow_id;
18.2右外连接
右外连接(RIGHT [OUTER] JOIN)：在表关系的笛卡尔积记录中，除了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录。
SELECT col_name1,col_name2,…col_namen
FROM table_name1 RIGHT JOIN table_name2
ON join_condition;
右外连接查询实例
新建t_book1,表结构与t_book结构一致，并复制表t_book中的所有数据至表t_book1中，删除表t_book1中的press_id为‘101’的图书信息。
CREATE TABLE t_book1 SELECT * FROM t_book;
DELETE FROM t_book1 WHERE press_id=‘101’;
查询在图书馆书籍中与已知出版社对应的情况。
SELECT a.ISBN,a.book_name,b.press_id,b.press_name
FROM t_book1 a RIGHT JOIN t_press b
ON a.press_id=b.press_id;
19. 子查询1(IN,EXISTS)
19.1 IN子查询
在内层查询中返回某列的值，外层使用相同的列和子查询的返回列值比较。使用关键词IN时，返回值可以有多个，使用关系运算符时，返回值至多一个 。
SELECT table1. col_namen FROM table1
WHERE col_name1 IN |NOT IN
(SELECT col_name1 FROM table2
WHERE condition );
IN子查询实例
查询女读者的所有的借阅信息
SELECT t_borrow_record .* FROM t_borrow_record
WHERE reader_id IN
(SELECT reader_id FROM t_reader
WHERE reader_sex=‘女’ );
19.2EXISTS子查询
EXISTS用来检查子查询是否有查询结果返回，只要返回
EXISTS的结果即为True，外查询语句将进行查询；反之结果为 False，此时外层语句将不进行查询SELECT table1. col_namen FROM table1
WHERE EXISTS |NOT EXISTS
(SELECT col_name1 FROM table2
WHERE condition );
查询没有借阅记录的读者信息。
SELECT * FROM t_reader a
WHERE NOT EXISTS
(SELECT reader_id FROM t_borrow_record
WHERE reader_id=a.reader_id);
19.3IN、EXISTS之间的转换
查询女读者的所有的借阅信息
SELECT t_borrow_record .*
FROM t_borrow_record
WHERE reader_id IN
(SELECT reader_id FROM t_reader
WHERE reader_sex=‘女’ );
------------->>>>>>>>>>
SELECT t_borrow_record .*
FROM t_borrow_record
WHERE EXISTS
(SELECT reader_id FROM t_reader
WHERE reader_sex=‘女’ AND t_borrow_record.reader_id=t_reader.reader_id );
20.子查询2(ALL,ANY)
20.1ANY子查询
>=ANY: 返回比子查询中最小的还要大于等于的数据记录。<=ANY: 返回比子查询中最大的还要小于等于的数据记录。SELECT table1. col_namen FROM table1
WHERE col_name1 >=ANY|<=ANY
(SELECT col_name1 FROM table2
WHERE condition );
20.2ALL子查询
>= ALL : 返回比子查询中最大的还要大于等于的数据记录。<= ALL : 返回比子查询中最小的还要小于等于的数据记录。SELECT table1. col_namen FROM table1
WHERE col_name1 >=ALL|<=ALL
(SELECT col_name1 FROM table2
WHERE condition );
20.3ALL子查询实例
在t_reader表中查询女读者的出生日期大于或等于所有男读者的信息。
SELECT *
FROM t_reader
WHERE reader_birthday>=ALL
(SELECT reader_birthday
FROM t_reader
WHERE reader_sex=‘男’)
AND reader_sex=‘女’;
------------->
SELECT *
FROM t_reader
WHERE reader_birthday>=
(SELECT MAX(reader_birthday)
FROM t_reader
WHERE reader_sex=‘男’)
AND reader_sex=‘女’;
21.插入操作
21.1插入完整数据记录
INSERT INTO table_name VALUES(values1, values2……valuesn);
21.2插入数据记录的一部分
INSERT INTO table_name (co_name1, co_name2……co_namen)
VALUES(values1, values2……valuesn);注意：字段的名字顺序可以和数据库中表的字段顺序不一样，只要values中的值一 一匹配就可以，当然，最主要的是类型对应，加入说输入了错误的值，那就没有意义了。
22.3插入多条数据记录
INSERT INTO table_name (co_name1, co_name2……co_namen)
VALUES(values11, values21……valuesn1),
(values12, values22……valuesn2),
(values13, values23……valuesn3),
……
(values1m, values2m……valuesnm)
22.4插入来自另一个表的记录
INSERT INTO table_name1 (co_name1, co_name2……co_namen)
SELECT name (co_name1, co_name2……co_namen)
FROM table_name2;
插入记录实例
新建一个表t_book1，表结构与t_book一致，将表t_book中的数据全部插入到表t_book1中。
CREATE TABLE t_book1 like t_book;
INSERT INTO t_book1 SELECT * FROM t_book;
等价于================================
CREATE TABLE t_book1 SELECT * FROM t_book;
22. 更新操作
通过SQL命令更新数据记录
UPDATE table_name
SET co_name1=values1,
co_name2=values2,
……
co_namen=valuesn
WHERE condition
更新记录实例
将读者“王芝” 的借书日期延期一个月
UPDATE t_borrow_record a
SET borrow_date=DATE_ADD(borrow_date, INTERVAL 1 month)
WHERE reader_id in
(SELECT reader_id
FROM t_reader WHERE reader_name=“王芝” ;
23.删除操作
通过SQL命令删除数据记录
DELETE FROM table_name
WHERE condition;
修改外键与主键的约束方式为restrict，删除press_telephone以“010”开始的出版社的信息。
ALTER TABLE t_book ADD CONSTRAINT fk_pressid FOREIGN KEY(press_id) REFERENCES t_press(press_id) ON DELETE RESTRICT;
DELETE FROM t_press WHERE press_telephone LIKE '010%';
修改外键与主键的约束方式为cascade ,删除press_telephone以“0431”开始的出版社的信息。
ALTER TABLE t_book ADD CONSTRAINT fk_pressid FOREIGN KEY(press_id) REFERENCES t_press(press_id) ON DELETE CASCADE;
DELETE FROM t_press WHERE press_telephone LIKE '0431%';
24. 索引的概述
25. 创建索引
25.1创建表时创建索引
CREATE TABLE table_name
(
col_name col_definition
col_name col_definition
……
col_name col_definition
[UNIUQE|FULLTEXT] INDEX index_name(col_name ASC|DESC)
);
25.2为已存在的表创建索引
方法1CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (co_name ASC|DESC)
方法2ALTER TABLE table_name ADD [UNIQUE|FULLTEXT] INDEX index_name （co_name ASC|DESC）
创建索引实例01
创建表t_book2，与t_book结构一致（不能复制表结构），在创建表的过程中为ISBN创建一个唯一降序索引。
CREATE TABLE t_book2
(
ISBN char(17) , book_name varchar(50), book_author varchar(50),
book_price decimal(3,1), press_id char(3), book_copy int(3),
book_inventory int(3),
UNIQUE INDEX index_isbn(ISBN DESC)
);
SHOW INDEX FROM t_book2;
创建索引实例02
为表t_press2的字段列press_name创建全文索引。
ALTER TABLE t_press2 ADD FULLTEXT INDEX index_name1(press_name);
SHOW INDEX FROM t_press2;
创建索引实例03
为表t_borrow_record2建立多列索引，字段列borrow_id降序排列， reader_id列升序排列。
CREATE INDEX index_bid1 ON t_borrow_record2(borrow_id DESC, reader_id ASC);
SHOW INDEX FROM t_borrow_record2;
26.视图的概述
27.创建视图
27.1创建视图SQL语句
CREATE VIEW view_name
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
27.2查看视图SQL语句
SHOW TABLES [LIKE ‘view_name’];
SHOW TABLE STATUS [LIKE ‘view_name’];
SHOW CREATE VIEW view_name;
DESCRIBE|DESC view_name;
27.3修改视图SQL语句
ALTER VIEW view_name
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
27.4删除视图SQL语句
DROP VIEW IF EXISTS view_name [CASCADE|RESTRICT];
28.存储过程的基本语法
28.1会话用户变量定义
方法1
SET @var1 = 值1 [, @var2 = 值2, … ]
示例：SET @price=5.5;
方法2
SELECT 值1 INTO var1[ , 值2 INTO var2, … ]
示例：SELECT min(book_price) into para1,
max(book_price) into para2, avg(book_price)into para3
28.2局部变量定义
DECLARE var1 [, var2] ... 数据类型 [DEFAULT 默认 值 ]作用：定义 1 ~ n 个指定类型的局部变量，并设置初始值。示例： DECLARE num int DEFAULT 0;说明：DECLARE 只能用于 BEGIN…END 的开头部分定义局部变量。28.3程序结构28.3.1分支语句28.3.11 IF语句
IF 条件1 THEN 语句序列1
[ELSEIF条件2 THEN 语句序列2]
...
[ELSE 语句序列n]
END IF
28.3.12 CASE语句1
CASE
WHEN 条件1 THEN 语句序列1
[WHEN 条件2 THEN 语句序列2]
...
[ELSE 语句序列n]
END CASE
28.3.13 CASE语句2
CASE 表达式
WHEN 值1 THEN 语句序列1
[WHEN 值2 THEN 语句序列2]
...
[ELSE 语句序列n]
END CASE
28.3.2循环语句
28.3.21 WHILE语句
[开始标号:]WHILE条件 DO
程序段
END WHILE[结束标号]
28.3.22 REPEAT语句
[开始标号:]REPEAT
程序段
UNTIL 条件
END REPEAT[结束标号]
28.3.23 LOOP语句
语法格式: [开始标号：]LOOP
程序段
END LOOP[结束标号]
29.创建普通的存储过程
29.1创建存储过程
语法：
CREATE PROCEDURE procedure_name ([procedure_parameter[,……]])
[characteristic……] routine_body
说明：
procedure_name:要创建的存储过程的名字
procedure_parameter：表示存储过程的参数
Characteristic：表示存储过程的特性
routine_body：表示存储过程的SQL语句代码
可以用BEGIN……END来标志SQL语句的开始和结束
29.2查看存储过程状态信息
SHOW PROCEDURE STATUS LIKE ‘procedure_name’
29.3查看存储过程定义信息
SHOW CREATE PROCEDURE procedure_name ;
29.4修改存储过程
ALTER PROCEDURE procedure_name [characteristic……];
注意：这个语法用于修改存储过程的某些特征，比如读写权限。如要修改存储过程的内容，可以先删除该存储过程，再重新创建。
29.5删除存储过程
DROP PROCEDURE procedure_name;
29.6存储过程的语句结构
DELIMITER $
BEGIN
……
END $
DELIMITER ;
创建存储过程实例
创建一个存储过程，查询所有读者的基本信息。(tips:delimiter)
DELIMITER $
CREATE PROCEDURE p1 ()
COMMENT '查询所有读者的信息’
BEGIN
SELECT *
FROM t_reader
END $
DELIMITER ;
调用存储过程：
CALL p1 ();
30. 创建带参数的存储过程
30.1参数类型
IN输入参数表示调用者向过程传入值（传入值可以是常量或变量）
OUT输出参数表示过程向调用者传出值(可以返回多个值，传出值只能是变量）
INOUT输入输出参数既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
30.2输入参数实例
创建带输入参数的存储过程，查询某个读者ID对应的读者姓名。
DELIMITER $
CREATE PROCEDURE p_in(in pid CHAR(6))
COMMENT ‘查询某个ID对应的读者姓名’
BEGIN
SELECT reader_name
FROM t_reader
WHERE reader_id=pid;
END $
DELIMITER ;
CALL p_in('101101');
30.3输出参数实例
创建一个带输出参数的存储过程，查询图书的最高价格，最低价格和平均价格。
DELIMITER $
CREATE PROCEDURE p_out
(out para_min float, out para_max float, out para_avg float)
COMMENT “查询图书的最高价格，最低价格和平均价格"
BEGIN
SELECT min(book_price),
max(book_price),
avg(book_price)
INTO para_min,para_max,para_avg
FROM t_book;
END $
DELIMITER ;
CALL p_out(@para_min,@para_max,@para_avg);
SELECT @para_min,@para_max,@para_avg;
30.4输入输出参数实例
delimiter $
CREATE PROCEDURE p_inout(inout price float)
COMMENT ‘对某本书的价格增加10元’
BEGIN
SET price=price+10;
END $
DELIMITER ;
SET @currentprice=5.5;
CALL p_inout(@currentprice);
SELECT (@currentprice);
31.创建带控制条件的存储过程
31.1循环条件
DELIMITER $
CREATE PROCEDURE p2(OUT total INT)
BEGIN
DECLARE num int DEFAULT 0;
SET total=0;
WHILE num<=100 DO
SET num=num+1;
SET total=total+num;
END WHILE;
END $
DELIMITER ;
CALL p2(@sum);
SELECT @sum;
31.2IF分支条件
DELIMITER $
CREATE PROCEDURE compare_age
(OUT age1 INT,OUT age2 INT,IN name1 VARCHAR(50),IN name2 VARCHAR(50),OUT result CHAR(20))
BEGIN
SELECT year(curdate())-year(reader_birthday) INTO age1
FROM t_reader WHERE reader_name=name1;
SELECT year(curdate())-year(reader_birthday) INTO age2
FROM t_reader WHERE reader_name=name2;
IF age1>age2 THEN
SET result=CONCAT(name1,'的年龄大于',name2);
ELSEIF age1=age2 THEN
SET result=CONCAT(name1,'与',name2,'同岁');
ELSE
SET result=CONCAT(name1,'的年龄小于',name2);
END IF;
END $
DELIMITER ;
CALL compare_age(@age1,@age2,'肖华','郑琴',@result);
SELECT @age1,@age2,@result;
31.3CASE分支条件
DELIMITER $
CREATE PROCEDURE proc_price
( IN bname VARCHAR(50),OUT price FLOAT,OUT result VARCHAR(10))
Begin
SELECT book_price INTO price
FROM t_book
WHERE book_name=bname;
CASE
WHEN price>=100 THEN SET result="昂贵";
WHEN price<=50 THEN SET result="便宜";
WHEN price is null THEN SET result="无此书籍";
ELSE SET result="中等";
END CASE;
END $
DELIMITER ;
CALL proc_price('云计算应用技术',@price,@b);
SELECT @price,@b;
32.创建函数
32.1语法：CREATE FUNCTION func_name(parameters)
RETURNS type
body
说明：
• CREATE FUNCTION 用来创建函数的关键字；• func_name 表示函数的名称；• parameters 为函数的参数列表，参数列表的形式为： [IN|OUT|INOUT] param_name type• type 为函数的类型，比如 int 或者 char(20) 等。• body 的一般格式为： beginreturn(select查询语句);
end
32.2函数实例
创建一个函数，查询某本图书的复本量
DELIMITER $
CREATE FUNCTION func_bookcopy(bookid CHAR(17))
RETURNS int(3)
BEGIN
RETURN(SELECT book_copy
FROM t_book
WHERE isbn=bookid);
END $
DELIMITER ;
SELECT func_bookcopy('978-7-04-034745-6');
33.触发器概述
33.1语法：
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW [trigger_order] trigger_body
说明：
Trigger_time:触发器执行的时间:AFTER｜BEFORE
Trigger_event:触发器触发的事件：DELETE|UPDATE|DELETE
FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器
Table_name:表示触发事件操作表的名字
Trigger_body:创建触发器的SQL语句
33.2查看存储器
SHOW TRIGGERS;
SHOW CREATE TRIGGER trigger_name;
33.3NEW和OLD的应用
MySQL 中定义了 NEW 和 OLD 两个临时表，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：①在INSERT型触发器中，NEW用来拦截并保存将要（BEFORE）或已经（AFTER）插入的新数据；
②在UPDATE型触发器中，OLD用来拦截并保存将要或已经被修改的原数据，NEW用来拦截并保存将要或已经修改为的新数据；
③在DELETE型触发器中，OLD用来拦截并保存将要或已经被删除的原数据；
33.4删除触发器
DELETE TRIGGER trigger_name;
34. 创建插入触发器
34.1实例1
每向t_book中插入一条记录后，则向t_log表中插入该表的表名t_book和插入的时间。
CREATE TABLE t_log
(
logno INT AUTO_INCREMENT PRIMARY KEY,
tname VARCHAR(20),
logtime DATETIME
);
CREATE TRIGGER trigger_log
AFTER INSERT
ON t_book1 FOR EACH ROW
INSERT INTO t_log (tname,logtime) VALUES('t_book',now());
--向表t_book1中插入数据
INSERT INTO t_book1 (isbn,book_name) VALUES('978-7-115-45663-2','MySQL数据库管理与开发');
--查看日志表的数据
SELECT * FROM t_log;
34.2实例2
创建一个t_borrow_record表的插入触发器，当向t_borrow_record表中插入一条数据时，该记录对应的图书的库存量自动-1，对应的读者的借阅量自动+1。
CREATE TRIGGER trigger_insert2
AFTER INSERT
ON t_borrow_record FOR EACH ROW
BEGIN
UPDATE t_reader SET reader_borrowtotal=reader_borrowtotal+1
WHERE reader_id=new.reader_id;
UPDATE t_book SET book_inventory=book_inventory-1
WHERE ISBN=new.ISBN;
END
--执行一条插入语句，测试
INSERT INTO t_borrow_record(reader_id,ISBN)
VALUES('101103','978-7-5106-4661-4');
35.创建更新触发器
35.1更新触发器的关键点
关键点：
1.更新相当于删除旧数据，插入新数据；
2.旧数据存储到OLD表中；
3.新数据存储到NEW表中。
35.2实例1
创建一个t_press表的更新触发器，当更新t_press表中某一条记录的出版社信息后，获取更新前后的出版社名称，并判断是否一致。
CREATE TRIGGER trigger_u2
AFTER UPDATE
ON t_press FOR EACH ROW
BEGIN
IF(NEW.press_name=OLD.press_name) THEN
SELECT "相同" INTO @result;
ELSE
SELECT "不同" INTO @result;
END IF;
END
--更新记录，测试触发器效果
UPDATE t_press SET press_name='清华大学出版社' WHERE press_id='101';
SELECT @result;
36.创建删除触发器
实例1
创建一个delete触发器，删除t_reader1中的数据时，将对应的t_borrow_record1中的数据删除。
CREATE Trigger trigger_delete
AFTER DELETE
ON t_reader1 FOR EACH ROW
BEGIN
DELETE FROM t_borrow_record1
WHERE reader_id=old.reader_id;
END
--删除前先查询'101101'的借阅数据
SELECT * FROM t_borrow_record1 WHERE reader_id='101101';
--删除t_reader1中的数据：
DELETE FROM t_reader1 WHERE reader_id='101101';
--再次查询'101101'的借阅数据,进行对比。
SELECT * FROM t_borrow_record1 WHERE reader_id='101101';
37.范式
38.关系代数
∪ ∩ - × 传统
σ Π ∞ ÷ 专门
39.并发控制
39.1 l 基本封锁类型– 排它锁（ eXclusive lock ，简记为 X 锁）– 共享锁（ Share lock ，简记为 S 锁）39.2封锁协议l 一、保证数据一致性的封锁协议l 二、保证并行调度可串行性的封锁协议 —— 两段锁协议39.3 死锁和活锁39.3.1如何避免活锁l 采用先来先服务的策略39.3.2 1. 死锁的预防l 预防死锁的方法– 一次封锁法– 顺序封锁法2. 死锁的诊断与解除超时法等待图法39.4封锁粒度– 封锁的粒度越大，系统中能够被封锁的对象就越少，并发度也就越小，但同时系统开销也越小；– 封锁的粒度越小，并发度越高，但系统开销也就越大。
40.数据库安全保护
40.1
1．操作系统口令：
安全系统的核心问题是身份识别：（含WINDOWS-SERVER，应用软件及数据库）
⒈ 用户名（User）通常Administrator
⒉ 口令（Password）
2.应用软件用户口令
如工资管理，或银行管理，分不同级别的用户登录。包括网站的后台也需要用户口令，论坛，EMAIL等。
3. DB数据库口令
对于数据库进行适当的加密可以防止非法用户直接进入你的数据库系统。
4.DBMS权限限制
进行了对用户的识别，在DBMS中还应该有机器强制存取控制起作用， 即严格按照授权控制对数据库的存取。
由此，DBMS要维护一张用户权限表（用户权限表：指不同的用户对于不同的数据对象允许执行的操作权限。），每次用户存取数据库任何数据之 前，都要查表确认存取是否合法。
授权GRANT 收回权限 REVOTE
5. 审计
审计功能把用户对数据库的操作自动记录下来放入审计日志（Audit Log），有时也被称为审计跟踪。
DBA利用审计日志找出非法存取数据的人、时间和内容
6.数据加密
数据加密是防止数据库数据在存储和传输中失密的有效手段，目前仍是计算机系统对信息进行保护的一种最可靠的办法。它利用密码技术对信息进行加密，实现信息隐蔽，从而起到保护信息的安全的作用。
40.2数据完整性
数据完整性(data integrity)是指数据的正确性和相容性。DBMS必须提供一种功能保证数据库的数据完整性，这种功能称为完整性检查。数据完整性是为了防止数据库存在不符合语义的数据，防止错误信息输入和输出，即数据要遵守由DBA或应用开发者决定的一组预定义的规则
1.实体完整性（唯一约束、主键约束、标识列 ）
实体是一个数据对象，是指客观存在并可以相互区分的事务，如一个学生或一个职员。实体完整性规则是指关系的主属性，即主键的组成不能为空，也就是关系的主属性不能为空值NULL。实体完整性是通过主键约束和候选键约束来实现的。
（1）主键约束
规则·：
1）每个表只能定义一个主键；
2）唯一性原则。主键的值必须能够唯一标识表中的每一行记录。
3）最小化原则，复合主键·不能包含不必要的多余列；
4）一个列名在复合主键的列表中只能出现一次。
(2).候选键约束：
定义：若一个属性集能唯一标识元组，且不含多余的属性，那么这个属性集称为关系的候选键。任何时候候选键的值必须是唯一的，且不能为空NULL。其可以在CREATE TABLE 和ALTER TABLE 使用关键字UNIQUE来定义。
(3).候选键与主键区别：
一个表只能创建一个主键，但可以定义多个候选键;定义主键系统会主动创建PRIMARY KEY 索引，而定义候选键约束，系统会自动创建UNIQUE索引。
2.参照完整性（限制数据类型、检查约束、外键约束、默认值、非空约束 ）
参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考完整性。
参照完整性规则：即本关系的属性值需要参照另一关系的属性和值，这就叫参照。参照完整性规则就是定义主键和外键之间的引用规则，它对关系间引用数据的一种限制。
例如建立SC表并将sno,cno分别定义为关系表student,course的外键。
3.用户自定义完整性（规则、存储过程、触发器）
用户定义完整性(user-defined Integrity)是对数据表中字段属性的约束，用户定义完整性规则(user-defined integrity)也称域完整性规则。包括字段的值域、字段的类型和字段的有效规则(如小数位数)等约束，是由确定关系结构时所定义的字段的属性决定的。如，百分制成绩的取值范围在0~100之间等。
用户自定义完整性约束规则
(1) 非空约束（Not Null Constraint）
(2)唯一约束（Unique Constraint）
1) 一个表只能创建一个主键约束，但一个表可以根据需要对不同的列创建若干unique约束
2)主键字段不允许为null ，unique允许为空
3)一般创建主键约束时，系统自动产生簇索引，unique约束自动产非簇索引
(3)检查约束（The Check Clause）
例如：
create table student （
sno varchar(15) sname varchar(10) not null,
sex varchar(2) , degree_level varchar(15), primary key(student_id), check(degree_level in('Bachelors','Masters','Doctorate')));
即检查约束要包含在'Bachelors','Masters','Doctorate'三个学位内。
41.数据库系统设计
41.1数据库系统设计的内容
数据库的结构特性设计——静态
数据库的行为特性设计——动态
41.2数据库系统设计的六个阶段
（1）需求分析阶段(数据流图，数据字典)
（2）概念结构设计阶段(概念模型（如E-R图）)
（3）逻辑结构设计阶段(逻辑模型（如关系模式）)
（4）物理设计阶段
（5）数据库实施阶段(数据载入，应用程序调试)
(6）数据库运行与维护阶段
41.3数据库系统设计的特点
强调结构设计与行为设计相结合；
是一种“反复探寻，逐步求精”的过程；
首先从数据模型开始设计，以数据模型为核心 进行展开，数据库设计和应用系统设计相结合， 建立一个完整、独立、共享、冗余小、安全有 效的数据库系统。
41.4数据库系统的三级模式是指
内模式（存储模式或物理模式）
模 式（逻辑模式或概念模式）
外模式（子模式或用户模式）
模式（Schema）仅涉及“型”，而不涉
及具体的“值”。
信息世界基本术语
实体(Entity)：客观存在并且可以相互区别的“事 物”。
属性(Attributes)：实体的某一特性。
实体型(Entity Type):用实体名及其属性名集合来抽 象和描述的同类实体。
实体集(Entity Set):同型实体的集合。
联系(Relationship):在现实世界中，事物内部以及 事物之间联系的抽象表示。
实体集内部的联系
17万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
