MySQL Common commands

1. System Management
Connect to MySQL (in MySQL/bin) mysql-H host address-u username-P MySQL 3306 127.0.0.1 Root
Exit MySQL Command exit
Change Password (in MySQL/bin) mysqladmin-u username-P old Password New Password
User authorization
Grantselect [insert] [, update] [, delete] on database. * to user name
@ Localhost ("%", indicating any host) identifiedby "password"
Add users
Mysqldump-u root-P opt Database Name> Backup File Name
Back up the database (in MySQL/bin)
Mysql-u root-P <batch file (for example, backup file name)
Batch Processing
MySQL. Server start
Start the server
MySQL. server stop
Stop a server
Msql. server log

2. query command
Select version () query version number
Select current_date query current date
3. Display commands
Show databases: displays the Database List
Show tables
Describe Table Name displays the structure of the data table
Select * from Table Name displays records in the table
Select what_to_select from which table
[Whereconditions_to_satisfy and (or) Where conditions_to_satisfy]
Retrieve data from a table "meet the conditions 』
Select Field 1, Field 2 ,... From table name: displays the values of a specific column
Select * from table name order by field name sort row
Select Field 1, which includes field 2, calculation of the field value of the new field from Table Name
Select Field 1 is null (is not null) null operation
Select * from table name where field name like (not like) "character"
Allow "_" to match any single character, while "%" matches any number of character pattern matches
Select * from table name where field name Regexp (not Regexp) or rlike (not rlike)
"." Matches any single character and one character class.
[…] Match any character in the box. For example, [a], [ASD], [AZ] match any lowercase letter, and [09] match any number.
"*" Matches zero or multiple items before it. Regular Expressions are case sensitive [AA]
If it appears anywhere in the tested value, the pattern matches
Locate, use "^" at the beginning of the mode, and "$" at the end, for example, "^ B"
Extended Regular Expression
Select count (*) from Table Name
Select field name, count (*) from table name group by field name row count
4. Edit commands
Database used by the Use Database Name
Create Database database name create database
Create Table Name create a table in the database
Insert into table name values ("data", "data") add records to the table
Load data infile "/path/FILENAME" External table name
Add data to a table from a file. each row of the file contains a record, and values are separated by a tab.
Drop database database name delete database
Drop table name Delete table in Database
Delete from table name where delete records in database table
Update table name set field = "value" wherewhereconditions_to_satisfy
Update the record value in the database table

 

========================================================== ========================================================

1. Start the MySQL server

Two Methods: one is to use winmysqladmin. If the machine runs automatically at startup, you can directly go to the next step. Second, Run D: \ mysql \ bin \ mysqld in DOS mode
(Assume that MySQL is installed in D: \ mysql). Of course, you can also run the Net start MySQL command to start MySQL. In Linux, the "/etc/rc. d/init. d/mysqld start" command is available. Note that the initiator should have administrator privileges.

2. Enter the MySQL interactive operation interface

In DOS mode, run: D: \ mysql \ bin \ mysql
The prompt "mysql>" For MySQL appears. The interactive operation mode for MySQL is displayed.
If "error 2003: Can't connect to MySQL server on 'localhost' (10061)" appears, your MySQL has not been started yet.

3. Exit the MySQL operation interface.

Enter quit at the mysql> prompt to exit the interactive operation interface at any time:
Mysql> quit
Bye

4. The First Command (multiple commands are separated by commas (,). One command can be input in multiple lines until a semicolon (;) appears.

Mysql> select version (), current_date ();
Mysql> select (20 + 5) * 4;
Mysql> select (20 + 5) * 4, sin (PI ()/3 );
Mysql> select (20 + 5) * 4 as result, sin (PI ()/3); (as: Specify the alias as result)

5. Common SQL statements

1: Use the show statement to find out the current database on the server:
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 ';
13: insert data
Mysq> linsert into mytable values (1, 'Chapter 2 ', 0 × 2134545 );
14. log on to MySQL
Mysql-u root-P MySQL
Mysql-u root-p-h 11.11.11.11 Database
15: MySQL JDBC connection URL uses Chinese Characters
JDBC: mysql: // localhost/test? Useunicode = true & characterencoding = gb2312

[Separator]

Others:
Show processlist; lists information about each online transaction.
Show varlables; List MySQL system settings.
Show tables from db_name; list all data tables in db_name;
Show [full] columns from table_name; lists the complete information in table_name, such as the column name and type, including character set encoding.
Show index from table_name; Lists All indexes in table_name.
Show table status; lists information about data tables in the current database.
Show table status from db_name; lists information about data tables in the current db_name.
Alter table table_name engine InnoDB | MyISAM | memory; change the table Type
Explain table_name/describe table_name; list the complete information of table_name, such as the column name and type.
Show create table table_name: displays the table creation statements of the current table
Alter table table_name add primary key (picid); add a primary key to the table
Alter table table_name add column userid int after picid Add a new field to modify the table structure
Alter table table_name Character Set gb2312 alter table Encoding
Select User (); displays the current user.
Select password ('root'); displays the current user password
Select now (); displays the current date
Flush privileges refresh User Permissions without restarting
Mysqld-default-character-set = gb2312; set the default character set to gb2312

6. Security

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 ';

Restart MySQL to take effect.

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 ";
Grant all privileges on *. * to root @ localhost
Grant select, insert, delete, update, alter, create, drop on lybbs. * To newusername @ "%" identified by "lybbs ";

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 permission:
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 a table record.
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: allows you to do anything (same as root ).
usage: Only logon is allowed-nothing else is allowed.