Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of Mariadb's single-table query method, the content is detailed and easy to understand, the operation is simple and fast, and it has a certain reference value. I believe you will gain something after reading this Mariadb single-table query method article, let's take a look at it.

MariaDB Server is one of the most popular open source relational databases. It was created by the original developers of MySQL and guaranteed to remain open source. It is part of most cloud products and is the default configuration for most Linux distributions. MariaDB is designed as a direct alternative to MySQL, with more features, a new storage engine, fewer errors and better performance.

Single table simple query

A few days ago, I wrote down the study notes such as creating, deleting, modifying databases, tables, and so on. Today I finally start to query, query data ~ anyway, it is very difficult in my heart, after all, SQL is not easy to write, stupid ah.

First of all, Mysql officially provided us with a database example, which is the famous world.sql.

After entering the database, enter SOURCE / PATH/world.sql

For example:

MariaDB [world] > SOURCE / root/world.sqlMariaDB [world] > SHOW DATABASES / / you can see our world library +-+ | Database | +-+ | information_schema | | mydb | | mysql | | performance_schema | | world | +-+ MariaDB [world] > USE world / / switch database Database changedMariaDB [world] > SHOW TABLES; / / there are three tables for us to use +-+ | Tables_in_world | +-+ | city | | country | | countrylanguage | +-+ 3 rows in set (0.00 sec) MariaDB [world] > DESC city / / structure of city table +-+-+ | Field | Type | Null | Key | Default | Extra | +-+-+ -+ | ID | int (11) | NO | PRI | NULL | auto_increment | | Name | char (35) | NO | | CountryCode | char (3) | NO | MUL | | District | | char (20) | NO | | Population | int (11) | NO | | 0 | | +-+ + |

Very simple single table query

Let's first look at the syntax of the SELECT statement:

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 [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]

Where select_expr can be the following value:

Column name *-> all column Mysql functions and various operators tbl _ name.* refer to column 1 of other tables. Query all columns

There's nothing to say about this, it's very simple.

MariaDB [world] > SELECT * FROM city;2. Query the specified column and display its column as an alias

Aliases are a very useful feature ~ especially when querying multiple tables

MariaDB [world] > SELECT ID as' iid' FROM city LIMIT 2 + | iid | +-+ | 129 | | 1 | +-+ 3. Limit the number of rows displayed-LIMIT

Previously, the LIMIT,LIMIT keyword was also used to receive two parameters, the first parameter is the offset position, and the second parameter is to display the number of rows

MariaDB [world] > SELECT * FROM city LIMIT 2 / / it is sorted by primary key by default, so 2 indicates that the third row of data is displayed. A total of two lines +-+ | ID | Name | CountryCode | District | Population | + -+-+ | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | + -+ 2 rows in set (0.01sec) 4. Display after sorting the data by the specified column

ODER BY was also used before. Sorting according to the specified column ~ ASC is displayed in order (default, from small to large), and DESC is displayed in reverse order. When multiple columns are specified, they are first sorted by the previous column (divided into a group), and then sorted by the following columns in the group, and so on. The following shows the two most populous countries:

MariaDB [world] > SELECT * FROM city ORDER BY Population DESC LIMIT 2 +-+ | ID | Name | CountryCode | District | Population | +- -- + | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 2331 | Seoul | KOR | Seoul | 9981619 | +- -+-+ 2 rows in set (0.01 sec) 5. Remove duplicate data SELECT DISTINCT column name FROM table name; SELECT DISTINCT * FROM city; / / of course, there is no duplicate data in this city table

Let's create a table and test it:

MariaDB [world] > CREATE TABLE test (name VARCHAR (50), pass VARCHAR (50)); MariaDB [world] > INSERT INTO test VALUES / / insert 4 test data-> ('test','123'),-> (' test','321'),-> ('test','123'),-> (' test1','123'); MariaDB [world] > SELECT DISTINCT * FROM test +-+-+ | name | pass | +-+-+ | test | 123 | test | 321 | test1 | 123 | +-+-+ MariaDB [world] > SELECT DISTINCT name,pass FROM test +-+-+ | name | pass | +-+-+ | test | 123 | test | 321 | test1 | 123 | MariaDB [world] > SELECT DISTINCT pass,name FROM test +-+-+ | pass | name | +-+-+ | 123 | test | | 321 | test | | 123 | test1 | +-+

