目录
数据库基本概念
数据库是什么
存储数据的仓库
- 列表,字典...等等,都是内存中的
缺点:断电即消失
优点:速度快
- 文件存储
缺点:速度慢
优点:可以实现永久保存
数据库本质是一套基于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 指定密码
添加环境变量
注册系统服务
注册:mysqld --install
删除: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最大为127smallint
: 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的数据了, 这个是非常重要的性质,即索引的最左匹配特性。
聚集索引
聚集索引中包含了所有字段的值,如果指定了主键,主键就是聚集索引,如果没有则找一个非空的字段作为聚集索引,如果也找不到,就自动生成一个字段作为聚集索引,聚集索引中存储了所有的数据
辅助索引
除了聚集索引以外的都叫做辅助索引
辅助索引中只包含当前的索引字段和主键的值
覆盖查询
指的是在当前索引结构中就能找到所有需要的数据,如果使用的是聚集索引来查询那么一定是覆盖查询,速度是最快的
回表查询
指的是在当前索引中找 不到所需的数据,需要通过id去聚集索引中查询,速度慢于聚集索引
结论:
- 使用占用空间最小的字段来作为索引
- 不要在一行中存储太多的数据,例如小说,视频,如果字段太多,可以分表
- 尽量使用覆盖查询
- 如果字段区分度低(重复度高),建立索引是没有意义的,反过来说应该将区分度高的字段作为索引
- 模糊匹配中,百分号不要写在前面
- 不要在等号的左边做运算
- 例如:select count() from usr where id 3 = 6; 也会遍历所有记录
- and语句中会自动找一个具备索引的字段优先执行,所以我们应该在and语句中至少包含一个具备索引的字段
- or语句要避免使用,如果要使用则保证所有字段都有索引才能加速
- 联合索引中,顺序应该将区分度最高的放到左边,最低的放到右边,查询语句中必须保证最左边的索引出现在语句中
总结:不是添加了索引就能提速,需要考虑索引添加的是否合理,sql语句是否使用到了索引 ,另外如果要查询的数据量非常大 索引无法加速。
语法
创建索引的语法:
create index 索引的名字 on 表名称(字段名)
联合索引:
create index 索引的名字 on 表名称(字段名,字段名2)
create index union_index on user(id,email,name)
删除索引:
drop index 索引名称 on 表名称