MySQL CookBook 学习笔记-01

1、使用指定文件，创建表并插入数据：

文件，d:\MySQL_cookbook\limbs.sql

DROP TABLE IF EXISTS limbs;CREATE TABLE limbs(    thing   VARCHAR(20),    # what the thing is    legs    INT,            # number of legs it has    arms    INT             # number of arms it has);INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);INSERT INTO limbs (thing,legs,arms) VALUES('octopus',0,8);INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);

SQL 命令如下：

mysql> use cookbook;mysql> source d:/MySQL_cookbook/limbs.sql;

SELECT 查询验证是否成功：

mysql> select * from limbs;+--------------+------+------+| thing        | legs | arms |+--------------+------+------+| human        |    2 |    2 || insect       |    6 |    0 || squid        |    0 |   10 || octopus      |    0 |    8 || fish         |    0 |    0 || centipede    |  100 |    0 || table        |    4 |    0 || armchair     |    4 |    2 || phonograph   |    0 |    1 || tripod       |    3 |    0 || Peg Leg Pete |    1 |    2 || space alien  | NULL | NULL |+--------------+------+------+

2、指定用户登录，非root:

使用MySQL Command  Line  Client，一进入就是输密码（默认为root用户），根本没有选择用户的过程

Enter password: ****Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 38Server version: 5.0.67-community-nt MySQL Community Edition (GPLType 'help;' or '\h' for help. Type '\c' to clear the buffer.

原来，要能指定用户是通过 cmd 进入的：

C:\>mysql -h localhost -u cbuser -pcbpass -D cookbookWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 41Server version: 5.0.67-community-nt MySQL Community Edition (GType 'help;' or '\h' for help. Type '\c' to clear the buffer.

3、插入 SQL 类型为ENUM 和 SET 的数据（其实和操作字符串一样）

文件，d:/MySQL_cookbook/profile.sql

DROP TABLE IF EXISTS profile;create table profile(idint unsigned not null auto_increment,namechar(20) not null,birthDATE,colorenum('blue', 'red', 'green', 'brown', 'black', 'white'),foodsset('lutefisk', 'burrito', 'curry', 'eggroll', 'fadge', 'pizza'),catsint,primary key (id));INSERT INTO profile (name, birth, color, foods, cats) VALUES('Fred', '1970-04-13', 'black', 'lutefisk,fadge,pizza',  0);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Mort', '1969-09-30', 'white', 'burrito,curry,eggroll', 3);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Brit', '1957-12-01', 'red',   'burrito,curry,pizza',   1);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Carl', '1973-11-02', 'red',   'eggroll,pizza',         4);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Sean', '1963-07-04', 'blue',  'burrito,curry',         5);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Alan', '1965-02-14', 'red',   'curry,fadge',           1);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Mara', '1968-09-17', 'green', 'lutefisk,fadge',        1);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Shepard', '1975-09-02', 'black', 'curry,pizza',        2);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Dick', '1952-08-20', 'green', 'lutefisk,fadge',        0);INSERT INTO profile (name, birth, color, foods, cats) VALUES('Tony', '1960-05-01', 'white', 'burrito,pizza',         0);

4、Using Prepared Statements and Placeholders in Queries

PreparedStatement s;s = conn.prepareStatement ("SELECT id, name, cats FROM profile WHERE cats < ? AND color = ?");s.setInt (1, 2);    s.setString (2, "green");s.executeQuery ( );// ... process result set here ...s.close ( );     // close statement

      One of the benefits of prepared statements and placeholders is that parameter binding operations automatically handle escaping of characters such as quotes and backslashes that you have to worry about yourself if you put the data values into the query yourself.

     Another benefit of prepared statements is that they encourage statement reuse.

     A third benefit is that code that uses placeholder-based queries can be easier to read, although that's somewhat subjective.

5、Including Special Characters and NULL Values in Queries

