Mysql_查询语句（1）

最新推荐文章于 2022-02-16 02:35:15 发布

qq_37032193

最新推荐文章于 2022-02-16 02:35:15 发布

阅读量156

点赞数

分类专栏： sql学习

sql学习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

基本使用

select子句：

select 结果集中的列名 as 自定义列名 from …
select distinct 列名…

from子句：

查询目标表：永久表、临时表（子查询返回的表）、虚拟表（视图）

where子句（在on里面的是连接条件，where里面的是过滤条件）：

过滤条件：

比较操作：=、<>/!=、like（例子：name like ‘_a%e%’;_是单个字符的占位符，%任意个）、in、not in、between 下限 and 上限(上下限是闭合的（即包含在内）)、正则（例子：where lname regexp '^[FG]' 表示匹配首字母是F/G的lname值）等。

¹备注：null关键字：
当使用null时，需记住列表可以为null，但不能=null(???既不是字符串匹配，那又是什么意思？？？)：

where street is null;  *表示过滤该列无入参的

where id !=3. *不会将无id值的显示出来，所以还是加上	or id is null;

连接条件：

a join b:不加on条件的话，会将这两张表置换交叉连接，笛卡尔积运算形成的结果集；
inner join：内连接，多表连接时，没有先后顺序之分（sql是非过程化语言）。如果要求按指定顺序，在mysql中使用straight_join关键字：select straight_join...
备注：在1条sql语句中，连续两次使用同张表时，需要取不同的别名(相当于创建不同实例)，所以自连接也是一样；
不等连接：例如象棋对弈：
左连接left join：由左边的表来决定结果集的行数
右连接right join：由右边的表来决定结果集的行数
交叉连接cross join：

例子：查2008年那天的交易数量

select days.dt,count(t.txn_id)
from transaction t right join 
(select date_add('2018-01-01', interval (ones.num+tens.num+hundreds.num) day) dt
from 
(select 0 num union all
select 1 num union all
select 2 num union all
select 3 num union all
select 4 num union all
select 5 num union all
select 6 num union all
select 7 num union all
select 8 num union all
select 9 num) ones
cross join
(select 0 num union all
select 10 num union all
select 20 num union all
select 30 num union all
select 40 num union all
select 50 num union all
select 60 num union all
select 70 num union all
select 80 num union all
select 90 num) tens
cross join
(select 0 num union all
select 100 num union all
select 200 num union all
select 300 num) hundreds
where date_add('2018-01-01', interval (ones.num+tens.num+hundreds.num) day)<'2019-01-01') days
on days.dt=t.txn_date
group by days.dt
order by 1;

DATE_ADD() 函数向日期添加指定的时间间隔:DATE_ADD(date,INTERVAL expr type)

...
on 表1中的人 < 表2中的人   *逻辑：首先两辆组合条件是不能和自己对弈，所以应该是!= ;然后会出现‘a对弈b’ 和 ‘b对弈a’的情况，所以又要过滤掉*

group by和having子句

在返回结果集前对数据进行分组、过滤等操作。
备注：having子句是针对分组后的数据进行过滤；可以在having子句中包含未在select语句中出现的聚集函数。

select 表列a,count(*) 列b 
from 表
group by 表列a;

错误sql语句,因为在评估where子句时，分组还没创建：
select 表列a,count(*) 列b 
from 表 
where count(*)>4
group by 表列a;

聚集函数：ma x(),min(),arg(),sum(),count()
select count(distinct 表列a)  *看列a有多少个不同的值*；
count(*)会统计所有行，包括null；count(表列名)对包含值的数目进行计数。

对多列进行分组：
select product_id,open_branch_id,sum(avail_balance) hot_balance
from account
group by product_cd,open_branch_id.；*没有用线后顺序的区别？？？*

产生合计数(统计)with rollup、with cube：
select product_id,open_branch_id,sum(avail_balance) hot_balance
from account
group by product_cd,open_branch_id with rollup;

order by子句

升／降序：asc／desc;
可以根据字段、根据表达式、数字占位符进行排序操作，例子：

select * from favorite_food order by right(food,1);  *表示根据food字段的最后1位升序（默认的）排序 *

根据查询返回的第2列和第5列排序：…order by 2;

集合：

集合操作的前提：1、集合要有相同数据的列；2、对应列的数据类型需一致：

并集：集合a union all 集合b（返回的数据不过滤重复数据，union会过滤重复数据）；
交集：集合a intersect 集合b（返回的数据会过滤重复数据）；
差集：集合a except 集合b(会去除所有与b重复的数据，不会考虑次数)；
集合操作后使用order by 排序时，字段要选择集合a的列名；

子查询：

非关联子查询(即子查询可以独立执行)、关联子查询（依附于包含语句并引用其单行或多行）
注意：

