MySql基础篇——基本命令

二、基本介绍

1、名词介绍

数据库
DataBase,简称DB。按照一定格式存储数据的一些文件组合。通俗一点是存储数据的仓库。
数据库管理系统
DataBaseManagement，简称DBMS。是专门用来管理数据库的一套系统，可以控制、操作和管理多个数据库。
常见的数据库管理系统有：MySql，Oracle，SqlServer，DB2
sql结构化查询语言
用来操作数据库中数据的一套标准语言

2、SQL语句的分类

1. DQL : 数据查询语言，凡是带有select关键字的都是DQL。
2. DML : 数据操作语言，凡是对表中的数据进行增删改的都是DML。
3. DDL : 数据定义语言，主要操作的是表的结构。
4. DCL : 数据控制语言，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。授权grant，撤销revoke。
5. TCL : 事务控制语言，事务的提交回滚。

3、常用命令

1. show databases; 查看mysql中有哪些数据库，mysql默认有4个数据库。
   

2. show tables 查看某个数据库下有哪些表

3. create database 数据库名 创建数据库

4. mysql -u用户名 -p密码 -h主机 -P端口号 登录mysql服务

5. use 数据库名 选择使用哪个数据库

6. source 路径/xxx.sql 在当前数据库中导入表

7. desc 表名 查看表的结构

8. select version() 查看当前mysql的版本号

9. select database() 查看当前使用哪个数据库

10. 输入\c终止一条命令输入

4、补充说明

1. 在所有的数据库中，字符串统一使用单引号括起来，单引号是标准。双引号可以在mysql中使用，在oracle数据库中用不了。
2. 别名是中文、有空格必须用单引号括起来
3. 如果+号两边是数值型，不管有没有引号都做加法运算。+号两边有字符型，将字符型转换为数值0进行运算。null不管参与什么运算结果都是null。

---

三、DQL

1.简单查询

1. 基本语法:
   select 字段名 from 表名;
2. as关键字可以给查询的列起别名，将显示的查询结果列名显示为别名。as可以省略为空格。
3. 字段名可以使用数学表达式：
   select 字段名*5 from 表名;

2.条件查询

where关键字后面可以添加一下查询条件：

= 等于
<> 或 != 不等于
< 小于
> 大于
between … and … 在两个值之间，等同于 >= and <=
is null 为null（is not null 不为空）
and 并且
or 或者
in 包括，相当于多个or（not in 不在这个范围中）
not 可以取非，主要用在is或in中
like 模糊查询，%代表任意多个字符，_表示任意一个字符

3.排序

order by [字段]
按照[字段]排序，默认升序。

order by [字段] desc
按照[字段]排序，指定降序

order by [字段] asc
按照[字段]排序，指定升序

order by [字段1] asc，[字段2] asc
先按照[字段1]升序排序，[字段1]相同按照字段2升序排序

order by 2
按照查询结果的第二列排序

4.单行处理函数

一行一行处理的，处理完后总行数不变。

1. lower 转换小写
2. upper 转换大写
3. substr(被截取字符串，起始下标，截取长度) 取子串，起始下标从1开始
4. length 取长度
5. trim 去空格
6. str_to_date 将字符串转换为日期
7. date_format 将date类型转换成具有一定格式的varchar字符串类型
8. round(数字，保留几位小数(0保留整数)) 四舍五入
9. rand 生成随机数
10. ifnull 可以将null转换为一个具体的值
11. concat(string1,string2) 字符串拼接
12. case …when…then…when…then…else…end
13. distinct 字段名，distinct 只能在所有字段的最前面，对后面的字段联合起来去重。

5.多行处理函数（分组函数）

输入多行，最终输出一行。分组函数在使用的时候必须先进行分组，然后才能使用。如果没有分组，整张表默认为一组。
分组函数自动忽略null。
分组函数不能直接使用在where子句中。

1. count 计数
   count(*)，统计表中的总行数，因为不存在每个字段都为null的数据。
   count(具体某个字段)，统计该字段下所有不为null的元素总数。
