JAVA执行mysql脚本和mysql相关命令(alter table示例/多表更新/Insert Into Select/多表查询) 及ibatis模糊查询(concat用法)

最新推荐文章于 2024-05-16 00:05:47 发布

mike_caoyong

最新推荐文章于 2024-05-16 00:05:47 发布

阅读量4.9k

点赞数

分类专栏： mysql java

java 同时被 2 个专栏收录

46 篇文章 1 订阅

订阅专栏

mysql

10 篇文章 0 订阅

订阅专栏

MYSQL常用命令列表

1、系统管理

mysql -h主机地址 -u用户名 -p密码 -P端口
连接MYSQL（在mysql/bin）

exit
退出MYSQL命令

mysqladmin -u用户名 -p旧密码 password新密码
修改密码（在mysql/bin）

grantselect[insert][,update][,delete]on数据库.*to用户名@localhost

（"%"，表示任何主机）identifiedby "密码"
增加用户

mysqldump –u root –p opt数据库名>备份文件名
备份数据库（在mysql/bin）

mysql –u root –p < batch file （例如备份文件名）
使用批处理

mysql.server start
启动服务器

mysql.server stop
停止服务器

msql.server log

2、查询命令

select version()
查询版本号

select current_date
查询当前日期

3、显示命令

show databases
显示数据库列表

show tables 显示库中的数据表

describe 表名显示数据表的结构

select * from 表名显示表中的记录

select what_to_select from which table

[whereconditions_to_satisfy and (or) where conditions_to_satisfy]

从一个表中检索数据『满足条件』

select 字段1,字段2,… from 表名显示特定列的值

select * from 表名 order by 字段名排序行

select 字段1,包含字段2的运算式as 新字段 from 表名字段值运算操

作

select 字段1 is null（is not null）空值操作

Select*from表名where字段名like（not like） “ 字符”
注：允许使用“_”匹配任何单个字符，而“％” 匹配任意数目字符模

式匹配

Select * from表名where字段名regexp（not regexp）或者rlike（not

rlike） “.”匹配任何单个的字符一个字符类[…]匹配方框内任何字符

。例如[a]，[asd]，[az] 匹配任何小写字母，[09] 匹配任何数

字。 “*”匹配零个或者多个在它前面的东西。正则表达式区分大小

写[aA] 。如果它出现在被测试值的任何地方，模式都匹配。定位

，在模式开始处用“^”，结尾处用“$”，例如“^b”
扩展正则表达式

Select count(*) from 表名
Select 字段名，count(*) from 表名 group by 字段名行计数

4、编辑命令

use database 库名
使用的数据库

create database 库名
创建数据库

create table 表名
在数据库中创建表

insert into表名values (“data”,”data”)
向表中添加记录

Load data infile “/path/filename” intotable 表名
从文件中向表添加数据，文件每行包括一条记录，用定位符（tab

）把值分开。

drop database 库名
删除数据库

drop table 表名
删除数据库中的表

delete from表名where
删除数据库表中的记录

Update表名set字段＝”值” wherewhereconditions_to_satisfy
更新数据库表中记录的值

Mysql数据库是一个多用户，多线程的关系型数据库，是一个客户机

/服务器结构的应用程序。它是对个人用户和商业用户是免费的.

Mysql数据库具有以下优点：
1.同时访问数据库的用户的数量不受限制
2.可以保存超过5千万条的记录
3.是目前市场上现有数据库产品中运行速度最快的数据库系统
4.用户权限设置简单、有效。

Mysql数据库常用命令：
启动Mysql数据库
C:/>cd Mysql5.0/bin
C:/Mysql5.0/bin>mysqld –install 安装Mysql服务
C:/Mysql5.0/bin>net start mysql 启动Mysql服务
请求的服务已经启动。

连接mysql
用户需要提供Mysql的用户名和密码来连接服务器，如果服务器不是

在本机，则还需要一个主机名或IP来指定服务器的位置。
C:/Mysql5.0/bin>mysql -h localhost -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 6 to server version: 5.0.18-nt
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql>

使用一条简单的查询语句
mysql> select version(),current_date;
mysql> select version();select now();

新建或删除一个数据库
Mysql>create database mydb;
Mysql> drop database mydb;

打开的数据库的命令
mysql> use mysql
Database changed

查看数据库的命令
mysql> show databases;

查看数据表的详细结构
mysql> desc func;

新建数据库
mysql> create database school;
Query OK, 1 row affected (0.00 sec)

新建表
mysql> create table user01(
-> id varchar(20) NOT NULL,
-> userName varchar(10) NOT NULL,
-> age int(11) default'0',
-> sex char(2) NOT NULL default'm',
-> PRIMARY KEY (id)
-> )TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql>desc

student;

插入和删除表中的数据
Create table student(stuName varchar(20),age varchar(20),id

varchar(20),set0 char(1));

插入
mysql> insert into student(id,stuName) values('1','tomcat');
Query OK, 1 row affected (0.00 sec)

删除
mysql> delete from student where id='1';
Query OK, 1 row affected (0.01 sec)

删除表中所有数据
mysql> truncate table student;
Query OK, 1 row affected (0.01 sec)

删除表
mysql> create table temp(t varchar(1));
Query OK, 0 rows affected (0.00 sec)
mysql> drop table temp;
Query OK, 0 rows affected (0.00 sec)

创建新用户并给予权限
mysql> grant all privileges on *.* to dbuser@localhost identified

by '1234'
with grant option;

更改Mysql用户密码
c:/Mysql5.0/bin>mysqladmin -u root -p password 1234
Enter password: ****

备份数据库及表
我们用mysqldump命令来备份数据库
c:/mysql/bin/>mysqldump –u root –p 3306 mysql>d:/backup.sql
执行此语句将把mydb 备份到D盘的backup.sql文件中

备份多个数据库表
c:/mysql/bin/>mysqldump –u root –p 3306 school user01 user

>d:/backup.sql
此句的意思是把school库中的user01表和user表的内容和表的定义备

份到D盘backup.sql文件中。

备份所有的数据库
c:/myql/bin>mysqldump –u root –p 3306 –all –

database>d:backup.sql

还原Mysql数据库
c:/mysql/bin/mysql –u root –p 3306 school
还原其中的一个表

mysql> source d:/books.sql;
ERROR:
Unknown command '/b'.
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

退出Mysql连接
mysql>quit(exit)

关闭mysql服务
C:/mysql/bin>net mysql

1、启动MySQL服务器
实际上上篇已讲到如何启动MySQL。两种方法：一是用

winmysqladmin，如果机器启动时已自动运行，则可直接进入下一步

操作。二是在DOS方式下运行 d:mysqlbinmysqld

2、进入mysql交互操作界面
在DOS方式下，运行： d:mysqlbinmysql
出现: mysql 的提示符，此时已进入mysql的交互操作方式。
如果出现 "ERROR 2003: Can′t connect to MySQL server on ′

localhost′ (10061)“，
说明你的MySQL还没有启动。

3、退出MySQL操作界面
在mysql>提示符下输入quit可以随时退出交互操作界面：
mysql> quit
Bye
你也可以用control-D退出。

4、第一条命令
mysql> select version(),current_date();
+----------------+-----------------+

| version() | current_date() |

+----------------+-----------------+

| 3.23.25a-debug | 2001-05-17 |

+----------------+-----------------+

1 row in set (0.01 sec)

mysql>

此命令要求mysql服务器告诉你它的版本号和当前日期。尝试用不同

大小写操作上述命令，看结果如何。结果说明mysql命令的大小写结

果是一致的。

练习如下操作：

mysql>Select (20+5)*4;
mysql>Select (20+5)*4,sin(pi()/3);
mysql>Select (20+5)*4 AS Result,sin(pi()/3); (AS: 指定假名为

Result)

5、多行语句
一条命令可以分成多行输入，直到出现分号“；”为止：
bordercolorlight = "black" bordercolordark = "#FFFFFF"

align="center">
mysql> select
-> USER()
-> ,
-> now()
->;
+--------------------+---------------------+
| USER() | now() |
+--------------------+---------------------+
| ODBC@localhost | 2001-05-17 22:59:15 |
+--------------------+---------------------+
1 row in set (0.06 sec)
mysql>

注意中间的逗号和最后的分号的使用方法。

6、一行多命令
输入如下命令：

mysql> SELECT USER(); SELECT NOW();
+------------------+
| USER() |
+------------------+
| ODBC@localhost |
+------------------+
1 row in set (0.00 sec)

+---------------------+
| NOW() |
+---------------------+
| 2001-05-17 23:06:15 |
+---------------------+
1 row in set (0.00 sec)
mysql>

注意中间的分号，命令之间用分号隔开。

