C# 对MySQL数据的操作

怼烎@ 2021-10-13 02:10 341阅读 0赞

## C\#链接MySQL数据库 ##

学习资料：[https://blog.csdn.net/shenqiankk/article/details/99704319][https_blog.csdn.net_shenqiankk_article_details_99704319]

## Sql数据库操作语句 ##

学习资料（这个才是mysql数据库的重点）：[https://blog.csdn.net/shenqiankk/article/details/99704345][https_blog.csdn.net_shenqiankk_article_details_99704345]

--------------------

## 增 ##

**sql添加语句：**  
`insert into <表名>(字段1,字段2,...) values(值1,值2,...)`  
`insert into user(username,password,...) values("王五","789",...)`

**sql传参语句：**  
`insert into user(username,password,...) values(@name,@pwd,...)`

**给sql参数赋值：**  
`cmd.Parameters.Add(new MySqlParameter("@name", "王五"));`  
`cmd.Parameters.Add(new MySqlParameter("@pwd", "789"));`

**sql命令执行：** 返回值为改动成功的行数，返回添加成功的行数  
`int rowsCount = cmd.ExecuteNonQuery();`

//建立MySQL数据库的连接
                string connstr = "data source=localhost;database=test1;user id=root;password=1234;pooling=true;charset=utf8;";
                using(MySqlConnection msc = new MySqlConnection(connstr))
                {
                    //准备添加的数据
                    string username = "王五";
                    string password = "789";
                    //sql传参语句
                    string sql = "insert into user(username,password) values(@username,@password)";
                    //创建mysql命令对象
                    MySqlCommand cmd = new MySqlCommand(sql, msc);
                    //mysql语句传参
                    cmd.Parameters.Add(new MySqlParameter("@username", username));
                    cmd.Parameters.Add(new MySqlParameter("@password", password));
                    //开启mysql数据库连接
                    msc.Open();
                    //执行sql命令，使用ExecuteNonQuery，返回值为改动成功的行数
                    int rowsCount = cmd.ExecuteNonQuery();
                    //判断执行是否成功
                    if (rowsCount == 1)
                    {
                        Console.WriteLine("添加成功！");
                    }
                    else
                    {
                        Console.WriteLine("添加失败！");
                    }
    
                }

--------------------

## 查 ##

**sql查询语句：**  
`select 字段/表达式列表 form <数据表> where <查询条件>`  
`select id form user where username=@username`

**sql多条件查询：**  
`select 字段/表达式列表 form <数据表> where <查询条件1 and/or 查询条件2>`  
`select id form user where username=@username and password=@password`

**sql命令执行：**  
`object id = cmd.ExecuteScalar();` 返回查询结果的首行数据，适合单行数据查询  
`MySqlDataReader reader = cmd.ExecuteReader();` 返回多行查询结果 `reader.Read()` 逐行读取数据，配合循环使用

//建立mysql数据库的连接
                string connstr = "data source=localhost;database=test1;user id=root;password=1234;pooling=true;charset=utf8;";
                using(MySqlConnection msc =new MySqlConnection(connstr))
                {
                    //输入用户名和密码
                    Console.WriteLine("请输入用户名：");
                    string username = Console.ReadLine();
                    Console.WriteLine("请输入密码：");
                    string password = Console.ReadLine();
                    //写sql传参语句
                    string sql = "select id from user where username=@username and password=@password";
                    //创建sql命令对象
                    MySqlCommand cmd = new MySqlCommand(sql, msc);
                    //给sql参数传值
                    cmd.Parameters.Add(new MySqlParameter("@username", username));
                    cmd.Parameters.Add(new MySqlParameter("@password", password));
                    //开启mysql数据库连接
                    msc.Open();
                    //执行sql命令
                    object id = cmd.ExecuteScalar();
                    if (id == null)
                    {
                        Console.WriteLine("用户名密码错误！");
                    }
                    else
                    {
                        Console.WriteLine("登录成功！");
                    }
                }

--------------------

## 删 ##

**sql删除语句：**  
`delete from <数据表> where <查询条件>`  
`delete from user where id=@id`

**sql命令执行：** 返回值为改动成功的行数，返回删除成功的行数。  
`int rowsCount = cmd.ExecuteNonQuery();`

//建立数据库连接
                string connstr = "data source=localhost;database=test1;user id=root;password=1234;pooling=true;charset=utf8;";
                using(MySqlConnection msc = new MySqlConnection(connstr))
                {
                    //写sql参数语句
                    string sql = "delete from user where id=@id";
                    //创建sql命令对象
                    MySqlCommand cmd = new MySqlCommand(sql, msc);
                    //给sql参数传值
                    cmd.Parameters.Add(new MySqlParameter("@id", 3));
                    //开启数据库连接
                    msc.Open();
                    //执行sql命令
                    int rowsCount = cmd.ExecuteNonQuery();
                    if (rowsCount == 1)
                    {
                        Console.WriteLine("删除成功！");
                    }
                    else
                    {
                        Console.WriteLine("删除失败！");
                    }
                }

--------------------

## 改 ##

**sql修改语句：**  
`string sql = "update <数据表> set 字段1=新值1 where <查询条件>";`  
`string sql = "update user set password=@password where username=@username";`

**sql多字段修改语句：**  
`string sql = "update <数据表> set 字段1=新值1,字段2=新值2 where <查询条件>";`  
`string sql = "update user set password=@password,username=@username where id=1";`

**sql命令执行：** 返回值为改动成功的行数，返回删除成功的行数。  
`int rowsCount = cmd.ExecuteNonQuery();`

//建立数据库连接
                string connstr = "data source=localhost;database=test1;user id=root;password=1234;pooling=true;charset=utf8;";
                using(MySqlConnection msc = new MySqlConnection(connstr))
                {
                    //输入数据
                    string username = "李思";
                    string password = "321";
                    //写sql语句
                    string sql = "update user set password=@password where username=@username";
                    //创建命令对象
                    MySqlCommand cmd = new MySqlCommand(sql, msc);
                    //给sql参数传值
                    cmd.Parameters.Add(new MySqlParameter("@username", username));
                    cmd.Parameters.Add(new MySqlParameter("@password", password));
                    //打开数据连接
                    msc.Open();
                    //执行sql命令
                    int rowsCount = cmd.ExecuteNonQuery();
                    if (rowsCount == 1)
                    {
                        Console.WriteLine("修改成功！");
                    }
                    else
                    {
                        Console.WriteLine("修改失败！");
                    }
                }

[https_blog.csdn.net_shenqiankk_article_details_99704319]: https://blog.csdn.net/shenqiankk/article/details/99704319
[https_blog.csdn.net_shenqiankk_article_details_99704345]: https://blog.csdn.net/shenqiankk/article/details/99704345