MySQL 入门（二）

2022-02-17  本文已影响0人  yjtuuige

二、操作数据库

1. 结构化查询语句分类
名称 解释 命令
DDL（数据定义语言） 定义和管理数据对象，如数据库，数据表等 CREATE、DROP、 ALTER
DML（数据操作语言） 用于操作数据库对象中所包含的数据 INSERT、UPDATE、 DELETE
DQL（数据查询语言） 用于查询数据库数据（重点） SELECT
DCL（数据控制语言） 用于管理数据库的语言，包括管理权限及数据更改 GRANT、commit、 rollback
2. 数据库操作

命令行操作数据库（重点）

-- [ ] 可选
-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
-- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
-- 查看数据库
SHOW DATABASES;
-- 使用数据库
USE 数据库名;

工具操作数据库

3. 数据值和列类型（了解）

数值类型

image

字符串类型

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65535字节 二进制形式的长文本数据
TEXT 0-65535字节 长文本数据
MEDIUMBLOB 0-16777215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16777215字节 中等长度文本数据
LONGBLOB 0-4294967295字节 二进制形式的极大文本数据
LONGTEXT 0-4294967295字节 极大文本数据

日期/时间类型

image

Null 值

4. 数据字段属性（重点）
image
5. 创建数据表（DDL 的一种）
-- 目标：创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
CREATE TABLE IF NOT EXISTS `student`(
    -- MySQL8 INT(M) 改为 INT
    `id` INT 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 '性别',
    `brithday` DATETIME DEFAULT NULL COMMENT '生日',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    -- 设置主键
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

CREATE TABLE [IF NOT EXISTS] `表名`(
    '字段名1' 列类型 [属性][索引][注释],
    '字段名2' 列类型 [属性][索引][注释],
    #...
    '字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

-- 查看数据库的定义
show create database 数据库名;
-- 查看数据表的定义
show create table 数据表名;
-- 显示表结构
DESC 数据表名;
-- 设置严格检查模式(不能容错了)
SET sql_mode='STRICT_TRANS_TABLES';

注意点：

规范：

6. 数据表的类型

设置数据表的类型

CREATE TABLE `表名`(
    -- 省略一些代码
    -- Mysql注释
    -- 1. # 单行注释
    -- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)
-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;
名称 MyISAM InnoDB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大，约为 MyISAM 两倍

数据表的存储位置

设置数据表字符集

7. 修改数据库

修改表 ( ALTER TABLE )

删除数据表

drop table if exists `teacher`;

其它：

三、MySQL 数据管理

1. 外键（了解）

外键的概念

创建外键

-- 年级表 (id 年级名称)
CREATE TABLE `grade` (
  `gradeid` INT NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
  `studentno` INT NOT NULL COMMENT '学号',
  `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` TINYINT DEFAULT '1' COMMENT '性别',
  `gradeid` INT DEFAULT NULL COMMENT '年级',
  `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
  `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
  `birthday` DATETIME DEFAULT NULL COMMENT '生日',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
  PRIMARY KEY (`studentno`),
  -- 设置外键
  KEY `FK_gradeid` (`gradeid`),
  -- 外键添加约束（执行引用）references 引用
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

-- alter table 表名 add constraint 约束名 foreign key(作为外键的列) references 主表（字段）

alter table `student`
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);

删除外键

image 
-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;
2. DML 语言（重点）：数据操作语言
3. 添加数据

INSERT 命令

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')

-- INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
INSERT INTO `grade`(`gradename`) VALUES ('大一');
-- 一次插入多条数据
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大三');

-- 报错，与表结构不对应
INSERT INTO `grade` VALUES ('大四');
-- 操作成功，与表结构对应
INSERT INTO `grade` VALUES ('4','大四');
image
4. 修改数据

update 命令

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

where 条件子句

运算符 含义 范围 结果
= 等于 5=6 false
<> 或 != 不等于 5!=6 true
> 大于 5>6 false
< 小于 5<6 true
>= 大于等于 5>=6 false
<= 小于等于 5<=6 true
BETWEEN 在某个范围之间 BETWEEN 5 AND 10
AND 并且 5 > 1 AND 1 > 2（&&） false
OR 或 5 > 1 OR 1 > 2（||） true 
-- 修改年级信息
UPDATE `grade` SET `gradename` = '高中' WHERE `gradeid` = 1;
-- value值可以是一个变量 CURRENT_TIME：时间变量
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='测试' AND `sex`='0';
5. 删除数据

DELETE 命令

DELETE FROM `表名` [WHERE condition];

-- 删除最后一个数据
DELETE FROM `grade` WHERE `gradeid`=5;

TRUNCATE 命令

TRUNCATE [TABLE] `数据表名`;
-- 清空年级表
truncate `grade`;

-- 创建一个测试表
CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `coll` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

-- 插入测试数据
INSERT INTO `test`(`coll`) VALUE('row1'),('row2'),('row3');
-- 方式1：清空表数据 (delete不带where条件)
DELETE FROM `test`;
-- 方式2：清空表数据 (truncate)
TRUNCATE TABLE `test`;

四、使用 DQL 查询数据（重点）

1. DQL 语言

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}];
    -- 指定查询的记录从哪条至哪条

