Mysql的高阶语句介绍(排序,查询和视图)
mysql除了增删改查之外,有时候需要对查询结果进行处理,这就衍生出了高级的查询语句
mysql> select * from test;
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 60.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)
mysql> select id,name,score from test order by score; //指定score默认排序是升序
+------+-----------+-------+
| id | name | score |
+------+-----------+-------+
| 6 | hanmeimei | 40.00 |
| 7 | lilei | 50.00 |
| 3 | wangwu | 60.00 |
| 1 | zhangsan | 80.00 |
| 2 | lisi | 90.00 |
| 5 | wangdi | 98.00 |
| 4 | tianqi | 99.00 |
+------+-----------+-------+
7 rows in set (0.00 sec)
mysql> select id,name,score from test order by score desc; //查询score指定为降序方式
+------+-----------+-------+
| id | name | score |
+------+-----------+-------+
| 4 | tianqi | 99.00 |
| 5 | wangdi | 98.00 |
| 2 | lisi | 90.00 |
| 1 | zhangsan | 80.00 |
| 3 | wangwu | 60.00 |
| 7 | lilei | 50.00 |
| 6 | hanmeimei | 40.00 |
+------+-----------+-------+
7 rows in set (0.00 sec)
mysql> select id,name,score from test where address='nanjing' order by score desc; //指定address=南京的score以降序排列
+------+-----------+-------+
| id | name | score |
+------+-----------+-------+
| 7 | lilei | 50.00 |
| 6 | hanmeimei | 40.00 |
+------+-----------+-------+
2 rows in set (0.00 sec)
mysql> select id,name,hobbid from test order by hobbid desc,id desc; //指定hobbid降序排列,相同的hobbid中id降序排列
+------+-----------+--------+
| id | name | hobbid |
+------+-----------+--------+
| 7 | lilei | 5 |
| 4 | tianqi | 5 |
| 3 | wangwu | 4 |
| 6 | hanmeimei | 3 |
| 5 | wangdi | 3 |
| 2 | lisi | 2 |
| 1 | zhangsan | 2 |
+------+-----------+--------+
7 rows in set (0.00 sec)
mysql> select id,name,hobbid from test order by hobbid desc,id; //默认不加desc就是升序排列
+------+-----------+--------+
| id | name | hobbid |
+------+-----------+--------+
| 4 | tianqi | 5 |
| 7 | lilei | 5 |
| 3 | wangwu | 4 |
| 5 | wangdi | 3 |
| 6 | hanmeimei | 3 |
| 1 | zhangsan | 2 |
| 2 | lisi | 2 |
+------+-----------+--------+
7 rows in set (0.00 sec)
mysql> select * from test where score >50 and score <=90; //查询score在大于50和小于等于90中间
+------+----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+------------+--------+
| 2 | lisi | 90.00 | shengzheng | 2 |
| 3 | wangwu | 60.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+----------+-------+------------+--------+
3 rows in set (0.00 sec)
mysql> select * from test where score >50 or score <=90; //查询score在大于50或者小于等于90的记录,所以都满足
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 60.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)
mysql> select * from test where score >50 or (score >40 and score <90); //先执行括号的句子,括号取值为大于40和小于等于90的score记录,然后再跟大于50取或,最小值为50,所以最终为大于50
+------+----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+------------+--------+
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 60.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+----------+-------+------------+--------+
6 rows in set (0.00 sec)
mysql> select distinct hobbid from test; //将重复的去掉不显示
+--------+
| hobbid |
+--------+
| 3 |
| 5 |
| 2 |
| 4 |
+--------+
4 rows in set (0.00 sec)
mysql> select count(name),hobbid from test group by hobbid; //将hobbid分组并计数
+-------------+--------+
| count(name) | hobbid |
+-------------+--------+
| 2 | 2 |
| 2 | 3 |
| 1 | 4 |
| 2 | 5 |
+-------------+--------+
4 rows in set (0.00 sec)
mysql> select count(name),score,hobbid from test where score>=80 group by hobbid order by count(name); //将hobbid分组并计数然后计数默认升序排列
+-------------+-------+--------+
| count(name) | score | hobbid |
+-------------+-------+--------+
| 1 | 99.00 | 5 |
| 1 | 98.00 | 3 |
| 1 | 80.00 | 4 |
| 2 | 90.00 | 2 |
+-------------+-------+--------+
4 rows in set (0.00 sec)
mysql> select * from test limit 4; //显示五行,第一行是字段名,实际显示是四行记录
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
+------+-----------+-------+------------+--------+
4 rows in set (0.00 sec)
mysql> select * from test limit 4,2; //从第五行起,查询后面两行
+------+--------+-------+----------+--------+
| id | name | score | address | hobbid |
+------+--------+-------+----------+--------+
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
+------+--------+-------+----------+--------+
2 rows in set (0.00 sec)
mysql> select id,name from test order by id limit 3; //查询id,name并以升序排列id并限制只查询四行
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)
mysql> select id,name from test order by id desc limit 3; //将查询的记录倒序,并只查四行,也就是查记录的最后三行
+------+-----------+
| id | name |
+------+-----------+
| 7 | lilei |
| 6 | hanmeimei |
| 5 | wangdi |
+------+-----------+
3 rows in set (0.00 sec)
mysql> select count(id) as member from test; //统计id总数并设置字段别名为member
+--------+
| member |
+--------+
| 7 |
+--------+
1 row in set (0.00 sec)
mysql> select count(id) member from test; //中间的as可以省略
+--------+
| member |
+--------+
| 7 |
+--------+
1 row in set (0.00 sec)
mysql> create table test1 as select * from test; 用as相当于克隆test表,但是约束并不会被复制
Query OK, 7 rows affected (0.09 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from test1;
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)
mysql> desc test;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | PRI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| hobbid | int(5) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> desc test1;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| hobbid | int(5) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> create table test2 as select * from test where score >=60; //也可以加上where来判断复制的内容
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test2;
+------+----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+------------+--------+
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+----------+-------+------------+--------+
5 rows in set (0.00 sec)
mysql> select * from test;
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)
mysql> select id,name from test where name like 'l%'; //查询名字是l开头的记录
+------+-------+
| id | name |
+------+-------+
| 7 | lilei |
| 2 | lisi |
+------+-------+
2 rows in set (0.00 sec)
mysql> select id,name from test where name like 'l_s_'; //查询ls中间任一字符,s后任一字符的名字
+------+------+
| id | name |
+------+------+
| 2 | lisi |
+------+------+
1 row in set (0.00 sec)
mysql> select id,name from test where name like '%s%'; //查询中间有s的名字记录
+------+----------+
| id | name |
+------+----------+
| 2 | lisi |
| 1 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
mysql> select id,name from test where name like 'l___'; //查询l后面有三个任意字符的名字记录
+------+------+
| id | name |
+------+------+
| 2 | lisi |
+------+------+
1 row in set (0.00 sec)
mysql> select id,name from test where name like 'l_%'; //查询l后面跟任意字符的名字记录
+------+-------+
| id | name |
+------+-------+
| 7 | lilei |
| 2 | lisi |
+------+-------+
2 rows in set (0.01 sec)
mysql> select name,score from test where id in (select id from test where score >80); //子语句查询出来的id给外面的主语句来查询,in是将主语句跟子语句中的表关联
+--------+-------+
| name | score |
+--------+-------+
| lisi | 90.00 |
| tianqi | 99.00 |
| wangdi | 98.00 |
+--------+-------+
3 rows in set (0.00 sec)
mysql> select id from test where score >80
-> ;
+------+
| id |
+------+
| 2 |
| 4 |
| 5 |
+------+
3 rows in set (0.00 sec)
mysql> select id,name,score from test where id in (select id from qq); //先查询qq表的id然后赋值给主语句查询
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 2 | lisi | 90.00 |
| 1 | zhangsan | 80.00 |
+------+----------+-------+
2 rows in set (0.00 sec)
mysql> select id from qq;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
mysql> select * from test2;
+------+----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+------------+--------+
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+----------+-------+------------+--------+
5 rows in set (0.00 sec)
mysql> insert into test2 select * from test where id in (select id from test); //将test的记录全部插入test2中
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from test2;
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+------------+--------+
12 rows in set (0.00 sec)
mysql> update test set score=50 where id in (select id from test2 where id=2); //test对应id为2的score修改为50
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 50.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)
mysql> select * from test;
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 50.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)
mysql> delete from test where id in (select id from test2 where score>80); //查询test2表中的score大于80的id然后对应test中删除
Query OK, 3 rows affected (0.00 sec)
mysql> select * from test;
+------+-----------+-------+----------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+----------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+----------+--------+
4 rows in set (0.00 sec)
mysql> select * from test;
+------+-----------+-------+----------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+----------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+----------+--------+
4 rows in set (0.00 sec)
mysql> delete from test where id not in (select id where score>=80); //查询score大于等于80的id然后排除并删除不是这些id的记录
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test;
+------+----------+-------+----------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+----------+--------+
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+----------+-------+----------+--------+
2 rows in set (0.00 sec)
mysql> select count(*) from test1 where exists(select id from test1 where score=80); //查询score等于80的id,如果有就执行主语句
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from test1 where exists(select id from test1 where score<40); //查询scorexiaoyu40的id,没有就不执行前面的主语句
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+-----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+-------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+-------+------------+--------+
7 rows in set (0.00 sec)
mysql> select id from (select id,name from test);
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select a.id,name from (select id,name from test) a; //将a作为别名
+------+----------+
| id | name |
+------+----------+
| 3 | wangwu |
| 1 | zhangsan |
+------+----------+
2 rows in set (0.00 sec)
mysql> create view v_score as select * from test1 where score>=80;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_score; //只有大于等于80的score记录
+------+----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+------------+--------+
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 80.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+----------+-------+------------+--------+
5 rows in set (0.00 sec)
mysql> update test1 set score='50' where name='wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from v_score; //视图中没有wangwu的记录,因为改成了小于80的score了
+------+----------+-------+------------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+------------+--------+
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+----------+-------+------------+--------+
4 rows in set (0.00 sec)
mysql> update v_score set score='100' where name='tianqi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from v_score;
+------+----------+--------+------------+--------+
| id | name | score | address | hobbid |
+------+----------+--------+------------+--------+
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 100.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+----------+--------+------------+--------+
4 rows in set (0.00 sec)
mysql> select * from test1;
+------+-----------+--------+------------+--------+
| id | name | score | address | hobbid |
+------+-----------+--------+------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 |
| 2 | lisi | 90.00 | shengzheng | 2 |
| 4 | tianqi | 100.00 | hangzhou | 5 |
| 5 | wangdi | 98.00 | laowo | 3 |
| 3 | wangwu | 50.00 | shanghai | 4 |
| 1 | zhangsan | 80.00 | beijing | 2 |
+------+-----------+--------+------------+--------+
7 rows in set (0.00 sec)
mysql> create table test3 (id int,name varchar(10),age char(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test3 values(1,'zhangsan',20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test3 values(2,'lisi',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test3 values(3,'wangwu',29);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test3;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 20 |
| 2 | lisi | 30 |
| 3 | wangwu | 29 |
+------+----------+------+
3 rows in set (0.00 sec)
mysql> create view v_test1(id,name,score,age) as select test1.id,test1.name,test1.score,test3.age from test1,test3 where test1.name=test3.name; //将各自表中需要的字段拿出来然后判断name相同,查询出来的结果就是name相同的
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_test1;
+------+----------+-------+------+
| id | name | score | age |
+------+----------+-------+------+
| 2 | lisi | 90.00 | 30 |
| 3 | wangwu | 50.00 | 29 |
| 1 | zhangsan | 80.00 | 20 |
+------+----------+-------+------+
3 rows in set (0.00 sec)
mysql> alter table test1 add column addr varchar(50); //增加一个字段
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update test1 set addr='shanghai' where score >80; //score大于80的addr定义为shanghai
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from test1;
+------+-----------+--------+------------+--------+----------+
| id | name | score | address | hobbid | addr |
+------+-----------+--------+------------+--------+----------+
| 6 | hanmeimei | 40.00 | nanjing | 3 | NULL |
| 7 | lilei | 50.00 | nanjing | 5 | NULL |
| 2 | lisi | 90.00 | shengzheng | 2 | shanghai |
| 4 | tianqi | 100.00 | hangzhou | 5 | shanghai |
| 5 | wangdi | 98.00 | laowo | 3 | shanghai |
| 3 | wangwu | 50.00 | shanghai | 4 | NULL |
| 1 | zhangsan | 80.00 | beijing | 2 | NULL |
+------+-----------+--------+------------+--------+----------+
7 rows in set (0.00 sec)
mysql> select count(addr) from test1; //函数只统计出三个值,也就是说null不算
+-------------+
| count(addr) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> update test1 set addr='' where name='zhangsan'; //将zhangsanaddr改为空值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test1;
+------+-----------+--------+------------+--------+----------+
| id | name | score | address | hobbid | addr |
+------+-----------+--------+------------+--------+----------+
| 6 | hanmeimei | 40.00 | nanjing | 3 | NULL |
| 7 | lilei | 50.00 | nanjing | 5 | NULL |
| 2 | lisi | 90.00 | shengzheng | 2 | shanghai |
| 4 | tianqi | 100.00 | hangzhou | 5 | shanghai |
| 5 | wangdi | 98.00 | laowo | 3 | shanghai |
| 3 | wangwu | 50.00 | shanghai | 4 | NULL |
| 1 | zhangsan | 80.00 | beijing | 2 | |
+------+-----------+--------+------------+--------+----------+
7 rows in set (0.00 sec)
mysql> select count(addr) from test1; //发现统计多一个,说明空值被算入统计中
+-------------+
| count(addr) |
+-------------+
| 4 |
+-------------+
1 row in set (0.01 sec)
mysql> select * from test1 where addr is null;
+------+-----------+-------+----------+--------+------+
| id | name | score | address | hobbid | addr |
+------+-----------+-------+----------+--------+------+
| 6 | hanmeimei | 40.00 | nanjing | 3 | NULL |
| 7 | lilei | 50.00 | nanjing | 5 | NULL |
| 3 | wangwu | 50.00 | shanghai | 4 | NULL |
+------+-----------+-------+----------+--------+------+
3 rows in set (0.00 sec)
mysql> select * from test1 where addr is not null;
+------+----------+--------+------------+--------+----------+
| id | name | score | address | hobbid | addr |
+------+----------+--------+------------+--------+----------+
| 2 | lisi | 90.00 | shengzheng | 2 | shanghai |
| 4 | tianqi | 100.00 | hangzhou | 5 | shanghai |
| 5 | wangdi | 98.00 | laowo | 3 | shanghai |
| 1 | zhangsan | 80.00 | beijing | 2 | |
+------+----------+--------+------------+--------+----------+
4 rows in set (0.00 sec)