Data types in the database

Contrast with Java

The type of Java Types in the database
int tinyint,smallint,midiumint,int/integer,bigint
double float,double
BigDecimal decimal
String Varchar, xxtext, binary, varbinary xxblob, enum, set, etc
char char
Date Date, DateTime, time, datetemp

Create data table

create tableTable name (column name1Data type [length] [Constraint], column name2Data type [length] [Constraint], column name3Data type [length] [Constraint])Copy the code

Delete data table

drop table[table name]Copy the code

Modify table structure

Changes the structure of a table over a data table that has already been defined.

Key word: alter

Have certain risk, do as little as possible

alter table[table name]add【 Type 】Copy the code

Delete the column

There is a risk that if you delete it, you won’t get it back.

alter table[table name]drop"Column"Copy the code

Change the data type of the column

There are risks

For example, double –>varchar converts all data to 0

alter table[table name] modify column name [new data type of column]Copy the code

Modify the column name

There is a risk that other programmers will not find it.

Alter table [alter table] change [old column name] [new column name] [new data type]Copy the code

Insert data into data table

Keyword: insert into values

insert into[Table name] (Column name1And the column name2And the column name3) values(value1And the value2And the value3#) orinsert into[table name]values(full column value) # Batch addinsert into student valuesFull column value full column valueCopy the code

Modify row data

If the modified row does not exist, no error will occur, affecting row 0.

Update [table name]set【 name 】 【age】="Age"+ 1, [List name]="New value"where"Id"="Value"Copy the code

Delete data (DELETE, truncate)

Keyword: delete from

It has to be conditional, preferably unique data

delete from[table name]where"Id"="List of values"Copy the code
truncate table[table name]Copy the code

Delete keyword and TRUNCate

Delete Deletes table data

Truncate Destroys the table. All data is lost.

Suppose the data column can automatically grow, delete all 10 columns in the table, start with the increment (10), and restart the record after the TRUNCate.

Primary key constraint

The data in this class should not be null, cannot be repeated, and must be unique.

A table can only have one primary key constraint.

Every data table should have a primary key constraint.

The primary key constraint can be added directly when a table is created.

Primary key is placed after the data type in the column, keyword, primary key.

create table person(
	int id primary key,
	firstname varchar(20),
	lastname varchar(20)
)
Copy the code

The second (recommended) method for adding a primary key constraint is to add it in the constraint area of the data table.

Key constraint

create table person(
	int id,
	firstname varchar(20),
	lastname varchar(20),
	constraint primary key(id)
)
Copy the code

To add the primary key constraint, modify the table structure.

create table person(
	int id primary key,
	firstname varchar(20),
	lastname varchar(20))alter table person add constraint primary key(id)
Copy the code

Delete the primary key constraint

alter table person drop primary key
Copy the code

Automatic growth of primary key constraints (starting at 0)

Java i++, to implement automatic data growth key, must be a primary key, must be int.

create table person(
	id int primary key auto_increment,
	firstname varchar(20),
	lastname varchar
)
Copy the code

You don’t have to set the ID key anymore. After deletion, it may appear to be discontinuous.

Character set coding

character set = utf8

Not null constraint

The column value cannot be null. If no non-null column value is specified, a warning is displayed, but the column is still executed. The column value is null.

The keyword is not NULL.

format

create table person(
	id int primary key auto_increment,
	firstname varchar(20) not null,
	lastname varchar
)
Copy the code

Delete a non-empty constraint

alter table person modify firstname varchar(20)
Copy the code

The only constraints

Columns to which unique constraints are added must have unique values.

The unique keyword

Declaration when creating a table (recommended)

create table person(
	id int primary key auto_increment,
	firstname varchar(20) ,
	lastname varchar unique
)
Copy the code

When the add fails, the value of the increment column is also incremented, and the value is skipped the next time the add succeeds.

Columns with unique constraints can have multiple null values. But empty strings don’t work.

Declaration when adding constraints

create table person(
	id int primary key auto_increment,
	firstname varchar(20) ,
	lastname varchar ,
	constraint unique uk_lastname (lastname)
)
Copy the code

Uk_address is the custom constraint name

Delete unique constraint, delete index

The index name is the constraint name. Without the constraint name, the constraint name defaults to the column name.

alter table person drop index uk_lastname
Copy the code

The default constraints

You can add default values for columns, using the keyword default

create table person(
	id int primary key auto_increment,
	firstname varchar(20) ,
	lastname varchar  default 'no'
)
Copy the code

Deleting the default constraint

Modify table structure deletion

alter table person modify lastname varcnar
Copy the code

In the mysql

If you exceed the length specified by the Varchar, it will be truncated, starting from the front. (Warning, but insertion successful)

If you have an increment column and a column with a constraint check, the condition on that constraint column is invalidated.

conclusion

create

insert

Modify the

delete