【MySQL】查询顺序与查询SQL

【MySQL】查询顺序与查询SQL

SQL执行过程

执行过程

我们发送一条SQL给数据库，其实数据库并没有马上执行，而是进行预处理，对SQL进行分析和优化，可以说最终执行的SQL不一定是我们提交的SQL。整个过程非常繁杂，详细过程见下图：

虚拟表

数据库提供临时表、内存表、永久表，虚拟表一般要么是临时表，要么是内存表，用完就删除。永久表则数据会存储在磁盘中。

游标

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

游标类似java中的foreach循环，每次调用都自动走向下一行数据。

执行顺序

(8) SELECT (9) DISTINCT column,… 选择字段、去重

(6) AGG_FUNC(column or expression),… 聚合

(1) FROM [left_table] 选择表

(3) <join_type> JOIN <right_table> 链接

(2) ON <join_condition> 链接条件

(4) WHERE <where_condition> 条件过滤

(5) GROUP BY <group_by_list> 分组

(7) HAVING <having_condition> 分组过滤

(10) ORDER BY <order_by_list> 排序

(11) LIMIT count OFFSET count; 分页

SELECT

a. select_expr
    -- 可以使用表达式（计算公式、函数调用、字段也是个表达式）
        select stu, 29+25, now() from tb;
    -- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。
        - 使用 as 关键字，也可省略 as.
        select stu+10 as add10 from tb;

	①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
	② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

	数值型和日期型的常量值必须用单引号引起来，数值型不需要
        
b. FROM 子句
    用于标识查询来源。
    -- 可以为表起别名。使用as关键字。
        SELECT * FROM tb1 AS tt, tb2 AS bb;
    -- from子句后，可以同时出现多个表。
        -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。
        SELECT * FROM tb1, tb2;
    -- 向优化符提示如何选择索引
        USE INDEX、IGNORE INDEX、FORCE INDEX
        SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
        SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;

c. WHERE 子句
    -- 从from获得的数据源中进行筛选。
    -- 整型1表示真，0表示假。
    -- 表达式由运算符和运算数组成。
        -- 运算数：变量（字段）、值、函数返回值
        -- 运算符：
            =, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
            in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
            is/is not 加上ture/false/unknown，检验某个值的真假
            <=>与<>功能相同，<=>可用于null比较

	-- 条件表达式
	> < >= <= = != <>不等于，注意等于是=

	-- 逻辑表达式
		and（&&）:两个条件如果同时成立，结果为true，否则为false
		or(||)：两个条件只要有一个成立，结果为true，否则为false
		not(!)：如果条件成立，则not后为false，否则为true
		and并且，or或者 需要加括号

	-- 模糊查询
	like
	通配符：
		%任意多个字符
		_任一个字符,可以用转义符

	between x and y 在x和y之间的值
	WHERE sal BETWEEN 5000 AND 10000;
	等价于
	WHERE sal >= 5000 AND sal <= 10000;

	in//NOT IN
	SELECT * FROM `employees` WHERE job_id IN ['AD_VP','AD_PRES']不可以写通配符，可以写()

	SELECT `last_name`,`commission_pct` FROM employees WHERE `commission_pct` IS NOT NULL;等号不能用于判断null值
	SELECT `last_name`,`commission_pct` FROM employees WHERE `commission_pct` <=> 120;安全等于。即可以判断NULL，又可以判断数值

d. GROUP BY 子句, 分组子句
    对查询的结果进行分组统计。升序：ASC，降序：DESC

    以下[合计函数]需配合 GROUP BY 使用：
-- 聚合函数 aggregation
    count 返回不同的非NULL值数目  count(*)、count(字段)
    	字段、*、常量值，一般放1
    	建议使用 count(*)统计行数，括号内任意数字或任意常量也可以统计行数
    max 求最大值
    min 求最小值
    	max、min、count可以处理任何数据类型
    sum 求和
    avg 求平均值
    	sum和avg一般用于处理数值型
    group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。

	以上五个分组函数都忽略null值，avg也只计算非null，除了count(*)
	都可以搭配distinct使用，用于统计去重后的结果
	不能在 WHERE 子句中使用组函数。
	可以在 HAVING 子句中使用组函数。

	分组前筛选： 原始表 group by的前面 where
	分组后筛选： 分组后的结果集 group by的后面 having
		   
select avg(score) from stu where class='1';
select avg(score) from stu group by class;

#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;


