SQLServer数据库（一）

数据库（DataBase,简DB）

数据在应用程序中以“类.对象”的形式来组织。而在数据库中以“表”的形式来组织。

一行记录（一条数据）———— 一个对象（实体）。

一个表—————— 一个类。

表的列—————— 类的字段（属性）。

数据库管理系统————DBMS

数据库系统————DBS

数据库管理员————DBA   Administrator

系统数据库：master,model,msdb,tempdb

数据库文件分为：数据库文件.mdf 、事务日志文件.ldf

 

数据完整性：

1.实体完整性约束。

2.域完整性约束。

3.引用完整性约束。

4.自定义完整性约束。

 

主键和外键：

主键，primary key ,用于强制表的实体完整性。主键中禁止相同的数据。如果两列或多列组合起来唯一的标识表中的每 一行，则该主键叫“复合主键”。主银拥有：唯一性、最少性、稳定性。

外键：foreign key ,立字段引用其他表的主键。

 

SQLServer数据类型：

类型	数据类型	描述	用途
整型	int	占用4个字节的整数	存储到数据库的几乎所有数值型的数据都可以使用这种数据类型
	smallint	占用2个字节的整数	对存储一些常限定在特定范围内的数值型数据非常有用
	tinyint	占用1个字节的整数	在只打算存储有限数目的数值时很有用
浮点型	real	近似数值类型	一种近似数值类型，供浮点数使用
	float	近似数值类型	一种近似数值类型，供浮点数使用
	decimal	固定精度和范围的数值型数据。使用这种数据类型时，必须指定范围和精度。范围是小数点左右所用存储的数字的总位数，精度是小数点右边存储的数字的位数	通常存储精度要求较高的数据，如货币金额
	numeric	同上	同上
字符型	char	固定长度非Unicode字符数据，最大8000个字节	常用于存储固定长度的少量文本，如身份证号
	varchar	可变长度非Unicode字节数据，有两种形式：varchar(n)或varchar(max)	常用于存储可变长度的少量文本，如学校的英文名称
	text	非Unicode字节数据	常用于存储文章等大文本，如个人的英文简历
Unicode型	nchar	固定长度Unicode字符数据，最大4000字符	常用于存储固定长度的少量文本。
	nvarchar	可变长度Unicode字符数据	常用于存储可变长度的少量文本，如学校的中文名称
	ntext	Unicode字符数据	常用于存储文章等大文本，如个人的中文简历
是/否型	bit	只能是0、1或空值	表示 是/否 值
二进制型	binary	定长的二进制数据	当输入表的内容接近相同的长度时，应该使用这种数据类型
	varbinary	变长的二进制数据	当输入表的内容大小可变时，应该使用这种数据类型
	image	变长的二进制数据	如图片、声音等
货币型	money	固定精度和范围的数值型数据	常用于存储金额
	smallmoney	同money,但小于money的取值范围	同上
日期时间型	datetime	表示日期和时间，可以精确到3.33毫秒	如’2010-1-1 01:01:01’
	smalldatetime	同datetime,精确到分种
特殊类型	timestamp	创建一个数据库范围内的唯一时间戳	一个表中只能有一个timestamp列。在一个数据库中，timestamp值是唯一 的
	uniqueidentifier	一个全局的唯一标识符

精确数字：bit,tinyint,smallint,int,bigint,numeric,decimal

近似数字：float,real

字符串：char,varchar,text

Unicode字符串：nchar,nvarchar,ntext

二进制字符串：binary, varbinary,image

日期和时间：date,time,datetime,smalldatetime

 

 

SQL:结构化查询语言，是针对数据库而言的一门语言。可以创建数据库、数据表。可以针对数据库的数据进行增、删、改、查 等操作，可以创建视图、存储过程，可以赋予用户权限。

 

SQLServer中的运算符与编程语言的区别:

1.比较运算符：等于——“=” ， 不等于——“<>”。

      逻辑运算符：and、or、not

 

use database_name; 用于选定当前SQL操作语句对哪一个数据库生效，默认的是系统数据库master

 

插入数据：

1.单行插入：insert [into] table_name [列名列表，如果是全字段可省]  values (值列表)

