Mysql基础使用

1.分类：
关系型数据库：
Oracle
Microsoft SQL Server
MySQL
SQLite
非关系型数据库：（强调key-value的方式存储）
MongoDB
Redis

2.数据库的特点
持久化存储
读写速度极高
保证数据的有效性

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

命令：

MySQL数据库服务端软件的安装:
sudo apt-get install mysql-server

ps说明
ps 查看当前系统中的进程 ps -ef |grep redis 查看redis的进程
-a 表示所有用户
-u 表示显示用户名
-x 表示显示所有的执行程序
查看MySQL服务状态:
sudo service mysql status

停止MySQL服务:
sudo service mysql stop

启动MySQL服务:
sudo service mysql start

重启MySQL服务:
sudo service mysql restart
MySQL配置文件的介绍:
配置文件路径为: /etc/mysql/mysql.conf.d/mysqld.cnf

主要配置信息说明:
port表示端口号，默认为3306

bind-address表示服务器绑定的ip，默认为127.0.0.1

datadir表示数据库保存路径，默认为/var/lib/mysql

log_error表示错误日志，默认为/var/log/mysql/error.log

mysql命令的使用帮助:
mysql --help

MySQL客户端的使用:
MySQL客户端连接MySQL服务端命令
mysql -uroot -p

说明:
-u: 表示MySQL服务端的用户名
-p: 表示MySQL服务端的密码
quit 或者 exit 或者 ctr + d 表示退出

显示当前时间

select now();

数据类型

常用数据类型如下:
整数：int，bit
小数：decimal
字符串：varchar,char
日期时间: date, time, datetime
枚举类型(enum)
数据类型说明:
decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位.
char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为’ab ‘，3表示字符数
varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’，3表示字符数
对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径.
字符串 text 表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.

数据约束（约束是指数据在数据类型限定的基础上额外增加的要求）
常见的约束如下:
主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
非空 not null: 此字段不允许填写空值.
惟一 unique: 此字段的值不允许重复.
默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

数据库操作的SQL语句

1.查看所有数据库
show databases;

2.创建数据库
create database 数据库名 charset=utf8;
例：
create database python charset=utf8;

3.使用数据库
use 数据库名;

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

5.删除数据库-慎重
drop database 数据库名;
例：
drop database python;

3. 表结构操作的SQL语句
   1.查看当前数据库中所有表
   show tables;

2.创建表
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) not null,
age tinyint unsigned default 0,
height decimal(5,2),
gender enum(‘男’,‘女’,‘人妖’,‘保密’)
);

说明:
create table 表名(
字段名称 数据类型 可选的约束条件,
column1 datatype contrai,
…
);

1.修改表-添加字段
alter table 表名 add 列名 类型 约束;
例：
alter table students add birthday datetime;

2.修改表-修改字段类型
alter table 表名 modify 列名 类型 约束;
例：
alter table students modify birthday date not null;

说明:
modify: 只能修改字段类型或者约束，不能修改字段名

1.修改表-修改字段名和字段类型
alter table 表名 change 原名 新名 类型及约束;
例：
alter table students change birthday birth datetime not null;

说明:
change: 既能对字段重命名又能修改字段类型还能修改约束

1.修改表-删除字段
alter table 表名 drop 列名;
例：
alter table students drop birthday;

2.查看创表SQL语句
show create table 表名;
例：
show create table students;

3.查看创库SQL语句
show create database 数据库名;
例：
show create database mytest;

5.删除表
drop table 表名;
例：
drop table students;

2.表数据操作的SQL语句
1.查询数据
– 1. 查询所有列
select * from 表名;
例：
select * from students;
– 2. 查询指定列
select 列1,列2,… from 表名;
例：
select id,name from students;

2.添加数据
– 1. 全列插入：值的顺序与表结构字段的顺序完全一一对应
insert into 表名 values (…)
例:
insert into students values(0, ‘xx’, default, default, ‘男’);
– 2. 部分列插入：值的顺序与给出的列顺序对应
insert into 表名 (列1,…) values(值1,…)
例:
insert into students(name, age) values(‘王二小’, 15);
– 3. 全列多行插入
insert into 表名 values(…),(…)…;
例:
insert into students values(0, ‘张飞’, 55, 1.75, ‘男’),(0, ‘关羽’, 58, 1.85, ‘男’);
– 4. 部分列多行插入
insert into 表名(列1,…) values(值1,…),(值1,…)…;
例：
insert into students(name, height) values(‘刘备’, 1.75),(‘曹操’, 1.6);

说明:
主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null或者default)
在全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值

1.修改数据
update 表名 set 列1=值1,列2=值2… where 条件
例：
update students set age = 18, gender = ‘女’ where id = 6;

2.删除数据
delete from 表名 where 条件
例：
delete from students where id=5;

