Mysql 基础知识简单总结

字段类型

int(5) 和 int(10) 的区别

可点击博客 程序猿杂货铺 int(5) 与int(11)后的括号中的字符表示显示宽度,整数列的显示宽度与 MySQL 需要用多少个字符来显示该列数值,与该整数需要的存储空间的大小都没有关系, int类型的字段能存储的数据上限依旧是2147483647(有符号型)和4294967295(无符号型)。

char 和 varchar 的区别

可点击博客 A_aliane 1.两者存储大小不同:char 258 varchar65535 2.char为定长,varchar变长,char效率更高,时间换空间 3.char保留两边空格,varchar会去掉空格

例子 人的年龄适合使用tinyint类型 乌龟的年龄使用smallint类型 数据表主键id值在没有超过1600万的时候,就可以使用mediumint类型 手机号码存储:char(11) 存储邮箱:速度快char(40) 、 空间节省 varchar(40) 集合:set(‘篮球’,’排球’,’足球’,’棒球’) 枚举:enum(‘男’,’女’,’保密’)

存储引擎

概念:数据表存储数据的一种格式。 常见存储引擎的区别 可点击博客 m2ez

MyISAM 与 InnoDB

属性/存储 MyISAM InnoDB
存储结构 每张表被存放在三个文件:frm-格定义 MYD(MYData)-数据文件 MYI(MYIndex)-索引文件 所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB
存储空间 MyISAM可被压缩,存储空间较小 InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
存储方式 不排顺序 ,按写入存储 数据的写入顺序 与 存储的顺序不一致,按照主键的顺序把记录摆放到对应的位置上去,速度比Myisam的要稍慢。
可移植性、备份及恢复 由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了
事务安全 不支持 每次查询具有原子性 支持 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表
AUTO_INCREMEN MyISAM表可以和其他字段一起建立联合索引 InnoDB中必须包含只有该字段的索引
SELECT MyISAM查询更优
INSERT InnoDB更优
UPDATE InnoDB更优
DELETE InnoDB更优 它不会重新建立表,而是一行一行的删除
COUNT without WHERE MyISAM更优。因为MyISAM保存了表的具体行数 InnoDB没有保存表的具体行数,需要逐行扫描统计,就很慢了
COUNT with WHERE 一样 一样,InnoDB也会锁表
只支持表锁/并发性低 支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的
外键 不支持 支持
FULLTEXT全文索引 支持 不支持(5.6.4以上支持英文全文索引) 可以通过使用Sphinx从InnoDB中获得全文索引,会慢一点

悲观锁 与乐观锁

可点击博客 jopen

悲观锁 乐观锁
概念 正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度(悲观),因此,在整个数据处理过程中,将数据处于锁定状态。 悲观锁的实现,往往依靠数据库提供的锁机制 (也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据) 乐观并发控制(又名“乐观锁”,Optimistic Concurrency Control,缩写“OCC”)是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,正在提交的事务会进行回滚。乐观事务控制最早是由孔祥重(H.T.Kung)教授提出
MySQL InnoDB中使用 四步 0. 开始事务begin;/begin work;/start transaction; (三者选一就可以) 1.查询出商品信息select status from t_goods where id=1 for update; 2.根据商品信息生成订单insert into t_orders (id,goods_id) values(null,1); 3.修改商品status为2update t_goods set status=2; 4.提交事务commit;/commit work;
优点与不足 悲观并发控制实际上是“先取锁再访问”的保守策略,为数据处理的安全提供了保证。但是在效率方面,处理加锁的机制会让数据库产生额外的开销,还有增加产生死锁的机会;另外,在只读型事务处理中由于不会产生冲突,也没必要使用锁,这样做只能增加系统负载;还有会降低了并行性,一个事务如果锁定了某行数据,其他事务就必须等待该事务处理完才可以处理那行数 乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的,因此尽可能直接做下去,直到提交的时候才去锁定,所以不会产生任何锁和死锁。但如果直接简单这么做,还是有可能会遇到不可预期的结果,例如两个事务都读取了数据库的某一行,经过修改以后写回数据库,这时就遇到了问题。

