数据库

最新推荐文章于 2024-08-14 19:44:34 发布

weixin_30706507

最新推荐文章于 2024-08-14 19:44:34 发布

阅读量246

点赞数

文章标签：运维数据结构与算法数据库

原文链接：http://www.cnblogs.com/WilliamKong94/p/11272925.html

版权

数据库基本概念

数据库基本概念

数据库是什么

存储数据的仓库

列表，字典...等等，都是内存中的

缺点：断电即消失
优点：速度快

文件存储

缺点：速度慢
优点：可以实现永久保存

数据库本质是一套基于CS架构的客户端和服务器程序，最终的数据存储在服务器端的磁盘中。

为什么要使用数据库

直接使用文件存储带来的问题

速度慢
我们的程序将来可能分布在不同的机器上，单台机器的性能肯定有上限，如果一台机器不能够满足，你可以用多个机器共同完成任务。

分布式：每个服务器提供不同的服务，有时候某个业务流程可能会涉及到多个服务器
- 优点：耦合度降低，易维护
- 缺点：通讯繁琐，容灾性没有集群好
集群：所有服务器提供相同的服务
- 优点：容灾性强，易扩展，可拔插
- 问题：数据分布在不同机器上，通过网络访问

用户权限管理
多个客户端并发访问，数据要保证安全

数据库的分类

关系型数据库：数据可以存在关联关系，数据库会帮我们维护这种关系，通常存储的介质都是磁盘

常见：

mysql：学习的重点，目前最流行的关系型数据库，因为免费开源，性能不错
sqlserver:微软推出，只能在Windows平台运行
Oracle：目前最强大的关系型数据库，主要是在集群和用户管理上，非常适合大型企业
db2：IBM的产品，主要面向企业级用户

非关系型数据库：没有能帮助我们维护数据之间的关系，通常介质都是内存

常见：

MongoDB
redis
memcache

数据库重要概念

数据（Column）：文件中的某个字符串

记录（Row）：文件中的某一行

表（Table）: 某个文件

库（Database）：就是一个文件夹

DBMS：数据库管理系统（数据库软件）

数据库服务器：运行DBMS的计算机

安装数据库

采用压缩包的方式来安装,解压到本地即可

bin：存储所有执行文件

data：存储数据的位置

简单的使用步骤:

bin下有 mysqld.exe，是服务器端程序，mysql.exe是客户端程序

需要先运行mysqld.exe

运行客户端时,如果直接双击运行进入游客模式

正确的运行方式：是在终端里指定，用户名密码等参数

常见的参数：

-h 主机名称如果是本机可以忽略
-P 指定端口默认3306 可以不写
-u 指定用户名
-p 指定密码

添加环境变量

注册系统服务

删除：sc delete mysql (注意是服务名称不是文件名称)

启动服务：net start mysql;

停止服务：net stop mysql;

查找某个进程：tasklist | findstr mysqld

杀死进程：taskkill /f /pid 111111

mysql5.6 管理员密码的设置

知道原始密码

登陆到mysql执行更新语句来修改：update user set password = password('密码') where host='localhost' and user = 'root'
mysqladimin 小工具：mysqladmin -uroot -p123(原始密码) password 321

不知道原始密码

删除权限相关的文件（容易挨打）
跳过授权表
- 手动启动mysql指定参数：mysql --skip-grant-tables
- update user set password = password("111") where host="localhost" and user="root";
- 重启mysql即可

简单的使用

数据必须找个文件存起来，也就是表，表必须存在于库中，也就是文件夹

库的操作

# 切换数据库
use 数据库名  可以不加分号


# 查看所有数据库
show databases;


# 查看数据库详细信息
show create database 库名;


# 创建新的数据库
create database 数据库名;
create database 数据库名 charset utf8;  # 指定编码，不能有-，代表减号
# charset  是 character set 的简写


# 删除数据库
drop database 数据库名;


#修改数据库编码  可以进入到数据库文件夹中修改db.opt  
#第一行是编码 第二行是排序规则 自己搜索一个


#修改数据库名 可以直接修改对应的文件夹名称

命名规范：

不区分大小写
不要使用关键字，例如create，select等...
不能为纯数字
可以下划线，通常字符下划线数字的组合

表的操作

# 创建表
create table 表名(列名称 列的数据类型，列名称2，类型2，...);


# 指定编码
create table 表名(列名称 列的数据类型，列名称2，类型2，...)  charset gbk;


# 查看当前库下所有表
show tables;


# 查看表的创建语句
show create table 表名;


# 查看表的结构
desc 表名;


# 删除表
drop table 表名;


# 清空数据 （重建表）
truncate table 表名;


# 修改表结构

# 添加字段
alter table 表名 add 列名称 数据类型;
# 删除字段
alter table 表名 drop 列名称;
# 修改数据类型
alter table 表名 modify 列名称 新的数据类型;
# 修改列名
alter table 表名 change 旧列名  新列名 新数据类型;
# 修改编码
alter table 表名 charset utf8;


# 修改表的名称
rename 旧表名 to 新表名;

配置文件的使用

配置文件需要放在安装目录的根目录，与bin同一级

文件名称：my.ini

内容和使用：cfg格式，即section + option

[mysqld] ：分区服务器端配置，修改后需要重启服务器

[mysql] ：客户端配置，修改后需要重连

统一编码方式：配置文件如下：

