时间:2021-07-01 10:21:17 帮助过:12人阅读
-- 数据库中实际存储的其实是整型数据!
- <span style="color: #008080;">1</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 插入female</span>
- <span style="color: #008080;">2</span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> test_gender <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">2</span>);
在实际的开发中,自由输入区一般都用text类型,比如新闻正文,博客正文等!
- <span style="color: #008080;">1</span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> news(
- </span><span style="color: #008080;">2</span> news_id <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increment ,
- </span><span style="color: #008080;">3</span> content <span style="color: #0000ff;">text</span>
- <span style="color: #008080;">4</span> );
- <span style="color: #008080;">/*</span><span style="color: #008080;">
- 也叫做集合类型,类似于多选项!
- 如果一个字段的值只能是某个或某几个选项的值,最好使用set类型
- 同enum类型一样,在定义的时候也需要把所有可能的选项都罗列出来:
- </span><span style="color: #008080;">*/</span>
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> zifu(
- hobby </span><span style="color: #0000ff;">set</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sleep</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">eat</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">study</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">php</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">LOL</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">WOW</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
- );
- </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> zifu <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sleep,php,LOL</span><span style="color: #ff0000;">‘</span>);
其实,多选项实际存储的也是整型数据:
- <span style="color: #008080;">1</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 选择sleep 和php 1+8= 9</span>
- <span style="color: #008080;">2</span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> zifu <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">9</span>);
默认情况下,字段都是可以为空的,也就是该属性的缺省值为null
- <span style="color: #008080;">1</span> <span style="color: #008080;">--</span><span style="color: #008080;"> not null 不能为空 unsigned非特殊字符</span>
- <span style="color: #008080;">2</span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> stu(
- </span><span style="color: #008080;">3</span> name <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>) <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">,
- </span><span style="color: #008080;">4</span> age <span style="color: #0000ff;">tinyint</span><span style="color: #000000;"> unsigned
- </span><span style="color: #008080;">5</span> );
自定义默认值属性,也叫做default约束,通常就是配合not null属性一起使用,也就是说某个字段不允许为空,但是如果用户没有给该字段插入数据,就用默认值去填充!
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> user_my(
- id </span><span style="color: #0000ff;">int</span> primay <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increment
- name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">32</span>) <span style="color: #0000ff;">unique</span> <span style="color: #0000ff;">key</span> <span style="color: #0000ff;">default</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">我是没有设置唯一键内容的name</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
- );</span>
- <span style="color: #008080;">--</span><span style="color: #008080;"> 当然,也可以直接插入default关键字,意思就是插入默认值:</span>
- <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> user_my <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">23</span>,<span style="color: #0000ff;">default</span>);
1.设置主键之后就不能添加重复的主键的值了
2.主键会自动增加非空约束
定义主键方法有2:
- <span style="color: #008080;"> 1</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 1.直接在字段的后面进行设置</span>
- <span style="color: #008080;"> 2</span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> stu(
- </span><span style="color: #008080;"> 3</span> id <span style="color: #0000ff;">int</span> <span style="color: #0000ff;">unique</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increment
- </span><span style="color: #008080;"> 4</span> <span style="color: #000000;">);
- </span><span style="color: #008080;"> 5</span>
- <span style="color: #008080;"> 6</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 2.定义完字段后再定义主键</span>
- <span style="color: #008080;"> 7</span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> stu(
- </span><span style="color: #008080;"> 8</span> id <span style="color: #0000ff;">int</span> <span style="color: #0000ff;">unique</span><span style="color: #000000;"> auto_increment,
- </span><span style="color: #008080;"> 9</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(id)
- </span><span style="color: #008080;">10</span> );
注意:
如果某个主键是一个组合主键,就只能使用第二种方式!
- <span style="color: #008080;">1</span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> tea(
- </span><span style="color: #008080;">2</span> tea_name <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
- </span><span style="color: #008080;">3</span> class_id <span style="color: #0000ff;">tinyint</span><span style="color: #000000;"> unsigned,
- </span><span style="color: #008080;">4</span> day_num <span style="color: #0000ff;">tinyint</span><span style="color: #000000;"> unsigned,
- </span><span style="color: #008080;">5</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 定义组合主键</span>
- <span style="color: #008080;">6</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(tea_name,class_id)
- </span><span style="color: #008080;">7</span> );
- <span style="color: #008080;"> 1</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 1.直接在字段后面加unique</span>
- <span style="color: #008080;"> 2</span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> stu(
- </span><span style="color: #008080;"> 3</span> id <span style="color: #0000ff;">int</span> unsigned <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increament,
- </span><span style="color: #008080;"> 4</span> stu_id <span style="color: #0000ff;">int</span> unsigned <span style="color: #0000ff;">unique</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">,
- </span><span style="color: #008080;"> 5</span> tel <span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #0000ff;">unique</span> <span style="color: #0000ff;">key</span>
- <span style="color: #008080;"> 6</span> <span style="color: #000000;">);
- </span><span style="color: #008080;"> 7</span>
- <span style="color: #008080;"> 8</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 2.先定义字段,后设置unique key</span>
- <span style="color: #008080;"> 9</span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> stu(
- </span><span style="color: #008080;">10</span> id <span style="color: #0000ff;">int</span> unsigned <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increament,
- </span><span style="color: #008080;">11</span> stu_id <span style="color: #0000ff;">int</span><span style="color: #000000;"> unsigned ,
- </span><span style="color: #008080;">12</span> tel <span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">11</span><span style="color: #000000;">) ,
- </span><span style="color: #008080;">13</span> <span style="color: #008080;">--</span><span style="color: #008080;"> 定义两个唯一键</span>
- <span style="color: #008080;">14</span> <span style="color: #0000ff;">unique</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(stu_id,tel)
- </span><span style="color: #008080;">15</span> );
MySQL数据库操作语句(补充1)(cmd环境运行)
标签:text mysql数据库 sql var 语句 枚举类 enum 输入 def