DDL
Data Definition Language is commonly used for: library and table management
- CREATE – CREATE
- Modify > ALTER
- Delete – DROP
1. Library management
1. Library creation
Syntax: CREATE DATABASE [IF NOT EXISTS] DATABASE name;
2. Library modification
ALTER DATABASE ALTER DATABASE name CHARACTER SET GBK; ALTER DATABASE CHARACTER SET GBK; The default is UTF-8. The RENAME statement is available from 5.1.7 to 5.1.23, but is not recommended because it may cause data loss. It has been removed.
3. Delete the library
Syntax: DROP DATABASE DATABASE name;
Two, table management
1. Create a table
Syntax: CREATE TABLE [IF NOT EXISTS] TABLE name (type of column name [(length) constraint] type of column name [(length) constraint]… Column name Column type [(length) constraint]);
2. Table modification
ALTER TABLE ADD/DROP/MODIFY/CHANGE column name
Modify the column nameALTER TABLEThe table name CHANGECOLUMNOld column name New column name [type] Modify the type or constraint of the columnALTER TABLEThe name of the table the MODIFYCOLUMNNew type of column name; ; Add a new columnALTER TABLEThe name of the tableADD COLUMNColumn name [type]; Delete the columnALTER TABLEThe name of the tableDROP COLUMNThe column name. Modify the name of the tableALTER TABLEThe old table name RENAMETOThe new name of the table;Copy the code3. Delete the table
Syntax: DROP TABLE name;
4. Copy the table
Copy only the structure of the table:CREATE TABLE 表2 LIKE 表1; Copy all structure+Data CREATTABLE 表3 SELECT * FROM 表1; Only part of the data CREAT is copiedTABLE 表4
SELECT 列1Column,2.FROM 表1
WHEREconditions1. ; Only part of the structure CREAT is copiedTABLE 表5
SELECT 列1Column,2.FROM 表1
WHERE 0 [ false ];
Copy the codeCommon data types
There are five main types: INTEGER types: TINY INT, SMALL INT, NEDIUM INT, INT/INTEGER, and BIG INT Floating point types: FLOAT, DOUBLE, and DECIMAL String types: CHAR, VARCHAR, TINY INT, TEXT, MEDIUM TEXT, LONGTEXT, TINY BLOB, BLOB, MEDIUM BLOB, LONG BLOB Date, DateTime, TimeStamp, Time, Year Other data types: BINARY, ENUM, SET, Geometry, Point, MultiPoint, LineString, MultiLineString, Polygon, GeometryCollection, etc
Binary and varbinary are used to hold shorter binary enums to hold enum sets to hold collections
***SET type: Similar to Enum type, can hold 0 to 64 members. The biggest difference between the set type and the Enum type is that the set type can select multiple members at a time, while the Enum type can select only one member
| membership | The number of bytes |
|---|---|
| 1 ~ 8 | 1 |
| 9 ~ 16 | 2 |
| 17 ~ 24 | 3 |
| 25 to 32 | 4 |
| 33~64 | 8 |
| — |
1. The integer
| MySQL data type | Meaning (symbol) |
|---|---|
| tinyint(m) | 1 byte range (-128 to 127) |
| smallint(m) | 2 bytes range (-32768 to 32767) |
| mediumint(m) | 3-byte range (-8388608 to 8388607) |
| int(m) | 4-byte range (-2147483648 to 2147483647) |
| bigint(m) | 8-byte range (+-9.22*10 ^ 18) |
| Value range If unsigned is added, the maximum value is doubled. For example, tinyint The value of unsigned ranges from 0 to 256. |
Int (m) specifies the width of the SELECT result set, and does not affect the actual value range.
2. Floating point (float and double)
| MySQL data type | meaning |
|---|---|
| float(m,d) | Single precision floating point type 8 bit precision (4 bytes) m total, D decimal |
| double(m,d) | Double precision floating point type 16 bit precision (8 bytes) m total, D decimal |
| Let a field be defined as float(6,3). If you insert the number 123.45678, the actual database contains 123.457, but the total number is 6 bits. The integer part is at most 3 bits. If 12.123456 is inserted, 12.1234 is stored, and if 12.12 is inserted, 12.1200 is stored. |
3. The fixed-point number
| MySQL data type | meaning |
|---|---|
| DEC(m,d) | M + 2 bytes |
| DECIMAL(m,d) | M + 2 bytes |
| The maximum value range is the same as double, and the valid value range for a given Decimal is determined by M and D |
Features:
- M: Integer part + decimal part
- D: Decimal part
- If the range is exceeded, the critical value is inserted
- M and D can be omitted
- In the case of float and double, the accuracy depends on the progress of the inserted value
- Fixed point type has high precision, and can be used if high precision of inserting value is required, such as currency calculation
Rule of thumb: The simpler the type you choose, the smaller the type that can hold values
4. String (char, varchar, text)
| MySQL data type | meaning |
|---|---|
| char(n) | A fixed length of up to 255 characters |
| varchar(n) | The value contains a maximum of 65535 characters |
| tinytext | The variable length contains a maximum of 255 characters |
| text | The value contains a maximum of 65535 characters |
| mediumtext | Variable length, up to 2 ^ 24 -1 characters |
| longtext | Variable length, up to 2 ^ 32 -1 characters |
| char | varchar | |
|---|---|---|
| (m) | Can be omitted, default is 1 | Do not omit |
| The characteristics of | A fixed-length character | Characters of variable length |
| space | Compare the cost | Compare save |
| The efficiency of | high | At the end of |
5. Binary data (_Blob)
1._BLOB and _text are stored in different ways. _text is stored as text and English is case sensitive, while _BLOB is stored as binary and case insensitive.
2._BLOB data can only be read as a whole.
3._TEXT can specify a character set. _BLO does not specify a character set.
6. Date and time type
| MySQL data type | meaning |
|---|---|
| date | The date ‘2008-12-2’ |
| time | Time ’12:25:36′ |
| datetime | Date & Time ‘2008-12-2 22:06:44’ |
| timestamp | Automatically store the modification time |
| The characteristics of |
- Timestamp Indicates a small time range. The value ranges from 19700101080001 to 2038
The value of Datetime ranges from 1000-1-1 to 999-12-31. 2. Timestamp Is related to the actual time zone and reflects the actual date. Datatime can only reflect the local time zone at the time of insertion. 3. Timestamp attribute is affected by Mysql version and SQLMode. So the field of this data type can hold the time when the record was last modified.
7. Attributes of data types
| MySQL data type | meaning |
|---|---|
| NULL | Data columns can contain NULL values |
| NOT NULL | Data columns are not allowed to contain NULL values |
| DEFAULT | The default value |
| PRIMARY KEY | A primary key |
| AUTO_INCREMENT | Auto-increment, for integer types |
| UNSIGNED | unsigned |
| CHARACTER SET name | Specify a character set |
4. Common constraints
Definition: A restriction used to restrict data in a table in order to ensure the accuracy and reliability of data in a table
1. Six constraints
| The constraint | meaning |
|---|---|
| NOT NULL | Is not empty |
| DEFAULT | The default value |
| PRIMARY KEY | Primary key, which ensures that the field segment value is unique and non-empty |
| FOREIGN KEY | The foreign key, which limits the relationship between two tables, must come from the primary table |
| UNIQUE | Unique, guaranteed uniqueness, can be empty |
| CHECK | Check constraint [MySQL not supported] |
2. Add timing
- Create table
- Modify the table
Foreign keys cannot be named. 2. Foreign keys cannot be named. Table level constraints: non-null, default, other support, foreign keys can be named
Add constraint type (default, non-null, primary key, unique)CREATE TABLEThe name of the table1(
id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
gender CHAR(1)，
seat INT UNIQUE,
majorID INT FOREIGN KEY REFERENCESThe name of the table2(id)
);
CREATE TABLEThe name of the table2(
id INT PRIMARY KEY,
majorName VARCHAR(20));Copy the codeAdd table level constraint syntax at the bottom of each field [constraint] Constraint name Constraint type (field name)CREATE TABLEThe name of the table1(
id INT,
Name VARCHAR(20),
gender CHAR(1),
seat INT UNIQUE,
majorID INT[constraint ] pk PRIMARY KEY(id),
[ constraint ] uq UNIQUE(seat),
);
CREATE TABLEThe name of the table2(
id INT PRIMARY KEY,
majorName VARCHAR(20),constraint] fk_ Secondary table name _ Primary table nameFOREIGNKEY(from table name)REFERENCESThe main table (id)); General writing:CREATE TABLEThe name of the table1(
id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
gender CHAR(1)，
seat INT UNIQUE,
majorID INT[constraint] fk_ Secondary table name _ Primary table nameFOREIGNKEY(from table name)REFERENCESThe main table (id));Copy the codePrimary key and unique comparison:
| uniqueness | Whether is empty | The number of | combination | |
|---|---|---|---|---|
| A primary key | Y | N | ONLY | Y but not recommended |
| The only | Y | Y | Many | Y but not recommended |
Foreign key features:
- Foreign key relationships are required to be set from the table
- The type of the foreign key column from the table and the associated column data type of the primary index must be consistent or compatible, regardless of the name
- The associated column in the primary table must be a key(typically a primary key or unique)
- When inserting data, insert the primary table first and then the secondary table
- When deleting data, delete the secondary table first and then the primary table
Alter table alter table add constraint syntax1.Add a column level constraint:alter tableThe name of the table the modifycolumnField name field type new constraint;2.Add table level constraint:alter tableThe name of the tableadd [constraintConstraint name] Constraint type (field name) [foreign key reference]CREATE TABLEThe name of the table1(
id INT,
Name VARCHAR(20),
gender CHAR(1)，
seat INT UNIQUE,
majorID INT
);
1.Add a non-null constraintALTER TABLEThe name of the table1 MODIFY COLUMN Name VARCHAR(20) NOT NULL;
2.Adding default ConstraintsALTER TABLEThe name of the table1 MODIFY COLUMN age INT DEFAULT;
3.Add primary key column level:ALTER TABLEThe name of the table1 MODIFY COLUMN id INT PRIMARYKEY; Table:ALTER TABLEPk_ table nameADD PRIMARYThe KEY (column name);4.Add the onlyALTER TABLEThe name of the table1 MODIFY COLUMN seat INT UNIQUE;
ALTER TABLEThe name of the table1 ADD UNIQUE(seat);
5.Add a foreign keyALTER TABLEThe name of the table1 ADD CONSTRAINTFk_ Secondary table name _ Primary table nameFOREIGNKEY(from table name)REFERENCESMain table (column name);Copy the codeAlter table delete constraint1.Delete a non-null constraintALTER TABLEThe name of the table1 MODIFY COLUMN s tuname VARCHAR(20) NULL;
2.Delete default ConstraintsALTER TABLEThe name of the table1 MODIFY COLUMN age INT ;
3.Remove the primary keyALTER TABLEThe name of the table1 DROP PRIMARY REY;
4.Delete the onlyALTER TABLEThe name of the table1 DROPThe INDEX seat;5.Remove the foreign keyALTER TABLEThe name of the table1 DEOP FOREIGNThe KEY fk_stuinfo_major;Copy the code5, identity column // self-growing column
The system provides default sequence values instead of manually inserting values.
- Must be paired with key
- One table at most
- It can only be numerical
- You can use auto_increment_increment = 3; Set the step length
The starting value can be set by manual interpolation
Set the identity column when creating the tableCREATE TABLE 表1(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20));INSERT INTO 表1 VALUES(NULL.'JOJO');
Insert into 表1(name) values ('MUDA'); Set variables:SET auto_increment_increment = 3;
Copy the codeSet the identity column when modifying the tableALTER TABLE 表1 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
Copy the codeDelete the identity columnALTER TABLE 表1 MODIFY COLUMN id INT;
Copy the code