触发器

触发程序是与表有关的命名数据库对象,当该表出现特定事件时,将激活该对象。
监听:记录的增加、修改、删除。

创建触发器

  • CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt 参数: trigger_time是触发程序的动作时间。它可以是 before 或 after,以指明触发程序是在激活它的语句之前或之后触发。 trigger_event指明了激活触发程序的语句的类型
  • INSERT:将新行插入表时激活触发程序
  • UPDATE:更改某一行时激活触发程序
  • DELETE:从表中删除某一行时激活触发程序
  • tbl_name:监听的表,必须是永久性的表,不能将触发程序与TEMPORARY表或视图关联起来。
  • trigger_stmt:当触发程序激活时执行的语句。执行多个语句,可使用BEGIN...END复合语句结构

删除

  • DROP TRIGGER [schema_name.]trigger_name
  • 可以使用old和new代替旧的和新的数据
  • 更新操作,更新前是old,更新后是new.
  • 删除操作,只有old.
  • 增加操作,只有new.

注意

对于具有相同触发程序动作时间和事件的给定表,不能有两个触发程序。 字符连接函数 concat(str1,str2,...]) concat_ws(separator,str1,str2,...)

语法

 分支语句
if 条件 then
执行语句
elseif 条件 then
执行语句
else
执行语句
end if;

 修改最外层语句结束符
delimiter 自定义结束符号
SQL语句
自定义结束符号
delimiter ;      修改回原来的分号

 语句块包裹
begin
语句块
end

特殊的执行

  1. 只要添加记录,就会触发程序。
  2. Insert into on duplicate key update 语法会触发: 如果没有重复记录,会触发 before insert, after insert; 如果有重复记录并更新,会触发 before insert, before update, after update; 如果有重复记录但是没有发生更新,则触发 before insert, before update
  3. Replace 语法 如果有记录,则执行 before insert, before delete, after delete, after insert

SQL编程

变量

变量声明
declare var_name[,...] type [default value]
这个语句被用来声明局部变量。要给变量提供一个默认值,请包含一个default子句。值可以被指定为一个表达式,不需要为一个常数。如果没有default子句,初始值为null。
 赋值
使用 set 和 select into 语句为变量赋值。
- 注意:在函数内是可以使用全局变量(用户自定义的变量)

// 全局变量 
 定义、赋值
set 语句可以定义并为变量赋值。
set @var = value;
也可以使用select into语句为变量初始化并赋值。这样要求select语句只能返回一行,但是可以是多个字段,就意味着同时为多个变量进行赋值,变量的数量需要与查询的列数一致。
还可以把赋值语句看作一个表达式,通过select执行完成。此时为了避免=被当作关系运算符看待,使用:=代替。(set语句可以使用= 和 :=)。
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into 可以将表中查询获得的数据赋给变量。
-| select max(height) into @max_height from tb;

 自定义变量名
为了避免select语句中,用户自定义的变量与系统标识符(通常是字段名)冲突,用户自定义变量在变量名前使用@作为开始符号。
@var=10;
- 变量被定义后,在整个会话周期都有效(登录到退出)

控制结构

if语句
if search_condition then
statement_list
[elseif search_condition then
statement_list]
...
[else
statement_list]
end if;
 case语句
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
 while循环
[begin_label:] while search_condition do
statement_list
end while [end_label];
- 如果需要在循环内提前终止 while循环,则需要使用标签;标签需要成对出现。
 退出循环
退出整个循环 leave
退出当前循环 iterate
通过退出的标签决定退出哪个循环

函数

 数值函数
abs(x)           绝对值 abs(-10.9) = 10
format(x, d)     格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x)          向上取整 ceil(10.1) = 11
floor(x)         向下取整 floor (10.1) = 10
round(x)         四舍五入去整
mod(m, n)        m%n m mod n 求余 10%3=1
pi()             获得圆周率
pow(m, n)        m^n
sqrt(x)          算术平方根
rand()           随机数
truncate(x, d)   截取d位小数

 时间日期函数
