MySQL Guide to SQL statement basics

All my articles are compiled in this article, which will be updated one after another: Knowledge is long, but the path of traveler is endless (my programming path)

Structured Query Language (SQL)

DDL data definition language management library, table DML data operation language add, delete, change, check DCL data control language data control, permission access, etcCopy the code

Preparation activities: Create libraries and tables

CREATE DATABASE datatype;
USE datatype;

CREATE TABLE type_number(
type CHAR(12), 
byte TINYINT UNSIGNED,
range_singed VARCHAR(20),
range_unsinged VARCHAR(20),
info VARCHAR(40)
);
Copy the code

Current Status:

mysql> SHOW DATABASES;                                                      
+--------------------+                                                      
| Database           |                                                      
+--------------------+                                                      
| datatype           |                                                      
| information_schema |                                                      
| mycode             |                                                      
| mysql              |                                                      
| performance_schema |                                                      
| seckill            |                                                      
+--------------------+                   

mysql> USE datatype;
Database changed

mysql> SHOW TABLES;
+--------------------+
| Tables_in_datatype |
+--------------------+
| type_number        |
+--------------------+

mysql> DESC type_number;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| type           | char(12)            | YES  |     | NULL    |       |
| byte           | tinyint(3) unsigned | YES  |     | NULL    |       |
| range_singed   | varchar(20)         | YES  |     | NULL    |       |
| range_unsinged | varchar(20)         | YES  |     | NULL    |       |
| info           | varchar(40)         | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
Copy the code

DML database record operation`LEVEL 1`

LEVEL 1: LEVEL 1

1. Record insertion operation

INSERT INTO < table name > (attribute,...) VALUES (VALUES,...). . ;

| - INSERT a data INSERT INTO < table name > (properties, etc.) VALUES (VALUES,...). ; INSERT INTO type_number(type,byte,range_singed,range_unsinged,info) 
VALUES
('TINYINT', 1,'- 2 ⁷ ~ 2 ⁷ - 1'.'0 ~ 2 ⁸ - 1'.'Small integer'); | - queries all SELECT * FROM < table name >. mysql> SELECT * FROM type_number; +---------+------+----------------+----------------+--------------+ |type| byte | range_singed | range_unsinged | info | +---------+------+----------------+----------------+--------------+ | TINYINT | 1 | | 0-2 ⁷ ~ 2 ⁷ - 1 ~ 2 | | small integer ⁸ - 1 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | - INSERT INTO type_number()type,byte,range_singed,range_unsinged,info) 
VALUES
('TINYINT', 1,'- 2 ⁷ ~ 2 ⁷ - 1'.'0 ~ 2 ⁸ - 1'.'Small integer'),
('SMALLINT', 2,'- 2 creates ⁶ ~ 2 creates ⁶ - 1'.'0 ~ 2 creates ⁶ - 1'.'Small integer'),
('MEDIUMINT', 3,'- 2 squared ⁴ ~ 2 squared ⁴ - 1'.'0 ~ 2 squared ⁴ - 1'.'Medium integer'),
('INT', 4,'- 2 after squared ~ 2 after squared - 1'.'0 ~ 2 after squared - 1'.'Standard integer'),
('BIGINT'Eight,'- 2 ⁶ ⁴ ~ 2 ⁶ ⁴ - 1'.'0 ~ 2 ⁶ ⁴ - 1'.'Big integer');

mysql> SELECT * FROM type_number;
+-----------+------+----------------------+----------------+--------------+
| type| byte | range_singed | range_unsinged | info | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | TINYINT | 1 | | 0-2 ⁷ ~ 2 ⁷ - 1 ~ 2 | small ⁸ - 1 integer | | | TINYINT | 1-2 ⁷ | 0 ~ 2 ⁷ - 1 ~ 2 | small ⁸ - 1 integer | | SMALLINT | | - 2 creates ⁶ ~ 2 creates ⁶ - 1 | 0 ~ 2 creates ⁶ - 1 small integer | | | MEDIUMINT | 3 | - 2 squared ⁴ ~ 2 squared ⁴ - 1 | 0 ~ 2 squared | medium ⁴ - 1 integer | | INT | | 4-2 after squared ~ 2 after | 0 squared - 1 ~ 2 after squared - 1 standard integer | | | BIGINT 8 | | - 2 ⁶ ⁴ ~ 2 ⁶ ⁴ - 1 | 0 ~ 2 ⁶ ⁴ - 1 Big integer | | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

2. Record update operation

