MySQL Learning note 1-7

I. Understanding of databases and tables

• SHOW DATABASES; Returns a list of available databases
• SHOW TABLES; Returns the list that is available within the currently selected database
• SHOW COLUMNS from table name; Returns a row for each field, including the field name, data type, whether NULL is allowed, key information, default value, and other information

Ii. Retrieval of data

• Retrieving a single column: SELECT prod_name from Products;
• Retrieving multiple columns: SELECT prod_id, Prod_name, prod_price from products;
• Retrieve all columns: SELECT * FROM Products;
• Search not peer: keyword DISTINCT. SELECT DISTINCT vend_id from Products; Only vend_id different rows are returned. Note: You cannot partially use the distinct to apply this keyword to all columns, not just the columns that are predecessor to it. If a select DISTINCT vend_id is given, the prod_price from the products; unless the specified two columns are different, all rows will be retrieved.
• Limit result: Keyword: limit. SELECT Prod_name from Products LIMIT 5; This statement uses Select to retrieve a single column. Limit 5 indicates that MySQL returns no more than five elements; if the limit 5,5 indicates that MySQL returns 5 rows starting at line 5. Note: The first row retrieved is row 0 instead of line 1.

Third, sort the retrieval data

Keyword: ORDER by clause;

• Sort data: SELECT Prod_name from Products prder by Prod_name;
• Sort by multiple columns: SELECT prod_id, Prod_price, prod_name from Products Oeder by Prod_price, Prod_name; When sorting by more than one column, the sort is done exactly in the order specified
• Specify the sort direction: if ascending, then nothing is required to declare, default ascending; If descending, the DESC keyword is needed; SELECT prod_id, Prod_price, prod_name from Products ORDER by prod_  Price DESC, Prod_name; The DESC keyword is applied only to the column name immediately preceding it. If you want to sort on more than one column, you must specify the DESC keyword for each column.

IV. Filtering data

Use the WHERE clause: the WHERE clause is given after the table name (FROM clause). When you use both the ORDER BY and WHERE clauses, you should leave the order by in the where, or it will result in dislocation.

WHERE clause operator: between value 1 and value 2;between matches all values in the range, including the specified start and end values

Five, data filtering

Combine WEHRE clauses: used in the form of an AND clause or an OR clause

SELECT prod_id, Prod_price, prod_name from products WHERE vend_id = 1003

and Prod_price <= 10; And: Used to indicate the retrieval of rows that satisfy all given criteria.

SELECT Prod_name, Prod_price from products WHERE vend_id = 1002 OR vend_id

= 1003; OR: Used to retrieve rows that match either condition.

SQL prioritizes and operators before processing or operations; The workaround is to group the parentheses explicitly

The corresponding operator.

In operator: The in operator is used to specify the condition range, and each condition in the range can be matched

SELECT Prod_name, Prod_price from Products WHERE vend_id in (1001,1003) ORDER by Prod_name; Retrieve all products manufactured by suppliers 1002 and 1003. The in operator is followed by a comma-delimited list of legitimate values, and the entire list must be enclosed in parentheses.

The advantages of the in operator:

• The In operator syntax is clearer and more intuitive when using a long list of legitimate options
• When using in, the order of calculation is easier to manage,
• In operators are generally faster than the OR operator manifest
• The greatest advantage of in is that it can contain other SELECT statements, enabling a more dynamic creation of WHERE clauses

Vi. Filtering with wildcards

1. Link operator

Wildcard: A special character used to match a portion of a value.

Search mode: A search condition consisting of a literal value, a wildcard, or a combination of both

(1), percent semicolon (%) wildcard characters

In the search string,% indicates any number of occurrences of any field.

SELECT prod_id, prod_name from the products WHERE the prod_name like ' jet% ';

Search find all the products with the word jet beginning

% can appear in the front end of the string and in the middle: ' s%e ', '%anv% '

In addition to one or more characters,% can also match 0 characters.

(2), Underline (_) wildcard characters

The underscore is used in the same way as%, but the underscore matches only a single character instead of multiple characters

SELECT prod_id, prod_name from products WHERE prod_name like ' _ Ton ';

and% can match 0 characters not the same, _ always match a character, can not more nor less

2. Tips for using wildcard characters

• Do not use wildcard characters excessively;
• Do not use them at the beginning of the search mode unless absolutely necessary;

MySQL Learning note 1-7