Oracle数据库基础++

--表与常用数据类型

--创建表基本语法
/*
CREATE TABLE 表名(
列名 数据类型[(长度)] [约束],
列名 数据类型[(长度)] [约束],
……
)
*/
--Oracle中常用的数据类型
/*
一.数字类型:NUMBER
1.整数:NUMBER(6),长度可以省略，如果省略则默认为38位的数字。
2.浮点数:NUMBER(8,2),长度不能省略，第一个参数表示整个数字的长度，第二个参数表示小数位。


二.字符类型:长度不能省略，默认单位字节
1.CHAR:保存固定长度的字符串，如果内容长度不足，使用空格补齐长度。
2.VARCHAR2:保存可变长度的字符串，如果内容长度不足，不使用空格补齐长度。
3.VARCHAR:目前为止VARCHAR与VARCHAR2功能相同，但以后版本的Oracle中的VARCHAR可能会改为其它数据类型。


三.日期类型：不能设置长度
1.DATE:日期+时间
*/
CREATE TABLE TEST1(
T_ID NUMBER(5),
T_NAME VARCHAR2(20 CHAR),
T_SEX CHAR(1 CHAR),
T_BIRTH DATE
);


--数据操作
/*
一、添加数据：INSERT INTO语句
1.向表中添加一行新数据，并向新行中所有列赋值。
格式:INSERT INTO 表名 VALUES(值,值,……);
示例:INSERT INTO TEST1 VALUES(1,'张三','男',TO_DATE('1990-10-20','YYYY-MM-DD'));


2.向表中添加一行新数据，并向新行中指定列赋值。
格式:INSERT INTO 表名(列名,列名,……) VALUES(值,值,……);
示例:INSERT INTO TEST1(T_ID,T_NAME) VALUES(2,'李四');


3.将其它表中的数据导入到指定的表中。
格式:INSERT INTO 表名[(列名,列名,……)] SELECT语句;
示例:INSERT INTO TEST1(T_ID,T_NAME,T_BIRTH) SELECT EMPLOYEE_ID,LAST_NAME,HIRE_DATE FROM EMPLOYEES;


二、修改数据：UPDATE语句
格式:UPDATE 表名 SET 列名=值[,列名=值,……] [WHERE 条件];
示例:
UPDATE TEST1 SET T_BIRTH=SYSDATE WHERE T_BIRTH IS NULL;
UPDATE TEST1 SET T_SEX='女',T_BIRTH=TO_DATE('1987-6-17','YYYY-MM-DD') WHERE T_ID=200;


三、删除数据:DELETE语句
格式:DELETE [FROM] 表名 [WHERE 条件];
示例:DELETE FROM TEST1 WHERE T_ID>200;
*/


--约束
/*
1. 主键约束:PRIMARY KEY,通过主键可以在表中找到唯一的一行数据。不能为NULL，不能重复。
2. 非空约束:NOT NULL，不能为空。
3. 检查约束:CHECK,当向列中添加数据或修改列中的数据时，检查数据是否合法。
4. 默认值:DEFAULT，当没有向列中添加数据时，默认向列中添加的数据。
5. 唯一约束:UNIQUE,可以为NULL(Oracle认为NULL可以重复，SQL Server认为NULL也不能重复)，不能重复。
6. 外键约束:
*/
CREATE TABLE TEST2(
T_ID NUMBER(5) PRIMARY KEY,
T_NAME VARCHAR2(20 CHAR) NOT NULL,
T_AGE NUMBER(2) CHECK(T_AGE BETWEEN 20 AND 50),
T_SEX CHAR(1 CHAR) DEFAULT '女'
);


INSERT INTO TEST2(T_ID,T_NAME,T_AGE) VALUES(1,'AA',20);


CREATE TABLE S(
S_ID NUMBER(6) PRIMARY KEY,
S_NAME VARCHAR2(30 CHAR) NOT NULL
);


CREATE TABLE C(
C_ID NUMBER(3) PRIMARY KEY,
C_NAME VARCHAR2(200 CHAR) NOT NULL
);


CREATE TABLE SC(
SC_ID NUMBER(8) PRIMARY KEY,
SC_S_ID NUMBER(6) NOT NULL,
SC_C_ID NUMBER(3) NOT NULL,
CONSTRAINT SC_SID_FK FOREIGN KEY(SC_S_ID) REFERENCES S(S_ID),
CONSTRAINT SC_CID_FK FOREIGN KEY(SC_C_ID) REFERENCES C(C_ID)
);


--主外键添加数据： 先添加主表，再添加子表
INSERT INTO S VALUES(1,'AA');
INSERT INTO C VALUES(1,'Java');
INSERT INTO SC VALUES(1,1,1);


--主外键删除数据：先删除子表，再删除主表
DELETE FROM SC WHERE SC_C_ID=1;
DELETE FROM C WHERE C_ID=1;


--联合主键：一个表只能有一个主键，但一个主键可以由多个列组成。
CREATE TABLE TEST3(
T_NAME VARCHAR2(20),
T_SEX CHAR(1 CHAR),
CONSTRAINT TEST_ID_PK PRIMARY KEY(T_NAME,T_SEX)
);


