SQL

SQL语句分类
- 数据处理语言（DML）
select，insert，update，delete，where从句，group by子句，order by子句句以及查询子句
- 数据定义语言（DDL）
create，alter，drop
数据类型
1.数值型数据类型
NUMBER:格式为NUMBER(p,s)
BINARY:以二进制形式存储数字。
DATE ：日期类型
2.字符型数据类型
CHAR:char(20)，后补空格
VACHAR2和VACHAR:存储用户输入的字符。
NCHAR和NVCAHR2：对双字节语言支持。
LONG:上限为2GB数据，后被取代
BLOB、CLOB和NLOB：统称为LOB对
3.其他数据类型
包括RAW，LONG RAW、对象数据类型
数据定义语言–CREATE
1.CREATE TABLE:
eg：

create table school_user(userID      varchar2(15),userName   varchar2(15),userPassword varchar2(15) not null,primary key(userid))

2.CREATE VIEW

CREATE VIEW SYSTEM.EMPVIEW AS(SELECT EMPNO,ENAME,DNAME,LOCFROM EMP,DEPTWHERE EMP.DEPTNO = DEPT.DEPTNO);

数据定义语言–DROP

DROP object_type object_nema;

数据定义语言–ALTER

ALTER TABLE table_name add(phone_no varchar2(20));ALTER TABLE table_name dropCOLUMN phone_no;

更改表的约束

ALTER TABLE table_name  ADD CONSTRAINT constraint_name constraint_type;

eg:

ALTER TABLE emp  ADD CONSTRAINT FK_test FOREIGN KEY(deptno) REFERENCES dept(deptno);

删除表的约束

alter table table_name drop constraint  constraint_name;

e.g:

alter table student drop constraint  PK_EMP;

数据处理语言
- SELECT
最常用的语句，常和where、order by、group by、搭配完成查询操作
eg:select * from emp;
定义别名：select e.ename 姓名，e.job, e.mgr from scott.emp e;
1.WHERE
作用：限制了结果集中返回行的数量；

– WHERE从句中跟随有条件表达式，通过对该表达式求值，求值的结果为TRUE或FALSE。如果值的结果为TRUE，则将行加入到结果集中；否则不加入。
例如：查询月工资超过1500的员工

select * from scott.emp where sal > 1500;

– WHERE从句：IN表达式

select * from scott.emp where job in ('ANALYST', 'SALESMAN');

–WHERE从句：多选择条件(部门20/30工资过千的)

select * from scott.emp where deptno=20 or deptno = 30 and sal > 1000；select * from scott.emp where ( deptno=20 or deptno = 30 ) and sal > 1000;

2.ORDER BY语句
– SELECT语句返回的行不按照任何特殊逻辑顺序进行显示。如果需要按照特定顺序对结果集进行处理，则应该使用ORDER BY从句，明确地指定结果集中行的顺序。
– 可以通过一个或多个列指定排列顺序，而且列不一定包含在返回的数据中（既查询列列表中可以不包含）。
– 可以指定两种排列顺序，ASC（升序，默认）或者DESC（降序）。
– 在SELECT语句中，ORDER BY从句在所有其他从句的后面。

select * from scott.emp order by salselect * from scott.emp order by deptno, sal desc;

3.GROUP BY
– 用于聚集信息；在SELECT语句内部，可以包含在多个值上运算的函数，例如SUM、AVG、MIN、MAX或者COUNT等集合函数，一般称为集合函数。
– Oracle内部提供了很多函数供开发人员使用。函数嵌套在SQL语句中使用，但某些函数的使用需要特殊处理。
例如：确定每个部门的工资总额。

select deptno, sum(sal) from scott.emp;

集合函数在处理过程中，会根据GROUP BY从句中的列对结果集进行分类。此时正确的语句为：

select deptno, sum(sal) from scott.emp group by deptno;

4.HAVING
– HAVING从句必须在有GROUP BY子句的前提下方能使用。其工作原理和WHERE从句限制结果集的工作方式相似。

select deptno, job, sum(sal) from scott.emp group by deptno, job having job=‘CLERK’;

