MySQL入门二之一些小注意点

Posted on | In

distinct关键字

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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高

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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和空字符是不一样的, 找到他和删除他的方式也比较特别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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 语句的
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    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