模型是现实世界特征的模拟和抽象。在数据库技术中，用数据模型的概念描述数据库的结构和语义，是对现实世界的数据抽象。数据模型是研究数据库技术的核心和基础。
 


 
文章目录
 
1.概念数据模型（CDM）
2.逻辑数据模型（LDM）
3.物理数据模型（PDM）
     
    
   
  
 

 

 

 
1.概念数据模型（CDM）
 
    现实世界主要涉及以下一些概念：
     （1）实体（Entity）
     客观存在并可相互区别的事物称为实体。
     可以是具体的人、事、物或抽象的概念。
     （2）属性（Attribute）
     实体所具有的某一特性称为属性。
     一个实体可以由若干个属性来刻画。
     （3）码（Key）
     唯一标识实体的属性集称为码。
     （4）实体型（Entity Type）
     用实体名及其属性名集合来抽象和刻画同类实体称为
 实体型
     （5）实体集（Entity Set）
     同一类型实体的集合称为实体集
     （6）联系（Relationship）
     现实世界中事物内部以及事物之间的联系在信息世界中反映为实体（型）内部的联系和实体（型）之间的联系。
     实体内部的联系通常是指组成实体的各属性之间的联系
     实体之间的联系通常是指不同实体集之间的联系
     实体之间的联系有一对一、一对多和多对多等多种类型
 
    概念数据模型是现实世界到信息世界的第一层抽象，主要是在高水平和面向业务的角度对信息的一种描述，通常作为业务人员和技术人员之间沟通的桥梁。作为现实世界的概念化结构，这种数据模型使得数据库的设计人员在最初的数据库设计阶段将精力集中在数据之间的联系上，而不用同时关注数据的底层细节（如所用的计算机系统的特性以及数据库管理系统—DBMS的特性）。
 
    概念数据模型主要的贡献在于分析数据之间的联系，它是用户对数据存储的一种高度抽象，反应的是用户的一种业务层面的综合信息需求。
 
    在这个阶段一般会形成整个数据模型或者是软件系统中的实体的概念以及实体之间的联系，为构建逻辑数据模型奠定基础。下图中描述了现实世界和信息世界以及最终转换成计算机世界信息的转换流程。
 

 
 
    设计概念数据模型的主要工具是E-R图，扩展的E-R图。
     概念模型的表示方法最常用的是P.P.Chen于1976年提出的"实体-联系图方法（Entity-Relationship Approach），简称E-R模型"。E-R实体联系图是直观表示概念模型的工具，其中包含了实体、联系、属性三个成分，联系的方法为一对一（1:1）、一对多（1:N）、多对多（M:N）三种方式，联系属于哪种方式取决于客观实际本身。
     E-R模型图，既表示实体，也表示实体之间的联系，是现实世界的抽象，与计算机系统没有关系，是可以被用户理解的数据描述方式。通过E-R模型图可以使用户了解系统设计者对现实世界的抽象是否符合实际情况，从某种程度上说E-R模型图也是用户与系统设计者进行交流的工具，E-R模型图已成为概念模型设计的一个重要设计方法。
 　　
 
 

 
 
2.逻辑数据模型（LDM）
 
数据模型由三部分组成：数据结构、数据操作和数据约束。
 　　(1)数据结构：数据结构主要描述数据的类型、内容、性质、以及数据之间的联系，是整个数据模型的基础，而针对数据的操作和数据之间的约束都是建立在数据结构的基础上的；
 　　(2)数据操作：主要定义了在相应的数据结构上的操作类型和操作方式（数据库中的增删改查等）；
 　　(3)数据约束：数据约束主要用来描述数据库中数据结构之间的语法、词义联系以及彼此之间的相互约束和制约关系（如MySQL中使用外键保证数据之间的数据完整性）
 
逻辑数据模型是对概念数据模型进一步具体化，在概念数据模型定义实体的基础上定义了各个实体的属性，是用户从数据库的角度能够看到的数据的模型，是所使用的数据库管理系统（Database Management System，DBMS）所支持的数据类型（网状数据模型、层次数据模型、关系数据模型）。这种数据模型架起了用户和系统之间的桥梁，既要面向用户，同时也考虑到了所用的DBMS所支持的特性。
 
逻辑数据模型反映了系统分析设计人员针对数据在特定的存储系统（如MySQL）的观点，是对概念数据模型的进一步细化和划分。逻辑数据模型是根据业务之间的规则产生的，是关于业务对象、业务对象数据以及业务对象彼此之间关系的蓝图。
 
逻辑数据模型的内容包括所有的实体、实体的属性、实体之间的关系以及每个实体的主键、实体的外键（用于维护数据完整性）。其主要目标是尽可能详细的描述数据，但是并不涉及这些数据的具体物理实现。逻辑数据模型不仅会最终影响数据库的设计方向，并最终会影响到数据库的性能（如主键设计、外键等都会最终影响数据库的查询性能）。
 
