Shulou(Shulou.com)05/31 Report--

This article is to share with you about what commands MySQL has. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Connect Mysql

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

1. Connect to the MYSQL on this machine.

First open the DOS window, then enter the directory mysql\ bin, and then type the command mysql-u root-p. Enter and prompt you for your password.

Note: there can be spaces or no spaces before the user name, but there must be no spaces before the password, otherwise you will be asked to re-enter the password.

If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by pressing enter. The prompt for MYSQL is: mysql >

2. Connect to the MYSQL on the remote host. Assuming that the IP of the remote host is 192.168.1.110, the user name is root, and the password is abcd123, type the following command:

$mysql-h292.168.1.110-u root-p 123; (note: there is no space between u and root, and so do others)

3. Exit MYSQL command: exit (enter)

2. Change the 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: since root does not have a password at the beginning, the-p old password can be omitted.

2. Change the password of root to djg345.

$mysqladmin-u root-p ab12 password djg345

3. Add new users

Note: unlike the above, the following is a command in the MYSQL environment, so it is followed by a semicolon as the command Terminator

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

1. Add a user's test1 password to abc, so that he can log in on any host and have the authority to query, insert, modify and delete all databases.

First connect to MYSQL with the root user, and then type the following command:

$grant select,insert,update,delete on *. * to test1@ "%" Identified by "abc"

But the addition of users is very dangerous. If someone knows the password of test1, he can log in to your mysql database on any computer on internet and do whatever he or she wants with your data. See 2 for the solution.

2, add a user's test2 password to abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that even if the user knows the test2 password, he cannot access the database directly from the internet, only through the web page on the MYSQL host.

$grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"

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

$grant select,insert,update,delete on mydb.* to test2@localhost identified by ""

4. Operate the database 4.1 create the database

Note: connect to the Mysql server before creating the database

Command: create database

Example 1: set up a database called hkdb

Mysql > create database hkdb

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 above three commands in turn to complete the database creation.

Note: Chinese "password" and "database" are set by users themselves.

4.2 display database

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

Mysql > show databases

Note: in order not to garble when displaying, modify the database default code. Take the GBK encoding format as an example to illustrate:

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

2. Modify the code at run time:

① Java Code: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk

② PHP Code: header ("Content-Type:text/html;charset=gb2312")

③ C code: int mysql_set_character_set (MYSQL * mysql, char * csname)

This function sets the default character set for the current connection. The string csname specifies a valid character set name. Connection proofreading becomes the default proofreading of the character set. This function works similar to the SET NAMES statement, but it can also set the value of mysql- > charset, thus affecting the character set set by mysql_real_escape_string ().

4.3 Delete the database

Command: drop database

For example: delete the database named xhkdb

Mysql > drop database xhkdb

Example 1: delete a database that has been determined to exist

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

/ / an error occurred and the 'drop_database' database cannot be deleted. 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 determines whether the database exists, does not exist and does not generate an error

Query OK, 0 rows affected (0.00 sec)

4.4 connect to the database

Command: use

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

Mysql > use hkdb

Screen Tip: Database changed

The use statement can tell MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default database until the end of the paragraph 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 user statement to mark a specific current database does not 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 statements are set up to be compatible with Sybase.

Some netizens asked how to exit after the connection. In fact, there is no need to check out, after use database, you can use show databases to query all databases. If you want to jump to other databases, use

Use other database names

Just do it.

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 the results of a string, number, mathematical expression, and so on. How do I use the special features of the SELECT command in MySQL?

1. Display the version of MYSQL

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.5-m15 |

+-+

1 row in set (0.00 sec)

two。 Show current time

Mysql > select now ()

+-+

| | now () |

+-+

| | 2016-05-03 11:49:39 |

+-+

1 row in set (0.06 sec)

3. Show year, month and day

SELECT DAYOFMONTH (CURRENT_DATE)

+-+

| | DAYOFMONTH (CURRENT_DATE) |

+-+

| | 3 |

+-+

1 row in set (0.01 sec)

SELECT MONTH (CURRENT_DATE)

+-+

