mysql高阶操作：排序、分组、子查询、视图、正则

于 2021-07-17 14:56:08 发布
阅读量683 收藏 3
点赞数
分类专栏： 集群
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Ryougi_Shiki_uio/article/details/118738987
版权

排序

使用order by对表中数据进行排序

#先创建一张表
mysql> select * from game;
+------+----------+-------+----------+-------+
| id   | name     | sorce | addr     | level |
+------+----------+-------+----------+-------+
|    1 | zhangsan | 49.00 | beijing  |    10 |
|    2 | lisi     | 51.00 | beijing  |    13 |
|    3 | lisi     | 37.00 | nanjing  |     9 |
|    4 | wangwu   | 43.00 | zhejiang |    12 |
|    5 | laoliu   | 38.00 | shanghai |    16 |
+------+----------+-------+----------+-------+
5 rows in set (0.00 sec)

#排序默认升序排序，asc是升序排序，desc是降序排序
mysql> select * from game order by addr asc;
+------+----------+-------+----------+-------+
| id   | name     | sorce | addr     | level |
+------+----------+-------+----------+-------+
|    1 | zhangsan | 49.00 | beijing  |    10 |
|    2 | lisi     | 51.00 | beijing  |    13 |
|    3 | lisi     | 37.00 | nanjing  |     9 |
|    5 | laoliu   | 38.00 | shanghai |    16 |
|    4 | wangwu   | 43.00 | zhejiang |    12 |
+------+----------+-------+----------+-------+
5 rows in set (0.00 sec)

#语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按照第二个字段进行排序，ORDER BY后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定。
#但order by之后的第一个参数只有在出现相同值时，第二个字段才有意义。

#order by可以对多字段进行排序
#对addr升序排序，当addr相同时对level进行降序排序
mysql> select * from game order by addr,level desc;
+------+----------+-------+----------+-------+
| id   | name     | sorce | addr     | level |
+------+----------+-------+----------+-------+
|    2 | lisi     | 51.00 | beijing  |    13 |
|    1 | zhangsan | 49.00 | beijing  |    10 |
|    3 | lisi     | 37.00 | nanjing  |     9 |
|    5 | laoliu   | 38.00 | shanghai |    16 |
|    4 | wangwu   | 43.00 | zhejiang |    12 |
+------+----------+-------+----------+-------+
5 rows in set (0.00 sec)

#order by也可以跟where进行判断
mysql> select * from game where sorce >45 order by addr,level desc;
+------+----------+-------+---------+-------+
| id   | name     | sorce | addr    | level |
+------+----------+-------+---------+-------+
|    2 | lisi     | 51.00 | beijing |    13 |
|    1 | zhangsan | 49.00 | beijing |    10 |
+------+----------+-------+---------+-------+
2 rows in set (0.00 sec)

分组

使用group by进行分组，当分组的字段遇到有相同值时，只会保留第一个记录，所以通常跟count()对分组后的数据进行统计

mysql> select count(*),addr from game group by addr;
+----------+----------+
| count(*) | addr     |
+----------+----------+
|        2 | beijing  |
|        1 | nanjing  |
|        1 | shanghai |
|        1 | zhejiang |
+----------+----------+
4 rows in set (0.01 sec)

#结合group by order by count进行分组排序并统计
mysql> select count(*),addr,sorce from game group by addr order by sorce desc;
+----------+----------+-------+
| count(*) | addr     | sorce |
+----------+----------+-------+
|        2 | beijing  | 49.00 |
|        1 | zhejiang | 43.00 |
|        1 | shanghai | 38.00 |
|        1 | nanjing  | 37.00 |
+----------+----------+-------+
4 rows in set (0.00 sec)

子查询

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。
子语句可以与主语句所查询的表相同，也可以是不同表。子语句中的sql语句是为了，最后过滤出一个结果集，用于主语句的判断条件，子语句通过in和主语句连接。
子语句也可以使用where进行判断，使用order by，group by等。

#子语句使用where判断
mysql> select id,name from game where sorce in (select sorce from ti where sorce > 40);
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    4 | wangwu   |
+------+----------+
3 rows in set (0.00 sec)

#子语句使用group by分组统计
mysql> select id,name from game where id in (select count(*) from ti group by addr);
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

视图

视图是数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射视图可以理解为镜花水月/倒影，动态保存结果集（数据)
简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性本质而言视图是一种select(结果集)
视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

#创建视图
mysql> create view v_game as select * from game where sorce>=38;
Query OK, 0 rows affected (0.00 sec)

#查看视图数据
mysql> select * from v_game;
+------+----------+-------+----------+-------+
| id   | name     | sorce | addr     | level |
+------+----------+-------+----------+-------+
|    1 | zhangsan | 49.00 | beijing  |    10 |
|    2 | lisi     | 51.00 | beijing  |    13 |
|    4 | wangwu   | 43.00 | zhejiang |    12 |
|    5 | laoliu   | 38.00 | shanghai |    16 |
+------+----------+-------+----------+-------+
4 rows in set (0.01 sec)

