MySQL技术手册要点解析-2
13.mysql有些命令是oracle里面没有的，而且这些show命令也不同于sqlplus里面的，这里的show都必须要有分号结尾。
SHOW TABLES;
SHOW DATABASES;
desc table_name;
14.mysql 导入load datainfile其实类似于oracle的sqlloader,也有行分隔、列分隔、可以指定字符集
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';
load data infile '/tmp/t0.txt' into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n' (`name`,`age`,`description`) set update_time=current_timestamp;
15.mysql 可以使用like语句进行匹配，也可以使用rlike进行正则表达式匹配
A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)
SELECT * FROM pet WHERE name REGEXP '^b';
区分大小写的匹配binary
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';
16.执行sql脚本
mysql -h host -u user -p < batch-file
windows上
mysql -e "source batch-file"
17. 关于 BIT_COUNT()&BIT_OR()
BIT_COUNT( expr )：返回 expr 的二进制表达式中”1“的个数。
BIT_OR( expr )：返回 expr 中所有比特的bitwise OR。计算执行的精确度为64比特(BIGINT) 。可以多多行数据进行计算
18.mysql中的序列可以绑定在表定义阶段，其最大取值取决于字段的定义，如果是tinyint最大值127,如果是tinyint unsigned 则最大值255，到达最大值以后就会报错，无法插入。如果插入失败会如何？是否会如同oracle sequence那样的gap?
Use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255.
id MEDIUMINT NOT NULL AUTO_INCREMENT,
19.mysql中 database 和schema完全是等价的，这一点，在oracle里面则是user-schema--account一一对应 .
As defined in the MySQL Glossary:
In MySQL, physically, a schema is synonymous with a database. You can substitute the keywordSCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.
Some other database products draw a distinction. For example, in the Oracle Database product, aschema represents only a part of a database: the tables and other objects owned by a single user
20.mysql 中 INFORMATION_SCHEMA这个schema包括了系统的数据字典视图，这一点和oracle 的sys类似，但是mysql 的并不会为这个数据库创建目录，因为里面没有表，都是视图，用户也无法对这些视图进行insert/update/delete,问题在于，这些视图底层表在哪里？
INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.In effect, we have a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but it is possible only to read the contents of tables. You cannot insert into them, update them, or delete from them.