msyql常用大全3-高级应用

 2. MYSQL
2.1. 连接MYSQL
格式： mysql -h主机地址 -u用户名 -p用户密码
连接远程机器：
E:/mysql>mysql -h10.4.3.188 -uptsdb -p
等价写法
E:/mysql>mysql --host=10.4.3.188 --user=ptsdb --password
连接本地机器：
E:/mysql>mysql -uroot -p
等价写法
E:/mysql>mysql --user=root -password
（注:u与root可以不用加空格，其它也一样）
注意事项：环境变量path 里面要设定mysql的bin的路径：
C:/Program Files/MySQL/MySQL Server 5.0/bin
2.2. 修改密码
方法一：使用mysqladmin
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例1：E:/mysql>mysqladmin -uroot password root
注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
例2：再将root的密码改为root123。
E:/mysql>mysqladmin -uroot -proot password root123
方法二：直接更新 user 表
mysql>UPDATE user SET password=PASSWORD("test123") WHERE user='test';
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR test=PASSWORD('test123');
mysql> FLUSH PRIVILEGES;
方法三：使用 grant
格式：grant 权限 on 数据库.表格| 其他 to 用户@主机 IDENTIFIED BY 口令
例1：给test用户在本地localhost 所有权限(除了GRANT OPTION)，口令为 test
(相当于修改了test 用户的口令)
mysql>grant all on *.* to test@localhost identified by "test";
等同于
mysql>grant all on *.* to test @localhost identified by PASSWORD " *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 ";

例2、增加一个用户test密码为abc，让他可以在任何主机上登录，并对test数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL，然后键入以下命令：
mysql>grant select,insert,update,delete on test.* to test@"%" Identified by "abc";

在mysql.user 表中，有两个test 用户
一个test 用户，在本地有所有的权限
另外的test 用户，在所有主机上有增删改查权限

同样可以取消部分权限（全部）
mysql>revoke insert,update,delete on test.* from test@"%"
mysql>REVOKE ALL PRIVILEGES, GRANT OPTION FROM test@"%"
然后 mysql> FLUSH PRIVILEGES;

Test 用户不再使用用了，也可以删除
mysql>Delete from user where user='test' and host='%'
mysql> FLUSH PRIVILEGES;

注意：例2增加的用户是比较危险的，你想如某个人知道test的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据库test为所欲为了(可以通过限定主机)
mysql>grant select,insert,update,delete on test.* to test@"IP地址" Identified by "abc";
2.3. 显示命令
显示数据库列表:
mysql>show databases;
mysql>show schemas; --mysql 5.0.2
显示表格
mysql>show tables from mydb;
显示表格状态
Mysql>SHOW TABLE STATUS;
显示字符集:
mysql> SHOW CHARACTER SET;
显示创建表:
mysql> show create table quote;
显示用户权限:
mysql> SHOW GRANTS FOR 'test'@'localhost';
mysql>SHOW GRANTS;
mysql>SHOW GRANTS FOR CURRENT_USER;
mysql>SHOW GRANTS FOR CURRENT_USER();
显示index:
mysql>SHOW INDEX FROM mydb.mytable;
显示表结构:
mysql>desc mydb.tablename;
mysql>show columns from mydb.tablename;
显示MySQL数据库的版本：
mysql>select version();
显示函数
mysql>Select * from mysql.func;
显示存储过程
mysql>Select * from mysql.proc;
显示存储引擎
mysql> SHOW ENGINES;
显示变量:
mysql>SHOW VARIABLES;
显示状态:
Mysql> SHOW STATUS;
显示进程
Mysql>SHOW PROCESSLIST
显示 INNODB 状态
Mysql>SHOW INNODB STATUS
显示连接状态
Mysql>SHOW STATUS LIKE '%CONNECT%';
显示线程状态
Mysql>SHOW STATUS LIKE '%THREAD%';

等等..

2.4. 创建.修改.删除
2.4.1. 创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]