8、选择数据库并显示当前选择的数据库
mysql> USE mysql
Database changed
mysql>
(USE 和 QUIT 命令不需要分号结束。）
mysql> select database();
+---------------+
| database() |
+---------------+
| mysql |
+---------------+
1 row in set (0.00 sec)

9、显示当前数据库中存在的表
mysql> SHOW TABLES;

10、显示表(db)的内容
mysql>select * from db;

11、命令的取消

当命令输入错误而又无法改变（多行语句情形）时，只要在分号出

现前就可以用 c来取消该条命令

mysql> select
-> user()
-> c
mysql

12.mysql修改表(alter table)

我们在创建表的过程中难免会考虑不周，因此后期会修改表修改表需要用到alter table语句

修改表名复制代码代码如下:
mysql> alter table student rename person;
Query OK, 0 rows affected (0.03 sec)

这里的student是原名，person是修改过后的名字
用rename来重命名，也可以使用rename to

修改字段的数据类型复制代码代码如下:
mysql> alter table person modify name varchar(20);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

此处modify后面的name为字段名，我们将原来的varchar(25)改为varchar(20)

修改字段名复制代码代码如下:
mysql> alter table person change stu_name name varchar(25);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

这里stu_name是原名，name是新名
需要注意的是不管改不改数据类型，后面的数据类型都要写
如果不修改数据类型只需写成原来的数据类型即可
tips：我们同样可以使用change来达到modify的效果，只需在其后写一样的字段名

增加无完整性约束条件的字段复制代码代码如下:
mysql> alter table person add sex boolean;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

此处的sex后面只跟了数据类型，而没有完整性约束条件

增加有完整性约束条件的字段复制代码代码如下:
mysql> alter table person add age int not null;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

地处增加了一条age字段，接着在后面加上了not null完整性约束条件

在表头添加字段复制代码代码如下:
mysql> alter table person add num int primary key first;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

默认情况下添加字段都是添加到表尾，在添加语句后面加上first就能添加到表头

在指定位置添加字段复制代码代码如下:
mysql> alter table person add birth date after name;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

这里添加一条新字段放在name字段后面
tps：表中字段的排序对表不会有什么影响，不过更合理的排序能便于理解表

删除字段复制代码代码如下:
mysql> alter table person drop sex;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

和前面删除表或数据库一样，这里也需要用drop
不同的是，删除字段还要用alter table跟着表名

修改字段到第一个位置复制代码代码如下:

mysql> alter table person modify id int first;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

first在前面已经讲过，此处要注意的是字段后面要写数据类型

修改字段到指定位置复制代码代码如下:

mysql> alter table person modify name varchar(25) after id;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

我们把name字段放到了id后面，此处的varchar(25)要写全，varchar不行
建议操作以上步骤之前都先desc table

修改表的存储引擎复制代码代码如下:
mysql> alter table user rename person;
Query OK, 0 rows affected (0.05 sec)

这里先不具体讲各个存储引擎的特点，内容比较多
修改完之后别忘了使用show create table语句查看，第三节有写用法
tips：如果表中已存在很多数据，不要轻易修改存储引擎

增加表的外键复制代码代码如下:
mysql> alter table score add constraint fk foreign key(stu_id) references student(id);
Query OK, 10 rows affected (0.18 sec)
Records: 10 Duplicates: 0 Warnings: 0

这里只需使用add增加即可，后面的语法参见第四节中的外键设置

删除表的外键约束复制代码代码如下:
mysql> alter table student3 drop foreign key fk;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

由于基本的表结构描述无法显示外键，所以在进行此操作前最好使用show create table查看

表
这里的fk就是刚刚设置的外键
需要注意的是：如果想要删除有关联的表，那么必先删除外键
删除外键后，原先的key变成普通键

1、MySQL常用命令
create database name; 创建数据库
use databasename; 选择数据库
drop database name 直接删除数据库，不提醒
show tables; 显示表
describe tablename; 表的详细描述
select 中加上distinct去除重复字段
mysqladmin drop databasename 删除数据库前，有提示。
显示当前mysql版本和当前日期
select version(),current_date;

2、修改mysql中root的密码：
shell>mysql -u root -p
mysql> update user set password=password(”xueok654123″)

where user=’root’;
mysql> flush privileges //刷新数据库
mysql>use dbname；打开数据库：
mysql>show databases; 显示所有数据库
mysql>show tables; 显示数据库mysql中所有的表：先use mysql；

然后
mysql>describe user; 显示表mysql数据库中user表的列信息）；

3、grant
创建一个可以从任何地方连接服务器的一个完全的超级用户，但是

必须使用一个口令something做这个

mysql> grant all privileges on *.* to user@localhost identified by ’

something’ with
增加新用户
格式：grant select on 数据库.* to 用户名@登录主机 identified by “

密码”

GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED

BY ’something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’

something’ WITH GRANT OPTION;
删除授权：

mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;

创建一个用户custom在特定客户端it363.com登录，可访问特定数据

库fangchandb

mysql >grant select, insert, update, delete, create,drop on

fangchandb.* to custom@ it363.com identified by ‘ passwd’

重命名表:

mysql > alter table t1 rename t2;
4、mysqldump
备份数据库

shell> mysqldump -h host -u root -p dbname >dbname_backup.sql
恢复数据库

shell> mysqladmin -h myhost -u root -p create dbname
shell> mysqldump -h host -u root -p dbname <

dbname_backup.sql
如果只想卸出建表指令，则命令如下：

shell> mysqladmin -u root -p -d databasename > a.sql
如果只想卸出插入数据的sql命令，而不需要建表命令，则命令如下

：

shell> mysqladmin -u root -p -t databasename > a.sql
那么如果我只想要数据，而不想要什么sql命令时，应该如何操作呢

？

　　 mysqldump -T./ phptest driver
其中，只有指定了-T参数才可以卸出纯文本文件，表示卸出数据的

目录，./表示当前目录，即与mysqldump同一目录。如果不指定

driver 表，则将卸出整个数据库的数据。每个表会生成两个文件，

一个为.sql文件，包含建表执行。另一个为.txt文件，只包含数据，

且没有sql指令。
5、可将查询存储在一个文件中并告诉mysql从文件中读取查询而不

是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项

工作。例如，如果在文件my_file.sql 中存放有查
询，可如下执行这些查询：
例如，如果您想将建表语句提前写在sql.txt中:

mysql > mysql -h myhost -u root -p database < sql.txt

MySQL 开启与关闭远程访问

(1)通过MySQL用户去限制访问

权限系统目的:

MySQL基于安全考虑root账户一般只能本地访问，但是在开发过程

中可能需要打开root的远程访问权限，今天介绍的就是如何开启和

关闭Mysql远程访问

MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并

且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权

限。附加的功能包括有匿名的用户并对于MySQL特定的功能例如

LOAD DATA INFILE进行授权及管理操作的能力。

权限系统原理:

MySQL权限系统保证所有的用户只执行允许做的事情。当你连接

MySQL服务器时，你的身份由你从那儿连接的主机和你指定的用户

名来决定。连接后发出请求后，系统根据你的身份和你想做什么来

授予权限。

MySQL在认定身份中考虑你的主机名和用户名字，是因为几乎没有

原因假定一个给定的用户在因特网上属于同一个人。例如，从

office.com连接的用户joe不一定和从elsewhere.com连接的joe是同

一个人。MySQL通过允许你区分在不同的主机上碰巧有同样名字的

用户来处理它：你可以对joe从office.com进行的连接授与一个权限

集，而为joe从elsewhere.com的连接授予一个不同的权限集。

阶段1：服务器检查是否允许你连接。阶段2：假定你能连接，服务

器检查你发出的每个请求。看你是否有足够的权限实施它。例如，

如果你从数据库表中选择(select)行或从数据库删除表，服务器确定

你对表有SELECT权限或对数据库有DROP权限。如果连接时你的权

限被更改了(通过你和其它人)，这些更改不一定立即对你发出的下一

个语句生效。MySQL权限是保存在cache中，这个时候就你需要执行
flush privileges;

开启远程访问:

- 更新用户
use mysql;

update user set host = "%" where user = "root";

flush privileges;

- 添加用户
use mysql;
#可能不同MYSQL版本USER表字段不同，需要插入必填字段，可以

先插入空字符串
insert into user(host, user, password) values("%", "root",

password("yourpassword"))
grant all privileges on *.* to 'root'@'%' with grant option #赋予任

何主机访问数据库权限

flush privileges; //使以上命今生效

关闭远程访问:
use mysql;

update user set host = "localhost" where user = "root" and host=

"%";

flush privileges;

查看用户权限:
use information_schema;

select * from user_privileges;

查看当前mysql用户:
use mysql;

select user, host from user;

更新用户:
update mysql.user set password=password('新密码') where

User="phplamp" and Host="localhost";

flush privileges;

删除用户:
DELETE FROM user WHERE User="phplamp" and

Host="localhost";

flush privileges;

user host指定方法:
Host值可以是主机名或IP号，或’localhost’指出本地主机。
你可以在Host列值使用通配符字符“%”和“_”。
host值’%’匹配任何主机名，空Host值等价于’%’。它们的含义与LIKE

操作符的模式匹配操作相同。例如，’%’的Host值与所有主机名匹配

，而’%.mysql.com’匹配mysql.com域的所有主机。

ip地址例子:
192.0.0.0/255.0.0.0(192 A类网络的任何地址)
192.168.0.0/255.255.0.0(192.168 A类网络的任何地址)
192.168.1.0/255.255.255.0(192.168.1 C类网络的任何地址)
192.168.1.1(只有该IP)

mysql doc:

http://dev.mysql.com/doc/refman/5.1/zh/database-

administration.html
(2)通过IpTable

iptables是一款防火墙软件。它在Ubuntu系统中是默认安装的。通常

情况下，iptables随系统一起被安装，但没有对通信作任何限制，因

此防火墙并没有真正建立起来。

iptables帮助:
sudo iptables -h #下面全部使用root用户调用指令

查看iptables:
iptables -L

Chain INPUT (policy ACCEPT)
target prot opt source destination

Chain FORWARD (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

可以看到，上面规则都是空的

允许已建立的连接接收数据:
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j

开放指定的端口:

接下来，我们可以尝试开放 ssh 22端口，告诉iptables允许接受到所

有目标端口为22的tcp报文通过
iptables -A INPUT -p tcp -i eth0 --dport ssh -j ACCEPT

执行上面的命令，一条规则会被追加到INPUT规则表的末尾（-A表

示追加）。根据这条规则，对所有从接口eth0（-i指出对通过哪个接

口的报文运用此规则）接收到的目标端口为22的报文，iptables要执

行ACCEPT行动（-j指明当报文与规则相匹配时应采取的行动）。

开放80端口:
iptables -A INPUT -p tcp -i eth0 --dport 80 -j ACCEPT

这时，你再次去看看规则表中内容，你会发现有
iptables -L

Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
ACCEPT tcp -- anywhere anywhere tcp dpt:ssh
ACCEPT tcp -- anywhere anywhere tcp dpt:www

通过上述命令，我们已经代开了SSH和web服务的相应的端口，但由

于没有阻断任何通信，因此所有的报文都能通过，所以接下来，我

们就可以尝试阻断3306 mysql端口

阻断通讯:
iptables -A INPUT -p tcp -i eth0 --dport 3306 -j DROP

通过这样指令，就可以阻断任何访问3306报文，但是这样就有一个

问题，就是可能连我们自己信任的主机都无法访问mysql了，所以，

我们需要编辑下iptables，添加特定允许访问的主机
iptables -I INPUT 4 -p tcp -s ip_address -i eth0 --dport 3306 -j

好了，通过上面，我们把该指令插入到规则表里第四行，然后允许

特定ip访问3306端口

Logging记录:

如果希望被丢失的报文记录到syslog中，最简单的方法可以这样做:
iptables -I INPUT 5 -m limit --limit 5/min -j LOG --log-prefix

"iptables denied: " --log-level 7

保存设置:

机器重启后，iptables中的配置信息会被清空。您可以将这些配置保

存下来，让iptables在启动时自动加载，省得每次都得重新输入。

iptables-save和iptables-restore 是用来保存和恢复设置的。

先将防火墙规则保存到/etc/iptables.up.rules文件中
iptables-save > /etc/iptables.up.rules

然后修改脚本/etc/network/interfaces，使系统能自动应用这些规则

（最后一行是我们手工添加的）。
auto eth0
iface eth0 inet dhcp
pre-up iptables-restore < /etc/iptables.up.rules

当网络接口关闭后，您可以让iptables使用一套不同的规则集。
auto eth0
iface eth0 inet dhcp
pre-up iptables-restore < /etc/iptables.up.rules
post-down iptables-restore < /etc/iptables.down.rules

技巧(Tips):

大多数人并不需要经常改变他们的防火墙规则，因此只要根据前面

的介绍，建立起防火墙规则就可以了。但是如果您要经常修改防火

墙规则，以使其更加完善，那么您可能希望系统在每次重启前将防

火墙的设置保存下来。为此您可以在/etc/network/interfaces文件中

添加一行：
pre-up iptables-restore < /etc/iptables.up.rules
post-down iptables-save > /etc/iptables.up.rules

Mysql数据库的安全设置方法大全

MySQL 是一个真正的多用户、多线程SQL数据库服务器，它是一个

客户机/服务器结构的实现。MySQL是现在流行的关系数据库中其中

的一种，相比其它的数据库管理系统（DBMS）来说，MySQL具有小

巧、功能齐全、查询迅捷等优点。MySQL 主要目标是快速、健壮和

易用。目前，在大中型企业中已经得到了较好的运用，但是由于它

是多平台的数据库，不可避免的默认配置也是适合多种情况的需求

，因此需要用户需要在自定义的环境下对MySQL的使用进行加固。

假如软件本身有严重安全问题，即使安全配置做的更好，也没有用

。因此，要首先了解MySQL的版本。关于MySQL的版本，在MySQL

官方文档中是这么描述的：
MySQL 5.2是最新开发的发布系列，是将执行新功能的系列。不

久的将来可以使用Alpha发行，以便感兴趣的用户进行广泛的测试。
MySQL 5.1是当前稳定(产品质量)发布系列。只针对漏洞修复重

新发布；没有增加会影响稳定性的新功能。
MySQL 5.0是前一稳定(产品质量)发布系列。只针对严重漏洞修

复和安全修复重新发布；没有增加会影响该系列的重要功能。
MySQL 4.0和3.23是旧的稳定(产品质量)发布系列。该版本不再

使用，新的发布只用来修复特别严重的漏洞（以前的安全问题）。
Mysql开发组织不认为有完全的冻结版，因为任何版本均需要对漏洞

进行修复和其它修复。对于“某种程度的冻结”，他们是指他们可以

在产品发布中增加一些不会影响当前工作的小东西。当然，前一系

列的相关漏洞修复会移植到后面的系列。

根据官方的建议，至目前为止，推荐使用目前的稳定版本MySQL 5.1

。如果你正在运行一个老的系统并且想要升级，但是又不想冒险进

行非无缝升级，应该升级到最新版本中你正使用的相同的发布系列

（只有版本号的最后部分比你使用的新，例如5.0和5.1为同一系列）

。

一、MySQL安装

MySQL可以在redhat环境下进行RPM安装和debian下apt安装，但是

最新的包一般都是源码的形式，因此这里选择源码编译安装的方式

。
首先登陆官方网站http://www.gw005.com，下载最新的released版

本。
# tar zxf mysql-5.1.22-rc-linux-i686-glibc23.tar.gz
为mysql的运行建立mysql用户和mysql用户组
#groupadd mysql
# useradd -g mysql mysql
# ./configure –prefix=/usr/local/mysql
#make
#make install
# cp support-files/my-medium.cnf /etc/my.cf
# bin/mysql_install_db –user=mysql //用mysql生成初始数据库，出

现类似thank for using mysql 证明初始化数据库成功。
# chown -R root . //当前目录给root
# chown -R mysql var //var给mysql，这个很重要，也是安全起见
# chgrp -R mysql .
# bin/mysqld_safe –user=mysql &
#bin/mysql –u root
此时安全完毕，但是最重要是对MySQL进行安全配置，检查你的系

统，最基本要做到以下配置。

二、MySQL安全配置

数据库作为数据管理的平台，它的安全性首先由系统的内部安全和

网络安全两部分来决定。对于系统管理员来说，首先要保证系统本

身的安全，在安装MySQL数据库时，需要对基础环境进行较好的配

置。

1、修改root用户口令，删除空口令
缺省安装的MySQL的root用户是空密码的，为了安全起见，必须修

改为强密码，所谓的强密码，至少8位，由字母、数字和符号组成的

不规律密码。使用MySQL自带的命令mysaladmin修改root密码，同

时也可以登陆数据库，修改数据库mysql下的user表的字段内容，修

改方法如下所示：

# /usr/local/mysql/bin/mysqladmin -u root password “upassword”

//使用mysqladmin
#mysql> use mysql;
#mysql> update user set password=password(‘upassword’)

where user=’root’;
#mysql> flush privileges; //强制刷新内存授权表，否则用的还是在

内存缓冲的口令

2、删除默认数据库和数据库用户
一般情况下，MySQL数据库安装在本地，并且也只需要本地的php脚

本对mysql进行读取，所以很多用户不需要，尤其是默认安装的用户

。MySQL初始化后会自动生成空用户和test库，进行安装的测试，这

会对数据库的安全构成威胁，有必要全部删除，最后的状态只保留

单个root即可，当然以后根据需要增加用户和数据库。

#mysql> show databases;
#mysql> drop database test; //删除数据库test
#use mysql;
#delete from db; //删除存放数据库的表信息，因为还没有数据库信

息。
#mysql> delete from user where not (user=’root’) ; // 删除初始非

root的用户
#mysql> delete from user where user=’root’ and password=”; //

删除空密码的root，尽量重复操作
Query OK, 2 rows affected (0.00 sec)
#mysql> flush privileges; //强制刷新内存授权表。

3、改变默认mysql管理员帐号
系统mysql的管理员名称是root，而一般情况下，数据库管理员都没

进行修改，这一定程度上对系统用户穷举的恶意行为提供了便利，

此时修改为复杂的用户名，请不要在设定为admin或者administraror

的形式，因为它们也在易猜的用户字典中。

mysql> update user set user=”newroot” where user=”root”; //改

成不易被猜测的用户名
mysql> flush privileges;

4、关于密码的管理
密码是数据库安全管理的一个很重要因素，不要将纯文本密码保存

到数据库中。如果你的计算机有安全危险，入侵者可以获得所有的

密码并使用它们。相反，应使用MD5()、SHA1()或单向哈希函数。

也不要从词典中选择密码，有专门的程序可以破解它们，请选用至

少八位，由字母、数字和符号组成的强密码。在存取密码时，使用

mysql的内置函数password（）的sql语句，对密码进行加密后存储

。例如以下方式在users表中加入新用户。

#mysql> insert into users values (1,password(1234),’test’);

5、使用独立用户运行msyql
绝对不要作为使用root用户运行MySQL服务器。这样做非常危险，

因为任何具有FILE权限的用户能够用root创建文件(例如，

~root/.bashrc)。mysqld拒绝使用root运行，除非使用–user=root选

项明显指定。应该用普通非特权用户运行mysqld。正如前面的安装

过程一样，为数据库建立独立的linux中的mysql账户，该账户用来只

用于管理和运行MySQL。

要想用其它Unix用户启动mysqld，，增加user选项指定/etc/my.cnf

选项文件或服务器数据目录的my.cnf选项文件中的[mysqld]组的用

户名。
#vi /etc/my.cnf
[mysqld]
user=mysql
该命令使服务器用指定的用户来启动，无论你手动启动或通过

mysqld_safe或mysql.server启动，都能确保使用mysql的身份。也可

以在启动数据库是，加上user参数。

# /usr/local/mysql/bin/mysqld_safe –user=mysql &
作为其它linux用户而不用root运行mysqld，你不需要更改user表中

的root用户名，因为MySQL账户的用户名与linux账户的用户名无关

。确保mysqld运行时，只使用对数据库目录具有读或写权限的linux

用户来运行。

6、禁止远程连接数据库
在命令行netstat -ant下看到，默认的3306端口是打开的，此时打开

了mysqld的网络监听，允许用户远程通过帐号密码连接数本地据库

，默认情况是允许远程连接数据的。为了禁止该功能，启动skip-

networking，不监听sql的任何TCP/IP的连接，切断远程访问的权利

，保证安全性。假如需要远程管理数据库，可通过安装PhpMyadmin

来实现。假如确实需要远程连接数据库，至少修改默认的监听端口

，同时添加防火墙规则，只允许可信任的网络的mysql监听端口的数

据通过。

# vi /etc/my.cf
将#skip-networking注释去掉。
# /usr/local/mysql/bin/mysqladmin -u root -p shutdown //停止数据

库
#/usr/local/mysql/bin/mysqld_safe –user=mysql & //后台用mysql

用户启动mysql

7、限制连接用户的数量
数据库的某用户多次远程连接，会导致性能的下降和影响其他用户

的操作，有必要对其进行限制。可以通过限制单个账户允许的连接

数量来实现，设置my.cnf文件的mysqld中的max_user_connections

变量来完成。GRANT语句也可以支持资源控制选项来限制服务器对

一个账户允许的使用范围。

#vi /etc/my.cnf
[mysqld]
max_user_connections 2

8、用户目录权限限制
默认的mysql是安装在/usr/local/mysql，而对应的数据库文件

在/usr/local/mysql/var目录下，因此，必须保证该目录不能让未经

授权的用户访问后把数据库打包拷贝走了，所以要限制对该目录的

访问。确保mysqld运行时，只使用对数据库目录具有读或写权限的

linux用户来运行。
# chown -R root /usr/local/mysql/ //mysql主目录给root
# chown -R mysql.mysql /usr/local/mysql/var //确保数据库目录权

限所属mysql用户

9、命令历史记录保护
数据库相关的shell操作命令都会分别记录在.bash_history，如果这

些文件不慎被读取，会导致数据库密码和数据库结构等信息泄露，

而登陆数据库后的操作将记录在.mysql_history文件中，如果使用

update表信息来修改数据库用户密码的话，也会被读取密码，因此

需要删除这两个文件，同时在进行登陆或备份数据库等与密码相关

操作时，应该使用-p参数加入提示输入密码后，隐式输入密码，建

议将以上文件置空。

# rm .bash_history .mysql_history   //删除历史记录
# ln -s /dev/null .bash_history   //将shell记录文件置空
# ln -s /dev/null .mysql_history   //将mysql记录文件置空

10、禁止MySQL对本地文件存取
在mysql中，提供对本地文件的读取，使用的是load data local infile

命令，默认在5.0版本中，该选项是默认打开的，该操作令会利用

MySQL把本地文件读到数据库中，然后用户就可以非法获取敏感信

息了，假如你不需要读取本地文件，请务必关闭。

测试：首先在测试数据库下建立sqlfile.txt文件，用逗号隔开各个字

段
# vi sqlfile.txt
1,sszng,111
2,sman,222
#mysql> load data local infile ‘sqlfile.txt’ into table users fields

terminated by ‘,’; //读入数据
#mysql> select * from users;
+——–+————+———-+
| userid   | username   | password |
+——–+————+———-+
|       1 | sszng     | 111   |
|       2 | sman     | 222   |
+——–+————+———-+

成功的将本地数据插入数据中，此时应该禁止MySQL中用“LOAD

DATA LOCAL INFILE”命令。网络上流传的一些攻击方法中就有用它

LOAD DATA LOCAL INFILE的，同时它也是很多新发现的SQL

Injection攻击利用的手段！黑客还能通过使用LOAD DATALOCAL

INFILE装载“/etc/passwd”进一个数据库表，然后能用SELECT显示它

，这个操作对服务器的安全来说，是致命的。可以在my.cnf中添加

local-infile=0，或者加参数local-infile=0启动mysql。

#/usr/local/mysql/bin/mysqld_safe –user=mysql –local-infile=0 &
#mysql> load data local infile ‘sqlfile.txt’ into table users fields

terminated by ‘,’;
#ERROR 1148 (42000): The used command is not allowed with

this MySQL version

–local-infile=0选项启动mysqld从服务器端禁用所有LOAD DATA

LOCAL命令，假如需要获取本地文件，需要打开，但是建议关闭。

11、MySQL服务器权限控制
MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并

且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE等

权限（详见user超级用户表）。它的附加的功能包括有匿名的用户

并对于MySQL特定的功能例如LOAD DATA INFILE进行授权及管理操

作的能力。

管理员可以对user，db，host等表进行配置，来控制用户的访问权

限，而user表权限是超级用户权限。只把user表的权限授予超级用

户如服务器或数据库主管是明智的。对其他用户，你应该把在user

表中的权限设成’N'并且仅在特定数据库的基础上授权。你可以为特

定的数据库、表或列授权，FILE权限给予你用LOAD DATA INFILE和

SELECT … INTO OUTFILE语句读和写服务器上的文件，任何被授予

FILE权限的用户都能读或写MySQL服务器能读或写的任何文件。(说

明用户可以读任何数据库目录下的文件，因为服务器可以访问这些

文件）。 FILE权限允许用户在MySQL服务器具有写权限的目录下创

建新文件，但不能覆盖已有文件在user表的File_priv设置Y或N。，

所以当你不需要对服务器文件读取时，请关闭该权限。

#mysql> load data infile ‘sqlfile.txt’ into table loadfile.users fields

terminated by ‘,’;
Query OK, 4 rows affected (0.00 sec) //读取本地信息sqlfile.txt’
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
#mysql> update user set File_priv=’N’ where user=’root’; //禁止读

取权限
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges; //刷新授权表
Query OK, 0 rows affected (0.00 sec)
#mysql> load data infile ‘sqlfile.txt’ into table users fields

terminated by ‘,’; //重登陆读取文件
#ERROR 1045 (28000): Access denied for user ‘root’@'localhost’

(using password: YES) //失败
# mysql> select * from loadfile.users into outfile ‘test.txt’ fields

terminated by ‘,’;
ERROR 1045 (28000): Access denied for user ‘root’@'localhost’

(using password: YES)
为了安全起见，随时使用SHOW GRANTS语句检查查看谁已经访问

了什么。然后使用REVOKE语句删除不再需要的权限。

12、使用chroot方式来控制MySQL的运行目录
Chroot是linux中的一种系统高级保护手段，它的建立会将其与主系

统几乎完全隔离，也就是说，一旦遭到什么问题，也不会危及到正

在运行的主系统。这是一个非常有效的办法，特别是在配置网络服

务程序的时候。

13、关闭对Web访问的支持
如果不打算让Web访问使用MySQL数据库，没有提供诸如PHP这样

的Web语言的时候，重新设置或编译你的PHP，取消它们对MySQL

的默认支持。假如服务器中使用php等web程序，试试用Web形式非

法的请求，如果得到任何形式的MySQL错误，立即分析原因，及时

修改Web程序，堵住漏洞，防止MySQL暴露在web面前。
对于Web的安全检查，在MySQL官方文档中这么建议，对于web应

用，至少检查以下清单：
试试用Web形式输入单引号和双引号(‘’’和‘”’)。如果得到任何形

式的MySQL错误，立即分析原因。
试试修改动态URL，可以在其中添加%22(‘”’)、%23(‘#’)和%27(‘

’’)。
试试在动态URL中修改数据类型，使用前面示例中的字符，包括

数字和字符类型。你的应用程序应足够安全，可以防范此类修改和

类似攻击。
试试输入字符、空格和特殊符号，不要输入数值字段的数字。你

的应用程序应在将它们传递到MySQL之前将它们删除或生成错误。

将未经过检查的值传递给MySQL是很危险的！
将数据传给MySQL之前先检查其大小。
用管理账户之外的用户名将应用程序连接到数据库。不要给应用

程序任何不需要的访问权限。

14、数据库备份策略
一般可采用本地备份和网络备份的形式，可采用MySQL本身自带的

mysqldump的方式和直接复制备份形式，

直接拷贝数据文件最为直接、快速、方便，但缺点是基本上不能实

现增量备份。为了保证数据的一致性，需要在备份文件前，执行以

下 SQL 语句：FLUSH TABLES WITH READ LOCK；也就是把内存中

的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会

有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷

贝回原来的数据库目录下即可。

使用mysqldump可以把整个数据库装载到一个单独的文本文件中。

这个文件包含有所有重建您的数据库所需要的SQL命令。这个命令取

得所有的模式（Schema，后面有解释）并且将其转换成DDL语法（

CREATE语句，即数据库定义语句），取得所有的数据，并且从这些

数据中创建INSERT语句。这个工具将您的数据库中所有的设计倒转

。因为所有的东西都被包含到了一个文本文件中。这个文本文件可

以用一个简单的批处理和一个合适SQL语句导回到MySQL中。

使用 mysqldump进行备份非常简单，如果要备份数据库”

nagios_db_backup ”，使用命令，同时使用管道gzip命令对备份文件

进行压缩，建议使用异地备份的形式，可以采用Rsync等方式，将备

份服务器的目录挂载到数据库服务器，将数据库文件备份打包在，

通过crontab定时备份数据：

#!/bin/sh
time=`date +”(“%F”)”%R`
$/usr/local/mysql/bin/mysqldump -u nagios -pnagios nagios |

gzip>/home/sszheng/nfs58/nagiosbackup/nagios_backup.$time.gz
# crontab -l
# m h dom mon dow command
00 00 * * * /home/sszheng/shnagios/backup.sh

恢复数据使用命令：
gzip -d nagios_backup.$2008-01-24$00\:00.gz
nagios_backup.(2008-01-24)00:00
#mysql –u root -p nagios <

/home/sszheng/nfs58/nagiosbackup/nagios_backup.\(2008-01-24

\)12\:00
三、Mysqld安全相关启动选项
下列mysqld选项影响安全：
–allow-suspicious-udfs
该选项控制是否可以载入主函数只有xxx符的用户定义函数。默认情

况下，该选项被关闭，并且只能载入至少有辅助符的UDF。这样可

以防止从未包含合法UDF的共享对象文件载入函数。
–local-infile[={0|1}]
如果用–local-infile=0启动服务器，则客户端不能使用LOCAL in

LOAD DATA语句。
–old-passwords
强制服务器为新密码生成短(pre-4.1)密码哈希。当服务器必须支持

旧版本客户端程序时，为了保证兼容性这很有用。
(OBSOLETE) –safe-show-database
在以前版本的MySQL中，该选项使SHOW DATABASES语句只显示用

户具有部分权限的数据库名。在MySQL 5.1中，该选项不再作为现在

的默认行为使用，有一个SHOW DATABASES权限可以用来控制每个

账户对数据库名的访问。

–safe-user-create
如果启用，用户不能用GRANT语句创建新用户，除非用户有

mysql.user表的INSERT权限。如果你想让用户具有授权权限来创建

新用户，你应给用户授予下面的权限：
mysql> GRANT INSERT(user) ON mysql.user TO ‘user_name’

@'host_name’;
这样确保用户不能直接更改权限列，必须使用GRANT语句给其它用

户授予该权限。
–secure-auth
不允许鉴定有旧(pre-4.1)密码的账户。
–skip-grant-tables
这个选项导致服务器根本不使用权限系统。这给每个人以完全访问

所有的数据库的权力！（通过执行mysqladmin flush-privileges或

mysqladmin eload命令，或执行FLUSH PRIVILEGES语句，你能告诉

一个正在运行的服务器再次开始使用授权表。）
–skip-name-resolve
主机名不被解析。所有在授权表的Host的列值必须是IP号或localhost

。
–skip-networking
在网络上不允许TCP/IP连接。所有到mysqld的连接必须经由Unix套

接字进行。
-skip-show-database
使用该选项，只允许有SHOW DATABASES权限的用户执行SHOW

DATABASES语句，该语句显示所有数据库名。不使用该选项，允许

所有用户执行SHOW DATABASES，但只显示用户有SHOW

DATABASES权限或部分数据库权限的数据库名。请注意全局权限指

数据库的权限。
　　mysql安全设置及性能优化配置全攻略，注意在不熟悉的情况下

更改设置前最好进行备份，以免出错。
　　1、修改root用户的的口令
　　缺省情况下MySQL没有设置密码，我们可以用三种方法设置密

码头：
　　* 用mysqladmin命令来改root用户口令
　　＃ mysqladmin -uroot password ecan5com //设置MySQL管理

操作用户root的密码为52netseek
　　* 用set password修改口令：
　　mysql> set password for root@localhost=password

(‘ecan5com’);
　　* 直接修改user表的root用户口令
　　mysql> use mysql;
　　mysql> update user set password=password(‘ecan5com’)

where user=’root’;
　　mysql> flush privileges;
　　2、删除默认的数据库和用户
　　我们的数据库是在本地，并且也只需要本地的php脚本对mysql

进行读取，所以很多用户不需要。m
　　ysql初始化后会自动生成空用户和test库，这会对数据库构成威

胁，我们全部删除。
　　我们使用mysql客户端程序连接到本地的mysql服务器后出现如

下提示：
　　mysql> drop database test;
　　mysql> use mysql;
　　mysql> delete from db;
　　mysql> delete from user where not(host=”localhost” and

user=”root”);
　　mysql> flush privileges;
　　3、改变默认mysql管理员的名称 //仅为建议
　　根据个人习惯，因为默认的mysql的管理员名称是root，所以如

果能够修改的话，
　　能够防止一些脚本小子对系统的穷举。我们可以直接修改数据

库，把root用户改为”admin”
　　mysql> use mysql;
　　mysql> update user set user=”admin” where user=”root”;
　　mysql> flush privileges;
　　4、提高本地安全性
　　提高本地安全性，主要是防止mysql对本地文件的存取，比如黑

客通过mysql把/etc/passwd获取了，会对系统构成威胁。
　　mysql对本地文件的存取是通过SQL语句来实现，主要是通过

Load DATA LOCAL INFILE来实现，我们能够通过禁用该功能来
　　防止黑客通过SQL注射等获取系统核心文件。禁用该功能必须

在 my.cnf 的[mysqld]部分加上一个参数：
　　set-variable=local-infile=0
　　5、禁止远程连接mysql
　　因为我们的mysql只需要本地的php脚本进行连接，所以我们无

需开socket进行监听，那么我们完全可以关闭监听的功能。
　　有两个方法实现：
　　* 配置my.cnf文件，在[mysqld]部分添加 skip-networking 参数
　　* mysqld服务器中参数中添加 –skip-networking 启动参数来使

mysql不监听任何TCP/IP连接，增加安全性。如果要进行mysql的管

理的话,可以在服务器本地安装一个phpMyadmin来进行管理。
　　6、控制数据库访问权限
　　对于使用php脚本来进行交互，最好建立一个用户只针对某个库

有 update、select、delete、insert、drop table、create table等权

限，这样就很好避免了数据库用户名和密码被黑客查看后最小损失

。
　　比如下面我们创建一个数据库为db1，同时建立一个用户test1

能够访问该数据库。
　　mysql> create database db1;
　　mysql> grant select,insert,update,delete,create,drop privileges

on db1.* to test1@localhost identified by ‘admindb’;
　　以上SQL是创建一个数据库db1，同时增加了一个test1用户，口

令是admindb，但是它只能从本地连接mysql，对db1库有

select,insert,update,delete,create,drop操作权限。
　　7、限制一般用户浏览其他用户数据库
　　如果有多个数据库，每个数据库有一个用户，那么必须限制用

户浏览其他数据库内容，可以在启动MySQL服务器时加–skip-show

-database 启动参数就能够达到目的。
　　忘记mysql密码的解决办法
　　如果不慎忘记了MySQL的root密码，我们可以在启动MySQL服

务器时加上参数–skip-grant-tables来跳过授权表的验证

(./safe_mysqld –skip-grant-tables &)，这样我们就可以直接登陆

MySQL服务器，然后再修改root用户的口令，重启MySQL就可以用

新口令登陆了。
　　8、数据库文件的安全
　　我们默认的mysql是安装在/usr/local/mysql目录下的，那么对应

的数据库文件就是在/usr/local/mysql/var目录下，那么我们要保证

该目录不能让未经授权的用户访问后把数据库打包拷贝走了，所以

要限制对该目录的访问。

我们修改该目录的所属用户和组是mysql，同时改变访问权限：
　　# chown -R mysql.mysql /usr/local/mysql/var
　　# chmod -R go-rwx /usr/local/mysql/var
　　9、删除历史记录
　　执行以上的命令会被shell记录在历史文件里，比如bash会写入

用户目录的.bash_history文件，如果这些文件不慎被读，
　　那么数据库的密码就会泄漏。用户登陆数据库后执行的SQL命令

也会被MySQL记录在用户目录的.mysql_history文件里。
　　如果数据库用户用SQL语句修改了数据库密码，也会

因.mysql_history文件而泄漏。所以我们在shell登陆及备份的时候
　　不要在-p后直接加密码，而是在提示后再输入数据库密码。另

外这两个文件我们也应该不让它记录我们的操作，以防万一。
　　# rm .bash_history .mysql_history
　　# ln -s /dev/null .bash_history
　　# ln -s /dev/null .mysql_history
　　10、其他
　　另外还可以考虑使用chroot等方式来控制mysql的运行目录，更

好的控制权限，具体可以参考相关文章。
　　my.cnf的调试参数
　　11、快速修复MySQL数据库
　　修复数据库
　　# mysqlcheck -A -o -r -p
　　修复指定的数据库
　　# mysqlcheck -o -r Database_NAME -p
　　12、跟据内存的大小选择MySQL的负载,选择相应的文件名复制

到/etc/my.cnf
　　如果是RPM包安装:/usr/share/doc/mysql-XXX/目录下有
　　如果是源代码编译安装:support-files/目录下有.
　　cp /usr/local/share/mysql-***/my-large.cnf /etc/my.cnf
　　my-small.cnf # > my-medium.cnf # 32M – 64M
　　my-large.cnf # memory = 512M
　　my-huge.cnf # 1G-2G
　　my-innodb-heavy-4G.cnf # 4GB

mysql执行sql脚本文件

sql脚本是包含一到多个sql命令的sql语句，我们可以将这些sql脚本

放在一个文本文件中（我们称之为“sql脚本文件”），然后通过相关

的命令执行这个sql脚本文件。基本步骤如下：
一、创建包含sql命令的sql脚本文件
文件中包含一系列的sql语句，每条语句最后以;结尾，假设文件

名为ss.sql，文件内容示例如下（使用“--”进行注释）：

--创建表
create table 表名称
(
Guid Varchar(38) not null primary key,
Title Varchar(255),

) TYPE=InnoDB;
--在表A中增加字段Status
alter table A add Status TinyInt default '0';
--在表A上创建索引
create index XX_TaskId_1 on A(Id_);
--在A表中添加一条记录
Insert into A (Id,ParentId, Name) values(1,0,'名称');
--添加、修改、删除数据后，有可能需要提交事务
Commit;

二、执行sql脚本文件
方法一使用cmd命令执行(以windows为例，unix或linux的在其类似

的控制台下运行)
格式：【Mysql的bin目录】\mysql –u用户名 –p密码 –D数据库<

【sql脚本文件路径全名】
示例：D:\mysql\bin\mysql –uroot –p123456 -Dtest

<d:\test\ss.sql
注意事项：
1、如果在sql脚本文件中使用了use 数据库，则-D数据库选项可

以忽略
2、如果【Mysql的bin目录】中包含空格，则需要使用“”包含，

如：
“C:\Program Files\mysql\bin\mysql” –u用户名 –p密码 –D数据

库<【sql脚本文件路径全名】
3、如果需要将执行结果输出到文件，可以采用以下模式
D:\mysql\bin\mysql –uroot –p123456 -Dtest <d:\test\ss.sql

>d:\dd.txt
4、【sql脚本文件路径全名】这个文件可以使用批处理对应的相

对路径

方法二进入mysql的控制台后，使用source命令执行
   1、进入mysql控制台
       D:\mysql\bin\mysql –uroot –p123456 -Dtest
   2、执行sql脚本文件
       Mysql>source 【sql脚本文件的路径全名】

如：mysql>source c:\xx.sql
或者输入mysql>source c://user.sql
使用//是一个转意字符://就是:/

java 执行cmd命令传参到批处理文件
java 代码：
try {
final Process makemenuconfig = Runtime.getRuntime

().exec("cmd.exe /k start \WAIT c:\\text1.bat "+"F:\\zzjzmdx");
//注意批处理后面（需空格隔开）是参数（可多个需空格隔开）
} catch (IOException e) {
e.printStackTrace();
}

ps： java代码直接扔在 main 里执行即可
批处理文件text1.bat代码：（注意%1表示第一个参数，%2表第二

个。。。）
f:
cd %1
make ARCH=mips CROSS_COMPILE=mipsel-linux- menuconfig

远程访问mysql

可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%"

mysql -u root -pvmwaremysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>flush privileges;
mysql>select host, user from user;

mysql>quit
授权你想让远程访问的账户：
例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器的dk数据库，并使用mypassword作为密码
GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

我用的第一个方法,刚开始发现不行,在网上查了一下,少执行一个语句 mysql>FLUSH RIVILEGES 使修改生效.就可以了
另外一种方法,不过我没有亲自试过的,在csdn.net上找的,可以看一下.

在安装mysql的机器上运行：
1、d:\mysql\bin\>mysql -h localhost -u root //这样应该可以进入MySQL服务器

2、mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION //赋予任何主机访问数据的权限

3、mysql>FLUSH PRIVILEGES //修改生效

4、mysql>EXIT //退出MySQL服务器

这样就可以在其它任何的主机上以root身份登录啦！

mysql中delimiter作用

1. delimiter
delimiter是mysql分隔符，在mysql客户端中分隔符默认是分号（；）。
如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符。

2. delimiter使用
在前一篇中有设置mysql的触发器时使用例子

mysql> delimiter //
mysql> create trigger upd_check before update on account
    -> for each row
    -> begin
    -> if new.amount < 0 then
    -> set new.amount=0;
    -> elseif new.amount > 100 then
    -> set new.amount = 100;
    -> end if;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

上面就是，先将分隔符设置为 //,

直到遇到下一个 //,才整体执行语句。

执行完后，最后一行， delimiter ; 将mysql的分隔符重新设置为分号；

如果不修改的话，本次会话中的所有分隔符都以// 为准。

delimiter命令指定了mysql解释器命令行的结束符，默认为“;”
说白了就是告知命令到哪儿结束，可以执行此命令了

但一般在存储过程中会有多个分号，我们并不希望一遇到分号就执行命令，因此可以用delimiter命令指定其他结束符来代替“;”
这个结束符可以自己定义，常用的是“//” 和 “$$”

举个例子：

mysql> delimiter // --将结束符指定为“//”

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> // --遇到//符号结束并执行上面的命令
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ; --将结束符改回默认值“;”

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

注意：在定义结束符时：delimiter // 命令后不能加上";" 出于习惯或者是把delimiter命令误以为必须加“;”才能执行而在后面加上了分号，那么在执行存储过程时会报错，
因为当mysql> delimiter // ;这样定义时，此时的结束符不是“//”，而是“// ;”

ibatis模糊查询的like '%$name$%'的sql注入避免。

在用ibatis进行模糊查询的时候很多同学习惯用like '%$name$%'的方式，其实这种方式会造成sql注入。ibatis对于$符号的处理是默认不加’‘号的，所以如果传入的参数是：

1'或者是1231%' or 1%' = '1这些形式就回造成注入危险。

解决是避免用like '%$name$%'，可以进行字符的拼接进行规避，比如oracle：like '%'||#name#||'%';mysql：like concat('%',#name#,'%');

mysql字符串连接concat和字符串大小写转换函数

1、concat()函数
   1.1 MySQL的concat函数可以连接一个或者多个字符串,如
       mysql> select concat('10');
       | 10           |
       mysql> select concat('11','22','33');
       | 112233                 |
       而Oracle的concat函数只能连接两个字符串
       SQL> select concat('11','22') from dual;
       select concat("#",name,"#") from t_student where id=10;
       上面的语句可以在name的前后加上#号再输出到终端，从而可以看到前后是否有空格，或name本身是否为空白符
   1.2 MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
       mysql> select concat('11','22',null);
       | concat('11','22',null) |
       | NULL                   |
       而Oracle的concat函数连接的时候，只要有一个字符串不是NULL,就不会返回NULL
       SQL> select concat('11',NULL) from dual;
       11

2、concat_ws()函数, 表示concat with separator,即有分隔符的字符串连接
   如连接后以逗号分隔
       mysql> select concat_ws(',','11','22','33');
       | concat_ws(',','11','22','33') |
       | 11,22,33                      |
   和concat不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL
       mysql> select concat_ws(',','11','22',NULL);
       | concat_ws(',','11','22',NULL) |
       | 11,22                         |
3、group_concat()可用来行转列, Oracle没有这样的函数
   完整的语法如下
   group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
   如下例子
   mysql> select * from aa;
   | id   | name |
   |    1 | 10   |
   |    1 | 20   |
   |    1 | 20   |
   |    2 | 20   |
   |    3 | 200 |
   |    3 | 500 |

   3.1 以id分组，把name字段的值打印在一行，逗号分隔(默认)
       mysql> select id,group_concat(name) from aa group by id;
       | id   | group_concat(name) |
       |    1 | 10,20,20           |
       |    2 | 20                 |
       |    3 | 200,500            |
   3.2 以id分组，把name字段的值打印在一行，分号分隔
       mysql> select id,group_concat(name separator ';') from aa group by id;
       | id   | group_concat(name separator ';') |
       |    1 | 10;20;20                         |
       |    2 | 20                               |
       |    3 | 200;500                          |

   3.3 以id分组，把去冗余的name字段的值打印在一行，逗号分隔
       mysql> select id,group_concat(distinct name) from aa group by id;
       |    1 | 10,20                       |
       |    2 | 20                          |
       |    3 | 200,500                     |

   3.4 以id分组，把name字段的值打印在一行，逗号分隔,以name排倒序
       mysql> select id,group_concat(name order by name desc) from aa group by id;
       |    1 | 20,20,10                              |
       |    2 | 20                                    |
       |    3 | 500,200                               |
4、repeat()函数，用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数
   mysql> select repeat('ab',2);
   | abab           |
   又如
   mysql> select repeat('a',2);
   | aa            |
5、SPACE(N) 函数。生成N个空格，如
   mysql> select space(3);
   | space(3) |
6、STRCMP(STR1,STR2) 字符串比较函数,该函数和字符集有关系，默认区分大小写
   若STR1和STR2相同, 则返回 0,
   若STR1小于STR2,   则返回 -1,
   若STR1大于STR2,   则返回 1
   mysql> select strcmp('abc','abc');
   |                   0 |
   mysql> select strcmp('a','ab');
   |               -1 |
   mysql> select strcmp('abc','ab');
   | strcmp('abc','ab') |
   |                  1 |

7.字符串大小写转换函数

MySQL字符串大小写转换需要用到MySQL字符串大小写转换函数，MySQL 字符串大小写转化函数有两对： lower(), uppper() 和 lcase(), ucase()：

mysql> select lower('DDD');
+--------------+
| lower('DDD') |
+--------------+
| ddd          |
+--------------+
mysql> select upper('ddd');
+--------------+
|upper('ddd') |
+--------------+
| DDD          |
+--------------+
mysql> select lcase('DDD');
+--------------+
| lcase('DDD') |
+--------------+
| ddd          |
+--------------+
mysql> select ucase('ddd');
+--------------+
| ucase('ddd') |
+--------------+
| DDD          |
+--------------+
通常情况下，我选择 lower(), upper() 来实现MySQL转换字符串大小写，因为这和其他数据库中函数相兼容。

注意concat与大小写函数一起使用时,最好先转换大小写后再使用连接函数.大小写函数在连接函数之后使用可能不起作用.

MYSQL中根据两个表中相同的字段数据去更新其中一张表的另一个字段

比如A,B表 A中有两个字段全部省市和对应编码 B表中有部分省市和对应编码两个字段。现在我要把B表中的地市拿到A表中查出相同的来，并把对应的编码更新到B表的对应编码。
~~~~~~~~~~表A
id   地市    地市编码
1   北京       001
2   天津       002
3 上海        003

~~~~~~~~~~表B
id   地市    地市编码
1   北京
2   天津

update A , B set A.编码=B.编码 where A.地市=B.地市

SQL Insert Into Select 用法

1. 语法介绍

Insert是SQL中常用的语句，主要用途就是向表中添加数据。下面说说Insert子句的几种形式

：

A、Insert Into Table（field1，fields2,……）values(value1,value2,……)

这种语法形式的Insert语句，一般用于向表中所有字段或者部分字段添加字符串。

如果是向表中部分字段添加数据，table后面的括号内容就不能省略。形式就是上面的形式

Insert Into Table（field1，fields2,……）values(value1,value2,……)；

如果是向表中所有字段添加数据，table后面的括号就可以省略。就是这样：Insert Into Table

values(value1,value2,……)

B、Insert Into Table1 Select value1,value2,value3,…… from table2 where ……

这种语法形式的Insert语句，主要用于表复制。它能将Table2中的部分或者全部数据复制到

Table1中。
如果我们要实现的功能是复制Table2中的一部分数据，和一部分字符串，我们只需要将

Table2中所需数据和所需字符串放在Select后面。Insert语句会自动识别。

示例:
有三张表a、b、c，现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段

。对于这种情况，可以使用如下的语句来实现：

INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name

上面的语句比较适合两个表的数据互插，如果多个表就不适应了。对于多个表，可以先

将需要查询的字段JOIN起来，然后组成一个视图后再SELECT FROM就可以了：

INSERT INTO a (field1,field2) SELECT * FROM(SELECT b.f1,c.f2 FROM b JOIN c) AS tb

其中f1是表b的字段，f2是表c的字段，通过JOIN查询就将分别来自表b和表c的字段进行

了组合，然后再通过SELECT嵌套查询插入到表a中，这样就满足了这个场景了，如果需要不止

2个表，那么可以多个JOIN的形式来组合字段。

2. 语法错误注意
需要注意的是嵌套查询部分最后一定要有设置表别名，如下：

SELECT * FROM (SELECT f1,f2 FROM b JOIN c) AS tb

即最后的AS tb是必须的（tb这个名称可以随意取），即指定一个别名。每个派生出来的

新表都必须指定别名，否则在mysql中会报如下错误：

ERROR 1248 (42000): Every derived TABLE must have its own alias

另外，MySQL中INSERT INTO SELECT不能加VALUES，即不能写成如下形式：

INSERT INTO db1_name(field1,field2) VALUES SELECT field1,field2 FROM db2_name

否则也会报错：You have an error in your SQL syntax

3.如果需要提高插入效率，先插入到一个临时表,再:

select key,min(col1),...min(conN)
into temptable2
from temptable
group by key

insert tablename(...)
select ... from temptable2
where not exists (
select * from tablename
where tablename.key=temptable.key
)

drop table temptable2,temptable

4.MySql自动类型转换 MySql insert into select 带来的问题
MySql自作聪明的做了很多数据类型转换，导致我们在操作的时候，出现很多莫名其妙的问题

，比如：‘a’+‘a',MySql的结果为0，必须用concat来进行连接；

如果使用快速插入：insert into select操作语句时，select中有where过滤条件，而过滤条

件为字符串类型，我们又偷懒的使用数字类型作为条件去查询，那你就悲剧了，查出来的数

据永远是对的，但是与insert into结合起来就会包类型错误，让你怎么也想不到是你的where

条件中不规范的类型书写导致的，如下：
insert into atbl(c1,c2) select c1,c2 from btbl where c1 in(1,2,3,4);

如果你的c1字段在表atbl与btbl两个表中都是字符串类型，而你的查询条件c1对应的类型是

整数1，2，3，4，光看 select c1,c2 from btbl where c1 in(1,2,3,4); 是能查处正确的结果

来，但是，一个完整的 insert into atbl(c1,c2) select c1,c2 from btbl where c1 in(1,2,3,4);

操作，就会报 Error Code: 1292. Truncated incorrect DOUBLE value:

    让你永远也想不到是where条件导致的
    正确做法： insert into atbl(c1,c2) select c1,c2 from btbl where c1 in(‘1’,‘2’,‘3’,‘4’);
     这样就不会导致这样的错误问题产生

下面这种方式也可以
insert into atbl(c1,c2) select c1,c2 from btbl where c1 in concat(‘1’,‘2’,‘3’,‘4’);
多个字段作为条件判断,比如A1和A2字段是否在另一个表ctbl中的字段B1和B2,
如果不存在则插入表atbl
insert into atbl(c1,c2)
select b1,b2 from ctbl
where concat(c1,"##",c2)
not in concat(b1,"##",b2);
如果有子查询,即not in后面又是一个查询,则子查询需要括起来.
insert into atbl(c1,c2)
select b1,b2 from ctbl
where concat(c1,"##",c2)
not in (select concat(d1,"##",d2) from dtbl);

mysql中left join,right join,inner join,outer join的用法详解

转载: http://blog.wdou.cn/index.php/7139/viewspace-14285.html
下面是例子分析
表A记录如下：
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
创建这两个表SQL语句如下：
CREATE TABLE a
aID int( 1 ) AUTO_INCREMENT PRIMARY KEY ,
aNum char( 20 )
)
CREATE TABLE b(
bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
bName char( 20 )
)
INSERT INTO a
VALUES ( 1, 'a20050111' ) , ( 2, 'a20050112' ) , ( 3, 'a20050113' ) , ( 4, 'a20050114' ) , ( 5,

'a20050115' ) ;
INSERT INTO b
VALUES ( 1, ' 2006032401' ) , ( 2, '2006032402' ) , ( 3, '2006032403' ) , ( 4, '2006032404' )

, ( 8, '2006032408' ) ;
实验如下:
1.left join(左联接)
sql语句如下:
SELECT * FROM a
LEFT JOIN b
ON a.aID =b.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
（所影响的行数为 5 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为

: A.aID = B.bID).
B表记录不足的地方均为NULL.
2.right join(右联接)
sql语句如下:
SELECT * FROM a
RIGHT JOING b
ON a.aID = b.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
（所影响的行数为 5 行）
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方

用NULL填充.
3.inner join(相等联接或内联接)
sql语句如下:
SELECT * FROM a
INNER JOIN b
ON a.aID =b.bID
等同于以下SQL句:
SELECT *
FROM a,b
WHERE a.aID = b.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合

条件的记录.
LEFT JOIN操作用于在任何的 FROM 子句中，
组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了

从第一个（左边）开始的两个表中的全部记录，即
使在第二个（右边）表中并没有相符值的记录。
语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2
说明：table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类

型的数据，但它们不需要有相同的
名称。
compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。
如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，

将会发生错误。

MySQL中的各种JOIN

1. 笛卡尔积(交叉连接)
在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用','
如
SELECT * FROM table1 CROSS JOIN table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1,table2

由于其返回的结果为被连接的两个数据表的乘积，因此当有WHERE, ON或USING条件的时候

一般不建议使用，因为当数据表项目太多的时候，会非常慢。
一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

2. 内连接INNER JOIN
在MySQL中把INNER JOIN叫做等值连接，即需要指定等值连接条件
在MySQL中CROSS和INNER JOIN被划分在一起，不明白。
参看MySQL帮助手册
http://dev.mysql.com/doc/refman/5.0/en/join.html
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. MySQL中的外连接，分为左外连接和右连接，
即除了返回符合连接条件的结果之外，还要返回左表(左连接)或者右表(右连接)中不符合连接

条件的结果，相对应的使用NULL对应。

a. LEFT [OUTER] JOIN
SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON

table1.column=table2.column
除了返回符合连接条件的结果之外，还需要显示左表中不符合连接条件的数据列，相对应使

用NULL对应

b. RIGHT [OUTER] JOIN
SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON

table1.column=table2.column
RIGHT与LEFT JOIN相似不同的仅仅是除了显示符合连接条件的结果之外，还需要显示右表中

不符合连接条件的数据列，相应使用NULL对应

--------------------------------------------
添加显示条件WHERE, ON, USING
1. WHERE子句
2. ON
3. USING子句，如果连接的两个表连接条件的两个列具有相同的名字的话可以使用USING
例如
SELECT FROM LEFT JOIN USING ()

连接多余两个表的情况
举例：
mysql> SELECT artists.Artist, cds.title, genres.genre
    -> FROM cds
    -> LEFT JOIN genres
    -> ON cds.genreID = genres.genreID
    -> LEFT JOIN artists
    -> ON cds.artistID = artists.artistID;
或者
mysql> SELECT artists.Artist, cds.title, genres.genre
    -> FROM cds
    -> LEFT JOIN genres
    -> ON cds.genreID = genres.genreID
    -> LEFT JOIN artists
    -> ON cds.artistID = artists.artistID
    -> WHERE (genres.genre = 'Pop');
--------------------------------------------

另外需要注意的地方

在MySQL中涉及到多表查询的时候，需要根据查询的情况，想好使用哪种连接方式效率更高

。
1. 交叉连接(笛卡尔积)或者内连接
[INNER | CROSS] JOIN
2. 左外连接LEFT [OUTER] JOIN或者右外连接RIGHT [OUTER] JOIN

注意指定连接条件WHERE, ON，USING.

--------------------------------------------
看懂MySQL手册定义的MySQL各种JOIN的用法：
//看懂如下的定义方式

table_references: table_reference [, table_reference] ...
//不同的JOIN EXPRESSION之间使用','分割
A table reference is also known as a join expression.table_reference: table_factor |

join_table
//每个JOIN EXPRESSION由数据表table_factor以及JOIN表达式构成
join_table table_factor: tbl_name [[AS] alias] [index_hint)] | ( table_references ) | { OJ

table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
//数据表table_factor，注意其递归定义的table_referencesjoin_table: table_reference

[INNER | CROSS] JOIN table_factor [join_condition] | table_reference STRAIGHT_JOIN

table_factor | table_reference STRAIGHT_JOIN table_factor ON condition |

table_reference LEFT [OUTER] JOIN table_reference join_condition | table_reference

NATURAL [LEFT [OUTER]] JOIN table_factor | table_reference RIGHT [OUTER] JOIN

table_reference join_condition | table_reference NATURAL [RIGHT [OUTER]] JOIN

table_factor
//数据表的连接表达式join_table join_condition: ON conditional_expr | USING

(column_list)
//连接表达式的连接条件定义使用ON或者USING index_hint: USE [FOR JOIN] (index_list)

| IGNORE [FOR JOIN] (index_list) | FORCE [FOR JOIN] (index_list)index_list:

index_name [, index_name] ...

MySQL手册中提到的JOIN需要注意的地方：
1.
In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each

other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause,

CROSS JOIN is used otherwise.
手册中提到
标准SQL中CROSS JOIN交叉连接(笛卡尔积)和内连接INNER JOIN不同，但是MySQL中两者是

相同的，即有[CROSS | INNER] JOIN，两者可以互相替代，而且可以只使用JOIN

2. A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:
SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name =

t2.name; 可以对数据表使用别名

3. ,运算符例如SELECT * FROM table1,table2 由于在MySQL中INNER JOIN与CROSS JOIN相

同，INNER JOIN和 , 在MySQL也相同，都是产生两个表的笛卡尔积Cartesian Product(等于两

个表格的行数乘积).但是,号的优先级要低于INNER JOIN, CROSS JOIN, LEFT JOIN因此If you

mix comma joins with the other join types when there is a join condition, an error of the

form. Unknown column 'col_name' in 'on clause' may occur.

4. 什么时候使用ON，什么时候使用WHEREON应该用户数据表连接的时候指定连接条件；

WHERE用于用户限制所选取的列例如ON a.column=b.column WHERE a.column='hello'

5. 可以使用LEFT JOIN查看，两个连接的表中，不符合连接条件的部分，因为不符合条件的部

分LEFT JOIN之后会显示为NULL
If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a

row with all columns set to NULL is used for the right table.
You can use this fact to find rows in a table that have no counterpart in another table:

SELECT left_tbl.* FROM left_tbl
LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that

is, all rows in left_tbl with no corresponding row in right_tbl).
This assumes that right_tbl.id is declared NOT NULL.

