MySQL学习笔记——数据表相关操作

什么是数据表？

  数据表是数据库最重要的组成部分之一，是其它对象的基础。

• 数据表是存储数据的数据结构
• 数据表是包含了特定实体类型的数据
• 数据表由行(row)和列(column)构成的二维网络
• 数据表一定先有表结构，再有数据
• 数据表至少有一列，可以没有行或者多行
• 数据表名称要求唯一，而且不要包含特殊字符

如何创建数据表？

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

完整性约束条件：

• PRIMARY KEY主键
• AUTO_INCREMENT自增长
• FOREIGN KEY外键
• NOT NULL非空
• UNIQUE KEY唯一
• DEFAULT默认值

代码示例：

-- 创建学员表(user)
-- 编号 id
-- 用户名 username
-- 年龄 age
-- 性别 sex
-- 邮箱 email
-- 地址 addr
-- 生日 birth
-- 薪水 salary
-- 电话 tel
-- 是否结婚 married
-- 注意：当需要输入中文的时候，需要临时转换客户端的编码方式
-- SET NAMES GBK;
-- 字段注释 通过COMMENT 注释内容 给字段添加注释
CREATE TABLE IF NOT EXISTS `user`(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM('男','女','保密'),
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0代表未结婚，非0代表已婚'
)ENGINE=INNODB CHARSET=UTF8;

-- 创建课程表 course
-- 编号 cid
-- 课程名称courseName
-- 课程描述courseDesc
CREATE TABLE IF NOT EXISTS course(
cid TINYINT,
courseName VARCHAR(50),
courseDesc VARCHAR(200)
);


-- 创建新闻分类表cms_cate
-- 编号、分类名称、分类描述
CREATE TABLE IF NOT EXISTS cms_cate(
id TINYINT,
cateName VARCHAR(50),
cateDesc VARCHAR(200)
)ENGINE=MyISAM CHARSET=UTF8;


-- 创建新闻表 cms_news
-- 编号、新闻标题、新闻内容、新闻发布时间、点击量、是否置顶
CREATE TABLE IF NOT EXISTS cms_news(
id INT,
title VARCHAR(50),
content TEXT,
pubTime INT,
clickNum INT,
isTop TINYINT(1) COMMENT '0代表不置顶，1代表置顶'
);

如何查看数据库中的数据表以及表结构？

• 查看数据库下的数据表
  SHOW TABLES
• 查看指定表的表结构
  DESC tbl_name
  DESCRIBE tbl_name
  SHOW COLUMNS FROM tbl_name

如何修改表结构？

• 修改表名

ALTER TABLE tbl_name RENAME [TO|AS] new_name
RENAME TABLE tbl_name TO new_name

• 添加字段

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

• 删除字段

ALTER TABLE tbl_name DROP 字段名称

• 修改字段

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

• 修改字段名称

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

• 添加默认值

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

• 删除默认值

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

• 添加主键

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type] (字段名称,...)

• 删除主键

ALTER TABLE tbl_name DROP PRIMARY KEY

• 添加唯一

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [索引名称](字段名称,...)

• 删除唯一

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

• 修改表的存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称

• 设置自增长的值

ALTER TABLE tbl_name AUTO_INCREMNET=值

代码示例：

-- 创建用户表
CREATE TABLE IF NOT EXISTS user10
(
    id       SMALLINT UNSIGNED KEY AUTO_INCREMENT,
    username VARCHAR(20)  NOT NULL UNIQUE,
    password CHAR(32)     NOT NULL,
    email    VARCHAR(50)  NOT NULL DEFAULT '382771946@qq.com',
    age      TINYINT UNSIGNED      DEFAULT 18,
    sex      ENUM ('男','女','保密')   DEFAULT '保密',
    addr     VARCHAR(200) NOT NULL DEFAULT '北京',
    salary   FLOAT(6, 2),
    regTime  INT UNSIGNED,
    face     CHAR(100)    NOT NULL DEFAULT 'default.jpg'
);

-- 将user10重命名成user11
ALTER TABLE user10 RENAME TO user11;

ALTER TABLE user11 RENAME AS user10;

ALTER TABLE user10 RENAME user11;

RENAME TABLE user11 TO user10;

-- 添加card 字段 CHAR(18) 
ALTER TABLE user10
    ADD card CHAR(18);

