数据库课程设计_购书管理系统代码（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)
        {

        }
    }
}