逻辑数据模型是开发物理数据库的完整文档，逻辑数据模型主要采用的是层次模型、网状模型、关系模型，其中最常用的是关系模型，对应的数据库称之为关系型数据库，如MySQL。
 　　常用的结构数据模型是关系模型和面向对象模型，关系模型的理论基础是数学理论，数据的操作通过关系运算实现。在关系模型中用二维表表示实体及实体之间的联系，关系模型的实例称为关系。从数学的观点上看，关系是集合，其元素是元组（记录）。遵循一定的规则后，可以将E-R模型图转换成关系模型。
 

     将E-R模型图转换成关系模型的规则：
 E-R模型图中的主要成分是实体及实体之间的联系，对于实体的转换方式是：
 　　1) 将一个实体转换成一个关系模型。实体的属性为关系模型的属性，实体的标识符为关系模型的关键字，如上图所示的E-R模式中有两个实体：学生、课程，可以分别转换学生模型和课程模型：
     学生模型（学号,姓名,性别,年龄），学号是学生模型的关键字
     课程模型（课程号，课程名，学时数），课程号是课程模型的关键字
     2) 联系转换为关系模型。联系转换成关系模型时，要根据联系方式的不同采用不同的转换方式：
     若联系的方式是一对一的(1:1)，可以在两个实体关系模型中的任意一个关系模型中加入另一个关系模型的关键字和联系类型的属性。
     若联系方式是一对多的(1:N)，则在N端（为多的一端）实体的关系模型中加入1端实体关系模型的关键字和联系类型的属性
     若联系方式是多对多的（M:N），则将联系也转换成关系模型，其属性是互为联系的两个实体的关键字和联系的属性
 
 

 

 
3.物理数据模型（PDM）
 
物理数据模型，又称为物理模型，是概念数据模型和逻辑数据模型在计算机中的具体表示。该模型描述了数据在物理存储介质上的具体组织结构，不但与具体的数据库管理系统相关，同时还与具体的操作系统以及硬件有关，但是很多工作都是由DBMS自动完成的，用户所要做的工作其实就是添加自己的索引等结构即可。
 
物理数据模型是在逻辑数据模型的基础上，综合考虑各种存储条件的限制，进行数据库的设计，从而真正实现数据在数据库中的存放。其主要的工作是根据逻辑数据模型中的实体、属性、联系转换成对应的物理模型中的元素，包括定义所有的表和列，定义外键以维持表之间的联系等，具体例子如下：
 

导读：在看《SQL必知必会(第四版)》看到运用外键建立表关系，联想到工作时建表主键类型的选择问题，查资料的时候遇到了比较简洁的一个文章；
 
1. 基础理论：
 
1.主键和外键的设计原则。 
 a. 主键应尽量分离于业务的。 
 b. 主键应尽量是单列的，以便提高筛选和连接的效率。 
 c. 主键不应该被更新，且不含动态变化的数据。 
 d. 主键应是有计算机自动生成的。
 
2.常见的主键选取方式 
 a.自动增长。 
 b.手动增长字段 
 c.UniqueIdentifier 
 d.COMB Combine 组合类型 
 目前再项目中用到了a,c两种。以下是优缺点 
 使用自增长做主键的优点： 
 1、很小的数据存储空间 
 2、性能最好 
 3、容易记忆 
 使用自增长做主键的缺点： 
 1、如果存在大量的数据，可能会超出自增长的取值范围 
 2、很难（并不是不能）处理分布式存储的数据表，尤其是需要合并表的情况下 
 3、安全性低，因为是有规律的，容易被非法获取数据 
 使用GUID做主键的优点： 
 1、它是独一无二的，出现重复的机会少 
 2、适合大量数据中的插入和更新操作，尤其是在高并发和分布式环境下 
 3、跨服务器数据合并非常方便 
 4、安全性较高 
 使用GUID做主键的缺点： 
 1、存储空间大（16 byte），因此它将会占用更多的磁盘空间 
 2、会降低性能 
 3、很难记忆
 
COMB Combine 组合类型的方法：uuid+时间
 
ID=CAST(CAST(NEWID() AS BINARY(10))   + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)
 
2. SQL 中的约束 
 2.1 Not Null 
 作用：强制字段始终包含值，若字段为null,无法插入或更新记录。
 
/-- 创建表时添加 not  null 约束
create table table_NotNull
(
   ID int identity(1,1) primary key,
   Name varchar(50) not null
)
/--对已经存在是某列添加not null 约束
/--已存在的数据中 Name均有值 
alter table dbo.table_NotNull alter column Name varchar(20) not null
/--已存在的数据中 存在Name为null 的情况。以后无法插入name=null 的数据
alter table dbo.table_NotNull with nocheck add constraint Name check(Name is not null)
 
2.2 unique 
 作用：约束唯一标识数据库-表中的每条数据。
 
create table table_Unique
(
  ID int primary key Identity(1,1) not null,
  Name varchar(50) unique,
  Age int not null
)
alter table table_Unique add unique(Age)
 