# 客户端   除了mysql之外的
# 客户端的配置修改后，只需要退出重新连接即可

[client]
user = root
password = 111
default-character-set = utf8

# 服务器端配置，需要重启服务器
[mysqld]
character-set-server = utf8

表的分类，数据库引擎

引擎就是一个系统最核心的部分，数据库引擎指的是真正负责存储数据的模块，不同的引擎具备不同的特点，需要根据实际需求来选择最合适的。

补充：SQL全程是结构化查询语句，在关系型数据库中是通用的。

查看数据库支持的引擎：show engines

创建表指定引擎：create table 名称(字段，类型) engine= xxxx

默认的也是最常用的引擎是：innodb

简单的增删改查 CRUD

插入数据：

该方式必须保证插入的数据个数与表格字段一一对应

insert into 表名 values(v1,v2，....）
该方式必须保证插入的数据个数与指定的字段一一对应

insert into 表名(字段名1，字段名2) values(v1,v2)
同时插入多个数据

insert into 表名(字段名1，字段名2) values(v1,v2)，values(v1,v2)

查询：

select * from 表名; # * 表示全部字段，查询所有记录
select 字段 from 表名 where 条件;

更新：

update 表名 set 字段名称=新的值，字段名称2=值2; # 修改所有记录
update 表名 set 字段名称=新的值，字段名称2=值2 where 条件; # 修改满足条件的记录

删除数据：

delete from 表名; # 删除所有记录
delete from表名 where 条件; # 删除满足条件的记录

补充：修改数据库语法
alter database 数据库名称 charset utf8;
select database() 查看当前数据库

创建表的完整写法

create table 表名称 (字段名称字段类型[(长度整数) 约束]) charset utf8;

mysql中的数据类型

整数

tinyint: 1个字节，无符号的8bit，最大为255，有符号的7个bit最大为127
smallint: 2个字节
mediumint: 3个字节
int: 4个字节
bigint: 8个字节

注意：

1.默认情况下整数时有符号的

设置为无符号：unsigned

alter table t2 modify age tinyint unsigned;
create table t2(age tinyint unsigned);

2.mysql5.6默认是非严格模式，如果你的数据超出范围，会自动取最大值，一般不这么用，会丢失数据

查看当前的sql模式： select @@sql_mode;：其中@表示全局变量，@@表示会话级变量（局部）

临时设置sql_mode： set @@sql_mode='xxxx'

建议直接修改配置文件：

[mysqld] sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

指定长度：int(10)指的是如果你的数据不足10位，用0填充（需要添加zerofill这个约束），对于整数和小数而言，都是控制显示填充的最小长度

浮点

两种不精确的小数

float：可以精确到大约5位数

double：比float更精确

如果对精确的要求比较高，需要使用decimal类型

decimal需要指定位数：位数不包括小数点

decimal(65,30) ，总长度最大为65位，小数最大为30位

字符串

`char`:定长字符串

`varchar`:长度可变的字符串

char(10):存储abc,占十个字符长度

* 优点：读取存取速度快于`varchar`
* 缺点：浪费磁盘空间

varchar(10)，存储abc，占3个字符长度外加一个bit来存储字符长度

* 优点：节省空间
* 缺点：速度慢于`char`类型

如果字符长度不大，建议使用char类型，反之选择varchar类型

char最大范围为255

varchar最大范围65535

如果还不够就采用text，最大可以存储$2^{32}$次方。

text

具备编码的大字符串

blob

没有编码的大二进制，可以用来存储多媒体数据，视频、音频等.....一般不会存这种数据，都是建一个文件服务器，数据库只保存文件的地址信息

注意：mysql会自动将字符串后面的空格删掉，所以你的数据在存储前应该先把空格处理掉

枚举和集合

枚举是提前规定一个范围，你的值只能是其中之一，多选一

集合是提前规定一个范围，你的值可以是其中的多个，多选多

集合在插入数据时，多个值之间用逗号隔开，但他们必须包含在同一个字符串内

日期和时间

year

date

time

year date time 都可以用字符串或数字两种方式输入值

datetime

datestamp

datetime和datestamp 只能使用字符串输入

上述所有类型的年份都可以是两位数

0-69 翻译为 2000-2069 70-99 翻译为1790-1999

时间相关的函数和常量

CURRENT_TIME 获取当前时间

now()获取当前时间

约束

什么是约束（constraint）

约束就是一种限制，数据库的约束是对数据的安全性完整性的保证

mysql中的约束

unique：唯一性约束，表示这个字段不能出现重复的值，用于唯一标识一条记录，例如身份证号码，学号等

not null：非空约束，表示这个字段的值不能为空，例如账户名，密码等

default：默认值，用于给某一个字段设置默认值

普通约束测试：

# 完整的建表语句
create table table_name(字段名称 字段类型[(宽度) 约束]) charset utf8;


# 学生表 具备 姓名 性别 学号
create table student(
    # 非空
    name char(20)  not null,
    
    # 默认值
    gender  enum('girl','boy') default 'b',
    
    # 唯一
    id int unique
)

# 测试：
insert into student values(null,null,null);  # 错误原因是name不能为空


insert into student values('jack',null,null); # 可以插入null也是一个特殊的值，并且id的唯一约束，也可以为null


insert into student(name,id) values('jack',null); # 可以插入当没有给gender指定参数时，将使用默认值


alter table student modify id int unique not null;  # 为已经存在的字段添加约束

primary key

主键约束，从约束的角度来看就等同于非空+唯一

主键与普通约束的区别

create table person(
    id char(18) primary key,
    name char(20)
);


insert into person values('1','rose');  # OK

iserrt into person values('1','jack'); 
#  ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'主键冲突;

insert into person values('2','jack'); # OK
insert into person values(null,'tom');
# Column 'id' cannot be null #主键不能为空  

# 从约束角度来看就等同于 非空+唯一
create table person2(id char(18),name char(20));

在innodb存储引擎中，主键用于组织数据（树形结构），也就是说对于innodb引擎来说是必须的，没有不行，如果没有手动指定主键，mysql会自动查找一个具备非空且唯一的字段作为主键，首先明确主键是一种索引，unique也是，索引的作用是加速查询，如果我们在查询语句中没有使用索引字段，mysql将无法为我们的查询加速，意味着如果没有主键，将无法加速查询

总的来说：主键具备约束的作用，还能加快我们的查询速度，所以今后在创建表的时候都应该创建索引字段

应该将什么样的字段设置为主键？如果本来的业务中就存在非空且唯一的字段，那就把它设为主键，如果没有就自己添加一个字段专门作为主键

通常我们会将主键设置为int类型，是为了方便保证其唯一性

create table PC(
    id int primary key,
    logo char(10),
    model char(10),
    price float
);

insert into PC values(1,'IBM','123444',10000);
insert into pc values(2,'dell','23523',5000);
select *from pc;
select *from pc where id = 1;
select *from pc where logo = 'dell';

为主键设置自动增长

当我们创建了主键字段时，插入数据必须保证主键是唯一的不能重复，这就需要我们自己管理主键值，这是很麻烦的，所以mysql有一个自动增长的属性，可以添加在整形字段上，每次插入数据时，都可以自动插入值，并且每次加1不会冲突

create table teacher(
    id int primary key auth_increment,
    name char(10)
);

insert into teacher values(null,'jack'); # 对于自动增长的字段可以给null也会自动生成值

insert into teacher(name) values('jack'); # 也可以跳过这个字段


create table teacher3(
    id char unique auth_increment,
    name char(10)
);

注意：自动增长可以用在具备索引并且是数字类型的字段上，但是通常与主键一起使用！

表之间的关系

foreign key

外键约束，用于指向另一个表的主键字段

# 创建表的时候添加外键

create table teacher(
    id int primary key auto_increment,
    name char(20),
    gender char(10),
    dept_id int,
    foreign key(dept_id) references dept(id)
);

'''
解释：
foreign key(dept_id) references dept(id)
dept_id 表示当前表的外键字段
dept 表示要关联的那个表
dept(id) id表示关联的dept表的id字段


主表与从表
先有主表再有从表
先有dept 再有teacher
'''

foreign key带来的约束作用：

在从表中插入一条记录，关联了一个主表中不存在的id，导致插入失败，必须保证部门id(外键的值)必须是在主表中存在的
插入数据的顺序：先插入主表的数据，再插入从表的数据
从表更新外键时也必须保证外键的值在主表中是存在的
删除主表记录前，要保证从表中没有外键关联被删除的id
更新主表记录的主键时，要保证从表中没有外键关联被删除的id
必须先创建主表
删除表必须先删除从表

强调：foreign key就是用来保证两张表之间的关联关系是正确的

create table class(
    id int primary key auto_increment,
    name char(20)
);


create table student(
    id int primary key auto_increment,
    name char(20),
    gender char(10),
    c_id int,
    foreign key(c_id) references class(id)
);

级联操作（cascade）

当我们需要删除部门（主表）信息时，必须先删除从表中关联的数据，很麻烦，级联操作指的是当操作主表时，自动的操作从表

两种级联操作

级联的删除：当删除主表时，自动删除从表中相关数据
级联更新：当主表的主键更新时，自动更新关联的从表数据

drop table if exists class;

# 如果这表存在才执行删除，可以避免报错

# if not exists 如果不存在才执行

create table class (
    id int primary key auto_increment,
    name char(20),
);

insert into class values(null,'py9');
insert into class values(null,'py10');


# 创建表的时候指定级联操作
drop table if exists student;
create table student(
    id int primary key auto_increment,
    name char(20),
    gender char(10),
    c_id int,
    foreign key(c_id) references class(id)
    on update cascade
    on delete cascade
);


# 级联操作可以单独使用，也可以一起使用，空格隔开即可
insert into student values(null,'jack','man',1),(null,'rose','woman',1),(null,'tom','man',2);

外键的使用

什么时候使用外键：表之间存在关联关系，首先要确定表之间的关系

多对一

一对多（多对一）：

老师和部门的关系

老师的角度看：

一个老师应该对应有一个部门
一个老师可以对应对多个部门? 不行，一个老师只能属于一个部门 (要看具体业务要求)!多个老师可以对应一个部门

部门的角度看：

一个部门可以对应多个老师，一个部门可以对应一个老师，多个部门可以对应一个老师? 不行

如何处理一对多(多对一)?在老师表中存储部门id，即多的一方存储一的一方的id

处理方式

在多的那一方即teacher表中保存相应的部门（一的一方）的编号

# 部门：
create table dept(
    id int primary key auto_increment,
    name char(20),
    job char(50),
    manager char(10)
);


# 老师表：

create table teacher(
    id int primary key auto_increment,
    name char(20),
    gender char(10),
    dept_id int,
    foreign key(t_id) references teacher(id),
);

多对多

如何确定多对多关系

例如：老师表和学生表

老师表角度：一个老师可以对应多个学生，一对多

学生表角度：一个学生可以对应多个老师，一对多

如果双方都是一对多的关系，那么两者是多对多关系

处理方式

建立一个中间表，用于存储关系，至少具备两个字段分别指向老师和学生的主键，两个字段都是外键

create table t_s_r(
    id int primary key auto_increment,
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
);


# 上表中的id是可选的，问题是如何保证没有重复


# 方式一：给两个字段设置联合唯一+非空

# 表已存在
alter table t_s_r add unique key(t_id,s_id);

# 创建表时指定多字段联合唯一
create table t_s_r(t_id int,s_id int,unique key(t_id,s_id));


# 方式二：将中间的关系表，两个id作为联合主键，同时具备了非空且唯一（推荐）
create table t_s_r(t_id int,s_id int,primary key(t_id,s_id));

处理多对多关系

创建两个主表，如学员和老师
创建关系表，包含两个字段，分别设置外键，指向对应的表
将两个字段，作为联合主键

create table student(id int primary key auto_increment,name char(10));
create table teacher(id int primary key auto_increment,name char(10));

create table t_s_r(
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
    primary key(t_id,s_id)
);


insert into teacher values(null,'tom'),(null,'jerry');

insert into student values(null,'jack'),(null,'rose');


insert into t_s_r values(1,1),(2,2),(2,1);


# 已知老师名称为tom，请找出他教过的学生
1. 通过名字拿到老师的id
2.用id在关系表中取出，教过学生的id
3.用id找出学生信息

select id from teacher where name='tom';

select s_id from t_s_r where t_id = 1;

select *from student where id=1;


# 子查询方式，把一条语句作为另一条语句的条件！
select * from student where id = (select s_id from t_s_r where t_id =(select id from teacher where name='tom'));

一对一

如一个客户对应一个学生；站在两边看都是一对一的关系

处理方式

确定先后顺序，将先存在的数据作为主表，后存在的作为从表，是两个表的id保持一一对应

方法一：从表的id既是主键又是外键

方法二：从表的id设置为外键并且保证唯一

# 人员表
create table person(
    id int primary key auto_increment,
    name char(10),
    age int
);
# 详情表 
create table person_info(
    id int primary key,
    height float,
    weight float,
    foreign key(id) references person(id)
);


#再这样的关系中 必须先插入主表即person 拿到一个id 在添加详情表的数据  

#将一条完整数拆分到不同表中,可以提高查询的效率,上述方式称之为垂直分表!

Mysql表查询

单表查询

单表查询语法

select distinct （* or 字段名 or 四则运算） from 表名

where 条件
group by 分组
having 筛选
order by 排序
limit 限制

以上是书写顺序，必须按照这个顺序来书写sql语句，但是书写顺序不是执行顺序

关键字执行的优先级

from：找到表
where：拿着where指定的约束条件，去文件/表中取一条条的记录
group by：将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
select：执行select
distinct：去重
having：将分组的结果进行having过滤
order by：将结果按条件排序：order by
limit：限制结果的显示条数

# 伪代码

  
# 第一步找到对应的文件
def from(name):
    open (file)
    pass

# 第二步 读取并筛选数据

def where(条件):
#读取每一行数据 判断是否满足条件
  for line in file:
        if XXXxxx

def group():
  #将数据按照某个字段进行分组
  pass

def having():
  #对分组后的数据进行筛选
  pass

def distinct():
  #对数据进行去重处理
    pass

def order():
  #对数据进行排序
    pass

def limit()
  #选取一部分数据
    pass

def select()
  from()
  where()
  group()
  having()
  distinct()
  order()
  limit()
    return data;

简单查询

上述的关键字大多数是可选的：

select distinct （* or 字段名 or 四则运算）from 表名

distinct 是可选的，用于去除重复记录，只有取出的所有列数据都重复时才去除

当字段名太长获取不容易理解时，可用as来取名字

# 准备数据

create table stu(id int primary key auto_increment,
                 name char(10),
                 math float,
                 english float);

insert into stu values(null,"赵云",90,30);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"大乔",10,70);
insert into stu values(null,"李清照",100,100);
insert into stu values(null,"铁拐李",20,55);
insert into stu values(null,"小李子",20,55);


