mysql命令汇总

最近花差不多一周，零零散散地读完了《mysql必知必会》，回过来复习一遍，再对里面的命令总结一哈。

连接数据库

 1. 登录数据库 shell命令：
	mysql -u root -p //输入密码就OK
 2. 显示数据库 mysql命令：
	show databases; 
 3. 选择数据库：
 	use 数据库名;
 4. 显示表：
 	show tables;
 5. 显示列：
 	desc 表名; 
 	show column from 表名 //和上面等价

简单选择-select(distinct,limit,as,desc)

 1. 选择多行：
 	select * from 表名; //选择所有记录
 	select a1,a2 from表名; //选择特定的列
 2. 选择不同的行：
 	select distinct a1 from 表名; //a1相同的合并成一个
 	select distinct a1,a2 from 表名; //a1,a2任一不同都显示
 3. 取前行-limit：
 	select * from 表名 limit 3; //显示前三行
 	select * from 表名 limit 2 offset 3; //从第3行(第一行为0)开始取2个
 4. 取别名-as：
 	select b1.a1 from 表名 as b1;
 5. 排序-desc：
 	select a1,a2 from 表名 order by a1 desc,a2;//先按a1逆序，a1相同的行按a2正序，默认大小写一样

过滤数据-where(and,or,in,not)

 1. and or
	select * from 表名 where a1=2 and(or) a2=1; //先处理and 再处理or，尽量加括号
 2. between
 	select * from 表名 where a1 between 2 and 5; //一般包含两端值[2,5]的闭区间
 3. in
 	select * from 表名 where a1 in (2,5); //包含2，5，速度比or快，还能包含其他select子句
 4. null
 	select * from 表名 where a1 is not null; //非空，与0、空字符串和空格不同

通配符like和正则表达式regexp

 1. like和通配符% _匹配
	select * from 表名 where a1 like '2%'; //%表示任意数量的任意字符
	select * from 表名 where a1 like '2_'; //_表示一个任意字符
	//这一般要注意字符前后空格，采用Ltrim和Rtrim和trim（去除左右空格）
 2. regexp和正则表达式. or [] * + ？ binary（区分大小写）
	select * from 表名 where a1 regexp binary '2'; //只要包含2都选出来，binary用来区分大小写
	//只写regexp后面的
	'2|3'//或
	'[123]s' //匹配多个字符之一  1或2或3 + 's' [^12]排除1或2
	'[1-5]' '[a-c]' //范围
	'\\\'   '\\n'  //特殊字符前面两个\\
	'{n,m}' \\前面那个字符出现n-m次
	'^' //文本的开始 $文本的结尾

like和regexp的区别在于，like关注的是列，而regexp关注的列值。如果匹配项在列值中like找不到，最明显的例子，'ha' like必须列值为ha才匹配，而regexp列值里只要有ha就匹配。hahaha这个like就匹配不了

拼接concat()、函数abs()avg()sum()min()max()

	upper() lower() //大小写转换
	length() locate() //字符串长度
	soundex() //发音相似
	这里面函数基本忽略null

过滤分组group by和having，以及子句顺序

 1. 纵向统计 with rollup
	select coalesce(a1,'总个数') as a1, count(*) from 表名 group by a1 with rollup; //coalesce是这一列里面的列值为a1对应，如果没有则为后一个。with rollup是纵向统计分组后的结果
 2. 分组过滤 having
	select count(*) from 表名 group by a1 having count(*)>2; 
 3. 子句顺序
	select  from where group-by having order-by limit

联结表 join

 1. 内部联结inner join或者where on
	select * from 表1 inner join 表2 on 表1.id=表2.id; //取交集，会出现重复的列，例如表1,2的id都会列出来
 2. 自然联结
 	select （列出所有需要的列，也就是说id只出现一次） from 表1，表2; //自然联结是内部联结的一种特殊情况，即相同的列只出现一次，将所有需要的select列都列出来，防止相等的列同时出现
 3. 外部联结left-join right-join
 	select a1,a2,b1,b2 from 表1 left join 表2 on 表1.id=表2.id; //left是以左表为准，右表有就出现；right是右表都出现，左表有就出现。

组合查询union

 1. 多子句union union-all
	select a1 from 表1 where a3<102 union select a1 from 表1 where a2<10; //必须包含相同的列，union隐藏相同的行，union all显示所有行，order by只能有一个，出现在最后一个子句

插入insert 更新update 删除delete

update和delete弄之前用select看看where对不对

 1. 插入insert-into
 	insert into 表名(a1,a2) values(1,'2'); 
	insert low_priority into 表名(a1,a2) values(1，'2'),(2,'4'); //low_priority降低优先级，先查询完所有的再插入。
 2. 利用insert as复制表
	create table 新表名 as select * from 表1；
 3. 更新update
	update ignore table set a1='2' where a2=4; //一定要加where要不全更新了,ignore多条更新时忽略错误
 4. 删除delete truncate
 	delete from 表名 where a1='2'; //delete删除行，可rollback
 	truncate table 表名;
 	delete from 表名; //truncate 删除表再重建，速度快

建表create-table 更新alter 删除drop table

 1. 创建create
 	create table 表名 if exists
 	(
 		id bigint not null auto_increment, //select last_insert_id()可知道最后插入的id，auto_increment 自增id
 		name char(50) not null,
 		age int null default 1, //默认1
 		primary key(id)，
 		foreign key(for_id) references for_table_name(for_id)
 	)enging=innodb; //引擎innodb只支持事物可靠，myisam只支持全文本搜索速度快，memory 存在内存临时表与myisam相似，外键不能跨引擎
 2. 更新表 alter
 	alter table 表名
 	add  constraint yueshu_id //外键约束名
 	foreign key(for_id) references for_table_name(for_id); //更新表一般都是针对外键添加和删除
 3. 删除表 drop
 	drop table 表名;
 4. 重命名 rename
	rename table 原表名 to 新表名; 

 	

