什么是数据表?
数据表是数据库最重要的组成部分之一,是其它对象的基础。
- 数据表是存储数据的数据结构
- 数据表是包含了特定实体类型的数据
- 数据表由行(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;