# 查看所有数据
select * from stu;

# 查看英语成绩
select name,english from stu;

# 查看所有人的数学成绩，并且去除姓名相同的数据
select distinct name,math from stu;

# 统计每个人的总分
select name,english+math as total from stu;

# 为每个人的英语加十分再显示
select name,english + 10 as english from stu;


# 调整显示的格式：需要在字段的数据前面加上字段名
name:赵云  english：90  math:30
        
# 字符串拼接函数 concat()

select concat('姓名:',name) name,concat('英语：',english) english,concat('数学：',math) math from stu;


# 需求：如果总分小于150，在名字后面加上shit，大于等于加上nice

select 
(case
when english + math >= 150 then
 concat('name','nice')
 when english + math < 150 then 
 concat('name','shit')
end),english,math from stu;


select if(english+math > 150,concat(name,'nice'),concat(name,'shit')) from stu;

where关键字

select * from table_name
where


where 后面可以是

1. 比较运算符  >  <  >=  <=  =  !=


2. 成员运算符  in    not in  后面是一个集合set


3. 逻辑运算符   and  or  not 

    注意： not 要放在表达式的前面   and 和 or 放在两个表达式的中间
    
    
4. 模糊查询  like

    % 表示任意个数的任意字符串
    _ 表示一个任意字符
    
