【SQLServer2005提供了很多的新...... SQL Server 2005提供了很多的新特性,但是这些新特性是否值得冒险尝试最新的发布?根据你自身的需要来帮助评估这些新的能力吧 现在是网络时代,大大提高了大家互相之间的数据共享,于是出现了数据库,而sql语言作为对数据库的标准查询语言大大简化了程序员的工作,使程序员更多的精力可以放在程序上,而不是数据库上。下面我举一些sql语句的例子共大家学习参考,如有不当欢迎批评指正! 建表: create table Teacher( Tno integer primary key, Tname char(6) not null, Title char(6), Dept char(10)); 插入数据: insert into Teacher values (101,'李华','讲师','计算机'); insert into Teacher values (102,'张丽','讲师','通信'); insert into Teacher values (103,'刘力伟','助教','计算机'); insert into Teacher(Tno,Tname,Dept) values (104,'李春生','计算机'); insert into Teacher(Tno,Tname,Dept) values (105,'王华英','自动化'); 查询: select * from teacher; select * from teacher where dept='通信'; select distinct dept from teacher; select count(*) from teacher; select count(distinct dept) from teacher; select * from teacher aa,teacher bb where aa.tno=bb.tno; select Distinct Tno from course where 2<=(select count(*)from Course aa where aa.Tno=course.tno); 视图: create view v_t_c as select Teacher.Tno,Tname,Title,Dept,Cno,Cname from Teacher,course where Teacher.Tno=course.Tno; 集合运算: select * from teacher union select * from teacher_copy; 索引: create Table Course( Cno integer not null, Tno integer not null, Cname char(10) not null, credit numeric(3,1) not null); //insert into Course //values(1,101,'数据库',3.5); //insert into Course //values(1,103,'数据库',3.5); //insert into Course //values(2,102,'网络',3); //insert into Course //values(2,101,'网络',3); //insert into Course //values(3,103,'操作系统',3); // //select * from course; // //delete from course; // //create unique index course_ind on course(Tno,Cno); 认识null: create table Teacher( Tno integer Primary Key, Tname char(6) not null, Title char(6), Dept char(10)); insert into Teacher values (901,'李华','讲师','计算机'); insert into Teacher values (902,'张丽','讲师','通信'); insert into Teacher values (903,'刘力伟','助教','计算机'); insert into Teacher values (904,'赵莺',null,'计算机'); insert into Teacher values (905,'张大军',null,null); select * from teacher; Select * from teacher where title is null; select * from teacher where dept is not null; 嵌套: //select sname,dept from student //where sno in(select sno //from sc //where cno=2); select sname,dept from student where sno in(select sno from sc where cno in(select cno from course where cname='网络')); 修改删除: //update student //set dept='通信工程' //where dept='通信'; //select * from student; //delete from student where dept='计算机'; //select * from student; delete from student; select * from student; 外键: create table father_t (Cno integer primary key, Cname char(10) not null, Credit numeric(3,1) ); create table son_t (st_no integer primary key, fk_cno integer, grade integer, foreign key(fk_cno) references father_t(Cno)); 外键数据插入: insert into father_t values (1,'数据库',2); insert into father_t values (2,'网络',3); insert into son_t values (101,2,86); insert into son_t values (102,5,78); 事务: begin transaction select * from teacher; update teacher set title=null where tno=101; select * from teacher; rollback; select * from teacher; 以上脚本均在windows server 2000+sql server 2000下调试成功! |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 03:36 , Processed in 0.116852 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.