mysql

[mysql]# 设置mysql客户端默认字符集default-character-set=utf8[mysqld]#设置3306端口port = 3306# 设置mysql的安装目录basedir=D:\\mysql\\mysql-5.6.44-winx64# 设置mysql数据库的数据的存放目录datadir=D:\\mysql\\mysql-5.6.44-winx64\\data# 允许最大连接数max_connections=200# 服务端使用的字符集默认为8比特编码的latin1字符集character-set-server=utf8# 创建新表时将使用的默认存储引擎default-storage-engine=INNODB

默认配置

create user 'gcs'@'%' identified by '123456';# 创建账户并设置密码，%表示所有机器都可以连接

flush privileges;# 权限立即生效

show grants for 'gcs'@'%';# 查看用户数据库权限+---------------------------------+| Grants for gcs@%                |+---------------------------------+| GRANT USAGE ON *.* TO `gcs`@`%` |+---------------------------------+

https://dev.mysql.com/doc/refman/8.0/en/grant.html

账户相关

# Innodb存储引擎（mysql5.6之后的默认存储引擎）# 数据和索引存储在一起 2个文件    # 数据索引/表结构    # 数据持久化    # 支持事物：为了保证数据的完整性，将多个操作变成原子性操作(原子性：不可拆分的)    # 行级锁：修改的行少的时候使用                         -> 修改数据频繁的操作    # 表级锁：批量修改多行的时候使用                       -> 对于大量数据的同时修改    # 支持外键：约束两张表中的关联字段不能随意的添加、删除    -> 能够降低数据增删改的出错率

补充：    # select database() 查看当前库    # show engines; # 查看当前版本支持的存储引擎    # show variables like '%engine%'    # creat table tbname(fdname type) engine = Innodb; 创建表，并指定存储引擎

存储引擎# mysql5.6默认存储引擎Innodb

# 慢查询优化：    # 首先从sql的角度优化        # 把每一句话单独执行，找到效率低的表，优化这句sql        # 了解业务场景，适当的创建索引，帮助查询        # 尽量用连表代替子查询        # 确认命中索引的情况    # 考虑修改表结构        # 拆表        # 把固定的字段往前调整    # 使用执行计划，观察sql的type通过以上调整是否提高# mysql的慢日志    # 在mysql的配置中开启并设置一下    # 在超过设定时间之后，这条sql总是会被记录下来    # 这个时候我们可以对这些被记录的sql进行定期优化

sql慢查询优化

如何正确使用mysql数据库

# 表和数据的备份    # 备份数据在终端命令行直接执行    # mysqldump -uroot -p123456 -h172.16.238.4 homework > /home/zew/tem.sql    # 恢复数据，在mysql中执行命令    # 切换到一个要备份的数据库中    # sourse /home/zew/tem.sql

备份和恢复表

# 备份库    # 备份    # mysqldump -uroot -p123456 --databases homework > /home/zew/tem2.sql    # 恢复    # source /home/zew/tem2.sql

备份和恢复库

mysql数据库的备份与恢复

聚集索引与非聚集索引解析# 查询时尽量使用主键作为条件

## 多线程或并发情况下，添加锁# begin;  # 开启事务# select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；# update emp set salary=10000 where id = 1; # 完成更新# commit; # 提交事务

事物

mysql> show variables like '%character%';

字符集

select @@basedir as basePath from dual;# 查看mysql的安装目录# /usr/

select @@datadir as basePath from dual;# 查看mysql的数据目录# /var/lib/mysql/

select@@port as port from dual;# 查看端口

service mysql status# 查看服务状态 # Active: active (running)# Active: inactive (dead)

service mysql start# 启动服务

service mysql stop# 停止服务

# service mysql restart# 重启服务

mysql服务

查看默认配置

mysql> show databases;# 查看所有库信息

mysql> use dbname# 切换库

mysql> create database dbname;# 创建库

mysql> show tables;# 查看库下的所有表

mysql> drop database day39;# 删除库

库

mysql> desc t1; # 部分属性mysql> show create table t1; # 查看建表时的全部属性

# 修改表名alter table tbname rename 新名字;

# 添加字段alter table tbname add 字段名 类型(长度) 约束 after 某字段

# 删除某个字段alter table tbname drop 字段名;

# 修改某个字段的类型、约束alter table tbname modify 字段名 类型(长度) 约束

# 修改某个字段的名字、类型、约束alter table tbname change 旧字段名 新名字 类型(长度) 约束

修改表结构

