Home >Database >Mysql Tutorial >MySQL command collection (collection)

MySQL command collection (collection)

PHPzOriginal: 2017-04-04 14:18:181438browse

The following is a collection of MySQL commands that I have collected, which is very practical!

1. Connect to Mysql
Format: mysql -h host address -u username -p user password

1. Connect to MYSQL on this machine.
First open the DOS window, then enter the directory mysql\bin, and then type the command mysql -u root -p. After pressing Enter, you will be prompted to enter the password. Note that the user name may or may not have spaces before it, but there must be no spaces before the password. , otherwise you will be asked to re-enter your password.

If MYSQL has just been installed, the super user root does not have a 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 user name is root, and the password is abcd123. Then type the following command:
mysql -h110.110.110.110 -u root -p 123; (Note: There is no need to add a space between u and root, and the same applies to others)

3. Exit the MYSQL command : exit (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 under DOS, and then type the following command
mysqladmin -u root -password ab12
Note: Because root does not have a password at the beginning, the -p old password item can be omitted. .

2. Then change the root password to djg345.
mysqladmin -u root -p ab12 password djg345
3. Add new users
Note: Unlike the above, the following are commands 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 the password abc so that he can log in on any host. And has query, insert, modify, and delete permissions for all databases. First connect to MYSQL as the root user, and then type the following command:
grant select,insert,update,delete on . to [email=test1@”%]test1@”%[/email]” Identified by “abc”;

But adding users is very dangerous. If you want someone to know the password of test1, then he can log in to your mysql database on any computer on the Internet and You can do whatever you want with your data. See solution 2. Add a user test2 with a password of abc so that he can only log in on localhost and query, insert, and modify the database mydb. Delete operation (localhost refers to the local host, that is, the host where the MYSQL database is located), so that even if the user knows the password of test2, he cannot directly access the database 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 database

Note: You must first connect to the Mysql server before creating the database

Command: create database

Example 1: Create a database named xhkdb

mysql> create database xhkdb;

Example 2: Create a database and assign users

①CREATE DATABASE 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 the 3 commands in sequence to complete the database creation. Note: Chinese "Password" and "Database" need to be set by the user himself.

4.2 Show databases

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

Note: In order to no longer display garbled characters, the default encoding of the database must be modified. . 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. Modify when the code is 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. The concatenated collation becomes the default collation for the character set. This function works similarly to the SET NAMES statement, but it also sets the value of mysql- > charset, thereby affecting the character set set by mysql_real_escape_string(). 4.3 Delete database
Command: drop database
For example: delete a database named xhkdb
mysql> drop database xhkdb;

Example 1: Delete a The database that has been determined to exist

mysql> drop database drop_database;

Query OK, 0 rows affected (0.00 sec)

Example 2: Delete 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. The 'drop_database' database cannot be dropped. 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 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 determines whether the database exists. If it does not exist, no error will be generated.
Query OK, 0 rows affected (0.00 sec)
4.4 Connect to the database
Command: use

For example: If the xhkdb database exists, try to access it:
mysql> use xhkdb;
Screen prompt: Database changed

The use statement can notify 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 segment, 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

Use the USE statement to mark a specific current database, no Will prevent you from accessing tables in other databases. The following example accesses the author table from the db1 database and the edit 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;

USE statement is set up for compatibility with Sybase.

Some netizens asked how to exit after connecting. In fact, there is no need to exit. After using the database, you can query all databases using show databases. If you want to jump to other databases, just use
use other database names
.
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 , numbers, results of mathematical expressions, etc. How to use the special features of the 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)

Display the current time
mysql> select now();
+————————+
| now() |
+————————+
| 2009-09-15 22:35:32 |
+————————+
1 row in set (0.04 sec)
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)

Display string
mysql> SELECT “welcome to my blog!”;
+————————-+
| welcome to my blog! |
+————————-+
| welcome to my blog! |
+————————-+
1 row in set (0.00 sec)
Use it as a calculator
select ((4 4) / 10 ) + 25;
+————————-+
| ((4 4) / 10 ) + 25 |
+————————-+
| 26.60 |
+————————-+
1 row in set (0.00 sec)
Concatenation string
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 before to give the result column ‘CONCAT(f_name, ” “, l_name)’ a pseudonym.
5.1 Create a data table
Command: create table
( [,.. ]);

