Mysql的高阶语句介绍（排序，查询和视图）

最新推荐文章于 2024-03-29 11:27:26 发布
最新推荐文章于 2024-03-29 11:27:26 发布
阅读量159 收藏
点赞数 1
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45059041/article/details/118935969
版权

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)

一元十二會_
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
mysql 高级（进阶学习）
qq_46381044的博客
07-05 1512
视图就是将某个查询语句存储在数据中，并为其命名，视图中并不存储数据，数据还是在基本表中存储。定义视图使用视图删除视图存储过程就是把一段处理逻辑存入到数据库中，使用是就由 JDBC 调用即可。调用存储过程可以减少应用程序和数据库交互次数，在数据库内部执行，执行效率高。存储事先需要定义，有三种参数类型：in 入参（接收调用者传入的数据）out 返回（向调用者返回数据）inout （既可以接收调用者传入的数据，也可以向调用者返回数据）函数是一个特殊的存储过程。存储过程不仅有输入参数，还有输出参数，但是没有返回值，
MySQL高阶语句（select查询、视图））
jiaoshu__的博客
07-21 929
文章目录一、MySQL高阶语句查询1.1概述1.2关键字1.3关键字排序1.4多字段查询二、区间判断及查询不重复记录2.1 and或or（且、或）2.2嵌套、多条件2.3添加-distinct(查询不重复记录）三、结果进行分组3.1hobbid相同的分组，计算相同分数的学生个数（基于name个数进行计数）3.2 结合where语句，筛选分数大于等于80的分组，计算学生个数3.3结合order by计算出的学生个数按顺序排列四、限制结果条目（limit）4.1limit(位置偏移)4.2查询五、设置别名5.1
Mysql数据库（七）——mysql高阶语句（上）
想成功，靠自己
04-16 2857
Mysql数据库（六）——mysql高阶语句（上）一、按关键字排序1、单字段排序2、多字段排序二、或/且的运用三、查询不重复记录四、对结果进行分组五、限制结果条目六、设置别名使用场景七、连接语句八、通配符九、子查询1、定义2、子查询—别名3、子查询—exists十、视图十一、NULL值1、定义2、NULL和空值的区别 一、按关键字排序 使用select语句可以将需要的数据从 mysql 数据库中查询出来，如果对查询的结果进行排序操作，可以使用 order by 语句完成排序，并且最终将排序后的结果返回
MYSQL高阶
qq_52286612的博客
12-23 618
MYSQL 存储引擎 innoDB 1.支持事务 对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务;(也不是说越多越好，多了会造成卡顿)。 2.支持表锁和行锁 InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。 例如： t_user(uid, uname, age, sex) innodb; uid..
mysql高阶
qq_28613337的博客
08-27 367
mysql底层数据结构及运行原理
MySQL数据库高阶语句之查询、视图、NULL值
28线不知名云架构师的后花园
07-21 181
准备工作 1.MySQL安装 2.实验准备，数据表配置 mysql -uroot -p show databases; create database ky11； use ky11； create table www (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbid int(5)); insert into www values(1,'lilei',90,'n..
MySQL高阶语句
夏颜的博客
06-22 413
使用ORDER BY语句来对语句实现排序，这个语句的排序可以针对一个或多个字段语法格式： 建立一张表 查询name，cardid，并按照cardid升序排列 查询name、score，cardid，并按照score降序查询 查询name，sorce，cardid，先按照score降序，score相同再按cardid降序查询 在大型数据库中，有时查询数据需要数据符合某些特点条件 AND/OR 且/或 和shell脚本中一样使用方法 查询表内secore大于80且小于90的数据的name和cardid
掌握Mysql高阶语句，玩转Mysql（万字总结）
彼岸吖的博客
09-06 309
SELECT
mysql高阶语句（一）
m0_67402013的博客
08-02 189
数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射。视图可以理解为镜花水月/倒影，动态保存结果集（数据）基础表info----》映射（投影）-----视图#语法CREATEVIEW“视图表名”AS“SELECT语句”;介绍了一些高阶语句在数据表或者数据库里面的用法！！先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。httpshttpshttpshttpshttpshttpshttps。...
MySql视图顺序整理工具
06-13
MySql视图顺序整理工具，只要把Navicat 中的视图sql 单独保存用这个程序就自动整理好！！快速安装部署。
MySQL高级
大象
07-19 2990
索引主要目的：提高查询效率，对需要经常查询的字段添加索引，可有效提高查询效率。简介 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。 索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行
mysql 高级排序_mysql高级排序&高级匹配查询示例
weixin_32915107的博客
01-19 122
在大多数应用场景下，我们使用mysql进行查询时只会用到'=', '>' , '1. 根据状态来排序的查询假设现在一个记录有四种状态，未处理(0)、正在处理(2)、处理成功(1)、处理失败(4)，之所以他们的值是这个样子，是因为我们一般情况下是不会用它去排序，所以自然的就想到这样的一些值赋予意义。但是，在排序的时候怎么处理呢？ 假如要求的先后顺序是这样的：未处理>处理失败>正在处...
MySQL数据库 --- 高级篇
shuai_h的博客
06-12 4033
大家可能没有听说过存储引擎，但是一定听过引擎这个词，引擎就是发动机，是一个机器的核心组件。比如，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎一样。而对于存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
Mysql高阶功法
Zonzereal的博客
12-04 3245
####对于SQL语言的高级知识总结 1、MySql的编码问题 注释：创建数据库的时候，已经指定了该数据库的编码格式，但是由于计算机操作系统的不同，所以出现编码问题 以Windows操作系统为例---手动表达各种嫌弃，使用linux安装mysql的时候直接设置编码格式为utf-8，建表也设置，一点问题都没有，就windows逼事多 a、当在Windows上使用MySql的时候，使用命令窗口...
MySQL的高阶部分
lv_hulk的博客
02-01 2773
MySQL的高阶部分 1. MySQL的事务 （1）存储引擎的介绍    介绍：当客户端发送一条SQL语句给服务器时，服务器端通过缓存、语法检查、校验通过之后，然后会通过调用底层的一些软件组织，去从数据库中查询数据，然后将查询到的结果集返回给客户端，而这些底层的软件组织就是存储引擎。    MySQL的存储引擎：      - MySQL的核心就是存储引擎，MySQL可以设置多种不同的存储引擎，不...
mysql高级（进阶篇）
最新发布
qq_73182976的博客
03-29 830
存储引擎就是存储数据，建立索引，更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。// 在创建表的时候，指定存储引擎CREATE TABLE 表名{...// 查看当前数据库支持的存储引擎用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接"@变量名"使用就可。其作用域为当前连接# 赋值SELECT 字段名 INTO @var_name FROM 表名# 使用。
MySQL高级语句(一)
ZFX20001123的博客
04-06 1388
目录 一、常用查询（增、删、改、查） 1.1.按关键字排序（asc|desc） 1.2.区间判断（and/or） 1.3.查询不重复记录（distinct） 1.4.对结果进行分组（group by） 1.5.限制结果条目（limit） 1.6.设置别名（alias——>as） 1.7.通配符 1.8.子查询 一、常用查询（增、删、改、查） 对MySQL数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。 例如取 10 条数据、对查询结果进行排序或分组等等。 1.
mysql高阶语句
weixin_67470255的博客
06-22 509
对MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取10条数据、对查询结果进行排序或分组等等。​使用select语句可以将需要的数据从 mysql 数据库中查询出来，如果对查询的结果进行排序操作，可以使用 order by 语句完成排序，并且最终将排序后的结果返回给客户。这个语句的排序不光可以针对某一个字段，也可以针对多个字段。 创建一个新表 1.1.2 按分数降序排序 1.2 多字段排序 ORDER BY 语句也可以使用多个字段来进
mysql查询语句高阶用法
07-15
MySQL 提供了一些高阶的查询语句用法，可以帮助我们更灵活、高效地进行数据查询。以下是一些常用的高阶用法： 1. 子查询： 子查询是将一个查询嵌套在另一个查询中的查询结构。它可以用作 `SELECT` 语句中的列、`...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值