MySQL语句

基础

>>> mysql -u root -p
show databases; # 显示所有
use 数据库名;  # 切换到指定数据库中
show tables; # 查看当前数据中所有表
# 添加地址
# ip地址：% 表示所有
create user '用户名'@'ip地址' identified by '密码'; 
# 添加权限
grant all privileges on 数据库名.* from '用户名'@'ip地址';
grant all on *.* to '用户名'@'ip地址'
# 修改密码
 set password for 用户名@localhost = password('新密码');

数据库操作

# 创建数据库, 指定字符为utf8编码
create database 数据库名 default charset utf8;
# 删除数据库
drop database 数据库名;

表操作

# 创建表
create table t1(
    id int not null,  
    name char(10
) engine=innodb default charset=utf8;
# engine：    
#     innodb 支持事务
#     myisam
# 数据类型
    数字：
        tinyint:
            有符号：-128~127
            无符号：0 ~ 255
        int：
        bigint：
        float：
        double：
        decimal：
    字符串:
        char     定长，速度快，max:255
        varchar  变长，节省空间，速度慢 max:255
        text：
            max:65535字符
        mediumtext：
            max:2**24字符
        longtext：
            max:2**32字符
        ps:创建数据表时定长列往前放
    时间类型:
        date:
            年月日
        time:
            时分秒
        year:
            年
        datetime:
            年月日时分秒
        timestamp:
    枚举:
        enum
    集合:
        set
# 列配置
    unsigned  # 数字类型，无符号
    null/not null # 是否可以为空
    default 值 #默认值
    auto_increment # 自增，必须是主键
    primary key  # 主键 约束(不能重复且不能为空)，一个表中只能有一个
# 清空表
    delete from t1;
    truncate table t1;
# 删除表
    drop table t1;

desc 表名; # 查看表结构
show create 表名; # 查看表创建sql语句
show create 表名 /G; # 竖着看
alter talbe 表名 auto_increment=1; # 修改表自增值

基本CURD

# 插入数据
insert into t1(id, name) values(1, 'zhangsan');
# 删除
delete from t1 where id<5;
# 修改
update t1 set age=18 where id=5;
# 查看数据
select * from t1;

约束

主键

primary key
(不能重复且不能为空)，一个表中只能有一个

唯一索引

unique
不能重复,可以为空

create table t1(
    id int auto_imcrenent primary key,
    ID_number char(15),
    unique unique_in(ID_number)
)

普通索引

create table t1(
    id int auto_imcrenent primary key,
    ID_number char(15),
    unique index(ID_number)
);

外键

create table userinfo(
    uid int auto_increment primary key,
    department_id int,
    constraint fk_user_depar foreign key(department_id) references department('id') # 外键约束
)engine=innodb default charset=utf8;
create table table department(
    id tinyint auto_increment primary key,
    title char(15)
)engine=innodb default charset=utf8;

数据操作

增

insert into t1(name,age) values ('zhangsan', 16),('lishi', 19)
insert into t1(name, age) select name,age from t2

删

delete from t1 where id=5;

改

update t1 set name='wanwu',age=22 where id=6

查

AS 定义别名
WHERE 条件
    条件:
        IN ()  指定的值
        NOT IN () 不在指定的值
        BETWEEN n AND m 指定区间[n,m]
        LIKE 模糊查询 通配符 %:多个字符 _:一个字符
LIMIT
    LIMIT n # 前n条记录
    LIMIT m,n # 从第m条开始取n条, m从0开始
ORDER BY
    排序:
        默认:顺序 ASC
        倒序: DESC
GROUP BY
    分组
    聚合函数：
        COUNT(), MAX(), MIN(), SUM(), AVG()
    对聚合函数结果进行二次筛选必须使用 having
CASE WHEN 条件 THEN 为真值 ELSE 为假值 END
IF(isnull())

连表操作

LEFT JOIN 
    左连接，左边表全部显示
    SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.tid
RIGHT JOIN
    右连接，右边表全部显示
union 上下连表， 自动去重
UNION ALL 不去重

命令行

备份

备份数据表结构+数据
mysqldump -u root db1 > db1.sql -p
备份数据表结构
mysqldump -u root -d db1> db1.sql -p
执行文件
# 先创建数据库
create database db5 default charset utf8
# 再执行文件
mysqldump -u root -d db5 < db1.sql -p

视图

# 创建视图
create view 视图名称 as sql
# 修改视图
alter view 视图名称 as sql
# 删除视图
drop view 视图名称

函数

内置函数

# 时间
CURDATE()  当前时间 年-月-日
CURRENT_TIMESTAMP() 年月日时分秒
DATA_FORMAT() 时间格式化

自定义函数

DELIMITER //
CREATE FUNCTION f1(
    p1 INT,
    p2 INT)
RETURN INT
BEGIN
# 函数体
DECLARE 变量名 类型 [DEFAULT 设置默认值];
END //
DELIMETER

存储过程

保存在MySQL上的一个别名(一堆sql语句)
用于替代程序员写SQL语句

# 创建存储过程
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
    # SQL语句
END //
DELIMETER
CALL p1()
# 传参数 IN
DELIMITER //
CREATE PROCEDURE p2(
    IN n1 INT,
    IN n2 INT
)
BEGIN
    # SQL语句
END //
DELIMETER
# 传参数 IN
DELIMITER //
CREATE PROCEDURE p2(
    IN n1 INT,
    OUT n2 INT
)
BEGIN
    # SQL语句
    SET n2 = 1111;
END //
DELIMETER
SET @v1 = 0;  # 创建session级别的变量
call(12, @v1); # @v1 = 1111

索引

约束
加速查找

索引种类：
    hash索引
        单值块
        范围
    btree索引
        二叉树
建立索引：
    额外的文件保存特殊的数据结构
    查询快，插入更新删除慢
    命中索引

普通索引：
    CREATE INDEX 索引名 on 表名(列名)
    DROP INDEX 索引名 on 表名
唯一索引：
    CREATE UNIQUE INDEX 索引
组合索引
    多个字段组成索引
    最左前缀
频繁查找的列创建索引
    创建索引
    命中索引
like
    数据量比较大，避免使用
函数
or
类型不一致
!=
order by
组合索引，最左前缀

执行计划

# 预估执行操作
explain sql语句
type：
    查询的访问方式
    性能：all < index < range < index_merge < ref_or_null < ref < eq_erf < system/const 
    ALL 全表扫描
    index 全索引扫描
    RANGE 对索引进行范围查找
    INDEX_MERGE 合并索引
    ref 根据索引找一个或多个值
    eq_ref 连接时使用primary ley 或 unique 类型
    const 常量
    system 系统

大数据分页优化

记录当前页最大和最小id

页面只有上一页和下一页
``
下一页
记录当前页最大id page_max_id
where id > page_max_id limit 10
上一页
记录当前页最小id page_min_id
where id < page_min_id order by id limit 10
页面上有当前页左右页码
往下翻页 select_page > now_page
count = (select_page - now_page) * 10 # 10 每页显示条数

select * from user where id in(
    select id form (
        select id from user where id > page_max_id limit count) as t1 order by as.id desc limit 10
    )
)

