EXISTS关键字表示存在，使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。如果内层查询语句查询到妈祖条件的记录，就返回一个真值，否则返回一个假值。当返回真值时，外层查询语句将进行查询；当返回假值时，外层查询不进行查询。

4）带ANY关键字的子查询

ANY 关键字表示满足其中任一条件即可。

例：SELECT * FROM computer_stu

WHERE score>=ANY

(SELECT score FROM scholarship);

5）带ALL关键字的子查询

ALL关键字表示满足所有条件。

6、合并查询结果

将多个SELECT语句的查询结果合并到一起。

SELECT 语句1

UNION|UNION ALL

SELECT 语句2

UNION|UNION ALL…

SELECT 语句n；

注：使用UNION，会将查询结果合并在一起，然后去除掉相同的记录；但UNION ALL会显示合并后的所有记录。

7、为表和字段取别名

表名 表的别名

属性名 [AS] 别名

8、使用正则表达式查询

属性名 REGEXP ‘匹配方式

^：匹配字符串开始的部分；

$：匹配子渡船结束的部分；

.：代表字符串中的任意一个字符，包括回车和换行；

[字符集合]：匹配“字符集合”中的任何一个字符；

[^字符集合]：匹配除了“字符集合”以外的任意一个字符；

S1|S2|S3：匹配S1、S2和S3中任意一个；

*：代表多个该符号之前的字符(0,1,2,3…)

+：代表多个该符号之前的字符（1,2,3…）

字符串{N}：字符串出现N次；

字符串{M,N}：字符串出现至少M次，至多N次。

九、插入、更新与删除数据

1、插入数据

1）为表的所有字段插入数据

①INSERT语句中不指定具体的字段名

INSERT INTO 表名VALUES(值1,值2,…,值n);

②INSERT语句中列出所有的字段

INSERT INTO 表名(属性1,属性2,…,属性n)

VALUES(值1,值2,…,值n);

2）为表的指定字段插入数据

INSERT INTO 表名(属性1,属性2,…,属性m)

VALUES(值1,值2,…,值m);

3）同时插入多条记录

INSERT INTO 表名[属性列表] VALUES

(取值列表1),

(取值列表2),

…

(取值列表n);

4）将查询结果插入到表中

INSERT INTO 表名1（属性列表1）

SELECT 属性列表2 FROM 表名2 WHERE 条件表达式；

注：属性列表1和属性列表2要能对应上。

2、更新数据

UPDATE 表名

SET 属性名1=取值1，属性名2=取值2，…，属性名n=取值n

WHERE 条件表达式；

3、删除数据

DELETE FROM 表名 [WHERE 条件表达式];

十、MySQL运算符

1、算术运算符；

2、比较运算符；

3、逻辑运算符；

4、位运算符；

5、运算符的优先级；

十一、MySQL函数

1、数学函数

2、字符串函数

3、日期和时间函数

4、条件判断函数

5、系统信息函数

6、加密函数

7、其它函数

十二、存储过程和函数

1、创建存储过程和函数

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当做一个整体存储在MySQL服务器中。

1）创建存储过程

CREATE PROCEDURE sp_name (proc_parameter[,…])

[characteristic…] routine_body

s_name:存储过程的名称；

proc_parameter:参数列表；

characteristic:特性；

routine_body:SQL代码内容，可以用BEGIN…END格式；

proc_parameter中的每个参数由3个部分组成，分别是输入输出类型、参数名称和参数类型。其形式如下：

[IN|OUT|INOUT] param_name type

其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入，也可以输出；param_name表示存储过程的参数名称；type参数指定存储过程的参数类型。

characteristic有多个取值：

LANGUAGE SQL:说明routine_body部分是由SQL语句组成；

