SQL Server数据库学习初探

 B站地址：https://www.bilibili.com/video/BV1zt411g7Pr

• 安装问题

        Sql2000 要解决挂起问题；

• 预备知识

1. 数据库原理
2. SQLServer 2005：TL_SQL语言
3. 数据结构和数据库的区别：

       数据库是在应用软件级别研究数据的存储和操作（外层，硬盘级别）；数据结构是在系统软件级别研究数据的存储和操作（内存，内存级别）。

• 什么是连接
• 有了编程语言为什么还需要数据库

对内存数据操作是编程语言的强项，但对硬盘数据操作却是编程语言的弱项；

对硬盘数据操作却是数据库的强项，是数据库研究的核心；

对硬盘数据的复杂操作，需要使用编程语言，将数据库中数据调入系统内存处理，处理之后再存入数据库中。

• 建议初学者从三个方面学习数据库：

     数据库是如何存储数据的：字段 记录 表 约束（主键 外键 唯一键 check default 触发器）；

     数据库是如何操作数据的：insert update delete T-SQL 存储过程 函数 触发器

     数据库是如何显示数据的：select（重点）

• 必备技巧

     如何建数据库

     系统库：master model msdb tempdb

     如何删除数据库

     如何附加分离数据库

• 简介

     关系型数据库：用一张二维表来描述事物及事物间的关系。表间联系通过外键实现。

表相关数据：

字段（列、属性）： 一个事物的某一个静态特征

记录（元组）：字段的组合，表示一个具体的事物

表：记录的组合，表示同一类事物的集合

表、字段、记录的关系：字段是事物的属性，记录时事物本身，表是事物的集合。

万物皆对象：对象包含属性和操作（数据库也是对象操作的）。

约束

定义：对一个表中的属性操作的限制叫做约束

分类：

主键约束：不允许重复元素 避免了数据的冗余

Create table banji_jiaoshi_mapping
(
  banji_id int,
  jiaoshi_id int,
  kecheng nvarchar(20),
  constraint pk_banji_id_jiaoshi_id primary key (banji_id,jiaoshi_id)
)

外键约束：通过外键约束从语法上保证本事物所关联的其他事物一定是存在的，事物和事物之间的联系只有通过外键体现。

Create table student
(
  stu_id int primary key,
  class_id int constraint fk_stu_class foreign key references class(class_id)
)

Check约束：保证事物属性的取值在合法范围内

Create table student
(
   stu_id int primary key,
   stu_sal int check (stu_sal>=1000 and stu_sal<8000),
   stu_sex nchar(1) default(‘男’),
   stu_name nvarchar(200) unique
)

Default约束：设置字段默认值

Unique约束：保证事物属性的取值不允许重复，但允许其中有一列且只能有一列为空

唯一约束和主键约束：和数据表业务无关的编号字段设为主键，即代理主键，业务中唯一的字段作为唯一约束键，即业务主键。

单引号：字符串；双引号：名称。

表和约束的区别

数据库是通过表来解决事物的存储问题

数据库是通过约束来解决事物取值的有效性和合法性的问题

建表的过程就是指定事物属性及其事物属性各种约束的过程

什么是关系？

定义：表和表之间的关系

实现方式：通过设置不同形式的外键来体现表和表的不同关系

分类：

       一对一：既可以把A表的主键当作B表的外键，也可把B表的主键当A表外键

       一对多：把表A的主键当作表B的外键，或把表A的主键添加到表B充当表B外键，即在多的一方添加外键。

        多对多：必须通过单独一张表来表示B表和A表的关系，

---

主键

能够唯一标识一个事物的一个字段或多个字段的组合

附注：

    含有主键的表叫主键表；

    主键通常是整数，不建议字符串作为主键（主键用于集群式服务，可以考虑字符串当主键）；

    主键的值通常不允许修改，除非本记录被删除；

    主键不要定义成id，而要定义成表名Id或者表名_id；

要用代理主键，不要用业务主键（任何一张表，强烈建议不要使用有业务含义的字段充当主键，我们通常都是在表中单独添加一个整型的编号充当主键字段）

---

外键---外键=来自

