Basic MySQL database operation commands

1. 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 the mysql database

4. quit // exit mysql

5. mysqladmin-u username-p old password new password // Change password

6. grant select on database. * to username @ login host identified by "password" // Add new user

Exemple:

Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also 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 the user knows the password of test2, he cannot access the database directly from the internet, but can only access the database through the web pages on the MYSQL host.

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

If you do not want test2 to have a password, you can run another command to remove the password.

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

Ii. Database Operations

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 operations

1. show databases; // list all databases

Use Database Name; // enter 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; create a new table named tab_name

2. drop table tab_name delete a data table named tab_name

3. describe tab_name // display the data structure of the table named tab_name

4. show columns from tab_name // same as above

5. delete from tab_name // clear records in Table tab_name

6. select * from tab_name // displays records in tab_name.

7. mysqldump-uUSER-pPASSWORD -- no-data database table> table. SQL // copy the TABLE structure

4. Modify the table structure

1. alter table tab_name add primary key (col_name)

Note: You can change the table definition to set a column as a primary key.

2. alter table tab_name drop primary key (col_name)

Delete the definition of the primary key

3. alter table tab_name add col_name varchar (20); // add a field named col_name in the tab_name table and its type is 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 the field attribute. Note that if not null is added, no data under the original field is required.

The statement in SQL Server200 is: 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); // you must specify the data type and other attributes for the current field; otherwise, the field cannot be modified.

8. create table new_tab_name like old_tab_name // use an existing table to create a new table, but it does not contain the data of the old table.

V. data backup and recovery

Import external data text:

1. Execute External SQL scripts

Run mysql <input. SQL on the current database.

Run the following command on the specified database: mysql [Table name] <input. SQL

2. data Import command load data local infile "[file name]" into table [table name];

Back up the database: (under dos)

Mysqldump -- opt school> school. bbb

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

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

6. Uninstall

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

Sudo apt-get autoremove