Python MySQL 数据库技术 part 3

8.MySQL数据操作 DML

增删改

8.1 添加数据

insert into 表名[(字段列表)] values(值列表);
1） 标准添加（指定所有字段，给定所有值）
insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
–insert into user(name,age,sex) values(‘zhangsan’,20,‘male’);
2） 指定部分字段添加值
insert into 表名(字段1,字段3) values(值1,值3);
3） 不指定字段添加值
insert into 表名 values(值1，值2，值3);
此时的值不能缺少任何一个！
4） 批量添加值
insert into 表名 values(a值1,a值2,a值3),(b值1,b值2,b值3);

8.2 修改数据

update 表名 set 字段1=值1,字段2=值2 where 条件;
-字段可为一个字段或多个字段
–update users set age=33 where id=3;
–update users set age=17,sex=‘male’ where id in(5,9); 注意！
update 表名 set 字段=字段+值 where 条件;

8.3 删除数据

delete from 表名 [where 字段=值];
–delete from users where id>=10 and id<=15;
–delete from users where id between 10 and 15;

9.MySQL数据查询 DQL

select 字段列表|* from 表名
[where 搜索条件]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 排序规则]
[limit 分页参数];

9.1 基础查询

1） 查询表中所有列 所有数据
select * from 表名;
2） 指定字段列表进行查询
select 字段1,字段2 from 表名;

9.2 where 条件查询

• where子句中指定任何条件
• 使用 and 或者 or 指定一个或多个条件
• where条件可运用在 update 和 delete 语句后面
• where子句中 if 条件，根据mysql表中的字段值来进行数据的过滤
  示例：
  select * from 表名 where 字段=某个值;
  select * from users where age>20;
  select * from users where age not between 20 and 25;
  select * from users where age >=20 and age <=26 and sex =‘male’;

and 和 or 使用时注意

and 和 or 在同一条件句时，会优先处理 and
select * from users where age=20 or age=25 and sex=‘male’;
– 使用小括号来关联相同的条件
select * from users where (age=20 or age=25) and sex=‘male’;

like 子句

可以在where条件中使用=<>等符号进行条件的过滤，若想查询某个字段是否包含，
使用 like 语句 进行某个字段的模糊搜索
% 代表任意个任意字符；
_ 代表任意一个字符。
select * from users where name like ‘%n’;
select * from users where name like ‘__’; --任意两个字符
注意：
where子句中的like 在使用%_时，效率不高。
尽量不去使用，若使用，尽量不要把通配符放在开头！

9.3 MySQL中的统计函数（聚合函数）

max()，min()，count()，sum()，avg()
–求表中最大 最小 总和 平均年龄
select max(age),min(age),sum(age),avg(age) from users;
–上面数据中的列都是在查询时使用的函数名，不方便阅读和后期的调用，可以通过别名方式美化
select max(age) as max_age,min(age) as min_age,
sum(age) as sum_age,avg(age) as avg_age from users;
–统计表中的数据量

select count(*) from users;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.10 sec)
select count(id) from users;
| count(id) |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

count(*)： 按照表中所有的列进行数据统计，只要其中一列上有数据，就可以计算。
count(id)：按照指定的id字段进行统计，也可是用别的字段统计，但是
若指定的列上出现了NULL值，那么为NULL的数据不会被统计。
如，若address列有一个NULL，则 count(address) 结果为6。

聚合函数除以上简单的使用外，通常情况下都是配合分组进行统计计算。

9.4 group by 分组

根据一个或多个列对结果进行分组，一般用于 数据的统计计算，配合聚合函数使用。
–统计表中男女生人数

select sex,count(*) from users group by sex;
+--------+----------+
| sex    | count(*) |
+--------+----------+
| male   |        3 |
| female |        4 |
+--------+----------+
2 rows in set (0.00 sec)

–统计每个班级的男女生人数

