MySQL常用语法

MySQL常用命令

数据库相关

，展示数据库
	show databases;
，创建数据库
	create database db_name
，删除数据哭
	drop database db_name
，使用数据库
	user db_name
	注：在操作表时必须先指定使用那个数据库

用户管理

注:mysql数据库下存在user表，创建的用户都在该表中

，创建用户
	create user '用户名'@'ip地址' identified by '密码';
	注：ip地址为 %号时表示任意ip都可访问
，删除用户
	drop user '用户名'@'ip地址'
，修改用户
	rename user '旧用户名'@'旧ip地址'; to '新用户名'@'新ip地址'
，修改密码
	set password for '用户名'@'ip地址' = Password('passwd');
，查看用户权限
	show grants for '用户'@'ip地址'
，增加权限
	grant 权限1,权限2 on dbname.db_table(*) to  '用户'@'ip地址'
，删除权限
	revoke 权限1,权限2 on dbname.db_table(*)  from '用户'@'ip地址'
	权限类别
		数据操作
			select--查询, insert--添加, update--修改, delete--删除
		表操作
			create-创建表，alter--修改表结构, drop--删除表
			references--外键，create temporary tables--临时表权限，
			index--索引，create view--创建视图，show view--查勘视图
			create routine | alter routine -- 添加表操作函数，execute--执行函数
		全部权限
			all privilages--某个数据库的整个权限
			all--所有数据库权限

表级别操作

，查看表
	show tables
，查看表结构
	desc table_name;
，查看建表语句
	show create table table_name
，创建表
	create table table_name(
		id int not null auto_increment primary key,
		age int unsigned not null default "0" comment "年龄"
	);
	auto_increment	--	自动增长
	primary key	--	主键
	unsigned	--	设置无符号
	default	--	默认值
	comment	--	备注
，设置表备注
	alter table table_name comment "注释"
，修改表名称
	rename table old_tablename to new_tablename
，删除表
	drop table table_name;
，删除表内容
	delete from table_name;
，清空表内容(主键变为1)
	truncate table table_name;
，查看表数据(主键不变)
	select * from table_name
，增加字段
	alter table `table_name` add field_name field_type not null default "默认值" comment "注释" 
，删除字段
	alter table `table_name` drop `index_name` 
，修改字段类型
	alter table `table_name` modify `column_name` int not null (auto_increment primary key)
，重命名
	alter table `table_name` change old_field new_field field_type
，创建索引
	index	-	普通索引
		create index `index_name` on table(`field_name`)
	unique index	--	唯一索引
		create unique index `index_name` on table(`field_name`)
	fulltext index	--	全文索引
		create fulltext index `index_name` on table(`field_name`)
，删除索引
		drop index ·index_name· on table
，插入数据
		单独添加
		insert into table_name(`field_a`, `field_b`) values(``,``)
		批量添加
		insert into table_name(`field_a`, `field_b`)values(``,``),(``,``)(``,``)
，设置自增主键
  		field field_type not null auto_increment primary key

查询语法

，查询，排序，分页
	select field,field2 from table where field_name='search_val' order by field desc limit 0,10
，分组, having过滤
	select sex from table group by sex(`field`) having avg(age) > 30

注：
	where 与 having 的区别
	where  不能使用聚合函数作为检索条件
	having  通常在group by  后使用过滤条件可使用聚合函数当过滤条件
，内链接(只显示满足条件的)
	select * from table as table_a inner join table as table_b where table_a.bid  = table_b.id
，左外链接(以左表为主 主表中匹配不到右表以null代替)
	 select * from table as table_a left join table as table_b where table_a.bid  = table_b.id	
，右外链接(以右表为主 主表中匹配不到左表以null代替)
	 select * from table as table_a right join table as table_b where table_a.bid  = table_b.id	
，全外链接
	select * from table as table_a left join table as table_b where table_a.bid  = table_b.id	
	union 
	select * from table as table_a right join table as table_b where table_a.bid  = table_b.id	
