Sql复杂查询

妖狐艹你老母 2022-08-09 09:10 328阅读 0赞

`use 学生数据库;`

`drop database 学生数据库;`

`create database 学生数据库`

`ON`

`(`

`NAME = 学生数据库_数据文件,`

`FILENAME=` `'E:\sql sever 2000\学生数据库_数据文件.MDF'` `,`

`SIZE=10,`

`MAXSIZE =50,`

`FILEGROWTH=5`

`)`

`LOG ON`

`(NAME = 学生数据库_日志文件,`

`FILENAME=` `'E:\sql sever 2000\学生数据库_日志文件.LDF'` `,`

`SIZE=5MB,`

`MAXSIZE=25MB,`

`FILEGROWTH=5MB`

`)`

`GO`

`CREATE TABLE 学生表`

`(`

`SNO CHAR(4),`

`SN CHAR(8),`

`SEX CHAR(2) NOT NULL,`

`AGE INT NOT NULL,`

`DEPT INT NOT NULL,`

`PRIMARY KEY(SNO),`

`CHECK (AGE BETWEEN 14 AND 45),`

`CHECK (SEX=` `'男'` `OR SEX=` `'女'` `)`

`)`

`CREATE TABLE 课程表`

`(`

`CNO CHAR(4),`

`CN CHAR(8) NOT NULL,`

`PRIMARY KEY(CNO)`

`)`

`CREATE TABLE 学生选课表`

`(`

`SNO CHAR(4),`

`CNO CHAR(4) NOT NULL,`

`GRADE INT NULL,`

`PRIMARY KEY(SNO,CNO),`

`FOREIGN KEY(SNO) REFERENCES 学生表(SNO),`

`FOREIGN KEY(CNO) REFERENCES 课程表(CNO)`

`)`

`insert` `into`  `学生表 values(` `'S1'` `,` `'徐琳'` `,` `'女'` `, 17,2);`

`insert` `into`  `学生表 values(` `'S2'` `,` `'李国华'` `,` `'男'` `, 18,6);`

`insert` `into`  `学生表 values(` `'S3'` `,` `'徐萍'` `,` `'女'` `, 20, 1);`

`insert` `into`  `学生表 values(` `'S4'` `,` `'林新明'` `,` `'男'` `, 23, 6);`

`insert` `into`  `学生表 values(` `'S5'` `,` `'张家杰'` `,` `'男'` `, 19, 6) ;`

`insert` `into`  `学生表 values(` `'S6'` `,` `'张婷'` `,` `'女'` `, 21, 3);`

`insert` `into`  `学生表 values(` `'S7'` `,` `'赵大地'` `,` `'男'` `, 18, 6) ;`

`insert` `into`  `学生表 values(` `'S8'` `,` `'赵树林'` `,` `'男'` `, 19, 3);`

`insert` `into`  `课程表 values(` `'C1'` `,` `'数学'` `) ;`

`insert` `into`  `课程表 values(` `'C2'` `,` `'英语'` `);`

`insert` `into`  `课程表 values(` `'C3'` `,` `'C语言'` `) ;`

`insert` `into`  `课程表 values(` `'C4'` `,` `'数据库'` `);`

`insert` `into`  `课程表 values(` `'C5'` `,` `'政治'` `) ;`

`insert` `into`  `课程表 values(` `'C6'` `,` `'物理'` `);`

`insert` `into`  `课程表 values(` `'C7'` `,` `'心理学'` `);`

`insert` `into`  `学生选课表 values(` `'S1'` `,` `'C1'` `,80);`

`insert` `into`  `学生选课表 values(` `'S1'` `,` `'C2'` `,85);`

`insert` `into`  `学生选课表 values(` `'S1'` `,` `'C6'` `,75);`

`insert` `into`  `学生选课表 values(` `'S1'` `,` `'C4'` `,56);`

`insert` `into`  `学生选课表 values(` `'S1'` `,` `'C5'` `,90);`

`insert` `into`  `学生选课表 values(` `'S2'` `,` `'C1'` `,47);`

`insert` `into`  `学生选课表 values(` `'S2'` `,` `'C3'` `,80);`

`insert` `into`  `学生选课表 values(` `'S2'` `,` `'C4'` `,75);`

`insert` `into`  `学生选课表 values(` `'S2'` `,` `'C5'` `,70);`

`insert` `into`  `学生选课表 values(` `'S6'` `,` `'C1'` `,95);`

`insert` `into`  `学生选课表 values(` `'S6'` `,` `'C2'` `,80);`

`insert` `into`  `学生选课表 values(` `'S6'` `,` `'C3'` `,87);`

`insert` `into`  `学生选课表 values(` `'S3'` `,` `'C1'` `,75);`

`insert` `into`  `学生选课表 values(` `'S3'` `,` `'C2'` `,70);`