[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{DEFINED|INVOKER}:指明谁有权限来执行。DEFINED表示只有定义者自己才能执行；INVOKER表示调用者可以执行。默认DEFINED。

COMMENT ‘string’：注释信息。

例：创建一个名为num_from_employee的存储过程。

CREATE PROCEDURE num_from_employee(IN emp_id INT,OUT count_num INT)

READS SQL DATA

BEGIN

SELECT COUNT(*) INTO count_num FROM employee WHERE d_id=emp_id;

END

2）创建存储函数

CREATE FUNCTION sp_name ([func_parameter[,…]])

RETURNS type

[characteristic…] routine_body

其中：

sp_name:存储函数的名称；

func_parameter:存储函数的参数列表；

RETURNS type:返回值的类型；

characteristic 同上；

func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，形式为：

param_name type

例：创建名为name_from_employee的存储函数。

CREATE FUNCTION name_from_employee(emp_id INT)

RETURNS VARCHAR(20)

BEGIN

RETURN (SELECT name FROM employee WHERE num=emp_id);

END

3）变量的使用

①定义变量

DECLARE var_name[,…] type [DEFAULT value];

例：定义变量my_sql,数据类型为INT，默认值10.

DECLARE my_sql INT DEFAULT 10;

②为变量赋值

SET var_name=expr[,var_name=expr]…;

例：SET my_sql=30;

SELECT col_name[,…] INTO var_name[,…]

FROM table_name WHERE condition;

例：SELECT d_id INTO my_sql FROM employee WHERE id=2;

4）定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题,并且在定义的同时可以定

义解决办法。

①定义条件

DECLARE condition_name CONDITION FOR condition_value;

Condition_value:

SQLSTATE [VALUE] sqlstate_value | mysql_error_code

例：定义“ERROR 1146(42S02)”这个错误，名称为can_not_find。可以有两种方式：

方式1：使用sqlstate_value

DECLARE can_not_find CONDITION FOR SQLSTATE ‘42S02’;

方式2：使用mysql_error_code

DECLARE can_not_find CONDITON FOR 1146;

②定义处理程序

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement;

handler_type:

CONTINUE|EXIT|UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value|condition_name|SQLSARNING

|NOT FOUND|SQLEXCEPTION|mysql_error_code

sp_ statement:

表示一些存储过程或函数的执行语句；

例：下面是定义处理程序的几种方式。

方式1：捕获sqlstate_value

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’ SET @info=’CAN NOT FIND’;

方式2：捕获mysql_error_code

DECLARE CONTINUE HANDLER FOR 1146 SET @info=’CAN NOT FIND’;

方式:3：先定义条件，然后调用

DECLARE can_not_find CONDITON FOR 1146;

DECLARE CONTINUE HANDLER FOR can_not_find SET @info=’CAN NOT FIND’;

方式4：使用SQLWARNING

DECLARE EXIT HANDLER FOR SQLWARNING SET @info=’ERROR’;

方式5：使用NOT FOUND

DECALRE EXIT HANDLER FOR NOT FOUND SET @info=’CAN NOT FIND’;

方式6：使用SQLEXCEPTION

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’ERROR’;

5）光标的使用

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记

录。

①声明光标

DECLARE crusor_name CURSOR FOR select_statement;

例：DECLARE cur_employee CURSOR FOR SELECT name,age FROM employee;

②打开光标

OPEN cursor_name;

③使用光标

FETCH cursor_name INTO var_name[,var_name…];

其中：

cursor_name:光标名称；

var_name:将cursor_name中SELECT语句查询出的信息存入该参数中，var_name必须在声明光标之前定义好。

④关闭光标

CLOSE cursor_name;

6）流程控制的使用

①IF语句

②CASE语句

③LOOP语句

④LEAVE语句

⑤ITERATE语句

⑥REPEAT语句

⑦WHILE语句

2、调用存储过程和函数

存储过程和函数都是存储在服务器端的SQL语句集合。要使用这些定义好的存储过程和函数，就必须通过调用的方式来实现。

存储过程通过CALL调用；存储函数的调用和内部函数的使用方式一样。执行存储过程和函数都需要有EXECUTE权限。

1）调用存储过程

CALL sp_name(parameter[,…]);

2）调用存储函数

SELECT sp_name ([func_parameter[,…]])；

3、查看存储过程和函数

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pattern’];