如果一个表中的若干个字段是来自另外若干个表的主键或唯一键，则这若干个字段就是外键。

附注：

外键通常是来自己另外表的主键而不是唯一键，因为唯一键可能为null

外键不一定来自另外的表，也可能来自本表的主键

含有外键的表叫外键表，外键字段来自的那一张表叫做主键表

先删除外键表，后删除主键表

• 数据查询：顺序
• 计算列

附注：

在Oracle中字段别名不允许用单引号括起来，但SQLServer却允许，为了可移植性，字段别名统一用双引号括起来。

Distinct

Between

In：查询若干个孤立的值

Top

Null：没有值，空值

附注：

零和null不一样，null表示空值，没有值，零表示一个确定的值；

Null不能参加如下运算：<>  !=  =；

Null可以参加如下运算：is       not is；

任何类型的数据都允许为null

任何数字与null参与数学运算的结果永远是null

Order by

模糊查询

格式：

select 字段的集合 from 表名 where 某个字段的名字 like 匹配条件（通配符）

通配符：

%：表示0或多个字符

_：任意单个字符

Select * from emp where ename like ‘_A%’  ---第二个字符是A的字符串

[a-f]：a到f中的任意单个字符  只能是a b c d e f 中的任意一个字符

Select * from emp where ename like ‘_[A-F]%’ ---第二个字符是A-F中任意一个

[a,f]：a或f

[^a-c]：不是a，b，c中的任意单个字符

Select * from emp where ename like ‘_[^A-F]%’

注意：匹配的条件必须得用单引号括起来 不能省略 也不能改用双引号

预备操作：

Create table student
(
  Name varchar(20) null,
  Age int
);

Insert into student values (‘张三’,88);
Insert into student values (‘Tom’,66);
Insert into student values (‘a_b’,22);
Insert into student values (‘c%d’,44);
Insert into student values (‘abc_fe’,56);
Insert into student values (‘haobin’,25);
Insert into student values (‘HaoBin’,88);
Insert into student values (‘c%’,66);
Insert into student values (‘long’’s’,100);
Select * from student where name like ‘%\%%’ escape ‘\’;
Select * from student where name like ‘%\_%’ escape ‘\’

-----escape 指定转义符

聚合函数

函数分类：

单行函数：每行返回一个值

多行函数：多行返回一个值（聚合函数是多行函数）

例子：

Select lower(ename) from emp;---返回14行数据
Select max(sal) from emp;---返回1行数据

聚合函数分类：

Max()

Min()

Avg()

Count()：求个数

Count(*)：返回表中所有得记录的个数

Count(字段名)：返回字段值非空记录的个数，重复记录也被当作有效记录

Count(distinct 字段名)：返回字段的不重复且非空记录的个数

注意：判断如下sql语句正确与否

Select max(sal) “最高工资”,min(sal) “最低工资”,count(*) “员工人数” from emp; ---ok
Select max(sal),lower(ename) from emp; ---error

Group by

格式：group by 字段的集合

功能：把表中的记录按照字段分成不同的组

例子：查询不同部分平均工资

Select deptno,avg(sal) as “部门平均工资” from emp group by deptno

注意：group by a,b,c用法

    先按a分组，若a相同，再按b分组，若b相同，再按c分组，最终统计最小分组的信息。

错误示范：

Select deptno,avg(sal) as “部门平均工资”,ename from emp group by deptno
Select deptno,ename from emp group by deptno;
Select deptno,job,sal from emp group by deptno,job,

Group by之后select后面只能出现分组后的整体信息，不能出现组内成员的详细信息

Having：对分组后的信息进行过滤

1）Having子句是用来对分组之后的数据进行过滤，因此使用having通常先使用group by；

2）如果没使用group by 但使用了having则意味着having把所有的记录当作一组来进行过滤（极少用）  

Select count(*) from emp having avg(sal)>1000

3）having子句出现的字段必须是分组之后的组的整体信息，having子句不允许出现组内的详细信息

4）尽管select字段中可以出现别名，但是having子句中不能出现字段的别名，只能使用字段最原始的名字

5）Having和where的异同：

