---创建 表 学生信息表
create table tb_students(
---列名 数据类型 not null,
Stu_num char(5) primary key,---(primary key设置主键)学号
---主键(primary key)就是数据表中的一个或多个字段,用于唯一标识数据表中的一条记录。
Stu_name varchar2(10) not null,---姓名
Stu_sex char(2) not null,---性别
Stu_age number(2),/*年龄*/
Stu_tel char(11)---电话
);
create table tb_students_2(
stu_achievement varchar2(3) not null,---成绩
stu_Course_id varchar2(5) not null ,---课程名
stu_Course_name varchar2(20) not null,--课程号
Stu_num char(5) not null,
Stu_name varchar2(10) not null
);
--修改表的数据;新增列
---ALTER TABLE 表名 add 列名 数据类型 ;
ALTER TABLE tb_students add stu_email char(50) ;
--修改表的数据;修改列
---ALTER TABLE 表名 MODIFY(修改) 表名 数据类型 (要修改的列的目标数据类型);
alter table tb_students modify stu_email varchar(40);
--修改表的数据;删除列
---ALTER TABLE 表名 DROP column(删除列) 列名;
alter table tb_students drop column stu_email;
--修改表的数据;删除表
---DROP(删除) TABLE(表) table_name(表名)
drop TABLE tb_students
---创建数据表之后添加主键约束
---ALTER/修改/ TABLE/表/ table_name/表名/ ADD CONSTRAINTS/约束/ constraint_name/列名/ PRIMARY KEY/主键/(column_name)
alter table tb_students add CONSTRAINTS pk_stu primary key(stu_num);
---2、在修改数据表时添加外键约束
/*ALTER TABLE 表名1 ADD CONSTRAINTS constraint_name约束的表名所有的class
FOREIGN KEY(column_name)/表名1内列名/ REFERENCES引用 table_name表名2(column_name表名2内列名)
ON DELETE CASCADE;*/
-- 先创建 班级表
create table tb_classes(
class_id char(3) primary key,
class_name varchar(40) not null,
class_leader varchar(20) not null,
class_desc varchar(100)
);
-- 后创建 学生表
create table tb_students(
stu_num char(5) primary key,
stu_name varchar(20) not null,
stu_sex char(2) not null,
stu_age number(2) not null,
stu_tel char(11) not null,
stu_cid char(3) not null -- 外键
);
-- 修改数据表时添加外键约束
ALTER TABLE tb_students ADD CONSTRAINTS fk_stu_class
FOREIGN KEY(stu_cid) REFERENCES tb_classes(class_id) ON DELETE CASCADE;
--插入数据
---insert into 表名 values ('数据',)
insert into tb_students values ('10001','老王','男','22','12345678910')
---
---
create table clazz(
id number primary key,
cname varchar2(50) not null,
bzr varchar2(20),
ms varchar2(100)
);
create table t_student(
sid char(5) primary key,
sname varchar2(20),
age int,
cid int,
constraint fk_cid foreign key(cid) references clazz(id) on delete cascade
);
drop table clazz;
insert into clazz values(1001,'java','121','javaVIP');
insert into clazz values(1002,'web','122','webVIP');
select * from clazz;
insert into t_student values('881','123',22,1001);
insert into t_student values('882','124',23,1002);
insert into t_student values('883','125',21,1003);
---查询该表信息
select * from t_student;
--添加外键
alter table tb_students add constraints fk_cid foreign key(cid) references clazz(id);
delete from t_student where sid = '881';
delete from clazz where id = '1002';
delete from t_student where sid = '1002';
---级联删除 on delete cascade
delete from clazz where id=1002;
/*删除外键约束
语法:
ALTER TABLE table_name DROP CONSTRAINTS constraint_name;
*/
-- 删除外键约束
ALTER TABLE tb_students DROP CONSTRAINTS fk_cid;
--添加CHECK约束
create table tb_students(
stu_num char(5) primary key,
stu_name varchar(20) not null,
stu_sex char(2) not null,
stu_age number(2) not null,
stu_tel char(11),
constraint ck_stu_sex CHECK(stu_sex='男' or stu_sex='女'),
constraint ck_stu_age CHECK(stu_age between 6 and 30)
);
/*语法:
CREATE TABLE table_name(
…,
CONSTRAINT constraint_name列名 CHECK控制( 约束条件 )
);
*/
/*在修改数据表时添加CHECK约束
语法:
ALTER TABLE table_name表名 ADD CONSTRAINTS constraint_name列名 CHECK ( 约束条件 )
*/