2. sum 求和
3. avg 平均值
4. max 最大值
5. min 最小值

6.分组查询

1. 在一条select 语句当中，如果有group by语句的话，select后面只能跟分组字段或者分组函数。

2. select ... from ... where ... group by ... order by [字段1]，[字段2]
   按照两个字段分组

3. 分组完后可以使用having进行条件判断
   select ... from ... where ... group by ... having ... *

6.单表查询总结

1.执行顺序：
from -> where -> group by -> having -> select -> order by -> limit

7.连接查询

1. 分类

根据语法的年代分为SQL92和SQL99，以下重点介绍SQL99。
更加表连接的方式分为，内连接（等值连接，非等值连接，自连接），外连接（左外连接，右外连接），全连接。

2. 当两张表连接进行查询时，没有任何条件限制会发生笛卡尔积现象。即一张表中的每一条数据都和另一张表每个字段匹配。
3. 内连接之等值连接
   select * from 表1，表2 where 表1.字段 = 表2.字段 92的语法，where时添加条件。
   select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 99 语法。
4. 内连接之非等值连接就是连接条件不是一个等值关系。
5. 内连接之自连接就是同一张表连接在一起。
6. 内连接是当能够匹配上条件的数据才能查询出来。
7. 右外连接：表1 right outer join 表2 on … 。将join关键字右边的表看成主表，将主表数据都查出来，捎带着关联查询左边的表。
8. 左外连接： 表1 left outer join 表2 on … 。同上。
9. 全连接就是左右两张表都是主表，都查出来。

8.子查询

select 
	...(select)
from 
	...(select)
where 
	...(select)

where后面子查询，可以将子查询出来的结果当判断条件。

from后面的子查询，可以将子查询的查询结果当初一张临时表。

select后面的子查询，要求子查询结果一次只能返回一条记录，多余一条就报错。

9.union合并查询结果集

union可以将两次的查询结果上下拼接在一起。

select ... from ... where ....
union
select ... from ... where ....

union在进行结果集合并时，要求两个结果集的列数相同。并且数据类型也相同。

10.limit

可以将查询结果集的一部分取出来，分页。

limit startIndex,length

1. startIndex为起始下标，length是长度。startIndex缺省时为0，表示获取前length个数据。
2. limit在order by之后执行。
3. startIndex = (pageNo - 1) * pageSize

---

四、DDL

1.创建表

create table 表名(
	字段名1 数据类型 [列级约束]，
	字段名2 数据类型，
	字段名3 数据类型
	表级约束条件，
	表级约束条件
)；

创建表还有一种快速创建表的方式，也叫做表的复制：create table 表名1 as select * from 表名2，将表2的表复制到表1中。
数据库中有一条命名规范：所有标识符全部小写，单词和单词之间使用下划线进行衔接。
表约束有如下：

1. default xxx 当插入数据时，默认没赋值的字段都为null。当设置该选项后，默认为xxx。
2. 非空约束，not null
3. 唯一性约束，unique，但是可以为null，null不算重复。还可以在约束条件的位置加上unique(字段1,字段2)表示字段1,字段2看上去是一个字段不可重复。
4. 主键约束，primary key，主键就是非空并且唯一。如果一个字段被not null和unique同时约束，那么在mysql中会成为主键。
5. 外键约束，foreign key(当前表的外键) references 外键表(外键字段)。外键可以为null，外键字段不一定是主键，至少有unique约束。
6. 检查约束，check,mysql不支持，oracle支持
7. auto_increment，自增

2.删除表

drop table if exists 表名

3.数据类型

1. varchar （最长255）可变长度字符串，会根据传过来的实际数据长度自动分配。char是定长字符串，使用不当会导致空间的浪费，但是不需要动态分配空间，速度快。
2. int （最长11）数字中的整数型，等同于java中的int。
3. bigint 数字中的长整型，等同于java中的long。
4. float 单精度浮点型
5. double 双精度浮点型
6. date 短日期类型
7. datetime 长日期类型
8. clob字符大对象。最多可以存储4G的字符串。
9. blob 二进制大对象，专门用来存储图片，声音，视频等流媒体数据。往blob类型的字段上插入数据时，需要使用io流。

