建庫、建表、添加數據

use master
go
 
if exists (select * from sysdatabases where name='Study')--判斷Study數據庫是否存在，是則刪除
    drop database Study
go
 
EXEC sp_configure 'show advanced options', 1
GO
-- 更新當前高級選項的配置信息
RECONFIGURE
GO
                                                   
EXEC sp_configure 'xp_cmdshell', 1
GO
-- 更新當前功能（xp_cmdshell）的配置信息.
RECONFIGURE
GO
 
exec xp_cmdshell 'mkdir D:\data', NO_OUTPUT
--利用xp_cmdshell 命令創建文件夾，此存儲過程的第一個參數為要執行的有效DOS命令，第二個參數為是否輸出返回信息。
go
 
 
 
 
create database Study--創建數據庫
on primary
    (
       name='Study_data',--主數據文件的邏輯名
       fileName='D:\data\Study_data.mdf',--主數據文件的物理名
       size=10MB,--初始大小
       filegrowth=10%    --增長率
    )
log on
    (
       name='Study_log',--日志文件的邏輯名
       fileName='D:\data\Study_data.ldf',--日志文件的物理名
       size=1MB,
       maxsize=20MB,--最大大小
       filegrowth=10%
    )
go
 
use Study
go
 
if exists (select * from sysobjects where name='Student')--判斷是否存在此表
    drop table Student
go
create table Student
(
    id int identity(1,1) primary key,--id自動編號，並設為主鍵
    [name] varchar(20) not null,
    sex char(2) not null,
    birthday datetime not null,
    phone char(11) not null,
    remark text,
    tId int not null,
    age as datediff(yyyy,birthday,getdate())--計算列。
)
go
 
if exists (select * from sysobjects where name='Team')
    drop table Team
go
create table Team
(
    id int identity(1,1) primary key,
    tName varchar(20) not null,
    captainId int
)
go
 
alter table Student
add
    constraint CH_sex check(sex in ('男','女')),--檢查約束，性別必須是男或女
    constraint CH_birthday check(birthday between '1950-01-01' and '1988-12-31'),
    constraint CH_phone check(len(phone)=11),
    constraint FK_tId foreign key(tId) references Team(id),--外鍵約束，引用Team表的主鍵
    constraint DF_remark default('請在這裡填寫備注') for remark--默認約束，
go
 
alter table Team
add
    constraint UK_captainId unique(captainId)--唯一約束
go
 
insert into Team values('第一組',1)
insert into Team values('第二組',2)
insert into Team values('第三組',3)
insert into Team values('第四組',4)
insert into Team values('第五組',5)
 
insert into Student values('張三','男','1982-6-9','23456789451','來自天津',1)
insert into Student values('李四','男','1987-6-9','78945678945','安徽',4)
insert into Student values('王五','男','1982-6-9','65987845651','大連',3)
insert into Student values('趙六','男','1981-6-9','25487965423','湖南',5)
insert into Student(name,sex,birthday,phone,tId) values('江七','男','1984-6-9','25487965423',5)
 
select * from Team
select * from Student
 
if exists (select * from sysobjects where name='teacher')
    drop table teacher
go
 
 
create table teacher
(
    id int identity (1,1) primary key,
    name varchar(20),
    address varchar(20)
)
 
go
 
insert into teacher values('zhang','hubei')
insert into teacher values('wang','hubei')
insert into teacher values('li','hubei')
insert into teacher values('chen','hunan')
insert into teacher values('zhao','hunan')
insert into teacher values('tian','guangdong')
insert into teacher values('ma','guangdong')
insert into teacher values('chang','tianjin')
insert into teacher values('liang','beijing')
 
select * from teacher
 
select count(*),address from teacher group by address having address<>'hunan'