SQL考核

一、需求"员工需要上传资料到系统，并且为文档进行了分类展示"注按照需求绘画ER图（可不画）,理清实体关系对象，（上传功能暂忽略）

答：

二、根据ER创建表结构，注意主键，外键等约束

答：

/*
Navicat SQL Server Data Transfer

Source Server         : SQL2000
Source Server Version : 80000
Source Host           : 127.0.0.1:1433
Source Database       : document
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 80000
File Encoding         : 65001

Date: 2014-05-13 14:35:13
*/


-- ----------------------------
-- Table structure for docClass
-- ----------------------------
DROP TABLE [dbo].[docClass]
GO
CREATE TABLE [dbo].[docClass] (
[classId] int NOT NULL ,
[className] varchar(100) NOT NULL ,
[createTime] varchar(40) NOT NULL ,
[path] text NULL ,
[programId] varchar(100) NULL 
)


GO

-- ----------------------------
-- Records of docClass
-- ----------------------------
INSERT INTO [dbo].[docClass] ([classId], [className], [createTime], [path], [programId]) VALUES (N'1', N'A', N'2014-1-2', N'/', N'001')
GO
GO
INSERT INTO [dbo].[docClass] ([classId], [className], [createTime], [path], [programId]) VALUES (N'2', N'A1', N'2014-1-3', N'/A/', N'001001')
GO
GO
INSERT INTO [dbo].[docClass] ([classId], [className], [createTime], [path], [programId]) VALUES (N'3', N'A1.1', N'2014-2-1', N'/A/A1/', N'001001001')
GO
GO
INSERT INTO [dbo].[docClass] ([classId], [className], [createTime], [path], [programId]) VALUES (N'4', N'A1.2', N'2014-3-1', N'/A/A1/', N'001001002')
GO
GO
INSERT INTO [dbo].[docClass] ([classId], [className], [createTime], [path], [programId]) VALUES (N'5', N'B1', N'2014-3-2', N'/A/', N'001002')
GO
GO
INSERT INTO [dbo].[docClass] ([classId], [className], [createTime], [path], [programId]) VALUES (N'6', N'B1.1', N'2014-3-26', N'/A/B1/', N'001002001')
GO
GO
INSERT INTO [dbo].[docClass] ([classId], [className], [createTime], [path], [programId]) VALUES (N'7', N'B2.2', N'2014-4-1', N'/A/B2/', N'001002002')
GO
GO

-- ----------------------------
-- Table structure for document
-- ----------------------------
DROP TABLE [dbo].[document]
GO
CREATE TABLE [dbo].[document] (
[docId] int NOT NULL ,
[userId] int NOT NULL ,
[classId] int NOT NULL ,
[uploadDate] datetime NULL 
)


GO

-- ----------------------------
-- Records of document
-- ----------------------------
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'1', N'5', N'4', N'2008-01-09 05:06:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'2', N'4', N'2', N'2008-01-07 23:43:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'3', N'3', N'6', N'2008-01-01 13:04:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'4', N'5', N'3', N'2008-01-18 00:00:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'5', N'2', N'7', N'2008-01-19 00:00:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'6', N'2', N'3', N'2008-01-22 00:00:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'7', N'1', N'2', N'2007-01-23 00:00:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'8', N'1', N'5', N'2005-01-12 00:00:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'9', N'2', N'1', N'1997-01-20 00:00:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'10', N'3', N'7', N'1996-01-21 00:00:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'11', N'4', N'6', N'1995-01-01 00:00:00.000')
GO
GO
INSERT INTO [dbo].[document] ([docId], [userId], [classId], [uploadDate]) VALUES (N'12', N'2', N'2', N'1995-01-01 00:00:00.000')
GO
GO

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE [dbo].[users]
GO
CREATE TABLE [dbo].[users] (
[userId] int NOT NULL ,
[userName] char(50) NOT NULL ,
[QuanXian] binary(1) NULL 
)


GO

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO [dbo].[users] ([userId], [userName], [QuanXian]) VALUES (N'1', N'user1                                             ', 0x01)
GO
GO
INSERT INTO [dbo].[users] ([userId], [userName], [QuanXian]) VALUES (N'2', N'user2                                             ', 0x01)
GO
GO
INSERT INTO [dbo].[users] ([userId], [userName], [QuanXian]) VALUES (N'3', N'user3                                             ', 0x01)
GO
GO
INSERT INTO [dbo].[users] ([userId], [userName], [QuanXian]) VALUES (N'4', N'user4                                             ', 0x01)
GO
GO
INSERT INTO [dbo].[users] ([userId], [userName], [QuanXian]) VALUES (N'5', N'user5                                             ', 0x01)
GO
GO

-- ----------------------------
-- Procedure structure for dir_path
-- ----------------------------
DROP PROCEDURE [dbo].[dir_path]
GO
CREATE PROCEDURE [dbo].[dir_path]
  -- @dir_id varchar,
  @dir_name varchar(100)
