sql server 語句

標籤：sql server   sql server 語句   

use master

go

create database libraryDB

go

use libraryDB

go

---方法一

create table student

(

   SNO int primary key,

   SName char(10)

)

----方法二

create table student02

(

 SNo int constraint PK_SNO primary key ClusterED(SNO),

SName char(10)

)

----方法三

create table student03

(

  SNo int,

  SName char(10),

  constraint PK_SNO3 primary key ClusterED(SNO)

)

alter table libraryDB.dbo.student02 add SAGE tinyint 

alter table libraryDB.dbo.student02 add SEX char(2)

insert into libraryDB.dbo.student02(SNo,SName,SAGE,SEX) values (01,‘one‘,24,‘女‘)

select * from libraryDB.dbo.student02

use libraryDB

go

create table BorrowBook

(

  SNO int,

  BookID int,

  BorrowDate SMallDatetime,

  ReturnDate SMallDatetime,

  Constraint PK_BBook primary key ClusterED(SNO,BookID)

)

 

insert into BorrowBook(SNO,BookID,BorrowDate,ReturnDate) values (1001,2002,GETDATE(),GETDATE()+60)

insert into BorrowBook(SNO,BookID,BorrowDate,ReturnDate) values (1001,2003,GETDATE(),GETDATE()+60)

insert into BorrowBook(SNO,BookID,BorrowDate,ReturnDate) values (1002,2003,GETDATE(),GETDATE()+60)

select * from libraryDB.dbo.BorrowBook

use libraryDB

go

---立法一

create table student04

(

  SNo int constraint Pk_SNo4 primary key clusterED(SNo),

  SName char(10),

  SAGE tinyint,

  SEX char(2),

  StuMobileNo char(11) constraint UQ_Mobile  UNIQUE NONClusterED(StuMobileNo),

  StuEMailAdd varchar(50) constraint UQ_EMail UNIQUE NONClusterED(StuEMailAdd)

)

----方法二

create table student05

(

  SNo int,

  SName char(10),

  SAGE tinyint,

  SEX char(2),

  StuMobileNo char(11),

  StuEMailAdd varchar(50),

  constraint Pk_SNo5 primary key clusterED(SNo),

  constraint UQ_Mobile  UNIQUE NONClusterED(StuMobileNo),

  constraint UQ_EMail UNIQUE NONClusterED(StuEMailAdd)

)

select * from libraryDB.dbo.student04

insert into libraryDB.dbo.student04(SNo,SName,SAGE,SEX,StuMobileNo,StuEMailAdd) values (01,‘one‘,24,‘女‘,‘12345678901‘,‘[email protected]‘)

insert into libraryDB.dbo.student04(SNo,SName,SAGE,SEX,StuMobileNo,StuEMailAdd) values (02,‘two‘,26,‘女‘,‘12345678902‘,‘[email protected]‘)

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

use libraryDB

go

create table student07

(

  SNo int,

  SName char(10),

  SAGE tinyint,

  SEX char(2),

  StuMobileNo char(11),

  StuEMailAdd varchar(50),

  constraint PK_SNO7 primary key clusterED(SNo),

  constraint UQ_MobileNo UNIQUE NONclusterED(StuMobileNo),

  constraint UQ_EMailNo UNIQUE NONClusterED(StuEMailAdd),

  constraint Check_SAGE check(SAGE>0),

  constraint Check_SEX check(SEX in(‘男‘,‘女‘)),

  constraint Check_Mobile check(StuMobileNo Like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘),

  constraint Check_EMail check(StuEMailAdd like ‘%_[@]%_[.]%_‘)

)

insert into libraryDB.dbo.student07(SNo,SName,SAGE,SEX,StuMobileNo,StuEMailAdd) values (101,‘one‘,26,‘女‘,‘13512341234‘,‘[email protected]‘)

-----要求手機號碼，第一位是1，第二位是3,5,8，倒數第二位不能是4,5,8，其他是數字就行

