mysql的常用操作与特性

1.数据库介绍

连接到mysql服务的指令：mysql -h 主机IP -P 端口 -u 用户名 -p密码

登陆前保证mysql处于服务状态，如果没有写-h，默认是本机，如果没有-p，默认是3306，在实际工作中3306一般会被修改，因为3306容易被攻击

数据库的三层结构

-所谓mysql数据库，就是在主机安装一个数据库系统，就是在主机安装一个数据库管理系统DBMS ，这个管理程序可以管理多个数据库

-一个数据库中可以创建多个表，以保存数据

-数据库管理系统DBMS（mysqld ，在3306端口监听），数据库DB（data目录下的db01、db02….）和表(db01下面的文件)的关系如图所示

数据库的本质仍然是文件，后面会学到一些其它的数据库比如redis，可能会把数据存放到内存中，但是要实现数据的持久化，仍然要将其放入到磁盘中

数据库的表由行列构成，一行称之为一条记录，在java程序中往往使用一个对象来映射

SQL语句分类

-DDL:数据定义语句【create表，库…..】

-DML:数据操作语句【增加insert，修改update，删除delete】

-DQL:数据查询语句【select】

-DCL:数据控制语句【管路数据库:比如用户权限grant revoke】

Java程序操作数据库：JDBC后续内容

2.创建数据库

#使用指令创建数据库,创建一个名称为cs_db01的数据库

CREATE DATABASE db01;

#创建一个使用utf8字符集的cs_db02数据库

CREATE DATABASE cs_db02 CHARACTER SET utf8

#创建一个使用utf8字符集，并带校对规则的cs_03数据库

CREATE DATABASE cs_db03  CHARACTER  SET  utf8  COLLATE  utf8_bin

#校对规则 utf_bin区分大小写  默认的utf8_general_ci 不区分大小写

3.查看、删除数据库

显示数据库语句：

SHOW DATABASES

显示数据库创建语句：

SHOW CREATE DATABASE db_name

数据库删除语句[慎用]：

DROP DATABASE [IF EXISTS] db_name

#查看当前数据库服务器中所有的数据库

SHOW DATABASE

#查看前面创建的cs_db01数据库的定义信息[在创建数据库表的时候，为了规避关键字，可以使用反引号解决]

SHOW CREATE DATABASE ‘cs_db01’

#删除当前创建的cs_db01数据库

DROP DATABASE cs_db01

4.备份恢复数据库

备份数据库的原理就是将数据库备份到文件系统中，如果数据库被黑客攻击，就可以从文件系统中恢复

备份数据库（注意：在DOS执行）命令行

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql

恢复数据库（注意：进入Mysql命令行执行）

sourse 文件名.sql

#备份，直接打开dos下执行mysqldump指令其实在mysql安装目录\bin

#这个备份文件就是对应的sql语句

mysqldump -u root -p -B cs_db01 cs_db02 > d:\\mysql\\back.sql

#恢复数据库（进入mysql命令行执行）

source d:\\mysql\\back.sql

ps：也可以直接将back.sql里面所有的内容复制到SQLyog查询编辑器里面执行，也可以恢复数据库（文件太大不建议这么做）

如果觉得数据库太大，不想备份整张数据库，可以只备份其中的几张表：

mysqldump -u 用户名 -p 密码 数据库 表1 表2 表n > d:\\文件名.sql

二、表

1.创建

CREATE TABLE table_name

(

        field1 datatype,

        field2 datatype,

        field3 datatype

)character set 字符集 collate  校对规则  engine 引擎

说明：

-field:指定列名

-datatype:指定列类型（字段类型）

-character set: 字符集设置，如不指定则为所在数据库的字符集

-collate:校对规则设置，如果不指定则为所在数据库的校对规则

-engine:存储引擎（涉及内容多，后面有单独板块讲）

可以在SQLyog里面进行图形化的创建

但更多的还是使用上面的指令sql语句进行创建：

2.删除

删除数据库的语句很简单，但一般不用

3.修改

可以直接通过SQLyog的图形化操作界面来修改表的各种内容，比如名称，引擎，字符集等等

通过SQL语句修改：

alter的意思就是修改

这里 NOT NULL DEFAULT ‘ ’代表元素不为空，默认为‘ ’

三、Mysql数据类型

Mysql的数据类型就是针对每列的数据类型-列类型

Mysql数据类型：

-数值类型

  -整形：tinyint[1个字节] smallint[2个字节] mediumint[3个字节]  int[4个]  bigint[8个]

  -小数类型：float[单精度 4个] double[双精度 8个] decimal[M,D][大小不确定]

