！！！ SQL 数据库开发基础 传智！

SQL数据库开发和ADO.net

 

 

DBMS 数据库管理系统

1.分类 Database(不同类放到不同的数据库里)

2,表table (列名(人的字段） 每一行就是一个对象（人）)

 

 

主键(Primary)： （唯一标识行，避免重复字段分不清楚）

1）业务主键（不常用很难保证不重复） 身份证号，工号...

2）逻辑主键（常用） （没有任何意义完全给计算机看的，工 作人员不会去管它的） 流水号（不是工号，工号可以被重复利用给别 人）

 

外键（ForeignKey）： 主键表的外键指向关联表的主键

 

 

 

 

SQLServer的管理

Express 免费版

服务器名称 .(代表本机装的) /local/sql服务器IP地址

身份登入 windows / Sqlserver

建立数据库--新建表--设计表--编辑表

 

 

 

数据类型

                                                                                                bit（0,1）相当于布尔

char（10） （如果填的数据不满足自身的长度，会用字符填 充，而varchar(可变字符串)则是放多少就是多少）

datatime int（32位） bigint(64位)

nvarchar(50)(“无限大的字符串” 可能含有中文，日 文...)

varchar（50）（不含有中文...等信息的字段类型）

 

 

 

 

 

SQL基础

SQL语句（和数据库交谈的语句国际标准的语句，不论什么数据库都得认这 个语句）

字符串：用单引号 书写：语句内容大小写不敏感，但本身放入的值是大小写敏感的

Creat table mydb int not null ,name nvarchar(50) 创建一个表

drop table mydb 删除一个表 insert into mydb(name,)

 

SQL主要分： DDL(数据定义语言) 和 DML（数据操作语言） 两类

Creat table drop table等属于DDL， insert select等对数据本身进行 操作的属于DML 

主键设置 (两种类型)：int（或bigint）整数和uniqueidertifier(又称 Guid.UUID)

一般用id做主键名字就行；

int（或bigint）整数自动增长：再设计中标识规范设为是主键值将自动增 长，可以改增长跨度但一般不需要改；一个标只能有一个标识列，解决并发 事情，防止两个人同时往这个表里填数据

优缺点

int（或bigint）整数优点：占用空间小，生成过程不需要开发人员干预； 好读 缺点：效率低数据导入导出困难；

GUID优点：效率高不需要借助其他操作；GUID (取之不尽用之不竭) 缺点：占用空间大，不好读； 业务逐渐倾向于GUID C#代码显示Duid id=GUID.newguid()

 

标识列表添加数据：

insert into mydb(,列名,,)value(值,,,) 列明可 以省略知道就行，尽量不要省略,省略对id的操作,因为是自增长列, 

Guid列表添加操作添加:insert into mydb(id,列名,,)value(newid(),值,,,) 需 要开发人员干预 ，操作不用指定主键 每个字段都可以设默认值：默认值可以是newid()可以自动生成，但很少这 么用 修改：

更新数据 Update t_name Set 列=‘值’，，Age=Age++//表达式 Update t_name Set name=n'青年人' where Age<>20 中文前面最好加n可 能会出错 <>不等于号 or 或 between and并且 not非

删除数据

Delete from t_name where id=3 清空数据（与Drop不同）

 

 

 

检索select（主要语句）

一：select*from t_name

select 列名(字段名) as 姓名 from t_name where code=3 //as别名 设置，Where过滤语句

备注：跟表不相关的都可以，非常灵活：

select getdate()当前时间 select 1+2+”“+”cs23“ 可计算

二:聚合函数（对某个字段进行操作计算，对结果进行的操作）：

select count（*）,Max（gongzi）,min(),sum(),Aug() from where gongzi>5000

三：数据的排序

select*from t_name where id=2 order by Age asc(升序由小到大) ，工 资desc（降序） 先按Age排序结果再按工资排序

注意：where在 order by 之前

四：模糊查询(通配符查询)一般有两种

一种：单字符通配符"_"半角下划线

select*from t_name where name like"_emcn"

第二种：多字符通配符半角百分号”%“他匹配任意次数（0或多个）出现的任 意字符 "k%"匹配以k开头的任意长度的字符串

select*from t_name where name like"k%"

检索姓名中包含字母”n“的员工信息 select*from t_name where name like"%n%"

 

 

 

Null:

在sql中表示不知道而不是表示没有值 ,有Null参与的运算结构都是null（ 不知道） 如何查询（is Null/is not Null）

select*from t_name where name is Null

多值匹配 select*from t_name where Age =30 or Age=50 or Age=20

select*from t_name where Age in[23,25,34]

select*from t_name    where   Age >30  and  Age30   group by   chengji    

select不能使用没有出现在group by子句里面的字段（列名） ，必须是聚 合函数和group by中出现的字段 group by 按照某个字段进行分组后查询，having子句必须放在where之后

 select count(*) from t_name where Age group by age having count (*)>1 聚合函数不能出现在where子句中，having必须出现在group by后面， having是对分组后结果操作的，字段必须是和select中能用的是一样， where是对原始数据的过滤

 

限制结果集范围（行数）

select  top 10  from t_name order by gongzi desc

子查询（常用于分页查询）：select top3 id from t_name where id not in(selec top 5 id from t_name order by gongzi desc)

抑制重复数据（distance去重查询）

selec distinct from t_name order by gongzi desc

注意：过滤的是整行重复的

联合结果集（Unoin all）

selec*from t_临时工

Union

selec*from t_正式工

将两个结果合为一个结果（两个表的所有结果） 把重复数据自动去除相当 于distinct

注意：列名（字段）个数一样和类型相同 个数不足需要补足：

selec name,bumrnfrom t_临时工

select name ，’临时工无部门'from t_正式工  all全部显示

selec*from t_临时工

Union all

selec*from t_正式工

注意：all全部显示,无特殊理由不要去除all

 

案例： 查询所有员工工资最大值和最小值，区分临时工和正式工

select '正式员工‘,max(Age) from t_name

Union all

select '正式员工‘,min(Age) from t_name

Union all 

select '正式员工‘,max(Age) from t_name

Union all

select '正式员工‘,min(Age) from t_name

案例2： 查询美每位式员工的信息，包括工号、工资，并且在最后一行加上所有员工 工资额合计。

select gonghao ,gongzi from t_name  

Union

select '工资合计',sum(gongzi)from t_name

 

 

数据库函数

 

ABS（）：                       求绝对值 selectABS（-3）

CELLING():                    舍入到最大整数 3.33将被舍入为4、2.89将会舍入为3、-3,61将 被舍入为-3，celling --天花板 FLOOR()舍入为最小整数 3.33将被舍入为3、2.89将会舍入为2、-3,61将被 舍入为-4 floor--地板

ROUND()                       四舍五入。 舍入到离我“半径”最近的数

round()--                      半径 round（3.1415926，0） 两个参数后一个参数是精度

LEN（）：                       计算字符串的长度

LOWER()/UPPER();       转小写、大写

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

RTRIM()                        字符串右侧的空格去掉

LTRIM(RTRIM())          去掉两边空格

SUBSTRING（’‘，，）

GETDATE():当前日期 DATEPART（datepart.number,date） DATE ADD(getdate,-3)

DATE DIFF(差额代表数,开始日期,最后日期)

select Date Diff(year,FinDate,getdate())count(*)from T_name group by Date Diff(year,FinDate,getdate()) DATEPART（datepart,date） 返回一个日期的特定部分

 select Datepart(year,FinDate)count(*) from t_name group by Datepart(year)

 

 

 

 

类型转换函数

CAST（expxession被转换的表达式 As date_type数据类型）

CONVERT（date_type,expxession）

SELECT Fldnumber RIGHT(Fldnumber,3)AS 后三位

CAST(RIGHT(Fldnumber,3)AS INNERGER)+AS 后三位的整数形式

CAST(RIGHT(Fldnumber,3)AS INNERGER)+1 AS 后三位加1

CONVERT(INNERGER.RIGHT(Fldnumber,3)/2 AS 后三位除以2 备注：所有函数都可以写字段

 

 

 

 

 

流控函数（控制处理函数）

isnull (expression,value)不为空则返回expression否则返回value

 

select isnull (Fname,'佚名'）as 姓名from T_employee

 

 

CASE函数应用法

单值判断，相当于switch case
case expression
When value1 then returnvalue1
When value2 then returnvalue2
When value3 then returnvalue3
else defaulttreturnvalue
End

 

例子：select Fname,

(Case flevel
when 1 then 'VIP客户'

 

when 2 then '高级客户'

 

when 3 then '普通客户'
else'客户类型错误'
END)as FlevelName from T_Customer

 

 

练习一：表中有ABC三列，用SQL语句实现；当A列大于B列时选择A列否则选
择B列，当B列大于C列时选择B列否则选择C列
select (case when a>b then a else b end),(case when b>c then b
else c end)

 

 

练习二有一张表，记录比赛成绩
Date Name Score
2008-8-8 拜仁 胜
2008-8-9 奇才 胜
2008-8-9 湖人 胜
2008-8-10 拜仁 负
2008-8-8 拜仁 负
2008-8-12 七彩 胜
要求输出下面格式
Name 胜 负
拜仁 1 2
湖人 1 0
奇才 2 0
注意在中文字符串前面加‘N’,比如N‘胜’
select Name，
Sum(
case Score
when N'胜' then 1
else 0
end
)as 胜，

 

Sum(
case Score
when N'负' then 1
else 0
end
)as 负，
from T_Score group by Name

 

 

练习三

 

单号 金额
Rk1 10
Rk2 20
Rk3 -30
Rk4 -10
将上面的表输出为如下格式：
单号 收入 支出
Rk1 10 0
Rk2 20 0
Rk3 0 30
Rk4 0 10
select Fnumber，
(
case
when Famount>0 then Famount
else 0
end
)as 收入，

 

(
case
when Famount<0 then ABS(Famount)
else 0
end
)as 支出，
from t_test 1

 

终极练习：创建一张表，记录电话工作员的电话流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间、建表、插数据等最后都自己写SQL语句
要求：
输出所有数据中通话时间最长的5条记录。    order by /date diff
输出所有数据中拨打长途号码（对方号码以0开头）的总时间长    like、sum
输出本月通话总时长最多的前三个呼叫员的编号。    date diff(month...),sum.orderby
输出本月拨打电话次数最多的前三个呼叫员的编号      group by ，count(*)
输出所有数据的拨号流水，并且在最后一行添加总呼叫时长
1）呼叫员编号、对方号码、通话时长
2）......
3)汇总【市内号码总时长】【长途号码总时长】

 

 

输出所有数据中通话时间最长的5条记录。
select top 5from t_callrecords1 order by datediff(second,Startdatetime,enddatetime) desc

输出所有数据中拨打长途号码（对方号码以0开头）的总时间长
select sum(datediff(second,Startdatetime,enddatetime))from t_callrecords1 where telnum like'0%'

输出本月通话总时长最多的前三个呼叫员的编号。

select datediff(month,convert(datetime,2010-06-01)),convert(datetime,'2010-07-02')),datediff(month,convert(datetime,2010-06-15)),convert(datetime,'2010-07-02'))

 