2.3 primary key 
 作用：主键，记录唯一标识，主键自动拥有not null及 unique约束。
 
/--定义多个列的primaey key 约束
create Table Table_PrimaryKey
(
   Code varchar(20),//自动约束为not null
   DateInfo datetime,//自动约束为not null
   Name varchar(20) not null,
   constraint pk_Table_PrimaryKey Primary Key(Code,DateInfo) 
)
-- 主键列应申明不包含null 值
ALTER TABLE Table_PrimaryKey ADD PRIMARY KEY (Code )
ALTER TABLE Table_PrimaryKey ADD CONSTRAINT pk_PersonID PRIMARY KEY (Code,DateInfo)
 
2.4 foreign key 
 作用：外键，预防破坏表之间的连接行为，防止非法数据插入外键列。
 
create Table Table_ForeignKey
(
   Id int identity(1,2) primary key,
   Code int,
   DateInfo datetime,
   constraint fk_ForeignKey foreign key(Code) references table_Unique(ID)
) 
--撤销外键约束 定义外键且已命名
ALTER TABLE [dbo].[Table_ForeignKey] drop constraint fk_ForeignKey
 
2.5 Check 
 作用：限制列的取值范围
 
create Table Table_Check
(
   Id int identity(1,2) primary key,
   Age int not null,
   State int,
   constraint ck_Check check(Age>20 and State in (0,1))
) 
--删除同上
 
2.6 DEFAULT 
 作用：设置默认值
 
create Table Table_DEFAULT
(
   Id int identity(1,2) primary key,
   Age int not null,
   State int,
   Name varchar(20) DEFAULT 'Sandnes'
) 
--更改
ALTER TABLE Table_DEFAULT ALTER COLUMN City 
SET DEFAULT 'ss' (drop DEFAULT )
 
参考：1. 优缺点 http://www.yyjjssnn.cn/articles/754.html 
 2. 4中常见方法 http://blog.sina.com.cn/s/blog_5f39af320102wsfs.html 
 3.sql 基础：http://www.runoob.com/sql/sql-default.html

 
文章目录
 
数据类型
数值类型：
整数类型：
浮点型：
   
日期类型：
字符串类型：
枚举类型与集合类型：
  
约束条件
null与default
unique
primary key
auto_increment
修改初始值
   
foreign key
 

 
数据类型
 
数值类型：
 
整数类型：
 
 
 tinyint 默认有符号，[unsigned]代表无符号，超出范围显示最大范围。
 
语法：
 
tinyint[(m)] [unsigned] [zerofill]
 
 int类型无符号的存储宽度4个字节，
 创建表整形类型不指定宽度。指定宽度对存储宽度没用，只是更改显示宽度。显示宽度是指查询时显示的宽度，用 [zerofill] 填充。
 其他所有类型指定宽度都表示存储宽度。
 默认得无符号得显示宽度 是10
 默认得有符号得显示宽度 是11
 
语法：
 
 int[(m)][unsigned][zerofill]
 
浮点型：
 
    浮点类型：float double decimal=dec
    作用：存储薪资、身高、体重、体质参数等。
    
    规则：
  # FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
   	定义：
      单精度浮点数（非准确小数值），
       m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
    精确度：
   		随着小数的增多，精度变得不准确 ****
       
 # DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
      定义：
   		双精度浮点数（非准确小数值），
   		m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
     精确度：
       随着小数的增多，精度比float要高，但也会变得不准确 
        
 # decimal[(m[,d])] [unsigned] [zerofill]
     定义：
          准确的小数值，
          m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
    精确度： 
    	随着小数的增多，精度始终准确

  decaimal能够存储精确值的原因在于其内部按照字符串存储。
 
示例：
 
 1             mysql> create table t1(x float(256,31));
 2             ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.
 
 5             mysql> create table t1(x float(255,30)); #建表成功

 8             mysql> create table t2(x double(255,30)); #建表成功

15             mysql> create table t3(x decimal(65,30)); #建表成功


28             mysql> insert into t1 values(1.1111111111111111111111111111111); #小数点后31个1
29             Query OK, 1 row affected (0.01 sec)
30 

31             mysql> insert into t2 values(1.1111111111111111111111111111111);
32             Query OK, 1 row affected (0.00 sec)
33 
34             mysql> insert into t3 values(1.1111111111111111111111111111111);
35             Query OK, 1 row affected, 1 warning (0.01 sec)
36 
37             mysql> select * from t1; #随着小数的增多，精度开始不准确
38             +----------------------------------+
39             | x                                |
40             +----------------------------------+
41             | 1.111111164093017600000000000000 |
42             +----------------------------------+
43             row in set (0.00 sec)
44 
45             mysql> select * from t2; #精度比float要准确点，但随着小数的增多，同样变得不准确
46             +----------------------------------+
47             | x                                |
48             +----------------------------------+
49             | 1.111111111111111200000000000000 |
50             +----------------------------------+
51             row in set (0.00 sec)
52 
53             mysql> select * from t3; #精度始终准确,d为30，于是只留了30位小数
54             +----------------------------------+
55             | x                                |
56             +----------------------------------+
57             | 1.111111111111111111111111111111 |
58             +----------------------------------+
59             row in set (0.00 sec)

  精度： decimal double float
 
