Mysql Study Notes 1, mysql Study Notes

Mysql Study Notes 1, mysql Study Notes
Database Operations

1. Create a database

Create database databasename

Return Query OK

Error 1007 is returned if an Error occurs.

2. view all databases

Show databases

Note: It is databases, plural

3. Select a database

Use databasename

The returned result is Database changed. It is useful when using reverse CT.

Error 1049 is returned if an Error occurs.

4. Delete

Drop database databasename

Return Query OK

Error returned for failure

Database Engine

The storage engine specifies the table category, that is, how to store and index data, and whether transactions are supported. It also determines the storage mode of the table on the computer.

1. view supported Engines

Show engines

2. query the default Engine

Show variables like "storage_engine %"

MyISAM

3. Modify the default Engine

Modify my. ini configuration file

4. Select an engine

As needed

Data Type

1. Integer type, 1, 2, 3, 4, 8, unit: Byte
2. floating point type, 4, 8
3. Number of points:

DEC (M, D) and DECIMAL (M, D) M + 2 Byte

4. Bit type

BIT (M)

5. Date and Time types

DATE41000-01-01 ~ 9999-12-31

DATATIME8 1000-01-01 00:00:00 ~ 23:59:59, 9999-12-31

TIMESTAMP4 19700101080001 ~ A time point in 2038

TIME3-835: 59: 59 ~ 835: 59: 59

YEAR11901 ~ 2155

6. string type

Varchar (MB) M: 0-65535

Char (M) M: 0-255

Tinytext0-255Byte

Text0-65535 Chinese Characters

Mediumtext0-167772150 million Chinese Characters

Longtext0-4294967295 2 billion Chinese Characters

# A small amount of binary data, images, music, and videos

Binary (M) 0-M

Varbinary (M) 0-M

# A large number of Binary

Tinyblob0-255

Blob0-2 ^ 160-64 K

Mediumblob0-2 ^ 24 0-16 M

Longblob0-2 ^ 32 0-4G

Table operations

Format:

Command (create, desc, alter, drop) table tablename [action]

1. Create

Create table tablename (
Columnname, type
...
)
Create table t_table (id int, money float, date, time, desc varchar (500), picture mediumblob );
Failed to add because of the keyword date, time, desc
Create table t_table (id int, money float, tdate date, ttime time, tdesc varchar (500), picture mediumblob );
Or use:
Create table t_table (id int, money float, 'date' date, 'time' time, 'desc' varchar (500), picture mediumblob );

2. View

Describe table_name
Desc table_name
View table Definitions
Show create table table_name \ G
Note: \ G display is more user-friendly and beautiful (statement Terminator;, \ g, \ G)

3. modify (add modify change rename drop)

Format: alter table tablename [rename/modify/add/change/drop]

0. Modify the table name

Rename: rename

Alter table old_table_name rename [to] new_table_name

Alter table t_table rename t_test

1. Modify the column name

A. Modify the column name and Data Type

Change: change, change

Alter table t_table change oldcolumnname newcolumnname newdatatype

B. Only modify the Data Type

Modify: modify

Alter table t_table modify columnname datetype

After the data type is modified, the data value may change, but it is not reminded that the conversion should be careful.

2. Insert a new column at the end

Alter table tablename add columnname datatype

Alter table t_test add new1 tinyint;

Desc t_test

3. insert a new column at the beginning

Alter table table_name add columnname datatype first;

Alter table t_test add first1 bigint first;

Note: No long type, only bigint type

Tinyint smallint mediumint int/Integer bigint

4. Specify the position of the new column

Alter table tablename add columnname datatype after column

Desc t_test

Add to third column

Alter table tablename add three bigint after id

Desc t_test

5. Adjust the position of certain Columns

Alter table table_name modify column1 datatype first | alfter column2

Attribute name 1 and attribute name 2 must both exist.

Alter table t_test modify three bigint after picture;

6. Delete Columns

Alter table tablename drop columnname

Desc t_test

Alter table tablename drop new1

Desc t_test

4. Delete

Drop table tablename