简单的sql语句代码

create database banktest

on

(

 name = 'banktest',

 filename = 'D:\15\banktest.mdf',

 size = 5MB,

 maxsize = 10MB,

 filegrowth = 5%

)

Go

create database LoanDB

on

(

 name = 'LoanDB',

 filename = 'D:\15\LoanDB.mdf',

 size = 10MB,

 maxsize = 100MB,

 filegrowth = 5%

)

log on

(

 name = 'LoanDB_log',

 filename = 'D:\15\LoanDB_log.ldf',

 size = 3MB,

 maxsize = 30MB,

 filegrowth = 1MB

)

GO

alter database LoanDB

add log file

(

 name = 'Loan_log',

 filename = 'D:\15\Loan_log.ldf',

 size = 2mb,

 maxsize = 6mb,

 filegrowth = 1mb

)

go

alter database LoanDB

add filegroup LoanDBfgp

go

alter database LoanDB

add file

(

 name = 'LoanDBtfile',

 filename = 'D:\15\LoanDBtfile.ndf'

)

to filegroup LoanDBfgp

Go

drop database banktest

go

create table LoanDB. dbo.CustomerT

(

 Cno char(5) not null,

 Cname nvarchar(20) null,

 Cnature nchar(2) null,

 Ccaptical int null,

 Crep nchar(4) null,

 constraint PK_CustomerT primary key clustered(Cno ASC)

)

Go

create table LoanDB. dbo.CustomerT1

(

 Cno char(5) not null,

 Cname nvarchar(20),

 Cnature nchar(2),

 Ccaptical int,

 Crep nchar(4),

 constraint PK_CustomerT1 primary key(Cno)

)

Go

create table LoanDB. dbo.CustomerT2

(

 Cno char(5) not null

constraint PK_CustomerT2 primary key,

 Cname nvarchar(20),

 Cnature nchar(2),

 Ccaptical int,

 Crep nchar,

)

Go

create table LoanDB.dbo.LoanT

(

 Cno char(5) not null,

 Bno char(5) not null,

 Ldate smalldatetime not null,

 Lamount decimal(8,2),

 Lterm int,

 constraint PK_LoanT Primary key

 (

  Ldate,Cno,Bno

 )

)

Go

drop table LoanDB.dbo.CustomerT1

go

use LoanDB

go

create table LoanDB.dbo.CustomerT1(Cid char(5))

go

exec sp_help CustomerT1

go

alter table CustomerT1

add Caddr varchar(20)

go

exec sp_help CustomerT1

go

alter table CustomerT1

drop column Cid

go

exec sp_help CustomerT1

go

use LoanDB

go

alter table CustomerT1

alter column Cadd

go

exec sp_rename 'CustomerT1.Caddr','Address'

go

exec sp_help CustomerT1

go

drop table LoanDB.dbo.CustomerT1

go

select name, state, state_desc From sys. Databases

select name, physical_name, type, type_desc, state, state_desc

From sys.master_files

exec sp_help CustomerT2

go

drop table LoanDB.dbo.CustomerT2

go

create table LoanDB.dbo.BankT(

 Bno char(5) not null

 constraint PK_Bno primary key,

 Bname nchar(10),

 Bloc nchar(6),

 Btel varchar(16)

)

Go

insert into BankT

values('N0102','农行和平支行','历下区','0531-86567718')

insert into CustomerT

values('C001','三盛科技公司','私营',30,'张雨')

insert into LoanT

values('C001','J0102','2019-8-20',10,15)

create database test

go

use test

go

create table customer(

 Cno char(6) not null primary key

 Cname nchar(10) not null,

 Ctel char(15) not null

)

alter table customer

add Email char(20) constraint UQ_Email UNIQUE

go

alter table customer

add constraint UQ_Ctel unique nonclustered (Ctel)

go

alter table customer

drop constraint UQ_Email,UQ_Ctel

go

create database teaching

on

primary

(

 name = 'teaching',

 filename = 'D:\tt\teaching.mdf',

 size = 5mb,

 maxsize = 30mb,

 filegrowth =1mb

),

(

 name = 'teaching12',

 filename = 'D:\tt\teaching12.ndf',

 size = 4mb,

 maxsize = 30mb,

 filegrowth = 1mb

),

