MySQL的基本用法和常用命令

水深无声 2022-07-15 06:45 241阅读 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，可以清屏

4. 查看mysql的字符集（假设用户名为root，密码为root）

mysql -uroot -proot

show variables like "character%";

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

需要修改my.ini配置文件。

如果是单独装的mysql服务器，找到客户端配置 \[client\] ，在下面添加：

default-character-set=utf8

找到服务器端配置 \[mysqld\] ，在下面添加：

default-character-set=utf8

如果是装的wampserver集成环境，找到客户端配置 \[client\] ，在下面添加：

default-character-set=utf8

找到服务器端配置 \[wampmysqld\] ，在下面添加：

character\_set\_server = utf8

保存后，重启mysql服务器和客户端。

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

打开命令行，连接mysql，输入：

set names gbk;

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

7. mysql中的注释

\-- 注释内容

\# 注释内容

8. 查看mysql中所有的数据库名

show databases;

或

show schemas;

9. 选择数据库（假设mysql中有一个数据库的名称为blog）

use blog;

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

SHOW TABLES;

11. 查看数据表的语法结构（假设数据库中有一张数据表的名称为blog\_user）

SHOW CREATE TABLE blog\_user;

12. 查看数据表的表结构

DESC blog\_user;

或

DESCRIBE blog\_user;

或

SHOW COLUMNS FROM blog\_user;

13. SQL语句的语法

SQL语句以分号结尾，默认分隔符。

SQL语句不区分大小写。

遵循系统命令、函数、关键字需要大写。

自己起的名字、数据库名、表名、字段名称需要小写。

