2019-12-10-SQL部分知识点回顾

select语句的应用

多表查询

​ 多表查询允许在查询的FROM条件后面跟多个表，然后把表之间的关系在WHERE条件后进行连接。

		Select A.fee_name 
		from 收费项目表A，收费项目目录表B  
		where A.fee_id = B.fee_id

外连接

​ 在多表查询的实际引用中，我们可能需要列出一个表的全部记录，于是引入外连接，通过在等式连接的一边加上（+）来表示外连接，（+）放在取全部目录的表的另一边。

	Select A.student_id,A.student_name,B.dep_id 
	From 学生信息表A，部门表B
	Where A.dep_id=B.dep_id(+)   //左连接
	
	Select A.student_id,A.student_name,B.dep_id 
	From 学生信息表A
		Left join 部门表B 
			On A.dep_id=B.dep_id(+)   //左连接

ex:
内连接：inner join 表名 on 连接条件

​ 外连接：
​ 左外连接：左表 left outer join 右表 on 连接条件 左表全部，若右表无值则为null
​ 右外连接：左表 right outer join 右表 on 连接条件 同上，反之

​ 自连接：自己和自己表查询

子查询

​ 将子查询或in或exists当成where条件的一部分，这样的查询称为子查询；

​ where中可以包含一个select语句的子查询；

​ where中可以包含in，exists语句

	Select fee_id 
	From 收费项目表
	Where fee_id 
		In （select fee_id from 收费项目表）

嵌套查询

​ select查询语句里可以嵌入select查询语句，称为嵌套查询；

最多嵌套16层，层次过多会影响性能。

	Select rownum as 序号，名称，实际数量 
	From （select A.名称 as 药品名称，SUM(nvl（b.实际数量，0））as 实际数量 
			From 收费项目目录A，药品库存B
			Where A.ID = B.药品ID group by a.名称）
			Order by 实际数量

分组查询

​ SQL无法把正常的列和汇总函数结合在一起时，需用GROUP BY子句。

GROUP BY子句可以对select的结果进行分组后再应用汇总函数。

HAVING子句允许将汇总函数作为条件，使用在查询语句中。

	Select name，SUM（fee）
	From 费用记录表 
	Where rownum < 7
	Group by name
		Having SUM（fee）> 5

补充：
		SQL分组GroupBy+Having
		group by + having 用来分组查询后指定一些条件来输出查询结果
		having 和 where 一样，但 having 只能用于 group by
		
		-- 查询工资总和大于 9000的部门名称
		SELECT deparmant, GROUP_CONCAT(salary), SUM(salary) FROM employee 
		GROUP BY deparmant 
		HAVING SUM(salary) > 9000;
		having 和 where 的区别：
		having 是在分组后对数据进行过滤，where 是在分组前对数据进行过滤
		having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数
		where 是对分组前记录的条件，如果某行记录没有满足where字句的条件，那么这行记录不会参加分组；而having是对分组后数据的约束
		
		-- 查询工资大于2000的，工资总和大于9000的部门名称以及工资和
		select deparmant,GROUP_CONCAT(salary), SUM(salary) from employee 
		WHERE salary > 2000 
		GROUP BY deparmant 
		HAVING sum(salary) > 9000
		ORDER BY SUM(salary) DESC;

select语句优化查询的两种方法

	Oracle 的解析器从右到左的顺序处理from子句中的表名（最右边的表为基础表）
	故，3张表以上的连接查询选择交叉表作为基础表
	
	用EXISTS代替IN
	SELECT * 不利于后期维护，如添加字段等

DML数据操纵语句

insert

	Insert into 表名（字段名1，字段名2，…） values（值1，值2，…）
	Insert into 表名（字段名1，字段名2，…）select 字段名1，字段名2，…from 其它表名

update

	Update 表名 set 字段名1 = 值1 ，字段名2 = 值2 ，… where 条件
	/*eg：*/
	Update 人员表 set 年龄 = 28 where name = ‘张三’

delete

	DELETE FROM table_name [WHERE Clause];

补充：
	DELETE FROM table_name [WHERE Clause];
	-- 如果不指定条件，那么DELETE将会删除表中所有值
	--TRUNCATE命令可以释放占用的数据表空间，不可恢复
注意：
	以上语句操作完成后，必须加上事物处理结束的命令COMMIT才能正式生效；
	若果需要撤回这些操作，可以使用命令ROLLBACK复原

DDL数据定义语句

创建表create

	CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#图书名
	price DOUBLE,#价格
	authorId INT,#作者编号
	publishDate DATETIME#出版日期);
	/*ex：库的创建语法：*/
	create database [if not exists]库名;
	/*eg：创建库Books*/
CREATE DATABASE IF NOT EXISTS books ;

修改表alter

	/*表的修改
	语法*/
	alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
	/*修改列名*/
	ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
	/*修改列的类型或约束*/
	ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
	/*添加新列*/
	ALTER TABLE author ADD COLUMN annual DOUBLE;
	/*删除列*/
	ALTER TABLE book_author DROP COLUMN annual;
	/*修改表名*/
	ALTER TABLE author RENAME TO book_author;

	/*库的修改*/
	RENAME DATABASE books TO 新库名;
	/*更改库的字符集*/
	ALTER DATABASE books CHARACTER SET gbk;

删除表\库drop

	DROP TABLE IF EXISTS book_author;
	DROP DATABASE IF EXISTS 旧库名;
	/*CREATE DATABASE 新库名;*/

序列sequence

用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。创建序列:CREATE SEQUENCE 序列号的名称（建议：表名+序列号记）

	/*eg:*/
	create sequence student_id
	minvalue 1
	maxvalue 999
	start with 1
	increment by 1
	nocycle
	nocache;
	commit;

DCL数据控制语句

用来定义访问权限和安全级别

三种系统权限

CONNECT：拥有Connect权限的用户只可以登录ORACLE，不可以创建实体，不可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
	补充：
		connect 权限：分配给普通用户；
		该权限具有：
		alter session —— 修改会话；
		create cluster —— 创建聚簇；
		create database link —— 创建数据库连接；
		create sequence —— 创建序列；
		create session —— 创建会话；
		create synonym —— 创建同义词；
		create view —— 创建视图；
		
		resource 权限：分配给设计人员；
		该权限具有：
		create cluster —— 创建聚簇；
		create procedure —— 创建过程；
		create sequence —— 创建序列；
		create table —— 创建表；
		create trigger —— 创建触发器；
		create type —— 建类型；

六种数据对象权限

ALL ON 数据对象名
SELECT ON 数据对象名
UPDATE ON 数据对象名
DELETE ON 数据对象名
INSERT ON 数据对象名
ALTER ON 数据对象名

GRANT：赋予权限

GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

REVOKE：收回权限

REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用户名1, 用户名2;