MySQL速查笔记（二）

列属性与数据完整性

1 - 数据类型

1.1 数据类型——值类型

1.1.1 整形

| 类型 | 字节 | 范围 | | --------- | ---- | ---------------- | | tinyint | 1 | -128~127 | | smallint | 2 | -32768~32767 | | mediumint | 3 | -8388608~8388607 | | int | 4 | -231~231-1 | | bigint | 8 | -263~263-1 |

注意：

  1. 无符号整数（unsigned）没有负数，整数部分是有符号的两倍
  2. 整型支持显示宽度（最小显示位数），比如int(5)，如果数值的位数小于 5 位，前面加上前导 0.（结合zerofill才起作用）

1.1.2 浮点型（保存近似值小数)

| 类型 | 字节 | 范围 | | ------ | ---- | ------------------ | | float | 4 | -3.4E+38~3.4E+38 | | double | 8 | -1.8E+308~1.8E+308 |

注意：

  1. 浮点数声明：float(M,D) double(M,D)
    • M：总位数
    • D：小数位数
  2. 浮点的精度可能会丢失

1.1.3 定点数 语法：decimal(M, D) 注意：

  1. 定点数是变长的，大致每 9 个数字用 4 个字节来存储。定点数之所以能保存精确的小数，是因为整数和小数是分开存储的，占用的资源比浮点数要多。
  2. 定点数和浮点数都支持显示宽度和无符号数。

1.2 数据类型——字符型

| 数据类型 | 字节 | 长度 | | ------------- | -------- | ---------- | | char(长度) | 定长 | 最长 255 | | varchar(长度) | 变长 | 最大 65535 | | tinytext | 大段文本 | 255 | | text | 大段文本 | 65535 | | mediumtext | 大段文本 | 224-1 | | longtext | 大段文本 | 232-1 |

注意：

  1. char(10)和varchar(10)的区别？

    相同点：它们最多只能保存 10 个字符； 不同点：char 不回收多余的字符，varchar 会回收多余的字符；char 效率高，浪费空间，varchar 节省空间，效率比 char 低。

  2. char 最大长度 255

  3. varchar 理论长度 65535

1.3 数据类型——枚举（enum）

特点：

  1. MySQL 的枚举类型是通过整数来管理的，第一个值是 1，第二个值是 2，以此类推；
  2. 枚举类型可以直接插入数字

优点：

  1. 运行速度快（数字比字符串运算快）
  2. 限制数据，保证数据完整性
  3. 节省空间

1.4 数据类型——集合（set）

  1. 集合中插入顺序不一样，但是显示的顺序都是一样的
  2. 不能插入集合中没有的数据
  3. 集合的每个元素都被分配一个固定数字，从左往右按 20, 21,22...排列

1.5 数据类型——日期时间

| 数据类型 | 字节 | | --------- | ----------------------- | | datetime | 日期时间，占用 8 个字节 | | date | 日期，占用三个字节 | | time | 时间，占用 3 个字节 | | timestamp | 时间戳，占用四个字节 | | yaer | 年份 占用 1 个字节 |

1.6 数据类型——boolean

true 和 false 在数据库中对应 1 和 0

数据库设计

1 - 数据库基本概念

  1. 关系：两个表的公共字段
  2. 行：也称记录，也称实体
  3. 列：也称字段，也称属性
  4. 数据冗余：相同的数据存储在不同的地方
  5. 数据完整性：正确性 + 准确性 = 数据完整性
    • 正确性：数据类型正确
    • 准确性：数据范围要准确

2 - 实体和实体之间的关系

2.1 一对一

  1. 主表中的一条记录对应从表中的一条记录
  2. 实现：主键和主键建立关系
  3. 问题：一对一两个表完全可以用一个表实现，为什么要分成两个表？
    • 在字段数量很多的情况下，每次查询需要检索大量数据，这样效率低下。所以可以将所有字段分为“常用字段”和“不常用字段”，这样对大部分查询者来说效率提高了。【表的垂直分割】

2.2 一对多

  1. 主表中的一条记录对应的从表中的多条记录
  2. 实现：主键和非主键建立关系

2.3 多对多

  1. 主表中的一条记录对应从表中的多条记录，从表中的一条记录对应主表中的多条记录
  2. 实现：建立第三张表来保存关系

