MySQL（day01）
基本概念
1、sql、DB、DBMS分别是什么，他们之间的关系？
DB:
DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）
DBMS: <br /> DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer...）SQL: <br /> 结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。<br /> SQL属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。<br /> SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成。）<br /> <br /> DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。<br /> DBMS -(执行)-> SQL -(操作)-> DB
2、什么是表？
表：table
表：table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。一个表包括行和列：<br /> 行：被称为数据/记录(data)<br /> 列：被称为字段(column)<br /> <br /> 学号(int) 姓名(varchar) 年龄(int)<br /> ------------------------------------<br /> 110 张三 20<br /> 120 李四 21每一个字段应该包括哪些属性？<br /> 字段名、数据类型、相关的约束。
3、学习MySQL主要还是学习通用的SQL语句，那么SQL语句包括增删改查，SQL语句怎么分类呢？
DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。
DDL（数据定义语言）：create drop alter，对表结构的增删改。
TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
DCL（数据控制语言）: grant授权、revoke撤销权限等。
4、导入数据（后期大家练习的时候使用这个演示的数据）
第一步：登录mysql数据库管理系统
dos命令窗口：
mysql -uroot -p333
第二步：查看有哪些数据库
show databases; (这个不是SQL语句，属于MySQL的命令。)
+——————————+
| Database |
+——————————+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————————+
第三步：创建属于我们自己的数据库
create database bjpowernode; (这个不是SQL语句，属于MySQL的命令。)
第四步：使用bjpowernode数据
use bjpowernode; (这个不是SQL语句，属于MySQL的命令。)
第五步：查看当前使用的数据库中有哪些表？
show tables; (这个不是SQL语句，属于MySQL的命令。)
第六步：初始化数据
mysql> source D:\course\05-MySQL\resources\bjpowernode.sql
注意：数据初始化完成之后，有三张表：
+———————————-+
| Tables_in_bjpowernode |
+———————————-+
| dept |
| emp |
| salgrade |
+———————————-+
5、bjpowernode.sql，这个文件以sql结尾，这样的文件被称为“sql脚本”。什么是sql脚本呢？
当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
注意：直接使用source命令可以执行sql脚本。
sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。
6、删除数据库：drop database bjpowernode;
常用命令
- select database(); 查看当前使用的是哪个数据库
- select version(); 查看mysql的版本号。
- show create table emp; 查看创建表的语句
简单的查询语句（DQL）
- 语法格式：select 字段名1,字段名2,字段名3,…. from 表名;
提示：
1、任何一条sql语句以“;”结尾。
2、sql语句不区分大小写。
注意：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。
查询所有字段
select * from emp; // 实际开发中不建议使用*，效率较低。
条件查询。
语法格式：
select
字段,字段…
from
表名
where
条件;执行顺序：先from，然后where，最后select
模糊查询 like
需求：找出名字当中含有O的（在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_）<br /> %代表任意多个字符，_代表任意1个字符。<br /> select ename from emp where ename like '%O%';
运算符
详细的见宋红康版本的
常见的运算符：
in
in等同于or，in后面的值不是区间，是具体的值。
or
and
not in: 不在这几个值当中
总结：当运算符的优先级不确定的时候加小括号。and优先级大于or
排序（升序、降序）
注意：默认是升序。怎么指定升序或者降序呢？
asc表示升序，desc表示降序。
分组函数
- 需要掌握的聚合函数
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
- 记住：所有的分组函数都是对“某一组”数据进行操作的。
- 区别：
count()和count(具体的某个字段)，他们有什么区别？
count():不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(comm): 表示统计comm字段中不为NULL的数据总数量。
单行函数
单行处理函数
什么是单行处理函数？
输入一行，输出一行。计算每个员工的年薪？
select ename,(sal+comm)*12 as yearsal from emp;<br /> 重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。使用ifnull函数：<br /> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;<br /> <br /> **ifnull() 空处理函数？**<br />** ifnull(可能为NULL的数据,被当做什么处理) ： 属于单行处理函数。**
数据分组
group by 和 having
group by ： 按照某个字段或者某些字段进行分组。
having : having是对分组之后的数据进行再次过滤。总结：
注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。
3.语句分析：
select ename,max(sal),job from emp group by job;
以上在mysql当中，查询结果是有的，但是结果没有意义，在Oracle数据库当中会报错。语法错误。
Oracle的语法规则比MySQL语法规则严谨。
记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
- where和having的区别
DQL语句执行顺序
总结一个完整的DQL语句怎么写？
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
day（02）
数据类型
关于MySQL当中字段的数据类型？以下只说常见的
int 整数型(java中的int)<br /> bigint 长整型(java中的long)<br /> float 浮点型(java中的float double)<br /> char 定长字符串(String)<br /> varchar 可变长字符串(StringBuffer/StringBuilder)<br /> date 日期类型 （对应Java中的java.sql.Date类型）<br /> BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object）<br /> CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object）<br /> ......<br /> <br /> char和varchar怎么选择？<br /> 在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。<br /> 当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。<br /> <br /> BLOB和CLOB类型的使用？<br /> 电影表: t_movie<br /> id(int) name(varchar) playtime(date/char) haibao(BLOB) history(CLOB)<br /> ----------------------------------------------------------------------------------------<br /> 1 蜘蛛侠 <br /> 2<br /> 3
表名在数据库当中一般建议以：t或者tbl开始。
对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）
day（03）
约束
唯一性约束（unique）
- 唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。
事务（Transaction）
- 什么是事务？
一个事务是一个完整的业务逻辑单元，不可再分。
比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
update t_act set balance = balance - 10000 where actno = ‘act-001’;
update t_act set balance = balance + 10000 where actno = ‘act-002’;
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。
和事务相关的语句只有：DML语句。（insert delete update）
为什么？因为它们这三个语句都是和数据库表当中的“数据”相关的。<br /> 事务的存在是为了保证数据的完整性，安全性。
事务的特性？
**事务包括四大特性：A C I D**<br /> A: **原子性**：事务是最小的工作单元，不可再分。<br /> C: **一致性**：事务必须保证多条DML语句同时成功或者同时失败。<br /> I：**隔离性**：事务A与事务B之间具有隔离。<br /> D：**持久性**：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。
关于事务之间的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括4个：<br /> **第一级别：读未提交（read uncommitted）**<br /> 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。<br /> 读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。<br /> **第二级别：读已提交（read committed）**<br /> 对方事务提交之后的数据我方可以读取到。 [提交一次，就要重新读一次]<br /> 这种隔离级别解决了: 脏读现象没有了。<br /> 读已提交存在的问题是：不可重复读。<br /> **第三级别：可重复读（repeatable read）**<br /> 这种隔离级别解决了：不可重复读问题。 <br />（ 将现提交了的数据拍一个快照，然后在里面读，不影响后续事务的提交，比如银行需要统计这个月存了多少钱，取了多少钱，当日不能关闭系统走账，就需要重复读，不影响当日的钱的存取 ）<br /> 这种隔离级别存在的问题是：读取到的数据是幻象。<br /> **第四级别：序列化读/串行化读（serializable） **<br /> 解决了所有问题。<br /> 效率低。需要事务排队。<br /> （如果银行按照这个方式进行，统计数据需要关闭系统，影响当日走账）**oracle数据库默认的隔离级别是：读已提交。**<br />** mysql数据库默认的隔离级别是：可重复读。**
演示事务
mysql事务默认情况下是自动提交的。
（什么是自动提交？只要执行任意一条DML语句则提交一次。）<br /> **怎么关闭自动提交机制？ start transaction;**
- 提交事务：commit 回滚事务：rollback
索引
- 什么是索引？有什么用？
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式：
**第一种方式：全表扫描**<br /> **第二种方式：根据索引检索（效率很高）**
索引为什么可以提高检索效率呢？
**其实最根本的原理是缩小了扫描的范围。**<br />
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
添加索引是给某一个字段，或者说某些字段添加索引。
select ename,sal from emp where ename = 'SMITH';<br /> 当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。<br /> 当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。
怎么创建索引对象？怎么删除索引对象？
创建索引对象：<br /> create index 索引名称 on 表名(字段名);<br /> 删除索引对象：<br /> drop index 索引名称 on 表名;
什么时候考虑给字段添加索引？（满足什么条件）
- 数据量庞大。（根据客户的需求，根据线上的环境）
- 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
- 该字段经常出现在where子句中。（经常根据哪个字段查询）
注意：主键和具有unique约束的字段自动会添加索引。
**根据主键查询效率较高。尽量根据主键检索。**
查看sql语句的执行计划（explain 计划）：
explain 查询语句
索引底层采用的数据结构是：B + Tree
索引的实现原理？
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。<br /> select ename from emp where ename = 'SMITH';<br /> 通过索引转换为：<br /> select ename from emp where 物理地址 = 0x3;<br />
索引的分类？
单一索引：给单个字段添加索引<br /> 复合索引: 给多个字段联合起来添加1个索引<br /> 主键索引：主键上会自动添加索引<br /> 唯一索引：有unique约束的字段上会自动添加索引<br /> ....
索引什么时候失效？
select ename from emp where ename like '%A%';<br /> **模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。**
DBA命令
将数据库当中的数据导出
在windows的dos命令窗口中执行：（导出整个库）<br /> mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333<br /> <br /> 在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）<br /> mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123
导入数据
create database bjpowernode;<br /> use bjpowernode;<br /> source D:\bjpowernode.sql
数据库设计的三范式（重点内容）
什么是设计范式？
**设计表的依据。按照这个三范式设计的表不会出现数据冗余。**<br />
三范式都是哪些？
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
**第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。**<br /> 多对多？ 三张表，关系表两个外键。<br /> t_student学生表<br /> sno(pk) sname<br /> -------------------<br /> 1 张三<br /> 2 李四<br /> 3 王五t_teacher 讲师表<br /> tno(pk) tname<br /> ---------------------<br /> 1 王老师<br /> 2 张老师<br /> 3 李老师t_student_teacher_relation 学生讲师关系表<br /> id(pk) sno(fk) tno(fk)<br /> ----------------------------------<br /> 1 1 3<br /> 2 1 1<br /> 3 2 2<br /> 4 2 3<br /> 5 3 1<br /> 6 3 3<br /> <br /> **第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。**<br /> **一对多？ 两张表，多的表加外键。**<br /> 班级t_class<br /> cno(pk) cname<br /> --------------------------<br /> 1 班级1<br /> 2 班级2学生t_student<br /> sno(pk) sname classno(fk)<br /> ---------------------------------------------<br /> 101 张1 1<br /> 102 张2 1<br /> 103 张3 2<br /> 104 张4 2<br /> 105 张5 2<br /> <br /> **提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。**
一对一怎么设计？
一对一设计有两种方案：主键共享
t_user_login 用户登录表
id(pk) username password
———————————————————
1 zs 123
2 ls 456t_user_detail 用户详细信息表<br /> id(pk+fk) realname tel ....<br /> ------------------------------------------------<br /> 1 张三 1111111111<br /> 2 李四 1111415621
一对一设计有两种方案：外键唯一。
t_user_login 用户登录表
id(pk) username password
———————————————————
1 zs 123
2 ls 456t_user_detail 用户详细信息表<br /> id(pk) realname tel userid(fk+unique)....<br /> -----------------------------------------------------------<br /> 1 张三 1111111111 2<br /> 2 李四 1111415621 1<br />
若有收获，就点个赞吧
0 人点赞
