MySQL数据库操作

python2.7： 安装mysql模块
sudo apt-get install python-mysql
安装pip apt-get install python-pip
安装python-dev， 否则后面安装mysql模块报错
升级pip
安装:libmysqlclient-dev
pip install MySQL-python
在文件中引入模块
import Mysqldb
python3.x：
Python3中不支持MySqldb包， 使用pymysql来代替了， 用法一模一样。
pip install pymysql
在文件中引入模块：
import pymysql
 

相关术语
主键： 一个记录中有若干个属性， 其中一个能唯一标识该记录， 该属性就是主键
比如一条记录包括身份证号， 姓名， 年龄， 身份证号是唯一确定这个人的， 它就是主键
外键： 外键是与另一张表的关联， 能确定另一个表中的记录
比如：
有三个表：
客户表： 记录客户的信息， 如客户编号， 客户名称， 地址， 联系方式等
商品表： 记录商品的信息， 比如商品编号， 商品名称， 品牌， 单价， 库存数量等
订单表： 包括订单信息
一条订单记录： 包括客户编号， 商品编号， 商品数量， 金额等属性
客户编号是客户表中的主键， 它就是订单表的外键
约束： 一种限制， 通过对表的行或列的数据做出限制， 来确保数据的完整性、 唯一性
比如： 在订单记录中， 指定的客户编码， 必须是客户表中存在的客户
商品编号， 必须是商品表中存在的商品
 

表字段约束
• 主键primary key
• 非空not null
• 惟一unique
• 默认default
• 外键foreign key
 

表字段类型
• 在mysql中包含的数据类型很多， 这里主要列出来常用的几种
• 数字： int,decimal， float
• 字符串： varchar,text
• 日期： datetime
• 布尔： bit
 

 

*******************常用命令************************

连接命令
使用命令连接
• 命令操作方式， 在工作中使用的更多一些， 所以要达到熟练的程度
• 打开终端， 运行命令
mysql -uroot -p
回车后输入密码， 当前设置的密码为mysql
查看版本： select version();
显示当前时间： select now();
• 注意： 在语句结尾要使用分号;
• 退出登录
quit或exit
******************************************************

数据库操作
• 显示数据库列表
show databases;
• 创建数据库
create database 数据库名 charset=utf8;
• 删除数据库
drop database 数据库名;
• 切换数据库
use 数据库名;
• 查看当前选择的数据库
select database();

*********************************************************

表操作
• 查看当前数据库中所有表
show tables;
• 创建表
• create table 表名(列及类型);
• 如：
• create table students(
id int auto\_increment primary key,
sname varchar\(10\) not null
\);
• 修改表
alter table 表名 add|change|drop 列名 类型;
alter table 表名 add 列名 类型； --添加某一列
alter table 表名 change 原列名 新列名 类型； --修改表的列属性名
alter table 表名 drop 列名； --删除表的某一列
alter table 表名 rename 新表名； --修改表名
如：
alter table students add birthday datetime;
• 删除表
drop table 表名;
• 查看表结构
desc 表名;
***********************************************************

数据操作
• 查询
select * from 表名
• 增加
全列插入： insert into 表名 values(...)
缺省插入： insert into 表名(列1,...) values(值1,...)
同时插入多条数据： insert into 表名 values(...),(...)...;
或insert into 表名(列1,...) values(值1,...),(值1,...)...;
• 主键列是自动增长， 但是在全列插入时需要占位， 通常使用0， 插入成功后以实际数据为准
• 修改
update 表名 set 列1=值1,... where 条件
• 删除
delete from 表名 where 条件

********************************************************************************
数据备份与恢复
• 进入mysql库目录
• 运行mysqldump命令
mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;
按提示输入mysql的密码
数据恢复
• 连接mysqk， 创建数据库
• 退出连接， 执行如下命令
mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql
根据提示输入mysql密码

查询********************************************

