看《Manage and Using Mysql》的笔记

今天的无线上网卡借了出去，也刚好有几个SQL语句不知道怎么写为好。其实用MySQL这么久了，还一直都没有系统的看过介绍和使用MySQL的书，大部分情况下是使用phpMysqlAdmin来管理，有些SQL语句也是简单的查询了官方的手册。

于是翻出了还是02年做完毕业设计就在我电脑上放着的O’Reilly 2001的《Manage & Using MySQL》不过还是草稿版本，不是正式版本，针对的MySQL还是3.22.29版本。

真是开卷有意呀，看到第30多页的时候，就解决了困扰我的一个问题，其实问题很简单，看来自己还是对SQL语言，MySQL不够了解，因此趁机做一下笔记，把觉得比较好的地方，或者将来可能用得到的都记录下来。

1）字符类型存储空间的解释

MySQL中有各种字符类型，以前我基本上都是使用varchar类型，偶尔使用text。后来看到一个文章说使用varchar对搜索速度有一定的影响，因为不是定长，因此后来就把varchar替换成了char类型。下面的表对常见的字符类型的存储空间给出了一个实例，值得一看。

2）MySQL支持多行一次性插入。

这是一个非标准的支持，如果你要考虑兼容和移植，那就不要使用了，给出例子： INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39), (NULL, 'Bananas', 122, 0, 4, 29), (NULL, 'Liver', 232, 3, 15, 10)

3）MySQL还支持另外一种非标注的插入模式，那就是你可以一起制定列名和其值，有点类似update的语法，给出例子： INSERT INTO book SET title=’The Vampire Lestat’, author=’Anne Rice’;

4)对null值的特殊操作符

MySQL对null的操作除了标准操作符以外，还特意提供了称之为null-safe的操作符<=>，只有两个操作数同时为null或者同时不为null时才返回真。给出例子：

 mysql> SELECT 1 <=> NULL, NULL <=> NULL, 1 <=> 1;
+------------+---------------+---------+ 
| 1 <=> NULL | NULL <=> NULL | 1 <=> 1 | 
+------------+---------------+---------+ 
| 0 | 1 | 1 |
+------------+---------------+---------+ 
1 row in set (0.00 sec)

5)支持Perl编程和grep用法的正规表达式来增强模式匹配。

在标准SQL语句中，能用于模式匹配的操作符是LIKE，NOT LIKE。MySQL扩展了这个功能，采用了类似Perl语法和grep命令语法的正规表达式来做模式匹配。语法规则如下：

他的使用方法是将like替换成regexp操作符，给出几个例子：

 mysql> SELECT * FROM BOOK;
 +---------+-----------------------------------------+---------------+
 | BOOK_ID | TITLE | AUTHOR |
 +---------+-----------------------------------------+---------------+
 | 1 | Database Programming with JDBC and Java | George Reese |
 | 2 | JavaServer Pages | Hans Bergsten |
 | 3 | Java Distributed Computing | Jim Farley |
 +---------+-----------------------------------------+---------------+
 3 rows in set (0.01 sec)

– 查询包含Java的书名

 mysql> SELECT TITLE FROM BOOK WHERE TITLE REGEXP 'Java';
 +-----------------------------------------+
 | TITLE |
 +-----------------------------------------+
 | Database Programming with JDBC and Java |
 | JavaServer Pages |
 | Java Distributed Computing |
 +-----------------------------------------+
 3 rows in set (0.06 sec)

– 查询以Java开头的书名

 mysql> SELECT TITLE FROM BOOK WHERE TITLE REGEXP '^Java';
 +----------------------------+
 | TITLE |
 +----------------------------+
 | JavaServer Pages |
 | Java Distributed Computing |
 +----------------------------+
 2 rows in set (0.01 sec)

– 查询以Java结尾的书名

 mysql> SELECT TITLE FROM BOOK WHERE TITLE REGEXP 'Java$';
 +-----------------------------------------+
 | TITLE |
 +-----------------------------------------+
 | Database Programming with JDBC and Java |
 +-----------------------------------------+
 1 row in set (0.00 sec)

6）一些与日期有关的函数

from_unixtime()：将unix时间戳转换成友好的日期格式,参数为记录unix时间戳的整数。

mysql> select * from ss; 
+----+------------+ 
| id | pubtime |
+----+------------+ 
| 1 | 1166064340 | 
+----+------------+ 
1 row in set(0.27 sec) 

mysql> select from_unixtime(pubtime) from ss;
+------------------------+ 
| from_unixtime(pubtime) |
+------------------------+ 
| 2006-12-14 10:45:40 |
+------------------------+ 
1 row in set (0.00 sec)

rand(): 取[0,1]之间的随机数;round()：和其他语言中的round函数类似功能。 – 随机打印[1,35]之间的数

