mysql 5.1简明教程
第一章Mysql简介与安装
第一节 MySql简介
百度百科
第二节 MySql安装与配置
1、MySql5.1下载及安装
2、MySql数据库编码配置 UTF-8
3、MySql图形页面sqlyog下载与安装
https://eyun.baidu.com/s/3o7GaVJO#sharelink/path=%2F
第二章 Mysql数据类型简介
第一节：整数类型、浮点数类型和定点数类型
1、整数类型
类型 | 字节数 | 无符号(unsigned)范围 | 有符号(signed)范围 |
TINYINT | 1 | 0~255 | -128~127 |
SMALLINT | 2 | 0~65535 | -32768~32767 |
MEDIUMINT | 3 | 0~16777215 | -8388608~8388607 |
INT | 4 | 0~4294967295 | -2147483648~2147483647 |
INTEGER | 4 | 0~4294967295 | -2147483648~2147483647 |
BIGINT | 8 | 0~18446744073709551615 | -9223372036854775808 ~9223372036854775807 |
2、浮点数类型和定点数类型
类型 | 字节数 | 无符号(unsigned)范围 | 有符号(signed)范围 |
FLOAT | 4 | 0, 1.175494351E-38 ~-3.402523466E+38 | -3.402523466E+38 ~1.175494351E-38 0, 1.175494351E-38 ~3.402523466E+38 |
DOUBLE | 8 | 0, 2.2250738585072014E-308 ~1.7976931348623157E-308 | -1.7976931348623157E+308 ~2.2250738585072014E-308 0, 2.2250738585072014E-308 ~1.7976931348623157E+308 |
DECIMAL(M,D) | M+2 | 同DOUBLE | 同DOUBLE |
M表示：数据的总长度（不包括小数点）； D表示：小数位； 例如 decimal(5,2) 123.45 存入数据的时候，按四舍五入计算 |
第二节：日期与时间类型
类型 | 字节数 | 取值范围 | 零值 |
YEAR | 1 | 1910~2155 | 0000 |
DATE | 4 | 1000-01-01~9999-12-31 | 0000:00:00 |
TIME | 3 | -838:59:59~838:59:59 | 00:00:00 |
DATETIME | 8 | 1000-01-01 00:00:00 ~9999-12-31 23:59:59 | 0000:00:00 00:00:00 |
TIMESTAMP | 4 | 19700101080001 ~20380119111407 | 00000000000000 |
第三节：字符串类型
类型 | 说明 |
CHAR | 固定长度字符串 |
VARCHAR | 可变长度字符串 |
TEXT | 大文本（TINYTEXT, TEXT, MEDIUMTEXT, LOGNTEXT） |
ENUM | 枚举类型（只能取一个元素） |
SET | 集合类型（能取多个元素） |
第四节：二进制类型
类型 | 说明 |
BINARY(M) | 字节数为M，允许长度为0~M的定长二进制字符串 |
VARBINARY(M) | 允许长度为0~M的变长二进制字符串，字节数为值的长度加1 |
BIT(M) | M位二进制数据，最多255个字节 |
TINYBLOB | 可变长二进制数据，最多255个字节 |
BLOB | 可变长二进制数据，最多（216-1）个字节 |
MEDIUMBLOB | 可变长二进制数据，最多（224-1）个字节 |
LONGBLOB | 可变长二进制数据，最多（232-1）个字节 |
第三章 数据库基本操作
第一节：数据库简介
数据库（database）是按照数据结构来组织、存储和管理数据的仓库； |
第二节：显示所有数据库
show databases; |
第三节：创建数据库
create database 数据库名; |
第四节：删除数据库
drop database 数据库名; |
第四章 数据库表基本操作
第一节：创建表
表是数据库存储数据的基本单位。一个表包含若干个字段或记录； 语法： create table 表名（ 属性名 数据类型 [完整性约束条件], ... 属性名 数据类型 [完整性约束条件] ）; | |
| |
约束条件 | 说明 |
PRIMARY KEY | 标识该属性为该表的主键，可以唯一的标识对应的记录 |
FOREIGN KEY | 标识该属性为该表的外键，与某表的主键关联 |
NOT NULL | 标识该属性不能为空 |
UNIQUE | 标识该属性的值是唯一的 |
AUTO_INCREMENT | 标识该属性的值自动增加 |
DEFAULT | 为该属性设置默认值 |
USE db_book;
DROP TABLE IF EXISTS `t_book`; DROP TABLE IF EXISTS `t_booktype`;
/*创建图书类别表：t_bookType*/ CREATE TABLE t_booktype ( id INT PRIMARY KEY AUTO_INCREMENT, typeName VARCHAR (20) ) ;
/*创建图书表：t_book*/ CREATE TABLE t_book ( id INT PRIMARY KEY AUTO_INCREMENT, bookName VARCHAR (50), author VARCHAR (50), price DECIMAL (6, 2), bookTypeId INT, CONSTRAINT `fk_t_book` FOREIGN KEY (`bookTypeId`) REFERENCES `t_booktype` (`id`) ) ; |
第二节：查看表结构
USE db_book;
/*查看基本表结构*/ DESCRIBE t_book;
DESC t_booktype;
/*查看表详细结构*/ SHOW CREATE TABLE t_book;
SHOW CREATE TABLE t_booktype; |
第三节：修改表
USE db_book;
/*修改表名*/ ALTER TABLE t_book2 RENAME t_book;
/*修改字段*/ ALTER TABLE t_book CHANGE bookName bookName2 VARCHAR(20);
/*在第一字段前插入一个新的字段*/ ALTER TABLE t_book ADD testField INT FIRST;
/*在指定字段后插入一个新的字段*/ ALTER TABLE t_book ADD testField INT AFTER author;
/*删除字段*/ ALTER TABLE t_book DROP testField; |
第四节：删除表
/*删除表*/ DROP TABLE t_booktype; |
第四章 查询数据
数据准备：
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_book` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `db_book`;
/*Table structure for table `t_book` */
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bookName` varchar(50) DEFAULT NULL, `author` varchar(50) DEFAULT NULL, `price` decimal(6,2) DEFAULT NULL, `bookTypeId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_t_book` (`bookTypeId`), CONSTRAINT `fk_t_book` FOREIGN KEY (`bookTypeId`) REFERENCES `t_booktype` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*Data for the table `t_book` */
LOCK TABLES `t_book` WRITE;
insert into `t_book`(`id`,`bookName`,`author`,`price`,`bookTypeId`) values (1,'Java编程思想(第4版)','埃史尔、 陈昊鹏','84.24',1),(2,'Spring实战(第4版) ','Craig Walls','50.00',1),(3,'Spring+MyBatis企业应用实战','疯狂软件','45.20',1),(4,'Node.js实战','吴海星 (译者)','54.50',1),(5,'React精髓','阿尔乔姆·费多耶夫 (Artemij Fedosejev)、 奇舞团','50.00',1),(6,'Learning TypeScript中文版','Remo H.Jansen、 龙逸楠','70.30',1),(7,'基因与细胞(全彩)','《科学新闻》杂志社 (Science News) ','42.28',2),(8,'物种起源 (译林人文精选)','达尔文 (Charles Robert Darwin)、 苗德岁','25.60',2),(9,'生物心理学(第10版)','詹姆斯•卡拉特、 苏彦捷','152.04',2),(10,'视觉之旅:神奇的化学元素(彩色典藏版)','西奥多•格雷 (Theodore Gray)、 尼克•曼 (Nick Mann)','47.40',3),(11,'元素的盛宴(化学奇谈与日常生活) ','山姆·基恩 (作者), 杨蓓 (译者), 阳曦 (译者)','34.10',3),(12,'化学简史','J.R.柏廷顿(James Riddick Partington)、 胡作玄','29.90',3),(13,'人体:人体结构、功能与疾病图解','帕克 (Steve Parker)、 左焕琛','44.53',4),(14,'全科医学(第4版) ','墨塔(John Murtagh) (作者), 梁万年 (译者)','210.00',4),(15,'医学图文史:改变人类历史的7000年(彩色典藏版)','玛丽•道布森(Mary Dobson)','106.60',4),(16,'仿生结构:结构的相似性','约瑟夫·利姆、 沈海晖','22.10',5),(17,'仿生智能纳米材料','江雷等','110.50',5),(18,'分子仿生','李峻柏、 白春礼','100.60',5),(19,'物理学与生活(原书第8版)','(W. Thomas Griffith)、 (Juliet W.Brosing)','53.50',6),(20,'物理学','亚里士多德、 张竹明','14.00',6),(21,'\r\n物理学基础','David Halliday、Resnick','148.50',6),(22,'数学之美(第二版) ','吴军 (作者)','36.60',7),(23,'迷人的数学','伊凡.莫斯科维奇、 佘卓桓','49.84',7),(24,'牛津通识读本:数学','蒂莫西·高尔斯 (Gowers T.)、 刘熙','19.70',7),(25,'分子系统发生学','黄原','118.50',NULL);
UNLOCK TABLES;
/*Table structure for table `t_booktype` */
DROP TABLE IF EXISTS `t_booktype`;
CREATE TABLE `t_booktype` ( `id` int(11) NOT NULL AUTO_INCREMENT, `typeName` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*Data for the table `t_booktype` */
LOCK TABLES `t_booktype` WRITE;
insert into `t_booktype`(`id`,`typeName`) values (1,'计算机'),(2,'生物学'),(3,'化学'),(4,'医学'),(5,'仿生学'),(6,'物理学'),(7,'数学'),(8,'哲学');
UNLOCK TABLES;
/*Table structure for table `t_pricelevel` */
DROP TABLE IF EXISTS `t_pricelevel`;
CREATE TABLE `t_pricelevel` ( `id` int(11) NOT NULL AUTO_INCREMENT, `priceLevel` int(11) DEFAULT NULL, `price` float DEFAULT NULL, `desc` varchar(300) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `t_pricelevel` */
LOCK TABLES `t_pricelevel` WRITE;
insert into `t_pricelevel`(`id`,`priceLevel`,`price`,`desc`) values (1,1,80,'价格较贵'),(2,2,60,'价格适中'),(3,3,40,'价格便宜');
UNLOCK TABLES; |
第一节：单表查询
1、查询所有字段
SELECT id, bookname, author, price, booktypeid FROM t_book; SELECT * FROM t_book; |
2、查询指定字段
SELECT bookname, author, price FROM t_book; |
3、where条件查询
SELECT * FROM t_book t WHERE t.`bookTypeId` = 1; SELECT * FROM t_book t WHERE t.`price` > 100; |
4、带in关键字查询
SELECT * FROM t_book t WHERE t.`bookTypeId` IN (1, 3); SELECT * FROM t_book t WHERE t.`bookTypeId` NOT IN (1, 3); |
5、带between and的范围查询
SELECT * FROM t_book t WHERE t.`price` BETWEEN 50 AND 100; SELECT * FROM t_book t WHERE t.`price` NOT BETWEEN 50 AND 100; |
6、带like的模糊查询
/*全字匹配，较少用*/ SELECT * FROM t_book t WHERE t.`bookName` LIKE '物理学'; /*单个字模糊匹配*/ SELECT * FROM t_book t WHERE t.`bookName` LIKE '物理_'; /*多个字模糊匹配*/ SELECT * FROM t_book t WHERE t.`bookName` LIKE '物理学%'; SELECT * FROM t_book t WHERE t.`bookName` LIKE '%物理学%'; |
7、空值查询
SELECT * FROM t_book t WHERE t.`author` IS NULL; SELECT * FROM t_book t WHERE t.`author` IS NOT NULL; |
8、带and的多条件查询
SELECT * FROM t_book t WHERE (t.`price` NOT BETWEEN 50 AND 100) AND t.`bookTypeId` = 1; |
9、带or的多条件查询
SELECT * FROM t_book t WHERE t.`bookTypeId` = 1 OR t.`bookTypeId` = 7; |
10、distinct支重复查询
SELECT DISTINCT t.booktypeid FROM t_book t; |
11、对查询结果排序
SELECT * FROM t_book t ORDER BY t.`price`; |
12、group by分组查询
/*与GROUP_CONCAT一起使用，会把结果拼接在一起*/ SELECT GROUP_CONCAT(t.bookname), t.booktypeid FROM t_book t GROUP BY t.booktypeid; /*与COUNT一起使用，统计分组后每组成员的个数*/ SELECT COUNT(t.bookname), t.booktypeid FROM t_book t GROUP BY t.booktypeid; /*与having一起使用，限制输出结果*/ SELECT COUNT(t.bookname), t.booktypeid FROM t_book t GROUP BY t.booktypeid HAVING COUNT(t.bookname) > 3; /*WITH ROLLUP，在最后加入一个汇总行*/ SELECT COUNT(t.bookname), t.booktypeid FROM t_book t GROUP BY t.booktypeid WITH ROLLUP; |
13、limit分页查询
/*查询1-5条记录*/ SELECT * FROM t_book t LIMIT 0,5; /*查询6-10条记录*/ SELECT * FROM t_book t LIMIT 5,5; /*查询11-15条记录*/ SELECT * FROM t_book t LIMIT 10,5; |
第二节：使用聚合函数
1、count函数
SELECT COUNT(*) AS total FROM t_book ; SELECT COUNT(t.bookname), t.booktypeid FROM t_book t GROUP BY t.booktypeid; |
2、sun函数
SELECT SUM(t.price), t.booktypeid FROM t_book t GROUP BY t.booktypeid; |
3、avg函数
SELECT AVG(t.price), t.booktypeid FROM t_book t GROUP BY t.booktypeid; |
4、max函数
SELECT MAX(t.price), t.booktypeid FROM t_book t GROUP BY t.booktypeid; |
5、min函数
SELECT MIN(t.price), t.booktypeid FROM t_book t GROUP BY t.booktypeid; |
第三节：连接查询
连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。 |
1、内连接查询
内连接查询是一种最常用的连接查询。内连接查询可以查询两个或者两个以上的表。 SELECT * FROM t_book, t_booktype;
SELECT * FROM t_book b, t_booktype bt WHERE b.`bookTypeId` = bt.`id`;
SELECT b.`bookName`, b.`price`, bt.`bookTypeName` FROM t_book b, t_booktype bt WHERE b.`bookTypeId` = bt.`id`; |
2、外连接查询
外连接可以查出某一张的所有信息 |
a) 左连接查询
可以查询出“表1”的所有记录，而“表2”中只能查询出匹配的记录 /*以左表作为全量表进行关联查询*/ SELECT b.`bookName`, b.`author`, bt.`bookTypeName` FROM t_book b LEFT JOIN t_booktype bt ON b.`bookTypeId` = bt.`id`; |
b) 右连接查询
可以查询出“表2”的所有记录，而“表1”中只能查询出匹配的记录 /*以右表作为全量表进行关联查询*/ SELECT b.`bookName`, b.`author`, bt.`bookTypeName` FROM t_book b RIGHT JOIN t_booktype bt ON b.`bookTypeId` = bt.`id`; |
3、多条件连接查询
SELECT b.`bookName`, b.`price`, bt.`bookTypeName` FROM t_book b, t_booktype bt WHERE b.`bookTypeId` = bt.`id` AND b.`price` > 50; |
第四节：子查询
1、带in关键字的子查询
一个查询语句的条件可能落在另一个select语句的查询结果中 SELECT * FROM t_book t WHERE t.`bookTypeId` IN (SELECT id FROM t_booktype); SELECT * FROM t_book t WHERE t.`bookTypeId` NOT IN (SELECT id FROM t_booktype); |
2、带比较运算符的子查询
子查询可以使用比较运算符 SELECT * FROM t_book t WHERE t.price >= (SELECT price FROM t_pricelevel WHERE pricelevel = 1); |
3、带exists关键字的子查询
假如子查询查询到记录，则进行外层查询，否则，不执行外层查询 SELECT * FROM t_book t WHERE EXISTS (SELECT id FROM t_booktype); SELECT * FROM t_book t WHERE NOT EXISTS (SELECT id FROM t_booktype); |
4、带any关键字的子查询
Any关键字表示满足其中任一条件 SELECT * FROM t_book t WHERE t.price >= ANY(SELECT price FROM t_pricelevel); |
5、带all关键字的子查询
All关键字表示满足所有条件 SELECT * FROM t_book t WHERE t.price >= ALL(SELECT price FROM t_pricelevel); |
第五节 合并查询结果
1、union
使用union关键字时，数据库系统会将所有的查询结果合并在一起，然后去除掉相同的记录。 SELECT * FROM t_book t WHERE t.`price` BETWEEN 50 AND 100 UNION SELECT * FROM t_book t WHERE t.`bookTypeId` = 1 |
2、union all
使用union all不会去除掉系统相同的记录，而是将所有结果合并到一起。 SELECT * FROM t_book t WHERE t.`price` BETWEEN 50 AND 100 UNION ALL SELECT * FROM t_book t WHERE t.`bookTypeId` = 1 |
第五章 为表和字段取别名
1、为表取别名
SELECT * FROM t_book t WHERE t.id = 1; |
2、为字段取别名
SELECT t.id AS id, t.`bookName` AS bookName, t.`price` AS price FROM t_book t WHERE t.id = 1 ; |
第六章 插入、更新和删除数据
第一节：插入数据
1、给表的所有字段插入数据
INSERT INTO t_book VALUES ( NULL, 'Effective Java中文版(第2版)', 'Joshua Bloch', 41.00, 1 ) ; |
2、给表的指定字段插入数据
INSERT INTO t_book (bookname, author, price, booktypeid) VALUES ( 'Java并发编程实战', 'Brian Goetz, Tim Peierls, Joshua Bloch', 52.42, 1 ) ; |
3、同时插入多条记录
INSERT INTO t_book (bookname, author, price, booktypeid) VALUES ( 'Java 8实战', '厄马(Raoul-Gabriel Urma)、 弗斯科(Mario Fusco)', 61.62, 1 ), ( 'Java性能权威指南', '奥克斯 (Scott Oaks)、 柳飞', 62.40, 1 ) ; |
第二节：更新数据
UPDATE t_book t SET t.`bookName` = 'Java编程思想(第4版)', t.`price` = 84.24 WHERE t.id = 1 ; |
第三节：删除数据
DELETE FROM t_book t WHERE t.id = 1 ; |
第七章 索引
第一节：索引的引入
索引定义：索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度；类似于图书的目录，方便快速定位，寻找指定的内容。 |
第二节：索引的优缺点
优点：提高查询数据的速度。 缺点：创建和维护索引的时间增加了。 |
第三节：索引实例
第四节：索引的分类
1、普通索引
这类索引可以创建在任何数据类型中。 |
2、唯一索引
使用unique参数可以设置，在创建唯一索引时，限制该索引的值必须是唯一的。 |
3、全文索引
使用fulltext参数可以设置，全文索引只能创建在char、varchar、text、类型的字段上。主要作用就是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，Mysql默认引擎不支持。 |
4、单列索引
在表中可以给单个的字段创建索引，单列索引可以是普通索引，也可以是唯一索引，还可以是全文索引。 |
5、多列索引
多列索引是在表的多个字段上创建一个索引。 |
6、空间索引
使用spatial参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。 |
第五节：创建索引
1、创建表的时候创建索引
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [别名](属性名1[(长度)][ASC|DESC])
/*单列索引*/ CREATE TABLE t_user ( id INT, username VARCHAR (20), PASSWORD VARCHAR (20), INDEX idx_username(username) ) ;
/*唯一索引*/ CREATE TABLE t_user2 ( id INT, username VARCHAR (20), PASSWORD VARCHAR (20), UNIQUE INDEX idx_username(username) ) ;
/*多列索引*/ CREATE TABLE t_user3 ( id INT, username VARCHAR (20), PASSWORD VARCHAR (20), INDEX idx_username_password(username,PASSWORD) ) ; |
2、在已经存在的表上创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (属性名[(长度)][ASC|DESC])
CREATE TABLE t_user4 ( id INT PRIMARY KEY NOT NULL, username VARCHAR (20), PASSWORD VARCHAR (20) ) ;
CREATE INDEX idx_username ON t_user4(username); CREATE UNIQUE INDEX idx_username ON t_user4(username); CREATE INDEX idx_username_password ON t_user4(username,PASSWORD); |
3、用alter table语句来创建索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (属性名[(长度)][ASC|DESC])
ALTER TABLE t_user4 ADD INDEX idx_username(username); ALTER TABLE t_user4 ADD UNIQUE INDEX idx_username(username); ALTER TABLE t_user4 ADD INDEX idx_username(username, PASSWORD); |
第六节：删除索引
DROP INDEX 索引名 ON 表名; DROP INDEX idx_username ON t_user4; |
第八章 视图
第一节：视图的引入
1、视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。
2、数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。
3、使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
第二节：视图的作用
1、使操作简便化。
2、增加数据的安全性。
3、提高表的逻辑独立性。
第三节：创建视图
CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名[(属性清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION]]
ALGORITHM是可选参数，表示视图选择的算法。它包括3个选项UNDEFINED、MERGE和TEMPTABLE。其中，UNDEFINED选项表示mysql将自动选择所要使用的算法；MERGE选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分；TEMPTABLE选项表示将视图的结果存入临时表，然后使用临时表执行语句；
“视图名”参数表示要创建的视图和名称
“属性清单”是可选参数，其指定了视图中各种属性的名词，默认情况下与select语句中查询的属性相同；
SELECT 语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中；
WITH CHECK OPTION是可选参数，表示在更新视图时要保证在该视图的权限范围之内。CASCADED是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；LOCAL表示更新视图时，要满足该视图本身的定义条件即可； |
1、在单表上创建视图
CREATE VIEW v_book AS SELECT bookname, price FROM t_book; CREATE VIEW v_book(NAME, price) AS SELECT bookname, price FROM t_book; |
2、在多表上创建视图
CREATE VIEW v_book (bookname, booktype) AS SELECT b.bookname, bt.typename FROM t_book b, t_booktype bt WHERE b.booktypeid = bt.id; |
第四节：查看视图
1、DESCRIBE语句查看视图基本信息
DESCRIBE v_book; DESC v_book; |
2、SHOW TABLE STATUS语句查看视图基本信息
SHOW TABLE STATUS LIKE 'v_book'; |
3、SHOW CREATE VIEW语句查看视图详细信息
SHOW CREATE VIEW v_book; |
4、在views表中查看视图详细信息
第五节：修改视图
1、CREATE OR REPLACE VIEW语句修改视图
CREATE OR REPLACE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名[(属性清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION]]
CREATE OR REPLACE VIEW v_book (bookname, price, booktype) AS SELECT b.bookname, b.price, bt.typename FROM t_book b, t_booktype bt WHERE b.booktypeid = bt.id; |
2、ALTER语句修改视图
ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名[(属性清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION]]
ALTER VIEW v_book (bookname, booktype) AS SELECT b.bookname, bt.typename FROM t_book b, t_booktype bt WHERE b.booktypeid = bt.id; |
第六节：更新视图
更新视图是指通过视图来插入、更新和删除表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。
1、插入(INSERT)
INSERT INTO v_book VALUES(NULL, 'java', 'jack', 50.00, 1); |
2、更新(UPDATE)
UPDATE v_book SET bookname='java good' WHERE id=30; |
3、删除(DELETE)
DELETE FROM v_book WHERE id=30; |
第七节：删除视图
删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。
DROP VIEW [IF EXISTS] 视图名列表 [RESTRICT|CASCADE]
DROP VIEW IF EXISTS v_book; |
第九章 触发器
第一节：触发器的引入
触发器（trigger）是由事件来触发某个操作。这些事件包括insert语句、update语句和delete语句。当数据库系统执行这此事件时，就会激活触发器执行相应的操作。 |
第二节：创建与使用触发器
1、创建只有一个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句
CREATE TRIGGER trig_book AFTER INSERT ON t_book FOR EACH ROW UPDATE t_bookType bt SET bookNum = bookNum + 1 WHERE bt.id = new.bookTypeId ; |
2、创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句列表 END
DELIMITER | CREATE TRIGGER trig_book2 AFTER DELETE ON t_book FOR EACH ROW BEGIN UPDATE t_bookType bt SET bookNum = bookNum - 1 WHERE bt.id = old.bookTypeId ;
INSERT INTO t_log VALUES ( NULL, NOW(), '在book表里删除了一条数据' ) ;
DELETE FROM t_test WHERE old.bookTypeId = t_test.id ; END | DELIMITER ; |
第三节：查看触发器
1、show triggers语句查看触发器信息
SHOW TRIGGERS; |
2、在triggers表中查看触发器信息
第四节：删除触发器
DROP TRIGGER trig_book ; |
第十章 mysql常用函数
第一节：日期和时间函数
1、CURDATE()：返回当前日期
2、CURTIME()：返回当前时间
3、MONTH(d)：返回日期d中的月份值，范围是1~12
第二节：字符串函数
1、CHAR_LENGTH(s)：计算字符串s的字符数
2、UPPER(s)：把所有字母变成大写字母
3、LOWER(s)：把所有字母变成小写字母
第三节：数学函数
1、ABS(X)：求绝对值
2、SQRT(X)：求平方根
3、MOD(X,Y)：求余
第四节：加密函数
1、PASSWORD(str)：一般用户的密码加密，不可逆
2、MD5(str)：普通加密，不可逆
3、ENCODE(str, pswd_str)：加密函数，结果是一个二进制数，必须使用BLOB类型的字段来保存它
4、DECODE(crypt_str, pswd_str)：解密函数
更多函数请到网上下载手册
MySQL 5.1参考手册.7z
http://pan.baidu.com/s/1dDhqLaD
第十一章 存储过程与函数
第一节：存储过程与函数的引入
存储过程和函数是在数据库中定义一些SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的SQL 语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输； |
第二节：创建存储过程和函数
1、创建存储过程
CREATE PROCEDURE sp_name([proc_parameter[,...]]) [characteristic...] routine_body
sp_name 参数是存储过程的名称； proc_parameter 表示存储过程的参数列表； characteristic 参数指定存储过程的特性； routine_body 参数是SQL 代码的内容，可以用BEGIN...END 来标志SQL 代码的开始和结束。 proc_parameter 中的每个参数由3 部分组成。这3 部分分别是输入输出类型、参数名称和参数类型。 [ IN | OUT | INOUT ] param_name type 其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是 存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是MySQL 数据库的任意数据类型； Characteristic 参数有多个取值。其取值说明如下： LANGUAGE SQL：说明routine_body 部分是由SQL 语言的语句组成，这也是数据库系统默认的语言。 [ NOT ] DETERMINISTIC ：指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每 次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是非确定的，相同的输入 可能得到不同的输出。默认情况下，结果是非确定的。 { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL 语句的限制； CONTAINS SQL 表示子程序包含SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含SQL 语句；READS SQL DATA 表示子程序中包含读数据的语句；MODIFIES SQL DATA 表示子程序中包含写数据的 语句。默认情况下，系统会指定为CONTAINS SQL； SQL SECURITY { DEFINER | INVOKER }；指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行； INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是DEFINER。 COMMENT ‘string’ ：注释信息； |
示例：
DELIMITER &&
CREATE PROCEDURE proc_book (IN bt INT, OUT count_num INT) READS SQL DATA BEGIN SELECT COUNT(*) FROM t_book WHERE booktypeid = bt ; END &&
DELIMITER ; |
调用：
proc_book(1, @total); |
2、创建存储函数
CREATE FUNCTION sp_name ( [func_parameter[,...]] ) RETURNS TYPE [ characteristic... ] routine_body
sp_name 参数是存储函数的名称； func_parameter 表示存储函数的参数列表； RETURNS type 指定返回值的类型； characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的；routine_body 参数是SQL 代码的内容，可以用BEGIN...END 来标志SQL 代码的开始和结束； func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下： param_name type 其中，param_name 参数是存储函数的参数名称；type 参数指定存储函数的参数类型， 该类型可以是MySQL 数据库的任意数据类型； |
示例：
DELIMITER &&
CREATE FUNCTION func_book (bookId INT) RETURNS VARCHAR (50) BEGIN RETURN (SELECT bookname FROM t_book WHERE id = bookId) ; END &&
DELIMITER ; |
调用：
SELECT func_book(1); |
3、变量的使用
a) 定义变量
DECLARE var_name [,...] TYPE [ DEFAULT VALUE ] |
b) 为变量赋值
SET var_name = expr [,var_name=expr] ... |
SELECT col_name[,...] INTO var_name[,...] FROM table_name WHERE CONDITION |
4、游标的使用
查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。 |
a) 声明游标
DECLARE cursor_name CURSOR FOR select_statement ; |
b) 打开游标
OPEN cursor_name; |
c) 使用游标
FETCH cursor_name INTO var_name [,var_name ... ]； |
d) 关闭游标
CLOSE cursor_name； |
示例：
DELIMITER &&
CREATE PROCEDURE porc_user2 () BEGIN DECLARE uname, pwd VARCHAR (20) ; DECLARE cur_user CURSOR FOR SELECT username, PASSWORD FROM t_user2 ; OPEN cur_user ; FETCH cur_user INTO uname, pwd ; INSERT INTO t_user VALUES (NULL, uname, pwd) ; CLOSE cur_user ; END &&
DELIMITER ; |
调用：
CALL porc_user2(); |
5、流程控制的使用
存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用IF 语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和WHILE 语句来进行流程控制。 |
a) IF语句
IF search_condition THEN statement_list [ ELSEIF search_condition THEN statement_list ]... [ ELSE statement_list ] END IF |
示例：
DELIMITER &&
CREATE PROCEDURE porc_user3 (IN bookId INT) BEGIN SELECT COUNT(*) INTO @num FROM t_user WHERE id = bookId ; IF @num > 0 THEN UPDATE t_user SET username = 'java' WHERE id = bookId ; ELSE INSERT INTO t_user VALUES (NULL, 'newuser', 'newpwd') ; END IF ; END &&
DELIMITER ; |
b) CASE语句
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list ] END CASE |
c) LOOP, LEAVE语句
LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP 语句本身没有停止循环 的语句，必须是遇到LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下： [begin_label：]LOOP Statement_list END LOOP [ end_label ] LEAVE 语句主要用于跳出循环控制。语法形式如下： LEAVE label |
d) ITERATE语句
ITERATE 语句也是用来跳出循环的语句。但是，ITERATE 语句是跳出本次循环，然后直接进入下一次 循环。基本语法： ITERATE label ; |
e) REPEAT语句
REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT 语句的基本 语法形式如下： [ begin_label : ] REPEAT Statement_list UNTIL search_condition END REPEAT [ end_label ] |
f) WHILE语句
[ begin_label : ] WHILE search_condition DO Statement_list END WHILE [ end_label ] |
第三节：调用存储过程和函数
1、调用存储过程
CALL sp_name( [parameter[,...]] ) |
2、调用存储函数
fun_name( [parameter[,...]] ) |
第四节：查看存储过程和函数
1、SHOW STATUS语句查看存储过程和函数的状态
SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ] ; |
2、SHOW CREATE语句查看存储过程的函数的定义
SHOW CREATE { PROCEDURE | FUNCTION } sp_name ; |
3、从information_schema.Routines表中查看存储过程和函数的信息
第五节：修改存储过程和函数
ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ] characteristic : { CONTAINS SQL } NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT ‘string’
其中，sp_name 参数表示存储过程或函数的名称； characteristic 参数指定函数的特性。 CONTAINS SQL 表示子程序包含SQL 语句，但不包含读或写数据的语句； NO SQL 表示子程序中不包含SQL 语句； READS SQL DATA表示子程序中包含数据的语句； MODIFIES SQL DATA 表示子程序中包含写数据的语句。 SQL SECURITY{ DEFINER | INVODER } 指明谁有权限来执行。 DEFINER 表示只有定义者自己才能够执行； INVODER 表示调用者可以执行。 COMMENT ‘string’ 是注释信息。 |
第六节：删除存储过程和函数
DROP {PROCEDURE | FUNCTION } sp_name ; |
第十二章 数据备份与还原
第一节：数据备份
备份数据可以保证数据库的安全，数据库管理员需要定期的进行数据库备份。 |
1、使用mysqldump命令备份
mysqldump -u username -p dbname table1 table2 ... > BackupName.sql dbname参数表示数据库的名称。 table1和table2参数表示表的名称，没有该参数时将备份整个数据库。 BackupName.sql参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常以sql作为后缀。
示例： mysqldump -u root -p db_book > D:\backup\db_book.sql |
2、使用sqlyog图形工具备份
第二节：数据还原
1、使用mysql命令还原
mysql -u root -p [dbname] < backup.sql dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。 指定数据库名时，表示还原该数据库下的表。 不指定数据库名时，表示还原特定的一个数据库。而备份文件中有创建数据库的语句。 |
2、使用sqlyog图形工具还原