| | MONTH (CURRENT_DATE) |

+-+

| | 5 |

+-+

1 row in set (0.00 sec)

SELECT YEAR (CURRENT_DATE)

+-+

| | YEAR (CURRENT_DATE) |

+-+

| | 2016 |

+-+

1 row in set (0.00 sec)

4. Display string

Mysql > SELECT "welecome to my blog!"

+-+

| | welecome to my blog! |

+-+

| | welecome to my blog! |

+-+

1 row in set (0.00 sec)

5. Used as a calculator

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

+-+

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

+-+

| | 26.60 |

+-+

1 row in set (0.00 sec)

6. Concatenated 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 used the AS we learned before to give the result column 'CONCAT (f_name, ", l_name) a pseudonym.

5. Operation data sheet

5.1 create a data table

Command: create table ([,.. ])

For example, create a table called MyClass

Field name, numeric type, data width is empty, whether the primary key automatically increases the default value idint4 No primary keyauto_incrementnamechar20 No sexint4 No 0degreedouble16 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 (16Pol 2))

5.2 Delete the data table

Command: drop table

For example: delete a table named MyClass

Mysql > drop table MyClass

DROP TABLE is used to cancel one or more tables. You must have DROP permission for 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 permanently undefines the table, cancels the partitions, and cancels all data stored in those 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 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 are not working.

5.3 Table insert data

Command: insert into [[,.. ])] Values (value 1) [, (value n)]

For example, insert two records into the table MyClass, which indicate that the score of Tom with number 1 is 96.45, that of Joan with number 2 is 82.99, and that of Wang with number 3 is 96.5.

Mysql > insert into MyClass values (1), (2), (2)

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

5.4 query the data in the table

1), query all rows

Command: select from

< 表名 >

Where

< 表达式 >

For example: view all data in table MyClass

Mysql > select * from MyClass

2) query the first few rows of data

For example: view the first two rows of data in table MyClass

Mysql > select * from MyClass order by id limit 0Pol 2

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

5.5 deleting data from the table

Command: delete from table name where expression

For example: delete the record numbered 1 in table MyClass

Mysql > delete from MyClass where id=1

The following is a comparison of tables before and after deleting data.

FirstNameLastNameAgePeterGriffin35GlennQuagmire33

Take the PHP code as an example to delete all LastName='Griffin' records in the "Persons" table:

After this deletion, the table looks like this:

FirstNameLastNameAgeGlennQuagmire33

5.6 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: multi-table UPDATE statement:

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

The UPDATE syntax can update columns in existing table rows with new values. The SET clause indicates which columns to modify and which values to give. 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, the 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.7 add field

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'

Indexing

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)

Index with unique restrictions

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 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

Delete a field:

MySQL ALTER TABLE table_name DROP field_name

5.8 modify table name

Command: rename table original table name to new table name

For example: change the name of MyClass to YouClass in the table

Mysql > rename table MyClass to YouClass

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have ALTER and DROP permissions on the original table, as well as CREATE and INSERT permissions on the new table.

If MySQL encounters any errors in multiple table renaming, it will retrograde 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\ mysql\ bin directory of DOS

1. Export the entire database

Export files are stored in the mysql\ bin directory by default

Mysqldump-u user name-p database name > exported file name

Mysqldump-u user_name-p123456 database_name > outfile_name.sql

two。 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 adds a drop table before each create statement

Note: if you only want to export data and do not need a table statement, use the-t option.

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. Instance 7.1 instance 1

Drop database if exists school; / / delete if SCHOOL exists

Create database school; / / build 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 insert 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')

It's OK if you type the above command at the mysql prompt, but it's not easy to debug.

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

Mysql-uroot-p password

< c:\\school.sql 如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。 2、或者进入命令行后使用 mysql>

Source c:\\ school.sql; can also import school.sql files into the database.

7.2 instance 2

Drop database if exists school; / / delete if SCHOOL exists

Create database school; / / build 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 insert 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 construction of the table

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

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

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

4. Set YEAR to the date field.

Thank you for reading! This is the end of this article on "what are the commands of MySQL?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.