mysql高阶语句一

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,money from FARE where money>1000;

在这里插入图片描述

4.AND|OR:且|或

SELECT "栏位" FROM "表名" WHERE "条件1" {[AND|OR]"条件2"}+;

select site,money from FARE where money > 1000 and money < 2200;
select site,money from FARE where money > 1200 or money < 1000;
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 * from FARE where site in ('tianjin','beijing');

在这里插入图片描述

6.BETWEEN:显示两个值范围内的资料

语法: SELECT "栏位" FROM "表名" WHERE "栏位" BETWEEN '值1' AND '值2';

select site,money from FARE where money between '900' and '2100';

在这里插入图片描述

7、通配符:通常通配符都跟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:按关键字排序

按关键字排序
语法:SELECT 字段 FROM 表名 [WHERE 条件] ORDER BY 字段 [ASC,DESC];
#ASC:按照升序进行排序,默认的排序方式
#DESC:按照降序进行排序

在这里插入图片描述

9.GROUP BY:对GROUP BY后面的栏位的查询结果进行汇总分组

语法: SELECT "栏位1", SUM("栏位2") FROM "表名" GROUP BY "栏位1";

select site,sum(money) from FARE group by site;
select site,sum(money),date from FARE group by site order by money desc;
select site,count(money),sum(money),date from FARE group by site order by money desc;

在这里插入图片描述

10.HAVING:用来过滤由GROUP BY 语句返回的记录表

语法:SELECT "栏位1", SUM("栏位2") FROM "表格名" GROUP BY "栏位1" HAVING (函数条件);
例:
select site,count(money),sum(money),date from FARE group by site having sum(money) >=700;

在这里插入图片描述

11.别名:alias

语法:SELECT "表格別名"."栏位1" [AS] "栏位別名" FROM "表格名" [AS] "表格別名";
例:                           ##AS可以加也可以不加
select RE.region  AS reg, count(site) from REGION AS RE group by reg;  

select FA.site AS si,sum(money),count(money),date AS da from FARE AS FA group by si;

在这里插入图片描述

12.子查询:连接表格

语法:
#外查询
SELECT "栏位1" FROM "表格1" WHERE "栏位2" [比较运算符]
#内查询
(SELECT "栏位1" FROM "表格2" WHERE "条件");
#可以是符号的运算符,例如:=、>、<、>=、<= ;也可以是文字的运算符,例如 LIKE、IN、BETWEEN

select A.site,region from REGION AS A where A.site in(select B.site from FARE AS B where money<2000);

select B.site,money,date from FARE AS B where site in(select A.site from REGION

在这里插入图片描述

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值