一、首先解决数据库内部中文是问号问题
查询数据库字符集
mysql>SHOW VARIABLES LIKE 'character_set_%';//查看数据库字符集
进入Mysql输入以下
mysql>SET NAMES 'utf8';
这条语句相当于以下三条语句
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
之后进入docker下的mysql文件目录
docker exec -it [dockerID] bash
cd /etc/mysql
vim my.cnf
如果没vim 用 apt-get update 之后下载vim
然后你发现vim 编辑器可以使用以后,在此文件中添加如下字段
在 [mysqld] 标签下加上三行
default-character-set = utf8
character_set_server = utf8
在 [mysql] 标签下加上一行
default-character-set = utf8
在 [mysql.server]标签下加上一行
default-character-set = utf8
在 [mysqld_safe]标签下加上一行
default-character-set = utf8
在 [client]标签下加上一行
default-character-set = utf8
二、建立表
1、教师表 T
2、学生表 S
3、课程表 C
4、选课关系 SC
5、授课关系 TC
PS:数据库SQL代码
/*
Navicat Premium Data Transfer
Source Server : DuCloud
Source Server Type : MySQL
Source Server Version : 80011
Source Host : 182.61.54.209:3302
Source Schema : school
Target Server Type : MySQL
Target Server Version : 80011
File Encoding : 65001
Date: 28/10/2019 19:43:38
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for C
-- ----------------------------
DROP TABLE IF EXISTS `C`;
CREATE TABLE `C` (
`CNO` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
`CN` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`CT` int(3) NOT NULL,
PRIMARY KEY (`CNO`),
KEY `CNO` (`CNO`),
KEY `CNO_2` (`CNO`),
KEY `CNO_3` (`CNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of C
-- ----------------------------
BEGIN;
INSERT INTO `C` VALUES ('C1', '程序设计', 60);
INSERT INTO `C` VALUES ('C2', '微机原理', 80);
INSERT INTO `C` VALUES ('C3', '数字逻辑', 60);
INSERT INTO `C` VALUES ('C4', '数据结构', 80);
INSERT INTO `C` VALUES ('C5', '数据库', 60);
INSERT INTO `C` VALUES ('C6', '编译原理', 60);
INSERT INTO `C` VALUES ('C7', '操作系统', 60);
COMMIT;
-- ----------------------------
-- Table structure for S
-- ----------------------------
DROP TABLE IF EXISTS `S`;
CREATE TABLE `S` (
`SNO` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
`SN` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`Sex` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
`Age` int(3) NOT NULL,
`Dept` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`SNO`),
KEY `SNO` (`SNO`),
KEY `SNO_2` (`SNO`),
KEY `SNO_3` (`SNO`),
KEY `SNO_4` (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of S
-- ----------------------------
BEGIN;
INSERT INTO `S` VALUES ('S1', '赵毅', '女', 17, '计算机');
INSERT INTO `S` VALUES ('S2', '钱尓', '男', 18, '信息');
INSERT INTO `S` VALUES ('S3', '孙珊', '女 ', 20, '信息');
INSERT INTO `S` VALUES ('S4', '李思', '男', 21, '自动化');
INSERT INTO `S` VALUES ('S5', '周武', '男', 19, '计算机');
INSERT INTO `S` VALUES ('S6', '吴丽', '女 ', 20, '自动化');
COMMIT;
-- ----------------------------
-- Table structure for SC
-- ----------------------------
DROP TABLE IF EXISTS `SC`;
CREATE TABLE `SC` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`SNO` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
`CNO` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
`Score` double(255,0) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of SC
-- ----------------------------
BEGIN;
INSERT INTO `SC` VALUES (1, 'S1', 'C1', 90);
INSERT INTO `SC` VALUES (2, 'S1', 'C2', 85);
INSERT INTO `SC` VALUES (3, 'S2', 'C5', 57);
INSERT INTO `SC` VALUES (4, 'S2', 'C6', 80);
INSERT INTO `SC` VALUES (5, 'S2', 'C7', NULL);
INSERT INTO `SC` VALUES (6, 'S2', 'C4', 70);
INSERT INTO `SC` VALUES (7, 'S3', 'C1', 75);
INSERT INTO `SC` VALUES (8, 'S3', 'C2', 70);
INSERT INTO `SC` VALUES (9, 'S3', 'C4', 85);
INSERT INTO `SC` VALUES (10, 'S4', 'C1', 93);
INSERT INTO `SC` VALUES (11, 'S4', 'C2', 85);
INSERT INTO `SC` VALUES (12, 'S4', 'C3', 83);
INSERT INTO `SC` VALUES (13, 'S5', 'C2', 89);
COMMIT;
-- ----------------------------
-- Table structure for T
-- ----------------------------
DROP TABLE IF EXISTS `T`;
CREATE TABLE `T` (
`TNO` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
`TN` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`Sex` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
`Age` int(3) NOT NULL,
`Prof` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
`Sal` double NOT NULL,
`Comm` double NOT NULL,
`Dept` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`TNO`),
KEY `TNO` (`TNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of T
-- ----------------------------
BEGIN;
INSERT INTO `T` VALUES ('T1', '李力', '男', 47, '教授', 1500, 3000, '计算机');
INSERT INTO `T` VALUES ('T2', '王平', '女', 28, '讲师', 800, 1200, '信息');
INSERT INTO `T` VALUES ('T3', '刘伟', '男', 30, '讲师', 900, 1200, '计算机');
INSERT INTO `T` VALUES ('T4', '张雪', '女', 51, '教授', 1600, 3000, '自动化');
INSERT INTO `T` VALUES ('T5', '张兰', '女', 39, '副教授', 1300, 2000, '信息');
COMMIT;
-- ----------------------------
-- Table structure for TC
-- ----------------------------
DROP TABLE IF EXISTS `TC`;
CREATE TABLE `TC` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`TNO` varchar(4) COLLATE utf8mb4_general_ci NOT NULL,
`CNO` varchar(4) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `TC_@` (`CNO`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of TC
-- ----------------------------
BEGIN;
INSERT INTO `TC` VALUES (1, 'T1', 'C1');
INSERT INTO `TC` VALUES (2, 'T1', 'C4');
INSERT INTO `TC` VALUES (3, 'T2', 'C6');
INSERT INTO `TC` VALUES (4, 'T2', 'C5');
INSERT INTO `TC` VALUES (5, 'T3', 'C1');
INSERT INTO `TC` VALUES (6, 'T3', 'C5');
INSERT INTO `TC` VALUES (7, 'T4', 'C2');
INSERT INTO `TC` VALUES (8, 'T4', 'C3');
INSERT INTO `TC` VALUES (9, 'T5', 'C5');
INSERT INTO `TC` VALUES (10, 'T5', 'C7');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
三、基本语句练习
1、查询教师关系中有哪些系(投影)
∏Dept(T)
select Dept from T;
2、查询讲授C5课程课程的教师号(投影)
∏TNO(σCNO=‘C5’(TC))
select TNO from TC where CNO='C5';
3、查询讲授‘数据库’课程的教师姓名(θ连接)
∏TN(σCN=‘数据库’©⋈TC⋈∏TNO,TN(T))
select TN from T where
TNO = ANY(select TNO from TC where
CNO= ALL(select CNO from C where
CN='数据库'));
4、查询选修了全部课程的学生学号和姓名(除法)
∏SNO,CNO(SC)÷∏CNO©⋈∏SNO,SN(S)
***@查询(比较大小、多重条件查询、确定范围、确定集合、部分匹配查询)
1、查询选修课程号为C1的学生的学号和成绩
select SNO,Score from SC where CNO='C1';
2、查询成绩高于85分的学生的学号,课程号和成绩
select SNO,CNO,Score from SC where Score>85;
3、查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩。
select SNO,CNO,Score from SC where
(CNO='C1'||CNO='C2') and
Score>=85;
4、查询工资在1000元~1500元之间的教师的教师号、姓名及职称。
select TNO,TN,prof from T where
Sal BETWEEN
1000 and 1500;
5、查询工资不在1000~1500元之间的教师的教师号、姓名及职称。
select TNO,TN,prof from T where
Sal NOT BETWEEN
1000 and 1500;
6、查询选修C1或C2的学生的学号、课程号和成绩。
select SNO,CNO,Score from SC where
CNO IN ('C1','C2');
等价于
select SNO,CNO,Score from SC where
CNO='C1' or CNO='C2';
7、查询没有选修C1,也没有选修C2的学生的学号、课程号和成绩。
select SNO,CNO,Score from SC where
CNO NOT IN ('C1','C2');
等价于
select SNO,CNO,Score from SC where
CNO <> 'C1' and CNO <> 'C2';
@部分匹配查询
通配符 | 功能 | 实例 |
---|---|---|
% | 代表0或者多个字符 | ‘ab%’,'ab’后可接任意字符串 |
_(下划线) | 代表一个字符 | ‘a_b’,'a’和’b’指尖可以有一个字符 |
[ ] | 表示在某一范围的字符 | [ 0-9],0~9之间的字符 |
[^ ] | 表示不在某一范围的字符 | [^0-9] ,不在0~9指尖的字符 |
8、查询所有姓张的教师的教师号和姓名。
select TNO,TN from T where TN LIKE '张%';
9、查询姓名中第二个汉子是‘力’ 的教师号和姓名。
select TNO,TN from T where TN LIKE '_力%';
!!! PS: 以上查询因为SC表分数之前录入有问题导致分数错误,现在重新录入已经更新了数据库的插入语句,但是上面的查询结果没有修改,如果有不一样的地方不是语句错了,只是表里信息不一样!!!
10、查询没有考试成绩的学生的学号和相对应的课程号。
select SNO,CNO from SC where Score is null;
***@常用库函数及统计汇总查询
函数名称 | 功能 |
---|---|
AVG | 按列计算平均值 |
SUM | 按列计算值的总和 |
MAX | 求一列中最大值 |
MIN | 求一列中最小值 |
COUNT | 按列值统计个数 |
1、求学号为S1的学生的总分和平均分
select SUM(Score) 总分,AVG(Score) 平均分 from SC where SNO='S1';
2、求选修C1号课程的最高分、最低分及之间相差的分数。
select MAX(Score) 最高分,
MIN(Score) 最低分 ,
MAX(Score)-MIN(Score) 相差分数 from SC where CNO='C1';
3、求计算机系学生总数。
select count(SNO) from S where Dept = '计算机';
4、求学校中共有多少个系。
DISTINCT表示消去重复行,可计算字段DEPT不同值的数目。COUNT函数对NULL值不进行计算,但对0进行计算。
select count(DISTINCT Dept) from S ;
5、统计有成绩的同学的个数。
select count(Score) 个数 from SC ;
6、利用特殊函数COUNT(*) 求计算机系学生的总数。
COUNT(*) 用来统计元组个数,不消除重复行,不允许使用DISTINCT关键字
select count(*) 个数 from S where Dept='计算机' ;
***@分组查询
1、查询每个教师的教师号及其任课门数。
select TNO,COUNT(*) 任课门数 from TC GROUP BY TNO;
2、查询选修两门以上(含两门)课程的学生的学号和选课门数。
select SNO,COUNT(*) FROM SC GROUP BY SNO HAVING (COUNT(*)>=2);
GROUP BY 子句按SNO的值进行分组,所有具有相同SNO的元组为一组,对每个组使用函数COUNT()进行计算,统计出每个学生选课的门数。
HAVING子句去掉不满足COUNT()>=2 的组。
当一个SQL查询同时含有 WHERE、GROUP BY、HAVING子句时,执行顺序为 WHERE , GROUP BY , HAVING。
WHERE与HAVING子句不同在于作用对象不同,一个作用于基本表或者视图,从中选择满足条件的元组;HAVING则作用于组,选择满足条件的组,必须作用于GROUP BY 子句之后。
*** @ 查询结果的排序(ORDER BY |DESC\ASC)
1、查询选修C1的学生学号和成绩,并按成绩降序排列。
select SNO,Score from SC where CNO='C1' ORDER BY Score DESC;
2、查询选修C2、C3、C4或C5课程的学号、课程号和成绩,查询结构按学号升序排列,学号相同再按成绩降序排列
select SNO,CNO,Score from SC where
CNO IN ('C2','C3','C4','C5') ORDER BY
SNO ASC ,Score DESC;
ASC 可以省略。
***@多关系表的连接查询
表之间满足一定条件的行进行连接时,FROM子句指明进行连接的表名,WHERE子句指明连接的列名以及连接条件。
利用关键字JOIN进行连接
INNER JOIN 内连接 显示符合条件的记录,此为默认值。
LEFT JOIN 左外连接 用于显示符合条件的数据行以及左边表中不符合条件的数据行,此时右边的表数据行会以NULL来显示。
RIGHT JOIN 右外连接 用于显示符合条件的数据行以及右边表中不符合条件的数据行,此时左边的表数据行会以NULL来显示。
FULL JOIN 显示符合条件的数据行以及左边表和右边表中不符合条件的数据行,此时缺乏数据的数据行会以NULL来显示
CROSS JOIN 笛卡尔积 将一个表的每个记录和另一个表的每个记录匹配成新的数据行
当JOIN 关键词放于FROM子句时,应有关键词ON与之对应,以表明连接的条件。
FROM 表1 INNER JOIN 表2 ON 条件
1、查询“刘伟”老师所讲授的课程,要求列出教师号、教师姓名和课程号。
方法一:
select T.TNO,T.TN,TC.CNO from T,TC where T.TN="刘伟" and T.TNO=TC.TNO;
方法二:
select T.TNO,TN,CNO from T INNER JOIN TC on T.TNO=TC.TNO where TN='刘伟';
2、查询所有选课学生的学号、姓名、选课名称以及成绩。
select S.SNO,S.SN,C.CN,SC.Score FROM S,C,SC where
S.SNO=SC.SNO and SC.CNO=C.CNO;
3、查询每门课程的课程号、课程名和选课人数。
select C.CNO,C.CN,COUNT(SC.SNO) 选课人数 from
C,SC where
SC.CNO=C.CNO GROUP BY
C.CNO,CN;
4、查询所有学生的学号、姓名、选课名称以及成绩(没有选课的同学的选课信息显示为空)
select SC.SNO,S.SN,C.CN,SC.Score from S LEFT JOIN SC ON
S.SNO=SC.SNO LEFT JOIN C ON SC.CNO=C.CNO;
5、对学生表S和课程表C进行交叉查询
select * from S CROSS JOIN C;
6、查询所有比"刘伟" 工资高的教师姓名、工资、和刘伟的工资。
select X.TN,X.Sal,Y.Sal from T as X ,T as Y where X.Sal>Y.Sal and Y.TN='刘伟';
7、检索所有学生姓名、年龄和选课名称
# 可检索空
select S.SN,S.Age,C.CN from S LEFT JOIN SC ON S.SNO=SC.SNO LEFT JOIN C ON SC.CNO=C.CNO;
# 省略空
select S.SN,S.Age,C.CN from S,SC,C where S.SNO=SC.SNO AND SC.CNO=C.CNO;
***@子查询(普通子查询、相关子查询)
1、查询与“刘伟”老师职称相同的教师号、姓名。
select TNO,TN from T where Prof=(select Prof from T where TN="刘伟");
2、查询讲授课程号为C5的教师姓名。
一般解法
select T.TN from T,TC where T.TNO=TC.TNO and TC.CNO='C5';
使用ANY
select TN from T where TNO=ANY(select TNO from TC where CNO='C5');
3、查询其他系中比计算机系某一教师工资高的教师姓名和工资。
select T.TN,T.Sal from T where
T.Sal>(Select MIN(Sal) from T where T.Dept='计算机' )
and T.Dept<>'计算机';
4、查询讲授课程号为C5的教师姓名(使用IN)
select T.TN from T where
T.TNO IN(select TC.TNO from TC where TC.CNO='C5');