【黑马程序员】数据库基本知识

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ----------------------

一、数据库基础

1.主键 PrimaryKey

主键是数据行的唯一标识。不会重复的列才能当主键，一个表可以没有主键，但是会非常难以处理，因此没有特殊理由表都要设定主键

主键有两种选用策略：业务主键和逻辑主键、业务主键是使用有业务意义的字段做主键，比如身份证、银行账号等；逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。因为很难保证业务主键不会重复（身份证号码重复）、不会变化（账号升为），因此推荐使用逻辑主键。

主键选择策略：

SQLServer中的两种常用主键数据类型：int（或Bigint）+标识列（又称自动增长字段）；uniquedentifier(又称Guid、UUID)

用标识列实现字段自增可以避免并发等问题，不要开发人员控制自增。用标识列的字段在Insert的时候不用指定主键的值

Guid算法是一种可以产生唯一标识的高效算法，它使用网卡MAC、地址、纳秒级时间、芯片ID等算出来的，这样保证每次生成的Duid永远不会重复，无论是同一个计算机上还是不用计算机上。在公元3400年以前产生的GUID与任何其他产生的GUID都不用。SQLServerf中生成GUID的函数newid(), .net中生成Guid的方法：Guid.NewGuid(),返回值是Guid类型。

int自增字段的优点:站用户空间小、无需开发人员干预、易读、；缺点：效率低、数据导入导出的时候很痛苦。

Guid的优点：效率高、数据导入导出方便；缺点：占用空间大、不易读。

业界主流倾向于使用Guid,

   2. sqlserver 使用

  常用字段类型：bit(可选值0 1)、 datatime、int、 varchar、 nvarchar(可能含有中文用nvarchar)

 Char 和varchar、nvarchar的区别：char(n)不足长度n的部分用空格填充  Var:variable 可变的。

二、常用语句

1.      数据插入

1>insert 语句可以省略表名后的列名，但是不推荐

 2>如果插入的行中有些字段不确定，那么Insert的时候不指定列即可

3>可以给字段默认值，如果Guid类型主键的默认值设为 newid()就会自动生成，很少这么干

 4>主键：inset intoPerson3（Name，age）values(‘lily’,38);

Insert into Person4(Id,Name,Age)values(newid(),’tom’,30);

2.数据更新：

1>更新一个列：update T_Person Set Age=30

2>更新多个列：update T_Person Set Age=30,Nam=’tom’

3>更新一部分数据：update T_Person Set Age=30 where Name=’tom’,用where语句表示只更新Name是’tom’的行，注意SQL中等于判断用单个=而不是==。

4>where 中还可以使用复杂的逻辑判断update T_Person Set Age=30 where Name=’tom’ or Age <25,or相当于C#中的||（或者）

5>where中可以使用的其他逻辑符：or and not < > >= <= !=等

3.数据删除：

    1>删除表中的全部数据：delete fromT_Person

   2>Delete只是删除数据，表还在，和DropTable不同

   3>Delete 也可以带 where子句来删除一部分数据：deletefrom T_Person wher age>20

4.数据检索

1>执行备注中的代码创建测试数据表

 2>简单的数据检索：select*from Person

 3>只检索需要的列：selectNumber from Perosn、select  Name,Age from Person

 4>列别名：selectNumber as编号，Name姓名，Age as年龄from Person

 5>使用where检索符合条件的数据：selectName from Person where Salary<5000

还可以检索不与任何表关联的数据：select 1+1;select newid();select getdate()；

5.数据汇总

1>SQL聚合函数：MAX、MIN、AVG、SUM、 COUNT

2>大于25岁的员工的最高工资：select max(Salary) from Employee where Age>25

3>最低工资和最高工资：select  min(Salary),MAX（Salary） fromEmployee

6.数据排序

 1>Order by 子句位于 select语句末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排列方式是升序asc还是降序desc

 2>按照年龄升序排列所有员工信息的列表：select * from Employee order by Age ASC

 3>按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序：select * from Employee order by Age desc,Salary desc

 4>Order by 子句要放到where子句之后：select *from Employee where Age >23 order by Age desc,Salary desc

