1.Mysql数据库——初识创建表

女爷i 2023-06-18 14:57 52阅读 0赞

## Mysql数据库——创建表 ##

## 一、使用命令行创建表 ##

**->>> 建表语句:**

create table  表名 (

列名1  列类型【完整性约束】,

列名2  列类型【完整性约束】,

列名3  列类型【完整性约束】,

......

)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70][]**红色为常用数据类型**

**1.数值型**

<table> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">Mysql数据类型</td> 
 <td style="text-align:center;vertical-align:middle;">含义</td> 
 <td style="text-align:center;vertical-align:middle;width:494px;">说明</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">tinyint(m)</td> 
 <td style="text-align:center;vertical-align:middle;">非常小的整数</td> 
 <td style="text-align:left;vertical-align:middle;width:494px;">1个字节&nbsp; &nbsp;范围（-128~127）</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">smallint(m)</td> 
 <td style="text-align:center;vertical-align:middle;">较小的整数</td> 
 <td style="text-align:left;vertical-align:middle;width:494px;">2个字节&nbsp; 范围（-32768~32767）</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">mediumint(m)</td> 
 <td style="text-align:center;vertical-align:middle;">中等大小的整数</td> 
 <td style="text-align:left;vertical-align:middle;width:494px;">3个字节&nbsp; 范围（-8388608~8388607）</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">int(m)</td> 
 <td style="text-align:center;vertical-align:middle;">标准整数</td> 
 <td style="text-align:left;vertical-align:middle;width:494px;">4个字节&nbsp; 范围（-2147483648~2147483647）</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">bigint(m)</td> 
 <td style="text-align:center;vertical-align:middle;">大整数</td> 
 <td style="text-align:left;vertical-align:middle;width:494px;">8个字节&nbsp; 范围（-9223372036854775808~9223372036854775807）</td> 
 </tr> 
 </tbody> 
</table>

**2.小数型**

<table> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">Mysql数据类型</td> 
 <td style="text-align:center;vertical-align:middle;">含义</td> 
 <td style="text-align:center;vertical-align:middle;width:494px;">说明</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">float(m,d)</td> 
 <td style="text-align:center;vertical-align:middle;">单精度浮点型【近似值】</td> 
 <td style="text-align:left;vertical-align:middle;width:494px;">8位精度，（4字节），m表示总长度，d表示小数点后面的长度</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">double(m,d)</td> 
 <td style="text-align:center;vertical-align:middle;">双精度浮点型【近似值】</td> 
 <td style="text-align:left;vertical-align:middle;width:494px;">16位精度，(8字节)，m表示总长度，d表示小数点后面的长度</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;">decimal(m,d)</td> 
 <td style="text-align:center;vertical-align:middle;">存储为字符串的浮点型【精确值】</td> 
 <td style="text-align:left;vertical-align:middle;width:494px;">参数m&lt;65,d&lt;30,m表示总长度，d表示小数点后面的长度【常用于存储货币数据，比如价格、工资、账户余额等】</td> 
 </tr> 
 </tbody> 
</table>

**3.字符串型**

<table> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:120px;">Mysql数据类型</td> 
 <td style="text-align:center;vertical-align:middle;width:98px;">含义</td> 
 <td style="text-align:center;vertical-align:middle;width:202px;">描述</td> 
 <td style="text-align:center;vertical-align:middle;width:419px;">备注</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:120px;">char(n)</td> 
 <td style="text-align:center;vertical-align:middle;width:98px;">固定长度</td> 
 <td style="text-align:center;vertical-align:middle;width:202px;">n为0~255之间的一个整数</td> 
 <td colspan="1" style="text-align:center;vertical-align:middle;width:419px;">都是用来保存mysql中较短的字符串</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:120px;">varchar(n)</td> 
 <td style="text-align:center;vertical-align:middle;width:98px;">可变长度</td> 
 <td style="text-align:center;vertical-align:middle;width:202px;">n为0~65536之间的一个整数</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:120px;">text</td> 
 <td style="text-align:center;vertical-align:middle;width:98px;">可变长度</td> 
 <td style="text-align:center;vertical-align:middle;width:202px;">最多65535个字符</td> 
 <td colspan="1" style="text-align:center;vertical-align:middle;width:419px;">存储长文本信息</td> 
 </tr> 
 </tbody> 
</table>

**4.日期与时间型**

<table style="width:538px;"> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:204px;">Mysql数据类型</td> 
 <td style="text-align:center;vertical-align:middle;width:331px;">含义</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:204px;">date</td> 
 <td style="text-align:center;vertical-align:middle;width:331px;">3字节，日期，格式：2019-01-01</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:204px;">time</td> 
 <td style="text-align:center;vertical-align:middle;width:331px;">3字节，时间，格式：08:00:00</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:204px;">datetime</td> 
 <td style="text-align:center;vertical-align:middle;width:331px;">8字节，日期时间，格式：2019-01-01&nbsp;08:00:00</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:204px;">timestamp</td> 
 <td style="text-align:center;vertical-align:middle;width:331px;">4字节,自动存储记录修改时间</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:204px;">year</td> 
 <td style="text-align:center;vertical-align:middle;width:331px;">1字节，年份，格式：2019</td> 
 </tr> 
 </tbody> 
</table>

**5.常见的完整性约束**

<table style="width:618px;"> 
 <tbody> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:223px;">约束</td> 
 <td style="text-align:center;vertical-align:middle;width:393px;">描述</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:223px;">primary key</td> 
 <td style="text-align:center;vertical-align:middle;width:393px;">主键约束：可以唯一标识一个元组【确定哪列是主键】</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:223px;">foreign key</td> 
 <td style="text-align:center;vertical-align:middle;width:393px;">外键约束：是与之联系的某表的主键</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:223px;">unique</td> 
 <td style="text-align:center;vertical-align:middle;width:393px;">唯一性约束：标识该属性的值是唯一的</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:223px;">not null</td> 
 <td style="text-align:center;vertical-align:middle;width:393px;">非空值约束：标识该属性不能为空</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:223px;">auto_increment</td> 
 <td style="text-align:center;vertical-align:middle;width:393px;">创建标识列：标识该属性的值自动增加</td> 
 </tr> 
 <tr> 
 <td style="text-align:center;vertical-align:middle;width:223px;">default</td> 
 <td style="text-align:center;vertical-align:middle;width:393px;">默认值约束：为该属性设置默认值</td> 
 </tr> 
 </tbody> 
</table>

**示例一：**

create table fruits2(
    id  int not null auto_increment,
    name varchar(255),
    price float,
    nums int,
    primary key(id));
    
    ->> not null:表示id字段不能为空
    ->> auto_increment:表示id字段为自增长类型
    ->>  primary key(id):表示id为主键

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2][]

**示例二：**

create table movies(name varchar(255), 
    director varchar(255), 
    area varchar(255), 
    movie_type varchar(255), 
    actors varchar(255),
    summary text);

![20191204104733384.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 3][]

### 二、使用navicat创建表 ###

1.打开navicat软件，进入到自己的数据库。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 5][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 8][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 9][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191203110401905.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191204103359432.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191204103456421.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[20191204104733384.png]: https://img-blog.csdnimg.cn/20191204104733384.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2019120410482198.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2019120411211093.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191204112445305.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20191204112553257.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20191204112652712.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20191204112805144.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20191204112907605.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70