当自己起的名称和mysql的关键字（保留字）冲突时，可以使用反引号\`\`，将名称括起来。

14. 创建数据库

CREATE DATABASE|SCHEMA \[IF NOT EXISTS\] db\_name;

说明：| 表示二选一，\[\] 表示可选

CREATE DATABASE IF NOT EXISTS blog;

或

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

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

SHOW CREATE DATABASE blog;

16. 修改数据库的编码方式

ALTER DATABASE blog DEFAULT CHARACTER SET 'utf8';

17. 得到已打开的数据库的名称

SELECT DATABASE();

18. 删除指定的数据库

DROP DATABASE | SCHEMA \[IF EXISTS\] blog;

19. 创建数据表

CREATE TABLE if not exists blog\_test(

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;

20. 查看MySQL中的帮助手册

?或者help

如：? tinyint 或 help tinyint

21. 字段的数据类型

整型：

tinyint -128 to 127 0 to 255

smallint -32768 to 32767 0 to 65535

mediumint -8388608 to 8388607 0 to 16777215

int -2147483648 to 2147483647 0 to 4294967295

bigint -9223372036854775808 to 9223372036854775807 0 to 18446744073709551615

浮点型：

float(M,D) M代表浮点数的总长度，D代表小数点后几位 如：float(10,2) 12345678.99

decimal(M,D) decimal(6) 等同于 decimal(6,0) numeric 和 decimal 是同一种类型

字符型：

char 定长字符型，必须指定长度 字符个数（长度） 0 到 255 如：char(32)，固定长度为32个字符。

varchar 变长字符型，必须指定长度 字符个数（长度） 如：varchar(300)，最大长度限制为300个字符。

注意：

varchar数据类型比较特殊，它所支持的字节数范围是0到65535，它的最大有效长度受限于整个数据行的最大字节数（65535个字节，所有的列共享这些字节）和所使用的字符集。存储varchar数据时，会使用1个或2个字节存储前缀，后面的字节来存储数据。如果数据的大小不大于255个字节，就使用1个字节存储前缀；反之，使用2个字节存储前缀。在utf8字符集下，一个汉字占3个字节，此时，varchar能够设置的最大长度是21844个字符。

定义char或varchar数据类型时，必须指定长度，这个长度指的是字符数。如果插入的数据长度超过指定的长度，就会自动截短为指定的长度。

文本型（变长，text同blob）：

tinytext 0 到 255 字节

text 0 到 65535 字节

mediumtext 0 到 16777215 字节

longtext 0 到 4294967295 字节

枚举型（变长）：

enum("值1","值2" ...) 最多列举65535个值

日期时间型：

date 'YYYY-MM-DD' 只包含日期 如：'2016-03-05'

time 'HH:MM:SS' 只包含时间 如：'08:20:03'

datetime 'YYYY-MM-DD HH:MM:SS' 包含日期和时间 如：'1926-03-05 08:20:03'

timestamp 'YYYY-MM-DD HH:MM:SS' '1970-01-01 00:00:00' UTC to '2038-01-19 03:14:07' UTC

注意：所有和日期时间相关的数据，都可以用整型 int 来存储，存储的是时间戳，方便计算和存储。

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

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

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

\\s 或 status

24. 删除数据表

drop table blog\_test;

或

drop table tbl\_name1,tbl\_name2 ...; \# 删除多个表

25. 字段约束

unsigned 无符号的（也就是只能为正数），只能限制数值型。

zerofill 零填充，整型的后面的小括号中的数值表示长度，当显示长度达不到指定长度的时候，会以0在前面补齐。

not null 非空。 尽量指定非空。

default 默认值，一般配合 not null 使用。 插入值时，如果不给这个字段分配值，将使用默认值。 尽量指定默认值。

\[primary\] key 主键，被标志成主键的字段自动非空，而且具有唯一性。 可以快速搜索定位某条记录，一般把主键定义在无意义的字段上（如：编号）。

注意：一张数据表只能有一个主键

auto\_increment 自动增长，只能标志在数值列。 被标志为自动增长的字段，必须是主键。

unique \[key\] 唯一性，被标志为唯一约束的字段的值不能重复，一张表中可以有多个唯一字段。

注意：被标志为自动增长的字段一定是主键，但主键不一定是自动增长。

26. 字段注释

comment 给字段添加注释，便于理解该字段。 如：comment "用户名"

27. 添加字段

alter table blog\_test add email char(30) not null default 'user@qq.com' after id;

28. 删除字段

ALTER TABLE tbl\_name DROP 字段名称;

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

ALTER TABLE tbl\_name MODIFY 字段名称 新类型 新属性;

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

ALTER TABLE tbl\_name CHANGE 原字段名称 新字段名称 新类型 新属性;

31. 设置字段的默认值

ALTER TABLE tbl\_name ALTER 字段名称 SET DEFAULT '值';

32. 删除字段的默认值

ALTER TABLE tbl\_name ALTER 字段名称 DROP DEFAULT;

33. 添加主键

alter table blog\_test add primary key(id);

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

alter table blog\_test modify id int;

alter table blog\_test drop primary key;

35. 添加唯一索引

alter table blog\_test add unique key uname(username);

uname是唯一索引的名称，如果不给名称，则索引名称默认和字段名称相同

或

alter table blog\_test add unique key(username);

36. 删除唯一索引

alter table blog\_test drop index uname;

或

alter table blog\_test drop key uname;

37. 添加普通索引

alter table blog\_test add key uname(username);

或

alter table blog\_test add index uname(username);

38. 删除普通索引

alter table blog\_test drop key uname;

或

alter table blog\_test drop index uname;

39. 查询数据表中的所有索引

show index from blog\_test;

40. 修改数据表的表名

ALTER TABLE tbl\_name RENAME \[TO|AS\] new\_tbl\_name;

或

RENAME TABLE src\_name TO new\_tbl\_name;

41. 修改自增长的值

ALTER TABLE tbl\_name AUTO\_INCREMENT=值;

42. 给数据表添加记录

INSERT \[INTO\] tbl\_name VALUES(值, ...);

表中的所有字段（自增字段可以不给值）都要给值，而且按照创建表时的字段顺序来写。如果字段类型非数值，则用引号括起来

INSERT \[INTO\] tbl\_name(字段名称, ...) VALUES(值, ...);

给表中的某些字段添加值，没给值的字段取默认值（如果没有默认值，则为空NULL）

INSERT \[INTO\] tbl\_name(字段名称, ...) VALUES(值, ...),(值, ...);

43. 更新记录

UPDATE tbl\_name SET 字段名称=值,字段名称=值... \[WHERE 条件\]

注意：如果不给条件，表中的所有记录都会被更新

44. 删除记录

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

45. 彻底清空表中的记录

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

46. 查询记录

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

select id,username from blog\_test;

47. 取消当前的命令

\\c

48. 给字段取别名

SELECT 字段名称 \[AS\] 别名名称 ,字段名称 \[AS\] 别名名称 ... FROM tbl\_name;

49. 给数据表取别名

SELECT 字段名称... FROM tbl\_name \[AS\] 别名;

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

select blog\_test.id,blog\_test.username from blog\_test;

或 select b.id,b.username from blog\_test b;

51. 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 ，但我们可以使用小括号指定优先级。

52. 排序 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;

53. 限制记录数 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用于删除或更新记录的时候，只能给一个值。

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

select id,username,password from blog\_test group by username;

username字段值相同的被分为同一个组，不同的被分为不同的组

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

select id,username,password from blog\_test group by username having id>3;

56. 聚合函数

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;

57. 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。

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

select rand();

select rand(3);

产生固定的随机数，数字相同，随机数就相同

select id,username from blog\_test order by rand();

可实现随机排序，但比较影响性能，尽量少用。

59. distinct 去除重复的值

select distinct username from blog\_test;

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

select id,username from blog\_test union select id,password from blog\_user;

注意：多个查询的查询的列数（字段数）必须相同，合并后，会去除重复的记录数。

61. 子查询

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

SELECT id,username FROM blog\_test WHERE id in (SELECT id FROM blog\_user);

由于子查询效率较低，一般不推荐使用，而是用多表关联查询替代。

62. 多表查询

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;

这种情况下，给了条件，就是正常的多表查询

63. 内连接 \[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

64. 外连接 \[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。暂时不支持。

65. 外键 foreign key

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

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

外键字段如果没有指定索引，会自动创建索引。

如果要删除父表（如分类表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。

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

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

66. 数据表的复制

复制表结构：

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

复制表数据

insert t2 select \* from t1;

或

insert into t2(username,password) select username,password from t1;

MySQL的基本用法和常用命令

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 rsync 基本命令和用法

相关 Linux 常用命令之 netstat 的10个基本用法

相关 gdb常用命令的用法

相关 mysql 常用基本命令

相关 HBase基本概念和hbase shell常用命令用法

相关 chroot命令的基本用法

相关 MySQL的基本用法和常用命令

相关 rsync：基本命令和用法

相关 jstat命令基本用法

相关 Linux常用基本命令：三剑客命令之-awk基础用法