SHOW CREATE {PROCEDURE|FUNCTION} sp_name;

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=’sp_name’;

4、修改存储过程和函数

ALTER {PROCEDURE|FUNCTION} sp_name[characteristic…]

5、删除存储过程和函数

DROP {PROCEDURE|FUNCTION} sp_name;

MySQL数据库高级管理

十三、MySQL用户管理

MySQL用户主要包括普通用户和root用户，这两种用户的权限是不一样的。root用户是超级管理员，拥有所有的权限。root用户的权限包括创建用户、删除用户和修改普通用户的的密码等管理权限。而普通用户只拥有创建该用户时赋予它的权限。

1、权限表

安装MySQL时会自动安装一个名为mysql的数据库，这里面存储的都是权限表。这些权限表中最重要的是user表、db表和host表，除此之外还有tables_priv表、columns_priv表和proc_priv表等。

1）user表

user表示MySQL中最重要的一个权限表，user表有39个字段，这些字段大致可以分为4类，分别是用户列、权限列、安全列和资源控制列。

User表的权限是针对所有数据库。

2）db表和host表

db表存储了某个用户对一个数据库的权限，host表和db表差不多，但较少用。这两个表的字段大致分为两类，用户列和权限列。

Db表的权限是针对某一个用户。

3）tables_priv表和columus_priv表

tables_priv表可以对单个表进行权限设置，columus_priv表可以对单个数据列进行权限设置。

注：mysql中权限分配是按照user表、db表、tables_priv表和columus_priv表的顺序进行分配的，数据库系统中，先判断user表中的值是否为Y，如果是Y，就不需要检查后面的表，如果是N，则一次检查db表、tables_priv表和columus_priv表。

4）procs_priv表

procs_priv表可以对存储过程和存储函数进行权限设置。

2、账户管理

1）登录和退出MySQL服务器

Mysql –h hostname|hostIP –P port –u username –p DatabaseName –e “SQL语句”

参数：

-h：hostname主机名，hostIP主机IP；

-P：端口；

-u：用户名；

-p：提示密码；

DatabaseName:指定登录的数据库，默认是MySQL数据库；

-e：登录后执行的SQL语句，执行完后退出MySQL服务器。

退出服务器的方式为：EXIT或者QUIT或者\q。

2）新建普通用户

①用CREATE USER语句新建普通用户

CREATE USER user[IDENTIFIED BY [PASSWORD]’password’]

[,user[IDENTIFIED BY [PASSWORD]’password’]]…

user参数表示新建用户的账户，uer由用户名和主机名构成，IDENTIFIED BY关键字用来设置新用户的密码

例：创建新用户，用户名为test1，密码为test1，主机名为localhost。

CREATE USER ‘test1’@’localhost’ IDENTIFIED BY ‘test1’;

②用INSERT语句来创建普通用户

可以使用INSERT语句直接将用户信息添加到mysql.user表中，但必须拥有对mysql.user表的INSERT权限。基本语法：

INSERT INTO mysql.user(Host,User,Password)

VALUES(‘hostname’,’username’,PASSWORD(‘password’));

PASSWORD()函数是用来为密码加密的。

因为只设置了这3个字段，所以其他字段都为默认值，但如果没有默认值，则必须为没有默认值的字段设置初始值，通常，ssl_cipher、x509_issuer和x509_subject这3个字段没有默认值，因此必须为它们设置初始值。

例：INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject)

VALUES(‘localhost,’ test2’,PASSWORD(‘test2’),’’,’’,’’);

执行完INSERT命令后要用FLUSH命令来使用户生效，

FLUSH PRIVILEGES;

