复制即用-c#-连接MySQL数据库及基础增删改查操作

声明

我很讨厌重复造轮子,所以将很基础的东西贴粗来,不要浪费大家时间.
所有方法简单粗暴,不一定是最好的实现方式,酌情选用及调整.

环境需求

1.(免币) MySql.Data.dll 点击下载
添加到引用即可:

2.mysql-installer-community-8.0.25.0
这个自己到官网下载安装即可,官网地址:
https://www.mysql.com/

实现代码

连接数据库和断开连接

private MySqlConnection conn;

/// <summary>
/// 开启连接
/// </summary>
public void ConnectIt()
{
    string connStr = "data source=localhost;database=world;user id=root;password=root;pooling=true;charset=utf8;";
    conn = new MySqlConnection();
    conn.ConnectionString = connStr;
    conn.Open();
    Console.WriteLine("连接已经成功!用完记得断开连接!");
}

/// <summary>
/// 关闭连接
/// </summary>
public void DisConnectIt()
{
    conn.Close();
    Console.WriteLine("连接已经断开!");
}

查询数据

/// <summary>
/// 查询多条项目
/// </summary>
public void SearchItAll()
{
    string searchStr = "select * from city where ID < 10";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    // 获取结果解析器
    MySqlDataReader reader = cmd.ExecuteReader();
    // 每一次循环都是一条查询到的数据
    while (reader.Read()) 
    {
        //输出第一列字段值
        Console.Write(reader.GetInt32(0) + "\t");

        //判断字段"username"是否为null，为null数据转换会失败
        if (!reader.IsDBNull(1))
        {
            //输出第二列字段值
            Console.Write(reader.GetString(1) + "\t");
        }
        //判断字段"password"是否为null，为null数据转换会失败
        if (!reader.IsDBNull(2))
        {
            //输出第三列字段值
            Console.Write(reader.GetString(2) + "\n");
        }
    }
    Console.WriteLine("查询已经结束!");
}

插入数据

/// <summary>
/// 插入数据
/// </summary>
public void InsertIt()
{
    string searchStr = "Insert into city values (6002,'bigShit','IDN','EastFather',93494)";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.ExecuteNonQuery();
    Console.WriteLine("插入数据结束!");
}

更新数据

/// <summary>
/// 更新一条数据
/// </summary>
public void UpdateIt()
{
    string searchStr = "update city set name='smallShit' where ID=6002";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.ExecuteNonQuery();
    Console.WriteLine("更新数据结束!");
}

删除数据

/// <summary>
/// 删除一条数据
/// </summary>
public void DeleteIt()
{
    string searchStr = "update city set name='smallShit' where ID=6002";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.ExecuteNonQuery();
    Console.WriteLine("更新数据结束!");
}

创建和删除表

/// <summary>
/// 创建一个表
/// </summary>
public void CreateTable()
{
    string searchStr = "create table player(id int(20) primary key, name varchar(40), memo TEXT)";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.ExecuteNonQuery();
    Console.WriteLine("创建数据库结束!");
}

/// <summary>
/// 删除一个表
/// </summary>
public void DropTable()
{
    string searchStr = "drop table player";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.ExecuteNonQuery();
    Console.WriteLine("删除数据库结束!");
}

表主键的添加和删除

/// <summary>
/// 表主键的添加和删除
/// </summary>
/// <param name="remove"></param>
public void AdjustKey(bool remove)
{
    string searchStr = "";
    if (remove)
    {
        // 注:ID这一列本就存在,只是升级为主键
        searchStr = "alter table city add primary key (ID)";
    }
    else
    {
        // 注:ID这一列依然存在,不会因为删除主键而消失
        // 如果主键存在自增,则无法直接删除主键,需要修改其属性 
        // alter table [table] modify [column] [type](len);
        searchStr = "alter table city drop primary key";
    }
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.ExecuteNonQuery();
    Console.WriteLine("主键调整结束!");
}

表新增一列

/// <summary>
/// 给数据库表增加一列
/// </summary>
public void AddColumn()
{
    string searchStr = "alter table city add column funny varchar(50);";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.ExecuteNonQuery();
    Console.WriteLine("新增一列结束!");
}

表修改一列

/// <summary>
/// 修改表的一列属性
/// </summary>
public void ModifyColumn()
{
    string searchStr = "alter table city modify funny int(25)";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.ExecuteNonQuery();
    Console.WriteLine("列调整结束!");
}

补充

附 补充操作的表结构

数据库中NULL值的插入

/// <summary>
/// 插入数据(带null)
/// </summary>
public void InsertItWithNull(int bookId, String chapterName, String chapterContent, int chapterIndex)
{
    String now = DateTime.Now.ToString("yyyy-MM-dd");
    string searchStr = "Insert into chapters value(@id, @bookId, @chapterName, @chapterContent,@now, @chapterIndex)";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    cmd.Parameters.AddWithValue("@id", DBNull.Value); // 注意c#的null和数据库的null不同， 如果赋值null必须使用DBNull.Value 
    cmd.Parameters.AddWithValue("@bookId", bookId);
    cmd.Parameters.AddWithValue("@chapterName", chapterName);
    cmd.Parameters.AddWithValue("@chapterContent", chapterContent);
    cmd.Parameters.AddWithValue("@now", now);
    cmd.Parameters.AddWithValue("@chapterIndex", chapterIndex);
    cmd.ExecuteNonQuery();
}

数据库中自增主键的插入并获取

/// <summary>
/// 插入数据(插入带自增主键的数据，并返回自增数据id)
/// </summary>
public long InsertItReturnId(int bookId, String chapterName, String chapterContent, int chapterIndex)
{
    String now = DateTime.Now.ToString("yyyy-MM-dd");
    string searchStr = "Insert into chapters(book_id, chapter_name, chapter_content,update_date, chapter_index) " +
        "value(@bookId, @chapterName, @chapterContent,@now, @chapterIndex)";
    MySqlCommand cmd = new MySqlCommand(searchStr, conn);
    // 这里存在一个id参数是自增的，如果插入时不定义，系统会自动使其自增填充
    cmd.Parameters.AddWithValue("@bookId", bookId);
    cmd.Parameters.AddWithValue("@chapterName", chapterName);
    cmd.Parameters.AddWithValue("@chapterContent", chapterContent);
    cmd.Parameters.AddWithValue("@now", now);
    cmd.Parameters.AddWithValue("@chapterIndex", chapterIndex);
    cmd.ExecuteNonQuery();
    // 这个返回值就是自增值结果
    return cmd.LastInsertedId;
}