mysql for python

mysql for python

mysql

数据库是一种按照数据结构来组织、存储和管理数据的仓库,一般数据库软件均为C/S架构,目前数据库软件分为两种,关系型数据库与非关系型数据,常见的关系型数据库有:mysql(mariadb),oracle,pgsql等,非关系型数据库有mongodb,redis(也是消息队列)。

mysql是目前互联网公司中最常用的关系型数据库之一。

存储引擎

日常生活中文件格式有很多中,并且针对不同的文件格式会有对应不同存储方式和处理机制(txt,pdf,word,mp4…)

针对不同的数据应该有对应的不同的处理机制来存储

存储引擎就是不同的处理机制

MySQL主要存储引擎

  • Innodb

    是MySQL5.5版本及之后默认的存储引擎

    存储数据更加的安全

  • myisam

    是MySQL5.5版本之前默认的存储引擎

    速度要比Innodb更快 但是我们更加注重的是数据的安全

  • memory

    内存引擎(数据全部存放在内存中) 断电数据丢失

  • blackhole

    无论存什么,都立刻消失(黑洞)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
"""
# 查看所有的存储引擎
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);
"""

SQL语言

DQL:数据查询语言,用于对数据进行查询,如select
DML:数据操作语言,对数据进行增加、修改、删除,如insert、update、delete
TPL:事务处理语言,对事务进行处理,包括begin transaction、commit、rollback
DCL:数据控制语言,进行授权与权限回收,如grant、revoke
DDL:数据定义语言,进行数据库、表的管理等,如create、drop

数据类型

整型

  • 分类

    TINYINT SMALLINT MEDUIMINT INT BIGINT

  • 作用

    存储年龄、等级、id、号码等等

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
"""
以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位

# 总结:
针对整型字段 括号内无需指定宽度 因为它默认的宽度以及足够显示所有的数据了

浮点型

  • 分类

    FLOAT、DOUBLE、DECIMAL

  • 作用

    身高、体重、薪资

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    # 存储限制
    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
    # 要结合实际应用场景 三者都能使用

字符类型

  • 分类

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    """
    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对比

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    """
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    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');

枚举与集合类型

  • 分类

    1
    2
    3
    4
    """
    枚举(enum) 多选一
    集合(set) 多选多
    """
  • 具体使用

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    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','生蚝'); 报错
    # 集合可以只写一个 但是不能写没有列举的

    总结

    1
    2
    3
    4
    5
    6
    7
    8
    9
    """
    字段类型
    严格模式

    约束条件
    not null
    zerofill
    unsigned
    """

约束条件

default默认值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 补充知识点  插入数据的时候可以指定字段
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唯一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 单列唯一
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
"""
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自增

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 当编号特别多的时候 人为的去维护太麻烦
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

结论

1
2
3
4
"""
以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment
"""

补充

1
2
3
delete from t1  删除表中数据后 主键的自增不会停止

truncate t1 清空表数据并且重置主键

表与表之间建关系

1
2
3
4
5
6
7
8
9
10
11
"""
定义一张员工表 表中有很多字段
id name gender dep_name dep_desc
"""
# 1 该表的组织结构不是很清晰(可忽视)
# 2 浪费硬盘空间(可忽视)
# 3 数据的扩展性极差(无法忽视的)

# 如何优化?
"""上述问题就类似于你将所有的代码都写在了一个py文件中"""
将员工表拆分 员工表和部门表

外键

1
2
3
4
5
"""
外键就是用来帮助我们建立表与表之间关系的,对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证,数据如果不合法则更新和插入会失败,保证数据的有效性
添加外键约束: alter table 从表 add foreign key(外键字段) references 主表(主键字段);
删除外键约束: alter table 表名 drop foreign key 外键名;
"""

表关系

1
2
3
4
5
6
7
8
9
"""
表与表之间最多只有四种关系
一对多关系
在MySQL的关系中没有多对一一说
一对多 多对一 都叫一对多!!!
多对多关系
一对一关系
没有关系
"""

一对多关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
"""
判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议
换位思考 分别站在两张表的角度考虑

员工表与部门表为例
先站在员工表
思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
不能!!!
(不能直接得出结论 一定要两张表都考虑完全)
再站在部门表
思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
能!!!
得出结论
员工表与部门表示单向的一对多
所以表关系就是一对多
"""
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);

多对多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
"""
图书表和作者表
"""
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 # 同步删除
);

