《遇见狂神说》MySQL从入门到删库(一)

落日映苍穹つ 2022-10-06 11:57 228阅读 0赞

# 0.概述 #

1.  MySQL基础笔记，复习回顾使用
2.  **秦疆老师配套视频直达**：[MySQL最新教程通俗易懂][MySQL]
3.  [环境搭建：MySQL数据库详细安装步骤直达][MySQL 1]

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>解释</th> 
   <th>命令</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DDL(数据定义语言)</td> 
   <td>定义和管理数据对象，如数据库，数据表等</td> 
   <td>create、drop、alter</td> 
  </tr> 
  <tr> 
   <td>DML(数据操作语言)</td> 
   <td>用于操作数据库对象中所包含的数据</td> 
   <td>insert、update、delete</td> 
  </tr> 
  <tr> 
   <td>DQL(数据查询语言)</td> 
   <td>用于查询数据库数据</td> 
   <td>select</td> 
  </tr> 
  <tr> 
   <td>DCL(数据控制语言)</td> 
   <td>用于管理数据库的语言，包括管理权限及数据更改</td> 
   <td>grant、commit、rollback</td> 
  </tr> 
 </tbody> 
</table>

# 1.操作数据库 #

*  **操作数据库 > 操作数据库中的表 > 操作数据库中表的数据**
 *  mysql 关键字不区分大小写

## 1.1操作数据库 ##

*  **创建数据库**：`create database [if not exists] 数据库名;`
 *  **删除数据库**：`drop database [if exists] 数据库名;`
 *  **使用数据库**：`use 数据库`

-- 使用数据库 `school`
    use `school`;

*  **查看数据库**：`show databases`

--------------------

使用SQLyog创建数据库时，需要选择

*  基字符集：`utf8`
 *  数据库排序规则：`utf8_unicode_ci`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center]

## 1.2数据库的列类型 ##

*  列类型 : **规定数据库中该列存放的数据类型**

> 数值类型

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th align="left">说明</th> 
   <th align="center">存储需求</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyint</td> 
   <td align="left">非常小的数据</td> 
   <td align="center">1个字节</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td align="left">较小的数据</td> 
   <td align="center">2个字节</td> 
  </tr> 
  <tr> 
   <td>mediumint</td> 
   <td align="left">中等大小的数据</td> 
   <td align="center">3 个字节</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td align="left">标准整数</td> 
   <td align="center">4个字节</td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td align="left">较大的整数</td> 
   <td align="center">8个字节</td> 
  </tr> 
  <tr> 
   <td>float</td> 
   <td align="left">单精度浮点数</td> 
   <td align="center">4个字节</td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td align="left">双精度浮点数</td> 
   <td align="center">8个字节</td> 
  </tr> 
  <tr> 
   <td>decimal</td> 
   <td align="left">字符串形式的浮点数</td> 
   <td align="center">金融计算的时候，一般是使用decimal</td> 
  </tr> 
 </tbody> 
</table>

> 字符串

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
   <th>最大长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char[(M)]</td> 
   <td>固定长字符串，检索快但费时间</td> 
   <td>0~255个字节</td> 
  </tr> 
  <tr> 
   <td>varchar[(M)]</td> 
   <td>可变字符串</td> 
   <td>0~65535个字节</td> 
  </tr> 
  <tr> 
   <td>tinytext</td> 
   <td>微型文本串</td> 
   <td>2^8 -1个字节</td> 
  </tr> 
  <tr> 
   <td>text</td> 
   <td>文本串</td> 
   <td>2^16-1个字节</td> 
  </tr> 
 </tbody> 
</table>

> 时间日期

java.util.Date

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>date</td> 
   <td>YYYY-MM-DD，日期格式</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>HH:mm:ss，时间格式</td> 
  </tr> 
  <tr> 
   <td>datetime</td> 
   <td>YYYY-MM-DD hh: mm: ss，最常用的时间格式</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>YYYYMMDDhhmmss格式表示的时间戳，1970.1.1到现在的毫秒数</td> 
  </tr> 
  <tr> 
   <td>year</td> 
   <td>YYYY格式的年份值</td> 
  </tr> 
 </tbody> 
</table>

> null

*  没有值，未知
 *  注意，不要使用NULL进行运算，因为结果为NULL

## 1.3 数据库的字段属性(重点) ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center 1]

Unsigned：

