Simple database operation statements (records left by the student age)

Update MySQL user set Password=password (' 1234 ') where user= ' Ming '

Delete from MySQL user where user= ' and host= '

Ipconfig/all

Ping 127.0.0.1

If not exists

The CREATE table if not EXISTS table name (field name fields type)

Engine=innodb DEFAULT charset=gb2312

Create database------------------Creating databases

CREATE TABLE---------------------Creating tables

Drop-----------------------------Delete

TRUNCATE TABLE table----------------empty tables

Mysql-uroot-p

Mysql-uroot-p

DESC Table name------------------------view structure

show databases; ------------------View all databases

Show tables;---------------------View all Tables

Show create table student; -------View the structure of a table

Show engines---------------------View data Engine

\c-------------------------------Clear the data above

\q-------------------------------Exit command

Exit-----------------------------Close the window (log out)--shortcut key CTRL + C

\G-----Show CREATE TABLE Table \g--

Use------------------------------Enter

\s-------------------------------View MySQL server status information

Primary key----------------------Create primary key

Auto_increment-------------------Self-increment

Not null-------------------------is not empty

Unique---------------------------Uniqueness constraints

UNSIGNED-------------------------unsigned integers

Default--------------------------defaults----------field name data type default defaults

Insert into table (type) values (content)-insert

Select*from table where type = ' content '

where----------------------------conditions

Select type from table

Limit----------------------------Number of bars

Limit 1 (hide 1 data), 5 (show how many bars) (for pagination)

ORDER BY type ASC----------------from small to large (ascending)

ORDER BY type Desc---------------from large to small (descending)

ORDER BY--------------------------Sort

SELECT * FROM table where type a value of type like

Like-----------------------------fuzzy query

Between--------------------------data query

Group BY-------------------------repeats only one

' Old type name ' as ' new type name '----------renamed

Max------------------------------Max value

Min------------------------------Minimum value

Select Max (type) from

Nullif (field 1, field 2)--------------returns the first field if the value is equal to return to null

Avg------------------------------Average

Sum------------------------------and

Count----------------------------counts

Or-------------------------------a multi-condition query

Any------------------------------sub-query criteria

Union----------------------------links Two conditions

Type in (numeric value of type)-------------condition query

Update table set type = (the value to be modified), type = (the value to be modified)

Update table set--------------------Modify values

Delete from----------------------deletes an entire row

ALTER TABLE old table name rename new table name-Change table name

ALTER TABLE Table Add column name (type)----adding columns

------ALTER TABLE Add primary key (field name)--Add primary key

------ALTER TABLE Drop PRIMARY KEY--------Delete primary key

------ALTER TABLE tables Add field Name fields Type () First (add field in column one)

------ALTER TABLE tables Add field Name fields Type () after field name (Add a field after the second field name)

ALTER TABLE Table DROP column name---------Delete Column

ALTER TABLE table Modify column name (type)-Modify data type

------ALTER TABLE Modify field name fields Type () First (Modify column field)

------ALTER TABLE Modify field name fields Type () after field name (Add a field after the second field name)

ALTER TABLE table change old column new column name type (type)--Modify column name

ALTER TABLE name modify field type after field name--Inserts the first field after the second field

ALTER TABLE name engine= the storage engine to be modified.

ALTER TABLE name drop FOREIGN key FOREIGN KEY constraint name

is-------------------------------Yes

SELECT DISTINCT (type) from-------to weight

INNER JOIN-----------------------INNER JOIN

Left join------------------------outside link

Having---------------------------similar to where action (must after group by)

Rand-----------------------------Random Sort

substr (column name, 1,3)----------------x column displays three characters from one display

Left Join------------------------Link

Right Join-----------------------Link

Right outer join-----------------link

SELECT * FROM Union SELECT * FROM

FRM------------------------------Table Structure

MYI------------------------------Index File

MYD------------------------------Data files

Create view Lese as select * from table name

Create view view name as------------creating views

Create View name Li (field name) as Select field name from table name

----------------------------------Create a view of the specified column name

Show Tables----------------------View view

Show CREATE VIEW Teacherli \g----View structure

Create or replace view-----------Replace if view exists

Alter VIEW table name (field name) as Select field name from table name

----------------------------------Modifying views

Drop View-----------------------Delete views

Update table name SET field name = value-------Modify View

----------------------------------Create a function

CREATE PROCEDURE function name ()

Begin

SELECT * from table name;

End

----------------------------------

Show procedure status------------view existing stored procedures

Call Function name ()-------------------calling syntax

drop procedure function name; -----------Delete Syntax

Timestamp default Now ()------------------------------------------set the current time

Timestamp default Current_timestamp on Update Current_timestamp--Set the current time

Constraint foreign Key name (self-defined) foreign key field name 1 references primary table name (primary key 1)

CONSTRAINT ' SS ' FOREIGN key (field) REFERENCES primary table name (primary key 1) on delete CASCADE on UPDATE CASCADE--multi-table foreign key modification Delete

Index-----------------------Indexes

Unique----------------------uniquely indexed

Index (field name)---------------Single-column indexes

Index (field name, field name)-------Multi-column index

Fulltext Index--------------full-text indexing

Spatial index--------------spatially indexed

ALTER TABLE name add index index name (field)---

Create index index name on table name (field)

ALTER TABLE name DROP INDEX index name-----drop indexes

Drop index index name on table name

Explain---------------------View index

Show Processlist------------Display run

Kill 519

Mysql–hlocalhost–uroot-p

Host hosts

User users

Password Password

MySQL modifies user password into MySQL,

Mysql>use MySQL;

Mysql>update user Set Password=password (' New password ') where user= ' root ';

Mysql>flush privileges;

The modification is complete.

Data type:

String type:

CHAR (m) fixed-length non-binary character M-byte, 1<=m<=255

Varvahr (M) variable length non-binary string l+1 byte, l<=m,1<=m<=255

Tinytext very small non binary string l+1 byte, l<2~8

Text small non-binary string l+2 byte, l<2~16

Mediumtext medium-sized non-binary string l+3 byte, l<2~24

Longtext large non binary string l+4 byte, l<2~32

Enum enum type, only one enumeration string value 1 or 2 bytes, depending on the number of enumeration values (maximum 65535)

Set a setting, a string object can have 0 or more set members 1,2,3,4 or 8 bytes, depending on the number of members of the collection (up to 64 members)

Integer data type:

tinyint very small integer 1 bytes, signed -128~127, unsigned 0~255

smallint small integer 2 bytes, signed 32768~32767, unsigned 0~65535

Mediumint a medium-sized integer 3 bytes, signed -8388608~8388607, unsigned 0~16777215

int normal size integer 4 bytes, signed -2147483648~2147483647, unsigned 0~4294967295

bigint large integer 8 bytes, signed -9223372036854775808~9223372036854775807, unsigned 0~18446744073709551615

Floating-point type, fixed-point number type:

Float single precision floating point 4 bytes

Double-precision floating-point 8 bytes

Decimal (M,D), DEC compressed "strict" fixed-point number m+2 bytes

Date and time data type:

Year YYYY 1901~2155 1 bytes

Time hh:mm:ss-838:59:59~838:59:59 3 bytes

Date yyyy-mm-dd 1000-01-01~9999-12-3 3 bytes

DateTime yyyy-mm-dd HH:MM:SS 1000-01-01 00:00:00~9999-12-31 23:59:59 8 bytes

Timestamp yyyy-mm-dd HH:MM:SS 1970-01-01 00:00:01 utc~2038-01-19 03:14:07 UTC 4 bytes

Simple database operation statements (records left by the student age)