数据库开发（六）索引、内连接、子查询

DataSet和DataTable

通过DataAdapter绑定数据

string constr = "Data Sourcr=.;Initial Catalog=MyDatabaseOne;Intergrated Security=True";
string sql = "select * from TblPerson";
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
{
    adapter.Fill(dt);//实现了从数据库中读取数据并加载到DataTable中
}
this.dataGridView1.DataSource = dt;//绑定数据

创建一个DataSet

DataSet就是一个集合，内存数据库，临时数据库。

//1.创建一个DataSet（临时数据库）
DataSet ds = new DataSet("School");

//2.创建一张表
DataTable dt = new DataTable("Student");

//2.1向dt表中创建一些列
DataColumn dcAutoId = new DataColumn("AutoId", typeof(int));
//设置列为自动编号
dtAutoId.AutoIncrement = true;
dcAutoId.AutoIncrementSeed = 1;//从1开始
dtAutoId.AutoIncrementStep = 1;//每次增加1
//把dcAutoId列加到dt表中
dt.Columns.Add(dtAutoId);

//增加一个姓名列
DataColumn dcUserName = dt.Columns.Add("UserName", typeof(string));//返回值为DataColumn类型
//设置列不允许为空
dcUserName.AllowDBNull = false;

//增加一个年龄列
dt.Columns.Add("UserAge", typeof(int));

//------------------向dt表中增加一些行数据---------------------
//创建一个行对象
DataRow dr1 = dt.NewRow();//根据dt表结构创建一个行对象
dr1["UserName"] = "hzd";
dr1["UserAge"] = 24;
//把该行对象增加到dt中
dt.Rows.Add(dr1);
//再增加一行
DataRow dr2 = dt.NewRow();
dr2["UserName"] = "bbb";
dr2["UserAge"] = 23;
dt.Rows.Add(dr2);

//3.把表加到数据库中（把dt加到ds中）
ds.Tables.Add(dt);

Console.WriteLine("------------------遍历表中的数据-----------------");

//1.遍历DataSet中的每张表
for (int i = 0; i < ds.Tables.Count; i++)
{
    //输出每个表的表名
    Console.WriteLine("表名：{0}", ds.Tables[i].TableName);
    //遍历表中的每一行
    for (int r = 0; r < ds.Tables[i].Rows.Count; r++)
    {
        //获取每一行
        DataRow currentRow = ds.Tables[i].Rows[r];
        //输出当前行中的每一列
        for (int c = 0; c < ds.Tables[i].Columns.Count; c++)
        {
            Console.Write(currentRow[c] + "   |   ");
        }
        Console.WriteLine();
    }
}

查看连接字符串

有时候当我们写连接字符串记不住的时，可以通过Visual Studio查看连接字符串。

步骤：视图，服务资源管理器，数据连接，输入服务器名，Windows身份验证，选择或输入数据库名称，测试连接。

测试连接成功后，可通过属性窗口查看连接字符串。

内连接

查询数据时，当需要将多个表中的列共同显示到一个结果集中的时候，可以使用连接查询。

笛卡儿积

把两张表的结果集连在一起，得出来一大很大的结果集，A表中的每条记录与B表中的每条记录相连，叫做笛卡儿积。

笛卡儿积记录条数，等于A表记录条数乘以B表记录条数。

select * from PhoneType,PhoneNum --28条记录

内连接查询

内连接查询的原理是，先把两张表的笛卡儿积查出来，然后根据条件对笛卡儿积结果集进行筛选。

select
    *
from PhoneNum inner join PhoneType on PhoneNum.pTypeId = PhoneType.ptId --7条记录

筛选两表中，ptId不相等的记录。

select
    *
from PhoneNum inner join PhoneType on PhoneNum.pTypeId <> PhoneType.ptId --21条记录

查询的时候，如果表中有重名的列，应该通过表名.列名的方式来限定指定的列是属于哪张表的。

select
    PhoneNum.pid,
    PhoneNum.pname,
    PhoneNum.pcellphone,
    PhoneType.ptname
from PhoneNum inner join PhoneType on PhoneNum.ptypeId = PhoneType.ptId

