以下是 MySQL 5.7 中57个关键字的用法举例:
- ADD:用于添加新列、索引、约束等,例如:
ALTER TABLE table_name ADD new_column INT(11) NOT NULL;
- ALL:用于指示 SQL 操作应对所有行或所有列执行,例如:
SELECT ALL column_name FROM table_name;
- ALTER:用于修改表的结构,例如:
ALTER TABLE table_name MODIFY old_column VARCHAR(100);
- ANALYZE:用于收集并分析表和索引的统计信息,例如:
ANALYZE TABLE table_name;
- AND:用于连接两个或多个条件,例如:
SELECT * FROM table_name WHERE column_name1 < 100 AND column_name2 = 'some_value';
- AS:用于为查询中的表或列指定别名,例如:
SELECT column_name1 AS some_alias, column_name2 AS another_alias FROM table_name;
- ASC:用于按升序排序查询结果,例如:
SELECT * FROM table_name ORDER BY column_name ASC;
- ASENSITIVE:用于指示搜索时不区分大小写,例如:
SELECT * FROM table_name WHERE UPPER(column_name) = 'SOME_VALUE' COLLATE utf8_general_ci;
- BEFORE:用于在触发器中指定操作时间,例如:
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;
- BETWEEN:用于指定一个区间,例如:
SELECT * FROM table_name WHERE column_name BETWEEN 100 AND 200;
- BIGINT:用于定义大整数类型,例如:
CREATE TABLE table_name (column_name BIGINT);
- BINARY:用于指定二进制数据类型,例如:
CREATE TABLE table_name (column_name BINARY(100));
- BLOB:用于指定二进制大对象类型,例如:
CREATE TABLE table_name (column_name BLOB);
- BOTH:用于指示两个条件都必须成立,例如:
SELECT * FROM table_name WHERE column_name1 > 100 AND column_name2 < 200;
- BY:用于分组查询结果,例如:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
- CALL:用于调用存储过程或函数,例如:
CALL procedure_name();
- CASCADE:用于设置删除或更新主表时级联删除或更新从表中的相关记录,例如:
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column) ON DELETE CASCADE;
- CASE:用于在查询中进行条件判断,例如:
SELECT column_name, CASE WHEN column_name1 > 100 THEN 'large' ELSE 'small' END AS size FROM table_name;
- CHANGE:用于修改列的数据类型、名称和默认值,例如:
ALTER TABLE table_name CHANGE old_column new_column VARCHAR(100) DEFAULT 'some_value' NOT NULL;
- CHAR:用于指定固定长度的字符串类型,例如:
CREATE TABLE table_name (column_name CHAR(100));
- CHARACTER:用于指定字符数据类型,例如:
CREATE TABLE table_name (column_name CHARACTER(10));
- CHECK:用于指定列的验证规则,例如:
CREATE TABLE table_name (column_name INT(11) CHECK(column_name > 0));
- COLLATE:用于指定字符集和排序规则,例如:
SELECT * FROM table_name WHERE column_name LIKE '%some_value%' COLLATE utf8_general_ci;
- COLUMN:用于定义表中的列,例如:
CREATE TABLE table_name (column_name1 INT(11), column_name2 VARCHAR(100) DEFAULT 'some_value');
- CONDITION:用于在存储过程或函数中定义条件并执行相应操作,例如:
DECLARE condition_name CONDITION FOR SQLSTATE '42000';
- CONSTRAINT:用于定义表中的键和约束,例如:
CREATE TABLE table_name (column_name INT(11), CONSTRAINT constraint_name PRIMARY KEY (column_name));
- CONTINUE:用于跳过迭代中的当前循环并执行下一次循环,例如:
WHILE condition DO IF some_condition THEN CONTINUE; END IF; END WHILE;
- CONVERT:用于将一个数据类型转换为另一个数据类型,例如:
SELECT CONVERT(column_name, UNSIGNED) FROM table_name;
- CREATE:用于创建表、索引、存储过程和函数等,例如:
CREATE TABLE table_name (column_name1 INT(11), column_name2 VARCHAR(100));
- CROSS:用于返回符合两个或多个表之间所有条件的所有记录,例如:
SELECT * FROM table_name1 CROSS JOIN table_name2;
- CURRENT_DATE:用于返回当前日期,例如:
SELECT CURRENT_DATE();
- CURRENT_TIME:用于返回当前时间,例如:
SELECT CURRENT_TIME();
- CURRENT_TIMESTAMP:用于返回当前日期和时间,例如:
SELECT CURRENT_TIMESTAMP();
- CURRENT_USER:用于返回当前用户,例如:
SELECT CURRENT_USER();
- CURSOR:用于在存储过程或函数中遍历结果集,例如:
DECLARE cursor_name CURSOR FOR SELECT column_name FROM table_name;
- DATABASE:用于指定当前数据库,例如:
USE database_name;
- DATABASES:用于返回 MySQL 服务器上的所有数据库名称,例如:
SHOW DATABASES;
- DAY_HOUR:用于格式化日期时间值为天数和小时数,例如:
SELECT DATE_FORMAT(date_time, '%d:%H') FROM table_name;
- DAY_MICROSECOND:用于格式化日期时间值为天数和微秒数,例如:
SELECT DATE_FORMAT(date_time, '%d:%f') FROM table_name;
- DAY_MINUTE:用于格式化日期时间值为天数和分钟数,例如:
SELECT DATE_FORMAT(date_time, '%d:%i') FROM table_name;
- DAY_SECOND:用于格式化日期时间值为天数和秒数,例如:
SELECT DATE_FORMAT(date_time, '%d:%s') FROM table_name;
- DEC:用于定义小数类型,例如:
CREATE TABLE table_name (column_name DECIMAL(10, 2));
- DECIMAL:用于定义小数类型,例如:
CREATE TABLE table_name (column_name DECIMAL(10, 2));
- DECLARE:用于声明变量并指定数据类型,例如:
DECLARE variable_name INT(11);
- DEFAULT:用于指定列的默认值,例如:
CREATE TABLE table_name (column_name VARCHAR(100) DEFAULT 'some_value');
- DELAYED:用于延迟对表进行写入操作,例如:
INSERT DELAYED INTO table_name VALUES (...);
- DELETE:用于删除表中的记录,例如:
DELETE FROM table_name WHERE some_condition;
- DESC:用于按降序排序查询结果,例如:
SELECT * FROM table_name ORDER BY column_name DESC;
- DESCRIBE:用于返回表结构的详细信息,例如:
DESCRIBE table_name;
- DETERMINISTIC:用于指示存储过程或函数是否总是返回相同的结果,例如:
CREATE FUNCTION function_name(...) RETURNS INT(11) DETERMINISTIC BEGIN ... END;
- DISTINCT:用于返回不同的值并删除重复值,例如:
SELECT DISTINCT column_name FROM table_name;
- DISTINCTROW:用于返回不同的行并删除重复记录,例如:
SELECT DISTINCTROW column_name1, column_name2 FROM table_name;
- DIV:用于计算两个数相除的商,例如:
SELECT 9 DIV 4;
- DOUBLE:用于定义双精度浮点数类型,例如:
CREATE TABLE table_name (column_name DOUBLE);
- DROP:用于删除表、索引、存储过程和函数等,例如:
DROP TABLE IF EXISTS table_name;
- DUAL:用于返回单行单列的虚拟表,例如:
SELECT 1 + 2 FROM dual;
- EACH:用于指示在循环中迭代每个元素,例如:
LOOP label_name: EACH row_variable IN cursor_name DO ... END LOOP;
好的,以下是 MySQL 5.7 中57个关键字的用法举例的续集:
- ELSE:用于在条件语句中指定当条件不满足时执行的操作,例如:
IF some_condition THEN SELECT 'yes'; ELSE SELECT 'no'; END IF;
- ELSEIF:用于在条件语句中指定另一个条件并执行相应操作,例如:
IF some_condition THEN SELECT 'yes'; ELSEIF another_condition THEN SELECT 'maybe'; ELSE SELECT 'no'; END IF;
- ENCLOSED:用于设置 CSV 文件中字符字段的包装符号,默认为双引号,例如:
LOAD DATA INFILE 'file_name.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"';
- ESCAPED:用于设置 CSV 文件中特殊字符的转义符,默认为反斜杠符号,例如:
LOAD DATA INFILE 'file_name.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\';
- EXISTS:用于检查子查询是否返回任何记录,例如:
SELECT column_name FROM table_name WHERE EXISTS(SELECT * FROM another_table WHERE column_name1 = table_name.column_name2);
- EXIT:用于跳出迭代并停止执行循环,例如:
WHILE some_condition DO IF another_condition THEN LEAVE label_name; END IF; END WHILE;
- FALSE:用于表示布尔值的假值,例如:
IF 0 = 1 THEN SELECT 'true'; ELSE SELECT 'false'; END IF;
- FETCH:用于从游标中获取下一行数据,例如:
FETCH cursor_name INTO variable1, variable2, ...;
- FLOAT:用于定义单精度浮点数类型,例如:
CREATE TABLE table_name (column_name FLOAT);
- FOR:用于指定循环的条件和迭代方式,例如:
FOR i IN 1..10 LOOP SELECT i; END LOOP;
- FORCE:用于强制 MySQL 使用指定的索引,例如:
SELECT * FROM table_name FORCE INDEX(index_name);
- FOREIGN:用于定义表之间的外键关系,例如:
CREATE TABLE table_name1 (column_name INT(11), FOREIGN KEY (column_name) REFERENCES table_name2(column_name));
- FROM:用于指定要查询的表,例如:
SELECT column_name1, column_name2 FROM table_name1, table_name2 WHERE table_name1.column_name = table_name2.column_name;
- FULLTEXT:用于定义全文索引,例如:
CREATE TABLE table_name (column_name TEXT, FULLTEXT(column_name));
- GRANT:用于授予用户访问和权限,例如:
GRANT SELECT, INSERT ON database_name.table_name TO 'user_name'@'host_name' IDENTIFIED BY 'password';
- GROUP:用于根据一个或多个列对查询结果进行分组,例如:
SELECT column_name1, COUNT(column_name2) FROM table_name GROUP BY column_name1;
- HAVING:用于在 GROUP BY 子句中过滤分组后的结果,例如:
SELECT column_name1, COUNT(column_name2) FROM table_name GROUP BY column_name1 HAVING COUNT(column_name2) > 10;
- HIGH_PRIORITY:用于优先处理 SELECT 语句,例如:
SELECT HIGH_PRIORITY column_name FROM table_name;
- IF:用于在查询中进行条件判断,例如:
SELECT column_name1, IF(column_name2 > 100, 'large', 'small') AS size FROM table_name;
- IGNORE:用于在插入数据时忽略重复的记录,例如:
INSERT IGNORE INTO table_name VALUES (...);
- IN:用于指定一个值集合或子查询,例如:
SELECT * FROM table_name WHERE column_name IN (100, 200, 300);
- INDEX:用于定义表上的索引,例如:
CREATE TABLE table_name (column_name1 INT(11), column_name2 VARCHAR(100), INDEX index_name(column_name1, column_name2));
- INFILE:用于将 CSV 文件导入到表中,例如:
LOAD DATA INFILE 'file_name.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
- INNER:用于返回符合两个或多个表之间共有的条件的记录,例如:
SELECT * FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
- INOUT:用于在存储过程或函数中传递输入和输出参数,例如:
CREATE PROCEDURE procedure_name(IN input_parameter INT(11), INOUT output_parameter VARCHAR(100))
BEGIN SET output_parameter = CONCAT('Hello, ', input_parameter); END;
- INT:用于定义整数类型,例如:
CREATE TABLE table_name (column_name INT(11));
- INTEGER:用于定义整数类型,例如:
CREATE TABLE table_name (column_name INTEGER);
- INTERVAL:用于计算日期和时间差异,例如:
SELECT DATE_ADD('2022-06-18', INTERVAL 1 MONTH);
- INTO:用于将查询结果存储到变量中,例如:
SELECT column_name INTO variable_name FROM table_name WHERE some_condition;
- IS:用于比较两个值是否相等或不等,例如:
SELECT * FROM table_name WHERE column_name IS NULL;
- ITERATE:用于跳过迭代中的当前循环并继续执行下一次循环,例如:
WHILE condition DO IF some_condition THEN ITERATE label_name; END IF; END WHILE;
- JOIN:用于连接两个或多个表,例如:
SELECT * FROM table_name1 JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
- KEY:用于定义表上的键,例如:
CREATE TABLE table_name (column_name1 INT(11), column_name2 VARCHAR(100), PRIMARY KEY (column_name1));
- KEYS:用于返回指定表上的所有索引名称,例如:
SHOW KEYS FROM table_name;
- KILL:用于终止正在运行的进程,例如:
KILL process_id;
- LEADING:用于指示 MySQL 使用索引中的第一列进行索引扫描,例如:
SELECT * FROM table_name WHERE column_name1 = 100 AND column_name2 = 200 ORDER BY column_name1;
- LEFT:用于返回左边表中所有记录以及右边表中符合条件的记录,例如:
SELECT * FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
- LIKE:用于模糊匹配文本值,例如:
SELECT * FROM table_name WHERE column_name LIKE '%some_value%';
- LIMIT:用于限制查询返回的记录数量,例如:
SELECT * FROM table_name LIMIT 10 OFFSET 20;
- LINEAR:用于强制 MySQL 使用线性查询扫描,例如:
SELECT * FROM table_name FORCE INDEX(index_name) WHERE column_name = 100;
- LINES:用于控制 CSV 文件中的行终止符,例如:
LOAD DATA INFILE 'file_name.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
- LOAD:用于将数据从一个文件加载到表中,例如:
LOAD DATA INFILE 'file_name.txt' INTO TABLE table_name;
- LOCALTIME:用于返回当前时间,例如:
SELECT LOCALTIME();
- LOCALTIMESTAMP:用于返回当前日期和时间,例如:
SELECT LOCALTIMESTAMP();
- LOCK:用于锁定表,并防止其他进程修改数据,例如:
LOCK TABLES table_name WRITE;
- LONG:用于定义长文本类型,例如:
CREATE TABLE table_name (column_name LONGTEXT);
- LONGBLOB:用于定义二进制大对象类型,例如:
CREATE TABLE table_name (column_name LONGBLOB);
- LONGTEXT:用于定义长文本类型,例如:
CREATE TABLE table_name (column_name LONGTEXT);
- LOOP:用于创建无限循环,例如:
LOOP SELECT column_name FROM table_name; END LOOP;
- LOW_PRIORITY:用于指定 INSERT 语句的优先级,例如:
INSERT LOW_PRIORITY INTO table_name VALUES (...);
- MATCH:用于在全文索引中搜索匹配项,例如:
SELECT * FROM table_name WHERE MATCH (column_name) AGAINST ('some_value');
- MEDIUMBLOB:用于定义中等大小的二进制大对象类型,例如:
CREATE TABLE table_name (column_name MEDIUMBLOB);
- MEDIUMINT:用于定义中等大小的整数类型,例如:
CREATE TABLE table_name (column_name MEDIUMINT(11));
- MEDIUMTEXT:用于定义中等大小的文本类型,例如:
CREATE TABLE table_name (column_name MEDIUMTEXT);
- MIDDLEINT:用于定义中等大小的整数类型,例如:
CREATE TABLE table_name (column_name MIDDLEINT(11));
- MINUTE_MICROSECOND:用于将分钟和微秒数表示为时间,例如:
SELECT CAST('12:30.123456' AS TIME(6)) AS result;
- MINUTE_SECOND:用于将分钟和秒数表示为时间,例如:
SELECT CAST('12:30:45' AS TIME) AS result;
- MOD:用于计算两个数的模数,例如:
SELECT 10 % 3 AS result;
- MODIFY:用于修改表上的列定义,例如:
ALTER TABLE table_name MODIFY column_name VARCHAR(100);
- MONTH:用于从日期中提取月份部分,例如:
SELECT MONTH('2022-06-18') AS result;
- NOT:用于取反逻辑值,例如:
SELECT NOT 0 AS result;
- NULL:用于表示空值,例如:
SELECT NULL;
- NUMERIC:用于定义浮点数类型,例如:
CREATE TABLE table_name (column_name NUMERIC(10,2));
- ON:用于指定在表之间建立关系时触发的操作,例如:
CREATE TABLE table_name1 (column_name INT(11), FOREIGN KEY (column_name) REFERENCES table_name2(column_name) ON DELETE CASCADE);
- OPTIMIZE:用于优化表的性能,例如:
OPTIMIZE TABLE table_name;
- OPTION:用于在连接字符串中指定 MySQL 客户端选项,例如:
mysql -u user_name -p --default-character-set=utf8mb4
- OPTIONALLY:用于在插入数据时指定可选的列,例如:
INSERT INTO table_name (column_name1, column_name2) VALUES (100, 'some_value') ON DUPLICATE KEY UPDATE column_name1 = VALUES(column_name1);
- OR:用于指定多个条件中的任意一个成立即可,例如:
SELECT * FROM table_name WHERE column_name1 = 'some_value' OR column_name2 > 10;
- ORDER:用于对查询结果进行排序,例如:
SELECT * FROM table_name ORDER BY column_name1 DESC, column_name2 ASC;
- OUT:用于在存储过程或函数中传递输出参数,例如:
CREATE PROCEDURE procedure_name(IN input_parameter INT(11), OUT output_parameter VARCHAR(100))
BEGIN SET output_parameter = CONCAT('Hello, ', input_parameter); END;
- OUTER:用于返回左边表和右边表中所有记录,并匹配符合条件的记录,例如:
SELECT * FROM table_name1 OUTER JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
- OUTFILE:用于将表数据导出到 CSV 文件中,例如:
SELECT * FROM table_name INTO OUTFILE 'file_name.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
- PERIOD_ADD:用于给日期加上指定的时间段,例如:
SELECT PERIOD_ADD(202206, 3) AS result;
- PERIOD_DIFF:用于计算两个日期之间的月数,例如:
SELECT PERIOD_DIFF(202206, 202203) AS result;
- PRECISION:用于定义浮点数类型的精度,例如:
CREATE TABLE table_name (column_name FLOAT(10,2));
- PRIMARY:用于定义表中的主键,例如:
CREATE TABLE table_name (column_name1 INT(11), column_name2 VARCHAR(100), PRIMARY KEY (column_name1));
- PROCEDURE:用于定义存储过程,例如:
CREATE PROCEDURE procedure_name (input_parameter INT(11), output_parameter VARCHAR(100)) BEGIN SELECT CONCAT('Hello, ', input_parameter) INTO output_parameter; END;
- QUERY:用于指定要执行的查询,例如:
EXPLAIN QUERY PLAN SELECT column_name FROM table_name WHERE column_name = 100;
- RANGE:用于在查询中指定范围条件,例如:
SELECT * FROM table_name WHERE column_name BETWEEN 100 AND 200;
- READ:用于锁定表,并防止其他进程修改数据,例如:
READ LOCK table_name;
- REFERENCES:用于定义表之间的外键关系,例如:
CREATE TABLE table_name1 (column_name INT(11), FOREIGN KEY (column_name) REFERENCES table_name2(column_name));
- REGEXP:用于使用正则表达式匹配文本值,例如:
SELECT * FROM table_name WHERE column_name REGEXP '^some[ _]value$';
- RELEASE:用于释放表上的锁定,例如:
UNLOCK TABLES;
- RENAME:用于重命名表或列,例如:
ALTER TABLE table_name RENAME TO new_table_name;
- REPEAT:用于创建固定次数的循环,例如:
REPEAT SELECT column_name FROM table_name; UNTIL some_condition END REPEAT;
- REPLACE:用于插入或更新记录,例如:
REPLACE INTO table_name (column_name1, column_name2) VALUES (100, 'some_value');
- REQUIRE:用于授予用户访问和权限,例如:
GRANT SELECT, INSERT ON database_name.table_name TO 'user_name'@'host_name' IDENTIFIED BY 'password' REQUIRE SSL;
- RESTRICT:用于在删除操作时防止意外删除记录,例如:
CREATE TABLE table_name1 (column_name INT(11), FOREIGN KEY (column_name) REFERENCES table_name2(column_name) ON DELETE RESTRICT);
- RETURN:用于返回存储过程或函数的结果,例如:
CREATE FUNCTION function_name (input_parameter INT(11)) RETURNS VARCHAR(100) BEGIN RETURN CONCAT('Hello, ', input_parameter); END;
- REVOKE:用于撤销用户访问和权限,例如:
REVOKE SELECT, INSERT ON database_name.table_name FROM 'user_name'@'host_name';
- RIGHT:用于返回右边表中所有记录以及左边表中符合条件的记录,例如:
SELECT * FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name = table_name2.column_name;
- RLIKE:用于使用正则表达式匹配文本值,例如:
SELECT * FROM table_name WHERE column_name RLIKE '^some[ _]value$';
- SCHEMA:用于指定要查询的数据库,例如:
SELECT * FROM database_name.table_name;
- SCHEMAS:用于返回所有数据库的名称,例如:
SHOW DATABASES;
- SECOND_MICROSECOND:用于将秒数和微秒数表示为时间,例如:
SELECT CAST('12:30:45.123456' AS TIME(6)) AS result;
- SELECT:用于从表中选择数据,例如:
SELECT column_name1, column_name2 FROM table_name WHERE column_name1 = 'some_value' ORDER BY column_name2 DESC LIMIT 10;
- SET:用于设置变量或表列的值,例如:
SET @variable_name = 'some_value';
UPDATE table_name SET column_name1 = 100 WHERE column_name2 = 'some_value';
- SHOW:用于显示数据库或表中的信息,例如:
SHOW DATABASES;
SHOW CREATE TABLE table_name;
- SIGNED:用于定义有符号整数类型,例如:
CREATE TABLE table_name (column_name SIGNED);
- SMALLINT:用于定义小整数类型,例如:
CREATE TABLE table_name (column_name SMALLINT(6));
- SPATIAL:用于定义空间数据类型,例如:
CREATE TABLE table_name (column_name GEOMETRY);
- SQL:用于执行 SQL 语句,例如:
SET @sql_statement = CONCAT('SELECT * FROM ', table_name);
PREPARE stmt FROM @sql_statement;
EXECUTE stmt;
- SQLEXCEPTION:用于捕获 SQL 异常,例如:
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'An error occurred';
- SQLSTATE:用于捕获 SQL 异常,例如:
DECLARE EXIT HANDLER FOR SQLSTATE '42S02' SELECT 'Table does not exist';
- SQL_BIG_RESULT:用于优化大查询的性能,例如:
SELECT SQL_BIG_RESULT column_name FROM table_name WHERE column_name = 'some_value';
- SQL_CALC_FOUND_ROWS:用于在不影响查询结果的情况下计算符合条件的行数,例如:
SELECT SQL_CALC_FOUND_ROWS column_name1, column_name2 FROM table_name WHERE column_name1 = 'some_value' LIMIT 10;
SELECT FOUND_ROWS();
- SQL_SMALL_RESULT:用于优化小查询的性能,例如:
SELECT SQL_SMALL_RESULT column_name FROM table_name WHERE column_name = 'some_value';
- SSL:用于在 MySQL 客户端和服务器之间启用 SSL 连接,例如:
mysql -u user_name -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
- STARTING:用于在查询中指定开始条件,例如:
SELECT * FROM table_name WHERE column_name LIKE 'some%';
- STRAIGHT_JOIN:用于强制使用连接顺序,例如:
SELECT STRAIGHT_JOIN * FROM table_name1, table_name2 WHERE table_name1.column_name = table_name2.column_name;
- SUBDATE:用于从日期中减去指定的时间段,例如:
SELECT SUBDATE('2022-06-18', INTERVAL 1 DAY) AS result;
- SUBSTR:用于从字符串中提取子串,例如:
SELECT SUBSTR('some_value', 5) AS result;
- SUBSTRING:用于从字符串中提取子串,例如:
SELECT SUBSTRING('some_value', 5) AS result;
- SUM:用于计算数值列的总和,例如:
SELECT SUM(column_name) AS result FROM table_name;
- TABLE:用于定义表的属性,例如:
CREATE TABLE table_name (column_name INT(11)) ENGINE=InnoDB CHARSET=utf8mb4;
- TEMPORARY:用于创建临时表,例如:
CREATE TEMPORARY TABLE temp_table_name (column_name INT(11));
- TERMINATED:用于在导出 CSV 文件时指定字段分隔符,例如:
SELECT * FROM table_name INTO OUTFILE 'file_name.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
- TEXT:用于定义文本类型,例如:
CREATE TABLE table_name (column_name TEXT);
- THEN:用于 IF 语句中的条件分支,例如:
IF some_condition THEN SELECT 'Condition is true'; END IF;
- TINYBLOB:用于定义极小的二进制大对象类型,例如:
CREATE TABLE table_name (column_name TINYBLOB);
- TINYINT:用于定义极小的整数类型,例如:
CREATE TABLE table_name (column_name TINYINT(4));
- TINYTEXT:用于定义极小的文本类型,例如:
CREATE TABLE table_name (column_name TINYTEXT);
- TO:用于在查询中指定结束条件,例如:
SELECT * FROM table_name WHERE column_name BETWEEN 100 AND 200;
- TRAILING:用于去除字符串末尾空格,例如:
SELECT TRIM(TRAILING ' ' FROM 'some_value ') AS result;
- TRIGGER:用于定义触发器,例如:
CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW SELECT 'New row inserted';
- TRUE:用于表示真值,例如:
SELECT TRUE;
- UNDO:用于撤销事务中的操作,例如:
ROLLBACK;
- UNION:用于合并多个查询结果集,例如:
SELECT column_name1 FROM table_name1 UNION SELECT column_name2 FROM table_name2;
- UNIQUE:用于定义表中的唯一键,例如:
CREATE TABLE table_name (column_name1 INT(11), column_name2 VARCHAR(100), UNIQUE (column_name1));
- UNSIGNED:用于定义无符号整数类型,例如:
CREATE TABLE table_name (column_name UNSIGNED);
- UPDATE:用于更新表中的记录,例如:
UPDATE table_name SET column_name1 = 100 WHERE column_name2 = 'some_value';
- USAGE:用于授予用户对数据库和表的访问权限,例如:
GRANT USAGE ON database_name.table_name TO 'user_name'@'host_name';
- USE:用于选择要使用的数据库,例如:
USE database_name;
- USER:用于返回当前用户,例如:
SELECT USER();
- USING:用于指定连接表的列名,例如:
SELECT * FROM table_name1 INNER JOIN table_name2 USING(column_name);
- UTC_DATE:用于返回当前 UTC 日期,例如:
SELECT UTC_DATE() AS result;
- UTC_TIME:用于返回当前 UTC 时间,例如:
SELECT UTC_TIME() AS result;
- UTC_TIMESTAMP:用于返回当前 UTC 日期和时间,例如:
SELECT UTC_TIMESTAMP() AS result;
- VALUES:用于插入数据,例如:
INSERT INTO table_name (column_name1, column_name2) VALUES (100, 'some_value');
- VARBINARY:用于定义可变长度二进制类型,例如:
CREATE TABLE table_name (column_name VARBINARY(100));
- VARCHAR:用于定义可变长度字符类型,例如:
CREATE TABLE table_name (column_name VARCHAR(100));
- VARCHARACTER:用于定义可变长度字符类型,例如:
CREATE TABLE table_name (column_name VARCHARACTER(100));
- VIEW:用于创建视图,例如:
CREATE VIEW view_name AS SELECT * FROM table_name WHERE column_name = 'some_value';
- WAIT_FOR_EXECUTED_GTID_SET:用于等待指定的 GTID 集合被执行,例如:
SET @gtids = '1-1-1';
SELECT WAIT_FOR_EXECUTED_GTID_SET(@gtids, 10);
- WEEK:用于返回日期所在的周数,例如:
SELECT WEEK('2023-06-18') AS result;
- YEAR:用于返回日期的年份,例如:
SELECT YEAR('2023-06-18') AS result;
- YEARWEEK:用于返回日期所在的周数和年份,例如:
SELECT YEARWEEK('2023-06-18') AS result;
- ZEROFILL:用于在数字前补零,例如:
CREATE TABLE table_name (column_name INT(4) ZEROFILL);
- @@GLOBAL.sql_mode:用于获取全局 SQL 模式,例如:
SELECT @@GLOBAL.sql_mode AS result;
- @@SESSION.sql_mode:用于获取当前会话的 SQL 模式,例如:
SELECT @@SESSION.sql_mode AS result;
- @@VERSION:用于获取 MySQL 版本号,例如:
SELECT @@VERSION AS result;
- IFNULL:用于当某个列的值为 NULL 时,返回指定的值,例如:
SELECT column_name1, IFNULL(column_name2, 'N/A') AS result FROM table_name;
- INSERT INTO SELECT:用于将一个表的数据插入到另一个表中,例如:
INSERT INTO table_name1 (column_name1, column_name2) SELECT column_name1, column_name2 FROM table_name2 WHERE column_name1 = 'some_value';
- LOAD DATA INFILE:用于从一个 CSV 文件中导入数据到表中,例如:
LOAD DATA INFILE 'file_name.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
- REPLACE:用于替换表中的记录,如果不存在则插入新的记录,例如:
REPLACE INTO table_name (column_name1, column_name2) VALUES (100, 'some_value');
- SET GLOBAL:用于设置全局系统变量,例如:
SET GLOBAL max_allowed_packet = 1073741824;
- SET SESSION:用于设置会话级别的系统变量,例如:
SET SESSION wait_timeout = 3600;
- SHOW COLUMNS:用于显示表的列信息,例如:
SHOW COLUMNS FROM table_name;
- SHOW CREATE DATABASE:用于显示创建数据库的语句,例如:
SHOW CREATE DATABASE database_name;
- SHOW GRANTS:用于显示用户的权限,例如:
SHOW GRANTS FOR 'user_name'@'host_name'
- SHOW INDEXES:用于显示表的索引信息,例如:
SHOW INDEXES FROM table_name;
- SHOW TABLES:用于显示数据库中的表,例如:
SHOW TABLES FROM database_name;
- SLEEP:用于让当前线程休眠指定的时间,例如:
SELECT SLEEP(10);
- SUBSTRING_INDEX:用于从字符串中提取指定分隔符的前缀或后缀,例如:
SELECT SUBSTRING_INDEX('some_value', '_', 1) AS result;
- TRUNCATE:用于清空表中的数据,例如:
TRUNCATE TABLE table_name;
- UNIX_TIMESTAMP:用于将日期时间转换为 Unix 时间戳,例如:
SELECT UNIX_TIMESTAMP('2023-06-18 06:26:08') AS result;
- UUID:用于生成 UUID,例如:
SELECT UUID() AS result;
- VALUES ROW:用于插入一行数据到表中,例如:
INSERT INTO table_name VALUES ROW(100, 'some_value');
- WHILE LOOP:用于循环执行语句,直到条件成立,例如:
WHILE some_condition DO
SELECT 'Condition is true';
END WHILE;
- WITH ROLLUP:用于在 GROUP BY 子句中添加小计行,例如:
SELECT column_name1, SUM(column_name2) FROM table_name GROUP BY column_name1 WITH ROLLUP;