*  无符号的整数
 *  声明了该数据列不允许声明为负数

zerofill：

*  0填充的
 *  不足的位数，使用0来填充 【如int(3), 5则为005】

Auto\_InCrement：

*  自动增长的，每添加一条数据，自动在上一条记录的基础上 + 1 （默认）
 *  通常用来设计唯一的主键，且必须是整数类型
 *  可以自定义设计主键自增的起始值和步长（一般不用）

NULL和 NOT NULL：

*  默认为NULL，即没有插入该列的数值
 *  如果设置为 NOT NULL,则该列必须有值

default：

*  设置默认的值！
 *  sex，默认值为男，如果不指定该列的值，则会有默认值为“男”的值

## 1.4 创建数据库表（重点） ##

*  创建一个school数据库
 *  创建学生表
    
     *  学号int
     *  姓名varchar
     *  密码varchar
     *  性别varchar
     *  出生日期datetime
     *  家庭住址varchar
     *  邮箱varchar

-- 创建数据库 school
    CREATE DATABASE IF NOT EXISTS `school`;
    -- 删除数据库 student
    DROP DATABASE IF  EXISTS `student`;
    -- 注意点:表的名称和字段尽量使用 `` 括起来
    -- 创建学生表
    CREATE TABLE IF NOT EXISTS `student` (
      `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '主键',  		-- id 不为空，自增
      `name` VARCHAR(30) NOT NULL DEFAULT '林晓' COMMENT '姓名',	-- name 不为空，默认为林晓
      `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',	-- pwd 不为空，默认为123456
      `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',	-- sex 不为空，默认为男
      `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',		-- birthdat 默认为空
      `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',	-- address 默认为空
      `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',		-- email默认为空
        
      PRIMARY KEY (`id`)  									 -- 主键，一般一个表只有一个唯一的主键
    )ENGINE=INNODB DEFAULT CHARSET=utf8;

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
    	`字段名` 列类型 [属性] [索引] [注释],
        `字段名` 列类型 [属性] [索引] [注释],
        .......
        `字段名` 列类型 [属性] [索引] [注释]
    )[表类型][表的字符集设置][注释]

常用命令

SHOW CREATE DATABASE school  -- 查看创建数据库的语句
    SHOW CREATE TABLE student    -- 查看student数据表的定义语句
    DESC student                 -- 显示表的结构

## 1.5 数据表的类型 ##

常见的有两种类型：

*  INNODB 默认使用
 *  MYISAM 早些年使用的

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>MYISAM</th> 
   <th>INNODB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>事务支持</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>数据行锁定</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>外键约束</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td>表空间的大小</td> 
   <td>较小</td> 
   <td>较大，约为2倍</td> 
  </tr> 
 </tbody> 
</table>

**常规的使用操作**：

*  MYISAM 节约空间，速度较快
 *  INNODB 安全性高，事务的处理，多表多用户操作

> 数据表的存储位置

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

*  位置 : Mysql安装目录\\data 目录下

## 1.6 修改删除表 ##

> 修改表（ALTER TABLE）

*  **修改表名**：`alter table 旧表名 rename as 新表名`

-- 修改表名 alter table 旧表名 rename as 新表名
    ALTER TABLE teacher RENAME AS teacher1

*  **添加字段**：`alter table 表名 add 字段名 列属性[属性]`

-- 增加表的字段 alter table 表名 ADD 字段名 列属性
    ALTER TABLE teacher1 ADD age INT(11)

*  **修改字段**：
    
     *  `alter table 表名 modify 字段名 列属性[属性]`
     *  `alter table 表名 change 旧字段名 列属性[属性]`

-- 修改表的字段 (1.重命名 2.修改约束)
    -- ALTER TABLE 表名 MODIFY 字段名 列属性[]
    ALTER TABLE teacher1 MODIFY age VARCAHAR(11)  -- 修改约束
    -- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
    ALTER TABLE teacher1 CHANGE age age1 INT(1)  -- 重命名字段

*  **删除字段**：`alter table 表名 drop 字段名`

-- 删除表的字段 ALTER TABLE 表名 drop 字段名
    alter table teacher1 drop age1

最终结论

*  change 用来字段重命名，不能修改字段类型和约束
 *  modify 不用来字段重命名，只能修改字段类型和约束

> 删除表

*  **删除表**：`DROP TABLE [IF EXISTS] 表名`

