mysql
MySQL数据库
数据分类：
- 关系型数据库：sqllite,db2,oracle,access,sql server,MySQL
- 非关系型数据库：MongoDB,redis
以上软件都是DBMS。
MySQL数据库是用于管理文件的一个软件，包括两部分：
服务端
- socket服务端
- 本地文件操作
- 解析指令【sql规则】
客户端
- socket客户端
- 发送指令
- 解析指令【sql规则】
PS：
- 其中socket功能、文件操作、指令发送可以由DBMS(数据库管理系统)实现
下面介绍内容：
- MySQL安装(服务端 和 客户端)
- 连接
- SQL语句
MySQL安装
linux
- 语句安装
yum install mysql-server
windows
- 执行文件安装：
下一步，下一步
- 压缩包安装：
- 解压
- 初始化(初始化后默认用户名为root,密码为空)
E:\...\mysql-5.x.x-winx64\bin\mysqld --initialize -insecure
- 启动服务端
E:\...\mysql-5.x.x-winx64\bin\mysqld
- 客户端连接服务端
E:\...\mysql-5.x.x-winx64\bin\mysql -u root -p
配置环境变量：
- 将MySQL解压路径，加入配置环境path，可以在终端直接运行
windows服务：
- 将MySQL服务端作为windows服务
E:\...\mysql-5.x.x-winx64\bin\mysqld --install
- 去除MySQL的windows服务
E:\...\mysql-5.x.x-winx64\bin\mysqld --remove
- 启动命令
net start MySQL
- 停止命令
net stop MySQL
初始化：--initialize -insecure
连接服务： mysql -u root -p
windows服务：
--install
--remove
MySQL服务启动\停止命令：
net start MySQL
net stop MySQL
mac
- dmg安装
ps：各种报错处理
连接
数据库结构
数据库
|--表1
| |--行1（数据）
| |--行2
|
|--表2
| |--行1
| |--行2
连接
- 连接数据库
$ mysql -u root -p
- 显示数据库们
mysql> show databases;
- 使用名称为mysql的数据库
mysql> use mysql;
- 显示表们
mysql> show tables;
- 显示全部行（数据）
mysql> select * from user;
创建用户
规则：create user '用户名'@‘ip地址’ identified by '密码'；
百分号可以表示ip地址范围；
- 创建用户tom，密码为123，访问ip地址只能是192.168.11.1～255范围
mysql> create user 'tom'@'192.168.11.%' identified by '123';
授权
规则：grant 权限1[,权限2,...] on 数据库名.表名 to '用户名'@'ip地址';
全部权限，grant除外：all privileges；
- 给用户tom 授权 数据库db1 表t1 权限select,inster,update
mysql> grant select,inster,update on db1.t1 'tom'@'192.168.11.%';
- 给用户tom 解除 数据库db1 表t1 全部权限
mysql> revoke all privileges on db1.t1 from 'tom'@'192.168.11.%';
mysql语句
操作数据库
- 创建数据库db1
create database db1;
- 创建数据库db2带有utf8编码识别
create database db2 default charset utf8;
- 删除数据库db1
drop database db1;
- 查全部数据
show databases;
推荐创建数据库方式：create database db2 default charset utf8;
操作表
增
规则：
create table 表名（
列名1 类型 [null | not null] [auto_increment] [primary key],
列名2 ...
...
） [engine = innodb | myisam] [default charset = utf8];
innodb: 支持事务，原子性操作，sql操作失败回滚
primary key: 表示 约束(不能重复且不能为空); 加速查找
not null: 不能为空
- 创建表名为t1 包括列id,name utf8编码
create table t1(id int,name char(10)) default charset=utf8;
- 创建表名为t2 表示支持事物,原子性操作engine=innodb
create table t2(id int,name char(10)) engine=innodb;
- 创建表名为t3 列id不为空,自增,主键
create table t3(
id int not null auto_increment primary key,
name char(10));
推荐写法:
create table t3(
id int not null auto_increment primary key,
name char(10)
)engine=innodb default charset=utf8;
删
- 清空表t1中数据
delete from t1;
- 清空表t1数据,同时自增id清空
truncate table t1;
- 删除表t1
drop table t1;
查
- 查看全部表
show tables;
操作表（补充）
- 查看创建表的sql语句，横表
show create table t1;
- 查看创建表的sql语句，纵表
show create table t1 \G;
# 结果：
CREATE TABLE `t1` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
- 查看表信息
desc t1;
- 修改表自增主键的值从2开始
alter table t1 auto_increment=2;
MySQL可以设置自增主键n开始，但是不可以像SqlServer那样可以在建表时设置自增步长
所以,MySQL设置步长方式有两种方式基于会话级别和基于全局级别
基于会话级别
在本终端设置步长后，效果只有在本中端起作用，关闭后失效
- 查看会话变量
show session variables like 'auto_inc%';
# 结果：
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
- 设置会话步长为10，自增主键从10开始
set session auto_increment_increment=2; # 步长
set session auto_increment_offset=10; # 起始
# 结果：
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 10 |
+--------------------------+-------+
基于全局级别
设置步长后，永久有效
- 查看全局变量
show global variables like 'auto_inc%';
- 设置全局步长
set global auto_increment_increment=2; # 步长
set global auto_increment_offset=10; # 起始
操作数据（行）
- 插入数据
insert into t4(id,name) values(1,'tom');
- 插入多条数据
insert into t4(id,name) values(2,'alex'),(3,'peter');
- 插入一张表的查询结果
insert into t4(id,name) select id,name from tb3;
- 删
delete from t4 where id = 1 ;
- 改
update t4 set name = kirs;
update t4 set name = alex where id = 2;
- 查
selet * from t1；
数据类型
数字
- tinyint
- int
- bigint
- FLOAT
- DOUBLE
- decimal 绝对精确
字符串
- char(10) 查询速度快
- varchar(10) 可变长，节省空间
- text 用于存放多媒体文件url
PS：创建数据表通常把可以确定长度的字段至于前列，变长至于后面
时间类型
- DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
枚举类型
表中数据只可以是规定内容
- enum
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
集合类型
SET类型的值可以取列表中的一个元素或者多个元素的组合
规则：属性名 SET('值1','值2','值3'...,'值n')
-set
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
外键
本表引入其他表的主键作为列
规则：[constraint <关系名>] foreign key (列名) references <外键表名>(外键列名)
create table userinfo(
uid bigint auto_increment primary key,
name varchar(32),
department_id int,
constraint fk_user_depar foreign key (department_id) references department(id)
)engine=innodb default charset=utf8;
create table department(
id bigint auto_increment primary key,
title char(15)
)engine=innodb default charset=utf8;
主键：
一张表只可以有一个主键，主键不一定只是一列，可以是多列组成，且不能重复，不能为空
所以，联合主键作为外键引入时是多列
- 当表中的外键是联合主键时，引入外键也是多列
create table t1(
id int not null auto_increment,
num int not null,
primary key(id,num),
)engine=innodb default charset=utf8;
create table t2(
id int not null auto_increment primary key,
t1_id int not null,
t1_num int not null,
constraint fk_t2_t1 foreign key(t1_id,t1_num) t1(id,num)
)engine=innodb default charset=utf8;
唯一索引：
唯一索引是一种约束，创建表时对表中的某一列或者某几列设置，在数据插入表中时，别标记为唯一索引的几列不能重复，可以为空，对于表的查询有加速功效
规则：
create table 表名(
列名1 类型 ...,
列名2 类型 ...,
列名3 类型 ...,
unique 唯一索引名称(列名1,列名2),
cconstraint ...
);
唯一索引通常与外键联用，对应表与表之间的一对一、一对多、多对多关系
- 一对多关系
适用于用户表与部门表情况【部门：用户 = 1：n】
- 一对一关系
用户开通博客模式【用户：博客 = 1：1】
用户id作为博客表的外键，并需唯一
- 多对多关系
用户权限模式，用户表与权限表之间需要关系表【用户：权限 = n : n】
用户id与权限id作为关系表的外键，并且联合唯一
操作数据-查询
通配符
- % 通配a开头
select * from tb4 where name like "a%";
- _ 通配a开头，只有两个字符
select * from tb4 where name like "a_";
limit
- limit 查询前10条信息
select * from tb4 where limit 10;
- 分页效果 从0开始，每次查询10条信息
n = 页码
x = (n-1) * 10
select * from tb4 where limit x,10;
- offset 以下效果一样
select * from tb12 limit 10 offset 20;
select * from tb4 where limit 20,10;
排序
- order by
select * from tb4 order by id desc; # 从大到小
select * from tb4 order by id asc; # 从小到大
- 从后取10条信息
select * from tb4 order by id desc limit 10;
分组
- 聚合函数
- count
- max
- min
- sum
- avg
- group by 通过id分组
select count(id),name from tb4 group by id;
- having **** 如果对于聚合函数结果进行二次筛选时，必须使用having ****
select count(id),name from tb4 group by id having count(id) > 1;
连表
- left join 左连接，左侧表内容全部显示，右侧表没有对应显示null
select * from tb4 left join tb5 on tb4.id = tb5.t_id;
- right join 右连接，右侧表内容全部显示，左侧表没有对应显示null
select * from tb4 right join tb5 on tb4.id = tb5.t_id;
select * from tb5 left join tb4 on tb4.id = tb5.t_id; # 与上条结果一样色儿的
- inner join 将出现null时一行隐藏
select * from tb4 inner join tb5 on tb4.id = tb5.t_id;
备份
数据库备份
- 【表结构+数据】
mysqldump -u root db1 > Users/.../db1.sql -p
- 【表结构】
mysqldump -u root -d db1 > Users/.../db1.sql -p
数据库还原
- 导入 【表结构】
mysql -u root -d db1 < Users/.../db1.sql -p
