数据库课程设计_购书管理系统代码（sql_c#及sql_java）
已完成两种语言操作sql server的课设，将陆续将代码发布，本人菜鸡，大佬勿喷。
1：几个表的创建，在sql的查询里面写
CREATE TABLE BookCentermessage( --Sno int PRIMARY KEY,--设置主键 BookCentercode varchar(10) NULL, BookCentername varchar(10) NULL, manager varchar(10) NULL, fare int NULL, ) CREATE TABLE department( --Sno int PRIMARY KEY,--设置主键 departmentcode varchar(10) NULL, departmentmanagername varchar(10) NULL, departmentmanagerdirectory varchar(10) NULL, departmentmanagerphone varchar(10) NULL, departmentfare int NULL, ) CREATE TABLE departmentnumber( numbercode varchar(10) PRIMARY KEY, numbername varchar(10) NULL, numberage varchar(10) NULL, numbersex varchar(1) NULL, numberzhiwe varchar(10) NULL, numbersalary int NULL, departmentcode varchar , ) CREATE TABLE book( bookcode varchar(10) PRIMARY KEY, bookname varchar(10) NULL, purchasecosting int NULL, sellfare int NULL, buyfare varchar(10) NULL, departmentcode varchar(10) ,) CREATE TABLE publication( publicationcode varchar(10) NULL, publicationcname varchar(10) NULL, publicationdirectory varchar(10) NULL, publicationphone varchar(10) NULL, book varchar(10), bookname varchar(10), ) CREATE TABLE warehouse( warehousecode varchar(10) NULL, warehousemanagercname varchar(10) NULL, warehousemanagerphone varchar(10) NULL, warehousefare int NULL, bookcoder varchar(10) NULL, ) 2 约束条件 防止一些数据的损坏及方便数据更新，添加约束。 ALTER TABLE departmentnumber
add constraint FK_departmentnumber_department foreign key(departmentcode) references department(departmentcode) ALTER TABLE book
add constraint FK_book_department foreign key(departmentcode) references department(departmentcode) ALTER TABLE book
add constraint FK_book_publication foreign key(bookcode) references publication(bookcode) ALTER TABLE prifit
add constraint FK_prifit_publication foreign key(bookcode) references publication(bookcode) 3 一些基本的查询操作 select sellfare
from book
where buyfare <=30 select numbername,numbercode,numbersalary
from departmentnumber
where numbername IN ('小王') update department set departmentmanagerdirectory='14号楼620' where departmentmanagerdirectory='14号楼619' insert into publication (publicationcode,publicationcname,publicationdirectory, publicationphone,bookcode,bookname) values(04,'一德儿',619,123125,004001,'日语') update book set sellfare=100 where bookname='高数' insert into departmentnumber(numbercode,numbername,numberage,numbersex,numberzhiwe,numbersalary,departmentcode) values(006002,'朱翔龙',20,'T','销售',3000,'005') delete from departmentnumber where numbername='小刘' 4 使用c#创建窗体框进行操作。（没时间水了，直接附代码了） 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 end
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void textBox1_TextChanged(object sender, EventArgs e)
{ } private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{ } private void button1_Click(object sender, EventArgs e)
{
if(textBox1.Text=="")
{
MessageBox.Show("请输入书籍编码");
return;
} string constr = "Data Source=.;Initial Catalog='购书管理系统';Integrated Security=True";
string sqlstr = "select * from book where book.bookcode='"+textBox1.Text+"'";
SqlConnection con = new SqlConnection();
con.ConnectionString = constr;
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
cmd.Connection = con;
SqlDataReader reader;
reader = cmd.ExecuteReader();
DataTable tablel = new DataTable();
tablel.Load(reader);
dataGridView1.DataSource = tablel; }
catch(Exception)
{
Console.WriteLine("错误发生在:", e);
}
finally
{
con.Close();
}
} private void Form1_Load(object sender, EventArgs e)
{ } private void button2_Click(object sender, EventArgs e)
{
string constr = "Data Source=.;Initial Catalog=zhu;Integrated Security=True";
string sqlstr = "SELECT TOP (1000) [bookcode] ,[bookname],[purchasecosting] ,[sellfare],[buyfare],[departmentcode] FROM[购书管理系统].[dbo].[book]";
SqlConnection con = new SqlConnection();
con.ConnectionString = constr;
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
cmd.Connection = con;
SqlDataReader reader;
reader = cmd.ExecuteReader();
DataTable tablel = new DataTable();
tablel.Load(reader);
dataGridView1.DataSource = tablel; }
catch (Exception)
{
Console.WriteLine("错误发生在:", e);
}
finally
{
con.Close();
}
} private void button4_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
{
MessageBox.Show("请输入出版社名称");
return;
} string constr = "Data Source=.;Initial Catalog='购书管理系统';Integrated Security=True";
string sqlstr = "Insert into publication (bookcode) values('" + textBox1.Text + "')";
SqlConnection con = new SqlConnection();
con.ConnectionString = constr;
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
cmd.Connection = con;
SqlDataReader reader;
reader = cmd.ExecuteReader();
DataTable tablel = new DataTable();
tablel.Load(reader);
dataGridView1.DataSource = tablel; }
catch (Exception)
{
Console.WriteLine("错误发生在:", e);
}
finally
{
con.Close();
}
} private void button3_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
{
MessageBox.Show("请输入书籍名称");
return;
} string constr = "Data Source=.;Initial Catalog='购书管理系统';Integrated Security=True";
string sqlstr = "delete from publication where bookname='" + textBox1.Text + "'";
SqlConnection con = new SqlConnection();
con.ConnectionString = constr;
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
cmd.Connection = con;
SqlDataReader reader;
reader = cmd.ExecuteReader();
DataTable tablel = new DataTable();
tablel.Load(reader);
dataGridView1.DataSource = tablel; }
catch (Exception)
{
Console.WriteLine("错误发生在:", e);
}
finally
{
con.Close();
}
} private void button5_Click(object sender, EventArgs e)
{ label2.Text = 0.ToString();
string constr = "Data Source=.;Initial Catalog='购书管理系统';Integrated Security=True";
string sqlstr = "SELECT TOP (1000) [bookcode],[bookfare] ,[buynumber] ,[sellnumber] ,[month]FROM[购书管理系统].[dbo].[prifit]";
SqlConnection con = new SqlConnection();
con.ConnectionString = constr;
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
cmd.Connection = con;
SqlDataReader reader;
reader = cmd.ExecuteReader();
DataTable tablel = new DataTable();
tablel.Load(reader);
dataGridView1.DataSource = tablel;
int sum = 0; int lirun1, lirun,lirun3=20, lirun4=20;
for (int i = 0; i < tablel.Rows.Count; i++)
{
lirun = int.Parse(tablel.Rows[i]["sellnumber"].ToString());
lirun1= int.Parse(tablel.Rows[i]["buynumber"].ToString());
lirun3 = int.Parse(tablel.Rows[i]["bookfare"].ToString());
lirun4 = lirun4 + 10;
sum = sum +lirun*lirun3-lirun1*lirun4;
}
Visible = true;
label2.Text = sum.ToString();
}
catch (Exception)
{
Console.WriteLine("错误发生在:", e);
}
finally
{
con.Close();
}
} private void label1_Click(object sender, EventArgs e)
{ } private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{ }
}
}