（单引号[ ' ]；双引号[  "  ]；反斜线[  \  ]；二进制数据[  可能包含：单引号，双引号，反斜线，null]）

       a）：Use placeholders if your API supports them. Generally, this is the preferred method, because the API itself will do all or most of the work for you of providing quotes around values as necessary, quoting or escaping special characters within the data value, and possibly interpreting a special value to map onto NULL without surrounding quotes.

PreparedStatement s;int count;s = conn.prepareStatement ("INSERT INTO profile (name,birth,color,foods,cats) VALUES(?,?,?,?,?)");s.setString (1, "De'Mont");s.setString (2, "1973-01-12");s.setNull (3, java.sql.Types.CHAR);s.setString (4, "eggroll");s.setInt (5, 4);count = s.executeUpdate ( );s.close ( );     // close statement

       b）：Use a quoting function if your API provides one for converting data values to a safe form that is suitable for use in query strings. 

//PHP3还不支持nullfunction sql_quote ($str){    return (isset ($str) ? "'" . addslashes ($str) . "'" : "NULL");}//PHP4后function sql_quote ($str){    return (isset ($str) ? "'" . mysql_escape_string ($str) . "'" : NULL);}unset ($null);  # create a "null" value$stmt = sprintf ("            INSERT INTO profile (name,birth,color,foods,cats)            VALUES(%s,%s,%s,%s,%s)",                sql_quote ("De'Mont"),                sql_quote ("1973-01-12"),                sql_quote ($null),                sql_quote ("eggroll"),                sql_quote (4));$result_id = mysql_query ($stmt, $conn_id);//转换后的SQL语句INSERT INTO profile (name,birth,color,foods,cats)VALUES('De\'Mont','1973-01-12',NULL,'eggroll','4')

6、注意：NULL的特殊性( like '%' ; not like '%' ; regexp '.*' ; not regexp '.*' 全都不能匹配)

如下表和数据：

DROP TABLE IF EXISTS taxpayer;CREATE TABLE taxpayer(    name   VARCHAR(20),    id     VARCHAR(20)    );INSERT INTO taxpayer (name,id) VALUES('bernina', '198-48');INSERT INTO taxpayer (name,id) VALUES('bertha',  NULL);INSERT INTO taxpayer (name,id) VALUES('ben',     NULL);INSERT INTO taxpayer (name,id) VALUES( NULL,     '475-83');INSERT INTO taxpayer (name,id) VALUES( 'baidu',  '111+55');

假设我要查询，id匹配如下模式：“包含-”

mysql> select * from taxpayer where id like '%-%';+---------+--------+| name    | id     |+---------+--------+| bernina | 198-48 || NULL    | 475-83 |+---------+--------+

这里id为NULL的没有匹配（正确），但是如果我用{ id not like '%-%' }，按照一般思维“要么匹配，要么不匹配”应该可以查出所有不匹配的，但实际是id为NULL还是不再查询结果中！

mysql> select * from taxpayer where id not like '%-%';+-------+--------+| name  | id     |+-------+--------+| baidu | 111+55 |+-------+--------+

可见对NULL要做特殊处理，{ id like '%-%' }全集的补集为{ id not like '%-%' or id is NULL }

mysql> select * from taxpayer where id not like '%-%' or id is null;+--------+--------+| name   | id     |+--------+--------+| bertha | NULL   || ben    | NULL   || baidu  | 111+55 |+--------+--------+

正则表达式的处理结果一样，结果如下：

mysql> select * from taxpayer where id regexp '[0-9]{3}\-[0-9]{2}';+---------+--------+| name    | id     |+---------+--------+| bernina | 198-48 || NULL    | 475-83 |+---------+--------+

mysql> select * from taxpayer where id not regexp '[0-9]{3}\-[0-9]{2}';+-------+--------+| name  | id     |+-------+--------+| baidu | 111+55 |+-------+--------+

mysql> select * from taxpayer where id not regexp '[0-9]{3}\-[0-9]{2}' or id is null;+--------+--------+| name   | id     |+--------+--------+| bertha | NULL   || ben    | NULL   || baidu  | 111+55 |+--------+--------+

7、MySQL时区，column为 TIMESTAMP 时，'1970-01-01 00:00:00' 无法插入问题。

（参考：http://www.cnblogs.com/lexus/archive/2010/11/30/1892231.html）

MySQL 时区默认是服务器的时区。
可以通过以下命令查看

mysql> show variables like '%time_zone%';+------------------+--------+| Variable_name    | Value  |+------------------+--------+| system_time_zone |        || time_zone        | SYSTEM |+------------------+--------+

可以通过修改my.cnf
在 [mysqld] 之下加

default-time-zone=timezone 
来修改时区。如：
default-time-zone = '+8:00'
改了记得重启msyql喔
注意一定要在 [mysqld] 之下加 ，否则会出现 unknown variable 'default-time-zone=+8:00'

另外也可以通过命令 set time_zone = timezone
比如北京时间（GMT+0800）
set time_zone = '+8:00';
这个和php的时区设置又有点差别，比如北京时间在php中是
date_default_timezone_set('Etc/GMT-8');

美国pst时间（GMT-08:00）

set time_zone = '-8:00';

#默认时区，time_zone='+8:00'mysql> select now();+---------------------+| now()               |+---------------------+| 2011-07-21 11:52:00 |+---------------------+mysql> set time_zone='+0:00';mysql> select now();+---------------------+| now()               |+---------------------+| 2011-07-21 03:52:18 |+---------------------+

如下表和数据，'1970-01-01 00:00:00' 无法插入

DROP TABLE IF EXISTS timestamp_val;CREATE TABLE timestamp_val(  ts  TIMESTAMP);INSERT INTO timestamp_val (ts) VALUES('1970-01-01 00:00:00');INSERT INTO timestamp_val (ts) VALUES('1987-03-05 12:30:15');INSERT INTO timestamp_val (ts) VALUES('1999-12-31 09:00:00');INSERT INTO timestamp_val (ts) VALUES('2000-06-04 15:45:30');

ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:00:00' for column 'ts' at row 1

我考虑到可能是时区的问题，就设置 time_zone='+0:00' 依然无法插入，'1970-01-01 00:00:01' 能插入！费解！！！

8、TIMEDIFF 函数有最大差值限制（838:59:59）；TIMESTAMPDIFF无限制

范围以内，正确：

mysql> select timediff('2011-10-21 14:25:00','2011-10-02 10:12:22');+-------------------------------------------------------+| timediff('2011-10-21 14:25:00','2011-10-02 10:12:22') |+-------------------------------------------------------+| 460:12:38                                             |+-------------------------------------------------------+

范围以外，错误：

mysql> select timediff('2011-10-21 14:25:00','2011-01-02 10:12:22');+-------------------------------------------------------+| timediff('2011-10-21 14:25:00','2011-01-02 10:12:22') |+-------------------------------------------------------+| 838:59:59                                             |+-------------------------------------------------------+

mysql> select timediff('2011-10-21 14:25:00','2001-10-02 10:12:22');+-------------------------------------------------------+| timediff('2011-10-21 14:25:00','2001-10-02 10:12:22') |+-------------------------------------------------------+| 838:59:59                                             |+-------------------------------------------------------+

TIMESTAMPDIFF 示例：

mysql> set @dt1 = '1900-01-01 00:00:00',@dt2='1910-01-01 00:00:00';mysql> select    -> timestampdiff(second, @dt1, @dt2) as seconds,    -> timestampdiff(minute, @dt1, @dt2) as minutes,    -> timestampdiff(hour, @dt1, @dt2) as hours,    -> timestampdiff(day, @dt1, @dt2) as days,    -> timestampdiff(week, @dt1, @dt2) as weeks,    -> timestampdiff(year, @dt1, @dt2) as years;+-----------+---------+-------+------+-------+-------+| seconds   | minutes | hours | days | weeks | years |+-----------+---------+-------+------+-------+-------+| 315532800 | 5258880 | 87648 | 3652 |   521 |    10 |+-----------+---------+-------+------+-------+-------+

9、NULL 的特殊性！

A、排序时，asc（默认值）NULL在头，desc NULL在尾

示例--如下表和数据

DROP TABLE IF EXISTS taxpayer;CREATE TABLE taxpayer(    name   VARCHAR(20),    id     VARCHAR(20)    );INSERT INTO taxpayer (name,id) VALUES('bernina', '198-48');INSERT INTO taxpayer (name,id) VALUES('bertha',  NULL);INSERT INTO taxpayer (name,id) VALUES('ben',     NULL);INSERT INTO taxpayer (name,id) VALUES( NULL,     '475-83');INSERT INTO taxpayer (name,id) VALUES( 'baidu',  '111+55');

操作

mysql> select * from taxpayer order by id;+---------+--------+| name    | id     |+---------+--------+| bertha  | NULL   || ben     | NULL   || baidu   | 111+55 || bernina | 198-48 || NULL    | 475-83 |+---------+--------+

mysql> select * from taxpayer order by id desc;+---------+--------+| name    | id     |+---------+--------+| NULL    | 475-83 || bernina | 198-48 || baidu   | 111+55 || bertha  | NULL   || ben     | NULL   |+---------+--------+

当然也可以特殊处理，将NULL永远放在尾巴处（详见：Recipe 7.14. Floating Values to the Head or Tail of the Sort Order）

mysql> select * from taxpayer order by if(id is null,1,0), id;+---------+--------+| name    | id     |+---------+--------+| baidu   | 111+55 || bernina | 198-48 || NULL    | 475-83 || bertha  | NULL   || ben     | NULL   |+---------+--------+

B、Most aggregate functions ignore NULL values.（COUNT()、MIN()、MAX()、AVG()、SUM()）

（详见：Recipe 8.8. Summaries and NULL Values）

示例--如下表和数据

DROP TABLE IF EXISTS expt;CREATE TABLE expt(  subject VARCHAR(10),  test    VARCHAR(5),  score INT);INSERT INTO expt (subject,test,score) VALUES('Jane','A',47);INSERT INTO expt (subject,test,score) VALUES('Jane','B',50);INSERT INTO expt (subject,test,score) VALUES('Jane','C',NULL);INSERT INTO expt (subject,test,score) VALUES('Jane','D',NULL);INSERT INTO expt (subject,test,score) VALUES('Marvin','A',52);INSERT INTO expt (subject,test,score) VALUES('Marvin','B',45);INSERT INTO expt (subject,test,score) VALUES('Marvin','C',53);INSERT INTO expt (subject,test,score) VALUES('Marvin','D',NULL);

操作

mysql> select subject,    -> count(score) as n,    -> sum(score) as total,    -> avg(score) as average,    -> min(score) as lowest,    -> max(score) as hightest    -> from expt group by subject;+---------+---+-------+---------+--------+----------+| subject | n | total | average | lowest | hightest |+---------+---+-------+---------+--------+----------+| Jane    | 2 |    97 | 48.5000 |     47 |       50 || Marvin  | 3 |   150 | 50.0000 |     45 |       53 |+---------+---+-------+---------+--------+----------+

实际处理的数据只有2+3=5条！NULL都被忽略了！

如果计算的集合为 empty 或集合内的值都为 NULL ，则计算后的结果也为 NULL 。

mysql> select subject,    -> count(score) as n,    -> sum(score) as total,    -> avg(score) as average,    -> min(score) as lowest,    -> max(score) as hightest    -> from expt where score is null group by subject;+---------+---+-------+---------+--------+----------+| subject | n | total | average | lowest | hightest |+---------+---+-------+---------+--------+----------+| Jane    | 0 |  NULL |    NULL |   NULL |     NULL || Marvin  | 0 |  NULL |    NULL |   NULL |     NULL |+---------+---+-------+---------+--------+----------+

10、group by 后排序 ， 直接在后边加 order by xxx 即可。

mysql> select    -> monthname(statehood) as month,    -> dayofmonth(statehood) as day,    -> count(*) as count    -> from states group by month , day having count>1;+----------+------+-------+| month    | day  | count |+----------+------+-------+| February |   14 |     2 || June     |    1 |     2 || March    |    1 |     2 || May      |   29 |     2 || November |    2 |     2 |+----------+------+-------+

mysql> select    -> monthname(statehood) as month,    -> dayofmonth(statehood) as day,    -> count(*) as count    -> from states group by month , day having count>1 order by day;+----------+------+-------+| month    | day  | count |+----------+------+-------+| June     |    1 |     2 || March    |    1 |     2 || November |    2 |     2 || February |   14 |     2 || May      |   29 |     2 |+----------+------+-------+