C#链接MySQL数据库
学习资料:https://blog.csdn.net/shenqiankk/article/details/99704319
Sql数据库操作语句
学习资料(这个才是mysql数据库的重点):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("修改失败!");
}
}