开发者社区> 巴黎香榭> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: SQL命令大全、MySQL 、Python调用、MySQL常用命令大全
+关注继续查看

 
梦想与了信仰
 
  开局一张图

主要三个方面:
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)


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Python调用MySQL模块初试
学Python喊了很长时间了,总是因为各种各样的理由搁置,昨天想起来前同事推荐过一本Python的书《Python核心编程》第二版,就火速买了一本,Python的学习也算是个开始了。
1271 0
+关注
巴黎香榭
一个有梦想的咸鱼~~~
106
文章
1
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载