SQL語句的基本操作

--建立資料庫
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 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)
);

1 2 下一頁