《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];