create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
例如:
CREATE DATABASE IF NOT EXISTS ddd --如果不存在,则创建.
CHARACTER SET 'ujis' --设定字符集
COLLATE 'ujis_japanese_ci';
2.4.2. 创建表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
例子:
CREATE TABLE if not exists `Admin_User` (
`id` int(11) NOT NULL auto_increment, --PRIMARY KEY，
`livedoorId` varchar(255) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`auth` int(11) default '0',
PRIMARY KEY (`id`) --设定主健
) ENGINE=MyISAM DEFAULT CHARSET=ujis ?设定字符集
ENGINE=MyISAM 默认存储引擎
The binary portable storage engine that is the default storage engine used by MySQL
对于每个MyISAM 存储引擎的表,在硬盘上存在3个文件
File Purpose
tbl_name.frm Table format (definition) file
tbl_name.MYD Data file
tbl_name.MYI Index file

ENGINE= InnoDB
Transaction-safe tables with row locking and foreign keys.
ENGINE = BDB
Transaction-safe tables with page locking.
还有其他的内存引擎 MEMORY 归档 ARCHIVE 等等
ISAM 不再使用了

2.4.3. 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]

将customer 表的name 字段前十个字符做为索引
CREATE INDEX part_of_name ON customer (name(10));
MYSQL 5.0 特性
存储引擎为 MyISAM, InnoDB, or BDB 的表格上,可以在有null值的字段上创建索引
存储引擎为 MyISAM, InnoDB, or BDB 的表格上,可以在BLOB TEXT 上创建索引
只有在MyISAM 类型表格上,可以在CHAR, VARCHAR, and TEXT 字段类型上创建FULLTEXT 索引
Storage Engine Allowable Index Types
MyISAM BTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
可以指定索引类型

Example:
CREATE TABLE testtable (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON testtable (id);
2.4.4. 修改表
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
IGNORE 忽略主健重复的错误,如果重复,采用第一条,其余删除
例子:同时多个操作
mysql> ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
例子, 重命名 INTEGER 字段,从 a 到 b:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
例子 修改字段类型,仍然需要新旧字段名称,即使字段名称相同:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
也可以使用modify
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

在mysql 5.0 可以使用FIRST or AFTER 字段来添加add 字段,默认是在最后
modify change 也可以使用
在mysql 5.0 InnoDB 存储引擎支持 ALTER TABLE 删除外健:
mysql>ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

例子:
创建表
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
重命名表
mysql> ALTER TABLE t1 RENAME t2;
MODIFY a 字段为 TINYINT NOT NULL , 并且 change 字段 b,从 CHAR(10) 到 CHAR(20) 并改名为c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
添加新字段 d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
在a d 上增加索引:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);
删除字段c:
mysql> ALTER TABLE t2 DROP COLUMN c;
添加一个自动增长的字段c ,并且添加c 为主健:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> ADD PRIMARY KEY (c);
2.4.4.1. 修改外键
语法:
ALTER TABLE tbl_name
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
例子:
create table aa (id1 int not null,name varchar(20),primary key(id1))type=InnoDB;
create table b(id2 int not null,lessonname varchar(20),primary key(id2))type=InnoDB;
alter table b add FOREIGN KEY id (id2) references aa(id1);

2.4.5. 删除
删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
例子: mysql>drop DATABASE IF EXISTS testdb;
删除表
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
例子: mysql>drop TABLE IF EXISTS testTable;
删除索引
DROP INDEX index_name ON tbl_name
例子: mysql>drop index testIndex on testTable;
重命名
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
例子:RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
2.5. 数据库备份恢复
数据库备份
数据库备份命令：
mysqldump --opt --user=用户名 --password=密码 --default_character-set=字符集 -B数据库> 输出的sql文件
例子:
E:/mysql>Mysqldump --user=ptsdb --password=ptsdb --default_character-set=ujis --opt pts>dump.sql
参见批处理文件

数据库导入命令：
mysql --user=用户名 --password=密码 --default_character-set=字符集 [数据库]<导入的sql 语句

E:/mysql>mysql -uptsdb -pptsdb --default-character-set=ujis

例子： 对于InnoDB（没有设定字符集）
Following mysqldump import example for InnoDB tables is at least 100x faster than previous examples.
1. mysqldump --opt --user=username --password database > dumbfile.sql
2. Edit the dump file and put these lines at the beginning:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
3. Put these lines at the end:
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
4. mysql --user=username --password database < dumpfile.sql

