2013-04-29 sql server2005

1.数据库学习数据库

（1）数据库存储数据：字段、记录、表、约束（主键、外键、唯一键、非空、default）

（2）数据库操作数据：insert、update、delete、T-SQL 存储过程、函数、触发器

（3）数据库显示数据：select（及其重要）

2.建立数据库

（1）打开服务

（2）打开连接

3.事务：作为单个逻辑工作单元执行的一系列操作。（一个逻辑工作单元要成为事务，要满足ACID（原子性、一致性、隔离性、持久性）属性）

4.表的相关数据

 （1）字段、列、属性：模拟一个事务的某一个静态的特征

      元组、记录：字段的组合，表示具体的事物

      表：记录的组合，表示同一个类型的事务的集合

      表和字段、记录的关系：字段是事务的属性，记录是事务本身，表示事务的集合

      

 （2）create table 命令：

      通过图形化界面建表

      Create table 用的是（）而不是{}，且组后一句建议不加“，”因为oracle的不支持有“，”

      用到的关键字constraint、references等……

 （3）什么是约束

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

       分类：

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

            外键约束：通过外键约束从语法上保证所关联的其他事务一定存在（msdn，指定表中强制引用的完整性）

            事务和事务之间的关系是通过外键来体现的

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

            Default约束：保证事务的属性一定有一个值（注意属性的个数）

            唯一约束（unique）：类似于主键，不允许事务属性取值重复，但是，它允许其中有且只有一列为null（但是oracle允许多列为null）

       主键和唯一约束的区别在于：  

            not null:要求用户必须为该属性赋一个值，否则语法出错         

 （4）表和约束的区别：

          数据库通过表解决数据的存储问题

          数据库是通过约束来解决事务取值的范围和合法性问题

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

 （5）什么是关系：

  通过外键建立起来的表之间的联系

分类（）：

 一对一：

一对多：把A表的主键添加为B表的外键，那么一个A对应多个B。（即在多的一方添加外键）

多对多：

 （6）主键：唯一的标识一个事务的一个字段或多个字段的组合

       注：含有主键的表叫做主键表

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

           一定不要用业务主键当主键，要用无关的代理主键作为主键

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

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

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

         注意：外键通常是来自外表的主键而不是唯一键，因为唯一键可能为null。

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

    删除主键表和外键表的次序：先删外键表，后删主键表，否则会导致外键表中的数据引用出错。           

               

 （8）查询

     1.计算列

         select * from emp;

    --*表示所有的

   --from emp表示从emp表查询

 select  empno, ename from emp;

 

 select ename, sal from emp;

 select ename, sal*12 as '年薪' from emp;

                 --as可以省略;但是一定要记住:"年薪"不可写成‘年薪’(为了保证与oracle的兼容性)或年薪

 select ename, sal*12 as "年薪", sal '月', job from emp;

 select 888 form emp;

              --ok

              --输出的行数是emp表的函数，每行只有一个字段值是

   select 5;

              --ok

             --不推荐的做法

     2. distinct（不允许重复的）

select deptno from emp; --14行记录不是行记录

select distinct deptno from emp;  --distince deptno 会过滤掉重复的deptno

select distinct comm from emp;  --distinct也可以过滤掉重复的null  或者说如果有多个null 只输出一个

select distinct comm, deptno from emp;--把comm和deptno的组合进行过滤

select deptno, distinct comm from emp;--error 逻辑上有冲突

        select 10000 from emp; --14行记录

       

     3. Between

--查找工资在到之间(包括和)的所有的员工的信息

select * from emp 

where sal>=1500 and sal<=3000

等价于

select * from emp 

where sal between 1500 and 3000

--查找工资在小于或大于之间的所有的员工的信息

select * from emp 

where sal<1500 or sal>3000

等价于

select * from emp 

where sal not between 1500 and 3000

     4.in（属于若干个孤立的值）

select * from emp where sal in (1500, 3000, 5000)

等价于

select * from emp 

where sal=1500 or sal=3000 or sal=5000

select * from emp where sal not in (1500, 3000, 5000) --把sal既不是也不是也不是的记录输出

等价于

select * from emp 

where sal<>1500 and sal<>3000 and sal<>5000

--数据库中不等于有两种表示： !=    <>   推荐使用第二种

--对或取反是并且 对并且取反是或

     5.top（显示前n行记录）

select * from emp;

select top 5 * from emp;

select top 15 percent * from emp; --输出的是个，不是个

--把工资在到之间(包括和)的员工中工资最高的前个人的信息输出

select top 4 * 

