mysql 常用的sql语句

登陆语句

mysql -h127.0.1 (此处-h 后填写数据库的ip地址） -uroot ( 数据库的账户） -p123456（数据库的密码);

(相关资料图)

数据库不认识Java语言，但是我们需要和数据库进行交互，这时需要使用数据库认识的语言SQL语句SQL(structured query language) 结构化查询语言SQL分类：<1>数据定义语言 DDL(Data Definition Language)-- 数据库，表，列关键字： create alter drop<2>数据操作语言 DML (Data Manipulation Language)-- 插入，修改，删除 表或者视图的数据关键字: insert update delete<3>数据控制语言 DCL (Data Control Language)-- 用于创建用户，控制访问权限<4>数据查询语言 DQL ( Data Query Language)-- 查询关键字： select from where order by group by having limitSQL语句语法：SQL语句可以单行或者多行书写，以分号结尾可以使用空格或者缩进提供sql语句可读性MYSQL数据库的SQL语句不区分大小写-- 注释 /*多行注释*/

一.对库 ( database) 的操作

1.  创建库 create database  dept（数据库的名字)            -> default charactre set utf8  (设置数据库的编码格式）            -> collate utf8_genaral_ci;  (设计数据库的排序规则）        2.  show databases; -- 显示当前所有的数据库        3.  drop database    数据库名 ;   ----删除数据库        4.  show engines; ----- 显示数据库中支持的引擎        5.  show variables like "storage_engine";   ------ 查看当前默认的存储引擎

二. 对表 (table) 进行的操作（DDL语句）

use 数据库名; -------先使用数据库

DDL语句（数据定义语句）

1.创建表

create table <表名>(        <字段名1> <数据类型> [列级别约束条件] [默认值],        <字段名2> <数据类型> [列级别约束条件] [默认值],        <字段名3> <数据类型> [列级别约束条件] [默认值],        ...        )[engine=引擎] [default chaset=字符编码];        "注意：<> 必须要有的 [] 可有可无"        "最后一个字段后面不要加逗号"        "[]部分如果没有设置，则MYSQL会使用默认的设置"

约束

创建表时，要确保数据的完整性，【约束】是保证数据完整性的重要技术手段。 通过约束，MYSQL数据库对于数据库操作行为进行限定， 某些约束的操作行为被MYSQL数据库拒绝。 需要注意的是： 【列级别约束】位于列声明的末尾 表级别的约束，位于表声明的尾部

<1>非空约束（not null）

非空约束是一个列级别的约束，它要求所限定的列不允许插入空值。

create table 表名(        字段名 数据类型 not null,        ...        );

<2>唯一性(unique) 约束

唯一性约束，是一个列级别约束，设定的列不允许插入重复的值

create table 表名(    字段名 数据类型 unique,    ...    );

<3>默认值(default)约束

默认值约束，是一个列级别余数，所限定的列，当没有插入值 的时候，会插入这个默认值。

create table 表名(    字段名 数据类型 default 具体的值,    ....    );

<4>主键( primary key) 约束

主键约束，是一个表级别的约束，它等价于 非空 + 唯一 约束 它要求 所限定的列 非空且唯一

"一张表中，主键约束最多只能有1个"

主键约束分为两种：        单主键： 通常直接写在列的声明的尾部        create table 表名(        字段名 数据类型 primary key,        ...        );        联合主键：主键涉及2个列或者2个以上的列        多个列一起作为主键        create table 表名(        字段名1 数据类型,        字段名2 数据类型,        字段名3 数据类型,        ...        primary key(字段名1,字段名2,..)        );

<6>外键 foreign key 约束

外键约束，是一个表级别的约束，它用于表示 被它修饰的列中的数据，需要参考另一张表的 某列中出现的值。

create table A表(        ....        字段名 类型 references B表(某个字段)        ....        );        create table A表(        ...        ...        constraint 约束名 foreign key A表中的字段名 references B表(某个字段)        );        "如果两张表之间存在外键关联，则先创建主表，再创建从表"        删除表的时候则相反。

2.查询表结构

① 查看表的字段信息

desc 表名;    describe 表名;

② 查看建表语句

show create table 表名;

3.修改表中的元素—对列进行的操

关键字

add

modify

=====================================================

① 添加主键约束

alter table 表名 add [constraint 主键约束名]primary key(主键字段);

② 添加外键约束

alter table 从表名 add constraint fk_主表_主键字段    foreign key(外键字段名) references 主表(关联字段名);

③ 删除约束

alter table 表名 drop primary key;    alter table 表名 drop foreign key fk_主表_主键字段;

============================ 以上只需要了解就行======================

④ 添加列

add添加新的列

alter table 表名 add 新字段名 数据类型;

⑤ 修改列的类型长度以及约束**

modify修改列的属性

alter table 表名 modify 列名 数据类型(长度) 约束; -- 列级别的约束

⑥ 修改列的名称**

**change ** 修改列的名字

alter table 表名 change 旧列名 新列名 类型(长度) 约束;  -- 列级别的约束

⑦ 删除列**

**drop ** 删除列

alter table 表名 drop 列名;

⑧ 给表重命名**

**rename ** 给表重命名

rename table 旧表名 to 新表名;

⑨ 修改表的字符编码

alter table 表名 character set GBK;

⑩ 修改表的存储引擎

alter table 表名 engine=新的存储引擎;

4.删除表

drop table if exists 表名; -- 删除表        -- truncate table 表名; -- 将表中的数据清空 ,表还在    -- delete from 表名; -- 删除表的数据    -- 删除一张表的时候，先删除从表，再删除主表

三. 数据类型和运算符

1.数据类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jgjvvKOA-1607770888495)(C:\Users\24140\AppData\Roaming\Typora\typora-user-images\image-20201205181057821.png)]

