-SQL基本查询
-
where, between, like, and, or, in, not, order by, as, distinct,max/min
-
sum, avg, count, group by(根据一个或多个列队结果集进行分组)
-
HAVING在SQL中增加HAVING句的主要原因是因为,WHERE关键字无法与合计函数一起使用
-
TOP
-
CASE语句
--student info
CREATETABLE Students(
ID int primary keynot null,
Name nvarchar(50),
Age int,
City nvarchar(50),
MajorID int
)
--majors
CREATETABLE Majors(
ID int,
Name nvarchar(50)
)
--courses
CREATETABLE Courses(
ID int primarykey not null,
Name nvarchar(50)not null
)
--select course
CREATETABLE SC(
StudentIDint notnull,
CourseIDint notnull,
Score int
)
insertinto Students values (1,'林三',18,'guangxi','01');
insertinto Students values(2,'李十四',32,'shanghai','03');
insertinto Students values(3,'李以恒',20,'American','02');
select* from Students;
update Studentsset Name=N'林珊'whereID=1;
update studentsset name=N'李立宙'whereid=2;
update Studentsset Name=N'张以恒',Age=1where id=3;
SELECT ID,NAME,AGEFROM Students where agebetween 1 and 20;
insertinto Majors values (01,'computer');
insertinto Majors values(02,'Art');
insertinto Majors values(01,'medicine');
update Majorsset ID=03 where ID=02;
update Majorsset ID=02 where Name='medicine';
select* from Majors;
select* from Students whereName like N'林%';
select* from students whereAge>=18 ANDNAMENOT LIKE N'李%'
SELECT* FROM Students WHEREAGE>=18 ORNAMELIKE N'林%'
SELECT* FROM Students WHEREAGE IN ( 1,19,32);
SELECT* FROM STUDENTS ORDER BY AGE DESC
SELECT IDAS StudentID, NAMEAS StudentName, AgeFROM Students
SELECTDISTINCT S.ID,S.City,S.Name,M.NameAS MajoreName FROM Students SLEFT JOIN Majors MON S.MajorID=M.ID
SELECTMAX(AGE)FROM Students
SELECTMIN(AGE)FROM Students
SELECTSUM(AGE)FROM Students
SELECTAVG(AGE)FROM Students
创建数据过程中会插入多条重复数据,用主键能避免此问题
ALTERTABLE Majors ALTERCOLUMN ID NVARCHAR(50);
SELECT* FROM Majors
TRUNCATE table Majors
INSERTINTO Majors VALUES('M1','computer');
insertinto majors values('M2','MEDICINE');
INSERTINTO MAJORS VALUES('M3','ART');
ALTERTABLE COURSES ALTERCOLUMN ID NVARCHAR(50);
ALTERTABLE STUDENTS altercolumn majorID nvarchar(50);
SELECT* FROM Courses
SELECT* FROM Majors
SELECT* FROM Students
UPDATE StudentsSETMajorID='M1'WHERE MajorID=1
update StudentssetmajorID='M2'WHERE MajorID='2'
UPDATE STUDENTSSETMajorID='M3'WHERE MajorID='3'
DROPTABLE Courses
CREATETABLE Courses
(
ID nvarchar(50)PRIMARY KEY,
Name nvarchar(50)
)
INSERTINTO Courses VALUES('C1',N'数学');
INSERTINTO Courses VALUES('C2',N'语文');
INSERTINTO Courses VALUES('C3',N'英语');
SELECT* FROM Courses
SELECT* FROM SC
DROPTABLE SC
CREATETABLE SC
(
StudentIDnvarchar(50),
CourseIDnvarchar(50),
Score int
)
SELECT* FROM SC ORDER BY StudentID
INSERTINTO SC VALUES(1,'C1',80),(2,'C2',85),(3,'C3',90);
INSERTINTO SC VALUES(1,'C2',79),(1,'C3',90),(2,'C1',99),(2,'C3',97),(3,'C1',90),(3,'C2',87)
SELECT* FROM Students SFULL JOIN SC ON S.ID=SC.StudentID
SELECTCOUNT(*) FROM Students
SELECTCOUNT(*) FROM Majors
SELECT* FROM Courses
SELECTCOUNT(*) FROM SC
SELECTAVG(AGE)FROM Students
/*查询课程号为C1的平均成绩*/
SELECTAVG(Score)FROM SC WHERECourseID='C1'
/*查询数学的平均成绩*/
SELECTAVG(Score)FROM SC WHERECourseID=(SELECTIDFROM Courses whereCourses.Name=N'数学')
/*查询年龄分布的总数*/
SELECT COUNT(DISTINCT AGE) FROM Students
ALTERTABLE Students Add SEXNVARCHAR(50);
update Studentsset SEX=N'男'whereID IN(2,3)
SELECT* FROM Students
UPDATE STUDENTSSET SEX=N'女'whereID=1;
/*查询男女生数量*/
SELECT SEX,COUNT(ID) NUMBERFROMStudents GROUPBYSEX
/*查询学生的分布数量*/
SELECT City,COUNT(ID)AS NUMBER FROM StudentsGROUP BY City
SELECT* FROM SC
/*查询学生的平均成绩,包括学生的ID,平均成绩*/
SELECT StudentID,AVG(Score) avgscore FROM SC GROUPBY StudentID
SELECT* FROM Students
/*查询平均成绩大于等于90的学生ID及平均成绩*/
/*SELECT StudentID,AVG(Score) FROM SCWHERE AVG(Score)>=90 ERROR:WHERE关键字不能与合计函数一起使用*/
SELECT StudentID,AVG(Score)FROM SC GROUP BY StudentID HAVING AVG(Score)>=90
/*查询平均成绩大于80且ID=3的学生的ID及平均成绩*/
/*还是用HAVING的SQL语句中,可以有普通的WHERE条件*/
SELECT StudentID,AVG(Score)as avgscore FROM SCWHEREStudentID=3 GROUPBY StudentID HAVINGAVG(Score)>80
/*查询总成绩在270分以上的学生的ID*/
SELECT StudentIDFROM SC GROUP BY StudentID HAVING SUM(Score)>270
/*查询年龄最大的两位学生*/
SELECTTOP 2 ID,Name,AgeFROM Students ORDERBY Age DESC
/*如果有年龄相同的如何处理*/
SELECT* FROM Students
INSERTINTO Students VALUES(4,N'刘玉婷',18,'tianjin','M2',N'女')
SELECT ID, NAMEFROM Students WHEREAGEIN (SELECTTOP 2 AGE FROMStudents)
/*查询学生信息,如果SEX为男则显示0,SEX为女则显示1,其它显示为其他*/
SELECTID,(CASE Students.SexWHEN N'女'THEN'0' WHEN N'男'THEN'1')AS SEXNUM FROMStudents
3)统计成年未成年学生的个数
要求结果
成年 | 未成年 |
23 | 6 |
SELECT ID,Name,CASEWHEN Age>=18 THEN N'成年'WHENAge<18 THEN N'未成年'ENDAS IFADULT FROM Students
/*统计成年未成年学生个数*/
SELECT SUM(CASEWHEN Age>=18 THEN 1 ELSE 0 END)AS N'成年',SUM(CASEWHEN Age<18 THEN 1 ELSE 0 END)AS N'未成年'FROMStudents
4)行列转换。统计男女生中未成年、成年的人数
结果如下:
性别 | 未成年 | 成年 |
男 | 3 | 13 |
女 | 2 | 18 |
/*统计男女生中未成年,成年的人数*/
SELECTCASE WHEN Sex=0THEN '男'ELSE'女'END AS '性别',
SUM(CASEWHEN Age<18 THEN 1 ELSE 0 END)AS '未成年',
SUM(CASEWHEN Age>=18 THEN 1 ELSE 0 END)AS '成年'
FROM Students
GROUPBY Sex
-表连接
准备工作
SELECT* FROM Students
/*添加列用ADD COLUMNNAME,修改列类型ALTER COLIMUMOLUMNNAME*/
/*修改列名称*/
ALTERTABLE Students
/*删除列*/
ALTERTABLE Students DROPCOLUMN SEX
/*清空表*/
TRUNCATETABLE Students
/*插入表数据*/
CREATETABLE Teachers(
ID INT PRIMARY KEYNOT NULL,
NAME NVARCHAR(50)
)
INSERTINTO Students VALUES (101,'Tom',20,'BeiJing',10)
INSERTINTO Students(ID,Name,Age,City,MajorID)VALUES(102,'Lucy',18,'ShangHai',11)
INSERTINTO Teachers(ID,Name)VALUES(101,'Mrs Lee')
INSERTINTO Teachers(ID,Name)VALUES(102,'Lucy')
SELECT* FROM Students Sinnerjoin Teachers TONS.ID=T.ID
/*All queries combined using a UNION,INTERSECT or EXCEPT operator must have an equal number of expressions in theirtarget lists.错误的例子*/
SELECT* FROM Students UNION SELECT *FROM Teachers
/*正确的例子*/
SELECT ID,NameFROM Students UNIONSELECT * FROM Teachers
/*UNION只会选择不同的值,如果学生和老师有重名的情况则需要用UNION ALL*/
/*查询教师学生的全部姓名*/
SELECT ID,NAMEFROM Students UNIONALL SELECT * FROM Teachers
SELECT* FROM MAJORS
TRUNCATETABLE MAJORS
INSERTINTO Majors VALUES(10,'ENGLISH')
INSERTINTO MAJORS VALUES (12,'COMPUTER')
/*查询学生信息,包括ID,姓名,专业名称*/
SELECT S.ID,S.Name,M.NameFROM Students S INNERJOIN Majors MONS.MajorID=M.ID
/*左外连结*/
SELECT* FROM Students SLEFT JOIN Majors MON S.MajorID=M.ID
--右连结
SELECT* FROM Students SRIGHTJOIN Majors MONS.MajorID=M.ID
--全连结
SELECT* FROM Students SFULL JOIN Majors MON S.MajorID=M.ID
--交叉连接,也成为笛卡尔积,效率最低
SELECT* FROM Students SCROSSJOIN Majors M
--一次查询多表,得出来的结果跟交叉连接一样,笛卡尔积
SELECT* FROM Students,Majors
--查询实战
IFEXISTS(SELECT ID FROM SYSOBJECTS WHEREname='Students')
DROPTABLEStudents
GO
CREATETABLEStudents(
SID NVARCHAR(20)PRIMARY KEY NOT NULL,
SNAME NVARCHAR(20),
SAGE INT,
SSEX INT
)
IFEXISTS(SELECT ID FROM SYSOBJECTS WHEREname='Teachers')
DROPTABLETeachers
GO
CREATETABLETeachers(
TID NVARCHAR(20) PRIMARYKEY NOT NULL,
TNAME NVARCHAR(20)NOT NULL
)
IFEXISTS(SELECT ID FROM SYSOBJECTS WHEREname='Courses')
DROPTABLECourses
GO
CREATETABLECourses(
CID NVARCHAR(20) PRIMARYKEY NOT NULL,
CNAME NVARCHAR(20)NOT NULL,
TID NVARCHAR(20)
)
IFEXISTS(SELECT ID FROM SYSOBJECTS WHEREname='SC')
DROPTABLESC
GO
CREATETABLESC(
SID NVARCHAR(20) NOT NULL,
CID NVARCHAR(20) NOTNULL,
Score INT
)
INSERTINTO Students(SID,SName,SAge,SSex)VALUES('S001','Tom','20','0')
INSERTINTO Students(SID,SName,SAge,SSex)VALUES('S002','Lucy','21','1')
INSERTINTO Students(SID,SName,SAge,SSex)VALUES('S003','Jim','18','0')
INSERTINTO Students(SID,SName,SAge,SSex)VALUES('S004','Brush','20','0')
INSERTINTO Students(SID,SName,SAge,SSex)VALUES('S005','Kim','22','1')
INSERTINTO Students(SID,SName,SAge,SSex)VALUES('S006','Fka','20','0')
INSERTINTO Students(SID,SName,SAge,SSex)VALUES('S007','Cidy','17','1')
INSERTINTO Students(SID,SName,SAge,SSex)VALUES('S008','YouNi','19','0')
TRUNCATETABLE TEACHERS
INSERTINTO Teachers(TID,TName)VALUES('T001',N'张三')
INSERTINTO Teachers(TID,TName)VALUES('T002',N'李四')
INSERTINTO Teachers(TID,TName)VALUES('T003',N'王五')
TRUNCATETABLE COURSES
INSERTINTO Courses(CID,CName,TID)VALUES('C01',N'英语','T001')
INSERTINTO Courses(CID,CName,TID)VALUES('C02',N'体育','T002')
INSERTINTO Courses(CID,CName,TID)VALUES('C03',N'数学','T003')
TRUNCATETABLE SC
INSERTINTO SC(SID,CID,Score)VALUES('S001','C01',78)
INSERTINTO SC(SID,CID,Score)VALUES('S001','C02',60)
INSERTINTO SC(SID,CID,Score)VALUES('S001','C03',97)
INSERTINTO SC(SID,CID,Score)VALUES('S002','C01',56)
INSERTINTO SC(SID,CID,Score)VALUES('S003','C01',55)
INSERTINTO SC(SID,CID,Score)VALUES('S004','C01',55)
INSERTINTO SC(SID,CID,Score)VALUES('S002','C02',90)
INSERTINTO SC(SID,CID,Score)VALUES('S002','C03',40)
INSERTINTO SC(SID,CID,Score)VALUES('S003','C02',40)
SELECT* FROM Students
SELECT* FROM Teachers
SELECT* FROM SC
SELECT* FROM Courses
--查询c01课程比C02课程成绩高的所有学生的学号
--分别得到C01成绩单和C02课程的成绩单,然后再得到C01课程比C02课程高的学生学号
SELECT C1.SIDFROM
(SELECT Score,SIDFROM SC WHERE SC.CID='C01')AS C1
INNERJOIN
(SELECT Score,SIDFROM SC WHERE SC.CID='C02')AS C2 ON C1.SID=C2.SID
WHERE C1.Score<C2.Score
--查询平均成绩大于60分的同学的学号和平均成绩
--平均成绩
SELECTAVG(Score)FROM SC WHERE SID='S001'
SELECTAVG(Score)FROM SC WHERE SID='S002'
SELECTSID,AVG(Score)FROM SC GROUP BY SID HAVINGAVG(Score)>60
--查询所有同学的学号(Students),姓名,选课数,总成绩(SC)
SELECT S.SID,S.SNAME,A.ScCount,A.ScoreSumFROM
(SELECT SC.SID,COUNT(SC.CID)AS ScCount,SUM(SC.Score)AS ScoreSum FROM SCGROUP BY SC.SID)AS A LEFT JOIN
(SELECT* FROM Students)AS S ON A.SID=S.SID
--查询姓‘李’的老师的个数,不能重复
SELECTCOUNT(DISTINCT(TID))FROM Teachers WHERETNAMELIKE N'李%'
--查询没学过‘张三’老师的课程的同学的学号姓名
SELECT S.SID,S.SNAMEFROM StudentsS
--张三老师的课程
SELECT C.CIDFROM Teachers T INNERJOIN Courses CONT.TID=C.TIDWHERE T.TNAME=N'张三'
--学过张三老师课程的学生学号
SELECTSID FROM SC WHERE SC.CID =(SELECT C.CIDFROM Teachers T INNERJOIN Courses CONT.TID=C.TIDWHERE T.TNAME=N'张三')
--不学过张三老师的课程的学生学号要从Students表里查找
SELECT S.SID,S.SNAMEFROM StudentsS LEFTJOIN SC ON S.SID=SC.SID
WHERE S.SIDNOT IN
(SELECTSIDFROM SC WHERESC.CID =(SELECT C.CIDFROM Teachers T INNERJOIN Courses CONT.TID=C.TIDWHERE T.TNAME=N'张三'))
--查询两门以上不及格课程的同学的学号及其平均成绩
SELECT* FROM SC
--查询课程不及格的同学的学号 CASE WHEN Score<60 THEN 1 ELSE 0 END
SELECT S.SNAME,S.SIDFROM Students S WHERE S.SIDIN
(SELECT SC.SID FROM SC GROUP BYSID HAVING SUM(CASE WHEN Score<60 THEN 1ELSE 0 END)>=2)
--查询全部学生都选修的课程的课程号和课程名
SELECTCOUNT(*) FROMStudents /*8*/
SELECT SC.CIDFROM SC GROUP BY CID HAVING COUNT(CID)=4
SELECT C.CID,C.CNAMEFROM Courses C WHEREC.CIDIN
(SELECT SC.CIDFROM SC GROUP BY CID HAVINGCOUNT(CID)=(SELECTCOUNT(*) FROM Students))
--统计每门课程的学生选修人数(超过3人才统计)
/*先选出超过两人的选修课是什么*/
SELECT CIDFROM SC GROUP BY CID HAVING COUNT(CID)>=3
SELECTCOUNT(SC.CID)AS Number,SC.CIDFROM SC GROUP BY CID HAVING SC.CIDIN (SELECT CID FROM SCGROUP BY CID HAVING COUNT(CID)>=3)
--查询每门课程成绩最好的前三名,要求输出课程ID,前三名学号以及成绩,并且按照课程号升序排序,同课程的成绩倒叙排序
--先查询一门课程的前三名
SELECTTOP 3 SID,CID,SC.ScoreFROM SC WHERE CID='C01'
SELECT CID,SID,ScoreFROM SC AS A WHERE A.SIDIN (SELECTTOP 3 SID FROM SC WHERE CID=A.CIDORDER BY Score DESC) ORDERBY CID,SCORE DESC
--查询选修张三老师的课程的学生中,成绩最高的学生姓名以及其成绩
--先查询张三老师的课的课程号
SELECT CIDFROMCourses WHERE TID=(SELECT TIDFROMTeachers WHERE TNAME=N'张三')
--查询CID为C01的课,成绩最高的学生姓名以及成绩
--查询成绩最高的学生的学号,姓名,成绩
SELECTTOP 1 SC.SID,CID,SC.ScoreFROM SC WHERE SC.CID='C01'
SELECT A.SID,S.SNAME,A.ScoreFROM (SELECTTOP 1 SC.SID,CID,SC.ScoreFROM SC WHERE SC.CID='C01')AS A INNER JOIN Students S ON S.SID=A.SID
--查询每门课程的平均成绩,结果按照平均成绩降序排列,平均成绩相同时按照课程号升序排序
SELECT SC.CID,AVG(SC.Score)AS AVGSCORE FROM SCGROUP BY CID ORDER BY AVGSCORE DESC
--查询学生总成绩以及名次(如果学生未选课怎么办)
SELECT SC.SID,SUM(SC.Score)AS SUMSCORE FROM SCGROUP BY SC.SID
--统计各科成绩,各分数段人数,结果包括:课程ID,课程名称
--查询各科的及格率
--求各科成绩的最高分和最低分
SELECT CID,MAX(SC.Score)AS MAXSCORE,MIN(SC.Score)AS MINSCORE FROM SCGROUP BY CID
--查询出生1990年前的学生名单
--查询选课少于两门课程的学生名单
SELECT SC.SIDFROM SC GROUP BY SC.SID HAVING COUNT(CID)<2
--查询英语成绩第三名的学生的成绩单
--英语课的课程号c01
--第三名的学生的学号
SELECTTOP 1 A.SIDFROM (SELECTTOP 3 Score,SC.SIDFROM SC WHERE CID='C01'ORDER BY Score ASC) AS A
--成绩单
SELECT SC.CID,SC.ScoreFROM SC
WHERE SC.SID=
(SELECTTOP 1 A.SIDFROM (SELECTTOP 3 Score,SC.SIDFROM SC WHERE CID=(SELECT CIDFROMCourses C WHERE C.CNAME=N'英语')ORDERBY Score ASC) AS A)