likes
comments
collection
share

MySQL建表详细介绍

作者站长头像
站长
· 阅读数 21

建表过程

建表的过程就是声明列的过程

create table t1 (
sn int,
name varchar(20)
);

列选什么类型的列？
列给什么样的属性？

建表语句

create table 表名 (
列1 列类型[列属性 默认值],
列2 列类型[列属性 默认值],
...
列n 列类型[列属性 默认值],
) 
engine = 存储引擎
charset = 字符集

列类型

大概有三种类型: 数值型： 整型、浮点型、定点型 字符串类型： char、varchar、text 日期时间类型： date、time、datetime

整型列

字节，英文Byte，简写B，一般称作“大B”，以跟“小b”（位）区别。 一个字节可以存1个ASCII码（就是纯英文字符数字符号之类）或者半个汉字。 一个字节可以存8位二进制数，所谓1B=8b就是这意思。 运营商高呼：俺的宽带100M，其实是100Mb，还要除以8才变成大家熟悉的MB。

int 1 4个字节 一个字节有8个位

00000000 00000000 00000000 00000001 11111111 11111111 11111111 11111111 2^32-1

  • bigint 8字节
  • int 4字节
  • mediumint 3字节
  • smallint 2字节
  • tinyint 1字节，默认带正负号
create table t2 (
num tinyint
);

insert into t2 values(255); // query ok,但其值是127
insert into t2 values(127); // query ok
insert into t2 values(-255); // query ok,但其值是-128
insert into t2 values(-128); // query ok

证明：tinyint 默认带正负号

但是如果我们存储年龄，不需要负数，不想浪费一半的存储空间？
答：见下面unsigned

整型列的属性

unsigned 无符号的，修饰列，列的值从0开始，不为负

alter table t2 add unum tinyint unsigned;
insert into t2 values(3,255); // query ok
insert into t2 values(4,-1); // query ok,但其值是0

zerofill 适合用于学号，编码等，固定宽度的数字，可以用0填充至固定宽度

  • 学号-》1 =》0001

  • 学号-》123 =》0123

  • 思路：zerofill填充至多宽？M

  • 注意：zerofill默认设置列是unsigned, M参数必须配合zerofill使用

alter table t2 add sn tinyint(5) zerofill;
insert into t2 values (4,4,3);
insert into t2 values (4,4,33);
insert into t2 values (4,4,333);

浮点列与定点列

  • float(M,D); M是精度，总位数， D是标度，小数点后面的位数
  • double与float区别在范围上
  • decimal
create table t3 (
salary float(5,2)
);

insert into t3 values(9999); // query ok,1警告,但其值是999.99
insert into t3 values(999.99);

create table t4 (
f float(9,2),
d decimal(9,2)
);


insert into t4 values (1234567.23,1234567.23);

+------------+------------+
| f          | d          |
+------------+------------+
| 1234567.25 | 1234567.23 |
+------------+------------+

float/double: 有精度损失
decimal: 定点型，更精确

字符型列

  • char
  • varchar
  • text/blob
  • enum检举型，是定义好，值就在某几个枚举范围内,不在枚举范围内可以插入，但其值是空字符串
char(10) // 最多能存10个字符
如果给其一个字符，但其占用10个字符的宽度

varchar(10) // 最多也是能存10个字符
如果给其一个字符，但是其占【字符宽度+（1至2）】个字节

create table t5 (
n1 char(10),
n2 varchar(10)
);

insert into t5 values(' hello ',' hello ');

// 取出
select concat('!',n1,'!'),concat('!',n2,'!') from t5;
+--------------------+--------------------+
| concat('!',n1,'!') | concat('!',n2,'!') |
+--------------------+--------------------+
| ! hello!           | ! hello !          |
+--------------------+--------------------+

char型，如果不够M个字符(注意是字符，不是字节)，
内部用空格补齐，取出时再把右侧空格删掉
这意味着：如果右侧本身有空格，将会丢失。

create table t7 (
gender enum('men','women')
);

