mysql学习笔记
数据库基础
数据存储演变过程
1.with open()
问题：1.文件存储位置没有固定
2.数据存储格式千差万别 1—1-1 or 1~1~1
2.软件开发目录规范
限制了存储数据的具体位置
bin conf core db lib readme
若是单机则每个人的记录只会在自己的计算机下保存
并且单机游戏账号不能在别的电脑登录
3.如何将单机变为联网？
单机： 程序----自己电脑的数据库
联网： 计算机1 基于网络发送保存
计算机2 --------------------》数据库服务端
计算机3 同步
数据库1
同步
数据库2
-----数据库集群-----
单机--》联网
将数据库保存部分全部统一起来 所有人操作数据从一个地方得到数据
数据库本质
'''
每个人都可以开发一款数据库软件 因为其本质就是一个基于网络通信的程序 ’‘’
常见数据库
分类：
关系型数据库(彼此有关 互相约束 表格存储)：
MySQL oracle db2 access sql server
每个字段 还能限制存储数据的格式
非关系型数据库(存储数据通常都是以k，v键值对的形式)：
redis mongodb memcache
关系型是存数据的 非关系型常用来做缓存
MySQL
'''一款基于网络通信的应用程序'''
服务端：
基于socket
客户端：
基于socket
客户端：可以是MySQL自己编写的客户端
也可以是编程语言来充当客户端进行操作
针对不同语言，服务端如何兼容：
采用统一的语言 SQL语句
重要概念
库: 文件夹
表: 文件夹里的文件
记录: 文件内一行行的数据
表头: 表格的第一行字段
字段: 以段段文字
默认条例
不会使用最新版本的软件
条件
为了方便学习 将服务端客户端都放到本地来使用
以管理员权限运行cmd
常见软件的端口号
MySQL 3306
django 8000
tomcat 8080
mysql启动
MySQL第一次以管理员进入 没有密码
客户端链接服务端命令
mysql -h 127.0.0.1 -P 3306 -uroot -p
mysql -uroot -p
SQL语句初识
1.以;为结尾
2.show databases; 查看所有库名(文件夹)*
3.链接服务器命令简写 mysql -uroot -p
4.若不想执行输入命令 \c
5.如何退出mysql quit or exit
6.只输入mysql 也能进入mysql客户端 但不是管理员身份 而是游客模式
环境变量配置及系统服务制作
如何查看当前具体进程: tasklist
tasklist |findstr mysqld
如何杀死进程: 只有在管理员cmd窗口下才能成功
taskkill \F \PID PID号
将bin添加到环境变量
进一步优化: 将mysql服务端制作为系统服务端 避免了每次启动mysqld进行命令行
将mysql制作为系统服务 mysql --install
移除 mysqld --remove
默认可以开机自启动
密码设置
直接在终端下
mysqladmin -uroot -p 原密码 password 新密码
忘记密码
1.mysqld --skip-grant-tables 跳过认证表启动
2.以无密码模式登录 mysql
3.修改当前用户密码 updata mysql.user set password=password(xxx) where
user='root' and host ='localhost';
4.立刻将修改数据刷入硬盘 flush privileges;
5.关闭当前服务端 以正常形式启动服务端
统一编码
mysql默认的my-default.ini
配置文件 程序启动先加载配置文件中的内容
统一编码的配置
[mysqld]
character-set-server=utf-8
collation-server=utf8_general_ci
[client]
default-character-set=utf-8
[mysql]
default-character-set=utf-8
基本sql语句
'''
大部分程序的业务逻辑其实都是增删改查 MySQL 是帮助远程操作文件的软件
'''
1.针对库(文件夹)的增删改查
(1)增:create database xx1;
(2)查:show create database xx1;
(3)改:alter database xx1 charset='utf8';
(4)删:drop database xx1;
2.针对表(文件)的增删改查
在操作表的时候 需要指定库
‘’‘查看当前所在库名字:select database();’‘’
'''切换库:use 库名;'''
(1)增:create table xx1(id int,name char(4)); 两个表头 name4个字段
(2)查: show tables; show create table xx1; describe xx1(简写:desc xx1);
(3)改: alter table xx1 modify name char(x);
(4)删: drop table xx1;
'''给任意目录添加表 create table xx.xx1(id int(4));
3.针对数据(数据)的增删改查
‘’‘一定要先有库 再有表 最后 才有数据
(1)增:insert into xx1 values(1,'json');
insert into xx1 values(1,''json'),(xxx),(xxxx);
(2)查:select * from xx1(改命令数据量太大时不建议使用);
select id,name from xx1;
(3)改:update xx1 set name='DSB' where id>1;
(4)删:delete from db where id>1;
(5)清空:delete from db;
存储引擎
'''
文件格式有很多种，针对不同的文件格式有不同的存储方式和处理机制：txt,pdf,word...不同的数据有不同的处理机制
'''
存储引擎就是不同的处理机制；
MySQL主要的存储引擎：
1.innodb:5.5后默认的存储引擎 存储数据更加安全
2.myisam:5.5前默认的存储引擎
3.memory:内存引擎，数据全部存放在内存中，快，但断电丢失
4.blackhole:无论存什么都立刻消失(黑洞)。。。奇葩啊
查看所有的数据库存储引擎:show engines; 安全是第一位;
create table xx1(id int(4)) engine=innodb;
不同引擎下创建表格式不同;
建表注意事项
创建表的完整语法: create table xx1(
字段名1 类型(宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件
)
1.字段名不可重复
2.宽度和约束条件 可选 字段名和字段类型 必选
3.约束条件可以有多个
4.最后一行不能有逗号
5.宽度默认为1 最高限度 5.6默认不是严格模式 会默认留下一个字符 5.7以上会默认严格模式 会直接报错
能尽量少的让数据库干活 就少的让数据库干活
约束条件: NULL not NULL
create table t8 values(id int(1),name char not null);
name字段不能为空
宽度和约束条件的关系: 宽度是用来限制数据的存储 约束条件是宽度基础上增加额外的约束
基本数据类型
1.整形
(1)TINYINT SAMLLINT MEDUIMINT INT BIGINT
作用:范围和所占字节不同 超出不会报错 会存最大值 默认带符号
create table xx1 values(id TINYINT(2) unsigned) 无符号小整形表
约束条件 unsigned
针对整形括号内的宽度(特例): 指显示长度 而不是位数 没有超过x位那么用0填充到x位
超过八位 有几位存几位
不是最多位数了，反而是最小
约束条件 zerofill
总结 针对整形字段 无需加括号
严格模式：
show variables like "%mode" 模糊匹配 搜索带有mode的
关键字：like 像 %匹配任意多个字符 _匹配单个字符
修改严格模式: set session 只在当前窗口有效 set global 全局有效
set global sql_mode = 'STRICT_TRANS_TABLES';
重启服务端
2.浮点型
FLOAT DOUBLE DECIMAL
存小数型
FLOAT:(255,30) 共255位 小数30位
DOUBLE:(255,30)
DECIMAL:(65,30) 共65位 小数30位
create table xx1(sarl float(255,30))
区别 精确度不一样
float 6位
double 多一点
decimal 非常精确
3.字符类型
char varchar
区别: char 定长 不够空格补全 超过消失
varchar 变长 不够四个字符 有几个存几个
varchar 节省空间
select char_length(name) from xx1
取的时候 会自动去掉空格
优缺点:
char: 浪费空间 不用数据库进行处理
varchar: 节省空间 数据库需要额外处理 取得时候多出一个报头
4.时间类型
date类型:
年-月-日
datatime类型
年-月-日 时:分:秒
time类型
时:分:秒
year:
2020
5.枚举与集合类型
枚举(enum):
多选一
多个数据里选择一个
create table sex(sex enum('man','girl','others'))
insert into sex values('man')
若 插入的选项不存在 则会传入空
集合(set):
多选多
create table likes(lover set('man','girl','others'))
insert into likes values('man,girl')
若无则空
约束条件
create table xx1(id int(4),loves set('x','xx') default 'xx')
1.default 默认值 插入时 insert into xx2(id) values(1111) 通过指定插入字段 不填写具有默认值的
2.unique 唯一
单列唯一:
一个字段中唯一
create table xx1(id int(4) unique)
联合唯一:
ip和端口 单个都可以重复 但是 不能同时重复
,unique(ip,port)
primary key主键
1.单从约束效果
primary key 等价于 not null + unique
非空 + 唯一
2.除了约束条件外 还是Innodb 存储引擎组织数据的依据
类似书的目录 能够帮忙提供查询效率
3.一张表有且只有一个主键 如果没有设置主键 从上向下搜索 直到遇到一个非空且唯一的字段 自动设为主键
若无非空且唯一字段 Innodb会提供一个隐藏的字段作为主键 隐藏意味着无法使用
4.一张表中都应该有一个主键字段 并且通常将id\uid\sid字段设为主键
5.联合主键
多个字段联合起来作为一个主键 但是本质还是一个主键
primary key(ip,port)
auto_increament 自增 主要为主键服务 不能给普通字段加
当编号太多 人为维护麻烦
create table txx(id int(4) primary key auto_increment)
删除数据后 自增的计数器不会停止
truncate 删除并清空计数器
表与表间的关系
1.定义一张表 其中有很多字段
缺点: 组织结构不清晰
浪费硬盘空间
数据扩展性差(不能忽视的)
2.改进
拆分
部门表 --- 员工信息表
联系断开了
员工表 外键
主键id 名字 性别 部门表的id --
部门表 | 联系
主键id 部门名 部门描述 --
外键:用来建立表与表间关系的 foregin key
3.表关系
最多四种关系
1对多
多对多
1对1
没有关系
确定表关系: 换位思考
先员工表考虑------一个员工能否对应多个部门(不可以)
部门表-----部门表一个不能能否对应多个员工(可以)
单向一对多
结论 员工表---------部门表
sql语句建立关系
create table emp(id int(4) primary key auto_increment,
name char(4),
dep_id int
,foreign key(dep_id) references dep(id)
)
dep_id 为外键 且和 dep(id) 字段有关系
1.修改学生的id字段 可改
2.修改老师的id字段 已被引用的不可改 也不可删除 未被引用的可改
除非 1.先删除学生处的数据 再改老师
级联更新 级联删除
foreign key(dep_id) references dep(id)
on update cascade # 同步更新
on delete cascade # 同步删除
多对多
book ------ 作者
针对多对多字段表关系 不能直接在两张间建立关系
需要一个一对多的中间媒介表
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) reference author(id)
on update cascade
on delete cascade,
foreign key(book_id) reference book(id)
on update cascade
on delete cascade
);
一对一
没有多对一的说法 都称为 一对多
对于一个表 字段很多 每次又不需要全部的字段
可以将用户表 分为 用户表 和 用户详情表
一个用户 对应 一个详情
一个详情 对应 一个用户
外键字段 放在哪一方都可以 但是推荐 放在查询频率高的一方
外键字段要加个 unique
单向一对多: 一对多
双向一对多: 多对多
没有一对多: 一对一
单条数据里面 好像都存的是 一个数据 而不是多个。。。
foreign key(open key) references xx1(id)
on update cascade
on delete cascade
修改表
mysql 对大小写不敏感 。。。。。。。。
1.修改表名
alter table 表名 rename 新表名;
2.增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件 ;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;
3.删除字段
alter table 表名 drop 字段名；
4.修改表名
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
alter table 表名 change 旧字段名 新字段名(宽度) 约束条件;
复制表
sql 语句的结果 也是一个虚拟表
create table 新表名 select * from 旧表;
可以将 旧表数据复杂 但是不能复杂约束条件 不能复制键
如何查询表
select
where
group by
having
distinct
order by
limit
regexp
like
...
insert into emp(name,sex,age,hire_date,post,salary,office) values
('tom','male',77,'20201001','teacher',300000.5,1),
('qom','male',77,'20201002','teacher',110000.5,2),
('wom','female',27,'20201003','teacher',101000.5,2),
('aom','male',17,'20211003','sale',100010.5,2),
('wom','male',37,'20201103','sale',100010.5,3),
('qom2','male',27,'20201003','teacher',100200.5,3),
('com','female',77,'20211003','teacher',300000.5,3),
('som','male',17,'20121003','operation',200000.5,2),
('gbom','male',57,'20101003','operation',100000.5,1),
('thgm','male',73,'20101003','teacher',200000.5,1)
select * from xx1 \G; 按每个展示
书写顺序:
select id,name from emp where id > 3
执行顺序:
from >where >select
虽然执行顺序和书写顺序不一致
但是就按书写顺序的方式写 sql 先用 * 占位 之后去补全后面sql语句 将*号改为替换后的具体字段
1.where 约束条件
作用:对整体数据的筛选操作
select * from emp where salary = 100000;
select * from emp where salary = 100000 or salary >= 120000;
select * from emp where salary = 100000 and name = 'tom';
select * from emp where name like '%o%'; 名字中有o的 %接受o前后全部 _收一个字符
select * from emp where name like '_o_';
select * from emp where name like '____'; 筛选长度为4的
select * from emp where id not between 3 and 6;
select * from emp where id not in (1,3,5);
针对NULL 不能用= 用is
2.group by 分组
作用:对数据进行分组
按照部门分组:
select * from emp group by post;
分组后 最小可操作单位应该是组 而不是组内的数据
两种情况: 没有严格模式 是返回每个组的第一条数据 但是不符合规范
如果设置了严格模式 会报错
set global sql_mode = 'strict_trans_tables,only_full_group_by';
设置严格模式后 按什么分组 就只能拿到什么
select post from emp group by post; 按部门分组
1.获取每个部门的最高薪资
select post,max(salary) from emp group by post; 借助一些方法来得到
select post as '部门',max(salary) as '最高薪资' from emp group by post; 借助一些方法来得到
as 起别名
聚合函数: 针对每组进行操作
sum()总和
avg()平均
count()计数
group_concat(name) 得到分组后 普通字段的值
还能拼接字段
group_concat(name,'_DSB') 将拿到的name加上_DSB
group_concat(name,':',salary) 将拿到的name加上_DSB
concat 不分组时用
group_concat 分组用
as语法 还可以给表起别名
select * from emp as t1;
获得年薪
select name,salary*12 as '年薪' from emp;
group by 和 where
1.同时出现 group by 在where后面
2.where先对整体数据进行过滤 再分组
聚合函数只能在分组后使用 不能用在where
不分组默认整体为一组 可以用聚合函数
拿到表内最大薪资人
select name,salary from emp where salary = (select max(salary) from emp);
having 分组后的筛选条件
语法和where一致 只不过是分组后 进行的操作
可以直接使用聚合函数
distinct 去重
必须完全一致的数据才能去重
一定不要忽视主键 有主键存在的情况下 不可能去重
order by 排序
select * from emp order by salary;
select * from emp order by salary asc; 升序 可以不写
select * from emp order by salary dasc; 降序
select * from emp order by salary dasc,salary asc; 指定多个进行排 先后
limit 分页
select * from emp limit 3; 第一条到5条
select * from emp limit 0,5; 第一个参数起始位置 第二个参数条数'
正则表达式 re模块
select * from emp where name regexp '^j.*(n|y)$'; j开头 n或者y结尾
多表查询
select * from dep,emp; 得到一个笛卡尔积 一对多
select * from emp,dep where emp.dep_id = dep.id; 可以得到对应表
inner join 内链接
left join 左连接
right join 右链接
union 全链接
select * from emp inner join dep on emp.dep_id = dep.id; 特点: 只拼接两个表内共有的
select * from emp left join dep on emp.dep_id = dep.id; 特点: 左表所有数据都展示 无对应项用NUll
select * from emp right join dep on emp.dep_id = dep.id; 特点: 右表所有数据都展示 无对应项用NUll
select * from emp left join dep on emp.dep_id = dep.id 特点: 左表所有数据都展示 无对应项用NUll
union
select * from emp right join dep on emp.dep_id = dep.id; 特点: 左右表所有数据都展示 无对应项用NUll
子查询
是平时解决问题的思路
:将一个查询语句的的结果 当作 另一个查询语句的条件去用
总结:表的查询结果 可以当作其他表的查询条件
也可以通过起别名的方式 将其变为虚拟表 与 替他表关联
create table emp(id int(4) primary key auto_increment, name char(4), sex set('male','female') default 'male',
hire_date date, post varchar(50), post_comment varchar(50), salary float(15,2), office int, foreign key(office)
references office(office_id));
习题
1.查询平均年龄大于25的部门名
多表查询的两种思路 1.拼表 2.子查询
拼表 涉及多表操作时 必须加上前缀
select office.office_name,avg(age) from emp inner join office
on emp.office = office.office_id
group by office_name
having avg(age) > 50;
子查询
select office_name from office where office_id in (select office from emp group by office having avg(age) > 25);
关键字 exists(了解)
用来判断结果为True 或者 False 只返回布尔值 返回True外层执行 否则 不执行
select * from emp where exists (select id from office where id > 30);
navicat可视化操作数据库
方便写代码的工具
在终端写 不方便书写
navicat 封装了所有操作数据库的命令 无需手写
非常强大 能够充当多个数据库的客户端
utf8mb4 可以存表情
navicat 要刷新 因为反应速度较慢
当有一些需求软件无法满足 就自己写sql
提示: mysql大小写不敏感 mysql建议所有关键字写大写
mysql注释: -- or #
快速注释 ctrl + ?
练习题
python如何操作mysql pymysql模块
sql注入问题
结合数据库完成用户的登录功能
name = input('>>>:')
password = input('>>>:')
sql = 'select * from user where name = {} and password = {}'.format(name,password)
row = cursor.execult(sql)
if row != 0:
print('登录成功')
else:
print('登录失败')
若 输入 '柴 --xasdas' 则 后面密码的验证就被注释掉了....
或者 sadasd or 1=1 -- 则恒成立 ... 就直接被登陆了 nb 啊
sql注入:
利用语法特性 实现一些固定的语法 mysql利用的就是mysql的注释语法
日常生活中:
很多软件在注册的时候都不让含有特殊符号
就是为了防止 构造出特定的语法 入侵数据库
解决方法:
敏感的数据 不要自己进行拼接!!!
python:
先用%s占位 之后直接交给execute方法即可
rows = cursor.execute(sql,(username,password))
会自动识别sql里面%s 会替换 并且会过滤特殊符号
sql 语句后面不用跟填入数据
sql = "select * from userinfor where name = %s and password = %s"
course.execute(sql,(name,password))
execute函数会自动识别%s 所以%s不该跟‘’ 并且execute会对输入的字符进行检验 避免sql注入
掌握 1.navicat
2.练习题
3.pymysql使用
4.sql注入的解决
pymysql
增删改查
pymysql补充:
sql = 'select * from userinfor where name = %s and password = %s' # 查
sql1 = 'insert into userinfor(name,password) values(%s,%s)' # 增
sql2 = 'update userinfor set name = %s where id = 1' # 改
sql3 = 'delete from userinfor where name = %s' # 删
查可以 但增删改有二次确认的过程
conn.commit() 确认
可以修改conn里面的 autocommit 来进行自动提交
一次增加多条数据:
sql1 = 'insert into userinfor(name,password) values(%s,%s)' # 增
rows = cursor.executemany(sql1,[(name,password)(name,password)(name,password)])
比 values(),(),()好多了
了解知识点
视图:
通过查询得到一张虚拟表 然后保存下来 下次可以直接使用
频繁操作一张表 可以制作位视图 后续直接操作
create view 表名 as 虚拟表的查询语句
视图的数据 只会有结构 不会有 数据
数据还会在 原表中
视图里面的数据不要进行修改 否则可能会影响真正的表
视图使用频率不高 了解即可
触发器:
在满足对表的数据 增删改 的情况下自动触发的功能 称为触发器
可以用来做记录
NEW指代是被监视的数据
语法结构:
delimiter $$
create trigger 触发器名 before/after insert/update/delete on 表名 for each row
begin
if NEW.success = 'no' then
insert xxxx sql语句
end if;
end$$
delimiter ;
六种情况 操作前后
具体使用,针对触发器的名字应该见名知意
ps:修改默认语句结束符; delimiter $$ 将默认结束符改为$$ 只作用当前窗口
mysql内置函数 NOW() 获取当前时间
删除:
drop trigger xxx;
事务*:
什么是事务 开启一个事物可以包含多个sql语句
可以同时成功 或者同时失败 称为 事务的原子性
作用: 保证了对数据操作的安全性
转账问题
发 成功
收 失败
GG
事务的四大特性:
ACID
A:原子性
一个事务是一个不可分割的单位
事务中的各种操作 只能同时成功失败
C:一致性
事务必须是使数据由一个一致性的状态变为另一个一致性的状态
一致性和原子性密切相关
I:隔离性
一个事务的执行 不能被其他事务干扰
即一个事务内部的操作对并发的其他事务是隔离的,并发执行的事务之间是互相不干扰的
D:持久性
永久性
一个事务一旦执行成功 那么它对数据的修改是永久的 直接修改的硬盘内的数据
其他操作或者故障不应该对其有影响
事务的使用:
关键字:
开启关键字:start transaction;
回滚(回到事务执行前状态):rollback;
二次确认(确认后无法回滚了):commit;
start transaction;
update user set balance = 900 where name = 'xx';
update user set balance = 900 where name = 'xx';
update user set balance = 900 where name = 'xx';
commit;
总结:当想要多条sql语句保存一致性 就考虑使用事务
存储过程:
类似于python中的自定义函数 其内部包含一系列可以执行的sql语句
存储过程存放在MYsql服务端中,可以直接通过调用存储过程触发内部sql语句执行
基本使用:
delimiter $$
create procedure p1(in m int,in n int,out res int) # in表示只进不出 out表示可以返回
begin
select tname from teacher where tid>m and tid <n;
set res = 0;
end$$
delimiter ;
针对形参res 不能直接传数据 应定义一个变量
set @ret = 10;
select @ret;
调用: call 存储过程的名字()
三种数据库开发模式:
应用程序:程序员写代码开发
Mysql:
1.提前写好存储过程 供应用程序调用 提升开发效率 执行效率提升 人为因素和跨部门沟通问题 扩展性差
2.全部自己动手写 扩展性高 开发效率降低 写sql语句繁琐 还要考虑优化问题
3.应用程序只写程序代码 不写sql语句 基于框架 直接调用操作即可 ORM框架 开发效率最高 但语句扩展性差
在pymysql调用存储过程:
# 调用存储过程:
cursor.callproc('p1',(1,5,10)) 10传入值会相当于直接传入变量 pymysql模块帮助完成了这件事
内置函数:
1.日期格式化 date_format(time,'%Y-%M')
流程控制:
流程控制:if 判断 while循环
Begin
if i = 1 Then
sql;
elseif i = 2 Then
sql;
else:
sql;
end if;
End
索引理论:
数据都是存在硬盘上面 查询数据肯定会进行IO操作
索引就是一种数据结构 类似与书的目录
意味着查询数据应该先找目录在找数据 而不是一页一页的翻书 减少IO操作
索引在Mysql中 也叫'键' 是存储引擎用于快速查询记录的一种数据结构
primary key
unique key
index key
ps:foreign key 是用来链接的 不是用来加速查询
前两种 除了可以增加查询速度 还有约束条件 而最后一个没有任何约束条件 只是来帮助快速查询
本质:
通过不断缩小想要的数据筛选范围 选出最终结果 同时将随机事件(一页一页的翻)变为顺序事件(先找目录 再找数据)
有了索引机制后 我们总有一种固定方式查数据
一张表中可有 多个索引(多个目录)
弊端:
1.当表中有大量数据的前提下 创建索引数据会很慢
2.在索引创建完毕后 对表的查询性能会大幅提升 但是写入性能也会大幅度降低
b+树:
磁盘块
17-35
p1 p2 p3
8-12 26-30 65-87
.
.
.
.
磁盘分块
ps: 好像二叉搜索树
为什么建议将id字段作为索引:
占用空间少 降低树的高度 从而减少查询次数
聚集索引:
primary key
聚集索引指的就是主键
Innodb 两个文件 将主键放在了idb表中
MyIsam 三个文件 单独将索引放在了一个文件
辅助索引:
查询数据时 不可能一直使用到主键 也可能是其他字段
这时 没有办法利用聚集索引
这时 可以根据情况设置辅助索引
叶子姐节点存放的数据是对应的主键值
先按照辅助索引得到数据主键值
再去主键的聚集索引里面查看数据
覆盖索引:
在辅助索引的叶子节点就拿到了需要的数据
select name from emp where name = 'chai';
浙公网安备 33010602011771号