mysql command-common SQL statement Command code

mysql command-common SQL statement Command code

Use G to display results vertically by row
If a line is long and needs to be displayed, it's very hard to look at the results. By using g instead of a semicolon at the end of a SQL statement or command, you can export the value of each row vertically. This is probably one of the most familiar features of MySQL that distinguishes it from other database tools.

Mysql> SELECT * from Db_archivelogg
1. Row ***************************
Id:1
Check_day:2008-06-26
Db_name:tbdb1
arc_size:137
arc_num:166
per_second:1.6
avg_time:8.7
2. Use pager to set display mode
If a select result set is more than a few screens, the previous result is flash and cannot be seen. You can use pager to display query results such as more or less that call the OS, as well as using more in the OS or less viewing large files.
Use more

Mysql> Pager More
PAGER set to ' more '
Mysql> P More
PAGER set to ' more '
Using less

Mysql> Pager Less
PAGER set to ' less '
Mysql> P Less
PAGER set to ' less '
Revert to stdout

Mysql> Nopager
PAGER set to stdout
3. Save run results to file using Tee
This sqlplus-like spool feature allows you to save results from the command line to an external file. If you specify a file that already exists, the results are appended to the file.

Mysql> Tee output.txt
Logging to file ' Output.txt '
Or
Mysql> T Output.txt
Logging to file ' Output.txt '

Mysql> notee
outfile disabled.
Or
Mysql> T
outfile disabled.
4. Execute OS command

mysql> system uname
Linux
Mysql>! Uname
Linux
5. Execute SQL file

Mysql> Source Test.sql
+----------------+
| Current_date () |
+----------------+
| 2008-06-28 |
+----------------+
1 row in Set (0.00 sec)
Or
Mysql>. Test.sql
+----------------+
| Current_date () |
+----------------+
| 2008-06-28 |
+----------------+
1 row in Set (0.00 sec)

A) connection to MySQL:

Format: mysql-h host address-u user name-P user Password

1, Example 1: Connect to MySQL on this computer

First in the Open DOS window, and then into the MySQL installation directory under the bin directory, such as: D:mysqlbin, and then type the command mysql-uroot-p, enter after the prompts you to lose the password, if you have just installed a good MySQL, superuser root is no password, So you can enter the direct return to MySQL, the MySQL prompt is:mysql>
2, Example 2: Connect to the remote host MySQL

Suppose the IP of the remote host is: 10.0.0.1, the username is root, and the password is 123. Type the following command:
Mysql-h10.0.0.1-uroot-p123
(Note: U and root can be without spaces, others are the same)
3, quit the MySQL command

Exit (Enter)

(ii) Change Password:

Format: Mysqladmin-u username-P Old password password new password
1, Example 1: Add a password to root 123. First enter the directory C:mysqlbin in DOS, and then type the following command:

Mysqladmin-uroot-password 123
Note: Since Root does not have a password at the beginning, the-p old password can be omitted.
2, Example 2: And then change the root password to 456

MYSQLADMIN-UROOT-PAB12 Password 456
(iii) Adding new users: (Note: Unlike the above, the following is the command in the MySQL environment, followed by a semicolon as the command Terminator)

Format: Grant Select on database. * To User name @ login host identified by "password"
Example 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 the root user to MySQL, and then type the following command:
Grant Select,insert,update,delete on *.* to test1@ "%" identified by "ABC";

But for example 1 the increased user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and can do whatever it wants with your data, as shown in Example 2.
Example 2, add a user test2 password for ABC, so that he can only log on the localhost, and can query the database mydb, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database of the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to Test2@localhost identified by "ABC";
If you do not want to test2 the password, you can make another command to eliminate the password.
Grant Select,insert,update,delete on mydb.* to Test2@localhost identified by "";
(iv) Display commands

1. Display Database list:

show databases;
Only two databases were initially: MySQL and test. MySQL Library is very important it has MySQL system information, we change the password and new users, is actually using this library to operate.
2, display the data table in the library:

use MySQL;//Open Library
Show tables;
3, display the structure of the data table:

describe table name;
4, build the library:

Create database library name;
5, the establishment of the table:

Use library name;
CREATE TABLE table name (field set list);
6, delete the database and delete the table:

drop Database library name;
The drop table table name;
7. Empty the record of the table:

Delete from table name;
8, display the records in the table:

SELECT * from table name;
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 when the database changed the operation success!) )
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: Load data into a database table (for example, d:/mysql.txt) in text mode
mysql> LOAD DATA Local INFILE "D:/mysql.txt" into TABLE MYTABLE;
9: Import. sql file command (e.g. D:/mysql.sql)
Mysql>use database;
Mysql>source D:/mysql.sql;
10: Delete Table
Mysql>drop TABLE MYTABLE;
11: Empty the table
Mysql>delete from MYTABLE;
12: Update the data in the table
Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';

In Windows, MySQL exists as a service, and you should make sure that the service is started and that the available net start MySQL command is not started before you use it. Linux can be started with the "/etc/rc.d/init.d/mysqld start" command, and notice 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, where the anonymous account deletion, the root account set the password, you can use the following command:
Use MySQL;
Delete from User where user= "";
Update User set Password=password (' NewPassword ') where user= ' root ';
If you want to limit the logon terminals used by the user, you can update the host field for the corresponding user in the users table, restart the database service after making the above changes, and at this point you may be able to log on to the following similar commands:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;
The command parameters above are part of the common parameters, and the details refer to the documentation. The mydb here is the name of the database to log on to.
In the development and practical applications, users should not only use root to connect the database, although the use of root users to test is very convenient, but it will bring significant security risks to the system, but also not conducive to the improvement of management technology. 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 data. MySQL user management is implemented through the user table, there are two ways to add new users, one is to insert the corresponding data rows in the user table, set the appropriate permissions, and the second is to create a user with some kind of permission through the grant command. One of the common uses of grant is as follows:
Grant all on mydb.* to newusername@hostname identified by "password";
Grant usage on *.* to newusername@hostname identified by "password";
Grant Select,insert,update on mydb.* to newusername@hostname identified by "password";
Grant Update,delete on MyDB. TestTable to Newusername@hostname identified by "password";
To give this user the ability to manage his or her permissions on the object, 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, in case the malicious person steals the password. For those who have not used the user should be given clearance, permissions of the user should be timely recall permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.

Global Administrative permissions:
File: Read and write files on the MySQL server.
PROCESS: Displays or kills a service thread that belongs to another user.
RELOAD: Overload access Control table, refresh log, etc.
SHUTDOWN: Turn off the MySQL service.
Database/datasheet/data column permissions:
Alter: Modify an existing datasheet (for example, add/Remove Columns) and indexes.
Create: Creates a new database or datasheet.
Delete: Deletes a record of a table.
Drop: Deletes a datasheet or database.
Index: Create or delete indexes.
INSERT: Adds a record for a table.
SELECT: Displays/searches for records of a table.
UPDATE: Modifies records that already exist in the table.
Special Permissions:
All: Allow to do anything (like root).
USAGE: Only allow logins-nothing else allowed.