echochio

DDL data definition language
create database(table)
alter database(table)
drop database(table)

DML data manipulation language
insert table
update
select * from 
delete

DCL data control language
great
revoke

--------------------------------------------------------

CREATE TABLE Student
(
sclass varchar(10) NOT NULL,
snumb varchar(10) PRIMARY KEY,
sname varchar(10) NOT NULL,
sgender varchar(4) DEFAULT '男' CONSTRAINT consgender CHECK(sgender IN('男','女')),
sbirthday date,
sage int CONSTRAINT consage CHECK ( sage >=0 and sage <=150)
intro text
)

CREATE TABLE course
(
cnamb varchar(10) PRIMARY KEY,
cnamn varchar(40)  NOT NULL,
chours int CONSTRAINT conshour CHECK(chours >=0 andm chours<=300),
credit decimal (5.2) CONSTRAINT conscredit CHECK(credit >=0 and credit<20)
)

CREATE TABLE study
(
snumb varchar(10),
cnumb varchar(10),
score decimal(5,2) CONSTRAINT consscore CHECK(score >=0 and score <= 100),
PRIMARY KEY (snumb,cnumb),
FOREIGN KEY (snumb) REFERENCES student(snumb),
FOREIGN KEY (cnumb) REFERENCES course(cnamb)
)

-----------------------------------------------------------------------
insert into Student
values ('英文','2016006001','王大頭','男','1990-2-1',18)

insert into Student
values ('英文','2016006002','王小頭','女','1990-3-1',18)

-----------------------------------------------------------------------
select * from Student
"英文"    "2016006001"    "王大頭"    "男"    "1990-2-1"    "18"
"英文"    "2016006002"    "王小頭"    "女"    "1990-3-1"    "18"

-----------------------------------------------------------------------
insert into course
values('英文',20,5,'英文課程')

select * from course
"英文"    "20"    "5"    "英文課程"

-----------------------------------------------------------------------
insert into study
values ('2016006001','ENG09001',90)

select * from study
"2016006001"    "ENG09001"    "90"

----------------------------------------------------------------------

alter table people
add gender varchar(2)

alter table people
add CONSTRAINT pgender CHECK (gender='男' or gender='女')

ALTER TABLE people
ADD "ID" INT IDENTITY(1,1)

ALTER TABLE people
ADD "name" varchar(40)

alter table people
add CONSTRAINT pkey
PRIMARY KEY (id,name)

SQLite get table info: PRAGMA table_info(people)

MSSQL get table  primary : 

SELECT  i.name AS IndexName,
        OBJECT_NAME(ic.OBJECT_ID) AS TableName,
        COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM    sys.indexes AS i INNER JOIN 
        sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID
                                AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1

----------------------------------------------------------------------------