使用这个命令可以从mysql数据库中的user表中重新装载权限，但是执行FLUSH命令需要有RELOAD权限。

③GRANT语句新建普通用户

GRANT语句创建用户时可以为用户授权，但必须拥有对GRANT权限。

GRANT priv_type ON database.table

TO user[IDENTIFIED BY [PASSWORD] ‘password’]

[,user[IDENTIFIED BY [PASSWORD] ‘password’]]…

Priv_type：表示新用户的权限；

database.table：表示新用户的权限范围，即只能在指定的数据库和表上使用自己的权限；

user：由用户名和主机名构成；

UDENTIFIED BY：设置密码

例：创建test3用户，主机名为localhost，密码为test3，该用户对所有数据库的所有表都有SELECT权限。

GRANT SELECT ON *.* TO ‘test3’@’localhost’ IDENTIFIED BY ‘test3’;

*.*表示所有数据库下的所有表。

3）删除普通用户

①用DROP USER 语句删除普通用户

使用DROP USER语句来删除用户时，必须拥有DROP USER权限。

DROP USER user[,user]…

例：DROP USER ‘test2’@’localhost’;

②用DELETE 语句来删除普通用户

可以用DELETE语句将用户信息从mysql.user表中直接删除，但必须又有对mysql.user表的DELETE权限。

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;

执行完DELETE命令后要使用FLUSH命令来使用户生效。

FLUSH PRIVILEGES;

4）root用户修改自己的密码

①使用mysqladmin命令来修改root用户的密码

mysqladmin –u username –p password “new_password”

注：password为关键字，不是指旧密码，且new_password需要用双引号括起来。

②修改mysql数据库下的user表

UPDATE mysql.user SET Password=PASSWORD(“new_password”)

WHERE User=”root” AND Host=”localhost”;

然后使用FLUSH PRIVILEGES；加载权限。

③使用SET语句来修改root密码

SET PASSWORD=PASSWORD(“new_password”);

5）root用户修改普通用户的密码

①使用SET语句修改普通用户密码

SET PASSWORD FOR ‘username’@’hostname’=PASSWORD(“new_password”);

②修改mysql数据库下的user表

UPDATE mysql.user SET Password=PASSWORD(“new_password”)

WHERE User=”root” AND Host=”localhost”;

③用GRANT语句来修改普通用户的密码

GRANT priv_type ON database.table

TO user [IDENTIFIED BY [PASSWORD]’password’];

例：GRANT SELECT ON *.* TO ‘test3’@’localhost’ IDENTIFIED BY ‘mytest3’;

为用户test3设置新密码为mytest3，并使其对所有数据库的所有表拥有SELECT权限。

6）普通用户修改密码

SET PASSWORD=PASSWORD(‘new_password’);

7）root用户密码丢失的解决办法

步骤1：使用—skip-grant-tables选项启动MySQL服务

Skip-grant-tables选项将使MySQL服务器停止权限判断，任何用户都有访问数据库的权力。这个选项是跟在MySQL服务的命令的后面的。

Windows中，使用mysqld或者mysqld-nt来启动MySQL服务。也可以使用net start mysql命令来启动MySQL服务。

mysqld命令如下：

mysqld --skip-grant-tables

mysqld—nt命令如下：

mysqld-nt --skip-grant-tables

net start mysql命令如下：

net start mysql --skip-grant-tables

linux系统中，使用mysqld_safe来启动MySQL服务，也可以使用/etc/init.d/mysql来启动MySQL服务。

mysqld_safe命令如下：

mysqld_safe --skip-grant-tables user=mysql

使用/etc/init.d/mysql的执行语句如下：

/etc/init.d/mysql start –mysql --skip-grant-tables

启动MySQL服务后，就可以使用root用户登录了。

步骤2：登录root用户，设置新密码

通过上面方式登录时，可以不输入密码就登录root用户，登录以后，可以使用UPDATE语句修改密码，但不能用SET语句修改密码。

