[积累]数据库学习笔记

Oracle

数据库就是柜子，柜子中的抽屉就是表空间，抽屉中的文件夹就是数据文件，文件夹中的纸就是表，写在纸上的字就是数据。

别名

别名默认的可以不写as关键字，除了数字必须用双引号括起来，其余的使不使用双引号均可。 Oracle中英文别名不加双引号时默认全部大写，存在小写时，需要加双引号。

distinct

order by

order by在排序的时候，系统会默认的将空值标注为最大，此时可以通过where条件过滤掉空值，也可以通过nulls last和nulls first来处理。

in 和 between……and

空值处理

因为空值与任何值进行运算的结果都为空，所以在实际运用中经常需要进行空值处理，空值处理用nvl函数，查询空值用is null，查询非空记录用is not null

模糊查询

模糊查询用like，其中下划线表示一个字符，百分号表示若干字符

PLSQL保存的 .sql 文件 Navicat中文乱码

更改PLSQL编码格式设置（工具-首选项-文件-格式-Encoding ）为 ”alway utf-8“

select substr(‘oracle’,-3,3) from dual;

字符函数

大小写转换用关键字upper和lower：

select upper('oracle') from dual;
select lower(ename),sal,comm from EMP where comm is not null;

联接字符串用 concat ，concat只有两个参数，也就是说只能关联两个字符串，当需要关联很多字符串时用 ||：

select concat('oracle','mysql') from dual;
select 'oracle'||'mysql'||'db2' from dual;

截取字符串用substr，对于substr，从1开始与从0开始截取结果是一样的，负数表示从倒数第几个字符开始截取字符串：

select substr('oracle',2) from dual;
--后三位
select substr('oracle',-3) from dual;
select substr(ename,length(ename)-2) from emp;

求字符串长度用length关键字：

select ename,length(ename) from emp;

串替换用replace：

select replace('hello datang','datang','zhixin') from dual;

串替字符串中的空格：

select replace('hello da tang',' ','') from dual;

ASCII码的转换：

select ASCII('a') from dual;
select chr(97)from dual;

trim的用法：

trim主要用来消除目标字符串左边或者右边的连续字符或者空格，例如：

select trim(both 'x' from 'xxxxscottxxxx')from dual;   
select ltrim('xxxxscottxxxx','x')from dual;
select rtrim('xxxxscottxxxx','x')from dual;
select trim('   scott   ')from dual;
select ltrim('   scott   ')from dual;
select rtrim('   scott   ')from dual;

lpad/rpad

主要用来在目标字符串的左边或者右边添加字符串：

select lpad('dasfs',8,'o')from dual;
select rpad('dasfs',13,'x')from dual; --13为添加之后的总长度

数值函数

round

四舍五入用round，可以指定保留小数位数，也可以对整数四舍五入（round也可以用于对日期的舍入）：

select round(35746.57) from dual;
select round(35746.57,1) from dual;
select round(35746.57,-1) from dual;
select round(1994,-1) from dual;

SELECT   round(TO_DATE('2008-11-28 12:59:59','YYYY-MM-DD hh24:mi:ss'),'MM') FROM   DUAL;

trunc

截断小数位用trunc，也可以截断整数：

select trunc(35746.57) from dual;
select trunc(35746.57,1) from dual;
select trunc(35746.57,-1) from dual;

得到最当前日期的所在月份的第一天

SELECT   TRUNC(TO_DATE('2008-11-28 12:59:59','YYYY-MM-DD hh24:mi:ss'),'MM') FROM   DUAL;

取余用关键字mod：

select mod(10,3) from dual;

日期函数

查看当前系统时间用sysdate：

select sysdate from dual;

求两个时间段之间的月份数用months_between：

select months_between(sysdate,hiredate)from emp;
select "ROUND"(months_between(sysdate,hiredate))from emp;

NEXT_DAY表示参考日期之后的某一个指定日期第一次出现的日期如：

select next_day(sysdate,'星期一')from dual; -- PL/SQL 和 SQLPLUS 可用
select next_day(sysdate,'mon')from dual;		-- Navicat可用

强制转换函数

char和varchar2的区别

CREATE TABLE "SCOTT".test (
"id" CHAR(10),"name" VARCHAR2(10)
)
;

INSERT INTO "TEST"();

SELECT * FROM "TEST";		-- char自动补全空格
SELECT length("id"),length("name") FROM "TEST"; --char 10

数据类型的特点 date/number:都有固定的格式，number的格式要求不能以0开头 char/varchar2:均表示字符型，字符型没有格式 char 是定长的,varchar2 是变长

blob 大对象类型，表示以二进制的形式存储大对象 clob 大对象类型，表示以文本的形式存储大对象

to_char

