MySql深入了解----数据类型常用函数

数据类型

1. 字符串类型

(1)char—定长字符串，最大长度为256个字节

(2)varchar—可变长字符串，最大长度为256个字节 对应Java中的String 常用

(3)tiny text—同varchar，最大长度为256个字节

(4)text—可变长字符串，最大长度为64KB

(5)mediumtext—可变长字符串，最大长度为16KB

(6)longtext—可变长字符串，最大长度为4GB

2. 整数类型

(1)int或integer—4个字节 对应Java中的的int 常用

(2)tinyint—1个字节 对应Java中的byte

(3)smallint—2个字节 对应Java中的short

(4)mediumint—3个字节

(5)bigint—8个字节 对应Java中的long

3. 小数类型

(1)float—4个字节 对应Java中的float

(2)double—8个字节 对应Java中的double

(3)decimal(m, n) m指定整数的位数，n指定小数的位数

4. 日期时间类型

(1)date—日期 对应Java.sql中的Date

(2)time—时间 对应Java.sql中的Time

(3)datetime—日期时间 对应Java.util中的的Date 常用

(4)timestamp—时间戳 对应Java.sql中的Timestamp

5. 二进制类型

(1)blob—Binary Large Object—最大长度为64KB

(2)tinyblob—最大长度为256B

(3)mediumblob—最大长度为16MB

(4)longblob------最大长度为4GB

常用函数

1. 字符串类函数

(1)concat(s1, s2, s3, …) //连接字符串

(2)lower(s) //将字符串全部变成小写

(3)upper(s) //将字符串全部变成大写

(4)ltrim(s) //去除字符串左侧的空格

(5)rtrim(s) //去除字符串右侧的空格

(6)trim(s) //去除字符串左右两侧的空格

(7)lpad(s, len, pad) //用字符串pad来对字符串s的左侧进行填充，直至长度达到len

(8)rpad(s, len, pad) //用字符串pad来对字符串s的右侧进行填充，直至长度达到len

(9)repeat(s, n) //将字符串s重复n次

(10)replace(s, source, dest) //将字符串中包含source的字符替换成dest

(11)strcmp(s1, s2) //比较s1与s2，如果相同返回0，s2大于s1返回1，s2小于s1返回-1

(12)left(s, n) //返回字符串最左边的n个字符

(13)right(s, n) //返回字符串最右边的n个字符

(14)substr(s, begin, len) //返回从begin位置开始的len个字符，begin从1开始

(15)instr(s, target) //返回s中出现target子串的位置，从1开始，找不到则返回0

(16)insert(s, index, len, target) //从index位置开始的len个字符替换成target字符串

(17)length(s) //返回字符串的长度

(18)reverse(s) //返回字符串的逆序

2. 数学类函数

(1)ceil(n) //返回大于n的最大整数

(2)floor(n) //返回小于n的最大整数

(3)rand() //返回0到1之间的随机小数

(4)round(n, len) //将n保留len位小数，四舍五入

(5)truncate(n, len) //将n保留len位小数，直接截断

3. 日期时间类函数

(1)now()和sysdate() //返回当前日期时间 常用

(2)curdate() //返回当前日期

(3)curtime() //返回当前时间

(4)year/month/day/week/hour/minute/second(date) //返回日期中的年/月/日/周/时/分/秒

(5)date_format(date, pattern) //格式化输出日期，相当于SimpleDateFormat类

pattern参数的取值

%y 表示两位数字年份   %Y 表示四位数字年份
%m 表示两位数字月份  %M 表示月份的英文名称
%d 表示两位数字的天数
%H 表示两位数字的小时数，24小时制
%i 表示两位数字的分钟数
%s 表示两位数字的秒数

(7)datediff(date1, date2) //返回date1与date2相差的天数

(8)date_add(date, INTERVAL n type) //给日期date加上n个指定类型的值
type参数的取值—year/month/day/hour/minute/second

4. 流程控制类函数

(1)if(条件, 表达式1, 表达式2) //如果条件为true，返回表达式1，否则返回表达式2

(2)case
when 条件1 then 表达式1
when 条件2 then 表达式2
when 条件n then 表达式n
else 表达式n+1
end

(3)ifnull(表达式1,表达式2) //如果表达式1的值不为null，返回表达式1，否则返回表达式2 经常用于对空值进行处理

(4)isnull(表达式) //如果表达式的值为null，则返回1，否则返回0

表操作

表约束

1. primary key – 主键
2. auto_increment – 自增
3. not null – 非空
4. default – 默认值
5. foreign key(deptno) references dept(deptno) //表级外键约束
6. unique(empname) //表级唯一约束

删除表 drop truncate delete 的区别

1. drop删除表结构和表中所有数据，速度最快，自动提交
2. truncate删除表中所有数据，保留表结构，自动提交
3. delete from 表名[where 条件] 可以按行删除表内容，保留表结构，DML语句，记录到事务日志，支持回滚

修改和展示

• 添加字段 alter table 表名 add (字段名 类型 [not null] [default 默认值]) [After 指定列名]
• 删除字段 alter table 表名 drop (字段名)
• 修改字段 alter table 表名 modify (字段名 类型 [not null] [default 默认值])
• 查看数据库中所有表名 show tables
• 查看表结构 desc 表名
• 修改表名 rename table 原表名 to 新表名

数据约束

1. 主键约束 — primary key 主键 = 非空 + 唯一
   • 一张表只能有一个主键
   • 主键可以包含多个列，称为复合主键
   • 主键分为 自然主键和业务主键
   • 自然主键 – 自增长(auto_increment)主键
     -业务主键 – 根据业务规则和算法生成的主键
