MySQL笔记

1、SQL语句分类

DQL(数据查询语言) :查询语句，凡是select语句都是DQL。
DML(数据操作语言) : insert delete update, 对表当中的数据进行增删改。
DDL (数据定义语言) : create drop alter, 对表结构的增删改。
TCL (事务控制语言) : commit提交事务, rollback回滚事务。(TCL中的T是Transaction：事务)
DCL (数据控制语言) : grant授权、revoke撤销权限等。

2、mysql数据库加载

1、打开cmd窗口，输入mysql -uroot -p+（密码）连接数据库
2、建数据库：create database 数据库名；
3、导入sql文件到数据库：source +sql文件路径
4、使用该数据库：use 数据库名；
5、查看数据库表：show tables；

3、零散知识点

查询
简单查询
条件查询
查询顺序：from---where---group by---having---select---order by
分组查询：group by，having
连接查询：
1、内连接：等值连接、非等值连接、自连接
2、外连接：左连接、右连接
3、全连接

排序
排序：order by 字段名1，字段名2（先按照字段1排，数据一样则再按照字段2排序）
排序默认asc（升序），desc为降序，指定的话加在字段名后

分组函数count，sum，max，min，avg
例：select count（name）from emp；
规定：
①　（分组函数会自动忽略null）
②　（where后面不可以加分组函数）因为group by是在where后执行，所以要先分组才能执行分组函数

Count（*）：统计总记录条数
Count（某个字段）：统计该字段不为空的条数

单行处理函数：ifnull（字段名，代替值）一行一行处理
例：ifnull（sal，0）：如果sal为null，则将sal的值代替为0

去重distinct

例:Select distinct dep，sal from emp；

字段两个以上表示联合起来去重

笛卡尔积现象
两张表字段乘积，加where条件不会减少匹配次数，只会筛选出合适的记录

select 
	e.name,d.dname
from
	emp e
join 
	dept d
on
	e.deptno = d.deptno

4、多表查询

例：

Select
Xxx
From
A
Join
B
On
A.xxx=B.xxx
Join
C
On 
A.xxx=C.xxx
Left join
A a1
On
A.xxx=a1.xxx

5、limit（用于分页查询）

1、limit是mysql特有的
2、limit取的是结果集的部分数据
3、语法：

limit startindex，length

4、limit最后执行
5、例子：找出e表工资第4到第9的员工

select ename from e order by sal desc limit 3,6

6、常见数据类型：

1、
int 整型（Java的int）
bigint长整型（java的long）
float 浮点型（Java的float double）
==char == 定长字符串（String）
varchar 不定长字符串（StringBuffer，StringBuilder）
==date == 日期（java.sql.date）
BLOB 二进制大对象（视频，音频）（Object）
CLOB 字符大对象（大的文本）（Object）

2、char与varchar选择：
char适用于确定长度的字符串
varchar适用于不确定长度的字符串

7、表

插入：

insert into xxx表（字段，字段.......） values（对应值.......），values（..........）.............

如果字段无对应值，且无设置默认值，则自动填入为空
复制表：

create table 表名 as DQL语句

批量插入：

insert into 表名 DQL语句

更新：

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

删除：

delete from 表名 where 条件

删除大表数据：
truncate table 表名：直接把表截断了，截到只剩个表头，不可回滚，永久丢失，表为空
增删改查术语：crud（create，retrieve（检索），update，drop）

8、约束

类型

非空约束（not null）
主键约束（primary key）
外键约束（foreign key）
唯一约束（unique）
检查约束（check）：Oracle才有

唯一性约束：
给一个列加unique：（列级约束）

create table A{
ID int unique
}

给多个列单独加unique：（列级约束）

create table B{
ID int unique,
name varchar(6) unique
}

多个列联合起来加unique：(表级约束)

create table C{
ID int ,
name varchar(6) ,
unique（ID,name）
}

主键约束：
按照字段个数：
单一主键
复合主键（多个字段联合构成主键，不推荐）
按照性质：
自然主键
业务主键（不推荐）

如何实现主键值自增：
在创表时，在对应主键后面加auto_increment

外键约束：

foreign key (主键名) reference 表名（字段名）

9、存储引擎：表的存储方式

