MySQL

参考：

数据库

数据库，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。
所谓“数据库”是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。

MySQL数据库

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

Mysql是开源的，所以你不需要支付额外的费用。
Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

应用环境

与其他的大型数据库例如 Oracle、DB2、SQL Server等相比，MySQL [1] 自有它的不足之处，但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于 MySQL是开放源码软件，因此可以大大降低总体拥有成本。
Linux作为操作系统，Apache 或Nginx作为 Web 服务器，MySQL 作为数据库，PHP/Perl/Python作为服务器端脚本解释器。由于这四个软件都是免费或开放源码软件（FLOSS)，因此使用这种方式不用花一分钱（除开人工成本）就可以建立起一个稳定、免费的网站系统，被业界称为“LAMP“或“LNMP”组合。

数据库管理软件分类

分两大类：
　　关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
　　非关系型：mongodb，redis，memcache

总结两句话：
    关系型数据库需要有表结构
    非关系型数据库是key-value存储的，没有表结构

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

RDBMS 术语
在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

数据库: 数据库是一些关联表的集合。.
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQl安装及基本操作

windows版本，官网下载https://dev.mysql.com/downloads/mysql/,选择 MySQL Community Server 5.6 »或MySQL Community Server 5.5 » （较为稳定）
针对操作系统的不同下载不同的版本
解压
添加环境变量，添加到系统环境变量中
初始化，mysqld --initialize-insecure
启动mysql服务,mysqld
启动mysql客户端并连接mysql服务端（新开一个cmd窗口）mysql -u root -p # 连接MySQL服务器
将MySQL服务制作成windows服务

注意：--install前，必须用mysql启动命令的绝对路径
# 制作MySQL的Windows服务，在终端执行此命令：
"c:\mysql-5.6.40-winx64\bin\mysqld" --install

# 移除MySQL的Windows服务，在终端执行此命令：
"c:\mysql-5.6.40-winx64\bin\mysqld" --remove


注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令：
# 启动MySQL服务
net start mysql

# 关闭MySQL服务
net stop mysql

windows下登录设置密码

mysql # 普通登录
mysql -uroot -p # 管理员登录
select user(); # 查看当前登录的账号
C:\mysqladmin -uroot -p password "123"  # 设置密码
C:\mysqladmin -uroot -p"123" password "456" # 重设密码

忘记密码

管理员方式打开cmd
停掉mysql服务，net stop mysql
跳过授权表，mysqld --skip-grant-tables
再次查看，这是可以直接登录，不需要输入密码
修改密码，update mysql.user set password =password("新密码") where User='root'; #5.6版本
刷新权限，flush privileges;
退出mysql,exit
结束mysql进程，

tasklist |findstr mysql  #查看当前mysql的进程 
taskkill /F /PID 6052  # 杀死当前的进程pid

9 . 重启服务，密码修改完成。net start mysql

统一字符编码

进入mysql，执行\s，可查看当前编码

为了统一字符编码，请执行如下操作：

在C:\mysql-5.6.40-winx64文件下创建my.ini文件，my.ini文件是mysql的配置文件。
将如下代码拷贝保存

[mysqld]
# 设置mysql的安装目录 **后面的路径一定是安装sql的目录（自己电脑的）**
basedir=C:\mysql-5.7.22-winx64\mysql-5.7.22-winx64
# 设置mysql数据库的数据的存放目录，必须是data
datadir=C:\mysql-5.7.22-winx64\mysql-5.7.22-winx64\data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# mysql端口
port=3306
# 字符集
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

3 .以管理员身份重启服务,net stop mysql;net start mysql
4 .在cmd中输入mysql进入mysql环境，执行\s,检测，如修改为utf8，则表示成功。

基本的mysql语句

数据库

增，create database db1 charset utf8;
删，drop database db1;
改，show databases;# 查看所有数据库 show create database db1 # 查看当前数据库
查，alter database db1 charset gbk;

表

use db1; # 切换数据库
select database(); # 查看当前所在数据库

增，create table t1(id int,name char) ;
删，drop table t1;
改

