会话：session
.会话是用户与Orcal服务器的连接
.当用户与服务器建立连接时创建会话
.当用户与服务器断开连接时关闭会话
Orcale有几个默认用户：
-SYS是系统最高权限的管理员。
-SYSTEM是默认的系统管理员，该用户拥有Orcale管理工具
使用的内部表和视图。通常通过SYSTEM用户管理数据库用户、
权限和存储等
-SOCTT用户是Orcale数据库的一个示范账户，
在数据库安装时建表（测试账号）。
访问会产生【热点】：多个数据放在了一个表中。
表空间文件.DBF
创建表空间：
create tablespace 表空间名
datafile '路径'
size 20M 意思是超过20M会自动增长
autoextend on;
删除表空间同时删除表空间的所在的物理文件：
drop tablespace 表名 Including contents and datafiles;
--注意千万不要手动去删除文件下面的bdf文件
--------------------------------------
--数据字典查询所有表空间
select * from DBA_TABLESPACSE;
--查询所有数据文件
select * from DBA_DATA_FILES
------------------------------------------
创建用户：
create user name --创建 用户 用户名
identified by pass --密码 密码字符
default tablespace tname --默认表空间 空间名
temporary tablespace temp; --历史表 表空间名
--删除用户，注意：drop会删除用户的所有表和数据，表空间还在
drop user 名字;
--Oracle创建数据库会默认创建一个测试用户soctt，密码tiger，
这个用户默认是锁定的
alter user 用户名 account lock; --加锁
alter user 用户名 account unlock; --解锁
--赋予连接权限给用户
grant connect to 用户名;
--赋予权限给该用户，该用户可以将该权限在赋予给另外一个用户
grant connect to 用户名 with admin option;
-- 角色，DBA 数据库管理员角色有很多权限，
-- 一个角色有很多权限
grant DBA to 用户名 with admin option;
-- 收回权限
revoke connect from 用户名
/**
Orcale常用的数据类型
数值型：int（整型） number（浮点型）
字符型：
char：固定长度，char(8)，存入“jack”，
数据库开辟八个字节的空间存储数据，速度快。
varchar：可变长度，varchar(8)，存入“jack”，
数据库开辟四个字节的空间存储数据,可节省空间。
varchar2(orcale独有的)：可变长度，orcale不建议存储空字符，
建议存储null，为了orcale的兼容性，推荐使用。
日期型：date
*/
SQL(Structured Query Language)结构化查询语句：
→DDL(Data definition Language)：是针对表结构:
create(创建)、drop(删除)、alter(更新)、rename(重名名)、truacate(截断)
→DML(Data Manipulation Language):是针对表数据:
insert(插入)、delete(删除)、update(更新)
/**
在数据库系统中，SQL是不区分大小写（建议大写），
但字符串常量区分大小写，
SQL语句可单行或多行书写，以“;”结尾。
*/
--创建表
create table tb_user(
NAME varchar2(18),
sex char(3),
age int
);
--查询表
select * from tb_user;
--修改表 Alter
--增加两个字段 address phone
--就该表 表名 add(列 类型[长度], 列 类型[长度])
alter table tb_user add(address varchar2(100),
phone varchar2(21));
-- 删除字段
-- 修改 表 表名 drop(列)
alter table tb_user drop(adress);
-- 修改字段长度
-- 修改 表 表名 modify(列 类型[长度])
alter table tb_user modify(phone varchar2(50));
-- 修改列名
-- 修改 表名 rename column 旧列名 to 新列明
alter table tb_user rename column phone to iphone12s;
-- 修改表明
-- 改名 旧表明 to 新表明
rename tb_user to tb_person;
-- 删除表
-- 删除 表 表名
-- 注意：drop 会删除表结构和表所有数据，并且不能修改，慎用。
drop table tb_user;
-- 注释
-- 注释 on 表 表名 is '自己的注释'
comment on table tb_user is '用户表';
comment on column tb_user.name is '姓名';
-- 用户.表名[对象名]
-- 默认查询当前用户的表
selset * from tb_user;
-- 查询用户j1601的tb_user表
select * from j1601.tb_user;
-- 查询用户soctt的emp表
selsec * from soctt.emp;
数据字典主要有三种静态视图组成，每种应用在不同的范围
-DBA：所有方案中的对象
-ALL：用户所能访问的所有对象
-USER：在用户方案中的对象
-- 数据字典
-- dba：所有
-- all：用户所访问的所有对象
-- user：当前用户
select * from User_tables;
select * from all_tables;
select * from dba_tables;
-- 查询scott用户的dept表，前提是有查询soctt表的权限
select * from scott.dept;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
DML(Data Manipulation Language):是针对表数据:
insert、delete、update
/**
insert 插入
语法：insert into table [(column[,column...])]
values(value[,value...])
1、默认情况下，一次插入操作只插入一行（mysql一次可以插入多行）
2、插入的值只必须和要插入的字段数量，类型匹配，数据库中字符用单引号表示
3、如果为每列都指定值，则表名后不需要插入列名
*/
-- 标准插入方式
insert into tb_user(name,sex,age) values('jack','男'，20);
-- 不推荐的方式
insert into tb_user values('rose','女',19);
-- age没有插入则值为null(null不等于0，null表示什么都没有)
insert into tb_user(name,sex) values('tom','男');
/**
数据库查询过程：
1.客户端发送sql
2.DBMS(数据库管理员)检查sql
3.执行sql，去表空间（硬盘）提取数据
注意：上面的insert插入数据，此时的数据没有存在硬盘，
而是在内存中。
→如果：此时再打开一个客户端查询数据是不能查看到
刚才插入的数据的，因为它们在不同的Session(会话)中，是不能相互访问的
Transaction Control:事务控制
commit(提交)：就是将你的数据放到数据库(硬盘);
rollback(回滚)：
*/
-- 若执行commit将被保存到数据库
commit;
-- 若执行rollback将数据清除内存
rollback;
→ 事务：将多个sql语句看成一个整体，要么一起成功，要么一起回滚。
A.场景：
班长转账2000给肖老师
tb_account
name account amount
班长 001 50000
肖老师 002 5000000
1.班长的账户减去20000。
update tb_account set amount = amount - 20000 where name='班长';
2.肖老师的账户加上20000。
update tb_account set amount = amount + 20000 where name='肖老师';
B.事务的临界点(什么时候开始什么时候结束)。
.当一个commit/rollback 执行之后，到另外一个commit/rollback执行之前，
所有的都是一个事务。
C.一个事务commit/rollback执行之后，这个事务就结束了，不能再次commit/rollback。
D.Orcale当中，默认DDL语句自动提交数据(语句之后带了一个commit)，
DML语句需要手动提交数据。
/**
update 修改
语法：UPDATE table SET column = value [,column = value] [WHERE condition];
1.默认修改所有符合条件的数据
2.修改可以一次修改多行数据，修改的数据可用where子句限定
where 子句里是一个条件表达式，只有符合该条件的行才会被修改
3.也可以同时修改多列，多列的修改中间用(,)隔开
*/
-- 1.修改所有数据的age = 18
UPDATE tb_user SET age = 18;
-- 2.修改所有数据的age = 18，条件是男
UPDATE tb_user SET age = 18 WHERE sex = '男';
-- 3.修改多列
UPDATE tb_user SET age = 18, sex = '女' WHERE name = 'tom';
-- 4.多个条件使用and(并且)或着or(或者)
UPDATE tb_user SET age = 25 WHERE name = 'jack' AND sex = '男';
下面将修改name为jack或者sex为 男 的数据
UPDATE tb_user SET age = 23 WHERE name = 'jack' OR sex = '男';
-- 5.将字段数据修改为null
UPDATE tb_user SET age = null WHERE name = 'tome';
/**
delet 删除
语法：DELET FROM table [WHERE condition];
注意：delet 语句最小单位是行。
1.默认删除所有符合条件的数据。
2.删除可以一次删除多行数据，删除的数据可用where字句限定。
where 子句里是一个条件表达式，只有符合该条件的行才会被删除。
3.也可以同时修改多列，多列的修改中间用(,)隔开
*/
-- 1.删除所有数据
DELETE FROM tb_user;
-- 2.根据条件删除
DELETE FROM tb_user WHERE name = 'jack';
-- 3.根据条件删除，多个条件使用and(并且)或者or(或者)
-- 若条件不符合则不删除任何数据
DELETE FROM tb_user WHERE name = 'jack' AND sex = '女';
/**
DDL：TRUNCATE(截断),DDL不能回滚，因为默认执行commit
注意：TRUACATE是截断表的所有数据，ddl是针对表。
*/
TRUNCATE TABE tb_user;
/**
面试：
sql 语句删除数据有几种方式？ 分别是什么？
答：
1.delet
-- DML 语句，delet语句最小单位是行，也可以通过where删除，
删除数据可以回滚，保留表结构，最慢。
可以回滚 意味着删一条就要找地方保存一条。
2.truncate
-- DDL 语句，TRUNCATE 是截断表的所有数据，删除数据不可以回滚，
保留表结构，较快。
3. drop
-- DDL语句，删除表结构和表所有数据，并且不能回滚，慎用，最快。
*/
/**
数据完整性指所有存在数据库中的数据都是正确的状态。
如果数据库中存有不正确的值，那么我们称它为已丧失数据完整往往。
-- 不正确状态数据
如.表的字段为： name sex age adress
INSERT INTO tb_student(sex, age, adress) VALUES('男',25,'gz');
INSERT INTO tb_student(name, sex, age, adress) VALUES('aa', '妖', 250,'gz');
ALTER TABLE tb_student ADD (phone VARCHAR2(20));
UPDATE tb_student SET phone = '123456' WHERE name = 'jack' OR name = 'rose';
*/
/**
→约束：是在表上强制执行的校验规则，
约束主要用于保证数据库的完整性，
大部分数据库支持下面五类完整性约束：
① UNIQUE Key 唯一约束
② CHECK检查(NOT NULL 非空)
-- 测试非空约束，检查和唯一约束
-- 在建表时直接在列后面创建约束为：列级约束，
-- 数据库会默认给约束增加一个唯一键的名称SYS_XXX用于管理
CREATE TABLE tb_student(
name VARCHAR2(18) NOT NULL,
--检查约束若使用DEFAULET，则当不插入此字段时，为默认值，若下面默认'妖'会报错
sex CHAR(4) DEFAULT '男' CHECK(sex='男' OR sex='女')，
age INT CHECK(age>15 AND age <50),
adress VARCHAR2(20) UNIQUE
);
③ PRMARY KEY 主键
作用:主键是表中唯一确定一行数据的字段，
主键从功能上看相当于非空且唯一。
1.一个表中只允许一个主键
联合主键，主键字段可以是单字段或者是多字段的组合
2.现代数据库建模，建议一张表一定要有主键，并且主键应该和
业务数据无关，建议使用自动增长的自然数。
-- 为什么要有主键约束
唯一确定一条记录。
④ FOREGIN KEY 外键
格式FOREGIN KEY (外键列名) REFERENCES 主表(参照列)
作用：外键是构建于一个表的两个字段或者两个表的两个字段
之间的关系，解决数据冗余(多余)问题。
-- 为什么要有外键
CREATE TABLE tb_student(
ID PRIMARY KEY,
name VARCHAR2(18) NOT NULL,
sex CHAR(4) DEFAULT '男' CHECK(sex='男' OR sex='女')，
age INT CHECK(age>15 AND age <50),
adress VARCHAR2(20),
phone VARCHAR2(20) UNIQUE,
c_CODE VARCHAR2(10),
c_NAME VARCHAR2(18),
c_bzr VARCHAR2(18)
);
INSERT INTO tb_student
(id,name,age,adress,phone,c_CODE,c_NAME,c_bzr)
VALUES(1,'tom',25,'gz',123456,'j1601','结业','罗');
INSERT INTO tb_student
(id,name,age,adress,phone,c_CODE,c_NAME,c_bzr)
VALUES(2,'jack',25,'gz',123756,'j1601','结业','罗');
INSERT INTO tb_student
(id,name,age,adress,phone,c_CODE,c_NAME,c_bzr)
VALUES(3,'rose',25,'gz',123956,'j1601','结业','罗');
-- 这样插入数据会出现数据冗余(多余),因为后面三列完全重复
-- FOREIGN KEY 外键
外键是构建于一个表的两个字段或者两个表的两个字段
之间的关系，解决数据冗余(多余)问题。
CREATE TABLE tb_clazz(
ID INT PRIMARY KEY,
CODE VARCHAR2(10),
NAME VARCHAR2(18),
bzr VARCHAR2(18)
);
CREATE TABLE tb_student(
ID INT PRIMARY KEY；
name VARCHAR2(18) NOT NULL,
sex CHAR(4) DEFAULT '男' CHECK(sex='男' OR sex='女')，
age INT CHECK(age>15 AND age <50),
adress VARCHAR2(20),
phone VARCHAR2(20) UNIQUE,
-- 参照 主表(列)
FOREIGN KEY class_id INT REFERENCE tb_clazz(ID)
);
INSERT INTO tb_clazz(ID,CODE,NAME,bzr)
VALUES(1,'j1601','就业'，'习');
INSERT INTO tb_clazz(ID,CODE,NAME,bzr)
VALUES(2,'j1602','基础'，'关');
INSERT INTO tb_clazz(ID,CODE,NAME,bzr)
VALUES(3,'j1603','就业'，'胡');
INSERT INTO tb_student
(id,name,age,adress,phone,clazz_id)
VALUES(1,'tom',25,'gz',123456,1);
INSERT INTO tb_student
(id,name,age,adress,phone,clazz_id)
VALUES(2,'jack',25,'gz',123756',1);
INSERT INTO tb_student
(id,name,age,adress,phone,clazz_id)
VALUES(3,'rose',25,'gz',123956,1);
-- 1.子(从)表[tb_student]外键列[clazz_id]的值必须在
父(主)表[tb_clazz]被参照列[id]值的范围之内
-- 错误信息:未找到父项关键字，因为现在tb_clazz当中没有id为5的班级
-- 2.外键[class_id]参照的只能是主表[tb_clazz]的主键或者唯一建列
-- 保证子表记录可以准确定位到被参照的记录。
-- 3.当主表[tb_clazz]的记录被子表[tb_student]参照时，
主表记录不允许被删除。
-- 错误信息：已找到子记录
--解决方案1：先删除关联的子表数据，再删除主表数据
先删除关联的子表数据
DELETE FROM tb_student WHERE clazz_id = 1;
再删除主表数据
DELETE FROM tb_clazz WHERE ID = 1;
--解决方案2：先将关联的子表数据修改，再删除主表数据
UPDATE tb_student SET clazz_id = 2;
DELETE FROM tb_clazz WHERE ID = 1;
-- 解决方案3：先将关联的子表数据设置为null，再删除主表数据
UPDATE tb_student SET clazz_id = null;
DELET FROM tb_clazz WHERE ID = 1;
-- 4.建表时可以增加一下内容：
(1)ON DELETE CASCADE; 当父类中的行被删除时，
同时删除在子表中依靠的行
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
name VARCHAR2(18) NOT NULL,
sex CHAR(4) DEFAULT '男' CHECK(sex='男' OR sex='女')，
age INT CHECK(age>15 AND age <50),
adress VARCHAR2(20),
phone VARCHAR2(20) UNIQUE,
-- 参照 主表(列)
--当父类中的行被删除时，同时删除在子表中依靠的行
class_id INT REFERENCE tb_clazz(ID) ON DELETE CASCADE
);
(2)ON DELETE SET NULL;将依靠的外键值设置为空值
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
name VARCHAR2(18) NOT NULL,
sex CHAR(4) DEFAULT '男' CHECK(sex='男' OR sex='女')，
age INT CHECK(age>15 AND age <50),
adress VARCHAR2(20),
phone VARCHAR2(20) UNIQUE,
-- 参照 主表(列)
--当父类中的行被删除时，将依靠的外键值设置为空值
class_id INT REFERENCE tb_clazz(ID) ON DELETE SET NULL
);
/**
列级约束：
在建表时直接在列后面创建的约束为列级约束。
数据库会默认给约束增加一个唯一的名称SYS_XXX用于管理
表级约束：
在建表时直接在所有列创建完成后再创建约束称为表级约束
CREATE TABLE tb_student(
ID INT,
NAME VAHCHAR2(18),
sex CHAR(2) DAFAULT '男',
age INT,
adress VARCHAR2(100),
phone VARCHAR2(30),
clazz_id INT,
-- 表级约束
PRIMARY KEY(ID),
CHECK(NAME IS NOT NULL),
CHECK(sex = '男' OR sex = '女'),
CHECK(age>15 AND age<50),
UNIQUE(phone),
FOREIGN KEY (clazz_id) REFERENCES tb_clazz(ID),
-- 约束 约束名 约束类型(字段)
CONSTRAINTS tb_student_pk PRIMARY KRY(ID)
);
CREATE TABLE tb_student(
ID INT,
NAME VAHCHAR2(18),
sex CHAR(2) DAFAULT '男',
age INT,
adress VARCHAR2(100),
phone VARCHAR2(30),
-- 约束 约束名 约束类型(字段)
CONSTRAINTS tb_student_i PRIMARY KEY(ID),
CONSTRAINTS tb_student_c clazz_id INT,
CONSTRAINTS tb_student_n CHECK(NAME IS NOT NULL),
CONSTRAINTS tb_student_s CHECK(sex = '男' OR sex = '女'),
CONSTRAINTS tb_student_d CHECK(age>15 AND age<50),
CONSTRAINTS tb_student_u UNIQUE(phone),
CONSTRAINTS tb_student_f FOREIGN KEY (clazz_id) REFERENCES tb_clazz(ID),
);
*/
/**
注意：
推荐写法:外键使用表级约束，其他的都是用列级约束
*/
CREATE TABLE tb_student(
ID INT,
NAME VAHCHAR2(18),
sex CHAR(2) DAFAULT '男' NOT NULL,
age INT CHECK(age>15 AND age<50),
adress VARCHAR2(100),
phone VARCHAR2(30) UNIQUE,
-- 约束 约束名 约束类型(字段)
CONSTRAINTS tb_student_f FOREIGN KEY(clazz_id) REFERENCES tb_clazz(ID),
);
/**建表之后再建约束
注意：如果已经存在的数据和约束有冲突，需要首先搞定数据
*/
CREATE TABLE tb_student(
ID INT,
NAME VARCHAR2(20),
sex CHAR(2),
age INT,
adress VARCHAR2(50),
phone VARCHAR2(30),
clazz_id INT
);
-- 增加主键
如果下面id有重复则不能使用下面语句。
ALTER TABLE tb_student ADD PRIMARY KEY(ID);
ALTER TABLE tb_student COUSTANTS tb_student_pk PRIMARY KEY(ID);
-- 增加外键
ALTER TABLE tb_student ADD FOREGIN KEY(clazz_id);
-- 增加非空约束
ALTER TABLE tb_student ADD CHECK(NAME IS NOT NULL);
-- 增加检查约束
ALTER TABLE tb_student ADD CHECK(sex='男' OR sex='女');
-- 增加唯一约束
ALTER TABLE tb_student ADD UNIQUE(phone);
-- 删除约束
ALTER TABLE tb_student DROP CONSTRAINTS 约束名;
-- 禁用约束
ALTER TABLE tb_student DISABLE CONSTRAINT 约束名;
-- 激活约束
ALTER TABLE tb_student ENABLE CONSTRAINT 约束名;
-- 联合主键:主键字段可以是单个字段或者是多字段的组合。
财务报表
年 月 内容
2015 1
2015 2
2016 1
注意：联合主键只能做成表级约束
CREATE TABLE tb_ropt(
year CHAR(4),
month CHAR(2),
content VARCHAR2(50),
CONSTRAINTS tr PRIMARY KEY(year,month)
);
INSERT INTO tb_ropt(year,month,content) VALUES('2015','5','vv');
-- 字符串连接使用 //
scott.emp 表示用户scott的emp表
SELECT empno || '_' || ename FROM scott.emp
/**
空值是指不可用，未分配的值
空值不等于零或空格
任何类型都可以支持空值
包括空值的任何算术表达式都等于空
*/
-- nvl(first,second)函数, 如果第一个参数为null，
返回第二个参数，否则返回第一个参数
SELECT empno, ename, sal, comm, (sal+nvl(comm,0)) * 15 FROM scott.emp;
-- 字段别名
SELECT empno, ename, sal, comm, (sal+nvl(comm,0)) * 15 as 年薪 FROM scott.emp;
SELECT empno, ename, sal, comm, (sal+nvl(comm,0)) * 15 年薪 FROM scott.emp;
SELECT empno, ename, sal, comm, (sal+nvl(comm,0)) * 15 as "年薪" FROM scott.emp;
SELECT empno, ename, sal, comm, (sal+nvl(comm,0)) * 15 "年薪" FROM scott.emp;
注意:empno 称为 columnName no 称为 columnLable
jdbc中获取数据的方法getString(String columnLable),
如果没有别名则是empno，有别名则是no
SELECT empno AS NO FROM scott.emp;
-- 使用 DISTINCT 关键字可从查询结果中清除重复行
SELECT DINSTINCT deptno FROM scott.emp;
-- DINSTINCT 关键字作用于之后的所有字段
-- 意思是当deptno和ename字段一起重复时才去除重复字段
SELECT DINSTINCT deptno,ename FROM scott.emp;
-- WHERE 条件限制
-- 注意: WHERE必须出现在from后面 and or
SELECT * FROM scott.emp WHERE deptno = 20;
SELECT * FROM scott.emp WHERE deptno = 20 AND deptno = 30;
SELECT * FROM scott.emp WHERE deptno = 20 OR deptno = 30;
-- between and
SELECT * FROM scott.emp WHERE sal >=800 AND sal <=1600;
SELECT * FROM scott.emp WHERE sal BETWEEN 800 AND 1600;
-- in(list)
SELECT * FROM scott.emp WHERE deptno = 20 OR deptno = 30;
SELECT * FROM scott.emp WHERE deptno IN(20,30);
-- like 模糊查询 %(表示所有字符) _(表示一个字符)
-- 查询姓名字母是以"S"开头的
SELECT * FROM scott.emp WHERE ename LIKE 'S%';
-- 查询姓名字母是以"S"结尾的
SELECT * FROM scott.emp WHERE ename LIKE '%S';
-- 查询姓名字母包含"S"
SELECT * FROM scott.emp WHERE ename LIKE '%S%';
--查询第二个字母是"S"
SELECT * FROM scott.emp WHERE ename LIKE '_S%';
-- is null
SELECT * FROM scott.emp WHERE ename IS NULL;
SELECT * FROM scott.emp WHERE ename IS NOT NULL;
--- 优先级 and高于or
SELECT ename, job, sal
FROM scott.emp
WHERE job='SA' OR job='DF' AND sal>1500;
SELECT ename, job, sal
FROM scott.emp
WHERE (job='SA' OR job='DF') AND sal>1500;
SELECT ename, job, sal
FROM scott.emp
WHERE job='SA' OR (job='DF' AND sal>1500);
-- 结果排序 order by
-- ASC 升序是默认的 DESC降序
-- order by 必须出现再sql语句的最后一行
SELECT * FROM scott.emp ORDER BY sal;
SELECT * FROM scott.emp ORDER BY sal ASC;
SELECT * FROM scott.emp ORDER BY sal DESC;
-- 查询7369员工的所有数据
SELECT * FROM scott.emp WHERE empno = '7369'; -- 1
SELECT * FROM scott.dept; -- 4
-- 连两张表查询
SELECT *
FROM scott.dept, scott.emp
WHERE scott.emp.empno = '7396'; -- 4
-- 笛卡儿积：没有告诉数据库如何连接，数据库忽略了连接条件，查询一个交集
若dept表有4条记录，emp有14条记录，则下面会查出 4*14 条记录。
非标准的SQL语句
SELECT *
FROM scott.dept, scott.emp;
标准sql
SELECT *
FROM scott.dept CROSS JOIN scott.emp;
-- 内连接(等值连接)
-- 注意：多表连接查询时【第一件事】就是告诉数据库 关联条件(外键)
-- 两张表通常有一个关联条件，三张表通常有2个关联条件，
即多表连接的关联条件使N-1
SELECT *
FROM scott.dept ,scott.emp
WHERE scott.dept.deptno = scott.emp.deptno
ADN scott.emp.empno = '7369';
-- 通过别名简化
SELECT *
FROM scott.dept d, scott.emp e
WHERE d.deptno = e.deptno
AND e.empno = '7369';
-- 如果字段在两张表中只有一个就可以不用写表名
SELECT dname,loc,e.*
FROM scott.dept d,soctt.emp e
WHERE d.deptno = e.deptno
AND e.empno = '7369';
-- 标准sql
SELECT dname,loc,e.*
FROM scott.dept d INNER JOIN scott.emp e
ON d.deptno = e.deptno
WHERE e.empno = '7396';
-- 外键连接(非等值连接)：
用于查询一张表在另一张表中没有关联数据的信息
注意：下面的 + 号只有在Orcale中可以使用
-- 左外连接：+ 号在右边，左边表的所有数据显示，
如果右边表没有对应的数据，则补Null
SELECT *
FROM scott.dept d, scott.emp e
WHERE d.deptno = e.deptno(+);
-- 右外连接：+ 号在左边，右边表的所有数据显示，
如果左边表没有对应的记录，则补null
SELECT *
FROM scott.dept d, scott.emp e
WHERE d.deptno(+) = e.deptno;
-- 全外连接 左边的数据全显示，右边的数据全显示
SELECT *
FROM scott.emp e FULL OUTER JOIN scott.dept d
ON d.deptno = e.deptno;
-- 自连接
/**
在emp中的，每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工
自身也有自己的经理，下面我们需要将每一个员工自己的名字和经理的名字都找出来
这时我们该怎么做呢？
empno ename mgr mgrname
7369 SMITH 7902 FORD
*/
-- 表连接
-- 最快复制、备份数据的方法，该方法只是备份数据，并不复制表约束
CREATE TABLE tb_emp AS SELECT * FROM scott.emp;
/**
自连接(self join) 是SQL语句中经常需要的连接方式，
使用自连接可以将自身表的一个镜像当作另一个表来对待，
即将一张表看成多张表来做链接，从而能偶得到一些特殊的数据
*/
/**
聚合函数对一组值进行运算，并返回单个值，也叫组函数。
COUNT(*/列名) 统计行数
SUM(数值类型列名) 求和
AVG(数值类型列名) 平均值
MAX(列名) 最大值
MIN(列名) 最小值
聚合函数都会忽略NULL值
*/
注意：为了保证完整性，系统有约定俗成，在使用了聚合函数的查询语句中，
除了聚合函数，可以在查询列表上，要出现其它字段，那么该字段就必须为分组字段，
而且该字段一定要跟随在GROUP BY 关键字后面
SQL语句：
SELECT GradeId,sex,COUNT(*)
FROM student
GROUP BY Gradeld,sex
ORDER BY GradeId
WHERE:是针对数据表原始数据进行筛选
WHERE关键字只能出现一次，是先执行WHERE条件筛选后得到的结果集，
在进行GROUP BY 分组
HAVING核心：是针对分组统计之后的结果集，进行数据筛选
SQL语句：
SELECT GradeId， sex，COUNT(*) AS renshu
FROM student
GROUP BY GradeId, sex
HAVING COUNT(*)>3
ORDER BY GradeId
查询关键字家族成员：
SELECT TOP/DISTINCT 字段列表
FROM 表列表
WHERE 筛选条件
GROUP BY 分组字段
HAVING 对分组得到的结果集筛选
ORDER BY 排序字段
子查询：
-- 1.查询ALLEN的工资
SELECT sal
FROM scott.emp
WHERE ename = 'ALLEN';
-- 2.查询比ALLEN工资高的员工
SELECT *
FROM scott.emp
WHERE sal>1600;
-- 以上语句可以写成子查询
-- 子查询就是把多条语句写成一条，
子查询执行的时候会先执行子查询在执行主查询
SELECT *
FROM scott.emp
WHERE sal > (SELECT * FROM scott.emp WHERE ename ='ALLEN');
-- 子查询需要注意的问题
-- 单行子查询返回多行
下面会报错：
SELECT *
FROM scott.emp
WHERE sal >( SELECT MIN(sal) FROM scott.emp GROUP BY deptno );
-- IN 包含
SELECT *
FROM scott.emp
WHERE sal IN(SELECT MIN(sal) FROM scott.emp GROUP BY deptno );
-- ANY 比较的时候符合其中某一个即可 > ANY 大于最小的 < ANY 小于最大的
SELECT *
FROM scott.emp
WHERE sal ANY>(SELECT MIN(sal) FROM scott.emp GROUP BY deptno );
-- ALL 与子查询返回的所有值比较 >ALL 大于最大的 <ALL 小于最小的
SELECT *
FROM scott.emp
WHERE sal ALL>(SELECT MIN(sal) FROM scott.emp GROUP BY deptno );
-- dual 是Orcale的一个虚表
下面会返回一个 5
SELECT LENGTH('hello') FROM dual;
-- 常用函数
-- LOWER把大写转小写 UPPER把小写转大写
SELECT *
FROM scott.emp
WHERE ename = 'smith';
SELECT *
FROM scott.emp
WHERE LOWER(ename)='smith';
-- 小写转大写
SELECT UPPER('helloworld')
FROM dual;
-- 大写转小写
SELECT LOWER('HELLOWORLD')
FROM dual;
-- INITCAP 使所有单词首字母变为大写
SELECT INITCAP('sql course') FROM dual;
-- CONCAT 连接两个字符串
SELECT CONCAT('hello','world') FROM dual;
-- SUBSTR 取字符串，从start开始，取count个
SELECT SUBSTR('Helloworld',1,5) FROM dual;
-- INSTR(string,char) 在一个字符串中搜索指定的字符，
返回发现指定的字符的位置，从1开始；
SELECT INSTR('Helloworle','l') FROM dual;
-- RPAL 在列的右边粘贴字符 LPAL 在列的左边粘贴字符
下面判断 sal列的字符长度是否有八位，若没有则在右边补 * 直到长度为八位
SELECT RPAL(sal,8,'*') FROM scott.emp;
-- TRIM 删除首位空字符串
SELECT TRIM(' Helloworld ') FROM dual;
SELECT LENGTH(' Helloworld ') FROM dual;
SELECT LENGTH(TRIM(' Helloworld ')) FROM dual;
-- REPLACE('string','s1','s2')
-- string 希望被替换的字符串或变量
-- s1需要被替换的字符串 s2替换的字符串
SELECT REPLACE('HelloWorld','ll','H') FROM dual;
-- 数值函数，四舍五入
保留两位小数 45.93
SELECT Round(45.926,2) FROM dual;
-- 截断 45.92
SELECT TRUNC(45.926,2) FROM dual;
-- 取模 100 1600除以300剩余100
SELECT MOD(1600,300) FROM dual;
-- 查询系统时间
SELECT SYSDATE FROM dual;
-- 日期函数
CREATE TABLE tb_test(
currdate date
);
INSERT INTO tb_test(currdate) VALUES(SYSDATE); --插入当前日期
-- 将自定义日期格式字符串插入date类型的数据 to_date()日期转换函数。
INSERT INTO tb_test(currdate) VALUES(to_date('2012-09-30','yyyy/mm/dd'));
INSERT INTO tb_test(currdate) VALUES
(to_date('2012-09-30 09:12:12','yyyy/mm/dd hh:mi:ss'))
-- 日期函数
-- Orcale的日期类型时date 默认 yyyy/mm/dd hh:mi/ss
SELECT sysdate FROM dual;
-- 在日期上加上或减去一个数结果仍为日期
+10 加10天
SELECT sysdate + 10 FROM dual;
-- 三个转换函数
-- to_date to_char to_number
-- to_date(日期字符串，格式)
SELECT to_date('2012-07-30 12:12:12','yyyy/mm/dd hh:mi:ss')
FROM dual;
SELECT to_data('2012-07-30','yyyy/mm/dd') FROM dual;
-- to_char(日期，格式) 将日期转成字符
SELECT to_char(sysdate,'yyyy') FROM dual;
SELECT to_char(sysdate,'mm') FROM dual;
SELECT to_char(syadate,'yyyy/mm/dd') FROM dual;
-- 将数字转成字符
SELECT to_char(123) FROM dual;
SELECT to_char(123.123) FROM dual;
-- to_number(字符串) 将一个字符串的数字转成number类型
SELECT to_number('123') FROM dual;
-- 不能将非数字的字串转成number类型
SELECT to_number('abc') FROM dual;
-- 两个日期相减返回日期之间相差的天数
SELECT sysdate - to_date('2015-12-12','yyyy/mm/dd') FROM dual;
SELECT TRUNC(sysdate - to_date('2015-11-13','yyyy/mm/dd')) FROM dual;
SELECT to_date('2015-12-12 14:27:22','yyyy/mm/dd hh:mi:ss')-sysdate FROM dual;
-- 可以用数字除以24来向日期中加上或减去小时
SELECT sysdate + 2/24 FROM dual;
-- 可以用数字除以20再除以60来向日期中加入或减去分钟
SELECT sysdate + 2/24/60 FROM dual;
-- 返回d1，d2之间月的数量，d1，d2可为字符串
SELECT month_between(
to_date('2012-02-23','yyyy/mm/dd'),
to_date('2012-03-24','yyyy/mm/dd')
) AS FROM dual;
SELECT ID,
SUM(DECODE(NAME,'C语言'，score,0)) C语言，
SUN(DECODE(NAME,'VB',score,0)) VB,
SUM(DECODE(NAME,'网页编程',score,0))网页编程
FROM tb_exam
GROUP BY ID;
集合运算：从多个结果集中提取数据
-- 从多个结果集中提取数据
SELECT * FROM scott.dept;
SELECT * FROM sectt.emp;
-- UNION 从两个查询中返回不包括重复的那些行，类似distinct
-- 把两个结果集的数据合起来，然后干掉重复的
-- 10，20，30，40 两个查询中返回消除重复之后的结果
SELECT deptno FROM scott.dept -- 4
UNION
SELECT deptno FROM scott.emp --14
-- UNION ALL返回两个查询的结果，其中包括重复的
-- 把两个结果集的数据联合起来
--18
SELECT deptno FROM scott.dept
UNION ALL
SELECT deptno FROM scott.dept;
-- 返回两个查询的共同行 10，20，30
-- 把两个结果集的数据联合起来，
再找出两个查询中都出现的记录
SELECT deptno FROM scott.dept -- 4
INTERSECT
SELECT deptno FROM scott.emp; --14
-- 返回第一个查询中有但第二个查询中没有的行；40
SELECT deptno FROM scott.dept --4
MINUS
SELECT deptno FROM scott.emp;
练习：
R表： B表： 最终结果表
id R1 id R2 id R1 R2
1 hello 2 wel 1 hello null
2 world 3 nat 2 world wal
3 null nat
CREATE TABLE b(
ID NUMBER NOT NULL,
R2 VARCHAR(25) DEFAULT NULL,
PRIMARK KEY(ID)
);
INSERT INTO b(ID,R2) VALUES(2,'wel');
INSERT INTO b(ID,R2) VALUES(3,'nat');
CREATE TABLE r(
ID NUMBER NOT NULL,
R1 VARCHAR(25),
PRIMARY KEY(ID)
);
INSERT INTO r(ID,R1) VALUES(1,'hello');
INSERT INTO r(ID,R1) VALUES(2,'world');
l
SELECT r.id,R1,nvl(R2,'null') FROM r,b WHERE r.id=b.id(+)
UNION
SELECT b.id,R2,nvl(R1,'null') FROM r,b WHERE r.id(+) = b.id;
-- 标准的sql
SELECT r.id,R1,R2 FROM r LEFT OUTER JOIN b ON r.id = b.id
UNION
SELECT b.id,R2,R1 FROM r RIGHT OUTER JOIN b ON r.id = b.id;
-- 全外连接
SELECT r.id,b.id,R1,R2 FROM r FULL OUTER JOIN b ON r.id = b.id;
结果：
ID ID R1 R2
1 hello
2 2 world wel
3 nat
SELECT nvl(r.id,b.id) AS ID ,R1,R2 FROM r
FULL OUTER JOIN b ON r.id = b.id;
结果：
ID R1 R2
1 hell
2 world wel
3 nat
-- 查询的结果称为'结果集'
-- Orcale中的 rownum 伪列 '结果集' 中产生的序列
rownum作用：1、取前几条数据。2、分页
-- 在下面的结果集中deptno为20的rownum为2
SELECT rownum, deptno,dname FROM scott.emp;
结果为：
ROWNUM DEPTNO DNAME
1 10 A
2 20 B
3 30 C
4 40 D
SELECT rownum deptno dname FROM scott.emp WHERE deptno IN(20,30);
结果为
ROWNUM DEPTNO DNAME
1 20 B
2 30 C
/**
Orcale对rownum的处理：
rownum是在得到结果集的时候产生的，用于标记结果集中的一个字段，
这个字段被称为"伪序列"，也就是事实上不存在的一个数列，
它的特点是“顺序标记”而且“逐次递加”的，
换句话说就是只有存在rownum=1的记录才有可能存在rownum=2的记录,
假设我们的查询条件为rownum=2，那么在查询出第一条记录的时候
Orcale标记辞条记录为1，结果发现和rownum=2的条件不符，于是结果集为空
*/
SELECT * FROM scott.emp WHERE ROWNUM <=3;
-- 利用rownum分页
SELECT empno,ename FROM scott.emp; -- 15
-- 查询第6条到第10条的记录，下面不会出现记录
SELECT empno,ename FROM scott.emp
WHERE ROWNUM BETWEEN 6 AND 10;
SELECT * FROM
(
SELECT ROWNUM AS tempid,empno ename FROM scott.emp
)t
WHERE t.tempid BETWEEN 6 AND 10;
-- 练习：提取scott.dept 中第3条到第4条记录
SELECT * FROM
(SELECT rownum AS tempid,empno,ename FROM scott.dept
)t
WHERE t.tempid BETWEEN 3 AND 4;
/**
Orcale还提供了另外一个数列：rowid
rowid和rownum不同，一般来说一行数据对应一个rowid，而且是固定且唯一的，
在这一行数据存入数据库的时候就确定了，可以理解成java对象中的内存地址
可以利用rowid来查询记录，而且通过rowid查询记录是查询最快的查询方法
(有谁能记住18位长度的rowid字符)
rowid只有在表发生移动(比如表空间变化，数据导入/导出以后)，才会发生变化
*/
SELECT rowid,deptno,dname FROM scott.dept;
SELECT deptno,dname FROM scott.dept
WHERE rowid = 'AAAEgAAEAAAAAAQAAA';
-- 面试题
-- 删除重复数据
DROP TABLE tb_test;
-- 删除重复数据
CREATE TABLE tb_test(
name VARCHAR(18),
age number,
address VARCHAR2(18)
);
INSERT INTO tb_test(name,age,address) VALUES('tom',22,'广州');
INSERT INTO tb_test(name,age,address) VALUES('marry',23,'香港');
INSERT INTO tb_test(name,age,address) VALUES('tom',22,'香港');
NSERT INTO tb_test(name,age,address) VALUES('alice',22,'美国');
INSERT INTO tb_test(name,age,address) VALUES('tom',22,'广州');
NSERT INTO tb_test(name,age,address) VALUES('scott',18,'美国');
INSERT INTO tb_test(name,age,address) VALUES('scott',18,'广州');
/**
重复数据：tom 22[3] scott18[2]
1.删除所有重复数据
2.删除重复数据，但是保留一条(保留最大的rowid或者最小)
*/
①：删除所有重复数据
1、删除重复的首先判断哪些重复
SELECT NAME,age,COUNT(*)
FROM tb_test
GROUP BY NAME,age;
2、选出重复的列
SELECT NAME,age,COUNT(*)
FROM tb_test
GROUP BY NAME,age
HAVING COUNT(*)>1;
3、删除
DELETE FROM tb_test
WHERE NAME IN(
SELECT NAME
FROM tb_tset
GROUP BY NAMR,age
HAVING COUNT(*)>1
);
②：删除重复数据，但是保留一条(保留最大的rowid或者最小)
--1.通过创建临时表
-- 注意:当字段比较多(name,age,sex,adress,phone...),但是判断重复只是name和age字段，
就不能使用此方法
CREATE TABLE tb_tmp AS SELECT distinct name,age FROM tb_test;
TRUNCATR TABLE tb_test; --清空表记录
insert into tb_test(NAME,age) SELECT NAME,age FROM tb_tmp; --将临时表中的数据插回来
SELECT * FROM tb_tmp;
-- 这里max/min都可以
DELETE FROM tb_test WHERE rowid NOT IN
(SELECT MAX(rowid) FROM tb_test GROUP BY name,age);
DELETE FROM tb_test WHERE rowid NOT IN
(SELECT MIN(rowid) FROM tb_test GROUP BY name,age);
/**
Orcale中的对象：表、序列
对象的特点：可以给其他对象使用，名字不能重复
序列:自动生成的唯一序列号，常用在主键自动生成
*/
CREATE SEQUENCE s_tb_stuednt;
/
CREATE SEQUENCE S_TB_STUDENT
MINVALUE 1 --最小值
MAXVALUE 999999999999999 --最大值
START WITH 1 --从那里开始
INCEREMENT BY 1 --每次自增1
CACHE 20; --高速缓存：就是每次Orcale会生成20个序列
-- 删除 序列 序列名
DROP SEQUENCE s_tb_student;
-- 使用序列，序列有两个属性 currval,nextval
currval,SEQUENCE的当前值
nextval,SEQUENCE的下一个值
-- 必须要使用过(nextval),才能查询(currval)
-- 执行过nextval之后，该值就已经 使用过
-- 查询当前值currval时，序列必须使用过
SELECT s_tb_student.currval FROM dual;
SELECT s_tb_student.nextval FROM dual;
-- 使用序列
INSERT INTO tb_student(ID,NAME,sex)
VALUES(s_tb_student.nextval,'tom','男');
-- 序列作为一个对象，可以给多个table使用，建议一张表使用一个序列
INSERT INTO tb_clazz(ID,CODE)
VALUES(s_tb_student.nextval,'j1501');
注意：只要使用过序列之后，序列就会被用过，就意味着+1
即使回滚也不能回滚回来。
/**
面试：
如何优化你的数据库查询？
1.数据库查询的方式？
--全表扫描 select * from tb_student 慢
--利用索引扫描 快
--共享语句 最快(Orcale有个回滚段，临时表空间）
索引 index
作用：在数据库中用来加速对表的查询
原理：通过使用快速路径访问方法快速定位数值，减少了磁盘的I/O
特点：
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表删除时，该表上的索引自动被删除。
索引的创建：
自动：当一个表上定义一个 PRIMARY KEY 或者UNIQUE约束条件时，
数据库自动创建一个对应的索引
手动：用户可以创建索引以加速查询。
索引的维护：
1.建立索引后，查询的时候需要在where条件中带索引字段才可以使用索引
2.在经常使用的字段上建立索引，不要在所有字段上建立索引。
3.因为索引是用来加速查询的，如果一张表经常做insert、delet、update，
而很少做select，不建议建立索引，因为Orcale需要对索引进行额外的维护
如果一张表字段很少，不建议使用索引。
4.索引是由Orcale自动维护的，索引使用久了会产生索引碎片(磁盘碎片)，
影响查询效果，所以使用久了需要手动进行维护(删除在重建)。
SELECT * FROM tb_student;
-- 在tb_student表的name字段上面建立一个索引
-- 创建 索引 索引名 on 表(字段)
CREATE INDEX i_tb_student_name ON tb_student(name);
-- 查询的时候使用索引(where条件使用建立了索引字段)
SELECT * FROM tb_stude nt WHERE name = 'Alice';
-- 删除索引
DROUP INDEX i_tb_student;
<>不等于
SELECT * FROM scott.emp;
-- 创建 同义字 同义字名 for 来源于那个表
CREATE synonym tb_emp for scott.emp;
SELECT * FROM tb_emp;
-- 删除
drop synonym tb_emp;
-- 注意：同义字可以删除原表数据，前提是有权限，不建议
DELET FROM tb_emp;
-- 创建用户时通过dba角色赋予权限，而不是手动赋予权限，
则创建试图会报：没有权限
-- 解决方案：使用system用户登录，在创建j1509赋予权限
-- 同义字可以理解为表的别名，视图可以理解为sql语句的别名
-- 创建一个简单视图(一个表)
CREATE VIEW v_deptinof
AS
SELECT deptno AS 部门,count(*)人数,SUM(asl)总工资,AVG(sal)平均工资，
MAX(*)最高工资,MIN(*)最小工资
FROM scott.emp
GROUP BY deptno;
-- 使用试图
SELECT 部门 FROM v_deptinof;
-- 复杂视图(多个表)
CREATE VIEW v_info
AS
SELECT d.dname AS dname,d.loc AS loc,e.*
FROM scott.dept d,scott.emop e
WHERE d.deptno = e.deptno;
SELECT * FROM v_info;
-- 删除视图
DROP VIEW v_info;
-- 注意：可以通过视图操作表，但是不建议
/**
软件开发过程
1.需求调研
2.需求分析，将将现实中的动作模拟到计算机‘
数据建模
3.开发
4.测试
5.上线部署
三范式：
第一范式（确保每列保持原子性）：
第一范式是最基本的范式，如果数据库表中的【所有字段都是不可分解的原子值】，
就说明数据库满足了第一范式。
第一范式的合理遵循需要根据系统【实际需求】来定，比如某些数据库系统中需要用到
“地址”这个属性，本来直接将“地址”属性设计成一个数据表的字段就行。但是如果
系统经常会访问“地址”属性中的“城市”部分，那么就必须要将“地址”这个属性重
新拆分为省份、城市、等多部分进行存储，这样对地址中某一部分操作的时候将非常方
便。
第二范式（确保表中的每列都和主键相关）：所有非主关键字都会完全依赖于关键字(通常用于联合主键)
第二范式在第一范式的基础之上更进一层，第二范式需要确保数据库表中的每一列都和
主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。【也就是一个数据表中，一个表只能
保存一种数据，不可以把多种数据保存在同一张表数据库表中。】
比如要设计一个订单信息表，因为订单可能会有多种商品，所有要将订单编号和商品编号
作为数据表的联合主键
----------------------------------------------------------------------------
订单编号 商品编号 商品名称 数量 单位 单价 客户 所属单位 联系方式
001 1 挖掘机 1 台 1122 张三 天堂 123
001 2 冲击钻 1 把 11 张三 天堂 123
002 3 铲车 1 辆 56 李四 天堂 159
---------------------------------------------------------------------------
但是这样会产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、
单位、单价等信息不与该表主键相关，而仅仅是与商品编号相关。所以这里违反了第二范式的设计原则。
解决方案：
但是如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，
把订单项目也分离到另一个表中，就非常完美了。如：
订单信息表
---------------------------------------------
订单编号 客户 所属单位 联系方式
001 张三 天堂 123
220 李四 天堂 159
---------------------------------------------
订单项目表
---------------------------------------------
订单编号 商品编号 数量
001 1 1
001 2 1
002 3 1
----------------------------------------------
商品信息表
----------------------------------------------
商品编号 商品名称 单位 单价
1 挖掘机 台 1122
2 冲击钻 个 11
3 铲车 辆 56
----------------------------------------------
这样设计，很大程度减少了数据的冗余。如果要获取订单的商品信息，
使用商品编号到商品信息表中查询即可。
第三范式（确保每列都和主键列直接相关，而不是间接相关）：
第三范式需要确保数据表中的【每一列数据和主键直接相关，而不是间接相关】。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。
而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司）的字段。如下面这两个
表所示的设计就是一个满足第三范式的数据库。
订单信息表
-----------------------------------------------------------------
订单编号 订单项目 负责人 订单数量 客户编号
001 挖掘机 张三 1台 1
002 冲击钻 王五 2个 2
003 铲车 赵四 2辆 1
-----------------------------------------------------------------
客户信息表
-----------------------------------------------------------------
客户编号 客户名称 所属公司 联系方式
1 张四 天堂 147
2 刘明 天堂 125
-----------------------------------------------------------------
/**
PL/SQL 是Procedure Language & Structured Query Language 的缩写
PL/SQL是Orcale 数据库对SQL语句的扩展，增加了编程语言的特点。
数据操作和查询语句被包含在PL/SQL代码的过程单元中，
经过逻辑判断，循环等操作完成复杂的功能或者计算，
简单地归纳：PL/SQL = 传统SQL + 结构化流程控制
好处：
1.有结构化的流程控制，可以完成复杂操作
2.性能高于sql
sql：页面输入信息 -- servlet 接收数据，
调用持久化层生成sql - sql语句发送到数据库(DBMS)，
数据库编译 -- 执行sql。
plsql:页面输入信息 -- servlet接收数据，调用持久层传入参数(调用存储过程)
--执行sql，银行系统
3.可以对程序中的异常进行处理
*/
/**
PL/SQL 分为匿名块和命名块(存储过程、函数等)
PL/SQL 程序由三个块组成，即
声明部分：在此声明PL/SQL用到的变量，
类型及游标，以及局部的存储过程和函数
执行部分：过程及SQL语句，即程序的主要部分
异常处理部分：错误处理
*/
DECLARE
-- 声明部分，用来定义变量
BEGIN
-- 可以理解成java的花括号
EXCEPTION
-- 处理程序抛出异常
END；
/**
第一个PL/SQL，在控制台输出HelloWorld
*/
BEGIN
-- System.out.println("HelloWorld");
dbms_output.put_line("HelloWorld");
END;
/**
定义变量，变量的初始值都是null
常量 CONSTANT
*/
DECLARE
--变量名 类型 长度
v_name VARCHAR(18);
v_id INT := 1;
v_clzz CONSTANT CHAR(5) := 'jj';
BEGIN
v_name := 'jack';
dbms_output.put_line(v_id ||''|| v_name ||''|| v_clazz);
END;
/*****************************
集合变量(数据类型) VARRAY(不重要) TABLE(不重要) RECORD(重要)
1.包含多个内部组件，用于存放多个值
2.需要先定义类型，然后用该类型可以重复定义多个变量
注意：复合变量属于数据类型，定义时前面要加TYPE
TYPE是不能直接使用的，使用前需要定义变量引用
********************************/
DELCARE
-- 定义了一个RECORD类型，名字是v_tb_user
TYPE t_tb_user is RECODE
(
name VARCHAR2(18), -- 变量
sex CHAR(3),
age INT
);
-- 使用数据类型
-- v_id int
v_user t_tb_user;
BEGIN
v_user.name := 'jack';
v_user.sex := '男';
v_user.age := 20;
dbms_output.put_line(v_user.name ||''|| v_user.sex ||''|| v_user.age);
END;
-- DML
BEGIN
INSERT INTO tb_clazz(ID,CODE) VALUES(1,'j65');
COMMIT;
END;
-- select
/**
plsql是用来操作数据，不是用来查看的
1.select语句中缺少into子句
2.实际返回行数超出请求行数(提取所有数字需要游标)
3.未找到任何数据
*/
DECLARE
v_id INT;
v_code VARCHAR2(18);
BEGIN
SELECT ID, CODE INTO v_id,v_code FROM tb_user WHERE ID =1;
dbms_output.put_line(v_id ||''|| v_code);
END;
/**
%type
v_id tb_clazz.id%TYPE
v_id 变量和tb_clazz表的id列的数据类型、长度一致
*/
DECLARE
v_id tb_clazz.id%TYPE;
v_code tb_clazz.code%TYPE;
BEGIN
SELECT ID,CODE INTO v_id,v_code FROM tb_clazz WHERE ID = 1;
dbme_output.put_line(v_id ||''|| v_code);
END;
/**
%rowtype
rt_tb_clazz tb_clazz%ROWTYPE
rt_tb_clazz变量和tb_clazz表的表结构一致，相当于表结构的副本
*/
DECLARE
rt_tb_clazz tb_clazz%ROWTYPE;
BEGIN
SELECT ID,CODE INTO rt_tb_clazz.id,rt_tb_clazz.code FROM tb_clazz WHERE ID = 3;
dbms_output,put_line(rt_tb_clazz.id ||''|| rt_tb_clazz.code);
END；
-- 实际操作
DECLARE
rt_tb_clazz tb_clazz%ROWTYPE;
BEGIN
SELECT * INTO rt_tb_clazz FROM tb_clazz WHERE ID = 3;
dbms_output,put_line(rt_tb_clazz.id ||''|| rt_tb_clazz.code);
END；
-- 使用 RECODE
DELCARE
-- 定义了一个RECORD类型
TYPE t_tb_clazz is RECODE
(
ID tb_clazz.id%TYPE;
CODE tb_clazz.code%TYPE;
);
-- 定义一个变量
v_tb_clazz t_tb_clazz;
BEGIN
SELECT * INTO v_tb_clazz FROM tb_clazz WHERE ID = 3;
dbms_output,put_line(v_tb_clazz.id ||''|| v_tb_clazz.code);
END;
/*******************
if 分支
********************/
DECLARE
v_i number :=10;
BEGIN
if(v_i=10) then
dbms_output.put_line('进入if块');
end if;
END;
/**********************
if else 分支
***********************/
declare
v_i varchar(18):='a';
begin
if(v_i='b') THEN
dbms_output.put_line('进入if块');
else
dbms_output.put_line('进入else块');
else if;
end;
/*************************
if elsif else 分支
**************************/
declare
v_i varchar2(18):='c'; -- 任何类型变量，如果没有赋值，都是null
begin
if(v_i='a') then
null; -- 假设不想做任何动作给null，null用来保证数据完整
elsif(v_i='b') then
dbms_output.put_line('b');
elsif(v_i='c') then
dbms_output.put_line('c');
ELSIF(v_i IS NULL) THEN
dbms_output.put_line('v_i没有赋值');
else
dbms_output.put_line('进去else块');
end if;
end;
/*************************************
plsql的循环有三种：loop for while
**************************************/
-- loop
declare
v_i number :=0;
begin
dbms_output.put_line('循环开始之前...');
loop
v_i := v_i+1; --自增
dbms_output.put_line(v_i);
exit when v_i=5; --当v_i=5的时候结束循环
end loop;
dbms_output.put_line('循环结束之后...');
end;
-- 第二中结束方法
declare
v_i number :=0;
begin
dbms_output.put_line('循环开始之前...');
loop
v_i := v_i+1; --自增
dbms_output.put_line(v_i);
if(v_i=5) then
exit; --退出循环
end if;
end loop;
dbms_output.put_line('循环结束之后...');
end;
-- for
begin
dbms_output.put.line('循环开始之前...');
-- v_i是整数，for循环自动给v_i赋值
for v_i in 1..5
loop
dbms_output.put.line(v_i);
-- exit when v_i = 3;
end loop;
dbms_output.put_line('循环结束之后...');
end;
begin
dbms_output.put.line('循环开始之前...');
-- v_i是整数，for循环自动给v_i赋值
-- REVERSE 反转赋值，将值反转过来
for v_i IN REVERSE 10..50
loop
dbms_output.put.line(v_i);
-- exit when v_i = 3;
end loop;
dbms_output.put_line('循环结束之后...');
end;
-- while
declaer
v_i number :=0;
begin
dbms_output.put_line('循环开始之前...');
-- v_i 是整数，for循环自动给v_i赋值
while v_i <5
loop
dbms_output.put_line(v_i);
v_i := v_i +1;
end loop;
dbms_output.put_line('循环结束之后...);
end;
/**
1.预定义的Orcale数据库异常
有异常名，有错误代码，有异常信息
2.非预定义的Orcale数据异常
有错误代码，有异常信息，没有异常名
3.用户自定义的异常
违反用户自定义的业务逻辑，由程序主动触发
*/
/**
游标：用来提取数据
Orcale 打开一个工作区来保存多行查询的结果集，
游标就是给这个工作区命名的名称，
并能用于处理由多行查询而返回的记录行。
隐式游标：默认的DML语句和select语句都有隐式游标
显示游标：开发中给工作区命名，并且可以进行操作
%ISOPEN Boolean 游标打开，则返回True
%NOTFOUND Boolean 如果最近抓取没有获得记录，返回True
%FOUND boolean 如果最近抓取获得记录，返回True
%ROWCOUNT Number 返回到目前为止获取的记录数
使用游标的步骤：
1.定义游标 CURSOR c_tb_clazz IS SELECT * FROM tb_clazz;
2.打开游标 OPEN c_tb_clazz;
3.fetch游标 FETCH c_tb_clazz INTO r_tb_clazz;
游标有个指针，默认指向第一行之上，FETCH 将指针向下移动，指向第N行数据，
如果到末尾，会一直抓取最后一条数据
4.关闭游标 CLOSE c_tb_clazz;
*/
/**
例子：使用游标抓取tb_clazz的多所有数据
*/
DECLARE
-- 1.定义游标
CURSOR c_tb_clazz IS SELECT * FROM tb_clazz;
rt_tb_clazz tb_clazz%ROWTYPE;
BEGIN
-- 2.打开游标
OPEN c_tb_clazz;
-- 3.抓取数据
FETCH c_tb_clazz INTO rt_tb_clazz;
dbms_output.put_line(rt_tb_clazz.id ||''|| rt_tb_clazz.code);
FETCH c_tb_clazz INTO rt_tb_clazz;
dbms_output.put_line(rt_tb_clazz.id ||''|| rt_tb_clazz.code);
FETCH c_tb_clazz INTO rt_tb_clazz;
dbms_output.put_line(rt_tb_clazz.id ||''|| rt_tb_clazz.code);
FETCH c_tb_clazz INTO rt_tb_clazz;
dbms_output.put_line(rt_tb_clazz.id ||''|| rt_tb_clazz.code);
--4.关闭游标
CLOSE c_tb_clazz;
END;
-- 循环抓取
DECLER
COURSE c_tb_clazz IS SELECT * FROM tb_clazz;
rt_tb_clazz tb_clazz%ROWTYPE;
BEGIN
OPEN c_tb_clazz;
LOOP
FETCH c_tb_clazz INTO rt_tb_clazz;
EXIT WHEN c_tb_clazz NOTFOUND;
dbms_output.put_line(rt_tb_clazz.id ||''|| rt_tb_clazz.code);
END LOOP;
CLOSE c_tb_clazz;
END;
/**
带参数的游标
注意：传递的是形参，形参是不用长度的
*/
DECLER
CURSOR c_tb_clazz IS SELECT * FROM tb_clazz;
rt_tb_clazz tb_clazz%ROWTYPE;
-- 带参数的游标(参数类型是tb_clazz表的id字段类型)
CURSOR c_tb_student(v_clazz_id tb_clazz.id%TYPE)
IS SELECT * FROM tb_student WHERE clazz_id = v_clazz_id;
rt_tb_student tb_student%ROWTYPE;
BEGIN
OPEN c_tb_clazz;
LOOP
FETCH c_tb_clazz INTO rt_tb_clazz;
EXIT WHEN c_tb_clazz%NOTFOUND;
dbms_output.put_line(rt_tb_clazz.id ||''|| rt_tb_clazz.code);
-- 打印每个学生
OPEN c_tb_student(rt_tb_clazz.id);
LOOP
FETCH c_tb_student INTO rt_tb_student;
EXIT WHEN c_tb_student%NOTFOUND;
dbms_output.put_line(rt_tb_student.id ||''|| rt_tb_student.code);
END LOOP;
CLOSE c_tb_student;
dbms_output.put_line('*************************************');
END LOOP;
CLOSE c_tb_clazz;
END;
/**
联系
使用游标提取scott用户的dept的所有数据，同时提取每个部门的员工数据
*/
DECLARY
CURSOR c_dept IS SELECT * FROM scott.dept;
rt_dept scott.dept%ROWTYPE;
CURSOR c_emp(v_deptno scott.deptno%TYPE)
IS SELECT * FROM scott.emp WHERE deptno = v_deptno;
rt_emp scott.emp%ROWTYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO rt_dept;
EXIT WHEN c_dept%NOTFOUND;
dbms_output.put_line(rt_dept.deptno||' '||rt_dept.name);
OPEN c_emp(rt_dept.deptno)
LOOP
FETCH c_emp INTO rt_emp;
EXIT WHEN c_emp%NOTFOUND;
dbms_output.put_line(rt_emp.deptno||' '||rt_emp.name);
END LOOP;
CLOSE c_emp;
END LOOP;
CLOSE c_dept;
END;
-- 上面的简化写法
DECLARE
CURSOR c_dept IS SELECT * FROM scott.dept;
CURSOR c_emp(v_deptno scott.emp.deptno%TYPE)
IS SELECT * FORM scott.emp;
BEGIN
-- for v_i is 1..5
FOR r_dept IN c_dept
LOOP
dbms_output.put_line(r_dept.deptno||' '||r_dept.name);
FOR r_emp IN (r_dept.deptno) e_emp
LOOP
dbms_output.put_line(r_emp.deptno||' '||r_emp.name);
END LOOP;
END LOOP;
END;
/**
存储过程的作用：
1.（银行）将经常要执行的sql语句写成存储过程，存储在数据库，可以提高执行效率
2.存储过程真正的作用在于数据整合等复杂的业务操作。
在真正的数据开发领域当中，存储过程是用作数据整合，数据接口，数据备份
/**
备份表
*/
create table tb_tran(
id number primary key,
account varvhar2(18), -- 账户
amount number, -- 金额
currdate date
);
create sequence seq_tb_tran;
INSERT INTO tb_tran(id,account,amount,currdate)
VALUES(seq_tb_tran.nextval,'001',500,SYSDATE);
INSERT INTO tb_tran(id,account,amount,currdate)
VALUES(seq_tb_tran.nextval,'002',1500,SYSDATE);
INSERT INTO tb_tran(id,account,amount,currdate)
VALUES(seq_tb_tran.nextval,'003',2000,SYSDATE);
-- 备份表
CREATE TABLE tb_back AS SELECT * FROM tb_tran WHEN ID =999;
-- 存储每天的最大的ID的表
CREATE TABLE tb_maxid(
maxid INT
);
INSERT INTO tb_maxid(maxid) VALUES(0);
CREATE OR REPLACE PROCEDURE backpro(v_maxid INT)
IS
CURSOR c_tb_tran IS SELECT * FROM tb_tran WHERE id>v_maxid;
--标记变量
v_index INT :=0;
V_tempid tb_tran.id%TYPE;
BEGIN
-- 通过游标获取数据
FOR t_tb_tran IN c_tb_tran
LOOP
-- 备份
INSERT INTO tb_back(id,account,amount,currdate)
VALUES(t_tb_tran.id,t_tb_tran.account,t_tb_tran.amount,
t_tb_tran.currdate);
-- 分段提交
v_index := v_index+1;
IF(V_index = 1000) THEN;
COMMIT;
v_index :=0;
END IF;
END LOOP;
-- 查询到当天备份的最大id
SELECT MAX(id) INTO v_tempid FROM tb_back;
UPDATE tb_maxid SET maxid = v_tempid;
COMMIT;
END backpro;
/**
调用备份的存储过程
1.查询最大id
2.调用backpro
*/
CREATE OR REPLACE PROCEDURE callpro
IS
v_maxid tb_maxid.maxid%TYPE;
BEGIN
SELECT maxid INTO v_maxid FROM tb_maxid;
backpro(v_maxid);
END callpro;
-- 测试备份
BEGIN
callpro();
END;
-- 创建任务调度器
declare
jobno number;
begin
-- dbms_job.submit(job编号，调用那个存储过程，间隔时间);
dbms_job.submit(
jobno,
what => 'callpro;', -- 指定存储过程的名字
Interval => 'TRUNC(sysdate,"mi")+1/(24*60)' -- 定义时间间隔每分钟
);
commit;
end;
-- void add(STRING name,INT age);
-- 对象.add('hello',18);
-- 对象.add(NAME=>'hello',age=>18);
-- 删除调度任务定时器
begin
dbms_job.remove(21); -- 括号里为随机生成的编号
commit;
end;
5676
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
