数据库之sql语句汇总20180616
/*******************************************************************************************/
一、SQL简介
SQL:结构化查询语言
SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。
SQL 语句用于取回和更新数据库中的数据。SQL 可与数据库程序协同工作，比如 MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 以及其他数据库系统。
注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的私有扩展！
SQL语法基础：
select... 查询内容
from... 在哪里查询
where... 使用什么样的条件
group by...(sql的入门) 按什么进行分组
having... 做分组时按什么条件过滤
order by... 按什么来排序
/*******************************************************************************************/
二、基本的操作
0.显示当前用户： SQL> show user;
1.查看当前用户下，有几个表的指令：select * from tab;//tab是数据字典，记录的是scott用户下有哪些表
2.查看表结构：
desc dept(dept部门表)
DEPTNO 部门编号(这些查看信息都可以用select * from dept来看)
DNAME 部门名称
LOC 地点
desc emp(emp员工表)
EMPNO 员工号
ENAME 员工姓名
JOB 工种
MGR 经理
HTREDATE入职日期
SAL 薪水
COMM 奖金
DEPTNO 部门编号（两个表都有的字段，这个是关系型数据库的特点，两个表可以有同一个字段）
3.查询命令可以使用select * from dept
4.其他
1).
select * from emp//结果出来很多东西，格式很乱，解决方法：
set linesize 140;//包括空格一行的大小为140,也就是设置一行的宽度
/ 扛表示执行上一条命令，也就是查询命令
set pagesize 120;//设置一页的宽度
/
也可以把命令写入到如下两个配置文件里，这样登录退出后也会生效，
C:\app\Administrator\product\11.2.0\client_1\sqlplus\admin\glogin.sql
C:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql
2).
select * from dept;
col deptno for(format) 9999;//设置deptno列宽度为4个9这么宽(这个是数值型的设置)
col dname for a20;//设置dname 列宽度为20(这个是字符型的设置)
3).
清屏命令：host cls
/*******************************************************************************************/
三、基本的查询
1.查询的语法
select *|{[distinct]column|expression[alias],..} from table;
基本查询：可以查询全部列，部分列,表达式，别名，去除重复
2.示例：
1).基本查询
--查询员工表信息
SQL> select * from emp;
2).as后面是别名,别名的意思是查询后的结果不显示原来的名字显示定义的别名。其中as可以省略。
--查询员工号，姓名，月薪，奖金，年薪 -- 需要注意别名,如果不使用双引号,不能有空格和不可见字符
SQL> select empno as "员工号",ename "姓 名",sal 薪水,comm 奖金,sal*14 年薪 from emp;
3).select * 与 select 列的区别
推荐写指定列,效率高.
4).null(空，也就是项目里面没有内容)与表达式运算，结果为null，为防止结果为空，则
使用滤空函数：nvl(a,b)，含义是如果a为null则结果为b，如果a不为null则结果为a
--查询员工号，姓名，月薪，奖金，年薪，年收入 -null捣蛋鬼
null与表达式运算,结果为null ,使用nvl函数虑空函数 nvl(a,b) 如果a为null,结果为b,如果a不为null,结果为a
SQL> select empno as "员工号",ename "姓 名",sal 薪水,comm 奖金,sal*14 年薪,sal*14+nvl(comm,0) from emp;
5).使用distinct关键字去除重复行,
--查看员工表不同的部门编号distinct关键字,作用域:整行
SQL> select distinct deptno from emp;//得到不同的部门编号
注意,distinct作用域是整行，而不是某一列，
比如select distinct deptno,job from emp;
这是得到的是deptno和job组合起来不同的结果，也就是整体(行)不同的结果，而不是单独deptno列或者job列不同的结果
6).sql错误修改方法：
出错时使用edit或者ed指令，这是会弹出一个记事本，修改记事本中的内容也就是上一条指令，然后保存退出，再次使用/来执行上一条指令即可，这样便于修改比较长的指令
--sql错误修改方法
ed(it) 指令
7).sql也可以来计算表达式以及显示当前日期等，
--输出计算表达式 3+20*5，显示当前日期
SQL> select 3+20*5, sysdate from emp;//这个就会有结果显示，但是显示的结果是很多行的，行数等于emp中的行数。
//如何解决这个多行的问题，就要使用到伪表(即空表)和伪列(是本来就存在的列，但不依赖于某一个表)，即dual(计算或者函数情况时使用)，
伪表和伪列dual (计算或者函数情况时使用) ,伪列是本来就存在的列,不依赖于某一个表
SQL> select 3+20*5, sysdate from dual;
8).SQL语句使用注意事项
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。
9).连接符
Oracle中定义了一个连接符 ’||’ 用来连接字符串。
显示“xxx 是一个 xxx”怎么显示呢？
SQL> select ename || ‘ is a ’ || ‘job’ from emp
但是如果显示一个“hello world”应该怎么显示呢？
(1).使用concat函数来实现：SQL> select concat(‘hello’, ‘ world’) from ????
这两个字符串不是任何一个表的内容，在Oracle中，定义一张“伪表”dual用来满足SQL99语法（ANSI）。
语法规定：select 后 必须接 from。
可以这样：SQL> select concat('aa', concat('bb', 'cc')) from dual;
(2).使用连接符 || 来实现: SQL> select 'hello' || ' world' || ' Oracle' as "你好" from dual
再如： SQL> select 3 + 2 from dual;
‘||’就是用来代替concat函数的。SQL> select ename || ‘ is a ’ job “员工描述” from emp
10).SQL和sqlplus
sqlplus是登录工具
sql命令是敲出来，
如何区别，注意操作本地的就是属于sqlplus的命令，也就是说在客户端执行的命令就是sqlplus的命令，如果是对oracle实例进行操作的那就是sql的命令。
也就是说,sql指令是需要和数据库进行交互的，sqlplus是本地更改
比如：
desc ql
edit sqlplus
select sql
那么sqlplus有哪些指令呢，可以在sqlplus下执行 ? topic即可。
区分的意义在于，如果换了数据库那么sqlplus的命令就不一样了，因为客户端是其他数据库的了，但是sql指令是一样的。
注意，
sql指令可以换行写，但是第一行不能缺少select
/*******************************************************************************************/
四、where过滤
where过滤条件
基本语法 select ... from ... where condl
其中这个条件就涉及到运算符
其中，比较运算符要注意<>就是不等的意思 between and是一对整体
1.比较运算符 = != <> < > <= >= between and
--查询10号部门的员工信息
SQL> select * from emp where deptno=10;
--查询员工名字为king的员工信息
SQL> select * from emp where ename='KING';//注意在sql中单引号代表字符串，双引号代表别名,注意字符串要全匹配也就是说区分大小写
--查找薪水不等于1250员工的信息
SQL> select * from emp where sal != 1250;
SQL> select * from emp where sal <> 1250;
--查询入职日期为1981年11月17日的员工信息---日期型的查询
注意日期型的查询，也就是如何匹配日期，注意日期格式敏感
首先查看数据库的参数配置，也就是查看日期格式的设置：select * from v$nls_parameters;
然后查询的时候比较的字符串格式要与设置的一致
或者设置日期格式：alter session set NLS_DATE_FORMAT='yyyy-mm-dd';,然后按照该格式输入字符串查询,注意登录的是会话所以设置的是所属于会话的。
所以如果有人修改了设置，查询会报错。所以一般不会轻易去改动
SQL> select * from emp where hiredate='1981-11-17';
select * from emp where hiredate='1981-11-17'
*
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
--查看日期设置 select * from v$nls_parameters; year-月份-日期 yyyy-mm-dd
见图1：
SQL> select * from emp where hiredate='17-11月-81';--日期格式敏感
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
SQL> select * from emp where hiredate ='1981-11-17';
带来一个问题,如果有人修改了设置了,我们的语句将报错!!!
SQL> alter session set NLS_DATE_FORMAT='dd-mon-rr';--恢复原状
2.前面都是一个条件，如果需要多个条件的就需要用到逻辑运算符：逻辑运算符 and or not
--查询10号部门或者20部门的员工信息
SQL> select * from emp where deptno =10 or deptno = 20;
--查询10号部门员工工资为1300的员工信息
SQL> select * from emp where deptno =10 and sal=1300;
--查找工资介于1000-2000之间的员工信息，
SQL> select * from emp where sal >=1000 and sal <= 2000;
等同于：SQL> select * from emp where sal between 1000 and 2000 ;
注意，between 1000 and 2000这个是闭区间，即[]
--查询81年2月(含2月)至82年2月(不含2月)入职的员工信息81-2-1 -->82-1-31
SQL>select * from emp where hiredate between '1981-2-1' and '1982-1-31';
--查询奖金为空的员工信息-null
注意，查询为空的记录时，也就是null时，要注意，
null重要结论，不能用= !=,也就是说 columnname=null 或者!=null 均永远为假
正确的写法:SQL>select *from emp where comm is null;
或者SQL>select *from emp where comm is not null;
--多个条件时怎么写更优？
注意，sql查询记录的时候，是读取一条条记录然后和where条件进行比对，所以查询语句越少效率越高。
当一条查询语句中有多个条件时，这时sql是从where后面最右边一个条件开始比对，直到where最近的一个条件。所以为了效率最高，如果最后的条件就能(退出比对)直接返回
结果，那么就是最优的写法，(and的时候,易假的放在右侧,or 与之相反.)
--多个逻辑运算符的执行顺序
注意出现多个逻辑运算符的执行顺序，也就是优先级，从前面我们可以得到从右边开始比对，所以逻辑运算是从右开始执行，
当一个结果计算好后再由右向左与下一个进行运算。如果想打破这种顺序，就需要用()将先执行的包括起来：
SQL>select * from emp where (deptno=10 or deptno=30) and sal=1250
3.in：在集合中
--查询部门号是10或者20的员工信息：
SQL>select * from emp where deptno in (10,20);
--查询部门号不是10或者20的员工信息：
SQL>select * from emp where deptno not in (10,20);
注意，如果想查询部门号不是10或者20也不为空的员工信息：
select * from emp where deptno not in (10,20,null);
这样是错误的，因为null不能用来进行判断的，否则为假，这个前面也说了这个重要结论了
所以结论是 ：not in 的集合不能有null
4.like:模糊查询
知识点： ‘%’匹配任意多个字符。‘_’匹配一个字符
--查询员工首字母是S的员工信息
SQL>select * from emp where ename like 'S%';
--查询四个字母的员工信息
SQL>select * from emp where ename like '____';//四个下划线即可
--查询带有下划线的，涉及到转义，要使用关键字escape
SQL>select * from emp where ename like '%\_%' escape'\';//把_转义为普通字符，注意escape用来定义转义字符
SQL>select * from emp where ename like '%/_%' escape '/';
select ... 指定列
from ... 指定表
where... 指定条件
作用是为了实例可以解析sql命令
null总结:
1.null在表达式结果为null ,要用虑空函数nvl(a,b) 如果a为null,结果为b,否则结果为a
2.not in 的集合中不能有null
3.null 不能用= ,!= (<>)
/*******************************************************************************************/
五、排序
完整sql语句
select...
from...
where...
group by...
having...
order by...
排序可以 列名，表达式，别名，序号，语法是order by col|alias|number 默认是asc模式，升序，desc降序
其中，这个序号的意思是属于查询结果集中第几个的意思，如果查询结果集是*那么序号就是第几列了。
例如：
1.
--员工信息按入职日期排序
SQL>select * from emp order by hiredate;
2.排序（序号）
--员工薪水按从大到小排序（序号）
SQL>select * from emp order by sal desc;
SQL>select * from emp order by 6 desc;//第6列降序排序，6等价于sal
SQL>select empno,ename,sal from emp order by 3 desc;//等价于前面的，还是按照第6列(sal)降序排序
3.多种条件排序
--员工信息按部门、薪水排列
//两种或多种条件排序，条件越靠前面的越优先进行排序，即当前面排序条件相同时再用后面的条件排序，即
排序的原则:先按第一个字段,然后再按第二个字段...
SQL>select * from emp order by deptno,sal;//先按deptno排序，deptno中一样的再按sal排序。
4.作用范围
--asc 和desc 作用范围是它之前的一个字段
SQL>select * from emp order by deptno,sal desc;//desc只作用于sal,即sal是按递减排序的。
5.null 代表无穷大
--员工信息按奖金逆序 (nulls last --null)
null 代表无穷大,排序默认排在最后,
如果在降序中则在最前面，要想把null的放在最后则要在语句后面加上nulls last，强制放到最后
或者使用滤空函数把空的变为最小再排序：
SQL>select * from emp order by nvl(comm,-1) desc;//可能数据库的数据里有0的，所以填-1来保证最小(一旦comm为null就等于-1
)。
/*******************************************************************************************/
六、单行函数
单行函数:只对一行进行变换，产生一个结果。函数可以没有参数，但必须要有返回值。如：concat、nvl
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
1.字符函数
--lower小写，upper大写，initcap首字母大写
SQL>select lower('Hello wOrld') 一,upper('Hello wOrld') 二,initcap('Hello wOrld') 三 from dual;
concat(连接符||)，substr，length，lengthb，instr
--concat函数,字符串连接,只能传入两个参数，多个字符连接可以使用concat中嵌套concat实现，或者使用这个符号： ||，这个对于个数没有限制。
SQL>select concat('hello',' world') from dual;
SQL>select 'hello'||' world'|| ' 1111'||'2222' from dual;
--length求字符长度，注意汉字当一个字符， lengthb求字节数，一个汉字占两个字节。
SQL> select length('hello中国'),lengthb('hello中国') from dual;
LENGTH('HELLO中国') LENGTHB('HELLO中国') ---需要注意lengthb求字节数,gbk编码汉字2个字节
---------------------------------------
7 9
--substr
SQL>select substr('helloworld',1,3) 一,substr('helloworld',1) 二 ,substr('helloworld',-3) 三 from dual;
--instr函数返回的是子字符串所在的位置，位置从1开始，若不存在这样的子字符则返回0
SQL>select instr('helloworld','owo') 一,instr('helloworld','owow') 二 from dual;--查找instr(a,b) 判断b是否在a中,如果存在,返回b在a中的位置,如果不存在返回0
--l(r)pad(str,length,C)
返回length长度的字符串,如果str不够,则在左(右)补充C字符
lpad有三个参数，第一个参数为字符串，第二个参数为长度，第三个为字符，意思是返回满足第二参数表示的长度的
字符串(是第一个参数中的)，不够则用第三个参数在左边补全。
rpad有三个参数，第一个参数为字符串，第二个参数为长度，第三个为字符，意思是返回满足第二参数表示的长度的
字符串(是第一个参数中的)，不够则用第三个参数在右边补全。
--trim函数，
trim函数是去空格的,去掉字符串左边和右边的空格,字符串中间的空格是不会去的.如果不是去掉指定字符串的首尾的空格，
而是去掉字符串首尾的其他字符，则原先的写法由trim(' 字符串 ')改为trim('字符' from '字符串')
SQL>select 'aaa'||trim(' hello world ')||'bbb' from dual;
SQL>select trim('H' from 'HHHHHelloHHHworldHHHHH') from dual;--trim(C from str) 去掉str首尾为C的字符
--replace函数，
SQL>select replace('helloworld','llo','kk') from dual;
SQL>select replace('helloworld','llo','') from dual;
//表示的是将helloword中的llo替换为kk，如果kk不写得话就相当于去掉了其中的llo
2.数值函数
数值函数 正、负表示小数点之后，或小数点以前的位数
--round(a,b)函数：四舍五入，
SQL> select round(45.926, 2) 一, round(45.926, 1) 二, round(45.926, 0) 三, round(45.926, -1) 四, round(45.926, -2) 五 from dual;
--trunc(a,b)函数：直接舍掉，
其中b为正数表示取小数点后b位，为负数表示取小数点前b位。
SQL> select trunc(45.926, 2) 一, trunc(45.926, 1) 二, trunc(45.926, 0) 三, trunc(45.926, -1) 四, trunc(45.926, -2) 五 from dual;
--mod(a,b),取余，a除以b的余数
SQL> select mod(1600,600),mod(600,1600) from dual;
MOD(1600,600) MOD(600,1600)
--------------------------
400 600
--ceil(a/b) ，a除以b商向上取整。floor(a/b) ，a除以b商向下取整
SQL> select ceil(121/60),floor(121/60) from dual;
3.转换函数
(1).显示转换，就是使用函数转换，
函数包括 to_char to_number to_data，注意这字符，数字，日期这三种类型之间的转换，
字符是可以和其他互相转换的，但是数字和日期之间无法直接转换，也就是说字符串起中间作用，具体见图2：
(2).隐式转换就是用等号就转换了类型
具体见图3：
隐式转换前提:能转换。
当能够显示转换的时候尽量显示转换.
(3).转换函数的使用格式：
字符函数（待转换内容，转换的格式）
其中转换的格式都是可逆的。
--to_char 与to_number互相转换
注意to_char函数中使用到的格式，
例如：
--把薪水转换为本地货币字符型,见图4：
SQL> select sal,to_char(sal,'L9,999') from emp;//表示将原来数值型的薪水都转换为带有货币符号和四位数值
以及千位符这种格式的字符型
SQL> select to_number('￥1,250','L9,999') from dual;//转换回去，转换的格式还是一样的
TO_NUMBER('￥1,250','L9,999')
-----------------------------
1250
--to_char和to_date互相转换
注意to_date函数中使用到的格式，具体见图5：
例如查找日期型数据：
SQL> select * from emp where hiredate=to_date('1981-11-17','yyyy-mm-dd');//将字符类型按照日期类型转换后用作条件查找
to_char 与 to_date 显示 "yyyy-mm-dd hh24:mi:ss 今天是 星期几"
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;
//其中，格式化字符串中的非格式化字符用双引号，系统时间所在的伪列为sysdate,
将上述输出字符串反转回日期
select to_date('2017-02-13 16:06:04 今天是 星期一','yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;
4.日期函数
(1).显示昨天，今天，明天
SQL> select sysdate-1 昨天，sysdate-1 今天，sysdate-1 明天 from dual;//oracl日期格式可以加数字。注意日期之间可以
相减表示天数，但是不能相加。//其中昨天，今天，明天是列的别名，也就是显示出来的列名用逗号后面的别名不用前面的
--计算员工工龄 可以按日，周，月，年 日期差减方法
SQL> select sysdate-hiredate 日,(sysdate-hiredate)/7 周,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp;
(2).日期函数 mounts_between(a,b) add_months
参数类型是日期类型，表示a和b之间间隔几个月。sql中的乘除都是可以带小数的，这个函数如果结果不是整数返回的也是小数。
用这种函数算比自己用天数来除以12更准确。
SQL> select months_between(sysdate,to_date('2016-12-13','yyyy-mm-dd')) from dual;
SQL> select months_between(sysdate,hiredate) 月,months_between(sysdate,hiredate)/12 年 from emp;
(3).日期函数 last_day
求给定月份的最后一天
SQL> select last_day(sysdate) from dual;
(4).日期函数 next_day
求给定日期的下一个星期几
SQL> select next_day(sysdate,'星期六') from dual;//求当前日期的下一个星期六
/*******************************************************************************************/
七、条件表达式
sql中没有if else,所以判断条件一个用的是case...end,还有一个是decode
1.case...end -------sql99标准
语法为：
CASE expr WHEN comparison_expr1THEN return_expr1
[WHENcomparison_expr2 THENreturn_expr2
WHENcomparison_exprn THENreturn_exprn
ELSE else_expr]
END
case 变量 when 变量值 then 该变量值下要做的动作
(when 变量值 then 该变量值下要做的动作)
(else 其他条件要做的动作)
end
示例：
SQL> select empno,ename,job,sal 涨前薪水,case job when 'PRESIDENT' then sal+0
when 'MANAGER' then sal+200
else sal+1000 end 涨后薪水
from emp;
2.decode函数 ------oracle特有
decode：是一个函数，其语法为：
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
语法中文说明：
decode(变量，变量值，该变量值下要做的动作，变量值，该变量值下要做的动作，其他条件要做的动作 );
即参数位置含义固定
示例：
SQL> select empno,ename,job,sal 涨前薪水,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 涨后薪水
from emp
/*******************************************************************************************/
八、分组多行(函数)与分组数据
1.分组函数
分组函数是针对前面单行函数说的，前面学的函数都是单行函数。多行函数也叫组函数
即分组函数是对多行进行操作的，作用的范围是多行的。
分组函数包括：max,min,avg，sum,count(求数量),
1).sum(求和)
--求员工的工资总额
SQL> select sum(sal) from emp;
2).count(求数量)
--员工人数
SQL> select count(*) from emp;//求员工人数，即行数
--求emp表中的工种数
SQL> select count(distinct job) from emp;//求工种数目，即去除了重复
3).avg(求平均)
--求员工的平均工资（两种方式）
SQL> select avg(sal) 一,sum(sal)/count(*) 二 from emp;
--求员工的平均奖金（三种方式）
SQL> select avg(comm) 一,sum(comm)/count(*) 二,sum(comm)/count(comm) 三 from emp;
一 二 三
------------------------------
550 157.142857 550
结果：只有第一个和第三个一样。
原因是，count(*)计算的是行数即奖金不管是否为空都计算，count(comm)计算的是奖金不为空的数目
即组函数都有自动的滤空功能。
2.分组数据
分组数据的含义就是把数据进行分组，然后按组进行处理，使用GROUP BY 子句数据分组：
按照group by 后给定的表达式，将from后面的table进行分组。针对每一组，使用组函数(分组数据的含义就是把分出来的各个组的数据一起显示出来)。
group by中出现的也必须在在select中出现，这样才能选中显示出来(在select中出现的非组函数的列则必须有分组即group by...，
同时在select中出现的非组函数的列,必须在group by中出现.)
示例：
统计各个部门的平均工资，先统计10号的，再统计20的，最后统计30的
SQL> select deptno,avg(sal) from emp group by deptno;//按部门号计算每个部门的平均工资，即按部门号分组，一号一组，再计算每组的平均工资，(再按部门号列出来)
SQL> select a,avg(X) from tablename group by a
SQL> select a,b,avg(X) from tablename group by a,b
SQL> select a,b,c,avg(X) from tablename group by a,b,c
...
结论：(首先出现a,avg(x)则必须有分组即group by...，同时在select中出现的非组函数的列，必须在group by中出现)。
--查询各部门平均工资
SQL> select deptno,avg(sal) from emp group by deptno
--查询各部门各工种平均工资
SQL> select deptno,job,avg(sal) from emp group by deptno,job;
3.having
使用HAVING 子句过滤分组结果集。
使用 HAVING 过滤分组:
1). 行已经被分组。
2). 使用了组函数。
3). 满足HAVING 子句中条件的分组将被显示。
where后不能使用分组函数. having 的作用对分组进行过滤
--查询平均薪水大于2000的部门
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--求10号部门员工的平均薪水
select deptno,avg(sal) from emp group by deptno having deptno=10;
select deptno,avg(sal) from emp where deptno=10 group by deptno;
having与where哪个好？
如果都能用的情况下，优先使用where，因为先过滤再分组(sql语句是顺序执行的)效率更高。
4.常见的非法使用组函数的情况
--缺失group by字句,语法不符合规范.
//
select查询可以全部列,部分列,表达式,别名
group by分组的要求：在select中出现的非组函数的列，必须在group by后出现，--数据按什么分组，数据如何显示？。
分组函数也叫组函数，或者 聚合函数
like效率比较低，能少用就少用。
/*******************************************************************************************/
九、多表查询(基于oracle)
1.理论基础:笛卡尔全集
笛卡尔全集:两个表中的每条记录两两组合形成新的表，
所以新的表的列数等于两个表列数相加，新的表的行数等于两个表行数相乘:
全集的行数= 表1 的行数* 表2的行数
全集的列数= 表1的列数+ 表2的列数
组合后还要看新的表中的记录是否有意义，即两个表中相同的列中的元素相等的记录才有意义，即连接条件(相同的列)相等的记录才有意义
没有意义的是垃圾数据,需要使用连接条件过滤，如下图6，图7 中画下划线的部分就是垃圾数据：
2张表连接的条件至少是1个
3张表连接的条件至少是2个
===>N张表的连接条件至少是N-1
2.连接条件写法:1.表名.列名 2.给表起别名 别名.列名
推荐用第二种，因为当表名很长的时候 别名更方便。
例子：
select * from emp,dept;//emp,dept就是卡尔全集:
select * from emp e,dept d where e.deptno=d.deptno;//e d是别名，连接条件是deptno相等的
3.根据连接条件的不同可以划分为：等值连接，不等值连接，外连接，自连接
Oracle 连接:
Equijoin：等值连接
Non-equijoin：不等值连接
Outer join：外连接
Self join：自连接
SQL: 1999
Cross joins
Natural joins
Using clause
Full or two sided outer joins
1).等值连接(使用等号，在mysql中称为内连接)
--查询员工信息：员工号 姓名 月薪(emp)和部门名称dept
SQL> select empno,ename,sal,dname from emp e, dept d where e.deptno = d.deptno ;
注意，都有的列不能放在select后面，这样会有冲突，必须要指定时属于哪张表的列，放在连接条件内其实就不用指定了
2).不等值连接(不使用等号)
--查询员工信息：员工号 姓名 月薪 和 薪水级别(salgrade表)
SQL> select empno,ename,sal,grade
from emp e,salgrade s
where e.sal>=s.losal and e.sal<=s.hisal;
--可以用between and
SQL> select empno,ename,sal,grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
注意，当前薪水符合范围的，这样的记录中的薪水级别才是对的。
也就是说，记录要符合连接条件，也就是经过连接条件过滤后的记录是要有意义的。
3).外连接
当连接条件中的内容两个表不一致时,或者说连接重组后的表中,有的内容是只有一张表中有的,其他的表没有，如图8中的40号部门这一行：
这时如果需要统计这条记录时,则要特别处理.
例子:
--按部门统计员工人数，显示如下信息：部门号 部门名称 人数,注意有的部门没有人
SQL> select d.deptno,d.dname,count(empno)//count(empno)统计有人的部门，所以不能用*。由于不知道用哪一列，以及不知道列的别名是什么，所以这里最后写
from dept d,emp e
where e.deptno(+)=d.deptno//需要包含那条数据不完整的记录，也就是想保留等号右边的，(+)放在等号左边，称为右外连接
group by d.deptno,d.dname;
--左外连接:想保留等号左边的,(+)放在右边
SQL> select d.deptno,d.dname,count(empno)
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname
order by 1
--右外连接:想保留等号右边的,(+)放在左边
SQL> select d.deptno,d.dname,count(empno)
from dept d,emp e
where e.deptno(+)=d.deptno
group by d.deptno,d.dname
order by 1
4).自连接
一个表中的两条记录需要组合起来或者说需要相连接起来,这时就需要自连接,也就是把一张表当作两张表.
所以自连接时特殊的外连接。
例子:
--查询员工信息：xxx的老板是yyy
分析：可以把员工表emp当成两个表，一个是员工表，一个是老板表。
SQL> select e.ename||'''s boss is' || nvl(b.ename,'himself')//字符串内部还需要显示单引号，则直接加单引号即可
from emp e,emp b
where e.magr=b.empno(+)
//老板没有上级，所以这个条件不成立，但是为了把这条不成立的记录保留下来，也就是需要保留这条不完整的记录，这条记录
只有左边的，即只有左边的自己没有老板，所以保留左边的。
--下面的查询结果少了大老板,因为连接条件不成立
select e.ename||'''s boss is'||b.ename
from emp e,emp b
where e.mgr = b.empno
自连接的使用条件:
(1).数据都在一个表中
(2).数据不在同一行
自连接的弊端:
自连接对应的笛卡尔集合是平方的关系,所以行数很多(数量级平方的增长).所以最好在小表中使用
/*******************************************************************************************/
十、子查询
什么情况下使用子查询?一个查询不能完成的时候。
子查询的本质：sql嵌套sql
例子：
查询比scott工资更高的员工信息
分析：首先找到scott的工资，然后条件大于这个工资的。
SQL> select * from emp where sal>(select sal from emp where ename='scott');
注意事项：
1. 合理的书写风格 (如上例，当写一个较复杂的子查询的时候，要合理的添加换行、缩进)
2. 小括号( )
3. 主查询和子查询可以是不同表，只要子查询返回的结果主查询可以使用即可
--查询部门名称是“SALES”的员工信息
分析: 1. 先找到 SALES 部门名称对应的编号 2. 用这个编号做条件过滤员工
select deptno from dept where dname ='SALES';
select * from emp where deptno = 30;
===>
SQL> select * from emp where deptno = (select deptno from dept where dname ='SALES');
4. 可以在主查询的where,select,having,from后都可以放置子查询，即"两个by"后面不行。
select ..
from …
where …
group by … err
having …
order by … err
1).select后
--查询10号部门员工号，员工姓名，部门编号，部门名称
(1).等值连接写法
SQL> select e.empno,e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.deptno=10;
(2).子查询写法
SQL> select empno,ename,deptno,(select dname from dept where deptno=10)
from emp
where deptno=10;
2).from后
--查询员工的姓名，薪水和年薪
SQL> select * from (select empno,ename,sal*14 from emp);
3).where后
--查询与ward相同job并且薪水比他高的员工信息
先找到ward的工种和薪水,然后用作过滤条件
select sal,job from emp where ename ='WARD';
select * from emp where job='SALESMAN' and sal > 1250;
==>变成子查询
SQL> select * from emp where job=(select job from emp where ename ='WARD') and sal > (select sal from emp
where ename ='WARD');
4).having后
--查询高于30号部门最低薪水的部门及其最低薪水
SQL> select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 30)
5. 不可以在主查询的group by后面放置子查询 (SQL语句的语法规范)
6. 强调：在from后面放置的子查询(***) from后面放置是一个集合(表、查询结果)
7. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
单行的查询只能使用返回结果是单行的子查询(单行子查询就是该条子查询执行结束时，只返回一条记录(一行数据)),
即整个查询语句是单行的则括号里面返回的结果也要是单行的。多行子查询(子查询的结果是多行的)
这个规范的意思也就是说，结果要能组合起来。//其实主要意思是多行操作符操作的多行，所以多行操作符后面得是多行的集合，也就是多行子查询(子查询的结果是多行的集合)
单行操作符：
=、>、>=、<、<=、<>
多行操作符有：
IN 等于列表中的任意一个
ANY 和子查询返回的任意一个值比较
ALL 和子查询返回的所有值比较
举例：
1).多行操作符 IN，in后面为多行子查询(子查询的结果是多行的)
--查询部门名称为SALES和ACCOUNTING的员工信息
分析: 先找到部门编号,再通过编号查找
select * from dept where dname in ('SALES','ACCOUNTING');
select * from emp where deptno in (10,30);
==> 变成子查询
SQL> select * from emp where deptno in (select deptno from dept where dname in
('SALES','ACCOUNTING'));
2).多行操作符 ANY，any后面为多行子查询(子查询的结果是多行的)
--查询薪水比30号部门任意一个(any 某一个，即薪水最低的)员工高的员工信息
--多行子查询写法，any后面为多行子查询(子查询的结果是多行的)
SQL> select * from emp where sal > any (select sal from emp where deptno=30);
--单行子查询写法
SQL> select * from emp where sal > (select min(sal) from emp where deptno=30);
3).多行操作符 ALL ，all后面为多行子查询(子查询的结果是多行的)
--查询薪水比30号部门所有人工资高的员工信息
--多行子查询写法，all后面为多行子查询(子查询的结果是多行的)
SQL> select * from emp where sal > all (select sal from emp where deptno=30);
--单行子查询写法
SQL> select * from emp where sal > (select max(sal) from emp where deptno=30);
8. 子查询中的null值
判断一个值等于、不等于空，不能使用=和!=号，而应该使用is 和 not
例子：
--查询不是老板的员工信息
思路 先找到谁是老板，取反
--得到老板
select * from emp where empno in( select mgr from emp);
--取反
SQL> select * from emp where empno not in( select mgr from emp where mgr is not null);//not in里面不能出现有空值，一旦有，那么结果始终为假
9. 一般先执行子查询(内查询)，再执行主查询(外查询)；但是相关子查询除外
10. 一般不在子查询中使用order by, 但在Top-N分析问题中，必须使用order by
/*******************************************************************************************/
十一、配置与配置文件(oracle)
登陆命令sqlplus scott/11@orcl222中的orcl222来自于
配置文件tnsnames.ora，在配置文件中成为服务名
增加新的登陆服务，可以参考该文件中的其他登陆服务来填写服务名，ip，端口号，实例名
也可以使用工具来完成这个增加的操作 ：
工具名称：oracle net configuration assistant
注意，
登陆数据库的工具：pl/sql developer 中的drop命令表示的是删除的意思 。
/*******************************************************************************************/
十二、集合运算
1.集合运算符
集合运算的操作符：
1).A并B 并集 对应sql关键字:union(集合的交集只保留一份), union all(两个集合的内容全部保留)
SQL> select * from emp where deptno in(10,20) union select * from emp where deptno in(20,30)
SQL> select * from emp where deptno in(10,20) union all select * from emp where deptno in(20,30)
2).A交B 交集 对应sql关键字: intersect
SQL> select * from emp where deptno in(10,20) intersect select * from emp where deptno in(20,30)
3).A-B 差集 对应sql关键字: minus
SQL> select * from emp where deptno in(10,20) minus select * from emp where deptno in(20,30)
(from后面放的是一个集合(表))
具体见图9
2.集合运算需要注意的问题：
1).参与运算的各个集合必须列数相同，且类型一致。注意如果不一致则要处理，一般以列数最多的集合为准，
其他集合相对这个列数最多的结合用null来补充对应的列，注意列的类型要一致。
2).采用第一个集合的表头作为最终使用的表头。 别名也只能在第一个集合上起
3).可以使用括号先执行其中的语句。
3.示例，按照图10显示的样式输出结果：
分析：
数据分成三块;
1). 各个部门各个工种的工资总额 集合
2). 各个部门的工资总额
3). 总额
求集合
SQL> select deptno,job,sum(sal) from emp group by deptno,job union
SQL> select deptno,to_char(null),sum(sal) from emp group by deptno union
SQL> select to_number(null),to_char(null),sum(sal) from emp;
SQL> break on deptno skip 2;//设置为 去掉相同的部门号两个，这个是报表的处理技巧
SQL> break on null;//恢复回去 break on null
/*******************************************************************************************/
十三、数据处理
SQL 语言类型：
DML data manlpulation language 数据操纵语言： insert,select,update,delete，所有的dml语句中都可以使用子查询，同时子查询的规则同样适用
DDL data definition language 数据定义语言: create,truncate
DCL data control language 数据控制语言: grant(赋权)/revoke(回收权限)
1.insert插入数据
语法格式：
INSERT INTO table[(column[,colum...])] VALUES (value{,value...});
value 与 column (数目)要对应
1).可以插入全部列，即所有的列都有新数据的插入，此时不用指明列名
insert into dept values(50,'50name','50loc');
2).插入部分列
insert into dept(deptno,dname) values(51,'51name');//隐式插入Null
insert into dept(deptno,dname,loc) values(51,'51name',null);//显式插入Null
3).地址符号 & 的作用
& 号会提示用户来输入内容，用于插入
insert into dept values(&dpt,&dnm,&loc);
如图11：
这样的好处在于:
使用 / 来执行上一条语句(&号的语句)，则直接提示用户输入数据，这样就不用输入指令就可以插入数据了，
加快了插入数据的速度，
同时还可以用格式化输入，比如加入引号后，则用户输入就不需要再输入引号了：
insert into dept values(&dpt,'&dnm','&loc');
4).批量新增数据
create table emp10 as select * from emp where 1=2;
复制emp表的结构，由于1=2条件不会成立，所以表的数据不会被复制
批量插入10号部门的信息
insert into emp10 select * from emp where deptno=10;//将查询返回的结果插入到新表中。
5).insert也可以使用选择一个集合的方式来插入
insert into dept select 57,'57name','57loc' from dual;
其中 表示选中 列的内容分别为 的记录(特定的一条记录)
从伪表中去内容等同于直接输入指。
即等同于
insert into dept values(57,'57name','57loc');
2.update 更新数据
语法格式：
update tablename set column1=val1,column2=val2,... where cond;
对于更新操作来说,一般会有一个where条件,如果没有这个限制条件,更新的就是整张表.
例子：
update dept set loc ='51loc',dname='51dname' where deptno=51;
update dept set loc ='51loc',dname='51dname' where deptno=&dpt;//由用户输入来决定该得的是哪个编号的行
update dept set dname='52name',loc=null where deptno=52;//set中的等值可以有null
子查询的规则和注意事项在DML语句都适用!!
3.delete 删除数据
1).语法格式：
delete from tablename where cond...;
2).例子：
delete from dept where deptno=52;
3).注意：
做事情之前要做好备份：
批量新增就是备份了或者用客户端工具拷贝到excl(可以拷贝回来)。
4).delete 删除的是表内容，不会删除表结构，//删除表：drop命令
注意，删除一条记录后，后面的记录将会移动到这条记录的位置上。
如果说删除的记录还想插回的是原来的位置，则需要开启行移动。
5).delete 和truncate
delete和turncat两者都可以删除数据
(1).delete 是逐条删除数据,truncate 是先摧毁表,再重建. 表的数据会被清空 ..
删除整个表时，效果都一样，但是：
当前数量级的情况下，delete更快，当数据量大(比如一百万条记录时)的时候，truncate更快
所以工作的时候，还是用turncate。
(由于delete使用频繁，Oracle对delete优化后delete快于truncate,即delete更快的原因是oracle对delete进行了优化，但是mysql里面实际上是truncate更快)
(2).delete 是DML语句，truncate 是DDL语句。
DML语句可以闪回(flashback)，DDL语句不可以闪回(回滚)。
（闪回： 做错了一个操作并且commit了，对应的撤销行为。了解）
(3).由于delete是逐条操作数据，所以delete会产生碎片，truncate不会产生碎片。
（同样是由于Oracle对delete进行了优化，让delete不产生碎片）。
两个数据之间的数据被删除，删除的数据——碎片，整理碎片，数据连续，行移动 【图示】
(4).delete不会释放空间，truncate 会释放空间
用delete删除一张10M的表，空间不会释放。而truncate会。所以当确定表不再使用，应truncate
(5).delete可以回滚rollback， truncate不可以回滚rollback。
(6).【做实验sql.sql】：验证delete和truncate的时效性
关闭时间开关就不会显示: 已用时间:xxxx
关闭回显开关就不会显示: 已选择x行
@符号代表要执行哪个脚本
语句执行时间记录开关：set timing on/off
回显开关：set feedback on/off
示例：
SQL> set feedback off
SQL> set timing off
SQL> @c:\sql.sql
SQL> select count(*) from testdelete;
COUNT(*)
----------
5000
SQL> delete from testdelete;
SQL> rollback;
SQL> select count(*) from testdelete;
COUNT(*)
----------
5000
SQL> set timing on
SQL> set feedback on
SQL> delete from testdelete;
已删除5000行。
已用时间: 00: 00: 00.03
SQL> select count(*) from testdelete;
COUNT(*)
----------
0
SQL> set feedback off
SQL> set timing off
SQL> @c:\sql.sql
SQL> select count(*) from testdelete;
COUNT(*)
----------
5000
SQL> set timing n
SP2-0265: timing 必须设置为 ON 或 OFF
SQL> set timing on
SQL> set feedback on
SQL> truncate table testdelete;
表被截断。
已用时间: 00: 00: 00.31
SQL> select count(*) from testdelete;
COUNT(*)
----------
0
已选择 1 行。
结论：当前数量级的情况下，delete更快，当数据量大(比如一百万条记录时)的时候，truncate更快
所以工作的时候，还是用turncate。
/*******************************************************************************************/
十四、事务
1.事务：
若干个操作指令的集合(DML,即若干个DML语句组成就是事务)
事物特点：集合中的操作要么一起成功，要么一起失败
所以才有commit/rollback ,即所有都成功了则提交，否则回滚
2.事务开启的标志：DML语句开始
3.事务结束的标志：
提交结束：
1.显示提交 commit
2.隐式提交
执行DDL语句后会自动提交
正常退出:quit命令等 会自动提交
回滚结束：
1.显示回滚 rollback
2.隐式回滚 异常退出，掉电，宕机等情况会自动回滚
4.事务的4大特性(ACID):原子性，一致性，隔离性，持久性
原子性 (Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。
一致性 (Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。
隔离性 (Isolation)：事务的执行不受其他事务的干扰，当数据库被多个客户端并发访问时，隔离它们的操 作，防止出现：脏读、幻读、不可重复读。
持久性 (Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出
5.隔离级别
1).对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
(1).脏读: 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
(2).不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
(3).幻读: 对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.
2). 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
3). 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱
SQL99定义4中隔离级别：
(1).Read Uncommitted 读未提交数据。
(2).Read Commited 读已提交数据。 （Oracle默认）
(3).Repeatable Read 可重复读。 （MySQL默认）
(4).Serializable 序列化、串行化。 （查询也要等前一个事务结束）
具体说明见图12：
这4种MySQL都支持
Oracle支持的隔离级别： Read Commited（默认）和 Serializable，以及Oracle自定义的Read Only三种。
(2).Read Commited读已经提交，意思是如果没有提交，其他人是读不到的。
级别越高越好，最高级别也就是(4).Serializable，三种不希望出现的读 都不会出现，但是这种没人用，因为是串行的，效率太低
Read Only：由于大多数情况下，在事务操作的过程中，不希望别人也来操作，但是如果将别人的隔离级别设置为Serializable（串行），但是单线程会导致数据库的性能太差。是应该允许别人来进行read操作的。
6.事务也是关系型数据库的特点，也就是关系型数据库一般都支持事务，非关系型的则不支持
7.控制事务
除了commit rollback外还有savepoint(保存点)
保存点，类似还原点，即可以使用回滚的方式恢复到那一时刻(保存的时刻)。如果都不是自己
要回滚的保存点，那就可以用rollback回到最初，也就是直接回滚到上一次commit的时候。
如果没有回滚，觉得事务结束了，则直接提交即可，代表当前的事务结束了。
具体见图13：
使用示例：
savepoint aaa;
rollback to savepoint aaa;//回滚到保存点aaa
注意，
如果保存点设置名称重复，则会删除之前的那个保存点。
一但commit之后，savepoint将失效。
回退到某个保存点后，则不能在回退到该保存点之后的保存点了。也就是说，不会回退到比当前保存点更新的保存点。
/*******************************************************************************************/
十五、Top-N问题 相关子查询
1.【第一题】：找到员工表中工资最高的前三名，要求按排名递增的顺序输出
——涉及Top-N分析问题
Top-N问题: 取前3名
分页问题：取5-8名
1).Top-N问题分析
(1).一般不在子查询中使用order by,但是在top-n分析问题中，必须使用order by
(2).rownum 行号(伪列)，也就是查询这个就会显示行号出来。
(3).rownum 随着集合的创建就会存在，不会随着后面修改，也就是无论怎么排序显示出来，记录对应的行号是不会变的
(4).由于rownum是按照默认顺序生成，所以只能使用<,<=符号，不能使用>,>=符号。
2).Top-N问题答案
SQL> select rownum,empno,ename,sal from ( select * from emp order by sal desc ) e where rownum <=3;
3).分页问题分析
分页问题：取5-8名
对新产生的集合(rownum小于8的)中的rownum取别名R,这样新产生的列R就不是伪列了，就可以使用R>4来过滤了。
4).分页问题答案
SQL> select *
from (select rownum r, empno, ename, sal
from (select * from emp order by sal desc) e
where rownum <= 8)
where r > 4;
2.【第二题提示】：找到emp表中薪水大于本部门平均薪水的员工
1).两张表中没有相同的列，即没有冲突，所以直接选择查询即可：
先得到各个部门的平均薪水
select deptno,avg(sal) from emp group by deptno
把上述结果当成一个集合(表)
SQL>select empno,ename,sal,avgsal
from (select deptno,avg(sal) avgsal from emp group by deptno ) a ,emp e
where a.deptno = e.deptno
and e.sal > a.avgsal
2).相关子查询写法:
相关子查询先执行主查询，再执行子查询
原因在于，子查询的执行需要的东西由主查询产生，即子查询依赖于主查询。
示例：
---求10号部门 大于10号部门平均薪水的员工
SQL>select empno,ename,sal,(select avg(sal) from emp where deptno=10) avgsal
from emp
where deptno=10
and sal > (select avg(sal) from emp where deptno=10)
===> 将10号部门替换为 emp表每条记录的部门：这个就是相关子查询
SQL>select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno= e.deptno)//注意依赖：e.deptno
多表查询与子查询都可以解决的时候哪个更好？
一般情况下是多表查询，多表查询是以空间换时间，多表查询一下把数据都加载过来，子查询和数据库需多次的交互，
很明显多表查询和数据库的交互更少，一般情况下sql优化的原则都是尽量减少和数据库的交互
3. 【第三题提示】：统计每年入职的员工个数
分析，
——员工的入职年份是已知条件——1980、1981、1982、1987这4个。
要统计每年入职的人数，一定要知道每个员工的入职日期，可以通过查询hiredate列来得到
结合查询结果，以1981年为例，如何统计出81年入职的有多少个人呢？可以从写C程序的角度入手。
思路：定义一个计数器count=0; 有一个81年的员工，就+1，不是81的就+0;最后查看count的值就可以了。
求和，使用sum函数，内部逻辑：sum(if 是81年 then +1 else +0)
也就是取员工的hiredate列的“年”那一部分，与81比较，进行判断。
to_char(hiredate, ‘yyyy’) 得到年，与‘1981’进行比较
答案，
select to_char(hiredate,'yyyy') from emp ;
select decode(to_char(hiredate,'yyyy'),'1981',1,0) from emp ;
select sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981" from emp ;--搞定一列
SQL>select count(*) "Total",
sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) "1980",
sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) "1981",
sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) "1982",
sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) "1987"
from emp;
注意，
数字不能用作别名，得转换为字符型，即加上双引号。
集合的效率一般比较低，即多表组成集合的方式这种效率一般比较低。
具体见《课堂练习—子查询_多表查询.docx》
4.统计部门人数小于4的部门情况，显示部门编号，名称，部门人数
select d.deptno,d.dname,count(empno)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.name
having count(empno) < 4
order by 1;
//1表示按照第一列进行排序
复习的时候可以从多表查询进行复习
/*******************************************************************************************/
十六、表的管理
表的管理包括：创建表，删除表，修改表，重命名表，表的约束。
1.创建表
表是由行和列组成的。所以创建表的时候就要决定有哪些列。
1).列(里面的)的数据类型，（表示列里面的内容的数据类型）
创建表时， 列所使用的数据类型(oracl)：
VARCHAR2 不定长字符，定义了10，存了5个，则只占用5
CHAR 定长字符，定义了CHAR10，则占用的就是10个内存
NUMBER(整数，小数)
ROWID 行地址，是一个伪列(用的时候直接取就行了，就在那里了)。
具体见图14：
2).创建表的前提条件：表空间 + 权限
Oracle默认存储是都存为大写，意思是表名默认存储在数据库里的是大写的，即：
select * from tab;//看到的表名里都是大写的
也就是说，由于对于字符串大小写敏感，所以查找表名的时候要大写，即：
select * from tab where tname = 'EMP'
3).示例：
(1).创建一个表
create table t1(id number(4),tname varchar2(30),hiredate date default sysdate);//t1表名，id是第一列的列名,number
//是第一列列内容的类型为数值型，并指定长度为4，同时defalut的意思是，insert时不想放入数据时，会使用这个默认值
//即第三列插入时不用填：insert into t1(id,tname) values(1,'1234sdf');
SQL> create table t1(id number(4),tname varchar2(30),hiredate date default
sysdate);
表已创建。
SQL>
SQL> desc t1
名称 是否为空? 类型
----------------------------------------------------- -------- ----------------
--------------------
ID NUMBER(4)
TNAME VARCHAR2(30)
HIREDATE DATE
SQL>
SQL> insert into t1(id,tname) values(1,'yekai');
已创建 1 行。
SQL> select * from t1;
ID TNAME HIREDATE
---------- ------------------------------ --------------
1 yekai 16-2月 -17
(2).创建一个与 t1相同表结构的表
create table t2 as select * from t1 where 1=2;//1=2不成立，集合为空，所以内容不会拷贝过去，只是把表结构(格式)拷贝过去
2.删除表
//这个前面说过了
当表被删除：
数据和结构都被删除
所有正在运行的相关事物被提交
所有相关索引被删除
DROP TABLE 语句不能回滚，但是可以闪回
1).drop table t2;//这个是删除到回收站里面了。此时查询这个表也是查询不到的。
oracle有回收站机制 ，所以会看到：BIN$A8EsHYSkSiKQfnHrOw4E7g==$0
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$A8EsHYSkSiKQfnHrOw4E7g==$0 TABLE
2).查看回收站
SQL> show recyclebin;//sql plus工具里才看得到结果
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T2 BIN$A8EsHYSkSiKQfnHrOw4E7g==$0 TABLE 2017-02-16:09:45:54
3).闪回表，(flashback 闪回，需要开启行移动，但是表的闪回不需要)
flashback table t2 to before drop;//oracle 10g才支持的功能，从回收站里恢复(闪回)
注意：并不是所有的用户都有“回收站”，对于没有回收站的用户（管理员）来说，删除操作是不可逆的。
4).不经过回收站删除
SQL> drop table t3 purge;//不经过回收站删除，类似win：shift+delete;
SQL> purge recyclebin;//清空回收站,注意回收站是在服务器中的，如果清除回收站，则有可能把别人的也清除了。
回收站已清空。
3.修改表
增加一列
alter table t1 add email varchar2(30);//t1表增加一列,email是新列的名字，varchar2(30)列内容的类型
修改列属性(内容的类型)
alter table t1 modify email varchar2(40);//如果表非空，长度往小改，这时如果有数据长度大雨想要修改的长度值，则会修改失败
修改列名
alter table t1 rename column email to address;
删除列
alter table t1 drop column address;
4.重命名表
rename t1 to t3;//表是数据库的对象，rename 是针对对象的。
5.表的约束
1).表的约束的含义：对录入进来的数据有要求
2).作用：保持数据的一致性。
3).有5种约束(要求)：
(1).Primary Key 主键约束
要求：唯一+非空
(2).Foreign Key 外键约束
要求：主表里有这个东西，子表才可以引用这个东西。比如部门表就是主表，员工表就是子表，员工表依赖于部门表，比如
有个10号部门的员工，则必须先有10号部门。
外键还有一个要求：作为子表里的外键(比如部门编号)的字段必须是主表里面的主键。
外键也是关系型数据库的特点，即两个表之间有关联。
(3). Unique 唯一性约束
要求：不能重复。是主键要求内的一种要求。
(4).Not Null 非空约束
要求：不能为空。是主键要求内的一种要求。
(5).Check 检查性约束
检查输入数据的格式，是否符合要求。
4).constraint 关键字，用于创建约束时，给约束起名
5).示例:
create table student(sid number(4) constraint student_PK primary key ,
sname varchar2(30) not null,
sex varchar2(30) check (sex in ('男','女')),
sal number check (sal > 10000),
deptno number(2) references dept(deptno) on delete cascade
);
//constraint 是约束关键字表示后面是约束，student_PK是约束名(规范:表名_约束类型)，primary key表示这个约束是主键
//not null表示约束非空，check是约束中的检查,Foreign Key(可以省略,由references去指定依赖是哪个表哪一列) 外键约束
例如：部门表dept和员工表emp，不应该存在不属于任何一个部门的员工。用来约束两张表的关系。
注意：外键约束会限制删除操作，即如果父表的记录被子表引用的话，父表的记录默认不能删除。
默认方式要删除的解决方法：
1） 先将子表的内容删除，然后在删除父表。
2） 将子表外键一列设置为NULL值，断开引用关系，然后删除父表。
无论哪种方法，都要在两个表进行操作。所以定义外键时，可以通过references指定如下参数：
——ON DELETE CASCADE：当删除父表时，如发现父表内容被子表引用，(级联)删除子表所有引用该内容的记录。
——ON DELETE SET NULL：当删除父表时，会先把子表中对应外键值置空(SET NULL)，再删除父表。
多数情况下，使用SET NULL方法，防止子表列被删除，数据出错。
推荐使用的外键方式:默认,先删除子表,再删除主表.
6).约束演示:
(1).违反外键约束
SQL> insert into student values(1,'yekai','男',100000,50);
insert into student values(1,'yekai','男',100000,50)
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.SYS_C0011218) - 未找到父项关键字
//SYS_C0011218系统自动生成的约束名
(2).违反主键约束
SQL> insert into student values(1,'fuhongxue','男',100000,40);
insert into student values(1,'fuhongxue','男',100000,40)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.STUDENT_PK)//STUDENT_PK自己起的约束名，最好约束名都自己起这样便于自己去查找
SQL> insert into student values(2,'fuhongxue','男',10000,40);
insert into student values(2,'fuhongxue','男',10000,40)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011216)
SQL> insert into student values(2,'fuhongxue','男',10001,40);
(3).违反性别检查
SQL> insert into student values(3,'luxiaojia','无',10001,40);
insert into student values(3,'luxiaojia','无',10001,40)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011215)
--违反非空检查
SQL> insert into student values(3,null,'无',10001,40);
insert into student values(3,null,'无',10001,40)
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."STUDENT"."SNAME")
insert into student values(3,'luxiaojia','男',10001,40);
(4).级联删除
SQL> delete from dept where deptno=40;
已删除 1 行。
SQL> select * from student;
未选定行
推荐使用的外键方式:默认,先删除子表,再删除主表.
7).查询表里面的约束：
select constraint_name,constraint_Type,search_condition from user_constraints where table_name = 'STUDENT';
表删除后，这些约束就没了。即约束的生命周期是表存在。
/*******************************************************************************************/
十七、其他数据库对象
数据库的对象：表、视图、索引、序列、同义词
：存储过程、存储函数、触发器、包、包体、数据库链路(datalink)、快照。(12个)
表 基本的数据存储集合，由行和列组成。
视图 从表中抽出的逻辑上相关的数据集合。
序列 提供有规律的数值。
索引 提高查询的效率
同义词 给对象起别名
数据库对象中的表相关的前面已经讲了，接下来是其他数据库对象
1.视图
视图：从表中抽出的逻辑上相关的数据集合。
所以：1. 视图基于表。2. 视图是逻辑概念。3. 视图本身没有数据。
使用示例：
1).创建(视图只能创建、删除、替换。（不能修改，修改则要加括号里的 替换replace，
同时还有一个作用如果创建的这个视图已经存在加了这个替换关键字就会替换掉旧的(其实也就是相当于修改旧的视图了)，就不会报错了）
如：刚刚创建的empincomeview，其他语句不变，将create一行改写成)：
create (or replace) view empincomeview as
select e.empno, e.ename, e.sal, e.sal*12 annualsal, e.sal*12+nvl(comm, 0) income, d.dname
rom emp e, dept d
where e.deptno = d.deptno;
2).使用：
select * from empincomeview;
没有权限,需要先登录 dba
C:\Users\Administrator>sqlplus sys/sys@orcl100 as sysdba
授权给scott
grant create view to scott;
视图本身没有数据,逻辑概念,数据依赖于表.即数据是从表里面来的。
3).作用:简化复杂查询,隔离数据访问(从视图中获取，也就是查询时不知道具体的查询语句，只有创建者才知道)。
相当于作了替换，用视图替换复杂的查询语句，
即执行查询视图，相当于执行查询视图中的语句
总结一句话：不通过视图做insert、update、delete操作。因为视图提供的目的就是为了简化查询。
4).删除视图：SQL> drop view testview; //不加“purge”关键字(因为视图本身都没有数据，即不会经过回收站)。
2.序列
序列 提供有规律的数值。
1).可以理解成数组：默认，从[1]开始，长度[20] [1, 2, 3, 4, 5, 6, …, 20] 在内存中。
20个是在内存中，如果用完了则会再加载20个，也就是缓冲长度是20 *
由于序列是被保存在内存(即实例中)中，访问内存的速率要高于访问硬盘的速率。所以序列可以提高效率
2).create sequence myseq;//myseq为序列的名字
3).序列的2个值：nextval,currval ,引用时语法，序列名.xxxval ,:
获取序列会使用到这两个值，注意，可以理解为序列里面蕴含着一个指针，
使用nextval获取序列值的时候，获取的是序列中指针指向的下一个元素，同时获取后指针会加一，即
此时再使用currval获取的就是刚才nextval获取的值，因为指针移动了，
同时 下次再使用nextval获取序列值的时候，获取的是下下一个元素。
当表刚创建好时，此时指针指向的是第一个元素的前一个位置，即使用currval获取不到任何元素，
要使用 nextval才可以开始使用
SQL> create sequence myseq;
SQL> select myseq.currval from dual;//获取当前序列元素
select myseq.currval from dual
*
第 1 行出现错误:
ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义
SQL> select myseq.nextval from dual;//获取下一个序列元素
NEXTVAL
----------
1
drop sequence myseq;// 删除序列
4).序列的作用:一般给主键使用字段使用
insert into dept values(myseq.nextval,myseq.nextval||'name',myseq.nextval||'loc');//序列给主键字段使用
循环执行这个语句，即可实现插入不同的数据，也就是可以插入唯一的主键
注意上述语句是一条语句，所以nextval是一样的值，也就是引用nextval是在一条语句里面跑的时候值是一样的。
5).查询哪一列是主键的其他方式：
ed dept;//dept是表名，执行这个命令后再选择keys时 则会有对应列信息的展示。
具体见图15：
6).什么情况下主键不连续?
一个序列多个表使用
数据删除
执行rollback ,序列不进行回滚(原因是序列在实例中，所以回滚不了)，
所以记录恢复到以前了，即之前获取到的序列值没了，同时序列没有恢复，也就是再次获取的序列值
是回滚删除掉的记录中的序列的下一个，那么这个序列值是和删除掉的序列值连续的，因为是删除掉的，所以此时
就不连续了。
3.索引
1).索引需要占用空间,需要权限.
作用，提高查询效率，,提高查询效率的原因就是有序
由于原有的数据是不能进行排序的(不能修改数据)，所以需要创建一个有序的，这个创建的有序对象就是索引
2).关于索引的创建：
对索引值进行排序，然后再把索引值和行地址关联起来，这样(即创建索引后)就可以，
通过索引确定行地址(rowid，代表当前记录在内存中地址),通过行地址得到数据.
3).创建索引：
--create index indexname on tablename(columnname1,columnname2,...);
create index myinde on emp(ename);//create index 创建索引，myinde 索引名，on emp(ename) 使用emp(ename)作为索引
索引一旦创建就形成了索引表,由oracle自行维护. 默认索引是btree索引，还有一种索引是位图索引
4).删除索引：
drop index indexname;
5).使用索引：
在查询条件里有索引的条件，即要使用 作为索引值的列 来进行查询，
如上诉例子中就要使用ename来进行查询才会用好索引。
create index indexname on tablename(columnname1,columnname2,...);
//创建了多个列作为索引，称为联合索引，联合索引的使用是多个索引值都要作为查询条件，即
columnname1,columnname2,... 都要作为查询条件。
6).索引是为了提高查询效率的，所以：
以下情况可以创建索引:
列中数据值分布范围很广
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
下列情况不要创建索引(没有意义，浪费空间):
表很小
列不经常作为连接条件(查询条件)或出现在WHERE子句中
查询的数据大于 2%到4% //这个指标只是建议值
表经常更新
4.同义词
给对象起别名
1).同义词的意思
实际生产环境(工作环境，即数据库的数据都是真实的)中，一般开发者之类的用户都是只有查询权限，
如果想要修改则必须通过公司开发的修改工具去修改，修改工具会记录具体谁去修改了，以及修改的内容都会记录。
并且当前用户并不是所有的数据都可以查询的，如果想查询其他用户的数据，要select * from 用户名.表名
但是有时候用户名过长，所以一般在查询用户建立同义词。
同义词的意思是，在当前用户下建立一个和其他用户相同名字的对象，这个对象名叫同义词
比如在当前用户下创建一个也叫emp的(可以认为是表，但是实际上是同义词)，然后查询的时候就可以直接 select * from emp;
相当于select * from 用户名.表名，即相当于作了一个替换。
所以同义词的作用也是:简化复杂查询,隔离数据访问
2).创建示例：scott用户访问hr用户中的表，
(scott用户看不到 数据库中hr用户能看到的表。默认情况下创建一个用户并在该用户下创建的表其他用户是看不到的，
即使用 select * from 其他用户名.表名 也无法访问，
想看到的话，需要其他用户或者系统管理员去授予权限：
grant select,update,insert on employees to 其他用户;
//给予其他用户select,update,insert当前用户下employees表的权限,此时就可以通过 select * from 其他用户名.表名 查看了)
(1).在hr用户下给scott用户赋权：grant select on employees to scott;
能查看其他用户的表的时候，就可以建立同义词了：
(2).scott用户(查询用户)下：
此时可以查看hr用户employees,因为已经赋权
SQL> select * from hr.employees;
创建同义词
SQL> create synonym employees for hr.employees;
//create synonym 创建同义词 ，employees 同义词名，for hr.employees 等同于hr用户下的employees表
(3).创建权限不足,需要dba用户
给scott创建同义词的权限：
grant create synonym to scott;
3).删除同义词
drop synonym employees;
4).新创建一个用户,再创建一个查询用户,如何在查询用户批量创建新创建用户的同义词?
思路: tab 字典 (tab 字典下可以看到当前用户下所有的表)===> 批量生成 create 语句.
//tab 字典下可以看到当前用户下所有的表，然后考虑如何用这些表批量生成 create 语句：
reate synonym employees for hr.employees;
