Mysql使用时的注意事项

每一条数据库操作语句都应该以分号；结尾，因为mysql支持换行操作
mysql数据库对大小写不敏感，大小写皆可，通常关键字使用大写表示
mysql数据库中库的名称应该以英文字符或者一些符号起始，但是不允许以数字起始
mysql数据库中库的名称、表的名称、字段的名称都不能使用mysql关键字，比如create、database；如果非要使用，那就要用反引号括起来

基础常用命令

本地登录：

1 2	mysql -uroot -p 密码; --也可以不带密码，之后输入本地登录

远程登录：

1	mysql -h 登录ip -p 端口(通常3306） -uroot -p密码;

查看表的各个字段的属性，以及自增键

desc 表名;

导出数据库文件，保存

1	mysqldump -u用户 -p 数据库名 > xx.sql;

导入数据库文件

1 2	mysql -u用户 -p密码数据库名< xx.sql; --也可以选择登录进去，在选择数据库后，使用source命令导入数据

创建用户

ip是指用户登录mysql的电脑ip，可以写%，本地写localhost

1	create user '用户名' @'ip' identified by '密码';

用户授权

1	grant 权限(select/insert/updata/all priveleges) on 表/数据库名 to '用户'@'ip' identified by '密码';

删除用户

1	drop user 用户名@ip ;

库的操作

查看mysql中所有的库：

1	show databases;

创建库：

1
2
3

create database 库名称 default charset utf8;
create database if not exists 库名称;
## 如果不存在该库，则创建

删除库：

1	drop database 库名称;

选择使用数据库

use 库名称;

显示当前使用的数据库：

1	select database();

数据类型

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),
以及近似值数据类型（FLOAT、REAL和DOUBLE PRECISION）。

#关键字 INT 是 INTEGER 的同义词，关键字 DEC 是 DECIMAL 的同义词。
BIT 数据类型保存位字段值，并且支持MylSAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，Mysql也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。
下面的表显示了需要的每个整数类型的存储和范围。

日期和时间类型

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME和YEAR

每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”MySQL支持所有标准SQL数值数据类型。

字符串类型

字符串类型值 CHAR、VARCHAR、BINARY、VARBINARY、BLOG、TEXT、ENUM和SET

注意：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

表的操作

显示库中所有的表：

1	show tables;

创建表：