2.基本的运算符

算术运算

运算符操作方式说明

+a + b实现两数相加

-a - b两数相减

--a一元减号，取负值

*a * b两数相乘

/a / b两数相除

%a % b两数取余

关系运算

运算符操作方式说明

=a=b如果两操作数相等则为true

!=,<>a != b,a<>b如果两操作数不相等则为true

>a > b如果a大于b,为true

>=a >= b如果a大于等于b,为true

<a < b如果a小于b,为true

<=a <= b如果a小于等于b,为true

ina in(b1,b2,b3…)判断a是否是b1,b2,b3…中的一个

between…anda between b1 and b2判断a是否在b1,b2之间

likea like b如果a与b匹配，则为true

not likea not like b如果a与b不匹配，则为true

is nulla is null如果操作数为null，则为true

is not nulla is not null如果操作数不为null，则为true

notwhere not 过滤条件取反

逻辑运算

运算符操作房四海说明

and,&&a and b,a && b逻辑与，两操作数都为true则为true

or，||a or b,a||b逻辑或，两操作数任意一个为true，则为true

not,!not a,!a逻辑非，如果操作数为false，则结果为true

四. 对表中的内容进行操作（DML语句）

DML语句（数据操作语句）

增删改操作属于 DML语句

DML语句用于操作数据库表和视图的数据。

insert向表中添加数据

update修改表中的数据

delete删除表中的数据

（1）insert 的使用

insert into表名(字段名1,字段名2,字段名3,…) values(值1,值2,值3,…);

insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);    "字段名 和 值 的顺序和数量都应该 一一对应"

（2） update 的使用

update表名 set字段名1=值1, 字段名2=值2,字段名3=值3,…where条件表达式;

update 表名 set 字段名1=值1, 字段名2=值2,字段名3=值3,.....where 条件表达式;        "凡是符合where子句的行都会被更新"    "没有 where 子句，修改所有的行的数据 "

（3）delete 的使用

delete from表名 where条件表达式;

delete from 表名 where 条件表达式;    "凡是符合条件的行会被删除，若没有where子句，则删除表中所有数据"    "清空表的数据，表还在"    drop table 表名; -- 删除表    truncate table 表名; -- 快速删除表中的数据，表还存在

****2.查询语句 (select)

select·是 DQL语句

DQL语句（数据查询语句）

（1）查询单例

select列名 from表名;

select 列名 from 表名;    -- 从指定的表中查询出单个指定的列的数据

（2） 查询多列

select列名1,列名2,… from表名;

select 列名1,列名2,... from 表名;    -- 列与列之间使用,逗号隔开

（3）查询所有的列

select * from表名;

select * from 表名;    -- * 通配符    -- 在实际的工作中，最好不要使用 * 通配符，因为查询出不需要的列通常会降低查询效率

（4）对查询的数据进行排序 (order by)

select字段名1,字段名2… from表名 order by列名; ---------------单列的排序

select 字段名1,字段名2... from 表名 order by 列名;    "如果不进行排序，则数据时以它们插入的顺序显示"    "如果需要排序，则可以使用order by"

select字段名1,字段名2… from表名 order by列名1,列名2,…; -----------------多列的排序

select 字段名1,字段名2... from 表名 order by 列名1,列名2,...;    "以第一个列进行排序，若出现相等，则会以第2个列进行排序，以此类推"

排序规则 ASC DESC

降序 DESC

升序 ASC 默认

（5）过滤数据 where

select字段名1,字段名2,… from表名 where过滤条件;

高级过滤

select * from 表名 where 过滤条件1 or 过滤条件2; -- 或者        select * from 表名 where 过滤条件1 and 过滤条件2; -- 并且        in 运算符替换or，    select * from 表名 where 列名 in (值1,值2,....);    -- in运算符要比or运行效率快些，in 里面可以写另一条sql语句        select * from 表名 where not 过滤条件; -- 取反

（6）模糊查询 (like)

select* from表名 where列名 like通配符字符串;

select * from 表名 where 列名 like 通配符字符串;    % 匹配出现任意次数的任意字符串 % 也可以匹配 0个字符 %不能匹配 NULL    _ 只能匹配单个字符    例如： 查询以字母"S"开头的员工信息    select * from employee where name like "S%";

**(7) 分组查询 ** （group by)

先分组再查询每个组里面的内容

在没有指定分组查询之前，所有查询的数据都默认在同一个组里面， 聚合函数都对这一组进行计算，并得出结果。 我们可以使用 group by 进行分组处理 select department_id,count(*) from employee group by department_id;

group by 和 select 一样，可以通过逗号分隔指定多列