6.当别连接的表指定连接条件的列举有相同的名称的时候，
不需要ON a.column=b.column不同的时候才使用ON a.column_a=b.column_b.
可以使用USING (column),当然也可以使用多个USING (c1,c2,c3)
The USING(column_list) clause names a list of columns that must exist in both tables. If

tables a and b both contain columns c1, c2, and c3, the following join compares

corresponding columns from the two tables:a LEFT JOIN b USING (c1,c2,c3)

7.其他的：
#The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an

INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both

tables.
#RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it

is recommended that you use LEFT JOIN instead of RIGHT JOIN.
#The { OJ ... LEFT OUTER JOIN ...} syntax shown in the join syntax description exists only

for compatibility with ODBC. The curly braces in the syntax should be written literally; they

are not metasyntax as used elsewhere in syntax descriptions.
#STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the

right table. This can be used for those (few) cases for which the join optimizer puts the

tables in the wrong order.

MYSQL字符串操作常用函数

本文转载：http://blog.csdn.net/wangzhi_821/archive/2009/10/22/4711830.aspx

ASCII(str)
返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL。
mysql> select ASCII('2');
        -> 50
mysql> select ASCII(2);
        -> 50
mysql> select ASCII('dx');
        -> 100也可参见ORD()函数。

ORD(str)
如果字符串str最左面字符是一个多字节字符，通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。
mysql> select ORD('2');
        -> 50
