SQL高级语句（一）

目录

一、准备工作

二、MySQL 高级(进阶) SQL 语句

1、SELECT

2、DISTINCT

3、WHERE

4、AND、OR

5、IN

6、BETWEEN

7、通配符、LIKE

8、ORDER BY

函数

1、数学函数

2、聚合函数

3、字符串函数

4、| | 连接符

5、GROUP BY

6、HAVING

7、别名

8、子查询

---

一、准备工作

1、安装MySQL数据库

2、实验准备，数据表配置

<span style="color:#000000"><span style="background-color:#282c34"><code>mysql -uroot -p
show databases;

create database train_ticket;
use train_ticket;
create table REGION(region varchar(10),site varchar(20));
create table FARE(site varchar(20),money int(10),date varchar(15));

desc REGION;
desc FARE;

insert into REGION values ('south','changsha');
insert into REGION values ('south','nanchang');
insert into REGION values ('north','beijing');
insert into REGION values ('north','tianjin');

insert into FARE values ('changsha',1000,'2021-01-30');
insert into FARE values ('nanchang',700,'2021-01-30');
insert into FARE values ('beijing',1500,'2021-01-30');
insert into FARE values ('tianjin',1200,'2021-01-30');
insert into FARE values ('beijing',2200,'2021-02-05');

select * from REGION;
select * from FARE;
</code></span></span>

二、MySQL 高级(进阶) SQL 语句

1、SELECT

显示表格中一个或数个字段的所有资料
语法：SELECT 字段 FROM 表名

<span style="color:#000000"><span style="background-color:#282c34"><code>select region from REGION;
</code></span></span>

2、DISTINCT

不显示重复的资料（去重）
语法：SELECT DISTINCT 字段 FROM 表名

<span style="color:#000000"><span style="background-color:#282c34"><code>select distinct region from REGION;
</code></span></span>

3、WHERE

有条件查询
语法：SELECT 字段 FROM 表名 WHERE 条件

<span style="color:#000000"><span style="background-color:#282c34"><code>select site from FARE where money > 1000;
select site from FARE where money < 1000;
select site from FARE where money = 1000;
</code></span></span>

4、AND、OR

and（并且）、or（或者）
语法：SELECT 字段 FROM 表名 WHERE 条件1 ([AND|OR] 条件2)+；

<span style="color:#000000"><span style="background-color:#282c34"><code>select site from FARE where money > 1000 and (money < 1500);

select site,money from FARE where money < 500 or (money < 1500 and money >= 700);

select site,money,date from FARE where money >= 500 and (date < '2021-02-05' and money < 1000);
</code></span></span>

5、IN

显示已知的值的资料
语法：SELECT 字段 FROM 表名 WHERE 字段 IN (‘值1’,‘值2’,……);

<span style="color:#000000"><span style="background-color:#282c34"><code>select site,money from FARE where money in (700,1000);
</code></span></span>

6、BETWEEN

显示两个值范围内的资料
语法：SELECT 字段 FROM 表名 WHERE 字段 BETWEEN ‘值一’ and ‘值二’;

<span style="color:#000000"><span style="background-color:#282c34"><code>select * from FARE where money between 500 and 1000;
</code></sp