MySQL建表详细介绍

建表进程

建表的进程便是声明列的进程

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  |

列称号	列类型	默许值	是否主键
id	int unsigned	PRI
Username	Varchar(20)
gender	char(1) tinyint/enum(‘男’,’女’)
weight	tinyint unsigned
birth	date
salary	decimal(8,2)
lastLogin	datetime
intro	varchar(1500)

这张表不行好，能够优化

分析：这张表除了username/intro列之外，每一列都是定长的
我们无妨让其所有列都定长，能够极大进步查询速度

列称号	列类型	默许值	是否主键
id	int unsigned	PRI
Username	char(20)
gender	char(1) tinyint
weight	tinyint unsigned
birth	date
salary	decimal(8,2)
lastLogin	int unsigned
intro	varchar(1500)

Username char(10) 是会构成空间的糟蹋，可是进步了速度（值得）
intro char(1500) 却糟蹋的太多了，另一方面，人的简介，一旦注册完，该的频率也不高。
我们能够把intro列独自拿出来，放到另一张表里。

列称号	列类型	默许值	是否主键
id	int unsigned	PRI
intro	varchar(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; // 只能查到这三列

2. 简化杂乱的查询

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

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;

3. 视图能不能更新和删去，增加？

假如视图的每一行是与物理表一一对应的，则能够
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