按照多个列进行分组" 使用分组和聚合函数时， select 子句 只能存在三种元素

① 常数

② 聚合函数

③ group by 子句中的 列名

注意：group by 子句不能使用列的别名

having 分组条件 （对group by后续的补充继续对分组后的内容再过滤） （只能配合group by 使用，不能单独使用）

where 子句，用于指定行所对应的条件    having 子句，用于指定组所对应的条件    having 是分组以后，对分组后的结果再一次进行过滤    select department_id,count(*) from employee group by department_id having    count(*) > 5;

（8）limit 使用

可以用于分页实现，一般用于查询语句的末尾

limit 10,5 是从第11条数据为起点（不包含10），向后截取5条数据

(9)子查询

子查询：嵌套查询，select语句中也可以嵌套其他的select语句。    由一条select语句返回的结果充当另一条select语句的where子句的条件

关于SQL不区分大小写的问题

解决方案一（治标） 治标的办法是在查询语句中使用 binary关键字， binary 关键字可以加在两处地方（效果一样）：

select * from department where name = binary "testing";        select * from department where binary name = "testing";

binary表示的是：以字符串的二进制数据为依据进行比较，这样，比较的结果自然就是大小写敏感 的。

解决方案二（治本） 在指定 Charset（ utf8 或 utf8mb4 ）时，同时指定与之配套使用的 Collation 。 通过使用命令 show collation where Charset = ‘…’ 可以查看 utf8 和 utf8mb4 对应的 Collation 。但是，可惜的是有 _ci ，却没有 _cs （Case Sensitive，大小写敏感）的 Collation ！ 不过，有 utf8_bin 和 utf8mb4_bin 。你可以在建库，或建表，或在列声明中使用它们。

五 .DQL 语句的详解

1.DQl语句

① 语法

-- select语句中各种子句的顺序    select    {*|字段列表}    from 表1,表2....    [where 条件]    [group by 分组的列]    [having 分组条件]    [order by 列名 ASC|DESC]    [limit 偏移行,记录行数]

② 如何写查询语句

-- 先读懂题目    -- 注意： insert，update，delete都是单表操作    -- select 可以是单表，也可以是多张表    明确三个点：    【1】 题目要求你查询出什么？ (查询出哪些列)    -- 把你分析出来的列写在 select 查询列1,查询列2    【2】 从哪里查？单表，还是多表    -- 把你分析出来的表写在 from 目标表    【3】 查询的条件    -- 把你分析出来的条件写在 where 条件

③ 单表查询

-- 【语法：】select distinct *|字段名列表 from 表名    -- where 条件 group by 分组字段 having 分组条件    -- order by 列 ASC|DESC limit m,n;

④ 多表查询

SQL最强大的一个特性就是能自由地连接表。

1> 笛卡尔积

select * from 表1,表2;    -- 例如表1中有4条记录    -- 例如表2中有14条记录，查询出结果就是 14*4条    -- 有很多不符合规范的数据

2> 等值连接

select * from 主表,从表 where 从表.外键 = 主表.主键;    -- 若存在列名相同，则使用表名.列名区分    -- 若没有存在列名相同，则可以省掉表名.

3> 连接 join

连接，使用一种特殊的语法，可以把多个表连接起来

内连接（等值连接）

select 主表.列1,.....,从表.列1,...        from 主表 inner join 从表 on 主表.主键=从表.外键        where 过滤条件;

外连接

左外连接

保证左边表的完整性

select * from 表1 left join 表2 on 连接条件 where 过滤条件;    -- 1. 不能换表1和表2的位置

右外连接

保证右边表的完整性

select * from 表1 right join 表2 on 连接条件 where 过滤条件;

自连接

– 自连接(一张表自己连接自己)

– 前提：这张表中有字段相关联

select * from 表1 表1 where 连接条件 过滤条件

、

⑤ 子查询

子查询：嵌套查询，比较符合我们逻辑的查询，先查什么再查什么 是指select子句或者where子句中嵌入select语句，子查询的语句 放在括号中

把查询的结果放在where 后面当作查询的条件进行查询

⑥ 联合查询

对多个select语句的结果进行合并(要求：两个结果的列数和对应列的数据类型要一致) union和 union all

union 会去除重复的记录  union all 不会去重

-- select 查询工资大于1000员工姓名和工资    -- select 查询在1号部门和2号部门的员工姓名和工资    -- 使用union将两个结果合并    -- select 查询工资大于1000员工姓名和工资    -- select 查询在1号部门和2号部门的员工姓名和工资    -- 使用union将两个结果合并    select name,salary from employee where salary>1000 -- 12    union    select name,salary from employee where department_id in(1,2); --8

六. 正则表达式

mysql数据库中支持正则表达式

-- 判断是否以 fo 开头select "fofo" regexp "^fo";

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AGpZgvpS-1607770888498)(C:\Users\24140\AppData\Roaming\Typora\typora-user-images\image-20201206142526911.png)]

七 . myqsl 函数

1.条件判断函数

条件判断函数也称为控制流程函数，

根据满足条件的不同，执行相应的 流程。 MYSQL中支持IF、IFNULL、CASE

① if (表达式,值1,值2)

if(布尔表达式,v1,v2)        布尔表达式是TRUE，则返回v1        否则返回v2