select class,sex,count(*) as num from users group by class,sex;
+-------+--------+-----+
| class | sex    | num |
+-------+--------+-----+
|     2 | male   |   4 |
|     2 | female |   1 |
|     1 | female |   3 |
|     1 | male   |   1 |
+-------+--------+-----+
4 rows in set (0.00 sec)

注意：

在使用 group by 分组时，一般除了聚合函数，其他在 select 后面出现的字段列
都需要出现在 group by 后面。

9.5 order by 排序

MySql中使用 select 语句查询的数据结构是根据数据在底层文件的结构来排序的。
首先不要依赖默认的排序，另外在需要排序时要使用 order by 对返回的结果进行排序。
asc 升序，不用写，默认。
desc 降序。
–按照年龄对结果进行排序，从大到小
select * from users order by age desc;
–可以按照多个字段进行排序
–age相同情况下按照id进行排序！
select * from users order by age,id;
select * from users order by age,desc id;

having 子句

在分组聚合计算后，对结果再次进行过滤，类似于 where，
where 过滤的是行数据，having 过滤的是分组数据
–统计班级人数，并且人数达5人及以上
select class,count(*) as num from users group by class having num >=5;

9.6 limit 数据分页

• limit n 提取n条数据
• limit m,n 跳过m条数据，提取n条数据
  –查询表中数据，只要3条
  select * from users limit 3;
  limit 一般应用在数据分页上
  第一页，limit 0,10
  第二页，limit 10,10
  第三页，limit 20,10

提取表中 年龄最大的3个 ？

select * from users order by age desc limit 3;

练习题
1.统计班级为2的男女生人数
2.获取每个班级中的平均年龄，并按照平均年龄降序
3.统计每个班级人数，降序
4.获取班级人数最多的 班级id信息

select class, sex, count(*) from users where class=2 group by sex;
+-------+--------+----------+
| class | sex    | count(*) |
+-------+--------+----------+
|     2 | male   |        4 |
|     2 | female |        1 |
+-------+--------+----------+
2 rows in set (0.00 sec)
select class,avg(age) as avg_age from users group by class order by avg_age desc;
+-------+---------+
| class | avg_age |
+-------+---------+
|     1 | 34.0000 |
|     2 | 23.4000 |
+-------+---------+
2 rows in set (0.00 sec)

select id,count(class) as num from users group by id

10.MySQL数据库导入导出和权限管理

10.1 数据导出

1.数据库数据导出
mysqldump -u root -p 库名 > ~存放路径
导出一个库中所有数据，会形成一个建表和添加语句组成的sql文件
之后可以使用sql文件到别的库，或本机中创建或恢复数据
2.数据库中的表导出
mysqldump -u root -p 库名 表名 > ~存放路径

10.2 数据导入

把sql文件导入到数据库
mysql -u root -p 库名 < 文件所在路径

10.3 权限管理

root用户是权限最高的用户，不能用在项目中。
创建新用户，给予适当授权，完成数据库相关操作。

10.3.1 创建/授权用户

执行create user/grant命令
用的MySQL版本较新（从5.7.7版本开始），要注意语法，需要先用create user创建用户，再用grant命令赋权限；

1. 创建用户

create user [用户名]@[访问地址] identified by [密码]
–create user lisi@’%’ identified by ‘123’;
–% 表示任何主机都可以连接（可远程连接）
–‘localhost’ 表示只能在本地登录
查询创建的用户

mysql> use mysql;
Database changed
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| lisi             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

desc user # 查询表的结构

2. 授权用户

grant 授权的操作 on 授权的库.授权的表 to 用户名@地址;

