MySQL Common command Summary

One, database-level operations

1. Log in to MySQL

The commonly used format is Mysql-u user_name-p Press ENTER after entering the password again to enter.

2. View all databases that currently exist

show databases; (Note that semicolons cannot be omitted.) A few commands can ignore semicolons, but most MySQL commands must end with a semicolon)

3. Select Database

Once you know all the database names, you need to select a specific database to operate on, with the following commands:

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 privileges may be required here)

e.g.  Create database one; This will create a database named one.

5. Delete Database

DROP DATABASE database_name;

Second, table-level operation

1. View all table names for the current database

Show tables;

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

Describe table_name; (may also be abbreviated as: DESC table_name;)

Show columns from table_name;

Show CREATE TABLE table_name;

3. View a column in a table

Select column_1 from table_name;

If you want to see a few columns, you need to separate the column names with commas, such as: Select Column_1, column_2 from table_name;

You can also use the wildcard character * To view all columns, such as SELECT * FROM table_name;

4. Create a table

CREATE TABLE table_name (COLUMN_NAME1 type constraint, column_name2 type constraint, ..., Column_namex type constraint, PR Imary key (column_name));

The constraint here include NOT null (non-NULL), unique (cannot be duplicated), etc., is optional, that is, can not;

The type includes char (n) varchar (n) int numeric float real double precision; (note thatMySQL strings are enclosed in single quotes )

The last side of the primary key constraint is required, primary key () parentheses inside the property can be one, or can be separated by commas.

5. Delete a table

DROP TABLE table_name; (This will completely delete this table, which is show tables; the command will not show it again)

(Note a command close to it: delete from table_name; This command simply empties the table, but the table schema still exists, which is show tables; the command still shows it)

6. Add/Remove column names (attribute names) to the table

ALTER TABLE table_name ADD COLUMN_NAME type;

ALTER TABLE table_name DROP COLUMN_NAME;

7. Modify a column in a table

ALTER TABLE table_name change OLD_COL new_col type;

Third, row-level operation

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

INSERT INTO table_name values (...); (Note that the values inside the parentheses must correspond to the order of the columns when the table was created, separated by commas)

If you forget the order of the column names and bother 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);

At this time as long as the val_n corresponds to Col_n on the line, rather than the real order of the name of the tube.

2. Delete specific rows in the table

Delete from table_name where P;

P is a condition in which the general format is: Col_name = value, that is, the property equals one row of a value.

3, modify/Update line

UPDATE table_nameSet col_name = New_val where P;

Iv. User rights operations (typically requires root privileges)

1. View all existing users

Select User from Mysql.user;

2. View Current User

Select User ();

3. Create a user

Create user user_name identified by ' passwd ';

This creates a user named User_name, who can log in to MySQL using the password passwd, but its permissions are limited to landing only, after landing nothing will do. This requires that the permissions are assigned before certain actions can be made.

If there is no subsequent identified by ' passwd ', will create a user without password, login is just enter mysql-u user_name return, no need to enter the password.

The effect of the above command is the same as this one (provided that you log in as root):

Insert into Mysql.user (Host,user,password) VALUES (' localhost ', ' user_name ', Password (' passwd '));

They are essentially modifying the table user in MySQL's metabase MySQL, a table that records all the user information for MySQL. Notice that the second command has more than one parameter host, which is the default in the first command, but is necessary in the second, otherwise it will not be able to log on to the native MySQL.

Of course, the value of the host can be changed, for the second command, the change is obvious; for the first command, if you do not want to select the default value of ' localhost ', you can:

Create user_name@other_host identified by ' passwd ';

4, to give users the relevant rights

The scope of the permission includes the classic "Delete and remove", as well as representing all of them. The overall format is as follows:

Grant Privilege_list on database_name.table_name to [email protected] ' host_name ';

privilege_list can be one or several of the Select/update/delete/insert (comma separated), or directly with all instead, representing all permissions;

On is to restrict permissions on a table in a database, where wildcard characters * are used to represent all, such as database_one.* means that there are permissions on all tables in Database_one, and of course database_name can be substituted with *;

To follow the user_name, after the @ ' host_name ' can be omitted, the default value is localhost, you can also use the wildcard% to represent all hosts. Note that if User_name is not already present, this command will create a new user whose name is user_name, which is another way to create a user, and at the same time give the relevant permissions, I usually use this method. If you need a password, you can add identified by ' passwd ' in the back, otherwise there is no password user.

MySQL Common command Summary