SQL

==SQLPLUS==

• DML(Data Manipulation Language,数据操作语言)----用于检索或者修改数据
• DDL(Data Definition Language)----用于定义数据的结构，如创建、修改或者删除数据库对象
• DCL(Data Control Language,数据控制语句)----用于定义数据库用户的权限

• sqlplus使用其他用户连接

  conn 用户名/密码 [AS SYSDBA | SYSOPER]
  eg:conn sys/change_on_install as sysdba;

• 显示当前正在连接的用户是哪一个

  show user;

简单查询

select * from emp;
select * from tab;

---

限制查询

• 设置每行显示的长度

  set linesize 长度;
  eg:set linesize 300;

• 设置每页显示记录的长度

  set pagesize 行数;
  eg:set pagesize 30;

• 列的几种类型

  NUMBER(4):表示是数字，长度为4
  VARCHAR2(10):表示的是字符串，只能容纳10个长度
  DATE:表示日期
  NUMBER(7,2):表示的是数字，其中小数位占2位，整数位占5位，总共是7位

---

==SQL语句==

简单查询语句

• 格式
  SELECT * | 具体的列 别名 FROM 表名称;
• 指定需要查询的列
  `SELECT empno,ename,job FROM emp;'(eg:需要查询编号、姓名、工作)
• 给一个列起别名
  SELECT empno 编号，ename 姓名,job 工作 FROM emp;
• 查询一个指定的字段
  SELECT job FROM emp;
• 消除掉所有重复的列
  SELECT {DISTINCT}|具体的列 别名 FROM 表名称;
  eg:SELECT DISTINCT empno,job FROM emp;
  ==注意*== 消除重复列的时候，如果要同事查询多列，则必须保证所有列都重复才能消除掉。
• 要求显示格式
  编号是：xxx的雇员，姓名是：Smith，工作：CLERK
  ==使用||表示进行字符连接，所有其他的固定信息要使用"'"括起来==
  SELECT'编号是：'||empno||'的雇员，姓名是：'||ename||',工作：'||job FROM emp;
• 四则运算
  SELECT ename,sal*12 FROM emp;
  给运行结果sal12起别名 SELECT ename,sal*12 income FROM emp;

---

限定查询

• 语法：SELECT {DISTINCT}*|具体的列别名 FROM 表名称{WHERE 条件(s)};
  eg:SELECT *FROM emp WHERE sal>1500;
• 查询字段不为空
  SELECT * FROM emp WHERE comm IS NOT NULL;
• 查询字段为空
  SELECT * FROM emp WHERE comm IS NULL;
• 两个条件同时满足用AND连接
  SELECT * FROM emp WHERE sal>1500 AND comm IS NOT NULL;
• 两个条件满足其中一个即可OR
  SELECT * FROM emp WHERE sal>1500 OR comm IS NOT NULL;
• 取反
  SELECT * FROM emp WHERE NOT(sal>1500 AND comm IS NOT NULL);
• 条件同时满足:sal>1500,sal<3000
  SELECT * FROM emp WHERE sal>1500 AND sal<3000;
• 指定查询范围的过滤语句
  语法格式：字段 BETWEEN 最小值 AND 最大值(在...到...之间)
  eg:SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;
  日期过滤(查询出1981年雇佣的全部雇员信息)
  eg:SELECT * FROM emp WHERE hiredate BETWEEN '1-1月 -81'AND'31-12 月-81'
• Oracle对大小写敏感
  SELECT * FROM emp WHERE ename='smith'!=SELECT * FROM emp WHERE ename='SMITH'
• IN操作符（在...范围内）
  语法格式
  字段 IN(值1,值2,值n)
  字段 NOT IN(值1,值2,值n)
  eg:SELECT * FROM emp WHERE empno IN(7369,7499,7521)等同于SELECT * FROM emp WHERE empno==7369 OR empno==7499 OR empno==7521;
  ==使用IN操作符不光可以用在数字上，也可以用在字符串的信息上。==
  SELECT * FROM emp WHERE ename IN('SMITH','ALLEN','KING')
  ==如果在指定的查询范围中指定了额外的内容，则不影响程序运行.==
• LIKE模糊查找
  通配符
  "%":可以匹配任意长度的内容
  "_":可以匹配一个长度的内容
  查询出所有雇员姓名中第二个字母包含“M”的雇员信息
  SELECT * FROM emp WHERE ename LIKE '_M%';
  查询出雇员姓名中包含字母M的雇员信息（任意位置）
  SELECT * FROM emp WHERE ename LIKE '%M%';
  如果在使用LIKE的时候没有指定查询的关键字,则表示查询全部
  SELECT * FROM emp WHERE ename LIKE '%%';
  要求查询出在1981年雇佣的雇员信息
  SELECT * FROM hiredate LIKE '%81%';
  查询工资中包含5的雇员信息
  SELECT * FROM emp WHERE SAL like '%5%';
• 运算符 >,>=,=,<,<=
  使用"<>"完成
  SELECT * FROM emp WHERE empno<>7369;
  使用"!="完成
  SELECT * FROM emp WHERE empno!=7369;

---

数据的排序

语法格式
SELE {DISTINCT} *| 具体的列 别名 FROM 表名称
{WHERE 条件(s)} {ORDER BY 排序的字段1,排序的字段2 ASC|DSC} ==->ASC表示升序、DSC表示降序==

• 工资从低到高排序(升序)
  SELECT * FROM emp ORDER BY sal;等同于
  SELECT * FROM emp ORDER BY sal ASC;(默认升序排列)
• 降序
  SELECT * FROM emp ORDER BY sal DESC;
• 同时存在两个排序条件
  SELECT * FROM emp WHERE deptno=10 ORDER BY sal DESC,hiredate ASC;
  ==排序的操作肯定是在整个sql语句的最后执行==

单行函数

单行函数语法
function_name(column|expression,[arg1,arg2,...])
参数说明
function_name:函数名称;column:数据库列名;expression字符串或计算表达式;arg1,arg2在函数中使用参数
单行函数分类
字符函数: 接收字符输入并且返回字符或数值
数值函数: 接收数值输入并返回数值
日期函数: 对日期数据进行操作
转换函数: 从一种数据类型转换成另一种数据类型
通用函数: NVL函数、DECODE函数

字符函数

字符函数的功能主要是进行字符串数据的操作:
UPPER(字符串|列)：将输入的字符串变为大写返回
LOWER(字符串|列)：将输入的字符串变为小写返回
INITCAP(字符串|列)：开头首字母大写
LENGTH(字符串|列)：求出字符串的长度
REPLACE(字符串|列)：进行替换    
SUBSTR(字符串|列,开始点,[结束点])：字符串截取;

==Oracle之中有一点比较麻烦，即使要验证字符串，也必须编写完整的SQL语句，所以在Oracle数据库之中为了用户的查询方便，所以专门提供了一个“dual”的虚拟表。==

• 转大写的函数(UPPER())
  eg: SELECT UPPER('hello') FROM dual;
• 将用户输入的数据全部转换成大写进行查询
  eg:SELECT * FROM emp WHERE ename=UPPER('Smith');
• 使用LOWER()函数将一个字符串变为小写字母表示
  eg:SELECT LOWER('HELLO WORLD') FROM dual;
• 使用initcap()函数将函数单词的第一个字母大写
  eg:SELECT INITCAP('HELLO WORLD')FROM dual;
• 将雇员姓名（变量）变为开头字母大写
  eg:SELECT INITCAP(ename) FROM emp;
• 字符串除了可以使用“||”连接之外，还可以使用CONCAT()函数进行连接操作。
  eg:SELECT CONCAT('hello ','world')FROM DUAL;
• 字符串的截取、求出字符串的长度、进行指定内容的替换
  eg:SELECT SUBSTR('hello',1,3) 截取字符串,length('hello') 字符串长度,replace('hello','l','x') 字符串替换 FROM dual;
• ==SUBSTR()函数的面试题==
  Oracle中substr()函数的截取点是从0还是从1开始？
  从0或从1开始效果是一样的，因为Oracle比较智能。
• 要求显示所有雇员的姓名及姓名后三个字符(截取)
  eg:'SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM dual;'
• 倒着截取数据
  eg:SELECT ename,SUBSTR(ename,-3,3)FROM emp;

数值函数

1. *四舍五入:*ROUND()           
2. *截断小数位:*TRUNC()
3. *取余(取模):*MOD

• 执行四舍五入操作
  eg:SELECT ROUND(789.536) FROM dual;==result:790==
• 保留两位小数(截取)
  eg:SELECT ROUND(789.536,2) FROM dual;==result:789.54==
• ==ROUND()== 函数还可以直接对整数进行四舍五入的进位。
  eg:SELECT ROUND(789.536,-2)FROM dual;==result:800==
• ==TRUNC()== 与ROUND()不同的是，在ROUND()操作中，不会保留任何的小数，而且小数点也不会执行四舍五入的操作。
  eg:SELECT TRUNC(789.536)FROM dual;==result:789==
• 通过TRUNC()也可以指定小数点的保留位数
  eg:SELECT TRUNC(789.536,2)FROM dual;==result:789.53==
• 使用负数表示位数(TRUNC())
  eg:SELECT TRUNC(789.536,-2)FROM dual;
• 使用MOD()函数可以进行取余的操作
  eg:SELECT MOD(10,3) FROM dual;

日期函数

1. 日期-数字=日期
2. 日期+数字=日期
3. 日期-日期=数字（天数）

• 显示当前日期
  SELECT SYSDATE FROM DUAL;
• 求出星期数：当前日期-雇佣日期=天数/7=星期数
  SELECT empno,ename,ROUND((SYSDATE-hiredate)/7)FROM emp;

Oracle提供了以下的日期函数支持

* MONTHS_BETWEEN():求出给定日期范围的月数
* ADD_MONTHS():在指定日期上加上指定的月数，求出之后的日期
* NEXT_DAY():写一个的今天是那一个日期
* LAST_DAY():求出给定日期的最后一天日期     

• 验证MONTHS_BETWEEN()
  SELECT empno,ename,MONTHS_BETWEEN(sysdate,hiredate)FROM emp;==注意：== ==程序查询时包含了小数点，可以使用ROUND()进行四舍五入的操作==
• ADD_MONTHS()函数
  SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL;
• NEXT_DAY()函数
  SELECT NEXT_DAY(SYSDATE,'星期一') FROM DUAL;
• LAST_DAY()函数
  SELECT LAST_DAY(SYSDATE) FROM DUAL;

转换函数

* TO_CHAR():转换成字符串
* TO_NUMBER():转换成数字
* TO_DATE():转换成日期

• 查询所有雇员的雇员编号、姓名、雇佣日期
  SELECT empno,ename,hiredate FROM emp;
• TO_CHAR()函数
  1. 通配符
  • 年：y,年是四位的数字，所以使用yyyy表示
  • 月：m,月是二位的数字，
  • 日:d,日是二位的数字，所以使用dd表示
    SELECT empno,ename,TO_CHAR(hiredate,'yyyy')year,TO_CHAR(hiredate,'mm')months,TO_CHAR(hiredate,'dd')day FROM emp;
    ==TO_CHAR()还可以用于日期显示的转换功能==
  • Oracle中默认的日期格式：19-4 月 -87
  • 中国喜欢的格式：1987-04-19
    SELECT empno,ename,TO_CHAR(hiredate,'yyyy-mm-dd')FROM emp;
  • 去掉前导0(加fm)
    SELECT empno,ename,TO_CHAR(hiredate,'fmyyyy-mm-dd')FROM emp;
    ==TO_CHAR()也可以用于数字的转换功能==
    最好在数字中加入一些符号，以分割太长的数字，中国一般用“,”
  • 9:表示一位数字
    SELECT empno,ename,TO_CHAR(sal,'99,999')FROM emp;
  • ==希望数字可以明确表示出区域（货币显示格式）==
    1. $:表示美元
    2. L:表示Local的缩写，以本地的语言进行金额的显示
       SELECT empno,ename,TO_CHAR(sal,'L99,999')FROM emp;
• TO_DATE()函数
  SELECT TO_DATE('2009-02-16','yyyy-mm-dd') FROM dual;

通用函数

• 要求求出每个雇员的年薪
  SELECT empno,ename,(sal+comm)*12 FROM emp;
  ==结果出现为空==是因为有些雇员的奖金是NULL,NULL值计算之后的结果还是NULL.
• 将一个指定的NULL值变为指定的内容
  SELECT empno,ename,NVL(comm,0),(sal+NVL(COMM,0))*12 income FROM emp;
• DECODE()函数。类似与IF...ELSEIF..ELSE的语句
  SELECT DECODE(1,1,'内容是1',2,'内容是2',3,'内容是3') FROM dual;
• 要求查询出雇员的编号，姓名，雇佣日期及工作，将工作替换成以下的信息
  • CLERK:业务员
  • SALESMAN:销售人员
  • MANAGER:经理
  • ANALYST:分析员
  • PRESIDENT:总裁
• 要求查询出雇员的编号，姓名，雇佣日期及工作，将工作替换成以下的信息
  SELECT empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期,DECODE(JOB,'CLERK','业务员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁')职位 FROM emp;

---

Oracle

==多表查询==

多表查询的基本语法

• 格式
  *SELECT {DISTINCT} |查询列1 别名1,查询列2 别名2,...FROM 表名称1 别名1,表名称2 别名2,...{WHERE 条件(s)} {ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC,...}
• 同时查询emp和dept表
  SELECT * FROM emp,dept; -- 56条记录
• 查询emp表中的记录数
  SELECT COUNT(*)FROM emp; - 14条记录
• 查询dept表中的记录数
  SELECT COUNT(*)FROM dept;-4条记录
  
• 多表查询中加入WHERE语句，就可以消除笛卡尔积
  SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
• 如果表名称过长的话？所以在使用的时候一般会为表起别名
  SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
• 要求查询出雇员的编号、雇员的姓名、部门的编号、部门名称及部门位置
  SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno;
• 要求查询出每个雇员的姓名、工作、雇员的直接上级领导的姓名
  mgr表示一个雇员的上级领导的编号。
  SELECT e.ename,e.job,m.ename FROM emp e,emp m WHERE e.mgr=m.empno;
• 要求进一步扩展之前的程序，将雇员的部门名称同时列出
  SELECT e.ename,e.job,m.ename,d.dname FROM emp e,emp m,dept d WHERE e.mgr=m.empno AND e.deptno=d.deptno;

要求查询出每个雇员的姓名、工资、部门名称，工资在公司的等级，及其领导的姓名及工资所在的公司的等级

• 先确定工资等级表的内容（查询salgrade表）
  SELECT * FROM salgrade;
• 分解：查询出每个雇员的姓名、工资、部门名称，工资在公司的等级（salgrade）
  SELECT e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
• 分解：其领导的姓名及工资所在公司的等级
  SELECT e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade FROM emp e,dept d,salgrade s,emp m,salgrade ms WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal AND e.mgr=m.empno AND m.sal BETWEEN ms.losal AND ms.hisal;
• 现在要求按照以下的样式显示工资等级
  *1.第五等工资
  2.第四等工资
  3.第三等工资
  4.第二等工资
  5.第一等工资
  SELECT e.ename,e.sal,d.dname,DECODE(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),m.ename,m.sal,DECODE(ms.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资') FROM emp e,dept d,salgrade s,emp m,salgrade ms WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal AND e.mgr=m.empno AND m.sal BETWEEN ms.losal AND ms.hisal;

---

左、右连接

• 将emp和dept表关联查询
  SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno;

• 部门表中有四个部门的信息，但只查询出3个。因为在雇员表中并没有指定40部门的雇员。
  SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno(+)=d.deptno;

  • (+)在=左边表示右连接
  • (+)在=右边表示左连接

• 查找雇员的编号、姓名及其领导的编号、姓名
  SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m WHERE e.mgr=m.empno;
  ==结果少了一条KING的记录，因为其是最高领导，所以mgr为空==

  SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m WHERE e.mgr=m.empno(+);
  ==加入左连接之后可以发现KING出现了。==

---

1999语法对SQL的支持

• 语法格式

SELECT table1.column,table2.column FROM table
[CROSS JOIN table2]|[NATURAL JOIN table2]|
[JOIN table2 USING(column_name)]|
[JOIN table2 ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULL OUTER JOINtable2 ON(table1.column_name=table2.column_name)];

• 交叉连接（CORSS JOIN）:产生笛卡尔积
  SELECT * FROM emp CROSS JOIN dept;
• 自然连接（NATURAL JOIN）:自动进行关联字段的匹配
  SELECT * FROM emp NATURAL JOIN dept;
• USING子句：直接关联的操作列
  SELECT * FROM emp e JOIN dept d USING(deptno) WHERE deptno=30;
• ON子句，用户自己编写连接的条件
  SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno) WHERE e.deptno=30;
• 左外连接、右外连接：LEFT JOIN，RIGHT JOIN
  SELECT e.ename,d.deptno,d.dname,d.loc FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno);

组函数及分组统计

组函数

- COUNT():求出全部的记录数
- MAX():求出一组中的最大值
- MIN():求出最小值
- AVG():求出平均值
- SUM():求和

• COUNT()函数
  SELECT COUNT(empno) FROM emp;
• 求出所有员工的最低工资
  SELECT MIN(sal) FROM emp;
• 求出所有员工的最高工资
  SELECT MAX(sal) FROM emp;
• 求出20部门中的总工资
  SELECT SUM(sal) FROM emp WHERE deptno=20;
• 求出所有员工的平均工资
  SELECT AVG(sal) FROM emp;

分组统计

• SQL语法格式（GROUP BY）：
  *SELECT {DISTINCT} |查询列1 别名1，查询列2 别名2,...FROM 表名称1 别名1,表名称 别名2,...{WHERE 条件(s)}{GROUP BY 分组条件}{ORDER BY 排序字段 ASC|DESC,|DESC,...}

• 求出每个部门的雇员数量。
  SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno;

• 求出每个部门的平均工资
  SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

• ==注意==
  SELECT deptno,COUNT(empno)FROM emp;

  1. 如果程序中使用了分组函数，则有两种可以使用的情况：
     • 程序中存在了GROUP BY,并指定了分组条件，这样可以将分组条件一起查询出来
     • 如果不使用分组的话，则只能单独的使用分组函数。
  2. 在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。
     SELECT deptno,empno,COUNT(empno) FROM emp GROUP BY deptno;==(报错)==

• 按部门分组，并显示部门的名称，及每个部门的员工数
  SELECT d.dname,COUNT(e.empno) FROM dept d,emp e WHERE d.deptno=e.deptno GROUP BY d.dname;

• 要求显示平均工资大于2000的部门编号和平均工资
  SELECT deptno,AVG(sal) FROM emp WHERE AVG(sal)>2000 GROUP BY deptno;==（报错）==
  原因：分组函数只能在分组中使用，不允许在WHERE语句之中出现；如果现在假设要指定分组的条件，则只能通过第二种条件的指令：

• ==HAVING==
  SELECT {DISTINCT} * |查询列1 别名1，查询列2 别名2,...FROM 表名称1 别名1，表名称2 别名2,..{WHERE 条件(s)} {GROUP BY 分组条件{HAVING 分组条件}}{ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC,...}
  SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;

• 显示全部的非销售人员：job<>'SALESMAN'
  SELECT * FROM emp WHERE job<>'SALESMAN';

• 按工作分组，同时求出工资的总和
  SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job;

• 对分组的条件进行限制，工资总和大于5000
  SELECT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000;

• 使用排序，按升序排序
  SELECT job,SUM(sal) su FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ORDER BY su;