日期类型：
 
    YEAR DATE TIME DATETIME TIMESTAMP
    作用：存储用户注册时间，文章发布时间等
    规则：
        YEAR
            YYYY（1901/2155）
        DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）
        TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）
        DATETIME
            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
        TIMESTAMP
            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
 
示例：
 
19             MariaDB [db1]> create table t11(d date,t time,dt datetime);
20             MariaDB [db1]> desc t11;
21             +-------+----------+------+-----+---------+-------+
22             | Field | Type     | Null | Key | Default | Extra |
23             +-------+----------+------+-----+---------+-------+
24             | d     | date     | YES  |     | NULL    |       |
25             | t     | time     | YES  |     | NULL    |       |
26             | dt    | datetime | YES  |     | NULL    |       |
27             +-------+----------+------+-----+---------+-------+
28 
29             MariaDB [db1]> insert into t11 values(now(),now(),now());
30             MariaDB [db1]> select * from t11;
31             +------------+----------+---------------------+
32             | d          | t        | dt                  |
33             +------------+----------+---------------------+
34             | 2017-07-25 | 16:26:54 | 2017-07-25 16:26:54 |
35             +------------+----------+---------------------+
36 
37             ============timestamp===========
38             MariaDB [db1]> create table t12(time timestamp);
39             MariaDB [db1]> insert into t12 values();
40             MariaDB [db1]> insert into t12 values(null);
41             MariaDB [db1]> select * from t12;
42             +---------------------+
43             | time                |
44             +---------------------+
45             | 2017-07-25 16:29:17 |
46             | 2017-07-25 16:30:01 |
47             +---------------------+
48 
49             ============注意===========
50             1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入
51             2. 插入年份时，尽量使用4位值
52             3. 插入两位年份时，<=69，以20开头，比如50,  结果2050
53                             >=70，以19开头，比如71，结果1971

66             =======================
67             MariaDB [db1]> create table student(
68                 -> id int,
69                 -> name varchar(20),
70                 -> born_year year,
71                 -> birth date,
72                 -> class_time time,
73                 -> reg_time datetime);
74 
75             MariaDB [db1]> insert into student values