# 删delete from tbname where 条件;# 清空表delete from tbname; # 不会清空主键的自增值truncate table tbname; # 会清空表和自增字段的偏移量

单表的增删改查

定义：通过关联关系创建一张大表，保存两张表的笛卡尔积，再根据条件筛选数据

# 内连接inner join  tbname on 关联条件

多表查询

其中一个查询结果作为另一个select查询的条件，或者其中一个查询结果作为另一个select查询关联的外表

mysql> select * from class where exists (select cid where caption = '一年二班');    +-----+--------------+    | cid | caption      |    +-----+--------------+    |   3 | 一年二班     |    +-----+--------------+    1 row in set (0.00 sec)    mysql> select * from class where exists (select cid where caption = '一年一班');    Empty set (0.00 sec)

子查询

表

sql的执行顺序

建库建表相关

int 不约束长度，最多表示10位

unsigned 无符号约束，例如年龄不能为负数

整型

浮点型

date# 年月日

time# 时分秒，常用，比如计时类软件

datetime# 年月日时分秒，常用

year# 年，不常用

timestamp# 时间戳，截止到2038年，已经不用了

+---------------------+| time                |+---------------------+| 2022-04-28 16:08:53 |+---------------------+1 row in set (0.00 sec)

+------------+| date       |+------------+| 2022-04-28 |+------------+1 row in set (0.00 sec)

+---------+| time    |+---------+| 31.4904 |+---------+1 row in set (0.00 sec)

时间类型

# 适合使用char    # 身份证号    # 手机号    # qq号    # username 12 - 18    # password 32    # 银行卡号

char(n)# 最多只能表示255个字符# 定长存储，节省时间，浪费空间# 不足的长度，右侧补空白# 需要频繁更改的固定长度字段，建议使用char

# 适合使用varchar    # 评论    # 朋友圈    # 微博

varchar(n)# 最多表示65535个字符# 变长存储，节省空间，存取速度慢# 对于不经常查看或更改的字段，使用varchar

字符型

单选enum和多选set

数据类型(常用)

int unsigned# 整型无符号约束

not null# 非空约束

unique# 唯一约束

default# 默认值约束

auto_increment# 自增约束# 至少是unique约束的列才能加自增约束，# 主要用于主键自增# 在自增约束的列，插入值时，可传null值，入库时会自增值

on update cascade# 外键级联更新，即：外表更新时，关联表同步更新

on delete cascade# 外键级联删除，即，外表删除是，关联表同步删除(不安全，不用)

foreign key(class_id) references class(id) on update cascade on delete cascade

foreign key(本表字段) references 外表(外表的字段)# 外键约束

约束 constraints

一对多/多对一# foreign key

多对多 （建两个外键）# 找出关联关系，建立第三张表，# 第三张存储那两张表的外键# 那两张表分别关联第三张表存储的列

一对一# 一张表唯一约束，另一张表建立 唯一约束加外键约束     # 客户    学生    # unique  foreign key + unique

如何确认表关系

表与表之间的关系

# b+树    # b是balance 平衡        # 为了保证每一个数据查找经历的IO次数都相同    # 只在叶子节点存储数据        # 为了降低树的高度    # 叶子节点之间加入双向连接        # 为了查找范围的时候比较快

B+树

# 聚集索引(聚簇索引)    # 全表的数据都存储到叶子节点上 -- Innodb存储引擎中的主键# 非聚集索引(非聚簇索引)/辅助索引    # 叶子节点不存放具体的整行数据，而是存储主键的值

聚簇索引和非聚簇索引

创建和删除索引

合理的创建和使用索引

索引

创建连接

cur.execute(sql语句) # 执行sql

执行sql语句

ret = cur.fetchone() # 获取一条结果ret = cur.fetchmany(10) # 获取n条结果ret = cur.fetchall() # 获取全部结果# 查询时，通过 try 捕获异常

查询

cur.execute(insert语句)cur.execute(update语句)cur.execute(delete语句)cur.commit() # 提交事物

# 执行增删改操作，需提交事物，# 如果出现异常，需要在异常处理中回滚事物cur.commit() 提交事物cur.roolback() # 回滚事物

增删改

利用rowcount 搭配fetchone循环遍历查询结果，获取使用 yeld/yeld from 得到一个生成器函数

cur.rowcount # 获取查询结果的行数cur.rownumber # 获取当前结果的行号

sql = \"select * from userinfo where user = %s and password = %s\

利用execute()传元组参数，避免sql拼接引起的sql注入

pymysql模块

mysql