Database Review (2)

Database Review (2)

 

1. Insert

Syntax:

Insert into table name [(column name [, column name]...)] values (value [, value]...);

Note:

The data type of the inserted value must be the same as that of the corresponding column.

Data cannot exceed the length

Insert values must be consistent with column name order

Character and date data must be placed in single quotes

Null is used to insert null values.

If you do not specify which column to insert, all columns are inserted.

Chinese Data

Because the default code table is utf8 and the code table of cmd.exe is gbk, an error is reported when Chinese data is inserted. Therefore, we need to modify the code table of the client.

First, check the system variables: showvariables like 'character % ';

Modify the client code table: SETcharacter_set_client = gbk;

This solves the problem of inserting Chinese characters, but garbled characters are still displayed during data query. This is because utf8 is used when mysql transfers data to cmd.

Modify the output data table: SETcharacter_set_results = gbk

Ii. Delete

Syntax

UPDATE table name SET column name = value [, column name = value]... [WHERE Condition Statement];

Notes

The WHERE clause selects rows that meet the conditions for update. If no data is written, all rows are updated.

Syntax

Delete from table name [where Condition Statement]

Notes

If the where clause is not added, all records in the table will be deleted.

Delete can only be used to delete rows. Values in a column cannot be deleted. update is required.

Pay attention to the association between tables during delete and update operations.

You can use the TRANCATE table name to delete all the data in the table. In this way, the old table is deleted and re-created.

3. Back up and restore the database

Back up database

Enter quit to exit mysql. In cmd.exe, enter:

Mysqldump-u user name-p Password Database Name> file name

Restore database

After mysql.exe is used

Source File Name

Four primary key constraints: Primary Key

When designing a table, we usually need to assign a unique identifier to each record. We will use the primary key to restrict this identifier.

Primary key is used to identify a field. This field is not empty and unique.

 

Set the primary key when creating a table

Create table test2 (

Id int primary key,

Name varchar (20)

);

 

Delete primary key

Alter table test2 dropprimary key;

 

Add a primary key to a column

Alter table Test2 changeid ID int primary key;

Alter table Test2 addprimary key (ID );

 

Set automatic growth of primary keys

Create Table test3 (

Id int primary key auto_increment,

Name varchar (20)

);

 

Delete auto-Increment

Alter table test3 changeid ID int;

 

Set auto-Growth

Alter table test3 changeid ID int auto_increment;

 

UUID primary key

128 bits in hexadecimal notation, 32 bits in hexadecimal notation plus 4-

Java. util. UUID.Randomuuid(). Tostring ()