[大数据][数据库]-MySQL

MySQL

前言

• 数据库：按照一定格式存储数据的文件的集合，本质上就是一堆文件
• 数据库管理系统：DBMS，管理数据库的软件，对数据库中的数据进行操作，增删查改。如MySQL，Oracle，DB2，Sybase等
• SQL：结构化查询语句。由程序员编写sql语句，dbms执行语句，从而实现对数据库数据的操作；SQL语句中可以用 -- 注释内容 或 # 注释内容(这种是MySQL特有的)来实现单行注释，也可以用 /*注释内容*/ 来实现多行注释
• 通过cmd启停MySQL服务：net stop MySQL;net start MySQL(这里MySQL的服务名称为MySQL)
• 通过 cmd登录MySQL：mysql -uroot -p密码，或-p后不输入密码直接回车，下一行会给出输入密码的提示

数据库操作命令

语句结尾要加分号；不区分大小写

• show databases：查看当前所有数据库
• use 数据库名：使用指定数据库
• create database 数据库名：创建数据库
• create database if not exists 数据库名：判断不存在指定数据库时再创建
• create database if not exists 数据库名 character set 字符集名：创建数据库同时指定字符集
• show tables：查看当前所使用的数据库所有的表
• desc 表名：查看数据库中指定表的结构(各字段)
• select version()：查看版本号
• select database()：查看当晚使用的是哪个数据库
• alter database 数据库名 character set 字符集名：修改指定数据库的字符集
• drop database 数据库名：删除指定数据库
• drop database if exists 数据库名：判断指定数据库是否存在，存在则删除
• \c可以用来终止一条命令的输入

SQL分类

• DQL：数据查询语言(select)
• DML：数据操作语言(增删改，insert，delete，update)
• DDL：数据定义语言，操作的是表，如create建表，drop删表，alter改表
• TCL：事务控制语言，commit事务提交，rollback事务回滚
• DCL：数据控制语言，grant授权，revoke撤销权限

cmd中执行.sql脚本文件

在cmd中输入source ，然后将.sql文件拖拽到cmd窗口中，然后点击回车执行即可执行.sql文件中的sql语句

SQL语句学习

单表查询语句

1.简单查询：select 字段1,字段2,字段3,…,字段n from 表名。当把所有字段都写上即查询所有字段时，可以直接用select * from 表名，不过一样会被转化为所有字段的名，所有效率会差。
select后面除了跟字段名还可以跟字面值(如’abc’，1000等)，跟字面值的话查询结果是根据查询的表的结构得到对应个数的字面值，如select 1200 from dept，此时dept表中有四条记录，那么查询结果就会得到四个1200
2.起别名：使用as关键字，select 字段名1 as 别名1 from 表名，查询结果字段的名字就会显示为别名；as可以省略，只用空格分隔字段名跟别名；别名中如果想有空格，可以对整个别名使用单或双引号即可，在所有的数据库中，字符串统一用单引号括起来(双引号在Oracle就不行)
3.字段参与运算：select 字段名 数学运算 from 表名，可以把表中的这个字段做运算后显示出来，数学运算如+2等等；也可以对一条记录中的两个及以上个字段之间进行数学运算
4.条件查询：=等于；<>或!=不等于；<小于；<=小于等于；>大于；>=大于等于；between…and…在两个值中间(between较小数值and较大数值)，相当于>=…and<=…；is null为空(数据库中的NULL表示该处数据真的什么都没有，不能用等号来衡量)；is not null不为空；and且；or或(and优先级比or高)；in包含，相当于多个或；not in不包含；not可以取非；like，模糊查询，支持%或_匹配，一个%代表任意个字符，一个下划线只匹配一个字符(如果要查询含有_的数据，要用转义字符，即’\_’)
5.排序：select … from 表名 order by 排序字段 desc/asc(降序/升序，默认为升序)；有多个排序字段时，字段间用逗号隔开，只有前面的字段数据相等时才会用其后的字段进行排序，即放前面的排序字段优先级较高；根据字段顺序排序：select 字段1,字段2 from 表名 order by 2，此时会按照查询的第二列，即这里的字段2进行排序，这种方式不健壮，因为查询字段不同时，其字段顺序也不同；排序操作总是在最后执行
6.分组查询：借助关键字group by，没有group by的话就默认整张表为一组。到这里所有的关键字的书写顺序应为select…from…where…group by…order by…，执行顺序为from->where->group by->select->order by。
select name,job,sum(salary) from employee group by job，这个语句在MySQL中可以执行，但是没有任何意义，因为查询结果是根据job进行分组，就是说有多少种job最终就会出来多少条记录，而每一种job对应的多条记录的其它字段就被折叠了，比如这条语句中查询了name，一种job会对应多个人，也就会对应多个name，最终查询结果只会出现一个name，这样的数据没有什么意义。在Oracle中执行会报错，所以Oracle的语法会更严格。所以说，在一条select语句中，如果有group by语句的话，select后面只能跟参加分组的字段(如前面的语句中的job)以及分组函数，其它的都不要跟
当要进行分组的字段多于1个时，group by参与分组的字段1,参与分组的字段2…即可
使用having可以对分完组的数据进一步过滤。having不能单独使用，不能代替where，只能和group by联合使用(在group by后使用)
示例：a.查询显示每个部门的最高薪资：select dept,max(salary) from employee group by dept;
b.要求只显示薪资大于3000的：select dept,max(salary) from employee group by dept having max(salary) > 3000;
c.以上的sql语句效率会低，可以这样考虑：select dept,max(salary) from employee where salary > 3000 group by dept;where跟having优先考虑where，where无法完成再使用having，where相当于先筛选再分组，having相当于先分组然后再筛选，例如要显示平均薪资大于2500的，就必须得用having了
7.查询结果去除重复记录：使用关键字distinct，select distinct 字段 from 表名。distinct后能跟多个字段，且只能在所有字段的前面，如select name,distinct salary from employee这条语句是语法错误，因为对name跟对distinct salary的查询结果的记录数可能是不同的，自然就无法显示。用在多个字段前相当于对多个字段联合起来去除重复记录。
示例：统计不同工作岗位的数量：select count(distinct job) from employee;

