Python中的Mysql数据库

MySQL数据库安装

服务器端用于接收客户端的请求，执行sql语句，管理数据库：

sudo apt-get install mysql-server

启动服务：sudo service mysql start

查看进程中是否存在mysql服务：ps ajx|grep mysql

停止服务：sudo service mysql stop

进入数据库命令：mysql -uroot -p  # 回车后输入密码，当前设置的密码为mysql

设置密码：mysqladmin -uroot -p password "mysql"

退出：exit；

SQL语句命令

1. 数据检索 select语句

2. 数据操作 dml 语句 对数据进行增加、修改、删除 ： insert ，delete，update

3. 数据定义 ddl 语句 进行数据库、表的管理等 create， drop， alter， truncate

4. 事务操作 tcl 语句 事务处理语言，对事务进行处理 commit， rollback， savepoint

5. 数据控制 dcl 语句 进行授权与权限回收 grant revoke

常用命令：

        查看版本：select version();

        查看当前时间：select now();

        查看所有的数据库：show databases;

        使用数据库：use 数据库名；

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

        创建数据库：create database XXX;

        查看数据库下的表：show tables from 数据库名;

        查看表的信息：desc 表名；

        删除数据库：drop database 数据库名；

常用的数据类型如下

数据类型的使用原则是：够用就行，尽量使用取值范围小的，可以节省存储空间

1.整型

        tinyint(m) unsigned zerofill：

        小整数，数据类型用于保存一些范围的整数数值范围：

        有符号：-128 ～ 127

        无符号：0 ～ 255

        int(m) unsigned zerofill：

        整数，数据类型用于保存一些范围的整数数值范围：

        有符号：-2147483648 ～ 2147483647

        无符号：0 ～ 4294967295

2.浮点型

        float(M,D)  unsigned zerofill： M是全长，D是小数点后个数。M最大值为255，D最大值为30

        随着小数点增多，精度变得不准确

        double（M,D）unsigned zerofill： M是全长，D是小数点后个数。M最大值为255，D最大值为30

        随着小数点增多，精度变得不准确，但是精度比float要高

        decimal（M,D）unsigned zerofill： M是全长，D是小数点后个数。M最大值为65，D最大值为30

        随着小数点增多，精度始终准确

约束

Primary key (PK)  # 标识该字段为该表的主键，可以唯一的标识记录

Foreign key (FK)  # 标识该字段为该表的外键

NOT NULL  # 标识该字段不能为空

UNIQUE KEY (UK)  # 标识该字段的值是唯一的

AUTO_INCREMENT  # 标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT  # 为该字段设置默认值

UNSIGNED  # 无符号

ZEROFILL  # 使用0填充

1.非空约束

        列值不能为空；只能定义在列级；在创建表的时候设置非空约束

2.唯一约束

        设置字段值的唯一性（不可重复）

        唯一约束和主键约束的区别：主键字段值必须是非空的

        唯一约束允许有一个空值，唯一约束允许有多个，主键约束每张表只能有一个

        在创建表的时候设置唯一约束，列级设置

        创建表时增加 Unique 约束

3.主键约束

        一张表只能有唯一的一个主键约束，且这个字段非空，在创建表的时候设置主键

        # 修改表结构增加主键（主键名称即为主键字段另起的名字）

        alter table 表名 add constraint [主键名称] primary key (主键字段)

4.外键约束

添加方式：

        CREATE TABLE score1(

                score double,sid int,

                constraint fk_stu_score1_sid foreign key(sid) references stu(stu_sid）

        );

在关联表中加入：

        on delete cascade  # 同步删除

        on update cascade  # 同步更新

5.自动增长列

        法一：create table 表名（字段名 数据类型 auto_increment);

        法二：alter table 表名 modify 字段名 数据类型 primary key auto_increment;

修改表

注意：

        数据和结构都被删除、所有正在运行的相关事物被提交

        所有相关索引被删除、DROP TABLE 语句不能回滚

        1.添加新字段：alter table 表名 add 字段名 字段类型;

        2.修改字段（重命名）：alter table 表名 change 原名 新名 类型及约束;

        3.修改字段（非重命名）：alter table 表名 modify 字段名 修改数据类型（大小）;

        4.删除字段：alter table 表名 drop 列名;

        5.删除表：drop table 表名；

        6.查看表的创建语句：show create table 表名；

表中数据的增删改查

增加数据：

        全列插入：insert into 表名 values(...)

        部分列插入：

                法一：insert into 表名 (列1,...) values(值1，...)；

                法二：insert into class values(1,'math',85.2);

                法三：insert into class (class_id ,class_name ,class_num) values(2,'PE',60.22);

修改数据：update 表名 set 字段名=值 ，字段名=值 where 条件；

删除数据：delete from 表名 where 条件；

查询数据：select * from 表名；

        1.连接符 把列与列，列与字符连接在一起 用concat(字段1，字段2)

                eg:Select concat(sname,ssex) from student；

        2.数据排重：select distinct class from student;

        3.SELECT 特殊标识 数字和日期使用的数学表达式（如+、-、*、/）

        4.可以用列的别名将列的数据表示出来