UPDATE < table name > SET attribute = value,... WHERE conditions;

UPDATE type_number 
SET 
info='miniature integer'
WHERE type = 'TINYINT';

mysql> SELECT * FROM type_number;
+-----------+------+----------------------+----------------+--------------+
| type| byte | range_singed | range_unsinged | info | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | TINYINT | 1 | | 0-2 ⁷ ~ 2 ⁷ - 1 ~ 2 | miniature ⁸ - 1 integer | | | TINYINT | 1-2 ⁷ | 0 ~ 2 ⁷ - 1 ~ 2 | miniature ⁸ - 1 integer | | SMALLINT | | - 2 creates ⁶ ~ 2 creates ⁶ - 1 | 0 ~ 2 creates ⁶ - 1 small integer | | | MEDIUMINT | 3 | - 2 squared ⁴ ~ 2 squared ⁴ - 1 | 0 ~ 2 squared | medium ⁴ - 1 integer | | INT | | 4-2 after squared ~ 2 after | 0 squared - 1 ~ 2 after squared - 1 standard integer | | | BIGINT 8 | | - 2 ⁶ ⁴ ~ 2 ⁶ ⁴ - 1 | 0 ~ 2 ⁶ ⁴ - 1 Big integer | | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

3. Delete records

DELETE FROM < table name > WHERE condition;

| - DELETE DELETE FROM type_number WHEREtype = 'TINYINT';

mysql> SELECT * FROM type_number;
+-----------+------+----------------------+----------------+--------------+
| type| byte | range_singed | range_unsinged | info | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | SMALLINT | | - 2 creates ⁶ ~ 2 creates ⁶ - 1 | 0 ~ 2 creates | ⁶ - 1 Small integer | | MEDIUMINT | 3 | - 2 squared ⁴ ~ 2 squared ⁴ - 1 | 0 ~ 2 squared | medium ⁴ - 1 integer | | INT | | 4-2 after squared ~ 2 after | 0 squared - 1 ~ 2 after squared - 1 standard integer | | | BIGINT | | 8 - 2 ⁶ ⁴ ~ 2 ⁶ ⁴ - 1 | 0 ~ 2 ⁶ ⁴ - 1 big integer | | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

4. Record query operations

The SELECT attribute,... FROM < table name > WHERE condition;

mysql> 
SELECT 
type,range_unsinged 
FROM type_number 
WHERE byte>=4;
+--------+----------------+
| type| range_unsinged | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | INT | 0 ~ 2 after squared - 1 | | BIGINT | 0 ~ 2 ⁶ ⁴ - 1 | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +Copy the code

Ii. Picture list PIC`(LEVER 2)`

MySQL > select * from ‘MySQL’; select * from ‘MySQL’; select * from ‘MySQL’; Let’s do a bunch of string operations

1. Build a predicate sentence

CREATE TABLE pic( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pic_path VARCHAR(120) NOT NULL, pic_length INT UNSIGNED DEFAULT 0, pic_mime TINYINT UNSIGNED, pic_width SMALLINT UNSIGNED, pic_height SMALLINT UNSIGNED ); | - id primary key Since the growth | - pic_path said name, indefinite length, give a VARCHAR 120, almost enough. | - image file size is not very big, give a INT enough, Give a default value of 0 | - pic_mime 0 means image/jpeg image/PNG 1 said to the smallest | - pic_width and pic_height wouldn't be very big, unsigned SMALLINT enoughCopy the code

2. Query operations`AS`The role of

| - query in the record of 1200 pixels tall, use the AS to temporarily change the attribute name query output (a record) will not change the actual mysql > SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px'FROM pic WHERE pic_height>1200; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path width/px high/px | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 30000X20000.jpg | 30000 | 20000 | | 3000X2000.jpg | 3000 | 2000 | | ecNKedygCmSjTWWF.jpg | 700 | 1352 | | gtQiXnRfkvvTLinw.jpg | 2880 | 2025 | | HXqqASHJETSlvpnc.jpg | 3600 | 2400 | | ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 | | screen.png | 1080 | 1920 | | XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 | +----------------------+--------+--------+Copy the code

3. Query is an attribute that can participate in the operation

