Module 01 · Getting Started with SQL
SQL Cheat Sheet: Basics - SELECT, INSERT, UPDATE, DELETE, COUNT, DISTINCT, LIMITSQL 速查表：基础 - SELECT、INSERT、UPDATE、DELETE、COUNT、DISTINCT、LIMIT
| Command 命令 | Syntax 语法 | Description 描述 | Example 示例 |
|---|---|---|---|
| SELECT | SELECT column1, column2, ... FROM table_name; |
SELECT statement is used to fetch data from a database.SELECT 语句用于从数据库中获取数据。 |
SELECT city FROM placeofinterest; |
| WHERE | SELECT column1, column2, ...FROM table_name WHERE condition; |
WHERE clause is used to extract only those records that fulfill a specified condition.WHERE 子句用于提取满足指定条件的记录。 |
SELECT * FROM placeofinterest WHERE city = 'Rome' ; |
| COUNT | SELECT COUNT * FROM table_name ; |
COUNT is a function that takes the name of a column as argument and counts the number of rows when the column is not NULL.COUNT 是一个函数，它接受列名作为参数，并在列不为 NULL 时计算行数。 |
SELECT COUNT(country) FROM placeofinterest WHERE country='Canada'; |
| DISTINCT | SELECT DISTINCT columnname FROM table_name; |
DISTINCT function is used to specify that the statement is a query which returns unique values in specified columns.DISTINCT 用于指定该语句是一个查询，该查询在指定列中返回唯一值。 |
SELECT DISTINCT country FROM placeofinterest WHERE type='historical'; |
| LIMIT | SELECT * FROM table_name LIMIT number; |
LIMIT is a clause to specify the maximum number of rows the result set must have.LIMIT 是一个子句，用于指定结果集必须拥有的最大行数。 |
SELECT * FROM placeofinterest WHERE airport="pearson" LIMIT 5; |
| INSERT | INSERT INTO table_name (column1,column2,column3...) VALUES(value1,value2,value3...); |
INSERT is used to insert new rows in the table.INSERT 用于在表中插入新行。 |
INSERT INTO placeofinterest (name,type,city,country,airport) VALUES('Niagara Waterfalls','Nature','Toronto','Canada','Pearson'); |
| UPDATE | UPDATE table_name SET[[column1]=[VALUES]] WHERE [condition]; |
UPDATE used to update the rows in the table.UPDATE 用于更新表中的行。 |
UPDATE placeofinterest SET name = 'Niagara Falls' WHERE name = "Niagara Waterfalls"; |
| DELETE | DELETE FROM table_name WHERE [condition]; |
DELETE statement is used to remove rows from the table which are specified in the WHERE condition.DELETE 语句用于从表中删除在 WHERE 条件中指定的行。 |
DELETE FROM placeofinterest WHERE city IN ('Rome','Vienna'); |