数据库（class0427,29)

数据库（仓库）
表table(货架)
行 记录
列 字段
主键 表的唯一标示，并且不能为空
外键 创建表之间的关联，如果一个列是外键则在另一个表中必定是主键

sqlserver版本 Express 免费版
Standard 标准版
企业版 不能装在xp上，只能装在server版的操作系统上

服务器名称：
机器名
.
ip地址 127.0.0.1 在网络中的ip地址 192.168.1.100

数据类型
char 固定长度，char(10) 张三 补6个空格
varchar 可变长度
nchar 固定长度 Unicode编码存储 nchar(10) 张三 补 8个空格
nvarchar 可变长度 Unicode编码存储

sex 男/女 nchar(1)

建库
create database shujuku
删除库
drop database shujuku

建表
create table biao
(
)
删表
drop table biao

增
insert [into] biao (lie1,lie2,lie3) values('值1','值2','值3')
删
delete from biao [where 条件]
truncate table biao
改
update biao set lie1 = '',lie2='' [where 条件]

约束
--主键约束
alter table class
add constraint PK_Class primary key(cId)

--唯一约束
alter table class
add constraint UQ_Class_cName unique(cName)

--默认约束
alter table student
add constraint DF_student_sBirthday default (getdate()) for sBirthday

--添加检查约束
alter table student
add constraint CK_student_sAge check (sAge >= 18 and sAge<=25)

--外键约束
alter table student
add constraint FK_student_sClassId foreign key (sClassId) references class(cId)

--删除约束 因为约束在表中存储
alter table student
drop constraint DF_student_sAge

什么是数据库

数据库Database：数据的仓库

仓库里有排货架(表)，货物会分类存放。比如牙膏、牙刷会放到一个排货架上，面包会单独放在一个排货架上。

仓库又分很多种粮仓、武器仓库。

仓库中还会有仓库管理员(DBA)对货物进行管理 从仓库中拿货物需要凭证，取货和进货的人(程序员)

*数据库的发展

文件管理数据 层次、

网状数据库系统

关系数据库系统

面向对象数据库

层次数据库 树形结构 公司部门可以使用此种结构 (1)有且仅有一个结点没有双亲结点，这个结 点称为根结点; (2)其它结点均有且仅有一个父结点。 网状数据库 （1）允许一个以上的结点无双亲；（2）一个结点可以有多于一个的双亲。

数据库概述

DBMS（DataBase Management System，数据库管理系统）和数据库。平时谈到“数据库”可能有两种含义：MSSQLServer、Oracle等某种DBMS；存放一堆数据表的一个分类（ Catalog ）。

不同品牌的DBMS有自己的不同的特点：MYSQL、MSSQLServer、DB2、Oracle、Access、 SQLite 、Sybase等。对于开发人员来讲，大同小异 SQL<>SQLServer<>MSSQLServer。最常见的错误。

除了Access、SQLServerCE、SQLite等文件型数据库之外，大部分数据库都需要数据库服务器才能运行。学习、开发时是连接本机的数据库，上线运行时是数据库运行在单独的服务器。

数据库中的概念

数据库DataBase，不同类的数据应该放到不同的数据库中

便于对各个数据类别的进行个性化管理 避免命名冲突 安全性更高

Table（表）：关系数据库中的关系指的就是表。不同的货物要放到各自的货物架，将这种区域叫做“表”（Table）。不同的表根据放的数据不同进行空间的优化，找起来也方便。 列（Column）、字段（Field）

主键（PrimaryKey）

工号	姓名	部门	入职时间
001	凤姐	员工培训部	2010年7月5日
002	瘦瘦	公关部	2010年8月2日
003	憨憨	开发部	2009年3月5日

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

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

表间关联、外键（ForeignKey）

SQLServer的管理

需要安装SQLServer2005或者SQLServer2008，若要使用SQLServer管理工具进行开发还要安装SQL Server Management Studio，还可以使用VisualStudio进行管理

使用免费的SQLServerExpress版本，Express版本的服务器名称. \SQLEXPRESS，对于开发人员来讲和其他版本没有区别。