python

pymysql

pip3 install pymysql

import pymysql
conn = pymysql.connect(host='loalhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 得到的结果集的元素为字典
# sql = 'select * from tb1 where user=%s' % user # sql拼接会有SQL注入问题
sql = 'select * from tb1 where user=%s and passwd=%s'
cursor.excute(sql, user, passwd)
cursor.excute(sql, [user, passwd])
sql = 'select * from tb1 where user=%(u)s and passwd=%(p)s'
cursor.excute(sql, {'u':user, 'p':passwd})
res = cursor.fetchone()  # 获取结果集中一条记录，指针后移一位
# 移动指针
cursor.scroll(位置,mode='relative') # 相对当前位置移动
cursor.scroll(位置,mode='absolute') # 相对绝对位置移动
res = cursor.fetchall()  # 获取所有
cursor.close()
conn.close()

# 增删改
sql = 'insert into userinfo(username,password) values(%s,%s)'
res = cursor.execute(sql) # res 受影响的值
# 可批量添加 ， 只能insert时使用
cursor.executemany(sql, [(...),(...)])
# 获取插入的自增id, 多条同时插入时返回最后一条id
cursor.lastrowid
conn.commit()  # 修改值时 需要commit

ORM框架 SQLAlchemy

关系对象映射
作用:

提供简单的规则
自动转换成SQL语句

Python笔记

Python数据库操作

MySQL语句

基础

数据库操作

表操作

基本CURD

约束

主键

唯一索引

普通索引

外键

数据操作

增

删

改

查

连表操作

命令行

视图

函数

内置函数

自定义函数

存储过程

索引

执行计划

大数据分页优化

python

pymysql

ORM框架 SQLAlchemy