肝了五万字把SQL数据库从基础到高级所有命令写的明明白白，内容实在丰富，MySQL这一篇就够了

MySQL教程，全文累计 50000 字，思维导图 n 张，帮助大家快速学习，原创不易，如果觉得不错可以关注博主，一起进步 🦢，如需完整脑图和pdf可以评论区留言，博主看到会第一时间回复，这里是小周，期待你的关注

在这里插入图片描述

文章目录

1. 基本操作 😀

对mysql数据库的基本操作，启动，关闭，登录，退出，帮助等等……

脑图

在这里插入图片描述

启动mysql服务

net start mysql

C:\Windows\System32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

关闭mysql服务

net stop mysql

C:\Windows\System32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

登录mysql服务

注意，地址就是ip，本地可以写localhost和127.0.0.1，端口就是mysql启动服务占用的端口号默认是3306

mysql -h 地址 -P 端口 -u root -p 密码

C:\Windows\System32>mysql -h localhost -P 3306-u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands endwith;or \g.
Your MySQL connection id is3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c)2000,2017, Oracle and/or its affiliates.All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type'help;'or'\h'for help.Type'\c'to clear the current input statement.

退出mysql登录

ctrl + z 回车
exit
quit

显示哪些线程正在运行

show processlist;

mysql>show processlist;+----+------+-----------+------+---------+------+----------+------------------+| Id |User| Host      | db   | Command |Time| State    | Info             |+----+------+-----------+------+---------+------+----------+------------------+|4| root | localhost |NULL| Query   |0|starting|show processlist |+----+------+-----------+------+---------+------+----------+------------------+1rowinset(0.03 sec)

查看帮助

help 关键字;

2. 数据库操作 💻

脑图

对数据库的操作，包括查看当前使用的哪个数据库，使用数据库，查看所有数据库，查看当前时间，查看当前用户，查看数据库版本，查看创建数据库语句，创建数据库，以及删除数据库等围绕着数据库的相关操作
在这里插入图片描述

查看当前使用数据库

select database();

mysql>selectdatabase();+------------+|database()|+------------+|NULL|+------------+1rowinset(0.03 sec)

查看所有数据库

show databases;

使用数据库

use 数据库;

mysql>use db12;Database changed

查看当前时间

select now();

mysql>selectnow();+---------------------+|now()|+---------------------+|2022-05-2000:01:35|+---------------------+1rowinset(0.03 sec)

查看当前用户

select user();

mysql>selectuser();+--------+|user()|+--------+| root@  |+--------+1rowinset(0.00 sec)

查看数据库版本

select version();

mysql>select version();+-----------+| version()|+-----------+|5.7.19|+-----------+1rowinset(0.04 sec)

查看创建数据库信息

show create database 数据库名;

mysql>showcreatedatabase db12;+----------+---------------------------------------------------------------+|Database|CreateDatabase|+----------+---------------------------------------------------------------+| db12     |CREATEDATABASE`db12`/*!40100 DEFAULT CHARACTER SET utf8 */|+----------+---------------------------------------------------------------+1rowinset(0.00 sec)

创建数据库

基本写法

create database 数据库名;

mysql>createdatabase db14;
Query OK,1row affected (0.03 sec)

创建数据库指定utf8编码

create database 数据库名 charset utf8;

mysql>createdatabase db15 charset utf8;
Query OK,1row affected (0.03 sec)

不存在再创建数据库

create database if not exists 数据库名;

mysql>createdatabaseifnotexists db15;
Query OK,1row affected,1 warning (0.00 sec)

删除数据库

直接删除

drop database 数据库名;

mysql>dropdatabase db14;
Query OK,0rows affected (0.05 sec)

判断删除

drop database if exists 数据库名;

mysql>dropdatabaseifexists db15;
Query OK,0rows affected (0.00 sec)

3. 表操作 ❤️

脑图

在这里插入图片描述

查看所有表

show tables;

mysql>showtables;
Empty set(0.00 sec)

mysql>showtablesfrom db12;
Empty set(0.00 sec)

创建班级表 grade

id 主键
grade_name 班名 varchar(100)

mysql>createtable grade(-> id intprimarykeyauto_increment,-> grade_name varchar(100)comment"班级名字"->);
Query OK,0rows affected (0.06 sec)

创建学生表 stu，添加外键约束

id 主键
name 学生姓名 varchar(100) not null
sex 学生性别

创建数据表 stu ，并在表 stu 上创建外键约束，让它的键 gradeId 作为外键关联到表 grade 的主键 id ，SQL这样写

[CONSTRAINT<外键名>]FOREIGNKEY 字段名 [，字段名2，…]REFERENCES<主表名> 主键列1[，主键列2，…]

CREATETABLE stu (
  id INTPRIMARYKEYAUTO_INCREMENT,
  NAME VARCHAR(100)NOTNULL,
  age INT(3),
  bir DATE,
  english INT(3),
  chinese INT(3),
  gradeId INT,CONSTRAINT fk_stu_grade FOREIGNKEY(gradeId)REFERENCES grade (id));

注意：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。例如，两者都是 INT 类型，或者都是 CHAR 类型。如果不满足这样的要求，在创建从表时，就会出现“ERROR 1005(HY000): Can’t create table”错误

在修改表时添加外键约束

假如在创建表时，没有添加外键约束，那么可以在创建表后通过修改表给表添加外键约束，语法如下

ALTERTABLE<数据表名>ADDCONSTRAINT<外键名>FOREIGNKEY(<列名>)REFERENCES<主表名>(<列名>);

CREATETABLE stu (
  id INTPRIMARYKEYAUTO_INCREMENT,
  NAME VARCHAR(100)NOTNULL,
  age INT(3),
  bir DATE,
  english INT(3),
  chinese INT(3),
  gradeId INT);ALTERTABLE stu 
  ADDCONSTRAINT fk_stu_grage FOREIGNKEY(gradeId)REFERENCES grade (id);

注意：在为已经创建好的数据表添加外键约束时，要确保添加外键约束的列的值全部来源于主键列，并且外键列不能为空。

删除外键约束

通过外键名删除外键，这里演示一下，删除之后会继续添加上的，因为需要这个外键约束

ALTERTABLE<表名>DROPFOREIGNKEY<外键约束名>;

ALTERTABLE stu DROPFOREIGNKEY fk_stu_grage;

查看表结构

DESC stu;

显示存储引擎的状态信息

SHOW ENGINES;

修改表

修改表名 rename to

mysql>altertable stu renameto stus;
Query OK,0rows affected (0.03 sec)

mysql>showtables;+----------------+| Tables_in_db12 |+----------------+| grade          || stus           |+----------------+2rowsinset(0.00 sec)

添加一列 add

alter table 表名 add 列名列数据类型;

修改数据类型 modify

alter table 表名 modify 列名新的数据类型;

修改字段位置置顶 first

alter table 表名 modify 字段名字段属性 first;

修改字段位置再什么之后 after

alter table 表名 modify 字段字段属性 after 字段;

修改列名和数据类型 change

alter table 表名 change 原列名新列名新数据类型;

删除列 drop

alter table 表名 drop 列名;

删除表

直接删除

drop table 表名;

判断存在再删除

drop table if exists 表名;

4. 添加数据 insert 📗

脑图

在这里插入图片描述

语法

