Mysql - 3 SQL语句

约 1574 字大约 5 分钟

SQL语句

结构化查询语言（Structured Query Language）简称SQL，这是一种特殊的语言，它专门用于数据库的操作。每一种数据库都支持SQL，但是他们之间会存在一些细微的差异，因此不同的数据库都存在自己的“方言”。

SQL语句不区分大小写（关键字推荐使用大写），它支持多行，并且需要使用 ; 结尾

SQL也支持注释，通过使用--或是#来编写注释内容，也可以使用/*来进行多行注释

四种 SQL 语言类型：

DDL: 数据库定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言
DML: 数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是开发以数据为中心的应用程序必定会使用到的指令。
DQL: 数据查询语言（Data Query Language, DQL）基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块。
DCL: 数据库控制语言（Data Control Language,DCL）。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。

DDL (数据定义语言)

库操作

操作	命令	说明
创建	`CREATE DATABASE db_name;`	新建数据库
查看	`SHOW DATABASES;`	列出所有数据库
使用	`USE db_name;`	切换数据库
修改	`ALTER DATABASE db_name CHARACTER SET utf8mb4;`	修改字符集
删除	`DROP DATABASE db_name;`	删除数据库（不可恢复）

创建数据库

-- 基本语法
CREATE DATABASE database_name;

-- 指定字符集
CREATE DATABASE database_name CHARACTER SET utf8mb4;

-- 指定字符集和排序规则
CREATE DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS database_name;

查看数据库

-- 查看所有数据库
SHOW DATABASES;

-- 查看数据库创建语句
SHOW CREATE DATABASE database_name;

-- 模糊查询数据库
SHOW DATABASES LIKE 'test%';

删除数据库

-- 基本语法
DROP DATABASE database_name;

-- 如果存在则删除
DROP DATABASE IF EXISTS database_name;

选择/使用数据库

-- 切换到指定数据库
USE database_name;

-- 查看当前使用的数据库
SELECT DATABASE();

修改数据库属性

-- 修改字符集
ALTER DATABASE database_name CHARACTER SET utf8mb4;

-- 修改字符集和排序规则
ALTER DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

表操作

操作类型	SQL 命令	示例
创建表	`CREATE TABLE`	`CREATE TABLE users (id INT PRIMARY KEY);`
查看所有表	`SHOW TABLES`	`SHOW TABLES;`
查看表结构	`DESC` / `DESCRIBE`	`DESC users;`
查看建表语句	`SHOW CREATE TABLE`	`SHOW CREATE TABLE users;`
添加列	`ALTER TABLE ... ADD`	`ALTER TABLE users ADD age INT;`
修改列类型	`ALTER TABLE ... MODIFY`	`ALTER TABLE users MODIFY age TINYINT;`
修改列名	`ALTER TABLE ... CHANGE`	`ALTER TABLE users CHANGE age user_age INT;`
删除列	`ALTER TABLE ... DROP`	`ALTER TABLE users DROP COLUMN age;`
修改表名	`ALTER TABLE ... RENAME TO`	`ALTER TABLE users RENAME TO user_info;`
删除表	`DROP TABLE`	`DROP TABLE IF EXISTS users;`
清空表	`TRUNCATE TABLE`	`TRUNCATE TABLE users;`

创建表

-- 基本语法
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

-- 完整示例
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    age INT DEFAULT 0 COMMENT '年龄',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 根据现有表创建新表（复制表结构）
CREATE TABLE new_table LIKE old_table;

-- 根据查询结果创建表（复制表结构和数据）
CREATE TABLE new_table AS SELECT * FROM old_table;

查看表

-- 查看当前数据库所有表
SHOW TABLES;

-- 查看其他数据库的表
SHOW TABLES FROM database_name;

-- 模糊查询表
SHOW TABLES LIKE 'user%';

-- 查看表结构
DESC table_name;

-- 查看建表语句
SHOW CREATE TABLE table_name;

-- 查看表状态信息
SHOW TABLE STATUS LIKE 'table_name';

修改表

通过alter table来进行修改

添加字段(列)：

ALTER TABLE 表名 ADD 字段名 数据类型(长度) [列级约束条件];

修改数据类型：

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

例：将emp表的nickname字段修改为username，类型为varchar(30)

ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

删除操作

删除字段：

ALTER TABLE 表名 DROP 字段名;

修改表名：

ALTER TABLE 表名 RENAME TO 新表名

删除表：

DROP TABLE [IF EXISTS] 表名;

删除表，并重新创建该表：

TRUNCATE TABLE 表名;

DML（数据操作语言）

操作	SQL 命令	说明
增	INSERT	插入数据
删	DELETE	删除数据
改	UPDATE	修改数据

插入数据

指定字段：

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

全部字段：

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

批量添加数据：

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

更新数据

UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];

例：

UPDATE emp SET name = 'Jack' WHERE id = 1;

删除数据

DELETE FROM 表名 [ WHERE 条件 ];

DCL (数据库控制语言)

创建用户

可以通过@来限制用户登录的登录IP地址，%表示匹配所有的IP地址，默认使用的就是任意IP地址

CREATE USER 用户名 IDENTIFIED BY 密码;

-- 示例
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'lisi'@'%' IDENTIFIED BY '123456';  -- %表示任意主机

登陆用户

login -u 用户名 -p

用户授权

通过使用grant来为一个数据库用户进行授权：

grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option]

其中all代表授予所有权限，当数据库和表为*，代表为所有的数据库和表都授权。如果在最后添加了with grant option，那么被授权的用户还能将已获得的授权继续授权给其他用户

可以使用revoke来收回一个权限：

revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户