前言:本文范例数据库中含有3个表,student(s#,sname),course(c#,cname),sc(s#,c#),SC为student和course的联系集
1.CRUD操作
查询:
select column1,column2... from table1,table2...where columnX 比较符 columnY [and 其他条件] [or 其他条件]
高级用法:
1.join(又称连接)。比如查询一名学生的选课信息。如果采用如下方式:
select * from student, sc where student.`s#`=sc.`s#` and `s#`='001'
from student,course会将student表和course表进行笛卡尔积,两个表任意两项结合成一个新行。会产生m*n行数据,这种相乘关系会导致数据量十分庞大,易出现溢出。这时我们使用连接,如下:
select * from student join sc on student.`s#`=sc.`s#`;
则会过滤掉不符合on后条件的条目,就极大的缩减了数据量。
另外,像上面这样连接的两个表的属性名相同(均为s#),可以直接如下使用自然连接:
select * from student natural join sc
就可以省略不写on后的条件。
2.group by。 对选择结果进行分组,比如筛选出多名同学所选的课程,并将这些课程按照不同学生进行分组,如下效果:
每个同学选的课都跟在名字后面,十分清晰。
使用以下SQL语句:
select sname,group_concat(cname) from student natural join sc natural join course where sname like '张%' group by sname;
解释:group_concat(cname)组内的所有内容,where后条件筛选所有张姓同学的选课信息,将筛选后的结果按照学生姓名(sname)进行分组。 如果上面不使用group_concat(cname),直接使用cname,则只能显示出每组的第一个元素。后面还可以加WITH ROLLUP关键字,这样会在最后增加一个分组包含了所有内容。
另外,还可以使用聚合函数,统计每个组的各项数据,比如平均值、数目之类的
3.limit 对筛选的条目数量进行限制。例如:
select * from student natural join sc limit 2,3
从第二条记录开始,只筛选出3个(对应limit 后面的2和3)
增加:
insert into student(`s#`,sname..) values('001','张三'),('002','李四');
另外,可以把一个表中的内容经过筛选插入到另一个表中:
insert into student select `s#`,sname from otherTable [where...];
删除:
delete from student [where ...]
修改:
update student set sname='王五' where `s#`=`003`;
2.使用C#连接和操作数据库
1.连接数据库
设置连接字符串(包含了诸如用户名,密码,ip端口号之类的信息),如下使用了MySqlConnectionStringBuilder类可以快速创建。当然也可以使用string自己写。
如下方法返回了一个mysql连接
public static MySqlConnection ConnectMysql()
{
MySqlConnectionStringBuilder mySqlConnectionString= new MySqlConnectionStringBuilder();
mySqlConnectionString.UserID = "root";
mySqlConnectionString.Password= "123456";
mySqlConnectionString.Server = "localhost";
mySqlConnectionString.Database= "shujukulab";
mySqlConnectionString.Port = 3306;
connection = new MySqlConnection(mySqlConnectionString.ConnectionString);
try
{
Console.WriteLine("Connecting to MySQL...");
connection.Open();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
Console.WriteLine("Done.");
return connection;
}
2.执行SQL语句
有两种方法操作数据库,即不采用MySqlDataAdapter和采用MySqlDataAdapter。
1.直接法
using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
public class Tutorial2
{
public static void Main()
{
string connStr = "server=localhost;user=root;database=world;port=3306;password=******";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'";//定义SQL语句
MySqlCommand cmd = new MySqlCommand(sql, conn);//把SQL语句转化成能使用的MySqlCommand,这里conn指明操作的对象是这个连接
MySqlDataReader rdr = cmd.ExecuteReader();//直到这一步才执行了SQL语句
while (rdr.Read())
{
Console.WriteLine(rdr[0]+" -- "+rdr[1]);
}
rdr.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
Console.WriteLine("Done.");
}
}
cmd.ExecuteReader()是对于有返回内容的sql语句(一般是select),对于增加、删除、修改这种没有返回值的操作,可以使用cmd.ExecuteNonQuery();
2.使用MySqlDataAdapter
接下来解释MySqlDataAdapter的作用。首先,为了达到解耦的目的,我们可以在内存中储存一个DataSet,作为一个数据库的复制版本,当然DataSet中的数据要少得多,对这个DataSet进行增删改查各种操作,都不影响数据库,直到最后再把DataSet写回到数据库中(感觉可以类比内存和硬盘的写回策略),于是MySqlDataAdapter就相当于数据库和DataSet和数据库的桥梁,使用MySqlDataAdapter的fill方法把数据装进内存,再使用update方法写回数据库。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace WindowsFormsApplication5
{
public partial class Form1 : Form
{
MySqlDataAdapter daCountry;
DataSet dsCountry;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
string connStr = "server=localhost;user=root;database=world;port=3306;password=******";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
label2.Text = "Connecting to MySQL...";
string sql = "SELECT Code, Name, HeadOfState FROM Country WHERE Continent='North America'";
daCountry = new MySqlDataAdapter (sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(daCountry);
dsCountry = new DataSet();
daCountry.Fill(dsCountry, "Country");/*相当于在内存中创立了一个叫dsCountry的数据库,这个数据库中包含了一个叫Country的表*/
dataGridView1.DataSource = dsCountry;
dataGridView1.DataMember = "Country";
}
catch (Exception ex)
{
label2.Text = ex.ToString();
}
}
private void button1_Click(object sender, EventArgs e)
{
daCountry.Update(dsCountry, "Country");
label2.Text = "MySQL Database Updated!";
}
}
}
注:如果我们想执行带有参数的SQL语句,一般会想到利用字符串拼接把变量拼到SQL语句中,再执行,这种字符串操作容易出现错误。但有一种更安全的方法:
cmd.CommandText = "insert into Course values(@kechenghao,@cname,@credit,@chours,@t)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@kechenghao", kechenghao);
cmd.Parameters.AddWithValue("@cname", cname);
cmd.Parameters.AddWithValue("@credit", credit);
cmd.Parameters.AddWithValue("@chours", chours);
cmd.Parameters.AddWithValue("@t", tnum);
cmd.ExecuteNonQuery();
先用@加变量名占位,再添加参数和参数值,执行时原本由@+变量名的内容就会被传入的参数值替换掉。
以上示例代码大多来自于Mysql官网