-- 删除表
    DROP TABLE [IF EXISTS] teacher1

# 2.MySQL数据管理 #

## 2.1 外键(了解) ##

*  创建外键

> 方式一: 创建子表同时创建外键

*  学生表的gradeid字段，要去引用年级表的 gradeid
    
     *  1.  定义外键key
     *  1.  给这个外键添加约束(执行引用)

-- 年级表
    create table `grade`(
        `gradeid` int(10) not null auto_increment comment '年纪id',
        `gradename` varchar(50) noy null comment '年级名称',
        primary key(`gradeid`)
        
    )engine=innodb default charset = utf8;
    
    -- 学生表
    -- 学生表的gradeid字段，要去引用年级表的 gradeid
    -- 1. 定义外键key
    -- 2. 给这个外键添加约束(执行引用)
    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` DATETIME DEFAULT NULL COMMENT '出生日期',
        `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
        `gradeid` int(10) noy null comment '学生的年级',
        `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
         PRIMARY KEY(`id`), 
    
         -- 1. 定义外键 FK_gradeid
         KEY `FK_gradeid`(`gradeid`),
         -- 2. 给这个外键添加约束
         CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) references `grade`(`gradeid`)
    )ENGINE = INNODB DEFAULT CHARSET = utf8;

> 方式二：创建表成功后，添加外键约束

-- 创建表的时候没有外键关系
    alter table `studnet` add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`),

以上操作都是物理外键，数据库级别的外键，我们不建议使用！

最佳实践

*  数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)
 *  我们想使用多张表的数据，想使用外键(程序去实现)

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

## 2.2 DML语言(记住) ##

**数据库意义**：数据存储，数据管理

**DML语言**：数据操作语言

*  Insert
 *  update
 *  delete

## 2.3 添加 ##

> insert

**语法**：

-- 插入数据(添加)
    insert into 表名([字段名1,字段2,字段3]) values('值1'),('值2'),('值3');

**注意**：

*  字段或值之间用英文逗号隔开
 *  可同时插入多条数据，values 后用英文逗号隔开
 *  由于主键自增我们可以省略(如果不写表的字段，它就会一一匹配)
 *  一般写插入语句，我们一定要数据和字段一一对应！
 *  字段是可以省略的，但是后面的值必须要一一对应

-- 向`grade`表中的`gradename`字段插入
    insert into `grade`(`gradename`) values('大四');
    
    -- 插入多个字段
    insert into `grade`(`gradename`) 
    values('大二'),('大一');
    
    -- 数据和字段一一对应！
    insert into `student`(`name`,`pwd`,`sex`) values('张三','aaaa','男');

## 2.4 修改 ##

> update

**语法**：

update 表名 set colnum_name = value,[.....] where [条件]

**注意事项**：