数据处理函数

数据处理函数又称单行处理函数，特点是一个输入对应一个输出。与其相对应的是多行处理函数
1.lower(字段名)，转换小写
2.upper(字段名)，转换大写
3.substr(被截取的字符串,起始下标,截取的长度)，取子串。起始下标从1开始
4.length(字符串)，取长度
5.trim()，去空格
6.str_to_date()，将字符串varchar类型转换成日期date类型，str_to_date(‘字符串日期’,‘日期格式’)，MySQL中的日期格式：%Y，年；%m，月；%d，日；%h，时；%i，分；%s，秒。如要将varchar字符串日期’01-10-1990’转化为日期date类型则是str_to_date(‘01-10-1990’,’%d-%m-%Y’)
7.date_format()，将date类型转换为具有一定格式的varchar类型，用法类似于str_to_date()函数，date_format(日期类型数据,‘日期格式’)。如date_format(1990-10-01,’%m/%d/%Y’)得到的结果就是10/01/1990。这个函数通常用于查询日期数据时，设置显示出来的日期格式。如果在查询日期数据时没有使用这个函数设置显示的格式，sql语句执行时实际上会进行默认的日期格式化(’%Y-%m-%d’)，将date类型转化为varchar类型
8.format()，设置千分位，format(数字,‘$999,999’)
9.round(字面数值,保留几位小数)，四舍五入。如round(1237.567,0)得到的结果就是1238；round(1237.567,-1)得到的应该是1240；round(1237.567,1)得到的1237.6
10.rand()，生成一个处于[0,1)的随机数
11.ifnull()，可以将null转换成一个具体值。在所有的数据库中，只要有NULL参与的数学运算，最终结果都是NULL。所以为了避免这个现象，需要用这个函数将NULL转换为其它数值。用法：ifnull(值1,值2)，如果值1为NULL，那么用值2替代
12.concat(字符串1,字符串2)，拼接字符串
13.case…when…then…when…then…else…end，类似于C语言中的switch-case语句。如"select name,job,(case job when ‘manager’ then salary * 1.1 when ‘worker’ then salary * 1.5 else salary end) as new_salary from employee;"语句表示查询employee表时，如果是经理的话工资显示上调10%，如果是工人工资显示上调50%，其它人正常

分组函数

分组函数又称多行处理函数，特点是输入多行，输出一行。使用前必须先分组，如果没有分组，会把整张表默认为一个组
1.count()，计数
2.sum()，求和
3.avg()，求该字段数据平均值
4.max()，求该字段所有数据中的最大值
5.min()，求该字段所有数据中的最小值
分组函数会自动忽略NULL，不需要事先对NULL先处理，所以使用count()函数计数含NULL的字段时，得到的结果是不为NULL的记录数而不是表中总的记录数；count(具体字段名)表示统计该字段下所有不为NULL的元素的总数，而count(*)表示统计表当中的总行数(总记录数)；分组函数不能直接使用在where子句中，如select name,salary from employee where salary > min(salary)，本意是想查询出比最低工资高的员工的信息，但执行后会出现报错“1111:Invalid user of group function”，因为分组函数在使用的时候必须先分组，而根据前面说到的执行顺序，where执行的时候还没有分组，自然就无法执行分组函数

连接查询

