MySQL高级（进阶）SQL语句

2022-10-25 13:04:58

MySQL高级（进阶）SQL语句

MySQL高级（进阶）SQL语句

一、实例准备--制表

1. 表一（商店区域表）

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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> select * from city;
+-----------+
| city_name |
+-----------+
| beijing   |
| nanjing   |
| shanghai  |
| NULL      |
| NULL      |
+-----------+
5 rows in set (0.00 sec)

二、SQL查询/匹配/排序语句

1. select

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

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

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)

三、函数

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(-2.3);
+-----------+
| abs(-2.3) |
+-----------+
|       2.3 |
+-----------+
1 row in set (0.00 sec)

（2）rand()

返回0-1的随机数

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

（3）mod（x,y）

返回x除以y以后的余数

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

（4）power(x,y)

返回x的y次方

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

（5）round(x)

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

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

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

（6）round(x,y)

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

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

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

（7）sqrt(x)

返回x的平方根

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

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

（8）truncate(x,y)

返回数字X截断为y位小数的值

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

mysql> select truncate(3.1415,3);
+--------------------+
| truncate(3.1415,3) |
+--------------------+
|              3.141 |
+--------------------+
1 row in set (0.00 sec)

（9）ceil(x)

返回大于或等于x的最小整数

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

（10）floor(x)

返回小于或等于x的最大整数

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

（11）greatest(x1,x2...)

返回集合中最大的值

mysql> select greatest(3.1415,3,4,5.2);
+--------------------------+
| greatest(3.1415,3,4,5.2) |
+--------------------------+
|                   5.2000 |
+--------------------------+
1 row in set (0.00 sec)

（12）least(x1,x2...)

返回集合中最小的值

mysql> select least(3.1415,3,4,5.2);
+-----------------------+
| least(3.1415,3,4,5.2) |
+-----------------------+
|                3.0000 |
+-----------------------+
1 row in set (0.00 sec)

2. 聚合函数

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

（1）avg()

返回指定列的平均值

mysql> select avg(sales) from store_info;
+------------+
| avg(sales) |
+------------+
|   687.5000 |
+------------+
1 row in set (0.00 sec)

（2）count()

返回指定列中非NULL值的个数

mysql> select count(store_name) from store_info;
+-------------------+
| count(store_name) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

返回指定列中非NULL值且去重的个数

mysql> select count(distinct store_name) from store_info;
+----------------------------+
| count(distinct store_name) |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)

count()
count()包括了所有的列的行数，在统计结果的时候，不会忽略值为NULL的行

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

count("列名")
count("列名")只包括列名那一列的行数，在统计结果的时候，会忽略值为NULL的行

mysql> select count(city_name) from city;
+------------------+
| count(city_name) |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

（3）min()

返回指定列的最小值

mysql> select min(sales) from store_info;
+------------+
| min(sales) |
+------------+
|        250 |
+------------+
1 row in set (0.01 sec)

（4）max()

返回指定列的最大值

mysql> select max(sales) from store_info;
+------------+
| max(sales) |
+------------+
|       1500 |
+------------+
1 row in set (0.00 sec)

（5）sum()

返回指定列的所有值之和

mysql> select sum(sales) from store_info;
+------------+
| sum(sales) |
+------------+
|       2750 |
+------------+
1 row in set (0.00 sec)

3. 字符串函数

常用的字符串函数	说明
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数x和y拼接成一个字符串
substr(x,y)	获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z)	获取从字符串x中第y个位置开始长度为z的字符串
length(x)	返回字符串x的长度
replace(x,y,z)	将字符串z替代字符串x中的字符串y
upper(x)	将字符串x的所有字母变成大写字符
lower(x)	将字符串x的所有字母变成小写字符
left(x,y)	返回字符串x的前y个字符
right(x,y)	返回字符串x的后y个字符
reprat(x,y)	将字符串x重复y次
space(x)	返回x个空格
strcmp(x,y)	比较x和y，返回的值可以为-1,0,1
reverse(x)	将字符串x反转

（1）trim()

返回去除指定格式的值
语法：select trim([[位置] [要移除的字符串] from] 字符串);
[位置]：该值可以为leading（起头），trailing（结尾），both（起头及结尾）。缺省时为both。
[要移除的字符串]：从字串的起头、结尾，或起头即结尾移除的字符串。缺省时为空格。

mysql> select trim('g' from 'guangdong');
+----------------------------+
| trim('g' from 'guangdong') |
+----------------------------+
| uangdon                    |
+----------------------------+
1 row in set (0.00 sec)

