Shulou(Shulou.com)06/01 Report--

This article mainly explains "what are the common commands of mysql under ubuntu". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the common commands of mysql under ubuntu.

I. mysql service operation

0. Check the database version sql- > status

1. Net start mysql / / start the mysql service

2. Net stop mysql / / stop mysql service

3. Mysql-h host address-u user name-p user password / / enter mysql database

4. Quit / / exit mysql operation

5. Mysqladmin-u username-p old password password new password / / change password

6. Grant select on database. * to user name @ login host identified by "password" / / add new users

Exemple:

Example 2, add a user's test2 password to abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that even if the user knows the test2 password, he can not access the database directly from the internet, but only through the web page on the MYSQL host.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grant select,insert,update,delete on mydb.* to test2@localhost identified by ""

II. Database operation

1. Show databases; / / list databases

2. Use database_name / / use database_name database

3. Create database data_name / / create a database named data_name

4. Drop database data_name / / Delete a database named data_name

III. Table operation

1. Show databases;// lists all databases

Use database name; / / arrive at a database

Show tables / / list all tables

Create table tab_name (

Id int (10) not null auto_increment primary key

Name varchar (40)

Pwd varchar (40)

) charset=gb2312; creates a new table called tab_name

2. Drop table tab_name deletes the data table named tab_name

3. Describe tab_name / / displays the data structure of the table named tab_name

4. Show columns from tab_name / / ditto

5. Delete from tab_name / / empty the records in the table tab_name

6. Select * from tab_name / / displays the records in the table tab_name

7. Mysqldump-uUSER-pPASSWORD-- no-data DATABASE TABLE > table.sql / / copy the table structure

Fourth, modify the table structure

1. ALTER TABLE tab_name ADD PRIMARY KEY (col_name)

Description: change the definition of the table to set a field as the primary key.

2. ALTER TABLE tab_name DROP PRIMARY KEY (col_name)

Description: delete the definition of the primary key

3. Alter table tab_name add col_name varchar (20); / / add a field named col_name to the tab_name table with the type varchar (20)

4. Alter table tab_name drop col_name / / delete the col_name field in tab_name

5. Alter table tab_name modify col_name varchar (40) not null / / modify field attributes. Note that if not null is added, there is no data under the original field.

It is written under SQL Server200 as follows: Alter Table table_name Alter Column col_name varchar (30) not null

6. How to modify the table name: alter table tab_name rename to new_tab_name

7. How to modify the field name: alter table tab_name change old_col new_col varchar (40); / / attributes such as data type must be specified for the current field, otherwise it cannot be modified.

8. Create table new_tab_name like old_tab_name / / creates a new table with an existing table, but does not contain data from the old table

V. backup and recovery of data

Import external data text:

1. Execute external sql scripts

Execute on the current database: mysql

School.bbb

Mysqldump-u [user]-p [password] databasename > filename (backup)

Mysql-u [user]-p [password] databasename < filename (restore)

VI. Unloading

Uninstall mysql:sudo apt-get remove mysql-server mysql-client

Sudo apt-get autoremove

At this point, I believe you have a deeper understanding of "what are the common commands of mysql under ubuntu?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.