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