CONV(N,from_base,to_base)
在不同的数字基之间变换数字。返回数字N的字符串数字，从from_base基变换为to_base基，如果任何参数是NULL，返回NULL。参数N解释为一个整数，但是可以指定为一个整数或一个字符串。最小基是2且最大的基是36。如果to_base是一个负数，N被认为是一个有符号数，否则，N被当作无符号数。 CONV以64位点精度工作。
mysql> select CONV("a",16,2);
        -> '1010'
mysql> select CONV("6E",18,8);
        -> '172'
mysql> select CONV(-17,10,-18);
        -> '-H'
mysql> select CONV(10+"10"+'10'+0xa,10,10);
        -> '40'
BIN(N)
返回二进制值N的一个字符串表示，在此N是一个长整数(BIGINT)数字，这等价于CONV(N,10,2)。如果N是NULL，返回NULL。
mysql> select BIN(12);
        -> '1100'
OCT(N)
返回八进制值N的一个字符串的表示，在此N是一个长整型数字，这等价于CONV(N,10,8)。如果N是NULL，返回NULL。
mysql> select OCT(12);
        -> '14'
HEX(N)
返回十六进制值N一个字符串的表示，在此N是一个长整型(BIGINT)数字，这等价于CONV(N,10,16)。如果N是NULL，返回NULL。
mysql> select HEX(255);
        -> 'FF'
