devbox
喵喵爱编程
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

【数据库设计】学籍管理的数据库系统_学籍管理系统数据库设计

作者：喵喵爱编程 | 2024-06-22 06:49:57

赞

踩

学籍管理系统数据库设计

如下代码供学习交流，获取完整代码，请关注文后二维码（coding加油站）回复“数据库学籍管理”免费获取

1、简介

可以用于数据库作业的设计，也可以用于javase作业的设计，数据库为sql server

功能说明：

  1. 简化了数据库的使用者，即没有根据用户名自动切换布局的功能，目标使用者即为管理员
  2. 提供了txt导入学生信息，成绩，课程信息的功能
  3. 提供手动录入学生信息，修改学生信息，批量修改班级及删除学生信息功能
  4. 提供按学号修改成绩及按课程号修改成绩功能
  5. 提供了班级增添，修改，删除，查询功能
  6. 提供了利用班号，学号，姓名，性别查询学生信息及查看学生成绩详情的功能
  7. 提供了按学号查询所有教过课的任课老师姓名
  8. 提供了教学计划修改，查询，统计功能
  9. 提供了自定义查询将被开除学生功能，可以同时设定选修未完成学分限制，必修未完成学分限制及学期必修未完成学分限制，并可查看所有未达标详情
  10. 提供了登陆登出功能，可以利用数据库权限控制指定用户所可拥有的功能

2、项目截图

3、项目代码：

