MySQL高阶sql语句（二）

最新推荐文章于 2024-07-03 03:35:17 发布

突币楠波望

最新推荐文章于 2024-07-03 03:35:17 发布

阅读量226

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_35479765/article/details/118759455

版权

本文介绍了MySQL中的视图概念，强调了视图作为虚拟表的特点和应用场景，如简化查询、权限控制等。通过实例展示了如何创建、查询及更新视图，并探讨了表与视图的区别。同时，讲解了联表查询的操作，包括内连接、左外连接和右外连接，并给出了正则表达式在数据检索中的应用。此外，还列举了MySQL中的数学、聚合及字符串等函数，提供了丰富的示例。

摘要由CSDN通过智能技术生成

MySQL视图

视图是数据中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射，视图是在基本表之上建立的表。
视图的应用场景：简化查询结果，针对不同的人（不同权限身份），提供不同结果集的表。视图适合于多表连接查询时使用。不适合增、删、改。
表与视图的区别：
1、视图是已经编译好的sql语句，表不是。
2、视图没有实际的物理记录，表有。
3、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
4、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
5、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
6、视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

实际操作：

mysql> select * from info;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 张三   | 语文    |    90 |
|    2 | 张三   | 数学    |    85 |
|    3 | 张三   | 英语    |    50 |
|    4 | 李四   | 语文    |    87 |
|    5 | 李四   | 数学    |    95 |
|    6 | 李四   | 英语    |    40 |
|    7 | 王五   | 语文    |    70 |
|    8 | 王五   | 数学    |    80 |
|    9 | 王五   | 英语    |    59 |
+------+--------+---------+-------+
9 rows in set (0.00 sec)
mysql> create view v_info as select * from info where score>80; //创建视图，显示分数大于80的记录
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_info;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 张三   | 语文    |    90 |
|    2 | 张三   | 数学    |    85 |
|    4 | 李四   | 语文    |    87 |
|    5 | 李四   | 数学    |    95 |
+------+--------+---------+-------+
4 rows in set (0.00 sec)
mysql> update info set score=90 where name='张三' and subject='英语';
Query OK, 1 row affected (0.00 sec) //修改物理表的数据，会影响到视图的结果
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from v_info;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 张三   | 语文    |    90 |
|    2 | 张三   | 数学    |    85 |
|    3 | 张三   | 英语    |    90 |
|    4 | 李四   | 语文    |    87 |
|    5 | 李四   | 数学    |    95 |
+------+--------+---------+-------+
5 rows in set (0.00 sec)
mysql>

联表查询操作如下
测试数据：

create table test1(id int(4) not null primary key,name varchar(20) not null,subject_id int(4),score double);
create table test2(id int(4) not null primary key,subject_name varchar(20) not null);
alter table test1 add constraint FK_subjectid foreign key(subject_id) references test2(id);
insert into test2 values(1,'yuwen');
insert into test2 values(2,'shuxue');
insert into test2 values(3,'yingyu');
insert into test1 values(1,'zhangsan',1,95);
insert into test1 values(2,'zhangsan',2,80);
insert into test1 values(3,'zhangsan',3,70);
insert into test1 values(4,'lisi',1,50);
insert into test1 values(5,'lisi',2,85);
insert into test1 values(6,'lisi',3,75);
insert into test1 values(7,'wangwu',1,90);
insert into test1 values(8,'wangwu',2,75);
insert into test1 values(9,'wangwu',3,60);

创建连表查询视图

		//创建连表查询视图
mysql> create view v_t1_t2 as select t1.id,t1.name,t2.subject_name,t1.score from test1 t1,test2 t2 where t1.subject_id=t2.id;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_t1_t2; //查看视图
+----+----------+--------------+-------+
| id | name     | subject_name | score |
+----+----------+--------------+-------+
|  1 | zhangsan | yuwen        |    95 |
|  4 | lisi     | yuwen        |    50 |
|  7 | wangwu   | yuwen        |    90 |
|  2 | zhangsan | shuxue       |    80 |
|  5 | lisi     | shuxue       |    85 |
|  8 | wangwu   | shuxue       |    75 |
|  3 | zhangsan | yingyu       |    70 |
|  6 | lisi     | yingyu       |    75 |
|  9 | wangwu   | yingyu       |    60 |
+----+----------+--------------+-------+
9 rows in set (0.00 sec)
mysql>

