Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL (SQL 语句快速参考 (update (The UPDATE statement edits a row in the table.…
SQL
SQL 语句快速参考
update
The UPDATE statement
edits a row
in the table. You can use the UPDATE statement when you want to change existing records.
UPDATE is a clause that edits a row in the table.
SQL UPDATE 语法
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
alter table
The ALTER TABLE statement
added a new column
to the table. You can use this command when you want to add columns to a table.
ALTER TABLE is a clause that lets you make the specified changes.
SQL Select 语句
SELECT name FROM celebs;
SELECT statements are used to fetch data from a database. Here, SELECT returns all data in the name column of the celebs table.
SELECT is a clause that indicates that the statement is a query. You will use SELECT every time you query data from a database.
name specifies the column to query data from.
FROM celebs specifies the name of the table to query data from. In this statement, data is queried from the celebs table.
delete from
The DELETE FROM statement deletes one or more rows from a table. You can use the statement when you want to delete existing records.
DELETE FROM is a clause that lets you delete rows from a table.
select distinct
SELECT DISTINCT is used to return unique values in the result set. It filters out all duplicate values. Here, the result set lists each genre in the movies table exactly once.
在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。
DISTINCT 关键词用于返回唯一不同的值。
insert into
INSERT INTO 语句可以有两种编写形式。
第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
INSERT INTO table_name
VALUES (value1,value2,value3,...);
第二种形式需要指定列名及被插入的值：
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
WHERE
WHERE is a clause that indicates you want to filter the result set to include only rows where the following condition is true.
like 1
LIKE can be a useful operator when you want to compare similar values. Here, we are comparing two movies with the same name but are spelled differently.
LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.
like 2
% is a wildcard character that matches zero or more missing letters in the pattern.
A% matches all movies with names that begin with "A"
%a matches all movies that end with "a"
and
Sometimes you want to combine multiple conditions in a WHERE clause to make the result set more specific and useful. One way of doing this is to use the AND operator.
or
The OR operator can also be used to combine more than one condition in a WHERE clause. The OR operator evaluates each condition separately and if any of the conditions are true then the row is added to the result set.
order by
You can sort the results of your query using ORDER BY. Sorting the results often makes the data more useful and easier to analyze.
LIMIT
LIMIT is a clause that lets you specify the maximum number of rows the result set will have. Here, we specify that the result set can not have more than three rows.
Aggregate Functions
group by
Aggregate functions are more useful when they organize data into groups.
GROUP BY is a clause in SQL that is only used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.
SELECT price, COUNT(*) FROM fake_apps
GROUP BY price;
sum
SQL makes it easy to add all values in a particular column using SUM().
SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column. Here, it adds all the values in the downloads column.
count
The fastest way to calculate the number of rows in a table is to use the COUNT() function.
COUNT() is a function that takes the name of a column as an argument and counts the number of rows where the column is not NULL. Here, we want to count every row so we pass * as an argument.
max
MAX() is a function that takes the name of a column as an argument and returns the largest value in that column. Here, we pass downloads as an argument so it will return the largest value in the downloads column.
AVG
round
By default, SQL tries to be as precise as possible without rounding. We can make the result set easier to read using the ROUND() function.
ROUND() is a function that takes a column name and an integer as an argument. It rounds the values in the column to the number of decimal places specified by the integer. Here, we pass the column AVG(downloads) and 2 as arguments. SQL first calculates the average for each price and then rounds the result to two decimal places in the result set.
SQL 简介
SQL 能做什么？
RDBMS
relational database
即有关系的数据，
被放在图表里， 就叫数据库
SQL 是什么？
Multiple Tables
CREAT TABLE Primary Key ()
SQL 语法
SQL 语句
SQL 对大小写不敏感：SELECT 与 select 是相同的。
在本教程中，我们将在每条 SQL 语句的末端使用分号。
一些最重要的 SQL 命令
SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO
- 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
数据库表
SQL 通用数据类型
Integer, a positive or negative whole number
Text, a text string
Date, the date formatted as YYYY-MM-DD for the year, month, and day
Real, a decimal value