SQL Server Statements

Use master

Go

Create DATABASE Librarydb

Go

Use Librarydb

Go

---method one

CREATE TABLE Student

(

SNO int PRIMARY KEY,

SName Char (10)

)

----Method Two

CREATE TABLE student02

(

SNo int constraint Pk_sno primary key ClusterED (SNo),

SName Char (10)

)

----Method Three

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 ("One", 24, ' female ')

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

---legislation one

CREATE TABLE student04

(

SNo int constraint Pk_sno4 primary key ClusterED (SNo),

SName Char (10),

SAGE tinyint,

SEX char (2),

Stumobileno char (one) constraint uq_mobile UNIQUE nonclustered (Stumobileno),

Stuemailadd varchar (constraint) Uq_email UNIQUE nonclustered (stuemailadd)

)

----Method Two

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 ("One", 24, ' female ', ' 12345678901 ', ' [email protected] ')

Insert into libraryDB.dbo.student04 (Sno,sname,sage,sex,stumobileno,stuemailadd) VALUES (26, ' female ', ' 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 (' Male ', ' female ')),

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, ' female ', ' 13512341234 ', ' [email protected] ')

-----Require mobile phone number, the first is 1, the second is 3,5,8, the second-last digit can not be 4,5,8, the other is the number on the line

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

------Create a student table

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)

)

---create Book table

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

)

----Create a library table

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, ' female ', ' 13512341234 ', ' [email Protected] ')

Insert into student09 (Sno,name,sage,sex,stumobileno,stuemailadd) VALUES (1002, ' Bob ', 23, ' female ', ' 13512345678 ', ' [email Protected] ')

Insert into student09 (Sno,name,sage,sex,stumobileno,stuemailadd) VALUES (1003, ' abc ', 20, ' Male ', ' 13512341472 ', ' [email Protected] ')

SELECT * FROM libraryDB.dbo.student09

Insert into LibraryDB.dbo.Book (Bookid,bookname,author,press,price) VALUES (39001, ' windows ', ' Gzh ', ' Beijing Press ', 60)

Insert into LibraryDB.dbo.Book (Bookid,bookname,author,press,price) VALUES (39002, ' AD ', ' qwe ', ' Henan Press ', 32.2)

Insert into LibraryDB.dbo.Book (Bookid,bookname,author,press,price) VALUES (39003, ' SQL ', ' ert ', ' Shanghai Press ', 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 (TEN) NOT NULL

)

Insert into LibraryDB.dbo.Book (Bookid,bookname,author,press,price) VALUES (39004, ' SQL ', ' ert ', ' Shanghai Press ', NULL)

Select COUNT (Price) from book

Select SUM (Price) from book

Select AVG (Price) from book

---Null value does not join the operation

DECLARE @i int

Select @i+100

This article is from the "Baby God" blog, make sure to keep this source http://babyshen.blog.51cto.com/8405584/1582848

SQL Server Statements