SQL语言基础

SQL语言概述

SQL介绍

SQL（结构化查询语言）是一种在关系型数据库中定义和操作数据的标准语言，是应用程序与数据库进行交互操作的接口。当执行SQL语句时，每次只能发送并处理一条语句。如果需要减少语句的发送和处理的次数，就可以使用PL/SQL。当使用PL/SQL执行SQL语句时，用户只需要理解其逻辑含义即可，无须了解SQL语句执行的具体步骤。Oracle会自动的对SQL语句进行优化处理，确定最佳执行路线，然后执行SQL语句，最终返回用户结果。

SQL语言分类

⑴ 数据定义语言（Data Definition Language,DDL）
用于创建、修改和删除数据库对象，如CREATE TABLE、ALTER TABLE 、DROP TABLE等。DDL语句可以自动提交事务。
⑵ 数据操纵语言（Data Manipulation Language,DML）
用于操纵数据库，包括INSERT、UPDATE、DELETE、SELECT等。
⑶ 数据控制语言（Data Control Language,DCL）
用于执行授予权限和撤销权限的操作，包括CRANT(授予权限)、REVOKE(撤销权限)两条命令。DCL语句可以自动提交事务。
⑷ 事务控制语言（Transactional Control Language,TCL）
用于维护数据的一致性，包括COMMIT(提交事务)、ROLLBACK(回滚事务)、SAVEPOINT(设置保存点)3条语句。

---

Oracle 数据类型

字符类型

⑴ CHAR类型
CHAR表示固定长度字符串，长度不足时使用空格补充，最多可以存储2000个字节。CHAR类型区分中英文，中文在CHAR中占两个字节，而英文只占一个字节。
⑵ VARCHAR2类型
VARCHAR2表示可变字符串，最多可以存储4000个字节。在定义该数据类型时，应该指定其大小。与CHAR类型相比，使用VARCHAR2可以节省磁盘空间。

数值类型

NUMBER类型可以存储正数、负数、零、定点数和精度为38位的浮点数，格式为：
NUMBER(M,N)
其中，M表示精度，代表的是数字的总位数；N表示小数点右侧的位数。以下将演示NUMBER数据类型的用法：
⑴ age number(2)：表示age列的值只能是整数，并且最多只能取两位整数
⑵ price number(6,2)：表示price列的整数部分最多是6位，小数部分最多只能保留2位。

日期类型

⑴ DATA类型
DATA数据类型用于存储表中的日期和时间数据，取值范围时公元前4712年1月1日至公元9999年12月31日。DATA类型的长度是7,7个字节分别表示世纪、年、月、日、时、分、秒。
⑵ TIMESTAMP类型
TIMESTAMP数据类型用于存储日期的年、月、日以及小时、分和秒值。其中，秒值精确到小数点后6位，该数据类型同时包含时区信息。

大对象类型

⑴ CLOB大字符串对象类型
CLOB（Character Large Object）数据类型用于存储可变长度的字符数据，最多可存储4GB的数据。该数据类型用于存储VARCHAR2类型不能存储的长文本信息。
⑵ BLOB类型
BLOB（Binary Large Object）数据类型用于存储较大的二进制对象，如图形、视频剪辑、声音剪辑等，最多可以存储4GB的数据。

---

DDL 语句

CREATE TABLE 语句

CREATE TABLE 用于创建表，经常会创建该表的主键、外键、唯一约束、Check约束等。在Oracle中创建两张表，SQL代码如下：

create table tb_shop
create table tb_shopType

ALTER TABLE 语句

ALTER TABLE 语句用于对已创建的表进行修改。
⑴ 向已经创建的表中增加一个新列。
语法：

ALTER TABLE tableName ADD columnName dataType

向tb_shop表中添加一个长度为100的字符型新列memo（备注）。

alter table tb_shop add memo varchar2(100)

⑵ 修改表中指定列的数据类型和类型长度。
语法：

ALTER TABLE tableName MODIFY COLUMN columnName dataType 

将tb_shop表中的memo列的长度修改为50。

alter table tb_shop modity memo varchar2(50)

⑶ 删除表中指定的列
语法：

ALTER TABLE tableName DROP columnName

删除tb_shop表中的memo列。

alter table tb_shop drop clumn memo

DROP TABLE 语句

DROP TABLE 语句用于从数据库中删除表及全部数据。
语法：

DROP TABLE tableName

删除tb_shop表

drop table tb_shop

---

DML 语句

INSERT 语句

INSERT 语句用于向表中添加记录。使用该语句时，需要用逗号将各个值隔开，并用单引号将字符串数据值引起来，输入值的顺序必须在与表中定义的顺序相同。
语法：

INSERT INTO table[(column[,column])]
VALUES(value,[,value...]);

⑴ 插入普通数据
向tb_shopType表中插入一条数据。

insert into tb_shopType values(1,'家电类')

⑵ 插入日期值
在表中插入日期值时，必须用单引号将其引起来。Oracle数据库默认的日期格式是“DD-MOD-YY”，分别代表日、月、年。
向tb_shop表中插入一条数据。

insert into tb_shop values(3,'S003','电脑',3400.00,1,'11-3月-10')

UPDATE 语句

UPDATE 语句用于更新表的内容。
语法：

UPDATE table SET column=value[,column=value,...]
[WHERE condition];

将雇用日期为1981年2月20日的雇员工资增加100

update emp set sal=sal+100 where hiredate='20-2-81'

DELETE 语句

