老男孩数据库学习记录
课程安排(day44)数据库基础前端基础django基础bbs小作业路飞学城django drflinuxgok8s docker爬虫cmdb代码发布每日测验今日暂无数据库存储数据的演变过程随意的存到一个文件中、数据格式也是千差万别的完全取决于我们自己"""# 小李jason|123|NB# 小王egon-123-DBJ# 小红tank~123~hecha"""软件开发目录规范限制了存储数据的具体
·
课程安排(day44)
数据库基础
前端基础
django基础
bbs小作业
路飞学城 django drf
linux
go
k8s docker
爬虫
cmdb
代码发布
每日测验
今日暂无
数据库
存储数据的演变过程
-
随意的存到一个文件中、数据格式也是千差万别的完全取决于我们自己
""" # 小李 jason|123|NB # 小王 egon-123-DBJ # 小红 tank~123~hecha """ -
软件开发目录规范
限制了存储数据的具体位置
""" bin conf core db lib readme """假设上述是一个单机游戏
那么每个人的游戏记录只会在自己的计算机上面保存
注册登录的账号也只能在自己的计算机上有效
-
如何将单机变成连网
""" 将数据库保存部分全部统一起来 所有人操作数据都来一个地方操作 """
数据库的本质
"""
本质其实就是一款基于网络通信的应用程序
那其实每个人都可以开发一款数据库软件 因为它仅仅就是一个基于网络通信的应用程序
也就意味着数据库软件其实有很多很多
关系型数据库
MySQL、oracle、db2、access、sql server
非关系型数据库
redis、mongodb、memcache
"""
# 关系型
1 数据之间彼此有关系或者约束
男生表和前女友表
2 存储数据的表现形式通常是以表格存储
name password hobby
jason 123 学习
egon 123 女教练
tank 123 吃生蚝
每个字段还会有存储类型的限制
比如姓名只能存字符串...
# 非关系型
存储数据通常都是以k,v键值对的形式
MySQL
"""
任何基于网络通信的应用程序底层用的都是socket
-服务端
-基于socket通信
-收发消息
-SQL语句
-客户端
-基于socket通信
-收发消息
-SQL语句
"""
MySQL不单单支持MySQL自己的客户端app还支持其他编程语言来充当客户端操作
如何解决语言沟通的障碍？
# 1 让服务端兼容所有的语言(一个人精通多国语言)
# 2 采用统一的语言(SQL语句)
重要概念介绍
"""
库 》》》 文件夹
表 》》》 文件
记录 》》》 文件内一行行的数据
name password hobby
jason 123 学习
egon 123 女教练
tank 123 吃生蚝
表头 表格的第一行字段
字段 name、password、hobby
"""
MySQL的安装
"""
在IT界 一般都不会轻易的使用最新版本的软件，因为新版本可能会出现各种问题
(你原本项目跑的好好的 非要画蛇添足更新版本 然后项目奔溃)
小段子:
更新完没事 那么你还是一个普通员工
更新完出事 那么你就是一名"烈士"
"""
# MySQL有很多版本(5.6、5.7、8.0) 目前企业里面用的比较多的还是5.6左右
参考网站:https://www.mysql.com/
下载5.6版本即可，如果你下载了其他版本问题也不大，因为sql语句是一样的学
按照教学方式下载，会将服务端和客户端一并下载到本地
为了学习方便我们将服务端和客户端都在本地启动，后期到了公司服务端会专门跑在一台服务器上，所有人基于网络连接服务端操作
MySQL服务端与客户端
"""
服务端
mysqld.exe
客户端
mysql.exe
"""
注意
"""
在前期配置MySQL的时候 cmd终端尽量以管理员的身份运行
windows+r 输入cmd 进入的是普通用户终端 有一些命令是无法执行的
搜索cmd右键 以管理员身份运行
"""
启动
-
先切换到mysqld所在的bin目录下，然后输入mysqld即可
-
保留原来的cmd窗口重新打开一个
""" 常见软件的默认端口号 MySQL 3306 redis 6379 mongodb 27017 django 8000 flask 5000 ... MySQL第一次以管理员身份进入是没有密码的 直接回车即可 客户端连接服务端完整命令 mysql -h 127.0.0.1 -P 3306 -uroot -p """
sql语句初识
"""
1 MySQL中的sql语句是以分号作为结束的标志
2 基本命令
show databases; 查看所有的库名
3 连接服务端的命令可以简写
mysql -h 127.0.0.1 -P 3306 -uroot -p
mysql -uroot -p
4 当你输入的命令不对 又不想让服务端执行并返回报错信息 可以用\c取消
错误命令
5 客户端退出 退出命令加不加分号都可以执行
quit
exit
6 当你在连接服务端的时候 发现只输入mysql也能连接
但是你不是管理员身份 而只是一个游客模式
"""
环境变量配置及系统服务制作
小知识点补充
"""
1 如何查看当前具体进程
tasklist
tasklist |findstr mysqld
2 如何杀死具体进程(只有在管理员cmd窗口下才能成功)
taskkill /F /PID PID号
"""
环境变量配置
"""
每次启动mysqld需要先切到对应的文件路径下才能操作太多繁琐
将mysqld所在的文件路径添加到系统环境变量中
"""
还是繁琐 需要起两个cmd窗口 不好
将mysql服务端制作成系统服务(开机自启动)
"""
查看当前计算机的运行进程数
services.msc
将mysql制作成系统服务
mysqld --install
移除mysql系统服务
mysqld --remove
"""
设置密码
"""
mysqladmin -uroot -p原密码 password 新密码
改命令直接在终端输入即可 无序进入客户端
mysqladmin -uroot -p123 password 123456
"""
破解密码
出去玩了好几个月，回来返现密码忘了，死活进不去
"""
你可以将mysql获取用户名和密码校验的功能看成是一个装饰器
装饰在了客户端请求访问的功能上
我们如果将该装饰器移除 那么mysql服务端就不会校验用户名和密码了
"""
# 1 先关闭当前mysql服务端
命令行的方式启动(让mysql跳过用户名密码验证功能)
mysqld --skip-grant-tables
# 2 直接以无密码的方式连接
mysql -uroot -p 直接回车
# 3 修改当前用户的密码
update mysql.user set password=password(123456) where user='root' and host='localhost';
"""
真正存储用户表的密码字段 存储的肯定是密文
只有用户自己知道明文是什么 其他人都不知道 这样更加的安全
密码比对也只能比对密文
"""
# 4 立刻将修改数据刷到硬盘
flush privileges;
# 5 关闭当前服务端 然后以正常校验授权表的形式启动
统一编码
-
mysql默认的配置文件
""" my-default.ini ini结尾的一般都是配置文件 程序启动会先加载配置文件中的配置之后才真正的启动 """ [mysqld] # 一旦服务端启动立刻加载下面的配置 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysql] # 一旦客户端启动立刻加载下面的配置 ... [client] # 其他客户端 ... # 需要你自己新建一个my.ini的配置文件 # 验证配置是否真的是自动加载 [mysql] print('hello world') # 修改配置文件后一定要重启服务才能生效 # 统一编码的配置 无需掌握 直接拷贝即可 # 偷懒 将管理员的用户名和密码也添加到配置文件中 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8 [mysql] user="root" password=123456 default-character-set=utf8
基本sql语句
PS:大部分程序的业务逻辑其实都是增删改查
"""
辛辛苦苦一个月 只为了一条sql语句(将自己账户的钱修改一下)
"""
针对库的增删改查(文件夹)
# 增
create database db1;
create database db2 charset='gbk';
# 查
show databases; # 查所有
show create database db1; # 查单个
# 改
alter database db2 charset='utf8';
# 删
drop database db2;
针对表的增删改查(文件)
"""
在操作表(文件)的时候 需要指定所在的库(文件夹)
"""
# 查看当前所在的库的名字
select database();
# 切换库
use db1;
# 增
create table t1(id int,name char(4));
# 查
show tables; # 查看当前库下面所有的表名
show create table t1;
describe t1; # 支持简写 desc t1;
# 改
alter table t1 modify name char(16);
# 删
drop table t1;
"""
create table db2.t1(id int); 也可以用绝对路径的形式操作不同的库
"""
针对数据的增删改查(一行行数据)
"""
一定要先有库 有表 最后才能操作记录
"""
# 增
insert into t1 values(1,'jason');
insert into t1 values(1,'jason'),(2,'egon'),(3,'tank');
# 查
select * from t1; # 该命令当数据量特别大的时候不建议使用
select name from t1;
# 改
update t1 set name='DSB' where id > 1;
# 删
delete from t1 where id > 1;
delete from t1 where name='jason';
# 将表所有的数据清空
delete from t1;
作业布置
- 每个人自己完成MySQL的安装
- 整理今日内容
- 今天介绍的所有命令都给我记下来尤其是数据的操作命令
- 复习巩固之前的知识点
每日测验(day45)
- 简述数据库的本质及分类
- MySQL中针对库、表、数据的增删改查语句
上周内容回顾
-
存储数据的演变过程
""" 1 随意建文件、数据格式也是千差万别的 jason|123 egon~123 tank+123 2 软件开发目录规范 明确了数据存放的大致位置 3 从单机变成联网 如何操作 数据统一存放并且格式固定 将原本存放于本地的数据全部存放到某一个基于网络通信的地方 然后所有的客户端都去该地方统一的操作数据 """ -
数据库的概念
""" 本质其实就是一款基于网络通信的应用程序 也就意味着数据库软件你也可以自己开发 还意味着市面上的数据库软件很多 """ 关系型 MySQL、oracle、access、SQL server、sqlite 非关系型 redis、mongodb(最像关系型数据库的非关系型数据库)、memcache 关系型 数据彼此之间有约束或者联系 存储数据一般都是以表格形式 非关系型 通常以K、V键值对的形式存储数据 -
MySQL简介
""" 本质其实就是一款基于网络通信的应用程序 MySQL开源免费 企业使用率较广 使用方便 好学 """ -
SQL语句
""" 服务端 客户端 MySQL支持多中类型的客户端 1.自己写的客户端 2.其他编程语言写(python、java、php...) 如何兼容 1.本人牛逼 自动区分对应语言 2.统一标准(******) """ -
重要概念
""" 库 文件夹 表 文件 记录 文件内一行行的数据 表头 表格第一行字段 字段 字段名+字段类型 """ -
MySQL的安装
""" 1 版本问题 IT界一般情况下不会直接使用最新版本的软件 (如果你在使用某一些模块或者软件的时候出现了未知的错误 确实无法解决，那么这个时候应该考虑是否是模块或者软件兼容性问题) 我们以5.6版本为例(市面上用的最多的版本) python解释器推荐你们使用3.6版本 其他版本针对一些模块还存在兼容性问题 2 官网安装即可(每个人都要自己独立的安装完成) """ -
MySQL主要文件介绍
""" bin文件下的两个启动程序 mysqld 服务端 mysql 客户端 """ mysqld先启动服务端 之后客户端连接 mysql -h ... -P ... -uroot -p 简写 mysql -uroot -p 第一次连接不需要密码 直接回车即可 当你值属于mysql发现也可以连接服务端 但是这个时候是游客模式 -
初识sql语句
""" sql语句是以分号作为结束的标志 ; 取消书写的内容执行 \c 查看所有的库名 show databases; 退出服务端 quit exit """ -
环境变量及系统服务配置
""" 环境变量 将bin目录路径添加到系统环境变量即可 制作系统服务的时候cmd一定要用管理员身份运行 mysqld --install mysqld --remove """ -
修改密码
mysqladmin -uroot -p原密码 password新密码 # 直接在cmd输入即可 无需进入服务端 -
忘记密码(了解)
""" 1 跳过授权表的形式启动服务端 mysqld --skip-grant-tables 2 直接无密码登录管理员账户 mysql -uroot -p 3 重置当前用户的密码 update mysql.user set password=password(123) where user='root' and host='localhost'; flush privileges; 4 再以正常的方式启动服务端 """ -
配置文件
""" MySQL的配置文件 在启动的时候都会自动去加载对应的配置 \s 发现编码不一致 """ [mysql] ... [mysqld] ... [client] ... 编码配置无需掌握 直接拷贝使用即可 -
基本SQL语句
其实我们程序员写的本质就是数据的增删改查，没什么高大上的东西
"""针对库的增删改查""" create database db1; show databases; show create database db1; alter database db1 charset='gbk'; drop database db1; """针对表的增删改查""" # 如何查看当前所在的库 select database(); # 切换当前所在的库 use db1; create table t1(id int,name char); show tables; show create table t1; desc t1; >>>: 全称 describe t1; alter table t1 modify name char(16); drop table t1; """针对记录的增删改查""" # 先明确 要有库和表 才能操作记录 insert into t1 values(1,'jason'),(2,'egon'); select * from t1; select name from t1; update t1 set name='DSB' where name='egon'; delete from t1; delete from t1 where id > 1;
今日内容概要
-
存储引擎
-
数据类型
整型
浮点型
字符类型
日期类型
枚举与集合类型
-
约束条件
今日内容详细
存储引擎
日常生活中文件格式有很多中，并且针对不同的文件格式会有对应不同存储方式和处理机制(txt,pdf,word,mp4…)
针对不同的数据应该有对应的不同的处理机制来存储
存储引擎就是不同的处理机制
MySQL主要存储引擎
-
Innodb
是MySQL5.5版本及之后默认的存储引擎
存储数据更加的安全
-
myisam
是MySQL5.5版本之前默认的存储引擎
速度要比Innodb更快 但是我们更加注重的是数据的安全
-
memory
内存引擎(数据全部存放在内存中) 断电数据丢失
-
blackhole
无论存什么，都立刻消失(黑洞)
"""
# 查看所有的存储引擎
show engines;
# 不同的存储引擎在存储表的时候 异同点
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;
# 存数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
"""
创建表的完整语法
# 语法
create table 表名(
字段名1 类型(宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件
)
# 注意
1 在同一张表中字段名不能重复
2 宽度和约束条件是可选的(可写可不写) 而字段名和字段类型是必须的
约束条件写的话 也支持写多个
字段名1 类型(宽度) 约束条件1 约束条件2...,
create table t5(id); 报错
3 最后一行不能有逗号
create table t6(
id int,
name char,
); 报错
"""补充"""
# 宽度
一般情况下指的是对存储数据的限制
create table t7(name char); 默认宽度是1
insert into t7 values('jason');
insert into t7 values(null); 关键字NULL
针对不同的版本会出现不同的效果
5.6版本默认没有开启严格模式 规定只能存一个字符你给了多个字符，那么我会自动帮你截取
5.7版本及以上或者开启了严格模式 那么规定只能存几个 就不能超，一旦超出范围立刻报错 Data too long for ....
"""严格模式到底开不开呢？"""
MySQL5.7之后的版本默认都是开启严格模式的
使用数据库的准则:
能尽量少的让数据库干活就尽量少 不要给数据库增加额外的压力
# 约束条件 null not null不能插入null
create table t8(id int,name char not null);
"""
宽度和约束条件到底是什么关系
宽度是用来限制数据的存储
约束条件是在宽度的基础之上增加的额外的约束
"""
基本数据类型
整型
-
分类
TINYINT SMALLINT MEDUIMINT INT BIGINT
-
作用
存储年龄、等级、id、号码等等
"""
以TINYINT
是否有符号
默认情况下是带符号的
超出会如何
超出限制只存最大可接受值
"""
create table t9(id tinyint);
insert into t9 values(-129),(256);
# 约束条件之unsigned 无符号
create table t10(id tinyint unsigned);
create table t11(id int);
# int默认也是带符号的
# 整型默认情况下都是带有符号的
# 针对整型 括号内的宽度到底是干嘛的
create table t12(id int(8));
insert into t12 values(123456789);
"""
特例:只有整型括号里面的数字不是表示限制位数
id int(8)
如果数字没有超出8位 那么默认用空格填充至8位
如果数字超出了8位 那么有几位就存几位(但是还是要遵守最大范围)
"""
create table t13(id int(8) unsigned zerofill);
# 用0填充至8位
# 总结:
针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了
严格模式
# 如何查看严格模式
show variables like "%mode";
模糊匹配/查询
关键字 like
%:匹配任意多个字符
_:匹配任意单个字符
# 修改严格模式
set session 只在当前窗口有效
set global 全局有效
set global sql_mode = 'STRICT_TRANS_TABLES';
修改完之后 重新进入服务端即可
浮点型
-
分类
FLOAT、DOUBLE、DECIMAL
-
作用
身高、体重、薪资
# 存储限制 float(255,30) # 总共255位 小数部分占30位 double(255,30) # 总共255位 小数部分占30位 decimal(65,30) # 总共65位 小数部分占30位 # 精确度验证 create table t15(id float(255,30)); create table t16(id double(255,30)); create table t17(id decimal(65,30)); """你们在前期不要给我用反向键 所有的命令全部手敲！！！增加熟练度""" insert into t15 values(1.111111111111111111111111111111); insert into t16 values(1.111111111111111111111111111111); insert into t17 values(1.111111111111111111111111111111); float < double < decimal # 要结合实际应用场景 三者都能使用
字符类型
-
分类
""" char 定长 char(4) 数据超过四个字符直接报错 不够四个字符空格补全 varchar 变长 varchar(4) 数据超过四个字符直接报错 不够有几个存几个 """ create table t18(name char(4)); create table t19(name varchar(4)); insert into t18 values('a'); insert into t19 values('a'); # 介绍一个小方法 char_length统计字段长度 select char_length(name) from t18; select char_length(name) from t19; """ 首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的 但是在显示的时候MySQL会自动将多余的空格剔除 """ # 再次修改sql_mode 让MySQL不要做自动剔除操作 set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';char与varchar对比
""" char 缺点:浪费空间 优点:存取都很简单 直接按照固定的字符存取数据即可 jason egon alex wusir tank 存按照五个字符存 取也直接按照五个字符取 varchar 优点:节省空间 缺点:存取较为麻烦 1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank 存的时候需要制作报头 取的时候也需要先读取报头 之后才能读取真实数据 以前基本上都是用的char 其实现在用varchar的也挺多 """ 补充: 进来公司之后你完全不需要考虑字段类型和字段名 因为产品经理给你发的邮件上已经全部指明了
时间类型
-
分类
date:年月日 2020-5-4
datetime:年月日时分秒 2020-5-4 11:11:11
time:时分秒11:11:11
Year:2020
create table student( id int, name varchar(16), born_year year, birth date, study_time time, reg_time datetime ); insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');
枚举与集合类型
-
分类
""" 枚举(enum) 多选一 集合(set) 多选多 """ -
具体使用
create table user( id int, name char(16), gender enum('male','female','others') ); insert into user values(1,'jason','male'); 正常 insert into user values(2,'egon','xxxxooo'); 报错 # 枚举字段 后期在存数据的时候只能从枚举里面选择一个存储 create table teacher( id int, name char(16), gender enum('male','female','others'), hobby set('read','DBJ','hecha') ); insert into teacher values(1,'jason','male','read'); 正常 insert into teacher values(2,'egon','female','DBJ,hecha'); 正常 insert into teacher values(3,'tank','others','生蚝'); 报错 # 集合可以只写一个 但是不能写没有列举的总结
""" 字段类型 严格模式 约束条件 not null zerofill unsigned """
作业
- 整理今日内容到笔记
- 设计的所有命令全部用手敲一遍，不要使用方向键
每日测验(day46)
- 你都了解哪些MySQL存储引擎，简单说说它们的特点
- 创建表的完整语法及注意事项
- MySQL基本数据类型有哪些
昨日内容回顾
-
存储引擎
""" 针对不同的数据应该有不同的处理机制 查看MySQL所有的存储引擎 show engines; Innodb MySQL5.5版本之后默认的存储引擎 支持事务 行锁 外键 数据更加的安全 创建表会生成两个文件 表结构文件 表数据文件 MyIsam MySQL5.5版本之前的默认的存储引擎 它虽然在数据安全上没有Innodb可靠 但是查询速度上较Innodb更快 创建表会生成三个文件 表结构文件 表数据文件 表索引文件 memory 内存引擎 临时数据存储 创建表会生成一个文件 表结构文件 blackhole 黑洞 创建表会生成一个文件 表结构文件 """ -
创建表的完整语法
# 语法 create table 表名( 字段名1 字段类型(宽度) 约束条件, 字段名2 字段类型(宽度) 约束条件, 字段名3 字段类型(宽度) 约束条件 ) # 注意 1 在同一张表中字段名不能重复 2 宽度和约束条件是可选的 字段名和字段类型是必须的 3 最后一个字段后面不能加逗号 # 宽度一般情况下是对存储数据的限制 # 约束条件可以有多个 是额外的限制 -
严格模式
# 使用数据库的时候 应该尽量让数据库少干活！！！ show variables like '%mode'; set session set global sql_mode = 'STRICT_TRANS_TABLES'; -
基本数据类型
""" 整型 TINYINT INT BIGINT 默认都是有符号的 int(4) 针对整型括号内的数字并不是用来限制存储的 不够四位默认用空格填充 够四位有几位存几位 zerofill 总结 以后在定义整型字段的时候 其实都不需要自己加宽度 因为整型都自带宽度 完全足够展示所有的数字 浮点型 float(255,30) double(255,30) decimal(65,30) 只要是浮点型 括号后面有两个数字 第一个数字表示的总位数 第二个数字表示的小数位 精确度 float < double < decimal 扩展:其实在实际生产中 很多看似需要用整型或者浮点型存储的数据 内部可能都是用的字符类型存储 字符类型 char(4) 定长 只能存储最大四个字符 超出报错 不超默认用空格补全 varchar(4) 变长 只能存储最大四个字符 超出报错 不超有几个存几个 char Vs varchar char 浪费空间 存取方便 简单暴力 直接按照固定的位数存取即可 varchar 节省空间 但是相较于char 它的存取速度较慢 因为它在存取数据的时候需要有一个 报头 以前用char很多 限制varchar很多 时间类型 date 年月日 datetime 年月日时分秒 time 时分秒 year 年(有范围限制) 枚举与集合类型 枚举enum 多选一 集合set 多选多(也可以单选) gender enum('male','female','others') hobby set('read','sangna','DBJ','hecha') 你的数据只能来源于字段提供的选择项 不能凭空捏造 """ -
约束条件
""" zerofill unsigned not null """
今日内容概要
- 约束条件
- 表与表之间建关系(约束)(重点)
- 修改表的完整语法大全
- 复制表
- 作业布置(如何判断表关系及如何建立表关系)
今日内容详细
约束条件
default默认值
# 补充知识点 插入数据的时候可以指定字段
create table t1(
id int,
name char(16)
);
insert into t1(name,id) values('jason',1);
create table t2(
id int,
name char(16),
gender enum('male','female','others') default 'male'
);
insert into t2(id,name) values(1,'jason');
insert into t2 values(2,'egon','female');
unique唯一
# 单列唯一
create table t3(
id int unique,
name char(16)
);
insert into t3 values(1,'jason'),(1,'egon');
insert into t3 values(1,'jason'),(2,'egon');
# 联合唯一
"""
ip和port
单个都可以重复 但是加载一起必须是唯一的
"""
create table t4(
id int,
ip char(16),
port int,
unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);
insert into t4 values(3,'127.0.0.2',8080);
insert into t4 values(4,'127.0.0.1',8080); 报错
primary key主键
"""
1.单单从约束效果上来看primary key等价于not null + unique
非空且唯一！！！
"""
create table t5(id int primary key);
insert into t5 values(null); 报错
insert into t5 values(1),(1); 报错
insert into t5 values(1),(2);
"""
2.它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据
"""
# 1 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t6(
id int,
name char(16),
age int not null unique,
addr char(32) not null unique
);
# 2 如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提示查询速度
# 3 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键
# 单个字段主键
create table t5(
id int primary key
name char(16)
);
# 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
create table t7(
ip char(16),
port int,
primary key(ip,port)
);
"""
也意味着 以后我们在创建表的时候id字段一定要加primary key
"""
auto_increment自增
# 当编号特别多的时候 人为的去维护太麻烦
create table t8(
id int primary key auto_increment,
name char(16)
);
insert into t8(name) values('jason'),('egon'),('kevin');
# 注意auto_increment通常都是加在主键上的 不能给普通字段加
create table t9(
id int primary key auto_increment,
name char(16),
cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
结论
"""
以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment
"""
补充
delete from t1 删除表中数据后 主键的自增不会停止
truncate t1 清空表数据并且重置主键
表与表之间建关系
"""
定义一张员工表 表中有很多字段
id name gender dep_name dep_desc
"""
# 1 该表的组织结构不是很清晰(可忽视)
# 2 浪费硬盘空间(可忽视)
# 3 数据的扩展性极差(无法忽视的)
# 如何优化？
"""上述问题就类似于你将所有的代码都写在了一个py文件中"""
将员工表拆分 员工表和部门表
外键
"""
外键就是用来帮助我们建立表与表之间关系的
foreign key
"""
表关系
"""
表与表之间最多只有四种关系
一对多关系
在MySQL的关系中没有多对一一说
一对多 多对一 都叫一对多！！！
多对多关系
一对一关系
没有关系
"""
一对多关系
"""
判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议
换位思考 分别站在两张表的角度考虑
员工表与部门表为例
先站在员工表
思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
不能！！！
(不能直接得出结论 一定要两张表都考虑完全)
再站在部门表
思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
能！！！
得出结论
员工表与部门表示单向的一对多
所以表关系就是一对多
"""
foreign key
1 一对多表关系 外键字段建在多的一方
2 在创建表的时候 一定要先建被关联表
3 在录入数据的时候 也必须先录入被关联表
# SQL语句建立表关系
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
# 修改dep表里面的id字段
update dep set id=200 where id=2; 不行
# 删除dep表里面的数据
delete from dep; 不行
# 1 先删除教学部对应的员工数据 之后再删除部门
操作太过繁琐
# 2 真正做到数据之间有关系
更新就同步更新
删除就同步删除
"""
级联更新 >>> 同步更新
级联删除 >>> 同步删除
"""
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
多对多
"""
图书表和作者表
"""
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(author_id) references author(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
"""
按照上述的方式创建 一个都别想成功！！！
其实我们只是想记录书籍和作者的关系
针对多对多字段表关系 不能在两张原有的表中创建外键
需要你单独再开设一张 专门用来存储两张表数据之间的关系
"""
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade # 同步更新
on delete cascade, # 同步删除
foreign key(book_id) references book(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
一对一
"""
id name age addr phone hobby email........
如果一个表的字段特别多 每次查询又不是所有的字段都能用得到
将表一分为二
用户表
用户表
id name age
用户详情表
id addr phone hobby email........
站在用户表
一个用户能否对应多个用户详情 不能！！！
站在详情表
一个详情能否属于多个用户 不能！！！
结论:单向的一对多都不成立 那么这个时候两者之间的表关系
就是一对一
或者没有关系(好判断)
客户表和学生表
在你们报名之前你们是客户端
报名之后是学生(期间有一些客户不会报名)
"""
一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
authordetail_id int unique,
foreign key(authordetail_id) references authordetail(id)
on update cascade # 同步更新
on delete cascade # 同步删除
)
总结
"""
表关系的建立需要用到foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三张存储
一对一
建在任意一方都可以 但是推荐你建在查询频率较高的表中
判断表之间关系的方式
换位思考！！！
员工与部门
图书与作者
作者与作者详情
"""
修改表(了解)
# 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 旧表; 不能复制主键 外键 ...
create table new_dep2 select * from dep where id>3;
作业布置
练习：账号信息表，用户组，主机表，主机组
#用户表
create table user(
id int not null unique auto_increment,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);
#用户组表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);
#主机表
create table host(
id int primary key auto_increment,
ip char(15) not null unique default '127.0.0.1'
);
#业务线表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);
#建关系：user与usergroup
create table user2usergroup(
id int not null unique auto_increment,
user_id int not null,
group_id int not null,
primary key(user_id,group_id),
foreign key(user_id) references user(id),
foreign key(group_id) references usergroup(id)
);
#建关系：host与business
create table host2business(
id int not null unique auto_increment,
host_id int not null,
business_id int not null,
primary key(host_id,business_id),
foreign key(host_id) references host(id),
foreign key(business_id) references business(id)
);
#建关系：user与host
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id),
foreign key(host_id) references host(id)
);
练习:
# 班级表
cid caption
# 学生表
sid sname gender class_id
# 老师表
tid tname
# 课程表
cid cname teacher_id
# 成绩表
sid student_id course_id number
最幸福的阶段(day47)
网络并发
数据库(简单)
前端(简单 枯燥)
django(有趣)
慢慢的蜕变(小白慢慢变成程序员)
每日测验
- 你所知道的约束条件有哪些
- 表与表之间关系有几种，如何判断
- 创建上述表关系的SQL语法如何书写
昨日内容回顾
-
约束条件
""" not null zerofill unsigned default gender enum('male','female','others') default 'male' unique 单列唯一 id int unique 联合唯一 ip int, port int, unique(ip,port) primary key 主键 1.在限制效果上跟not null + unique一致 id int primary key 2.它还是Innodb存储引擎组织数据的依据 1.使用Innodb规定一张表中必须有且只有一个主键 2.当你没有设置主键的时候 1.从上往下查找非空且唯一的自动升级为主键 2.如果什么都没有设置 那么就会使用内部隐藏的字段作为主键 无法使用到 3.一张表中通常都应该有一个id字段并且该字段应该是主键 auto_increment 自增列 该限制条件只能加在被设置成键的字段上 并且一般都是跟主键一起使用 id int primary key auto_increment """ 针对主键补充知识点 你在将表中数据删除的时候 主键的自增并不会停止 truncate 清空数据并重置主键 -
外键
""" 外键就是用来在代码层面真正的实现表与表之间的关系 foreign key """ -
表与表之间建关系
""" 表与表之间的关系只有三种 一对多 在MySQL中 一对多和多对一 都叫一对多 外键字段建在多的那一方 多对多 关系无需建外键 而是单独开设一张表专门用来存储关系 一对一 外键字段建在任意一方均可 但是推荐你建在查询频率较高的表中 """ """判断表关系前期不熟练一定要换位思考 慢慢问自己""" # 一对多判断 图书与出版社 先站在图书表 一本书能否被多个出版社出版 版权问题 不可以！！！ 再站在出版社表 一个出版社能否出版多本书 可以 结论:单向的一对多成立 那么表关系就是一对多 书是多的一方 外键带来的约束 1.在创建表的时候一定要先创建被关联表 2.在插入数据的时候也要先插入被关联表 3.操作数据的时候 会出现多种限制 同步更新 同步删除 create table publish( id ... ); create table book( id ... publish_id int, foreign key(publish_id) references publish(id) on update cascade # 同步更新 on delete cascade # 同步删除 ); # 多对多 图书和作者 先站在图书表 一本书可不可以有多个作者 可以！！！ 再站在作者表 一个作者能不能写多本书 可以！！！ 结论：图书和作者是双向的一对多 那么表关系就是 多对多 一定要单独开设一张新的表存储表关系 create table book( id ... ) create table author( id ... ) create table book2author( id ... book_id int, author_id int, foreign key(book_id) references book(id) on update cascade # 同步更新 on delete cascade, # 同步删除 foreign key(author_id) references author(id) on update cascade # 同步更新 on delete cascade, # 同步删除 ) # 一对一 qq用户表 客户与学生表 """ 当你一张表中的数据并不都是频率需要用到的情况 但是字段有特别的多 那么这个时候你就应该考虑分表 然后做一对一的关联 节省查询时间和传输时间 """ 作者与作者详情 无论站在哪一方都不能成立一对多 双方一对多都不成立那么表关系 一对一 没有关系 create table author( id ... authordetail_id int unique, foreign key(authordetail_id) references authordetail(id) on update cascade # 同步更新 on delete cascade, # 同步删除 ); create tabel authordetail( id ... );补充
""" 表与表之间如果有关系的话 可以有两种建立联系的方式 1.就是通过外键强制性的建立关系 2.就是自己通过sql语句逻辑层面上建立关系 delete from emp where id=1; delete from dep where id=1; 创建外键会消耗一定的资源 并且增加了表与表之间的耦合度 在实际项目中 如果表特别多 其实可以不做任何外键处理 直接 通过sql语句来建立逻辑层面上的关系 到底用不用外键取决于实际项目需求 """ -
修改表
""" alter table t1 rename new_t1; alter table t1 add 字段名 ...; alter table t1 add 字段名 ... first; alter table t1 add 字段名 ... after 旧字段; alter table t1 drop 字段名; modify一般都是用来修改字段的字段类型 约束条件等 不能修改字段名 alter table t1 modify 字段名 数据类型(宽度) 约束条件 alter table t1 change 旧字段名 新字段名 ...; """ -
复制表
""" 我们sql语句查询出来的结果也可以看成是一张表(虚拟表) 言外之意 就是针对这个查询结果还可以继续用查询表的语法继续操作该虚拟表 """ -
作业
""" 自己联系表关系的判断与创建 笔记里面给的代码只是一个参考而已 """
今日内容概要
-
如何查询表
""" select where group by having distinct order by limit regexp like ... """ -
连表操作理论
今日内容详细
前期表准备
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;
# 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK
几个重要关键字的执行顺序
# 书写顺序
select id,name from emp where id > 3;
# 执行顺序
from
where
select
"""
虽然执行顺序和书写顺序不一致 你在写sql语句的时候可能不知道怎么写
你就按照书写顺序的方式写sql
select * 先用*号占位
之后去补全后面的sql语句
最后将*号替换后你想要的具体字段
明天会一直使用 这里先理解
"""
where筛选条件
# 作用:是对整体数据的一个筛选操作
# 1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name from emp where id between 3 and 6; 两者等价
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
like
% 匹配任意多个字符
_ 匹配任意单个字符
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的 姓名和薪资 char_length() _
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# 5.查询id小于3或者id大于6的数据
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工姓名和岗位名 针对null不用等号 用is
select name,post from emp where post_comment = NULL;
select name,post from emp where post_comment is NULL;
group by分组
# 分组实际应用场景 分组应用场景非常的多
男女比例
部门平均薪资
部门秃头率
国家之间数据统计
# 1 按照部门分组
select * from emp group by post;
"""
分组之后 最小可操作单位应该是组 还不再是组内的单个数据
上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)
如果设置了严格模式 那么上述命令会直接报错
"""
set global sql_mode = 'strict_trans_tables,only_full_group_by';
设置严格模式之后 分组 默认只能拿到分组的依据
select post from emp group by post;
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)
"""
什么时候需要分组啊？？？
关键字
每个 平均 最高 最低
聚合函数
max
min
sum
count
avg
"""
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱
# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的人数
select post,count(id) from emp group by post; # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post; null不行
# 6.查询分组之后的部门名称和每个部门下所有的员工姓名
# group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用
select concat('NAME:',name),concat('SAL:',salary) from emp;
# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; 报错
select t1.id,t1.name from emp as t1;
# 查询每个人的年薪 12薪
select name,salary*12 from emp;
分组注意事项
# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000;
select max(salary) from emp; # 不分组 默认整体就是一组
# 统计各部门年龄在30岁以上的员工平均薪资
1 先求所有年龄大于30岁的员工
select * from emp where age>30;
2 再对结果进行分组
select * from emp where age>30 group by post;
select post,avg(salary) from emp where age>30 group by post;
having分组之后的筛选条件
"""
having的语法根where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) > 10000
;
distinct去重
"""
一定要注意 必须是完全一样的数据才可以去重！！！
一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
ORM 对象关系映射 让不懂SQL语句的人也能够非常牛逼的操作数据库
表 类
一条条的数据 对象
字段对应的值 对象的属性
你再写类 就意味着在创建表
用类生成对象 就意味着再创建数据
对象点属性 就是在获取数据字段对应的值
目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL
"""
select distinct id,age from emp;
select distinct age from emp;
order by排序
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
"""
order by默认是升序 asc 该asc可以省略不写
也可以修改为降序 desc
"""
select * from emp order by age desc,salary asc;
# 先按照age降序排 如果碰到age相同 则再按照salary升序排
# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
select post,avg(salary) from emp
where age>10
group by post
having avg(salary) > 1000
order by avg(salary) desc
;
limit限制展示条数
select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3; # 只展示三条数据
select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置
第二个参数是展示条数
正则
select * from emp where name regexp '^j.*(n|y)$';
多表操作
前期表准备
#建表
create table dep(
id int,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
表查询
select * from dep,emp; # 结果 笛卡尔积
"""
了解即可 不知道也没关系
"""
select * from emp,dep where emp.dep_id = dep.id;
"""
MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作
所以特地给你开设了对应的方法
inner join 内连接
left join 左连接
right join 右连接
union 全连接
"""
# inner join 内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 只拼接两张表中公有的数据部分
# left join 左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左表所有的数据都展示出来 没有对应的项就用NULL
# right join 右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右表所有的数据都展示出来 没有对应的项就用NULL
# union 全连接 左右两表所有的数据都展示出来
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
子查询
"""
子查询就是我们平时解决问题的思路
分步骤解决问题
第一步
第二步
...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
1 先获取部门的id号
2 再去员工表里面筛选出对应的员工
select id from dep where name='技术' or name = '人力资源';
select name from emp where dep_id in (200,201);
select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');
总结
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
"""
多表查询就两种方式
先拼接表再查询
子查询 一步一步来
"""
作业
1.整理今日内容
2.完成下列分组查询练习题(以课上建表代码为参考)
1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
3.练习拼表操作并理解其意义
4.理解子查询思路体会其意义
每日测验(day48)
- MySQL中常用查询关键字及作用
- 针对多表查询有哪些查询方式，各自有什么特点
昨日内容回顾
-
查询主要关键字
select distinct 字段1,字段2,... from 表名 where 分组之前的筛选条件 group by 分组条件 having 分组之后的筛选条件 order by 排序字段1 asc,排序字段2 desc limit 5,5 -
where
where id>=3 and id<=6; where id between 3 and 6; where salary=18000 or salary=17000; where salary in (18000,17000); where salary not in (18000,17000); where salary*20 from emp; # 模糊匹配 like """ % 任意多个字符 _ 任意单个字符 """ where name like '%mode%'; where name like '____'; where char_length(name) = 4; # 针对null数据 判断的时候用is不要用= where post_comment is null; -
group by
# 分组的应用场景非常多 每个 平均 最大 最小 ... """ 分组之后只能直接获取到分组的依据 其他字段都无法直接获取 set global sql_mode = 'only_full_group_by' """ select * from emp group by post; 报错 select post from emp group by post; # group_concat:帮助我们获取到分组之外的字段信息并且可以拼接多个字段 select post,group_concat(salary,':',name) from emp; # concat:分组之前帮助我们获取字段信息并且可以拼接多个字段 select concat(name,'??') from emp; # concat_ws:如果多个字段之间的连接符号是相同的情况下 你可以直接使用concat_ws来完成 select concat_ws(':',name,age,sex) from emp; """ 复习 '?'.join([111,222,333,444]) 报错！ """ # as语法 1 可以给展示字段起别名 2 可以给表起别名 # 聚合函数 max min sum count avg 聚合函数必须在分组之后使用 -
having
# 用法根where一模一样 只不过它是作用于分组之后的再次筛选 ....group by post having avg(salary) > 30000; -
distinct
# 数据必须是一模一样的情况下才能去重 select distinct post from emp; -
order by
# 排序 默认是升序 order by salary; === order by salary asc; order by salary desc; order by salary asc,age desc; # 还支持多个字段备用比较 -
limit
""" 限制数据的展示条数 效果就是分页的效果 """ select * from emp limit 5; limit 5; limit 5,5 第一个参数是起始位置 第二个参数是条数 -
regexp
""" 正则是一门独立的语言 在python中如果你想使用正则需要借助于re模块 面试题 1.re模块中常用的方法 findall:分组优先展示 ^j.*(n|y)$ 不会展示所有正则表达式匹配到的内容 而仅仅展示括号内正则表达式匹配到的内容 match:从头匹配 search:从整体匹配 2.贪婪匹配与非贪婪匹配 正则表达式默认都是贪婪匹配的 将贪婪变成非贪婪只需要在正则表达式后面加? .* 贪婪 .*? 非贪婪 """ select * from emp where name regexp '^j.*n$' -
多表查询
# 联表操作 select * from emp,dep; 笛卡尔积 inner join 只拼接两种表中都公有的部分 select * from emp inner join dep on emp.dep_id = dep.id; # 要加上表的前缀 不然容易造成冲突 left join 左表数据全部展示 没有对应的就用NULL补全 right join 右表数据全部展示 没有对应的就用NULL补全 union 左右全书展示 没有对应的就用NULL补全 # 子查询 """ 子查询就是我们平时解决问题的思路 分步处理 将一张表的查询结果当做另外一条sql语句的查询条件 (当做条件的时候 用括号括起来) select * from emp where id in (select id from dep); """ -
总结
# 书写sql语句的时候 select后面先用*占位 之后写完再改 # 在写较为复杂的sql语句的时候 不要想着一口气写完 写一点查一点看一点再写！！！(只要是涉及到数据查询相关的语法都不应该一次性写完 不太现实) # 在做多表查询的时候 联表操作和子查询可能会结合使用
知识点补充
# 查询平均年龄在25岁以上的部门名称
"""只要是多表查询 就有两种思路 联表 子查询"""
# 联表操作
1 先拿到部门和员工表 拼接之后的结果
2 分析语义 得出需要进行分组
select dep.name from emp inner join dep
on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25
;
"""涉及到多表操作的时候 一定要加上表的前缀"""
# 子查询
select name from dep where id in
(select dep_id from emp group by dep_id
having avg(age) > 25);
# 关键字exists(了解)
只返回布尔值 True False
返回True的时候外层查询语句执行
返回False的时候外层查询语句不再执行
select * from emp where exists
(select id from dep where id>3);
select * from emp where exists
(select id from dep where id>300);
今日内容概要
- navicat可视化界面操作数据库
- 数据库查询题目讲解(多表操作)
- python如何操作MySQL(pymysql模块)
- sql注入问题
- pymysql模块增删改查数据操作
今日内容详细
Navicat软件
"""
一开始学习python的时候 下载python解释器然后直接在终端书写
pycharm能够更加方便快捷的帮助你书写python代码
excel word pdf
我们在终端操作MySQL 也没有自动提示也无法保存等等 不方便开发
Navicat内部封装了所有的操作数据库的命令
用户在使用它的时候只需要鼠标点点即可完成操作 无需书写sql语句
"""
安装
直接百度搜索 有破解版的也有非破解
非破解的有试用期 你如果不嫌麻烦 你就用使用
到期之后重新装再使用 或者破解一下也很简单
https://www.cr173.com/soft/126934.html
下载完成后是一个压缩包 直接解压 然后点击安装 有提醒直接点击next即可
navicat能够充当多个数据库的客户端
navicat图形化界面有时候反应速度较慢 你可以选择刷新或者关闭当前窗口再次打开即可
当你有一些需求该软件无法满足的时候 你就自己动手写sql
提示
"""
1 MySQL是不区分大小写的
验证码忽略大小写
内部统一转大写或者小写比较即可
upper
lower
2 MySQL建议所有的关键字写大写
3 MySQL中的注释 有两种
--
#
4 在navicat中如何快速的注释和解注释
ctrl + ？ 加注释
ctrl + ？ 基于上述操作再来一次就是解开注释
如果你的navicat版本不一致还有可能是
ctrl + shift + ？解开注释
"""
练习题
"""
课下一定要把握上课将的这几道题全部自己独立的理解并写出来
在解决sql查询问题的时候 不要慌
一步一步慢慢来 最终能够东拼西凑出来就过关了！！！
"""
-- 1、查询所有的课程的名称以及对应的任课老师姓名
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- SELECT
-- student.sname,
-- t1.avg_num
-- FROM
-- student
-- INNER JOIN (
-- SELECT
-- score.student_id,
-- avg( num ) AS avg_num
-- FROM
-- score
-- INNER JOIN student ON score.student_id = student.sid
-- GROUP BY
-- score.student_id
-- HAVING
-- AVG( num ) > 80
-- ) AS t1 ON student.sid = t1.student_id;
-- 7、 查询没有报李平老师课的学生姓名
# 分步操作
# 1 先找到李平老师教授的课程id
# 2 再找所有报了李平老师课程的学生id
# 3 之后去学生表里面取反 就可以获取到没有报李平老师课程的学生姓名
-- SELECT
-- student.sname
-- FROM
-- student
-- WHERE
-- sid NOT IN (
-- SELECT DISTINCT
-- score.student_id
-- FROM
-- score
-- WHERE
-- score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
-- );
-- 8、 查询没有同时选修物理课程和体育课程的学生姓名
-- (只要选了一门的 选了两门和没有选的都不要)
# 1 先查物理和体育课程的id
# 2 再去获取所有选了物理和体育的学生数据
# 3 按照学生分组 利用聚合函数count筛选出只选了一门的学生id
# 4 依旧id获取学生姓名
-- SELECT
-- student.sname
-- FROM
-- student
-- WHERE
-- student.sid IN (
-- SELECT
-- score.student_id
-- FROM
-- score
-- WHERE
-- score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) )
-- GROUP BY
-- score.student_id
-- HAVING
-- COUNT( score.course_id ) = 1
-- );
-- 9、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1 先筛选出所有分数小于60的数据
# 2 按照学生分组 对数据进行计数获取大于等于2的数据
SELECT
class.caption,
student.sname
FROM
class
INNER JOIN student ON class.cid = student.class_id
WHERE
student.sid IN (
SELECT
score.student_id
FROM
score
WHERE
score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2
);
pymysql模块
"""
支持python代码操作数据库MySQL
"""
pip3 install pymysql
sql注入
"""
利用一些语法的特性 书写一些特点的语句实现固定的语法
MySQL利用的是MySQL的注释语法
select * from user where name='jason' -- jhsadklsajdkla' and password=''
select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password=''
"""
日常生活中很多软件在注册的时候都不能含有特殊符号
因为怕你构造出特定的语句入侵数据库 不安全
# 敏感的数据不要自己做拼接 交给execute帮你拼接即可
# 结合数据库完成一个用户的登录功能？
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '123456',
database = 'day48',
charset = 'utf8' # 编码千万不要加-
) # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('>>>:')
password = input('>>>:')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
print(sql)
rows = cursor.execute(sql,(username,password)) # 自动识别sql里面的%s用后面元组里面的数据替换
if rows:
print('登录成功')
print(cursor.fetchall())
else:
print('用户名密码错误')
作业布置
"""
1 navicat自己玩一玩
2 练习题一定要搞懂 照着我的思路一遍遍的看敲
3 熟悉pymysql的使用
4 sql注入产生的原因和解决方法 了解
5 思考:如何结合mysql实现用户的注册和登录功能？
"""
1、pymysql模块.py
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '123456',
database = 'day48',
charset = 'utf8' # 编码千万不要加-
) # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 产生一个游标对象(就是用来帮你执行命令的)
"""
cursor=pymysql.cursors.DictCursor将查询结果以字典的形式返回
"""
sql = 'select * from teacher;'
res = cursor.execute(sql)
# print(res) # execute返回的是你当前sql语句所影响的行数 改返回值一般不用
# 获取命令执行的查询结果
# print(cursor.fetchone()) # 只拿一条
# print(cursor.fetchall()) # 拿所有
# print(cursor.fetchmany(2)) # 可以指定拿几条
print(cursor.fetchone())
print(cursor.fetchone()) # 读取数据类似于文件光标的移动
# cursor.scroll(1,'relative') # 相对于光标所在的位置继续往后移动1位
cursor.scroll(1,'absolute') # 相对于数据的开头往后继续移动1位
print(cursor.fetchall())
2、sql注入问题.py
# 结合数据库完成一个用户的登录功能？
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '123456',
database = 'day48',
charset = 'utf8' # 编码千万不要加-
) # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('>>>:')
password = input('>>>:')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
print(sql)
rows = cursor.execute(sql,(username,password)) # 自动识别sql里面的%s用后面元组里面的数据替换
if rows:
print('登录成功')
print(cursor.fetchall())
else:
print('用户名密码错误')
每日测验(day49)
-
默写pymysql模块操作MySQL的代码及主要方法
-
什么是sql注入，如何避免
(ps:sql注入有很多种类型，我们只是介绍了其中最具代表性的一种来诠释，感兴趣的可以自己再去百度查看完整的sql注入种类及解决方式)
昨日内容回顾
-
Navicat软件的使用
""" 作为开发 我们使用软件的频率较高 一般不用直接书写sql语句 快速完成库 表的创建 等等... """ -
数据库练习题
""" 1 面对复杂的查询题目的时候 不要捉急 sql语句不要想着一口气写完 分步书写 2 只要是涉及到多表查询的时候 肯定用到联表操作和子查询 联表操作 你可以无限制的联N多张表 将两张表的结果起别名 变成了一张表 之后再去另外一张表拼接再起别名 再去拼接... 3 一定要把昨天上课将的练习题自己能够理解并敲出来 """ -
pymysql模块
# 直接安装 无需考虑版本 pip3 install pymysql import pymysql conn = pymysql.connect( host = '127.0.0.1', port = 3306 user = 'root', password = '123456', # 还可以简写passwd = '123456' database = 'db666', # 还可以简写db = 'db666' charset = 'utf8' # 千万不要加横杆 ) # cursor = conn.cursor() # 括号内不加参数的话 那么查询出来的数据是元组的形式 数据不够明确 容易混乱 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 字典形式返回数据 数据有具体的描述信息 更加的合理方便 sql = 'select * from user' affect_rows = cursor.execute(sql) # 返回值是当前sql语句执行的受影响的行数 cursor.fetchone() # 只能结果的一条 数据本身 cursor.fetchall() # 拿所有 列表套多个数据 cursor.fetchmany(n) # 指定获取几条 """ 上述三个方法在读取数据的时候有一个类似于文件指针的特点 """ cursor.scroll(1,'relative') # 相对于光标所在的当前位置往后移动 cursor.scroll(1,'absolute') # 相对于数据开头往后移动 -
sql注入
""" 就是利用一些特殊字符 结合软件固定的一些语句句式 非法侵入并违规操作 利用MySQL注释的语法 造成了sql注入的问题 用户不需要属于用户名和密码也能够登录并且获取到整个用户表的数据 日常应用软件在获取用户输入的内容时 都会限制一些特殊符号的输入 如何解决上述问题？ 所有敏感的信息不要自己去做拼接操作 交互固定的模块帮你去过滤数据防止sql注入 在pymysql中 execute就能够帮你过滤 """ # sql注入 sql = "select * from user where username='%s' and password='%s'"%(username,password) # 不要自己拼接 sql = "select * from user where username=%s and password=%s" execute(sql,(username,password)) # 只能识别%s
pymysql补充
# 1.针对增删改 pymysql需要二次确认才能真正的操作数据
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
passwd = '123456',
db = 'day48',
charset = 'utf8',
autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 增
sql = 'insert into user(name,password) values(%s,%s)'
# rows = cursor.execute(sql,('jackson',123))
rows = cursor.executemany(sql,[('xxx',123),('ooo',123),('yyy',123)])
print(rows)
# conn.commit() # 确认
# 修改
# sql = 'update user set name="jasonNB" where id=1'
# rows = cursor.execute(sql)
# print(rows)
# conn.commit() # 确认
# 删除
sql = 'delete from user where id=7'
rows = cursor.execute(sql)
print(rows)
conn.commit() # 确认
# 查
# sql = 'select * from user'
# cursor.execute(sql)
# print(cursor.fetchall())
"""
增删改查中
删改增它们的操作设计到数据的修改
需要二次确认
"""
# 还可以一次性插入N多条数据
rows = cursor.executemany(sql,[('xxx',123),('ooo',123)])
今日内容概要
今日内容基本都是了解知识点，作为一名开发人员你在工作中基本用不到
- 视图(了解)
- 触发器(了解)
- 事务(需要掌握)
- 存储过程(了解)
- 内置函数(了解)
- 流程控制(了解)
- 索引理论(了解 日后再去扩展)
今日内容详细
视图(了解)
-
什么是视图
""" 视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用 其实视图也是表 """ -
为什么要用视图
""" 如果要频繁的操作一张虚拟表(拼表组成的)，你就可以制作成视图 后续直接操作 """ -
如何操作
# 固定语法 create view 表名 as 虚拟表的查询sql语句 # 具体操作 create view teacher2course as select * from teacher INNER JOIN course on teacher.tid = course.teacher_id ; -
注意
""" 1 创建视图在硬盘上只会有表结构 没有表数据(数据还是来自于之前的表) 2 视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表 """ -
视图到底使用频率高不高呢？
""" 不高 当你创建了很多视图之后 会造成表的不好维护 """ # 总结 视图了解即可 基本不用！！！
触发器(了解)
在满足对表数据进行增、删、改的情况下，自动触发的功能
使用触发器可以帮助我们实现监控、日志…
触发器可以在六种情况下自动触发 增前 增后 删前删后 改前改后
基本语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名
for each row
begin
sql语句
end
# 具体使用 针对触发器的名字 我们通常需要做到见名知意
# 针对增
create trigger tri_before_insert_t1 before insert on t1
for each row
begin
sql语句
end
create trigger tri_after_insert_t1 after insert on t1
for each row
begin
sql语句
end
"""针对删除和修改 书写格式一致"""
ps:修改MySQL默认的语句结束符 只作用于当前窗口
delimiter $$ 将默认的结束符号由;改为$$
delimiter ;
# 案例
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表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据
NEW指代的就是一条条数据对象
"""
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
if NEW.success = 'no' then
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ;
# 朝cmd表插入数据
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('jason','0755','ls -l /etc',NOW(),'yes'),
('jason','0755','cat /etc/passwd',NOW(),'no'),
('jason','0755','useradd xxx',NOW(),'no'),
('jason','0755','ps aux',NOW(),'yes');
# 删除触发器
drop trigger tri_after_insert_cmd;
事务
-
什么是事务
""" 开启一个事务可以包含多条sql语句 这些sql语句要么同时成功 要么一个都别想成功 称之为事务的原子性 """ -
事务的作用
""" 保证了对数据操作的安全性 """ eg:还钱的例子 egon用银行卡给我的支付宝转账1000 1 将egon银行卡账户的数据减1000块 2 将jason支付宝账户的数据加1000块 你在操作多条数据的时候可能会出现某几条操作不成功的情况 -
事务的四大特性
""" ACID A:原子性 一个事务是一个不可分割的单位，事务中包含的诸多操作 要么同时成功要么同时失败 C:一致性 事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态 一致性跟原子性是密切相关的 I:隔离性 一个事务的执行不能被其他事务干扰 （即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰的） D:持久性 也叫"永久性" 一个事务一旦提交成功执行成功 那么它对数据库中数据的修改应该是永久的 接下来的其他操作或者故障不应该对其有任何的影响 """ -
如何使用事务
# 事务相关的关键字 # 1 开启事务 start transaction; # 2 回滚(回到事务执行之前的状态) rollback; # 3 确认(确认之后就无法回滚了) commit; """模拟转账功能""" create table user( id int primary key auto_increment, name char(16), balance int ); insert into user(name,balance) values ('jason',1000), ('egon',1000), ('tank',1000); # 1 先开启事务 start transaction; # 2 多条sql语句 update user set balance=900 where name='jason'; update user set balance=1010 where name='egon'; update user set balance=1090 where name='tank'; """ 总结 当你想让多条sql语句保持一致性 要么同时成功要么同时失败 你就应该考虑使用事务 """存储过程(了解)
存储过程就类似于python中的自定义函数
它的内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部sql语句的执行
基本使用
create procedure 存储过程的名字(形参1,形参2,...) begin sql代码 end # 调用 call 存储过程的名字();三种开发模型
第一种
""" 应用程序:程序员写代码开发 MySQL:提前编写好存储过程，供应用程序调用 好处:开发效率提升了 执行效率也上去了 缺点:考虑到认为元素、跨部门沟通的问题 后续的存储过程的扩展性差 """第二种
""" 应用程序:程序员写代码开发之外 设计到数据库操作也自己动手写 优点:扩展性很高 缺点: 开发效率降低 编写sql语句太过繁琐 而且后续还需要考虑sql优化的问题 """第三种
""" 应用程序:只写程序代码 不写sql语句 基于别人写好的操作MySQL的python框架直接调用操作即可 ORM框架 优点:开发效率比上面两种情况都要高 缺点:语句的扩展性差 可能会出现效率低下的问题 """第一种基本不用。一般都是第三种，出现效率问题再动手写sql
-
存储过程具体演示
delimiter $$ create procedure p1( in m int, # 只进不出 m不能返回出去 in n int, out res int # 该形参可以返回出去 ) begin select tname from teacher where tid>m and tid<n; set res=666; # 将res变量修改 用来标识当前的存储过程代码确实执行了 end $$ delimiter ; # 针对形参res 不能直接传数据 应该传一个变量名 # 定义变量 set @ret = 10; # 查看变量对应的值 select @ret;在pymysql模块中如何调用存储过程呢？
import pymysql conn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'day48', charset = 'utf8', autocommit = True ) cursor = conn.cursor(pymysql.cursors.DictCursor) # 调用存储过程 cursor.callproc('p1',(1,5,10)) """ @_p1_0=1 @_p1_1=5 @_p1_2=10 """ # print(cursor.fetchall()) cursor.execute('select @_p1_2;') print(cursor.fetchall())函数
跟存储过程是有区别的，存储过程是自定义函数，函数就类似于是内置函数
('jason','0755','ls -l /etc',NOW(),'yes') CREATE TABLE blog ( id INT PRIMARY KEY auto_increment, NAME CHAR (32), sub_time datetime ); INSERT INTO blog (NAME, sub_time) VALUES ('第1篇','2015-03-01 11:31:21'), ('第2篇','2015-03-11 16:31:21'), ('第3篇','2016-07-01 10:21:31'), ('第4篇','2016-07-22 09:23:21'), ('第5篇','2016-07-23 10:11:11'), ('第6篇','2016-07-25 11:21:31'), ('第7篇','2017-03-01 15:33:21'), ('第8篇','2017-03-01 17:32:21'), ('第9篇','2017-03-01 18:31:21'); select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');流程控制(了解)
# if判断 delimiter // CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END // delimiter ; # while循环 delimiter // CREATE PROCEDURE proc_while () BEGIN DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT num ; SET num = num + 1 ; END WHILE ;索引
ps:数据都是存在与硬盘上的，查询数据不可避免的需要进行IO操作
索引:就是一种数据结构，类似于书的目录。意味着以后在查询数据的应该先找目录再找数据，而不是一页一页的翻书，从而提升查询速度降低IO操作
索引在MySQL中也叫“键”,是存储引擎用于快速查找记录的一种数据结构
- primary key
- unique key
- index key
注意foreign key不是用来加速查询用的，不在我们的而研究范围之内
上面的三种key，前面两种除了可以增加查询速度之外各自还具有约束条件，而最后一种index key没有任何的约束条件，只是用来帮助你快速查询数据
本质
通过不断的缩小想要的数据范围筛选出最终的结果，同时将随机事件(一页一页的翻)
变成顺序事件(先找目录、找数据)
也就是说有了索引机制，我们可以总是用一种固定的方式查找数据
一张表中可以有多个索引(多个目录)
索引虽然能够帮助你加快查询速度但是也有缺点
""" 1 当表中有大量数据存在的前提下 创建索引速度会很慢 2 在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低 """ 索引不要随意的创建！！！b+树
""" 只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据 仅仅是用来指路的 树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步) 一个磁盘块存储是有限制的 为什么建议你将id字段作为索引 占得空间少 一个磁盘块能够存储的数据多 那么久降低了树的高度 从而减少查询次数 """聚集索引(primary key)
""" 聚集索引指的就是主键 Innodb 只有两个文件 直接将主键存放在了idb表中 MyIsam 三个文件 单独将索引存在一个文件 """辅助索引(unique,index)
查询数据的时候不可能一直使用到主键，也有可能会用到name,password等其他字段
那么这个时候你是没有办法利用聚集索引。这个时候你就可以根据情况给其他字段设置辅助索引(也是一个b+树）
""" 叶子节点存放的是数据对应的主键值 先按照辅助索引拿到数据的主键值 之后还是需要去主键的聚集索引里面查询数据 """覆盖索引
在辅助索引的叶子节点就已经拿到了需要的数据
# 给name设置辅助索引 select name from user where name='jason'; # 非覆盖索引 select age from user where name='jason';测试索引是否有效的代码
感兴趣就自己试一试 不感兴趣直接忽略
**准备** ```mysql #1. 准备表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 创建存储过程，实现批量插入记录 delimiter $$ #声明存储过程的结束符号为$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy')); set i=i+1; end while; END$$ #$$结束 delimiter ; #重新声明分号为结束符号 #3. 查看存储过程 show create procedure auto_insert1\G #4. 调用存储过程 call auto_insert1(); ``` ```mysql # 表没有任何索引的情况下 select * from s1 where id=30000; # 避免打印带来的时间损耗 select count(id) from s1 where id = 30000; select count(id) from s1 where id = 1; # 给id做一个主键 alter table s1 add primary key(id); # 速度很慢 select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级 select count(id) from s1 where name = 'jason' # 速度仍然很慢 """ 范围问题 """ # 并不是加了索引，以后查询的时候按照这个字段速度就一定快 select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多 select count(id) from s1 where id >1 and id < 3; select count(id) from s1 where id > 1 and id < 10000; select count(id) from s1 where id != 3; alter table s1 drop primary key; # 删除主键 单独再来研究name字段 select count(id) from s1 where name = 'jason'; # 又慢了 create index idx_name on s1(name); # 给s1表的name字段创建索引 select count(id) from s1 where name = 'jason' # 仍然很慢！！！ """ 再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分 那这个树其实就建成了“一根棍子” """ select count(id) from s1 where name = 'xxx'; # 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了 select count(id) from s1 where name like 'xxx'; select count(id) from s1 where name like 'xxx%'; select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性 # 区分度低的字段不能建索引 drop index idx_name on s1; # 给id字段建普通的索引 create index idx_id on s1(id); select count(id) from s1 where id = 3; # 快了 select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算 drop index idx_id on s1; select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件 create index idx_name on s1(name); select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速 drop index idx_name on s1; # 给name，gender这种区分度不高的字段加上索引并不难加快查询速度 create index idx_id on s1(id); select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段 drop index idx_id on s1 create index idx_email on s1(email); select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉 ``` #### 联合索引 ```mysql select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 如果上述四个字段区分度都很高，那给谁建都能加速查询 # 给email加然而不用email字段 select count(id) from s1 where name='jason' and gender = 'male' and id > 3; # 给name加然而不用name字段 select count(id) from s1 where gender = 'male' and id > 3; # 给gender加然而不用gender字段 select count(id) from s1 where id > 3; # 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间 create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快 ``` 总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了 慢查询日志 设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！
作业
-
复习整理数据相关所有知识点
-
整理今日内容相关博客
-
练习数据库查询题目:https://www.cnblogs.com/Dominic-Ji/p/10875493.html
能做几道做几道，不强制要求。掌握课上讲的题目解决思路即可
1、pymysql补充.py
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
passwd = '123456',
db = 'day48',
charset = 'utf8',
autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 增
sql = 'insert into user(name,password) values(%s,%s)'
# rows = cursor.execute(sql,('jackson',123))
rows = cursor.executemany(sql,[('xxx',123),('ooo',123),('yyy',123)])
print(rows)
# conn.commit() # 确认
# 修改
# sql = 'update user set name="jasonNB" where id=1'
# rows = cursor.execute(sql)
# print(rows)
# conn.commit() # 确认
# 删除
sql = 'delete from user where id=7'
rows = cursor.execute(sql)
print(rows)
conn.commit() # 确认
# 查
# sql = 'select * from user'
# cursor.execute(sql)
# print(cursor.fetchall())
"""
增删改查中
删改增它们的操作设计到数据的修改
需要二次确认
"""
2、存储过程.py
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
passwd = '123456',
db = 'day48',
charset = 'utf8',
autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1',(1,5,10))
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchall())
K8S/Kubernetes社区为您提供最前沿的新闻资讯和知识内容
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)