当前位置：网站首页>（2） Using MySQL

Through this query, we can summarize of mysql A little bit of knowledge
Queries are usually made by One SQL Statement and a semicolon form .（ There are some exceptions that may omit semicolons .QUIT As mentioned earlier ）
When you send a query ,mysql Send it to the server for execution and display the results , Then print another mysql> Prompt to indicate that it is ready for another query .
mysql In tabular form （ Row and column ） Show query output . The first row contains the label of the column . The next line is the query result . Usually , The column label is the name of the column you get from the database table . If you are retrieving expressions instead of table column values （ As shown in the example just shown ）,mysql Mark the column with the expression itself .
mysql Displays how many rows were returned and how long the query took to execute , Get a general understanding of server performance . These values are imprecise , Because they represent wall clock time （ No CPU Or machine time ）, And because they are affected by factors such as server load and network latency .

Be careful ：

Keywords can be typed in any case .
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
These three sentences are the same ！

Let's take another example ： mysql Used as a simple calculator

2. Cancel the order

If you decide not to execute the query you are entering , Type the following command to cancel it \c：

After canceling the command , You can then enter other commands .

3、 ... and 、 Create and use databases

1. use SHOW Statement to find out which databases currently exist on the server

mysql> SHOW DATABASES;

2. Access the existing database

mysql> USE mysql
Database changed

Be careful ：use Statements can be without semicolons 【;】

use The statement must be given on one line

3. Create a database

mysql> CREATE DATABASE happy;

！！ The name of the database is case sensitive

4. Using a database

mysql> USE happy
Database changed

Four 、 Create table

1. view the database

mysql> SHOW TABLES;
Empty set (0.00 sec)

You can see that the database is empty at this time .

2. CREATE TABLE Statement to specify the layout of the table

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

This is a pet surface , Including the name of the animal 、 owner 、 species 、 Gender 、 Birth and death .

3.SHOW TABLES Displays the created table

mysql> SHOW TABLES;

4.DESCRIBE Look at the table

mysql> DESCRIBE pet;

A little summary of the summer *~*

The picture below shows Create and select databases and create tables 、 See the table The whole process

5、 ... and 、 Load data into a table

Method 1 ： Text file loading `pet.txt` To `pet` In the table

① First write the data in the table into the text file pet.txt in ;

② Load the text file pet.txt To pet In the table

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

Method 2 ： Use INSERT Statement adds one message at a time

If ： Xiao Yang got one named “ Oreo ” Cat , You can enter the following commands in the format in the table

mysql> INSERT INTO pet VALUES ('aoliao','xiaoyang','cat','f','2022-07-11',NULL);

6、 ... and 、 Retrieve information from a table （ The increase of the table 、 Delete 、 Change 、 check ）

1. Use SELECT Statement to extract information from a table

General form ：

SELECTwhat_to_select
FROMwhich_table
WHERE conditions_to_satisfy;
what_to_select It means what you want to see . This can be a list of columns , also * Can be said “ All columns ”.”
which_table Represents the table from which to retrieve data . The WHERE Clauses are optional .
If there is , conditions_to_satisfy Then the specified row must meet one or more conditions to be eligible for retrieval .

2. Select all data

SELECT Retrieve everything from the table The simplest form of ：

mysql> SELECT * FROM pet;
* Represents the abbreviation of all columns

3. Modify the error information in the table

mysql> UPDATE pet SET birth = '2022-06-06' WHERE name = 'xuewa';

You can see the name is xuewa The birthday of the cat was changed to 2022-06-06.

4. Choose a specific line

1. Choice in 2002 Animals born during or after the year

mysql> SELECT * FROM pet WHERE birth >= '2002-01-01';

2. Use AND Logical operators and OR Operator

AND also OR It can be mixed , Even though AND Priority over OR. If you use both operators , It is best to use parentheses to clearly indicate how conditions should be grouped

mysql> SELECT * FROM pet WHERE species = 'dog' OR species = 'bird';

5. Select a specific column

Don't want to see the whole row in the table , Just name the columns you are interested in , Separate with commas

mysql> SELECT name, birth FROM pet;

Only check the name and birthday columns .

1.DISTINCT keyword

Minimize output , You only need to retrieve each unique output record once by adding keywords DISTINCT.

mysql> SELECT DISTINCT owner FROM pet;

2. Use `WHERE` Clause combines row selection with column selection

mysql> SELECT name, species, birth FROM pet
WHERE species = 'dog' OR species = 'cat';

6. Sort rows

1. Use `ORDER BY` Clause to sort results

mysql> SELECT name, birth FROM pet ORDER BY birth;

2. DESC keyword

The default sort order is ascending , If descending order is required , Need to use DESC keyword

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

DESC Keywords only apply to the column names immediately preceding them ( ) birth; It does not affect species Column sort order .

7、 ... and 、 Date calculation

MySQL Several functions are provided , You can use them to perform date calculations

1. Calculate the age of each pet , Use TIMESTAMPDIFF() function . Its parameter is the unit you want to express the result , And the two dates of the difference .

mysql> SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
FROM pet;
TIMESTAMPDIFF（） Function analysis
The first parameter ： Unit of result , The unit here is year;
second 、 Three parameters ： Take the difference between the two dates 【eg：birth;CURDATE（）】（ In years ）DATE
CURDATE Represents the current date .

8、 ... and 、 Count line

1. Use COUNT（*） Count lines

mysql> SELECT COUNT(*) FROM pet;

2.COUNT(*) And GROUP BY Combined use of

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

It shows the number of animals per species

Nine 、 Use multiple tables

The pet The table will record your pet . If you want to record other information about them , For example, events in their lives , For example, when going to the vet or giving birth , You need another form .
This table includes ：
Pet name , So that you know which animal each event is related to .
A date , So that you know when the event happened .
Fields describing events .
An event type field , If you want to be able to classify events .

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
type VARCHAR(15), remark VARCHAR(255));

Build table

mysql> INSERT INTO event VALUES('aoliao','2022-07-11','shouyi','jiaoduanle');
mysql> INSERT INTO event VALUES('xuewa','2022-07-11','chanzai','4cat,3f,1m');

mysql> INSERT INTO event VALUES('111111','2010-01-01','chanzai','5dog,2f,3m');

mysql> INSERT INTO event VALUES('222222','2020-02-02','chanzai','3bird,3f');

Suppose you want to know about each pet Birth age . We saw earlier how to calculate age from two dates . The mother's birth date is event In the table , But calculate her age on that date , You need her date of birth , The date is stored in pet In the table . This means querying Two tables are required ：

mysql>SELECT pet.name,
TIMESTAMPDIFF(YEAR,birth,date)AS age,
remark
FROM pet INNERJOINevent
ON pet.name=event.name
WHEREevent.type='litter';

Considerations for using multiple table queries

use FROM Clause joins two tables , Because the query needs to extract information from these two tables .
When combined （ Connect ） Information from multiple tables , You need to specify how records in one table match records in another table . This is easy. , Because they all have one name Column . This query uses ON Clause according to name Value matches the records in both tables .
This query uses an INNER JOIN To combine tables . If and only if both tables satisfy the INNER JOIN When the conditions , To allow rows in any table to appear in the result .
ON In this example , ON Clause assignment In the table name Column pet Must be consistent with name Column matching event. If the name appears in one table and does not appear in another table , Then the row will not appear in the result , because ON Condition in Clause failed .
because name Columns appear in two tables , So when referencing this column , You must specify which table you are referring to . This is done by adding the table name to the column name .