② ifnull(表达式1,表达式2)

若表达式1为NULL，则使用第2个表达式

③ case when then end

类似于Java的switch        case 值        when 条件1 then 输出结果1        when 条件2 then 输出结果2        ...        else        默认结果        end

2.数学函数

函数名说明

abs(n)取数值绝对值

round四舍五入

ceiling(n)向上取整

floor(n)向下取整

format(n,len)截取len位小数（四舍五入）

rand()获取随机数(0-1)，取0-1之间随机的小数，无限接近于0且不会等于0，无限接近于1，且不会等于1

mod(a,b)求余数

pow(a,b)求x的y次方

sqrt(a)平方根，若负数返回NULL

3.字符串函数

函数名说明

concat (st2 [,… ])连接多个字符串为一个字符串

length(s)获取字符串的字节长度

lcase/ucase字符串转小写/字符串转大写

ltrim(s)/rtrim(s)去除字符串左边空格/右边空格

substr(s,pos,len)将字符串s从pos开始截取len长

lpad/rpad左填充/右填充

4.日期和时间函数

函数名说明

now()获取当前时间

current_date()获取当前日期

current_time()获取当前时间（时分秒）

current_timestamp()获取当前时间戳

date(t)获取时间的日期部分

day(t)获取日期中的天数部分

datediff(t1,t2)获取两个日期之差（天数）

date_format(now(),’%Y年%m月%d日’)日期格式化

5.加密和解密函数

函数名说明

md5(s)对字符串使用md5算法加密

sha(s)对字符串使用sha加密

password(s)使用sha1对字符串加密

6.聚合函数

函数名说明

count()统计数据行

sum()对指定列求和

avg()对指定列求平均值

max()获取指定列最大值

min()获取指定列最小值

八. 索引

索引：相当于一本书的目录，方便查找。

① 索引优点和缺点

优点

缺点

②如何合理的建立索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qXxvywyY-1607770888500)(C:\Users\24140\AppData\Roaming\Typora\typora-user-images\image-20201206182325231.png)]

③ 索引的分类

-- 创建索引的语法    create [unique|fulltext|spatial] index 索引名称 on 表名 (列表,..)    1 普通索引    允许索引列包含重复的值和NULL值    create index index_1 on tb_group(groupName);    2 唯一索引    索引值必须唯一，允许NULL值    3 全文索引    -- InnoDB 不支持全文索引 MyISAM存储引擎才支持全文索引    4 单列索引|组合索引    -- 单列索引，只涉及到一个列的索引，一个表可以创建多个单列索引    -- 组合索引，create index index_2 on tb_order(status,goodsType,create_time)    ,三列联合起来作为一个索引，只有    -- 查询语句中查询了status这个组合索引才起作用    5 空间索引    6 主键索引(聚簇索引)：即表中主键列

④ 创建索引和删除

create [unique] index <索引名称> on 表名(列名,...);    drop index 索引名称 on 表名;

【面试题SQL优化】

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索    引。    2、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。    3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表    扫描，如：    select id from t where num is null    可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：    select id from t where num=0    4、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫    描，如：    select id from t where num=10 or num=20    可以这样查询：    select id from t where num=10 union all select id from t where num=20    5、下面的查询也将导致全表扫描：    select id from t where name like "%abc%"    若要提高效率，可以考虑全文检索。    6、in 和 not in 也要慎用，否则会导致全表扫描，如：    select id from t where num in(1,2,3)    对于连续的数值，能用 between 就不要用 in 了：    select id from t where num between 1 and 3    7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但    优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访    问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：    select id from t where num=@num    可以改为强制查询使用索引：    select id from t with(index(索引名)) where num=@num    8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫    描。如：    select id from t where num/2=100    应改为:    select id from t where num=100*2    9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：1 优化 select count(*)    count()是一个聚合函数，主要是用来统计行数    count(*) 和 count(列名) 的区别？    count(*) 会统计NULL值 count(列名) 不会统计NULL    1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索    引。    2、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。    3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表    扫描，如：    select id from t where num is null    可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：    select id from t where num=0    4、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫    描，如：    select id from t where num=10 or num=20    可以这样查询：    select id from t where num=10 union all select id from t where num=20    5、下面的查询也将导致全表扫描：    select id from t where name like "%abc%"    若要提高效率，可以考虑全文检索。    6、in 和 not in 也要慎用，否则会导致全表扫描，如：    select id from t where num in(1,2,3)    对于连续的数值，能用 between 就不要用 in 了：    select id from t where num between 1 and 3    7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但    优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访    问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：    select id from t where num=@num    可以改为强制查询使用索引：    select id from t with(index(索引名)) where num=@num    8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫    描。如：    select id from t where num/2=100    应改为:    select id from t where num=100*2    9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：    select id from t where substring(name,1,3)="abc"--name以abc开头的id    select id from t where datediff(day,createdate,"2005-11-30")=0--"2005-11-    30"生成的id    应改为:    select id from t where name like "abc%"    select id from t where createdate>="2005-11-30" and createdate<"2005-12-1"    10、不要在where子句中的=左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使    用索引。    11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为    条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相    一致。    12、不要写一些没有意义的查询，如需要生成一个空表结构：    select col1,col2 into #t from t where 1=0    这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：    create table #t(...)    13、很多时候用 exists 代替 in 是一个好的选择：    select num from a where num in(select num from b)    用下面的语句替换：    select num from a where exists(select 1 from b where num=a.num)    14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复    时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex    上建了索引也对查询效率起不了作用。    15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及    update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，    视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是    否有必要。    16、应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录    的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统    需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。    17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性    能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数    字型而言只需要比较一次就够了。    18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以    节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。    19、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何    字段。    20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索    引）。    21、避免频繁创建和删除临时表，以减少系统表资源的消耗。    22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或    常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。    23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create    table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create    table，然后insert。    24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table    ，然后 drop table ，这样可以避免系统表的较长时间锁定。    25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改    写。    26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通    常更有效。    27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐    行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比    使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一    种方法的效果更好。    28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT    OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。    29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。    30、尽量避免大事务操作，提高系统并发能力。

