Photon Server游戏服务器教程（三）MySQL数据库基础及数据操作

• ._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。
• ._BLOB存储的数据只能整体读出。
• ._TEXT可以指定字符集，_BLO不用指定字符集。

选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

        static void Main(string[] args)
        ｛
             string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root;";
             MySqlConnection conn = new MySqlConnection(connectStr);
            try
            ｛
                conn.Open();
                //在这里执行其它操作
                //  Console.WriteLine("已经建立连接");
            ｝
            catch (Exception e)
            ｛
                Console.WriteLine(e.ToString());
            ｝
            finally
            ｛
                conn.Close();
            ｝
        ｝

 string sql = "select * from users";
 MySqlCommand cmd = new MySqlCommand(sql, conn);
 //cmd.ExecuteReader();//执行一些查询
 //cmd.ExecuteNonQuery();//插入 删除
 //cmd.ExecuteScalar();//执行一些查询，返回一个单个的值
 MySqlDataReader reader = cmd.ExecuteReader();
 //reader.Read();//读取下一页数据，如果读取成功，返回true，如果没有下一页了，读取失败的话，返回false
 while (reader.Read())
  ｛
      //Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
      //Console.WriteLine(reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetString(2));
      Console.WriteLine(reader.GetInt32("id") + " " + reader.GetString("username") + " " + reader.GetString("password"));
  ｝

string sql = "insert into users(username,password,registerdate) values('csdFu','234','" + DateTime.Now + "')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数

string sql = "delete from users where id = 4";
MySqlCommand cmd = new MySqlCommand(sql, conn);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数

string sql = "update users set username='sdfsedfwer',password='23242342432' where id = 4";
MySqlCommand cmd = new MySqlCommand(sql, conn);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数

//COUNT() 函数返回匹配指定条件的行数。
string sql = "select count(*) from users";
MySqlCommand cmd = new MySqlCommand(sql, conn);
object o = cmd.ExecuteScalar();
int count = Convert.ToInt32(o.ToString());
Console.WriteLine(count);
//AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
//FIRST() 函数返回指定的字段中第一个记录的值。
//提示：可使用 ORDER BY 语句对记录进行排序。

 static bool VerifyUser(string username,string password)
    ｛
        string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root;";
        MySqlConnection conn = new MySqlConnection(connectStr);//并没有去跟数据库建立连接
        try
        ｛
            conn.Open();
            //string sql = "select * from users where username = '"+username+"' and                 password='"+password+"'";
            //我们自己按照查询条件去组拼sql，当参数多时容易出错。
            string sql = "select * from users where username =@para1 and password = @para2 ";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("para1", username);
            cmd.Parameters.AddWithValue("para2", password);
            MySqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            ｛
            return true;
            ｝
        ｝
        catch (Exception e)
        ｛
        Console.WriteLine(e.ToString());
        ｝
        finally
        ｛
        conn.Close();
        ｝
        return false;
    ｝