Mysql 中文版

“This is the 11th day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021.”

First, database operation

1. Display the database

mysql> SHOW DATABASES;
Copy the code

CREATE DATABASE CREATE DATABASE name CHARSET=’ encoder ‘

mysql> CREATE DATABASE create_test CHARSET = 'utf8';
Copy the code

3. Use a database

mysql> USE create_test;
Copy the code

Use SELECT DATABASE() to view the current DATABASE.

mysql> SELECT DATABASE();
+-------------+
| DATABASE()  |
+-------------+
| create_test |
+-------------+
Copy the code

5. Delete the database

mysql> DROP DATABASE create_test;
Copy the code

Table operation

Create a table

Code format:

CREATE TABLE  [IF NOT EXISTS] ` table name ` (` field name ` column type [properties] [index] [note], ` field name ` column type [properties] [index] [note],... 'Field name' column type [Attribute] [Index] [comment]) [table type] [Character set setting] [comment]Copy the code

Use the following statement to create the one_PIECE table in the example.

mysql> CREATE TABLE one_piece
    -> (
    ->     id CHAR(10) NOT NULL COMMENT 'Bandit ID'.->     pirates CHAR(10) NOT NULL COMMENT 'Name of the Pirates'.->     name CHAR(10) NOT NULL COMMENT 'Pirate Name'.->     age INT(11) NOT NULL COMMENT 'Age of the thief'.->     post VARCHAR(10) NULL COMMENT 'Pirate Corps position'
    -> );
Copy the code

Note: When creating a table, the specified table name must not exist, otherwise an error will occur.

Update the table

Add a bounty column to the one_piece table you just created.

mysql> ALTER TABLE one_piece
    -> ADD bounty INT(15);
Copy the code

Delete the bounty column.

mysql> ALTER TABLE one_piece
    -> DROP COLUMN bounty;
Copy the code

View table structure

mysql> DESC one_piece;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | NO   |     | NULL    |       |
| pirates | char(10)    | NO   |     | NULL    |       |
| name    | char(10)    | NO   |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| post    | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
Copy the code

View table details

\G cannot be followed by “;” .

mysql> SHOW TABLE STATUS LIKE 'one_piece' \G
*************************** 1. row ***************************
           Name: one_piece
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021- 11- 08 15:20:13
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
Copy the code

Rename table

Two methods:

The ALTER TABLE TABLE name RENAME/TO | AS the new name of the TABLE;
RENAME TABLE name TO new TABLE name;

Rename the Products table to new_Products using method 1 and change it back using method 2.

Method - a
mysql> ALTER TABLE one_piece RENAME TO new_one_piece;
- the method 2
mysql> RENAME TABLE new_one_piece TO one_piece;
Copy the code

Delete table

DROP TABLE TABLE name

mysql> DROP TABLE one_piece;
Copy the code

Note: When this table is associated with another table,MysqlPrevents the table from being deleted.

3. The query

Query multiple columns

The name and age columns are also printed.

mysql> SELECT name, age
    -> FROM one_piece;
Copy the code

Retrieve unique value

Use the DISTINCT keyword to query the unique value of the age field.

mysql> SELECT DISTINCT age
    -> FROM one_piece;
Copy the code

Limit the output

Use the LIMIT keyword in Mysql to LIMIT output data. LIMIT has two common uses:

SELECT * FROM table  LIMIT [offset].rows    -- LIMIT is used alone
SELECT * FROM table  LIMIT rows OFFSET [offset]    -- Used with OFFSET
Copy the code

Offset: indicates the index of the starting row. 0 means starting at line 1 (including line 1), and so on.

Rows: Indicates the number of rows displayed in the data.

Example:

SELECT * FROM one_piece LIMIT 5;    Retrieve the first 5 items of data
- the equivalent of
SELECT * from one_piece LIMIT 0.5;    -- Retrieves five pieces of data starting at line 0
- the equivalent of
SELECT * FROM one_piece LIMIT 5 OFFSET 0;    Retrieve 5 items of data starting at line 0. Note that LIMIT 5 refers to quantity
Copy the code