78                 -> (3,'wsb',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13");
79 
80             MariaDB [db1]> select * from student;
81             +------+------+-----------+------------+------------+---------------------+
82             | id   | name | born_year | birth      | class_time | reg_time            |
83             +------+------+-----------+------------+------------+---------------------+
86             |    1 | wsb  |      1998 | 1998-01-01 | 13:13:13   | 2017-01-01 13:13:13 |
87             +------+------+-----------+------------+------------+---------------------+
 
datetime与timestamp的区别：
 
DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP空间利用率更高。
DATETIME的默认值为null；TIMESTAMP的字段默认不为空（notnull）,默认值为当前时间
 
一般用datetime就可以了
 
字符串类型：
 
官网：https://dev.mysql.com/doc/refman/5.7/en/char.html
 
#注意：char和varchar括号内的参数指的都是字符的长度

1.char类型：定长，浪费空间，存取速度快
    字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
    存储：
        存储char类型的值时，按指定宽度存，不够右填充空格，
        所以定长检索速度快，定长的宽度去解析。
        
        例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储

    检索：
        在检索或者说查询时，查出的结果显示时，会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式
        （SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）

2.varchar类型：变长，精准，节省空间，存取速度慢
    字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：
    存储：
        varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来，
        但是：varchar类型会在真实数据前加1-2Bytes的前缀，
        表示真实数据的bytes字节数，检索的时候才知道检索多少。
        
        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）

    检索：
        尾部有空格会保存下来，在查询时，正常显示包含空格在内的内容

    　　#其他字符串系列（效率：char>varchar>text）
    　　 TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
   　　  BLOB 系列    TINYBLOB BLOB MEDIUMBLOB LONGBLOB
    　　 BINARY系列 BINARY VARBINARY
 
虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。
 
 
 大部分情况 下 用 char，存完之后 不查得话 就可以用varchar
 
 
 建表得时候: 定长得数据 往前放,变长得数据往后放
 
 
 一张表,char varchar 不要混着用
 
 
分别查看存储得字符数和字节数
 
length：查看字节数
char_length:查看字符数

 1                 mysql> create table t1(x char(5),y varchar(5));
 2                 Query OK, 0 rows affected (0.26 sec)
 3 
 4                 #char存5个字符，而varchar存4个字符
 5                 mysql> insert into t1 values('你瞅啥 ','你瞅啥 ');
 6                 Query OK, 1 row affected (0.05 sec)

12                 mysql> select x,char_length(x),y,char_length(y) from t1;
13                 +-----------+----------------+------------+----------------+
14                 | x         | char_length(x) | y          | char_length(y) |
15                 +-----------+----------------+------------+----------------+
16                 | 你瞅啥    |              3 | 你瞅啥     |              4 |
17                 +-----------+----------------+------------+----------------+
18                 row in set (0.00 sec)


21                 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
22                 Query OK, 0 rows affected (0.00 sec)

25                 mysql> select x,char_length(x),y,char_length(y) from t1;
26                 +-------------+----------------+------------+----------------+
27                 | x           | char_length(x) | y          | char_length(y) |
28                 +-------------+----------------+------------+----------------+
29                 | 你瞅啥      |              5 | 你瞅啥     |              4 |
30                 +-------------+----------------+------------+----------------+
31                 row in set (0.00 sec)

33    #char类型：3个中文字符+2个空格=11Bytes
34    #varchar类型:3个中文字符+1个空格=10Bytes

35                 mysql> select x,length(x),y,length(y) from t1;
36                 +-------------+-----------+------------+-----------+
37                 | x           | length(x) | y          | length(y) |
38                 +-------------+-----------+------------+-----------+
39                 | 你瞅啥      |        11 | 你瞅啥     |        10 |
40                 +-------------+-----------+------------+-----------+
41                 row in set (0.00 sec)
 
虽然 CHAR 和 VARCHAR 的存储方式不太相同,但是对于两个字符串的比较,都只比 较其值，where查询时后面加不加空格都可以查出来。但是用like查询时，必须保证与存储格式一致。
 
枚举类型与集合类型：
 
字段的值只能在给定范围中选择，不在范围内显示空值
 
 
 enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
 
 
 set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）
 
 
约束条件
 
PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充
 
null与default
 
 是否可空，null表示空，非字符串
 not null - 不可空
 null - 可空
 default - 默认值
 
设置默认值后，则无论字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值。
 
unique
 
 设置位唯一不重复
 可以设置复合唯一，单列可以重复，但不能同时重复。
 
设置方法：
 
方法一：
create table department1(
id int unique,
name varchar(20) unique,
comment varchar(100)
);

 
方法二：
create table department(
        id int,
        name char(10),
        unique(id),
        unique(name)
    );
 
 create table service(
        id int ,
        name varchar(20) unique,
        host varchar(15) not null,
        port int not null,
        unique(host,port)  #联合唯一
    );
  
 
primary key
 
 主键，存储引擎innodb一张表必须有主键，不指定会自动检索不为空且唯一的字段创建，检索不到会创建一个隐藏字段。建表时指定主键。
 通常一张表，都应该有一个id字段用来标识记录编号，id通常设置为主键。
 一个表内只能有一个主键primary key
 =not null unique 不为空且唯一
 单列做主键：不为空且唯一；常用。
 多列做主键（复合主键）：多个字段连在一起，当作主键；
 
设置方法：
 
#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

 
#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);
 
#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

 
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);

 
auto_increment
 
 
 约束字段为自动增长，被约束的字段必须同时被key约束
 
 
 不指定id，则自动增长，可以指定id 。
 
 
 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
 
 
 用truncate清空表。增长从1开始，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它，
 
mysql> truncate student;
 Query OK, 0 rows affected (0.01 sec)
 
 
修改初始值
 
#在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;
 
#也可以创建表时指定auto_increment的初始值，
注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;
 
设置起始偏移量和步长
 
#步长：
	auto_increment_increment默认为1
	#起始偏移量
	auto_increment_offset默认1
	

	#设置步长

	set session auto_increment_increment=5; 会话级别，本次连接有效

	set global auto_increment_increment=5; 全局，所有会话重新再登陆才能起作用

	
	#设置起始偏移量
	set global auto_increment_offset=3;

	强调：起始偏移量<=步长，如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略
 
foreign key
 
外键，建立表与表之间的关系。
 
 表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
 语法：foreign key(dep_id) references dep(id)
 最好不要用外键建立硬性关系，以后修改比较麻烦。
 
#先建被关联的表,并且保证被关联的字段唯一
	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 # 被关联的有更新或者删除都会关联，没有逗号
	);

#先往被关联表插入记录
insert into dep values

#再往关联表插入记录
insert into emp values

 
 
学习地址：一天学会 MySQL 数据库      MySQL安装教程      MySQL专栏
 
 
🚀01-03：MySQL数据库-笔记01【数据库概念、数据库安装、终端操作数据库】
🚀04-15：MySQL数据库-笔记02【创建数据库与数据表、数据类型、约束概念与举例】
🚀16-21：MySQL数据库-笔记03【范式（1NF、2NF、3NF）、查询练习题*10道（附解析）】
🚀22-29：MySQL数据库-笔记04【查询练习题*8道（附解析）】
🚀30-54：MySQL数据库-笔记05【查询练习题*25道（附解析）】
🚀55-64：MySQL数据库-笔记06【SQL的4种连接查询、事务】
 
目   录
 
04-如何创建 数据库、数据表？
 