SQLServer的两种验证方式：用户名验证和Windows验证，开发时用Windows验证就行。

开发人员关注点在开发上，而不是配置、备份等之上，那是DBA做的事情。

创建数据库，创建表，设置主键

数据库的分离和附加

分类	备注和说明	类型	说明
二进制数据类型	存储非子符和文本的数据	Image	可用来存储图像
文本数据类型	字符数据包括任意字母、符号或数字字符的组合	Char	固定长度的非 Unicode 字符数据
		Varchar	可变长度非 Unicode 数据
		Nchar	固定长度的 Unicode 数据
		Nvarchar	可变长度 Unicode 数据
		Text	存储长文本信息(指针,2G)
		Ntext	存储可变长度的长文本
日期和时间	日期和时间在单引号内输入	Datetime	日期和时间
数字数据	该数据仅包含数字，包括正数、负数以及分数	intsmallint	整数
		floatreal	数字
货币数据类型	用于十进制货币值	Money
Bit数据类型	表示是/否的数据	Bit	存储布尔数据类型

练习

创建一个School

Student id（主键自动编号）

Name Age Sex Address

分离数据库 在需要分离的数据库上点右键-任务-分离

附加数据库

在数据库节点上点右键-附加

SQL语句入门

SQL 全名是结构化查询语言（Structured Query Language），是关系数据库管理系统的标准语言

SQL语句是和DBMS“交谈”专用的语句，不同DBMS都认SQL语法。

SQL语句中字符串用单引号。

SQL语句是大小写不敏感的，不敏感指的是SQL关键字，字符串值还是大小写敏感的

建库、删除数据库、创建表、删除表不仅可以手工完成，还可以执行SQL语句完成，在自动化部署、数据导入中用的很多

简单的Insert语句。

（*） SQL主要分DDL（数据定义语言）、DML（数据操作语言）和DCL （数据库控制语言） 。Create Table、Drop Table、Alter Table等属于DDL，Select、Insert、Update、Delete等属于DML， GRANT 授权、REVOKE 取消授权属于DCL

