MySQL教程

MySQL数据库基本操作

数据库概述

概念： 一些特殊格式的文件的集合。
作用： 用来存储各种数据。
特点： 持久化存储；读写速度极高；保证数据的有效性；对程序支持性非常好，容易扩展；
分类：
关系型数据库：基于关系模型建立，用二维表进行数据存储的数据库。如：MySQL、Oracle、SQLite、SQLServer
非关系型数据库：不是基于二维表，基于key-value方式存储。如：MongoDB、Redis

数据库管理系统

定义：为管理数据库而设计的软件系统，英文Database Management System，简称DBMS。
构成：客户端、服务端、数据库文件。

• SQL语句
  作用：实现数据库客户端和服务端之间的通信。
  含义：结构化查询语言。
  分类：
  DQL：数据查询语言，用来查询。如select；
  DML：数据操作语言，对数据进行增删改。如insert、update、delete；
  DDL：数据定义语言，进行数据库、数据表的管理。如create、drop；
  DCL：数据控制语言，进行授权与权限回收。如grant、revoke；
  TPL：事务处理语言，对事务进行处理。如begin transaction、commit、rollback；

• 关系型数据库
  核心元素：数据库、数据表、字段（数据列）、记录（数据行）

MySQL环境搭建

• 服务器端的安装
  在终端中输入如下命令：sudo apt-get install mysql-server
  MySQL数据库操作：
  启动：sudo service mysql start
  查看进程中是否存在mysql服务：ps ajx | grep mysql
  （ps：查看当前系统进程；-a显示所有用户进程；-j任务格式显示进程；-x显示无控制终端进程）
  重启：sudo service mysql restart
  停止：sudo service mysql stop
  MySQL数据库的配置：
  配置文件目录：/etc/mysql/mysql.cnf

• 客户端的安装
  在终端中输入如下命令：sudo apt-get install mysql-client
  图形化工具： navicat
  命令行连接：
  可以看到密码：mysql -u用户名 -p密码 回车
  不可以看到密码：mysql -u用户名 -p 回车输入密码再回车
  命令行退出： quit 或者 exit （快捷键：【Ctrl+d】）
  查看数据库的版本： select version();
  查询当前时间： select now();

数据库完整性和约束

完整性的作用：保证数据的正确性。

完整性	约束
实体完整性	主键约束：不重复不为空，primary key 唯一约束：不重复可以为空，unique key
域完整性	非空约束：该字段信息不能为空，not null 默认约束：这个字段可以设置默认值，default
参照完整性	外键约束：建立表和表主键的关系，foreign key
用户定义完整性	字典的值必须是设定的范围之内

客户端Navicat使用

建立连接： 连接>>MySQL>>输入连接名、主机、端口、用户名和密码>>确定。

创建数据库： 连接>>右键>>新建数据库>>输入名称、字符集utf8、排序规则。
创建数据表： 打开数据库>>右键表>>新建表>>输入字段>>保存，输入表名。
修改数据表： 右键表名>>设计表>>修改信息>>保存。
打开表： 双击表名。
添加数据： 单击单元格。自动增长列不需要写；保存：【Ctrl+S】或底部“√”
修改数据： 双击单元格。
删除数据： 选中某行>>点击底部“-”号

MySQL数据类型

选择合适的类型保存数据的好处：1）节省存储空间 2）提升查询效率

• 数值

  • 整型类型：

    类型	存储Bytes	范围
    tinyint	1	-128~127 0~255
    smallint	2	-32768~32767 0~65535
    mediumint	3	-8388608~8388607 0~16777215
    int	4	-2147483648~2147483647 0~4294967295
    bigint	8	-9223372036854775808~9223372036854775807 0~18446744073709551615

    应用场景：
    保存人的年龄（1-100）：tinyint
    保存某个状态值（0、1）：tinyint
    小型项目的编号：int

  • 浮点型：
    float：单精度型，只保证6位有效数字的准确性；
    double：双精度型，只保证16位有效数字的准确性；

  • 定点数：
    decimal(M, D)，M代表总的数字位数（最大为65），D代表其中的小数位。如：decimal(5,2)代表5位数字，其中2位是小数，比如888.88。

    应用场景：用在需要精确的小数时，比如价格。

• 字符串

  字段名称	长度
  char	最大255个字符
  varchar	最大65535个字节
  tinytext	最多255个字节
  text	最多65535个字节
  mediumtext	最多16777215个字节
  longtext	最多4294967295个字节

  char和varchar的区别：
  char最多能保存255个字符（无论中文、英文还是任何符号）。比如：可以保存255个汉字、英文字母、数字、符号等等。
  varchar最多能保存65535个字节。（UTF8编码时，一个字符占3个字节，可以保存2万多个字符；GBK编码时一个字符占2个字节，可以保存3万多个字符）
  char：定长字符串
  char也叫做定长字符串，指的是在创建表时，char字段占用硬盘空间的大小就已经固定了。
  （比如，我们定义name char(10)，代表name字段将占10个字符的硬盘空间，那么具体是多少个字 节？要看字段是什么编码的，如果是utf8编码，那么一个字符占3个字节，所以char(10)将占30个字 节，无论内容够不够10个字符都占30个字节，比如只存abc三个字符也要占30个字节。）
  varchar：变长字符串
  varchar也叫做变长字符串，指的是字段占用硬盘空间的大小并不是固定的，而是由内容决定的，等于内容的长度+1个字节(字符串结束’\0’)
  （比如，我们定义name varchar(10)，代表最多保存10个字符，如果表是utf8编码，则最多占用空间30个字节，但是，如果内容不够10个字符，比如内容是abc，那么只占3个字符9字节再+1，共占10个字节。）

  选择字符串类型的原则：
  经常变化的字段用varchar
  知道固定长度的用char
  尽量用varchar
  超过255字符的只能用varchar或者text
  能用varchar的地方不用text

• 枚举类型
  枚举类型eum，在定义字段时就规定好固定的几个值，然后插入记录时值只能从这几个固定好的值中选择一个。
  语法定义： gender enum(‘男’, ‘女’)
  应用场景： 当值是几个固定可选时，比如：性别、星期、月份、表示状态时（比如：是、否）
  注意：
  一个enum最多可以设置65535个值
  这个字段最终只占1到2个字节（要看设置值的多少）比较节省空间。

• 时间类型

  字段名称	格式
  date	年-月-日。如：2024-01-03
  datetime	年-月-日 时:分:秒。如：2024-01-03 17:07:10
  timestamp	年-月-日 时:分:秒。如：2024-01-03 17:07:10
  time	时:分:秒。如：17:07:10
  year	年。如：2024

  datetime和timestamp的区别

  1. 范围不同：
     datetime保存时间的范围：1000-01-0100:00:00 到 9999-12-31 23:59:59
     timestamp保存时间的范国：1970-01-01 00:00:01 到 2038-01-19 03:14:07
  2. 存储空间不同

数据库操作

-- MySQL数据库的操作
ctrl+a 回到开头
ctrl+e 回到结尾
ctrl+l 清屏

-- 连接数据库
-- 显示密码
mysql -u用户名 -p密码
-- 不显示密码
mysql -u用户名 -p
密文输入密码

-- 退出数据库
exit
quit
ctrl+d

-- sql语句最后要有；结尾
-- 显示数据库版本
select version();

-- 显示时间
select now();

-- 查看当前使用的数据库
select database();

-- 查看所有数据库
show databases;

-- 创建数据库（python_db）
create database python_db;
-- 指定编码的数据库创建
create database python_db charset=utf8;

-- 查看创建数据库的语句
show create database python_db;

-- 使用/切换数据库
use python_db;

-- 删除数据库
drop database python_db;
-- 

数据表结构的操作

