数据库的四种列类型和字段属性

一.数值类型

（1）tinyint（十分小的数据 1字节的大小）

（2）smallint （较小的数据 2字节）

（3）mediumint （中等大小的字节 3字节）

（4）int （标准的整数    4字节）常用的

（5） bigint（较大的数据 8字节）

（6）float （浮点数 4字节）

（7）double （浮点数 8字节）

（8）decimal （字符串形式的浮点数 一般用于经济计算 ）

二.字符串类型

（1）char （字符产固定大小的 0~255）

（2）varchar（可变字符串 0~65535）常用的 对应Java中的string

（3）tinytext （微型文本2^8-1 ）

(4) text (文本串 2^16-1) 保存大文本

三.时间和日期

（1）date YYYY-MM-DD (日期格式)

（2）time HH:MM:SS(时间格式)

（3）datetime YYYY-MM-DD HH：MM：SS 最常用的时间格式

（4）timestamp 时间戳 从1970.1.1到现在的毫秒数 也较为常用

（5）year（年份表示）

四.null 没有值，未知

---

                                                               字段属性

unsigned （无符号的整数 声名该列不能为负数）

zerofill（0填充  不足的位数用0填充  1    001）

自增 auto_increment（自动在上一条记录的基础上+1（默认） 通常用来设计唯一的主键  必须是整数  可自定义设计主键的自增起始值和步长）

非空 NULL  NOT NULL（假设不给复制就会报错）

默认 default '默认值'（设置默认值）

列约束

1.主键约束（ primary key）：不能为null，不能重复，一个表只能有一个主键  

唯一约束

1.unique key 不重复 可以为null，一张表上可以有多个唯一约束

非空约束

1. not null   有此属性不能为空 null会降低查询小率

默认约束

1.

外键约束

1.一对多（多对一）。列如部门和员工，员工和部门

2.一对 一    列如：任何身份证

3.多对多    列如：老师和学生 

模拟一对一关系

用一张表的外键去指向另一张表的主键，此时外键也是主键

#模拟丈夫和妻子

create table wife(

         wid int primary key,

         wname varchar(20)

)engine innodb charset utf8;

create table husband(

         hid int primary key,

         hname varchar(20),

         constraint fk_husband_wife foreign key(hid) references wife(wid)

)engine innodb charset utf8;

insert into wife

values

(1,'妻子1');

insert into husband

values

(1,'丈夫1');

insert into husband

values

(1,'丈夫2');

模拟多对多关系

如何实现多对多关系：创建一个关系表

#模拟老师和学生

create table teacher(

         tid int primary key,

         tname varchar(20)

)engine innodb charset utf8;

create table stu(

         sid int primary key,

         sname varchar(20)

)engine innodb charset utf8;

insert into teacher

values

(1,'老师1'),(2,'老师2'),(3,'老师3');

insert into stu

values

(1,'学生1'),(2,'学生2'),(3,'学生3'),(4,'学生4'),(5,'学生5');

实现：老师1带了学生123，老师2带了学生345，老师3带了所有学生

创建关系表：

create table r_stu_teacher(

         rid int primary key,

         tid int,

         sid int,

         constraint fk_r_teacher foreign key(tid) references teacher(tid),

         constraint fk_r_stu foreign key(sid) references stu(sid)

)engine innodb charset utf8;

insert into r_stu_teacher

values

(1,1,1),(2,1,2),(3,1,3),

(4,2,3),(5,2,4),(6,2,5),

(7,3,1),(8,3,2),(9,3,3),(10,3,4),(11,3,5);

思考：查询老师2带的学生姓名(可以利用子查询)

思路：

1.先在老师表中查询老师2的编号

select tid from teacher where tname = '老师2';

2.在关系表中查询tid对应的sid

select sid from r_stu_teacher where

tid = (select tid from teacher where tname = '老师2');

3.在学生表中查询对应的学生姓名

select sname from stu where

sid in (select sid from r_stu_teacher where

tid = (select tid from teacher where tname = '老师2'));

子查询在表很多时查询效率较低

在开发中一般使用逻辑外键，不使用物理外键。

多表查询

方式一：使用子查询（略）

方式二：使用笛卡尔积

语法：select * from 表1,表2...

子查询在表很多时查询效率较低

在开发中一般使用逻辑外键，不使用物理外键。

select * from teacher,r_stu_teacher,stu;

观察结果表和原表的关系

老师表3行2列

关系表11行3列

学生表5行2列

查询结果：165行7列

对于笛卡尔积结果：列数是原表列数之和，行数是原表行数之积

为了保证数据的完整性，会产生大量的冗余数据

去除冗余数据

select * from teacher,r_stu_teacher, stu

where

teacher.tid = r_stu_teacher.tid

and

stu.sid = r_stu_teacher.sid;

给表名起别名简化书写

select * from teacher t,r_stu_teacher r, stu s

where

t.tid = r.tid

and

s.sid = r.sid;

课堂练习：查询老师1带的学生姓名和学生学号

select sid,sname from teacher t,r_stu_teacher r, stu s

where

t.tid = r.tid

and

s.sid = r.sid

and

tname = "老师1";

#报错了，因为两张表中有同名字段，解决方案使用表名.列名解决

select s.sid,sname from teacher t,r_stu_teacher r, stu s

where

t.tid = r.tid

and

s.sid = r.sid

and

tname = "老师1";

课堂练习：查询老师3带的学生姓名和老师编号

select t.tid,s.sname from teacher t,r_stu_teacher r, stu s

where

t.tid = r.tid

and

s.sid = r.sid

and

tname = "老师3";

案例：查询研发部的所有员工姓名，部门名称，部门编号

insert into dept

values

(20,'财务部','榆林'),(30,'运维部','延安');

+-----+--------+------+

| did | dname  | dloc |

+-----+--------+------+

|  10 | 研发部 | 西安 |

|  20 | 财务部 | 榆林 |

|  30 | 运维部 | 延安 |

+-----+--------+------+

insert into emp

values

(2,'李四',10),(3,'王五',20),(4,'赵六',30),(5,'整齐',30);

+-----+-------+------+

| eid | ename | did  |

+-----+-------+------+

|   1 | 张三  |   10 |

|   2 | 李四  |   10 |

|   3 | 王五  |   20 |

|   4 | 赵六  |   30 |

|   5 | 整齐  |   30 |

+-----+-------+------+

思路：

1.先使用笛卡尔积

select * from dept,emp;

2.去掉冗余数据

select * from dept d, emp e where d.did = e.did;

3.添加条件

select d.did,dname,ename from dept d, emp e where d.did = e.did;