MySQL Learning--basics

This article is the basic knowledge of MySQL.

Linux startup mysql service command:

Service MySQL Start

Linux shutdown mysql service command:

Service MySQL Stop

Log in to MySQL command:

Mysql-u < user name >

To view a database command:

show databases;

Switch Database command:

Use < database name >[;]

View Table Commands:

Show tables;

To exit the MySQL command:

Quit/exit

To create a database command:

Create databases < database name >;

New Data Table command:

CREATE table < table name >

(Column name 1 data type (data length),

............

Column name n data type (data length)

);

Execute SQL File command:

SOURCE <sql file Path >;

MySQL data type

Data type	Size (bytes)	Use	Format
Int	4	Integer
Float	4	Single-precision floating-point number
Double	4	Double-precision floating-point number
Enum		Enumeration, single-selection	ENUM (' A ', ' B ')
Set		Multi-Select	Set (' 1 ', ' 2 ', ' 3 ')
Date	3	Date	Yyyy-mm-dd
Time	3	Point-in-time or duration	YYYY-MM-DD Hh:ms:ss
Year	1	Year value	yyyy
Char	0-255	Fixed length string
varchar	0-255	Variable length string
Text	0-65535	Long Text data

In addition, there are tinyint, smallint, Mediumint, bigint.

Char differs from varchar:

Char fixed length, filled when the character is not enough, varchar is longer, no padded empty character

Enum differs from set:

Enum is defined as one by one enum; set is multi-selected

MySQL Constraint classification:

Constraint type	Primary key	Default value	Only	FOREIGN key	Non-empty
Key words	PRIMARY KEY	DEFAULT	UNIQUE	FOREIGN KEY	Not NULL

PRIMARY KEY constraint command:

< column name > < data type >[data length] PRIMARY KEY

Or

CONSTRAINT < primary Key name > PRIMARY key (< column name 1>,..., < column name n>)

Default value constraint command:

< column name > < data type >[data length] Default < defaults >

Unique constraint command:

UNIQUE < (column name) >

FOREIGN KEY Constraint command:

CONSTRAINT < foreign key > FOREIGN key < (column name) > REFERENCES < table name >< (column name) >

Non-null constraint command:

< column name > < data type >[data length] not NULL

Insert Data command:

INSERT into < table name > (< column name 1>,..., < column name n>) values

(value 1,..., value N),

............

(value 1,..., value n);

To delete a library command:

Drop Database < library name >;

To delete a table command:

DROP table < table name >;

Query command:

Select < column name n> from < table name > where < constraints >;

Mathematical symbol conditions:

= < > <= >= <>! =

And and

OR OR

Between ...   And ... Between the two

In within range

Not in out of range

Wildcard characters:

_ (Underscore): represents an unspecified character, which occupies a

%: Indicates indefinite unspecified character

Use with like

Results sorted by:

ORDER BY [< Keywords >];

ASC: Ascending (default)

DESC: Descending

Built-in functions:

Count	Sum	Avg	Max	Min
Count	Sum	Averaging	Maximum Value	Minimum value

Count can be used for any data type; Sum and AVG are used for numeric class data types; Max and min are used for numeric, character, datetime types

As: Aliases

Sub-query

nested queries

Connection Query command

Jion ...

Left join (affects right table): Left jion ... on ...

Right connection (affects left table): Jion ...

Connections that affect both sides: inner jion ... on ...

Table Rename Command

Rename table < tables name > to < table new names >;

ALTER TABLE < table formerly known as > Rename < table new name >;

ALTER TABLE < table formerly known as > Rename to < table new name >;

Add Column command

ALTER TABLE < table name > add [Column] < column name > < data type > < constraint >;

or ALTER TABLE < table name > Add < Column name > < data type > < constraint >;

Delete Column command

ALTER TABLE < table name > drop [column] < column name >;

Rename naming commands

ALTER TABLE < table name > change < column formerly > < column New name > < data type > < constraint >;

Modify Data Type command

ALTER TABLE < table name > Modify < Column name > < new data type >;

Modify Value command

Update < table name > set column 1= value 1,..., column n= value n where < condition >;

Delete Row Record command

Delete from < table name > where < condition >;

CREATE INDEX command

ALTER TABLE < table name > Add index < index name > (< column name >);

or CREATE index < index name > on < table name > (column name);

View Index Commands

Show index from < table name >;

Create a View command

Create View < view name > (column 1,..., column n) as select column 1,..., column n from < table name >;

Import command

Import data from a file into a single table

Load data infile < ' file path and file name ' > into table < table name >;

Export command

Save the data in a table to a file

Select column 1,..., column n into outfile < ' file path and file name ' > from < table name >;

Database backup command

Mysqldump-u < username, such as root> < database name > > < file path and filename >;

Data Table Backup command

Mysqldump-u < username, such as root> < database name > < table name > > < file path and file name >;

Data Recovery command

Source < file path and filename >;

or Mysql-u < username, such as root> < database name > < < file path and filename >;

MySQL Learning--basics