参数说明：
--add-drop-database
Add a DROP DATABASE statement before each CREATE DATABASE statement.

--add-drop-table
Add a DROP TABLE statement before each CREATE TABLE statement.

--all-databases, -A
Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.
--databases, -B
Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name
and following names as table names. With this option, it treats all name arguments as database names.
CREATE DATABASE IF NOT EXISTS db_name and USE db_name statements are included in the output before each new database.

--host=host_name, -h host_name
Dump data from the MySQL server on the given host. The default host is localhost.
--opt
This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options
--disable-keys --extended-insert --lock-tables --quick --set-charset.
It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.
In MySQL 5.0, --opt is on by default, but can be disabled with --skip-opt.
To disable only certain of the options enabled by --opt, use their --skip forms;
for example, --skip-add-drop-table or --skip-quick.
还有一些其他参数，有兴趣可以学习
2.6. 表数据备份
mysql>use test;
mysql> CREATE TABLE imptest(id INT, n VARCHAR(30));
Query OK, 0 rows affected (0.03 sec)
方法一：
导出使用：Mysqldump
E:/mysql>mysqldump -uptsdb -pptsdb -where "id>='100'"
test imptest
E:/mysql>mysqldump -uptsdb -pptsdb test imptest>e:/mysql/imp/imptest2.txt
导入使用 mysql
mysql -uptsdb -pptsdb< imptest2.txt
方法二：
导出使用 select into OUTFILE
mysql> select * from imptest where id=101 into OUTFILE 'e://mysql//imp//test3.txt' FIELDS TERMINATED BY ',';

导入使用 LOAD DATA INFILE
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]]
例子：
mysql> LOAD DATA INFILE 'e://mysql//imp//test3.txt' INTO TABLE imptest FIELDS TERMINATED BY ',';

导入使用mysqlimport：
E:/mysql>mysqlimport -uptsdb -pptsdb --local test E:/mysql/imp/imp.txt
mysqlimport: Error: Table 'test.imp' doesn't exist, when using table: imp

E:/mysql>mysqlimport -uptsdb -pptsdb --local test E:/mysql/imp/imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
注意：文件名必须跟表名相同
参数
-d or --delete 新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force 不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore mysqlimport跳过或者忽略那些有相同唯一
-r or -replace 这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录
2.7. 数据管道导入导出
E:/mysql>mysql -h10.5.1.66 -uroot -proot --default-character-set=name frontdb_20060415(databasename)
>e:/mysql/test.txt (输出的sql 语句)

