mysql注意点

where与having区别

1）where和having都是为了完成数据的过滤，他们后面都是添加条件

2）where是在group by之前完成过滤

3）having是在group by之后完成过滤

select语句总结

一个完整的SQL语句如下：

select --对当前临时表进行整列读取
    xxxx
from  ---将硬盘上的表文件加载到内存
    xxxx
where --将符合条件的数据进行摘取出来，生成一张新的临时表
    xxxx
group by --根据列中的数据种类，将当前临时表划分成若干个新的临时表
    xxxx
having ---可以过滤掉group by生成的不符合条件的临时表
    xxxx
order by --对select生成的临时表，进行重新排序，生成新的临时表
    xxxx
    --limit对最终生成的临时表的数据行，进行截取

str_to_date函数：将字符串转换为日期

作用：将‘日期字符串’ 转换为 ‘日期类型’数据

用法：str_to_date(‘日期字符串’，‘日期格式’)

日期字符串：日期格式的字符串

日期格式：告知MySQL输入日期字符串的格式是什么

数据排序asc、desc

排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序（asc），如果存在 where 子句，那么 order by 必须放到where 询句后面。

collate为数据表的校对规则，指定数据集如何排序。

笛卡尔积现象

含义：若两张表进行连接查询的时候没有任何条件限制，最终的查询结果总数是两张表记录的成绩，该现象称为笛卡尔积现象。

SQL92语法

语法结构：select xxx from A 表名,B表名 where 表连接条件 and 数据查询条件;
缺点：表连接条件与查询条件放在一起，没有分离；

SQL99语法

语法结构：select xxx from A 表名 join B 表名 on 表的连接条件；
优点：表连接独立，结构清晰，如果结果数据不满足要求，可再追加where条件进行过滤；

连接查询根据：连接方式分类

内连接

1）等值连接
查询员工的名称和员工所对应的部门名称

select e.ename,d.dname from emp e (inner) join dept d on e.deptno = d.deptno;

2）非等值连接
查询员工的工资等级

select e.ename,s.grade from emp e join salgrade s on e.sal between losal and hisal;

3）自连接
查询员工的名称和员工对应的领导名称

select e.ename empname,b.ename leadername from emp e join emp b on e.empno = b.mgr;

外连接

定义：A表和B表能够完全匹配的记录查询出来之外，将其中一张表的记录无条件的完全查询出来，对方表没有匹配的记录时，会自动模拟出null值与之匹配；

1）左外连接
定义：包含左边表的全部行（不管右边的表中是否存在与他们匹配的行），以及右边表中全部匹配的行。

2）右外连接
定义：包含右边表的全部行（不管右边的表中是否存在与他们匹配的行），以及左边表中全部匹配的行。

找出每一个员工对应的部门名称，要求部门名称全部显示（左/右连接）

思路分析：

1）先查询出每一个员工的部门名称

2）查询部门信息

3）右外连接条件：员工表的部门编号=部门表的部门编号

select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;
select e.ename,d.dname from dept d left outer join emp e on e.deptno = d.deptno;

注意：任何一个右外连接都可写成左外连接，任何一个左外连接都可写成右外连接；

多张表进行表连接得语法格式：

select
    xxx
from
    A表
join
    B表
on
    连接条件1
join
    C表
on
    连接条件2

子查询

定义：select 语句嵌套select语句被称为子查询；

注意：select子句可出现在select、from、where关键字后面

union合并（相加集合）

定义：将查询的结果集合并
注意：合并结果集的时候，查询字段个数必须相同；

limit使用

作用：获取一表前几条或中间某几行数据

用法：limit起始下标m，长度n

varchar与char对比：

a）都是字符串

b）varchar比较智能，可以根据实际的数据长度分配空间，比较节省空间，但在分配的时候需要相关判断

c）char不需要动态分配空间，所以执行效率高，但是可能会导致空间浪费

d）若字段中的数据不具备伸缩性，建议采用char类型存储

e）若字段中的数据具有很强的伸缩性，建议采用varchar类型存储

删除表格

1）drop table t_student;

2）drop table if exists t_student;（推荐）

向t_student表格中插入数据

1）DML语句包括：insert、update、delete；

a）插入数据insert语法

