Mysql Command-common SQL statement command code

Mysql Command-common SQL statement command code

Use G to display results vertically by line
If a line is long and needs to be displayed in this line, it is very uncomfortable to see the results. After an SQL statement or command, you can use G instead of the end of a semicolon to vertically output the values of each row. This may also be a feature that distinguishes MySQL 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 the Display Mode
If the selected result set contains more than a few screens, the previous results cannot be seen. You can use pager to display query results by calling more or less of the OS, which is the same as using more or less in the OS to view large files.
Use more

Mysql> pager more
PAGER set to 'more'
Mysql> P more
PAGER set to 'more'
Use less

Mysql> pager less
PAGER set to 'less'
Mysql> P less
PAGER set to 'less'
Restore to stdout

Mysql> nopager
PAGER set to stdout
3. Use tee to save the running result to the file
This is similar to the SQL plus spool function. You can save the results in the command line to an external file. If an existing file is specified, the result is 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. Run the OS command

Mysql> system uname
Linux
Mysql>! Uname
Linux
5. Execute the 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)

1) connect to MYSQL:

Format: mysql-h host address-u user name-p User Password

1. Example 1: connect to MYSQL on the local machine

First, open the DOS window and enter the bin directory under the mysql installation directory, for example, D: mysqlbin. Then, enter the mysql-uroot-p command and press enter to prompt you to enter the password, if you have just installed MYSQL, the Super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql>
2. Example 2: connect to MYSQL on the remote host

Assume that the IP address of the remote host is 10.0.0.1, the user name is root, and the password is 123. Enter the following command:
Mysql-h10.0.0.1-uroot-p123
(Note: you do not need to add spaces for u and root. The same applies to others)
3. Exit MYSQL Command

Exit (Press ENTER)

 

(2) Change the password:

Format: mysqladmin-u username-p old password New password
1. Example 1: Add a 123 password to the root user. First enter the directory C: mysqlbin in DOS, and then type the following command:

Mysqladmin-uroot-password 123
Note: because the root account does not have a password at the beginning, the old-p password can be omitted.
2. Example 2: change the password of root to 456.

Mysqladmin-uroot-pab12 password 456
(3) Add a new user: (Note: Unlike the above, the following commands in the MYSQL environment are followed by a semicolon as the command Terminator)

Format: grant select on database. * to username @ login host identified by "password"
Example 1: Add a user named "test1" with the password "abc" so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:
Grant select, insert, update, delete on *. * to test1 @ "%" Identified by "abc ";

However, the User Added in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. For the solution, see Example 2.
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 "";
(4) display commands

1. display the Database List:

Show databases;
At the beginning, there were only two databases: mysql and test. The mysql database contains the MYSQL system information. We change the password and add new users to use this database for operations.
2. display the data tables in the database:

Use mysql; // open the database
Show tables;
3. display the data table structure:

Describe table name;
4. database creation:

Create database name;
5. Create a table:

Use Database Name;
Create table Name (field setting list );
6. Delete databases and tables:

Drop database name;
Drop table name;
7. Clear records in the table:

Delete from table name;
8. Display records in the table:

Select * from table name;
Mysql> show databases;
2. Create a database named MYSQLDATA
Mysql> create database mysqldata;
3: select the database you created
Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)
4: view the tables in the current database
Mysql> show tables;
5. Create a database table
Mysql> create table mytable (name VARCHAR (20), sex CHAR (1 ));
6: display the table structure:
Mysql> describe mytable;
7. Add records to the table
Mysql> insert into MYTABLE values ("hyq", "M ");
8: load data into database tables in text mode (for example, D:/mysql.txt)
Mysql> load data local infile "D:/mysql.txt" into table mytable;
9: import the. SQL FILE command (for example, D:/mysql. SQL)
Mysql> use database;
Mysql> source d:/mysql. SQL;
10: delete a table
Mysql> drop table mytable;
11: Clear the table
Mysql> delete from MYTABLE;
12: Update table data
Mysql> update MYTABLE set sex = "f" where name = 'hyq ';

 

In windows, MySql exists as a service. Before using mysql, make sure that the service has been started and the net start MySql command is not enabled. In Linux, the "/etc/rc. d/init. d/mysqld start" command is available. Note that the initiator must have administrator privileges.
The newly installed MySql contains a root account with a blank password and an anonymous account, which poses a major security risk. For some important applications, we should improve the security as much as possible, here, you should delete anonymous accounts and Set passwords for root accounts. You can run the following command:
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 the User, you can update the Host field of the corresponding User in the User table. After making the above changes, restart the database service, at this time, the following commands can be used for Logon:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
Mysql mydb-uroot-p;
Mysql mydb-uroot-pnewpassword;
The preceding command parameters are part of common parameters. For details, refer to the documentation. Here, mydb is the name of the database you want to log on.
In development and practical applications, users should not only use root users to connect to the database. Although it is convenient to use root users for testing, it will bring significant security risks to the system, it is not conducive to the improvement of management technology. We grant the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be granted the permission to delete data. MySql User management is implemented through the User table. There are two common methods to add new users. One is to insert the corresponding data rows in the User table and set the corresponding permissions; the second is to use the GRANT command to create a user with certain permissions. The common usage 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 GRANT the user the ability to manage permissions on the corresponding object, you can add the with grant option after GRANT. For users inserted into the User table, use the Password function to update and encrypt the PASSWORD field to prevent unauthorized users from stealing the Password. Users who do not need permissions should be cleared, and those who pass the permissions should be revoked in a timely manner. To REVOKE permissions, you can update the corresponding fields in the User table or use the REVOKE operation.

Global Management permissions:
FILE: read and write files on the MySQL server.
PROCESS: displays or kills service threads of other users.
RELOAD: RELOAD Access Control tables and refresh logs.
SHUTDOWN: Shut down the MySQL service.
Database/data table/data column permissions:
ALTER: Modify existing data tables (such as adding/deleting columns) and indexes.
CREATE: CREATE a new database or data table.
DELETE: DELETE table records.
DROP: delete a data table or database.
INDEX: Create or delete an INDEX.
INSERT: Add Table records.
SELECT: displays/searches for table records.
UPDATE: Modify existing records in the table.
Special permissions:
ALL: allow anything (same as root ).
USAGE: Only logon is allowed. Other operations are not allowed.