Python全栈 MySQL 数据库(SQL命令大全、MySQL 、Python调用)

时间:2023-03-08 16:37:00
为了梦想与了信仰
 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) # 参数化执行SQL命令
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)