一、构建表结构

本文全部来自sql语句练习，我是用来进行练习。并对sql的一些基础进行一下记录。

构建一个Grade 的表结构，进行 SQL 语句练习。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

-- MySQL dump 10.13  Distrib 5.7.17, for macos10.12 (x86_64)
--
-- Host: localhost    Database: learn_sql_pdai_tech
-- ------------------------------------------------------
-- Server version	5.7.28

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 */;

--
-- Table structure for table `COURSE`
--

DROP TABLE IF EXISTS `COURSE`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `COURSE` (
  `CNO` varchar(5) NOT NULL,
  `CNAME` varchar(10) NOT NULL,
  `TNO` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `COURSE`
--

LOCK TABLES `COURSE` WRITE;
/*!40000 ALTER TABLE `COURSE` DISABLE KEYS */;
INSERT INTO `COURSE` VALUES ('3-105','计算机导论','825'),('3-245','操作系统','804'),('6-166','数据电路','856'),('9-888','高等数学','100');
/*!40000 ALTER TABLE `COURSE` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `SCORE`
--

DROP TABLE IF EXISTS `SCORE`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `SCORE` (
  `SNO` varchar(3) NOT NULL,
  `CNO` varchar(5) NOT NULL,
  `DEGREE` decimal(10,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `SCORE`
--

LOCK TABLES `SCORE` WRITE;
/*!40000 ALTER TABLE `SCORE` DISABLE KEYS */;
INSERT INTO `SCORE` VALUES ('103','3-245',86.0),('105','3-245',75.0),('109','3-245',68.0),('103','3-105',92.0),('105','3-105',88.0),('109','3-105',76.0),('101','3-105',64.0),('107','3-105',91.0),('101','6-166',85.0),('107','6-106',79.0),('108','3-105',78.0),('108','6-166',81.0);
/*!40000 ALTER TABLE `SCORE` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `STUDENT`
--

DROP TABLE IF EXISTS `STUDENT`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `STUDENT` (
  `SNO` varchar(3) NOT NULL,
  `SNAME` varchar(4) NOT NULL,
  `SSEX` varchar(2) NOT NULL,
  `SBIRTHDAY` datetime DEFAULT NULL,
  `CLASS` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `STUDENT`
--

LOCK TABLES `STUDENT` WRITE;
/*!40000 ALTER TABLE `STUDENT` DISABLE KEYS */;
INSERT INTO `STUDENT` VALUES ('108','曾华','男','1977-09-01 00:00:00','95033'),('105','匡明','男','1975-10-02 00:00:00','95031'),('107','王丽','女','1976-01-23 00:00:00','95033'),('101','李军','男','1976-02-20 00:00:00','95033'),('109','王芳','女','1975-02-10 00:00:00','95031'),('103','陆君','男','1974-06-03 00:00:00','95031');
/*!40000 ALTER TABLE `STUDENT` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `TEACHER`
--

DROP TABLE IF EXISTS `TEACHER`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TEACHER` (
  `TNO` varchar(3) NOT NULL,
  `TNAME` varchar(4) NOT NULL,
  `TSEX` varchar(2) NOT NULL,
  `TBIRTHDAY` datetime NOT NULL,
  `PROF` varchar(6) DEFAULT NULL,
  `DEPART` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `TEACHER`
--

LOCK TABLES `TEACHER` WRITE;
/*!40000 ALTER TABLE `TEACHER` DISABLE KEYS */;
INSERT INTO `TEACHER` VALUES ('804','李诚','男','1958-12-02 00:00:00','副教授','计算机系'),('856','张旭','男','1969-03-12 00:00:00','讲师','电子工程系'),('825','王萍','女','1972-05-05 00:00:00','助教','计算机系'),('831','刘冰','女','1977-08-14 00:00:00','助教','电子工程系');
/*!40000 ALTER TABLE `TEACHER` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-02-06 18:18:25

二、练习

  1. 查询 Student 表中的所有记录的Sname、Ssex和Class 列。

    注释

    select * from table -- 这是一条注释

    # 这是一条注释

    /* 注释 */

    简单的 检索数据 select 用法

    1

    SELECT Sname, Ssex, Class FROM student;

  2. 查询教师所有的单位即不重复的 Depart 列。

    检索不同的值，使用 DISTINCT 关键词

    1

    SELECT DISTINCT depart FROM teacher;

  3. 查询Student 表的所有记录。

    1

    SELECT * FROM student;

  4. 查询 Score 表中成绩在60到 80 之间的所有记录。

    过滤数据，where， between

    1

    SELECT * FROM score WHERE degree > 60 AND degree < 80;

  5. 查询 Score 表中成绩为85，86或88的记录。

    高级过滤数据， where，in、or

    1

    SELECT * FROM score WHERE degree IN ( 85, 86, 88);

  6. 查询 Student 表中”95031“班 或性别为 ”女“的同学记录。

    高级过滤数据，组合where 子句：and， OR

    1

    SELECT * FROM student WHERE class = '95031' AND Ssex = '女';

  7. 以 Class 降序查询 Student 表的所有记录。

    排序检索数据，order by 、desc

    数据排序默认是升序排序。ASC 没有多大用处。

    1

    SELECT * FROM student ORDER BY class DESC;

  8. 以 Cno 升序、Degree降序查询 Score 表的所有记录。

    按照多个列进行排序，为每一个列都进行指定顺序

    1

    SELECT * FROM score ORDER BY Cno, Degree DESC;

  9. 查询 ”95031“ 班的学生人数。

    汇总数据，count、avg、max、min、sum

    1

    SELECT COUNT(*) AS num FROM student WHERE class = '95031';

  10. 查询 Score 表中的最高分的学生学号和课程号。

    子查询

    1
2

    SELECT sno, cno FROM score WHERE 
degree = ( SELECT MAX(degree) FROM score);

  11. 查询’3-105‘ 号课程的平均分。

    AVG字段

    1

    SELECT AVG(degree) AS avgdegree FROM score WHERE Cno = '3-105';

  12. 查询 score 表中至少有5名学生选修的并以3开头的课程的平均分数。

    分组数据：group by、having 过滤

    group by 子句必须出现在 where 子句之后， order by 子句之后

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    select
  avg(DEGREE),
  CNO
from SCORE
where cno like '3%'
group by CNO
having count(*) > 5;

下面的方式是冗余的
SELECT AVG(degree) FROM score WHERE Cno LIKE '3%' AND 
Cno in (SELECT Cno FROM score GROUP BY Cno HAVING COUNT(*) >= 5);

  13. 查询最低分大于70，最高分小于90的sno列。

    1

    SELECT sno, MAX(degree), MIN(degree) FROM score GROUP BY sno HAVING MAX(degree) < 90 AND MIN(degree) > 70;

  14. 查询所有学生的 Sname、Con和Degree列。

    联结：内连接、左外连接、右外连接、全外连接、笛卡儿积

    1
2

    SELECT Sname, Cno, Degree FROM student, score WHERE student.sno = score.sno;
SELECT Sname, Cno, Degree FROM student INNER JOIN score ON student.sno = score.sno;

  15. 查询所有学生的 Sno、Cname和 Degree 列。

    联结多个表数据

    1

    SELECT student.Sno, Cname, Degree FROM student, score, course WHERE student.sno = score.sno AND score.cno = course.cno;

  16. 查询所有学生的Sname、Cname和Degree列。

    1

    SELECT A.SNAME, C.cname, B.degree FROM student AS A INNER JOIN (score AS B, course AS C) ON A.sno = B.sno AND B.cno = C.cno;

  17. 查询“95033”班所选课程的平均分。

    1
2
3
4
5
6
7
8

    SELECT AVG(B.degree) FROM student AS A  INNER JOIN score AS B ON A.sno = B.sno WHERE A.CLASS = '95033';

select avg(DEGREE)
from SCORE
where sno in (select SNO
              from STUDENT
              where CLASS = '95033');

  18. 假设使用如下命令建立了一个grade表:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    create table grade (
  low  numeric(3, 0),
  upp  numeric(3),
  rank char(1)
);
insert into grade values (90, 100, 'A');
insert into grade values (80, 89, 'B');
insert into grade values (70, 79, 'C');
insert into grade values (60, 69, 'D');
insert into grade values (0, 59, 'E');

    现查询所有同学的Sno、Cno和rank列。

    1
2
3
4
5
6
7

    SELECT
  A.SNO,
  A.CNO,
  B.RANK
FROM SCORE A, grade B
WHERE A.DEGREE BETWEEN B.LOW AND B.UPP
ORDER BY RANK;

  19. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

    1
2

    SELECT * FROM score WHERE cno = '3-105' and degree > ALL(SELECT degree FROM score where sno = '109');
SELECT * FROM score WHERE cno = '3-105' and degree > (SELECT MAX(degree) FROM score where sno = '109');

  20. 查询score中选学一门以上课程的同学中分数为非最高分成绩的学生记录

    1
2
3
4
5
6

    select * from STUDENT where SNO
  in (select SNO
  from SCORE
  where DEGREE < (select MAX(DEGREE) from SCORE)
  group by SNO
  having count(*) > 1);

  21. 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录.

    1
2
3
4
5
6
7

    select *
from SCORE
where CNO = '3-105' and DEGREE > ALL (
  select DEGREE
  from SCORE
  where SNO = '109'
);

  22. 查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

    数据处理函数，时间处理

    1

    SELECT Sno, Sname, Sbirthday FROM student WHERE YEAR(Sbirthday) = (SELECT YEAR(Sbirthday) FROM student WHERE sno = 108);

  23. 查询“张旭“教师任课的学生成绩。

    1
2
3
4
5
6
7

    select *
from SCORE
where cno = (
  select CNO
  from COURSE
    inner join TEACHER on COURSE.TNO = TEACHER.TNO and TNAME = '张旭'
);

  24. 查询选修某课程的同学人数多于5人的教师姓名。

    1
2
3

    SELECT tname FROM teacher WHERE tno = (
SELECT tno FROM course WHERE cno = (
SELECT cno FROM score GROUP BY cno HAVING COUNT(sno) > 5));

  25. 查询95033班和95031班全体学生的记录。

    1
2
3

    select *
from STUDENT
where CLASS in ('95033', '95031');

  26. 查询存在有85分以上成绩的课程Cno

    1

    SELECT cno FROM score GROUP BY cno HAVING MAX(degree) > 85;

  27. 查询出“计算机系“教师所教课程的成绩表

    1
2
3

    SELECT * FROM score WHERE cno IN (
SELECT cno FROM course WHERE tno IN (
SELECT tno FROM teacher WHERE depart = '计算机系'));

  28. 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof

    1
2
3
4
5
6
7
8
9

    select
  tname,
  prof
from TEACHER
where depart = '计算机系' and prof not in (
  select prof
  from TEACHER
  where depart = '电子工程系'
);

  29. 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

    1
2
3
4
5

    SELECT cno, sno, degree FROM score
WHERE cno = '3-105' AND degree > any (
	SELECT degree FROM score WHERE cno = '3-245'
)
ORDER BY degree desc;

  30. 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

    1
2
3
4
5

    SELECT cno, sno, degree FROM score
WHERE cno = '3-105' AND degree > ALL (
	SELECT degree FROM score WHERE cno = '3-245'
)
ORDER BY degree desc;

  31. 查询所有教师和同学的name、sex和birthday

    组合查询：union

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    select
  TNAME     name,
  TSEX      sex,
  TBIRTHDAY birthday
from TEACHER
union
select
  sname     name,
  SSEX      sex,
  SBIRTHDAY birthday
from STUDENT;

  32. 查询所有“女”教师和“女”同学的name、sex和birthday

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

    select
  TNAME     name,
  TSEX      sex,
  TBIRTHDAY birthday
from TEACHER
where TSEX = '女'
union
select
  sname     name,
  SSEX      sex,
  SBIRTHDAY birthday
from STUDENT
where SSEX = '女';

  33. 视图

    视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

    对视图的操作和对普通表的操作一样。

    视图具有如下好处：

    • 简化复杂的 SQL 操作，比如复杂的连接；
    • 只使用实际表的一部分数据；
    • 通过只给用户访问视图的权限，保证数据的安全性；
    • 更改数据格式和表示。
    1
2
3
4

    CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;