-文本类型（字符串类型）

  -char 0-255

  -varchar 0-65535

  -text 0-65535(0-2^16-1)

  -longtext 0-2^32-1

二进制数据类型

  -blob[0-2^16-1]

  -longblob[0-2^32-1]

日期类型

  -year【年】

  -data【日期年月日】

  -time【时分秒】

  -datatime【存放年月日时分秒 YYYY-MM-DD HH:mm:ss】

  -timestamp[时间戳，自动更新按当前insert、select操作的时间]

Mysql数据类型看起来多，实际开发中用的没那么多，常用的就标红的这几种

整形的使用：

在实际开发中，讲究需求适当原则，如果整形只存放1-100就只需要int，用bigint就会影响查询效率，就比如age这种就用smallint就行

bit位类型的使用：

这里表中加入长度为8的bit，加入1显示‘1’，加入3显示‘11’

小数的使用：

-FLOAT/DOUBLE 单精度/双精度

-DECIMAL[M,D]:可以支持更加精确的小数位，M是小数位数的总数，D是小数点后面的位数，如果D是0则默认没有小数部分，M最大为65，D最大30，M被省略默认是10

字符串的基本使用

CHAR(size) 固定长度字符串 最大255字符，一个字符可以存放一个字母或者一个汉字

VARCHAR（size）0-65535

可变长度字符串最大65532字节，因为utf8编码最大21844字符，varchar的1-3个字节用于记录大小，如果是GBK编码则最多存32766字符，因为GBK是两个字节存一个字符

使用细节：

-char(4) varchar(4)都代表最多存放4个字符，对于char和varchar一个汉字或字母都是一个字符。

-char(4)是固定的大小，如果插入aa有两个空间没有使用，char的长度仍然是4， varchar(4)是可变长度大小，如果有空间没有使用，它的长度是实际存放数据长度，不会造成内存的浪费，所以varchar会有1-3个字节的预留空间记录长度

-但实际上有些场景还是会用char，比如存储固定长度的身份证号，邮编、手机号之类的，这样用varchar的查询速度比varchar快

-如果varchar不够用，可以考虑使用mediumtext或者longtext

-说明一点：mysql里面的字符串和java不同，是由单引号‘’圈起来

日期类型的使用：

黄字代表时间戳的配置，如果没有人为加时间进去，它就会自己根据当前时间进行加入

四、CRUD（create read update delete）

1.Insert（添加数据）

例子：

insert语句的注意事项：

-插入数据类型应与字段数据类型相同

-数据的长度应在列的规定范围内，比如：不能将一个长度为80的字符串加入到长度为40的列中

-在values中列出的数据位置必须与被加入的列的排列位置向对应

-字符和日期型数据应包含在单引号之中

-列可以插入空值[前提是该字段允许添加空值]，insert into table value(null)

-insert into tab_name values ()，()，()形式添加多条记录

-如果是给表中的所有字段添加数据，可以不写前面的字段名称

-默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

2.Update（更新数据）

如果不加where条件，会修改所有的记录，因此要小心

如果要修改多个字段就可以 SET 字段1 = 值1 , 字段2 = 值2……

3.Delete（修改数据）

使用起来与之前的UPDATE 差不多，如果不加where，会将表内所有数据删除

drop 表名会将表也删除

4.Select（查询数据）（单表、多表）

单表：

运算

在where过滤运算符子句中常用的运算符

这里使用了一个模糊查询的功能，LIKE‘韩%’表示把名字以韩开头的所有人拿出来

使用order by 子句排序查询的结果

asc是英文升序的缩写，desc是降序

where子句的加强：在mysql中 日期类型可以直接比较：查找1992.1.1后入职的员工：

要注意格式

模糊查询（like）:

%表示0到多个字符，_表示单个任意字符

查询首个字母为S的所有员工：

查找第三个字符为大写o的所有员工的姓名和工资

显示没有上级雇员的情况，这里不能用=，而是用is

查询表结构：DESC        

使用order by子句 按照工资从低到高显示雇员信息：

按照先雇员号升序而后薪资降序排列显示信息：

分页查询：

假设表有十万条数据，要求查询其中一项，这里就要用到分页查询

按雇员的id号升序取出，每页显示3条记录，分别显示第一页第二页和第三页

基本语法是：select …… limit start,rows //从第几行开始，取多少行

分组函数与子句的加强

显示每种岗位的雇员总数和平均工资：

表查询-加强：

