MySQL 学习笔记整理

1.      创建表

CREATE TABLE item(

ID INT(6) NOT NULL AUTO_INCREMENT,

Name CHAR(32) NOT NULL,

Price DECIMAL(4,2) NOT NULL,

Description CHAR(255) DEFAULT 'No description',

 

PRIMARY KEY(ID),

KEY(Name)

)

 

其中,Description列中的DEFAULT,真的会将NULL转换为字符串吗?我试过怎么不行?

 

 

2.      联接

继续使用上表,并创建下面一个新表:

CREATE TABLE item_option(

ID INT(6) NOT NULL AUTO_INCREMENT,

Item INT(6) NOT NULL,

Name CHAR(32) NOT NULL,

 

PRIMARY KEY(ID),

FOREIGN KEY(Item) REFERENCES Item(ID)

)

 

有外键关系的这两个表item和item_option,DROP其中的item表,另一个表也会被删除。

先为两个表都添加一些数据:

insert into item values(null,'Toothbrush', 1.25, NULL);

insert into item values (null, 'Comb', 2.50, NULL);

insert into item values (Null, "Brush", 3.00, NULL);

insert into item values (NULL, "Toothpaste", 4.35, NULL);

 

insert into item_option VALUES(NULL, 2, "RED");

insert INTO item_option VALUES (Null, 2, "BLUE");

(1)使用WHERE语句的内联接:

SELECT i.Name, i.Price, o.Name AS 'Option Name' FROM item i, item_option o WHERE i.ID=o.item;

出来的结果:

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

| Name | Price | Option Name |

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

| Comb |  2.50 | RED         |

| Comb |  2.50 | BLUE        |

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

2 rows in set (0.00 sec)

(2)使用INNER JOIN也可以达到上面的效果:

SELECT i.Name, i.Price, o.Name AS 'Option Name' FROM item i INNER JOIN item_option o ON i.ID=o.Item;

(3)使用LEFT JOIN:

SELECT i.Name, i.Price, o.Name AS 'Option Name' FROM item i LEFT JOIN item_option o ON i.ID=o.Item;

结果是这样的:

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

| Name       | Price | Option Name |

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

| Toothbrush |  1.25 | NULL        |

| Comb       |  2.50 | RED         |

| Comb       |  2.50 | BLUE        |

| Brush      |  3.00 | NULL        |

| Toothpaste |  4.35 | NULL        |

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

5 rows in set (0.00 sec)

 

可以看到,(1)和(2)都只是得到了两个表中都有的项。而(3)中将item表中的全部数据都至少放了一行在结果集中,而Comb由于在item_option里有两个记录,所以在结果集中它也出现了两次。

 

附:NOT IN的用法

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

| user_id | user_name | user_class |

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

|       1 | aaa       |          1 |

|       2 | aab       |          1 |

|       3 | aac       |          1 |

|       4 | aad       |          1 |

|       5 | aae       |          1 |

|       6 | bb        |          2 |

|       7 | bb        |          4 |

|       8 | xx        |          2 |

|       9 | ddd       |          2 |

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

 

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

| vote_id | to_id | from_id |

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

|       1 |     2 |       1 |

|       2 |     3 |       1 |

|       3 |     6 |      8 |

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

 

 

欲知道同一班级的谁还没有对本班级的其他人投票,可以这样查询:

select c.user_id as user_id,c.user_name,c.user_class, b.user_id as from_id from user c inner join (select user_id,user_class from user inner join (select from_id from vote group by from_id) a on user.user_id=a.from_id) b on b.user_class=c.user_class and c.user_id!=b.user_id and c.user_id not in (select to_id from vote where vote.from_id=b.user_id);

 

结果为:

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

| user_id | user_name | user_class | from_id |

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

|       4 | aad       |          1 |       1 |

|       5 | aae       |          1 |       1 |

|       9 | ddd       |          2 |       8 |

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

3 rows in set (0.00 sec)

3.      排序

(1)一个排序标准,默认为ASC

SELECT i.Name, i.Price, o.Name AS 'Option Name' FROM item i LEFT JOIN item_option o ON i.ID=o.Item ORDER BY i.Name;

结果为:

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

| Name       | Price | Option Name |

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

| Brush      |  3.00 | NULL        |

| Comb       |  2.50 | RED         |

