数据库Student中表的结构如下： （1）学生信息表T_studnet（stu_no，stu_name，stu_sex，stu_age） 字段含义及要求：stu_no：学号，不能为空，主键；stu_name：姓名，不能为空；stu_sex：性别，不能为空；stu_age：年龄，不能为空。 （2）课程信息表T_course（cou_no，cou_name） 字段含义及要求：cou_no：课程号，不能为空，主键；cou_name：课程名，不能为空。 （3）成绩表：T_achievement（stu_no，cou_no，achievement） 字段含义及要求：stu_no：学号，不能为空，cou_no：课程号，学号和课程号一起为主键，achievement：成绩，可以为空。 利用Transact－SQl语言完成下列操作 （1）创建学生成绩视图v_studnet(stu_name,cou_name, achievement) （2）创建统计视图V_count，功能是实现统计出各科的不及格的人数。 （3）创建一个存储过程P_find，实现通过学生的学号来查询对应这个学生的姓名。要求有一个输入参数和一个输出参数。 （4）为t_ achievement表创建一个触发器tr_delete，当对表t_achievement进行删除数据时，提示“你无权删除数据”，并取消删除的操作。
MySQL？
自己选择数据库文件保存目录，目前保存在[D:\Student]
表结构创建
USE [master]
GO
CREATE DATABASE [Student]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Student', FILENAME = N'D:\Student\Student.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Student_log', FILENAME = N'D:\Student\Student_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Student] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Student].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Student] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Student] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Student] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Student] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Student] SET ARITHABORT OFF
GO
ALTER DATABASE [Student] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Student] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Student] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Student] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Student] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Student] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Student] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Student] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Student] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Student] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Student] SET DISABLE_BROKER
GO
ALTER DATABASE [Student] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Student] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Student] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Student] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Student] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Student] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Student] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Student] SET RECOVERY FULL
GO
ALTER DATABASE [Student] SET MULTI_USER
GO
ALTER DATABASE [Student] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Student] SET DB_CHAINING OFF
GO
ALTER DATABASE [Student] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [Student] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [Student]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_Achievement](
[Stu_no] [int] NOT NULL,
[Cou_no] [int] NOT NULL,
[Achievement] [decimal](18, 2) NULL,
CONSTRAINT [PK_T_Achievement] PRIMARY KEY CLUSTERED
(
[Stu_no] ASC,
[Cou_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_Course](
[Cou_no] [int] IDENTITY(1,1) NOT NULL,
[Cou_name] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_T_Course] PRIMARY KEY CLUSTERED
(
[Cou_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_Studnet](
[Stu_no] [int] IDENTITY(1,1) NOT NULL,
[Stu_name] [nvarchar](50) NOT NULL,
[Stu_sex] [nvarchar](10) NOT NULL,
[Stu_age] [int] NOT NULL,
CONSTRAINT [PK_T_Studnet] PRIMARY KEY CLUSTERED
(
[Stu_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[T_Achievement] ([Stu_no], [Cou_no], [Achievement]) VALUES (1, 1, CAST(95.50 AS Decimal(18, 2)))
INSERT [dbo].[T_Achievement] ([Stu_no], [Cou_no], [Achievement]) VALUES (1, 2, CAST(100.00 AS Decimal(18, 2)))
INSERT [dbo].[T_Achievement] ([Stu_no], [Cou_no], [Achievement]) VALUES (2, 1, CAST(78.00 AS Decimal(18, 2)))
INSERT [dbo].[T_Achievement] ([Stu_no], [Cou_no], [Achievement]) VALUES (2, 2, CAST(59.50 AS Decimal(18, 2)))
SET IDENTITY_INSERT [dbo].[T_Course] ON
INSERT [dbo].[T_Course] ([Cou_no], [Cou_name]) VALUES (1, N'语文')
INSERT [dbo].[T_Course] ([Cou_no], [Cou_name]) VALUES (2, N'数学')
SET IDENTITY_INSERT [dbo].[T_Course] OFF
SET IDENTITY_INSERT [dbo].[T_Studnet] ON
INSERT [dbo].[T_Studnet] ([Stu_no], [Stu_name], [Stu_sex], [Stu_age]) VALUES (1, N'张三', N'男', 19)
INSERT [dbo].[T_Studnet] ([Stu_no], [Stu_name], [Stu_sex], [Stu_age]) VALUES (2, N'李四', N'女', 18)
SET IDENTITY_INSERT [dbo].[T_Studnet] OFF
ALTER TABLE [dbo].[T_Achievement] WITH CHECK ADD CONSTRAINT [FK_T_Achievement_T_Course] FOREIGN KEY([Cou_no])
REFERENCES [dbo].[T_Course] ([Cou_no])
GO
ALTER TABLE [dbo].[T_Achievement] CHECK CONSTRAINT [FK_T_Achievement_T_Course]
GO
ALTER TABLE [dbo].[T_Achievement] WITH CHECK ADD CONSTRAINT [FK_T_Achievement_T_Studnet] FOREIGN KEY([Stu_no])
REFERENCES [dbo].[T_Studnet] ([Stu_no])
GO
ALTER TABLE [dbo].[T_Achievement] CHECK CONSTRAINT [FK_T_Achievement_T_Studnet]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Achievement', @level2type=N'COLUMN',@level2name=N'Stu_no'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'课程号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Achievement', @level2type=N'COLUMN',@level2name=N'Cou_no'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'成绩' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Achievement', @level2type=N'COLUMN',@level2name=N'Achievement'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'成绩表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Achievement'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'课程号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Course', @level2type=N'COLUMN',@level2name=N'Cou_no'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'课程名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Course', @level2type=N'COLUMN',@level2name=N'Cou_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'课程信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Course'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Studnet', @level2type=N'COLUMN',@level2name=N'Stu_no'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Studnet', @level2type=N'COLUMN',@level2name=N'Stu_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Studnet', @level2type=N'COLUMN',@level2name=N'Stu_sex'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Studnet', @level2type=N'COLUMN',@level2name=N'Stu_age'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学生信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_Studnet'
GO
USE [master]
GO
ALTER DATABASE [Student] SET READ_WRITE
GO
创建学生成绩视图
USE [Student]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_Studnet]
AS
SELECT s.Stu_name, c.Cou_name, a.Achievement
FROM T_Achievement a
INNER JOIN T_Course c ON c.Cou_no = a.Cou_no
INNER JOIN T_Studnet s ON s.Stu_no = a.Stu_no
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学生成绩视图' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_Studnet'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "T_Achievement"
Begin Extent =
Top = 6
Left = 38
Bottom = 127
Right = 200
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "T_Course"
Begin Extent =
Top = 6
Left = 238
Bottom = 108
Right = 387
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "T_Studnet"
Begin Extent =
Top = 6
Left = 425
Bottom = 146
Right = 569
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_Studnet'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_Studnet'
GO
统计出各科的不及格的人数
USE [Student]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_Count]
AS
SELECT c.Cou_name, COUNT(a.Achievement) AS FlunkCount
FROM T_Achievement a
INNER JOIN T_Course c ON c.Cou_no = a.Cou_no
GROUP BY c.Cou_name, a.Achievement
HAVING a.Achievement < 60
GO
通过学生的学号来查询对应这个学生的姓名
USE [Student]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[P_Find]
(
@Stu_no INT,
@Stu_name NVARCHAR(50) OUTPUT
)
AS
SELECT @Stu_name = Stu_name FROM T_Studnet WHERE Stu_no=@Stu_no
GO
为t_ achievement表创建一个触发器tr_delete
USE [Student]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Tr_Delete]
ON [dbo].[T_Achievement]
Instead of DELETE
AS
PRINT '你无权删除数据'