MySQL建表操作大全

建表语句

通用模板

简易版

    DROP TABLE IF EXISTS `user_tb`;	# 删除原表，IF EXISTS ：防止报错
    
    CREATE TABLE t1(
        `id` varchar(50) not null,
        `name` varchar(20)
    );
1
2
3
4
5
6

带主键版本

	DROP TABLE IF EXISTS `user_tb`;  # 删除原表，IF EXISTS ：防止报错
	
    CREATE TABLE `user_tb`(
        	# primary key ： 标注主键 - 主键唯一
        `id` varchar(50) not null primary key,	        				
        `name` varchar(20)    
    );
1
2
3
4
5
6
7

带主键版本 - 复合主键

	DROP TABLE IF EXISTS `user_tb`;  # 删除原表，IF EXISTS ：防止报错
   
    CREATE TABLE `user_tb`(
            `id` varchar(50) not null ,	        				
            `name` varchar(20), 
        	primary key (`id`,`name`)
    );
1
2
3
4
5
6
7

说明：

1、所谓的复合主键，就是指你表的主键含有一个以上的字段组成 。

2、上面的name和id字段组合起来就是你user_tb表的复合主键

3、它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性

一般情况下，主键的字段长度和字段数目要越少越好

4、一般在一个主键不能唯一标识这个表中的记录的时候才使用复合主键

比如商品SPU相等的时候
可能SPU1下有code为0010的SKU，SPU2下也有code为0010的SKU
这种情况下，就无法单纯依靠SPU来保证此条记录的唯一性
所以就靠这样的联合主键来确定这条记录的唯一性

带默认值

   	DROP TABLE IF EXISTS `user_tb`;  # 删除原表，IF EXISTS ：防止报错			
   
    CREATE TABLE `user_tb`(
            `id` varchar(50) not null primary key,	        				
            `name` varchar(20), 
        	`is_validity`  char(1) default '1' COMMENT '是否有效 1 ：有效 0：无效'
    ) COMMENT='带默认值的测试表'; 
1
2
3
4
5
6
7

说明：

1、char(1) ：只存储 1 0 用这个节省空间

2、COMMENT：为备注值

