SqlServer笔记
通过CMD使用SqlServer数据库
-
WIN + R 键打开命令行，输入 cmd，进入黑乎乎的窗口
-
输入osql ?/ 查看sqlServer相关信息
-
登录进去
osql -S [服务器ip地址] -U [用户名] -P [密码]
如果访问本机，可以省略-S, -后面的字母必须大写
use scott;
select * from emp;
go --执行
主要说明通过原始的命令行访问数据库特别不方便
打开sqlServer管理工具
Microsoft 微软 Management 管理 Studio 工作
微软的sqlServer图形化管理工具
作用： 简单方便的操作数据库
数据库的备份与导入
介绍三个文件
mdf是数据文件，数据库系统的可实时操作/读取的数据文件 ldf 应该是日志文件，用于存储数据库的逻辑日志信息。
bak，是备份文件。数据库逻辑备份和增量备份的输出格式
找到mdf文件讲解并复制出来
进图形化工具-》右击要备份的数据库-》属性-》选文件那一项-》找到路径
复制时会被占用， 去任务管理器中，关闭MSSQLSERVER服务即可，复制完记得打开服务
必须拿走mdf和ldf，不然会导入会失败
从mdf文件中导入数据库
进图形化管理工具-》右击“数据库”-》附加-》添加-》找到导入的mdf文件位置-》确定-》刷新(不然以为没成功)
通过bak文件备份数据库
backup database [数据库名称] to disk = 'd:\database\test1.bak' --路径和备份后的名称;
--注意备份后的名称一定要加.bak结尾
--英文翻译大致意思 导出 数据库 到磁盘 某某某位置
通过bak文件恢复数据库
restore database [数据库名称] from disk = 'd:\database\test1.bak' --恢复时文件的路径;
-- 这里的数据库名称是自己定义的
-- 恢复数据库来自某某某位置
演示如何用管理工具手动备份还原
右击要备份的数据库-》任务-》备份-》可以修改路径
右击“数据库”-》还原-》选中要还原的数据库-》在选项中选中覆盖现有数据库
SqlServer数据库基本类型
--整形
int - --4字节
bigint --8字节
smallint --2字节
tinyint --1字节
--浮点型
real --4字节
float --8字节
decimal(p, s) --大精度, p精度（可以显示多少位），s小数部分(可以显示多少小数)
--字符型
char(n) --默认1个字节，n的取值为(1-8000),数据长度小于n补空格，大于截断
varchar(n) --可变字符型，n的取值为（1-8000），数据长度小于n就存n
nchar(n) --固定长度，采用Unicode编码，n的取值为（1-4000），一个字符占两个字节
nvarchar(n) --可变长度，采用Unicode编码，n的取值为(1-4000)
--日期和时间数据类型
date --存储时间，用字符串存，格式为"YYYY-MM-DD",占用三个字节的空间
time --字符串形式记录一天中的某个时间，格式为"hh:mm:ss[n*7]" 7个长度存储毫秒，占5个字节
datetime --时间和日期，占用8个字节
datetime2 --扩展类型，数据范围更大，默认为最高精度
smalldatetime --与datetime类似，只是范围缩小到(1900年1月1日到2079年6月6日),占用4个字节
--文本
text --存储文本数据，非Unicode，最大长度为2的31次方个字符
对表的基本操作
创建及修改表
-- 记录玩sqlServer的第一个坑，上来创建一个表，名为user，怎么select都报错，害，因为没有加[]
create table [user]
(
id int primary key identity(1, 1),
username nvarchar(20) not null,
age int check (age between 18 and 150),
gender tinyint default(0)
)
-- 讲解user表
-- 1 [user] 防止与数据库关键字起冲突
-- 2 定义主键的是primary key, identity(起始值，增长值)主键自动增长
-- 3 not null 不能为空
-- 4 check 限定值的范围
-- 5 default 默认值
-- 6 插入数据演示
create table [account]
(
id int identity(1,1),
bank nvarchar(50),
userId int references [user](id)
)
-- 1 添加一个money字段 alter table [account] add money decimal(10, 3)
-- 2 插入数据演示
-- 增删改查
-- insert into [表名] (字段名...) values(值) #值要和字段名一一对应
-- delete [表名] where 条件
-- update [表名] set 字段名 = 值 where 条件 (如果不加where 条件会怎么样)
-- select * from [表名]
数据库中的术语
字段 等同于（属性）（列） 表示某一列
记录 等同于（元组）（行） 表示某一行
主键 唯一标识事物的信息
外键 避免数据冗余 举列子
约束
主键约束 primary key
唯一键约束 unique
外键约束 foreign key
默认约束 default
检查约束 check
简单说明用户表和账户表关系
一对一关系
一对多关系 (在多的一方添加外键) 例子->银行和账户
多对多关系 (需要中间表完成) 例子->老师、学生、
介绍SqlServer中一些关键字
and -- 且
or -- 或
not -- 非
in -- 在这之内
between A and B -- 位于一个区间
top -- 前多少个（SqlServer 独有)
null -- 为空判断 (演示一个空串)
not null -- 不为空
order by -- 排序
ASC DESC -- 顺序
like -- 模糊查询 %一边忽略， _占位（_A -> AA, BA), '[A-F]', [^A-F]
case -- 条件判断 (select '性别' = case gender when 0 then '男' when 1 then '女' else '晓不得' end from [user])
distinct -- 去重
when(如果) then(那么) else(其它) end(结束)
聚合函数
count() -- 统计总数 count(*) count(1) count(字段) 字段为null不统计，不为null都会统计 -- 效率 count(*)=count(1)>count(id)>count(字段) sum() -- 求和 avg() -- 求平均值 max() -- 最大 min() -- 最小
介绍scott表
emp表 编号， 姓名， 工作， 上司编号， 入职日期，工资， 奖金， 部门编号
dept表 编号， 名称， 地址
salgrade 等级， 最低标准，最高标准
查询(难点，重点)
union -- 联合(竖向合并结果集), 默认去掉结果集中重复的记录 union all -- 联合后的所有属性，不去重 -- union使用时如果一条记录的所有值都一样时会去重 -- 联合的两组数据，字段数量一定要相同，类型可以不同，但是能兼容 group by --把表中记录按照不同字段分组，分组后只能查看分组后的整体信息 -- 例题： 输出每个部门的编号和该部门的平均工资 select deptno, avg(sal) as '部门平均工资' from [emp] group by deptno -- 进阶先按部门分组，再按工作分组 select deptno, avg(sal) as '部门平均工资', job as '工作' from [emp] group by deptno, job -- 再次进阶 select deptno, avg(sal) as '部门平均工资', job as '工作', COUNT(*) as '部门人数', MAX(sal) as '部门中最高工资', MIN(sal) as '部门中最低工资' from [emp] group by deptno, job having --通常用于分组后的信息进行过滤，不允许出现组内详细信息 -- 例子： 按部门编号分组后把部门中平均工资大于2000的输出 select AVG(sal) '平均工资' from [emp] group by deptno having AVG(sal) > 2000
-- 连接查询
-- 将两个或两个以上的表，以一定的条件连接起来，从中检索条件满足的数据
-- 内连接
1. select ... from A, B 的用法
讲解
1.1 去数据库执行这条语句
select * from emp, dept
1.2 先查看这两张表，分析这个零时表是如何得出的（笛卡尔积）
1.3 实质上把emp表中的每条记录和dept表中的记录做匹配 =>emp.length * dept.length
总结
总行数是A和B的乘积
列数是A和B之和
2. select ... from A, B where ...的用法
讲解
2.1 select * from emp, dept where emp = 7369
2.2 为什么只有5条数据呢，因为在emp表中只有一个7369的元组 => 1 * dept.length
总结
把A和B组成的结果集按where条件进行过滤
3. select ... from A join B on ... 的用法
讲解
3.1 select * from emp join dept on 1 = 1
3.2 on后面表示连接条件， 有join就必须要on
3.3 select * from emp join dept on emp.deptno = dept.deptno
3.4 画图展示mspaint,解释连接的方式
4. select ... from A, B where ...
与
select ... from A join B on ...
的比较
讲解
4.1 分别以 where 和 join 执行一条语句，达到同一个结果
4.2 where 是sql92标准 join 是sql99标准（推荐使用）
4.3 sql99更容易理解， on和where可以做不同的分功
4.4 小练习： 把工资大于2000的员工的姓名和部门编号输出
4.5 select e.ename, e.deptno, e.sal
from emp "e"
join dept "d"
on e.deptno = d.deptno
where e.sal > 2000
5. select、 from、 where、 join、 on、 group、 by、 order、 top、 having的混合使用
5.1 怎么把92标准转换成99标准
select * from emp, dept
where dept.deptno = 10
因为它没有连接条件，只有筛选条件
select *
from emp
join dept
on 1 = 1
where dept.deptno = 10
5.2 判断一下语句输出是几行（15行）
select * from emp, dept where emp.deptno = 10
--注意这里是个筛选条件
--那么怎么把它转换成3行呢？(加上筛选条件即可)
select * from emp, dept where emp.deptno = 10 and emp.deptno = dept.deptno
5.3 输出所有员工中工资从高到底的前三名的员工姓名，工资，工资等级，部门名称
select top 3 e.ename, e.sal, s.grade, d.dname
from emp "e"
join dept "d"
on e.deptno = d.deptno
join SALGRADE "s"
on e.sal between s.LOSAL and s.hisal
order by e.sal desc
6. 习题练习
6.1 求出每个员工的姓名 部门编号 薪水 和 薪水等级
select "E".ename, "E".deptno, "E".sal, "S".grade
from emp "E"
join salgrade "S"
on "E".sal between "S".losal and "S".hisal
6.2 查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级
先查出部门分组和部门的平均工资，把它当作一个表来看待
select deptno, avg(sal) as "avg_sal"
from emp
group by deptno
再次连表分等级
select "A".deptno, "A".avg_sal as "部门平均工资", "S".grade
from(
select deptno, avg(sal) as "avg_sal"
from emp
group by deptno
) as "A"
join salgrade as "S"
on "A".avg_sal between "S".losal and "S".hisal
6.3 求出emp表中所有领导的姓名
先找出领导的id
select distinct mgr from emp
再根据id找到姓名
select emp.ename
from emp
where empno in (select distinct mgr from emp)
6.4 求出平均薪水最高的部门的编号和部门的平均工资
select top 1 avg(sal) as "平均工资", deptno as "部门编号"
from emp
group by deptno
order by avg(sal) desc
6.5 去掉工资最低的，把剩下最低的三个人的姓名 工资 部门编号 部门名称 工资等级 输出
select top 3 emp.ename, emp.sal, emp.deptno, dept.dname, SALGRADE.grade
from emp
join dept
on dept.deptno = emp.deptno
join salgrade
on emp.sal between SALGRADE.LOSAL and SALGRADE.HISAL
where sal > (select min(sal) from emp)
order by sal ASC
效率更高的是先把员工表中工资最低的去掉，变成一张零食表，再与其它的表连接
左外连接（右外连接）
-- 用左表的第一行分别和右表的所有行进行联接 -- 如果有匹配行，则一起输出 -- 若没有匹配行，则输出一行，输出左表内容，右边全部输出null select * from dept d left join emp e on d.deptno = e.deptno
内连接
-- 自己和自己连接 -- 不用聚合函数，找到表中工资最高的员工 select * from emp where emp.empno not in( select DISTINCT e1.empno from emp e1 join emp e2 on e1.sal < e2.sal )
分页
-- 输出工资最高的三个员工所有信息 -- 输出工资排名在第4-6的员工信息 select top 3 * from emp where emp.empno not in (select top 3 emp.empno from emp order by sal desc) order by sal desc
感谢你能看完，从来没这么认真写过笔记，原来这是给一个女孩讲课做的备课笔记