# 查询  姓小的 数学小于80分并且英语大于20分的人的数学成绩

select name,math from stu where math < 80 and english > 20 and name like '小%';

distinct去除重复记录

select distinct * from stu;

# 注意：仅当查询结果中所有字段全都相同时，才算重复的记录

指定字段

星号表示所有字段
手动指定需要查询的字段
还可以是四则运算
聚合函数

# 查询：英语及格的人的平均分

select name,(english+math)/2 as 平均分 from stu where english >= 60;

取别名

select name,math+english as 总分 from stu where name = '赵云';

as 可以省略

统计函数

也称之为聚合函数，将一堆数据经过计算得出一个结果。

求和：sum（字段名）
平均数：avg（字段名）
最大值：max（字段名）
最小值：min（字段名）
个数：count（字段名）

注：字段名可以用*来代替，如果字段为空会被忽略

可以用在字段的位置或是分组的后面

例：查询所有人的平均工资

select avg(salary) from emp;

错误案例：查询工资最高的人的姓名

select name,max(salary) from emp;

默认显示第一个name，因为name有多行，而max（salary）有很多行，两列的行数不匹配

select name from emp where salary = max(salary);

报错，where后面不能用聚合函数

group by

group是分组的意思，即将一个整体按照某个特征或依据来分为不同的部分，为什么要分组，分组是为了统计，例如统计男性有几个，女性有几个

