mysql
1. 简介
数据库就是一个可以操作文件并且基于网络通信的应用程序,
任何基于网络通信的底层都是socket
SQL语句:MySql不单单支持MySQL自己的客户端还支持其他编程语言来充当客户端,统一采用SQL语句进行通信
2. 数据库的分类
2.1 关系型数据库
特点:
- 数据之间彼此有关系或者约束
- 存储数据的表现形式通常是以表格形式呈现
- 例如:MySQL、Oracle、db2、access、sql server
2.2 非关系型数据库
特点:
- 存储数据通常以k、v键值对的形式
- 例如:redis、MongoDB、memcache
3. SQL语句的使用
3.1 SQL语句以;
分号结尾
3.2 基本命令
show databases
:查看所有库名\c
: 取消quit
:退出exit
:退出use db1
: 切换数据库db1select database()
: 查看当前所在库的名字set global xxx
: 设置全局属性,如set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH'
3.3 游客模式
只输入mysql
3.4 库操作
-
增
create database db1; create database db1 charset='utf-8';
-
删
drop database db1;
-
改
alter database db1 charset='gbk';
-
查
show database; show create database db1;
3.5 表操作
在进行表(文件)操作时,需要指定所在的库(文件夹)
-
增
# 用绝对路径的形式操作不同的库 create table db1.t1(id int,name char(4)) # 完整写法 create table db1.t1(字段名1 类型(宽度),字段名2 类型(宽度), 约束条件1, 约束条件2)
- 在同一张表中字段名不能重复
- 宽度和约束条件是可选的
- 最后一行不能有逗号
-
删
# 删除表 drop table db1.t1 # 删除字段 alter table 表名 drop 字段名;
-
改
# 修改字段属性 alter table db1.t1 modify name char(16) comment 注释 # 修改字段名 alter table db1.t1 change 旧字段 新字段 char(8) comment 注释 # 重命名表名 alter table 旧表名 rename to 新表名 # 新注释 alter table comment 注释 # 默认在最后增加添加新的字段 alter table 表名 add 新增字段名 字段类型[约束条件] # 在最前增加添加新的字段 alter table 表名 add 新增字段名 字段类型[约束条件] first # 在其他字段后增加添加新的字段 alter table 表名 add 新增字段名 字段类型[约束条件] after 其他字段名
关键字:
- add
- modify
- change
- first
- after
- comment
- rename to
-
查
# 显示所有表 show tables; # 显示指定表 show create table db1.t1; # 格式化显示 desc db1.t1
3.6 数据操作
-
增
# 不指定字段,按字段顺序填充, insert into db1.t1 values(1,'aaa'),(2,'bbb'),(3,'ccc') # 指定字段(name,id) insert into db1.t1(name,id), values('aaa',1)
关键词:
insert to
-
删
# 删除表中数据,删除以后主键的自增不会停止 delete from db1.t1; # 限定条件 delete from db1.t1 where id=1; # 清空表数据并且重置主键 truncate db1.t1
关键词:
delete from
truncate
-
改
# 修改name值为hhh的所有项,新值设置为dba update db1.t1 set name='dba' where name='hhh'
-
查
# 查询所有字段 select * from db1.t1; # 查询name字段 select name from db1.t1; # 查询id>1的 id和name 字段 select id,name from db1.t1 where id>1
关键词:
select xxx from
4. 存储引擎
存储引擎就是不同的处理机制,针对不同的数据(txt,pdf,word....)应该有对应的不同的处理机制来存储
innodb
:5.7以后的默认引擎,存储数据更加安全,有事务,外键等myisam
:5.7以前的默认引擎,速度比innodb更快memory
: 内存引擎,数据存储在内存中,断电数据丢失blackhole
: 无论存什么都立即消失
# 查看引擎
show engines;
# 创建表时设置引擎
create table t1(id int) engine=innodb # xx.frm xx.ibd
create table t2(id int) engine=myisam # xx.frm xx.MYD xx.MYI
create table t3(id int) engine=memory # xx.frm
create table t4(id int) engine=blackhole # xx.frm
5. 数据的类型
-
整型
tinyint(m) 1个字节 范围(-128~127) smallint(m) 2个字节 范围 (-32768~32767) mediumint(m) 3个字节 范围(-8388608~8388607) int(m) 4个字节 范围(-2147483648~2147483647) bigint(m) 8个字节 范围(+-9.22*10的18次方) 默认情况下整型是带符号的,超出限制只存最大可接收的值(非严格模式,严格模式下报错)
取值范围如果加unsigned,则为无符号,最大值翻倍,如
tinyint unsigned
取值范围为(0~256)特例:只有整型括号里面的数字不是表示限制位数如:
- id int(8) 如果数字没有超出8位 那么默认用空格填充至8位
- 如果数字超出了8位 那么有几位就存几位(但是还是要遵守最大范围)
针对整型字段 括号内无需指定宽度 因为它默认的宽度int(11)足够显示所有的数据了
-
浮点型
float(m,d) 单精度 8位有效位(4字节) m表示总长度,d表示小数部分长度 double(m,d) 双精度 16位有效位(8字节)m表示总长度,d表示小数部分长度 decimal(m,d) 定点类型 m表示总长度,d表示小数部分长度 精度比较:float<double<decimal
decimal适用于科学计算,金融方面,它是最精确的值
-
日期类型
date 日期格式 2000-12-1 time 时间格式 12:24:23 datetime 2008-12-2 22:06:44 timestamp 自动存储记录修改时间 Year 年2008 -
字符串
char(m) 固定长度,最多255个字符,位数不够是空格(默认)补位 varchar(m) 固定长度,最多65535个字符,位数不够有几个存几个 char与varchar对比:
- 存储空间:char浪费空间(固定的字符存数据),varchar节省空间(存的时候需要制作报头,1bytes),
- 存取速度:char存取简单,varchar存取复杂(取的时候先读取报头,),
-
枚举
enum(选择1,选择2,...) 多选一 -
集合
set(选择1,选择2,...) 多选多
6. 约束条件
宽度是用来限制数据的存储,约束条件是在宽度的基础上额外的约束
- not null: 不能为空
- zerofill: 位数不够补0
- unsigned: 无符号操作
- default: 默认值
- unique: 唯一
- 联合唯一: 在最后写unique(id,port)
- 单列唯一: 在字段类型后写unique
- primary key: 主键(非空且唯一)
- 单一主键: 如 id int primary key
- 联合主键: primary key(id,port)
- auto_increment: 自增 通常加在主键上,不能给普通字段加
7. 表与表之间
7.1 外键:
外键是建立表与表之间关系的纽带,写法:foreignkey(当前表中的键名) references 主表表名(主表表中的键名)
如:foreign key(t2_id) reference t2(id)
表示将t2_id字段设置为外键,关联t2表中的id字段
外键创建的时机:
-
创建表时创建
create table t2(id int primery key,addr varchar(50),t1_id int,foreign key(t1_id) references t2(id))
-
修改表时添加
# 外键忘记关联:先删除后添加 show create table 表名 # 查找外键名称 # CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`) alter table t2 drop foreign key 外键名 # 添加外键 alter table t2 add foreign key(t1_id) references t1(id)
级联操作:
alter table t2 add foreign key(t1_id) references t1(id)
on update cascade # 如果主键表中被参考字段更新,外键表中也更新
on delete cascade # 主键表中的记录被删除,外键表中改行也相应删除
表与表之间两种建立关系的方式:
-
通过外键强制建立关联
-
通过sql语句逻辑上建立联系
delete from t1 where id=1; delete from t2 where id=1;
创建外键会消耗一定的资源,并且增加表与表之间的耦合度在实际项目中,如果表很多,可以不建立外键而是采用逻辑上建立联系的方式去建立联系
7.2 表关系
一对多
举例:员工表和部门表,
- 先站在员工表思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据) ,答案是不能!! (不能直接得出结论 一定要两张表都考虑完全)
- 再站在部门表思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据),答案是能!!! 得出结论
- 员工表与部门表示单向的一对多所以表关系就是一对多
# 部门表
create table dep(
id int premary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
# 员工表
create table dep(
id int primery key auto_increment,
name char(16),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id),
# 真正做到数据之间有关系,级联更新与删除
on update cascade # 同步更新
on delete cascade # 同步删除
)
总结:
- 一对多关系,外键字段建在多的一方,如上:一个部门(部门表)对应多个员工(员工表),外键建在员工表中
- 在创建表的时候,一定要先建立被关联的表,如上:先建立部门表,部门表是被员工表关联的关联表
- 在录入数据的时候,也必须先录入别关联表, 如上:先录入部门数据
- 真正做到数据之间有关系,级联更新与删除:
on update cascade
,on delete cascade
多对多
举例:书籍和作者
一本书籍可以有多个作者,一个作者可以有多本书籍,这就是多对多的关系
针对多对多:需要建立第三张表进行关联
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
# 第三张表
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade # 同步更新
on delete cascade, # 同步删除
foreign key(book_id) references book(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
一对一
如果一个表的字段特别多,每次查询又不是所有字段都用到,可以分表
举例:用户表和用户详情表
用户表中的字段: id,name,age
用户详情表的字段:id, addr ,phone ,hobby, email........
一个用户只能对应一个用户详情,这就是一对一关系
# 详情表
create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);
# 用户表
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
authordetail_id int unique,
foreign key(authordetail_id) references authordetail(id)
on update cascade # 同步更新
on delete cascade # 同步删除
)
外键的位置:建立在任何一方都可以,但是推荐建立在查询频率较高的表中
8. 表的查询
关键字:
-
select: 如 :
select * from 表名
查询所有字段 -
where:条件
select * from 表名 where id>1
id大于的查询 -
group by:用于结合聚合函数,根据一个或多个列对结果集进行分组。
# 查询author表中不同年龄的的个数 select age,count(author.id) as nums from author group by age; +------+------+ | age | nums | +------+------+ | 13 | 2 | | 15 | 1 | | 16 | 2 | +------+------+
重要:键字where和group by同时出现的时候group by必须在where的后面,数据分组后,如果直接查询某个列时:只会返回该分组内第一个值group_concat() 返回该分组内所有的值
select age,group_concat(author.name) as names from author group by age; +------+-------+ | age | names | +------+-------+ | 13 | aa,bb | | 15 | cc | | 16 | dd,ee | +------+-------+
-
having: 针对group by 分组的筛选条件,后面可以直接跟聚合函数,
# 用法 select age,count(id) as nums from author group by age having age>=14; +------+------+ | age | nums | +------+------+ | 15 | 1 | | 16 | 2 | +------+------+
-
distinct:去重,必须是完全一样的数据才可以去重,distinct必须位于所有字段的前面,如果去重的字段大于一个,则会进行组合去重,只有多个字段组合起来相同时才会被去重
# 用法 select distinct age from author; +------+ | age | +------+ | 13 | | 15 | | 16 | +------+
-
order by: 顺序
- order by asc: 升序,默认值
- order by desc: 降序
# 升序 select * from author order by name; +----+------+------+ | id | name | age | +----+------+------+ | 1 | aa | 13 | | 2 | bb | 13 | | 3 | cc | 15 | | 4 | dd | 16 | | 5 | ee | 16 | +----+------+------+ # 降序 select * from author order by age desc; +----+------+------+ | id | name | age | +----+------+------+ | 4 | dd | 16 | | 5 | ee | 16 | | 3 | cc | 15 | | 1 | aa | 13 | | 2 | bb | 13 | +----+------+------+ # 年龄升序,姓名降序 select * from author order by age, name desc; +----+------+------+ | id | name | age | +----+------+------+ | 2 | bb | 13 | | 1 | aa | 13 | | 3 | cc | 15 | | 5 | ee | 16 | | 4 | dd | 16 | +----+------+------+
-
limit: 限制数据的数量
- limit m: m条
- limit start,m: start 开始的位置,m条
select * from author limit 3; +----+------+------+ | id | name | age | +----+------+------+ | 1 | aa | 13 | | 2 | bb | 13 | | 3 | cc | 15 | +----+------+------+ select * from author limit 1,3; # 从第二行开始,3条数据 +----+------+------+ | id | name | age | +----+------+------+ | 2 | bb | 13 | | 3 | cc | 15 | | 4 | dd | 16 | +----+------+------+
-
regexp: 正则
select * from author where name regexp '^a'; +----+------+------+ | id | name | age | +----+------+------+ | 1 | aa | 13 | +----+------+------+
.
:匹配任意单个字符。^
:匹配字符串的开始。$
:匹配字符串的结束。*
:匹配零个或多个前面的元素。+
:匹配一个或多个前面的元素。?
:匹配零个或一个前面的元素。[abc]
:匹配字符集中的任意一个字符。[^abc]
:匹配除了字符集中的任意一个字符以外的字符。[a-z]
:匹配范围内的任意一个小写字母。\d
:匹配一个数字字符。\w
:匹配一个字母数字字符(包括下划线)。\s
:匹配一个空白字符。
-
like:
%
任意多个字符,_
任意单个字符select * from author where name like "a%"; # 'a%' 匹配以字母 'a' 开头的任何字符串 +----+------+------+ | id | name | age | +----+------+------+ | 1 | aa | 13 | +----+------+------+ select * from author where name like "_a%"; # '_a%' 匹配第二个字母为 'a' 的任何字符串。4 +----+------+------+ | id | name | age | +----+------+------+ | 1 | aa | 13 | +----+------+------+
-
join:连接两个或以上的表
# stu表 +----+------+---------+ | id | name | classid | +----+------+---------+ | 1 | A | 1 | | 2 | B | 1 | | 3 | C | 2 | +----+------+---------+ # class表 +----+-----------+---------+ | id | name | teacher | +----+-----------+---------+ | 1 | 实验班 | 小红 | | 2 | 普通班 | 小蓝 | +----+-----------+---------+ # 使用join以后 select * from stu join class; +----+------+---------+----+-----------+---------+ | id | name | classid | id | name | teacher | +----+------+---------+----+-----------+---------+ | 1 | A | 1 | 1 | 实验班 | 小红 | | 1 | A | 1 | 2 | 普通班 | 小蓝 | | 2 | B | 1 | 1 | 实验班 | 小红 | | 2 | B | 1 | 2 | 普通班 | 小蓝 | | 3 | C | 2 | 1 | 实验班 | 小红 | | 3 | C | 2 | 2 | 普通班 | 小蓝 | +----+------+---------+----+-----------+---------+
- join 会将stu表中每一行与class表中每一行拼接,产生新的行,
- join 后的表是将两个表的列名加在一起的,可能产生相同的列名;
-
on: 一般使用了join后的表,并不是我们想要的,这时可以使用on来加一些条件
select * from stu join class on classid=class.id; +----+------+---------+----+-----------+---------+ | id | name | classid | id | name | teacher | +----+------+---------+----+-----------+---------+ | 1 | A | 1 | 1 | 实验班 | 小红 | | 2 | B | 1 | 1 | 实验班 | 小红 | | 3 | C | 2 | 2 | 普通班 | 小蓝 | +----+------+---------+----+-----------+---------+
-
union: 用于合并两个或多个 SELECT 语句的结果集
select id from str union select id from class; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ select id,name from stu union select id,name from class; +----+-----------+ | id | name | +----+-----------+ | 1 | A | | 2 | B | | 3 | C | | 1 | 实验班 | | 2 | 普通班 | +----+-----------+
- 每个 SELECT 语句必须拥有相同数量的列
- 列也必须拥有相似的数据类型。
- 每个 SELECT 语句中的列的顺序必须相同。
执行顺序:
from on join where group by having select distinct union order by
聚合函数:
- max: 最大值
- min:最小值
- sum:和
- count:数量
- avg:平均值
9. 连表操作
- inner join: 内连接,只拼接两张表中公有的数据部分,简写join
- left join: 左连接,左表所有数据都显示,没有对应的项就用NULL表示,
- right join: 右连接,右表所有数据都显示,没有对应的项就用NULL表示,
- union: 全连接,左右表所有数据都显示出来,如上关键字中的例子
子查询
分步骤解决问题,将一个查询语句的结果当做另外一个查询语句的条件去用
select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');
# 表的查询结果可以作为其他表的查询条件
# 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
# 关键字exists(了解)
# 只返回布尔值 True False
# 返回True的时候外层查询语句执行
# 返回False的时候外层查询语句不再执行
10. 视图
视图就是通过查询得到一张虚拟表,然后保存下来,下次可以直接使用其实视图也是表。如果要频繁的操作一张虚拟表(拼表组成的),你就可以制作成视图后续直接操作
# 固定语法
create view 表名 as 虚拟表的查询sql语句
# 报错 Duplicate column name 'id'
create view stu2class as select * from stu left join class on classid=class.id;
# 相同字段起别名
create view stu2class as select stu.id as stu_id,stu.name as stu_name,teacher from stu join class on stu.classid=class.id;
注意:对于相同字段,需要起别名
- 创建视图在硬盘上只会有表结构 没有表数据(数据还是来自于之前的表)
- 视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表
- 当你创建了很多视图之后 会造成表的不好维护,效率不高
11. 触发器
触发器可以帮助我们实现监控、日志...,触发器可以在六种情况下自动触发增前 、增后 、删前、删后 、改前、改后
# 语法
create trigger 触发器的名字 before/after insert/update/delete on 表名
for each row
begin
sql语句
end
# 应用
create trigger tri_before_insert_t1 before insert on t1
for each row
begin
sql 语句
end
create trigger tri_after_insert_t1 after insert on t1
for each row
begin
sql语句
end
重点:修改MySQL默认的语句结束符, 只作用于当前窗口 delimiter $$ 将默认的结束符号由;
改为$$
delimiter ;
# 案例
create table cmd (
id int primary key auto_increment,
user char(32),
priv char(10),
cmd char(64),
sub_time datetime, # 提交时间
success enum('yes','no')
)
create table errlog(
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
)
# 实现功能:当cmd表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据,NEW指代的就是一条条数据对象
delimiter $$ # 修改sql结束符
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
if NEW.success='no' then # 如果信息插入的数据的success值为no
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$ # sql语句结束
delimiter ; # sql语句结束符重新改为;
# 朝cmd表插入数据
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('jason','0755','ls -l /etc',NOW(),'yes'),
('jason','0755','cat /etc/passwd',NOW(),'no'),
('jason','0755','useradd xxx',NOW(),'no'),
('jason','0755','ps aux',NOW(),'yes');
# 删除触发器
drop trigger tri_after_insert_cmd;
12. 事务
开启一个事务可以包含多条sql语句 这些sql语句要么同时成功,要么一个都别想成功,称之为事务的原子性。
作用:保证了对数据操作的安全性
事务的四大特性:
- 原子性:不可分割,同时成功,同时失败
- 一致性:从一个一致的状态,变成另一个一致性状态
- 隔离性:一个事务的执行不能被其他事务干扰
- 持久性:一旦提交成功,对数据的修改是永久的
使用事务的步骤:
- 开启事务:
start transaction 或者 begin
- 设置保存点:
savepoint savepoint_name
- 回滚(回到事务执行之前的状态):
rollback
或者回滚到保存点rollback to savepoint_name
- 确认(确认之后无法回滚):
commit
# 模拟转账功能
create table user(
id int primary key auto_increment,
name char(16),
balance int
)
insert to user(name,balance) values('jason',1000),('egon',1000),('tank',1000);
# 开启事务
start transaction ;
# 多条sql语句
update user set balance=900 where name='jason';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='tank';
# 判断是否要提交还是回滚
if (条件) then
commit; # 提交事务
else
rollback; # 回滚事务
end if;
13. 存储过程
存储过程就是自定义函数,它内部包含一系列可以执行的sql语句,存储过程存放于mysql服务端中,可以直接通过存储过程触发内部sql语句执行。
三种开发模式:
- 程序员写代码开发,sql语句由数据库运维人员提前写好存储过程,供程序员调用
- 程序员需要写程序代码和sql语句
- 程序员只写代码,sql语句使用别人写好的第三方库如python的ORM框架
第一种基本不用,一般都是第三种,出现效率问题再动手写sql
# 固定语法
create procedure 存储过程的名字(参数1,参数2,....)
begin
sql 语句
end
# 调用
call 存储过程的名字
# 查询
show create procedure
# 删除
drop procedure if exists 存储过程的名字
# 具体使用
delimiter $$
create procedure p1(
in m int, # 只进不出 m不能返回出去
in n int,
out res int # 该形参可以返回出去
)
begin
select tname from teacher where tid>m and tid<n;
set res=666; # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ;
# 针对形参res 不能直接传数据 应该传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret;
变量分类
-
用户变量:在客户端链接到数据库实例整个过程中用户变量都是有效的
set @num=1 set @num:=1 select @num:=1; select @num:=字段名 from 表名 where ……,
-
局部变量:一般用在sql语句块中,只在当前begin/end代码块中有效
declare var_name [, var_name]... data_type [ DEFAULT value ]; declare c int default 0; set c=a+b; select c as C; # select into 语句句式: select col_name[,...] into var_name[,...] table_expr [where...]; declare v_employee_name varchar(100); declare v_employee_salary decimal(8,4); select employee_name, employee_salary into v_employee_name, v_employee_salary from employees where employee_id=1;
-
全局变量:全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改
set global var_name = value; #注意:此处的global不能省略。根据手册,set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION set @@global.var_name = value; #同上
-
会话变量:服务器为每个连接的客户端维护一系列会话变量
set session var_name = value; set @@session.var_name = value; set var_name = value; #缺省session关键字默认认为是session # 查看变量 select @@var_name; select @@session.var_name; show session variables like "%var%"; # session可以使用local代替
# 显示会话变量
show session variables;
show global variables
14. 函数
跟存储过程是有区别的,存储过程是自定义函数,函数就类似于内置函数。
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
15. 流程控制
# if判断
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
16. 索引
数据都是存在与硬盘上的,查询数据不可避免的需要进行IO操作,索引就是一种数据结构,类似于书的目录。索引在MySQL中也叫“键”,是存储引擎用于快速查找记录的一种数据结构primary key、unique key、index key
,foreign key
不是用来加速查询用的,不在我们的而研究范围之内。index key
没有任何的约束条件,只是用来帮助你快速查询数据。本质是通过不断的缩小想要的数据范围筛选出最终的结果。
注意事项:
- 当表中有大量数据存在的前提下 创建索引速度会很慢
- 在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低
- 索引不要随意的创建!!!
17. b+树
只有叶子节点存放的是真实的数据,其他节点存放的是虚拟数据。仅仅是用来指路的树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)
为什么将id字段作为索引:一个磁盘块存储是有限制的,占得空间少,一个磁盘块能够存储的数据多,那么就降低了树的高度,从而减少查询次数
聚集索引(primary key)
聚集索引指的就是主键,Innodb
只有两个文件 ,直接将主键存放在了idb表中,MyIsam有三个文件 ,单独将索引存在一个文件。
辅助索引(unique、index)
查询数据的时候不可能一直使用到主键,也有可能会用到name,password等其他字段,那么这个时候你是没有办法利用聚集索引。这个时候你就可以根据情况给其他字段设置辅助索引(也是一个b+树)。叶子节点存放的是数据对应的主键值,先按照辅助索引拿到数据的主键值,之后还是需要去主键的聚集索引里面查询数据
覆盖索引
在辅助索引的叶子节点就已经拿到了需要的数据
# 给name设置辅助索引 name='jason'
select name from user where name='jason';
# 非覆盖索引
select age from user where name='jason';
18. 其他
常见端口号
MySQL 3306
redis 6379
mongodb 27017
django 8000
flask 5000
mysql重置和修改密码
# MYSQL5.7 版本后不再支持password()函数和password字段。
# 修改密码
mysqladmin -uroot -p(紧跟原密码或不写) password 新密码
# 重置密码
# 1. 关闭mysqld服务 services.ms或任务管理器--服务--MySQL
# 2. 使用管理员打开cmd,
mysqld --skip-grant-tables
# 3. 进入mysql
mysql -uroot -p
# 4. 更新代码 password('新密码')是一种加密
# 5.6 mysql
update mysql.user set password=password('新密码') where user="root" and host="localhost";
# 5.7 mysql
update mysql.user set authentication_string=password('新密码') where user="root" and host="localhost";
# 5. 立即保存到硬盘
flush privileges;
# 6. 关掉--skip-grant-tables窗口,然后重新启动mysql服务
查看进程和杀死进程
# 1 如何查看当前具体进程
tasklist
tasklist | findstr mysqld
# 2 如何杀死具体进程(只有在管理员cmd窗口下才能成功)
taskkill /F /PID PID号
严格模式
STRICT_TRANS_TABLES:strict_trans_tables;
show variables like "%mode";
# 模糊匹配/查询
# 关键字 like
# %:匹配任意多个字符
# _:匹配任意单个字符
# 修改严格模式
set session # 只在当前窗口有效
set global # 全局有效
set global sql_mode = 'STRICT_TRANS_TABLES';
# 修改完之后 重新进入服务端即可
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库