Detailed explanation of mysql basic operation statement commands

1. Connect to MySQL

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

1. Connect to MYSQL on this machine.

First open a DOS window, then enter the directory mysql\bin, and then type the command mysql -u root -p. Press Enter and you will be prompted to enter your password. Note that there may or may not be a space before the user name, but there must be no space before the password, otherwise you will be asked to re-enter the password.

If MYSQL has just been installed, the super user root has no password, so just press Enter to enter MYSQL. The MYSQL prompt is: mysql>

2. Connect to MYSQL on the remote host. Assume that the IP of the remote host is: 110.110.110.110, the username is root, and the password is abcd123. Then type the following command:

mysql -h110.110.110.110 -u root -p 123;（注:u與root之間可以不用加空格，其它也一樣）

3. Exit MYSQL command: exit (press Enter)

2. Change password

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

1. Add a password ab12 to root.

First, enter the directory mysql\bin in DOS, and then type the following command

mysqladmin -u root -password ab12

Note: Because root has no password at the beginning, the -p old password item can be omitted.

2. Change the root password to djg345.

mysqladmin -u root -p ab12 password djg345

3. Add new users

Note: Unlike the above, the following commands are in the MYSQL environment, so they are followed by a semicolon as the command terminator.

Format: grant select on database.* to username@login host identified by "password"

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

grant select,insert,update,delete on *.* to test1@localhost Identified by “abc”;

But adding users is very dangerous. If someone knows the password of test1, he can log in to your MySQL database from any computer on the Internet and do whatever he wants with your data. See 2 for the solution.

2. Add a user test2 with password abc, so that he can only log in on localhost and query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located). In this way, even if the user knows the password of test2, he cannot access the database directly from the Internet and can only access it through the web page on the MYSQL host.

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;

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

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”;

4.1 Create a database

Note: Before creating a database, you must first connect to the Mysql server.

Command: create database <database name>

Example 1: Create a database named xhkdb

mysql> create database xhkdb;

Example 2: Create a database and assign a user

CREATE DATABASE database name;

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON databasename.* TO databasename@localhost IDENTIFIED BY 'password';

SET PASSWORD FOR 'database name'@'localhost' = OLD_PASSWORD('password');

Execute the three commands in sequence to complete the database creation. Note: The Chinese "password" and "database" need to be set by the user himself.

4.2 Display Database

Command: show databases (note: there is an s at the end)

mysql> show databases;

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

1. Modify the MYSQL configuration file: modify default-character-set=gbk in my.ini

2. Code runtime modification:

①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. The connection collation becomes the default collation for the character set. This function works like the SET NAMES statement, but it also sets the value of mysql->charset, thus affecting the character set set by mysql_real_escape_string().

4.3 Deleting a Database

Command: drop database <database name>

For example: delete the database named xhkdb

mysql> drop database xhkdb;

Example 1: Delete an existing database

mysql> drop database drop_database;

Query OK, 0 rows affected (0.00 sec)

Example 2: Deleting a database that does not exist

 mysql> drop database drop_database;

 ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist

  //An error occurred, cannot delete the 'drop_database' database, the database does not exist.

 mysql> drop database if exists drop_database;

 Query OK, 0 rows affected, 1 warning (0.00 sec)//Generate a warning indicating that the database does not existmysql> create database drop_database;

 Query OK, 1 row affected (0.00 sec)

 mysql> drop database if exists drop_database; //if exists determines whether the database exists. If it does not exist, no error is generated. Query OK, 0 rows affected (0.00 sec)

4.4 Connecting to the Database

Command: use <database name>

For example, if the xhkdb database exists, try to access it:

mysql> use xhkdb;

Screen prompt: Database changed

The use statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. This database remains the default database until the end of the session, or until a different USE statement is issued:

 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 can access the authors table from the db1 database and the edits 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 was created for compatibility with Sybase.

Some netizens asked how to log out after connecting. 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, just use the name of another database.

4.5 Currently selected database

Command: mysql> select database();

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

1. Display the version of MYSQL

mysql> select version();

+-----------------------+

| version() |

+-----------------------+

| 6.0.4-alpha-community |

+-----------------------+

1 row in set (0.02 sec)

2. Display current time

mysql> select now();

+---------------------+

| now() |

+---------------------+

| 2009-09-15 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) |

+--------------------+

| 2009 |

+--------------------+

1 row in set (0.00 sec)

4. Displaying a string

mysql> SELECT "welcome to my blog!";

+----------------------+

| welecome to my blog! |

+----------------------+

| welecome to my blog! |

+----------------------+

1 row in set (0.00 sec)

5. Use as a calculator

select ((4 * 4) / 10 ) + 25;

+----------------------+

| ((4 * 4) / 10 ) + 25 |

+----------------------+

| 26.60 |

+----------------------+

1 row in set (0.00 sec)

6. Concatenating Strings

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 here to concatenate strings. In addition, we also used the AS we learned previously to give the result column 'CONCAT(f_name, " ", l_name)' a pseudonym.