AS
BEGIN
	DECLARE @dir_id VARCHAR(100)
	DECLARE @path VARCHAR(100)
	SET @path = '';
  SELECT @dir_id = programId FROM docClass
	WHERE className=@dir_name
	loop:
	IF(@dir_id='001')
		SET @path = 'A' + @path
	ELSE
		BEGIN
			-- 递归
			SELECT @dir_name = className FROM docClass
			WHERE programId = @dir_id
			SET @path = '->' + @dir_name + @path
			SET @dir_id = LEFT(@dir_id, LEN(@dir_id)-3)
			GOTO loop
		END
	PRINT @path
END
GO

-- ----------------------------
-- Procedure structure for Factorial_ap
-- ----------------------------
DROP PROCEDURE [dbo].[Factorial_ap]
GO
CREATE PROCEDURE [dbo].[Factorial_ap]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number - 1
EXEC Factorial_ap @In, @Out OUTPUT
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN

GO

-- ----------------------------
-- Indexes structure for table docClass
-- ----------------------------
CREATE INDEX [_WA_Sys_className_79A81403] ON [dbo].[docClass]
([className] ASC) 
GO
CREATE INDEX [_WA_Sys_programId_79A81403] ON [dbo].[docClass]
([programId] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table docClass
-- ----------------------------
ALTER TABLE [dbo].[docClass] ADD PRIMARY KEY ([classId])
GO

-- ----------------------------
-- Indexes structure for table document
-- ----------------------------
CREATE INDEX [_WA_Sys_userId_77BFCB91] ON [dbo].[document]
([userId] ASC) 
GO
CREATE INDEX [_WA_Sys_classId_77BFCB91] ON [dbo].[document]
([classId] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table document
-- ----------------------------
ALTER TABLE [dbo].[document] ADD PRIMARY KEY ([docId])
GO

-- ----------------------------
-- Indexes structure for table users
-- ----------------------------
CREATE INDEX [_WA_Sys_userName_75D7831F] ON [dbo].[users]
([userName] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table users
-- ----------------------------
ALTER TABLE [dbo].[users] ADD PRIMARY KEY ([userId])
GO

-- ----------------------------
-- Uniques structure for table users
-- ----------------------------
ALTER TABLE [dbo].[users] ADD UNIQUE ([userName] ASC)
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[document]
-- ----------------------------
ALTER TABLE [dbo].[document] ADD FOREIGN KEY ([classId]) REFERENCES [dbo].[docClass] ([classId]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[document] ADD FOREIGN KEY ([userId]) REFERENCES [dbo].[users] ([userId]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

三、录入测试数据目录和文档(要求分类树有至少3个层级)，查询内容为:某个指定节点ID下上传文件列表（包含子节点），并按照创建时间排序展示

答：

SELECT docId, userName, className, path, uploadDate
FROM (document JOIN users ON document.userId=users.userId) JOIN docClass on document.classId = docClass.classId
WHERE programId LIKE 
(SELECT programId FROM docClass
WHERE className = 'A') + '%'
ORDER BY uploadDate

四、统计查询“每个用户各上传了多少个文件”

答：

SELECT users.userName, COUNT(docId) AS amount 
FROM document JOIN users ON document.userId = users.userId
GROUP BY users.userName

五、创建存储过程如：传入当前目录Id，传入某一目录Id,返回该目录路径完整路径

如传入 A1.2的ID返回结果为A->A1->A1.2

目录结构如下：

  A

  |-A1

  | |-A1.1

  | |-A1.2

  |-B1

    |-B1.1

    |-B2.2

答：

思路：

dig(@dir_id)

{

if(@dir_id == '001') print "A"

else 

{

@dir_id = left(@dir_id,len(@dir_id)-3)

dig(@dir_id);

select className from docClass

where programId = programId 

print "->" + className;

}

}

实现：

ALTER PROCEDURE [dbo].[dir_path]

  -- @dir_id varchar,

  @dir_name varchar(100)

AS

BEGIN

DECLARE @dir_id VARCHAR(100)

DECLARE @path VARCHAR(100)

SET @path = '';

  SELECT @dir_id = programId FROM docClass

WHERE className=@dir_name

-- 避免查找不存在目录导致死循环

if(@dir_id LIKE '001%')

BEGIN

loop:

IF(@dir_id='001')

SET @path = 'A' + @path

ELSE

BEGIN

-- 递归

SELECT @dir_name = className FROM docClass

WHERE programId = @dir_id

SET @path = '->' + @dir_name + @path

SET @dir_id = LEFT(@dir_id, LEN(@dir_id)-3)

GOTO loop

END

END

ELSE

SET @path = '查无此目录'

PRINT @path

END

执行：

EXEC dir_path @dir_name='A1.2'