MySQL Database operations

1. Displays the list of databases.

show databases;

Just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate.

2. To display the data tables in the library:

use MySQL;//Open the library, learn FOXBASE must not be unfamiliar with it

Show tables;

3. Show the structure of the data table:

describe table name;

4. Build Library:

Create database name;

5. Build table:

Use library name;

CREATE TABLE table name (field settings list);

6. Delete the library and delete the table:

drop database name;

drop table name;

7. Empty the records in the table:

Delete from table name;

8. Show the records in the table:

SELECT * from table name;

9. Displays errors, warnings, and notifications from the last statement executed:

Show warnings;

10. Displays only the errors resulting from the last execution statement:

Show errors;

Example

Drop database if exists school; Delete if school is present

Create Database School; Building a library School

Use school; Open Library School

CREATE TABLE teacher//Create tables Teacher

(

ID int (3) auto_increment NOT null primary key,

Name Char (TEN) is not NULL,

Address varchar (+) Default ' Shenzhen ',

Year Date

); End of Build table

The following is the Insert field

Insert into teacher values (' ', ' Glchengang ', ' xx company ', ' 1976-10-10 ');

Insert into teacher values (' ', ' Jack ', ' xx company ', ' 1975-12-23 ');

Note: In the construction table:

1. Set the ID to a number field of length 3: Int (3) and let it automatically add one to each record: Auto_increment cannot be empty: NOT null and let him be the main field primary key.

2. Set name to a character field of length 10.

3. The address is set to a character field of length 50, and the default value is Shenzhen. What is the difference between varchar and char, only to wait for a later article to say.

4. Set year as the Date field.

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. You can write the above command as-is to a text file, assume that it is school.sql, then copy it to C: \ and enter directory \mysql\bin in DOS, and then type the following command.

Mysql-uroot-p Password < C:\school.sql

If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).

text go to database

1. The format in which the text data should conform: The field data is separated by the TAB key, and the null value is replaced by \ n.

Cases:

3. Rose Shenzhen II 1976-10-10

4. Mike Shenzhen one 1975-12-23

2. Data incoming command load data local infile "file name" into table name;

Note: You might want to copy the file to the \mysql\bin directory, and you must first open the database with the use command

or transfer the path (CD) to the path where you want to import the text before logging into the database.

backing up the database

(commands are executed in the DOS \mysql\bin directory)

Mysqldump--opt SCHOOL>SCHOOL.BBB

Note: Back up the database school to the school.bbb file, school.bbb is a text file, the filename is taken, open to see what you will find.

How to automatically back up MySQL database

1, first create a batch file, save the following code as a. bat file, the file name is preferably in English. Note the following path to the author's own database, for example, the database is installed under the D disk under Mysql\mysql, backup location in F:\beifen, the following code is the date [5] .

@echo off[6]

Color 0D

MODE con:cols=71 lines=25

Title MySQL database automatic backup script (Task Scheduler)--script http://www. 。 Com

Set sou_dir= "D:\mysql\Mysql\data"

Set obj_dir=f:\beifen\%date:~0,10%

net stop MySQL

MD%obj_dir%

xcopy/e/y%sou_dir%%obj_dir%

net start MySQL

@echo off&setlocal enabledelayedexpansion

call:d,30

Echo. 30 days ago Date:%d%

Echo. Delete 30 days ago Backup ...

If exist F:\beifen\%D% rd/s/q F:\beifen\%D%

Echo Automatic backup complete, the program will automatically exit .....

MySQL Database operations