DQL查询数据！
1.DQL( Data Query Language 数据查询语言 )
-
SELECT
-
数据库中最核心的语言，最重要的语句
狂神MySQL中p16 school.sql文件
（数据为部分数据）
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 5.7.33 : Database - school
*********************************************************************
*/
/*!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 */`school` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `school`;
/*Table structure for table `grade` */
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`
(
`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`GradeID`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
/*Data for the table `grade` */
INSERT INTO `grade`(`GradeID`, `GradeName`)
VALUES (1, '大一'),
(2, '大二'),
(3, '大三'),
(4, '大四'),
(5, '预科班');
/*Table structure for table `result` */
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`
(
`StudentNo` INT(4) NOT NULL COMMENT '学号',
`SubjectNo` INT(4) NOT NULL COMMENT '课程编号',
`ExamDate` DATETIME NOT NULL COMMENT '考试日期',
`StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
KEY `SubjectNo` (`SubjectNo`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
/*Data for the table `result` */
INSERT INTO `result`(`StudentNo`, `SubjectNo`, `ExamDate`, `StudentResult`)
VALUES (1000, 1, '2013-11-11 16:00:00', 85),
(1000, 2, '2013-11-12 16:00:00', 70),
(1000, 3, '2013-11-11 09:00:00', 68),
(1000, 4, '2013-11-13 16:00:00', 98),
(1000, 5, '2013-11-14 16:00:00', 58);
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
`StudentNo` INT(4) NOT NULL COMMENT '学号',
`LoginPwd` VARCHAR(20) DEFAULT NULL,
`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`GradeId` INT(11) DEFAULT NULL COMMENT '年级编号',
`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`Address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号允许为空',
`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`StudentNo`),
UNIQUE KEY `IdentityCard` (`IdentityCard`),
KEY `Email` (`Email`)
) ENGINE = MYISAM DEFAULT CHARSET = utf8;
/*Data for the table `student` */
INSERT INTO `student`(`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`,
`IdentityCard`)
VALUES (1000, '123456', '张伟', 0, 2, '13800001234', '北京朝阳', '1980-01-01 00:00:00', '[email protected]',
'123456198001011234'),
(1001, '123456', '赵强', 1, 3, '13800002222', '广东深圳', '1990-01-01 00:00:00', '[email protected]',
'123456199001011233');
/*Table structure for table `subject` */
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`
(
`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
`GradeID` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`SubjectNo`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
/*Data for the table `subject` */
INSERT INTO `subject`(`SubjectNo`, `SubjectName`, `ClassHour`, `GradeID`)
VALUES (1, '高等数学-1', 110, 1),
(2, '高等数学-2', 110, 2),
(3, '高等数学-3', 100, 3),
(4, '高等数学-4', 130, 4),
(5, 'C语言-1', 110, 1),
(6, 'C语言-2', 110, 2),
(7, 'C语言-3', 100, 3),
(8, 'C语言-4', 130, 4),
(9, 'Java程序设计-1', 110, 1),
(10, 'Java程序设计-2', 110, 2),
(11, 'Java程序设计-3', 100, 3),
(12, 'Java程序设计-4', 130, 4),
(13, '数据库结构-1', 110, 1),
(14, '数据库结构-2', 110, 2),
(15, '数据库结构-3', 100, 3),
(16, '数据库结构-4', 130, 4),
(17, 'C#基础', 130, 1);
/*!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 */;
select完整语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
注意：[ ]括号代表可选 ，{ }括号代表必选
2.指定查询字段
-- 查询所有学生 SELECT 字段 FROM 表
SELECT * FROM student;
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student;
-- 别名，AS 给字段、表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s;
-- 函数 Concat（a,b）
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student;
语法：SELECT 字段 .... FROM 表
别名 AS
字段名 AS 别名表名 AS 别名
去重 distinct
作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条
-- 查询以下哪些同学参加了考试，成绩
SELECT * FROM result; -- 查询全部的考试成绩
SELECT `StudentNo` FROM result; -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result; -- 发现重复数据，去重
数据库的列(表达式)
SELECT VERSION(); -- 查询系统版本 函数
SELECT 100*3-1 AS 计算结果; -- 用来计算 表达式
SELECT @@auto_increment_increment; -- 查询自增的步长 变量
-- 学员考试成绩 + 1分查看
SELECT `StudentNo`,`StudentResult`+1 AS `提分后` FROM result;
数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量......
select 表达式 from 表
3.where条件子句
作用：检索数据中符合条件的值
条件，一个或多个表达式组成！
逻辑运算符
-- ========where=======
SELECT `StudentNo`,`StudentResult` FROM result;
-- 查询考试成绩在95~100分之间
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- and &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentResult>=95 && StudentResult<=100;
-- 模糊查询（区间）
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100;
-- 除了1000号学生之外的同学的成绩
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentNo!=1000;
-- != not
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT StudentNo=1000;
模糊查询：比较运算符
-- ======模糊查询======
-- 查询姓刘的同学
-- like结合 %（代表0到任意个字符） _ （一个字符）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%';
-- 查询姓刘的同学,名字后面只有一个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_';
-- 查询姓刘的同学,名字后面只有2个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__';
-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';
-- ===== in(具体的一个或多个值) =====
-- 查询1001，1002，1003号学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询在北京朝阳的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE Address IN ('北京朝阳');
-- =====null not null=====
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE Address='' OR Address IS NULL;
-- 查询有出生日期的同学 不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE BornDate IS NOT NULL;
-- 查询没有出生日期的同学 为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE BornDate IS NULL;
4.联表查询
-- ======联表查询 Join======
-- 查询参加了考试的同学(学号，姓名，科目编号，分数)
/*思路
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表 StudentNo= 成绩表 StudentNo
*/
-- join(连接的表) on(判断的条件) 连接查询
-- where 等值查询
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM student AS s
INNER JOIN result AS r
WHERE s.StudentNo=r.StudentNo;
-- Right Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo=r.StudentNo;
-- Left Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo=r.StudentNo;
-- 查询缺考的同学
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo=r.StudentNo
WHERE StudentResult IS NULL;
| 操作 | 描述 |
|---|---|
| Inner join | 如果表中至少有一个匹配，就返回行 |
| Right join | 会从右表中返回所有的值，即使左表中没有匹配 |
| Left join | 会从左表中返回所有的值，即使右表中没有匹配 |
-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
SELECT s.StudentNo,StudentName,SubjectName,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo;
自连接(了解)
自己的表和自己的表连接，核心：一张表拆为两张一样的表的即可
/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/
-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
-- 查询父子信息:把一张表看为两个一摸一样的表
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
5.分页和排序
排序
-- 排序：升序 ASC ，降序 DESC
-- ORDER BY 通过那个字段排序，怎么排
-- 查询的结果根据 成绩降序 排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC;
分页
-- 100万
-- 分页，每页只显示五条数据
-- 语法：limit起始值，页面的大小
-- 网页应用：当前，总的页数，页面的大小
-- LIMIT 0,5 1~5
-- LIMIT 1,5 2~6
-- LIMIT 6,5
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC
LIMIT 5,5
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第N页 limit 0,5 (n-1)*pageSize,pageSize
-- 【pageSize：页面大小】
-- 【(n-1)*pageSize：起始值】
-- 【n:当前页】
-- 【数据总数/页面大小 = 总页数】
语法：limit 查询起始下标，pageSize
思考：
-- 思考
-- 查询 Java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo`=r.`SubjectNo`
WHERE SubjectName='Java第一学年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10;