MySQL transaction, view, index, database backup, recovery, mysql Database Backup

MySQL transaction, view, index, database backup, recovery, mysql Database Backup

Create database 'mybank ';
/* Create a table */
USE mybank;
Create table 'bank '(
'Mermername' CHAR (10), # User Name
'Currentmoney' DECIMAL (10, 2) # current balance
);
/* Insert data */
Insert into 'bank' ('mermername', 'currentmoney') VALUES ('zhang san', 1000 );
Insert into 'bank' ('mermername', 'currentmoney') VALUES ('Li si', 1 );

 

2. -- transfer test: Zhang San hopes to directly send money to Li Si for 500 yuan through the transfer.

UPDATE 'bank' SET 'currentmoney' = 'currentmoney'-500
WHERE 'mermername' = 'zhang san ';
UPDATE 'bank' SET 'currentmoney' = 'currentmoney' + 500
WHERE 'mermername' = 'Li si ';

 

3. execute the transaction and submit the transaction

USE mybank;

/* Set the result set to display in gbk encoding format */
Set names gbk;

/* Start transaction */
BEGIN;
UPDATE 'bank' SET 'currentmoney' = 'currentmoney'-500
WHERE 'mermername' = 'zhang san ';
UPDATE 'bank' SET 'currentmoney' = 'currentmoney' + 500
WHERE 'mermername' = 'Li si ';
COMMIT; # COMMIT a transaction and write it to the hard disk

 

 

4. Start transaction rollback
BEGIN;
UPDATE 'bank' SET 'currentmoney' = 'currentmoney'-1000 WHERE 'mermername' = 'zhang san ';
/* Roll back */
ROLLBACK;

 

5. Set automatic submission to close or enable

/* Disable automatic transaction commit */
SET autocommit = 0;

/* Transfer */
UPDATE 'bank' SET 'currentmoney' = 'currentmoney'-500
WHERE 'mermername' = 'zhang san ';
UPDATE 'bank' SET 'currentmoney' = 'currentmoney' + 500
WHERE 'mermername' = 'Li si ';
/* Submit */
COMMIT;
UPDATE 'bank' SET 'currentmoney' = 'currentmoney'-1000 WHERE 'mermername' = 'zhang san ';

/* Roll back the transaction */
ROLLBACK;

 

6. Create a view

/* Current database */
USE myschool;

Drop view if exists 'view _ student_result ';

/* Create a view */
Create view 'view _ student_result'
AS

/* Data for the view to be created */
SELECT 'studentname' AS name, student. 'studentno' student ID, 'studentresult' AS score, 'subobjectname' AS course name. 'examdate' AS test date
FROM 'student'
Inner join 'result' ON 'student '. 'studentno' = 'result'. 'studentno'
Inner join 'subobject' ON 'result'. 'subjectno' = 'subobject'. 'studentno'
WHERE 'subobject'. 'subjectno' = (
SELECT 'subjectno' FROM 'subobject' WHERE 'subobjectname' = 'logic Java'
) AND 'examdate' = (
Select max ('examdate') FROM 'result'. 'subject 'where 'result'. 'subjectno' = 'subobject'. 'subjectno'
AND 'subobjectname' = 'logic Java'
);

 

7. Create an index

USE myschool;

/* Create an Index */
Create index 'index _ student_studentname'
ON 'student '('studentname ');

 

 

8. Use the DOS command to back up the database

Mysqldump-u username-h-ppassword

Dbname [tbname1 [, tbname2] ......]> filename. SQL

 

Example:

Mysqldump-u root-p myschool student> d: \ backup \ myschool. SQL

EnterPassword:

 

9. Use the DOS command to restore the database

1) create a new database on the MySQL server before restoring the database.

2) execute mysql-u username-p [dbname] <filename. SQL

 

Example:

Mysql-u root-p schoolDB <d: \ backup \ myschool. SQL

 

10. Use the source command to restore the database

Syntax: source filename;

 

Example:

/* Create a database */
Create database myschoolDB2;
USE myschoolDB2;
/* Restore the database */
Source 'd: \ backup \ myschool. SQL ';

 

11. Export table data to a text file

Syntax: select column name from table name [where condition, condition 2 ......]

Into outfile 'filename ';

 

Example:

SELECT * FROM 'result' WHERE 'subjectno' =

(SELECT 'subjectno' FROM 'subobject' WHERE 'subobjectname' = 'logic Java ')

Into outfile 'd:/backup/result.txt ';

 

 

12. Import text files to Data Tables

Syntax: load data infile 'filename' into table 'tablename ';

1) create a table structure for importing data first.

USE myschoolDB2;
/* Create the result table structure */
Create table 'result'
(
'Studentno' INT (4) not null comment 'student ID ',
'Subjectno' INT (4) not null comment' course number ',
'Examdate' datetime not null comment' test date ',
'Studentresult' INT (4) not null comment 'test result ',
Primary key ('studentno', 'subjectno', 'examdate ')
) ENGINE = innodb default charset utf8;

2). Import Data
Load data infile 'employee .txt 'into table result;
/* View the result table data */
SELECT * FROM result;