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) 编辑 收藏 举报