INSERT INTO 表名(字段，字段……) VALUES(与字段对应);

mysql>desc stus;+---------+--------------+------+-----+---------+----------------+| Field   |Type|Null|Key|Default| Extra          |+---------+--------------+------+-----+---------+----------------+| id      |int(11)|NO| PRI |NULL|auto_increment|| gradeId |int(11)| YES  | MUL |NULL||| NAME    |varchar(100)|NO||NULL||| age     |int(3)| YES  ||NULL||| bir     |date| YES  ||NULL||| english |int(3)| YES  ||NULL||| chinese |int(3)| YES  ||NULL||+---------+--------------+------+-----+---------+----------------+7rowsinset(0.00 sec)

mysql>desc grade;+------------+--------------+------+-----+---------+----------------+| Field      |Type|Null|Key|Default| Extra          |+------------+--------------+------+-----+---------+----------------+| id         |int(11)|NO| PRI |NULL|auto_increment|| grade_name |varchar(100)| YES  ||NULL||+------------+--------------+------+-----+---------+----------------+2rowsinset(0.01 sec)

给指定列添加数据

INSERTINTO stus(NAME,age,bir,english,gradeId)VALUES("周棋洛",18,"2002-06-01",78,1);

mysql>select*from stus;+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|1|1| 周棋洛    |18|2002-06-01|78|NULL|+----+---------+-----------+------+------------+---------+---------+1rowinset(0.00 sec)

给全部列添加数据

mysql>insertinto stus values(2,2,"张郁苗",18,"2002-11-07",130,120);
Query OK,1row affected (0.03 sec)

给全部列添加数据时，可以简写，不建议，因为代码可读性变差，推荐写成下面这样，一眼就能知道要插入的字段是什么

mysql>insertinto stus(gradeId,name,age,bir,english,chinese)values->(1,"小猪佩奇",6,"2015-06-10",34,23);
Query OK,1row affected (0.03 sec)
mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2002-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|23|+----+---------+--------------+------+------------+---------+---------+3rowsinset(0.00 sec)

一次添加多条数据

mysql>insertinto stus(gradeId,name,age,bir,english,chinese)values->(3,"猪妈妈",7,"2012-09-11",56,78),->(2,"谷歌",6,"2000-11-11",100,110);
Query OK,2rows affected (0.00 sec)
Records: 2  Duplicates: 0Warnings: 0

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2002-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|23||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 谷歌         |6|2000-11-11|100|110|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.00 sec)

查看最后一次添加的主键值

select last_insert_id();

mysql>select last_insert_id();+------------------+| last_insert_id()|+------------------+|5|+------------------+1rowinset(0.03 sec)

5. 更新数据 update 📖

脑图

在这里插入图片描述

语法

update 表 set 要更新字段 = 要更新成啥 where 更新条件;

注意：如果不使用 where 字句进行限制，就会把表里所有记录都修改了，凉凉了，可别怪本帅哥没提醒你

更新一个字段

修改stus表名字叫周棋洛的生日，改为 2001-06-01

mysql>update stus set bir ="2001-06-01"where name ="周棋洛";
Query OK,1row affected (0.02 sec)Rowsmatched: 1  Changed: 1Warnings: 0

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|23||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 谷歌         |6|2000-11-11|100|110|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.00 sec)

更新多个字段

把id为6的名字改为胡歌，年龄改为24

mysql>update stus set name ="胡歌",age =24where id =6;
Query OK,1row affected (0.00 sec)Rowsmatched: 1  Changed: 1Warnings: 0

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|23||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 胡歌         |24|2000-11-11|100|110|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.00 sec)

ignore关键字

当使用update语句进行多行更新，如果在更新过程中出错了，则整个update语句都会被取消，恢复到更新之前，如果你想即使发生错误，也继续进行更新，可以使用

ignore

关键字

语法：

updateignore 表 set 要更新字段 = 要更新成啥 where 更新条件;

设置为null

当我们想要将记录的某个字段或多个字段改为空，就可以将它修改为 null ，而不是空字符串，mysql中的空为

null

例如：
修改id为3的中文成绩为null空

mysql>update stus set chinese =nullwhere id =3;
Query OK,1row affected (0.03 sec)Rowsmatched: 1  Changed: 1Warnings: 0

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 胡歌         |24|2000-11-11|100|110|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.00 sec)

6. 删除数据 delete 😨

脑图

在这里插入图片描述

语法

delete from 表 where 限制条件;

删除一行记录

删除id为6的数据

mysql>deletefrom stus where id =6;
Query OK,1row affected (0.03 sec)

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+5rowsinset(0.00 sec)

删除所有记录

警告：不要省略 where 子句，如果不使用 where 子句进行限制，别怪我没提醒你，你会把表中所有行全部干掉，准备后事吧！兄弟

deletefrom stus;

这里就不执行了

注意：delete语句从表中删除行，甚至删除表中所有行，但是，delete不删除表本身

删除所有记录（效率高）

如果你想从表中删除所有数据，不要使用delete，可以使用 truncate 表名，它完成相同的工作，但是速度更快，因为它实际上是删除原来的表并重新创建一张表，而不是逐行删除表中的数据

mysql>select*fromuser;+------+------+| id   | name |+------+------+|1| hah1 |+------+------+1rowinset(0.00 sec)

mysql>truncateuser;
Query OK,0rows affected (0.06 sec)

mysql>select*fromuser;
Empty set(0.00 sec)

在这里插入图片描述

无水印脑图

在这里插入图片描述

表结构

mysql>desc stus;+---------+--------------+------+-----+---------+----------------+| Field   |Type|Null|Key|Default| Extra          |+---------+--------------+------+-----+---------+----------------+| id      |int(11)|NO| PRI |NULL|auto_increment|| gradeId |int(11)| YES  | MUL |NULL||| NAME    |varchar(100)|NO||NULL||| age     |int(3)| YES  ||NULL||| bir     |date| YES  ||NULL||| english |int(3)| YES  ||NULL||| chinese |int(3)| YES  ||NULL||+---------+--------------+------+-----+---------+----------------+

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+

7. 简单查询

脑图

在这里插入图片描述

单列查询

select 列名 from 表;

从stus表中查询名字的字段

mysql>select name from stus;+--------------+| name         |+--------------+| 周棋洛       || 张郁苗       || 小猪佩奇     || 猪爸爸       || 猪妈妈       |+--------------+

多列查询

select 列名,列名…… from 表;

从stus表中查询名字和年龄这两个字段

mysql>select name,age from stus;+--------------+------+| name         | age  |+--------------+------+| 周棋洛       |18|| 张郁苗       |18|| 小猪佩奇     |6|| 猪爸爸       |8|| 猪妈妈       |7|+--------------+------+

查询所有列 *

select * from 表;

从stus表查询所有字段

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+

distinct去重

distinct关键字可以出去重复出现的内容，注意：不能部分使用distinct

mysql>selectdistinct age from stus;+------+| age  |+------+|18||6||8||7|+------+

起别名 as

我们在查询时，如果对原有字段名不满意，这时就可以通过as关键字对字段起别名，注意：as是可省略的，如下所示

mysql>select name as"姓名"from stus;+--------------+| 姓名         |+--------------+| 周棋洛       || 张郁苗       || 小猪佩奇     || 猪爸爸       || 猪妈妈       |+--------------+

