你的位置:首页 > ASP.net教程

[ASP.net教程]常用的 SQL语句


复习之前课本上的sql语句,以前上课的时候都是老师在上面讲,我们在下面玩,根本没有把这个放在心上,等到考试的时候临时学习突击下,就可以顺利过60了,但是现在不行了,自己要重新的学习sql,应该把里面最基本的语句和语法都全部的学习下,每个案例都要在数据库中执行下,我们需要这些基本的知识点。

1:创建表,修改表,添加列

/*创建基本表Student*/create table Student(      Sno char(5) not null unique,    /*定义的学号不能为空,并且是唯一的值。*/		  Sname char(20),		  Ssex char(2),		  Sage int,		  Sdept Char(15));/*修改基本表Student*/   /*添加新列Scome*/   alter table Student Add Scome DATE;   /*修改数据类型*/	 alter table Student MODIFY Sage SMALLINT;	 /*删除学号的唯一标识*/	 alter table Student DROP UNIQUE(Son);/*删除表*/drop table Student;/*创建索引*/  CREATE CLUSTER INDEX Stusname ON Student(Sname);  /*删除索引*/  drop index Stusname;

2:基本的查询

/*查询*/     select Sname,Sage from Student;	 select Sname,Sage,Sdept from Student;	 select * from Student;	 select Sname,1996-Sage from Student;	 select Sname,'Year of Birth:',1996-Sage,LOWER(Sdept) from Student;	 select Sname NAME,'Year of Birth:'BIRTH,1996-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM Student;  /*为列创建别名*/

3:复杂的查询

	  /*消除重复行*/	   select DISTINCT Sno from Student;	   /*查询满足条件的元组*/	   select Sname from Student where Sdept="CD";		 select Sname,Sage from Student where Sage>=20;		 alter table Student add Grade int;		 select DISTINCT Sno from Student where Grade<60;		 /*确定范围-----------between and */		 select Sname,Sage,Sdept from Student where Sage between 20 and 23;		 /*确定集合-----------IN('','','')*/		 select Sname,Ssex from Student where Sdept IN('IS','MA','CS');		 /*字符匹配-----------LIKE+++( %:代表任意长度)( _ :代表单个字符)*/		 select Sname,Sage from Student where Sname LIKE 'A%';		 SELECT Sname ,Sage from Student where Sname LIKE 'A_';		 /*字符匹配-------里面的转义字符-----ESCAPE'\':表示\为转义字符*/		/*查询条件是“A_”此时这里的_不是代表一个字符,只是单纯的表示下划线而已。因为语句前面有转义字符。*/		 select Sname from Student where Sage LIKE 'A\__' ESCAPE '\';		 /*涉及空值的查询*/		 select Sname,Sage from Student where Grade IS NULL; /*查询成绩为空的学生*/

4:更复杂查询

/*多重条件查询*/  select Sname from Student where Sdept='cd' and Sage>20;  select Sname from Student where Sdept='cd' or Sage>20;/*对查询结果进行排序*/  select Sname Grade from Student where Sage>20 order by Sage DESC;  select * from Student order by Sage DESC;/*使用集函数*/  select count(*) from Student;         /*求总个数*/  select count(distinct Sno) from Student;  select avg(Sage) from Student where Sname='ahui';   /*avg:求平均值*/  select Sname,count(Sage) from Student group by Sname;  select MAX(Sage) from Student where Sno='1';  /*最大值*/

5:连接查询

/*连接查询*/   /*等值的查询*/   select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;	 /*自然连接两个表*/	 select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,SC.Grade from Student,SC where Student.Sno=SC.Sno;	 /*将表Student进行了重新命名,为两个名字,从而进行对自己的查询*/	 select FIRS.Sage,SECO.Sno from Student FIRS,Student SECO where FIRS.Sno=SECO.Sage; 	/*外连接*/	select Student.Sno,Sname,Sage,Sdept,Ssex,Cno,SC.Grade from Student,SC where Student.Sno=SC.Sno(*);	/*复合条件连接--------就是利用and来进行操作*/	 select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and SC.Cno='2' and Student.Sage=2;

6: 嵌套查询

      这个查询是将最里面的查询结果当作外面查询的条件来进行查询的,执行顺序是从里到外执行的。注意:最里面的查询(子查询的select语句中不能使用ORDER BY子句,ORDER BY子句只能对最终的查询结果进行排序)

  /*嵌套查询---就是把一个查询的结果当做另一个查询的条件来进行查询*/   select Sname from Student where Sno 	    IN(			  select Sno from SC where Cno='2'			);			/*--01:带有IN的子查询*/	 select Sno,Sname,Sdept from Student where Sdept IN( select Sdept from Student where Sname='ahui');	    /*--02:带有比较运算符的子查询*/	 select Sno,Sname,Sdept from Student where Sdept=( select Sdept from Student where Sname='ahui');

7:集合查询

      每一个select语句都能获得一个或一组元组,若要把多个select语句的结果合并为一个结果,可用集合操作来完成。集合操作主要包括并操作(UNION)交操作(INTERSECT),差操作(MINUS).    

      使用UNION将多个查询结果合并起来,形成一个完整的查询结果时,系统会自动去掉重复的元组;

注意的是参加UNION操作的各数据项数目必须相同,对应项的数据类型也要必须相同。

/*集合查询*/    /*查询计算机系的学生及年龄不大于19学生----是个并集*/   select * from Student where Sdept='CS' UNION select * from Student where Sage<=19;	 select Sno from SC where Cno='1' UNION select Sno from SC where Cno='2';

8:数据更新

 /*插入数据*/ insert into Student values('05','aaa','男','25','3','2015-11-26','3'); insert into SC(Sno,Cno) values('06','3'); insert into Deptage(Sdept,Avgage) select Sdept,AVG(Sage) from Student GROUP BY Sdept; /*修改数据*/   update Student set Sage=233 where Sno='01';  update Student Set Sage=Sage+1;  update SC set Grade=1 where '1'=( select Sdept from Student where Student.Sno=SC.Sno);/*删除数据*/  delete from Student where Sno='01';