# modify
alter table t1 modify name char(6);
# 改变name为大写的NAME
alter table t1 change name NAMA char(7);

#查看当前的这张t1表
show create table t1;

# 查看所有的表
show tables;

# 查看表的详细信息
desc t1;

表中数据

增，insert into t1(id,name) values(1,"zhangsan"),(2,"lisi");
删，delete from t1
改，update t1 set name="wangwu" where name="lisi";
查，select * from t1; select id from t1;

数据类型

mysql常用数据类型概括：

数值
字符串
时间类型
枚举类型和集合类型

数值类型

tinyint[(m)] [unsigned] [zerofill]
int[(m)][unsigned][zerofill]
bigint[(m)][unsigned][zerofill]
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
decimal[(m[,d])] [unsigned] [zerofill]

参数解释：
m，显示宽度，并不是存宽度
unsigned，无符号类型
zerofill，用0填充
M，数字全长，float，M最大值为255
D，小数点后个数，D最大值为30

日期类型

YEAR，YYYY（1901/2155）
DATE，YYYY-MM-DD（1000-01-01/9999-12-31）
TIME， HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME ，YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）
TIMESTAMP， YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037），时间戳

注意：

单独插入时间时，需要以字符串的形式，按照对应的格式插入
插入年份时，尽量使用4位值
插入两位年份时，<=69，以20开头，比如50, 结果2050

datetime与timestamp的区别

在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。

DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，
操作系统以及客户端连接都有时区的设置。
DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），
如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

字符类型

char类型，定长，浪费空间，存取速度快
varchar类型，变长，节省空间，存取速度慢

注意
char和varchar括号内的参数指的都是字符的长度
varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
length()：查看字节数，char_length():查看字符数

枚举类型和集合类型

enum 单选只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

注意:在sql中使用tinyint(1)来表示boolean类型

create table consumer(
id int,
name varchar(50),
sex enum('male','female','other'),
level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一
fav set('play','music','read','study') #在指定范围内，多选多
);

完整性约束

作用：用于保证数据的完整性和一致性
约束条件与数据类型的宽度一样，都是可选参数

PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录，一个表中只有一个，等价于 not null unique，存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键
FOREIGN KEY (FK) #标识该字段为该表的外键
NOT NULL #标识该字段不能为空
UNIQUE KEY (UK) #标识该字段的值是唯一的，UK可以为多个参数，联合唯一。
AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT #为该字段设置默认值
UNSIGNED #无符号
ZEROFILL #使用0填充

注意：

auto_increment_increment（起始的偏移量，默认为1）和 auto_increment_offset（起始偏移量，默认为1），
delete和truncate的区别
- delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
- truncate table t1;数据量大，删除速度比上一条快，且直接从零开始。
foreign key,级联删除、更新，在关联表中添加on delete cascade #同步删除 on update cascade #同步更新

内置函数
- password()
- length() 获取字节数
- char_length() 获取字符数
- now() 根据数据类型来获取当前时间

创建用户和授权

进入到mysql数据库下
use mysql
对新用户增删改

# 1.创建用户:
# 指定ip：192.118.1.1的用户登录
create user 's1'@'192.118.1.1' identified by '123';
# 指定ip：192.118.1.开头的用户登录
create user 's1'@'192.118.1.%' identified by '123';
# 指定任何ip的用户登录
create user 's1'@'%' identified by '123';

# 2.删除用户
drop user '用户名'@'IP地址';


# 3.修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

# 4.修改密码
set password for '用户名'@'IP地址'=Password('新密码');

对当前的用户授权管理

#查看权限
show grants for '用户'@'IP地址';

#授权 s1用户仅对db1.t1文件有查询、插入和更新的操作
grant select ,insert,update on db1.t1 to "s1"@'%';

# 表示有所有的权限，除了grant这个命令，这个命令是root才有的。s1用户对db1下的t1文件有任意操作
grant all privileges  on db1.t1 to "s1"@'%';
# s1用户对db1数据库中的文件执行任何操作
grant all privileges  on db1.* to "s1"@'%';
# s1用户对所有数据库中文件有任何操作
grant all privileges  on *.*  to "s1"@'%';
 