九. 视图

① 视图的概念

从一张或者多张表中查询出来的结果，是一张虚拟的表。它没有真实的数据，数据来源于原表，就是SQL语句的结果集。你可以像操作表一样去操作视图。– 因为视图没有数据，数据存储在原表中– 当对视图进行修改，删除时，相应的原表也会发生改变。若原表的数据发生改变, 视图中的数据也会发生改变

② 视图的作用

1、简单化

直接查询视图就是你想要的结果，不用写复杂的sql了 那些被经常用于查询的复杂的sql语句可以定义成视图。

2、 安全性

不允许用户访问表 因为表中有敏感的数据

例如： 一个员工表，一般用户只能看到每个员工工号和姓名，工资和年龄等看不到 其实一般的用户看到的只是这个表的视图

select no,name,salary,age from emp;    create or replace view v_emp as select no,name from emp;

3、 帮助用户屏蔽真实表的结构

③ 创建视图语法

create or replace view <视图名称> as select语句;

④ 删除视图

drop view if exists 视图名称;

– 删除一个视图，会对原表有影响吗？

– 没有，但是你操作(update,delete)视图的数据，会对原表的数据有影响

⑤ 查看已经创建好的视图

show create view 视图名称;    -- 查看系统中的视图，若想要查看它的创建的sql，则可以使用show create

⑥ 视图和表之间区别

1 视图虚拟表 表是基本表2 视图是已经编译好的sql语句，是基于sql语句的结果集的可视化的表 而表不是，表就是存储真实数据的表3 视图没有实际的物理记录，表有4 表中是有内容， 视图没有只是一个窗口5 表占物理空间，视图不占物理空间6 视图是查看表的一种方式，只是一个sql语句的集合，从安全角度 防止用户接触真实的表。7 视图的创建和销毁不会影响原表，但是修改视图中的数据会影响原表 同理修改表的数据也会影响视图。

十. 用户管理 (DCL)

数据控制语言 DCL (Data Control Language)

mysql数据库中： root 系统用户 和 普通用户

新创建的用户，我们称为普通用户，任何权限都没有 我们需要使用root的身份登录给新用户授予权限， 新的用户才能操作。

① 查看当前数据库中所有的用户

select * from mysql.user;

② 如何创建新用户

create user "qiufen"@"localhost" identified by "密码";

③ 删除用户（不要删除root）

drop user "用户名"@"localhost"

④ 授予用户权限，回收权限

grant 权限名称 on 数据库名.* to "用户名"@"localhost";    revoke 权限名称 on 数据库名.* from "用户名"@"localhost";    grant all on *.* to "用户名"@"localhost";    ---授予全部的权限 all    -- 第一个 * 代表 所有的数据库    -- 第二个 * 代表 数据库中所有的表、视图等等

⑤ MYSQL中权限分类

mysql中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表。    权限表的存取过程是：        1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存    在表中，存在则通过身份验证；        2) 通过权限验证，进行权限分配时，按照user>db>tables_priv>columns_priv的顺序进行    分配。即先检查全局权限表 user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，    将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据    库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的    权限Y，以此类推

⑥ MYSQL常见的权限

以下操作都是以root身份登陆进行grant授权    1. usage    连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。    mysql> grant usage on *.* to "qiufen"@"localhost" identified by "123456";    该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能    删除用户。    2. select    必须有select的权限，才可以使用select table    mysql> grant select on j2001_db.* to "qiufen"@"localhost";    mysql> select * from dept;    3. create    必须有create的权限，才可以使用create table    mysql> grant create on j2001_db.* to "qiufen"@"localhost";    4. create routine必须具有create routine的权限，才可以使用{create |alter|drop}    {procedure|function}    mysql> grant create routine on j2001_db.* to "qiufen"@"localhost";    当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：    -- mysql> show grants for "qiufen"@"localhost";    5. create temporary tables(注意这里是tables，不是table)    必须有create temporary tables的权限，才可以使用create temporary tables.    6. create view    必须有create view的权限，才可以使用create view    mysql> grant create view on j2001_db.* to "qiufen"@"localhost";    mysql> create view v_dept as select id from dept;    7. create user    要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。    mysql> grant create user on *.* to "qiufen"@"localhost";    8. insert    必须有insert的权限，才可以使用insert into ….. values….    9. alter    必须有alter的权限，才可以使用alter table    10. alter routine    必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function}    mysql>grant alter routine on pyt.* to "qiufen"@"localhost";    11. update    必须有update的权限，才可以使用update table    12. delete    必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)    13. drop    必须有drop的权限，才可以使用drop database db_name; drop table tab_name;    14. show database    通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权    限。    对于qiufen@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看    到mysql数据库：    mysql> show databases;    15. show view    必须拥有show view权限，才能执行show create view。    16. index    必须拥有index权限，才能执行[create |drop] index    mysql> grant index on j2001_db.* to "qiufen"@"localhost";    17. excute    执行存在的Functions,Procedures    18. lock tables    必须拥有lock tables权限，才可以使用lock tables    19. references    有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。    ...