数据过滤：

 

注意：

1.LIKE 运算选择类似的值 选择条件可以包含字符或数字

  %代表一个或多个字符 _代表一个字符 ‘%’和‘-’可以同时使用

逻辑运算：

常用数学函数：

        1 count(*)

        2 avg() 平均值

        3 sum() 求和

        4 min() 最下值

        5 max() 最大值

        6 round() (3.14,1) 保留1位小数

数据排列

1.单个列排序：select * from 表名 order by 字段 排序方式（asc/desc)；

2.多个列排序：select * from 表名 order by 字段 （排序方式）,表名 （排序方式）;

  先按第一个条件排，第一个相同再按第二个排。

3.按别名排序： select 字段名,别名 from 数据库名 order by 字段名;

子查询

语法：SELECT 字段 FROM 表名 WHERE 字段 +运算符 （select 字段 from 表名 where 条件）

单行子查询：使用单行比较操作符

多行子查询：1.in 等于列表中的任何一个

                      2.any 和子查询返回的任意一个值比较

                      3.all 和子查询返回的所有值比较）

多表查询

1.等值连接：

        select stu8.id stu8 ,stu2.id stu2 from stu8 ，stu2 where stu8.id = stu2.id;

2.内连接：

        select stu8.id stu8 ,stu2.id stu2 from stu8 inner join stu2 on stu8.id = stu2.id;

3.非等值连接：

        select stu8.id stu8 ,stu2.id stu2 from stu8,stu2 where stu8.age >4 and stu2.id2 >1;

4.外连接：使用外连接可以查询不满足连接条件的数据（左外连接和右外连接）

        select stu8.id ,stu2.id from stu8 left join stu2 on stu8.id = stu2.id;

        此为左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

Mysql与Python交互

1.开始

        引入模块：from pymysql import *

2.Connection对象：用于建立与数据库的连接

        创建对象：调用connect（）方法

        close()方法关闭连接

        commit()方法提交保存，若遇到数据修删改查，一定要用此方法保存操作

        cursor()返回Cursor对象，用于执行sql语句并获得结果

3.Cursor对象：用于执行sql语句

                        对象的方法：

• close()关闭对象方法
• execute()方法可以执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
• fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
• fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
• fetchmany(4):获取4行数据

4. 执行SQL语句（增删改查数据）

5. 关闭cursor

6. 关闭connection

7. 结束

mysql常用高级用法

1.分组

• group by：将查询的结果按照一个或多个字段进行分组，字段值相同的为一组，可用于单/多个字段
• group by + group_concat(字段名)：根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
• group by + 集合函数：通过group_concat()的结果，统计出每个分组的某字段的值的集合，通过集合函数对这个值的集合做一些操作
• group by + having：后跟 having 条件表达式，用来分组查询后指定一些条件来输出查询结果    #having作用和where一样，但having只能用于group by
• group by + with rollup：with rollup的作用是在最后新增一行用于记录当前列表里所有记录之和

2.分页：select * from 表名 limit start,count（从start开始，获取count条数据）

数据库数据的导出导入命令

1.mysqldump命令
只要表结构，不要数据：

mysqldump -uroot -pmysql -d （要备份的数据库名称） > /home/zx/（备份文件名称）.sql

只要表数据,不要表结构：

mysqldump -uroot -pmysql -t 数据库名称 表名称 > /home/zx/（备份文件名称）.sql

2.数据恢复

mysql 内置了source命令，可以读取sql文件，进行数据恢复，说白了就是重新执行SQL，对数据进行创建操作

1. 登录进mysql
2. 选择对应的库
3. source 备份文件的绝对路径.sql；

视图

视图是一条SELECT语句执行后返回的结果集

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变），视图不能增删改查

视图的作用：

        提高了重要性，就像一个函数

        对数据库重构，却不影响程序的运行

        提高了安全性能，可以对不同的用户

        让数据更加清晰

定义视图：create view 视图名称 as select语句;

查看视图：show tables;

使用视图：select * from 视图名称;

删除视图：drop view 视图名称;

 

事务

事务是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单位

事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

事务四大特性(简称ACID)：

        原子性(Atomicity)：事务中的所有操作要么全部提交成功，要么全部失败回滚

        一致性(Consistency)：数据库总是从一个一致性的状态转换到另一个一致性的状态

        隔离性(Isolation)：一个事务所做的修改在最终提交以前，对其他事务是不可见的

        持久性(Durability)：一旦事务提交，则其所做的修改会永久保存到数据库

事务命令

表的引擎类型必须是 innodb 类型才可以使用事务，这是 mysql 表的默认引擎

        1.开启事务：begin；  或者 start transaction；

        2.提交事务：commit；

           #修改数据的命令会自动的触发事务，包括insert、update、delete

        3.回滚事务：rollback；

索引

数据库索引好比是一本书前面的目录，能加快数据库的查询速度

索引的使用：

        1.查看索引：show index from 表名;

        2.创建索引：create index 索引名称 on 表名(字段名称(长度))；

• 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
• 字段类型如果不是字符串，可以不填写长度部分

        3.删除索引：drop index 索引名称 on 表名;