常用的数据库SQL命令

CREATE TABLE,在数据库中创建一个新表
CREATE TABLE table_name (column_a datatype, column_b datatype);

DELETE,从表中删除行
DELETE FROM table_name WHERE column_name= column_value;

ALTER TABLE,向数据库中的表添加列
ALTER TABLE table_name ADD column_name datatype;

UPDATE,编辑表中的行
UPDATE table_name SET some_column = some_value WHERE some_column = some_value;

BETWEEN,运算符,指定一个过滤范围
SELECT column_name FROM table_name WHERE column_name BETWEEN value_a AND value_b;

SUM,聚合函数,求和
SELECT SUM(column_name) FROM table_name;

MAX(),聚合函数,返回列中最大值
SELECT MAX(column_name) FROM table_name;

MIN(),聚合函数,返回列中最小值
SELECT MIN(column_name) FROM table_name;

COUNT(),聚合函数,计算列不为NULL的行数,列名称为参数
SELECT COUNT(column_name) FROM table_name;

GROUP BY,分组语句,配合聚合函数,先通过SELECT语句得到一个结果集,再根据列分组
注意这里COUNT(*)不会忽略NULL
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

HAVING,过滤GROUP BY语句返回的记录集
注意WHERE关键字不能与聚合函数一起使用
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;

INNER JOIN,内连接,组合不同表的行
SELECT column_name FROM table_a JOIN table_b ON table_a.column_name = table_b.column_name;

INSERT,向表中添加新行
INSERT INTO table_name (column_a, column_b) VALUES (value_a, value_b);

IS NULL或IS NOT NULL,是否为空值
SELECT column_name FROM table_name WHERE column_name IS NULL;

LIMIT,指定结果集的最大行数
SELECT column_name FROM table_name LIMIT number;

AND,和运算符,要求条件同时满足
SELECT column_name FROM table_name WHERE column_a = value_a AND column_b = value_b;

OR,或运算符,要求条件满足一个即可
SELECT column_name FROM table_name WHERE column_name = value_a OR column_name = value_b;```

ORDER BY,对结果集排序,ASC升序,DESC降序
SELECT column_name FROM table_name ORDER BY column_name ASC;

OUTER JOIN,外连接,左表中的行都在结果集中,如果连接条件不满足,则用NULL值填充右表中的列
SELECT column_name FROM table_a LEFT JOIN table_b ON table_a.column_name = table_b.column_name;

ROUND(),函数,把数值字段舍入为指定的小数位数
SELECT ROUND(column_name, integer) FROM table_name;

SELECT,按条件查询
SELECT column_name FROM table_name;

SELECT DISTINCT,同SELECT,但是返回不重复的唯一值
SELECT DISTINCT column_name FROM table_name;

AS,关键字,使用别名重命名列或表
SELECT column_name AS 'another_name' FROM table_name;

AVG(),函数,返回数值列平均值
SELECT AVG(column_name) FROM table_name;

CASE,条件选择语句
SELECT column_name, CASE WHEN condition_a THEN 'Result_a' WHEN condition_b THEN 'Result_b' ELSE 'Result_c' END FROM table_name;

登录注册后才能评论。