MySQL与Unity的基本交互

一.准备工作

(1)Unity使用MySQL需要导入以下两个dll文件，注意文件的.net版本。

（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);
    }

}