SQL 实用基础代码

创建数据库
CREATE DATABASE DB_Student --创建数据库
ON PRIMARY
(
NAME=N'DB_Student', --创建数据库.mdf文件
FILENAME=N'D:\database\db_Student.mdf',
SIZE=3072KB,
FILEGROWTH=1024KB
)
LOG
ON
(
NAME=N'DB_Studenta', --创建日志文件，不能重复mdf
FILENAME=N'D:\database\db_Student.ldf',
SIZE=1024KB,
FILEGROWTH=10%
)
GO

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

创建外键,B中存在外键，且外键是A主键，则应该先创建A，再创建B
bAId INT FOREIGN KEY(bAId) REFERENCES A(aId)

BACKUP DATABASE [hanson]
TO DISK = N'D:\database\db_hanson_20120719.bak'
WITH DESCRIPTION = N'12年7月19日的备份',
NAME = N'db_hanson 的备份',
SKIP , NOREWIND , NOUNLOAD , STATS = 10
GO

BACKUP DATABASE [hanson] --差异备份
TO DISK = N'D:\database\db_hanson_20120719.bak'
WITH DIFFERENTIAL,
DESCRIPTION = N'12年7月19日的备份',
NOFORMAT , NOINIT,
NAME = N'db_hanson 的差异备份',
SKIP , NOREWIND , NOUNLOAD , STATS = 10
GO

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

USE Homework
SELECT DISTINCT T.Name
FROM T
WHERE T.Name
NOT IN(SELECT T.Name FROM T WHERE T.fenshu<80)

1、查询出所有数据中拨打长途号码（对方号码以0开头）的总时长
2、查询出本月通话总时长最多的前三个呼叫员的编号
3、查询出本月拨打电话次数最多的前三个呼叫员的编号

SELECT SUM(DATEDIFF(second,Inquiry_Begin,Inquiry_End))
FROM Tel_Inquiry
WHERE Inquiry_Caller LIKE '0%'

SELECT TOP 3 Inquiryer_Id ,SUM(DATEDIFF(second,Inquiry_Begin,Inquiry_End))
FROM Tel_Inquiry
GROUP BY Inquiryer_Id
ORDER BY SUM(DATEDIFF(second,Inquiry_Begin,Inquiry_End))
DESC

SELECT TOP 3 Inquiryer_Id,COUNT(Inquiryer_Id) AS '拨打次数'
FROM Tel_Inquiry
GROUP BY Inquiryer_Id
ORDER BY COUNT(Inquiryer_Id)
DESC

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1、查询20号部门中的员工信息

SELECT EMP.DEPTNO,DEPT.DNAME,EMP.EMPNO,EMP.ENAME,EMP.HIREDATE,EMP.JOB,EMP.MGR,EMP.SAL,EMP.COMM,DEPT.LOC
FROM EMP
LEFT JOIN DEPT
ON DEPT.DEPTNO=EMP.DEPTNO
WHERE EMP.DEPTNO=20

2、查找出名字以“MA”开头的全部员工信息

SELECT EMP.DEPTNO,DEPT.DNAME,EMP.EMPNO,EMP.ENAME,EMP.HIREDATE,EMP.JOB,EMP.MGR,EMP.SAL,EMP.COMM,DEPT.LOC
FROM EMP
LEFT JOIN DEPT
ON DEPT.DEPTNO=EMP.DEPTNO
WHERE EMP.ENAME LIKE 'MA%'

3、按工种升序排列显示全部员工的有关情况

SELECT EMP.DEPTNO,DEPT.DNAME,EMP.EMPNO,EMP.ENAME,EMP.HIREDATE,EMP.JOB,EMP.MGR,EMP.SAL,EMP.COMM,DEPT.LOC
FROM EMP
LEFT JOIN DEPT
ON DEPT.DEPTNO=EMP.DEPTNO
ORDER BY EMP.JOB
ASC

4、查询统计各部门的员工数据

SELECT DEPT.DEPTNO,DEPT.DNAME,COUNT(*) AS '员工人数'
FROM EMP
LEFT JOIN DEPT
ON DEPT.DEPTNO=EMP.DEPTNO
GROUP BY DEPT.DEPTNO,DEPT.DNAME

5、查找出工资高于20号部门中所有员工的人员信息

SELECT EMP.DEPTNO,DEPT.DNAME,EMP.EMPNO,EMP.ENAME,EMP.HIREDATE,EMP.JOB,EMP.MGR,EMP.SAL,EMP.COMM,DEPT.LOC
FROM EMP
LEFT JOIN DEPT
ON DEPT.DEPTNO=EMP.DEPTNO
WHERE EMP.SAL>(SELECT MAX(EMP.SAL) FROM EMP WHERE EMP.DEPTNO=20)

6、为EMP表创建一个视图emp10_view，要求只包括10号部门员工的有关信息。
CREATE VIEW MYVIEWS
AS
SELECT EMP.DEPTNO,DEPT.DNAME,EMP.EMPNO,EMP.ENAME,EMP.HIREDATE,EMP.JOB,EMP.MGR,EMP.SAL,EMP.COMM,DEPT.LOC
FROM EMP
LEFT JOIN DEPT
ON DEPT.DEPTNO=EMP.DEPTNO
WHERE DEPT.DEPTNO=10

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