CHAR(N,...)
CHAR()将参数解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串。NULL值被跳过。
mysql> select CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> select CHAR(77,77.3,'77.3');
        -> 'MMM'
CONCAT(str1,str2,...)
返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
mysql> select CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
        -> NULL
mysql> select CONCAT(14.3);
        -> '14.3'
LENGTH(str)
　
OCTET_LENGTH(str)
　
CHAR_LENGTH(str)
　
CHARACTER_LENGTH(str)
返回字符串str的长度。
mysql> select LENGTH('text');
        -> 4
mysql> select OCTET_LENGTH('text');
        -> 4
注意，对于多字节字符，其CHAR_LENGTH()仅计算一次。

LOCATE(substr,str)
　
POSITION(substr IN str)
返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.
mysql> select LOCATE('bar', 'foobarbar');
        -> 4
mysql> select LOCATE('xbar', 'foobar');
        -> 0
该函数是多字节可靠的。 LOCATE(substr,str,pos)
返回子串substr在字符串str第一个出现的位置，从位置pos开始。如果substr不是在str里面，返回0。
mysql> select LOCATE('bar', 'foobarbar',5);
        -> 7
这函数是多字节可靠的。

INSTR(str,substr)
返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同，除了参数被颠倒。
mysql> select INSTR('foobarbar', 'bar');
-> 4
mysql> select INSTR('xbar', 'foobar');
-> 0
这函数是多字节可靠的。

