ubuntu14.04中mysql常用命令

数据库操作：
1. 创建数据库：create database db_name;
2. 显示当前已有数据库：show databases;
3. 删除数据库： delete database db_name;
表操作：
4. 创建新表：
create table tb_name(
id int not null,
name varchar(20) null,
age int null,
constraint PK_TB_K1 primary key clustered (id)
);
5. 查看创建表的sql语句：show create table tb_name;
6. 查看表结构：desc(ribe) tb_name;
7. 显示当前已有表：show tables;
8. 删除表：drop table tb_name;
增、删、改、查：
9. 插入数据：insert into table tb_name(id, name) values(1,’coco’);
10. 删除数据：delete from tb_name [where…];
11. 删除主键：alter table tb_name drop primary key;
12. 增加主键：alter table tb_name add primary key(id)
列操作：
13. 增加列
alter table tb_name add salary varchar(10) not null default '100000'; //新增列在表的最后
alter table tb_name add salary varchar(10) not null default '10000'
first; //新增列在表的最开始
alter table tb_name add salary varchar(10) not null default '10000' after age; //新增列在指定列（age列）后
14. 删除列 alter table tb_name drop salary;
15. 修改列
alter table tb_name modify id int not null; //修改列类型
alter table tb_name change id uid int not null; //修改列名和列类型
存储过程：
16. 查询指定数据库中存储过程：
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'
17. 查询存储过程创建代码：show create procedure proc_name;
18. 创建存储过程
DELIMITER $ //声明分隔符
mysql> CREATE PROCEDURE proc1(OUT s int)
BEGIN
SELECT COUNT(*) INTO s FROM user;
END
$
mysql> DELIMITER ; //还原分隔符
19. 删除存储过程：drop procedure procdure_name;

触发器
20. 查询所有触发器：show trigger status [from db_name];
21. 查询触发器创建代码：show create trigger tri_name;
22. 删除触发器：drop trigger tri_name;
23. 创建触发器：
CREATE TRIGGER trigger_name
trigger_time //before，after
trigger_event ON tbl_name //insert，delete，update
FOR EACH ROW
trigger_stmt //不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器
变量
24. 局部变量
定义：DECLARE variable_name [,variable_name...] datatype [DEFAULT value]; //declare num int default 0;
使用：create procedure myadd(in a int, in b int)
begin
declare c int default 0; //声明局部变量，作用域只在存储过程中
set c=a+b;
select c as sum;
end
25. 用户变量
定义：select 'hello world' into @uservar;
set @uservar=’hello coco’;
查看：select @uservar;
使用create procedure myadd(in a int, in b int)
begin
select @sum:=(a+b) as sum; //@用户变量，作用域为当前整个连接，连接断开用户变量消失。PS：select语句中=为比较，用 :=赋值
end
26. 会话变量
27. 全局变量
28. 变量赋值
set [@]var=value;
select name,age into @v_name,@v_age from tb_name where id='2312'
内置函数：
29. concat：字符串连接select concat(@name, ' haha');
导入到处数据库/表：
30. 导出数据库结构：mysqldump -uroot -p123456 -d db_name > *.sql;
31. 导出数据库（包含数据）：mysqldump -uroot -p123456 db_name > *.sql;
32. 导出数据表结构：mysqldump -uroot -p123456 -d db_name tb_name > *.sql
33. 导出数据表：mysqldump -uroot -p123456 db_name tb_name > *.sql
34. 导入数据表：mysql … < xx.sql或者Mysql中source xx.sql
查看数据库/表信息
34. 查看整个数据库的大小：
select concat(round(sum((data_length+index_length)/1024/1024),2),'MB') from information_schema.tables;
35. 查看指定数据库大小：
select concat(round(sum((data_length+index_length)/1024/1024),2),'MB') from information_schema.tables where table_schema='db_name';
36. 查看指定表大小：
select concat(round(sum((data_length+index_length)/1024/1024),2),'MB');
37.查看数据库或表的创建/修改时间：
select update_time,create_time from information_schema.tables [where table_schema='db_name' and table_name='tb_name'];
38. 竖排显示查询结果，语句后加\G
编码设置与修改：
37. 修改表编码：alter table tb_name convert to character set gbk
数据库端口查看与修改：
38. 查看数据库端口，进入mysql，show global variables like 'port';
39. 修改数据库端口，修改配置文件my.cnf中mysqld标签下的port项
允许远程登陆
40. 添加用户并赋予指定权限：grant insert[select|delete|update|all privileges] on *.* to root@localhost["%"|'ip'] identified by "passwd";
41. 修改配置文件以允许远程登陆：注释掉my.cnf中bind-address = 127.0.0.1行，重新启动mysqld服务
* 导出数据库记录到文件*
select * into outfile ‘/tmp/1.txt’ fields terminated by ‘—’ from tb_name;
修改表的存储引擎
1.alter table mytable engine=InnoDB; //缺点时执行时间会相对较长，因为mysql会将原表数据拷贝到一张新表中，在复制期间会消耗系统I/O能力，而且会在原表上加读锁。
2.导入与到出，即用mysqldump工具将数据导出到文件，然后修改文件中create table语句中的存储引擎选项。
3.创建与查询，即创建与原表结构相同但存储引擎不同的表，把原表中数据导入进来就可以了，数据量大时可以分批处理(必要时对原表加锁，以保证新表和原表的数据一致），示例如下：
create table innodb_table like mysiam_table;
alter table innodb_table engine=InnoDB;
insert into innodb_table select * from mysiam_table;--->
start transaction;
insert into innodb_table select * from myisam_table where id between x and y;
commit;
4.