复制表时,一次性向表中追加所有约束:
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