Mysql-select Statement Advanced Application

1.1 Select Advanced Applications 1.1.1 Pre-prep work

This test uses the world database, by MySQL Official offer :

Https://dev.mysql.com/doc/index-other.html

World file Import method, official Description:

Https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html

Download SQLyog software that is used for subsequent database management purposes :

Http://www.webyog.com

Create user , be able to let SQLyog log in database, pay attention to permission control.

Mysql> Grant All on * * to [e-mail protected] '% ' identified by ' 123 '; Query OK, 0 rows Affected (0.00 sec)

After authorized users see

Mysql> Select User,host from mysql.user where user like ' root '; +------+-----------+| user | Host      |+------+-----------+| Root |%         | | root | 10.0.0.1  | | root | 127.0.0.1 | | root | localhost |+------+----- ------+4 rows in Set (0.00 sec)

1.1.2 Select Syntax Format description

mysql> Help Select; Name: ' SELECT ' Description:Syntax:SELECT    [All | DISTINCT | Distinctrow] [      high_priority] [      straight_join]      [Sql_small_result] [Sql_big_result] [Sql_buffer_result]      [Sql_cache | Sql_no_cache] [sql_calc_found_rows]    select_expr [, select_expr ...]    [From Table_references      [PARTITION Partition_list]    [WHERE Where_condition]    [GROUP by {col_name | expr | position}      [ASC | DESC], ... [with ROLLUP]]    [Having where_condition]    [ORDER by {col_name | expr | position}      [ASC | DESC], ...]    [LIMIT {[offset,] row_count | row_count offset offset}]    [PROCEDURE procedure_name (argument_list)]    [Into OUTFILE ' file_name '        [CHARACTER SET Charset_name]        Export_options      | Into DumpFile ' file_name '      | into Var_name [, Var_name]]    [For UPDATE | LOCK in SHARE MODE]]

The WHERE clause in 1.2 select uses the

