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-winx64inmysqld --initialize -insecure
	- 启动服务端
		E:...mysql-5.x.x-winx64inmysqld
	- 客户端连接服务端
		E:...mysql-5.x.x-winx64inmysql -u root -p
		
	配置环境变量：
		- 将MySQL解压路径，加入配置环境path，可以在终端直接运行

	windows服务：
		- 将MySQL服务端作为windows服务
			E:...mysql-5.x.x-winx64inmysqld --install
		- 去除MySQL的windows服务
			E:...mysql-5.x.x-winx64inmysqld --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.%';
解除权限规则：revoke 权限1[,权限2,...] on 数据库名.表名 from '用户名'@'ip地址'; 此处使用from； 
- 给用户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

临时表

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/sunqim16/p/6961281.html