SQL documentation

安裝教程

法則1:col

法則2:select

select col,col,col,* 找什么
FROM table 從哪找卖词?
WHERE col 條件 條件是啥谐腰?

條件:數(shù)字(where)

當(dāng)查找條件col是數(shù)字

select * from table where col = 1;

Operator Condition SQL Example 解釋
=, !=, <, <=, >, >= Standard numerical operators col != 4 等于 大于 小于
BETWEEN … AND … Number is within range of two values (inclusive)Numb col_name BETWEEN 1.5 AND 10.5 在X和X之間
NOT BETWEEN … AND … Number is not within range of two values (inclusive) col_name NOT BETWEEN 1 AND10 不在X和X之間
IN (…) Number exists in a list col_name IN (2, 4, 6) 在X集合
NOT IN (…) Number does not exist in a list col_name NOT IN (1, 3, 5) 不在X集合

條件: 文本(where)

Operator Condition Example 解釋
= Case sensitive exact string comparison (notice the single equals) col_name = "abc" 等于
!= or <> Case sensitive exact string inequality comparison col_name != "abcd" 不等于
LIKE Case insensitive exact string comparison col_name LIKE "ABC" 等于
NOT LIKE Case insensitive exact string inequality comparison col_name NOT LIKE "ABCD" 不等于
% Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) col_name LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS") 模糊匹配
_ Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) col_name LIKE "AN_" (matches "AND", but not "AN") 模糊匹配單字符
IN (…) String exists in a list col_name IN ("A", "B", "C") 在集合
NOT IN (…) String does not exist in a list col_name NOT IN ("D", "E", "F") 不在集合

排序(rows)

需要對(duì)結(jié)果rows排序和篩選部分rows

select * from table where col > 1 order by col asc limit 2 offset 2

Operator Condition Example 解釋
ORDER BY . ORDER BY col ASC/DESC 按col排序
ASC . ORDER BY col ASC/DESC 升序
DESC . ORDER BY col ASC/DESC 降序
LIMIT OFFSET . LIMIT num_limit OFFSET num_offset 從offset取limit
ORDER BY . ORDER BY col1 ASC, col2 DESC 多列排序

合并(INNER/LEFT/RIGHT/FULL JOIN)

SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table 
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

NULL(IS/IS NOT NULL)

SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

表達(dá)式

SELECT particle_speed / 2.0 AS half_particle_speed
FROM physics_data
WHERE ABS(particle_position) * 10.0 > 500;
SELECT col_expression AS expr_description, …
FROM mytable;
--Example query with both column and table name aliases
SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
  ON mywidgets.id = widget_sales.widget_id;

聚合函數(shù)

Function Description
COUNT(*****), COUNT(column) A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.
MIN(column) Finds the smallest numerical value in the specified column for all rows in the group.
MAX(column) Finds the largest numerical value in the specified column for all rows in the group.
AVG(column) Finds the average numerical value in the specified column for all rows in the group.
SUM(column) Finds the sum of all numerical values in the specified column for the rows in the group.

Docs: MySQL, Postgres, SQLite, Microsoft SQL Server

GROUP BY

--Select query with aggregate functions over groups
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column;

if the GROUP BY clause is executed after the WHEREclause, SQL allows us to do this by adding an additional HAVING clause which is used specifically with the GROUP BY clause to allow us to filter grouped rows from the result set.

--Select query with HAVING constraint
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

插入行

--Insert statement with specific columns
INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
      (value_or_expr_2, another_value_or_expr_2, …),
      …;

修改行

--Update statement with values
UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

刪除行

--Delete statement with condition
DELETE FROM mytable
WHERE condition;

創(chuàng)建表格

--Create table statement w/ optional table constraint and default value
CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

Table data types

Data type Description
INTEGER, BOOLEAN The integer datatypes can store whole integer values like the count of a number or an age. In some implementations, the boolean value is just represented as an integer value of just 0 or 1.
FLOAT, DOUBLE, REAL The floating point datatypes can store more precise numerical data like measurements or fractional values. Different types can be used depending on the floating point precision required for that value.
CHARACTER(num_chars), VARCHAR(num_chars), TEXT The text based datatypes can store strings and text in all sorts of locales. The distinction between the various types generally amount to underlaying efficiency of the database when working with these columns.Both the CHARACTER and VARCHAR (variable character) types are specified with the max number of characters that they can store (longer values may be truncated), so can be more efficient to store and query with big tables.
DATE, DATETIME SQL can also store date and time stamps to keep track of time series and event data. They can be tricky to work with especially when manipulating data across timezones.
BLOB Finally, SQL can store binary data in blobs right in the database. These values are often opaque to the database, so you usually have to store them with the right metadata to requery them.

Docs: MySQL, Postgres, SQLite, Microsoft SQL Server

Table constraints

