MySQL 数据库、表的创建、导出与增删改查
- 1.数据库创建
- 2.创建表
- 3.表数据导出
- 4.使用C#代码进行增删改查
- 4.0 环境配置
- 4.1 使用的接口解释
- 4.1.1 MySqlConnection 建立与SQL数据库的链接
- 4.1.2 MySqlCommand 将SQL语句传入MySQL Server并执行
- 4.1.3 MySqlDataReader 读取查询返回的数据
- 4.2 增加(insert)
- 4.3 删除(delete)
- 4.4 修改(Update)
- 4.5 查找(Select)
1.数据库创建
在MySQL Workbench上创建数据库
(1)建立与MySQL服务器的连接
(2)输入密码
(3)点击图片中的图标,创建一个数据库
后面按照提示点击即可
2.创建表
在第一个红箭头处添加字段,设置类型;第二个红箭头处设置属性;第三个红箭头处应用配置
点击图标进入表编辑视图
3.表数据导出
选择数据库,选择表,配置路径,开始导出
4.使用C#代码进行增删改查
4.0 环境配置
使用NuGet导出库MySql.Data
4.1 使用的接口解释
4.1.1 MySqlConnection 建立与SQL数据库的链接
(1)生成对象
MySqlConnection connect= new MySqlConnection(" server=127.0.0.1;port=3306;user=root;password=root;database =user;");
(2)建立连接
connect.Open();
(3)断开连接
connect.Close();
4.1.2 MySqlCommand 将SQL语句传入MySQL Server并执行
(1)生成对象
string sql = "select * from user ";
MySqlCommand cmd = new MySqlCommand(sql, connect);
(2)执行
执行方法有四种:
(1)执行增删改时使用,同步
int resultCode=cmd.ExecuteNonQuery();
(2)执行增删改时使用,异步
IAsyncResult result=cmd.BeginExecuteNonQuery();
int resultCode=cmd.EndExecuteNonQuery(result);
(3)执行查时使用,同步
MySqlDataReader dataReader=cmd.ExecuteReader();
(4)执行查时使用,异步
IAsyncResult result=cmd.BeginExecuteReader();
MySqlDataReader dataReader=cmd.EndExecuteReader(result);
4.1.3 MySqlDataReader 读取查询返回的数据
(1)读取数据
while (dataReader.Read())
{
Console.Log(dataReader.GetInt32("userid") + dataReader.GetString("username") + dataReader.GetString("password"));
}
registerReader.Close();
4.2 增加(insert)
/// <summary>
/// 插入数据
/// </summary>
/// <param name="connect">与SQL的链接</param>
/// <param name="tableName">表名</param>
/// <param name="insertK2V">要插入的数据键值对</param>
/// <returns>是否插入成功</returns>
public bool Insert(MySqlConnection connect,string tableName,Dictionary<string,string> insertK2V)
{
string sql = "insert into ";
sql += "user(";
string[] keys = insertK2V.Keys.ToArray();
for (int i = 0; i < keys.Length; i++)
{
sql += keys[i];
if (i!= keys.Length - 1)
{
sql += ",";
}
}
sql += ") values(";
string[] values = insertK2V.Values.ToArray();
for (int i = 0; i < values.Length; i++)
{
sql += "'" + values[i] + "'";
if (i != values.Length - 1)
{
sql += ",";
}
}
sql += ")";
LogUtils.LogInfo(sql);
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result=cmd.ExecuteNonQuery();
return result==1;
}
4.3 删除(delete)
/// <summary>
/// 删除
/// </summary>
/// <param name="connect">与SQL的链接</param>
/// <param name="tableName">表名</param>
/// <param name="selectK2V">选择条件键值对</param>
/// <returns>是否删除成功</returns>
public bool Delete(MySqlConnection connect, string tableName, Dictionary<string, string> selectK2V)
{
string sql = "delete from user where ";
foreach (var item in selectK2V)
{
sql += " "+item.Key + "='" + item.Value + "' and";
}
sql = sql.Remove(sql.Length - 3,3);
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result = cmd.ExecuteNonQuery();
return result == 1;
}
4.4 修改(Update)
/// <summary>
/// 修改
/// </summary>
/// <param name="connect">与SQL的链接</param>
/// <param name="tableName">表名</param>
/// <param name="updateK2V">要更新的数据的键值对</param>
/// <param name="selectK2V">选择的条件</param>
/// <returns></returns>
public bool Update(MySqlConnection connect, string tableName, Dictionary<string, string> updateK2V, Dictionary<string, string> selectK2V)
{
string sql = "update user set ";
foreach (var item in updateK2V)
{
sql += "'" + item.Key.ToString() + "=" + "'" + item.Value + "'" + ",";
}
sql = sql.Remove( sql.Length - 1) ;
sql += " where ";
foreach (var item in selectK2V)
{
sql+= sql += "'" + item.Key.ToString() + "=" + "'" + item.Value + "'" + ",";
}
sql=sql.Remove(sql.Length - 1);
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result = cmd.ExecuteNonQuery();
return result == 1;
}
4.5 查找(Select)
/// <summary>
/// 查找
/// </summary>
/// <param name="connect">与SQL的链接</param>
/// <param name="tableName">表名</param>
/// <param name="selectK2V">选择条件键值对</param>
/// <returns>找到的数据集</returns>
public MySqlDataReader Select(MySqlConnection connect, string tableName, Dictionary<string, string> selectK2V)
{
string sql = "select * from " + tableName;
if (selectK2V.Count > 0)
{
sql += " where";
}
foreach (var item in selectK2V)
{
sql += " " + item.Key + "='" + item.Value + "' and";
}
sql = sql.Remove(sql.Length - 3, 3);
LogUtils.LogInfo(sql);
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
return reader;
}