Kevin-Blog
注意事项:

  • 固定长度的字段放在前面，不固定的放在后面
  • SQL语句结束后必须加上;号
  • () 号中的最后一个不能带上,号
  • 数据库不应该保存上传文件，而应该保存的是该上传文件的地址
  • 可以在不使用 use 数据库名 的情况下直接使用 数据库名.表名 直接操作表 或 表中的数据，但是在创建表的时候还是需要先执行 use 数据库名 再执行创建表的命令


1. 创建表的格式

create table 表名(
    列名 类型 是否可以为空,
    列名 类型 是否可以为空
)engine=innodb/myisam default charset=编码方式

# 创建一张表

create table t1(
    id int, 
    name char(10), 
    age int null
) engine=innodb default charset=utf8;

2. 常用的数据类型

整数类型
tinyint
tinyint(5)
  • 小整数
  • 有符号的取值范围(包含负数): -128~127
  • 无符号的取值范围(不包含负数): 0~255
  • 如果使用了tinyint(5),插入数据2时,那么查询出来会是00002，所以一般会直接使用tinyint
int
int(5)
  • 整数
  • 有符号的取值范围(包含负数): -2147483648~2147483647
  • 无符号的取值范围(不包含负数): 0~4294967295
  • 如果使用了int(5),插入数据2时,那么查询出来会是00002，所以一般会直接使用int
bigint
  • 大整数
  • 有符号的取值范围(包含负数): -9223372036854775808~9223372036854775807
  • 无符号的取值范围(不包含负数): 0~18446744073709551615
  • 如果使用了bigint(5),插入数据2时,那么查询出来会是00002，所以一般会直接使用bigint

浮点数类型
float(m,d)
  • 单精度浮点数（非准确小数值）
  • m: 数字总个数，d:小数点后的个数
  • 数值越大越不准确
double(m,d)
  • 双精度浮点数（非准确小数值）
  • m: 数字总个数，d:小数点后的个数
  • 数值越大越不准确
  • 和float的区别，double的值会比float的值大一点才开始不准确
decimal
  • 准确的小数值（推荐使用）
  • m: 数字总个数，d:小数点后的个数，m最大值为65，d最大值为30
  • 对于精确数值计算时需要用此类型
  • decaimal能够存储精确值的原因在于其内部按照字符串存储

字符串类型
char(m)
  • char数据类型用于表示固定长度的字符串，可以包含最多达255个字符
  • m: 该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中
  • 和varchar的区别：即使保存字符串长度小于m长度，char也会自动补全剩下的长度，而varchar不会
  • char的优点：处理速度比varchar要快，因为char的长度是固定的
  • char的缺点: 浪费内存空间
  • 尽量使用 char 代替 varchar
varchar(m)
  • varchar数据类型用于变长的字符串（不是固定长度的字符串），可以包含最多达255个字符。
  • m: 该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中
  • 和char的区别：即使保存字符串长度小于m长度，char也会自动补全剩下的长度，而varchar不会，从而可以看出varchar更加节省内存空间 https://zhidao.baidu.com/question/56951710.html
  • varchar的优点: 节省内存空间
  • varchar的缺点: 处理速度比char要慢，因为varchar的长度是不固定的
  • 虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%，因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
  • 尽量使用 char 代替 varchar
text
  • text数据类型用于保存变长的大字符串，可以最多到65535 (2**16 − 1)个字符
mediumtext
  • mediumtext数据类型用于保存变长的大字符串，可以最多到16,777,215 (2**24 − 1)个字符
longtext
  • mediumtext数据类型用于保存变长的大字符串，可以最多到4,294,967,295 or 4GB (2**32 − 1)个字符
如果保存的字符串类型长度超过了 longtext 的最大长度，那么就应该以文件的形式保存到硬盘中，而数据库则保存该文件的路径
时间类型
date
YYYY-MM-DD（1000-01-01 ~ 9999-12-31）
time
HH:MM:SS（'-838:59:59' ~ '838:59:59'）
year
YYYY（1901 ~ 2155）
datetime
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59）
timestamp
YYYYMMDD HHMMSS（1970-01-01 00:00:00 ~ 2037 年某时）

  • enum 类型 -> 只能存储 enum 里面设置的值的任意一个 -> 使用场景: 选项固定且不经常修改的情况下使用

# 创建表

create table clothes(
    name varchar(40),
    size enum('sm', 'm', 'l', 'xl')
) engine=innodb default charset=utf8;

# 插入数据

