SQLServer数据库教程

SQL Server是由Microsoft开发和推广的关系数据库管理系统(DBMS),它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的,并于1988年推出了第一个OS/2版本。Microsoft SQL Server近年来不断更新版本,1996年,Microsoft 推出了SQL Server 6.5版本;1998年,SQL Server 7.0版本和用户见面;SQL Server 2000是Microsoft公司于2000年推出,目前最新版本是2015年份推出的SQL SERVER 2015。

SQLServer特点

  • 1.真正的客户机/服务器体系结构。
  • 2.图形化用户界面,使系统管理和数据库管理更加直观、简单。
  • 3.丰富的编程接口工具,为用户进行程序设计提供了更大的选择余地。
  • 4.SQL Server与Windows NT完全集成,利用了NT的许多功能,如发送和接受消息,管理登录安全性等。SQL Server也可以很好地与Microsoft BackOffice产品集成。
  • 5.具有很好的伸缩性,可跨越从运行Windows 95/98的小型电脑到运行Windows 2000的大型多处理器等多种平台使用。
  • 6.对Web技术的支持,使用户能够很容易地将数据库中的数据发布到Web页面上。

SQL Server提供数据仓库功能,这个功能只在Oracle和其他更昂贵的DBMS中才有。

SQL Server 2000与以前版本相比较,又具有以下新特性 :

1.支持XML(Extensive Markup Language,扩展标记语言)
2.强大的基于Web的分析
3.支持OLE DB和多种查询
4.支持分布式的分区视图

~~~~~~~

SQLServer数据库 #

#1. 为什么需要数据库

1.1 数据的存储方式 #

  1. 大脑记忆
  2. 记录在纸上
  3. 存放在计算机的内存里
  4. 存放成硬盘上的文件

    1.2 数据库的优势 #

  5. 可以存储大量数据,查询非常方便
  6. 可以保证数据的一致性和完整性
  7. 方便共享和传播
  8. 通过数据分析可以获得不同维度的有用信息

2. 数据库的基本概念 #

2.1 实体 #

只要是在客观世界存在的、可以被描述出来的都是实体

2.2 数据库(DB) #

  • 数据库就是数据的仓库,可以存放结构化的数据

    2.3 数据库管理系统(DBMS) #

    是一种系统软件,提供操作数据库的环境,可以通过数据库管理系统对数据进行插入、修改、删除和查询等操作。

    2.4 数据表 #

    格式相同的实体以结构化的方式存储在数据库的表里

    2.5 数据冗余 #

    如果不冗余会增加数据查询的复杂性,则数据库中可以允许有一定数量的冗余数据

    2.6 数据完整性 #

    数据数据库中的数据不准确,不一致,那么就说数据不具有完整性

3. 数据库的安装 #

3.1 安装要求 #

3.2 安装过程 #

3.3 服务的启动方式 #

  1. 通过操作系统的服务启动
  2. SQL Server Management Studio中启动

    3.4 连接服务器 #

    3.5 数据库 #

    3.5.1 系统数据库 #

  3. master 记录数据库系统信息
  4. model 系统创建所有数据库的模板
  5. msdb Sqlserver代理用于执行报警、作业调度等自动化任务
  6. tempdb 用于保存临时对象和中间结果集的工作空间,每次启动都会重新生成

    3.5.2 用户数据库 #

    就是用户自行建立的数据库

    3.6 数据库权限 #

    3.6.1 登录名 #

    用以登录SQL Server数据库服务器,仅仅能登录进去,但不具备任何权限

    3.6.2 数据库用户 #

    登录成功后可以访问具体的用户数据库

    3.6.3 建立登录名 #

  7. 新建可以登录服务器的登录名,与此同时还可以设置此登录名对应的服务器角色,映射为数据库的用户并设置数据库权限
  8. 如果创建用户名的时候没有创建用户,则可以在数据库中单独创建用户并关联登录名。同一个数据库中只能有一个用户关联同一个登录名

    sa 账户属于SQL Server自带的超级管理员用户,新建用户名后应该把sa禁用或删除掉以保证服务器的安全

4. 数据库物理操作 #

4.1 数据库物理文件组成 #

一个数据库至少包含一个数据库文件和一个事务日志文件

  1. 数据文件 .mdf或.ndf
  2. 日志文件.ldf

4.2 创建数据库 #