问题:
上面的操作称之为物理删除，一旦删除就不容易恢复，我们可以使用逻辑删除的方式来解决这个问题。
– 添加删除表示字段，0表示未删除 1表示删除
alter table students add isdelete bit default 0;
– 逻辑删除数据
update students set isdelete = 1 where id = 8;

as和distinct关键字

在使用SQL语句显示结果的时候，往往在屏幕显示的字段名并不具备良好的可读性，此时可以使用 as 给字段起一个别名。
1.as关键字（使用 as 给字段起别名）
select id as 序号, name as 名字, gender as 性别 from students;

可以通过 as 给表起别名
– 如果是单表查询 可以省略表名
select id, name, gender from students;

– 表名.字段名
select students.id,students.name,students.gender from students;

– 可以通过 as 给表起别名
select s.id,s.name,s.gender from students as s;

2. distinct关键字
   distinct可以去除重复数据行。
   select distinct 列1,… from 表名;

例： 查询班级中学生的性别
select name, gender from students;

– 看到了很多重复数据 想要对其中重复数据行进行去重操作可以使用 distinct
select distinct name, gender from students;

查询

1.where条件查询语法格式如下:
select * from 表名 where 条件;
例：
select * from students where id = 1;

2. 比较运算符查询
   等于: =
   大于: >
   大于等于: >=
   小于: <
   小于等于: <=
   不等于: != 或 <>
   例1：查询编号大于3的学生:
   select * from students where id > 3;

例2：查询编号不大于4的学生:
select * from students where id <= 4;

例3：查询姓名不是“黄蓉”的学生:
select * from students where name != ‘黄蓉’;

例4：查询没被删除的学生:
select * from students where is_delete=0;

3. 逻辑运算符查询
   and
   or
   not
   例1：查询编号大于3的女同学:
   select * from students where id > 3 and gender=0;

例2：查询编号小于4或没被删除的学生:
select * from students where id < 4 or is_delete=0;

例3：查询年龄不在10岁到15岁之间的学生:
select * from students where not (age >= 10 and age <= 15);

说明:
多个条件判断想要作为一个整体，可以结合‘()’。

4. 模糊查询
   like是模糊查询关键字
   %表示任意多个任意字符
   _表示一个任意字符
   例1：查询姓黄的学生:
   select * from students where name like ‘黄%’;

例2：查询姓黄并且“名”是一个字的学生:
select * from students where name like ‘黄_’;

例3：查询姓黄或叫靖的学生:
select * from students where name like ‘黄%’ or name like ‘%靖’;

5. 范围查询
   between … and … 表示在一个连续的范围内查询
   in 表示在一个非连续的范围内查询
   例1：查询编号为3至8的学生:
   select * from students where id between 3 and 8;

例2：查询编号不是3至8的男生:
select * from students where (not id between 3 and 8) and gender=‘男’;

6. 空判断查询
   判断为空使用: is null
   判断非空使用: is not null
   例1：查询没有填写身高的学生:
   select * from students where height is null;

注意:
不能使用 where height = null 判断为空
不能使用 where height != null 判断非空
null 不等于 ‘’ 空字符串

排序（order by）

1. 排序查询语法
   排序查询语法：
   select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…]

语法说明:
先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推
asc从小到大排列，即升序
desc从大到小排序，即降序
默认按照列值从小到大排列（即asc关键字）
例1：查询未删除男生信息，按学号降序:
select * from students where gender=1 and is_delete=0 order by id desc;

例2：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序:
select * from students order by age desc,height desc;

分页查询（limit）

select * from 表名 limit start,count

说明:
limit是分页查询关键字
start表示开始行索引，默认是0
count表示查询条数
例1：查询前3行男生信息:
select * from students where gender=1 limit 0,3;
简写
select * from students where gender=1 limit 3;

聚合函数

特点：聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。
常用的聚合函数:
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值

求总行数
– 返回非NULL数据的总行数.
select count(height) from students;
– 返回总行数，包含null值记录;
select count(*) from students;

求最大值
– 查询女生的编号最大值
select max(id) from students where gender = 2;

求最小值
– 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

求和
– 查询男生的总身高
select sum(height) from students where gender = 1;
– 平均身高
select sum(height) / count(*) from students where gender = 1;

求平均值
– 求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
– 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;

说明
ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。

分组查询

分组查询基本的语法格式如下：
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
说明:
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

group by的使用
group by可用于单个字段分组，也可用于多个字段分组
– 根据gender字段来分组
select gender from students group by gender;
– 根据name和gender字段进行分组
select name, gender from students group by name, gender;

group by + group_concat()的使用
group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
– 根据gender字段进行分组， 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;

group by + 聚合函数的使用
聚合函数在和 group by 结合使用时, 聚合函数统计和计算的是每个分组的数据
– 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
– 统计不同性别的人的个数
select gender,count(*) from students group by gender;