insert into t7 values ('men');
insert into t7 values ('women');
insert into t7 values ('yao');
  • Blob是二进制类型，用来存储图像，音频等二进制信息。
  • 意义：2进制，0-255都有可能出现
  • Blob在于防止因为字符集的问题，导致信息丢失。
  • 比如一张图片中有0xFF字节，这个在ascii字符集认为非法，在入库的时候，被过滤掉了。

日期时间类型

Year年(1字节) [1901,2155]

Date日期 1998-12-23 ====>范围：1000/01/01，9999/12/31

datetime时期时间 1998-12-31 13:45:43 ===>范围：1000/01/01 00:00:00，9999/12/31 23:59:59

时间戳：是1970-01-01 00:00:00到当前的秒数

create table t8 (
yq year,
dt date,
tm time,
dttm datetime
);

insert into t8 (yq) values (1901);

insert into t8 (dt) values ('1990-11-12');

insert into t8 (tm) values ('16:20:23');

insert into t8 (dttm) values ('1990-11-12 16:20:23');

列的默认值

  1. NULL查询不便
  2. NULL的索引效果不高
  3. 所以实用中，避免列的值为NULL，如何避免：not null default xx
create table t9 (
id int not null default 0,
name char(10) not null default ''
);

insert into t9 values (1,'lisi');
insert into t9(id) values (2);

主键与自增

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

// 声明方式1
create table t10 (
id int primary key,
name char(2)
);

// 声明方式2
create table t11 (
id int,
name char(2),
primary key(id)
);

insert into t11 values (1,'lisi');// 再插入一次


// 一张表只能有一列为zuto_increment,且此列必须加索引(index,key)
create table t12 (
id int auto_increment,
name char(2),
key id(id)
);

create table t13 (
id int primary key auto_increment
);

综合建表案例

| id    |  用户名  |   性别   | 体重（KG） |   生日  |   工资    | 上次登录   | 个人简介 |
| id    | Username |  gender | weight    |   birth |  salary  |  lastLogin |  intro  |
列名称列类型默认值是否主键
idint unsignedPRI
UsernameVarchar(20)
genderchar(1) tinyint/enum('男','女')
weighttinyint unsigned
birthdate
salarydecimal(8,2)
lastLogindatetime
introvarchar(1500)

这张表不够好，可以优化

分析：这张表除了username/intro列之外，每一列都是定长的 我们不妨让其所有列都定长，可以极大提高查询速度

列名称列类型默认值是否主键
idint unsignedPRI
Usernamechar(20)
genderchar(1) tinyint
weighttinyint unsigned
birthdate
salarydecimal(8,2)
lastLoginint unsigned
introvarchar(1500)

Username char(10) 是会造成空间的浪费，但是提高了速度（值得） intro char(1500) 却浪费的太多了，另一方面，人的简介，一旦注册完，该的频率也不高。 我们可以把intro列单独拿出来，放到另一张表里。

列名称列类型默认值是否主键
idint unsignedPRI
introvarchar(1500)

在开发中，会员的信息优化往往是把频繁用到的信息，优先考虑效率，存储到一张表中 不常用的信息和比较占据空间的信息，优先考虑空间占用，存储到辅表中。

定长与变长分离 常用与不常用分离

create table user (
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,
salary decimal(8,2) not null default '000000.00',
lastLogin int unsigned not null default 0
)

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

rename table infomation to intro

列的增删改

  • alter table 表名 add 列名 列类型 列属性...默认在表的最后
  • alter table 表名 add 列名 列类型 列属性...after 列名(将会出现在列名后)
  • alter table 表名 drop column 列名
  • alter table 表名 change 列名 把改列名变为其他的名称 列属性
  • alter table 表名 modify 列名 新属性
alter table user add height tinyint unsigned not null default 0; // 增

alter table user drop column height; // 删

alter table user add height tinyint unsigned after weight; // 放在某列之后

alter table user change height shengao smallint; // 修改

alter table user modify height tinyint;

视图 view

视图的概念及建立视图 view: 又被称为虚拟表，view是sql语句的查询结果

