4, DDL (data definition language)

4.1 data type

4.1.1 numerical type

1. type

type	TINYINT	SMALLINT	MEDIUMINT	INT/INTEGER	BIGINT
byte	1	2	3	4	8

1. characteristic

• Both unsigned and signed can be set. They are signed by default, and unsigned can be set through unsigned.
• If it exceeds the range, an out or range exception will be reported, and the critical value will be inserted (the maximum or minimum value of this type is the critical value).
• The length can not be specified. There will be a length by default. The length represents the maximum width of the display. If it is not enough, fill the left with 0, but it needs to be matched with zerofill, and it becomes unsigned integer by default.
• If there are no special requirements for data, INT/INTEGER is preferred.

4.1.2 floating point

1. type

• Fixed point number
  • DEC(M,D): M+2 bytes
  • DECIMAL(M,D): M+2 bytes
• Floating point number
  • FLOAT(M,D): 4 bytes
  • DOUBLE(M,D): 8 bytes

1. characteristic

2. M represents the number of integer parts + decimal parts, and D represents decimal parts.

3. If it exceeds the range, an out or range exception is reported, and a critical value is inserted (the maximum or minimum value of this type is the critical value).

4. Both M and D can be omitted, but for fixed-point numbers, m defaults to 10 and D defaults to 0.

5. If the accuracy requirement is high, the fixed-point number is preferred.

4.1.3 character type

1. type

type	CHAR	VARCHAR	BINARY	VARBINARY	ENUM	SET	TEXT	BLOB
describe	Fixed length character	Variable length character	Binary string	Binary string	enumeration	aggregate	text	Binary large object

1. characteristic

2. Char: fixed length character. The writing method is char(M). The maximum length cannot exceed M. M can be omitted. The default is 1.

3. Varchar: variable length character written as varchar(M). The maximum length cannot exceed M, where M cannot be omitted.

4. If there are no special requirements for data, VARCHAR is preferred.

4.1.4 date type

1. type

type	YEAR	DATE	TIME	DATETIME	TIMESTAMP
describe	particular year	date	time	Date + time	Date + time

1. characteristic

2. TIMESTAMP is easily affected by time zone, syntax mode and version, and can better reflect the real time of the current time zone, while DATETIME can only reflect the local time zone at the time of insertion.

3. The time range supported by TIMESTAMP is small. The value range of DATETIME is 1000-1-1-9999-12-31.

4. The properties of TIMESTAMP are greatly affected by the Mysql version and SQLMode.

5. If there are no special requirements for data, DATETIME is preferred.

4.2 common constraints

4.2.1 meaning

Constraint is a restriction used to restrict the data in the table in order to ensure the accuracy and reliability of the data in the table.

4.2.2 classification

constraint	significance
NOT NULL	Non empty, the value of this field is required
UNIQUE	Unique, the value of this field cannot be repeated
DEFAULT	By default, the value of this field does not need to be manually inserted
CHECK	Check that MySQL does not support
PRIMARY KEY	Primary key, the value of this field cannot be repeated and is not empty -- > unique + not null
FOREIGN KEY	Foreign key, the value of this field refers to the field of another table

4.2.3 features

1. Primary key and unique

• Similarities:
  • Are unique.
  • Key combinations are supported, but not recommended.
• difference:
  • A table can have at most one primary key, but can have multiple unique keys.
  • The primary key cannot be empty. The only one can be empty.

1. Foreign key

• It is used to restrict the relationship between two tables. The field value of the primary table is referenced from the field value of the table.
• The foreign key column and the referenced column of the main table require the same type, the same meaning, and no name.
• The referenced column of the main table must be a key (usually a primary key).
• To insert data, first insert the main table; To delete data, delete the slave table first.

4.2.4 example

1. Add constraints when creating tables

   CREATE TABLE Table name(
     Field name field type NOT NULL,
       # Non empty
       Field name field type DEFAULT value,
       # Add default
     Field name field type PRIMARY KEY,
       # Primary key
     Field name field type UNIQUE,
       # only
     CONSTRAINT Constraint name FOREIGN KEY(Field name) REFERENCES Main table(Referenced column)
   );
   

be careful:

• Column level constraints support: non empty, default, primary key, unique, and cannot have constraint names.
• Table level constraints support: primary key, unique and foreign key. Constraint names can be used, but they are not valid for primary keys in MySQL.
• Column level constraints can append multiple to a field, separated by spaces, without order requirements.

1. Add or remove constraints when modifying tables

# 1. Non empty

ALTER TABLE Table name MODIFY COLUMN Field name field type NOT NULL;
# Add non empty (column level constraint)
ALTER TABLE Table name MODIFY COLUMN Field name field type;
# Delete non empty

# 2. Acquiescence