7.通配符过滤

 1>通配符过滤使用LIKE

2>单字符匹配的通配符为半角下划线”_”,它匹配单个出现的字符。以任意字符开头，剩余部分为“erry”:select * from Employee where Name like ‘_erry’

 3>多字符匹配的通配符为半角百分号“%“，它匹配任意次数出现的任意字符。”k%“匹配以K开头，任意长度的字符串。检索姓名中包含字母 n 的员工信息：select *from Employee where Name like’%n%’

8.空值处理

 1> 数据库中，一个列如果没有指定值，那么值就为Null，这个null和c#中的null,数据库中null表示“不知道“，而不是表示没有。因此select null+1结果是null,因为“不知道”加1的结果还是“不知道”。

 2> Select * from Employeewhere Name=null;

 Select *from Employee whereName!=null;

都没有任何返回结果，因为数据库也不“不知道”。

3> SQL中使用 IS NULL、is not null来进行空值判断：

Select * from Employee where Name is null;

Select *from Employee where Name is not null;

9.多值匹配

1>Select Age ,number , Name from Employee where Age in(23,25,28)

2>范围值：select * from Employee where Age >=23 and Age <=27; select*from Employe  where  Age between 23 and 27

10.数据分组

   按照年龄进行分组统计各个年龄段的人数：

Select Age,count (*) from Employee group by Age

Group by 子句必须放到where语句的之后

没有出现在Group by子句的列是不能放到select 语句后的列名列表中的（聚合函数除外在）

 错误：select Age,Salary from Empoyee group by Age

 正确：select Age,AVG(Salary) from Employee group by Age

11.Having 语句

 在where中不能使用聚合函数，必须使用Having,Having要位于Group by之后

 select  Age,count(*) as 人数 fromEmployee group by age  havingcount(*)>1

 注意Having中不能使用未被参数分组的列， Having不能代替where。作用不一样，Having是对组进行过滤  

12.限制结果集行数

   Select top 5* from Employeeorder by Salary desc

   检索按照工资从高到低排序检索从第六名开始一共三个人的信息：select  top 3* from Employee  where Number not in (select top 5 number fromEmployee orderby Salary desc)

oerder by Salary desc

 SQLServer 2005后增加了row_number函数简化实现

13.去掉数据重复

Distinct 是对整个结果集进行数据重复处理的，而不是针对每一列，因此下面的语句并不是会只保留Department进行重复值处理：

Select distinct

Department,SubCompanyfrom

Employee

 14．联合结果集

     简单的结果集联合：

   Select Number ,Name,Agefrom Employee

Union

select FidCardNumber,Name,Age from Employee

   基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相同

   Select Number ,Name,Age,Department fromEmployee

union

select IdCardNumber ,Name,Age,临时工，无部门 fromT_Employee

   2、Union all

      Select Name ,Age from_Employee

Union select Name,Age from T_Employee。Union合并两个查询结果集，并且将其中完全重复的数据合并为一条

      Select Name,Age fromEmployee

Union all

Select Name,Age from T_Employee

Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么久用Union all

15.数据库函数

     1、数字函数

        1>Abs():求绝对值

        2>Ceiling():舍入到最大整数。3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3

Celing->天花板

        3>Floor():舍入到最小整数。3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4

Floor->地板

        4>Round():四舍五入。舍入到“离我半径最近的数”。Round->”半径“。Round(3.1425,2)。

       2、字符串函数

          Len():计算字符串长度

          Lower（）、upper():转小写、大写

          Ltrim():字符串左侧的空格去掉

          Rtrim():字符串右边的空格去掉

          两边都去掉先l再r:ltrim(rtrim(‘   bb    ‘))

          Substring（string,start_postion,length）

参数string为主字符串，start_postion为子字符串在主字符串中的起始位置，length为子字符串的最大长度