| - CONCAT function used in the connection string Note: \ need to escape the mysql > SELECT CONCAT ('E:\\SpringBootFiles\\imgs\\',pic_path) AS absolute path, pic_width * pic_height AS'Number of pixels'FROM pic WHERE pic_height>1200; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | absolute path | pixel number +----------------------------------------------+-----------------+ | E:\SpringBootFiles\imgs\30000X20000.jpg | 600000000  | | E:\SpringBootFiles\imgs\3000X2000.jpg | 6000000 | | E:\SpringBootFiles\imgs\ecNKedygCmSjTWWF.jpg | 946400 | | E:\SpringBootFiles\imgs\gtQiXnRfkvvTLinw.jpg | 5832000 | | E:\SpringBootFiles\imgs\HXqqASHJETSlvpnc.jpg | 8640000 | | E:\SpringBootFiles\imgs\ndbMXlwKuCpiiVqC.jpg | 3857868 | | E:\SpringBootFiles\imgs\screen.png | 2073600 | | E:\SpringBootFiles\imgs\XQWGrglfjGVuJfzJ.jpg | 2035200 | +----------------------------------------------+-----------------+Copy the code

4.`WHERE`A kaleidoscope of conditions

Condition of 4.1:`with` — `AND` 和 `&&`

All conditions must be met

SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px'FROM pic WHERE pic_height>1200 AND pic_width > 1500; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path width/px high/px | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 30000X20000.jpg | 30000 | 20000 | | 3000X2000.jpg | 3000 | 2000 | | gtQiXnRfkvvTLinw.jpg | 2880 | 2025 | | HXqqASHJETSlvpnc.jpg | 3600 | 2400 | | ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 | +----------------------+--------+--------+ | - AND the effect is equal to the && SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px' 
FROM pic 
WHERE pic_height>1200 && 
pic_width > 1500;
Copy the code

Condition of 4.2:`or` — `OR` 和 `||`

Only one condition is satisfied

SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px'FROM pic WHERE pic_height>1200 OR pic_width > 1500; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path width/px high/px | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 30000X20000.jpg | 30000 | 20000 | | 3000X2000.jpg | 3000 | 2000 | | ecNKedygCmSjTWWF.jpg | 700 | 1352 | | gtQiXnRfkvvTLinw.jpg | 2880 | 2025 | | HXqqASHJETSlvpnc.jpg | 3600 | 2400 | | ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 | | screen.png | 1080 | 1920 | | XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 | +----------------------+--------+--------+ |--- OR Effect is equal to the | | SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px' 
FROM pic 
WHERE pic_height>1200 ||
pic_width > 1500;
Copy the code

Condition of 4.3:`non` — `NOT` 和 `!`

I’m going to invert the condition

SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px'FROM pic WHERE NOT pic_height < 1200; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path width/px high/px | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 30000X20000.jpg | 30000 | 20000 | | 3000X2000.jpg | 3000 | 2000 | | ecNKedygCmSjTWWF.jpg | 700 | 1352 | | gtQiXnRfkvvTLinw.jpg | 2880 | 2025 | | HXqqASHJETSlvpnc.jpg | 3600 | 2400 | | ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 | | screen.png | 1080 | 1920 | | XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 | +----------------------+--------+--------+Copy the code

4.4: Scatter matching`IN(v1,v2,v3,...)`

In line with the v1, v2, v3,… One match

SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px'FROM PIC WHERE pic_height IN (1696,2268); + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path width/px high/px | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 | | XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 | +----------------------+--------+--------+Copy the code

4.5: Interval matching`BETWEEN v1 AND v2`

V1 and v2 match each other

SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px'FROM pic WHERE pic_height BETWEEN 1696 AND 2268; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path width/px high/px | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 3000X2000.jpg | 3000 | 2000 | | gtQiXnRfkvvTLinw.jpg | 2880 | 2025 | | ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 | | screen.png  | 1080 | 1920 | | XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 | +----------------------+--------+--------+Copy the code

4.6: Fuzzy query:`LIKE`

'%' matches any number of characters, and '_' matches any single character

Mysql > SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px' 
FROM pic 
WHERE pic_path LIKE 'androi%'; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path width/px high/px | | +----------------------------------------------+--------+--------+ | android\008525ebc2b7d434070e74c00841a30f.png | 544 | 544 | | android\054d98e2d96dc42d9b2b036126fccf49.png | 544 | 544 | | android\05baf2d03651d1110d7a403f14aee877.png | 544 | 544 | | android\0655e07d6717847489cd222c9c9e0b1d.png | 500 | 500 | | android\079c4cb46c95b2365b5bc5150e7d5213.png | 544 | 544 | | android\07a4dc9b4b207cb420a71cbf941ad45a.png | 544 | 544 | | android\07abb7972a5638b53afa3b5eb98b19c1.png | 500 | 500 | . Mysql > SELECT pic_path AS path, pic_width AS'width/px', 
pic_height AS 'high/px' 
FROM pic 
WHERE pic_path LIKE 'p_em%'; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path width/px high/px | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | poem \ world bloom. JPG | 1148 | 712 | | poem \ I love you, Is volcanic rock's silence. JPG | 690 | 397 | | poem \ branch, you dance is tree. JPG | 500 | 333 | | poem \ sea and deer. JPG | 799 | 499 | | poem \ swim dream, born of the poem. JPG | 800 | 444 | | poem \ coral graveyard. JPG | 1104 | 719 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