mysql> select round(rand()*34 + 1);
+----------------------+ 
| round(rand()*34 + 1) |
+----------------------+ 
| 14 | 
+----------------------+ 
1 row in set (0.00 sec) 
mysql> select round(rand()*34 + 1);
+----------------------+ 
| round(rand()*34 + 1) |
+----------------------+ 
| 18 | 
+----------------------+ 
1 row in set (0.00 sec)

to_days(): 返回从公元元年开始到现在经过的天数，接受的参数可以是unix时间戳类型，也可以是datetime或者date类型（e.g 2006-01-01)。

 mysql> select to_days(now()) - to_days('2006-12-01') as days;

+------+ 
| days | 
+------+ 
| 13 | 
+------+ 
1 row in set (0.00 sec)

DATE_FORMAT(date, format)：返回指定格式的日期。

 mysql> SELECT DATE_FORMAT('1969-02-17', '%W, %M %D, %Y');
+--------------------------------------------+ 
|DATE_FORMAT('1969-02-17', '%W, %M %D, %Y') |
+--------------------------------------------+ 
| Monday, February 17th,1969 
| +--------------------------------------------+ 
1 row in set (0.39sec)

下表给出了该函数能接受那些指定的格式：

%a Short weekday name (Sun, Mon, etc.)
%b Short month name ( Jan, Feb, etc.)
%D Day of the month with ordinal suffix (1st, 2nd, 3rd, etc.)
%d Day of the month
%H 24-hour hour (always two digits, e.g., 01)
%h/%I 12-hour hour (always two digits, e.g., 09)
%i Minutes
%j Day of the year
%k 24-hour hour (one or two digits, e.g., 1)
%l 12-hour hour (one or two digits, e.g., 9)
%M Name of the month
%m Number of the month (January is 1).
%p AM or PM
%r 12-hour total time (including AM/PM)
%S Seconds (always two digits, e.g., 04)
%s Seconds (one or two digits, e.g., 4)
%T 24-hour total time
%U Week of the year (new weeks begin on Sunday)
%W Name of the weekday
%w Number of weekday (0 is Sunday)
%Y Four digit year
%y Two digit year
%% A literal “%” character.

类似日期的函数，MySQL还提供了很多。这个链接可以看到目前MySQL提供的全部日期函数。

7) load:从文件中导入数据

这个命令以前用过，还利用它做过坏事，呵呵。只是比较难得记住他的用法，因此也记录下来。 load命令允许你从一个仅仅包含数据的文件中导入数据。 load local infile 'books.dat' into table books 这个命令假设文件books.dat的内容是每一行对应一条记录，其中值之前用tab键分隔，空置(null)用N表示。 local 关键字表示告诉mysql命令行加载的文件和发出该命令的客户端是同一台机器。否则，它会在服务器上寻找该文件，当然你可以这样做，不过你需要得到相关的授权。同时要记住的是非本地加载的文件，默认是从数据库安装目录开始寻找的(在Linux里，默认是/var/lib/mysq/下）。这和后面要说的导出文件存放的位置规则是一致的。如果你的文件不是用tab键分隔的，而是别的字符，比如逗号(,) 。那么你可以制定分隔符号来导入数据。

LOAD DATA LOCAL INFILE ’books.dat’ INTO TABLE BOOK FIELDS TERMINATED BY ’,’;

如果文件中包含的数据和数据库中已有的数据相冲突，那么你可以使用关键字replace 或者ignore来指定其行为，默认是ignore。

LOAD DATA LOCAL INFILE 'books.dat' REPLACE INTO TABLE BOOK

8)从数据库中导出数据到文件(Pulling from MySQL)

MySQL允许将select出来的结果写入到文件 SELECT * INTO OUTFILE ’books.dat’ FROM BOOK;

如果你并不希望用tab键分隔值，而是希望用的符号，比如逗号(,)来分隔，这样的好处是一些电子表格程序可以处理这些文件。那么你可以这样做：

SELECT * INTO OUTFILE ’books.dat’ FIELDS TERMINATED BY ’,’ FROM BOOK;

同时，你可以将每一个值用引号包含起来

SELECT * INTO OUTFILE ’books.dat’ FIELDS ENCLOSED BY ’"’ TERMINATED BY ’,’ FROM BOOK;

当然，你可以考虑只有对类型是字符串类型的域的值用引号，其他就不必了。那么可以使用这样的命令

SELECT * INTO OUTFILE ’books.dat’ FIELDS OPTIONALLY ENCLOSED BY ’"’ TERMINATED BY ’,’ FROM BOOK;

更详细的解释可以参考官方手册。

Linux|系统管理|WEB开发

关注Linux,系统管理,WEB开发以及开源世界

Comments