mysql对表，数据，索引的操作sql

对表的操作

新建表

创建一个名为rwh_test的表，id为主键自增

-- 新建表
CREATE TABLE rwh_test(`id` int NOT NULL auto_increment PRIMARY KEY COMMENT '主键id',`username` VARCHAR(20) DEFAULT NULL COMMENT '用户名',`age` int DEFAULT NULL COMMENT '年龄',`create_date` datetime DEFAULT NULL COMMENT '创建时间',`create_by` VARCHAR(20) DEFAULT NULL COMMENT '创建人',`update_date` datetime DEFAULT NULL COMMENT '修改时间',`update_by` VARCHAR(20) DEFAULT NULL COMMENT '修改人'
);

查看表结构（表中所有字段）

1，DESCRIBE 表名;

DESCRIBE rwh_test;

或者简写成：DESC 表名;

DESC rwh_test;

结果如下：

2，SHOW CREATE TABLE 表名;

-- SHOW CREATE TABLE 表名;
SHOW CREATE TABLE rwh_test;

结果如下：

其中Create Tabel里面的内容如下：

CREATE TABLE `rwh_test` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',`username` varchar(20) DEFAULT NULL COMMENT '用户名',`age` int(11) DEFAULT NULL COMMENT '年龄',`create_date` datetime DEFAULT NULL COMMENT '创建时间',`create_by` varchar(20) DEFAULT NULL COMMENT '创建人',`update_date` datetime DEFAULT NULL COMMENT '修改时间',`update_by` varchar(20) DEFAULT NULL COMMENT '修改人',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改表名

RENAME TABLE 旧表名 TO 新表名;

RENAME TABLE rwh_test TO rwh_test1;

ALTER TABLE 旧表名 RENAME TO 新表名;

ALTER TABLE rwh_test1 RENAME TO rwh_test;

删除表

1，DROP TABLE 表名;

删除整个表，表没了，表中数据没了，表结构没了，释放了存储空间，啥都没了

DROP TABLE rwh_test;

2，DELETE FROM 表名 WHERE id=1;

删除表中符合条件的数据

DELETE FROM rwh_test WHERE id=1;

DELETE FROM 表名;

删除表中所有数据，数据没了，表和表结构都在，没有释放存储空间

DELETE FROM rwh_test;

3，TRUNCATE TABLE 表名;

删除表中数据，数据没了，表和表结构都在，释放了存储空间

TRUNCATE TABLE rwh_test;

修改表结构

新增字段

新增一个字段（默认放到所有字段最后面）

如：新增“备注”字段

ALTER TABLE rwh_test ADD COLUMN remark VARCHAR(32) DEFAULT NULL COMMENT '备注';

新增一个字段到指定字段后面

如：在age字段新增字段address（地址）

ALTER TABLE rwh_test ADD COLUMN address VARCHAR(32) DEFAULT NULL COMMENT '地址' AFTER age;

新增多个字段

如：新增haha字段和hehe字段

ALTER TABLE rwh_test 
ADD COLUMN haha VARCHAR(32) DEFAULT NULL COMMENT '新字段1',
ADD COLUMN hehe VARCHAR(32) DEFAULT NULL COMMENT '新字段2';

新增多个字段到指定字段后面

如：新增enen字段在haha字段后面，oo字段在hehe字段后面

ALTER TABLE rwh_test 
ADD COLUMN enen VARCHAR(32) DEFAULT NULL COMMENT '新字段3' AFTER haha,
ADD COLUMN oo VARCHAR(32) DEFAULT NULL COMMENT '新字段4' AFTER hehe;

修改字段

修改字段名字

如：把haha字段改为haha123

ALTER TABLE rwh_test CHANGE COLUMN haha haha123 VARCHAR(20) DEFAULT NULL COMMENT '新字段1';

修改字段类型，大小，默认值，备注

如：把hehe字段的类型改为int，默认值改为0，备注改为“新字段222”

ALTER TABLE rwh_test MODIFY COLUMN hehe int DEFAULT 0 COMMENT '新字段222';

删除字段

删除一个字段

如：删除字段oo

ALTER TABLE rwh_test DROP COLUMN oo;

删除多个字段

如：删除haha123，enen，hehe字段

ALTER TABLE rwh_test 
DROP COLUMN haha123,
DROP COLUMN enen;
DROP COLUMN hehe;

对数据的操作

插入数据

插入一条完整的数据

INSERT INTO rwh_test 
(id,username,age,address,create_date,create_by,update_date,update_by,remark)
VALUES
(1,'张三',18,'北京',NOW(),'rwh',NOW(),'rwh','这是备注');

插入一条不完整的数据

备注：

这里的字段名和值要一个一个对应，比如第一个字段是username，第一个值就必须填姓名，不能填年龄或者其他值。

有些数据插入时可以不给值，有些必须给，not null 类型的就必须给值，这里id也是not null ，为啥可以不给值呢，因为id是自增的，你不给值，他会自增加1给一个默认值的。

不是not null 类型的字段都可以不给值。