字符型的特点是没有格式限制，而日期型、数值型都有固定的格式限制，所以在实际应用中，经常需要将其他一些类型的数据转化为字符型，好进行一些原本无法进行的操作：

1.日期型转化为字符型：

select to_char(sysdate, 'fmyyyy-mm-dd-dy-q') from dual; --fm    2017-7-1-星期六-3
select to_char(sysdate, 'yyyy-mm-dd-dy-q') from dual;     --无fm   2017-07-01-星期六-3
-- -q 是什么？

2.数值型转化为字符型：

select to_char(sal,'$99,999')from emp;

SELECT
	TO_CHAR ("id", '$99,999') -- "TO_CHAR"(x) 结果前 一个空格，'$99,999'长度不够，$前面再加空格
FROM
	"TEST";
  
select to_char(1234,'$99,999')from dual;

select to_char(1,'0000')from dual; --结果 前面加0

to_date 与 to_number 数据库默认的日期型的输入方式为：08-8月 -08 这样的输入方式很不方便，实际工作中，一般通过to_date函数将字符串转换为日期型来输出输入日期型数据，譬如：

select to_date('2009-09-23','yyyy-mm-dd') from dual;
select TO_NUMBER('100.00', '9G999D99') from dual;

to_number

or 与 union 、union all的异同点：

三者都表示对结果并运算（合并），在考虑数据库查询优化的时候，我们一般建议用union替代or，两者的运行结果是完全一样的。 union与union all的区别是：union自动去除重复记录，union all保存重复记录，如：

select ename from emp where sal >2000 or comm is not null;

select ename from emp where sal>2000   union  select ename from emp where comm is not null;

select ename from emp where sal>2000   union all select ename from emp where comm is not null;

文

and 与 intersect的用法说明：两者都表示交集，运行的结果完全一样

select ename from emp where sal >2000 and comm is null;
 select ename from emp where sal>2000    intersect  select ename from emp where comm is null;

布尔操作符的优先顺序为：not、and、or，看下面例子并分析执行结果：

select * from emp where job='SALESMAN' or job='CLERK' and comm is null;

分组

在数据查询处理过程中，经常会出现以某一个属性为标准进行分组统计的情况，分组用group by，分组查询一般是伴随着列函数一起出现的，常用的列函数有：sum、count、max、min、avg等等，分组的原则是：不在列函数中的列，必须全出现在group by 之后。分组的语法格式：

SELECT 
FROM
WHERE
GROUP BY
ORDER BY

分组只能用于 select 语句，having 伴随 group by 一起出现，但是顺序可以互换， group by 或者 having 必须在 where 子句之后， order by 永远在语句的最后。

SUM 求和假如需要求每个部门的员工工资和，如何写SQL语句？求总的工资和大家都会，要求每个部门的工资和，只需要按部门分组即可：

SELECT
	SUM (sal),
	deptno
FROM
	emp
GROUP BY
	deptno;

COUNT 求总数对于count函数，永远不会返回null值，如果列中没有值，至少也会返回0值，同时聚集函数中也可以使用all和distinct，例如：

SELECT
	COUNT (DISTINCT JOB),
	COUNT (ALL JOB)
FROM
	emp;

AVG 求平均数前面学习中我们知道限定条件用where，如果要限定列函数的条件用什么呢？这里大家需要注意，对于列函数的限制用having，而非where，例如求各个部门的平均工资、员工人数，要求输出平均工资大于2000的部门，结果按平均工资降序排序，如下：

SELECT
	COUNT (empno),
	AVG (sal),
	deptno
FROM
	emp
GROUP BY
	deptno
HAVING
	AVG (sal) > 2000
ORDER BY
	AVG (sal) DESC;

MAX、MIN求最大值最小值

SELECT
	MAX (sal),
	MIN (sal)
FROM
	emp;

多表查询

联接查询这一块主要知识点包括自联接、外联接和内联接，以及SQL1999对查询的支持。基本的联接查询语法格式是：

             SELECT 别名1.colmun,

                    别名2.colmun  

             FROM table1 别名1,

                   table2 别名2 

             WHERE 别名1.colmun1=别名2.colmun2;

其中别名1.colmun表示要从第一张表中检索的数据列，别名2.colmun表示要从第二表中检索的数据列，别名1.colmun1表示指定的colmun1来自表table1中，别名2.colmun2表示指定的colmun2来自表table2中。

联接查询应遵循一些规则：

1、表明限定。

2、联接查询的表可以是两个或两个以上。使用表名限定列名，可以增强数据检索的效率。

3、如果相同的列名出现在多个表中，必须使用表名限定。

例如：

      select a.ename, a.sal, b.dname

          from emp a, dept b

         where a.deptno = b.deptno;

内联接

