介绍高级进阶的MySQL数据库SQL语句

免费学习推荐：mysql教程(视频)

一、准备工作

1、安装MySQL数据库

Shell脚本一键部署——源码编译安装MySQL

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

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-3<p>本文来源gao!%daima.com搞$代*!码9网(</p>0');
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;

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

1、SELECT

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

select region from REGION;

2、DISTINCT

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

select distinct region from REGION;

3、WHERE

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

select site from FARE where money > 1000;
select site from FARE where money < 1000;
select site from FARE where money = 1000;

4、AND、OR

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

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);

5、IN

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

select site,money from FARE where money in (700,1000);

6、BETWEEN

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

select * from FARE where money between 500 and 1000;

7、通配符、LIKE

通常通配符都是跟LIKE一起使用

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

LIKE：用于匹配模式来查找资料
语法：SELECT 字段 FROM 表名 WHERE 字段 LIKE ‘模式’;

select * from FARE where site like 'be%';
select site,money from FARE where site like '%jin_';

8、ORDER BY

喜欢 (0)赏

[搞代码]

分享 (0)