sql server2008

查看用户数据库mydb中的jobs表，因为此表是通过表复制功能得到的，所以除了主键列的自增属性外，所有约束都丢失了。

在向复制表中追加约束之前，先查看源表中有哪些约束，根据源表中的约束进行追加：

通过观察可以看到，源表jobs中有两个检查约束，一个默认约束和一个主键约束。现在将这四个约束一次性追加到目标表jobs上。

use mydb go select * from jobs go

一次性向表中追加所有约束：

因为向表中添加约束相当于修改表的结构，因此使用关键字alter

use mydb go alter table jobs add primary key(job_id), --主键约束 default 'New Position' for job_desc,--默认约束 check(max_lvl<=250), --检查约束 check(min_lvl>=10)--检查约束 go

相看追加约束的目标表jobs结构：

一次性删除多个、多种类型的约束：

alter table jobs drop constraint    CK__jobs__max_lvl__4BAC3F29,    CK__jobs__min_lvl__4CA06362,    DF__jobs__job_desc__49C3F6B7,    PK__jobs__4AB81AF0 go

 

最后一个重要的约束外键约束，用于表示多个表之间的关联关系，一般情况下，使用外键的场合是至少两个表以上，但在特殊情况下，在一个表中也可以有外键。

Sql中的表关系有三种：

1、    一对一关系，如丈夫表和妻子表，使用非常少；记为1：1

2、    一对多关系，如父亲表和儿子表，商品表和销售表，使用比较多；记为1：m

3、    多对多关系，如师生关系,记为m：n，使用最多。

对于多对多关系的处理是将其转换为多个一对多关系来处理的。

一对一和一对多关系的处理，相当于平面问题；

多对多关系的处理，相当于三维问题；

在立体几何学习时，将三维问题，转换为平面问题来解决；

商品表和销售表：

模拟一个场景，有一个小超市，日常工作为进货和卖货两件事。在此情况下，第二天要卖的货，肯定是前一天进来的。

 

在有多对多关系的多个表中，根据逻辑关系的先后顺序，分为主表和从表。

创建商品表Item：

if exists(select name from sysobjects  where name='item'    and  xtype='u') drop table item go create table item (    itemid  char(3) primary key,    itemname  nvarchar(20) not null unique,    quantity  int not null check(quantity>0),    price  money not null check(price>0), ) go

向商品表item中添加记录：

将商品表内容创建一个文件f:/data.sql:

将文件内容批量导入到表item中：

bulk insert item from 'f:/data.sql' with (     fieldterminator=',',    rowterminator='\n' ) go

创建销售表sales:

if exists(select name from sysobjects  where name='sales'    and  xtype='u') drop table sales go create table sales (    saleid  int identity primary key,    itemid  char(3) not null,--代表销售的商品    quantity  int not null check(quantity>0),    sale_date  datetime default getdate(), ) go

向销售表sales添加记录：

insert into sales(itemid,quantity)  values('001',2) insert into sales(itemid,quantity)  values('002',1) go

再向表中添加记录：

insert into sales(itemid,quantity)  values('008',3) go

观察可知，销售表中销售的商品并不是商品中表的，所以，用户表和销售表之间并没有任何关系，只是两个独立的表。要想两个表中的数据具有一致性，需要使用外键约束，将两个表关联起来。

外键约束，它要建在哪个表？主表还是从表？

建在从表中！

从表中的外键约束列正好对应主表中的主键列。

外键所在列与主键所在列名可以不同，但一般情况下我们设计为一样。

create table sales (    saleid  int identity primary key,    itemid  char(3) not null--代表销售的商品       constraint fk_itemid foreign key       references item(itemid)       on delete cascade       on update cascade,    quantity  int not null check(quantity>0),    sale_date  datetime default getdate(), ) go

向表中添加记录：

insert into sales(itemid,quantity)  values('001',2) insert into sales(itemid,quantity)  values('002',1) go

再向表中添加记录：

insert into sales(itemid,quantity)  values('008',3) go

再向销售表中添加记录：

insert into sales(itemid,quantity)  values('001',1) insert into sales(itemid,quantity)  values('002',2) insert into sales(itemid,quantity)  values('003',3) insert into sales(itemid,quantity)  values('004',2) go

测试主表与从表的关系之级联更新和级联删除：

--删除主表中一列id='001' delete from item where itemid='001' go

此时sales中的信息：

可知从表中与001有关的行记录也被删除了，这就是“级联删除”的作用。

销售表只能销售商品中存在的商品，一旦主表中的某种商品不存在，销售表也不会存在。

在任何情况下，从表都是依赖于主表的。

--修改item表中的'002'为'012' update item set itemid='012' where itemid='002' go

 

表的多对多关系，是将一个多对多关系，转换为多个一对多关系进行处理。处理方式为添加中间表，用来表示“多对多”。以师生关系为例来说明这个问题。

学生表，教师表，师生关系表；

教师表teacher:

if exists(select name from sysobjects  where name='teacher'    and  xtype='u') drop table teacher go create table teacher (     tid  char(3) primary key,    tname  nvarchar(6) not null,    --... ) go

学生表student:

if exists(select name from sysobjects  where name='student'    and  xtype='u') drop table student go create table student (    sid  char(3) primary key,    sname  nvarchar(6) not null,    --... ) go

中间表teacher_student：（处理多对多关系的核心表）

