SELECT QueriesWHERE ConditionsORDER BYAggregate FunctionsJOINsINSERTUPDATEDELETETable ManagementConstraintsSubqueriesString/Date Functions
#SELECT Queries
| Command | Description | |
|---|---|---|
SELECT * FROM table | Select all columns | |
SELECT col1, col2 FROM table | Select specific columns | |
SELECT DISTINCT col FROM table | Select distinct values | |
SELECT col AS alias FROM table | Column alias | |
SELECT * FROM table LIMIT 10 | Limit results | |
SELECT * FROM table OFFSET 5 | Skip results |
#WHERE Conditions
| Command | Description | |
|---|---|---|
WHERE col = value | Equals | |
WHERE col <> value | Not equals | |
WHERE col > value | Greater than | |
WHERE col >= value | Greater than or equal | |
WHERE col < value | Less than | |
WHERE col <= value | Less than or equal | |
WHERE col BETWEEN a AND b | Between range | |
WHERE col IN (v1, v2, v3) | In list | |
WHERE col LIKE '%pattern%' | Pattern matching | |
WHERE col IS NULL | NULL check | |
WHERE col IS NOT NULL | NOT NULL check | |
WHERE cond1 AND cond2 | AND condition | |
WHERE cond1 OR cond2 | OR condition | |
WHERE NOT condition | NOT condition |
#ORDER BY
| Command | Description | |
|---|---|---|
ORDER BY col | Ascending order | |
ORDER BY col ASC | Ascending (explicit) | |
ORDER BY col DESC | Descending order | |
ORDER BY col1, col2 | Multiple columns | |
ORDER BY col NULLS FIRST | NULLs first | |
ORDER BY col NULLS LAST | NULLs last |
#Aggregate Functions
| Command | Description | |
|---|---|---|
COUNT(*) | Count rows | |
COUNT(col) | Count non-null | |
COUNT(DISTINCT col) | Count distinct | |
SUM(col) | Sum | |
AVG(col) | Average | |
MIN(col) | Minimum | |
MAX(col) | Maximum | |
GROUP BY col | Group by | |
HAVING condition | Group filter |
#JOINs
| Command | Description | |
|---|---|---|
INNER JOIN t2 ON t1.col = t2.col | Inner join | |
LEFT JOIN t2 ON t1.col = t2.col | Left outer join | |
RIGHT JOIN t2 ON t1.col = t2.col | Right outer join | |
FULL OUTER JOIN t2 ON ... | Full outer join | |
CROSS JOIN t2 | Cross join | |
SELF JOIN (t AS a, t AS b) | Self join | |
NATURAL JOIN t2 | Natural join |
#INSERT
| Command | Description | |
|---|---|---|
INSERT INTO t (cols) VALUES (vals) | Insert single row | |
INSERT INTO t VALUES (v1), (v2) | Insert multiple rows | |
INSERT INTO t SELECT ... | Insert from select | |
INSERT INTO t DEFAULT VALUES | Insert default values |
#UPDATE
| Command | Description | |
|---|---|---|
UPDATE t SET col = val | Update all rows | |
UPDATE t SET col = val WHERE ... | Conditional update | |
UPDATE t SET c1 = v1, c2 = v2 | Update multiple columns | |
UPDATE t SET col = col + 1 | Increment value |
#DELETE
| Command | Description | |
|---|---|---|
DELETE FROM t | Delete all rows | |
DELETE FROM t WHERE ... | Conditional delete | |
TRUNCATE TABLE t | Truncate table (fast) |
#Table Management
| Command | Description | |
|---|---|---|
CREATE TABLE t (col type, ...) | Create table | |
DROP TABLE t | Drop table | |
ALTER TABLE t ADD col type | Add column | |
ALTER TABLE t DROP COLUMN col | Drop column | |
ALTER TABLE t RENAME TO new | Rename table | |
CREATE INDEX idx ON t(col) | Create index | |
DROP INDEX idx | Drop index |
#Constraints
| Command | Description | |
|---|---|---|
PRIMARY KEY | Primary key | |
FOREIGN KEY REFERENCES t(col) | Foreign key | |
UNIQUE | Unique constraint | |
NOT NULL | Not null | |
DEFAULT value | Default value | |
CHECK (condition) | Check constraint | |
AUTO_INCREMENT / SERIAL | Auto increment |
#Subqueries
| Command | Description | |
|---|---|---|
WHERE col IN (SELECT ...) | IN subquery | |
WHERE col = (SELECT ...) | Scalar subquery | |
WHERE EXISTS (SELECT ...) | EXISTS subquery | |
SELECT (SELECT ...) AS col | Select subquery | |
FROM (SELECT ...) AS t | From subquery | |
WITH cte AS (SELECT ...) | Common Table Expression |
#String/Date Functions
| Command | Description | |
|---|---|---|
CONCAT(s1, s2) | Concatenate strings | |
UPPER(str) / LOWER(str) | Upper/lower case | |
LENGTH(str) | String length | |
SUBSTRING(str, start, len) | Substring | |
TRIM(str) | Trim whitespace | |
REPLACE(str, from, to) | Replace string | |
NOW() / CURRENT_TIMESTAMP | Current timestamp | |
DATE(datetime) | Extract date | |
YEAR(date) / MONTH(date) | Extract year/month | |
DATEDIFF(d1, d2) | Date difference |
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.