MySQL 入门（二）

二、操作数据库

1. 结构化查询语句分类

2. 数据库操作

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

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

工具操作数据库

• 学习方法：对照 SQLyog 工具，自动生成的语句学习；
• 固定语法中的单词，需要记忆；

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

• 列类型 : 规定数据库中，该列存放的数据类型；
• 数据类型，对数据库的优化非常重要；
• 分三类：数值、日期/时间、字符串(字符)类型。

数值类型

• tinyint：小整数，1个字节；
• smallint：较小整数，2个字节；
• mediumint：中等整数，3个字节；
• int（常用）：标准整数，4个字节；
• bigint：大整数，8个字节；
• float：浮点数，4个字节；
• double：浮点数，8个字节；
• decimal：字符串浮点数，金融计算。

image

字符串类型

• char：字符串固定大小，0~255 字节；
• varchar（常用）：可变字符串，0~65535 字节；
• tinytext：微型文本，2^8-1 字节；
• text：文本串，2^16-1 字节。

日期/时间类型

• date：YYYY-MM-DD，日期格式；
• time：HH:mm:ss，时间格式；
• datatime（常用）：YYYY-MM-DD HH:mm:ss；
• timestamp：时间戳，1970.1.1 ~ 现在 的毫秒数；
• year：年份。

image

Null 值

• 理解为 没有值 或 未知值；
• 注意：不要用 NULL 进行算术运算 , 结果仍为NULL。

4. 数据字段属性（重点）

image

• UnSigned：不允许负数
  • 无符号的；
  • 声明该数据列不允许负数。
• Zerofill：补 0
  • 用 0 填充；
  • 不足位数的用 0 来填充 , 如 int(3)，5 则为 005。
• 自增：Auto_InCrement
  • 自动增长，每添加一条数据，自动在上一个记录数上加 1(默认)；
  • 通常用于设置 主键 , 且为整数类型；
  • 可定义起始值和步长：
    • 当前表设置步长（AUTO_INCREMENT=100）：只影响当前表；
    • SET @@auto_increment_increment=5; 影响所有使用自增的表（全局）。
• 非空：
  • 默认为 NULL，即没有插入该列的数值；
  • 如果设置为 NOT NULL , 则该列 必须有值。
• 默认：
  • 用于设置默认值；
  • 例如：
    • 性别字段，默认为 "男" , 否则为 "女" ;
    • 若无指定，则默认值为 "男"。

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

• MySQL8 中更改内容：

  • 需要将 INT(M) 改为 INT，否则告警；
  • 字符集格式：utf8mb4，或在 my.ini 中做相应配置，否则告警；

• 创建数据表格式：DDL

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

• 常用命令（重点）：

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

注意点：

• AUTO_INCREMENT 自增；
• PRIMARY KEY：主键，一般一个表只有一个唯一的主键；
• 使用英文 ()，表的名称和字段，尽量使用 反引号 括起来；
• 反引号 用于区别 MySQL 保留字与普通字符（键盘 esc 下面的键）
• 字符串使用单引号括起来；
• 所有的语句后面加 ，（英文的），最后一个不用加。

规范：

• 每一个表，都必须存在以下五个字段：
  • id：主键；
  • version：乐观锁；
  • is_delete：伪删除；
  • gmt_create：创建时间；
  • gmt_update：修改时间。

6. 数据表的类型

设置数据表的类型

CREATE TABLE `表名`(
    -- 省略一些代码
    -- Mysql注释
    -- 1. # 单行注释
    -- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)
-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;

• MySQL 的数据表的类型：MyISAM、InnoDB、HEAP、BOB、CSV 等；
• 常用：InnoDB、MyISAM。

• 适用场合（经验） :

  • MyISAM : 节约空间及相应速度；

  • InnoDB : 安全性 , 事务处理及多用户操作数据表。

数据表的存储位置

• MySQL 数据表以文件方式存放在磁盘中：

  • 包括表文件 , 数据文件 , 以及数据库的选项文件；
  • 位置：Mysql 安装目录\data\下存放数据表 ；
  • 目录名对应数据库名 , 该目录下文件名对应数据表。

• 注意：（MySQL 8，取消了*.frm）

  • InnoDB 类型数据表只有一个 *.frm 文件 , 以及上一级目录的 ibdata1 文件；
  • MyISAM 类型数据表，对应三个文件：
    • *.frm：表结构定义文件；
    • *.MYD：数据文件（data）；
    • *.MYI：索引文件（index）。

设置数据表字符集

