外联约束关系的取消mysql_SQL Cookbook精简版（只有MySql）

工欲善其事，必先利其器。既然以后偏数据，那必须要写高质量的sql才行啊。

就跟随作者捋一遍，整理整理。

看完了发现都是基础知识啊~然而不结合实际应用干看好无聊啊，本喵大人快看吐了。

如果有实际应用，有不会的，对照目录查找用法会比较高效一些吧~

第一章 检索记录(都是基础，简略看下)1.1 检索所有行列 select * from a

1.2 检索部分行 select * from a where…

1.3 查找满足多个条件的行 select * from a where …or… and…

1.4从表中检索部分列 select c1,c2,c3 from a

1.5 为列取有意义的名称 select c1 as name,c2 as sex from a 这样可以使其他人更容易理解查询结果

1.6 在where子句中引用取别名的列 select * from (select sal as salary,comm as commision from emp) x where salary<5000

1.7 连接列值 将多列值作为一列返回。mysql， select concat(ename,' WORKS AS A', job) as msg from where deptno=10

1.8 在select 语句中使用条件逻辑 使用case表达式 select ename,sal, case when sal<2000 then 'UNDERPAID'

when sal>4000 then 'OVERPAID'

else 'OK'

end as status from emp

case when..then …when … then … else…end

1.9限制返回的行数 limit

1.10 从表中随机返回n条记录 select ename,job from emp order by rand() limit 5

1.11 查找空值 select * from emp where comm is null , 因为null

1.12 将空值转换为实际值 select coalesce(comm,0) from emp

1.13 按模式搜索 select ename,job from emp where deptno in (10,20) and (ename like '%I%')

第二章 查询结果排序(也都很基础，就是排序子句中使用case表达式，以前还没用过)2.1 以指定的次序返回查询结果 select ename,job,sal from emp where deptno = 10 order by sal asc

2.2 按多个字段排序 select ename,job,sal from emp where deptno = 10 order by deptno, sal desc

2.3 按子串排序 select ename,job,sal from emp where deptno = 10 order by substr(job,length(job)-2)

2.4 对字母数字混合的数据排序 translate mysql和sql server 不支持，暂时先不看吧

2.5 处理排序空值 使用case句组合嵌套查询

2.6 根据数据项的键排序 在order by 子句中使用case表达式： select ename,sal,job,comm from emp order by case when job='SALESMAN' then comm else sal end

第三章 操作多个表(有点常用)3.1 记录集的叠加

select … union all … union all 如果使用union而不是union all，很可能会为了去除重复项而进行排序操作，如非必要不要用，distinct同理。

3.2 组合相关的行

等值联接，内联接的一种 select e.ename,d.loc from emp e,dept d where e.deptno = d.deptno and e.deptno=10

3.3 在两个表中查找共同行(内联接 查找公共集)

select e.empno,e.name,e.job,e.sal,e.deptno from emp e,V where e.ename= v.ename and e.job= v.job and e.sal= v.sal

select e.empno,e.name,e.job,e.sal,e.deptno from emp e join V on ( e.ename= v.ename and e.job= v.job and e.sal= v.sal)

3.4 从一个表中查找另一个表没有的值

select deptno from dept where deptno not in (select deptno from emp)

注意null对结果的影响，选择范围包含null时，使用exists

3.5 在一个表中查找与其他表不匹配的记录

使用外联接，返回公共集和其中一表的不匹配行，然后通过where筛选只保留不匹配的记录。

3.6 向查询中增加联接而不影响其他联接

1)先联接两表，再外联接一个表来获取所需数据

2)使用标量子查询

select e.ename,d.loc,(select eb.received from emp_bonus eb where eb.empno=e.empno) as received from emp e, dept d

where e.deptno= d.deptno order by 2

3.7 检测两个表中是否有相同的数据

1)差集函数

2)使用关联子查询和union all

原理：1.首先，查找出表emp中存在而视图V中没有的行。