mysql>select name "姓名"from stus;+--------------+| 姓名         |+--------------+| 周棋洛       || 张郁苗       || 小猪佩奇     || 猪爸爸       || 猪妈妈       |+--------------+

条件查询

大于判断 >

😀 : 查找stus表中年龄大于18岁的学生姓名

mysql>select name from stus where age >18;
Empty set(0.00 sec)

小于判断 <

😀 : 查找stus表中年龄小于18岁的学生姓名

mysql>select name from stus where age <18;+--------------+| name         |+--------------+| 小猪佩奇     || 猪爸爸       || 猪妈妈       |+--------------+

等于判断 =

😀 : 查找stus表中年龄等于18岁的学生姓名

mysql>select name from stus where age =18;+-----------+| name      |+-----------+| 周棋洛    || 张郁苗    |+-----------+

大于等于判断 >=

😀 : 查找stus表中年龄大于等于18岁的学生姓名

mysql>select name from stus where age >=18;+-----------+| name      |+-----------+| 周棋洛    || 张郁苗    |+-----------+

小于等于判断 <=

😀 : 查找stus表中年龄小于等于18岁的学生姓名

mysql>select name from stus where age <=18;+--------------+| name         |+--------------+| 周棋洛       || 张郁苗       || 小猪佩奇     || 猪爸爸       || 猪妈妈       |+--------------+

不等于判断 <> 或 !=

😀 : 查找stus表中年龄不等于18岁的学生姓名

mysql>select name from stus where age <>18;+--------------+| name         |+--------------+| 小猪佩奇     || 猪爸爸       || 猪妈妈       |+--------------+

mysql>select name from stus where age !=18;+--------------+| name         |+--------------+| 小猪佩奇     || 猪爸爸       || 猪妈妈       |+--------------+

BETWEEN … AND …包含判断

😀 : 查找stus表中年龄在10~18岁的学生姓名

mysql>select name,age from stus where age between8and20;+-----------+------+| name      | age  |+-----------+------+| 周棋洛    |18|| 张郁苗    |18|| 猪爸爸    |8|+-----------+------+

IN(…)

😀 : 查找stus表中年龄为8岁或7岁的学生姓名

mysql>select name,age from stus where age in(7,8);+-----------+------+| name      | age  |+-----------+------+| 猪爸爸    |8|| 猪妈妈    |7|+-----------+------+

IS NULL

😀 : 查找stus表中名字为空的学生信息

mysql>select*from stus where name isnull;
Empty set(0.02 sec)

IS NOT NULL

😀 : 查找stus表中名字不为空的学生信息

mysql>select*from stus where name isnotnull;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+

AND 或 &&

只有多个条件都成立才会返回真，否则就认为false

😀 : 查找stus表中年龄为18岁的周棋洛的学生信息

mysql>select*from stus where name ="周棋洛"AND age ="18";+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|1|1| 周棋洛    |18|2001-06-01|78|NULL|+----+---------+-----------+------+------------+---------+---------+

mysql>select*from stus where name ="周棋洛"&& age ="18";+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|1|1| 周棋洛    |18|2001-06-01|78|NULL|+----+---------+-----------+------+------------+---------+---------+

OR 或 ||

只要有一个条件成立就返回真，都不成立返回false

😀 : 查找stus表中名字叫周棋洛或者叫张郁苗的学生信息

mysql>select*from stus where name ="周棋洛"OR name ="张郁苗";+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|1|1| 周棋洛    |18|2001-06-01|78|NULL||2|2| 张郁苗    |18|2002-11-07|130|120|+----+---------+-----------+------+------------+---------+---------+

mysql>select*from stus where name ="周棋洛"|| name ="张郁苗";+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|1|1| 周棋洛    |18|2001-06-01|78|NULL||2|2| 张郁苗    |18|2002-11-07|130|120|+----+---------+-----------+------+------------+---------+---------+

8. 模糊查询

糊查询

需要使用

like

占位符，几个案例学会模糊查询，下面是stus表的数据

脑图

在这里插入图片描述

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+

% 多个任意字符

1.查询 stus 表中 name 以猪开头的学生信息

mysql>select*from stus where name like"猪%";+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|4|3| 猪爸爸    |8|2012-09-12|34|56||5|3| 猪妈妈    |7|2012-09-11|56|78|+----+---------+-----------+------+------------+---------+---------+

2.查询 stus 表中 name 以爸爸结尾的学生信息

mysql>select*from stus where name like'%爸爸';+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|4|3| 猪爸爸    |8|2012-09-12|34|56|+----+---------+-----------+------+------------+---------+---------+

3.查询 stus 表中 name 中包含猪的学生信息

mysql>select*from stus where name like'%猪%';+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+

_ 单个任意字符

4.查询 stus 表中 name 以猪开头的且名字长度为2的学生信息

mysql>select*from stus where name like'猪_';
Empty set(0.00 sec)

5.查询 stus 表中 name 以猪开头的且名字长度为3的学生信息

mysql>select*from stus where name like'猪__';+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|4|3| 猪爸爸    |8|2012-09-12|34|56||5|3| 猪妈妈    |7|2012-09-11|56|78|+----+---------+-----------+------+------------+---------+---------+

6.查询 stus 表中 name 以妈妈结尾的且名字长度为3的学生信息

mysql>select*from stus where name like'_妈妈';+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|5|3| 猪妈妈    |7|2012-09-11|56|78|+----+---------+-----------+------+------------+---------+---------+

7.查询 stus 表中 name 以张开头以苗结尾的且名字长度为3的学生信息

mysql>select*from stus where name like'张_苗';+----+---------+-----------+------+------------+---------+---------+| id | gradeId | NAME      | age  | bir        | english | chinese |+----+---------+-----------+------+------------+---------+---------+|2|2| 张郁苗    |18|2002-11-07|130|120|+----+---------+-----------+------+------------+---------+---------+

9. 排序查询

关键字：

order by

关系型数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出来的数据的顺序有意义，可以使用

order by 子句

对查询结果进行排序，

默认是升序

，即从小到大

ASC

，如果想要

降序

排序，则需要指定

DESC

脑图

在这里插入图片描述

ASC 升序

1. 查询stus表中名字字段，要求按照年龄的升序进行排序

mysql>select name from stus orderby age;+--------------+| name         |+--------------+| 小猪佩奇     || 猪妈妈       || 猪爸爸       || 周棋洛       || 张郁苗       |+--------------+

2. 查询stus表中名字,年龄字段，要求按照年龄大于7进行筛选之后按照年龄的升序进行排序

mysql>select name,age from stus where age >7orderby age;+-----------+------+| name      | age  |+-----------+------+| 猪爸爸    |8|| 周棋洛    |18|| 张郁苗    |18|+-----------+------+

DESC 降序

3. 查询stus表中名字字段，要求按照年龄的升序进行排序，如果年龄相同再按照英语成绩降序排列

mysql>select name from stus orderby age,english desc;+--------------+| name         |+--------------+| 小猪佩奇     || 猪妈妈       || 猪爸爸       || 张郁苗       || 周棋洛       |+--------------+

limit 选学

这个还没讲到，如果你不知道，可以跳过，后面会将到哦

4. 查询stus表中名字字段，要求按照年龄的升序进行排序，并通过limit返回一条数据，即年龄最小的