`insert` `into`  `学生选课表 values(` `'S3'` `,` `'C3'` `,85);`

`insert` `into`  `学生选课表 values(` `'S3'` `,` `'C4'` `,86);`

`insert` `into`  `学生选课表 values(` `'S3'` `,` `'C5'` `,90);`

`insert` `into`  `学生选课表 values(` `'S3'` `,` `'C6'` `,99);`

`insert` `into`  `学生选课表 values(` `'S4'` `,` `'C1'` `,83);`

`insert` `into`  `学生选课表 values(` `'S4'` `,` `'C2'` `,85);`

`insert` `into`  `学生选课表 values(` `'S4'` `,` `'C3'` `,83);`

`insert` `into`  `学生选课表 values(` `'S5'` `,` `'C2'` `,99);`

`SELECT * FROM 课程表; --*表示表中的所有列`

`SELECT * FROM 课程表;`

`SELECT * FROM 学生选课表;`

`SELECT SNO FROM 学生选课表;`

`SELECT DISTINCT SNO FROM 学生选课表;  --DISTINCT 在结果中消除重复行`

`--WHERE 子句限定查询条件`

`--WHERE 子句用来限定查询条件，可用条件表达式，也可以用着键字来。`

`/*`

`条件表达式有两种：`

`1、算术表达式： >,<,=,<>,>=,<=,!>,!<,!=`

`2、逻辑表达式：NOT,AND ,OR`

`关键字是：BETWEEN, IN ,NOT IN`

`*/`

`USE 学生数据库;`

`--从学生中，检索出分数大于85，小于95之间的学生号及课程号，并且汉字显示属性`

`SELECT  SNO 学生号,SNO 课程号 FROM 学生选课表  WHERE GRADE>85 AND GRADE <95;`

`--从学生中，检索出学号分别的S2,S4,S6的学生姓名，年龄，并且汉字显示属性`

`SELECT SN 学生姓名, AGE 年龄 FROM 学生表 WHERE SNO IN(` `'S2'` `,` `'S4'` `,` `'S6'` `);`

`--多表查询`

`--1、把多张表的名字写在FROM 后面，然后用WHERE子句限定表之间的连接条件`

`--2、用JOIN和ON两个关键字，JOIN 用于连接两张表,ON 用于给出两张表相连条件`

`--检索李国华同学所学课程的课程号，课程名和成绩，结果标题用中文`

`SELECT 课程表.CNO 课程号,CN 课程名, GRADE 成绩 FROM 学生选课表,课程表,学生表 WHERE`

`学生选课表.CNO =课程表.CNO AND 学生表.SNO=学生选课表.SNO AND 学生表.SN=` `'李国华'` `;`

`SELECT 课程表.CNO 课程号,CN 课程名, GRADE 成绩 FROM 课程表`

`JOIN 学生选课表 ON 课程表.CNO =学生选课表.CNO`

`JOIN 学生表 ON 学生表.SNO= 学生选课表.SNO`

`WHERE 学生表.SN=` `'李国华'` `;`

`-------------------------------------自表查询---------------------------------`

`--检索所有比徐萍年龄大的学生姓名，年龄，性别以及徐萍的年龄`

`USE 学生数据库;`

`SELECT 学生表1.SN 比徐萍年龄大的学生名, 学生表1.SEX 性别,学生表1.AGE 年龄,学生表2.AGE 徐萍的年龄`

`FROM 学生表 学生表1, 学生表  学生表2`

`WHERE 学生表1.AGE>学生表2.AGE AND 学生表2.SN=` `'徐萍'` `;`

`--LIKE --- LIKE 子句必需与指定的符号联合起来，以构成查询条件-------`

`--1、检索姓赵的学生所在的系以及所选的课程名`

`SELECT DEPT 系别, CN 课程名 FROM 学生表,课程表,学生选课表`

`WHERE 学生表.SNO=学生选课表.SNO AND 课程表.CNO=学生选课表.CNO AND SN LIKE` `'张%'` `;`

`SELECT DEPT 系别, CN 课程名 FROM 学生表`

`JOIN 学生选课表 ON 学生表.SNO=学生选课表.SNO`

`JOIN 课程表 ON 课程表.CNO=学生选课表.CNO`

`WHERE SN LIKE` `'张%'` `;`

`--检索学生成绩高于90分的学生名，所选的课程名以及成绩，结果标题用中文`

`SELECT SN 学生名,CN 课程名,GRADE 成绩 FROM 学生表`

`JOIN 学生选课表 ON 学生选课表.SNO=学生表.SNO`

`JOIN 课程表 ON 课程表.CNO=学生选课表.CNO`

`WHERE GRADE LIKE` `'9%'` `;`

`--------------------复杂查询-------------------------------`

`--用多条SELECT ... FROM ...WHERE 语句实现嵌套查询`

