MySQL: database entry 4, mysql database entry

MySQL: database entry 4, mysql database entry

1. View
Create View
Create view name as query SQL statement;

Drop view name;

Alter view name as query SQL statement;
2. triggers
1. Insert an event trigger
Insert into order_table (gid, much) VALUES (1, 3 );

-- Update goods set num = num-3 where id = 1;

Create trigger tg1 after insert on order_table
For EACH row -- fixed syntax
BEGIN
Update goods set num = num-new. much where id = new. gid;
END

-- Delete a trigger
Drop TRIGGER TG1;

2. Update event triggers
Update order_table set much = much + 2 where oid = 6;

Update goods set num = num + 2 where id = 1;

Create TRIGGER tg2 after update on order_table
For EACH ROW
BEGIN
Update goods set num = num + old. much-new. much where id = old. gid;
END
3. Delete event triggers
Delete from order_table where oid = 6;

Update goods set num = num + 3 where id = 1;

Create TRIGGER tg3 after delete on order_table
For EACH ROW
BEGIN
Update goods set num = num + old. much where id = old. gid;
END
4. view the trigger
Show tiggers;

3. Stored Procedure
1. Encapsulation
-- Create procedure p1 ()
-- BEGIN
-- INSERT into goods VALUES (null, 'Korean refer', 50 );
-- Select * from goods;
-- END
--

Call p1 ();
2. Parameters
-- In out inout
Create procedure p3 (in I int, inout names varchar (50 ))
BEGIN
Update goods set name = names where id = I;
END

Set @ names = 'Go ';

Call p2 (4, @ names );

Select @ names;
Into usage
Set @ I = 0;
Set @ n = '';
Select num into @ I from goods where id = 1;

Select @ I;
3. Judgment
Create procedure p3 (in flag char (5), in nums int)
BEGIN
If flag = 'true' then
SELECT * from goods where num <nums;
ELSEIF flag = 'false' THEN
SELECT * FROM goods where num> nums;
ELSE
SELECT * FROM goods;
END if;

END
Call p3 ('false', 20 );
4. Loop
-- Sum of 1-100
Create PROCEDURE p4 (in n int, out he int)
BEGIN
Declare I int DEFAULT 0;
DECLARE sum int;
Set sum = 0;
While I <= n DO
Set sum = sum + I;
Set I = I + 1;

End WHILE;
Set he = sum;
End

Set @ he = 0;

Call p4 (100, @ he );

Select @ he;
View stored procedures
Show PROCEDURE status;
Delete stored procedure
Drop PROCEDURE p1;

4. Functions
Create FUNCTION f1 (x int, y int)
RETURNS INT

BEGIN
Declare sum int DEFAULT 0;
Set sum = x + y;
RETURN (sum );
END

Select f1 (100,2 );

Select g. *, f1 (100, num) FROM goods g;

Drop function f1;

5. Transactions
What is things
Batch execution of a group of SQL statements is either successful or failed.

Four features of things:
Atomicity: either all or none of its data modifications are performed.
Consistency: What kind of constraints does the database have in the past? After the transaction is executed, such constraints still need to exist. All rules must be applied to transaction modifications to maintain the integrity of all data.
Isolation: one transaction cannot know the execution of another transaction (intermediate state ). so when the same data is accessed by multiple things, one of them gets the data access modification, and the other things are blocked until the data is released.
Durability: this will remain even if a fatal system failure occurs. Don't tell me that the commit (commit) is successful. Call back to tell me that the server room is powered off, and the data changes involved in my transaction may not enter the database.
Start TRANSACTION; -- enable the TRANSACTION and disable the mysql automatic submission method.
SAVEPOINT sa1;

Update account set money = money-1000 where id = 4;

SAVEPOINT sa1;

Update account set money = money + 1000 where id = 3;

-- COMMIT; -- submit the current transaction
Select * from account;

ROLLBACK to sa1; -- roll back the current transaction

6. Lock
When concurrent transactions access a resource at the same time, data inconsistency may occur. Therefore, a mechanism is required to sequentially access data to ensure database data consistency.

7. Database Backup
Backup:
Mysqldump-uroot-p123456 database name table> storage location.
Import:
Mysql> USE Database Name;
Mysql> source backup file. SQL;