Unity 3 D操作MySQL
最近はデータベース用が多いので、MySQLの常用文を整理しておきます.1.データベースSqlAccess sql=new SqlAccess()を開く.sql.OpenSql(「データベース名」);
2.テーブルsqlを作成する.CreateTable AutoID(「tablename」,new string[]{"学号","名前"},new string[]{"VARCHAR(255)","VARCHAR(255)})});
4.データsqlを挿入する.InsertInto(「tablename」,new string[{「学号」,「氏名」,new string[{「001」,「張三」)
複数のデータを同時に更新(置換)する複数のフィールド値は、異なる場合があります.
最後にsql文を実行する方法を貼り付けます
主な文が貼られているだけで、詳しく説明されていないところが多いのは、コピーするだけではなく、考えなければならないからです.これらの方法は基本的にMysqlの一般的な使用者を満たしており,多くの方法は高同時性をサポートしており,毎秒数千件のデータに問題はない.また本人もアマチュアで、そこに间违った文法があれば指摘してほしい.ありがとう!
public MySqlConnection dbConnection;
string host = "localhost";
string id = "root";
public void OpenSql(string db)
{
try
{
string connectionString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};charset=utf8;", host, db, id, " "," ");
dbConnection = new MySqlConnection(connectionString);
dbConnection.Open();
}
catch (Exception e)
{
UnityEngine.Debug.Log("SQL: ， MySql 。" + e.Message.ToString());
}
}2.テーブルsqlを作成する.CreateTable AutoID(「tablename」,new string[]{"学号","名前"},new string[]{"VARCHAR(255)","VARCHAR(255)})});
////// /// /// /// /// ///public DataSet CreateTableAutoID(string name, string[] col, string[] colType) { if (col.Length != colType.Length) { UnityEngine.Debug.Log("SQL:columns.Length != colType.Length"); } // " NOT NULL AUTO_INCREMENT" // string query = "CREATE TABLE " + name.ToString() +" (" + col[0] + " " + colType[0];//+ " NOT NULL AUTO_INCREMENT"; for (int i = 1; i < col.Length; ++i) { query += ", " + col[i] + " " + colType[i]; } query += ", PRIMARY KEY (" + col[0] + ")" + ")"; return ExecuteQuery(query); }
3.删除表
sql.DeleteTable(“tablename”);
public DataSet DeleteTable(string name)
{
string query = "DROP TABLE " + name.ToString();//+ " NOT NULL AUTO_INCREMENT";
return ExecuteQuery(query);
}4.データsqlを挿入する.InsertInto(「tablename」,new string[{「学号」,「氏名」,new string[{「001」,「張三」)
////// /// /// /// /// ///public DataSet InsertInto(string tableName, string[] col, string[] values) { if (col.Length != values.Length) { UnityEngine.Debug.Log("SQL:columns.Length != colType.Length"); } string query = "INSERT INTO " + tableName + " (" + col[0]; for (int i = 1; i < col.Length; ++i) { query += ", " + col[i]; } query += ") VALUES (" + "'" + values[0] + "'"; for (int i = 1; i < values.Length; ++i) { query += ", " + "'" + values[i] + "'"; } query += ")"; return ExecuteQuery(query); }
5.同时插入多条数据
sql.InsertIntos(“tablename”,new string[] { “学号”, “姓名” },new List
////// /// /// /// /// ///public DataSet InsertIntos(string tableName, string[] col, List<string[]> values) { try { string query = "INSERT IGNORE INTO " + tableName + " (" + col[0]; for (int i = 1; i < col.Length; ++i) { query += ", " + col[i]; } query += ") VALUES " ; for (int i = 0; i < values.Count; i++) { string[] value = values[i]; if (i!=0) { query += ",(" + "'" + value[0] + "'"; } else { query += "(" + "'" + value[0] + "'"; } for (int j = 1; j < value.Length; ++j) { query += ", " + "'" + value[j] + "'"; } query += ")"; } return ExecuteQuery(query); } catch (Exception) { return null; } }
6.删除数据
////// cols colsvalues /// /// /// /// ///public DataSet Delete(string tableName, string cols, string[] colsvalues) { string query = "DELETE FROM " + tableName + " WHERE " + cols + " = '" + colsvalues[0] + "'"; for (int i = 1; i < colsvalues.Length; ++i) { query += " or " + cols + " = '" + colsvalues[i]+"'"; } return ExecuteQuery(query); }
7.查找
////// /// /// /// /// ///public List<string> SearchAssign(string table, string key, string match) { List<string> txet = new List<string>(); if (dbConnection == null) return txet; string sqlSearch = "select * from " + table + " WHERE " + key + "=" + match; MySqlCommand mySqlCommand = new MySqlCommand(sqlSearch, dbConnection); MySqlDataReader reader = mySqlCommand.ExecuteReader(); try { while (reader.Read()) { if (reader.HasRows) { for (int i = 0; i < reader.FieldCount; i++) { txet.Add(reader.GetString(i)); } } } } catch (Exception e) { } finally { reader.Close(); } return txet; }
////// (LIKE) /// /// /// /// ///public Dictionary<string, List<string>> SearchMatch(string table,string key,string match) { Dictionary<string, List<string>> txet = new Dictionary<string, List<string>>(); if (dbConnection == null) return txet; //a N //'%a%' a //'%a' a //'a%' a string sqlSearch = "select * from "+ table+ " WHERE " + key+" LIKE "+ "'%"+ match+ "%'"; MySqlCommand mySqlCommand = new MySqlCommand(sqlSearch, dbConnection); MySqlDataReader reader = mySqlCommand.ExecuteReader(); try { while (reader.Read())// { if (reader.HasRows) { List<string> data = new List<string>(); for (int i = 0; i < reader.FieldCount; i++) { data.Add(reader.GetString(i)); } if (data.Count > 0) txet.Add(data[0], data); } } } catch (Exception e) { Console.WriteLine("SQL: ！" + e.Message); } finally { reader.Close(); } return txet; }
那么如果是读取表中所有数据的话SQL语句直接把where去掉就可以
8.更新(同时更新多条数据且要修改的值相同)
AND 同时满足多个条件
////// /// /// /// /// /// /// ///public DataSet UpdateIntos(string tableName, string[] cols, string[] colsvalues, List<string> selectkey, List<string> selectvalue) { // ： ID Name ID name // cols=Name colsvalues selectkey=ID selectvalue= 1 string query = "UPDATE " + tableName + " SET " + cols[0] + " = '" + colsvalues[0] + "'"; for (int i = 1; i < colsvalues.Length; ++i) { query += ", " + cols[i] + " = '" + colsvalues[i] + "'"; } query += " WHERE " + selectkey[0] + " = '" + selectvalue[0] + "'"; for (int i = 1; i < selectvalue.Count; i++) { query += " AND " + selectkey[i] + " = '" + selectvalue[i] + "'"; } UnityEngine.Debug.Log(query); return ExecuteQuery(query); }
把AND 改为 OR 就可以赛选满足一项的数据
同时更新(替换 如果不希望替换则使用insert into)多条数据 中的一个字段 值可不同
public DataSet UpdateIntos1(string tableName, string cols, List<string> colsvalues, string selectkey, List<string> selectvalue)
{
string query = "replace into " + tableName + " (" + selectkey + "," + cols + ")";
query += " VALUES (" + selectvalue[0] + ",'" + colsvalues[0] + "')";
for (int i = 1; i < colsvalues.Count; ++i)
{
query += ",(" + selectvalue[i] + ",'" + colsvalues[i] + "')";
}
return ExecuteQuery(query);
}複数のデータを同時に更新(置換)する複数のフィールド値は、異なる場合があります.
public DataSet UpdateIntos2(string tableName, List<string> cols, List<string[]> colsvalues, string selectkey, List<string> selectvalue)
{
string query = "replace into " + tableName + " (" + selectkey + "," + cols[0];
for (int i = 1; i < cols.Count; i++)
{
query += "," + cols[i];
}
query += ")";
query += " VALUES ";
for (int i = 0; i < colsvalues.Count; i++)
{
if (i==0)
query += "(" + selectvalue[i];
else
query += ",(" + selectvalue[i];
string[] values = colsvalues[i];
for (int j = 0; j < values.Length; j++)
{
query += ",'" + values[j] + "'";
}
query += ")";
}
return ExecuteQuery(query);
}最後にsql文を実行する方法を貼り付けます
public DataSet ExecuteQuery(string sqlString)
{
if (dbConnection==null)
return null;
if (dbConnection.State == ConnectionState.Open)
{
DataSet ds = new DataSet();
try
{
MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
da.Fill(ds);
}
catch (Exception ee)
{
//Console.WriteLine("zheli"+ee.Message);
UnityEngine.Debug.Log("SQL:" + sqlString + "
" + ee.Message.ToString());
ds = null;
}
finally
{
}
return ds;
}
return null;
}主な文が貼られているだけで、詳しく説明されていないところが多いのは、コピーするだけではなく、考えなければならないからです.これらの方法は基本的にMysqlの一般的な使用者を満たしており,多くの方法は高同時性をサポートしており,毎秒数千件のデータに問題はない.また本人もアマチュアで、そこに间违った文法があれば指摘してほしい.ありがとう!