MySQL学习002：执行数据库查询命令MySQLDataReader、插入命令Insert、更新命令Update、删除命令Delete

一、查询命令：MySQLDataReader

 static void Read()
        {
            //跟MySQL建立连接
            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";
                MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
                //cmd.ExecuteReader();//执行一些查询
                //cmd.ExecuteScalar();//执行一些查询，返回一个单个的值
                MySqlDataReader reader = cmd.ExecuteReader();//读出流
                reader.Read();//打开第一个数据（如果读取成功，返回true; 如果没有下一页数据，则读取失败，返回false）
                Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
                reader.Read();//打开第二个数据
                Console.WriteLine(reader[0].ToString() + reader[1].ToString() + reader[2].ToString());
                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"));
                }
                Console.WriteLine("C#和MySQL已经建立连接");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
        }

二、插入命令：Insert

 static void Insert()
        {
            //跟MySQL建立连接
            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 = "insert into users(username,password) values('caixukun','234')";
                //string sql = "insert into users(username,password,registerdate) values('caikun','23','2020-09-14')";//插入指定日期
                string sql = "insert into users(username,password,registerdate) values('cai','2345','"+DateTime.Now+"')";//插入实时日期
                MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数
                
                Console.WriteLine("C#和MySQL已经建立连接");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
        }

三、更新命令：Update

static void Update()
        {
            //跟MySQL建立连接
            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 = "update users set username='sfeggd',password='432432' where id=4";
                Console.WriteLine(sql);
                MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数

                Console.WriteLine("C#和MySQL已经建立连接");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
        }

四、删除命令：Delete

  static void Delete()
        {
            //跟MySQL建立连接
            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 = "delete from users where id=4";
                Console.WriteLine(sql);
                MySqlCommand cmd = new MySqlCommand(sql, conn);//创建命令
                int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据的行数

                Console.WriteLine("C#和MySQL已经建立连接");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                conn.Close();
            }
        }