Note: If there is insufficient data in the table, that is, if LIMIT is set too high, only the last row will be retrieved.

annotation

Three ways to comment

-- Single-line comment# single-line comment/* Multi-line comments */
Copy the code

B: I’d like to ORDER BY

A single order

Use the ORDER BY clause. The ORDER BY clause takes the name of one or more columns and sorts the output accordingly (ascending BY default).

mysql> SELECT name, age
    -> FROM one_piece
    -> ORDER BY age;
Copy the code

Note: specify aORDER BYClause, you should make sure that it isSELECTThe last clause in a sentence.

Multi-column sorting

mysql> SELECT A, B
    -> FROM test
    -> ORDER BY A, B;
Copy the code

When sorting by multiple columns, sort by B only if multiple rows have the same A value. If all the values in column A are unique, then B will not be sorted.

Specify sort direction

ORDER defaults to ascending ORDER (A to Z). Specify DESC keyword to sort in descending order (Z to A).

mysql> SELECT age
    -> FROM one_piece
    -> ORDER BY age DESC;
Copy the code

When multiple columns specify sorting directions, separate them with commas.

mysql> SELECT name, age
    -> FROM one_piece
    -> ORDER BY name DESC, age;
Copy the code

WHERE filter data

The WHERE clause operator

The operator	instructions	The operator	instructions
=	Is equal to the	>	Is greater than
The < > and! =	Is not equal to	> =	Greater than or equal to
<	Less than	! >	No greater than
< =	Less than or equal to	BETWEEN	Between two values (including boundaries)
! <	Not less than	IS NULL	Is a NULL value

Range check

Use the WHERE keyword AND BETWEEN AND for range checking (close before AND close after).

mysql> SELECT age
    -> FROM one_piece
    -> WHERE A BETWEEN 5 AND 10;
Copy the code

Select * from age where age >=5 and <= 10;

Null check

Use the WHERE keyword and IS NULL for range checking. If there is no NULL value, no data is returned.

mysql> SELECT name
    -> FROM one_piece
    -> WHERE name IS NULL;
Copy the code

WHERE combined filtering

Add additional conditions to the WHERE clause using the AND AND OR operators. The priority of AND is higher than that of OR. (), AND, OR. In the process of use, pay attention to the impact of each priority.

mysql> SELECT name, age
    -> FROM one_piece
    -> WHERE(name = 'sauron' OR name = 'luffy')
    -> AND age > = 18;
Copy the code

The IN operator

The IN operator is used to specify a range of conditions IN which each condition can be matched. (Has the same function as OR, but slower than IN)

mysql> SELECT name, age
    -> FROM one_piece
    -> WHERE name IN ('sauron'.'luffy')
Copy the code

The NOT operator

The NOT operator in the WHERE clause has one and only one function: to negate any conditions that follow.

mysql> SELECT name
    -> FROM one_piece
    -> WHERE name NOT IN ('sauron'.'luffy')
Copy the code

7. Wildcard filtering

Wildcard searches can only be used for text fields (strings); wildcard searches cannot be used for fields of non-text data types. Before using wildcard filtering, know about LIKE. The LIKE operator is used to search for a specified pattern or value in a column in the WHERE clause.

% wildcards

% indicates any number of occurrences of any character. For example, to find all names that start with a road.

mysql> SELECT name, age
    -> FROM one_piece
    -> WHERE name LIKE 'road %';
Copy the code

_ a wildcard

The wildcard _ is used to match any character like %, but it matches only one character, not multiple characters.

mysql> SELECT name, age
    -> FROM one_piece
    -> WHERE name LIKE '乌_普';
Copy the code

This is what I want to share today. Search Python New Horizons on wechat, bringing you more useful knowledge every day.

Mysql 中 文 版