MySQL的常用命令

1. 连接mysql服务器

打开命令行（开始菜单 + R ，输入cmd ，回车 ），输入下面的命令：

如果报错，可将mysql的bin目录加入windows系统的环境变量path中或切换至mysql的bin目录。

mysql -u用户名 -p密码
或
mysql -u用户名 -p
Enter Password:*****输入密码
或
mysql -h主机名称 -u用户名 -p密码
或
mysql -h主机名称 -u用户名 -p密码 -P3306
或
mysql -u用户名 -p密码 数据库名称
或
mysql -u用户名 -p密码 -D数据库名称

连接成功之后，可以看到命令提示符 mysql>

2. 退出mysql

exit 或 \q 或 quit 或 Ctrl+C

3. 清屏

退出mysql后，输入cls，可以清屏。
其实，cls命令是windows的清屏命令。

4. 查看mysql的默认字符集

show variables like "character%";

5. 统一mysql客户端和服务器端的字符集

修改my.ini配置文件，设置默认字符集为utf8。

如果是单独装的mysql，找到客户端配置 [client] ，在下面添加：
default-character-set=utf8 
找到服务器端配置 [mysqld] ，在下面添加：
default-character-set=utf8 

如果是装的wamp集成环境，找到客户端配置 [client] ，在下面添加：
default-character-set=utf8 
找到服务器端配置 [wampmysqld] ，在下面添加：
character_set_server = utf8
保存后，重启mysql服务器和客户端。

6. 临时设置客户端的字符集

set names gbk;	
关闭mysql连接后，会恢复到默认的配置文件中的字符集。

7. mysql中的注释

--  注释内容
#   注释内容

8. 查看所有的数据库名称

show databases;
或
show schemas;

9. 选择数据库

use blog;
选择blog数据库。（假设，blog数据库已存在）

10. 查看当前打开的数据库名称

SELECT DATABASE();

11. 查看选定数据库中的所有数据表

show tables;

12. 查看数据表的语法结构

SHOW CREATE TABLE blog_user;

13. 查看数据表的表结构

DESC blog_user;
或
DESCRIBE blog_user;
或 
SHOW COLUMNS FROM blog_user;

14. SQL语句的语法

SQL语句以分号结尾，默认分隔符。
SQL语句不区分大小写。
遵循系统命令、函数、关键字需要大写。
自己起的名字、数据库名、表名、字段名称需要小写。
当自己起的名称和mysql的关键字（保留字）冲突时，可以使用反引号``，将名称括起来。

15. 创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name;	
说明：| 表示二选一；[] 表示可选。

CREATE DATABASE IF NOT EXISTS blog;
或
CREATE DATABASE IF NOT EXISTS blog DEFAULT CHARACTER SET ='utf8';

16. 查看指定数据库的字符集

SHOW CREATE DATABASE blog;

17. 修改指定数据库的字符集

ALTER DATABASE blog DEFAULT CHARACTER SET 'utf8';

18. 删除指定的数据库

DROP DATABASE | SCHEMA [IF EXISTS] blog;

19. 查看当前数据库的状态

\s
或  
status

20. 创建数据表

CREATE TABLE if not exists blog_user(
id int unsigned not null auto_increment primary key comment '主键id',
username char(16) not null unique default "" comment '用户名',
password char(32) not null default "" comment '密码'
)engine=InnoDB default charset=utf8 auto_increment=1;

21. 查看帮助信息

? 或者 help

? tinyint	
或	
help tinyint

22. 清空数据表中的所有记录

truncate blog_user;		
# 如果数据表中包含主键字段，主键的自增起始值会恢复到初始状态1。

23. 删除数据表

drop table blog_test;
或
drop table tbl_name1,tbl_name2 ...;		
删除多个表

24. 添加字段

alter table blog_test add email char(30) not null default '' after id;

25. 删除字段

ALTER TABLE tbl_name DROP 字段名称

26. 修改字段的类型、属性

ALTER TABLE tbl_name MODIFY 字段名称 新类型 新属性;

27. 修改字段的名称、类型、属性

ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 新类型 新属性;

28. 设置字段的默认值

ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT '值';

29. 删除字段的默认值

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT;

30. 添加主键

alter table blog_test add primary key(id);

31. 删除主键

alter table blog_test modify id int;
alter table blog_test drop primary key;

注意：如果主键是自动增长，必须先删除自动增长，再来删除主键。

32. 添加唯一索引

alter table blog_test add unique key uname(username);
uname是唯一索引的名称，如果不给名称，则索引名称默认和字段名称相同
或
alter table blog_test add unique key(username);

33. 删除唯一索引

alter table blog_test drop index uname;
或
alter table blog_test drop key uname;

34. 添加普通索引

