2.mysql创建表类型

1.存储引擎

存储引擎就是表的类型，包括innodb,myisam等

• 查看MySQL支持的存储引擎
  show engines;
• 指定表类型/存储引擎

create table t1(id int)engine=innodb;
create table t2(id int)engine=memory;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=myisam;

2.日期类型

create table student(
	id int,
	name char(6),
	born_year year,
	birth_date date,
	class_time time,
	reg_time datetime
);

insert into student values
(1,'egon',now(),now(),now(),now());

insert into student values
(2,'alex',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12");

3.字符类型

CREATE TABLE t13(NAME CHAR(5));
CREATE TABLE t14(NAME VARCHAR(5));

INSERT INTO t13 VALUES('李杰 '); #'李杰   '
INSERT INTO t14 VALUES('李杰 '); #'李杰 '

4.枚举类型与集合类型

create table consumer(
	id int,
	name char(16),
	sex enum('male','female','other'),
	level enum('vip1','vip2','vip3'),
	hobbies set('play','music','read','run')
);

insert into consumer values (1,'egon','male','vip2','music,read');
insert into consumer values (1,'egon','xxxxx','vip2','music,read');
INSERT INTO consumer VALUES (2,'lcy',1,2,'play');
INSERT INTO consumer VALUES (2,'lcy',1,2,1);

5.约束条件 not null 和default

非空和默认值设置

CREATE TABLE t16(
	id INT,
	NAME CHAR(6),
	sex ENUM('male','female') NOT NULL DEFAULT 'male'
);
INSERT INTO t16(id,NAME) VALUES(1234,'egon');

CREATE TABLE t17(
	id INT(11) UNSIGNED ZEROFILL,
	NAME CHAR(6)
);
INSERT INTO t17(id,NAME) VALUES(12345,'egon');

6.唯一值

• 单列唯一

	-- 方式一
	create table department(
		id int unique,
		name char(10) unique
	);
	-- 方式二：
	create table department(
		id int,
		name char(10)，
		unique(id),
		unique(name)
	);

• 联合唯一
  如果像IP和port这种需要联合起来可以代表一个地址，需要关注两者时需要用到联合唯一，就是说只有当IP和端口号都相同时才会触发非唯一错误

create table services(
	id int,
	ip char(15),
	port int,
	unique(id),
	unique(ip,port)
);

insert into services values
(1,'192.168.11.10',80),
(2,'192.168.11.10',81),
(3,'192.168.11.13',80);

7.主键

primary key
约束：not null unique
存储引擎（innodb）:对于innodb存储引擎来说，一张表内必须有一个主键

• 单列主键

create table t17(
	id int primary key,
	name char(16)
);

insert into t17 values
(1,'egon'),
(2,'alex');

insert into t17 values
(2,'wxx');

insert into t17(name) values
('wxx');

create table t18(
	id int not null unique,
	name char(16)
);

• 复合主键

create table t19(
	ip char(15),
	port int,
	primary key(ip,port)
);


insert into t19 values
('1.1.1.1',80),
('1.1.1.1',81);

8.自增

create table t20(
	id int primary key auto_increment,
	name char(16)
);

insert into t20(name) values
('egon'),
('alex'),
('wxx');



insert into t20(id,name) values
(7,'yuanhao');

insert into t20(name) values
('egon1'),
('egon2'),
('egon3');

9.外键

foreign key 建立表与表之间的关系

9.1建立表关系：

- 先建被关联的表,并且保证被关联的字段唯一

create table dep(
	id int primary key,
	name char(16),
	comment char(50)
);

- 再建立关联的表

create table emp(
	id int primary key,
	name char(10),
	sex enum('male','female'),
	dep_id int,
	foreign key(dep_id) references dep(id) 
	on delete cascade 
	on update cascade
);

9.2 插入数据

#先往被关联表插入记录
insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");

#再往关联表插入记录
INSERT INTO emp VALUES
(1,'egon','male',1),
(2,'alex','male',1),
(3,'wupeiqi','female',2),
(4,'yuanhao','male',3),
(5,'jinximn','male',2);