MySQL (关系型数据库管理系统)

MySQL (关系型数据库管理系统)

mysql数据引擎

InnoDB：提供对数据客ACID 事务的支持，也提供了行级锁和外键的约束（处理大数据容量的数据可系统）
Memory：数据都存储在内存中，数据处理速度快，但不安全
MyISam：不提供事务的支持，不支持行级锁和外键（MySQL默认引擎）
CSV
Mrg_MyISam
Archive
Federated
Performance_schema
Blackhole

MySQL 数据库服务仅支持 InnoDB 存储引擎

MyISam索引与InnoDB索引的区别？
InnoDB 索引是聚簇索引，MyISam索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，查询时做到覆盖索引非常高效。
MyISam索引的叶子节点存储的是行数据地址，所以需要再寻址一次才能得到数据。

索引的概念：
索引是一种特殊的文件，他们包含着数据表里的所有记录的引用指针。
索引是一种数据结构，数据索引是数据库管理系统中一个排序的数据结构，可以协助快速查询，更新数据库表的数据，索引的实现一般使用B树或B+树。
索引就像是一本书的目录，为了方便查找内容，通过对内容建立索引形成目录，索引是一个文件，它需要占据物理空间。

索引类型：
1、主键索引： （primary key） 以主键为索引，要求不可以重复，不允许为空值
2、唯一索引： （unique key ） 用来建立索引的列，值必须唯一的，但是可以为null
3、普通索引： （ key ）没有任何限制
4、全文索引： （text/fulltext） 全文索引创建速度比其他索引慢，相应的查询效率也比其他索引慢
5、组合索引： 用多个列组成构建的索引，这多个列中的值不允许为空

索引的优点：
建立索引可以加快数据的检索速度，这就是建立索引的主要目的
使用索引，可以在查询中使用优化隐藏器,从而提高系统的性能。

索引的缺点：
索引需要占物理空间。
创建索引和维护索引需要消耗时间，当对数据进行新增，修改，删除操作的时候，索引需要动态维护，降低了新增，修改，删除操作的执行效率。

索引的使用：
查询时where的使用
查询时orderby 的使用
查询时join的使用

索引覆盖：
如果要查询的字段都建立了索引，那么引擎会直接在索引表中查询而不会访问元数据 (只要还有一个字段没有建立索引就会进行全表扫描），这就是索引覆盖，所以在查询SELECT 的时候只写必要的查询的字段，就可以增加索引覆盖的几率。

索引的基本原理：
索引用来快速查询特定值的记录，如果没有索引，查询就会遍历整一张表。
索引的原理就是把无序的数据变成有序的查询。

创建索引的原则：
1、最左前缀匹配原则：mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就会匹配停止。
2、较为频繁作为查询条件的字段才去创建索引。
3、跟新频繁的自段则不合适创建索引。
4、若不能有效区分数据的列则也不适合创建索引（比如性别，男女未知）。
5、尽量扩展索引，不要新建索引
6、定义有外键的数据列一定要建立索引。

InnoDB的特点：
插入缓冲
二次写
自适应哈希索引
预读

MySQL服务

本地计算机CMD命令：
启动mysql服务：net start mysql
停止mysql服务：net stop mysql
登录mysql： mysql -h 服务器地址 -P 服务端口 -u 用户名，-p 密码
Lunix 系统终端命令：
启动mysql服务：service mysqld start
停止mysql服务：service mysqld stop
登录mysql -h 服务器地址 -P 服务端口 -u 用户名，-p 密码
--------CentOS7 系统-----
启动mysql 需要安装maria DB (CentOS7 系统默认没有安装maria DB)
yum install -y mariadb-server ----安装mariadb-server
systemctl start marisdb.service ---------启动mysql服务

注意：如果远程的服务器连接本地mysql，需要将本地mysql数据库里user表的Host修改为%，默认的Host是localhost。

退出mysql：exit 或者快捷键 ctrl+c

操作mysql
show engines; -----查看存储引擎
show databases; --列出系统数据库
use 数据库名称； --进入数据库
select database(); --查看当前使用的数据库
select version(); --查看当前数据库版本
show tables; —查看当前的数据库中的数据表
show full tables; —可区分普通表和视图
show create table 表名; —查看表建立的脚本：

备份数据库
----导出单个数据库脚本-------
mysqldump -uroot -p 数据库名 > 导出的路径

----导出多个数据库脚本-------
mysqldump -uroot -p --databases 数据库1 数据库2 > 导出的路径

----用notepad查看导出数据库脚本-------
notepad 导出数据库的路径

----导入数据库脚本-------
mysql -uroot -p --default-character-set=utf8 < 数据库脚本的路径

----查看系统中的字符集编码排序规则
注：character set: 字符集（在计算机内部把文字编码成数字的方案，同一个方案编码的字符，只能用同一种方案解码）
show charset;
show character set;

collation: 排序规则（如汉字在字典中就有按笔划排序、按拼音排序等规则）
show charset like “%utf8%”;
show collation;
show collation like ‘%utf8%’;

查看系统使用的字符集：
show variables like ‘char%’;
desc 表名； —查看表的结构信息：

mysql 常见函数
1、字符串函数
concat(); --连接
find_in_set(); --查询的结果集
用法：
select concat(p.province," “,c.city,a.area,” ",s.store_code) AS
from provinces as p, cities as c ,areas as a ,store as s
where c.citycode=a.citycide
and p.provinceid=c.provinceCode
and find_in_set(a.areaid,s.store_address)；

substing_index(); --从字符串中提取子字符串
用法：substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
select concat(p.province," “,c.city,a.area,” ",s.store_code) AS 地址
from provinces as p, cities as c ,areas as a ,store as s
where a.areaid=substring_index(s.store_address,’,’,-1)
and c.citycode=substring_index(a.citycide,’,’,-1)
and p.provinceid=substring_index(c.provinceCode,’,’,-1);

substr(); —截取字符串
upper(); —变大写
lower(); —变小写
replace()：—替换
length(); ----获取字节长度
trim(); -----去前后空格
lpad(); ----左填充
rpad();-----右填充
inster();----获取

2、数学函数
ceil(); -----向上取整
round(); -----四舍五入
mod(); ----取模
floor();-----向下取整
----按年龄段（10+，20+，30+，40+ …）分组统计会员人数 (利用视图 member_infos)----
select floor(age/10)10 as age_range,count() from member_infos group by age_range;
truncate();—截断
rand();------获取随机数（0-1）

3、日期函数
now(); —返回当前日期+时间
year();----返回年
moth();—返回月
day();-----返回日
date_format();----将日期转换成字符
curdate();------返回当前日期
date_sub();—计算日期
select date_sub(curdate(),interval 1 month);
str_to_date();----将字符转换成日期
select str_to_date(‘1999-3-2’,’%Y-%c-%d’);
curtime();------返回当前时间
hour();-----返回小时
minure();—返回分钟
second();—返回秒
detediff();—返回日期相差天数
monthname();—以英文的形式返回月

4、分类
max();----最大值
min();----最小值
sum();----求和
avg();----求平均值
count();—计算个数 ：count(字段)—统计该字段非空值个数；count(*) --统计结果集的行数
注：sum和avg一般用于处理数值类型，
max、min、count可以处理任何类型
其都忽略null值、都可以搭配distinct 字段 【去重复】
select sum（distinct 字段）from表

5、其他函数
version();—当前数据表库服务器版本
database();—当前打开的数据库；
user();—当前用户
password(‘字符’);—返回字符的加密形式
md5(‘字符’);—返回字符的md5加密形式

6、流程控制函数
1)
if（条件表达式，表达式1，表达式2）----如果表达式1成立，返回1 ，否则返回2