-- 创建一个school数据库
DROP DATABASE IF EXISTS `school`;
CREATE DATABASE IF NOT EXISTS `school`;
USE `school`;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE IF NOT EXISTS `grade` (
  `GradeID` INT NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`GradeID`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
-- 插入年级数据
INSERT INTO `grade` (`GradeID`,`GradeName`) 
VALUES
(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE IF NOT EXISTS `result` (
  `StudentNo` INT NOT NULL COMMENT '学号',
  `SubjectNo` INT NOT NULL COMMENT '课程编号',
  `ExamDate` DATETIME NOT NULL COMMENT '考试日期',
  `StudentResult` INT NOT NULL COMMENT '考试成绩',
  KEY `SubjectNo` (`SubjectNo`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
-- 插入成绩数据
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),
(1001,1,'2013-11-11 16:00:00',80),
(1001,2,'2013-11-12 16:00:00',90),
(1001,3,'2013-11-11 09:00:00',78),
(1001,4,'2013-11-13 16:00:00',90),
(1001,5,'2013-11-14 16:00:00',68),
(1002,1,'2013-11-11 16:00:00',88),
(1002,2,'2013-11-12 16:00:00',79),
(1002,3,'2013-11-11 09:00:00',52),
(1002,4,'2013-11-13 16:00:00',69),
(1002,5,'2013-11-14 16:00:00',77),
(1003,1,'2013-11-11 16:00:00',90),
(1003,2,'2013-11-12 16:00:00',89),
(1003,3,'2013-11-11 09:00:00',96),
(1003,4,'2013-11-13 16:00:00',83),
(1003,5,'2013-11-14 16:00:00',79);

-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE IF NOT EXISTS `student` (
  `StudentNo` INT NOT NULL COMMENT '学号',
  `LoginPwd` VARCHAR(20) DEFAULT NULL,
  `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
  `Sex` TINYINT DEFAULT NULL COMMENT '性别，0或1',
  `GradeId` INT 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=INNODB DEFAULT CHARSET=utf8mb4;
-- 插入学生数据
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','张三',0,2,'13800001207','北京海淀','1983-2-15','text207@qq.com','123456198301011234'),
(1003,'123456','李四',0,2,'13800003333','北京通州','1985-10-18','text333@qq.com','123456198501011234'); 

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE IF NOT EXISTS `subject` (
  `SubjectNo` INT NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
  `ClassHour` INT DEFAULT NULL COMMENT '学时',
  `GradeId` INT DEFAULT NULL COMMENT '年级编号',
  PRIMARY KEY (`SubjectNo`)
) ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4;
-- 插入科目数据
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);
2. 指定查询字段
-- 查询表中所有的数据列结果,采用 *，效率低，不推荐
-- 查询所有学生信息
SELECT * FROM `student`;
-- 查询指定列(字段：学号 , 姓名)
SELECT `StudentNo`,`StudentName` FROM `student`;

AS 子句作为别名（AS 关键词可以省略）

-- 为列取别名(AS可以省略)
SELECT `StudentNo` AS 学号,`StudentName` AS 姓名 FROM `student`;
-- 为表取别名
SELECT `StudentNo` 学号,`StudentName` 姓名 FROM`student` s;
-- 为查询结果设置别名
-- CONCAT(a,b)函数，拼接字符串
SELECT CONCAT('姓名:',`StudentName`) AS 新姓名 FROM `student`;
image

distinct 关键字（去重复）

-- 查看考试成绩(所有)
SELECT * FROM `result`; 
-- 查看参加考试人员(按学号)，有重复
SELECT `StudentNo` FROM `result`;
-- distinct 去除重复项(默认是ALL)
SELECT DISTINCT `StudentNo` 学号去重 FROM `result`;
image

数据库的列（使用表达式）

-- selcet查询中使用表达式
-- 查询自增步长(变量)
SELECT @@auto_increment_increment;
-- 查询版本号（函数）
SELECT VERSION();
-- 计算结果（表达式）
SELECT 100*2+10 AS 计算结果;
-- 查看学生成绩，并将成绩整体 +1
SELECT `StudentNo` 学号,`StudentResult`+1 提分后 FROM `result`;
3. where 条件语句

逻辑操作符 MySQL8 运算符

运算符 语法 描述
ADN 或 && a AND b 或 a && b 逻辑与：两个都为真，结果才为真
OR 或 || a OR b 或 a || b 逻辑或：一个为真，结果为真
NOT ! not a 或 !a 逻辑非：取反 
-- 查询成绩85-100之间的数据
SELECT `StudentNo` 学号,`StudentResult` 成绩 FROM `result`
WHERE `StudentResult`>=85 AND `StudentResult`<=100;
-- AND也可以写成 &&
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult`>=85 && `StudentResult`<=100
-- 模糊查询 between(对应:精确查询)
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentResult` BETWEEN 85 AND 100;
-- 查看，除学号为1000的，其它学生成绩(!= 或 <>)
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `StudentNo`!=1000;
-- 使用not
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE NOT `StudentNo`=1000;

模糊查询：比较操作符

操作符 语法 描述
IS NULL a IS NULL 操作符为 NULL，结果为真
IS NOT NULL a IS NOT NULL 操作符不为 NULL，结果为真
BETWEEN a BETWEEN b AND c a 范围在 b 与 c 之间，结果为真
LIKE a LIKE b SQL 模式匹配：a匹配b，结果为真
IN a IN (a1,a2,a3,...) a 等于 a1,a2...中的一个，结果为真 
-- 多字符匹配：like 结合 %
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '张%';
-- 单字符匹配：like 结合 _
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '张_';
-- 查询姓张的同学,后面只有两个字的：LIKE '张__'
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '张__'
-- 查询名字中含有 嘉 字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '%嘉%';

-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'
-- =============================================
-- IN(具体的一个或多个值，不能用通配符)
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
SELECT `StudentNo`,`StudentName` FROM`student`
WHERE `StudentNo` IN('1000','1001','1002');
-- 查询地址在北京朝阳,广东深圳的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('北京朝阳','广东深圳');

-- NULL 空
-- =============================================
-- 查询email没有填写的学生信息
-- 不能直接写=NULL（错误）,用 is null
SELECT `StudentName` FROM `student`
WHERE `Email`='' OR `Email` IS NULL;
-- 查询出生日期填写的学生信息（不为空）
SELECT `StudentName`,`BornDate` FROM `student`
WHERE `BornDate` IS NOT NULL
4. 联表查询

JOIN 对比

操作符 描述
inner join 如果表中，至少有一个匹配，则返回行
left join 右表 中没有匹配，也从 左表 中返回所有行
right jion 左表 中没有匹配，也从 右表 中返回所有行
image image

连接查询：

-- 查询参加考试的学生信息(学号、学生姓名、科目编号、分数)
-- 所有学生信息
SELECT * FROM student;
-- 所有成绩信息
SELECT * FROM result;

/*
思路:
1：分析需求,确定查询的列来源于两个类 student、result,连接查询
2：确定使用哪种连接查询?(内连接)
确定交叉点：不同表中，共有的列信息（如：学号）
判断条件：学生表中的 StudentNo= 成绩表中的 StudentNo
*/

-- 等值连接
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s,`result` r
WHERE s.`StudentNo`=r.`StudentNo`;

-- 内连接（inner join）
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`;

-- 在student插入一条学生记录（无考试成绩）
-- 左连接（left join）:查询结果中，包含新插入无成绩学生信息
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s LEFT JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`;

-- 右连接（right join）:只包含有成绩学生信息
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s RIGHT 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;

-- 查询参加考试的同学信息(4张表：学号、年级名称、学生姓名、科目名、分数)
SELECT s.`StudentNo`,`GradeName`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
INNER JOIN `grade` g
ON s.`GradeId`=g.`GradeID`;

-- 查询 高等数学-4 的所有考试结果(学号、学生姓名、科目名称、成绩)
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`='高等数学-4';

分析：

自连接（了解）

-- 创建表
CREATE TABLE `category` (
  `categoryid` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` int NOT NULL COMMENT '父id',
  `categoryName` varchar(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

-- 插入记录
INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
image 
/*
将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询
*/
-- 内连接方式：
SELECT a.`categoryName` AS 父栏目,b.`categoryName` AS 子栏目
FROM `category` AS a INNER JOIN `category` AS b
ON a.`categoryid`=b.`pid`;

-- 等值查询：
SELECT a.`categoryName` AS 父栏目,b.`categoryName` AS 子栏目
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`;
5. 排序和分页

排序

-- 查询 高等数学-4 的所有考试结果(学号、学生姓名、科目名称、成绩)
-- 按成绩降序排序
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`='高等数学-4'
ORDER BY `StudentResult` DESC;

分页

-- 每页显示3条数据
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`='高等数学-4'
ORDER BY `StudentResult` DESC
LIMIT 0,3

-- 查询 高等数学-1 课程成绩前2名并且分数大于80的学生信息(学号、姓名、课程名、分数)
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' AND `StudentResult`>80
ORDER BY `StudentResult` DESC
LIMIT 0,2
6. 子查询
-- 查询 高等数学-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;

-- 查询课程为 高等数学-1 且分数不小于80分的学生的学号和姓名
-- 方式1：连接查询
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

-- 方法2：使用连接查询+子查询
SELECT s.`StudentNo`,`StudentName` FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
WHERE `SubjectNo`=(
    SELECT `SubjectNo` FROM `subject`
    WHERE `SubjectName`='高等数学-1' AND `StudentResult`>=80
);

-- 方法3：使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE `StudentNo` IN(
    SELECT `StudentNo` FROM `result`
    WHERE `StudentResult`>=80 AND `SubjectNo`=(
        SELECT `SubjectNo` FROM `subject`
        WHERE `SubjectName`='高等数学-1'
    )
);
7. 分组和过滤
-- 查询不同课程的平均分,最高分,最低分
-- 前提：根据不同的课程进行分组
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
8. select 小结
select 去重 要查询的字段 from 表名（注意：表和字段可以取别名 as）
xxx join 要连接的表 on 等值判断
where(具体的值，子查询语句)
group by(用哪个字段来分组)
having(过滤分组后的信息，条件与where一样，位置不同)
order by(排序 升序，降序)
limit 分页起始值，记录条数;
上一篇 下一篇

猜你喜欢

热点阅读