java代码：

  1. package database;
  2.  
  3. import java.awt.Component;
  4. import java.awt.event.MouseAdapter;
  5. import java.awt.event.MouseEvent;
  6.  
  7. import javax.swing.JCheckBox;
  8. import javax.swing.JComponent;
  9. import javax.swing.JLabel;
  10. import javax.swing.JTable;
  11. import javax.swing.SwingConstants;
  12. import javax.swing.UIManager;
  13. import javax.swing.table.JTableHeader;
  14. import javax.swing.table.TableCellRenderer;
  15.  
  16. public class CheckHeaderCellRenderer implements TableCellRenderer {
  17.     CheckTableModle tableModel;
  18.     JTableHeader tableHeader;
  19.     final JCheckBox selectBox;
  20.  
  21.     public CheckHeaderCellRenderer(JTable table) {
  22.         this.tableModel = (CheckTableModle)table.getModel();
  23.         this.tableHeader = table.getTableHeader();
  24.         selectBox = new JCheckBox(tableModel.getColumnName(0));
  25.         selectBox.setSelected(false);
  26.         tableHeader.addMouseListener(new MouseAdapter() {
  27.             public void mouseClicked(MouseEvent e) {
  28.                 if (e.getClickCount() > 0) {
  29.                     //获得选中列
  30.                     int selectColumn = tableHeader.columnAtPoint(e.getPoint());
  31.                     if (selectColumn == 0) {
  32.                         boolean value = !selectBox.isSelected();
  33.                         selectBox.setSelected(value);
  34.                         tableModel.selectAllOrNull(value);
  35.                         tableHeader.repaint();
  36.                     }
  37.                 }
  38.             }
  39.         });
  40.     }
  41.     @Override
  42.     public Component getTableCellRendererComponent(JTable table, Object value,
  43.             boolean isSelected, boolean hasFocus, int row, int column) {
  44.         // TODO Auto-generated method stub
  45.         String valueStr = (String) value;
  46.         JLabel label = new JLabel(valueStr);
  47.         label.setHorizontalAlignment(SwingConstants.CENTER); // 表头标签剧中
  48.         selectBox.setHorizontalAlignment(SwingConstants.CENTER);// 表头标签剧中
  49.         selectBox.setBorderPainted(true);
  50.         JComponent component = (column == 0) ? selectBox : label;
  51.  
  52.         component.setForeground(tableHeader.getForeground());
  53.         component.setBackground(tableHeader.getBackground());
  54.         component.setFont(tableHeader.getFont());
  55.         component.setBorder(UIManager.getBorder("TableHeader.cellBorder"));
  56.  
  57.         return component;
  58.     }
  59.  
  60. }
  1. package database;
  2.  
  3. import java.awt.BorderLayout;
  4. import java.awt.EventQueue;
  5. import java.awt.event.ActionEvent;
  6. import java.awt.event.ActionListener;
  7. import java.util.Vector;
  8.  
  9. import javax.swing.JButton;
  10. import javax.swing.JCheckBox;
  11. import javax.swing.JFileChooser;
  12. import javax.swing.JFrame;
  13. import javax.swing.JLabel;
  14. import javax.swing.JOptionPane;
  15. import javax.swing.JPanel;
  16. import javax.swing.JScrollPane;
  17. import javax.swing.JTable;
  18. import javax.swing.JTextField;
  19. import javax.swing.border.EmptyBorder;
  20. import javax.swing.filechooser.FileNameExtensionFilter;
  21. import javax.swing.table.DefaultTableModel;
  22. import javax.swing.table.TableColumn;
  23.  
  24. public class Course_insert extends JFrame {
  25.  
  26.  
  27. 	private JPanel contentPane;
  28. 	private JTextField textField;
  29. 	private JTextField textField_1;
  30. 	private JCheckBox checkBox_2,checkBox_1,checkBox;
  31. 	private Vector<Integer> order=new Vector<Integer>();
  32. 	private final String[] name=new String[]{"课程号","课程名","学分","课程类型"};
  33. 	private JPanel panel;
  34. 	/**
  35. 	 * Launch the application.
  36. 	 */
  37. 	public static void main(String[] args) {
  38. 		EventQueue.invokeLater(new Runnable() {
  39. 			public void run() {
  40. 				try {
  41. 					Sql_connetcton.login_s("admin", "admin");
  42. 					Course_insert frame = new Course_insert();
  43. 					frame.setVisible(true);
  44. 				} catch (Exception e) {
  45. 					e.printStackTrace();
  46. 				}
  47. 			}
  48. 		});
  49. 	}
  50. 	
  51. 	/**
  52. 	 * Create the frame.
  53. 	 */
  54. 	public Course_insert() {
  55. 		setTitle("\u8BFE\u7A0B\u4FE1\u606F\u5BFC\u5165");
  56. 		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
  57. 		setBounds(100, 100, 450, 300);
  58. 		contentPane = new JPanel();
  59. 		this.setResizable(false);//居中
  60. 		this.setLocationRelativeTo(null);
  61. 		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
  62. 		setContentPane(contentPane);
  63. 		contentPane.setLayout(null);
  64. 		ActionListener checkBoxListener=new ActionListener() {
  65. 			
  66. 			@Override
  67. 			public void actionPerformed(ActionEvent e) {
  68. 				String s=textField_1.getText();
  69. 				if(((JCheckBox)e.getSource()).isSelected()){	
  70. 					s+=e.getActionCommand()+",";
  71. 				}else{
  72. 					s=s.replaceAll(e.getActionCommand()+",","");
  73. 				}
  74. 				textField_1.setText(s);
  75. 			}
  76. 		};
  77. 		
  78. 		JLabel lblNewLabel = new JLabel("\u5BFC\u5165\u6587\u4EF6:");
  79. 		lblNewLabel.setBounds(10, 10, 63, 15);
  80. 		contentPane.add(lblNewLabel);
  81. 		
  82. 		textField = new JTextField();
  83. 		textField.setBounds(67, 7, 216, 21);
  84. 		contentPane.add(textField);
  85. 		textField.setColumns(10);
  86. 		
  87. 		JButton button = new JButton("...");
  88. 		button.addActionListener(new ActionListener() {
  89. 			public void actionPerformed(ActionEvent e) {
  90. 				JFileChooser file=new JFileChooser();
  91. 				file.setAcceptAllFileFilterUsed(false); //关闭所有文件筛选器
  92. 				file.setMultiSelectionEnabled(false); //关闭多选
  93. 				file.setFileFilter(new FileNameExtensionFilter("TXT", "txt")); //添加txt筛选器
  94. 				if(file.showDialog(getParent(), "导入")==JFileChooser.APPROVE_OPTION){
  95. 					textField.setText(file.getSelectedFile().getPath()); //获得保存路径
  96. 				}
  97. 				
  98. 			}
  99. 		});
  100. 		button.setBounds(287, 7, 25, 21);
  101. 		contentPane.add(button);
  102. 		
  103. 		JButton button_1 = new JButton("\u5BFC\u5165");
  104.  
  105. 		button_1.setBounds(379, 6, 63, 23);
  106. 		contentPane.add(button_1);
  107. 		
  108. 		JButton button_2 = new JButton("\u9884\u89C8");
  109.  
  110. 		button_2.setBounds(315, 6, 63, 23);
  111. 		contentPane.add(button_2);
  112. 		
  113. 		JLabel label = new JLabel("\u5BFC\u5165\u683C\u5F0F:");
  114. 		label.setBounds(10, 38, 87, 15);
  115. 		contentPane.add(label);
  116. 		
  117. 		textField_1 = new JTextField();
  118. 		textField_1.setBounds(67, 35, 216, 21);
  119. 		contentPane.add(textField_1);
  120. 		textField_1.setColumns(10);
  121. 		 checkBox = new JCheckBox("\u8BFE\u7A0B\u53F7");
  122. 		checkBox.setBounds(10, 59, 63, 23);
  123. 		checkBox.addActionListener(checkBoxListener);
  124. 		checkBox.addActionListener(new ActionListener() {
  125. 			
  126. 			@Override
  127. 			public void actionPerformed(ActionEvent e) {
  128. 				if(((JCheckBox)e.getSource()).isSelected()){
  129. 					order.add(new Integer(0));
  130. 				}else{
  131. 					order.remove(new Integer(0));
  132. 				}
  133. 			}
  134. 		});
  135. 		
  136. 		 checkBox_2 = new JCheckBox("\u5B66\u5206");
  137. 		checkBox_2.setBounds(146, 59, 54, 23);
  138. 		checkBox_2.addActionListener(checkBoxListener);
  139. 		checkBox_2.addActionListener(new ActionListener() {
  140. 			
  141. 			@Override
  142. 			public void actionPerformed(ActionEvent e) {
  143. 				if(((JCheckBox)e.getSource()).isSelected()){
  144. 					order.add(new Integer(2));
  145. 				}else{
  146. 					order.remove(new Integer(2));
  147. 				}
  148. 				
  149. 			}
  150. 		});
  151. 		contentPane.add(checkBox_2);
  152. 		
  153. 		 checkBox_1 = new JCheckBox("\u8BFE\u7A0B\u540D");
  154. 		checkBox_1.setBounds(77, 59, 67, 23);
  155. 		checkBox_1.addActionListener(checkBoxListener);
  156. 		checkBox_1.addActionListener(new ActionListener() {
  157. 			
  158. 			@Override
  159. 			public void actionPerformed(ActionEvent e) {
  160. 				if(((JCheckBox)e.getSource()).isSelected()){
  161. 					order.add(new Integer(1));
  162. 				}else{
  163. 					order.remove(new Integer(1));
  164. 				}
  165. 				
  166. 			}
  167. 		});
  168. 		contentPane.add(checkBox_1);
  169. 		contentPane.add(checkBox);
  170. 		final JButton button_3 = new JButton("\u9501\u5B9A");
  171. 		button_3.addActionListener(new ActionListener() {
  172. 			public void actionPerformed(ActionEvent e) {
  173. 				String s=textField_1.getText();
  174. 				if(e.getActionCommand()=="锁定"){
  175. 					textField_1.setEnabled(false);
  176. 					checkBox_2.setEnabled(false);
  177. 					checkBox_1.setEnabled(false);
  178. 					checkBox.setEnabled(false);
  179. 					if(s.endsWith(",")){
  180. 						textField_1.setText(s.substring(0,s.length()-1));
  181. 					}
  182. 					button_3.setText("解锁");
  183. 				}else{
  184. 					textField_1.setEnabled(true);
  185. 					checkBox_2.setEnabled(true);
  186. 					checkBox_1.setEnabled(true);
  187. 					checkBox.setEnabled(true);
  188. 					textField_1.setText(s+",");
  189. 					button_3.setText("锁定");
  190. 				}
  191. 			}
  192. 		});
  193. 		button_3.setBounds(287, 34, 63, 23);
  194. 		contentPane.add(button_3);
  195. 		panel = new JPanel();
  196. 		panel.setBounds(10, 88, 424, 173);
  197. 		contentPane.add(panel);
  198. 		panel.setLayout(new BorderLayout(0, 0));//保持边距，不然会超出显示区域
  199. 		final JTable table = new JTable();
  200.  
  201. 		panel.removeAll();
  202. 		JScrollPane sPane=new JScrollPane(table);
  203. 		sPane.setAutoscrolls(true);
  204. 		panel.add(sPane,BorderLayout.CENTER);
  205. 		
  206. 		JCheckBox kind = new JCheckBox("\u8BFE\u7A0B\u7C7B\u578B");
  207. 		kind.setBounds(202, 59, 103, 23);
  208. 		contentPane.add(kind);
  209. 		kind.addActionListener(new ActionListener() {
  210. 			
  211. 			@Override
  212. 			public void actionPerformed(ActionEvent e) {
  213. 				if(((JCheckBox)e.getSource()).isSelected()){
  214. 					order.add(new Integer(3));
  215. 				}else{
  216. 					order.remove(new Integer(3));
  217. 				}
  218. 				
  219. 			}
  220. 		});
  221. 		kind.addActionListener(checkBoxListener);
  222. 		button_2.addActionListener(new ActionListener() {
  223. 			public void actionPerformed(ActionEvent e) {
  224. 				final Vector<Vector<String>> ans=
  225. 						file_insert.get(textField_1.getText(), textField.getText(), name, order);
  226. 						//file_insert.get("","C:\\Users\\jxy1\\Documents\\GitHub\\Database-project\\导入数据\\test.txt",name,order);
  227. 				final Vector<String> names=new Vector<String>();
  228. 				for(String s:name)names.add(s);
  229. 				DefaultTableModel dataModel=new DefaultTableModel(ans,names)
  230. 				{
  231. 					
  232. 					
  233.  
  234. 					
  235. 					@Override
  236. 					public boolean isCellEditable(int rowIndex, int columnIndex) {
  237. 				
  238. 						return false;
  239. 					}
  240. 					
  241. 					@Override
  242. 					public Object getValueAt(int rowIndex, int columnIndex) {
  243. 						if(columnIndex==0){
  244. 							return rowIndex+1;
  245. 						}
  246. 				        Vector rowVector = (Vector)dataVector.elementAt(rowIndex);
  247. 				        if(columnIndex==4)
  248. 				        	if(rowVector.elementAt(columnIndex-1)=="NULL")
  249. 				        		setValueAt("必修", rowIndex, columnIndex-1);
  250. 				        return rowVector.elementAt(columnIndex-1);
  251. 					}
  252. 					
  253. 					@Override
  254. 					public String getColumnName(int columnIndex) {
  255. 						if(columnIndex==0)return "序号";
  256. 						return names.get(columnIndex-1);
  257. 					}
  258. 					
  259. 					@Override
  260. 					public int getColumnCount() {
  261. 						return names.size()+1;
  262. 					}
  263.  
  264. 					
  265. 				};
  266. 				
  267. 				table.setModel(dataModel);
  268. 				for(int i=0;i<1;i++){
  269. 					TableColumn column = table.getColumnModel().getColumn(i); 
  270. 					column.setPreferredWidth(25);
  271. 				}
  272. 				
  273. 				
  274. 			}
  275. 		});
  276. 		button_1.addActionListener(new ActionListener() {
  277. 			public void actionPerformed(ActionEvent e) {
  278. 				
  279. 				DefaultTableModel  now=(DefaultTableModel) table.getModel();
  280. 				int row=now.getRowCount(),col=now.getColumnCount()-1;
  281. 				int rr=0;
  282. 				Vector s= now.getDataVector();
  283. 				for(int i=0;i<row;i++){
  284. 					Vector<String> temp=(Vector<String>) s.elementAt(rr);
  285. 					if(temp.size()>col)temp.remove(col);
  286. 					if(Sql_connetcton.insert(temp,"课程")==true){
  287. 						now.removeRow(rr);
  288. 					}else{
  289. 						rr++;
  290. 					}
  291. 				}
  292. 				
  293. 				JOptionPane.showMessageDialog(null,"成功"+(row-rr)+"个,失败"+rr+"个","导入结果", JOptionPane.INFORMATION_MESSAGE);
  294.  
  295. 			}
  296. 		});
  297. 		
  298. 	}
  299. }