带默认值 - 主键自增、字段备注

	DROP TABLE IF EXISTS `user_tb`;  # 删除原表，IF EXISTS ：防止报错	
    CREATE TABLE `user_tb`(
                `id` int(11) not null AUTO_INCREMENT comment 'id' ,	       				
                `name` varchar(20) comment '名字', 
                `is_validity`  char(1) default '1' COMMENT '是否有效 1 ：有效 0：无效',
				primary key(`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='带默认值的测试表'; 
1
2
3
4
5
6
7

说明：

主键格式需要可加格式，才可以自增，这里的int 是可以的
完整模板

	DROP TABLE IF EXISTS `user_tb`;  # 删除原表，IF EXISTS ：防止报错	
    CREATE TABLE `user_tb`(
          `id` varchar(50) COLLATE utf8_bin NOT NULL comment 'id',   				
          `custimer_name` varchar(50) COLLATE utf8_bin DEFAULT NULL comment '客户名字', 
        	`order_code` bigint(20) COLLATE utf8_bin DEFAULT NULL COMMENT '订单CODE',
        	`goods_code` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '商品CODE',
        	`goods_name` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '商品名称',
        	`goods_market_price` decimal(18,6) DEFAULT NULL COMMENT '商品市场价钱',
        	`goods_sell_price` decimal(18,6) DEFAULT NULL COMMENT '商品销售价钱',
        	`order_number` int(10) DEFAULT NULL COMMENT '订单商品数量',
        	`pay_fee` decimal(18,6)  DEFAULT NULL COMMENT '付款金额',
       		`pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  			 `order_type` varchar(5) COLLATE utf8_bin DEFAULT NULL  COMMENT '(订单类型 10:实物商品 20：虚拟商品 30：其他商品)',
  			  `order_status` varchar(5) COLLATE utf8_bin DEFAULT NULL COMMENT '(订单状态：10：待付款；20：已支付；30：支付失败；40：超时未支付订单自动关闭；',
          `is_validity`  char(1) default '1' COMMENT '是否有效 1 ：有效 0：无效',
        	`create_code` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人CODE',
        	`create_name` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人名称',
       		`create_date` datetime DEFAULT NULL COMMENT '创建时间',
        	`update_code` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人CODE',
        	`update_name` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人名称',
       		`update_date` datetime DEFAULT NULL COMMENT '修改时间',
        	PRIMARY KEY (`id`),
  			UNIQUE KEY `index_order_code` (`order_code`)        	
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='完整测试表';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

注意点：

1、MySQL的SQL特性：表名和字段名需要加反引号引住；
2、decimal(18,6)：类型可用于交易金额，精度更准确，double会丢失精度；
3、COMMENT：中文描述
4、ENGINE=InnoDB： 使用InnoDB引擎（聚集索引）
5、DEFAULT CHARSET=utf8： 选择合适的编码、 默认编码集为utf-8
6、COLLATE utf8_bin ：以二进制值比较，也就是区分大小写，collate是核对的意思（拓展：如果是uft-8_general_ci ：普通比较，不区分大小写）
7、varchar(50)：如果需要使用AUTO_INCREMENT id需要设置成 可加类型，比如int

索引与主键

	- 增加索引
		- ALTER TABLE 库点table_name ADD PRIMARY KEY ( `column` );
		- column为添加的索引字段
	- 查询表中存在的索引信息
		- show indexes from 库点table_name
	- 增加索引
		-  ALTER TABLE 库点table_name ADD INDEX ( `column` );
		-  column 为添加的索引字段
	- 删除索引
		- alter table 库点table_name drop index `column`,add  UNIQUE (`column`);
		- unique的作用：保证唯一
1
2
3
4
5
6
7
8
9
10
11

修改表结构

	1、修改编码
	- ALTER TABLE 库点table_name  MODIFY COLUMN `column` varchar(50) 
	- CHARACTER SET utf8 COLLATE utf8_bin NULL;
	
	2、修改注释
		2.1、修改表注释
		- ALTER TABLE student COMMENT '学生表';
		2.2、修改列注释
		- ALTER TABLE student MODIFY COLUMN name VARCHAR(100) COMMENT '姓名';
	
	3、修改表中字段长度
	- 3.1、表中已存有数据：ALTER table 表名 MODIFY (字段名 字段类型(长度));
	- 3.2、表中未存有数据：ALTER TABLE 表名 MODIFY 字段名 字段类型（长度）;

	4、添加字段
	- ALTER TABLE prod_fsk_statistics.calendar ADD year_ int COMMENT '年份';
	- ALTER TABLE prod_fsk_statistics.calendar ADD month_ int COMMENT '月份';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

增删查改

1.1、插入单条

	-- INSERT INTO 表名 (字段1，字段2，......)  VALUES (值1，值2，.....);
	insert into 库点table_name (`column_1`,`column_2`)
	VALUES (20,'商品_name');
1
2
3

1.2、批量插入

	-- INSERT INTO 表名 (字段1，字段2，......）VALUES (值1，值2，......),(值1，值		2，......),(值1，值2，......);
	INSERT INTO form (id,username,age）
	VALUES 
		(2,"王二",22),
		(3,"周三",23),
		(4,"刘四",24),
		(5,"吴五",25),
		(6,"陆六",26);
1
2
3
4
5
6
7
8

2、修改

	-- UPDATE 表名（修改）	SET（修改哪些字段）WHERE（查询条件）
	-- UPDATE 表名 SET 字段1 = 值1，字段2 = 值2，...... WHERE 字段1 = 值1；
	UPDATE form SET username = "张张",age = 22 WHERE id = 1;
1
2
3

3、删除

	-- 删除语句
	-- DALETE FROM 表名 WHERE 字段1 = 值1
	DELETE FROM form WHERE username = "李四";
1
2
3

MySQL中utf8和utf8mb4有什么区别

1、使用方法

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

2、内容描述

那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等(utf8的缺点)。

通常，计算机在存储字符时，会根据不同类型的字符以及编码方式分配存储空间。例如以下几种编码方式;

①ASCII编码中，一个英文字母（不分大小写）占用一个字节的空间，一个中文汉字占用两个字节的空间。一个二进制的数字序列，在计算机中作为一个数字单元存储时，一般为8位二进制数，换算为十进制。最小值0，最大值255。

②UTF-8编码中，一个英文字符占用一个字节的存储空间，一个中文（含繁体）占用三个字节的存储空间。

③Unicode编码中，一个英文占用两个字节的存储空间，一个中文（含繁体）占用两个字节的存储空间。

④UTF-16编码中，一个英文字母字符或一个汉字字符存储都需要占用2个字节的存储空间（Unicode扩展区的一些汉字存储需要4个字节）。

⑤UTF-32编码中，世界上任何字符的存储都需要占用4个字节的存储空间。

既然utf8能兼容绝大部分的字符，为什么要扩展utf8mb4？

随着互联网的发展，产生了许多新类型的字符，例如emoji这种类型的符号，也就是我们通常在聊天时发的小黄脸表情，这种字符的出现不在基本多平面的Unicode字符之中，导致无法在MySQL中使用utf8存储，MySQL于是对utf8字符进行了扩展，增加了utf8mb4这个编码。

所以，设计数据库时如果想要允许用户使用特殊符号，最好使用utf8mb4编码来存储，使得数据库有更好的兼容性，但是这样设计会导致耗费更多的存储空间。