Database outfile backup and restore view things trigger MySQL functions and custom functions

outfile exporting data from a database

SELECT * into outfile ' e://mysqloutfile//1.txt ' from table name;

Backup and restore

No longer backup in MySQL directory, mysqldump-uroot-p database name + table name > Specific path name (where do you want to import)

If you want to get more than one table, then add a table behind the table.

Restores:

Remove everything from the database first

If not, then there is no delete clean

Create a new database with the database

Find file source + specific file; Import data into

View:

What is a view?

The view is actually nonexistent is a SQL statement when queried, this statement executes, draws a result, and then uses a result to query

Create a View

CREATE VIEW name as SELECT * from table name;

SELECT * from view name;

Change the view structure

ALTER VIEW name (what you want to change s1,s2) SELECT * from table name;

S1,s2 to replace Fields

Things:

What is a thing?

A thing is a collection of SQL, and if one of the statements in one of the SQL collections fails, it means that the whole process is meaningless and should be the database back to its original state.

See Show variables like '%autocommit% ' first

View the value of Autocommit if Autocommit is open, then close.

Set autocommit=0; close things.

Create two tables

CREATE TABLE Stu_mon (

ID int primary KEY auto_increment,

Stu_money Decimal (10,2)

) engine InnoDB character set UTF8;

CREATE TABLE Cla_mon (

ID int primary KEY auto_increment,

Cla_money Deciaml (10,2),

Stu_count int

) engine InnoDB character set UTF8;

Changing data in a database

Update Stu_mon set Stu_money =stu_money-50 where id=1;

Update Cla_mon set cla_money=cla_money+50 where id=1;

Open another window if changing the data will not change the value in this window because changing the value of autocommit to 0 will not immediately change the value of the database.

You ever want to change the value of the window then you have to commit, then change the value in the database.

Commit

If you change the value, but wrong, so you need to roll back;

Rollback

Start transaction

This instruction is to not set the value of Autocommit

First set the value of autocommit to 1;

When the beginning is open, open is open, when the operation of the thing is closed, when you finish the thing, the food will be opened back to the original state

Trigger:

On the current table, set a listener event for each row of the table, and execute a SQL statement whenever the event occurs

Create a Trigger

Create trigger Trigger name + event (e.g. after update) on the table you want to manipulate Stu_mon

For each row

Update another table Cla_mon set cla_money=cla_money+10 where id=1;

If you change the value of the Stu_mon, the value that is not Cla_mon will change.

Details of the use of triggers:

Cannot have the same name;

Set a trigger for a class of events;

New old inside the trigger program

A concrete example

Delete the previous trigger

The name of the drop trigger trigger

Create trigger Trigger name + Event +on+ The table you want to manipulate

For each row

Update another table set cla_money=cla_money+ (New.stu_money-old.stu_money) where id=1;

More than one SQL statement

Delete the previous trigger

Drop trigger used trigger name

Decimal $$//Terminator

Create trigger Chufaqi after update on Stu_mon

For each row

Begin

Update Cla_mon set cla_money=cla_money+ (New.stu_money-old.stu_money) where id=1;

Update Cla_mon set stu_count=stu_count+1 where id=1;

End

$$//Terminator avoids a semicolon conflict

When you insert a data, you change the values in the CLA table.

If you want to manipulate the Insert event

But insert cannot use old data

Delete cannot use the new data

Decimal//

Create trigger Chufaqi after insert on Stu_mon

For each row

Begin

Update Cla_mon set stu_count=stu_count+new.id;

Update Cla_mon set cla_money=cla_money+50;

End

//

INSERT into Stu_mon values (3,5000);

Then the new.id=3 in the trigger

MySQL function

Note: #--[space]

SELECT * from Stu_mon #where id=1;

;//Terminator

Find out.

Decimal to modify the end character of the statement.

Variables: Setting variables

1.select @who: = ' AA ';

2.select * from Stu_mon to where @who = ' AA ';

Variables must be added @

View variables

Select @who;

Built-in functions

Random number between rand () 0-1

Format (X,D)

X for the data you want to format d you want to be accurate to several numbers

Unix_timestamp (); Get the number of seconds

SUBSTRING (specific data, 2,2) get two clockwise

The database is starting from 1.

Encryption method:

MD5 (you want to encrypt)

SHA1 ()

Password ()

Custom functions

Decliniter//

Database outfile backup and restore view things trigger MySQL functions and custom functions