-- 查看当前数据库中的所有表
show tables;

-- 创建表 classes(id, name, num)
-- int unsigned 无符号整型
-- auto_increment 表示自动增长
-- not null 不能为空
-- primary key 主键
-- default 默认值
create table classes(
id int unsigned primary key auto_increment,
name varchar(10) not null,
num tinyint
);

-- 创建students表(id, name, age, high, gender, cls_id)
create table students(
id int unsigned primary key auto_increment,
name varchar(20) not null,
age tinyint(1),
height decimal(3,2),
gender enum('男', '女'),
cls_id int unsigned
);

-- 查看表结构
desc classes;

-- 查看表的创建语句
show create table students;

-- 修改表students：添加字段 生日birthday
alter table students add birthday datetime;

-- 修改表students：修改字段类型或约束
alter table students modify birthday not null;

-- 修改表students，重命名字段birthday>>birth
alter table students change birthday birth datetime not null;

-- 修改表students，删除字段birth
alter table students drop birth; 

-- 删除表
drop table students;

-- 重命名表的名称
rename table students to student;

数据表数据的操作 CRUD

-- 增加
-- 全列插入: insert [into] 表名 values(...)
-- 主键字段可以用0 null default 来占位
insert into classes values(1, '1年级1班', 45）;
insert into classes values(null, '1年级2班', 50)
-- 部分插入：insert int 表名(列1, ...) values(值1, ...)
insert into students(id, name) values(null, '小明');
-- 多行插入
insert int classes values(null, '1年级3班', 52),(null, '2年级1班', 47)

-- 修改：update 表名 set 列1=值1, 列2=值2... where 条件;
-- 全部修改
update students set age=6;
-- 按条件修改
update student set age=8, height=1.21 where name='小明';

-- 查询
-- 查询所有列
select * from students;
-- 按条件查询
select * from students where name='小明';
-- 查询指定列
select name, age from students;
-- 查询不重复的行（查性别）
select distinct gender from students;

-- 删除
-- 物理删除：delete from 表名 where 条件;
delete from students where id=2;
-- 逻辑删除
-- 用一个字段来表示 这条信息是否已经不能再使用了
-- 给students表添加一个is_delete字段bit类型 默认为0
alter table students add is_delete bit default 0;
-- bit类型，智能保存0或者1；is_delete=1 逻辑删除
update students set is_delete=1 where id=3;

MySQL数据库进阶操作

标准的SQL书写格式：

select 字段1, 字段2, ...
from 表名
[where 条件]
[group by 字段名]
[having 条件]
[order by 字段名 排序规则]
[limit 起始位置, 数量]

where条件查询

• where之比较运算

  比较运算	符号
  等于	=
  大于	>
  大于等于	>=
  小于	<
  小于等于	<=
  不等于	!= <>

  -- 条件查询
  -- 查询年龄为8岁的所有学生的姓名
  select name from students where age=8;
  
  -- 查询年龄大于（小于）8岁的学生信息
  select * from students where age>8;
  select * from students where age<8;
  
  -- 查询年龄大于等于（小于等于）8岁的学生信息
  select * from students where age>=8;
  select * from students where age<=8;
  
  -- 查询年龄不等于8岁的学生信息
  select * from students where age!=8
  select * from students where age<>8;
  

• where之逻辑运算

  逻辑运算符	含义
  and	表示多个条件时，多个条件必须同时成立
  or	表示多个条件时，满足任意一个条件时成立
  not	表示取反操作

  -- 逻辑运算符
  -- and：查询年龄在5和7之间的所有学生的信息
  select * from students where age>=5 and age<=7;
  
  -- or: 查询年龄在7岁以上或者身高有1.32（包含）以上的学生信息
  select * from students where age>7 or height>=1.32;
  
  -- not：查询年龄不在7岁以上的女学生的信息
  select * from students where not age>7 and gender='女';
  

• where之模糊查询

  运算符	含义
  like	关键字
  %	表示任意0个或多个任意字符
  _	表示1个任意字符

  -- 模糊查询
  -- 查询名字中以“小”开始的学生信息
  select * from students where name like '小%';
  
  -- 查询姓名中有“小”的学生信息
  select * from students where name like '%小%';
  
  -- 查询名字是2个字的学生信息
  select * from students where name like '__';
  
  -- 查询名字至少有3个字的学生信息
  select * frome students where name like '___%';
  

• where之范围查询

  运算符	含义
  between A and B	连续范围查询，匹配范围为[A, B]
  in	非连续范围查询

  -- 范围查询
  -- 查询年龄为6,8的学生姓名
  select name from students where age in (6, 8);
  -- 查询年龄不是6,8的学生姓名
  select name from students where age not in (6, 8);
  
  -- 查询年龄在6到8岁之间的男生信息
  select * from students where (age between 6 and 8) and gender='男';
  -- 查询年龄不在6到8岁之间的学生姓名
  select name from students where age not between 6 and 8;
  

• where之空值判断

  运算符	含义
  is null	判断为空
  is not null	判断非空

  -- 空判断
  -- 查询身高为空的学生信息
  select * from students where height is null;
  
  -- 查询身高非空的学生信息
  select * from students where height is not null;
  

order排序

语法： select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
说明：
1）将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推。
2）asc从小到大排列，即升序（默认）。
3）desc从大到小排序，即降序。

-- 排序
-- 查询年龄在6到8岁之间的男生，按照年龄从小到大排序
select * from students where age between 6 and 8 and gender='男' order by age;

-- 查询年龄在6到8岁之间的女生，按照身高从高到矮排序
select * from students where age between 6 and 8 and gender='女' order by height desc;

-- 查询年龄在6到8岁之间的女生，按照身高从高到矮排序，如果身高相同的情况下按照年龄从大到小排序
select * from students where age between 6 and 8 and gender='女' order by height desc, age desc;

聚合函数

聚合函数aggregation function又称为组函数。默认情况下，聚合函数会对当前所在表当做一个组进行统计。
特点：
（1）每个组函数接收一个参数（字段名或者表达式）
（2）统计结果中默认忽略字段为NULL的记录。要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换。
（3）不允许出现嵌套，比如sum(max(xx))
MySQL常见的聚合函数：

命令	作用	示例
count(*)l	计算总行数	select count(*) from car;
max(列)	求此列的最大值	select max(speed) from car;
min(列)	求此列的最小值	select min(speed) from car;
sum(列)	求此列的和	select sum(speed) from car;
avg(列)	求此列的平均值	select avg(speed) from car;

-- 聚合函数
-- 查询一共有多少个学生
select count(*) '总人数' from students;

-- 查询男生有多少人，女生有多少人
select count(*) as '男生人数' from students where gender='男';
select count(*) as '男生人数' from students where gender='女';

-- 查询最大年龄
select max(age) from students;

-- 查询女生的最矮身高
select min(height) from students where gender='女';

-- 查询所有人的年龄总和
select sum(age) from students;

-- 查询所有学生的平均年龄
select avg(age) from students;
select sum(age)/count(*) from students;

-- 四舍五入 round(123.23, 1)保留一位小数
-- 计算所有学生的平均年龄，保留2位小数
select round(avg(age), 2) from students;
-- 计算男生的平均身高，保留2位小数
select round(avg(height), 2) from students where gender='男';

group分组查询

分组： 所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
group by分组使用特点：
（1）group by的含义：将查询结果按照1个或多个字段进行分组，字段值相同的为一组。
（2）group by可用于单个字段分组，也可用于多个字段分组。
注意： select后面的字段只能出现以下两种情况：1. 在group by后出现过；2. 在聚合函数中出现。（错误示范：select * from students group by gender;）。即：分组的字段要出现在select后面！

• group by + group_concat()
  group_concat(字段名)根据分组结果，使用group_concat()来放置每一个分组中某字段的集合。
  
• group by + 聚合函数
• group by + having
  having条件表达式：用来过滤分组结果。
  having作用和where类似，但having只能用于group by而where是用来过滤表数据。
• group by + with rollup
  with rollup的作用是：在最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果。
  

--分组查询
-- group by
-- 根据性别分组，查询所有的性别
select gender from students group by gender;
-- 查询每种性别的人数
select gender, count(*) from students group by gender;
-- 计算每个年龄中的人数
select age, count(*) from students group by age;
-- group by + group_concat(...)
-- 查询同种性别中的姓名
select gender, group_concat(name) from students group by gender;

-- group by + 聚合函数
-- 查询每组性别的平均年龄
select gender, avg(age) from students group by gender;

-- group by + having
-- 查询平均年龄超过7岁的性别及其平均年龄和名字
select gender, avg(age) as avg_age, group_concat(name) from students group by gender having avg_age>7;
-- 查询每种性别中人数多于2人的性别和名字
select gender, count(*) c, group_concat(name) from students group by gender having c>2;

limit限制记录

可以使用limit限制取出记录的数量，但limit要写在sql语句的最后。
语法：limit起始记录，记录数
说明：
1）起始记录是指从第几条记录开始取，第一条记录的下标是0。
2）记录数是指从起始记录开始向后依次取的记录数。
例如：
select * from students limit 0,3; 意思是：从第下标为0的记录开始取，取3条。
select * from students limit 5,2; 意思是：从第下标为5的记录开始取，取2条。

-- limit查询
-- 查询前5个学生的信息
select * from students limit 0,5;
select * from students limit 5;

分页查询

分页语法： select * from 表名 limit start=0, count;
说明：
1）从start开始，获取count条数据；
2）start默认值为0；
3）当用户需要获取数据的前n条时，可以直接写上select * from 表名 limit n;
分页的推导公示： limit (n-1)*m, m (每页显示m条数据，当前显示第n页）
获取第n页数据的SQL语句： select * from 表名 limit (n-1)*m, m;

-- 分页查询
-- 每页显示2个，第1个页面
select * from students limit 0, 2;
-- 每页显示2个，第2个页面
select * from students limit 2, 2;
-- 每页显示2个，第3个页面
select * from students limit 4, 2;

-- 每页显示3个，显示第2页的信息，按照年龄从大到小排序（limit放在最后面！！）
select * from students order by age desc limit 3, 3;

-- 展示第1页，最新数据，每页显示2条
select * from students order by id desc limit 0,2;

连接查询

作用： 当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集进行汇总显示。
类型：

• 内连接查询： 查询的结果为两个表匹配到的数据，默认是笛卡尔积n*m。select 字段 from 表1 inner join 表2 where/on 表1.字段=表2.字段;
• 右（外）连接查询： 查询的结果为两个表匹配到的数据和右表特有的数据，对于左表中不存在的数据使用null填充。select 字段 from 从表 right join 主表 on 连接条件;
• 左（外）连接查询： 查询的结果为两个表匹配到的数据和左表特有的数据，对于右表中不存在的数据使用null填充。select 字段 from 主表 left join 从表 on 连接条件;

-- 连接查询
-- 内连接
select * from students inner join classes;

-- 查询有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;

-- 查询有能够对应班级的学生姓名以及班级名称
-- 给数据表起别名
select s.name, c.name from students s inner join classes c on s.cls_id=c.id;
-- 内连接的另一种写法
select s.name, c.name from students s, classes c where s.cls_id=c.id;

-- 查询有能够对应班级的学生信息以及班级名称
select s.*, c.name from students s inner join classes c on s.cls_id=c.id;

-- 查询有能够对应班级的学生信息以及班级名称，班级名称显示在第1列，按照班级编号进行排序
select c.name, s.* from students s inner join classes c on s.cls_id=c.id order by c.id;

-- 查询有能够对应班级的学生信息以及班级名称，班级名称显示在第1列，按照班级编号进行排序，当同一班级时，按照学生的年龄倒序排列
select c.name, s.* from students s inner join classes c on s.cls_id=c.id order by c.id, s.age desc;

-- 左（外）连接
-- 查询每位学生对应的班级信息
select * from students left join classes on students.cls_id=classes.id;
-- 查询米有对应班级的学生信息
select students.* from students left join classes on students.cls_id=classes.id where classes.id is null;

-- 右（外）连接
-- 将数据表名字位置互换，用right join完成
select * from classes right join students on students.cls_id=classes.id;

自连接查询

准备数据：
（1）创建areas表的语句如下：

create table areas(
aid int primary key,--地区id
atitle varchar(20),--地区名
pid int--上级地区id
);

（2）插入数据：

insert into areas values(1, '广东省', null),(2, '河南省', null),(3, '深圳市', 1),(4, '广州市', 1),(5, '南山区', 3),(6, '宝安区', 3);

（3）自连接用法：

-- 自连接
-- 查询一共有多少个省
select count(*) from areas where pid is null;

-- 查询广东省的所有城市
select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='广东省';

-- 查询深圳市的所有区县
select district.* from areas district, areas city where district.pid=city.aid and city.atitle='深圳市';

子查询

在一个select语句中，嵌入了另外一个select语句，那么被嵌入的select语句称之为子查询语句，外部那个select语句则称为主查询。

主查询和子查询的关系：
子查询是嵌入到主查询中
子查询是辅助主查询的，要么充当条件，要么充当数据源
子查询是可以独立存在的语句，是一条完整的select语句

分类：
1）标量子查询：子查询返回的是1行1列
2）列子查询：子查询返回的是1列多行
3）行子查询：子查询返回的是1行多列
4）表子查询：子查询返回的是多行多列

-- 子查询
-- 标量子查询：查询身高高于平均身高的学生信息
-- （1.查询班级学生的平均年龄；2.查询大于平均年龄的学生）
select * from students where height > (select avg(height) from students);

-- 列子查询：查询还有学生在班的所有班级名称
--（1.找出学生表中所有的班级id；2.找出班级表中对应的名称）
select name from classes where id in (select cls_id from students);

MySQL数据库编程

SQL演练

1、创建数据库及数据表

-- 创建“京东”数据库
create database jing_dong charset=utf8;

-- 使用“京东”数据库
use jing_dong;

-- 创建一个商品goods数据表
create table goods(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0,
is_show bit not null default 1,
is_saleoff bit not null default 0
);

2、插入数据

-- 向goods表中插入数据
insert into goods values(0, 'r510vc 15.6英寸笔记本', '笔记本', '华硕', '3399', default, default);
insert into goods values(0, 'y400n 14.0英寸笔记本电脑', '笔记本', '联想', '4999', default, default);
insert into goods values(0, 'g150th 15.6英寸游戏本', '游戏本', '雷神', '8499', default, default);
insert into goods values(0, 'x550cc 15.6英寸笔记本', '笔记本', '华预', '2799', default, default);
insert into goods values(0, 'x240 超极本', '超级本', '联想', '4880', default, default);
insert into goods values(0, 'u330p 13.3英寸超极本', '超级本', '联想', '4299', default, default);
insert into goods values(0, 'svp13226scb 触控超极本', '超级本', '索尼', '7999', default, default);
insert into goods values(0, 'ipad mini 7,9英寸平板电脑', '平板电脑', '苹果', '1998', default, default);
insert into goods values(0, 'ipad air 9.7英寸平板电脑', '平板电脑', '苹果', '3388', default, default);
insert into goods values(0, 'ipad mini 配备 retina显示屏', '平板电脑', '苹果', '2788', default, default);
insert into goods values(0, 'ideacentre c340 20英寸一体电脑', '台式机', '联想', '3499', default, default);
insert into goods values(0, 'vostro 3800-r1206 台式电脑', '台式机', '戴尔', '2899', default, default);
insert into goods values(0, 'imac me086ch/a 21.5英寸一体电脑', '台式机', '苹果', '9188', default, default);
insert into goods values(0, 'at7-7414lp台式电脑 llinux', '台式机', '宏荟', '3699', default, default);
insert into goods values(0, 'z220sff f4f06pa工作站', '服务器/工作站', '惠普', '4288', default, default);
insert into goods values(0, 'poweredge ii服务器', '服务器/工作站', '戴尔', '5388', default, default);
insert into goods values(0, 'mac pro专业级台式电脑', '服务器/工作站', '苹果', '28888', default, default);
insert into goods values(0, 'hmz-t3w 头戴显示设备', '笔记本配件', '索尼', '6999', default, default);
insert into goods values(0, '商务双肩背包', '笔记本配件', '索尼', '99', default, default);
insert into goods values(0, 'x3250 m4机架式服务器', '服务器/工作站', 'ibm', '6888', default, default);
insert into goods values(0, '商务双肩背包', '笔记本配件', '索尼', '99', default, default);

3、SQL强化演练

-- 查询类型cate_name为‘超级本’的商品名称name、价格price（where）
select name, price from goods where cate_name='超级本';

-- 显示商品的种类
-- 1）分组的方式（group by）
select cate_name from goods group by cate_name;
--2）去重的方法（distinct）
select distinct cate_name from goods;

-- 求所有电脑产品的平均价格avg，并且保留两位小数(round)
select round(avg(price),2) from goods;

-- 显示每种类型cate_name（由此可知需要分组）的平均价格
select cate_name, avg(price) from goods group by cate_name;

-- 查询每种类型的商品中最贵max、最便宜min、平均价avg、数量count
select cate_name, max(price) max_price, min(price) min_price, avg(price) avg_price, count(*) from goods group by cate_name;

-- 查询所有价格大于平均价格的商品，并且按价格降序排序 order desc
-- 1)查询平均价格avg(price)
select avg(price) from goods;
-- 2)使用子查询
select * from goods where price>(select avg(price) from goods) order by price desc;