# 语法
select xxx from table_name group by 字段名称;

# 需求：统计每个性别分别有几人
select sex,count(*) form emp group by sex;

# 需求：查询每个性别有几个，并且显示姓名
select name,sex,count(*) from emp group by sex;

# mysql 5.6下，查询结果是name仅显示该分组下的第一个
# 5.7 以上直接报错，5.6可以手动开启这个功能

# 我们可以用group_concat将分组之外的字段做一个拼接，但是这是没有意义
# 如果要查询某个性别下的所有信息，直接用where即可

# 结论：只有出现在了group by后面的字段才能出现在select的后面

having

用于过滤，但是与where不同的是，having使用在分组之后

案例：

# 求出平均工资大于500的部门信息
select dept,avg(salary) from emp group by dept having avg(salary) > 5000;

# 查询，部门人数少于3的，部门名称，人员名称，人员个数
select dept,group_concat(name),count(*) from emp group by dept having count(*) < 3;

order

根据某个字段排序

# 语法：
select * from table_name order by 字段名称;
# 默认是升序

# 改为降序
select * from table_name order by 字段名称 desc;

# 多个字段，第一个相同在按照第二个   asc表示升序
select * from table_name order by 字段名称1 desc，字段名称2 asc;


# 案例：
select * from emp order by salary desc,id desc;

limit

用于限制要显示的记录数量

# 语法1：
select * from table_name limit 个数;

# 语法2：
select * from table_name limit 起始位置，个数;


# 查询前三条
select * from emp limit 3;

# 从第三条开始，查询3条  3-5
select * from emp limit 2,3


# 注意：起始位置从0开始

# 经典的使用场景：分页显示
# 1.每一页显示的条数：a = 3
# 2.明确当前页数：b = 2
# 3.计算起始位置：c = （b-1)*3



select * from emp limit 0,3；
select * from emp limit 3,3;
select * from emp limit 6,3;

# django 提供了现成的分页组件，但是它是先查询所有数据，丢到列表中，再取出数据，这样如果数据量太大可能会有问题

多表查询

笛卡尔积查询

select * from table1,table2,....

# 笛卡尔积查询的结果会出现大量的错误数据，即数据关联错误！
# 添加过滤条件，从表外键值等于主表的主键值

# 并且会产生重复的字段信息，例如员工里的部门编号和部门表的id字段
# 在select后指定需要查询的字段名称

# 案例：
select dept.name 部门,dept.id 部门编号,emp.name 姓名,emp.id 员工编号,sex from emp,dept where dept.id = dept_id;

内连接查询

本质上就是笛卡尔积查询

# 语法：
select * from table1 inner join table2;

# 案例：
select * form emp inner join dept where dept_id = dept.id;

inner 可以省略
select * from emp join dept where dept_id = dept.id;

左外连接查询

左边的表无论是否能够匹配都要完整显示

右边的表仅展示匹配上的记录

# 需求：要查询所有员工以及其所属的部门信息
select * from emp left join dept on dept_id = dept.id;

# 注意：在左外连接查询中不能使用where关键字，必须使用on专门来做表的相对应关系

右外连接查询

右边的表无论是否能够匹配都要完整显示

左边的表仅展示匹配上的记录

# 需求：要查询所有部门以及对应的员工信息
select * from emp right join dept on dept_id = dept.id;

全外连接查询

无论是否匹配成功，两边表的数据都要显示

# 需求：查询所有员工与所有部门对应的关系
select * from emp full join dept on dept_id = dept.id;


## 注意：mysql不支持全外连接