十一. 事务

1. 事务定义

Transaction

事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业 务，该业务就是一个最小的工作单元)一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML 语句的个数不同

为什么要事务

一个用户提交了一个订单，那么这条数据包含了两个信息，用户信息 和购买的 商品信息，我需要把他 们分别存到 用户表 和 商品表，如果不采用事务，可能会出现，商品信息插入成功，而用户信息没有， 这时候就会出现无主商品了，用户付了钱，却得不到商品，这就尴尬了，而如果采用事务，就可以保 证，用户信息 和 商品信息 都必须插入成功，该次事务才算成功，那就不会出现这种问题了

存储引擎 innoDB 是支持事务

2. 事务四大特征(ACID)

<1>.原子性：

对于事务中的多次更新操作要么同时成功，要么同时失败

<2>.一致性：

保证事务操作完成之后，所有的结果一致

<3>.隔离性：

事物之间各自独立存在相互不影响

<4>.持久性：

事务完成之后，确保所有的数据长期持久的存在

3、关于事务的一些术语

事务（transaction）

执行一组SQL语句； start transaction 即可开始一个事务

回退（rollback）

撤销指定SQL语句的过程； rollback 即可回退一个事务：rollback 必须是在一 个事务里面才能使用，没有事务，就不能谈回退

提交（commit）

将未存储的SQL语句结果写入数据库表； commit 使用 commit 来结束一个事务 的处理：一般使用事务，我们需要自己手动提交，mysql 默认是自动提交的，所以我们需要设置 set autocommit = 0 来更改提交模式。其中值得注意的是 rollback 也会触发提交事务

保留点（savepoint）

指事务处理中设置的临时占位符（placeholder），然后你就可以回退到该 点。 savepoint s1 生成一个保留点，然后可以通过 rollback to s1 来回退到 s1 这个保留点

4、事务开启的标志？事务结束的标志？

开启标志：

- 任何一条DML语句(insert、update、delete)执行，标志事务的开启

结束标志(提交或者回滚)：

- 提交commit：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步    - 回滚rollback：失败的结束，将所有的DML语句操作历史记录全部清空

5.事务与数据库底层数据

在事务进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在 内存中完成记录。只有在事务结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中 的数据

6.在MySQL中，事务提交与回滚

在MySQL中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句就开启了事 物，并且提交了事务

提交操作(事务成功)

start transaction； 手动开启事务DML语句; 执行insert,update，delete语句commit; 提交事务

-- 手动开启事务 (转账成功案例)    start transaction; -- 手动开启事务    update tb_account set balance=balance-100 where actno=1;    update tb_account set balance=balance+100 where actno=2;    commit; -- 提交事务，同步到数据文件中    select * from tb_account;

回滚操作(事务失败)

start transactionDML语句rollback

-- 手动开启事务 (转账失败案例)    start transaction; -- 手动开启事务    update tb_account set balance=balance-100 where actno=1;    update tb_account set balance=balance+100 where actno=2;    rollback; -- 事务回滚    select * from tb_account;

– 开启事务(事务一旦开，后续的所有更新操作都在这个事务中，直到提交后才会对物理表产生影响) start transaction;– 设置保存点(在当前位置设置保存点，通过rollback to 保存点，可以回滚到该位置) savepoint p1;– 回滚保存点（回滚到指定的保存点：一旦回滚，则当前保存点会撤销） rollback to p1;– 回滚到事务开始的位置rollback;– 提交事务commit;

7.实例

在MySQL中，如果不更改其自动提交变量，则系统会自动向数据库提交结果，用户在执行数据库操作过 程中，不需要使用START TRANSACTION语句开始事务，应用COMMIT或者ROLLBACK提交事务或执行 回滚操作。

关闭mysql的默认提交 set autocommit=0;

十二. 事务四大特性之一隔离性(isolation)

由于实际的业务操作可能会涉及到很多事务的并发操作，因此在事务并发时可能会遇到以下问题：

1.丢失更新

撤消一个事务时，把其它事务已提交的更新的数据覆盖了。

2.脏读

事务A读取了事务B更新的数据，而此时事务B并未提交，那么A读取到的数据是脏数据

脏数据

3.不可重复读

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新 并提交，导致事务A多次读取同一数据时，结果 不一致。

4.幻读

系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有 改过来，就好像发生了幻觉一样，这就叫幻读。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zbPVQVPl-1607770888503)(C:\Users\24140\AppData\Roaming\Typora\typora-user-images\image-20201206180729081.png)]

