Mysql Common commands

1. Connect to Mysql

Command: mysql-u <User Name>-p <password>

1. Connect to MYSQL on the local machine.

First, open the DOS window, enter the mysql \ bin directory, and then enter the mysql-u User Name)-p password). Press enter and you will be prompted to enter the password. note that there can be spaces or spaces before the user name, but there must be no spaces before the password. Otherwise, you can re-enter the password.

If you have just installed MYSQL, the Super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql>

2. Connect to MYSQL on the remote host.Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:
Mysql-h110.110.110.110-u root-p abcd123)

3. Exit MYSQL Command: Exit and press Enter)

2. Change the password

Command: mysqladmin-u <User Name>-p <password>

1. Add a password ab12 to the root user.
First, enter the mysql \ bin directory under DOS, and then type the following command
Mysqladmin-u root-password ab12
Note: because the root account does not have a password at the beginning, the old-p password can be omitted.

2. Change the root password to djg345.
Mysqladmin-u root-p ab12 password djg345

3. Add new users

Command: grant <permission> on * to <username> Identified by <password>

1. Add a user named "test1" with the password "abc" so that he 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 *. * to test1 @ "%" Identified by "abc ";

However, the added users are very dangerous. If someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. See solution 2.

2. Add a user named "test2" with the password "abc" so that the user can only log on to localhost and query, insert, modify, and delete the Database "mydb". localhost indicates the local host, that is, the host of the MYSQL database)In this way, the user knows the password test2 and cannot directly access the database from the internet. He can only access the database through the web page on the MYSQL host.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identified 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. * to test2 @ localhost identified by "";

4.1 create a database

Command: create database <database Name>

Example 1: Create a database named xhkdb
Mysql> create database xhkdb;

Example 2: Create a database and assign users

① Create database name;

② Grant select, INSERT, UPDATE, DELETE, CREATE, DROP, alter on database name. * TO database name @ localhost identified by 'Password ';

③ Set password for 'database name' @ 'localhost' = OLD_PASSWORD ('Password ');

Execute three commands in sequence to create a database. Note: You must set the Chinese "password" and "Database.

4.2 Display Database

Command: show databases Note: There is a last s)
Mysql> show databases;

Note: In order to stop displaying garbled characters, you need to modify the default database encoding. The following uses the GBK encoding page as an example:

1. Modify the MYSQL configuration file:Modify default-character-set = gbk in my. ini
2. modify the code during running:
① Java code: jdbc: mysql: // localhost: 3306/test? UseUnicode = true & characterEncoding = gbk
② PHP code: header ("Content-Type: text/html; charset = gb2312 ");
③ C language code: int mysql_set_character_set (MYSQL * mysql, char * csname );
This function is used to set the default character set for the current connection. The string csname specifies a valid Character Set Name. Connection proofreader is used as the default proofreader for character sets. This function works in a similar way as the set names statement, but it can also SET the value of mysql-> charset, thus affecting the character SET by mysql_real_escape_string.

4.3 database deletion command: drop database <database Name>
For example, delete a database named xhkdb.
Mysql> drop database xhkdb;

Example 1: delete a database that already exists
Mysql> drop database drop_database;
Query OK, 0 rows affected (0.00 sec)

Example 2: delete an uncertain Database
Mysql> drop database drop_database;
ERROR 1008 (HY000): Can't drop database 'drop _ database'; database doesn' t exist
// The 'drop _ database' database cannot be deleted because it does not exist.
Mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 warning (0.00 sec) // generates a warning indicating that this database does not exist
Mysql> create database drop_database;
Query OK, 1 row affected (0.00 sec)
Mysql> drop database if exists drop_database; // if exists is used to determine whether the database exists or not.
Query OK, 0 rows affected (0.00 sec) 4.4 database connection command: use <Database Name>

For example, if the xhkdb database exists, try to access it:
Mysql> use xhkdb;
On-screen prompt: Database changed

The use statement can be used to notify MySQL to use the db_name database as the default database) for subsequent statements.The database remains the default database until the end of the CIDR block or until a different USE statement is published:
Mysql> USE db1;
Mysql> select count (*) FROM mytable; # selects from db1.mytable
Mysql> USE db2;
Mysql> select count (*) FROM mytable; # selects from db2.mytable