根据语法的年代，可以分为sql92跟sql99，分别是1992年跟1999年出现的语法
根据表连接的方式，可以分为内连接，外连接跟全连接。内连接又可分为等值连接，非等值连接跟自连接；外连接又可分为左外连接跟右外连接

笛卡尔积现象

当两张表进行连接查询没有任何条件限制时(如select ename,dname from employee,dept;)，最终查询结果条数为两张表的记录数的乘积，称之为笛卡尔积现象。
避免笛卡尔积现象需要加上条件限制(如select ename,dname from employee,dept where emp.deptno = dept.deptno)，最终查询结果会因条件限制而减少，条件就是为了在所有的匹配记录(笛卡尔积)中找出符合需求的有效记录，不过总的匹配次数不会减少，还是笛卡尔积的大小。所以表的连接次数越多，所需的匹配次数就越多，效率越低
连接查询时应该为表起别名(如select e.ename,d.name from employee e,dpet d where e.deptno = d.deptno)，是效率问题

内连接

1.等值连接
查询每个员工所在部门的名称
sql92语法：select e.ename,d.dname from employee e,dept d where e.deptno = d.deptno
sql99语法：select e.name,d.name from employee e (inner) join dept d on e.deptno = d.deptno(句中的inner可省略，不省略的话可读性更好)
sql92的缺点在于其结构不清晰，表的连接条件和后期进一步对连接查结果进行筛选的条件都放到了where的后面；而sql99中表连接的条件是独立的，连接之后如果需要进一步筛选，只需继续往后添加where子句，表的连接条件单独出现在on后面
句中的连接条件是等量关系，所以称为等值连接
2.非等值连接
查询每个员工的薪资的级别，其中薪资处于某个特定区间内时为一个特定级别
select e.name,e.salary,s.grade from employee e inner join salary_grade s on e.sal between s.lowsalary and s.highsalary;
条件不是一个等量关系，称为非等值连接
3.自连接
员工表中包含了所有包括普通员工以及上级领导在内的所有员工，其中有一个字段employeeno表示员工编号，字段manager表示的是员工的上级领导的编号。现在要查询每个员工的上级领导，就应该把员工表看成两个表，一个员工表，一个领导表，进行自连接
select a.name as ‘员工名’,b.name as ‘领导名’ from employee a join employee b on a.manager = b.employeeno;
连接条件相当于员工的上级领导编号等于领导的员工编号
内连接的特点，完全能够匹配连接条件的数据就查询出来。查询时没有主表次表之分

外连接

1.在查询每个员工所在部门时，就算有的部分没有对应的员工信息也要显示出来
select e.ename,d.dname from employee e right (outer) join dept d on e.deptno = d.deptno;
这是一个右外连接(右连接)查询操作，表示将join关键字右边的表dept看为主表，它的数据会被全部查询出来，而关键字左边的表employee中如果在连接条件下没有与dept表匹配的记录的话，查询结果中就会显示为NULL。相对的即为left，左外连接(左连接)，左右连接可以相互转换，产生相同的查询结果
可以发现，外连接的查询记录条数一定大于等于内连接的记录条数
2.查询每个员工的上级领导，显示所有的员工名字
select a.name as ‘员工名’,b.name as ‘领导名’ from employee a left join employee b on a.manager = b.employee no;

三张表，四张表的连接

基本语法：select … from a join b on a与b的连接条件 join c on a与c的连接条件 join d on a与d的连接条件。具体每一步联查使用内连接还是外连接，连接条件是什么，看具体需求

子查询

select语句中嵌套select语句，被嵌套的select语句则被称为子查询
select …(select) from …(select) where …(select);

where子句中的子查询

示例：找出比最低工资高的员工姓名以及工资
第一步，查询最低工资是多少；第二步，查询工资大于最低工资的
合起来就是select ename,salary from employee where salary > (select min(salary) from employee);

from子句中的子查询

将from子句中的子查询的查询结果当作一张临时表即可
示例：找出每个岗位的平均工资的薪资等级
第一步，找出每个岗位的平均工资：select job,avg(salary) from employee group by job;
第二步，把上面的拆线呢结果看作一张新的临时表，查询其中每条记录的平均工资字段所属的薪资等级
select t.*,s.grade from (select job,avg(salary) as avgsal from employee group by job) t join salary_grade s on t.avgsal between s.lowsal and s.highsal;
子查询的结果起别名t，平均工资这个字段应该起别名，后面t表引用这个字段时才能用别名进行引用，而不能t.avg(salary)，这样的话关键字会被识别为一个函数

select子句中的子查询

示例：找出每个员工的部门名称，显示员工名，部门名(员工表中有一个字段deptno表示该员工所属部门编号，即部门表中的主键)
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
子查询每次只能返回一条记录，如果子查询会返回多条记录，执行时会报错“ERROR 1242:subquery returns more than 1 row”

