基本介绍 | 皮特王同学

# 基本介绍

数据表是数据库最重要的组成部分之一，数据是保存在数据表中
数据表由行(row)和列(column)来组成
每个数据表中至少有一列，行可以有零行、一行或多行
表名要求唯一，不要包含特殊字符，最好含义明确

# 创建数据表

CREATE TABLE [IF NOT EXISTS] tbl_name(
    字段名称 字段类型 [完整性约束条件],
    字段名称 字段类型 [完整性约束条件],
    ...
) ENGINE=存储引擎 CHARSET=编码方式;

CREATE TABLE IF NOT EXISTS user(
    id INT,
    username VARCHAR(20),
    password CHAR(32),
    email VARCHAR(50),
    age TINYINT,
    card CHAR(18),
    tel CHAR(11),
    salary FLOAT(8,2),
    married TINYINT(1),
    addr VARCHAR(100),
    sex ENUM('男', '女', '保密')
) ENGINE=INNODB CHARSET=UTF8;

# 完整性约束条件

约束条件	描述
UNSIGNED	无符号，没有负数，从0开始
ZEROFILL	零填充，当显示长度不够的时候可以使用前补0的效果填充至指定长度
NOT NULL	非空约束，插入值的时候这个字段必须要有值
DEFAULT	默认值，如果插入记录的时候没有给字段赋值，则使用默认值
PRIMARY KEY	主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
UNIQUE KEY	唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但NULL值除外
AUTO_INCREMENT	自动增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
FOREIGN KEY	外键约束

# 测试主键

CREATE TABLE test_primary_key(
    id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(20)
);

-- 省略PRIMARY
CREATE TABLE test_primary_key1(
    id INT UNSIGNED KEY,
    username VARCHAR(20)
);

-- 单独添加
CREATE TABLE test_primary_key2(
    id INT UNSIGNED,
    username VARCHAR(20),
    PRIMARY KEY(id)
);

-- 复合主键
CREATE TABLE test_primary_key3(
    id INT UNSIGNED,
    courseId VARCHAR(20),
    username VARCHAR(20),
    email VARCHAR(50),
    PRIMARY KEY(id, courseId)
);

# 测试自增AUTO_INCREMENT

CREATE TABLE test_auto_increment(
    id INT UNSIGNED KEY AUTO_INCREMENT,
    username VARCHAR(20),
);

INSERT test_auto_increment(id, username) VALUES(NULL, 'E');

# 测试非空

CREATE TABLE test_not_null(
    a VARCHAR(20),
    b VARCHAR(20) NOT NULL
);

# 测试默认值

CREATE TABLE test_default(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 18,
    email VARCHAR(50) NOT NULL default 'admin@qq.com'
);

CREATE TABLE test_default1(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    sex ENUM('a','b','c') NOT NULL DEFAULT 'a'
);

INSERT test_default1(sex) VALUES(DEFAULT);
INSERT test_default1(id) VALUES(2);

# 测试UNIQUE KEY

CREATE  TABLE test_unique(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY,
    email VARCHAR(50) UNIQUE,
    card CHAR(18) UNIQUE
);

# 完整建表案例

CREATE TABLE IF NOT EXISTS `user`(
    `id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
    `username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    `password` CHAR(32) NOT NULL COMMENT '密码',
    `email` VARCHAR(50) NOT NULL UNIQUE COMMENT '邮箱',
    `age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
    `sex` ENUM('男', '女', '保密') NOT NULL DEFAULT '保密' COMMENT '性别',
    `tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话',
    `addr` VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '地址',
    `card` CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
    `married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已结婚',
    `salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

# 查看所有数据表

SHOW TABLES;
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

# 查看指定数据表的详细信息

SHOW CREATE TABLE tbl_name;

# 查看表结构

DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;

# 删除数据表

DROP TABLE [IF EXISTS] tbl_name;

# 表结构相关操作

# 添加字段

ALTER TABLE tbl_name 
ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称];

