mysql常用命令

mysql命令

查询

#限制返回个数及偏移量：
#m: 偏移量，n: 个数，只限制个数可省略m，如limit 2
select * from <talbe_name> limit m, n

#排序：
#按col1排序，col1值相同再按col2排序，ASC: 升序，DESC: 降序
select * from <talbe_name> order by <col1>, <col2> [ASC|DESC]

#判断空值
select * from <talbe_name> where <col> is null

#指定范围, 查询col1在或不在0、1之间的行
select * from <talbe_name> where <col> [not] in (1, 2)

#使用通配符, %: 匹配多个任意字符，_: 匹配单个任意字符
select * from <talbe_name> where <col> like '%name_'

#使用正则表达式匹配子串
select * from <talbe_name> where <col> regexp '^[0-9]'

#concat()函数将多列输出按格式拼接在一列，RTrim()、LTrim()删除结果中右侧、左侧空格
select concat(<col1>, ' (', <col2>, ')') as <alias> from <talbe_name>;

#使用+、-、*、/计算两字段的结果
select <col1>, <col2>, <col1>*<col2> as <alias> from <talbe_name>;

更新

update <talbe_name> set user = '' where <condition>

插入

insert into <talbe_name> values(<value>, ... ), (<value>, ... )

删除

delete from <talbe_name> where <condition>

#删除表中所有数据
delete from <talbe_name>
truncate table <talbe_name> #速度快，但不能回滚，binlog不会记录日志

修改表结构

#添加列
alter table <talbe_name> add <column_name> <datatype>
#删除列
alter table <talbe_name> drop <column_name>
#改变列的数据类型
alter table <talbe_name> <modify|change> column <column_name> <datatype>

#添加索引
alter table <talbe_name> add index <index_name> (column_name)
create index <index_name> on <talbe_name> (column_name)
alter table <talbe_name> add primary key(colunm_name) #添加主键
alter table <talbe_name> add unique <index_name>(column_name) #创建唯一索引（除了NULL外，NULL可能会出现多次）。
alter table <talbe_name> add fulltext <index_name>(column_name) #创建全文索引

#删除索引
alter table <talbe_name> drop index <index_name>

复制表

create table <dst_talbe_name> like <src_talbe_name>;
insert into <dst_talbe_name> select * from <src_talbe_name>;

导出数据

mysqldump -uroot -p <database_name> [table_name] > database_dump.txt
#备份所有数据库
mysqldump -u root -p --all-databases > database_dump.txt

导入数据

mysql -u root -p <database_name> < dump.txt

存储过程

存储过程编写及调用示例

 #将语句结束符从;临时替换成//
mysql> delimiter //
#创建存储过程，括号中为参数，in：输入，out：输出
mysql> create procedure test(in p_in int, out p_out int) 
    -> begin
    -> select p_in;
    -> set p_out = 2;
    -> end//
#将语句结束符恢复;
mysql> delimiter ;
#变量赋值
mysql> set @p_in = 1;
#调用存储过程
mysql> call test(@p_in, @p_out);
+------+
| p_in |
+------+
|    1 |
+------+
#打印输出参数
mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+

存储过程中的控制语句

#变量存在作用域
mysql> delimiter //
mysql> create procedure test()
    -> begin
    -> 	declare x1 int default 1;
    -> 		begin
    -> 			declare x1 int default 2;
    -> 			select x1;
    -> 		end;
    -> 	select x1;
    -> end//
mysql> delimiter ;
mysql> call test;
+------+
| x1   |
+------+
|    2 |
+------+
+------+
| x1   |
+------+
|    1 |
+------+

#条件语句
if x1=1 then 
	...;  
else 
	...;  
end if; 

#case语句
case var  
    when 0 then   
        ...;  
    when 1 then   
        ...;  
    else   
        ...;  
end case;  

#循环语句
declare v int default 0;
#repeat
repeat  
    set v = v + 1;
    until v > 5
end repeat;
#while
while v < 6 do
	set v = v + 1;
end while;
#loop
# iterate类似于continue
# leave类似于break
LOOP_LABLE:loop
	if v=3 then
		set v=v+1;
		iterate LOOP_LABLE;
	end if;
	set v=v+1;
	if v>=5 then
		leave LOOP_LABLE;
	end if;
end loop;

查询数据库中的存储过程

select name from mysql.proc where db='<db_name>'
select routine_name from information_schema.routines where routine_schema='<db_name>'
show procedure status where db='<db_name>'

删除数据库中的存储过程

drop procedure <procedure_name>

事务

begin、start transaction：显示开始一个事务
commit：提交事务
rollback：回滚事务
savepoint <point_name>：创建一个保存点，一个事务中可以有多个保存点
release savepoint <point_name>：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常
rollback to <point_name>：把事务回滚到指定保存点
show global variables like '%isolation%'：查看隔离级别
set transaction_isolation='repeatable-read'：设置隔离级别，read-uncommitted、read-committed、repeatable-read、serializable