mysql>select name from stus orderby age asclimit1;+--------------+| name         |+--------------+| 小猪佩奇     |+--------------+

哦，原来小猪佩奇是最小的

位置问题

在使用 order by 子句对检索出的数据进行排序时，应该保证它是位于 from，如果有条件，应位于where之后，如果使用 limit ，它必须位于 order by 之后，使用子句的次序不对将产生错误消息

10. 聚合函数

MySQL 提供了5个聚合函数，聚合函数能够汇总数据，这些函数是高效设计的，它们返回结果一般比你自己在客户机应用程序中计算要快的多

脑图

在这里插入图片描述

AVG()

返回某列的平均值

查看stus表学生的平均英语成绩

mysql>selectavg(english)as avgEnglish from stus;+------------+| avgEnglish |+------------+|66.4000|+------------+

COUNT()

返回某列的行数

查看stus表一共有多少行

mysql>selectcount(*)as num from stus;+-----+| num |+-----+|5|+-----+

MAX()

返回某列的最大值

查看stus表中英语成绩最高分

mysql>selectmax(english)as score from stus;+-------+| score |+-------+|130|+-------+

MIN()

返回某列的最小值

查询stus表英语成绩的最低分

mysql>selectmin(english)as score from stus;+-------+| score |+-------+|34|+-------+

还不错，没有0蛋

SUM()

返回某列之和

查询stus表中学生英语成绩的总分

mysql>selectsum(english)as score from stus;+-------+| score |+-------+|332|+-------+

11. 分组查询

脑图

在这里插入图片描述

分组

对stus表的数据按照班级进行分组，并查看每个班都有多少人

mysql>select gradeId,count(*)as num from stus groupby gradeId;+---------+-----+| gradeId | num |+---------+-----+|1|2||2|1||3|2|+---------+-----+

对stus表的数据按照班级进行分组，查看每个班都有多少人以及班级英语平均分

mysql>select gradeId,avg(english)as english from stus groupby gradeId;+---------+----------+| gradeId | english  |+---------+----------+|1|56.0000||2|130.0000||3|45.0000|+---------+----------+

过滤

mysql>select gradeId,count(*)as num from stus groupby gradeId havingcount(*)>1;+---------+-----+| gradeId | num |+---------+-----+|1|2||3|2|+---------+-----+

having和where的区别

执行时间不一样：where是分组之前进行限定，不满足where条件，就不参与分组，而having是分组之后对结果进行过滤
可判断的条件不一样：where 不能对聚合函数进行判断，having可以
where > 聚合函数 > having

12. select子句查询顺序

select

from

where 行级过滤

group by 分组

having 组级过滤

order by 输出排序顺序

limit 要检索的条目数

13. 分页查询

关键字：

limit

MySQL中使用limit来限制返回的条目数

一个参数，从开始处返回几条数据
select * from XXX limit 5;

mysql>select id,name,age from stus limit3;+----+--------------+------+| id | name         | age  |+----+--------------+------+|1| 周棋洛       |18||2| 张郁苗       |18||3| 小猪佩奇     |6|+----+--------------+------+

两个参数，注意检索出来的数据第一行为0而不是1,所以下面语句意思就是，从第一条数据算起，查询3条数据
select * from XXX limit 0,3;

mysql>select id,name,age from stus limit0,2;+----+-----------+------+| id | name      | age  |+----+-----------+------+|1| 周棋洛    |18||2| 张郁苗    |18|+----+-----------+------+

mysql>select id,name,age from stus limit2,2;+----+--------------+------+| id | name         | age  |+----+--------------+------+|3| 小猪佩奇     |6||4| 猪爸爸       |8|+----+--------------+------+

假如规定一页有10条记录
当查询第一页时就是 limit 0,10;
当查询第二页时就是 limit 10,10;
当查询第三页时就是 limit 20,10;
所以总结一下，查询第n页数据时，

limit (n-1)*每页记录数,每页记录数;

14. 多表查询

脑图

在这里插入图片描述

试着查询两张表

mysql>select*from stus,grade;+----+---------+--------------+------+------------+---------+---------+----+--------------+| id | gradeId | NAME         | age  | bir        | english | chinese | id | grade_name   |+----+---------+--------------+------+------------+---------+---------+----+--------------+|1|1| 周棋洛       |18|2001-06-01|78|NULL|1| 高三一班     ||1|1| 周棋洛       |18|2001-06-01|78|NULL|2| 高三二班     ||1|1| 周棋洛       |18|2001-06-01|78|NULL|3| 高一二班     ||2|2| 张郁苗       |18|2002-11-07|130|120|1| 高三一班     ||2|2| 张郁苗       |18|2002-11-07|130|120|2| 高三二班     ||2|2| 张郁苗       |18|2002-11-07|130|120|3| 高一二班     ||3|1| 小猪佩奇     |6|2015-06-10|34|NULL|1| 高三一班     ||3|1| 小猪佩奇     |6|2015-06-10|34|NULL|2| 高三二班     ||3|1| 小猪佩奇     |6|2015-06-10|34|NULL|3| 高一二班     ||4|3| 猪爸爸       |8|2012-09-12|34|56|1| 高三一班     ||4|3| 猪爸爸       |8|2012-09-12|34|56|2| 高三二班     ||4|3| 猪爸爸       |8|2012-09-12|34|56|3| 高一二班     ||5|3| 猪妈妈       |7|2012-09-11|56|78|1| 高三一班     ||5|3| 猪妈妈       |7|2012-09-11|56|78|2| 高三二班     ||5|3| 猪妈妈       |7|2012-09-11|56|78|3| 高一二班     |+----+---------+--------------+------+------------+---------+---------+----+--------------+15rowsinset(0.04 sec)

为啥返回给我15条结果啊❓

这样写的多表查询会有

笛卡尔积

：就是A，B两个集合，取 A，B所有的组合情况，比如A有3条记录，B有6条，查询就会有3*6=18条数据，显然不合符需求，所以要消除无效数据

内连接

关键字：INNER JOIN

连接结果仅包含符合连接条件的行组合起来作为结果集，参与连接的两个表都应该符合连接条件使用关键词：INNER JOIN 连接多张表

显示内连接

使用

INNER JOIN

关键字，条件使用

on

关键字

mysql>select grade_name, name from grade innerjoin stus on stus.gradeId = grade.id;+--------------+--------------+| grade_name   | name         |+--------------+--------------+| 高三一班     | 周棋洛       || 高三二班     | 张郁苗       || 高三一班     | 小猪佩奇     || 高一二班     | 猪爸爸       || 高一二班     | 猪妈妈       |+--------------+--------------+

隐式内连接

内连接还有一种隐式的写法，即不需要显示的指定
INNER JOIN
关键字，需要注意，使用隐式内连接条件的关键字要使用
where
而不再是
on

mysql>select grade_name, name from grade,stus where stus.gradeId = grade.id;+--------------+--------------+| grade_name   | name         |+--------------+--------------+| 高三一班     | 周棋洛       || 高三二班     | 张郁苗       || 高三一班     | 小猪佩奇     || 高一二班     | 猪爸爸       || 高一二班     | 猪妈妈       |+--------------+--------------+

一般我们常用直接使用where关键词查询连接条件这样更方便简单

外连接

关键字：OUTER JOIN

左外连接

LEFT JOIN

左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL

语法：

LEFTJOINONLEFTOUTERJOINON

