DDL操作：
- 表创建，掌握
- 表的修改，掌握一个约束添加
- 表的删除，掌握
- 其它DDL语句
创建表
- 一般创建
- 使用子查询创建表
- 语法：
CREATE TABLE 表名 AS 子查询; CREATE TABLE table[column(, column...)] AS subquery; - 注：只有非空约束会包含，其它约束会丢失
- 语法：
eg：
CREATE TABLE s_emp45
AS SELECT id,first_name,dept_id,salary
FROM s_emp
WHERE dept_id=45;复制表
CREATE TABLE s_emp1
AS SELECT *
FROM s_emp;- 表的修改（表很大了才使用）：了解（只用记住增加约束即可，其它用时查看就行）
- 增加列：
ALTER TABLE 表名 ADD ( column 类型 默认值 非空约束， ... ); --只能加非空约束 - 修改列
ALTER TABLE 表名 MODIFY ( column 类型 默认值 非空约束， ... ); /* 列宽，默认值，非空约束之类的修改 注：只要不和原来数据冲突的修改都可以 */ - 删除列
ALTER TABLE 表名 DROP column 列名; - 增加约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型(修饰的列); //注：添加的是表级别约束，所以不能添加非空约束 //在创建表时，先不加外键约束，这样表的创建没有顺序要求 //所有表创建完后，在单独添加外键约束 - 删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名 [CASCADE]; cascade:级联删除约束（把相关的约束一起删掉） - 约束失效
ALTER TABLE 表名 DISABLE CONSTRAINT 约束名 [CASCADE]; //cascade:级联失效 - 约束生效
ALTER TABLE 表名 ENABLE CONSTRAINT 约束名 [CASCADE]; //cascade:级联生效 - 约束不可以修改
- 增加列：
- 删除表
- 删除表
DROP TABLE 表名 [CASCADE CONSTRAINTS]; - 重命名对象：
RENAME oldname TO newname; - 清空表：DDL是不能回滚的
TRUNCATE TABLE 表名; - TRUNCATE vs DELETE：
- TRUNCATE不可回滚， DELETE可以回滚
- TRUNCATE立刻释放表空间， DELETE不会，COMMIT是释放
- TRUNCATE比较快， DELETE在大数据量时没有TRUNCATE快
- TRUNCATE只能删除表， DELETE语法中还可以使用视图等
- DELETE FROM 表名; (DML)
- 删除表
数据字典
- 数据库中的表，一部分是用户自己创建的，用户表
- 还有一部分是安装时就创建了，为了维护数据库的正常运行，称为数据字典表
分类：
- USER开头，当前用户下各种信息相关的表，比如USER_TABLES（开发人员主要使用）
- ALL开头，所有有权限看到的用户的各种信息相关的表
- DBA开头：所有对象对应的数据字典表（管理员）（DBA人员使用）
- V$：描述系统性能相关的数据字典表（DBA人员使用）
- DICTIONARY：一个特殊的数据字典表-用来描述数据字典表相关信息的数据字典表
- TABLE——PRIVILEGES：数据表权限
一些常用字典表：
- USER_TABLES：当前用户下的表相关的信息
- eg:
SELECT table_name FROM user_tables; //查询当前用户下所有表的名字
- eg:
- USER_USERS：当前用户的相关信息
- eg:
show user; //这是一个命令 SELECT username FROM user_users; //查看当前用户的名字
- eg:
- USER_CONSTRAINTS：当前用户约束的相关信息
- 能查到约束名，以及对应的表名
- eg：
SELECT constraint_name, table_name FROM user_constraints;
- USER_CONS_COLUMNS:
- 能查到约束名，对应的表名，对应的列名
- eg：
SELECT constraint_name,table_name,column_name FROM user_cons_columns;
- USER_VIEWS：视图
- USER_SEQUENCES：序列
- USER_INDEXES：索引
常用数据库对象
1.序列：SEQUENCE
- 为了达到列的自动增长，使用序列
- 序列：就像一个取号机，取得好可以一次递增
- 通常：会为每一个表创建一个序列
创建语法：
CREATE SEQUENCE 序列名
START WITH n --以n开始，默认从1开始
INCREMENT BY n --每次增加n，默认每次加1
MAXVALUE n|NOMAXVALUE --上限，默认无上限
MINVALUE n|NOMINVALUE --下限，默认无下限
CYCLE|NOCYCLE --是否是循环的，默认不循环
CACHE n|NOCACHE; --缓存n个数，默认缓存20个
--注：这些参数无前后顺序
给学生表创建序列：
CREATE SEQUENCE student_id_seq
START WITH 3
INCREMENT BY 1; 序列的使用：
- 序列名.NEXTVAL：取序列的下一个值
SELECT student_id_seq.NEXTVAL from dual; - 序列名.CURRVAL：取到序列刚刚取过的值，如果本次连接中没有取过，会报错
INSERT INTO student (id, name, sex, age, sno)
VALUES (student_id_seq.NEXTVAL,'bb',1,15,3);修改序列：了解
ALTER SEQUENCE 序列名
...;只要起始值不能修改
删除序列：了解
DROP SEQUENCE 序列名
...;
2.视图：VIEW
- 定义视图，只包含源表部分字段，源表隐藏，此时利用视图查看源表的数据
- 只能视图中包含的数据，达到只能看到公开的数据
- 定义视图，我们经常多表联查，使用频率很高，写起来麻烦
- 可以针对多表联查定义一个视图
- 查看多表数据通过视图即可，简化语句
- 本质上：视图就是一个起了名字的查询语句，没有存储数据的功能
- 查看视图，实际上还是查看源表
视图分类：
- 简单视图：数据来源于一张表，不能使用函数和分组，默认可以增删改查
- 复杂视图：数据来源于多张表，或者包含函数，分组等等，只能读
创建
CREATE VIEW 视图名
AS 子查询;管理员授予创建视图权限：CREATE VIEW
GRANT CREATE VIEW TO 用户名;eg:为了能够45号部门员工看到他们的信息，定义一个视图
CREATE VIEW s_emp45
AS SELECT id,first_name,title,dept_id,salary
FROM s_emp
WHERE dept_id=45;基于视图查询：
SELECT 字段
FROM 视图名;OR REPLACE：如果不存在，创建，如果已存在，则替换
CREATE OR REPLACE VIEW s_emp45
AS SELECT id,first_name,title,dept_id,salary
FROM s_emp
WHERE dept_id=45;FORCE：如果创建视图时，表存在，force强制创建，但是不能使用，默认是NOFORCE
WITH READ ONLY：只能通过视图读，不能DML
CREATE OR REPLACE VIEW s_emp45
AS SELECT id,first_name,title,dept_id,salary
FROM s_emp
WHERE dept_id=45
WITH READ ONLY;WITH CHECK OPTION：如果简单视图可以增删改，加了这个选项后，只能改变成不违背WHERE的值，否则无法改变
CREATE OR REPLACE VIEW s_emp45
AS SELECT id,first_name,title,dept_id,salary
FROM s_emp
WHERE dept_id=45
WITH CHECK OPTION;UPDATE s_empv45
SET dept_id=50 --违背了WHERE的值，会报错
WHERE id=10;
表空间和权限控制
表空间
- 数据库物理文件的逻辑映射
- 一个表空间可以对应一个或多个文件，是最大的逻辑结构
- 数据库逻辑结构从大到小：表空间，段，区，块
- 一个数据库可以有多个表空间
- 在数据库默认有一些表空间：
- SYSTEM：在安装或创建数据库时就创建了，用来存储系统的数据
- 默认情况下，创建的用户使用的表空间就是SYSTEM
- TEMP：是一个临时表空间，用来存储一些临时数据，系统临时数据都存储在temp临时表空间中
- SYSAUX：SYSTEM辅助空间
- ...
- SYSTEM：在安装或创建数据库时就创建了，用来存储系统的数据
- 查找用户对应的表空间和临时表空间：凡是dba开头的表都需要管理员权限
SELECT username,user_id,default_tablespace,temporary_tablespace FROM dba_users; - 查找数据文件路径：需要管理员权限
SELECT file_name FROM sys.dba_data_files; - 创建表空间：
CREATE TABLESPACE 表空间名 DATAFILE '路径' SIZE 起始大小 AUTOEXTEND ON NEXT 每次扩展大小 MAXSIZE 上限 EXTENT MANAGEMENT LOCAL;- eg：在Y盘创建一个oracle_data的文件目录，在该目录创建表空间
CREATE TABLESPACE test_dataspace DATAFILE 'Y:\oracle_data\test_data.def' SIZE 256M AUTOEXTEND ON NEXT 5M MAXSIZE 512M EXTENT MANAGEMENT LOCAL;
- eg：在Y盘创建一个oracle_data的文件目录，在该目录创建表空间
- 创建临时表空间
CREATE TEMPORARY TABLESPACE 临时表空间名 TEMPFILE '路径' SIZE 起始大小 AUTOEXTEND ON NEXT 每次扩展大小 MAXSIZE 上限 EXTENT MANAGEMENT LOCAL;- 在刚创建的oracle_data目录中创建临时表空间
CREATE TEMPORARY TABLESPACE test_tempspace TEMPFILE 'Y:\oracle_data\test_data.def' SIZE 20M AUTOEXTEND ON NEXT 5M MAXSIZE 100M EXTENT MANAGEMENT LOCAL;
- 在刚创建的oracle_data目录中创建临时表空间
- 创建用户时：如果没有指定表空间和临时表空间，则默认使用SYSTEM，TEMP
CREATE USER 用户名 IDENTIFIED BY 密码; - 指定表空间和临时表空间
CREATE USER 用户名 IDENTIFIED BY 密码 DEFAULT TABLESPACE 表空间 TEMPORARY TABLESPACE 临时表空间;- eg：
CREATE USER test1 IDENTIFIED BY test DEFAULT TABLESPACE test_dataspace TEMPORARY TABLESPACE test_tempspace;
- eg：
- 删除表空间：
DROP TABLESPACE oaecspace INCLUDING CONTENTS AND DATAFILES;
权限控制
- CREATE TABLE
- CREATE VIEW
- CREATE SESSION
修改密码：（要么是管理员，要么是当前用户）
ALTER USER 用户名 IDENTIFIED BY 密码;
权限很多种，要给一个用户授权，可能要同时授予几十个等等，要给多个用户都授予这些权限等等，都很麻烦
角色：可以包含多个权限
可以根据需要定义角色，授予权限，在需要时给用户授予角色（对应的权限）即可
系统默认有三个角色：
- CONNECT
- RESOURCE
- DBA
授权：
GRANT 权限,...
ON 对象
TO 用户/角色/PUBLIC
[WITH GRANT OPTION];
/*
PUBLIC：所有用户
WITH GRANT OPTION：权限的授予这还可以把权限授予其他用户
[]代表可选
*/回收权限：
REVOKE 权限,...
ON 对象
FROM 用户;eg
--授权
GRANT connect TO test1;
--回收
REVOKE connect FROM test1;同义词：起个小名
--创建同义词
CREATE [PUBLIC] SYNONYM synonym_name FOR object_name;
--public：其它用户可以使用这个名字
--删除同义词
DROP SYNONYM synonym_name;
==========================
SQL：
- PL/SQL：过程化的SQL，是一门编程语言
- 可以顺序结构，条件结构，循环结构，对象...
- 定义函数，存储过程（面试可能涉及到）......
341
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
