oracle数据库基本知识和操作，java项目性能调优

–基本的SELECT语句

SELECT 列名[,列名,……]|*

FROM 表名

/*

1.Oracle基本的SELECT语句中不能省略FROM子句。

2.关系数据库中，SQL语句不区分大小写。

3.SQL语句可以写成一行或多行。

4.通常情况下，SQLPLUS中SQL语句使用分号表示结束。

5.SQL语句只能直接使用半角的符号。

6.工作中不建议在SELECT语句中使用*查询表中的所有列，会降低查询的效率。

*/

–查询employees表中所有员工的last_name,salary

SELECT LAST_NAME,SALARY

FROM EMPLOYEES;

–查询departments表中所有的部门信息

SELECT *

FROM DEPARTMENTS;

–算术运算符：+，-，*，/

–+：只有加法运算的功能，没有连接的功能。

SELECT 5/2 --Oracle的结果为2.5，SQL Server的结果为2

FROM DUAL;

–查询employees表中所有员工的last_name,salary,年薪

SELECT LAST_NAME,SALARY,SALARY * 12

FROM EMPLOYEES;

–列别名

1.列名 列别名

2.列名 AS 列别名

3.Oracle会将英文列名变为大写。

4.如果列别名要区分大小写，或列别名中包含了特殊字符时,当列别名为关键字时，需要将列别名放在一对双引号中。

SELECT LAST_NAME AS “USER”,SALARY,SALARY * 12 AS “YEAR SALARY”

FROM EMPLOYEES;

–关系数据库中，双引号通常用于表示别名。

–关系数据库中，单引号表示字符串类型。

–连接符：||

SELECT FIRST_NAME||’.’||LAST_NAME AS NAME

FROM EMPLOYEES;

– DISTINCT关键字:屏蔽查询结果中重复的数据。

SELECT DISTINCT JOB_ID,SALARY

FROM EMPLOYEES;

/*

1.通常情况下，DISTINCT只能出现在SELECT与第一个列之间。

2.通常情况下，DISTINCT只能出现一次。

*/

–2016-11-11

–过滤与排序

–过滤：WHERE子句

SELECT 3

FROM 1

[WHERE 条件] 2 --条件的结果一定为boolean

–条件为数字类型

–查询employees表中所有50号部门员工的last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID=50;

- -条件为字符串类型:值必须放在一对单引号中，英文区分大小写

–查询employees表中last_name为fay的员工的employee_id,last_name,hire_date

SELECT EMPLOYEE_ID,LAST_NAME,HIRE_DATE

FROM EMPLOYEES

WHERE LAST_NAME=‘Fay’;

–条件为日期类型:值必须放在一对单引号中，并且必须为Oracle默认为日期格式：DD-MON-RR

–查询employees表中1994-6-7入职员工的last_name,hire_date

SELECT LAST_NAME,HIRE_DATE

FROM EMPLOYEES

WHERE HIRE_DATE=‘7-6月-94’;

–WHERE子句中不能使用列别名

– 比较运算符：>,>=,<,<=,=,!=,<>

–查询employees表中年薪大于100000的员工的last_name,salary,job_id,年薪

SELECT LAST_NAME,SALARY,JOB_ID,SALARY*12 AS 年薪

FROM EMPLOYEES

WHERE SALARY*12>100000;

–查询employees表中97年之后入职员工的last_name,hire_date

SELECT LAST_NAME,HIRE_DATE

FROM EMPLOYEES

WHERE HIRE_DATE>=‘1-1月-97’;

SET LINESIZE xxx

–特殊比较运算符

- -BETWEEN…AND…:查询指定范围内的数据(包含边界值)

–查询employees表中工资在6000-12000之间员工的last_name,salary

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY BETWEEN 6000 AND 12000;

–查询employees表中97-99年之间入职员工的last_name,hire_date

SELECT LAST_NAME,HIRE_DATE

FROM EMPLOYEES

WHERE HIRE_DATE BETWEEN ‘1-1月-97’ AND ‘31-12月-99’;

- -IN(值列表)：查询与值表中任意一个值匹配的数据。只能用于判断是否相等。

–查询employees表中在20或50号部门工作的员工，显示：last_name,department_id

SELECT LAST_NAME,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN(20,50);

–查询employees表中job_id为sa_rep或it_prog的员工的last_name,job_id

SELECT LAST_NAME,JOB_ID

FROM EMPLOYEES

WHERE JOB_ID IN(‘SA_REP’,‘IT_PROG’);

–LIKE:模糊查询

%:任意长度的任意字符。

