Shulou(Shulou.com)05/31 Report--

This article mainly explains the "summary of commonly used mysql commands". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn the "summary of commonly used mysql commands" together.

1. Connect MySQL

Format: mysql -h host address-u user name-p user password

Second, change the password

Format: mysqladmin -u username-p old password new password

3. Add new users.

(Note: Different from the above, the following is a command in MySQL environment, so it is followed by a semicolon as the command terminator)

Format: grant select on database.* to username @ login host identified by "password" [with grant option] or [with admin option]

Example 1: Add a user test1 password to abc, so that he can log in on any host and have query, insert, modify and delete permissions on all databases. First connect to MySQL as root and type the following command:

grant select,insert,update, delete on *.* to identified by "abc";

If you don't want test2 to have a password, you can type another command to erase the password.

grant select,insert,update,delete on mydb .* to identified by "";

with admin option for system authorization, with grant option for object authorization.

However, when a user is granted the system permission with admin option, the user can grant the system permission to other users or roles. However, when the system permission of this user is withdrawn, the system permission granted by this user to other users or roles will not be invalid due to propagation. For example, A is granted the system permission create session with option, and then A grants the create session permission to B. However, when the administrator withdraws the create session permission of A, B still has the create session admin permission. But administrators can explicitly revoke the permission of B create session, that is, directly revoke create session from B.

When with grant option is used for object authorization, the granted user can also grant this object permission to other users or roles. The difference is that when the administrator withdraws the user object permission granted with grant option, the permission will be invalid due to propagation. For example, grant select on table with grant option to A,A user grants this permission to B, but when the administrator withdraws the permission of A, the permission of B will also be invalid, but the administrator cannot directly withdraw the SELECT ONTABLE permission of B.

For example: GRANT ALLPRIVILEGES ON *.* TO IDENTIFIED BY ’pwd’ WITH GRANT OPTION;

4. Delete user authorization revokeall privileges on *.* from ";

V. Renaming table:

mysql > alter table t1 rename t2;

VI. Backup database

shell> mysqldump -h host -u root -pdbname >dbname_backup.sql

recover the database

shell> mysqladmin -h myhost -u root -pcreate dbname

shell> mysqldump -h host -u root -pdbname

mysqladmin -u root -p -ddatabasename > a.sql

If you only want to unload the sql command that inserted the data and do not need the table creation command, the command is as follows:

shell> mysqladmin -u root -p -t databasename> a.sql

So what if I want data and not sql commands?

mysqldump-T./ test driver

Among them, only when the-T parameter is specified can the plain text file be unloaded, indicating the directory where the data is unloaded,./ Represents the current directory, i.e. the same directory as mysqldump. If no driver table is specified, the entire data is unloaded. Each table generates two files, one for the.sql file, containing the execution of the table. The other is a.txt file, which contains only data and no sql instructions.

Instead of waiting for keyboard input, queries can be stored in a file and told to read from the file. This can be done using the shell typing redirection utility. For example, if there is a query stored in the file my_file.sql

These queries can be executed as follows:

For example, if you want to pre-write table creation statements in sql.txt:

mysql > mysql -h myhost -u root -pdatabase

create database staffer

2) Create table

col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]

[PRIMARY KEY] [reference_definition]

or PRIMARY KEY (index_col_name,...)

or KEY [index_name] (index_col_name,...)

or INDEX [index_name] (index_col_name,...)

or UNIQUE [INDEX] [index_name] (index_col_name,...)

or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)

[reference_definition]

or CHECK (expr)

col_name: The name of the column in the table. The identifier rules must be met and unique within the table.

type: Data type of column. Some data types require length n to be specified and enclosed in parentheses. For more information about MySQL's current data types, see MySQL Advanced_Column Types.

NOT NULL |NULL: Specifies whether the column is allowed to be null. If neither NULL nor NOT NULL is specified, the column is assumed to have NULL specified.

DEFAULT default_value: Specifies the default value for the column. If no default value is specified for the column, MySQL automatically assigns one. If the column can take NULL as a value, the default value is NULL. If the column is declared NOT NULL, the default value depends on the column type:

For numeric types that do not have the AUTO_INCREMENT attribute declared, the default value is 0. For an AUTO_INCREMENT column, the default value is the next value in the order.

For date and time types other than TIMESTAMP, the default value is the appropriate "zero" value for that type. For the first TIMESTAMP column in the table, the default is the current date and time.

For string types other than ENUM, the default is an empty string. For ENUM, the default value is the first enumerated value.

AUTO_INCREMENT: Sets the column to have an autoincrement attribute, which can only be set for integer columns. When you insert a NULL value or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the maximum value of the column in the previous table. AUTO_INCREMENT sequence starts with 1. Each table can have only one AUTO_INCREMENT column, and it must be indexed.

create table department

(

id int not null auto_increment,

name varchar(20) not null default 'System department',#Set default

description varchar(100),

primary key PK_department (id) #Set primary key

);

VIII. Modify the structure alter command

mysql>

#table position added column test

alter table positionadd(test char(10));

#table position modify column test

alter table positionmodify test char(20) not null;

#table position modify column test default

alter table positionalter test set default 'system';

#table position remove default value of test

alter table positionalter test drop default;

#table position remove column test

alter table positiondrop column test;

#table depart_pos delete primary key

alter table depart_posdrop primary key;

#Add PK to table depart_pos

alter tabledepart_pos add primary key PK_depart_pos (department_id,position_id);

9. 1: Use the SHOW statement to find out what databases currently exist 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 show Database changed, indicating successful operation!)

4: See what tables exist in the current database

mysql> SHOW TABLES;

5: Create a database table

mysql> Create TABLE MYTABLE (nameVARCHAR(20), sex CHAR(1));

6: Display table structure:

mysql> DESCRIBE MYTABLE;

7: Add records to the table

mysql> insert into MYTABLE values("hyq","M");

8: Load data into database tables as text (e.g. D:/mysql.txt)

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 a table

mysql>drop TABLE MYTABLE;

11: Empty the table

mysql>delete from MYTABLE;

12: Update the data in the table

mysql>update MYTABLE setsex="f" where name='hyq';

Thank you for reading, the above is the "commonly used mysql command summary" content, after the study of this article, I believe we have a deeper understanding of the commonly used mysql command summary this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.