python mysql的使用

MySQL的启动、停止、重启指令
sudo service mysql start # 启动命令
sudo service mysql stop # 关闭命令
sudo service mysql restart # 重启命令
sudo service mysql status # 查看状态
ps -aux | grep mysql
MySQL的登录的退出
mysql [-h ip地址]-u 用户名 -p 密码 # 登录命令
exit quit ctrl+D # 退出命令
数据类型
int,bit(1) # 整数
decimal # 小数 # decimal(5,2) 表示共存5位数，小数占两位 123.00
varchar,char # 字符串 # char(3)固定长度的字符串
text 存储大文本，当字符大于4000时推荐使用
date,time datetime # 日期时间
enum # 枚举类型
约束
primary key # 主键 物理上存储的书序
not null # 非空 此字段不允许填写空值
unique # 唯一 此字段的值不允许重复
default # 默认 当不填写此值时会使用默认值，如果填写时以填写微赚
foreign key # 外键，对关系字段进行约束
auto_increment # 自动增长
unsigned # 不为负数
数据库的操作
show databases # 查看所有数据库
select database() # 查看当前所在数据库
create database 数据库名 charset=utf8 #创建数据库
use 数据库名 # 使用数据库
drop database 数据库名 # 删除数据库
数据表的操作
show tables # 查看数据表
create table 数据表名（数据类型 约束）#创建数据表
alter table 表名 add 列名 类型 [after 字段名]#修改数据表
alter table 表名 modify 列名 类型及约束 # 修改字段【不重命名版】
alter table 表名 change 原名 新名 类型及约束 #【重命名版】
alter table 表名 drop 列名 # 删除字段
drop table 数据表 # 删除表
rename table 旧表名 to 新表名 # 重命名表
数据表中数据的操作
insert into 表名 values(全部列的值) # 增加数据 全部数据
insert into 表名(字段1，字段2) value(值1，值2) # 部分字段
update 表名 set 字段=值，字段=值 where 条件 # 修改数据
delete from 表名 where 条件 # 删除数据
truncate 表名 # 清空数据
查询数据
select * from 表名
select * from 表名 where 条件
select 字段 as 别名 from 表名 where 条件
comment 注释
desc 表名 # 查看数据表类型
数据库创建记得加编码【charset=utf8】
查询所有字段
select * from 表名;
select * from students;
select * from classes;
select id, name from classes;
– 查询指定字段
– select 列1,列2,… from 表名;
select name, age from classes;
– 使用 as 给字段起别名
– select 字段 as 名字…. from 表名;
select name as 姓名, age as 年龄 from classes;
– select 表名.字段 …. from 表名;
select students.name, students.age from students;
– 可以通过 as 给表起别名
– select 别名.字段 …. from 表名 as 别名;
select students.name, students.age from students;
select s.name, s.age from students as s;
-- 消除重复行
– distinct 字段
select distinct gender from students;
– like
% 替换1个或者多个
_ 替换1个
– 查询姓名中 以 “小” 开始的名字
select name from students where name=”小”;
select name from students where name like “小%”;
– 查询姓名中 有”小” 所有的名字
select name from students where name like “%小%”;
– rlike 正则
– 查询以 周开始的姓名
select name from students where name rlike “^周.*”;
– 查询已 周开始、伦结尾的姓名
select name from students where name rlike “^周.*伦$”;
– 范围查询
– in（1, 3，8 ）表示在一个非连续的范围内
– 查询 年龄为18、34的名字
select name,age from students where age=18 or age=34;
select name,age from students where age=18 or age=34 or age=12;
select name,age from students where age in (12, 18, 34);
– not in 不非连续的范围之内
– 年龄不是 18、34岁之间的信息
select name,age from students where age not in (12, 18, 34);
– between … and …表示在一个连续的范围内
– 查询 年龄在18到34之间的信息
select name,age from students where age between 18 and 34;
– not between … and …表示不在一个连续的范围内
– 查询 年龄不在在18到34之间的的信息
select * from students where age not between 18 and 34;
select * from students where not age between 18 and 34;
排序
– order by 字段
– asc从小到大排列，即升序
– desc从大到小排序，即降序
– 查询年龄在18到34岁之间的男性，按照年龄从小到到排序
select * from students where (age between 18 and 34) and gender=1;
select * from students where (age between 18 and 34) and gender=1 order by age;
select * from students where (age between 18 and 34) and gender=1 order by age asc;
– 查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where (age between 18 and 34) and gender=2 order by height desc;
– order by 多个字段
– 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc;
– 如果年龄也相同那么按照id动大到小排序
select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc, id desc;
– 按照年龄从小到大、身高从高到矮的排序
select * from students order by age asc, height desc;
– 聚合函数
– 总数– count
– 查询男性有多少人，女性有多少人
select * from students where gender=1;
select count(*) from students where gender=1;
select count(*) as 男性人数 from students where gender=1;
select count(*) as 女性人数 from students where gender=2;
– 求和– sum
– 计算所有人的年龄总和
select sum(age) from students;
– 平均值– avg
– 计算平均年龄
select avg(age) from students;
– 四舍五入 round(123.23 , 1) 保留1位小数
– 计算所有人的平均年龄，保留2位小数
select round(sum(age)/count(*), 2) from students;
select round(sum(age)/count(*), 3) from students;
– 计算男性的平均身高 保留2位小数
select round(avg(height), 2) from students where gender=1;
– select round(avg(height), 2) from students where gender=1;
– 分组– group by
– 按照性别分组, 查询所有的性别
select name from students group by gender;
select * from students group by gender;
select gender from students group by gender;
– group_concat(…)
– 查询同种性别中的姓名
select gender, group_concat(name) from students where gender=1 group by gender;
select gender, group_concat(name, age, id) from students where gender=1 group by gender;
select gender, group_concat(name, “_”, age, ” “, id) from students where gender=1 group by gender;
– having
– 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30
select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30;
– 查询每种性别中的人数多于2个的信息
select gender, group_concat(name) from students group by gender having count(*)>2;
-- 分页
– limit start, count**
– 限制查询出来的数据格式
select * from students where gender=1 limit 2;
– 查询前5个数据
select * from students limit 0,5;
– 查询id6-10（包含）的书序
select * from students limit 5,5;
– 链接查询
– inner join … on
– select * from 表A inner join 表B;
select * from students inner join classes;
– 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
– 按照要求显示姓名、班级
select students.* classes.name from students inner join classes on students.cls_id=classes.id;
select students.name, classes.name from students inner join classes on students.cls_id=classes.id;
– 给数据表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
– 查询 又能够对应班级的学生以及班级信息, 显示学生的所有信息, 只显示班级名称
select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;
– 在以上的查询中，将班级姓名显示在第1列
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id;
– 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
– select c.xxx s.xxx from student as s inner join clssses as c on …. order by ….;
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;
– 当时同一个班级的时候, 按照学生的id进行从小到大排序
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;
– left join
– 查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;
– 自关联
select * from areas as city inner join areas as province on city.pid=provice.aid having provice.atitle = “山东省”
– 子查询
– 标量子查询
– 查询出高于平均身高的信息
select * from students where height > (select avg(height) from students);
– 列级子查询
– 查询学生的班级号能够对应的学生信息
select * from students where cls_id in (1, 2);
select * from students where cls_id in (select id from classes);
form pymysql import * # 导入pymysql模块
conn = connect (参数列表)
host 连接的mysql主机，如果本机是localhost
port 连接的mysql主机的端口，默认是3306
database 数据库的名称
user 连接的用户名
password 连接的密码
charset 通信采用的编码方式，推荐使用utf8
cs1 = conn.cursor() # 获取cursor 对象，电泳connection对象的cursor()方法
execute(select语句，元祖或列表) # 提交信息
commit() # 提交信息
close() 关闭连接
先关闭cursor 再关闭conn
对象的方法
close() # 关闭
execute(select语句，元祖或列表) # 提交信息
fetchone() # 执行查询语句时，获取查询结果集的第一个行数据，返回一个元祖
fetchall() # 执行查询时，获取结果集的所有行，一行构成一个元祖，再将这些元祖装入一个元祖返回
对象的属性
rowcount 只读属性，表示最近一次execute()执行后受影响的行数
connection 获得当前连接对象
参数化