# 我们可以将左外连接查询的结果和右外连接查询的结果做一个合并
select * from emp left join dept on dept_id = dept.id
union
select * from emp right join dept on dept_id = dept.id;


# union的用法：
select * from emp
union
select * from emp;

# union将自动去除重复的记录
# union all 不会去除重复
## 注意：union必须保证两个查询结果，列数相同，一般用在多个结果的结构完全一致时

总结：外连接查询查到的是没有对应关系的记录，但是这样的数据原本就是有问题的，所以最常用的是内连接查询

内连接表示只显示匹配成功的记录

外连接表示没有匹配成功的也要显示

多表查询案例：

create table stu(
    id int primary key auto_increment,
    name char(10)
);

create table tea(
    id int primary key auto_increment,
    name char(10)
);

create table tsr(
    id int primary key auto_increment,
    t_id int,
    s_id int,
    foreign key(t_id) references tea(id),
    foreign key(s_id) references stu(id)
);

insert into stu values(null,'张三'),(null,'李四');
insert into tea values(null,'tom'),(null,'jerry');
insert into tsr values(null,1,1),(null,1,2),(null,2,2);


# 需求：tom老师教过那些人
select tea.name,stu.name from stu join tea join tsr on stu.id = s_id and tea.id = t_id where tea.name = 'tom';

# 子查询实现
select * from stu where id in (select s_id from tsr where t_id =(select t_id from tea where name = 'tom'));

子查询

将一个查询语句的结果作为另一个查询语句的条件或是数据来源

当我们一次查不到想要数据时就需要使用子查询

in关键字查询

当内层查询（括号内的）结果会有多个结果时，不能使用=，必须使用in，另外子查询必须只能包含一列数据

需求：指定一个部门名称，获取该部门下的所有员工信息

# 1.查询出平均年龄大于25的部门编号

select dept_id from emp group by dept_id having avg(age) > 25;

# 2.再根据编号查询部门的名称

select name from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);

# 子查询的思路：
# 1.要分析查到最终的数据到底有哪些步骤
# 2.根据步骤写出对应的sql语句
# 3.把上一个步骤的sql语句丢到下一个sql语句中作为条件

exists关键字子查询

当内层查询有结果时，外层才会执行

# 案例：
select * from dept where exists (select * from dept where id = 1);

# 由于内层查询产生了结果，所以执行了外层查询dept的所有数据

高级部分

视图

什么是视图

本质上是一个虚拟的表，即看的见但是不实际存在

使用场景

为什么要虚拟表

场景1：我们希望某些查询语句只能查看到某个表中的一部分记录

场景2：简化sql语句的编写

使用方法

# 语法：
create [or replace] view view_name as 查询语句;
# or replace 如果视图已经存在了，就替换里面的查询语句

# 修改视图
alter view view_name as 新的语句;

# 删除视图
drop view view_name;

# 查看视图
desc view_name;
show create view view_name;


# 限制可以查看的记录
create table salarys(id int,name char(10),money float);

insert into salarys values(1,'张三丰',50000),(2,'张无忌',40000);

# 创建视图，限制只能查看张无忌的工资
create view zwj_view as select * from salarys where name = '张无忌';



# 简化sql编写
create table student(
    s_id int(3),
    name varchar(20),
    math float,
    chinese float
);

insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',80,90);

create table stu_info(
    s_id int(3),
    class varchar(50),
    addr varchar(100)
);

insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');

# 查询班级和学员的对应关系做成一个视图，方便后续的查询
create view class_info as select student.s_id,name,class from student join stu_info on student.s_id = stu_info.s_id;


select * from class_info;

注意：修改视图也会引起原表的变化，我们不要这么做，视图仅用于查询

触发器

触发器是一段与某个表相关的sql语句，会在某个时间点，满足某个条件后自动触发执行

两个关键：

时间点：事件发生前before和事件发生后after
事件：update 、delete 、insert

触发器自动的包含两个对象

old：update，delete中可用
new：update，insert中可用

用来干什么：当表的数据被修改时，自动记录一些数据，执行一些sql语句

# 语法
create trigger t_name t_time t_event on table_name for each row
begin
# sql语句。。。。;
end

# 案例

# 准备数据
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
);

#需求： 当插入cmd表 的时候 如果执行状态为失败的 那么将信息插入到errlog中

# 将结束符设置为 |
delimiter |
create trigger cmd_insert after insert on cmd for each row
begin
if new.success = 'no' then
    insert into errlog values(null,new.cmd,new.sub_time);
end if;
end |
# 还原之前的结束符
delimiter ;


# 创建一个触发器叫cmd_insert
# 触发器会在插入数据到cmd表后执行
# 当插入的记录的success为no时，自动插入记录到errlog中


# 删除触发器
drop trigger cmd_insert;

# 查看所有触发器
show triggers；

# 查看某个触发器的语句
show create trigger t_name;

事务`*****`

什么是事务：事务就是一系列sql语句的组合，是一个整体

原子性：指的是这个事务中的sql语句是一个整体，不能拆分，要么都执行，要么全都失败
一致性：事务执行结束后，表的关联关系一定是正确的，不会发送数据错乱
隔离性：事务之间相互隔离，数据不会相互影响，即使操作了同一个表，本质就是加锁，根据锁的粒度不同分为几个隔离级别
持久性：事务执行成功后数据将永久保存，无法恢复

事务的应用场景：

转账操作

