MySQL基础教程3-DDL(创建表)

DDL-创建表

• 所谓建表，就是一个声明字段的过程
  列选什么类型，列选什么属性

简单建个表:

create table people(
     -> age int,
     -> name varchar(10)
     -> );

建表语句:

一、列

列的类型:

• 数值型—整形、型，浮点型，定点型
• 字符串型—char，varchar，text
• 日期时间类型—2020-12-30 23:59:59

1.1.整型列

(1).各整型类型所占字节大小:

(2).整型列可选参数:

• unsigned: 无符号，列的值从0开始，不为负
• zerofill: 适用于 学号、编码 等，固定宽度的数字，可以用0填充至固定宽度
  学号–>1–>0001
  学号–>123–>0123
  思路: zerofill填充至多宽？M
  注意: zerofill属性默认决定列为unsigned类型
• M: 为zerofill 指定填充的宽度。只使用M没有任何意义，必须配合zerofill使用
  注意: M的数值不是为了限制数值宽度，只是为了当数值宽度不到M时，为其填充至宽度为M

例:

/* 为user表添加带有unsigned属性的number字段 */
alter table user add number tinyint unsigned;

/* 为user表添加带有zerofill属性的number字段，并为其指定宽度 */
alter table user add number tinyint(5) zerofill;

1.2.浮点列与定点列

• flocat(M,D): 浮点型
  **M**是精度,即总位数
  **D**是标度,即小数点后面的位数
• double: 浮点型
• decimal: 定点型
  **float/double浮点型数据会有精度损失,decmail**定点型,更精确

float/double官方文档:

1.3.字符型

MySQL字符串类型:

• Char: 定长类型
  char型，传入数据如果不够M个字符，内部用空格补齐，取出时再把**右侧空格删掉。这意味着:如果数据本身右侧有空格，将会丢失**
• Varchar: 变长类型

**注意:**char(M)限制的是字符，不是字节。即char(2) charset utf8，能存两个utf8字符，比如中国;
**char与varchar型的选择原则:

• 1.空间利用率：
  四字成语表:char(4)；
  微博个人简介140字:varchar(140)
• 2.速度：
  用户名: char

Char与Varchar:

Char右侧空格丢失:
注: 从截图中可以看出，最后输出的数据中的char型数据丢失了右侧的空格

其他类型的字符类型及容量:

• Text: 文本类型，可以存比较大的文本段，搜索速度慢。因此，如果不是特别大的内容，建议用char、varchar来代替。test不用默认值(加了也没用)
• Blob:二进制类型，用来存储图像、音频等二进制信息。
  **意义:**2进制，0-255都有可能出现。Blob在于防止因为字符集而导致信息丢失的问题。比如: 一张图片中有0xFF字节，这个在ascii字符集中认为非法，在入库的时候被过滤了。
• **Enum:**枚举类型。意思是值是定义好的，就在几个枚举范围内。
  例: gender(‘男’,‘女’)；insert时只能选"男",“女”。
• **Set:**同Enum。但insert时可同时选多个值。

1.4.日期时间类型

二、列的默认值

列的默认值

• 1.NULL 查询不便
• 2.NULL的索引效果不高
  所以，实用中要避免列的值为NULL
• 如何避免–声明列 :
  NOT NULL default 默认值

三、主键与自增

• **主键:**primary key，此列不重复，能够区分每一行

/* 第一重指定方式 */
create table t1(
     -> id int primary key,
     -> name char(2)
);
/* 第一重指定方式 */
create table t2(
     -> id int,
     -> name char(2),
     -> primary key(id)
);

• 自增(auto_increment): 一张表只能有1列为auto_increment，且此列必须加索引(index/key)
  一般实用中自增与主键搭配使用

create table t3(
     -> id int auto_increment,
     -> name char(2),
     -> key id(id)
);

• 表设计案例:某社交网站

建表秘诀:
1.定长与变长分离
2.常用与不常用分离

上面表设计的并不是很好，还可以优化
**分析:**张这表除了username/intro列之外，每一行都是定长的，我们不妨让其所有列都定长。可以极大地提高查询速度。

MySQL建立上述表表指令:

create table regist3(
    -> id int unsigned primary key auto_increment,
    -> username char(10) not null default ' ',
    -> gender tinyint not null default 0,
    -> weight tinyint unsigned not null default 0,
    -> Birth Date not null default '0000-00-00',
    -> Salary Decimal(8,2) not null default '000000.00',
    -> lastlogin int unsigned not null default 0
);

create table regist1(
    -> id int unsigned primary key auto_increment,
    -> username char(10) not null default ' ',
    -> intro varchar(1500) not null default ''
);

效果图:

四、列的增删改

4.1.添加列

/*格式:*/
alter table 表名
     -> add 列名 列类型 列属性... 
注意: 默认将该列添加到表的最后

/*实例1:*/
alter table student
     -> add height tinyint unsigned not null default 0;

/*实例2: 添加到weight列后面*/
alter table student
     -> add height tinyint unsigned after weight;

4.2.删除列

alter table student
     -> drop column height;

4.3.修改列

/*修改列名并修改该列的属性*/
alter table student
     -> change height shengao smallint;

/*修改列的属性*/
alter table student
     -> change shengao tinyint;

五、表管理语句

5.1.查看所有表

show tables;

5.2.查看表结构

desc 表名/视图名;

5.3.查看建表过程

/*查看建表过程*/
show create table 表名;

/*查看建视图过程*/
show create view 视图名;

5.4.查看表详细信息

/*查看所有表详细信息*/
show table status;

/*查看某张表详细信息*/
show table status where name='表名';

/*竖列显示表信息*/
show table status \G;

5.5.删除表

/*删除表*/
drop table 表名;

/*删除视图*/
drop view 视图名;

5.6.为表改名

rename table oldName to newName;

5.7.清空表数据

truncate 表名;
/*相当于删除表，再重建*/