前言
最近在学习scrapy redis，在复习redis的同时打算把mysql和mongodb也复习一下，本篇为mysql篇，实例比较简单，学习sql还是要动手实操记的比较牢。
安装与启动
安装： sudo apt-get install mysql-server 查看服务： ps ajx | grep mysql 停止服务: sudo service mysql stop 开启服务: sudo service mysql start 重启服务: sudo service mysql restart 链接数据库： mysql -uroot -p 后输入密码 查看版本： select version();
常见数据库语句
查看数据库： show database; 创建数据库： create database 库名 [charset = UTF8]; 查看建库语句： show create database 库名; 使用数据库： use 库名; 删除数据库： drop 库名;
常见数据表语句
查看表： show table; 查看表结构： desc 表名; 创建表：
CREATE?TABLE?table_name( ????column1?datatype?contrai, ????column2?datatype, ????column3?datatype, ????..... ????columnN?datatype, ????PRIMARY?KEY(one?or?more?columns) );
创建表常用属性字段：
--?auto_inorement?表示自动增长 --?not?null?表示不为空 --?primary?key?表示为主键 --?defaul?表示默认值
删除表： drop table; 修改表结构：
添加字段：alter?table?表名?add?列名?类型; 修改字段(重命名)：alter?table?表名?change?原名?新名?类型及约束; 修改字段(不重命名)：alter?table?表名?modify?列名?类型及约束; 删除字段：alter?table?表名?drop?列名;
常用增删改查
基本查询查看所有列： select * from 表名; 查看指定列： select 列1,列2,... from 表名;
新增全列插入： insert into 表名 values(...); --需要给主键留下占位符,用0或null皆可。 部分列插入： insert into 表名(列1,...) values(值1,...); 插入多行全列数据： insert into 表名 values(...),(...)...; 插入多行部分列数据： insert into 表名(列1,...) values(值1,...),(值1,...)...;
更新更新操作： update 表名 set 列1=值1,列2=值2... where 条件;
删除删除操作(不推荐)： delete from 表名 where 条件; 逻辑删除(推荐)： update 字段名 set isvalid=0 where id=1; --设置删除字段,执行删除字段的操作即对该字段更新。
mysql查询详解
查询消除重复行： select distinct 列1,... from 表名;
条件查询where条件查询： select * from 表名 where 条件; where可以与比较运算符、逻辑运算符、模糊查询、范围查询、空判断搭配使用
比较运算符等于:?= 大于:?> 大于等于:?>= 小于:?< 小于等于:?<= 不等于:?!=?或?<>
举个栗子：
select?*?from?students?where?id?>?1; select?*?from?students?where?id?<=?2; select?*?from?students?where?name?!=?'咸鱼'; select?*?from?students?where?is_delete=0;逻辑运算符
and or not
举个栗子：
select?*?from?students?where?id?>?3?and?gender=0; select?*?from?students?where?id?<?4?or?is_delete=0; select?*?from?students?where?id?not?4;模糊查询
like %?表示任意多个任意字符 _?表示一个任意字符 rlike
举个栗子：
select?*?from?students?where?name?like?'咸%';??--查询以咸字开头的内容 select?*?from?students?where?name?like?'咸_';??--查询以咸字开头且后面只有一个字的内容 select?*?from?students?where?name?like?'咸%'?or?name?like?'%鱼';?--?查询以咸字开头或以鱼字结尾的内容范围查询
in?表示在一个非连续的范围内 no?in?表示不在一个非连续的范围内 between?...?and?...?表示在一个连续的范围内 rlike?表示正则查询，可以使用正则表达式查询数据
举个栗子：
select?*?from?students?where?id?in(1,3,8);??--?查询?id?在?1,3,8?当中的内容 select?*?from?students?where?id?not?in(1,3,8);??--?查询?id?不在?1,3,8?当中的内容 select?*?from?students?where?id?between?3?and?8;?--?查询?id?在3到8之间的内容 select?*?from?students?where?name?rlike?"^咸";?--?查询?name?是以咸字开头的内容空判断
判断是否为空?is?null
举个栗子：
select?*?from?students?where?height?is?null;
以上几种预算符优先级为: 优先级由高到低的顺序为：小括号、not、比较运算符、逻辑运算符。 and比or先运算，如果同时出现并希望先算or，需要结合()使用。
排序asc?升序 desc?降序
举个栗子：
select?*?from?students??order?by?age?desc,height?desc;?--显示所有的学生信息，先按照年龄从大到小排序，当年龄相同时?按照身高从高到矮排序聚合函数
count(*)查询总数 max(列)表示求此列的最大值 min(列)表示求此列的最小值 sum(列)表示求此列的和 avg(列)表示求此列的平均值
举个栗子：
select?count(*)?from?students; select?max(id)?from?students?where?gender=2; select?min(id)?from?students?where?is_delete=0; select?sum(age)?from?students?where?gender=1; select?sum(age)/count(*)?from?students?where?gender=1;?--求平均年龄 select?avg(id)?from?students?where?is_delete=0?and?gender=2;分组
group?by?将查询结果按照1个或多个字段进行分组，字段值相同的为一组 group_concat?表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
举个栗子：
select?gender?from?students?group?by?gender;??--?将学生按照性别分组 输出结果： +--------+ |?gender?| +--------+ |?男??????| |?女??????| |?中性????| |?保密????| +--------+ select?gender,group_concat(name)?from?students?group?by?gender; 输出结果： +--------+-----------------------------------------------------------+ |?gender?|?group_concat(name)????????????????????????????????????????| +--------+-----------------------------------------------------------+ |?男?????|?小彭,小刘,小周,小程,小郭?????????????????????????????????| |?女?????|?小明,小月,小蓉,小贤,小菲,小香,小杰????????????????????????| |?中性???|?小金???????????????????????????????????????????????????????| |?保密???|?小凤???????????????????????????????????????????????????????| +--------+-----------------------------------------------------------+分页
select?*?from?表名?limit?start,count
举个栗子：
select?*?from?students?where?gender=1?limit?0,3;??--查询前三行的数据连接查询
语法：
select?*?from?表1?inner/left/right?join?表2?on?表1.列?=?表2.列
其中：
inner?join(内连接查询)：查询的结果为两个表匹配到的数据 right?join(右连接查询)：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充 left?join(左连接查询)：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
举个栗子：
select?*?from?students?inner?join?classes?on?students.cls_id?=?classes.id; select?*?from?students?as?s?left?join?classes?as?c?on?s.cls_id?=?c.id; select?*?from?students?as?s?right?join?classes?as?c?on?s.cls_id?=?c.id;子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句。 子查询可以和 in 搭配使用
主查询?where?条件?in?(子查询)
数据库的备份与恢复
数据库备份mysqldump?–uroot?–p?数据库名?>?备份文件名.sql;数据库恢复
mysql?-uroot?–p?新数据库名?<?备份文件名.sql
Python与mysql交互
Python与mysql交互流程 安装与导入
安装相关库： pip install pymysql 导入： from pymysql import *
创建connection对象connection?=?connect(host,?port,?database,?user,?password,?charset)
其中参数如下：
host：连接的mysql主机，如果本机是'localhost' port：连接的mysql主机的端口，默认是3306 database：数据库的名称 user：连接的用户名 password：连接的密码 charset：通信采用的编码方式，推荐使用utf8
connection对象方法如下：
close()关闭连接 commit()提交 cursor()返回Cursor对象，用于执行sql语句并获得结果获取cursor
cursor=connection.cursor()
其中常用方法：
close():关闭cursor execute(operation?[,?parameters?]):执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句。 fetchone():执行查询语句时，获取查询结果集的第一个行数据，返回一个元组 fetchall():执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
举个栗子：
from?pymysql?import?* def?main(): ??conn?=?connect(host='localhost',port=3306,database='xianyuplus',user='root',password='mysql',charset='utf8') ??cs1?=?conn.cursor() ??count?=?cs1.execute('insert?into?xianyufans(name)?values("666")') ??conn测试数据mit() ??cs1.close() ??conn.close() if?__name__?==?'__main__': ????main() mysql视图
什么是视图？视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据。
视图语句创建视图:create?view?视图名称?as?select语句;?--建议视图以v_开头 查看视图：show?tables; 使用视图：select?*?from?视图名称; 删除视图：drop?view?视图名称;视图作用
提高了重用性，就像一个函数
对数据库重构，却不影响程序的运行
提高了安全性能，可以对不同的用户
让数据更加清晰
mysql事务
什么是事务？事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
事务有什么特点？原子性，一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
一致性，数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性，一旦事务提交，则其所做的修改会永久保存到数据库。
事务相关命令开启事务：start?transaction;?或者?begin; 提交事务：commit; 回滚事务：rollback;
mysql索引
什么是索引？数据库索引好比是一本书前面的目录，能加快数据库的查询速度
索引相关命令创建索引：create?index?索引名称?on?表名(字段名称(长度))?--当指定索引的字段类型为字符串时，应填写长度 查看索引：show?index?from?表名; 删除索引：drop?index?索引名称?on?表名;注意事项
建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
建立索引会占用磁盘空间。
尾言
以上就是关于mysql的一些用法，其实是比较基础的，重点部分是关于mysql的查询部分，毕竟在业务应用中主要还是查询为主。
咸鱼普拉思
一只咸鱼在编程路上的摸爬滚打,记录摸索中的点点滴滴。
查看更多关于Python | Python学习之mysql交互详解的详细内容...