精华内容
下载资源
问答
  • 数据库大作业学生信息管理系统
    2021-05-18 13:54:05

    《数据库大作业——学生宿舍管理系统》由会员分享,可在线阅读,更多相关《数据库大作业——学生宿舍管理系统(9页珍藏版)》请在人人文库网上搜索。

    1、学生宿舍管理系统信管2班 李维1、 需求分析阶段我们宿舍在管理上都是采用纸上登记,文本记录的,针对此现状,我提出了学生宿舍管理系统的设计。(1)调查分析现在要开发学生宿舍管理系统,首先要调查用户的需求。本次调查采取的主要步骤有:(1) 开调查会。通过与老师,学生座谈了解他们对该管理系统的期望与要求。(2) 请专人介绍。找专业老师了解宿舍的具体安排情况以及学生的入住信息。(3) 设计调查表请学生填写。调查学生的个人信息,宿舍的基本信息,工作人员的基本信息。首先调查基本信息:学生住在宿舍楼中,每站宿舍区都会有若干名阿姨负责本宿舍区的日常管理。(1)学生的基本信息:入校时,每位同学都有唯一的学号,并。

    2、被分配到指定的宿舍楼和指定的宿舍,也会有一个宿舍号,其入校时间就是他的入住时间。(2)宿舍的基本信息:每间宿舍都有唯一的楼号和宿舍号,每站宿舍区都有自己的电话号码。(3)工作人员基本信息:每站宿舍区都有驻楼阿姨和保洁阿姨的编号。(4)报修的基本信息:宿舍楼中经常出现财产的损坏,比如灯泡坏了,厕所的马桶出故障了等,这时,同学们需要将财产损坏情况报告给宿舍楼管理员,以便学校派人进行维修。这时,需要记录报修的宿舍号和损坏的财产编号,同时记录报修的时间和损坏的原因。当损坏的财产维修完毕后,应记录解决时间,表示该报修成功解决。(5)来访者基本信息每站宿舍对于每一次的人员来访都要做好相应的登记。包括来访者。

    3、和被访者的信息。(2)用户对系统的要求1、信息要求:宿舍楼管理员能查询上面提到的宿舍楼的所有相关信息,包括某一学号的学生在宿舍楼中住宿的详细信息,报修的所有信息和来访者的信息,以利于对整个宿舍楼的全面管理。2、处理要求:当学生基本信息发生变化时,宿舍楼管理员能对其进行修改。比如,某些同学搬到其他的宿舍中去,他们在本宿舍楼中相应的记录就应该删去;当宿舍财产报修及时解决后,管理员应登记解决时间,表明该报修问题已成功解决。3、安全性与完整性要求:A、安全性要求:(1)系统应设置访问用户的标识以鉴别是否是合法用户,并要求合法用户设置其密码,保证用户身份不被盗用;(2)系统应对不同的数据设置不同的访问级。

    4、别,限制访问用户可查询和处理数据的类别和内容;(3)系统应对不同用户设置不同的权限,区分不同的用户,如区分普通用户(学生),管理员。B、完整性要求:(1)各种信息记录的完整性,信息记录内容不能为空;(2)各种数据间相互的联系的正确性;(3)相同的数据在不同记录中的一致性。数据库功能:基本信息管理、住宿管理、服务管理、来访者管理。基本信息管理包括学生信息、宿舍信息、工作人员信息;住宿信息包括入住、分配床位、毕业退宿;服务管理包括电费、维修;来访者管理包括来访者信息登记、来访者查询。(3) 数据字典设计(数据项,数据结构,数据量,数据存储,处理过程)1、 数据项数据项名含义说明别名数据类型长度取值。

    5、范围学号住宿学生学生编号char8姓名住宿学生学生姓名Varchar10性别住宿学生char2专业住宿学生Varchar20联系方式住宿学生char11员工号宿舍阿姨员工编号char4员工姓名宿舍阿姨Varchar10年龄宿舍阿姨smallint2职位宿舍阿姨员工从事工作Varchar20联系方式宿舍阿姨手机号码char11区名宿舍所属园区澄,泽,沁,润Varchar2站名园区几站一站,二站char2楼号宿舍是几栋char2宿舍号宿舍几零几char3宿舍电话宿舍每站号码char12可住人数宿舍smallint2已住人数宿舍smallint2物品号物品报修varchar8报修原因物品报修char。

    6、50提交日期物品报修datetime 8解决日期物品报修datetime8报修费用物品报修decimal5来访人姓名来访者Varchar10被访人姓名来访者Varchar10所属关系来访者Varchar20证件名称来访者Varchar20来访日期来访者Datetime8来访时间来访者Datetime8归访时间来访者Datetime8备注来访者Varchar502、 数据结构数据结构名含义说明组成学生住宿信息学生登记入住信息学号,姓名,性别,专业,联系方式,入住时间员工信息员工就职信息员工号,姓名,年龄,性别,职位,家庭地址,联系方式宿舍信息学校宿舍园区分布情况以及入住情况区名,站名,楼号,宿舍。

    7、号,宿舍电话,可住人数,已住人数报修信息设备保修楼号,宿舍号,物品号,报修原因,提交日期,解决日期,报修费用来访者信息每站来访人员登记来访人姓名,被访人姓名,所属关系,证件名称,来访日期,来访时间,归访时间,备注3、 数据流以来访人员为例:数据流:来访人员说明:对每日的来访人员进行登记查询数据流来源:来访者数据流去向:来访者信息表组成:来访信息平均流量:20人/天高峰期流量:50人/天4、 数据存储数据存储名说明输入的数据流输出的数据流组成存取频度存取方式报修信息表设备保修信息存储表报修信息已修信息报修信息报修信息已修信息5件/h随机来访者信息表来访者信息存储表来访信息来访结束信息来访信息来访。

    8、信息来访结束信息10/天随机5、 处理过程处理过程名输入数据流输出数据流登记报修报修信息报修信息查询报修报修信息报修信息登记已修信息已修信息已修信息登记来访者信息来访者信息来访者信息查询来访者信息来访者信息来访者信息登记归访信息归访信息归访信息2、 概念结构设计(1) 分E-R图学生联系方式专业学号姓名已住人数宿舍区名可住人数楼号站名宿舍电话员工年龄员工号职位联系方式员工姓名来访者性别证件关系姓名(2)总E-R图学号区名入住时间站名楼号姓名1n宿舍电话宿舍入住学生专业可住人数离宿时间11联系方式已住人数来访时间管理来访归访时间nn年龄员工来访者员工号职位性别证件联系方式员工姓名关系姓名3、 逻。

    9、辑结构设计(1)转换原则: 一个实体型转换为一个关系模式。 关系的属性:实体型的属性 关系的码:实体型的码 2. 一个1:n联系可以转换为一个独立的关系模式,也可以与n端对应的关系 模式合并。 1) 转换为一个独立的关系模式 关系的属性:与该联系相连的各实体的码以及联系本身的属性 关系的码:n端实体的码 2)与n端对应的关系模式合并合并后关系的属性:在n端关系中加入1端关系的码和联系本身的属性 合并后关系的码:不变 可以减少系统中的关系个数,一般情况下更倾向于采用这种方法。 3一个1:1联系可以转换为一个独立的关系模式,也可以与任意一端对应的关系模式合并。 1) 转换为一个独立的关系模式 关系。

    10、的属性:与该联系相连的各实体的码以及联系本身的属性 关系的候选码:每个实体的码均是该关系的候选码 2) 与某一端对应的关系模式合并 合并后关系的属性:加入对应关系的码和联系本身的属性 合并后关系的码:不变 4一个m:n联系转换为一个关系模式。 关系的属性:与该联系相连的各实体的码以及联系本身的属性 关系的码:各实体码的组合。 5三个或三个以上实体间的一个多元联系转换为一个关系模式。 关系的属性:与该多元联系相连的各实体的码以及联系本身的属性 关系的码:各实体码的组合 6. 具有相同码的关系模式可合并。 目的:减少系统中的关系个数。 合并方法:将其中一个关系模式的全部属性加入到另一个关系模式中,。

    11、然后去掉其中的同义属性(可能同名也可能不同名),并适当调整属性的次序(2) 关系模式(1)学生住宿表(student accommodation(SA):(学号xh,姓名xm,性别xb,专业zy,入住时间rzsj,联系方式lxfs,楼号lh,宿舍号ssh)(2)宿舍表(Dormitory):(区名qm,站名zm,楼号lh,宿舍号ssh,电话号码dhhm,可住人数kzrs,已住人数yzrs)(3)设备报修表(equipment maintenance(EM):(区名qm,站名zm,楼号lh,宿舍号ssh,物品号wph,报修原因bxyy,提交日期tjrq,解决日期jjrq, 报修费用bxfy)(4。

    12、)来访者表Victor:(来访人姓名lfrxm,被访人姓名bfrxm,所属关系gx,证件名称zj,来访日期lfrq,来访时间lfsj,归访时间gfsj,学号xh)(5)员工信息表Staff:(员工号ygh,员工姓名ygxm,年龄nl,楼号lh,性别xb,职位zw,联系方式lxfs)4、 物理结构设计数据库在物理设备上的存储结构与存储方法称为数据库的物理结构,为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程就是数据库的物理设计分两步走:(1)确定数据库的物理结构,在关系数据库中主要是存取方法和存储结构;(2)对物理结构进行评价,评价的重点是时间和空间效率。对于不同的表要建立不同的索。

    13、引,如学生住宿表建立唯一索引,宿舍表建立组合索引5、 实施和维护Create table SA(xh char(8) primary key,xm varchar(50) ,xb char(2),zy varchar(50), lxfs char(11),rzsj datetime ,lh char(2) ,ssh char(3), );Create table Dormitory(qm varchar(50),Zm int,Lh varchar(50),Ssh int,Dhhm int,Kzrs int,Yzrs int,);Create table EM(lh char(6) ,ssh c。

    14、har(6) ,wph char(8) ,bxyy char(50) ,tjrq datetime ,jjrq datetime ,bxfy decimal(5,2) ,);Create table Victor(lfzxm varchar(10) ,bfzxm varchar(10) ,gx Varchar(20) ,zj Varchar(20) ,lfrq Datetime ,lfsj Datetime ,gfsj Datetime ,bz Varchar(50),xh char(8) );Create table staff(ygh char(4) primary key,ygxm va。

    15、rchar(10) not null,nl smallint ),lh char(2),xm char(2) ,zw varchar(20) ,lxfs char(11),);导入数据Insert into SA(xh,xm,xb,zy,rzsj,lxfs,lh,ssh)Values()Insert into Dormitory(qm,zm,lh,ssh,dhhm,kzrs,yzrs)Values()Insert into EM(qm,zm,lh,ssh,wph,bxyy,tjrq,jjrq, bxfy)Values()Insert into Victor(lfrxm,bfrxm,gx,zj,lfrq,lfsj,gfsj,xh)Values()更新删除Update SA set xm=m+yghDelete from Staff where nl=50 简单查询Select * from SA;Select xh,xm,zy from SA where lh=5 and ssh=601;Select ygh from staff;Select ssh from Dormitory where qm=澄园。

    更多相关内容
  • 数据库大作业学籍管理系统代码和报告,数据库大作业学生信息管理系统,Java源码.zip
  • 此为大学数据库大作业的报告以及源代码及打包程序。数据库使用MySQL设计,管理界面采用java编写,附带完整报告。
  • 免费的,开源的,C#数据库大作业学生信息管理系统),可能还有未知bug还请各位指正,谢谢,如果可以关注一波就更好了
  • 数据库大作业学籍管理系统 C++ builder 数据库大作业学籍管理系统 C++ builder 数据库大作业学籍管理系统 C++ builder
  • 通过调查本地的学校,根据学校的具体情况设计学生选课信息管理系统。主要功能有: 1.学生管理:学生信息查询、插入、删除、修改等 2.课程信息:课程的编号、课程名、开课单位。 3.教师信息:教师信息的查询、插入、...
  • 基于Android的简单学生信息管理系统,使用了SQLite数据库, 可以简单用于学生实验与课程设计的使用。
  • 数据库大作业-学生信息管理系统

    万次阅读 多人点赞 2020-06-07 09:29:47
    Visual Studio 语言:C#,SQL 两个身份,管理员和学生管理员功能:管理学生专业信息、课程信息、选课信息(增删改查),查看已注册过的同学信息(密码不可见,是乱码)以及照片。 学生功能:注册自己的信息,查看...

    软件:SQL Server;Visual Studio
    语言:C#,SQL
    两个身份,管理员和学生。
    管理员功能:管理学生专业信息、课程信息、选课信息(增删改查),查看已注册过的同学信息(密码不可见,是乱码)以及照片。
    学生功能:注册自己的信息,查看自己的信息包括专业信息、注册时的信息、选课及成绩,修改自己的密码。
    在这里插入图片描述

    在SQL Server创建数据库,在新数据库中新建需要用的表并添加数据。

    create database curricula_variable_system;//创建数据库
    
    USE curricula_variable_system;
    //建表,记录注册信息的
    CREATE TABLE SysUser          
     ( 
     UserID NCHAR(20) ,                          
     UserPassWord NCHAR(32) ,    /*密码32位加密*/ 
     UserSchoolID NCHAR(20) PRIMARY KEY,
     UserMobile NCHAR(11),
     UserBirthday datetime,
     UserIdentity NCHAR(20),
     UserPhoto image
     ); 
     //建表,记录登录信息的
     CREATE TABLE SysLog          
     ( 
     UserID NCHAR(20) ,                          
     DateAndTime datetime,
     UserOperation NCHAR(200)
     ); 
     //建管理员表,存管理员的账号密码
     CREATE TABLE Teacher          
     ( 
     UserID NCHAR(20) ,                          
     UserPassWord NCHAR(32) ,    /*密码32位加密*/ 
     );
     //建学生表
     CREATE TABLE Student          
     ( 
     Sno CHAR(9) PRIMARY KEY,        /* 列级完整性约束条件,Sno是主码*/                  
     Sname CHAR(20) UNIQUE,             /* Sname取唯一值*/
     Ssex CHAR(2),
     Sage SMALLINT,
     Sdept CHAR(20)
     ); 
    //课程表
    CREATE TABLE  Course
     ( 
     Cno CHAR(4) PRIMARY KEY,
     Cname CHAR(40),            
     Cpno CHAR(4),                                      
     Ccredit SMALLINT,
     FOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
     ); 
     //选课表
     CREATE TABLE  SC
     (
     Sno CHAR(9), 
     Cno CHAR(4),  
     Grade SMALLINT,
     PRIMARY KEY (Sno,Cno),                     /* 主码由两个属性构成,必须作为表级完整性进行定义*/
     FOREIGN KEY (Sno) REFERENCES Student(Sno),  /* 表级完整性约束条件,Sno是外码,被参照表是Student */
     FOREIGN KEY (Cno)REFERENCES Course(Cno)     /* 表级完整性约束条件, Cno是外码,被参照表是Course*/
     ); 
      //插入数据
    INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
    INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
    INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
    INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
    INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);
    
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('1','数据库',NULL,4);
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('2','数学',NULL,4);
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('3','信息系统',NULL,4);
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('4','操作系统',NULL,4);
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('5','数据结构',NULL,4);
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('6','数据处理',NULL,4);
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('7','Pascal语言',NULL,4);
    UPDATE Course SET Cpno = '5' WHERE Cno = '1' 
    UPDATE Course SET Cpno = '1' WHERE Cno = '3' 
    UPDATE Course SET Cpno = '6' WHERE Cno = '4' 
    UPDATE Course SET Cpno = '7' WHERE Cno = '5' 
    UPDATE Course SET Cpno = '6' WHERE Cno = '7' 
    
    INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
    INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
    INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
    INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
    INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);
    
    //新建触发器
    CREATE TRIGGER regist_recorder
    ON SysUser            
    AFTER
    INSERT
    AS 
     declare @UserName    nchar(20)
     declare @DateTime    datetime
     declare @UserOperation nchar(200)
     select @UserName = system_user
     select @DateTime = CONVERT(datetime,GETDATE(),120) 
     declare @op varchar(10)
     select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
                       then 'Update'
                       when exists(select 1 from inserted) and not exists(select 1 from deleted)
                       then 'Insert'
                       when not exists(select 1 from inserted) and exists(select 1 from deleted)
                       then 'Delete' end               
     select @UserOperation = @op 
     INSERT INTO SysLog(UserID,DateAndTime,UserOperation)
     VALUES (@UserName,@DateTime,@UserOperation)

    刚开始的登录页面
    在这里插入图片描述
    点击按钮显示新的窗体,这是其中一个按钮的代码。

    Form2 form2 = new Form2();//新建窗体
    form2.Show();//显示新建窗体
    this.Hide();//隐藏当前窗体

    在这里插入图片描述
    确定登录

    string username = textBoxtea.Text.Trim();  //取出账号
                string password = EncryptWithMD5(textBoxcher.Text.Trim());  //取出密码并加密
                string myConnString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//连接数据库
                SqlConnection sqlConnection = new SqlConnection(myConnString);  //实例化连接对象
                sqlConnection.Open();
                string sql = "select UserID,UserPassWord from Teacher where UserID = '" + username + "' and UserPassWord = '" + password + "'"; 
                //教工号:201210,密码:123
                //编写SQL命令
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader.HasRows && textBoxyan.Text == code)
                {
                    MessageBox.Show("欢迎使用!");             //登录成功
                    Form6 form6 = new Form6();
                    form6.Show();
                    this.Hide();
                }
                else
                {
                    MessageBox.Show("登录失败!");
                    return;
                }
                sqlDataReader.Close();           
                sqlConnection.Close();

    密码加密

    public static string EncryptWithMD5(string source)
            {
                byte[] sor = Encoding.UTF8.GetBytes(source);
                MD5 md5 = MD5.Create();
                byte[] result = md5.ComputeHash(sor);
                StringBuilder strbul = new StringBuilder(40);
                for (int i = 0; i < result.Length; i++)
                {
                    strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
                }
                return strbul.ToString();
            }

    验证码
    点击窗体|在事件里找Load|双击,然后输入以下代码

     public string code;
    //随机实例化 
                Random ran = new Random();
                int number;
                char code1;
                //取五个数 
                for (int i = 0; i < 5; i++)
                {
                    number = ran.Next();
                    if (number % 2 == 0)
                        code1 = (char)('0' + (char)(number % 10));
                    else
                        code1 = (char)('A' + (char)(number % 26)); //转化为字符 
                    this.code += code1.ToString();
                }
                label5.Text = code;

    在这里插入图片描述
    在这里插入图片描述
    查看照片,根据学号查看

    	    try
                {
                    string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//数据库连接字符串
                    SqlConnection connection = new SqlConnection(connString);//创建connection对象
                    //打开数据库连接
                    connection.Open();
                    //创建SQL语句
                    string sql = "select UserPhoto from SysUser where UserSchoolID = '" + textBox1.Text + "'";
                    //创建SqlCommand对象
                    SqlCommand command = new SqlCommand(sql, connection);
                    //创建DataAdapter对象
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                    //创建DataSet对象
                    DataSet dataSet = new DataSet();
                    dataAdapter.Fill(dataSet, "SysUser");
                    int c = dataSet.Tables["SysUser"].Rows.Count;
                    if (c > 0)
                    {
                        Byte[] mybyte = new byte[0];
                        mybyte = (Byte[])(dataSet.Tables["SysUser"].Rows[c - 1]["UserPhoto"]);
                        MemoryStream ms = new MemoryStream(mybyte);
                        pictureBox2.Image = Image.FromStream(ms);
                    }
                    else
                    {
                        pictureBox2.Image = null;
                        MessageBox.Show("无照片");
                    }                  
                    connection.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

    返回上一界面

                Form6 form6 = new Form6();//上一界面的窗体
                form6.Show();//显示
                this.Hide();//隐藏当前窗体

    在这里插入图片描述
    在这里插入图片描述
    我对性别的填写进行了限定只能是”男“或“女”。学号也限定是201215开头再加三位数字。
    SQL语句如下

    alter table Student add constraint c1 check(Sno between 201215000 and 201215999)
    alter table Student add constraint c2 check(Ssex IN('男','女'))

    删除

                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
                try
                {
                    con.Open();
                    string select_id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值,也就是ID
                    string delete_by_id = "delete from Student where Sno=" + select_id;//sql删除语句
                    SqlCommand cmd = new SqlCommand(delete_by_id, con);
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("请正确选择行!");
                }
                finally
                {
                    con.Dispose();
                }
                this.studentTableAdapter.Fill(this.curricula_variable_systemDataSet3.Student);//Form10_Load里的那条代码

    修改,根据学号修改姓名

                String StuID = textBox1.Text.Trim();
                String StuName = textBox2.Text.Trim();
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
                try
                {
                    con.Open();
                    string insertStr = "UPDATE Student SET Sname = '" + StuName + "' WHERE Sno = '" + StuID + "'";
                    SqlCommand cmd = new SqlCommand(insertStr, con);
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("输入数据违反要求!");
                }
                finally
                {
                    con.Dispose();
                }
                this.studentTableAdapter.Fill(this.curricula_variable_systemDataSet3.Student);//Form10_Load里的那条代码

    查询,根据学号

                String StuID = textBox1.Text.Trim();
                String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
                SqlConnection sqlConnection = new SqlConnection(conn);  //实例化连接对象
                try
                {
                    sqlConnection.Open();
                    String select_by_id = "select * from Student where Sno='" + StuID + "'";
                    SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    BindingSource bindingSource = new BindingSource();
                    bindingSource.DataSource = sqlDataReader;
                    dataGridView1.DataSource = bindingSource;
                }
                catch
                {
                    MessageBox.Show("查询语句有误,请认真检查SQL语句!");
                }
                finally
                {
                    sqlConnection.Close();
                }

    清空文本行

                textBox1.Text = null;
                textBox2.Text = null;
                textBox3.Text = null;
                textBox4.Text = null;
                textBox5.Text = null;

    在这里插入图片描述
    添加课程

                string Coucno = textBox1.Text.Trim();
                string Couname = textBox2.Text.Trim();
                string Coucredit = textBox3.Text.Trim();
                string Coupno = textBox4.Text.Trim();            
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
                    try
                    {
                        con.Open();//打开
                        string insertStr = "INSERT INTO  Course (Cno,Cname,Cpno,Ccredit)    " +
                            "VALUES ('" + Coucno + "','" + Couname + "','" + Coupno + "','" + Coucredit + "')";
                        SqlCommand cmd = new SqlCommand(insertStr, con);//使用
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        MessageBox.Show("输入数据违反要求!");
                    }
                    finally
                    {
                        con.Dispose();//释放
                    }     
                this.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);

    删除

                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
                try
                {
                    con.Open();
                    string select_id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值,也就是Cno那列
                    string delete_by_id = "delete from Course where Cno=" + select_id;//sql删除语句
                    SqlCommand cmd = new SqlCommand(delete_by_id, con);
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("请正确选择行!");
                }
                finally
                {
                    con.Dispose();
                }
                this.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);

    修改,根据课程号修改课程名

                string Cno = textBox1.Text.Trim();
                string Cname = textBox2.Text.Trim();
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
                try
                {
                    con.Open();
                    string insertStr = "UPDATE Course SET Cname = '" + Cname + "' WHERE Cno = '" + Cno + "'";
                    SqlCommand cmd = new SqlCommand(insertStr, con);
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("输入数据违反要求!");
                }
                finally
                {
                    con.Dispose();
                }
                this.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);

    查询,根据课程号

                string Cno = textBox1.Text.Trim();
                String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
                SqlConnection sqlConnection = new SqlConnection(conn);  //实例化连接对象
                try
                {
                    sqlConnection.Open();
                    String select_by_id = "select * from Course where Cno='" + Cno + "'";
                    SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    BindingSource bindingSource = new BindingSource();
                    bindingSource.DataSource = sqlDataReader;
                    dataGridView1.DataSource = bindingSource;
                }
                catch
                {
                    MessageBox.Show("查询语句有误,请认真检查SQL语句!");
                }
                finally
                {
                    sqlConnection.Close();
                }

    在这里插入图片描述
    查询

                string StuID = textBox1.Text.Trim();
                String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
                SqlConnection sqlConnection = new SqlConnection(conn);  //实例化连接对象
                try
                {
                    sqlConnection.Open();
                    String select_by_id = "select * from SC where Sno='" + StuID + "'";
                    SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    BindingSource bindingSource = new BindingSource();
                    bindingSource.DataSource = sqlDataReader;
                    dataGridView1.DataSource = bindingSource;
                }
                catch
                {
                    MessageBox.Show("查询语句有误,请认真检查SQL语句!");
                }
                finally
                {
                    sqlConnection.Close();
                }

    修改,根据学号、课程号修改成绩

                string StuID = textBox1.Text.Trim();
                string Cno = textBox2.Text.Trim();
                string Grade = textBox3.Text.Trim();
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
                try
                {
                    con.Open();
                    string insertStr = "UPDATE SC SET Grade = '" + Grade + "' WHERE Cno = '" + Cno + "'AND Sno='"+ StuID+"'";
                    SqlCommand cmd = new SqlCommand(insertStr, con);
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("输入数据违反要求!");
                }
                finally
                {
                    con.Dispose();
                }
                this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);

    添加

                string StuID = textBox1.Text.Trim();
                string Cno = textBox2.Text.Trim();
                string Grade = textBox3.Text.Trim();
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
                try
                {
                    string stu = "select Sno from Student where Sno='" + StuID + "'";
                    if (stu != "")
                    {
                        con.Open();//打开
                        string insertStr = "INSERT INTO  SC (Sno,Cno,Grade)    " +
                            "VALUES ('" + StuID + "','" + Cno + "','" + Grade + "')";
                        SqlCommand cmd = new SqlCommand(insertStr, con);//使用
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        MessageBox.Show("没有该学生!请重新输入");
                    }            
                }
                catch
                {
                    MessageBox.Show("输入数据违反要求!");//新加的学号在已有学号中,课程号在已有的课程中,成绩在0到100之间
                }
                finally
                {
                    con.Dispose();//释放
                }
                this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);

    删除,输入学号、课程号删除对应行。因为选课表是学号课程号一起作为主码的所以和前边的删除方式不同。

                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
                try
                {
                    con.Open();
                    string StuID = textBox1.Text.Trim();
                    string Cno = textBox2.Text.Trim();
                    string delete_by_stc= "delete from SC where Sno='"+ textBox1.Text + "' and Cno='" + textBox2.Text + "'";
                    SqlCommand cmd = new SqlCommand(delete_by_stc, con);
                    cmd.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("请正确选择行!");
                }
                finally
                {
                    con.Dispose();
                }
                this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);

    在这里插入图片描述
    查看专业信息(查看专业信息代码类似,就是SQL语句那改成SC表)

                String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
                SqlConnection sqlConnection = new SqlConnection(conn);  //实例化连接对象
                String StuID = textBox1.Text.Trim();
                if(textBox1.Text=="")
                {
                    MessageBox.Show("请先输入学号!");
                    return;
                }
                else
                {
                    try
                    {
                        sqlConnection.Open();
                        String select_by_id = "select * from Student where Sno='" + StuID + "'";
                        SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);
                        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                        BindingSource bindingSource = new BindingSource();
                        bindingSource.DataSource = sqlDataReader;
                        dataGridView1.DataSource = bindingSource;
                    }
                    catch
                    {
                        MessageBox.Show("查询语句有误,请认真检查SQL语句!");
                    }
                    finally
                    {
                        sqlConnection.Close();
                    }
                }

    查看个人信息

                String StuID = textBox1.Text.Trim();
                String conn1 = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
                SqlConnection sqlConnection1 = new SqlConnection(conn1);  //实例化连接对象
                if (textBox1.Text == "")
                {
                    MessageBox.Show("请先输入学号!");
                    return;
                }
                else
                {            
                    try
                    {
                        sqlConnection1.Open();
                        String select_by_id1 = "select UserID,UserSchoolID,UserMobile,UserBirthday,UserIdentity from SysUser where UserSchoolID='" + StuID + "'";
                        SqlCommand sqlCommand = new SqlCommand(select_by_id1, sqlConnection1);
                        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                        BindingSource bindingSource = new BindingSource();
                        bindingSource.DataSource = sqlDataReader;
                        dataGridView1.DataSource = bindingSource;
                    }
                    catch
                    {
                        MessageBox.Show("查询语句有误,请认真检查SQL语句!");
                    }
                    finally
                    {
                        sqlConnection1.Close();
                    }
                }

    查看照片

                if (textBox1.Text == "")
                {
                    MessageBox.Show("请先输入学号!");
                    return;
                }
                else
                {
                    try
                    {
                        string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//数据库连接字符串
                        SqlConnection connection = new SqlConnection(connString);//创建connection对象
                        //打开数据库连接
                        connection.Open();
                        //创建SQL语句
                        string sql = "select UserPhoto from SysUser where UserSchoolID = '" + textBox1.Text + "'";
                        //创建SqlCommand对象
                        SqlCommand command = new SqlCommand(sql, connection);
                        //创建DataAdapter对象
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                        //创建DataSet对象
                        DataSet dataSet = new DataSet();
                        dataAdapter.Fill(dataSet, "SysUser");
                        int c = dataSet.Tables["SysUser"].Rows.Count;
                        if (c > 0)
                        {
                            Byte[] mybyte = new byte[0];
                            mybyte = (Byte[])(dataSet.Tables["SysUser"].Rows[c - 1]["UserPhoto"]);
                            MemoryStream ms = new MemoryStream(mybyte);
                            pictureBox2.Image = Image.FromStream(ms);
                        }
                        else
                            pictureBox2.Image = null;
                        connection.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }

    修改密码
    在这里插入图片描述
    确定修改,使用正则表达式约束新密码的格式

                if (textBox3.Text == "")
                {
                    MessageBox.Show("学号不能为空!");
                }
                if(textBox1.Text=="")
                {
                    MessageBox.Show("新密码不能为空!");
                }
                if (textBox2.Text == "")
                {
                    MessageBox.Show("确认密码不能为空!");
                }
                if(textBox1.Text.Trim()!="")//新密码不为空时,输入满足正则表达式
                {
                    //使用regex(正则表达式)进行格式设置 至少有数字、大写字母、小写字母各一个。最少3个字符、最长20个字符。
                    Regex regex = new Regex(@"(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z]).{3,20}");
                    if (regex.IsMatch(textBox1.Text))//判断格式是否符合要求
                    {
                        //MessageBox.Show("输入密码格式正确!");
                    }
                    else
                    {
                        MessageBox.Show("至少有数字、大写字母、小写字母各一个。最少3个字符、最长20个字符!");
                        return;
                    }
                }
                if (textBox1.Text == textBox2.Text)
                {
                    string sql = "update SysUser set UserPassWord='"+ EncryptWithMD5(textBox1.Text)+"' where UserSchoolID='"+ textBox3.Text.Trim()+"'";
                    string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";
                    SqlConnection con = new SqlConnection(connString);//创建connection对象
                    con.Open();
                    SqlCommand command = new SqlCommand(sql, con);
                    command.ExecuteNonQuery();
                    MessageBox.Show("新密码已经修改完成");                
                    con.Close();
                }
                else
                {
                    MessageBox.Show("请输入两次相同的密码");
                }

    对密码加密

            public static string EncryptWithMD5(string source)
            {
                byte[] sor = Encoding.UTF8.GetBytes(source);
                MD5 md5 = MD5.Create();
                byte[] result = md5.ComputeHash(sor);
                StringBuilder strbul = new StringBuilder(40);
                for (int i = 0; i < result.Length; i++)
                {
                    strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
                }
                return strbul.ToString();
            }

    主要代码都在上面了,参考的时候结合自己的稍加改动就可以,背景图片是pictureBox组件然后选自己喜欢的照片就可以了,注意一下大小模式这里,选这个图片才显示完整。在这里插入图片描述
    所有代码我压缩放在github上了,需要的可以下载然后在Visual studio打开看一下点这里
    在这里插入图片描述
    视频讲解:点这里

    展开全文
  • 数据库大作业 :人事管理系统 报告中包括:数据库的背景及功能需求、数据库的概念结构设计、数据库的逻辑结构设计、关键问题论述、自我总结 部分展示: 一、数据库的背景及功能需求 人事管理系统,属于ERP的一个部分...
  • 西电MySQL数据库大作业——学生管理系统GUI 原程序链接: https://www.bbsmax.com/A/kmzL3WQBdG/ 为了完成数据库大作业, 我在其基础上进行了一定的修改和扩充. 如果要使用的话, 请修改__init__中的数据库登录信息,把...

    MySQL数据库大作业——学生管理系统GUI

    原程序链接: https://www.bbsmax.com/A/kmzL3WQBdG/

    为了完成数据库大作业, 我在其基础上进行了一定的修改和扩充.

    如果要使用的话, 请修改__init__中的数据库登录信息,把其中的*****修改成自己的信息即可。

    主界面会自动分辨学生与教工账号,直接登录即可。

    图片演示

    我其实是有实验报告的,但是不好上传,而且重要的是写的确实不咋地。
    就在这里贴几张图片展示一下吧。

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    使用程序之前

    数据库中表的建立:

    # student
    create table student(
    sno char(8) primary key,
    sname char(8) not null,
    sex char(2),
    birthday date,
    tel varchar(20))
    
    # student_course
    create table student_course(
    sno char(8) not null,
    tcid smallint not null,
    score tinyint)
    
    # course
    create table course(
    cno char(10) primary key,
    cname char(20) not null,
    credit tinyint not null)
    
    # teacher
    create table student(
    sno char(8) primary key,
    sname char(8) not null,
    sex char(2),
    birthday date,
    tel varchar(20))
    
    # student_pwd
    create table student_pwd(
    user char(8) primary key,
    pwd varchar(40) not null)
    
    # teacher_pwd
    create table teacher_pwd(
    user char(8) primary key,
    pwd varchar(40) not null)
    
    

    重要!!

    使用程序之前,请先使用MySQL创建一个teacher账号。

    需要同时在teacher表、teacher_pwd表中键入信息。

    (pwd表中的user即为教工工号或学生学号)

    程序开头

    from tkinter import *
    import pymysql
    from tkinter import messagebox  # 消息提示框
    from tkinter import ttk
    
    
    class Basedesk:
        """
        基准框模块
        """
    
        def __init__(self, master):
            # 主界面
            self.root = master  # 窗口传入
            self.root.config()  # 顶层菜单
            self.root.title('教务管理系统')
            self.width = 600  # 界面宽
            self.height = 300  # 界面高
            # 获取屏幕尺寸以计算布局参数,使窗口居屏幕中央
            self.screenwidth = self.root.winfo_screenwidth()  # 屏幕宽
            self.screenheight = self.root.winfo_screenheight()  # 屏幕高
            self.alignstr = '%dx%d+%d+%d' % (
                self.width, self.height, (self.screenwidth - self.width) / 2, (self.screenheight - self.height) / 2)
            self.root.geometry(self.alignstr)
    
            # 进入应用
            self.R = Register(self.root)
            self.R.register()
    
    
    class Register:
    
        def __init__(self, master):
            self.root = master  # 窗口传入
            # 数据库登录
            self.ip = '*****'
            self.port = *****
            self.id = '*****'
            self.pd = '*****'
            self.db = '*****'
            # 个人信息
            self.no = ''
            self.name = ''
            self.sex = ''
            self.birthday = ''
            self.tel = ''
            self.flag = 0
            # 临时变量(click单击后选择的变量 smanage中)
            self.temporary_sno = ''
            self.temporary_sname = ''
            self.temporary_sex = ''
            self.temporary_birth = ''
            self.temporary_tel = ''
            self.temporary_pwd = ''
            self.temporary_cno = ''
            self.temporary_cname = ''
    
        '''
        登录模块
        '''
    
        def register(self):
            # 账号密码输入框
            self.initface = LabelFrame(self.root, text='教务系统登录', font=('微软雅黑', 16))
            self.initface.grid(row=1, column=0, padx=170, pady=30, )
    
            self.people = Label(self.initface, text='账号 :', font=('黑体', 12))  # 账号
            self.people.grid(row=1, column=0, padx=20, pady=10, sticky=W)
            self.password = Label(self.initface, text='密码 :', font=('黑体', 12))  # 密码
            self.password.grid(row=2, column=0, padx=20, pady=10, sticky=W)
            self.var1 = StringVar
            self.var2 = StringVar
            self.entry_people = Entry(self.initface, textvariable=self.var1)  # 账号输入框
            self.entry_people.grid(row=1, column=1, padx=10, pady=10)
            self.entry_password = Entry(self.initface, textvariable=self.var2, show='*')  # 密码输入框
            self.entry_password.grid(row=2, column=1, padx=10, pady=10)
    
            self.button_into = Button(self.initface, text='登录', command=self.conn)  # 登录按钮
            self.button_into.grid(row=3, column=0, padx=10, pady=20, sticky=E)
            self.button_into = Button(self.initface, text='退出', command=self.root.quit)  # 退出按钮
            self.button_into.grid(row=3, column=1, padx=20, pady=20, )
    
        # ======= register的登录
        def conn(self):
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            self.cursor = self.connect.cursor()
            if self.connect:
                print('连接成功')
            self.ssql = "select * " \
                        "from student_pwd " \
                        "where user=%s and pwd=%s" % (self.entry_people.get(), self.entry_password.get())
            self.tsql = "select * " \
                        "from teacher_pwd " \
                        "where user=%s and pwd=%s" % (self.entry_people.get(), self.entry_password.get())
    
            # 学生登录
            self.flag = 0
            self.cursor.execute(self.ssql)
            self.result = self.cursor.fetchone()  # 查询
            if self.result:
                print('账号密码正确')
                self.flag = 1
                self.no = self.result[0]
                self.initface.destroy()  # 销毁initface
                self.check()
            else:
                # 教工登录
                self.cursor.execute(self.tsql)
                self.result = self.cursor.fetchone()  # 查询
                if self.result:
                    print('账号密码正确')
                    self.flag = 2
                    self.no = self.result[0]
                    self.initface.destroy()  # 销毁initface
                    self.check()
            # 若均未登录成功
            if self.flag == 0:
                # 账号或密码错误清空输入框
                self.entry_people.delete(0, END)
                self.entry_password.delete(0, END)
                messagebox.showinfo(title='提示', message='账号或密码输入错误\n请重新输入?')
    
            self.cursor.close()
            self.connect.close()
    
            # self.no = self.entry_people.get()      # 获取框中用户名
            # self.pd = self.entry_password.get()     # 获取框中密码
    
        '''
        选择模块
        '''
    
        # 登录conn之后, 进入check界面
        def check(self):
            # 查询并记录基本信息
            self.basic()  # self.no self.name self.sex self.sex self.birthday self.tel
            self.label_basic = Label(self.root, text='\n'
                                                     '学号/工号: %s\n\n'
                                                     '姓名: %s\n\n'
                                                     '性别: %s\n\n'
                                                     '出生日期: %s\n\n'
                                                     '联系方式: %s\n\n' %
                                                     (self.no, self.name, self.sex,
                                                      self.birthday, self.tel),
                                     font=('宋体', 10)
                                     )
            self.label_basic.grid(row=0, columnspan=4, padx=230)
            # 界面
            self.frame_checkbutton = LabelFrame(self.root, text='功能选择', font=('微软雅黑', 14))
            self.frame_checkbutton.grid(padx=60, pady=10)
            if self.flag == 1:
                # 查询成绩按钮
                self.button_success = Button(self.frame_checkbutton, text='查询成绩', width=10, height=2, command=self.success)
                self.button_success.grid(row=1, column=0, padx=20, pady=20)
    
                # 选择课程按钮
                self.button_select = Button(self.frame_checkbutton, text='选课', width=10, height=2, command=self.select)
                self.button_select.grid(row=1, column=1, padx=20, pady=20)
            elif self.flag == 2:
                # 学生管理按钮 以及成绩管理
                self.button_smanage = Button(self.frame_checkbutton, text='学生管理', width=10, height=2, command=self.smanage)
                self.button_smanage.grid(row=1, column=0, padx=20, pady=20)
                # 课程管理按钮
                self.button_cmanage = Button(self.frame_checkbutton, text='课程管理', width=10, height=2, command=self.cmanage)
                self.button_cmanage.grid(row=1, column=1, padx=20, pady=20)
    
            # 修改密码按钮
            self.button_revise = Button(self.frame_checkbutton, text='修改密码', width=10, height=2, command=self.revise)
            self.button_revise.grid(row=1, column=2, padx=20, pady=20)
            # 修改信息按钮
            self.button_select = Button(self.frame_checkbutton, text='修改信息', width=10, height=2, command=self.update)
            self.button_select.grid(row=1, column=3, padx=20, pady=20)
    
        # 使用basic以记录基本信息
        def basic(self):
            # 链接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            if self.connect:
                print('连接成功')
                print(self.no)  # 用户名, 即学号/工号
                # 查询语句
                search_sql = ''
                if self.flag == 1:
                    search_sql = "select * " \
                                 "from student " \
                                 "where sno=%s" % self.no
                elif self.flag == 2:
                    search_sql = "select * " \
                                 "from teacher " \
                                 "where tno=%s" % self.no
                # 创建游标
                self.cursor1 = self.connect.cursor()
                self.cursor1.execute(search_sql)
                self.row = self.cursor1.fetchone()  # 读取查询结果
    
                self.name = self.row[1]
                self.sex = self.row[2]
                self.birthday = self.row[3]
                self.tel = self.row[4]
                self.row = ()  # 查询结果置空
    
        # 查询成绩界面
        def success(self):
            # 链接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            if self.connect:
                print('连接成功')
                print(self.no)  # 用户名, 即学号
                # 查询语句
                search_sql = "select c.cno,cname,score " \
                             "from student_course sc " \
                             "inner join course c " \
                             "on sc.cno=c.cno " \
                             "where sno=%s" % self.no
    
                # 创建游标
                self.cursor1 = self.connect.cursor()
                self.cursor1.execute(search_sql)
                self.row = self.cursor1.fetchone()  # 读取查询结果
    
                # 表格框
                root = Tk()  # 初始框的声明
                root.geometry('500x400+100+100')
                root.title('成绩查询系统')
                columns = ("姓名", "学号", "课程", "成绩")
                self.treeview = ttk.Treeview(root, height=18, show="headings", columns=columns)
                self.treeview.column("姓名", width=150, anchor='center')  # 表示列,不显示
                self.treeview.column("学号", width=100, anchor='center')
                self.treeview.column("课程", width=150, anchor='center')
                self.treeview.column("成绩", width=100, anchor='center')
    
                self.treeview.heading("姓名", text="姓名")  # 显示表头
                self.treeview.heading("学号", text="学号")
                self.treeview.heading("课程", text="课程")
                self.treeview.heading("成绩", text="成绩")
                self.treeview.pack(side=LEFT, fill=BOTH)
    
                # 插入查询结果
                while self.row:
                    self.treeview.insert('', 0, values=(self.name, self.no, self.row[1], self.row[2]))
                    self.row = self.cursor1.fetchone()
    
                self.cursor1.close()
                self.connect.close()
                root.mainloop()
    
        # 修改密码界面
        def revise(self):
            self.window = Tk()  # 初始框的声明
            self.window.geometry('400x200+100+100')
            self.window.title('密码修改管理')
            self.frame_revise = LabelFrame(self.window)
            self.frame_revise.grid(padx=60, pady=60)
            self.label_revise = Label(self.frame_revise, text='新密码:')
            self.label_revise.grid(row=0, column=0, padx=10, pady=10)
            self.var3 = StringVar
            self.entry_revise = Entry(self.frame_revise, textvariable=self.var3)
            self.entry_revise.grid(row=0, column=1, padx=10, pady=10)
            self.button_ok = Button(self.frame_revise, text='确定', command=self.revise_ok)
            self.button_ok.grid(row=1, column=0)
            self.button_resive = Button(self.frame_revise, text='取消', command=self.revise_resive)
            self.button_resive.grid(row=1, column=1)
            self.button_quit = Button(self.frame_revise, text='退出', command=self.window.destroy)
            self.button_quit.grid(row=1, column=2)
    
        # ======= revise的"确定"按钮
        def revise_ok(self):
            # 连接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            self.cursor2 = self.connect.cursor()  # 创建游标
            sql_revise = ''
            if self.flag == 1:
                sql_revise = "update student_pwd " \
                             "set pwd=%s " \
                             "where user=%s" % (self.entry_revise.get(), self.no)
            elif self.flag == 2:
                sql_revise = "update teacher_pwd " \
                             "set pwd=%s " \
                             "where user=%s" % (self.entry_revise.get(), self.no)
    
            if self.connect:
                print('连接成功')
                print(self.no)
                self.cursor2.execute(sql_revise)
                self.connect.commit()
                print(self.entry_revise.get())
                messagebox.showinfo(title='提示', message='密码修改成功!')
                self.cursor2.close()
                self.connect.close()
    
        # ======= revise的"取消"按钮
        def revise_resive(self):
            self.entry_revise.delete(0, END)
    
        # 选择课程界面
        def select(self):
            # 链接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            if self.connect:
                print('连接成功')
                print(self.no)  # 用户名, 即学号
                # 查询语句
                search_sql1 = "select * from course "
                search_sql2 = "select c.cno,cname,credit " \
                              "from student_course sc " \
                              "inner join course c " \
                              "on sc.cno=c.cno " \
                              "where sno=%s " % self.no
    
                # 创建游标
                self.cursor1 = self.connect.cursor()
    
                # 初始框的声明
                root_select = Tk()
                root_select.geometry('700x500+100+100')
                root_select.title('选课系统')
    
                # 所有课程表格框
                columns = ("编号", "课程", "学分")
                self.treeview1 = ttk.Treeview(root_select, height=18, show="headings", columns=columns)
                self.treeview1.column("编号", width=50, anchor='center')  # 表示列,不显示
                self.treeview1.column("课程", width=75, anchor='center')
                self.treeview1.column("学分", width=50, anchor='center')
                # self.treeview.column("成绩", width=100, anchor='center')
                self.treeview1.heading("编号", text="编号")  # 显示表头
                self.treeview1.heading("课程", text="课程")
                self.treeview1.heading("学分", text="学分")
                # self.treeview.heading("成绩", text="成绩")
                self.treeview1.grid(row=1, column=0, rowspan=3)
                # 插入查询结果
                self.cursor1.execute(search_sql1)
                self.row = self.cursor1.fetchone()  # 读取查询结果
                while self.row:
                    self.treeview1.insert('', 0, values=(self.row[0], self.row[1], self.row[2]))
                    self.row = self.cursor1.fetchone()
    
                # 已选课程表格框
                self.treeview2 = ttk.Treeview(root_select, height=18, show="headings", columns=columns)
                self.treeview2.column("编号", width=50, anchor='center')  # 表示列,不显示
                self.treeview2.column("课程", width=75, anchor='center')
                self.treeview2.column("学分", width=50, anchor='center')
                self.treeview2.heading("编号", text="编号")  # 显示表头
                self.treeview2.heading("课程", text="课程")
                self.treeview2.heading("学分", text="学分")
                self.treeview2.grid(row=1, column=1, rowspan=3)
                # 插入查询结果
                self.cursor1.execute(search_sql2)
                self.row = self.cursor1.fetchone()  # 读取查询结果
                while self.row:
                    self.treeview2.insert('', 0, values=(self.row[0], self.row[1], self.row[2]))
                    self.row = self.cursor1.fetchone()
    
                # 标签
                self.label_selectcourse1 = Label(root_select, text='学校开设课程表')
                self.label_selectcourse1.grid(row=0, column=0, padx=10, pady=10)
                self.label_selectcourse2 = Label(root_select, text='已选课程表')
                self.label_selectcourse2.grid(row=0, column=1, padx=10, pady=10)
    
                # 按钮框
                self.frame_selectbutton = LabelFrame(root_select, text='选课', font=('微软雅黑', 14))
                self.frame_selectbutton.grid(row=0, column=2, padx=10, pady=10, rowspan=4)
                # 输入框
                self.var4 = StringVar
                self.entry_insert = Entry(self.frame_selectbutton, textvariable=self.var4)
                self.entry_insert.grid(row=2, column=2, padx=10, pady=10)
                # 插入课程按钮
                self.button_insert = Button(self.frame_selectbutton, text='选择', width=10, height=2,
                                            command=self.select_insert)
                self.button_insert.grid(row=3, column=2, padx=10, pady=10)
                # Label
                self.label_selectcourse3 = Label(self.frame_selectbutton, text="输入要选择的课程编号: ")
                self.label_selectcourse3.grid(row=1, column=2, padx=10, pady=10)
    
                self.cursor1.close()
                self.connect.close()
                root_select.mainloop()
    
        # 修改信息界面
        def update(self):
            self.window = Tk()
            self.window.geometry('400x400')
            self.window.title('更新个人信息')
    
            self.varno = StringVar(self.window, value=self.no)
            self.varname = StringVar(self.window, value=self.name)
            self.varsex = StringVar(self.window, value=self.sex)
            self.varbirth = StringVar(self.window, value=self.birthday)
            self.vartel = StringVar(self.window, value=self.tel)
            # 输入框展示个人信息
            self.entry_no = Entry(self.window, textvariable=self.varno, state='disabled')  # 账号输入框
            self.entry_no.grid(row=1, column=1, padx=10, pady=10)
            self.entry_name = Entry(self.window, textvariable=self.varname)  # 名字输入框
            self.entry_name.grid(row=2, column=1, padx=10, pady=10)
            self.entry_sex = Entry(self.window, textvariable=self.varsex)  # 性别输入框
            self.entry_sex.grid(row=3, column=1, padx=10, pady=10)
            self.entry_birth = Entry(self.window, textvariable=self.varbirth)  # birth输入框
            self.entry_birth.grid(row=4, column=1, padx=10, pady=10)
            self.entry_tel = Entry(self.window, textvariable=self.vartel)
            self.entry_tel.grid(row=5, column=1, padx=10, pady=10)
            # Label输入框
            self.label_no = Label(self.window, text='学号/工号:', font=('黑体', 12))
            self.label_no.grid(row=1, column=0, padx=10, pady=10)
            self.label_name = Label(self.window, text='姓名:', font=('黑体', 12))
            self.label_name.grid(row=2, column=0, padx=10, pady=10)
            self.label_sex = Label(self.window, text='性别:', font=('黑体', 12))
            self.label_sex.grid(row=3, column=0, padx=10, pady=10)
            self.label_birth = Label(self.window, text='生日:', font=('黑体', 12))
            self.label_birth.grid(row=4, column=0, padx=10, pady=10)
            self.label_tel = Label(self.window, text='联系方式:', font=('黑体', 12))
            self.label_tel.grid(row=5, column=0, padx=10, pady=10)
            # Lable个人信息
            self.label_info = Label(self.window, text='\n'
                                                      '学号/工号: %s\n\n'
                                                      '姓名: %s\n\n'
                                                      '性别: %s\n\n'
                                                      '出生日期: %s\n\n'
                                                      '联系方式: %s\n\n' %
                                                      (self.no, self.name, self.sex,
                                                       self.birthday, self.tel),
                                    font=('宋体', 10)
                                    )
            self.label_info.grid(row=0, column=0, columnspan=2, padx=20, pady=10, sticky=W)
            # 更新按钮
            self.button_update = Button(self.window, text='更新', width=10, height=2, command=self.update_up)
            self.button_update.grid(row=2, column=2, padx=10, pady=10, rowspan=3)
    
            self.window.mainloop()
    
        # ======= select的"选课"按钮
        def select_insert(self):
            # 连接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            self.cursor2 = self.connect.cursor()  # 创建游标
            # 查询输入课程是否存在
            sql_insert1 = "select * " \
                          "from course " \
                          "where cno=%s" % self.entry_insert.get()
            # 插入/忽略课程
            sql_insert2 = "insert ignore into student_course " \
                          "(sno, cno) values " \
                          "(%s, %s);" % (self.no, self.entry_insert.get())
            if self.connect:
                print('连接成功')
    
                self.cursor2.execute(sql_insert1)
                self.result = self.cursor2.fetchone()
                if self.result:
                    self.cursor2.execute(sql_insert2)
                    self.connect.commit()
                    messagebox.showinfo(title='提示', message='已添加/已存在!')
                else:
                    messagebox.showinfo(title='提示', message='请输入正确的课程编号!')
    
                self.cursor2.close()
                self.connect.close()
    
        # ======= update的"修改信息"按钮
        def update_up(self):
            # 连接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            self.cursor2 = self.connect.cursor()  # 创建游标
            sql_up = ''
            if self.flag == 1:
                sql_up = "update student " \
                         "set sname=\'%s\', sex=\'%s\', birthday=\'%s\', tel=%s " \
                         "where sno=%s;" % \
                         (self.entry_name.get(), self.entry_sex.get(), self.entry_birth.get(), self.entry_tel.get(),
                          self.no)
            elif self.flag == 2:
                sql_up = "update teacher " \
                         "set tname=\'%s\', sex=\'%s\', birthday=\'%s\', tel=%s " \
                         "where tno=%s;" % \
                         (self.entry_name.get(), self.entry_sex.get(), self.entry_birth.get(), self.entry_tel.get(),
                          self.no)
    
            if self.connect:
                print('连接成功')
                print(self.no)
                self.cursor2.execute(sql_up)
                self.connect.commit()
                messagebox.showinfo(title='提示', message='信息修改成功!')
                self.cursor2.close()
                self.connect.close()
    
        # 学生管理界面
        def smanage(self):
            # 链接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            if self.connect:
                print('连接成功')
                print(self.no)  # 用户名, 即学号
                # 查询语句
                search_sql1 = "select sno, sname, sex, birthday, tel, pwd " \
                              "from student " \
                              "inner join student_pwd " \
                              "where sno=user "
    
                # 创建游标
                self.cursor2 = self.connect.cursor()
    
                # 初始框的声明
                root_smanage = Tk()
                root_smanage.geometry("1150x550+100+100")
                root_smanage.title("学生管理系统")
    
                # 学生管理表格框
                columns = ("学号", "姓名", "性别", "生日", "电话", "密码")
                self.treeview3 = ttk.Treeview(root_smanage, height=18, show="headings", columns=columns)
                self.treeview3.column("学号", width=80, anchor='center')  # 表示列,不显示
                self.treeview3.column("姓名", width=70, anchor='center')
                self.treeview3.column("性别", width=50, anchor='center')
                self.treeview3.column("生日", width=100, anchor='center')
                self.treeview3.column("电话", width=80, anchor='center')
                self.treeview3.column("密码", width=70, anchor='center')
                self.treeview3.heading("学号", text="学号")  # 显示表头
                self.treeview3.heading("姓名", text="姓名")
                self.treeview3.heading("性别", text="性别")
                self.treeview3.heading("生日", text="生日")
                self.treeview3.heading("电话", text="电话")
                self.treeview3.heading("密码", text="密码")
    
                self.treeview3.grid(row=1, column=0, rowspan=8, padx=10)
                # 插入查询结果
                self.cursor2.execute(search_sql1)
                self.row = self.cursor2.fetchone()  # 读取查询结果
                while self.row:
                    self.treeview3.insert('', 0, values=(
                        self.row[0], self.row[1], self.row[2], self.row[3], self.row[4], self.row[5]))
                    self.row = self.cursor2.fetchone()
    
                # 该生成绩显示表格框
                columns = ("学号", "课程号", "课程", "成绩")
                self.treeview4 = ttk.Treeview(root_smanage, height=18, show="headings", columns=columns)
                self.treeview4.column("学号", width=80, anchor='center')  # 表示列,不显示
                self.treeview4.column("课程号", width=70, anchor='center')
                self.treeview4.column("课程", width=100, anchor='center')
                self.treeview4.column("成绩", width=50, anchor='center')
                self.treeview4.heading("学号", text="学号")  # 显示表头
                self.treeview4.heading("课程号", text="课程号")
                self.treeview4.heading("课程", text="课程")
                self.treeview4.heading("成绩", text="成绩")
    
                self.treeview4.grid(row=1, column=1, rowspan=8, columnspan=3)
    
                # 框框
                self.frame_update = LabelFrame(root_smanage, text='修改信息', font=('微软雅黑', 16))
                self.frame_update.grid(row=0, column=6, padx=10, pady=10, rowspan=5)
    
                # 标签
                self.label_smanage1 = Label(root_smanage, text='学生表')
                self.label_smanage1.grid(row=0, column=0, padx=10, pady=10)
                self.label_smanage2 = Label(root_smanage, text='该生成绩表')
                self.label_smanage2.grid(row=0, column=2, padx=10, pady=10)
                self.label_score = Label(root_smanage, text='该科成绩:')
                self.label_score.grid(row=9, column=1, padx=10, pady=10)
                self.label_smanage_no = Label(self.frame_update, text='学号')
                self.label_smanage_no.grid(row=0, column=0, padx=10, pady=10)
                self.label_smanage_name = Label(self.frame_update, text='姓名')
                self.label_smanage_name.grid(row=1, column=0, padx=10, pady=10)
                self.label_smanage_sex = Label(self.frame_update, text='性别')
                self.label_smanage_sex.grid(row=2, column=0, padx=10, pady=10)
                self.label_smanage_birth = Label(self.frame_update, text='生日')
                self.label_smanage_birth.grid(row=3, column=0, padx=10, pady=10)
                self.label_smanage_tel = Label(self.frame_update, text='电话')
                self.label_smanage_tel.grid(row=4, column=0, padx=10, pady=10)
                self.label_smanage_pwd = Label(self.frame_update, text='密码')
                self.label_smanage_pwd.grid(row=5, column=0, padx=10, pady=10)
    
                # 按钮
                self.button_delete = Button(root_smanage, text='删除该学生', width=20, height=2, command=self.smanage_delete)
                self.button_delete.grid(row=9, column=0, padx=20, pady=20)
                self.button_score = Button(root_smanage, text='成绩更新', width=10, height=1, command=self.smanage_score)
                self.button_score.grid(row=9, column=3, padx=10, pady=10)
                self.button_smanage_insert = Button(root_smanage, text='插入学生', width=20, height=2,
                                                    command=self.smanage_insert)
                self.button_smanage_insert.grid(row=5, column=6, padx=20, pady=20, rowspan=5)
                self.button_smanage_update = Button(self.frame_update, text="确定修改", width=10, height=1,
                                                    command=self.smanage_update)
                self.button_smanage_update.grid(row=6, column=0, columnspan=2, padx=10, pady=10)
    
                # 输入框
                self.varscore = StringVar
                self.var_smanage_no = StringVar
                self.var_smanage_name = StringVar
                self.var_smanage_sex = StringVar
                self.var_smanage_birth = StringVar
                self.var_smanage_tel = StringVar
                self.var_smanage_pwd = StringVar
                self.entry_score = Entry(root_smanage, textvariable=self.varscore)
                self.entry_score.grid(row=9, column=2, padx=15, pady=10)
                self.entry_smanage_no = Entry(self.frame_update, textvariable=self.var_smanage_no)
                self.entry_smanage_no.grid(row=0, column=1, padx=15, pady=10)
                self.entry_smanage_name = Entry(self.frame_update, textvariable=self.var_smanage_name)
                self.entry_smanage_name.grid(row=1, column=1, padx=15, pady=10)
                self.entry_smanage_sex = Entry(self.frame_update, textvariable=self.var_smanage_sex)
                self.entry_smanage_sex.grid(row=2, column=1, padx=15, pady=10)
                self.entry_smanage_birth = Entry(self.frame_update, textvariable=self.var_smanage_birth)
                self.entry_smanage_birth.grid(row=3, column=1, padx=15, pady=10)
                self.entry_smanage_tel = Entry(self.frame_update, textvariable=self.var_smanage_tel)
                self.entry_smanage_tel.grid(row=4, column=1, padx=15, pady=10)
                self.entry_smanage_pwd = Entry(self.frame_update, textvariable=self.var_smanage_no)
                self.entry_smanage_pwd.grid(row=5, column=1, padx=15, pady=10)
    
                # 单击---显示该生详细信息
                def treeview_click1(event):
                    print('单击')
                    item_text = []
                    if self.treeview3.selection():
                        # 获取学生表展示信息值
                        for item in self.treeview3.selection():
                            item_text = self.treeview3.item(item, "values")
                            print(item_text[0])
                        # 绑定
                        self.temporary_sno = item_text[0]
                        self.temporary_sname = item_text[1]
                        self.temporary_sex = item_text[2]
                        self.temporary_birth = item_text[3]
                        self.temporary_tel = item_text[4]
                        self.temporary_pwd = item_text[5]
                        # 查询成绩
                        search_sql2 = "select c.cno,cname,score " \
                                      "from student_course sc " \
                                      "inner join course c " \
                                      "on sc.cno=c.cno " \
                                      "where sno=%s" % self.temporary_sno
                        self.cursor2.execute(search_sql2)
                        self.row = self.cursor2.fetchone()  # 读取查询结果
                        del_button(self.treeview4)
                        while self.row:
                            self.treeview4.insert('', 0,
                                                  values=(self.temporary_sno, self.row[0], self.row[1], self.row[2]))
                            self.row = self.cursor2.fetchone()
    
                        # 修改信息栏里显示信息
                        self.entry_smanage_no.delete(0, "end")
                        self.entry_smanage_no.insert(0, self.temporary_sno)
                        self.entry_smanage_name.delete(0, "end")
                        self.entry_smanage_name.insert(0, self.temporary_sname)
                        self.entry_smanage_sex.delete(0, "end")
                        self.entry_smanage_sex.insert(0, item_text[2])
                        self.entry_smanage_birth.delete(0, "end")
                        self.entry_smanage_birth.insert(0, item_text[3])
                        self.entry_smanage_tel.delete(0, "end")
                        self.entry_smanage_tel.insert(0, item_text[4])
                        self.entry_smanage_pwd.delete(0, "end")
                        self.entry_smanage_pwd.insert(0, item_text[5])
                        # 清空该科成绩框
                        self.entry_score.delete(0, "end")
    
                self.treeview3.bind('<ButtonRelease-1>', treeview_click1)  # 绑定单击离开事件
    
                # 单击---显示该科成绩
                def treeview_click2(event):
                    print('单击')
                    item_text = []
                    if self.treeview4.selection():
                        for item in self.treeview4.selection():
                            item_text = self.treeview4.item(item, "values")
                            print(item_text[0])
                        self.temporary_cname = item_text[2]
                        self.temporary_cno = item_text[1]
                        self.entry_score.delete(0, "end")
                        self.entry_score.insert(0, item_text[3])
    
                self.treeview4.bind('<ButtonRelease-1>', treeview_click2)  # 绑定单击离开事件
    
                root_smanage.mainloop()
    
        # 课程管理界面
        def cmanage(self):
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            if self.connect:
                print('连接成功')
                print(self.no)  # 用户名, 即学号
                # 查询语句
                search_sql1 = "select cno, cname, credit " \
                              "from course "
                # 创建游标
                self.cursor2 = self.connect.cursor()
    
                # 初始框的声明
                root_cmanage = Tk()
                root_cmanage.geometry("500x500+100+100")
                root_cmanage.title("课程管理系统")
    
                # 学生管理表格框
                columns = ("课程号", "名称", "学分")
                self.treeview5 = ttk.Treeview(root_cmanage, height=18, show="headings", columns=columns)
                self.treeview5.column("课程号", width=80, anchor='center')  # 表示列,不显示
                self.treeview5.column("名称", width=70, anchor='center')
                self.treeview5.column("学分", width=50, anchor='center')
                self.treeview5.heading("课程号", text="课程号")  # 显示表头
                self.treeview5.heading("名称", text="名称")
                self.treeview5.heading("学分", text="学分")
    
                self.treeview5.grid(row=1, column=0, rowspan=3, padx=10, pady=3)
                # 插入查询结果
                self.cursor2.execute(search_sql1)
                self.row = self.cursor2.fetchone()  # 读取查询结果
                while self.row:
                    self.treeview5.insert('', 0, values=(self.row[0], self.row[1], self.row[2]))
                    self.row = self.cursor2.fetchone()
    
                # 框框
                self.frame_cmanage = LabelFrame(root_cmanage, text='修改课程', font=('微软雅黑', 16))
                self.frame_cmanage.grid(row=1, column=1, padx=10, pady=10)
    
                # 标签
                self.label_cmanage1 = Label(root_cmanage, text='课程表')
                self.label_cmanage1.grid(row=0, column=0, padx=10, pady=10)
                self.label_cmanage_cno = Label(self.frame_cmanage, text='课程号:')
                self.label_cmanage_cno.grid(row=0, column=0, padx=10, pady=10)
                self.label_cmanage_cname = Label(self.frame_cmanage, text='课程名:')
                self.label_cmanage_cname.grid(row=1, column=0, padx=10, pady=10)
                self.label_cmanage_credit = Label(self.frame_cmanage, text='学分:')
                self.label_cmanage_credit.grid(row=2, column=0, padx=10, pady=10)
    
                # 按钮
                self.button_cmanage1 = Button(root_cmanage, text='选中课程删除', width=10, height=1, command=self.cmanage_delete)
                self.button_cmanage1.grid(row=4, column=0, padx=20, pady=20)
                self.button_cmanage_update = Button(self.frame_cmanage, text='确定修改', width=10, height=1,
                                                    command=self.cmanage_update)
                self.button_cmanage_update.grid(row=4, column=0, padx=20, pady=20, columnspan=2)
                self.button_cmanage_insert = Button(root_cmanage, text='添加课程', width=20, height=2,
                                                    command=self.cmanage_insert)
                self.button_cmanage_insert.grid(row=2, column=1, padx=10, pady=10)
    
                # 输入框
                self.var_cno = StringVar
                self.var_cname = StringVar
                self.var_credit = StringVar
                self.entry_cno = Entry(self.frame_cmanage, textvariable=self.var_cno)
                self.entry_cno.grid(row=0, column=1, padx=10, pady=10)
                self.entry_cname = Entry(self.frame_cmanage, textvariable=self.var_cname)
                self.entry_cname.grid(row=1, column=1, padx=10, pady=10)
                self.entry_credit = Entry(self.frame_cmanage, textvariable=self.var_credit)
                self.entry_credit.grid(row=2, column=1, padx=10, pady=10)
    
                def treeview_click1(event):
                    print('单击')
                    item_text = []
                    if self.treeview5.selection():
                        # 获取课程表展示信息值
                        for item in self.treeview5.selection():
                            item_text = self.treeview5.item(item, "values")
                            print(item_text[0])
                        self.temporary_cno = item_text[0]
                        # 修改信息栏里显示信息
                        self.entry_cno.delete(0, "end")
                        self.entry_cno.insert(0, item_text[0])
                        self.entry_cname.delete(0, "end")
                        self.entry_cname.insert(0, item_text[1])
                        self.entry_credit.delete(0, "end")
                        self.entry_credit.insert(0, item_text[2])
    
                self.treeview5.bind('<ButtonRelease-1>', treeview_click1)  # 绑定单击离开事件
    
                root_cmanage.mainloop()
    
        # ======== smanage中"删除该生"按钮
        def smanage_delete(self):
            if self.temporary_sno != '':
                # 链接数据库
                self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
                if self.connect:
                    print('连接成功')
                    print(self.no)  # 用户名, 即学号
                    # 查询语句
                    update_sql1 = "delete " \
                                  "from student " \
                                  "where sno=%s " % self.temporary_sno
                    update_sql2 = "delete " \
                                  "from student_pwd " \
                                  "where user=%s " % self.temporary_sno
    
                    # 创建游标
                    self.cursor2 = self.connect.cursor()
    
                    self.cursor2.execute(update_sql1)
                    self.connect.commit()
                    self.cursor2.execute(update_sql2)
                    self.connect.commit()
    
                    messagebox.showinfo(title='提示', message='删除成功!')
    
                    # 重置treeview3
                    del_button(self.treeview3)
                    search_sql1 = "select sno, sname, sex, birthday, tel, pwd " \
                                  "from student " \
                                  "inner join student_pwd " \
                                  "where sno=user "
                    self.cursor2.execute(search_sql1)
                    self.row = self.cursor2.fetchone()  # 读取查询结果
                    while self.row:
                        self.treeview3.insert('', 0, values=(
                            self.row[0], self.row[1], self.row[2], self.row[3], self.row[4], self.row[5]))
                        self.row = self.cursor2.fetchone()
            else:
                messagebox.showinfo(title='提示', message='未选中, 请选中学生')
    
        # ======== smanage中"更新成绩"按钮
        def smanage_score(self):
            if self.entry_score.get() != '':
                # 连接数据库
                self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
                self.cursor2 = self.connect.cursor()  # 创建游标
    
                if self.connect:
                    print('连接成功')
                    print(self.no)
                    sql_student_score = "update student_course " \
                                        "set score=%s " \
                                        "where sno=%s and cno=%s;" % \
                                        (self.entry_score.get(), self.temporary_sno, self.temporary_cno)
                    self.cursor2.execute(sql_student_score)
                    self.connect.commit()
                    messagebox.showinfo(title='提示', message='成绩更新成功!')
    
                    # 重置treeview4
                    del_button(self.treeview4)
                    search_sql2 = "select c.cno,cname,score " \
                                  "from student_course sc " \
                                  "inner join course c " \
                                  "on sc.cno=c.cno " \
                                  "where sno=%s" % self.temporary_sno
                    self.cursor2.execute(search_sql2)
                    self.row = self.cursor2.fetchone()  # 读取查询结果
                    while self.row:
                        self.treeview4.insert('', 0,
                                              values=(self.temporary_sno, self.row[0], self.row[1], self.row[2]))
                        self.row = self.cursor2.fetchone()
            else:
                messagebox.showinfo(title='提示', message='请勿更新空成绩!')
    
    
        # ========= smanage中的"修改信息"按钮
        def smanage_update(self):
            if self.entry_smanage_no.get() == self.temporary_sno:
                self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
                self.cursor2 = self.connect.cursor()  # 创建游标
                sql_up = "update student " \
                         "set sname='%s', sex='%s', birthday='%s', tel=%s " \
                         "where sno=%s; " % (self.entry_smanage_name.get(),
                                             self.entry_smanage_sex.get(),
                                             self.entry_smanage_birth.get(),
                                             self.entry_smanage_tel.get(),
                                             self.temporary_sno)
                if self.connect:
                    print('连接成功')
                    print(self.no)
                    self.cursor2.execute(sql_up)
                    self.connect.commit()
                    sql_up2 = "update student_pwd " \
                              "set pwd=%s " \
                              "where user=%s ;" % (self.entry_smanage_pwd.get(),
                                                   self.temporary_sno)
                    self.cursor2.execute(sql_up2)
                    self.connect.commit()
                    messagebox.showinfo(title='提示', message='课程信息修改成功!')
    
                    # 修改treeview3中值
                    sql_up3 = "select sno, sname, sex, birthday, tel, pwd " \
                              "from student " \
                              "inner join student_pwd " \
                              "where sno=user "
                    del_button(self.treeview3)
                    self.cursor2.execute(sql_up3)
                    self.row = self.cursor2.fetchone()
                    while self.row:
                        self.treeview3.insert('', 0,
                                              values=(self.row[0],
                                                      self.row[1],
                                                      self.row[2],
                                                      self.row[3],
                                                      self.row[4],
                                                      self.row[5]))
                        self.row = self.cursor2.fetchone()
            else:
                messagebox.showinfo(title='提示', message='请勿修改学号!')
    
        # ========= smanage中的插入学生界面
        def smanage_insert(self):
            root.window = Tk()  # 初始框的声明
            root.window.geometry('300x250+100+100')
            root.window.title('添加学生')
            # 框框
            self.frame_smanage_insert = LabelFrame(root.window)
            self.frame_smanage_insert.grid(padx=30, pady=30)
            # Label
            self.label_smanage_insert_sno = Label(self.frame_smanage_insert, text='学号:')
            self.label_smanage_insert_sno.grid(row=0, column=0, padx=10, pady=10)
            self.label_smanage_insert_sname = Label(self.frame_smanage_insert, text='姓名:')
            self.label_smanage_insert_sname.grid(row=1, column=0, padx=10, pady=10)
            self.label_smanage_insert_pwd = Label(self.frame_smanage_insert, text='初始密码为学号!')
            self.label_smanage_insert_pwd.grid(row=2, column=0, columnspan=2, padx=10, pady=10)
            # 输入框
            self.var_smanage_insert_sno = StringVar
            self.var_smanage_insert_sname = StringVar
            self.entry_smanage_insert_sno = Entry(self.frame_smanage_insert, textvariable=self.var_smanage_insert_sno)
            self.entry_smanage_insert_sno.grid(row=0, column=1, padx=10, pady=10)
            self.entry_smanage_insert_sname = Entry(self.frame_smanage_insert, textvariable=self.var_smanage_insert_sname)
            self.entry_smanage_insert_sname.grid(row=1, column=1, padx=10, pady=10)
            # 按钮
            self.button_ok = Button(self.frame_smanage_insert, text='确定', command=self.smanage_insert_ok)
            self.button_ok.grid(row=3, column=0, columnspan=2, padx=10, pady=10)
    
        # ========= smanage_insert中的"插入学生"按钮
        def smanage_insert_ok(self):
            # 连接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            self.cursor2 = self.connect.cursor()  # 创建游标
            sql_insert1 = "insert into student " \
                          "(sno, sname) values " \
                          "(%s, '%s') ;" % (self.entry_smanage_insert_sno.get(),
                                            self.entry_smanage_insert_sname.get())
            if self.connect:
                print('连接成功')
                print(self.no)
                self.cursor2.execute(sql_insert1)
                self.connect.commit()
                sql_insert2 = "insert into student_pwd " \
                              "(user, pwd) values " \
                              "(%s, %s) ;" % (self.entry_smanage_insert_sno.get(),
                                              self.entry_smanage_insert_sno.get())
                self.cursor2.execute(sql_insert2)
                self.connect.commit()
                messagebox.showinfo(title='提示', message='插入成功!')
    
                # 修改treeview3中信息
                sql_insert3 = "select sno, sname, sex, birthday, tel, pwd " \
                              "from student " \
                              "inner join student_pwd " \
                              "where sno=user "
                del_button(self.treeview3)
                self.cursor2.execute(sql_insert3)
                self.row = self.cursor2.fetchone()
                while self.row:
                    self.treeview3.insert('', 0,
                                          values=(self.row[0],
                                                  self.row[1],
                                                  self.row[2],
                                                  self.row[3],
                                                  self.row[4],
                                                  self.row[5]))
                    self.row = self.cursor2.fetchone()
    
        # ========= cmanage中"删除课程"按钮
        def cmanage_delete(self):
            # 链接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            if self.connect:
                print('连接成功')
                print(self.no)  # 用户名, 即工号
                # 查询语句
                update_sql1 = "delete " \
                              "from course " \
                              "where cno=%s " % self.temporary_cno
                update_sql2 = "delete " \
                              "from student_course " \
                              "where cno=%s " % self.temporary_cno
                # 创建游标
                self.cursor2 = self.connect.cursor()
                self.cursor2.execute(update_sql1)
                self.connect.commit()
                self.cursor2.execute(update_sql2)
                self.connect.commit()
    
                messagebox.showinfo(title='提示', message='删除成功!')
    
                # 重置treeview5
                del_button(self.treeview5)
                search_sql1 = "select cno, cname, credit " \
                              "from course "
                # 插入查询结果
                self.cursor2.execute(search_sql1)
                self.row = self.cursor2.fetchone()  # 读取查询结果
                while self.row:
                    self.treeview5.insert('', 0, values=(self.row[0], self.row[1], self.row[2]))
                    self.row = self.cursor2.fetchone()
    
        # ========= cmanage中插入课程界面
        def cmanage_insert(self):
            root.window = Tk()  # 初始框的声明
            root.window.geometry('300x250+100+100')
            root.window.title('添加课程')
            # 框框
            self.frame_cmanage_insert = LabelFrame(root.window)
            self.frame_cmanage_insert.grid(padx=30, pady=30)
            # Label
            self.label_cmanage_insert_cno = Label(self.frame_cmanage_insert, text='课程号:')
            self.label_cmanage_insert_cno.grid(row=0, column=0, padx=10, pady=10)
            self.label_cmanage_insert_cname = Label(self.frame_cmanage_insert, text='课程名:')
            self.label_cmanage_insert_cname.grid(row=1, column=0, padx=10, pady=10)
            self.label_cmanage_insert_credit = Label(self.frame_cmanage_insert, text='学分:')
            self.label_cmanage_insert_credit.grid(row=2, column=0, padx=10, pady=10)
            # 输入框
            self.var_cmanage_insert_cno = StringVar
            self.var_cmanage_insert_cname = StringVar
            self.var_cmanage_insert_credit = StringVar
            self.entry_cmanage_insert_cno = Entry(self.frame_cmanage_insert, textvariable=self.var_cmanage_insert_cno)
            self.entry_cmanage_insert_cno.grid(row=0, column=1, padx=10, pady=10)
            self.entry_cmanage_insert_cname = Entry(self.frame_cmanage_insert, textvariable=self.var_cmanage_insert_cname)
            self.entry_cmanage_insert_cname.grid(row=1, column=1, padx=10, pady=10)
            self.entry_cmanage_insert_credit = Entry(self.frame_cmanage_insert, textvariable=self.var_cmanage_insert_credit)
            self.entry_cmanage_insert_credit.grid(row=2, column=1, padx=10, pady=10)
            # 按钮
            self.button_ok = Button(self.frame_cmanage_insert, text='确定', command=self.cmanage_insert_ok)
            self.button_ok.grid(row=3, column=0, columnspan=2, padx=10, pady=10)
    
        # ========= cmanage中"更新"按钮
        def cmanage_update(self):
            if self.entry_cno.get() == self.temporary_cno:
                self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
                self.cursor2 = self.connect.cursor()  # 创建游标
                sql_up = "update course " \
                         "set cname='%s', credit=%s " \
                         "where cno=%s; " % (self.entry_cname.get(),
                                             self.entry_credit.get(),
                                             self.temporary_cno)
                if self.connect:
                    print('连接成功')
                    print(self.no)
                    self.cursor2.execute(sql_up)
                    self.connect.commit()
                    messagebox.showinfo(title='提示', message='信息修改成功!')
    
                    # 修改treeview5中值
                    sql_up3 = "select cno, cname, credit " \
                              "from course "
                    del_button(self.treeview5)
                    # 插入查询结果
                    self.cursor2.execute(sql_up3)
                    self.row = self.cursor2.fetchone()  # 读取查询结果
                    while self.row:
                        self.treeview5.insert('', 0, values=(self.row[0], self.row[1], self.row[2]))
                        self.row = self.cursor2.fetchone()
            else:
                messagebox.showinfo(title='提示', message='请勿修改课程号!')
    
        def cmanage_insert_ok(self):
            # 连接数据库
            self.connect = pymysql.connect(host=self.ip, port=self.port, user=self.id, passwd=self.pd, db=self.db)
            self.cursor2 = self.connect.cursor()  # 创建游标
            sql_insert1 = "insert into course " \
                          "(cno, cname, credit) values " \
                          "(%s, '%s', %s) ;" % \
                          (self.entry_cmanage_insert_cno.get(),
                           self.entry_cmanage_insert_cname.get(),
                           self.entry_cmanage_insert_credit.get())
            if self.connect:
                print('连接成功')
                print(self.no)
                self.cursor2.execute(sql_insert1)
                self.connect.commit()
                messagebox.showinfo(title='提示', message='插入成功!')
    
                # 修改treeview5中值
                sql_up3 = "select cno, cname, credit " \
                          "from course "
                del_button(self.treeview5)
                # 插入查询结果
                self.cursor2.execute(sql_up3)
                self.row = self.cursor2.fetchone()  # 读取查询结果
                while self.row:
                    self.treeview5.insert('', 0, values=(self.row[0], self.row[1], self.row[2]))
                    self.row = self.cursor2.fetchone()
    
    
    # 清空treeview
    def del_button(tree):
        x = tree.get_children()
        for item in x:
            tree.delete(item)
    
    
    if __name__ == '__main__':
        # 初始化Tk()
        root = Tk()
        Basedesk(root)
        # 进入消息循环 mainloop()
        mainloop()
    

    程序末尾

    展开全文
  • C# 数据库大作业-学生管理系统

    万次阅读 多人点赞 2020-06-17 14:57:45
    制作了一个学生管理系统,基本满足了课程要求

    C# 学生管理系统

    使用数据库为SQL Server
    代码已存放在Github:https://github.com/BrumaireParis/Csharp_Curricula_Variable_System
    B站视频演示:https://www.bilibili.com/video/BV1U54y1B7DL/

    概览

    本次的实验我的程序架构大概是这个样子:
    在这里插入图片描述
    先给出我的Test解决方案里面所有的程序块:
    在这里插入图片描述
    数据库一览:
    在这里插入图片描述
    接下来我会对每项的代码进行详细说明。

    C#代码

    1、LoginForm(登录界面)

    在这里插入图片描述

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Security.Cryptography;
    using System.Windows.Forms;
    using System.Media;
    
    namespace Test
    {
        public partial class LoginForm : Form
        {
            protected override CreateParams CreateParams		//发现系统的双缓冲加载并不给力,网上搜到的重写的函数,效果拔群
            {
                get
                {
                    CreateParams cp = base.CreateParams;
                    cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
                    return cp;
                }
            }
            public LoginForm()
            {
                InitializeComponent();
                this.MaximizeBox = false;		//禁止最大化
                StartPosition = FormStartPosition.CenterScreen;
            }
            bool formMove = false;//窗体是否移动
            Point formPoint;//记录窗体的位置 这是把默认的Border去掉后的窗体移动的函数
            //一直到下面的Mouse_Down都是
            private void LoginForm_MouseDown(object sender, MouseEventArgs e)//鼠标按下
            {
                formPoint = new Point();
                int xOffset;
                int yOffset;
                if (e.Button == MouseButtons.Left)
                {
                    xOffset = -e.X - SystemInformation.FrameBorderSize.Width;
                    yOffset = -e.Y - SystemInformation.CaptionHeight - SystemInformation.FrameBorderSize.Height;
                    formPoint = new Point(xOffset, yOffset);
                    formMove = true;//开始移动
                }
            }
            private void LoginForm_MouseMove(object sender, MouseEventArgs e)//鼠标移动
            {
                if (formMove == true)
                {
                    Point mousePos = Control.MousePosition;
                    mousePos.Offset(formPoint.X, formPoint.Y);
                    Location = mousePos;
                }
            }
            private void LoginForm_MouseUp(object sender, MouseEventArgs e)//鼠标松开
            {
                if (e.Button == MouseButtons.Left)//按下的是鼠标左键
                {
                    formMove = false;//停止移动
                }
            }
    
            private string code;	//声明变量Code,作验证码
            private void playsound()
            {
                SoundPlayer play = new SoundPlayer(Test.Properties.Resources.GF_Title);
                play.PlayLooping();
            }			//播放背景音乐
            private void LoginForm_Load(object sender, EventArgs e)
            {
                playsound();		//!!注意!! 启用背景音乐时,进入重置密码界面会导致Crash,原因不明
                Random ran = new Random();
                int number;
                char code1;
                for (int i = 0; i < 4; i++)
                {
                    number = ran.Next();
                    if (number % 2 == 0)
                        code1 = (char)('0' + (char)(number % 10));
                    else
                        code1 = (char)('A' + (char)(number % 26));
                    this.code += code1.ToString();
                }
                label6.Text = code;
            }
    
            private void Button1_Click(object sender, EventArgs e)
            {
                string username = textBoxUserName.Text.Trim();  //取出账号
                string password = EncryptWithMD5(textBoxPassWord.Text.Trim());  //取出密码
                string checkcode = textBoxCode.Text.Trim();
    
                if (string.Compare(checkcode, code, true) != 0)
                {
                    MessageBox.Show("验证码错误!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    foreach (Control control in this.Controls)
                    { if (control.GetType().Name == "TextBox") { ((TextBox)control).Text = string.Empty; } }
                    LoginForm_Load();
                    return;
                }
                //string connstr = ConfigurationManager.ConnectionStrings["connectionString"].ToString(); //读取连接字符串
                try
                {
                    string myConnString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
    
                    SqlConnection sqlConnection = new SqlConnection(myConnString);  //实例化连接对象
                    sqlConnection.Open();
    
                    string sql = "select Accounts,Passwords from Account where Accounts = '" + username + "' and Passwords = '" + password + "'";                                          
                    SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
    
                    if (sqlDataReader.HasRows)
                    {
                        SoundPlayer play = new SoundPlayer(Test.Properties.Resources.HK416);
                        play.Load();
                        play.Play();
                        play.Dispose();
                        MessageBox.Show("欢迎登录:" + username, "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        sqlDataReader.Close();
                        
                        string sql1 = "SELECT * FROM Account WHERE Accounts = '" + username + "'";
                        SqlCommand command = new SqlCommand(sql1, sqlConnection);
                        SqlDataReader read = command.ExecuteReader();
                        while (read.Read())
                        {
                            Program.loginName = read["Accounts"].ToString();
                            Program.photoPath = read["Photo"].ToString();
                            Program.loginID = read["ID"].ToString();
                            Program.LoginType = read["Type"].ToString();
                        }
                        MainForm mainform = new MainForm();
                        mainform.Show();
                        this.Hide();
                    }
                    else
                    {
                        LoginForm_Load();
                        MessageBox.Show("账号密码错误!\nError:001", "notice", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    sqlConnection.Close();
                }
                catch (SqlException) {
                    LoginForm_Load();
                    MessageBox.Show("数据库连接失败!请联系管理人员!\nError:002", "发生错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                
            }
    
            private void LoginForm_KeyPress(object sender, KeyPressEventArgs e)
            {
                if ((Keys)e.KeyChar == Keys.Enter)
                {
                    Button1_Click(sender, e);
                }
            }
            private void LoginForm_Load()
            {
                //throw new NotImplementedException();
                Random ran = new Random();
                int number;
                char code1;
                code = null;
                for (int i = 0; i < 4; i++)
                {
                    number = ran.Next();
                    if (number % 2 == 0)
                        code1 = (char)('0' + (char)(number % 10));
                    else
                        code1 = (char)('A' + (char)(number % 26));
                    this.code += code1.ToString();
                }
                label6.Text = code;
            }
    
            private void Button3_Click(object sender, EventArgs e)
            {
                Reg reg = new Reg();
                reg.StartPosition = FormStartPosition.CenterScreen;
                reg.ShowDialog();
            }
            public static string EncryptWithMD5(string source)
            {
                byte[] sor = Encoding.UTF8.GetBytes(source);
                MD5 md5 = MD5.Create();
                byte[] result = md5.ComputeHash(sor);
                StringBuilder strbul = new StringBuilder(40);
                for (int i = 0; i < result.Length; i++)
                {
                    strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
                }
                return strbul.ToString();
            }
    
            private void PictureBox1_Click(object sender, EventArgs e)
            {
                Application.Exit();
            }
    
            private void PictureBox2_Click(object sender, EventArgs e)
            {
                this.WindowState = FormWindowState.Minimized;
            }
    
            private void Label6_Click(object sender, EventArgs e)
            {
                LoginForm_Load();
            }
    
            private void LinkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
            {
                ResetP resetp = new ResetP();
                resetp.StartPosition = FormStartPosition.CenterScreen;
                resetp.Show();
            }
        }
    }
    

    2、Reg(注册界面)

    在这里插入图片描述

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Security.Cryptography;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace Test
    {
        public partial class Reg : Form
        {
            protected override CreateParams CreateParams
            {
                get
                {
                    CreateParams cp = base.CreateParams;
                    cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
                    return cp;
                }
            }
            public Reg()
            {
                InitializeComponent();
            }
            bool formMove = false;//窗体是否移动
            Point formPoint;//记录窗体的位置
            private void Reg_MouseDown(object sender, MouseEventArgs e)//鼠标按下
            {
                formPoint = new Point();
                int xOffset;
                int yOffset;
                if (e.Button == MouseButtons.Left)
                {
                    xOffset = -e.X - SystemInformation.FrameBorderSize.Width;
                    yOffset = -e.Y - SystemInformation.CaptionHeight - SystemInformation.FrameBorderSize.Height;
                    formPoint = new Point(xOffset, yOffset);
                    formMove = true;//开始移动
                }
            }
            private void Reg_MouseMove(object sender, MouseEventArgs e)//鼠标移动
            {
                if (formMove == true)
                {
                    Point mousePos = Control.MousePosition;
                    mousePos.Offset(formPoint.X, formPoint.Y);
                    Location = mousePos;
                }
            }
            private void Reg_MouseUp(object sender, MouseEventArgs e)//鼠标松开
            {
                if (e.Button == MouseButtons.Left)//按下的是鼠标左键
                {
                    formMove = false;//停止移动
                }
            }
            private string code;
            private void Reg_Load(object sender, EventArgs e)
            {
                Random ran = new Random();
                int number;
                char code1;
                for (int i = 0; i < 4; i++)
                {
                    number = ran.Next();
                    if (number % 2 == 0)
                        code1 = (char)('0' + (char)(number % 10));
                    else
                        code1 = (char)('A' + (char)(number % 26));
                    this.code += code1.ToString();
                }
                label7.Text = code;
            }
            public static string EncryptWithMD5(string source)
            {
                byte[] sor = Encoding.UTF8.GetBytes(source);
                MD5 md5 = MD5.Create();
                byte[] result = md5.ComputeHash(sor);
                StringBuilder strbul = new StringBuilder(40);
                for (int i = 0; i < result.Length; i++)
                {
                    strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
                }
                return strbul.ToString();
            }
    
            private void Button1_Click(object sender, EventArgs e)
            {
                string r_username = textBox1.Text.Trim();
                string password1 = textBox2.Text.Trim();
                string password2 = textBox3.Text.Trim();
                string type = comboBox1.Text.Trim();
                string sid = textBox4.Text.Trim();
                string checkcode = textBox5.Text.Trim();
                string mail = textBox6.Text.Trim();
    
                foreach (Control control in this.Controls)
                { if (control.GetType().Name == "TextBox") { ((TextBox)control).Text = string.Empty; } }
                if (r_username==""||password2==""||sid==""||mail=="" )
                {
                    MessageBox.Show("请完整填写信息!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    Reg_Load();
                    return;
                }
                if (string.Equals(password1, password2) == false)
                {
                    MessageBox.Show("两次输入密码不相符!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    Reg_Load();
                    return;
                }
                //MessageBox.Show(type);
                if (type!="学生"&&type!="教师"&&type!="管理员")
                {
                    MessageBox.Show("未选择账号类型!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    Reg_Load();
                    return;
                }
                if (string.Compare(checkcode,code,true)!=0)
                {
                    MessageBox.Show("验证码错误!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Reg_Load();
                    return;
                }
                try
                {
                    string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
                    SqlConnection connection = new SqlConnection(connString);//创建connection对象
                    string sql = "INSERT INTO Account (Accounts,Passwords,ID,Type,Email) VALUES (@username, @userpassword,@userid,@usertype,@usermail)";
                    SqlCommand command = new SqlCommand(sql, connection);
    
                    string sql2 = "SELECT Accounts FROM Account WHERE ID='" + sid + "'OR Accounts='"+r_username+"'";
                    SqlCommand command2 = new SqlCommand(sql2, connection);
    
                    SqlParameter sqlParameter = new SqlParameter("@username", r_username);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@userpassword", EncryptWithMD5(password1));
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@usertype", type);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@userid", sid);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@usermail", mail);
                    command.Parameters.Add(sqlParameter);
    
                    //打开数据库连接
                    connection.Open();
                    SqlDataReader sqlDataReader = command2.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        MessageBox.Show("该ID或账号已被注册!\nError:003", "notice", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        Reg_Load();
                        return;
                    }
                    sqlDataReader.Close();
                    command.ExecuteNonQuery();
                    connection.Close();
                    MessageBox.Show("注册成功!");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            this.Close();
            //MessageBox.Show("看到这条表示程序还在运行","警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
    
            private void Reg_Load()
            {
                //throw new NotImplementedException();
                Random ran = new Random();
                int number;
                char code1;
                code = null;
                for (int i = 0; i < 4; i++)
                {
                    number = ran.Next();
                    if (number % 2 == 0)
                        code1 = (char)('0' + (char)(number % 10));
                    else
                        code1 = (char)('A' + (char)(number % 26));
                    this.code += code1.ToString();
                }
                label7.Text = code;
            }
    
            private void Button2_Click(object sender, EventArgs e)
            {
                this.Close();
            }
    
            private void textBox2_Leave(object sender, EventArgs e)
            {
                if (textBox2.Text.Trim() != "")
                {
                    //使用regex(正则表达式)进行格式设置 至少有数字、大写字母、小写字母各一个。最少6个字符、最长16个字符。
                    Regex regex = new Regex(@"(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z]).{6,16}");
    
                    if (regex.IsMatch(textBox2.Text))//判断格式是否符合要求
                    {
                        //MessageBox.Show("输入密码格式正确!");
                    }
                    else
                    {
                        MessageBox.Show("至少有数字、大写字母、小写字母各一个。最少6个字符、最长16个字符!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                        textBox2.Text = string.Empty;
                        Reg_Load();
                        textBox2.Focus();
                    }
                }
            }
    
            private void Button3_Click(object sender, EventArgs e)
            {
                MessageBox.Show("1、密码必须大于等于6位且小于等于16位;\n2、密码至少含有一个大写字母、小写字母和数字;\n3、每人仅可注册一个账号,如有管理员权限请注册为管理员;\n4、验证码不区分大小写。", "帮助信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
    
            private void PictureBox3_Click(object sender, EventArgs e)
            {
                this.Close();
            }
    
            private void PictureBox2_Click(object sender, EventArgs e)
            {
                this.WindowState = FormWindowState.Minimized;
            }
    
            private void Label7_Click(object sender, EventArgs e)
            {
                Reg_Load();
            }
        }
    }
    

    3、ResetP(重设密码界面)

    在这里插入图片描述

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Net;
    using System.Net.Mail;
    using System.Data.SqlClient;
    using System.Media;
    
    namespace Test
    {
        public partial class ResetP : Form
        {
            protected override CreateParams CreateParams
            {
                get
                {
                    CreateParams cp = base.CreateParams;
                    cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
                    return cp;
                }
            }
            public ResetP()
            {
                InitializeComponent();
            }
    
            private void PictureBox3_Click(object sender, EventArgs e)
            {
                this.Close();
            }
    
            private void PictureBox2_Click(object sender, EventArgs e)
            {
                this.WindowState = FormWindowState.Minimized;
            }
    
            private string code;
            private int cnt1, cnt2;
    
            public static string CreateRandomCode(int length)  //生成由数字和大小写字母组成的验证码
            {
                string list = "qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM1234567890";
                Random random = new Random();
                string code = "";   //验证码
                for (int i = 0; i < length; i++)   //循环6次得到一个伪随机的六位数验证码
                {
                    code += list[random.Next(0, list.Length - 1)];
                }
                return code;
            }
    
            private void Button1_Click(object sender, EventArgs e)
            {
                string userid = textBox1.Text.Trim();   //账号
                string email = textBox2.Text.Trim();    //邮箱
                if (!String.IsNullOrEmpty(userid) && !String.IsNullOrEmpty(email))  //账号、邮箱非空
                {
                    SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=a123456");
                    connection.Open();
                    SqlCommand command = new SqlCommand("SELECT Accounts,Email FROM Account WHERE Accounts='" + userid + "' AND Email='" + email + "'", connection);
                    SqlDataReader data = command.ExecuteReader();
                    if (data.HasRows)   //若输入的电子邮箱是账号注册时填写的邮箱
                    {
                        try
                        {
                            MailMessage mail = new MailMessage();  //实例化一个发送邮件类
                            mail.From = new MailAddress(Program.Email163);   //发件人邮箱地址
                            mail.To.Add(new MailAddress(email));    //收件人邮箱地址
                            mail.Subject = "【选课管理系统V1.0】找回密码";    //邮件标题
                            code = CreateRandomCode(6);   //生成伪随机的6位数验证码
                            mail.Body = "验证码是: " + code + ",请在5分钟内进行验证。验证码提供给他人可能导致账号被盗,请勿泄露,谨防被骗。系统邮件请勿回复。";  //邮件内容          
                            SmtpClient client = new SmtpClient("smtp.163.com");   //实例化一个SmtpClient类。
                            client.EnableSsl = true;    //使用安全加密连接
                            client.Credentials = new NetworkCredential(Program.Email163, Program.AuthorizationCode);//验证发件人身份(发件人的邮箱,邮箱里的生成授权码);        
                            client.Send(mail);
                            //计时器初始化
                            cnt1 = 600;
                            cnt2 = 3000;
                            timer1.Enabled = true;   //time1用来记录1分钟
                            timer2.Enabled = true;   //time2用来记录5分钟
                            button1.Enabled = false;  //发送按钮不可点击
                            MessageBox.Show("发送成功!");
                        }
                        catch
                        {
                            MessageBox.Show("发送失败!\n请检查邮箱是否输入有误。", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                    else
                    {
                        MessageBox.Show("该邮箱不是账号绑定的邮箱。", "失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
                else
                {
                    MessageBox.Show("请将账号和邮箱填写完整!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            private void Timer1_Tick(object sender, EventArgs e)    //发送完邮件,需要60秒后才能再次发送邮件
            {
                if (cnt1 > 0)
                {
                    cnt1--;
                    button1.Text = "发送(" + cnt1/10 + ")";
                }
                else
                {
                    timer1.Enabled = false;
                    button1.Enabled = true;
                    button1.Text = "发送";
                }
            }
    
            private void Timer2_Tick(object sender, EventArgs e)    //验证码5分钟内有效,但是如果有新的验证码出现,旧验证码就会GG
            {
                if (cnt2 == 0)
                {
                    timer2.Enabled = false;
                    code = CreateRandomCode(6);    //旧的验证码过期,生成一个新的验证码
                }
            }
    
            private void Button2_Click(object sender, EventArgs e)
            {
                string rpassword = Program.EncryptWithMD5(textBox4.Text.Trim());
                string ac = textBox1.Text.Trim();
                if(string.Compare(textBox3.Text.Trim(),code,true)==0)
                {
                    try
                    {
                        string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
                        SqlConnection connection = new SqlConnection(connString);//创建connection对象
                        string sql = "Update Account set Passwords = '"+rpassword+"'Where Accounts='"+ac+"'";
                        SqlCommand command = new SqlCommand(sql, connection);
    
                        SqlParameter sqlParameter = new SqlParameter("@userid", ac);
                        command.Parameters.Add(sqlParameter);
                        sqlParameter = new SqlParameter("@userpassword", rpassword);
                        command.Parameters.Add(sqlParameter);
    
                        connection.Open();
                        command.ExecuteNonQuery();
                        connection.Close();
                        MessageBox.Show("更改密码成功!");
                        this.Close();
    
                    }
                    catch(SqlException)
                    {
                        MessageBox.Show("数据库连接错误!");
                    }
                }
                else
                {
                    MessageBox.Show("验证码错误!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }
    }
    
    

    4、MainForm(主界面,包括了选课和查询界面)

    在这里插入图片描述

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data;
    using System.Threading.Tasks;
    using System.Drawing;
    using System.Windows.Forms;
    using System.Media;
    using System.IO;
    using System.Data.SqlClient;
    using System.Linq.Expressions;
    
    namespace Test
    {
        public partial class MainForm : Form
        {
            int PanelWidth;
            bool isCollapsed;
            protected override CreateParams CreateParams
            {
                get
                {
                    CreateParams cp = base.CreateParams;
                    cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
                    return cp;
                }
            }
            public MainForm()
            {
                InitializeComponent();
                PanelWidth = 0;//panel1.Left;
                isCollapsed = true;
            }
    
            bool formMove = false;//窗体是否移动
            Point formPoint;//记录窗体的位置
            private void MainForm_MouseDown(object sender, MouseEventArgs e)//鼠标按下
            {
                formPoint = new Point();
                int xOffset;
                int yOffset;
                if (e.Button == MouseButtons.Left)
                {
                    xOffset = -e.X - SystemInformation.FrameBorderSize.Width;
                    yOffset = -e.Y - SystemInformation.CaptionHeight - SystemInformation.FrameBorderSize.Height;
                    formPoint = new Point(xOffset, yOffset);
                    formMove = true;//开始移动
                }
            }
            private void MainForm_MouseMove(object sender, MouseEventArgs e)//鼠标移动
            {
                if (formMove == true)
                {
                    Point mousePos = Control.MousePosition;
                    mousePos.Offset(formPoint.X, formPoint.Y);
                    Location = mousePos;
                }
            }
            private void MainForm_MouseUp(object sender, MouseEventArgs e)//鼠标松开
            {
                if (e.Button == MouseButtons.Left)//按下的是鼠标左键
                {
                    formMove = false;//停止移动
                }
            }
            protected override void OnResize(EventArgs e)
            {
                this.Region = null;
                SetWindowR();
    
            }
    
            private void SetWindowR()
            {
                System.Drawing.Drawing2D.GraphicsPath 
                gPath = new System.Drawing.Drawing2D.GraphicsPath();
                Rectangle rect = new Rectangle(0, 5, this.Width, this.Height - 5);
                gPath = GetRoundedRP(rect, 30); //后面的30是圆的角度,数值越大圆角度越大
                this.Region = new Region(gPath);
            }
    
            private System.Drawing.Drawing2D.GraphicsPath GetRoundedRP(Rectangle rect, int a)
            {
                int diameter = a;
                Rectangle arcRect = new Rectangle(rect.Location, new Size(diameter, diameter));
                System.Drawing.Drawing2D.GraphicsPath gp = new System.Drawing.Drawing2D.GraphicsPath();
                gp.AddArc(arcRect, 180, 90);
                arcRect.X = rect.Right - diameter;
                gp.AddArc(arcRect, 270, 90);
                arcRect.Y = rect.Bottom - diameter;
                gp.AddArc(arcRect, 0, 90);
                arcRect.X = rect.Left;
                gp.AddArc(arcRect, 90, 90);
                gp.CloseFigure();
                return gp;
            }
    
            private void MainForm_Load(object sender, EventArgs e)
            {
                timer1.Start();
                pictureBox6.Visible = false;
                pictureBox7.Visible = false;
                pictureBox8.Visible = false;
                pictureBox9.Visible = false;
                SoundPlayer play = new SoundPlayer(Test.Properties.Resources.GF_Lobby); 
                play.PlayLooping();
                try
                {
                    string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                    SqlConnection connection = new SqlConnection(connString);
                    string sql = "SELECT Photo FROM Account WHERE Accounts='"+Program.loginName+"'";
                    SqlCommand command = new SqlCommand(sql, connection);
    
                    connection.Open();
                    SqlDataReader read = command.ExecuteReader();
    
                    while (read.Read())
                        Program.photoPath = read["Photo"].ToString();
                    read.Close();
                    connection.Close();
                    //判断是否为空,为空时的不执行
                    if (Program.photoPath != null)
                    {
                        // 将图片放置在 PictureBox 中
                        this.pictureBox10.SizeMode = PictureBoxSizeMode.Zoom;
                        this.pictureBox10.BackgroundImage = Image.FromFile(@Program.photoPath);
                    }
                }
                catch (Exception)
                { 
                    //MessageBox.Show("未能加载到照片,请尽快提交!");
                    pictureBox10.BackgroundImage = Test.Properties.Resources._404logo;
                }
                if(Program.LoginType.Trim()=="学生")
                {
                    try
                    {
                        string conString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                        SqlConnection myconnection = new SqlConnection(conString);
                        string sql2 = "SELECT * FROM Student WHERE ID='" + Program.loginID + "'";
                        SqlCommand sqlCommand = new SqlCommand(sql2, myconnection);
                        myconnection.Open();
                        SqlDataReader read1 = sqlCommand.ExecuteReader();
    
                        while (read1.Read())
                        {
                            Program.realName = read1["Sname"].ToString();
                            label11.Text = read1["Sage"].ToString();
                            label16.Text = read1["Ssex"].ToString();
                            label14.Text = read1["Sdept"].ToString();
                            label20.Text = read1["Sclass"].ToString();
                            label18.Text = read1["Stel"].ToString();
                        }
                        read1.Close();
                        myconnection.Close();
                        button3.Enabled = false;
                        button4.Enabled = false;
                        button5.Enabled = false;
                    }
                    catch (Exception) 
                    {
                        MessageBox.Show("无法初始化学生类账户!");
                    }
                }
                else if (Program.LoginType.Trim() == "教师")
                {
                    try
                    {
                        string cconString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                        SqlConnection cconnection = new SqlConnection(cconString);
                        string sql2 = "SELECT * FROM Teacher WHERE ID='" + Program.loginID + "'";
                        SqlCommand sqlCCommand = new SqlCommand(sql2, cconnection);
                        cconnection.Open();
                        SqlDataReader read2 = sqlCCommand.ExecuteReader();
    
                        while (read2.Read())
                        {
                            Program.realName = read2["Tname"].ToString();
                            label11.Text = read2["Tage"].ToString();
                            label16.Text = read2["Tsex"].ToString();
                            label14.Text = read2["Tdept"].ToString();
                            label20.Text = read2["Tlev"].ToString();
                            label18.Text = read2["Ttel"].ToString();
                        }
                        read2.Close();
                        cconnection.Close();
                        label19.Text = "职称:";
                        button3.Enabled = false;
                        button4.Enabled = false;
                    }
                    catch (Exception) { MessageBox.Show("无法初始化教师类账户!"); }
                }
                label3.Text = Program.loginName;
                labelLG.Text = Program.loginName;
                label7.Text = Program.realName;
                label9.Text = Program.loginID;
                label21.Text = Program.LoginType;
            }
    
            private void PictureBox3_Click(object sender, EventArgs e)
            {
                timer2.Stop();
                Random ran = new Random();
                int r = ran.Next(0, 5);
                pictureBox6.Visible=false;
                pictureBox7.Visible = false;
                pictureBox8.Visible = false;
                pictureBox9.Visible = false;
                if (r == 1)
                    pictureBox6.Visible = true;
                if (r == 2)
                    pictureBox7.Visible = true;
                if (r == 3)
                    pictureBox8.Visible = true;
                if (r == 4)
                    pictureBox9.Visible = true;
                timer2.Start();
    
            }
    
            private void Timer2_Tick(object sender, EventArgs e)
            {
                //timer2.Stop();
                pictureBox6.Visible = false;
                pictureBox7.Visible = false;
                pictureBox8.Visible = false;
                pictureBox9.Visible = false;
            }
    
            private void PictureBox4_Click(object sender, EventArgs e)
            {
                this.Dispose();
                LoginForm lg = new LoginForm();
                lg.Show();
            }
    
            private void Timer3_Tick(object sender, EventArgs e)
            {
                if (isCollapsed)
                {
                    panel1.Left = panel1.Left + 50;
                    if (panel1.Left >= PanelWidth)
                    {
                        timer3.Stop();
                        isCollapsed = false;
                        this.Refresh();
                    }
                }
                else
                {
                    panel1.Left = panel1.Left - 50;
                    if (panel1.Left <= -500)
                    {
                        timer3.Stop();
                        isCollapsed = true;
                        this.Refresh();
                    }
                }
            }
    
            private void PictureBox5_Click(object sender, EventArgs e)
            {
                timer3.Start();
            }
    
            private void Button7_Click(object sender, EventArgs e)
            {
                OpenFileDialog openfile = new OpenFileDialog();
                openfile.Title = "请选择要上传的图片";
                openfile.Filter = "图片(*.jpg;*.bmp;*png)|*.jpeg;*.jpg;*.bmp;*.png|AllFiles(*.*)|*.*";
                if (DialogResult.OK == openfile.ShowDialog())
                {
                    try
                    {
                        string picturePath = openfile.FileName;
                        string picName = openfile.SafeFileName;
                        string pSaveFilePath = "D:\\LocalPic\\";//指定存储的路径
                        if (!System.IO.Directory.Exists(@"D:\LocalPic"))
                        {
                            System.IO.Directory.CreateDirectory(@"D:\LocalPic");//不存在就创建目录
                        }
                        string picSave = pSaveFilePath + picName;
                        Program.photoPath = picSave;
                        //MessageBox.Show(Program.photoPath);
                        if (File.Exists(picturePath))//必须判断要复制的文件是否存在
                                File.Copy(picturePath, picSave, true);
                        
                        string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456"; 
                        SqlConnection connection = new SqlConnection(connString);
                        string sql = "Update Account set Photo = '"+picSave+"'WHERE Accounts = '"+Program.loginName+"'";
                        SqlCommand command = new SqlCommand(sql, connection);
                        connection.Open();
                        command.ExecuteNonQuery();
                        connection.Close();
                        this.pictureBox10.SizeMode = PictureBoxSizeMode.Zoom;
                        this.pictureBox10.BackgroundImage = Image.FromFile(@Program.photoPath);
                        MessageBox.Show("上传成功!");
                    }
                    catch(Exception) { MessageBox.Show("上传图片出现错误!"); }
                }
            }
    
            private void button6_Click(object sender, EventArgs e)
            {
                Pinfo pinfo = new Pinfo();
                pinfo.ShowDialog();
                MainForm_Load(sender,e);
                pinfo.Close();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                panel4.Visible = true;
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                panel3.Visible = true;
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                //MessageBox.Show("Update与Delete已整合在一起。");
                Admin admin = new Admin();
                admin.Show();
                this.Close();
            }
    
            private void button4_Click(object sender, EventArgs e)
            {
                //MessageBox.Show("Update与Delete已整合在一起。");
                Admin admin = new Admin();
                admin.Show();
                this.Close();
            }
    
            private void button5_Click(object sender, EventArgs e)
            {
                panel5.Visible = true;
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    string sql = "EXEC Proc_TRANSFER1 " +
                                 "SELECT* FROM SC_Avg";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dataGridView3.DataSource = ds.Tables[0];
                    dataGridView3.ReadOnly = true;
                    dataGridView3.AllowUserToAddRows = false;
                    dataGridView3.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                    dataGridView3.MultiSelect = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }
            private void PictureBox1_Click(object sender, EventArgs e)
            {
                timer.Start();
            }
    
            private void PictureBox2_Click(object sender, EventArgs e)
            {
                this.WindowState = FormWindowState.Minimized;
            }
    
            private void Timer1_Tick(object sender, EventArgs e)
            {
                label2.Text = "今の時間:" + System.DateTime.Now.ToString();
            }
    
            private void Timer_Tick(object sender, EventArgs e)
            {
                if (this.Opacity >= 0.025)
                    this.Opacity -= 0.025;
                else
                {
                    timer.Stop();
                    Application.Exit();
                }
            }
    
      
    		/*---------------------------*/
            //查询界面
            private bool xscx;
            private bool cxcj;
    
            private void Panel3_Load(object sender,EventArgs e)
            {
                label22.Visible = false;
                textBox1.Visible = false;
                button11.Visible = false;
                label23.Visible = false;
                if (Program.LoginType.Trim()=="教师")
                {
                    button8.BackgroundImage = Test.Properties.Resources.skcx;
                    button9.BackgroundImage = Test.Properties.Resources.xscx;
                    pictureBox11.BackgroundImage = Test.Properties.Resources.txgz;
                    pictureBox12.BackgroundImage = Test.Properties.Resources.lyco;
                    xscx = false;
                }
                else
                {
                    cxcj = false;
                }
            }
            private void button10_Click(object sender, EventArgs e)
            {
                panel3.Visible = false;
                dataGridView1.Visible = false;
                label22.Visible = false;
                label23.Visible = false;
                textBox1.Visible = false;
                button11.Visible = false;
            }
    
            //查询全部课程
            private void StuCourse()
            {
                //数据库连接串
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                //创建SqlConnection的实例
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    //打开数据库
                    conn.Open();
                    string sql = "SELECT TRIM(Sname) as 姓名, TRIM(Cno) as 课程号, TRIM(Cname) as 课程名,Ccredit as 学分, Trim(Tname) as 任课老师 FROM Stuc WHERE Sname='" + Program.realName+"'";
                    //创建SqlDataAdapter类的对象
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    //创建DataSet类的对象
                    DataSet ds = new DataSet();
                    //使用SqlDataAdapter对象sda将查新结果填充到DataSet对象ds中
                    da.Fill(ds,"Stuc");
                    //设置表格控件的DataSource属性
                    dataGridView1.DataSource = ds.Tables["Stuc"];//.DefaultView;//.Tables[0];
                    //设置数据表格为只读
                    dataGridView1.ReadOnly = true;
                    //不允许添加行
                    dataGridView1.AllowUserToAddRows = false;
                    //整行选中
                    dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }
    
            private void StuGrade()
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                //创建SqlConnection的实例
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    //打开数据库
                    conn.Open();
                    string sql = "SELECT Trim(Sname) as  姓名 ,Trim(SC.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit AS 学分,Grade as 成绩 FROM Course, SC, Student WHERE Student.ID = SC.ID AND SC.Cno = Course.Cno AND Student.ID='"+Program.loginID+"'";
                    string sql1 = "SELECT Trim(SC.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit AS 学分,Grade as 成绩 FROM Course, SC, Student WHERE Student.ID = SC.ID AND SC.Cno = Course.Cno AND Cname like '%"+textBox1.Text.Trim()+"%' and Student.ID = '"+Program.loginID+"' ";
                    //创建SqlDataAdapter类的对象
                    if (textBox1.Text.Trim() != "ALL")
                    {
                        SqlDataAdapter db = new SqlDataAdapter(sql1, conn);
                        DataSet dss = new DataSet();
                        db.Fill(dss);
                        dataGridView1.DataSource = dss.Tables[0];
                    }
                    else
                    {
                        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    } 
                    dataGridView1.ReadOnly = true;
                    dataGridView1.AllowUserToAddRows = false;
                    dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }
    
            private void TeaCourse()
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                //创建SqlConnection的实例
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    string sql = "SELECT Trim(Tname) as  姓名 ,Trim(Course.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit as 学分 ,Trim(Cpno) as 先修课序号 FROM Course, Teacher WHERE Course.ID = Teacher.ID AND Teacher.ID='"+Program.loginID+"' ";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dataGridView1.DataSource = ds.Tables[0];
                    dataGridView1.ReadOnly = true;
                    dataGridView1.AllowUserToAddRows = false;
                    dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }
    
            private void TeacherStu()
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                //创建SqlConnection的实例
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    //打开数据库
                    conn.Open();
                    string sql = "SELECT Trim(SC.Cno) as 课序号 , Trim(Cname) as 课程名 ,Trim(Student.ID) as 学号,trim(Sname) as 学生姓名,Grade as 成绩 FROM Teacher, Course, SC, Student WHERE Student.ID = SC.ID AND SC.Cno = Course.Cno and Course.ID = Teacher.ID And Teacher.ID='" + Program.loginID + "'order by SC.Cno ASC; ";
                    string sql1 = "SELECT Trim(Student.ID) AS 学号, Trim(Sname) as 学生名,Trim(SC.Cno) as 课序号 , Trim(Cname) as 课程名,trim(Tname) as 教师名 FROM Course, SC, Student, Teacher WHERE Teacher.ID = Course.ID AND SC.Cno = Course.Cno AND SC.ID = Student.ID and Sname like '%" + textBox1.Text.Trim() + "%' and Teacher.ID = '" + Program.loginID + "'";
                    if (textBox1.Text.Trim() != "ALL")
                    {
                        SqlDataAdapter daa = new SqlDataAdapter(sql1, conn);
                        DataSet dss = new DataSet();
                        daa.Fill(dss);
                        dataGridView1.DataSource = dss.Tables[0];
                    }
                    else
                    {
                        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                    }
                    //设置数据表格为只读
                    dataGridView1.ReadOnly = true;
                    //不允许添加行
                    dataGridView1.AllowUserToAddRows = false;
                    //整行选中
                    dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }
    
            private void button8_Click(object sender, EventArgs e)
            {
                dataGridView1.Visible = true;
                label22.Visible = false;
                label23.Visible = false;
                textBox1.Visible = false;
                button11.Visible = false;
                if (Program.LoginType.Trim() == "学生")
                {
                    StuCourse();
                }
                else if (Program.LoginType.Trim() == "教师")
                {
                    TeaCourse();
                }
            }
            private void button9_Click(object sender, EventArgs e)
            {
                dataGridView1.Visible = true;
                label22.Visible = true;
                textBox1.Visible = true;
                label23.Visible = true;
                button11.Visible = true;
                dataGridView1.DataSource = null;
                if (Program.LoginType.Trim() == "学生")
                {
                    cxcj = true;
                    xscx = false;
                    label22.Text = "请输入你要查询的课程名:";
    
                }
                if (Program.LoginType.Trim() == "教师")
                {
                    xscx = true;
                    cxcj = false;
                    label22.Text = "请输入你要查询的学生名:";
                }
            }
    
            private void button11_Click(object sender, EventArgs e)
            {
                if(cxcj==true)
                {
                    dataGridView1.DataSource = null;
                    StuGrade();
                }
                else if(xscx==true)
                {
                    dataGridView1.DataSource = null;
                    TeacherStu();
                }
            }
    
    
    
    
    		/*------------------------------------------------------------------------*/
    		//选课退课
            private bool studentsel;
            private bool studentdel;
            private bool teachersel;
            private bool teacherdel;
            private bool teachersel2 = false;
            private bool teacherdel2 = false;
            private string sid;
            private string ccid;
            private void Panel4_Load(object sender,EventArgs e)
            {
                label24.Visible = false;
                button15.Visible = false;
                if (Program.LoginType.Trim()=="教师")
                {
                    pictureBox13.BackgroundImage = Test.Properties.Resources.NPC_Seele;
                    pictureBox14.BackgroundImage = Test.Properties.Resources._404logo;
                    button12.BackgroundImage = Test.Properties.Resources.jszr;
                    button13.BackgroundImage = Test.Properties.Resources.jstk;
                }
            }
    
            private void button14_Click(object sender, EventArgs e)
            {
                panel4.Visible = false;
                label24.Visible = false;
                button15.Visible = false;
                dataGridView2.DataSource = null;
                dataGridView2.Visible = false;
            }
            private void StuSle()
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    string sql = "SELECT distinct TRIM(Course.Cno) as 课序号, TRIM(Cname) as 课程名, TRIM(Cpno) as 先修课序号,Ccredit as 学分, Trim(Tname) as 任课老师  FROM Course, Teacher, SC Where Cname not in  (SELECT Cname From SC, Course where SC.ID = '"+Program.loginID+"' and Course.Cno = SC.Cno) and Teacher.ID = Course.ID; ";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dataGridView2.DataSource = ds.Tables[0];
                    dataGridView2.ReadOnly = true;
                    dataGridView2.AllowUserToAddRows = false;
                    dataGridView2.MultiSelect = false;
                    dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
    
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            private void StuDel()
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    string sql = "SELECT TRIM(Sname) as 姓名, TRIM(Cno) as 课程号, TRIM(Cname) as 课程名,Ccredit as 学分, Trim(Tname) as 任课老师 FROM Stuc WHERE Sname='" + Program.realName + "'";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds, "Stuc");
                    dataGridView2.DataSource = ds.Tables["Stuc"];//.DefaultView;//.Tables[0];
                    dataGridView2.ReadOnly = true;
                    dataGridView2.AllowUserToAddRows = false;
                    dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                    dataGridView2.MultiSelect = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            private void TeaSel()
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    string sql = "SELECT TRIM(ID) as 学号, TRIM(Sname) as 姓名, TRIM(Ssex) as 性别,Sage as 年龄, Trim(Sdept) as 院系, Trim(Sclass) as 班级 FROM Student";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dataGridView2.DataSource = ds.Tables[0];
                    dataGridView2.ReadOnly = true;
                    dataGridView2.AllowUserToAddRows = false;
                    dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                    dataGridView2.MultiSelect = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }
            private void TeaDel()
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(connStr);
                    conn.Open();
                    string sql = "SELECT Trim(Tname) as  姓名 ,Trim(Course.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit as 学分 ,Trim(Cpno) as 先修课序号 FROM Course, Teacher WHERE Course.ID = Teacher.ID AND Teacher.ID='" + Program.loginID + "' ";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dataGridView2.DataSource = ds.Tables[0];
                    dataGridView2.ReadOnly = true;
                    dataGridView2.AllowUserToAddRows = false;
                    dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                    dataGridView2.MultiSelect = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }
    
            private void button12_Click(object sender, EventArgs e)
            {
                dataGridView2.DataSource = null;
                dataGridView2.Visible = true;
                label24.Visible = true;
                button15.Visible = true;
                if (Program.LoginType.Trim() == "学生")
                {
                    studentsel = true;
                    studentdel = false;
                    teachersel = false;
                    teacherdel = false;
                    StuSle();
                }
                else
                {
                    label24.Text = "请选择您要置入课程的学生:";
                    TeaSel();
                    studentsel = false;
                    studentdel = false;
                    teachersel = true;
                    teacherdel = false;
                }
            }
    
            private void button13_Click(object sender, EventArgs e)
            {
                dataGridView2.DataSource = null;
                dataGridView2.Visible = true;
                label24.Visible = true;
                button15.Visible = true;
                if (Program.LoginType.Trim() == "学生")
                {
                    studentsel = false;
                    studentdel = true;
                    teachersel = false;
                    teacherdel = false;
                    label24.Text = "请选择你要退选的课:";
                    StuDel();
                }
                else
                {
                    label24.Text = "请选择您要移除学生的课程:";
                    TeaDel();
                    studentsel = false;
                    studentdel = false;
                    teachersel = false;
                    teacherdel = true;
                }
            }
    
            private void button15_Click(object sender, EventArgs e)
            {
                if (studentsel == true)
                {
                    int a = dataGridView2.CurrentRow.Index;
                    string cid = dataGridView2.Rows[a].Cells[0].Value.ToString();
                    DialogResult dr = MessageBox.Show("你确定要选修这门课吗?", "通知", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (dr == DialogResult.OK)
                    {
                        try
                        {
                            string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
                            SqlConnection connection = new SqlConnection(connString);//创建connection对象
                            string sql = "insert into SC (ID,Cno,Grade) " +
                                                                    "values (@uid, @uno,NULL)";
                            SqlCommand command = new SqlCommand(sql, connection);
    
                            SqlParameter sqlParameter = new SqlParameter("@uid", Program.loginID.Trim());
                            command.Parameters.Add(sqlParameter);
                            sqlParameter = new SqlParameter("@uno", cid);
                            command.Parameters.Add(sqlParameter);
    
                            //打开数据库连接
                            connection.Open();
                            command.ExecuteNonQuery();
                            connection.Close();
                            MessageBox.Show("选课成功!");
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("选课失败!");
                        }
                    }
                    else
                        return;
                }
                else if (studentdel == true)
                {
                    int a = dataGridView2.CurrentRow.Index;
                    string cid = dataGridView2.Rows[a].Cells[1].Value.ToString();
                    DialogResult dr = MessageBox.Show("你确定要退选这门课吗?", "通知", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (dr == DialogResult.OK)
                    {
                        try
                        {
                            string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
                            SqlConnection connection = new SqlConnection(connString);//创建connection对象
                            string sql = "Delete From SC Where Cno = '" + cid + "'and ID='" + Program.loginID + "'";
                            SqlCommand command = new SqlCommand(sql, connection);
    
                            //打开数据库连接
                            connection.Open();
                            command.ExecuteNonQuery();
                            connection.Close();
                            MessageBox.Show("退课成功!");
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("退课失败!");
                        }
                    }
                    else
                        return;
                }
                else if (teachersel == true && teachersel2==false)
                {
                    int a = dataGridView2.CurrentRow.Index;
                    sid = dataGridView2.Rows[a].Cells[0].Value.ToString();
                    MessageBox.Show("选定学生成功!");
                    dataGridView2.DataSource = null;
                    label24.Text = "请选择您要置入的课程(仅能置入您教授的课):";
                    string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                    SqlConnection conn = null;
                    try
                    {
                        conn = new SqlConnection(connStr);
                        conn.Open();
                        string sql = "SELECT Trim(Tname) as  教师姓名 ,Trim(Course.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit as 学分 ,Trim(Cpno) as 先修课序号 FROM Course, Teacher WHERE Course.ID = Teacher.ID AND Teacher.ID='" + Program.loginID + "' ";
                        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        dataGridView2.DataSource = ds.Tables[0];
                        dataGridView2.ReadOnly = true;
                        dataGridView2.AllowUserToAddRows = false;
                        dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                        dataGridView2.MultiSelect = false;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("查询错误!" + ex.Message);
                    }
                    finally
                    {
                        if (conn != null)
                        {
                            conn.Close();
                        }
                    }
                    teachersel2 = true;
                    return;
                }
                else if (teacherdel == true && teacherdel2 == false)
                {
                    int a = dataGridView2.CurrentRow.Index;
                    ccid = dataGridView2.Rows[a].Cells[1].Value.ToString();
                    MessageBox.Show("选定课程成功!");
                    dataGridView2.DataSource = null;
                    label24.Text = "请选择您要退课的学生(仅能去除您教授的学生):";
                    string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                    SqlConnection conn = null;
                    try
                    {
                        conn = new SqlConnection(connStr);
                        conn.Open();
                        string sql = "SELECT TRIM(Student.ID) as 学号, TRIM(Sname) as 姓名, TRIM(Ssex) as 性别,Sage as 年龄, Trim(Sdept) as 院系, Trim(Sclass) as 班级 FROM Student,SC  Where Student.ID=SC.ID and SC.Cno='"+ccid+"'";
                        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        dataGridView2.DataSource = ds.Tables[0];
                        dataGridView2.ReadOnly = true;
                        dataGridView2.AllowUserToAddRows = false;
                        dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                        dataGridView2.MultiSelect = false;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("查询错误!" + ex.Message);
                    }
                    finally
                    {
                        if (conn != null)
                        {
                            conn.Close();
                        }
                    }
                    teacherdel2 = true;
                    return;
                }
                else if(teachersel2==true&&teachersel==true)
                {
                    int a = dataGridView2.CurrentRow.Index;
                    ccid = dataGridView2.Rows[a].Cells[1].Value.ToString();
                    DialogResult dr = MessageBox.Show("你确定要置入这门课吗?", "通知", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (dr == DialogResult.OK)
                    {
                        try
                        {
                            string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
                            SqlConnection connection = new SqlConnection(connString);//创建connection对象
                            string sql = "insert into SC (ID,Cno,Grade) " +
                                                                    "values (@uid, @uno,NULL)";
                            SqlCommand command = new SqlCommand(sql, connection);
    
                            SqlParameter sqlParameter = new SqlParameter("@uid", sid);
                            command.Parameters.Add(sqlParameter);
                            sqlParameter = new SqlParameter("@uno", ccid);
                            command.Parameters.Add(sqlParameter);
    
                            //打开数据库连接
                            connection.Open();
                            command.ExecuteNonQuery();
                            connection.Close();
                            MessageBox.Show("置入成功!");
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("置入失败!");
                        }
                        teachersel2 = false;
                        this.button12_Click(sender, e);
    
                    }
                    else
                    {
                        label24.Text = "请选择您要置入课程的学生:";
                        this.button12_Click(sender,e);
                        teachersel2 = false;
                        return;
                    }
                }
                else if(teacherdel2==true&&teacherdel==true)
                {
                    int a = dataGridView2.CurrentRow.Index;
                    sid = dataGridView2.Rows[a].Cells[0].Value.ToString();
                    DialogResult dr = MessageBox.Show("你确定要删除这名学生的选课吗?", "通知", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                    if (dr == DialogResult.OK)
                    {
                        try
                        {
                            string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
                            SqlConnection connection = new SqlConnection(connString);//创建connection对象
                            string sql = "Delete from SC where ID='"+sid+"' and Cno='"+ccid+"'";
                            SqlCommand command = new SqlCommand(sql, connection);
    
                            //打开数据库连接
                            connection.Open();
                            command.ExecuteNonQuery();
                            connection.Close();
                            MessageBox.Show("删除成功!");
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("删除失败!");
                        }
                        teacherdel2 = false;
                        this.button13_Click(sender, e);
                    }
                    else
                    {
                        this.button13_Click(sender, e);
                        label24.Text = "请选择您要删除学生的课程:";
                        teacherdel2 = false;
                        return;
                    }
                }
                else
                    MessageBox.Show("Debug~~");
            }
    
            private void pictureBox15_Click(object sender, EventArgs e)
            {
                panel5.Visible = false;
                dataGridView3.DataSource = null;
            }
        }
    }
    

    5、Pinfo(个人信息界面)

    在这里插入图片描述

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace Test
    {
        public partial class Pinfo : Form
        {
            protected override CreateParams CreateParams
            {
                get
                {
                    CreateParams cp = base.CreateParams;
                    cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
                    return cp;
                }
            }
            protected override void WndProc(ref Message m)
            {
                if (m.Msg == 163 && this.ClientRectangle.Contains(this.PointToClient(new Point(m.LParam.ToInt32()))) && m.WParam.ToInt32() == 2)
                    m.WParam = (IntPtr)1;
                base.WndProc(ref m);
                if (m.Msg == 132 && m.Result.ToInt32() == 1)
                    m.Result = (IntPtr)2;
            }
            public Pinfo()
            {
                InitializeComponent();
            }
    
            private void Pinfo_Load(object sender, EventArgs e)
            {
                if (Program.LoginType.Trim() == "学生")
                {
                    try
                    {
                        string conString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                        SqlConnection myconnection = new SqlConnection(conString);
                        string sql2 = "SELECT * FROM Student WHERE ID='" + Program.loginID + "'";
                        SqlCommand sqlCommand = new SqlCommand(sql2, myconnection);
                        myconnection.Open();
                        SqlDataReader read1 = sqlCommand.ExecuteReader();
    
                        while (read1.Read())
                        {
                            textBox1.Text = read1["Sname"].ToString().Trim();
                            textBox2.Text = read1["Sage"].ToString().Trim();
                            comboBox1.Text = read1["Ssex"].ToString().Trim();
                            textBox3.Text = read1["Sdept"].ToString().Trim();
                            textBox4.Text = read1["Sclass"].ToString().Trim();
                            textBox5.Text = read1["Stel"].ToString().Trim();
                        }
                        read1.Close();
                        myconnection.Close();
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("无法初始化学生类账户!");
                    }
                }
                else if (Program.LoginType.Trim() == "教师")
                {
                    try
                    {
                        string cconString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                        SqlConnection cconnection = new SqlConnection(cconString);
                        string sql2 = "SELECT * FROM Teacher WHERE ID='" + Program.loginID + "'";
                        SqlCommand sqlCCommand = new SqlCommand(sql2, cconnection);
                        cconnection.Open();
                        SqlDataReader read2 = sqlCCommand.ExecuteReader();
    
                        while (read2.Read())
                        {
                            textBox1.Text = read2["Tname"].ToString().Trim();
                            textBox2.Text = read2["Tage"].ToString().Trim();
                            comboBox1.Text = read2["Tsex"].ToString().Trim();
                            textBox3.Text = read2["Tdept"].ToString().Trim();
                            textBox4.Text = read2["Tlev"].ToString().Trim();
                            textBox5.Text = read2["Ttel"].ToString().Trim();
                        }
                        read2.Close();
                        cconnection.Close();
                        label6.Text = "职称:";
                    }
                    catch (Exception) { MessageBox.Show("无法查询教师类账户!"); }
                }
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                foreach (Control control in this.Controls)
                { 
                    if (control.GetType().Name == "TextBox") 
                    {
                        ((TextBox)control).Text = string.Empty; 
                    } 
                }
                comboBox1.Text = "-请选择-";
            }
    
            private void textBox2_Leave(object sender, EventArgs e)
            {
                bool flag = true;
                string text = textBox2.Text.Trim();
                for (int i = 0; i <= text.Length-1; i++)
                {
                    if((int)text[i]<48||(int)text[i]>58)
                    {
                        flag = false;
                        break;
                    }
                }
                if (textBox2.Text.Trim() != "")
                {
                    if (flag == true)
                    { }
                    else
                    {
                        MessageBox.Show("请输入数字!");
                        textBox2.Text = "";
                        textBox2.Focus();
                    }
                }
                else
                    MessageBox.Show("信息不能为空");
            }
            private void comboBox1_Leave(object sender,EventArgs e)
            {
                if (comboBox1.Text.Trim() != "男" || comboBox1.Text.Trim() != "女" || comboBox1.Text.Trim() != "其它")
                    MessageBox.Show("请不要输入奇怪的信息~");
            }
      
            private void textBox5_Leave(object sender, EventArgs e)
            {
                bool flag = true;
                string text = textBox5.Text.Trim();
                for (int i = 0; i <= text.Length - 1; i++)
                {
                    if ((int)text[i] == 43 || (int)text[i] == 45)
                        break;
                    if ((int)text[i] < 48 || (int)text[i] > 58)
                    {
                        flag = false;
                        break;
                    }
                }
                if (textBox5.Text.Trim() != "")
                {
                    if (flag == true)
                    { }
                    else
                    {
                        MessageBox.Show("请输入正确的手机号码格式!");
                        //textBox5.Text = "";
                        textBox5.Focus();
                    }
                }
                else
                    MessageBox.Show("信息不能为空");
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                string username = textBox1.Text.Trim();
                string age = textBox2.Text.Trim();
                string sex = comboBox1.Text.Trim();
                string dept = textBox3.Text.Trim();
                string Lclass = textBox4.Text.Trim();
                string tel = textBox5.Text.Trim();
    
                if (username == "" || age == "" || sex == "" || dept == "" || Lclass == "" || tel == "")
                {
                    MessageBox.Show("有信息为空,操作失败!");
                    return;
                }
                string myConnString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
    
                SqlConnection sqlConnection = new SqlConnection(myConnString);  //实例化连接对象
                sqlConnection.Open();
    
                if (Program.LoginType.Trim() == "学生")
                {
                    /*try
                    {*/
                    string sql = "Update Student set Sname= @usern, Sage=@usera,Ssex=@users,Sdept=@userd,Sclass=@userc,Stel=@usert Where ID='" + Program.loginID + "'";
                    SqlCommand command = new SqlCommand(sql, sqlConnection);
    
                    SqlParameter sqlParameter = new SqlParameter("@usern", username);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@usera", age);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@users", sex);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@userd", dept);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@userc", Lclass);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@usert", tel);
                    command.Parameters.Add(sqlParameter);
    
                    command.ExecuteNonQuery();
                    /*}
                    catch (Exception) { MessageBox.Show("数据更新出错"); }*/
                }
                else if (Program.LoginType.Trim() == "教师")
                {
                    /*try
                    {*/
                    string sql = "Update Teacher set Tname= @usern, Tage=@usera,Tsex=@users,Tdept=@userd,Tlev=@userc,Ttel=@usert Where ID='" + Program.loginID + "'";
                    SqlCommand command = new SqlCommand(sql, sqlConnection);
    
                    SqlParameter sqlParameter = new SqlParameter("@usern", username);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@usera", age);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@users", sex);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@userd", dept);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@userc", Lclass);
                    command.Parameters.Add(sqlParameter);
                    sqlParameter = new SqlParameter("@usert", tel);
                    command.Parameters.Add(sqlParameter);
    
                    command.ExecuteNonQuery();
                    /*}
                    catch (Exception) { MessageBox.Show("数据更新出错"); }*/
                }
                else
                {
                    MessageBox.Show("管理员不配拥有资料哦!");
                    return;
                }
                MessageBox.Show("更改成功!");
                sqlConnection.Close();
                this.Close();
            }
      
            private void pictureBox1_Click(object sender, EventArgs e)
            {
                this.Close();
            }
    
            private void pictureBox2_Click(object sender, EventArgs e)
            {
                this.WindowState = FormWindowState.Minimized;
            }
        }
    }
    

    6、Admin(管理员操作界面)

    在这里插入图片描述

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace Test
    {
        public partial class Admin : Form
        {
            protected override CreateParams CreateParams
            {
                get
                {
                    CreateParams cp = base.CreateParams;
                    cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
                    return cp;
                }
            }
            protected override void WndProc(ref Message m)
            {
                if (m.Msg == 163 && this.ClientRectangle.Contains(this.PointToClient(new Point(m.LParam.ToInt32()))) && m.WParam.ToInt32() == 2)
                    m.WParam = (IntPtr)1;
                base.WndProc(ref m);
                if (m.Msg == 132 && m.Result.ToInt32() == 1)
                    m.Result = (IntPtr)2;
            }
            public Admin()
            {
                InitializeComponent();
            }
    
            private void Admin_Load(object sender, EventArgs e)
            {
                
            }
    
            private void pictureBox1_Click(object sender, EventArgs e)
            {
                MainForm mainForm = new MainForm();
                mainForm.Show();
                this.Close();
            }
    
            private void pictureBox2_Click(object sender, EventArgs e)
            {
                this.WindowState = FormWindowState.Minimized;
            }
            DataTable dt;
            SqlConnection conn;
            private void button2_Click(object sender, EventArgs e)
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                conn = null;
                conn = new SqlConnection(connStr);
                conn.Open();
                try
                {
                    string sql = "SELECT TRIM(ID) as 学号, TRIM(Sname) as 姓名, TRIM(Ssex) as 性别,Sage as 年龄, Trim(Sdept) as 院系, Trim(Sclass) as 班级,Trim(Stel) as 手机号 FROM Student";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    dataGridViewS.DataSource = dt.DefaultView;
                    dataGridViewS.MultiSelect = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
                conn.Close();
            }
    
            private void button4_Click(object sender, EventArgs e)
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                conn = null;
                conn = new SqlConnection(connStr);
                conn.Open();
                try
                {
                    string sql = "SELECT TRIM(ID) as 工号, TRIM(Tname) as 姓名, TRIM(Tsex) as 性别,Tage as 年龄, Trim(Tdept) as 院系, Trim(Tlev) as 职称,Trim(Ttel) as 手机号 FROM Teacher";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    dataGridViewT.DataSource = dt.DefaultView;
                    dataGridViewT.MultiSelect = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
            }
    
            private void button6_Click(object sender, EventArgs e)
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                conn = null;
                conn = new SqlConnection(connStr);
                conn.Open();
                try
                {
                    string sql = "SELECT TRIM(ID) as 学号, TRIM(Cno) as 课序号, Grade as 成绩 FROM SC";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    dataGridViewSC.DataSource = dt.DefaultView;
                    dataGridViewSC.MultiSelect = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
            }
    
            private void button8_Click(object sender, EventArgs e)
            {
                string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
                conn = null;
                conn = new SqlConnection(connStr);
                conn.Open();
                try
                {
                    string sql = "SELECT TRIM(Cno) as 课序号, TRIM(Cname) as 课程名, TRIM(Cpno) as 先修课序号,Ccredit as 学分, Trim(ID) as 工号 FROM Course";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    dataGridViewC.DataSource = dt.DefaultView;
                    dataGridViewC.MultiSelect = false;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询错误!" + ex.Message);
                }
            }
            private void button1_Click(object sender, EventArgs e)
            {
                try
                {
                    DataTable changeDt = dt.GetChanges();
                    foreach (DataRow dr in changeDt.Rows)
                    {
                        string sql = string.Empty;
                        if (dr.RowState == System.Data.DataRowState.Added)
                        {
                            sql = "INSERT INTO Student(ID,Sname,Ssex,Sage,Sdept,Sclass,Stel)" +
                                "VALUES('" + dr["学号"].ToString() + "','" + dr["姓名"].ToString() + "','" + dr["性别"].ToString() + "','" + dr["年龄"].ToString() + "'," +
                                "'" + dr["院系"].ToString() + "','" + dr["班级"].ToString() + "','" + dr["手机号"].ToString() + "')";
                        }
                        else if (dr.RowState == System.Data.DataRowState.Deleted)
                        {
                            sql = "DELETE FROM Student WHERE Student.ID='"+dr["学号",DataRowVersion.Original].ToString()+"'";
                        }
                        else if (dr.RowState == System.Data.DataRowState.Modified)
                        {
                            sql = "UPDATE Student SET Ssex='" + dr["性别"].ToString() + "'," +
                            "Sage='"+Convert.ToInt32(dr["年龄"])+"',Sdept='" + dr["院系"].ToString() + "',Sclass='" + dr["班级"].ToString() + "',Stel='" + dr["手机号"].ToString() + "'" +
                            "WHERE ID='"+ dr["学号"].ToString() +"'";  
                        }
                        SqlCommand comm = new SqlCommand(sql, conn);
                        conn.Open();
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                }
                catch(Exception)
                {
                    MessageBox.Show("保存失败!");
                }
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                try
                {
                    DataTable changeDt = dt.GetChanges();
                    foreach (DataRow dr in changeDt.Rows)
                    {
                        string sql = string.Empty;
                        if (dr.RowState == System.Data.DataRowState.Added)
                        {
                            sql = "INSERT INTO Teacher(ID,Tname,Tsex,Tage,Tdept,Tlev,Ttel)" +
                                "VALUES('" + dr["工号"].ToString() + "','" + dr["姓名"].ToString() + "','" + dr["性别"].ToString() + "','" + dr["年龄"].ToString() + "'," +
                                "'" + dr["院系"].ToString() + "','" + dr["职称"].ToString() + "','" + dr["手机号"].ToString() + "')";
                        }
                        else if (dr.RowState == System.Data.DataRowState.Deleted)
                        {
                            sql = "DELETE FROM Teacher WHERE Teacher.ID='" + dr["工号", DataRowVersion.Original].ToString() + "'";
                        }
                        else if (dr.RowState == System.Data.DataRowState.Modified)
                        {
                            sql = "UPDATE Teacher SET Tsex='" + dr["性别"].ToString() + "'," +
                            "Tage='" + Convert.ToInt32(dr["年龄"]) + "',Tdept='" + dr["院系"].ToString() + "',Tlev='" + dr["职称"].ToString() + "',Ttel='" + dr["手机号"].ToString() + "'" +
                            "WHERE ID='" + dr["工号"].ToString() + "'";
                        }
                        SqlCommand comm = new SqlCommand(sql, conn);
                        conn.Open();
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                }
                catch (Exception)
                {
                    MessageBox.Show("保存失败!");
                }
            }
    
            private void button5_Click(object sender, EventArgs e)
            {
                try
                {
                    DataTable changeDt = dt.GetChanges();
                    foreach (DataRow dr in changeDt.Rows)
                    {
                        string sql = string.Empty;
                        if (dr.RowState == System.Data.DataRowState.Added)
                        {
                            sql = "INSERT INTO SC(ID,Cno,Grade)" +
                                "VALUES('" + dr["学号"].ToString() + "','" + dr["课序号"].ToString() + "','" + dr["成绩"].ToString() + "')";
                        }
                        else if (dr.RowState == System.Data.DataRowState.Deleted)
                        {
                            sql = "DELETE FROM SC WHERE ID='" + dr["学号", DataRowVersion.Original].ToString() + "' AND Cno='"+ dr["课序号"].ToString() + "'";
                        }
                        else if (dr.RowState == System.Data.DataRowState.Modified)
                        {
                            sql = "UPDATE SC SET Grade='" + Convert.ToInt32(dr["成绩"]) + "'" +
                            "WHERE ID='" + dr["学号"].ToString() + "' AND Cno='" + dr["课序号"].ToString() + "'";
                        }
                        SqlCommand comm = new SqlCommand(sql, conn);
                        conn.Open();
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                }
                catch (Exception)
                {
                    MessageBox.Show("保存失败!");
                }
            }
    
            private void button7_Click(object sender, EventArgs e)
            {
                try
                {
                    DataTable changeDt = dt.GetChanges();
                    foreach (DataRow dr in changeDt.Rows)
                    {
                        string sql = string.Empty;
                        if (dr.RowState == System.Data.DataRowState.Added)
                        {
                            sql = "INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID)" +
                                "VALUES('" + dr["课序号"].ToString() + "','" + dr["课程名"].ToString() + "','" + dr["先修课序号"].ToString() + "','" + dr["工号"].ToString() + "')";
                        }
                        else if (dr.RowState == System.Data.DataRowState.Deleted)
                        {
                            sql = "DELETE FROM Course WHERE Cno='" + dr["课序号", DataRowVersion.Original].ToString() + "'";
                        }
                        else if (dr.RowState == System.Data.DataRowState.Modified)
                        {
                            sql = "UPDATE Course SET Cname='" + dr["课程名"].ToString() + "'," +
                            "Ccredit='" + Convert.ToInt32(dr["学分"]) + "',ID='" + dr["工号"].ToString() + "',Cpno='" + dr["先修课序号"].ToString() + "'" +
                            "WHERE Cno='" + dr["课序号"].ToString() + "'";
                        }
                        SqlCommand comm = new SqlCommand(sql, conn);
                        conn.Open();
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                }
                catch (Exception)
                {
                    MessageBox.Show("保存失败!");
                }
            }
        }
    }
    

    7、Program.cs(主要是一些全局变量、函数)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Security.Cryptography;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace Test
    {
        static class Program
        {
            //静态全局变量
            public static string Email163 = "你的邮箱";
            public static string AuthorizationCode = "邮箱授权码";
            public static string loginName;
            public static string realName;
            public static string loginID;
            public static string LoginType;
            public static string photoPath;
            /// <summary>
            /// 应用程序的主入口点。
            /// </summary>
            [STAThread]
            static void Main()
            {
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Application.Run(new LoginForm());
            }
            public static string EncryptWithMD5(string source)
            {
                byte[] sor = Encoding.UTF8.GetBytes(source);
                MD5 md5 = MD5.Create();
                byte[] result = md5.ComputeHash(sor);
                StringBuilder strbul = new StringBuilder(40);
                for (int i = 0; i < result.Length; i++)
                {
                    strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
                }
                return strbul.ToString();
            }
    
    
        }
    }
    

    SQL语句

    1、建表语句

    --EDIT BY FDR_Enterprise_CSDN
    
    DROP TABLE IF EXISTS SC
    DROP TABLE IF EXISTS Student
    DROP TABLE IF EXISTS Course
    DROP TABLE IF EXISTS Teacher
    DROP TABLE IF EXISTS Account
     
    CREATE TABLE Student          
     (	
     ID VARCHAR(30) PRIMARY KEY,        /* 列级完整性约束条件,Sno是主码*/                  
     Sname CHAR(20) UNIQUE,          /* Sname取唯一值*/
     Ssex CHAR(2),
     Sage SMALLINT,
     Sdept CHAR(20),
     Sclass CHAR(20),
     Stel VARCHAR(15)
     ); 
    
     CREATE TABLE Teacher
     (	
     ID VARCHAR(30) PRIMARY KEY,        /* 列级完整性约束条件,Sno是主码*/                  
     Tname CHAR(10) UNIQUE,          /* Sname取唯一值*/
     Tsex NCHAR(2),
     Tage SMALLINT,
     Tdept NCHAR(20),
     Tlev NCHAR(10),
     Ttel VARCHAR(15)
     ); 
     
     CREATE TABLE  Course
     (	
     Cno CHAR(4) PRIMARY KEY,
     Cname CHAR(40),            
     Cpno CHAR(4),               	                      
     Ccredit SMALLINT,
     ID VARCHAR(30),
     FOREIGN KEY (Cpno) REFERENCES  Course(Cno),		/* 表级完整性约束条件, Cpno是外码,被参照表是自身*/
     FOREIGN KEY (ID)   REFERENCES  Teacher(ID)
     );
    
     CREATE TABLE  SC
     (
     ID VARCHAR(30), 
     Cno CHAR(4),  
     Grade SMALLINT,
     PRIMARY KEY (ID,Cno),                      /* 主码由两个属性构成,必须作为表级完整性进行定义*/
     FOREIGN KEY (ID) REFERENCES Student(ID),  /* 表级完整性约束条件,Sno是外码,被参照表是Student*/
     FOREIGN KEY (Cno)REFERENCES Course(Cno)     /* 表级完整性约束条件,Cno是外码,被参照表是Course*/
     ); 
    
     CREATE TABLE Account
     (
     Accounts VARCHAR(30) PRIMARY KEY,
     Passwords VARCHAR(50) NOT NULL,
     ID VARCHAR(30) UNIQUE,
     Type NCHAR(10),
     Email VARCHAR(30),
     Photo VARCHAR(MAX)
     );
    INSERT  INTO  Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215121','李勇','男','CS',20,'1','15800000000');
    INSERT  INTO  Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215122','刘晨','女','CS',19,'2','15800000011');
    INSERT  INTO  Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215123','王敏','女','MA',18,'1','15800000022');
    INSERT  INTO  Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215125','张立','男','IS',19,'3','15800000033');
    INSERT  INTO  Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215128','陈冬','男','IS',20,'2','15800000044');
     
    SELECT * FROM Student
     
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit,ID)	VALUES ('1','数据库',NULL,4,'T200001');
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit,ID)	VALUES ('2','数学',NULL,4,'T200002');
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit,ID)	VALUES ('3','信息系统',NULL,4,'T200003');
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit,ID)	VALUES ('4','操作系统',NULL,4,'T200003');
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit,ID)	VALUES ('5','数据结构',NULL,4,'T200001');
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit,ID)	VALUES ('6','数据处理',NULL,4,'T200001');
    INSERT  INTO Course(Cno,Cname,Cpno,Ccredit,ID)	VALUES ('7','Pascal语言',NULL,4,'T200001');
     
    UPDATE Course SET Cpno = '5' WHERE Cno = '1' 
    UPDATE Course SET Cpno = '1' WHERE Cno = '3' 
    UPDATE Course SET Cpno = '6' WHERE Cno = '4' 
    UPDATE Course SET Cpno = '7' WHERE Cno = '5' 
    UPDATE Course SET Cpno = '6' WHERE Cno = '7' 
     
    SELECT * FROM Course
     
     //可以根据自己的需求多插入几条选课记录
    INSERT  INTO SC(ID,Cno,Grade) VALUES ('201215121 ','1',92);
    INSERT  INTO SC(ID,Cno,Grade) VALUES ('201215121 ','2',85);
    INSERT  INTO SC(ID,Cno,Grade) VALUES ('201215121 ','3',88);
    INSERT  INTO SC(ID,Cno,Grade) VALUES ('201215122 ','2',90);
    INSERT  INTO SC(ID,Cno,Grade) VALUES ('201215122 ','3',80);
     
    SELECT * FROM SC
    
    INSERT INTO Teacher(ID,Tname,Tsex,Tage,Tdept,Tlev,Ttel) VALUES ('T200001','张明','男',32,'CS','讲师','15000000001');
    INSERT INTO Teacher(ID,Tname,Tsex,Tage,Tdept,Tlev,Ttel) VALUES ('T200002','李强','男',32,'62','教授','15000000002');
    INSERT INTO Teacher(ID,Tname,Tsex,Tage,Tdept,Tlev,Ttel) VALUES ('T200003','王敏','女',32,'44','副教授','15000000003');
    
    select *
    from Course
    

    2、视图

    建立学生选的课的视图
    在这里插入图片描述

    3、触发器

    CREATE TABLE SysLog          
     (	
     UserID NCHAR(20) ,                          
     DateAndTime datetime,
     UserOperation NCHAR(200)
     ); 
     IF(OBJECT_ID('regist_recorder') is not null)        -- 判断名为 regist_recorder 的触发器是否存在
    DROP TRIGGER regist_recorder        -- 删除触发器
    GO
    
    CREATE TRIGGER regist_recorder
    ON Account,Student,Teacher,SC,Course  	         
    AFTER
    INSERT
    AS 
    	declare @UserName    nchar(20)
    	declare @DateTime    datetime
    	declare @UserOperation nchar(200)
    
    	select @UserName = system_user
    	select @DateTime = CONVERT(datetime,GETDATE(),120) 
    
    	declare @op varchar(10)
    	select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
                       then 'Update'
                       when exists(select 1 from inserted) and not exists(select 1 from deleted)
                       then 'Insert'
                       when not exists(select 1 from inserted) and exists(select 1 from deleted)
                       then 'Delete' end
                       
    	
    	select @UserOperation = @op
    	
    
    	INSERT INTO SysLog(UserID,DateAndTime,UserOperation)
    	VALUES (@UserName,@DateTime,@UserOperation)
    

    4、存储过程

    DROP TABLE IF EXISTS SC_Avg;
    CREATE TABLE SC_Avg
    (
    Cno CHAR(4),
    Cname CHAR(10),
    Avg_Score FLOAT 
    )
    -----------------------------------------
    INSERT INTO SC_Avg VALUES('1','数据库',0)
    INSERT INTO SC_Avg VALUES('2','数学',0)
    INSERT INTO SC_Avg VALUES('3','信息系统',0)
    INSERT INTO SC_Avg VALUES('4','操作系统',0)
    INSERT INTO SC_Avg VALUES('5','数据结构',0)
    INSERT INTO SC_Avg VALUES('6','数据处理',0)
    INSERT INTO SC_Avg VALUES('7','Pascal语言',0)
    SELECT * FROM SC_Avg
    -------------------
    IF(exists(select * from sys.objects where name='Proc_TRANSFER1'))
    	DROP PROCEDURE Proc_TRANSFER1
    GO
    CREATE PROCEDURE Proc_TRANSFER1
    AS
    BEGIN TRANSACTION TRANS
    	DECLARE 
    	@Avg1 FLOAT,
    	@Avg2 FLOAT,
    	@Avg3 FLOAT,
    	@Avg4 FLOAT,
    	@Avg5 FLOAT,
    	@Avg6 FLOAT,
    	@Avg7 FLOAT;
    	
    	SELECT @Avg1=AVG(Grade)
    	FROM SC
    	WHERE Cno ='1'
    	UPDATE SC_Avg SET Avg_Score=@Avg1 WHERE Cno ='1'
    
    	SELECT @Avg2=AVG(Grade)
    	FROM SC
    	WHERE Cno ='2'
    	UPDATE SC_Avg SET Avg_Score=@Avg2 WHERE Cno ='2'
    
    	SELECT @Avg3=AVG(Grade)
    	FROM SC
    	WHERE Cno ='3'
    	UPDATE SC_Avg SET Avg_Score=@Avg3 WHERE Cno ='3'
    
    	SELECT @Avg4=AVG(Grade)
    	FROM SC
    	WHERE Cno ='4'
    	UPDATE SC_Avg SET Avg_Score=@Avg4 WHERE Cno ='4'
    
    	SELECT @Avg5=AVG(Grade)
    	FROM SC
    	WHERE Cno ='5'
    	UPDATE SC_Avg SET Avg_Score=@Avg5 WHERE Cno ='5'
    
    	SELECT @Avg6=AVG(Grade)
    	FROM SC
    	WHERE Cno ='6'