查询的时候为表起列名。

select
    pn.pid,
    pn.pname,
    pn.pcellphone,
    pt.ptname
from PhoneNum as pn inner join PhoneType as pt on pn.ptypeId = pt.ptId

使用带参数的SQL语句向数据库插入空值

向数据库中插入null值，不能直接使用C#中的null，必须使用DBNull.Value。

string name = "hezhendong";
int age = 24;
int? height = null;
bool? gender = null;

string sql = "insert into TblPerson values(@name,@age,@height,@gender)";
SqlParameter[] pms = new SqlParameter[] {
    new SqlParameter(@name, SqlDbType.NVarChar, 50) { Value = name },
    new SqlParameter(@age, SqlDbType.Int) { Value = age },
    //向数据库中插入null值，不能直接使用C#中的null，必须使用DBNull.Value
    new SqlParameter(@height, SqlDbType.Int) { Value = height == null ? DBNull.Value : (object)height },
    new SqlParameter(@gender, SqlDbType.Bit) { Value = gender == null ? DBNull.Value : (object)gender }
};
SqlHelper.ExecuteNonQuery(sql, pms);

可空值类型

可空值类型写法：值类型?，等价于Nullable<值类型>，Nullable<T>类型是一个结构体类型。

int? n = 10;
//Nullable<int> n = 10;

当可空值类型不为空时。

//int? n = 10;
Nullable<int> n = 10;
//n.HasValue = true;
//n.Value = 10;

当可空值类型为null时。

//int? n = null;
Nullable<int> n = null;
//n.HasValue = false;
if (n == null)
{
    
}

注意：Nullable<T>类型还是值类型，值类型不可以赋值为null。但是Nullable<T>可以通过HasValue属性判断值是否为空。

case函数

case函数用于每列根据不同的情况显式不同的值，注意case和end必须成对出现，要求then后面的数据类型必须一致。

相当于C#中的if-else，可以用于区间判断。

select
    *,
    头衔=case
            when [level]=1 then '菜鸟'
            when [level]=2 then '老鸟'
            when [level]=3 then '大师'
            else '骨灰级大师'
        end
from [user]

相当于C#中的switch，只能用于等值判断。

select
    *,
    头衔=case [level]
            when 1 then '菜鸟' --then后面的数据类型必须一致
            when 2 then '老鸟'
            when 3 then '大师'
            else '骨灰级大师'
        end
from [user]

根据成绩决定等级。

select
    tscoreId,
    tsid,
    tenglish,
    等级=case
            when tenglish>=95 then '优'
            when tenglish>=80 then '良'
            when tenglish>=70 then '中'
            else '差'
        end
from TblScore

成绩表增加是否及格列。

select
    *,
    是否及格=case
                when tenglish>=60 and tmath>=60 then '及格'
                else '不及格'
            end
from TblScore

表中有ABC三列。当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

select
    x=case
        when A>B then A
        else B
      end,
    y=case
        when B>C then B
        else C
      end
from TestA

索引

索引的目的

提高查询效率。

索引的分类

索引分为两种：

聚集索引（物理），一个表中只能有一个聚集索引。
非聚集索引（逻辑），一个表中可以有多个非聚集索引。

索引的缺点

增加索引后，会增加额外的存储空间。
降低了增加新纪录，修改，删除记录的效率。

创建索引的方式

在表设计器中，右键，选择索引/键，添加，在列种选择索引包含的列。

也可以通过SQL语句创建索引，一般在where条件后的列，或者经常查询的列中创建索引。

--创建聚集索引
create clustered index IXc4 on TestIndex1002(c4)

--创建非聚集索引
create nonclustered index IXc3 on TestIndex1002(c3)

--删除索引
drop index TestIndex1002.IXc4

打开查询的统计信息

打开统计信息：查询，查询选项，高级，SET STATISTICS TIME、SET STATISTICS IO。
打开“实际的执行计划”或“估计的执行计划”。

预读取、物理读取、逻辑读取