SQL中的正则表达式

MySQL 正则表达式的使用场景通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式如表所示。

匹配	说明
^	匹配以某个字符串为起始位置
$	匹配以某个字符串为结束位置
*	匹配前面的字符0次或多次
+	匹配前面的字符1次或多次（至少1次）
?	匹配前面的字符串0次或1次
.	匹配任何单个字符
{n}	匹配前面的字符串 n 次
{n,m}	匹配前面的字符串至少n次，至多m次
[…]	匹配字符集合中的任意一个
[^…]	匹配不在字符集合中的任意一个
字符串	匹配包含指定的字符串
字符串a\|字符串b	匹配字符串a 或者字符串b

实际操作：

mysql> select name from test1 where name regexp 'san$';
+----------+
| name     |
+----------+
| zhangsan |
| zhangsan |
| zhangsan |
+----------+
3 rows in set (0.00 sec)
mysql> select name from test1 where name regexp '^li';
+------+
| name |
+------+
| lisi |
| lisi |
| lisi |
+------+
3 rows in set (0.00 sec)
mysql> select name from test1 where name regexp 'zh|li';
+----------+
| name     |
+----------+
| zhangsan |
| zhangsan |
| zhangsan |
| lisi     |
| lisi     |
| lisi     |
+----------+
6 rows in set (0.00 sec)
mysql> select name from test1 where name regexp 'zh.*san';
+----------+
| name     |
+----------+
| zhangsan |
| zhangsan |
| zhangsan |
+----------+
3 rows in set (0.00 sec)
mysql> select name from test1 where name regexp 'sang?';
+----------+
| name     |
+----------+
| zhangsan |
| zhangsan |
| zhangsan |
+----------+
3 rows in set (0.00 sec)
mysql> select name from test1 where name regexp 'sang*';
+----------+
| name     |
+----------+
| zhangsan |
| zhangsan |
| zhangsan |
+----------+
3 rows in set (0.00 sec)
mysql> select name from test1 where name regexp '^[b-n]';
+------+
| name |
+------+
| lisi |
| lisi |
| lisi |
+------+
3 rows in set (0.01 sec)
mysql> select name from test1 where name regexp '^[^b-n]';
+----------+
| name     |
+----------+
| zhangsan |
| zhangsan |
| zhangsan |
| wangwu   |
| wangwu   |
| wangwu   |
+----------+
6 rows in set (0.00 sec)
mysql>

连接查询

使用较多的连接查询类型有：内连接、左外连接、右外连接
内连接：两张或多张表中同时符合某种条件的数据记录的组合，在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件
左外连接：在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，匹配左表中的所有行以及右表中符合条件的行。
右外连接：在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配

