Common MySQL database commands

Secrets 1. MySQL common command createdatabasename; Create Database usedatabasename; select database dropdatabasename to delete database directly, no reminder

Http://news.newhua.com/news1/program_database/2009/217/0921715343537K7H7IDI2CCI 09JCI1DK8FJ4B07B3A04219G 561C3JAB.html 1, MySQL Common commands create database name; create database use databasename; choose database drop database name to delete the database directly, Do not remind

Http://news.newhua.com/news1/program_database/2009/217/0921715343537K7H7IDI2CCI09JCI1DK8FJ4B07B3A04219G561C3JAB.html

1. MySQL Common commands

Create database name; create a database

Use databasename; select database

Drop database name directly deletes the database, no reminder

Show tables; displays tables

Describe tablename; Detailed description of the table

Add distinct to the select statement to remove duplicate fields.

Before mysqladmin drop databasename deletes a database, a message is displayed.

Display current mysql version and current date

Select version (), current_date;

2. Modify the root password in mysql:

Shell> mysql-u root-p

Mysql> update user set password = password ("xueok654123") where user = 'root ';

Mysql> flush privileges // refresh the database

Mysql> use dbname; open the database:

Mysql> show databases; displays all databases

Mysql> show tables; display all tables in mysql: use mysql first; then

Mysql> describe user; displays the column information of the user table in the mysql database );

3. grant

Create a full Super User that can connect to the server from anywhere, but you must use a password something to do this

Mysql> grant all privileges on *. * to user @ localhost identified by 'something'

Add new users

Format: grant select on database. * to username @ login host identified by "password"

Grant all privileges on *. * TO monty @ localhost identified by 'something' with grant option;

Grant all privileges on *. * TO monty @ "%" identified by 'something' with grant option;

Delete authorization:

Mysql> revoke all privileges on *. * from root @ "% ";

Mysql> delete from user where user = "root" and host = "% ";

Mysql> flush privileges;

Create a User custom to log on to it363.com on a specific client and access the specific database fangchandb.

Mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom @ it363.com identified by 'passwd'

Rename a table:

Mysql> alter table t1 rename t2;

4. mysqldump

Back up database

Shell> mysqldump-h host-u root-p dbname> dbname_backup. SQL

Restore database

Shell> mysqladmin-h myhost-u root-p create dbname

Shell> mysqldump-h host-u root-p dbname <dbname_backup. SQL

If you only want to unload the table creation command, the command is as follows:

Shell> mysqladmin-u root-p-d databasename> a. SQL

If you only want to unload the SQL command for inserting data without the table creation command, the command is as follows:

Shell> mysqladmin-u root-p-t databasename> a. SQL

What should I do if I only want data and do not want any SQL commands?

Mysqldump-T./phptest driver

Only when the-T parameter is specified can the plain text file be detached, indicating the directory where the data is detached and./indicates the current directory, that is, the same directory as mysqldump. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is a. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands.

5. You can store the query in a file and tell mysql to read the query from the file instead of waiting for keyboard input. You can use the shell to type the redirection utility to do this. For example, if the file my_file. SQL contains a query

Query:

For example, if you want to write a table prefix in SQL .txt:

Mysql> mysql-h myhost-u root-p database <SQL .txt

6. select * from tablename where id = + id + order by time asc;

AscSort in ascending order

Desc in descending order

Mysql basic commands

I also use mysql from time to time, so I can write down these basic commands for later searching.

1. Enter

   $ Mysql-u username-p

2. Create a database

   Mysql> create database database_name;

3. Delete Database

   Mysql> drop database database_name;

4. Show all databases

   Mysql> show databases;

5. Database Operations

   Mysql> USE database_name;

6. Create a table

   Mysql> create table table_name
   (
   ID int (7) not null AUTO_INCREMENT,
   Name varchar (50 ),
   Time varchar (20 ),
   Email varchar (50 ),
   Text varchar (1000 ),
   UNIQUE ('id ')
   );

7. Delete table

   Mysql> drop table table_name;

8. Show all tables

   Mysql> show tables;