| Comb       |  2.50 | BLUE        |

| Toothbrush |  1.25 | NULL        |

| Toothpaste |  4.35 | NULL        |

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

5 rows in set (0.00 sec)

(2)两个排序标准

SELECT i.Name, i.Price, o.Name AS 'Option Name' FROM item i LEFT JOIN item_option o ON i.ID=o.Item ORDER BY i.Name, o.Name;

结果应该是这样的:

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

| Name       | Price | Option Name |

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

| Brush      |  3.00 | NULL        |

| Comb       |  2.50 | BLUE        |

| Comb       |  2.50 | RED         |

| Toothbrush |  1.25 | NULL        |

| Toothpaste |  4.35 | NULL        |

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

5 rows in set (0.00 sec)

 

4.      分组

SELECT i.Name, i.Price, count(o.ID) AS 'Options' FROM item i LEFT JOIN item_option o ON i.ID=o.Item  GROUP BY i.Name ORDER BY i.Name;

运行结果为:

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

| Name       | Price | Options |

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

| Brush      |  3.00 |       0 |

| Comb       |  2.50 |       2 |

| Toothbrush |  1.25 |       0 |

| Toothpaste |  4.35 |       0 |

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

4 rows in set (0.00 sec)

 

5.      限制

SELECT i.Name, i.Price, count(o.ID) AS 'Options' FROM item i LEFT JOIN item_option o ON i.ID=o.Item  GROUP BY i.Name ORDER BY i.Name LIMIT 1,2;

结果为(注意跟上面的分组结果比照):

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

| Name       | Price | Options |

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

| Comb       |  2.50 |       2 |

| Toothbrush |  1.25 |       0 |

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

2 rows in set (0.00 sec)

 

可见,LIMIT 语法实际上是LIMIT fromIndex, totalCount。

 

6.      事务和锁定

不太明白

 

7.      变量

(1)直接设置:

set @i=1;

 

mysql> select * from item where id=@i;

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

| ID | Name       | Price | Description |

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

|  1 | Toothbrush |  0.75 | NULL        |

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

1 row in set (0.00 sec)

 

(2)在SELECT语句中设置:

mysql> select @i:=ID from item WHERE Name="Comb";

+--------+

| @i:=ID |

+--------+

|      2 |

+--------+

 

SELECT语句的中变量,在执行WHERE时使用的是上一次所赋的值,如下面的例子:

set @p=1;

select name, price, @p:=price from item where price < @p;

结果为:

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

| name       | price | @p:=price |

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

| Toothbrush |  0.75 |      0.75 |

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

mysql> select name, price, @p:=price from item where price < @p;

得到的结果则为:

Empty set (0.00 sec)

 

mysql> select @p;

+------+

| @p   |

+------+

| 0.75 |

+------+

因为此时@p已经等于0.75了。

 

8.      使用正则表达式

为什么下面的例子中,Comb不会出来呢?它也含有大写字母啊。

select * from item where name regexp '[:upper:]';

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

| ID | Name       | Price | Description |

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

|  1 | Toothbrush |  0.75 | NULL        |

|  3 | Brush      |  3.15 | NULL        |

|  4 | Toothpaste |  3.85 | NULL        |

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

3 rows in set (0.00 sec)

而使用[A-Z]就可以,好奇怪!

select * from item where name regexp '[A-Z]';

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

| ID | Name       | Price | Description |

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

|  1 | Toothbrush |  0.75 | NULL        |

|  2 | Comb       |  2.00 | NULL        |

|  3 | Brush      |  3.15 | NULL        |

|  4 | Toothpaste |  3.85 | NULL        |

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

4 rows in set (0.00 sec)

 

 

9.      列数据类型

mysql> CREATE TABLE testint(

    -> i INT(11) UNSIGNED ZEROFILL

    -> );

Query OK, 0 rows affected (0.31 sec)

 

mysql> insert into testint values (123);

Query OK, 1 row affected (0.11 sec)

 

mysql> select * from testint;

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

| i           |

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

| 00000000123 |

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

1 row in set (0.00 sec)

ZEROFILL的作用就是在前面添0补足。

 

 

10.              枚举

mysql> CREATE TABLE testenum(

    -> A ENUM("A","B","C")

    -> );

Query OK, 0 rows affected (0.29 sec)

 

