MySQL的常用命令

Bertha 。 2022-06-08 14:59 645阅读 0赞

### 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. 取消当前的命令 ###

### 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;