1.  colnum\_name 是数据库的列，尽量带上\`\`
2.  条件，筛选的条件，如果没有指定，则会修改所有的列
3.  value ，是一个具体的值，也可以是一个变量
4.  多个设置的属性之间，使用英文逗号隔开

update `student` set `name`='狂神' where id = 1;
    
    -- 不指定条件的情况下，会改动所有表！
    update `student` set `name`='长江7号'; 
    
    -- 修改多个属性
    update `student` set `name`='狂神',`email`='12123@qq.com' where id between 2 and 5;
    
    -- 通过多个条件定位数据 
    update `student` set `name`='长江7号' where `name`='狂神' and `sex`='男';

<table> 
 <thead> 
  <tr> 
   <th>操作符</th> 
   <th>含义</th> 
   <th>范围</th> 
   <th>结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
   <td>5=6</td> 
   <td>false</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt;或 !=</td> 
   <td>不等于</td> 
   <td>5&lt;&gt;6</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>between…and…</td> 
   <td>在某个范围内</td> 
   <td>[2,5]</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>and</td> 
   <td>&amp;&amp;</td> 
   <td>5&gt;1 and 1&gt;2</td> 
   <td>false</td> 
  </tr> 
  <tr> 
   <td>or</td> 
   <td>||</td> 
   <td>5&gt;1or 1&gt;2</td> 
   <td>true</td> 
  </tr> 
 </tbody> 
</table>

## 2.5 删除 ##

> delete 命令

语法: `delete from 表名 [where 条件]`

-- 删除数据,如不指定条件则删除该表的所有列数据
    delete from `student`;
    
    -- 删除指定数据
    delete from `student` where id = 1;

> truncate 命令

作用: 完全清空一个数据库表，表的结构和索引约束不会变

-- 清空student 表
    truncate `student`;

> delete 和 truncate 区别

*  **相同点**: 都能删除数据，都不会删除表结构，但truncate速度更快
 *  **不同点**：
    
     *  truncate 重新设置 自增列，计数器会归零
     *  truncate 不会影响事务

# 3. DQL 查询数据(最重点) #

## 3.1DQL ##

(Date Query Language): 数据查询语言

*  所有的查询操作都用它 ，`select`
 *  简单 和 复杂的查询都能做
 *  **数据库中最核心的语言，最重要的语句**

## 3.2Select语句 ##

**基本语法**：`select 字段 from 表`

-- 查询全部学生
    select * from student;
    
    -- 查询指定字段
    select `StudentNo`,`StudentName` from student;

> AS子句作为别名

作用：

*  可给数据列取一个新别名
 *  可给表取一个新别名
 *  也可以给查询结果取一个新别名

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名(AS关键词可以省略)
    select `StudentNo` As 学号,`StudentName` AS 学生姓名 from student AS S
    
    -- 函数 拼接字符串Concat(a,b)
    select concat('姓名',StudentName) AS 新名字 from student

语法： `SELECT 字段1,字段2,..... FROM 表`

## 3.3去重distinct ##

作用：去掉 select 查询返回的记录结果中重复的记录（返回所有列的值都相同），只返回一条

-- 查询一下有哪些同学参加了考试，成绩
    select * from  result;   -- 查询全部的考试成绩
    select `StudentNo` from result;   -- 查询有哪些同学参加了考试
    select distinct `StudentNo` from result;   -- 发现重复数据，去重

> 数据库的列(表达式)

*  select `表达式` from 表
 *  **数据库中的表达式**: 文本值 ，列 ， Null ，函数 ， 计算表达式 ， 系统变量

select version() -- 查询系统版本(函数)
    select 100*3-1 AS 计算结果   -- 用来计算(表达式)
    select @@auto_increment_increment -- 查询自增的步长(变量)
    
    -- 学员考试成绩 + 1 查看
    select `StudentNo`,`StudentResult` + 1 AS '提分后' from result;

## 3.4where条件子句 ##

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

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

> 逻辑运算符

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>语法</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>and &amp;&amp;</td> 
   <td>a and b a&amp;&amp;b</td> 
   <td>逻辑与，两个都为真，结果为真</td> 
  </tr> 
  <tr> 
   <td>or ||</td> 
   <td>a or b a || b</td> 
   <td>逻辑或，其中一个为真，则结果为真</td> 
  </tr> 
  <tr> 
   <td>Not ！</td> 
   <td>not a ！a</td> 
   <td>逻辑非，真为假，假为真</td> 
  </tr> 
 </tbody> 
</table>

尽量使用英文字母

-- 查询考试成绩在 95 ~ 100分之间的
    select studentNo,`StudentResult` 
    from result 
    where StudentResult>=95 and StudentResult<=100

## 3.5模糊查询 ##

> 模糊查询：比较运算符

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>语法</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>is null</td> 
   <td>a is null</td> 
   <td>如果操作符为null，结果为真</td> 
  </tr> 
  <tr> 
   <td>is not null</td> 
   <td>a is not null</td> 
   <td>如果操作符不为null，结果为真</td> 
  </tr> 
  <tr> 
   <td>between</td> 
   <td>a between and c</td> 
   <td>若a在b和c之间，则结果为真</td> 
  </tr> 
  <tr> 
   <td><strong>like</strong></td> 
   <td>a like b</td> 
   <td>SQL匹配，如果a匹配b，则结果为真</td> 
  </tr> 
  <tr> 
   <td><strong>in</strong></td> 
   <td>a in(a1,a2,a3…)</td> 
   <td>假设a在a1或者a2…，则结果为真</td> 
  </tr> 
 </tbody> 
</table>

-- 除了1000号同学,要其他同学的成绩
    SELECT studentno,studentresult
    FROM result
    WHERE studentno!=1000;
    
    -- 使用NOT
    SELECT studentno,studentresult
    FROM result
    WHERE NOT studentno=1000;
    
    -- 查询 1001，1002，1003号学员
    select `Sno`,`Sname` 
    from `student`
    where `Sno` in (1001,1002,1003);
    
    
    -- 查询在北京的学生
    select `Sno`,`Sname` 
    from `student`
    where `Address` in ('安徽','河南洛阳');
    
    
    
    -- 查询姓刘的同学的学号及姓名
    -- 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 '%嘉%';
    
    -- 查询出生日期没有填写的同学
    -- 不能直接写=NULL , 这是代表错误的 , 用 is null
    SELECT studentname FROM student
    WHERE BornDate IS NULL;
    
    -- 查询出生日期填写的同学
    SELECT studentname FROM student
    WHERE BornDate IS NOT NULL;
    
    -- 查询没有写家庭住址的同学(空字符串不等于null)
    SELECT studentname FROM student
    WHERE Address='' OR Address IS NULL;

*  注意： `% _`只能在like里面使用
 *  `in` 里面是确切的集合

## 3.6连接查询 ##

*  内连接`inner join` ：查询两个表中的结果集中的交集
 *  左外连接`left join`：以左表为基准，右边表来一一匹配，匹配不上的，返回左表的记录，右表以NULL填充
 *  右外连接`right join`：以右表为基准，左边表来一一匹配，匹配不上的，返回右表的记录，左表以NULL填充

数据库的搭建如下：

*  创建一个school数据库
 *  创建学生表student
 *  创建年级表grade
 *  创建科目表subject
 *  创建成绩表result

-- 创建一个school数据库
    CREATE DATABASE IF NOT EXISTS `school`;
    
    -- 创建学生表
    CREATE TABLE IF NOT EXISTS `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;
    
    -- 创建年级表
    CREATE TABLE IF NOT EXISTS `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;
    
    -- 创建科目表
    CREATE TABLE IF NOT EXISTS `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;
    
    
    -- 创建成绩表
    CREATE TABLE IF NOT EXISTS `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;

> JOIN 对比

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center 2]

> inner join

*  **分析查询的字段来自哪些表 (来自两个表就用连接查询)**
 *  **确定交叉点(这两个表中哪个数据是相同的)**
 *  **判断条件: 学生表中的 studentNo = 成绩表中的 studentNo**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center 3]

查询参加了考试的同学的学号,姓名,科目编号,分数

1.  **学号,姓名 studentNo,studentName 在student表中**
2.  **科目编号,分数 SubjectNo,StudentResult 在result表中**
3.  **两个表中 studentNo 是相同的**

select t1.studentNo,studentName,SubjectNo,StudentResult
    from student as t1
    inner join result as t2
    -- 既在student表里查,student表连接result,
    -- 两个表连起来,都有studentNo,所以要指向一个表的studentNo查
    on t1.studentNo = t2.studentNo;

> right join

select t1.studentNo,studentName,SubjectNo,StudentResult
    from student  t1
    right join result t2
    on t1.studentNo = t2.studentNo;

> left join

select t1.studentNo,studentName,SubjectNo,StudentResult
    from student  t1
    left join result t2
    on t1.studentNo = t2.studentNo;

*  左表是 `student`,先从 `student`里面查学生学号，学生姓名
 *  即使右表`result`表中没有和学生学号,学生姓名匹配的科目编号，分数
 *  也会返回所有的值，只是科目编号、分数为null

<table> 
 <thead> 
  <tr> 
   <th>studentNo</th> 
   <th>studentName</th> 
   <th>SubjectNo</th> 
   <th>StudentResult</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1001</td> 
   <td>孙大圣</td> 
   <td>null</td> 
   <td>null</td> 
  </tr> 
 </tbody> 
</table>

## 3.7总结与区别 ##

<table> 
 <thead> 
  <tr> 
   <th>操作</th> 
   <th>描述</th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>inner join</td> 
   <td>如果表中至少有一个匹配，就返回行</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>left join</td> 
   <td>会从左表中返回所有的值，即使右表中没有匹配</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>right join</td> 
   <td>会从右表中返回所有的值，即使左表中没有匹配</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

-- 查询缺考的同学
    select t1.studentNo,studentName,SubjectNo,StudentResult
    from student  t1
    left join result t2
    on t1.studentNo = t2.studentNo
    where StudentResult = null;
    
    
    -- 查询了参加考试的同学信息: 学号,学生姓名,科目名,考试成绩
    -- 1.学号,学生姓名 student表
    -- 2.科目名 subject表
    -- 3.分数 result表
    
    
    
    
    -- 具体实现
    select t1.studentNo,studentName,SubjectName,StudentResult
    from student t1
    right join result t2
    on t1.studentNo = t2.studentNo
    -- 查询考试的信息,要以考试那张表去连,左连就是以左边的表为基准
    -- 再在上面所查询的结果中连接第三张表
    inner join subject t3
    on t2.SubjectNo = t3.SubjectNo;

**语法**：

*  `join(连接的表) on(判断条件)` 连接查询
 *  `where` 等值查询

## 3.8自连接(了解) ##

数据库建立：

*  创建分类表
 *  在分类表中插入数据

-- 创建分类表
    CREATE TABLE IF NOT EXISTS `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,'办公信息');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center 4]

