MySQL Common query

MySQL common query one, experiment introduction

In this section of the experiment we will learn and practice a number of common ways to find records in a database through an example of a reseller database.

Ii. contents of the experiment

In the following example, a data table is used shop to store the price of each item (item number) of a merchant (dealer). If each merchant has a fixed price for each item, then the keywords in the table are the same 物品 商人 .

First you need to start the server and connect to MySQL:

Then you create a database and TEST switch to that database:

Then create a sample table with the following code:

Mysql> CREATE TABLE Shop (article INT (4) UNSIGNED ZerofillDEFAULT' 0000 ' notNULL, Dealer CHAR (20)DEFAULT' NotNULL, Price DOUBLE (16,2)DEFAULT 0.00 ' not null, PRIMARY KEY (article, dealer)); # Insert data mysql> INSERT INTO shop VALUES---1,3.45), (1, ' B ', 3.99), (2, ' A ', 10.99), (3, ' B ', 1.45), (3, ' C ', 1.69", (3, ' D ', 1.25), (4, ' D ', 19.95);      

Execute the following statement to view the contents of the table:

mysql> SELECT * FROM shop;

1. Find the maximum value of a column

Use MAX() the function to calculate article the maximum value of the item number:

SELECT MAX(article) as article FROM shop;

2. Query the row where the maximum value of a column is located

# 显示price这一列最大值对应的行mysql> SELECT article, dealer, price    -> FROM   shop    -> WHERE  price=(SELECT MAX(price) FROM shop);

Another method is to sort the prices of all rows in descending order, and then use the MySQL-specific limit clause to display one of the lines (here is the first row to show the highest price):

mysql> SELECT article, dealer, price    -> FROM shop    -> ORDER BY price DESC    -> LIMIT 1;

You can enter whether the above statement query results are the same.

Note: If more than one item is priced at 19.95 (the most expensive item), the limit display will show only one

3. Display the maximum number of columns by group

Find the highest bid price for each item by using the following code

AS price    -> FROM shop    -> GROUP BY article;

4. Using User variables

Find the most expensive or lowest items by using User variables:

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;mysql> SELECT * FROM shop WHERE [email protected]_price OR [email protected]_price;

Think of other ways to find the item that corresponds to the maximum and minimum value of the price.

5. Using foreign keys

In MySQL, the InnoDB table supports checking for foreign key constraints.

A FOREIGN key constraint is not required when connecting two tables. For a InnoDB table other than a table, you can use a REFERENCES tbl_name(col_name) statement definition to set its column to a foreign key, but the statement does not actually work, just as a comment to remind you that the column you are defining now points to another table. The following points need to be noted when executing the statement:

• MySQL does not take any action to check col_name if the column exists tbl_name in the table (it does not even check tbl_name whether the table actually exists).
• MySQL does not perform operations on the table tbl_name , such as changing the behavior of the row you are defining, causing the on delete or on UPDATE statement to not take effect on that line (meaning that if you write an on delete or an ON UPDATE clause in the REFERENCES clause, will be ignored).
• The syntax allows you to create a column, but does not create any indexes or keywords.

You can use the above statement to create two tables and define a foreign key to concatenate columns from two tables:

mysql> CREATE TABLE Person (-ID SMALLINT UNSIGNED not NULL auto_increment,, Name CHAR () Not NULL,PRIMARY KEY (ID),;mysql> CREATE TABLE shirt (-ID SMALLINT UNSIGNED not NULL auto_increment,-Style ENUM (' T-shirt ',' Polo ',' Dress ') not NULL,-Color ENUM (' Red ',' Blue ',' Orange ',' White ',' Black ') not NULL,-Owner SMALLINT UNSIGNED not NULL REFERENCES person (ID),PRIMARY KEY (ID));mysql> INSERT into person VALUES (NULL,' Antonio Paz ');mysql> SELECT @last: = last_insert_id ();mysql> INSERT into Shirt VALUES (NULL,' Polo ',' Blue ', @last),-> (NULL,  ' dress ',  ' white ', @ Last), -> (NULL,  ' t-shirt ',  Blue ', @last);mysql> INSERT into person VALUES (NULL,  ' Lilliana Angelovska ');mysql> SELECT @last: = last_insert_id ();mysql> INSERT into Shirt VALUES--(NULL,  ' dress ', -> (NULL,  ' polo ', Span class= "hljs-string" > ' Red ', @last), -> (NULL,  ' dress ', -> (NULL,  T-shirt ',  ' White ', @last);          

Try querying person and both shirt of the contents

By creating the table as described above and using the statement SHOW CREATE TABLE or DESCRIBE viewing the output, you will find that the REFERENCES clause does not appear in the result:

SHOW CREATE TABLE shirt\G

Look at the results of your output ~

6. Search using two keywords

Make OR the most of the connection two keywords ( AND also the same reason)

# 这里面的test_table可以是任何一个表，关键词也是类似SELECT field1_index, field2_index FROM test_tableWHERE field1_index = ‘1‘ OR  field2_index = ‘1‘

You can also use the UNION keywords in the two tables to search

SELECT field1_index, field2_index    FROM test_table WHERE field1_index = ‘1‘    UNIONSELECT field1_index, field2_index FROM test_table WHERE field2_index = ‘1‘;

7. Calculate the number of visits per month

The following uses BIT_COUNT the function to calculate the number of days a user visits a webpage each month:

CREATETABLE T1 (YearYear (4),MonthINT (2)UNSIGNED Zerofill,day int (2) unsigned Zerofill); insert into T1 VALUES (2000,1,1), (2000,< Span class= "Hljs-number" >1,20), (2000, 1,30), (2000, 2,2), (2000, 2,23), (2000, 2,23);          

In the tables created above, you can use the following statement to query the number of visits per month by the date the user visited the page:

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;

8. Use AUTO_INCREMENTStatement

Adding statements when defining column properties allows AUTO_INCREMENT each record to be uniquely identified:

CREATEtable Animals (id mediumint NOT Span class= "hljs-literal" >null auto_increment, name CHAR ( Span class= "Hljs-number" >30) not null, PRIMARY key (id)); insert into Animals (name) values ( ' dog '), ( ' cat '), ( ' Penguin '), ( ' lax '), ( ' Whale '), ( ' ostrich '); select * from animals;        

Looking at the results of the above statement, you will find that id it is automatically generated.

To make the AUTO_INCREMENT statement generate a starting value other than 1, you can CREATE TABLE ALTER TABLE set the value by or, as follows:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

Learn more about AUTO_INCREMENT sentences:

• How to specify properties for a column AUTO_INCREMENT : CREATE table syntax and ALTER TABLE syntax.
• Find the rows that contain the most recent AUTO_INCREMENT values: comparison functions and operators.

Third, the operation

Try to build a library database, let it play, but must contain basic information: book information, borrower's information, publishing house information. and use the index knowledge learned in this lesson to index the database, your thoughts and steps into the experimental report, in the experimental class environment to establish the system and index, remember Oh.

MySQL Common query