mysql> select trim(leading 'g' from 'guangdong');
+------------------------------------+
| trim(leading 'g' from 'guangdong') |
+------------------------------------+
| uangdong                           |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select trim(trailing 'g' from 'guangdong');
+-------------------------------------+
| trim(trailing 'g' from 'guangdong') |
+-------------------------------------+
| guangdon                            |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select trim(both 'g' from 'guangdong');
+---------------------------------+
| trim(both 'g' from 'guangdong') |
+---------------------------------+
| uangdon                         |
+---------------------------------+
1 row in set (0.00 sec)

（2）concat(x,y)

将提供的参数x和y拼接成一个字符串

mysql> select concat (region,' ',store_name) from location where store_name='Beeijing';
+--------------------------------+
| concat (region,' ',store_name) |
+--------------------------------+
| North Beijing                  |
+--------------------------------+
1 row in set (0.00 sec)

如sql_mode开启了PIPES_AS_CONCAT（可使用"select @@SESSION.sql_mode;"或"select @@GLOBAL.sql_mode;"进行查看），"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数相类似，这和Oracle数据库使用方法一样。

mysql> select store_name || ' ' || sales from store_info where store_name='Guanngzhou';
+----------------------------+
| store_name || ' ' || sales |
+----------------------------+
| Guangzhou 1500             |
| Guangzhou 300              |
+----------------------------+
2 rows in set (0.00 sec)

（3）substr(x,y)

获取从字符串x中的第y个位置开始的字符串，跟substring()函数作用相同

mysql> select substr(store_name,3) from location where store_name='Guangzhou'; 
+----------------------+
| substr(store_name,3) |
+----------------------+
| angzhou              |
+----------------------+
1 row in set (0.00 sec)

（4）substr(x,y,z)

获取从字符串x中第y个位置开始长度为z的字符串

mysql> select substr(store_name,3,4) from location where store_name='Guangzhou'';
+------------------------+
| substr(store_name,3,4) |
+------------------------+
| angz                   |
+------------------------+
1 row in set (0.00 sec)

（5）length(x)

返回字符串x的长度

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

（6）replace(x,y,z)

将字符串z替代字符串x中的字符串y

mysql> select replace(store_name,'ng','xx') from location;
+-------------------------------+
| replace(store_name,'ng','xx') |
+-------------------------------+
| Beijixx                       |
| Shaxxhai                      |
| Guaxxzhou                     |
| Shenzhen                      |
+-------------------------------+
4 rows in set (0.00 sec)

（7）upper(x)

将字符串x的所有字母变成大写字符

mysql> select upper(store_name) from location;
+-------------------+
| upper(store_name) |
+-------------------+
| BEIJING           |
| SHANGHAI          |
| GUANGZHOU         |
| SHENZHEN          |
+-------------------+
4 rows in set (0.00 sec)

（8）lower(x)

将字符串x的所有字母变成小写字符

mysql> select lower(store_name) from location;
+-------------------+
| lower(store_name) |
+-------------------+
| beijing           |
| shanghai          |
| guangzhou         |
| shenzhen          |
+-------------------+
4 rows in set (0.00 sec)

（9）left(x,y)

返回字符串x的前y个字符

mysql> select left('Beijing',3);
+-------------------+
| left('Beijing',3) |
+-------------------+
| Bei               |
+-------------------+
1 row in set (0.00 sec)

（10）right(x,y)

返回字符串x的后y个字符

mysql> select right('Beijing',3);
+--------------------+
| right('Beijing',3) |
+--------------------+
| ing                |
+--------------------+
1 row in set (0.00 sec)

（11）reprat(x,y)

将字符串x重复y次

mysql> select repeat('Beijing ',3);
+--------------------------+
| repeat('Beijing ',3)     |
+--------------------------+
| Beijing Beijing Beijing  |
+--------------------------+
1 row in set (0.00 sec)

（12）space(x)

返回x个空格

mysql> select space(10);
+------------+
| space(10)  |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

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

（13）strcmp(x,y)

比较x和y，返回的值可以为-1,0,1
x=y，返回0