4.日期类型

1. 在插入时如果数据类型时date类型，但是直接插入一个字符串是错误的。需要使用 str_to_date (String格式的日期类型，“String对应的日期格式”)
2. mysql中的日期格式为：%Y年%m月%d日%h时%i分%s秒
3. 如果插入的字符串是%Y-%m-%d这个格式，那么str_to_date函数就不需要了。
4. date_format(日期格式的字符串,"%Y-%m-%d") 将日期转换为字符串。
5. 长日期默认格式为：%Y-%m-%d %h:%i:%s
6. 在mysql中通过now()函数可以获取系统当前时间，是datetime类型的。

---

五、DML

1.插入数据

insert into 表名(字段1，...) values(值1，...),( ... ),( ... )

1. 没有指定值的字段，默认值为null。也可以在创表的时候指定默认值。
2. insert语句中的“字段名”可以省略。代表全部字段，所以values后面赋的值要给每个字段都赋值。
3. insert语句可以一次性插入多条记录
4. 可以使用insert into 表名1 select * from 表名2,将表2查出来的数据插入到表1中。

2.更新数据

update 表名 set 字段名 = " " where 条件 选择性更新

update 表名 set 字段名 = " " 更新所有

3.删除数据

delete from 表名 where ... 删除某几条数据
delete from 表名 删除所有数据
delete删除效率比较低，应为不会释放数据在数据库中实际的位置，可以回滚。

truncate table 表名 删除效率较高，直接物理删除，不支持回滚。这种语句是DDL语句

---

六、存储引擎

1.什么是存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

2. 介绍

1. mysql默认的存储引擎是innodb，默认的编码方式是utf8
2. show engines \G 查看支持的所有存储引擎
3. mysql支持九大存储引擎，根据版本不同支持数有有差异，5.5.3只支持8个。

3. MEMORY存储引擎

其数据存储在内存中，且行的长度固定。

1. 在数据库目录中，每个表均以.frm格式的文件表示
2. 表数据及索引被保存在内存中
3. 表级锁机制
4. 不能包含TEXT或BLOB字段

优点是查询效率是最高的，不需要和硬盘交互。
缺点是不安全，关机之后数据消失。

4. MyISAM存储引擎

（读音：迷桑母）
他使用三个文件表示每个表：
① 格式文件 ： 存储表结构的定义（mytable.frm）
② 数据文件 ：存储表行的内容（mytable.MYD）
③ 索引文件 : 存储表上索引（mytable.MYI）
可被转换为压缩、只读表来节省空间。但是不支持事务，安全性低。

5. InnoDB存储引擎

InnoDB是mysql默认的存储引擎，同时也是一个重量级的存储引擎。

1. InnoDB支持事务
2. 每个表在数据库目录中以.frm格式文件表示
3. InnoDB表空间tablespace被用于存储表的内容
4. 提供一组用于记录事务性活动的日志文件
5. 提供全ACID兼容
6. 在MySQL服务器崩溃后提供自动恢复
7. 多版本（MVC）和行级锁定
8. 支持外键及引用完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务，以保证数据的安全。效率不是很高，并且不能压缩，不能转换为只读，不能很好的节省空间。

七、事务（transaction）

1.介绍

在事务的执行过程中，每一条DML的操作都会被记录到事务性活动的日志文件中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。
commit；提交事务
提交事务标志着事务的结束。并且是一种全部成功的结束。会清空事务性活动的日志文件，将数据全部彻底持久化到数据库中。
rollback；回滚事务
回滚事务会将之前所有DML操作全部撤销。并清空事务性活动的日志文件。

2.事务命令

mysql默认自动提交事务

1. select @@tx_isolation; 或 select @@session.tx_isolation; 查看会话级的当前隔离级别。8.0以后使用select @@transaction_isolation
2. select @@global.tx_isolation; 查看全局的当期隔离级别。
3. set transaction isolation level read committed 或 set session transaction isolation level read committed 设置会话的隔离级别。
4. set global transactionisolation level read committed 设置全局的隔离级别。

