This is the 19th day of my participation in the August Wenwen Challenge.More challenges in August

An overview of the

Untiy is a database that is connected to a database in Unity. Untiy is a database that is connected to a database. Untiy is a database that is connected to a database. There are specific code download address at the end of the article

Database visualization operation

The last article forgot to introduce, this article fill, which is used in Navicat, there are many online, you can download. The installation process is equivalent to a dumb-ass installation. After installation, open the software as follows (the two existing database connections are already connected)

Select File -> New Connection ->MySQL… To connect to the database

Fill in the following information

After all is filled in, click the connection test, but the reality is successful arc, click OK to connect to the database, then you can carry out database operation

A more detailed tutorial can be found here:Navicat tutorial

Unity to MySql database operation

MySQL database basic statements

Add table data

  1. Adds data to the specified column
Insert into table_name (1, 2)..... Values (Data 1, data 2.....) ;Copy the code
  1. Add data to all columns
Insert into values(data 1, data 2,.......) ;Copy the code
  1. Batch Adding Data
Insert into table_name (1, 2)..... Values (Data 1, data 2.....) ,(Data 1, data 2....) . ; Insert into values(data 1, data 2,.......) ,(Data 1, Data 2,.......) ,(Data 1, Data 2,.......) . ;Copy the code

Modifying data (update)

Update table_name set table_name 1 = 1, table_name 2 = 2,..... [] the where condition;Copy the code

Delete the data

Delete from table name [where condition];Copy the code

Query data

1. Query all information

Select * from table_name;Copy the code

2. Partial query

Select field 1, field 2..... From the name of the table;Copy the code

3. Query conditions

Select * from (select * from (select * from (select * from (select * from (select * from).....) ;Copy the code

This is the basic MySql query, so let’s see how it is used in Unity

Use it in Unity

Execute SQL statement

First determine whether the database connection is open, and then in the SQl statement operation, through MySqlDataAdapter database statement operation. Define a DataSet as return information for SQl statement execution

 DataSet ds = new DataSet();
 MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter(sqlString, mySqlConnection);
 mySqlAdapter.Fill(ds);
Copy the code

Query data

The method used is to write a method, pass in all the conditions to be queried, and then spliced into a complete SQL statement, the main parameters are:

  • The name of the table
  • The column to query
  • The condition column of the query
  • Conditional operator
  • The value of the condition
public DataSet Select(string tableName, string[] items, string[] whereColumnName,string[] operation, string[] value)
Copy the code

Then the data is spliced

string query = "Select " + items[0];
for (int i = 1; i < items.Length; i++)
{
    query += "," + items[i];
}
query += " FROM " + tableName + " WHERE " + whereColumnName[0] + " " + operation[0] + " '" + value[0] + "'";
for (int i = 1; i < whereColumnName.Length; i++)
{
    query += " and " + whereColumnName[i] + " " + operation[i] + " '" + value[i] + "'";
}
Copy the code

Create a table

The overall idea is the same as above, mainly data splicing should pay attention to the content, the main parameters:

  • The name of the table
  • The attribute columns
  • Attribute column type
 string query = "CREATE TABLE  " + name + "(" + colName[0] + " " + colType[0];
 for (int i = 1; i < colName.Length; i++)
 {
     query += "," + colName[i] + " " + colType[i];
 }
 query += ")";
Copy the code

Create a table with an incremented ID

string query = "CREATE TABLE  " + name + " (" + colName[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT";
for (int i = 1; i < colName.Length; ++i)
{
    query += ", " + colName[i] + " " + colType[i];
}
query += ", PRIMARY KEY (" + colName[0] + ")" + ")";
Copy the code

Insert data

Insert data is to add a data item to the database, including all data and some data. The main parameters for inserting all data are as follows:

  • The name of the table
  • Insert data
  string query = "insert into " + tableName + " values (" + "'" + values[0] + "'";
  for (int i = 1; i < values.Length; i++)
  {
      query += ", " + "'" + values[i] + "'";
  }
  query += ")";
Copy the code

Insert some data, main parameters:

  • The name of the table
  • The column to insert
  • Insert data for the corresponding column
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 += ")";
Copy the code

Update the data

Update data using the Update SQL statement. The main parameters are as follows:

  • The name of the table
  • Update the column
  • Updated data
  • The column to update
  • Data to be updated

Parameters 2,3 are updated data, and 4,5 are search conditions

 string update = "update " + tableName + " set " + cols[0] + " = " + colsvalues[0];
 for (int i = 1; i < colsvalues.Length; i++)
 {
     update += ", " + cols[i] + " =" + colsvalues[i];
 }
 update += " where " + selectkey + " = " + selectvalue + " ";
Copy the code

delete

Delete data and query similar, by where to delete data to find and then delete, the main parameters are

  • The name of the table
  • Data column
  • data
 string delete = "delete from " + tableName + " where " + cols[0] + " = " + colsvalues[0];
 for (int i = 1; i < colsvalues.Length; i++)
 {
     delete += " or " + cols[i] + " = " + colsvalues[i];
 }
Copy the code

Source code sharing address

GitHub download address: Click here to skip to download

Write in the last

All the shared content is the author used in the daily development process of a variety of small function points, sharing is also a way to review, if there is a bad place to write, please give more advice. Welcome to learn from each other and make progress. This piece article is written here first, hope to be able to help you