select Callernumber,telnum,datediff(month,stardatetime,getdate())from t_callrecords1

 

select*from t_callrecords1

where datediff(month,startdatetime,getdate())=0

 

 

select top3 allernumber from t_callrecords1
where datediff(month,startdatetime,getdate())=0
group by callernumber
order by sum(datediff(second,dtartdatetime,enddatetime))desc

 

输出本月拨打电话次数最多的前三个呼叫员的编号
select top 3 callernumber,count(*）from t_callrecords
where datediff(month,startdatetime,getdate())=0
group by callernumber
order by count(*)desc

输出所有数据的拨号流水，并且在最后一行添加总呼叫时长
select callernumber,telnum,datediff(second,startdatetime,enddatetime)
from t_records1
union all
select '汇总'
convert（varchar(50),
sum((
case
when telnum not like'0%' then datediff(second,startdatetime,enddatetime)
else 0
end
))
)as 市内通话，
sum((
case
when telnum like'0%' then datediff(second,startdatetime,enddatetime)
else 0
end
))
)as 长途通话，
from t_callrecords1
))

 

索引Index
1，全表扫描（了解）：对数据进行检索（select）效率最差的就是全表扫描，也就是一条条的找
打个比方，翻看字典，如果没有目录我们要一页一页的查找，而有了目录查询目录即可，为了提高检索的速度，可以为经常检索的列添加索引，相当于创建目录。
2，创建索引的方式：在表设计器中右键，选择“索引/键”——添加——在列中选择索引包含的列
3索引优缺点：使用索引提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。所以尽量只在经常检索的字段上（where）创建索引

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

 

 