-- 查询每种类型中最贵的电脑的所有信息
-- 1）查找每种类型中最贵的价格
select cate_name, max(price) as max_price from goods group by cate_name;
-- 2）关联查询 inner on每种类型中最贵的物品信息
select * from goods inner join 
(select cate_name, max(price) as max_price from goods group by cate_name) as max_price_goods 
on goods.cate_name=max_price_goods.cate_name and goods.price=max_price_goods.max_price;

SQL操作实战

解决问题： goods表中不再保存分类名称，改成分类id。根据商品表中的分类信息创建分类表。
第一步： 创建“商品分类”表
由于只存在一张表，其中既有商品信息又有分类名称和品牌名称。当把某种分类或者品牌的商品全部删除后，本不应该删除<独立于商品信息的分类和品牌>等信息结果也随着删除商品信息而删除，那么此时存在删除异常。
为解决删除异常问题，则必须再创建分类表和品牌表单独用来存储数据。

-- 创建商品分类表
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);

第二步：同步商品分类表数据将商品的所有（种类信息）写入到（商品种类表）中。❤❤❤

-- 按照分组的方式查询goods表中的所有种类(cate_name)
select cate_name from goods group by cate_name;
-- 通过子查询插入数据到新的分类表中
insert into goods_cates(name) (select cate_name from goods group by cate_name);