数据库相关sql：

  1. USE [学籍管理系统]
  2. GO
  3.  
  4. /****** Object:  Table [dbo].[专业]    Script Date: 12/18/2021 00:42:01 ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10.  
  11. CREATE TABLE [dbo].[专业](
  12. 	[专业号] [int] NOT NULL,
  13. 	[专业名] [nchar](10) NOT NULL,
  14. 	[学院名] [nchar](10) NOT NULL,
  15. PRIMARY KEY CLUSTERED 
  16. (
  17. 	[专业号] ASC
  18. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  19. ) ON [PRIMARY]
  20.  
  21. GO
  22.  
  23. USE [学籍管理系统]
  24. GO
  25.  
  26. /****** Object:  Table [dbo].[班级]    Script Date: 12/18/2021 00:42:08 ******/
  27. SET ANSI_NULLS ON
  28. GO
  29.  
  30. SET QUOTED_IDENTIFIER ON
  31. GO
  32.  
  33. CREATE TABLE [dbo].[班级](
  34. 	[班号] [nchar](6) NOT NULL,
  35. 	[专业号] [int] NOT NULL,
  36. 	[班主任] [nchar](10) NOT NULL,
  37. 	[入学年份] [int] NOT NULL,
  38.  CONSTRAINT [PK_班级] PRIMARY KEY CLUSTERED 
  39. (
  40. 	[班号] ASC
  41. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  42. ) ON [PRIMARY]
  43.  
  44. GO
  45.  
  46. ALTER TABLE [dbo].[班级]  WITH CHECK ADD  CONSTRAINT [FK_班级_专业] FOREIGN KEY([专业号])
  47. REFERENCES [dbo].[专业] ([专业号])
  48. GO
  49.  
  50. ALTER TABLE [dbo].[班级] CHECK CONSTRAINT [FK_班级_专业]
  51. GO
  52.  
  53. ALTER TABLE [dbo].[班级]  WITH CHECK ADD  CONSTRAINT [CK_班级] CHECK  (([入学年份]>(1800) AND [入学年份]<(10000)))
  54. GO
  55.  
  56. ALTER TABLE [dbo].[班级] CHECK CONSTRAINT [CK_班级]
  57. GO
  58.  
  59. USE [学籍管理系统]
  60. GO
  61.  
  62. /****** Object:  Table [dbo].[学生]    Script Date: 12/18/2021 00:42:18 ******/
  63. SET ANSI_NULLS ON
  64. GO
  65.  
  66. SET QUOTED_IDENTIFIER ON
  67. GO
  68.  
  69. CREATE TABLE [dbo].[学生](
  70. 	[班号] [nchar](6) NULL,
  71. 	[学号] [nchar](8) NOT NULL,
  72. 	[姓名] [nchar](10) NOT NULL,
  73. 	[性别] [nchar](1) NOT NULL,
  74. 	[出生日期] [smalldatetime] NULL,
  75.  CONSTRAINT [PK_学生] PRIMARY KEY CLUSTERED 
  76. (
  77. 	[学号] ASC
  78. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  79. ) ON [PRIMARY]
  80.  
  81. GO
  82.  
  83. ALTER TABLE [dbo].[学生]  WITH CHECK ADD  CONSTRAINT [FK_学生_班级] FOREIGN KEY([班号])
  84. REFERENCES [dbo].[班级] ([班号])
  85. GO
  86.  
  87. ALTER TABLE [dbo].[学生] CHECK CONSTRAINT [FK_学生_班级]
  88. GO
  89.  
  90. ALTER TABLE [dbo].[学生]  WITH CHECK ADD  CONSTRAINT [CK_学生] CHECK  (([性别]=N'女' OR [性别]=N'男'))
  91. GO
  92.  
  93. ALTER TABLE [dbo].[学生] CHECK CONSTRAINT [CK_学生]
  94. GO
  95.  
  96. ALTER TABLE [dbo].[学生] ADD  CONSTRAINT [DF_学生_性别]  DEFAULT (N'男') FOR [性别]
  97. GO
  98.  
  99. USE [学籍管理系统]
  100. GO
  101.  
  102. /****** Object:  Table [dbo].[课程]    Script Date: 12/18/2021 00:42:25 ******/
  103. SET ANSI_NULLS ON
  104. GO
  105.  
  106. SET QUOTED_IDENTIFIER ON
  107. GO
  108.  
  109. CREATE TABLE [dbo].[课程](
  110. 	[课程号] [nchar](9) NOT NULL,
  111. 	[课程名] [nchar](25) NOT NULL,
  112. 	[学分] [int] NOT NULL,
  113. 	[课程类型] [nchar](2) NOT NULL,
  114.  CONSTRAINT [PK_课程] PRIMARY KEY CLUSTERED 
  115. (
  116. 	[课程号] ASC
  117. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  118. ) ON [PRIMARY]
  119.  
  120. GO
  121.  
  122. ALTER TABLE [dbo].[课程]  WITH CHECK ADD  CONSTRAINT [CK_课程] CHECK  (([课程类型]='任选' OR [课程类型]='限选' OR [课程类型]='必修'))
  123. GO
  124.  
  125. ALTER TABLE [dbo].[课程] CHECK CONSTRAINT [CK_课程]
  126. GO
  127.  
  128. ALTER TABLE [dbo].[课程] ADD  CONSTRAINT [DF_课程_课程类型]  DEFAULT (N'必修') FOR [课程类型]
  129. GO
  130.  
  131. USE [学籍管理系统]
  132. GO
  133.  
  134. /****** Object:  Table [dbo].[课表]    Script Date: 12/18/2021 00:42:30 ******/
  135. SET ANSI_NULLS ON
  136. GO
  137.  
  138. SET QUOTED_IDENTIFIER ON
  139. GO
  140.  
  141. CREATE TABLE [dbo].[课表](
  142. 	[课程号] [nchar](9) NOT NULL,
  143. 	[班级号] [nchar](6) NOT NULL,
  144. 	[任课教师] [nchar](10) NOT NULL,
  145.  CONSTRAINT [PK_课表] PRIMARY KEY CLUSTERED 
  146. (
  147. 	[任课教师] ASC,
  148. 	[班级号] ASC
  149. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  150. ) ON [PRIMARY]
  151.  
  152. GO
  153.  
  154. ALTER TABLE [dbo].[课表]  WITH CHECK ADD  CONSTRAINT [FK_课表_班级] FOREIGN KEY([班级号])
  155. REFERENCES [dbo].[班级] ([班号])
  156. GO
  157.  
  158. ALTER TABLE [dbo].[课表] CHECK CONSTRAINT [FK_课表_班级]
  159. GO
  160.  
  161. ALTER TABLE [dbo].[课表]  WITH CHECK ADD  CONSTRAINT [FK_课表_课程] FOREIGN KEY([课程号])
  162. REFERENCES [dbo].[课程] ([课程号])
  163. GO
  164.  
  165. ALTER TABLE [dbo].[课表] CHECK CONSTRAINT [FK_课表_课程]
  166. GO
  167.  
  168. USE [学籍管理系统]
  169. GO
  170.  
  171. /****** Object:  Table [dbo].[教学计划]    Script Date: 12/18/2021 00:42:50 ******/
  172. SET ANSI_NULLS ON
  173. GO
  174.  
  175. SET QUOTED_IDENTIFIER ON
  176. GO
  177.  
  178. CREATE TABLE [dbo].[教学计划](
  179. 	[学期] [nchar](1) NOT NULL,
  180. 	[专业号] [int] NOT NULL,
  181. 	[课程号] [nchar](9) NOT NULL,
  182.  CONSTRAINT [PK_教学计划] PRIMARY KEY CLUSTERED 
  183. (
  184. 	[专业号] ASC,
  185. 	[课程号] ASC
  186. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  187. ) ON [PRIMARY]
  188.  
  189. GO
  190.  
  191. ALTER TABLE [dbo].[教学计划]  WITH CHECK ADD  CONSTRAINT [FK_教学计划_课程] FOREIGN KEY([课程号])
  192. REFERENCES [dbo].[课程] ([课程号])
  193. GO
  194.  
  195. ALTER TABLE [dbo].[教学计划] CHECK CONSTRAINT [FK_教学计划_课程]
  196. GO
  197.  
  198. ALTER TABLE [dbo].[教学计划]  WITH CHECK ADD  CONSTRAINT [FK_教学计划_专业] FOREIGN KEY([专业号])
  199. REFERENCES [dbo].[专业] ([专业号])
  200. GO
  201.  
  202. ALTER TABLE [dbo].[教学计划] CHECK CONSTRAINT [FK_教学计划_专业]
  203. GO
  204.  
  205. ALTER TABLE [dbo].[教学计划]  WITH CHECK ADD  CONSTRAINT [CK_教学计划] CHECK  (([学期]>(0) AND [学期]<(9)))
  206. GO
  207.  
  208. ALTER TABLE [dbo].[教学计划] CHECK CONSTRAINT [CK_教学计划]
  209. GO
  210.  
  211. USE [学籍管理系统]
  212. GO
  213.  
  214. /****** Object:  Table [dbo].[成绩]    Script Date: 12/18/2021 00:42:53 ******/
  215. SET ANSI_NULLS ON
  216. GO
  217.  
  218. SET QUOTED_IDENTIFIER ON
  219. GO
  220.  
  221. CREATE TABLE [dbo].[成绩](
  222. 	[学号] [nchar](8) NOT NULL,
  223. 	[课程号] [nchar](9) NOT NULL,
  224. 	[成绩] [int] NULL,
  225. 	[补考成绩] [int] NULL,
  226. 	[真实成绩] [int] NULL,
  227.  CONSTRAINT [PK_成绩] PRIMARY KEY CLUSTERED 
  228. (
  229. 	[学号] ASC,
  230. 	[课程号] ASC
  231. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  232. ) ON [PRIMARY]
  233.  
  234. GO
  235.  
  236. ALTER TABLE [dbo].[成绩]  WITH CHECK ADD  CONSTRAINT [FK_成绩_课程] FOREIGN KEY([课程号])
  237. REFERENCES [dbo].[课程] ([课程号])
  238. GO
  239.  
  240. ALTER TABLE [dbo].[成绩] CHECK CONSTRAINT [FK_成绩_课程]
  241. GO
  242.  
  243. ALTER TABLE [dbo].[成绩]  WITH CHECK ADD  CONSTRAINT [FK_成绩_学生] FOREIGN KEY([学号])
  244. REFERENCES [dbo].[学生] ([学号])
  245. GO
  246.  
  247. ALTER TABLE [dbo].[成绩] CHECK CONSTRAINT [FK_成绩_学生]
  248. GO
  249.  
  250. ALTER TABLE [dbo].[成绩]  WITH CHECK ADD  CONSTRAINT [CK_成绩] CHECK  (([成绩]>=(0) AND [成绩]<=(100)))
  251. GO
  252.  
  253. ALTER TABLE [dbo].[成绩] CHECK CONSTRAINT [CK_成绩]
  254. GO
  255.  
  256. ALTER TABLE [dbo].[成绩]  WITH CHECK ADD  CONSTRAINT [CK_成绩_t] CHECK  (([补考成绩] IS NULL OR [成绩] IS NOT NULL AND [补考成绩]>=(0) AND [补考成绩]<=(100) AND [成绩]<(60)))
  257. GO
  258.  
  259. ALTER TABLE [dbo].[成绩] CHECK CONSTRAINT [CK_成绩_t]
  260. GO
  261.

如下代码供学习交流，获取完整代码，请关注文后二维码（coding加油站）回复“数据库学籍管理”免费获取

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/喵喵爱编程/article/detail/745725
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号