步骤3：加载权限表

修改完密码之后，必须用FLSUH PRIVILEGES语句加载权限表。

3、权限管理

权限管理主要是对登录到数据库的用户进行权限验证。

1）MySQL的各种权限

2）授权

GRANT priv_type [(column_list)] ON database.table

TO user [IDENTIFIED BY [PASSWORD] ‘password’]

[,user [IDENTIFIED BY [PASSWORD] ‘password’]]…

[WITH with_option[with_option]…]

其中：

priv_type：权限的类型；

column_list：权限作用的列；

user：用户名和主机名；

IDENTIFIED BY：设置密码；

With_option：有5个选项

GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；

MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行count次查询；

MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行count次更新；

MAX_CONNECTIONS_PER_HOUT count：设置每个小时可以建立count连接；

MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的count个连接数；

3）收回权限

REVOKE priv_type [(comumn_list)]…

ON database.table

FROM user[,user]…

收回全部权限的语法是：

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]…

4)查看权限

SELECT * FROM mysql.user;

SHOW GRANTS FOR ‘username’@’hostname’;

十四、数据备份与还原

1、数据备份

1)、使用mysqldump命令备份

mysqldump可以将数据库中的数据备份成一个文本文件。Mysqldump命令工作原理是：先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。这些CREATE语句和INSERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句创建表，使用其中的INSERT语句还原数据。

①备份一个数据库

mysqldump –u username –p dbname table1 table2…>BackupName.sql

其中：

dbname：数据库的名称；

table1…：表示dbname数据库中的表，如果没有指定表，将备份整个数据库；

BackupName.sql：表示备份文件的名称，文件名前面可以加上一个绝对路径；

例：使用root用户备份test数据库下的student表。

mysqldump –u root –p test student > C:\student.sql;

②备份多个数据库

mysqldump –u username –p –databases dbname1 dbname2… >BackupName.sql

例：使用root用户备份test数据库和mysql数据库。

mysqldump –u root -p -databases test mysql >C:\backup.sql;

③备份所有数据库

mysqldump –u username -p --all-databases >BackupName.sql

2）直接复制整个数据库目录

MySQL有一种最简单的备份方法，就是将MySQL中的数据库文件直接复制出来，这种方法最简单也最快。使用这种方法时，最好将服务器先停止，这样可以保证复制期间数据库中的数据不会发生变化。

这种方法虽然快速，但不是最好的备份方法，因为实际情况可能不允许停止MySQL服务器，而且，这种方法对InnoDB存储引擎的表不适用，对于MyISAM存储引擎的表，这样备份和还原很方便。但是，还原时最好是相同的版本的MySQL数据库，否则可能会存在文件类型不同的情况。

3）使用mysqlhotcopy工具快速备份

mysqlhotcopy工具是一个perl脚本，主要在linux系统下使用。mysqlhotcopy工具使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份。其工作原理是：现将需要进行备份的数据库加上一个读操作锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库中，最后把需要备份的数据库文件复制到目标目录。

[root@localhost~]#mysqlhotcopy [option] dbname1 dbname2… backupDir/

dbname1:需要备份的数据库的名字；

backupDir：备份的目录；

选项：

--help：用来查看mysqlhotcopy的帮助；

--allowold:

…….

mysqlhotcopy工具的工作原理是将数据库文件拷贝到目标目录，因此，它只能备份MyISAM类型的表，不能备份InnoDB类型的表。

2、数据还原

1）使用mysql命令

mysql –u root –p [dbname] <backup.sql;

2）直接复制到数据库目录下

3、数据库迁移

数据库迁移就是将数据库从一个系统移动到另一个系统上。

1）相同版本的MySQL数据库之间的迁移

相同版本的MySQL数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库的移动。

最常用和最安全的方式是使用mysqldump命令来备份数据库，然后使用mysql命令将备份文件还原到新的MySQL数据库中。这里可以将备份和迁移同时进行。