Insert into class (cName,cDescription) values(‘T001’, ‘这是一个高级班，人数50')
Insert into class (cName,cDescription) values(‘T002’, ‘这是一个高级班，人数60')

--drop database MySchool
create database MySchool
on
(
--括号一定是圆括号
name='MySchool_data',--数据库名称
filename='d:\MySchool_data.mdf',--物理文件名
size=5mb,--初始大小
maxsize=10mb,--最大大小
filegrowth=15% --主文件增长率
)
log on
(
name='MySchool_log',--日志文件名
filename='d:\MySchool_log.ldf',--日志物理文件名
maxsize=4mb,--最大大小
size=2mb,
filegrowth=1mb
)
go

use MySchool
go
--drop table Class
create table Class
(
    cId int identity(1,1) primary key,
    cName nvarchar(50) not null,
    cDesciption text
)
--drop table student
create table Student
(--创建学生信息表
    sId int identity(1,1) primary key,--自动编号
    sClassId int not null,    --班级外键
    sName nvarchar(50) not null,
    sAge int not null,
    sNo numeric(18,0),--身份证号，十八位数字，小数为
    sSex char(2) not null,
    sEmail varchar(50)
)

使用sql语句创建数据库和表

create table Class
(
    cId int identity(1,1) primary key,
    cName varchar(50) not null,
    cDescription text
)
go

--drop table student
create table Student
(--创建学生信息表
    sId int identity(1,1) primary key,--自动编号
    sClassId int not null,    --班级id，外键
    sName nvarchar(50) not null,
    sAge int not null,
    sNo numeric(18,0),--身份证号，十八位数字，小数为0
    sSex nchar(1),
    sBirthday datetime
    
)

创建表练习

创建学生成绩表Score

scoreId,studentId,english,math

创建老师表Teacher

tId,tName,tSex,tAge,tSalary,tBirthday

数据插入

Insert语句可以省略表名后的列名，但是不推荐。

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

主键：insert into Student(Name,Age) values('lily',38);

数据更新

更新一个列：update Student set sSex = ‘男’

更新多个列： update Student set sSex ='女',sAge = 18,sBirthday='1989-8-8'

更新一部分数据： update Student set sClassId= 4 where sClassId = 1，用where语句表示只更新Name是’tom’的行，注意SQL中等于判断用单个=，而不是==。 Where中还可以使用复杂的逻辑判断update Student set sAge=30 where sName='华佗' or sAge<25 ，or相当于C#中的||（或者）

所有学生的年龄加1update Student set sAge = sAge + 1 update Student set sClassId=6 where (sAge>20 and sAge<30) or(sAge=50) Where中可以使用的其他逻辑运算符：or、and、not、<、>、>=、<=、!=（或<>）等

练习1：给刘备的英语成绩加10分

练习2：考试题偏难，所有人的成绩加5分

练习3：所有女学生的成绩加5分

删除表中全部数据：DELETE FROM Student。

Delete只是删除数据，表还在，和Drop Table不同。

Delete 也可以带where子句来删除一部分数据：DELETE FROM Student WHERE sAge > 20

Truncate清空表中的数据没有条件，和delete的区别不存日志，清空自动编号

插入 几条老师信息 和成绩

练习1：给刘备的英语成绩加10分

练习2：考试题偏难，所有人的成绩加5分

练习3：所有女学生的成绩加5分

删除工资大于2000的老师 删除所有老师

删除数据时候 把自增长列的值还原成种子

约束

数据库约束是为了保证数据的完整性(正确性)而实现的一套机制

非空约束

主键约束(PK) primary key constraint 唯一且不为空

唯一约束 (UQ)unique constraint 唯一，允许为空，但只能出现一次

默认约束 (DF)default constraint 默认值

检查约束 (CK)check constraint 范围以及格式限制

外键约束 (FK)foreign key constraint 表关系

--添加主键约束
alter table Score
add constraint  PK_Score primary key(sId)

 
--添加唯一约束
alter table student
add constraint UQ_student unique(sNo)


--添加默认约束
alter table student
add constraint DF_student default('男') for sSex

--添加检查约束
alter table student
add constraint CK_student check(sAge >=18 and sAge <=100) 

--添加外键约束（主键表Class 外键表student）
alter table student
add constraint FK_student
foreign key(sClassId) references Class(cId) --外键student表中的sClassId来references引用主键表中的cid
--级联删除
--on delete cascade on update cascade

--删除约束
alter table student
drop constraint FK_student

约束练习

Teacher表中

tSex 控制只能是男 女，默认男

tAge 在30-40之间 默认30

Score表中

studentId 是外键 先要把Student表中的sId设置为主键

--在学生表中删除有成绩的学生

--成绩表中添加 学生表中没有的学生

数据检索

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

简单的数据检索 ：SELECT * FROM Student

只检索需要的列 ：SELECT sName FROM Student 、SELECT sName,sAge FROM Student

列别名：SELECT sName AS 姓名,sAge AS 年龄,sBirthday AS 出生日期 FROM Student

使用where检索符合条件的数据：SELECT sName FROM Student WHERE sSex=‘女’。

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

insert into Class (cName,cDescription) values ('高一一班','快班')
insert into Class (cName,cDescription) values ('高一二班','中班')
insert into Class (cName,cDescription) values ('高一三班','慢班')
insert into Class (cName,cDescription) values ('高二一班','快班')
insert into Class (cName,cDescription) values ('高二二班','中班')
insert into Class (cName,cDescription) values ('高二三班','慢班')

insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'刘备',20,'男',123456789012345678,'1987-5-6')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'关羽',19,'男',123456789012345671,'1988-8-6')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'张飞',18,'男',123456789012345672,'1989-5-19')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'曹操',22,'男',123456789012345673,'1985-12-6')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'夏侯惇',22,'男',123456789012345674,'1985-3-6')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'华佗',50,'男',12345678901234565,'1957-1-16')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'甄姬',18,'女',12345678901234565,'1989-8-8')