2.然后合并(UNION ALL)在视图V中存在，而在表emp中没有的行

3.8 识别和消除笛卡尔积

注意筛选条件

笛卡尔积应用：转置结果集、产生顺序值和模拟循环

3.9 聚集与联接

DISTINCT 每个值只参与计算一次

3.10 聚集与外联接

3.11 从多个表中返回丢失的数据

使用基于公共值的完全外联接来返回两个表中丢失的数据

select d.deptno,d.dname,e.ename from dept d full outer join emp e on (d.deptno=e.deptno)

或者 合并两个不同的外联接的结果

3.12 在运算和比较时使用NULL值

可以使用coalesce函数将NULL值转换为一个可以用来作为标准值进行比较的真实值

第四章 插入、更新和删除(又是大量基础，可略过)4.1 插入新纪录 insert into dept (deptno,dname,loc) values (50,XXX,XX)

4.2 插入默认值 create table D (id integer default 0)

4.3 使用NULL值代替默认值

4.4 从一个表向另外的表中复制行 insert into d (a,b,c) select a,b,c form d1 where …

4.5 复制表定义 create table d2 as select * from d1 where 1=0 在create table 命令中，使用一个不返回任何行的子查询

4.6 一次向多个表中插入记录 (Oracle，DB2)支持，mysql等不支持

4.7 阻止对某几列插入 (即插入时指定某几列)

4.8 在表中编辑记录 (当大量更新数据前，可能需要先预览下结果，可以 使用select语句，该语句中包含想要放到set子句中的表达式)

4.9 当相应行存在时更新某表中的一些行

4.10 用其他表中的值更新 update emp e set (e.sal,e.comm)=(select ns.sal,ns.sal/2 from new_sal ns where ns.deptno = e.deptno)

4.11 合并记录 (Oracle merge)

4.12 从表中删除所有记录 delete from emp

4.13 删除指定记录 delete from emp where deptno = 10

4.14 删除单个记录

4.15 删除违反参照完整性的记录 delete from emp where deptno not in (select deptno from dept)

4.16 删除重复记录 delete from dupes where id not in (select min(id) from dupes group by name)

4.17 删除从其他表引用的记录

第5章 元数据查询5.1 列出模式中的表 select table_name from information_schema.tables where table_schema=SMEAGOL

5.2 列出所有的列。

select * from information_schema.columns

5.3 列出表的索引

show index from emp;

5.4 列出表约束

select a.table_name, b.constraint_name, b.column_name,a.constraint_type from information_schema.table_constraints a, information_schema.key_column_usage b where a.table_name =‘emp' and b.table_name = a.table_name;

约束是关系数据库的关键部分，要知道在表中有什么样的约束是理所当然的要求。

5.5 列出没有相应索引的外键 show index

5.6 使用sql来生成sql

也就是说使用select+字符串 拼接出一系列 sql语句，用于创建可移植脚本

第6章 使用字符串6.1 遍历字符串