For example, create a table named MyClass,
Field name numeric type data width Whether it is empty Whether the primary key automatically increases the default value
id int 4 No primary key auto_increment
name char 20 no
sex int 4 no0
degree double 16 yes
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 Delete data table
Command: drop table
For example: delete the table named MyClass
mysql> drop table MyClass;
DROP TABLE is used to cancel one or more tables. You must have DROP permission on each table. All table data and table definitions will be canceled, so be careful when using this statement!
Note: For a partitioned table, DROP TABLE will permanently cancel the table definition, cancel each partition, and cancel all data stored in these partitions. DROP TABLE also cancels the partition definition (.par) file associated with the dropped table.
For tables that do not exist, use IF EXISTS to prevent errors. When using IF EXISTS, a NOTE is generated for each table that does not exist.
RESTRICT and CASCADE can make partitioning easier. Currently, RESTRICT and CASCADE don't work.
5.4 Insert data into table
Command: insert into

[(
[,..
])] values ( value 1 )[, ( Value n )]

For example: Insert two records into the table MyClass. These two records represent: the score of the person named Tom with the number 1 is 96.45, and the score of the person named Joan with the number 2 is 82.99. A 3 named Wang scored 96.5.
mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

Note: insert into every Only one record can be inserted into the table at a time.
5.5 Query the data in the table
1), query all rows
Command: select from < table name> where < expression>
For example: View all data in the table MyClass
mysql> select * from MyClass;

2), query the first few rows of data
For example: View the first 2 rows of data in the table MyClass
mysql> select * from MyClass order by id limit 0,2;

select is generally used in conjunction with where to query more precise and complex data.
5.6 Delete data in the table

Command: delete from table name where expression

For example: delete the record numbered 1 in the table MyClass
mysql> delete from MyClass where id =1;

The following is a comparison of the table before and after deleting data.
FirstName LastName Age
Peter Griffin 35
Glenn Quagmire 33
The following uses PHP code as an example to delete all records with LastName='Griffin' in the "Persons" table:
$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:
FirstName LastName Age
Glenn Quagmire 33

5.7 Modify the 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]

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

5.8 Add fields
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), 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 index of the primary key
mysql> alter table table name add primary key (field name);
Example: mysql> alter table employee add primary key(id);

Add unique restrictive index
mysql> alter table table name add unique index name (field name);
Example: mysql> ; alter table employee add unique emp_name2(cardnumber);

Delete an index
mysql> alter table table name drop index index name;
Example: mysql>alter table employee drop index emp_name;

Add a 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 Modify table name
Command: rename table original table name to new table name;

For example: in the table MyClass name Change to YouClass
mysql> rename table MyClass to YouClass;

When you perform RENAME, you cannot have any locked tables or active transactions. 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 multi-table renaming, it will perform a backward renaming of all renamed tables, returning everything to its original state.

RENAME TABLE was added in MySQL 3.23.23.
6. Backup database
The command is executed in the [url=file://\mysql\bin]\mysql\bin[/url] directory of DOS

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 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 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 creating a database and a table 1
drop database if exists school; //If SCHOOL exists, delete it
create database school; //Create the library SCHOOL
use school; //Open the 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 are the inserted fields
insert into teacher values(”,'allen','Dalian No. 1 Middle School', '1976-10-10′);
insert into teacher values(”,'jack','Dalian No. 2 Middle School','1975-12-23′);

If you are at the mysql prompt Typing the above command is also possible, but it is inconvenient for debugging.
1. You can write the above command as it is into a text file, assuming it is school.sql, then copy it to c:\, and enter the directory [url=file://\mysql\bin] in DOS state \mysql\bin[/url], and then type the following command:
mysql -uroot -ppassword< c:\school.sql
If successful, leave a blank line and nothing will be displayed; if there is an error, there will be hint. (The above command has been debugged, you only need to remove the // comment to use it).
2. Or use mysql> source c:\school.sql; after entering the command line to import the school.sql file into the database.

7.2 An example of creating a database and a table 2
drop database if exists school; //If SCHOOL exists, delete it
create database school; //Create database SCHOOL
use school; //Open the library SCHOOL
create table teacher //Create the 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 are the inserted fields
insert into teacher values(””,”glchengang”, "Shenzhen No. 1 Middle School","1976-10-10");
insert into teacher values("","jack","Shenzhen No. 1 Middle School","1975-12-23");

Note: In the process of building the table
1. Set the ID to a numeric field with a length of 3: int (3); and let each record automatically increase by one: auto_increment; and it cannot be empty: not null; and let He becomes the primary key of the main field.

2. Set NAME to a character field of length 10

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

4. Set YEAR as the date field
The above is the detailed content of MySQL command collection (collection). For more information, please follow other related articles on the PHP Chinese website!
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Four methods for phpmyadmin to export TrojansNext article：Four methods for phpmyadmin to export Trojans
Related articles
See more
Public welfare online PHP training，Help PHP learners grow quickly！
About us Disclaimer Sitemap
© php.cn All rights reserved

MySQL command collection (collection)

Related articles