Using the USE statement to mark a specific current database does not prevent you from accessing tables in other databases.The following example shows how to access the author table from the db1 database and edit the table from the db2 database:
Mysql> USE db1;
Mysql> SELECT author_name, editor_name FROM author, db2.editor
-> WHERE author. editor_id = db2.editor. editor_id;

The USE statement is set up to be compatible with Sybase.

Some netizens asked how to exit after the connection. In fact, you don't need to exit. After using the database, you can use show databases to query all databases. If you want to jump to another database, use
Use other database names
You can. 4.5 currently selected Database

Command: mysql> select database ();

The SELECT command in MySQL is similar to the print or write command in other programming languages. You can use it to display the results of a string, number, and mathematical expression. How to use the special features of the SELECT command in MySQL?

1. Display MYSQL version
Mysql> select version ();
+ ----------------------- +
| Version () |
+ ----------------------- +
| 6.0.4-alpha-community |
+ ----------------------- +
1 row in set (0.02 sec)

2. display the current time
Mysql> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 22:35:32 |
+ --------------------- +
1 row in set (0.04 sec)

3. Display year, month, and day
Select dayofmonth (CURRENT_DATE );
+ -------------------------- +
| DAYOFMONTH (CURRENT_DATE) |
+ -------------------------- +
| 15 |
+ -------------------------- +
1 row in set (0.01 sec)

Select month (CURRENT_DATE );
+ --------------------- +
| MONTH (CURRENT_DATE) |
+ --------------------- +
| 9 |
+ --------------------- +
1 row in set (0.00 sec)

Select year (CURRENT_DATE );
+ -------------------- +
| YEAR (CURRENT_DATE) |
+ -------------------- +
| 1, 2009 |
+ -------------------- +
1 row in set (0.00 sec)

4. Display strings
Mysql> SELECT "welecome to my blog! ";
+ ---------------------- +
| Welecome to my blog! |
+ ---------------------- +
| Welecome to my blog! |
+ ---------------------- +
1 row in set (0.00 sec)

5. When using a calculator
Select (4*4)/10) + 25;
+ ---------------------- +
| (4*4)/10) + 25 |
+ ---------------------- +
| 1, 26.60 |
+ ---------------------- +
1 row in set (0.00 sec)

6. String concatenation
Select CONCAT (f_name, "", l_name)
AS Name
From employee_data
Where title = 'marketing Executive ';
+ --------------- +
| Name |
+ --------------- +
| Monica Sehgal |
| Hal Simlai |
| Joseph Irvine |
+ --------------- +
3 rows in set (0.00 sec)
Note: The CONCAT () function is used to concatenate strings. In addition, we also use the previously learned AS to give the result column 'concat (f_name, "", l_name) 'A Kana.

5.1 create a data table

Command: create table <table Name> (<field name 1> <type 1> [,... <field name n> <type n>]);

For example, to create a table named MyClass,

Field name	Numeric type	Data width	Empty or not	Primary Key?	Auto-Increment	Default Value
Id	Int	4	No	Primary key	Auto_increment
Name	Char	20	No
Sex	Int	4	No			0
Degree	Double	16	Yes

Mysql> Create TableMyClass (
> Id int (4) not null Primary key auto_increment,
> Name char (20) not null,
> Sex int (4) not null Default'0 ',
> Degree double (5.2); get the table structure

Command: desc table name or show columns from Table Name

Mysql> desc MyClass;
Mysql> show columns from MyClass;

When using the desc Table Name of the MySQL database, there may be four values in the Key column, namely '', 'pri ', 'uni', and 'mul '.
1. If the Key is null, the column value can be repeated, indicating that the column has no index or a non-leading column with a non-unique composite index;
2. If the Key is PRI, the column is an integral part of the primary Key;
3. If the Key is UNI, this column is the first column (Leading column) of a unique value index, and does not contain NULL values );
4. if the Key is MUL, the values of this column can be repeated. This column is a non-unique index leading column (the first column) or a unique index component, but can contain NULL values.