insert into 表名(字段名1，字段名2，…) values(值1，值2)；
注意：字段和数值必须一一对应，字段与数据个数必须相同，数据类型必须一致；

表的复制

1.定义：将查询结果当作一张表创建

2.语法结构：create table 表名 as select 查询语句；

语法结构

1）新增：alter table 表名 add 字段名 字段类型（长度）；

2）修改：alter table 表名 modify 字段名 字段类型（长度）；

3）删除：alter table 表名 drop 字段名；
2.4 添加/修改/删除 表数据

DML数据操作语句

insert、update、delete；

2.4.2 insert添加数据
语法格式：insert into 表名(字段名1,字段名2…) values(值1,值 2)；

2.4.3 update修改数据
语法格式：update 表名 set 字段名 = 字段值，字段名 = 字段值 where 条件；

注意：update如果没有条件限制，将把整张表的数据全部更新；
2.4.4 delete删除数据
语法格式：delete from 表名 where 条件限制

注意：若没有条件限制，会将表中所有记录全部删除；

SQL分类

DQL语句 数据查询语言 select
DML语句 数据操作语言 insert delete update
DDL语句 数据库定义语言 create drop alter
TCL语句 事务控制语言 commit rollback

唯一性约束（unique）

作用：unique约束的字段具有唯一性，不可重复

非空约束

作用：not null约束的字段不能为NULL值，必须赋具体数据；
unique约束的字段不能重复，但是可以为NULL，NULL不是一个值，也不能用等号比较；

drop table if exists t_user;
create table t_user(
    id int(10),
    name varchar(32) not null,
    email varchar(128) unique
);

方法二：表级约束

drop table if exists t_user;
create table t_user(
    id int(10),
    name varchar(32) not null,
    email varchar(128),
    unique(email)
);

3）使用表级约束给多个字段联合添加字段

drop table if exists t_user;
create table t_user(
    id int(10),
    name varchar(32) not null,
    email varchar(128),
    unique(name,email)
);

主键设计到的3个术语：主键约束、主键字段、主键值

表中某个字段添加主键约束之后，该字段被称为主键字段，主键字段中出现的每一个数据都被称为主键值

主键作用

1）添加主键primary key的字段即不能重复也不能为空，效果与“not null nuique”相同，但本质是不同的，添加主键约束后，主键不仅会有“not null unique”作用，而且主键字段还会自动添加“索引——index”
2）一张表应该有主键，若没有，表示这张表时无效的，“主键值”是当前行数据的唯一标识
复合主键：是给多个字段联合添加一个主键约束：
drop table if exists t_user;
create table t_user(
id int(10),
name varchar(32),
email varchar(128),
constraint t_user_id_name_pk primary key(id,name)
);

外键约束FK（foreign key）

外键涉及到的术语：外键约束、外键字段、外键值
给某个字段添加外键约束之后，该字段称为外键字段，外键字段中的数据称为外键值。

外键根据个数分为：单一外键、复合外键
1）单一外键：给一个字段添加外键约束

2）复合外键：给多个字段添加一个外键

2.3 外键在同一张表中可以有多个外键存在

①外键字段可以为NULL，外键为空的数据也叫孤儿数据；

②被引用字段必须具有unique约束

③有了外键引用之后，表分为父表和子表，父表：t_class；子表：t_student；创建表时先创建父表，在创建子表；插入数据时，先插入父表数据，再插入子表数据；

存储引擎

通过采用不同的技术将数据存储在文件或内存中
每一种技术都有不同的存储机制，不同的存储机制提供不同的功能和能力
通过选择不同的技术，可以获得额外的速度或功能，改善我们的应用

查看存储引擎

show engines\G

常用的存储引擎

MylSAM存储引擎

MyISAM引擎是MySQL数据库最常用的
它管理的表具有以下特征：
使用三个文件表示每个表：
a）格式文件——存储表的结构
b）数据文件——存储表的数据
c）索引文件——存储表的索引
可转换为压缩、只读表来节省空间

InnoDB存储引擎

InnoDB存储引擎是MySQL数据库的缺省引擎；

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

a）每个InnoDB表在数据库目录中以.frm格式文件表示

b）InnoDB表空间tablespace被用于存储表的内容