Therefore, identical rows are excluded as duplicate data.

6. Sort using constants, expressions, functions MariaDB [world] > SELECT 'The City Info:', / / displays a column of constants, of course, it can also be a variable Name, / / an ordinary column of CountryCode AS Country, / / use aliases Population*100 AS Population, / / a pair of population magnified 100x MD5 (ID) AS ID / / use the MD5 function to generate the MD5 value FROM city ORDER BY Population DESC LIMIT 2 of the column ID +-+ | The City Info: | Name | Country | Population | ID | | The City Info: | Mumbai (Bombay) | IND | 1050000000 | 021bbc7ee20b71134d53e20206bd6feb | | The City | Info: | Seoul | KOR | 998161900 | 273448411df1962cba1db6c05b3213c9 | +-- + 2 rows in set (sec)

Filter the result set using the where clause

The above, we only control which columns are displayed, but not which rows are displayed, of course, LIMIT may control which rows are displayed; but often we have more complex requirements, such as finding all cities with a population greater than 1000000, then we can use the WHERE clause to control which rows are displayed.

Of course, these filtering actions can be done at the business layer, but is it necessary for us to transfer tens of thousands of pieces of data but only use one of them? Moreover, who can guarantee that the filtering algorithm in the business layer is more efficient than that in the database management system? So very few people filter data at the business layer.

[WHERE where_condition]

To use the WHERE clause, you only need to follow where_condition, which includes operators, functions, and so on. As long as this line of data makes the expression TRUE, this line is displayed:

Common operators: greater than: >; less than: equal to: =; greater than or equal to: > =; less than or equal: not equal to:! =; 1. Query cities with a population of more than 1000000 MariaDB [world] > SELECT * FROM city WHERE Population > = 1000000 +-+ | ID | Name | CountryCode | District | Population | +-+- -+-+-- +-+ | 1 | Kabul | AFG | Kabol | 1780000 | | 35 | Alger | DZA | Alger | 2168000 | | 56 | Luanda | AGO | Luanda | 2022000 | | 69 | Buenos Aires | ARG | Distrito Federal | 2982146 |. Manual omission. | 3798 | Phoenix | USA | Arizona | 1321045 | | 3799 | San Diego | USA | California | 1223400 | 3800 | Dallas | USA | Texas | 1188580 | | 3801 | San Antonio | USA | Texas | 1144646 | | 4068 | Harare | ZWE | Harare | 1410000 | +- -+ 238 rows in set (0.09 sec) / / you can see There are 238 cities with a population greater than 10000002. Null operation-> IS NULL

Returns TRUE if the value of the given column is NULL, otherwise returns FALSE

MariaDB [world] > SELECT * FROM city WHERE CountryCode IS NULL;Empty set (0.00 sec) MariaDB [world] > SELECT NULL IS NULL; / / returns TRUE+-+ | NULL IS NULL | +-+ | 1 | +-+ 1 row in set (0.01 sec) 3 only if the specified value is NULL. Logic and AND operator

Expression (test condition) 1 AND expression (test condition) 2: the entire expression holds when both expression 1 and expression 2 are TRUE

Inquire about all cities with a population of more than 100000 in China:

MariaDB [world] > SELECT * FROM city WHERE CountryCode = 'CHN' AND Population > 100000 +-+ | ID | Name | CountryCode | District | Population | +- -- + | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 1891 | Peking | CHN | Peking | 7472000 |. Manually omitted... | 2228 | Zhucheng | CHN | Shandong | 102134 | | 2229 | Kunshan | CHN | Jiangsu | 102052 | | 2230 | Haining | CHN | Zhejiang | 100478 | + -- + 341 rows in set (0.00 sec) / / A piece of data is displayed only when both conditions are met. Logic or operator OR

Expression 1 OR expression 2: when any expression is TRUE, the whole expression is TRUE, and expression 2 is no longer judged when expression 1 is already TRUE

List all the cities in Beijing and Shanghai:

The area to which a city belongs cannot belong to both Beijing and Shanghai, so it is the combination of Shanghai and Beijing:

