博客

1. SQL概述

1.1  SQL语言的功能

（1）数据定义语言（DDL ）: Data Definition Language

用于建立、修改、删除数据库对象。

数据库对象包括：表、视图、索引、序列。

包括：

CREATE ：创建表或其他对象的结构

ALTER ：修改表或其他对象的结构

DROP：删除表或其他对象的结构

TRUNCATE：删除表数据，保留表结构

（2） 数据操纵语言（DML）： Data Manipulation Language

用于改变数据表中的数据。和事务相关，执行完后需要经过事务控制语句提交后才能真正的将改变应用到数据库中。

包括：

INSERT：将数据插入到数据表中

UPDATE：更新数据表中已存在的数据

DELETE：删除数据表中的数据

（3） 事务控制语言（TCL）： Transaction Control Language

用来维护数据一致性的语句。

包括：

COMMIT：提交，确认已经进行的数据改变

ROLLBACK：回滚，取消已经进行的数据改变

SAVEPOINT：保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变

（4）数据查询语言（DQL）： Data Query Language

用来查询所需要的数据。

SELECT语句

（5）数据控制语言（DCL）： Data Control Language

用于执行权限的授予和收回操作。

包括：

GRANT：授予，用于给用户或角色授予权限

REVOKE：用于收回用户或角色已有的权限

CREATE USER：创建用户

1.2  SQL的特点

• 功能强大
• 集合操作
• 非过程化
• 语言简洁
• 具有交互式和嵌入式两种形式
• 支持三级模式结构
  • 全体基表构成了数据库的模式
  • 视图和部分基表构成了数据库的外模式
  • 数据库的存储文件及其索引文件构成了关系数据库的内模式

1.3  SQL 语句的编写规则

SQL关键字

不区分

大小写

SELECT id,name FROM emp;

select id,name from emp;

对象名和列名

不区分

大小写

select id,name from emp;

select ID,NAME from emp;

字符值和日期值

区分

大小写

select id, name from emp where name=’TOM’;

select id, name from emp where name=’tom’;   两句查询的结果不同

2.  数据定义

2.1  CREATE

1.  创建表

CREATE TABLE <表名>(<列名><数据类型>)[列级完整性约束条件],

[  <列名><数据类型>)[列级完整性约束条件]],   …

[  <表级完整性约束条件>]);

 CREATE TABLE employee(
   id NUMBER(4) not null unique,
   name VARCHAR2(20) NOT NULL,
   gender CHAR(1) DEFAULT 'M',
   birth DATE,
   salary NUMBER(30),
   job VARCHAR2(30),
   deptno NUMBER(2)
 );

2.  创建视图

CREATE VIEW <视图名>[(<列名>[,<列名>]…)]

AS<子查询>

[WITH CHECK OPTION]

with check option

表示对视图进行update、insert、delete操作时，要保证更新，插入或删除的行满足视图定义的谓词条件（即子查询中的条件表达式）

在输入视图的属性列名时 ，要么全部省略，要么全部指定。

以下情况必须明确指定

：

• 目标列存在集函数或列表达式时，需要指定列名
• 多表连接时存在几个同名列作为视图的手段，需要指定不同的列名
• 某个列需要重命名

create view prog_emp
as 
    select id,name,email,phone
    from emp
    where id=1
with check option;

3.  创建索引

CREATE[UNIQUE][CLUSTER] INDEX<索引名>

ON<表名>(<列名>[<次序>]  [  ,


<列名>[<次序>]]…


);

cluster表示要创建的索引是聚簇索引

(

指

索引项的顺序

与表中

记录的物理顺序

相一致的索引组织

)

CREATE INDEX IT_LASTNAME
ON IT_EMPLOYEES(LAST_NAME);

2.2  DROP

1.  删除表

DROP TABLE <表名>;

drop table emp;

删除基表定义后，表中的数据、在该表中建立的索引都将自动被删除掉。

在oracle中，删除后在此表上定义的视图仍然会保留在数据字典中，但当用户引用该视图时会报错

2.  删除视图

DROP VIEW <视图名>;

由该视图导出的其他视图的定义仍在数据字典中，不会被删除，这将导致用户在使用相关视图时会发生错误

3.  删除索引

DROP INDEX <索引名>;

2.3  ALTER

ALTER TABLE<表名>

[ADD<新列名><数据类型>[完整性约束]]

[DROP<完整性约束>]

[MODIFY<列名><数据类型>];

3.  数据查询

select语句执行顺序：

1. from子句，组装来自表的数据，有可能是多张表
2. where子句，基于指定的条件对记录行进行筛选
3. group by子句，将数据划分为多个分组
4. 使用聚合函数对每个小组中的数据进行计算
5. having子句，进行条件筛选，这里可以使用聚合函数的计算结果
6. 计算所有的运算表达式，主要是select部分
7. order by子句，对结果集进行排序