insert into Score (studentId,english) values(1,90)
insert into Score (studentId,english) values(2,90)
insert into Score (studentId,english) values(3,59)
insert into Score (studentId,english) values(4,100)
insert into Score (studentId,english) values(5,60)
insert into Score (studentId,english) values(6,0)
insert into Score (studentId,english) values(7,80)

Top Distinct

Top 获取前几条数据

获得年纪最小的5个学生

获得年纪最大的10%的学生

Distinct 去除重复数据

select distinct sName from student select distinct sName,sAge from student DISTINCT是对整个结果集进行数据重复处理的，而不是针对某一个列

Select top 5 sName,sAge from student
Select top 30 percent sName,sAge from student  非四舍五入，返回最大整数 2.1 返回3

聚合函数

SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量）

平均成绩select avg(english) from score

男学生出生日期的最大值和最小值：SELECT select max(sBirthday),min(sBirthday) from student where sSex='男'

select count(*) from student;

select max(english) from score;

select min(english) from score;

select avg(english) from score

select sum(english) from score;

select count(*) from student where sSex=‘女’;

带条件的查询

Select …from…where

查询没有及格的学生的学号

查询年龄在20-30岁之间的男学生

Between…and …在之间

查询年龄在20-30岁之间的男学生

查询成绩在80-90分之间的所有学生

查询班级id为1，2，3的所有学生

select sName,sAge from student where sClassId=1 or sClassId=2 or sClassId=3

select sName,sAge from student where sClassId in (1,2,3)

select studentId from score where english < 60
select sName,sAge,sSex from student where sAge >=20 and sAge <=30 and sSex ='男'

select sName,sAge,sSex from student where sAge between 20 and 30 and sSex ='男'

带条件的查询-模糊查询

查询所有姓张的同学

Select * from student where left(sName,1)=‘张‘ 看上去很美,如果改成查询名字中带亮的学生怎么做？

换一种做法 like

Select * from student where sName like ‘张%’ 会吧所有姓张的都查询到，现在我想查询姓张并且名字是一个字的学生？

Select * from student where sName like ‘%亮%’

通配符 %多字符匹配的通配符，它匹配任意次数（零或多个）出现的任意字符

通配符_ 单字符匹配，它匹配单个出现的字符 [] 只匹配一个字符 并且这个字符必须是[]范围内的 [0-9] [a-z] [a,b,c]

空值处理

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

select * from score where english = null ；

select * from score where english != null ；都没有任何返回结果，因为数据库也“不知道”。

SQL中使用is null、is not null来进行空值判断： select * from score where english is null ； select * from score where english is not null ；

数据排序

ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）。 按照年龄升序排序所有学生信息的列表：SELECT * FROM Student ORDER BY sAge ASC

按照英语成绩从大到小排序，如果英语成绩相同则按照数学成绩从大到小排序 ：SELECT * FROM Score ORDER BY english DESC,math DESC

ORDER BY子句要放到WHERE子句之后 ： SELECT * FROM Score where english>=60 and math>=60 ORDER BY english DESC,math DESC

数据分组

select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息

按照班级进行分组统计各个班级的人数： select sClassId,count(sName) from student group by sClassId

GROUP BY子句必须放到WHERE语句的之后

没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 （聚合函数中除外） 错误： select sClassId,count(sName),sAge from student group by sClassId 正确： select sClassId,count(sName),avg(sAge) from student group by sClassId

可以使用having子句限制返回的结果集。group by 子句可以将查询结果分组，并返回行的汇总信息. 按照group by 子句中指定的表达式的值分组查询结果。

Having语句

Having 是Group By的条件对分组后的数据进行筛选

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

查询班级人数超过三个人的班级

select sClassId,count(sName) from student group by sClassId having count(sName)>3

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

select sClassId,count(sName) from student where count(sName)>3 group by sClassId
聚合函数不应出现在WHERE 子句中


select sClassId,count(sName) from student group by sClassId having count(sName)>3

Group by 前可以有where，是对筛选过后的数据进行分组
select sClassId,count(sName) from student where sSex='男' group by sClassId 

select sClassId,count(sName) from student group by sClassId having sAge>30
//错，having是对分组后信息的过滤，能用的列和select中能用的列是一样。
//having无法代替where。

