DML (data manipulation language): They are SELECT, UPDATE, INSERT, DELETE. Just like its name, these 4 commands are languages used to operate on data in the database.
DDL (data definition language): DDL has more than DML. The main commands include CREATE, ALTER, DROP, etc. DDL is mainly used to define or change the initialization work of table (TABLE) structure, data type, links and constraints between tables. Most of them use it when creating tables.
DCL (Data Control Language): It is a database control function. It is a statement used to set or change the permissions of the database user or role, including (grant, deny, revoke, etc.) statements. By default, only sysadmin, dbcreator, db_owner or db_securityadmin and other personnel have the right to execute DCL
TCL - Transaction Control Language: Transaction Control Language, COMMIT - Save completed work, SAVEPOINT - Set savepoint in transaction, can roll back to this, ROLLBACK - Rollback, SET TRANSACTION - Change transaction options
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
Added fields
Basic syntax:
ALTER TABLE Table name ADD COLUMN Field name Field type;
- Add a new age column after the name field
ALTER TABLE tuser ADD COLUMN age int(11) DEFAULT NULL COMMENT 'age' AFTER name;
# AFTER: After a certain field, BEFOR: Before a certain field
- Append a column after the table
ALTER TABLE tuser ADD COLUMN age int(11) DEFAULT NULL COMMENT 'age';
Modify fields
- Modify the field name
ALTER TABLE tuser CHANGE name user_name varchar(32) DEFAULT NULL COMMENT 'Name';
# ALTER TABLE table name CHANGE Old field name New field name New data type;
After the CHANGE keyword, the field name you want to modify is followed, and then specify the new field name and type.
- Modify field type
ALTER TABLE tuser MODIFY name varchar(32) DEFAULT NULL COMMENT 'Name';
# ALTER TABLE table name MODIFY field name data type;
Delete fields
ALTER TABLE tuser DROP name;
# ALTER TABLE table name DROP field name;
Set self-increment: alter table tuser AUTO_INCREMENT=10000;