INSERT INTO rwh_test 
(username,age,address,create_date,create_by,update_date,update_by)
VALUES
('李四',20,'上海',NOW(),'rwh',NOW(),'rwh');

一次插入多条数据

INSERT INTO rwh_test 
(username,age,address,create_date,create_by,update_date,update_by)
VALUES
('王五',20,'上海',NOW(),'rwh',NOW(),'rwh'),
('赵六',20,'上海',NOW(),'rwh',NOW(),'rwh'),
('孙七',20,'上海',NOW(),'rwh',NOW(),'rwh');

查询数据

SELECT * FROM rwh_test;

修改数据

UPDATE 表名 
SET 字段1=值1,字段2=值2
WHERE 字段1=值1;UPDATE rwh_test 
SET age=22,address='广州'
WHERE username='张三';

删除数据

DELETE FROM rwh_test WHERE username = '孙七';

对索引的操作

新建索引

新建表时创建索引

-- 创建主键索引,id
CREATE TABLE user_test(`id` int NOT NULL auto_increment PRIMARY KEY COMMENT '主键',·username· VARCHAR(20) COMMENT '姓名'
);-- 创建主键索引
CREATE TABLE user_test(`id` int NOT NULL COMMENT '主键',`username` VARCHAR(20) COMMENT '姓名',PRIMARY KEY (id)
);-- 创建唯一索引，给username字段创建唯一索引，索引名叫index_username
-- 格式：UNIQUE INDEX 索引名(字段)
CREATE TABLE user_test(`id` int NOT NULL auto_increment PRIMARY KEY COMMENT '主键',`username` VARCHAR(20) COMMENT '姓名',UNIQUE INDEX index_username(username)
);-- 创建普通索引，给email字段创建普通索引，索引名叫index_email
-- 格式：INDEX 索引名(字段)
CREATE TABLE user_test(`id` int NOT NULL auto_increment PRIMARY KEY COMMENT '主键',`username` VARCHAR(20) COMMENT '姓名',`email` VARCHAR(20) COMMENT '邮箱',INDEX index_email(email)
);-- 创建组合索引,给username和email字段创建组合索引，索引名叫index_username_email
-- 格式：INDEX 索引名(字段1，字段2...)
CREATE TABLE user_test(`id` int NOT NULL auto_increment PRIMARY KEY COMMENT '主键',`username` VARCHAR(20) COMMENT '姓名',`email` VARCHAR(20) COMMENT '邮箱',INDEX index_username_email(username,email)
);

在已有的表中直接创建索引

-- 创建唯一索引,给username字段创建唯一
-- 格式：CREATE UNIQUE INDEX 索引名 ON 表名(字段);
CREATE UNIQUE INDEX index_username ON user_test(username);-- 创建普通索引,给email字段创建普通索引，索引名叫index_email
-- 格式：CREATE INDEX 索引名 ON 表名(字段名);
CREATE INDEX index_email ON user_test(email);-- 创建组合索引，给username和email字段创建组合索引，索引名叫index_username_email
-- 格式：CREATE INDEX 索引名 ON 表名(字段1，字段2...);
CREATE INDEX index_username_email ON user_test(username,email);

通过修改表的方式创建索引

-- 创建主键索引
ALTER TABLE user_test ADD PRIMARY KEY (id);-- 创建唯一索引
ALTER TABLE user_test ADD UNIQUE INDEX index_username(username);-- 创建普通索引
ALTER TABLE user_test ADD INDEX index_email(email);-- 创建组合索引
ALTER TABLE user_test ADD INDEX index_username_email(username,email);

查看索引

-- 这3种方式都可以
-- 格式：SHOW INDEX FROM 表名
SHOW INDEX FROM user_test;SHOW KEYS FROM user_test;SHOW indexes FROM user_test;

各个字段含义

字段名	含义
Table	索引所在的数据表的名称
Non_unique	索引是否可以重复，0表示不可以，1表示可以
Key_name	索引的名称，如果索引是主键索引，则它的名称为PRIMARY
Seq_in_index	建立索引的字段序号值，默认从1开始
Column_name	建立索引的字段
Collation	索引字段是否排序，A表示有排序，NULL表示没有排序
Cardinality	MySQL连接时使用索引的可能性（精确度不高），值越大可能性越高
Sub_part	前缀索引的长度，如字段值都被索引，则 Sub_part 为NULL
Packed	关键词如何被压缩，如果没有被压缩，则为NULL
Null	索引字段是否含有NULL值，YES表示含有，NO表示不含有
Index_type	索引方式，可选值有FULLTEXT 、HASH 、BTREE 、RTREE
Comment	索引字段的注释信息
Index_comment	创建索引时添加的注释信息
Visible	索引对查询优化器是否可见。YES表示可见，NO表示不可见
Expression	使用什么表达作为建立索引的字段，NULL表示没有

修改索引

没有修改索引的sql，所谓的修改索引，就是删除旧的索引，新建新的索引

删除索引

-- 删除索引，方式一
-- 格式：DROP INDEX 索引名 ON 表名;
DROP INDEX index_username ON user_test;-- 删除索引，方式二
-- 格式：ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE user_test DROP INDEX index_username_email;