LPAD(str,len,padstr)
返回字符串str，左面用字符串padstr填补直到str是len个字符长。
mysql> select LPAD('hi',4,'??');
        -> '??hi'
RPAD(str,len,padstr)
返回字符串str，右面用字符串padstr填补直到str是len个字符长。
mysql> select RPAD('hi',5,'?');
        -> 'hi???'
LEFT(str,len)
返回字符串str的最左面len个字符。
mysql> select LEFT('foobarbar', 5);
        -> 'fooba'
该函数是多字节可靠的。

RIGHT(str,len)
返回字符串str的最右面len个字符。
mysql> select RIGHT('foobarbar', 4);
-> 'rbar'
该函数是多字节可靠的。

SUBSTRING(str,pos,len)
　
SUBSTRING(str FROM pos FOR len)
　
MID(str,pos,len)
从字符串str返回一个len个字符的子串，从位置pos开始。使用FROM的变种形式是ANSI SQL92语法。
mysql> select SUBSTRING('Quadratically',5,6);
-> 'ratica'
该函数是多字节可靠的。

SUBSTRING(str,pos)
　
SUBSTRING(str FROM pos)
从字符串str的起始位置pos返回一个子串。
mysql> select SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> select SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
该函数是多字节可靠的。

SUBSTRING_INDEX(str,delim,count)
返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数，返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数，返回最后的分隔符到右边的所有字符(从右边数)。
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
该函数对多字节是可靠的。