把转出账户的钱扣掉
在给转入账户的余额做增加操作

update money set money = money - 100 where name = "李寻欢";

update money set money = money + 100 where name = "步惊云";

注意：在官方提供的cmd的mysql客户端下，事务是默认就开启，会将一条sql语句作为一个事务自动提交

# 语法

# 开启事务
start transaction
# sql 语句。。。
rollback # 回滚操作，即撤销没有提交前的所有操作
# sql 语句。。。
commit  # 提交事务，一旦提交就持久化

CREATE TABLE `account` (
  `name` char(10),
  `money` float  
);

start transaction;
update account set money = money -100 where name = 'jack';
update account set money = money +100 where name = 'rose';
commit;

# 何时应该回滚，当一个事务执行所有过程中出现了异常时
# 何时提交，当事务中所有语句都执行成功时

# 保存点可以在rollback指定回滚到某一个savepoint，也就是回滚一部分

start transaction;
update account set money = money - 100 where name = 'jack';
savepoint a;
update account set money = money - 100 where name = 'jack';
savepoint b;
update account set money = money - 100 where name = 'jack';
savepoint c;

select * from account;

# 回滚至某个保存点
rollback to 保存点名称

事务的用户隔离级别

数据库使用者可以控制数据库工作在那个级别下，就可以防止不同的隔离性问题

read uncommitted：不做任何隔离，可能脏读，幻读
read committed：可以防止脏读，不能防止不可重复读和幻读
repeatable read ：可以防止脏读和不可重复读，不能防止幻读
serializable:数据库串行，所有问题都没有，就是性能低

修改隔离级别

# 查询当前级别
select @@tx_isolation;

# 修改全局的
set global transaction isolation level read committed;

# 或者
set @@tx_isolation = 'read committed'

# 修改局部
set session transaction isolation level read committed;

@@系统内置变量
@表示用户自定义变量

存储过程

什么是存储过程：任意sql语句的组合，被放到某一个存储过程中，类似于一个函数

用来干什么：其中可以包含任意的sql语句，逻辑处理，事务处理，所有的我们学过的sql语句都可以放到里面

三种数据处理方式：

应用程序：只关注业务逻辑，所有与数据相关的逻辑封装到mysql中

优点：应用程序要处理的事情变少了，可以减少网络传输

缺点：增加了人力成本，沟通成本，降低整体开发效率

应用程序既要处理业务逻辑，还要自己编写sql语句

优点：降低了沟通成本，人力成本

缺点：网络传输增加，sql语句的编写非常繁琐，易出错

通过ORM框架，对象关系映射，自动生成sql语句并执行

优点：不需要再编写sql语句，明显提升开发速度

缺点：不够灵活，应用程序开发者和数据库完全隔离，可能导致仅关注上层开发，而不清楚底层原理

使用存储过程

# 语法
create procedure p_name(p_type p_name p_data_type)
begin
sql....
end


# p_type:参数的类型
# 1.in输入  2.out输出  3.inout即可输入也可输出

# p_name:参数的名字
# p_data_type:参数的数据类型，如int，float

# 注意：out参数必须是一个变量，不能是值

# 案例：
delimiter |
create procedure add1(in a float,in b float,out c float)
begin
set c = a + b
end |
delimiter ;

# 调用
set @res = 0;
call add1(100,10,@res);

select @res;

# 查看
show create procedure 名称;

# 查看全部 库名下的所有过程
select name from mysql.proc where db = 'day0716' and type='PROCEDURE';


delimiter |
create procedure transfer_money(in aid int,in bid int,in m int,out res int)
begin
    declare exit handler for sqlexception
    begin
    
    # 异常代码处理
    set res = 99;
    rollback;
    end;
    
    start transaction;
    undate account set money = money - m where id = aid;
    undate account set money = moneys + m where id = bid;
    commit;
    set res = 1;
end |
delimiter ;

函数

自定义函数

create function func_name(参数 类型)
函数体
returns 返回值的类型
return 返回值

delimiter |
create function add2(a int,b int)
returns int
return a + b |
delimiter ;

# 查看创建语句
show create function func_name;

# 查看所有函数的状态
show function status;

# 查看某个库下所有的函数
select name from mysql.proc where db = '库名' and type = 'FUNCTION';

# 删除
drop function func_name;

备份与恢复

# 备份
mysqldump -u用户名 -p密码  数据库名  表名1 表名2 ... > 文件路径
# 注意 第一个表示数据库，后面全是表名，中间没有符号

# 备份多个数据库
mysqldump -uroot -p111 --databases day41 day42 > 文件路径
# 指定 --databases 后导出的文件包含创建库的语句，上面的方式没有创建库语句

# 备份所有数据
mysqldump -uroot -p111 --all-databases > all.sql

# 自动备份所有数据
linux crontab 指令可以定时执行某一个指令


# 恢复数据
# 没有登录mysql
mysql < 文件的路径

# 已经登录mysql
source 文件路径


# 注意：如果导出的sql中没有包含选择数据库的语句，需要手动加上

流程控制

delimiter |
create procedure jishu()
begin
declare i int default 0;
aloop: loop
set i = i + 1;
if i >= 101 then leave aloop; end if;
if i % 2 = 0 then iterate aloop; end if;
select i;
end loop aloop;
end |
delimiter ;

用户管理

主要为了控制权限，让不同开发者仅能操作属于自己的业务范围内的数据

