数据库编程2017

ImComingSCUT

已于 2023-03-23 15:38:40 修改

阅读量135

点赞数

文章标签：数据库 Powered by 金山文档

于 2023-03-15 00:53:03 首次发布

本文链接：https://blog.csdn.net/m0_58835361/article/details/129542321

版权

数据库编程，有三个表：

读者表：卡号，学生姓名，性别

书籍表：书号，书名，出版社

借阅表：卡号，书号，借阅日期

输入学生姓名，能够查出所借书籍和借阅日期

列出书的热度排行榜，需给出书的书名，出版社和借阅次数，并根据借阅次数从多到少排序

打开数据库，Windows验证登录，安全性---登录名----sa----属性---修改密码

sa登录可能遇到报错如下方法解决：

https://www.cnblogs.com/fearless-g/p/16919614.html

建立数据库，文件日志保存到指定文件夹

sql语句速查：https://cnblogs.com/ysblo/p/16757130.html

建表

备份数据库数据库-右键-任务-备份先删除系统默认的备份位置，再创建放到指定文件夹，记得带后缀.bak

create table Reader(
    Rid varchar(20) primary key,
    Name Varchar(20),
    Sex BIT
);
create table Book(
    Bid varchar(20)primary key,
    Name Varchar(50),
    Publisher Varchar(50),
);
create table Brosser(
    Rid varchar(20),
    Bid Varchar(20),
    GetTime Datetime
);

插入数据

use library
go

INSERT INTO Reader Values
('189210125','lzp',1),
('189210126','cyn',2);


INSERT INTO Book Values
('1','钢铁是怎样炼成的','pku'),
('2','数据结构','nju'),
('3','操作系统','nju'),
('4','计算机组成原理','scut'),
('5','计算机网络','zju'),
('6','软件工程','zj');

INSERT INTO Brosser Values
('189210125','2','2022-09-09'),
('189210125','3','2022-08-09'),
('189210126','3','2022-10-09'),
('189210125','4','2022-09-09'),
('189210126','4','2022-11-09'),
('189210125','1','2022-12-09'),
('189210126','5','2022-10-09'),
('189210125','6','2022-09-09'),
('189210126','6','2022-11-09'),
('189210125','5','2022-12-09')

sql查询语句

建立c#窗口程序 .framwork架构

form1.cs
//加入如下命名空间
using System.Data;
using System.Data.SqlClient;

建立数据库连接

//创建连接
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = "REOORO";
scsb.UserID = "sa";
scsb.Password = "19858184859";
scsb.InitialCatalog = "library";

//判断连接是否打开
SqlConnection conn = new SqlConnection(scsb.ToString());//参数是连接数据库的字符串
if(conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}

T-sql语句

Select Book.Bname,Brosser.GetTime from
Reader,Book,Brosser
where Reader.Rid = Brosser.Rid
AND Book.Bid = Brosser.Bid
AND Reader.Name = '"+textBox1.Text.Trim()+"'

string strSQL = "Select Book.Bname,Brosser.GetTime from Reader,Book,Brosser where Reader.Rid = Brosser.Rid AND Book.Bid = Brosser.Bid AND Reader.Name = '" + textBox1.Text.Trim() + "'";
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);//参数一：T-sql语句，参数二：数据库连接对象
//dataset是表的集合  ，可以存储不同的表
//实例化一个dataset
DataSet ds = new DataSet();
da.Fill(ds,"xxx");//参数1：dataset对象；参数二：表名，自定义的名字，不需要和查询的表名一致

绑定数据到datagridview

拖动datagridview之后，背景改白

//绑定数据到DataGriView

//dataGridViewstu为设置的名称

            //方法一
            //dataGridViewstu.DataSource = ds;
            //dataGridViewstu.DataSource = "book";

            //方法二
            //dataGridViewstu.DataSource = ds.Tables["Book"];

            //方法三
            //绑dataset中不同的表，点击查询的结果不同
            //把dt改成全局变量
            //DataTable dt = ds.Tables["Book"];

            dt = ds.Tables["xxx"];
            dataGridViewstu.DataSource = dt.DefaultView;

热度排行点击事件，新建dataGridViewhot和hot button