SQL Server存取数据都是以页为单位（每页总大小8KB）。
逻辑读取：从缓存中读取数据。
物理读取：从磁盘中读取数据。
预读取：一种性能优化机制，在执行查询时先预测执行“查询计划”所需的数据和索引页，然后在查询实际使用这些页之前将它们读入缓冲区高速缓存。

数据库引擎优化顾问

当我们执行一条（多条）SQL查询语句，感觉到效率很低，但是又不知道怎么创建索引，就可以通过数据库引擎优化顾问，帮我们分析如何优化或创建索引。

把查询的SQL保存到桌面文件，如a.sql。
点击工具，打开数据库引擎优化顾问，连接，新建会话，在文件中选择sql文件，选择数据库，选择要优化的数据库和表，点击开始分析。
在建议窗口中，可以看到可以进行优化的建议（分区建议、索引建议）。
在索引建议中，最后一列定义，可以直接拷贝创建索引的代码，并且执行。
执行完成后，再次查询SQL语句，可以看到，执行效率明显提高。

注意：当我们使用模糊查询的时候，注意尽量不要把百分号写在前面，因为把百分号写在前面，查询将不会用到索引（前提是表中已经创建了索引），但是如果表中没有创建索引，则百分号写在前面没有影响。

select a from Test where a like '%aa%' --不会使用索引查询

select a from Test where a like 'aa%' --会使用索引查询

子查询

定义：把一个查询的结果在另一个查询中使用就叫子查询。（将一个查询语句作为一个结果集供其他SQL语句使用）

select * from (select col1, col2 from tab) as t

子查询的基本分类

独立子查询。即子查询可以独立运行，如上面示例中的就是独立子查询，因为select col1, col2 from tab可以独立运行，注意子查询一定要起别名。
相关子查询。子查询中引用了父查询中的结果。

通过子查询，查询高二二班的所有学生。

--独立子查询
select * from TblStudent
where tsClassId = (select tClassId from TblClass where tClassName = '高二二班')

--相关子查询
select * from TblStudent as ts
where exist(select * from TblClass as tc where ts.tsClassId = tc.tClassId and tc.tClassName = '高二二班')

注意：在exist中，只要里面能查询出来结果，exist就返回布尔值true。相关子查询不能独立运行，引用了父查询的结果。所有子查询都可以写成相关子查询的形式。

子查询的返回值不止一个。

select
    1 as f1,
    2 as f2,
    (select tenglish from tblScore) as f3 --该列返回多个值
	
--错误，tclassid返回多个值
select * from TblStudent where tsClassId = (select tclassId from TblClass)

--正确，使用in可以包含多个值
select * from TblStudent where tsClassId in (select tclassId from TblClass)

分页查询

要分页查询，或者分页显示。首先要确定按照什么顺序，然后才能确定哪些记录应该放在第一页，哪些记录应该在第二页。

使用top实现分页

例子：查询Customers表的数据，每页显示7条数据。

select * from Customers

查询出第一页的数据。

select top 7 * from Customers order by CustomerID asc

查询出前两页的数据。

select top (7*2) * from Customers order by CustomerID asc

查询出第二页的数据。

select top 7 * from Customers where CustomerID not in
(select top (7*(2-1)) CustomerID from Customers order by CustomerID asc)
order by CustomerID asc

查询出第五页的数据。

select top 7 * from Customer where CustomerID not in
(select top (7*(5-1)) CustomerID from Customers order by CustomerID asc)
order by CustomerID asc

分页的思路：比如要查询第n页的数据。先查询出（n-1）页的数据的CustomerID。然后再查询这个表中CustomerID不在这里面的数据（除去已经看过的数据，剩下没看过的数据），升序排序，再取前7条，就是第n页的数据。

使用ROW_NUMBER实现分页

为数据排序，然后编号。
根据用户要查看的每页记录条数，以及要查看第几页，确定应该查询第几条到第几条。

select
    *,
    Rn=ROW_NUMBER() over(order by CustomerID asc)
from Customers

每页显示7条，要查看第八页。

select * from
(select *, Rn=ROW_NUMBER() over(order by CustomerID asc) from Customers) as T
where T.Rn between (8-1)*7+1 and 8*7