1:>;create databases newname(在新的server上建立空的数据库)
2:#/usr/local/mysql/bin/mysqldump databasename >*.sql(在旧的服务器上导出数据库)
3:#/usr/local/mysql/bin/mysql databasename < *.sql(在新的服务器上导入*.sql)
注意数据库名要一一对应.
2.8. 各种字段的取值范围
TINYINT 1 byte
-128 - 127
TINYINT UNSIGNED 1 byte
0 ? 255 即0-（28-1）
SMALLINT 2 bytes
-32768 - 32767 即-215至(215-1)
SMALLINT UNSIGNED 2 bytes
0 - 65535 即0至(216-1)
MEDIUMINT 3 bytes
-8388608 - 8388607 即?223至(223-1)
MEDIUMINT UNSIGNED 3 bytes
0 - 16777215 即0至(224-1)
INT 或 INTEGER 4 bytes
-2147483648 - 2147483647 即?231至(231-1)
INT UNSIGNED 或 INTEGER UNSIGNED 4 bytes
0 - 4294967295 即0至(232-1)
BIGINT 8 bytes
-9223372036854775808 - 9223372036854775807 即?263至(263-1)
BIGINT UNSIGNED 8 bytes
0 - 18446744073709551615 即0至(264-1)
FLOAT 4 bytes
-3.402823466E+38 - -1.175494351E-38
0
1.175494351E-38 - 3.402823466E+38
DOUBLE 或 DOUBLE PRECISION 或 REAL 8 bytes
-1.7976931348623157E+308 - -2.2250738585072014E-308
0
2.2250738585072014E-308 - 1.7976931348623157E+308
DECIMAL[(M,[D])] 或 NUMERIC(M,D) 不定
由M(整个数字的长度,包括小数点,小数点左边的位数,小数点右边的位数,但不包括负号)和
D(小数点右边的位数)来决定,M缺省为10,D缺省为0
DATE 3 bytes
1000-01-01 - 9999-12-31
DATETIME 8 bytes
1000-01-01 00:00:00 - 9999-12-31 23:59:59
TIMESTAMP 4 bytes
1970-01-01 00:00:00 - 2037-12-31 23:59:59
TIME 3 bytes
-838:59:59' to 838:59:59
YEAR[(2|4)] 1 byte
缺省为4位格式,4位格式取值范围为1901 - 2155,0000,2位格式取值范围为70-69(1970-2069)
CHAR(M) [BINARY] 或 NCHAR(M) [BINARY] M bytes
M的范围为1 - 255,如果没有BINARY项,则不分大小写,NCHAR表示使用缺省的字符集.在数据库 中以空格补足,但在取出来时末尾的空格将被去掉.
[NATIONAL] VARCHAR(M) [BINARY]
Before 5.0.3 M的范围为0?255 L+1 bytes L<=M
5.0.3 以及以后 M 范围为 0-65535
L+1 bytes L<=M 0<=M<=256
L+2 bytes L<=M 256<=65535
在数据库中末尾的空格将自动去掉.
TINYBLOB 或 TINYTEXT L+1 bytes
255(2^8-1)个字符
BLOB 或 TEXT L+2 bytes
65535(2^16-1)个字符
MEDIUMBLOB 或 MEDIUMTEXT L+3 bytes
16777215 (2^24-1)个字符
LONGBLOB 或 LONGTEXT L+4 bytes
4294967295 (2^32-1)个字符
ENUM('value1','value2',...) 1 or 2 bytes
可以总共有65535个不同的值
SET('value1','value2',...) 1/2/3/4/8 bytes
最多有64个成员
2.9. 查询
2.9.1. limit
LIMIT子句可以用来限制由SELECT语句返回过来的数据数量，它有一个或两个参数，如果给出两个参数，
第一个参数指定返回的第一行在所有数据中的位置，从0开始（注意不是1），第二个参数指定最多返回行
数。例如：
select * from table LIMIT 5,10; #返回第6-15行数据
select * from table LIMIT 5; #返回前5行
select * from table LIMIT 0,5; #返回前5行
2.9.2. join 详解
还是先 Create table 吧

create table emp(
id int not null primary key,
name varchar(10)
);

create table emp_dept(
dept_id varchar(4) not null,
emp_id int not null,
emp_name varchar(10),
primary key (dept_id,emp_id));

insert into emp() values
(1,"Dennis-1"),
(2,"Dennis-2"),
(3,"Dennis-3"),
(4,"Dennis-4"),
(5,"Dennis-5"),
(6,"Dennis-6"),
(7,"Dennis-7"),
(8,"Dennis-8"),
(9,"Dennis-9"),
(10,"Dennis-10");

insert into emp_dept() values
("R&D",1,"Dennis-1"),
("DEv",2,"Dennis-2"),
("R&D",3,"Dennis-3"),
("Test",4,"Dennis-4"),
("Test",5,"Dennis-5");
("dddd",20,"eeee");
>> left join
-------------
select a.id,a.name,b.dept_id
from emp a left join emp_dept b on (a.id=b.emp_id);

# 挑出左边的 table emp 中的所有资料,即使 emp_dept 中没有的资料也挑出来,没有的就用 NULL 来显示,
# 也即显示资料是以左边的 table emp 中的资料为基础

mysql> select a.id,a.name,b.dept_id
-> from emp a left join emp_dept b on (a.id=b.emp_id);
+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Dennis-1 | R&D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# 挑出 table emp 中有而 table emp_dept 中没有的人员资料
select a.id,a.name,b.dept_id
from emp a left join emp_dept b on (a.id=b.emp_id)
where b.dept_id IS NULL;

mysql> select a.id,a.name,b.dept_id
-> from emp a left join emp_dept b on (a.id=b.emp_id)
-> where b.dept_id IS NULL;
+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+