4.7: Comparison symbols`= != < > <= >=`

Elementary school students all know, no nonsense, look at the picture is less than 10Kb

Mysql > SELECT pic_path AS path, pic_length AS'size/byte'FROM pic WHERE pic_length < 10*1024; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path size/byte | +----------------------------------------------+-------------+ | 30X20.jpg | 10158 | | android\613f2b8f0eaa8f63bedce9781527c9ab.png | 4001 | | android\94b5c41232f9761403890c09c2b1aae3.png | 4001 | | Android \ d3fd676f224f0734beb48d0c0d2f4e66 PNG | 4001 | | udp send and receive messages _ console. PNG | 9184 | +----------------------------------------------+-------------+Copy the code

5. `GROUP BY`Grouping query

It’s sorted and then listed

|--- GROUP BY
SELECT 
pic_mime AS "Type",
avg(pic_length) AS 'Average size /byte' ,
count(pic_length) AS 'Total quantity/unit' ,
min(pic_length) AS 'Minimum /byte' ,
max(pic_length) AS 'Max /byte' ,
sum(pic_length) AS 'combined/byte'FROM pic GROUP BY pic_mime; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | type average size/byte | total quantity/a | the minimum/maximum byte | / byte/byte | | combined + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 0 | | 229 | 141518.8734 4001 | 829338 | 32407822 | | 1 | 2133272.8000 | | 10158 | | 127996368 | 116342886 60 +--------+-------------------+---------------+----------------+----------------+-------------+Copy the code

6. Result set screening:`HAVING`

Now query for images with an aspect ratio between 1.1 and 1.3

| - if the WHERE to the SELECT pic_path AS path query feeling a little grace, pic_width/pic_height AS'Aspect ratio'FROM PIC WHERE pic_width/pic_height > 1.1 && pic_width/pic_height<1.3; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | | | path width to height ratio + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | dQXbnTRjUdNxhiyl. 1.2308 JPG | | | JsXHWmKqOlziKmeA. 1.2600 JPG | | | logo, android, android native drawing for you to understand the View of sport. 1.2884 PNG | | | lo day. 1.1990 JPG | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | - AS equivalent to take the column variable, Select * from 'WHERE' WHERE 'WHERE' SELECT pic_path AS path, pic_width/pic_height AS ratio FROM PIC HAVING ratio > 1.1&& ratio <1.3; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | path thewire | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | dQXbnTRjUdNxhiyl. 1.2308 JPG | | | JsXHWmKqOlziKmeA. 1.2600 JPG | | | logo, android, android native drawing for you to understand the View of sport. 1.2884 PNG | | | lo day. 1.1990 JPG | | +------------------------------------------------------------------+--------+Copy the code

7. Ranking of results:`ORDER BY`

Put them in order by ratio

SELECT pic_path AS path, pic_width/pic_height AS ratio FROM PIC HAVING ratio > 1.1&& ratio <1.3; The ORDER BY thewire DESC + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | path thewire | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | dQXbnTRjUdNxhiyl. 1.2308 JPG | | | JsXHWmKqOlziKmeA. 1.2600 JPG | | | logo, android, android native drawing for you to understand the View of sport. 1.2884 PNG | | | lo day. 1.1990 JPG | | +------------------------------------------------------------------+--------+Copy the code

8. Control the number of items:`LIMIT`

| - offset one, SELECT pic_path AS; Pic_width /pic_height AS ratio FROM PIC HAVING ratio > 1.1 && ratio <1.3 ORDER BY ratio DESC LIMIT 1,2; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | path thewire | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | JsXHWmKqOlziKmeA. 1.2600 JPG | | | DQXbnTRjUdNxhiyl. 1.2308 JPG | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- +Copy the code

Third, sub-query`(LEVER 3)`

1. Query images with larger than average size —`WHERE`

| - appear in other SQL statements in the SELECT statement | - sub queries must be within () | - add and delete all can undertake the subquery, return: scalar, row, column or a subqueryCopy the code

