SQLServer 2008 (五)

复制表时，一次性向表中追加所有约束：

use mydb
go
alter table jobs
add primary key(job_id),
default 'Neew Psition' for job_desc,
check(max_lvl<=250),
check(min_lvl>=10)
go

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

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：m
多对多关系，如师生关系,记为m：n，使用最多。


“一对多”列举：模拟一个场景，有一个小超市，日常工作为进货和卖货两件事。在此情况下，第二天要卖的货，肯定是前一天进来的。
商品表:

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

销售表:

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

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

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

学生表：

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

查询：

级联查询：
查询：李老师所教的学生信息，因为老师信息在教师表中，学生信息在学生表中，师生之间的关系情况在教师学生表中。要完成以上操作，需要使用多表联合查询。
查询时所需要的条件就是关系！
查询时给表或列重新命名（别名）的场合：
如果表名比较长，要起别名，因为条件语句构造时，可以少写一些代码；
在特定情况下，一定要起别名（比如在一个表时作复杂查询时，一个表作为两个表使用，只能用别名区分）

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

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

select * from employees
where reportsto is null
go

或者

select * from employees
where reportsto is not null
go

聚合函数(也叫分组函数)：group by
进一步筛选：having
排序函数：order by asc(desc)

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

--按照工号进行统计订单数
select employeeid 工号,count(*) '订单数'
from orders
group by employeeid
order by employeeid
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的挑选顺序：from子句最早执行；

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

简单查询：一条语句只包含一个select
复杂查询：一条语句包含多个select

要求查询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)四种方式。

一、与“增”有关的关键字：
create 增加数据库对象，database,table,view,trigger,cursor等。
insert 向表或视图中添加记录
alter


二、与“删”有关的关键字：
1、alter 删除表中的列，删除表中列的约束
2、delete 删除表或视图中的记录
3、drop


三、与“改”有关的关键字：
1、alter  修改表的结构，向表中添加新列；
2、update 修改表的记录


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