SQL

数字

类型	字节	取值范围（有符号）	取值范围（无符号）	描述	备注
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	-263 ~ 263	0 ~ -1 264-1	较大的数据
float	4			浮点数 7 个有效位
double	8			浮点数 15 个有效位
decimal	128			字符串形式的浮点数 28 个有效位	不存在精度损失，常用于银行帐目计算

字符串

类型	范围	备注
char	0 ~ 255
varchar	0 ~ 65535	常用
tinytext	2^8 - 1
varchar	2^16 - 1	大文本

日期

类型	格式	备注
date	YYYY-MM-DD
time	HH:mm:ss
datetime	YYYY-MM-DD HH:mm:ss	常用
timestamp		1970.1.1 年到现在的毫秒数 常用
year		年份

枚举

字段属性

Unsigned

• 无符号的整数
• 声明后值不能为负数

Zerofill

• 0 填充
• 不足的位数使用 0 填充

Auto Increment

• 自增，在上一条记录的基础上 +1
• 通常用来设计唯一的主键 index ，必须是整数类型
• 可以自定义起始值和步长

Not Null

• 设置 Not Null ，如果不赋值就会报错
• Null，如果不填写则为 Null

默认

• 设置默认值
• 如果不设置值，则为默认值

每个表都要有下面 5 个字段

• id: 主键
• version: 乐观锁
• is_delete: 逻辑删除
• gmt_create: 创建时间
• gmt_update: 更新时间

创建数据库

CREATE DATABASE [IF NOT EXISTS] qa

CREATE DATABASE dev

CREATE DATABASE IF NOT EXISTS qa

删除数据库

DROP DATABASE [IF EXISTS] qa

DROP DATABASE qa
DROP DATABASE IF EXISTS qa

使用数据库

USE `qa`

-- 如果表名或者字段名是一个特殊字符需要带 ``
SELECT `user` FROM student

查看数据库

查看所有的数据库

SHOW DATABASES

---

title: 表

创建表

CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(4) PRIMARY  KEY AUTO_INCREMENT NOT NULL COMMENT '主键',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` TIMESTAMP DEFAULT NULL COMMENT '生日',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱'
)ENGINE=INNODB DEFAULT CHARACTER SET utf8

• 表的名称和字段使用``括起来
• AUTO_INCREMENT 自增
• 所有语句后面加, （英文的）最后一个不要加
• 字符串使用单引号括起来
• PRIMARY KEY主键，一般一个表只有一个主键

常用命令

查看创建表的语句

SHOW CREATE TABLE student

student  CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gender` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` timestamp NULL DEFAULT NULL COMMENT '生日',
  `email` varchar(30) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

查看表结构

DESC student

外键约束

建表的时候直接添加约束

CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(4) PRIMARY  KEY AUTO_INCREMENT NOT NULL COMMENT '主键',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `gradeid` INT(10) NOT NULL COMMENT '年级',
  `birthday` TIMESTAMP DEFAULT NULL COMMENT '生日',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`id`)
)ENGINE=INNODB DEFAULT CHARACTER SET utf8

CREATE TABLE IF NOT EXISTS `grade`(
  `id` INT(4) PRIMARY  KEY AUTO_INCREMENT NOT NULL COMMENT '主键',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名'
)ENGINE=INNODB DEFAULT CHARACTER SET utf8

建完表之后建立约束

CREATE TABLE IF NOT EXISTS `student`(
  `id` INT(4) PRIMARY  KEY AUTO_INCREMENT NOT NULL COMMENT '主键',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` TIMESTAMP DEFAULT NULL COMMENT '生日',
  `gradeid` INT(10) NOT NULL COMMENT '年级',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱'
)ENGINE=INNODB DEFAULT CHARACTER SET utf8

CREATE TABLE IF NOT EXISTS `grade`(
  `id` INT(4) PRIMARY  KEY AUTO_INCREMENT NOT NULL COMMENT '主键',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名'
)ENGINE=INNODB DEFAULT CHARACTER SET utf8

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`id`)

以上使用都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）

• 维护麻烦，要删除被应用的表，必须先删除应该的表
• 可能导致死锁，性能下键

最佳实践

• 数据库只是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 想用多张表的数据，想使用外键，使用程序实现

新增

 INSERT INTO `表名` (`字段名`,`字段名`,`字段名`, ...) VALUES('值 1','值二','值三', ...);

INSERT INTO `grade` (`name`) VALUES ('大一')

-- 如果没有写字段名称 则值要和表字段一一对应
INSERT INTO `grade` VALUES(2, '大二')

-- 插入多个值
INSERT INTO `grade` (`name`) VALUES ('大三'),('大四')

修改

-- 如果没有条件则更新所有
UPDATE `grade` SET `name`='update'

UPDATE `grade` SET `name`='update' WHERE `id`=1

删除

删除指点的数据

DELETE FROM `grade`
TRUNCATE `grade`

DELETE FROM `grade` WHERE `id`=1

删除表中所有数据

DELETE FROM `grade`

TRUNCATE TABLE `grade`

TRUNCATE 优点 （推荐使用）

• 重新设置自增列，计数器会归零
• 不会影响事务