第三步：同步商品表数据通过goods_cates数据表来更新goods表。

-- 因为要通过goods_cates表更新goods表所以要把两个表连接起来(goods.cate_name=goods_cates.name)
select * from goods inner join goods_cates on goods.cate_name=goods_cates.name;
-- 把商品表goods中的cate_name全部替换成商品分类表中的商品id(update...set)
update goods inner join goods_cates on goods.cate_name=goods_cates.name set goods.cate_name=goods_cates.id;

第四步：修改表结构

-- 查看表结构（注意两个表中的外键类型需要一致）
desc goods;

-- 修改表结构 alter table 字段名字不同change。把cate_name改成cate_id int unsigned not null
alter table goods change cate_name cate_id int unsigned not null;

同理，根据商品表goods中的品牌信息创建分类表。

-- 创建brands表
create table brands(
id int unsigned primary key auto_increment,
name varchar(40) not null
);

-- 插入数据 brand_name
insert into brands(name) (select brand_nanme from goods group by brand_name);

-- 同步数据
update goods inner join brands on goods.brand_name=brands.name set goods.brand_name=brands.id;

-- 修改表结构
alter table goods change brand_name brand_id int unsigned not null;

外键使用

概念： 一个表的主键A，在另外一个表中出现，我们就说A是表B的一个外键。
作用： 表间的数据插入、更新时候的一种约束。
创建外键：
（1）已经存在的表建立外键：alter table 表名 add foreign key(当前表的字段) references 表名(字段);
（2）创建表的时候设置外键约束：

create table goods2(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
price decimal(10,3) not null default 0,
cate_id int unsigned,
brand_id int unsigned,
is_show bit not null default 1,
is_saleoff bit not null default 0,
foreign key(cate_id) references goods_cates(id),
foreign key(brand_id) references brands(id)
);

删除外键约束：

-- 需要先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称
show create table goods2;

-- 获取名称之后就可以根据名称来删除外键约束
alter table goods2 drop foreign key 外键名称;

使用到外键约束会极大的降低表更新的效率，所以在追求读写效率优先的场景下一般很少使用外键。

解决如下问题：
在goods数据表中写入任意记录：insert into goods (name, cate_id, brand_id, price) values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4, '1849');
注意：goods_cates数据表中无id=12的记录，应不允许插入！
sql -- 查询所有商品的详细信息（左连接，将右表未显示数据添加到最终结果） select * from goods left join goods_cates on goods.cate_id=goods_cates.id;

如何防止无效信息的插入，就是可以在插入前判断类型是否存在？可以使用外键来解决。对于已经存在的字段添加外键约束。

-- 给brand_id添加外键约束和brands的id建立外键关联
alter table goods add foreign key (brand_id) references brands(id);
-- 给cate_id添加外键约束和goods_cates的id建立外键关联
alter table goods add foreign key (cate_id) references goods_cates(id);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails.
-- 给cate_id添加外键失败，会出现1452错误，原因：已经添加了一个不存在的cate_id值12，因此需要先删除
delete from goods where cate_id=12;
alter table goods add foreign key (cate_id) references goods_cates(id);
-- 查看good数据表的创建语句
show create table goods;
-- 再次尝试插入cate_id=12不存在的数据（插入失败，会出现1452错误）
insert into goods (name, cate_id, brand_id, price) values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4, '1849');

视图

• 视图的概念

-- 查询goods数据表中产品名称，分类名称及对应品牌
select goods.name gname, goods_cates.name gcname, brands.name gbname 
from goods 
inner join goods_cates on goods.cate_id=goods_cates.id 
inner join brands on goods.brand_id=brands.id;

上述复杂的查询，往往是有多个数据表进行关联查询而得到，我们可能非常频繁的使用。为了简化用户复杂的操作，我们可以考虑使用视图。
视图： 视图就是一个能够把复杂SQL语句的功能封装起来的一个虚表。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图是对若干张基本表的引用，一张虚表，只查询语句执行结果的字段类型和约束，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；$仅支持查询$。

• 视图的使用
  定义视图： create view 视图名称 as select语句; 建议以v_开头；
  查看视图： show tables;
  使用视图： select * from 视图名称;
  删除视图： drop view 视图名称;

