·¢²¼Ê±¼ä : ÐÇÆÚÈÕ ÎÄÕÂSQL Sever 2005 Ï°ÌâÓë´ð°¸¸üÐÂÍê±Ï¿ªÊ¼ÔĶÁee28118681c758f5f71f676c
UPDATE stu2 SET ѧºÅ=REPLICATE('0',4-len(@max))+@max FROM stu2 INNER JOIN inserted on stu2.ѧºÅ=inserted.ѧºÅ
Ö´ÐÐÒÔÉÏ´úÂ룬²é¿´studentsdbÊý¾Ý¿âÖÐÊÇ·ñÓÐstu2±í£¬Õ¹¿ªstu2£¬²é¿´Æä´¥·¢Æ÷ÏîÖÐÊÇ·ñÓÐstu_str´¥·¢Æ÷¡£
ÔÚ²éѯÉè¼ÆÆ÷µÄ±à¼´°¿ÚÊäÈëÒÔÏ´úÂ룺
INSERT INTO stu2(ѧºÅ,ÐÕÃû,ÐÔ±ð) VALUES('0001','ÕÅÖ÷','Å®')
ÔËÐÐÒÔÉÏ´úÂ룬²é¿´stu2±íµÄ±ä»¯Çé¿ö£¬ÎªÊ²Ã´²åÈë¼Ç¼µÄѧºÅÖµ·¢ÉúÁ˸ı䣿
´ð°¸
Ò»
crate database studb on
(name=studb1_dat,
filename='g:\\students\\studb1.ndf', size=5Mb,
maxsize=10Mb, filegrowth=1Mb )
alter database studb add file
(name=studb1_dat,
filename='g:\\students\\studb1.ndf', size=5Mb,
maxsize=10Mb, filegrowth=1Mb )
use studentdb create table grade (ѧºÅchar(4),
¿Î³Ì±àºÅchar(4), ·ÖÊýchar (5) )
alter table grade
alter column ѧºÅchar(4) not null
alter table grade add ±¸×¢ varchar(20)
insert into grade
(ѧºÅ,¿Î³Ì±àºÅ,·ÖÊý) values ('0004','0001','80')
delete from grade where ѧºÅ='0001'
update grade
set ±¸×¢='³É¼¨Á¼ºÃ' where ѧºÅ='0004'
¶þ
select ѧºÅ,ÐÕÃû,³öÉúÈÕÆÚfrom student_info
select ÐÕÃû,¼Òͥסַfrom student_info where ѧºÅ='0002' select ѧºÅ,ÐÕÃûfrom student_info where ÐÔ±ð='ÄÐ'
select ѧºÅ,·ÖÊýfrom grade where ·ÖÊýbetween 80 and 90 select avg(·ÖÊý) from grade where ¿Î³Ì±àºÅ='0003'
select ¿Î³Ì±àºÅ,count(*) as ÈËÊýfrom grade group by ¿Î³Ì±àºÅ select ѧºÅ,ÐÕÃûfrom student_info where ÐÕÃûlike 'ÕÅ%'
select ѧºÅ,ÐÕÃû,ÐÔ±ð,³öÉúÈÕÆÚ,¼Òͥסַfrom student_info order by ÐÔ±ð,ѧºÅ
select ѧºÅ,avg(·ÖÊý) as ƽ¾ù·Öfrom grade group by ѧºÅ
select ÐÕÃû,³öÉúÈÕÆÚfrom student_info
where ÐÔ±ð=(select ÐÔ±ðfrom student_info where ÐÕÃû='ÁõÎÀƽ')
select ѧºÅ,ÐÕÃû,ÐÔ±ðfrom student_info where ѧºÅin (select ѧºÅfrom grade where ¿Î³Ì±àºÅin ('0002','0005'))
select a.ѧºÅ,b.ÐÕÃû,a.¿Î³Ì±àºÅ,a.·ÖÊý
from grade a inner join student_info b on a.ѧºÅ=b.ѧºÅ
where a.·ÖÊýin (select max(·ÖÊý) from grade group by ѧºÅ
select a.ѧºÅ,ÐÕÃû,sum(·ÖÊý) as ×ܳɼ¨
from student_info a left outer join grade b on a.ѧºÅ=b.ѧºÅ group by a.ѧºÅ,a.ÐÕÃû
insert into grade values ('0004','0006',76)
select a.¿Î³Ì±àºÅ,b.¿Î³ÌÃû³Æ,count(a.ѧºÅ) as Ñ¡ÐÞÈËÊý
from grade a left outer join curriculum b on a.¿Î³Ì±àºÅ=b.¿Î³Ì±àºÅ group by a.¿Î³Ì±àºÅ,b.¿Î³ÌÃû³Æ
Èý
3.1 alter table student_info add constraint pk_no primary key(ѧºÅ)
alter table curriculum add constraint pk_kc primary key(¿Î³Ì±àºÅ) 3.2 ÂÔ
3.3 create index grade_index on grade(·ÖÊý)
3.4 create unique index grade_id_ind on grade(ѧºÅ,¿Î³Ì±àºÅ) 3.5 ÂÔ
3.6 exec sp_helpindex grade
exec sp_helpindex student_info
3.7 drop index grade.grade_index 3.8 ÂÔ
3.9 create view v_stu_c
as
select a.ѧºÅ,a.ÐÕÃû,b.¿Î³Ì±àºÅ
from student_info a inner join grade b on a.ѧºÅ=b.ѧºÅ
select * from v_stu_c 3.10 create view v_stu_g
as
SELECT a.ѧºÅ,a.ÐÕÃû,b.¿Î³Ì±àºÅ,b.·ÖÊý,c.¿Î³ÌÃû³Æ
FROM student_info a INNER JOIN grade b ON a.ѧºÅ=b.ѧºÅ INNER JOIN curriculum c ON b.¿Î³Ì±àºÅ= c.¿Î³Ì±àºÅ
3.11 select ѧºÅ,ÐÕÃû,¿Î³ÌÃû³Æ,·ÖÊýfrom v_stu_g where ѧºÅ='0001' 3.12 alter view v_stu_c
as
SELECT a.ѧºÅ, a.ÐÕÃû, COUNT(b.¿Î³Ì±àºÅ) AS ÈËÊý FROM student_info a INNER JOIN grade b ON a.ѧºÅ= b.ѧºÅ GROUP BY a.ѧºÅ,a.ÐÕÃû
3.13 insert into v_stu_i values ('0015','³ÂæÃ','Å®')
3.14 update v_stu_g set ·ÖÊý=84 where ÐÕÃû='ÁõÎÀƽ' and ¿Î³ÌÃû³Æ='¸ßµÈÊýѧ' 3.15 DROP VIEW v_stu_c,v_stu_g
ËÄ
/* 4.1 */
SELECT * INTO stu_phone FROM student_info
ALTER TABLE stu_phone ADD µç»°ºÅÂëCHAR(7) NULL CREATE rule phone_rule AS
@phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' sp_bindrule phone_rule ,'stu_phone.µç»°ºÅÂë'
INSERT INTO stu_phone(ѧºÅ,ÐÕÃû,µç»°ºÅÂë) VALUES('0009','Íõ¹úÇ¿','1234567') /* 4.2 */
CREATE rule stusex_rule AS
@sex in ('ÄÐ','Å®')
sp_bindrule stusex_rule,'stu_phone.ÐÔ±ð' /* 4.3 */
sp_helptext stusex_rule
sp_rename stusex_rule,stu_s_rule /* 4.4 */
ALTER TABLE student_info ADD ԺϵCHAR(10) NULL