查询
简介
• 查询的基本语法
select * from 表名;
• from关键字后面写表名， 表示数据来源于是这张表
• select后面写表中的列名， 如果是*表示在结果中显示表中所有列
• 在select后面的列名部分， 可以使用as为列起别名， 这个别名出现在结果集中
• 如果要查询多个列， 之间使用逗号分隔
消除重复行
• 在select后面列前使用distinct可以消除重复的行
select distinct gender from students;
• 使用where子句对表中的数据筛选， 结果为true的行会出现在结果集中
• 语法如下：
select * from 表名 where 条件;
比较运算符*******************************************************

比较运算符
• 等于=
• 大于>
• 大于等于>=
• 小于<
• 小于等于<=
• 不等于!=或<>
• 查询编号大于3的学生
select * from students where id>3;
• 查询编号不大于4的科目
select * from subjects where id<=4;
• 查询姓名不是“黄蓉”的学生
select * from students where sname!='黄蓉';
• 查询没被删除的学生
select * from students where isdelete=0;
 

逻辑运算符****************************************

逻辑运算符
• and
• or
• not
• 查询编号大于3的女同学
select * from students where id>3 and gender=0;
• 查询编号小于4或没被删除的学生
select * from students where id<4 or isdelete=0;
模糊查询***************************************************

模糊查询
• like
• %表示任意多个任意字符
• _表示一个任意字符
• 查询姓黄的学生
select * from students where sname like '黄%';
• 查询姓黄并且名字是一个字的学生
select * from students where sname like '黄_';
• 查询姓黄或叫靖的学生
select * from students where sname like '黄%' or sname like '%靖%';

************************范围查询***********************************************
• in表示在一个非连续的范围内
• 查询编号是1或3或8的学生
select * from students where id in(1,3,8);
• between ... and ...表示在一个连续的范围内
• 查询学生是3至8的学生
select * from students where id between 3 and 8;
• 查询学生是3至8的男生
select * from students where id between 3 and 8 and gender=1;
 

***********************空判断**********************************************
• 注意： null与''是不同的
• 判空is null
• 查询没有填写地址的学生
select * from students where hometown is null;
• 判非空is not null
• 查询填写了地址的学生
select * from students where hometown is not null;
• 查询填写了地址的女生
select * from students where hometown is not null and gender=0;

 

************************聚合******************************************
为了快速得到统计数据， 提供了5个聚合函数
• count(*)表示计算总行数， 括号中写星与列名， 结果是相同的
查询学生总数
select count(*) from students;
• max(列)表示求此列的最大值
查询女生的编号最大值
select max(id) from students where gender=0;
• min(列)表示求此列的最小值
查询学生最小编号
select min(id) from students ;
• sum(列)表示求此列的和
查询男生的编号之和
select sum(id) from students where gender=1;
• avg(列)表示求此列的平均值
查询未删除女生的编号平均值
select avg(id) from students where isdelete=0 and gender=0;

******************************分组********************************
按照字段分组， 表示此字段相同的数据会被放到一个组中
分组后， 只能查询出相同的数据列， 对于有差异的数据列无法出现在结果集中
可以对分组后的数据进行统计， 做聚合运算
语法：
select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
查询男女生总数
select gender as 性别,count(*)
from students
group by gender;
查询各城市人数
select hometown as 家乡,count(*)
from students
group by hometown;
分组后的数据筛选
• 语法：
select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...
• having后面的条件运算符与where的相同
• 查询男生总人数
select count(*)
from students
where gender=1;

*********************排序******************************
为了方便查看数据， 可以对数据进行排序
语法：
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
将行数据按照列1进行排序， 如果某些行列1的值相同时， 则按照列2排序， 以此类推
默认按照列值从小到大排列
asc从小到大排列， 即升序
desc从大到小排序， 即降序
查询男生学生信息， 按学号降序
select * from students
where gender=1
order by id desc;
查询未删除科目信息， 按名称升序
select * from subject
order by stitle;

 

*********************分页****************************
获取部分行
当数据量过大时， 在一页中查看数据是一件非常麻烦的事情
语法
select * from 表名
limit start,count
从start开始， 获取count条数据
start索引从0开始
示例： 分页
已知： 每页显示m条数据， 当前显示第n页
求第n页的数据
select * from students
limit (n-1)*m,m