DELETE 语句用于删除表的内容
语法：

DELETE [FROM] table [WHERE condition];

使用DELETE语句可以删除一行或多行。若需要从表中删除特定的行，则可以将WHERE字句和DELETE语句一起使用。
删除职位为salesman的员工。

delete from emp where jop='salesman'

---

SELECT 语句

示例数据库概述

在Oracle安装完成后，会在数据库中创建一个SCOTT用户及所属的4张表：dept、emp、bonus和salgrade。随着Oracle数据库技术的发展，这些表已经不能展示Oracle数据库及其产品的最基本特征了。所以从Oracle 9i开始就提供了一个可以用于学习各种新特征的具有丰富示例的数据库。在安装Oracle 11g创建数据库的环节中，如果在“指定数据库配置详细资料”窗口的“实例方案”选项卡选择了“创建带样本方案的数据库”复选框，就会安装新的用户示例数据库。Oracle的示例方案都基于一个虚拟的通过各种渠道销售物资的公司。其中，HR方案是基本的关系数据库方案，用于介绍最简单和最基本的话题，该方案中包含7张表：雇员（employess）、部门（departments）、地点（locations）、国家（countries）、地区（regions）、岗位（jobs）和工作履历（job_history）。

SELECT 语句基本结构

通过select语句实现数据查询功能，从一个或多个数据表中检索所需要的数据。它的基本结构如下：
【语法】

Select [ALL | distinct] [top n [percent]] *|字段列表
From 表1 [,......]
[Where 条件表达式]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 [ASC | DESC],......]

【示例】
从学生表中查询性别为男的学生信息。

Select * from 学生 Where 性别='男'

说明：
⑴ Select 语句共分为5个子句，分别是select子句，from子句，where子句，group by子句，order by子句。其中只有select子句和form子句是必选项，其余都是可选项。
⑵ Select子句，用于投影，用来指明要查询的数据字段列。如果查询所有列，则用*表示。如果查询表中的部分列，则必须要书名字段列表。
⑶ From子句，用来指定要从中检索数据的表名或视图名。可以是单表，也可以是多表，但多表必须要建立表之间的连接。
⑷ Where子句，用于选择操作，表示一个筛选条件，即从表中查询满足条件的数据记录。
⑸ Group by子句用来进行分组查询，having表示对分组后的数据进一步限制条件。
⑹ Order by表示对查询结果集进行排序，ACS表示升序，可省略，DESC表示降序，不可省略。
⑺ ALL表示所有记录，distinct表示去掉结果集中的重复元组。
⑻ Top n表示查询结果集的前n条记录，top n percent表示现实查询结果集的前百分之n条记录。
⑼ 各个子句的运算次序是：from子句，where子句，select子句，group by子句，having子句和order by子句。

---

子查询

子查询是指嵌套在其他SQL语句中的查询，它可以出现在SELECT、WHERE、FROM等语句中，并且继续嵌套子查询（最多255层）。使用子查询，可以用一系列简单的查询构成复杂的查询，从而增强SQL语句的功能。

单行子查询

单行子查询不像外层查询返回记录或者只返回一条记录。子查询可以放在SELECT语句的WHERE或FROM等子句中。
例如，查询公司中工资最高的雇员信息时，先利用子查询获得最高工资，再利用父查询获得其工资等于此最高工资的雇员信息。代码如下：

select first_name,last_name,salary from employess
where salary=(select max(salary) from employess)

多行子查询

多行子查询可以向父查询返回多行记录。在WHERE语句中使用多行子查询时，必须使用多行运算符（IN 、NOT IN、 EXISTS 、NOT EXISTS 、ALL 和 ANY等）。
例如，查询工资高于部门20中所有员工的雇员信息时，先利用子查询获得部门20中所有员工的工资，再利用父查询获得其工资大于等于这些员工的雇员信息。代码如下：

select first_name,last_name,salary,department_id
from employess
where salary>=all (select salary from employess
where department_id=20)

多列子查询

Oracle程序并没有限制子查询只能返回一列，可以编写结果集中包含多个列的多列子查询。当多列子查询返回单行数据时，在WHERE子句中可以使用单行运算符（如“=”）；当返回多行数据时，则必须使用多行运算符（如“IN”）。多列子查询一般用于多列中的数据。
例如，查询各部门中工资最低的雇员信息，先利用子查询获得各个部门的最低工资及其部门编号，再利用父查询获得与最低工资及其部门编号列表对应的雇员信息。代码如下：

select first_name,last_name,salary,department_id
from employess
where (salary,department_id) in 
(
  select min(salary),department_id from employess
  group by department_id
)
 order by department_id

相关子查询

相关子查询是指引用了父查询中某些表或某些列的子查询。在相关子查询中，父查询所处理的每一行都先被传递给子查询。子查询依次处理这些行，即将其应用到子查询，如果满足子查询中的条件，则父查询中的这一行就是最终结果集中的一行，直到父查询中的每一行都处理完成为止。一般可以在SELECT或WHERE语句中使用相关子查询。
例如，查询负责管理其他雇员的管理员信息时，父查询提供一个雇员编号（employee_id）给子查询，子查询利用该雇员编号查询是否存在一个管理者（manager_id）与父查询提供的雇员编号（employee_id）对应。若存在，则说明employee_id就是管理者，将其显示出来。代码如下：

select employee_id, first_name,last_name,department_id
from employess a
where exists
(
    select * from employess b where b.manager_id=a.employee_id
)
 order by department_id,employee_id