一对一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
"""
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 # 同步删除
)

总结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
"""
表关系的建立需要用到foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三张存储
一对一
建在任意一方都可以 但是推荐你建在查询频率较高的表中

判断表之间关系的方式
换位思考!!!
员工与部门

图书与作者

作者与作者详情
"""

表操作

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# 语法
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);

"""
宽度和约束条件到底是什么关系
宽度是用来限制数据的存储
约束条件是在宽度的基础之上增加的额外的约束
"""

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 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 旧字段名 新字段名 字段类型(宽度) 约束条件;

"""

复制表

1
2
3
4
5
6
"""
我们sql语句查询的结果其实也是一张虚拟表
"""
create table 表名 select * from 旧表; 不能复制主键 外键 ...

create table new_dep2 select * from dep where id>3;

表查询操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
select 查询语句
select 字段名 from 表名
as :给字段名或表取别名,例如
select * from studentu as a
distinct:distinct可以去除重复数据行。
select distinct1,... from 表名;
where:条件查询
select * from 表名 where 条件;
order by : 排序
select * from 表名 order by1 asc|desc [,列2 asc|desc,...]
例:
select * from students where gender=1 and is_delete=0 order by id desc;
asc 表示升序
desc 表示降序
limit :分页查询
select * from 表名 limit start,count
limit是分页查询关键字
start表示开始行索引,默认是0
count表示查询条数
例:查询前3行男生信息:
select * from students where gender=1 limit 0,3;
简写
select * from students where gender=1 limit 3;
聚合函数:
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值
GROUP BY: 分组查询
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP:在所有记录的最后加上一条记录,显示select查询时聚合函数的统计和计算结果
例:
-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;

group_concat(字段名): 统计每个分组指定字段的信息集合,每个信息之间使用逗号进行分割
例:
-- 根据gender字段进行分组, 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;
连接查询:
内连接查询:内连接根据连接查询条件取出两个表的 “交集”
左连接查询:左连接以左表为主根据条件查询右表数据,右表数据不存在使用null值填充。
右连接查询:右连接以右表为主根据条件查询左表数据,左表数据不存在使用null值填充。
自连接查询

内连接查询语法格式:
select 字段 from1 inner join2 on1.字段1 =2.字段2
左连接查询语法格式:
select 字段 from1 left join2 on1.字段1 =2.字段2
右连接查询语法格式:
select 字段 from1 right join2 on1.字段1 =2.字段2
自连接查询:
自连接查询就是把一张表模拟成左右两张表,然后进行连表查询。
自连接就是一种特殊的连接方式,连接的表还是本身这张表
自连接查询必须对表起别名
自连接查询的用法:
select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '山西省';
子查询:
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句,外部那个select语句则称为主查询.
主查询和子查询的关系:
子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句

严格模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 如何查看严格模式
show variables like "%mode";

模糊匹配/查询
关键字 like
%:匹配任意多个字符
_:匹配任意单个字符

# 修改严格模式
set session 只在当前窗口有效
set global 全局有效

set global sql_mode = 'STRICT_TRANS_TABLES';

修改完之后 重新进入服务端即可

前期表准备(实例)

(针对下面一直到正则的查询语句)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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

where筛选条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 作用:是对整体数据的一个筛选操作
# 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# 分组实际应用场景  分组应用场景非常的多
男女比例
部门平均薪资
部门秃头率
国家之间数据统计

# 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;

分组注意事项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 关键字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分组之后的筛选条件