JOIN
表连接join(关联查询)

 

练习说明：有客户表（t_kehu）和订单表（t_dingdan）两个表，客户表字段为：Id、Name、Age,订单表字段为：Id、Billno、customerld,订单表通过customerld关联客户表。

select o.Billno,C.name,c.Age
from T_dingdan as o
join t_kehu as c on o.customerld=c.id

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

备注：其他形式：Innner Join 、left join 、right join

 

 

 

 

子查询

概念：讲一个查询语句作为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当做结果集的查询语句被称为子查询，所有可以使用表的地方几乎都可以使用子查询来代替。select*from（select*from t2 where fage<30）

 

单值作为子查询：select 1 as f1,2,(select min(FYearpublished)from t_book) as f4
只有返回且仅返回一行/一列数据的子查询才能当成单值子查询。下面是错误的：select 1as fi,2,(select fyearpublished from t_book)
select*from t_readerfavrite where fCategoryld=(select fld from t_category where fname='story')

 

1、如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合
select*from t_reader
where fyearofjoin in(2001,2003) in()//在（此）范围内
select*from t_reader
where fyearofjoin in
(
select Fyearpublished from T_book
)
2、限制结果集。返回第三行到第五行的数据
select*from
(
select Row_number() over(order by Fsalary desc)AS rownum,fnumber,fname,fsalary,fage from t_employee
)as a
where a.rowmun=>3 and a.rownum<=5

 

Row_number开窗函数 行号  可代替top
备注：把查询结果当做子查询最常用的方法——分页查询
注意：只能出现在select 、 order by 中 不能出现在Where中