DQL（数据查询语言）

MySQL数据库操作与查询实践

原创已于 2022-06-13 10:44:32 修改 · 635 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #database

于 2022-06-13 10:01:36 首次发布

select简单查询

建一个数据库和表

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
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;

INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1002,'123456','李白',2,4,'13800002552','广东广州','1991-1-1','text158@qq.com','123456199001011288'),
(1003,'123456','王源',3,5,'13800562222','四川成都','1992-1-1','text458@qq.com','123456188001011233'),
(1004,'123456','易烊千玺',4,6,'13812342222','湖南长沙','1993-1-1','text456@qq.com','123456199001011238'),
(1005,'123456','刘昊然',5,7,'13898752222','河南平顶山','1994-1-1','text185@qq.com','123456199771011233'),
(1006,'123456','王俊凯',6,8,'13855552222','浙江杭州','1995-1-1','text11178@qq.com','123456199262611233'),
(1007,'123456','杨紫',7,9,'13800006666','福建厦门','1996-1-1','text333@qq.com','123456148561011233');

-- 创建年级表
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 AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

INSERT INTO `grade` (`gradeid`,`gradename`) 
VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');


-- 创建科目表
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 AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

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


-- 创建成绩表
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;

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

-- 查询全部的内容 SELECT 字段 FROM 表
SELECT * FROM student

SELECT * FROM result

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 给查出的结果一个指定名字
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student

有时候，列名字不是那么见名知意，我们可以给字段名起一个别名显示出来

-- 函数 Concat（a,b）
SELECT CONCAT('姓名：',studentname)AS 新名字 FROM student

SELECT `studentno` FROM `result` -- 查询一下哪些同学参加了考试

-- 发现重复数据去重 distinct关键字

作用：去除查询出来的数据中重复的数据，只显示一条
SELECT DISTINCT `studentno` FROM `result`

SELECT VERSION() -- 查询系统版本
SELECT 100*3-5 AS 计算结果 -- 用来计算数值
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学生考试成绩
SELECT `studentno`,`studentresult` FROM `result`
-- 学生考试成绩全部+1
SELECT `studentno`,`studentresult`+1 AS 全部增加一分 FROM `result`

where条件子句

-- 查询考试成绩在95到100之间的  
-- 可以使用and 、&& 、 或者 between and
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=80 AND `studentresult`<=100

SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`BETWEEN 82 AND 100

-- 除了1000号学生的成绩
-- 使用!=或者not
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000

SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno` =1000

模糊查询like

like结合 %（代表0到任意个字符）或者_（代表一个字符）

-- 查询姓刘的同学
-- like结合 %（代表0到任意个字符）或者_（代表一个字符）
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '王%'

-- 模糊查询
-- like结合 %（代表0到任意个字符）或者_（代表一个字符）
-- 查询姓王的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '王%'
-- 查询姓王的同学，后面还有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '王_'
-- 查询姓王的同学，后面还有两个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '王__'
-- 查询名字中间有昊字的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%昊%'
-- 查询1000,1001号的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(1000,1001)
-- 查询在四川成都的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('四川成都')
-- 查询地址为空的学生  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

联表查询

-- 查询参加了考试的同学的学号、姓名、科目编号和分数
/*思路
1、分析需求，分析查询的字段来自哪些表（连接查询）
2、确定使用哪种连接查询
确定交叉点（这两个表中哪些数据是相同的）
判断的条件：学生表中的studentno=成绩表中的studentno

*/

-- Inner join
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` AS s
RIGHT JOIN `result` AS r
ON s.`studentno`=r.`studentno`

-- left join 查询出所有的左表的相关内容，即使右表没有匹配的内容
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`


-- 查询缺考的同学
-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE `studentresult` IS NULL

-- 查询参加了考试的同学的学号、姓名、科目名和分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`



-- 要查询的数据  select...
-- 从哪几个表中查询 from 表... join 表... on  交叉的条件（相同的属性）
-- 假设存在一种多表查询，先查询两张表，再慢慢增加

-- 查询学生所属的年级（学号，学生的姓名，年级名称）
SELECT `studentno`,`studentname`,`gradename`
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.`gradeid`=g.`gradeid`

-- 查询科目所属的年级
SELECT `subjectname`,`gradename`
FROM `subject` AS sub
INNER JOIN `grade` AS g
ON sub.`gradeid`=g.`gradeid`

-- 查询参加高等数学-1考试的同学的学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

拆成两张表

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息,把一张表看成两个一模一样的表

SELECT a.`categoryname` AS '父类',b.`categoryname` AS '子类'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

分页limit和排序order by

排序

-- 按成绩升序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` ASC
-- 按学号降序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentno` DESC

分页 limit 起始的数据一页显示的数据量

limit 5,6 当前页从第5条数据开始，显示6条

当数据量很大的时候，可以缓解数据库压力，给人的体验更好

第一页 limit 0,5

第二页 limit 5,5

第三页 limit 10,5

第n页 limit (n-1)*5,5 (n-1)*pagesize,pagesize

-- 查询Java第一学年 课程成绩排名前10的学生，并且分数要大于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`='ava第一学年' AND `studentresult`>=80
ORDER BY `studentresult` DESC
LIMIT 0,10

子查询

在where语句中嵌套一个子查询语句

where（select * from table）

-- 查询 高等数学-1 的所有考试结果（学号，科目编号，成绩），降序排列

-- 方式1   使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` DESC

-- 方法2 使用子查询
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
SELECT `subjectno` FROM `subject` 
WHERE `subjectname`='高等数学-1')
ORDER BY `studentresult` DESC


-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80


-- 高等数学-1分数不小于80分的学生的学号和姓名
-- 子查询方式
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80 AND `subjectno`=(
SELECT `subjectno` FROM `subject` 
WHERE `subjectname`='高等数学-1'
)

-- 高等数学-1分数不小于80分的学生的学号和姓名
-- Inner join

SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1' AND `studentresult`>=80

-- 高等数学-1分数不小于80分的学生的学号和姓名
-- 嵌套两次的子查询
SELECT `studentno`,`studentname` 
FROM `student` WHERE `studentno` IN(
  SELECT `studentno` FROM `result` 
  WHERE `studentresult`>=80 AND `subjectno`=(
    SELECT `subjectno` FROM `subject`
    WHERE `subjectname`='高等数学-1'
  )
)

分组

-- 查询不同课程的平均分，最高分和最低分，并且平均分大于80
-- 根据不同的课程进行分组
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`)AS 最大值,MIN(`studentresult`) AS最小值
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno`  -- 通过什么字段来分组
HAVING 平均分>=80   -- 分组筛选的条件使用having而不是where