SQL语言简单入门

王朝mssql·作者佚名  2006-11-24
窄屏简体版  字體: |||超大  

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下调试成功!

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有  導航