创建mysql账户

账户中涉及的三个数据：账户名，密码，IP地址

IP地址是用于限制某个账户只能在那些机器上登陆

create user 用户名@主机地址 identified by '密码';

# 注意：操作用户，只能由root账户来进行

# 删除，将同时删除所有权限
drop user 用户名@主机地址;

权限管理

涉及到的表

user ：与用户相关的信息
db  : 用户的数据库权限信息
tables_priv : 用户的表权限
columns_priv : 用户的字段权限

# 语法
grant all on *.* to 用户名@主机地址 identified by '密码';

# 如果用户不存在则自动创建新用户，推荐使用
grant all on *.* to rose@localhost identified by '123';

grant all on day42.* to rose1@localhost identified by '123';

grant all on day42.table1 to rose2@localhost identified by '123';


grant select(name),update(name) on day42.table1 to rose3@localhost identified by '123';


# all表示的是对所有字段的增删改查，*.*代表所有库的所有表

# 收回权限
revoke all on *.* from 用户名@主机地址;


revoke all on day42.table1 from rose@localhost;

# 刷新权限
flush privileges;


# 将拥有的权限授予其他的用户
grant all on *.* to root1@localhost identified by '123' with grant option;

# 授予某个用户，可以在任意主机上登陆
grant all on *.* to jack@'%' identified by '123';
grant all on *.* to jack1@localhost identified by '123';

可视化客户端

Navicat
mysqlbench

pymysql

pymysql是一个第三方库，帮我们封装了建立连接，用户认证，sql的执行以及结果的获取

import pymysql

'''
1.连接服务器
2.用户认证
3.发送指令
4.提取结果
'''

# 1.连接服务器，获取连接对象（本质上就是封装好的socket）
conn = pymysql.connect(
    host = '127.0.0.1',
    port = '3306',
    user = 'root',
    password = '123',
    database = 'day0716'
)

# 2.通过连接拿到游标对象
# 默认的游标返回的是元组类型，不方便使用，需要更换字典类型的游标
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 3.执行sql
sql = 'select * from table1'
res = cursor.execute(sql)
# 查询语句将返回查询的结果数量

# 4.提取结果
print(cursor.fetchall())
# fetchall、fetchmany、fetchone


# 5.关闭连接
cursor.close()
conn.close()


# 移动游标，参数1为移动的位数，第二个mode指定是相对或是绝对
# cursor.scroll(1,mode='absolute')
# 从开始移动一位

sql注入攻击

指的是一些程序员在输入域数据时，按照sql语法规范，提交了用于攻击性目的的数据，怎样才能避免这个问题？

在服务器端执行sql前做sql语句的验证

而pymysql已经封装好了，所以我们把参数传入就可以了

修改数据

import pymsql

conn = pymysql.connect(
    host = "127.0.0.1",  #如果是本机 可以忽略
    port = 3306,    # 如果没改过 可以忽略
    user = "root", #必填
    password = "111", #必填
    database = "day42", #必填,
)

c = conn.cursor(pymysql.cursors.DictCursor)

name = input('please enter your name>>>').strip()
pwd = input('please enter your password>>>').strip()


sql = 'select * from user where name = %s'

if c.execute(sql,(name,)):
    print('用户已存在！')
else:
    sql2 = 'insert into user values(%s,%s)'
    if c.execute(sql2,(name,pwd)):
        print('注册成功!')
        conn.commit()  # 调用连接对象提交函数
     else:
        print('注册失败')
        
c.close()
conn.close()

注意：pymysql自动开启了事务，所以我们自己在合适的位置提交

调用存储过程

import pymysql
conn = pymysql.connect(
    host = "127.0.0.1",  #如果是本机 可以忽略
    port = 3306,    # 如果没改过 可以忽略
    user = "root", #必填
    password = "111", #必填
    database = "day42", #必填,
    autocommit=True  # 开启自动提交  不常用....
)
c = conn.cursor(pymysql.cursors.DictCursor)

c.callproc('add1',(1,2,res)) # @_add1_0 @_add1_1 @_add_2

c.execute('select @_add1_2')
print(c.fetchall())

# 调用存储过程时,传入参数,会自动定义成变量,
# 命名方式 @_过程的名称_参数的索引 从0开始

索引

什么是索引

可以理解为：搜索引导，索引是一个特殊的数据结构，其存储的是数据的关键信息与详细信息的位置对应关系，例如书本的目录

为什么需要索引

加速查询，当数量非常大的时候，查询某一个数据是非常慢的

索引的影响

不是说有了索引就能加速，得看查询语句有没有正确使用索引
索引也需要占用额外的数据空间
添加索引后将导致，增减删除修改变慢（写入）

什么样的数据应该添加索引：查询操作较多，写入较少并且数据量很大时，查询与写入操作的占比10:1或者查询更多时

本质上索引原理是尽可能的减小搜索范围

磁盘IO

平均查找一个数据需要花费至少9ms，这段时间CPU就会切换到其他程序，我们要加速查询，必须较少的IO操作次数

索引数据结构

b+树

在b+树中叶子节点才是存储真实数据的，叶子数量越多，树的层级越高，导致IO次数增加，要避免这个问题，在叶子节点中尽可能的存储更多的数据，应该将数据量小的字段作为索引。

最左匹配原则

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候(多字段联合索引)，b+树会按照从左到右的顺序来建立搜索树，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。