#2.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) DIFFRENCE
FROM employees;
#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

#4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;
#5.选择具有各个job_id的员工人数
SELECT COUNT(*) 个数,job_id
FROM employees
GROUP BY job_id;

e. HAVING 子句，条件子句
	having 分组后数据的过滤，就是where语句，只是having专门配合groupby
    where 在开始时执行检测数据，对原数据进行过滤。
    having 对筛选出的结果再次进行过滤。
    having 字段必须是查询出来的，where 字段必须是数据表存在的。
    where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
    where 不可以使用合计函数。一般需用合计函数才会用 having
    SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。
	where 不可以使用聚合函数。一般需用聚合函数才会用 having，where执行顺序大于聚合函数。
	where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
	聚合函数是比较where、having 的关键。若须引入聚合函数来对group by 结果进行过滤 则只能用having。

使用 HAVING 过滤分组前提：
1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。

不可以使用having的情况：筛选条件的列没有出现在查询select查询字段中

having的原理是先select 然后从select出来的进行筛选。而where是先筛选在select。

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000 -- 在这里，我们不能用where来筛选超过1000的地区，因为表中不存在这样一条记录。

select sum(score)
from student  
where sex='man'
group by name
having sum(score)>210

-- 平均工资小于8000的部门
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal)<8000

-- 学生姓名重名名单*
SELECT NAME,COUNT(NAME) FROM student
GROUP BY NAME
HAVING COUNT(NAME)>1

f. ORDER BY 子句，排序子句
	order by 排序的字段|表达式|函数|别名 【asc|desc】
    支持多个字段的排序。
    
    ORDER BY cno,degree DESC;
	ORDER BY class DESC,sbirthday ASC;

g. LIMIT 子句，限制结果数量子句
    仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。
    limit 【起始的条目索引,最大条目数】;-- 0开始  -- 第二个参数为-1代表打印后面所有
    limit 起始位置, 获取条数
    省略第一个参数，表示从索引0开始。limit 获取条数

	SELECT * FROM emp LIMIT 3	  #返回前3条
	SELECT * FROM emp LIMIT 0,3 #返回前3条（offset,count）offset从0开始
	SELECT * FROM emp LIMIT 1,3 #返回第2到4条，共计3条
	分页：select * from  表 limit （page-1）*sizePerPage,sizePerPage
	每页显示条目数sizePerPage
	要显示的页数 page

h. DISTINCT, ALL 选项
    distinct 去除重复记录
    默认为 all, 全部记录
    

子查询 subquery

子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。需用括号包裹。

子查询：内层被嵌套的select

• 子查询都放在小括号内
• 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
• 查询结果：
  • 结果集只有一行：一般搭配单行操作符使用：> < = <> >= <=
  • 结果集有多行：一般搭配多行操作符使用：any、all、in、not in

主查询：外层的select

-- from型
    from后要求是一个表，必须给子查询结果取个别名。
    - 简化每个查询内的条件。
    - from型需将结果生成一个临时表格，可用以原表的锁定的释放。
    - 子查询返回一个表，表型子查询。
    select * from (select * from tb where id>0) as subfrom where id>1;
-- where型
    - 子查询返回一个值，标量子查询。
    - 不需要给子查询取别名。
    - where子查询内的表，不能直接用以更新。
    select * from tb where money = (select max(money) from tb);
    -- 列子查询
        如果子查询结果返回的是一列。
        使用 in 或 not in 完成查询
        exists 和 not exists 条件
            如果子查询返回数据，则返回1或0。常用于判断条件。
            select column1 from t1 where exists (select * from t2);
    -- 行子查询
        查询条件是一个行。
        select * from t1 where (id, gender) in (select id, gender from t2);
        行构造符：(col1, col2, ...) 或 ROW(col1, col2, ...)
        行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。
    
单行子查询 =
select * from emp where deptno = (select deptno from emp where ename='tony');

多行子查询 in
select * from emp where job in (select distinct job from emp);

    -- 特殊运算符
    != all()    相当于 not in
    = some()    相当于 in。any 是 some 的别名
    != some()   不等同于 not in，不等其中某一个。
    all, some 可以配合其他运算符一起使用。

union

    将多个select查询的结果组合成一个结果集合。
    SELECT ... UNION [ALL|DISTINCT] SELECT ...
    默认 DISTINCT 方式，即所有返回的行都是唯一的
    建议，对每个SELECT查询加上小括号包裹。
    ORDER BY 排序时，需加上 LIMIT 进行结合。
    需要各select查询的字段数量一样。
    每个select查询的字段列表(数量、类型)应一致，因为结果中的字段名以第一条select语句为准。