filegroup tt

(

 name = 'teaching21',

 filename = 'D:\tt\teaching21.ndf',

 size = 4mb,

 maxsize = 30mb,

 filegrowth = 1mb

)

SELECT Cname,Crep

FROM CustomerT C JOIN (SELECT *FROM LoanT

WHERE Ldate= '2019-8-20') L1 ON C.Cno=L1.Cno

SELECT Cname,Crep

FROM CustomerT

WHERE Cno IN (SELECT Cno

FROM LoanT

WHERE Ldate ='2019-8-20')

SELECT Cname,Bname,Bloc,Lamount,Ldate

FROM CustomerT C JOIN LoanT L ON C.Cno=L.Cno JOIN BankT B ON L.Bno=B.Bno

WHERE Bloc = N'市中区'

SELECT Cname,Bname,Lamount,Ldate

FROM CustomerT C JOIN LoanT L ON C.Cno=L.Cno JOIN

(SELECT Bno,Bname FROM BankT WHERE Bloc =N'市中区') B1 ON L.Bno=B1.Bno

SELECT Cname,Bname,Lamount,Ldate FROM CustomerT C JOIN (SELECT L.Bno,Bname,Cno,Lamount,Ldate FROM LoanT L JOIN

(SELECT Bno,Bname FROM BankT WHERE Bloc = N'市中区') B1

ON L.Bno =B1.Bno) LB1 ON C.Cno = LB1.Cno

SELECT Bno, COUNT(distinct Cno) C_Cno

FROM LoanT

GROUP BY Bno

SELECT Cno FROM LoanT

WHERE Bno = N'J0102'

UNION

SELECT Cno FROM LoanT

WHERE Bno = N'Z0102'

SELECT *

FROM CustomerT C,LoanT T

WHERE C.Cno = T.Cno

SELECT *

FROM CustomerT C INNER JOIN LoanT L ON C.Cno = L.Cno

SELECT Ename ,Salary ,Grade

FROM Employee E INNER JOIN Job_Grade J ON E.Salary BETWEEN J.Lowest_Salary AND J.Highest_Salary

SELECT *

FROM BankT B, CustomerT C, LoanT L

WHERE B.Bno = L.Bno AND C.Cno = L.Cno

SELECT Cnature , SUM(Lamount) Sum_Lamount

FROM LoanT L, CustomerT C

WHERE L.Cno = C.Cno AND Ldate > N'2018-1-1'

GROUP BY Cnature

SELECT C2.Cname, C2.Cnature

FROM CustomerT C1,CustomerT C2

WHERE C1.Cnature = C2.Cnature AND C1.Cname = N'博科生物集团' AND C2.Cname != N'博科生物集团'

SELECT e1.Ename,N'的领导是',e2.Ename

FROM Employee e1 JOIN Employee e2 ON e1.Manager=e2.Eno

SELECT

FROM LoanT L1,LoanT L2

WHERE L1.Bno = 'Z0101' AND L2.Bno = 'N0103' AND L1.Cno = L2.Cno

SELECT *

FROM (LoanT L RIGHT JOIN CustomerT C ON L.Cno = C.Cno)

SELECT *

FROM CustomerT C LEFT JOIN LoanT L ON C.Cno = L.Cno FULL JOIN BankT B ON L.Bno = B.Bno

SELECT C.Cno, Cname, Cnature, Bno, Ldate, Lamount

FROM CustomerT C LEFT JOIN LoanT L ON C.Cno = L.Cno

SELECT C.Cno, Cname, Cnature, Bno, Ldate, Lamount

FROM LoanT L RIGHT JOIN CustomerT C ON L.Cno = C.Cno

SELECT C.Cno, Cnature, Cnature

FROM CustomerT C LEFT JOIN LoanT L ON C.Cno = L.Cno

WHERE Bno IS NULL

SELECT C.Cno, C.Cname, ISNULL(SUM(Lamount),0) Sum_Lamount

FROM CustomerT C LEFT JOIN LoanT L ON C.Cno = L.Cno

WHERE Cnature = N'国营'

GROUP BY C.Cno, C.Cname

ORDER BY ISNULL(SUM(Lamount),0) DESC

SELECT C.*, B.*