#取消权限
 
# 取消s1用户对db1的t1文件的任意操作
revoke all on db1.t1 from 's1'@"%";  

# 取消来自远程服务器的s1用户对数据库db1的所有表的所有权限

revoke all on db1.* from 's1'@"%";  

# 取消来自远程服务器的s1用户所有数据库的所有的表的权限
revoke all privileges on *.* from 's1'@'%';

MySql备份命令行操作

# 备份：数据表结构+数据
mysqdump -u root db1 > db1.sql -p


# 备份：数据表结构
mysqdump -u root -d db1 > db1.sql -p

#导入现有的数据到某个数据库
# 1.先创建一个新的数据库
create database db10;
# 2.将已有的数据库文件导入到db10数据库中
mysqdump -u root -d db10 < db1.sql -p

表与表的关系

多对一
多对多
一对一

分析步骤：
使用ER图分析

单表查询

单表查询的语法：

SELECT 字段1,字段2... FROM 表名
              WHERE 条件
              GROUP BY field
              HAVING 筛选
              ORDER BY field
              LIMIT 限制条数

关键字的执行优先级（重点）

from
where
group by
having
select
distinct
order by
limit

找到表:from
拿着where指定的约束条件，去文件/表中取出一条条记录
将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
将分组的结果进行having过滤
执行select
去重
将结果按条件排序：order by
限制结果的显示条数

where 约束,where子句中可以使用

比较运算符：>、<、>=、<=、<>、!=
between 80 and 100 ：值在80到100之间
in(80,90,100)值是10或20或30
like 'pattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符
逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

group by 分组查询

首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
为何要分组呢？
- 取每个部门的最高工资
- 取每个部门的员工数
- 取男人数和女人数
  小窍门：‘每’这个字后面的字段，就是我们分组的依据
大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

聚合函数

max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数

HAVING过滤
HAVING与WHERE不一样的地方在于:执行优先级从高到低：where > group by > having

Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

order by 查询排序


# 按单列排序
    SELECT * FROM employee ORDER BY age;
    SELECT * FROM employee ORDER BY age ASC;
    SELECT * FROM employee ORDER BY age DESC;
# 按多列排序:先按照age升序排序，如果年纪相同，则按照id降序
    SELECT * from employee
        ORDER BY age ASC,
        id DESC;

limit 限制查询的记录数

    SELECT * FROM employee ORDER BY salary DESC 
     LIMIT 3;                    #默认初始位置为0 

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

多表查询

多表连接查询

语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

交叉连接,生成笛卡尔积,select * from employee,department;
内连接：只连接匹配的行，select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
左连接：优先显示左表全部记录,select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
右连接：优先显示右表全部记录,select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
全外连接：显示左右两个表全部记录，在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

select * from employee left join department on employee.dep_id = department.id 
   union all
  select * from employee right join department on employee.dep_id = department.id;

select * from employee left join department on employee.dep_id = department.id
      union
    select * from employee right join department on employee.dep_id = department.id;

注意：
左连接本质，在内连接的基础上增加左边有，右边没有的结果
内连接，找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了匹配的结果
mysql不支持全外连接 full JOIN
mysql可以使用此种方式间接实现全外连接
union与union all的区别：union会去掉相同的纪录

符合条件连接查询

先约束表之后，再进行连接。

select employee.name,department.name from employee inner join department
　　on employee.dep_id = department.id
　　where age > 25;

子查询

子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等

带in关键字的子查询
select name,age from employee where age > (select avg(age) from employee);
带比较运算符的子查询
比较运算符：=、!=、>、>=、<、<=、<>
select name,age from employee where age > (select avg(age) from employee);
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
select * from employee where exists (select id from department where id=200);

索引

索引介绍

数据库中专门用于帮助用户快速查找数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置吗，然后直接获取。

索引的作用

约束和加速查找

常见的几种索引

普通索引
唯一索引
主键索引
联合索引（多列）
- 联合主键索引
- 联合唯一索引　　
- 联合普通索引

