MySQL basic commands

[Switch] MySQL basic command 1. mysql service operation 0. view the database version SQL-status; 1. netstartmysql starts mysql Service 2. netstopmysql stops mysql service 3. mysql-h host address-u user name-p User Password enters mysql database 4. quit exits mysql operation 5. mysqladmin-u user

[Switch] MySQL basic command 1. mysql service operation 0. view the database version SQL-status; 1. net start mysql // start 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 user

[Go to] basic MySQL commands

I. mysql service operations
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 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

?

1. start MySQL on the terminal:/etc/init. d/mysql start; (stop, restart .)
2. log on to MySQL: mysql-uroot-p (Log On with the root account) and enter the password;
3. View All Database names: show databases;
4. Select a database operation: use database_name;
5. view all the table names in the current database: show tables;
6. create a database: create database database_name;
7. delete a database: drop database database_name;
8. create a table: create table mytest (uid bigint (20) not null, uname varchar (20) not null );
9. delete a table: drop table mytest;
10. SQL insert statement: insert into table_name (col1, col2) values (value1, value2 );
11. SQL update statement: update table_name set col1 = 'value1', col2 = 'value2' where where_definition;
12. SQL query statement: select * from table_name where ...... (the most complex Statement)
13. SQL deletion statement: delete from table_name where...
14. add the table structure field: alert table table_name add column field1 date, add column field2 time...
15. Delete the table structure field: alert table table_name drop field1;
16. view the table structure: show columns from table_name;
17. Use of limit: select * from table_name limit 3; // only three lines are displayed on each page.
Select * from table_name limit 3, 4 // four results are displayed starting from the third query result.
This can be used for paging.
18. Sort the query results: select * from table_name order by field1, orderby field2; multiple sorting
19. exit MySQL: exit;

?