union合并查询结果集

示例：查询工作岗位是MANAGER和SALESMAN的员工
写法1：select ename,job from employee where job = ‘MANAGER’ or job = ‘SALESMAN’;
写法2：select ename,job from employee where job in(‘MANAGER’,‘SALESMAN’);
写法3：select ename,job from employee where job = ‘MANAGER’ union select ename,job from employee where job = ‘SALESMAN’;
union的效率相对于表连接来说效率会更高，因为多表查询的匹配次数满足笛卡尔积，而union是相当于将多表查询分为多个查询结果集，减少匹配次数，完成两个结果集的拼接，相当于把乘法运算变成了加法运算
注意事项：

• union在进行结果集的合并的时候，要求两个结果集的列数相等
• 合并时，结果集间每一对应的列其数据类型也应该时相同的。虽然不相同的话在MySQL中是允许的，不会报错，但在Oracle中就不可以，会报错

limit

limit用于将查询结果集的一部分取出来，通过使用在分页查询中
用法：limit startIndex,length，表示从查询结果的startIndex下标对应记录开始(包含这条记录)，取length条记录(下标从0开始)；也可以直接limit length，取前length条记录(相当于这里的startIndex为0)。例如limit 0,5跟limit 5取出的结果是一样的
MySQL当中limit在order by之后执行，到这里各关键字在SQL语句中的书写顺序为select … from … where … group by … having … order by … limit …；执行顺序为from -> where -> group by -> having -> select -> order by -> limit

分页

假设每页显示pageSize条记录，那么第pageNo页(从1开始)对应的limit子句应为limit (pageNo - 1) * pageSize,pageSize

关于表的语句

建表

create table 表名(
字段名1 数据类型,
字段名2 数据类型,
…,
字段名n 数据类型
)
可以在数据类型后加上“default 默认值”用于指定该字段如果没有被赋值时的默认值
规范问题，表名以及字段名都使用小写字母，单词之间用下划线’_'分开
使用show create table 表名;可以显示出表创建时使用的语句

数据类型

• varchar：可变长度的字符串，在一开始建表声明时设定一个长度(最长255。数据类型中的指定的长度指的是存储数据的内容的长度，不是所占空间的字节数)，但会根据实际的数据长度动态分配空间，也就是说当有一个记录的这个字段的数据长度小于一开始设定的长度的话，会自动分配实际数据大小的空间。优点是节省空间，缺点是需要动态分配空间，速度慢
• char：定长字符串，一开始声明时设定一个长度(最长255)，不管实际数据的长度是多少，都会分配固定长度的空间来存放数据。优点是不需要动态分配空间，速度快，缺点是空间大小固定，使用不当可能会造成空间的浪费
  对于这两种字符串数据类型，实际使用时需恰当选择。例如性别字段的数据应该是固定长度的字符串，所以选择char；姓名字段的数据长度各有不同，所以选择varchar
• int，整型，等同于java中的int，长度最长11；bigint，长整型，等同于java中的long
• float，单精度浮点型；double，双精度浮点型
• date，短日期，只有年月日，%Y-%m-%d；datetime，长日期，包含年月日时分秒，%Y-%m-%d %h:%i:%s。可以使用now()函数获取系统当前时间，是datetime类型；timestamp，时间戳，包含年月日时分秒
• clob(Character Large OBject)，字符大对象，最多可以存储4G的字符串；blob(Binary Large OBject)，二进制大对象，专门用来存储图片，声音，视频等流媒体数据，往blob类型的字段插入数据的时候需要使用IO流

删除表

drop table 表名
drop table if exists 表名：删表之前先判断表是否存在，存在再删除，这样不存在的话才不会报错

插入记录insert

1. 基本语法：insert into 表名(字段1,字段2,…,字段n) values (值1,值2,…,值n);
注意字段名和值要一一对应，包括字段数量和数据类型。没有选择要赋值的字段，在执行insert语句后对应的值就默认为NULL
2. 也可以省略字段名这一部分不写，不过不写的话相当于都写上了，所以后面的值也应该都写上，即每个字段都应该提供值，如user表中有3个字段且顺序为字段1，字段2，字段3，那么值的列表中就应该包含3个值，分别按顺序对应表中3个字段：insert into user values(value1,value2,value3)。其中value1对应字段1，value2对应字段2，value3对应字段3
3. 日期字段可以用date类型，当然也可以使用varchar字符串类型
如果使用的是date类型，在插入数据时使用的是字符串型数据(如’1990-10-01’)需要借助数据处理函数str_to_date()来将varchar转化为date类型
如果此时表中日期字段使用的是date类型，而且写插入语句时提供的日期字符串恰好是 ‘%Y-%m-%d’ 这种格式，那么就不需要使用str_to_date()函数进行转换，执行sql语句时会进行自动类型转换
4. insert一次插入多条记录
inseet into 表名 (字段1,字段2,…,字段n) values (值1,…,值n),(值1,…,值n),…,(值1,…,值n);
每一个括号(每一个值的列表)对应一条记录