假设从一个名为host1的机器中备份出所有数据库，然后，将这些数据库迁移到名为host2的机器上，命令如下：

mysqldump –h host1 –u root –password=password1 –all-databases|

mysql –h host2 –u root –password=password2

其中：

“|”符号表示管道，其作用是将mysqldump备份的文件送给mysql命令；

“--password=password”是主机host1上root用户的密码。

2）不同版本的MySQL数据库之间的迁移

不同版本的MySQL数据库之间的进行数据迁移通常是MySQL升级的原因。高版本的数据库通常都会兼容低版本，因此可以从低版本迁移到高版本，对于MyISAM类型的表可以直接复制，但InnoDB类型的表不可以使用这种方法。通常是用mysqldump命令进行备份，然后还原到目标MySQL数据库中。

3）不同数据库之间的迁移

不同数据库之间的迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类型数据库。

4、表的导出和导入

MySQL数据库中的表可以导出成文本文件、XML文件或者HTML文件。相应的文件也可以导入到MySQL文件中。

1）用SELECT…INTO OUTFILE导出文本文件

SELECT [列名]FROM table [WHERE 语句]

INTO OUTFILE ‘目标文件’ [OPTION];

OPTION:

FIELDS TERMINATED BY ‘字符’：设置字符串为字段的分隔符，默认值是“\t“；

FIELDS ENCLOSED BY ‘字符’:设置字符来括上CHAR、VARCHAR和TEXT等字符型字段。默认情况下不适用任何字符；

FIELDS ESCAPED BY ‘字符’:设置转义字符，默认为’\’;

LINES STARTING BY ‘字符串’：设置每行开头的字符，默认情况下无任何字符；

LINES TERMINATED BY ‘字符串’：设置每行的结束符，默认为“\n“；

例：下面用SELECT…INTO OUTFILE语句来导出test数据库下的student表的记录。其中，字段之间用“、“隔开，字符型数据用双引号括起来，每条记录以”>”开头。

SELECT * FROM test.student INTO OUTFILE ‘C:/student1.txt’

FIELDS TERMINATED BY ‘\、’ OPTIONALLY ENCLOSED BY ‘\”’

LINES STARTING BY ‘\>’ TERMINATED BY ‘\r\n’;

Windows中“\r\n”表示回车换行。

2）用mysqldump命令导出文本文件

mysqldump –u root –pPassword –T 目标目录 dbname table [option]

其中：

Password:root用户密码，紧挨着-p；

option：

--fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是“\t”；

--fields-enclosed-by=字符：设置字符来括上字段的值；

--fields-optionally-enclosed-by=字符：设置字符括上CHAR、VARCHAR和TEXT等字符型字段；

fields-escaped-by=字符：设置转义字符；

--lines-terminated-by=字符串：设置每行的结束符；

这些选项必须以双引号括起来。

例：用mysqldump命令导出test数据库下student表的记录，其中，字段之间用“，”隔开，字符型数据用双引号括起来，命令如下：

mysqldump –u root –proot –T C:\ test student

“—fields-terminated-by=,” “fields-optionally-enclosed-by=””

mysqldump命令还可以导出xml格式的文件：

mysqldump –u root –pPassword –xml|-X dbname table >C:/name.xml

3)用mysql命令导出文本文件

mysql命令可以用来登录服务器，也可以用来备份文件，同时，还可以导出文本文件。其基本语法为：

mysql –u root –pPassword –e “SELECT语句” dbname >C:/name.txt

例：用mysql命令来导出test数据库下student表的记录。

mysql –u root –proot –e “SELECT * FROM student” test >C:/student2.txt;

4）用LOAD DATA INFILE方式导入文本文件

LOAD DATA [LOCAL] INFILE file INTO TABLE table [OPTION];

“LOCAL”实在本地计算机中查找文本文件时使用的；

”file”：指定了文本文件的路径和名称；