统计每个部门的平均工资，并且只统计大于1000的，按从高到低排序

五、函数

1.统计函数

Count

COUNT(*) 和COUNT(列名)的区别:后者会排除null的情况

下面这种写法会排除null的行数，所以返回3

Sum

注意：sum仅对数值有用，对其它数据类型无意义会报错，对于多列求和逗号不能少

合计函数AVG 求平均值

合计函数-MAX/MIN

如果这里要查询最高分是谁，则要用到子查询，会比较复杂

分组统计语句 group by 和 having过滤查询

演示案例先建一个表

添加数据:

再建一个工资级别表

2.时间日期

时间日期常用相关函数：

例子：

在实际开发中，经常使用int保存unix的时间戳，然后使用from_unixtime()进行转换

3.字符串函数

字符串常用相关函数：

红色代表最常用

演示：

以首字母小写的方式显示所有员工emp表的姓名：

4.数学函数

数学相关的常用函数：

举例说明：

5.流程控制

常用流程控制语句：

例子：

6.加密和系统函数

常用加密和系统函数：

例子：

六、多表查询

内连接：内多表查询

问题引出：显示

雇员名，雇员工资以及所在 部门的名字（另一张表），并将它们按降序排列

如果不加任何条件直接查两张表，它的规则是：

1.从第一张表中取出一行，与第二张表的每一行进行组合，返回结果含有两张表的所有列

2.一共返回的记录数第一张表数*第二张表数

3.这样的默认多表查询结果，称为笛卡尔集

4．解决多表的关键就是写出正确的过滤条件where，需要程序员进行分析

自连接：将同一张表当作两张表用，需要给同一张表取别名，列名不明确，可以指定列的别名

子查询：子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询

-单行子查询：指返回一行数据的子查询语句

-多行子查询：指返回多行属性据的子查询 使用关键字in

表子查询：子查询当作临时表使用，这种查询可以解决很多场景

在多行子查询中使用all操作符：

案例：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

这里把ALL改成ANY就可以查到比30号部门其中一个高的所有员工

多列子查询：指查询返回多个列数据的子查询语句

基本语法：（字段1 ， 字段2） = （select 字段1 ， 字段2 from…）

案例：查询与smith的部门和岗位完全相同的所有雇员（且不含他本人）

几个练习题：查找每个部门高于自己部门平均工资的人的员工号和工资

查找每个部门工资最高的人的详细资料

表复制

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

面试题：如何去掉一张表里的重复的记录

先构建一张具有重复记录的表：

去重思路：

-先创建一张临时表，利用like使该表结构与需要去重的表一样

-把原表的记录通过distinct关键字处理后复制到临时表

-清除掉原表的记录

-把临时表的记录复制到原表

-利用drop去掉临时表

合并查询 – 合并两条查询语句：

union all就是将两个表的查询结果合并，不会去重

只用union的话就会去重

外连接

-左外连接（左侧的表完全显示）

基本语法：select….from 表1 left join 表2  on  [表1是左表]

-右外连接（右侧的表完全显示）

在实际开发中，绝大多数情况下使用的是内连接

八、约束

约束用于确保数据库的数据满足特定的商业规则

1.not null

如果在列上定义了not null，那么在插入数据事，必须为列提供数据

之前有演示

2.主键primary key

主键使用的许多细节：

-组件不能重复，也不能为null

-一张表中只能有一个主键，但可以是复合主键（将两个或多个元素相加视为主键）

-主键的指定方式有两种：直接在字段名后指定：字段名 primary key 或者 在表定义最后写primary key（列名）

-使用desc表名，可以看到主键的情况。

-在实际开发中，每一个表往往都会有一个主键，用来唯一标识

3.unique（唯一）

当定义了唯一约束后，该列值是不能重复的

注意：

-如果列没有指定not null，那么unique字段可以有多个null

-一张表可以有多个unique字段

4.foreign key（外键）

外键用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或者unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

这里class_id 与 id形成了外键约束，班级表为主表，学生表为外键，如果直接加一个300的class_id是加不进去的，应该先加id 300，同样在班级表里面直接删除id 200也是无法删除的，应该先删class_id 200

FOREIGN KEY(本表字段名) REFRENCES 主表名（或主键名或unique字段名）

案例：创建上述的学生表和班级表：

外键使用的注意事项：

-外键指向的表的字段，要求是primary key或者是unique

-表的类型是innodb，这样的表才支持外键

-外键字段的类型要和主键字段的类型一致（长度可以不同）

