Common MySQL commands collected

1. Connect to MySQL

Format: mysql-H host address-u user name-P User Password

Ii. Change the password

Format: mysqladmin-u username-P old Password New Password

 

3. Add new users.

(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" [with grant option] or [with admin option]

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 *. *
Test2 @ localhostidentified 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. *
Test2 @ localhostidentified "";

With admin option is used for system permission authorization, and with grant option is used for object authorization.
However, when a user is granted system permissions with the with admin option, the user can grant the system permissions to other users or roles, but the user's system permissions are revoked, the system permission granted by this user to other users or roles will not be invalid due to propagation. For example, if A is granted system permission to create session with admin option, then a grants the createsession permission to B, however, when the Administrator revokes the create session permission of a, B still has the create session permission, but the administrator can explicitly revoke the permissions of B create session, that is, directly revoke create
Session from B.

When with grantoption is used for object authorization, the authorized user can also grant this object permission to other users or roles, the difference is that when the Administrator revokes the permission of a user object authorized by the with grant option, the permission will be invalidated due to propagation. For example, grant select on table with grant option to, user A grants this permission to user B. However, when the Administrator revokes the permission of user A, the permission of user B also becomes invalid, but the administrator cannot directly revoke the select ontable permission of user B.

For example, grant allprivileges on *. * to Monty @ localhostidentified by 'pwd' with grant option;

 

4. delete user authorization revokeall privileges on *. * from root @ "% ";

5. rename a table:

Mysql> alter table T1 rename T2;

6. Back up the database

Shell> mysqldump-H host-u root-pdbname> dbname_backup. SQL

Restore database

Shell> mysqladmin-H myhost-u root-pcreate dbname

Shell> mysqldump-H host-u root-pdbname <dbname_backup. SQL

If you only want to unload the table creation command, the command is as follows:

Shell> mysqldump-u root-p-ddatabasename> A. SQL

If you only want to unload the SQL command for inserting data without the table creation command, the command is as follows:

Shell> mysqldump-u root-p-t databasename> A. SQL

What should I do if I only want data and do not want any SQL commands?

Mysqldump-T./phptest driver

Only when the-t parameter is specified can the plain text file be detached, indicating the directory where the data is detached and./indicates the current directory, that is, the same directory as mysqldump. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is a. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands.

7. You can store the query in a file and tell MySQL to read the query from the file instead of waiting for keyboard input. You can use the shell to type the redirection utility to do this. For example, if the file my_file. SQL contains a query

Query:

For example, if you want to write a table prefix in SQL .txt:

Mysql> mysql-H myhost-u root-pdatabase <SQL .txt

VII. 1) Create a database staffer

Mysql> 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: name of the column in the table. It must comply with the identifier rules and be unique in the table.

Type: the data type of the column. Some data types need to specify the length N and enclose them in parentheses. For details about the data types currently provided by MySQL, see MySQL advanced _ column type.

Not null | NULL: Specifies whether the column can be null. If neither null nor not null is specified, the column is considered null.

Default default_value: Specify 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 the value, the default value is null. If the column is declared as not null, the default value depends on the column type:

1. For numeric types that do not declare the auto_increment attribute, the default value is 0. For an auto_increment column, the default value is the next value in the sequence.

2. For date and time types except timestamp, the default value is the appropriate "zero" value of this type. For the first timestamp column in the table, the default value is the current date and time.

3. For string types except Enum, the default value is null. For Enum, the default value is the first enumerated value.

Auto_increment: set this column to have the auto-increment attribute. This attribute can be set only for integer columns. When you insert a null value or 0 to an auto_increment column, the column is set to value + 1, where value is the maximum value of this column in the previous table. The auto_increment sequence starts from 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 amount', # Set the default value

Description varchar (100 ),

Primary Key pk_department (ID) # Set the primary key

);

8. Alter command

Mysql>

# Add column test to table position

Alter table positionadd (test char (10 ));

# Test

Alter table positionmodify test char (20) not null;

# Modify the default value of column test in table position

Alter table positionalter Test Set Default 'system ';

# Remove the default value of test from table position

Alter table positionalter test drop default;

# Remove column test from table position

Alter table positiondrop column test;

# Table depart_pos Delete primary keys

Alter table depart_posdrop primary key;

# Add a primary key for table depart_pos

Alter tabledepart_pos add primary key pk_depart_pos (department_id, position_id );

9. 1: Use the show statement to find 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 (namevarchar (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 update command

Mysql> Update mytable setsex = "F" where name = 'hyq ';