MariaDB [world] > SELECT * FROM city WHERE District = 'Peking' OR District =' Shanghai'

+-+ | ID | Name | CountryCode | District | Population | +- -+ | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 1891 | Peking | CHN | Peking | 7472000 | 2236 | Tong Xian | CHN | Peking | 97168 | +-+ 3 rows in set (0.00 sec) 5. Logic is used with or

When there are too many conditions, it can be used in combination with, or operation, such as:

Inquire about cities with a population of more than 1000000 in Beijing or Zhejiang:

MariaDB [world] > SELECT * FROM city WHERE District = 'Peking' OR District =' Zhejiang' AND Population > 1000000 +-+ | ID | Name | CountryCode | District | Population | +- -+ | 1891 | Peking | CHN | Peking | 7472000 | | 1905 | Hangzhou | CHN | Zhejiang | 2190500 | 1915 | Ningbo | CHN | Zhejiang | 1371200 | | 2236 | Tong Xian | CHN | Peking | 97168 | +-- -+ 4 rows in set (0.00 sec) / / logical operation is a binocular operator from left to right So the condition District = 'Peking' OR District =' Zhejiang' is judged first, and then the TRUE or FALSE obtained after this condition is judged, and then the Boolean value is used to operate with AND Population > 1000000.

It is recommended to use () when using AND and OR together, so it is not easy to mess up.

Inquire about the cities with a population of less than 100000 and greater than 10000 in Zhejiang:

MariaDB [world] > SELECT * FROM city WHERE District = 'Zhejiang' AND (Population > 10000 AND Population in set (0.00 sec) / / although this example does not seem necessary to use (). Dry smile.

Today, I suddenly found something in the document that AND has a higher priority than OR, so take a look at the following example:

Inquire about cities with a population of more than 100000 in Zhejiang or Beijing:

MariaDB [world] > SELECT * FROM city WHERE District = 'Peking' OR District =' Zhejiang' AND Population > 100000 +-+ | ID | Name | CountryCode | District | Population | +- -+ | 1891 | Peking | CHN | Peking | 7472000 | | 1905 | Hangzhou | CHN | Zhejiang | 2190500 | 1915 | Ningbo | CHN | Zhejiang | 1371200 |. Manually omit... | 2199 | Yuyao | CHN | Zhejiang | 114065 | | 2230 | Haining | CHN | Zhejiang | 100478 | 2236 | Tong Xian | CHN | Peking | 97168 | / / look at this line, why is the population 97168 filtered out? +-+ 16 rows in set (0.00 sec) / / the reason is that District = 'Zhejiang' AND Population > 100000 is FALSE, and then calculates with the previous OR, which happens to be in Beijing. / / so if you want to filter data correctly, you need to add a parenthesis 6. Range detection BETWEEN AND

TRUE if a value satisfies a continuous range; otherwise, FALSE

Query cities with ID range from 1 to 10:

MariaDB [world] > SELECT * FROM city WHERE ID BETWEEN 1 AND 10 +-+ | ID | Name | CountryCode | District | Population | + -+-+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | 8 | Utrecht | NLD | Utrecht | 234323 | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | 10 | Tilburg | NLD | Noord- Brabant | 193238 | +-+ 10 rows in set (193238 sec)

Just used Name BETWEEN 'abc' AND' efg' as a condition to filter, unexpectedly can filter out more than 900 rows of data, do not know what principle.

It seems to match only the ASCII value of the first character, this thing really doesn't understand what scene will use the character. .

BETWEEN 1 AND 10 equals > = 1 AND

7. Discrete range detection IN

TRUE when a value belongs to a piece of discrete data, for example, 1 IN (2 IN 3) obviously 1 is not equal to 2 or 3, so it is FALSE

Query the cities where ID belongs to 1, 3, 5, 7, 9:

MariaDB [world] > SELECT * FROM city WHERE ID IN (1pm, 3pm, 5pm, 7pm) +-+ | ID | Name | CountryCode | District | Population | + -+-+ | 1 | Kabul | AFG | Kabol | 1780000 | | 3 | Herat | AFG | Herat | 186800 | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | + -+ 5 rows in set (0.01 sec)

Inquire about all cities in Beijing, Zhejiang and Henan:

MariaDB [world] > SELECT * FROM city WHERE District IN ('Peking','Zhejiang','Henan') +-+ | ID | Name | CountryCode | District | Population | +- -- +-+ | 1891 | Peking | CHN | Peking | 7472000 | | 1905 | Hangzhou | CHN | Zhejiang | 2190500 | 1906 | Zhengzhou | CHN | Henan | 2107200 | | 1915 | Ningbo | CHN | Zhejiang | 1371200 | | 1934 | Luoyang | CHN | Henan | 760,000 | | 1951 | Kaifeng | CHN | Henan | | 510000 |. Manually omitted... | 2214 | Cixi | CHN | Zhejiang | 107329 | | 2230 | Haining | CHN | Zhejiang | 100478 | 2236 | Tong Xian | CHN | Peking | 97168 | | 2242 | Yuzhou | CHN | Henan | 92889 | | 2246 | Linhai | CHN | Zhejiang | 90870 | | 2252 | Huangyan | CHN | Zhejiang | 89288 | +-+ 36 rows in set (sec)

Therefore, the IN operator is very similar to the function of OR, for example, District IN ('Peking','Zhejiang','Henan') equals District =' Peking' OR District = 'Zhejiang' OR District =' Henan'

8. Logical non-NOT

Judge after logical non-operation of its original Boolean value.

For example: query ID

ID=10

MariaDB [world] > SELECT * FROM city WHERE NOT ID > = 10 +-+ | ID | Name | CountryCode | District | Population | + -+-+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | + | -+ 9 rows in set (0.00 sec)

Therefore, NOT can be used for any of the above operations, such as cities where the ID range is not in the range of 10-4000:

MariaDB [world] > SELECT * FROM city WHERE ID NOT BETWEEN 10 AND 4000 +-+ | ID | Name | CountryCode | District | Population | +-+- -+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | | Herat | 186800 |.. Manually omitted... | 4077 | Jabaliya | PSE | North Gaza | 113901 | | 4078 | Nablus | PSE | Nablus | 100231 | | 4079 | Rafah | PSE | Rafah | 0 | +-+-+ 88 rows in set (92020 sec) 9. String search LIKE

Using LIKE, you can retrieve strings that match wildcards, with the following two strings:

%: any arbitrary character _: single arbitrary character

Search for cities whose names begin with T:

MariaDB [world] > SELECT * FROM city WHERE Name LIKE'Y%' +-+ | ID | Name | CountryCode | District | Population | +- -+ | Yerevan | ARM | Yerevan | 1248700 | | 516 | York | GBR | England | 104425 | | 955 | Yogyakarta | IDN | Yogyakarta | 418944 | | 1220 | Yamuna | Nagar | IND | Haryana | 144346 | | 1300 | Yeotmal (Yavatmal) | IND | Maharashtra | 108578 | | 1396 | Yazd | IRN | Yazd | 326776 |. Manually omitted.. | 3888 | Yonkers | USA | New York | 196086 | +- -+-+ 63 rows in set (0.00 sec)

Search for cities with three-letter names:

MariaDB [world] > SELECT * FROM city WHERE Name LIKE'_ _' +-+ | ID | Name | CountryCode | District | Population | +- -+-+ | 29 | Ede | NLD | Gelderland | 101574 | | Itu | BRA | S ã o Paulo | 132736 | | Ja ú | BRA | S ã o Paulo | 109965 | | Po á | BRA | S ã o Paulo | 89236 | 1387 | Qom | IRN | Qom | 777677 |.... | 2902 | Ica | PER | Ica | 194820 | | 3134 | Ulm | DEU | Baden-W ü rttemberg | 116103 | | 3379 | Van | TUR | Van | 219319 | | 3588 | Ufa | RUS | Ba roomkortostan | 1091200 | | 3775 | Hue | VNM | Thua Thien-Hue | 219149 | +- -+ 31 rows in set (0.00 sec) MariaDB [world] > SELECT * FROM city WHERE CHAR_LENGTH (Name) = 3 / / same as the above results. This is the end of the article on "single table query method of Mariadb". Thank you for reading! I believe you all have a certain understanding of the "single table query method of Mariadb". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.