修改记录update

基本语法：update 表名 set 字段1=value1,字段2=value2,…,字段n=valuen where 条件;
注意没有条件的话表中所有记录都会被修改

删除记录delete

基本语法：delete from 表名 where 条件;
注意没有条件的话表中所有记录都会被删除

快速复制表

create table 结果表 as select * from 被复制的表;
快速复制要被复制的表得到结果表，表的结构包括记录数据都会被复制。其实就是将一个查询结果当作一张表新建 ，所以as 后面的部分也可以换成其它的查询语句。
类似地，可以将查询结果插入一张表中，insert into 要插入的表 select … from 被查询的表;

快速删除表中的数据

我们知道delete from 表名;可以删除表中的所有数据。delete语句删除数据的特点是，虽然表中的数据被删除了，但这个数据在磁盘上的真实存储空间不会被释放掉，缺点是删除效率比较低，优点是支持回滚，删除后可以恢复数据
另外一种删除数据的方式是truncate语句，这种方式效率更高，表被物理删除，不过不支持回滚。是DDL语句。基本语法：truncate table 表名;。只能删除所有记录，不能删除单条记录，要删除表中的单条记录只能用delete
当然以上的delete跟truncate都只是删除表中的数据，表还在

约束

约束的作用就是为了保证表中的数据完整，有效。包括非空约束，唯一性约束，主键约束，外键约束和检查约束(这种约束MySQL不支持，Oracle支持)

非空约束

建表时在要加入非空约束的字段后加上not null即可。非空约束的字段不能为NULL

唯一性约束

建表时在要加入唯一性约束的字段后加上unique即可。唯一性约束的字段不能重复，但是可以为NULL，即允许有多个NULL。
在MySQL中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段，Oracle中就不会

两个字段联合起来进行唯一性约束

建表时在要联合唯一的两个字段声明后，使用unique(字段1,字段2)即可，如
create table user(
id int,
name varchar(255),
emali varchar(255),
unique(name,email)
);
这样表示name跟email两个字段联合起来唯一，每两条记录中这两个字段可以有一个也至多只能有一个字段数据是相同的

列级约束与表级约束

约束添加在列后面的，如create table user(id int,name varchar(255) unique)，称为列级约束，not null只允许列级约束
约束没有添加在列的后面的，如create table user(id int,name varchar(255),emali varchar(255), unique(name,email))这样多个字段联合起来添加约束，称为表级约束。
需要给多个字段联合起来添加某一个约束时，需要使用表级约束

主键约束

主键值是每一行记录的唯一标识，类似于人的身份证号，同一张表中有可能两条记录之间每一个字段的数据都是相等的，因此需要一个数据不可能相等的字段作为唯一标识，这就是主键。任何一张表都应该有主键
主键具有not null 和 unique的特征，不为NULL也不能重复
一张表中只能有一个主键
主键值建议使用int，bigint，char等类型，不建议使用varchar。因为主键值一般都是数字，一般都是定长的

建表时设置主键

create table user(id int primary key,name varchar(255));
上面是列级约束的形式，也可以使用表级约束的形式设置主键：
create table user(id int,name varchar(255),primary key(id));
设置主键后可以加上auto_increment设置主键自增(每次自增1)

单一主键与复合主键

一个字段作为主键称为单一主键，使用表级约束将多个字段联合起来作为主键称为复合主键：
create table user(id int,name varchar(255),email varchar(255),primary key(id,name));
以上语句就是id和name两个字段联合起来作为表的复合主键
实际开发中建议使用单一主键即可，因为主键的作用就是为了作为唯一标识，单一主键就已经满足这一需求，不需要更加复杂的复合主键

自然主键与业务主键

除了以单一主键与复合主键作为主键的分类外，主键还可以分为自然主键与业务主键。自然主键指的是主键值是一个自然的数，与业务没有关系；业务主键指的是主键值和业务紧密相关，如用银行卡账号作为主键值。实际开发中使用自然主键比较多，因为主键只要做到不重复就行，不需要有过多的意义，而如果使用业务主键，一旦主键与业务挂钩，当业务发生变动的时候，可能会影响到主键值，因此不建议使用业务主键

外键约束