FROM CustomerT C FULL JOIN LoanT L ON C.Cno = L.Cno

 FULL JOIN BankT B ON L.Bno = B.Bno

SELECT Cname, Cnature

FROM CustomerT

WHERE Cname != N'博科生物集团' AND Cnature = (

SELECT Cnature

FROM CustomerT

WHERE Cname = N'博科生物集团'

)

SELECT Cname

FROM

(

SELECT DISTINCT Cno

FROM LoanT

WHERE Bno = (

SELECT Bno FROM BankT WHERE Bname = N'建行济南新华支行'

)) T JOIN CustomerT C ON T.Cno = C.Cno

SELECT Cno, Cname, Cnature

FROM CustomerT

WHERE Cno NOT IN (

SELECT Cno

FROM LoanT

)

SELECT C.Cno, Cname, Cnature

FROM CustomerT C LEFT JOIN LoanT L ON C.Cno = L.Cno

WHERE Bno IS NULL

SELECT Cno, Cname, Cnature

FROM CustomerT

WHERE Cno NOT IN (

SELECT Cno FROM LoanT WHERE Bno = N'Z0102'

SELECT *

FROM CustomerT

WHERE Cnature = (

SELECT Cnature FROM CustomerT WHERE Cname = N'博科生物集团'

) AND Cname != N'博科生物集团'

SELECT Cname, Cnature, Ccaptical

FROM CustomerT,LoanT

WHERE CustomerT.Cno = LoanT.Cno AND Lamount =

(SELECT MAX(Lamount) FROM LoanT WHERE Bno = N'Z0102')

SELECT Cname,Cnature,Ccaptical

FROM CustomerT C JOIN(

SELECT Cno

FROM

(SELECT Cno,SUM(Lamount) SUM_Lamount

FROM LoanT

WHERE Bno = N'Z0102'

GROUP BY Cno) T1

WHERE SUM_Lamount =

(SELECT MAX(SUM_Lamount) FROM (SELECT SUM(Lamount) SUM_Lamount

FROM LoanT

WHERE Bno = N'Z0102'

GROUP BY Cno) T2))T3 ON C.Cno = T3.Cno

SELECT Cno, Ldate, Lamount

FROM LoanT

WHERE Bno = N'Z0102' AND Lamount >

(SELECT AVG(Lamount) FROM LoanT WHERE Bno = N'Z0102')

SELECT Cno, COUNT(Lamount) 贷款数量,SUM(Lamount) 总贷款金额

FROM LoanT

WHERE Cno =ANY(SELECT Cno

FROM LoanT

WHERE Bno = (SELECT Bno FROM BankT WHERE Bname = N'招行洪楼支行'))

GROUP BY Cno

SELECT Cno, Bno, Ldate, Lamount

FROM LoanT L

WHERE Lamount > (SELECT AVG(Lamount) FROM LoanT

WHERE Cno = L.Cno)

SELECT Cno 客户代码, DATEADD(YEAR, Lterm, Ldate) 还款日期, Lamount 还款金额

FROM (SELECT * FROM LoanT WHERE Lamount>=500) T

WHERE YEAR(Ldate)+Lterm<2030

SELECT *

INTO NewBankT

FROM BankT

SELECT *

INTO NewCustomerT

FROM CustomerT

SELECT *

INTO NewLoanT

FROM LoanT

DELETE FROM NewLoanT

FROM NewBankT B JOIN NewLoanT L ON B.Bno = L.Bno

WHERE Btel = N'0531-82597845'

CREATE VIEW V_C

AS

SELECT Cno, Cname, Ccaptical, Crep FROM CustomerT WHERE Cnature = N'国营'

SELECT Cname FROM V_C

WHERE Ccaptical >= 1000

CREATE VIEW Test

AS

SELECT * FROM BankT WHERE Bname LIKE N'招行%'

UPDATE Test

SET Bname = N'建行济南新华分行'

WHERE Bno = N'Z0102'

UPDATE BankT

SET Bname = N'招行洪楼支行'

WHERE Bno = 'Z0102'

DROP VIEW Test

CREATE VIEW V_Bank

AS

SELECT * FROM BankT WHERE Bname LIKE N'招行%' WITH CHECK OPTION

--更新V_Bank，但因为更改条件不满足select语句，没有修改

UPDATE V_Bank

SET Bname = N'建行济南新华分行'

WHERE Bno = N'Z0102'

DROP VIEW test, V_Bank

CREATE VIEW V_L_C

AS

SELECT C.Cno, Cname, Ldate

FROM LoanT L, CustomerT C WHERE L.Cno = C.Cno

ALTER VIEW V_L_C

AS

SELECT C.Cno, C.Cname, Bno, Ldate

FROM LoanT L, CustomerT C WHERE L.Cno = C.Cno

--如下将BankT和V_L_C连接

SELECT B.Bno, Bname, Cno , Cname, Ldate

FROM BankT B, V_L_C L

WHERE B.Bno = L.Bno

SELECT B.Bno, Bname, Cno , Cname, Ldate

FROM BankT B,(SELECT C.Cno, C.Cname, Bno, Ldate

FROM LoanT L, CustomerT C WHERE L.Cno = C.Cno) L

WHERE B.Bno = L.Bno

UPDATE V_L_C

SET Cname = 'test'

WHERE Cno = 'C002'

UPDATE CustomerT

SET Cname = N'华森装饰公司'

WHERE Cno = 'C002'

DROP VIEW V_L_C

CREATE VIEW V_Loan(客户名称,银行名称,贷款日期,贷款金额)

AS

SELECT Cname, Bname, Ldate, Lamount FROM CustomerT C, BankT B, LoanT L

WHERE C.Cno = L.Cno AND B.Bno = L.Bno

SELECT * FROM V_Loan

WHERE 客户名称 = N'三盛科技公司' AND 银行名称 = N'建行济南新华支行'

CREATE VIEW V_BankLoan AS

SELECT 客户名称,贷款金额,贷款日期 FROM V_Loan WHERE 银行名称 = N'招行洪楼支行'

SELECT * FROM V_BankLoan WHERE 客户名称 = N'三盛科技公司'

CREATE VIEW V_LoanYear AS

SELECT Cno, Bno, YEAR(Ldate) Lyear FROM LoanT

CREATE VIEW V_AVG_Loan AS

SELECT Bno, AVG(Lamount) AVG_Lamount

FROM LoanT GROUP BY Bno

CREATE VIEW V_LE_Loan WITH encryption

AS

SELECT BankT.Bno 银行编号,COUNT(DISTINCT Cno) 贷款客户数,

CASE

WHEN COUNT(DISTINCT Cno) = 0 THEN N'无客户贷款'

WHEN COUNT(DISTINCT Cno) = 1 THEN N'少'

WHEN COUNT(DISTINCT Cno)>= 3 THEN N'多'

ELSE N'一般'

END AS 贷款情况

FROM LoanT RIGHT JOIN BankT ON LoanT.Bno = BankT.Bno

GROUP BY BankT.Bno

ALTER VIEW V_AVG_Loan(Bno, AVG_Lamount)

AS

SELECT BankT.Bno, ISNULL(AVG(Lamount), 0)

FROM LoanT RIGHT JOIN BankT ON LoanT.Bno = BankT.Bno

WHERE Bloc = N'历下区'

GROUP BY BankT.Bno

Select Case month('2005-6-05')

When 11 then N'十一'

When 12 then N'十二'

Else substring(N'一二三四五六七八九十',month('2005-6-05'),1)

End

---rtrim()

SELECT Len(Rtrim(Space(8)+Space(8)))

IF EXISTS

(SELECT Cno FROM LoanT WHERE Lamount > 800)

SELECT N'存在'

SELECT * FROM CustomerT C--这里的*为要显示的行

WHERE EXISTS

(SELECT * FROM LoanT WHERE Cno = C.Cno AND Lamount>800)-

SELECT Cname, Bname, Lamount

FROM CustomerT, LoanT, BankT

WHERE CustomerT.Cno = LoanT.Cno AND BankT.Bno = LoanT.Bno AND EXISTS(

SELECT Cno

FROM LoanT

WHERE Cno = CustomerT.Cno

GROUP BY Cno

HAVING COUNT(Lamount) >=3

)

SELECT *

FROM CustomerT

WHERE EXISTS

(SELECT Cno FROM LoanT WHERE Lamount > 8000)

--当>8000的不存在,则返回空

SELECT *

FROM CustomerT

WHERE EXISTS

(SELECT * FROM CustomerT WHERE Cnature = N'三资')

SELECT *

FROM CustomerT, BankT

--或--

SELECT *

FROM CustomerT CROSS JOIN BankT

SELECT *

FROM CustomerT

WHERE Cno != ANY(SELECT Cno FROM LoanT)

SELECT T1.Cno 课程号, Bno 学生号, Lamount 成绩, 平均分数

FROM (SELECT Cno,AVG(Lamount) 平均分 FROM LoanT GROUP BY Cno) T1,LoanT T2

WHERE T1.Cno = T2.Cno AND Lamount > 平均分

SELECT * FROM LoanT L1

WHERE Lamount > (SELECT AVG(Lamount) FROM LoanT

WHERE L1.Cno = Cno)

EXEC sp_helpindex 表名

EXEC sp_help BankT

EXEC sp_helpindex BankT_copy1

ALTER TABLE BankT_copy1

ADD CONSTRAINT UQ_Bank UNIQUE NONCLUSTERED(Bname)

DROP INDEX UQ_Bank ON BankT_copy1

DROP TABLE BankT_copy1

SELECT *

INTO test

FROM BankT

ALTER TABLE test

ADD CONSTRAINT PK_Bno PRIMARY KEY(Bno)

EXEC sp_helpindex test

DROP INDEX PK_Bno ON test

ALTER TABLE test

DROP CONSTRAINT PK_Bno

EXEC sp_helptext sp_help -- 显示系统存储过程sp_help的创建文本

CREATE PROCEDURE up_TopLoans AS

SELECT TOP 5 Lamount, Cname

FROM CustomerT C JOIN LoanT L ON C.Cno = L.Cno

ORDER BY Lamount DESC; -- 显示并列两个结果

SELECT TOP 5 Lamount, Cname

FROM CustomerT C JOIN LoanT L ON C.Cno = L.Cno

ORDER BY Lamount ASC

EXEC up_TopLoans

CREATE PROCEDURE up_SearchLoan @custName nvarchar(10)

AS

SELECT Cname, L.*

FROM LoanT L JOIN CustomerT C ON L.Cno = C.Cno

WHERE Cname = @custName

EXEC up_SearchLoan N'华森装饰公司'

EXEC up_SearchLoan @custName =

CREATE PROCEDURE up_SearchLoanOutput @custName nvarchar(10),

@sumLamount NUMERIC(10,2) OUTPUT

AS

SELECT Cname, L.*

FROM LoanT L JOIN CustomerT C ON L.Cno = C.Cno

WHERE Cname = @custName

SELECT @sumLamount = SUM(Lamount)

FROM LoanT L JOIN CustomerT C ON L.Cno = C.Cno

WHERE Cname = @custName

DECLARE @sum NUMERIC(10,2)

EXEC up_SearchLoanOutput N'华森装饰公司',@sum OUTPUT

SELECT N'该客户的贷款总款为：'+STR(@sum)

CREATE PROCEDURE up_SearchLoanReturn @custName nvarchar(10) AS

DECLARE @sumLamount NUMERIC(10,2)

SELECT Cname, L.*

FROM LoanT L, CustomerT C

WHERE L.Cno = C.Cno AND Cname = @custName

SELECT @sumLamount = SUM(Lamount)

FROM LoanT L, CustomerT C

WHERE L.Cno = C.Cno AND Cname = @custName

RETURN @sumLamount

DECLARE @sum NUMERIC(10,2)

EXEC @sum = up_SearchLoanReturn N'华森装饰公司'

SELECT N'贷款金额为：'+STR(@sum)

CREATE TRIGGER Tri_CustomerT ON CustomerT

AFTER UPDATE

AS

IF UPDATE(Cno)

BEGIN

RAISERROR('不能修改客户代码',16,2)

ROLLBACK

END

UPDATE CustomerT

SET Cno = 'V001'

WHERE Cno = 'C001'

CREATE TRIGGER Tri_BankT ON test

AFTER UPDATE

AS

IF UPDATE(Bno)

BEGIN

RAISERROR('不能修改客户代码',16,2)

ROLLBACK

END

UPDATE test

SET Bno = 'V001'

WHERE Bno = 'G0101'

DROP TABLE test