Basic MySQL database operation commands and basic mysql database operations

Basic MySQL database operation commands and basic mysql database operations

1.Local ConnectionMySQLDatabase:

Mysql-h connection address (localhost)-u username (root)-p [Password] [database name];

For example, mysql-h localhost-u root-p123456 db_test (Note: there cannot be spaces after-p)

The password and database name can be left blank. When the password is not added, the system prompts you to enter the password. When the database name is not added, you must use the (use Database Name) statement to set the currently used database.

 

2.Remote connectionMySQLDatabase:

Mysql-h connection address (IP address)-p port number-u User Name (root)-p [Password] [database name];

The syntax is similar to that in 1. The default port is 3306.

 

3.Locally authorized remote connection User:

Before connecting a remote computer to a local computer, you must first use the local computer to authorize the remote computer. Otherwise, you do not have the permission to connect to the local MySQL database.

Grant permission 1, permission 2 ,... Permission n on Database Name table name to user name @ user address identified by 'Connection password ';

 

4.Database Operations:

4.1Export Database

Mysqldump-u username-p -- default-character-set = encoding Database Name> exported file name

The default database encoding is latin1.

4.2Import Database

Mysqldump-u username-p database name <imported file name

 

4.3Database Operations

• • Showdatabases; display the list of all databases in MySQL
  • Createdatabase <Database Name>; Create a database
  • Use <Database Name>; set the currently used database
  • Dropdatabase <Database Name>; delete a database
  • Select database (); view the currently used database
  • Showtables; displays all tables in the current database
  • Createdatabase cc default charset utf8 collate utf8_unicode_ci; Create a database with UTF8 to ensure normal insertion of Chinese data.

 

5.Table operations

• Createtable <Table Name> (<field name 1> <type 1> [,... <field name n> <type n>]); Create a table

Create table MyClass (

Id int (4) not null primary keyauto_increment,

Name char (20) not null,

Sex int (4) not null default '0 ',

Degree double (16, 2)

);

• Desc table name (or showcolumns from Table Name); get table structure
• Droptable <Table Name>; delete a table
• Altertable <Table Name> ...; Modify Table Information
• Insertinto <Table Name> [(<field name 1> [,... <field name n>])] values (value 1) [, (value n)]; Insert table data
• Mysqldump-u user name-p database name Table Name> exported file name; export a table, the exported file name is generally *. SQL File
• Select * or field list from table name where condition orderby sorting field; query table records
• Deletefrom table name where condition; Delete table records
• Update table name set field = value where condition; Modify Table records
• Altertable table name add field type others; add new fields to the table
• Renametable original table name to new table name; change table name
• Altertable table name default character set encoding method; modify the default character set of the table

6.Field Type

• INT [(M)] type: normal Integer type
• DOUBLE [(M, D)] [ZEROFILL] type: normal size (DOUBLE Precision) floating point number type
• DATE type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays DATE values in YYYY-MM-DD format, but allows you to assign values to the DATE column using strings or numbers
• CHAR (M): fixed-length string type. When stored, it is always filled with spaces to the right to the specified length.
• BLOBTEXT type, with a maximum length of 65535 (2 ^ 16-1) characters
• VARCHAR: variable-length string type

7.Change Password

Mysqladmin-u username-p old password New password

 

8.Back up database

Mysqldump -- optschool> school. bbb

Note: Back up the database school to the school. bbb file. school. bbb is a text file with any file name. Open it and you will find new discoveries.

 

9.Import and Export Databases

Mysqldump-u user name-p Password Database Name> export file name Export Database

Source import file name import database

 

10.Table Index

Alter table name add unique index name (column name); add unique index

Alter table name add index name (column name 1, column name 2 ,...); Add a common index (multiple or one column can be added)

Alter table name add fulltext index name (column name); add full-text index