mysql远程连接数据库(MySQL与PostgreSQL最全SQL命令集汇总)

操作描述

命令

说明

本地连接

mysql -u 用户名 -p

-p后可直接跟密码（无空格），建议省略密码仅输-p，回车后隐式输入

远程连接

mysql -h 主机地址 -u 用户名 -p -P 端口号

默认端口3306，端口参数-P为大写

查看当前用户

SELECT USER();

返回当前登录的用户名及主机信息

退出连接

EXIT; 或 \q

两种命令效果一致，均退出MySQL命令行

操作描述

命令

说明

创建数据库

CREATE DATABASE 数据库名;

可加字符集约束：CREATE DATABASE 库名 DEFAULT CHARACTER SET utf8mb4;

删除数据库

DROP DATABASE 数据库名;

不可逆操作，删除前需备份数据

查看所有数据库

SHOW DATABASES;

列出当前实例下所有可用数据库

选择数据库

USE 数据库名;

切换至目标数据库，后续操作均针对该库

查看数据库详情

SHOW CREATE DATABASE 数据库名;

显示数据库创建语句及字符集、排序规则

修改数据库编码

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 编码 DEFAULT COLLATE 排序规则;

例：ALTER DATABASE test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

操作描述

命令

说明

创建表

CREATE TABLE 表名 (列名1 数据类型 约束, 列名2 数据类型 约束, ...);

例：CREATE TABLE user(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT DEFAULT 18);

删除表

DROP TABLE 表名;

不可逆，可加IF EXISTS避免报错：DROP TABLE IF EXISTS 表名;

查看所有表

SHOW TABLES;

显示当前数据库下所有表

查看表结构

DESC 表名; 或 SHOW COLUMNS FROM 表名;

DESC为简写，显示字段名、类型、约束等信息

查看建表语句

SHOW CREATE TABLE 表名;

显示表的完整创建语句，含索引、引擎等配置

添加列

ALTER TABLE 表名 ADD 列名 数据类型 约束;

可指定位置：ALTER TABLE 表名 ADD 列名 类型 约束 AFTER 目标列;

删除列

ALTER TABLE 表名 DROP 列名;

删除列会同时删除该列所有数据

修改列类型

ALTER TABLE 表名 MODIFY 列名 新数据类型 约束;

需注意数据类型兼容性，避免数据丢失

重命名表

ALTER TABLE 旧表名 RENAME TO 新表名;

也可使用RENAME TABLE 旧表名 TO 新表名;

操作类型

命令

说明

插入数据

INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);INSERT INTO 表名 VALUES (值1, 值2, ...);

第二种方式需按表字段顺序插入所有值，推荐第一种指定列名的方式

查询数据

SELECT 列1, 列2 FROM 表名 WHERE 条件 ORDER BY 列名 ASC/DESC LIMIT 条数;

*表示查询所有列，ORDER BY默认升序（ASC），LIMIT用于分页

更新数据

UPDATE 表名 SET 列1=值1, 列2=值2 WHERE 条件;

必须加WHERE条件，否则更新全表数据，风险极高

删除数据

DELETE FROM 表名 WHERE 条件;

无WHERE条件删除全表数据，可使用TRUNCATE 表名快速清空（不记录日志）

分组查询

SELECT 列名, COUNT(*) FROM 表名 GROUP BY 列名 HAVING 条件;

HAVING用于过滤分组结果，区别于WHERE过滤行数据

多表连接

SELECT * FROM 表1 INNER/LEFT/RIGHT JOIN 表2 ON 表1.id=表2.外键;

支持内连接、左连接、右连接，MySQL需通过UNION实现全外连接

操作描述

命令

说明

创建用户

CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

主机用%表示所有主机，localhost表示本地

授权用户

GRANT 权限 ON 数据库名.* TO '用户名'@'主机';

权限可设SELECT、INSERT、ALL等，*表示所有表

撤销权限

REVOKE 权限 ON 数据库名.* FROM '用户名'@'主机';

撤销指定权限，需与授权权限对应

刷新权限

FLUSH PRIVILEGES;

修改权限后需执行，使配置生效

删除用户

DROP USER '用户名'@'主机';

需指定完整的用户名和主机组合

操作描述

命令

说明

导出数据库

mysqldump -u 用户名 -p 数据库名 > 备份文件名.sql

在系统命令行执行，非MySQL命令行

导入数据库

mysql -u 用户名 -p 数据库名 < 备份文件名.sql

导入前需先创建目标数据库并选择

导出表数据

SELECT * INTO OUTFILE '文件路径' FIELDS TERMINATED BY ',' FROM 表名;

需配置secure_file_priv参数指定文件路径

操作描述

命令

查看MySQL版本

SELECT VERSION();

查看数据库状态

SHOW STATUS;

查看错误信息

SHOW ERRORS;

事务操作

BEGIN; （开始） COMMIT; （提交） ROLLBACK; （回滚）

处理NULL值

IFNULL(列名, 默认值)

操作描述

命令

说明

本地连接

psql -U 用户名 -d 数据库名 -W

默认端口5432，-W强制提示输入密码，默认数据库与用户名一致

远程连接

psql -h 主机地址 -p 端口号 -U 用户名 -d 数据库名 -W

例：psql -h 192.168.56.104 -p 5432 -U tony -d hrdb -W

查看帮助

\? （psql命令帮助） \h （SQL命令帮助）

在psql交互模式下执行，\h 命令名可查看指定SQL语法

退出连接

\q

唯一退出psql交互模式的命令

操作描述

命令（psql交互模式）

说明

创建数据库