十三. 数据库可编程性介绍

概述

在之前学习到所有有关数据库操作几乎都是一行命令解决问题，这些命令大多都是sql标准语法；但是不同的数据库管理系统对sql语句都添加了扩展支持，允许通过一些常见数据类型，运算符，分支语句，循环语句等实现一些类似常规编程语言的操作，这个概念称之为数据库的可编程性；对于不同的数据库产品对于可编程的命名也存在差异：

MSSQLServer 的可编程性称之为T-SQLOracle中的可编程性称之为PL/SQL

数据库可编程性分类

存储过程(procedure)触发器(trigger)

1.存储过程（Procedure）

存储过程(Procedure),是数据库操作语言SQL的可编程性实现，传统的SQL语句通常是在数据库服务器中执行一条命令，命令在数据库引擎内部需要经过创建与分析的过程，因此在运行时间上开销较大；而存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划，这样，后期的使用只需通过命令调用即可，因此，在执行过程时便可节省此开销。

实例

--最基础简单存储过程    create procedure testP1 (a varchar(10),b varchar(30))    BEGIN        if a is null then            set a = "男";            end if;            SELECT * from emp where sex = a;        end        call testP1(null,"1");    -- 使用零时变量的存储过程    create procedure emp1 ($ename varchar(30))    BEGIN        DECLARE $job varchar(30);        SELECT job into $job from emp where ename = $ename;        if $job is null then            set $job = "普通员工";            update emp set job = $job where ename = $ename;            SELECT * from emp where ename = $ename;            end if;        end        call emp1("沙和尚");        DROP procedure emp1;    -- 查询指定部门名的员工信息？如何使用存储过程实现    create procedure sp_emp3($dname varchar(30))    begin      -- 声明临时变量        declare $dno int;        -- 根据指定的部门名称查询到部门号并赋值到临时变量中        select * from emp where dno in (select dno from dept where dname=$dname);    end    call sp_emp3("研发部");    -- 如何利用存储过程实现一个分页操作：输入一个每页数据行数和页码数，显示该页数据    create procedure sp_page(pagesize int,pagenum int)    begin        -- 声明临时变量        declare startNum int;        -- 对变量赋值操作        set startNum = (pagenum - 1) * pagesize;        select * from emp limit startNum,pagesize;    end    call sp_page(3,3)    -- 删除存储过程    drop procedure sp_page;

优劣分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CicsXgfJ-1607770888505)(C:\Users\24140\AppData\Roaming\Typora\typora-user-images\image-20201206183730559.png)]

2.触发器（TRIGGER）

触发器（TRIGGER）是MySQL的数据库对象之一，对表中数据进行删除，更新和插入时做的一个监听，触发器就自动启动，并做一些后续处理

列子：

对用户信息的更新删除插入等一些操作的时候，对该动作捕获，将一些关键性信息保持到一个指定的位置，并记录下来

从5.0.2版本开始支持。该对象与编程语言中的存储过程以及函数非常类似，都是SQL语句可编程性的实现，并且都需要编写、编译以及调用。但是触发器的执行不是由程序调用，也不是由手动启动，而是由事件来触发、激活以及执行。

案例

-- 触发器创建    -- 当对emp表做更新操作时，将操作记录保存在日志表中    --当触发器启动时，将部分信息保存到日志表中    create TRIGGER emp_trigger BEFORE insert ON emp for each row    BEGIN            insert into logs (id,createTime,exquet) values (null,now(),"inset");            end;    -- 操作emp表，对emp表中使用更新    update emp set ename = "天蓬元帅" where eno=11;    -- 将数据插入到复制表中    create trigger emp_trigger2 after update on emp for each row    BEGIN        INSERT into empcopy (eno,ename,job,hiredate,age,sex,sal,dno) values (old.eno,old.ename,old.job,old.hiredate,old.age,old.sex,old.sal,old.dno);    end;    --当员工表的dno和部门表dno是主外建关系，那么当删除部门表信息时，就需要先将员工表中该部门的dno更新为null，才可以删除部门信息    create trigger emp_trigger2 BEFORE delete on dept for each row    BEGIN        UPDATE emp set dno = null where dno = old.dno;    end;

触发器的应用：

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

触发器类型new和old的使用

INSERT型触发器没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据

UPDATE型触发器既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据

DELETE型触发器没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据

–BEFORE 和old old使用的没更新之前的数据源 –BEFORE 和new new使用的是更新之后的数据源 –after 和old old使用的没更新之前的数据源 –after 和new new使用的是更新之后的数据源

mysql 优化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6l8geBEl-1607770888507)(C:\Users\24140\AppData\Roaming\Typora\typora-user-images\image-20201206184327859.png)]

十四. 数据库设计

目前所有的主流DBMS都是关系型数据库。通过二维表表示数据本身，另外表中存在一些关联列实现表和表之间的关系。

表之间关系

一对一（人对应一个身份证）一对多/多对一（一个部门包含多个员工）多对多（学生选课，用户和商品）

一对一

一张表中的一条数据，对应另外一张表中的一条数据，而且这些数据在表里都是唯一存在的