-外键字段的值，必须在主键字段中出现过，或者为null（前提是外键字段允许为null）

-一旦建立主外键关系，数据就不能随意删除了

5.check

用于强制使数据必须满足的条件，假设在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错

Oracle和sql server均支持check，但是mysql5.7目前不支持check，只做语法校验，但不会生效

九、自增长

在某张表中存在一个id列（int），我们希望在添加数据的时候，该表的id从1开始自动增长，就会用到自增长技术

自增长的使用:

字段名 整形 primary key auto_increment

‘

注意：

-自增长一般配合主键或者unique使用

-自增长修饰的字段一般为整数型

-自增长默认从1开始，你也可以通过命令修改alter table 表名 auto_increment = xxx;

十、索引

说起提高数据库的性能，索引是最物美价廉的东西，不用加内存，不用改程序，不用调sql，查询速度就可能提高千百倍

比如，在一个含有8000000条数据的表中查询一条数据，要用4.55s才能查到，这里使用索引，速度就快很多

创建索引后，只对创建了索引的列有效

索引的代价：磁盘的额外占用、对dml（update , delete , insert）语句的效率有影响

1.索引的原理

如果没有索引的话，查询的时候程序会对全表进行扫描，如果查询的id是第一条记录，程序依然会对全表进行扫描，来看看是否有id还是1的数据,查询速度会非常慢

创建了索引的话，表会创建一个索引的二叉树（B树，B+树），查询的时候就利用二叉树来进行查询，效率会高非常多

对dml操作速度有影响的原因是因为修改这个树要进行额外的操作，对索引进行维护，保持其二叉树的性质

但是在实际项目中select比dml操作要多很多很多，所以一般还是需要利用索引对数据库表进行优化

2. 索引类型

主键索引

主键自动为一个索引，叫主索引（类型为Primary key）

唯一索引（unique）

普通索引（index）

全文索引[适用于MySAM]

Mysql自带全文索引，但一般开发不使用，性能太差，开发中考虑使用Solr和ElasticSearch

3.索引的使用

如果某列的值不会重复，优先考虑使用unique索引，否则使用普通索引

添加普通索引也可以这么写:

ALTER TABLE t25 ADD INDEX id_index(id)

添加主键索引：

在创建表变量时，直接在变量后面写PRIMARY KEY 也可以

删除索引：

删除主键索引

修改索引：先删除索引，再添加新的索引

查询索引：

3.创建索引的情况

哪些情况适合创建索引：

-较为频繁的作为拆线呢条件字段时应该创建索引

-唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，比如性别

-更新频繁的字段不适合创建索引

-不会出现在WHERE子句的字段不该创建索引

十一、事务

1.事务的介绍以及操作

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理

事物和锁：当执行事物操作时（dml语句），mysql会在表上加锁，防止其它用户改表的数据，这对用户来讲是非常重要的

Mysql控制台的几个重要操作：transaction就是事务的意思

-start transaction –开始一个事务

-savepoint 保存点名 –设置保存点

-rollback to 保存点名 –回退事务

-rollback –回退全部事务

-commit –提交事务，所有的操作生效，不能回退

回退事务:

保存点（savepoint）保存点是事务中的点，用于取消部分事务，当结束事务时（commit），会自动删除改事务所定义的所有保存点，当执行回退事务时，通过指定保存点可以回退到指定的点

提交事务：

使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话将可以查看到事务变化后的新数据

上述例子一旦直接回退到a点就不能再回到b点

事务细节：

-如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚

-如果开始一个事务，你没有创建保存点，你可以执行rollback，默认回到事务开始的一个状态（这也要开始事务，也就是start transction，如果没有，rollback也没有任何意义）

-你也可以在这个事务中（还没有提交时），创建多个保存点，比如savepoint aaa，执行dml，savepoint bbb；

-你可以在事务没有提交前，选择回退到哪个保存点

-mysql的事务机制需要InnoDB的存储引擎才可以使用，myisam不好使

-开始一个事务 start transaction ，set autocommit = off

2.隔离级别

事务隔离级别的介绍：

-多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性

-如果不考虑隔离性，可能会引发下列问题:脏读、不可重复读、幻读

脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读

不可重复读：同一查询在同一事务中多次进行，由于其它提交事务所做出的修改或删除，每次返回不同的结果集，此时发生不可重复读

幻读：同一查询在同一事务中多次进行，由于其它提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读

查看当前的隔离级别：select @@tx_isolation

默认是可重复度级别

事务的隔离级别：