_:一个长度的任意字符。

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%a%’;

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%n_’;

–使用 ESCAPE声明一个符号，说明LIKE中此符号后面的一个_或%不是转义字符，而是查询的内容。

–查询employees表中job_id中包含A_的员工的last_name,job_id

SELECT LAST_NAME,JOB_ID

FROM EMPLOYEES

WHERE JOB_ID LIKE ‘%A/_%’ ESCAPE ‘/’;

–IS NULL/IS NOT NULL

–NULL:与类型无关，表示未知或不确定的值。

SELECT LAST_NAME,JOB_ID,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID IS NULL;

–逻辑运算符：AND OR NOT

–查询employees表中工资在6000-12000之间员工的last_name,salary

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY BETWEEN 6000 AND 12000;

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY>=6000 AND SALARY<=12000;

–查询employees表中在20或50号部门工作的员工，显示：last_name,department_id

SELECT LAST_NAME,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN(20,50);

SELECT LAST_NAME,DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID=20 OR DEPARTMENT_ID=50;

–查询employees表中工资在5000-12000之间，并且在20或50号部门工作的员工，显示：last_name,salary,department_id

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE SALARY BETWEEN 5000 AND 12000 AND DEPARTMENT_ID IN(20,50);

–AND的优先级大于OR

SELECT LAST_NAME,SALARY,DEPARTMENT_ID

FROM EMPLOYEES

WHERE (SALARY>=5000 AND SALARY<=12000) AND (DEPARTMENT_ID=20 OR DEPARTMENT_ID=50);

–查询employees表中last_name中包含a(不区分大小写)的员工的last_name

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%A%’ OR LAST_NAME LIKE ‘%a%’;

SELECT LAST_NAME

FROM EMPLOYEES

WHERE NOT (LAST_NAME LIKE ‘%a%’);

– 排序：ORDER BY子句

SELECT 3

FROM 1

[WHERE] 2

[ORDER BY] 4

–通常情况下，ORDER BY子句会出现在SELECT语句的最后。

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

ORDER BY SALARY DESC ;

SELECT LAST_NAME,HIRE_DATE

FROM EMPLOYEES

ORDER BY HIRE_DATE ASC;

SELECT LAST_NAME,HIRE_DATE

FROM EMPLOYEES

ORDER BY LAST_NAME ASC;

/*

1 .升序：使用ASC关键字,默认为升序排序。

2.降序：使用DESC关键字。

*/

–查询employees表中所有员工的last_name,年薪,结果根据年薪降序排序。

SELECT LAST_NAME,SALARY*12 AS 年薪

FROM EMPLOYEES

ORDER BY SALARY*12 DESC;

SELECT LAST_NAME,SALARY*12 AS 年薪

FROM EMPLOYEES

ORDER BY 年薪 DESC;

SELECT LAST_NAME,SALARY*12 AS 年薪

FROM EMPLOYEES

ORDER BY 2 DESC;

- -多列排序

–查询employees表中所有员工的last_name,job_id,department_id,salary。结果根据department_id升序排序，salary降序排序。

SELECT LAST_NAME,JOB_ID,DEPARTMENT_ID,SALARY

FROM EMPLOYEES

ORDER BY DEPARTMENT_ID ASC NULLS FIRST,SALARY DESC;

–Oracle认为NULL是最大的。

–SQL Server认为NULL是最小的。

- -NULLS FIRST/NULLS LAST:在不影响排序规则的前提下，将NULL放在结果的最前/后面 。

–2016-11-14

–单行函数

–字符单行函数

SELECT LAST_NAME, UPPER(LAST_NAME)- -大写

FROM EMPLOYEES;

SELECT LAST_NAME, LOWER(LAST_NAME) --小写

FROM EMPLOYEES;

–查询employees表中last_name中包含a(不区分大小写)的员工的last_name

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%A%’ OR LAST_NAME LIKE ‘%a%’;

SELECT LAST_NAME

FROM EMPLOYEES

WHERE UPPER(LAST_NAME) LIKE ‘%A%’;

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LOWER(LAST_NAME) LIKE ‘%a%’;

SELECT LAST_NAME,LENGTH(LAST_NAME)

FROM EMPLOYEES;

SELECT LENGTH(‘中国’)–获得字符数

FROM DUAL;

SELECT LENGTHB(‘中国’)–获得字节数

FROM DUAL;

– 从指定位置开始截取字符串，直到字符串的最后

SELECT SUBSTR(LAST_NAME,3) AS A

FROM EMPLOYEES;

– 从指定的位置截取指定长度的字符串

SELECT SUBSTR(LAST_NAME,3,2)