• 可为数据库、数据表、数据列设定不同的字符集，设定方法 :

  • 命令：CREATE TABLE 表名()CHARSET = utf8mb4;

  • MySQL 数据库配置文件 my.ini 中的参数设定；

    # 服务端使用的字符集默认为utf8mb4（mysql8）
    character-set-server=utf8mb4
    

  • 建议在创建时设置。

7. 修改数据库

修改表 ( ALTER TABLE )

• 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名;

  alter table `teacher` rename as `teacher1`;
  

• 添加字段： ALTER TABLE 表名 ADD 字段名 列属性[属性];

  -- MySQL8 去掉了int位数
  alter table `teacher1` add age int;
  

• 修改字段：

  • ALTER TABLE 表名 MODIFY 字段名 列类型[属性];
  • ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性];

  -- 修改字段属性
  alter table `teacher1` modify age varchar(10);
  -- 更改字段名，并修改字段属性
  alter table `teacher1` change age age01 int;
  

  区别：

  • change：一般用来重命名字段；
  • modify：只能修改字段类型和约束，不能重命名字段。

• 删除字段：ALTER TABLE 表名 DROP 字段名;

  alter table `teacher` drop `age`;
  

删除数据表

• 语法：DROP TABLE [IF EXISTS] 表名;
  • IF EXISTS 为可选， 判断是否存在该数据表；
  • 删除不存在的数据表，会抛出错误。

drop table if exists `teacher`;

其它：

• 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符；
• 模式通配符：
  • _ ：任意单个字符；
  • %： 任意多个字符，甚至包括零字符；
  • 单引号，需要进行转义 \'。
• SQL 对大小写不敏感 （关键字）。

三、MySQL 数据管理

1. 外键（了解）

外键的概念

• 外键：如果公共关键字，在一个关系中是主关键字，那么这个公共关键字，被称为另一个关系的外键；
• 两个关系之间的相关联系；
• 以另一个关系的外键，作主关键字的表，被称为 主表，具有此外键的表被称为 主表的从表；
• 在实际操作中：
• 将一个表的值，放入第二个表来表示关联，所使用的值，是第一个表的主键值（在必要时可包括复合主键值）；
• 第二个表中，保存这些值的属性，称为外键(foreign key)。
• 外键作用：保持数据一致性、完整性，控制存储在外键表中的数据（约束），使两张表形成关联，外键只能引用外表中的列的值，或使用空值。

创建外键

• 方式一：创建子表同时创建外键；

-- 年级表 (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`);

删除外键

• 操作：删除 grade 表，报错

image

• 注意：删除具有主外键关系的表时，要先删从表，后删主表；

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

• 以上操作，都是物理外键，数据库级别的外键，不建议使用！！
• 数据库为单纯的表，只用来存储数据，只有行（数据）和列（字段）；
• 多张表同时操作，使用外键时，用程序实现。

2. DML 语言（重点）：数据操作语言

• 数据库意义：数据存储、数据管理；
• 管理数据库数据方法：
  • 通过 SQLyog 等管理工具，管理数据库数据；
  • 通过 DML 语句，管理数据库数据。
• 操作数据库对象，所包含的数据：
  • INSERT：添加数据；
  • UPDATE：更新数据；
  • DELETE：删除数据。

3. 添加数据

INSERT 命令

• 语法：

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

• 注意：
  • 字段或值之间，用英文 , 隔开 ；
  • 字段1,字段2.. 该部分可省略，但添加的值，务必与表结构，数据列，顺序相对应，且数量一致；
  • 可同时插入多条数据，values 后用英文 , 隔开。

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

• 结论：字段1,字段2... 该部分可省略，前提是，添加的值，务必与表结构，数据列，顺序相对应，且数量一 致。

4. 修改数据

update 命令

• 语法：

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

• 注意：
  • column_name：要更改的数据列；
  • value：修改后的数据，可以为变量，具体指，表达式或者嵌套的 SELECT 结果；
  • condition：为筛选条件，如不指定，则修改该表的所有列数据。

where 条件子句

• 简单理解为：有条件的，从表中筛选数据；

• 测试：

-- 修改年级信息
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];

• 注意：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`;

• 注意：
  • 区别于 DELETE 命令；
  • 相同：都能删除数据，不删除表结构，但 TRUNCATE 速度更快；
  • 不同：
    • TRUNCATE 重新设置 索引 计数器，不会记录日志；
    • TRUNCATE 不会对事务有影响。
• delete 问题：重启数据库后（了解）
  • InnoDB：自增列会从1开始（存在内存中的，断电即失）；
  • MyISAM：继续从上一个自增量开始（存在文件中，不丢失）。

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

1. DQL 语言

• DQL（Data Query Language 数据查询语言）：
  • 查询数据库数据，如 SELECT 语句；
  • 单表查询、多表的复杂查询、嵌套查询；
  • 数据库语言中最核心、最重要的语句；
  • 使用频率高。

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

• 注意：[ ] 可选，{ } 必选，可选参数必须按语法的前后顺序，否则报错。
• 导入 SQL 文件：

-- 创建一个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 查询返回结果中，重复的记录 ( 列值相同 ) , 只返回一条；

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

image

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

• 语法：select 表达式 from 表名;
• 数据库中的表达式：文本值、列值、NULL、函数、操作符等组成；
• 应用场景：
  • SELECT 返回结果列中使用；
  • SELECT 的 ORDER BY , HAVING 等子句中使用；
  • DML 语句中的 where 条件语句中使用。

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

3. where 条件语句

• 作用：检索数据表中，符合条件的记录；
• 搜索条件：一个或多个逻辑表达式，结果一般为 true 或 false。

逻辑操作符 MySQL8 运算符

• 运算符尽量使用英文字母
• 满足条件的查询（where)：