首先由from子句找到数据表，where子句则接收from子句输出的数据，而having子句则接收来自group by、where或from子句的输入

简单查询：

1. 使用from子句指定表  from子句指定查询中包含的行和列所在的表
2. 使用select指定列   各列使用,隔开
3. 算术表达式  + – * / ()      如果列标题中含有一些特殊的字符，则必须使用双引号括起来
4. distinct关键字    删除结果集中重复的行
5. where子句
   1. 条件表达式  = > < != <> like (%  _ )  not
   2. 连接运算符 and or
   3. null值   is null    is not null
6. order by子句  ASC升序   DESC降序(默认)
7. group by子句 在查询结果集中对记录进行分组，用以汇总数据或者为整个分组显示单行的汇总信息
8. having子句  通常与group by子句一起使用，在完成对分组结果统计后，可以使用having子句对分组的结果做进一步的筛选。若不使用group by子句，having子句的功能与where子句一样。having与组有关，where与单个的行有关

多表连接查询

1. 简单连接   查询的结果是一个通过笛卡尔积所生成的表，行数是两个基表行数的积

2. join连接

内连接

inner join  on

自然连接

会将第一个表中的列与第二个表中具有相同名称的列进行连接

外连接

左外连接(left outer join 或 left join)、右外连接(right outer join 或right join) 、全外连接(full outer join 或 full join)

自连接

是在from子句中两次指定了同一个表，为了在其他字句中区分，分别指定了表别名，将两个表看作是两个数据源

集合操作

是将两个或多个SQL查询结果合并成复合查询。

1. UNION (并运算)

union可以将第一个查询中的所有行与第二个查询中的所有行相加，并消除其中重复的行形成一个合集

2. UNION ALL

union all与union语句的工作方式基本相同 ，不同之处是union all形成的结果集中会包含两个子结果集中重复的行。

3. INTERSECT (交运算)

也会对两个SQL语句所产生的结果集进行处理，只不过是一个交集运算

4. MINUS (差运算)

可以找到两个给定的集合之间的差集，该操作符会返回所有从第一个查询中返回的，但是没有在第二个查询中返回的记录。

子查询

子查询是一个select语句，他可以在select、insert、update或delete语句中使用

EXISTS后面跟一个子查询，当该子查询可以查询出至少一条记录时，则EXISTS表达式成立并返回true。

4.  数据操纵

4.1  INSERT语句

insert语句用于完成各种向数据表中插入数据的功能，既可根据对列赋值一次插入一条记录，也可根据select查询子句获得的结果记录集批量插入指定数据表。

#例如：
INSERT INTO myemp
    (id,name,salary,dempno)
    VALUES(1,'jian',6000,10)
    SELECT * FROM myemp
    COMMIT

4.2  UPDATE语句

修改表中的数据。

修改表中的数据需要添加

WHERE

过滤条件，这样会将只满足条件的记录进行修改，否则将会把所有的数据都修改。一次也可以修改多个字段。

UPDARE myemp
SET salary=6000,name='jian',gender='F'
WHERE id=1

4.3  DELETE语句

删除表中数据

1. 如果没有

WHERE

子句，

则全表的数据都会被删除

2. DDL语句中的TRUNCATE语句，同样有删除表数据的作用

4.4  TRUNCATE语句

删除表中的所有记录，不会产生回滚信息，因此不能撤销

可以使用关键字 REUSE STORAGE 表示删除记录后仍然保存记录占用的空间  默认使用

DROP STORAGE  表示删除记录后立即回收记录占用的空间

DELETE与TRYNCATE的区别

(1）DELETE可以有条件删除，TRUNCATE将表数据全部删除。

（2）DELETE是DML语句，可以回退，TRUNCATE是DDL语句，立即生效，无法回退。

（3）如果是删除全部表记录，且数据量较大，DELETE语句效率比TRUNCATE语句低。

5.  数据控制

5.1  GRANT语句

使用grant语句向用户授予操作权限，一般格式为

GRANT <权限>[ , <权限> ]…

GRANT  INSERT

[ on <对象类型><对象名>]

ON TABLE Dep

TO<用户>[ , <用户> ]…

TO User  WITH GRANT OPTION;

[ WITH GRANT OPTION ]

将指定操作对象的指定操作权限授予指定的用户

注：如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限在授予其他用户，若没有，则该用户就只能使用该权限

5.2  REVOKE语句

授予的权限可以由DBA或者授权者使用REVOKE回收

REVOKE<权限>[ , <权限>]…

REVOKE UPDATE(EMP_ID)

[ON  <对象类型><对象名>]

ON TABLE IT_EPM

FROM<用户> [,<用户>]…;

FROM User;

6.  Oracle常用函数

6.1  字符类函数

6.2  数字类函数

6.3 日期类函数

6.4  转换类函数

6.5  聚集类函数