澳门网络娱乐游戏平台-澳门电子游戏娱乐网址-官方直营

澳门网站大全网址平台:SQL语句学习总括

数据库表和值

随地抵补学习中,持续更新
2016.12.12,13
gruad

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

/*
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) 课程表

 

1.以询问语句为主

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

澳门网站大全网址平台 1

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

澳门网站大全网址平台 2

table sc 学子选课表
学号:课程号:战表分数

澳门网站大全网址平台 3

  1. --新扩充表sc的布局,扩展一列hours 数据类型INT
    ALTER TABLE sc ADD COLUMN hours INT ;

运维结果:

澳门网站大全网址平台 4

2.--查询战表在70-80以内的学习者学号,和科目号,和成就
SELECT sno,cno,grade FROM sc WHERE grade between 70 AND 80;

运行结果:

澳门网站大全网址平台 5

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

运作结果:

澳门网站大全网址平台 6

4.-- 查询学子都选用了这一个课程,列出课程名和课程号
SELECT cname ,cno
FROM course
WHERE cno in(SELECT DISTINCT cno FROM sc);

运维结果:

澳门网站大全网址平台 7

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

运作结果:

澳门网站大全网址平台 8

6.-- 总括各类系的学子人数
SELECT sdept,COUNT(*)
FROM student
WHERE sdept in(SELECT DISTINCT sdept from student)
GROUP BY sdept;

运转结果:

澳门网站大全网址平台 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;

运作结果:

澳门网站大全网址平台 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;

运转结果:

澳门网站大全网址平台 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);

运转结果:

澳门网站大全网址平台 12

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

运维结果:

澳门网站大全网址平台 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

运维结果:

澳门网站大全网址平台 14

12.-- 列出总战表超过150分的学员,供给列出学号、总战表
SELECT sno,SUM(grade)
FROM sc
GROUP BY sno
HAVING SUM(grade)>150;

运行结果:

澳门网站大全网址平台 15

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

运作结果:

澳门网站大全网址平台 16

select FirstName as Family, LastName as Name
from Persons

待增多中...

SC(Sid,Cid,score) 成绩表

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

Teacher(Tid,Tname) 教师表

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

 

步骤二

练习内容:

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

1.询问“某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 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="关羽")))

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

结果

 

澳门网站大全网址平台 17

2.询问平均成绩超越60分的同室的学号和平均战绩;

 

澳门网站大全网址平台,SELECT sid,avg(score)  FROM sc  GROUP BY sid having avg(score) >60;

-- 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

此题知识点,GROUP BY 语句用于结合合计函数,依据叁个或多个列对结果集实行分组。group by前边不能够接where,having取代了where

 

 

-- 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

 

3.查询全部同学的学号、姓名、选课数、总战表

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

 

4.查询姓“李”的教授的个数;

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

 

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

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='叶平');

 此题知识点,distinct是去重的功效

 

6.查询学过“```”並且也学过数码“```”课程的同班的学号、姓名;

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 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;

规范答案(可是好像不佳使)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');  

此题知识点,exists是在群集里找数据,as正是起别称

 

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

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

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

标准答案: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='杨巍巍'卡塔尔卡塔尔

 

8.询问课程编号“”的实际业绩比课程编号“”课程低的有所同学的学号、姓名;

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,

(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 sid,Sname FROM (SELECT Student.sid,Student.Sname,score ,

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

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

 

9.查询全体课程成绩小于分的同窗的学号、姓名;

本文由澳门网络娱乐游戏平台发布于数据库,转载请注明出处:澳门网站大全网址平台:SQL语句学习总括

相关阅读