constraint Check_Mobile check(StuMobileNo like ‘[1][358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^458][0-9]‘)

use libraryDB

go

create table student08

(

  SNo int identity(95001,1),

  SName char(10),

  constraint PK_SNO8 primary key cluster(SNo)

)

insert into Student08(SName) values(‘alice‘)

insert into Student08(SName) values(‘bob‘)

insert into Student08(SName) values(‘peter‘)

select * from libraryDB.dbo.student08

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

create table BorrowBook01

(

  SNo int,

  BookID int,

  BorrowBook SMallDatetime constraint DF_BBook default GetDate(),

  ReturnBook SMallDatetime constraint DF_RBook default Getdate()+30 

)

insert into BorrowBook01(SNo,BookID) values (101,102)

select * from libraryDB.dbo.BorrowBook01

------建立student表

use libraryDB

go

create table student09

(

  SNo int constraint PK_SNO9 primary key clusterED(SNO),

  SName char(10),

  SAGE tinyint constraint check_SAGE9 check(SAGE>9),

  SEX char(2),

  StuMobile char(11),

  StuEMailAdd varchar(50)

)

---建立Book表

use libraryDB

go

create table Book

(

  BookID int constraint PK_BookID primary key clusterED(BookID),

  BookName varchar(50),

  Author char(10),

  Press char(40),

  Price float

)

----建立借書表

use libraryDB

go

create table BorrowBook02

(

  SNO int constraint FK_SNO foreign key (SNO) references student09(SNO),

  BookID int constraint FK_BookID foreign key (BookID) references Book(BookID),

  BorrowDate SMallDatetime constraint DF_BDate default GetDate(),

  ReturnDate SMallDatetime constraint DF_RDate default Getdate()+60,

  constraint PK_BBook1 primary key clusterED(SNO,BookID)

)

insert into student09(SNO,Name,SAGE,SEX,StuMobileNo,StuEMailAdd) values(1001,‘alice‘,21,‘女‘,‘13512341234‘,‘[email protected]‘)

insert into student09(SNO,Name,SAGE,SEX,StuMobileNo,StuEMailAdd) values(1002,‘bob‘,23,‘女‘,‘13512345678‘,‘[email protected]‘)

insert into student09(SNO,Name,SAGE,SEX,StuMobileNo,StuEMailAdd) values(1003,‘abc‘,20,‘男‘,‘13512341472‘,‘[email protected]‘)

select * from libraryDB.dbo.student09

insert into libraryDB.dbo.Book(BookID,BookName,Author,Press,Price) values (39001,‘windows‘,‘gzh‘,‘北京出版社‘,60)

insert into libraryDB.dbo.Book(BookID,BookName,Author,Press,Price) values (39002,‘AD‘,‘qwe‘,‘河南出版社‘,32.2)

insert into libraryDB.dbo.Book(BookID,BookName,Author,Press,Price) values (39003,‘SQL‘,‘ert‘,‘上海出版社‘,69.63)

select * from libraryDB.dbo.Book

insert into BorrowBook02(SNO,BookID) values(1001,39001)

insert into BorrowBook02(SNO,BookID) values(1002,39002)

insert into BorrowBook02(SNO,BookID) values(1003,39003)

select * from libraryDB.dbo.BorrowBook02

create table student10

(

  SNO int constraint PK_SNO10 primary key clusterED(SNO),

  SName char(10) not null

)

insert into libraryDB.dbo.Book(BookID,BookName,Author,Press,Price) values (39004,‘SQL‘,‘ert‘,‘上海出版社‘,null)

select COUNT(price) from Book

select SUM(price) from Book

select AVG(price) from Book

---空值不加入運算

declare @i int

select @i+100

本文出自 “baby神” 部落格，請務必保留此出處http://babyshen.blog.51cto.com/8405584/1582848

sql server 語句