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.%';
解除权限规则: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

临时表

posted @ 2017-06-08 09:50  六神酱  阅读(345)  评论(0编辑  收藏  举报