select goods_id,goods_name,market_price-shop_price as sheng from goods;

||
\/

create view vgoods as select goods_id,goods_name,(market_price - shop_price) as sheng from goods;

||
\/

select * from vgoods;  //结果等于第一行最上面sql语句的效果

有什么好处?

  1. 权限控制时可以用 比如某几个列，允许用户查询，其他列不允许 可以通过视图，开发其中一列或几列，起到权限控制的作用
create view v2goods as select goods_id,goods_name,shop_price from goods;

select * from v2goods; // 只能查到这三列
  1. 简化复杂的查询

查询每个栏目下商品的平均价格，并按平均价格排序，查出前三高的

select cat_id,avg(shop_price) as pj from goods group by cat_id;

create view v3goods as select cat_id,avg(shop_price) as pj from goods group by cat_id;

select * from v3 order by pj desc limit 0,3;
  1. 视图能不能更新和删除，添加？

如果视图的每一行是与物理表一一对应的，则可以 view的行是由物理表多行经过计算得到的结果，view是不可更新的。

视图的 algorithm

视图放哪儿了？ 存储的都是语句

对于简单查询形成的view，再对view查询时，如where，order等等 可以把建视图的语句+查询视图的语句===合并成===》查物理表的语句。 这种视图算法叫merge(合并)

也有可能视图语句本身比较复杂，很难再和查询视图的语句合并，mysql可以先 执行视图的创建语句，把结果集形成内存中的临时表，然后再去查临时表，temptable

create algorithm=merge view v7 as select goods_id,goods_name from goods; // 一一对应

create algorithm=temptable view v8 as select goods_id,goods_name from goods; // 视图语句比较复杂

create view v9 as select goods_id, goods_name from goods; // 交给系统去判断

存储引擎的概念

  • goods.frm 表结构
  • goods.MYD 表数据
  • goods.MYI 索引文件
  • Myisam: 批量插入速度快, 不支持事务,锁表
  • Innodb: 批量插入相对较慢,支持事务,锁行

字符集，校对集与乱码问题

文字本来的字符集和展示的字符集不一致=》导致乱码

三步走，彻底破解乱码问题：

  1. html: meta-> charset="utf-8"

  2. mysql: create table () charset utf8;

  3. query之前： set names utf8

校对集：排序规则

索引概念

索引是数据的目录，能款速定位行数据的位置。

索引提高了查询的速度，但是却降低了增删改的速度，并非加的越多越好。

一般在查询频繁的列上架，而且在重复度低列上加效果更好。

  • key : 普通索引
  • unique key: 唯一索引
  • primary key: 主键索引
  • fulltext: 全文索引, 在中文条件下，几乎无效。要分词+索引才有效，要引用第三方解决方案才行，如sphinx

索引长度：建索引时，可以只索引列的前一部分的内容，比如，前10个字符。 如：key emial(email(10))

多列索引，就是把2列或多列的值，看成一个整体，然后建立索引。

冗余索引：就是在某个列上，可能存在过个索引。 比如：xm(xing, ming) m(ming)

create table t15 (
    id int,
	name char(10),
	email char(20),
	primary key(id),
	key name(name),
	unique key email(email)
)

# 多列索引
create table t16 (
	xing char(2),
	ming char(10),
	key xm(xing, ming)
)

show index from t16  \G

# 解释当前搜索语句
explain select * from t16 where xing = '朱' and ming='元璋';
explain select * from t16 where xing = '朱';
explain select * from t16 where ming='元璋';

# 冗余索引
create table t17(
	xing char(2),
	ming char(10),
	key xm(xing, ming),
	key m(ming)
)
  • 查看索引：show index form 表名或show create table 表名
  • 删除索引：alter table 表名 drop index 索引名或drop index 索引名 on 表名
  • 添加索引：alter table 表名 add [index/unique] 索引名(列名)
  • 添加主键索引：alter table 表名 add primary key(列名称)
  • 删除主键索引：alter table 表名 drop primary key
转载自:https://juejin.cn/post/7161037642807640071
评论
请登录