from emp

where sal between 1500 and 3000

order by sal desc  --desc降序不写则默认是升序

     6.null（没有值）

         零和null不同，null表示空值，无值，零时一个确定的值

         Null不参与< > 、!=、=这些运算

         Null参与如下运算：is  is not

  select * from emp where comm != null; --输出为空error

select * from emp where comm = null;--输出为空error

--总结：null不能参与<>  !=  =运算

--null可以参与is  not is

select * from emp where comm is null; --输出奖金为空的员工的信息

select * from emp where comm is not null; --输出奖金不为空的员工的信息

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

create table t1 (name nvarchar(20), cnt int, riqi datetime);  

insert into t1 values (null, null, null)

select * from t1;

select * from emp;

--输出每个员工的姓名年薪(包含了奖金)  comm假设是一年的奖金

select empno, ename, sal*12+comm "年薪" from emp;

--本程序证明了：null不能参与任何数据运算否则结果永远为空     

        isnull（a， 0）如果a时null就返回0，否则返回a的值   

select top 5  from emp;

select * from emp where comm is not null

select ename, sal*12+comm from emp;

select ename, sal*12+isnull(comm, 0) "年薪" from emp;

--isnull(comm, 0) 如果comm是null 就返回零否则返回comm的值

     7.order by（以某字段为排序的依据）

--asc是升序的意思默认可以不写 desc是降序

select * from emp order by sal; --默认是按照升序排序

select * from emp order by deptno, sal; --先按照deptno升序排序，如果deptno相同，再按照sal升序排序

select * from emp order by deptno desc, sal;

--先按deptno降序排序如果deptno相同再按照sal升序排序

--记住sal是升序不是降序

--order by a desc, b, c, d    desc只对a产生影响不会对后面的b  c d 产生影响

select * from emp order by deptno, sal desc

--问题：desc是否会对deptno产生影响?

--答案：不会

--先按deptno升序，如果deptno相同，再按sal降序

     8. 模糊查询

          格式：

              Select  字段的集合 from表名 where 某个字段的名字 like 匹配的条件

          其中，匹配条件通常有通配符

       通配符

        %  表示任意0个或者多个字符

        select * from emp where ename like '%A%'  --ename只要含有字母A就输出

        select * from emp where ename like 'A%'  --ename只要首字母是A的就输出

        select * from emp where ename like '%A'  --ename只要尾字母是A的就输出

      _  下划线表示任意单个字符

        select * from emp where ename like '_A%' --ename只要第二个字母是A的就输出

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

        select * from emp where ename like '_[A-F]%' --把ename中第二个字符是A或B或C或D或E或F的记录输出

      [a, f]   a或f

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

        select * from emp where ename like '_[^A-F]%' --把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是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;

select * from student where name like '%\%%' escape '\'  --把name中包含有%的输出

select * from student where name like '%\_%' escape '\' --把name中包含有_的输出

     9. 聚合函数

        函数分类：

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

             多行函数：多行返回一个值

             例子：

            select lower(ename) from emp; -- 最终返回的是行lower()是单行函数

          select max(sal) from emp; --返回行max()是多行函数

聚合函数的分类

max（）

min（）

avg（）
    count（）

select count(*) from emp;  --返回emp表所有记录的个数

select count(deptno) from emp;  --返回值是这说明deptno重复的记录也被当做有效的记录

select count(distinct deptno) from emp; --返回值是 统计deptno不重复且非null的记录的个数

select count(comm) from emp; --返回值是这说明comm为null的记录不会被当做有效的记录

select max(sal) "最高工资", min(sal) "最低工资", count(*) "员工人数" from emp; --ok 

select max(sal), lower(ename) from emp  //有冲突，单行函数和多行函数不可以通用！

     10.group by

use scott;

select * from emp;

--输出每个部门的编号和该部门的平均工资

select deptno, avg(sal) as "部门平均工资" 

from emp 

group by deptno 

-- 判断下面语句是否正确

select deptno, avg(sal) as "部门平均工资", ename

from emp

group by deptno 

-- 判断下面语句是否正确

select deptno, ename

from emp

group by deptno 

总结： 使用了group by 之后 select 中只能出现分组后的整体信息，不能出现组内的详细信息

group by a, b 的用法

--error

select deptno , job, sal

from emp

group by deptno, job

--error

select *

from emp

group by deptno, job

--ok

select deptno , job, avg(sal) 

from emp

group by deptno, job

--ok

select deptno , job, avg(sal) "平均工资", count(*) "部门人数", sum(sal) "部门的总工资", min(sal) "部门最低工资" 