无索引和有索引的区别以及建立索引的目的
无索引：从前往后一条一条查询
有索引：创建索引的本质，就是创建额外的文件（某种格式存储，查询的时候，先去格外的文件找，定好位置，然后再去原始表中直接查询。但是创建索引越多，会对硬盘也是有损耗。

建立索引的目的：

额外的文件保存特殊的数据结构
查询快，但是插入更新删除依然慢
创建索引之后，必须命中索引才能有效

hash索引和BTree索引

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

普通索引

作用：仅有一个加速查找

create table userinfo(
                   nid int not null auto_increment primary key,
                   name varchar(32) not null,
                   email varchar(64) not null,
                   index ix_name(name)
               );

普通索引，create index 索引的名字 on 表名(列名)
删除索引，drop index 索引的名字 on 表名
查看索引，show index from 表名

唯一索引

唯一索引有两个功能：加速查找和唯一约束（可含null）
create unique index 索引名 on 表名(列名)

  create table userinfo(
                   id int not null auto_increment primary key,
                   name varchar(32) not null,
                   email varchar(64) not null,
                   unique  index  ix_name(name)
               );

主键索引

主键索引有两个功能：加速查找和唯一约束（不含null）
主键索引一定是唯一索引，唯一索引不一定是主键索引。

创建索引
alter table 表名 add primary key(列名);
删除
alter table 表名 drop primary key;

组合索引

组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询。
create index 索引名 on 表名(列名1,列名2);

索引名词

覆盖索引：在索引文件中直接获取数据，select name from userinfo where name = '233';
索引合并：把多个单列索引合并成使用, select * from userinfo where name = '233' and id = 233;
最左前缀

最左前缀匹配：
create index ix_name_email on userinfo(name,email);
         select * from userinfo where name = '233';
         select * from userinfo where name = '233' and email='233';

         select * from userinfo where  email='233';

     如果使用组合索引如上，name和email组合索引之后，查询
     （1）name和email ---使用索引
     （2）name        ---使用索引
     （3）email       ---不适用索引
      对于同时搜索n个条件时，组合索引的性能好于多个单列索引
　　　 ******组合索引的性能>索引合并的性能*********

正确使用索引的情况

创建索引
命中索引
正确使用索引

准备300W条数据

# 1. 准备表
create table userinfo(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

# 2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into userinfo values(i,concat('pig',i),'male',concat('gg',i,'@233'));
        set i=i+1;
    end while;
END  # $$结束
delimiter ; #重新声明分号为结束符号

# 3. 查看存储过程
show create procedure auto_insert1\G 

# 4. 调用存储过程
call auto_insert1();

测试

- like '%xx'
    select * from userinfo where name like '%g';
- 使用函数
    select * from userinfo where reverse(name) = 'pig333';
- or
    select * from userinfo where id = 1 or email = 'gg@233';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from userinfo where id = 1 or name = 'pig1222';
            select * from userinfo where id = 1 or email = 'gg@233' and name = 'pig112'
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from userinfo where name = 999;
- !=
    select count(*) from userinfo where name != 'pig'
    特别的：如果是主键，则还是会走索引
        select count(*) from userinfo where id != 123
- >
    select * from userinfo where name > 'pig'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from userinfo where id > 123
        select * from userinfo where num > 123
- order by
    select email from userinfo order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from userinfo order by nid desc;
 
- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

索引的注意事项

避免使用select *
count(1)或count(列) 代替count(*)
创建表时尽量使用char代替varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引（经常使用多个条件查询时）
尽量使用短索引（create index ix_title on tb(title(16));特殊的数据类型 text类型）
使用连接（join）来代替子查询
连表时注意条件类型需一致
索引散列（重复少）不适用于建索引，例如：性别不合适

执行计划

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

慢日志记录

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

分页性能相关方案

# （1）只有上一页和下一页
#     做一个记录：记录当前页的最大id或最小id
#     下一页：
    select * from userinfo where id>max_id limit 10;

#     上一页：
    select * from userinfo where id<min_id order by id desc limit 10;


# (2) 中间有页码的情况
       select * from userinfo where id in(
           select id from (select * from userinfo where id > pre_max_id limit (cur_max_id-pre_max_id)*10) as A order by A.id desc limit 10
       );

pymysql模块

pymysql的下载和使用

之前我们都是通过MySQL自带的命令行客户端工具mysql来操作数据库，那如何在python程序中操作数据库呢？这就用到了pymysql模块，该模块本质就是一个套接字客户端软件，使用前需要事先安装。
使用pip或者pycharm包管理器下载

简单使用

import pymysql

username = input('请输入用户名:')
pwd = input('请输入密码：')

# 连接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password="",
    database='db3',
    port=3306,
    charset='utf8',
)
# 创建游标
cur = conn.cursor()
sql = "select * from userinfo where username = '%s' and pwd = '%s'" %(username,pwd)
print(sql)
# 执行sql语句
count = cur.execute(sql)
print(count)

