MySQL的这些显而易见的坑你踩过了没

distinct关键字

distinct是应用于所有列的, 而不是某一个列

mysql> select * from test_table;
+------+------+
| one  | two  |
+------+------+
| 56   | 12   |
| 52   | 10   |
| 56   | 12   |
| 56   | 13   |
+------+------+
4 rows in set (0.00 sec)

mysql> select distinct one, two from test_table;
+------+------+
| one  | two  |
+------+------+
| 56   | 12   |
| 52   | 10   |
| 56   | 13   |
+------+------+
3 rows in set (0.00 sec)

mysql> select distinct one from test_table;
+------+
| one  |
+------+
| 56   |
| 52   |
+------+
2 rows in set (0.00 sec)

and关键字

and的组合优先级比or高

mysql> select * from test_table;
+------+------+
| one  | two  |
+------+------+
| 56   | 12   |
| 52   | 10   |
| 56   | 12   |
| 56   | 13   |
| NULL | NULL |
+------+------+
5 rows in set (0.00 sec)

mysql> select one, two from test_table where one = 52 or one = 56 and two > 12;
+------+------+
| one  | two  |
+------+------+
| 52   | 10   |
| 56   | 13   |
+------+------+
2 rows in set (0.00 sec)

mysql> select one, two from test_table where one = 52 or (one = 56 and two > 12);
+------+------+
| one  | two  |
+------+------+
| 52   | 10   |
| 56   | 13   |
+------+------+
2 rows in set (0.00 sec)

mysql> select one, two from test_table where (one = 52 or one = 56) and two > 12;
+------+------+
| one  | two  |
+------+------+
| 56   | 13   |
+------+------+
1 row in set (0.00 sec)

NULL

null和空字符是不一样的, 找到他和删除他的方式也比较特别

mysql> insert into test_table(one , two) values (null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+------+------+
| one  | two  |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

mysql>  delete from test_table where one = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_table;
+------+------+
| one  | two  |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

mysql>  delete from test_table where one = ' ';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_table;
+------+------+
| one  | two  |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> delete from test_table where isnull(one);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
Empty set (0.00 sec)

rollback

• 并不是什么都可以回滚的, 典型的如创建表和删除表这些都是不能回退的.
• 事务是用来管理 insert,update,delete 语句的

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_tab;
+-----+-----+-------+
| one | two | three |
+-----+-----+-------+
|   3 | 4   |     5 |
+-----+-----+-------+
1 row in set (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_tab value  (4, 4, 5);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test_tab;
+-----+-----+-------+
| one | two | three |
+-----+-----+-------+
|   3 | 4   |     5 |
+-----+-----+-------+
1 row in set (0.00 sec)

mysql> drop table test_tab;
Query OK, 0 rows affected (0.02 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_tab;
ERROR 1146 (42S02): Table 'b_test_database.test_tab' doesn't exist