《MySQL数据库应用从入门到精通》
第1章 数据库概述
1.1基本概念
1.1.1数据库技术发展阶段
人工管理阶段——>文件系统阶段——>数据库系统阶段
1.1.2涉及的概念
数据库（DB）、数据库管理系统（DBMS）、数据库系统（DBS）
1.1.3经历的阶段
层次数据库和网状数据库（IDS）——>关系数据库（Oracle、DB2、SQL Server、MySQL、Sybase等）——>后关系数据库（面向对象数据库技术（ORDBMS）、结构化数据库技术（NOSQL））
1.1.4提供的功能：
1、数据定义语言（DDL）——>定义各种对象，完整性约束和保密限制等约束等
2、数据操作语言（DML）——>检索（查询）和更新（插入、删除和更新）
3、数据控制语言（DCL）——>数据完整性控制、数据安全性控制和数据库的恢复等
1.2 MySQL数据库管理系统
1.2.1MySQL概述
免费、开源、小型、关系型
1.2.2常用数据库管理系统
Oracle、DB2、SQL Server、PostgreSQL等
第2章 MySQL安装和配置
1、4个比较重要的文件夹：bin、include、lib、share
2、my.ini配置文件：如果修改了配置文件，则必须重新启动MySQL服务，修改的内容才能生效
3、启动和关闭MySQL服务
net start // 查看已启动的服务项
net stop MySQL //停止MySQL服务
net start MySQL //启动MySQL服务
第3章 MySQL数据库基本操作
3.1数据库和数据库对象
1、系统数据库：information_schema、performance_schema、mysql、test
2、用户数据库
3.2创建数据库
CREATE DATABASE database_name
3.3查看和选择数据库
//查看数据库
SHOW DATABASES;
//选择数据库
USE database_name;
3.4删除数据库
DROP DATABASE database_name;
第4章 MySQL数据库中存储引擎和数据类型
4.1认识存储引擎
// 查看所支持的存储引擎
SHOW ENGINES;
SHOW ENGINES \g
SHOW ENGINES \G
SHOW VARIABLES LIKE \’have%\’;
// 查询默认存储引擎
SHOW VARIABLES LIKE \’storage_engine%\’;
4.2数据类型
4.2.1整数类型
TINYINT—> (1)、SMALLINT—> (2)、MEDIUMINT—> (3)、INT和INTEGER—> (4)、BIGINT—> (8)
4.2.2浮点数类型、定点数类型和位类型
FLOAT—> (4)、DOUBLE—> (8)
DEC(M,D)和DECIMAL(M,D) —> (M+2)
BIT(M) —>(1-8)
4.2.3日期和时间类型
DATE—> (4)、DATETIME—> (8)、TIMESTAMP—> (4)、TIME—> (3)、YEAR—> (1)
4.2.4字符串类型
CHAR(M) —> (M)、VARCHAR(M) —> (M)
TINYTEXT—> (0-255)、TEXT—> (0-65535)、MEDIUMTEXT、LONGTEXT
BINARY(M) —> (M)、VAR BINARY(M) —> (M)
TINYBLOB—> (0-255)、BLOB—> (0-2^16)、MEDIUMBLOB(0-2^24)、LONGBLOB(0-2^32)
第5章 表的操作
5.1表的基本概念
列（Column）、索引（Index）、触发器（Trigger）
5.2创建表
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
..…..
属性名 数据类型
) ;
5.3查看表结构
//查看表定义
DESCRIBE table_name;
DESC table_name;
//查看表详细定义
SHOW CREATE TABLE table_name \G
5.4删除表
DROP TABLE table_name;
5.5修改表
5.5.1修改表名
ALTER TABLE old_table_name
RENAME [TO] new_table_name;
5.5.2增加字段
//在表的最后一个位置增加字段
ALTER TABLE table_name
ADD 属性名 数据类型;
//在表的第一个位置增加字段
ALTER TABLE table_name
ADD 属性名 数据类型 FIRST;
//在表的指定字段之后增加字段
ALTER TABLE table_name
ADD 属性名 数据类型
AFTER 属性名;
5.5.3删除字段
ALTER TABLE table_name
DROP 属性名;
5.5.4修改字段
//修改字段的数据类型
ALTER TABLE table_name
MODIFY 属性名 数据类型;
//修改字段的名字
ALTER TABLE table_name
CHANGE 旧属性名 新属性名 旧数据类型;
//同时修改字段的名字和属性
ALTER TABLE table_name
CHANGE 旧属性名 新属性名 新数据类型;
//修改字段的顺序
ALTER TABLE table_name
MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2;
5.7操作表的约束
5.7.1MySQL支持的完整性约束
NOT NULL(NK)、DEFAULT、UNIQUE KEY(UK)、PRIMARY KEY(PK)、AUTO_INCREAMENT、FOREIGE KEY(FK)
//多字段约束
CONSTRAINT 约束名 UNIQUE|PRIMARY KEY|…(属性名，属性名……)
//外键约束
CONSTRAINT 约束名 FOREIGE KEY (属性名1)
REFERENCES 表名 (属性名2)
第6章 索引的操作
6.1 为什么使用索引
根据索引的存储类型：B型树索引（BTREE）和哈希索引（HASH）
MySQL支持6种索引：普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。
6.2创建和查看索引
6.2.1创建和查看普通索引
//创建表时创建普通索引
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
……
属性名 数据类型 ,
INDEX|KEY 【索引名】(属性名1 【(长度)】【ASC|DESC】)
) ;
//在已经存在的表上创建普通索引
CREATE INDEX 索引名
ON 表名(属性名【(长度)】【ASC|DESC】)
//通过ALTER TABLE创建普通索引
ALTER TABLE table_name
ADD INDEX|KEY 索引名(属性名【(长度)】【ASC|DESC】)
6.2.2创建和查看唯一索引
UNIQUE INDEX|KEY 索引名(属性名【(长度)】【ASC|DESC】)
CREATE UNIQUE INDEX 索引名
ON 表名(属性名【(长度)】【ASC|DESC】)
ALTER TABLE table_name
ADD UNIQUE INDEX|KEY 索引名(属性名【(长度)】【ASC|DESC】)
6.2.3创建和查看全文索引
只能在存储引擎为MyISAM的数据库表上创建全文索引
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
……
属性名 数据类型 ,
FUNNTEXT INDEX|KEY 索引名(属性名【(长度)】【ASC|DESC】)
) ENGINE=MyISAM;
CREATE FUNNTEXT INDEX 索引名
ON 表名(属性名【(长度)】【ASC|DESC】)
ALTER TABLE table_name
ADD FUNNTEXT INDEX|KEY 索引名(属性名【(长度)】【ASC|DESC】)
6.2.4创建和查看多列索引
6.3 删除索引
DROP INDEX index_name
ON table_name;
第7章 视图的操作
7.1为什么使用视图
1、对视图内容的更新（添加、删除和修改）直接影响基本表。
2、当视图来自多个基本表时，不允许添加和删除数据。
7.2创建视图
CREATE VIEW view_name
AS 查询语句;
7.3查看视图
//查看视图名
USE view;
SHOW TABLES;
//查看视图详细信息
SHOW TABLE STATUS 【FROM db_name】【LIKE \’pattern\’】
例：SHOW TABLE STATUS FROM view \G
//查看视图定义信息
SHOW CREATE VIEW view_name \G
//查看视图设计信息
DESCRIBE|DESC view_name;
7.4 删除视图
DROP VIEW view_name 【,view_name】…
7.5修改视图
CREATE OR REPLACE VIEW view_name
AS 查询语句;
ALTER VIEW view_name
AS 查询语句;
7.6利用视图操作基本表
第8章 触发器的操作
8.1为什么使用触发器
触发器（TRIGGER）
8.2创建触发器
8.2.1创建有一条执行语句的触发器
CREATE TRIGGER trigger_name
BEFORE|AFTER trigger_EVENT
ON table_name FOR EACH ROW trigger_STMT
8.2.2创建包含多条执行语句的触发器
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE|AFTER trigger_EVENT
ON table_name FOR EACH ROW
BEGIN
trigger_STMT
END
$$
DELIMITER ;
8.3查看触发器
SHOW TRIGGERS \G
//通过查看系统表查看触发器
USE information_schema;
SELECT * FROM triggers \G
8.4删除触发器
DROP TRIGGER trigger_name;
第9章 数据的操作
9.1插入数据记录
//插入完整数据记录
INSERT INTO table_name(field1,field2,…)
VALUES(value1,value2,…)
//插入数据记录一部分
//插入多条数据记录
//插入查询结果
9.2更新数据记录
//更新特定数据记录
UPDATE table_name
SET field1=value1,
Field2=value2,
……
WHERE CONDITION;
9.3删除数据记录
DELECT FROM table_name
WHERE CONDITION;
第10章 单表查询数据记录
10.1简单数据记录查询
SELECT field1, field2, ……, fieldn
FROM table_name
//避免重复查询记录
SELECT DISYINCT field1, field2, ……, fieldn
FROM table_name
10.2条件数据记录查询
SELECT field1, field2, ……, fieldn
FROM table_name
WHERE CONDITION
关键字：(NOT) BETWEEN AND、(NOT) IS NULL、(NOT) IN、(NOT) LIKE\’_\’、\’%
10.3排序数据记录查询
SELECT field1, field2, ……, fieldn
FROM table_name
WHERE CONDITION
ORDER BY fieldm1 [SAC|DESC] [,fieldm2 [SAC|DESC], ]
10.4限制数据记录查询数量
SELECT field1, field2, ……, fieldn
FROM table_name
WHERE CONDITION
LIMIT OFFSET_START, ROW_COUNT
OFFSET_START: 数据记录的起始偏移量（可不指定，默认为0）
ROW_COUNT: 显示的行数
10.5统计函数和分组数据记录查询
COUNT()、AVG()、SUM()、MAX()、MIN()
如果所操作的表中没有任何数据记录，则COUNT()函数会返回数据0，而其它函数则会返回NULL
//简单分组查询（显示每组中的一条数据记录）
SELECT function()
FROM table_name
WHERE CONDITION
GROUP BY field;
//实现统计功能分组查询：GROUP_CONTACT() 显示每个分组中的指定字段值
SELECT GROUP_CONTACT(field)
FROM table_name
WHERE CONDITION
GROUP BY field;
//实现多个字段分组查询
SELECT GROUP_CONTACT(field), function(field)
FROM table_name
WHERE CONDITION
GROUP BY field1, field2……, fieldn ;
//实现HAVING子句限定分组查询
SELECT function(field)
FROM table_name
WHERE CONDITION
GROUP BY field1, field2……, fieldn ;
HAVING CONDITION;
第11章 多表数据记录查询
11.1关系数据操作
并、笛卡尔积、内连接（自然连接、等值连接、不等连接）、外连接（左外连接、右外连接、全外连接）
11.2内连接查询
SELECT field1, field2, ……, fieldn
FROM join_tablename1 INNER JOIN join_tablename2【INNER JOIN join_tablename】
ON join_condition
11.3外连接查询
SELECT field1, field2, ……, fieldn
FROM join_tablename1 LEFT|RIGHT|FULL [OUTER] JOIN join_tablename2
ON join_condition
11.4合并查询数据记录
UNION、UNION ALL
11.5子查询
//带有关键字ANY的子查询：
=ANY
>ANY (>=ANY)
比子查询中返回数据记录中最小的还要大于（大于等于）数据记录
<ANY (<=ANY) 比子查询中返回数据记录中最大的还要小于（小于等于）数据记录
//带有关键字ANY的子查询：
=ALL
>ALL (>=ALL) 比子查询中返回数据记录中最大的还要大于（大于等于）数据记录
<ALL (<=ALL) 比子查询中返回数据记录中最小的还要小于（小于等于）数据记录
第12章 使用MySQL运算符
12.1为什么要使用运算符
12.2使用算术运算符
+、-、*、/(DIV)、%(MOD)
12.3使用比较运算符
>、<、=(<=>)、!=(<>)、>=、<=、BETWEEN AND、IS NULL、IN、LIKE、REGEXP (正则表达式匹配)
12.4使用逻辑运算符
AND(&&)、OR(||)、NOT(！)、XOR
12.5使用位运算符
&、|、-、^、<<、>>
第13章 使用MySQL常用函数
13.1使用字符串函数
13.1.1合并字符串函数：CONCAT(S1,S2,…,SN)和CONCAT_WS(sep, S1,S2,…,SN)
13.1.2比较字符串大小函数：STRCMP(str1,str2)
13.1.3获取字符串长度函数：LENGTH() 和字符数函数：CHAR_LENGTH()
13.1.4字母大小写转换：UPPER()和LOWER()
UCASE()和LCASE()
13.1.5查找字符串：
FIND_IN_SET(str,str1)和FIELD(str,str1, str2,str3)
LOCATE(str,str1)、POSITION(str IN str1)、INSTR(str,str1)
ELT(n,str1,str2)
MAKE_SET(num,str1,str2…,strn)
LEFT(str,num)和RIGHT(str,num)、SUBSTRING(str,num,len)和MID(str,num,len)
LTRIM(str)、RIGHT(str)、TRIM(str)
INSERT(str,pos,len,newstr)、REPLACE(str,substr,newstr)
13.2使用数值函数
ABS(x)、CEIL(x) 、FLOOR(x)、MOD(x)、RAND()、ROUND(x,y)、TRUNCATE(x,y)、
13.3使用日期和时间函数
CURDATE()、CURTIME()、NOW()、UNIX_TIMESTAMP(date)、FROM_UNIXTIME()、
WEEK(date)、YEAR(date)、HOUR(time)、MINUTE(time)、MONTHNAME(date)
13.4使用系统信息函数
VERSION()、DATABASE()、USER()、LAST_INSERT_ID()
第14章 存储过程和函数的操作
14.1为什么使用存储过程和函数
简单、高性能
14.2创建存储过程和函数
//创建存储过程语法形式
CREATE PROCEDURE procedure_name([procedure_parameter[,…]])
[charaxteristic…] routine_body
//创建函数语法形式
CREATE FUNCTION function_name([function_parameter[,…]])
[charaxteristic…] routine_body
14.3关于存储过程和函数的表达式
14.3.1操作变量
//声明变量
DECLARE var_name [,…] type [DEFAULT value]
//赋值变量
SET var_name=expr[,…]
//通过”SELECT……INTO”实现
SELECT field_name[,…] INTO var_name[,…]
FROM table_name
WHERE condition
例：
DECLARE employee_sal INT DEFAULT 1000;
SET employee_sal=3500;
或者：
SELECT sal INTO employee_sal
FROM t_employee
WHERE empno=7565;
14.3.2操作条件
定义条件、定义处理程序
14.3.3使用光标
//声明光标
DECLARE cursor_name CURSOR FOR select_statement;
//打开光标
OPEN cursor_name;
//使用光标
FETCH cursor_name INTO var_name [,var_name]…
//关闭光标
CLOSE cursor_name;
14.3.4使用流程控制
条件控制语句：IF、CASE
循环控制语句：LOOP、WHILE、REPEAT（退出循环体：LEAVE）
14.4查看存储过程和函数
SHOW PROCEDURE STATUS [LIKE \’pattern\’ ] \G
SHOW FUNCTION STATUS [LIKE \’pattern\’ ] \G
//通过查看系统表information_schema_routines实现查看存储过程和函数的信息
USE information_schema;
SELECT * FROM routines \G
//SHOW CREATE语句
SHOW CREATE PROCEDURE proce_name \G
SHOW CREATE FUNCTION func_name \G
14.5修改存储过程和函数
ALTER PROCEDURE procedure_name
[characteristic…]
ALTER FUNCTION function_name
[characteristic…]
14.6删除存储过程和函数
DROP PROCEDURE proce_name;
DROP FUNCTION func_name;
第15章 MySQL安全性机制
15.1MySQL软件所提供的权限
15.1.1系统表mysql.user
该表有39个字段，可分为4类：用户字段、权限字段、安全字段和资源控制手段
15.1.2系统表mysql.db和mysql.host
可分为2类：用户字段和权限字段
15.1.3其它权限表
mysql.tables_priv、mysql.columns_priv、procs_priv
15.2MySQL所提供的用户机制
15.2.1登陆和退出
mysql -h hostname|hostIP -p port -u usename -p DatabaseName -e “SQL语句”;
EXIT|QUIT
15.2.2创建普通用户账户
1、执行”CREATE USER”语句来创建用户账户
CREATE USER username[IDENTIFIED BY [PASSWORD] \’password\’]
[ , username[IDENTIFIED BY [PASSWORD] \’password\’]]
……
[ , username[IDENTIFIED BY [PASSWORD] \’password\’]]
2、执行INSERT语句创建用户
INSERT INTO user(Host, User, Password) Values(\’hostname\’, \’username\’, PSAAWORD(\’password\’));
FLUSH PRIVILEGES; //使新建的用户账号生效
3、执行GRANT语句创建用户
GRANT priv_type ON databasename.tablename
TO usename[IDENTIFIED BY [PASSWORD] \’password\’]
[ , username[IDENTIFIED BY [PASSWORD] \’password\’]]
……
[ , username[IDENTIFIED BY [PASSWORD] \’password\’]]
15.2.3利用拥有超级权限用户root修改用户账号密码
1、通过mysqladmin命令修改root用户密码
mysqladmin -u usename -p password “new_password”
2、通过SET命令修改root用户密码
SET PASSWORD=PASSWORD(“new_password”)
3、更新系统表mysql.user数据记录修改root用户密码
UPDATE user SET password=PASSWORD(“new_passwprd”)
WHERE user=”root” AND host=”localhost”;
15.2.4利用root用户修改普通用户账号密码
GRANT SELECT,CREATE.DROP ON *.*
TO \’qian\’@\’localhost\’ IDENTIFIED BY \’new_passwprd\’
WITH GRANT OPTION;
15.2.5删除普通用户账号
DROP USER user1 [,user2]…
//通过系统表mysql.user删除
DELETE FROM user
WHERE user=”cjgong” AND host=”localhost”;
15.3权限管理
GRANT priv_type ON database.table
TO user [IDENTIFIED BY [PASSWORD] \’passwprd\’]
[, user [IDENTIFIED BY [PASSWORD] \’passwprd\’]]
……
[WITH GRANT OPTION…..]
//查看用户所拥有权限
SHOW GRANTS FOR user
//收回用户所拥有权限
REVOKE priv_type ON database.table
TO user [IDENTIFIED BY [PASSWORD] \’passwprd\’]
[, user [IDENTIFIED BY [PASSWORD] \’passwprd\’]]
……
第16章 MySQL日志管理
16.1MySQL所支持的日志
二进制日志、错误日志、查询日志
第17章 MySQL数据库维护和性能提高
17.1MySQL数据库维护
17.1.1通过复制数据文件实现数据备份
只适合存储引擎为MyISAM的表
17.1.2通过命令mysqldump实现数据备份
//备份一个数据库
mysqldump -u username -p dbname
table1 table2…tablen
>backupname.sql
//备份多个数据库
mysqldump -u username -p –databases
dbname1 dbname2…dbnamen
>backupname.sql
//备份所有数据库
mysqldump -u username -p –all -databases
>backupname.sql
17.1.3通过复制数据文件实现数据还原
17.1.4通过命令MySQL实现数据还原
mysqldump -u username -p [dbname] < backupname.sql
17.1.5实现数据库中表导出成文本文件
//通过SELECT…INTO OUTFILE
SELECT [file_name] FROM table_name
[WHERE contion]
INTO OUTFILE \’FILE_NAME\’
[OPTION]
//通过mysqldump命令
mysqldump -u root -pPassword -T file_dorectory dbname table_name [option]
//通过mysql命令
mysql -u root -pPassword -e “SELECT [file_name] FROM table_name” dbname > file_name
17.1.6实现文本文件导入到数据库表
//执行”LOAD DATA INFILE”命令
LOAD DATA [LOCAL] INFILE \’file_name\’ INTO TABLE table_name [OPTION];
//执行mysqlimport命令
mysqlimport -u root -pPassword [–LOCAL] dbname file_name [OPTION];