--SQL语句的分类
/*
1.DML(数据操作语言):SELECT,INSERT,UPDATE,DELETE
2.DDL(数据定义语言):CREATE,DROP,ALTER,TRUNCATE等
3.DCL(数据控制语言):COMMIT,ROLLBACK,SAVEPOINT等
*/


--事务:单位时间内一系列的操作，这些要么全部成功，要么全部失败。
/*
1.COMMIT:提交事务，将对数据的操作保存到数据库中。
2.ROLLBACK:回退事务，将数据还原到最初或最后一次提交的状态。
*/


/*
事务的组成
1.可以有多个DML语句
2.只能有一个DDL或DCL语句。
*/


/*
1.当异常结束Oracle会话时，Oracle会自动回退事务。
2.当正常结束Oracle会话时，Oracle会自动提交事务。
3.当执行DDL语句时，会自动提交事务。
*/

-- 截断表：TRUNCATE TABLE 表名。删除表中所有的数据，但不删除表结构。删除的数据不能回退。
TRUNCATE TABLE TEST2;


--序列：产生一个数字。用于自动生成主键。
--创建序列基本格式：
--CREATE SEQUENCE 序列名;
CREATE SEQUENCE TEST2_ID_SEQ;


INSERT INTO TEST2(T_ID,T_NAME) VALUES(TEST2_ID_SEQ.NEXTVAL,'AA');

--序列的值不能回退。
--多个表可以共用一个序列。

--序列属性：
--1.NEXTVAL:获得序列的下一个值，每次调用此属性序列的值都会发生改变。
--2.CURRVAL:获得序列的当前值，每次调用此属性序列的值不会发生改变。


SELECT TEST2_ID_SEQ.CURRVAL
FROM DUAL;


--查询当前用户中所有的表名
SELECT TNAME
FROM TAB;

--视图
/*
CREATE [OR REPLACE] VIEW 视图名
AS
SELECT语句;
*/


CREATE OR REPLACE VIEW V1
AS
SELECT LAST_NAME,TO_CHAR(HIRE_DATE,'YYYY-MM-DD') AS A,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 工龄
,CASE
WHEN MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12>25 THEN TO_CHAR(SALARY*20,'FM$9,999,999.00')
WHEN MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12>23 THEN TO_CHAR(SALARY*15,'FM$9,999,999.00')
WHEN MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12>20 THEN TO_CHAR(SALARY*10,'FM$9,999,999.00')
ELSE '不在此次活动范围内'
END AS 奖金
FROM EMPLOYEES;


--视图用法与表相同。
SELECT * FROM V1;


/*
视图的作用
1.简化查询(视图不能提高查询效率)。
2.提高数据库安全性。
*/


--视图中没有数据，视图的数据来自于表。

--索引：提高查询的效率，但会降低增删改的效率。
- -当表有主键约束或唯一约束时，Oracle会为这些列添加唯一性索引。

--多表连接
--笛卡尔积
--行数：多张表行数的乘积。
--原因：没有设置连接条件或连接条件不正确。


--等值连接(内连接)：只能查询出满足连接条件的数据
--查询员工的last_name,department_name
SELECT LAST_NAME,DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;

--当在FROM子句中为表起了别名后，其它子句不能再使用表的原名。


--多表连接时，建议在每个列的前面都加上表前缀，可以提高查询的效率。
SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;


--查询员工的last_name,job_id,job_title(在JOBS表中)
SELECT E.LAST_NAME,E.JOB_ID,J.JOB_TITLE
FROM EMPLOYEES E , JOBS J
WHERE E.JOB_ID=J.JOB_ID;


--通常情况下， 连接条件的个数为表的个数减1
--查询员工的last_name,department_name,city
SELECT E.LAST_NAME,D.DEPARTMENT_NAME,L.CITY
FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
AND D.LOCATION_ID=L.LOCATION_ID;


--SQL:1999
SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;


--查询员工的last_name,department_name,city,job_title
SELECT E.LAST_NAME,D.DEPARTMENT_NAME,L.CITY,J.JOB_TITLE
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
JOIN LOCATIONS L ON L.LOCATION_ID=D.LOCATION_ID
JOIN JOBS J ON J.JOB_ID=E.JOB_ID;


--外连接:可以查询出满足连接条件与不满足连接条件的数据。
--左外连接
--查询所有员工的last_name,department_name
SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;


--右外连接
SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;


--全外连接
SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;


--Oracle中特有的外连接:(+)
--左外连接
SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID (+);


--右外连接
SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID (+)=D.DEPARTMENT_ID;


--SQL Server中特有的外连接:*
--左外连接
SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE E.DEPARTMENT_ID*=D.DEPARTMENT_ID;


--右外连接
SELECT E.LAST_NAME,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D

WHERE E.DEPARTMENT_ID=*D.DEPARTMENT_ID;

其他连接：

Oracle数据库基础

Oracle数据库基础之单行函数

Oracle数据库基础之组函数、子查询