#对数据表查看
mysql> alter table game add constraint pk_id primary key(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc game;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| sorce | decimal(5,2) | YES  |     | NULL    |       |
| addr  | varchar(20)  | YES  |     | NULL    |       |
| level | int(5)       | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc v_game;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| sorce | decimal(5,2) | YES  |     | NULL    |       |
| addr  | varchar(20)  | YES  |     | NULL    |       |
| level | int(5)       | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#可以发现视图隐藏了原表的主键等属性，增强了数据表的安全性

#用多张表创建组合视图，视图的字段的内容和select后面选取的字段的顺序一一对应
mysql> create view v_zuhe(id,sorce,addr,passwd) as select game.id,game.sorce,game.addr,name.passwd from game,name where game.id=name.id;

mysql> select * from v_zuhe;
+----+-------+---------+--------+
| id | sorce | addr    | passwd |
+----+-------+---------+--------+
|  1 | 49.00 | beijing | abc123 |
+----+-------+---------+--------+
1 row in set (0.01 sec)

#对应的顺序出错任然能创建视图，且对应字段的数据类型会变更。
mysql> create view v_zu(id,sorce,addr,passwd) as select game.addr,egame.sorce,name.passwd,game.addr from game,name where game.id=name.id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_zu;
+---------+-------+--------+---------+
| id      | sorce | addr   | passwd  |
+---------+-------+--------+---------+
| beijing | 49.00 | abc123 | beijing |
+---------+-------+--------+---------+
1 row in set (0.01 sec)

mysql> desc v_zu;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | varchar(20)  | YES  |     | NULL    |       |
| sorce  | decimal(5,2) | YES  |     | NULL    |       |
| addr   | varchar(11)  | YES  |     | NULL    |       |
| passwd | varchar(20)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

视图与表的区别：
视图是己经编译好的sql语句。而表不是。

视图没有实际的物理记录。而表有。

表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改。

视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQn语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。

表属于全局模式中的表，是实表;视图属于局部模式的表，是虚表

视图的建立和删除只影响视图本身，不影响对应的基本表。

正则

在mysql中也可以使用正则。

^	匹配开头的字符
$	匹配末尾的字符
.	匹配单个字符
*	匹配0或多个在它前面的字符
+	匹配前面的字符1到无穷次
' '	匹配' '中的字符串
a|b	匹配a或b
[...]	匹配[]中的任意一个字符
[^...]	匹配不在[]中的字符
{n}	匹配前面的字符n次
{n,m}	匹配前面的字符n-m次
?	匹配前面的字符0-1次

正则在mysql数据库中需要配合where和regexp使用

#匹配l开头的
mysql> select * from game where name regexp '^l';
+----+--------+-------+----------+-------+
| id | name   | sorce | addr     | level |
+----+--------+-------+----------+-------+
|  2 | lisi   | 51.00 | beijing  |    13 |
|  3 | lisi   | 37.00 | nanjing  |     9 |
|  5 | laoliu | 38.00 | shanghai |    16 |
+----+--------+-------+----------+-------+
3 rows in set (0.00 sec)

#匹配name字段中有n或u的
mysql> select * from game where name regexp 'n|u';
+----+----------+-------+----------+-------+
| id | name     | sorce | addr     | level |
+----+----------+-------+----------+-------+
|  1 | zhangsan | 49.00 | 233      |    10 |
|  4 | wangwu   | 43.00 | zhejiang |    12 |
|  5 | laoliu   | 38.00 | shanghai |    16 |
+----+----------+-------+----------+-------+
3 rows in set (0.00 sec)

mysql> select * from game where name regexp '[nu]';
+----+----------+-------+----------+-------+
| id | name     | sorce | addr     | level |
+----+----------+-------+----------+-------+
|  1 | zhangsan | 49.00 | 233      |    10 |
|  4 | wangwu   | 43.00 | zhejiang |    12 |
|  5 | laoliu   | 38.00 | shanghai |    16 |
+----+----------+-------+----------+-------+
3 rows in set (0.00 sec)

匹配没有laoiu字符的
mysql> select * from game where name regexp '[^laoiu]';
+----+----------+-------+----------+-------+
| id | name     | sorce | addr     | level |
+----+----------+-------+----------+-------+
|  1 | zhangsan | 49.00 | 233      |    10 |
|  2 | lisi     | 51.00 | beijing  |    13 |
|  3 | lisi     | 37.00 | nanjing  |     9 |
|  4 | wangwu   | 43.00 | zhejiang |    12 |
+----+----------+-------+----------+-------+
4 rows in set (0.00 sec)
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值