日常SQL常见问题

将MySQL中的表导出到excel表中：select * into outfile ‘d:/data.xls’ from db_name.tb_name;
Mysql查询结果导出为Excel的几种方法
Data truncated for column ‘字段名’ at row 1 的解决方法 去掉或修改 带有 null 值 的 ( 需要设置 not null 的) 字段
字段类型
python面试题
什么时候用GROUP BY —sq

1.SQL书写顺序

SQL之定义变量（DECLARE）

select  distinct
from
[on >join]
where -- 在查询之前进行的一个约束
group by -- 分组
having --在查完表之后对结果进行过滤，后面可以使用相应的聚合函数，where不可以
order by desc
limit

2.SQL执行顺序

from 表名
where 
group by
having
select distinct  -- 这时候才进行查找
order by
limit 

3.分组排序函数（row_number）问题

--分组排序函数(row_number)
1.无分组排序
row_number() over(order by 字段 desc)
例如：row_number() over(order by 学生成绩 desc)
表示不分班级，所有学生的成绩从高到低排序
2.分组排序 
row_number() over(partition by 字段1 order by 字段2 desc)
表示根据字段1分组，在分组内根据字段2排序，这个函数计算的值就表示每组内部排序后的顺序编号
例如：row_number() over(partition by 班级 order by 学生成绩 desc)
表示根据"班级"分组，在每个"班级"内部根据"学生成绩"排序，这个函数计算的值就表示每组内部排序后的顺序编号
解释：
row_number() 起到编号的功能
partition by 将相同数据进行分区
order by 使得数据按一定顺序排序

-- 8.0版本
select sales_name,sum(sales),row_number() over(order by sum(sales) desc) as 'rank'
from spm_order
group by sales_name;

--5.7版本
set @rank=0;
select A.*,@rank:=@rank + 1 as rank_no from(
select sales_name,sum(sales)
from spm_order
group by sales_name
order by sum(sales) desc) A;

MySQL8.0
select sales_name,city,sum(sales),
row_number() over(partition by sales_name order by sum(sales)desc) as 'rank'
from spm_order
group by sales_name,city;

mysql 5.7
set @r :=0,@type :='';
select
	 @r:=case when @type=a.sales_name then @r+1 else 1 end as rowNum,
	 @type:=a.sales_name as type,
	 a.*
from
	(select sales_name,city,sum(sales)
	 from spm_order
	 group by sales_name,city,
	 order by sales_name,sum(sales)desc)a 

4.MySQL left join操作中on和where放置条件的区别介绍

优先级
两者放置相同条件，之所以可能会导致结果集不同，就是因为优先级。on的优先级是高于where的。
首先明确两个概念：

1. LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
   数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
   在left join下，两者的区别：
2. on是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。
   where则是在生成临时表之后使用的条件，此时已经不管是否使用了left join了，只要条件不为真的行，全部过滤掉。
   测试：
   表1：table1

id	No
1	n1
2	n2
3	n3
表2：table2
No	name
–	–
n1	aaa
n2	bbb
n3	ccc

select a.id,a.No,b.name from table1 a left join table2 b on (a.No = b.No and b.name='aaa');
select a.id,a.No,b.name from table1 a left join table2 b on (a.No = b.No) where b.name='aaa';

第一个结果集：
|id |No |name|
|1 |n1 |aaa|
|2 |n2 |(Null)|
|3 |n3 |(Null)|

第二个结果集：
|id |No |name|
|1 |n1 |aaa|

5.Mysql字符串截取函数SUBSTRING的用法说明

1、从左开始截取字符串
left（str, length）
说明：left（被截取字段，截取长度）
例：select left（content,200） as abstract from my_content_t
2、从右开始截取字符串
right（str, length）
说明：right（被截取字段，截取长度）
例：select right（content,200） as abstract from my_content_t
3、截取字符串
substring（str, pos）
substring（str, pos, length）
说明：substring（被截取字段，从第几位开始截取）
substring（被截取字段，从第几位开始截取，截取长度）
例：select substring（content,5） as abstract from my_content_t
select substring（content,5,200） as abstract from my_content_t
（注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度）
4、按关键字截取字符串
substring_index（str,delim,count）
说明：substring_index（被截取字段，关键字，关键字出现的次数）
例：select substring_index（“blog.jb51.net”，“。”，2） as abstract from my_content_t
结果：blog.jb51
（注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束）
函数简介：
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

6.mysql 中的limit和offset

mysql limit和offset
limit和offset用法
mysql里分页一般用limit来实现；
1.select* from article LIMIT 1,3；
2.select* from article limit 3 offset 1.
上面两种写法都表示取2,3,4三条条数据
当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如
select* from article LIMIT 1,3 就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
当 limit后面跟一个参数的时候，该参数表示要取的数据的数量
例如 select* from article LIMIT 3 表示直接取前三条数据，类似sqlserver里的top语法。
当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。
例如select * from article LIMIT 3 OFFSET 1 表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

7.Oracle中row_number()、rank()、dense_rank() 的区别

详细说明地址

8.

9. 有趣的电影

编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。
例如，下表 cinema:
±--------±----------±-------------±----------+
| id | movie | description | rating |
±--------±----------±-------------±----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
±--------±----------±-------------±----------+
对于上面的例子，则正确的输出是为：
±--------±----------±-------------±----------+
| id | movie | description | rating |
±--------±----------±-------------±----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
±--------±----------±-------------±----------+
我们可以使用 mod(id,2)=1 来确定奇数 id，然后添加 description != ‘boring’ 来解决问题。

select *
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating DESC
;