数据类型

名称	类型	说明
INT	整型	4字节 ±22亿的范围
BIGINT	长整型	8字节 ±922亿
REAL	浮点型	4字节浮点数，±10^38
DOUBLE	浮点型	8字节浮点，±10^308
DECIMAL(M,N)	高精度小数	DECIMAL(20,10)20位中10位小数
CHAR(N)	字符串	固定长度N，只能存长度N的字符串
VARCHAR(N)	可变字符串	可变长度，最大为N，可取0-N位
BOOLEAN	布尔	True False
DATE	日期	1995-08-24
TIME	时间	11:30:21
DATETIME	日期时间	1995-08-24 11:30:21

视图view

不能重名，若使用时用的order-by会把视图里的order-by覆盖，不能索引，能和表一起用。

 1. 创建视图 create-view
	create view 视图名 as 
	select * from 表名; //视图是对mysql语句进行封装
 2. 删除视图 drop-view
 	drop view 视图名;
 3. 替换视图 replace
 	create or replace view 视图名; //没有同名的则创建，否则覆盖
 4. 查看视图语句
 	show create view 视图名;

存储过程procedure 游标cursor

相当于函数，注意函数里用; 如果在命令行里输入要将别的字符如//作为语句结束标志。
delimiter // 用//代替;作为语句结束标志
游标只能在存储过程中用

 1. 创建存储过程，包括循环fetch
	create procedure 过程名(
	in p0 bigint, //输入值
	out p1 decimal(8,2) //输出值
	)
	begin
	declare 游标名 cursor // 记录后面select中的每一行，自动加一
	for 
	select count(*) from 表名;
	
	declare down boolean default 0; //循环标志位
	declare continue handler for sqlstate '02000' set donw 1; // 02000是循环结束的标志
	open 游标名 //打开游标
	repeat //循环开始
		fetch 游标名 into p1; //将游标中的值，赋值给输出变量p1
	until done end repeat; //循环结束
	close 游标名
	end; //参数只能是数，不能是多行，多列
 2. 使用过程call
 	call 过程名(
 	@变量名1，
 	@变量名2
 	);
 	select @变量名2; //查看输出值
 3. 删除从存储过程
	drop procedure 过程名;
 4. 查看所有存储过程信息 show
 	show procedure status;
 5. 查看存储过程创建语句
 	show create procedure 过程名 like 'asd'; //加like可以指定输出

触发器 trigger

触发器在表内的，表内不可同名，只能对delete insert update使用

 1. 创建触发器
	create trigger 触发器名 after insert on 表名
	for each row select '添加了' //这样每次插入后，都会打印出 添加了，如果是before触发器，如果触发器失败了，不执行后面的命令
 2. insert 触发器
	create trigger 触发器名 after insert on 表名
	for each  row select new.num; //new为虚拟表，表示被插入的行，before触发器可以被更新，在auto_increment中执行前为0
 3. delete 触发器 和 begin-end语句
 	create trigger 触发器名 before delete on 表名
	for each row 
	begin 
		insert into 另一个表(a1,a2) values(old.a1,old.a2);
	end; //before触发器可实现删除前备份，old表示删除的行，好处是，如果转存失败则不删除。
 4. update触发器
 	create trigger 触发器名 before insert on 表名
 	for each row set new.a1=upper(new.a1); //可以用old访问更新之前的，new访问更新之后的。

事物处理 start transaction

一般情况mysql执行完一个语句会自动提交，要用这个一般就要关闭自动提交
set autocommit=0; //这个是针对每个连接也就是客户端，而不是服务器

 1. 事物开始start 提交commit
 	start transaction
 	delete from 表名 where a1='2';
 	commit;
 2. 事物回滚
	start transaction
	savepoint delete1;
	delete from 表名 where a1='2';
	rollback to delete1; //回滚到保留点delete1，如果没有则回到start之前,rollback和commit之后保留点自动释放或者，release savepoint 手动释放 

控制访问和服务器管理

 1. 查看用户名库
 	use mysql;
 	select user from user;
 2. 创建带密码的用户名
 	create user 用户名 identified by paasword('密码'); //paasword函数散列表加密方式
 3. 重命名 rename
 	rename user 旧用户名 to 新用户名;
 4. 删除 drop
 	drop user 用户名; //要先删除权限 revoke
 5. 权限 grant
 	show grants for 用户名; //查看有多少权限
 	grant select,delete,insert on 数据库名.* to 用户名; //给这个用户赋予查询这个数据库所有表的权限 逗号可多次授权
 	revoke select on 数据库名.* to 用户名; //删除权限，注意删除数据库或表，但用户对这个名的权限不会删除，如果再重建，用户对这个数据库或表，还有权限
	grant-all revoke-all 赋予它整个服务器权限
	on 数据库名.* 这个数据库权限
 	on 数据库名.表名 特定的表
 6. 设置和改密码
 	set password for 用户名 = password('密码');
	set paasword = password('密码');

数据库维护

 1. 查看键是否正常 analyze
 	analyze table 表名;
 2. 检查状态 check changed
 	check table 表名; //检查多种问题
 	/*
 	changed table 表名; //查看自上一次检查后修改的表
 	extended table 表名; //最彻底检查
 	fast table 表名; //检查为正常关闭的表
 	medium table 表名; //检查所有被删除的链接和键校验
 	quick table 表名; //快速扫描
*/
 3. 查看语句执行顺序explain
 	explain select * from 表名; //查看后面语句的执行情况