大圣Blog

MySQL

数据库操作

数据表操作

Mysql列类型及列属性

MySQL常用函数

MySQL增删改

MySQL查询

JOSN的支持

MySQL流程控制语句

视图

存储过程及存储函数

变量与游标

事务

触发器

权限及安全管理

MySQL日志

二进制日志

Mysql5.7复制功能

数据备份与恢复

性能优化

课程导航

数据库操作

Mysql列类型及列属性

## 创建表

### 语法

CREATE TABLE [IF NOT EXISTS] 表名 (  
    字段名1 列类型 [属性] [索引],  
    字段名2 列类型 [属性] [索引],  
    字段名3 列类型 [属性] [索引],  
    ......  
) [表类型] [表字符集]

### 实践

```
CREATE TABLE IF NOT EXISTS users(
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(8) NOT NULL DEFAULT '' COLLATE utf8mb4_bin COMMENT '用户名',
    userpass CHAR(32) NOT NULL DEFAULT '',
    mobile CHAR(11) NOT NULL DEFAULT '' UNIQUE,
    sex ENUM('男','女','未知') DEFAULT '未知' ,
    birth DATE NOT NULL,
    INDEX username(username)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
```

注意：主键是特殊的唯一索引，一个表只能有一个主键

## 查看表结构

### 语法

DESC 表明

### 实践

![图片](https://uploader.shimo.im/f/Zsyshbujbp8yU4Sx.png!thumbnail)

## 清空表

### 语法

TRUNCATE 表名

### 实践

```
mysql> TRUNCATE users;
Query OK, 0 rows affected (0.01 sec)
```

## 查看表

### 语法

SHOW TABLES;

## 删除表

### 语法

DROP TABLE IF EXISTS 表名

### 实践

```
mysql> DROP TABLE IF EXISTS users_copy1;
Query OK, 0 rows affected (0.01 sec)
```

## 修改表

### 语法

ALTER TABLE 数据表名 alter_spec[,alter_spec] ... table options

1. 添加新字段 ALTER TABLE 表名 ADD 字段名 <建表语句> [FISRT|ALTER 列名]
2. 修改字段 ALTER TABLE 表名 change|modify 列表 <建表语句> 注意：modify与change的区别，modify只能修改列类型，change除了修改列类型外还能改列名称。
3. 删除字段 ... DROP 列名
4. 添加索引名称 ... ADD INDEX [INDEX_NAME] (index_col1,index_col2,...)
5. 删除索引 ...DROP INDEX INDEX_NAME
6. 删除主键 ... DROP PRIMARY KEY 
7. 添加主键 ... ADD PRIMARY KEY (INDEX_COL1,INDEX_COL2,...)
8. 添加唯一索引 ... ADD UNIQUE [index_name] (index_col1,index_col2,...)
9. 修改表名 RENAME newName

### 实践

添加新字段

```
-- 新字段在最后一位  
mysql> ALTER TABLE users ADD add_time INT(10) UNSIGNED NOT NULL DEFAULT 0;  
Query OK, 0 rows affected (0.01 sec)  
-- 新字段在某项后  
mysql> ALTER TABLE users ADD email VARCHAR(128) NOT NULL DEFAULT '' AFTER userpass;  
Query OK, 0 rows affected (0.03 sec)  
-- 新字段在第一位

```

mysql> -- 新增字段
mysql> ALTER TABLE users ADD column age TINYINT(3) NOT NULL DEFAULT 0 AFTER id;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

```

修改字段
```

ALTER TABLE users MODIFY email VARCHAR(64) NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.05 sec)
mysql> ALTER TABLE users CHANGE telno mobile CHAR(64) NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.03 sec)

```
删除字段
```

mysql> ALTER TABLE users DROP first_col;
Query OK, 0 rows affected (0.02 sec)

```
添加索引名称
```

mysql> ALTER TABLE users ADD INDEX (email);
Query OK, 0 rows affected (0.01 sec)

```
删除索引
```

mysql> ALTER TABLE users DROP INDEX email;
Query OK, 0 rows affected (0.02 sec)

```
新建唯一索引
```

mysql> ALTER TABLE users DROP INDEX users_username;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE users ADD UNIQUE (username);
Query OK, 0 rows affected (0.02 sec)

```
修改表名
```

mysql> ALTER TABLE users RENAME user;
Query OK, 0 rows affected (0.01 sec)

```
删除主键
```

-- 删除主键
ALTER TABLE users MODIFY id INT(10) NOT NULL,DROP PRIMARY KEY;

## 练习

创建一张用户表，含基本信息：如用户名、性别、年龄等信息；查看该表结构；清空该表；删除该表；

```
CREATE TABLE IF NOT EXISTS users (
	id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(16) NOT NULL DEFAULT '' COLLATE utf8_bin COMMENT '用户名',
	userpass CHAR(32) NOT NULL DEFAULT '' COMMENT '用户密码(加密过）',
	age TINYINT(3) UNSIGNED NOT NULL  DEFAULT 0,
	sex ENUM('男', '女', '未知') NOT NULL DEFAULT '未知',
	mob CHAR(11) NOT NULL DEFAULT '' UNIQUE,
	birth DATE NOT NULL DEFAULT '1970-01-01',
	index(username)
)  ENGINE = INNODB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

desc users;

truncate users;

drop table users;
```

创建一张用户表，含基本信息，然后做各种修改表的操作。

1.添加新字段email

```
ALTER TABLE users ADD 
  email VARCHAR(128) NOT NULL DEFAULT '' AFTER age;
```

2.修改userpass字段，char(40)

```
ALTER TABLE users MODIFY userpass CHAR(40) NOT NULL DEFAULT '' COMMENT 'user password';
```

3.修改mob字段为mobile

```
ALTER TABLE users CHANGE mob mobile CHAR(11) NOT NULL DEFAULT '';
```

4.删除email字段

```
ALTER TABLE users DROP email;
```

5.删除主键，然后新增主键

```
> 删除主键
ALTER TABLE users MODIFY id INT(10) UNSIGNED;  -- 不能含有auto_increment
ALTER TABLE users DROP PRIMARY KEY;

> 添加主键
ALTER TABLE users ADD PRIMARY KEY(id);
ALTER TABLE users MODIFY id INT(10) UNSIGNED AUTO_INCREMENT;  -- 非必须操作
```

6.给username添加唯一索引，然后再删除

```
ALTER TABLE users ADD UNIQUE name(username);
ALTER TABLE users DROP index name;
```

7.删除username的一般索引，然后再添加

```
ALTER TABLE users DROP index username;
ALTER TABLE `users` ADD INDEX (`username`) ;
```

8.修改表名称

```
ALTER TABLE users rename `user`;
```

数据库操作

Mysql列类型及列属性

MySQL

课程导航

数据表操作

文章目录