Python —— MySQL
- sql小点
- 域约束
- E-R 图
- 远程登录linux服务的MySQL
- 登录本机时
- 远程登录
- 创建一个允许任意主机登录的账号
- 一些其他命令
- 查看某库中存在哪些用户
- 查看权限
- 回收权限
- 删除用户
- mysql清屏 —— 仅linux
- 修改密码
- 数据库操作
- 创建数据库
- 修改数据库编码方式
- 创建表
- 删除表
- 查看表结构
- 查看建表语句
- 修改表
- 修改表名
- 移动表到别的数据库
- 添加字段
- 修改字段
- 删除字段
- 修改表的存储引擎 —— 最简便法
- 修改表的存储引擎 —— 最安全法
- 修改表的存储引擎 —— 最实用法
- 多表查询
- union
- inner join...on
- left join...on / left outer join...on
- right join...on / right outer join...on
- 视图
- 约束
- 外键约束
- 事务
- 脏读
- 不可重复读
- 幻读
- 隔离级别与脏读、幻读、不可重复读的关系
- 查询当前的隔离级别
- 修改隔离级别
- 创建保存点
- 删除保存点
- 事务回滚到保存点
- 存储过程
- 查询procedure信息
- 查询procedure创建语句
- 删除procedure
- 可传参的procedure
- MySQL函数
- concat()
- order by的部分排序
- now()
- rand()
- 聚合函数
- 日期函数
- 常用内置函数
- 日期计算类函数
- 字符串函数
- 其他函数
- mycli
- 删除mycli的历史记录
- 数据操作
- 增（insert into）
- 改 （update set）
- 删 （delete from、truncate）
- 复制表
- 复制表结构
- 复制表数据
- 复制表结构并插入数据
- 数据类型及字符编码
- 字符编码
- 字符编码
- 修改表字符编码
- 修改字段字符编码
- 校对集
- 数据类型
- 整数类型
- 小数类型
- 定点数类型
- 位类型
- 字符串类型
- 枚举
- 集合
- 时间类型
- 布尔类型
- 字段的常见属性
- MySQL运算符
- 注意点
- where 和 having 的区别
- 其他语句
- order by
- limit
- 经典实例
- 实例一、求各地区最高分的学生的详细信息
- pymysql
- sql注入
- 数据库的备份与恢复
- 备份
- 恢复
- 空间数据导入
sql小点
功能 | 关键字 | 描述 |
数据定义（DDL） | CREATE、DROP、ALTER | 增删改表结构 |
数据查询（DQL） | SELECT | 查询已有数据 |
数据操作（DML） | INSERT、UPDATE、DELETE | 增删改数据 |
数据控制（DCL） | GRANT、REVOKE | 数据的访问、修改权限 |
事务处理（TPL） | START TRANSACTION、COMMIT、ROLLBACK | 以原子化行为组来修改数据，保证在并发下的数据一致性 |
指针控制（CCL） | CURSOR相关 | 对多条记录逐行控制 |
mysql qps —> 5000~8000
域约束
create domain domainName fieldType check(value in ('value1', 'value2'));
域约束类似于字段枚举（ enum(‘value1’, ‘value2’)）
E-R 图
E-R图，即实体-关系（Entity-Relationship）图
E-R图的基本属性：
- 实体（表）：客观存在的事物、事件、角色等，比如采购员、老师、课程、订单等
- 实体属性（列）：用于描述实体的特性，每个实体可以有多个属性，比如老师的性别、名字、住址等
- 关系（表关联）：反映两个实体之间客观存在的关系
E-R图中的描述
- 方块 —— 实体
- 椭圆 —— 实体属性
- 菱形 —— 关系
上图的 任教 1：N 表示 1个老师只可带一个课，但是一个课可由多个老师带
关系表的设计过程
- 需求分析
- E-R图设计
- 数据库选型
- 实体转表，属性转列
- 一对一关系的，设置相同的主键列
- 一对多关系的，在多的表中设置外键关联一表的主键
- 多（M）对多（N）的，创建一个单独的表表示该关系，该表的两列由两表的主键值填充。且使用这两个列组合作为主键
- 重新审核所有的表，对需要的地方添加约束，对常用的条件字段设置索引
上图的 E-R 图可以将关系实例化为下表
课程表
列名 | 类型 | 索引、约束 | 作用及备注 |
id | INT | PK | 唯一标识 |
title | TEXT | NOT NULL | 课程名 |
period | INT | Index | 学时 |
description | TEXT | 课程描述 |
老师表
列名 | 类型 | 索引、约束 | 作用及备注 |
id | INT | PK | 唯一标识 |
name | TEXT | NOT NULL | 姓名 |
gender | BOOL | Index | 性别True：男False：女 |
address | TEXT | 住址 | |
course_id | INT | FK: course.id | 由于1:N关系所添加的外键 |
学生表
列名 | 类型 | 索引、约束 | 作用及备注 |
student_id | INT | PK | 学号，唯一标识 |
name | TEXT | NOT NULL | 姓名 |
age | INT | Index | 年龄 |
parent | TEXT | 家长 |
学生证表
列名 | 类型 | 索引、约束 | 作用及备注 |
student_id | INT | PK | 学号，唯一标识 |
startFrom | TIMESTAMP | 注册日期、入学日期 | |
endTo | TIMESTAMP | 本学生证有效期的截止日 |
学生及课程多对多关系表
列名 | 类型 | 索引、约束 | 作用及备注 |
student_id | INT | PK, FK: student.student_id | 学生标识 |
course_id | INT | PK, FK:course_id | 课程标 |
实体关系的实现
- 1:1关系的实现 —— 学生表和学生证表的相同主键
- 1:N关系的实现 —— 老师表的外键关联到课程表
- M:N关系的实现 —— 新建一张独立的表，两列共同组成主键，且两列分别通过外键关联学生、课程表
远程登录linux服务的MySQL
登录本机时
mysql -uroot -hlocalhsot -P3306 -p
远程登录
远程使用 root 登录会提示无法登录，回到 linux 的 mysql 中，创建一个新的用户，用于远程登录
mysql -uroot -h192.168.1.103 -P3306 -p
--- 创建用户
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
--- 授予权限
GRANT ALL ON *.* TO '用户名'@'主机' WITH GRANT OPTION;
--- 刷新权限
FLUSH PRIVILEGES;- *.* 代表所有数据库的所有表
- 权限类型 —— select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file
创建一个允许任意主机登录的账号
CREATE USER 'alun'@'%' IDENTIFIED BY '0403';
GRANT ALL ON *.* TO 'alun'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
一些其他命令
查看某库中存在哪些用户
use mysql;
select user, host from user;
查看权限
--- 查看当前用户权限
show grants;
--- 查看某用户权限
show grants for '用户'@'主机';
回收权限
--- 回收所有权限
revoke all privileges on *.* from '用户'@'主机';
--- 回收权限的传递
revoke grant option on *.* from '用户'@'主机';
删除用户
--- 切换数据库
use mysql;
--- 查看用户表中的用户及其主机
select user, host from user;
--- 删除用户
drop user '用户名'@'%';
mysql清屏 —— 仅linux
system clear;
修改密码
数据库操作
创建数据库
-- 创建数据库，并设置字符集为 utf-8
create database python_mysql charset=utf8;
-- 查看是否创建成功
show databases
修改数据库编码方式
alter database <databaseName> charset=utf8;
创建表
1、创建表需要先选中某个数据库
2、创建表时若不指定编码方式则默认使用数据库的编码方式
-- 选中数据库
use python_mysql;
-- 创建表
create table if not exists tableName(columnName columnType [params], .....)
-- 查看表是否创建成功
show tables;pramas：
- AUTO_INCREMENT —— 字段自增，一般用于主键
- NOT NULL —— 设置字段非空
- PRIMARY KEY —— 在定义字段时设置主键
- COMMENT —— 注释，用户的唯一标识
- UNIQUE —— 字段唯一约束，值只允许存在一次
设置主键，在tableName(col1, col2…, PRIMARY KEY ( columnName ))
支持字段类型如下
类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
删除表
drop table [if exists] <tableName>;
查看表结构
desc <tableName>;
describe <tableName>;
查看建表语句
show create table <tableName>;
修改表
对表的修改基本都使用 alter table，但中间所用的关键字有所不同
关键字 | 作用 |
rename | 重命名表 |
rename to | 移动至别的库 |
add / add first / add after | 添加字段 / 表最后添加 / 表某个字段后添加 |
modify | 修改字段属性 |
change / change after | 修改字段名 / 修改字段位置 |
drop | 删除字段 |
后面子节点中所有的 【属性】 均代表 primary key 等属性
修改表名
alter table <tableName> rename <newTableName>;
移动表到别的数据库
alter table <tableName> rename to <dbName.table>;
添加字段
末尾添加新字段
alter table 表名 add 字段名 数据类型 【属性】;首位添加新字段
alter table 表名 add 字段名 数据类型 【属性】 first;在某字段后添加新字段
alter table 表名 add 字段名 数据类型 【属性】 after 指定字段;
修改字段
修改字段属性
alter table 表名 modify 字段名 数据类型 【属性】;修改字段名
alter table 表名 change 原字段名 新字段名 数据类型 【属性】;修改字段位置
alter table 表名 change 原字段名 新字段名 数据类型 after 指定字段;
删除字段
alter table 表名 drop 字段名;
修改表的存储引擎 —— 最简便法
alter table 表名 engine=InnoDB;原理：MySQL按行将数据从原表复制到一张新表中
特点：
- 适用于任何引擎
- 执行时间很长
- 期间可能会消耗掉系统所有的I/O能力
- 会对原表上锁
修改表的存储引擎 —— 最安全法
使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎，同时修改表名（不可存在同名表），且要注意删除掉DROP TABLE语句，以免原数据丢失
修改表的存储引擎 —— 最实用法
原理：先创建一张新存储引擎表，再分块（或者不分块）的插入数据
不分块插入，数据量大时则执行速度很慢
create table 新表名 like 原表名;
alter table 新表名 engine=InnoDB;
insert into 新表名 select * from 原表名;
分块插入，需要以事务提交的方式运行，以免产生过多的undo
create table 新表名 like 原表名;
alter table 新表名 engine=InnoDB;
start transaction;
insert into 新表名 select * from 原表名 where 主键 between 值1 and 值2;
commit;
start transaction;
insert into 新表名 select * from 原表名 where 主键 between 值3 and 值4;
commit;
多表查询
union
作用：将两表上下合并（将表2追加到表1中）
用法：
select * from table1 union select * from table2;
注意：
1、列的数量必须匹配
2、列名、类型均无须匹配
3、默认列名取左边表
inner join…on
作用：横向连接两表，当没有on条件时，则求笛卡尔积。当有on条件时，则仅取符合on的两表交集
用法：
select xxx from t1 inner join t1 on t1.id = t2.id
select t1.field1, t1.field2, t2.field1 from t1 inner join t2
left join…on / left outer join…on
作用：横向连接两表，会保留左侧表中不符合 on 条件的数据
right join…on / right outer join…on
作用：横向连接两表，会保留右侧表中不符合 on 条件的数据
视图
语句：
create view viewName as ...
drop view viewName;
如：
create view V_STUDENT as (select s1.*, s2.math math, s2.english english from student s1 inner join score s2 on s1.id = s2.id);
特点：
1、视图相当于是一组查询语句，可以提升开发效率，但不会提升查询效率
2、创建视图依赖于一个查询
3、视图占用空间，且不会自动清除
4、视图适合多表联合查询，但不适合增、删、改，这可以提高执行效率
5、修改原表数据，会影响视图数据（视图是动态的）。修改视图数据，原表也会改变（视图表与原表联动）
6、在多表查询的视图中，无法同时修改多个基表
7、视图表是否可以被修改，依赖于创建视图表时的算法值 ALGORITHM —— 有三个可选值 UNDEFINED（默认，可修改）、MERGE（可修改）、TEMPTABLE（不允许修改）
在创建视图时指定算法
create algorithm=temptable view viewName as ...
约束
约束条件名的规范：
非外键：
2个字符的约束条件_作用的表名_作用的列
外键：
2个字符的约束条件_作用的表名_外键关联的表_作用的列
外键约束的写法：
CONSTRAINT 约束条件名 列名 REFREENCES( 外键连接表名( 外键主键列名 )
外键约束
功能：
1、通过某个字段将两个表关联起来
2、两表存在主表和从表
3、主表中存在的，从表中可以存在或者不存在对应数据。但主表中不存在的，不可在子表中存在
特点：
1、可以很好的保持数据一致性
2、外键的存在会导致每次数据插入、删除、修改的时候，均会对数据进行检查，以满足外键约束，所以对性能会有损耗
语法：
alter table 从表名 add constraint ‘fk_从表名_主表名_从表列名’ foreign key(字段) references 主表名(主表字段) ;
事务
作用：保证一个业务的一系列动作同时生效或失效，主要为 insert，update，delete 语句而设置
语法:
– 开始事务
start transaction;
***
***
***
commit;
特性：
- 原子性（Atomicity） —— 事务中所有操作，要么全部成功，要么全部失败，即不可细分执行事务的某一部分
- 一致性（Consistency） —— 写入的资料必须完全符合所有的预设规则，包含资料的精确度、串联性
- 隔离性（Isolation） —— 各个事务互相隔离，一个事务在提交前的修改，其他事务不可见（与事务隔离级别有关，可能会引发脏读、幻读、不可重复度的情况）
- 持久性（durability）—— 事务提交之后，其所作的修改即可持久化的保存在数据库中
脏读
含义：某个事务读取到了其他事务未提交的修改、删除或新增；
例：
原始数据
设置事务A的会话事务隔离级别为 read uncommitted，并开启事务A，查询表
设置事务B的会话事务隔离级别为 read uncommitted，并开启事务B，之后修改id = 1 的 age 为 20
-- 事务B
set session transaction isolation level read uncommitted;
start transaction;
update test1 set age = 20 where id = 1;
select * from test1;此时，事务B的查询结果为
此时，事务A的查询结果如下
两次对比（此时的事务B并未提交修改）
此时再在事务B中新增一条数据
insert into test1 values(9, '马云', 50);事务A再次查询，查询到未提交的新增数据
此时事务B在删除一条数据
delete from test1 where id = 5;事务A再次查询，未提交的删除也会被读取到
不可重复读
含义：某个事务上次和下次的读取，相同的数据 两次读取结果不同。即在某个事务中读取到了其他事务提交后的修改
例：
原始数据
设置会话事务隔离级别read committed，开启事务A，查询数据
设置会话事务隔离级别read committed，开启事务B，修改某条数据。并不提交
-- 事务B
set session transaction isolation level read committed;
start transaction;
update test1 set age = 20 where id = 1;
select * from test1;此时从事务A中读取数据，并未读到数据修改
提交事务B，使修改生效
commit;从事务A中进行查询，读取到了事务B提交修改的数据
幻读
含义：幻读与不可重复读类似，只是不可重复读是对数据修改而言，幻读则是对数据的新增而言，因为新增的数据是无法添加行级锁的，故二者存在区别
例：
原始数据
设置事务A会话事务隔离级别为 repeatable read，并开启事务A，读取数据
设置事务B会话事务隔离级别为 repeatable read，并开启事务B，修改数据，并不提交
set session transaction isolation level repeatable read;
start transaction;
update test1 set age = 20 where id = 1;
select * from test1;事务A读取不到被修改的数据
事务B提交修改
commit;事务A查询，依旧查询不到已经修改并提交后数据
此时提交事务A，再进行查询，查询到了事务B的修改
同上一系列操作，只是把修改数据改为新增数据
1、事务A开启事务并查询
2、事务B开启事务，并新增数据，不提交
set session transaction isolation level repeatable read;
start transaction;
insert into test1 values(9, '东方不败', 999);
select * from test1;事务A查询不到新增
3、事务B提交修改，事务A查询，依旧查询不到新增
那是不是可以说明，repeatable read不存在幻读呢？ 其实不然，这里我们通过在事务A中插入一条与事务B所插入数据相同id（主键）的数据，看看是否可以执行
然而提示，id 9 已经存在。所以幻读还是存在的，只是repeatable read的MVCC对部分幻读有过滤功能
隔离级别与脏读、幻读、不可重复读的关系
隔离级别 | 脏读 | 不可重复度 | 幻读 |
read uncommited | 存在 | 存在 | 存在 |
read commited | 不存在 | 存在 | 存在 |
repeatable read | 不存在 | 不存在 | 存在 |
serializable | 不存在 | 不存在 | 不存在 |
查询当前的隔离级别
show variables like ‘%isolation%’
修改隔离级别
set session/global transaction isolation level read uncommitted/read committed/repeatable read/serializable;
创建保存点
savepoint identifier;
删除保存点
release savepoint identifier;
事务回滚到保存点
rollback to identifier;
存储过程
功能：是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数来调用执行
语法：
1、修改结束符（默认为分号，但是分号常表示将SQL语句传递至服务器，所以需要修改结束符）
将$$替换;作为结束符，分号即表示普通字符
delimiter $$
– 或者
delimiter //
– 之后使用
select * from demp $$
2、声明存储过程
create procedure 存储过程名(in 参数名 参数类型)
3、存储过程的作用域标识符
begin … end
4、变量定义
declare 变量名 变量类型;
5、变量赋值
set @变量名=值
6、创建mysql存储过程
create procedure 存储过程名（参数）
7、存储过程体
create function 郭村函数名（参数）
优点：
- 存储过程可封装，并隐藏复杂的商业逻辑
- 存储过程可以回传值，并接受参数
- 存储过程无法使用select执行来运行
- 存储过程可以用在数据检验，强制实行商业逻辑等
缺点：
- 存储过程往往定制化于数据库，难以跨不同的数据库
- 存储过程的性能调教，受限于各个数据库
简例：
-- 定义
-- 如果存储过程中只有一条SQL语句，则可省略begin...end
create procedure get_info()
select * from student;
-- 调用
call get_info();
标准过程：
-- 修改结束符，以$$为结束符
delimiter $$;
-- 定义存储过程
create procedure get_info()
-- 开始
begin
-- 存储过程要执行的东西
select * from table1;
select * from table2;
select * from table3;
-- 结束时，要以修改后的结束符($$)作为结尾
end$$
--调用
call get_info()$$
查询procedure信息
语法
show procedure status like ‘%存储过程名%’
查询procedure创建语句
show procedure ‘存储过程名’
删除procedure
drop procedure 存储过程名;
可传参的procedure
例：
delimiter $$
create procedure foo(in uid int, in new_name varchar(32))
begin
select * from student;
update student set name=new_name where id=uid;
select * from student;
end$$
MySQL函数
concat()
用途：合并两个或以上的字符串
用法：concat( str/field, str/field, …)
order by的部分排序
用途：仅对某些数据进行排序
用法：order by field in(‘value1’, ‘value2’, …) —— 仅对 value1、value2、…等数据排序，其他数据不重新排序
now()
功能：用于生成当前时间，会自动匹配该列的时间格式
rand()
功能：生成 0 ~ 1之间的随机数
聚合函数
函数 | 作用 |
AVG() | 取均值 |
BIT_AND() | 按位与 |
BIT_OR() | 按位或 |
BIT_XOR() | 按位异或 |
COUNT() | 计数 |
COUNT( DISTINCT ) | 去重后计数 |
GROUP_CONCAT() | 连接字符串 |
JSON_ARRAY() | 将结果作为单个JSON数组返回 |
JSON_OBJECT | 将结果作为单个JSON对象返回 |
MAX() | 取最大值 |
MIN() | 取最小值 |
STD() | 求标准差 |
SUM() | 求和 |
VAR_POP() | 求标准差 |
VAR_SAMP() | 求方差 |
日期函数
函数 | 功能 |
str_to_date(str, format) | 字符串转日期 |
date_format(str, format) | 字符串转日期 |
unix_timestamp(日期) | 日期转时间戳 |
from_unixtime(时间戳) | 时间抽转日期 |
如：
- str_to_date(‘1980-12-31’, ‘%Y-%m-%d’);
- date_format(‘1980-12-31’, ‘%Y-%m-%d’);
- unix_timestamp(now())
- from_unixtime(1602989057)
常用内置函数
函数 | 功能 |
abs() | 取绝对值 |
floor() | 向下取整 |
ceil() | 向上取整 |
mod(x, y) | 取 x/y 的模 |
rand() | 取 0~1 随机数 |
round(x, y) | 对 x 以四舍五入的方式取到 y 位小数。若y为负数则向小数点前进行四舍五入 |
TRUNCATE(x, y) | 类似 round()，只是以舍尾法进行 |
日期计算类函数
函数 | 功能 |
CURDATE() | 返回当前日期 |
CURTIME() | 当前时间 |
NOW() | 当前日期和时间 |
YEAR(日期) | 年份 |
MONTH(日期) | 年份 |
DAY(日期) | 年份 |
WEEK(日期) | 一年中的第几周 |
HOUR(日期) | 小时 |
MINUTE(日期) | 分 |
SECOND(日期) | 秒 |
MONTHNAME(日期) | 月份英文名的全称 |
DATE_ADD(日期，时间间隔) | 在日期上加一个时间间隔的结果日期 |
DATEDIFF(时间1， 时间2) | 返回两个时间之间的天数，时间1-时间2 |
如：
select datediff('1980-12-12', '2020-12-12')select datediff(str_to_date('1980-12-12', '%Y-%m-%d'), str_to_date('2020-12-12', '%Y-%m-%d'));
字符串函数
函数 | 功能 |
CONCAT(str1, str2, …) | 将多个字符串拼接为一个 |
INSERT(str1, x, y, str2) | 将str1的位置位于x，长度为y的内容替换为str2 |
LOWER(str) / UPPER(str) | 切换大小写 |
LEFT(str, x) / RIGHT(str, x) | 取str的左/右的x长子串 |
LPAD(str, n, pad) / RPAD(str, n, pad) | 用字符串pad对str的左/右进行填充，填充至n个字符长度 |
LTRIM(str) / RTRIM(str) / TRIM(str) | 去除字符串 左/右/两侧 的空格 |
REPEAT(str, x) | 将str重复x次 |
REPLACE(str1, a, str2) | 用str2来替换str1中所有的a |
STRCMP(str1, str2) | 比较str1和str2，返回 1 —— str1 包含 str2，0 —— 匹配成功，-1 —— 其他情况 |
SUBSTRING(str, x, y) | 从str中取x开始y个长度的子串 |
其他函数
函数 | 作用 |
DATABASE() | 返回数据库名 |
VERSION() | 数据库版本 |
USER() | 登录用户 |
INET_ATON(IP) | 返回IP的数字表示 |
INTE_NTOA(IP的数值表示) | 将IP的数值转回IP |
PASSWORD(str) | 返回str的加密，mysql8已经弃用 |
MD5(str) | 返回str的MD5值 |
mycli
用途：具有关键字高亮、自动填充、多行等功能的增强版 cmd 中的 mysql
安装：pip3 install mycli
用法：mycli -uroot -h192.168.1.109
通过快捷键 F3 开启多行模式
删除mycli的历史记录
$ rm -rf .mycli-history
数据操作
增（insert into）
用途：向表中插入数据
用法：insert into table(column1, column2, …) values(value1, value2, …)
注意：
1、若设置主键自增，则主键可以显示的传值，也可以不传值；
2、若给自增主键传一个跳跃性的值，则后续的值自动在最大的值后进行递增
3、可以同时插入多行，插入多行的时候 … values 后不加括号，每一行为一个元祖，多行之间以逗号分隔
inset into 表名(列1，列2) values (列1值1，列2值1), (列1值2，列2值2);
改 （update set）
用途：用于修改表中的值
用法：update 表 set 字段a=值a where 筛选条件;
删 （delete from、truncate）
用途：用于删除表中数据
用法：delete from 表 where 条件;
注意：
1、若直接使用 delete from 表;
2、可以使用 truncate 表;
二者区别：
1、delete from 表;
2、 truncate 表; 直接清空表格，不会产生rollback，效率高。但不能rollback
复制表
复制表结构
功能：
语句：
create table 表名1 like 表名2;
表结构创建成功
但无数据
复制表数据
功能：将表2数据全部复制到表1，但不建议使用， 原表中字段的属性都不会被复制过来（通过 desc 表名 来查看表的属性）
语句：
create table 表名1 select * from 表名2;
表中字段属性没有复制成功
表中数据复制成功
复制表结构并插入数据
功能：在保留原表字段属性的同时，将数据同时复制过来
语句：
-- 复制表结构
create table 表2 like 表1;
-- 复制数据
insert into 表2 select * from 表1;
数据类型及字符编码
字符编码
字符编码
注意：mysql中，数据库、表及字符型字段均具有独自的编码类型。
1、在修改表的字符编码时，要注意同时修改字符型字段的编码类型；
2、要保持字段、表及数据库的编码类型一致
查看 mysql 的默认编码类型
show variables like '%character%';
查看某个数据库的编码类型
show create database 数据库名;
修改表字符编码
alter table 表名 charset=utf-8;
修改字段字符编码
注意：修改字段的编码类型时，使用 charset utf-8 没有等号（非 charset=utf-8） 与 改表不同
alter table 表名 modify 字段名 字段类型 charset utf-8;
校对集
1、查看校对集
show character set;
字段含义：
- Charset —— 字符集（各种编码类型，如utf-8，ascii，gbk，big5等）
- Description —— 字符集的描述信息
- Default collation —— 对字符集的详细描述，其中最后的字母为_ci则为大小写不敏感，为_cs则为大小写敏感
- Maxlen —— 字符集中每个字符允许的最大字节数
数据类型
整数类型
类型 | 字节 | 有符号范围 | 无符号范围 |
TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
SMALLINT | 2 | -28 ~ 28-1 | 0 ~ 216-1 |
MEDIUMINT | 3 | -212 ~ 212-1 | 0 ~ 224-1 |
INT、INTEGER | 4 | -216 ~ 216-1 | 0 ~ 232-1 |
BIGINT | 8 | -232 ~ 232 | 0 ~ 264-1 |
小数类型
类型 | 字节 | 最小值 | 最大值 |
FLOAT | 4 | ±1.17549E-38 | ±3.402823E+38 |
DOUBLE | 8 | ±2.2250738E-308 | ±1.79769313E+308 |
定点数类型
类型 | 字节 | 描述 |
DEC(M, D) | M+2 | 取值范围与DOBLE相同，具体范围由M和D决定 |
DECIMAL(M, D) | M+2 | 同上 |
M —— 支持的总长度
D —— 小数点后面的位数
用法：
float(10, 2)
double(10, 2)
decimal(10, 2)
位类型
类型 | 字节 | 最小值 | 最大值 |
BIT(M) | 1 ~ 8 | BIT(1) | BIT(64) |
字符串类型
类型 | 字节 | 描述 |
CHAR(M) | M | M为 0 ~ 255 之间的整数 |
VARCHAR(M) | M为 0 ~ 65535 之间的整数，值的长度+1个字节 | |
TINYBLOB | 允许长度 0 ~ 255 字节，值的长度+1个字节 | |
BLOB | 允许长度 0 ~ 65535 字节，值的长度+2个字节 | |
MEDIUMBLOB | 允许长度 0 ~ 167772150 字节，值的长度+4个字节 | |
LONGBLOB | 允许长度 0 ~ 4294967295 字节，值的长度+4个字节 | |
TINYTEXT | 允许长度 0 ~ 255 字节，值的长度+2个字节 | |
TEXT | 允许长度 0 ~ 65535 字节，值的长度+2个字节 | |
MEDIUMTEXT | 允许长度 0 ~ 167772150 字节，值的长度+3个字节 | |
LONGTEXT | 允许长度 0 ~ 4294967295 字节，值的长度+4个字节 | |
VARBINARY(M) | 允许长度 0 ~ M 个字节的变长字节字符串，值的长度+1个字节 | |
BINARY(M) | M | 允许长度 0 ~ M 个字节的定长字节字符串 |
CHAR(M) 和 VARCHAR(M) 的M是指字符长度，中英文字符相同（MySQL引擎自动处理不相同处）
CAHR和VARCHAR的区别：
1、CHAR(M) —— Size 固定为 5 Bytes
VARCHAR(M) —— Size 变长，大小为内容+1 Bytes，多的一个用来记录长度
2、VARCHAR会删除最后的空格，不显示空格，但是会把长度记录下来。由此可以节省空格所占的空间
3、CHAR浪费空间，节省时间。VARCHAR节省空间，浪费时间。但二者速度差距不大，一般使用VARCHAR即可。
如：有下表
现插入输入如下
当英文字符长度超过3个时，插入失败
中英文混合长度超过3个时，同样失败
枚举
功能：提供一个选择集，该字段的值仅允许从创建字段时设置的枚举项中进行选择，不匹配则抛出异常，值仅可为枚举选项的其中一个。
语法：enum(‘选项1’, ‘选项2’, ‘选项3’)
集合
功能：
语法：
1、创建字段语法：set(‘值1’, ‘值2’, ‘值3’)
2、插值语法：values(‘值1, 值2, 值3’)
时间类型
类型 | 字节 | 最小值 | 最大值 |
DATE | 4 | 1000-01-01 | 9999-12-31 |
DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAP | 4 | 19700101080001 | 2038年的某个时刻 |
TIME | 3 | -838:59:59 | 838:59:59 |
YEAR | 1 | 1901 | 2155 |
-- 时间列为 datetime 数据类型
insert into 表名(时间列) values('2000-01-01 12:00:00')
insert into 表名(时间列) values(now())时间分隔可以用 “-” 或者 “/”
布尔类型
True、False或其他可以布尔化的值，若以数值传入则最大接受 127，因为布尔类型的字段以TINYINT存储
字段的常见属性
属性 | 含义 | 用法 |
PRIMARY KEY | 主键 | 可以设置组合主键（primary key(id, sid)），但是建议不要这么做 |
AUTO_INCREMENT | 自增 | 若此字段不给值，则从上一行增加1作为此行值，默认从1开始 |
NOT NULL | 非空约束 | |
DEFAULT | 默认值 | 若某行不给此值，则使用default的值， default 123 |
UNIQUE | 唯一约束 | |
COMMENT | 注释 | 用来给开发者查看，用于说明字段 |
UNSIGNED | 无符号型 | 用于对数值型字段进行设置 |
MySQL运算符
运算符 | 作用 |
=、>、<、!=、<>、<=、>= | 常规运算符 |
<=> | 比较是否为空，是返回1，否则返回0。若用 = 比较，则结果为 NULL |
BETWEEN | 指定范围 |
IN | 指定集合 |
IS (NOT) NULL | 判断是否为NULL |
LIKE | 通配符匹配，% —— 任意多个任意字符，_ —— 一个任意字符 |
REGEXP 或 RLIKE | 正则匹配 |
正则表达式的使用
select * from emp
where hiredate regexp '1987.*';
注意点
where 和 having 的区别
相同点：都用来进行条件筛选
不同点：
1、where 执行顺序在 group by 之前，having执行顺序在group by之后
2、having只能对 select 筛选出的字段进行条件筛选，若 select 中没有选择出字段，则having无法以此字段作为筛选条件
3、where 无法使用 select 选择出的字段的别名进行筛选，having 可以
如：
1、where 以字段别名进行筛选 —— 失败
2、having以字段别名进行筛选 —— 成功
4、having后可以跟聚合函数，where不可以。当having后跟聚合函数的时候，被聚合的列可以不在select中
其他语句
order by
升序 —— asc
降序 —— desc
limit
limit n —— 仅选出前n项
limit m, n —— 选出行数为（m, n] 的数据
limit n offset m —— 跳过前m个，从第m+1个开始，向下选出n个
经典实例
实例一、求各地区最高分的学生的详细信息
基础数据：
1、表1 —— 学生信息表，不包含成绩
2、表2 —— 学生成绩表，仅有ID与学生信息对应
需求：按城市分组，求出每个城市中数学最高的那个学生的所有详细信息及成绩
思路：
1、创建具有成绩的学生详细信息视图
2、求此视图与按城市分组的最高分的交集，交集以城市和数学最高分为条件
答案：
-- 创建具有成绩信息的学生信息视图
create or replace view V_EXAM as select t1.*, t2.math, t2.english from student t1 inner join score t2 on t1.id = t2.id;
select v1.* from V_EXAM v1 inner join ( select city, max(math) math from V_EXAM group by city ) t1 on v1.city = t1.city and v1.math = t1.math;
pymysql
使用库：pymysql
使用过程：
1、连接数据库
import pymysql
# 连接mysql
db = pymysql.connect(host="192.168.1.106", port=3306, charset="utf8", user="用户", password="密码", database="mysql_python")
2、创建游标
# 创建游标
cur = db.cursor()
3、执行SQL语句
# 执行sql语句
cur.execute("select * from test1;")
4、获取执行结果
# 获取执行结果
res = cur.fetchall()
print(res)
5、关闭数据库连接
cur.close()
db.commit()
db.close()
sql注入
原理：通过传入注释符，修改代码中的sql语句来达到侵入的目的
需求：
1、sql语句在代码中的传参需要以字符串模板的形式进行，如
sql = “select * from table1 where name=’%s’ and password = ‘%s’” % (name, password)
2、在传参时，用户名后传注释符，如
name = zhangsan’#
原因：后面的引号表示字符串结束，#表示注释语句。则效果为修改sql语句为
“select * from table1 where name=‘zhangsan’# and password =’%s’”
解决：
sql的传参不以模板字符串的形式进行，而是通过库的借口来传，如
sql = “select * from table1 where name=’%s’ and password = ‘%s’”
cur.execute(sql, (name, password))
数据库的备份与恢复
备份
备份数据库
mysqldump -hlocalhost -uroot -p <数据库名> <输出的sql文件名>
如：
mysqldump -hlocalhost -uroot -p mysql_python > \home\alun\mysql_dump\home_mysql_python.sql
文件内容如下：
恢复
mysql -hlocalhost -uroot -p mysql中要恢复的数据库名 < 备份的.sql文件
要恢复的数据库必须已经创建，若要恢复的数据库不存在，则报错
正常情况如下，无任何提示
可以看到表已恢复成功
空间数据导入
