第二十五篇（使用DQL操作数据）

DQL(Data Query Language) 数据查询语言

先创建五张相关信息的表

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `id` int(11) NOT NULL COMMENT '游戏分类编号',
  `categoryName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '游戏分类名称',
  `pid` int(11) NULL DEFAULT NULL COMMENT '分类的父分类编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `category` VALUES (1, '角色扮演', NULL);
INSERT INTO `category` VALUES (2, 'FPS射击', NULL);
INSERT INTO `category` VALUES (3, '休闲益智', NULL);
INSERT INTO `category` VALUES (4, '系统游戏', NULL);
INSERT INTO `category` VALUES (5, 'DNF', 1);
INSERT INTO `category` VALUES (6, '和平精英', 2);
INSERT INTO `category` VALUES (7, '连连看', 3);



DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `gradeId` int(11) NOT NULL,
  `gradeName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`gradeId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `grade` VALUES (1, '大学一年级');
INSERT INTO `grade` VALUES (2, '大学二年级');
INSERT INTO `grade` VALUES (3, '大学三年级');



DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`  (
  `stuId` int(11) NULL DEFAULT NULL COMMENT '学生编号',
  `subjectId` int(11) NULL DEFAULT NULL COMMENT '课程编号',
  `result` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考试成绩',
  `examDate` datetime(0) NULL DEFAULT NULL COMMENT '考试时间'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `result` VALUES (3, 3, '65', '2019-05-26 10:22:09');
INSERT INTO `result` VALUES (3, 4, '90', '2019-05-26 10:22:18');
INSERT INTO `result` VALUES (1, 1, '86', '2019-05-26 10:22:57');
INSERT INTO `result` VALUES (2, 2, '90', '2019-05-26 10:23:10');



DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `stuId` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `stuName` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
  `stuPwd` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '12321' COMMENT '学生密码',
  `gender` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '男' COMMENT '性别',
  `gradeId` int(11) NULL DEFAULT NULL COMMENT '年级编号',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电话',
  `email` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '地址不详' COMMENT '地址',
  `identityId` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `bornDate` datetime(0) NULL DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`stuId`) USING BTREE,
  UNIQUE INDEX `identityCard`(`identityId`) USING BTREE,
  INDEX `fk_stu_grade`(`gradeId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生表' ROW_FORMAT = Compact;

INSERT INTO `student` VALUES (1, '张三', '123', '男', 1, NULL, NULL, '北京西城', NULL, NULL);
INSERT INTO `student` VALUES (2, '张丰三', '123', '女', 2, NULL, NULL, '北京西城', NULL, NULL);
INSERT INTO `student` VALUES (3, '田七', '123', '男', 4, NULL, NULL, NULL, NULL, NULL);



DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`  (
  `subjectId` int(11) NOT NULL COMMENT '课程编号',
  `subjectName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '课程名称',
  `gradeId` int(11) NULL DEFAULT NULL COMMENT '年级编号',
  PRIMARY KEY (`subjectId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `subject` VALUES (1, '高等数学', 1);
INSERT INTO `subject` VALUES (2, '数据结构设计', 2);
INSERT INTO `subject` VALUES (3, 'MySQL数据库', 3);
INSERT INTO `subject` VALUES (4, 'Java高级特性', 3);

1.掌握MySQL的标准查询语句结构】

SELECT [ALL|DISTINCT]
		{* | 表名.* | 表名.字段名.... [AS 别名]}
	FROM 要查询的表名 [AS 别名]
		[LEFT | RIGHT | INNER JOIN 连接表名 [AS 别名]]
	WHERE 查询条件;

1.1 SELECT后 FROM前的这一块内容，它是用来筛选查询字段的。

简单理解就是你想查看哪些信息 ，例如：我想查看student表中的学生名、学生年龄、学生性别信息
1.查看学生表中的所有信息

SELECT * FROM student;

2.查看学生表中的学生姓名和性别

SELECT stuName,gender FROM student;

1.2 别名(比较常用)

注意：此处使用中文只不过是方便你理解而已 后面不会采用写中文别名。

AS关键字可以省略 ''也可以省略。

SELECT stu.stuName AS '姓名',stu.gradeId '年级' FROM student stu;

1.3 去重 (默认的)ALL所有的、全部 DISTINCT直接的、明显的 (去重)

SELECT ALL stuName,gradeId FROM student;

DISTINCT 用来在指定的查询字段值范围内 去除重复数据

SELECT DISTINCT stuName,gradeId FROM student;

SELECT DISTINCT stuName,gender,gradeId FROM student;

1.4 where查询条件

用于检索数据表中符合条件的记录的。

简单理解：上方的操作是用来筛选列的 where是用来筛选行的。

在where条件语句中 可以由一个或者多个逻辑表达式组成 结果一般为真或假。

<关系/比较运算符和逻辑运算符>

查询年级是大于1的学生信息

SELECT * FROM student WHERE gradeId > 1;

复杂条件的处理：逻辑运算符 与and 或or 非not

查询姓名为张三 且 性别为女的学生信息

SELECT * FROM student WHERE stuName = '张三' AND gender = '女';

查询性别是女的 或者 年级为3的

SELECT * FROM student WHERE gender = '女' OR gradeId = 3;

查询性别不是女的
三种写法都可以

SELECT * FROM student WHERE NOT gender = '女';

SELECT * FROM student WHERE gender <> '女';

SELECT * FROM student WHERE gender != '女';

1.5特殊的比较运算符

IS NULL
IS NOT NULL

查询地址为空的学生姓名 ，查询空千万记住不能使用 =

SELECT stuName FROM student WHERE address IS NULL;

SELECT stuName FROM student WHERE address IS NOT NULL;

BETWEEN… AND 在…之间/范围内 它等价于>= and <=

查询年级在2-3之间的学生姓名

SELECT stuName FROM student WHERE  gradeId >= 2 AND gradeId <= 3;

优化，更加简洁 结果一样

SELECT stuName FROM student WHERE  gradeId BETWEEN 2 AND 3;

IN查询 在…内/里面

查询年级为1或年级为3的学生信息

SELECT * FROM student WHERE gradeId = 1 OR gradeId = 3;

优化，更加简洁

SELECT * FROM student WHERE gradeId IN (1,3);

SELECT * FROM student WHERE gradeId NOT IN (1,3);

2.掌握模糊查询 LIKE 表示像…一样

LIKE属于比较常用的比较运算符，通过它可以实现模糊查询。它有两种通配符：“%”和下划线“_”。

“%”可以匹配一个或多个字符，而“_”只能匹配一个字符。

查询姓为张的学生信息

SELECT * FROM student WHERE stuName LIKE '张%';

查询姓张的两个字的学生信息

SELECT * FROM student WHERE stuName LIKE '张_';

查询名称中带有三的学生信息

SELECT * FROM student WHERE stuName LIKE '%三%';

查询三是姓名第二个字符的学生信息

SELECT * FROM student WHERE stuName LIKE '_三%';

3.理解连接查询原理及掌握连接查询的使用(多表连查)

连接查询分类：

• 内连接查询
• 外连接查询
• 自连接查询

内连接又分：

• 显式内连接
• 隐式内连接

3.1采用显式内连接[推荐给多表起别名 ，区分清楚 防止出现模糊不清错误]。

内连接可以理解为交集。

查询学生的姓名和所在年级

SELECT 
	stu.stuName,g.gradeName
FROM 
	student stu
INNER JOIN grade g 
ON stu.gradeId = g.gradeId; # 关联条件

3.2采用隐式内连接查询 笛卡尔积

SELECT
	stu.stuName,g.gradeName
FROM 
	student stu,grade g

SELECT
	stu.stuName,g.gradeName
FROM 
	student stu,grade g
WHERE
	stu.gradeId = g.gradeId;

查询所有的学生姓名、课程名称、考试成绩

SELECT
	stu.stuName,sub.subjectName,r.result
FROM
	student stu,`subject` sub,result r
WHERE 
	stu.stuId = r.stuId 
	AND 
	sub.subjectId = r.subjectId;

外连接又分：

• 左外连接
• 右外连接

查询学生的姓名和所在年级

3.3.采用左外连接 以左表为主 LEFT JOIN前面的就是左表

SELECT
	stu.stuName,g.gradeName
FROM
	student stu 
LEFT OUTER JOIN grade g
	ON stu.gradeId = g.gradeId;

3.4采用右外连接

SELECT
	stu.stuName,g.gradeName
FROM
	student stu 
RIGHT JOIN grade g
	ON stu.gradeId = g.gradeId;

3.5自连接(内连接)

查询游戏名称和所属分类

SELECT
	c1.categoryName,c2.categoryName
FROM 
	category c1,category c2
WHERE
	c1.pid = c2.id