复习之前课本上的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';
