SQL-结构化查询语言

数据库语言分类：

根据功能不同，可以将数据库系统提供的语言分成三种类型：1）数据定义语言（DDL），用于定义数据库模式；2）数据操纵语言（DML），用于对数据库进行查询和更新；3）数据控制语言（DCL），用于对数据进行权限管理。

数据库模式：

根据数据的不同抽象层次，数据库有三级模式：物理模式（内模式）在物理层描述数据库中全体存储结构和存取方法，而逻辑模式（概念模式）则在逻辑层描述数据库中全体数据的逻辑结构和特征。在视图层也可分为若干模式，称为子模式（外模式），它描述了数据库用户能够看见和使用的局部数据的逻辑结构和特征。通常一个数据库只有一个物理模式和一个逻辑模式，但是子模式有若干个。

事务：

事务是一系列的数据库操作，是数据库应用程序的基本单元，是反映现实世界需要以完整单位提交的一项工作。事务是用户定义的一个数据库操作序列。

事务的四个特征：原子性、一致性、隔离性和持久性。

触发器：

一个触发器用来定义一个条件以及在该条件为真时需要执行的动作。通常，触发器的条件以断言的形式定义。动作以过程的形式定义。

SQL执行优先级

( 8 ) SELECT ( 9 ) DISTINCT ( 11 ) < Top Num > < select list >

( 1 ) FROM [ left_table ]

( 3 ) < join_type > JOIN < right_table >

( 2 ) ON < join_condition >

( 4 ) WHERE < where_condition >

( 5 ) GROUP BY < group_by_list >

( 6 ) WITH < CUBE | RollUP >

( 7 ) HAVING < having_condition >

( 10 ) ORDER BY < order_by_list >

------------------------------------SQL练习解答
---------------------------

CREATE TABLE STUDENT(

Sno VARCHAR(255) NOT NULL,

Sname VARCHAR(255),

Ssex VARCHAR(255),

Sage int,

Sdept VARCHAR(255)

)

SELECT * FROM STUDENT

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES ('9512101','李勇','男',19,'计算机系'),

('9512102','刘晨','男',20,'计算机系'),

('9512103','王敏','女',20,'计算机系'),

('9521101','张力','男',22,'信息系'),

('9521102','吴宾','女',21,'信息系'),

('9521103','张海','男',20,'信息系'),

('9531101','钱小力','女',18,'数学系'),

('9531102','王大力','男',19,'数学系')

CREATE TABLE COURSE(

CNO VARCHAR(255) NOT NULL PRIMARY KEY,

CNAME VARCHAR(255),

Hours INT

)

INSERT INTO COURSE (CNO,CNAME,Hours) VALUES ('C01','计算机文化学',70),

('C02','VB',90),

('C03','计算机网络',80),

('C04','数据库基础',108),

('C05','高等数学',180),

('C06','数据结构',72)

select * from course

CREATE TABLE SC(

SNO VARCHAR(255),

CNO VARCHAR(255),

GRADE INT

)

INSERT INTO SC(SNO,CNO,GRADE) VALUES ('9512101','C01',90),

('9512101','C02',86),

('9512101','C06',NULL),

('9512102','C02',78),

('9512102','C04',66),

('9521102','C01',82),

('9521102','C02',75),

('9521102','C04',92),

('9521102','C05',50),

('9521103','C02',68),

('9521103','C06',NULL),

('9531101','C01',80),

('9531101','C05',95),

('9531102','C05',85)

select * from student

SELECT SNO,CNO,GRADE FROM SC WHERE GRADE BETWEEN 70 AND 80 查询成绩在70到80之间的学生号、课程号和成绩

SELECT MAX(GRADE) FROM (SELECT * FROM SC WHERE CNO='C01') 查询C01课程中成绩最高的分数

with zuida as (select row_number() over(order by grade desc) as rowno,sc.* from sc) select * from zuida where rowno=1 当GRADE中没有null时可得出最大值

SELECT DISTINCT CNO FROM SC 查询学生都选修了哪些课程要求列出课程名、课程号

SELECT COURSE.CNAME,SC.CNO FROM COURSE INNER JOIN SC ON COURSE.CNO=SC.CNO 查询学生都选修了哪些课程要求列出课程名和课程号有重复！

SELECT COURSE.CNAME,SC.CNO FROM COURSE,SC WHERE COURSE.CNO=SC.CNO

select cname,cno from course where cno in (select distinct cno from sc) 人家的方法思路！

select AVG(GRADE),MAX(GRADE),MIN(GRADE) from sc where CNO='C02' 统计C02课程学生平均、最大、最小分数

select SDEPT,COUNT(*) from student where SDEPT IN (select DISTINCT(SDEPT) FROM STUDENT) GROUP BY SDEPT 统计每个系的人数

先where 再group by

SELECT COURSE.CNAME,A.* FROM (select cno,count(distinct sno),max(grade) from sc group by cno) AS A INNER JOIN COURSE ON A.CNO=COURSE.CNO 统计每门课程的修课人数和考试最高分

SELECT CNAME,COUNT(*),MAX(GRADE) FROM SC,COURSE WHERE SC.CNO IN (SELECT DISTINCT CNO FROM SC) AND COURSE.CNO=SC.CNO GROUP BY COURSE.CNAME 人家的思路！

SELECT STUDENT.SNAME,COUNT(CNO) NUMC FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO GROUP BY STUDENT.SNAME ORDER BY NUMC ASC 统计每个学生选课门数并按照门数递增排序

