SELECT:查询数据
查询指定字段:
SELECT column1, column2 FROM table_name WHERE condition;
查询全部字段:
SELECT * FROMtable_name WHERE condition;
INSERT:插入数据
省略列名:
INSERT INTO table_name VALUES (value1, NULL, value3, ...);
不省略列名:
INSERT INTO table_name (column1, column3, ...) VALUES (value1, value3, ...);
插入另一个表中的查询结果:
INSERT INTO table_name (column1, column2, ...) SELECT column1, column2, ... FROM another_table WHERE condition;
UPDATE:更新数据
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
DELETE:删除数据
DELETE FROM table_name WHERE condition;
WHERE:条件子句,过滤查询结果
SELECT * FROM table_name WHERE column1 > value1;
逻辑条件:
SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;
SELECT * FROM table_name WHERE column1 = value1 OR column2 = value2;
SELECT * FROM table_name WHERE NOT column1 = value1;
在 SQL 语言中,"!=" 和 "NOT" 是两种不同的否定操作符。
"!=" 是一个比较操作符,它表示"不等于"。例如,"column1 != value1" 表示 "column1" 的值不等于 "value1"。
"NOT" 是一个逻辑操作符,它表示逻辑否定。例如,"NOT column1 = value1" 表示 "column1 = value1" 的逻辑否定,也就是 "column1" 的值不等于 "value1"。
所以,在这种情况下,"!=" 和 "NOT" 具有相同的效果。但是,在一些复杂的逻辑表达式中,"NOT" 可以提供更多的灵活性,因为它可以应用于整个表达式,而不仅仅是两个值的比较。例如,"NOT (column1 = value1 AND column2 = value2)" 表示 "column1" 的值不等于 "value1" 或 "column2" 的值不等于 "value2"。
范围条件:
SELECT * FROM table_name WHERE column1 BETWEEN value1 AND value2;
列表条件:
SELECT * FROM table_name WHERE column1 IN (value1, value2, value3);
模式匹配条件:
SELECT * FROM table_name WHERE column1 LIKE 'pattern%';
空值条件:
SELECT * FROM table_name WHERE column1 IS NULL;
SELECT * FROM table_name WHERE column1 IS NOT NULL;
聚合条件:
SELECT COUNT(*) FROM table_name WHERE column1 = value1;
子查询条件:
SELECT * FROM table_name WHERE column1 = (SELECT column2 FROM another_table WHERE condition);