数据库学习笔记

数据库(基于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 字段;
select1,列2…… from xxx
--自定义名称
select1 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 fromwhere yy in (param1,param2,……)
select name,age from students where age not in (12,18,24)
--介于a到b
select xxx fromwhere 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隔离级别为读已提交,否则其他进程读的还是原来的没有变化的

何时用乐观锁?

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

数据库优化

表结构设计优化

三范式设计优化

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 像素格子 设计师:CSDN官方博客 返回首页