mysql> select * from test1 t1 inner join test2 t2 on t1.subject_id=t2.id;
+----+----------+------------+-------+----+--------------+
| id | name     | subject_id | score | id | subject_name |
+----+----------+------------+-------+----+--------------+
|  1 | zhangsan |          1 |    95 |  1 | yuwen        |
|  4 | lisi     |          1 |    50 |  1 | yuwen        |
|  7 | wangwu   |          1 |    90 |  1 | yuwen        |
|  2 | zhangsan |          2 |    80 |  2 | shuxue       |
|  5 | lisi     |          2 |    85 |  2 | shuxue       |
|  8 | wangwu   |          2 |    75 |  2 | shuxue       |
|  3 | zhangsan |          3 |    70 |  3 | yingyu       |
|  6 | lisi     |          3 |    75 |  3 | yingyu       |
|  9 | wangwu   |          3 |    60 |  3 | yingyu       |
+----+----------+------------+-------+----+--------------+
9 rows in set (0.01 sec)
mysql> select * from test1 t1 left join test2 t2 on t1.subject_id=t2.id;
+----+----------+------------+-------+------+--------------+
| id | name     | subject_id | score | id   | subject_name |
+----+----------+------------+-------+------+--------------+
|  1 | zhangsan |          1 |    95 |    1 | yuwen        |
|  2 | zhangsan |          2 |    80 |    2 | shuxue       |
|  3 | zhangsan |          3 |    70 |    3 | yingyu       |
|  4 | lisi     |          1 |    50 |    1 | yuwen        |
|  5 | lisi     |          2 |    85 |    2 | shuxue       |
|  6 | lisi     |          3 |    75 |    3 | yingyu       |
|  7 | wangwu   |          1 |    90 |    1 | yuwen        |
|  8 | wangwu   |          2 |    75 |    2 | shuxue       |
|  9 | wangwu   |          3 |    60 |    3 | yingyu       |
+----+----------+------------+-------+------+--------------+
9 rows in set (0.00 sec)
mysql> select * from test1 t1 right join test2 t2 on t1.subject_id=t2.id;
+------+----------+------------+-------+----+--------------+
| id   | name     | subject_id | score | id | subject_name |
+------+----------+------------+-------+----+--------------+
|    1 | zhangsan |          1 |    95 |  1 | yuwen        |
|    4 | lisi     |          1 |    50 |  1 | yuwen        |
|    7 | wangwu   |          1 |    90 |  1 | yuwen        |
|    2 | zhangsan |          2 |    80 |  2 | shuxue       |
|    5 | lisi     |          2 |    85 |  2 | shuxue       |
|    8 | wangwu   |          2 |    75 |  2 | shuxue       |
|    3 | zhangsan |          3 |    70 |  3 | yingyu       |
|    6 | lisi     |          3 |    75 |  3 | yingyu       |
|    9 | wangwu   |          3 |    60 |  3 | yingyu       |
| NULL | NULL     |       NULL |  NULL |  4 | lishi        |
| NULL | NULL     |       NULL |  NULL |  5 | wuli         |
| NULL | NULL     |       NULL |  NULL |  6 | huaxue       |
+------+----------+------------+-------+----+--------------+
12 rows in set (0.00 sec)
mysql>

MySQL函数

1、数学函数

数学函数	说明
abs(x)	返回x的绝对值
rand()	返回0-1的随机数
mod(x,y)	返回x除以y以后的余数
power(x,y)	返回x的y次方
round(x)	返回离x最近的整数
round(x,y)	保留x的y位小数四舍五入后的值
sqrt(x)	返回x的平方根
truncate(x,y)	返回数字x截断为y位小数后的值
ceil(x)	返回大于或等于x的最小整数
floor(x)	返回小于或等于x的最大整数
greatest(x,y,z…)	返回集合中最大的值
least(x,y,z…)	返回集合中最小的值

2、聚合函数

聚合函数	说明
avg()	返回指定列的平均数
count()	返回指定列中非 NULL 值的个数
min()	返回指定列中的最小值
max()	返回指定列中的最大值
sum	返回指定列的所有值的总和

3、字符串函数

字符串函数	说明
length(x)	返回字符串x的长度
trim(x)	返回去除字符串头尾的空格的值
concat(x,y)	将提供的参数x和y拼接成一个字符串
upper(x)	将字符串x的所有字母转换成大写
lower(x)	将字符串x的所有字母转换成小写
left(x,y)	返回字符串x的前y个字符
rught(x,y)	返回字符串x的后y个字符
repeat(x,y)	将字符串x重复y次
space(x)	返回x个空格
replace(x,y,z)	将字符串x中的y替换成z
strcmp(x,y)	比较x和y，返回的值可以为 -1，0，1
substring(x,y,z)	获取从字符串x中的第y个位置开始（包含第y个的位置）开始长度为z的字符串
reverse(x)	将字符串x的排列顺序反转

4、日期函数

日期函数	说明
curdate()	返回当前时间的年月日
curtime()	返回当前时间的时分秒
now()	返回当前时间的年月日时分秒
month(x)	返回日期x中的月份值
week(x)	返回日期x是年度第几个星期
hour(x)	返回x中的小时值
minute(x)	返回x中的分钟值
second(x)	返回x中的秒钟值
dayofweek(x)	返回x是星期中的星期几
dayofmouth(x)	返回x是本月的第几天
dayofyear(x)	返回x是本年的第几天

突币楠波望

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
MySQL高阶sql语句（二）

MySQL视图视图是数据中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射，视图是在基本表之上建立的表。视图的应用场景：简化查询结果，针对不同的人（不同权限身份），提供不同结果集的表。视图适合于多表连接查询时使用。不适合增、删、改。表与视图的区别：1、视图是已经编译好的sql语句，表不是。2、视图没有实际的物理记录，表有。3、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改4、视图是查看数据表的一种方法，可以查询数据表
复制链接

扫一扫