--------------------

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

**父类**

<table> 
 <thead> 
  <tr> 
   <th>categoryid</th> 
   <th>categoryName</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>信息技术</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>软件开发</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>美术设计</td> 
  </tr> 
 </tbody> 
</table>

**子类**

<table> 
 <thead> 
  <tr> 
   <th>pid</th> 
   <th>categoryid</th> 
   <th>categoryName</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>4</td> 
   <td>数据库</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>8</td> 
   <td>办公信息</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>6</td> 
   <td>web开发</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>7</td> 
   <td>ps技术</td> 
  </tr> 
 </tbody> 
</table>

解释：数据库的`pid`是3，说明数据库在软件开发下，数据库自己的分类id`categoryid`是4

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

<table> 
 <thead> 
  <tr> 
   <th>父类</th> 
   <th>子类</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>信息技术</td> 
   <td>办公信息</td> 
  </tr> 
  <tr> 
   <td>软件开发</td> 
   <td>数据库</td> 
  </tr> 
  <tr> 
   <td>软件开发</td> 
   <td>web开发</td> 
  </tr> 
  <tr> 
   <td>美术设计</td> 
   <td>ps技术</td> 
  </tr> 
 </tbody> 
</table>

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

select a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
    from `category` AS a,`category` AS b
    where a.`categoryid` = b.`pid`;

