首先创建了一个SQL Server数据库作为测试的数据库，建立表并填入测试数据
数据库：SQL Server
数据库名：Blog
表名：Test
注：数据库的连接可以使用“dbl”文件测试，具体使用百度即可。
1.将数据库的数据全部查询到datagridview
语句：select * from Test
private void button1_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr="select * from Test";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
2.查询数据库的部分数据并显示到datagridview
语句： string cmdstr = "select 姓名,年龄 from Test";
private void button2_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr = "select 姓名,年龄 from Test";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
3.使用列别名显示数据
语句： string cmdstr = "select 姓名 as name,年龄 as age from Test";
注：语句中的“as”关键词，可以使用空格代替
private void button3_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr = "select 姓名 as name,年龄 as age from Test";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
4.使用列计算
语句：string cmdstr = "select 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test";
private void button4_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr = "select 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
5.限定查询数值范围大小
语句：string cmdstr = "select 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test where (语文+数学+英语)>215";
private void button5_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr = "select 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test where (语文+数学+英语)>215";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
6.通配符
“_” ：代表一个字符
"%" ：代表多个字符
"[]" ：代表范围
"[^]”：非括号范围内的单个字符
“distinct”：去除重复记录
7.排序取前2
语句：string cmdstr = "select top 2 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test order by (语文+数学+英语) desc";
private void button6_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr = "select top 2 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test order by (语文+数学+英语) desc";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
8.排序取前百分之50
语句：string cmdstr = "select top 50 percent 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test order by (语文+数学+英语) desc";
private void button7_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr = "select top 50 percent 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test order by (语文+数学+英语) desc";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
9.分组查询、统计
语句：string cmdstr = "select 班级,sum(语文)as 语文总分,sum(数学) as 数学总分,sum(英语) as 英语总分,sum(语文+数学+英语) as 总分 from Test group by 班级";
private void button8_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr = "select 班级,sum(语文)as 语文总分,sum(数学) as 数学总分,sum(英语) as 英语总分,sum(语文+数学+英语) as 总分 from Test group by 班级";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
10.聚合函数
AVG：数字表达式中所有值的平均值
COUNT：选定的行数
MAX：表达式中的最高值
MIN：表达式中的最低值
STDEV：表达式中所有值的统计标准偏差
STDEVP：表达式中所有值的填充统计标准偏差
VAR：表达式中所有值的统计方差
VARP：表达式中所有值的填充统计方差
SUM：数字表达式中所有值的和
11.数据处理函数
LEN：限定字段长度
Ltrim、Rtrim：去处空格函数
left、right：字符函数
ABS：数字函数
Year、Month：日期函数
12.多表分组查询
语句：string cmdstr = "select t.姓名,t.性别,t.年龄,p.name,p.age from Test t,practice p where t.姓名=p.name group by t.姓名,t.性别,t.年龄,p.name,p.age";
注：该功能将同一数据库下的不同数据表进行合并查询，取不同数据表的数据显示到同一个datagridview中
private void button11_Click(object sender, EventArgs e)
{
string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
string cmdstr = "select t.姓名,t.性别,t.年龄,p.name,p.age from Test t,practice p where t.姓名=p.name group by t.姓名,t.性别,t.年龄,p.name,p.age";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdstr;
cmd.Connection = conn;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[].DefaultView;
}