`--查出与张婷同一个系的学生名`

`SELECT SN FROM 学生表 WHERE DEPT IN`

`(SELECT DEPT FROM 学生表 WHERE SN=` `'张婷'` `);`

`--查出和赵树林同年龄的学生名，其结果将不包括赵树林本人。`

`SELECT SN 与赵树林同年龄的学生名 FROM 学生表 WHERE AGE`

`IN (SELECT AGE FROM 学生表 WHERE  SN=` `'赵树林'` `)`

`AND SN<>` `'赵树林'` `;`

`/*`

`分析：`

`1、用子句找出赵树林的年龄是19  SELECT AGE FROM 学生表 WHERE SN='赵树林';`

`2、然后找出年龄为19的学生名  SELECT SN FROM 学生表 WHERE AGE =19`

`3、最后用IN关键字把以上两部分联系起来，查出和赵树林同年龄的学生，并用<>运算排队赵树林`

`*/`

`--找出同时选学课程号为C3和C5的学生号，学生名和性别`

`SELECT 学生表.SNO 学生号,SN 学生名,SEX 性别 FROM 学生表,学生选课表 WHERE 学生表.SNO= 学生选课表.SNO AND CNO=` `'C3'` `AND 学生表.SNO`

`IN`

`( SELECT SNO FROM 学生选课表 WHERE CNO=` `'C5'` `);`

`----` `///------`

`SELECT 学生表.SNO ,SN,SEX FROM 学生表 JOIN 学生选课表 ON 学生表.SNO=学生选课表.SNO`

`WHERE CNO=` `'C3'`  `AND 学生表.SNO IN(SELECT SNO FROM 学生选课表 WHERE CNO=` `'C5'` `);`

`/*`

`分析：1、选学课程号为C5的学生号`

`      ` `2、选学课程号为C3的学生号，学生名和年龄`

`      ` `3、把第一步与第二步进行'与'运算`

`*/`

`--找出选学课程号为C3和课程名为政治的学生号和学生名`

`SELECT SNO,SN FROM 学生表 WHERE SNO `

`IN(`

`SELECT SNO FROM 学生选课表 WHERE CNO=` `'C3'`  `AND SNO`

`IN(`

`SELECT SNO FROM 课程表,学生选课表 WHERE 课程表.CNO=学生选课表.CNO AND 课程表.CN=` `'政治'` `));`

`--------` `///---------`

`SELECT 学生表.SNO,SN FROM 学生表 JOIN 学生选课表 ON 学生表.SNO =学生选课表.SNO`

`WHERE CNO =` `'C3'`  `AND 学生表.SNO IN(`

`SELECT SNO FROM 学生选课表 WHERE CNO IN (SELECT CNO FROM 课程表 WHERE CN=` `'政治'` `)`

`);`

`/* 分析：1、先找出课程名为政治的课程号是C5`

`         ` `2、找出由第一步找出的课程号为C5的学生号`

`     ` `3、找出选学课程号为C3的学生号和学生名`

`         ` `4、对第二步和第三步进行‘与’运算`

`*/`

`----NOT IN-----`

`--找出没有选择课程号为C3的学生名`

`SELECT SN FROM 学生表 WHERE SNO NOT IN(`

`SELECT SNO FROM 学生选课表 WHERE CNO=` `'C3'` `);`

`/* 分析：1、在学生选课表中，找出课程号是C3的学生号`

`     ` `2、在学生表中，找出学生号不是C3的学生号的学生名`

`         ` `3、把第一步插入到第二步中，即本题结果`

`注：使用IN('C1','C2')这种情况是‘或’的关系，当使用NOT IN('C1','C2')则是‘与’的关系`

`*/`

`----EXISTS, NOT EXISTS 查询`

`--查询选修课程号为C4的学生号和学生名，分别用EXISTS 和IN 两种关键字来查询`

`--1、用EXISTS`

`SELECT SNO ,SN FROM 学生表 WHERE  EXISTS`

`(SELECT * FROM 学生选课表 WHERE 学生选课表.SNO=学生表.SNO AND 学生选课表.CNO =` `'C4'` `);`

`/*`

`SELECT SNO ,SN FROM 学生表 WHERE  EXISTS`

`(SELECT 学生选课表.SNO ,SN FROM 学生选课表 WHERE 学生选课表.SNO=学生表.SNO AND 学生选课表.CNO ='C4');`

`1、在EXISTS 关键字的嵌套查询中，外围查询的数据表与嵌套子句中的数据表之间连接，是通过嵌套子句中`

`列出两张表连接条件`

`2、在嵌套子句(select * .....)中，跟在SELECT 关键字后面是的*而不是列名。`

`*/`

`SELECT SNO ,SN FROM 学生表 WHERE SNO IN`

`(SELECT SNO FROM 学生选课表 WHERE CNO=` `'C4'` `);`

