Python —— MySQL
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 约束条件名 FOREIGN KEY( 列名 ) 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 表; 逐行删除,并且会产生rollback,效率低。但是可以使用rollback回滚数据
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文件
要恢复的数据库必须已经创建,若要恢复的数据库不存在,则报错
正常情况如下,无任何提示
可以看到表已恢复成功
空间数据导入
https://my.oschina.net/u/4599572/blog/4473542