c）提供一组用来记录事务性活动的日志文件

d）用commit、savepoint及rollback支持事务处理

e）提供全部ACID兼容

f）在MySQL服务器崩溃后提供自动恢复

g）支持外键及引用的完整性，包括级联更新和删除

MEMORY存储引擎

使用MEMORY存储引擎的表，因为数据存储在内存中，且行的长度固定，所以使得MEMORY存储引擎非常快

MEMORY存储引擎管理的表具有下列特征：

a）在数据库目录内，每个表均以.frm格式文件表示

b）表数据及索引被存储在内存中

c）表级锁机制

d）字段属性不能包括TEXT或BLOB字段

事务Transaction

事务是什么

1）一个最小的不可再分的工作单元

2）通常一个事务对应一个完整的业务

3）而一个完整的业务需要批量的DML语句共同完成

4）事务只和DML语句有关系，或者说只有DML语句才有事务
.概述

事务具有四个特性ACID：

1）原子性

事务是最小单元，不可再分

2）一致性

事务要求所有的DML语句操作的时候，必须保证同时成功或同时失败

3）隔离性

一个事务不会影响其他事务的运行

4）持久性

在事务完成之后，该事务对数据库所作的更改将持久地保存在数据库中，并不会被回滚

事务中的一些概念

1）开启事务：start transaction

2）结束事务：end transaction

3）提交事务：commit transaction

4）回滚事务：rollbakc transaction
和事务有关的两条SQL语句

1）commit：提交

2）rollback：回滚

事务开启和结束的标志

开启：任何一条DML语句执行，标志事务的开启

结束

a）提交：成功的结束，将所有的DML语句操作记录和底层硬盘文件中数据进行一次同步

b）回滚：失败的结束，将所有DML语句操作记录全部清空
事务的隔离级别

事务四个特性ACID之一：隔离性（isolation）

隔离性有四个隔离级别：

1）read uncommitted 读未提交

2）read committed 读已提交

3）repearable read 可重复读

4）serialiazble 串行化

read uncommitted 读未提交（级别最低）

1）事务A和事务B，事务A未提交的数据，事务B可以读取

2）这里读取的数据可以叫做“脏数据”或“脏读Dirty Read”

3）读未提交隔离级别最低，这种级别一般只在理论上存在，数据库默认隔离级别一般都高于该隔离级别

read committed 读已提交

1）事务A和事务B，事务A提交的数据，事务B才可读取到；

2）该隔离级别高于“读未提交”级别；

3）换句话说：对方事务提交之后的数据，当前事务才可读取到；

4）该隔离级别可以避免脏数据；

5）该隔离级别能够导致“不可重复读”

repeatable read 可重复读

1）事务A和事务B，事务A提交之后的数据，事务B读取不到

2）事务B可重复读到数据的

3）这种隔离级别高于“读已提交”

4）换句话说，对方提交之后的数据，还是读取不到

5）这种隔离级别可以避免“脏读和不可重复读”，达到“重复读取”

6）MySQL数据库管理系统默认隔离级别为：可重复读

7）虽然可以达到“可重复读”，但是会导致幻象读

serializable 串行化

1）事务A和事务B，事务A在操作数据库表中数据的时候，事务B只能排队等待

2）这种事务隔离级别一般很少使用，吞吐量太低，用户体验不好

3）这种隔离级别可以避免幻象读，每一个读取都是数据库表中真实的记录

4）事务A和事务B不再并发

视图

1.什么是视图
1）视图在数据库管理系统中也是一个对象，也是以文件形式存在的

2）视图对应了一个查询结果，知识从不同的角度查看数据

数据库设计三范式

1.第一范式：主键、字段不能再分
定义：要求有主键，数据库中不能出现重复记录，每一个字段是原子性不能再分；

1）每一行必须唯一，也就是每个表必须有主键

2）主键通常采用数值型或定长字符串表示

3）关于列不可再分，应根据具体的情况来决定

2.第二范式：非主键字段完全依赖主键
定义：第二范式是建立在第一范式基础之上，要求数据库中所有非主键字段完全依赖主键，不能产生部分依赖

3.第三范式：建立在第二范式基础之上，要求主键字段不能产生传递依赖于主键字段