Python全栈 MySQL 数据库（SQL命令大全、MySQL 、Python调用）
梦想与了信仰 开局一张图 主要三个方面： 1.Linux终端命令 2.MySQL语句 3.Python调用 先删库 再跑路..... 终端命令： vi 文本编辑器 cat /etc/password | grep “用户名” 获取user表 sudo -i 获取root权限 sudo apt-get install python3-pip 安装pip3 sudo pip3 install pymysql 安装mysql sudo apt-get install mysql-server 安装服务端 sudo apt-get install mysql-client 安装客户端 sudo apt-get update 读取列表 保存到 /var/lib/apt/lists sudo apt-get upgrade 对比下载列表并更新 sudo /etc/init.d/mysql status 查询状态 sudo /etc/init.d/mysql stop 停止服务 sudo /etc/init.d/mysql restart 重启服务 sudo /etc/init.d/mysql reload 重新加载 mysql -h主机地址 -u用户名 -p密码 链接mysql 修改mysql默认字符集： sudo -i 1.获取root chmod 644 文件名 2.修改文件权限 cd etc/mysql/mysql.conf.d 3.进入配置文目录 cp etc/msql.cnf/mysqld.cnf.bak 4.备份 subl mysqld.cnf 5.vi打开源文件 [mysqld] 目录 character_set_server = utf8 6.添加命令 /etc/init.d/mysql mysqldump -u用户 -p源库名 > ~/xxx.sql 数据备份： 参数： --all-databases 1.备份所有库 库名 2.备份单个库 -B 库1 库2.. 3.备份多个库 库名 表1 表2... 4.备份指定库指定表 数据恢复： mysql -uroot -p < 目标库名 xxx.sql 1. 恢复备份库 mysql -uroot -p --one-database 目标库名 < xxx.sql 2. 恢复备份内某一个库 恢复：表不删除 表记录删除覆盖 MySQL远程连接： sudo -i 1.管理员模式 cd /etc/mysql/mysql.conf.d/ 2.cd到mysql目录 vi mysqld.cnf 3.打开注释掉默认IP #bind-address = 127.0.0.1 4.保存 /etc/init.d/mysql restart 5.重启服务 授权用户： grant 授权列表 on 库.表 to “用户名“@”%”identified by “密码” with grant option 1.命令格式 示例：grant all privileges on *.* to "tiger"@"%" identified by "123" with grant option; 2.示例 all privileges、select、insert ... 4.库.表: *.* 所有库所有表 3.权限列表 python3模块安装： 模块名 ：pymysql 在线 ：sudo pip3 install pymysql 离线 ：pymysql-0.7.11.tar.gz $ tar -zxvf pymyql-0.7.11.tar.gz $ cd pymysql-0.7.11 $ sudo python3 setup.py install 验证： $ python3 >>> import pymysql >>> python2模块安装： 模块名 ：MySQLdb 安装 ：sudo pip install mysql-python sqlalchemy 框架 安装： 在线 ：sudo pip3 install sqlalchemy 离线 ： $ tar -zxvf SQLAlchemy-1.2.10.tar.gz $ cd SQLAlchemy-1.2.10 $ sudo python3 setup.py install 验证： $ python3 >>> import sqlalchemy >>> pymysql使用： from pymsql import * 导入模块 (db = pymysql.connect(...)) 1、建立数据库连接 c = db.cursor()) 2、创建游标对象 c.execute("insert ....") 3、游标方法: db.commit() 4、提交到数据库 c.close() 5、关闭游标对象 db.close() 6、断开数据库连接 ： 7. connect对象： db = pymysql.connect(参数列表) 1、host ：主机地址,本地 localhost 2、port ：端口号,默认3306 3、user ：用户名 4、password ：密码 5、database ：库 6、charset ：编码方式,推荐使用 utf8 8. 连接对象的方法： 数据库连接对象(db)的方法 1、db.close() 关闭连接 2、db.commit() 提交到数据库执行 3、db.rollback() 回滚 4、cur = db.cursor() 返回游标对象,用于执行具体SQL命令 9. 游标对象的方法： 游标对象(cur)的方法 1、cur.execute(sql命令,[列表]) 执行SQL命令 2、cur.close() 关闭游标对象 3、cur.fetchone() 获取查询结果集的第一条数据 ((记录1),) 4、cur.fetchmany(n) 获取n条 ((记录1),(记录2)) 5、cur.fetchall() 获取所有记录 ORM：orm(Object Relation Mapping 对象关系映射) 定义：把对象模型映射到MySQL数据库中 SQL命令： /var/lib/mysql MySQL数据目录 show variables like "autocommit"; 查询commit事务 begin； 开启事务 commit； 提交事务（MySQL默认自动提交） rollback； 终止事务 system sudo -i 由数据直接进入终端 show databases； 查看已有库 create database 库名； 创建库 create database 库名 charcater set utf8； 指定字符集 show create database 库名； 查看库字符集 select database（）； 查看当前所在库 use 库名； 切换库 drop database 库名； 删除库 show tables； 查看已有表 create table 表名（字段名1 数据类型，....）； 创建表 show create table 表名； 查看表字符集 desc 表名； 查看表结构 drop table 表名； 删除表 insert into 表名 values（值1），（值2）...； 插入完整记录 insert into 表名 （字段名1，...） values（值1），...； 插入字段数据 select * from 表名 [where 条件]； 查询所有字段 select 字段名1，字段名2，...from 表名[where 条件]； 查看字段 alter table 表名 add 字段名 数据类型; 添加字段 alter table 表名 add 字段名 数据类型 first； 头插） alter table 表名 add 字段名 数据类型 after 字段名； 指定插入） alter table 表名 drop 字段名； 删除字段 alter table 表名 modify 字段名 新数据类型； 修改数据类型 alter table 表名 rename 表名； 表重命名 delete from 表名 where 条件； 删除表记录（必须加where） update 表名 set 字段1=值1，字段名2=值2，... where 条件 更改表记录（必须加where） alter table 表名 change 原名 新名 数据类型； 字段重命名 create table 表名 select .. from 表名 where 条件; 复制表（不复制key） create table 表名 select * from 表名 where false; 复制表结构（不复制key） sex enum（“M”,"F","S"） not null defaulf "S" 约束 show variables like 变量名； 查询MySQL变量 select 字段名列表 from 表名列表; (笛卡尔积) select t1.name,t2.name from t1,t2 where 条件 多表查询 create index 索引名 on 表名(字段名); 添加普通索引 create table（....index（字段名），...） 创建表时创建普通索引 drop index 索引名 on 表名； 删除普通索引 show index from 表名； 查看普通索引 create unique index 索引名 on表名（字段名）； 添加唯一索引 create table 表名( .... , unique key (字段名) ); 创建表时创建唯一索引 drop unique index 索引名 on 表名； 删除唯一索引 show unique index from 表名； 查看唯一索引 alter table 表名 add primary key（字段名）； 添加主键索引 create table 表名( .... , id int， primary key (字段名) ); 创建表时创主键一索引 (id int primary key auto_increment,)auto_increment=10000; 设置自增长起始值 alter table 表名 modify id int auto_increment; 添加自增长 alter table 表名 auto_increment=20000; 修改自增长起始值 alter table 表名 modify id int; 删除自增长 alter table 表名 drop primary key; 删除主键索引 show index from 表名\G; 查看表索引 desc 表名； 查看表结构（key） Non_Unique：1 ：index 普通索引（查询结果） Non_Unique：0 ：unique 唯一索引（查询结果） alter table 表名 drop foreign key 外键名; 删除外键 show create table 表名; 查看外键名 创建外键： create......t1（）； create table t2（ ... ... foreign key(参考字段名) references 主表(被参考字段名) on delete 级联动作 on update 级联动作）； 添加外键： alter table 表名 add foreign key(参考字段) references 主表(被参考字段) on delete ... on update ... 级联动作： restrict(默认)不允许主表操作从表 cascade ：跟随删除、更新 set null：主表更改后从表值为NULL 内链接： select 字段名 from表1 inner join 表2 on 条件 inner join 表3 on 条件...； 外链接.左链接： 以左表为主显示查询结果 select 字段名 from 表1 left join 表2 on 条件 left join 表3 on 条件...； 右链接 以右表为主显示查询结果 数据导入： load data infile “文件名” into table 表名 fields terminated by “分隔符” lines terminated by “\n”； 数据导出： select ... from 表名 into outfile “/var/lib/mysql-files/文件名” fields terminated by “分隔符” lines terminated by “\n”; 数据恢复： 恢复单个库 mysql -uroot -p < 目标库名 xxx.sql 从所有库备份中恢复某一个库（-one-database） mysql -uroot -p --one-database 目标库名 < xxx.sql 恢复：表不删除 表记录删除覆盖 数据备份： mysqldump -u用户 -p源库名 > ~/xxx.sql --all-databases 备份所有库 库名 备份单个库 -B 库1 库2.. 备份多个库 库名 表1 表2... 备份指定库指定表 运行时间检测： 开启：set profiling=1； 关闭：set profiling=0； 查询执行记录：show profilings； SQL查询： 3.select ... 聚合函数 from 表名 1.where 2.group by... 4.having ... 5.order by ... 6.limit ...； select ... from 表名 where 条件(select ....); 查询嵌套： 2、找出每个国家攻击力最高的英雄的名字和攻击值 select name,gongji from sanguo where (country,gongji) in (select country,max(gongji) from sanguo group by country); where：只能操作表中实际存在的字段 group by：给查询结果进行分组 having：对查询结果进一步筛选 distinct：不显示字段重复值 show engines; 查看所有存储引擎 show create table 表名; 查看表的存储引擎 create table 表名(...)engine=myisam; 创建表时指定存储引擎 alter table 表名 engine=innodb; 添加储存引擎 InnoDB： InnoDB特点（2文件）： 行级锁、支持外键、事务操作 .frm（表结构，索引）、.ibd（表记录） MyISAM特点（3文件）： MyISAM： 独享表空间、表级锁 .frm（结构）、.myd（记录）、.myi（索引） 锁： select ：加读锁之后别人不能更改表记录,但可以进行查询 insert、delete、update ：加写锁之后别人不能查、不能改 锁粒度： 表级锁 ：myisam 行级锁 ：innodb 调优： 1.选择合适的存储引擎 2.常用字段建立索引 3.where 避免使用 !=、NULL判断、or链接、 like前置%、in、not in、*代替字段、 数据类型: 数据类型: int 大整型（4个字节） 2**32 - 1（4294967295） tinyint 微小整型（一个字节） 有符号（signed默认）：-128 ~ 127 无符号（unsigned）：0 ~ 255 smallint 小整型（2字节） bigint 极大整型（8字节） float 浮点数（4个字节，7个有效位） 字段名 float（m，n） m：总位数 n：小数位数 decimal 浮点数（28个有效位） 字段名 decimal（m，n）m：总位数 n：小数位数 将9的倍数包装成4个字节 余数 字节 0 0 1-2 1 3-4 2 5-6 3 7-9 4 字段名 enum（值1，值2...）； 单选（enum） 字段名 set（值1，值2...）； 多选（set） （多项放在一个字符串内用，号隔开） date：“YYYY-MM-DD” time：“HH：MM：SS” datetime：“YYYY-MM-DD HH：MM：SS” timestamp：“YYYY-MM-DD HH：MM：SS” datetime：不给值默认返回Null timestamp：不给值默认返回系统时间 时间函数 now（） 返回服务器当前的时间 curdate（） 返回当前时期 curtime（） 返回当前日期 year（date） 返回指定时间的年份 date（date） 返回指定时间的日期 time（date） 返回指定时间的时间 聚合函数 avg（字段名）：求该字段的平均值 sum（字段名）：求和 max（字段名）：最大值 min（字段名）：最小值 count（字段名）：统计该字段的个数 运算符：+ - * / % 时间运算符 select * from 表名 where 字段名 运算符（时间-interval 时间间隔单位）； 时间间隔单位：1 day | 2hour | 1 minute | 2year | month 数值比较： = != > >= < <= 字符比较： = != 逻辑比较： and or 范围内比较： 1.where 字段名 between 值1 and 值2 2.where 字段名 in（值1，值2，....） 3.where 字段名 not in （值1，值2，...） 空：where name is null 非空：where name is not null NILL：空值，只能用is或is not取匹配 “” ： 空字符串，用 = 或 != 去匹配 模糊比较： where 字段名 like 表达式 表达式 _ : 匹配单个字符 %：匹配0到多个字符 NULL不会被统计 排序：order by ASC | DESC 显示： limit 开始显示位置，条数 每页显示n条记录，显示第m页： limit（m-1）*n，n MySQL 与 Python 交互 # mysqlpython.py # 导入mysql模块 from pymysql import * class MysqlPython: def __init__(self, database, # 库 host="127.0.0.1", # ip地址 user="root", # 用户名 password="123456", # 密码 port=3306, # 端口 charset="utf8"): # 字符集 self.host = host self.database = database self.user = user self.password = password self.port = port self.charset = charset def open(self): # 创建数据库链接函数 self.db = connect(host=self.host, database=self.database, user=self.user, password=self.password, port=self.port, charset=self.charset) self.cur = self.db.cursor() # 创建游标对象 def close(self): # 创建断开数据库链接 关闭游标函数 self.cur.close() self.db.close() def zhixing(self, sql, L=[]): # 创建pymysql.execute() 方法函数 try: self.open() # 链接数据库 self.cur.execute(sql, L) # 参数化执行ＳＱＬ命令 self.db.commit() # 提交数据 print("ok") except Exception as e: self.db.rollback() # 出错取消提交 print("Failed", e) self.close() # 断开数据库链接 关闭游标 def all(self, sql, L=[]): try: self.open() self.cur.execute(sql, L) result = self.cur.fetchall() return result except Exception as e: print("Failed", e) self.close() 数据库用户登录 from mysqlpython import Mysqlpython from hashlib import sha1 uname = input("请输入用户名:") pwd = input("请输入密码:") # 用sha1给pwd加密 s1 = sha1() # 创建sha1加密对象 s1.update(pwd.encode("utf8")) # 指定编码 pwd2 = s1.hexdigest() # 返回16进制加密结果 sqlh = Mysqlpython("db4") select = "select password from user where \ username=%s;" result = sqlh.all(select, [uname]) # print(result) # (('7c4a8d09ca3762af61e59520943dc26494f8941b',),) if len(result) == 0: print("用户名不存在") elif result[0][0] == pwd2: print("登录成功") else: print("密码错误") ORM sqlalchemy框架 # 创建一张表 # 连接数据库的模块 from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String engine = create_engine("mysql+pymysql://root:123456@localhost/db4", encoding="utf8") Base = declarative_base() # orm基类 class User(Base): # 继承Base基类 __tablename__ = "t123" id = Column(Integer, primary_key=True) name = Column(String(20)) address = Column(String(40)) Base.metadata.create_all(engine)