所谓内联接，就是当且仅当所有被联接的表中都存在匹配的行时，才将两个或者多个表中的行进行关联，内联接保证了两个表中所有的行都满足联接条件，但却丢失了一些不满足联接条件的数据。工作中用的最多的联接查询就是内联接。

例如查询员工的姓名、编号、部门编号、部门名称：

select e.ename, e.empno, e.deptno, d.dname  
from emp e, dept d 
where e.deptno = d.deptno and d.dname='SALES';

或者

select e.ename, e.empno, e.deptno, d.dname  
from emp e inner join dept d on e.deptno = d.deptno and dname='SALES';

或者

select e.ename, e.empno, e.deptno, d.dname  from emp e joindept d
 on e.deptno = d.deptno  where dname='SALES';

外联接

如果想只限定一个表的条件而不限定另外一个表的条件（换句话说，如果存在于一个表中的数据在另一个表没有匹配的值，未能匹配的行仍然包含在输出结果中），就需要使用外联接，外联接只能用于两张表的联接，包括左外联接和右外联接，用(+)表示，格式如下：

SELECT别名1.colmun,别名2.colmun FROM table1 别名1, table2 别名2 WHERE 别名1.colmun1(+)=别名2.colmun2;

或：

SELECT别名1.colmun,别名2.colmun FROM table1 别名1, table2 别名2 WHERE别名1.colmun1=别名2.colmun2(+);

对于外联接，（+）在左边表示右外联接，显示出右边表中存在而左边表不存在的数据。

自联接

自联接就是把某一张表中的行同该表中另外一些行联接起来。自联接主要用于查询比较相同的信息，所比较的列必须有相同的或兼容的数据类型。自联接是一种特殊的内联接。

为了联接同一个表，需要为该表指定两个不同的别名，只有这样才能把该表逻辑上作为两个不同的表使用。

例如查询编号为7369的员工的上级领导姓名及编号：

select e.ename,e.empno,a.ename,a.empno from emp e,emp a where e.mgr=a.empno  and e.empno=7369;

子查询
decode 与 case用法详解

decode作为一个一般函数经常被大家忽视，其实它的作用是巨大的， decode函数相当于一个条件语句,它将输入数值与函数中的参数相比较，根据输入值返回一个对应值,函数的参数是由若干数值及其对应结果值组成, 当然，如果未能与任何一个参数匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，decode函数还能识别和操作空值，decode是Oracle特有的函数。我们此处学习decode，主要是学习通过decode如何实现表的转置。

DECODE(value, if1, then1, if2, then2, if3, then3,...ifn, thenn, else)

表示如果value等于if1时，DECODE函数的结果返回then1,…,如果不等于任何一个if值，则返回else。亦即：decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)

行列互换，例：

create table TEST("ID" NUMBER,"KC" VARCHAR2(20),"CJ" NUMBER);

insert into TEST (ID, KC, CJ)values (1, '语文', 89);
insert into TEST (ID, KC, CJ)values (2, '语文', 98);
insert into TEST (ID, KC, CJ)values (2, '数学', 100);
insert into TEST (ID, KC, CJ)values (3, '语文', 100);
insert into TEST (ID, KC, CJ)values (3, '数学', 73);
insert into TEST (ID, KC, CJ)values (3, '英语', 25);
commit;

DECODE(value, if1, then1, if2, then2, if3, then3,...ifn, thenn, else)

SELECT 
"DECODE"("ID", 1, '一年级', 2, '二年级', 3, '三年级', 0),
"SUM"("DECODE"("KC", '语文', "CJ", 0)) as "语文",
"SUM"("DECODE"("KC", '数学', "CJ", 0)) as "数学",
"SUM"("DECODE"("KC", '英语', "CJ", 0)) as "英语"
FROM "TEST"
GROUP BY "ID"; --!!!ID分组

表操作

复制表

工作中，给某一张表创建复制表是很有必要的一件事，

复制一般有两种方式：一是复制表结构，一是复制结构和数据。

例如，创建emp表的复制表，代码如下：

create table myemp as select * from emp;

如果只是要复制表结构，只需要在语句后面添加一个不成立的等式即可：

create table myemp as select * from emp where 1=2;

创建表

创建表的语法结构是：

create table 表名 (列名 列属性，列名 列属性)  tablespace 表空间名；

现在要求创建一张学生表，包含三个属性，分别是：学生姓名（sname）字符型、学生年龄（sage）数值型、学生班级（cname）字符型，建表语句如下：



CREATE TABLE stu (
	sname VARCHAR2 (20),
	sage NUMBER (4),
	cname VARCHAR2 (20)
) TABLESPACE USERS;

有时候在创建表的时候，需要给列插入说明，创建方法如下：