5.多表访问
– 内部链接
内部连接是将表连接在一起的标准方式。实际上在设计表结构时，已经隐含了表和表之间的联系。
例如：dept表中存储了部门信息，emp表中存储了员工信息，如果需要获取员工以及员工所在部门详细信息（例如名称和地址）时，需要进行两表的内部连接。连接涉及的列是两表中都存在的deptno列。

select dept.deptno, dname, empno, enamefrom scott.dept, scott.empwhere dept.deptno = emp.deptno;

– 外部链接
如果查询的需求变为：查询所有的部门信息以及每个部门中所包含的员工信息。既查询每个部门的员工明细记录。
需要外部连接来解决：当两个表中根据deptno不匹配时，将对应的行加入到结果集中。

select dept.deptno, dname, empno, enamefrom scott.dept, scott.empwhere dept.deptno = emp.deptno(+);

6.子查询：
– 前面例子使用where从句中两个列值的比较来限制SQL语句的结果集；
– 同样可以使用一个查询的结果来限制另一个查询的结果集。此时SQL语句内部的一个查询称为子查询。实际上是一种查询的嵌套关系。
– 子查询语句出现的位置：在任何使用单独值的地方使用子查询，可以是在where从句中或者select关键字后的列列表中。
– 子查询真正强大的方面是有相互关系的子查询的使用，不使用子查询往往无法实现其查询功能。
例子：查找所有部门中比其部门平均工资高的员工。

select ename, sal, deptno from scott.emp ewhere sal >     ( select avg(sal) from scott.emp e1       where e1.deptno = e.deptno )order by deptno, sal;

INSERT
– INSERT语句用来将一个或者多个行加入到一个或者多个表中（通常为一个表）
– INSERT语句的语法格式如下：

  INSERT INTO  < table-expression>    < column-list>     VALUES ( < values> );

– 多表插入语句

INSERT FIRST    when comm >= 1000 then        into high_comm    when comm < 1000 then        into other_comm    else        into no_commselect empno, ename, comm from emp;

UPADTE
– UPDATE语句用来改变已经存在于行中的数据。
– UPDATE语句的语法为：

UPDATE table_name  set column = value [, column = value ]  [where < restrictive-condition>]

DELETE
– DELETE语句用来删除一个表中所有与选择标准相匹配的行。
– DELETE语句的语法为：

DELETE FROM table_name  where < restrictive-condition>

SQL扩展
指的是对SQL语句中WHERE从句中的条件表达式的扩展。

• LIKE
  Oracle通过提供LIKE关键字以及通配符来满足对于字符的比较操作。
  通配符%，代表了任意长度的任意字符；下划线“_”则表示任意的单个字符。

select * from scott.emp where ename like ‘刘%’;

通过单个匹配符来查询:

select * from scott.emp where ename like ‘S____’;

• IN及EXISTS
  两个条件IN及EXISTS都用来决定一个结果集中的值是否在其他结果集中。
  对应的语法分别为：

where column in ( value_list | subquery )select …… where exists subquery ;

两者的区别：
IN返回的是满足条件的结果集。EXISTS返回的是存在满足条件的结果集。EXISTS并不会只返回满足的，只要当中存在满足条件的则会全部返回，与IN不同。
- ALL、SOME、ANY
检查另外查询组中行的存在，可以在带有关系运算符的连接中使用它们，例如大于或小于。
三个条件如下：
– ANY检查条件左边的表达式是否存在于条件右边的任何行中。
– SOME执行与ANY一样的比较
– ALL检查条件左边的表达式对于条件右边的结果组中的所有行是否是正确的
eg：希望查出比任何销售人员的薪金高的员工，该薪金包括工资及奖金。

select * from scott.emp where job != ‘SALESMAN’and sal >any ( select (sal+comm) from scott.emp   where job = ‘SALESMAN’);

在ANY关键字下，找出的是所有薪金高于销售人员薪金的非销售人员。注意此时任何一个非销售人员的薪金只要高于销售人员的最低薪金（销售人员薪金的MIN操作），既满足查询条件；
SOME结果和ANY一样；
在ALL关键字下，要求任何一个非销售人员的薪金都要高于任何一个销售人员的薪金，既高于销售人员的最高薪金（销售人员薪金的MAX操作），方满足查询条件。