SQL 语句总结

use school goCREATE TABLE xsb(xh  INT PRIMARY KEY IDENTITY(1,1),xm VARCHAR(4) NOT NULL)use schoolgoCREATE TABLE cjb(xh INT NOT NULL ,kcdm char(6) NOT NULL,cj NUMERIC(3,0),bz VARCHAR(30))ALTER TABLE cjb   ADD CONSTRAINT PK_CJB  PRIMARY KEY(xh,kcdm)   ---两个主键一起合成一个主键 --ALTER TABLE cjbADD CONSTRAINT FK_CJBXHFOREIGN KEY(xh) REFERENCES xsb(xh)--ALTER TABLE cjbADD CONSTRAINT FK_CJBKCDMFOREIGN KEY(kcdm) REFERENCES kcb(kcdm)--use schoolgoCREATE TABLE kcb(kcdm CHAR(6) PRIMARY KEY,kcmc varchar(50) NOT NULL,jsid char(4))use schoolgoALTER TABLE  kcb ADD CONSTRAINT FK_JCBJSID FOREIGN KEY(jsid) REFERENCES jsb(jsid)use schoolgoCREATE TABLE jsb(jsid CHAR(4) PRIMARY KEY,jsxm VARCHAR(40) NOT NULL,bz VARCHAR(40))use schoolgoINSERT INTO jsb(jsid,jsxm,bz) VALUES('0101','荣玉凤',null)INSERT INTO jsb(jsid,jsxm,bz) VALUES('0102','李老师',null)INSERT INTO jsb(jsid,jsxm,bz) VALUES('0103','张老师',null)INSERT INTO jsb(jsid,jsxm,bz) VALUES('0104','刘老师',null)INSERT INTO jsb(jsid,jsxm,bz) VALUES('0105','赵老师',null)SELECT * FROM jsb----课程表INSERT INTO kcb(kcdm,kcmc,jsid)VALUES('100101','大学英语','0101')INSERT INTO kcb(kcdm,kcmc,jsid)VALUES('100102','高等数学','0102')INSERT INTO kcb(kcdm,kcmc,jsid)VALUES('100103','SQL Server','0103')INSERT INTO kcb(kcdm,kcmc,jsid)VALUES('100104','JAVAScript','0104')INSERT INTO kcb(kcdm,kcmc,jsid)VALUES('100105','C#','0104')---select * from kcbuse schoolgoselect * from xsb use schoolgoINSERT xsb(xm)VALUES('小王'),INSERT xsb(xm)VALUES('小张')INSERT xsb(xm)VALUES('小赵')INSERT xsb(xm)VALUES('小名')INSERT xsb(xm)VALUES('小美')----成绩表INSERT INTO cjb(xh,kcdm,cj,bz)VALUES(3,'100101',70,null)INSERT INTO cjb(xh,kcdm,cj,bz)VALUES(4,'100101',60,null)INSERT INTO cjb(xh,kcdm,cj,bz)VALUES(4,'100102',80,null)INSERT INTO cjb(xh,kcdm,cj,bz)VALUES(5,'100104',65,null)INSERT INTO cjb(xh,kcdm,cj,bz)VALUES(6,'100105',76,null)INSERT INTO cjb(xh,kcdm,cj,bz)VALUES(7,'100102',60,null)--select *from cjb

use schoolgoselect xsb.xh as '学号',xsb.xm as '姓名',kcb.kcmc as '课程名称',jsb.jsxm as '教师',cj as '成绩' from cjb,kcb,xsb,jsbwhere xsb.xh=cjb.xh AND cjb.kcdm=kcb.kcdm   AND jsb.jsid = kcb.jsidgo

--增加学分 字段use schoolgoalter table kcbadd xf numeric(2,0)--设置课程的学分update kcb  set xf=3 where kcdm='100101'update kcb  set xf=3 where kcdm='100102'update kcb  set xf=4 where kcdm='100103'update kcb  set xf=4 where kcdm='100104'update kcb  set xf=5 where kcdm='100105'-----求学生 1与 2 的年龄差declare @x intdeclare @y intselect @x=year(GETDATE())-year(csrq) from  xsb where xh=16230033select @y=year(GETDATE())-year(csrq) from xsb where xh=16230034SELECT @x-@y as 王生金币沈宏斌大---求全体学生的平均年龄SELECT AVG(year(GETDATE())-year(csrq)) as 平均年龄 from xsb--学生年龄前三名SELECT TOP 3 * from xsb ORDER BY xsb.csrq ASC--用distinct控制查询返回的记录不重复（过滤重复的记录,有重复的只取一个）select * from xsb---查询xsb表中有哪些姓氏SELECT DISTINCT LEFT(xm,1) as 姓氏 from xsb --计算xsb中男，女生 各有多少人(分组查询)SELECT xb as 性别,count(xb) as 人数 from xsb GROUP BY xb--计算cjb中各科课程的平均成绩use schoolgoSELECT  cjb.kcdm,ceiling(AVG(cjb.cj)) as 平均成绩  FROM cjb GROUP BY cjb.kcdmgo--select * from cjb ORDER BY kcdmselect distinct kcdm  from  cjb---计算每个学生的的平均成绩SELECT xh as 学号 ,ceiling(AVG(cj)) as 平均成绩 from cjb  GROUP BY xh --计算每个学生的总成绩SELECT xh as 学号 ,ceiling(SUM(cj)) as 总成绩 from cjb  GROUP BY xh  --计算学生 学号3的全部课程的平均成绩SELECT xh as 学号 ,ceiling(AVG(cj)) as 平均成绩 from cjb where xh=3 GROUP BY xh ---分组前用where 分组后 用havingSELECT xh as 学号 ,ceiling(AVG(cj)) as 平均成绩 from cjb  GROUP BY xh having xh=3 --计算小美的全部课程的平均成绩SELECT  xm as 姓名,AVG(cj) as 平均成绩 from cjb,xsb where  cjb.xh=xsb.xh AND xsb.xm='小美' GROUP BY xsb.xm,cjb.cj  --第二种写法(利用子查询)SELECT xh  as 学号,CEILING(AVG(cj)) as 平均成绩  from cjb WHERE xh=(SELECT xh FROM xsb where xm='小美') group by xh--给出学生学号，学生姓名，课程代码及成绩报表  （两张表里面有交叉的数据才显示（内连接））use schoolgoSELECT cjb.xh as 学号 ,xm as 姓名 ,cjb.kcdm as 课程代码 ,cj as 成绩 from cjb INNER JOIN xsb ON (cjb.xh=xsb.xh) go ---外连接 ：1.左外连接2.右外连接 use school goSELECT cjb.xh as 学号,xm as 姓名,cjb.kcdm as 课程代码 ,cj as 成绩 from xsb  LEFT OUTER JOIN cjb ON cjb.xh=xsb.xh go