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
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值