MySQL Common commands

MySQL Common commands
1: Use the show statement to find out what database currently exists on the server:
Mysql> SHOW DATABASES;
2:2. Create a database Mysqldata
mysql> CREATE DATABASE Mysqldata;
3: Select the database you created
mysql> use Mysqldata; (press ENTER to appear database changed the operation is successful!) )
4: See what tables exist in the current database
Mysql> SHOW TABLES;
5: Create a database table
Mysql> CREATE TABLE MYTABLE (name VARCHAR), sex CHAR (1));
6: Show the structure of the table:
Mysql> DESCRIBE MYTABLE;
7: Add a record to the table
mysql> INSERT INTO MYTABLE values ("HyQ", "M");
8: Loading data into a database table in text mode (for example, D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLE MYTABLE;
9: Import. sql File command (for example, D:/mysql.sql)
Mysql>use database;
Mysql>source D:/mysql.sql;
10: Delete Table
Mysql>drop TABLE MYTABLE;
11: Clear the table
Mysql>delete from MYTABLE;
12: Update data in table
Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';
Here are some of the management tips that you can inadvertently see on the web using MySQL,
In Windows, MySQL exists as a service and you should ensure that the service is started before use, and that the available net start MySQL command is not started. While Linux starts with the "/etc/rc.d/init.d/mysqld start" command, note that the initiator should have administrator privileges.
The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security risk, for some important applications we should improve security as far as possible, the anonymous account should be deleted, the root account password, the following commands can be used:
Use MySQL;
Delete from User where user= "";
Update User set Password=password (' NewPassword ') where user= ' root ';
If you want to restrict the logon terminal used by users, you can update the user's host field in the user table, and you should restart the database service when you make the above changes, and you will be able to log in with a command like this:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, which can be referenced in detail in the documentation. The mydb here is the name of the database to log in to.
In the development and the actual application, the user should not only use the root user to connect the database, although uses the root user to carry on the test to be convenient, but will bring the system the significant security hidden danger, also is not advantageous to the management technology enhancement. We give the most appropriate database permissions to the users used in an application. A user who only inserts data should not be given permission to delete the data. The user management of MySQL is implemented through the users table, there are two common methods for adding new users, one is to insert the corresponding data row in the user table, set the appropriate permissions, and the other is to create a user with some kind of permission through the grant command. The common usage of grant is as follows:
Grant all on mydb.* to [e-mail protected] identified by "password";
Grant Usage on * * to [e-mail protected] identified by "password";
Grant Select,insert,update on mydb.* to [e-mail protected] identified by "password";
Grant Update,delete on MyDB. TestTable to [e-mail protected] identified by "password";
To give this user the ability to manage the permissions on the object, you can add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption to prevent the malicious person from stealing the password. For those who have not used the user should be given clearance, the permission of the user should be in a timely manner to reclaim permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.
The following is an explanation of the common permissions I have obtained from other sources (www.cn-java.com):
Global Administrative permissions:
File: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overloads the Access Control table, refreshes the log, and so on.
SHUTDOWN: Turn off the MySQL service.
Database/data Table/Data column permissions:
Alter: Modifies an existing data table (for example, add/Remove Columns) and index.
Create: Create a new database or data table.
Delete: Deletes the record for the table.
Drop: Deletes a data table or database.
Index: Establish or delete the indexes.
INSERT: Adds a table record.
SELECT: Displays/searches the table's records.
UPDATE: Modifies a record that already exists in the table.
Special permissions:
All: Allow to do anything (as root).
USAGE: Allow login only – nothing else is allowed.
One, connect MySQL
Format: mysql-h host address-u user name-P user Password
Or: Mysql-u user name---------Enter the password, the password is not visible
1. Connect to MySQL on this machine.
First open the DOS window, then go to directory Mysql\bin, then type the command Mysql-u root-p, You will be prompted to lose the password after entering. Note The user name can have a space or no space, but if the user password after-p, then the password must be no space between-p, or let you re-enter the password. For example, the following are legal login: (Account: root password: 123)
Mysql-u root-p
Mysql-uroot-p
Mysql-uroot-p123
If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>
2. Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123; (Note: You can not add a space between the root and the other)
3. Exit MySQL command: Exit (enter)
Second, change the password.
Format: Mysqladmin-u username-P Old password password new password
1, add a password to root ab12. First enter directory Mysql\bin under DOS, and then type the following command
Mysqladmin-u Root-password AB12
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
2, then change the root password to djg345.
Mysqladmin-u root-p ab12 Password djg345
(Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)
3, the command line to modify the root password:
mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';
mysql> FLUSH privileges;
4. Display the current User:
Mysql> SELECT USER ();
Third, add new users.
Format: Grant Select on database. * To User name @ login host identified by "password"
1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, connect with the root user
MYSQL, and then type the following command:
Grant Select,insert,update,delete on *. * to Test1 "%" identified by "ABC";
But the added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log into your MySQL database and your data can do whatever you like, solution see 2.
2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host),
This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [email protected] identifiedby "ABC";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";
Four, the Operation skill
1, if you hit the command, enter after the discovery forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it.
In other words, you can break a complete command into a few lines, and then use a semicolon to make the end sign OK.
2. You can use the cursor up and down keys to recall the previous command.
V. Operation of the database
1. Displays the list of databases in the current database server:
Mysql> SHOW DATABASES;
Note: MySQL library has the MySQL system information, we change the password and new users, is actually using this library to operate.
2. Display the data table in the database:
mysql> use library name;
Mysql> SHOW TABLES;
3. Display the database name of use:
Mysql> SELECT DATABASE ();
4. Establish the database:
mysql> CREATE database name;
5. Delete the database:
mysql> DROP database name;
6. Import the. sql File command:
mysql> use database name;
Mysql> SOURCE D:/mysql.sql;
You can also import by typing the following command in a DOS environment:
Mysql-uroot-proot DatabaseName < Databasename.sql
Note: Before importing, please ensure that MySQL must have databasename this database;
Vi. backing up the database:
Note that the mysqldump command executes under the DOS Mysql\bin directory and cannot be executed in a MySQL environment, so it cannot be separated by a semicolon ";" End. If you have logged in to MySQL, run the exit command mysql> exit
1. Export the entire database
The export file is present in the Mysql\bin directory by default
Mysqldump-u user name-p database name > exported file name
mysqldump-uroot-p123456 database_name > Outfile_name.sql
2. 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
3. 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
4. Export with language parameters
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql
Vii. transferring text data to the database
1, the text data should conform to the format: The field data is separated by the TAB key, the null value is replaced by \ n. Example:
3 Rose Dalian II 1976-10-10
4 Mike Dalian One 1975-12-23
Suppose you save these two sets of data as school.txt files, placed in the C packing directory.
2. Data Incoming command
mysql> Load Data local infile "c:\school.txt" into table name;
Note: You might want to copy the file to the Mysql\bin directory, and use the using command to hit the library that contains the table.
Viii. operation of the table
1, display the structure of the data table:
mysql> DESCRIBE table name; (DESC table name)
2. Set up the data sheet:
mysql> use library name; Enter the database
mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));
3. Delete Data sheet:
mysql> DROP table name;
4. Renaming the data table
ALTER TABLE t1 rename T2;
5. Display the records in the table:
Mysql> SELECT * from table name;
6. Insert a record into the table:
mysql> INSERT into table name VALUES ("HyQ", "M");
7. Update the data in the table:
mysql-> UPDATE table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';
8. Empty the records in the table:
Mysql> DELETE from table name;
9. Load data into the data table in text mode:
mysql> LOAD DATA LOCAL INFILE "d:/mysql.txt" into table name;
10, display the definition of the table, you can also see the constraints of the table, such as foreign keys
mysql> SHOW CREATE TABLE yourtablename;
You can also dump the full definition of a table into a file by mysqldump, including, of course, the foreign key definition.
You can also list foreign key constraints for table T by using the following directives:
Mysql> SHOW TABLE STATUS from yourdatabasename like ' T '
FOREIGN KEY constraints will be listed in the table comments.
Stored Procedures
11. Create a stored procedure
CREATE PROCEDURE procedurename (in Paramentname type, in Paramentname type,......)
BEGIN
SQL sentences;
END
12. Call the stored procedure
Mysql> call ProcedureName (paramentlist);
Example:mysql> call Addmoney (12, 500);
13. View stored procedures for a specific database
Method one:mysql> SELECT ' name ' from mysql.proc WHERE db = ' your_db_name ' and ' type ' = ' PROCEDURE ';
Method Two:mysql> show procedure status;
14. Delete stored Procedures
mysql> DROP PROCEDURE procedure_name;
mysql> DROP PROCEDURE IF EXISTS procedure_name;
15. View the specified stored procedure definition
mysql> SHOW CREATE PROCEDURE proc_name;
mysql> SHOW CREATE FUNCTION func_name;
----------Example One-----------
Mysql> DELIMITER
mysql> use ' db_name '

