MySQL Tutorial: part 1

Querying data

SELECT

the syntax of the SELECT statement:

SELECT 
    column_1, column_2, ...
FROM
    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

If you want to get data for all columns in the employees table, you can list all column names in the SELECT clause. Or you just use the asterisk (*) to indicate that you want to get data from all columns of the table like the following query:

SELECT * FROM table1;

DISTINCT

The syntax of using the DISTINCT clause is as follows:

SELECT DISTINCT
    columns
FROM
    table_name
WHERE
    where_conditions;
  • DISTINCT clause vs. GROUP BY clause

If you use the GROUP BY clause in the SELECT statement without using aggregate functions, the GROUP BY clause behaves like the DISTINCT clause.

Generally speaking, the DISTINCT clause is a special case of the GROUP BY clause. The difference between DISTINCT clause and GROUP BY clause is that the GROUP BY clause sorts the result set whereas the DISTINCT clause does not.

for examples,

SELECT
  columns
FROM
  table_name
GROUP BY columns;

is equivalent to

SELECT DISTINCT
  columns
FROM
  table_name
ORDER BY columns;

Filtering Data

WHERE

Even though the WHERE clause appears at the end of the statement, MySQL evaluates the expression in the WHERE clause first to select the matching rows.

The following table lists the comparison operators that you can use to form filtering expressions in the WHERE clause.

where

AND

The following illustrates the results of the AND operator when combining true, false, and null.

and

OR

The following table shows the result of the OR operator.

or

IN

The IN operator allows you to determine if a specified value matches any one of a list or a subquery.
The following illustrates the syntax of the IN operator.

SELECT 
    column1,column2,...
FROM
    table_name
WHERE 
 (expr|column_1) [NOT] IN ('value1','value2',...);

!Note that if the expr or any value in the list is NULL, the IN operator returns NULL

You can combine the IN operator with the NOT operator to determine if a value does not match any value in a list or a subquery.

BETWEEN

The BETWEEN operator allows you to specify a range to test.

The following illustrates the syntax of the BETWEEN operator:

expr [NOT] BETWEEN begin_expr AND end_expr;

!NOTE If any expression is NULL, the BETWEEN operator returns a NULL value.

  • with DATA type

When you use the BETWEEN operator with date values, to get the best result, you should use the type cast to explicitly convert the type of column or expression to the DATE type.

CAST('2020-01-01' as DATE)

LIKE

MySQL provides two wildcard characters for using with the LIKE operator, the percentage % and underscore _ .

% wildcard allows you to match any string of zero or more characters.
_ wildcard allows you to match any single character.

  • with ESCAPE clause

If you don’t specify the escape character explicitly, the backslash character \ is the default escape character.

SELECT 
    columns
FROM
    table_name
WHERE
    column LIKE '%\_20%';

Or you can specify a different escape character e.g., $ by using the ESCAPE clause:

SELECT 
    columns
FROM
    table_name
WHERE
    column LIKE '%$_20%' ESCAPE '$';

LIMIT

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set.

The following illustrates the LIMIT clause syntax with two arguments:

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count;

The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
The count specifies the maximum number of rows to return.

When you use the LIMIT clause with one argument, this argument will be used to determine the maximum number of rows to return from the beginning of the result set.

SELECT 
    column1,column2,...
FROM
    table
LIMIT count;

The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:

SELECT 
    column1,column2,...
FROM
    table
LIMIT 0 , count;
  • Using MySQL LIMIT to get the first N rows
SELECT 
    column1,column2,...
FROM
    table
LIMIT N;

  • Using MySQL LIMIT to get the highest and lowest values
    The LIMIT clause often used with the ORDER BY clause. First, you use the ORDER BY clause to sort the result set based on certain criteria, and then you use the LIMIT clause to find lowest or highest values.

  • Using MySQL LIMIT to get the nth highest value

SELECT 
    column1, column2,...
FROM
    table
ORDER BY column1 DESC
LIMIT nth-1, count;

IS NULL

