MySQL Common commands

• MySQL start service and exit service

net start MySQL startup MySQL

net stop MySQL stop MySQL

• MySQL Login and logout

Mysql-u User name-p password-p port number-H server address

Port number defaults to 3306 server address defaults to local server address

mysql-uroot-p123456-p3306-h127.0.0.1

Log out of MySQL for quit; Exit

• MySQL Setup prompt

Login MySQL after entering prompt xxxx, you can change the MySQL command line prompt to xxxx,\h represents the server address \u represents the user name \d represents the current table name \d Full date

PROMPT \[email protected]\u \d:;

• MySQL Statement specification

Keyword and function name all uppercase

Database name, table name, field name lowercase

SQL statement ends with a semicolon

For example, select USER (); SELECT VERSION; SELECT now ();

• Database creation

CREATE DATABASE IF not exsts db_name CHARACTER SET UTF8;

• View Database properties (character encoding, etc.)

SHOW CREATE DATABASE db_name;

• Changing the way the database is encoded

ALTER DATABASE db_name CHARACTER SET UTF8;

• Deleting a database

DROP DATABASE IF EXISTS db_name;

• Create a data table

CREATE TABLE IF not EXISTS table_name (

column_name data_type [Not NULL],

COLUMN_NAME data_type [NULL],

‘‘‘‘

);

For example: CREATE TABLE IF not EXISTS tb_1 (

Username VARCHAR () not NULL,

Age TINYINT UNSIGNED,

Salary FLOAT (8,2) UNSIGNED

);

• View data tables that already exist

SHOW TABLES [from database_name];

• View the structure of a data table

SHOW COLUMNS from Tb_name;

• Primary key, UNIQUE constraint, DEFAULT constraint

CREATE TABLE table_name (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR () not NULL UNIQUE KEY,

Sex ENUM (' 1 ', ' 2 ', ' 3 ') DEFAULT ' 3 ');

MySQL Common commands