Data Definition Language (DDL) in MySQL)

Data Definition Language (DDL) in MySQL)

Data Definition Language (DDL) in MySQL)

Create (create TABLE)

Standard table creation statement:

Create table [schema name.] table Name
(
# Multiple column Definitions
ColumnName1 dataType [default expr (this is the default value)],
...
)

Parentheses can contain multiple column definitions. Each column definition is separated by a comma (,). The last column definition ends with parentheses without a comma.

Creating a table only creates a table structure. It defines the number of columns in the data table, including the column name, class type, and optional default values (defined using the default keyword ).

Column types supported by MySQL

Column Type	Description
tinyint/smallint/mediumint/int(integer)/bigint	1-byte/2-byte/3-byte/4-byte/8-byte positive number, which can be divided into two types: signed and unsigned. The difference between these positive numbers is that the number range of tables is different.
float/double	Single precision, Double Precision Floating Point Type
decimal(dec)	Exact decimal type, no loss of precision compared to float and double
date	Date type. The time cannot be saved. When you save the java. util. Date object to the date column, the time part is lost.
time	Time type. The date cannot be saved. When the java. util. Date object is saved in the time column, the Date part is lost.
datetime	Date and Time Type
timestamp	Timestamp type
year	Year type. Only the year of the time is saved.
char	Fixed-length string type
varchar	Variable-length string type
binary	A fixed-length binary string that stores strings in binary format.
varbinary	A variable-length binary string that stores strings in binary format.
tinyblob/blob/mediumblob/longblob	1-byte, 2-byte, 3-byte, and 4-byte Binary large objects, which can be used to store binary data such as images and music. They can be respectively stored in the size of 255B/64KB/16 MB/4 GB.
tinytext/text/mediumtext/longtext	1-byte/2-byte/3-byte/4-byte Text object, which can be used to store long-length strings, respectively 255B/64KB/16 MB/4 GB text
enum('value1','value2',...)	Enumeration type. The value of this column can only be one of multiple values in the enum brackets.
set('value1','value2',...)	Set type. The value of this column can be one of the multiple values in the brackets after set.

# Example:
Create table t_test
(# Int is usually used for integer data.
Test_id int,
# Decimal Definition
Test_price decimal,
# Define common text and use the default keyword to specify the default value
Test_name varchar (255) default 'xxx ',
# Defining large text types
Test_desc text,
# Define images
Test_img blob,
# Define a date
Test_date datetime
);

The preceding common table creation statements only create an empty table with no data in the table.

If you use a subquery to create a table, you can insert data simultaneously when creating a table. Syntax:

Create table [mode name.] table name [column1 [, column2,...] as subQuery;

In the preceding syntax, the field list of the new table must match the number of fields in the subquery. The field list can be omitted when a new table is created. If this field list is omitted, the column names of the new table are identical to those of the selected table, as shown below:

Create table user2 as select * from user;

The preceding statement copies the user table named user2.

2. alter (modify table structure ):
Including adding column definitions, modifying column definitions, deleting columns, and renaming Columns

Add column definition:

Alter table tableName add
(
# Multiple column Definitions
ColumnName dataType [default expr],
...
);

The preceding statement adds the columns in parentheses to the column definitions of the specified table. If only one column is added, parentheses can be omitted.

# Example:

# Add an add_id field to the t_test table. The field type is int.
Alter table t_test add add_id int;

# Add the add_name and add_address fields to the t_test table. All types are varchar.
Alter table t_test add
(
Add_name varchar (255) default 'this is name ',
Add_addresses varchar (255)
);

Copy code

Note: strings in SQL statements are not enclosed by double quotation marks, but are enclosed by single quotation marks. When adding a field, if data records exist in the data table, unless the default value is specified for the new column,
Otherwise, the new data column cannot be specified as a non-empty constraint, because the existing records must be empty in the new column. (As long as the newly added constraint conflicts with the existing data, modifying the data table structure will fail ).

Modify the column definition:

Alter table tableName modify columnName dataType [default expr] [first | after columnName];

The first or after columnName in the above syntax indicates that the target column is modified to the specified position.

# Example:

# Change the type of the add_id column in The t_test table to varchar (255 ).
Alter table t_test modify add_id varchar (255 );

# Change the add_name column in The t_test table to the int type.
Alter table t_test modify add_name int;

Delete column definitions:

Alter table tableName drop columnName;

To delete a column, you only need to keep up with the column name to be deleted after the drop keyword.

# Delete the add_name column in The t_test table
Alter table t_test drop add_name;

Rename a data table name:

Alter table tableName rename to newTableName;

# Example:
# Rename the t_test data table to t_demo
Alter table t_test rename to t_demo;

Rename the column name in the data table:

Alter table tableName change
Old_column_name new_column_name dataType [default expr] [first | after column_name];

3. drop (delete table ):

# Syntax:
Drop table tableName;

# Example:
# Delete the t_demo data table
Drop table t_demo;

Note: After a data table is deleted, the table structure is deleted, and the table object no longer exists. All data in the table is also deleted. All related indexes and constraints of the table are also deleted.

4. truncate (truncation table)
Delete all data in the table, but retain the table structure. Truncate can only delete all records of the entire table at one time.

# Syntax:
Truncate tableName;

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151360.htm