How to mySQL

本文收录mysql基本操作语句，以供查阅。

数据库操作

/* 创建数据库 */
create database [if no exists] `mydatabase`
	[character set somecharset]
	[collate somecollate];
/* 查看数据库定义 */
show create database `mydatabase`;
/* 修改数据库 */
alter database [`mydatabase`] [character set somecharset] [collate somecollate];
/* 删除数据库 */
drop database [if exists] `mydatabase`,...;
/* 查看所有数据库 */
show databases;
/* 切换数据库 */
use `mydatabase`;

创建数据表

/* 创建表 */
create [temporary] table [if no exists] `mytable`(
	-- 字段类型
	`int_like` tinyint(len)|smallint(len)|mediumint(len)|int(len)|bigint(len) [unsigned],
	`float_like` float|double|decimal precision(len,dec) [unsigned],
	`bool_like` bit|bool|boolean,
	`date_like` datetime|date|time|timestamp|year,
	`char_like` char(len)|varchar(len),
	`text_like` tinytext|text|mediumtext|longtext,
	`binary_like` tinyblob|blob|mediumblob|longblob,
	`enum_like` enum(var1,var2,...)|set(var1,var2,...),
	
	-- 字段可选项
	-- 非空约束
	`myfiled` fieldtype [not null | null],
	-- 默认值
	`myfiled` fieldtype [default var|null],
	-- 自增
	`intfiled` intfiletype [auto_increment],
	-- 创建时候自动生成时间
	`myfield` datefieldtype [default current_timestamp],
	-- 插入时候自动修改时间
	`myfield` datefieldtype [on update current_timestamp],
	-- 注释
	`myfield` fieldtype [comment commentstatement],
	
	-- 实体完整性约束
	primary key(`myfield1`,...),
	-- 索引
	index|unique|fulltext|spatial `mykey`(`myfield`,...),
	-- 引用完整性约束
	[constraint `myconstraint`]
	foreign key(`myfield`,...) references on `myothertable`(`myfield`,...) 
		[on delete cascade|no action|restrict|set null] 
		[on update cascade|no action|restrict|set null],
	-- 域完整性约束
	[constraint `myconstraint`] check(boolexpr)
)[engine=someengine] [default charset=somecharset] [collate=somecollate] 
[partition by partitionstatement];
-- 从现有数据创建
create table [if no exists] `mytable`[(tablestatement)]
	select selectstatement;
-- 从现有表格结构创建
create table [if no exists] `mytable` like `myothertable`;
/* 查看数据表定义 */
show create table `mytable`;
/* 重命名表 */
alter table `myoldtable` rename to `mynewtable`;
rename table `myoldtable` to `mynewtable`,...;
/* 删除表 */
drop [temporary] table [if exists] `mytable`,...;
/* 查看所有表 */
show tables [from `mydatabase'];

变更数据表结构

/* 增加字段 */
alter table `mytable` add [column] `mynewfield` filedstatement [first | after `myfield`],...;
/* 删除字段 */
alter table `mytable` drop [column] `myfield`,...;
/* 修改字段 */
alter table `mytable` modify [column] `myfield` fieldstatement [first | after `myotherfield`],...; 
/* 删除并增加字段 */
alter table `mytable` change [column] `myoldfield` `mynewfield` fieldstatement [first | after `myfield`],...;

/* 创建主键 */
alter table `mytable` add primary key (`myfield`,..);

/* 创建索引 */
create index|unique|fulltext|spatial `myindex` on `mytable'('myfield`,...),...;
alter table `mytable` add index|unique|fulltext|spatial `myindex`(`myfield`,...),...;
/* 删除索引 */
drop index `myindex` on `mytable`,...;
alter table `mytable` drop index `myindex`,...;

/* 创建外键 */
alter table `mytable` add foreign key `myconstraint`(`myfield`) referencestatement,...;
/* 删除外键 */
alter table `mytable` drop foreign key `myconstraint`;

/* 修改存储引擎 */
alter table `mytable` set engine=someengine;

/* 修改分区 */
alter table `mytable` reorganize partition `mypartition` into partitionstatement;

变更数据

/* 插入多条数据 */
insert [ignore] into `mytable`[(`myfield`,...)] values(var1,...),...;
/* 从现有表导入数据 */
insert [ignore] into `mytable`[(`myfield`,...)] select selectstatement;
/* 从文件批量导入数据 */
load data local infile "myfile.dump" into table `mytable`;

/* 更新数据 */
update `mytable` set `myfield`=var,... [where wherestatement];

/* 删除数据 */
delete from `mytable` [where wherestatement];
/* 多表删除 */
delete `mytable`,... from `mytable` joinstatement [where wherestatement];
delete from `mytable`,... using `mytable` joinstatement [where wherestatement];

检索数据

/* 检索数据 */
select [distinct] `myfield1` [as `mynewname`],...
	from `mytable` [inner|left|right join `myothertable`
		on `mytable`.`myfield` = `myothertable`.`myfield`],...
	where wherestatement
	group by groupstatement
	having havingstatement
	order by `myfield` [asc|desc],...
	limit [offset,] n|-1;

事务

/* 事务处理 */
start transaction | begin
	savepoint `mysavepoint`;
	statement;
	rollback to `mysavepoint`;
commit|rollback;

视图

/* 创建视图 */
create [algorithm=merge|temptable|undefined]
	[definer=`user`@`host`]
	[sql security definer|invoker]
view `myview`[(`myfield`,...)]
	as select selectstatement
	[with check option];
/* 删除视图 */
drop view `myview`,...;

存储过程

/* 创建存储过程 */
create [definer=`user`@`host`]
	[sql security definer|invoker]
procedure `myprocedure`([in|out|inout `myarg1` fieldtype,...])
begin
	-- 声明变量
	declare `myvar` fieldtype [default initvar];
	-- 赋值变量
	set `myvar` = var;
	-- 流程控制
	-- if
	if expr then
		statement;
	elseif expr then
		statement;
	else 
		statement;
	end if;
	-- switch
	case expr
		when var1 then
			statement;
		when var2 then
			statement;
		else 
			statement;
	end case;
	-- while
	while boolexpr
		statement;
	end while;
	-- do...while
	repeat
		statement;
	until boolexpr end repeat;
end
/* 调用存储过程 */
call myprocedure([myarg1, @myarg2,...]);
/* 删除存储过程 */
drop procedure `myprocedure`,...;

存储函数

/* 创建存储函数 */
create [definer=`user`@`host`]
	[sql security definer|invoker]
function `myfunction`([`myarg1` fieldtype,...])
returns fieldtype
begin
	-- 游标
	declare mycursor cursor for select selectstatement;
	open mycursor;
	fetch mycursor into var1,...;
	statement;
	return var;
end
/* 调用存储函数 */
select myfunction([myarg1,...]);
/* 删除存储函数 */
drop function `myfunction`,...;

触发器

/* 创建触发器 */
create trigger `mytrigger` 
    before|after insert|delete|update
    on `mytable` 
    for each row
begin
    select old.myfield, new.myfield;
    statement;
end
/* 删除触发器 */
drop trigger `mytrigger`,...;

定时器

/* 创建事件 */
create event `myevent`
	on schedule at sometime | every timeinterval 
	[starts sometime] [ends sometime]
do
	statement;
/* 启动事件 */
alter event `myevent` enable;
/* 禁用事件 */
alter event `myevent` disable;