MySQL常用语法举例

#创建数据库
create {database | schema} [if not exists] db_name 
[default] character set [=] charset_name 
[default] collate [=] collation_name;

#选择数据库
use db_name;

#修改数据库
alter {database | schema} [db_name] 
default character set [=] charset_name 
default collate [=] collation_name;

#删除数据库
drop {database | schema} [if not exists] db_name;

#查看数据库
show {database | schema} 
[like 'pattern' | where expr];

#创建表
create [temporary] table tbl_name
(
	字段名1 数据类型 [列约束条件] [默认值]
	[,字段名2 数据类型 [列约束条件] [默认值]]
	[,……]
	[,表约束条件]
)[engine=引擎类型];
#创建临时表：temporary
#常见数据类型：int、double、bool、date、timestamp、time、char、varchar
#列自增属性：auto_increment
#列默认值：default
#列是否允许缺值：null / not null
#表的主键：primary key(int_col_name)

#更新表
alter table db_name.tbl_name add column ... [after col_name | first]; #增加新列
alter table db_name.tbl_name add primary key ...; #添加主键
alter table db_name.tbl_name add foreign key ...; #添加外键
alter table db_name.tbl_name add index ...; #添加索引
alter table db_name.tbl_name add unique [index | key] ...; #添加唯一索引
alter table db_name.tbl_name change column ...; #修改列的名称和数据类型
alter table db_name.tbl_name alter column ...; #修改或删除列的默认值
alter table db_name.tbl_name modify column ... [after col_name | first]; #修改列的数据类型和位置
alter table db_name.tbl_name drop column col_name; #卸除列
alter table db_name.tbl_name drop primary key ...; #卸除主键
alter table db_name.tbl_name drop foreign key ...; #卸除外键
alter table db_name.tbl_name drop index ...; #卸除索引
alter table db_name.tbl_name rename to db_name.new_tbl_name; #修改表名

#重命名表
rename table tbl_name to new_tbl_name,...;

#删除表
drop [temporary] table [if exists] tbl_name1,table_name2,...[restrict | cascade];

#查看表
show [full] tables [{from | in} db_name] [like 'pattern' | where expr]; #查看表名
show [full] columns {from | in} tbl_name [{from | in} db_name] [like 'pattern' | where expr]; #显示表结构
{describe | desc} tbl_name [col_name | wild]; #显示表结构

#创建索引
create [unique] index index_name on tbl_name(col_name[(length)] [asc | desc],...);
create table tbl_name(
	id int not null auto_increment,
	primary key(id), #主键
	{index | key} index_name(id), #索引
	unique [index | key] index_name(id), #唯一索引
	foreign key index_name(...), #外键
);

#查看索引
show {index | indexs | keys} {from | in} tbl_name [{from | in} db_name] [where expr];

#删除索引
drop index index_name on tbl_name;

#插入数据
insert into tbl_name(col_name1) {values | value}


#选择
SELECT 关系名 WHERE 条件;

#投影
PROJECTION 关系名 (属性名1, 属性名2, ..., 属性名n);

#连接
JOIN 关系名1 AND 关系名2 WHERE 条件;

#创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;

#选择数据库
USE 数据库名;

#修改数据库
ALTER DATABASE 数据库名 ...;

#删除数据库
DROP DATABASE IF EXISTS 数据库名;

#查看数据库
SHOW DATABASE;

#创建表
CREATE TABLE 表名(
	id INT NOT NULL AUTO_INCREMENT, #自增属性
	name VARCHAR NULL, #默认值为NULL
	sex CHAR(1) NOT NULL DEFAULT 1, #设置非NULL默认值
	字段名 数据类型 约束条件 默认值,
	...,
	PRIMARY KEY(id,...), #指定主键
	INDEX index_name(col_name) #创建索引
);

#创建临时表
CREATE TEMPORARY TABLE 表名(
	字段名 数据类型 约束条件 默认值,
	...
);

#更新表
ALTER TABLE 数据库名.表名 ADD COLUMN 新列名 数据类型 约束条件 默认值 AFTER 列A; #在列A后添加新列
ALTER TABLE 数据库名.表名 CHANGE COLUMN 列A 新列名 数据类型 约束条件 默认值; #修改列A的名称和数据类型
ALTER TABLE db_name.table_name ALTER COLUMN col_name SET DEFAULT new_default_value; #修改列的默认值
ALTER TABLE db_name.table_name MODIFY COLUMN col_name new_data_type FIRST; #修改列的数据类型
ALTER TABLE db_name.table_name DROP COLUMN col_name; #删除一列
ALTER TABLE db_name.table_name DROP PRIMARY KEY ...; #删除主键
ALTER TABLE db_name.table_name DROP FOREIGN KEY ...; #删除外键
ALTER TABLE db_name.table_name DROP INDEX ...; #删除索引
ALTER TABLE db_name.table_name RENAME TO db_name.new_table_name; #更改表名

RENAME TABLE db_name.table_name TO db_name.new_table_name,...; #重命名多个表
DROP TABLE table_name,...; #删除对个表
SHOW TABLES; #查看数据库中的表名
SHOW COLUMNS FROM table_name; #显示表的结构
DESC db_name.table_name; #显示表结构

CREATE INDEX index_name ON db_name.table_name(col_name(3) ASC,...); #根据列的前三个字符创建升序索引index_name
SHOW INDEX FROM db_name.table_name; #查看表的索引
ALTER TABLE db_name.table_name ADD INDEX index_name(col_name); #给col_name添加一个非唯一的索引index_name
DROP INDEX index_name ON table_name; #删除表table_name的索引index_name
ALTER TABLE db_name.table_name DROP PRIMARY KEY, DROP index index_name; #删除表的主键和索引