left join

是

left outer join

的简写

mysql>select name,grade_name from stus s leftouterjoin grade g on s.id = g.id;+--------------+--------------+| name         | grade_name   |+--------------+--------------+| 周棋洛       | 高三一班     || 张郁苗       | 高三二班     || 小猪佩奇     | 高一二班     || 猪爸爸       |NULL|| 猪妈妈       |NULL|+--------------+--------------+

mysql>select name,grade_name from stus s leftjoin grade g on s.id = g.id;+--------------+--------------+| name         | grade_name   |+--------------+--------------+| 周棋洛       | 高三一班     || 张郁苗       | 高三二班     || 小猪佩奇     | 高一二班     || 猪爸爸       |NULL|| 猪妈妈       |NULL|+--------------+--------------+

右外连接

RIGHT JOIN

右(外)连接，右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录。左表记录不足的地方均为NULL

语法：

RIGHTJOINONRIGHTOUTERJOINON

right join

是

right outer join

的简写

mysql>select name,grade_name from stus s rightjoin grade g on s.id = g.id;+--------------+--------------+| name         | grade_name   |+--------------+--------------+| 周棋洛       | 高三一班     || 张郁苗       | 高三二班     || 小猪佩奇     | 高一二班     |+--------------+--------------+

子查询

查询中嵌套查询，称嵌套查询为子查询

分类 (根据查询结果的不同，作用不同)

单行单列

作为条件值，使用 = != < > 等进行条件判断

语法：

select 字段列表 from 表 where 字段名 =(子查询);

例如：查询stus表名字叫周棋洛的在几班
1.首先，要在stus表中查询名字为周棋洛的 gradeId 是多少
2.根据查到的 gradeId 再去 grade 查询相对应的 grade_name

mysql>select gradeId from stus where name ="周棋洛";+---------+| gradeId |+---------+|1|+---------+

mysql>select grade_name from grade where id =1;+--------------+| grade_name   |+--------------+| 高三一班     |+--------------+

能不能把这两个sql语句合并为一个❓答案是毋庸置疑的，不可以，开玩笑的，哈哈，当然可以，要不也不说子查询了，开始合并

mysql>select grade_name from grade where id =(select gradeId from stus where name ="周棋洛");+--------------+| grade_name   |+--------------+| 高三一班     |+--------------+

多行单列

作为条件值，用 in 等关键字进行条件判断

语法：

select 字段列表 from 表 where 字段名 in(子查询);

多行多列

作为虚拟表

select 字段列表 from(子查询)where t;

这里是热爱动漫，热爱技术，热爱生活的小周，期待你的关注！
在这里插入图片描述

在这里插入图片描述

全文思维导图

整理了思维导图，方便小伙伴下载无水印思维导图，方便后期的复习
在这里插入图片描述

在这里插入图片描述

表介绍 🍉

学习之前，先说一下使用的表结构，以及相关数据

grade年级表

