[大数据]MySQL数据库学习总结

连接：mysql -h 主机地址 -u 用户名 －p / mysql -u root -p root

DB database 数据库

DBMS 数据库管理系统 （Oracle mysql DB2 sybase sqlserver)

DBMS->(执行)->SQL->(操作)->DB

sql 语句分类：

DQL(数据查询语言）：select 查询语句

DML(数据库操作语言）: inset delect update

DDL(数据定义语言/修改表结构)：create? drop? alter

TCL（事务控制语言）：commit 提交事务 rollback 回滚事务

DCL（数据控制语言）：grant授权 revoke 撤销权限

source 路径（路径中不能有中文）； //初始化表格（去公司用的到）

注意：直接使用source 可以执行SQL脚本 SQL脚本数量太大时，无法打开，请使用source命令完成初始化

\c 终止一条语句

exit退出

优先级加（）

show tables from ....;查看别的数据库中的表格

show create table...;查看创建表的语句

DQL

1、简单的查询语句

select * from 表名； <!--查询表名中的所有字段，缺点：效率低，可读性差，尽量不要写在程序中-->

desc 表名；<!--查看表结构-->

select database(); <!--当前使用的数据库-->

select version（窝神）(); <!--查看数据库版本号-->

select 字段名 from 表名；<!--查询一个字段-->

select 字段名 ，字段名as 字段名 。。。。from 表名；<!--给字段名重命名（as关键字可以省略，或者用单引号或双引号括起来，一般使用单引号）-->

2、条件查询

<>或！= 不等于

between ...and ...两个值之间等同于 >= and <=

select 字段名... from 表名 where 条件 ;

is null 为空（is not null 不为空 ）

and 并且 or 或者

and 和or 同时出现 优先级问题 <!--and 比or的优先级高，如果想先执行 or 用括号分割-->

in(包含 )等同于or not in 可以取反

select 字段名 from 表名 where 字段名=or

select 字段名 from 表名 where 字段名 in （ ， ）；<!-- 括号里不是区间而是或的关系 -->

模糊查询 like % 表示任意多个字符 下划线_ 表示任意一个字符 eg select 字段名 from 表名 where 字段名like ‘ %o_ ’；

查询含有下划线的数据的时候，需要使用转义字符 \转义

3、排序

排序（指定升序asc、降序desc）默认升序 select 字段名 from 表名 order by 字段名 asc；升序 select 字段名 from 表名 order by 字段名 desc；降序

select 字段名 from 表名 order by 字段名 asc，字段名 desc；<!--升序起的主导作用大 相同时启动后面-->

查询加排序

select 字段名 from 表名 where 条件 order by 字段名 asc； 3 1 2 4

4、数据处理函数

单行处理函数：一行输入对应一行输出

常见的单行处理函数：

lower（） 转换小写

upper () 转换大写

substr() 取子串{substr（被截取的字段，起始下标（从1开始），截取长度）}

concat() 字符串拼接

length(字段名) 取长度

trim() 去空格

str_to_data 将字符串varchar类型转换为日期data类型

date_format 将日期data类型转换为字符串varchar类型 设置千分位

case ... when ...then ...when...then...else...end

round() 四舍五入

rand（）生成随机数

ifnull(数据，被当作那个值) 可以将null转换成一个具体值

常见的多行处理函数（分组函数）

多行处理函数：多行输入对应一行输出

count 计数 avg 平均值 sum 求和 max 求最大值 min 求最小值

分组函数使用时的注意事项：

1. 分组函数：自动忽略null ，不需要外加过滤条件，不需要提前处理

2. count（*）和count（具体字段）有什么区别？ count（）:不是统计某个字段中数据的个数，而是统计总记录条数。 count （）：表示统计某个字段中 不为null 的数据总数量

3. 分在函数：不可直接使用在where子句当中

4. 所有分组函数可以组合起来一起用

5.分组查询????

分组查询：先进行分组，然后再对每一组的数据进行查询

group by: 按照某个字段或者某些字段进行分组，group by后不能有where having : having 是对分组之后的数据进行再次过滤，不能单独使用，必须与group by组合使用

where与 having 优先使用where，where后面不能有分组函数

select .. from..where..group by...having...orderby... 5 1 2 3 4 6排序

为什么分组函数不能在where后面执行？

因为select 在group by 之后执行 而where在group by 之前执行

distinct去除重复记录

1. 关键字 distinct

2. distinct 只能出现在所有字段的最前方

3. distinct出现在两个字段之前，表示两个字段联合起来去除重复数据

6.连接查询?????

笛卡尔积现象：两张表进行连接查询，没有任何条件限制的时候最终查询条数，是两张表条数的乘积。

怎样避免笛卡尔积现象？

连接时加条件，满足这个条件的记录被筛选出来

根据语法出现的年代来划分的话，

包括： SQL92（比较老，几乎不用）

语法：select...fromA , B on where ...

SQL99（比较新的语法） 根据表的连接方式来划分

SQL99语法更清晰，表连接条件和后来的where条件分离了。（更常用）

语法：select...from A (inner ) join B on 连接条件 where ... <!--inner 可以省略-->

内连接：假设A和B进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的.

1. 等值连接

2. 非等值连接(连接条件中是非等量关系)

3. 自连接 （一个表看成两个表）

外连接：假设A和B进行连接，使用外链接的话AB两张表中有一张表是主表，一张表是附表，主要查询主表中的数据，捎带查询附表，当附表中的数据没有和主表的数据匹配上，附表自动模拟出null与之匹配。

1. 左外连接（左连接）：左边是主表

   语法：select...from A left join B on 连接条件

2. 右外连接（右连接）：右边是主表

   语法：select...from A right join B on 连接条件

3. 全连接（这个不讲，很少用！）

外连接的查询结果条数一定是>= 内连接的查询结果条数

三张表，四张表怎么连接？

select ... from a join b on ...(a和b的连接条件) join c on ... (a 和 c 的连接条件) join on ... (a和d的连接条件)

总结：一条SQL中内连接和外连接可以混合，都可以出现

子查询 子查询先执行（select嵌套select语句，被嵌套的select语句称为子查询）

语法：select ....(select) from ....(select) where ......(select)

union（可以将查询结果相加合并）

使用union效率更高（union把乘法变成了加法，减少次数）

union注意事项？

1. union在进行结果集合并的时候，要求两个结果集的列数相同。

2. MySQL可以，oracle语法严格，不可以，报错。

   要求结果集合并时列与列的数据类型也一样。

7、limit（分页查询全靠它）

limit是mysql中有特有的

完整用法 ：limit startIndex, length

startIndex 表示起始位置，从0开始，0表示第一条数据。

length 表示取几个

缺省用法：limit 5 ；这是取前五

语法：select ... from ... order by limit ....;

分页公式：

第pageNo页 ：limit（pageNo -1）*pageSize，pageSize pageSize每页显示多少条记录 pageNo 显示第几页

DQL语句总结

select .. from..where..group by...having...orderby... limit 5 1 2 3 4 6排序 7

char和varchar怎么选择？

varchar （最长255）：可变长度的字符串、比较智能节省空间，会根据数据长度自动分配空间，节省空间。 缺点：动态分配空间，速度慢

char（最长255）： 定长字符串。优点:速度快 缺点：使用不当造成资源空间浪费

在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。 当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

关于MySQL当中字段的数据类型？以下只说常见的

int 整数型(java中的int)

bigint 长整型(java中的long)

float 浮点型(java中的float double)

char 定长字符串(String) varchar 可变长字符串(StringBuffer/StringBuilder)

date 短日期类型 （包含年月日信息）%Y-%m-%d

datatime 长日期类型 (包含年月日时分秒信息）%Y-%m-%d %h：%i：%s

BLOB （ Binary Large OBject ） 二进制大对象（存储图片、视频等流媒体信息） 在使用时必须使用IO流

CLOB （Character Large OBject） 字符大对象（存储较大文本，比如，可以存储4G的字符串。）（超过255 就用它）

8、表的创建（建表DDL）

表名：建议t_ 或 tbl_开始

1. 创建一张表 create table 表名（

   列名 列类型，

   列名 列类型，

   列名 列类型 default 1，（默认值为1） ）；

2. 删除数据表（if exists 是指如果存在则删除，不存在则跳过） drop table [if exists] 表名；

3. 插入数据（DML）（一次可以查找多条数据）： insert into 表名（字段名1，字段名2.。。。。。）values (值1，值2。。。)；

4. insert 插入日期

   数字格式化：format（数字，‘ 格式 ’ ）

   mysql的日期格式：

   %Y 年

   %m 月

   %d 日

   %h 时

   %i 分

   %s 秒

5. 修改update（DML）

   update 表名 set 字段名1 = 值1，字段名2 = 值2，字段名3 = 值3 ....where 条件；

6. 删除数据delect （DML）

   delect from 表名 where 条件；

7. 快速创建表

   create table emp2 as select * from emp;

   原理：将一个查询结果当作一张表新建

   可以完成表的快速复制

   表创建出来，同时表中的数据也存在了

8. 快速删除表中数据

   用法：delect from 表名；

   delect 删除的原理？（DML）

   表中的数据被删除了，但数据在硬盘上的真实存储空间没有被释放，缺点是：删除效率比较低。优点是：支持回滚（rollback），后悔了可以再恢复数据。

   truncate语句删除数据的原理？（DDL）

   这种删除效率比较高，表被一次截断，物理删除。

   缺点:不支持回滚，不能恢复

   优点：快速

   用法：truncate from 表名；

9. 表结构的增删改（在实际开发中很少使用） a.修改列类型 alter table 表名 modify 列名 列类型; b.增加列 alter table 表名 ADD 列名 列类型; c.删除列 alter table 表名 drop 列名; d.列改名 alter table 表名 change 旧列名 新列名 列类型； e.更改表名 alter table 表名 rename 新表名； 或 rename table 表名 TO 新表名；

10. 修改字段 alter table employees modify age int(3);

11. 增加字段 alter table employees ADD column gender CHAR(1);

12. 删除字段 alter table employees drop column sex;

13. 修改字段名称 alter table 表名 change 旧列名 新列名 旧列类型； alter table 表名 change 旧列名 新列名 新列类型；

14. 更改表名 rename table ... to...

9、约束

1. 约束有哪些？ not null 非空 unique key 唯一键 primary key 主键 ????? （id int(4) not null primary key auto_increment 主键自增） foreign key 外键 （foreign key (classno) refernces t _class (cno)） check 检查约束（mysql 不支持，Oracle支持）

2. 添加约束

   添加非空约束 ALTER TABLE student2 MODIFY name varchar(255) NOT NULL; 添加唯一约束 ALTER table student2 ADD unique key student2_name_uk (name);//student2_name_uk为约束名 添加主键约束????? ALTER TABLE student2 ADD PRIMARY KEY student2(id); 添加外键约束（先增加外键字段，在增加约束） ALTER TABLE student2 ADD COLUMN ttid INT; ALTER TABLE student2 ADD CONSTRAINT student2_tid_fk FOREIGN KEY (tid) REFERENCES teacher (ttid)ON DELETE CASCADE ON UPDATE CASCADE;

3. 唯一约束总结：name字段虽然被unique约束了，但是可以为Null。

   给某一列添加unique ， 称为列级约束。

   给两个列或者多个列添加unique， 多个字段联合起来添加1个约束unique ， 称为表级约束。

   not null约束只有列级约束，没有表级约束。

   什么时候使用表级约束？

   需要给多个字段联合起来添加某一个约束得时候，需要使用表级约束。

   在MySQL 中 not null 和unique 可以联合，被他俩约束之后字段变成主键字段。（oracle不是这样）

4. primary key 主键约束?????

   表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。

   主键值是每行记录的唯一标识

   一个字段做主键叫单一主键

   多个字段联合起来叫复合主键

5. 主键的分类？

   根据主键字段的字段数量来划分：

   单一主键（推荐的，常用的。）

   复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）

   根据主键性质来划分：

   自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）

   业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

   最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要

   随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

6. 外键约束 foreign key ?????

   外键约束: foreign key

   外键字段：添加有外键约束的字段

   外键值：外键字段中的每一个值。

   为了防止出现字段没有任何约束可能导致的数据无效，需要给字段添加外键约束。

7. 存储引擎

   存储引擎这个名字只有在mysql中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字， 就是“表的存储方式”）

   完整的建表语句：

   CREATE TABLE ‘t_x ’(

   ‘id’ int(11) DEFAULT NULL

   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

建表的时候可以指定存储引擎，也可以指定字符集。 mysql默认使用的存储引擎是InnoDB方式。 默认采用的字符集是UTF8

查看当前mysql支持的存储引擎？

show engines \G

MySQL支持九大存储引擎，版本不同支持情况不同。

1. 常见的存储引擎

   MyISAM这种存储引擎不支持事务。

   MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。 MyISAM采用三个文件组织一张表： xxx.frm（存储格式的文件） xxx.MYD（存储表中数据的文件） xxx.MYI（存储表中索引的文件）

   优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。 缺点：不支持事务。

   InnoDB存储引擎（mysql默认的存储引擎，重量级存储引擎，安全性高，效率低）

   优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。 表的结构存储在xxx.frm文件中 数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读，不能节省空间。

   这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。 InnoDB支持级联删除和级联更新。

   MEMORY存储引擎

   缺点：不支持事务。数据容易丢失，不安全，关机后数据消失，不需要和硬盘交互。因为所有数据和索引都是存储在内存当中的。

   优点：查询速度最快，效率高。 以前叫做HEPA引擎。

10、事务

1. 事务（Transaction）：一个事务是一个完整的业务逻辑，是一个最小的工作单元，不可再分。（一个事务就是要完成一件事）

2. 和事务相关的语句只有：DML语句（insert delete update）。 只要涉及数据的增删改问题就熬考虑安全问题。

   事务本质上就是多条DML语句同时成功，或者同时失败。

   事务存在是为了保证数据的完整性安全性。

   假设所有业务都能用一条DML语句搞定，就不需要事务机制了在事务的。

   执行过程中，每一条DML的操作都会记录到”事务性活动的日志文件“中。

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

   Mysql 默认支持自动提交事务，每执行完一条DML语句，则提交一次。回滚只能滚回到上一次的提交点。

   关闭MySQL的自动提交机制？ 执行 start transaction；

   3.事务的特性？???? 事务包含四大特征：ACID A:原子性：事务是最小的工作单元，不可再分 C:一致性：事务必须保证多条DML语句同时成功或者同时失败 I: 隔离性：事务A与事务B之间具有隔离 D:持久性：持久性说的是最终数据必须持久化到硬盘当中，事务才算完成

3. 关于事务之间的隔离性 事务隔离性存在隔离级别，理论上隔离级别包括4个: 第一级别:读未提交(read uncommitted) 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。 读未提交存在脏读(DirtyRead)现象:表示读到了脏的数据。 隔离级别最低。 第二级别:读已提交(read committed) 对方事务提交之后的数据我方可以读取到。 这种隔离级别解决了:脏读现象没有了。

   oracle 默认读已提交

   读已提交存在的问题是:不可重复读。 第三级别:可重复读(repeatable read) 这种隔离级别解决了:不可重复读问题。 这种隔离级别存在的问题是:读取到的数据是幻象

   mysql 默认可重复读

   第四级别:串行化/序列化读 解决了所有问题 效率低。需要事务排队。

   oracle数据库默认的隔离级别是：读已提交。 mysql数据库默认的隔离级别是：可重复读。

11、索引

Mysql在查询方面主要有两种方式？

1. 全表扫描

2. 根据索引检索

索引之前必须先排序，这个索引的排序和TreeSet数据结构相同。TreeSet底层是一个自平衡的二叉树，在MySQL当中索引是一个B—Tree数据结构。

遵循左小右大的原则存放，采用中序遍历的方式遍历取数据。

mysql当中主键上自动创建索引，unique约束的字段也自动创建索引。

索引的实现原理？

每一行记录在硬盘上都有物理的存储标编号

其实最根本的原理是缩小了扫描的范围。

什么条件下给字段添加索引？

1. 数据量庞大

2. 该字段总是存在where后面，总是被扫描

3. 字段很少有DML的操作。

   建议不要随意添加索引，建议通过主键查询或者unique约束查询

索引怎么创建

语法：创建索引 create index 字段别名 on 表名（字段名）；

删除索引 drop index 索引字段 on 表名 ；

MySQL 中索引的底层是 B-Tree ； B-Tree 底层： 二叉树

查看sql语句是否用了索引？

explain 查询语句。。。

索引有失效的时候，什么时候索引失效？

失效的第1种情况:

select ename from emp where ename like '%A';

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

失效的第2种情况: 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因。

失效的第3种情况:

使用复合索引的时候，没有使用左侧的列查找，索引失效什么是复合索引? 两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

失效的第4种情况:

在where当中索引列参加了运算，索引失效。

失效的第5种情况:

在where当中索引列使用了函数，索引失效。

索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。

索引在数据库当中分了很多类 单一索引: 一个字段上添加素引。 复合索引: 两个字段或者更多的字段上添加索引。 主键索引: 主键上添加素引。 唯一性索引:具有unique约束的字段上添加索引。

注意:唯一性比较弱的字段上添加索引用处不大。

12、视图（view）

视图：站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

1. 创建视图（注意：只有DQL语句才能以视图对象的方式创建出来。）

   create view myview as select empno,ename from emp;

   删除视图

   drop view myview;

create table emp_bak as select * from emp;

create view myview1 as select empno,ename,sal from emp_bak;

update myview1 set ename='hehe',sal=1 where empno = 7369; // 通过视图修改原表数据。

delete from myview1 where empno = 7369; // 通过视图删除原表数据

视图的作用？ 方便、简化开发、利于维护 视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

13、DBA命令

1. 将数据库当中的数据导出 在windows的dos命令窗口中执行:(导出整个库) mysqldump bipowernode>D:\bipowernode.sq1-uroot-p333

   在windows的dos命令窗口中执行:(导出指定数据库当中的指定表) mysqldump bjpowernode emp>D:\bjpowernode.sq1-uroot -p123

1. 导入数据 create database bipowernode; use bjpowernode; source D:\bjpowernode.sql I

14.数据库设计三范式

第一范式:任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式:建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。

多对多，三张表，关系表两个外键

第三范式:建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖.

一对多，两张表，多的表加外键

设计表的时候，根据三范式进行，可以避免表中的数据冗余。

一对一 怎么设计？

主键共享，外键唯一（一对一，外键唯一）。

提醒: 在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。