Oracle使用(九)_表的创建约束索引

表创建标准语法:

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr] , …)

--设计要求：建立一张用来存储学生信息的表，表中的字段包含了学生的学号、姓名、年龄、入学日期、年级、班级、email等信息，

--并且为grade指定了默认值为1，如果在插入数据时不指定grade得值，就代表是一年级的学生

--DML是不需要commit的，隐式事务

create table student

(

stu_id number(10),

name varchar2(20),

age number(2),

hiredate date,

grade varchar2(10) default 1,

classes varchar2(10),

email varchar2(50)

)

-- 注意日期格式要转换，不能是字符串，varchar2类型要用引号，否则出现类型匹配

--DML 需要收到commit

insert into student values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'2','1',' 123@qq.com ')

insert into student(stu_id,name,age,hiredate,classes,email) values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'1',' 1234@qq.com ')

select * from student

-- 给表添加列，添加新列时不允许为not null,因为与旧值不兼容

alter table student add address varchar(100)

-- 删除列

alter table student drop column address

--修改列

alter table student modify(email varchar2(100))

正规表设计使用power disinger

--表的重命名

rename student to stu

-- 表删除

drop table stu

**

在删除表的时候，经常会遇到多个表关联的情况(外键)，多个表关联的时候不能随意删除，使用如下三种方式:

2.表的约束（constraint）

约束:创建表时，指定的插入数据的一些规则

约束是在表上强制执行的数据校验规则

Oracle 支持下面五类完整性约束:

1). NOT NULL 非空约束 ---- 插入数据时列值不能空

2). UNIQUE Key 唯一键约束 ----限定列唯一标识，唯一键的列一般被用作索引

3). PRIMARY KEY 主键约束 ----唯一且非空，一张表最好有主键，唯一标识一行记录

4). FOREIGN KEY 外键约束---多个表间的关联关系，一个表中的列值，依赖另一张表某主键或者唯一键

-- 插入部门编号为50的，部门表并没有编号为50的，报错

insert into emp(empno,ename,deptno) values(9999,'hehe',50)

5). CHECK 自定义检查约束---根据用户需求去限定某些列的值，使用check约束

-- 添加主键约束/not null约束/check约束/唯一键约束

create table student

(

stu_id number(10) primary key,

name varchar2(20) not null,

age number(3) check(age>0 and age<126),

hiredate date,

grade varchar2(10) default 1,

classes varchar2(10),

email varchar2(50) unique,

deptno number(2),

)

-- 添加外键约束

create table stu

(

stu_id number(10) primary key,

name varchar2(20) not null,

age number(3) check(age>0 and age<126),

hiredate date,

grade varchar2(10) default 1,

classes varchar2(10),

email varchar2(50) unique,

deptno number(2),

FOREIGN KEY(deptno) references dept(deptno)

)

-- 创建表时没添加外键约束 也可以修改 其中fk_0001为外键名称

alter table student add constraint fk_0001 foreign key(deptno) references dept(deptno)

索引创建有两种方式:

组合索引：多个列组成的索引

--索引：加快数据剪碎

create index i_ename on emp(ename)

--当创建某个字段索引后，查询某个字段会自动使用到索引

select * from emp where ename = 'SMITH'

--删除索引 索引名称也是唯一的

drop index i_ename

一些概念：

回表：

覆盖索引

组合索引

最左匹配

alter index常用的语法如下：

(1)重建指定索引：

ALTER INDEX ind ON TA

REBUILD

(2)重建全部索引：

ALTER INDEX ALL ON TA

REBUILD

(3)禁用索引：

ALTER INDEX ALL ON TA

DISABLE

（再次启用使用REBUILD重建而不是ENABLED）

(4)指定参数重建索引：

ALTER INDEX ALL ON TA

REBUILD WITH(FILLFACTOR=80)

(5)指定参数修改索引：

ALTER INDEX ALL ON TA

SET(IGNORE_DUP_KEY = ON)

注意：alter index语法，不能用于修改索引定义，如添加或删除列，或更改列的顺序

Alter 

Alter是数据库SQL语言的修改语句，可以用来修改基本表，其一般表示格式为:

ALTER TABLE<表名>[改变方式]

基本介绍

数据库SQL语言的修改语句，可以用来修改基本表，其一般表示格式为:

ALTER TABLE<表名>[改变方式]

改变方式:

· 加一个栏位: ADD "栏位 1" "栏位 1 资料种类"

· 删去一个栏位: DROP "栏位 1"

· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"

· 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"

修改方式

由上可以看出，修改基本表提供如下四种修改方式:

(1)ADD方式:用于增加新列和完整性约束，列的定义方式同CREARE TABLE语句中的列定义方式相同，其语法格式:

ALTER TABLE <表名>ADD <列定义>|<完整性约束>。由于使用此方式中增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。

(2)DROP方式:用于删除指定的完整性约束条件，或删指定的列，其语法格式为:

ALTER TABLE<表名>DROP [<完整性约束名>]

ALTER TABLE<表名>DROP COLUMN <列名>

注释:某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN <列名>)。

(3)CHANGE方式，用于修改某些列，其语法格式:

ALTER TABLE [表名] CHANGE <原列名>TO <新列名><新列的数据类型>

(4)MODIFY方式，用于修改某些列的数据类型，其语法格式:

ALTER TABLE [表名] MODIFY [列名] [数据类型]

如何用sql更改表的列的数据类型和添加新列和约束

--修改插入标题

--A.添加新列

ALTER

TABLE

表名

ADD

列名

VARCHAR(20)

--B.修改列名

EXEC

sp_rename

'dbo.表名.列名',

'新列名',

'COLUMN'

--知识点衍生

--1.删除列

ALTER

TABLE

表名

DROP

COLUMN

column_b

--2.更改列的数据类型

ALTER

TABLE

表名

ALTER

COLUMN

列

DECIMAL

(5,

2)

--3.添加包含约束的列(唯一约束)

ALTER

TABLE

表

ADD

列

VARCHAR(20)

NULL

CONSTRAINT

约束名

UNIQUE

--4.添加一个未验证的check约束

ALTER

TABLE

表

WITH

NOCHECK

ADD

CONSTRAINT

约束名

CHECK

(列

>

1)

--5.在现有列中添加一个DEFAULT约束

ALTER

TABLE

表

ADD

CONSTRAINT

约束名

DEFAULT

50

FOR

列名

--6.删除约束

ALTER

TABLE

表

DROP

CONSTRAINT

约束名

--7.更改排序规则

ALTER

TABLE

表

ALTER

COLUMN

列

varchar(50)

COLLATE

Latin1_General_BIN

--8.修改表名

EXEC

sp_rename

'dbo.表名',

'新表名'

--9.重命名索引

EXEC

sp_rename

N'dbo.表名.索引名',

N'新索引名',

N'INDEX'

---