码上编程

外观

mysql常见操作 ​

什么是sql ​

SQL(英文全称: Structured Query Language)是结构化查询语言，专门用来访问和处理数据库的编程语言。能够让我们以编程的形式，操作数据库里面的数据。

  1. SQL是一门数据库编程语言
  2. 使用SQL语言编写出来的代码，叫做SQL语句
  3. SQL语言只能在关系型数据库中使用（例如MySQL、Oracle、SQL Server)。非关系型数据库（例如Mongodb)不支持SQL语言

sql的学习 ​

  • DDL(data defination language)数据库定义语言
  • DML(data manipulation language)数据操作语言
  • DQL(data query language)数据查询语言

命令行连接mysql数据库 ​

bash
mysql -h127.0.0.1 -uroot -proot -P3306

数据库database操作 ​

sql
-- 查看数据库
show
databases;
-- 创建数据库
create
database db1;
create
database if not exists db1;
-- 删除数据库
drop
database db1;
-- 条件删除数据库
drop
database if exists db1;
--  操作数据库表格
use
db1;

数据库表操作 ​

sql
-- 查看数据库中的表
show
tables;
-- 创建表格
create table tb_1
(
    id       int,
    username varchar(255),
    book     text
);
-- 修改表名
alter table tb_1 rename to tb_2;
-- 展示表结构
desc tb_1;
    -- 修改数据类型
alter table tb_1 modify book varchar (255);
-- 修改字段
alter table tb_1 change username book text;
-- 添加字段
alter table tb_1
    add weight int(11);
-- 添加字段到指定字段后面
alter table tb_1
    add weight int(11) after age;
-- 添加到第一个
alter table tb_1
    add weight int first;
-- 删除字段
alter table tb_1 drop weight;
-- 删除表格
drop table tb_1;
对表格添加数据
-- 添加
insert into tb_1 values(1,'zly',"水浒");
    -- 局部添加
insert into tb_1(id, username)
values (1, 'zly');
-- 添加多条数据
insert into tb_1
values (1, 'zly', "水浒"),
       (2, 'zzz', "西游记");
-- 修改数据 全表操作慎用
update tb_1
set books="";
-- 修改指定数据
update tb_1 books=""
where username="zly";
-- 修改多个字段
update tb_1 books="",id=1314
where username="zly";
-- 删除数据
delete
from tb_1
where username = "zly";
-- 删除表中所有数据
delete
from tb_1;
-- 删除所有数据
truncate tb_1;

查询数据 ​

sql
-- all 查询所有 可省略
select [all] *
from tb_1;
-- distinct 去重查询
select distinct *
from tb_1;
-- 字段查询
select (id, username)
from tb_1;
-- 取别名
select (id[序号], username[姓名])
from tb_1 [tb];

--where 字句 查询条件
-- = > < != ...
select *
from tb_1
where age >= 18;
-- between and
select *
from tb_1
where age between 17 and 19;
-- [not] in 在[不在]集合里
select *
from tb_1
where age in (1, 23, 4, 5, 6, 62, 7, 10);
-- 匹配查询
select *
from tb_1
where username like "zly";
-- _ 一个字符
--  % 任意字符
select *
from tb_1
where username like "%王_";
-- is [not] null;
-- 查询[不] 为空
select *
from tb_1
where username is null;
-- 多重查询
select *
from tb_1
where username is not null
  and age > 18;

-- order by 字句 排序 [asc|desc] asc 升序(默认) desc降序
select *
from tb_1
order by age desc;
-- limit [offset,]n offset偏移量   n个数
-- 为方言 仅在 mysql中有
-- 从开始查询 一千个数据
select *
from tb_1 limit 1000;
-- 从第十一个查询 一千个数据
select *
from tb_1 limit 10,1000;

-- group by 分组查询
select *
from tb_1
group by age;
-- 聚合函数
-- count
-- sum
-- max
-- avg

--having 子句
select max(age)
from tb_1
where username is not null
group by sex
having id > 100
order by desc limit 0, 1000;
-- 字句顺序
from | where | group by (having )|order by| limit;

约束 ​

  • 候选键 表内唯一确定的字段
  • 主键 其他没有被选定的就叫候选键
  • 外键 不是当前表的主键 别的表的主键

关联查询 ​

sql
-- 创建表
create table tb_student
(
    id   int primary key,
    name varchar(255),
    age  int,
    sex  varchar(255)
);
-- 主键值不能为空
-- 添加主键约束
alter table tb_student
    add primary key (id)
-- 删除主键约束
alter table tb_student drop primary key
-- 外键约束 外键必须来自主标中的主键
-- 添加外键约束时 主表要存在
foreign key ... reference
create table tb_student
(
    id   int primary key,
    name varchar(255),
    age  int,
    cid  int,
    constraint fk_student_ciid foreign key (cid) reference tb_cLass(id)
)

用于关联查询 唯一约束 unique 不可以重复 可以null

not null 不为空

default 默认约束 default "男"

自增 auto_increment

索引 ​

索引（index）是帮助mysql高效获取数据的数据结构。 索引是数据结构 可以简单理解为排好序的快速查找数据结构 二叉树 提高数据的检索效率 降低数据的IO成本 降低排序成本 降低cpu消耗

