mysql的使用23

mysql> #联合索引
mysql> #是指对表上的多个列进行索引，联合索引的创建方法与单个索引的创建方法一样，不同之处仅在于多个所以列
mysql> create table indext(a int, b int, primary key(a), key idx_a_b(a,b))engine=innodb;
ERROR 1046 (3D000): No database selected
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table indext(a int, b int, primary key(a), key idx_a_b(a,b))engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> #此时两个键值就组成了联合索引
mysql> insert into indext values(1,1),(1,2),(2,1),(2,4),(3,1),(3,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into indext values(1,1),(2,2),(3,1),(4,4),(5,1),(6,2);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from indext;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
| 3 |    1 |
| 4 |    4 |
| 5 |    1 |
| 6 |    2 |
+---+------+
6 rows in set (0.00 sec)

mysql> select * from indext where a=3,b=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b=1' at line 1
mysql> select * from indext where a=3 and b=1;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

mysql> #此时a节点递增 可以使用b+索引，b不能
mysql>
mysql> create table buy_log(
    -> userid int unsigned not null,
    -> buy_date date)engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into buy_log values(1,'2009-01-01'),(2,'2009-01-01'),(3,'2009-01-01'),(1,'2009-02-01'),(3,'2009-02-01'),(1,'2009-03-01'),(1,'2009-04-01');
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from buy_log;
+--------+------------+
| userid | buy_date   |
+--------+------------+
|      1 | 2009-01-01 |
|      2 | 2009-01-01 |
|      3 | 2009-01-01 |
|      1 | 2009-02-01 |
|      3 | 2009-02-01 |
|      1 | 2009-03-01 |
|      1 | 2009-04-01 |
+--------+------------+
7 rows in set (0.00 sec)

mysql> alter table buy_log add key(userid);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table buy_log add key(userid, buy_date);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from buy_log where userid=1 order by buy_date desc;
+--------+------------+
| userid | buy_date   |
+--------+------------+
|      1 | 2009-04-01 |
|      1 | 2009-03-01 |
|      1 | 2009-02-01 |
|      1 | 2009-01-01 |
+--------+------------+
4 rows in set (0.00 sec)

mysql> select * from buy_log where userid=1 order by buy_date desc limit 3;
+--------+------------+
| userid | buy_date   |
+--------+------------+
|      1 | 2009-04-01 |
|      1 | 2009-03-01 |
|      1 | 2009-02-01 |
+--------+------------+
3 rows in set (0.00 sec)

mysql> select * from buy_log where userid=1;
+--------+------------+
| userid | buy_date   |
+--------+------------+
|      1 | 2009-01-01 |
|      1 | 2009-02-01 |
|      1 | 2009-03-01 |
|      1 | 2009-04-01 |
+--------+------------+
4 rows in set (0.00 sec)

mysql> #在以上两个中  有两个索引  编译器会自动择优选择
mysql>
mysql> #覆盖索引
mysql>
mysql> #方法: select key from table where key1=xxx;
mysql> #select primary key2,key2 from table where key1=xxx;
mysql> #select primary key1,key2 from table where key1=xxx;
mysql> #select primary key1, primary key2,key2 from table where key1=xxx;
mysql>
mysql> select count(*) from buy_log;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> #force 强制使用索引
mysql> exit