ALTER TABLE Table name MODIFY COLUMN Field name field type DEFAULT value;
# Add default (column level constraint)
ALTER TABLE Table name MODIFY COLUMN Field name field type;
# Delete default

# 3. Primary key

ALTER TABLE Table name MODIFY COLUMN Field name field type PRIMARY KEY;
Add primary key (column level constraint)
ALTER TABLE Table name add [CONSTRAINT Constraint name] PRIMARY KEY(Field name);
Add primary key (table level constraint)
ALTER TABLE Table name DROP PRIMARY KEY;
Delete primary key

# 4. Only

ALTER TABLE Table name MODIFY COLUMN Field name field type UNIQUE;
# Add unique (column level constraint)
ALTER TABLE Table name add [CONSTRAINT Constraint name] UNIQUE(Field name);
# Add unique (table level constraint)
ALTER TABLE Table name DROP INDEX Index name;
# Delete unique

5,Foreign key


ALTER TABLE Table name add [CONSTRAINT Constraint name] FOREIGN KEY(Field name) REFERENCES Main table(Referenced column);
# Add foreign keys (table level constraints)
ALTER TABLE Table name DROP FOREIGN KEY Constraint name;
# Delete foreign key

4.3 self growth column

4.3.1 meaning

The self growing column is also called the identification column. It does not need to manually insert values. The system provides default sequence values.

4.3.2 features

1. Instead of manually inserting values, you can automatically provide sequence values. The default value starts from 1 and the step size is 1. If you want to change the starting value, manually insert the value for the first time and then use NULL. If you want to change the step size, modify and change the system variable: SET auto_increment_increment = value;.
2. A table can have at most one self growing column.
3. Self growing columns can only support numeric types.
4. Self growing column must be a key.

4.3.3 example

1. Add self growing columns when creating tables

CREATE TABLE Table name (
  Constraint type field name AUTO_INCREMENT
);

1. Add or remove self growing columns when modifying tables

ALTER TABLE surface MODIFY COLUMN Field name field type constraint AUTO_INCREMENT;
# Add self growing column


ALTER TABLE surface MODIFY COLUMN Field name field type constraint;
# Delete self growing columns

4.4 CRUD operation of Library

4.4.1 viewing database

show databases;
# View all databases

show create database Database name; 
# View database definitions

select database(); 
# View the database currently in use

4.4.2 creation of Library

create database Database name;

4.4.3 modify database

RENAME DATABASE Old library name TO New library name;
# Rename database

ALTER DATABASE Library name CHARACTER SET Character set name;
# Modify library character set

4.4.4 delete database

drop database Database name

4.4.5 selected database

use Database name

4.5 CRUD operation of table

4.5.1 table creation

CREATE TABLE IF NOT EXISTS Table name (
  Field name field type [constraint],
  Field name field type [constraint],
  ...
  Field name field type [constraint]
) ;

Example:

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

• If you do not want the field to be NULL, you can set the property of the field to NOT NULL. When operating the database, if the data entered in the field is NULL, an error will be reported.
• AUTO_INCREMENT defines a column as a self incrementing attribute, which is generally used for primary keys, and the value will be automatically incremented by 1.
• PRIMARY KEY keyword is used to define a column as a PRIMARY KEY. You can define a PRIMARY KEY using multiple columns separated by commas.
• ENGINE sets the storage ENGINE and CHARSET sets the encoding.

4.5.2 viewing table

show tables;
# View all tables

DESC t_student
DESCRIBE t_student
# View table details

show create table t_student
# View the sql statement that created the table

4.5.3 reproduction of tables

CREATE TABLE Table name LIKE Old table;
# Copy table structure

CREATE TABLE Table name 
SELECT Field 1,Field 2,... FROM Old table WHERE 0;
# Copy some fields of the table

CREATE TABLE Table name 
SELECT Query list FROM Old table[ WHERE [filter criteria];
# Copy table structure + data

CREATE TABLE Table name 
SELECT Field 1,Field 2,... FROM Old table[ WHERE [filter criteria];
# Copy some fields + data of the table

4.5.4 modification of table

ALTER TABLE Table name
# Add column

ALTER TABLE Table name ADD COLUMN Column name type FIRST;
# Add first column
ALTER TABLE Table name ADD COLUMN Column name type AFTER Field name;
# After adding a column

ALTER TABLE Table name MODIFY COLUMN Column name new type [new constraint];
# Modify the type or constraint of the column

ALTER TABLE Table name CHANGE COLUMN Old column name new column name type;
# Modify column name

ALTER TABLE Table name DROP COLUMN Listing;
# Delete column

ALTER TABLE Table name RENAME TO New table name;
# Modify table name

4.5.5 deletion of table

DROP TABLE IF EXISTS Table name;

TRUNCATE TABLE IF EXISTS Table name;