SQL语句的基本操作

–创建数据库create database Etp; –连接数据库connect to Etp; –断开连接disconnect Etp; –查看当前数据库下有哪些表list ta

–创建数据库
create database Etp;

–连接数据库
connect to Etp;

–断开连接
disconnect Etp;

–查看当前数据库下有哪些表
list tables;

–建表
create table studentInfo(
stuno char(5) not null,
stuname varchar(8),
stubirth date
);

–查看表结构
describe table studentinfo;

–新增表字段
alter table studentinfo add stutel int;
alter table studentinfo add abc int;

–修改字段类型
alter table studentinfo alter column stutel set data type char(11);

–删除字段
alter table studentinfo drop column abc;

–增加一个非空约束
alter table studentinfo alter column stuname set not null;

–重构表
reorg table studentinfo;

–增加一个唯一约束
alter table studentinfo alter column stutel set not null;
alter table studentinfo add本文来源gao*daima.com搞@代#码&网6 constraint un_stutel unique(stutel);

–添加检查约束
alter table studentinfo add column stuAge int;
alter table studentinfo add constraint ch_stuAge check(stuAge > 0 and stuAge <150);

–添加主键约束
alter table studentinfo add constraint pk_stuno primary key(stuno);

–删除表
drop table studentinfo;

–创建表的同时添加约束方式1
create table studentinfo(
stuNo int not null,
stuName varchar(8) not null,
stuAge int,
stuTel char(8),
constraint pk_stuNo primary key(stuNo),
constraint un_stuName unique(stuName),
constraint ch_stuAge check(stuAge >=0 and stuAge <150)
);

–创建表的同时添加约束方式2
create table studentinfo(
stuNo int not null primary key,
stuName varchar(8) not null unique,
stuAge int check(stuAge >=0 and stuAge <150),
stuTel char(8)
);

–添加主外键
–新增班级表
create table classInfo(
classId int not null primary key,
className varchar(20)
);

–建表的同时添加外键
create table studentinfo(
stuNo int not null,
stuName varchar(8) not null,
stuBirth date not null,
stuAge int,
stuTel char(8),
fclassId int,
stuBirth date not null,
constraint pk_stuNo primary key(stuNo),
constraint un_stuName unique(stuName),
constraint ch_stuAge check(stuAge >=0 and stuAge <150),
constraint fk_fcalssId foreign key(fclassid) references classInfo(classId)
);

— 自增
create table studentinfo(
stuNo int not null generated always as identity(start with 1 ,increment by 1),
stuName varchar(8) not null,
stuAge int,
stuTel char(8),
fclassId int,
stuBirth date not null,
constraint pk_stuNo primary key(stuNo),
constraint un_stuName unique(stuName),
constraint ch_stuAge check(stuAge >=0 and stuAge <150),
constraint fk_fcalssId foreign key(fclassid) references classInfo(classId)
);

，

喜欢 (0)赏

[搞代码]

分享 (0)