CREATE TABLE IF NOT EXISTS user1(
    id INT UNSIGNED AUTO_INCREMENT KEY
);
-- 添加用户名字段
ALTER TABLE user1 ADD username VARCHAR(20);

-- 添加密码字段
ALTER TABLE user1 ADD password CHAR(32) NOT NULL;

-- 添加邮箱字段
ALTER TABLE user1 ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;

-- 添加测试字段,添加到首位
ALTER TABLE user1 ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;

-- 添加多个字段，添加age，addr字段，删除email字段
ALTER TABLE user1
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '背景',
DROP email;

# 删除字段

ALTER TABLE tbl_name 
DROP 字段名称;

-- 删除test字段
ALTER TABLE user1 DROP test;

# 添加默认值

ALTER TABLE tbl_name
ALTER 字段名称 SET DEFAULT 默认值;

CREATE TABLE user2(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED NOT NULL DEFAULT 18,
    email VARCHAR(50) NOT NULL
);

-- 给email字段添加默认值admin@qq.com
ALTER TABLE user2
ALTER email SET DEFAULT 'admin@qq.com'

# 删除默认值

ALTER TABLE tbl_name
ALTER 字段名称 DROP DEFAULT;

-- 删除age字段默认值
ALTER TABLE user2
ALTER age DROP DEFAULT;

# 修改字段类型、约束条件

ALTER TABLE tbl_name
MODIFY 字段名称 字段类型 [约束条件] [FIRST|AFTER 字段名称];

CREATE TABLE user3(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(5) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL,
    email VARCHAR(10) NOT NULL
);

-- 将用户名字段的长度改为20
ALTER TABLE user3
MODIFY username VARCHAR(20) NOT NULL;

-- 将密码字段长度改为40
ALTER TABLE user3
MODIFY password CHAR(40) NOT NULL;

-- 将email字段改为VARCHAR(50) NOT NULL FIRST
ALTER TABLE user3
MODIFY email VARCHAR(50) NOT NULL FIRST;

# 修改字段名称、字段类型、约束条件

ALTER TABLE tbl_name
CHANGE 原字段名称 新字段名称 字段类型 [约束条件] [FIRST|AFTER 字段名称]

-- 将username名称改为user
ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL;

# 添加主键

ALTER TABLE tbl_name
ADD PRIMARY KEY(字段名称);

CREATE TABLE user4(
    id INT UNSIGNED,
    username VARCHAR(20) NOT NULL
);
ALTER TABLE user4
ADD PRIMARY KEY(id);

# 删除主键

ALTER TABLE tbl_name
DROP PRIMARY KEY;

ALTER TABLE user4
DROP PRIMARY KEY;

CREATE TABLE user4(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL
);

-- 将id的AUTO_INCREMENT去掉才能删除主键
ALTER TABLE user5
MODIFY id INT UNSIGNED;
ALTER TABLE user5
DROP PRIMARY KEY;

# 添加唯一索引

ALTER TABLE tbl_name
ADD UNIQUE KEY|INDEX [index_name] (字段名称)

CREATE TABLE user6(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE
);

-- 不指定索引名称（默认和字段名相同）
ALTER TABLE user6
ADD UNIQUE KEY(username);

-- 指定索引名称
ALTER TABLE user6
ADD UNIQUE INDEX uni_email(email);

# 删除唯一索引

ALTER TABLE tbl_name
DROP INDEX index_name;

ALTER TABLE user6
DROP INDEX username;

ALTER TABLE user6
DROP INDEX email;

# 修改数据表名称

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
RENAME TABLE tbl_name TO new_tbl_name;

ALTER  TABLE user6
RENAME TO user666;

ALTER TABLE user666
RENAME AS user6;

# 修改AUTO_INCREMENT的值

ALTER TABLE tbl_name AUTO_INCREMENT=值;

← 数值型