MySQL数据库高级SQL语句（图文详解！）

准备:先创建一个数据库及表

mysql -uroot -p
show databases;

create database plane;
use plane;
create table destination (
region char(20),
place_name char(20));

insert into destination values('southwest','chengdu');
insert into destination values('north China','beijing');
insert into destination values('southwest','kunming');
insert into destination values('north China','tianjin');
select * from destination;

create table info (
place_name char(20),
sales int(10),
date char(10));

insert into info values('chengdu','350','2021-02-10');
insert into info values('beijing','294','2021-02-10');
insert into info values('kunming','330','2021-02-10');
insert into info values('beijing','392','2021-02-16');
select * from info;

一、MySQL高级语句

1.SELECT

显示表格中一个或数个栏位的所有资料

语法：SELECT "栏位" FROM "表名";
例：
select region from destination;
select date,sales from info;
select date,sales from info;

2.DISTINCT

不显示重复的资料

语法：SELECT DISTINCT "栏位" FROM "表名";
例：
select place_name from info;
select distinct place_name from info;

3.WHERE

有条件查询

语法：SELECT "栏位" FROM "表名" WHERE "条件";
例:
select * from info;
select place_name from info where sales > 300;

4.AND|OR

且|或

语法：SELECT "栏位" FROM "表名" WHERE "条件1" {
   [AND|OR] "条件2"}+ ;
例：
select * from info;
select place_name from info where sales > 350 or sales < 300;
select place_name from info where sales > 300 and sales < 350;

5.IN

显示已知的值的资料

语法：SELECT "栏位" FROM "表名" WHERE "栏位" IN ('值1', '值2', ...);
例：
select * from info where place_name