MySQL common command backup table

1. Connect to the database:
Enter the MySQL installation path bin, for example, C:> Cd C: Program filesmysqlmysql server 5.0bin.
Enter the user name and password: C: Program filesmysqlmysql server 5.0bin> mysql-uroot-p123456
2. log out of MySQL
Mysql> exit
3. Change the password:
C: Program filesmysqlmysql server 5.0bin> mysqladmin-uroot-p123456 password 456123
4. Add users:
Add a user named "test1" with the password "ABC" so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL,
Mysql> grant select, insert, update, delete on *. * To test1 @ "%" identified by "ABC ";
Add a user named Test2 "ABC" so that the user can only log on to localhost and query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located. In this way, the user knows the password of Test2 and cannot directly access the database from the Internet. The user can only access the database through the web page on the MySQL host.
Mysql> grant select, insert, update, delete on mydb. *
Test2 @ localhost identified by "ABC ";
Add a full super user who can connect to the server from anywhere
Mysql> grant all privileges on *. * To test3 @ "%" identified by 'abc' with grant option;
5. Delete authorization (corresponding to the above authorization)
Mysql> revoke select, insert, update, delete on *. * From test1 @ "% ";
Mysql> revoke select, insert, update, delete on mydb. * from
Test2 @ localhost;
Mysql> revoke all privileges on *. * From test3 @ "% ";
6. display the database
Mysql> show databases;
7. display tables in the database (exam database name)
Mysql> Use exam;
Mysql> show tables;
8. display the table structure (db_testtemp table name)
Mysql> describe db_testtemp;
9. Create a database
Mysql> Create Database database name;
10. Create a table
Mysql> use test;
Mysql> Create Table teacher (
Id int (3) auto_increment not null primary key,
Name char (10) Not null,
Address varchar (50) default 'beijing ',
Year date
);
Or
School. SQL content
Use exam;
Create Table teacher (
Id int (3) auto_increment not null primary key,
Name char (10) Not null,
Address varchar (50) default 'beijing ',
Year date
);
Put the school. SQL file under C: And enter the directory c:> Cd C: Program filesmysqlmysql server 5.0bin in DOS status.
Then C: Program filesmysqlmysql server 5.0bin> mysql-uroot-p456123 <C: School. SQL
If it succeeds, no display is displayed for a blank row. If there is an error, a prompt is displayed.
11. delete a database
Mysql> drop database test;
And Delete tables
Mysql> Use exam;
Mysql> drop table teacher;
14. rename a table
Mysql> alter table teacher rename student;
15. Back up the database (the generated exam. SQL is stored in the directory c: Program filesmysqlmysql server 5.0bin)
C: Program filesmysqlmysql server 5.0bin> mysqldump-hlocalhost-uroot-pncae2010 exam> exam. SQL
16. Restore the database (localhost cannot be replaced by the local IP address)
C: Program filesmysqlmysql server 5.0bin> mysql-hlocalhost-uroot-pncae2010 exam <exam. SQL
17. Copy the database (back up all the databases to the all-databases. SQL file under the directory c: Program filesmysqlmysql server 5.0bin)
C: Program filesmysqlmysql server 5.0bin> mysqldump-hlocalhost-uroot-pncae2010 -- all-databases> all-databases. SQL
18. Backup table (the generated student. SQL is stored in the directory c: Program filesmysqlmysql server 5.0bin)
C: Program filesmysqlmysql server 5.0bin> mysqldump-hlocalhost-uroot-pncae2010 exam student> Student. SQL
19. Restore the table (delete the original table before the operation)
C: Program filesmysqlmysql server 5.0bin> mysql-H (IP)-uroot-P (password) databasename tablename <tablename. SQL
There are still some unpracticed
20. To change column A from integer to tinyint not null (same name ),
Change Column B from Char (10) To char (20), rename it, and change from B to C:
Alter table T2 modify a tinyint not null, change B C char (20 );
Add a new timestamp column named D:
Alter table T2 add D timestamp;
Add an index on column D and set column A as the primary key:
Alter table T2 add index (d), add primary key ();
Delete column C:
Alter table T2 drop column C;
Add a new auto_increment integer column named C:
Alter table T2 add c int unsigned not null auto_increment, add index (C );
Note that we have indexed C, because the auto_increment column must be indexed, and we declare C as not null,
Because the indexed Column cannot be null.