数据库学习
目录
sql server数据库基本概念
使用文件保存数据存在几个缺点：
1、文件的安全性问题；
2、文件不利于查询和对数据的管理；
3、文件不利于存放海量数据
4、文件在程序中控制不方便。
数据库的定义(1)
严格地说，数据库是“按照数据结构来组织、存储和管理数据的仓库”。
数据库的定义(2)
J.Martin组数据库下了一个比较完整的定义：数据库是存储在一起的相关数据的集合，这些数据是结构化的，无有害的或不必要的冗余，并为多种应用服务；数据的存储独立于使用它的程序；对数据库插入新数据，修改和检索原有的数据均能按一种公用的和可控制的方式进行。当某个系统中存在结构上完全分开的若干个数据库时，则该系统包含一个“数据库集合”。
数据库的基本结构
数据库的基本结构分三个层次，反映了观察数据库的三种不同角度。
1、物理数据层
它是数据库的最内层，是物理存贮设备上实际存储的数据的集合。这些数据的原始数据，是用户加工的对象，由内部模式描述的指令操作处理的位串、字符和字组成。
2、概念数据层
它是数据库的中间一层，是数据库的整体逻辑表示。指出了每个数据的逻辑定义及数据间的逻辑联系，是存贮记录的集合。它所涉及的是数据库所有对象的逻辑关系，而不是它们的物理情况，是数据库管理员概念下的数据库。
3、逻辑数据层
它是用户所看到和使用的数据库，表示了一个或一些特定用户使用的数据集合，即逻辑记录的集合。
数据库不同层次之间的联系是通过映射进行转换的。
数据库的基本特点
1、实现数据共享
数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享。
2、减少数据的冗余度
同文件系统比，数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性
3、数据实现集中控制
文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系。
4、数据一致性和可维护性，以确保数据的安全性和可靠性。
5、故障恢复
数据库选择：
1、成本；2、功能；3、并发性要求；4、安全性；
sql server介绍(1)
SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI(美国国家标准协会)的规定，SQL被作为关系型数据库管理系统的标准语言。SQL语句可以用来执行各种各样的操作，例如更新数据库中的数据，从数据库中提取数据等。
SQL全称是“结构化查询语言(Structured Query Language)”
SQL(Structured Query Language)是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。构化查询语言(Structured Query Language)最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。
sql语句
SQL语言包含4个部分：
数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。
数据操作语言(DML)，例如：INSERT、UPDATE、DELETE语句。
数据查询语言(DQL)，例如：SELECT语句。
数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。
完成梁山好汉管理数据库
1、建库
2、建表
英雄表 hero
[这里涉及到对表的几个重要概念：a、字段；b、字段类型(简单介绍)；c、表行；d、表列；e、记录]
- 对表进行增加、删除、修改、查询的操作
查询分析器中创建数据库
--创建数据库
create database LiangshanHero2
--创建表
use LiangshanHero2--使用指定数据库
go--执行
create table hero--表名
(heroId int ,--英雄排名
heroName varchar(50),--名字
heroNickName varchar(50),--绰号
sex char(2),--性别
sal int
)
--删除一张表(把表的结构和表的数据删除)
drop table hero
--使用sql语句来向hero表中添加数据
insert into hero values(1,'宋江','及时雨','男',20000)
insert into hero values(2,'卢俊义','玉麒麟','男',15000)
insert into hero values(3,'吴用','智多星','男',30)
insert into hero values(4,'公孙胜','入云龙','男',80)
--使用sql语句查询数据,最基础的查询语句
select * from hero
--1、查询工资低于100的同志
select * from hero where sal<100
--2、把工资低于100的人工资提高10%(update)
--语法结构：update 表名 set 字段名1=？,字段名2=？...where 条件语句
update hero set sal=sal*1.1 where sal<100
--3、请删除工资性别为男的
delete from hero where sex='男'
表的管理--表名和列的命名规则
1、必需以字母,_开头
2、长度不能超过128个字符
3、不要使用sql server的保留字
4、只能使用如下字符A-Z，a-z，0-9，$，#，_等
表的管理--支持的数据类型
字符型
char 定长 最大8000字符(非unicode编码)
char(10)'小韩'前四个字符放'小韩'，后添6个空格补全
varchar 变长 最大8000字符(非unicode编码)
varchar(10)'小寒'sql server分配四个字符，这样可以节省空间
ntext可变长度Unicode数据的最大长度为2的30次方-1(1,073,741,823)个字符
text可变长度非Unicode数据的最大长度为2的31次方-1(2,147,483,647)个字符
区别：
1、text是字节格式存储英文的，也可以存中文但有时候会显示成乱码
2、ntext是多字节格式存储unicode的，也就是存储各种文字用的。
在什么时候使用char型而不使用varchar,在知道字段定长固定时就使用char
字符型
nchar 定长 最大4000字符(unicode编码)
nchar(10)'小韩'前四个字符放'小韩'，后添6个空格补全
nvarchar 变长 最大4000字符(unicode编码)
nvarchar(10)'小寒'sql server分配四个字符，这样可以节省空间
特别说明：
一般带有汉字的字段用nvarchar，全英文或符号的用varchar，因为nvarchar为unicode字符集，该类型的字段无论是单个字母还是单个汉字都占两个字节，而varchar，字母占一个字节，汉字占两个，nvarchar处理汉字或其它unicode字符集的速度要比varchar字段快。
表的管理--支持的数据类型
数字型
1、bit 范围 0到1
2、int 范围 负的2的31次方到正的 2的31次方-1
3、bigint 范围 负的2的63次方到正的 2的63次方-1
4、float 存放小数，不推荐使用
5、numeric 小数
强烈建议 如果要去存放小数最好使用numeric
日期类型
datetime(表示日期)
timestamp(时间戳)
一般情况下 用datetime表示日期
--sql server为我们提供一个专门的时间函数
getdate()
create table spname--建表
(bir datetime)--字段
insert into spname values(getdate())--添加时间
图片
image保存图片，但是用的很少，一般用路径保存图片，在软件公司往往使用图片服务器和图床技术
视频
binary字段可以存放，但是我们往主，将视频文件保存在文件服务器上，sql server中只保留文件路径，存取效率高。
-------------------------------------------------------------------------------
表的管理
sql server建议表的设计者，最好给表定义一个主键，用来标示唯一的一条记录。
1、主键(primary key)，不能够重复出现；
2、主键必需给值，换言之主键不能为null
3、主键可以修改，但不能修改为同名的，同时要明确字段不有重复，否则报错。
添加数据
所有字段都插入
插入部分字段(语法)：insert into 表名 (字段名1,字段名2,...) values (对应字段数据1,对应字段数据2,...)
修改数据
改一个字段(语法)
update 表名 set 字段名='新值' where 字段='值'
例：update student set sex='女' where xh='A001'
update clerk set age=38 where cleName='贾政'
修改多个字段(语法)
update 表名 set 字段名1='新值',字段名2='新值'... where 字段='值'
例：update student set sex='男',birthday='1980-04-01' where xh='A001'
update clerk set cleName='薛蟠',age=40 where cleId=2
修改含有null值的数据(语法)
update 表名 set 字段名='新值' where 字段 is null
例：update clerk set cleName='薛宝钗' where age is null
删除数据
删除全部数据
delete from 表名
删除指定数据
delete from 表名 where 字段名='值'
删除多个指定数据(and是条件满足，or是前提条件不满足时使用)
delete from 表名 where 字段名1='值' and 字段名2='值' and或or ...
表的基本查询
介绍主键和外键
主键，一张表中只能有一个主键
1、主键(primary key)，不能够重复出现；
2、主键必需给值，换言之主键不能为null
3、主键可以修改，但不能修改为同名的，同时要明确字段不有重复，否则报错。
外键
1、外键只能指向主键
2、外键和主键的数据类型要一致
deptno int foreign key references dept(deptno)--部门编号(做成外键)
查询语句
--查看表内容
select * from 表名
--查询所有列
--语法：select * from 表名 where 条件
*能不用就不用浪费资源
--查询指定列(sql sever不区分大小写,oracle区分大小写)
--语法：select 字段1,字段2 from 表名 where 条件
--取消重复行(distinct只能消除完全一样的行，保留一行)
--语法：select distinct 字段 from 表名 where 条件
--使用算数表达式
--显示每个雇员的年工资+奖金
select ename"姓名",sal*13+isnull(comm,0)*13"年收入" from emp
姓名 和 年收入 为别名，可以使用单引号、双引号、或者什么都不加
isnull函数用来判断该列是否为null，为null返回0
--使用where子句
--如何显示工资高于3000的员工
select ename"员工姓名",sal"工资高于3000" from emp where sal>3000
--如何查找1982-1-1后入职的员工 用大于号即可
select ename"员工姓名",hiredate"1982-1-1后入职日期" from emp where hiredate>'1982-1-1'
--如何显示工资在2000到2500的员工情况
select * from emp where sal>=2000 and sal<=2500
select * from emp where sal between 2000 and 2500 between就是两边取等
--如何使用like操作符(模糊查询)
--%：表示0到多个字符 _:表示单个字符
--如何显示首字符为S的员工姓名和工资
select ename,sal from emp where ename like 's%'
--如何显示第三个字符为o的所有员工的姓名和工资
select ename,sal from emp where ename like '__O%'
--在where条件中使用in
--如何显示empno为123,345,800...的雇员情况
select * from emp where empno in(123,345,800)
--使用is null的操作符
--如何显示没有上级的雇员的情况
select * from emp where mgr is null
--使用逻辑操作符号
--查询工资高于500或是岗位为manager的雇员，同时还要满足他们的姓名首写字母为大写的J
select * from emp where (sal>500 or job='manager') and ename like 'J%'
--使用order by字句(asc默认是升序排列,desc为降序排列)
--如何按照工资的从低到高的顺序显示雇员的信息
select sal from emp order by sal asc
--按照部门号升序而雇员的工资降序排列
--order by可以根据不同的字段排序
select deptno,sal from emp order by deptno,sal desc
统计每个人的年薪，并按照从低到高的顺序排序
--使用列的别名排序(别名需要使用""号圈中)
select ename,sal*12"年薪" from emp order by "年薪"
表的复杂查询
说明：
在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给
大家介绍较为复杂的select语句
数据分组 -max(最大),min(最小),avg(平均),sum(和),count(统计)
--如何显示所有员工中最高工资和最低工资
select min(sal)"最低工资",max(sal)"最高工资" from emp
--显示最低工资并显示出雇员名字
select ename"姓名",sal"工资" from emp where sal=(select min(sal) from emp)
SQL语句是从右向左读取的，有括号先读括号。
--显示所有员工的平均工资和工资总和
select avg(sal)"平均工资",sum(sal)"工资总和" from emp
--把高于平均工资的雇员的名字和他的工资显示出来
select ename,sal from emp where sal>(select avg(sal) from emp) order by sal
--计算共有多少员工
select count(*)"雇员数" from emp
表的复杂查询
group by和having子句
group by用于对查询的结果分组统计
having子句用于限制分组显示结果
例：
--group by和having子句使用(having与group by结合使用，可以对分组后的查询结果进行筛选)
--如何显示每个部门的平均工资和最高工资
select deptno"部门号",avg(sal)"平均工资",max(sal)"最高工资",min(sal)"最低工资" from emp group by deptno
--显示每个部门的每种岗位的平均工资和最低工资
select deptno"部门号",job"岗位",avg(sal)"平均工资",min(sal)"最低工资" from emp group by deptno,job order by deptno
--显示平均工资低于2000的部门号和它的平均工资
select deptno"部门号",avg(sal)"平均工资" from emp group by deptno having avg(sal)<2000
对数据分组的总结
1、分组函数只能出现在选择列表，having、order by子句中
2、如果在select语句中同时包含有group by，having，ovrder by那么他们的顺序是group by，having，order by
3、在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必需有一个出现在group by子句中，否则就会出错
如：select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000
这里group by 后为 deptno就一定要出现在 select语句中
表的复杂查询--多表查询
说明：
多表查询是指基于两个或两个以上的表或是视图的查询，在实际应用中，查询单个表可能不能满足你的需求，(如显示sales部门位置和其员工的姓名)，这种情况下需要使用到(dept表和emp表)
--多表查询
--如果多张表都有相同名字的字段，则需要带表名(别名)
--显示雇员名，雇员工资及所在部门的名字
select emp.ename"雇员名",emp.sal"雇员工资",dept.dname"部门名称" from emp,dept where emp.deptno=dept.deptno
取别名的方式
select e.ename"雇员名",e.sal"雇员工资",d.dname"部门名称" from emp e,dept d where e.deptno=d.deptno
表的复杂查询--多表查询
自连接
自连接是指在同一张表的连接查询
--显示公司每个员工姓名和他的上级的名字
--分析，把emp表看成两张表分别是worker/boss
--外连接(左外连接、右外连接)
select worker.ename"员工名字",boss.ename"领导名字" from emp worker,emp boss where worker.mgr=boss.empno
表的复杂查询--子查询
什么是子查询
子查询是批嵌入在其它sql语句中的select语句，也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句（用=）
--如何显示与smith同一部门的所有员工？
select deptno"部门号",ename"员工名字" from emp where deptno=(select deptno from emp where ename='smith')
多行子查询
多行子查询指返回多行数据的子查询（用in）
--如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
select * from emp where job in(select distinct job from emp where deptno=10)
在from子句中使用子查询
--如何显示高于部门平均工资的员工名字、薪水、部门的平均工资
--分析：1、首先要知道各个部门的平均工资
select avg(sal)"部门平均工资",deptno from emp group by deptno
--2、把上面的查询结果当作一个临时表对待
select e.ename"员工名字",e.sal"薪水",temp.myavg"部门平均工资",e.deptno
from
emp e, (select avg(sal) myavg,deptno from emp group by deptno) temp
where
e.deptno=temp.deptno and e.sal>temp.myavg
在from子句中使用子查询
这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个临时表来对待，当在from子句中使用子查询时，必需给子查询指定别名
分面查询
按雇员的id号升序取出
--请显示第5个到第10个入职的雇员信息(按照入职的时间先后顺序查找)
--分析：1、显示第1个到第4个入职的雇员
select top 4 * from emp order by hiredate
--top后的数表示要取出几条记录
select top 6 *
from emp
where empno
not in
(select top 4 empno from emp order by hiredate)
order by hiredate
用查询结果创建新表
这个命令是一种快捷的建表方法
select *(这里可以选择字段) into 另一个表名 from 表
--如何删除掉一张表重复记录
create table cat(
catId int,
catName varchar(40)
)
insert into cat values(1,'aa')
select * from cat
--1、把cat表的记录distinct后的结果，放到临时表中
select distinct * into #temp from cat
--2、把cat表的记录清空
delete from cat
--3、把临时表中的数据信息加入到cat表中
insert into cat select * from #temp
--4、删除临时表
drop table #temp
--左外连接和右外连接
--思考题：显示公司每个员工和他的上级领导的名字
--内连接的处理方式(内连接只显示匹配的信息)
select worker.ename"员工名字",boss.ename"领导名字" from emp worker,emp boss where worker.mgr=boss.empno
--思考题：显示公司每个员工和他的上级领导的名字，没有上级领导的也要显示出来
--左外连接：指如果左边的表记录全部显示，如果没有匹配的记录，就用null填写
select worker.ename"员工名字",boss.ename"领导名字" from emp worker left join emp boss on worker.mgr=boss.empno
--右外连接：指如果右边的表记录全部显示，如果没有匹配的记录，就用null填写
select worker.ename"员工名字",boss.ename"领导名字" from emp worker right join emp boss on worker.mgr=boss.empno
维护数据的完整性--约束
约束用于确保数据库数据满足特定的商业规则。在sql server和oracle中，约束包括：not null、unique，primary key，foreign key和check五种
维护数据的完整性--使用
not null(非空)
如果在列上定义了not null，那么当插入数据时，必需为列提供数据。
unique(唯一)(一张表中可以有多个)
当定义了唯一约束后，该列值是不能重复的，但是可以为null,并只能有一个空值
primary key(主键)(一张表中只可以有一个主键)
用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null
需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。
表可以有复合主键，有多个列构成一个主键
primary key (testId,testname)--复合主键，需单独声明
--行级定义和表级定义
--在字段中定义主键为行级定义.例：testId int primary key
primary key (testId,testname)--复合主键，需单独声明为表级定义
foreign key(外键)(外键在从表上，要配合主表，但主表要有主键或unique约束)
用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必需具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必需在主表的主键列存在或是null
check(强制条件)
用于强制行数据必需满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不再1000-2000之间就会提示出错。
default使用(默认值)
mesDate datetime default getdate()--在不指定值是default可以直接取默认值，也可以由用户指定值
表的管理--修改表
添加一个字段
ALTER TABLE distributors ADD COLUMN address varchar(30);
修改字段的类型/或是名字(不能有数据)
ALTER TABLE distributors
ALTER COLUMN address TYPE varchar(80),
ALTER COLUMN name TYPE varchar(100);
ALTER TABLE distributors RENAME COLUMN address TO city;
删除一个字段
ALTER TABLE distributors DROP COLUMN address RESTRICT;
修改表的名字
ALTER TABLE distributors RENAME TO suppliers;
删除表
drop table 表名;
数据库的备份和恢复
使用企业管理器完成备份和恢复
使用企业管理器有两种方式完成备份和恢复
1、分离/附加
分离完后，请到sql server安装的目录下去找两个文件数据库名.mdf和数据库名.ldf，这两个文件就是分离后的文件，数据库分离后，该数据库就不能再使用了。
附加是指，当用户需要重新使用某个分离的数据库时进行的操作，就是让sql server数据库重新关联该数据库。
2、备份/恢复
备份数据库是指，把某个数据库文件从sql server中备份出来，这样用户可以根据需要再使用(用于恢复、复用..)，备份数据库不会影响到源数据库的使用
恢复数据库是指，当源数据库因为某种原因(比如源数据库毁坏、数据丢失、系统崩溃)需要恢复时进行的操作
使用查询分析器完成备份和恢复
用企业管理器完成对数据库的备份和恢复简单直观，同样我们也可以在查询分析器中完成类似的任务。
--使用查询分析器对数据库进行备份和恢复
--数据库备份
--语法：backup database 数据库名 to disk='存储路径'
backup database LiangshanHero2 to disk='f:/liangshan.bak'
--数据库恢复
--语法：restore database 数据库名 from disk='读取路径'
restore database LiangshanHero2 from disk='f:/liangshan.bak'
--新建数据库
--语法：create datebase 数据库名
create database LiangshanHero2
--删除数据库
--语法：drop database 数据库名
drop database LiangshanHero2
1474
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