| - 1-1: find out picture average size SELECT ROUND (AVG (pic_length), 2) the AS'Average size'FROM pic; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + average size | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 555031.80 + -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 rowin set(0.00 SEC) | - 1-2: in the WHERE screening SELECT pic_path AS path, pic_length AS'size/byte'FROM PIC WHERE pic_length > 555031.80; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | path size/byte | +----------------------------------------------+-------------+ | 30000X20000.jpg | 116342886 | | 3000X2000.jpg | 3404969  | | android\12284e5f7197d8be737fa967c8b00fbe.png | 829338 | | android\594665add495ac9da8b6bbee1c63f1b8.png | 598974 | |  android\7cc97458727e23f7d161b8a1a7c6b453.png | 559420 | | android\cbb1524f5ab4266698f3a6fc2992ccae.png | 829338 | | android\d52539b1b508a594d1f2865037ff50c5.png | 598974 | | android\f07ddfe5a103e4a024e14e2569f1d70e.png | 829338 | | android\f0d1e7713d5557a8f9c74c9904843e09.png | 559420 | | Bg. PNG | 688207 | | gtQiXnRfkvvTLinw. JPG | 771187 | | poem \ coral graveyard. JPG | 984472 | | XoazFNMQROveEPQn. JPG | 795364 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | - a statement is wrapped in the WHERE clause for the SELECT pic_path the AS path, pic_length AS'size/byte' 
FROM pic 
WHERE pic_length > (
    SELECT 
    ROUND(AVG(pic_length),2) 
    FROM pic
);
Copy the code

2. Check for the latest inserted images of each type`WHERE`

SELECT pic_path AS path, PIC_MIME AS type FROM PIC WHERE id IN (SELECT Max (id) FROM PIC GROUP BY pic_MIME); + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | path type | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | lo day. JPG | 1 | | virtual machine stack. PNG | | 0 +------------------+--------+Copy the code

3.FROM subquery —`FROM`

SELECT ID, pic_path AS path, pic_length AS'size/byte'FROM pic WHERE id>=10&&id<=15 ORDER BY pic_length DESC; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | id path size/byte | +----+----------------------------------------------+-------------+ | 15 | android\0f3bf63796ac370a08ee97b056b0587b.png | 178849 | | 14 | android\0951ef0be68f0c498ca34ffcd7fc7faa.png |      175842 |
| 11 | android\079c4cb46c95b2365b5bc5150e7d5213.png |       86996 |
| 10 | android\0655e07d6717847489cd222c9c9e0b1d.png | 53764 | | 12 | android\07a4dc9b4b207cb420a71cbf941ad45a.png | 46270 | | 13 | android\07abb7972a5638b53afa3b5eb98b19c1.png | 43360 | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | - the query results as a list, SELECT ID, path FROM (SELECT ID, pic_path AS path, pic_length AS'size/byte'FROM PIC WHERE ID >=10&& ID <=15 ORDER BY pic_length DESC) AS result WHERE size /byte < 59999; + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | id path + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + | 10 | android\0655e07d6717847489cd222c9c9e0b1d.png |
| 12 | android\07a4dc9b4b207cb420a71cbf941ad45a.png |
| 13 | android\07abb7972a5638b53afa3b5eb98b19c1.png |
+----+----------------------------------------------+
Copy the code

Four, connection query

0. Create an associated table

Select * from mime_type; select * from mime_type

| - build a TABLE CREATE TABLE mime_type (mime_id SMALLINT UNSIGNED PRIMARY KEY, mime_info CHAR (24)); | - INSERT data INSERT INTO mime_type (mime_id, mime_info) VALUES (0,'image/png'),
(1,'image/jpeg'),
(2,'image/svg+xml'),
(3,'video/mp4'),
(4,'text/plain'); | - effect mysql > select * from mime_type; +---------+---------------+ | mime_id | mime_info | +---------+---------------+ | 0 | image/png | | 1 | image/jpeg | | 2 | image/SVG + XML | | 3 | video/mp4 | | | 4 text/plain | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | - to illustrate this, PIC table to add a test data: INSERT INTO PIC (pic_path,pic_length, pic_MIME,pic_width,pic_height) VALUES(pic_mime,pic_width,pic_height)'test.jpg', 100,8,300,200);Copy the code

1. Internal connection query`INNER JOIN`

SELECT * FROM table 1 INNER JOIN table 2 ON condition WHERE condition