case 情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
…
else 值n
end

例：
select salary, depar_id
case depar_id
when 30 then salary1.1
when 40 then salary1.2
when 50 then salary*1.3
else salary end 新工资
from employees;

case 情况2
case
when 条件1 then 值1
when条件2 then 值2
…
else 值n
end

例：
select salary
case when salary>2000 then ‘A’
case when salary>1500 then ‘B’
case when salary>1000 then ‘C’
else ‘D’
end as 工资级别
from employees;

查询数据语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接的条件
where 筛选的条件
group by 分组
having筛选条件
order by 排序列表

连接类型分类：
内连接：inner
外连接：左外：left [outer] 右外right [outer] 全外：full [outer]
交叉连接：cross

内连接：

select c.* , g.* from groups g,contact_group cg ,contact c
where g.id=cg.gruop_id  and  c.id =cg.contact_id
 

自连接

select * from  divisions
where (select count(*) from dicisions d  where d.parent_id =divisions.id)=0;

全连接：

select * from groups g left join contact_group cg  on g.id=cg.group_id  left  join contacts c on c.id =cg.contact_id 
union 
select * from groups g right join contact_group cg  on g.id=cg.group_id  right  join contacts c on c.id =cg.contact_id ;

创建表：
create table 表名(列名 列类型，列名 列类型，…);
创建employees表 ：

create  table  employees (
---定义employees 表的列名----
id bigint unsigned not null auto_increment,
sid char(18) not null,
 name varchar(50) not null,
gender char(1),
birth_date date,
departement varchar(50) not null,
---定义employees 表的约束----
primary key(id),
unique key(sid)
);

employees表插入数据：
insert into 表名（列名1，列名2，…）values(值1，’ 值2 ‘，…), (值1，’ 值2 '…), … ;

insert into  employees(id,sid,name,departement) values
(1,'52456589659826885' , '丽丽' , '人事'),
(2,'546969859895989' , '李同学' , '开发');

修改employees表数据
update 表名，set 要修改列名=’ ’ where 要修改的id=id

update employees set departement= '开发'  where  employees.name='李同学';

为表增加索引：
alter table 表名 add index 索引名 （进行索引的列名1，进行索引的列名2，…）;

alter table employees add index UNT  (name);

查看employees表name字段是否走索引：

explain select name from employees;

删除表
delete from 表名； -------删除表的所有数据-------
drop from 表名； --------删除表-------

删除索引
alter table 表名 drop key 索引名；
alter table employees drop key name;