msyql概念总结【数据类型, 函数】

数据类型

  • 常用数据类型如下：
    • 整数：int，bit
    • 小数：decimal
    • 字符串：varchar,char
    • 日期时间: date, time, datetime
    • 枚举类型(enum)
  • 特别说明的类型如下：
    • decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
    • char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '
    • varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'
    • 字符串text表示存储大文本，当字符大于4000时推荐使用
    • 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
  • 更全的数据类型可以参考http://blog.csdn.net/anxpp/article/details/51284106

约束

  • 主键primary key：物理上存储的顺序
  • 非空not null：此字段不允许填写空值
  • 惟一unique：此字段的值不允许重复
  • 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
  • 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制

数值类型(常用)

类型字节大小有符号范围(Signed)无符号范围(Unsigned)
TINYINT 1 -128 ~ 127 0 ~ 255
SAMLLINT 2 -32768 ~ 32767 0 ~ 65535
MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215
INT/INTEGER 4 -2147483648 ~2147483647 0 ~ 4294967295
BIGINT 8 -9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615

字符串

类型字节大小示例
CHAR 0-255 类型:char(3) 输入 'ab', 实际存储为'ab ', 输入'abcd' 实际存储为 'abc'
VARCHAR 0-255 类型:varchar(3) 输 'ab',实际存储为'ab', 输入'abcd',实际存储为'abc'
TEXT 0-65535 大文本

日期时间类型

类型字节大小示例
DATE 4 '2020-01-01'
TIME 3 '12:29:59'
DATETIME 8 '2020-01-01 12:29:59'
YEAR 1 '2017'
TIMESTAMP 4 '1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC

 

三范式

  • 第一范式（1NF)：数据库表的每一列都是不可分割的原子数据项，即列不可拆分（不可拆分，一个字段为最小单位）
  • 第二范式（2NF)：建立在第一范式的基础上，要求数据库表中的每个实例或记录必须可以被唯一地区分，即（唯一标识）
  • 第三范式（3NF)：建立在第二范式的基础上，任何非主属性不依赖于其它非主属性，即（引用主键）

视图

  • 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦
  • 解决：定义视图
  • 视图本质就是对查询的封装
  • 定义视图，建议以v_开头
create view 视图名称 as select语句;
  • 例：创建视图，查询学生对应的成绩信息
create view v_fun as (select g.name,c.cate_name from goods as g inner join goods_cates as c on g.cate_id=c.cate_id);
  • 查看视图：查看表会将所有的视图也列出来
show tables;
  • 删除视图
drop view 视图名称;
drop view v_fun;
  • 使用：视图的用途就是查询
select * from v_fun;

事务：

  • 事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

事务四大特性(简称ACID)

  • 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
  • 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
  • 隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
  • 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

事务命令

  • 要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
  • 查看表的创建语句，可以看到engine=innodb
show create table students;

  • 修改数据的命令会触发事务，包括insert、update、delete

  • 开启事务，命令如下：

    • 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
begin;
  • 提交事务，命令如下
    • 将缓存中的数据变更维护到物理表中
commit;
  • 回滚事务，命令如下：
    • 放弃缓存中变更的数据
rollback;

索引

  • 思考：在图书馆中是如何找到一本书的？
  • 一般的应用系统对比数据库的读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重
  • 当数据库中数据量很大时，查找数据会变得很慢
  • 优化方案：索引
  • 主键和唯一索引，都是索引，可以提高查询速度
    • 主键是数据物理存储的位置
    • 索引会单独创建一个目录，对应数据的位置
  • 索引分单列索引和组合索引
    • 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引
    • 组合索引，即一个索引包含多个列

语法

  • 查看索引
show index from 表名;
  • 创建索引
    • 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
    • 字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
  • 删除索引：
drop index 索引名称 on 表名;

缺点

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
  • 建立索引会占用磁盘空间的索引文件

示例

创建测试表testindex

create table test_index(title varchar(10));

向表中加入十万条数据

  • 创建存储过程proc_test，在存储过程中实现插入数据的操作
  • step1：定义分割符
delimiter //
  • step2：定义存储过程
create procedure proc_test()
begin
declare i int default 0;
while i
<
100000 do
insert into test_index(title) values(concat('test',i));
set i=i+1;
end while;
end //
  • step3：还原分割符
delimiter ;
  • 执行存储过程proc_test
call proc_test();

查询

  • 开启运行时间监测：
set profiling=1;
  • 查找第1万条数据test10000
select * from test_index where title='test10000';
  • 查看执行的时间：
show profiles;
  • 为表title_index的title列创建索引：
create index title_index on test_index(title(10));
  • 执行查询语句：
select * from test_index where title='test10000';
  • 再次查看执行的时间
show profiles;

函数：

创建

  • 语法如下
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter ;
  • 说明：delimiter用于设置分割符，默认为分号
  • 在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符

示例

  • 要求：创建函数py_trim，用于删除字符串左右两侧的空格

  • step1：设置分割符

delimiter $$
  • step2：创建函数
create function py_trim(str varchar(100)) returns varchar(100)
begin
declare x varchar(100);
set x=ltrim(rtrim(str));
return x;
end
$$
  • step3：还原分割符