-- 查询goods数据表中产品名称，分类名称及对应品牌，创建上述结果的视图
create view v_goods_info as 
select goods.name gname, goods_cates.name gcname, brands.name gbname 
from goods 
inner join goods_cates on goods.cate_id=goods_cates.id 
inner join brands on goods.brand_id=brands.id;

-- 查看所有表或视图
show tables;

-- 当原表产品名称改变后，会影响视图（视图是虚拟表）
update goods set name='xxx' where id=21;
select * from v_goods_info;

-- 删除视图
drop view v_goods_info;

事务

• 事务的概念及特性
  概念： 事务Transaction，是指作为一个基本工作单元执行的一系列SQL语句的操作。
  作用： 要么完全地执行，要么完全地都不执行。
  特性ACID：
  原子性Atomicity：事务的操作是原子的，不能分隔；
  一致性Consistency：双方的结果要一致；
  隔离性Isolation：同一时间只有一个事务在操作；
  持久性Durability：操作完成结果持久不变；

为什么要有事务呢？
有时我们要完成一个功能，需要执行多个SQL语句，如果这些SQL执行到一半突然停电了，那么就会导致这个功能只完成了一半，这种情况在很多时侯是不允许出现的，比如：银行转账时。
例如：
A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：
1、检查A的账户余额>500元；
2、A账户中扣除500元；
3、B账户中增加500元；
正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。
那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。
以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此。
从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。这两个操作必须保证全部成功或者什么都不做，不允许出现成功一个失败一个的情况。

下面举一个银行应用是解释事务必要性的一个经典例子。
假如一个银行的数据库有两张表：支票表(checking)和储蓄表(savings)。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么至少需要三个步骤：
1、检查支票账户的余额高于或者等于200美元
2、从支票张户余额中减去200美元
3、在储蓄帐户余额中增加200美元
上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。
可以用START TRANSACTION语句开始一个事务，然后要么使用COMMIT提交，将修改的数据持久保存，要么使用ROLLBACK撒销所有的修改。事务SQL的样本如下：

start transaction;
select balance from checking where customer_id=10233276;
update checking set balance=balance-200.00 where customer_id 10233276;
update savings set balance=balance+200.00 where customer_id 10233276;
commit;

• 事务的使用
  查看表引擎： 数据库存储引篷是数据库底层软件组织，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。
  查看数据库服务器支持的表存储引擎： show engines;
  
  表的引擎类型必须是innodb类型才可以使用事务。
  查看表的创建语句，可以看到engine=innodb。
  
  开启事务： begin; 或者 start transaction; 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中。
  提交事务： commit; 将缓存中的数据变更维护到物理表中。
  回滚事务： rollback; 放弃缓存中变更的敬据，表示事务执行失败，应该回到开始事务前的状态。

-- 基本使用
-- 1. 开启事务
begin;
-- 2. 操作insert/update/delete
update goods set name='sss' where id=21;
select * from goods; -- 查看表信息（更新为sss）
-- 3. 回滚事务
rollback;
select * from goods; -- 查看表信息（回滚为xxx）
-- 提交事务
update goods set name='sss' where id=21;
commit;
select * from goods; -- 查看表信息（更新为sss）

数据库三范式

对于设计数据库提出了一些规范，这些规范被称为范式(Normal Form)。目前有迹可寻的共有8种范式，一般需要遵守3范式即可。

• 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。

  考虑这样一个表：【联系人】(姓名，性别，电话)
  如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到1NF。要符合1NF我们只需把列（电话）拆分，即：【联系人】(姓名，性别，家庭电话，公司电话)。
  

• 第二范式（2NF）：满足1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。

  考虑一个订单明细表：【OrderDetail】（OrderID, ProductID, UnitPrice, Discount, Quantity, ProductName)
  因为我们知道在一个订单中可以订购多种产品，所以单单一个OrderID是不足以成为主键的，主键应该是(OrderlD, ProductID)。显而易见Discount（折扣），Quantity（数量）完全依赖（取决）于主键(OderID, ProductID)，而UnitPrice单价，ProductNamei产品名称只依赖于ProductlD。所以，OrderDetail表不符合2NF。不符合2NF的设计容易产生冗余数据。
  可以把【OrderDetail】表拆分为【OrderDetail】(OrderlD, ProductID, Discount, Quantity)和【Product】(ProductID, UnitPrice, ProductName)来消除原订单表中UnitPrice, ProductName多次重复的情况。

• 第三范式(3NF)：满足2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。

  考虑一个订单表【Order】（OrderlD, OrderDate, CustomerID, CustomerName, CustomerAddr, CustomerCity）主键是(OrderID)。其中OrderDate. CustomerlD, CustomerName, CustomerAddr, CustomerCity等非主键列都完全依赖于主键(OrderID)，所以符合2NF。不过问题是CustomerName, CustomerAddr, CustomerCity直接依赖的是CustomerlD（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合3NF。
  通拆分【Order】为【Order】(OrderlD, OrderDate, CustomerID)和【Customer】(CustomerlD, CustomerName, CustomerAddr, CustomerCity)从而达到3NF。
  第二范式(2NF)和第三范式(3NF)的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

E-R模型及表间关系

• E-R模型简介
  E-R模型即E-R图
  E-R图即实体-联系图(Entity Relationship Diagram)，是指提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型。由美籍华裔计算机科学家陈品山(Peter Chen)发明。
• E-R模型的使用场景
  1）关系型数据库关系模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一步
  2）在设计阶段一般使用E-R模型进行建模。有很多设计数据库的软件，常用的如power designer，db desinger等，这些软件可以直观的看到实体及实体间的关系
  3）设计数据库，可能是由专门的数据库设计人员完成，也可能是由开发组成员完成，一般是项目经理带领组员来完成
  4）待设计完成E-R模型会将其转化为关系模型。关系模型用二维表的形式表示实体和实体间联系的数据模型
• E-R模型组成元素
  E-R图用实体、联系和属性这3个概念来描述现实问题，有以下三种元素：
  （1）实体型(Entity)：具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体：在E-R图中用矩形表示，矩形框内写明实体名；比如电商购物系统中用户、购物车、订单等都是实体。
  （2）属性(Attribute)：实体所具有的某一特性，一个实体可由若干个属性来刻画。在E-R图中用椭圆形表示，并用无向边捋其与相应的实体连接起来；比如用户的ID、用户名、密码、昵称、身份证号码都是属性。
  （3）联系(Relationship)：实体彼此之间相互连接的方式称为联系，也称为关系。联系可分为以下3种类型：
  一对一、一对多、多对多。关系也是一种数据，需要通过一个字段存储在表中。实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值。
  
• 总结
  范式就是设计数据库的通用规范。
  E-R图由实体、属性、实体之间的联系构成，主要用来描述数据库中表结构。

Python连接MySQL

作用：使用python代码充当客户端，连接数据库进行操作

• Python中操作MySQL步骤

  

  如何理解连接connection和游标cursor：connection就像是连接出发地和目的地的高速公路；cursor就像是在高速公路上的货车拉货。我们使用游标就可以完成对数据的操作。当我们完成操作后就可以停下货车，然后公路再停止使用。

• pymysql的使用

  1. 引入模块：import pymysql

  2. 建立连接对象：conn = pymysql.connect(参数列表)

     参数如下：
     host：连接的mysql主机，如果本机是’localhost’
     port：连接的mysql主机的端口，默认是3306
     user：连接的用户名
     password：连接的密码
     database：数据库的名称
     charset：通信采用的编码方式，准荐使用utf8

     关闭连接：conn.close()
     提交数据：conn.commit()
     撤销数据：conn.rollback()
     通过连接获取游标cur=conn.cursor()返回Cursor对象，用于执行sql 语句并获得结果。

  3. 创建游标对象：cur = conn.cursor()，目的：执行sql语句（select, insert,update, delete）

  4. 使用游标对象执行SQL语句： row_count = cur.execute('sql语句')，返回受影响的行数。

  5. 获取并打印执行结果
     获取并打印结果集中的一条：print(cur.fetchone())，返回一个元组，如(1, ‘小王’, 18)
     获取并打印结果集中的所有：result_list = cur.fetchall()，返回((1, ‘小王’, 18), (2, ‘小黄’, 16))， for line in result_list: print(line)

  6. 关闭游标对象：cur.close()

  7. 关闭连接对象：conn.close()