“table”:表的名称；

OPTION:

FIELDS TERMINATED BY ‘字符’：设置字符串为字段的分隔符，默认值是“\t“；

FIELDS ENCLOSED BY ‘字符’:设置字符来括上CHAR、VARCHAR和TEXT等字符型字段。默认情况下不适用任何字符；

FIELDS ESCAPED BY ‘字符’:设置转义字符，默认为’\’;

LINES STARTING BY ‘字符串’：设置每行开头的字符，默认情况下无任何字符；

LINES TERMINATED BY ‘字符串’：设置每行的结束符，默认为“\n“；

IGNORE n LINES:忽略文件的前n行记录；

（字段列表）：根据字段列表中的字段和顺序来加载记录；

SET column=expr:将指定的列column进行相应地转换后再加载，使用expr表达式来进行转换。

例：使用LOAD DATA INFILE 命令将student.txt中的记录导入到student中。

LOAD DATA INFILE ‘C:/student.txt’ INTO TABLE student

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’;

5）用mysqlimport命令导入文本文件

mysqlimport –u root –pPassword[--LOCAL] dbname file [OPTION]

option：

--fields-terminated-by=字符串：设置字符串为字段的分隔符，默认值是“\t”；

--fields-enclosed-by=字符：设置字符来括上字段的值；

--fields-optionally-enclosed-by=字符：设置字符括上CHAR、VARCHAR和TEXT等字符型字段；

fields-escaped-by=字符：设置转义字符；

--lines-terminated-by=字符串：设置每行的结束符；

--ignore-lines=n:表示忽略前n行；

十五、MySQL日志

1、日志简介

MySQL日志是记录MySQL数据库的日常操作和错误信息的文件。MySQL中，日志可以分为二进制日志、错误日志、通用查询日志和慢查询日志。

二进制日志：以二进制文件的形式记录了数据库中的操作，但不记录查询语句；

错误日志：记录MySQL服务器的启动、关闭和运行错误等信息；

通用查询日志：记录用户登录和记录查询的信息；

慢查询日志：记录执行时间超过指定时间的操作。

2、二进制日志

二进制日志主要用于记录数据库的变化情况。

1）启动和设置二进制日志

默认情况下，二进制日志是关闭的。通过my.cnf或者my.ini文件的log-bin选项可以开启二进制文件。将log-bin选项加入到my.cnf或者my.ini文件的[mysql]组中，形式如下：

#my.cnf(linux系统下)或者my.ini(windows系统下)

[mysqld]

log-bin[=DIR\[filename]]

其中：

DIR：指定二进制文件的存储路径；

filename:二进制文件的文件名，其形式为filename.number,number的形式为000001、000002；

2）查看二进制日志

要查看二进制日志，必须用mysqlbinlog命令。

mysqlbinlog filename.number

3）删除二进制日志

①删除所有二进制日志

RESET MASTER;

②根据编号来删除二进制日志

PURGE MASTER LOGS TO ‘filename.number’;

该语句将删除编号小于这个二进制日志的所有二进制日志。

③根据创建时间来删除二进制日志

PURGE MASTER LOGS TO ‘yyyy-mm-dd hh:MM:ss’;

该语句将删除指定时间之前创建的所有二进制日志。

4）使用二进制日志还原数据库

mysqlbinlog filename.number | mysql –u root -p

5）暂时停止二进制日志功能

SET SQL_LOG_BIN=0; //暂停

SET SQL_LOG_BIN=1; //开启

3、错误日志

1）启动和设置错误日志

错误日志功能是默认开启的，而且，错误日志无法被禁止。默认情况下，错误日志存储在MySQL数据库的数据文件夹下。错误日志文件通常名称为hostname.err。错误日志的存储位置可以通过log-err选项设置：

#my.cnf(linux系统下)或者my.ini(windows系统下)

[mysqld]

log-err=DIR/[filename]

2）查看错误日志