FROM EMPLOYEES;

–从倒数第N位开始截取字符串，直到字符串的最后

SELECT SUBSTR(LAST_NAME,-3) AS A

FROM EMPLOYEES;

–从倒数第N位截取指定长度的字符串

SELECT SUBSTR(LAST_NAME,-3,2)

FROM EMPLOYEES;

- -INSTR():在第一个参数中查找第二个参数首次出现的位置，没找到返回0.

SELECT LAST_NAME,INSTR(LAST_NAME,‘a’)

FROM EMPLOYEES;

SELECT LAST_NAME

FROM EMPLOYEES

WHERE INSTR(LOWER(LAST_NAME),‘a’)>0;

–LPAD()/RPAD():显示第一个参数的值，并使用第二个参数指定第一个参数显示的长度，如果第一个参数长度不够，则使用第三个参数在第一个参数的左/右面补齐长度。

SELECT LPAD(EMPLOYEE_ID,6,0),LAST_NAME

FROM EMPLOYEES;

SELECT TRIM( ’ ABC ABC ') AS A–去掉字符串两端的空格

FROM DUAL;

SELECT TRIM(‘A’ FROM ‘AAABACAAA’)–去掉字符串两端指定的字符

FROM DUAL;

–查询employees表中所有员工的last_name,要求显示的last_name首字母为小写，其它均为大写。

SELECT LOWER(SUBSTR(LAST_NAME,1,1))||UPPER(SUBSTR(LAST_NAME,2))

FROM EMPLOYEES;

–数字单行函数

– ROUND():四舍五入

SELECT ROUND(2563.987),ROUND(2563.987,2),ROUND(2563.987,0),ROUND(2563.987,-1)

FROM DUAL;

- -TRUNC():截断数字

SELECT TRUNC(2563.987),TRUNC(2563.987,2),TRUNC(2563.987,0),TRUNC(2563.987,-1)

FROM DUAL;

- -MOD():取余数

SELECT MOD(15,2)

FROM DUAL;

–ABS():绝对值

SELECT ABS(-100)

FROM DUAL;

–日期单行函数

- -SYSDATE:获得数据库当前日期+时间

SELECT SYSDATE-100

FROM DUAL;

/*

1.日期+天数=日期

2.日期-天数=日期

3.日期-日期=天数

4.日期不能加日期

*/

–查询employees表中所有员工的last_name,hire_date,月数

SELECT LAST_NAME,HIRE_DATE,TRUNC((SYSDATE-HIRE_DATE)/30) AS 月数

FROM EMPLOYEES;

– MONTHS_BETWEEN():获得两个日期相差的月数

SELECT LAST_NAME,HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS 月数

FROM EMPLOYEES;

- -ADD_MONTHS():在指定的日期上加上指定的月数

SELECT ADD_MONTHS(SYSDATE,100)

FROM DUAL;

–NEXT_DAY():获得下一个星期N的日期 。

SELECT NEXT_DAY(SYSDATE,4)

FROM DUAL;

SELECT NEXT_DAY(SYSDATE,‘星期三’)

FROM DUAL;

–类型转换单行函数

–自动转换

SELECT LAST_NAME,SALARY

FROM EMPLOYEES

WHERE SALARY>‘10000’;

- -手动转换：TO_CHAR(),TO_DATE(),TO_NUMBER()

/*

1.字符串与日期可以相互转换

2.字符串与数字可以相互转换

3.日期与数字不能相互转换

*/

–TO_CHAR(N,F):将日期类型N根据模板F转换为字符串类型的值 。

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘YYYY-MM-DD’)

FROM EMPLOYEES;

SELECT TO_CHAR(SYSDATE,‘YYYY-MM-DD HH24:MI:SS DAY DY’)

FROM DUAL;

SELECT TO_CHAR(SYSDATE,‘D DD DDD’)

FROM DUAL;

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘FMYYYY"年"MM"月"DD"日"’)

FROM EMPLOYEES;

–查询employees表中所有星期一入职员工的last_name,hire_date(格式如:1999-1-1 星期一)

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘FMYYYY-MM-DD DAY’)

FROM EMPLOYEES

WHERE TO_CHAR(HIRE_DATE,‘DAY’)=‘星期一’;

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘FMYYYY-MM-DD DAY’)

FROM EMPLOYEES

WHERE TO_CHAR(HIRE_DATE,‘D’)=‘2’;

–TO_CHAR(N,F):将数据类型N根据模板F转换为字符串类型的值。

SELECT

TO_CHAR(256987.9654,‘FM$99,999,999.00’) AS A,