value IS [NOT] NULL
  • MySQL IS NULL’s specialized features

  1. If the DATE or DATETIME column that has NOT NULL constraint and contains the special date 0000-00-00, you can use the IS NULL operator to find such rows.

  2. If the variable @@sql_auto_is_null is set to 1, you can get the value of a generated column after executing an INSERT statement by using the IS NULL operator. Note that by default the variable @@sql_auto_is_null is 0.

Sorting Data

ORDER BY

The ORDER BY clause allows you to:

  • Sort a result set by a single column or multiple columns.
  • Sort a result set by different columns in ascending or descending order.

The following illustrates the syntax of the ORDER BY clause:

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

By default, the ORDER BY clause sorts the result set in ascending order if you don’t specify ASC or DESC explicitly.

  • with customer sort order
    The ORDER BY clause enables you to define your own custom sort order for the values in a column using the FIELD() function.
ELECT 
    column_1, column_2, ...
FROM
    table_1
ORDER BY FIELD(status,
        'Red',
        'Green',
        'Blue',
        'Black',
        'White');

Joining tables

AS

MySQL supports two kinds of aliases which are known as column alias and table alias.

MySQL alias for columns
The following statement illustrates how to use the column alias:

SELECT 
 [column_1 | expression] AS descriptive_name
FROM table_name;

If the alias contains space, you must quote it as the following:

SELECT 
 [column_1 | expression] AS `descriptive name`
FROM table_name;

Because the AS keyword is optional, you can omit it in the statement.

MySQL alias for tables

table_name AS table_alias

JOIN

MySQL supports the following types of joins:

  • Cross join
  • Inner join
  • Left join
  • Right join

CROSS JOIN

The CROSS JOIN makes a Cartesian product of rows from multiple tables. Suppose, you join t1 and t2 tables using the CROSS JOIN, the result set will include the combinations of rows from the t1 table with the rows in the t2 table.

If you add a WHERE clause, in case T1 and T2 has a relationship, the CROSS JOIN works like the INNER JOIN clause as shown in the following query:

SELECT 
    *
FROM
    T1
        CROSS JOIN
    T2
WHERE
    T1.id = T2.id;

SELECT 
    *
FROM
    T1
INNER JOIN T2 ON T1.id = T2.id;

INNER JOIN

To form an INNER JOIN, you need a condition which is known as a join-predicate.

  • The syntax of the INNER JOIN clause is as follows:
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;
inner-join

For each row in the t1 table, the INNER JOIN clause compares it with each row of the t2,t3 ... table to check if both of them satisfy the join condition. When the join condition is met, the INNER JOIN will return a new row which consists of columns in both t1 and t2,t3 ... tables.

LEFT JOIN

Unlike an INNER JOIN, a LEFT JOIN returns all rows in the left table including rows that satisfy join-predicate and rows do not. For the rows that do not match the join-predicate, NULLs appear in the columns of the right table in the result set.

left-join

RIGHT JOIN

A RIGHT JOIN is similar to the LEFT JOIN except that the treatment of tables is reversed. With a RIGHT JOIN, every row from the right table ( t2) will appear in the result set. For the rows in the right table that do not have the matching rows in the left table ( t1), NULLs appear for columns in the left table ( t1).

SELF JOIN

There is a special case that you need join a table to itself, which is known as self join.

Grouping data

GROUP BY

The GROUP BY clause groups a set of rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set.

The following illustrates the GROUP BY clause syntax:

SELECT 
    c1, c2,..., cn, aggregate_function(ci)
FROM
    table
WHERE
    where_conditions
GROUP BY c1 , c2,...,cn;

HAVING

The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.

If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.

Notice that the HAVING clause applies a filter condition to each group of rows, while the WHERE clause applies the filter condition to each individual row.

Reference

Basic MySQL Tutorial

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 219,635评论 6赞 508
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,628评论 3赞 396
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 165,971评论 0赞 356
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,986评论 1赞 295
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 68,006评论 6赞 394
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,784评论 1赞 307
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,475评论 3赞 420
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,364评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,860评论 1赞 317
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 38,008评论 3赞 338
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,152评论 1赞 351
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,829评论 5赞 346
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,490评论 3赞 331
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,035评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,156评论 1赞 272
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,428评论 3赞 373
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,127评论 2赞 356