Select substring（‘abcdef111’,2,3）

      3、日期函数

         1>Getdate():取当前日期时间

        2>Dateadd(datepart,number,date),计算增加以后的日期。参数date为待计算的日期：参数date为增量：参数datepart为计量单位，可选值见备注。Dateadd(day,3,date)为计算日期date的三天后的日期，而dateadd(month,-8,date)为计算date的八个月之前的日期

        3>Datediff(datepart,startdate,enddate):计算两个日期之间的差额。Datepart为计量单位，可取值参考dateadd。

         4>SelectDateDiff(year,FinDate,getdate()),count(*) from Employee

Group by DateDiff(year,FinDate,getdate())//统计不同工龄的员工的个数

         5>Datepart(datepart,date):返回一个日期的特定部分。统计员工的入职年份个数：select DatePart(year,FinDate),count(*)  from Employee group by DatePart(year,FinDate)

    4、类型转换函数

      1> CAST（expressionas data_type）

      2>Convert(date_type,expression)

       Select FidNumber,

       Right (FidNumber,3) as后三位，

       Cast(right(FidNumber,3)as integer) as后三位的整数形式

       Cast(right(FidNumber,3) as integer)+1 as后三位加1

       Convert（integer,right(FidNumber,3)）/2 as后三位除以2

       From Person

16.

1、空值处理函数

      Isnull（expression,value）:如果expression不为空则返回expression，否则返回value。Selectisnull(FName,’佚名’as姓名 from Employee

   2、Case函数用法1

      单值判断相当于swtichcase

      Case expression

      When value1 thenretrunvalue1

      When value2 thenretrunvalue2

      When value3 thenretrunvalue3

      Else defaultreturnvalue

      End

      例子select

      FName,

      (cast FLevel  when 1 then ‘vip客户’

      when 2 then ‘高级客户’

      when 1 then ‘普通客户’

      else ‘客户类型错误’

      end) as FlevelName

      from Customer

三、索引index

      全表索引：对数据进行检索（select）效率最差的是全表扫描，就是一条条的找

      如果没有目录，查汉语字典就要一页页的翻，而有了目录只要查询目录即可。为了提高检索的速度，可以为经常进行索引的列添加索引，相当于创建目录

       创建索引的方式，在表设计器中点击右键，选择“索引/键”->添加->在列中选择索引包含的列

       使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要更新索引，因此会降低insert、update、delete的速度。只在经常检索的字段上（where）创建索引

       即使创建了索引，仍然有可能全表扫描，比如like。函数、类型转换等。

四。、

1、表连接join     

有客户表（T-Customers）和订单表(T-Order)两个表，客户表字段：id  name age ,订单表字段为：id billnocustomerid,订单表通过customid关联客户表。测试数据见备注

 Select o.billno,c.name,c.age

From T-order as o

Join T-customers as c on o.customerid=c.id

要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄

Select o.billno,c.name,c.age

From T-order as o join T-Customers as c on o.customerid=c.id

Where c.age>15

  要求显示年龄大于平均年龄的顾客购买的订单

Select o.billno,c.name,c.age

From T-order as o join T-Customers as c on o.customerid=c.id

Where c.age>(select avg(age) from T-customer)

 

  Inner join、left join、rightjoin

 2、子查询

     将一个查询语句作为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当做结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。Select * from T2 where FAge<30)

     单值做为子查询：select1 as f1.2.(select min(FYearPublished) from T-Book).(select max(fyearpublised)from t-book) as f4

        只有返回且返回一行、一列数据的子查询才能当成单值子查询。下面的是错误的：select 1 as f1.2.(select fyearpublised from t-book)

         Select *fromT-readerfavorite where fcategoryid=(select fid from t-category where fname=‘story’)

          如果子查询是多行单列的子查询，这样的子查询的结果其实是一个集合，select * from T-reader where fyearofjoin in (select fyearpubilsedfrom t-book)

          限制结果集，返回三行到五行的数据

          Select * from

          ( selectrow-number() over (order by fsalary desc )as rownum,fnumber,fname,fsalarym,fagefrom t-employee)as a

Where a.rownumber>=3 and a.rownumber<=5

 

 

 

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ---------------------- 详细请查看：http://edu.csdn.net/heima/