if count:
    print('登录成功')
else:
    print('登录失败')

execute()之sql注入

sql注入，通过拼接sql语句,改变命令原意，可造成重大危害。

import pymysql

user = input('请输入用户名：')

pwd = input('请输入密码：')

# 1.连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='db3', charset='utf8')

# 2.创建游标
cursor = conn.cursor()

# 注意%s需要加引号
# sql = "select * from userinfo where username='%s' and pwd='%s'" % (user, pwd)
# 如果用户输入的username是 asd ' or 1=1 -- ，将会改变sql语句，导致sql注入
# 解决方法：参数化
sql = "select * from userinfo where name = %(name)s and pwd = %(password)s"
# count = cursor.execute(sql,[user,pwd])
count = cursor.execute(sql, {"name": user, "password": pwd})
print(sql)

# 3.执行sql语句
cursor.execute(sql)

result = cursor.execute(sql)  # 执行sql语句，返回sql查询成功的记录数目
print(result)

# 关闭连接，游标和连接都要关闭
cursor.close()
conn.close()

if result:
    print('登陆成功')
else:
    print('登录失败')

增、删、改：conn.commit()

commit()方法：在数据库里增、删、改的时候，必须要进行提交，否则插入的数据不生效。

import pymysql

username = input('请输入用户名：')

pwd = input('请输入密码：')

# 1.连接
conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db3', charset='utf8')

# 2.创建游标
cursor = conn.cursor()

# 操作
# 增
# sql = "insert into userinfo(username,pwd) values (%s,%s)"


# effect_row = cursor.execute(sql,(username,pwd))
# 同时插入多条数据
# cursor.executemany(sql,[('李四'，'110'),（'王五'，'119'）]) 

# print(effect_row)#

# 改
# sql = "update userinfo set username = %s  where id = 2"
# effect_row = cursor.execute(sql,username)
# print(effect_row)


# 删
sql = "delete from userinfo  where id = 2"
effect_row = cursor.execute(sql)
print(effect_row)

# 一定记得commit
conn.commit()

# 4.关闭游标
cursor.close()

# 5.关闭连接
conn.close()

查：fetchone、fetchmany、fetchall

fetchone():获取下一行数据，第一次为首行；
fetchall():获取所有行数据源
fetchmany(4):获取4行数据

默认情况下，我们获取到的返回值是元组，只能看到每行的数据，却不知道每一列代表的是什么，这个时候可以使用以下方式来返回字典，每一行的数据都会生成一个字典：
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #在实例化的时候，将属性cursor设置为pymysql.cursors.DictCursor

cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
第一个值为移动的行数，整数为向下移动，负数为向上移动，mode指定了是相对当前位置移动，还是相对于首行移动

import pymysql

# 1.连接
conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db3', charset='utf8')

# 2.创建游标
cursor = conn.cursor()

sql = 'select * from userinfo'
cursor.execute(sql)

# 查询第一行的数据
row = cursor.fetchone()
print(row)  # (1, 'mjj', '123')

# 查询第二行数据
row = cursor.fetchone()
print(row)  # (3, '张三', '110')

# 4.关闭游标
cursor.close()

# 5.关闭连接
conn.close()

posted @ 2019-02-06 23:43 写bug的日子阅读(121) 评论(0) 编辑收藏举报

刷新页面返回顶部

写bug的日子