delimiter ;

查看

  • 所有函数存储在mysql数据库下的proc表中
  • 说明：存储过程与函数都存储在proc表中，区别在type字段，func表中无数据
  • 查看python数据库中的函数
select name,type from mysql.proc where db='python';

调用

  • 语法如下
select 函数名称(参数列表);

示例

  • 要求：调用自定义函数py_trim
select py_trim(' a ');

删除

  • 说明：函数的代码无法修改，如果不能满足要求可以删除后重新创建
  • 语法如下
drop function 函数名称;

示例

  • 要求：删除函数py_trim
drop function py_trim;

　　

变量

临时变量

  • 声明临时变量，只能在 begin ... end 体重语法如下
declare 变量名 类型 default 默认值;
例：
declare x varchar(100);
  • 设置变量值，语法如下
set 变量名=值;
例：
set x='abc';
  • 使用变量：将变量写到表达式中，即可调用变量的值

用户变量/会话变量

  • 只有在当前连接用户有效,其他连接用户无法访问
  • 使用@标识声明用户变量
  • 使用 select 来查看用户变量
定义
-- set @变量名 = 值;
set @age = 20;
-- 查看用户变量
select @age;
-- 但是如果在 select 语句中这么使用就会有问题, 因为 '=' 在 select 语句中是判断是否相等
-- mysql 使用 := 在 select 语句中表示赋值
set @maxage = 0;
select @maxage := max(age) from students;

系统变量

  • 任何一个用户都可以访问
  • 使用 @@来标识系统变量
  • 使用 select 来查看系统变量
-- 查询所有的系统变量
show variables;
-- 查看用户变量
select @@max_connections;
-- 当前用户/会话中修改系统变量
set max_connections = 1000;
-- 全局修改,所有的访问用户都会生效
set global max_connection = 1000;

判断

  • 语法如下
if 条件1 then
语句1;
elseif 条件2 then
语句2;
else
语句
end if;　　

循环

  • 语法如下
while 条件 do
语句;
end while;
  • 退出循环：leave，相当于break
  • 退出本次循环：iterate，相当于continue

示例

  • 要求：定义函数odd，输出1-100间的偶数

  • step1：设置分割符

delimiter $$
  • step2：创建函数
create function odd () returns varchar(300)
begin
declare i int default 1;
declare x varchar(300) default '';
while i <=100 do
if i%2=0 then
set x=concat(x,' ',i);
end if;
set i=i+1;
end while;
return x;
end
$$
  • step3：还原分割符
delimiter ;
  • step4：调用函数
select odd();

存储过程

  • 存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合，可以视为批处理，但是其作用不仅仅局限于批处理
  • 本节主要介绍如何创建存储过程，以及如何调用、查看、修改、删除存储过程
  • 存储过程也可以调用其他存储过程
  • 推荐阅读：存储过程详解http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

创建

  • 语法如下
delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;
  • 说明：delimiter用于设置分割符，默认为分号
  • 在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符

示例

  • 要求：创建查询过程，查询学生信息
  • step1：设置分割符
delimiter //
  • step2：创建存储过程
create procedure proc_stu()
begin
select * from students;
end
//
  • step3：还原分割符
delimiter ;
  • 执行效果图如下

查看

  • 所有存储过程和函数，都存储在mysql数据库下的proc表中
  • 查看表结构
desc mysql.proc\G;
  • 主要字段说明：
    • name表示名称
    • type表示类型，为存储过程、函数
    • body表示正文脚本
    • db表示属于的数据库
  • 查看python数据库的所有存储过程
select name,type,body from mysql.proc where db='python';

调用

  • 语法如下
call 存储过程(参数列表);

示例

  • 要求：调用存储过程proc_stu
call proc_stu();

　　

删除

  • 语法如下
drop procedure 存储过程名称;
  • 说明：存储过程内部的sql语句无法修改，如果之前创建的存储过程不能满足要求，可以删除后重新创建

示例

  • 要求：删除存储过程proc_stu
drop procedure proc_stu;

存储过程和函数的区别

相同点

  • 存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.
  • 存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql, 不需要重复编译
  • 减少网络交互,减少网络访问流量

不同点

  • 标识符不同,函数的标识符是 function, 过程:procedure
  • 函数中有返回值,且必须有返回值,而过程没有返回值,但是可以通过设置参数类型(in,out)来实现多个参数或者返回值
  • 函数使用 select 调用,存储过程需要使用 call 调用
  • select 语句可以在存储过中调用,但是除了 select ... into 之外的 select 语句都不能再函数中调用
  • 通过 in out 参数,过程相关函数更加灵活,可以返回多个结果
  • 在实际开发中根据个人喜好选择使用函数或者存储过程

MySQL 引擎http://blog.csdn.net/gaohuanjie/article/details/50944782

锁相关:http://blog.csdn.net/spring_model/article/details/54021122

数据库搜索和索引http://blog.csdn.net/hguisu/article/details/7786014

posted @ 2017-08-28 20:25  凯哥吧  阅读(190)  评论(0)  编辑  收藏  举报