5.1 Create a data table

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

For example, create a table called MyClass.

mysql> create table MyClass(

> id int(4) not null primary key auto_increment,

> name char(20) not null,

> sex int(4) not null default '0',

> degree double(16,2));

5.3 Deleting a Data Table

Command: drop table <table name>

For example: delete the table named MyClass

mysql> drop table MyClass;

DROP TABLE is used to drop one or more tables. You must have the DROP privilege for each table. All table data and table definitions will be canceled, so use this statement with caution!

Note: For a partitioned table, DROP TABLE permanently cancels the table definition, deletes the partitions, and deletes all data stored in those partitions. DROP TABLE also drops any partition definition (.par) files associated with the dropped 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 can make partitioning easier. Currently, RESTRICT and CASCADE have no effect.

5.4 Insert data into 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. These two records indicate that the score of Tom, numbered 1, is 96.45, the score of Joan, numbered 2, is 82.99, and the score of Wang, numbered 3, is 96.5.

 mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

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

5.5 Querying Data in a Table

1) Query all rows

Command: select <field1, field2, ...> from <table name> where <expression>

For example: 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 Deleting data from a table

Command: delete from table name where expression

For example: Delete the record numbered 1 in the MyClass table

mysql> delete from MyClass where id=1;

Below is a comparison of the table before and after deleting data.

The following PHP code is used as an example to delete all records in the "Persons" table where LastName='Griffin':

<?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 looks like this:

5.7 Modify data in the table

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 statement for multiple tables:

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

The UPDATE syntax can update columns in an existing table row with new values. The SET clause indicates which columns are to be modified and what values ​​they are to be given. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If an ORDER BY clause is specified, the rows are updated in the order specified. The LIMIT clause is used to give a limit on the number of rows that can be updated.

5.8 Add fields <br /> Command: alter table table name add field type other;

For example, a field passtest is added to the table MyClass, the type is int(4), and the default value is 0

mysql> alter table MyClass add passtest int(4) default '0'

Add index

mysql> alter table table name add index index name (field name 1 [, field name 2 ...]);

Example: mysql> alter table employee add index emp_name (name);

Add the primary keyword index

mysql> alter table 表名add primary key (字段名);

Example: mysql> alter table employee add primary key(id);

Add unique restriction index

mysql> alter table 表名add unique 索引名(字段名);

Example: mysql> alter table employee add unique emp_name2(cardnumber);

Delete an index

mysql> alter table 表名drop index 索引名;

Example: mysql>alter table employee drop index emp_name;

Add fields:

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;

To delete a field:

MySQL ALTER TABLE table_name DROP field_name;

5.9 Modify table name

Command: rename table original table name to new table name;

For example: Change the name of the table MyClass to YouClass

mysql> rename table MyClass to YouClass;

You cannot have any locked tables or active transactions when you perform a RENAME. You must also have ALTER and DROP permissions on the original table, and CREATE and INSERT permissions on the new table.

If MySQL encounters any errors during a multi-table rename, it will perform a rollback rename of all renamed tables, returning everything to its original state.

RENAME TABLE was added in MySQL 3.23.23.

6. Back up the database

The command is executed in the DOS directory [url=file://\\mysql\\bin]\\mysql\\bin[/url]

1. Export the entire database

The export file is 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 username -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 沒有數據–add-drop-table 在每個create語句之前增加一個drop table

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 library 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 An example of building a database and a table 1

drop database if exists school; //如果存在SCHOOL則刪除

create database school; //建立庫SCHOOL

use school; //打開庫SCHOOL

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
); //End of table creation

//The following is the inserted field
insert into teacher values(”,'allen','大連一中','1976-10-10′);
insert into teacher values(”,'jack','大連二中','1975-12-23′);

If you type the above command at the mysql prompt, it will work, but it is not convenient for debugging.

1. You can write the above command as is into a text file, assuming it is school.sql, and then copy it to c:\\, and enter the directory [url=file://\\mysql\\bin]\\mysql\\bin[/url] in DOS state, and then type the following command:

mysql -uroot -p密碼< c:\\school.sql

If successful, a line will be left blank without any display; if there is an error, there will be a prompt. (The above commands have been debugged, you just need to remove the // comments to use them).

2. Or enter the command line and use mysql> source c:\\school.sql; to import the school.sql file into the database.

7.2 An example of building a database and table 2

drop database if exists school; //If SCHOOL exists, delete it create database school; //Create database SCHOOL

use school; //Open library SCHOOL

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
); //End of table creation//The following is to insert fields insert into teacher values('''',''glchengang'',''深圳一中'',''1976-10-10'');

insert into teacher values('''',''jack'',''Shenzhen No.1 Middle School'',''1975-12-23'');

Note: In the construction table

1. Set ID to a numeric field with a length of 3: int(3); and let it automatically increase by one for each record: auto_increment; it cannot be empty: not null; and make it the primary key field.

2. Set NAME to a character field with a length of 10

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

4. Set YEAR as the date field.

I hope this article can help friends in need