数据库学习笔记

数据库(基于innoDB引擎)：

连接数据库：

mysql -uroot -p

mysql -uroot -xxx

退出数据库：

exit/quit/ctrl+d

显示时间

select now();

显示版本

select version();

查看所有数据库

show databases;

创建数据库

create database xxx charset=utf-8

如果不指定utf-8则默认数据类型为拉丁文

查看数据库编码

show create database xxx;

删除数据库

drop database xxxx;

`符号包裹名字防止mysql将其拆分（应对与特殊名称数据库）

查看当前正在使用的数据库

select database();

选择使用数据库

首先应该选择一个数据库使用

use xxx;

数据库之数据表：

数据库是一个整体概念，在里面有数据表的概念

查看数据库中的表

show tables；

创建表

--create table xxx(数据名称 类型 约束，……)
create table xxxx(id int,name varchar(30));

--添加约束
create table xxxx(
    id int primary key not null auto_increment,
    name varchar(30))
    
create table xxx(
id int unsigned not null auto_increment primary key, 
name varchar(30),
age tinyint unsigned,
high decimal(5,2),--总共五位 有两位小数
gender enum("男"，"女","中性","保密") default "保密",
cls_id int unsigned)

varchar是按数据长度分空间

char是直接分好不管数据是否装满

查看表的内容

desc xxx;

插入数据

insert into xxx values(id,name,age,high,gender,cls_id)
--主键为auto自动增长时，将忽略id的值
insert into xxx(name,age) values("xxx","x"),("xx1","y"),……
--指定插入，批量插入

修改数据

update 表名 set 列1=值1，列2=值2，…… where 条件；

不写where条件全部字段数据都修改

查询数据

select * from xxx;
select * from where 字段;
select 列1，列2…… from xxx
--自定义名称
select 列1 as newname，列2 as newname…… from xxx

--查询hegiht为空的数据
select * from students where height is null;
									is not null;
--去重
select distinct name from user

范围查询 between

--介于条件的
select xxx from 表 where yy in (param1,param2,……)
select name,age from students where age not in (12,18,24)
--介于a到b
select xxx from 表 where yy between a and b
select * from students where age between 18 and 34;
select * from students where age not between 18 and 34;
select * from students where not age between 18 and 34;

分页查询 limit

limit语法一定要在最后面

--从第一行数据到第武行数据 id 1-5
select * from students[where可选] limit 0[start],5[step]
select name from students where age=10 limit 0,4;
从students表选择age=10的数据，且从第一个数据开始，仅返回4个

排序 order by

--取年龄18到34 性别为1的数据并且以age排序
select * from students where (age between 18 and 34) and gender=1 order by age;
---如果age无法区分则用height进行排序以此类推添加任意字段
select * from students where (age between 18 and 34) and gender=1 order by age ,height desc;

• asc:从小到大
• desc：从大到小

分组

与聚合函数一起用，否则没用

select gender,count(*)  from students group by gender;

select gender,group,group_concat(name) from students group by gender;
--分开数据 group_concat 写什么打印什么
select gender,group,group_concat(name,'_',age) from students group by gender;

--以年龄分组并且组的平均年龄大于30的
select gender,group_concat(name) from students group by gender having avg(age)>30;

关联查询：

内链接

select * from student inner join classess;

inner join为固定语法

select * from students inner join classes on students.cls_id=classes.id;

select students.*,classes.name from students inner join classes on students.cls_id=classes.id

外连接

select * from students as s left join classes as c on s.cls_id=c.id;

把students表(left join左侧)作为基准，12，13，14没有对应班级，但我是左连接，所以学生是要打印出来，但其他没有的数据你可以给我设置成null

判断结果用where 从结果集找东西用having

外连接

调换两个表位置，换为left join

select s.name from classes as c left join students as s on s.cls_id!=c.id;

聚合函数

count 统计数据

select count(*) from students;

max 取最大

select max(age) from students;

min 取最小

sum 取总和

avg取平均值

round 取小数 round(字段,保留几位)

小数尽量不要存在数据库容易产生误差，建议扩大小数点倍数，使用时缩小倍数

修改表结构

添加表结构

增加一个birthday字段

alter table xxx add birthday datatime;

修改表结构

--修改表结构的数据类型未date
alter table xxx modify birthday date;
--修改表的字段 将birthday改为birth
alter table xxx change birthday birth date default "1990-01-01"

删除表结构（字段）

--删除xxx表中的yyy字段
alter table xxx drop yyy;

删除数据表

drop table xxx;

数据库不要轻易删除数据，可以新建一个字段，标记该数据是否删除 1/0

外键

连接两个相关联数据库时候保证A数据库对B数据库所引用的数据都存在

例如：

班级表

成员表

12，13，14数据的班级id 在班级表不存在，但依然添加了，这就是没有应用外键

数据库进阶：

视图

视图就是一张虚拟的表，对若干张表的虚拟的引用，当有表改动时候，不必修改每一条sql语句

create view xxx as sql语句

drop view xxx

将一个sql语句查询的结果作为一个新表

往往用在查询数据，多表关联情况下合到一个表中

并非是创造一个实际存在的表放在数据库，只是一个关联集合

修改真表，虚拟表也会相应的改变(这也体现了视图就是sql语句)

1. 提高了重用性，像一个函数
2. 数据库重构，不影响程序运行
3. 提高了安全性能，可以对不同用户
4. 让数据更加清晰

事务

一个对数据库操作的系列操作

为什么用？

能够保整数据修改要么成功，要么不成功

在增删改有用

事务中所有步骤要么全部成功，要么必须回滚所有步骤。

mysql控制台开启事务：

begin

执行后，如果客户端A对数据库进行了修改，但没有手动commit，那么mysql将自动锁住数据库，不允许其他客户端(前提是没有使用begin，如果是修改的一行可能会抛出数据类型错误)对其数据进行修改(隔离性)

如果不执行begin，mysql已经默认在原生sql语句提交后，自动commit

• 原子性：要么成功，要么全部失败回滚
• 一致性：数据 0 or 1 是或否
• 隔离性：客户端A修改一行数据时，如果不commit，另一个客户端无法修改该行数据，即对另一个客户是隔离的，看不见的，此外，A用户读取修改数据库但并没有提交A回滚了，B这个时候读的是A将要提交但未提交的数据，容易造成B对数据库的误判，从而造成脏读
• 持久性：永久保存

回滚

客户A和客户B同时进入事务处理，A对数据进行了修改，B也对同一个数据进行了修改，A率先提交commit，紧接着B也提交，如果B提交后出现了一定的数据库底层错误(如数据不符合 无符号数类型 出现负数)，这个时候B就需要执行回滚来更新一下数据库（B此时位于事务中）

行锁

对同一行修改会出现行锁

索引

一种特殊的文件（写到数据表的索引表空间）与db文件合二为一，

建立索引

读写=10：1

提高查询效率 大数据而言（一天可能发一个发朋友圈，但是刷好几条别人的）

create index 索引名 on 表名(字段名称(长度)); 
--如果是字符串可以指定长度，其他类型可以不用指定

通过缩小想要获得数据的范围来筛选数据范围

基础支撑：B树

主键，外键自动创建了索引

数据很少/不常用不需要建立索引，建立太多的索引会影响更新和插入的速度(B树的树枝会修改，这么多树枝改来改去又慢了)和占用空间

查看索引

show index from 表名；

删除索引

drop index 索引名称

主从配置

1. 备份主服务器数据库
2. 减轻主服务器压力，让请求在从服务器种查询读取，在主服务器进行写，因为主服务器写了自动同步到从服务器（读写分离）

备份恢复

SQL注入

SQL注入关键是单引号的闭合

利用`这个符号去拼合sql语句 进行注入

