c#连接mysql数据库查询语句_C#连接MySQL数据库（增删改查）

using System;

using MySql.Data.MySqlClient;

namespace CSharp直接连接MySQL

{

class Program

{

static void Main(string[] args)

{

//Read();

//Insert();

//Update();

//Delete();

//ExcuteSccalar();//查询一个值更方便

Console.WriteLine(VerifyUser("w", "2"));//验证库中是否有此数据

Console.ReadKey();

}

static bool VerifyUser(string username, string password)

{

string connectStr = "server=127.0.0.1;port=3306;database=zxw;user=root;password=123456";//本机配置

MySqlConnection conn = new MySqlConnection(connectStr);

try

{

conn.Open();//开启数据库

Console.WriteLine("已连接。。");

//string sql = "select * from users where username='" + username + "' and password='" + password + "'";//按照自定义查询规则组拼sql

string sql = "select * from users where [email protected] and [email protected]";//按照自定义查询规则组拼sql @符号等于临时定义一个数据

MySqlCommand cmd = new MySqlCommand(sql, conn);

cmd.Parameters.AddWithValue("username", username);[email protected]

cmd.Parameters.AddWithValue("password", password);[email protected]

MySqlDataReader reader = cmd.ExecuteReader();//查询

if (reader.Read()) return true;

else return false;

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

conn.Close();//关闭连接

return false;

}

static void Read()

{

string connectStr = "server=127.0.0.1;port=3306;database=zxw;user=root;password=123456";//本机配置

MySqlConnection conn = new MySqlConnection(connectStr);

try

{

conn.Open();//开启数据库

Console.WriteLine("已连接。。");

string sql = "select * from users";

MySqlCommand cmd = new MySqlCommand(sql, conn);

MySqlDataReader reader = cmd.ExecuteReader();//查询

while (reader.Read())//Read()返回值bool

{

//Console.WriteLine(reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString());//通过read数组取值

//Console.WriteLine(reader.GetString(0) + " " + reader.GetString(1) + " " + reader.GetInt16(2));//通过get方法读取

Console.WriteLine(reader.GetString("username") + " " + reader.GetString("password") + " " + reader.GetInt16("id"));//通过列名索引读取

}

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

conn.Close();//关闭连接

Console.ReadKey();

}

static void Insert()

{

string connectStr = "server=127.0.0.1;port=3306;database=zxw;user=root;password=123456";//本机配置

MySqlConnection conn = new MySqlConnection(connectStr);

try

{

conn.Open();//开启数据库

Console.WriteLine("已连接。。");

string sql = "insert into users(username,password) values('" + DateTime.Now + "','zxw')";//当前时间

MySqlCommand cmd = new MySqlCommand(sql, conn);

int result = cmd.ExecuteNonQuery();//返回值受数据库中数据的行数影响

Console.WriteLine("插入完成");

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

conn.Close();//关闭连接

Console.ReadKey();

}

static void Update()

{

string connectStr = "server=127.0.0.1;port=3306;database=zxw;user=root;password=123456";//本机配置

MySqlConnection conn = new MySqlConnection(connectStr);

try

{

conn.Open();//开启数据库

Console.WriteLine("已连接。。");

string sql = "update users set username='oo',password='oo' where id=1";

MySqlCommand cmd = new MySqlCommand(sql, conn);

int result = cmd.ExecuteNonQuery();//返回值受数据库中数据的行数影响

Console.WriteLine("更新完成");

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

conn.Close();//关闭连接

Console.ReadKey();

}

static void Delete()

{

string connectStr = "server=127.0.0.1;port=3306;database=zxw;user=root;password=123456";//本机配置

MySqlConnection conn = new MySqlConnection(connectStr);

try

{

conn.Open();//开启数据库

Console.WriteLine("已连接。。");

string sql = "delete from users where id=1";

MySqlCommand cmd = new MySqlCommand(sql, conn);

int result = cmd.ExecuteNonQuery();//返回值受数据库中数据的行数影响

Console.WriteLine("删除完成");

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

conn.Close();//关闭连接

Console.ReadKey();

}

static void ExcuteSccalar()

{

string connectStr = "server=127.0.0.1;port=3306;database=zxw;user=root;password=123456";//本机配置

MySqlConnection conn = new MySqlConnection(connectStr);

try

{

conn.Open();//开启数据库

Console.WriteLine("已连接。。");

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);

}

catch (Exception e)

{

Console.WriteLine(e.ToString());

}

conn.Close();//关闭连接

Console.ReadKey();

}

}

}