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','12@123.cn')
insert into libraryDB.dbo.student04(SNo,SName,SAGE,SEX,StuMobileNo,StuEMailAdd) values (02,'two',26,'女','12345678902','14@123.cn')
-------------
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','14@123.cn')
-----要求手机号码，第一位是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','alice@123.com')
insert into student09(SNO,Name,SAGE,SEX,StuMobileNo,StuEMailAdd) values(1002,'bob',23,'女','13512345678','bob@123.com')
insert into student09(SNO,Name,SAGE,SEX,StuMobileNo,StuEMailAdd) values(1003,'abc',20,'男','13512341472','abc@123.com')
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