mysql> INSERT INTO testenum VALUES("B");

Query OK, 1 row affected (0.12 sec)

 

mysql> select * from testenum;

+------+

| A    |

+------+

| B    |

+------+

1 row in set (0.00 sec)

 

mysql> INSERT INTO testenum VALUES("D");

ERROR 1265 (01000): Data truncated for column 'A' at row 1

mysql> select * from testenum;

+------+

| A    |

+------+

| B    |

+------+

1 row in set (0.00 sec)

注意下面的例子,说明了ENUM在内部储存的还是索引值:

mysql> select * from testenum where A=2;

+------+

| A    |

+------+

| B    |

| B    |

+------+

2 rows in set (0.00 sec)

 

mysql> select * from testenum where A='B';

+------+

| A    |

+------+

| B    |

| B    |

+------+

2 rows in set (0.00 sec)

11.              集合

CREATE TABLE testset(A SET('a','b','c'));

添加数据的时候要这样加:

INSERT INTO testset VALUES ('a,b');

但是,注意:

INSERT INTO testset VALUES (1);的结果是添加了a这一个值;

INSERT INTO testset VALUES (2);的结果是添加了b这两个值;

INSERT INTO testset VALUES (3);的结果是添加了a,b这两个值;

INSERT INTO testset VALUES (4);的结果是添加了c这一个值;

INSERT INTO testset VALUES (5);的结果是添加了a,c这两个值;

INSERT INTO testset VALUES (6);的结果是添加了b,c这两个值;

INSERT INTO testset VALUES (7);的结果是添加了a,b,c这三个值。

这就是说只能添加当前集合的一个子集。这些子集是像上面这样编号的。

 

添加的值可以为NULL:

mysql> insert into testset values (null);

Query OK, 1 row affected (0.10 sec)

 

12.              流程控制

为什么:

select id, case count(id) when 1 then 'a' when 2 then 'b' when 3 then 'c'  when 4 then 'd' when 5 then 'e' else 'defaultv' end as 'caser' from item;

的结果是:

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

| id | caser |

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

|  3 | e     |

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

1 row in set (0.00 sec)

 

select *, case count(id) when 1 then 'a' when 2 then 'b' when 3 then 'c'  when 4 then 'd' when 5 then 'e' else 'defaultv' end as 'caser' from item;

的结果是:

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

| ID | Name       | Price | Description | caser |

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

|  1 | Toothbrush |  0.75 | NULL        | e     |

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

1 row in set (0.00 sec)

 

 

可能是由于在选择不同字段时,默认的排序不同?看下面的例子:

mysql> select id, case when id>3 then 'good' else 'bad' end as 'caser' from item

;

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

| id | caser |

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

|  3 | bad   |

|  2 | bad   |

|  5 | good  |

|  1 | bad   |

|  4 | good  |

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

5 rows in set (0.00 sec)

这是按Name排序了。而下面:

mysql>  select *, case when id>3 then 'good' else 'bad' end as 'caser' from item;

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

| ID | Name       | Price | Description | caser |

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

|  1 | Toothbrush |  0.75 | NULL        | bad   |

|  2 | Comb       |  2.00 | NULL        | bad   |

|  3 | Brush      |  3.15 | NULL        | bad   |

|  4 | Toothpaste |  3.85 | NULL        | good  |

|  5 | God        |  3.35 | NULL        | good  |

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

5 rows in set (0.00 sec)

这又是按ID排序了。

 

 

另一种CASE WHEN的用法:

mysql> SELECT ID, CASE WHEN ID=1 THEN POWER(ID,2) WHEN ID=2 THEN ID ELSE Name END AS 'CASE BOX' FROM Item;

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

| ID | CASE BOX   |

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

|  3 | Brush      |

|  2 | 2          |

|  5 | God        |

|  1 | 1          |

|  4 | Toothpaste |

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

5 rows in set (0.00 sec)

 

如果同时有两个条件都为TRUE那只执行第一个条件。如下:

mysql> SELECT ID, CASE WHEN ID%2=0 THEN 'EVEN NUMBER' WHEN ID%3=0 THEN "MULTIPLY RESULT OF 3" ELSE Name END AS 'CASE BOX' FROM Item ORDER BY ID ASC;

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

| ID | CASE BOX             |

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