If the definition of a column meets multiple of the above four conditions, for example, if a column is both PRI and UNI, when "desc table name" is used, the displayed Key value displays PRI-> UNI-> MUL by priority. Then, PRI is displayed.

A unique index column can be displayed as PRI, and the column cannot contain null values. At the same time, the table does not have a primary key.
A unique index column can be displayed as MUL. If multiple columns constitute a unique compound index, although the combination of multiple columns of the index is unique, for example, ID + NAME is unique, however, no separate column can still have duplicate values, as long as ID + NAME is unique.

5.3 delete a data table

Command: drop table <table Name>

For example, delete a table named MyClass.
Mysql> drop table MyClass;

Drop table is used to cancel one or more tables. You must have the DROP permission for each table. All table data and table definitions will be canceled, so be careful when using this statement!

Note: For a TABLE with partitions, drop table permanently cancels TABLE definitions, cancels partitions, and removes all data stored in these partitions. Drop table also cancels the partition definition. par file associated with the canceled TABLE.

For tables that do not exist, use if exists to prevent errors. When if exists is used, a NOTE is generated for each table that does not exist.

RESTRICT and CASCADE make partitioning easier. Currently, RESTRICT and CASCADE do not work.

5.4 insert data into a table

Command: insert into <Table Name> [(<field name 1> [,... <field name n>])] values (value 1) [, (value n)]

For example, insert two records into the MyClass table. The two records indicate that the result of Tom numbered 1 is 96.45, and the result of Joan numbered 2 is 82.99, wang, numbered 3, scored 96.5.
Mysql> insert into MyClass values (1, 'Tom ', 96.45), (2, 'job', 82.99), (2, 'wang', 96.59 );

Note: insert into can only insert one record into the table at a time.

5.5 query table data

1) query all rows
Command: select <Field 1, Field 2,...> from <Table Name> where <expression>
For example, you can view all data in the MyClass table.
Mysql> select * from MyClass;

2) query the first few rows of data
For example, view the first two rows of data in the MyClass table.
Mysql> select * from MyClass order by id limit 0, 2;

Select is generally used with where to query more precise and complex data.

5.6 Delete table data

Command: delete from table name where expression

For example, delete the record numbered 1 in MyClass.
Mysql> delete from MyClass where id = 1;

The following is a comparison between tables before and after data deletion.

FirstName	LastName	Age
Peter	Griffin in	35
Glenn	Quagmire	33

The following uses the PHP code as an example to delete all LastName = 'grigin' records in the "Persons" table:

<?php    $con = mysql_connect("localhost","peter","abc123");    if (!$con)    {      die('Could not connect: ' . mysql_error());    }    mysql_select_db("my_db", $con);    mysql_query("DELETE FROM Persons WHERE LastName='Griffin'"); mysql_close($con); ?>

After this deletion, the table is as follows:

FirstName	LastName	Age
Glenn	Quagmire	33

5.7 Modify Table Data

Syntax: update table name set field = new value ,... Where condition
Mysql> update MyClass set name = 'Mary 'where id = 1;

Example 1: MySQL UPDATE statement for a single table:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1 = expr1 [, col_name2 = expr2...] [WHERE where_definition] [order by...] [LIMIT row_count]

Example 2: UPDATE statements for multiple tables:
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1 = expr1 [, col_name2 = expr2...] [WHERE where_definition]

The UPDATE syntax uses the new value to UPDATE columns in the original table rows. The SET clause indicates the columns to be modified and the values to be given. The WHERE clause specifies the rows to be updated. If there is no WHERE clause, all rows are updated. If the order by clause is specified, the row is updated in the specified ORDER. The LIMIT clause is used to specify a LIMIT to LIMIT the number of rows that can be updated.

5.8 Add the field command: Alter tableTable Name AddOther field types;
For example, a passtest field is added to the MyClass table. The type is int (4) and the default value is 0.
Mysql> Alter tableMyClass AddPasstest Int (4 ) Default'0'

Add Index
Mysql> alter table name add index name (field name 1 [, field name 2…]);
Example: mysql> alter table employee add index emp_name (name );

Index with primary keywords
Mysql> alter table name add primary key (field name );
Example: mysql> alter table employee add primary key (id );