alter table blog_test add key uname(username);
或
alter table blog_test add index uname(username);
或
create index uname on blog_test(username);

35. 删除普通索引

alter table blog_test drop key uname;
或
alter table blog_test drop index uname;
或
drop index uname on blog_test;

36. 查看数据表中的所有索引

show index from blog_test;

37. 修改数据表的表名

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
或
RENAME TABLE src_name TO new_tbl_name;

38. 修改自增长的值

ALTER TABLE tbl_name AUTO_INCREMENT=值;

39. 给数据表添加记录

INSERT [INTO] tbl_name  VALUES(值, ...);
表中的所有字段都要给值（自增主键可以给 null 或 0）。
必须按照创建表时的字段顺序来写。如果字段类型非数值，则用引号括起来。

INSERT [INTO] tbl_name(字段名称, ...) VALUES(值, ...);
给表中的某些字段添加值，没给值的字段取默认值（如果没有默认值，则为空NULL）。

INSERT [INTO] tbl_name(字段名称, ...) VALUES(值, ...),(值, ...);

40. 更新记录

UPDATE tbl_name SET 字段名称=值,字段名称=值... [WHERE 条件]
注意：如果不给条件，表中的所有记录都会被更新。

41. 删除记录

DELETE FROM tbl_name [WHERE 条件];   
注意：如果没有条件，表中的所有记录都会被删除。

42. 彻底清空表中的记录

TRUNCATE [TABLE] tbl_name;  
清空表，可以将自增值恢复至初始状态。

43. 查询记录

SELECT * FROM tbl_name;   
不推荐使用select * , 因为效率较低，还不如将查询的字段都列出来。

select id,username from blog_test;

44. 取消当前的命令

\c

45. 给字段取别名

SELECT 字段名称 [AS] 别名名称, 字段名称 [AS] 别名名称 ... FROM tbl_name;

46. 给数据表取别名

SELECT 字段名称... FROM tbl_name [AS] 别名;

47. 查询记录时，附带字段的表名

select blog_test.id,blog_test.username from blog_test;
或 
select b.id,b.username from blog_test b;

48. WHERE 条件（可用于删、改、查）

条件的类型：
 = > >= < <= != <> 	
 
注意：由于空值null非常特殊，故这些比较运算符不能对null进行比较。

<> 代表不等于，和!=相同
如：select id,username from blog_test where id <> 2; 

<=> 也是表示等于，但是，它还可以对是否等于null进行判断
如：select * from blog_test where username <=> null;

is null
如：select * from blog_test where username is null;
等价于 
select * from blog_test where username <=> null;

is not null 
如：select * from blog_test where username is not null;

BETWEEN ... AND ...
如：select * from blog_test where id between 2 and 5;

NOT BETWEEN ... AND ... 
如：select * from blog_test where id not between 2 and 5;

IN(值1,值2, ...)
如：select * from blog_test where id in(1,2,6);

NOT IN(值1,值2, ...)
如：select * from blog_test where id not in(1,2,6);

LIKE 可以实现模糊查询
有以下几个匹配符：
%	代表0个1个或多个任意字符    
_ 	代表1个任意字符

如：select * from blog_test where username like "%ab%";

逻辑运算符: AND、OR
如：select * from blog_test where (username like "%ab%" or id!=3) and id<9;
注意：and 的优先级高于 or ，但我们可以使用小括号指定优先级。

49. 排序 ORDER BY

指定记录按照字段的排序规则，默认是升序排列。

select id,username,password from blog_test order by id;
等价于
select id,username,password from blog_test order by id asc;
select id,username,password from blog_test order by id desc;
还可以根据多个字段进行排序，如：
select id,username,password from blog_test order by id desc,username asc;

50. 限制记录数 LIMIT

select id,username,password from blog_test limit 3;
只显示前3条记录。

select id,username,password from blog_test limit 5,3; 
跳过前5条记录后，显示3条记录。

update blog_test set password='456789' where id>2 limit 3;

注意：当limit用于删除或更新记录的时候，只能给一个值。

51. 分组 GROUP BY （通常与聚合函数一起使用）

select id,username,password from blog_test group by username;
username字段值相同的被分为同一个组，不同的被分为不同的组。

注意：如果同时有GROUP BY 子句和ORDER BY子句，必须把GROUP BY 子句放在ORDER BY子句的前面，否则会报错。

52. 二次筛选 HAVING

二次筛选的条件，通常配合group by使用，表示对分组后的结果再进行筛选。

SELECT
	m.phone,
	m.real_name,
	m.tenant_name,
	sum(o.amount) total_amount,
	count(o.id) count
FROM
	`pay_merchant_order` o
JOIN pay_merchant m ON o.tenant_id = m.tenant_id
WHERE
	o.create_time BETWEEN '2017-08-01' AND '2017-08-31 23:59:59'