9. Show all contents in a table

   Mysql> SELECT * FROM table_name
   

   -> Order by id desc; # sort BY "ID" in reverse ORDER

10. Search table content with keywords

    Mysql> SELECT * FROM table_name
    

    -> WHERE Name = 'string'; # exact search

    -> WHERE Name like '% string %'; # Fuzzy search

11. Delete a record in the table

    Mysql> delete from table_name
    

    -> WHERE Name = 'string'; # delete all entries with Name = 'string' In the table

12. Edit a record in the table

    Mysql> UPDATE table_name
    

    -> SET Name = '$ name', Time =' $ time', Email = '$ email', Text = '$ text'

    -> Where id = '$ id'; # content of each entry whose id is changed to' $ id'

13. Insert a new record into the table

    Mysql> insert into table_name (Name, Time, Email, Text)
    

    -> VALUES

    -> ('$ Name',' $ time', '$ email', '$ text ');

14. Copy the content of one table to another.

    Mysql> insert into database_name.table_name1 SELECT * FROM database_name.table_name2

15. Alter TABLE structure: alter command

    # Rename a table

    Mysql> alter table table_name RENAME table_name_new;
    
    

    # Deleting Columns

    Mysql> alter table table_name DROP column_name;
    
    

    # Adding columns

    Mysql> alter table table_name ADD column_name varchar (20 );
    Mysql> alter table table_name ADD column_name tinyint not null default '1 ';
    
    

    # Changing the column name and type

    Mysql> alter table table_name CHANGE column_name column_name_new new_type;
    Mysql> alter table table_name CHANGE column_name column_name_new tinyint not null default '1 ';
    

16. Create a mysql user

    # Create a user donkey with all permissions, but only connect to the database from localhost

    Mysql> grant all privileges on *. * TO 'donkey' @ 'localhost'
    

    -> Identified by 'your _ password' with grant option;

    
    

    # Create a user donkeytail with all permissions and can connect to the database from any host

    Mysql> grant all privileges on *. * TO 'donkeytail' @ '%'
    

    -> Identified by 'your _ password' with grant option;

    
    

    # Create a user admin and be granted reload and process management permissions. These permissions allow the admin to execute the mysqladmin reload, mysqladmin refresh, mysqladmin flush-xxx commands, and mysqladmin processlist.

    Mysql> grant reload, process on *. * TO 'admin' @ 'localhost ';
    
    

    # Create a user dummy without any permissions. However, you can use the grant statement to grant permissions.

    Mysql> grant usage on *. * TO 'dummy' @ 'localhost ';
    

17. Delete a user

    Mysql> drop user username @ localhost;

18. Change mysql user password

    # Use the mysql command to change the User Password

    $ Mysql-u root-p
    Mysql> USE mysql;
    Mysql> UPDATE user
    

    -> SET password = 'new _ password' WHERE User = 'username ';

    Mysql> flush privileges; # reload authorization table
    
    

    # Use the mysqladmin command to change the User Password

    $ Mysqladmin-u root-p password NEWPASSWORD # prompt to enter the old password and the new password will take effect

19. Back up database

    # Backing up a single database

    $ Mysqldump-u root-p database_name> backup. SQL
    $ Mysqldump -- add-drop-table-u root-p database_name> backup. SQL
    
    

    # Backing up a table

    $ Mysqldump-u root-p database_name table1 table2> backup. SQL
    
    

    # Backing up multiple databases

    $ Mysqldump-u root-p -- all-databases | bzip2-c> alldatabases. SQL .bz2
    $ Mysqldump-u root-p -- databases database_1 database_2> multibackup. SQL
    

20. Restore database backup

    $ Mysql-u [username]-p [database_to_restore] <[backupfile]
    $ Bzip-d <backupfile.bz2 | mysql-u [username]-p [database_to_restroe]

21. Copy the database to another machine

    $ Mysqladmin-h 'other _ hostname' CREATE db_name # CREATE a database on the target machine
    $ Mysqldump-u root-p db_name | mysql-h 'other _ hostname' database_name