###需求说明:

  • 数据库名称:school
  • 物理文件:D:\school
  • 数据文件:初始大小为10MB,增量为5mb,不限制增长
  • 日志文件:初始大小为1MB,增量10%,最大值为100MB
  • 其他选项:自动收缩,并且不创建统计信息

4.3 收缩数据库 #

如果因为删除数据而导致数据库空间空闲太多的情况,这可以就需要减少分配给数据库文件和事务日志文件的磁盘空间以免浪费磁盘空间。

4.4. 分离和附加数据库 #

如果要移动数据库到其它服务器上的时候可以使用分离和附加数据库

4.4.1 分享数据库 #

4.4.2 附加数据库 #

4.5. 备份数据库 #

为防止数据丢失或损坏我们可以定期备份数据库,以便在需要的时候还原

4.5.1 备份 #

4.5.2 还原 #

5. 数据表 #

  • 表是数据库中包含所有数据的数据库对象,也是其它对象的基础。
  • 表定义是一个列的集合,数据在表中是按行和列的格式组织的,用来存放数据
  • 行也称为记录用来存放一个个实体,列称为字段用来描述实体的某一个属性
    学生管理系统

5.1 数据完整性 #

为了实现数据完整性,需要检验数据库表中的每行和每列数据是否符合要求
在创建表的时候,应该保证以后的数据输入是正确的,错误的数据不允许输入

5.1.1 域完整性 #

5.1.1.1 数据类型 #

不同的字段需要设置为各种合适的类型,比如年龄就是整数类型

请大家思考 电话号码、性别、工资分别应该用什么数据类型存储?

5.1.1.2 检查约束 #

通过指定一些表达式从而让列的值符合某个定义

规定学生年龄必须大于0岁并且少于100岁

5.1.1.3 默认值 #

默认值是指如果用户没有指定值的情况下会记录的此字段指定一个提供一个预先设定的值

可以把居住地默认值设置为北京

5.1.1.4 非空约束 #

我们可以指定某个字段不能不输入,必须提供一个非空的值

姓名字段不能为空

5.1.2 实体完整性 #

5.1.2.1 主键约束 #

5.1.2.1.1 主键 #

表中一列或者几列组合的值能用来唯一标识表中的每一行,这样的列或者列组合称为表的主键,主键表的数据不同重复。

如果两列或者多列组合起来唯一标识表中的每一行,则该主键又称为”组合键”

主键的选择标准

  1. 最少性 尽量选择单个键作为主键
  2. 稳定性 ,由于主键是用来在两个表间建立联接的,所以不能经常更新,最好就不更新
    5.1.2.1.2 外键 #

    成绩表中的学生ID应该在学生表中是存在的
    我们应该让成绩表中的ID只能引用学生表中的ID,它们的值应该是一一对应的,也就是说成绩表中的ID是成绩表中的外键,对应学生表的主键 ,这样就可以保证数据的引用完整性

    5.1.3 唯一约束 #

    唯一约束是指某个字段值是唯一的,在所有的记录中不能有重复的值.

    学生的身份证号可以设置为唯一约束

5.1.4 标识列 #

  • 当表中没有合适的列作为主键时可以考虑增加标识列,标识列是一个无实际业务含义的列,仅仅用来区分每条记录。
  • 标识列的值是自动生成的,不能在该列上输入数据

    思考: 如果标识列id的初始值为1,增长量为3,则输入3行数据以后,再删除1行,下次再输入数据行的时候,标识值自动插入的值是多少?

5.1.3 引用完整性 #

5.1.3.1 外键约束 #

一个表的外键必须引用另一个表的主键,比如成绩表中的学生ID会引用学生表的主键,课程ID会引用成绩表的主键

  • 主表没有记录,子表中不能添加相应的记录
  • 修改和删除主表记录不能让子表记录孤立,必须相应修改和删除
  • 5.2 数据操作 #

    5.2.1 创建学生表 #

    use studb;
    go
    create table student
    (
    id int not null identity(1,1),
    name varchar(10),
    birthday datetime
    )
    

    5.2.2 修改学生表,增加身份证号字段 #

    alter table student add idcard varchar(15)
    alter table student alter column idcard(18)
    alter table student drop column idcard;
    

5.2.3 添加约束 #

主键约束
alter table student add constraint pk_idcard primary key(id)
唯一约束
alter table student add constraint uq_idcard unique(idcard)
默认约束
alter table student add constraint df_address default('地址不详') for address
检查约束
alter table student add constraint ck_score check(score between 0 and 100)
外键约束
alter table score add constraint fk_sid foreign key(sid) references student(id)
删除约束
alter table student drop constraint df_address