SELECT  *|{ [DISTINCT]  

where description of the condition:

The Where condition is also called the filter condition, which removes all conditions from the intermediate result of the FROM clause conditions not true (and false or null).

The WHERE clause follows the FROM clause, and you cannot use a column alias in the WHERE clause.

Basic use of the "example one" WHERE clause

SELECT * from World "City" where countrycode= ' CHN '; Orselect * from the world, ' city ' where countrycode= ' CHN ';

SQL Description: Find a city in China from the database.

Attention:

The string and date literals that appear in the where must be enclosed in quotation marks.

Here, the string literal is the same as the uppercase or lowercase result, which is a case-insensitive query.

This differs from Oracle in that the literals in the Where condition in Oracle are case-sensitive

Logical operators in the "example two" WHERE clause

SELECT * from world ' city ' WHERE countrycode= ' chn ' and district = ' Shanxi ';

SQL Description: Search from the database is China and is a city of Shanxi

Introduction to Logical operators:

logical operator	description
and	Logic and. and returns true only if all of the child conditions are true. Otherwise returns false or null
or	logical OR. Returns true as long as there is a child condition of true,or. Otherwise returns false or null
not	logical non. Returns false if the child condition is true, or true if the child condition is false
XOR	Logical XOR. When one child condition is true and the other child condition is false, the result is true; When all two conditions are true or false, the result is false. Otherwise, the result is null

"Example three": range comparison in the WHERE clause

SELECT * from world ' city ' WHERE population between 100000 and 200000;

SQL Note: Find a city with a population of 100000-200000 from the database

"Example four": in in the WHERE clause

SELECT * from Citywhere CountryCode in (' CHN ', ' JPN ');

SQL Description: Query all cities in China and Japan

"Example five": like in the WHERE clause

Use the world; SELECT * from Citywhere countrycode like ' ch% ';

SQL Description: Find the country from the city table is a CH beginning.

Syntax for like:

Like ' Match pattern string '

Implement pattern matching queries or fuzzy queries: Test whether a column value matches the given pattern

In the ' match pattern string ', there can be two wildcard characters with special meanings:

        %: denotes 0 or any number of characters        _: Only one arbitrary character is represented

The ORDER BY clause in 1.3 Select 1.3.1 The ORDER BY clause

The ORDER BY clause is used to sort rows

If there is no ORDER BY clause in the SELECT statement, the order of the rows in the result set is not predictable

Grammar:

SELECT  exprfrom  table[where condition (s)][order  by  {column, expr, numeric_position} [asc| DESC]];

some parameter description:

Parameters	Parameter description
Asc	Performs an ascending sort. Default value
DEsc	Perform descending sort
How to use	The ORDER by clause is typically the last face of the SELECT statement

1.3.2 ORDER BY example

"Example One" Order by basic use

SELECT * from Cityorder by population;

SQL Note: List city tables in ascending order of population

"Example two" multiple sort criteria

SELECT * from Cityorder by Population,countrycode;

SQL Description: Sort by population and country

"Example three" is sorted by column numbering of select words

SELECT * from Cityorder by 5;

SQL Note: Sort by 5th Column

"Example four" desc practice

SELECT * from Cityorder by 5 DESC;

SQL Note: Reverse ORDER by column

Description: NULL Sorting of Values

In MySQL, the null value is treated as the minimum value in a column value.

Therefore, when sorting in ascending order, it appears at the front.

1.4 LIMIT Clause

Feature Description:

MySQL special clause.

It is the last clause in the SELECT statement (after order by).

It is used to indicate that the first or last few rows are selected from the result set.

The minimum value of offset offsets is 0.

Grammar:

Limit  < rows fetched > [OFFSET < number of skipped rows;] or limit [< the number of rows skipped;,] < The number of rows to be fetched >  

query Example

SELECT * from Cityorder by 5 Desclimit 4;

SQL Note: Get the first 4 rows after sorting

Note: Sort in descending order of population, then use limit to pick out the first 4 rows.

If there is no ORDER BY clause, the 4 rows returned are not predictable.

1.5 Multi-table connection query 1.5.1 traditional connection notation (using where)

SELECT Name,ci.countrycode, Cl.language, Ci.populationfrom city  ci, countrylanguage clwhere ci. ' CountryCode ' = Cl.countrycode;

SQL Description: City is the alias of CI, the state alias is the CL, to make a table query, name is a common key value, using the Where condition to connect.

Note: Once an alias is defined for a table, the original table name cannot appear in the other clauses of the statement.

1.5.2 NATURAL join clause

Automatically finds all columns of the same type with the same name in the two tables to make the join columns, to make an equal connection

SELECT Name,countrycode, LANGUAGE, Populationfrom city  NATURAL  JOIN  countrylanguagewhere population > 1000000ORDER by population;

SQL Description: Use natural join for an equal connection, two tables, with a population greater than 1000000, in ascending order.

Note: Only one connection column can appear in the SELECT clause

1.5.3 using a using clause

SELECT Name,countrycode, LANGUAGE, Populationfrom city  JOIN  countrylanguageusing (CountryCode);

SQL Description: Use join for two tables to connect, using to specify CountryCode as the associated column.

1.5.4 Collection Operations

UNION [Distinct]union All

Grammar:

SELECT ... UNION [All | DISTINCT] SELECT ... [UNION [All | DISTINCT] SELECT ...]

? Union is used to combine the result set of two or more select queries into a single

? Two queries that are merged, whose select list must be consistent on the number and data type of the corresponding column

? Duplicate rows in two query result sets are removed by default

? Default result set is not sorted

? The column name of the final result set comes from the select list of the first query

1.5.5 grouping operation and grouping processing

"GROUP BY" is the literal understanding that the data is grouped according to the rules specified by the "by", in which the so-called grouping is dividing a "dataset" into several "small regions" and then processing data for several small regions.

The difference between having and where

The purpose of the WHERE clause is to remove rows that do not conform to the where condition before grouping the results of the query, that is, filtering the data before grouping, where the cluster function cannot be included, and the Where condition is used to filter out specific rows.

The HAVING clause is used to filter groups that satisfy a condition, that is, to filter the data after grouping, often with clustering functions, to filter out specific groups using the having condition, or to group by using multiple grouping criteria.

1.5.6 "Select Advanced Application" database backup script stitching

Select CONCAT ("Mysqldump", "-uroot", "-p123", Table_schema, "", TABLE_NAME, ">/tmp/", Table_schema, "_", TABLE_NAME, " . sql ") from Information_schema.tableswhere table_schema= ' to OUTFILE '/tmp/world_bak.sh '

Use CONCAT for stitching data backup scripts.

--Display information, can be directly calculated

Select CONCAT ("section"), select CONCAT ("132+123"), select CONCAT ("132+123");

--View engine is a table of InnoDB

SELECT table_name from  TABLES WHERE engine= ' InnoDB '; SELECT character_set_name, Collation_namefrom   information_schema. Collationswhere  is_default = ' Yes ';

--show how many tables are under each library

SELECT Table_schema, COUNT (*) from INFORMATION_SCHEMA. ' TABLES ' GROUP by Table_schema;

1.5.7 Sub-query

Sub-query definition

Another table expression can be called in a table expression, which is called a subquery (subquery), and I am also called a sub-selection (subselect) or inline selection (inner Select). The result of the subquery is passed to the table expression that called it to continue processing.

Subquery (inner query) executes first, then executes the main query (outer query)

Sub-query by the return result set of the call method, can be divided into: where sub-query, from sub-query and exists-type sub-query.

Using sub-query principles

A subquery must be placed in parentheses.

Place the subquery on the right side of the comparison condition to increase readability.

The subquery does not contain an ORDER by clause. Only one ORDER by clause can be used for a SELECT statement, and it must be placed at the end of the main SELECT statement if it is specified.

There are two comparison conditions that can be used in a subquery: a single-line operator (>=, =, <, <>, <=), and a multiline operator (in, any, all).

Unrelated subqueries

There are no columns in the subquery that are used in the table to the outer query. Execute the subquery first and then execute the external query

correlated subqueries (correlated subquery)

Any column in a table that is used in a subquery to an external query. Execute the external query first, then execute the subquery

The above two types can be divided into:

Row subquery (Row subquery): The result set returned is 1 Rows N column column subquery (column subquery): The result set returned is N row 1 list subquery (table subquery): The result set returned is n row n column scalar quantum query (SC Alar subquery): Returns a value of 1 rows and 1 columns

subquery Example

Create a data table

View Code CREATE DATABASE statement

Example one, get the player number with the same gender as the number 100th and live in the same city.

Select Playerno from   players   where (sex, town) = (      select sex, town from       players       where Playerno = 100) ;  

Example two, get the number of players who were born in the same year as number 27th

Select Playerno from   players   where year (birth_date) =       (select year (birth_date) from       players       where Playerno =   Playerno <> 27;

For example, get the names and initials of the players who have paid at least one fine.

Select name, initials from   players   where exists       (SELECT * from penalties       where Playerno = Players.playerno);  

For example, get the names and initials of players who have never been fined.

Select name, initials from   players   where isn't exists       (SELECT * from penalties       where Playerno = Players . Playerno);  

1.6 Informatica_schema Get metadata 1.6.1 Metadata access Methods

Query the INFORMATION_SCHEMA database table. Contains data about all objects managed by the MySQL database server

Use the SHOW statement. MySQL-specific statement for obtaining database and table information

Use the DESCRIBE (or DESC) statement. Shortcuts for checking table structure and column properties

Use the Mysqlshow client program. Command-line program for SHOW syntax

Information_schema Introduction to Database Benefits

Serves as a central repository for database metadata, schema and schema objects, server statistics (state variables, settings, connections).

Use tabular format for flexible access, using any SELECT statement. is a virtual database, the table is not a "real" table (the base table), but a system view that populates the table dynamically based on the privileges of the current user.

List Information_schema all tables in the database:

mysql> use  information_schema;database changedmysql>  SHOW tables;+--------------------------------- ------+| Tables_in_information_schema          |+---------------------------------------+| Character_sets                        | | Collations                            | | collation_character_set_applicability | | COLUMNS                               | | Column_privileges                     |

1.6.2 using SELECT for INFORMATION_SCHEMA

Example one:

The lookup engine is a InnoDB table.

SELECT table_name, Enginefrom  information_schema. Tableswhere engine= ' InnoDB ';

Example two:

SELECT TABLE_SCHEMA, TABLE_NAME, column_name from   information_schema. COLUMNS WHERE  data_type = ' Set ';

SQL Description: Lookup data type is set table

Example three:

SELECT character_set_name, Collation_name,is_defaultfrom   information_schema. Collationswhere  is_default = ' Yes ';

SQL Note: Look for a table with the default Yes

Example four:

SELECT Table_schema, COUNT (*)  from   information_schema. TABLES GROUP by Table_schema;

SQL Description: View the number of tables under each database

Use the INFORMATION_SCHEMA table to get information about creating shell commands.

Select CONCAT ("Mysqldump-uroot-p",   Table_schema, "",  table_name, ">>",   table_schema, ". Bak.sql") From TABLES WHERE table_name like ' country% '

1.6.3 use the MySQL command to create the SQL statement.

mysql-uroot-p123--silent--skip-column-names-e "Select CONCAT (' CREATE TABLE ', Table_schema, '. ', table_name, ' _backup Like ', Table_schema, '. ', table_name, '; ') From INFORMATION_SCHEMA. TABLES WHERE table_schema = ' world_innodb '; "

1.6.4 the show statement in MySQL

SOHW databases: List all databases show TABLES: List the tables in the default database show TABLES from <DATABASE_NAME>: Lists tables in the specified database show COLUMNS from < Table_name>: Displays the column structure of the table show index from <TABLE_NAME>: Displays information about indexes and indexed columns in the table show CHARACTER set: Displays the available character sets and their default grooming show COLLATION: Displays the collation of each character set Show status: List current database status show VARIABLES: List parameter definition values in the database show Processlist  View current number of connections

1.6.5 DESCRIBE Statements

The DESCRIBE statement is equivalent to SHOW COLUMNS

General Syntax:

Mysql> DESCRIBE <table_name>;

Display INFORMATION_SCHEMA table information

Mysql> DESCRIBE information_schema. character_sets;+----------------------+-------------+------+-----+---------+-------+| Field                | Type        | Null | Key | Default | Extra |+----------------------+-------------+------+-----+---------+-------+| Character_set_name   | varchar (32) | NO | | | |       | Default_collate_name | varchar (32) | NO | | | |       | DESCRIPTION          | varchar (60) | NO | | | |       | MaxLen               | bigint (3)   | NO   |     | 0       |       | +----------------------+-------------+------+-----+---------+-------+4 rows in Set (0.00 sec)

Information about the structure of the database and table is similar to the SHOW statement

General Syntax:

Shell> mysqlshow [Options] [db_name [Table_name[column_name]]

Displays information about all databases or specific databases, tables, and/or columns:

[[email protected] ~]#  mysqlshow-uroot-p123warning:using a password on the command line interface can be insecure.+ --------------------+|     Databases      |+--------------------+| information_schema | | clsn               | | haha               | | mysql              | | Oldboy             | | Performance_schema | | World              |+--------------------+

View a table under a database

[[email protected] ~]#  mysqlshow-uroot-p123 worldwarning:using a password on the command line interface can be inse Cure. database:world+-----------------+|     Tables      |+-----------------+| Penalties       | | PLAYERS         | | City            | | country         | | countrylanguage |+-----------------+

View database below table records

[[email protected] ~]# mysqlshow-uroot-p123 World citywarning:using A password on the command line interface can Be insecure. Database:world table:city+-------------+----------+-------------------+------+-----+---------+----------------+ ---------------------------------+---------+| Field | Type | Collation | Null | Key | Default | Extra | privileges | Comment |+-------------+----------+-------------------+------+-----+---------+----------------+----------------- ----------------+---------+| ID |                   Int (11) | | NO |         PRI | | auto_increment |         select,insert,update,references | || Name | char (35) | Latin1_swedish_ci |     NO |         |                | |         select,insert,update,references | || CountryCode | CHAR (3) | Latin1_swedish_ci | NO |         MUL |                | |         select,insert,update,references | || District | char (20) | Latin1_swedish_ci |     NO ||                | |         select,insert,update,references | || Population |                   Int (11) | |     NO | |                0 | |         select,insert,update,references | |+-------------+----------+-------------------+------+-----+---------+----------------+------------------------ ---------+---------+

View record information

[Email protected] ~]# mysqlshow-uroot-p123 World City  countrycodewarning:using a password on the command line inte Rface can be insecure. Database:world  table:city  wildcard:countrycode+-------------+---------+-------------------+------+---- -+---------+-------+---------------------------------+---------+| Field       | Type    | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |+-------------+---------+-------------------+------+-----+---------+-------+--------------------------- ------+---------+| CountryCode | CHAR (3) | Latin1_swedish_ci | NO   | MUL |         |       | select,insert,update,references |         | +-------------+---------+-------------------+------+-----+---------+-------+---------------------------------+- --------+

The view database looks like.

[[email protected] ~]#  mysqlshow-uroot-p123 "w%" warning:using a password on the command line interface can be insec Ure. wildcard:w%+-----------+| Databases |+-----------+| World     |+-----------+

1.7 References

https://dev.mysql.com/doc/refman/5.6/en/select.html         Select Syntax official description
Shanyan
Source: https://www.nmtui.com

Mysql-select Statement Advanced Application