ORACLE 数据库应用与开发 笔记期末

数据表对象

1：创建表

1.标准语句

create table 表名 （
列名 字符类型 约束
）;
可用default添加默认值 顺序：默认 非空

2.子查询建表

创建与原有表结构类似的新表

create table 子查询表名（ 列名） as 子查询语句

2：修改表

1.修改表名

rename 旧名字 to 新名字

2.增加列

alter table  表名  add (列名 字符类型 约束)

3.修改列

 alter table  表名  modify (列名 字符类型 约束)

4.删除列

alter table  表名 drop (列名)

3：删除表

1.drop table

将表结构 与 表内数据 一并删除

drop table 表名

2.truncate table

只删除数据，保留表结构

语句结构同上

4：约束

1.not null

非空约束 保证列必须有数据值

创建表之后未定义约束

 alter table  表名  modify 列名  not null

2.unique

限制数据的唯一性，约束的字段不能包含重复值，可以包含空值

 alter table  表名  add constraint 约束名  unique( 列名) 

3.primary key

只能有一个，不允许为空，限制数据的唯一性

[约束123]  都可在创建表时 直接按格式添加

4.foreign key

主表与从表之间数据的关联关系

主表：含引用码的表。 从表：含外部码的表。

创建职工表（employee），新增一列depeno用于存储职工所在部门号，此列引用部门表（department）的之间列deptno.

 CREATE TABLE employee(
  eno NUMBER(5) PRIMARY KEY,
  ename VARCHAR2(10) NOT NULL, 
deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES  department(deptno));
#REFERENCES用于指定外键列引用的主表

5.check

强制列数据必须要满足的条件

创建职工表（employee），其中年龄（age）列的取值必须在18~70之间。

CREATE TABLE employee(
  age NUMBER(2) NOT NULL CHECK(AGE BETWEEN 18 AND 70))

SQL 应用基础

1：基本查询语句

select 列名 from 表名

2：排序

select 列名 from 表名 where 条件 order by 字段 desc;#desc为降序，升序asc可省略

_可替换一个

%可替换多个

*3：多表查询

1.内连接（隐式好用）

select 表别名1.列名,表别名2.列名 from 表名1 表别名1,表名2 表别2 where 连接条件

2.外连接（左外）

select 表别名1.列名,表别名2.列名 from 表名1 表别名 1 left join 表名2 表别2 on 连接条件

左边是主表

3.（+）

放置在非主表一方

*4：子查询

单行运算符（>、=、>=，<，<>，<=）和多行运算符（IN，ANY，ALL）

单行子查询：单行比较符（>、<、=、>=、<=，<>）

多行子查询：返回多行数据

多行运算符：ALL：比较子查询返回的全部值

ANY：比较子查询返回的每个值

IN ：等于列表中的任何成员

ANY,ALL不能单独使用，只能与单行比较符结合使用

5：系统函数

1.ABS函数

绝对值

abs(数)

SELECT ABS(-5) FROM DUAL    锝5

2.round\trunc

round四舍五入

trunc直接截取

SELECT ROUND(89.985,-1) FROM DUAL;      90

6：字符函数

1.instr搜索位置

输出为7

2.substr 取字符串

输出：Jeff

7：日期时间

1.ADD_MONTHS

ADD_MONTHS(DATE,MONTHS)

date:指定日期 months:要加的月份数

给日期加上指定月份

 SELECT ADD_MONTHS('30-4月-2023',1)FROM DUAL;
输出：31-5月-2023

2.CURRENT_DATE**函数：**返回当前session所在时区的默认时间

sysdate默认时间

8：转换函数

TO_CHAR函数：将NUMBER转换为字符串

TO_DATE函数：将NUMBER、CHAR或VARCHAR2转换为DATE

TO_NUMBER函数：用于将字符串转换 成数字格式

9：分组函数

GROUP BY*语句：按照指定的列进行数据分组

SELECT columns,group_function
FROM table
[WHERE condition]
[GROUP BY columns]
[HAVING group_condition]
#group_function  指定统计函数
columns             指定分组依据的列/分组条件
group_condition过滤分组后的数据显示/组过滤条件
HAVING语句必须与GROUP BY一起使用。
​
    查询并显示部门平均工资2000以上的部门雇员人数及平均工资(截取保留两位小数)。
SELECT deptno 部门号,COUNT(ename) 雇员数,TRUNC(AVG(sal),2) 平均工资
     FROM emp
     GROUP BY deptno
     HAVING TRUNC(AVG(sal),2)>2000;

​
​

SELECT语句中出现的列必须出现在GROUP BY语句中

ORDER BY用于对查询结果进行排序，必须放在分组语句之后

10：数据操作

1.插入数据 INSERT语句

INSERT INTO <表名> [(<列名表>)] VALUES （值表即数据）

值表中的值与列名表中的列按位置顺序对应，数据类型必须一致

按指定列插入数据：按照指定列的次序为相应列提供插入数据。

 INSERT INTO department(deptno,deptname,location)
  VALUES(10,'市场一部','天津'); 
​

插入所有列数据：可以不指定列名表，在VALUES子句中为每个列提供数据，且数据顺序必须与表列顺序完全一致。

 INSERT INTO department
    VALUES(11,'市场二部','天津'); 

缺省数据列：某些列被定义为DEFAULT。

 INSERT INTO department
    VALUES(12,'市场三部',DEFAULT); 

插入日期数据：日期值必须匹配于日期格式和日期语言。

INSERT INTO employee
    VALUES(10001,'王刚',27,3400,NULL,SYSDATE,10);

2.更新数据

使用表达式更新数据 
     

  UPDATE <表名> 
        SET <列名＝列值> [,… ]
        [WHERE <更新条件>

3.删除数据

DELETE [ FROM ] <表名>
[WHERE <删除条件>

 视图与序列

1.视图

1.作用：

简单性、安全性、逻辑数据独立性

2.分类：

简单、复杂、连接（在here语句条件中指定有效的连接）、只读

*3.对应DML操作

简单视图：可以执行查询、插入、删除、更新操作

复杂视图：可以执行查询，不可以插入、删除、更新操作

连接视图：可以执行查询，不可以插入，在满足约束条件下，可更新、删除

只读视图：仅查询

4.建立视图

5.check约束

WITH CHECK OPTION
​
基于emp表建立视图，显示工资在1500以上雇员号，雇员名，工资，部门号。
CREATE VIEW vw_emp6 AS
       SELECT empno,ename,sal,deptno FROM emp
       WHERE sal>1500
       WITH CHECK OPTION CONSTRAINT ck_sal;

新数据在WHERE列上的值必须满足WHERE的条件限制。 见第三章约束

6.修改视图

加上 orr replace

create or replace .......

2.序列

创建一个由1000开始，最大值为9999999，间隔为1的序列。
CREATE SEQUENCE seq_first
INCREMENT BY 1 START WITH 1000 MAXVALUE 9999999 NOCYCLE;

事务管理与并发控制

1.事务提交

commit;

2.回滚

rollback

3.保存点

savepoint

是否回到指定位置

用户管理与安全

授权

grant 权限 to 用户

回收

revoke 权限 from 用户