MySQL Common commands

MySQL installation directory database directory/var/lib/mysql/configuration file/usr/share/MySQL (mysql.server command and configuration file) related commands/usr/bin (mysqladmin mysqldump, etc command) startup script/etc/init.d/MySQL (startup script file MySQL directory) system management connection MySQL format: MySQL-H Host Address-u user name-P User Cipher Example1: Connect to MySQL on this computer. [Email protected]:~$ Mysql-uroot-Pmysql; an example2: Connect to MYSQL on the remote host. [Email protected]:~$ mysql-h127.0.0.1-uroot-Pmysql; Modify the new password in terminal input: MySQL-U user Name-p password, enter MySQL. >Use MySQL;> Update UserSetPassword=password ('New Password')whereUser='User name';>flush privileges; #更新权限>quit; #退出增加新用户格式: GrantSelectOn database. * To User name @ login host identified by'Password'Example : Example1: Add a user test1 password to ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First use the root user to connect to MySQL, and then type the following command: MySQL>grantSelect, Insert,update,delete on * * to [e-mail protected] identified by'MySQL'; or grant all privileges on*. * to [e-mail protected] identified by'MySQL', and then refresh the permission settings. Flush privileges;2: If you do not want root to have the password operation database "MyDB" in the data table, you can call another command to erase the password. GrantSelect, Insert,update,delete on mydb.* to [email protected] identified by"'; Delete user [email protected]:~$ mysql-u User Name-p password MySQL>delete fromUserwhereUser='User name'and host='localhost'; MySQL>flush Privileges;//Delete a user's databaseMysql>drop database dbname; databases operation show all databases MySQL>show databases; (Note: Finally there is a s) to create the database MySQL>CREATE database test; connect to MySQL>use test; View the currently used database MySQL>Selectdatabase (); Table information contained in the current databases MySQL>Show tables; (Note: Finally there is a s) to delete the database MySQL>drop database test, table action Note: Use the< database name >"A database should be connected. Build Table command: Create table< table name > (< field name1> < Type1> [,.. < Field name N> < type n>]); Example: MySQL>CREATE TABLE MyClass (> IDint(4) notNULLprimary Key auto_increment,> NameChar( -) notNULL,> Sexint(4) notNULL default '0',> DegreeDouble( -,2) ; Gets the table structure command: DESC table name, or show columns fromTable name example: MySQL>describe Myclassmysql>desc Myclass;mysql> Show Columns fromMyClass; Delete Table command: Drop tables< table name >Example: Delete table with table named MyClass MySQL>drop table MyClass; Insert Data command: INSERT INTO< table name > [(< Field name1>[,.. < field name n >])] VALUES (value1) [, (value N)] Example: MySQL> INSERT into MyClass values (1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang',96.59Query table for data query all rows MySQL>Select* fromMyClass The first few rows of data, for example: View table MyClass2row Data MySQL>Select* fromMyClass ORDER BY ID limit0,2; or MySQL>Select* fromMyClass limit0,2; Delete data in table command: Delete fromTable namewhereexpression For example: Delete Table MyClass in the number1Records of MySQL> Delete fromMyClasswhereId=1Modify Table Data command: Update table nameSetfield = new Value,...whereconditional MySQL> Update MyClassSetName='Mary' whereId=1Add field in table command: ALTER TABLE name add field type other; for example: Add a field passtest in table MyClass, typeint(4), the default value is0MySQL> ALTER TABLE MyClass add Passtestint(4)default '0'Change table name command: Rename table name to a new name; For example: Change the table MyClass name to Youclassmysql>Rename table MyClass to Youclass; Update field contents command: Update table nameSetField name =New Content Update table nameSetField name = replace (field name,'Old content','New Content'For example: in front of the article, add4a space update articleSetContent=concat ('    ', content); Database import and Export export database files from a database use the "mysqldump" command to enter the DOS interface first, and then do the following. 1) Export all database formats: mysqldump-u [Database user name]-p-a>[Save path of backup file]2) export data and structure format: mysqldump-u [Database user name]-p [database name to be backed up]>[Save path of backup file] Example:1: Export the database mydb to the E:\MySQL\mydb.sql file. Open StartRun-enter "cmd" to enter the command line mode. C +> mysqldump-h localhost-u root-p mydb >E:\MySQL\mydb.sql then enter the password, wait for the export to succeed, you can check the target file for success. Example2: Export mytable from Database mydb to the E:\MySQL\mytable.sql file. C +> mysqldump-h localhost-u root-p mydb mytable>E:\MySQL\mytable.sql Example3: Exports the structure of the database mydb to the E:\MySQL\mydb_stru.sql file. C +> mysqldump-h localhost-u root-p mydb--add-drop-table >E:\MySQL\mydb_stru.sql notes:-h localhost can be omitted, it is generally used on the virtual host. 3) Export-only data not exported structure format: mysqldump-u [Database user name]-p-t [database name to be backed up]>[Save path of backup file]4) in the export database, the events format: mysqldump-u [Database user name]-p-e [database user name]>[Save path of backup file]5) format stored procedures and functions in the Export database: Mysqldump-u [Database user name]-p-r [database user name]>[Save path of backup file] Import from an external file into the database1use the source command to first enter the MySQL command console, then create the database, and then use the database. Finally, perform the following actions. MySQL>Source [Save path of backup file]2) Use the <the symbol first enters the MySQL command console, then creates the database, then exits MySQL and enters the DOS interface. Finally, perform the following actions. MySQL-U root–p < [save path of backup file]

MySQL Common commands