SQL语言简单入门

        此篇摘自网络。对SQL方面总结的比较全面,故转录与此,以便日后需要查阅时方便。

   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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值