LTRIM(str)
返回删除了其前置空格字符的字符串str。
mysql> select LTRIM(' barbar');
        -> 'barbar'
RTRIM(str)
返回删除了其拖后空格字符的字符串str。
mysql> select RTRIM('barbar   ');
        -> 'barbar'
该函数对多字节是可靠的。 TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
返回字符串str，其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出，BOTH被假定。如果remstr没被指定，空格被删除。
mysql> select TRIM(' bar   ');
        -> 'bar'
mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'
该函数对多字节是可靠的。

SOUNDEX(str)
返回str的一个同音字符串。听起来“大致相同”的2个字符串应该有相同的同音字符串。一个“标准”的同音字符串长是4个字符，但是SOUNDEX()函数返回一个任意长的字符串。你可以在结果上使用SUBSTRING()得到一个“标准”的同音串。所有非数字字母字符在给定的字符串中被忽略。所有在A-Z之外的字符国际字母被当作元音。
mysql> select SOUNDEX('Hello');
        -> 'H400'
mysql> select SOUNDEX('Quadratically');
        -> 'Q36324'
SPACE(N)
返回由N个空格字符组成的一个字符串。
mysql> select SPACE(6);
        -> '      '
REPLACE(str,from_str,to_str)
返回字符串str，其字符串from_str的所有出现由字符串to_str代替。
mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'
该函数对多字节是可靠的。