TO_CHAR(256987.9654,‘FML99,999,999.00’) AS A

FROM DUAL;

– RR:可自动补齐年的前2位

–T O_DATE(C,F):根据模板F将字符类型C转换为日期类型的值

SELECT TO_DATE(‘2017-1-27’,‘YYYY-MM-DD’)-SYSDATE

FROM DUAL;

SELECT TO_DATE(‘2017-1-27’,‘YYYY-MM-DD’)

FROM DUAL;

- -TO_NUMBER(C,F):根据模板F将字符类型C转换为数字类型的值。

SELECT TO_NUMBER(‘￥50,000.00’,‘L99,999.00’)

FROM DUAL;

–通用单行函数

–NVL():当第一个参数不为NULL，返回第一个参数。当第一个参数为NULL，返回第二个参数。两个参数的类型必须一致 。

SELECT LAST_NAME,NVL(TO_CHAR(COMMISSION_PCT,‘FM0.999’),‘没有佣金’)

FROM EMPLOYEES;

–关系数据库中，算术运算中出现NULL时，结果一定为NULL。

–查询employees表中所有员工的last_name,salary,commission_pct,年薪,年收入(年薪+年薪*佣金)

SELECT LAST_NAME,SALARY,COMMISSION_PCT,SALARY*12 AS 年薪,(SALARY*12)+(SALARY*12*NVL(COMMISSION_PCT,0)) AS 年收入

FROM EMPLOYEES;

–SQL Server中IFNULL()的功能与Oracle中的NVL()功能一致。

– 分支选择：case表达式，decode()

–CASE表达式：结果的类型必须保持一致 。

–查询employees表中所有员工的last_name,job_id,salary,新工资。如果job_id为it_prog时，工资增加10%。如果job_id为st_clerk时，工资增加15%。如果job_id为sa_rep时，工资增加20%，其它职位工资不变。

SELECT LAST_NAME,JOB_ID,SALARY,

CASE

WHEN JOB_ID=‘IT_PROG’ THEN TO_CHAR(SALARY*1.1,‘FM$999,999.00’)

WHEN JOB_ID=‘ST_CLERK’ THEN TO_CHAR(SALARY*1.15,‘FM$999,999.00’)

WHEN JOB_ID=‘SA_REP’ THEN TO_CHAR(SALARY*1.2,‘FM$999,999.00’)

ELSE ‘不在此次活动范围内’

END AS 新工资

FROM EMPLOYEES;

–只能判断是否相等

SELECT LAST_NAME,JOB_ID,SALARY,

CASE JOB_ID

WHEN ‘IT_PROG’ THEN TO_CHAR(SALARY*1.1,‘FM$999,999.00’)

WHEN ‘ST_CLERK’ THEN TO_CHAR(SALARY*1.15,‘FM$999,999.00’)

WHEN ‘SA_REP’ THEN TO_CHAR(SALARY*1.2,‘FM$999,999.00’)

ELSE ‘不在此次活动范围内’

END AS 新工资

FROM EMPLOYEES;

- -DECODE()函数:只能判断是否相等

SELECT LAST_NAME,JOB_ID,SALARY,

DECODE(

JOB_ID,

‘IT_PROG’,SALARY*1.1,

‘ST_CLERK’,SALARY*1.15,

‘SA_REP’,SALARY*1.2,

SALARY

)AS 新工资

FROM EMPLOYEES;

–2016-11-15

–查询employees表中所有5月入职的员工的last_name,hire_date(格式如：1999-5-1)

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘FMYYYY-MM-DD’)

FROM EMPLOYEES

WHERE TO_CHAR(HIRE_DATE,‘MM’)=‘05’;

SELECT LAST_NAME,TO_CHAR(HIRE_DATE,‘FMYYYY-MM-DD’)

FROM EMPLOYEES

WHERE TO_CHAR(HIRE_DATE,‘MON’)='5月 ';

–表

/*

CREATE TABLE 表名(

列名 数据类型[(长度)] [约束],

列名 数据类型[(长度)] [约束],

……

列名 数据类型[(长度)] [约束]

);

*/

–常用的数据类型

/*

一、数字类型：NUMBER

1.整数：NUMBER(5),长度可以省略，省略时默认为38位的数字。

2.浮点数：NUMBER(8,2),长度不能省略，整个数字为8位，其中有两位小数。

二、字符类型：不能省略长度。默认单位为字节。

1.CHAR:保存固定长度的字符串,如果内容的长度不足，使用空格在内容的后面补齐长度。

2.VARCHAR2:保存可变长度的字符串（不能超过上限）,不会使用空格补齐长度。

三、日期类型：不能指定长度。

DATE:日期+时间

*/