group by + having的使用
having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
– 根据gender字段进行分组，统计分组条数大于2的
select gender,count() from students group by gender having count()>2;

group by + with rollup的使用
with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
– 根据gender字段进行分组，汇总总人数
select gender,count(*) from students group by gender with rollup;
– 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;

连接查询

内连接查询
内连接使用inner join … on …, on 表示两个表的连接查询条件
内连接根据连接查询条件取出两个表的 “交集”
内连接查询效果图:

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
说明:
inner join 就是内连接查询关键字
on 就是连接查询条件
例1：使用内连接查询学生表与班级表:
select * from students as s inner join classes as c on s.cls_id = c.id;

左连接查询
左连接使用left join … on …, on 表示两个表的连接查询条件
左连接以左表为主根据条件查询右表数据，右表数据不存在使用null值填充。
左连接查询效果图:

select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2
说明:
left join 就是左连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表
例1：使用左连接查询学生表与班级表:
select * from students as s left join classes as c on s.cls_id = c.id;

右连接查询
右连接使用right join … on …, on 表示两个表的连接查询条件
右连接以右表为主根据条件查询左表数据，左表数据不存在使用null值填充。
右连接查询效果图:

右连接查询语法格式:
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2

说明:
right join 就是右连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表
例1：使用右连接查询学生表与班级表:
select * from students as s right join classes as c on s.cls_id = c.id;

自连接查询（必须对表起别名）
自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
自连接就是一种特殊的连接方式，连接的表还是本身这张表

左表和右表是同一个表，根据连接查询条件查询两个表中的数据。
创建areas表:
create table areas(
id varchar(30) not null primary key,
title varchar(30),
pid varchar(30)
);

执行sql文件给areas表导入数据:
source areas.sql;

说明:
source 表示执行的sql文件
自连接查询的用法:
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 = ‘山西省’;

子查询

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

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

2. 子查询的使用
   例1. 查询大于平均年龄的学生:
   select * from students where age > (select avg(age) from students);

例2. 查询学生在班的所有班级名字:
select name from classes where id in (select cls_id from students where cls_id is not null);

例3. 查找年龄最大,身高最高的学生:
select * from students where (age, height) = (select max(age), max(height) from students);

外键SQL语句的编写

1.添加外键约束: alter table 从表 add foreign key(外键字段) references主表(主键字段);
2.删除外键约束: alter table 表名 drop foreign key 外键名;

1. 外键约束作用
   外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

2. 对于已经存在的字段添加外键约束
   – 为cls_id字段添加外键约束
   alter table students add foreign key(cls_id) references classes(id);

3. 在创建数据表时设置外键约束
   – 创建学校表
   create table school(
   id int not null primary key auto_increment,
   name varchar(10)
   );

– 创建老师表
create table teacher(
id int not null primary key auto_increment,
name varchar(10),
s_id int not null,
foreign key(s_id) references school(id)
);

4. 删除外键约束
   – 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
   show create table teacher;

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

演练-分组和聚合函数的组合使用

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

– 向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 台式电脑 linux ）’,‘台式机’,‘宏碁’,‘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);

表结构说明:
id 表示主键 自增
name 表示商品名称
cate_name 表示分类名称
brand_name 表示品牌名称
price 表示价格
is_show 表示是否显示
is_saleoff 表示是否售完

2. SQL语句演练
   查询类型cate_name为 ‘超极本’ 的商品名称、价格
   select name,price from goods where cate_name = ‘超级本’;

显示商品的分类
select cate_name from goods group by cate_name;

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

显示每种商品的平均价格
select cate_name,avg(price) from goods group by cate_name;

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

查询所有价格大于平均价格的商品，并且按价格降序排序
select id,name,price from goods
where price > (select round(avg(price),2) as avg_price from goods)
order by price desc;

3.将查询结果插入到其它表中
insert into … select … 表示: 把查询结果插入到指定表中，也就是表复制
创建商品分类表
– 创建商品分类表
create table good_cates(
id int not null primary key auto_increment,
name varchar(50) not null
);

把goods表中的商品分类添加到商品分类表
– 查询goods表中商品的分类信息
select cate_name from goods group by cate_name;

– 将查询结果插入到good_cates表中
insert into good_cates(name) select cate_name from goods group by cate_name;

– 添加移动设备分类信息
insert into good_cates(name) values(‘移动设备’);

4.使用连接更新表中某个字段数据
连接更新表中数据使用: update … join … 语句
将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;

5.创建表并给某个字段添加数据
create table … select 列名 … 表示创建表并插入数据
创建品牌表
– 查询品牌信息
select brand_name from goods group by brand_name;