insert into clothes(name,size) values('凯文','l');

  • set 类型 -> 只能存储 set 里面设置的值的任意组合 -> 使用场景: 选项固定且不经常修改的情况下使用

# 创建表

create table clothes(
    name varchar(40),
    size set('sm', 'm', 'l', 'xl')
) engine=innodb default charset=utf8;

# 插入数据

insert into clothes(name,size) values('凯文','m,l');

3. engine=innodb/myisam

  • innodb引擎 -> 支持事务（原子操作: 两个东西是一个整体，只要有一方出现失败就会回滚到最初状态）-> 例如: 用户A向用户B转￥100，但是在转账的过程中电脑突然死机导致数据库停止运作，此时用户A的钱已经转出去了，但是用户B没有收到钱，如果此时使用了innodb引擎那么就会将这笔钱打回给用户A，如果使用了myisam引擎那么这笔钱就会丢失，所以在创建表的时候最好将 engine 设置为 innodb

  • myisam引擎 -> 不支持事务，但是使用myisam引擎进行数据的存储会比innodb引擎要快

create table t1(
    id int, 
    name char(10), 
    age int null
) engine=innodb default charset=utf8;

4. null / not null

    • null -> 可以为空，且 MySQl 默认为空
    • not null -> 不能为空

create table t1(
    nid int not null,
    num int null
) engine=innodb default charset=utf8;

5. 默认值

create table t1(
    nid int not null default 2,
    num int not null
) engine=innodb default charset=utf8;

6. int类型中的 signed 和 unsigned

  • signed 允许存储负值
  • unsigned 不允许存储负值 -> int类型 默认为 unsigned 

create table t1(
    num int signed
) engine=innodb default charset=utf8;

10. 清空表

  • delete -> 清空表，如果将id设置为主键和自增列的话，那么此时再插入一条数据且该数据的id会接着上一次的id开始自增

# delete from 表名;

delete from t1;

  • truncate -> 清空表，如果将id设置为主键和自增列的话，那么此时再插入一条数据且该数据的id会从1重新开始

# truncate table 表名;

truncate table t1;

11. 修改表

  • 修改表名

# alter table 原表名 rename to 新表名;

alter table t1 rename to t2;

  • 添加字段

# alert table 表名 add 字段名 类型;

alter table employee add sid int;

  • 在某个字段后面添加字段

# alter table 表名 add column 字段名 类型 not null AFTER 在哪个字段后面添加的字段名;

alter table t1 add column age int not null AFTER aid;

  • 增加一个字段，设好数据类型，且不为空，添加注释

# alert table 表名 add 字段名 类型 not null comment '注释';

alert table t1 add sex varchar(10) not null comment '性别';

  • 修改字段类型

# alter table 表名 modify column 字段名 类型;

alter table t1 modify column name varchar(10);

  • 修改字段名字(要重新指定该字段的类型)

# alter table 表名 change 原字段名 新字段名 类型 not null;

alter table t1 change name app_name varchar(20) not null;

  • 设置字段不允许为空

# alter table 表名 modify column 字段名 类型 not null;

alter table t1 modify column name varchar(10) not null;

  • 删除字段

# alter table 表名 drop 字段名;

alter table t1 drop age;

  • 添加主键

# alter table 表名 add 字段名 类型 not null ,add primary key (字段名);

alter table t1 add aid int(5) not null ,add primary key (aid);  

  • 添加自增主键

# alter table 表名 add 字段名 类型 not null auto_increment,add primary key (字段名);

alter table t_app add aid int(5) not null auto_increment,add primary key (aid);

  • 删除主键

# alter table 表名 drop primary key;

alter table t1 drop primary key;

  • 添加外键

# alter table 表名 add constraint 外键名 foreign key (主表字段) references 从表名(从表字段);

alter table employee add constraint fk_scheduling_did foreign key (sid) references scheduling(id);

  • 删除外键

# alter table 表名 drop foreign key 外键名称;

alter table t1 drop foreign key fk_t1;

12.删除表

# drop table 表名;

drop table t1;

13.查看所有表

  • 查看该数据库下的所有表

show tables;


  • 查看表的结构

# desc 表名;

desc t1;


  • 查看表是如何创建的

# show create table 表名;

show create table t1;


  • 查看表示如何创建的 -> 格式化输出

# show create table 表名 \G;

show create table t1 \G;


14.查看变量

  • 查看当前会话的变量

show session variables like 'auto_inc%';


  • 查看全局变量

show global variables like 'auto_inc%';



← 视图 →