同：都是对数据过滤，只保留有效的数据

    都不允许出现字段别名

异：where是对原始记录过滤，having是对分组后的记录过滤；

where必须写在having前面，顺序不可颠倒；

where子句不能出现聚合函数

例子：

（where和having的位置不能调换）

Select * from new_table_name

连接查询

将两张或两张以上的表按照一定的条件连接起来，从中检索出来满足条件的数据。

内连接

Select …… from A,B的用法

结果：行数是A和B的乘积，列数是A和B之和，即A，B记录组合在一起，形成笛卡尔积

例子：select * from emp,dept

Select …… from A,B where …… 用法

结果：产生的笛卡尔积用where条件过滤

例子：select * from emp,dept where empno = 7369

Select …… from A join B on …… 用法

结果：

例子：

SQL92标准和SQL99标准的区别，即select …… from A,B where …… 与select …… from A join B on ……的比较

---sql99标准

select "E".ename "员工名称","D".dname "部门名称"

     from emp "E"

 join dept "D" ---join是连接

 on "E".deptno="D".deptno ---on连接条件 on不能省，有join必须有on

---sql92标准

select *

     from dept,emp

 where dept.deptno=emp.deptno

---上面输出结果一样，推荐使用sql99标准

---1.sql99更容易理解

---2.sql99中，on和where分工不同，on指定连接条件，where对连接产生的临时表过滤

select、from、where、join、on、group、order by、top、having的混合

---混合过滤

---混合过滤

select top 3 "E".ename,"D".dname,"E".sal,"S".grade

     from emp "E"

 join dept "D"

 on "E".deptno="D".deptno

 join SALGRADE "S"

 ON "E".sal between "S".LOSAL and "S".HISAL

 where "D".dname not like '_A%'

 order by "E".sal desc

---查询顺序

---select...

---top...

---from...

---join...

---on...

---where...

---group by...

---having...

---order by...

---例子：将工资大于1500的所有员工按照部门分组 把部门平均工资大于2000的最高前两位输出

---例子：将工资大于1500的所有员工按照部门分组 把部门平均工资大于2000的最高前两位输出
select "T".*,"D".dname,"S".GRADE
   from dept "D"
   join (
          select top 2 deptno,avg(sal) as "avg_sal"
             from emp
              where sal>1500
              group by deptno
              having avg(sal)>2000
              order by "avg_sal" desc ---除非指定了top，否则order by不能放在查询子句中
        ) "T"
    on "T".deptno="D".deptno
    join SALGRADE "S"
    on "T"."avg_sal" between "S".LOSAL and "S".HISAL 
---等价于
select "T".*,"D".dname,"S".GRADE
   from (
          select top 2 "E".deptno,avg("E".sal) as "avg_sal"
             from emp "E"
              join dept "D"
              on "D".deptno="E".deptno
              join SALGRADE "S"
              on "E".sal between "S".LOSAL and "S".HISAL
              where "E".sal>1500
              group by "E".deptno
              having avg("E".sal)>2000
              order by "avg_sal" desc
        ) "T"
    join dept "D"
    on "D".deptno="T".deptno
    join SALGRADE "S"
    on "T"."avg_sal" between "S".LOSAL and "S".HISAL

习题练习：

---求出每一个员工的姓名 部门编号 薪水和薪水等级

---求出每一个员工的姓名 部门编号 薪水和薪水等级
select "E".deptno,"E".ename,"E".sal,"S".GRADE 
    from emp "E"
    join SALGRADE "S"
    on "E".sal between "S".losal and "S".hisal

---查出每个部门编号 部门名称 部门所有员工的平均工资 平均工资等级

---查出每个部门编号 部门名称 部门所有员工的平均工资 平均工资等级
select "T".deptno "部门编号","D".dname "部门名称","T"."avg_sal" "部门平均工资","S".GRADE "工资等级" 
    from (
            select deptno,avg(sal) as "avg_sal"
                 from emp
                 group by deptno
         ) "T"
    join SALGRADE "S"
    on "T"."avg_sal" between "S".losal and "S".hisal
    join dept "D"
    on "D".deptno="T".deptno

---等价于（可交换表格顺序）