错误日志为文本文件，可以直接打开。

3）删除错误日志

mysqladmin –u root –p flush-logs

4、通用查询日志

1）启动和设置通用查询日志

默认情况下，通用查询日志是关闭的。

#my.cnf(linux系统下)或者my.ini(windows系统下)

[mysqld]

log =[DIR/[filename]]

2）查看通用查询日志

通用查询日志为文本文件，可以直接打开。

3）删除通用查询日志

mysqladmin –u root –p flush-logs

5、慢查询日志

1）启动和设置慢查询日志

默认情况下，慢查询日志是关闭的。

#my.cnf(linux系统下)或者my.ini(windows系统下)

[mysqld]

log-slow-queries =[DIR/[filename]]

long_query_time=n //默认为10s

2）查看慢查询日志

慢查询日志为文本文件，可以直接打开。

3）删除慢查询日志

mysqladmin –u root –p flush-logs

十六、性能优化

1、优化简介

数据库管理员可以使用SHOW STATUS语句查询MySQL数据库的性能。

SHOW STATUS LIKE ‘value’;

value参数：

Connections:连接MySQL服务器的次数；

Uptime：MySQL服务器上线时间；

Slow_queries:慢查询的次数；

Com_select:查询操作的次数；

Com_insert:插入操作的次数；

Com_update:更新操作的次数；

Com_delete:删除操作的次数；

2、优化查询

1）分析查询语句

EXPLAIN SELECT 语句；

DESC SELECT 语句；

2）索引对查询速度的影响

索引可以提高查询速度。

3）使用索引查询

①查询语句中使用LIKE关键字

在查询中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%”时，则索引不会被使用；如果“%”不是在第一个位置时，索引会被使用。

②查询语句中使用多列索引

多列索引时在表的多个字段上创建一个索引。只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

③查询语句中使用OR关键字

查询语句只有OR关键字是，如果OR前后的两个条件的列都是索引时，查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

4）优化子查询

子查询语句灵活，但执行效率不高，因为MySQL需要为内层查询结果建立临时表，然后外层语句在临时表中查询记录，查询完毕后，在撤销这些临时表。在MySQL中，可以使用连接查询代替子查询，连接查询不需要临时表，速度要快于子查询。

3、优化数据库结构

1）将字段很多的表分解成多个表

2）增加中间表

3）增加冗余字段

4）优化插入记录的速度

①禁用索引

插入前禁用索引，插入后开启索引。

禁用：

ALTER TABLE 表名 DISABLE KEYS;

重新开启：

ALTER TABLE 表名 ENABLE KEYS;

②禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种校验会降低插入记录的速度，可以再插入记录前禁用唯一性检查，插入完毕后再开启。

禁用：

SET UNIQUE CHECKS=0;

重新开启：

SET UNIQUE CHECKS=1;

③优化INSERT语句

一次插入多条记录要多次将记录一条一条地插入。

INSERT INTO employee VALUES

(…),

(…);

要快于

INSERT INTO employee VALUES(…);

5）分析表、检查表和优化表

①分析表

分析表主要作用是分析关键字的分布。

ANALYZE TABLE 表名1 [,表名2…];

②检查表

检查表主要作用是检查表是否存在错误。

CHECK TABLE表名1 [,表名2…] [option];

Option参数：

QUICK、FAST、CHANGED、MEDIUM和EXTENED.

③优化表

优化表主要作用是消除删除或者更新造成的空间浪费。

OPTIMIZE TABLE语句只能优化表中VARCHAR、BLOB或TEXT类型字段。

OPTIMIZE TABLE 表名1[,表名2…];

通过优化，可以消除删除和更新造成的磁盘碎片，从而减少空间的浪费。

4、优化MySQL服务器

1）优化服务器硬件

2）优化MySQL的参数

posted on 2012-07-18 13:30 绝影王阅读(330) 评论(0) 收藏举报

刷新页面返回顶部

导航