这篇博客详细介绍了MySQL数据库的基础知识，包括数据库的创建、操作，存储引擎的对比，数据类型，表的操作，如创建、修改和删除，以及数据的插入、查询、修改和删除。还讲解了MySQL的索引原理，存储引擎InnoDB和MyISAM的特点，以及如何使用Python的pymysql模块操作MySQL数据库。此外，文章涉及了MySQL的备份与恢复、事务处理和锁机制，以及SQL注入问题和解决方案。
文章目录
1 初始数据库
1）数据库
- 数据库（DataBase，简称DB）
- 数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的
- 数据库是长期存放在计算机内、有组织、可共享的数据集合。
- 数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种 用户共享
- 数据库管理系统（DataBase Management System 简称DBMS）
- 用于科学地组织和存储数据，如高效获取和维护数据的系统软件
- 如MySQL、Oracle、SQLite、Access、MS SQL Server
- mysql主要用于大型门户
- oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。
- 数据库服务器、数据管理系统、数据库、表与记录的关系
- 记录：1 小明 123456789 22（多个字段的信息组成一条记录，即文件中的一行内容）
- 表：userinfo, studentinfo, courseinfo（即文件）
- 数据库：db（即文件夹）
- 数据库管理系统：如mysql（是一个软件）
- 数据库服务器：一台计算机（对内存要求比较高）
- 总结：
- 数据库服务器：运行数据库管理软件
- 数据库管理软件：管理-数据库
- 数据库：即文件夹，用来组织文件/表
- 表：即文件，用来存放多行内容/多条记录
2）mysql
- 数据库管理软件分类
- 关系型：
- 关系型数据库需要有表结构
- 如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
- 非关系型：
- 非关系型数据库是key-value存储的，没有表结构
- mongodb，redis，memcache (既可以做消息中间件，也可以做数据库)
- 关系型：
- mysql
- MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
- MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
- MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。
- 特点：体积小、速度快、成本低，开放源码
3）和用户权限相关的
- 环境变量
- 在命令行模式下输入python–>实际执行
python.exe - 在任何目录下等能找到
python.exe文件 - 只有在环境变量中输入
python.exe文件的路径才能在任意位置输入python命令启动python解释器
- 在命令行模式下输入python–>实际执行
- 安装mysql服务——
mysqld install mysql服务就被注册到操作系统中 - 启动mysql服务——
net start mysql - 停止mysql服务——
net stop mysql - 启动mysql客户端连接到server端(登录)——
mysql -uroot -p - 查看当前登录用户——
mysql>select user(); - 给当前用户设置密码——
mysql>set password = password('123'); - 创建一个其他用户——
create user 'guest'@'主机IP/主机域名' identified by '123'; - 远程登陆——
mysql -uroot -p123 -h 192.168.10.3 - 给一个用户授权
grant 权限类型 on ftp.* to 'guest'@'192.168.14.%';grant all/select/insert/update/deletemysql> flush privileges;# 刷新使授权立即生效
4）基本操作
- 操作数据库
- 查看所有数据库——
show databases; - 创建一个数据库——
creat database 数据库名; - 切换到这个数据库下——
use 数据库名; - 查看数据库下的所有表——
show tables; - 删除数据库——
drop databse 数据库名;
- 查看所有数据库——
- 操作表
- 创建一张表——
create table student(name char(12),age int); - 查看表结构——
desc student;
- 创建一张表——
- 操作数据
- 插入数据——
insert into student values ('wusir',73); - 查询数据——
select * from student; - 修改数据——
update student set age=85 where name='alex'; - 删除数据——
delete from student where name = 'alex';
- 插入数据——
2 表操作
1）存储引擎
-
定义：存储数据的方式
-
常用的三个存储引擎：
- Innodb存储引擎
- 数据和索引存储在一起 2个文件——数据索引\表结构
- 数据持久化
- 支持事务：
- 为了保证数据的完整性**,将多个操作变成原子性操作**
- 保持数据安全
- 支持行级锁 :
- 修改的行少的时候使用
- 修改数据频繁的操作
- 支持表级锁 :
- 批量修改多行的时候使用
- 对于大量数据的同时修改
- 支持外键 :
- 约束两张表中的关联字段不能随意的添加\删除
- 能够降低数据增删改的出错率
- Myisam存储引擎
- 数据和索引不存储在一起 3个文件——数据\索引\表结构
- 数据持久化
- 只支持表锁
- Memory存储引擎
- 数据存储在内存中, 1个文件——表结构
- 数据断电消失
- 三个存储引擎的文件存储结构
- engine1——Innodb engine2——Myisam engine3——Memory
- Innodb存储引擎
-
面试题
- 你了解mysql的存储引擎么?
- 答案如上
- 你的项目用了什么存储引擎,为什么?
- 用了innodb数据存储引擎
- 多个用户操作的过程中对同一张表的数据同时做修改
- innodb支持行级锁,所以我们使用了这个存储引擎
- 为了适应程序未来的扩展性,扩展新功能的时候可能会用到…(事务),涉及到要维护数据的完整性
- 项目中有一两张xx xx表,之间的外键关系是什么,一张表的修改或者删除比较频繁,怕出错所以做了外键约束
- 你了解mysql的存储引擎么?
-
存储引擎在mysql中的引用
- 存储引擎相关语句
- 查看当前的默认存储引擎
mysql> show variables like 'default_storage_engine';
- 查看当前数据库支持的存储引擎
mysql> show engines;
- 查看当前的默认存储引擎
- 存储引擎相关语句
-
指定存储引擎建表
- 指定存储引擎建表
mysql> create table engine1(id int(4),name char(20)) ENGINE=InnoDB;mysql> create table engine2(id int(4),name char(20)) ENGINE=MyISAM;mysql> create table engine3(id int(4),name char(20)) ENGINE=Memory;
- 也可以使用
alter table语句，修改一个已经存在的表的存储引擎。mysql> alter table engine2(表名) engine = innodb;
- 指定存储引擎建表
2)表和数据的一些基本操作
-
创建表
-
语法
-
create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); 放在[]内的内容可以不写
-
-
-
往表中写入数据的方式
insert into 表 values (值1,值2,值3);- 这张表有多少的字段,就需要按照字段的顺序写入多少个值
insert into 表 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3);- 一次性写入多条数据
insert into 表 (字段1,字段3 ) values (值1,值3);- 指定字段名写入,可以任意的选择表中你需要写入的字段进行
-
查表中的数据
select * from 表;
-
查看表结构
desc 表名;- 能够查看到有多少个字段\类型\长度,看不到表编码,引擎,具体的约束信息只能看到一部分
show create table 表名;- 能查看字段\类型\长度\编码\引擎\约束
3）mysql中的数据类型
-
数字
-
类型 大小 范围（有符号） 范围（无符号）unsigned约束 用途 TINYINT 1 字节 (-128，127) (0，255) 小整数值 SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 字节float(255,30) (-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 字节double(255,30) (-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+2double(65,30) 依赖于M和D的值 依赖于M和D的值 小数值 -
实例
-
整数实例
# 创建表一个是默认宽度的int，一个是指定宽度的int(5) mysql> create table t1 (id1 int,id2 int(5)); Query OK, 0 rows affected (0.02 sec) # 像t1中插入数据1，1 mysql> insert into t1 values (1,1); Query OK, 1 row affected (0.01 sec) # 可以看出结果上并没有异常 mysql> select * from t1; +------+------+ | id1 | id2 | +------+------+ | 1 | 1 | +------+------+ row in set (0.00 sec) # 那么当我们插入了比宽度更大的值，会不会发生报错呢？ mysql> insert into t1 values (111111,111111); Query OK, 1 row affected (0.00 sec) # 答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响 mysql> select * from t1; +------------+--------+ | id1 | id2 | +------------+--------+ | 0000000001 | 00001 | | 0000111111 | 111111 | +------------+--------+ rows in set (0.00 sec) # 修改id1字段 给字段添加一个unsigned表示无符号 mysql> alter table t1 modify id1 int unsigned; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id1 | int(10) unsigned | YES | | NULL | | | id2 | int(5) | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ rows in set (0.01 sec) # 当给id1添加的数据大于214748364时，可以顺利插入 mysql> insert into t1 values (2147483648,2147483647); Query OK, 1 row affected (0.00 sec) # 当给id2添加的数据大于214748364时，会报错 mysql> insert into t1 values (2147483647,2147483648); ERROR 1264 (22003): Out of range value for column 'id2' at row 1 int整数示例 -
小数实例
# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位 mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2)); Query OK, 0 rows affected (0.02 sec) # 向表中插入1.23，结果正常 mysql> insert into t2 values (1.23,1.23,1.23); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | +------+------+------+ row in set (0.00 sec) # 向表中插入1.234，会发现4都被截断了 mysql> insert into t2 values (1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | +------+------+------+ rows in set (0.00 sec) # 向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则 mysql> insert into t2 values (1.235,1.235,1.235); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | | 1.24 | 1.24 | 1.24 | +------+------+------+ rows in set (0.00 sec) # 建新表去掉参数约束 mysql> create table t3 (id1 float,id2 double,id3 decimal); Query OK, 0 rows affected (0.02 sec) # 分别插入1.234 mysql> insert into t3 values (1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.00 sec) # 发现decimal默认值是(10,0)的整数 mysql> select * from t3; +-------+-------+------+ | id1 | id2 | id3 | +-------+-------+------+ | 1.234 | 1.234 | 1 | +-------+-------+------+ row in set (0.00 sec) # 当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别 mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t3; +---------+--------------------+------+ | id1 | id2 | id3 | +---------+--------------------+------+ | 1.234 | 1.234 | 1 | | 1.23556 | 1.2355555555555555 | 1 | +---------+--------------------+------+ rows in set (0.00 sec)
-
-
-
字符串
-
类型 大小 用途 CHAR 0-255字节 定长字符串 VARCHAR 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串 TINYTEXT 0-255字节 短文本字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT 0-65 535字节 长文本数据 MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295字节 极大文本数据 -
实例
-
char/varchar实例
mysql> create table t9 (v varchar(4),c char(4)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t9 values ('ab ','ab '); Query OK, 1 row affected (0.00 sec) # 在检索的时候char数据类型会去掉空格 mysql> select * from t9; +------+------+ | v | c | +------+------+ | ab | ab | +------+------+ row in set (0.00 sec) # 来看看对查询结果计算的长度 mysql> select length(v),length(c) from t9; +-----------+-----------+ | length(v) | length(c) | +-----------+-----------+ | 4 | 2 | +-----------+-----------+ row in set (0.00 sec) # 给结果拼上一个加号会更清楚 mysql> select concat(v,'+'),concat(c,'+') from t9; +---------------+---------------+ | concat(v,'+') | concat(c,'+') | +---------------+---------------+ | ab + | ab+ | +---------------+---------------+ row in set (0.00 sec) # 当存储的长度超出定义的长度，会截断 mysql> insert into t9 values ('abcd ','abcd '); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t9; +------+------+ | v | c | +------+------+ | ab | ab | | abcd | abcd | +------+------+ rows in set (0.00 sec) char/varchar示例
-
-
-
时间日期
-
类型 大小 (字节) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 年月日 TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时分秒 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 年月日时分秒 TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳 -
date/time/datetime实例
mysql> create table t4 (d date,t time,dt datetime); Query OK, 0 rows affected (0.02 sec) mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into t4 values (now(),now(),now()); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | +------------+----------+---------------------+ 1 row in set (0.00 sec) mysql> insert into t4 values (null,null,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | | NULL | NULL | NULL | +------------+----------+---------------------+ 2 rows in set (0.00 sec) -
timestamp实例
mysql> create table t5 (id1 timestamp); Query OK, 0 rows affected (0.02 sec) mysql> desc t5; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.00 sec) # 插入数据null mysql> insert into t5 values (null); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; +---------------------+ | id1 | +---------------------+ | NULL | +---------------------+ 1 row in set (0.00 sec) #添加一列 默认值是'0000-00-00 00:00:00' mysql> alter table t5 add id2 timestamp; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t5 \G; *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified # 手动修改新的列默认值为当前时间 mysql> alter table t5 modify id2 timestamp default current_timestamp; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t5 \G; *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> insert into t5 values (null,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +---------------------+---------------------+ | id1 | id2 | +---------------------+---------------------+ | 2018-09-21 14:56:50 | 0000-00-00 00:00:00 | | 2018-09-21 14:59:31 | 2018-09-21 14:59:31 | +---------------------+---------------------+ 2 rows in set (0.00 sec) mysql> create table t6 (t1 timestamp); Query OK, 0 rows affected (0.02 sec) mysql> desc t6; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.01 sec) mysql> insert into t6 values (19700101080001); Query OK, 1 row affected (0.00 sec) mysql> select * from t6; +---------------------+ | t1 | +---------------------+ | 1970-01-01 08:00:01 | +---------------------+ 1 row in set (0.00 sec) # timestamp时间的下限是19700101080001 mysql> insert into t6 values (19700101080000); ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1 mysql> insert into t6 values ('2038-01-19 11:14:07'); Query OK, 1 row affected (0.00 sec) # timestamp时间的上限是2038-01-19 11:14:07 mysql> insert into t6 values ('2038-01-19 11:14:08'); ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1 mysql> -
year实例
mysql> create table t7 (y year); Query OK, 0 rows affected (0.02 sec) mysql> insert into t7 values (2018); Query OK, 1 row affected (0.00 sec) mysql> select * from t7; +------+ | y | +------+ | 2018 | +------+ 1 row in set (0.00 sec) -
datetime实例
mysql> create table t8 (dt datetime); Query OK, 0 rows affected (0.01 sec) mysql> insert into t8 values ('2018-9-26 12:20:10'); Query OK, 1 row affected (0.01 sec) mysql> insert into t8 values ('2018/9/26 12+20+10'); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values ('20180926122010'); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values (20180926122010); Query OK, 1 row affected (0.00 sec) mysql> select * from t8; +---------------------+ | dt | +---------------------+ | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | +---------------------+ 4 rows in set (0.00 sec)
-
-
enum/set
-
enum 单选
-
set 多选
-
实例
mysql> create table t10 (name char(20),gender enum('female','male')); Query OK, 0 rows affected (0.01 sec) # 选择enum('female','male')中的一项作为gender的值，可以正常插入 mysql> insert into t10 values ('nezha','male'); Query OK, 1 row affected (0.00 sec) # 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值 mysql> insert into t10 values ('nezha','male,female'); ERROR 1265 (01000): Data truncated for column 'gender' at row 1 mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车')); Query OK, 0 rows affected (0.01 sec) # 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能 mysql> insert into t11 values ('yuan','烫头,喝酒,烫头'); Query OK, 1 row affected (0.01 sec) mysql> select * from t11; +------+---------------+ | name | hobby | +------+---------------+ | yuan | 喝酒,烫头 | +------+---------------+ 1 row in set (0.00 sec) # 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项， mysql> insert into t11 values ('alex','烫头,翻车,看妹子'); ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
-
4）表的完整性约束
-
为了约束某一字段
-
分类
-
无符号的——
int unsigned
-
最低0.47元/天 解锁文章
扫一扫
371
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