SELECT STUDENT.SNAME,STUDENT.SNO,COUNT(SC.CNO) FROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNO GROUP BY STUDENT.SNAME,STUDENT.SNO ORDER BY COUNT(SC.CNO) ASC 其他思路！

SELECT COUNT(DISTINCT SNO),AVG(GRADE) FROM SC 统计选修课的学生总数和考试的平均成绩 WITH AS 必须加括号！

WITH A AS (SELECT SNO,COUNT(SC.CNO) NUMC,AVG(GRADE) AVG_GRADE FROM SC GROUP BY SNO) SELECT STUDENT.SNAME,A.* FROM A INNER JOIN STUDENT ON A.SNO=STUDENT.SNO WHERE NUMC>1 查询选课门数超过2门的学生平均成绩和选课门数

SELECT STUDENT.SNAME,AVG(SC.GRADE),COUNT(SC.CNO) FROM SC INNER JOIN STUDENT ON SC.SNO=STUDENT.SNO INNER JOIN COURSE ON SC.CNO=COURSE.CNO GROUP BY STUDENT.SNAME HAVING COUNT(DISTINCT COURSE.CNO)>1 别人的思路！

select sno,sum(grade) from sc group by sno having sum(grade)>200 查询总成绩超过200分的学生

with A as (select SNO from sc where CNO='C02') select student.SNAME,student.SDEPT from student,A where student.SNO=A.SNO 查询选修了C02号课程的学生姓名和所在系

SELECT SNAME,SDEPT,SC.CNO FROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNO WHERE SC.CNO='C02'

SELECT STUDENT.SNAME,SC.CNO,SC.GRADE FROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNO WHERE SC.GRADE>80 ORDER BY SC.GRADE DESC 查询成绩80分以上的学生姓名、课程号、成绩，并按成绩降序排列结果

查询计算机系修了数据库基础的学生姓名、性别、成绩

WITH A AS (SELECT SC.SNO,SC.GRADE FROM SC INNER JOIN (SELECT CNO CNO2 FROM COURSE WHERE COURSE.CNAME='数据库基础') ON SC.CNO=CNO2) SELECT STUDENT.SNAME,STUDENT.SSEX,A.GRADE FROM STUDENT INNER JOIN A ON STUDENT.SNO=A.SNO WHERE STUDENT.SDEPT='计算机系'

查询哪些学生的年龄相同，要求列出年龄相同的学生姓名和年龄答案很巧妙啊！

SELECT A.SNAME,A.SAGE FROM STUDENT A WHERE A.SAGE IN

(SELECT A.SAGE FROM STUDENT A,STUDENT B WHERE A.SAGE=B.SAGE AND A.SNAME!=B.SNAME) ORDER BY A.SAGE

SELECT A.CNO,A.CNAME FROM COURSE A WHERE A.CNO NOT IN (SELECT DISTINCT SC.CNO FROM SC) 查询哪些课程没有人选，列出课程号和课程名

查询有考试成绩的所有学生姓名，课程名，成绩精妙！

SELECT STUDENT.SNAME,COURSE.CNAME,SC.GRADE FROM STUDENT,COURSE,SC WHERE SC.GRADE IS NOT NULL AND STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO

分别查询计算机系和信息系的学生姓名，性别，修课名称，修课成绩

SELECT '计算机系',STUDENT.SNAME,STUDENT.SSEX,COURSE.CNAME,SC.GRADE FROM STUDENT,COURSE,SC WHERE STUDENT.SDEPT='计算机系' AND STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO

UNION SELECT '信息系',STUDENT.SNAME,STUDENT.SSEX,COURSE.CNAME,SC.GRADE FROM STUDENT,COURSE,SC WHERE STUDENT.SDEPT='信息系' AND STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO

第二种方法： SELECT '计算机系',STUDENT.SNAME,STUDENT.SSEX,COURSE.CNAME,SC.GRADE FROM SC INNER JOIN STUDENT ON SC.SNO=STUDENT.SNO INNER JOIN COURSE ON SC.CNO=COURSE.CNO WHERE STUDENT.SDEPT='计算机系'

查询选修了c01课程的学生姓名和所在系

SELECT A.SNAME,A.SDEPT FROM STUDENT AS A WHERE A.SNO IN (SELECT SNO FROM SC WHERE CNO='C01')

查询数学系成绩80分以上的学生的学号姓名!! 两个条件同时满足

SELECT STUDENT.SNO,STUDENT.SNAME FROM STUDENT WHERE STUDENT.SNO IN (SELECT SNO FROM STUDENT WHERE SDEPT='数学系') AND STUDENT.SNO IN (SELECT SNO FROM SC WHERE SC.GRADE>80)

查询计算机系学生所选课程名

with A as (select distinct cno from sc where sno in (SELECT STUDENT.SNO FROM STUDENT WHERE STUDENT.SDEPT='计算机系')) select course.CNAME from course,A where course.CNO=A.cno

查询计算机系的成绩80分以上的修课情况

select A.sno,student.SNAME,A.cno,course.CNAME,A.grade from sc as A,student,course where A.SNO in (select distinct sno from student where sdept='计算机系') and A.GRADE>80 and A.SNO=student.SNO and A.CNO=course.CNO

DELETE FROM SC WHERE GRADE<50 OR GRADE IS NULL 删除修课成绩小于50的学生记录

UPDATE SC SET GRADE=GRADE+10 WHERE CNO='C01' 修了c01课程的加10分

---------------------

作者：令狐公子

来源：CSDN

原文：
https://blog.csdn.net/qq_14959801/article/details/78258683