# 把 table emp_dept 放在左边的情形(当然以 emp_dept 中的数据为基础来显示资料,emp 中比emp_dept 中多的资料也就不会显示出来了):
select a.id,a.name,b.dept_id
from emp_dept b left join emp a on (a.id=b.emp_id);
mysql> select a.id,a.name,b.dept_id
-> from emp_dept b left join emp a on (a.id=b.emp_id);
+------+----------+---------+
| id | name | dept_id |
+------+----------+---------+
| NULL | NULL | dddd |
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+------+----------+---------+
>> right join
---------------
select a.id,a.name,b.dept_id
from emp a right join emp_dept b on (a.id=b.emp_id);
# 挑资料时以右边 table emp_dept 中的资料为基础来显示资料
mysql> select a.id,a.name,b.dept_id
-> from emp a right join emp_dept b on (a.id=b.emp_id);
+------+----------+---------+
| id | name | dept_id |
+------+----------+---------+
| NULL | NULL | dddd |
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+------+----------+---------+
6 rows in set (0.00 sec)
# 我们再把 table 的位置交换一下,再用 right join 试试

select a.id,a.name,b.dept_id
from emp_dept b right join emp a on (a.id=b.emp_id);

mysql> select a.id,a.name,b.dept_id
-> from emp_dept b right join emp a on (a.id=b.emp_id);
+----+-----------+---------+
| id | name | dept_id |
+----+-----------+---------+
| 1 | Dennis-1 | R&D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+----+-----------+---------+
# 是不是和 left join 一样了?
>> inner join STRAIGHT_JOIN
select a.id,a.name,b.dept_id
from emp a ,emp_dept b
where a.id=b.emp_id;

mysql> select a.id,a.name,b.dept_id
-> from emp a ,emp_dept b
-> where a.id=b.emp_id;
+----+----------+---------+
| id | name | dept_id |
+----+----------+---------+
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R&D |
| 3 | Dennis-3 | R&D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+----+----------+---------+
2.9.3. 别名 alias
你可以在GROUP BY、ORDER BY或在HAVING部分中使用别名引用列。别名也可以用来为列取一个更好点的名字：
SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0;
SELECT id AS "Customer identity" FROM table_name;
注意，你的 ANSI SQL 不允许你在一个WHERE子句中引用一个别名。这是因为在WHERE代码被执行时，列值还可能没有终结。例如下列查询是不合法：
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;
WHERE语句被执行以确定哪些行应该包括GROUP BY部分中，而HAVING用来决定应该只用结果集合中的哪些行。
2.9.4. 正则
正则表达式（regex）是定义复杂查询的一个强有力的工具。
这里是一个简单的资料，它忽略了一些详细的信息。
正则表达式定义了一个字符串的规则。最简单的正则表达式不包含任何保留字。例如，