不要自己去拼接sql原生语句

并发读取：

读取数据库数据可以不用加锁，但如果是对数据进行更新，则需要加事务锁/互斥锁/共享锁/

读锁：

什么时候使用呢，就是当前线程所读取的数据，在此期间，自己不去修改，其他人也不去修改，这个时候就要加读锁，为了是保证数据一致性，比如说用户A去银行查询个人银行记录，在加载数据的时候需要验证用户A的密码，在此期间，用户A收到了一笔转账，用户余额已经被修改了，但我展示给用户A的余额没有变动，为此需要加一个读锁，让转账信息搁置，等我用户A读取个人信息完成后，再让转账请求进入修改数据库

创建表必须要声明索引，否则将会锁住整个表

写锁：

悲观锁：

谁先拿锁谁执行，拿不到的就等待，用完的释放锁，原因是因为操作系统的进程切换，不知道谁拿到

select * from student where id=1 for update;加锁

乐观锁：

查找的时候不加锁，在更新的时候要校验初始数据，如果符合条件则执行，否则更新失败

xxx初始数据是1 修改为0 在更新的时候确保初始化是xxx=1

update table set xxx=0 where id = 1 and xxx=1

先查修改项然后更新修改项

有时候库存充足，但仍然导致有一方无法修改数据，因为在A修改后B检验改变了，所以B修改失败，但库存充足，不是临界条件(0，1),所以使用乐观锁还需要加个循环，让代码重复执行3次（经验），如果还是失败就失败了。

修改mysql隔离级别为读已提交，否则其他进程读的还是原来的没有变化的

何时用乐观锁？

冲突比较少的时候用乐观锁，提高性能，避免锁开销，冲突多用悲观锁

数据库优化

表结构设计优化

三范式设计优化