`--找出没有选择课程号为C3的学生名`

`SELECT SN FROM 学生表 WHERE SNO NOT IN`

`(SELECT SNO FROM 学生选课表 WHERE CNO=` `'C3'` `);`

`---------------------------------------------------------------`

`SELECT SN FROM 学生表 WHERE NOT EXISTS`

`(SELECT SNO FROM 学生选课表 WHERE  学生选课表.SNO=学生表.SNO AND 学生选课表.CNO=` `'C3'` `);`

`--NOT EXISTS 能够方便地解决` `'没有...'` `,` `'不存在'` `,` `'所有'` `查询难题`

`--检索选学了所有课程的学生名和学生号`

`SELECT SN 学生名, SNO 学生号 FROM 学生表`

`WHERE NOT EXISTS`

`(SELECT * FROM 课程表 WHERE NOT EXISTS`

`(SELECT * FROM 学生选课表 WHERE 学生表.SNO=学生选课表.SNO AND 课程表.CNO=学生选课表.CNO));`

`/*`

`1、检索课程表中的课程，这些课程在学生选课表中不存在`

`2、如果第一步没有结果，表示不存在这样的学生，没有课程，他们没有选。即：存在选学了所有课程的学生`

`3、如果第一步有结果，表示存在这样的学生，有课程，他们没有选，即：不存在选学了所有课程的学生。`

`*/`

`-----------------------函数查询--------------------------------------`

`--1、检索学校有多少个学生，多少个系，学生的平均年龄，学生最大年龄，学生最小年龄，结果标题用中文.`

`SELECT COUNT(SNO) 学生总数,COUNT(DISTINCT DEPT) 系别总数,AVG(AGE) 学生平均年龄, MAX(AGE) 学生最大年龄,`

`MIN(AGE) 学生最小年龄 FROM 学生表;`

`--跟在GROUP BY 后面的列名，应该是跟在关键字SELECT 后面的列名相同`

`--GROUP BY 后面的列可以是任意多`

`--2、按性别计算学生的平均年龄`

`SELECT SEX 性别,AVG(AGE) 平均年龄 FROM 学生表 GROUP BY SEX;`

`--按学生学号，性别，年龄来计算学生的平均成绩`

`SELECT 学生表.SNO 学号,SEX 性别,AGE 年龄 ,AVG (GRADE)学生平均成绩 FROM 学生表,学生选课表`

`WHERE 学生选课表.SNO= 学生表.SNO`

`GROUP BY 学生表.SNO, SEX,AGE;`

`--找出选修的课程至少四门的学生的姓名，性别，年龄，平均成绩，选课门数。结果标题用中文`

`/*  HAVING 子句必须与GROUP BY 子句联合起来使用`

`    ` `HAVING 子句给出分组查询的条件`

`    ` `HAVING 子句的分组查询条件必须是统计函数`

`*/`

`SELECT SN 姓名, SEX 性别, AGE 年龄,AVG(GRADE) 平均成绩,COUNT(学生选课表.CNO) 选课门数`

` ` `FROM 学生选课表 JOIN 学生表 ON 学生选课表.SNO=学生表.SNO`

`JOIN  课程表 ON 学生选课表.CNO=课程表.CNO`

`GROUP BY SN,SEX,AGE`

`HAVING COUNT(学生选课表.CNO)>=4;`

`--找出选课在三门以上课程的学生的总成绩(不统计不及格的课程)，并列出总成绩名次名单。结果标题用中文`

`SELECT SNO 学生号,SUM(GRADE) 总成绩 FROM 学生选课表 WHERE GRADE >=60`

`GROUP BY SNO`

`HAVING COUNT(*)>=3`

`ORDER BY 2 DESC;`

`--找出选学课程数据最多的学生`

`SELECT 学生表.SN 学生名 FROM 学生选课表, 学生表`

`WHERE 学生选课表.SNO=学生表.SNO`

`GROUP BY 学生表.SN`

`HAVING COUNT(*)>=ALL`

`(SELECT COUNT(*) FROM 学生选课表 GROUP BY SNO);`

`--学生选学的课程如果超过了四门课，则每门课加5分，请更新数据库`

`UPDATE 学生选课表 SET GRADE =GRADE+5`

`WHERE SNO IN (SELECT SNO FROM 学生选课表 GROUP BY SNO HAVING COUNT(*)>=4);`

Sql复杂查询

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle SQL查询效率低下：复杂查询优化

相关 Mybatis复杂查询及动态SQL

相关 Sql基础语句及一些sql复杂查询语句

相关 SQL-(3) --复杂查询

相关一个复杂的sql 查询

相关 SQL语言实现复杂查询

相关 Sql复杂查询

相关 spring data 复杂的查询，不用写sql

相关 SQL复杂查询与视图【实例】

相关复杂条件查询sql语句小技巧