> For the complete documentation index, see [llms.txt](https://ret2basic.gitbook.io/ctfnote/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ret2basic.gitbook.io/ctfnote/computer-science/databases/mysql/modifying-tables.md).

# Modifying Tables

## Modifying Column

Modify the data type of a column:

```sql
ALTER TABLE tb1C MODIFY name VARCHAR(100);
```

Add a new column to the end:

```sql
ALTER TABLE tb1C ADD birth DATETIME;
```

Add a new column to the beginning:

```sql
ALTER TABLE tb1C ADD birth DATETIME FIRST;
```

Add a new column to a specific location:

```sql
ALTER TABLE tb1C ADD birth DATETIME AFTER empid;
```

Modify the ordering of columns:

```sql
ALTER TABLE tb1C MODIFY birth DATETIME FIRST;
```

Change the name and date type of a column:

```sql
ALTER TABLE tb1C CHANGE birth birthday DATE;
```

Drop a column:

```sql
ALTER TABLE tb1C DROP birthday;
```

## Primary Key and Unique Key

Set primary key (no duplication + no NULL):

```sql
CREATE TABLE t_pk (a INT PRIMARY KEY, b VARCHAR(10));
```

Set unique key (no duplication):

```sql
CREATE TABLE t_uniq (a INT UNIQUE, b VARCHAR(10));
```

## Auto-Increment and Default Value

Set auto-increment:

```sql
CREATE TABLE t_series (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10));
```

Reset auto-increment index (deletion won't set the counter to 1):

```sql
ALTER TABLE t_series AUTO_INCREMENT=1;
```

Set default value for a column:

```sql
ALTER TABLE tb1G MODIFY name VARCHAR(10) DEFAULT 'nobody';
```

## Index

Create index:

```sql
CREATE INDEX my_ind ON tb1G (empid);
```

Show index (in a nice format):

```sql
SHOW INDEX FROM tb1G\G
```

Drop index:

```sql
DROP INDEX my_ind ON tb1G;
```