select "T".deptno "部门编号","D".dname "部门名称","T"."avg_sal" "部门平均工资","S".GRADE "工资等级" 
    from SALGRADE "S"
    join (
            select deptno,avg(sal) as "avg_sal"
                 from emp
                 group by deptno
         ) "T"
    on "T"."avg_sal" between "S".losal and "S".hisal
    join dept "D"
    on "D".deptno="T".deptno

---求出emp表领导姓名（in的集合不可包含null）

---求出emp表领导姓名（in的集合不可包含null）
select *
    from emp
    where EMPNO
    in (select mgr from emp where mgr is not null)

---求出平均薪水最高的部门和部门平均工资

select top 1 deptno,avg(sal) as "avg_sal"
    from emp
    group by deptno
    order by "avg_sal" desc

---等价于
select "T".*
   from (
          select deptno,avg(sal) as "avg_sal"
             from emp
              group by deptno
        ) "T"
    where "T"."avg_sal"=(
                          select max("avg_sal")
                              from (
                                     select deptno,avg(sal) as "avg_sal"
                                         from emp
                                         group by deptno
                                   ) "T1"
                         )

---排除工资最低的人，剩下人工资最低的前3人的姓名 工资 部门编号 部门名称 工资等级 输出

---排除工资最低的人，剩下人工资最低的前3人的姓名 工资 部门编号 部门名称 工资等级 输出
select top 3 "T".*,"D".dname,"S".GRADE 
   from (
           select ename, sal,deptno
              from emp
               where sal >(select min(sal) from emp)
        ) "T"
    join dept "D"
    on "D".deptno="T".deptno
    join SALGRADE "S"
    on "T".sal between "S".LOSAL and "S".HISAL ---between后and前后的条件不可调换顺序
    order by "T".sal asc

外连接：返回满足连接条件的数据和部分不满足连接条件的数据

---舍弃
select * from dept "D"
   left join emp "E"
   on E.deptno="D".deptno
---使用
select * from emp "E"
   left join dept "D"
   on E.deptno="D".deptno

   ---一般左连接，以成员表（emp）为主表，集合表（dept）为连接表

完全连接

交叉连接

select * from emp cross join dept

---等价于

select * from emp,dept

自连接：一张表自己和自己连接起来

---自连接：薪水最高的员工信息，不准用聚合函数

---自连接：薪水最高的员工信息，不准用聚合函数
select * from emp
   where empno
   not in (---获取不包含最高薪水的编号
            select distinct("E1".EMPNO)
             from emp "E1"
             join emp "E2"
             on "E1".sal<"E2".sal
          )

联合：表和表之间的数据以纵向的方式连接在一起（上面的所有连接都是横向连接）

---联合：输出每个员工的姓名 工资 上司的姓名

---联合：输出每个员工的姓名 工资 上司的姓名
select "E1".ename,"E1".sal,"E2".ename "上司"
   from emp "E1"
   join emp "E2"
   on "E1".mgr="E2".empno
union
select ename,sal,'最大boss' from emp where mgr is null

注意：联合的子句列数相同，数据类型一致。

嵌套查询---分页查询

---分页

---工资从高到低排序，输出工资10-12的员工信息

---工资从高到低排序，输出工资10-12的员工信息
select top 3 *
   from emp
   where EMPNO
   not in (select top 9 EMPNO from emp order by sal desc)
   order by sal desc

---工资从高到低排序，此计算机工资13-15员工信息

---工资从高到低排序，此计算机工资13-15员工信息
select top 3 *
   from emp
   where EMPNO
   not in (select top 12 EMPNO from emp order by sal desc)
   order by sal desc

视图

视图优点：简化查询，加强数据查询安全性

视图缺点：增加了数据库维护成本；只是简化查询，并不是优化查询（速度）

事务

事务和线程的关系：

事务是通过锁来解决很多问题

线程同步就是通过锁来解决的 synchronized

事务和第三方插件关系：

直接使用事务数据库技术难度很大 很多人是借助第三方插件来实现，因此一般人无需厌旧数据库中事务的语法细节

第三方插件要想完成预期的功能，一般必须借助数据库中的事务机制来实现