ALTER TABLE user10
    ADD test1 VARCHAR(100) NOT NULL UNIQUE;

ALTER TABLE user10
    ADD test2 VARCHAR(20) NOT NULL FIRST;

ALTER TABLE user10
    ADD test3 INT NOT NULL DEFAULT 100 AFTER username;

-- 选中一次表，完成多个操作
ALTER TABLE user10

    ADD test4 INT NOT NULL DEFAULT 123 AFTER password,

    ADD test5 FLOAT(6, 2) FIRST,

    ADD test6 SET ('A','B','C');

-- 删除test6字段

ALTER TABLE user10
    DROP test6;

-- 一次删除test2,test3,test4,test5
ALTER TABLE user10

    DROP test2,

    DROP test3,

    DROP test4,

    DROP test5;

-- 添加test字段删除addr字段
ALTER TABLE user10

    ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,

    DROP addr;


-- 将email VARCHAR(200) 

ALTER TABLE user10
    MODIFY email VARCHAR(200);


ALTER TABLE user10
    MODIFY email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com';

-- 将card字段移动到test字段之后
ALTER TABLE user10
    MODIFY card CHAR(18) AFTER test;

-- 将test字段修改为CHAR(32) NOT NULL DEFAULT '123' 移动到第一个位置
ALTER TABLE user10
    MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;

-- 将test字段改为test1
ALTER TABLE user10
    CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';

ALTER TABLE user10
    CHANGE test1 test VARCHAR(200) NOT NULL AFTER username;

ALTER TABLE user10
    CHANGE test test INT;

CREATE TABLE IF NOT EXISTS user11
(
    id       TINYINT UNSIGNED KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL UNIQUE,
    age      TINYINT UNSIGNED
);
ALTER TABLE user11
    ALTER age SET DEFAULT 18;

ALTER TABLE user11
    ADD email VARCHAR(50);


-- 删除默认值
ALTER TABLE user11
    ALTER age DROP DEFAULT;

ALTER TABLE user11
    ALTER email DROP DEFAULT;

-- 添加主键
CREATE TABLE IF NOT EXISTS test12
(
    id INT
);

ALTER TABLE test12
    ADD PRIMARY KEY (id);

CREATE TABLE IF NOT EXISTS test13
(
    id       INT,
    card     CHAR(18),
    username VARCHAR(20) NOT NULL
);

ALTER TABLE test13
    ADD PRIMARY KEY (id, card);

-- 删除test12表的主键
ALTER TABLE test12
    DROP PRIMARY KEY;

ALTER TABLE test13
    DROP PRIMARY KEY;

ALTER TABLE test12
    ADD CONSTRAINT symbol PRIMARY KEY index_type (id);


CREATE TABLE IF NOT EXISTS test14
(
    id INT UNSIGNED KEY AUTO_INCREMENT
);

ALTER TABLE test14
    MODIFY id INT UNSIGNED;
ALTER TABLE test14
    DROP PRIMARY KEY;

-- 测试添加唯一索引
CREATE TABLE IF NOT EXISTS user12
(
    id       TINYINT UNSIGNED KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    card     CHAR(18)    NOT NULL,
    test     VARCHAR(20) NOT NULL,
    test1    CHAR(32)    NOT NULL
);
ALTER TABLE user12
    ADD UNIQUE (username);

ALTER TABLE user12
    ADD CONSTRAINT symbol UNIQUE KEY uni_card (card);

ALTER TABLE user12
    ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1 (test, test1);

--删除唯一索引
ALTER TABLE user12
    DROP INDEX username;

ALTER TABLE user12
    DROP KEY uni_card;

ALTER TABLE user12
    DROP KEY mulUni_test_test1;

-- 修改表的存储引擎为MyISAM
ALTER TABLE user12
    ENGINE =MyISAM;

-- 修改自增长值

ALTER TABLE user12
    AUTO_INCREMENT = 100;

如何删除数据表

DROP TABLE [IF EXISTS] tbl_name[,tbl_name...]

代码示例：

-- 删除数据表
DROP TABLE user12;

DROP TABLE IF EXISTS user12;

DROP TABLE IF EXISTS user11,user10,user9;

DROP TABLE IF EXISTS user123,user8,user7,user6;