因为又要面试了~所以复习一下数据库的知识,适合有一定数据库基础的参考。
全文包括了:
1.MySQL数据库的基本操作
2.MySQL数据库的存储引擎
3.MySQL数据表的基本操作
4.MySQL中常用的几个数学函数
5.MySQL中常用的几个字符串函数
6.MySQL中的数据查询
7.MySQL中的索引(MySQL索引概念)
8.索引的语法(索引的创建、删除)
数据库的基本操作
创建、删除、显示数据库
- 登陆
- mysql -u root -p
- 创建
- create database base_name
- 使用
- use database base_name
- 显示
- show databases
- show database base_name
- 删除
- drop database base_name
数据库的存储引擎
- 定义
- 数据库引擎是数据库的底层软件,数据库管理系统(DBSM)使用数据库引擎进行增删改查。
- 不同的数据库引擎提供不同的功能
- MySQL5.5支持的主要的数据库引擎
- InnoDB(默认)、MyISAM、Memory、Archive...
- 查看引擎类型
- show engines
- 引擎的选择
- InnoDB
- 提供提交、回滚等事物能力,并要求实现并发控制。
- MyISAM
- 主要用来插入和查询,MyISAM有较高的处理效率
- Memory
- 存放临时数据,数据量不大时选择Memory
- MySQL一般使用Memory作为临时表,存放查询的中间结果
- Archive
- 只有插入和查询操作,切支持高并发的插入,选择Archive。
- 适合存放归档数据,例如日志
- InnoDB
数据表的基本操作
- 创建表
create table table_name(
id int(10) primary key auto_increment,
username varchar(20) unique,
address varchar(20) not null
)
- 查看所有表
- show tables
- 删除表
- drop table table_name
- 查看表结构
- DESC table_name
- 查看建表语句
- show create table table_name
- 修改字段类型
- alter table table_name modify A int(10) not null;
- 修改字段位置
- 修改字段A 到 字段B 的后面
- alter table table_name modify A varchar(20) after B varchar(20)
- 修改字段A 到 字段B 的后面
- 修改字段名
- 将字段名A的字段修改为B
- alter table table_name change A B date
- 将字段名A的字段修改为B
- 删除字段
- alter table table_name drop A
- 添加字段
- alter table table_name add A varcahr(20) unique
- 添加外键约束
- constraint fk_stu_cls foreign key (sid) references cls(cid)
- 删除外键约束
- alter table table_name drop foreign key fk_id_uid
- 修改存储引擎
- alter table table_name engine=MyISAM
- 修改表名
- alter table table_name rename new_table_name
- 不同的引擎之间不可以使用外键约束
MySQL的几个数学函数
- 取整函数
- ceil(x) 向上取整(天花板)
- ceil(-3.67) ---> -3
- ceil(3.67) ---> 4
- floor(x) 向下取整
- floor(-3.67) ---> -4
- floor(3.67) ---> 3
- ceil(x) 向上取整(天花板)
- 随机数
- rand() 返回一个0-1之间的浮点数
- rand(x) 同上,但是相同x返回的浮点数是相同的
- 四舍五入
- round(x) 四舍五入取整数
- rount(x,y) 保留小数点后 y 位
- round(1.38,1) ---> 1.4
- round(19.36,-1) ---> 10
- y为负数时,不四舍五入直接为0
MySQL的字符串函数
- 统计字符个数
- select length(username) from user
- 合并/连接字符串
- concat(lastName, firstName) ---> lastNameFirstName
- 如果连接中有null则结果为null
- concat(lastname, null, firstName) ---> null
- 如果连接中有null则结果为null
- concat(lastName, firstName) ---> lastNameFirstName
- 带分隔符的合并/连接字符串
- concat_WS("-","1999","1101") ---> 1999-1101
- 忽略null
- concat_WS("-","1999","1101") ---> 1999-1101
- 替换字符串
- insert(str,x,len,insertStr)
- insert("gzy",1,2,"111") ---> 111y
- insert("gzy",-1,2,"222") ---> x超出范围,返回原字符串
- insert("gzy",1,1000,"333") ---> len超出范围,则替换x位置后所有的
- insert(str,x,len,insertStr)
- 大小写转换
- lower("Gzy") -> gzy
- upper("Gzy") -> GZY
- 填充函数
- lpad(s1,len,s2) -> 当s1长度不足len则在左边填充s2
- rpad(s1,len,s2) -> 当s1长度不足len则在右边填充s2
- 删除空格
- trim(s) -> trim(" my name ") ---> my name
- 只会删除两侧的空格
- trim(s) -> trim(" my name ") ---> my name
- 删除指定字符串
- trim('ggg' from 'aaggagsssgbggbgcccggg') -> aaasssbbccc
- 重复生成字符串
- repeat('a',10) -> aaaaaaaaaa
- 比较字符串大小
- strcmp(s1,s2)
- s1小返回 -1
- 相等返回 0
- s1大返回 1
- strcmp(s1,s2)
- 获取子串
- substring(s,n,length)
- 如果len<1则为null
- substring(s,n,length)
- 匹配子串开始位置
- position(s1 in s2)
- position("gzy" in "asfgzysad") ---> 4
- position(s1 in s2)
- 字符串逆序
- reverse(s)
- 生成1-10之间的整数
- round(rand()*10)
- 日期格式化
- date_format(date,'%y年%m月%d日 星期 %w')
- 插入系统当前时间
- now()
- 查询最后插入的ID值
- last_insert_id
数据查询
- 去重
- select distinct age from user
- 排序
- order by field desc(降序)/asc(升序)
- 分组
- group by field having 条件
- having 是分组后查询,where是分组前查询
- group by 一般与聚合函数(avg/sum/count/max/min)同时使用
- 分组后显示组内某一列的所有数据
- select id, group_concat(myLike) from user group by id
- group by field having 条件
- 什么情况下通配符格式正确,但是没有查出相应的记录
- 可能在使用通配符的时候不小心将空格加入了进去
- 例如 Like '%e' 查询e结尾的数据,但是e后面多了空格的话就无法正常查询。
- 解决方法:使用trim函数删除空格
MySQL索引概念
- 索引是在MySQL存储引擎中实现的
- MySQL中索引的存储类型
- Hash和Btree
- BTree
- MyISAM
- InnoDB
- Hash/Btree同时支持
- Memory/Heap
- 索引的优点
- 创建唯一索引,可以保证数据的唯一性
- 可大大加快数据库的查询速度(使用索引的最主要原因)
- 可以加速表与表的连接
- 在使用group by和order by的时候,索引也可以显著减少查询中分组或排序的时间
- 索引的缺点
- 创建和维护索引需要消耗时间
- 索引需要占用更多的磁盘空间。
- 每一个索引需要占据一定的物理空间
- 降低了数据库的维护速度
- 对表中的数据进行增删改的时候,索引也会动态维护
- 索引的分类
- 普通索引和唯一索引
- 普通索引是MySQL中最常用的索引类型,允许重复和空
- 唯一索引不允许重复但是允许为空
- 如果是组合索引,则组合必须要是唯一
- 主键索引是特殊的唯一索引,不允许为null
- 单列索引和组合索引
- 单列索引:一个索引只包含一个单列
- 组合索引:多个字段组合创建的索引
- 只有在查询条件中使用了最左边的字段的时候,索引才会被使用
- 全文索引
- FullText,允许null和重复
- 只有MyISAM存储引擎支持
- 空间索引
- 空间索引的列必须要声明为not null
- 空间索引只能在MyISAM存储引擎中使用
- 普通索引和唯一索引
- 索引的设计原则
- 索引不是越多越好,如果索引过多,不但会占用更多的空间,还会影响增删改语句的性能。
- 进行增删改操作时,索引也会动态更新。
- 避免对经常更新的表建立索引。而对于经常查询的字段要尽量建立索引。
- 数据量较小的表不要建立索引
- 因为数据量小,查询花费的时间可能比遍历索引需要的时间要少
- 条件表达式中,为不同的值较多的列建立索引。
- 为不同值较少的列建立索引会影响更新的速度
- 当某一列能确保唯一性的时候,需要建立唯一性索引。
- 使用唯一索引能确保列数据的完整性并且提高查询速度
- 在需要频繁进行order by 和 order by的列上建立组合索引(在待排列有多个的情况下)
- 索引不是越多越好,如果索引过多,不但会占用更多的空间,还会影响增删改语句的性能。
索引的语法
- 建表的时候创建
create table table_name{
....
index(bookid)
}
- 查看索引是否被使用
- explain select * from booke where bookid in (1,2,3)
- 各行的含义
- select_type 查询的类型
- primary/union/subQuery(子查询)
- table from后面的表名
- type 指定了当前数据表与其他数据表的关系
- All/system/const/ref/index
- possible_keys MySQL在搜索时可选用的各个索引
- key 实际选用的索引名
- key_len 索引的字节长度
- ken_len越小,速度越快
- select_type 查询的类型
- 创建唯一索引
- create table ...{ unique index UnqId(id) }
- 创建组合索引
- create table ...{ index mutiIdx(id,pid) }
- 创建全文索引
- create table ...{ fulltext index fullIdx(info) } engine MyISAM
- 默认的InnoDB不支持全文索引
- create table ...{ fulltext index fullIdx(info) } engine MyISAM
- 为已有表添加索引
- alter table table_name add index newIdx(od)
- 删除索引
- alter table table_name drop index newIdx
- 尽量使用短索引
- 对varchar类型进行索引,最好指定一个前缀长度。
- 例如varchar(255),长度限定在10-30,且多数值是唯一的,则不需要对整列索引。这样做可以提高查询速度,减少IO操作