Summary of common mysql commands and common mysql commands

Summary of common mysql commands and common mysql commands

I. Database-level operations

1. log on to mysql

The common format is mysql-u user_name-p. Press enter and enter the password and press enter again.

2. view all existing databases

Show databases; (Note: The semicolon cannot be omitted. A few commands can ignore semicolons, but most mysql commands must end with semicolons)

3. Select a database

After you know the names of all databases, You need to select a specific database for operation. The command is:

Use database_name; (Note that this command is one of the few commands that can ignore semicolons)

E.g. use students;

4. Create a database

Create database database_name; (root permission may be required here)

E.g. create database one; this will create a database named one.

5. delete a database

Drop database database_name;

Ii. Table-level operations

1. View All table names of the current database

Show tables;

2. View All column names (attribute information) in a table)

Describe table_name; (can also be abbreviated as: desc table_name ;)

Show columns from table_name;

Show create table table_name;

3. view a column in the table

Select column_1 from table_name;

To view certain columns, separate the column names with commas (,), for example, select column_1, column_2 from table_name;

You can also use the wildcard * to view all columns, for example, select * from table_name;

4. Create a table

Create table table_name (column_name1 type constraint, column_name2 type constraint,..., column_nameX type constraint, primary key (column_name ));

Here the constraint includes not null (not empty), unique (not repeated), etc., is optional, that is, can not;

Type includes char (n) varchar (n) int numeric float real double precision; (Note that mysql strings must be enclosed in single quotes)

The primary key constraint at the end is required. The attributes in the primary key () brackets can be one or several separated by commas.

5. delete a table

Drop table table_name; (this will completely delete the table, that is, the show tables; command will not display it again)

(Note the similar command: delete from table_name; this command only clears the table, but the table mode still exists, that is, show tables; the command still displays it)

6. Add/delete column names (Attribute names) to/from the table)

Alter table table_name add column_name type;

Alter table table_name drop column_name;

7. modify a column in the table

Alter table table_name change old_col new_col type;

3. Row-level operations

1. Add rows (records, record) to the table)

Insert into table_name values)

If you forget the column name order and it is difficult to view the order, you can use this form:

Insert into table table_name (col_1, col_2,... col_n) values (val_1, val_2,... val_n );

In this case, only val_n corresponds to col_n, instead of the real order of column names.

2. delete a specific row in the table

Delete from table_name where P;

P is a condition, generally in the format of col_name = value, that is, a row whose property is equal to a value.

3. Modify/update rows

Update table_nameset col_name = new_val where P;

4. user permission operations (generally root permission is required)

1. view all existing users

Select user from mysql. user;

2. view the current user

Select user ();

3. Create a user

Create user user_name identified by 'passwd ';

In this case, a user named user_name can use passwd to log on to mysql, but the permission is limited to login, and nothing can be done after login. In this case, you must grant the relevant permissions before performing some operations.

If there is no identified by 'passwd', a user without a password will be created. to log on, just enter mysql-u user_name and press enter, without having to enter the password.

The effect of the above command is the same as that of the following command (the premise is to log on with root ):

Insert into mysql. user (Host, User, Password) values ('localhost', 'user _ name', password ('passwd '));

In essence, they modify the user table in mysql, a metadatabase of mysql, which records all the user information of mysql. Note that the second command has an additional Host parameter, which is default in the First Command, but it is essential in the second command. Otherwise, you will not be able to log on to the mysql of the local machine.

Of course, the Host value can be changed. For the second command, the modification is obvious. For the first command, if you do not want to select the default value 'localhost', you can do this:

Create user_name @ other_host identified by 'passwd ';

4. Grant related permissions to users

The permission range includes the classic "add, delete, modify, and delete" and all permissions. The overall format is as follows:

Grant privilege_list on database_name.table_name to user_name @ 'host _ name ';

Privilege_list can be one or several values in select/update/delete/insert (separated by commas), or all can be used directly to represent all permissions;

On limits permissions to a table in a database. Here, wildcard * can be used to represent all, such as database_one. * This means that all the tables in database_one have permissions. Of course, database_name can also be replaced;

To is followed by user_name, followed by @ 'host _ name' can be omitted, the default value is localhost, or wildcards % can be used to represent all hosts. Note that if user_name does not exist, this command will create a new user named user_name, which is another method for creating users, in addition, related permissions are granted. I generally use this method. If you need a password, you can add identified by 'passwd' to the end. Otherwise, the password is not used.