MySQL database common words

Description : The following words may have multiple meanings , This document is intended to be interpreted only for programming . words are not much, practice makes perfect, read and write every day, the effect is good. If there are errors , Please understand and point out , thank you !

Gold Super

2017-4-7

Connection MySQL Database :

Mysql-u Username-p password (default username root default password empty, only for integrated environment)

Start Stop MySQL

Start service: net start MySQL

Stop service: net stop MySQL

Exit MySQL \q exit

Sets the character set set names Gbk/utf8

Table details DESC table name

SQL Statement

Database operations

1, display the database "show databases;"

2. Create DATABASE "exists ' data name ' Charset=utf8;"

3. Delete database "Drop DB if exists ' database name ';"

4, select the database "use" database name;

Create Database Library Name

Set the character set CREATE database name CharSet UTF8

Show Database

Show database like '% ';

Show database creation statement show CREATE database name

Use database

• Modify database Alter library name CharSet UTF8 | | Change ['? lt?]
• Delete a database drop databases name | | To fall or stop [drɑp]

Table Operations

Create a table

CREATE TABLE table name (

Field 1 field type [null| NOT NULL] [default value], | | System default values

Field 2 field type [null| NOT NULL] [default value],

Field 3 field type [null| NOT NULL] [default value]

........

);

Note:

1, the last one do not comma, or will error

2. Because the field in the table needs to be increased (auto_increment) Note that the primary key (primary key) is added to the self-increment

3. Data type ['?? Kr?m (?) NT] Primary, basic [' Pra?m?ri]

Storage Engine MyISAM InnoDB

Show tables

Show tables like '% ';

Show creation statement show create TABLE name \g or \g

Create table table name like Library name. Table Name

Modified table name (two methods) rename table name to new table name ALTER TABLE table name rename new table name

Alter Table name Add column field name (age) type (int) [first]

Modify field name ALTER TABLE name change old name New name

Modify property ALTER TABLE name modify field name (age) varchar | | Modify [' Mɑd?fa?]

Delete field ALTER TABLE name drop field name

DROP table Name

Table details DESC table name

Table Properties Engine CharSet Collate | | [' End?? N] engine, engine

Integral type          

Minimum integer tinyint (m) 1-byte range ( -128~127)

Small integer smallint (m) 2-byte range ( -32768~32767)

Medium integral type Mediumint (m) 3 byte range ( -8388608~8388607)

Integral type, default one int (m) 4 byte range ( -2147483648~2147483647)

Large integer bigint (m) 8-byte range (18-square-+-9.22*10)

String type

char (n) fixed length, up to 255 characters

varchar (n) fixed length, up to 65,535 characters

Text variable length, up to 65,535 characters

Type Summary:

1, if the data is stored in the integer type, the specific use of who according to demand

2. VarChar saves space than Char, but Char performs more efficiently than varchar

Inserting Data      

Syntax: INSERT into table name (Field 1, Field 2, Field 3,... Values (value 1, value 2, value 3,.... ）;

Summarize:

0. The field must correspond to the value one by one

1, the field can not write, but the value must be written in full

2, the field can not be the same as the table structure (note: field write several values must also correspond)

Delete data

Syntax: delete from indicates [where condition]

Description

1, do not write where conditions will be deleted all

modifying data

Syntax: Update indicates set field name 1 = field value 1, field 2 = field value 2 [where condition]; | | [,? p ' det]

Description

1. Do not add where will be all modified

2, modify multiple fields need to be separated by commas ","

Find data

Syntax: SELECT * from indicates [where condition] [order BY field name Asc/desc] [limit start position 0, number of bars]

Description

1. Where is the limit query condition

2. ORDER BY order

3, limit display bar number limits

Sql:struct Query Language | | [' Kw?ri] Query

Mysql:

Host: Hosts

User: Users

Password: password

Data:

Database: Databases

Table: Tables

Column: Columns | | [' kɑl?m] columns, column, cylinder

Field: Column | | Field

Row: Line

Port: Ports

Quit: Cancel, Exit | | Stop it

Exit: Exit

CharSet: Character set, character encoding

COLLATE: Sorting Rules | | [k? ' Let] proofread, check

Collation: Sorting rules

Delimiter: delimiter | | [d? '  L?m?t?] Delimiter

Create: Creating

Drop: Fall, give up, stop

Exists: Presence

int type

tinyint type

smallint type

Mediumnint type

bigint type

unsigned unsigned | | Signature, token, gesture

Zerofill padding 0

FLOAT: single-precision floating-point type

Double: dual-precision floating-point type

Decimal: Decimal type, fractional type | | [' d?s?ml] decimal, fractional

Numeric: Digital

Fixed: fix type | | A. Determined, stubborn

Char: fixed-length character type

VARCHAR: variable-length character type

Binary: Binary type

Enum: Enumeration Type | | English [' en?m] beauty [?? NJU?M] Enumerate/enumerate types

Set: Multi-Select Type | | Set, device

bit: bit type

Primary key: Primary key

Unique key: Uniqueness Keys | | [j? ' Nik] The only, unique

Auto_increment: Self-growth

Default: Defaults

Comment: Comment, description | | [' kɑm?nt] comments, comments

_____________________________________________________________________2017.4.8

Index: Indexed | | British ['? Ndeks] beauty ['? Nd?ks] Index, pointer

Key: Index | | Key, Key, key

FOREIGN key: foreign Key | | [' f?r?n] foreign, diplomatic, irrelevant

Fulltext key Full-text index

Constraint Constraints | | [K?n ' strent] constraints, cramped

Engine Storage Engines

Alter change

Show display

Describe description | | [d? ' SKRA?B]

Rename renaming

View views

Insert Insertion

Value values

Replace replacement

Select Selection

Load load

Delete Deletes

From ...

Order sequence

Limit limits

Truncate | | [tr?? ' KET] cut short, shorten

Query queries

Distinct Differentiated | | [d? ' St?? KT] There's a difference, obviously.

where where

Group groups

As as

In ... Inside

Like ... The same

Count number, counting

Avg Avg | | Average mean, average

Max Max

Min min

Sum of sum

ASC Ascending | | Ascend [? ' S?ND]

Desc Descending | | Descend [d? ' Send

Join connection

Inside the inner

Cross-crossed

Left: Ieft

Right:

Outer outside.

In: ... Inside

Any: one

All: All

Some: part of it

Union: Union of

Identify: Confirmation, identification | | [A? ' Dent?fa?]

Grant: Granted

Revoke: Revocation, cancellation, abolition | | [R? '] Vok

Privilege: Permissions | | privilege, preferential treatment

Start: Begin

Transaction: Transaction | | [Tr?n ' Z?k?? N

Autocommit: Auto-commit

Begin: Start

End: Finish

Commit: Entrust, promise, guarantee | | [k? ' M?T]

Rollback: Rollback | | Reverse

Case: In ... situation | | Case, instance

Loop: Loops | | Ring, Ring

While: When ... The time

Repeat: Repeat

Leave: Leave

DECLARE: Disclaimer

Call: Invoke

Procedure: Stored Procedure | | [PR? '  Sid??] procedures, steps

Trigger: Trigger | | [' Tr?ɡ?]

MySQL database common words