数据库对象:
表名和列名命名规则:
- 字母开头
- 不超过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、总结
这章主要讲了
- 使用create table创建表和约束
- 数据类型
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-1852379/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-1852379/