构造参数列表

params = [find_name]

执行select语句，并返回受影响的行数：查询所有数据

count = cs1.execute(‘select * from goods where name=%s’, params)
视图
create view 视图名称 as 查询sql语句 # 创建视图 V_
show tables; # 查看视图
rename table 旧视图名 to 新视图名 # 重命名视图
create or replace view 视图名称 as 查询sql语句 # 修改视图的数据源
drop view 视图名 # 删除视图
事务
start transaction/begin # 开启事务
commit # 提交事务
rollback # 回滚事务
索引
show index from 表名 # 查看表的索引

创建索引

如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分​​
create index 索引名称 on 表名（字段名称（长度））
alter tabe 表名 add index 索引名称（字段名称（长度））
drop index 索引名称 on 表名 # 删除索引
set profiling=1 # 开启时间检测 关闭把1改成0即可
show profiles # 显示执行时间
三种索引
主键索引
普通索引
create index 索引名称 on 表名 (字段名称(长度))
alter table 表名 add index 索引名称 (字段名称(长度))
唯一索引
create unique index 索引名称 on 表名 (字段名称(长度))
alter table 表名 add unique index 索引名称 (字段名称(长度))
创建账号
all privileges 表示所有权限
数据库中的全部表则用 数据库.* 某张表用数据库.表名​
geant 权限名称 on 数据库 to 账户@主机 with grant option
修改密码
使用root 登录，修改mysql数据库的user表
使用password()函数进行密码加密
update user set authentication_string=password(‘新密码’) where user=’用户名’;