mysql常用权限：
all privileges / all ： 所有权限；
create ： 创建；
alter : 修改（表结构）；
drop ： 删除；
select ： 查询；
create view : 创建视图;
insert ： 添加（表数据）；
delete : 删除（表数据）；
update ： 修改（表数据）；
with grant option：于grant句尾，允许被授权的用户把得到的权限继续授给其它用户
1） 库和表的权限
–授权可以对user库的所有表 进行 添加和查询 的权限
grant select,insert on user.* to zhangsan;
–* 所有表，. 表示所有库的所有表
给用户管理员权限，并且允许该用户继续给别的用户赋权限
grant all on . to lisi@’%’ with grant option;
2） 列的权限
–只能在某库某表上常看一列或几列数据
grant select(id,name,age) on user.temp to lisi@’%’;
#刷新权限
flush privileges;
–可以对某库某表的一些数据进行更新
grant update(id,name) on user.temp to lisi@’%’;

查看自己的权限
show grant;

注意：使用grant添加权限，权限会自动叠加，不会覆盖之前授予的权限，
如，先给用户添加 select 权限，后又给用户添加 update 权限，那么该用户就同时拥有 select 和 update 权限。

10.3.2 修改权限和配置

1. 回收 mysql 权限

revoke 命令回收用户权限
回收时先查看权限
show grants for 用户名;
revoke all/部分权限 on 库.表 from 用户名;
revoke all on . from lisi;
–这是删除了所有库的权限！！

+--------------------------------------+
| Grants for lisi@%                    |
+--------------------------------------+
| GRANT DROP ON *.* TO `lisi`@`%`      |
| GRANT DROP ON `ling`.* TO `lisi`@`%` |
+--------------------------------------+
mysql> revoke all on *.* from lisi;
+----------------------------------+
| Grants for lisi@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `lisi`@`%` |
+----------------------------------+

2. 修改 mysql 用户密码

alter user lisi@’%’ identified by ‘1234’;

3.删除用户

drop user 用户名@地址;

11 Python操作MySQL

不同编程语言操作mysql，都使用了mysql提供的API接口。
若直接操作API相对复杂，因此编程语言提供封装好的包或者模块进行数据库的相关操作。
python中，pymysql，mysqldb…
pypi.org

11.1. 安装pymysql

pip install pymysql

11.2. 利用pycharm 或 sublime 编辑器编辑文件

1. 链接mysql数据库
2. 创建游标对象
3. 准备sql
4. 用游标对象执行sql
5. 提取结果
6. 关闭数据库连接
   https://pymysql.readthedocs.io/en/latest/user/examples.html

cursorclass=pymysql.cusors.DictCursor) 可把结果转为字典类型，默认为元组

import pymysql

1. 链接mysql数据库

db = pymysql.connection(host='localhost',
                        user='root',
                        password='',
                        db='ling',
                        charset='utf8mb4',
                        cursorclass=pymysql.cusors.DictCursor)

# db = pymysql.connection('localhost','root','','ling',charset=utf8mb4,
#                         cursorclass=pymysql.cursors.DictCursor)

try:
    # 2. 创建游标对象
    cursor = db.cursor()
    # 3. 准备sql
    sql = 'select version()'
    --mysql> select version;  可以返回mysql的版本
    # 4. 执行sql语句
    cursor.execute(sql)
    # 5. 提取结果
    # fetchall() 提取所有的结果   返回列表，列表中是字典
    # fetchone() 提取一条结果     返回字典
    data = db.fetchall()

finally:
    6. 关闭数据库连接
    db.close()

print(data)

11.3 运行文件

？？

11.4 数据的其他操作

https://pymysql.readthedocs.io/en/latest/modules/connections.html

...
try:
    # 2. 创建游标对象
    cursor = db.cursor()

    # 3. 准备sql
    sql = 'insert into users(name,phone) values('zhanz','13588')'
    # 当 sql = 'select * from users'

    # 4. 执行sql语句，返回行数
    row = cursor.execute(sql)

    # db.commit()   # 在添加，删除和修改的时候才用到，可不写
    # 若执行非查询类sql，咋没有适当的结果，提取不到数据

    # 5. 提取结果
    # data = db.fetchone()
    print(row)
except:
    db.rollback()  # 当代码出现问题时，进行回滚

finally:
    6. 关闭数据库连接
    db.close()

print(data)

11.5 Flask框架的基本实现