– 通过create table …select来创建数据表并且同时插入数据
– 创建商品分类表，注意: 需要对brand_name 用as起别名，否则name字段就没有值
create table good_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null) select brand_name as name from goods group by brand_name;

更新goods表中的品牌信息
– 将goods表中的品牌名称更改成品牌表中对应的品牌id
update goods as g inner join good_brands gb on g.brand_name = gb.name set g.brand_name = gb.id;

6.修改goods表结构
修改表结构可以使用: alter table 语句，多个修改字段之间使用逗号分隔
目前我们已经把good表中的商品分类和品牌信息已经更改成了商品分类id和品牌id，接下来需要把 cate_name 和 brand_name 字段分别改成 cate_id和 brand_id 字段，类型都改成int类型
– 查看表结构
desc goods;

– 通过alter table语句修改表结构
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

说明:
alert table 可以同时修改多个字段信息

PyMySQL的使用

Python程序操作MySQL数据库
安装pymysql第三方包:
sudo pip3 install pymysql

说明:
安装命令使用 sudo pip3 install 第三方包名
卸载命令使用 sudo pip3 uninstall 第三方包
大家现在使用的虚拟机已经安装了这个第三方包，可以使用： pip3 show pymysql 命令查看第三方包的信息
pip3 list 查看使用pip命令安装的第三方包列表
pymysql的使用:
导入 pymysql 包
import pymysql

创建连接对象
调用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语句，返回受影响的行数，主要用于执行insert、update、delete、select等语句
获取查询结果集中的一条数据:cur.fetchone()返回一个元组, 如 (1,‘张三’)
获取查询结果集中的所有数据: cur.fetchall()返回一个元组,如((1,‘张三’),(2,‘李四’))
关闭游标: cur.close(),表示和数据库操作完成
pymysql完成数据的查询操作
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()

pymysql完成对数据的增删改
import pymysql

创建连接对象

conn = pymysql.connect(host=‘localhost’, port=3306, user=‘root’, password=‘mysql’,database=‘python’, charset=‘utf8’)

获取游标对象

cursor = conn.cursor()

try:
# 添加 SQL 语句
# sql = “insert into students(name) values(‘刘璐’), (‘王美丽’);”
# 删除 SQ L语句
# sql = “delete from students where id = 5;”
# 修改 SQL 语句
sql = “update students set name = ‘王铁蛋’ where id = 6;”
# 执行 SQL 语句
row_count = cursor.execute(sql)
print(“SQL 语句执行影响的行数%d” % row_count)
# 提交数据到数据库
conn.commit()
except Exception as e:
# 回滚数据， 即撤销刚刚的SQL语句操作
conn.rollback()

关闭游标

cursor.close()

关闭连接

conn.close()

说明:
conn.commit() 表示将修改操作提交到数据库
conn.rollback() 表示回滚数据
防止SQL注入
什么是SQL注入?
用户提交带有恶意的数据与SQL语句进行字符串方式的拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象。
如何防止SQL注入?
SQL语句参数化
SQL语言中的参数使用%s来占位，此处不是python中的字符串格式化操作
将SQL语句中%s占位所需要的参数存在一个列表中，把参数列表传递给execute方法中第二个参数
防止SQL注入的示例代码:
from pymysql import connect

def main():

find_name = input("请输入物品名称：")

# 创建Connection连接
conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()

# 非安全的方式
# 输入 ' or 1 = 1 or '   (单引号也要输入)
# sql = "select * from goods where name='%s'" % find_name
# print("""sql===>%s<====""" % sql)
# # 执行select语句，并返回受影响的行数：查询所有数据
# count = cs1.execute(sql)

# 安全的方式
# 构造参数列表
params = [find_name]
# 执行select语句，并返回受影响的行数：查询所有数据
count = cs1.execute("select * from goods where name=%s", params)
# 注意：
# 如果要是有多个参数，需要进行参数化
# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可
# %s 不需要带引号

# 打印受影响的行数
print(count)
# 获取查询的结果
# result = cs1.fetchone()
result = cs1.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cs1.close()
# 关闭Connection对象
conn.close()

if name == ‘main’:
main()

说明:
execute方法中的 %s 占位不需要带引号

事务

1.事务的四大特性
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
原子性:
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
一致性:
数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在转账过程中系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
隔离性:
通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
持久性:
一旦事务提交，则其所做的修改会永久保存到数据库。
说明:
事务能够保证数据的完整性和一致性，让用户的操作更加安全。

2.MySQL数据库默认采用自动提交(autocommit)模式, 也就是说修改数据(insert、update、delete)的操作会自动的触发事务,完成事务的提交或者回滚

3.开启事务使用 begin 或者 start transaction;

4.回滚事务使用 rollback;

5.pymysql 里面的 conn.commit() 操作就是提交事务

6.pymysql 里面的 conn.rollback() 操作就是回滚事务