SELECT id, pic_path AS path, mime_type. Mime_info AS type, pic_length FROM pic INNER JOIN mime_type ON pic.pic_mime = mime_type.mime_id ORDER BY id DESC LIMIT 4; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | | | | id path type pic_length | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | | 289 virtual machine stack. PNG | image/PNG | 63723 | | 288 | united return. PNG | image/PNG 287 | | 29485 | | lo day. JPG | image/jpeg | 42117 | | 286 | tag. PNG | image/PNG | 29288 | +-----+------------------+------------+------------+Copy the code

2. Left link query:`LEFT JOIN`

Maintain the integrity of the left table, right table can not be found NULL

SELECT id, pic_path AS path, mime_type. Mime_info AS type, pic_length FROM pic LEFT JOIN mime_type ON pic.pic_mime = mime_type.mime_id ORDER BY id DESC LIMIT 4; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | | | | id path type pic_length | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | 290 | test. JPG | NULL | 100 | | 289 | virtual machine stack. PNG | image/PNG | 63723 | | 288 | united return. PNG | image/PNG | 29485 | | 287 | lo day. JPG | image/jpeg 42117 | | +-----+------------------+------------+------------+Copy the code

3. Right (outer) join query:`RIGHT JOIN`

Maintain the integrity of the right table, left table can not be found NULL

SELECT id, pic_path AS path, mime_type. Mime_info AS type, pic_length FROM pic RIGHT JOIN mime_type ON pic.pic_mime = mime_type.mime_id ORDER BY id LIMIT 8; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | | | | id path type pic_length | +------+--------------------------------------+---------------+------------+ | NULL | NULL | text/plain | NULL | | NULL | NULL | video/mp4 | NULL | | NULL | NULL | image/svg+xml | NULL | | 1 | 30000X20000.jpg | image/jpeg | 116342886 | | 2 | 3000X2000.jpg | image/jpeg | 3404969 | | 3 | 300X200.jpg | image/jpeg | 99097 | | 4 | 30X20.jpg | image/jpeg | 10158 |  | 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | image/jpeg | 236254 | +------+--------------------------------------+---------------+------------+Copy the code

4. Full (external) connection (fake):`The use of the UNION`

MySQL does not support full join, so the keyword UNION can only be used to combine left and right join method UNION: combine several SQL query results into one set. UNION ALL does not overwrite the same result

SELECT id, pic_path AS path, mime_type. Mime_info AS type, Pic_length FROM PIC LEFT JOIN mime_type ON pic.pic_mime = mime_type. Mime_id UNION(SELECT ID, pic_path AS path, Mime_type. Mime_info AS type, pic_length FROM pic RIGHT JOIN mime_type ON pic.pic_mime = mime_type.mime_id ) ORDER BY id DESC; +------+------------------------------------------------------------------------------------+---------------+----------- - + | | | | id path type pic_length | +------+------------------------------------------------------------------------------------+---------------+----------- - + | 290 | test. JPG | NULL | 100 | | 289 | virtual machine stack. PNG | image/PNG | 63723 | | 288 | united return. PNG | image/PNG | 29485 | | 287 | According to los day. JPG | image/jpeg 42117 | |... | 3 | 300X200.jpg | image/jpeg | 99097 | | 2 | 3000X2000.jpg | image/jpeg | 3404969 | | 1 | 30000X20000.jpg | image/jpeg  | 116342886 | | NULL | NULL | text/plain | NULL | | NULL | NULL | video/mp4 | NULL | | NULL | NULL | image/svg+xml | NULL | +------+------------------------------------------------------------------------------------+---------------+----------- -+Copy the code

5. UNION quiz

CREATE TABLE a(
id CHAR(4),
num INT
);

INSERT INTO a(id,num) VALUES
('a'And 4), ('b', 6), ('c', 2), ('d'And 8); CREATE TABLE b( id CHAR(4), num INT ); INSERT INTO b(id,num) VALUES ('b'And 8), ('c', 7), ('d'And 3), ('e', 18); mysql> SELECT * FROM a; mysql> SELECT * FROM b; +------+------+ +------+------+ | id | num | | id | num | +------+------+ +------+------+ | a | 4 | | b | 8 | | b | 6 | | c | 7 | | c | 2 | | d | 3 | | d | 8 | | e | 18 | +------+------+ +------+------+ SELECT id,sum(num) FROM (SELECT * FROM a UNION ALL SELECT * FROM b) as temp GROUP BY id; +------+----------+ | id | sum(num) | +------+----------+ | a | 4 | | b | 14 | | c | 9 | | d | 11 | | e | 18 | +------+----------+Copy the code

