SQL is a standard language for storing, manipulating and retrieving data in databases.
Tips: Many SQL developers prefer upper case for all SQL keywords and lower case for all column and table names, which makes the code easier to read and debug.

SELECT

This statement is used to retrieve data from a database. The data returned is stored in a result table, called the result-set. You must know that what and where you want to choose.

One Column

image.png

Multiple Columns

image.png

All Columns

image.png

Distinct Column

The [SELECT DISTINCT] statement is used to return only distinct(different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different(distinct) values.
image.png

Limit

[SELECT prod_name FROM products LIMIT 5,5;] This statement indicates that MySQL returns 5 lines beginning on line 5. The first number is the start position and the second number is the number of lines to retrieve.
image.png

ORDER BY

The [ORDER BY] keyword is used to sort the result-set in ascending or descending order. This statement sorts the records in ascending order by default. To sort the records in descending order, use the [DESC] keyword. The [DESC] keyword applies only to column in front of this.

Default Order by

image.png

Desc Order by

image.png

Order by and Limit

image.png

WHERE

The [WHERE] clause is used to filter records.
The [WHERE] clause can be combined with [AND] [OR] [NOT] operators.
Use parentheses in the [WHERE] clause whenever you use a [WHERE] clause with [AND] [OR] operators. Don’t relay too much on the default order of calculation, even if it’s really what you want. There is no harm in using parentheses, it removes ambiguity.
Tips: When using both the [ORDER BY] and [WHERE] statements, you must leave [ORDER BY] after [WHERE], otherwise an error will be generated. For example, [SELECT * FROM products WHERE xxx ORDER BY prod_name DESC]
image.png
[IN] operator is used to specify a range of conditions, and each condition in the range can be matched.
image.png
[NOT]
MySql supports the uese of [NOT] to reverse the [IN] [BETWEEN] and [EXIST].image.png

LIKE

The [LIKE] operator is used in a [WHERE] clause to search for a specified pattern in a column.
Wild card(in computers, a symbol that has no particular meaning of its own so that its space can be filled by any real character that is necessary)

  • % 匹配一个获或多个字符 ```sql SELECT prod_name,prod_id FROM products WHERE prod_name LIKE ‘jet%’

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE ‘%se%’

  2. - _ 只匹配单个字符而不是多个字符
  3. ```sql
  4. SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '_ ton anvil'
  6. SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '% ton anvil'

image.pngimage.png
notes:
1.不要过度使用通配符
2.除非有绝对必要，否则不要把通配符用在搜索模式的开始处。
select prodid,prod_name from products where prod_name like ‘%jet

正则表达式 REGEXP

如何在WHERE子句内使用正则表达式更好的控制数据过滤。

基本字符匹配

检索prod_name包含文本1000的所有行:

  1. SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name

这里要注意和LIKE表达式的区别，LIKE表达式如果不适用通配符，会默认匹配整个列值，例如 SELECT prod_name FROM products WHERE prod_name LIKE ‘1000’将不会返回任何行，如果那一列值仅仅有1000则会返回，如果想要匹配带有1000的行则可以用%1000%

匹配任意一个字符 .

  1. SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name DESC LIMIT 1,2

OR匹配 |

  1. SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000|3000' ORDER BY prod_name

匹配几个字符之一 []

匹配特定字符[] 这个是另一种形式的OR |

  1. SELECT prod_name FROM products WHERE prod_name REGEXP '[123] ton' ORDER BY prod_name DESC

[^123]匹配除这些字符外的任何东西
[0-9]匹配数字
[a-z]匹配任意字母字符

转义字符\

\. 转义字符(escaping)，查找.

  1. SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name DESC LIMIT 3,3

image.png

匹配字符类

image.png

匹配重复元字符

image.png

计算字段

字段（field） 基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

  1. 连接字段 Contact()

    1. SELECT Contact(A,'(',B,')') AS A_B FROM vendors ORDER BY vend_name    
    2. SELECT Contact(Trim(A),'(',RTrim(B),')') FROM vendors ORDER BY vend_name

  2. 别名

    1. SELECT A,B,A*B AS A*B FROM vendors WHERE vend_price>70

  3. 简单计算

    1. SELECT 3*2   
    2. SELECT Trim(A)

    函数

    文本处理函数

  • Upper()
  • Lower()
  • Right() 找出字符串右侧的字符
  • Left() 找出字符串左侧的字符
  • Locate() 找出串的一个子串
  • Trim() LTrim() RTrim() 去空格
  • Soundex() 根据发音找出相近的字符
    1. SELECT cus_name,cus_price FROM cus 
    2. WHERE Soundex(cus_name) = Soundex('Li Yet')

    日期和时间处理函数

    image.png
    MySQL 4.1增加了很多关于时间处理的函数，如果在此版本之前则需要参考文档具体了解相关数据库支持的时间日期函数。
    1. SELECT cust_id,order_num FROM orders WHERE Year(order_date) AND
    2. Month(order_date)匹配出九月的所有订单

    数值处理函数

    image.png

    聚集函数

    1654662501282.png
    1. SELECT AVG(A) AS avg_A FROM Customers
    2. //聚焦不同值
    3. SELECT AVG(DISTINCT A) AS avg_dist_A FROM Customers

    分组数据

    1. SELECT prod_id,Count(*) as count_price FROM products GROUP BY prod_id分组
    2. // WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。
    3. SELECT prod_id,Count(*) AS order FROM orders GROUP BY prod_id Having Count(*)>2

    子查询

    1. SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2')

    连接表 INNER JOIN

    外键（foreign key）外键为某个表中的一列，它包含另一个表的主键值。
    1. // WHERE联结
    2. SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id
    3. // 内部联结 A INNER JOIN B ON xxx=xxx
    4. SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id
    5. // 外部联结 A LEFT/RIGHT OUTER JOIN B ON xxx=xxx
    6. SELECT

    复合查询 UNION

    UNION 自动返回删除重复的行，UNION ALL 自动返回所有的行（含重复的）
    1. SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5 
    2. UNION
    3. SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002)