-
肝了五万字把SQL数据库从基础到高级所有命令写的明明白白，内容实在丰富，MySQL这一篇就够了
MySQL教程，全文累计 50000 字，思维导图 n 张，帮助大家快速学习 ，原创不易，如果觉得不错可以关注博主，一起进步 🦢，如需完整脑图和pdf可以评论区留言，博主看到会第一时间回复，这里是小周，期待你的关注
文章目录
1. 基本操作 😀
对mysql数据库的基本操作，启动，关闭，登录，退出，帮助等等……
脑图
启动mysql服务
net start mysql
C:\Windows\System32>net start mysql MySQL 服务正在启动 . MySQL 服务已经启动成功。- 1
- 2
- 3
关闭mysql服务
net stop mysql
C:\Windows\System32>net stop mysql MySQL 服务正在停止. MySQL 服务已成功停止。- 1
- 2
- 3
登录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 end with ; or \g. Your MySQL connection id is 3 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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
退出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 | +----+------+-----------+------+---------+------+----------+------------------+ 1 row in set (0.03 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看帮助
help 关键字;
2. 数据库操作 💻
脑图
对数据库的操作，包括查看当前使用的哪个数据库，使用数据库，查看所有数据库，查看当前时间，查看当前用户，查看数据库版本，查看创建数据库语句，创建数据库，以及删除数据库等围绕着数据库的相关操作
查看当前使用数据库
select database();
mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.03 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看所有数据库
show databases;
使用数据库
use 数据库;
mysql> use db12; Database changed- 1
- 2
查看当前时间
select now();
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2022-05-20 00:01:35 | +---------------------+ 1 row in set (0.03 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看当前用户
select user();
mysql> select user(); +--------+ | user() | +--------+ | root@ | +--------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看数据库版本
select version();
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.19 | +-----------+ 1 row in set (0.04 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看创建数据库信息
show create database 数据库名;
mysql> show create database db12; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | db12 | CREATE DATABASE `db12` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
创建数据库
基本写法
create database 数据库名;
mysql> create database db14; Query OK, 1 row affected (0.03 sec)- 1
- 2
创建数据库指定utf8编码
create database 数据库名 charset utf8;
mysql> create database db15 charset utf8; Query OK, 1 row affected (0.03 sec)- 1
- 2
不存在再创建数据库
create database if not exists 数据库名;
mysql> create database if not exists db15; Query OK, 1 row affected, 1 warning (0.00 sec)- 1
- 2
删除数据库
直接删除
drop database 数据库名;
mysql> drop database db14; Query OK, 0 rows affected (0.05 sec)- 1
- 2
判断删除
drop database if exists 数据库名;
mysql> drop database if exists db15; Query OK, 0 rows affected (0.00 sec)- 1
- 2
3. 表操作 ❤️
脑图
查看所有表
show tables;
mysql> show tables; Empty set (0.00 sec)- 1
- 2
mysql> show tables from db12; Empty set (0.00 sec)- 1
- 2
创建班级表 grade
id 主键
grade_name 班名 varchar(100)mysql> create table grade( -> id int primary key auto_increment, -> grade_name varchar(100) comment "班级名字" -> ); Query OK, 0 rows affected (0.06 sec)- 1
- 2
- 3
- 4
- 5
创建学生表 stu，添加外键约束
id 主键
name 学生姓名 varchar(100) not null
sex 学生性别创建数据表 stu ，并在表 stu 上创建外键约束，让它的键 gradeId 作为外键关联到表 grade 的主键 id ，SQL这样写
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]- 1
- 2
CREATE TABLE stu ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (100) NOT NULL, age INT (3), bir DATE, english INT (3), chinese INT (3), gradeId INT, CONSTRAINT fk_stu_grade FOREIGN KEY (gradeId) REFERENCES grade (id) ) ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
注意：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。例如，两者都是 INT 类型，或者都是 CHAR 类型。如果不满足这样的要求，在创建从表时，就会出现“ERROR 1005(HY000): Can’t create table”错误
在修改表时添加外键约束
假如在创建表时，没有添加外键约束，那么可以在创建表后通过修改表给表添加外键约束，语法如下
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);- 1
- 2
CREATE TABLE stu ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (100) NOT NULL, age INT (3), bir DATE, english INT (3), chinese INT (3), gradeId INT ) ; ALTER TABLE stu ADD CONSTRAINT fk_stu_grage FOREIGN KEY (gradeId) REFERENCES grade (id) ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
注意：在为已经创建好的数据表添加外键约束时，要确保添加外键约束的列的值全部来源于主键列，并且外键列不能为空。
删除外键约束
通过外键名删除外键，这里演示一下，删除之后会继续添加上的，因为需要这个外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;- 1
ALTER TABLE stu DROP FOREIGN KEY fk_stu_grage;- 1
查看表结构
DESC stu;- 1
显示存储引擎的状态信息
SHOW ENGINES;- 1
修改表
修改表名 rename to
mysql> alter table stu rename to stus; Query OK, 0 rows affected (0.03 sec) mysql> show tables; +----------------+ | Tables_in_db12 | +----------------+ | grade | | stus | +----------------+ 2 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
添加一列 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 | | +---------+--------------+------+-----+---------+----------------+ 7 rows in set (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 | | +------------+--------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
给指定列添加数据
INSERT INTO stus(NAME,age,bir,english,gradeId) VALUES("周棋洛",18,"2002-06-01",78,1);- 1
mysql> select * from stus; +----+---------+-----------+------+------------+---------+---------+ | id | gradeId | NAME | age | bir | english | chinese | +----+---------+-----------+------+------------+---------+---------+ | 1 | 1 | 周棋洛 | 18 | 2002-06-01 | 78 | NULL | +----+---------+-----------+------+------------+---------+---------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
给全部列添加数据
mysql> insert into stus values(2,2,"张郁苗",18,"2002-11-07",130,120); Query OK, 1 row affected (0.03 sec)- 1
- 2
给全部列添加数据时，可以简写，不建议，因为代码可读性变差，推荐写成下面这样，一眼就能知道要插入的字段是什么
mysql> insert into stus(gradeId,name,age,bir,english,chinese) values -> (1,"小猪佩奇",6,"2015-06-10",34,23); Query OK, 1 row 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 | +----+---------+--------------+------+------------+---------+---------+ 3 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
一次添加多条数据
mysql> insert into stus(gradeId,name,age,bir,english,chinese) values -> (3,"猪妈妈",7,"2012-09-11",56,78), -> (2,"谷歌",6,"2000-11-11",100,110); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 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 | +----+---------+--------------+------+------------+---------+---------+ 6 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
查看最后一次添加的主键值
select last_insert_id();
mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 5 | +------------------+ 1 row in set (0.03 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
5. 更新数据 update 📖
脑图
语法
update 表 set 要更新字段 = 要更新成啥 where 更新条件;
注意：如果不使用 where 字句进行限制，就会把表里所有记录都修改了，凉凉了，可别怪本帅哥没提醒你
更新一个字段
修改stus表名字叫周棋洛的生日，改为 2001-06-01
mysql> update stus set bir = "2001-06-01" where name = "周棋洛"; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 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 | +----+---------+--------------+------+------------+---------+---------+ 6 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
更新多个字段
把id为6的名字改为 胡歌，年龄改为24
mysql> update stus set name = "胡歌",age = 24 where id = 6; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 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 | +----+---------+--------------+------+------------+---------+---------+ 6 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
ignore关键字
当使用update语句进行多行更新，如果在更新过程中出错了，则整个update语句都会被取消，恢复到更新之前，如果你想即使发生错误，也继续进行更新，可以使用
ignore关键字语法：
update ignore 表 set 要更新字段 = 要更新成啥 where 更新条件;- 1
设置为null
当我们想要将记录的某个字段或多个字段改为空，就可以将它修改为 null ，而不是空字符串，mysql中的空为
null例如：
修改id为3的中文成绩为null空mysql> update stus set chinese = null where id = 3; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 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 | +----+---------+--------------+------+------------+---------+---------+ 6 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
6. 删除数据 delete 😨
脑图
语法
delete from 表 where 限制条件;
删除一行记录
删除id为6的数据
mysql> delete from stus where id = 6; Query OK, 1 row 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 | +----+---------+--------------+------+------------+---------+---------+ 5 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
删除所有记录
警告：不要省略 where 子句，如果不使用 where 子句进行限制，别怪我没提醒你，你会把表中所有行全部干掉，准备后事吧！兄弟
delete from stus;- 1
这里就不执行了
注意：delete语句从表中删除行，甚至删除表中所有行，但是，delete不删除表本身
删除所有记录（效率高）
如果你想从表中删除所有数据，不要使用delete，可以使用 truncate 表名，它完成相同的工作，但是速度更快，因为它实际上是删除原来的表并重新创建一张表，而不是逐行删除表中的数据
mysql> select * from user; +------+------+ | id | name | +------+------+ | 1 | hah1 | +------+------+ 1 row in set (0.00 sec) mysql> truncate user; Query OK, 0 rows affected (0.06 sec) mysql> select * from user; Empty set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
无水印脑图
表结构
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 | +----+---------+--------------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
7. 简单查询
脑图
单列查询
select 列名 from 表;
从stus表中查询名字的字段
mysql> select name from stus; +--------------+ | name | +--------------+ | 周棋洛 | | 张郁苗 | | 小猪佩奇 | | 猪爸爸 | | 猪妈妈 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
多列查询
select 列名,列名…… from 表;
从stus表中查询名字和年龄这两个字段
mysql> select name,age from stus; +--------------+------+ | name | age | +--------------+------+ | 周棋洛 | 18 | | 张郁苗 | 18 | | 小猪佩奇 | 6 | | 猪爸爸 | 8 | | 猪妈妈 | 7 | +--------------+------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
查询所有列 *
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 | +----+---------+--------------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
distinct去重
distinct关键字可以出去重复出现的内容，注意：不能部分使用distinct
mysql> select distinct age from stus; +------+ | age | +------+ | 18 | | 6 | | 8 | | 7 | +------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
起别名 as
我们在查询时，如果对原有字段名不满意，这时就可以通过as关键字对字段起别名，注意：as是可省略的，如下所示
mysql> select name as "姓名" from stus; +--------------+ | 姓名 | +--------------+ | 周棋洛 | | 张郁苗 | | 小猪佩奇 | | 猪爸爸 | | 猪妈妈 | +--------------+ mysql> select name "姓名" from stus; +--------------+ | 姓名 | +--------------+ | 周棋洛 | | 张郁苗 | | 小猪佩奇 | | 猪爸爸 | | 猪妈妈 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
条件查询
大于判断 >
😀 : 查找stus表中年龄大于18岁的学生姓名
mysql> select name from stus where age > 18; Empty set (0.00 sec)- 1
- 2
小于判断 <
😀 : 查找stus表中年龄小于18岁的学生姓名
mysql> select name from stus where age < 18; +--------------+ | name | +--------------+ | 小猪佩奇 | | 猪爸爸 | | 猪妈妈 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
等于判断 =
😀 : 查找stus表中年龄等于18岁的学生姓名
mysql> select name from stus where age = 18; +-----------+ | name | +-----------+ | 周棋洛 | | 张郁苗 | +-----------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
大于等于判断 >=
😀 : 查找stus表中年龄大于等于18岁的学生姓名
mysql> select name from stus where age >= 18; +-----------+ | name | +-----------+ | 周棋洛 | | 张郁苗 | +-----------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
小于等于判断 <=
😀 : 查找stus表中年龄小于等于18岁的学生姓名
mysql> select name from stus where age <= 18; +--------------+ | name | +--------------+ | 周棋洛 | | 张郁苗 | | 小猪佩奇 | | 猪爸爸 | | 猪妈妈 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
不等于判断 <> 或 !=
😀 : 查找stus表中年龄不等于18岁的学生姓名
mysql> select name from stus where age <> 18; +--------------+ | name | +--------------+ | 小猪佩奇 | | 猪爸爸 | | 猪妈妈 | +--------------+ mysql> select name from stus where age != 18; +--------------+ | name | +--------------+ | 小猪佩奇 | | 猪爸爸 | | 猪妈妈 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
BETWEEN … AND …包含判断
😀 : 查找stus表中年龄在10~18岁的学生姓名
mysql> select name,age from stus where age between 8 and 20; +-----------+------+ | name | age | +-----------+------+ | 周棋洛 | 18 | | 张郁苗 | 18 | | 猪爸爸 | 8 | +-----------+------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
IN(…)
😀 : 查找stus表中年龄为8岁或7岁的学生姓名
mysql> select name,age from stus where age in(7,8); +-----------+------+ | name | age | +-----------+------+ | 猪爸爸 | 8 | | 猪妈妈 | 7 | +-----------+------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
IS NULL
😀 : 查找stus表中名字为空的学生信息
mysql> select * from stus where name is null; Empty set (0.02 sec)- 1
- 2
IS NOT NULL
😀 : 查找stus表中名字不为空的学生信息
mysql> select * from stus where name is not null; +----+---------+--------------+------+------------+---------+---------+ | 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
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 | +----+---------+-----------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
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 | +----+---------+-----------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
% 多个任意字符
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 | +----+---------+-----------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
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 | +----+---------+-----------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
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 | +----+---------+--------------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
_ 单个任意字符
4.查询 stus 表中 name 以猪开头的且名字长度为2的学生信息mysql> select * from stus where name like '猪_'; Empty set (0.00 sec)- 1
- 2
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 | +----+---------+-----------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
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 | +----+---------+-----------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
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 | +----+---------+-----------+------+------------+---------+---------+- 1
- 2
- 3
- 4
- 5
- 6
9. 排序查询
关键字：
order by
关系型数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出来的数据的顺序有意义，可以使用order by 子句对查询结果进行排序，默认是升序，即从小到大ASC，如果想要降序排序，则需要指定DESC脑图
ASC 升序
1. 查询stus表中名字字段，要求按照年龄的升序进行排序mysql> select name from stus order by age; +--------------+ | name | +--------------+ | 小猪佩奇 | | 猪妈妈 | | 猪爸爸 | | 周棋洛 | | 张郁苗 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
2. 查询stus表中名字,年龄字段，要求按照年龄大于7进行筛选之后按照年龄的升序进行排序mysql> select name,age from stus where age > 7 order by age; +-----------+------+ | name | age | +-----------+------+ | 猪爸爸 | 8 | | 周棋洛 | 18 | | 张郁苗 | 18 | +-----------+------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
DESC 降序
3. 查询stus表中名字字段，要求按照年龄的升序进行排序，如果年龄相同再按照英语成绩降序排列mysql> select name from stus order by age,english desc; +--------------+ | name | +--------------+ | 小猪佩奇 | | 猪妈妈 | | 猪爸爸 | | 张郁苗 | | 周棋洛 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
limit 选学
这个还没讲到，如果你不知道，可以跳过，后面会将到哦
4. 查询stus表中名字字段，要求按照年龄的升序进行排序，并通过limit返回一条数据，即年龄最小的mysql> select name from stus order by age asc limit 1; +--------------+ | name | +--------------+ | 小猪佩奇 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
哦，原来小猪佩奇是最小的
位置问题
在使用 order by 子句对检索出的数据进行排序时，应该保证它是位于 from，如果有条件，应位于where之后，如果使用 limit ，它必须位于 order by 之后，使用子句的次序不对将产生错误消息
10. 聚合函数
MySQL 提供了5个聚合函数，聚合函数能够汇总数据，这些函数是高效设计的，它们返回结果一般比你自己在客户机应用程序中计算要快的多
脑图
AVG()
返回某列的平均值
查看stus表学生的平均英语成绩mysql> select avg(english) as avgEnglish from stus; +------------+ | avgEnglish | +------------+ | 66.4000 | +------------+- 1
- 2
- 3
- 4
- 5
- 6
COUNT()
返回某列的行数
查看stus表一共有多少行mysql> select count(*) as num from stus; +-----+ | num | +-----+ | 5 | +-----+- 1
- 2
- 3
- 4
- 5
- 6
MAX()
返回某列的最大值
查看stus表中英语成绩最高分mysql> select max(english) as score from stus; +-------+ | score | +-------+ | 130 | +-------+- 1
- 2
- 3
- 4
- 5
- 6
MIN()
返回某列的最小值
查询stus表英语成绩的最低分mysql> select min(english) as score from stus; +-------+ | score | +-------+ | 34 | +-------+- 1
- 2
- 3
- 4
- 5
- 6
还不错，没有0蛋
SUM()
返回某列之和
查询stus表中学生英语成绩的总分mysql> select sum(english) as score from stus; +-------+ | score | +-------+ | 332 | +-------+- 1
- 2
- 3
- 4
- 5
- 6
11. 分组查询
脑图
分组
对stus表的数据按照班级进行分组，并查看每个班都有多少人mysql> select gradeId,count(*) as num from stus group by gradeId; +---------+-----+ | gradeId | num | +---------+-----+ | 1 | 2 | | 2 | 1 | | 3 | 2 | +---------+-----+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
对stus表的数据按照班级进行分组，查看每个班都有多少人以及班级英语平均分mysql> select gradeId,avg(english)as english from stus group by gradeId; +---------+----------+ | gradeId | english | +---------+----------+ | 1 | 56.0000 | | 2 | 130.0000 | | 3 | 45.0000 | +---------+----------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
过滤
mysql> select gradeId,count(*) as num from stus group by gradeId having count(*) > 1; +---------+-----+ | gradeId | num | +---------+-----+ | 1 | 2 | | 3 | 2 | +---------+-----+- 1
- 2
- 3
- 4
- 5
- 6
- 7
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 limit 3; +----+--------------+------+ | id | name | age | +----+--------------+------+ | 1 | 周棋洛 | 18 | | 2 | 张郁苗 | 18 | | 3 | 小猪佩奇 | 6 | +----+--------------+------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
两个参数，注意检索出来的数据第一行为0而不是1,所以下面语句意思就是，从第一条数据算起，查询3条数据
select * from XXX limit 0,3;mysql> select id,name,age from stus limit 0,2; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | 周棋洛 | 18 | | 2 | 张郁苗 | 18 | +----+-----------+------+ mysql> select id,name,age from stus limit 2,2; +----+--------------+------+ | id | name | age | +----+--------------+------+ | 3 | 小猪佩奇 | 6 | | 4 | 猪爸爸 | 8 | +----+--------------+------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
假如规定一页有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 | 高一二班 | +----+---------+--------------+------+------------+---------+---------+----+--------------+ 15 rows in set (0.04 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
为啥返回给我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 inner join stus on stus.gradeId = grade.id; +--------------+--------------+ | grade_name | name | +--------------+--------------+ | 高三一班 | 周棋洛 | | 高三二班 | 张郁苗 | | 高三一班 | 小猪佩奇 | | 高一二班 | 猪爸爸 | | 高一二班 | 猪妈妈 | +--------------+--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
隐式内连接
内连接还有一种隐式的写法，即不需要显示的指定
INNER JOIN关键字，需要注意，使用隐式内连接条件的关键字要使用where而不再是onmysql> select grade_name, name from grade,stus where stus.gradeId = grade.id; +--------------+--------------+ | grade_name | name | +--------------+--------------+ | 高三一班 | 周棋洛 | | 高三二班 | 张郁苗 | | 高三一班 | 小猪佩奇 | | 高一二班 | 猪爸爸 | | 高一二班 | 猪妈妈 | +--------------+--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
一般我们常用直接使用where关键词查询连接条件这样更方便简单
外连接
关键字：OUTER JOIN
左外连接
LEFT JOIN
左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL
语法：
LEFT JOIN ON LEFT OUTER JOIN ON- 1
- 2
left join是left outer join的简写mysql> select name,grade_name from stus s left outer join grade g on s.id = g.id; +--------------+--------------+ | name | grade_name | +--------------+--------------+ | 周棋洛 | 高三一班 | | 张郁苗 | 高三二班 | | 小猪佩奇 | 高一二班 | | 猪爸爸 | NULL | | 猪妈妈 | NULL | +--------------+--------------+ mysql> select name,grade_name from stus s left join grade g on s.id = g.id; +--------------+--------------+ | name | grade_name | +--------------+--------------+ | 周棋洛 | 高三一班 | | 张郁苗 | 高三二班 | | 小猪佩奇 | 高一二班 | | 猪爸爸 | NULL | | 猪妈妈 | NULL | +--------------+--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
右外连接
RIGHT JOIN
右(外)连接，右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录。左表记录不足的地方均为NULL
语法：
RIGHT JOIN ON RIGHT OUTER JOIN ON- 1
- 2
right join是right outer join的简写mysql> select name,grade_name from stus s right join grade g on s.id = g.id; +--------------+--------------+ | name | grade_name | +--------------+--------------+ | 周棋洛 | 高三一班 | | 张郁苗 | 高三二班 | | 小猪佩奇 | 高一二班 | +--------------+--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
子查询
查询中嵌套查询，称嵌套查询为子查询
分类 (根据查询结果的不同，作用不同)
单行单列
作为条件值，使用 = != < > 等进行条件判断
语法：
select 字段列表 from 表 where 字段名 = (子查询);- 1
例如：查询stus表名字叫周棋洛的在几班
1.首先，要在stus表中查询名字为周棋洛的 gradeId 是多少
2.根据查到的 gradeId 再去 grade 查询 相对应的 grade_namemysql> select gradeId from stus where name = "周棋洛"; +---------+ | gradeId | +---------+ | 1 | +---------+ mysql> select grade_name from grade where id = 1; +--------------+ | grade_name | +--------------+ | 高三一班 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
能不能把这两个sql语句合并为一个❓答案是毋庸置疑的，不可以，开玩笑的，哈哈，当然可以，要不也不说子查询了，开始合并
mysql> select grade_name from grade where id = (select gradeId from stus where name = "周棋洛"); +--------------+ | grade_name | +--------------+ | 高三一班 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
多行单列
作为条件值，用 in 等关键字进行条件判断
语法：
select 字段列表 from 表 where 字段名 in (子查询);- 1
多行多列
作为虚拟表
select 字段列表 from (子查询) where t;- 1
这里是热爱动漫，热爱技术，热爱生活的小周，期待你的关注！
全文思维导图
整理了思维导图，方便小伙伴下载无水印思维导图，方便后期的复习
表介绍 🍉
学习之前，先说一下使用的表结构，以及相关数据
grade年级表
CREATE TABLE `grade` ( `id` int(11) NOT NULL AUTO_INCREMENT, `grade_name` varchar(100) DEFAULT NULL COMMENT '班级名字', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8- 1
- 2
- 3
- 4
- 5
grade表数据
mysql> select * from grade; +----+--------------+ | id | grade_name | +----+--------------+ | 1 | 高三一班 | | 2 | 高三二班 | | 3 | 高一二班 | +----+--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
stus表结构
CREATE TABLE `stus` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gradeId` int(11) DEFAULT NULL, `NAME` varchar(100) NOT NULL, `age` int(3) DEFAULT NULL, `bir` date DEFAULT NULL, `english` int(3) DEFAULT NULL, `chinese` int(3) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_stu_grage` (`gradeId`), CONSTRAINT `fk_stu_grage` FOREIGN KEY (`gradeId`) REFERENCES `grade` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
15. 视图 🍎
思维导图 📌
视图概念
视图是虚拟的表，与包含数据的表不同，视图本身不包含数据，视图只包含使用时动态检索出的数据，如果你创建了复杂的视图或嵌套了视图，可能会发现性能严重下降
为什么使用视图 ？
为了保障数据安全性，提高查询效率
视图的使用规则和限制
- 与表一样，视图必须唯一命名
- 视图可以嵌套，从视图检索数据构造新的视图
- 有足够权限，才可以创建视图
- 对于视图创建的数量没有限制
- 视图不可以索引，触发器和默认值
- 视图可以和表一起使用
视图操作
创建视图
语法
create view 视图名 as 查询语句;- 1
- 2
- 3
mysql> create view zhouql_grade_id as select gradeId from stus where name = "周棋洛"; mysql> select * from zhouql_grade_id; +---------+ | gradeId | +---------+ | 1 | +---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
查看创建视图语句
语法
show create view 视图名;- 1
mysql> show create view zhouql_grade_id; +-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | zhouql_grade_id | CREATE ALGORITHM=UNDEFINED DEFINER=`skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `zhouql_grade_id` AS select `stus`.`gradeId` AS `gradeId` from `stus` where (`stus`.`NAME` = '周棋洛') | utf8 | utf8_general_ci | +-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+- 1
- 2
- 3
- 4
- 5
- 6
更新视图
1. 直接删除DROP再CREATE
直接删除视图，再创建
2. CREATE OR REPLACE VIEW
mysql> CREATE OR REPLACE VIEW zhouql_grade_id -> as -> select gradeId from stus -> where name = "张郁苗"; mysql> select * from zhouql_grade_id; +---------+ | gradeId | +---------+ | 2 | +---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
删除视图
语法
drop view 视图名;- 1
1. 直接删除
mysql> drop view zhouql_grade_id; Query OK, 0 rows affected (0.00 sec)- 1
- 2
2. 判断存在再删除
mysql> drop view if exists zhouql_grade_id; Query OK, 0 rows affected, 1 warning (0.00 sec)- 1
- 2
利用视图简化复杂的联结
如果要查询学生周棋洛在几班，首先要查询学生表 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 | +--------------+ | 高三一班 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
经过两条SQL语句，最终得出周棋洛同学是高三一班的
为了方便点，人们想把两条SQL语句合并为一条SQL，于是子查询来了，万物皆可嵌套，语句如下
mysql> select grade_name from grade where id = (select gradeId from stus where name = "周棋洛"); +--------------+ | grade_name | +--------------+ | 高三一班 | +--------------+- 1
- 2
- 3
- 4
- 5
- 6
视图最常见的应用之一就是隐藏复杂的SQL，比如复杂的联结查询
1.创建视图 gradeName，该视图使用隐式内连接查询学生表的班级id与班级表的id相同的数据
mysql> create view gradeName -> as -> select stus.name,stus.gradeId,grade.grade_name from stus,grade -> where stus.gradeId = grade.id; Query OK, 0 rows affected (0.03 sec) mysql> select * from gradeName; +--------------+---------+--------------+ | name | gradeId | grade_name | +--------------+---------+--------------+ | 周棋洛 | 1 | 高三一班 | | 张郁苗 | 2 | 高三二班 | | 小猪佩奇 | 1 | 高三一班 | | 猪爸爸 | 3 | 高一二班 | | 猪妈妈 | 3 | 高一二班 | +--------------+---------+--------------+ 5 rows in set (0.03 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
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 | +-----------+--------------+ | 张郁苗 | 高三二班 | +-----------+--------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
利用视图格式化检索出的数据
创建一个视图 stuNameAndAge，要求有一个字段 name_age，值是 stus 表中查询出的学生姓名加年龄，并按照年龄的降序排列，SQL如下
mysql> create view stuNameAndAge -> as -> select concat(rtrim(name),rtrim(age)) as name_age -> from stus -> order by age; Query OK, 0 rows affected (0.03 sec) mysql> select * from stuNameAndAge; +---------------+ | name_age | +---------------+ | 小猪佩奇6 | | 猪妈妈7 | | 猪爸爸8 | | 周棋洛18 | | 张郁苗18 | +---------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
验证视图是动态的虚拟表
可以
简单验证一下视图就是本身是不存数据的，它其实存的就是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 | +----+---------+--------------+------+------------+---------+---------+ 5 rows in set (0.00 sec) mysql> insert into stus(gradeId,name,age,bir,english,chinese) -> values(2,"小爱同学",5,"2012-05-23",100,100); Query OK, 1 row 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 | +----+---------+--------------+------+------------+---------+---------+ 6 rows in set (0.00 sec) mysql> select * from stuNameAndAge; +---------------+ | name_age | +---------------+ | 小爱同学5 | | 小猪佩奇6 | | 猪妈妈7 | | 猪爸爸8 | | 周棋洛18 | | 张郁苗18 | +---------------+ 6 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
总结
视图是虚拟的表
一般将视图用来检索(select语句)而不用于更新(insert,update,delete)
16. 存储过程 🍊
思维导图 📌
存储过程概念
MySQL5添加了对存储过程的支持，所以想要使用存储过程，先要保证版本大于等于5，简单说存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合，可把它看成批处理，但是它的作用不仅仅是批处理
创建存储过程
语法
create procedure 存储过程名() begin 语句集合; end;- 1
- 2
- 3
- 4
例如创建一个存储过程 engaverage，用来计算学生表的英语成绩平均分，语句如下:
mysql> delimiter // mysql> create procedure engaverage() -> begin -> select avg(english) as englishaverage -> from stus; -> end // Query OK, 0 rows affected (0.06 sec) mysql> delimiter ; mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
这里需要注意，如果是在mysql的命令行创建，得使用指令
delimiter 自定义结束符修改mysql默认的结束符 ; 然后再创建完成之后再次使用命令修改回来，不然执行不成功，注意哈，如果使用可视化工具，就不用了使用存储过程
MySQL称存储过程的执行为 调用，因此MySQL执行存储过程的关键字使用了
call语法
call 存储过程名();- 1
mysql> call engaverage(); +----------------+ | englishaverage | +----------------+ | 72.0000 | +----------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
执行成功，计算出了学生的英语平均成绩为72分，还不错
使用参数存储过程
上面演示的是一个简单的存储过程，它只简单显示查询语句的结果，一般存储过程不显示结果，而是把结果返回给你指定的变量
变量（variable） 内存中一个特定区域，用于存储临时数据
假如要计算学生英语的最高分，最低分，平均分使用带变量的存储过程完成，如下，这个存储过程接收
三个参数，enMin表示英语最低分，enMax表示英语最高分，enAvg表示英语平均分，每个参数必须指定类型，关键字OUT指出相应的参数用来从存储引擎传出一个值，MySQL支持IN表示传入，存储过程位于begin 和 end语句中，保存到相应的变量通过into关键字指定mysql> delimiter $ mysql> create procedure englishcomputed( -> out engMin decimal(6,3), -> out engMax decimal(6,3), -> out engAvg decimal(6,3) -> ) -> begin -> select min(english) into engMin -> from stus; -> select max(english) into engMax -> from stus; -> select avg(english) into engAvg -> from stus; -> end$ Query OK, 0 rows affected (0.03 sec) mysql> delimiter ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
MySQL变量
MySQL中所有变量都必须以 @ 开始
使用带参存储过程
为调用此存储过程，必须指定3个变量名，因为此存储过程要求3个参数，因此必须正好传递3个参数，不能多也不能少，因此使用3个变量将存储过程计算结果保存下来
mysql> call englishcomputed(@englishMin,@englishMax,@englishAverage); Query OK, 1 row affected (0.03 sec)- 1
- 2
查询变量值
mysql> select @englishMax; +-------------+ | @englishMax | +-------------+ | 130.000 | +-------------+ mysql> select @englishMin; +-------------+ | @englishMin | +-------------+ | 34.000 | +-------------+ mysql> select @englishAverage; +-----------------+ | @englishAverage | +-----------------+ | 72.000 | +-----------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
查看存储过程状态
语法
SHOW PROCEDURE STATUS LIKE "存储过程名";- 1
\G格式化输出
mysql> SHOW PROCEDURE STATUS LIKE "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-26 18:26:13 | 2022-05-26 18:26:13 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci | +------+-----------------+-----------+-----------------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql> SHOW PROCEDURE STATUS LIKE "englishcomputed"\G; *************************** 1. row *************************** Db: db12 Name: englishcomputed Type: PROCEDURE Definer: skip-grants user@skip-grants host Modified: 2022-05-26 18:26:13 Created: 2022-05-26 18:26:13 Security_type: DEFINER Comment: character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
查看创建存储过程语句
语法
SHOW CREATE PROCEDURE 存储过程名;- 1
\G格式化输出
mysql> SHOW CREATE PROCEDURE englishcomputed; +-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | englishcomputed | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `englishcomputed`( out engMin decimal(6,3), out engMax decimal(6,3), out engAvg decimal(6,3) ) begin select min(english) into engMin from stus; select max(english) into engMax from stus; select avg(english) into engAvg from stus; end | utf8 | utf8_general_ci | utf8_general_ci | +-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE PROCEDURE englishcomputed\G; *************************** 1. row *************************** Procedure: englishcomputed sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `englishcomputed`( out engMin decimal(6,3), out engMax decimal(6,3), out engAvg decimal(6,3) ) begin select min(english) into engMin from stus; select max(english) into engMax from stus; select avg(english) into engAvg from stus; end character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
删除存储过程
直接删除
语法
drop procedure 存储过程名;- 1
mysql> drop procedure engaverage; Query OK, 0 rows affected (0.00 sec)- 1
- 2
判断存在再删除
语法
drop procedure if exists 存储过程名;- 1
mysql> drop procedure if exists engaverage; Query OK, 0 rows affected, 1 warning (0.00 sec)- 1
- 2
17. 索引 🍏
思维导图 📌
索引概念
其实小伙伴数字数据结构的话，其中有个数据结构叫做数组，它就是通过下标索引来查找元素的，很快，但是添加，删除，修改都成了它的泪，MySQL索引也是一样
顺序访问
顺序访问是全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据，顺序访问实现简单，但是当表中有大量数据的时候，效率非常低下。在几千万条数据中查找少量的数据，使用顺序访问方式将会遍历所有的数据，花费了大量时间，显然会影响数据库的查询性能
索引访问
索引访问是通过遍历索引来直接访问表中记录行的方式，使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快速地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。
索引的优缺点
世上没有完美的事物，算法里面。想提高查询速度，要付出代码，可能是占用更多内存记录，索引也是一样
优点
- 通过创建索引能保证数据库表中每一行数据的唯一性
- 大大加快数据的查询速度，这是使用索引很重要的点
- 可以给所有列类型设置索引
- ……
缺点
- 创建和维护索引需要耗费时间，并且随着数据量的上升，耗时也会增加
- 索引需要磁盘空间，使用索引，除了数据表占空间外，每个索引还要占一定的数据空间，如果有大量的索引，文件空间将变得超大
- 当对表里数据增删改时，索引也要动态维护，这会降低数据得维护速度，数据量大可能很致命
- ……
MySQL索引分类
按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引
按物理存储分类可分为：聚簇索引、二级索引（辅助索引）
按字段特性分类可分为：主键索引、普通索引、前缀索引
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）创建索引
语法
create index 索引名 on 表名(字段名称(长度));- 1
mysql> create index stus_index on stus(name); Query OK, 0 rows affected (0.07 sec)- 1
- 2
查看索引
show index from 数据表;- 1
删除索引
drop index 索引名 on 表名;- 1
这里没有详细讲解各种索引的创建，以及数据结构深入的东西，后期会在MySQL专栏慢慢详解，索引有好也有坏，把握好什么时候使用很重要
18. 触发器 🍒
思维导图 📌
触发器概念
MySQL对触发器支持的版本为5及以上，触发器是MySQL响应插入，更新，删除语句时自动执行的一条SQL语句
创建触发器
想要创建触发器，则需要给出4条信息，1，唯一的触发器名 2，触发器关联的表 3，触发器对插入，删除，更新那些操作起作用， 4，触发器何时执行（前，后）
注意：在创建触发器时，尽量保证每个数据库的触发器名唯一，最然MySQL要求的是在表及别唯一，但是不能保证以后会有变化，好习惯吧！🆗
单词：trigger vt.触发；引起；发动；开动；起动
例子：
mysql> create trigger firsttrigger after insert on stus -> for each row select '哈哈，我触发器来喽' into @haha; Query OK, 0 rows affected, 1 warning (0.01 sec)- 1
- 2
- 3
create trigge用来创建名为firsttrigger的新触发器，这里使用on stus表示作用在 stus 这张表，after insert表示在插入操作完成之后执行触发器，for each row表示代码每插入行执行，查询将字符串存入变量，好像是MySQL新的约定，🆗mysql> insert into stus(gradeId,name,age,bir,english,chinese) -> values(2,"王子",24,"2000-12-12",150,150); Query OK, 1 row affected (0.00 sec) mysql> select @haha; +-----------------------------+ | @haha | +-----------------------------+ | 哈哈，我触发器来喽 | +-----------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
插入一条数据，查看变量，已经执行了，🆗
注意：只有表支持触发器，视图和临时表都不支持触发器，因此，每个表最多有6个触发器，增加之前，增加之后，删除之前，删除之后，修改之前，修改之后，如果触发器失败，则语句也会不执行，如果语句失败，将不执行触发器，稳了
删除触发器
直接删除
mysql> drop trigger firsttrigger; Query OK, 0 rows affected (0.00 sec)- 1
- 2
判断有再删除
mysql> drop trigger if exists firsttrigger; Query OK, 0 rows affected, 1 warning (0.00 sec)- 1
- 2
注意：触发器不能修改或覆盖，如果想修改它，必须先删除它，然后再重新创建它
19. 事务 🍓
思维导图 📌
事务概念
事务可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行
MySQL支持多种引擎，并不是所有的引擎都能支持事务，例如常见的
MyISAM不支持事务，而InnoDB是支持事务的，如果你的业务场景需要事务，那么一定要正确使用引擎几个术语
事务（transaction）
指一组SQL语句
回退（rollback）
指撤销指定SQL语句的过程
提交（commit）
指将未存储的SQL语句结果写入到数据库表
保留点（savepoint）
指事务处理中设置的临时占位符，你可以对它发布回退，这里与回退整个事务处理不同
开始事务
MySQL中使用下面语句标识事务开始
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)- 1
- 2
使用rollback
mysql> delimiter $ mysql> delete from stus -> where name = "周棋洛"; -> select * from stus; -> rollback; -> select * from stus; -> $ Query OK, 1 row 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 | +----+---------+--------------+------+------------+---------+---------+ 5 rows in set (0.03 sec) Query OK, 0 rows 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 | +----+---------+--------------+------+------------+---------+---------+ 6 rows in set (0.05 sec) mysql> delimiter ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
看上面的语句，因为我是再命令行执行的，因此我需要改一下
结束标志，以便于我能写 ；不立即执行，OK，首先我把名字为周棋洛的删了，查询一下，看到下面打印确实没了，但是我后面还有一个rollback，咦，哦，我又滚回来了，再次查询一下，咦，呜，发现删掉的周棋洛又回来了，没了的周棋洛突然攻击我 哈哈，这就是事务里的回滚使用commit
如果你没开启事务，那么默认就是自动提交的，但是一旦你开启事务，就需要手动提交事务，只有这样对于数据的写操作才能真正的执行，提交使用 commit
当 commit 或 rollback 语句执行后，事务还会自动关闭
mysql> delimiter $ mysql> select * from stus; -> delete from 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 | +----+---------+--------------+------+------------+---------+---------+ 6 rows in set (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 0 rows 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 | +----+---------+--------------+------+------------+---------+---------+ 5 rows in set (0.01 sec) mysql> delimiter ; mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
使用保留点
对于简单的事务，提交和回滚是OK的，但是对于复杂的事物操作，可能需要部分提交或回滚
为了能够回退部分事物，需要再合适的位置防止占位符，这占位符就是保留点，创建保留点，使用语句
savepoint delete1;- 1
上面语句，创建了一个保留点，名字叫做 delete1，任何保留点都必须有标识它的唯一名字，这样在回滚时，才能知道自己到底滚哪里，滚多远
rollback to delete1;- 1
上面语句意思是，回滚到保留点 delete1 的位置，事物完成，保留点也就销毁了，所以我们无需操心
更改默认的提交行为
默认MySQL行为是自动提交的，假如你想手动提交，你可以使用
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)- 1
- 2
autocommit 标志决定是否自动提交，如果为0，表示为假，MySQL不自动提交，为真则自动提交
这里只是演示，所以，我再改回来
mysql> set autocommit=1; Query OK, 0 rows affected (0.00 sec)- 1
- 2
20. 安全管理 🐣
思维导图 📌
访问控制
概念：你需要给用户提供他们所需要的访问权，且仅提供他们所需的访问权，这就是所谓 访问控制
管理用户
MySQL用户账户和信息存储在名为mysql的数据库中，当我们需要获取所有用户账号列表时，需要直接访问它
1.登录MySQL
2.使用mysql数据库
mysql> use mysql; Database changed- 1
- 2
3.查看用户
mysql> select user from user; +---------------+ | user | +---------------+ | mysql.session | | mysql.sys | | root | +---------------+ 3 rows in set (0.04 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
创建用户账号
使用create user语句，创建账号
下面语句创建了用户sen密码为123456
create user sen identified by '123456';- 1
有可能会报错，解决方法是刷新一下权限表
flush privileges;- 1
重新命名一个用户账号，使用rename user语句
mysql> rename user sen to liusen; Query OK, 0 rows affected (0.00 sec)- 1
- 2
删除用户账号
mysql> drop user liu; Query OK, 0 rows affected (0.00 sec)- 1
- 2
设置访问权限
创建完用户账号之后，接着就要分配访问权限，新创建的用户账号没有访问权限，它们可以登录MySQL，但看不到数据，不能执行任何操作
为了看到用户账号的权限，可以使用
show grants for 用户账号;mysql> show grants for liusen; +------------------------------------+ | Grants for liusen@% | +------------------------------------+ | GRANT USAGE ON *.* TO 'liusen'@'%' | +------------------------------------+ 1 row in set (0.03 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
此结果表示根据没有任何权限，还挺可爱Q
为了设置权限，使用 grant 语句，使用 grant 语句需要以下信息，要授予的权限，被授予访问权限的数据库或表，用户名
mysql> grant select on db12.* to liusen; Query OK, 0 rows affected (0.03 sec) mysql> show grants for liusen; +------------------------------------------+ | Grants for liusen@% | +------------------------------------------+ | GRANT USAGE ON *.* TO 'liusen'@'%' | | GRANT SELECT ON `db12`.* TO 'liusen'@'%' | +------------------------------------------+ 2 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
上面语句，表示授予用户liusen权限，权限是允许对db12数据库的所有表进行select（读）操作，即授予只读访问权限，然后再查看权限，就看到已经有了
假如，liusen 用户不老实，一秒都查询上千次，我就要收回它的只读权限，怎么做呢？
mysql> revoke select on db12.* from liusen; Query OK, 0 rows affected (0.00 sec) mysql> show grants for liusen; +------------------------------------+ | Grants for liusen@% | +------------------------------------+ | GRANT USAGE ON *.* TO 'liusen'@'%' | +------------------------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
revoke 撤销；取消；废除；使无效
使用revoke关键字，阿哦，可怜的小 liusen🥹
还有很多权限，自行百度了
更改密码
如果用户想要更改密码，可以使用
set password语句mysql> set password for liusen = password('666666'); Query OK, 0 rows affected, 1 warning (0.00 sec)- 1
- 2
注意，新密码必须传入 password() 函数进行加密
21. 数据库备份与还原 🥔
思维导图 📌
备份
mysqldump命令可以将数据库中的数据备份成一个文本文件，表的结构和表中的数据将存储在生成的文本文件中。注意： 使用备份命令时应退出MySQL的登录，如果在MySQL的命令行使用会报错，原因很简单，给你看张图你就懂了
备份一个数据库
语法
mysqldump -u 用户名 -p 数据库名 > 备份到哪- 1
示例
C:\Windows\System32>mysqldump -uroot -p db12 > D:/dbdump/db12.sql Enter password: ****- 1
- 2
没报错没提示，打开文件夹low一眼
很好
备份多个数据库
加上–databases选项，然后在后面同时指定多个数据库
语法mysqldump -u 用户名 -p --databases 数据库1 数据库2 …… > 备份到哪- 1
示例
C:\Windows\System32>mysqldump -uroot -p --databases db12 db10 > D:/dbdump/db10db12.sql Enter password: ****- 1
- 2
备份所有数据库
语法
mysqldump -u 用户名-p -all-databases > 备份到哪- 1
示例
C:\Windows\System32>mysqldump -uroot -p --all-databases > D:/dbdump/all.sql Enter password: ****- 1
- 2
大家看，我的所有库加起来有接近6mb呢，哈哈备份一张表
语法
mysqldump -u 用户名 -p 数据库名 表名 > 备份到哪- 1
示例
C:\Windows\System32>mysqldump -uroot -p db12 stus > D:/dbdump/stus.sql Enter password: ****- 1
- 2
备份多张表
语法
注意空格
mysqldump -u 用户名 -p 数据库名 表名 表名 表名…… > 备份到哪- 1
示例
C:\Windows\System32>mysqldump -uroot -p db12 stus grade > D:/dbdump/two.sql Enter password: ****- 1
- 2
恢复
1. 使用mysqldump指令
跟备份相似，把 > 反过来就行了
mysqldump -uroot -p --all-databases < D:/dbdump/all.sql- 1
2. source命令
还可以使用source命令，前提是需要先登录MySQL命令行吗，它是再MySQL命令行才可以使用的，还有注意这个命令不要加 ;
语法
source 待还原数据库的磁盘路径- 1
22. 性能(了解) 🍍
- 对于学习MySQL，机器的配置无关紧要，但是作为生产环境的MySQL服务器，应该注意
- 一般来说，关键的生产DBMS应运行在自己专用服务器上
- MySQL默认配置，如果你需要更改缓冲区大小，内存分配等等，可以通过
show variables和show status查看 - 使用合适正确的数据类型
- 索引能改善数据检索性能，确定使用索引不是一件微不足道的小事，需要分析使用的 select 语句以找出重复的 where 和 order by 子句，如果一个简单的where子句返回结果的时间太长，则可以断定其中使用的一个或多个列是需要索引的对象
- 索引改善检索性能，但损害数据插入，删除，更新的性能，如果有一些表，收集的数据不常被搜索，则在有必要之前不要索引他们
- 最重要的规则就是，每条规则在某些条件下会被打破
24. 尾 🥝
恭喜你！三篇学会了MySQL数据库，完结撒花🌸🌸🌺
我想送你几句话，未来的路还很长，MySQL数据库还有很多细节等着你去深究，加油吧！少年 🛫
-
相关阅读:
[Servlet 1] JSP基础知识
Thread同步问题，小案例
spring框架（SSM）
【ARM】关于指令集架构？
Python基础语法(2)
In voluptate magnam blanditiis beatae.Parfaitement simple davantage frais.
java项目中@Data注解详细解析
Proxmox VE 修改集群名称
L1-002 打印沙漏
华为认证HCIA H12-811 Datacom数通考试真题题库【带答案刷题必过】【第四部分】
- 原文地址：https://blog.csdn.net/m0_53321320/article/details/125018880