create table 表名(列名 数据类型 约束···，列名 数据类型 约束···）engine=innodb default charset=utf8   创建表

create table 表名(列名 数据类型 primary key auto_increment,列名 数据类型 约束···）engine=innodb default charset=utf8 创建含主键的表

create table 表名(列名 数据类型 primary key,列名 数据类型 constraint fk_外键名 foreign key (列名[,]) references 表名2（列名[,]）···）engine=innodb default charset=utf8  创建含外键的表

create table if not exists tb_stu(id int,name varchar(3),age int, birth datetime);

查看表结构：desc tb_stu

删除表：

1	drop table tb_stu;

表中数据的增删改查基础

插入数据 insert

指定列插入：可以指定单独个一个或几个列信息进程插入，并且前缀列信息

1	insert into tb_stu(id,name) values (1,"韩云溪");

全列插入：可以省略前缀的列信息，按照列顺序插入所有列的数据

insert into tb_stu values (1,"韩云溪",21,"2000-03-19 12:00:00");
insert into tb_stu values (2,"夏紫藤",21,"2000-05-18 11:00:00");
insert into tb_stu values (3,"三三",17,"2021-06-26 11:43:01");
insert into tb_stu values (4,"四四",18,"2021-06-26 11:43:01");

多行插入：
insert into tb_name values(val1,val2…),(val1,val2…),…;
insert into tb_stu values (3,”三三”,17,now()),(4,”四四”,18,now());

查询数据 select

查询指定表中所有数据：

1	select * from tb_stu;

指定列查询：

1	select name,birth from tb_stu;

排序查询：按照年龄排序查询,默认为asc升序查询，desc为降序查询

1	select * from tb_stu order by age [asc]/desc;

多列排序：在第一列相同的情况下针对第二列进行排序：

1	select * from tb_stu order by age ,id desc;

分页查询：通常搭配排序一起使用

1 2	select * from tb_stu limit m offset n; --分页查询，每页显示m条数据，偏移到第n条开始显示m条数据（即从第n条起，显示一页）

查询字段为表达式：

1 2	select name,id+age from tb_stu; --按照name和id+age的值分列查询

查询时，字段别名的使用：

1 2	select name,id+age [as] total from tb_stu; --给id+age起了个别名叫total，as可以省略

去重：

1 2	select distinct age from tb_stu ; --根据age查询，并对age去重

条件查询 where

按照一定的限制条件进行查询，限制条件通过where子句给出

例如：按照id>1的限制条件进行查询：

1	select * from tb_stu where id>1;

where子句涉及的运算符：

另外还有：> = <（大于、等于、小于）可以和NULL进行比较
is NULL/is not NULL 可以用来判断是否为空

IN的使用：

判断查询的数据是否是给予的多个选项之一

例如：

1 2	select * from tb_stu where name in("韩云溪","夏紫藤"); --查询名字为韩云溪或夏紫藤的项，符合则显示，不符合则忽略

between A and B 的使用：查询范围介于AB两者之间项

1 2	select * from tb_stu where id between 1 and 3; --查询id介于1和3之间的项

LIKE 模糊匹配的使用：查询一个数据看起来像某个条件

1 2	select * from tb_stu where name like '韩%'; --查询表中姓名以韩开头的项，%为通配符

逻辑运算符的使用：与and、或or、非not

1 2	select * from tb_stu where age>17 and age<22; --查询表中年龄大于17并且小于22的项

连表操作

select * from 表1 left join 表2 on 表1.列名=表2.列名;  左连接

select * from 表1 right join 表2 on 表1.列名=表2.列名;  右连接

select * from 表1 inner join 表2 on 表1.列名=表2.列名;  内连接

注意:如果超过3个表联合操作，如果其中两个表操作时已经改变了表结构，应该将这两个表操作的结果作为一个临时表再与第三个表联合操作。

临时表

1	(select * from 表名)as e

修改数据：update

1 2	update tb_name set fields1=val1,fields2=val2 where condition; --尤其注意不要忘了where限制条件，否则整张表的该列数据都会更改

删除数据：Delete和Truncate

delete from tb_name where condition;
--尤其注意不要忘了where限制条件，否则整张表的数据都会被删除
--清除表(如果有自增id，id 不会重新开始)
truncate table 表名;
--清除表(如果有自增id，id 会重新开始)

修改表结构

alter table 表名 auto_increment=value; 设置自增键起始值；

alter table 表名 drop 列名; 删除列

alter table 表名 add 列名 数据类型 约束; 增加列

alter table 表名 change 旧列名 新列名 数据类型; 修改字段类型

alter table 表名 modify 列名 数据类型; 修改数据类型

alter table 旧表名 rename 新表名; 修改表名

alter table 表名 drop primary key; 删除表中主键

alter table 表名 add 列名 数据类型 primary key;添加主键

alter table 表名 add primary key(列名); 设置主键

alter table 表名 add column 列名 数据类型 after 列名; 在某一列后添加主键

mysql进阶

外键

外键适用于一对多，一对一，多对多三种情况

一对多

典型案例员工与部门，一个部门对应于多个员工，一个员工对应于一个部分，所以要在员工表中设置部门id列，并设置为外键，与部门表id关联。

一对一

案例博客园用户与博客，不是每个用户都写博客，写博客的用户与拥有的博客地址一一对应，所以在博客用户表user中设置blog_id，设置成外键和唯一索引，与博客表blog中id关联

create table user(id int not null auto_increment primary key,name char(10), blog_id int,unique uq1(blog_id),constraint fk_user_blog foreign key(blog_id) references blog(id) ) engine=innodb default charset=utf8;

多对多

典型案例电脑与用户，一个用户可以使用多台电脑，一台电脑对应多个用户，多对多，此时一般选择新建一个表contact，在其中设置两个外键，同时关联用户表id与电脑表id

create table contact( id int not null auto_increment primary key,user_id int,computer_id int,unique uq2(user_id,computer_id),constraint fk_user foreign key(user_id) references user(id),constraint fk_user foreign key(computer_id) references computer(id) ) engine=innodb default charset=utf8;

可以看情况决定要不要把两个外键弄成联合唯一索引。

自增

查看表的创建信息

1	show create table 表名 ;

对于自增键，我们可以设置它的初始值以及与自增步长

1	alter table auto_increment=value; --设置自增初始值

对于自增步长有两种类型：

第一种：

基于会话级别(只对本次登录有效)

show session variables like "auto_incre%";   -- 查看步长

set session auto_increment_increment=value;  --改变步长

set session auto_increment_offest=value;  --设置起始值(不常用，因为有之前的alter)

第二种：

基于全局级别(对所有会话都有用)

1	set global auto_increment_increment=value; --设置全局步长

pymysql

pymysql 是python第三方模块，主要用于python与mysql交互。

pymysql 方法:

connect(host=,user=,passwd=,database=) 连接数据库

cursor() 建立游标

execute(sql)执行sql语句

fetchone()从查询结果中取出一条数据

fetchmany(num)从查询结果中取出num条数据

fetchall()从查询结果中取出所有数据

commit() 改变(updata，insert，drop)数据库内容专用,execute sql语句后必须commit才能真正改变数据库

close()注意最后有两个close,游标要关闭，连接也要关闭

sql注入

sql注入就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

实例如下：

假设我们输入用户名和密码，然后登录数据库，在数据库中查找用户和密码是否存在，存在即登录成功，否则登录失败

程序如上，一开始我们输入数据库存在的用户和密码，自然能够登录成功

输入错误用户和密码，也能成功

追究其原有，原因出在sql语句字符串拼接上

1	拼接的字符串为 '' or 1=1 -- '' and passwd='123',有1=1，当然成立

解决办法：

将要拼接的内容放入execute括号中，由其自己拼接

视图

视图是一种虚拟存在的表，本身不包含数据，作为一个select语句保存在数据库中。通俗的讲视图代表的是一条select语句产生的结果集。

1	create view 视图名称 as select语句; --创建视图

视图单独不能插入，修改数据，因为视图是虚拟的。
但可以修改select语句，改变视图结果集。

1 2	alter view 视图名称 as sql语句; --修改结果集 drop view 视图名称; --删除视图

索引

定义:索引（Index）是帮助MYSQL高效获取数据的数据结构

索引的作用:

约束，加速查找

索引种类

普通索引: 加速查找
主键索引: 加速查找；约束(主键不能为空，不能重复)
唯一索引: 加速查找；约束(索引不能重复）
组合索引: 多列组成一个索引(联合主键索引，联合唯一索引，联合普通索引)

create index 索引名 on 表名(列名[,列名]);创建[联合]普通索引

create table 表名(列名 数据类型···index 索引名(列名[,列名])); 创建表时创建[联合]普通索引

create unique index 索引名 on 表名(列名);创建唯一索引

create table 表名(列名 数据类型···unique 索引名(列名)); 创建表时创建唯一索引

create unique index 索引名 on 表名(列名,列名);创建联合唯一索引

create table 表名(列名 数据类型···unique 索引名(列名,列名)); 创建表时创建联合唯一索引

drop index 索引名 on 表名; 删除索引

索引结构

索引一般有两种结构:哈希索引和BTree索引

哈希索引

哈希索引会产生一张索引表，把数据通过算法换算成哈希值，索引表存储这些哈希值，并在表中保存指向数据的指针，值得注意的是索引表存储哈希值时打乱了原有的存储顺序。哈希索引查找一条数据时特别快速，优于BTree索引，但因为打乱了原有的数据顺序，不支持范围查找与排序功能。

BTree索引

MYSQL普遍采用B+Tree结构

B+Tree所有索引数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针。BTree索引查找单条数据的速度不如哈希索引，但是更加适用于范围查找与排序，所以用的最为广泛，引擎innodb与MyIsam都使用了BTree索引。

索引是不是越多越好？

不是的，索引越多，占据的物理空间越大；索引只是加快了查询速度，而减缓了插入和修改速度。

事务

定义:在MYSQL中，事务其实是一个最小的不可分割的工作单元，事务能保证一个业务的完整性。

场景:在开发中，有多条语句可能会有同时成功的要求，如果只有一部分成功，则全部失败，回滚到原来的状态。

mysql中如何控制事务

通过

select @@autocommit; 
--语句查询自动提交是否开启，1表示开启，0表示没有开启
--当查询结果为1时，自动提交开启，执行sql语句(insert into ，alter),
--系统默认提交确认，在物理表生效
--当查询结果为0时，自动提交关闭，执行sql语句，
--系统会等待用户手动提交确认(commit;)
--否则可以撤回修改,回滚为原状态(rollback;）

开启事务

一般@@autocommit我们默认开启，否则每次都需手动提交太麻烦，但当我们想开启一个事务时应该怎么做？

我们可以通过 begin或start transaction 开启一个事务，最后以commit;或rollback;结束一个事务。

事务的四大特性(ACID)

A 原子性:事务是最小的不可分割的工作单元
C 一致性:在一个事务中，事务前后数据的完整性必须保持一致，sql语句要么全部成功，要么失败，回滚到原状态。如银行转账，总的钱数不变
I 隔离性:不同事务之间具有隔离性
D 持久性:事务一旦结束，就不可以返回，不可以rollback

mysql事务四大隔离级别

读未提交（read uncommitted）

不可重复读（read committed）

可重复读（repeatable read）

串行化（serializable）

mysql事务隔离级别默认是可重复读(repeatable-read）

1 2	select @@ global.tx_isolation; --mysql(5.x版本)查看隔离级别 set [global/session] transaction isolation level 隔离级别; --设置隔离级别

四种隔离级别导致的问题：

脏读：事务1读到了事务2未提交的数据，如果事务2rollback，这些数据便是脏数据

不可重复读：事务1对同一数据重复读，但是另一个事务不断修改这些数据，造成事务1每次读到的数据不一样

幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户在新开启的事务中发现表中还有没有修改的数据行，就好象发生了幻觉一样(用户1开启事务查看数据，然后同时有用户2开启事务对数据修改并提交，用户1在当前事务没有发现数据改变，结束事务，然后在新开启的事务中发现了新修改的数据，这像幻觉）

注意:不可重复读侧重修改，幻读侧重插入和删除

幻读试验：

设置隔离级别

开启一个事务，查询数据

重新打开一个窗口登录，开启事务插入数据，并commit

先前的事务查询数据，看不到新增语句，解决了不可重复读问题

关闭先前事务，重新查询，查到新增数据，出现幻读

mysql重要知识点

innodb与MyIsam的区别

InnoDB支持事务，而MyISAM不支持事务;
InnoDB支持行级锁，而MyISAM支持表级锁;
InnoDB支持MVCC(多版本并发控制), 而MyISAM不支持;
InnoDB支持外键，而MyISAM不支持;
InnoDB不支持全文索引，而MyISAM支持;

内连接，左连接，右连接的区别

内连接：返回两表指定列名相同的数据，如果没有返回空。

1	select * from 表1 inner join 表2 on 表1.列名=表2.列名; --内连接

左连接:左边数据全显示，右边表只显示符合条件的数据，
如果右边没有相符数据或满足的数据少于左边数据行，以null代替(记录不足的地方以null代替)

1	select * from 表1 left join 表2 on 表1.列名=表2.列名; --左连接

右连接:与左连接相反

1	select * from 表1 right join 表2 on 表1.列名=表2.列名; --右连接

drop,delete与truncate的区别

drop 直接删掉表

truncate 删除表中数据，再插入时自增长id又从1开始

delete 删除表中数据，可以加where字句,delete删除整个表数据时，再插入自增id不会从1开始

为什么用BTree做索引结构

哈希:虽然单词查询快，但是没有顺序，不适合范围查询
二叉树：树的高度不均匀，不能自平衡，查找效率与树的高度有关，IO代价高
红黑树：树的高度随数据量而变，IO代价高(数据量过大时，树的深度越高，IO读写越频繁)
Btree:数据很大时，不可能放在内存，所以放在磁盘上，BTree 每层节点数多，层数少，减少了IO读写次数，查询结果更加稳定

主键外键

主键:数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个表只能有一个主键，且主键的取值不能缺失，即不能为空值(Null)。

外键:在一个表中存在的另一个表的主键称此表的外键。

varchar与char区别

char是一种固定长度的类型，varchar则是一种可变长度的类型
char 长度是固定的，不管你存储的数据是多少他都会都固定的长度。而varchar则处可变长度
varchar(50)中50表示最多有50个字符
varchar节省空间，查询速度没有char速度快

行级锁和表级锁

表级: 直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
行级: 仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。