MySQL高级（进阶）SQL语句

最新推荐文章于 2023-01-14 10:01:02 发布

「已注销」

最新推荐文章于 2023-01-14 10:01:02 发布

阅读量327

点赞数 1

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/rnmmp2/article/details/120281703

版权

本文详细介绍了MySQL中的高级SQL语句，包括SELECT、DISTINCT、WHERE、JOIN、LIMIT、函数、视图、UNION等，以及各种聚合函数、字符串函数、存储过程等，深入探讨了SQL查询技巧和数据库操作。

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

一、准备实例（建表）

1. 表一（商店区域表）

[root@localhost ~]# mysql -uroot -p666520

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table location (region char(20),store_name char(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into location values('North','Beijing');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('Eaet','shanghai');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('South','Guangzhou');
Query OK, 1 row affected (0.00 sec)

mysql> insert into location values('South','Shenzhen');
Query OK, 1 row affected (0.01 sec)

mysql> select * from location;
+--------+------------+
| region | store_name |
+--------+------------+
| North  | Beijing    |
| East   | Shanghai   |
| South  | Guangzhou  |
| South  | Shenzhen   |
+--------+------------+
4 rows in set (0.00 sec)

2. 表2（商店销售表）

mysql> create table store_info (store_name char(20),sales int(10),date char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into store_info values('Guangzhou',1500,'2020-12-05');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Shenzhen',250,'2020-12-07');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Guangzhou',300,'2020-12-08');
Query OK, 1 row affected (0.00 sec)

mysql> insert into store_info values('Beijing',700,'2020-12-08');
Query OK, 1 row affected (0.00 sec)

mysql> select * from store_info;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |  1500 | 2020-12-05 |
| Shenzhen   |   250 | 2020-12-07 |
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
4 rows in set (0.00 sec)

3. 表3（城市表

mysql> create table city(city_name char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into city values('beijing'),('nanjing'),('shanghai');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into city values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into city values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into city values('');
Query OK, 1 row affected (0.00 sec)

mysql> select * from city;
+-----------+
| city_name |
+-----------+
| beijing   |
| nanjing   |
| shanghai  |
| NULL      |
| NULL      |
|           |
+-----------+
6 rows in set (0.00 sec)

4. 表4（total_sales）

mysql> create table total_sales (name char(20),sales int(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into total_sales values('zhangsan',10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into total_sales values('lisi',15);
Query OK, 1 row affected (0.01 sec)

mysql> insert into total_sales values('wangwu',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into total_sales values('zhaoliu',40);
Query OK, 1 row affected (0.00 sec)

mysql> insert into total_sales values('sunqi',50);
Query OK, 1 row affected (0.00 sec)

mysql> insert into total_sales values('zhouba',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into total_sales values('wujiu',30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from total_sales;
+----------+-------+
| name     | sales |
+----------+-------+
| zhangsan |    10 |
| lisi     |    15 |
| wangwu   |    20 |
| zhaoliu  |    40 |
| sunqi    |    50 |
| zhouba   |    20 |
| wujiu    |    30 |
+----------+-------+
7 rows in set (0.00 sec)

二、SQL语句

1. select

显示表格中一个或数个栏位的所有资料
语法：select “栏位” from “表名”;

2. distinct

不显示重复的资料
语法：select dstinct “栏位” from “表名”;

mysql> select distinct store_name from store_info;
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Beijing    |
+------------+
3 rows in set (0.00 sec)

3. where

有条件查询
语法：select “栏位” from “表名” where “条件”;

mysql> select store_name from store_info where sales>1000;
+------------+
| store_name |
+------------+
| Guangzhou  |
+------------+
1 row in set (0.00 sec)

4. and与or

且、或
语法：select “栏位” from “表名” where “条件1” {[and|or] “条件2”}…;

mysql> select store_name from store_info where sales>1000 or (sales<500 and sales>200);
+------------+
| store_name |
+------------+
| Guangzhou  |
| Shenzhen   |
| Guangzhou  |
+------------+
3 rows in set (0.00 sec)

5. in

显示已知的值的资料
语法：select “栏位” from “表名” where “栏位” in (“值1”,“值2”,…);

mysql> select * from store_info where store_name in ('Beijing','Shenzhen');
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Shenzhen   |   250 | 2020-12-07 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)

6. between

显示两个值范围内的资料
语法：select “栏位” from “表名” where “栏位” between ‘值1’ and ‘值2’;

mysql> select * from store_info where date between '2020-12-06' and '2020-12-10';
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Shenzhen   |   250 | 2020-12-07 |
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
3 rows in set (0.00 sec)

7. limit

（1）显示前几行内容
语法：select “栏位” from “表名” limit ‘行数’;

mysql> select * from store_info limit 2;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |  1500 | 2020-12-05 |
| Shenzhen   |   250 | 2020-12-07 |
+------------+-------+------------+
2 rows in set (0.00 sec)

（2）显示第几行后的前几行
语法：select “栏位” from “表名” limit “第几行后,显示行数”;

mysql> select * from store_info limit 2,2;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)

8. 通配符

通常通配符都是跟like一起使用的

% 		百分号表示零个、一个或多个字符
_ 		下划线表示单个字符

例如：

‘A-Z’
表示所有以’A’起头，另一个任何值的字符，且以’Z’为结尾的字符串。例如，'ABZ’和’A2Z’都符合这一个模式，而’ABCZ’并不符合（因为A和Z之间有两个字符，而不是一个字符）
‘ABC%’
表示所有以’ABC’起头的字符串。例如，'ABCD’和’ABCABC’都符合这个模式。
‘%XYZ’
表示所有以’XYZ’结尾的字符串。例如，'WXYZ’和’ZZXYZ’都符合这个模式。
‘%AN%’
表示所有含有’AN’这个模式的字符串。例如，'SHANGHAI’和’XIAN’都符合这个模式。
‘_AN%’
表示第二个字母为’A’且第三个字母为’N’的字符串。例如，'HANGZHOU’和’LANZHOU’都符合这个模式，而’SHANGHAI’和’XIAN’则不符合这个模式。

9. like

匹配一个模式来搜查资料
语法：select “栏位” from “表名” where “栏位” like {模式};

mysql> select * from store_info where store_name like '%e%';
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Shenzhen   |   250 | 2020-12-07 |
| Beijing    |   700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)

10. order by

按关键字排序
语法：select “栏位” from “表名” [where “条件”] order by “栏位” [ASC,DESC];
ASC是按照升序进行排序的，是默认的排序方式。
DESC是按降序方式进行排序。

mysql> select store_name,sales,date from store_info order by sales desc;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Guangzhou  |  1500 | 2020-12-05 |
| Beijing    |   700 | 2020-12-08 |
| Guangzhou  |   300 | 2020-12-08 |
| Shenzhen   |   250 | 2020-12-07 |
+------------+-------+------------+
4 rows in set (0.00 sec)

mysql> select store_name,sales,date from store_info order by sales asc;
+------------+-------+------------+
| store_name | sales | date       |
+------------+-------+------------+
| Shenzhen   |   250 | 2020-12-07 |
| Guangzhou  |   300 | 2020-12-08 |
| Beijing    |   700 | 2020-12-08 |
| Guangzhou  |  1500 | 2020-12-05 |
+------------+-------+------------+
4 rows in set (0.00 sec)

三、函数

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(x1,x2…)	返回集合中最大的值
least(x1,x2…)	返回集合中最小的值

例：

（1）abs(x)

返回x的绝对值

mysql> select abs(-3.2);
+-----------+
| abs(-3.2) |
+-----------+
|       3.2 |
+-----------+
1 row in set (0.01 sec)

（2）rand()

返回0-1的随机数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.1485054010045765 |
+--------------------+
1 row in set (0.00 sec)

（3）mod（x,y）

返回x除以y以后的余数

mysql> select mod(6,4);
+----------+
| mod(6,4) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

（4）power(x,y)

返回x的y次方

mysql> select power(3,2);
+------------+
| power(3,2) |
+------------+
|          9 |
+------------+
1 row in set (0.00 sec)

（5）round(x)

返回离x最近的整数,即四舍五入到个位

mysql> select round(3.423421)
    -> ;
+-----------------+
| round(3.423421) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(3.623421);
+-----------------+
| round(3.623421) |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.00 sec)

（6）round(x,y)

返回x的y位小数且四舍五入后的值

mysql> select round(3.144,2);
+----------------+
| round(3.144,2) |
+----------------+
|           3.14 |
+----------------+
1 row in set (0.00 sec)


mysql> select round(3.147,2);
+----------------+
| round(3.147,2) |
+----------------+
|           3.15 |
+----------------+
1 row in set (0.00 sec)

（7）sqrt(x)

返回x的平方根

mysql> select sqrt(16);
+----------+
| sqrt(16) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select sqrt(15);
+-------------------+
| sqrt(15)          |
+-------------------+
| 3.872983346207417 |
+-------------------+
1 row in set (0.00 sec)

（8）truncate(x,y)

返回数字X保留为y位小数的值

mysql

最低0.47元/天解锁文章

「已注销」

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
MySQL高级（进阶）SQL语句

一、准备实例（建表）1. 表一（商店区域表）[root@localhost ~]# mysql -uroot -p666520mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create tabl
复制链接

扫一扫