• pymysql完成数据查询

  """
  1. 导入模块 pymysql
  2. 建立连接对象pymysql.connect()
  3. 创建游标对象
  4. 使用游标对象执行SQL语句
  5. 获取并打印执行的结果
  6. 关闭游标对象
  7. 关闭连接对象
  """
  
  # 1. 导入模块 pymysql
  import pymysql
  
  # 2. 建立连接对象pymysql.connect()
  conn = pymysql.connect(
      host='localhost',
      port=3306,
      user='root',
      password='123456',
      database='jing_dong')
  # 3. 创建游标对象
  cur = conn.cursor()
  # 4. 使用游标对象执行SQL语句
  sql = 'select * from goods order by id desc;'
  row_count = cur.execute(sql)
  print("查询到%s条数据。" % row_count)
  # 5. 获取并打印执行的结果
  # print(cur.fetchone())         # 取出结果集中一行
  result_list = cur.fetchall()    # 取出结果集中的所有数据
  for line in result_list:
      print(line)
  # 6. 关闭游标对象
  cur.close()
  # 7. 关闭连接对象
  conn.close()
  

Python操作数据库CRUD

比查询多个conn.commit()

"""
1. 导入模块 pymysql
2. 建立连接对象pymysql.connect()
3. 创建游标对象
4. 使用游标对象执行SQL语句
5. 提交
6. 获取并打印执行的结果（影响的行数）
7. 关闭游标对象
8. 关闭连接对象
"""

# 1. 导入模块 pymysql
import pymysql

# 2. 建立连接对象pymysql.connect()
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    database='jing_dong')
# 3. 创建游标对象
cur = conn.cursor()
# 4. 使用游标对象执行SQL语句
# sql = "insert into goods values(null, 'iphone 15', 1, 1, 9998, 1, 1);"   # 插入
# sql = "update goods set name='iphone 16' where name='iphone 15';"          # 修改
sql = "delete from goods where name='iphone 16';"                           # 删除
row_count = cur.execute(sql)
# 5. 提交
conn.commit()
# 6. 获取并打印执行的结果（影响的行数）
print("影响行数：", row_count)
# 7. 关闭游标对象
cur.close()
# 8. 关闭连接对象
conn.close()

SQL防注入

参数化列表防止SQL注入
什么是SQL注入：
产生原因：后台将用户提交的带有恶意的数据和SQL进行字符串方式的拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象。
如何防止：sq语句的参数化，将SQL语句的所有数据参数存在一个列表中传递给execute函数的第二个参数。
注意:
（1）此处不同于python的字符串格式化，必须全部使用%s占位
（2）所有参数所需占位符外不需要加引号

• SQL注入演示

  # 1. 导入模块 pymysql
  import pymysql
  # 2. 建立连接对象pymysql.connect()
  conn = pymysql.connect(
      host='localhost',
      port=3306,
      user='root',
      password='123456',
      database='jing_dong')
  # 3. 创建游标对象
  cur = conn.cursor()
  # 4. 使用游标对象执行SQL语句
  input_name = input('请输入要查询的物料名称：\n')
  # 被注入的过程分析：
  # input_name = ' or 1 or '
  # sql = "select * from goods where name = '' or 1 or '';"
  sql = "select * from goods where name = '%s';" % input_name
  row_count = cur.execute(sql)
  print("查询到%s条数据。" % row_count)
  # 5. 获取并打印执行的结果
  for line in cur.fetchall():
      print(line)
  # 6. 关闭游标对象
  cur.close()
  # 7. 关闭连接对象
  conn.close()
  

  
• SQL防注入演示

  # 1. 导入模块 pymysql
  import pymysql
  # 2. 建立连接对象pymysql.connect()
  conn = pymysql.connect(
      host='localhost',
      port=3306,
      user='root',
      password='123456',
      database='jing_dong')
  # 3. 创建游标对象
  cur = conn.cursor()
  # 4. 使用游标对象执行SQL语句
  input_name = input('请输入要查询的物料名称：\n')
  # 防止注入：
  # 1）构建参数列表：params = [input_name]
  # 2）把列表传递给execute(sql, params)
  params = [input_name]
  sql = "select * from goods where name = %s;"
  row_count = cur.execute(sql, params)
  print("查询到%s条数据。" % row_count)
  # 5. 获取并打印执行的结果
  for line in cur.fetchall():
      print(line)
  # 6. 关闭游标对象
  cur.close()
  # 7. 关闭连接对象
  conn.close()
  

  

防注入的思想：
1、sql中需要变化的地方，用占位符%s %d，如sql = "select * from goods where name=%s;"，注意：SQL可以出现多个占位符，后续参数列表中元素的个数要与之对应；占位符不加引号。
2、把参数封装到列表中：params = [input_name]
3、把列表传递给execute(sql, 列表)：row_count = cur.execute(sql, params)

MySQL高级&装饰器基础

索引

索引的作用： 提升查询性能效率
创建索引的SQL语句：create index 索引名 on 表名(字段名称(长度));
注意：如果指定字段是字符串，需要指定长度，建议长度与定义字段的长度一致；不是字符串的话可不填长度部分；
查看索引的SQL语句：show index from 表名;
删除索引的SQL语句：drop index 索引名称 on 表名;

验证索引是否能提升查询性能：

1. 创建测试数据库python_index_db：create database python_index_db charset='utf8';

2. 创建测试表text_index：create table test_index(title varchar(10));

3. 使用python程序，通过pymysql模块向表中加入十万条数据。

   '''
   目标：插入10万条数据到python_index_db库中的test_index表
   步骤：
   1.导入模块
   2.创建连接对象
   3.创建游标对象
   4.for循环，插入10万条数据
   5.提交数据
   6.关闭游标
   7.关闭连接
   '''
   # 1.d导入模块
   from pymysql import connect
   
   def main():
       # 2.创建Connection连接
       conn = connect(host='localhost', port=3306, database='python_index_db', user='root', password='123456')
       # 3.获取Cursor对象
       cur = conn.cursor()
       # 4.for循环，插入10万条数据
       for i in range(100000):
           cur.execute('insert into test_index(title) values("ha-%d")' % i)
       # 5.提交数据
       conn.commit()
       # 6.关闭游标
       cur.close()
       # 7.关闭连接
       conn.close()
   
   if __name__ == '__main__':
       main()
   

4. 查询

   MySQL的Query Profiler是一个使用非常方便的Query诊断分析工具，通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况，如CPU，IO，IPC，SWAP等，以及发生的PAGE FAULTS，CONTEXT SWITCHE等等：同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置。
   通过执行“set profiling"命令，可以开后关闭Query Profiler功能。

   1）开启运行时间监测：set profiling=1;
   2）查找第1万条数据ha-9999：select * from test_index where title='ha-9999';
   3）查看执行时间：通过执行show profile;命令获取当前系统中保存的多个Query的profile的概要信息。
   4）为表titile_index的title列创建索引：create index title_index on test_index(title(10));
   5）执行查询语句：select * from test_index where title='ha-9999';
   6）再次查看执行时间：show profiles;
   

总结
（1）索引可以明显提高某些字段的查询效率。
（2）索引使用：
创建：create index 索引名 on 表名(字段名(索引长度 字符串类型才需要指定));
查看：show index from 表名;
（3）索引的副作用——索引虽好，不要贪杯！
要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
建立索引会占用磁盘空间。