CREATETABLE`grade`(`id`int(11)NOTNULLAUTO_INCREMENT,`grade_name`varchar(100)DEFAULTNULLCOMMENT'班级名字',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8

grade表数据

mysql>select*from grade;+----+--------------+| id | grade_name   |+----+--------------+|1| 高三一班     ||2| 高三二班     ||3| 高一二班     |+----+--------------+

stus表结构

CREATETABLE`stus`(`id`int(11)NOTNULLAUTO_INCREMENT,`gradeId`int(11)DEFAULTNULL,`NAME`varchar(100)NOTNULL,`age`int(3)DEFAULTNULL,`bir`dateDEFAULTNULL,`english`int(3)DEFAULTNULL,`chinese`int(3)DEFAULTNULL,PRIMARYKEY(`id`),KEY`fk_stu_grage`(`gradeId`),CONSTRAINT`fk_stu_grage`FOREIGNKEY(`gradeId`)REFERENCES`grade`(`id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8

stus表数据

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+

在这里插入图片描述

15. 视图 🍎

思维导图 📌

在这里插入图片描述

视图概念

视图是虚拟的表，与包含数据的表不同，视图本身不包含数据，视图只包含使用时动态检索出的数据，如果你创建了复杂的视图或嵌套了视图，可能会发现性能严重下降

为什么使用视图？

为了保障数据安全性，提高查询效率

视图的使用规则和限制

与表一样，视图必须唯一命名
视图可以嵌套，从视图检索数据构造新的视图
有足够权限，才可以创建视图
对于视图创建的数量没有限制
视图不可以索引，触发器和默认值
视图可以和表一起使用

视图操作

创建视图

语法

createview 视图名 
as 
查询语句;

mysql>createview zhouql_grade_id asselect gradeId from stus where name ="周棋洛";

mysql>select*from zhouql_grade_id;+---------+| gradeId |+---------+|1|+---------+

查看创建视图语句

语法

showcreateview 视图名;

mysql>showcreateview zhouql_grade_id;+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+|View|CreateView| character_set_client | collation_connection |+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| zhouql_grade_id |CREATEALGORITHM=UNDEFINEDDEFINER=`skip-grants user`@`skip-grants host`SQL SECURITY DEFINERVIEW`zhouql_grade_id`ASselect`stus`.`gradeId`AS`gradeId`from`stus`where(`stus`.`NAME`='周棋洛')| utf8                 | utf8_general_ci      |+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

更新视图

1. 直接删除DROP再CREATE

直接删除视图，再创建

2. CREATE OR REPLACE VIEW

mysql>CREATEORREPLACEVIEW zhouql_grade_id
    ->as->select gradeId from stus
    ->where name ="张郁苗";
    
mysql>select*from zhouql_grade_id;+---------+| gradeId |+---------+|2|+---------+

删除视图

语法

dropview 视图名;

1. 直接删除

mysql>dropview zhouql_grade_id;
Query OK,0rows affected (0.00 sec)

2. 判断存在再删除

mysql>dropviewifexists zhouql_grade_id;
Query OK,0rows affected,1 warning (0.00 sec)

利用视图简化复杂的联结

如果要查询学生周棋洛在几班，首先要查询学生表 stus 中周棋洛的班级id，在去班级表 grade 中查询相应 id 对应的班级，sql语句如下

mysql>select gradeId from stus where name ="周棋洛";+---------+| gradeId |+---------+|1|+---------+

mysql>select grade_name from grade where id =1;+--------------+| grade_name   |+--------------+| 高三一班     |+--------------+

经过两条SQL语句，最终得出周棋洛同学是高三一班的

为了方便点，人们想把两条SQL语句合并为一条SQL，于是子查询来了，万物皆可嵌套，语句如下

mysql>select grade_name from grade where id =(select gradeId from stus where name ="周棋洛");+--------------+| grade_name   |+--------------+| 高三一班     |+--------------+

视图最常见的应用之一就是隐藏复杂的SQL，比如复杂的联结查询

1.创建视图 gradeName，该视图使用隐式内连接查询学生表的班级id与班级表的id相同的数据

mysql>createview gradeName
    ->as->select stus.name,stus.gradeId,grade.grade_name from stus,grade
    ->where stus.gradeId = grade.id;
Query OK,0rows affected (0.03 sec)

mysql>select*from gradeName;+--------------+---------+--------------+| name         | gradeId | grade_name   |+--------------+---------+--------------+| 周棋洛       |1| 高三一班     || 张郁苗       |2| 高三二班     || 小猪佩奇     |1| 高三一班     || 猪爸爸       |3| 高一二班     || 猪妈妈       |3| 高一二班     |+--------------+---------+--------------+5rowsinset(0.03 sec)

2.现在有了视图 gradeName 这张虚拟表作为中间表，想查询任意一个学生是几班的就非常舒服了，语句如下，你 get 到了吗 🍉

mysql>select name,grade_name
    ->from gradeName
    ->where name ="周棋洛";+-----------+--------------+| name      | grade_name   |+-----------+--------------+| 周棋洛    | 高三一班     |+-----------+--------------+

mysql>select name,grade_name
    ->from gradeName
    ->where name ="张郁苗";+-----------+--------------+| name      | grade_name   |+-----------+--------------+| 张郁苗    | 高三二班     |+-----------+--------------+

利用视图格式化检索出的数据

创建一个视图 stuNameAndAge，要求有一个字段 name_age，值是 stus 表中查询出的学生姓名加年龄，并按照年龄的降序排列，SQL如下

mysql>createview stuNameAndAge
    ->as->select concat(rtrim(name),rtrim(age))as name_age
    ->from stus
    ->orderby age;
Query OK,0rows affected (0.03 sec)

mysql>select*from stuNameAndAge;+---------------+| name_age      |+---------------+| 小猪佩奇6|| 猪妈妈7|| 猪爸爸8|| 周棋洛18|| 张郁苗18|+---------------+

验证视图是动态的虚拟表

可以

简单验证

一下视图就是本身是不存数据的，它其实存的就是SQL语句，在你使用视图时，它会执行该视图的SQL来动态的查询获取最新数据，可以向学生表添加一条记录，此时的stus表已经改变，再去查一下视图有没有随之更新，实验证明，

视图就是动态虚拟表

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+5rowsinset(0.00 sec)

mysql>insertinto stus(gradeId,name,age,bir,english,chinese)->values(2,"小爱同学",5,"2012-05-23",100,100);
Query OK,1row affected (0.04 sec)

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 小爱同学     |5|2012-05-23|100|100|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.00 sec)

mysql>select*from stuNameAndAge;+---------------+| name_age      |+---------------+| 小爱同学5|| 小猪佩奇6|| 猪妈妈7|| 猪爸爸8|| 周棋洛18|| 张郁苗18|+---------------+6rowsinset(0.00 sec)

总结

视图是虚拟的表
一般将视图用来检索(select语句)而不用于更新(insert,update,delete)

在这里插入图片描述

16. 存储过程 🍊

思维导图 📌

在这里插入图片描述

存储过程概念

MySQL5添加了对存储过程的支持，所以想要使用存储过程，先要保证版本大于等于5，简单说存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合，可把它看成批处理，但是它的作用不仅仅是批处理

创建存储过程

语法

createprocedure 存储过程名()begin
    语句集合;end;

例如创建一个存储过程 engaverage，用来计算学生表的英语成绩平均分，语句如下:

mysql>delimiter//
mysql>createprocedure engaverage()->begin->selectavg(english)as englishaverage
    ->from stus;->end//
Query OK,0rows affected (0.06 sec)

mysql>delimiter;
mysql>

这里需要注意，如果是在mysql的命令行创建，得使用指令

delimiter 自定义结束符

修改mysql默认的结束符 ; 然后再创建完成之后再次使用命令修改回来，不然执行不成功，注意哈，如果使用可视化工具，就不用了

使用存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的关键字使用了

call

语法

call 存储过程名();

mysql>call engaverage();+----------------+| englishaverage |+----------------+|72.0000|+----------------+1rowinset(0.00 sec)

Query OK,0rows affected (0.01 sec)

执行成功，计算出了学生的英语平均成绩为72分，还不错

使用参数存储过程

上面演示的是一个简单的存储过程，它只简单显示查询语句的结果，一般存储过程不显示结果，而是把结果返回给你指定的变量

变量（variable） 内存中一个特定区域，用于存储临时数据

假如要计算学生英语的最高分，最低分，平均分使用带变量的存储过程完成，如下，这个存储过程接收

三个参数

，enMin表示英语最低分，enMax表示英语最高分，enAvg表示英语平均分，每个参数必须

指定类型

，关键字

OUT

指出相应的参数用来从存储引擎传出一个值，MySQL支持

IN

表示传入，存储过程位于

begin 和 end

语句中，保存到相应的变量通过

into

关键字指定

mysql>delimiter $
mysql>createprocedure englishcomputed(->out engMin decimal(6,3),->out engMax decimal(6,3),->out engAvg decimal(6,3)->)->begin->selectmin(english)into engMin
    ->from stus;->selectmax(english)into engMax
    ->from stus;->selectavg(english)into engAvg
    ->from stus;->end$
Query OK,0rows affected (0.03 sec)

mysql>delimiter;

MySQL变量

MySQL中所有变量都必须以 @ 开始

使用带参存储过程

为调用此存储过程，必须指定3个变量名，因为此存储过程要求3个参数，因此必须正好传递3个参数，不能多也不能少，因此使用3个变量将存储过程计算结果保存下来

mysql>call englishcomputed(@englishMin,@englishMax,@englishAverage);
Query OK,1row affected (0.03 sec)

查询变量值

mysql>select@englishMax;+-------------+|@englishMax|+-------------+|130.000|+-------------+

mysql>select@englishMin;+-------------+|@englishMin|+-------------+|34.000|+-------------+

mysql>select@englishAverage;+-----------------+|@englishAverage|+-----------------+|72.000|+-----------------+

查看存储过程状态

语法

SHOWPROCEDURESTATUSLIKE"存储过程名";

\G格式化输出

mysql>SHOWPROCEDURESTATUSLIKE"englishcomputed";+------+-----------------+-----------+-----------------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| Db   | Name            |Type|Definer| Modified            | Created             | Security_type |Comment| character_set_client | collation_connection |Database Collation |+------+-----------------+-----------+-----------------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| db12 | englishcomputed |PROCEDURE| skip-grants user@skip-grants host |2022-05-2618:26:13|2022-05-2618:26:13|DEFINER|| utf8                 | utf8_general_ci      | utf8_general_ci    |+------+-----------------+-----------+-----------------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+1rowinset(0.00 sec)

mysql>SHOWPROCEDURESTATUSLIKE"englishcomputed"\G;***************************1.row***************************
                  Db: db12
                Name: englishcomputed
                Type: PROCEDUREDefiner: skip-grants user@skip-grants host
            Modified: 2022-05-2618:26:13
             Created: 2022-05-2618:26:13
       Security_type: DEFINERComment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1rowinset(0.00 sec)

查看创建存储过程语句

语法

SHOWCREATEPROCEDURE 存储过程名;

\G格式化输出

mysql>SHOWCREATEPROCEDURE englishcomputed;+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+|Procedure| sql_mode                                                                                  |CreateProcedure| character_set_client | collation_connection |Database Collation |+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| englishcomputed | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |CREATEDEFINER=`skip-grants user`@`skip-grants host`PROCEDURE`englishcomputed`(out engMin decimal(6,3),out engMax decimal(6,3),out engAvg decimal(6,3))beginselectmin(english)into engMin
from stus;selectmax(english)into engMax
from stus;selectavg(english)into engAvg
from stus;end| utf8                 | utf8_general_ci      | utf8_general_ci    |+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+1rowinset(0.00 sec)

mysql>SHOWCREATEPROCEDURE englishcomputed\G;***************************1.row***************************Procedure: englishcomputed
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    CreateProcedure: CREATEDEFINER=`skip-grants user`@`skip-grants host`PROCEDURE`englishcomputed`(out engMin decimal(6,3),out engMax decimal(6,3),out engAvg decimal(6,3))beginselectmin(english)into engMin
from stus;selectmax(english)into engMax
from stus;selectavg(english)into engAvg
from stus;end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1rowinset(0.00 sec)

删除存储过程

直接删除

语法

dropprocedure 存储过程名;

mysql>dropprocedure engaverage;
Query OK,0rows affected (0.00 sec)

判断存在再删除

语法

dropprocedureifexists 存储过程名;

mysql>dropprocedureifexists engaverage;
Query OK,0rows affected,1 warning (0.00 sec)

在这里插入图片描述

17. 索引 🍏

思维导图 📌

在这里插入图片描述

索引概念

其实小伙伴数字数据结构的话，其中有个数据结构叫做数组，它就是通过下标索引来查找元素的，很快，但是添加，删除，修改都成了它的泪，MySQL索引也是一样

顺序访问

顺序访问是全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据，顺序访问实现简单，但是当表中有大量数据的时候，效率非常低下。在几千万条数据中查找少量的数据，使用顺序访问方式将会遍历所有的数据，花费了大量时间，显然会影响数据库的查询性能

索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式，使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快速地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

索引的优缺点

世上没有完美的事物，算法里面。想提高查询速度，要付出代码，可能是占用更多内存记录，索引也是一样

优点

通过创建索引能保证数据库表中每一行数据的唯一性
大大加快数据的查询速度，这是使用索引很重要的点
可以给所有列类型设置索引
……

缺点

创建和维护索引需要耗费时间，并且随着数据量的上升，耗时也会增加
索引需要磁盘空间，使用索引，除了数据表占空间外，每个索引还要占一定的数据空间，如果有大量的索引，文件空间将变得超大
当对表里数据增删改时，索引也要动态维护，这会降低数据得维护速度，数据量大可能很致命
……

MySQL索引分类

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引
按物理存储分类可分为：聚簇索引、二级索引（辅助索引）
按字段特性分类可分为：主键索引、普通索引、前缀索引
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）

创建索引

语法

createindex 索引名 on 表名(字段名称(长度));

mysql>createindex stus_index on stus(name);
Query OK,0rows affected (0.07 sec)

查看索引

showindexfrom 数据表;

删除索引

dropindex 索引名 on 表名;

这里没有详细讲解各种索引的创建，以及数据结构深入的东西，后期会在MySQL专栏慢慢详解，索引有好也有坏，把握好什么时候使用很重要

在这里插入图片描述

18. 触发器 🍒

思维导图 📌

在这里插入图片描述

触发器概念

MySQL对触发器支持的版本为5及以上，触发器是MySQL响应插入，更新，删除语句时自动执行的一条SQL语句

创建触发器

想要创建触发器，则需要给出4条信息，1，唯一的触发器名 2，触发器关联的表 3，触发器对插入，删除，更新那些操作起作用， 4，触发器何时执行（前，后）

注意：在创建触发器时，尽量保证每个数据库的触发器名唯一，最然MySQL要求的是在表及别唯一，但是不能保证以后会有变化，好习惯吧！🆗

单词：trigger vt.触发；引起；发动；开动；起动

例子：

mysql>createtrigger firsttrigger afterinserton stus
    ->for each rowselect'哈哈，我触发器来喽'into@haha;
Query OK,0rows affected,1 warning (0.01 sec)

create trigge

用来创建名为

firsttrigger

的新触发器，这里使用

on stus

表示作用在 stus 这张表，

after insert

表示在插入操作完成之后执行触发器，

for each row

表示代码每插入行执行，查询将字符串存入变量，好像是MySQL新的约定，🆗

mysql>insertinto stus(gradeId,name,age,bir,english,chinese)->values(2,"王子",24,"2000-12-12",150,150);
Query OK,1row affected (0.00 sec)

mysql>select@haha;+-----------------------------+|@haha|+-----------------------------+| 哈哈，我触发器来喽          |+-----------------------------+1rowinset(0.00 sec)

插入一条数据，查看变量，已经执行了，🆗

注意：只有表支持触发器，视图和临时表都不支持触发器，因此，每个表最多有6个触发器，增加之前，增加之后，删除之前，删除之后，修改之前，修改之后，如果触发器失败，则语句也会不执行，如果语句失败，将不执行触发器，稳了

删除触发器

直接删除

mysql>droptrigger firsttrigger;
Query OK,0rows affected (0.00 sec)

判断有再删除

mysql>droptriggerifexists firsttrigger;
Query OK,0rows affected,1 warning (0.00 sec)

注意：触发器不能修改或覆盖，如果想修改它，必须先删除它，然后再重新创建它

在这里插入图片描述

19. 事务 🍓

思维导图 📌

在这里插入图片描述

事务概念

事务可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行

MySQL支持多种引擎，并不是所有的引擎都能支持事务，例如常见的

MyISAM

不支持事务，而

InnoDB

是支持事务的，如果你的业务场景需要事务，那么一定要正确使用引擎

几个术语

事务（transaction）

指一组SQL语句

回退（rollback）

指撤销指定SQL语句的过程

提交（commit）

指将未存储的SQL语句结果写入到数据库表

保留点（savepoint）

指事务处理中设置的临时占位符，你可以对它发布回退，这里与回退整个事务处理不同

开始事务

MySQL中使用下面语句标识事务开始

mysql>starttransaction;
Query OK,0rows affected (0.00 sec)

使用rollback

mysql>delimiter $
mysql>deletefrom stus
    ->where name ="周棋洛";->select*from stus;->rollback;->select*from stus;-> $
Query OK,1row affected (0.03 sec)+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 小爱同学     |5|2012-05-23|100|100|+----+---------+--------------+------+------------+---------+---------+5rowsinset(0.03 sec)

Query OK,0rows affected (0.05 sec)+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 小爱同学     |5|2012-05-23|100|100|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.05 sec)

mysql>delimiter;

看上面的语句，因为我是再命令行执行的，因此我需要改一下

结束

标志，以便于我能写；

不立即执行

，OK，首先我把名字为

周棋洛

的删了，查询一下，看到下面打印确实没了，但是我后面还有一个

rollback

，咦，哦，我

又滚回来

了，再次查询一下，咦，呜，发现删掉的周棋洛又回来了，没了的周棋洛突然攻击我哈哈，这就是

事务里的回滚

使用commit

如果你没开启事务，那么默认就是自动提交的，但是一旦你开启事务，就需要手动提交事务，只有这样对于数据的写操作才能真正的执行，提交使用 commit

当 commit 或 rollback 语句执行后，事务还会自动关闭

mysql>delimiter $
mysql>select*from stus;->deletefrom stus
    ->where name ="小爱同学";->commit;->select*from stus;-> $
+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 小爱同学     |5|2012-05-23|100|100|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.00 sec)

Query OK,1row affected (0.01 sec)

Query OK,0rows affected (0.01 sec)+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+5rowsinset(0.01 sec)

mysql>delimiter;
mysql>

使用保留点

对于简单的事务，提交和回滚是OK的，但是对于复杂的事物操作，可能需要部分提交或回滚

为了能够回退部分事物，需要再合适的位置防止占位符，这占位符就是保留点，创建保留点，使用语句

savepoint delete1;

上面语句，创建了一个保留点，名字叫做 delete1，任何保留点都必须有标识它的唯一名字，这样在回滚时，才能知道自己到底滚哪里，滚多远

rollbackto delete1;

上面语句意思是，回滚到保留点 delete1 的位置，事物完成，保留点也就销毁了，所以我们无需操心

更改默认的提交行为

默认MySQL行为是自动提交的，假如你想手动提交，你可以使用

mysql>set autocommit=0;
Query OK,0rows affected (0.00 sec)

autocommit 标志决定是否自动提交，如果为0，表示为假，MySQL不自动提交，为真则自动提交

这里只是演示，所以，我再改回来

mysql>set autocommit=1;
Query OK,0rows affected (0.00 sec)

在这里插入图片描述

20. 安全管理 🐣

思维导图 📌

在这里插入图片描述

访问控制

概念：你需要给用户提供他们所需要的访问权，且仅提供他们所需的访问权，这就是所谓访问控制

管理用户

MySQL用户账户和信息存储在名为mysql的数据库中，当我们需要获取所有用户账号列表时，需要直接访问它

1.登录MySQL

2.使用mysql数据库

mysql>use mysql;Database changed

3.查看用户

mysql>selectuserfromuser;+---------------+|user|+---------------+| mysql.session|| mysql.sys     || root          |+---------------+3rowsinset(0.04 sec)

创建用户账号

使用create user语句，创建账号

下面语句创建了用户sen密码为123456

createuser sen identified by'123456';

有可能会报错，解决方法是刷新一下权限表

flush privileges;

重新命名一个用户账号，使用rename user语句

mysql>renameuser sen to liusen;
Query OK,0rows affected (0.00 sec)

删除用户账号

mysql>dropuser liu;
Query OK,0rows affected (0.00 sec)

设置访问权限

创建完用户账号之后，接着就要分配访问权限，新创建的用户账号没有访问权限，它们可以登录MySQL，但看不到数据，不能执行任何操作

为了看到用户账号的权限，可以使用

show grants for 用户账号;

mysql>show grants for liusen;+------------------------------------+| Grants for liusen@%|+------------------------------------+|GRANTUSAGEON*.*TO'liusen'@'%'|+------------------------------------+1rowinset(0.03 sec)

此结果表示根据没有任何权限，还挺可爱Q

为了设置权限，使用 grant 语句，使用 grant 语句需要以下信息，要授予的权限，被授予访问权限的数据库或表，用户名

mysql>grantselecton db12.*to liusen;
Query OK,0rows affected (0.03 sec)

mysql>show grants for liusen;+------------------------------------------+| Grants for liusen@%|+------------------------------------------+|GRANTUSAGEON*.*TO'liusen'@'%'||GRANTSELECTON`db12`.*TO'liusen'@'%'|+------------------------------------------+2rowsinset(0.00 sec)

上面语句，表示授予用户liusen权限，权限是允许对db12数据库的所有表进行select（读）操作，即授予只读访问权限，然后再查看权限，就看到已经有了

假如，liusen 用户不老实，一秒都查询上千次，我就要收回它的只读权限，怎么做呢？

mysql>revokeselecton db12.*from liusen;
Query OK,0rows affected (0.00 sec)

mysql>show grants for liusen;+------------------------------------+| Grants for liusen@%|+------------------------------------+|GRANTUSAGEON*.*TO'liusen'@'%'|+------------------------------------+1rowinset(0.00 sec)

revoke 撤销；取消；废除；使无效

使用revoke关键字，阿哦，可怜的小 liusen🥹

还有很多权限，自行百度了

更改密码

如果用户想要更改密码，可以使用

set password

语句

mysql>set password for liusen = password('666666');
Query OK,0rows affected,1 warning (0.00 sec)

注意，新密码必须传入 password() 函数进行加密

在这里插入图片描述

21. 数据库备份与还原 🥔

思维导图 📌

在这里插入图片描述

备份

mysqldump
命令可以将数据库中的数据备份成一个
文本文件
，表的结构和表中的数据将存储在生成的文本文件中。

注意：使用备份命令时应退出MySQL的登录，如果在MySQL的命令行使用会报错，原因很简单，给你看张图你就懂了

在这里插入图片描述

备份一个数据库

语法

mysqldump -u 用户名 -p 数据库名 > 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p db12 > D:/dbdump/db12.sql
Enter password: ****

没报错没提示，打开文件夹low一眼

在这里插入图片描述

很好

备份多个数据库

加上–databases选项，然后在后面同时指定多个数据库
语法

mysqldump -u 用户名 -p --databases 数据库1 数据库2 …… > 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p --databases db12 db10 > D:/dbdump/db10db12.sql
Enter password: ****

在这里插入图片描述

备份所有数据库

语法

mysqldump -u 用户名-p -all-databases> 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p --all-databases > D:/dbdump/all.sql
Enter password: ****

在这里插入图片描述
大家看，我的所有库加起来有接近6mb呢，哈哈

备份一张表

语法

mysqldump -u 用户名 -p 数据库名 表名 > 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p db12 stus > D:/dbdump/stus.sql
Enter password: ****

在这里插入图片描述

备份多张表

语法

注意空格

mysqldump -u 用户名 -p 数据库名 表名 表名 表名…… > 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p db12 stus grade > D:/dbdump/two.sql
Enter password: ****

在这里插入图片描述

恢复

1. 使用mysqldump指令

跟备份相似，把 > 反过来就行了

mysqldump -uroot -p --all-databases < D:/dbdump/all.sql

2. source命令

还可以使用source命令，前提是需要先登录MySQL命令行吗，它是再MySQL命令行才可以使用的，还有注意这个命令不要加 ;

语法

source 待还原数据库的磁盘路径

在这里插入图片描述

22. 性能(了解) 🍍

对于学习MySQL，机器的配置无关紧要，但是作为生产环境的MySQL服务器，应该注意
一般来说，关键的生产DBMS应运行在自己专用服务器上
MySQL默认配置，如果你需要更改缓冲区大小，内存分配等等，可以通过show variables 和 show status 查看
使用合适正确的数据类型
索引能改善数据检索性能，确定使用索引不是一件微不足道的小事，需要分析使用的 select 语句以找出重复的 where 和 order by 子句，如果一个简单的where子句返回结果的时间太长，则可以断定其中使用的一个或多个列是需要索引的对象
索引改善检索性能，但损害数据插入，删除，更新的性能，如果有一些表，收集的数据不常被搜索，则在有必要之前不要索引他们
最重要的规则就是，每条规则在某些条件下会被打破

在这里插入图片描述

24. 尾 🥝

恭喜你！三篇学会了MySQL数据库，完结撒花🌸🌸🌺

我想送你几句话，未来的路还很长，MySQL数据库还有很多细节等着你去深究，加油吧！少年 🛫

标签： mysql 数据库 database

本文转载自: https://blog.csdn.net/m0_53321320/article/details/125018880
版权归原作者 周棋洛ყ ᥱ ᥉ 所有，如有侵权，请联系我们删除。