into 可以省略，列名不写则插入所有列，多个列名和多个值列表用逗号隔开，分号“;”是终止符，可以省略。

每次只能插入一行，不能为标识列赋值。字符型和日期必须加单引号“ ’ ”。

2.通过select、union关键字插入多行：

insert into table_name (列名列表) 

select 值列表 union --第一条数据

select 值列表 union --第二条数据

..... --最后一条数据的末尾不加union

3.复制表数据：

select、from 关键字：insert into 目标数据表名 (列名列表) select 源表列名列表 from 源数据表名

目标数据表必须预先建好，并且具有相应数据类型的列。查询得到的数据个数、顺序、数据类型，必须与插入的项

保持一致

select、into、from关键字：select 源数据表的列名列表 into 目标数据表名 from 源数据表名

目标数据表会自动创建，并且结构和源数据表一模一样。

如果要在目标数据表中加一列（标识列），则可以这样：

select identity(数据类型一般是int，标识种子.标识增量) as 标识列名,源数据表列名列表 

into 目标数据库表名 from 源数据库表名

这样的目标表也会自动创建，只是多一了列标识列

 

修改数据：

update table_name set 列名1=更新值，列名2=更新值..... where 条件

如果where语句不写，则作用于所有记录

 

删除数据：

delete from table_name where 条件（如：id=100）

如果不写where会清空所有记录，但标识列没有归初始值。

truncate table table_name;

直接清空所有数据表中的数据，标识确也会初始化，但有外键的表无法使用该语句。

 

查询数据：

select 列名列表 from table_name where 查询条件表达式 order by 排序的列名 [asc或desc]

如果不限制where语句，则查询数据表中所有数据，

order by 是用来进行排序的，asc表示从小到大，desc相反，

order by newid() --可以把查询的结果集打乱顺序,即出现随机查询结果

默认是desc。orderby后面可以跟多个列，但是用逗号“,”隔开，这样排序是依column先后顺序来排序。

如：select * from news order by nid,ntid;--这样就是先按nid排序，然后按ntid排序

查询所有行和列：select * from table_name;--“*”表示所有的列

列的别名：select 原列名 as 新列名，列名 = 新列名，新列名=列名，列名 新列名，新列名=列名1+列名2(都是字 符列，这样可以合并多列一起显示) from table_name

查询空值：select * from table_name where column is null --查询该列是null的记录

查询时使用常量：select (‘北新新兴桥’+字符值列表) as 新列名 from table_name --该列每一条记录前都加上了

‘北新新兴桥’这几个字。

查询返回限制行数的记录：select top row_num 列名列表 from table_name --查询前row_num条数据

select top percent num 列名列表 from table_name --查询前百分之num条数据 

 

函数，常用四类：字符串函数、日期函数、数学函数、系统函数。不同类别的函数者可以和select,update,insert,delete

语句使用，where子句中也可以使用。视需求决定。以下的示例都是以select语句作例子。

字符串函数：

函数名	描述	举例
charindex	用来寻找一个指定的字符串在另一个字符串中的起始位置	select charindex(‘name’,’My name is Tom’,1) 返回：4
len	返回传递给它的字符串长度	select len(‘SQL Server课程’) 返回：12
upper	把传递给它的字符串转换为大写	select upper(‘sql’)  返回：SQL
lower	把传递给它的字符串转换为小写	select lower(‘SQL’) 返回：sql
ltrim	清除字符左边的空格	select ltrim(‘   宇‘) 返回：宇(后面的空格保留)
rtrim	清除字符右边的空格	select rtrim(‘  宇  ’) 返回：  宇(后面没有空格)
right	从字符串右边返回指定数目的字符	select right(‘买卖提’,1) 返回：提
left	从字符串左边返回指定数目的字符	select left(‘买卖提’,1)  返回：买
replace	替换一个字符串中的字符	select replace(‘爱不爱’,’爱’,’约’) 返回：约不约
stuff	在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串	select stuff(‘abcdefg’,2,3,’爱不爱我’) 返回：a爱不爱我efg

日期函数：