可串行化与可重复度级别的主要区别就是：可串行化会加锁，在一个事务尚未提交时，会卡在那个地方

具体来说就是：

一个用户对数据库的隔离级别是可重复读的话，数据会自动保存在该用户结束其自己的事务之前，其它用户修改数据库并且事务结束提交数据后，该用户查到的数据仍然是自己事务开始时的样子，不会受到其它用户修改数据的影响，只有自己的事务也提交过后才能看到数据库修改后的样子

  一个用户对数据库的隔离级别是可串行化的话，用户会对数据库进行加锁操作，其它用户在对数据库进行增删改的操作时，该用户无法进行查询，或者说会卡在那个地方，因为锁知道此时有用户在对数据库进行操作，一旦其它用户的事务结束进行提交，该用户就能立即查询到修改的数据，但其他用户一致不提交，可能会出现超时不能查询的问题。

其它隔离操作：

这里的系统隔离级别是所有用户的默认隔离级别

十二、mysql表类型和存储引擎

基本介绍:

-mysql的表类型由存储引擎（storage engines）决定，主要包括MyISAM 、innoDB、 Memory

-Mysql数据表主要支持六种类型：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB

-这六种又分为两类，一类是“事务安全型“，比如InnoDB，它支持事务，其余属于第二类称为非事务安全型，它们都不支持事务

InnoDB：支持事务、行级锁定和外键，但效率不高，并且会占用额外的存储空间

MRG_MYISAM:收集myisam表，集合性质

Memory：存储在内存里面，每一个memory表对应一个磁盘文件，读写速度非常快，默认使用hash索引，一旦服务关闭，表中的数据就会丢失，但表的结构还存在

MYISAM：不支持事务、外键，但批量添加速度非常快，访问速度快，对事务完整性没有要求

-如果应用不需要事务，处理的只是最基本的CRUD操作，那么就用MyISAM

-如果需要支持事务，选择innoDB

-Memory存储引擎就是将数据存储在内存中，由于没有磁盘io的等待，速度极快，但关闭服务器或者数据库内存将丢失，经典应用是用户在线状态

修改存储引擎：

ALTER TABLE `表名` ENGINE = 存储引擎；

十三、视图(View)

视图需求应用实例:数据库中存储了用户很多信息，但用户自己查看的时候，我们选择性的只提供表的一部分信息展示，每个用户权限不一样，看到的内容也不一样。

视图的基本概念：

视图是一个虚拟表，其内容由查询定义，同真是的表一样，驶入包含列，其数据来自对应的真是表（基表）

视图和基表的关系图：

1.视图是根据基本表来创建的，是虚拟的表

2.视图爷有列，数据来自基表

3.提供视图可以修改基表的数据

4.基表的改变，也会影响到视图的数据

视图的基本使用：

-create view 视图名 as select语句

-alter view 视图名 as select语句

-SHOW CREATE VIEW 视图名

-drop view 视图名1，视图名2

视图的创建不会产生新数据，视图创建后磁盘对应视图只有一个视图结构文件.frm

针对视图的数据变化会影响到基表，基表的数据变化也会影响到视图

视图中可以再使用视图，也就是从视图中再做出新的视图，但根本的数据来源还是基表

视图的好处：

-安全：一些数据表有着重要的信息，有些字段是保密的，不能让用户看到，有视图就能让用户只看到他权限所能看到的信息

-性能：关系数据库的数据常常会分表存储，使用外键建立这些表之间的关系，这时，数据库查询通常会用到连接（join）这样做不但麻烦，而且效率也比较低，如果建立一个视图，将相关的表和字段组合再一起，就可以避免使用join查询数据

-灵活：提供系统中有一张旧表，这张表由于涉及的问题，即将被抛弃，然而，很多应用都是基于这张表，这时就可以建立一张视图，视图中的数据直接映射到新建的表，就可以少做很多改动，也达到了升级数据表的目的

一张视图也可以映射多张表：

十四、Mysql管理

原因：当我们做项目开发时，可以根据不同的开发人员，赋予他们相应的mysql操作权限。

Mysql的用户都存储在系统数据库mysql中user表中

其中user表的重要字段说明：

-host：允许登录的位置，localhost表示该用户只允许本机登录，也可以指定ip地址

-user： 用户名；

-authentication_string:密码，是通过mysql的password()函数加密之后的密码

创建用户

create user `用户名` @  `允许登录的位置`  identified by  `密码`

创建用户同时指定密码

删除用户：

drop user `用户名`  @  `允许登录的位置` 

Mysql权限管理细节：

给用户授权：