04-SQL基础（表管理，约束，多表连接，子查询）

本文章主要内容

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_EMPFOREIGN KEY (MGR)  REFERENCES EMP (EMPNO)
删除字段
ALTER TABLE DEPT DROP COLUMN MGR;

删除表

DROP TABLE DEPT;

DML – 利用现有的表创建表

语法CREATE TABLE <new_table_name> ASSELECT column_names FROM <old_table_name>;

SQL> CREATE TABLE NEWEMPAS SELECT * FROM EMP;SQL> CREATE TABLE NEWEMPAS SELECT EMPNO, ENAME, SAL FROM EMP;

不复制原表数据到新表

SQL>CREATE TABLE NEWEMPAS 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 NEWEMPSELECT * 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;        --所有员工工资加100UPDATE 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>2000Or多个条件中只要有一个条件为真时条件表达式就为真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_EMPPRIMARY 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 managerwhere e.mgr = manager.empno;

查询sales 部门和Marketing部门的经理信息以及经理对应的雇员信息

select  d.dname , manager.ename as m_name, e.enamefrom EMP e, EMP manager,  DEPT  dwhere  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.deptnoinner 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 empwhere (deptno,mgr) in (select deptno,mgr from emp where job like '%CLERK%')Where字句中的子查询查询工资比部门平均工资高的员工Select a.empno,a.ename,a.deptno,a.sal,b.avg_salfrom emp a, (select deptno,avg(sal) avg_sal from empgroup by deptno) bwhere a.deptno = b.deptno and a.sal > b.avg_sal

标量子查询（scalar subquery）

查询员工，并显示工资等级，工资大于4000为白领，大于2000为蓝领，小于2000为打工仔

SQL>select empno,ename,sal, (casewhen 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 dwhere exists (select * from emp e where e.deptno=d.deptno)

With语句

•With语句定义一个变量来表示一个语句，通过引用变量来引用子句，实现重用，提高效率

•查询部门总工资大于所有部门工资的平均值的部门

SQL>with
dept_costs AS(select deptno,sum(sal) as dept_totalfrom empgroup by deptno),
avg_cost AS(select sum(dept_total)/count(*) as dept_avgfrom dept_costs)
select * from dept_costs
where dept_total>(select dept_avg from avg_cost)
order by deptno
/

本人从事软件项目开发20多年，2005年开始从事Java工程师系列课程的教学工作，录制50多门精品视频课程，包含java基础，jspweb开发，SSH,SSM，SpringBoot，SpringCloud,人工智能，在线支付等众多商业项目，每门课程都包含有项目实战，上课PPT，及完整的源代码下载，有兴趣的朋友可以看看我的在线课堂

讲师课堂链接：https://edu.csdn.net/lecturer/893