3 - 数据库设计的步骤

3.1 数据库设计具体步骤

  1. 收集信息：与该系统有关人员进行交流、座谈，充分理解数据库需要完成的任务
  2. 标识对象（实体 - Entity）标识数据库要管理的关键对象或实体
  3. 标识每个实体的属性（Attribute）
  4. 标识对象之间的关系（Relationship）
  5. 将模型转换成数据库
  6. 规范化

3.2 绘制 E-R 图（实体关系图）

E-R 图的语法

| 符合 | 含义 | | ------ | ---------------- | | 矩形 | 实体，一般是名词 | | 椭圆形 | 属性，一般是名词 | | 菱形 | 关系，一般是动词 |

3.3 将 E-R 图转成表

4 - 数据规范化

实践证明，三范式是性价比最高的。

4.1 第一范式：确保每列原子性

如果每列都是不可再分的最小数据单元（也称为最小的原子单元），那么满足第一范式。 注意：地址包含省、市、县、地区是否需要拆分？

4.2 第二范式：非键字段必须依赖于键字段

如果一个关系满足第一范式，并且除了主键意外的其他列，都依赖于该主键，则满足第二范式。 注意：第二范式要求每个表只描述一件事情

4.3 第三范式：消除传递依赖

如果一个关系满足第二范式，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式。 注意：传递依赖不能存在于非键字段中

视图

  1. 视图是一张虚拟表，他表示一张表的部分或多张表的综合的结构
  2. 视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。

1 创建视图

语法：

create [or replace] view 视图的名称 as select语句

注意：因为视图是一个表结构，所以创建视图后，会在数据库文件夹中创建一个视图名的 frm 文件

2 查看视图的结构

desc 视图名;

3 查看创建视图的语法

show create view 视图名\G;

4 显示所有视图

show tables;

select table_name from information_schema.views;

show table status where comment='view'\G;

5 更改视图

alter view 视图名 as select语句;

6 删除视图

drop view [if exists] 视图1,视图2...;

7 视图的作用

  1. 筛选数据，防止未经许可访问敏感数据
  2. 隐藏表结构
  3. 降低 SQL 语句的复杂度

8 视图的算法

场景：找出语文成绩最高的男生和女生

select * from (select * from stu order by ch desc)as t group by stusex;

我们可以将子查询封装到视图中：

create view vw_stu as select * from stu order by ch desc;

通过视图来查询：

select * from vm_stu group by stusex;

但是结果和上面直接查询的结果不一样，这是因为视图的算法造成的：

  1. merge：合并算法，将视图的语句和外层的语句合并后在执行。
  2. temptable：临时表算法，将视图生成一个临时表，再执行外层语句。
  3. undefined：未定义，MySQL 到底用哪种算法由 MySQL 决定，一般视图会自动使用 merge 算法，因为效率更高。

**解决：**在创建视图的时候指定算法

create algorithm=temptable view 视图名 as select语句;

事务【transaction】

  1. 事务是一个不可分割的执行单元
  2. 事务作为一个整体要么一起执行，要么一起回滚

1 事务操作

# 开启事务
start transaction 或者 begin [work]
# 提交事务
commit
# 回滚事务
rollback

例子：银行转帐

start transaction
update bank set money=money-100 where cardid='1001'
update bank set money=money+100 where cardid='1002';
# 如果有报错：
rollback;
# 如果成功：
commit;

注意：

  1. 事务在开启的时候产生，提交事务或回滚事务都结束；
  2. 只有 innodb 和 BDB 才支持事务，myisam 不支持事务；

2 设置事务的回滚点

语法：

# 设置回滚点
savepoint 回滚点名
# 回滚到回滚点
rollback to 回滚点

2.3 事务的特性（ACID）

  1. 原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行；
  2. 一致性（Consistency）：事务完成时，数据必须处于一致的状态；
  3. 隔离性（Isolation）：每个事务都是相互隔离的；
  4. 永久性（Durability）：事务完成后，对数据的修改是永久性的；

索引

优点：查询速度快 缺点：

  1. 数据操作语句效率降低
  2. 占用空间

1 索引的类型

  1. 普通索引
  2. 唯一索引（唯一键）
  3. 主键索引：只要创建主键就自动创建主键索引，不需要手动创建
  4. 全文索引，搜索引擎使用，MySQL 不支持中文的全文索引，我们通过 sphinx 解决中文的全文索引

