数据库对象:
表名和列名命名规则: - 字母开头
- 不超过30个字符
- 只能包含大小写字母,0-9,_,$,#
- 同一用户下对象名称不能重复
- 不能使用保留字
1、创建表
- 必须要有创建表的权限及存储区域
- 需要指定表名,列名,列数据类型及长度
访问其他用户的表需要加上用户名前缀。 创建表的时候可以为字段指定默认值,具体用法如下: - 字变量,表达式或者SQL函数是合法的默认值
- 其他字段名称或者伪列是非法的默认值
- 默认值的类型要与字段的类型一致
- 插入的时候default表示使用默认值
例子:创建一个入职时间表,其中入职时间字段使用系统日期为默认值 SQL> create table hire_dates( 2 id number(8), 3 hire_date date default sysdate); Table created. 例子:创建一个dept表,其中创建时间字段使用系统日期为默认值 SQL> create table dept( 2 deptno number(2), 3 dname varchar2(14), 4 loc varchar2(13), 5 create_date date default sysdate); Table created. 使用desc查看表的结构 SQL> desc dept; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) CREATE_DATE DATE 2、数据类型
其中: - 通过子查询创建的表,long类型列不能被复制
- long类型列不能位于group by和order by子句中
- 一个表中只能有一个long类型列
- long类型列上面不能加约束
- CLOB是不能指定大小
日期时间数据类型
timestamp语法:
其中: - timestamp包含年月日时分秒及微秒,他是date类型的一个扩展
- timestamp with time zone带有时区
- timestamp with local time zone根据当前session的时区进行变化
interval语法:
其中: interval year to month表示间隔多少年和月 interval day to second表示间隔多少天、小时、分钟和秒 例子:创建一个药品表,包含保质期字段 SQL> create table drug( 2 name varchar2(20), 3 grantee_time interval year to month); Table created. 查看表结构,可以看到精度默认为2位 SQL> desc drug Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(20) GRANTEE_TIME INTERVAL YEAR(2) TO MONTH 插入数据,其中'10-2'表示10年2个月 SQL> insert into drug values('aspinlin','10-2'); 1 row created. 查看到期时间 SQL> select name,sysdate,sysdate+grantee_time from drug; NAME SYSDATE SYSDATE+GRAN -------------------- ------------ ------------ aspinlin 27-OCT-15 27-DEC-25 interval举例:
3、约束 约束是表级上的一个规则,阻止我们对表依赖性的破坏,约束类型:
约束使用指导: - 可以命名一个约束,也可以不指定,使用Oracle自动产生的以sys_c开头的名字
- 既可以在创建表的时候创建约束,也可以在创建表之后再创建约束
- 既可以在表级创建约束,也可以在列级创建约束,一次只能指定一个约束
- 复合约束(包含多列)需在表级创建
- 可以通过数据字典查看约束
定义约束语法:
创建列级约束:
创建表级约束:
例子:创建列级约束 SQL> create table emp1( 2 employee_id number(6) constraint emp1_id_pk primary key, 3 first_name varchar2(20)); Table created. 例子:创建表级约束 SQL> create table emp2( 2 employee_id number(6), 3 first_name varchar2(20), 4 job_id varchar2(10) not null, 5 constraint emp2_id_pk primary key(employee_id)); Table created. (1)非空约束(not null) - 字段的值不能为null
- 只能在列级创建,不能在表级创建
例子:非空约束创建在表级会报错 SQL> create table emp3( 2 employee_id number(6), 3 first_name varchar2(20), 4 job_id varchar2(10) not null, 5 constraint emp2_name_nn not null(first_name)); constraint emp2_name_nn not null(first_name)) * ERROR at line 5: ORA-00904: : invalid identifier (2)唯一约束(unique) - 字段的值不能相同
- 但是可以为null
- 可以创建在列级,也可以创建在表级
- 唯一约束隐式创建唯一索引
例子:创建唯一约束 SQL> create table emp4( 2 employee_id number(6) unique, 3 email varchar2(25), 4 constraint emp4_email_uk unique(email)); Table created. 创建了2个唯一约束,一个列级,没有指定名字,一个表级,指定了名字。 插入null SQL> insert into emp4 values(null,null); 1 row created. SQL> select * from emp4; EMPLOYEE_ID EMAIL ----------- ------------------------- SQL> select count(*) from emp4; COUNT(*) ---------- 1 (3)主键约束(primary key) - 字段的值不能为null,也不能相同
- 一张表只能有一个主键约束
- 主键约束隐式创建唯一索引
(4)外键约束(foreign key) - 列级创建不需要foreign key关键字,表级创建必须指定,使用references关键字指定父表及参考列,如果父表有主键,则可以不指定参考列,默认为主键,如果父表没有主键,则必须指定参考列
- 子表外键的值必须存在于父表中或者为null
- 子表的外键列须是父表的主键或唯一键
- 为子表的列增加外键约束后,默认情况下不允许删除或更新父表相关列值,以确保存参照完整性
- on delete cascade允许删除父表相关记录,同时子表对应记录也将被删除
- on delete set null允许删除父表相关记录,同时子表对应记录被置为null
例子:创建外键 SQL> create table emp5( 2 employee_id number(8) primary key, 3 manager_id number(8) constraint emp5_emp5_fk references emp5(employee_id), 4 department_id number(4), 5 constraint emp5_dept_fk foreign key(department_id) references departments(department_id)); Table created. 创建了2个外键,一个在列级创建,参考自己的主键,一个在表级创建,参考departments表的主键,由于都有主键,都可以不指定父表的参考列。 例子:参照完整性 先创建2个表,emp6的deptid列参考dept6的deptid列 SQL> create table dept6( 2 deptid number constraint dept6_id_pk primary key, 3 deptname varchar2(20)); Table created. SQL> create table emp6( 2 empid number, 3 last_name varchar2(20), 4 deptid number, 5 constraint emp6_id_pk primary key(empid), 6 constraint emp6_dept6_fk foreign key(deptid) references dept6); Table created. 现在2个表没有数据,先向表emp6插入数据 SQL> insert into emp6 values(100,'aaa',10); insert into emp6 values(100,'aaa',10) * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP6_DEPT6_FK) violated - parent key not found 报错,提示违法约束完整性,没有找到父键,这里插入的deptid的值为10,但是在表dept6里面没有deptid为10的记录,向往表dept6里面插入一条记录。 SQL> insert into dept6 values(10,'sales'); 1 row created. 再次执行刚才插入表emp6的语句就可以了 SQL> insert into emp6 values(100,'aaa',10); 1 row created. 那么现在来删除父表dept6的这条记录 SQL> delete from dept6 where deptid=10; delete from dept6 where deptid=10 * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP6_DEPT6_FK) violated - child record found 报错,提示违法约束完整性,在子表找到相应的记录。 先删除约束再重新创建,增加on delete cascade关键字 SQL> alter table emp6 drop constraint emp6_dept6_fk; Table altered. SQL> alter table emp6 modify(deptid constraint emp6_dept6_fk references dept6 on delete cascade); Table altered. 再次来删除父表dept6的这条记录就可以了,同时子表的这条记录也会被删除,先看一下删除之前表的记录。 SQL> select * from emp6; EMPID LAST_NAME DEPTID ---------- -------------------- ---------- 100 aaa 10 SQL> select * from dept6; DEPTID DEPTNAME ---------- -------------------- 10 sales 执行删除 SQL> delete from dept6 where deptid=10; 1 row deleted. 再看一下这2张表的记录,emp6D的记录也被级联删除了 SQL> select * from emp6; no rows selected SQL> select * from dept6; no rows selected (5)check约束 - 定义一个字段必须满足的条件
- 不允许使用如下伪列:currval,nextval,level,rownum
- 不允许使用如下函数:sysdate,uid,user,userenv
- 不允许参照其他行的值
- 可以在表级也可以在字段级定义
例子:创建一个check约束,薪水必须大于0 SQL> create table emp7( 2 employee_id number, 3 salary number constraint emp7_salary_ck check(salary>0)); Table created. 插入一条薪水小于0的记录,报错,违法check约束 SQL> insert into emp7 values(100,-2000); insert into emp7 values(100,-2000) * ERROR at line 1: ORA-02290: check constraint (HR.EMP7_SALARY_CK) violated 4、使用子查询创建表 - 可以使用AS关键字加子查询创建表并插入数据
- 表的字段要和子查询的字段进行匹配,个数相等,类型相同
- 表后面定义了字段名字,那么子查询里面的表达式就不要定义别名,如果表后面没有定义,那么子查询里面的表达式就必须定义别名
- 列数据类型和非空约束将会继承到新表,其他约束不继承
例子:使用子查询创建表,单独使用表达式报错 SQL> create table dept80 2 as 3 select employee_id,last_name,salary*12,hire_date 4 from employees 5 where department_id=80; select employee_id,last_name,salary*12,hire_date * ERROR at line 3: ORA-00998: must name this expression with a column alias 表后面定义了字段名字就可以了 SQL> create table dept80(empid,name,annsal,hire_date) 2 as 3 select employee_id,last_name,salary*12,hire_date 4 from employees 5 where department_id=80; Table created. 表后面不定义字段名字,直接在子查询里面定义别名也可以 SQL> drop table dept80; Table dropped. SQL> create table dept80 2 as 3 select employee_id,last_name,salary*12 annsal,hire_date 4 from employees 5 where department_id=80; Table created. 5、修改表 使用alter table语句可以: - 增加字段
- 修改字段类型,长度,约束
- 定义一个字段的默认值
- 删除一个字段
只读表: - 只读表不允许进行DML操作
- 只读表可以进行不影响表中数据的DDL操作
- 只读表可以对索引进行操作
SQL> alter table emp read only; Table altered. SQL> alter table emp read write; Table altered. 6、删除表 所有数据和表结构都被删除 提交所有未完成的事务 所有的索引和约束都被删除 drop table语句不能回滚 例子:删除dept80表 SQL> drop table dept80; Table dropped. 7、总结 这章主要讲了
|