DDL database/table building

1. About the operation database

SHOW DATABASES; Display all databasesSHOW CREATE DATABASE < DATABASE name >Create a databaseUSE < database name >;# Use databaseCREATE DATABASE < name > [CHARACTER SET < CHARACTER SET >];Create a character set for the database specifiedALTER DATABASE < DATABASE name > CHARACTER SET < CHARACTER SET >;Alter database character setDROP DATABASE < DATABASE name >;# The legendary repository run away
SELECT DATABASE(); View the currently selected database
Copy the code

2. The database information is displayed

SHOW TABLES; Display the table in the current database
SHOW TABLES FROM mysql Display the table in the specified databaseDESC < table name >;Check table structureSHOW COLUMNS FROM < table name >;Check table structure
Copy the code

3. Create a table

| | - UNSIGNED UNSIGNED AUTO_INCREMENT since growth - front ZEROFILL automatic filling 0, DEFAULT UNSIGNED CREATE TABLE create_test(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code TINYINT(5) ZEROFILL DEFAULT 0); INSERT INTO create_test(code) VALUES (5); INSERT INTO create_test VALUES ();# Default value test
 mysql> SELECT * FROM create_test;
+----+-------+
| id | code  |
+----+-------+
|  1 | 00005 |
|  2 | 00000 |
+----+-------+
Copy the code

4. Add attributes to the table

ALTER TABLE < TABLE name > ADD attribute information [AFTER attribute];

| - look at the current table structure mysql > DESC create_test; +-------+------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default |  Extra | +-------+------------------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO |  PRI | NULL | auto_increment | | code | tinyint(5) unsigned zerofill | YES | | 00000 | | +-------+------------------------------+------+-----+---------+----------------+ mysql> ALTER TABLE create_test ADD age SMALLINT UNSIGNED NOT NULL; mysql> DESC create_test; +-------+------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default |  Extra | +-------+------------------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO |  PRI | NULL | auto_increment | | code | tinyint(5) unsigned zerofill | YES | | 00000 | | | age | smallint(5) unsigned | NO | | NULL | | +-------+------------------------------+------+-----+---------+----------------+ |-- AFTER the row in the specified attribute can be AFTER (ocd) | -- - the ALTER TABLE create_test ADD password VARCHAR (32) AFTER id; mysql> ALTER TABLE create_test ADD password VARCHAR(32) AFTER id; mysql> DESC create_test; +----------+------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | password | varchar(32) | YES | | NULL | | | code | tinyint(5) unsigned zerofill | YES | | 00000 | | | age | smallint(5) unsigned | NO | | NULL | | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | - a add multiple attributes ALTER TABLE create_test  ADD (aaa VARCHAR(32), bbb VARCHAR(32),ccc VARCHAR(32)); mysql> DESC create_test; +----------+------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | password | varchar(32) | YES | | NULL | | | code | tinyint(5) unsigned zerofill | YES | | 00000 | | | age | smallint(5) unsigned | NO | | NULL | | | aaa | varchar(32) | YES | | NULL | | | bbb  | varchar(32) | YES | | NULL | | | ccc | varchar(32) | YES | | NULL | | +----------+------------------------------+------+-----+---------+----------------+Copy the code

5. Delete attributes for the table

ALTER TABLE < TABLE name > DROP attribute

ALTER TABLE create_test DROP aaa,DROP bbb,DROP ccc;

mysql> DESC create_test;
+----------+------------------------------+------+-----+---------+----------------+
| Field    | Type                         | Null | Key | Default | Extra          |
+----------+------------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned             | NO   | PRI | NULL    | auto_increment |
| password | varchar(32)                  | YES  |     | NULL    |                |
| code     | tinyint(5) unsigned zerofill | YES  |     | 00000   |                |
| age      | smallint(5) unsigned         | NO   |     | NULL    |                |
+----------+------------------------------+------+-----+---------+----------------+
Copy the code

6. Change the attribute type

ALTER TABLE < TABLE name > MODIFY attribute type [FIRST];

| - change the password to VARCHAR (40) ALTER TABLE create_test MODIFY password VARCHAR (40); mysql> DESC create_test; +----------+------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | password | varchar(40) | YES | | NULL | | | code | tinyint(5) unsigned zerofill | YES | | 00000 | | | age | smallint(5) unsigned | NO | | NULL | | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | - a property will be moved to the top of the ALTER TABLE create_test MODIFY password VARCHAR(40) FIRST; +----------+------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------------+------+-----+---------+----------------+ | password | varchar(40) | YES | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | code | tinyint(5) unsigned  zerofill | YES | | 00000 | | | age | smallint(5) unsigned | NO | | NULL | | +----------+------------------------------+------+-----+---------+----------------+Copy the code