![在这里插入图片描述][20210613155705393.png_pic_center]

## 3.9分页和排序 ##

*  分页 `limit`
 *  排序 `order by`
    
     *  升序：`ASC`
     *  降序：`DESC`

--------------------

**排序语法**: `order by 通过哪个字段排序 怎么排`

-- 通过科目成绩升序排列
    order by StudentResult ASC;

--------------------

**分页语法**：

*  完整语法：`limit 起始下标,页面的大小`
 *  缺省语法：`limit 5` 这是取前5
 *  注意：mysql当中`limit`在`order by`之后执行！！
 *  第一页 ,显示5条数据
    
     *      limit 0,5;
 *  第二页 ,显示5条数据
    
     *      limit 5,5;
 *  第三页
    
     *      limit 10,5;
 *  第n页
    
     *      limit(n-1) * pageSize,pageSize
     *  `pageSize`：页面大小
     *  `(n-1) * pageSize`： 起始值
     *  数据总数/页面大小 = 总页数

例题：

查询JAVA第一学年课程成绩排名前十的学生，并且分数要大于80的学生信息(学号,姓名,课程名称,考试成绩)

*  学号,姓名 ➡ 在student表
 *  课程名称 ➡ 在subject表
 *  考试成绩 ➡ 在result表

select t1.studentNo,StudentName,SubjectName,StudentResult
    from student t1
    inner join result t2
    on t1.StudentNo = t2.StudentNo
    inner join subject t3
    on t3.SubjectNo = t2.SubjectNo
    where SubjectName = 'JAVA第一学年' and StudentResult >= 80;
    order by studentResult DESC
    limit 0,10;

# 4.MySQL函数 #

## 4.1常用函数（了解） ##

1.  **数学运算**

-- 数学运算
    select ABS(-8);        -- 绝对值8
    select celiling(9.4)   -- 向上取整 10
    select floor(9.4)      -- 向下取整 9
    select rand()          -- 返回一个 0~1 之间的随机数
    select sing(10)        -- 判断一个数的符号 0返回0 负数返回-1,正数返回1

1.  **字符串函数**

