相关概念
1、数据库是什么？
数据库本质就是一个C/S的套接字软件
常见的数据库：
关系型：
mysql
mariadb
oracle
db2
sqlserver
非关系：
存取数据都是以key:value
mongodb
redis
memcache
2、数据库相关概念
数据库服务器：运行有数据库管理软件的计算机
数据库管理软件mysql：就是一个套接字服务端
库：就是一个文件夹
表：就是一个文件
记录：就相当于文件中的一行内容（抽取事物一系列典型的特征拼到一起，）
数据：用于记录现实世界中的某种状态
数据库管理软件：本质就是个c/s架构的套接字程序
服务端套接字 客户端套接字
操作系统：linux 操作系统
计算机（本地文件） 计算机硬件
数据库管理软件DBMS->套接字软件
数据库服务器-》安装有DBMS服务端的计算机
例如：
关系型数据库管理软件
mysql、oracle
去IOE
非关系型数据库管理软件
redis、memcache、mongodb
sql语句：套接字管理软件的作者为使用者规定的命令规范
数据库核心概念总结：
数据-----------------》事物的状态
记录-----------------》文件中的一条信息
表------------------》一个文件
库------------------》文件夹
数据库管理软件--------》套接字程序：mysqld、mysql
数据库服务器----------》运行mysqld的计算机
跳过mysql密码
破解密码：
mysqld --skip-grant-tables --user=mysql #跳过授权表
update mysql.user set password=password("111") where user="root" and host="localhost"; #修改密码并指定用户和地址
flush privileges; #刷新
本地修改密码
mysqladmin -uroot -p"111" password""
Nginx是用了epoll I/O模型，为了解决某一种问题，为什么叫epoll I/O模型，它提供了一种解决问题的套路，把单线程下检测多个任务的I/O行为，实现遇到I/O就去切换，解决高并发的问题
提交任务的两张方式：
同步（等一个任务结束，拿到返回值再进行下一行代码）
异步（提交完任务继续运行下一行代码，不管有没有拿到返回值），通常结合回调使用
sql语句-增删改查
库->文件夹
增
create database day01 charset utf8mb4;
改
alter database day01 charset gbk;
查
show databases;
show create database day01;
删
drop database day01;
create database day01;
表-》文件
use day01;
select database();
增
create table t1(id int,name varchar(16));
改
alter table t1 rename t2;
alter table t2 modify name varchar(10);
查
show tables;
desc t2;
删
drop table day01.t2;
表相关操作
1、表的类型-》存储引擎
#innodb：支持事务、行级锁、外键
2、数据类型
3、表约束
not null #不为空
default #设置默认值
unique #唯一
primary key #主键，且唯一
auto_increment #自增
unsigned #无符号
foreign key #外键
4、表的三种关系
多对一
一对一
多对多
多对一
# 创建表时需要先建立被关联表(例如被关联表名为dep，关联表名为emp)
# 再创建关联表
# 插入数据时，应该先往dep插入数据，再往emp插入数据
多对多
#emp2dep（需要创建第三张表）
一对一
#fk(dep_id)+uk（唯一字段）
表详细操作
# 表--------------------------文件
# 存储引擎engine 文件的类型（jpg、png、mp4、mp3、txt）
myisam
innodb（默认）
blackhole
memory
create table t1(id int)engine=innodb; 文件.mp4
innodb存储引擎：本质就是一段处理程序 视频播放器
# 存储引擎实验
create database day02;
use day02;
create table t1(id int)engine=myisam;
create table t2(id int)engine=innodb;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;
insert t1 values(111);
insert t2 values(222);
insert t3 values(333);
insert t4 values(444);
# 创建表
create table t5(
id int,
name varchar(8) not null
);
# 插入记录
insert t5(name,id) values
("xxx",2),
("tom",1);
insert t5(name) values
("xxx"),
("yyy");
# 修改表
alter
# 复制表
create table t6 select host,user,password from mysql.user;
create table t7 select host,user,password from mysql.user where 1 < 0;
# 表字段之数据类型
# 数字类型
create table t8(id int(1));
insert t8 values(1212312312312312312);
create table t9(id int);
create table t10(x float(255,30));
create table t11(y double(255,30));
create table t12(z decimal(65,30));
insert t10 values(1.1111111111111111111111111111111);
insert t11 values(1.1111111111111111111111111111111);
insert t12 values(1.1111111111111111111111111111111);
# 日期类型
create table student(
id int,
name varchar(16),
birth date,
class_time time,
reg_time datetime,
born_year year
);
insert student values
(1,'xxx',"1993-01-23","08:30:00","2020-3-23 09:30:21","1993");
create table t13(x datetime);
create table t14(x timestamp);
create table t15(id int,name varchar(16),commit_time timestamp);
# 字符类型
create table t16(name char(4));
create table t17(name varchar(4));
insert t16 values("你好啊哈哈");
insert t17 values("你好啊哈哈");
insert t16 values("你"); -- "你 "
insert t17 values("你"); -- "你"
|a |ab |abc |abcd|
|1byte+a|1byte+ab|1byte+abc|1byte+abcd|
# 枚举与集合
create table t18(
id int,
name varchar(16),
gender enum("男","女","未知"),
hobbies set("play","music","read","movie")
);
insert t18 values
(1,"xxx","男","play,music");
记录-》文件中的一行内容
create database day02;
use day02;
create table t1(id int,name varchar(16));
增
insert t1 values
(1,"xxx"),
(2,"tom"),
(3,"jack");
改
update day02.t1 set name = "lili" where id = 2;
查
select * from day02.t1;
select name from day02.t1;
select name from day02.t1 where id >= 2;
删
delete from day02.t1 where id = 2;
数据类型
数据类型
int
float(255,30)
double(255,30)
decimal(65,30)
date
time
datetime/timestamp
year
enum()
set()
char(16)
varchar(16)
简单查询
查询格式
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组字段
having 条件
order by 排序字段
limit 限制条数
from》where》group by》having》distinct》order by》limit #优先级
avg(),count(),max(),min(),sum()，分组之后才能用聚合函数
1、select
select * from employee;
select name,post from employee;
select distinct post from employee;
select name as 名字,salary*12 as annual_salary from employee;
select concat("名字",":",name) as new_name,concat("薪资",":",salary) as new_salary from employee;
select concat_ws(":",name,post,salary) from employee;
SELECT
(
CASE
WHEN NAME = 'xxx' THEN
NAME
WHEN NAME = 'yyy' THEN
CONCAT(name,'_BIGSB')
ELSE
concat(NAME, 'SB')
END
) as new_name
FROM
employee;
2、where
select * from employee where id >= 10;
select * from employee where id >= 10 and id <= 13;
select * from employee where id between 10 and 13;
select * from employee where id = 10 or id = 13 or id = 15;
select * from employee where id in (10,13,15);
select * from employee where name like "ji%";
select * from employee where name like "ji_";
#regexp 正则表达式
3、group by
select post,max(salary) from employee group by post;
select post,min(salary) from employee group by post;
select post,avg(salary) from employee group by post;
select post,sum(salary) from employee group by post;
select post,count(id) from employee group by post;
select sex,count(id) from employee group by sex;
连表查询
格式
select * from t1 inner join t2
on t1.字段 = t2.字段;
select * from t1 left join t2
on t1.字段 = t2.字段;
select * from t1 right join t2
on t1.字段 = t2.字段;
select * from t1 left join t2
on t1.字段 = t2.字段
union
select * from t1 right join t2
on t1.字段 = t2.字段;
案例
两个表分别为：‘employee’，’department。
# 内链接：只保留有对应关系的记录
select * from employee,department where employee.dep_id = department.id;
select * from employee inner join department on employee.dep_id = department.id;
# 左链接：在内链接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id;
# 右链接：在内链接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id;
# 全外链接：full join，在内连接的基础上，左右两表没有对应关系的记录也留下来
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
select department.name,avg(age) from employee inner join department on employee.dep_id = department.id
group by department.name;
子查询
格式
select * from t1 where id in
(select id from t2);
select * from t1 where id =
any(select id from t2)
select * from t1 where id >
any(select id from t2)
select * from t1 where id >
all(select id from t2)
select * from t2 where exists (...);
not in 不支持null
案例
两个表分别为：‘employee’，’department。
select * from employee where dep_id =
(select id from department where name="技术");
select * from employee where dep_id in
(select id from department where name="技术" or name ="销售");
select * from employee where dep_id =
any(select id from department where name="技术" or name ="销售");
select * from employee where age > any(
select avg(age) from employee group by dep_id);
select * from employee where age < any(
select avg(age) from employee group by dep_id);
select * from employee where age > all(
select avg(age) from employee group by dep_id);
select * from employee where age < all(
select avg(age) from employee group by dep_id);
select * from department
where exists (
select * from employee where employee.dep_id = department.id
);
select * from department
where not exists (
select * from employee where employee.dep_id = department.id
);
select * from employee inner join
(select post,max(hire_date) as m from employee group by post) as t
on employee.post = t.post
where employee.hire_date = t.m;
视图
create view emp_dep_view as
select employee.*,department.name as depname from employee inner join department
on employee.dep_id = department.id;
alter view emp_dep_view as
select employee.*,department.name as depname from employee inner join department
on employee.dep_id = department.id;
drop view emp_dep_view;
触发器
#准备表
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') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
#创建触发器
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN #等值判断只有一个等号
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
END IF ; #必须加分号
END//
delimiter ;
存储过程
delimiter //
create procedure p1()
BEGIN
select * from user;
INSERT into user values(7,"yyyy","7777");
END //
delimiter ;
delimiter //
create procedure p4(
in n1 int,
out n2 int
)
BEGIN
select * from user where id > n1;
set n2 = 100;
END //
delimiter ;
索引
什么是索引？
索引是存储引擎中一种数据结构，或者说数据的组织方式，又称之为键key
为数据建立索引就好比是为书建目录
为何要用索引？
为了优化查询效率
ps：创建完索引后会降低增、删、改的效率
好就好在读写比例10:1
如何正确看待索引？
开发人员最懂业务，任何一个软件都有其吸引用户的亮点
亮点背后对应的是热数据，这一点开发人员是最清楚的，
开发人员最了解热数据对应的数据库表字段有哪些，所以
应该在开发软件的过程中就提前为相应的字段加上索引，而不是
等到软件上线后，让DBA发现慢查询sql后再做处理，因为
1、一个软件慢会影响用户体验，但是慢的原因有很多，你不能立即确定
是sql的问题，所以等到定位到sql的问题，可能已经过去了很久，问题已经被
拖了很久了
2、因为大多数DBA都是管理型DBA而非开发型，所以即便是DBA从日志中看到了慢查询sql，
也会因为其不懂业务而很难分析出慢的原因，最红这顶锅还是得扣在你开发的脑袋上，
躲得过初一躲不过十五啊
索引到底是一种什么样的数据结构：B+树
二叉树、平衡二叉树、B树=》B+树
表--》书
记录-》一页内容
索引-》书的目录
select * from user where id=12; #命中索引
select * from user where name="xxx"; # 无法命中索引
select * from user where id > 12 and id < 15;
B+树优点：
1、在二叉树、平衡二叉树、B树的基础上做了进一步优化，
只有叶子节点放真正的数据，这意味着在等量数据的前提下,B+树的高度是最低的
2、B+的叶子节点都是排好序的，这意味着在范围查询上，B+树比B树更快，
快就快在一旦找到一个树叶节点，就不需要在再从树根查起了
innodb存储引擎索引分类：
1、hash索引：更适合等值查询，不适合范围查询
2、B+树索引
聚集索引/聚簇索引-》以主键字段的值作为key创建的索引（一张表中只有一个）
辅助索引：针对非主键字段创建的索引（一张表中可以有多个）
innodb-》索引组织表
举例：
id字段为主键
我们为name字段创建了辅助索引
select name,age,gender from user where id =3; # 用的是聚集索引，所以压根不需要回表
select name,age,gender from user where name="xxx"; # 需要回表
回表查询:通过辅助索引拿到主键值，然后再回到聚集索引从根再查一下
覆盖索引：不需要回表就能拿到你要的全部数据
select name,age,gender from user where name="xxx"; # 没有覆盖索引，需要回表
select name,id from user where name="xxx"; # 覆盖了索引
250w条记录-》ibd文件的大小167M
索引的简单使用
create table t1(
id int,
name varchar(10),
);
create index idx_xxx on t1(id);
drop index idx_xxx on t1;
命中索引也未必能起到很好的提速效果
1、对区分度高并且占用空间小的字段建立索引
2、针对范围查询命中了索引，如果范围很大，查询效率依然很低，如何解决
要么把范围缩小
要么就分段取值，一段一段取最终把大范围给取完
3、索引下推技术（默认开启）
4、不要把查询字段放到函数或者参与运算
select count(*) from where id*12 = 3;
select count(*) from where id = 3/12;
5、索引覆盖
6、最左前缀匹配原则
create index idx_id_name_gender on s1(id,name,gender);
当查询条件中出现以下字段，可以命中联合索引，因为符合最左前缀原则
id
id name
id gender
id name gender
查询条件中只出现一个gender字段，不能命中
gender
事务
1、原子性
2、一致性
3、隔离性
4、持久性
并发开启多个事务去操作同一份数据，有可能会引发数据库读现象
1、脏读
2、不可重复读
3、幻读
解决方案
数据库锁机制
mvcc
事务隔离机制
锁机制
insert delete update-》排它锁（X）
select 默认不加锁，可以加
select ... from 表 ... for update; 排它锁（X）
select ... from 表 ... lock in share mode; 共享锁（S）
1、数据库锁机制
1、一旦事务1对数据A加了排它锁
那么其他事务无法对数据A加任何锁
只有事务1可以操作数据A，而且可以读也可以写
2、一旦事务1对数据A加了共享锁
那么其他事务可以对数据A加锁，但只能加共享锁
一旦多个事务都会数据A加了共享锁，大家都只能读不能改
1、什么是事务
ACID
transactions，一个事务里可以包含多条sql语句
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
2、为何要有事务
防止有脏数据等
3、开启事务
start transaction; 或者begin;
commit; 提交事务
rollback;回滚事务
commit或者rollback事务都结束
start transaction;
update user set name="xxx" where id =1;
commit;
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
