Finish MySQL (all) 3A in half A day

Finish MySQL in half a day (all)

Finish MySQL (all) 1 in half a day
Finish MySQL (all) 2 in half a day
Finish MySQL (all) 3 in half a day
The blogger uses MySQL version 8.0, and the storage engine is InnoDB. We don't explain InnoDB in detail here. If necessary, we recommend you to know This article Blog (or Baidu) https://www.jianshu.com/p/519fd7747137

7. alter command
Function: modify data table name or data table field
Add a field add

mysql> alter table city_food add people varchar(20);

First insert

mysql> alter table city_food add people varchar(20) first;

Back insertion

mysql> alter table city_food add pop varchar(20) after city_food;

Delete a field delete

mysql> alter table city_food drop people;

modify or change the field type and name
mosify
Modify field type

mysql> alter table city_food modify pop int;

Change field name and type pop int - > P varchar (20)

mysql> alter table city_food change pop p varchar(20);

Modify default and not null

mysql> alter table city_food modify p int not null default 0;

Add primary key

First, make sure the primary key is not empty

mysql> alter table c_f modify food_ID int not null;
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

add to

mysql> alter table c_f add primary key(food_ID);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Delete primary key

mysql> alter table c_f drop primary key;

Set field defaults

mysql> alter table city_food alter p set default 1;

Modify table name

mysql> alter table city_food rename to c_f;
Query OK, 0 rows affected (0.03 sec)

8. Index

The establishment of MySQL index is very important for the efficient operation of MySQL. Index can greatly improve the retrieval speed of MySQL.
Index is divided into single column index and combined index. Single column index, that is, an index only contains a single column, and a table can have multiple single column indexes, but this is not a composite index. Composite index, that is, an index contains multiple columns.
In fact, the index is also a table, which holds the primary key and index fields, and points to the records of the entity table.
All of the above are about the benefits of using index, but excessive use of index will cause abuse. Therefore, index also has its disadvantages: Although index greatly improves query speed, it also reduces the speed of updating tables, such as INSERT, UPDATE and DELETE. Because when updating the table, MySQL not only needs to save the data, but also the index file.
Index files that take up disk space.

Create index

 mysql> create index city on city_spots(city_name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Modify table index

mysql> alter table city_play add index play(city_thing);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> create table city_food (
    -> city_name varchar(20) not null,
    -> city_food varchar(20),
    -> food_kinds int,
    -> food_ID int auto_increment,
    -> index(food_ID));
Query OK, 0 rows affected (0.06 sec)

Drop index drop

mysql> drop index play on city_play;

perhaps

mysql> ALTER table city_play drop index city_name;

unique index

The value of the index column must be unique, but empty values are allowed. If it is a composite index, the combination of column values must be unique.
It's created in a similar way to a normal index, but with the keyword: unique

For example:
Create index

mysql> create unique index city on city_play(city_name);

perhaps

mysql> alter table city_play add unique index city_name(city_name);

Maybe I want to ask, I will create index, how to use index? But the index does not need to be used by people, nor can it be called actively, so it can only be called by the system itself.
For example, just gave table c_f creates the index ppl of people
At this time, the system will use the index automatically

mysql> select * from c_f where people regexp 'a$';

9. Provisional table

Mysql temporary tables are very useful when we need to save some temporary data. Temporary tables are only visible in the current connection. When the connection is closed, Mysql will automatically delete the table and free all space.
To create a temporary table:

mysql> create temporary table ppp(
    -> p1 int not null default 0,
    -> p2 int,
    -> p3 int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into ppp values(1,2,3),(2,3,4),(3,4,5);

mysql> select * from ppp;
+----+------+------+
| p1 | p2   | p3   |
+----+------+------+
|  1 |    2 |    3 |
|  2 |    3 |    4 |
|  3 |    4 |    5 |
+----+------+------+
3 rows in set (0.00 sec)

To manually delete a temporary table:

mysql> drop table ppp;
Query OK, 0 rows affected (0.00 sec)

To create a temporary table directly with a query:

mysql> create temporary table xxx as(
    -> select * from city_spots
    -> limit 0,10000);
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

Query new temporary table

mysql> select * from xxx;
+-----------+--------------------+------------+----------+----------+-----------+
| city_name | place_of_interest' | city_score | cost_rmb | time_way | time_stay |
+-----------+--------------------+------------+----------+----------+-----------+
| America   | unknown               |         82 |    20000 |      1.5 |         4 |
| Iceland   | Ice                |         88 |    19000 |      1.5 |         5 |
| Italy     | food               |         79 |    20000 |        1 |         4 |
| Janpan    | unknown               |         85 |    15000 |        1 |         3 |
| Shanghai      | shopping               |         80 |    13000 |        1 |       2.5 |
| northeast      | Snow                 |         86 |    10000 |      1.5 |         5 |
| Beijing      | the Great Wall               |         93 |    15000 |        1 |         3 |
| Xinjiang      | Fruits               |         90 |    12000 |      1.5 |         5 |
| Hainan      | sandy beach               |         78 |     9000 |        1 |         4 |
| Xi'an      | terra cotta warriors             |         83 |     5500 |        1 |         3 |
| Hong Kong      | unknown               |         76 |     8000 |        1 |         3 |
+-----------+--------------------+------------+----------+----------+-----------+
11 rows in set (0.00 sec)

Query xxx table after exiting MySQL server

mysql> select * from xxx;
ERROR 1146 (42S02): Table 'citys.xxx' doesn't exist

Therefore, the temporary table is only visible to the current connection. When the connection is closed, the temporary table will be deleted and free space.
To be continued.....
Finish MySQL (all) 1 in half a day
Finish MySQL (all) 2 in half a day
Finish MySQL (all) 3 in half a day

Tags: MySQL

Posted on Sat, 20 Jun 2020 00:32:52 -0400 by Frapster