CREATE DATABASE 数据库名;

也可通过createdb命令（系统命令行）：createdb -U 用户名 数据库名

删除数据库

DROP DATABASE 数据库名;

系统命令行：dropdb -U 用户名 数据库名，不可逆

查看所有数据库

\l （简写） 或 SELECT datname FROM pg_database;

\l+ 可查看更详细信息（含大小、编码）

切换数据库

\c 数据库名

等价于MySQL的USE命令，切换后操作针对目标库

创建模式

CREATE SCHEMA 模式名;

模式用于隔离表、视图等对象，类似命名空间

查看所有模式

\dn

显示当前数据库下所有模式

修改模式搜索路径

SET search_path TO 模式名;

指定默认访问的模式，避免手动写“模式名.表名”

操作描述

命令

说明

创建表

CREATE TABLE 表名 (列名1 数据类型 约束, 列名2 数据类型 约束, ...);

支持原生数组、范围等类型：CREATE TABLE test(arr INT[]);

删除表

DROP TABLE 表名;

加IF EXISTS避免报错：DROP TABLE IF EXISTS 表名;

查看所有表

\dt （当前模式） \dt *.* （所有模式）

\dt+ 显示表大小、描述等详情

查看表结构

\d 表名

\d+ 表名 显示更详细信息（含存储方式、约束说明）

添加列

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束;

支持多列同时添加，用逗号分隔

删除列

ALTER TABLE 表名 DROP COLUMN 列名;

可加CASCADE删除依赖该列的对象

重命名字段

ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名;

直接重命名，不改变数据类型和约束

重命名表

ALTER TABLE 旧表名 RENAME TO 新表名;

也可使用ALTER TABLE 表名 RENAME AS 新表名;

操作类型

命令

说明

插入数据

INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);INSERT INTO 表名 DEFAULT VALUES;

支持ON CONFLICT处理冲突：INSERT INTO 表名 VALUES(...) ON CONFLICT DO NOTHING;

查询数据

SELECT 列名 FROM 表名 WHERE 条件 ORDER BY 列名 LIMIT 条数 OFFSET 偏移量;

支持全外连接（FULL OUTER JOIN），OFFSET用于分页偏移

更新数据

UPDATE 表名 SET 列1=值1 WHERE 条件;

支持RETURNING子句返回更新后的数据：UPDATE 表名 SET ... RETURNING *;

删除数据

DELETE FROM 表名 WHERE 条件;

同样支持RETURNING子句，TRUNCATE 表名可快速清空表

处理NULL值

COALESCE(列名, 默认值)

等价于MySQL的IFNULL函数，替换NULL为指定值

递归查询

WITH RECURSIVE 递归表名 AS (初始查询 UNION ALL 递归查询) SELECT * FROM 递归表名;

原生支持递归查询，适合处理层级数据（如菜单、部门）

操作描述

命令

说明

创建角色（用户）

CREATE ROLE 用户名 WITH LOGIN PASSWORD '密码';

LOGIN权限允许角色登录，不加则仅为角色无法登录

查看角色

\du （简写） 或 SELECT * FROM pg_roles;

\du+ 显示角色详细权限信息

授权角色

GRANT 权限 ON 表名 TO 用户名;GRANT 角色名 TO 用户名;

权限可设SELECT、INSERT、ALL PRIVILEGES，支持授予角色给其他用户

撤销权限

REVOKE 权限 ON 表名 FROM 用户名;

撤销后需确保用户无其他途径获得该权限

修改密码

ALTER ROLE 用户名 WITH PASSWORD '新密码';

无需刷新权限，立即生效

删除角色

DROP ROLE 用户名;

需先撤销该角色的所有权限，否则加CASCADE强制删除

操作描述

命令（系统命令行）

说明

逻辑备份数据库

pg_dump -U 用户名 -d 数据库名 -f 备份文件名.sql

支持压缩备份：pg_dump -U 用户名 -d 库名 | gzip > 备份名.sql.gz

还原数据库

psql -U 用户名 -d 数据库名 -f 备份文件名.sql

压缩文件还原：gunzip -c 备份名.sql.gz | psql -U 用户名 -d 库名

导入/导出表数据

\copy 表名 TO '文件路径' WITH CSV;\copy 表名 FROM '文件路径' WITH CSV;

psql内置命令，无需超级权限，区别于COPY命令（需服务器文件权限）

备份整个集群

pg_basebackup -U 用户名 -D 备份目录 -F t -X stream

物理备份，适用于大规模数据库，支持增量备份

功能类型

命令

说明

创建索引

CREATE INDEX 索引名 ON 表名(列名);CREATE GIN INDEX 索引名 ON 表名(列名);（JSONB/数组）

支持B-tree、GiST、GIN等多种索引类型，适配不同场景

查看索引

\di （所有索引） \di 表名 （指定表索引）

\di+ 显示索引大小、状态等详情

创建函数

CREATE OR REPLACE FUNCTION 函数名(参数) RETURNS 类型 AS $$ BEGIN 函数体; END; $$ LANGUAGE plpgsql;

支持PL/pgSQL、Python等多种语言编写函数

查看函数

\df （所有函数） \df+ 函数名 （函数详情）

显示函数参数、返回值、定义语句等

时间函数

date_part('year', 日期列); （提取年份） CURRENT_TIMESTAMP; （当前时间）

替代MySQL的DATEDIFF等函数，功能更丰富

全文检索

SELECT * FROM 表名 WHERE to_tsvector('english', 列名) @@ to_tsquery('english', '关键词');

原生支持全文检索，性能优于MySQL