mysql常用命令大全

用户权限管理

• 创建和删除用户

create user "用户名"@"用户IP" identified by "密码"
drop user  "用户名"@"用户IP" ; 	

• 更改密码

set password for "用户名"@"用户IP = password("密码")"  

• 查看授权

  show grants for ‘用户’@‘IP地址’

• 取消授权

  revoke 权限 on 数据库.表 from ‘用户’@‘IP地址’

数据库

• 增
  create database test default charset utf8;
• 删
  drop database test;
• 查
  show databases;
• 使用
  use test;

数据表

• 导入导出
  • 导入：mysql -u root -p 数据库名 < 文件路径/db.sql
  • 导出：mysqldump -u root -p 数据库名 > 文件路径/db.sql
• 增
  create table table_test(
  id int not null auto_increment primary key,
  name varchar(32) not null
  )default charset = utf8;
• 删
  drop table 表名;
• 改

  • 添加列
    alter table 表名 add 列名 类型；

  • 删除列
    alter table 表名 drop column 列名；

  • 修改列 类型
    alter table 表名 modify column 列名 类型;

  • 修改列 类型和名称
    alter table 表名 change 原列名 新列名 新类型;

  • 修改列 默认值

    ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

  • 删除列 默认值

    ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

  • 添加主键

    alter table 表名 add primary key(列名);

  • 删除主键

    alter table 表名 drop primary key;

  • 查看表的结构：
    describe test;

  • 清空表
    delete from 表名;
    truncate table 表名;（速度快、无法回滚撤销等）

数据行

• 增
  insert into 表名（列名1，列名2）values(值1，值2);
• 删
  delete from 表名 where 条件;
• 改
  update 表名 set 列名=值;
• 查
  select * from 表名；
• sql防注入
  execute(“select * from users where name=%s and password=%s”, [user, pwd])

必备语句扩展

条件语句

注释： –
where:当…返回结果

select * from 表名 where id between 2 and 4;
– 查询表中id大于等于2、且小于等于4

模糊搜索

• 使用通配符
  “” 匹配单个字符,”_” 匹配””
  “%” 匹配任意个字符,包括零个字符
  MySQL支持正则

select * from 表名 where 列名 like “正则”;

排序

select * from 表名 order by age desc; – 倒序
select * from 表名 order by age asc; – 顺序

显示部分数据

select * from 表名 limit 5;
– 显示前五行数据

分页，查询时用索引,expalin可以看你查询计划
expalin select id from 表名 limit 10 offset 10;
– 从第十行开始往后取十行

连表

左右：
主表 left outer join 从表 on 主表.x = 从表.id

联合查询

select id,title from table1
union
select email,name from table2 ;
– 列数需相同,自动去重

union all
– 保留所有

表关系

单表
一对多 多对一
多对多

外键约束

在创建一对多的表时，表二的某一列必须为表一的某一列已存在的一个值，这个时候就需要给表二增加一个外键约束
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]
alter table boy_girl add constraint 外键名 foreign key 表名(列名) references 表名(列名);
alter table info drop foreign key 外键名;

索引

MySQL底层使用B-tree 的数据结构存储，利用索引可以快速的进行查询
引擎
定义
engine=引擎
- mysiam
- innodb(常用）
索引分类

主键索引

使用primary key关键字创建

• primary key(id)
  联合主键索引
• primary key(列1，列2）-- mysiam支持
• 特点
  一个表中只能有一个主键。如果在其他字段上建立主键，则原来的主键就会取消
  主键的值不可重复，也不可为空
• 删除
  ALTER TABLE 表名 DROP PRIMARY KEY;

唯一索引

主键索引不能有空值，但是唯一索引可以有空值。

• unique id_u(id) --创建；
• create unique index 索引名 on 表名(列名);
• alter table 表名 drop index name; --删除
• drop unique index 索引名 on 表名;

普通索引(INDEX)

最基本的索引，没有任何限制

• 创建
  create index 索引名 on 表名(列名);
  index id_u(id)
• 删除
  drop index 索引名 on 表名;

全文索引(FULLTEXT )

可用于 MyISAM 表，mysql5.6之后也可用于innodb表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时和空间。

索引优缺点

• 优点：查找速度快、约束（唯一、主键、联合唯一）
• 缺点：插入、删除、更新速度比较慢，因为每次操作都需要调整整个B+Tree的数据结构关系。

执行计划

explain + SQL语句;
可以看到执行这条语句的计划用来计算性能
其中重要的是 type，他他SQL性能比较重要的标志，性能从低到高依次：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

事务

innodb引擎中支持事务，myisam不支持。

事务四大特性

• 原子性（Atomicity）：要么全部成功，要么全部失败回滚
• 一致性（Consistency）：执行的前后数据的完整性保持一致。
• 隔离性（Isolation）：一个事务执行的过程中,不应该受到其他事务的干扰。
• 持久性（Durability）：事务一旦结束,数据就持久到数据库

创建事务

begin; – 开启事务 start transaction;
事务代码
commit; – 提交事务 rollback;

锁

update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。
select去申请锁，则需要配合 事务 + 特殊语法来实现。
MySQL中自带了锁的功能

• 排它锁（ for update）
  加锁之后，其他事务不可以读写。
• 共享锁（lock in share mode）
  可以读，但不允许写。

数据库连接池

• 安装第三方库
  pip3 install dbutils

import threading
import pymysql
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。
    # 如：0 = None = never, 1 = default = whenever it is requested, 
    # 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task():
    # 去连接池获取一个连接
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 将连接交换给连接池
    conn.close()

def run():
    for i in range(10):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()