|  1 | Toothbrush           |

|  2 | EVEN NUMBER          |

|  3 | MULTIPLY RESULT OF 3 |

|  4 | EVEN NUMBER          |

|  5 | God                  |

|  6 | EVEN NUMBER          |

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

6 rows in set (0.00 sec)

 

ID为6的地方就只显示了EVEN NUMBER。

 

下面是一个用IF来控制的例子:

select if(dayofweek(now()) in (0,6,7), 'weekend', 'weekday');

运行结果是:

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

| if(dayofweek(now()) in (0,6,7), 'weekend', 'weekday') |

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

| weekend                                               |

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

1 row in set (0.05 sec)

 

还有IFNULL,作用是如果第一个参数是NULL值,则返回第二个参数,否则返回第一个参数:

mysql> select ifnull("a","YES");

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

| ifnull("a","YES") |

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

| a                 |

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

1 row in set (0.05 sec)

 

mysql> select ifnull(null,"YES");

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

| ifnull(null,"YES") |

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

| YES                |

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

1 row in set (0.00 sec)

 

还有一个NULLIF,作用是两个参数如果相等,则返回NULL:

mysql> select nullif("A","YES");

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

| nullif("A","YES") |

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

| A                 |

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

1 row in set (0.00 sec)

 

mysql> select nullif("A", "A");

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

| nullif("A", "A") |

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

| NULL             |

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

1 row in set (0.00 sec)

 

13.              分组

主要有AVG、BIT_AND、BIT_OR、COUNT、COUNT DISTINCT、MAX、MIN、STD、SUM这几个分组统计函数。

区别一下MAX、MIN与GREATEST、LEAST的不同用法:

select Max(price), Min(price) from item;

select greatest(4,4,5335,63), least(44,42);

 

14.              字符串函数

BIN返回的是一个整数的二进制字符串形式,试图再将BIN后的值与其他整数相加减时,可以看到,如下:

mysql> SELECT BIN(2);

+--------+

| BIN(2) |

+--------+

| 10     |

+--------+

1 row in set (0.00 sec)

 

mysql> SELECT BIN(3);

+--------+

| BIN(3) |

+--------+

| 11     |

+--------+

1 row in set (0.00 sec)

 

mysql> SELECT BIN(2)+BIN(3);

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

| BIN(2)+BIN(3) |

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

|            21 |

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

1 row in set (0.00 sec)

 

 

难道CHAR_LENGTH参数如果是汉字,返回的是2N-1?

mysql> select char_length('钢是时候无论');

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

| char_length('钢是时候无论')            |

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

|                          11 |

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

1 row in set (0.00 sec)

 

而LENGTH函数就不一样了:

mysql> SELECT LENGTH('时间');

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

| LENGTH('时间')    |

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

|              4 |

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

1 row in set (0.00 sec)

 

CONV负责各种进制之间的转换:

mysql> SELECT CONV('B',16,10);

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

| CONV('B',16,10) |

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

| 11              |

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

1 row in set (0.00 sec)

 

加密与解密:

mysql> SELECT ENCODE("A","B");

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

| ENCODE("A","B") |

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

| ?               |

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

1 row in set (0.00 sec)

 

mysql> SELECT DECODE(ENCODE("A","B"),"B");

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

| DECODE(ENCODE("A","B"),"B") |

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

| A                           |

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

1 row in set (0.00 sec)

 

mysql> SELECT PASSWORD("B");

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

| PASSWORD("B")                             |

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

| *693EFD3BD44CCBA9924731C2DB18ADB8825C0B0A |

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

1 row in set (0.00 sec)

 

mysql> SELECT MD5("B");

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

| MD5("B")                         |

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

| 9d5ed678fe57bcca610140957afab571 |

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

1 row in set (0.00 sec)

 

这个函数可以拿来存什么数据吧?

mysql> SELECT EXPORT_SET(10,'1','0','',8);

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

| EXPORT_SET(10,'1','0','',8) |

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

| 01010000                    |

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

1 row in set (0.00 sec)

 

mysql> SELECT REVERSE( EXPORT_SET(10,'1','0','',8));

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

| REVERSE( EXPORT_SET(10,'1','0','',8)) |

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

| 00001010                              |

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

1 row in set (0.00 sec)

 

 

15.              一个数据库设计实例