now(), current_timestamp();      当前日期时间
current_date();                  当前日期
current_time();                  当前时间
date('yyyy-mm-dd hh:ii:ss');     获取日期部分
time('yyyy-mm-dd hh:ii:ss');     获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');  格式化时间
unix_timestamp();                获得unix时间戳
from_unixtime();                 从时间戳获得时间

 字符串函数
length(string)           string长度,字节
char_length(string)      string的字符个数
substring(str, position [,length])       从str的position开始,取length个字符
replace(str ,search_str ,replace_str)    在str中用replace_str替换search_str
instr(string ,substring)     返回substring首次在string中出现的位置
concat(string [,...])    连接字串
charset(str)             返回字串字符集
lcase(string)            转换成小写
left(string, length)     从string2中的左边起取length个字符
load_file(file_name)     从文件读取内容
locate(substring, string [,start_position])  同instr,但可指定开始位置
lpad(string, length, pad)    重复用pad加在string开头,直到字串长度为length
ltrim(string)            去除前端空格
repeat(string, count)    重复count次
rpad(string, length, pad)   在str后用pad补充,直到长度为length
rtrim(string)            去除后端空格
strcmp(string1 ,string2)     逐字符比较两字串大小

 流程函数
case when [condition] then result [when [condition] then result ...] [else result] end   多分支
if(expr1,expr2,expr3)  双分支。

 聚合函数
count();  返回指定列中非NULL值的个数
sum();    返回指定列的所有值之和 
max();    返回指定列的最大值
min();    返回指定列的最小值
avg();    返回指定列的平均值
group_concat();   返回由属于一组的列值连接组合而成的结果

其他常用函数
md5();
default();

存储函数,自定义函数

新建
CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型
函数体
- 函数名,应该合法的标识符,并且不应该与已有的关键字冲突。
- 一个函数应该属于某个数据库,可以使用db_name.funciton_name的形式执行当前函数所属数据库,否则为当前数据库。
- 参数部分,由"参数名"和"参数类型"组成。多个参数用逗号隔开。
- 函数体由多条可用的mysql语句,流程控制,变量声明等语句构成。
- 多条语句应该使用 begin...end 语句块包含。
- 一定要有 return 返回值语句。

 删除
DROP FUNCTION [IF EXISTS] function_name;

 查看
SHOW FUNCTION STATUS LIKE 'partten'
SHOW CREATE FUNCTION function_name;

 修改
ALTER FUNCTION function_name 函数选项

存储过程

存储存储过程 是一段代码(过程),存储在数据库中的sql组成。 一个存储过程通常用于完成一段业务逻辑,例如报名,交班费,订单入库等。 而一个函数通常专注与某个功能,视为其他程序服务的,需要在其他语句中调用函数才可以,而存储过程不能被其他调用,是自己执行 通过call执行。 实例 可点击博客 Crazygolf

创建
CREATE PROCEDURE sp_name (参数列表)
过程体
参数列表:不同于函数的参数列表,需要指明参数类型
IN,表示输入型
OUT,表示输出型
INOUT,表示混合型
注意,没有返回值。

/* 存储过程 */ 
存储过程是一段可执行性代码的集合。相比函数,更偏向于业务逻辑。
调用:CALL 过程名
 注意
- 没有返回值。
- 只能单独调用,不可夹杂在其他语句中

 参数
IN|OUT|INOUT 参数名 数据类型
IN      输入:在调用过程中,将数据输入到过程体内部的参数
OUT     输出:在调用过程中,将过程体处理完的结果返回到客户端
INOUT   输入输出:既可输入,也可输出

 语法
CREATE PROCEDURE 过程名 (参数列表)
BEGIN
过程体
END

各位帅气的博主

彼特城博客
请先登录后发表评论
  • latest comments
  • 总共0条评论