函数名	描述	举例
getdate	获取系统当前日期时间	select getdate()  返回：当前的日期
dateadd	将指定的数值添加到指定的日期部分后的日期	select dateadd(mm,4,’2009-1-1’),这里的mm是月份，返回:2009-05-01 00:00:00.000
datediff	两个日期之间的指定日期部分的间隔	select dateadd(mm,’2009-1-1’,’2009-5-1’) 返回：4 (后面一个减前一个)
datename	日期中指定日期部分的字符串形式	select datename(dw,’2009-1-1’) 返回：星期四(这里的dw和weekday一样)
datepart	日期中指定日期部分的整数形式	select datepart(dw,’2009-1-1’) 返回：5(这里表示星期四，从星期日开始1-7)

数学函数：

函数名	描述	举例
rand	返回从0到1之间的随机float值	select rand()  返回：0.123412341379645
abs	取数值表达式的绝对值	select abs(-43) 返回：43
ceiling	向上取整，取大于或等于指定数值、表达式的最小整数	select ceiling(43.1) 返回：44
floor	向下取整，取小于或等于指定表达式的最大整数	select floor(43.9) 返回：43
power	取数值表达式的幂值	select power(5,3)  返回:125
round	将数值表达式四舍五入为指定精度	select round(4.3234,1) 返回：4.3000
sign	对于正数返回1,对于负数返回-1,对于0则返回0	select sign(-43)  返回：-1
sqrt	取浮点表达式的平方根	select sqrt(9)  返回：3

系统函数：

函数名	描述	举例
convert	用来转变数据类型	select convert(varchar(5),12345) 返回：字符串形式的’12345’
current_user	返回当前数据库用户的名字	select current_user 返回：你的登录名(一般是dbo)
datalength	返回用于指定表达式的字节数	select datalength(‘中国A联盟’) 返回 ：5
host_name	返回当前用户所登录的计算机名字	select host_name 返回：你所登录的数据库服务器名
system_user	返回当前所登录的用户名称	select system_user 返回：你当前登录的用户名
user_name	从给定的用户ID返回用户名	select user_name() 返回 ：从任意数据库中返回’dbo’

 

模糊查询：

1.通配符：

使用like运算符，只用于字符串。

符号	解释	示例
_	一个字符	a lick ‘c_’，则符合条件的a如cs、cd等
%	任意长度的字符串	a lick ‘c%’,则符合条件的a如cba、ccc等
[]	括号中所指定范围内的一个字符	a lick ‘9w0[1-2]’,则符合条件的a如9w01、9w02
[^]	不在括号中所指定范围内的任意一个字符	a like ‘9w0[^1-2]’,则符合条件的a如9w03、9w09等

例：使用like进行模糊查询

select * from students where studentname like ‘张%’

2.使用between在某个范围内查询：

要实现这种查找，必须知道查找的初值和终值，并且初值必须小于等于终值

select * from table_name where column_name between 初值 and 终值

例：select * from students where score between 60 and 80

如果初值大于终值，不会报错，但没有结果返回

3.使用in在列举值内进行查询：

将列举值方在括号里，用逗号“，”隔开，列举值类型必须与匹配的列具有相同的数据类型。

例：select * from student where saddress in (‘北京’,’广州’,’上海’)

也可以把in关键字和not关键字联合起来用，如：select * from student where saddress not in (‘北京’)

表示的是地址不在’北京’的学生表信息

 

聚合函数：能基于列进行计算，将多个直合并为一个值。对一组值进行计算，并返回计算后的值。

使用聚合函数进行统计值时，将忽略NULL值，但不忽略重复项。

1.sun()，返回表达式中所有数值的总和，只能用于数字列，只能返回一个数值，不能够直接与可能返回多行的列一起 使用来进行查询。

例：select sun(score) from result where studentid=33 --查询学号为33的学生的成绩总和

2.avg(),返回表达式中所有数值的平均值，只能用于数值列。

例：select avg(score) from result where score>=60 --查询及格的平均值。

3.max()和min():max()返回表达式中的最大值，min()返回表达式中的最小值，它们都可以用于数字型，字符型以及 日期/时间类型的列。

例：select avg(score),max(score),min(score) from result where score>=60