背景：设计数据库表来描述“班级与学生”的信息
第一种方式：班级和学生信息放在同一张表中，包括学生名字，所属班级编号，班级名称等等。这种设计方式的缺点在于数据冗余，空间浪费，因为会有很多个学生同属于同一个班级，所以同样的班级信息如班级编号，班级名称等会在表中多条记录一起出现，如果与班级相关的信息越多，那么重复出现的数据就会越多。这种设计比较浪费
第二种方式：班级信息作为一张表，学生信息作为一张表，学生表中只需要一个字段指向班级信息表中的某条记录，表示自己所属的班级即可，这样除了这个字段之外，其它的班级信息就不会在学生信息表中重复出现。这种方式可能会出现的问题就是学生表中的这个字段的数据可能在班级信息表中找不到对应的记录，为了处理这个问题，就需要为这个字段加上外键约束，保证这个字段的值都能在班级信息表中找到对应的记录，这就是外键约束的作用
在上面说到的这种情况下，是学生表中设置了一个外键字段来指向班级信息表，那么称班级信息表为父表，学生表为子表。删表的时候应该先删子表再删父表；创建的时候应该先创建父表再创建子表

建表时设置外键

create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
no int primary key auto_increment,
name varchar(255),
cno int,
foreigh key(cno) references t_class(classno)
);
此时插入t_student表记录，设置cno字段的值时，必须是t_class表中classno字段中出现的值，否则会报错

其他事项

外键值可以为NULL
子表中的外键字段引用父表中的某个字段，被引用的这个父表中的字段不一定要是主键，但至少要有unique唯一性约束

存储引擎

存储引擎是MySQL中特有的术语。是一个表存储，组织数据的方式。不同的存储引擎，表存储数据的方式不同
可以在建表的时候给表指定存储引擎，默认的存储引擎是InnoDB，默认的字符编码方式是utf-8，在建表的时候如果没有自己指定ENGINE(存储引擎)和CHARSET(字符编码方式)，就会使用默认的设置。例如：
create table t_user(
id int primary key,
name varchar(255)
)engine=InnoDB default charset=utf8;

常用存储引擎

show engines \G可以查看MySQL支持的存储引擎。MySQL支持9个存储引擎，但根据版本不同支持情况也不同

MyISAM

它管理的表具有如下特征：

• 使用三个文件表示每个表：1. 格式文件，存储表结构的定义(my_table.frm)；2. 数据文件，存储表行的内容(my_table.MYD)；3. 索引文件，存储表上索引(my_table.MYI)
• 可被转换为压缩，只读表来节省空间
  对于一张表来说，只要是主键，或者有unique约束的字段上，会自动创建索引

InnoDB

MySQL的默认存储引擎，它的特点是非常安全。InnoDB支持事务，保证数据的安全，支持数据库崩溃后自动恢复机制。但效率不是很高，并且也不能压缩，不能转换为只读。它管理的表具有下列主要特征：

• 每个InnoDB表在数据库目录中以.frm格式文件表示
• InnoDB表空间tablespace被用于存储表的内容(数据加索引)
• 提供一组用来记录事务性活动的日志文件
• 用COMMIT，SAVEPOINT及ROLLBACK支持事务处理
• 提供全ACID兼容
• 在MySQL服务器崩溃后提供自动恢复
• 多版本(MVCC)和行级锁定
• 支持外键及引用的完整性，包括级联删除和更新

MEMORY

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快(不需要和硬盘交互)。但是不安全，关机之后数据消失，因为数据和索引都是在内存中的。它管理的表具有如下特征：

• 在数据库目录内，每个表均以.frm格式的文件表示
• 表数据和索引被存储在内存中
• 表级锁机制
• 不能包含TEXT或BLOB字段
  MEMORY存储引擎以前被称为HEAP引擎

事务

一个事务其实就是一个完整的业务逻辑。例如转账，假设账户A向账户B转账了1块钱，那么账户A的钱将减掉1，账户B的钱将加上1，这就是一个完整的业务逻辑，其中的操作要么同时成功，要么同时失败。只有DML语句才会有事务这一说，只要操作涉及到了数据的增删改，那么就一定要考虑安全问题。正是因为完成某件事的时候需要多条DML语句共同联合起来才能完成，所以需要事务的存在；如果任何一件事都能一条DML语句搞定，那事务就没有存在的价值了。所以事务其实就是多条DML语句同时成功，或者同时失败

事务的实现

InnoDB存储引擎中提到，引擎提供了一组用来记录事务性活动的日志文件，在事务的执行过程中，每一条DML的操作都会记录到日志文件中。提交事务将会清空事务性活动的日志文件，将数全部彻底持久化到数据库表中，标志着事务的成功结束；回滚事务会将之前所有的DML操作全部撤销，清空日志文件，标志着事务的失败结束
commit语句，提交事务
rollback语句，回滚事务，回滚永远都是回滚到上一次的提交点
MySQL默认情况下是自动提交事务的，即每执行一条DML语句就提交一次；使用start transaction语句关闭自动提交事务
所以一般使用就是先start transaction语句，然后执行业务逻辑相关的一系列增删改语句，最后选择提交事务commit或者回滚事务rollback