正则表达式hello只和字符串“hello”匹配。
一般的正则表达式使用了某些特殊的结构，所以它能匹配更多的字符串。例如，正则
表达式hello|word既能匹配字符串“hello”也能匹配字符串“word”。
举一个更复杂一点的例子，正则表达式B[an]*s可以匹配字符串“Bananas”、“Baaaaa
s”
、“Bs”以及其他任何以B开头以s结尾的字符串，中间可以包括任意个a和任意个n的组
合。
一个正则表达式中的可以使用以下保留字
^
所匹配的字符串以后面的字符串开头
mysql> select "fonfo" REGEXP "^fo$"; -> 0（表示不匹配）
mysql> select "fofo" REGEXP "^fo"; -> 1（表示匹配）
$
所匹配的字符串以前面的字符串结尾
mysql> select "fono" REGEXP "^fono$"; -> 1（表示匹配）
mysql> select "fono" REGEXP "^fo$"; -> 0（表示不匹配）
..
匹配任何字符（包括新行）
mysql> select "fofo" REGEXP "^f.*"; -> 1（表示匹配）
mysql> select "fonfo" REGEXP "^f.*"; -> 1（表示匹配）
a*
匹配任意0-n多个a（包括空串）
mysql> select "Ban" REGEXP "^Ba*n"; -> 1（表示匹配）
mysql> select "Baaan" REGEXP "^Ba*n"; -> 1（表示匹配）
mysql> select "Bn" REGEXP "^Ba*n"; -> 1（表示匹配）
a+
匹配任意1-n多个a（不包括空串）
mysql> select "Ban" REGEXP "^Ba+n"; -> 1（表示匹配）
mysql> select "Bn" REGEXP "^Ba+n"; -> 0（表示不匹配）
a?
匹配0-1个a
mysql> select "Bn" REGEXP "^Ba?n"; -> 1（表示匹配）
mysql> select "Ban" REGEXP "^Ba?n"; -> 1（表示匹配）
mysql> select "Baan" REGEXP "^Ba?n"; -> 0（表示不匹配）
de|abc
匹配de或abc
mysql> select "pi" REGEXP "pi|apa"; -> 1（表示匹配）
mysql> select "axe" REGEXP "pi|apa"; -> 0（表示不匹配）
mysql> select "apa" REGEXP "pi|apa"; -> 1（表示匹配）
mysql> select "apa" REGEXP "^(pi|apa)$"; -> 1（表示匹配）
mysql> select "pi" REGEXP "^(pi|apa)$"; -> 1（表示匹配）
mysql> select "pix" REGEXP "^(pi|apa)$"; -> 0（表示不匹配）
(abc)*
匹配任意多个（0-n个）abc（包括空串)
mysql> select "pi" REGEXP "^(pi)*$"; -> 1（表示匹配）
mysql> select "pip" REGEXP "^(pi)*$"; -> 0（表示不匹配）
mysql> select "pipi" REGEXP "^(pi)*$"; -> 1（表示匹配）
{1}
{2,3}
这是一个更全面的方法，它可以实现前面好几种保留字的功能
a*
可以写成a{0,}
a+
可以写成a{1,}
a?
可以写成a{0,1}
在{}内只有一个整型参数i，表示字符只能出现i次；在{}内有一个整型参数i，
后面跟一个“，”，表示字符可以出现i次或i次以上；在{}内只有一个整型参数i，
后面跟一个“，”，再跟一个整型参数j,表示字符只能出现i次以上，j次以下
（包括i次和j次）。其中的整型参数必须大于等于0，小于等于 RE_DUP_MAX（默认是25
5）。
如果有两个参数，第二个必须大于等于第一个
[a-dX]
匹配“a”、“b”、“c”、“d”或“X”
[^a-dX]
匹配除“a”、“b”、“c”、“d”、“X”以外的任何字符。
“[”、“]”必须成对使用
mysql> select "aXbc" REGEXP "[a-dXYZ]"; -> 1（表示匹配）
mysql> select "aXbc" REGEXP "^[a-dXYZ]$"; -> 0（表示不匹配）
mysql> select "aXbc" REGEXP "^[a-dXYZ]+$"; -> 1（表示匹配）
mysql> select "aXbc" REGEXP "^[^a-dXYZ]+$"; -> 0（表示不匹配）
mysql> select "gheis" REGEXP "^[^a-dXYZ]+$"; -> 1（表示匹配）
mysql> select "gheisa" REGEXP "^[^a-dXYZ]+$"; -> 0（表示不匹配）
------------------------------------------------------------
[[.characters.]]
表示比较元素的顺序。在括号内的字符顺序是唯一的。但是括号中可以包含通配符,
所以他能匹配更多的字符。举例来说：正则表达式[[.ch.]]*c匹配chchcc的前五个字符
。
[=character_class=]
表示相等的类，可以代替类中其他相等的元素，包括它自己。例如，如果o和(+)是
一个相等的类的成员，那么[[=o=]]、[[=(+)=]]和[o(+)]是完全等价的。
[:character_class:]
在括号里面，在[:和:]中间是字符类的名字，可以代表属于这个类的所有字符。
字符类的名字有: alnum、digit、punct、alpha、graph、space、blank、lower、uppe
r、cntrl、print和xdigit
mysql> select "justalnums" REGEXP "[[:alnum:]]+"; -> 1（表示匹配）
mysql> select "!!" REGEXP "[[:alnum:]]+"; -> 0（表示不匹配）
[[:<:>
[[:>:]]
分别匹配一个单词开头和结尾的空的字符串，这个单词开头和结尾都不是包含在alnum中
的字符也不能是下划线。
mysql> select "a word a" REGEXP "[[:<:>:]]"; -> 1（表示匹配）
mysql> select "a xword a" REGEXP "[[:<:>:]]"; -> 0（表示不匹配）
mysql> select "weeknights" REGEXP "^(wee|week)(knights|nights)$"; -> 1（表示
匹配）
2.9.5. Select 中使用 IF Statement
mysql> select * from test;
+------+------+------+-------+
| dept | id | sex | name |
+------+------+------+-------+
| 1 | 1 | 0 | wang |
| 2 | 2 | 1 | zhang |
| 3 | 3 | 0 | li |
+------+------+------+-------+
3 rows in set (0.00 sec)
mysql> select dept,id,if(sex=0,'女','男') sex,name from test;
+------+------+-----+-------+
| dept | id | sex | name |
+------+------+-----+-------+
| 1 | 1 | 女 | wang |
| 2 | 2 | 男 | zhang |
| 3 | 3 | 女 | li |
+------+------+-----+-------+
3 rows in set (0.00 sec)
2.9.6. Select中使用CASE Statement
mysql> select dept,id,(case sex when '0' then '女' else '男' end) as sex,name from test;
+------+------+------+-------+
| dept | id | sex | name |
+------+------+------+-------+
| 1 | 1 | 女 | wang |
| 2 | 2 | 男 | zhang |
| 3 | 3 | 女 | li |
+------+------+------+-------+
mysql> select (case dept when '1' then 'no1' when '2' then 'no2' else 'other' end) as dept from test;
+-------+
| dept |
+-------+
| no1 |
| no2 |
| other |
+-------+
3 rows in set (0.00 sec)
注意: 相当于Oracle 中的decode 和case when
在统计报表中很有用处

2.10. 存储过程和函数
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
Valid SQL procedure statement or statements

例子：创建一个过程hello，显示问候语
mysql> delimiter ;
mysql> drop PROCEDURE if exists hello;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE hello (IN s varchar(20))
-> BEGIN
-> SELECT CONCAT('Hello,',s,'!') as hello;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call hello('wangyl');
+---------------+
| hello |
+---------------+
| Hello,wangyl! |
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
例子2: 查询t表的记录总数
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)

例子：创建一个函数
mysql> delimiter ;
mysql> drop FUNCTION if exists dateFunction;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE FUNCTION dateFunction (iCase int)
-> RETURNS varchar(50)
-> begin
-> DECLARE iType int;
-> DECLARE sReturn varchar(50);
-> set iType =icase +1;
-> case iType
-> when 1 then select DATE_FORMAT(NOW(),'%Y-%m-%d') into sReturn;
-> when 2 then select DATE_FORMAT(NOW(),'%W %M %Y') into sReturn;
-> else
-> select NOW() into sReturn;
-> end case;
-> return sReturn;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select dateFunction(0);
+-----------------+
| dateFunction(0) |
+-----------------+
| 2005-11-14 |
+-----------------+
1 row in set (0.00 sec)

mysql> select dateFunction(1);
+----------------------+
| dateFunction(1) |
+----------------------+
| Monday November 2005 |
+----------------------+
1 row in set (0.00 sec)

mysql> select dateFunction(2);
+---------------------+
| dateFunction(2) |
+---------------------+
| 2005-11-14 15:05:43 |
+---------------------+
1 row in set (0.00 sec)

2.11. 补充：trigger
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END|

DELIMITER ;

INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

If you insert the following values into table test1 as shown here:

mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

Then the data in the four tables will be as follows:

mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)

2.12. 删除 DELETE Syntax
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
对于删除多个表,不能使用order by 和limit
多表删除语法一: ,只是在删除 在from 前面的表所中匹配的记录
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
从t1,t2,t3 表选出要删除的记录,只是删除t1,t2 表中所匹配得这些记录.
多表删除语法二: 在from 列出的表中删除选中的记录.
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
从t1,t2,t3 表选出要删除的记录(使用using ),只是删除t1,t2 表中所匹配得这些记录.

注意: 如果您使用一个别名,您必须使用别名.

DELETE t1 FROM test AS t1, test2 WHERE ...

支持多个数据之间的多表删除,但是在这种情况下,你必须指定表,而不能使用别名:

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...
当前,您不能从相同表的子查询中,删除同一表的数据