本文章主要内容
1、表的管理：创建表，修改表结构，删除字段，修改字段，添加字段，删除表，添加表约束；
2、数据管理：新增记录，修改记录，删除记录，查询数据；
3、查询数据：普通程序，条件查询，各种查询条件的灵活应用；
4、多表连接：内连接，左外连接，右外连接，完全连接，自然连接，交叉连接；
5、子查询；
目标
•
SQL
语言分类
DDL
DML
DCL
TCL
•
Oracle
中的数据类型
•
Oracle
数据定义语言
•
表完整性与约束
•
Oracle
操纵语言
SQL
•
SQL
是
Structured Query Language
（结构化查询语言）的首字母缩写词
•
•
SQL
是数据库语言，它可以对数据库中的数据进行查询、插入、更新和删除，并对数据库中的对象进行控制。
•
•
SQL
已经成为
RDBMS
的标准语言，最新标准是
ANSI-99
，并支持面向对象的数据库。
•
•
关系型数据库系统对
SQL
也进行了一些扩展，使之成为过程性语言，如
oralce
中的
PL/SQL
与微软的
Transact-SQL
SQL分类
SQL 支持下列类别的命令：
数据定义语言（DDL）
数据操纵语言（DML
）
事务控制语言（TCL
）
数据控制语言（DCL）
Oracle 数据类型
创建表时，必须为各个列指定数据类型
以下是
Oracle
数据类型的类别：
| 字符类型 | CHAR | 固定长度的字符串，列长度可以是 1 到 2000 个字节 |
| VARCHAR2 | 支持可变长度字符串,大小在1至4000个字节范围 | |
| LONG | 存储可变长度字符数据,最多能存储 2GB | |
| 数值类型 | NUMBER | NUMBER [( p[, s])]，存储整数、浮点数和实数，最高精度为 38 位 |
| 日期类型 | DATE | 存储日期和时间部分，精确到整个的秒 |
| TIMESTAMP | 存储日期、时间和时区信息，秒值精确到小数点后6位 | |
| RAW | RAW | 存储二进制数据，最多能存储 2000 字节 |
| LONG RAW | 存储可变长度的二进制数据，最多能存储 2 GB | |
| 大对象 LOB | LOB 类型可以存储 4GB内容，LOB 数据类型允许对数据进行高效、随机、分段的访问 | |
| CLOB | 即 Character LOB，它能够存储大量字符数据 | |
| BLOB | 即 Binary LOB（二进制 LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件 | |
| BFILE | 即 Binary File，用于将二进制数据存储在数据库外部的操作系统文件中 | |
| 伪列 | 伪列就像一个表列，但是它并没有存储在表中，伪列可以从表中查询，但不能插入、更新和删除它们的值 | |
| ROWID | 表中行的存储地址，可以使用 ROWID 伪列快速地定位表中的一行 | |
| ROWNUM | ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数 | |
数据定义语言
•
数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象
•
用于操纵表结构的数据定义语言命令有：
CREATE TABLE
ALTER TABLE
TRUNCATE TABLE
DROP TABLE
数据定义语言举例
创建表
CREATE TABLE DEPT (
DEPTNO NUMBER (2) NOT NULL,
DNAME VARCHAR2 (14),
LOC VARCHAR2 (13),
PRIMARY KEY ( DEPTNO )
)
修改表
修改LOC字段
ALTER TABLE DEPT MODIFY LOC VARCHAR2(20);
添加字段
ALTER TABLE DEPT ADD MGR NUMBER(4);
添加约束
ALTER TABLE DEPT ADD CONSTRAINT FK_EMP
FOREIGN KEY (MGR) REFERENCES EMP (EMPNO)
删除字段
ALTER TABLE DEPT DROP COLUMN MGR;
删除表
DROP TABLE DEPT;DML – 利用现有的表创建表
语法
CREATE TABLE <new_table_name> AS
SELECT column_names FROM <old_table_name>;
SQL> CREATE TABLE NEWEMP
AS SELECT * FROM EMP;
SQL> CREATE TABLE NEWEMP
AS SELECT EMPNO, ENAME, SAL FROM EMP;
不复制原表数据到新表
SQL>CREATE TABLE NEWEMP
AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE 1 = 2;
表完整性与约束
•
完整性
实体完整性
•
主键约束，保证数据唯一性
域完整性
•
字段规则，如性别必须是男或女，年龄在
0-200
参照完整性
•
外键约束
,
外键对应的记录必须存在
主键约束
alter table DEPT add constraint pk_DEPT primary key(deptno);
外键约束
alter table EMP add constraint fk_d_m foreign key(deptno) references DEPT(deptno);
Check 约束
Alter table emp add constraint ck_emp_sex check(sex=‘男’ or sex=‘女’)
Unique唯一约束
Alter table emp add constraint uk_emp unique(fname,lname)
数据操纵语言
数据操纵语言用于检索、插入和修改数据
数据操纵语言是最常见的
SQL
命令
数据操纵语言命令包括：
SELECT
INSERT
UPDATE
DELETE
DML – INSERT
插入日期类型的值
日期数据类型的默认格式为“
DD-MON-YY”
使用日期的默认格式
使用
TO_DATE
函数转换
INSERT INTO emp VALUES(1000,'PETER',7369,'12-5月-05',2800,1);
INSERT INTO emp VALUES(1001,‘MIKE',7369, TO_DATE('2005-10-18', 'YYYY-MM-DD'),2800,1);
INSERT INTO emp (empno,ename,sal) VALUES(1000,'PETER',2800 );
插入来自其它表中的记录
语法：
INSERT INTO <table_name> [(cloumn_list)]
SELECT column_names FROM <other_table_name>;
SQL> INSERT INTO NEWEMP
SELECT * FROM EMP;
SQL> INSERT INTO NEWEMP(EMPNO,ENAME,SAL)
SELECT EMPNO,ENAME,SAL FROM EMP;
DML – update
INSERT INTO emp VALUES(1001,'PETER',7369,'12-5月-05',2800,1);
UPDATE emp SET sal=sal+100; --所有员工工资加100
UPDATE emp SET ename=‘PETER ZHANG’ where empno=‘1001’;
UPDATE emp SET ename=‘PETER CHEN’,mgr=‘7876’,
hiredate=to_date(‘2004/10/21’,’YYYY/MM/DD’),
sal=3200,deptno=2
WHERE empno=‘1001’;
SQL> DELETE EMP WHERE deptno = 1 AND hiredate>to_date(‘2005/10/10’,‘YYYY/MM/DD’); --删除符合条件的数据
SQL> DELETE EMP --删除所有行
数据控制语言
数据控制语言为用户提供权限控制命令
用于权限控制的命令有：
GRANT
授予权限
REVOKE
撤销已授予的权限
SQL> GRANT SELECT, UPDATE ON EMP TO user;
SQL> GRANT SELECT ON EMP TO user WITH GRANT OPTION;
SQL> GRANT UPDATE(SAL, HIREDATE) ON EMP TO user;
SQL> REVOKE SELECT, UPDATE ON EMP FROM user;
SQL 操作符
Oracle
支持的
SQL
操作符分类如下：
SELECT
where子句中的运算符
| 查询条件 | 运算符 |
| 比较 | =,>,<,>=,<=,<>,!=,NOT |
| 范围 | between and,not between and |
| 集合 | In,not in |
| 字符匹配 | like,not like |
| 空值 | is null,is not null |
| 多重条件 | and,or |
Oracle中的用例表
SQL>conn sys/change_on_install as sysdba
SQL>alter user hr identified by hr account unlock; sys登录，解锁hr用户，并修改密码为hr
SQL>conn hr/hr
SQL>select table_name from user_tables;
Departments 部门信息表
Employees 雇员信息表
Jobs 职位信息表
Jobs_history 工作历史信息表
Locations 地址信息表
Regions 地区信息表
可以通过Desc tableName语句查看表的基本信息
SELECT
Order By
用于制定查询结果的排列循序，后面可以跟多个字段名，越在前面的排序优先权越高。
默认为升序,Desc(降序)和Asc（升序）
select empno,ename,sal from emp order by sal asc;
select empno,ename,sal from emp order by deptno asc,hiredate desc;
between and，not between and可以进行某一范围的查询 ,范围包含最大值和最小值 。
Select * from emp where sal between 2000 and 5000;
Select * from emp where sal not between 2000 and 5000;
in，not in是集合运算符，只要查询的值等于集合中的一个元素，表达式为真。
Select * from emp where empno in (select mgr from EMP)
Select * from emp where empno not in(7389,7367,7782)
like，not like
模糊匹配，“_”表示任意一个字符，“%”表示任意多个字符，如果字符串中有与匹配符相同的字符，需要使用逃逸字符串escape 。
select * from emp where ename like ‘S%’;
select * from emp where empno not in(7389,7367,7782);
select * from emp where ename like 'MULS#_I_' escape '#‘
And 多个条件同时成立时条件表达式才为真
select * from emp where empno>7600 and sal>2000
Or多个条件中只要有一个条件为真时条件表达式就为真
select * from emp where empno>7600 or sal>2000
连接查询
•
内连接
•
外连接
左外连接
(
LEFT JOIN
)
返回左边表的所有行
右外连接
(
RIGHT JOIN
)
返回右边表的所有行，实现
全外连接
(
FULL JOIN
)
返回连个表的所有数据
交叉连接
(
CROSS JOIN
)
返回连接表中所有数据行的笛卡尔积
CREATE TABLE DEPT (
DEPTNO NUMBER (2) NOT NULL,
DNAME VARCHAR2 (14),
LOC VARCHAR2 (13),
PRIMARY KEY ( DEPTNO )
);
CREATE TABLE EMP (
EMPNO NUMBER (4) NOT NULL,
ENAME VARCHAR2 (10),
MGR NUMBER (4),
HIREDATE DATE,
SAL NUMBER (7,2),
DEPTNO NUMBER (2),
CONSTRAINT PK_EMP
PRIMARY KEY ( EMPNO )
);
•内连接
查询所有员工及其所在的部门信息
SELECT e.*, d.DNAME from EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO AND e.SAL > 2000
查询部门信息
SELECT d.*, e.ENAME from DEPT d, EMP e WHERE e.MGR = e.EMPNO and d.deptno=e.deptno
•左外连接
查询所有部门及其部门下的员工(没有员工的部门也要查询出来)
select d.*, e.ename from dept d left join emp e on d.deptno=e.deptno
select d.*, e.ename from dept d,emp e where d.deptno = e.deptno;
查询所有部门编号大于20及其部门下的员工
select d.*, e.ename from dept d left join emp e on d.deptno=e.deptno WHERE d.deptno > 20
全外连接
SELECT e.empno,e.ename,d.dname FROM EMP e FULL JOIN dept d ON e.deptno=d.deptno
•
交叉连接
SELECT e.empno, e.ename,d.name FROM EMP e CROSS JOIN DEPT
对自身的连接查询
在Emp表中，每个雇员都对应一个经理，如何查询所有雇员及其经理的名称通过给其中一个表取不同的别名，然后使用对自身的联合查询来实现
select manager.ename as m_name, e.ename as e_name
from EMP e, EMP manager
where e.mgr = manager.empno;
查询
sales
部门和
Marketing
部门的经理信息以及经理对应的雇员信息
select d.dname , manager.ename as m_name, e.ename
from EMP e, EMP manager, DEPT d
where d.deptno = e. deptno --部门表与员工表连接
and d. deptno = manager. deptno --部门表与经理表连接
// and d.mgr = manager. empno --部门与经理的连接条件
and e.mgr = manager. empno --员工与经理连接
and (d.dname ='Sales' or d.dname ='Marketing');
select manager.ename,e.ename
from dept d inner join emp manager on d.deptno=manager.deptno
inner join emp e on e. mgr= manager.empno
where d.dname in('SALES', 'Marketing’)
order by manager.ename
替代变量
Select * from emp where deptno=&aa
替代变量的使用：&变量名
Select empno,ename,&v1 from &t1 where &w1;
Define a =10 //定义替代变量
Define a //查看变量
Select * from emp where deptno=&a;
Define //查所有替代变量
Undefine a //删除替代变量
Select empno,ename,&&a from emp where &a like ‘S%’ //重复引用
Delete与TRUNCATE
•
Delete from emp
•
Delete
是
DML
语句，将会做大量日志记录
•
TRUNCATE
emp
•
TRUNCATE
是
DDL
语句，不会做日志记录，效率会高，不能回滚
rename
重命名表、视图、序列、同义词
必须是对象的拥有者才能重名
Rename dept to new _dept
Group by with rollup and cube
用带rollup或cube的group by 实现超级聚合，即对group进行再聚合
Rollup and cube
•
Rollup
实现 从右往左再聚合
•
Cube
除了形成
rollup的结果， 还会按相反的方向形成结果
GROUPING SETS
两个group语句以union的形式组合在一起
高级子查询
成对比较
select * from emp
where (deptno,mgr) in (
select deptno,mgr from emp where job like '%CLERK%')
Where字句中的子查询
查询工资比部门平均工资高的员工
Select a.empno,a.ename,a.deptno,a.sal,b.avg_sal
from emp a, (select deptno,avg(sal) avg_sal from emp
group by deptno) b
where a.deptno = b.deptno and a.sal > b.avg_sal
标量子查询（scalar subquery）
查询员工，并显示工资等级，工资大于
4000
为白领，大于
2000
为蓝领，小于
2000
为打工仔
SQL>select empno,ename,sal, (
case
when sal>4000 then '白领'
when sal>2000 then '蓝领'
else '打工仔'
end) 工薪阶层
from emp;
查询员工，按部门名称排序
select empno,ename
from emp e
order by (select dname from dept d where e.deptno=d.deptno)
exists
•
查询有员工的部门，或有某个员工的部门
select * from dept d
where exists (
select * from emp e where e.deptno=d.deptno
)
With语句
•
With
语句定义一个变量来表示一个语句，通过引用变量来引用子句，实现重用，提高效率
•
查询部门总工资大于所有部门工资的平均值的部门
SQL>with
dept_costs AS(
select deptno,sum(sal) as dept_total
from emp
group by deptno),
avg_cost AS(
select sum(dept_total)/count(*) as dept_avg
from dept_costs)
select * from dept_costs
where dept_total>(select dept_avg from avg_cost)
order by deptno
/
1328
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
