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: 切换数据库db1
select 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对比：
1. 存储空间：char浪费空间（固定的字符存数据），varchar节省空间（存的时候需要制作报头，1bytes），
2. 存取速度：char存取简单，varchar存取复杂（取的时候先读取报头，），
枚举

enum(选择1，选择2，...) 多选一
集合

set(选择1，选择2，...) 多选多

date	日期格式 2000-12-1
time	时间格式 12:24:23
datetime	2008-12-2 22:06:44
timestamp	自动存储记录修改时间
Year	年2008

char(m)	固定长度，最多255个字符，位数不够是空格(默认)补位
varchar(m)	固定长度，最多65535个字符，位数不够有几个存几个

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';
    
# 修改完之后 重新进入服务端即可

posted @ 2024-01-05 22:52 转角90 阅读(9) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

转角90

mysql

1. 简介

2. 数据库的分类

2.1 关系型数据库

2.2 非关系型数据库

3. SQL语句的使用

3.1 SQL语句以;分号结尾

3.2 基本命令

3.3 游客模式

3.4 库操作

3.5 表操作

3.6 数据操作

4. 存储引擎

5. 数据的类型

6. 约束条件

7. 表与表之间

7.1 外键：

7.2 表关系

一对多

多对多

一对一

8. 表的查询

关键字：

聚合函数：

9. 连表操作

子查询

10. 视图

11. 触发器

12. 事务

13. 存储过程

变量分类

14. 函数

15. 流程控制

16. 索引

17. b+树

聚集索引(primary key)

辅助索引（unique、index）

覆盖索引

18. 其他

常见端口号

mysql重置和修改密码

查看进程和杀死进程

严格模式

公告

3.1 SQL语句以`;`分号结尾