Insert query results into other tables

• Be able to write SQL statements that insert query results into other tables

1. Thinking

At present, there is only one goods table. We want to add a commodity classification information. For example, the classification information of mobile devices cannot be added only through the goods table. How to add commodity classification information?

answer:

1. Create a commodity classification table and add the commodity classification information in the goods table to the table.
2. Change the classification name in the goods table to the corresponding classification id in the commodity classification table

2. Create commodity classification table

-- Create commodity classification table
create table good_cates(
    id int not null primary key auto_increment, 
    name varchar(50) not null
);

3. Add the commodity classification in the goods table to the commodity classification table

-- query goods Classification information of goods in the table
select cate_name from goods group by cate_name;

-- Insert query results into good_cates In the table
insert into good_cates(name) select cate_name from goods group by cate_name;

-- Add mobile device classification information
insert into good_cates(name) values('Mobile device');

explain:

• insert into... select... Means that the query result is inserted into the specified table, that is, the table is copied.

4. Summary

• To complete table replication, you can use: insert into select .. SQL statement

Update data of a field in a table with a join

1. Update the commodity classification information in the goods table

In the last lesson, we created a good_categories and inserted the commodity classification information. Now we need to update the commodity classification information in the goods table and change the commodity classification name to commodity classification id.

Next, we implement the second step:

• Change the classification name in the goods table to the corresponding classification id in the commodity classification table

-- see goods The commodity classification corresponding to the commodity classification name in the table id
select * from goods inner join good_cates on goods.cate_name = good_cates.name;

-- In this statement from The following statement is understood as a virtual table  
update goods g inner join good_cates gc on g.cate_name=gc.name set g.cate_name=gc.id;

2. Summary

• Use the: update... Join... Statement to update the data in the join table

Create a table and add data to a field

• Be able to write SQL statements that create tables and add data to a field

1. Thinking

In the last lesson, we completed the creation of good_categories and the addition of commodity classification information, and changed the commodity classification name in the goods table to the corresponding commodity classification id. if we want to add a brand, such as shuangfeiyan, we can't add the brand information only through the goods table, So how to add brand information?

answer:

1. Create a brand table and add the brand information in the goods table to the table.
2. Change the brand name in the goods table to the corresponding brand id in the brand table

2. Create brand table

​

-- Query brand information 
select brand_name from goods group by brand_name;

-- adopt create table ...select To create a data table and insert data at the same time
-- Create a commodity classification table. Note:: Need to brand_name use as Alias, otherwise name The field has no value
create table good_brands (     
	id int unsigned primary key auto_increment,     
	name varchar(40) not null
) select brand_name as name from goods group by brand_name;

explain:

• create table... select column name... Means to create a table and insert data

3. Update the brand information in the goods table

-- take goods Change the brand name in the table to the corresponding brand in the brand table id
update goods as g inner join good_brands gb on g.brand_name = gb.name set g.brand_name = gb.id;

4. Summary

• To create a table and insert data into fields, use the: create table... select statement

Modify the structure of the goods table

• Be able to write SQL statements that modify the table structure

Modify the structure of the goods table

At present, we have changed the commodity classification and brand information in the good table to commodity classification id and brand id. next, we need to change cat_ Name and brand_ Change the name field to cat respectively_ id and brand_ The id field and type are changed to int type

-- View table structure
desc goods;
-- adopt alter table Statement to modify table structure
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

explain:

• alert table can modify multiple field information at the same time

Summary

• To modify the table structure, you can use the alter table statement. Multiple modified fields are separated by commas

affair

• Be able to know the four characteristics of transactions

1. Introduction to services

Transaction is a series of user-defined operations to execute SQL statements. These operations are either completely executed or not executed at all. It is an inseparable work execution unit.

Usage scenario of transaction:

In daily life, sometimes we need to make bank transfer. Behind this bank transfer operation is the need to execute multiple SQL statements. If the power is suddenly cut off halfway through the execution of these SQL statements, this function will only be completed halfway. This situation is not allowed. To solve this problem, we need to complete it through transactions.

2. Four characteristics of affairs

• Atomicity
• Consistency
• Isolation
• Durability

Atomicity:

A transaction must be regarded as an indivisible minimum unit of work. All operations in the whole transaction must be committed successfully or rolled back. For a transaction, it is impossible to perform only part of the operations, which is the atomicity of the transaction