7. Modify the attribute name of the table

ALTER TABLE < TABLE name > CHANGE original attribute new attribute new attribute type;

mysql> ALTER TABLE create_test CHANGE password pw varchar(40); mysql> DESC create_test; +-------+------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default |  Extra | +-------+------------------------------+------+-----+---------+----------------+ | pw | varchar(40) | YES | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | code | tinyint(5) unsigned zerofill | YES | | 00000 | | | age | smallint(5) unsigned | NO | | NULL | | +-------+------------------------------+------+-----+---------+----------------+Copy the code

8. Change the table name

ALTER TABLE old TABLE name RENAME new TABLE name; Method 2: RENAME a TABLE TO a new TABLE.

ALTER TABLE create_test RENAME Armstrong gyroscope jet Gun; mysql> SHOW TABLES; +--------------------------------------------------------+ | Tables_in_datatype | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Armstrong cyclotron jet Armstrong gun | | | a | b | | mime_type | | PIC | | Type_number | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + RENAME TABLE Armstrong cyclotron jet Armstrong cannon TO toly; mysql> SHOW TABLES; +--------------------+ | Tables_in_datatype | +--------------------+ | a | | b | | mime_type | | pic | | toly | | type_number | +--------------------+Copy the code

That’s the basics of SQL. See you in the next article

Structured Query Language (SQL)

Preparation activities: Create libraries and tables

DML database record operationLEVEL 1

1. Record insertion operation

2. Record update operation

3. Delete records

4. Record query operations

Ii. Picture list PIC(LEVER 2)

1. Build a predicate sentence

2. Query operationsASThe role of

3. Query is an attribute that can participate in the operation

4.WHEREA kaleidoscope of conditions

Condition of 4.1:with — AND 和 &&

Condition of 4.2:or — OR 和 ||

Condition of 4.3:non — NOT 和 !

4.4: Scatter matchingIN(v1,v2,v3,...)

4.5: Interval matchingBETWEEN v1 AND v2

4.6: Fuzzy query:LIKE

4.7: Comparison symbols= != < > <= >=

5. GROUP BYGrouping query

6. Result set screening:HAVING

7. Ranking of results:ORDER BY

8. Control the number of items:LIMIT

Third, sub-query(LEVER 3)

1. Query images with larger than average size —WHERE

2. Check for the latest inserted images of each typeWHERE

3.FROM subquery —FROM

Four, connection query

0. Create an associated table

1. Internal connection queryINNER JOIN

2. Left link query:LEFT JOIN

3. Right (outer) join query:RIGHT JOIN

4. Full (external) connection (fake):The use of the UNION

5. UNION quiz

DDL database/table building

1. About the operation database

2. The database information is displayed

3. Create a table

4. Add attributes to the table

5. Delete attributes for the table

6. Change the attribute type

7. Modify the attribute name of the table

8. Change the table name

Related Posts

Cloud server Nginx+ UWSGi +MySQL deployed Django+Vue online bookstore project

Create a SpringCloudGateway gateway project

Service gateway Spring Cloud Zuul

DML database record operation`LEVEL 1`

Ii. Picture list PIC`(LEVER 2)`

2. Query operations`AS`The role of

4.`WHERE`A kaleidoscope of conditions

Condition of 4.1:`with` — `AND` 和 `&&`

Condition of 4.2:`or` — `OR` 和 `||`

Condition of 4.3:`non` — `NOT` 和 `!`

4.4: Scatter matching`IN(v1,v2,v3,...)`

4.5: Interval matching`BETWEEN v1 AND v2`

4.6: Fuzzy query:`LIKE`

4.7: Comparison symbols`= != < > <= >=`

5. `GROUP BY`Grouping query

6. Result set screening:`HAVING`

7. Ranking of results:`ORDER BY`

8. Control the number of items:`LIMIT`

Third, sub-query`(LEVER 3)`

1. Query images with larger than average size —`WHERE`

2. Check for the latest inserted images of each type`WHERE`

3.FROM subquery —`FROM`

1. Internal connection query`INNER JOIN`

2. Left link query:`LEFT JOIN`

3. Right (outer) join query:`RIGHT JOIN`

4. Full (external) connection (fake):`The use of the UNION`