用户管理

1. MySQL账户管理
在我们之前登录MySQL的时候我们都是直接使用的root用户，root用户属于数据库系统中的超级管理员，有权限对mysql进行任何想要做的操作。
如果在生产环境下操作数据库时也是全部直接使用root账户连接，这就和悬崖边跳舞差不多。所以创建定的账户，授予这个账户特定的操作权限，然后连接进行操作，比如常规的crud才是正道。
MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种：
- 服务实例级账号：启动了一个mysqld，即为一个数据库实例；如果某用户如root，拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库，连同这些库中的表。
- 数据库级别账号：对特定数据库执行增删改查的所有操作。
- 数据表级别账号：对特定表执行增删改查等所有操作。
- 字段级别的权限：对某些表的特定字段进行操作。
- 存储程序级别的账号：对存储程序进行增删改查的操作。

>注意：进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限。账户的操作主要包括创建账户、删除账户、修改密码丶授权权限等。

2. 查看所有用户
所有用户及权限信息存储在mysql数据库的user表中。

--查看所有数据库（默认创建了information_schema、 performance_schema、mysql、sys这4个库）
show databases; 
--切换mysql数据库
use mysql; 
 --查看mysql数据库下的所有数据表
show tables;
--查看user表的结构
desc user; 
--查看所有用户（host表示允许访问的主机；user表示用户名；authentication_string加密后的密码）
select host, user, authentication_string from user; 

3. 创建账户、授权
需要使用实例级账户登录后操作，以root为例：mysql -uroot -p --回车输入密码后再回车
创建用户：create user '用户名'@'主机' identified by '密码';
授权：grant 权限 on 数据库.表名 to '用户名'@'主机名';
常用权限主要包括：create、alter、drop、insert、update、delete、select；如果分配所有权限，可以使用all privileges
刷新权限：flush privileges;
查看用户有哪些权限：show grants for '用户名'@'主机名';

实例1： 创建一个laowang的账号，密码未123456，只能通过本地访问，并且只能对jing_dong数据库中的所有表进行读操作。

--登录root用户创建
mysql -uroot -p --回车输入密码后再回车
--创建用户
create user 'laowang'@'localhoast' identified by '123456';
--授权select权限
grant select on jing_dong.* to 'laowang'@'localhost';
-- 查看laowang有哪些权限
show grants for 'laowang'@'localhost'；
-- 退出root
quit;
--使用老王账号登录
mysql -ulaowang -p --回车输入密码后再回车

说明：
可以操作jing_dong数据库的所有表，方式为：jing_dong.*
访问主机通常使用百分号%表示此账户可以使用任何的主机登录访问此数据库。
访问主机可以设置成localhost或具体的ip，表示只允许本机或特定主机访问。

实例2： 创建一个laoli的账号，密码未123456，可以任意电脑进行链接访问，并且对jing_dong数据库中的所有表拥有所有权限。

--登录root用户创建
mysql -uroot -p --回车输入密码后再回车
--创建用户
create user 'laoli'@'%' identified by '123456';
--授权所有权限
grant all privileges on jingdong.* to 'laoli'@'%';

4. 修改权限：grant 权限 on 数据库 to '用户名'@'主机名' with grant option;
如：

-- 修改laowang权限
grant select,update on jing_dong.* to 'laowang'@'localhost' with grant option;
-- 刷新权限
flush privileges;

5. 修改密码： alter user ‘用户名'@'主机名' identified by '新密码';
如：

-- 修改laowang权限
alter user 'laowang'@'localhost' identified by '123';
-- 刷新权限
flush privileges;

6.删除账号
语法1：使用root登录：drop user '用户名'@'主机';
语法1：使用root登录，删除mysql数据库的user表中数据：delete from user where user='用户名';

爬虫实战：爬取数据并保存到数据库中

核心思路：
1、准备爬虫基础版代码
2、定义保存数据的函数，用于将影片名称和地址保存到数据库中
3、定义函数检测数据是否存在

--创建数据库
create database movie_db charset=utf8;
--选择数据库
use movie_db;
--创建数据表
create table movie_link(
id int(11) primary key auto_increment,
file_name varchar(255) not null,
file_link varchar(255) not null
)charset=utf8;
-- 展示表
show tables;

"""
1. 定义专门函数，负责保存数据 add_film
    1) 定义sql，准备插入数据
    2）执行insert
2. 定义专门函数，负责检测数据库中是否存在相同的数据 film_exist()
    1）定义sql 根据影片名称和地址查询
    2）执行查询，并获取查询的记录数
    3）如果获取的记录数>0 return True, 反之 return False
3. 创建链接对象（全局）
4. 创建游标对象（全局）
5. 提交操作
6. 关闭操作
"""

import re
import urllib.request
import pymysql

def add_film(film_name, film_link):
    """保存影片到数据库"""
    # 1) 定义sql，准备插入数据
    sql = 'insert into movie_link values (null, %s, %s)'
    # 2）执行insert
    ret = cur.execute(sql, [film_name, film_link])
    # 如果插入成功，给出提示
    if ret:
        print("保存成功！影片：[%s]" % film_name)

def film_exist(film_name, film_link):
    """检测数据是否已经存在"""
    # 1）定义sql 根据影片名称和地址查询
    sql = "select id from movie_link where film_name=%s, and film_link=%s limit 1"
    # 2）执行查询，并获取查询的记录数
    ret = cur.execute(sql, [film_name,film_link])
    # 3）如果获取的记录数>0 return True, 反之 return False
    if ret:
        return True
    else:
         return False

def get_movie_link():
    """获取电影下载链接"""
    # 1.定义列表的地址
    film_list_url = 'http://www.ygdy8.net/html/gndy/dyzz/list_23_1.html'
    # 2.打开url地址，获取数据
    response_list = urllib.request.urlopen(film_list_url)
    # 2.1通过read()读取网络资源数据
    response_list_date = response_list.read()
    # 3.解码获取到的数据
    response_list_text = response_list_date.decode('GBK')
    # 4.使用正则得到所有的影片内容地址
    # 4.1 使用findall()根据正则查找所有影片对应的内容页地址
    url_list = re.findall(r'<a href=\"(.*)\" class=\"ulink\">(.*)</a>', response_list_text)
    # 定义一个字典，用于保存影片信息
    film_dict = {}
    # 循环变量url_list
    i = 1
    for content_url, file_name in url_list:
        # 拼接内容页地址
        content_url = "http://www.ygdy8.net"+content_url
        # 打开内容页地址
        response_content = urllib.request.urlopen(content_url)
        # 接收内容页数据
        response_content_data = response_content.read()
        # 解码得到内容页的文本内容
        response_content_text = response_content_data.decode('GBK')
        result = re.search(r'bgcolor=\"#fdfddf\"><a href=\"(.*?)\">', response_content_text)
        film_dict[file_name]=result.group(1)
        print("以获取%d条信息" % i)
    return film_dict

def main():
    film_dict = get_movie_link()
    # 把字典遍历输出
    for film_name, file_link in film_dict.items():
        # print("%s | %s" % (film_name, file_link))
        # 如果数据库存在相同数据，就不会插入
        if film_exist(film_name, file_link):
            print("保存失败！影片：[%s]" % film_name)
            continue
        # 调用add_film方法添加数据
        add_film(film_name, file_link)

if __name__ == '__main__':
    # 3.创建链接对象（全局）
    conn = pymysql.connect(host="localhost", user="root", password="123456", database="movie_db")
    # 4.创建游标对象（全局）
    cur=conn.cursor()
    # 调用爬取数据
    main()
    conn.commit()
    # 5.关闭操作
    cur.close()
    conn.close()