uniformity:

The database always transitions from one consistent state to another. (in the previous example, consistency ensures that even if the system crashes during the transfer process, there will be no loss of $200 in the checking account. Because the transaction is not submitted in the end, the changes made in the transaction will not be saved to the database.)

Isolation:

Generally speaking, the modification operation made by a transaction is invisible to other transactions before committing the transaction. (in the previous example, when the third statement is executed and the fourth statement is not started, another account summary program starts running, and it sees that the balance of the checking account has not been reduced by $200.)

persistence:

Once a transaction is committed, its changes are permanently saved to the database.

explain:

Transactions can ensure the integrity and consistency of data and make users' operations more secure.

3. Use of transactions

Before using transactions, first ensure that the storage engine of the table is of InnoDB type. Only this type can use transactions. The storage engine of the table in MySQL database is of InnoDB type by default.

Table storage engine Description:

Table storage engine is a mechanism to store data. Different table storage engines provide different storage mechanisms.

Effect drawing of automobile engine:

explain:

• Different car engines provide different car power.

To view the storage engines of tables supported by MySQL database:

-- see MySQL Database supported table storage engine
show engines;

explain:

• The commonly used table storage engines are InnoDB and MyISAM
• InnoDB supports transactions
• MyISAM does not support transactions. The advantage is that it has fast access speed, has no requirements for transactions, or can use the storage engine to create tables based on select and insert

To view the table creation statement of the goods table:

​

-- Select database
use jing_dong;
-- see goods surface
show create table goods;