列数一致，类型相同

union代表去重，union all代表不去重

union 并集去重

select * from dept
union
select * from dept
union
select * from dept

union all 并集

会有重复数据，不进行合并

select * from dept
union all
select * from dept

intersect 交集

select * from dept where loc is not null
intersect
select * from dept

minus 差集

mysql不支持，可以使用leftjoin变相实现，而oracle支持，很少用

select * from dept where loc is not null
minus
select * from dept

select * from dept
minus
select * from dept where loc is not null

注意：两者的差异，第一个结果为空，第二个有一条记录，为何？因为差集的意思是返回存在在第一个集合中，不存在在第二个集合中的数据。和数学上的差集有所不同。

join

/* 连接查询(join) */ ------------------
    将多个表的字段进行连接，可以指定连接条件。
-- 内连接(inner join)
    - 默认就是内连接，可省略inner。
    - 只有数据存在时才能发送连接。即连接结果不能出现空行。
    on 表示连接条件。其条件表达式与where类似。也可以省略条件（表示条件永远为真）
    也可用where表示连接条件。
    还有 using, 但需字段名相同。 using(字段名)

    -- 交叉连接 cross join
        即，没有条件的内连接。
        select * from tb1 cross join tb2;
-- 外连接(outer join)
    - 如果数据不存在，也会出现在连接结果中。
    -- 左外连接 left join
        如果数据不存在，左表记录会出现，而右表为null填充
    -- 右外连接 right join
        如果数据不存在，右表记录会出现，而左表为null填充
-- 自然连接(natural join)
    自动判断连接条件完成连接。
    相当于省略了using，会自动查找相同字段名。
    natural join
    natural left join
    natural right join

select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;

**笛卡尔积 **Cartesian product

又称直积。一般笛卡尔积没有实际的业务意义，但作为概念必须了解，多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后在进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

阿里禁止三表联查

内连接 inner

#显示部门2的员工和工资

SELECT d.dname,e.ename,e.sal FROM emp e,dept d

WHERE e.deptno=d.deptno AND e.deptno=2

SELECT

d.dname,e.ename,e.job

FROM

(SELECT deptno,dname FROM dept) d

INNER JOIN

(SELECT deptno,ename,job FROM emp) e

ON d.deptno = e.deptno

WHERE d.deptno=2

左连接 left

也称为左外连接left outer join

SELECT

d.dname,e.ename,e.job

FROM

(SELECT deptno,dname FROM dept) d

LEFT JOIN

(SELECT deptno,ename,job FROM emp) e

ON d.deptno=e.deptno

下面是Oracle的独特语法，更加简洁，但不推荐，没有上面的语法更加清晰

select * from dept d,emp e where d.deptno=e.deptno(+)

select d.dname,e.ename,e.job from dept d,emp e

where d.deptno = e.deptno(+)

在使用left join时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

先进行on过滤，后进行where过滤

使用left join时on后面的条件只对右表有效

假设有两张表：

表1：tab2

id	size
1	10
2	20
3	30

表2：tab2

size	name
10	AAA
20	BBB
20	CCC

两条SQL:

1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

1、第一条SQL的过程：select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’

1.1、中间表
on条件: tab1.size = tab2.size

ab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	20	BBB
2	20	20	CCC
3	30	(null)	(null)

1.2、再对中间表过滤
where 条件：tab2.name=’AAA’

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA

2、第二条SQL的过程：select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	(null)	(null)
3	30	(null)	(null)

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

右连接 right

也称为右外连接right outer join

select

d.dname,e.ename,e.job

from

(select deptno,dname from dept) d

right join

(select deptno,ename,job from emp) e

on d.deptno = e.deptno

下面是Oracle的独特语法：

select * from dept d,emp e where d.deptno(+) = e.deptno

select d.dname,e.ename,e.job from dept d,emp e

where d.deptno(+) = e.deptno

自连接 self

一般只分为：内连接、左连接、右连接，自连接是指在同一张表的连接查询，下面代码本质还是内连接。

#通过别名区分

SELECT worker.ename, boss.ename FROM emp worker, emp boss

WHERE worker.mgr = boss.empno AND worker.ename = ‘tony’;

#简写

select w.ename, b.ename from emp w, emp b

