SQL学习笔记

一、SQL学习笔记

1.SQL语言概述

SQL：Structured Query Language
功能：查询、定义、操纵、控制
特点: 1、综合统一
2、高度非过程化
3、面向集合的操作方式
4、SQL可作为独立使用的语言又可作为嵌入式语言使用
5、语言简捷,易学易用

二、掌握数据定义语言DDL

1.样本数据库

emp:

dept:

salgrade:

2.create table语句

--该语句的一般格式如下：
	CREATE TABLE [schema.]table
	( { column DATATYPE 
	[DEFAULT expn] [COLUMN_CONSTRAINT] |TABLE_CONSTRAINT }
	[,{ column DATATYPE
	[DEFAULT expn] [COLUMN_CONSTRAINT] |TABLE_CONSTRAINT }]…);
	其中：
	schema   包括基表的模式（缺省：当前用户的帐号）
	table    表名
	column   列名
	DATATYPE 列数据类型
	DEFAULT  当前列的缺省值（常数）
	COLUMN_CONSTRAINT 列约束
	TABLE_CONSTRAINT  表约束

建表：

CREATE TABLE EMP 
  ( 
    EMPNO NUMBER(4) PRIMARY KEY, 
    ENAME VARCHAR2(10) NOT NULL, 
    JOB VARCHAR2(9), 
    MGR NUMBER(4), 
    HIREDATE DATE, 
    SAL NUMBER(7, 2) CHECK(SAL>100), 
    COMM NUMBER(7, 2) DEFAULT 0.0, 
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT 
  );

复制一个表：

CREATE TABLE EMP2 AS SELECT * FROM EMP;

参照一个已存在的表建立一个表结构（无数据）：

CREATE TABLE EMP3 AS SELECT EMPNO,ENAME,JOB,MGR,
		HIREDATE,SAL,COMM,DEPTNO 
	 FROM EMP WHERE ROWNUM<1;

3.alter table语句

--增加列
alter table dept add (headcount number(3));

--修改列
alter table dept modify(dname varchar(15));

--删除列
alter table dept drop column headcount;


下面还未实现：
--添加主键
alter table dept add(pk_dept primary key(deptno));

--改变主键，使主键无效
alter table dept disable scott.pk_dept;

--删除主键
alter table dept drop constraint pk_dept;

--添加外键 ，修改、删除外键形式同修改、删除主键。
alter table dept 
      add constraint fk_emp_deptno foreign key(deptno)
          references dept(deptno);

4.drop table 语句

DROP TABLE用于删除一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消，所以使用本语句要小心！

三、掌握数据查询语言DQL

1.select

语法

SELECT [DISTINCT|ALL] {*|column1[,column2]…}
   FROM {table1|(subquery)} [alias]
   [,{table2|(subquery)} [alias]]…
   [WHERE condition]
   [GROUP BY expn] [HAVING expn]
   [{UNION[ALL]|INTERSECT|MINUS} SELECT…]
   [ORDER BY expn] [ASC|DESC]

2.无条件查询

--列出所有部门信息
select * from emp;

--列出员工表员工编号、员工姓名、薪水信息
select empno,ename,sal from emp;

--列出员工表中不重复的职位
select distinct job from emp;
	 
--列出员工表员工姓名、年薪、职位，并设置其对应别名
select ename as 姓名,12*sal as 年薪,job as 岗位 from emp;

--列出员工表员工姓名、薪水，要求显示的格式为：XXXX员工的 薪水为XXX
select ename||'员工的薪水为'||sal FROM emp;

3.有条件查询

常用的比较条件：

操作符	用途	例子
=	等于	select * from emp where deptno=10;
>=	大于等于	select * from emp where sal>=5000;
>	大于	select * from emp where sal>5000;
<=	小于等于	select * from emp where sal<=5000;
<	小于	select * from emp where sal<5000;
<>或!=	不等于	select * from emp where deptno<>10;

操作符	用途
[not] in	判断某值是否在指定的结果集中
any	将一个值和一组值进行比较，返回满足条件的结果。必须更跟！=、>、<、>=、<=
some	通any
all	将一个值与一组值比较，返回满足条件的所有列值。必须跟!=,<,>,<=,>=
[not] between…and	判断某值是否界于两者之间
[not] exists	判断某个列是否存在于一组值中
[not] like	比较两个模式是否相似
is [not] null	判断是否为空
not	对结果否定
and	判断两个条件是否都满足
or	判断两个条件中是否有一个满足

--求部门号为10且工资大于2000的员工姓名及工资
 select ename,sal from emp 
 where deptno=10 and sal>2000;
	 
