Shulou(Shulou.com)06/01 Report--

1. MySQL transaction (1) introduction of storage engine

   introduction: when the client sends a SQL statement to the server, the server passes through caching, syntax checking and verification, and then calls some underlying software organizations to query data from the database, and then return the queried result set to the client, and these underlying software organizations are the storage engine.

Storage engine for    MySQL:

The core of     -MySQL is the storage engine. MySQL can set up many different storage engines, and different storage engines have different policies for indexing, storage, and locking.

Prior to     -Mysql5.5, the myisam storage engine was used, which supported full-text search and did not support transactions.

After     -Mysql5.5, the innodb storage engine is used, which supports transaction and row-level locking.

(2) introduction of MySQL transaction

   introduction: transaction is a sequence of operations, these operations are either done or not done, is an inseparable unit of work. Transactions are needed only when the business can be completed by two or more SQL statements, because the synchronization principle of transactions is relatively inefficient.

ACID features of    transactions:

    -atomicity: indivisible when placed in a set of operations in the same transaction

    -consistency: the overall state is unchanged before and after the execution of the transaction

    -isolation: transactions exist independently, and two different transactions do not affect each other

    -persistence: after the transaction is executed, it will permanently affect the database.

# example: a transaction operation BEGIN;update t_account set money=money+100 where id = 1 * update t_account set money=money-100 where id = 2 * * commit * A rollback operation BEGIN;update t_account set money=money+100 where id = 1 * * update t_account set money=money-100 where id = 2 * * commit

Note: automatic commit is used for MySQL database and dml operation

# View the problems caused by auto-commit show variables like 'autocommit';# modification auto-commit set autocommit=0; (3) MySQL transaction concurrency

   dirty read: uncommitted data from another transaction is read within the scope of the execution of one transaction.

   solution: read committed, one database can only read the data committed by another transaction. (Oracle default transaction isolation level)

   non-repeatable: a transaction that is modified and committed by another transaction within the read-only range, resulting in inconsistent data read multiple times.

   resolution: readable (MySQL default transaction isolation level)

   virtual reading: a problem in which data is deleted or added by another transaction within the read-only range of one transaction, resulting in inconsistent data read multiple times.

   solution: serialization: all problems are solved, but the speed is too slow to use concurrent transactions.

   Note: check the isolation level of the transaction: select @ @ tx_isolation

2. MySQL storage program (1) introduction of MySQL storage program

   description: run programs with the server side.

   advantages: simplify development, high execution efficiency (on the server side to pass the verification, can be used directly)

Disadvantages of   : storing these storage programs on the server side takes up disk space; when data migration, these storage programs need to be migrated; debugging and writing programs are not convenient on the server side.

Classification of    stored programs: stored procedures, stored functions, triggers

   Note: stored programs cannot use transactions

(2) stored procedure

   introduction: a stored procedure is an executable block of code on the server side.

Example:

# modify Terminator flag delimiter / / # create stored procedure create procedure pro_book () begin # sql select * from book;select * from book where bid=3;end / / # run the delimiter / / create procedure pro_book02 (num int) begin select * from book where bid=num;end of the parameter call pro_book () # / /-- call call pro_book02 (3) # outgoing parameter delimiter / / create procedure pro_book03 (num int,out v_name varchar (10)) begin select bname into v_name from book where bid=num;end; / /-- call, where @ v_name is a user variable call pro_book03 (1 _ create procedure pro_book04 _ name); select @ vsignnametrat # pass in the outgoing parameter delimiter / / create procedure pro_book04 (num int) begin select bid into num from book where bid=num;end / /-- call set @ vroomidroom3;-- assign call pro_book04 (@ v_id) to the user variable; select @ v_id

Control flow statement

# if statement delimiter / / create procedure if_test (score int) begin-defines the local variable declare myLevel varchar (20); if score > 80 then set myLevel='A';elseif score > 60 then set myLevel='B';else set myLevel='C';end if;select myLevel;end; / /-- calls call if_test (70); # while loop delimiter / / create procedure while_test () begin declare i int; declare sum int; set idirectset sum = 0bot while i100 then-- leaves loop loop leave lip; end if Set sum=sum+i;set iTunes 1 set end loop; select sum;end; / / call loop_test () # repeat Loop delimiter / / create procedure repeat_test () begin declare i int; declare sum int; set iTunes 1 switch set sum= 0 repeat set sum=sum+i;set ionomer 1 Tech sum=-do not add the semicolon until I > 100 end repeat; select sum;end; / / call loop_test () (3) Storage function

   is stored on the server side, has a return value, and the function can be called as part of the SQL.

* * example * *: delimiter / / create function func_01 (num int)-return value type returns varchar (20) deterministicbegin declare v_name varchar (20); select bname into v_name from book where bid = num; return vroomnamescape end; / / set @ v_name=func_01 (3); select @ vreturnametram-call select * from book where bname=func_01 (3) as part of SQL

The difference between functions and stored procedures

  -the stored procedure has three parameter modes (in, out, inout) to implement the input and output of data, while the function passes the data through the return value.

  -keyword is different

  -stored procedures can be executed as individuals, and functions can only be executed as part of SQL.

(4) trigger

   triggers, stored on the server side, are called by events and cannot pass parameters.

   event types: add, delete, change

   syntax:

Create trigger trigger name (after | before) event (update | delete | insert) on needs to set the table name of the trigger for each row (set to row-level trigger) begin a set of sqlend

Example:

Delimiter / /;-- create a trigger create trigger tri_testafter delete-- set to row-level trigger on book for each rowbegininsert into book values (old.id,' tragic data', 'zzy'); end;// Note: there are two objects in the trigger: old and new,old represent the original data record when the data is deleted, and new indicates the new data record when the data is modified and inserted. 3. The design of MySQL table (1) the three paradigms of database:

  -1NF: all fields are atomic and indivisible.

  -2NF: non-primary key fields must be related to the primary key (each table describes only one class of things), not part of the primary key (valid when federated with primary keys)

  -3NF: non-primary key fields must be related to the primary key (each table describes only one class of things), not part of the primary key (valid when federated with primary keys)

(2) the relationship of the table:

One to one correspondence

# take CREATE TABLE and × × as an example: CREATE TABLE `t _ people` (`id` int (11) NOT NULL, `name` varchar (50) DEFAULT NULL, `age` int (11) DEFAULT NULL, PRIMARY KEY (`id`)) × × × Table: create table t_idcard (card_number varchar (18) primary key,create_date date,p_id int unique,foreign key (p_id) REFERENCES t_people (id)) Note: design method: find a way to make foreign key fields have unique constraints at the same time Foreign key fields can be found in any table

One-to-many:

Take the department and employee table as an example: create table t_emp (eid int PRIMARY KEY,ename varchar (50) not null,job varchar (50), deptno int, foreign key (deptno) REFERENCES t_dept (deptno)) Department Table: create table t_dept (deptno int primary key,deptname varchar (50)) Note: design method: just add a foreign key constraint to the multiple table

Many to many:

Design method: need to find an intermediate table and transform it into two one-to-many relationships

(3) Database optimization: SQL optimization generally does not use * when querying records, because it generally uses (*), he will convert * to column names, and then use not null / null to search the index in the query (time-consuming), which will lead to index failure using functions in index columns, calculation in index columns, and index failure index columns do not use not |! = | avoid using or as far as possible. Using like in union index columns will also lead to index invalidation. Choose exists to execute the main query first. If the main query is filtered more, use existsin to execute the subquery first. If the subquery has more filtering, use in.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.