0、MySQL注释的3种写法(-- ...、#...、/*...*/)
 
1、如何在数据库服务器中创建自己的数据库?  （create database 数据库名;）
 
2、选中某一数据库（use 数据库名;）
 
3、查看数据库中的所有数据表（show tables;）
 
4、如何创建一个数据表?（create TABLE xxx...）
 
5、查看数据表是否创建成功？（show tables;）
 
6、查看创建好的数据表的结构（describe 数据表名;）
 
05-INSERT增加数据记录
 
1、查看数据表中的记录（select * from 数据表名;）
 
2、往数据表中添加数据记录（insert xxx）
 
2.1、INSERT方式①【VALUES(值1, 值2, ...)】
 
2.2、INSERT方式②【数据表名(数据表字段1, 字段2, ...)】
 
06-MySQL数据类型与类型选择
 
1、MySQL常用数据类型
 
1.1、数值类
 
1.2、日期和时间类型
 
1.3、字符串类型
 
2、注意
 
3、数据类型如何选择
 
07-数据记录增删改查操作
 
1、插入数据（INSERT）
 
2、查看数据（SELECT）
 
3、删除数据（DELETE）
 
4、修改数据（UPDATE）
 
5、总结
 
08-约束-主键约束primary_key
 
1、MySQL建表约束
 
1.1、主键约束
 
09-约束-主键约束-联合主键
 
1、联合主键
 
10-约束-自增约束-auto_increment
 
1、自增约束
 
11-约束-主键约束-建表后添加与删除
 
1、修改表结构，添加主键约束
 
2、删除主键约束
 
3、使用modify修改字段，添加主键约束
 
12-约束-唯一约束-unique
 
1、添加唯一约束的方式
 
1.1、添加“唯一约束”方式①【add unique(字段名)】
 
1.2、添加“唯一约束”方式②【建表时，添加-unique(name)】
 
1.3、添加“唯一约束”方式③【建表时，添加-name varchar(20) unique】
 
1.4、添加“唯一约束”方式④【建表时，添加-unique(id, name)】
 
1.5、添加“唯一约束”方式⑤【建表时，添加-unique(id, name)】
 
2、删除唯一约束（drop）
 
3、总结
 
4、业务需求（用户注册表---综合实例）
 
13-约束-非空约束-not_null
 
14-约束-默认约束-default
 
15-约束-外键约束-foreign_key
 
1、创建2个表（班级表、学生表）
 
1.1、班级表（主表）（classes）
 
1.2、学生表（副表）（students）
 
2、插入数据
 
2.1、在班级表（主表）中插入数据！
 
2.2、在学生表（副表）中插入数据！
 
3、删除数据
 

04-如何创建 数据库、数据表？
 
0、MySQL注释的3种写法(-- ...、#...、/*...*/)
 
 
 
“-- ”   这种注释后面，要加一个空格！！！
 
 
 
 
1、如何在数据库服务器中创建自己的数据库?  （create database 数据库名;）
 
create database 数据库名;
 
 
 
 
 
2、选中某一数据库（use 数据库名;）
 
use 数据库名;
 
3、查看数据库中的所有数据表（show tables;）
 
show tables;
 
 
 
刚创建的数据库（test）中，没有任何数据表！
 
 
 
 
4、如何创建一个数据表?（create TABLE xxx...）
 
create TABLE pet( #pet 数据表名
    name VARCHAR(20), #name 数据字段，varchar 字符串数据类型
    owner VARCHAR(20),
    specise VARCHAR(20),
    sex CHAR(1), #字符类型
    brith DATE, #日期类型
    death DATE
);
 
 
 
注意事项
 
 
1：var()与varchar()的区别在于var()是定常的,哪怕存储的字符串没有达到"()"中数字的上限,var()依然会占用空格来填充空间.而varchar()则是不定长的,没有达到"()"中的上限则会自动去掉后面的空格；
 
 
2：性别不要用:sex 要用:gender  一个是性 一个是性别；
 
 
3：定义最后一个字段的时候不要加","；
 
 
4：上面的"VAR","VARCHAR","DATE"可以用小写.不过最好用大写来表示区分关键字,若不然也许写到后面你自己都不知道这个词是数据库中的关键字还是你自己自定义的一些数据,同时一定要用英文的标点符号也必须半角输入。 
 
 
5、查看数据表是否创建成功？（show tables;）
 
show tables;
 
6、查看创建好的数据表的结构（describe 数据表名;）
 
describe 数据表名;
 
 
 
 
 
 
 
 
Field：字段的名称；
 
 
Type：字段的类型，int、var、varchar；
 
 
Key：是否是关键字，如可以定义为:  primary key 或者 unique key   ...
 
 
Default：若是该字段没有主动设置值的时候,该字段的默认值。
 
 
05-INSERT增加数据记录
 
1、查看数据表中的记录（select * from 数据表名;）
 
select * from 数据表名;
 
2、往数据表中添加数据记录（insert xxx）
 
2.1、INSERT方式①【VALUES(值1, 值2, ...)】
 
INSERT INTO pet VALUES('名字','主人名','品种','1','2000-01-01',null);
 
 
 
 
 
 
 
 
NULL:代表的是空,表示该字段还没有数据.
 
 
千万不要主动填写'NULL',这代表字段有一个值叫做'null'.
 
 
2.2、INSERT方式②【数据表名(数据表字段1, 字段2, ...)】
 
INSERT INTO pet(name, owner) VALUES ('xx', 'cc');
 
 
 
INSERT INTO pet(name,owner) VALUES ('xx','cc');
 
 
 
  
 
   
 
    

     代表：只在name和owner字段上面插入数据，其他皆为NULL/默认值的数据。
    
 
   
 
  
 
 
 
 
 
 
06-MySQL数据类型与类型选择
 
 
 
菜鸟教程 ：https://www.runoob.com/mysql/mysql-data-types.html
 
 
1、MySQL常用数据类型
 
 
 
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
 
 
1.1、数值类
 
 
 
MySQL支持所有标准SQL数值数据类型。
 
 
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，
 
 
以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
 
 
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
 
 
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
 
 
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。
 
 
下面的表显示了需要的每个整数类型的存储和范围。
 
 
 
 
 
 
create table testType(number TINYINT);

show tables;

describe testType;

SELECT * FROM testType;

INSERT INTO testType VALUES(128);
 
 
 
 
 
 
1.2、日期和时间类型
 
 
 
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
 
 
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
 
 
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
 
 
 
 
 
 
 
 
注意：金钱最好用int/bigint(整数,单位用分,拿出来进行*100换成元)，千万不要直接用浮点,会有精度损失。
 
 
1.3、字符串类型
 
 
 
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
 
 
该节描述了这些类型如何工作以及如何在查询中使用这些类型。
 
 
 
 
 
 
2、注意
 
 
 
char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
 
 
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
 
 
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
 
 
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
 
 
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。
 
 
3、数据类型如何选择
 
 
 
日期：按照 格式 ！
 
 
数值、字符串：按照 大小 ！
 
 
07-数据记录增删改查操作
 
1、插入数据（INSERT）
 
INSERT INTO pet VALUES('kk1','cc1','dog1','1','1998-1-1',null);
INSERT INTO pet VALUES('kk2','cc2','dog2','2','1998-2-2',null);
INSERT INTO pet VALUES('kk3','cc3','dog3','1','1998-3-3','1998-12-2');
INSERT INTO pet VALUES('kk4','cc4','dog4','2','1998-4-4',null);
 
 
 
 
 
2、查看数据（SELECT）
 
select * from pet;
 
 
 
 
 
3、删除数据（DELETE）
 
DELETE FROM tablesName WHRER 条件;

delete from pet where name='名字';
 
 
 
 
 
4、修改数据（UPDATE）
 
UPDATE tableName SET 字段1=值1,字段2=值2 ... WHERE 条件;

update pet set name='旺旺财' where owner='保安李大爷';
 
 
 
 
 
5、总结
 
 
 
数据记录常见操作：
 
 
1、增加（INSERT）   2、删除（DELETE）   3、修改（UPDATE）   4、查询（SELECT）
 
 
08-约束-主键约束primary_key
 
1、MySQL建表约束
 
1.1、主键约束
 
 
 
1、主键约束；2、自增约束；3、外键约束；
 
 
4、唯一约束；5、非空约束；6、默认约束
 
 
 
 
它能够唯一确定一张表中的一条记录，通过给某个字段添加约束，可以使得该字段不重复而且不为空。
 
 
create table user(
    id int PRIMARY KEY,
    name VARCHAR(20)
);
 
 
 
 
 
 
describe user;   ----->   id是不可以为null，且 key的值变为PRI(primary) ！
 
 
INSERT INTO user VALUES (1, '张三');
 
 
 
 
 
 
 
主键不能为空！
 
 
09-约束-主键约束-联合主键
 
1、联合主键
 
CREATE TABLE user2(
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY key(id,name)
);

INSERT INTO user2 VALUES (1,'老王','123456');
 
 
 
联合主键只要所有的字段都不是相同的情况下，可以允许其中的字段重复。
 
 
 
 
10-约束-自增约束-auto_increment
 
1、自增约束
 
CREATE TABLE user3(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
 
INSERT INTO user3(name) VALUES('霸刀宋壹');
INSERT INTO user3(name) VALUES('哒哒子前辈');
INSERT INTO user3(name) VALUES('天帝子前辈');
 
 
 
没有 定义id值，但是自动生成了id。
 
 
 
 
11-约束-主键约束-建表后添加与删除
 
 
 
忘记 给表 添加 主键约束！-->修改表结构，添加主键！
 
 
删除表的主键约束！！！
 
 
CREATE TABLE user4(
    id int,
    name VARCHAR(20)
);
 
 
 
 
 
1、修改表结构，添加主键约束
 
alter table user4 add primary key(id);
 
 
 
 
 
2、删除主键约束
 
alter table user4 drop primary key;
 
 
 
 
 
3、使用modify修改字段，添加主键约束
 
 
 
 
 
alter table user4 modify id int primary key;
 
 
 
 
 
12-约束-唯一约束-unique
 
 
 
约束修饰的字段的值不可以重复！
 
 
create table user5(
    id int,
    name varchar(20)
);
 
1、添加唯一约束的方式
 
1.1、添加“唯一约束”方式①【add unique(字段名)】
 
alter table user5 add unique(name);
 
insert into user5 values(1, '张三');
 
 
 
  
 
 
1.2、添加“唯一约束”方式②【建表时，添加-unique(name)】
 
create table user6(
    id int,
    name varchar(20),
    unique(name)
);
 
 
 
 
 
 
1.3、添加“唯一约束”方式③【建表时，添加-name varchar(20) unique】
 
create table user7(
    id int,
    name varchar(20) unique
);
 
 
 
 
 
1.4、添加“唯一约束”方式④【建表时，添加-unique(id, name)】
 
 
 
unique(id, name)：2个键在一起不重复，就可以 插入数据！
 
 
create table user8(
    id int,
    name varchar(20),
    unique(id, name)
);
 
insert into user8 values(1, '张三');
 
 
 
 
 
 
1.5、添加“唯一约束”方式⑤【建表时，添加-unique(id, name)】
 
alter table user7 modify name varchar(20) unique;
 
2、删除唯一约束（drop）
 
alter table user7 drop index name; /*name 是 字段名*/
 
 
 
 
 
3、总结
 
 
 
1、建表的时候，添加约束；
 
 
2、可以 使用 alter、add
 
 
3、alter、modify
 
 
4、删除（alter、drop）
 
 
4、业务需求（用户注册表---综合实例）
 
CREATE TABLE user_test(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',
    name VARCHAR(20) NOT NULL COMMENT'用户姓名,不能为空',
    phone_number VARCHAR(20) UNIQUE NOT NULL COMMENT'用户手机,不能重复且不能为空'
);
 
 
 
主键约束(primary key)中 包含了 唯一约束.
 场景：业务需求（设计一张用户注册表，用户姓名必须要用手机号来注册，而且手机号和用户名称都不能为空。）
 
 
 
 
 
这样的话就达到了，每一个手机号都只能出现一次，达到了每个手机号只能被注册一次，
 用户姓名可以重复，但是手机号码却不能重复，符合正常的逻辑需求。
 
 
 
 
 
非空约束（NOT NULL）
 
 
 
 
13-约束-非空约束-not_null
 
 
 
修饰的字段，不能为空。NULL
 
 
create table user9(
    id int,
    name varchar(20) not null
);
 
insert into user9 (id) values(1);
 
 
 
 
 
 
14-约束-默认约束-default
 
 
 
当插入字段值的时候，如果没有传值，就会使用默认值。
 
 
create table user10(
    id int,
    name varchar(20),
    age int default 10
);
 
insert into user10 (id, name) values(1, '张三');

insert into user10 values(1, '张三', 19);
 
 
 
 
 
 
15-约束-外键约束-foreign_key
 
 
 
设计到2个表：【父表、子表】（主表、副表）
 
 
副表要参照主表！
 
 
 
 
 
在副表中添加的数据，必须同样存在于主表。
 
 
 
 
 
1、主表（父表）中没有的数据值，在副表（子表）中，是不可以使用的。
 
 
2、主表中的记录被副表引用时，是不可以被删除的。
 
 
1、创建2个表（班级表、学生表）
 
1.1、班级表（主表）（classes）
 
-- 班级表
create table classes(
    id int primary key,
    name varchar(20)
);
 
1.2、学生表（副表）（students）
 
-- 学生表
create table students(
    id int primary key,
    name varchar(20),
    class_id int ,-- 关联班级表id，字段的数据类型需要相同
    foreign key(class_id) references classes(id)-- 绑定：指明class_id来自哪个表
    -- class_id必须来自classes表的id字段
);
 
 
 
 
 
 
2、插入数据
 
2.1、在班级表（主表）中插入数据！
 
-- 在班级表中插入数据！
insert into classes values(1, '1班');
insert into classes values(2, '2班');
insert into classes values(3, '3班');
insert into classes values(4, '4班');
insert into classes values(5, '5班');
insert into classes values(6, '6班');
select * from classes;
 
 
 
 
 
2.2、在学生表（副表）中插入数据！
 
-- 在学生表中插入数据！
insert into students values(1001, '张三', 1);
insert into students values(1002, '张三', 2);
insert into students values(1003, '张三', 3);
insert into students values(1004, '张三', 4);
select * from students;
 
 
 
 
 
3、删除数据
 
 
 
1、主表（父表）中没有的数据值，在副表（子表）中，是不可以使用的。
 
 
2、主表中的记录被副表引用时，是不可以被删除的。
 
 
delete from classes where id=4;