2. 外键约束 — foreign key
3. 非空约束—not null
   唯一约束—unique
   自定义检查约束—check(检查条件)

select操作

执行顺序

1. from 加载，连接来自不同数据表的数据
2. where 对记录行进行过滤筛选
3. group by 将数据进行分组
4. having 对分组进行过滤筛选，可以使用分组函数进行计算
5. select 提取列行，并计算所有表达式（计算列），可以使用分组函数进行计算
6. order by --排序
7. limit — 分页

重点语法

1. 空值的处理函数
   • ifnull(表达式1,表达式2) //如果表达式1的值不为null，返回表达式1，否则返回表达式2
   • isnull(表达式) //如果表达式的值为null，则返回1，否则返回0
2. 去重 关键字 DISTINCT
3. 分组函数不能嵌套使用，只能使用子查询实现
4. 每个派生表都必须有别名
5. 自连接时，一个表操作俩次，需要给表起别名，子查询用在from子句中，应该给临时表起别名
6. 为了方便在其他地方使用分组函数的结果，通常需要给分组函数起别名
7. where中选行条件的解析顺序是从右向左的，尽量把可能为假的放在右边
8. select子句和having子句中可以使用分组函数，where子句中不能使用分组函数
9. 分组函数中null值的处理
   • max/min/sum/avg 忽略列值为null的行
   • count分为俩种情况 count(*) 不忽略null 值的行 count(列名)忽略null值的行
10. group by 子句后，查询结果是每组一条记录，select子句中的字段要么是分组字段，要么是分组函数

多表连接

1. natural join 自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。
2. inner join 产生的是AB的交集
3. left join 产生的是A的完全集， B匹配则有值，不匹配则null值
4. right join 产生的是B的完全集，A匹配则有值，不匹配则null值
5. full join 产生的是AB的并集合 MySQL中不支持full join的写法，可以使用union联合两个查询结果
6. 自连接 自己与自己进行查询，需要给表起别名
   union和union all的区别

---

(1)union会自动去掉两个集合中重复的行，同时字段按照默认规则的排序
(2)union all只将两个集合中的数据合并，不会自动去重和排序

多行子查询中的null值处理

如果 not in 语句里面有null值 会出错
原因
A in （a,b,null） 相当于 A=a or A= b or A=null
A not in (a,b,null) 相当于 A!=a and A!=b and A!=null 所以直接相当于 A is not null 所以错了

存储引擎

• 数据引擎描述了数据表在磁盘数据文件中的存储结构，描述了数据表是否需要支持外键，索引，事务等功能
• 常见的存储引擎有InnoDB， MyISAM， MEMORY三种 可以使用 show engines命令查看MySQL支持的引擎

InnoDB引擎

MyISAM引擎

索引

• 索引是对列创建索引
• 索引的存储结构是由B+Tree实现的
• 不加unique创建普通索引，加unique创建唯一索引
• 具有外键约束的列，创表时会自动为列创建普通索引
• 具有唯一约束的列，创表时会自动为列创建唯一索引

创建：

1. create index 索引名 on 表(列)
2. alter table 表 add index 索引名(列)
3. key 索引名 列 // 建表时，创建索引

删除：

1. drop index 索引名 on 表名
2. alter table 表 drop index 索引名

索引的分类

聚簇索引

• 索引顺序和数据表的物理顺序相同
• 聚簇索引的数据结构采用B+Tree
• 索引表文件和数据表文件是同一个文件
• 一个表只能有一个聚簇索引，也就是主键，建表时自动为主键创建了聚簇索引

非聚簇索引

• 索引顺序和数据表的物理顺序不相同
• 索引表和数据表是不同的文件
• 具有外键约束的列，建表时会自动为该列创建普通的非聚簇索引索引
• 具有唯一约束的列，建表时会自动为该列创建唯一的非聚簇索引索引

B-Tree

B+Tree

索引的优缺点

【优点】使用索引可以显著地提供数据查询的速度，在多表连接，分组，排序时，索引也能提高性能
另外，唯一索引还能保证表中的记录不重复

【缺点】创建索引需要耗费时间，也会占用存储空间，所以索引不是越多越好
如果对表经常做插入和删除，每次都需要动态维护和更新索引，有了索引反而会影响性能

视图

• 视图封装了一个SQL查询，用于简化数据查询和限制数据更新操作
• 视图是一张虚表，只具有表结构，但不存储数据

创建视图

create view 视图名 as select语句

使用视图

• 视图的主要作用是查询
• 只有行列子集视图允许进行添加和删除操作
• 对视图的操作实际上都是对基本表中数据的操作
• 行列子集视图—对单张表只进行选行和选列操作的视图，可以执行增删改查所有操作
• 非行列子集视图—对单张表分组，或对多张表连接后得到的视图，只能查询，不能添加和删除

删除视图

drop view 名

视图的优缺点

【优点1】由于视图封装了一条复杂的查询语句，所以可以简化程序中SQL语句的书写
【优点2】只能操作视图中定义的数据，提高了数据访问的安全性

【缺点1】查询视图时，需要将其转换为对基本表的查询，所以执行效率不如直接查询基本表高
【缺点2】更新视图操作受限，只有行列子集视图允许进行增删操作

函数 了解

存储结构 了解

触发器 了解

• 对表进行insert/update/delete操作时，自动执行触发器中定义的代码

create trigger 触发器名 before|after insert|delete|update on 表名 for each row

begin
触发器代码
end