索引也是一张表 占空间 大大提高查询速度 降低表的更新表的速度 索引只是提到效率的一个因素 建立优秀的索引 优化查询语句

不要建太多索引

  • 单值索引 及一个索引只包含单个索引，一个表可以有多个单列索引
  • 唯一索引 索引值唯一
  • 复合索引 及多个列 基本语法
sql
-- 创建索引
create
[unique] index indexname on tb_1(colomn,...)
sql
show
index from tb_1;

mysql 索引结构 Btree索引 原理 3 层的b+树可以存储上百万数据 Hash full-text R-Tree 创建索引 那些情况需要创建 频繁查询 哪些情况不要创建索引 表记录太少 经常更新的表 数据重复且平均的表字段 性能分析 Mysql query optimizer 优化器模块 Mysql 常见瓶颈 Explain Explain的使用 使用explain关键字 模拟优化器 从而知道如何处理sql语句 explain [SQL语句]

能干嘛 各字段的意思

type System>const> extra

  • using filesort 无法借助索引排序 按文件排序
  • using temporary 创建临时表
  • using index 用了索引 good 索引优化 避免索引失效
sql
-- 全职匹配 我最爱
-- 1 2 3 =》 1 2 3
-- 索引失效
-- 1 2 3 =》 2 3
-- 最佳左前缀法则 中间兄弟不能断
-- 1 2 3 =》1 3
-- 不要在索引列做操作（计算 函数 自动 or 手动）
select *
from user
where left (name, 4) = "july";
-- 索引中范围条件
-- 范围之后全失效
-- range
select *
from user
where c1 = "111"
  and c2 = "222"
order by c3, c2;
-- c2是const
select *
from user
where c1 = "c1"
  and c2 = "c2"
  and c3 = "c3"
  and c4 > "c4";
select *
from user
where c1 = "c1"
  and c2 = "c2"
  and c4 > "c4"
  and c3 = "c3";
-- 4个索引
select *
from user
where c1 = "c1"
  and c2 = "c2"
order by c3;
-- 2个索引

filesort

temporary !!! 分组必排序

会有临时表

like 出现的索引失效 的情况

全值匹配我最爱，最左前缀要遵守; 带头大哥不能死，中间兄弟不能断; 索引列上少计算，范围之后全失效: LIKE百分写最右，覆盖索引不写星: 不等空值还有or，索引失效要少用；

小表驱动大表 及小的数据驱动大的数据集 查询优化 慢查询日志 批量数据脚本插入 Show Profiles 全局查询日志

1 explain

  1. 观察 先运行
  2. 开启慢查询日志
  3. explain +慢sql分析
  4. show profile
  5. DBA 进行SQL数据库调优 参数优化 。。。存储引擎

慢查询日志 默认关闭慢查询日志 不是调优需要 不要随意开启

开启

重启后失效 如果需要永久失效就得修改配置文件 查看慢sql的时间

设置慢sql时间

没反应？

重开会话

模拟一个慢查询SQL

查看日志

日志分析工具 mysqldumpslow

sql
-- 创建函数
-- 函数和存储过程
create
-- 创建函数出错
-- this function has none of DETERMINISTIC
-- 批量插入数据查询
delimiter $$
CREATE FUNCTION rand_String(n INT) RETURN VARCHAR(255)
BEGIN;
declare
char_str varchar(100) default 'qwrtyuiuytsasfghjhgfdfhjk' ;
END $$

大数据插入 会报错

修改参数

创建函数

sql
delimiter
$$;
create function rand_string(n int) returns varchar(255)
begin
declare
chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnm';
declare
return_str varchar(255) default '';
declare
i int default 0;
while
i<n do
set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set
i=i+1;
end while;
return return_str;
end $$
delimiter ;

-- 创建存储过程
delimiter
$$;
create procedure insert_table(in start int (10), in max_num int (10))
begin
declare
i int default 0;
set
autocommit = 0;
repeat
set i=i+1;
---插入语局
insert into test()
values () util i=max_num
end repeat;
commit;
end $$
delimiter ;
调用存储过程
call insert_table
show profile

默认关闭 并保存15次运行结果

开启

运行并查看

诊断sql

全局查询日志 永远不要在生产环境开启 记录所有日志 mysql 锁机制 表锁和行锁 读锁 针对一份 写锁 engine myisam; 表锁 加 读锁 共享锁

sql
lock
table tb1 read;

不可以被自己修改 别人被修改会阻塞

正在阻塞 需要解锁

sql
unlock
tables;

加写锁

sql
lock
table mylock write;

自己可以查看更改 别人不能 读

解锁

查看 表锁定

行锁 InnoDB 与 MyISAM 的不同

一 支持事务 二 采用行级锁 ACID 原子性 一致性 隔离性 持久性 更新丢失 脏读 幻读 不可重复读 幻读 默认隔离级别 可重复度 未提交读 已提交读 可重复读 可序列化 查询数据库默认事务级别mysql 8（mysql 5 tx_isolation）

产生阻塞 行锁

commit; 解除阻塞

间隙锁的危害

锁定一行

阻塞

commit；

行锁总结

sql
show
status like 'innodb_row%';

查看状态

避免无索引升级为表锁 varchar 加 '' 页锁