6.2引号必须成对出现，''''表示一个'

两个引号之间没有任何值得时候，表示空串，并不是null，在MySQL中

6.3 计算字符在字符串中出现的次数 在返回结果的时候，使用replace函数

select replace(user_name,'2','two') as new from user_info where user_id = 3214 ;

6.4 从字符串中删除不需要的字符replace

6.5 将字符和数字数据分离

6.6 判别字符串是不是字母数字型的 直接使用正则表达式

不包含数字字母以外的 其他字符

where data regexp '[^0-9a-zA-Z] = 0'

replace 函数

replace(user_name,'2','two')

第一个参数是 字段名，第二个是被替换的字符串，第三个是替换的新内容，这个例子的意思是 将user_name中的2替换成two。

substr()函数

select substr(name,3,30) from emp where id = 1;

表示 取name字段 ，从三个字符开始取， 取30个字符。

6.7 提取姓名的大写首字母缩写

SELECT replace(

translate( REPLACE('Dylan Xu', ' ','.'),

'abcdefghijklmnoopqrstuvwxyz',

rpad('#',26,'#')), '#') || '.'

FROM dual;

6.8 按照字符串中部分内容排序

SELECT ename FROM emp ORDER BY substr(ename, -2, 2);

… 各种排序和应用

主要都是用几种字符串操作函数的组合

6.9 按字符串中数字排序

CREATE OR REPLACE VIEW v_sqlbook1 AS

SELECT e.ename ||' '||

CAST(e.empno AS CHAR(4))||' '||

d.dname AS DATA

FROM emp e, dept d

WHERE e.deptno = d.deptno;

SELECT data

FROM v_sqlbook1

ORDER BY to_number(REPLACE(

translate(DATA, REPLACE(

translate(DATA,

'0123456789', '##########'), '#'), rpad('#', 20, '#')), '#'))

;

SELECT to_number(trim(REPLACE( translate('abc 123 bnh', 'abcdefghijklmnopqrstuvwxyz', '##########'), '#'))) FROM dual;

6.10 根据表中一行创建分隔列表

SELECT deptno,

ltrim(sys_connect_by_path(ename, ','), ',') emps

FROM ( select deptno,

ename,

row_number() over (partition by deptno order by empno) rn,

count(*) over(partition by deptno) cnt

from emp

)

WHERE LEVEL = cnt

START WITH rn = 1

CONNECT BY PRIOR deptno = deptno AND PRIOR rn = rn -1;

6.11 将分隔数据转换为多值IN列表

6.15 分析IP地址

SELECT instr('a,bc,def,g', ',', 1,1) FROM dual;

SELECT ip,

substr(ip, 1, instr(ip, '.') - 1) a,

substr(ip, instr(ip, '.') +1, instr(ip, '.', 1, 2) - instr(ip, '.')-1 ) b,

substr(ip, instr(ip, '.', 1, 2) +1, instr(ip, '.', 1, 3) - instr(ip, '.', 1, 2)-1 ) c,

substr(ip, instr(ip, '.',1,3) + 1) d

FROM (SELECT '192.168.1.240' AS ip FROM dual);

第七章 使用数字7.1-7.5 avg sum min max count等函数

7.6 生成累计和

SELECT ename, sal,

SUM(sal) over (ORDER BY sal, empno) AS running_total

FROM emp

ORDER BY 2;

7.7 生成累乘积

--c1 sal大于0情况，使用对数和求得

SELECT empno, ename, sal,

exp(SUM(ln(sal)) over(ORDER BY sal, empno)) AS running_product

FROM emp

WHERE deptno = 10;

注：指数与对数运算法则：①ln(x)=log(e)X ②ln(x) + ln(y) = ln(x*y), e^ln(x) = x;

c2 sal存在负数与0情况，使用model求得

SELECT empno, ename, sal, tmp AS running_product

FROM (

SELECT empno, ename, -sal AS sal

FROM emp

WHERE deptno =20

)

MODEL

DIMENSION BY(row_number() over (ORDER BY sal DESC) rn )

MEASURES(sal, 0 tmp, empno, ename)

RULES (

tmp[ANY] = CASE WHEN sal[cv() - 1] IS NULL THEN sal[cv()]

ELSE tmp[cv()-1]*sal[cv()]

END

)

第8章 日期运算8.1 加减日，月，年

问题：对日期加减日，月，年进行加减各前5后5.使用ADD_MONTHS函数加减月数和年数。

interval 关键字指定时间单位或DATE_ADD

8.2 计算两个日期之间的天数

问题：求两个日期之间相差的天数。

select datediff(day,allen_hd,ward_hd) from ...

8.3 确定两个日期之间的工作日数目

问题：给定两个日期，求它们之间(包括这两个日期本身)有多少个”工作“日。

select sum(case

when date_format(date_add(jones_hd, interval t500.id-1 DAY),'%a') in ('SAT', 'SUN') then

0

else

1

end) as days

from (select max(case

when ename = 'BLAKE' then

hiredate

end) as blake_hd,

max(case

when ename = 'JONES' then

hiredate

end) as jones_hd

from emp

where ename in ('BLAKE', 'JONES')) x,

t500

where t500.id <= datediff(blake_hd , jones_hd)+ 1

就是返回所有相差天数，再剔除周末

8.4 确定两个日期之间的月份数或年数

问题：求两个日期之间相差的月数或年数。

解决方案：使用函数MONTHS_BETWEEN,将得到两个日期之间相差的月数(要得到相差的年数，只需除以12即可)：

select mnth,mnth/12 from(

select (year(max_hd)-year(min_hd)*12+(month(max_hd)-month(min_hd)) as mnth

from (

select min(hiredate) as min_hd,max(hiredate) as max_hd from emp) x

) y

8.5 确定两个日期之间的秒，分，小时数

问题：求两个日期之间相差的秒数，

datediff(day,allen_hd,wartd_hd) *24*60*60

8.6 计算一年中周内各日期的次数

问题：计算一年中周内各日期(星期日，星期一......星期六)的次数。

解决方案：要计算一年中周内各日期分别有多少个，必须：

1.生成一年内的所有日期。

2.设置日期格式，得到每个日期对应为星期几。

3.计数周内各日期分别有多少个。

8.7 确定当前记录和下一条记录之间相差的天数

第9章9.1 确定一年是否为闰年

问题：确定当前年是否为闰年。

只检查2月的最后一天，如果它为29，则当前年就是闰年。

使用函数LAST_DAY,可计算出2月份的最后一天：

9.2 确定一年内的天数

问题：计算当前年天数

解决方案：当前年的天数等于第二年的第一天与当前年第一天(以日为单位)之差。针对每个系统的解决方案，以下步骤相同：

1.找到当前年的第一天。

2.给该日期加1年(即可得到第二年的第一天)。

3.从第二步的结果中减去当前年。

select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual;

9.3 从日期中提取时间的各部分

问题：把当前日期分为6部分：日，月，年，秒，分时，而且以数字方式返回结果。

date_format %k hour, %i min, %s sec, %d day,%m month, %y year

9.4 确定某个月的第一天和最后一天

问题：确定当前月的第一天和最后一天

select date_add(current_date,interval -day(current_date)+1 day) firstday, last_day(current_date) lastday from t1

9.5 确定一年内属于周内某一天的所有日期

找出一年内属于周内某一天的所有日期。例如，列出当年中的所有星期五的日期。

使用基干表T500，返回当前年的每一天。然后使用函数DAYNAME保留星期五

9.6 确定某月内第一个和最后一个“周内某天”的日期

问题：例如，找出当前月的第一个星期一及最后一个星期一的日期

使用ADDDATE找到当前月第一天

9.7 创建日历

问题：为当前月创建一个日历。

9.8 列出一年中每个季度的开始日期和结束日期

问题：返回一年中每个季度的开始日期和结束日期。

使用DATE_ADD和ADDDATE函数，使用QUARTER函数确定所属的季度

9.9 确定某个给定季度的开始日期和结束日期

问题：对于YYYYQ格式(其中4位年，一位季度)的年和季度信息，返回该季度的开始日期和结束日期。

SUBSTR确定年 MOD函数确定要查询的季度

9.10 填充丢失的日期

问题：为给定范围内的每个日期(每个月，周或年)生成一行信息。

9.11 按照给定的时间单位进行查找

问题：查找与给定月份，星期几或其他时间单位相匹配的日期。

MONTHNAME DAYNAME

9.12 使用日期的特殊部分比较记录

问题：查找聘用日期月份和周内日期都相同的员工，比如1988年3月10日星期一 与 2001年3月2日星期一 同月同星期

9.13 识别重叠的日期范围

问题：查找员工在老工程结束之前就开始新工程的所有实例。

第10章10.1 定位连续值的范围

问题：确定哪些行表示连续工程的范围。

10.2 查找同一组或分区中行之间的差

问题：返回每个员工的DEPTNO,ENAME和SAL以及与同一部门(即DEPTNO值相同)的员工间SAL之差，该差值在当前员工及同部门内紧随其后聘用的员工间计算而来(要从“每个部门”的角度看看资历和工资之间是否具有相关性)。对于每个部门中最新聘用的员工，这个差值为N/A。

10.3 定位连续值范围的开始点和结束点

10.4 补充范围内丢失的值

问题：返回1980年起始的十年间每年聘用的员工数，但有些年份并没有聘用员工。

内联视图X先找到最早的HIREDATE年份，内联视图Y返回每个HIREDATE的年份以及这一年聘用的员工数，再外联接X即得结果。

10.5.生成连续的数字

with x as (select level id from dual connect by level <=10) select * from X;

select array id

from dual model dimension by(0 idx) measures(1 array) rules iterate(10)(array iteration_number = iteration_number + 1);

第11章11.1 给结果集分页

limit限制返回行数 offset跳过n行

11.2 跳过表中n行

问题：编写一个查询，从表EMP中每隔一行返回一名员工;需要查找第1个员工，第3个员工，以此类推。

使用求模操作跳过行

11.3 在外联接中用OR逻辑

问题：返回部门10和20中所有员工的姓名和部门信息，并返回部门30和40(但不包含员工信息)的部门信息。

11.4 确定哪些行是彼此互换的

比如

a1 a2

10 20

20 10

15 90

89 90

78 98

34 43

43 34

要求查询结果：

10 20

34 43

解决方式：使用自联接

select distinct c1.* from cdla c1,cdla c2 where c1.a1 = c2.a2 and c1.a2 =c2.a1 and c1.a1<=c1.a2;

11.5 选择前N个记录

问题：以某种排序方式，限定结果集中只包含一定数目的记录。例如，返回最高5档工资的员工姓名和工资。

解决方案：这种解决方案的关键是两个步骤：首先按预定方式给行排序，然后限定结果集，只包含感兴趣的行。

11.6 找到包含最大值和最小值的记录

问题：查找表中的“两极”值。例如，找出表EMP中具有最高工资和最低工资的员工。

解决方案：使用窗口函数MIN OVER和MAX OVER,在TMP表中分别找到最低工资和最高工资：

11.7 存取“未来”行

问题：找到满足这样条件的员工：即他的收入比紧随其后聘用的员工要少，来的比他晚，赚的比他多。

select ename, sal, hiredate

from (select ename,

sal,

hiredate,

lead(sal) over(order by hiredate) next_sal

from emp)

where sal < next_sal

11.8 轮换行值

问题：返回每个员工的姓名和工资以及低于自己的最高工资和高于自己的最低工资。如果没有更高或更低的工资，则可能要求结果环线(即第一个SAL显示最后一个SAL,反之亦然)。

11.9 给结果分等级

问题：给表EMP中的工资分等级，并允许捆绑。

解决方案：窗口函数会使等级查询相当简单。要进行分等级，下面三个窗口函数特别有用：DENSE_RANK OVER,ROW_NUMBER OVER和RANK OVER,

select dense_rank() over(order by sal) rnk,sal from emp;

11.10 抑制重复

问题：在表EMP中查找不同的职位，但不想看到有重复。

select distinct job from emp

select job from emp group by job

11.11 返回一个结果集，它包含每个部门中所有员工 姓名，所在部门，工资，聘用日期以及部门中最新聘用员工的工资。

11.12 生成简单的预测

问题：以当前数据为基础，返回另外的行和列，用来表示未来活动。

mysql不支持

第12章12.1 将结果集转置为一行

问题：希望将几行组中的数据转换成几行中的列。每个原来的行组转换成一行。

1102450

2105000

3101300

4202975

5203000

6201100

720800

8203000

9301250

10301500

11301600

1230950

13302850

14301250

--------------------------------------

18750108759400

sum 聚合

12.2 把结果集转置为多行

问题：把行转换为列，根据原表给定列的每个值创建一个列。

1CLERKSMITH

2SALESMANALLEN

3SALESMANWARD

4MANAGERJONES

5SALESMANMARTIN

6MANAGERBLAKE

7MANAGERCLARK

8ANALYSTSCOTT

9PRESIDENTKING

10SALESMANTURNER

11CLERKADAMS

12CLERKJAMES

13ANALYSTFORD

14CLERKMILLER

--------------------------------------------------------------------------------

1ADAMSFORDBLAKEKINGALLEN

2JAMESSCOTTCLARKMARTIN

3SMITHWARD

4MILLERJONESTURNER

12.3 反向转置结果集

问题：把列转换为行。

12.4 将结果集反向转置为一列

问题：把查询中返回的所有列转换为1列。

12.5 抑制结果集中的重复值

问题：相同值只出现一次如下结果：

---------------------------------------------------------

110CLARK

2KING

3MILLER

420JONES

5FORD

6ADAMS

7SMITH

8SCOTT

930WARD

10TURNER

11ALLEN

12JAMES

13BLAKE

14MARTIN

-------------------------------------------------

12.6 转置结果集以利于跨行计算

问题：对来自多个行的数据进行计算。

12.7 创建固定大小的数据桶

问题：把数据编组成大小均匀的桶，每桶都包含预定的元素数。桶的总数可能不能欢确定，但要确保每桶都包含5个元素。

12.8 创建预定数目的桶。

问题：把数据编成固定的数目的桶

12.9 创建横向直方图

问题：使用SQL生成横向延伸的直方图。例如，采用横向直方图显示每个部门的职员数，一个星号“*”表示一个员工。

12.10 创建纵向直方图

问题：生成一个从底部向上延伸的直方图。例如,采用纵向直方图显示每个部门的职员数，一个星号“*”表示一个员工。

12.11 返回未包含在GROUP BY 中的列

问题：正在执行一个GROUP BY查询，并希望返回那些属于选择列表而不包含于GROUP BY子句中的列。

12.12 计算简单的小计

问题：定义一个“简单小计”结果集，它包含一列的聚集值以及全表的总计值。

解决方案：GROUP BY子句的ROLLUP扩展可以完美地解决这上问题。如果RDBMS没有提供ROLLUP,则可以使用标量子查询或UNION查询来解决这个问题，但可能会比这难一些。

12.13 计算所有表达式组合的小计

问题：对JOB/DEPTNO的每种组合，求按DEPTNO和JOB的总工资。并求表EMP中所有工资的总计。

解决方案：最近几年，GROUP BY中增加的扩展使这个问题相当容易解决。

12.14判别非小计的行

问题：前面曾经使用GROUP BY子句的CUBE扩展创建报表，而且需要采用一种方式区分哪些行是由常规GROUP BY子句生成的，哪些行是使用CUBE或ROLLUP的结果生成的。

12.15 使用CASE表达式给行做标记

问题：把一列中的值(如EMP表的JOB列)映射成一系列“布尔”标记。

解决方案：对每个雇员的JOB使用CASE表达式，并返回1或0表示他的JOB.

12.16 创建稀疏矩阵

问题：创建一个稀疏矩阵,由表EMP的DEPTNO列和JOB列变换来的：

解决方案：使用CASE表达式创建稀疏的行列列的变换：

select case deptno when 10 then ename end as d10,

case deptno when 20 then ename end as d20,

case deptno when 30 then ename end as d30,

case job when 'CLERK' then ename end as clerks,

case job when 'MANAGER' then ename end as mgrs,

case job when 'PRESIDENT' then ename end as prez,

case job when 'ANALYST' then ename end as anals,

case job when 'SALESMAN' then ename end as sales from emp

12.17 按时间单位给行分组

按某个时间间隔计算数据的和。例如，有一个事务处理日志，想求得每5秒钟内的总事条数。

第13章 分层查询

第14章 若干另类目标