3.事务ACID

1. 原子性（A）：说明事务是最小的工作单元，不可再分。
2. 一致性（C）： 所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
3. 隔离性（I） ： A事务和B事务之间具有一定的隔离，隔离性用来处理一个事务对另一个事务之间的干扰程度。
4. 持久性 （D） ：事务只要提交，就保存到硬盘上。

4.隔离级别

1. 读未提交：read uncommitted
   事务A可以读取到事务B未提交的数据。
   但是会存在脏读现象

2. 读已提交 : read committed
   事务A只能读取到事务B提交之后的数据。
   这种隔离级别解决了脏读的现象，但是存在不可重复读。
   这种隔离级别是比较真实的数据，每一次读到的数据都是真实的，oracle数据库默认的隔离级别是读已提交。

3. 可重复读 : repeatable read
   事务A开启后，不管是多久，新插入的行永远不能被A看到。
   解决了不可重复读的问题。存在幻读现象。幻读现象就是一个事务操作完，发现还存在自己没操作过的行。比如对全表更新，结果提交完发现还有一行新增的没有被更新。就像幻觉一样。
   mysql默认的隔离级别是可重复读。

4. 序列化/串行化 : serializable
   事务只能排队进行，不能并发。这是效率最低的，但是能解决所有的问题。

---

5.使用说明

事务只是对自身有效，例如一个会话的隔离级别是序列化，另一个会话的隔离级别是可重复读。第一个会话虽然开启事务，但是另一个会话依旧可以进行事务操作。但是当两个会话都是序列化时，就是单线程执行，即一个会话隔离级别时序列化，则当开启事务时，不能有其他序列化的事务在执行。

八、索引

1.介绍

索引的目的在于提高效率。如果没有索引可能要进行全表的依次扫描。而添加了索引，可以不断的缩小想要获取数据的范围来筛选出最终想要的结果。
虽然索引大大提高了查询效率，但是同时却会降低更新表的速度，如对表进行DML时。因为更新表时，MySQL不仅要保存数据，还要保存索引文件，建立索引会占用磁盘的索引文件。

2.索引原理

1. 在任何数据库中主键上都会自动添加索引对象。mysql数据库中，一个字段上如果有unique约束的话，也会自动创建索引对象。
2. 在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
3. 在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace当中。在MEMORY存储引擎当中索引被存储在内存中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。

3.什么时候使用索引

1. 数据量庞大
2. 该字段经常出现在where的后面
3. 该字段很少的DML操作，应为DML之后，索引需要重写排序
4. 该字段最好没有很多的重复数据

4.索引使用

1. create index 索引名 on 表名(字段名); 创建索引
2. drop index 索引名 on 表名 删除索引
3. explain SQL语句 解释这条SQL语句是否通过索引检索。索引检索type为ref，普通检索type为all

5.索引失效

1. 模糊查询时以%开头
2. 如果使用or，那么要求or两边的条件字段都要有索引，才会走索引。如果其中一边有一个字段没有索引，那么另外一边的索引也会失效。
3. 使用复合索引的时候，没有使用左侧的列查找。只使用右边的索引，索引会失效。
4. 在where当中索引列参加了运算，索引失效。
5. 在where当中索引列使用了函数，索引失效。

---

九、视图（view）

1.介绍

视图是由数据库中的一个表或多个表导出的虚拟表,是一种虚拟存在的表,方便用户对数据的操作。
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。

2.视图操作

1. create view 视图名 as DQL语句 创建视图
2. drop view 视图名 删除视图对象

---

十、数据库设计三范式

第一范式：
要求任何一张表必须有主键。每一个字段原子性不可再分。
第二范式：
建立在第一范式基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
产生部分依赖会导致数据冗余，空间浪费。
第三范式：
建立在第二范式基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

设计表口诀：

1. 多对多，三张表，关系表两个外键。
2. 一对多，两张表，多的表加外键。
3. 一对一，外键唯一。