--求部门号为10，20或30的员工全部信息
 select * from emp where deptno in(10,20,30);
	 
--求工资在2000到3000的员工姓名及工资
 select ename,sal from emp 
 where sal between 2000 and 3000;
	 
--求职位最高（即没有主管mgr）的员工全部信息
 select * from emp where mgr is null;
	 
--求员工姓名以字母S开头的所有员工
--通配符:%表示单个或多个字符序列，_表示单个字符。
 SELECT * FROM emp where ename LIKE 'S%';

4. 常用系统函数

--字符函数
--1.CONCAT<c1,c2>：拼接两个字符串，与||相同
SELECT CONCAT('0592-','8063123')||'转25' 张三电话 FROM dual;

--2.LENGTH(<c>)/LENGTHB(<c>)返回字符串c的长度/字节数
SELECT LENGTH('ABC好EF'),LENGTHB('ABC好EF') FROM dual;

--3.LTRIM（左截断）、RTRIM（右截断）函数
--  LTRIM(string [,’set’]去掉左边出现的任何字符
--  RTRIM(string [,’set’]去掉右边出现的任何字符
SELECT LTRIM(' ABCDEF'),RTRIM('ABCDEF ') FROM dual;

--4.SUBSTR(string,start[,count])取子字符串函数
--  对字串（或字段），从start字符开始，连续取count个字符
SELECT SUBSTR('ABCDEF',2,3) FROM dual;

--5.REPLACE(‘string’[,‘string_in’,’string_out’])
--  将字符串string中出现string_in字串替换为string_out
SELECT REPLACE('Informaix 中国公司','Informaix','IBM  Informaix') FROM dual;

--6、TRIM可以对给定字符串进行裁剪（前面、后面或前后）
select trim(' ABCDEF ') FROM dual;

--日期函数
--1.SYSDATE
SELECT SYSDATE FROM dual;

--转换函数
--1.TO_CHAR
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual;

--2.TO_DATE
SELECT TO_DATE('30-10月-2009') FROM dual;

--3.TO_NUMBER
SELECT TO_NUMBER('3333333') FROM dual;

--其他函数
--USER 查看当前的登录账号
SELECT USER FROM dual;

5.聚集函数

  1、COUNT：统计行数或不为NULL的列数
     语法：
     COUNT[（*）|（DISTINCT|ALL|]COLUMN NAME）
  2、SUM：求和
     语法：
     SUM（ [DISTINCT] COLUMN NAME）
  3、MAX和MIN：求最大值和最小值
     语法：
	 MAX（ [DISTINCT] COLUMN NAME ）
	 MIN（ [DISTINCT] COLUMN NAME ）
  4、AVG：求平均值
	 语法：
	 AVG（[DISTINCT]  COLUMN NAME）

--聚集函数示例
--查找公司的总人数：
SELECT COUNT(*) FROM emp;

--查找公司的工资总和：
SELECT SUM(sal) FROM emp;

--查找公司的最高工资：
SELECT MAX(sal) FROM emp;

--查找公司的最低工资：
SELECT MIN(sal) FROM emp;

--查找公司的平均工资：
SELECT AVG(sal) FROM emp;

6.其他函数

--其他函数
--USER 查看当前的登录账号
SELECT USER FROM dual;

--NVL
 --如果expn1的计算结果为 null 值，则返回 expn2。
 --如果 expn1的计算结果不是 null 值，则返回 expn1。
 --如果expn1与expn2的计算结果都为null，则返回null值。
SELECT ename,NVL(TO_char(comm),'空值')  FROM emp;

--DECODE
--语法： DECODE(条件, 值1, 翻译值1, 值2, 翻译值2, ...值n, 翻译值n, 缺省值)
--等同于如下SQL语句:
	IF 条件=值1 THEN
		RETURN(翻译值1)
ELSIF 条件=值2 THEN
		RETURN(翻译值2)
......
ELSIF 条件=值n THEN
		RETURN(翻译值n)
ELSE
		RETURN(缺省值)
END IF

select ename , decode (sal,1000,'D',2000,'C',3000,'B',4000,'A','Other') sal from emp;

7.子查询

--求工资比SCOTT高的员工
 SELECT ename,sal FROM emp
 WHERE sal>(SELECT sal FROM emp WHERE ename='SCOTT');
 
--求员工表中已有的部门的部门号、部门名称
 SELECT deptno,dname FROM dept d
 WHERE EXISTS
 (SELECT * FROM emp e WHERE d.deptno=e.deptno);
 
--求比部门号为10的员工中工资最低的还要低的员工
 SELECT ename,sal FROM emp
 WHERE sal<ANY(SELECT sal FROM emp WHERE deptno=10);
 
--求比部门号为10的员工中工资最高的还要高的员工
 SELECT ename,sal FROM emp
 WHERE sal<ALL(SELECT sal FROM emp WHERE deptno=10);
  SELECT ename,sal FROM emp
 WHERE sal>ALL(2000,3000,3500);

All：只有当其所有数据都满足条件时，条件才成立
Any：只要有一条数据满足条件，条件就成立
any的用法：

<any意味着小于最大、>any大于最小
all的用法：

<all：小于所有，即小于最小、> all：大于所有，即大于最大

8.分组查询

--求每个部门的总工资
 SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
 
 --求最大总工资的部门
SELECT deptno,sum(sal) FROM emp
 GROUP BY deptno
 HAVING sum(sal) =
(SELECT MAX(sum(sal)) FROM emp GROUP BY deptno);
 
 --求每个部门的平均工资
 SELECT deptno,avg(sal) FROM emp GROUP BY deptno;

---求最大平均工资的部门
 SELECT deptno,AVG(sal) FROM emp
 GROUP BY deptno
 HAVING AVG(sal) =
(SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno);
	
--求每个部门工资高于2000的员工的总工资，要求只显示总工资高于5000的，按总工资降序排列
 SELECT deptno,SUM(sal) FROM emp 
 WHERE sal>2000 
 GROUP BY deptno HAVING sum(sal)>5000
 ORDER BY SUM(sal) DESC; 

9.连接查询

--内连接，只返回满足连接条件的数据。
--查看部门号为10的员工的工资级别
 SELECT empno,sal,s.grade FROM emp e,salgrade s 
 WHERE deptno=30 AND sal BETWEEN s.losal AND s.hisal;
 
--左外连接，包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），
--以及右边表中全部匹配的行。

--所有员工及对应部门的记录，包括没有对应部门号的员工记录
  SELECT e.ename 姓名, d.dname 部门名称 FROM emp e 
  LEFT OUTER JOIN dept d ON e.deptno = d.deptno;
--等价于
  SELECT e.ename 姓名, d.dname 部门名称
  FROM emp e,dept d
  WHERE e.deptno=10 AND e.deptno = d.deptno(+);
  
--右外连接，包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），
--以及左边表中全部匹配的行。
--所有员工及对应部门的记录，包括没有任何员工的部门记录
  SELECT e.ename 姓名, d.dname 部门名称 FROM emp e 
  RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;
--等价于
  SELECT e.ename 姓名, d.dname 部门名称
  FROM emp e,dept d
  WHERE e.deptno=10 AND e.deptno(+) = d.deptno;
  
--完全外连接，包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
--所有员工及对应部门的记录，包括没有对应部门号的员工记录和没有任何员工的部门记录
   SELECT e.ename 姓名, d.dname 部门名称 
   FROM emp e 
   FULL OUTER JOIN dept d ON e.deptno = d.deptno;

10.集合操作

--集合操作
--UNION：用于返回两个查询中所有唯一的行；UNION ALL则表示返回所有行（不管是否重复）。
--查找出部门号10和部门号20的员工姓名
 SELECT ename 姓名 FROM emp WHERE deptno=10
 UNION
 SELECT ename 姓名 FROM emp WHERE deptno=20;
 
--INTERSECT：用于返回两个查询中相同的行。
--查询emp表和dept表中同时出现deptno的记录
   SELECT deptno  FROM dept
   INTERSECT
   SELECT deptno  FROM emp;
   
--MINUS：用于返回查询中不同的行。
--查询dept表中deptno不出现在emp表中的记录
   SELECT deptno  FROM dept
   MINUS
   SELECT deptno  FROM emp;

11.排序

--排序
--用 ORDER BY子句进行行的排序
--ASC: 升序, 缺省
--DESC: 降序

--缺省升序排序
 SELECT ename, job, deptno, hiredate
 FROM emp ORDER BY hiredate;
 
--按照列的别名来进行排序
 SELECT empno, ename, sal*12 annsal
 FROM emp ORDER BY annsal DESC;
 
--先按照部门升序排序，部门相同的员工按照工资降序排序
 SELECT ename, deptno, sal
 FROM emp ORDER BY	deptno, sal DESC;

四、掌握数据操纵语言DML

1.INSERT语句

一般格式

   INSERT  INTO [schema.]table_name [@db_link] [(column1[,column2]…)]
   VALUES (express1[,express2]…|subquery);

实例

--用文字插入操作
   INSERT INTO dept 
   VALUES(50,'PRODUCTION','SAN FRANCISCO');
	 
select * from dept order by deptno asc;
	
INSERT INTO emp(empno,ename,job,sal,comm,deptno)
   VALUES(7890,'LINKS','CLERK',1.2E3,NULL,40);

select * from emp;

--日期的插入
   INSERT INTO emp(empno,ename,job,hiredate,sal)
   VALUES(7890,'LINKS','CLERK',
          TO_DATE('2009-10-30','yyyy-mm-dd'),1.2E3);

2.UPDATE语句

一般格式

   UPDATE [schema.]table_name[@db_link] [alias]
   SET {column1=express1[.column2=express2]…|
       (column1[.column2]…)=(subquery)}
   [WHERE condition];
   

实例

--更新					
--一般的更新
UPDATE emp SET comm=800 WHERE empno=7784;

--一般的修改
UPDATE emp SET comm=800 WHERE empno=7784;
 
--带null修改
UPDATE emp SET comm=null WHERE empno=7654;
 
--用文字修改
UPDATE emp SET job='MANAGER',sal=sal+1000,deptno=20
WHERE ename='JONES';
 
--用查询修改
UPDATE emp e1 SET e1.sal=e1.sal+300
WHERE e1.empno IN(SELECT e2.empno FROM emp e2 
		 WHERE e2.job='SALESMAN');

3.DELETE语句

一般格式

DELETE [FROM] [schema.]table_name [@db_link] [alias]
[WHERE condition];

实例

--删除
	 
--例1：
DELETE FROM bonus;

--例2：
DELETE FROM emp WHERE JOB=‘SALESMAN’ AND comm<100;

--例3：
DELETE FROM(SELECT * FROM emp)
WHERE JOB=‘SALESMAN’ AND comm<100;

五、掌握数据控制语言DCL

1.GRANT语句

定义

授权：将对指定操作对象的指定操作权限授予指定的用户
语法：
   GRANT <权限>[，<权限>]…
	   [ON<对象类型> <对象名>]
	   TO <用户>[, <用户>]…
	   [WITH GRANT OPTION];
权限:
SELECT,INSERT,UPDATE,DELETE; CREATE,ALTER,DROP;
对象类型:TABLE,DATABASE
对象:属性列,视图,基本表,数据库

实例

--GRANT

--把查询emp表权限授给用户U1
 grant select on table emp to u1;
 
--把查询dept表权限授给所有用户
 GRANT SELECT ON TABLE dept TO PUBLIC;
 
--把对表dept的INSERT权限授给用户U2,并允许他转授
 GRANT INSERT ON TABLE dept 
 TO U2 WITH GRANT OPTION;
 GRANT INSERT ON TABLE dept TO U3

2.REVOKE语句

定义

收回权限：将对指定操作对象的指定操作权限授予指定的用户
语法：
   REVOKE <权限>[，<权限>]…
   [ON<对象类型> <对象名>]
   FROM <用户>[, <用户>]…;

实例

--REVOKE

--把所有用户查询dept表权限收回
 REVOKE SELECT ON TABLE dept FROM PUBLIC;
	 
--把用户U1修改emp表的ename字段的权限收回
 REVOKE UPDATE(ename) ON TABLE emp FROM U1;

六、掌握事务处理控制命令

COMMIT
--使用commit可以提交所有没提交的事务
--可以把上次提交以来的事务提交
SAVEPOINT
--使用rollback命令回滚到一个指定的保存点
ROLLBACK
--可以回滚所有没提交的事务
--已经提交的事务不能再回滚

的INSERT权限授给用户U2,并允许他转授
GRANT INSERT ON TABLE dept
TO U2 WITH GRANT OPTION;
GRANT INSERT ON TABLE dept TO U3

### 2.REVOKE语句

> 定义

~~~plsql
收回权限：将对指定操作对象的指定操作权限授予指定的用户
语法：
   REVOKE <权限>[，<权限>]…
   [ON<对象类型> <对象名>]
   FROM <用户>[, <用户>]…;

实例

--REVOKE

--把所有用户查询dept表权限收回
 REVOKE SELECT ON TABLE dept FROM PUBLIC;
	 
--把用户U1修改emp表的ename字段的权限收回
 REVOKE UPDATE(ename) ON TABLE emp FROM U1;

六、掌握事务处理控制命令

COMMIT
--使用commit可以提交所有没提交的事务
--可以把上次提交以来的事务提交
SAVEPOINT
--使用rollback命令回滚到一个指定的保存点
ROLLBACK
--可以回滚所有没提交的事务
--已经提交的事务不能再回滚