MySQL Command sorting

MySQL Command sorting

A total of 39 commands, brilliant

1. display the list of databases on the current database server:
Mysql> show databases;
Note: the MySQL database contains the MySQL system information. We change the password and add new users to use this database for operations.
2. display data tables in the database:
Mysql> Use Database Name;
Mysql> show tables;
3. display the data table structure:
Mysql> describe table name;
4. Create a database:
Mysql> Create Database database name;
5. Create a data table:
Mysql> Use Database Name;
Mysql> Create Table Name (field name varchar (20), field name char (1 ));
6. delete a database:
Mysql> drop database database name;
7. delete a data table:
Mysql> drop table name;
8. Clear records in the table:
Mysql> Delete from table name;
9. display the records in the table:
Mysql> select * from table name;
10. insert records into the table:
Mysql> insert into table name values ("hyq", "M ");
11. Update table data:
Mysql-> Update table name: Set field name: 1 = 'a'; field name: 2 = 'B' where field name: 3 = 'C ';
12. load data into a data table in text mode:
Mysql> load data local infile "D:/mysql.txt" into Table table name;
13. Import the. SQL FILE command:
Mysql> Use Database Name;
Mysql> source D:/MySQL. SQL;
Mysql> flush privileges;
15. display the Database Name of use:
Mysql> select database ();
16. display the current user:
Mysql> Select User ();
17. Use G to display results vertically by line
Select * from user \ G;
18. Create a user:
Create user 'hahaha' @ '%' identified by '201312 ';
19. Create a database:
Create Database if not exists 'hahaha ';
20. grant permissions:
Grant all privileges on 'hahaha'. * To 'hahaha' @ '% ';
21. Refresh permission:
Flush privileges;
22. revoke permissions:
Revoke all on *. * From 'e4200003458 '@' % ';
23. Change the root password on the command line:
Mysql> Update mysql. User SET Password = PASSWORD ('new password') where user = 'root ';
24. Insert table example:
Create Table teacher (
Id int (5) auto_increment not null primary key,
'Name' varchar (20) default 'hello ',
Year date
);
25. insert data
Insert into teacher values (", 'allen ', 'dalian Zhongyi', '2017-10-10 ′);
Insert into teacher values (", 'jack', 'dalian No. 2 middle school ', '2017-12-23 ′);

26. Export a table
Mysqldump-u username-P Database Name> exported file name
Mysqldump-u user_name-p123456 database_name> outfile_name. SQL
27. Export a table
Mysqldump-u user name-P database name Table Name> exported file name
Mysqldump-u user_name-P database_name table_name> outfile_name. SQL
28. Export a database structure
Mysqldump-u user_name-p-D-add-drop-Table database_name> outfile_name. SQL
-D no data-add-drop-table add a drop table before each create statement
29. Export with language Parameters
Mysqldump-uroot-p-default-character-set = Latin1-set-charset = GBK-Skip-opt database_name> outfile_name. SQL
30. Change the table name:
Alter table table_name rename new_t;
31. Add a column:
Alter table table_name add column C_n column attributes;
32. Delete columns:
Alter table table_name drop column C_n;
33. Create an index:
Alter table c_table add index (c_n1, c_n2 );
Alter table c_table add unique index_name (C_n );
Alter table c_table add primary key (SID );
34. delete an index:
Alter table c_table drop index c_n1;
35. Modify the column information:
Alter table t_table change C_1 C_1 varchar (200 );
Alter table t_table modify 1 C_1 varchar (200 );
36. Insert statement:
Insert into table_name (C_1, C_2)
Values ('x1', 1 );
37. Update statement:
Update table_name set c_1 = 1 where c_2 = 3;
38. delete a database or table:
Drop table table_name;
Drop database database_name; // data that can be deleted using mysql_drop_db.

39. Crack the local MySQL user name and password

Windows:
1. log on to the system as a system administrator.
2. Stop MySQL services.
3. Go to the Command window and enter the MySQL installation directory. For example, if my installation directory is c: \ mysql, go to c: \ mysql \ bin.
4. Skip the permission check and start MySQL,
C: \ mysql \ bin> mysqld-NT -- skip-grant-tables
5. open a new window, go to the C: \ mysql \ bin directory, and set the new root password.
C: \ mysql \ bin> mysqladmin-u root flush-Privileges password "newpassword"
C: \ mysql \ bin> mysqladmin-u root-P Shutdown
Replace newpassword with the root password you want to use. The second command will prompt you to enter a new password and repeat the password entered by the first command.
6. Stop MySQL server and start MySql in Normal Mode
7. You can use a new password to link to MySQL.

UNIX & Linux:
1. log on to the system using root or a user running mysqld;
2. Use the kill command to end the mysqld process;
3. Use the -- skip-grant-tables parameter to start MySQL Server
Shell> mysqld_safe -- skip-grant-tables &
4. Set a new password for root @ localhost
Shell> mysqladmin-u root flush-Privileges password "newpassword" 5. Restart MySQL Server