where w.mgr = b.empno and w.ename = ‘tony’;

万能连接

多表联查有个技巧，其实所有的连接方式都可以转换为左连接！如果记录内容完整，如每个部门对应有员工，每个员工对应有部门，内连接就等价左连接，结果内容一样。右连接是按右侧表关联，那把它换到左边，那不就是左连接，所以换先位置它们就等价。所以记住左连接写法即可。

左连接写的过程，写它是有套路的，这个套路记住，就特别简单。

实现步骤：

l 第一步：左边查询括号写别名

l 第二步：右边查询括号写别名

l 第三步：left join恒中间，后面加on是条件

l 第四步：select挑字段，别名后面写字段

With

使用with子句可以让子查询重用相同的with查询块，通过select调用，很少用

WITH num AS (SELECT d.deptno FROM dept d WHERE d.deptno=1)

SELECT e.ename,e.job,e.sal FROM emp e WHERE e.deptno IN (SELECT * FROM num);

if \case\while

-- if语句
if(expr1,expr2,expr3)  双分支。
if search_condition then
    statement_list   
[elseif search_condition then
    statement_list]
...
[else
    statement_list]
end if;

SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;

-- switch case
java中
switch(变量或表达式){
	case 常量1：语句1;break;
	...
	default:语句n;break;
}
mysql中：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary 
END AS 新工资
FROM employees;

-- case when
case语句 处理多分支
	when情况1  then处理等值判断
    when情况2  then处理条件判断
    ELSE
    END
case when [condition] then result [when [condition] then result ...] [else result] end   
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END

mysql中：
case[case后没有语句直接when]
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end


#案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别


SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

-- while循环
[begin_label:] while search_condition do
    statement_list
end while [end_label];

- 如果需要在循环内提前终止 while循环，则需要使用标签；标签需要成对出现。

    -- 退出循环
        退出整个循环 leave
        退出当前循环 iterate
        通过退出的标签决定退出哪个循环

行列转置

各科最好成绩

分组方式：

SELECT cno,MAX(degree) degree FROM scores

GROUP BY cno;

行列转置：

SELECT

MAX(CASE cno WHEN ‘3-105’ THEN degree ELSE 0 END) ‘3-105’,

MAX(CASE cno WHEN ‘3-245’ THEN degree ELSE 0 END) ‘3-245’,

MAX(CASE cno WHEN ‘6-106’ THEN degree ELSE 0 END) ‘6-106’,

MAX(CASE cno WHEN ‘6-166’ THEN degree ELSE 0 END) ‘6-166’

FROM scores;

函数

-- 字符串函数
length(string)          -- string长度，字节，中文是3B
char_length(string)     -- string的字符个数
substring(str, position [,length])      -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str)   -- 在str中用replace_str替换search_str
instr(string ,substring)    -- 返回substring首次在string中出现的位置
concat(string [,...])   -- 连接字串
	concat(str1,str2,...])
	concat_ws(separator,str1,str2,...)
charset(str)            -- 返回字串字符集
lcase(string)           -- 转换成小写
left(string, length)    -- 从string2中的左边起取length个字符
load_file(file_name)    -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad)   -- 重复用pad加在string开头,直到字串长度为length
ltrim(string)           -- 去除前端空格
repeat(string, count)   -- 重复count次
rpad(string, length, pad)   --在str后用pad补充,直到长度为length
rtrim(string)           -- 去除后端空格
strcmp(string1 ,string2)    -- 逐字符比较两字串大小

-- upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;

-- 3.1 首字母大写
SELECT 
dname,
SUBSTR(dname,1,1),UPPER(SUBSTR(dname,1,1)),
SUBSTR(dname,2,LENGTH(dname)),
CONCAT(UPPER(SUBSTR(dname,1,1)),SUBSTR(dname,2,LENGTH(dname)))
FROM dept;

#4.substr、substring 注意：索引从1开始
substr截取子串（字符串，开始位置从1开始，长度中文也是1个）
SUBSTR('HelloWorld',1,5) Hello
SELECT dname,SUBSTR(dname,1,3) FROM dept;

#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
FROM employees;
	
#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八䩠 'AS out_put;
instr 返回子串第一次出现的索引，如果找不到返回0
INSTR('HelloWorld', 'W') 6
instr返回子串第一次出现的索引
             
