MySQL 数据库查询 Select语句的使用

牛马__
131 阅读10分钟

DQL查询数据（重点）

4.1、DQL

（Data Query Language:数据库查询语言）

  • 所有的查询操作都用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;
 -- 创建年级表
 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;
 ​
 -- 创建科目表
 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;
 ​
 -- 创建成绩表
 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;
 ​
 ​
 -- 插入学生数据 其余自行添加 这里只添加了2行
 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');
 ​
 -- 插入成绩数据  这里仅插入了一组，其余自行添加
 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);
 ​
 -- 插入年级数据
 INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
 -- 插入科目数据
 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);

4.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*2-1 AS 计算结果;     -- 用来计算（表达式）
 SELECT @@auto_increment_increment;  -- 查询自增步长（变量）
 ​
 -- 学员考试成绩+1分查看
 SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result;

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量。。。。

select 表达式 from 表

4.3、where子句

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成！结果布尔值

逻辑运算符

运算符语法描述
and &&a and b a&&b逻辑与，两个都为真，结果为真
ora or b ab逻辑或，其中一个为真，结果为真
not !not a !a逻辑非，真为假，假为真

尽量只用英文字母

 SELECT `studentno`,`studentresult`FROM result;
 ​
 -- 查询考试成绩在 95-100分之间
 SELECT `studentno`,`studentresult`FROM result WHERE `studentresult`>=95 AND `studentresult`<=100;
 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;
 SELECT `studentno`,`studentresult`FROM result WHERE NOT `studentno`=1000;

模糊查询：比较运算符

运算符语法描述
IS NULLa is null如果操作符为NULL，结果为真
IS NOT NULLa is not null如果操作符不为NULL，结果为真
BETWEENa between b and c若a在b和c之间，则结果为真
LIKEa like bSQL匹配，如果a匹配b，则结果为真
INa in(a1,a2,a3...)假设a在a1，或者a2...其中一个值中，结果为真
 -- 查寻姓赵的同学
 -- 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 '%嘉%';
 ​
 -- =========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 `address` IS NOT NULL;
 ​
 -- 查询没有出生日期的同学  不为空
 SELECT `studentno`,`studentname` FROM `student`
 WHERE `address` IS NULL;

4.4、联表查询

JOIN

image-20230130153315094

image-20230130153407150

 ​
 -- 查询参加了考试的同学
 SELECT * FROM student
 SELECT * FROM result;
 /*
 思路：
 1. 分析需求，分析查询的字段来自那些表（连接查询）
 2. 确定那种连接查询？  7种
 确定交叉点（这两个表中那个数据是相同的）
 判断条件：学生表中 studentno =成绩表studentno
 */
 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;
 ​
操作描述
Inner join如果表中至少有一个匹配，就返回行
Left join会从左表中返回所有的值，即使右表中没有匹配
Right join会从右表中返回所有的值，即使左表中没有匹配
 -- join on(判断的条件） 连接查询
 -- where      等值查询
 ​
 -- 思考题（查询了参加考试的同学信息：学号，姓名，科目名，分数）
 /*
 思路：
 1. 分析需求，分析查询的字段来自那些表 student，result，subject（连接查询）
 2. 确定那种连接查询？  7种
 确定交叉点（这两个表中那个数据是相同的）
 判断条件：学生表中 studentno =成绩表studentno
 */
 SELECT s.studentno,studentname,subjectname,studentresult
 FROM student s
 RIGHT JOIN result r
 ON r.`studentno`=s.`studentno`
 INNER JOIN `subject` sub
 ON r.subjectno=sub.subjectno;
 ​
 -- 我要查询哪些数据 select 。。。
 -- 从哪几个表查from 表 XXX Join 连接的表 on 交叉条件
 -- 假设存在一种多张表表查询，慢慢来，先查询两张表然后慢慢增加

自联接

自己和自己的表建立连接， 一张表侪分为两张一样的表即可

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

父类

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

子类

pidcategoryidcateoryname
34数据库
28办公信息
36web开发
57ps技术

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

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

4.5、分页和排序

排序

 -- ==========分页limit和排序order by==============
 -- 排序 ： 升序 ASC ，降序DESC
 -- 查询的结果根据成绩降序 排序
 SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
 FROM student s
 INNER JOIN `result` r
 ON s.studentno=r.studentno
 INNER JOIN`subject` sub
 ON r.`subjectno`=sub.`subjectno`
 WHERE subjectname = '数据库结构-1'
 ORDER BY studentresult ASC;

分页

 -- 为什么分页
 -- 缓解数据库的压力，更好的体验，瀑布流
 -- 语法： Limit 起始值，页面的大小
 -- 网页应用 ： 当前，总的页数，页面的大小
 -- LIMIT 0,5    1-5
 -- LIMIT 1,5    2-6
 SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
 FROM student s
 INNER JOIN `result` r
 ON s.studentno=r.studentno
 INNER JOIN`subject` sub
 ON r.`subjectno`=sub.`subjectno`
 WHERE subjectname = '数据库结构-1'
 ORDER BY studentresult ASC
 LIMIT 0,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）

4.6、子查询

where（这个值是计算出来的）

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

where( select*from )

 -- ============where==========
 -- 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;
 ​
 -- 方式二：使用子查询（由里及外）
 SELECT `studentno`,`subjectno`,`studentresult`
 FROM `result`
 WHERE subjectno = (
     SELECT subjectno FROM `subject`
     WHERE subjectname='数据库结构-1'
 );
 ​
 -- 查询所有数据库结构-1 的学生学号
 SELECT subjectno FROM `subject`WHERE subjectname='数据库结构-1';
 ​
 -- 分数不小于80分的学生的学号和姓名
 SELECT DISTINCT s.`studentno`,`studentname`
 FROM student s
 INNER JOIN result r
 ON r.studentno = s.studentno
 WHERE `studentresult`>=80;
 ​
 -- 在这个基础上增加一个科目，高等数学-2
 -- 查询高等数学-2 的编号
 SELECT DISTINCT s.`studentno`,`studentname`
 FROM student s
 INNER JOIN result r
 ON r.studentno = s.studentno
 WHERE `studentresult`>=80 AND `subjectno` = (
     SELECT subjectno FROM `subject`
     WHERE `subjectname`='高等数学-2'
 );
 ​
 -- 再改造
 SELECT studentno,studentname FROM student WHERE studentno IN(
     SELECT studentno FROM result WHERE studentresult>80 AND subjectno = (
         SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-2'
     )
 )

4.7、分组和过滤

 -- 查询不同课程的平均分，最高分，最低分
 -- 核心： （根据不同的课程分组）
 SELECT subjectname,AVG(`studentresult`) 平均分,MAX(`studentresult`) 最高分,MIN(`studentresult`) 最低分
 FROM result r
 INNER JOIN `subject` sub
 ON r.`subjectno`=r.`subjectno`
 GROUP BY r.`subjectno`
 HAVING 平均分>80;

4.8、Select小结

 顺序
 select 去重 要查询的字段 from 表 （表和字段可以取别名）
 xxx join 要连接的表 on 等值判断
 where （具体的值，子查询语句）
 group by（通过那个字段来分组）
 having （过滤分组后的信息，条件和where是一样的，位置不同）
 order by ..（通过那个字段排序）[升序/降序]
 limit startindex，pagesize
avatar
文章
阅读
粉丝