-- 字符串函数
    select char_length('即使再小的帆也能远航')   -- 字符串长度 10
    select concat('我','要','加油')            -- 拼接字符串
    select lower('KUANG')    -- 转小写
    select upper('kuang')    -- 转大写
    select instr('kuangshen','h') -- 返回第一次出现的子串的索引
    select replace('坚持就能成功','坚持','努力')  -- 替换出现的指定字符串
    select substr('坚持就能成功',2,3) -- 返回指定的字符串(源字符串，截取的位置，截取的长度) 
    select reverse('Hello')    -- 反转字符串

1.  **时间和日期函数(记住)**

-- 时间和日期函数(记住)
    select current_data()   -- 获取当前日期
    select curdate()        -- 获取当前日期
    select now()            -- 获取当前的时间
    select localtime()      -- 本地时间
    select sysdate()        -- 系统时间
    select year(now())      -- 年
    select month(now())     -- 月
    select day(nowue())     -- 日

## 4.2聚合函数 ##

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>COUNT()</td> 
   <td>返回满足Select条件的<strong>记录总和数</strong></td> 
  </tr> 
  <tr> 
   <td>SUM()</td> 
   <td>返回数字字段或表达式列作统计，返回一列的总和</td> 
  </tr> 
  <tr> 
   <td>AVG()</td> 
   <td>通常为数值字段或表达列作统计，返回一列的平均值</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td>可以为数值字段，字符字段或表达式列作统计，返回最大的值</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>可以为数值字段，字符字段或表达式列作统计，返回最小的值</td> 
  </tr> 
 </tbody> 
</table>

-- count(指定列/字段) 
    select count(Studentname) from student;
    -- count(*) 
    select count(*) from  student;
    -- count(1) 推荐
    select count(1) from student;

上述三种sql语句返回的结果是相同的，都是有多少个学生姓名，那么它们有什么区别呢？

*  `count(指定列/字段)` 会统计该字段在表中出现的次数，忽略字段为null 的情况。**即不统计字段为null的记录。**
 *  `count(*)`包括了所有的列，相当于行数，在统计结果的时候，**包含字段为null 的记录**
 *  `count(1)` 用1代表代码行，在统计结果的时候，**包含字段为null 的记录**

--------------------

下面它们之间的一些对比：

1.  在表没有主键时，`count(1)`比`count(*)`快
2.  有主键时，主键作为计算条件，`count(主键)`效率最高
3.  若表格只有一个字段，则`count(*)`效率较高

--------------------

其他聚合函数用法

select sum('StudentResult') AS 总和 from result;
    select avg('StudentResult') AS 平均分 from result;
    select max('StudentResult') AS 最高分 from result;
    select min('StudentResult') AS 最低分 from result;

## 4.3MD5加密（扩展） ##

MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。

**实现数据加密**

1.  新建一个表 `testmd5`

CREATE TABLE `testmd5` (
        `id` INT(4) NOT NULL,
        `name` VARCHAR(20) NOT NULL,
        `pwd` VARCHAR(50) NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8

1.  插入一些数据

INSERT INTO testmd5 VALUES
    (1,'kuangshen','123456'),
    (2,'1 qinjiang','456789')

1.  如果我们要对pwd这一列数据进行加密，语法是：

update testmd5 set pwd = md5(pwd);

1.  如果单独对某个用户(如kuangshen)的密码加密

INSERT INTO testmd5 VALUES
    (3,'kuangshen2','123456')
    update testmd5 set pwd = md5(pwd) 
    where name = 'kuangshen2';

1.  插入新的数据自动加密

INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));

1.  查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）

SELECT * FROM testmd5 
    WHERE `name`='kuangshen' AND pwd=MD5('123456');

[MySQL]: https://www.bilibili.com/video/BV1NJ411J79W
[MySQL 1]: https://blog.csdn.net/Augenstern_QXL/article/details/117232602
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center]: /images/20221005/9b1b745e115f471caa5ddabab020aaa3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221005/8417e2380a794aa098b4195f8ee6206d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221005/5757925cf6cc4d17b6dc91b484a040e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221005/18554512ecae4283bbdde96f12d311e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0F1Z2Vuc3Rlcm5fUVhM_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221005/dc49af29fb314dd7a8f467a4e62680fa.png
[20210613155705393.png_pic_center]: /images/20221005/be72c1ab189b4cc6847d4b56ef138bc6.png