1. Python operates MySQL
Import third-party module: pymysql
Operation steps (text description):
1. Connect to MySQL first
host, port, username, password, charset, library, autocommit, etc.
2. Write SQL statements in python
3. Start executing the SQL statement and get the result
4. Processing in python (further processing of data)
Code implementation:
# 1. Link mysql con=mysql.connect( host='127.0.0.1', port='3306', user='root', password='1234', charset='utf8', db='db5', autocommit=True ) # 2. Get the cursor cursor=con.cursor() # 3. Write SQL statements sql = 'select*from class' # 4. Execute SQL statement e = sursor.execute(sql) # 5. Print SQL statement results res = cursor.fetchone() # fetchone gets the first piece of data print(res) res1 = cursor.fetchall() # fetchall gets all the data print(res1) res2 = cursor.fetchmany(2) # fetchmany(2) gets the first two pieces of data print(res2)
2. SQL injection problem
1. Reasons for SQL injection:
Because the combination of special symbols will produce special effects
2. Conclusion: When designing the sensitive data part, do not splice it yourself, just leave it to the thread method (execute) to splice it
3. Notes:
When using code for data operations, the levels of different operations are different. It doesn’t matter if you check. For additions, changes, and deletions, you need to confirm twice ——conn.commit(). You can also autocommit when connecting to mysql. =True, after adding this sentence, you no longer need to write conn.commit() in the future
3. View
1. What is a view
A view is to obtain a virtual table through query and then save it so that it can be used directly next time.
2. Why use views
If you want to use a virtual table frequently, you don’t need to write SQL statements repeatedly.
3. How to use views
Usage: create view view name as query SQL statement
create view info as select*from teacher inner join course on teacher.tid=course.teacher_id;
After it is created, verify its existence with Navicat verification, cmd terminal verification, and finally file verification.
Conclusion: Does the view only have table structure data or does it come from the previous table?
Delete a view: drop view view name;
The view only has table structure files and no table data files.
Views are generally used for queries. Do not modify the data in the view easily.
4. Trigger
1. What is a trigger
When the data in a certain table is added, deleted, etc., the function that is automatically triggered is called a trigger.
2. Why use triggers
Triggers are specifically targeted at our behavior of inserting, deleting, and updating data in a certain table. Once this type of syntax is executed, it will trigger the execution of the trigger, that is, automatically run another piece of SQL code.
3. Create trigger syntax
Grammar structure:
create trigger trigger name before/after/ insert/update/delete on table name for each row
begin
sql statement
end
Code demo:
# For insertion
create tigger tri_after_insert_t1 after insert on table name for each row
begin
sql code
end
# For deletion
create trigger tri_after_delete_t1 delete on table name for each row
begin
sql code
end
# For modifications
create trigger tri_after_update_t1 after update on table name for each row
begin
sql code
end
Note:
When writing SQL code, whether the end symbol is; and the end of the entire trigger also requires a semicolon, which will cause a syntax conflict. We need to temporarily modify the end symbol delimiter modified symbol (delimiter $$). This syntax is only valid in the current window.
5. Transactions
1. What is a transaction
Opening a transaction can contain a number of SQL statements that either succeed or fail at the same time.
2. The role of transactions
Guaranteed data security for data operations
3. Four attributes of transactions
1. Atomicity: A transaction is an indivisible unit of work. Many operations included in the transaction are either done or not done.
2. Consistency: A transaction must change the database from one consistency state to another. Consistency and atomicity are closely related.
3. Isolation: The execution of a transaction cannot be interfered with by other things. That is, the operations and data used within a transaction are isolated from other concurrent things, and there must be no interference between concurrently executed things.
4. Persistence: Persistence, also known as permanence, means that once a faulty thing is submitted, its changes to the data in the database are permanent, and subsequent other operations or failures should not have any impact on it.
4. How to use
'''
3 keywords for things:
starttransaction;
commit
rollback
'''
create table t1(
id int primary key auto_increment,
name char(32),
balance int
);
insert into t1(name,balance) values('jason',1000),('engo',1000),('jack',1000);
# Open the thing before modifying the data
start transaction;
# Modification operation
update t1 set balance=900 where name='jason';
update t1 set balance=900 where name='engo';
update t1 set balance=900 where name='jack';
rollback; # Roll back to the previous state
# After opening the thing, as long as the commit operation has not been performed, the data has not been refreshed to the hard disk, that is, the lock says that the online modification has not been successful.
commit;
'''If the thing detection is complete and the commit operation is turned on, the commit operation is complete and the rollback is performed to the previous state. After the commit operation is completed, modifications cannot be made'''
6. Stored procedures
1. What is a stored procedure
A stored procedure contains a series of executable SQL statements. The stored procedure is stored in MySQL. By calling its name, you can execute a bunch of SQL inside it, similar to custom functions in Python.
2. Basic usage
# Premise: The stored procedure can only be operated in the corresponding library under which library it is created.
create procedure p1()
begin
select*from user;
end
# transfer
call p1()
# Called in python program
pymysql link mysql
The generated travel table cursor, callproc()
cursor.execute()
# Define variables in advance in MySQL: set @variable name = variable value
# View variable name: select @variable name
# Call: call table name (parameter)
7. Function
Prerequisite: mysql built-in functions can only be used in sql statements
create table blog(
id int primary key auto_increment,
name varchar(32),
sub_time datatime
);
insert into blog(name,sub_time) values('Article 1','2012-12-11 11:11:11'),('Article 1','2013-10-11 11:11:11') ,('Article 1','2014-09-11 11:11:11');
# Convert year, month, day, hour, minute and second format into year and month format
select data_format(sub_time,"%Y-%m"),count(id) from group by data_format(sub_time,"%Y-%m");
8. Index (Key Points)
1. What is an index?
An index is a data structure, also called a “key”, that is used by the storage engine to quickly find records.
The indexes are:
primary key———-can speed up the query and also has a constraint effect
unique key———-can speed up the query and also has a constraint effect
Index key————- can speed up the query and has no constraint effect
2. The essence of index
By continuously narrowing the scope of the search, we can filter out the final desired results and turn random events into sequential events. In other words, with this indexing mechanism, we can always use the same search method to lock the data.
3. The impact of index
1. When there is a large amount of data in the table, index creation will be very slow (when creating the table, if you obviously need an index, add it in advance)
2. After the index is created, the query performance of the table will be greatly improved, but the write performance will be reduced.
b + tree
Leaf nodes store real data, and trunk and branch nodes store virtual data.
4. Clustered index (primay key)
Clustered index actually refers to the primary key of the table
Features: Complete records placed on leaf nodes
5. Auxiliary index (unique key index key)
Auxiliary index: When querying data, it is not always possible to use ID as a filtering condition. Field information such as name, password, etc. may also be used. At this time, the accelerated query effect of the clustered index cannot be used. You need to create indexes for other fields. These indexes are auxiliary indexes.
Features: The leaf node stores the primary key value of the record corresponding to the auxiliary index field.
select name from user where name=’jack’;
The above statement is called a covering index: all the data we want has been found only in the leaf nodes of the auxiliary index.
select age from user where name=’jack’;
The above statement is called a non-covering index. Although the index field name is hit during the query, the age field is what is being searched, so the primary key search is still needed.
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Python entry skill treeBasic skillsDatabase operation 383724 people are learning the system