private void Hot_Click(object sender, EventArgs e)
        {
            //创建连接
            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            scsb.DataSource = "REOORO";
            scsb.UserID = "sa";
            scsb.Password = "19858184859";
            scsb.InitialCatalog = "library";
            //判断连接是否打开
            SqlConnection conn = new SqlConnection(scsb.ToString());//参数是连接数据库的字符串
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }


            string strSQL = @"select Book.Bname,Book.Publisher,count(*) as Times
                                    from Book
                                    inner join Brosser
                                    on Book.Bid = Brosser.Bid
                                    group by Book.Bid,Book.Bname,Book.Publisher
                                    order by Times desc";

            SqlDataAdapter da1 = new SqlDataAdapter(strSQL, conn);

            DataSet ds1 = new DataSet();

            da1.Fill(ds1, "xxxx");

            dt = ds1.Tables["xxxx"];
            dataGridViewhot.DataSource = dt.DefaultView;
        }

增删改待补充

增加页面，一个按钮用于显示select*from 要修改的表另一个按钮用于保存对显示列表的修改

/// <summary>
        /// 保存按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void save_Click_1(object sender, EventArgs e)
        {
            //创建连接
            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            scsb.DataSource = "REOORO";
            scsb.UserID = "sa";
            scsb.Password = "19858184859";
            scsb.InitialCatalog = "2018sj";

            //判断连接是否打开
            SqlConnection conn = new SqlConnection(scsb.ToString());//参数是连接数据库的字符串
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }

            //增加、删除、修改
            //返回的是datatable类型的
            //存储自加载以来对数据表中所做的所有更改
            DataTable changeDt = dt.GetChanges();
            //使用循环逐行读取数据
            foreach (DataRow dr in changeDt.Rows)
            {
                //
                string strSQL = string.Empty;

                //如何判定每一行是什么操作
                //增加
                if (dr.RowState == System.Data.DataRowState.Added)
                {
                    strSQL = @"INSERT INTO students
                            (Stid
                            ,SName
                            ,Sex)
                        VALUES
                            ('" + dr["Stid"].ToString() + @"'
                            ,'" + dr["SName"].ToString() + @"'
                            ,'" + dr["Sex"].ToString() + @"')";
                    MessageBox.Show( "增加成功！");

                }
                //删除操作
                else if (dr.RowState == System.Data.DataRowState.Deleted)
                {
                    //获取已经标记为删除的行的数据
                    //MessageBox.Show (dr["Bid", DataRowVersion.Original].ToString());
                    strSQL = @"DELETE FROM [dbo].[students]                                    
                                          WHERE Stid = '" + dr["Stid", DataRowVersion.Original].ToString() + @"'";
                    MessageBox.Show("删除成功！");
                }
                else if (dr.RowState == System.Data.DataRowState.Modified)
                {
                    strSQL = @"UPDATE [dbo].[students]
                                       SET [Stid] = '"+ dr["Stid"].ToString() + @"'
                                          ,[SName] = '" + dr["SName"].ToString() + @"'
                                          ,[Sex] = '" + dr["Sex"].ToString() + @"'
                                     WHERE Stid = '"+ dr["Stid"].ToString() + "'";
                }

                SqlCommand comm = new SqlCommand(strSQL, conn);
                //返回受影响的行数
                comm.ExecuteNonQuery();


                //显示
                //MessageBox.Show(dr.RowState.ToString());
            }

完整代码

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.Data;
using System.Data.SqlClient;

