oracle
数据库
1.1.
表的概念
在关系数据库中，数据被存放于二维数据表(Table)
中。
一个关系数据库由多个数据表组成，数据表是关系数据库的基本存储结构，由行和列组成，行（Row
）也就是横排数据，也经常被称作记录(Record)，列（Column）就是纵列数据，也被称作字段(Field)。表和表之间是存在关联关系的。关系有1对1,1对多，多对1，多对多
1.2. SQLite
手机里面的数据库
1.3.1.
结构化查询语言
SQL(Structured Query Language)
是结构化查询语言的缩写。
SQL
是在关系数据库上执行数据操作、检索及维护所使用的标准语言,
可以用来查询数据，操纵数据，定义数据，控制数据，所有数据库都使用相同或者相似的语言。
SQL
可分为:
·
数据定义语言（DDL
） : Data Definition Language
o
包含create
、drop、alter等 创建或者删除数据库表、视图、索引、序列等待
·
数据操纵语言（DML
） : Data Manipulation Language
o
对于数据库表的insert
、update、delete
o
必须要绑定事务的。在数据库中的TCL
的操作 ，使用Java访问数据库时，事务管理（采用SpringAop声明式事务管理）
·
事务控制语言（TCL
）：Transaction Control Language)
o
commit
、
rollback
、
savepoint
·
数据查询语言（DQL
）：Data Query Language
o
select
查询
·
数据控制语言（DCL
） : Data Control Language
o
创建用户、授权、回收权限等
执行SQL
语句时，用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。
1.3.2.
数据定义语言（DDL
）
用于建立、修改、删除数据库对象，包括创建语句(CREATE)
、修改语句(ALTER)、删除语句(DROP)，比如使用CREATE TABLE创建表，使用ALTER TABLE修改表，使用DROPTABLE删除表等动作。这类语言不需要事务的参与，自动提交。
1.3.3.
数据操作语言（DML
）
用于改变数据库数据，包括INSERT
、UPDATE、DELETE三条语句。其中，INSERT语句用于将数据插入到数据库中，UPDATE语句用于更新数据库中已存在的数据，DELETE用于删除数据库中已存在的数据。DML语言和事务是相关的，执行完DML操作后必须经过事务控制语句提交后才真正的将改变应用到数据库中。
Oracle
默认的事务隔离级别是READ COMMITED,
只能读取到提交后的数据。
1.3.4.
事务控制语言（TCL
）
用来维护数据一致性的语句，包括提交(COMMIT)
、回滚(ROLLBACK)、保存点(SAVEPOINT)三条语句，其中COMMIT用来确认已经进行的数据库改变， ROLLBACK语句用来取消已经进行的数据库改变，当执行DML操作后(也就是上面说的增加、修改、删除等动作)，可以使用COMMIT语句来确认这种改变，或者使用ROLLBACK取消这种改变。SAVEPOINT语句用来设置保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变。
在使用savepoint
的时候，谨慎使用commit
|
|
1.3.5.
数据查询语言（DQL
）
用来查询所需要的数据。使用最广泛，语法灵活复杂。
|
|
1.3.6.
数据控制语言（DCL
）
用于执行权限的授予和收回操作、创建用户等，包括授予(GRANT)
语句，收回(REVOKE)语句，CREATE USER语句，其中GRANT用于给用户或角色授予权限， REVOKE用于收回用户或角色已有的权限。DCL语句也不需要事务的参与，是自动提交的。
|
|
2. SQL
（DDL
、DML）
2.1.Oracle
数据类型
三大类：字符、数值、日期
2.1.1. NUMBER
NUMBER
表示数字类型，经常被定义成NUMBER
（P[，S]）形式类似于mysql中的double类型，其中：
·
P
表示数字的总位数，最大38
·
S
表示小数点后面的位数 ,
可以没有
·
表示整数时，不需设置s
，需要制定小数的精度时制定s
例如在表Emp
中的Sal列的定义如下：
copytextpop-up
1.
Sal NUMBER
(6,2)
Sal NUMBER(6,2)
表示Sal
列中的数据，整数位最大为4位，小数位最大位数是2位，也就是最大取值：9999.99。当使用number类型，需要限定value值
|
|
2.1.2. CHAR
CHAR
表示固定长度的字符类型，经常被定义成CHAR
（N）形式, N表示占用的字节数，N的最大取值是2000。
例如在表Emp
中的Ename列的定义如下：
copytextpop-up
1.
Ename CHAR
(20)
；
Ename CHAR(20)；
表示Ename
列中最多可存储20个字节的字符串，并且占用的空间是固定的20个字节。
|
|
2.1.3. VARCHAR2
VARCHAR2
表示变长的字符类型，定义格式是VARCHAR2
（N）, N表示最多可占用的字节数，最大长度是4000字节。类似于mysql中的varchar
例如在表Emp
中的JOB列的定义如下：
copytextpop-up
1.
JOB VARCHAR2
(100)
；
JOB VARCHAR(100)；
表示JOB
列中最多可存储长度为100个字节的字符串。根据其中保存的数据长度，占用的空间是变化的，最大占用空间为100个字节。
|
|
2.1.4 clob
存储字符类型的大文本，比如文章
2.1.5 blob
存储流媒体，二进制的内容比如图片，视频，音频
2.1.6. DATE
DATE
用于定义日期时间的数据，长度是7
个字节，默认格式是：DD-MON-RR, 例如：“11-APR-71”。如果是中文环境，是“11-4月-71”这种形式。
|
|
例如在表Emp
中的Hiredate列的定义如下：
copytextpop-up
1.
Hiredate DATE
；
Hiredate DATE；
表示Hiredate
列中存放的是日期数据。
2.2.
创建表
2.2.1. CREATE
语句
创建表的语法是：
copytextpop-up
1.
CREATE TABLE [
schema.]table_name(
2.
column_name datatype[
DEFAULT expr][,…]
3.
);
|
|
建立两者表，分别是员工表和部门表
|
|
CREATE TABLE [schema.]table_name(
column_name datatype[DEFAULT expr][,…]
);
--创建表emp
CREATE TABLE emp(
id NUMBER(10),
name VARCHAR2(20),
gender CHAR(1),
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptid NUMBER(2)
);
2.2.2. DESC
语句
DESC
是查看表的数据结构，语法是：
copytextpop-up
1.
DESC table_name;
DESC table_name;
2.2.3. DEFAULT
可以通过DEFAULT
子句给列指定默认值，例如，在emp表中给gender列赋默认值‘M’,如果插入数据时没有指定性别的员工，则默认是男性。
|
|
创建表emp1
：
copytextpop-up
1.
CREATE TABLE emp1
(
2.
id NUMBER
(10),
3.
name VARCHAR2
(20),
4.
gender CHAR
(1) DEFAULT ‘M’,
5.
birth DATE,
6.
salary NUMBER
(6,2),
7.
job VARCHAR2
(30),
8.
deptid NUMBER
(2)
9.
);
|
|
CREATE TABLE emp1(
id NUMBER(10),
name VARCHAR2(20),
gender CHAR(1) DEFAULT ‘M’,
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptid NUMBER(2)
);
CREATE TABLE emp2(
id NUMBER(10),
name VARCHAR2(20) NOT NULL,
gender CHAR(1),
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptid NUMBER(2)
);
2.3.
修改表
2.3.1.
修改表名
在建表后如果希望修改表名，可以使用RENAME
语句实现，语法如下，将改变表名old_name为new_name：
copytextpop-up
1.
RENAME old_name TO new_name;
RENAME old_name TO new_name;
举例说明，如果要将表名emp
修改为employee，使用如下语法：
copytextpop-up
1.
RENAME emp TO employee;
RENAME emp TO employee;
2.3.2.
增加列
在建表之后，要给表增加列可以使用ALTERTABLE
的ADD子句实现。语法如下：
copytextpop-up
1.
ALTER TABLE table_nameADD
2.
(
column datatype [DEFAULT expr] [, column datatype…])
ALTER TABLE table_name ADD
(column datatype [DEFAULT expr] [, column datatype…])
注意一点，列只能增加在最后，不能插入到现有的列中。下例给表employee
增加一列hiredate，并设置默认值为当前日期。
copytextpop-up
1.
ALTER TABLE employee ADD
(hiredate DATE DEFAULT sysdate);
ALTER TABLE employee ADD (hiredate DATE DEFAULT sysdate);
2.3.3.
删除列
在建表之后，使用ALTER TABLE
的DROP子句删除不需要的列。语法如下：
copytextpop-up
1.
ALTER TABLE table_nameDROP
(column);
ALTER TABLE table_name DROP (column);
删除字段需要从每行中删掉该字段占据的长度和数据，并释放在数据块中占据的空间，如果表记录比较大，删除字段可能需要比较长的时间。
下例删除表employee
的列hiredate：
copytextpop-up
1.
ALTER TABLE employee DROP
(hiredate);
ALTER TABLE employee DROP (hiredate);
2.3.4.
修改列
建表之后，可以改变表中列的数据类型、长度和默认值，注意这种修改仅对以后插入的数据有效，另外如果表中已经有数据的情况下，把长度由大改小，有可能不成功，比如原来的类型是VARCHAR2(100),
其中已经存放了100个字节长度的数据，如果要改为VARCHAR2（80），则不会修改成功。
语法如下：
copytextpop-up
1.
ALTER TABLE table_nameMODIFY
2.
(
column datatype [DEFAULT expr] [, column datatype…])
ALTER TABLE table_name MODIFY
(column datatype [DEFAULT expr] [, column datatype…])
下例修改表employee
的列job，并增加默认值的设置
copytextpop-up
1.
ALTER TABLE employee
2.
MODIFY
(
job VARCHAR2
(40) DEFAULT ‘CLERK’ );
ALTER TABLE employee
MODIFY(job VARCHAR2(40) DEFAULT 'CLERK' );
2.4.DML
语句
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
dname VARCHAR2(14) ,
loc VARCHAR2(13) ) ;
DROP TABLE emp;
CREATE TABLE emp
(empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(15),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO dept VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES
(20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES
(30,'SALES','CHICAGO');
INSERT INTO dept VALUES
(40,'OPERATIONS','BOSTON');
SELECT * FROM dept;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('19-4-87','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('23-5-87','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
2.4.1. INSERT
语句
INSERT
语句用来给数据表增加记录，每次增加一条记录。语法如下：
copytextpop-up
1.
INSERT INTO table_name[(
column[, column…])]
2.
VALUES
(value[, value…]);
|
|
|
|
INSERT INTO table_name[(column[, column…])]
VALUES(value[, value…]);
需要注意的一点，所有的DML
操作，都需要再执行事务提交语句commit，才算真正确认了此操作。
向employee
表插入一条记录的例子如下：
copytextpop-up
1.
INSERT INTO employee
(id, name, job, salary)
2.
VALUES
(
1001
,
‘rose’
, ‘PROGRAMMER’, 5500);
INSERT INTO employee(id, name, job, salary)
VALUES(1001, 'rose', 'PROGRAMMER', 5500);
Oracle
中的日期数据比较特殊，如果插入的列有日期字段，需要考虑日期的格式。Oracle
的默认的日期格式‘DD-MON-RR，你或者按照默认格式插入数据，或者自定义日期格式，用TO_DATE函数转换为日期类型的数据。
copytextpop-up
1.
—
使用默认日期格式插入记录
2.
INSERT INTO employee
(id, name, job,birth)
3.
VALUES
(
1002
,
‘martha’
, ‘ANALYST’, ’01-9
月
-89′
);
4.
5.
—
使用自定义日期格式插入记录
6.
INSERT INTO employee
(id, name, job, birth)
7.
VALUES
(
1003
,
‘donna’
, ‘MANAGER’,
8.
TO_DATE
(
‘1978-09-01’
,
‘YYYY-MM-DD’
));
--使用默认日期格式插入记录
INSERT INTO employee(id, name, job,birth)
VALUES(1002, 'martha', 'ANALYST', '01-9月-89');
--使用自定义日期格式插入记录
INSERT INTO employee(id, name, job, birth)
VALUES(1003, 'donna', 'MANAGER',
TO_DATE('1978-09-01', 'YYYY-MM-DD'));
2.4.2. UPDATE
语句
UPDATE
语句用来更新表中的记录，语法如下：
copytextpop-up
1.
UPDATE table_name
2.
SET column =
value [, column = value]…
3.
[
WHERE condition];
UPDATE table_name
SET column = value [, column = value]…
[WHERE condition];
其中WHERE
子句是可选的，但是如果没有WHERE子句，则全表的数据都会被更新，务必小心。
下例更改职员ROSE
的薪水为8500
copytextpop-up
1.
UPDATE employee SET salary =
8500 WHERE name = ‘ROSE’;
UPDATE employee SET salary = 8500 WHERE name = 'ROSE';
2.4.3. DELETE
语句
DELETE
语句用来删除表中的记录，语法如下：
copytextpop-up
1.
DELETE [
FROM] table_name [WHERE condition];
DELETE [FROM] table_name [WHERE condition];
和UPDATE
语句一样，WHERE子句是可选的，但是如果没有WHERE子句，则全表的数据都会被删除，务必确认后再予以删除。
下例将删除职位是空的员工记录
copytextpop-up
1.
DELETE FROM employee WHERE job is null
;
3.
函数
数据库中函数分为两大类，一个单值函数，一个组函数
在
Oracle
中提供了一个表
叫
哑表
,
表名是
dual
。这个表除了不能插入、删除、修改，其他事都能做，包括进行计算。通常用于测试。
在查询中使用函数时，通常会对查询显示的字段列名
取别名
3.1
单值函数
针对于单个字段列值进行操作的。
根据不同的字段列的类型区分
3.1.1
字符类型的单值函数
l
concat(c1,c2)
用于做字符拼接的，只能两两拼接
,
支持嵌套
n
c1
和
c2
可以是字段列名，也可以是字符值
SQL>select concat(‘a’,’b’) from dual; —
|
|
由于只能两两拼接，会很少使用，通常会使用
||
进行字符连接，可以一直拼接
|
|
l
length(c1)
用于计算字符长度的。
n
c1
可以是字段列名，也可以是字符值
|
|
l
upper(c1) lower(c1) initcap(c1)
n
c1
可以是字段列名，也可以是字符值
在
Oracle
中字段列存储的字符值是严格区分大小写的
upper
用于字符转大写
lower
用于字符转小写
initcap
用于字符首字母大写
,
其余字母小写
|
|
l
trim ltrim rtrim
n
trim(c1[ from c2])
从
c2
的首尾截取
c1
，要求
c1
字符出现在
c2
的首尾的位置上，才能被截取
n
ltrim(c1[,c2])
从
c1
的左边截取
c2
，要求左边的第一个字符是
c2
才能被截取
n
rtrim(c1[,c2])
|
|
l
substr(c1,m[,n]])
用于截取子串
用于返回char
中从m位开始取n个字符的子串，字符串的首位计数从1开始。
n
c1
可以是字段列名，也可以是字符值
n
m
代表从
c1
字符的索引，
m=0
和
m=1
的结果是一致的。
n
n
代表截取的总位数，如果不设置或大于字符长度，代表截取到字符末尾
|
|
|
|
l
to_char(c1,fmt)
用于将日期转字符
n
c1
可以是字段列名，也可以是字符值
n
fmt
是字符格式，类似于
java
中的日期格式，
m
代表月，
mi
代表分钟
|
|
|
|
3.1.2
数值类型的单值函数
数值类型统一使用
number(m[,n])
如果只有
m
，代表整数，如果有
n
，
m
代表总位数，
n
代表小数点后位数。
使用
m
或者
n
控制数值的范围的
针对于数值的单值函数，类似于
java
中
Math
类中常用
比如说对数值操作的取地板值、天花板值、四舍五入、截取
l
round(c1[,n])
用于对
c1
进行四舍五入
n
c1
可以是字段列名，也可以是数值
n
n
代表四舍五入的精度。如果不赋值默认是
0
。
n
要求必须是整数
如果
n
是正整数，代表四舍五入到小数点后
n
位
如果
n
是
0
，代表到整数位
如果是负数，代表到小数点前
n
位
|
|
l
trunc(c1[,n])
用于对
c1
进行截取
n
c1
可以是字段列名，也可以是数值
n
n
代表截取的位数。如果不赋值默认是
0
。
n
要求必须是整数
n
如果
n
是正整数，代表截取到小数点后
n
位
n
如果
n
是
0
，代表到整数位
n
如果是负数，代表到小数点前
n
位
|
|
l
floor ceil
n
floor(c1)
用于取
c1
的地板值（无限趋近于
c1
的值并小于
c1
的整数）
n
ceil(c1)
用于取
c1
的天花板值（无限趋近于
c1
的值并大于
c1
的整数）
|
|
|
|
3.1.3
日期类型的单值函数
使用
Oracle
数据库时，默认的日期格式
‘DD-MM-YY’,
支持字符日期插入的。
在使用日期时，需要对日期进行格式化的，
y
代表年，
m
代表月，
d
代表日，
h
代表小时，
mi
代表分钟，
s
代表
秒
l
sysdate
用于获取系统默认的日期，默认显示的格式是
DD-MM-YY’
，不显示时分秒，如果需要用到时分秒，可以使用其他的函数
|
|
|
|
l
to_date(c1,fmt)
用于将字符转日期
n
c1
代表日期字符或者字段列名
n
fmt
是当前字符格式
假设获取的日期的内容是不满足系统默认支持的日期格式，获取到的表单的日期数据是
2017-10-12
，实体类设计是
String
类型的，对应的字段设计成
date
类型，需要将字符格式的日期转日期
|
|
l
last_day
获取月份的最后一天
|
|
l
add_months(c1,n)
从
c1
日期开始加
n
个月后的日期
n
是整数，如果是正整数，向后计算日期，如果是负数，就是向前推
n
个月
|
|
l
months_between(c1,c2)
用于计算两个日期之间的间隔月，如果不是整数月，会有小数值
|
|
l
next_day(c1,char)
用于获取
c1
日期下一个周几的日期
，
char
的值
1-7
代表周日到周六
|
|
3.1.4
空值处理函数
数据库中所有的字段列都支持
null
值，在一些情况下需要对
null
值进行处理。
比如说想要进行计算，任何一个值和
null
值进行计算，结果是
null
值。
比如使用计算时，
null
值转成
0
null
值处理中，还可以用作于条件
,
进行空值比较，
where xxx
is [not] null
l
nvl(c1,default_value)
空值处理函数，如果
c1
列的值为
null
，取
default_value
|
|
在
mysql
中的空值处理函数是
ifnull(c1,default_value)
l
nvl2(c1,fasle_value,true_value)
如果
c1
列值不为空，取
fasle_value
，如果
c1
列值为空，取
true_value
|
|
3.1.5 case when
类似于java中switch case。可以用于等值比较，也可以用于不等值比较
语法：case column when value1 then expr1 when value2then expr2
else default
处理
end
casewhen express1 then express1_value when express2 then express2_value else default处理 end
可以用作行列转换
|
需求：根据不同的部门对部门中的员工的工资上浮一定比例，查询出上浮后的工资和原工资，10号部门上浮10%，20号部门上浮20% ，30号部门上浮30%,其他部门上浮50%
SQL>select deptno,ename,sal,(case deptno when 10 then sal*1.1 when 20 then sal*1.2 when 30 then sal*1.3 SQL>select deptno,ename,sal,(case when deptno =10 then sal*1.1 when deptno =20 then sal*1.2 when deptno =30 then sal*1.3 else sal*1.5 end) 涨后工资 from emp; 行列转换： 已知数据库表t_score中的记录 name course score 蒋总 Java 80 蒋总 Oracle 90 蒋总 Spring 100 要求查询出的结果显示为 姓名 Java Oracle Spring 蒋总 80 90 100 SQL> select name 姓名, max(case when course=’Java’ then score end) Java , max(case when course=’Oracle’ then score end) Oracle,max(case when course=’Spring’ then score end) Spring from t_score group by name;–在使用oracle时需要加Group by分组，mysql不需要 |
3.1.6 decode
decode 只能用于等值比较
DECODE函数的语法如下：
DECODE(expr, search1, result1[, search2, result2…][, default])
它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，
可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，
则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。
|
需求：根据不同的部门对部门中的员工的工资上浮一定比例，查询出上浮后的工资和原工资，10号部门上浮10%，20号部门上浮20% ，30号部门上浮30%,其他部门上浮50% SQL> select deptno,ename,sal,decode(deptno,10,sal*1.1,20,sal*1.2,sal*1.5) from emp 需求：根据当前员工的收入，工资上浮，查询出上浮后的工资和原工资，小于1000上浮50% ，其余的上浮30% 不能使用decode函数，只能使用case when SQL>select deptno,ename,sal,case when sal<1000 then sal*1.5 else sal*1.3 end from emp; |
4.DQL
select语句 ，是SQL的核心。
语法：
select[distinct] column_name ,xxx from table_name[,table_name1] [where expr] [groupby column ] [having xxx] [order by column]
编写sql 的思路：
l 明确定义要查询的内容来自于哪些表
l 明确select后面的内容，select后面支持算术计算
l 明确where条件过滤
l 考虑是否要分组
l 是否要having,考虑是否要对分组后的数据再过滤
l 考虑是否要排序
l 如果需要用到子查询，使用子查询。出现在from后面，出现在where后，出现在having后面
l 注意别名的使用
4.1 where
用于过滤，筛选出符合条件的记录。
4.1.1 支持算术计算
+ – * /
|
需求： 查询员工表，查询出员工姓名，收入，年收入大于100000的员工 SQL>select ename,sal,sal*12 from emp where sal*12>100000; |
4.1.2 比较运算符
> 、 <、 >= 、<= 、 != 、 <>
日期类型支持比较大小
4.1.3 逻辑运算符
当有多个条件时，需要指定条件之间的联系
and 条件之间是并且的关系
or 条件之间是或的关系
如果条件过滤是一个组合，通常会用括号
当使用where条件进行过滤时，一般讲能够筛选出大部分记录的条件放在前面
|
需求： 查询出工资在1000-1500范围内的员工 SQL>select ename,sal from emp where sal>=1000 and sal<=1500; 查询出工资在1000-1500范围内10号部门的员工 SQL>select ename,sal from emp where sal>=1000 and sal<=1500 and deptno=10; SQL>select ename,sal from emp where deptno=10 and sal>=1000 and sal<=1500 |
4.1.4 between and
用于控制范围的。
|
需求： 查询出工资在1000-1500范围内的员工 SQL>select ename,sal from emp where sal between 1000 and 1500; |
4.1.5 in,not in
in用于控制在一个集合内比较
|
需求： 查询出10号部门和20号部门的工资 SQL>select ename,sal from emp where deptno = 10 or deptno = 20 or deptno=30; SQL>select ename,sal from emp where deptno in(10,20,30); |
4.1.6 like,notlike
用于做模糊查询，了解通配符 % 代表任意的n个字符， _ 代表任意的单个字符
|
需求： 查询出名字包含a的员工信息 SQL> select ename from emp where ename like ‘%a%’ or ename like ‘%A%’; SQL> select ename from emp where lower(ename) like ‘%a%’; |
% 和 _ 有特殊含义，如果要求查询出的结果中包含% 或者_，需要对% 和 _ 进行转义 ，使用 \ 转义
|
需求： 查询出名字中包含 _ 的员工 SQL>select ename from emp where ename like ‘%\_%’ escape ‘\’; 查询出名字中包含 % 的员工 SQL>select ename from emp where ename like ‘%\%%’ escape ‘\’; |
4.1.7is null ,is not null
用于对空值的比较。
|
需求： 查询出有提成的员工信息 SQL>select ename ,comm. from emp where comm. is not null and comm.!=0; |
4.1.8any,all
会出现在单列子查询中,配合比较运算符使用
|
需求： 查询比10号部门所有员工工资高的员工信息 SQL>select ename,sal from emp where deptno!=10 and sal>all (select sal from emp where deptno=10) |
>any 大于结果中的最小的
<any 小于结果中最大的
>all 大于结果中最大
<all 小于结果中最小
4.2 组函数
聚合函数，用于做统计的。
常见的组函数有
l sum 求和，会忽略空值
l max 取最大值，会忽略空值
l min 取最小值，会忽略空值
l avg 取平均值 ，会忽略空值
l count 统计行记录数 ，如果统计所有行，不忽略空值。如果根据某个字段列统计行记录数，会忽略空值。
|
需求： 查询出员工的最高工资、最低工资、平均工资 SQL>select sum(comm),max(comm),min(comm),avg(comm) from emp; 查询统计出有多少员工有提出 SQL>select count(comm) from emp; 查询统计出公司有多少员工 SQL>select count(*) from emp; 查询统计出公司员工的平均提成收入，没有提成的也要被提成,四舍五入到小数点后2位 SQL>select round( sum(comm)/count(*),2) from emp; |
4.3 group by
用于分组。 group by 字段列名，一般配合组函数一起使用。用于将相同字段列的值作为一组数据，用于统计.
在
Oracle
中使用
group by
子句时，需要注意
select
后面只能是
groupby
的列名或者是组函数。
|
需求： 查询统计出每个部门的最高工资 SQL>select max(sal) from emp group by deptno; |
4.4 having
用于对分组后的数据再过滤。.
|
需求： 查询统计出每个部门的人数 SQL>select count(*) from emp group by deptno; 查询统计出部门人数超过5个人的 SQL> select count(*) from emp group by deptno having count(*)>5; SQL> select distinct e.deptno,e1.c from emp e ,( select count(*) c, deptno from emp group by deptno) e1 where e.deptno=e1.deptno and e1.c>5; |
4.5 order by
排序，order by字段列名 默认按照升序 asc 升序 desc 降序
可以同时对多个字段列排序，必须分别制定排列的顺序
|
需求： 查询出员工的工资，按照升序排列，如果工资相同按照姓名的降序排列 SQL>select ename,sal from emp order by sal asc,ename desc; |
5.关联查询
在一定的情况下，查询的数据，可能来自于不同的数据库表。需要对多个表中的记录进行查询，就要使用管理查询。
from后面可以有多个表，中间用逗号分隔。需要对表取别名，对查询的列使用别名. 指定查询的列来自于哪张表。否则出错：未被明确定义的列
使用关联查询时，必须在where条件中指定表之间的连接条件。否则出笛卡尔积。
5.1 内连接
将表之间满足条件的记录都查询出来
使用 inner join on
|
需求： 查询出员工的姓名，部门名称 SQL>select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; SQL>select e.ename,d.dname from emp e inner join dept d on e.deptno= d.deptno; |
5.2 外连接
分为左外连接和右外连接
左外连接使用 left join on，将左表的数据都查询出来，不满足记条件的记录补null
左外连接的结果=内连接的结果+左表中不满足条件的记录
|
需求： 在emp表中插入一个员工信息，不分配deptno 要求查询出所有的员工和部门名称 SQL> select e.ename,d.dname from emp e left join dept d on e.deptno= d.deptno; |
右外连接使用 right join on
|
SQL> select e.ename,d.dname from dept d right join emp e on e.deptno= d.deptno; |
6.子查询
在执行sql查询时，一条sql查询不出结果，需要多条sql配合
根据子查询的结果，将子查询分为：单行单列、单行多列、多行单列、多行多列
子查询可以出现的位置：
l 出现在from语句后，作为一张虚表存在
l 出现在where语句后，要根据使用的运算符来选择使用哪种子查询
比如说使用比较运算符，只能使用单行单列的子查询
比如说使用in 的集合操作，>any 等，能够使用多行单行 单列的子查询
l having语句后
l 在使用update、delete语句中也可以使用select查询
update、delete的时候都会where语句，
6.2 分页查询
在使用mysql数据库时如何分页查询？
使用limit关键字，select *from tablet_name limit 起始位置,页面大小;
使用Oracle数据库：
在Oracle数据库中，不支持limit关键字，分页查询是借助于子查询实现。在oracle中提供了一个rownum字段列，可以查询使用，rownum称为伪列，不真实存在于数据库表的字段上，但是可以查询出来
|
–分页查询 –rownum 伪列，列不真实存在，但是可以select select rownum ,ename from emp; –rownum称为行号 –特点是 只能小于或者小于等于某一个值，不能大于或等于某一个值，1除外 select rownum,ename from emp where rownum<5;–获取到1-4行记录 –如果想要获取5-8行记录？？ select rownum,ename from emp where rownum<9 and rownum>4;–查询不到任何结果 –如果想要查询第2行记录 select rownum,ename from emp where rownum=2;–查询不到任何结果 –默认情况下rownum是从1开始取值的 –想要实现分页查询，借助于rownum实现，需要将rownum查询出来做成真实列，然后进行分页 –分页查询emp表，每页显示5行记录，查询第1页 select rownum ,e.* from emp e where rownum<=5; –查询第二页 select * from (select rownum rn,e.* from emp e) e1 where e1.rn>=6 and e1.rn<=10; 以职位分组，找出平均工资最高的两种职位 select job from (select job,avg(sal) AvgSal from emp group by job order by AvgSal desc) e where rownum in(1,2); –设计一个通用的分页查询sql –page 页码，从1开始取值， size 页面大小 select * from (select rownum rn,e.* from emp e ) e1 where e1.rn>=（page-1）*size+1 and e1.rn<=（page）*size; |
7.集合操作
用于对多个查询结果的处理
l union 合并查询结果，并去除重复行
l union all 合并查询结果，不去除重复行
l intersect 获取两个集合结果的交集
l MINUS 获取两个集合的差集
|
select * from emp where deptno=10;–4 select * from emp where sal>1500;–8 行记录，其中2行记录是10号部门的
select * from emp where deptno=10 union select * from emp where sal>1500;–10 –union all 集合操作，合并查询结果，不去除重复行 select * from emp where deptno=10 union all select * from emp where sal>1500;–12
select * from emp where deptno=10 INTERSECT select * from emp where sal>1500;–2 — MINUS 获取两个结果集的差集。只有在第一个结果集中存在，在第二个结果集中不存在的数据， –才能够被显示出来。也就是结果集一减去结果集二的结果 select * from emp where sal>1500 MINUS select * from emp where deptno=10 ;–6 |
9.序列
Sequence。作用可以用于维护主键值的。
在使用Oracle数据库如果使用number类型的字段作为主键，通常需要使用Sequence。。 序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，是一种高效率获得唯一键值的途径。
序列是独立的数据库对象，和表是独立的对象，序列并不依附于表。
通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值，不推荐使用。
语法：
createsequence 序列名; 默认从1开始，步增1
CREATESEQUENCE [schema.]sequence_name [start WITH i ] [ INCREMENT BY j [ MAXVALUE m |NOMAXVALUE ][ MINVALUE n | NOMINVALUE ][ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]
如果j是正数，表示递增，如果是负数，表示递减
序列可生成的最大值是m，最小值是n
如果没有设置任何可选参数，序列的第一个值是1，步进是1
CYCLE表示在递增至最大值或递减至最小值之后是否继续生成序列号，默认是NOCYCLE
CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20
正常使用时，针对于每张表都建立一个序列，序列名以seq_tablename
序列的使用:
提供了2个函数 nextval 和 currval 分别用于获取序列的下一个值和当前值。序列创建完成后，都是通过序列名. 调用，第一次调用只能先调用nextval
当序列建立完成后，当前序列中是没有值的，只有调用nextval，取到1.当前序列才有值。
|
create table t_zte (id number primary key, name varchar2(10)); –id是主键约束，要求是非空且唯一的。是number类型的，由数据库系统维护 –Oracle中提供了sequence序列 –针对于当前表建立序列 create sequence seq_zte; –要向t_zte表中插入数据，要指定主键值 insert into t_zte values(seq_zte.nextval,’zhang’); –通过dual表查询当前序列值 select seq_zte.currval from dual; –通过dual表查询当前序列的下一个值 select seq_zte.nextval from dual; –再次插入 insert into t_zte values(seq_zte.nextval,’张震’); –查询数据表内容 select * from t_zte; –调用序列的nextval就会获取当前序列的下一个值，调用序列的currval 就会获取当前序列中的值 –序列的删除使用drop命令 create sequence seq_test; –直接调用当前序列的currval。出错 select seq_test.currval from dual; 在使用JDBC或者MyBatis来访问操作Oracle数据库时。如果是number类型的主键，必须显示的插入主键值。 inert into t_user(id,name,xxxx) values(seq_user.nextval,’xxx’,x) |
10.视图
视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,
其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化
数据库表，是一个二维的机构，占内存
语法：
createview 视图名 as subquery;– subquery 是一个select语句，将当前select语句查询到的内容，映射到当前视图中。
create view 视图名 as subquery [with read only | with check option]
建立视图，视图名的选择一般是v_tablename tablename是针对于基本的。
|
–视图 –建立一个视图，视图中映射emp表的数据 create view v_emp as select * from emp; –目前使用etc账户登录的，没有权限 –需要授权，使用system用户 –grant create view to zte; –视图建立完成后，可以查看视图结构 –desc desc v_emp; –查询视图中的内容 select * from v_emp; –可以建立一个视图，视图中只包含部分记录，和部分字段列 create view v_emp1 as select empno, ename,sal,deptno from emp where sal>1500; desc v_emp1; select * from v_emp1; drop view v_emp1; –可以对视图进行dml操作 –视图中8行数据，基表emp 有16行记录 –insert into emp(empno,ename)values(110,’zhang’) –对应v_emp1执行insert,只能插入当前视图中的字段列 insert into v_emp1(empno,ename,sal,deptno) values(3333,’张震’,50000,10); –执行插入是插入到emp表中的，当前视图对应的基表 –建立视图的语句中 添加 with read only，代表当前视图是只读的，不能执行dml
create view v_emp2 as select empno, ename,sal,deptno from emp where sal>1500 desc v_emp2; select * from v_emp2;– –对v_emp2视图执行dml insert into v_emp2(empno,ename,sal,deptno) values(6666,’徐颖’,50000,10);–出错 — with check option –对应视图执行DML操作时，要求必须满足视图的创建中的查询条件 create view v_emp3 as select empno, ename,sal,deptno from emp where sal>1500 with check option; –对于视图v_emp3执行DML操作必须满足 sal>1500的条件 insert into v_emp3(empno,ename,sal,deptno) values(6666,’徐颖’,1000,10);–出错 –如果sal>1500的情况是正常插入的。 –总结：视图中本身不包含数据，只包含对于基表的数据映射，但是可以对视图执行DML操作，通常不这么做 –通常DML操作于数据库表对象，建立视图 可以设置 with read only 只读视图，当前视图只允许select，不支持DML –设置为with check option ，当前视图如果执行DML操作必须满足建立视图时映射的where条件 |
视图的作用：如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询
视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问.
如果建立视图时，
select
中有函数，需要加别名
。
11．索引
索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象。索引记录中存有索引关键字和指向表中数据的指针（地址）
索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引，是一种提高查询效率的机制。
可以对表的一些字段列加上索引。数据库表都有主键，主键默认有个主键索引。
语法：
create index on t_user(name);
CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED]INDEX <索引名>
ON <表名>(<列名>[ASC|DESC] [, <列名>[ASC|DESC]…])
UNIQUE——建立唯一索引。
CLUSTERED——建立聚集索引。
NONCLUSTERED——建立非聚集索引。
ASC——索引升序排序。
DESC——索引降序排序。
合理使用索引提升查询效率
为提升查询效率，创建和使用索引的原则：
为经常出现在WHERE子句中的列创建索引
为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
为经常作为表的连接条件的列上创建索引
不要在经常做DML操作的表上建立索引
不要在小表上建立索引
限制表上的索引数目，索引并不是越多越好，需要维护。
删除很少被使用的、不合理的索引
优点
1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点
1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。