mysql> select strcmp(1,1);
+-------------+
| strcmp(1,1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

x<y，返回-1

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

x>y，返回1

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

（14）reverse(x)

将字符串x反转

mysql> select reverse(sales) from store_info;
+----------------+
| reverse(sales) |
+----------------+
| 0051           |
| 052            |
| 003            |
| 007            |
+----------------+
4 rows in set (0.00 sec)

四、分组

1. group by

对group by后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的。
group by有一个原则，就是select后面的所有列中，没有使用聚合函数的列，必须出现在group by 后面。
语法：select "栏位1",sum("栏位2") from "表名" group by "栏位1";

mysql> select store_name,sum(sales) from store_info group by store_name order by sales desc;
+------------+------------+
| store_name | sum(sales) |
+------------+------------+
| Guangzhou  |       1800 |
| Beijing    |        700 |
| Shenzhen   |        250 |
+------------+------------+
3 rows in set (0.00 sec)

"Guangzhou"有两条属性信息，通过group by分组后，同名项将被合并，可通过以下方式确定是否有重名项以及重名次数。

mysql> select store_name,count(store_name),sum(sales) from store_info group by  store_name order by sales desc;
+------------+-------------------+------------+
| store_name | count(store_name) | sum(sales) |
+------------+-------------------+------------+
| Guangzhou  |                 2 |       1800 |
| Beijing    |                 1 |        700 |
| Shenzhen   |                 1 |        250 |
+------------+-------------------+------------+
3 rows in set (0.00 sec)

也可通过前后sales是否变化，判断是否有重名项被合并。

mysql> select store_name,sales,sum(sales) from store_info group by store_name order by sales desc;
+------------+-------+------------+
| store_name | sales | sum(sales) |
+------------+-------+------------+
| Guangzhou  |  1500 |       1800 |
| Beijing    |   700 |        700 |
| Shenzhen   |   250 |        250 |
+------------+-------+------------+
3 rows in set (0.00 sec)

2. having

用来过滤由group by语句返回的记录集，通常与group by语句联合使用。
having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。如果被select的只有函数栏，那就不需要group by子句。
语法：select "栏位1",sum("栏位2") from "表名" group by “栏位1” having (函数条件);

mysql> select store_name,sum(sales) from store_info group by store_name having sum(sales)>1500;
+------------+------------+
| store_name | sum(sales) |
+------------+------------+
| Guangzhou  |       1800 |
+------------+------------+
1 row in set (0.00 sec)

五、别名

栏位别名/表格别名
语法：select "表格别名"."栏位1" [as] "栏位别名" from "表格名" [as] "表格别名";

mysql> select A.store_name as STORE,sum(A.sales) as 'TOTAL_SALES' from store_info as A group by STORE;
+-----------+-------------+
| STORE     | TOTAL_SALES |
+-----------+-------------+
| Beijing   |         700 |
| Guangzhou |        1800 |
| Shenzhen  |         250 |
+-----------+-------------+
3 rows in set (0.00 sec)

六、子查询

连接表格，在where子句或having子句中插入另一个SQL语句
语法：slect "栏位1" from "表格1" where "栏位2" [比较运算符] (select "栏位1" from "表格2" where "条件");
其中：
slect "栏位1" from "表格1" where "栏位2" [比较运算符] 为外查询，
(select "栏位1" from "表格2" where "条件")为内查询。
[比较运算符]可以是符号的运算符，例如=、>、<、>=、<=；也可以是文字的匹配符，例如like、in、between等。

mysql> select sum(sales) from store_info where store_name in (select store_name from location where region='North');
+------------+
| sum(sales) |
+------------+
|        700 |
+------------+
1 row in set (0.00 sec)

注：匹配两个表的列名可以不相同，但列中需有相同内容，否则将返回空值。

mysql> alter table location change store_name name char(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select sum(sales) from store_info where store_name in (select name from location where region='North');
+------------+
| sum(sales) |
+------------+
|        700 |
+------------+
1 row in set (0.00 sec)

mysql> select sum(sales) from store_info where sales in (select name from locattion where region='North');
+------------+
| sum(sales) |
+------------+
|       NULL |
+------------+
1 row in set, 4 warnings (0.00 sec)

码农公寓

MySQL高级（进阶）SQL语句

一、实例准备--制表

1. 表一（商店区域表）

2. 表2（商店销售表）

3. 表3（城市表）

二、SQL查询/匹配/排序语句

1. select

2. distinct

3. where

4. and|or

5. in

6. between

7. limit

8. 通配符

9. like

10. order by

三、函数

1. 数学函数

（1）abs(x)

（2）rand()

（3）mod（x,y）

（4）power(x,y)

（5）round(x)

（6）round(x,y)

（7）sqrt(x)

（8）truncate(x,y)

（9）ceil(x)

（10）floor(x)

（11）greatest(x1,x2...)

（12）least(x1,x2...)

2. 聚合函数

（1）avg()

（2）count()

（3）min()

（4）max()

（5）sum()

3. 字符串函数

（1）trim()

（2）concat(x,y)

（3）substr(x,y)

（4）substr(x,y,z)

（5）length(x)

（6）replace(x,y,z)

（7）upper(x)

（8）lower(x)

（9）left(x,y)

（10）right(x,y)

（11）reprat(x,y)

（12）space(x)

（13）strcmp(x,y)

（14）reverse(x)

四、分组

1. group by

2. having

五、别名

六、子查询

相关文章