一.准备工作
(1)Unity2018.3.14使用MySQL需要导入以下dll文件，注意文件的.net版本。
Renci.SshNet / MySql.Data / Google.Protobuf / BouncyCastle.Crypto
（2）进行交互前了解数据库基本的增删改查语句
->增：insert into 表名 (数据1，数据2，数据3，......) values ('值1'，'值2'，'值3'，......)
->删：delete from 表名 where 限制条件
->改：update 表名 set 目标值 where 限制条件
->查：select 数据1 from 表名 where 限制条件
->建表：create table 表名 （数据1 类型，数据2 类型，数据3 类型，......）
->删表：drop table 表名
（3）新建脚本注意引入命名空间
二.脚本编写
(1)数据库功能封装(可以将核心sql语句一并进行封装，然后在调用时传参，这里为了提高可读性只封装基本实现方法)
public class SqlAccess { public static MySqlConnection mySqlConnection;//数据库名称 public static string database = "daybreak";//数据库IP private static string host = "localhost";//用户名 private static string username = "root";//用户密码 private static string password = "root"; public static string sql = string.Format("database={0};server={1};user={2};password={3};port={4}", database, host, username, password, "3306"); public static MySqlConnection con; private MySqlCommand com; #region BaseOperation
/// <summary> /// 构造方法开启数据库 /// </summary> public SqlAccess() { con = new MySqlConnection(sql); OpenMySQL(con); }
/// <summary> /// 启动数据库 /// </summary> /// <param name="con"></param> public void OpenMySQL(MySqlConnection con) { con.Open(); Debug.Log("数据库已连接"); }
/// <summary> /// 创建表 /// </summary> /// <param name="sql"></param> /// <param name="con"></param> public void CreateTable(string sql,MySqlConnection con) { MySqlCommand com = new MySqlCommand(sql, con); int res = com.ExecuteNonQuery(); }
/// <summary> /// 插入数据 /// </summary> /// <param name="sql"></param> /// <param name="con"></param> public void InsertInfo(string sql,MySqlConnection con) { MySqlCommand com = new MySqlCommand(sql, con); int res = com.ExecuteNonQuery(); }
/// <summary> /// 删除数据 /// </summary> /// <param name="sql"></param> /// <param name="con"></param> public void DeleteInfo(string sql,MySqlConnection con) { MySqlCommand com = new MySqlCommand(sql, con); int res = com.ExecuteNonQuery(); }
/// <summary> /// 修改数据 /// </summary> /// <param name="sql"></param> /// <param name="con"></param> public void UpdateInfo(string sql,MySqlConnection con) { MySqlCommand com = new MySqlCommand(sql, con); int res = com.ExecuteNonQuery(); }
/// <summary> /// 查询数据 /// </summary> /// <param name="sql"></param> /// <param name="con"></param> public Dictionary<int,List<string>> QueryInfo(string sql,MySqlConnection con) { int indexDic = 0; int indexList = 0; Dictionary<int, List<string>> dic = new Dictionary<int, List<string>>(); MySqlCommand com = new MySqlCommand(sql, con); MySqlDataReader reader = com.ExecuteReader(); while (true) { if (reader.Read()) { List<string> list = new List<string>(); for (int i = 0; i < reader.FieldCount; i++) { list.Add(reader[indexList].ToString()); indexList++; } dic.Add(indexDic, list); indexDic++; indexList = 0; } else { break; } } return dic; }
/// <summary> /// 关闭数据库 /// </summary> public void CloseMySQL() { (new MySqlConnection(sql)).Close(); Debug.Log("关闭数据库"); } #endregion}
（2）数据库功能实现
public class StudentSql : MonoBehaviour{/// <summary> /// 工具类对象 /// </summary> private SqlAccess sqlAce; MySqlConnection con; void Start() {//CreateStudentTable(); //InsertInfomation(); //QueryInfomation1(); //QueryInfomation2(); //UpdateInfomation(); //DeleteInfomation(); DeleteTable(); }
//1、建立一个“学生”表Student，它由学号Sid、姓名Sname、性别Ssex、年龄Sage、所在专业Sdept五个属性组成,类型自己定义/// <summary> /// 创建学生表 /// </summary> public void CreateStudentTable() { sqlAce = new SqlAccess(); con = SqlAccess.con; string sql = ("create table Student(Sid varchar(12),Sname varchar(8),Sage int,Sdept varchar(8))"); sqlAce.CreateTable(sql, con); sqlAce.CloseMySQL(); }
//2、添加自己所在组的所有成员到表中。/// <summary> /// 学生表插值 /// </summary> public void InsertInfomation() { sqlAce = new SqlAccess(); con = SqlAccess.con; string sql1 = ("insert into Student(Sid,Sname,Sage,Sdept) values('1001','学生A','20','土木工程')"); string sql2 = ("insert into Student(Sid,Sname,Sage,Sdept) values('1002','学生B','15','计算机与科学')"); string sql3 = ("insert into Student(Sid,Sname,Sage,Sdept) values('1003','学生C','16','电子商务')"); string sql4 = ("insert into Student(Sid,Sname,Sage,Sdept) values('1004','学生D','25','电子竞技')"); string sql5 = ("insert into Student(Sid,Sname,Sage,Sdept) values('1005','学生E','23','网络工程')"); string[] str = new string[5] { sql1, sql2, sql3, sql4, sql5 }; for (int i = 0; i < str.Length; i++) { sqlAce.InsertInfo(str[i], con); } sqlAce.CloseMySQL(); }
//3、查询年龄在18至24岁之间的学生的姓名和专业 /// <summary> /// 查询学生信息01 /// </summary> public void QueryInfomation1() { sqlAce = new SqlAccess(); con = SqlAccess.con; string sql = ("select Sname,Sdept from Student where Sage<'24' and Sage>'18' "); Dictionary<int, List<string>> dic = sqlAce.QueryInfo(sql, con); for (int i = 0; i < dic.Count; i++) { Debug.Log(string.Format("学生姓名：{0} 学生专业：{1}", dic[i][0], dic[i][1])); } sqlAce.CloseMySQL(); }
//4、查询所有学生的学号与姓名/// <summary> /// 查询学生信息02 /// </summary> public void QueryInfomation2() { sqlAce = new SqlAccess(); con = SqlAccess.con; string sql = ("select Sid,Sname from Student"); Dictionary<int, List<string>> dic = sqlAce.QueryInfo(sql, con); for (int i = 0; i < dic.Count; i++) { Debug.Log(string.Format("学生学号：{0} 学生姓名：{1}", dic[i][0], dic[i][1])); } sqlAce.CloseMySQL(); }
//5、将自己的年龄改为18岁/// <summary> /// 更新学生信息 /// </summary> public void UpdateInfomation() { sqlAce = new SqlAccess(); con = SqlAccess.con; string sql = ("update Student set Sage='18' where Sname='学生A'"); sqlAce.UpdateInfo(sql, con); sqlAce.CloseMySQL(); }
//6、删除学号为单数的学生记录/// <summary> /// 删除表信息 /// </summary> public void DeleteInfomation() { sqlAce = new SqlAccess(); con = SqlAccess.con; string sql2 = ("select Sid from Student"); Dictionary<int, List<string>> dic = sqlAce.QueryInfo(sql2, con); sqlAce.CloseMySQL(); sqlAce = new SqlAccess(); con = SqlAccess.con; for (int i = 0; i < dic.Count; i++) { if (Convert.ToInt32(dic[i][0]) % 2 == 1) { string sql1 = string.Format("delete from Student where Sid='{0}'", dic[i][0]); sqlAce.DeleteInfo(sql1, con); } } sqlAce.CloseMySQL(); }
/// <summary> /// 删除表 /// </summary> public void DeleteTable() { sqlAce = new SqlAccess(); con = SqlAccess.con; string sql = ("drop table Student"); sqlAce.DeleteInfo(sql, con); }}
转载于:https://www.cnblogs.com/Damon-3707/p/11609004.html
相关资源：使用Unity开发MySQL、附带类库和工具