REPEAT(str,count)
返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。
mysql> select REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REVERSE(str)
返回颠倒字符顺序的字符串str。
mysql> select REVERSE('abc');
-> 'cba'
该函数对多字节可靠的。

INSERT(str,pos,len,newstr)
返回字符串str，在位置pos起始的子串且len个字符长得子串由字符串newstr代替。
mysql> select INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
该函数对多字节是可靠的。

ELT(N,str1,str2,str3,...)
如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'
FIELD(str,str1,str2,str3,...)
返回str在str1, str2, str3, ...清单的索引。如果str没找到，返回0。FIELD()是ELT()反运算。
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 2
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 0
FIND_IN_SET(str,strlist)
如果字符串str在由N子串组成的表strlist之中，返回一个1到N的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为SET的列，FIND_IN_SET()函数被优化而使用位运算！如果str不是在strlist里面或如果strlist是空字符串，返回0。如果任何一个参数是NULL，返回NULL。如果第一个参数包含一个“,”，该函数将工作不正常。
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2
MAKE_SET(bits,str1,str2,...)
返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对应位1，等等。在str1, str2, ...中的NULL串不添加到结果中。
mysql> SELECT MAKE_SET(1,'a','b','c');
        -> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
        -> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
        -> ''
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个 “off”字符串。每个字符串用“separator”分隔(缺省“,”)，并且只有“bits”的“number_of_bits” (缺省64)位被使用。
mysql> select EXPORT_SET(5,'Y','N',',',4)
        -> Y,N,Y,N
LCASE(str)
　
LOWER(str)
返回字符串str，根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成小写。该函数对多字节是可靠的。
mysql> select LCASE('QUADRATICALLY');
        -> 'quadratically'
UCASE(str)
　
UPPER(str)
返回字符串str，根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成大写。该函数对多字节是可靠的。
mysql> select UCASE('Hej');
        -> 'HEJ'
该函数对多字节是可靠的。

LOAD_FILE(file_name)
读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。
mysql> UPDATE table_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;

MySQL必要时自动变换数字为字符串，并且反过来也如此：

mysql> SELECT 1+"1";
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
如果你想要明确地变换一个数字到一个字符串，把它作为参数传递到CONCAT()。

如果字符串函数提供一个二进制字符串作为参数，结果字符串也是一个二进制字符串。被变换到一个字符串的数字被当作是一个二进制字符串。这仅影响比较。