1
2
3
4
5
6
7
8
9
10
11
"""
having的语法根where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) > 10000
;

distinct去重

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
"""
一定要注意 必须是完全一样的数据才可以去重!!!
一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的
[
{'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排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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限制展示条数

1
2
3
4
5
6
7
8
select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3; # 只展示三条数据

select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置
第二个参数是展示条数

正则

1
select * from emp where name regexp '^j.*(n|y)$';

多表操作

前期表准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#建表
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);

表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
"""
子查询就是我们平时解决问题的思路
分步骤解决问题
第一步
第二步
...
将一个查询语句的结果当做另外一个查询语句的条件去用
"""
# 查询部门是技术或者人力资源的员工信息
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
3
4
5
6
7
8
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一个张虚拟表根其他表关联

"""
多表查询就两种方式
先拼接表再查询
子查询 一步一步来
"""

将查询结果插入到其它表中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
例:
-- 将查询结果插入到good_cates表中
insert into good_cates(name) select cate_name from goods group by cate_name;

使用连接更新表中某个字段数据
将goods表中的分类名称更改成商品分类表中对应的分类id
-- 查看goods表中的商品分类名称对应的商品分类id
select * from goods inner join good_cates on goods.cate_name = good_cates.name;

-- 把该语句中from 后的语句理解为一张虚表
update goods g inner join good_cates gc on g.cate_name=gc.name set g.cate_name=gc.id;

更改表字段名(修改表结构)
修改表结构可以使用: alter table 语句,多个修改字段之间使用逗号分隔
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

pymsql

python调用mysql

pip3 install pymysql(用pip安装pymysql模块)

python调用mysql接口分为六步:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
1.导包
2.创建连接对象
3.获取游标,目的就是要执行sql语句
4.执行sql语句
5.关闭游标
6.关闭连接
对应代码:
导包
import pymysql
创建连接对象
pymysql.connect(参数列表)
获取游标对象
cursor =conn.cursor()
执行SQL语句
row_count = cursor.execute(sql)
获取查询结果集
result = cursor.fetchall()
将修改操作提交到数据库
conn.commit()
回滚数据
conn.rollback()
关闭游标
cursor.close()
关闭连接
conn.close()
导入 pymysql 包
import pymysql

创建连接对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
调用pymysql模块中的connect()函数来创建连接对象,代码如下:
conn=connect(参数列表)

* 参数host:连接的mysql主机,如果本机是'localhost'
* 参数port:连接的mysql主机的端口,默认是3306
* 参数user:连接的用户名
* 参数password:连接的密码
* 参数database:数据库的名称
* 参数charset:通信采用的编码方式,推荐使用utf8
连接对象操作说明:
关闭连接 conn.close()
提交数据 conn.commit()
撤销数据 conn.rollback()
获取游标对象:
获取游标对象的目标就是要执行sql语句,完成对数据库的增、删、改、查操作。代码如下:
# 调用连接对象的cursor()方法获取游标对象
cur =conn.cursor()
游标操作说明:
使用游标执行SQL语句: execute(operation [parameters ]) 执行SQL语句,返回受影响的行数,主要用于执行insertupdatedeleteselect等语句
获取查询结果集中的一条数据:cur.fetchone()返回一个元组, 如 (1,'张三')
获取查询结果集中的所有数据: cur.fetchall()返回一个元组,如((1,'张三'),(2,'李四'))
关闭游标: cur.close(),表示和数据库操作完成

代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
import pymysql
# 创建连接对象
conn = pymysql.connect(host='localhost', port=3306, user='root', password='mysql',database='python', charset='utf8')
# 获取游标对象
cursor = conn.cursor()
# 查询 SQL 语句
sql = "select * from students;"
# 执行 SQL 语句 返回值就是 SQL 语句在执行过程中影响的行数
row_count = cursor.execute(sql)
print("SQL 语句执行影响的行数%d" % row_count)
# 取出结果集中一行数据, 例如:(1, '张三')
# print(cursor.fetchone())
# 取出结果集中的所有数据, 例如:((1, '张三'), (2, '李四'), (3, '王五'))
for line in cursor.fetchall():
print(line)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
添加(修改)sql数据:
import pymysql
conn=pymysql.connect(host="10.10.10.85",
port=3306,
user="root",
password="123456",
database="python",
charset="utf8")
cursor =conn.cursor()
sql = "insert into students(name) values('张三');"
try:
row_count=cursor.execute(sql)
print("SQL 语句执行影响的行数%d" % row_count)
conn.commit()
except Exception as e:
conn.rollback()
finally:
cursor.close()
conn.close()

防止SQL注入

1
2
3
4
5
6
7
	什么是SQL注入?
​ 用户提交带有恶意的数据与SQL语句进行字符串方式的拼接,从而影响了SQL语句的语义,最终产生数据泄露的现象。
​ 如何防止SQL注入?
SQL语句参数化
SQL语言中的参数使用%s来占位,此处不是python中的字符串格式化操作
​ 将SQL语句中%s占位所需要的参数存在一个列表中,把参数列表传递给execute方法中第二个参数

防止SQL注入的示例代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
"""
利用一些语法的特性 书写一些特点的语句实现固定的语法
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('用户名密码错误')

这里防止sql注入的主要方法是这句,rows = cursor.execute(sql,(username,password)) ,用execute自带的拼接方式来做防止注入。


本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!