GROUP BY
	o.tenant_id
HAVING
	sum(o.amount) >= 1000 AND count(o.id) >= 10
ORDER BY
	count(o.id) DESC

53. 聚合函数

count() 统计记录数
select count(id) from blog_test;
select count(username) from blog_test;
如果username中有 null ，不会计算在内。
select count(*) from blog_test;
就算某个字段值中有 null ，也会计算在内。

MIN() 查询某个字段的最小值
select min(id) from blog_test;

MAX() 查询某个字段的最大值
select max(id) from blog_test;

SUM() 求和
select sum(id) from blog_test;

GROUP_CONCAT() 显示分组详情
select username,count(id) as ids,group_concat(id) as id_list from blog_test group by username;

54. concat() 合并多个字段，将多个字段的值作为字符串连接起来。

select id,username,password from blog_test where concat(username,password) is null;
select id,username,password from blog_test where concat(username,password) like "%a%";
select id,concat(username,"-",password) from blog_test;

注意：null 和任何字符串相连的结果为null。

55. rand() 产生一个 0 到 1 之间的随机数

select rand();
select rand(3);
产生固定的随机数，数字相同，随机数就相同。

select id,username from blog_test order by rand();
可实现随机排序，但比较影响性能，尽量少用。

56. distinct 去除重复的值

select distinct username from blog_test;

57. union 合并多个查询的结果集

select id,username from blog_test union select id,password from blog_user;
注意：多个查询的查询的列数（字段数）必须相同，合并后，会去除重复的记录数。

58. 子查询

将select语句的结果，作为其他语句（select,delete,update,insert) 的操作对象，称为子查询。子查询需要用小括号括起来。

SELECT id,username FROM blog_test WHERE id in (SELECT id FROM blog_user);
由于子查询效率较低，一般不推荐使用，而是用多表关联查询替代。

59. 多表关联查询

select a.id,a.username,b.age,b.sex from blog_user a,blog_profile b;
多表查询，如果没有条件，会产生笛卡尔积效果。这种数据记录基本没有意义。

select a.id,a.username,b.age,b.sex from blog_user a,blog_profile b where a.id = b.uid;
这种情况下，给了条件，就是正常的多表查询。

60. 内连接 [inner] join

select a.id,a.username,b.age,b.pic from blog_user a join blog_profile b where a.id = b.uid;
select a.id,a.username,b.age,b.pic from blog_user a join blog_profile b on a.id = b.uid;
select a.id,a.username,b.age,b.pic from blog_user a inner join blog_profile b on a.id = b.uid;
select a.id,a.username,b.age,b.pic from blog_user a inner join blog_profile b where a.id = b.uid;

以上几条sql语句都是等价的，内连接（多表联表查询）时，where 可以用 on 替代。但外链接的条件，只能用on。

61. 外连接 [outer] join

左外连接： left outer join （outer 可省略）。

select a.id,a.username,b.age,b.pic from blog_user a left outer join blog_profile b on a.id = b.uid;
select a.id,a.username,b.age,b.pic from blog_user a left join blog_profile b on a.id = b.uid;

右外连接： right join
select a.id,a.username,b.age,b.pic from blog_user a right join blog_profile b on a.id = b.uid;

全外连接： full join
暂时不支持。

join通常有下面几种类型，不同类型的join操作会影响返回的数据结果：

• INNER JOIN： 内连接，多个表中都匹配到了条件，就返回匹配的记录，也就是说只返回匹配的记录。 同join，使用的频率最多。
• LEFT JOIN： 左外连接，无论是否有匹配，以左表为主，返回左表中的所有记录，右表中如果没有相关的记录，查询的字段的值就为null。
• RIGHT JOIN： 右外连接，无论是否有匹配，以右表为主，返回右表中的所有记录，左表中如果没有相关的记录，查询的字段的值就为null。
• FULL JOIN：全外连接，无论是否有匹配，返回左表和右表中的所有记录，如果没有相关的记录，查询的字段的值就为null。暂时不支持。

62. 外键 foreign key

外键的作用：保证记录的一致性和完整性。

注意：只有InnoDB存储引擎的表才有外键的特性。

外键字段如果没有指定索引，会自动创建索引。
如果要删除父表（如分类表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。

创建表的时候，可以给字段设置外键，如：foreign key(cId) references cms_cate(id)。

由于外键的效率并不是很好，因此并不推荐使用外键，但我们要使用外键的思想来保证数据的一致性和完整性。

63. 数据表的复制

复制表结构：
create table t2 like t1; 
仅仅是复制表结构，也就是说t2表和t1表具有相同的表结构。

复制表数据：
insert t2 select * from t1;
或
insert into t2(username,password) select username,password from t1;