6. SQL #

6.1 SQL简介 #

6.1.1 SQL是什么 #

Structured Query Language:结构化查询语言

6.1.2 为什么要用SQL #

  1. 使用SQL Server Management Studio操作数据库不方便
  2. 我们需要通过应用程序去操作数据库

    6.1.2 什么是T-SQL #

    Transact-SQL 是SQL 的增强版,对SQL功能进行了扩充,增加了如变量说明、流程控制和功能函数等

6.2 SQL组成 #

6.2.1 DDL(data definition language)是数据定义语言 #

主要的命令有CREATEALTERDROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用

6.2.2 DML(data manipulation language)是数据操纵语言 #

它们是SELECTUPDATE、INSERTDELETE`,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言

6.2.3 DCL(DataControlLanguage)是数据库控制语言 #

是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句

6.3 SQL运算符 #

是一种符号,它是用来进行列间或者变量之间的比较和数学运算的

6.3.1 算术运算符 #

运算符 说明
+ 加运算,求两个数或表达式相加的和,如1+1
减少减运算,求两个数或表达式相减的差,如4-1
* 乘运算,求两个数或表达式相乘的积,如2*2
/ 除运算,求两个数或表达式相除的商,如6/4的值为1
% 取模运算,求两个数或表达式相除的余数,如:6%4的值为2

6.3.2 赋值运算符 #

运算符 说明
= 把一个数或变量或表达式赋值给另一变量,如:name=’zhufengpeixun’

6.3.3 逻辑运算符 #

运算符 说明
AND 当且仅当两个布尔表达式都为true时,返回TRUE
OR 当且仅当两个布尔表达式都为false,返回FALSE
NOT 布尔表达式的值取反

6.3.4 比较运算符 #

运算符 说明
= 等于
> 大于
< 小于
<> 不等于
>= 大于等于
<= 小于等于
!= 不等于

6.4 查询窗口 #

Alt text

6.5 数据操作语言 #

6.5.1 插入数据行 #

6.5.1.1 语法 #

 INSERT  [INTO]  表名  [(列名)] VALUES  (值列表)

6.5.1.2 示例 #

INSERT INTO [school].[dbo].[student]
(name,idcard,age,city)
VALUES
('欧阳锋','410787',30,'白驼山')
GO

6.5.1.3 注意 #

  • 每次插入一行数据,不能只插入一部分数据,插入的数据是否有效将按照整行的完整性要求来检验
  • 每个数据值的数据类型、精度、位数必须与要应的列名精确匹配
  • 不能为标识符指定值
  • 如果某字段设置为不能为空,则必须插入数据
  • 插入数据时还要符合检查性约束的要求
  • 有缺省值的列,可以使用DEFAULT关键字来代替插入实际的值

6.5.2 插入多行数据 #

6.5.2.1 SELECT INTO #

通过SELECT INTO语句将现有表中的数据添加到新表中

6.5.2.1.1 语法 #

SELECT (列名) INTO <表名> FROM <源表名>

6.5.2.1.1 示例 #

SELECT id,name,idcard,age,city
INTO student_bak
FROM student

注意: 此语句只能执行一次

6.5.2.2 INSERT SELECT #

将现有表中的数据添加到已存在的表中

6.5.2.2.1 语法 #

 INSERT INTO <表名>(列名) SELECT <列名> FROM <源表名>

6.5.2.2.1 示例 #

INSERT INTO student_bak
SELECT name,idcard,age,city
FROM student

注意: student_bak 表必须预先建立好,并且有对应的列名

6.5.3 更新数据 #

更新表中的数据

6.5.3.1 语法 #

UPDATE 表名 SET 列名 = 更新值 [WHERE 更新条件]

6.5.3.2 示例 #

UPDATE [school].[dbo].[student]
   SET age = 40,city = '上海'
WHERE id=7

注意: 更多多列时用逗号隔开,一定要加更新条件以免错误更新

6.5.3.3 练习 #

  1. 多个联合条件使用 AND id=7 and idcard='410787'
  2. 判断某字段是否为空 email is null or email = ''

6.5.4 删除数据 #

删除表中的数据

6.5.4.1 语法 #

DELETE [FROM]  表名 [WHERE <删除条件>]

6.5.4.2 示例 #

DELETE FROM [school].[dbo].[student]
      WHERE IDENT_CURRENT=7

Delete语句是对整行进行操作,因此不需要提供列名
如果要删除主表数据,则要先删除子表记录

6.5.5 TRUNCATE 截断表 #

截断整 个表中的数据

6.5.3.1 语法 #

TRUNCATE  TABLE  表名

6.5.3.2 示例 #

TRUNCATE TABLE student

数据全部清空,但表结构、列、约束等不被改动
不能用于有外键约束引用的表
标识列重新开始编号
因为要删除的数据不会写入日志,数据也不能恢复,所以工作中请尽量不要使用此命令

6.5.6 导出数据 #

6.5.7 导入数据 #

6.6 数据查询 #

  • 查询就是从客户端 发出查询请求数据库服务器,并从数据库返回查询结果的过程
  • 每次执行查询只是从数据表中提取数据,并按表的方式呈现出来
  • 查询产生的是虚拟表,并不会保存起来

6.6.1 查询 #

6.6.1.1 语法 #

SELECT    <列名> 
FROM      <表名> 
[WHERE    <查询条件表达式>] 
[ORDER BY <排序的列名>[ASCDESC]]

6.6.1.2 示例 #

SELECT id,name,idcard,age,city
FROM student
WHERE home= ‘北京’ 
ORDER BY id asc

6.6.1.3 注意 #

  • 代表查询所有的列,如果要查询某几列需要指定列名
  • 通过 as 可以给列起别名
    SELECT id,name,idcard,age,city AS home
    FROM student
    WHERE city= '山东'
    ORDER BY id asc
    
  • 通过等号给列起别名
    SELECT id,name,age,home=province+city
    FROM student
    WHERE city= '济南'
    ORDER BY id asc
    
  • 查询空行、常量行
    SELECT id,name,age,city
    FROM student
    WHERE city is null or city =''
    
  • 常量列
    SELECT id,name,age,city,'中国' as country
    FROM student
    
  • 限制返回的行数
    SELECT top 2 id,name,age,city,'中国' as country
    FROM student
    
SELECT top 20 PERCENT id,name,age,city,'中国' as country
FROM student

按照一定的百分比返回查询行数可以得到表中大致有多少数据行

6.6.1.4 练习 #

  1. 查询山东省的同学全部信息
  2. 查询山东省的男同学的全部信息
  3. 查询没有填写电子邮箱的同学的姓名和身份证号
  4. 查询生日晚于1998年1月1号并且年龄小于20岁的女同学的姓名和生日信息

6.6.1.5 单列排序 #

ORDER BY

  1. 把学生的分数按升序排列
    select * from school.dbo.score order by grade asc;
    
  2. 把学生的分数按降序排列
    select * from school.dbo.score order by grade desc;
    
  3. 课程编号按升序,成绩按降序
    select * from school.dbo.score order by course_id asc, grade desc;
    

    6.6.1.6 SQL Server函数 #

    6.6.1.6.1 字符串函数 #

    6.6.1.6.2 日期函数 #

6.6.1.6.3 数学函数 #

6.6.1.6.4 系统函数 #

6.6.1.6.5 练习 #
  1. 把学生表中的邮箱地址全部由小写改为大写
    update student set email = UPPER(email)
    
  2. 字段level中的数据是 1-1、1-2、2-3之类的,现在要求先按前半部分升序排列,再按后半部分降序排列
    select level from stu
    order by 
    convert(int,left(level,CHARINDEX('-',level)-1)),
    convert(int,right(level,LEN(level)-CHARINDEX('-',level))) desc
    
  3. 查询年龄超过18岁的男学生
    select * from school.dbo.student
    where DATEDIFF(YY,birthday,GETDATE())>=18
    
  4. 查询出生月份是9月的山东省同学
    select *from school.dbo.student
    where DATEPART(M,birthday)=9 and province = '山东省'
    
  5. 查询生日是今天的学生姓名
    select * from school.dbo.student
    where DATEPART(D,birthday) = DATEPART(D,GETDATE())
    
  6. 查询ID为3的同学邮箱域名
    select RIGHT(email, LEN(email) - CHARINDEX('@',email) )
    from school.dbo.student where id=3
    

6.6.1.7 模糊查询 #

就是查询的条件是模糊的,不是特别明确的

6.6.1.7.1 通配符 #

代替一个或多个真正的字符,与LIKE 关键字一起使用

6.6.1.7.2 BETWEEN #

查询某一列在指定的规范内的记录,包括两个边界

select * from score where grade between 80 and 100
6.6.1.7.3 IN #

查询某一列中的值在列出的内容列表中

select * from student where city in ('北京','上海','广东')

6.6.1.8 什么是聚合函数 #

对一组值进行计算,并返回计算后的值,一般用来统计数据

6.6.1.8.1 SUM #

累加所有行的值

计算ID=1的学生的的总分
select SUM(grade)  as '总分' from score where student_id = 1;
6.6.1.8.2 AVG #

计算所有行的平均值

计算ID=1的学生的的平均分
select AVG(grade) as '平均分' from score where student_id = 1;
6.6.1.8.3 MAX、MIN #

计算所有行的平均值

计算ID=1的学生的的平均分
select AVG(grade) as '平均分',MAX(grade) 最高分,MIN(grade) 最低分 from score where student_id = 1;
6.6.1.8.4 AVG #

计算所有行的平均值

select AVG(grade) as '平均分',MAX(grade) 最高分,MIN(grade) 最低分 from score where student_id = 1;
6.6.1.8.5 COUNT #

计算学生总数

select COUNT(*) from student;

6.6.1.9 分组 #

分组查询就是按某列的值进行分组,相同的值分成一组,然后可以对此组内进行求平均、求和等计算

6.6.1.9.1 语法 #
SELECT  FROM  <表名>  
WHERE  <条件>
GROUP BY <分组字段>

SELECT列表中只能包含:

  • 被分组的列
  • 为每个分组返回一个值的表达式,如聚合函数
6.6.1.9.2 练习 #
  • 统计每位同学的平均成绩-单列分组
    select student_id,avg(grade) from score group by student_id;
    
  • 统计每门课程的最高分,并按分数从高到低排列
    select course_id,max(grade) 平均分 from score group by course_id order by max(grade) desc
    
  • 统计各省的男女同学人数-多列分组
    select province,gender,COUNT(*) from student group by province,gender
    

    6.6.1.10 分组筛选 #

    6.6.1.10.1 语法 #
    SELECT  FROM  <表名>
    WHERE 
    GROUP BY 
    HAVING
    
  • WHERE用于过滤掉不符合条件的记录
  • GROUP BY用于对筛选后的结果进行分组
  • HAVING 用于过滤分组后的记录
6.6.1.10.2 练习 #
  • 统计学生人数超过1人的省份
    select province,COUNT(*) from student group by province having COUNT(*)>1
    
  • 得到学生的年龄
    select DATEDIFF(dd,birthday,GETDATE())/365 from student
    
  • 不及格次数大于1次的学生
    select student_id,COUNT(*) 不及格次数 from score where grade <60 group by student_id having COUNT(*)>1
    

6.6.1.11 多表连接 #

多表联接查询实际上是通过各个表之间共同列的关联性来查询数据的,它是关系数据库查询最主要的特征

6.6.1.11.1 内联接 #

内联接就是通过比较运算符匹配两个表中的列值

语法

SELECT
FROM1
INNER JOIN2
ON1.字段1 = 表2.字段2

INNER JOIN 关键字后面是关联表,ON关键字后是表间联接字段的关系

  • 显示 每个人的成绩
    “`sql
    select student.name,course_id,grade from student inner join score on student.id = score.student_id;

select student.name,course.name,grade from student inner join score on student.id = score.student_id inner join course on score.course_id = course.id;
“`

6.6.1.11.2 左内联接 #

左外联接是以左表为基础的,左表的记录将会全部表示出来,而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL

6.6.1.11.2 右内联接 #

右外连接的原理与左外连接相同,右表逐条去匹配记录;否则NULL填充

sql server怎么创建数据库用户

1. 创建登陆帐户(create login)

create login db1 with password=’abcd123a’, default_database=data1
登陆帐户名为:“db1”,登陆密码:abcd123a”,默认连接到的数据库:“data1”。
帐户默认是 guest 数据库用户身份

2 创建数据库用户(create user):

在data1数据库中的security中的user下可以找到新创建的db1
create user db1 for login db1 with default_schema=dbo

3  赋予数据库用户“db_owner”权限

exec sp_addrolemember ‘db_owner’, ‘db1’ (责任编辑:admin)

未经允许不得转载:WEB前端开发 » SQLServer数据库教程

赞 (0)