ALTER keyword usage

-- Add field action
alter table tbl_name add Field name field type[Integrity constraints] [first|after Field name]

-- Add user name field
ALTER TABLE user1 ADD username VARCHAR(20);

-- Add password field
ALTER TABLE user1 ADD password CHAR(32) NOT NULL;

-- Add mailbox field
ALTER TABLE user1 ADD email VARCHAR(50) NOT NULL AFTER username;

-- Add test field
ALTER TABLE user1 ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;


-- Delete test field
ALTER TABLE user1 DROP test;

-- One time addition age,addr field,delete email field
ALTER TABLE user1 
ADD age INT NOT NULL DEFAULT 18,
ADD addr VARrCHAR(100) NOT NULL,
DROP email;


-- Add default
ALTER TABLE tbl_name ALTER Field name SET DEFAULT Default value;

-- by email Field add default action
ALTER TABLE user2 ALTER email SET DEFAULT 'imooc@qq.com';

-- delete age Default values for fields
ALTER TABLE user2 ALTER age DROP DEFAULT;

Usage of MODIFY and CHANGE keywords

-- Modify field template
ALTER TABLE tbl_name MODIFY Field name field type[Field properties] [FIRST|AFTER Field name]

-- CHANGE Keyword usage
ALTER TABLE tbl_name CHANGE Original field name new field name field type field property [FIRST|AFTER Field name]


-- take username Change field to user field
ALTER TABLE user3 CHANGE username user VARCHAR(20) NOT NULL;

-- take password Change to pwd field
ALTER TABLE user3 password pwd VARCHAR(20) NOT NULL;

-- take email Change to userEmail Type changed to VARCHAR(100) DEFAULT 'imooc@qq.com'
ALTER TABLE user3 email userEmail VARCHAR(100) DEFAULT 'imooc@qq.com';

Add and delete primary key constraints

-- Add primary key
ALTER TABLE tbl_name ADD PRIMARY KEY(Field name);

-- Delete primary key
ALTER TABLE tbl_name DROP PRIMARY KEY;

If there is self increment on the primary key, the self increment attribute on the primary key must be removed before the primary key attribute can be deleted

Add and delete unique indexes

--Add unique index field

ALTER TABLE tbl_ name ADD UNIQUE KEY|INDEX index_ Name (field name);

--Delete unique index field

ALTER TABLE tbl_name DROP INDEX  index_name;

Note: index_name is the index name

Modify data table name

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;

RENAME TABLE tbl_name TO new_tbl_name;

Database storage engine

MyISAM storage engine

1. Using the modified storage engine, three formats of files, table structure files, data storage files and index files will be generated in the disk
2. You can specify the storage location of data files and index files when creating tables.
3. The maximum amount of data supported by the engine in a single table is 64 records to the power of 2
4. Each table can build 64 indexes. The maximum length of the index is 1000B
5. Dynamic table data processing is more complex

InnoDB storage engine

1. The design follows the ACID model, supports transactions, has the ability to recover from service crash, and can protect user data to the greatest extent
2. Atomicity, consistency, isolation, persistence
3. InnoDB supports row level locks, not table level locks. Row level locks can improve the performance of multi-user concurrency
4. Support foreign keys to ensure data consistency and integrity
5. InnoDB has its own independent cache pool, and common data and indexes are stored in the cache
6. For Insert, update and delete operations, InnoDB will use a change buffering mechanism to automatically optimize, provide consistent reading, cache changed data, reduce I/O operations and improve performance.
7. After the InnoDB table is created, two files will be generated, the table structure file and the tablespace file. In fact, the tablespace file contains data and indexes
8. All tables need to create a primary key. Finally, with automatic growth, you can also use the fields queried frequently as the primary key