Select Database
mysql> DROP PROCEDURE IF EXISTS ' Addmoney ' $$

If there is a stored procedure with the same name, delete the

Mysql> CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' Addmoney ' (in XID Int (5), in Xmoney Int (6))
Mysql> BEGIN
Mysql> UPDATE USER u SET u.money = U.money + Xmoney WHERE u.id = XID;

Semicolon ";" Does not result in statement execution because the current delimiter is defined as $$
Mysql> END

Terminate
Mysql> DELIMITER; Change the delimiter back to the semicolon ";"
Mysql> call Addmoney (5,1000); Executing stored procedures
----------Example Two-----------
Mysql> delimiter//
Mysql> CREATE PROCEDURE Proc_name (in Parameter integer)
Mysql> begin
mysql> if Parameter=0 Then
Mysql> SELECT * from the user order by ID ASC;
Mysql> Else
Mysql> SELECT * from the user order by id desc;
Mysql> End If;
Mysql> end;
Mysql>////Here "//" for Terminator
Mysql> delimiter;
Mysql> Show warnings;
Mysql> call Proc_name (1);
Mysql> call Proc_name (0);
Ix. actions to modify column properties of a table
1. In order to change column A, change from integer to tinyint not NULL (same name),
and change column B, from char (10) to char (20), rename it at the same time, change from B to C:
mysql> ALTER TABLE T2 MODIFY A TINYINT not NULL, change b C CHAR (20);
2. Add a new timestamp column named D:
mysql> ALTER TABLE T2 ADD D TIMESTAMP;
3. Add an index to column D and make column a the primary key:
mysql> ALTER TABLE T2 add INDEX (d), add PRIMARY KEY (a);
4. Delete Column C:
mysql> ALTER TABLE T2 DROP COLUMN C;
5. Add a new auto_increment integer column named C:
mysql> ALTER TABLE T2 ADD C INT UNSIGNED not NULL auto_increment,add INDEX (c);
Note that we indexed C because the auto_increment column must be indexed, and in addition we declare that C is not NULL,
Because the indexed column cannot be null
Ang setting build and build tables and instances of inserting data
Drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table
The following is the Insert field
Insert into teacher values (' ', ' Allen ', ' Dalian One ', ' 1976-10-10 ');
Insert into teacher values (' ', ' Jack ', ' Dalian II ', ' 1975-12-23 ');
It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug.
(1) You can write the above command as-is to a text file, assume that it is school.sql, then copy to C: \, and enter directory \mysql\bin in DOS, and then type the following command.
Mysql-uroot-p Password < C:\school.sql
If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).
(2) or enter the command line after using mysql> source C:\school.sql; You can also import the School.sql file into the database.

MySQL Common commands