一、初识MySQL
1.1 前言
- JavaEE主要应用于企业级Java开发,即Web领域,且Web又可以分为:
- 前端:页面数据展示
- 后端:连接数据库 (JDBC, MyBitis) (获取数据)、连接前端 (select, springMVC) (控制视图的跳转、给前端传递数据)
- 数据库:存储数据
- 程序员进阶之路
- 初级:简单代码、数据库基本的增删改查
- 中级:操作系统、数据结构与算法
- 高级:离散数学、数字电路、体系结构、编译原理、实战经验
1.2 为什么要学习数据库
- 岗位需求
- 大数据时代
- 被迫需求:存数据
- 数据库是所有软件体系中最核心的存在
1.3 什么是数据库
- BataBase (DB)
- 概念:数据仓库、软件、安装在操作系统 (windows, linux, mac)之上、SQL、可以存储大量的数据 (500万左右)
- 作用:存储数据、管理数据
1.4 数据库分类
- 关系型数据库(Sql):
- MySQL、Oracle、Sql Server、DB2、SQLlite
- 通过表和表之间,行和列之间的关系进行数据的存储。
- 非关系型数据库(NoSql 即 Not Only Sql 不仅仅是sql):
- Redis、MongDB
- 对象存储,通过对象自身的属性来决定。
1.5 DBMS(数据库管理系统)
- 数据库的管理软件,科学有效地管理我们的数据。维护和获取数据。
- MySQL:数据库管理系统
1.6 MySQL简介
- MySQL是一个关系型数据库管理系统。
- 由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。
- MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
- 开源!
- 体积小、速度快、总体拥有成本低。
- 适用于中小型网站、或者大型网站 (集群)。
- 官网:www.mysql.com
- 下载:dev.mysql.com/downloads/mysql
- 版本:5.7 (稳定)、8.0 (最新)
1.7 安装MySQL
- 修改root用户密码:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
1.8 安装Navicat for MySQL
1.9 练习
- 新建一个数据库 (school)
- 新建一张表 (student)
- 查看表
- 添加一条记录
1.10 连接数据库
使用命令行连接
mysql -u root -p123456 --连接数据库
show databases; --查看所有的数据库
use school --切换数据库 use+数据库名
show tables; --查看数据库中所有的表
desc student; --查看指定表的信息
create database westos; --创建一个数据库
show databases;
exit; --退出连接
-- 单行注释
/*
多行注释
*/
1.11 数据库语言
- DDL:数据库定义语言
- DML:数据库操作语言
- DQL:数据库查询语言
- DCL:数据库控制语言
二、操作数据库
操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
mysql 关键字不区分大小写
2.1 操作数据库
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
- 删除数据库
DROP DATABASE [IF EXISTS] westos;
- 使用数据库
--如果表名或者字段名是一个特殊字符,就需要带` ` USE `school`;
- 查看数据库
SHOW DATABASES; --查看所有的数据库
2.2 数据库的列类型
数值
数据类型 | 表示 | 大小 |
---|---|---|
tinyint | 十分小的数据 | 1个字节 |
smallint | 较小的数据 | 2个字节 |
mediumint | 中等大小的数据 | 3个字节 |
int(常用) | 标准的整数 | 4个字节 |
bigint | 较大的数据 | 8个字节 |
float | 浮点数 | 4个字节 |
double | 浮点数 | 8个字节 |
decimal(一般在金融计算时使用) | 字符串形式的浮点数 | 8个字节 |
字符串
数据类型 | 表示 | 大小 |
---|---|---|
char | 固定大小的字符串 | 0~255 |
varchar(常用) | 可变字符串 (用于储存常用的变量) | 0~65535 |
tinytext | 微型文本 | 2^8 - 1 |
text | 文本串 (用于保存大的文本) | 2^16 - 1 |
时间、日期
数据类型 | 表示 |
---|---|
date | yyyy-MM-dd (日期格式) |
time | HH: mm: ss (时间格式) |
datetime(常用) | yyyy-MM-dd HH: mm: ss (最常用的时间格式) |
timestamp | 时间戳 (1970.1.1到现在的毫秒数) |
year | 年份 |
null
- 空值,未知。
- 不要使用null进行运算,结果为null。
2.3 数据库的字段属性 (重点)
- 无符号
- 该列不能声明为负数
- 填充零
- 不足的位数使用0来填充
- 自动递增
- 自动在上一条记录的基础上 +1 (默认)
- 通常用来设计唯一的主键,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
- 不是 null
- 如勾选此选项,不给赋值的情况下就会报错
- 如果没有勾选此选项,默认就是null
- 默认值
- 设置默认值
- 如果不指定该列的值,则会有默认值。
拓展:做项目时每一个表都要有的五个字段,表示每一个记录存在的意义。 (阿里巴巴规范)
- id 主键
- version 乐观锁
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
2.4 创建数据库表 (重点)
- 注意⚠️
- 使用英文括号(),表名和字段名尽量使用``(不是单引号!)括起来。
- AUTO_INCREMENT:自动递增
- 字符串使用单引号括起来。
- 所有的语句后面加英文逗号,最后一个可以不加。
- PRIMARY KEY:主键,一般一个表只有一个唯一的主键。
- 格式
CREATE TABLE [IF NOT EXISTS] `表名` ( `字段名` 列类型 [属性] [索引] [注释], `字段名` 列类型 [属性] [索引] [注释], ...... `字段名` 列类型 [属性] [索引] [注释], [主键] ) [表类型] [字符集设置] [注释]
常用命令
SHOW CREATE DATABASE school --查看创建此数据库的语句
SHOW CREATE TABLE student --查看创建此表的语句
DESC student --显示此表的结构
2.5 数据表的类型
- 数据库引擎
- MYISAM:曾经使用
- INNODB:默认使用
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
- 常规使用操作:
- MYISAM:节省空间,速度较快
- INNODB:安全性高,支持事务的处理,多表、多用户操作
在物理空间中存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库。
本质还是文件的存储。
- MySQL引擎在物理文件上的区别
- INNODB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件 (data)
- *.MYI 索引文件 (index)
设置数据库表的字符集编码
CHARSET=utf8mb4 COLLATE=utf8mb4_bin
- 不设置的话,会是mysql默认的字符集编码Latin1!(不支持中文)
2.6 修改、删除表
修改
- 修改表名
ALTER table 旧表名 RENAME AS 新表名;
- 增加表的字段
ALTER TABLE 表名 ADD 字段名 列属性;
- 修改表的字段
- 重命名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性;
- 修改约束
ALTER TABLE 表名 MODIFY 字段名 列属性;
- 重命名
删除
- 删除表的字段
ALTER TABLE 表名 DROP 字段名;
- 删除表
DROP TABLE [IF EXISTS] teacher;
所有的创建和删除操作尽量加上判断,以免报错。
三、MySQL的数据管理
3.1 外键 (了解)
方式一、在创建表的时候,增加约束。 (麻烦,比较复杂)
/*
学生表的 gradeid 字段要去引用年级表的 gradeid 字段。
定义外键 key
给这个外键添加约束 (执行引用) references 引用
*/
CREATE TABLE `grade`
(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE IF NOT EXISTS `student`
(
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` date DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
删除有外键关系的表时,必须要先删除引用它的表。
方式二、创建表成功后,添加外键约束。
CREATE TABLE `grade`
(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE IF NOT EXISTS `student`
(
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` date DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的字段) REFERENCES 表名(字段名)
以上的操作都是物理外键 (数据库级别的外键),不建议使用。避免数据库过多时造成困扰。
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)。
- 我们想使用多张表的数据,即需要使用外键时,使用程序实现即可。
3.2 DML语言 (重点)
- 数据库的意义:数据存储、数据管理
- DML语言:数据库操作语言
- insert
- update
- delete
3.3 添加 (insert)
-- 由于主键自增,我们可以省略.
-- 写插入语句时,数据和字段要一一对应!
INSERT INTO `student`(`name`) VALUES('张三');
-- 插入多个字段
INSERT INTO `student`(`name`, `pwd`, `sex`) VALUES('张三', 'abcabc', '男');
INSERT INTO `student`(`name`, `pwd`, `sex`)
VALUES('张三', '444444', '女'), ('李四', '666666', '男'), ('王五', 'lxtlxt', '女');
- 语法:
insert into 表名(字段名1, 字段名2, 字段名3, ...) values(值1, 值2, 值3, ...), (值1, 值2, 值3, ...)
- 注意⚠️:
- 非空的字段必须要定义,否则会报错。
- 字段和字段之间使用英文逗号隔开。
- 可以只写表名,不写字段名,但是后面的值要全部定义,一一对应。
- 可以同时插入多条数据。
3.4 修改 (update)
UPDATE `student` SET `name` = '狂神' WHERE id = 1;
-- 不指定条件的情况下,会改动所有的表!
UPDATE `student` SET `name` = '长江7号';
-- 修改多个属性
UPDATE `student` SET `name` = '狂神', `email` = '123456@qq.com' WHERE id = 1;
-- 通过多个条件定位数据
UPDATE `student` SET `name` = 'lucy', `email` = '123456@qq.com' WHERE id = 1 AND sex = '男';
-
条件:where 子句
操作符 含义 范例 结果 = 等于 5=6 false <> 或 != 不等于 5<>6 true > 大于 5>6 false < 小于 5<6 true >= 大于等于 5>=6 false <= 小于等于 5<=6 true BETWEEN … AND … 在某个范围内 (闭合区间) BETWEEN 2 AND 5 [2, 5] AND 与 5>1 AND 2>3 false OR 或 5>1 OR 2>3 true -
语法:
UPDATE 表名 SET colnum_name = value[, colnum_name = value, ...] WHERE 条件
-
注意⚠️:
- colnum_name 是数据库的字段,尽量带上``。
- 如果没有指定条件,将会修改所有的列。
- value,是一个具体的值,也可以是一个变量。
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE id = 1; -- CURRENT_TIME:当前时间
- 多个设置的属性之间,使用英文逗号隔开。
3.5 删除 (delete)
delete 命令 (可以指定条件)
-- 删除指定的一条数据
DELETE FROM `student` WHERE id = 3;
- 语法:
DELETE FROM 表名 [where 条件]
TRUNCATE命令 (清空数据库表时使用)
-- 清空 student 表
TRUNCATE `student`
- 作用:完全清空一个数据库表,表的结构和索引约束不会变。
比较 DELETE 和 TRUNCATE
- 相同点:都能删除数据。
- 不同点:
- TRUNCATE 重新设置自增列,计数器会归零。DELETE 不会影响自增。
- TRUNCATE 不会影响事务。
- 了解即可:用 DELETE 清空表之后,重启数据库
- 如果数据库引擎为 INNODB,自增列将会从1重新开始。(INNODB是存在内存中的,断电即失)
- 如果数据库引擎为 MYISAM,自增列将会从上一个自增量开始。(MYISAM是存在文件中的,不会丢失)
四、DQL查询数据 (最重点)
4.1 DQL
- Data Query Language:数据查询语言
- 所有的查询操作都使用它,关键字:Select
- 无论是简单的查询,还是复杂的查询,它都能做。
- 它是数据库中最核心、最重要、使用频率最高的语言。
- Select 完整语法
- 注意:[ ] 括号代表可选的,{ } 括号代表必选的
SELECT [ALL | DISTINCT] {* | TABLE.* | [TABLE.filed1[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}]; -- 指定查询的记录从哪条至哪条
4.2 指定查询字段
-- 查询全部的学生 SELECT 字段名 FROM 表名
SELECT * FROM `student`;
-- 查询指定字段
SELECT `StudentNo`, `StudentName` FROM `student`;
-- 给字段名起个别名,AS可以省略、单引号也可以省略,也可以给表起别名。
SELECT `StudentNo` AS '学号', `StudentName` AS '学生姓名' FROM `student`;
-- 函数 CONCAT(a, b)
SELECT CONCAT('姓名:', StudentName) AS '新名字' FROM `student`;
- 语法:
SELECT 字段1, 字段2, ... FROM 表
- 有时,字段名(列名)不是那么容易理解,这时我们可以给它起个别名。
去重 distinct
- 作用:去除SELECT查询出来的结果中重复的数据,重复的数据只显示一条。
-- 查询一下有哪些学生参加了考试
SELECT * FROM `result`; -- 查询全部的考试成绩
SELECT `StudentNo` FROM `result`; -- 查询有哪些学生参加了考试
SELECT DISTINCT `StudentNo` FROM `result`; -- 去除重复数据
数据库的列 (表达式)
SELECT VERSION(); -- 查询系统版本 (函数)
SELECT 100*3-2 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 | 逻辑与:两个都为真,则结果为真 |
or 或 || | a or b 或 a || b | 逻辑或:其中一个为真,则结果为真 |
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` BETWEEN 95 AND 100;
-- 查询除了学号为1000的同学之外的学生的成绩
-- 方法一:
select `studentNo`, `StudentResult` FROM `result`
WHERE `StudentNo` != 1000;
-- 方法二:
SELECT `studentNo`, `StudentResult` FROM `result`
WHERE NOT `StudentNo` = 1000;
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a IS NULL | 若a为NULL,结果为真 |
IS NOT NULL | a is not null | 若a不为NULL,结果为真 |
BETWEEN | a BETWEEN b AND c | 若a在b和c之间,结果为真 |
LIKE | a LIKE b | SQL匹配,若a和b匹配,结果为真 |
IN | x IN (a, b, c, …) | 若x匹配a,或x匹配b,或… 结果为真 |
-- =========== 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 '%嘉%';
-- =========== IN ============
-- 查询1001、1002、1003号学生的信息
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `studentNo` IN (1001, 1002, 1003);
-- 查询在山东济南的学生 IN后面的括号里一定要填具体的值
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `Address` IN ('山东济南');
-- =========== IS NULL / IS NOT NULL ============
-- 查询地址为空的学生
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `Address` ='' OR `Address` IS NULL;
-- 查询填写了出生日期的学生
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;
4.4 联表查询 (JOIN ON)
-- ============= 联表查询 join ================
-- 查询参加了考试的同学 (学号、姓名、科目编号、成绩)
SELECT * FROM `student`
SELECT * FROM `result`
-- 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` 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`
-- 思考题:查询参加了考试的学生信息:学号、姓名、科目名、成绩
/*
思路:
1.三张表的联表查询。
2.因为要查询参加了考试的学生信息,所以以成绩表为基准查询。
3.确定交叉点:student表与result表的交叉点为StudentNo,result表与subject表的交叉点为SubjectNo
4.确定查询方法:student表与result使用右连接查询,result与subject表使用inner连接查询。
*/
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`
操作 | 描述 |
---|---|
inner join | 左右两张表中都匹配StudentNo ,才会返回值 |
left join | 返回左表中所有匹配StudentNo 的值,即使右表中没有匹配 |
right join | 返回右表中所有匹配StudentNo 的值,即使左表中没有匹配 |
自连接 (了解)
- 自己的表和自己的表连接。
- 核心:一张表拆为两张一样的表即可。
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 = utf8mb4 COLLATE = utf8mb4_bin;
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`
- 父类
categoryid categoryName 2 信息技术 3 软件开发 5 美术设计 - 子类
pid categoryid categoryName 3 4 数据库 2 8 办公信息 3 6 web开发 5 7 ps技术 - 操作:查询父类对应的子类关系
父类 子类 软件开发 数据库 信息技术 办公信息 软件开发 web开发 美术设计 ps技术
4.5 分页和排序
排序 (ORDER BY)
- 升序 (ASC),降序 (DESC)
- 语法:
ORDER BY
通过哪个字段排序
排序方式
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`
WHERE SubjectName = '数据库结构-1'
ORDER BY `StudentResult` DESC -- 查询结果根据成绩按降序排序
分页 (LIMIT)
- 语法:
LIMIT
起始下标, 页面大小
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`
WHERE SubjectName = '数据库结构-1'
ORDER BY `StudentResult` DESC
LIMIT 0, 5 -- 从0开始,5条数据
4.6 子查询
- 本质:在where语句中嵌套一个子查询语句
-- 查询 ’数据库结构-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'
)
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
-- 查询‘高等数学-2’的分数不小于80分的学生的学号和姓名
-- 联表查询 + 子查询
SELECT s.`StudentNo`, `StudentName`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
WHERE `SubjectNo` = (
SELECT `SubjectNo` FROM `subject`
WHERE `SubjectName` = '高等数学-2'
) AND `StudentResult` >= 80
-- 联表查询
SELECT s.`StudentNo`, `StudentName`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE `SubjectName` = '高等数学-2' AND `StudentResult` >= 80
-- 子查询:嵌套查询
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `StudentNo` IN (
SELECT `StudentNo` FROM `result` WHERE `StudentResult` >= 80 AND `SubjectNo` = (
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = '高等数学-2'
)
)
五、MySQL函数
5.1 常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回 0~1 之间的随机数
SELECT SIGN(10) -- 返回参数的符号 负数返回-1 0返回0 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 返回字符串的长度
SELECT CONCAT('我', '爱', '你们') -- 拼接字符串
SELECT INSERT('我爱编程helloworld', 1, 2, '超级热爱') -- 插入,替换 从某个位置开始替换某个长度
SELECT LOWER('KuangShen') -- 将字符串转换为小写
SELECT UPPER('KuangShen') -- 将字符串转换为大写