mysql+pymysql(python) -> 新手引导笔记
mysql笔记
https://www.cnblogs.com/linhaifeng/p/7278389.html
配置文件
将mysql设置成系统服务
将环境变量设置好后直接mysqld --install 如果提示被占有直接可以启动mysql客户端
net start mysql
重新 打开一个命令行
登录mysq
mysql -u root -p
密码
密码的设置
mysqladmin -uroot password "123"
重置密码
跳过授权表启动mysql
关闭mysql服务
net stop mysql
跳过授权表
mysqld --skip-grant-tables 此时程序夯住就可以操作了
在程序打开mysql登录mysql 重新设置密码
update mysql.user set password=password('mysql456')
where user='root' and host='localhost';
#查看mysql进程的pid
tasklist |findstr mysql
#杀死进程(如果不杀死进程就会一直跳过授权表)
taskkill /F /PID 进程号
最后重启mysql
就好了
统一字符编码
在mysql目录下创建配置文件
my.ini输入以下
[mysqld]
character-set-server=utf8
collation-server=utf8-general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
重启mysql服务
就设置好了
库操作:{
增:{
CREATE DATABASE db2 CHARSET utf8;
}
删:{
DROP DATABASE db2; 删除库
}
改:{
ALTER DATABASE db2 CHARSET gbk; 修改编码
}
查:{
SHOW DATABASES; 查看所有的库
SHOW CREATE DATABASE db2; 查看库信息
}
}:
表操作::{
存储引擎{
存储引擎是存储数据表时的存储方式(硬盘物理存储 运行在内存的)
InnoDB 常用 在硬盘上有表结构 表数据的体现
memory 运行在内存 没有表数据体现
方法
查看支持的存储引擎
SHOW ENGINES;
show engines;
指定存储引擎 在表创建时
CREATE TABLE tb1(id int, name char(25)) engines="innodb";
增:{
CREATE TABLE tb2(
id int,
name char(5)
)
}
删:{
DROP TABLE tb2 删除表
}
改:{
ALTE R TABLE 表名 RENAME 新表名;
}
查:{
DESC tb1; 查看表结构
SHOW TABLES ; 查看数据库中所有的表
SHOW CREATE TABLE tb_name; 查看数据表的信息
}
···修改表结构{
·添加字段
默认最后 面添加字段
ALTER TABLE ADD 字段名 字段类型;
放到最前面
ALTER TABLE ADD 字段名 字段类型 FIRST;
放到某一个字段后面
ALTER TABLE ADD 字段名 字段类型 AFTER 放到某一个字段后面的字段名;
·删除字段
ALTER TABLE 表名 DROP 字段名;
·修改字段类型
ALTER TBALE t2 MODIFY 字段名， 字段类型
··修改该字段名和类型
ALTER TABLE t2 CHANGE id phone int(11);{
·ALTER TABLE t2 CHANGE id phone int(11);
·
}
ALTER TABLE t2 CHANGE 旧字段名 新字段名 新字段类型;
ALTER TABLE t2 CHANGE 旧字段名 新字段名 旧字段类型;
· 查看字段
DESC 表名
查看表信息
SHOW CREATE TABLE tb1;
}
复制表{
复制表结构和数据
将
查询到的数据 SELECT * FROM db1.tb1
传入新表(创建新表) CREATE TABLE tb1
中
CREATE TABLE tb1 select * from db1.tb1;
复制表结构
想法办获取不到数据
就可以只获取结构
我给一个永远不可能成立的条件
CREATE TABLE tb1 select * from db1.tb1 WHERE 1>5;
此操作不可拿到数据
CREATE TABLE tb1 like db1.tb1;
}
表的数据类型{
整数类型
int 不用添加宽度 添加宽度也是占用4字节 如果添加 只是查询到的时候的显示宽度
对于整型类型 int(这里面的宽度 是显示宽度 不是存储宽度)
除此之外其他数据类型就是存储宽度
unsigned 无符号
zerofill 用0填充 (用来查看显示宽度)
有符号
tinyint 1字节 -128,127
smallint 2字节 -32768， 32767
int 4字节 -2 147 483 648,2 147 483 647
bigint 8字节 -9 233 372 036 854 775 808
无符号
tinyint 1字节 0,255
smallint 2字节 0,65535
int 4字节 0,2 147 483 648*2 - 1
bigint 8字节 0，9 233 372 036 854 775 808*2 -1
浮点类型
float 4字节
float(m,d)
m整数 总共多少位数的最大值255位整数
d小数 最大值30位小数
精准度：随着小数的增加变得不精准
double 8字节
m整数 总共多少位数的最大值255位整数
d小数 最大值30位小数
精准度：随着小数的增加变得不精准
decimal 字符串的体现
m整数 总共多少位数的最大值65位整数
d小数 最大值30位小数
精准度：
区别 计算的精度不一样 FLOAT
year 年
data 月
time 时分秒
datatime 年月日时分秒 100-9999年 8字节
timestamp 年月日时分秒 1970-2038年 4字节
方法
now() 现在的 年或
月或
时分秒 或
年月日时分秒
·字符类型
字符类型的宽度 指的是字符的宽度
char(定长) 不够规定的字符数量用空格补齐
读取
varchar(变长)
egonalexwxx 像这样多条数据存在一次就会不知道取多少
所以 1bytesegon1bytesalex1byteswxx
第一个存储的是单条数据的数量 最多是两个字节能够65535的长度
1bytes 用来存储数据长度
不够规定的字符数量 放入的时候多少取出多少显示多少
char和varchar的区别
char取得时候只需要取规定的字符数量
而varchar则需要先读取字符长度 所以比char要效率低
如果要存储大文件 不该直接放入数据库里面 数据库越精简越好
如果用户来访问我这台服务器 我返回一个路径 让用户去访问另一台服务器 请求文章
·集合和枚举类型
enum枚举类型
规定范围 enum("nan","nv","outher")只能在范围内传值
set集合类型
·约束条件
就是在字段类型基础之上加的一个规则
null /default
是否允许传空值？
默认是空值
设置不为空
CREATE TABLE tb1(
sex enum('male','female') not null default 'male'
# 设置的是不可为空如果为空 默认传入male
unique key 加上此约束条件 插入的数据不可重复
单列唯一
unique(id)
unique(name)
联合唯一
几个字段传入的数据合在一起不重复
联合唯一的写法与单列唯一的写法区别
单列
unique(id)
unique(name)
联合
unique(id, name)
primary key
约束条件: not null unique
存储引擎 innodb :对于innodb存储引擎来说一张表必须有一个主键
如果没有主键 存储引擎会找到一个 不为空且唯一的作为主键， 如果
找不到不为空且唯一的 会用一个隐藏主键 所以必须指定主键
单列主键
primary key 从约束角度来说 不为空且唯一
innodb的存储特点
复合
多个字段合在一起当做主键
primory key(ip, post)
auto_increment
自增长字段
自增长字段只能有一个 必须是一个key
id int primary key auto_incement
自增长是由上一条数据的数字开始自增长
比如上一次传入的id 为 7 下一条就是8
查询步长偏移量等信息
show variables like "auto_inc%";
了解知识点:
设置步长
set session auto_increment _increment=5
设置全局步长
set global auto_increment _increment=5
设置起始偏移量 （从几开始计数）
set global auto_increment_offset=6
要小于步长 否则设置无效
外键 用来建立表与表之间的联系
有时候要考虑节省数据存储空间 效率 需要用作外键 让表与表之间建立联系
建表
foreign key(dep_id) references dep(id)
dep_id 关联表的字段
id 被关联表的字段
建立关联表的时候必须把被关联的表建立出来
被关联表的字段必须唯一
插入数据
关联表的数据存入前应先往被关联表里传入数据
如果没有存入 被关联的外键不存在 插入时会报错
删除的时候应该先删关联表的数据 才可以删被关联表的数据
只操作被关联表
在建立关联表的时候
写一个
on delete cascade
on update cascade
只要被关联表的数据被删 关联表被关联的数据也会被删
表与表之间的关系
三条
多对一
多条记录对应单条记录
找表与表之间的关系
1.先找 关联表这边有没有多条记录
对应被关联表的单条记录
2.找被关联表的这边有没有多条记录 对应 关联表的单条记录
多对多
如何建立多对多?
建立一张新表 关联关系表
存储两个表与表之间关联关系
一对一
一个客户变为学生 只能变成一次需要一对一关系
客户id进入学生表进行的时候只能出现一次 所以需要加
unique key 让他只能出现一次这就是一对一
数据操作{
增
INSERT INTO 表名 VALUES(值1,值2),(值一,值二)
表内差入数据必须是和值一一对应
UPDATA 修改数据
UPDATA db1.name SET name='kyle' WHERE id=5;
删
DELETE 用来删除单条数据
DELETE FROM 表名 WHRER id=1;
清空表
truncate tb1
注:
distinct去重
SELECT distinct 字段1, 字段2,字段2 from 库.表
WHERE 条件
group by 分组条件
having 过滤
order by 排序字段
单条数据显示的条数
limit n;
单表查询
简单查询
{
SELECT * FROM tb1;
# 避免重复DISTINCT
SELECT DISTINCT name FROM tb1;
# 通过四则运算查询
SELECT name, salary*12 AS Annual_salary FROM tb1;
# 定义显示格式
CONCAT() 此函数用于连接字符串
第一个参数是字符串 2是字段名
SELECT CONCAT('姓名：', name, '年薪：',salary*12) AS Annual_salary FROM tb1;
CONCAT_WS("用这个参数作为分隔符", 字段名,字段名)
}
WHERE 条件
{
单条件查询
SELECT id,name,salary FROM id WHERE id > 4;
多条件
与 && and
或 || or
非 ! not
SELECT id,name,salary FROM tb1 WHRER id = 7 and id = 15;
SELECT id, name, salary FROM tb1 WHERE id = 7 or id = 15;
SELECT id, name, salary FROM tb1
WHERE id != 7 and id != 15;
在条件与条件之间
WHERE 字段名 between ... and ....
SELECT id, name, salary FROM tb1 WHERE id BETWEEN 5 and 100;
不在条件与条件之间
WHERE NOT 字段名 between ... and ....
查找某个字段为空的数据
WHRER is_register is Null;
查找某个字段不为空的数据
WHRER is_register is not Null;
模糊查询
like
WHERE name like "k%" % 正则的 . 差不 后面的字符不管是什么都拿来
WHERE name like "k___" _ 代表单个字符(几个下划线就是几个字符 只要条件成立就拿来)
WHERE 执行逻辑：
每次将你定义的条件 与对应字段进行比对 符合条件就交给 SELECT 打印 》》》 查找范围从第一条到最好一条
}
group by 分组条件
{
如果有where 必须是在group by 之前写
必须将mysql 的模式修改为 严格模式
严格模式
set global sql_mode="ONLY_FULL_GROUP_BY"
设置完之后不会立刻生效因为修改的是 mysql 的全局
必须重启mysql服务端
分完组之后 select 只能查到 分组之后的字段
不设置严格模式
分完组之后 select 能查到 但没有意义
select post from tb1 group by post; 分组的目的是以组为单位进行统计
聚合函数
max()最大值
min()最小值
count()统计个数
avg()平均
sum()求和
取职位中每个有多少人
SELECT post,count(id) FROM tb1 group by post;
去每个部分的平均工资
SELECT post,avg(salart) FRom tb1 group by post;
取每个部门的 加起来的工资
SELECT post,sum(salary) FROM tb1 group by post;
取每个部门的最大工资
SELECT post,max(salary) FROM tb1 group by post;
取每个部分的最小工资
SELECT post,min(salary) FROM tb1 group by post;
注意：
一定不要用unique 的字段进行分组 这种分组无任何意义
没有group by 的 时候默认为 整体为一组
group_concat 在分组后 拿到指定字段下的所有数据
·比如 拿到一个部门里的所有员工id
SELECT post, group_concat(id) FROM tb1 group by post;
拿到所有员工id
SELECT post, group_concat(name) FROM tb1 group by post;
拿到所有员工name
先WHERE 后 group by
SELECT sex,GROUP_CONCAT(id) FROM tb1 WHERE age>18 group by sex;
}
having过滤
{
分组之后过滤
与where的区别 where是在分组之前过滤
having在分组之后过滤
having 是对查到的结果进行锅炉 就不能对没有查出来的值 使用having
例子
{
统计员工薪资大于4000 小于 6000 工号大于 2 的 男女人数各有多少人 和 各组人员的名字
SELECT sex AS "性别",
count(id) AS "员工人数",
GROUP_CONCAT(name) AS "员工名称"
FROM tb1 WHERE id > 2
GROUP BY sex
-- 不可以直接用的原因是 此时已经是分组之后了 需要配合用聚合函数 HAVING salary > 4000 ;
}
}
order by 排序
{
默认是以id 的升序进行排序
SELECT * FROM db4.tb1 order by id asc # 升序
SELECT * FROM db4.tb1 order by id desc # 降序
先按salary进行升序 如过某一个字段相同则按照 id降序 排序
SELECT * FROM db4.tb1 order by salary asc, id desc,
除了这一还能 ,号分割 继续这样写
limit 限制最终打印到屏幕的条数 (几条记录)
SELECT * FROM tb1 group by sex order by salary desc limit 1 显示一条数据
分页
在浏览器分页中
请求服务端 服务端进行mysql操作 每请求一次 取几条给浏览器
SELECT * FROM tb1 limit 1, 10; -- 第一个参数 从第几条往后取 第二个参数 取几条;
正则查询
{
SELECT * FROM tb1 where name regexp ‘^j.*(g|n)$’
}
多表查询
{
将表与表有关联的一起查询出来
select * from employee,department;
直接查找两张表
内链接
只取两张表相同部分
SELECT * FROM employee inner join department on employee.dep_id = department.id;
左链接
优先保留左表的记录
SELECT * FROM employee left join department on employee.dep_id = department.id;
右链接
SELECT * FROM employee right join department on employee.dep_id = department.id;
优先保留右表的记录
全外链接
在内链接的基础上保留两张表中没有对应关系的记录
SELECT * FROM employee right join department on employee.dep_id = department.id
union
SELECT * FROM employee left join department on employee.dep_id = department.id;
关联查询
}
符合条件连接查询
并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
{
SELECT employee.age,employee.name,department.name FROM employee INNER JOIN department
on employee.dep_id = department.id
group by employee.age
having age > 25;
}
子查询
"""
1.
当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！
2.
not exists查询的效率远远高与not in查询的效率。
"""
{
将一个sql语句的返回结果的数据 用来给另一个表做查询条件
in
{
关键字
查找平均年龄在25岁以上的部门名
where id in
where id not in
SELECT * FROM department where id in
(SELECT dep_id FROM employee
group by dep_id having avg(age) > 25);
将sql括起来就是返回结果可根据返回结果进行查询
带比较运算符的子查询
WHERE salary >/
exists 查看sql 语句返回的结果是否为空
如果为空返回false 如果不为空返回 true
WHERE EXISTS
(SELECT avg(salary) FROM employee)
}
(SELECT avg(salary) FROM employee) as emp
# 还可以把一个sql语句起一个别名当做一张表拿去用
}
SELECT 语句完整的执行顺序
{
1.FROM
left_table
left/inner/right join right_table
where 过滤
group BY 分组
having 分组之后过滤
distinct 去重
order by 排序
select 拿到结果
limit 限制条数
打印
权限管理{
创建账号
本地账号
CREATE user 'egon1'@'localhost' identified by '123'; # 登录: mysql -uegon1 -p123
远程账号
CREATE user 'egon1'@'192.168.31.10' identified by '123'; # 登录: mysql -uegon1 -p123
网段限制
CREATE user 'egon2'@'192.168.31.%' identified by '123'; # 登录: mysql -uegon1 -p123
全部
CREATE user 'egon3'@'%' identified by '123'; # 登录: mysql -uegon1 -p123
控制权限控制 依次降低
"""
all权限除了不能给其他账号授权 其他权限都有
select
update
DELECT
INSERT
"""
user：*.*
grant all on *.* to 'kyle1'@'localhost'
grent
db: db1.*
对库放权
grant all on db1.* to 'kyle1'@'localhost'
清除权限
revoke select on db1.* from'kyle1'@'localhost';
tables_priv: db1.tb1 对表放权
grant select on db1.tb1 to 'kyle1'@'localhost'
清除权限
revoke select on db1.tb1 from'kyle1'@'localhost';
cloumns_priv: id,name 字段放权
grant select(id,name),update(name) on db1.tb1 to 'kyle1'@'localhost'
清除权限
revoke select on db1.tb1 from'kyle1'@'localhost';
}
navicat(图像化界面mysql操作应用)
{
下载
https://download.navicat.com.cn/download/modeleress030_cs_x64.exe
}
补充
char_length(name) 获取字符的长度
建表的时候吧定长的数据往前方 定长的往后放
一个表内应该用char就全部用char 用varchar就全部用varchar
清空表
DELETE FROM t20;
删除了数据 自增长那条记录不会被删除(上次增长到的)
此方法 是用来删除固定范围的 和 where同时存在
truncate t20;
与上个方法不同的就是自增长也会删除
此方法 用来删除表在合适不过
关联表
要考虑项目扩展
最好不要建立表与表的硬性关系的限制
最好是用应用程序里控制关联关系
mysql 执行顺序 先执行 from tb1
第二步执行 where
第三部执行 group_by
第四部执行 having
第五步执行 distinct
第六步执行 order by
第七步执行 limit
第八步 打印
pymysql 模块学习
pip install pymysql
# 第一个练习py模块连接mysql
def connect_mysql():
username = input("请输入 username: ")
password = input("请输入 password: ")
#创建链接
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='mysql456',
db='db1',
charset='utf8'
)
# 拿到游标
cursor = conn.cursor()
sql = 'SELECT * FROM tb1 WHERE name=%s and password=%s'
# 拿到 受影响的行数
rows = cursor.execute(sql, (username, password))
if rows:
print('输入正确，登陆成功')
else:
print('登陆失败, 账号不存在或密码不正确')
#
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
# 增删改
进行数据修改必须在关闭连接前进行 conn.commit()
def mysql_u_d_i():
'''
mysql增删改 操作
:return:
'''
# 创建链接
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='mysql456',
db='db1',
charset='utf8'
)
# 拿到游标
cursor = conn.cursor()
sql = 'SELECT * FROM tb1 WHERE name=%s'
# 插入单条
rows = cursor.execute(sql,'yxx')
print(rows)
# 插入多条
'''
datalist = [('yxx', '456'), ('qxx', '789')]
rows = cursor.executemany(sql, datalist)
'''
# 想要插入必须加上此句
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
一次查单个
cursor = conn.cursor();
rows = cursor.execute('select * from tb1')
cursor.fetchone() # 每次取一条
多个
cursor.fetchmany(1) # 指定取几条
cursor.fetchall() 取出所有
每取出一条数据 光标就会移动到 该数据后面
mysql移动光标
print('-' * 60)
cursor.scroll(1, 'absolute') # 1.num, 2.mode 1.从第一条开始取 跳过 num 的条数 2.将光标移动到 第num条的最后面 绝对移动
print(cursor.fetchone())
cursor.scroll(1, 'relative') # 1.num, 2.mode 相对移动 从光标当前所在位置开始往后移动num条 执行后光标在所在位置+num条后面
print(cursor.fetchone())
补充：
插入之前自增id
cursor.lastrowid
···mysql内置功能
··视图 (虚拟表)
关联表得到 虚拟表 后在虚拟表中进行数据操作
单表的时候可以修改
多表的时候不可以修改
保存创建虚拟表(在磁盘中不显示)
create view SELECT * FROM cousor inner join teacher on cousor.tid = teacher.cid as new_tb1
删
drop view view_name
改
alter view view_name as select * from user;
查
select * from view_name;
···触发器
使用触发器 可以定制对表进行[增删改]操作前后的行为 ， 触发器没有查询行为
··
增
插入前
CREATE TAIGGER tri_before_insert_tbname BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
END
插入后
CREATE TAIGGER tri_after_insert_tbname AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
END
CREATE TAIGGER ...... BEFORE DELETE ON .. FRO EACH ROW BEGIN
删除前
CREATE TAIGGER tri_before_delete_tbname BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
END
删除后
CREATE TAIGGER tri_after_delete_tbname AFTER DELETE ON tb1 FOR EACH ROW
DEGIN
END
修改
修改前
CREATE TAIGGER tri_before_update_tbname BEFORE UPDATE ON FOR EACH ROW
DEGIN
END
修改后
CREATE TAIGGER tri_after_update_tbname AFTER UPDATE ON FOR EACH ROW
DEGIN
END
delimiter // -- 声明sql语句的结束符号不用;号 用//结束
delimiter //
CREATE TAIGGER tri_after_insert_tbname AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN --NEW 新数据 OLD 旧数据
insert into errlog(err_cmd,err_time) VALUES(NEW.cmd,NEW.sub_time);
END IF ;
END//
delimiter;
···存储过程
用来封装sql命令
创建存储过程
delimiter // --指定结束方法
create procedure p3()
begin
select * from db5.teacher;
end //
-- 还原结束符号
delimiter ; //
mysql调用
call p1()
python调用
cursor.callproc('p1')
print(cursor.fetchall())
传入参数
delimiter // --指定结束方法
in
传入参数声明
int 字段名 字段类型
out
返回参数声明
out 字段名 字段类型
create procedure p5(in num1 int,out res int)
begin
select * from db5.teacher;
set res = num1
end //
-- 还原结束符号
delimiter ; //
mysql调用
set @x = 0 --相当于声明一个变量
call p1(5,@x)
SELECT @x -- 相当于查看变量名
python调用
cursor.callproc('p2',(15, 0))
print(cursor.fetchall()) # 查询数据
print() # 加一个换行
cursor.execute('select @_p5_1') # 第几个参数有返回值 就把 _p5_num num改成下标数字 p5是你存储过程的名字
print(cursor.fetchall()) #查看参数的返回值
python程序和mysql结合
方式一
python程序 调用
mysql 把sql语句封装到存储过程中
方式二
python程序 编写原生SQL
mysql
方式三
基于ORM框架 基于类的方式编写方法 编写sql ORM框架 将方法转换成MYSQL
相比方式二 运行效率要低 因为要将方法转换成sql
但开发效率高
1.
方式一比方式二的效率要高
2.
方式二比方式一的扩展性要高
人为因素造成的 在公司里跨部门协做就会把事情变得很复杂 但编写纯生sql比较慢
运行效率
方式一
方式一运行效率低 但扩展时需要跨部门协作
方式二
方式二的开发效率比较低
因为要编写纯生sql
方式三
方式三的开发效率效率比方式二低
因为存在将方法转换成sql的操作
···事务操作
双方同时执行成功后才算完成 例如 转账
原子性:
要么执行成功要么都不成功
conmmit; 结束事务
rollback; 回滚
mysql 的异常处理定义
begin
declare exit handler for sqlexception
degin
#如果出现错误 就回滚
rollback;
end;
end;
···函数流程控制
函数是用在sql语句里、
格式化时间函数
select * from bolg
group by
-- 时间字段 格式化输出的格式
date_format(sub_time,'%Y-%m')
··· 索引
··索引 primary key 'unique key
·不加过多索引 不然会 写的慢
innodb 建表的时候一定要建主键 因为innodb存储引擎是默认是找主键当做所以 如果不指定innodb引擎会找一个 不为空且唯一的字段作为主键 如果都没 就会创建一个隐藏字段
·· 辅助索引
表中除了聚集索引
辅助索引的叶子节点 只存放辅助字段里的值
创建索引
create index idx_id on s1(id);