前言
最近在学习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交互流程](https://i-blog.csdnimg.cn/blog_migrate/26a5b5e49a2b3a268cb75d92f954a2a2.png)
安装与导入
安装相关库: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.commit()
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的查询部分,毕竟在业务应用中主要还是查询为主。
咸鱼普拉思
一只咸鱼在编程路上的摸爬滚打,记录摸索中的点点滴滴。