常用字段类型

数值类型

整数类型默认都是有符号整数，就是既可以存正数又可以存负数

类型名称	字节数	范围（有符号）含边界	范围（无符号）含边界
tinyint	1	-128~127	0~255
smallint	2	-32768~32767	0~65535
mediumint	3	-8388608~8388607	0~16777215
int	4	-2147483648~2147483647	0~4294967295
bigint	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

整型经常被用到，比如 tinyint、int、bigint 。默认是有符号的，若只需存储无符号值，可增加 unsigned 属性。

int(M)中的 M 代表最大显示宽度，并不是说 int(1) 就不能存储数值10了，不管设定了显示宽度是多少个字符，int 都是占用4个字节，即int(5)和int(10)可存储的范围一样。

存储字节越小，占用空间越小。所以本着最小化存储的原则，我们要尽量选择合适的整型，例如：存储一些状态值或人的年龄可以用 tinyint ；主键列，无负数，建议使用 int unsigned 或者 bigint unsigned，预估字段数字取值会超过 42 亿，使用 bigint 类型。

浮点型主要有 float，double 两个，浮点型在数据库中存放的是近似值，例如float(6,3)，如果插入一个数123.45678，实际数据库里存的是123.457，但总个数还以实际为准，即6位，整数部分最大是3位。float 和 double 平时用的不太多。

类型	大小	用途
float	4字节	单精度浮点数值
double	8字节	双精度浮点数值

定点型字段类型有 decimal 一个，主要用于存储有精度要求的小数。

类型	大小	用途
decimal	对decimal(M,D)，如果M>D，则为M+2字节，否则为D+2字节	精确小数值

注意：存金额一定要用decimal类型，可以很精确，用float和double都不精确，且存储和查询结果不一样（会四舍五入，所以存的和取的不一致）。

DECIMAL 从 MySQL 5.1 引入，列的声明语法是 DECIMAL(M,D) 。NUMERIC 与 DECIMAL 同义，如果字段类型定义为 NUMERIC ，则将自动转成 DECIMAL 。

对于声明语法 DECIMAL(M,D) ，自变量的值范围如下：

M是最大位数（精度），范围是1到65。可不指定，默认值是10。

D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。

例如字段 salary DECIMAL(5,2)，能够存储具有五位数字和两位小数的任何值，因此可以存储在salary列中的值的范围是从-999.99到999.99。

字符串类型

类型	大小	用途
char	0~255字节	定长字符串
varchar	0~65535字节	变长字符串
tinytext	0~255字节	短文本字符串
text	0~65535字节	长文本数据
longtext	0~4294967295字节	极大文本数据
tinyblob	0~255字节	二进制字符串
blob	0~65535字节	二进制形式的长文本数据
longbolb	0~4294967295字节	二进制形式的极大文本数据

其中 char 和 varchar 是最常用到的。char 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当保存 char 值时，在它们的右边填充空格以达到指定的长度，当检索到 char 值时，尾部的空格被删除掉。varchar 类型用于存储可变长字符串，存储时，如果字符没有达到定义的位数，也不会在后面补空格。

char(M) 与 varchar(M) 中的的 M 表示保存的最大字符数，单个字母、数字、中文等都是占用一个字符。char 适合存储很短的字符串，或者所有值都接近同一个长度。例如，char 非常适合存储密码的 MD5 值，因为这是一个定长的值。对于字符串很长或者所要存储的字符串长短不一的情况，varchar 更加合适。

我们在定义字段最大长度时应该按需分配，提前做好预估，能使用 varchar 类型就尽量不使用 text 类型。除非有存储长文本数据需求时，再考虑使用 text 类型。

BLOB 类型主要用于存储二进制大对象，例如可以存储图片，音视频等文件。日常很少用到，有存储二进制字符串时可以考虑使用。

时间类型

类型	大小	显示格式	存储范围
year	1字节	YYYY	1902~2155
time	3字节	hh:mm:ss或hhh:mm:ss	-838:59:59~838:59:59
date	3字节	YYYY-MM-DD	1000-01-01~9999-12-31 23:59:59
datetime	8字节	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00~9999-12-31 23:59:59
timestamp	4字节	YYYY-MM-DD hh:mm:ss	UTC 1970-01-01 00:00:00~2038-01-19 03:14:07

涉及到日期和时间字段类型选择时，根据存储需求选择合适的类型即可。

关于 DATETIME 与 TIMESTAMP 两种类型如何选用，可以按照存储需求来，比如要求存储范围更广，则推荐使用 DATETIME ，如果只是存储当前时间戳，则可以使用 TIMESTAMP 类型。不过值得注意的是，TIMESTAMP 字段数据会随着系统时区而改变但 DATETIME 字段数据不会。总体来说 DATETIME 使用范围更广。

参考文献：

MySQL学习总结（索引的概述、索引的创建、索引的查看、索引的删除）_drop index删除索引-CSDN博客

常用MySQL字段类型解析_mysql常用的字段类型及简要说明-CSDN博客