flush privileges; # – 注意修改完成后需要刷新权限

mysqldump –uroot –p密码 数据库名 > 文件名.sql # – 备份数据库 注意：需要先退出MySQL客户端

– 恢复

– 连接mysql，创建“新的数据库”

– 退出连接，执行如下命令

　mysql -uroot –p密码 新数据库名 < 文件名.sql

ＭySQL主从同步具体步骤
前情提要：
本文档以Ubuntu作为主服务器，Win7作为从服务器进行测试。要保证主从服务器之间能够互相通信（即能相互ping通）。
主服务器ip地址：192.168.13.81
从服务器ip地址：192.168.13.82
测试通信状况：
主可以ping通从：
从可以ping通主：
主服务器配置：
1. 备份主服务器的数据在终端执行如下命令：mysqldump -uroot -pmysql–all-databases –lock-all-tables > ~/master_db.sql将来会在家目录下生产一个master_db.sql的数据库文件，即备份文件
2. 编辑设置mysqld的配置文件，设置log_bin和server-id在终端执行如下命令：sudo vim/etc/mysql/mysql.conf.d/mysqld.cnf

43行前面加上注释，允许其他ip连接

83行的注释打开并且把server -id 改成1

84行的注释打开

修改完毕之后保存退出，并且把MySQL服务器进行重启，如下图：
sudo service mysql restart
3. 登入主服务器Ubuntu中的mysql，创建用于从服务器同步数据使用的帐号：
终端输入如下指令连接MySQL：mysql –uroot –pmysql
GRANT REPLICATION SLAVE ON . TO’slave’@’%’ identified by ‘slave’;
FLUSHPRIVILEGES;
4. 获取主服务器的二进制日志信息
在MySQL服务器上输入如下命令来获取二进制日志信息：SHOW MASTER STATUS;
记录下二进制文件的名字以及position位置信息，下面同步的时候要用到
从服务器配置：
1. 把主服务器上导出的master_db.sql备份文件先同步到从服务器上，保证主从数据一致。
把master_db.sql先放到从库的服务器上（注意文件位置要和你打开终端的位置保持一致，比如都在桌面），然后执行如下命令：
mysql -uroot-pmysql < master_db.sql
此时从库服务器已经拥有跟主服务器一样的数据了，如下图所示：
2. 修改从服务器的配置文件my.ini
加上对应的server-id，要保证不能跟主服务器的server-id一样
保持退出之后重启从服务器，如下图：
net stop/start mysql
3. 连接到从服务器，进行同步工作
输入如下指令：
change master to master_host=’192.168.13.81’, master_user=’slave’, master_password=’slave’,master_log_file=’mysql-bin.000008’, master_log_pos=582;
注意：需要修改的地方有3处，第一个ip地址改成自己主服务器的ip地址，第二个二进制文件要跟主服务器的一致，第三个是pos位置也要与主服务器一致。具体的值参见主服务器配置的第4步
4. 开始同步
输入命令：start slave;
5. 查看同步状态
输入指令：show slave status \G;
当看到两个yes意味着同步成功。如果没有成功先检查一下主从是否可以ping通，如果主从可以正常通信的话，则从文档的“主服务器配置”的第4步开始把步骤再走一遍。
测试同步
在主服务器创建一个新的数据库“test_slave”
create database test_slave charset=utf8;
此时进入到从服务器已经可以查看到效果：
恭喜！此致主从同步大功告成！！！