Mysql select statement

select statement可以从表或视图中查询数据

---

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;

SELECT statement examples：

SELECT
lastname,firstname,jobtype 
FROM
employees;

SELECT * FROM employees;

---

MySQL DISTINCT clause

SELECT DISTINCT
    columns
FROM
    table_name
WHERE
    where_conditions;

MySQL DISTINCT example:

SELECT DISTINCT
lastname 
FROM
employees
ORDER BY lastname;

1. If a column has NULL values and you use the DISTINCT clause for that column, MySQL keeps one NULL value and eliminates the other.
2. use the DISTINCT clause with more than one column. In this case, MySQL uses the combination of all columns to determine the uniqueness of the row in the result set.

SELECT DISTINCT
    state, city
FROM
    customers
WHERE
    state IS NOT NULL
ORDER BY state , city;

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

SELECT
  state
FROM
  customers
GROUP BY
  state;

1. use the DISTINCT clause with an aggregate function e.g., SUM, AVG, and COUNT, to remove duplicate rows before MySQL applies the aggregate function to the result set.

SELECT 
    COUNT(DISTINCT state)
FROM
    customers
WHERE
    country = 'USA';

1. use the DISTINCT clause with the LIMIT clause, MySQL stops searching immediately when it finds the number of unique rows specified in the LIMIT clause

SELECT DISTINCT
    state
FROM
    customers
WHERE
    state IS NOT NULL
LIMIT 5;

WHERE clause

use WHERE clause to specify exact rows to select based on particular filter express or condition

SELECT 
    lastname,firstname,jobtype
FROM
    employees
WHERE
    jobtype='Sales Rep' AND officeCode = 1;

the comparison operators that can use to form filtering expressions in the WHERE clause.

Operator	Description
=	Equal to. You can use it with almost any data types.
<> or !=	Not equal to.
<	Less than. You typically use it with numeric and date/time data types.
>	Greater than.
<=	Less than or equal to
>=	Greater than or equal to

Note:

---

1. some useful operators that you can use in the WHERE clause to form complex conditions:BETWEEN,LIKE,IN,IS NULL.
2. The WHERE clause is used not only with the SELECT statement but also other SQL statements to filter rows such as DELETE and UPDATE.

   ORDER BY clause

   use the ORDER BY clause to sort the result set

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

ORDER BY sort by an expression example:

SELECT
 ordernumber,
 orderlinenumber,
 quantityOrdered * priceEach AS subtotal
FROM
 orderdetails
ORDER BY
 ordernumber,
 orderLineNumber,
 subtotal;

ORDER BY with customer sort order:
define custom sort order use FIELD() function

SELECT 
    orderNumber, status
FROM
    orders
ORDER BY FIELD(status,
        'In Process',
        'On Hold',
        'Cancelled',
        'Resolved',
        'Disputed',
        'Shipped');

Natural Sorting with ORDER BY Clause:

SELECT 
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;

the LENGTH function returns the length of a string

SELECT 
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;

use the parentheses statement returns the customers who locate in the USA or France and have credit limit greater than 10000:

SELECT 
    customername, country, creditLimit
FROM
    customers
WHERE
    (country = 'USA' OR country = 'France')
        AND creditlimit > 100000;

IN Operator

The IN operator allows you to determine if a specified value matches any one of a list or a subquery:

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

find out the offices that locate in the U.S. and France:

SELECT 
    officeCode, city, phone, country
FROM
    offices
WHERE
    country IN ('USA' , 'France');

get offices that do not locate in USA and France, you use NOT IN in the WHERE clause:

SELECT 
    officeCode, city, phone
FROM
    offices
WHERE
    country NOT IN ('USA' , 'France');

MySQL IN with subquery:

SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (SELECT 
            orderNumber
        FROM
            orderDetails
        GROUP BY orderNumber
        HAVING SUM(quantityOrdered * priceEach) > 60000);

BETWEEN Operator

use the BETWEEN operator in the WHERE clause of the SELECT, INSERT, UPDATE, and DELETE statements:

expr [NOT] BETWEEN begin_expr AND end_expr;

find products whose buy prices are within the ranges of $90 and $100:

SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 90 AND 100;

get the orders whose required dates are from 01/01/2003 to 01/31/2003:

SELECT orderNumber,
         requiredDate,
         status
FROM orders
WHERE requireddate
    BETWEEN CAST('2003-01-01' AS DATE)
        AND CAST('2003-01-31' AS DATE);

赏

谢谢你请我吃糖果