Chapter three: three design patterns of database
This is the 17th day of my participation in the More text Challenge. For more details, see more text Challenge
1. Are all fields in the first normal form 1NF data table indivisible atomic values? create table user(id int primary key,name varchar(20)); Fields that can continue to be split do not satisfy the first normal form. Can not split to meet the first port type. For example, Xianfeng Dam in Enshi, Hubei, China. It can also be folded into Xianfeng Dam in Enshi, Hubei, China. The second normal form must satisfy the first normal form. The second normal form requires that every column except the primary key must be completely dependent on the primary key. Otherwise fold the meter. Incomplete dependencies can only occur if the primary key is joined. All elements in a table depend on a single primary key ！！！！！！ 3. Third normal form 3NF must first satisfy the second normal form. There must be no transport dependencies between columns other than primary key columns. For example, elements in a table cannot depend on each other except for the primary key.Copy the codeChapter 4 Four join queries of SQL
Select * from user inner join user1 on user.id = user1.id; On: indicates the inline query of the join conditions. In fact, the data in two tables is queried based on a certain field pair. No, no, no. Select * from user left join user1 on user.id = user1.id; select * from user left join user1 on user.id = user1.id; The left outer join will extract all the data in the left table (URSE), and the data in the right table (user1), if any, will be displayed. Select * from user right join user1 on user.id = user1.id; select * from user right join user1 on user.id = user1.id; The right outer join will pull out all the data in the right table (URSE), and the left table (user1) will display if there are equal data. Select * from user full join user1 on user.id = user1.id; select * from user full join user1; MYSQL does not support full join, but can join tables with unionCopy the codeChapter 5 mysql Transactions
In mysql, transactions are actually a minimal, indivisible unit of work. Transactions ensure the integrity of a business. Multiple SQL statements may be required to succeed simultaneously or fail simultaneously. Mysql > select @@autocommit; mysql > select @@autocommit; mysql > select @@autocommit; mysql > select @@autocommit; What is the function of default transaction enablement? When we execute an SQL statement, the effect is immediate and cannot be rolled back. Rollback: undo SQL statement execution rollback; Set autocommit=0 select @@autocommit: @@autocommit = 1. Select @@autocommit: @@autocommit = 1 Use rollback to undo the previous data. Rollback: Rollback is not allowed. Automatic submission is set to 0. Transactions give us an opportunity to return. begin; start transaction; Can manually start a transaction begin; Update user set mo =mo+100 where name='b'; Begin and start Transaction are the same as setautoCOMMIT =0. Four characteristics of transaction: A atomicity: transaction is the smallest unit, can not be split B consistency: transaction requirements, the same transaction SQL statements, must be guaranteed to succeed or fail at the same time. I Isolation: There is isolation between transaction 1 and transaction 2. D Persistence: transactions cannot be returned once they are committed (rollback). Set autocommit=0; 2.begin; 3.start transaction; Commit the transaction manually: commit; Rollback; Isolation: 1. Read UNcommitted; 2. Read committed; 3. Repeatable read; 4. Serializable; Serialization 1- Read UNcommitted if transaction A and TRANSACTION B operate on the data. During the operation, transaction A is not committed, but TRANSACTION B can see the results of operation A. If transaction A is open and its data can be read by another transaction, dirty reads will occur. The implementation does not allow dirty reads. !!!!!!!!!! How do I view the isolation level of a database? Mysql 8.0: -- system level: select @@globally. transaction_isolation; -- Session level: select @@transaction_isolation; Mysql 5.x: select @@global. Tx_isolation select @@tx_isolation; mysql 5.x: select @@global. How do I change the isolation level? set global transaction isolation level read uncommitted;Copy the codeRead UNcommitted (transaction A performs an operation on data, during which transaction A is not committed, but transaction B can see the results of A’s operation.) Mysql > select * from user1; + — — — — — – + + — — — — — — — — — — — — — — — + | | id name | money | + — — — — — – + — — — — — — — – + — — — — — — — + | 1 | xiaoming | 10000 | | 1 | taobao shop | 10000 | | | 2 Small culvert | | + — — — — — – 5000 + + — — — — — — — — — — — — — — — + 3 rows in the set (0.00 SEC)
//2. View the isolation level of the data table. Select @@global.tx_isolation; +———————–+ | @@global.tx_isolation | +———————–+ | READ-UNCOMMITTED | +———————–+
//3. Change the isolation level. Set global Transaction Isolation level read UNcommitted;
Mysql > select @@global.tx_isolation; +———————–+ | @@global.tx_isolation | +———————–+ | READ-UNCOMMITTED | +———————–+
//5. Start transaction begin; Update user1 set money = money-9000 where name=” xiao Ming “; update user1 set money = money-9000 where name=” xiao Ming “; Update user1 set money = money+9000 where name=” user1 “;
mysql> select * from user1; + — — — — — – + + — — — — — — — — — — — — — — — + | | id name | money | + — — — — — – + — — — — — — — – + — — — — — — — + | 1 | xiaoming | 1000 | | 1 | taobao shop | 19000 | | 2 | small culvert | | + — — — — — – 5000 + + — — — — — — — — — — — — — — — +
//7. Xiao Ming used rollback to undo the previous step. rollback;
mysql> select * from user1; + — — — — — – + + — — — — — — — — — — — — — — — + | | id name | money | + — — — — — – + — — — — — — — – + — — — — — — — + | 1 | xiaoming | 10000 | | 1 | taobao shop | 10000 | | | 2 Small culvert | | + — — — — — – 5000 + + — — — — — — — — — — — — — — — + 3 rows in the set (0.00 SEC)
2.read committed; (select @@global.tx_isolation;) select @@global.tx_isolation; set global transaction isolation level read committed; C. Start transaction start transaction d. Perform operations on the transaction e. Commit; 3. Repeatable read; (can be read twice) transaction 1: a. Create table b. Modify transaction (select @@global.tx_isolation;) set global transaction isolation level repeatable read; C. Start transaction. D. Insert data E. Commit; F. Read the committed data transaction 2: a. Create table first b. View table C. Start transaction d. Insert the same data as transaction 1. Transaction 1 and transaction 2 operate on the same table at the same time. Transaction 1 commits data that cannot be read by transaction 2. 4.serializable; (select @@global.tx_isolation;) select @@global.tx_isolation; set global transaction isolation level serializable; C. Start transaction start transaction d. Perform operations on the transaction e. Commit; B. Modify the transaction (select @@global.tx_isolation;) set global transaction isolation level serializable; If transaction 2 does not commit the transaction, transaction 1 cannot commit the data, and transaction 1 enters the queued state (serialization). Transaction 2 cannot commit the data until transaction 1 commits the commit. READ-UNCOMMITTED > READ-COMMITTED>REPEATABLE >SERIALIZABLECopy the codeChapter 6 Operation data Table records
5.1 Insert, Update, or delete a record to modify a table name: alert table Table name rename Table name to be modified; alter table A rename B; Alter table alter table name add column Add type; alter table B add column sex varchar(10); Alter table B drop column age; alter table B drop column age; Alter table table name alter column name attribute to be modified; alter table B modify sex char(20); Alter TABLE Table name change column Class name to be modified Class name to be modified attributes to be modified class name; alter table B change column id iddd char(10); Insert into table values(); Insert into B values (1, 2); Insert into table_name set id = 3; insert into B selectCopy the codeconclusion
This is a long article, give a big thumbs-up to those who see it! Due to the limited level of the author, it is inevitable that there will be mistakes in the article, welcome feedback and correction.
If you think the article is helpful to you, please like, comment, and collect
Your support is my biggest motivation!!