create table test(num number,name varchar2(20));
comment on column test.num is '1表示大客户，2表示一般客户';

删除表用drop：

drop table stu;

清空表用truncate：

truncate table stu;

插入

一般插入数据有两种方法：一是不需要指定列名直接插入值，要求就是插入的值从数量、类型、顺序必须与表中的列名保持一致；一是指定给那些列插入值，然后给相应列插值：

 insert into stu values ('fan',24,'数学一班');
 insert into stu(sname,sage,cname) values ('huang',25,'数学二班');
 insert into stu values ('zhu',null,'数学三班');

to_date在表插入值时的应用

create table stu (sname varchar2(20),sage number(4),cname varchar2(20), birthday date);
insert into stu values('rxxt',1,'shu0841',to_date('2015-01-01','yyyy-mm-dd'));

为了操作的方便，可以临时的对当前会话的日期格式进行一下更改，譬如：

alter session set nls_date_format='yyyy-mm-dd';

批量插值

批量插值的例子：

create table emp1 as select * from emp where 1=2;

insert into emp1 select * from emp;


create table test (id number,name varchar2(20));

insert into test select empno,ename from emp where deptno=30;

多表插值

insert all into：将相同的数据一次性插入到多张表中

create table emp1 as select * from emp where 1=2;
create table emp2 as select * from emp where 1=2;



INSERT ALL INTO emp1 INTO emp2 SELECT
	*
FROM
	emp
WHERE
	deptno = 30;

insert all into 限定条件用 when then

通过不同的条件，将内容分别插入到不同的表中

insert all 
	when comm is not null then into emp1 
	when comm is null then into emp2  
	select * from emp where deptno in (10, 20);

更新

修改表中记录用update语句，对于update语句，一定要有where限定条件，要不会修改整个表的数据，

修改emp表中员工的奖金，要求是工资大于2000的员工奖金加100：

     update emp set comm=nvl(comm,0)+100 where sal>2000;

删除

delete 需要注意的是要根据需求加 where 限定条件，如果不加的话会删除整张表的数据：

       delete from emp where deptno=20;

思考：create、drop、truncate、insert、update、delete在用法上有什么区别？

create、drop、truncate系统自动提交，insert、update、delete用户需要手动提交或者回滚

DML DDL DQL ???

文

SQL四种语言：DDL,DML,DCL,TCL

1.DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.

DDL是SQL语言的四大功能之一。用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束 DDL不需要commit.

CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

2.DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。 DML分成交互型DML和嵌入型DML两类。依据语言的级别，DML又可分成过程性DML和非过程性DML两种。需要commit.

SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

3.DCL（Data Control Language）数据库控制语言授权，角色控制等

GRANT 授权
REVOKE 取消授权

4.TCL（Transaction Control Language）事务控制语言

SAVEPOINT 设置保存点
ROLLBACK  回滚
SET TRANSACTION

SQL主要分成四部分：（1）数据定义。（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。（2）数据操纵。（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。（3）数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。（4）嵌入式SQL的使用规定。涉及到SQL语句嵌入在宿主语言程序中使用的规则。

删除重复行

对于没有主键约束或者唯一约束的表，出现完全重复的数据的可能性是很大的，那么如何删除一张表中重复的数据只保留一条哪？大家知道，一般的where条件根本无法限制，因为数据都一样，此处要用到 rowid 关键字，rowid是一个十八位的十六进制字符串，它是每一条记录的物理标识，不会有重复的rowid出现，根据rowid的这一属性，我们可以采取保留rowid最大或者最小的记录.

DELETE
FROM
	"TEST" E
WHERE
	ROWID < (
		SELECT
			MAX (ROWID)
		FROM
			"TEST" A
		WHERE
			A . ID = 3
		AND 
			E . ID = A . ID
	);

删除后rowid变化！！！？？？

INSERT INTO "SCOTT"."TEST" ("ID", "KC", "CJ", "SEX") VALUES ('3', '英语', '25', NULL);

SELECT
			ROWID
		FROM
			"TEST" A
		WHERE
			A ."ID" = 3

？？？

增加列

alter table "TEST" add (sex number(2));
--以下方式报错，不可使用
alter table "TEST" add column "s" number;

删除列

alter table emp1 drop (sex);
alter table emp1 drop column sex;

修改列： 1.如果列中有数据，列的类型是无法修改的，只可以修改大小，而且修改之后的长度不能小于已有数据的长度； 2.如果列中没有数据，列的大小可以随意更改，如果列中有数据, char/number 只可以往大的改，不可以改小， varchar2 既可以改大也可以改小

alter table emp1 modify(sex number);
alter table emp1 modify(sex number(10));
alter table emp1 modify(sex number(10));
alter table emp1 modify(sex varchar2(1));

Contents