实际开发中一对一的关系并不常见，大多数时候一对一的关系其实可以建立成为一张表；如果需要建立一对一的关系，实现方式有两种：

唯一外键关联(在其中一张表建立外键，同时设置外键列唯一unique)

主键关联（两个表的主键列一致）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1V3ouVd5-1607770888508)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596164167153.png)]

一对多/多对一

一对多：一张表的一条数据，可以对应到另外一张表的很多条数据

一对多/多对一关系在开发中十分常见，大多数时候表之间的关系都是一对多/多对一的，比如：员工和部门，学生和班级，老师和学生，商品类别和商品，实现方式：

在多的一方表中新增外键列，关联一的一方的主键列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XZuHDtal-1607770888509)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596164769379.png)]

多对多

多对多的关系在开发中也是很常见的，比如说：学生和课程关系，用户和商品的，实现关联的方式如下：

通过第三张表维护两个表的关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-29ae7US8-1607770888510)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596165443776.png)]

数据库设计范式

在进行数据库设计的时候需要满足的一些规范形式，称之为数据库范式，满足范式的数据库设计是合理的，数据冗余小的，并且不会引起数据更新的异常。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xQSfGGpf-1607770888511)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1607003203211.png)]

第一范式

要求建立的数据库表中所有的列是原子的，每一列不可再拆分；目前的关系型数据库默认都是满足第一范式（不可能创建出不满足第一范式的数据表）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i5JdTDfF-1607770888512)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596166392271.png)]

第一范式：列不可再分

保证每列的原子性，原子性的意思就是每列不可以再次被拆分，并保持独立且唯一

第二范式：表中的每一列都必须完全依赖主键

可以去除部分的数据冗余

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6jDHsvyl-1607770888513)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596167334552.png)]

第三范式: 需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xnp04Yso-1607770888514)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596167846781.png)]

范式总结

在实际开发中，一般情况只要满足三大范式即可；另外，由于程序对查询的需求(处于便捷性考虑)可能会出现违背三大范式的情况；因此三大范式只是设计数据时候的一种参考，并不是定律。

范式的存在主要解决了：

数据冗余更新(insert,delete)操作异常

数据库设计案例分析

概念模型设计（ER图）

E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TdyccUAa-1607770888515)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596370648058.png)]

实体(表)，关系理解为表之间的联系；在数据库设计阶段，实体关系图的建立位于概念模型设计阶段，这一阶段主要用于进行实体之间的关系建立

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ma3ltA2w-1607770888516)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596177151438.png)]

物理模型设计（建表)

数据库设计一般使用一些专业设计工具，其中最常见以sybase(SAP)的数据库建模工具 PowerDesigner最为常见，还有一些其他数据库图形工具，比如navcat的模型

常见，大多数时候表之间的关系都是一对多/多对一的，比如：员工和部门，学生和班级，老师和学生，商品类别和商品，实现方式：

在多的一方表中新增外键列，关联一的一方的主键列

[外链图片转存中…(img-XZuHDtal-1607770888509)]

多对多

多对多的关系在开发中也是很常见的，比如说：学生和课程关系，用户和商品的，实现关联的方式如下：

通过第三张表维护两个表的关系

[外链图片转存中…(img-29ae7US8-1607770888510)]

数据库设计范式

在进行数据库设计的时候需要满足的一些规范形式，称之为数据库范式，满足范式的数据库设计是合理的，数据冗余小的，并且不会引起数据更新的异常。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

[外链图片转存中…(img-xQSfGGpf-1607770888511)]

第一范式

要求建立的数据库表中所有的列是原子的，每一列不可再拆分；目前的关系型数据库默认都是满足第一范式（不可能创建出不满足第一范式的数据表）

[外链图片转存中…(img-i5JdTDfF-1607770888512)]

第一范式：列不可再分

保证每列的原子性，原子性的意思就是每列不可以再次被拆分，并保持独立且唯一

第二范式：表中的每一列都必须完全依赖主键

可以去除部分的数据冗余

[外链图片转存中…(img-6jDHsvyl-1607770888513)]

第三范式: 需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

[外链图片转存中…(img-Xnp04Yso-1607770888514)]

范式总结

在实际开发中，一般情况只要满足三大范式即可；另外，由于程序对查询的需求(处于便捷性考虑)可能会出现违背三大范式的情况；因此三大范式只是设计数据时候的一种参考，并不是定律。

范式的存在主要解决了：

数据冗余更新(insert,delete)操作异常

数据库设计案例分析

概念模型设计（ER图）

E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型

[外链图片转存中…(img-TdyccUAa-1607770888515)]

实体(表)，关系理解为表之间的联系；在数据库设计阶段，实体关系图的建立位于概念模型设计阶段，这一阶段主要用于进行实体之间的关系建立

[外链图片转存中…(img-ma3ltA2w-1607770888516)]

物理模型设计（建表)

数据库设计一般使用一些专业设计工具，其中最常见以sybase(SAP)的数据库建模工具 PowerDesigner最为常见，还有一些其他数据库图形工具，比如navcat的模型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6tcHmsSw-1607770888517)(C:/Users/24140/Desktop/课件笔记/课件笔记/Mysql讲解/20201204------触发器，数据库设计/笔记/assets/1596183813747.png)]