事务特性

• A，原子性：说明事务是最小的工作单元
• C，一致性：在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性
• I，隔离性：两个事务之间具有一定的隔离
• D，持久性：事务最终结束的一个保障。事务提交就相当于将没有保存到硬盘上的数据保存到硬盘上

事务的隔离级别

• 读未提交：read uncommitted，最低的隔离级别。指事务A可以读取到事务B未提交的数据，这种隔离级别存在的问题就是脏读现象。这种隔离级别一般都是理论上的，一般的隔离级别都是第二级别起步。不管事务B是否提交，每次DML语句的操作结果事务A都能访问到
• 读已提交：read committed。指事务A只能读取到事务B提交后的数据，解决了脏读现象，但不能重复读取数据。这种隔离级别每次读到的都是绝对真实的数据。是Oracle默认的隔离级别。如果事务B未提交，不管做了什么DML操作，事务A都读取不到，只有事务B提交了，事务A才能读取到事务B提交后的结果
• 可重复读：repeatable read。解决了不可重复读的问题，但可能会出现幻影读，读取的数据不够真实。是MySQL默认的隔离级别。只要事务A开启了，且还未提交，这段时间内不管事务B做了什么操作，不管是DML还是提交事务，事务A都只能读到一开始读到的数据。
• 序列化/串行化：serializable，最高的隔离级别，效率最低，解决了所有问题。指事务排队进行，不能并发。事务A开启了，且对某一张表操作，此时事务B也想对这张表操作，会被阻塞，直到事务A提交了，事务B才能继续对那张表进行操作
  select @@tx_isolation或select @@session.tx_isolation：查看会话级事务隔离级别
  select @@global.tx_isolation：查看全局级事务隔离级别
  set global transaction isolation level 隔离级别：设置全局事务隔离级别

索引

概述

索引是在数据库表的字段上添加的，是一种提高查询效率的机制。一张表的一个字段可以添加索引，也可以多个字段联合起来添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的
例如select * from t_user where name = “xiaoming”，这条sql语句会扫描name字段，如果没有给name字段添加索引，MySQL就会全盘扫描，把name字段的每一个值都对比一遍
MySQL的索引也是需要排序的，底层使用的是B+树的结构，采用中序遍历方式进行遍历取数据
任何数据库中，表中的主键字段上都会自动添加索引；MySQL中，如果一个字段有unique约束，也会自动添加上索引
MySQL中，索引是一个单独的对象
索引是各个数据库进行优化的重要手段，优化的时候优先考虑的因素就是索引。索引可以分为单一索引(一个字段上添加索引)和复合索引(两个字段或更多字段上添加索引)，或主键索引(主键上添加索引)和唯一性索引(具有unique约束的字段上添加索引)。唯一性比较弱，即重复数据比较多的字段上添加索引，用处并不大

实现原理

任何数据库的任何一张表中，每条记录在硬盘存储中都会对应一个物理存储编号，这些编号与索引字段的值一起存放在索引中。当在索引所在的树上查找，通过索引对象定位到某个索引字段上的值时，就可以得到该记录对应的物理存储编号，进而通过编号访问存储

是否添加索引的考虑因素

• 数据量庞大时可以考虑添加索引。至于多大的数据量算庞大，这要视硬件环境而定，硬件环境越好，几亿的数据量也能很快处理好；硬件环境太差，几万的数据量也能处理得很慢
• 该字段如果经常出现在where子句中，以sql语句的查询条件存在，即该字段总是要被扫描，那也可以考虑加上索引
• 该字段很少进行DML增删改语句。因为DML之后，该字段的数据发生变化，索引需要重新排序
  建议不要随意添加索引，因为索引也是需要维护的，太多索引的话反而会降低系统的性能。建议通过主键查询以及通过添加了unique约束的字段进行查询

关于索引的操作

创建索引

create index name_index on user(name);
给user表的name字段添加索引，起名为name_index

删除索引

drop index name_index on user;
将user表上的name_index索引对象删除

查看sql语句执行情况

explain sql语句该命令可以查看指定sql语句的执行情况，展示出来的属性中，type属性为ALL表示是全扫描，为ref表示使用了索引进行检索；row属性是一个数值，表示本次执行扫描了多少条记录

索引失效

模糊查询

select * from user where name like ‘%T’;
在这个sql语句中，即使name字段上添加了索引，也不会使用索引进行检索，因为模糊查询中使用了’%‘开头。所以应该尽量避免模糊查询的时候以’%'开头，这也是一种优化的手段，策略

or

如果查询条件使用or，要求or两边的条件字段都要有索引，整个查询才会使用索引。如果只有其中一边有索引，那么另一边的字段上的索引也会失效，这就是为什么不建议使用or的原因。可以用union合并分别查询有索引的字段和没有索引的字段的查询结果集