如果在等式条件下（即=、><等）使用子查询，而子查询又返回多行结果，会出错；
多行单列子查询常用的运算符：in、not in、< all (运算符与all结合使用：小于子查询集合中的最小值)、> any(只要有1个条件成立，比较就true)
多列子查询：

原sql语句:使用多个子查询

select account_id,product_cd,cust_id from account
where open_branch_id=(select branch_id from branch where name='hhh') and open_emp_id in (select emp_id from employee where title='aaa' or title='bbb');

进阶sql语句：需要结合业务实现，另外可以去对比下执行效率(???sql执行效率蛀牙哦取决于什么？？？)

select account_id,product_cd,cust_id from account
where (open_branch_id,open_emp_id) in (select b.branch_id,e.emp_id from branch b inner join employee e 
on b.branch_id=e.assigned_branch_id where b.name='hhh' and (e.title='aaa' or e.title='bbb'))

关联子查询例子：

select c.cust_id,c.cust_type_id,c.city
from customer c
where 2=(select count(*) from account a where a.cust_id=c.cust_id)

注释：这种查询感觉有点像for循环语句，先从customer中检索出记录，接着为每个客户执行1次子查询，每次执行包含查询都要向子查询传递cust_id，若子查询返回值2，则满足条件，被添加到结果集。
还有子查询条件在左边的情况，如：

where (select sum(a.avail_balance) from acount a where  a.cust_id=c.cust_id) between 5000 and 10000;

查找余额与交易金额总量不匹配的账户的部分解决方案：

select 'Alert!:acount #1 has incorrect balance!' from account 
where (avail_balance,pending_banlance) <>
(select sum(<生成获得的金额语句>),sum(<什么什么金额>) from transaction 
where account_id=1) and account_id=1;

查找余额与交易金额总量不匹配的all账户的部分解决方案,使用关联查询：

select contact('Alert!:acount #',a.account_id,  'has incorrect balance!') 
from account a
where (avail_balance,pending_banlance) <>
(select sum(<生成获得的金额语句>),sum(<什么什么金额>) from transaction  t  where t.account_id=a.account_id);

[not] exists */1应用于关联子查询：

...<包含查询>from a where exists (select * from t where t.id=a.id)

关联子查询操作数据例子：
EX1：

update account a set a.last_activity_date=
(select MAX(t.txn_date) from from transaction t where t.account_id=a.account_id);

PS：sql有漏洞，存在子查询如果为空（即未发生过交易），last_activity_date列的值将会被修改为null，所以修改如下：增加where条件,保护不被null重写：

   update account a set a.last_activity_date=
    (select MAX(t.txn_date) from from transaction t where t.account_id=a.account_id)
    where exists (select 1 from transaction t where t.account_id=a.account_id);

EX2：ps:在mysql的delete语句使用关联子查询时，不能使用表别名，会报错

delete from department 
where not exists (select * from employee where employee_id=department_id);

什么时候使用子查询：
1、可以创建自己需要的数据视图／数据源，适合将其与表一起包含在from子句的子查询里：
2、数据加工，如按照储蓄账户余额的多少对客户分组：
分组表结构：

select 'low' name,0 low_limit,4999.99 high_limit
union all
select 'average' name,5000 low_limit,9999.99 high_limit
select all
select 'heavy' name,10000 low_limit,99999999 high_limit

结合业务对客户进行分组统计：

select groups.name,count(*) num_customers
from
(select sum(a.avail_balance) cust_balance
from account a inner join product p
on a.product_cd=p.product_cd
where p.product_type_cd='account'
group by a.cust_id) cust_rollup
inner join
(select 'low' name,0 low_limit,4999.99 high_limit
    union all
    select 'average' name,5000 low_limit,9999.99 high_limit
    select all
    select 'heavy' name,10000 low_limit,99999999 high_limit) groups
    on cust_rollup.cust_balance between groups.low_limit and groups.high_limit
    group by groups.name;

任何值与null比较的时候需要注意

条件逻辑：

case表达式：

EX:结果集变换：将单列变成单行多列

select 
sum(case
	when extract(year from open_date)=2000 then 1
	else 0
	end ) year_2000,
sum(case
	when extract(year from open_date)=2001 then 1
	else 0
	end ) year_2001,
sum(case
	when extract(year from open_date)=2002 then 1
	else 0
	end ) year_2002 from account
where open_date>'1999-12-31' and open_date<'2003-01-01';

ps:EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

PS：以上均来自于《sql学习指南第2版》

↩︎

qq_37032193

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql_查询语句（1）

基本使用select子句：select 结果集中的列名 as 自定义列名 from …select distinct 列名…from子句：查询目标表：永久表、临时表（子查询返回的表）、虚拟表（视图）where子句（在on里面的是连接条件，where里面的是过滤条件）：过滤条件：比较操作：=、<>/!=、like（例子：name like ‘_a%e%’;_是单个字符...
复制链接

扫一扫

专栏目录