Add an index with unique conditions
Mysql> alter table name add unique index name (field name );
Example: mysql> alter table employee add unique emp_name2 (cardnumber );

Delete An index
Mysql> alter table Name drop index name;
Example: mysql> alter table employee drop index emp_name;

Add field:
Mysql> alter table table_name ADD field_name field_type;

Modify the original field name and type:
Mysql> alter table table_name CHANGE old_field_name new_field_name field_type;

Delete field:
MySQL alter table table_name DROP field_name; 5.9 ALTER table name command: rename TABLE original table name to new TABLE name;

For example, the MyClass name in the table is changed to YouClass.
Mysql> rename table MyClass to YouClass;

When you execute RENAME, you cannot have any locked table or activity transactions. You must also have the ALTER and DROP permissions for the original table and the CREATE and INSERT permissions for the new table.

If MySQL encounters any error in renaming multiple tables, it will rename all renamed tables and return everything to the initial state.

Rename table is added to MySQL 3.23.23. 6. Run the backup database command in the DOS [url = file: // \ mysql \ bin] \ mysql \ bin [/url] Directory.

1. Export the entire database
The exported files are stored in the mysql \ bin directory by default.
Mysqldump-u username-p Database Name> exported file name
Mysqldump-u user_name-p123456 database_name> outfile_name. SQL

2. Export a table
Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u user_name-p database_name table_name> outfile_name. SQL

3. Export a database structure
Mysqldump-u user_name-p-d-add-drop-table database_name> outfile_name. SQL
-D no data-add-drop-table add a drop table before each create statement

4. Export with language Parameters
Mysqldump-uroot-p-default-character-set = latin1-set-charset = gbk-skip-opt database_name> outfile_name. SQL

For example, back up the aaa database to the file back_aaa:
[Root @ test1 root] # cd/home/data/mysql

[Root @ test1 mysql] # mysqldump-u root-p -- opt aaa> back_aaa

7.1 a database and table creation instance 1 drop database if exists school; // Delete if SCHOOL exists
Create database school; // create a database SCHOOL
Use school; // open the SCHOOL library
Create table teacher // create table TEACHER
(
Id int (3) auto_increment not null primary key,
Name char (10) not null,
Address varchar (50) default 'shenzhen ',
Year date
); // Table creation ends

// Insert fields as follows
Insert into teacher values (", 'allen ', 'dalian Zhongyi', '2017-10-10 ′);
Insert into teacher values (", 'jack', 'dalian No. 2 middle school ', '2017-12-23 ′);

If you type the preceding command at the mysql prompt, debugging is not convenient.
1. You can write the above commands into a text file as they are, for example, school. SQL, then copy it to c :\\, and enter the directory [url = file: // \ mysql \ bin] \ mysql \ bin [/url], and then type the following command:
Mysql-uroot-p password <c: \ school. SQL
If it succeeds, no display is displayed for a blank row. If there is an error, a prompt is displayed. The preceding command has been debugged. You only need to remove the // annotation to use it ).

2. You can use mysql> source c: \ school. SQL after entering the command line. You can also import the school. SQL file to the database.
7.2 an instance of database creation and table creation 2

7.2 an instance of database creation and table creation 2

Drop database if exists school; // Delete if SCHOOL exists
Create database school; // create a database SCHOOL
Use school; // open the SCHOOL library
Create table teacher // create table TEACHER
(
Id int (3) auto_increment not null primary key,
Name char (10) not null,
Address varchar (50) default ''shenzhen '',
Year date
); // Table creation ends

// Insert fields as follows
Insert into teacher values (''', ''glengang '', ''shenzhen Zhongyi ''', ''2017-10-10 '');
Insert into teacher values (''', ''jack'', ''shenzhen No. 1 Middle School ''', ''2017-12-23 '');

Note: Table Creation
1. Set the ID to a numeric field with a length of 3: int (3), and enable it to automatically add auto_increment for each record; it cannot be blank: not null; in addition, set the primary key as the primary key.

2. Set NAME to a 10-character field

3. Set ADDRESS to a 50-character field, and the default value is Shenzhen.

4. Set YEAR as the date field.

This article is from the "IT technology sharing" blog. For more information, contact the author!