复合索引

使用复合索引的时候，没有使用左侧的字段查找，则索引失效(这里涉及到b+树中的最左原则)
两个或更多字段联合起来添加一个索引，称为复合索引
例如create index job_salary_index on employee(job,salary);
给employee表上的job跟salary两个字段添加了复合索引。此时执行select * from employee where job = 'MANAGER’的话会使用索引；但执行select * from employee where salary = 800时就不会使用索引

索引字段参加了运算

在where子句中索引字段参加了运算，索引失效
例如create index salary_index on employee(salary)，执行select * from employee where salary = 800时会使用索引；但执行select * from employee where salary + 1 = 800时就不会使用索引

索引字段使用函数

例如employee表中的name字段添加了索引，但执行select * from employee where lower(name) = 'smith’时并不会使用索引

视图

相关操作

创建视图

create view user_view as select * from user;
这个语句就是用as后面select * from user的查询结果创建一个名为user_view的视图对象。as后面必须是DQL语句

删除视图

drop view user_view;
删除视图user_view

视图的作用

我们可以面向视图对象进行增删查改，对视图对象的增删查改，会导致原表数据一起被操作
假设有一条非常复杂的sql语句，而这条sql语句需要在不同的位置上反复使用。如果每一次要使用的话都把整个sql语句重新编写或拷贝，很长，很麻烦。可以把这条复杂的sql语句以视图对象的形式新建，在需要编写这条sql语句的位置直接使用视图对象，大大简化开发，而且利于后期的维护，需要修改sql语句的话只需要修改视图对象所映射的sql语句即可
使用视图的时候可以像使用表一样，可以对视图进行增删改查CRUD操作
视图对象不是在内存中的，而是存储在硬盘上的

sql脚本导出与导入

导出

导出整个数据库

在Windows的dos命令行窗口中，输入mysqldump 数据库名>本地存储路径 -u用户名 -p用户密码可以把导出指定数据库的sql脚本.sql文件到本地指定的存储路径，如mysqldump test>D:\test.sql，表示将test数据库导出到D盘中的test.sql文件中

导出数据库指定表

mysqldump 数据库名 表名>本地存储路径 -u用户名 -p用户密码可以导出数据库中指定表的sql脚本

导入

登录MySQL后，创建要执行sql脚本的数据库，然后使用该数据库，最后用source命令使用sql脚本进行初始化数据库(如source D:\test.sql)即可

数据库设计三范式

数据库设计范式即数据库表的设计依据，如何进行数据库表的设计。按照这些范式进行数据库表的设计，可以避免表中数据的冗余以及空间的浪费
共有三个范式：

• 第一范式： 要求任何一张表必须有主键，每一个字段原子性，不可再分
• 第二范式：建立在第一范式的基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖，部分依赖简单来讲就是表中有不同字段各自依赖于其它不同的字段。
  例如两个实体之间是多对多的关系，如用户与其喜欢的食物，一个用户可以喜欢多种食物，一种食物也可以被多个用户喜欢，每个实体有各自的id，如果将两个实体的信息都放在同一张表中，那么两个实体各自的信息会依赖于各自的id，如用户名依赖于用户id，食物名依赖于食物id，这就产生了部分依赖
  所以对于多对多的关系，应该使用第三张表来描述两者之间的关系，第三张表中使用两个外键分别与两张主体表联系起来
• 第三范式：建立在第二范式的基础上，要求素有非主键字段直接依赖主键，不要产生传递依赖，传递依赖简单来讲就是表中有一个A字段依赖于B字段，同时B字段又依赖于C字段
  例如两个实体是一对多的关系，如学生与其所在班级，一个学生只能属于一个班级，而一个班级可以包含多个学生，如果将学生和班级的信息(学生编号，学生名，班级编号，班级名)都放在同一张表中，那么班级名就会依赖班级编号，而班级编号又会依赖学生编号，这就产生了传递依赖
  所以对于一对多的关系，应该使用两张表分别表示两个实体，在“多”对应的实体的表中，用一个外键指向“一”对应的表，如在学生表中用一个字段班级编号指向班级表中的班级编号字段

一对一的关系通常都是放在同一张表中，不过有可能存在一张表字段太多的情况，这时可能就要拆分为两张表了，一张父表(如登录信息)，一张子表(如其他信息)，然后子表中设置一个添加唯一性约束和指向父表主键的外键约束的字段
实际开发时和理论总是有偏差的，有时候会拿冗余换执行速度。例如，因为表的连接次数越多，执行效率越差，有时候为了减少表的连接次数，也会把字段都放在一张表中，这也是合理的，而且对于开发人员来说，这样也会降低sql语句的编写难度。