，联合
	select * from table_a
	union
	select * from table_b
	注：
		union 显示全部数据，不进行重复过滤，效率较高
		union all 过滤重复数据
		联合表中字段数必须一致
，子查询
	select * from table pid in (select id from table)

MySQL – Lock

，乐观锁：用于控制高并发的一种常见手段
	实现流程：
		1，获取准备修改数据的记录[包含控制字段此处以version 字段为例]
		2，在修改时，修改数据中需包含 version 字段并且在原有的基础上1，修改条件中需包含version字段 [比较值为从步骤1中获取的version值]
	实现原理：
		where条件成立则修改成功，反之失败
	例子：
		表为 id, surplus, version
		
		首先：查询准备修改的数据
			 info =  select id,surplus,version from table where id = 1
		然后：数据修改
			  update table set surplus = 1, version = version  + 1 where  id = 1 and version = info['version']
，悲观锁：用于控制高并发的一种手段
	实现原理：
		1，关闭MySQL自动提交功能【默认是开启的】
		2，开启事务
		3，使用 select.... for update 加行锁
			意义：加行锁，使得其他操作无法进行，只能等待检索才可进行下一步
			注：此处指定记录的中必须含有where条件，并且条件必须有索引，否则会将整个表锁定
		4，事务提交 | 回滚
	例子：
		set autocommit = 0;
		begin;
		select * from where table id = 1 for update;
		commit | rollback

分区

，分区类型
	，range分区：
		根据给定的范围区间进行区分，提供分区的字段多数为主键且该类型必须为数字类型
	，list分区
		与range类似只不过list是提供一个用于分类的离散值（列表集合），当某字段的值被分区包含时，便属于该分区
	，hash分区
		根据MOD(分区键，分区数)的值把数据行存储到表的不同分区中
		数据可以平均的分布在各个分区中
		HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型
	，key分区
			根据MySQL内部提供的哈希函数进行分区。
			
，分区操作
	，创建分区：
		，range分区
			，表已存在		
			alter table name partition by range(`field_name`) (
				partition partition_name values less than (10[分区区间]),
				partition partition_name values less than (20[分区区间]),
				partition partition_name values less than maxvalue
			)
			，表不存在
			create table table_name()partition by range('field_name') (
				partition partition_name values less than (10[分区区间]),
				partition partition_name values less than maxvalue
			);
		，list 分区
			，表已存在
			alter table `name` partition by list('field_name')(
				partition name values in (num1,num2,num3),
				partition name values in (num4,num5,num6)
			)
			，表不存在
			create table table_name()partition by list('field_name') (
				partition name values in (num1,num2,num3),
				partition name values in (num4,num5,num6)
			);
		，hash 分区
			，表已存在
			alter table `name` partition by hash(id) partitions 4(分区个数)
			，表不存在
			create table table_name()partition by hash('field_name') partitions 4(分区个数)
		，key 分区
			，表已存在
			alter table `name` partition by linear key(id) partitions 4(分区个数)
			，表不存在
			create table table_name() partition by linear key(id) partitions 4(分区个数)
	，删除分区：
		alter table `name` drop partition partition_name
	，移出分区
		alter table `name` remove partitioning
	，合并分区
		alter table `name` reorganize partition p_name1, p_name2, p_name3, into  (partition p_name values less than (num 合并区间))
	，拆分区间

  注意：
，含有外键，全文索引不能进行分区。
，如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。
，临时表不能分区。
，只有RANGE和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。
，分区字段尽量不要可以为null
，删除分区时，所在分区的数据同样删除(range, list分区相关数据删除，hash,key分区不删除)
，最后一个分区不能删除，想要删除的话只能使用删除表(drop table)
，当对非空数据表进行分区操作时，分区区间必须包含全部分区字段的值
，合并区间时，新区间范围区间值必须等于待合并区间中区间的最大值
，拆分区间时，不能改变原分区区间的最大值

总结

补录

  第一节，MySQL索引的使用及原理
  第二节，MySQL常用语法
  第三节，MySQL常遇问题

引用文档

[MySQL分区](https://www.cnblogs.com/qiyunhai/p/13729824.html)