目录
已经实现的功能
①数据库连接相关相关文档【DAO.cs】
②login:
1.用户/管理员登录
2.登录成功/报错提醒
③管理员:
1.图书管理:增删查改,刷新,当前选中的图书
④用户:
1.系统的“帮助”、退出、“联系管理员”
2.欢迎语
3.图书查看和借阅:实时更新借阅图书的相关信息【pd:不在馆则无法借阅】
4.图书借阅和归还情况:查看已经借阅的图书,并且可以实时归还
DAO.cs
using System.Data.SqlClient; //应该不需要了,因为这个是连sql Serve用的
using MySql.Data.MySqlClient;
namespace WindowsFormsApp1
{
class Dao
{
MySqlConnection sc;
//数据库连接
public MySqlConnection connect()
{
string str = "Database=ksdb;Data Source=localhost;User Id=root;Password=ww8888";//连接数据库的参数
sc = new MySqlConnection(str);//连接数据库
sc.Open();//打开数据库
return sc;//返回数据库连接对象
//SQL Server 的连接方法
/* string str = @"Data Source=LAPTOP-HCLU4VVJ;Initial Catalog=ksdb;Integrated Security=True";//数据库连接字符串
sc = new SqlConnection(str);//创建数据库连接对象
sc.Open();//打开数据库
return sc;//返回数据库连接对象*/
}
public MySqlCommand command(string sql)//sql 是mysql语句 eg:sql = select * from book;
{
MySqlCommand cmd = new MySqlCommand(sql, connect());
return cmd;
}
public int Execute(string sql)// 更新操作 //执行sql语句 ,返回int类型,告诉你受影响的行数 //传入对应的sql语句
{
return command(sql).ExecuteNonQuery();
}
public MySqlDataReader read(string sql)//读取操作 SqlDataReader->MySqlDataReader
{
return command(sql).ExecuteReader();//通过输入的ID和密码讲在数据库里查询出来的结果 显示出来
}
public void DaoClose()
{
sc.Close();//关闭数据库连接
}
}
}
Data.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WindowsFormsApp1
{
class Data
{
public static string UID = "", UName = "";//登录用户的ID和姓名
}
}
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApp1
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new login());
//Application.Run(new admin21());//指定从那个窗体启动
//Application.Run(new admin2());//指定从那个窗体启动
}
}
}
login.cs
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;
namespace WindowsFormsApp1
{
public partial class login : Form
{
public login()
{
InitializeComponent();
}
private void label2_Click(object sender, EventArgs e)
{
}
private void label3_Click(object sender, EventArgs e)
{
}
private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
//登录 首先判断是否为空
if(textBoxID.Text!=""&&textBoxPassWord.Text!="")
{
Login();
}
else
{
MessageBox.Show("鼠鼠我呀,不喜欢ID或者PassWord是空的嘞");//此处的弹出框可以弄的很炫,图标啥 的,通过输入参数实现
}
}
//登录方法,验证是否允许登录,允许返回真
public void Login()
{
//用户
if(radioButtonUser.Checked==true)
{
Dao dao = new Dao();
//sql 写法1
//string sql = "SELECT * FROM ks_reader WHERE rdID='"+textBoxID.Text+"' AND rdPwd='"+textBoxPassWord.Text+"';";//验证是否允许登录,PT:这里的sql语句是从navicat(sql编译器)上copy下来的
//sql 写法2 //sql 写法2
//string sql2 = string.Format("SELECT * FROM ks_reader WHERE rdID='{0}' AND rdPwd='{1}';",textBoxID.Text,textBoxPassWord.Text);
//sql 写法3
string sql = $"SELECT * FROM ks_reader WHERE rdID='{textBoxID.Text}' AND rdPwd='{textBoxPassWord.Text}';" ;
//MessageBox.Show(sql);//判断是否出错,弄一个弹窗,看自己login写对没有
IDataReader dc = dao.read(sql);//将输入的ID,密码 所形成的sql 语句输入,以此进行查询
/* dc.Read();//读取一行数据
MessageBox.Show(dc[0].ToString(),dc["rdName"].ToString());*///①将读取的第0行数据显示(从第0行开始计数) ②如果只有这一行代码,那么查询结果为空时会报错
if(dc.Read())//考虑到会因为查询结果(返回值)为空而出的改进,使当查询结果为空时出现可爱的报错
{
/* Data.UID = dc["id"].ToString();
Data.UName = dc["name"].ToString();*/
Data.UID = dc["rdID"].ToString();//这里的idID是你自己数据库给读者ID的命名
Data.UName = dc["rdName"].ToString();
string login_wel = "吹灭读书灯,一身都是月\n欢迎:";
MessageBox.Show(login_wel+dc[0].ToString()+dc["rdName"].ToString());
//return true;
//窗体跳转
user1 user = new user1();//用户登录后,弹出用户登录的窗口
this.Hide();//隐藏原来的login窗体,this 指登录(login)窗体
user.ShowDialog();//这里如果是user.show()的话,虽然也会弹出窗体,但是对于由这种形式弹出的窗体,会使在user窗体出现后仍然能够对login窗体操作,这样会使一个login窗体同时登录多个用户,不好。同时使用showdialog()也可以避免很多奇奇怪怪的bug
this.Show();//当对话框窗体关掉后,就显示登录(login)窗体
}
else
{
string id_or_pwd_wrong = "鼠鼠我呀,觉得你应该是输入错误呢";
MessageBox.Show(id_or_pwd_wrong);
//return false;
}
dao.DaoClose();//关闭数据库连接
}
//管理员
if(radioButtonAdmin.Checked==true)
{
Dao dao = new Dao();
string sql = $"SELECT * FROM ks_admin WHERE adID='{textBoxID.Text}' AND adPwd='{textBoxPassWord.Text}';";
IDataReader dc = dao.read(sql);//将输入的ID,密码 所形成的sql 语句输入,以此进行查询
if (dc.Read())//考虑到会因为查询结果(返回值)为空而出的改进,使当查询结果为空时出现可爱的报错
{
string login_wel = "书卷多情似故人,晨昏忧乐每相亲\n欢迎:";
MessageBox.Show(login_wel + dc[0].ToString() + dc["adName"].ToString());
//return true;
//窗体跳转
admin1 ad = new admin1();//用户登录后,弹出用户登录的窗口
this.Hide();//隐藏原来的login窗体,this 指登录(login)窗体
ad.ShowDialog();
this.Show();//当对话框窗体关掉后,就显示登录(login)窗体
}
else
{
string id_or_pwd_wrong = "鼠鼠我呀,觉得你应该是输入错误呢";
MessageBox.Show(id_or_pwd_wrong);
//return false;
}
dao.DaoClose();//关闭数据库连接
}
//return true;
//MessageBox.Show("鼠鼠我呀,觉得单选框没选呢");//两个radioButton(用户,管理员)都失败了,即单选框失效 其实这两行不会生效,因为我前面已经默认选了user的radioButton为true了
//return false;//两个radioButton(用户,管理员)都失败了,即单选框失效
}
private void textBoxPassWord_TextChanged(object sender, EventArgs e)
{
}
private void login_Load(object sender, EventArgs e)
{
}
}
}
admin.cs
①admin1.cs【管理员主页面】
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;
namespace WindowsFormsApp1
{
public partial class admin1 : Form
{
public admin1()
{
InitializeComponent();
}
private void 图书管理ToolStripMenuItem_Click(object sender, EventArgs e)
{
admin2 admin = new admin2();
admin.ShowDialog();
}
private void admin1_Load(object sender, EventArgs e)
{
}
}
}
②admin2.cs【图书管理页面】
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;
namespace WindowsFormsApp1
{
public partial class admin2 : Form
{
public admin2()//admin2的构造方法,实例化的时候就会执行,即new admin2()的时候就执行了
{
InitializeComponent();
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void admin2_Load(object sender, EventArgs e)//当整个窗体构建完了后就会执行这个函数
{
this.Width = 1000;//修改运行时显示的界面大小
this.Height = 550;
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
Table();
label_chose.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString()+ dataGridView1.SelectedRows[0].Cells[1].Value.ToString();//获取书号/选中行数的第0行的 第0个单元格 它的值转换成字符串
}
//从数据库读取数据显示在表格控件中
public void Table()
{
dataGridView1.Rows.Clear();//将控件中已经有的旧数据全部清空
Dao dao = new Dao();
string sql = "SELECT * FROM ks_book;";//要执行的sql语句,建议从编译器上copy该语句再黏贴到此处
IDataReader dc = dao.read(sql);
while(dc.Read())//当查询结果到达末尾后跳出while循环
{
//将读到的数据添加到dataGridView控件中
//这里几个dc[]取决于你在页面的那里设置了几个属性
dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString(), dc[5].ToString(), dc[6].ToString(), dc[7].ToString());
}
dc.Close();
dao.DaoClose();
}
private void button_add_book_Click(object sender, EventArgs e)
{
admin21 a = new admin21();//连接admin2【图书管理页面】和admin21【添加图书】,实现界面跳转
a.ShowDialog();
}
private void button_delete_book_Click(object sender, EventArgs e)
{
try//防止索引超出范围,这里就用try...catch
{
string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取书号/选中行数的第0行的 第0个单元格 它的值转换成字符串
label_chose.Text=id+ dataGridView1.SelectedRows[0].Cells[1].Value.ToString();//显示正在选中的书号+书名
DialogResult dr = MessageBox.Show("多笋啊~你真的想删书咩?", "消息提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if(dr==DialogResult.OK)
{
string sql = $"DELETE FROM ks_book WHERE bkCode= '{id}';";
Dao dao = new Dao();
if(dao.Execute(sql)>0)
{
MessageBox.Show("滴!知识的财富-1");
Table();
}
else
{
MessageBox.Show("鼠鼠我呀,删除失败了呢~" + sql);
}
dao.DaoClose();
}
}
catch (Exception)
{
MessageBox.Show("鼠鼠我呀,觉得你连想删除的都还没有选中呢", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void dataGridView1_Click(object sender, EventArgs e)
{
label_chose.Text= dataGridView1.SelectedRows[0].Cells[0].Value.ToString()+ dataGridView1.SelectedRows[0].Cells[1].Value.ToString();//获取书号/选中行数的第0行的 第0个单元格 它的值转换成字符串
}
private void button_alter_book_Click(object sender, EventArgs e)
{
try//双击tab 可直接补全
{
string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string name = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
string author = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
string press = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
string ISBN = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
string catalog = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
string price = dataGridView1.SelectedRows[0].Cells[6].Value.ToString();
string status = dataGridView1.SelectedRows[0].Cells[7].Value.ToString();
admin22 a2 = new admin22(id,name,author,press,ISBN,catalog,price,status);//连接admin2【图书管理页面】和admin22【修改图书信息】,实现界面跳转
a2.ShowDialog();
Table();//刷新数据
}
catch (Exception)
{
MessageBox.Show("鼠鼠我呀,不知道你哪里错了,\n但是肯定的是现在修改不了啦\n\t哈哈哈~~");
}
}
//从数据库根据书号读取数据 显示在表格控件中
public void TableID()
{
dataGridView1.Rows.Clear();//将控件中已经有的旧数据全部清空
Dao dao = new Dao();
string sql = $"SELECT * FROM ks_book WHERE bkCode='{textBox_bkNum_find.Text}';";//要执行的sql语句,建议从编译器上copy该语句再黏贴到此处
IDataReader dc = dao.read(sql);
while (dc.Read())//当查询结果到达末尾后跳出while循环
{
//将读到的数据添加到dataGridView控件中
//这里几个dc[]取决于你在页面的那里设置了几个属性
dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString(), dc[5].ToString(), dc[6].ToString(), dc[7].ToString());
}
dc.Close();
dao.DaoClose();
}
//书号查询
private void button_bkNum_find_Click(object sender, EventArgs e)
{
textBox_bkName_find.Text = "";//查询时,若书名查询有内容,将其为空
TableID();
}
//从数据库根据书名读取数据 显示在表格控件中 模糊查询
public void TableName()
{
dataGridView1.Rows.Clear();//将控件中已经有的旧数据全部清空
Dao dao = new Dao();
string sql = $"SELECT * FROM ks_book WHERE bkName LIKE '%{textBox_bkName_find.Text}%';";//要执行的sql语句,建议从编译器上copy该语句再黏贴到此处
IDataReader dc = dao.read(sql);
while (dc.Read())//当查询结果到达末尾后跳出while循环
{
//将读到的数据添加到dataGridView控件中
//这里几个dc[]取决于你在页面的那里设置了几个属性
dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString(), dc[5].ToString(), dc[6].ToString(), dc[7].ToString());
}
dc.Close();
dao.DaoClose();
}
//书名查询 模糊查询
private void button_bkName_find_Click(object sender, EventArgs e)
{
textBox_bkNum_find.Text = "";//查询时,若书号查询有内容,将其为空
TableName();
}
private void button_flash_book_Click(object sender, EventArgs e)
{
textBox_bkNum_find.Text = "";//刷新时,若书名/号查询有内容,将其为空
textBox_bkName_find.Text = "";
Table();
}
}
}
1.添加图书
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;
namespace WindowsFormsApp1
{
public partial class admin21 : Form
{
public admin21()
{
InitializeComponent();
}
private void admin21_Load(object sender, EventArgs e)
{
}
private void button_add_book_Click(object sender, EventArgs e)//添加图书按钮的事件
{
if(textBox1.Text!=""&& textBox2.Text != "" )
{
Dao dao = new Dao();
textBox8.Text = "";
textBox7.Text = "0";
string sql = $"INSERT INTO ks_book VALUES('{textBox1.Text}','{textBox2.Text}','{textBox3.Text}','{textBox4.Text}','{textBox5.Text}','{textBox6.Text}',{textBox7.Text},'{textBox8.Text}');";
int n = dao.Execute(sql);//dao.Execute(sql)返回受影响的行数
if (n > 0)//受影响行数大于1,即添加成功
{
MessageBox.Show("Added successfully!");
}
else//受影响行数小于1,即添加失败
{
MessageBox.Show("I am sorry,but you fail~~");
}
//添加成功后清空界面框
//法1 遍历所有TextBox,将其赋值为空
foreach (System.Windows.Forms.Control control in this.Controls)
{
if (control is System.Windows.Forms.TextBox)
{
System.Windows.Forms.TextBox tb = (System.Windows.Forms.TextBox)control;
tb.Text = String.Empty;
}
}
//法2 一条一条写,赋值为空
/*textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox7.Text = "";
textBox8.Text = "";*/
}
else
{
MessageBox.Show("鼠鼠我呀,不喜欢输入为空呢");
}
}
private void button_empty_Click(object sender, EventArgs e)
{
//点击“取消”button后 清空界面框
foreach (System.Windows.Forms.Control control in this.Controls)
{
if (control is System.Windows.Forms.TextBox)
{
System.Windows.Forms.TextBox tb = (System.Windows.Forms.TextBox)control;
tb.Text = String.Empty;
}
}
}
}
}
2.修改图书
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;
namespace WindowsFormsApp1
{
public partial class admin22 : Form
{
string ID = "";//ID是主键,用于修改检索目标课本
public admin22()
{
InitializeComponent();
}
public admin22(string id,string name,string author,string press,string ISBN,string catalog,string price,string status)//这里的8个参数是数据库表book的8个属性
{
InitializeComponent();
//传值
ID=textBox1.Text = id;
textBox2.Text = name;
textBox3.Text = author;
textBox4.Text = press;
textBox5.Text = ISBN;
textBox6.Text = catalog;
textBox7.Text = price;
textBox8.Text = status;
}
private void button_alter_book_Click(object sender, EventArgs e)//调用MySQL语句,开始修改
{
//pt:下面的sql语句是直接从编译器navicat上C V的
string sql = $"UPDATE ks_book SET bkCode='{textBox1.Text}',bkName='{textBox2.Text}',bkAuthor='{textBox3.Text}',bkPress='{textBox4.Text}',bkISBN='{textBox5.Text}',bkCatalog='{textBox6.Text}',bkPrice={textBox7.Text},bkStatus='{textBox8.Text}' WHERE bkCode='{ID}';";
Dao dao = new Dao();
if(dao.Execute(sql)>0)
{
MessageBox.Show("Alter successfully");
this.Close();
}
}
private void button_empty_Click(object sender, EventArgs e)
{
//点击“取消”button后 清空界面框
foreach (System.Windows.Forms.Control control in this.Controls)
{
if (control is System.Windows.Forms.TextBox)
{
System.Windows.Forms.TextBox tb = (System.Windows.Forms.TextBox)control;
tb.Text = String.Empty;
}
}
}
private void admin22_Load(object sender, EventArgs e)
{
}
}
}
User
① user1.cs【用户主界面】
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;
namespace WindowsFormsApp1
{
public partial class user1 : Form
{
public user1()
{
InitializeComponent();
label_user_lg_wel.Text = $"欢迎{Data.UName}登陆";
}
private void 图书借阅ToolStripMenuItem_Click(object sender, EventArgs e)
{
user2 u2 = new user2();
u2.Show();
}
private void 当前借阅和归还情况ToolStripMenuItem_Click(object sender, EventArgs e)
{
user3 u3 = new user3();
u3.Show();
}
private void 帮助ToolStripMenuItem_Click(object sender, EventArgs e)
{
//先随便弹个窗意思意思
MessageBox.Show("V鼠鼠50,鼠鼠帮你解决障碍");
}
private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
{
this.Close();
}
private void user1_Load(object sender, EventArgs e)
{
}
}
}
② user2.cs【查看和借阅】
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;
namespace WindowsFormsApp1
{
public partial class user2 : Form
{
public user2()
{
InitializeComponent();
Table();
}
private void user2_Load(object sender, EventArgs e)
{
}
public void Table()
{
dataGridView1.Rows.Clear();//将控件中已经有的旧数据全部清空
Dao dao = new Dao();
string sql = "SELECT * FROM ks_book;";//要执行的sql语句,建议从编译器上copy该语句再黏贴到此处
IDataReader dc = dao.read(sql);
while (dc.Read())//当查询结果到达末尾后跳出while循环
{
//将读到的数据添加到dataGridView控件中
//这里几个dc[]取决于你在页面的那里设置了几个属性
dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString(), dc[4].ToString(), dc[5].ToString(), dc[6].ToString(), dc[7].ToString());
}
dc.Close();
dao.DaoClose();
}
private void button_lend_bk_Click(object sender, EventArgs e)//还书button
{
string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取书号
string bkName = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();//获取书号
string ku_chun= dataGridView1.SelectedRows[0].Cells[7].Value.ToString();//获取库存,即是否在馆
if(ku_chun=="在馆")
{
string sql = $"INSERT INTO ks_borrow VALUES({Data.UID},{id},'{bkName}',CURDATE());UPDATE ks_book SET bkStatus='不在' WHERE bkCode='{id}';";//更新borrow、book.status表
Dao dao = new Dao();
if (dao.Execute(sql) > 1)
{
MessageBox.Show($"Congratulations {Data.UName}:\nhas borrowed {bkName}!");
Table();
}
else
{
MessageBox.Show("鼠鼠我呀\n觉得你的sql语句可能错了呢\n所以借书失败了哦");
}
}
else
{
MessageBox.Show("鼠鼠我呀,没有书了呢\n这边建议您联系管理员哈\n虽然可能联系不到\n因为他应该在某一个小角落看书呢");
}
}
}
}
③ user3.cs【归还】
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;
namespace WindowsFormsApp1
{
public partial class user3 : Form
{
public user3()
{
InitializeComponent();
Table();
}
private void user3_Load(object sender, EventArgs e)
{
}
//从数据库读取数据显示在表格控件中
public void Table()
{
dataGridView1.Rows.Clear();//将控件中已经有的旧数据全部清空
Dao dao = new Dao();
string sql = $"SELECT * FROM KS_Borrow WHERE rdID='{Data.UID}';";//要执行的sql语句,建议从编译器上copy该语句再黏贴到此处
IDataReader dc = dao.read(sql);
while (dc.Read())//当查询结果到达末尾后跳出while循环
{
//将读到的数据添加到dataGridView控件中
//这里几个dc[]取决于你在页面的那里设置了几个属性
dataGridView1.Rows.Add(dc[0].ToString(), dc[1].ToString(), dc[2].ToString(), dc[3].ToString());
}
dc.Close();
dao.DaoClose();
}
private void button1_Click(object sender, EventArgs e)//还书Button
{
string id = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();//获取书号
string bkName = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();//获取书号
//还书时间:
string sql = $"DELETE FROM ks_borrow WHERE bkID= '{id}';UPDATE ks_book SET bkStatus='在馆' WHERE bkCode='{id}';";//用图书ID 更新borrow、book.status表
Dao dao = new Dao();
if (dao.Execute(sql) > 1)
{
MessageBox.Show($"Congratulations {Data.UName}:\nhas return {bkName}!");
Table();
}
else
{
MessageBox.Show("鼠鼠我呀\n觉得你的sql语句可能错了呢\n所以还书失败了哦");
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}
MySQL all代码
-- 一、选择数据库
USE ksdb;
-- 二、创建数据表
-- 1、创建读者类别表并插入测试数据
CREATE TABLE KS_ReaderType
(
rdType SMALLINT PRIMARY KEY, -- 读者类别
rdTypeName VARCHAR(20) NOT NULL UNIQUE, -- 读者姓名
CanLenQty int, -- 可借书数目
CanLendDay int, -- 可借书天数
CanContinueTimes int,
PunishRate float, -- 惩罚钱
DateValid SMALLINT DEFAULT(0) -- 有效日期
)
show TABLES; -- 查看所有表
DESC KS_ReaderType; -- 查看数据表的结构
INSERT INTO KS_ReaderType VALUES
(10,'教师',12,60,2,0.05,0),
(20,'本科生',8,30,1,0.05,4),
(21,'专科生',8,30,1,0.05,3),
(30,'硕士研究生',8,30,1,0.05,3),
(31,'博士研究生',8,30,1,0.05,4);
SELECT * FROM KS_ReaderType;
-- 2、创建读者表并插入测试数据
CREATE TABLE KS_Reader
(
rdID int PRIMARY KEY,
rdName VARCHAR(20),
rdSex CHAR(1),
rdType SMALLINT REFERENCES KS_ReaderType(rdType),
rdDept VARCHAR(20),
rdPhone VARCHAR(25), -- 1
rdEmail VARCHAR(25), -- 1
-- rdDateReg datetime DEFAULT getdate(),
-- rdPhoto image,
rdStatus CHAR(2) DEFAULT'有效', -- 1
rdBorrowQty int DEFAULT 0, -- 1
rdPwd VARCHAR(20),
rdAdminRoles SMALLINT DEFAULT 0
)
show TABLES; -- 查看所有表
DESC KS_Reader; -- 查看数据表的结构
INSERT INTO ks_reader(rdID,rdName,rdPwd,rdType,rdDept,rdAdminRoles)VALUES
(1,'路飞','123',10,'图书馆',8), -- 系统管理员
(2,'索隆','123',10,'图书馆',1), -- 读者(借书证)管理员
(3,'乔巴','123',10,'图书馆',2), -- 图书管理员
(4,'娜美','123',10,'图书馆',4), -- 接还书管理员
(5,'乌索普','123',10,'图书馆',6), -- 图书、接还书管理员
(6,'香吉士','123',10,'图书馆',7), -- 读者、图书、接还书管理员
(7,'骷髅','123',10,'图书馆',15); -- 读者、图书、接还书、系统管理员
SELECT * FROM ks_reader;
INSERT INTO ks_reader(rdID,rdName,rdPwd,rdSex,rdType,rdDept)VALUES
(100,'白胡子','123','男',10,'计科院'), -- 读者,教师
(101,'红发','123','男',20,'计科11001班'), -- 读者,本科生
(102,'凯多','123','男',30,'硕计11201班'), -- 读者,硕士研究生
(103,'夏洛特.玲玲','123','女',20,'计科11001班'); -- 读者,本科生
-- 3、创建图书表并插入测试数据
-- bkCode,bkName,bkAuthor,bkPress,bkISBN,bkCatalog,bkPrice,bkStatus
CREATE TABLE KS_Book
(
-- bkID int identity(1,1)PRIMARY KEY,
bkCode VARCHAR(20)NOT NULL,
bkAuthor VARCHAR(30),
bkPress VARCHAR(50),
-- bkDatePress datetime,
bkISBN VARCHAR(15),
bkCatalog VARCHAR(30),
-- bkLanguage smallint,
-- bkPages int,
bkPrice FLOAT,
-- bkDateln datetime DEFAULT getdate(),
-- bkBrief text,
-- bkCover image,
bkStatus char(2) DEFAULT'在馆'
)
show TABLES; -- 查看所有表
DESC KS_Book; -- 查看数据表的结构
INSERT INTO ks_book VALUES
(1000,'如何成为海贼王','罗杰','集英社','1-111-1','TP312/12-3',59.0,NULL),
(1001,'如何成为海贼王','罗杰','集英社','1-111-1','TP312/12-3',59.0,NULL),
(1002,'如何成为海贼王','罗杰','集英社','1-111-1','TP312/12-3',59.0,NULL),
(1003,'迷','龙','革命军',NULL,NULL,59.0,NULL),
(1004,'迷','龙','革命军',NULL,NULL,59.0,NULL),
(1005,'迷','龙','革命军',NULL,NULL,59.0,NULL),
(1006,'迷','龙','革命军',NULL,NULL,59.0,NULL);
SELECT * FROM ks_book;
UPDATE ks_book SET bkStatus = '在馆';
-- VS图书插入测试
-- bkCode,bkName,bkAuthor,bkPress,bkISBN,bkCatalog,bkPrice,bkStatus
INSERT INTO ks_book VALUES(1007,'VStest','龙','革命军',NULL,NULL,59.0,'在馆');
SELECT * FROM ks_book;
DELETE FROM ks_book WHERE bkCode= '1006';
SELECT * FROM ks_book;
-- VS图书修改测试
-- bkCode,bkName,bkAuthor,bkPress,bkISBN,bkCatalog,bkPrice,bkStatus
INSERT INTO ks_book VALUES(1007,'VStest','龙','革命军',NULL,NULL,59.0,'在馆');
UPDATE ks_book SET bkCode='1008',bkName='VStest',bkAuthor='龙',bkPress='革命军',bkISBN='',bkCatalog='',bkPrice=99,bkStatus='在馆' WHERE bkCode='1007';
SELECT * FROM ks_book;
-- VS书号查询测试
SELECT * FROM ks_book WHERE bkCode='1001';
-- VS书名查询测试 模糊查询
SELECT * FROM ks_book WHERE bkName LIKE '%海%';
-- 4、创建借阅表
CREATE TABLE KS_Borrow
(
-- BorrowID numeric(12,0) IDENTIFIED(1,1)PRIMARY KEY,
rdID int not null REFERENCES ks_reader(rdID),-- 读者ID
bkID int not null REFERENCES ks_book(bkID),-- 书籍ID
idContinueTimes int, -- 可借阅时间
idDateOut datetime not null, -- 借出的日期
idDateRetPlan datetime, -- 预计归还日期
idDateReAct datetime, -- 实际归还日期
idOverDay int, -- 延时
idOverMoney DOUBLE,
idPunishMoney DOUBLE, -- 罚钱数
isHasReturn bit,
OperatorLend VARCHAR(20),
OperatorRet VARCHAR(20),
)
SELECT * FROM ks_reader WHERE rdID='100' AND rdPwd='123';
SELECT * FROM KS_Borrow;
-- VS借/还书表的测试
DROP TABLE ks_borrow;
CREATE TABLE KS_Borrow
(
rdID int not null REFERENCES ks_reader(rdID),-- 读者ID
bkID int not null REFERENCES ks_book(bkID),-- 书籍ID
bkName VARCHAR(50) REFERENCES ks_book(bkName),-- 书名
idDateOut date not null -- 借出的日期
)
SELECT * FROM KS_Borrow;
SELECT * FROM ks_reader;
SELECT * FROM ks_book;
show TABLES; -- 查看所有表
DESC ks_borrow; -- 查看数据表的结构
INSERT INTO ks_borrow VALUES(1,1001,'如何成为海贼王',CURDATE());
UPDATE ks_book SET bkStatus='不在' WHERE bkCode='1001';
-- 还书
DELETE FROM ks_borrow WHERE bkID= '1002';UPDATE ks_book SET bkStatus='在馆' WHERE bkCode='1002';
SELECT * FROM KS_Borrow WHERE rdID='1';
-- 5、创建管理员表并插入测试数据
CREATE TABLE KS_admin
(
adID int PRIMARY KEY,
adName VARCHAR(20),
adSex CHAR(1),
-- adType SMALLINT REFERENCES KS_ReaderType(rdType),
-- adDept VARCHAR(20),
-- adPhone VARCHAR(25), -- 1
-- adEmail VARCHAR(25), -- 1
-- rdDateReg datetime DEFAULT getdate(),
-- rdPhoto image,
-- adStatus CHAR(2) DEFAULT'有效', -- 1
-- adBorrowQty int DEFAULT 0, -- 1
adPwd VARCHAR(20)
-- adAdminRoles SMALLINT DEFAULT 0
)
show TABLES; -- 查看所有表
DESC KS_admin; -- 查看数据表的结构
-- 把四皇弄成管理员
DELETE FROM ks_reader WHERE rdID=100;
DELETE FROM ks_reader WHERE rdID=101;
DELETE FROM ks_reader WHERE rdID=102;
DELETE FROM ks_reader WHERE rdID=103;
SELECT * FROM ks_reader;
INSERT INTO ks_admin VALUES
(10000,'白胡子','男','123'), --
(10001,'红发','男','123'), --
(10002,'凯多','男','123'), --
(10003,'夏洛特.玲玲','女','123'); --
SELECT * FROM ks_admin;
SELECT * FROM ks_admin WHERE adID='10000' AND adPwd='123';