您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:sql语句演习50题,course多对多涉及为

新葡亰496net:sql语句演习50题,course多对多涉及为

发布时间:2019-10-30 00:37编辑:网络数据库浏览(134)

    数据库表和值

    Student(Sid,Sname,Sage,Ssex) 学生表

     笔记:

    不断补充学习中,持续更新
    2016.12.12,13
    gruad

    /*
    SQLyog Ultimate v8.32 
    MySQL - 5.7.17-log : Database - course_dbms
    *********************************************************************
    */
    
    
    /*!40101 SET NAMES utf8 */;
    
    /*!40101 SET SQL_MODE=''*/;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    CREATE DATABASE /*!32312 IF NOT EXISTS*/`course_dbms` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
    USE `course_dbms`;
    
    /*Table structure for table `course` */
    
    DROP TABLE IF EXISTS `course`;
    
    CREATE TABLE `course` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `teacher_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `teacher_id` (`teacher_id`),
      CONSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
    
    /*Data for the table `course` */
    
    insert  into `course`(`id`,`name`,`teacher_id`) values (1,'语文',1),(2,'数学',1),(3,'生物',2),(4,'化学',2),(5,'物理',2),(6,'英语',3);
    
    /*Table structure for table `student` */
    
    DROP TABLE IF EXISTS `student`;
    
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `city` varchar(10) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
    
    /*Data for the table `student` */
    
    insert  into `student`(`id`,`name`,`city`,`age`) values (1,'小王','北京',20),(2,'小李','上海',18),(3,'小周','北京',22),(4,'小刘','北京',21),(5,'小张','上海',22),(6,'小赵','北京',17),(7,'小蒋','上海',23),(8,'小韩','北京',25),(9,'小魏','上海',25),(10,'小明','北京',20);
    
    /*Table structure for table `student_course` */
    
    DROP TABLE IF EXISTS `student_course`;
    
    CREATE TABLE `student_course` (
      `student_id` int(11) DEFAULT NULL,
      `course_id` int(11) DEFAULT NULL,
      `score` int(11) DEFAULT NULL,
      KEY `student_id` (`student_id`),
      KEY `course_id` (`course_id`),
      CONSTRAINT `student_course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),
      CONSTRAINT `student_course_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `student_course` */
    
    insert  into `student_course`(`student_id`,`course_id`,`score`) values (1,1,80),(1,2,90),(1,3,85),(1,4,78),(2,2,53),(2,3,77),(2,5,80),(3,1,71),(3,2,70),(3,4,80),(3,5,65),(3,6,75),(4,2,90),(4,3,80),(4,4,70),(4,6,95),(5,1,60),(5,2,70),(5,5,80),(5,6,69),(6,1,76),(6,2,88),(6,3,87),(7,4,80),(8,2,71),(8,3,58),(8,5,68),(9,2,88),(10,1,77),(10,2,76),(10,3,80),(10,4,85),(10,5,83);
    
    /*Table structure for table `teacher` */
    
    DROP TABLE IF EXISTS `teacher`;
    
    CREATE TABLE `teacher` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    /*Data for the table `teacher` */
    
    insert  into `teacher`(`id`,`name`) values (1,'关羽'),(2,'张飞'),(3,'赵云');
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    

    Course(Cid,Cname,Tid) 课程表

    一、聚合函数:计数 最大值  最小值  平均数  求和

    为了查看语句运行是否正确,建立如下三张表

     

    SC(Sid,Cid,score) 成绩表

    1.计数 COUNT()  忽略NULL值

    1.以查询语句为主

    table student 学生表
    学号:学生姓名:年龄:性别:系

    新葡亰496net 1

    table course 课程表
    课程号:课程名:学时

    新葡亰496net 2

    table sc 学生选课表
    学号:课程号:成绩分数

    新葡亰496net 3

    1. --新增表sc的结构,增加一列hours 数据类型INT
      ALTER TABLE sc ADD COLUMN hours INT ;

    运行结果:

    新葡亰496net 4

    2.--查询成绩在70-80之间的学生学号,和课程号,和成绩
    SELECT sno,cno,grade FROM sc WHERE grade between 70 AND 80;

    运行结果:

    新葡亰496net 5

    3.-- 查询c03课程成绩最高的分数
    SELECT grade
    FROM sc
    WHERE cno='c03'
    ORDER BY grade DESC
    LIMIT 1;

    运行结果:

    新葡亰496net 6

    4.-- 查询学生都选择了那些课程,列出课程名和课程号
    SELECT cname ,cno
    FROM course
    WHERE cno in(SELECT DISTINCT cno FROM sc);

    运行结果:

    新葡亰496net 7

    5.-- 选择选修了c03课程的所有学生的平均成绩,最高成绩,最低成绩
    SELECT AVG( grade),MAX(grade),MIN(grade)
    FROM sc
    WHERE cno='c03';

    运行结果:

    新葡亰496net 8

    6.-- 统计每个系的学生人数
    SELECT sdept,COUNT(*)
    FROM student
    WHERE sdept in(SELECT DISTINCT sdept from student)
    GROUP BY sdept;

    运行结果:

    新葡亰496net 9

    7.-- 统计每门课的平均成绩
    select cname,AVG( grade)
    FROM sc,course
    WHERE sc.cno in(SELECT cno FROM sc) and sc.cno=course.cno
    GROUP BY course.cname;

    运行结果:

    新葡亰496net 10

    8.-- 统计每门课程的修课人数和考试最高分
    SELECT cname,COUNT(*),MAX(grade)
    FROM course,sc
    WHERE sc.cno in(SELECT DISTINCT cno FROM sc) AND sc.cno=course.cno
    GROUP BY course.cname;

    运行结果:

    新葡亰496net 11

    9.-- 统计每个学生的选课门数,并按选课门数的递增顺序显示结果。
    SELECT student.sname,student.sno,COUNT(sc.sno)
    FROM student,sc
    WHERE student.sno=sc.sno
    GROUP BY student.sname,student.sno
    ORDER BY COUNT(sc.sno) ASC;
    或:
    SELECT student.sname,student.sno,COUNT(sc.sno)
    from student
    INNER JOIN sc ON sc.sno=student.sno
    GROUP BY student.sname,student.sno
    ORDER BY COUNT(sc.sno);

    运行结果:

    新葡亰496net 12

    10.-- 统计选修课的学生总数和考试的平均成绩
    SELECT COUNT(DISTINCT sno),AVG(grade)
    FROM sc;

    运行结果:

    新葡亰496net 13

    11.-- 查询选课门数超过1门的学生的平均成绩和选课门数
    SELECT student.sname,AVG(sc.grade),COUNT(sc.sno)
    FROM sc
    join student on (sc.sno=student.sno)
    join course on (sc.cno=course.cno)
    GROUP BY student.sname
    HAVING COUNT(DISTINCT course.cno)>1

    运行结果:

    新葡亰496net 14

    12.-- 列出总成绩超过150分的学生,要求列出学号、总成绩
    SELECT sno,SUM(grade)
    FROM sc
    GROUP BY sno
    HAVING SUM(grade)>150;

    运行结果:

    新葡亰496net 15

    13.-- 查询选修了c02号课程的学生的姓名和所在系
    SELECT student.sname,student.sdept
    FROM student
    INNER JOIN sc on sc.sno=student.sno
    where sc.cno='c02';

    运行结果:

    新葡亰496net 16

    select FirstName as Family, LastName as Name
    from Persons

    待添加中...

    -- 4、查询没学过关羽老师课的同学的学号、姓名

    Teacher(Tid,Tname) 教师表

    方式1:COUNT(*)  *代表所有字段 一般用于查询表中共有多少条记录(实体)

    步骤一
    SELECT c.id FROM teacher t,course c WHERE t.id=c.teacher_id AND t.name="关羽"

    练习内容:

    SELECT COUNT(*) zongshu FROM emp;

    步骤二

    1.查询“某1”课程比“某2”课程成绩高的所有学生的学号;

    方式2:计算某个字段中非NULL值的个数

    SELECT DISTINCT s.id FROM
    student s,student_course sc WHERE s.id=sc.student_id AND course_id IN(1,2)

    SELECT a.sid FROM (SELECT sid,score FROM SC WHERE cid=1) a,(SELECT sid,score FROM SC WHERE cid=3) b WHERE a.score>b.score AND a.sid=b.sid;

    SELECT COUNT(ALL comm) FROM emp;-- all 可以省略

    步骤三

    此题知识点,嵌套查询和给查出来的表起别名

    方式3:去重计数  计算某列中不重复非NULL值的个数

    SELECT id,NAME FROM student WHERE id NOT IN(SELECT DISTINCT s.id FROM
    student s,student_course sc WHERE
    s.id=sc.student_id AND course_id IN
    ((SELECT c.id FROM teacher t,course c WHERE t.id=c.teacher_id AND t.name="关羽")))

    2.查询平均成绩大于60分的同学的学号和平均成绩;

    SELECT COUNT( DISTINCT job) FROM emp;

    结果

    SELECT sid,avg(score)  FROM sc  GROUP BY sid having avg(score) >60;

    2.最大值  MAX() 求某一列中的最大值,不分组的情况下不可与其他字段一起使用

    新葡亰496net 17

    新葡亰496net,此题知识点,GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。group by后面不能接where,having代替了where

    SELECT MAX(sal) FROM emp ;

     

    3.查询所有同学的学号、姓名、选课数、总成绩

    3.最小值  MIN()  不分组的情况下不可与其他字段一起使用

    -- 5、查询没有学三门课以上的同学的学号、姓名
    --前期准备步骤
    SELECT * FROM student
    SELECT * FROM course
    SELECT * FROM student_course
    --学了几门课
    SELECT COUNT(*) "course_nums",student_id
    FROM student_course GROUP BY student_id
    --几门课少于3门的是谁
    SELECT t.*
    FROM (SELECT COUNT(*) "course_nums",student_id
    FROM student_course GROUP BY student_id) t
    WHERE t.course_nums<3
    --方法一
    SELECT s.id,s.name
    FROM student s,
    (SELECT t.*
    FROM (SELECT COUNT(*) "course_nums",student_id
    FROM student_course GROUP BY student_id) t
    WHERE t.course_nums<3) a
    WHERE s.id=a.student_id
    --方法二 多表的内连接查询
    SELECT s.id,s.name
    FROM student s,
    (SELECT COUNT(*) "course_nums",student_id
    FROM student_course GROUP BY student_id HAVING course_nums<3) t
    WHERE s.id=t.student_id

    SELECT Student.sid,Student.Sname,count(SC.cid),sum(score)FROM Student left Outer JOIN SC on Student.sid=SC.cid GROUP BY Student.sid,Sname

    SELECT  MIN(sal) FROM emp ;

     

    4.查询姓“李”的老师的个数;

    4.平均值  AVG()  不分组的情况下不可与其他字段一起使用

    -- 6、查询各科成绩最高和最低的分及课程名称
    --前期准备步骤
    SELECT * FROM student
    SELECT * FROM course
    SELECT * FROM student_course
    --直接在student_course查询最高分与最低分是不可取的,因为没有分组
    SELECT MAX(score),MIN(score) FROM student_course
    --按照couse_id进行分组
    SELECT MAX(score),MIN(score),course_id FROM student_course
    GROUP BY course_id
    --把课程名称字段也显示出来,需要内连接查询(这个子查询实际上是VIEW视图的概念--视图就是一张虚表)
    --方法一显示全部字段方法
    SELECT *
    FROM course c,
    (SELECT MAX(score),MIN(score),course_id FROM student_course
    GROUP BY course_id) t
    WHERE c.id=t.course_id
    --方法二显示指定字段需要起别名
    SELECT t.max_score,t.min_score,course_id,c.name
    FROM course c,
    (SELECT MAX(score) AS "max_score",MIN(score) "min_score",course_id FROM student_course
    GROUP BY course_id) t
    WHERE c.id=t.course_id

    select count(teacher.tid)from teacher where teacher.tname like'李%'

    SELECT AVG(sal) FROM emp;

    5.查询没学过“叶平”老师课的同学的学号、姓名;

    5.求和  SUM() 不分组的情况下不可与其他字段一起使用

    SELECT Student.sid,Student.Sname FROM Student WHERE sid not in (SELECT distinct( SC.sid) FROM SC,Course,Teacher WHERE  SC.cid=Course.cid AND Teacher.id=Course.tid AND Teacher.Tname='叶平');

    SELECT SUM(sal) FROM emp;

     此题知识点,distinct是去重的作用

    练习:

    6.查询学过“```新葡亰496net:sql语句演习50题,course多对多涉及为例。”并且也学过编号“```”课程的同学的学号、姓名;

    1.查询员工的总人数;

    select a.SID,a.SNAME from (select student.SNAME,student.SID from student,course,sc where cname='c 'and sc.sid=student.sid and sc.cid=course.cid) a,

    SELECT COUNT(*) FROM emp;

    (select student.SNAME,student.SID from student,course,sc where cname='english'and sc.sid=student.sid and sc.cid=course.cid) b where a.sid=b.sid;

    2.查询员工的平均工资;

    标准答案(但是好像不好使)SELECT Student.S#,Student.Sname FROM Student,SC WHERE Student.S#=SC.S# AND SC.C#='001'and exists( SELECT * FROM SC as SC_2 WHERE SC_2.S#=SC.S# AND SC_2.C#='002');  

    SELECT AVG(sal) FROM emp;

    此题知识点,exists是在集合里找数据,as就是起别名

    3.查询文员的总人数;

    7.查询学过“叶平”老师所教的所有课的同学的学号、姓名;

    SELECT COUNT(*) FROM emp WHERE job='文员';

    select a.sid,a.sname from (select student.sid,student.sname from student,teacher,course,sc 

    1. 查询30号部门中文员的总工资;

    where teacher.TNAME='杨巍巍' and teacher.tid=course.tid and course.cid=sc.cid and student.sid=sc.sid) a

    SELECT SUM(sal IFNULL(comm,0)) FROM emp WHERE deptno=30 AND job ='文员';

    标准答案:SELECT sid,Sname FROM Student WHERE sid in (SELECT sid FROM SC ,Course ,Teacher WHERE SC.cid=Course.cid AND Teacher.tid=Course.tid AND Teacher.Tname='杨巍巍' GROUP BY sid having count(SC.cid)=(SELECT count(cid) FROM Course,Teacher  WHERE Teacher.tid=Course.tid AND Tname='杨巍巍'))

    5.查询10号部门中员工的最低工资;

    8.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名;

    SELECT MIN(sal) FROM emp WHERE deptno=10;

    select a.sid,a.sname from(select student.SID,student.sname,sc.SCORE  from student,sc where student.sid=sc.sid and sc.cid=1) a,

    6.查询入职时间在2001-2005的员工的最高工资

    (select student.SID,student.sname,sc.score from student,sc where student.sid=sc.sid and sc.cid=2) b where a.score<b.score and a.sid=b.sid

    SELECT MAX(sal) FROM emp WHERE hiredate BETWEEN '2001' AND '2005';

    标准答案:SELECT sid,Sname FROM (SELECT Student.sid,Student.Sname,score ,

    7.查询2005年之前入职的员工的平均工资

    (SELECT score FROM SC SC_2 WHERE SC_2.sid=Student.sid AND SC_2.cid=1) score2 FROM Student,SC

    SELECT AVG(sal) FROM emp WHERE hiredate <'2005';

    WHERE Student.sid=SC.sid AND cid=1) S_2 WHERE score2 <score;

    8.查询2002年之后入职的员工的最高工资和最低工资和平均工资。

    9.查询所有课程成绩小于分的同学的学号、姓名;

    SELECT MAX(sal) ,MIN(sal),AVG(sal) FROM emp  WHERE hiredate >'2002';

    SELECT sid,Sname FROM Student WHERE sid not in (SELECT Student.sid FROM Student,SC WHERE Student.sid=SC.sid AND score>60); 

    9.查询30号部门的最高工资、最低工资、平均工资;

    此题知识点,先查出大于60分的,然后not in 就是小于60分的了

    SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE deptno =30;

    10.查询没有学全所有课的同学的学号、姓名;

    10.查询10号或20号部门的文员的最高工资、最低工资、平均工资;

    SELECT Student.sid,Student.Sname  FROM Student,SC  

    SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE (deptno =10 OR deptno =20 ) AND job ='文员';

    WHERE Student.sid=SC.sid GROUP BY  Student.sid,Student.Sname having count(cid) <(SELECT count(cid) FROM Course); 

    二、分组  GROUP BY

    11.查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名;

    分组计算时SELECT 语句中可以有分组字段和聚合函数,但不要放其他字段;

    12.查询至少学过学号为“”同学所有一门课的其他同学学号和姓名;

    SELECT deptno, AVG(sal),MAX(sal) ,MIN(sal) ,SUM(sal)FROM  emp WHERE sal >20000 GROUP BY deptno;

    SELECT student.sid,student.Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHERE sid=1)

    HAVING 用户过滤聚合函数的值 只能用在分组后面

    此题知识点,SELECT sid,Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHERE sid=1)这样写是错误的,因为from后面是两个表,不能明确是哪个表里面的sid和sname所以错误提示是“未明确定义列”

    SELECT deptno ,MIN(sal),MAX(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>8000 AND MAX()<50000;

    13.把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

    SELECT deptno ,MIN(sal) minSal ,MAX(sal) b FROM emp GROUP BY deptno HAVING minSal>8000 AND b<50000;

    update sc set score=(select avg(score) from sc,course,teacher where course.cid=sc.cid and course.tid=teacher.tid and teacher.tname='杨巍巍')

    使用多个字段分组:

    14.查询和“”号的同学学习的课程完全相同的其他同学学号和姓名;

    计算每个部门中每个岗位的平均工资:

    SELECT sid FROM SC WHERE cid in (SELECT cid FROM SC WHERE sid=6) GROUP BY sid having count(*)=(SELECT count(*) FROM SC WHERE sid=6); 

    SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno ,job ;

    此题知识点,用数量来判断 

    分组练习:

    15.删除学习“叶平”老师课的SC表记录; 

    -- 1.查询各个部门的最高工资、最低工资、平均工资。

    delete from sc s where s.cid in (select c.cid from teacher t,course c where t.tid = c.tid and tname='李子')

    SELECT deptno,MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY deptno;

    此题知识点,嵌套查询可以分布考虑,先查出李子老师都交了什么课的id,然后再删除那些id的值

    -- 2.查询各个职位的平均工资 降序排列

    16.向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“”课程的同学学号、课程的平均成绩;

    SELECT job,AVG(sal) avgSal FROM emp GROUP BY job ORDER BY avgSal DESC ;

    Insert into SC SELECT sid,2,(SELECT avg(score) FROM SC WHERE cid=2) FROM Student WHERE sid not in (SELECT sid FROM SC WHERE cid=2); 

    -- 3.查询平均工资大于10000的岗位

    17.按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示:学生ID,,数据库,企业管理,英语,有效课程数,有效平均分;(没做出来)

    SELECT job ,AVG(sal) avgSal FROM emp GROUP BY job HAVING avgSal >10000 ;

    18.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;

    -- 4.查询每个部门的人数

    select cid as 课程号,max(score)as 最高分,min(score) as 最低分 from sc group by cid

    SELECT deptno ,COUNT(ename) FROM emp GROUP BY deptno;

    标准答案(但是运行不好使)SELECT L.cid As 课程ID,L.score AS 最高分,R.score AS 最低分 

    -- 5.查询人数大于5的部门

    FROM SC L ,SC AS R  

    SELECT deptno ,COUNT(ename) qty FROM emp GROUP BY deptno HAVING qty>5;

    WHERE L.cid = R.cid AND  

    新葡亰496net:sql语句演习50题,course多对多涉及为例。-- 6.查询部门人数小于3的部门的平均工资、最高工资、最低工资

    L.score = (SELECT MAX(IL.score)  

    SELECT deptno,AVG(sal),MAX(sal),MIN(sal) FROM emp  GROUP BY deptno HAVING COUNT(*)<=3;

    FROM SC AS IL,Student AS IM  

    -- 7.查询各个部门中工资大于10000的人数 降序显示

    WHERE L.cid = IL.cid AND IM.sid=IL.sid  

    SELECT deptno ,COUNT(empno) qty FROM emp WHERE sal>10000 GROUP BY deptno ORDER BY qty DESC ;

    GROUP BY IL.cid)  

    -- 8.查询各个岗位中工资大于10000的人数 升序显示

    AND  R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.cid = IR.cid  GROUP BY IR.cid ); 

    -- 9.查询平均工资大于10000的前两个部门。

    19.按各科平均成绩从低到高和及格率的百分数从高到低顺序

    SELECT AVG(sal) ,deptno FROM emp  GROUP BY deptno HAVING AVG(sal)>10000 LIMIT 2;

    26.查询每门课程被选修的学生数

    -- 10.查询每个部门中没有津贴的人数。

    select sc.cid,count(sc.sid) from sc,course where sc.cid=course.cid group by sc.cid

    SELECT deptno,COUNT(*) FROM emp WHERE comm IS NULL GROUP BY deptno

    27.查询出只选修了一门课程的全部学生的学号和姓名

    三、组合查询 - 子查询

    SELECT SC.sid,Student.Sname,count(cid) AS 选课数 FROM SC ,Student  

    1.单行单列  跟在WHERE子句后,用于判断的条件

    WHERE SC.sid=Student.sid GROUP BY SC.sid ,Student.Sname having count(cid)=1;

    -- 比李世民工资高的所有员工的信息

    32.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

    SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='李世民')

    SELECT Cid,Avg(score) FROM SC GROUP BY cid ORDER BY Avg(score),cid DESC ;

    2.单行多列  跟在WHERE子句后

    37.查询不及格的课程,并按课程号从大到小排列 

    -- 查询和李世民工资及职位都一样的人的所有信息

    SELECT cid,sid FROM sc WHERE score <60 ORDER BY cid 

    SELECT * FROM emp WHERE (job,sal) IN (SELECT job ,sal FROM emp WHERE ename='李世民')

    38.查询课程编号为且课程成绩在分以上的学生的学号和姓名;

    AND ename !='李世民';

    select student.sid,student.sname from sc,student where sc.cid=1 and sc.score>60 and sc.sid=student.sid

    3.多行单列

    40.查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩

    -- 查询大于30号部门中任意一个员工工资的人的所有信息

    select student.sname,sc.score from sc,student,teacher,course c where teacher.tname='李子'

    -- any:任意的意思

    and teacher.tid=c.tid and c.cid=sc.cid and sc.sid=student.sid and sc.score=(select max(score)from sc where sc.cid=c.cid)

    SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);

    41.查询各个课程及相应的选修人数

    SELECT * FROM emp WHERE sal >  (SELECT MIN(sal) FROM emp WHERE deptno=30);

    select sc.cid ,count(sc.sid)from sc,student where sc.sid=student.sid group by sc.cid 

    -- 查询大于30号部门中所有人工资的员工信息

    43.查询每门功成绩最好的前两名

    SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

     

    4.多行多列 用在FROM子句后面 作为虚表使用

    44.统计每门课程的学生选修人数(超过人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列

    SELECT e.ename FROM (SELECT * FROM emp WHERE deptno =30) e WHERE e.comm IS NULL;

    select sc.cid,count(sc.cid)from sc,course where sc.cid=course.cid group by sc.cid  order by sc.cid desc

    组合查询-- 合并结果集  上下两条查询语句的字段个数要一样;

    45.检索至少选修两门课程的学生学号

    UNION --去除重复数据的合并;

    SELECT sid FROM  sc group  by  sid having  count(*)  >  =  2  

    SELECT ename,sal FROM emp WHERE deptno=10 UNION SELECT  ename,sal FROM emp WHERE deptno=20;

     

    UNION ALL  -- 不去除重复记录

    rownum的用法

    SELECT ename,sal  FROM emp WHERE deptno=10 UNION ALL SELECT  ename,sal FROM emp WHERE deptno=20;

    查询所有成绩第二名到第四名的成绩

    四、链接

    select * from (select rownum p,t.score from(SELECT s.score score FROM sc s ORDER BY score desc)t )tt where tt.p>1 and tt.p<5

    1. 内连接 去除笛卡尔积:去除匹配错误的数据;

     

    1.1 mysql方言方式:

    47.查询没学过“叶平”老师讲授的任一门课程的学生姓名

    SELECT * FROM emp ,dept  WHERE emp.deptno= dept.deptno AND dept.dname='学工部' ORDER BY emp.sal DESC ;

    select distinct sid from sc where sid not in(select sc.sid from sc,course,teacher where sc.cid=course.cid and course.tid=teacher.tid and 

    1.2 标准SQL方式:SELECT * FROM emp INNER JOIN dept  ON emp.deptno= dept.deptno;

    teacher.tname='杨巍巍')

    1.3自然连接:SELECT * FROM emp NATURAL JOIN dept;

    48.查询两门以上不及格课程的同学的学号及其平均成绩

    2.外连接

    49.检索“”课程分数小于,按分数降序排列的同学学号

    2.1 左外连接 LEFT OUTER JOIN  -- out可以省略,以左边表的行数为准,若右边表没有与之匹配的数据,那么用null值填充

    select sc.sid from sc,course where sc.cid=course.cid and course.cname='java' and sc.score<90

    SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno =d.deptno;

    50.删除“”同学的“”课程的成绩 

    SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.deptno =d.deptno;

    delete from sc where sid=1 and cid=1

    2.2 右外连接  RIGHT OUTER JOIN  与左外连接意思相反

    SELECT * FROM emp e RIGHT  JOIN dept d ON e.deptno =d.deptno;

    二。1:查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

    2:薪水大于12000的雇员,按照部门编号进行分组,分组后的平均薪水必须大于15000,查询各分组的平均工资,按照工资的倒序进行排列

    3:查询每个雇员和其所在的部门名

    4.查询每个雇员姓名及其工资所在的等级

    5:查询雇员名第2个字不是‘中‘的雇员的姓名、所在的部门名、工资所在的等级。

    6:查询每个雇员和其经理的姓名

    -- 7:查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理))

    SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

    -- 8:查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门)

    SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;

    -- 9:查询每个部门中工资最高的人的姓名、薪水和部门编号

    SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);

    SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;

    -- 10:查询每个部门平均工资所在的等级

    SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

    SELECT deptno,AVG(sal) avgSal  FROM emp GROUP BY deptno

    ) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

    -- 11:查询每个部门内平均的薪水等级

    SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

    SELECT deptno,AVG(sal IFNULL(comm,0)) avgSal  FROM emp GROUP BY deptno

    ) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

    -- 12:查询雇员中有哪些人是经理人

    -- 13:不准用max函数,求雇员表中薪水的最高值。

    SELECT * FROM emp ORDER BY sal IFNULL(comm,0)  DESC LIMIT 0,1;

    -- 14:平均薪水最高的部门的部门编号

    SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

    -- 15:求平均薪水最高的部门的部门名称

    SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

    -- 16:查询手下有员工的领导的信息

    SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

    -- mysql联表查询练习:

    -- 1.查询销售部工资大于20000的所有人;

    SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='销售部' AND emp.sal >20000;

    -- 2.查询每个部门的人数,要求显示部门名称;

    SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (

    SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1  ON dept.deptno=d1.deptno;

    -- 3.查询每个部门的最高工资,平均工资,最低工资 ,要求显示部门名称;

    SELECT * FROM dept LEFT JOIN (

    SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2  ON dept.deptno = d2.deptno

    -- 4.查询教研部中入职时间最早的员工信息,要显示部门名称,姓名,入职时间;

    SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'  ORDER BY hiredate  LIMIT 1;

    -- 5.查询当前没有员工的部门信息;

    SELECT * FROM (

    SELECT dept.deptno,dept.dname ,dept.loc  ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;

    三。课后作业:

    -1.学生表

    Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

    --创建测试数据

    create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

    insert into Student values('02' , '钱电' , '1990-12-21' , '男');

    insert into Student values('03' , '孙风' , '1990-05-20' , '男');

    insert into Student values('04' , '李云' , '1990-08-06' , '男');

    insert into Student values('05' , '周梅' , '1991-12-01' , '女');

    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

    insert into Student values('08' , '王菊' , '1990-01-20' , '女');

    --2.课程表

    Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号

    create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

    insert into Course values('01' , '语文' , '02');

    insert into Course values('02' , '数学' , '01');

    insert into Course values('03' , '英语' , '03');

    --3.教师表

    Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名

    create table Teacher(Tid varchar(10),Tname varchar(10));

    insert into Teacher values('01' , '张三');

    insert into Teacher values('02' , '李四');

    insert into Teacher values('03' , '王五');

    --4.成绩表

    SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数

    create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));

    insert into SC values('01' , '01' , 80);

    insert into SC values('01' , '02' , 90);

    insert into SC values('01' , '03' , 99);

    insert into SC values('02' , '01' , 70);

    insert into SC values('02' , '02' , 60);

    insert into SC values('02' , '03' , 80);

    insert into SC values('03' , '01' , 80);

    insert into SC values('03' , '02' , 80);

    insert into SC values('03' , '03' , 80);

    insert into SC values('04' , '01' , 50);

    insert into SC values('04' , '02' , 30);

    insert into SC values('04' , '03' , 20);

    insert into SC values('05' , '01' , 76);

    insert into SC values('05' , '02' , 87);

    insert into SC values('06' , '01' , 31);

    insert into SC values('06' , '03' , 34);

    insert into SC values('07' , '02' , 89);

    insert into SC values('07' , '03' , 98);

    课后作业:

    -1.学生表

    Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

    --创建测试数据

    create table Student(Sid varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

    insert into Student values('02' , '钱电' , '1990-12-21' , '男');

    insert into Student values('03' , '孙风' , '1990-05-20' , '男');

    insert into Student values('04' , '李云' , '1990-08-06' , '男');

    insert into Student values('05' , '周梅' , '1991-12-01' , '女');

    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

    insert into Student values('08' , '王菊' , '1990-01-20' , '女');

    --2.课程表

    Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号

    create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

    insert into Course values('01' , '语文' , '02');

    insert into Course values('02' , '数学' , '01');

    insert into Course values('03' , '英语' , '03');

    --3.教师表

    Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名

    create table Teacher(Tid varchar(10),Tname varchar(10));

    insert into Teacher values('01' , '张三');

    insert into Teacher values('02' , '李四');

    insert into Teacher values('03' , '王五');

    --4.成绩表

    SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数

    create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));

    insert into SC values('01' , '01' , 80);

    insert into SC values('01' , '02' , 90);

    insert into SC values('01' , '03' , 99);

    insert into SC values('02' , '01' , 70);

    insert into SC values('02' , '02' , 60);

    insert into SC values('02' , '03' , 80);

    insert into SC values('03' , '01' , 80);

    insert into SC values('03' , '02' , 80);

    insert into SC values('03' , '03' , 80);

    insert into SC values('04' , '01' , 50);

    insert into SC values('04' , '02' , 30);

    insert into SC values('04' , '03' , 20);

    insert into SC values('05' , '01' , 76);

    insert into SC values('05' , '02' , 87);

    insert into SC values('06' , '01' , 31);

    insert into SC values('06' , '03' , 34);

    insert into SC values('07' , '02' , 89);

    insert into SC values('07' , '03' , 98);

    --3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

    SELECT st.sid,st.sname,AVG(sc.score) avgScore FROM student st ,sc WHERE st.sid=sc.Sid GROUP BY sid  HAVING avgScore>60;

    --4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

    --5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

    SELECT st.sid,st.sname ,COUNT(sc.cid),SUM(sc.score) FROM student st ,sc WHERE st.sid=sc.sid GROUP BY st.sid;

    --6、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩

    SELECT * FROM student st,sc,course co,teacher  te WHERE

    st.sid=sc.sid AND sc.cid=co.cid AND co.tid=te.tid AND te.tname='张三' ORDER BY sc.score DESC LIMIT 1;

    --7、查询本周过生日的学生

    SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW()) ;

    --8、查询下周过生日的学生

    SELECT  * FROM student WHERE  WEEKOFYEAR(DATE_ADD(sage,INTERVAL YEAR(NOW())-YEAR(sage) YEAR))=WEEKOFYEAR(NOW()) 1 ;

    --9、查询本月过生日的学生

    SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW());

    --10、查询下月过生日的学生

    SELECT * FROM student WHERE MONTH(sage) =MONTH(NOW()) 1;

    --11、查询每门功成绩最好的前两名

    SELECT  sid,cid,score

    FROM sc r1

    WHERE  (SELECT COUNT(*) FROM sc r2 WHERE r2.cid=r1.cid AND r1.score <= r2.score) <=2 ORDER BY cid ;

    --12、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

    SELECT  cid,COUNT(*) qty FROM sc GROUP BY cid HAVING qty>5 ORDER BY qty DESC ;

    --13、检索至少选修两门课程的学生学号

    SELECT sid ,COUNT(*) FROM sc GROUP BY sid HAVING COUNT(*) >=2;

    --14、查询选修了全部课程的学生信息

    SELECT sid ,COUNT(*) aa  FROM sc GROUP BY sid HAVING aa=(SELECT COUNT(*) FROM course);

    --15、查询各学生的年龄

    SELECT * ,YEAR(NOW())-YEAR(sage) FROM student

    --1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

    SELECT  a.sid,a.score,b.score FROM (

    SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.Cid=01 ) a,

    (

    SELECT st.sid,sc.cid,sc.score FROM student st ,sc WHERE st.sid=sc.sid AND sc.cid=02) b  WHERE a.sid=b.sid  AND a.score >b.score;

    --2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

    --3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

    SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN

    (SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs>=60)s2 ON s1.Sid=s2.Sid ;

    --4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

    SELECT DISTINCT s1.Sid,s1.Sname,s2.avgs FROM student s1 INNER JOIN

    (SELECT sid,AVG(score)avgs FROM sc GROUP BY sid HAVING avgs<60)s2 ON s1.Sid=s2.Sid ;

    --5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

    SELECT DISTINCT s1.Sid,s1.Sname,s2.cc,s2.ss FROM student s1 INNER JOIN

    (SELECT sid,COUNT(cid)cc,SUM(score)ss FROM sc GROUP BY sid) s2 ON s1.Sid=s2.Sid;

    --6、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩

    SELECT DISTINCT * FROM student s1 INNER JOIN (SELECT * FROM sc WHERE cid=01 ORDER BY score DESC LIMIT 2) s2 ON s1.sid=s2.sid;

    --7、查询本周过生日的学生

    --8、查询下周过生日的学生

    --9、查询本月过生日的学生

    --10、查询下月过生日的学生

    --11、查询每门功成绩最好的前两名

    (SELECT *FROM sc WHERE cid =01 ORDER BY score DESC LIMIT 2)UNION

    (SELECT *FROM sc WHERE cid =02 ORDER BY score DESC LIMIT 2)UNION

    (SELECT *FROM sc WHERE cid =03 ORDER BY score DESC LIMIT 2);

    --12、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

    SELECT cid,COUNT(sid) cs FROM sc GROUP BY cid HAVING cs>5 ORDER BY cs DESC;

    --13、检索至少选修两门课程的学生学号

    SELECT sid FROM sc GROUP BY sid HAVING COUNT(cid)>=2;

    --14、查询选修了全部课程的学生信息

    SELECT * FROM Student s1 INNER JOIN (SELECT sid,COUNT(cid) cc FROM sc GROUP BY sid HAVING cc=3) s2 ON s1.sid=s2.sid

    --15、查询各学生的年龄

    --1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

    --2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

    四。1:查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

    2:薪水大于12000的雇员,按照部门编号进行分组,分组后的平均薪水必须大于15000,查询各分组的平均工资,按照工资的倒序进行排列

    3:查询每个雇员和其所在的部门名

    4.查询每个雇员姓名及其工资所在的等级

    5:查询雇员名第2个字不是‘中‘的雇员的姓名、所在的部门名、工资所在的等级。

    6:查询每个雇员和其经理的姓名

    -- 7:查询每个雇员和其经理的姓名(包括公司老板本身(他上面没有经理))

        SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

    -- 8:查询每个雇员的姓名及其所在部门的部门名(包括没有雇员的部门)

      SELECT e.ename,d.dname FROM dept d  LEFT JOIN emp e  ON  d.deptno=e.deptno;

    -- 9:查询每个部门中工资最高的人的姓名、薪水和部门编号

        SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);

        SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;

    -- 10:查询每个部门平均工资所在的等级

    SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

    SELECT deptno,AVG(sal) avgSal  FROM emp GROUP BY deptno

    ) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

    -- 11:查询每个部门内平均的薪水等级

      SELECT  e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

    SELECT deptno,AVG(sal IFNULL(comm,0)) avgSal  FROM emp GROUP BY deptno

    ) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

    -- 12:查询雇员中有哪些人是经理人

    -- 13:不准用max函数,求雇员表中薪水的最高值。

      SELECT * FROM emp ORDER BY sal IFNULL(comm,0)  DESC LIMIT 0,1;

    -- 14:平均薪水最高的部门的部门编号

        SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

    -- 15:求平均薪水最高的部门的部门名称

        SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

    -- 16:查询手下有员工的领导的信息

            SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

    -- mysql联表查询练习:

    -- 1.查询销售部工资大于20000的所有人;

        SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='销售部' AND emp.sal >20000;

    -- 2.查询每个部门的人数,要求显示部门名称;

    SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (

      SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1  ON dept.deptno=d1.deptno;

    -- 3.查询每个部门的最高工资,平均工资,最低工资 ,要求显示部门名称;

    SELECT * FROM dept LEFT JOIN (

      SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2  ON dept.deptno = d2.deptno

    -- 4.查询教研部中入职时间最早的员工信息,要显示部门名称,姓名,入职时间;

        SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'  ORDER BY hiredate  LIMIT 1;

    -- 5.查询当前没有员工的部门信息;

    SELECT * FROM (

      SELECT dept.deptno,dept.dname ,dept.loc  ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:sql语句演习50题,course多对多涉及为

    关键词:

上一篇:CASE WHEN 及 SELECT CASE WHEN的用法

下一篇:没有了