2 创建普通索引

# 创建索引方法一
create index 索引名 on 表名(字段名);
# 创建索引方法二
alter table 表名 add index [索引的名称](列名);
# 创建表的时候就添加索引
create table emp(
	id int,
    name varchar(10),
    index ix_name(name) # 创建索引
);

3 创建唯一索引

语法一：create unique index 索引名 on 表名(字段名); 语法二：alter table 表名 add unique [index] [索引的名称](列名); 语法三：创建表的时候添加唯一索引，和创建唯一键是一样的

4 删除索引

drop index 索引名 on 表名;

5 创建索引的指导原则

  1. 该列用于频繁搜索；
  2. 该列用于排序；
  3. 公共字段要创建索引；
  4. 如果表中的数据很少，不需要创建索引。MySQL 搜索索引的时间比逐条搜索数据的时间要长；
  5. 如果一个字段上的数据只有一个不同的值，该字段不适合做索引，比如性别；

内置函数

1 数字类

# 生成随机数
select rand();
select * from stu order by rand() limit 2; # 随机抽取两个学生

# 取整（四舍五入，向上，向下）
select round(数字);
select ceil(数字);
select floor(数字);

# 截取数字
select truncate(数字，截取小数位数);

2 字符串类

# 转成大写/小写
select ucase/lcase(字符串);

# 截取字符串
select left/right(字符串,位数);
select substring(字符串，开始位数【从1开始】，位数);

# 字符串拼接
select concat(字符串...);

# 字符串替换
coalesce(字段1，字段2); # 如果字段不为空就显示字段1，否则显示字段2
select stuname,coalesce(writtenexam.'缺考') from stuinfo natural left join stumarks; # 如果笔试成绩为空就显示‘缺考’

# 字符串长度
select length(字符串); # 字节长度
select char_length(字符串); # 字符个数

3 时间类

# 获取当前的时间戳
select unix_timestamp();

# 时间戳转换为时间格式
select from_unixtime(unix_timestamp());

# 获取当前时间
select now();

# 日期相减
select datediff(now(),'2008-8-8') # 当前日期距离2008-08-08共多少天

# 转换格式
select cast(now() as time)
select convert(now(),time)

4 加密函数

select md5(字符串);
select sha(字符串);

5 判断函数

select stuname,if(ch>=60 && math>=60,'通过','不通过') '是否通过' from stu;

预处理

预编译一次，可以多次执行。用来解决一条 SQL 语句频繁执行的问题

# 预处理语句
prepare 预处理名字 from 预处理语句;

# 执行预处理
execute 预处理名字 [using 变量];

# MySQL中声明变量
set @id=值

存储过程【procedure】

1 存储过程的优点

  1. 存储过程可以减少网络流量
  2. 允许模块化设计
  3. 支持事务

2 创建存储过程

create procedure 存储过程名(参数)
	begin
	sql...
	end;

3 调用存储过程

call 存储过程名

4 删除存储过程

drop procedure [if exists] 存储过程名

5 查看存储过程的信息

show create procedure 存储过程名\G

6 显示所有的存储过程

show procedure status\G

7 存储过程的参数

存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout） 存储过程不能使用 return 返回值，要返回值只能通过“输出参数”来向外传递值。 例 1：输入参数

create procedure proc(in param varchar(10))
select * from stuinfo where stuno=param;

call proc('s25301');

例 2：查找同桌

create procedure proc(in name varchar(10))
begin
declare seat tinyint
select stuseat into seat from stuinfo where stuname=name
select * from stuinfo where stuseat=seat+1 or stuseat=seat-1
end;

总结：

  1. 通过 declare 关键字声明局部变量；全局变量@开头就可以了
  2. 给变量赋值有两种方法 方法一：set 变量名=值 方法二：select 字段 into 变量 from 表 where 条件
  3. 声明的变量不能与列名同名

例三：输出参数

create procedure proc(num int, out result int)
begin
set result=num*num
end;

call proc(10.@result);

select @result;

例四：输入输出参数

create procedure proc(inout num int)
begin
set num=num*num
end;

set @num=10

call proc(@num);

select @num;
相关文章