from emp

group by deptno, job

order by deptno

select comm, count(*) 

from emp

group by comm

select max(sal) from emp; --默认把所有的信息当做一组

     11.having(对分组以后的信息进行过滤)

--输出部门平均工资大于的部门的部门编号部门的平均工资

select deptno, avg(sal)

from emp

group by deptno

having avg(sal) > 2000

--判断下列sql语句是否正确

select deptno, avg(sal) as "平均工资"

from emp

group by deptno

having avg(sal) > 2000

--判断下列sql语句是否正确

select deptno, avg(sal) as "平均工资"

from emp

group by deptno

having  "平均工资" > 2000  --error

--判断下列sql语句是否正确

select deptno "部门编号", avg(sal) as "平均工资"

from emp

group by deptno

having deptno > 1

--判断下列sql语句是否正确

select deptno "部门编号", avg(sal) as "平均工资"

from emp

group by deptno

having "部门编号" > 1   --error

--判断下列sql语句是否正确

select deptno, avg(sal) as "平均工资"

from emp

group by deptno

having  deptno > 10

--判断下列sql语句是否正确

select deptno, avg(sal) as "平均工资"

from emp

group by deptno

having count(*) > 3

--判断下列sql语句是否正确

select deptno, avg(sal) as "平均工资"

from emp

group by deptno

having ename like '%A%'

--把姓名不包含A的所有的员工按部门编号分组，

--统计输出部门平均工资大于的部门的部门编号部门的平均工资

select deptno, avg(sal) "平均工资"

from emp

where ename not like '%A%'

group by deptno

having avg(sal) > 2000

--把工资大于，

--统计输出部门平均工资大于的部门的部门编号部门的平均工资

select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"

from emp

where sal > 2000  --where是对原始的记录过滤

group by deptno

having avg(sal) > 3000  --对分组之后的记录过滤

--判断入选语句是否正确

select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"

from emp

group by deptno

having avg(sal) > 3000  --对分组之后的记录过滤

where sal > 2000  --where是对原始的记录过滤

总计： 所有select的参数的顺序是不允许变化的，否则编译时出错 

select * from emp;

select count(*) 

from emp 

having avg(sal) > 1000

select ename, sal "工资" 

from emp

where sal > 2000

select ename, sal "工资" 

from emp

where "工资"  > 2000

select deptno, avg(sal) "平均工资", count(*) "部门人数", max(sal) "部门的最高工资"

into emp_2

from emp

where sal > 2000  --where是对原始的记录过滤

group by deptno

having avg(sal) > 3000  --对分组之后的记录过滤

select * from emp_2      

     12.连接查询

         定义

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

         分类

             内连接（重点难点）

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

from emp "E"

join dept "D"

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

1.

select ... from A, B的用法

--emp是行8 列 dept是行3列

select * from emp, dept

2. 

select ... from A, B where ... 的用法

--输出行11列

select * 

from emp, dept 

where empno = 7369

3. 

select ... from A join B on ... 的用法

--输出列70行

select *

from emp "E"

join dept "D"  --join是连接

on 1=1  --on连接条件

--输出列70行

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

from emp "E"

join dept "D"  --join是连接

on 1=1  --on连接条件 on不能省有join就必须有on

--判断下面语句是否正确

select deptno

from emp "E"

join dept "D"  --join是连接

on 1=1 

--判断下面语句是否正确

select "部门表".deptno "部门编号"

from emp "员工表"

join dept "部门表"  --join是连接

on 1=1 

--考虑下面语句的输出结果是什么

--答案：14行11列

select *

from emp "E"

join dept "D"  --join是连接

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

--考虑下面语句的输出结果是什么

--答案：行2列

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

from emp "E"

join dept "D"  --join是连接

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

select * 

from emp, dept

where emp.deptno = dept.deptno

等价于

select * from emp

join dept

on emp.deptno = dept.deptno                

             外连接

                不但返回满足连接条件的所有记录，而且会返回部分不满足条件的记录

               左外连接：

               右外连接：

             完全连接

                

             交叉连接：select * from emp cross join dept 等价于 select * from emp， dept

             自连接

               自己和自己连接查询

  用聚合函数 求薪水最高的员工的信息

select * from emp where sal = (select max(sal) from emp)

不准用聚合函数 求薪水最高的员工的信息

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, '已是最大老板' from emp where mgr is null

select * from emp;

     13.嵌套查询

5.数据库中的字符串必须用‘’括起来，“”是用来表示某个数据或事务的名字时候用。