if exists(select name from sysobjects  where name='teacher_student'    and  xtype='u') drop table teacher_student go create table teacher_student (    ts_id  int identity primary key,    teacher_id  char(3) not null --与teacher表关联       --constraint fk_teacher_id       references teacher(tid)       on delete cascade       on update cascade,    student_id  char(3) not null --与student表关联       --constraint fk_student_id       references student(sid)       on delete cascade       on update cascade,    --... ) go

以上三表中，何为主表和从表？

教师表和学生表都是主表，师生关系表teacher_student是从表。即两个主表，一个从表。

向表teacher中添加记录：

insert into teacher values('t01','张老师') insert into teacher values('t02','李老师') insert into teacher values('t03','王老师') go

向学生表中添加记录：

insert into student values('s01','刘备') insert into student values('s02','公孙赞') insert into student values('s03','曹操') insert into student values('s04','袁绍') insert into student values('s05','孙坚') insert into student values('s06','董卓') insert into student values('s07','袁术') go

向老师学生表中添加记录

张老师与学生之间的“一对多”关系：

insert into teacher_student  values('t01','s01') insert into teacher_student  values('t01','s02') insert into teacher_student  values('t01','s03') insert into teacher_student  values('t01','s04') insert into teacher_student  values('t01','s05') insert into teacher_student  values('t01','s06') insert into teacher_student  values('t01','s07') go

李老师与学生之间的“一对多”关系：

insert into teacher_student  values('t02','s01') insert into teacher_student  values('t02','s02') insert into teacher_student  values('t02','s03') insert into teacher_student  values('t02','s04') insert into teacher_student  values('t02','s05') go

王老师与学生之间的“一对多”关系：

insert into teacher_student  values('t03','s04') insert into teacher_student  values('t03','s05') insert into teacher_student  values('t03','s06') insert into teacher_student  values('t03','s07') go

 

查询：李老师所教的学生信息，因为老师信息在教师表中，学生信息在学生表中，师生之间的关系情况在教师学生表中。要完成以上操作，需要使用多表联合查询。

查询时所需要的条件就是关系！

 

查询时给表或列重新命名（别名）的场合：

1、    如果表名比较长，要起别名，因为条件语句构造时，可以少写一些代码；

2、     在特定情况下，一定要起别名（比如在一个表时作复杂查询时，一个表作为两个表使用，只能用别名区分）

 

select sid,sname from student,teacher,teacher_student where teacher.tname='李老师' and teacher.tid=teacher_student.teacher_id and student.sid=teacher_student.student_id go

以上查询代码执行时，得到了正确的结果，但代码较长，使用“别名”机制简化以上代码。

select sid,sname from student as s,teacher  t,teacher_student ts where t.tname='李老师' and t.tid=ts.teacher_id and s.sid=ts.student_id go

以上查询语句中的别名可有可无，因为在从表中，外键所在列与主表中主键所在列名不同；

但通常情况下，从表中的外键列名与主表中主键列相同，此时，必须每个表起别名，以作区别。

 

查询语句中select的挑选顺序：

from子句最早执行；

null值或not null作为查询条件的情况：

查看northwind中的employees表：

use northwind go select * from employees go

查找reportsto为null的行记录：

select * from employees where reportsto is  null go

查找reportsto不为null的行记录：

select * from employees where reportsto is  not null go

 

在java应用程序中测试两种关系：

1、    一对一关系 one-to-one

2、    一对多关系 one-to-many

3、    多对多关系 many-to-many

 

聚合函数,也称为“分组函数”

group by  通过什么字段分组

having 进一步分组

使用northwind数据库中的orders（订单表）测试聚合函数。

--统计5号员工的订单数 select count(*) '订单数' from orders where employeeid=5 go

获取所有员工的订单数，使用聚合函数group by

select employeeid 工号,count(*) '订单数' from orders group by employeeid go

--按照工号进行统计订单数 select employeeid 工号,count(*) '订单数' from orders group by employeeid order by employeeid go

按照订单数的降序排序：

select employeeid 工号,count(*) '订单数' from orders group by employeeid order by count(*)  desc go

可以使用count(*)的别名来代替count(*)进行排序。

select employeeid 工号,count(*) 订单数 from orders group by employeeid order by 订单数  desc go

结果与上面相同。

获取订单数过100的情况：

select employeeid 工号,count(*) 订单数 from orders group by employeeid having count(*)>100 order by 订单数 desc go

select的查询顺序：

select employeeid 工号,count(*) 订单数 --4 from orders  --1 group by employeeid --2 having count(*) > 100 --3 order by 订单数 desc  --5 go

复杂查询：带有子查询的查询语句。

mydb.dbo.jobs表的内容：

要求查询max_lvl列等于最大值的行记录？

select * from jobs where max_lvl=(select max(max_lvl) from jobs) go

将authors表中属于某些州的作者查询出来：

select * from authors where state in ('ks','ut','mi','tn') go

假定一个学生表，信息包括学号，姓名，年龄，性别等信息。要求查询学生表所有姓兰的女同学信息。

select * from student where ssex='女' and sname like '兰%' --sname like '%兰%'  带有兰字的信息 go

 

对于表中数据的操作，就是增(C)、删(D)、改(U)、查(R)四种方式。

一、与“增”有关的关键字：

1、    create 增加数据库对象，database,table,view,trigger,cursor等。

2、    insert 向表或视图中添加记录

3、    alter

二、与“删”有关的关键字：

1、alter 删除表中的列，删除表中列的约束

2、delete 删除表或视图中的记录

三、与“改”有关的关键字：

1、alter  修改表的结构，向表中添加新列；

2、update 修改表的记录

 

四、与“查”有关的关键字

select