使用 C# 操作 MYSQL 数据库的一些常用方法（基础）

在VS中操作 操作 MYSQL 之前需要先下载安装 MYSQL

然后需要引用 MYSQL 的 DLL

引用成功~

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace MySql_操作
{
    class Program
    {
        static void Main(string[] args)
        {
            #region 查询
            //读取指定数据
            //QueryOneData();
            //读取所有数据
            //QueryAllData();
            #endregion 查询

            #region 插入
            //InsertionData();
            #endregion

            #region 删除
            //DeleteData();
            #endregion

            #region 更新
            UpdateData();
            #endregion

            Console.ReadKey();
        }

        /// <summary>
        /// 查询一条
        /// </summary>
        static void QueryOneData()
        {
            string connStr = "Database=datetest;DataSource=localhost;port=3306;user id=root;password=123456";
            //字符串
            MySqlConnection conn = new MySqlConnection(connStr);
            //打开连接
            conn.Open();
            //创建、读取
            MySqlCommand cmd = new MySqlCommand("select * from user where id = 1", conn);
            //执行查询记录
            MySqlDataReader reader = cmd.ExecuteReader();
            //一行一行的读取
            if (reader.HasRows)
            {
                reader.Read();
                string username = reader.GetString("userName");
                string userPsd = reader.GetString("passWord");
                Console.WriteLine(username + ":" + userPsd);
            }
            //关闭流
            reader.Close();
            //数据的连接
            conn.Close();
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        static void QueryAllData()
        {
            string connStr = "Database=datetest;DataSource=localhost;port=3306;user id=root;password=123456";
            //字符串
            MySqlConnection conn = new MySqlConnection(connStr);
            //打开连接
            conn.Open();
            //创建、读取
            MySqlCommand cmd = new MySqlCommand("select * from user", conn);
            //执行查询记录
            MySqlDataReader reader = cmd.ExecuteReader();
            //一行一行的读取
            while (reader.Read())
            {
                string username = reader.GetString("userName");
                string userPsd = reader.GetString("passWord");
                Console.WriteLine(username + ":" + userPsd);
            }
            //关闭流
            reader.Close();
            //数据的连接
            conn.Close();
        }

        /// <summary>
        /// 插入
        /// </summary>
        static void InsertionData()
        {
            string connStr = "Database=datetest;DataSource=localhost;port=3306;user id=root;password=123456";
            //字符串
            MySqlConnection conn = new MySqlConnection(connStr);
            //打开连接
            conn.Open();
            
            string userName = "lsg";
            string passWord = "locker";
            int id = 100;
            //创建、读取
            //MySqlCommand cmd = new MySqlCommand("insert into datetest set userName ='" + userName + "'" + ",passWord'" + passWord + "'", conn);


            MySqlCommand cmd = new MySqlCommand("insert into user set id = @id, userName = @un,passWord = @psw", conn);
            cmd.Parameters.AddWithValue("id", id);
            cmd.Parameters.AddWithValue("un", userName);
            cmd.Parameters.AddWithValue("psw", passWord);


            cmd.ExecuteNonQuery();
            //数据的连接
            conn.Close();
        }

        /// <summary>
        /// 删除
        /// </summary>
        static void DeleteData()
        {
            string connStr = "Database=datetest;DataSource=localhost;port=3306;user id=root;password=123456";
            //字符串
            MySqlConnection conn = new MySqlConnection(connStr);
            //打开连接
            conn.Open();


            MySqlCommand cmd = new MySqlCommand("delete from user where id = @id", conn);
            cmd.Parameters.AddWithValue("id", 100);


            cmd.ExecuteNonQuery();
            //数据的连接
            conn.Close();

        }

        /// <summary>
        /// 更新
        /// </summary>
        static void UpdateData()
        {
            string connStr = "Database=datetest;DataSource=localhost;port=3306;user id=root;password=123456";
            //字符串
            MySqlConnection conn = new MySqlConnection(connStr);
            //打开连接
            conn.Open();
            MySqlCommand cmd = new MySqlCommand("update user set passWord = @psd where id = 3", conn);
            cmd.Parameters.AddWithValue("psd", "zhanzhan");
            cmd.ExecuteNonQuery();
            //数据的连接
            conn.Close();
        }
    }
}

数据库设计