一张表dtTest里面有year，mouth，day三个字段，分别表示年月日，如何查找出今天之后的数据？
SELECT * FROM dtTest
WHERE GETDATE()<CONVERT(DATE,CONVERT(CHAR(4),Years)+'-'+CONVERT(CHAR(2),Mouths)+'-'+CONVERT(CHAR(2),Daies))

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

有一张学科分值表Score，里面有3个字段：语文，数学，英语。比如有1条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询该表记录并按以下条件显示出来：（大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。）
显示格式：
语文 数学 英语
及格 优秀 不及格

SELECT
CASE WHEN 语文>=80 THEN '优秀' WHEN 语文>=60 THEN '及格' ELSE '不及格' END '语文',
CASE WHEN 数学>=80 THEN '优秀' WHEN 数学>=60 THEN '及格' ELSE '不及格' END '数学',
CASE WHEN 英语>=80 THEN '优秀' WHEN 英语>=60 THEN '及格' ELSE '不及格' END '英语'
FROM Score

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

创建索引

CREATE CLUSTERED INDEX riqi_person on person(date)
在person表的date字段上面创建名为riqi_person的【聚集索引】

CREATE NONCLUSTERED INDEX riqi_person on person(date)
在person表的date字段上面创建名为riqi_person的【非聚集索引】

CREATE CLUSTERED INDEX date_person on person(date,id)
在person表的date，age字段上面创建名为riqi_person的【复合聚集索引】

CREATE NONCLUSTERED INDEX date_person on person(date,age)
在person表的date，age字段上面创建名为riqi_person的【复合非聚集索引】

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL编程示例 （1）
DECLARE @i INT --声明变量
SET @i = 1
DECLARE @no VARCHAR(15)
SET @no='201200000001'

WHILE @i < 1000000
BEGIN
SET @i =@i +1
SET @no =cast(cast(@no as bigint) + 1 as varchar)
INSERT INTO StudentInfo
VALUES
(@no,'HANSON','male','302')
END

SQL编程示例 （2）
DECLARE @PhoneNumber CHAR(12)='15305535987'
DECLARE @AreaNum CHAR(4)
SET @AreaNum=SUBSTRING(@PhoneNumber,4,4);
IF(@AreaNum='0551')
SELECT '合肥' AS '地区'
ELSE IF(@AreaNum='0552')
SELECT'蚌埠' AS '地区'
ELSE IF(@AreaNum='0553')
SELECT '芜湖' AS '地区'
ELSE
SELECT '其他' AS '地区'

SQL编程示例 （3）
DECLARE @i INT
SET @i = 0
WHILE @i < 10
BEGIN
SET @i =@i +1
IF @i=4
CONTINUE
ELSE IF @i=8
BREAK
ELSE
PRINT @i
END

事务处理 @@error

SELECT * FROM Account
BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
UPDATE Account SET ABalance=ABalance-200
WHERE AName='李四'
SET @errorSum=@errorSum+@@ERROR
UPDATE Account SET ABalance=ABalance+1000
WHERE AName='张三'
SET @errorSum=@errorSum+@@ERROR
IF @errorSum<>0 --如果有错误
BEGIN
print '交易失败'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
print '交易成功'
COMMIT TRANSACTION
END
GO
--显示转账后的账户余额
SELECT * FROM Account
GO

事务处理
--try-catch捕获并处理
BEGIN TRY
BEGIN TRANSACTION
UPDATE Account
SET ABalance=ABalance-200
WHERE AName='李四'
UPDATE Account
SET ABalance=ABalance+200
WHERE AName='张三'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
rollback
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = '出现了错误，错误信息：'+ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=
GO
BEGIN TRANSACTION;
BEGIN TRY
--李四取出元
UPDATE Account SET Balance=Balance-200 --更新帐户余额
WHERE Name=N'李四'
--张三存入元
UPDATE Account SET Balance=Balance+1000 --更新帐户余额
WHERE Name=N'张三'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

+++++++++++++++++++++++++++++++++++++++++

创建存储过程
CREATE PROCEDURE avaday
@DAYS INT,
@ID INT
AS
BEGIN
SELECT
CASE WHEN StaffSex='female' AND (StaffUsed+@DAYS)<=5 THEN '批准'
WHEN StaffSex='male' AND (StaffUsed+@DAYS)<=2 THEN '批准'
ELSE '不批准' END 批复结果
FROM Vacation
WHERE StaffId=@ID
END

EXEC avaday @ID=101,@DAYS=1

--with 临时表

WITH
S
AS
(
SELECT TOP 2 Inquiry_Caller,count(*) AS 电话次数
FROM Tel_Inquiry
GROUP BY Inquiry_Caller
ORDER BY count(Inquiry_Caller) DESC
)
SELECT TOP 1 *
FROM S
ORDER BY 电话次数