mysql> CREATE TABLE EventRecording(

    -> ID INT(11) NOT NULL AUTO_INCREMENT,

    -> Starttime int(15) NOT NULL,

    -> Duration INT(15) NOT NULL,

    -> PRIMARY KEY(ID)

    -> );

Query OK, 0 rows affected (0.25 sec)

 

 

mysql> CREATE TABLE EventOption(

    -> ID INT(11) NOT NULL AUTO_INCREMENT,

    -> EVENTID INT(11) NOT NULL,

    -> Repeatinterval INT(15) NOT NULL,

    -> Repeatcount INT(11) NOT NULL,

    -> PRIMARY KEY(ID),

    -> FOREIGN KEY(EVENTID) REFERENCES EventRecording(ID),

    ->UNIQUE KEY(EVENTID)

    -> );

Query OK, 0 rows affected (0.27 sec)

 

mysql> show tables;

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

| Tables_in_test0811 |

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

| eventoption        |

| eventrecording     |

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

2 rows in set (0.00 sec)

 

 

mysql> insert into eventrecording (starttime,duration) VALUES (1376185540, 3600), (1376186540, 2400);

Query OK, 2 rows affected (0.07 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

mysql> select * from eventrecording;

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

| ID | Starttime  | Duration |

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

|  1 | 1376185540 |     3600 |

|  2 | 1376186540 |     2400 |

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

2 rows in set (0.00 sec)

 

mysql> insert into eventoption(repeatinterval, repeatcount, eventid) values(2000,2,1),(3600,10,2), (2000,2,4);

Query OK, 2 rows affected (0.12 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

mysql> select * from eventoption;

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

| ID | Repeatinterval | Repeatcount | EVENTID |

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

|  1 |           2000 |           2 |       1 |

|  2 |           3600 |          10 |       2 |

|  4 |           3600 |          10 |       2 |

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

2 rows in set (0.00 sec)

 

 

mysql> SELECT r.ID, r.Starttime, r.Duration, o.Repeatinterval, o.Repeatcount from eventrecording r inner join eventoption o on r.id=o.eventid;

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

| ID | Starttime  | Duration | Repeatinterval | Repeatcount |

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

|  1 | 1376185540 |     3600 |           2000 |           2 |

|  2 | 1376186540 |     2400 |           3600 |          10 |

|  4 | 1376186540 |     2400 |           2000 |           2 |

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

3 rows in set (0.00 sec)

 

 

添加一个更狠的:

mysql> insert into eventrecording values(null, unix_timestamp(), 3600*24*30);

Query OK, 1 row affected (0.12 sec)

 

mysql> select * from eventrecording;

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

| ID | Starttime  | Duration |

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

|  1 | 1376185540 |     3600 |

|  2 | 1376186540 |     2400 |

|  3 | 1376185540 |     3600 |

|  4 | 1376186540 |     2400 |

|  5 | 1376190822 |  2592000 |

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

5 rows in set (0.00 sec)

 

mysql> insert into eventoption values(5, 3600*24*265, 50);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into eventoption values(null,5,3600*24*365, 50);

Query OK, 1 row affected (0.10 sec)

 

mysql> select * from eventoption;

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

| ID | EVENTID | Repeatinterval | Repeatcount |

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

|  1 |       1 |           2000 |           2 |

|  2 |       2 |           3600 |          10 |

|  5 |       4 |           2000 |           2 |

|  6 |       5 |       31536000 |          50 |

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

4 rows in set (0.00 sec)

 

mysql>  SELECT r.ID, r.Starttime, r.Duration, o.Repeatinterval, o.Repeatcount from eventrecording r inner join eventoption o on r.id=o.eventid;

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

| ID | Starttime  | Duration | Repeatinterval | Repeatcount |

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

|  1 | 1376185540 |     3600 |           2000 |           2 |

|  2 | 1376186540 |     2400 |           3600 |          10 |

|  4 | 1376186540 |     2400 |           2000 |           2 |

|  5 | 1376190822 |  2592000 |       31536000 |          50 |

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

4 rows in set (0.00 sec)

 

实际上,时间戳只能到2E31就没了,大过这个数字的时间也只能是没有用了。

posted on 2013-08-20 10:54  pinocchioatbeijing  阅读(275)  评论(0编辑  收藏  举报

导航