联合结果集 

简单的结果集联合：

select tName,tSex from teacher union

select sName,sSex from student

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

select tName,tSex,-1 from teacher union

select sName,sSex,sClassId from student

 Union all

select tName,tSex from teacher union

select sName,sSex from student

UNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条

select tName,tSex from teacher union all

select sName,sSex from student

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

insert into teacher (tName,tSex,tAge,tSalary) values('罗永浩','男',30,3000)
insert into teacher (tName,tSex,tAge,tSalary) values('俞敏洪','男',40,900)
insert into teacher (tName,tSex,tAge,tSalary) values('李开复','男',50,1000)
insert into teacher (tName,tSex,tAge,tSalary) values('张飞','男',60,10000)

案例1

要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩

select 'english最高成绩',max(english) from score union all

select 'english最低成绩',min(english) from score union all

select 'english平均',avg(english) from score

案例2

查询每位老师的信息，包括姓名、工资，并且在最后一行加上平均工资和最高工资

select tName,tSalary from teacher union all

select '平均工资',avg(tSalary) from teacher union all

select '最高工资',avg(tSalary) from teacher

*一次插入多条数据

insert into Score(studentId,english,math)

select 1,80,100 union

select 2,60,80 union

select 3,50,59 union

select 4,6,89 union

select 5,59,100

*一次插入多条数据

把现有表的数据插入到新表（表不能存在）

--select * into newStudent from student

--把现有表的数据复制到一个已存在的表

--insert into backupStudent select * from students

字符串函数（*）

LEN() ：计算字符串长度

LOWER() 、UPPER () ：转小写、大写

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

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

LTRIM(RTRIM(' bb '))

LEFT()、RIGHT() 截取取字符串

SUBSTRING(string,start_position,length) 参数string为主字符串，start_position为子字符串在主字符串中的起始位置，length为子字符串的最大长度。

SELECT SUBSTRING('abcdef111',2,3)

日期函数

GETDATE() ：取得当前日期时间

DATEADD (datepart , number, date )，计算增加以后的日期。参数date为待计算的日期；参数number为增量；参数datepart为计量单位，可选值见备注。

DATEADD(DAY, 3,date)为计算日期date的3天后的日期，而DATEADD(MONTH ,-8,date)为计算日期date的8个月之前的日期

DATEDIFF ( datepart , startdate , enddate ) ：计算两个日期之间的差额。 datepart 为计量单位，可取值参考DateAdd。

统计不同入学年数的学生个数：select DateDiff(year,sInDate,getdate()) ,count(*) from student Group by DateDiff(year,sInDate,getdate())

DATEPART (datepart,date)：返回一个日期的特定部分

统计学生的生日年份个数：select DatePart(year,sBirthday),count(*) from student group by DatePart(year, sBirthday)

 

Datepart可选值
取值    别名    说明
year    yy,yyyy    年份
quarter    qq,q    季度
month    mm,m    月份
dayofyear    dy,y    当年度的第几天
day    dd,d    日
week    wk,ww    当年度的第几周
weekday    dw,w    星期几
hour    hh    小时
minute    mi,n    分
second    ss,s    秒
millisecond    ms    毫秒

类型转换函数

CAST ( expression AS data_type)

CONVERT ( data_type, expression)

Select ‘您的班级编号’+ 1 错误这里+是数学运算符

SELECT FIdNumber, CAST(RIGHT(sNo,3) AS INTEGER) as 后三位的整数形式,

CAST(RIGHT(sNo,3) AS INTEGER)+1 as 后三位加1,

CONVERT(INTEGER,RIGHT(sNo,3))/2 as 后三位除以2

FROM student

空值处理函数

执行备注中的代码

ISNULL(expression,value) ：如果expression不为空则返回expression，否则返回value。

select studentId,isnull(english,0) from score

练习

创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。

要求：

输出所有数据中通话时间最长的5条记录。Top 5 datediff order by

输出所有数据中拨打长途号码（对方号码以0开头）的总时长。

like、sum

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

输出本月拨打电话次数最多的前三个呼叫员的编号.group by ,count(*)