Constraint Description
PRIMARY KEY This means that the values in this column are unique, and each value can be used to identify a single row in this table.
AUTOINCREMENT For integer values, this means that the value is automatically filled in and incremented with each row insertion. Not supported in all databases.
UNIQUE This means that the values in this column have to be unique, so you can't insert another row with the same value in this column as another row in the table. Differs from the PRIMARY KEY in that it doesn't have to be a key for a row in the table.
NOT NULL This means that the inserted value can not be NULL.
CHECK (expression) This is allows you to run a more complex expression to test whether the values inserted are value. For example, you can check that values are positive, or greater than a specific size, or start with a certain prefix, etc.
FOREIGN KEY This is a consistency check which ensures that each value in this column corresponds to another value in a column in another table. For example, if there are two tables, one listing all Employees by ID, and another listing their payroll information, the FOREIGN KEY can ensure that every row in the payroll table corresponds to a valid employee in the master Employee list.

Example

--Movies table schema
CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER
);

修改表格

添加列

--Altering table to add new column(s)
ALTER TABLE mytable
ADD column DataType OptionalTableConstraint 
    DEFAULT default_value;

刪除列

--Altering table to remove column(s)
ALTER TABLE mytable
DROP column_to_be_deleted;

重命名表格

--Altering table name
ALTER TABLE mytable
RENAME TO new_table_name;

其他

Documentation: MySQL, Postgres, SQLite, Microsoft SQL Server.

刪除表格

--Drop table statement
DROP TABLE IF EXISTS mytable;

注:In addition, if you have another table that is dependent on columns in table you are removing (for example, with a FOREIGN KEY dependency) then you will have to either update all dependent tables first to remove the dependent rows or to remove those tables entirely.

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末玖瘸,一起剝皮案震驚了整個(gè)濱河市践樱,隨后出現(xiàn)的幾起案子踩萎,更是在濱河造成了極大的恐慌,老刑警劉巖登夫,帶你破解...
    沈念sama閱讀 222,590評(píng)論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件广匙,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡恼策,警方通過(guò)查閱死者的電腦和手機(jī)鸦致,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,157評(píng)論 3 399
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)涣楷,“玉大人分唾,你說(shuō)我怎么就攤上這事∈ǘ罚” “怎么了绽乔?”我有些...
    開(kāi)封第一講書(shū)人閱讀 169,301評(píng)論 0 362
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)情龄。 經(jīng)常有香客問(wèn)我沛善,道長(zhǎng)屠橄,這世上最難降的妖魔是什么? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 60,078評(píng)論 1 300
  • 正文 為了忘掉前任胁编,我火速辦了婚禮鹃觉,結(jié)果婚禮上专酗,老公的妹妹穿的比我還像新娘。我一直安慰自己盗扇,他們只是感情好祷肯,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,082評(píng)論 6 398
  • 文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著疗隶,像睡著了一般佑笋。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上斑鼻,一...
    開(kāi)封第一講書(shū)人閱讀 52,682評(píng)論 1 312
  • 那天蒋纬,我揣著相機(jī)與錄音,去河邊找鬼。 笑死蜀备,一個(gè)胖子當(dāng)著我的面吹牛关摇,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播碾阁,決...
    沈念sama閱讀 41,155評(píng)論 3 422
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼输虱,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼!你這毒婦竟也來(lái)了脂凶?” 一聲冷哼從身側(cè)響起宪睹,我...
    開(kāi)封第一講書(shū)人閱讀 40,098評(píng)論 0 277
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎蚕钦,沒(méi)想到半個(gè)月后横堡,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,638評(píng)論 1 319
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡冠桃,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,701評(píng)論 3 342
  • 正文 我和宋清朗相戀三年命贴,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片食听。...
    茶點(diǎn)故事閱讀 40,852評(píng)論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡胸蛛,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出樱报,到底是詐尸還是另有隱情葬项,我是刑警寧澤,帶...
    沈念sama閱讀 36,520評(píng)論 5 351
  • 正文 年R本政府宣布迹蛤,位于F島的核電站民珍,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏盗飒。R本人自食惡果不足惜嚷量,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,181評(píng)論 3 335
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望逆趣。 院中可真熱鬧蝶溶,春花似錦、人聲如沸宣渗。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 32,674評(píng)論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)痕囱。三九已至田轧,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間鞍恢,已是汗流浹背傻粘。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 33,788評(píng)論 1 274
  • 我被黑心中介騙來(lái)泰國(guó)打工巷查, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人抹腿。 一個(gè)月前我還...
    沈念sama閱讀 49,279評(píng)論 3 379
  • 正文 我出身青樓岛请,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親警绩。 傳聞我的和親對(duì)象是個(gè)殘疾皇子崇败,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,851評(píng)論 2 361

推薦閱讀更多精彩內(nèi)容