-- 查询成绩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;

模糊查询：比较操作符

• 注意：
  • 数值数据类型的记录之间，才能进行算术运算；
  • 相同数据类型的数据之间，才能进行比较；
  • IN 括号中，是具体的一个或多个值，不能用通配符。
• 模糊查询：between、and、like、in、null
  • 通配符：
    • %：0 到任意多个字符；
    • _ ：任意单个字符；
    • 特殊字符，需要进行转义 \。

-- 多字符匹配：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 对比

image

image

连接查询：

• 多张数据表数据查询，可通过连接运算符 JOIN 实现；
• inner join（内连接）：查询两个表中结果的交集；
• 外接接（outer join）：
  • left join（左外连接）：以左表为基准，右表进行匹配，匹配不上的，返回左表的记录，右表以 NULL 填充；
  • right join（右外连接）：以右表为基准，左表进行匹配，匹配不上的，返回右表的记录，左表以 NULL 填充；
• 等值连接和非等值连接；
• 自连接。
• 测试：
  • 连接查询：join 连接的表 on 判断条件；
  • 等值查询：join 连接的表 where 判断条件。

-- 查询参加考试的学生信息(学号、学生姓名、科目编号、分数)
-- 所有学生信息
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';

分析：

• 要查询哪些数据：select ...；
• 从哪几个表中查：from 表名 xxx join 连接的表 on 交叉条件；
• 多张表查询：先查两张，再逐个表增加。

自连接（了解）

• 自连接：
  • 数据表与自身进行连接；
  • 核心：一张表，拆为两张一样的表即可。
• SQL 文件：

-- 创建表
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

• 表折分：

  • 父类

    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 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. 排序和分页

排序

• 根据指定的列（字段），对结果集进行排序；

• 语法 : ORDER BY ...

  • ASC：升序（默认）；
  • DESC：降序。

• 测试：

-- 查询 高等数学-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;

分页

• 语法 : SELECT * FROM 表名 LIMIT [offset,] rows | rows OFFSET offset；

• 作用：

  • 缓解数据库压力；
  • 提高网络传输速度；
  • 更好的用户体验。

• 推导：

  • 第一页 : limit 0,5（记录起始值，记录条数）；
  • 第二页 : limit 5,5；
  • 第三页 : limit 10,5；

• 公式：limit (n-1)*pageSzie,pageSzie;

  • pageSzie：每页显示数据条数；
  • (n-1)*pageSize：起始值；
  • n：当前页；
  • 总页数：数据总数/页面显示条数。

• 测试：

-- 每页显示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. 子查询

• 在查询语句 WHERE 条件语句中，嵌套另一个查询语句；

  select * from user where id = (select id from grade);
  

• 嵌套查询，可由多个子查询组成（由里及外）;

• 子查询返回的结果，一般都是集合，建议使用 IN 关键字;

• 测试：

-- 查询 高等数学-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. 分组和过滤

• 分组：GROUP BY...
• 过滤：HAVING...

-- 查询不同课程的平均分,最高分,最低分
-- 前提：根据不同的课程进行分组
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

• 注意：
  • where 写在 group by（分组）前面；
  • 分组后面的筛选，要使用 HAVING...；
  • HAVING 是从前面筛选的字段再筛选，where 是从数据表的字段，直接进行筛选。

8. select 小结

• 顺序非常重要：

select 去重 要查询的字段 from 表名（注意：表和字段可以取别名 as）
xxx join 要连接的表 on 等值判断
where(具体的值，子查询语句)
group by(用哪个字段来分组)
having(过滤分组后的信息，条件与where一样，位置不同)
order by(排序 升序，降序)
limit 分页起始值，记录条数;