1、查询一条数据
string connect Str = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功!");
string sql = "select * from goods";
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();//读取第一条
Console.WriteLine(reader[0] + " " + reader[1]+ " " + reader[2]+ " " + reader[3]);
reader.Read();//读取第二条
Console.WriteLine(reader[0] + " " + reader[1]+ " " + reader[2]+ " " + reader[3]);
connect.Close();//关闭链接
Console.ReadKey();//让程序暂停一下
2、查询所有数据
string connect Str = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功!");
string sql = "select * from goods";
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{//Read()方法会返回当前条是否有数据,没有数据就返回false
//Console.WriteLine(reader[0] + " " + reader[1]+ " " + reader[2]+ " " + reader[3]);
Console.WriteLine(reader.GetInt32("goodsid")+reader.GetString("name")+reader.GetString("goodstype")+reader.GetInt32("hp"));//推荐此方法进行输出
}
connect.Close();//关闭链接
Console.ReadKey();//让程序暂停一下
3、只查询某几项的所有数据
string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功!");
string sql = "select name,goodstype from goods";//读取name项和goodstype项的数据
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//Console.WriteLine(reader[0] + " " + reader[1]);
Console.WriteLine(reader.GetInt32("goodsid")+reader.GetString("name")+reader.GetString("goodstype")+reader.GetInt32("hp"));//推荐此方法进行输出
}
connect.Close();//关闭链接
Console.ReadKey();//让程序暂停一下
4、插入一行数据
string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功!");
string sql = "insert into users(username,password,registertime)
values('hhhH','rrrr','"+DateTime.Now+"')";
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据行数
Console.WriteLine(result);
connect.Close();//关闭链接
5、修改数据
string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功!");
string sql = "update users set username='lllllll' where id='2'";
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据行数
Console.WriteLine(result);
connect.Close();//关闭链接
6、删除数据
string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功!");
string sql = "delete from users where id='1347'";
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据行数
Console.WriteLine(result);
connect.Close();//关闭链接
7、在查询的时候添加参数
方法一:
static bool isHaveUser(string username,string password)
{
string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功!");
string sql = "select * from users where username='"+username+"'and password='"+password+"'";
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
connect.Close();//关闭链接
return true;
}
connect.Close();//关闭链接
return false;
}
方法二:
static bool isHaveUser(string username,string password)
{
string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功!");
string sql = "select * from users where username = @username and password = @password";
MySqlCommand cmd = new MySqlCommand(sql, connect);
cmd.Parameters.AddWithValue("username", username);
cmd.Parameters.AddWithValue("password", password);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
connect.Close();//关闭链接
return true;
}
connect.Close();//关闭链接
return false;
}