#6.trim去前后空格
SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaa张aaa翠山aaaaa')  AS out_put;
trim去前后指定的空格和字符(字符串，去除内容' ')
ltrim去左边空格
rtrim去右边空格
TRIM('H' FROM 'HelloWorld')  elloWorld             
             
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
LPAD(salary,10,'*')  *****24000
lpad左填充(字符串，填充后的长度，填空内容'')
             
#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
RPAD(salary, 10, '*')  24000*****
rpad右填充
                          
#9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
replace替换(字符串，被替换内容，用什么替换)
REPLACE('abcd','b','m')  amcd
SELECT loc,REPLACE(loc,'区','区域') FROM dept; 
             

             
-- UUID          
SELECT UUID()
SELECT 
REPLACE(UUID(),'-','') UUID,
LENGTH(REPLACE(UUID(),'-','')) len
返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

-- 转义字符
单撇作为sql语句符号，内容中出现单撇就会乱套，进行转义即可
SELECT 'ab\'cde'
### **distinct**

-- distinct   去重        
SELECT loc FROM dept;
SELECT DISTINCT loc FROM dept;

-- nvl
-- 字段值为null时替换，loc为null替换为‘无’
insert into dept (deptno,dname) values(4,'workspace');
select deptno,dname,nvl(loc,'无') as loc from dept;

#二、数学函数
             
abs(x)          -- 绝对值 abs(-10.9) = 10
format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x)         -- 向上取整 ceil(10.1) = 11
floor(x)        -- 向下取整 floor (10.1) = 10
round(x)        -- 四舍五入去整
pi()            -- 获得圆周率
pow(m, n)       -- m^n
sqrt(x)         -- 算术平方根
rand()          -- 随机数
truncate(x, d)  -- 截取d位小数             
mod(m, n)       -- m%n m mod n 求余 

#三、日期函数       
             
select sysdate from dual			#oracle当前时间，dual内置虚拟表 
             
now()当前系统日期+时间 SELECT NOW();
curdate()当前系统日期，不包含时间 SELECT CURDATE();
curtime()当前系统时间，不包含日期 SELECT CURTIME();
now(), current_timestamp();     -- 当前日期时间
current_date();                 -- 当前日期
current_time();                 -- 当前时间             
date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分  
             
-- last_day
-- 每月最大日期
SELECT ename,hiredate,LAST_DAY(hiredate) FROM emp

-- 每月的第一天和最后一天
SELECT DATE_FORMAT(hiredate,'%Y-%m-01'),LAST_DAY(hiredate) FROM emp

-- date
SELECT hiredate,YEAR(hiredate),MONTH(hiredate),DAY(hiredate) FROM emp

-- year & month & day
-- 年月日，时分秒
SELECT hiredate,YEAR(hiredate) ,MONTH(hiredate),DAY(hiredate)
FROM emp

SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()),NOW()

#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;

SELECT  YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
             
-- str_to_date
-- 字符串转日期,中间的符号要一致
SELECT STR_TO_DATE('2020-05-08','%Y-%m-%d') FROM emp          

#查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
或
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
%Y四位的年份 %y二位的年份 %m二位的月份 %c月份 %d二位的日 %H二十四制小时 %h十二制小时 %i二位制分钟 %s二位制秒
       
-- date_format
-- 日期转字符串，注意格式的大小写，不错错误
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
             
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp();               -- 获得unix时间戳
from_unixtime();                -- 从时间戳获得时间
             
mysql 查询当天、本周，本月，上一个月的数据
	今天
	where to_days(时间字段名) = to_days( now() );

	昨天
	WHERE TO_DAYS( NOW() ) - TO_DAYS( 时间字段名 ) <= 1

	近7天
	where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
	近30天
	where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

	本月
	WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

	上一月
	WHERE PERIOD_DIFF( date_format( now() , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

	查询本季度数据
	where QUARTER(create_date)=QUARTER(now());
	查询上季度数据
	where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

	查询本年数据
	where YEAR(create_date)=YEAR(NOW());

	查询上年数据
	where year(create_date)=year(date_sub(now(),interval 1 year));

	查询当前这周的数据
	WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

	查询上周的数据
	SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

	查询上个月的数据
	select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

	select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

	select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 

	select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

	select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

	select * from user where pudate between  上月最后一天  and 下月第一天 

	查询当前月份的数据 
	where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

	查询距离当前现在6个月的数据
	where submittime between date_sub(now(),interval 6 month) and now();

#四、其他函敍
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

-- 其他常用函数
md5();
default();