INSERT INTO db_name.table_name VALUES(value1,value2,...,valueN); #插入一行数据
INSERT INTO db_name.table_name(col1,col2,...,colN) VALUES(val1,val2,...,valN); #插入一行数据,指定插入项
INSERT INTO db_name.table_name SET col1=val1,col2=val2,...,colN=valN; #插入部分列值
INSERT INTO db_name.table_name SELECT...; #把从其他表查询处理的数据插入

DELETE FROM db_name.table_name WHERE col_name=val1; #删除表中col_name等于val1的行
UPDATE db_name.table_name SET col_name=new_value WHERE col1=val1; #修改表中col1为val1的col_name的值为new_value

#查询表
SELECT [ALL|DISTINCT|DISTINCTROW] col1,col2 [AS] 别名,CASE WHEN col3='1' THEN 表达式1 WHEN col3='2' THEN 表达式2 ... ELSE 表达式3 END AS 别名,...,colN FROM db_name.table_name WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...;

#多表查询,交叉连接
SELECT * FROM table_name1 CROSS JOIN table_name2;
SELECT * FROM table_name1,table_name2;

#多表查询,内连接
SELECT * FROM table_name1 INNER JOIN table_name2 ON table_name1.col_name=table_name2.col_name;

#多表查询,外连接
SELECT * FROM table_name1 LEFT OUTER JOIN table_name2 ON table_name1.col_name=table_name2.col_name;
SELECT * FROM table_name1 RIGHT OUTER JOIN table_name2 ON table_name1.col_name=table_name2.col_name;

#判定范围查询
SELECT * FROM db_name.table_name WHERE col_name BETWEEN val1 AND val2; #查询表中列值在val1和val2之间的信息
SELECT * FROM db_name.table_name WHERE col_name IN(val1,val2,...,valN); #查询表中列值是val1,val2,...,valN的信息
SELECT * FROM db_name.table_name WHERE col_name IS [NOT] NULL; #查询表中列值(不)为NULL的信息

#MySQL常用聚合函数
COUNT #求项数
MAX #求最大值
MIN #求最小值
SUM #表达式值的和
AVG #求平均值
STD、STDDEV #表达式中的标准值
VARIANCE #表达式中的方差
GROUP_CONCAT #返回一组列值连接组合
BIT_AND #逻辑或
BIR_OR #逻辑与
BIT_XOR #逻辑异或



修改MySQL语句的结束标记
  delimiter $$

创建存储过程
  create procedure sp_name(proc_parameter) begin ... end$$

声明局部变量
  declare var_name var_type;

局部变量赋值
  set var_name = var_value;
  select col_name into var_name from ...;

举例：
DROP PROCEDURE if exists sp_insert_vipuser;
delimiter $$       说明：改变语句结束符号为$$,则；用于过程体内部语句结束符号
CREATE PROCEDURE sp_insert_vipuser()
BEGIN
	DECLARE i,j INT(10);
	SET i=0;
	while i<10 do
		SET i=i+1;
	END while;
	SELECT i;
END $$
delimiter ;




条件判断语句
  if ... then ... else ...
  语法的基本形式如下：
  if search_condition then statement_list
  [elseif search_condition then statement_list]
  …
  [else search_condition then statement_list]
  end if

  case
  语法的基本形式如下：
  case case_value
	when when_value then statement_list
	[when when_value then statement_list]
	…
	[else statement_list]
  end case


循环语句
  while
[begin_label:] while search_condition do
	statement_list
end while [end_label]

  repeat

[begin_label:] repeat
	statement_list
 	until search_confition
end repeat [end_label]


  loop

loop语句可以使用某些特定的语句重复执行，实现简单的循环。
  loop没有停止循环的语句。要结合leave离开退出循环或iterate
  继续迭代。基本形式如下：
[begin_label:] loop
	statement_list
end loop [end_label]


leave语句
  leave语句主要用于跳出循环。语法形式如下：
level label


  iterate
itebate语句主要用于跳出本次循环，然后进入下一轮循环。
  基本语法形式：
itebate label



声明游标
  declare cursor_name cursor for select ... from ...;

打开游标
  open cursor_name;

读取游标数据保存到变量
  fetch cursor_name into var_name ...

关闭游标
  close cursor_name;

调用存储过程
  call sp_name(parameter);

删除存储过程
  drop procedure if exists sp_name;

创建存储函数
  create function sp_name(func_parameter) returns data_type begin ... end$$

调用存储函数
  select sp_name(parameter);

删除存储函数
  drop function if exists sp_name;

创建触发器
  create trigger trigger_name {before | after} {insert | update | delete} on tbl_name for each row trigger_body;

删除触发器
  drop trigger if exists db_name.trigger_name;

创建用户账号
  create user 'user_name'@'host_name' identified by password(...);

删除用户
  drop user 'user_name'@'host_name';

修改用户账号
  rename user 'old_user_name'@'host_name' to 'new_user_name'@'host_name';

修改用户口令
  set password for 'user_name'@'host_name' = password(...);

查看用户权限
  show grants for 'user_name'@'host_name';

权限授予和转移
  grant priv_type on [table | function | procedure] priv_level to 'user_name'@'host_name' [identified by password(...)] [with grant option];

权限撤销
  revoke priv_type on [table | function | procedure] priv_level from 'user_name'@'host_name';

数据备份
  select * from db_name.tbl_name into outfile 'file_url' [fields [terminated by 'string'] [[optionally] enclosed by 'char'] [escaped by 'char']] [lines terminated by 'string'];
  SELECT * from db_name.tbl_name into dumpfile 'file_url';

数据恢复
  load data infile 'file_url' into table tbl_name [fields [terminated by 'string'] [[optionally] enclosed by 'char'] [escaped by 'char']] [lines [starting by 'string'] [terminated by 'string']];