CREATE TABLE TEST(

T_ID NUMBER(5),

T_NAME VARCHAR2(20 CHAR),

T_SEX CHAR(1 CHAR),

T_BIRTHDAY DATE

);

–查看表结构：DESC 表名/视图名

–数据操作

/*

一、添加数据：INSERT INTO语句;

1.向表中添加一行新数据，并向新行中所有的列赋值。

格式：INSERT INTO 表名 VALUES(值,值,……);

例：

INSERT INTO TEST VALUES(1,‘AA’,‘男’,TO_DATE(‘1989-10-20’,‘YYYY-MM-DD’));

2.向表中添加一行新数据，并向新行中指定的列赋值。

格式：INSERT INTO 表名(列名,列名,……) VALUES(值,值,……);

例：

INSERT INTO TEST(T_ID,T_NAME) VALUES(2,‘BB’);

3.将其它表中的数据复制到指定的表中。

格式：INSERT INTO 表名[(列名,列名,……)] SELECT语句;

例：

INSERT INTO TEST(T_ID,T_NAME,T_BIRTHDAY) SELECT EMPLOYEE_ID,LAST_NAME,HIRE_DATE FROM EMPLOYEES;

二、修改数据：UPDATE语句;

格式：UPDATE 表名 SET 列名=值[,列名=值,……] [WHERE 条件];

例：

UPDATE TEST SET T_BIRTHDAY=SYSDATE WHERE T_BIRTHDAY IS NULL;

UPDATE TEST SET T_NAME=‘张三’,T_SEX=‘女’ WHERE T_ID=1;

三、删除数据：DELETE语句;

格式：DELETE [FROM] 表名 [WHERE 条件];

例:

DELETE FROM TEST WHERE T_ID>=200;

–删除TEST表T_NAME列中所有的数据。

UPDATE TEST SET T_NAME=NULL;

*/

– 事务：单位时间内的一系列操作，这些操作要么全部成功，要么全部失败。

–提交事务：COMMIT。将对表中数据的操作保存到表中。

–回退事务：ROLLBACK。将数据还原为最初或最后一次提交的状态。

/*

SQL语句的分类：

DML(数据操作语句):SELECT,INSERT,UPDATE,DELETE

DDL(数据定义语句):CREATE,DROP,ALTER等

DCL(数据控制语句):COMMIT,ROLLBACK,SAVEPOINT等

*/

/*

1.当出现一个DDL语句时，数据库会自动提交事务。

2.当正常退出Oracle时，Oracle会自动提交事务。

3.当异常退出Oracle时，Oracle会自动回退事务。

*/

–约束：

1 .主键约束：PRIMARY KEY,不能为NULL，不能重复。 通过主键可以在表中查询出唯一的一行数据。

2.非空约束：NOT NULL,不能为空。

3.检查约束：CHECK,添加或修改列中的数据时，检查数据是否合法。

4.默认值：DEFAULT,当没有向列中添加数据时，Oracle自动为列添加的数据。

5.唯一约束：UNIQUE,可以为NULL，不能重复。

6.外键约束：FOREIGN KEY。

- -删除表：DROP TABLE 表名;删除表同时删除表中所有的数据，删除的数据不能回退 。

DROP TABLE STUDENTS;

CREATE TABLE STU

【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】

浏览器打开：qq.cn.hn/FTf 免费领取

DENTS(

STU_ID NUMBER(5) PRIMARY KEY,

STU_NAME VARCHAR2(10 CHAR) NOT NULL,

STU_SEX CHAR(1 CHAR) CHECK(STU_SEX IN(‘男’,‘女’)),

STU_DATE DATE DEFAULT SYSDATE

);

INSERT INTO STUDENTS VALUES(1,‘AA’,‘女’,NULL);

INSERT INTO STUDENTS(STU_ID,STU_NAME,STU_SEX) VALUES(2,‘BB’,‘男’);

–数据库中不能存在同名的对象

CREATE TABLE S(

S_ID NUMBER(6) PRIMARY KEY,

S_NAME VARCHAR2(20 CHAR)

);

CREATE TABLE C(

C_ID NUMBER(3) PRIMARY KEY,

C_NAME VARCHAR2(100 CHAR)

);

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)

);

– 添加数据：先添加主表数据，再添加子表数据。

–删除数据：先删除子表数据，再删除主表数据。

–视图

/*

CREATE [OR REPLACE] VIEW 视图名

AS

SELECT语句;

CREATE OR REPLACE VIEW V1