1、默认采用InnoDB引擎和utf8编码

2、常见存储引擎：
（1）MyISAM存储引擎：（最常用的）
不支持事务
由三个文件表示每张表：
格式文件(.frm)（存储格式）
数据文件(.MYD)（存储数据）
索引文件(.MYI)（存储索引）

（2）InnoDB存储引擎：
安全，支持事务

（3）MEMORY存储引擎：
不安全，不支持事务，查询速度最快

10、事务（Transaction）

1、事务：完整的一个业务逻辑单元，不可再分

如：A向B转账100
update bank set balance=balance-100 where name=’A’
update bank set balance=balance+100 where name=’B’
必须一起成功/失败，否则会出大问题

2、事务只和DML有关，保证数据安全，完整性

3、提交事务：清空操作的历史记录并向硬盘文件修改数据
回滚事务：只清空操作的历史记录

4、提交和回滚事务：commit，rollback

5、事务四大特性：ACID
A（atomicity）：原子性： 事务是最小的工作单元，不可再分
C（consistence）：一致性：事务必须保证多条DML语句同时成功或失败
I（insulate）：隔离性：事务A与事务B之间具有隔离
D（durability）：持久性：最终数据需要持久化到硬盘文件中，事务才算成功

6、隔离级别：
第一级别：读未提交（read uncommitted）
读取到对方还没提交的数据，可能会有脏数据（数据已经过期、错误或者没有意义）

第二级别：读已提交（read committed）
读取到对方已提交的数据（缺点：不可重复读）

第三级别：可重复读（repeatable read）
缺点：可能读取到幻象

第四级别：序列化读（serializable）
缺点：效率低

7、mysql默认自动提交事务，即执行DML语句一次就提交一次
怎么关闭自动提交？

start transaction；

8、设置全局隔离级别：set global transaction isolation level +隔离级别

11、索引

1、给某一字段或某些字段添加索引

2、添加索引条件：
数据量大
该字段很少DML操作
该字段经常出现在where子句中

3、主键和有unique约束的字段会自动添加索引

4、创建索引：

create index 索引名 on 表名（字段名）

删除索引：

drop index 索引名 on 表名

5、底层采用B+Tree数据结构

6、索引实现原理：通过B树缩小扫描范围，底层索引进行排序，分区，索引带着数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位数据

7、索引的分类?
单一索引:给单个字段添加索引
复合索引:给多个字段联合起来添加1个索引
主键索引:主键上会自动添加索引
唯一索引:有unique约束的字段上会自动添加索引

8、索引什么时候失效?

select ename from emp where ename like ' %A%' ;
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。.

12、视图(view)

1、 什么是视图?
站在不同的角度去看到数据。(同一张表的数据，通过不同的角度去看待)。

2、 怎么创建视图?怎么删除视图?

create view myview as select empno, ename from emp ; .
drop view myview ;

注意:只有DQL语句才能以视图对象的方式创建出来。

3、对视图进行增删改查，会影响到原表数据。(通过视图影响原表数据的，不是直接操作的原表)
可以对视图进行CRUD操作（增删改查）。

4、 面向视图操作?

mysq1> select * from myview ;
update myview1 set ename='hehe' ,sal=1 where empno = 7369; //通过视图修改原表数据。.
delete from myview1 where empno = 7369; //通过视图删除原表数据。

5、视图的作用?
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

13、DBA命令

1、将数据库当中的数据导出.
在windows的dos命令窗口中执行:(导出整个库)

mysqldump bjpowernode>D : \bjpowernode.sql -uroot -p333

在windows的dos命令窗口中执行:(导出指定数据库当中的指定表)

mysqldump bjpowernode emp>D : \bjpowernode.sql -uroot -p123

2、导入数据

create database bjpowernode ;
use bjpowernode ; 
source D: \bjpowernode . sql

14、数据库设计三范式

1、 什么是设计范式?
设计表的依据。按照这个三范式设计的表不会出现数据冗余。

2、三范式都是哪些?
第一范式:任何一张表都应该有主键， 并且每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
口诀：多对多?三张表，关系表两个外键。

第三范式:建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
口诀：一对多?两张表，多的表加外键。

3、 一对一怎么设计?