About MySQL transactions, views, indexes, database backup, recovery

1. /* Create a database */

CREATE DATABASE ' MYbank ';
/* CREATE TABLE */
Use MYbank;
CREATE TABLE ' Bank ' (
' CustomerName ' CHAR (Ten), #用户名
' Currentmoney ' DECIMAL (10,2) #当前余额
);
/* Insert Data */
INSERT into ' Bank ' (' customerName ', ' Currentmoney ') VALUES (' Zhang San ', 1000);
INSERT into ' Bank ' (' customerName ', ' Currentmoney ') VALUES (' John Doe ', 1);

2.---Transfer test: Zhang San hope to send money directly to Lee 4,500 yuan by transfer

UPDATE ' Bank ' SET ' currentmoney ' = ' Currentmoney '-500
WHERE ' customerName ' = ' Zhang San ';
UPDATE ' Bank ' SET ' currentmoney ' = ' Currentmoney ' +500
WHERE ' customerName ' = ' John Doe ';

3. Executing the transaction and committing the transaction

Use MYbank;

/* Set result set to display in GBK encoded format */
SET NAMES GBK;

/* Start Transaction */
BEGIN;
UPDATE ' Bank ' SET ' currentmoney ' = ' Currentmoney '-500
WHERE ' customerName ' = ' Zhang San ';
UPDATE ' Bank ' SET ' currentmoney ' = ' Currentmoney ' +500
WHERE ' customerName ' = ' John Doe ';
COMMIT; #提交事务, writing to the hard disk

4. Start a transaction ROLLBACK TRANSACTION
BEGIN;
UPDATE ' Bank ' SET ' currentmoney ' = ' Currentmoney ' -1000 WHERE ' customerName ' = ' Zhang San ';
/* Roll Back */
ROLLBACK;

5. Set auto-commit to close or turn on

/* Turn off transaction autocommit */
SET autocommit = 0;

/* Transfer * *
UPDATE ' Bank ' SET ' currentmoney ' = ' Currentmoney '-500
WHERE ' customerName ' = ' Zhang San ';
UPDATE ' Bank ' SET ' currentmoney ' = ' Currentmoney ' +500
WHERE ' customerName ' = ' John Doe ';
/* Submit */
COMMIT;
UPDATE ' Bank ' SET ' currentmoney ' = ' Currentmoney ' -1000 WHERE ' customerName ' = ' Zhang San ';

/* Roll back the transaction */
ROLLBACK;

6. Create a View

/* Current Database */
Use MySchool;

DROP VIEW IF EXISTS ' View_student_result ';

/* CREATE VIEW */
CREATE VIEW ' View_student_result '
As

/* You need to create the data for the view */
SELECT ' Studentname ' as name, student ' Studentno ' study number, ' Studentresult ' as grade, ' Subjectname ' as course name. ' Examdate ' as test date
From ' Student '
INNER JOIN ' result ' on ' student '. ' Studentno ' = ' result '. ' Studentno '
INNER JOIN ' subject ' on ' result '. ' Subjectno ' = ' subject '. ' Studentno '
WHERE ' subject '. ' Subjectno ' = (
SELECT ' Subjectno ' from ' Subject ' WHERE ' subjectname ' = ' Logic Java '
) and ' examdate ' = (
SELECT MAX (' examdate ') from ' result '. ' Subject ' WHERE ' result '. ' Subjectno ' = ' subject '. ' Subjectno '
and ' subjectname ' = ' Logic Java '
);

7. Create an index

Use MySchool;

/* Create an index */
CREATE INDEX ' Index_student_studentname '
On ' Student ' (' studentname ');

8. Backing up a database with DOS commands

Mysqldump-u username-h-ppassword

dbname[tbname1 [, Tbname2] ...] > filename.sql

Cases:

Mysqldump-u root-p MySchool Student > D:\backup\myschool.sql

Enterpassword:

9. Restore the database using the Post DOS command

1). Create a new database in the MySQL server before recovering the database.

2). Execute mysql-u username-p [dbname] <filename.sql

Cases:

Mysql-u Root-p Schooldb < D:\backup\myschool.sql

10. Recovering a database using the source command

Syntax: source filename;

Cases:

/* Create a database */
CREATE DATABASE myschoolDB2;
Use myschoolDB2;
/* Recover 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 ';

Cases:

SELECT * from ' result ' WHERE ' subjectno ' =

(SELECT ' Subjectno ' from ' Subject ' WHERE ' subjectname ' = ' Logic Java ')

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

12. Importing a text file into a datasheet

Syntax: LOAD DATA INFILE ' filename ' into TABLE ' tablename ';

1). Create a table structure that imports data first

Use myschoolDB2;
/* Create result table structure */
CREATE TABLE ' result '
(
' Studentno ' INT (4) Not NULL COMMENT ' study number ',
' Subjectno ' INT (4) Not NULL COMMENT ' course number ',
' Examdate ' DATETIME not NULL COMMENT ' test Date ',
' Studentresult ' INT (4) Not NULL COMMENT ' exam results ',
PRIMARY KEY (' Studentno ', ' subjectno ', ' examdate ')
) ENGINE = INNODB DEFAULT CHARSET UTF8;

2). Import data
LOAD DATA INFILE ' path. txt ' into TABLE result;
/* View Result table data */
SELECT * from result;

About MySQL transactions, views, indexes, database backup, recovery