namespace 数据库        //c#窗体程序名
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        //作为全局变量
        DataTable dt;
        SqlConnection conn;

        /// <summary>
        /// 点击事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSearch_Click(object sender, EventArgs e)
        {
            //创建连接
            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            scsb.DataSource = "REOORO";
            scsb.UserID = "sa";
            scsb.Password = "19858184859";
            scsb.InitialCatalog = "library";

            //判断连接是否打开
            SqlConnection conn = new SqlConnection(scsb.ToString());//参数是连接数据库的字符串
            if(conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }

            //拼接T-SQL语句
            //两张表的情况
            //string strSQL = "SELECT * FROM Book; SELECT * FROM Reader";
            //点查询显示表 百分号是模糊，如果前后都加% 只要bid中包含这个值都查出来 eg：1 ，11
            //如果百分号在前面，前面可以模糊，以后面的条件为结尾
            //百分号在后面，查以什么开始
            //string strSQL = "SELECT * FROM Book where Bid like '" + textBox1.Text.Trim() + "%'";
            string strSQL = "Select Book.Bname,Brosser.GetTime from Reader,Book,Brosser where Reader.Rid = Brosser.Rid AND Book.Bid = Brosser.Bid AND Reader.Name = '" + textBox1.Text.Trim() + "'";
            //string strSQL = "SELECT * FROM Book where Bid = '" + textBox1.Text.Trim() +"'";
            //执行查询语句
            SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);//参数一：T-sql语句，参数二：数据库连接对象
            //dataset是表的集合  ，可以存储不同的表
            DataSet ds = new DataSet();

            da.Fill(ds,"xxx");//参数1：dataset对象；参数二：表名，自定义的名字，不需要和查询的表名一致

            //绑定数据到DataGriView

            //方法一
            //dataGridViewstu.DataSource = ds;
            //dataGridViewstu.DataSource = "book";

            //方法二
            //dataGridViewstu.DataSource = ds.Tables["Book"];

            //方法三
            //绑dataset中不同的表，点击查询的结果不同
            //把dt改成全局变量
            //DataTable dt = ds.Tables["Book"];

            dt = ds.Tables["xxx"];
            dataGridViewstu.DataSource = dt.DefaultView;

            //创建comm对象
            //SqlCommand comm = new SqlCommand(strSQL, conn);//参数一：T-sql语句，参数二：数据库连接对象

        }
        /// <summary>
        /// 保存按钮的单击事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSave_Click(object sender, EventArgs e)
        {
            //增加 删除 修改

            //存储自加载以来对数据表中所做的所有更改
            DataTable changeDT = dt.GetChanges();
            //使用循环逐行读取数据
            foreach(DataRow dr in changeDT.Rows) 
            {
                //
                string strSQL = string.Empty;

                //如何判定每一行是什么操作
                //增加
                if (dr.RowState == System.Data.DataRowState.Added)
                {
                    strSQL = @"INSERT INTO Book
                            (Bid
                            ,Bname
                            ,Publisher)
                        VALUES
                            ('" + dr["Bid"].ToString() + @"'
                            ,'" + dr["Bname"].ToString() + @"'
                            ,'" + dr["Publisher"].ToString() + @"')";
                    
                }
                //删除操作
                else if(dr.RowState == System.Data.DataRowState.Deleted) 
                {
                    //获取已经标记为删除的行的数据
                    //MessageBox.Show (dr["Bid", DataRowVersion.Original].ToString());
                    strSQL = @"DELETE FROM [dbo].[Book]                                    
                                          WHERE Bid = '"+ dr["Bid", DataRowVersion.Original].ToString() + @"'";
                }
                else if(dr.RowState == System.Data.DataRowState.Modified)
                {
                    strSQL = @"UPDATE [dbo].[Book]
                                   SET [Bname] = ‘" + dr["Bname"].ToString() + @"’
                                      ,[Publisher] = ‘" + dr["Publisher"].ToString() + @"’
                                 WHERE Bid = '" + dr["Bid"].ToString() + @"'";
                }

                SqlCommand comm = new SqlCommand(strSQL, conn);
                comm.ExecuteNonQuery();
                
                 
                //显示
                //MessageBox.Show(dr.RowState.ToString());
            }
            
        }

        private void Hot_Click(object sender, EventArgs e)
        {
            //创建连接
            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            scsb.DataSource = "REOORO";
            scsb.UserID = "sa";
            scsb.Password = "19858184859";
            scsb.InitialCatalog = "library";
            //判断连接是否打开
            SqlConnection conn = new SqlConnection(scsb.ToString());//参数是连接数据库的字符串
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }


            string strSQL = @"select Book.Bname,Book.Publisher,count(*) as Times
                                    from Book
                                    inner join Brosser
                                    on Book.Bid = Brosser.Bid
                                    group by Book.Bid,Book.Bname,Book.Publisher
                                    order by Times desc";

            SqlDataAdapter da1 = new SqlDataAdapter(strSQL, conn);

            DataSet ds1 = new DataSet();

            da1.Fill(ds1, "xxxx");

            dt = ds1.Tables["xxxx"];
            dataGridViewhot.DataSource = dt.DefaultView;
        }
    }
}