--查询及格线上的平均分、最高分、最低分

4.count(),返回提供的组成或记录龄中的计数。可以用于数字和字符类型的列，也可以使用星号’*’作为count()的 表达式。

例：select count(*) from score where score>=60 --查询及格人数的语句 

也可以使用整数类型的值当作表达式，如：

select count(1000) from score where score>=60  --结果将和上面的一样

小提示：count(distinct 表达式),遇到重复数据，只记录一次

使用group by 进行分组查询：

使用group by关键字时，在select列表中可以指定的列是有限制的，

只允许“被分组的列”和“为每个分组返回一个值的表达式”

 

单列分组，例：

--按照课程编号来分组，并显示每个课程的平均分

select couseId,avg(score) as 课程平均成绩 from score 

group by couseId

--查询男女学生人数是多少。

select count(1) as 人数,ssex from student 

group by ssex

--查询每个年级的总人数。

select count(1) as 年级人数，gradeId from student 

group by gradeId

--查询每个科目的平均分，并且按影由低到高的顺序排列显示。

select coursedId,avg(score) from Score 

group by courseId 

order by avg(score) desc

多列分组查询，例：

--按年级进行分组，并分组成男女组，并且以年级进行排序。

select count(1) as 人数,gradeId,sex from student 

group by gradeId,sex 

order by gradeId

 

使用having子句进行分组筛选，用来对分组后的数据进行筛选，将’组’看做’列’来限定条件。

例1：--查询总人数超过15的年级。

select count(1),gradeId from students 

group by gradeId

having count(1)>15

例2：--查询平均分及格的课程信息。

select courseId,avg(score) from score

group by courseId

Having avg(score)>=60

例3：--查询每门音响及格人数和及格的平均分。

select count(1),avg(score),courseId from Score where score>=60

group by courseId

例4：--查询每门课程及格总人数和及格平均分在80分以上的记录。

select count(1),avg(score),courseId from Score where score>=60

group by courseId

having avg(score)>=80

例5：--在按影部门分类的员工表中，查询有多少个员工的工资不低于2000的部门编号。

select 部门编号,count(1) from 员工信息表 where 工资>=2000

group by 部门编号

having count(1)>1

小提示：在select语句中，where，group by，having子句和聚合函数的执行次序如下：

where子句从数据中地掉不符合其搜索条件的数据;group by子句搜集数据到各组，统计函数为各组统计;having子 句去掉不符合其组搜索条件的各组数据行。

多表联接查询：

1.内联接，是最典型、最常用的联接查询，它根据表中共同的列来进行匹配。特别是某个表存在主外键关系时通常会使用

到内联接查询。

1)在where子句中指定联接条件，例：

select Students.sname,Score.courseId,Score.score from Students,Score

where Students.scode=Score.studentId

2)在from子句中使用inner join ... on

select S.sname,C.courseId,C.score from Students as S

inner join Score as C on (S.scode=C.studentId)

inner join 用来联接两个表。inner 可以省略。on用来设置条件。as 指定表的“别名”。

3)用inner join三表内联接：

select S.sname,CS.courseName,C.score from Students as S

inner join Score as C on (S.scode=C.StudentId)

inner join Course as CS on (CS.courseId=C.courseId)

2.外联接，在外联接中参与联接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合联接条件的数据直接返回

到结果中，对那些不符合条件的列，将被填上NULL值后再返回到结果集中。

1)左外联接查询，使用left join ... on 或 left outer join ... on关键字来进行表之间的关联。

2)右外联接查询，使用right join ... on 或 right outer join ... on关键字来进行表之间的关联。

左右都是相对的，是指把关键字指向的那张表当作主表，主表的所有符合查询的数据行都会显示出来，从表的数据行

只有符合主表匹配的才会显示出来，没显示出来的用NULL值代替。

例，下面两个例子效果是一样的：

select S.sname,C.courseId,C.score from Students as S

left outer join Score as C on S.scode=C.studentId

select S.sname,C.courseId,C.score from Score as C

right outer join Students as S on C.studentId=S.scode

 

联结两个查询语句：

　　查询1 union [all] 查询2 --带’all’关键字时，重复数据不乎略。