mysql root@(none):jing_dong> show create table goods;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| goods | CREATE TABLE `goods` (                                 |
|       |   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,       |
|       |   `name` varchar(150) NOT NULL,                        |
|       |   `cate_id` int(10) unsigned NOT NULL,                 |
|       |   `brand_id` int(10) unsigned NOT NULL,                |
|       |   `price` decimal(10,3) NOT NULL DEFAULT '0.000',      |
|       |   `is_show` bit(1) NOT NULL DEFAULT b'1',              |
|       |   `is_saleoff` bit(1) NOT NULL DEFAULT b'0',           |
|       |   PRIMARY KEY (`id`)                                   |
|       | ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------+

explain:

• Through the table creation statement, we can know that the storage engine of the goods table is InnoDB.
• Modify the storage engine usage of the table: alter table name engine = engine type;
  • For example: alter table students engine = 'MyISAM';

Open transaction:

begin;
perhaps
start transaction;

explain:

• After the transaction is started, execute the modify command, and the changed data will be saved to the cache file of the MySQL server instead of being maintained in the physical table

• MySQL database adopts autocommit mode by default. If a transaction is not displayed, each sql statement will be committed as a transaction

• When autocommit=0 is set, the auto commit transaction mode is cancelled until the execution commit and rollback displayed indicate the end of the transaction.

  • set autocommit = 0 means that the auto commit transaction mode is cancelled. You need to manually execute commit to complete the transaction submission

set autocommit = 0;
insert into students(name) values('Liu Sanfeng');
-- Manual submission is required before data can be added to the table, To verify, you need to reopen a connection window to view the data information of the table, because the automatic submission mode is temporarily closed
commit

-- Reopen a terminal window and connect MySQL Database server
mysql -uroot -p

-- Then query the data,If the previous window was executed commit，Only this window can see the data
select * from students;

Commit transaction:

Submit the data in the local cache file to the physical table to complete the data update.

commit;

Rollback transaction:

Discarding the cached data in the local cache file indicates that it returns to the state before starting the transaction

rollback;

SQL statement of transaction drill:

begin;
insert into students(name) values('Li Bai');
-- Query data. There is new data at this time, be careful: If there is no subsequent commit transaction here, the data is not really updated to the physical table
select * from students;
-- Only when the transaction is committed here can the data be really inserted into the physical table
commit;

-- Open a new terminal and reconnect MySQL Database, query students surface,At this time, the newly added data is not displayed, indicating that the previous transaction has not been committed, which is the isolation of transactions
-- The modification operation made by a transaction is invisible to other transactions before committing the transaction
select * from students;

4. Summary

1. Transaction characteristics:

   • Atomicity: emphasize that multiple operations in a transaction are a whole
   • Consistency: emphasize that inconsistent status will not be saved in the database
   • Isolation: emphasize that transactions in the database are not visible to each other
   • Persistence: it emphasizes that the database can store data permanently and cannot be revoked once submitted

2. MySQL database adopts the auto commit mode by default, that is, the operation of modifying data (insert, update, delete) will automatically trigger the transaction and complete the transaction submission or rollback

3. Start a transaction using begin or start transaction;

4. Rollback is used to rollback transactions;

5. The conn.commit() operation in pymysql is to commit the transaction

6. The conn.rollback() operation in pymysql is to roll back the transaction

Indexes

• Be able to write SQL statements to create indexes

1. Introduction to index

Index is also called "key" in MySQL. It is a special file that stores the location information of all records in the data table. More generally, database index is like a directory in front of a book, which can speed up the query speed of the database.

Application scenario:

When there is a large amount of data in the database, the search for data will become very slow, so we can improve the query efficiency of the database through indexing.

2. Use of index

View existing indexes in the table:

show index from Table name;

explain:

• The primary key column is automatically indexed

Index creation:

-- Syntax format of index creation
-- alter table Table name add index Index name[Optional](Listing, ..)
-- to name Add index to field
alter table classes add index my_name (name);

explain:

• The index name is not specified. The field name is used by default

Index deletion:

​

-- Delete the syntax format of the index
-- alter table Table name drop index Index name
-- If you do not know the index name, you can view the created table sql sentence
show create table classes;
alter table classes drop index my_name;

3. Case - verify index query performance

Create test table testindex:

create table test_index(title varchar(10));

Insert 100000 pieces of data into the table:

from pymysql import connect

def main():
    # Create Connection
    conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
    # Get Cursor object
    cursor = conn.cursor()
    # Insert data 100000 times
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # Submit data
    conn.commit()

if __name__ == "__main__":
    main()

To verify index performance:

• Start running time monitoring: set profiling=1;

-- Start running time monitoring:
set profiling=1;
-- Find the 10000th data ha-99999
select * from test_index where title='ha-99999';
-- View execution time:
show profiles;
-- to title Create index for field:
alter table test_index add index (title);
-- Execute the query statement again
select * from test_index where title='ha-99999';
-- Review the execution time again
show profiles;

4. Joint index

Joint index is also called composite index, that is, an index covers two or more fields in a table. It is generally used when multiple fields are queried together.

-- establish teacher surface
create table teacher
(
    id int not null primary key auto_increment,
    name varchar(10),
    age int
);

-- Create federated index
alter table teacher add index (name,age);

Benefits of Federated indexing:

• Reduce the disk space overhead, because every index created is actually an index file created, which will increase the disk space overhead.

5. Leftmost principle of joint index

When using a federated index, we should follow a leftmost principle, that is, the index(name,age) supports the combined query of name, name and age, but does not support a separate age query, because the created federated index is not used.

Example of leftmost principle:

-- The following query uses a federated index
select * from stu where name='Zhang San' -- Here, the union index is used name part
select * from stu where name='Li Si' and age=10 -- Here the full use of joint indexes, including name and age part 
-- The following query does not use a federated index
select * from stu where age=10 -- Because there is no such combination in the union index, only name | name age These two combinations

explain:

• When using the joint index to query data, you must ensure that the leftmost field of the joint index appears in the query criteria, otherwise the joint index will become invalid

6. Advantages, disadvantages and usage principles of index in MySQL

• advantage:

  1. Speed up data query

• Disadvantages:

  1. Creating an index will take time and disk space, and the time will increase as the amount of data increases

• Use principle:

  1. Through the comparison of advantages and disadvantages, it is not that the more indexes the better, but that they need to be used reasonably.
  2. Avoid creating too many indexes for frequently updated tables, and create indexes for frequently used query fields,
  3. It is better not to use an index for a table with a small amount of data, because it may take less time to query all the data than to traverse the index, and the index may not produce optimization effect.
  4. There are many same values in one field. Do not establish an index. For example, there are only two different values for male and female in the "gender" field of the student table. On the contrary, more different values in a field can be indexed.

7. Summary

• Index is a means to speed up the query of database
• Index creation: alter table name add index index name [optional] (field name, xxx);
• To delete an index, use: alter table name drop index index name;

come on.

thank!

strive!