--1. DDL
--create database Testaircraft; --create database
-- การสร้าง Table
create table test1
( T_ID char(4) not null primary key,
T_Name char(40) not null,
T_salary int not null
);
-- ดูรายละเอียด ของตาราง
sp_help test1;
---------------
create table test2
( T_ID char(4) not null,
T_code char(3) not null,
T_salary int not null,
primary key (T_ID,T_code)
);
sp_help test2;
-- สร้าง fk
create table test3
( T_ID char(4) not null primary key,
T_code char(3) not null references test4(T_code),
T_salary int not null
);
create table test4
( T_code char(3) not null primary key,
T_Name char(3) not null,
T_salary int not null
);
------------------------------------------
create table test11
( T_ID char(4) not null primary key,
T_name char(4) not null
);
create table test12
( T_Code char(3) not null primary key,
T_name char(4) not null
);
create table test13
( T_Des int not null primary key,
T_name char(4) not null
);
create table test5
( T_ID char(4) not null references test11(T_ID),
T_Code char(3) not null references test12(T_Code),
T_name char(3) not null,
T_Des int not null references test13(T_Des),
primary key(T_ID,T_Code)
);
sp_help test5;
-------------------------------------
drop table test5;
------ เปลี่ยนชื่อตาราง--------
sp_rename 'test1','Exam1';
------ เปลี่ยนชื่อ arrtibute--------
sp_rename 'Exam1.T_name','T_surname';
-------------------------------------
drop table test11;
---- การเปลี่ยนแปลงแก้ใขโครงลร้าง Table
create table Ex1
( E_ID char(4) not null,
E_name varchar(40) not null
);
alter table Ex1 add primary key(E_ID); -- add primary key in table ex1
alter table Ex1 add constraint PK_E_ID primary key(E_ID); -- add primary key in table ex1
alter table Ex1 drop constraint PK__Ex1__2C3393D0; -- add primary key in table ex1
sp_help Ex1;
alter table Ext add E_slary int; -- add colum E_slary value by int
--------------------------------------------------------------------------
-- DML data manipulation language
/*
insert ,update ,delete,select
*/
create table Ex2
(
--E_ID char(4) not null check(E_ID like 'E?[a-z][0-9]') primary key,
E_ID char(4) not null check(E_ID like 'EA[0-9][0-9]') primary key,
E_Name varchar(30) not null,
E_BD datetime,
E_salary int check(E_Salary > 5000)
);
drop table Ex2;
--การ เพิ่มข้อมูล
insert into Ex2 values('EA01','Pirom konglerd','3/26/2011',50001);
insert into Ex2 values('EA02','rom konglerd','3/26/2011',50001);
insert into Ex2 (E_ID,E_Name)values('EA03','rom konglerd');
set dateformat dmy; -- set format date
select * from Ex2;
update Ex2 set E_salary=6000;
-- DCL
/*
grant revoke
examp:
create user pirom set password 123456
*/
ไม่มีความคิดเห็น:
แสดงความคิดเห็น