爬虫实战：展示电影数据到网页中

核心思路：
1、导入pymysql模块
2、连接数据库并查询数据
3、将查询结果拼接成响应报文，发送给客户端

"""
1.把web服务器返回值固定内容，代码拷贝过来
2.把原本固定的内容变成从数据库动态读取
2.1导入pymysql模块
2.2连接数据库，并且查询所有的影片信息
2.3遍历查询结果，拼接响应的主体
3.发送给客户端
"""
import socket
import pymysql

def request_handle(new_client_socket, ip_port):
    # 接收客户端浏览器发送的请求协议
    request_data = new_client_socket.recv(1024)
    # 判断协议是否为空
    if not request_data:
        print("%s客户端已经下线！"%str(ip_port))
        new_client_socket.close()
        return
    # 响应行
    response_line = "HTTP/1.1 200 OK\r\n"
    # 响应头
    response_header = "Server:Python20WS/2.1\r\n"
    response_header += "Content-type:text/html;charset=utf-8\r\n"
    # 响应空行
    response_blank = "\r\n"
    # 响应主体
    # response_body = "HelloWorld!"
    response_body = ''
    # 2.1导入pymysql模块
    # 2.2连接数据库，并且查询所有的影片信息
    conn = pymysql.connect(host="localhost", user="root", password="123456", database="movie_db")
    cur = conn.cursor()
    # 2.3遍历查询结果，拼接响应的主体
    cur.execute("select * from movie_link")
    # fetchall返回所有数据
    result_list = cur.fetchall() #((1,'filmName','fileLink'),(),...())
    for row in result_list:
        response_body += "%d.%s  下载地址：[<a href='%s'>%s</a>] <br>" % (row[0], row[1], row[2], row[2])
    # 关闭操作
    cur.close()
    conn.close()

    response_data = response_line + response_header + response_blank + response_body
    # 发送响应报文
    new_client_socket.send(response_data.encode())
    # 关闭当前连接
    new_client_socket.close()

def main():
    # 创建套接字
    tcp_server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    tcp_server_socket.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR)
    # 绑定端口
    tcp_server_socket.bind(("", 8080))
    # 设置监听，让套接字由主动变为被动接收
    tcp_server_socket.listen(128)
    # 接收客户端连接
    while True:
        new_client_socket, ip_port = tcp_server_socket.accept()
        print("新客户来了：", ip_port)
        # 调用功能函数处理请求并且响应
        request_handle(new_client_socket, ip_port)

if __name__ == '__main__':
    main()

运行结果如下：

闭包

函数概念理应引用：

def test():
    print('hello!')

test()       # hello!
ret = test
print(ret)      # <function test at 0x01C9A738> 

# id获取对象地址
print('%x' % id(ret))   # 1c9a738
print('%x' % id(test))   # 1c9a738

# 通过ret调用函数
ret()   # hello!

总结：函数名是一个特殊变量，保存了函数的地址id；自定义一个变量可以获取函数地址；自定义变量调用函数 “变量名()"

闭包概念： 闭包(closure)是函数式编程的重要的语法结构，Python也支持这一特性。在一个外函数中定义了一个内函数，内函数里运用了外函数的临时变量，并且外函数的返回值是内函数的引用地址（）。这样就构成了一个闭包。

闭包构成条件：
（1）必须有一个内嵌函数（函数里定义的函数）——这对应函数之间的嵌套
（2）内嵌函数必须引用一个定义在闭合范围内（外部函数里）的变量——内部函数引用外部变量
（3）外部函数必须返回内嵌函数——必须返回那个内部函数

闭包的基本使用：

def function_out(num):
    """外层函数"""
    print('function_out num = ', num)
    def function_in(num_in):
        """里层函数"""
        print('---function_in---num=', num)
        print('---function_in---num_in=', num_in)

    return function_in

# function_out(10)    # function_out num =  10

# 调用 function_out，获取内层函数的地址，保存到ret
ret = function_out(100)
# 调用里层函数
ret(88)

闭包中变量问题：
内层函数定义了和外层函数同名的变量：内层函数优先使用内层函数定义的变量，即使定义变量的代码再内层行数的最后面。
此时需要使用外层变量的解决方法：使用nonlocal关键字进行约束。

def function_out(num):
    """外层函数"""
    print('function_out num = ', num)
    def function_in():
        """里层函数"""
        # 如果在内层定义了和外层同名的变量，且需要使用外层的变量，需定义nonlocal
        nonlocal num
        print('---function_in---num=', num)
        # 内部自定义的变量
        num = 88      # 无nonlocal num运行报错。编译器认为内层函数已经定义了num变量，优先使用内层

    return function_in

# 调用 function_out，获取内层函数的地址，保存到ret
ret = function_out(100)
# 调用里层函数
ret()

装饰器

装饰器： 在不改变函数的代码前提下，给函数添加新的功能。
装饰器使用前提：
（1）存在闭包（用于拓展新的功能）
（2）待拓展的普通函数（目的就是不改变该函数，还增加新的功能）
装饰器的语法格式：

@闭包外层函数XXX# 此处的作用是使用XXX闭包 装饰 func_xxx函数，给其扩展功能
def func_xxx():
...

基本使用：

"""
目标：给login()函数增加验证功能，而且不能修改源代码
"""

def function_out(func):
    def function_in():
        print('开始验证...')
        func()
    return function_in

@function_out
# @function_out装饰了login()函数，底层：login = function_out(login)
def login():
    print('开始登录！')

# login = function_out(login)   #装饰器闭包写法，等价于@function_out
login()

运行结果如下：

开始验证...
开始登录！

装饰有参数的函数：

"""
装饰有（固定）参数的函数
"""
def function_out(func):
    def function_in(user):
        print('用户%s开始验证...' % user)
        # 执行待装饰的函数
        # func(user) == login(user)
        func(user)
    return function_in

@function_out
# login = function_out(login)
def login(user):
    print('用户%s开始登录！'% user)

# 装饰后，login==function_in
login('Lily')

运行结果如下：

用户Lily开始验证...
用户Lily开始登录！

"""
装饰存在可变参数的函数
"""
def function_out(func):
    def function_in(*args, **kwargs):
        print('开始验证...')
        print('function_in:args=', args)
        print('function_in:kwargs=', kwargs)
        # 执行待装饰的函数
        func(*args, **kwargs)
    return function_in

@function_out
# login = function_out(login)
def login(*args, **kwargs):
    print('开始登录！')
    print('login:args=', args)
    print('login:kwargs=', kwargs)
    return

# 装饰后，login==function_in
login(10, a=20)

运行结果如下：

开始验证...
function_in:args= (10,)
function_in:kwargs= {'a': 20}
开始登录！
login:args= (10,)
login:kwargs= {'a': 20}

装饰有返回值的函数：

def function_out(func):
    def function_in(num):
        print('开始验证...')
        # 执行待装饰的函数
        return func(num)
    return function_in

@function_out
# login = function_out(login)
def login(num):
    print('开始登录！')
    return num + 10

# 装饰后，login(8)==function_in(8)
result = login(8)
print(result)

运行结果如下：

开始验证...
开始登录！
18

装饰器通用版：

# 闭包函数funciton_out为外层
def function_out(func):
    # function_in 为内层函数
    def function_in(*args, **kwargs):
        print('正在进行验证...')
        # func --> login
        return func(*args, **kwargs)
    return  function_in

# 定义login函数
# login = function_out(login)
@function_out
def login(*args, **kwargs):
    print('开始登录！')
    return args[0]+100

# 调用login函数
result = login(10, 20, c=100)
print(result)

运行结果如下：

正在进行验证...
开始登录！
110

原文地址：https://blog.csdn.net/weixin_49026134/article/details/135361683

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！