在Navicat 工具平台上创建数据库对象
1、创建 【GZHX教务数据库】
基本步骤：
方法一： 利用创建语句创建，点击【新建查询】
输入命令并点击运行：CREATE DATABASE GZHX教务数据库
方法二：在Navicat 连接 SQL server 上，右击【新建数据库】输入数据库名（如：GZHX教务数据库）等信息，点击【确定】便可建立数据库。
2、在数据库内创建数据表等对象
方法一：直接利用 CREATE TABLE 表名（表结构描述）
例：创建 [学生表]
use GZHX教务数据库
GO
CREATE TABLE 学生表 (
学号 char(9) primary key,
姓名 nchar(4) NOT NULL,
性别 nchar(1) NULL,
民族 nchar(8) NULL,
出生日期 datetime NULL,
专业班级 nchar(10) NULL,
身份证号 char(18) NULL
)
GO
方法二：直接用手工操作完成。
具体步骤：在数据库有【表】上右击【新建表】，然后输入表结构信息描述，并以表名保存。
方法三：直接在数据库的【新建查询】打开已有的创建数据库对象的脚本程序，并运行。
如：在GZHX教务数据库，创建数据库对象的脚本程序
/*
Navicat Premium Data Transfer
Source Server : SQL SERVER
Source Server Type : SQL Server
Source Server Version : 11003128
Source Host : localhost:1433
Source Catalog : GZHX教务数据库
Source Schema : dbo
Target Server Type : SQL Server
Target Server Version : 11003128
File Encoding : 65001
Date: 06/06/2024 08:23:33
*/
-- ----------------------------
-- Table structure for 成绩表
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[成绩表]') AND type IN ('U'))
DROP TABLE [dbo].[成绩表]
GO
CREATE TABLE [dbo].[成绩表] (
[学号] char(9) COLLATE Chinese_PRC_CI_AS NOT NULL,
[课程号] char(6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[成绩] decimal(4,1) NULL,
[任课教师号] char(6) COLLATE Chinese_PRC_CI_AS NULL
)
GO
ALTER TABLE [dbo].[成绩表] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 成绩表
-- ----------------------------
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905101', N'105001', N'86.0', N'603010')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905101', N'106001', N'90.0', N'603011')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905101', N'106002', N'90.0', N'603020')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905101', N'601004', N'65.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905101', N'901001', N'88.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905102', N'105001', N'89.0', N'603010')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905102', N'106001', N'73.0', N'603011')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905102', N'106002', N'91.0', N'603020')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905102', N'601004', N'69.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201905102', N'901001', N'68.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906101', N'105001', N'79.0', N'603010')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906101', N'106001', N'83.0', N'603011')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906101', N'106002', N'81.0', N'603020')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906101', N'601004', N'83.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906101', N'901001', N'86.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906102', N'105001', N'79.0', N'603010')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906102', N'106001', N'83.0', N'603011')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906102', N'106002', N'81.0', N'603020')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906102', N'601004', N'83.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906102', N'901001', N'86.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906202', N'105001', N'79.0', N'603010')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906202', N'106001', N'83.0', N'603011')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906501', N'106002', N'81.0', N'603020')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906501', N'601004', N'83.0', N'603022')
GO
INSERT INTO [dbo].[成绩表] ([学号], [课程号], [成绩], [任课教师号]) VALUES (N'201906502', N'901001', N'86.0', N'603022')
GO
-- ----------------------------
-- Table structure for 教师表
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[教师表]') AND type IN ('U'))
DROP TABLE [dbo].[教师表]
GO
CREATE TABLE [dbo].[教师表] (
[教师号] char(6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[姓名] char(8) COLLATE Chinese_PRC_CI_AS NULL,
[性别] char(2) COLLATE Chinese_PRC_CI_AS NULL,
[专业] nchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[职称] nchar(6) COLLATE Chinese_PRC_CI_AS NULL,
[出生日期] datetime NULL,
[院系号] tinyint NULL
)
GO
ALTER TABLE [dbo].[教师表] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 教师表
-- ----------------------------
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'103011', N'王芳芳 ', N'女', N'财会 ', N'讲师 ', N'1983-11-15 00:00:00.000', N'1')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'103021', N'刘军芳 ', N'女', N'财会 ', N'副教授 ', N'1980-12-15 00:00:00.000', N'1')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'603010', N'李小鹏 ', N'男', N'计算机 ', N'副教授 ', N'1963-09-05 00:00:00.000', N'6')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'603011', N'林小芳 ', N'女', N'计算机 ', N'讲师 ', N'1983-11-15 00:00:00.000', N'6')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'603012', N'刘军 ', N'男', N'计算机 ', N'讲师 ', N'1982-12-18 00:00:00.000', N'6')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'603020', N'谢俊 ', N'男', N'计算机 ', N'教授 ', N'1958-09-05 00:00:00.000', N'6')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'603021', N'李红芳 ', N'女', N'计算机 ', N'助教 ', N'1986-11-15 00:00:00.000', N'6')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'603022', N'刘明明 ', N'男', N'计算机 ', N'副教授 ', N'1975-12-13 00:00:00.000', N'6')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'903010', N'谢江明 ', N'男', N'英语 ', N'讲师 ', N'1982-06-13 00:00:00.000', N'9')
GO
INSERT INTO [dbo].[教师表] ([教师号], [姓名], [性别], [专业], [职称], [出生日期], [院系号]) VALUES (N'903020', N'谢芳菲 ', N'女', N'英语 ', N'副教授 ', N'1981-08-13 00:00:00.000', N'9')
GO
-- ----------------------------
-- Table structure for 课程表
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[课程表]') AND type IN ('U'))
DROP TABLE [dbo].[课程表]
GO
CREATE TABLE [dbo].[课程表] (
[课程号] char(6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[课程名] nchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[学时] tinyint NULL,
[学分] float(53) NULL,
[类别] nchar(10) COLLATE Chinese_PRC_CI_AS NULL
)
GO
ALTER TABLE [dbo].[课程表] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 课程表
-- ----------------------------
INSERT INTO [dbo].[课程表] ([课程号], [课程名], [学时], [学分], [类别]) VALUES (N'105001', N'计算机网络基础 ', N'68', N'4', N'必修课 ')
GO
INSERT INTO [dbo].[课程表] ([课程号], [课程名], [学时], [学分], [类别]) VALUES (N'106001', N'数据库技术 ', N'64', N'4', N'必修课 ')
GO
INSERT INTO [dbo].[课程表] ([课程号], [课程名], [学时], [学分], [类别]) VALUES (N'106002', N'JAVA程序设计 ', N'90', N'5', N'必修课 ')
GO
INSERT INTO [dbo].[课程表] ([课程号], [课程名], [学时], [学分], [类别]) VALUES (N'106003', N'网页设计 ', N'78', N'4.5', N'必修课 ')
GO
INSERT INTO [dbo].[课程表] ([课程号], [课程名], [学时], [学分], [类别]) VALUES (N'601004', N'人工智能技术 ', N'36', N'2', N'选修课 ')
GO
INSERT INTO [dbo].[课程表] ([课程号], [课程名], [学时], [学分], [类别]) VALUES (N'901001', N'大学英语 ', N'90', N'5', N'必修课 ')
GO
-- ----------------------------
-- Table structure for 学生表
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[学生表]') AND type IN ('U'))
DROP TABLE [dbo].[学生表]
GO
CREATE TABLE [dbo].[学生表] (
[学号] char(9) COLLATE Chinese_PRC_CI_AS NOT NULL,
[姓名] nchar(4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[性别] nchar(1) COLLATE Chinese_PRC_CI_AS NULL,
[民族] nchar(8) COLLATE Chinese_PRC_CI_AS NULL,
[出生日期] datetime NULL,
[专业班级] nchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[身份证号] char(18) COLLATE Chinese_PRC_CI_AS NULL
)
GO
ALTER TABLE [dbo].[学生表] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of 学生表
-- ----------------------------
INSERT INTO [dbo].[学生表] ([学号], [姓名], [性别], [民族], [出生日期], [专业班级], [身份证号]) VALUES (N'201905101', N'刘红森 ', N'男', N'汉 ', N'1999-05-13 00:00:00.000', N'19网络1班 ', NULL)
GO
INSERT INTO [dbo].[学生表] ([学号], [姓名], [性别], [民族], [出生日期], [专业班级], [身份证号]) VALUES (N'201905102', N'黄河 ', N'女', N'回 ', N'2000-02-13 00:00:00.000', N'19网络1班 ', NULL)
GO
INSERT INTO [dbo].[学生表] ([学号], [姓名], [性别], [民族], [出生日期], [专业班级], [身份证号]) VALUES (N'201906101', N'李芳芳 ', N'女', N'汉 ', N'2000-05-23 00:00:00.000', N'19软件1班 ', NULL)
GO
INSERT INTO [dbo].[学生表] ([学号], [姓名], [性别], [民族], [出生日期], [专业班级], [身份证号]) VALUES (N'201906102', N'刘暖 ', N'女', N'汉 ', N'1999-07-23 00:00:00.000', N'19软件1班 ', NULL)
GO
INSERT INTO [dbo].[学生表] ([学号], [姓名], [性别], [民族], [出生日期], [专业班级], [身份证号]) VALUES (N'201906202', N'吕梁 ', N'男', N'汉 ', N'1999-09-03 00:00:00.000', N'19软件2班 ', NULL)
GO
INSERT INTO [dbo].[学生表] ([学号], [姓名], [性别], [民族], [出生日期], [专业班级], [身份证号]) VALUES (N'201906501', N'林军 ', N'女', N'回 ', N'2000-08-06 00:00:00.000', N'19软件5班 ', NULL)
GO
INSERT INTO [dbo].[学生表] ([学号], [姓名], [性别], [民族], [出生日期], [专业班级], [身份证号]) VALUES (N'201906502', N'卢森堡 ', N'男', N'汉 ', N'1999-09-03 00:00:00.000', N'19软件5班 ', NULL)
GO
-- ----------------------------
-- View structure for 成绩表视图
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[成绩表视图]') AND type IN ('V'))
DROP VIEW [dbo].[成绩表视图]
GO
CREATE VIEW [dbo].[成绩表视图] AS SELECT G.学号,姓名, 性别,专业班级,G.课程号,课程名,成绩
FROM 成绩表 G ,学生表 S ,课程表 K
WHERE S.学号=G.学号 and G.课程号=K.课程号
GO
-- ----------------------------
-- procedure structure for P_XS01
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[P_XS01]') AND type IN ('P', 'PC', 'RF', 'X'))
DROP PROCEDURE[dbo].[P_XS01]
GO
CREATE PROCEDURE [dbo].[P_XS01]
AS
SELECT 系名,COUNT(学号) AS '学生人数'
FROM XSDA
GROUP BY 系名
GO
-- ----------------------------
-- procedure structure for 查询成绩
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[查询成绩]') AND type IN ('P', 'PC', 'RF', 'X'))
DROP PROCEDURE[dbo].[查询成绩]
GO
CREATE PROCEDURE [dbo].[查询成绩]
AS
SELECT G.学号,S.姓名,G.课程号,K.课程名,G.成绩,T.姓名 任课教师,G.任课教师号
FROM [成绩表] G,[学生表] S,[课程表] K,教师表 T
WHERE G.学号=S.学号 AND G.课程号=K.课程号 AND G.任课教师号=T.教师号
GO
-- ----------------------------
-- procedure structure for P_TJ2
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[P_TJ2]') AND type IN ('P', 'PC', 'RF', 'X'))
DROP PROCEDURE[dbo].[P_TJ2]
GO
CREATE PROCEDURE [dbo].[P_TJ2]
@ZYBJ VARCHAR(10)
AS
SELECT @ZYBJ AS '专业班级',COUNT(学号) AS '人数'
FROM 学生表
WHERE 专业班级=@ZYBJ
GO
-- ----------------------------
-- procedure structure for 查询成绩F
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[查询成绩F]') AND type IN ('P', 'PC', 'RF', 'X'))
DROP PROCEDURE[dbo].[查询成绩F]
GO
CREATE PROCEDURE [dbo].[查询成绩F]
@g1 int,@g2 int --两个参数
AS
SELECT G.学号,S.姓名,G.课程号,K.课程名,G.成绩, T.姓名,G.任课教师号
FROM [成绩表] G,[学生表] S,[课程表] K,教师表 T
WHERE (成绩>=@g1 AND 成绩<=@g2) and G.学号=S.学号 AND G.课程号=K.课程号 AND G.任课教师号=T.教师号
GO
-- ----------------------------
-- procedure structure for 输出成绩表
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[输出成绩表]') AND type IN ('P', 'PC', 'RF', 'X'))
DROP PROCEDURE[dbo].[输出成绩表]
GO
GO
-- ----------------------------
-- Triggers structure for table 成绩表
-- ----------------------------
CREATE TRIGGER [dbo].[JWCJB]
ON [dbo].[成绩表]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE, DELETE
AS
PRINT '小夏提醒你：[成绩表] 记录已有变动！'
GO
CREATE TRIGGER [dbo].[xsjw_update]
ON [dbo].[成绩表]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
CREATE TRIGGER xsjw_update
ON 成绩表
AFTER UPDATE
AS
PRINT '小夏提醒你：记录已修改！'
GO
CREATE TRIGGER [dbo].[xsjwb_update]
ON [dbo].[成绩表]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE
AS
CREATE TRIGGER xsjwb_update
ON 成绩表
AFTER INSERT,UPDATE
AS
PRINT '小夏提醒你：记录已修改！'
GO
CREATE TRIGGER [dbo].[xsjwbs_update]
ON [dbo].[成绩表]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE, DELETE
AS
CREATE TRIGGER xsjwbs_update
ON 成绩表
AFTER INSERT,UPDATE,delete
AS
PRINT '小夏提醒你：记录已修改！'
GO
-- ----------------------------
-- Primary Key structure for table 成绩表
-- ----------------------------
ALTER TABLE [dbo].[成绩表] ADD CONSTRAINT [PK__成绩表__77CFF86FF177E893] PRIMARY KEY CLUSTERED ([学号], [课程号])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Primary Key structure for table 教师表
-- ----------------------------
ALTER TABLE [dbo].[教师表] ADD CONSTRAINT [PK__教师表__287897B5BCB36C22] PRIMARY KEY CLUSTERED ([教师号])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Primary Key structure for table 课程表
-- ----------------------------
ALTER TABLE [dbo].[课程表] ADD CONSTRAINT [PK__课程表__B0C6EBD77F3E2B1F] PRIMARY KEY CLUSTERED ([课程号])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Triggers structure for table 学生表
-- ----------------------------
CREATE TRIGGER [dbo].[xsb_update]
ON [dbo].[学生表]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
CREATE TRIGGER xsb_update
ON 学生表
AFTER UPDATE
AS
PRINT '小夏提醒你：【学生表】记录已修改！'
GO
CREATE TRIGGER [dbo].[xsbcjkc_update]
ON [dbo].[学生表]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
CREATE TRIGGER xsbcjkc_update
ON 学生表
AFTER UPDATE
AS
PRINT '小夏提醒你：记录已修改！'
GO
-- ----------------------------
-- Primary Key structure for table 学生表
-- ----------------------------
ALTER TABLE [dbo].[学生表] ADD CONSTRAINT [PK__学生表__1CC396D24D2A8607] PRIMARY KEY CLUSTERED ([学号])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
