1.创建数据库
|
1 |
<span class="pln">create database </span><span class="pun">数据库名称;</span> |
2.查看已经存在的数据库
|
1 |
<span class="pln">show databases</span><span class="pun">;</span> |
3.查看某个已经创建的数据库信息
|
1 |
<span class="pln">show create database </span><span class="pun">数据库名称;</span> |
4.修改数据库的编码
|
1 |
<span class="pln">alter database </span><span class="pun">数据库名称</span> <span class="kwd">default</span><span class="pln"> character </span><span class="kwd">set</span> <span class="pun">编码方式;</span> |
5.删除数据库
|
1 |
<span class="pln">drop database </span><span class="pun">数据库名称;</span> |
6.创建数据表
|
1 2 3 4 5 6 7 |
<span class="pln">create table </span><span class="pun">表名</span> <span class="pun">(</span> <span class="pun">字段名</span><span class="lit">1</span><span class="pun">,数据类型[完整性约束条件],</span> <span class="pun">字段名</span><span class="lit">2</span><span class="pun">,数据类型[完整性约束条件],</span> <span class="pun">………..</span> <span class="pun">字段名</span><span class="pln">n</span><span class="pun">,数据类型[完整性约束条件],</span> <span class="pun">)；</span> |
7.查看数据表
|
1 2 3 |
<span class="pun">查看建表语句：</span><span class="pln">show create table </span><span class="pun">表名;</span> <span class="pun">查看表结构：</span><span class="pln">describe </span><span class="pun">表名;</span> <span class="pun">查看当前数据库中所有的表：</span><span class="pln">show tables</span><span class="pun">;</span> |
8.修改表名
|
1 |
<span class="pln">alter table </span><span class="pun">旧表名</span><span class="pln"> rename </span><span class="pun">新表名;</span> |
9.修改字段名
|
1 |
<span class="pln">alter table </span><span class="pun">表名</span><span class="pln">change </span><span class="pun">旧字段名</span> <span class="pun">新字段名</span> <span class="pun">新数据类型;</span> |
10.修改字段的数据类型
|
1 |
<span class="pln">alter table </span><span class="pun">表名</span><span class="pln"> modify </span><span class="pun">字段名</span> <span class="pun">数据类型;</span> |
11.添加字段
|
1 |
<span class="pln">alter table </span><span class="pun">表名</span><span class="pln"> add </span><span class="pun">新字段名</span> <span class="pun">数据类型;</span> |
12.删除字段
|
1 |
<span class="pln">alter table </span><span class="pun">表名</span><span class="pln"> drop </span><span class="pun">字段名;</span> |
13.修改字段的排列位置
|
1 |
<span class="pln">alter table </span><span class="pun">表名</span><span class="pln"> modify </span><span class="pun">字段名</span><span class="lit">1</span> <span class="pun">数据类型</span><span class="pln"> first</span><span class="pun">|</span><span class="pln">after </span><span class="pun">字段名</span><span class="lit">2</span><span class="pun">;</span> |
14.删除数据表
|
1 |
<span class="pln">drop table </span><span class="pun">表名;</span> |
| 约束条件 | 说明 |
| primary key | 主键约束，用于唯一标识对应的记录 |
| foreign key | 外键约束 |
| not null | 非空约束 |
| unique | 唯一性约束 |
| default | 默认值约束，用于设置字段的默认值 |
15.单字段主键
|
1 |
<span class="pun">字段名</span> <span class="pun">数据类型</span><span class="pln"> primary key</span><span class="pun">;</span> |
16.多字段主键
|
1 |
<span class="pln">primary key</span><span class="pun">(字段名</span><span class="lit">1</span><span class="pun">，字段名</span><span class="lit">2</span><span class="pun">，….字段名</span><span class="pln">n</span><span class="pun">);</span> |
17.非空约束
|
1 |
<span class="pun">字段名</span> <span class="pun">数据类型</span> <span class="kwd">not</span> <span class="kwd">null</span><span class="pun">;</span> |
18.唯一约束
|
1 |
<span class="pun">字段名</span> <span class="pun">数据类型</span><span class="pln"> unique</span><span class="pun">;</span> |
19.默认约束
|
1 |
<span class="pun">字段名</span> <span class="pun">数据类型</span> <span class="kwd">default</span> <span class="pun">默认值;</span> |
20.设置表的字段值自动增加
|
1 |
<span class="pun">字段名</span> <span class="pun">数据类型</span> <span class="kwd">auto</span><span class="pln"> _increment</span><span class="pun">;</span> |
21.创建索引
|
1 2 3 4 5 6 7 8 |
<span class="pln">create table </span><span class="pun">表名</span> <span class="pun">(</span> <span class="pun">字段名</span><span class="lit">1</span> <span class="pun">数据类型</span> <span class="pun">[完整性约束条件],</span> <span class="pun">字段名</span><span class="lit">1</span> <span class="pun">数据类型</span> <span class="pun">[完整性约束条件],</span> <span class="pun">.......</span> <span class="pun">字段名</span><span class="pln">n </span><span class="pun">数据类型</span> <span class="pun">[完整性约束条件],</span> <span class="pun">[</span><span class="pln">unique</span><span class="pun">]|</span><span class="pln">fulltext</span><span class="pun">|</span><span class="pln">spatial</span><span class="pun">]</span><span class="pln"> index</span><span class="pun">|</span><span class="pln">key </span><span class="pun">[别名](字段名</span><span class="lit">1</span> <span class="pun">[(长度)]</span> <span class="pun">[</span><span class="pln">asc</span><span class="pun">|</span><span class="pln">desc</span><span class="pun">])</span> <span class="pun">);</span> |
22.使用create index语句在已经存在的表上创建索引
|
1 |
<span class="pln">create </span><span class="pun">[</span><span class="pln">unique</span><span class="pun">]|</span><span class="pln">fulltext</span><span class="pun">|</span><span class="pln">spatial</span><span class="pun">]</span><span class="pln"> index </span><span class="pun">索引名</span><span class="pln"> on </span><span class="pun">表名</span> <span class="pun">(字段名</span> <span class="pun">[长度]</span> <span class="pun">[</span><span class="pln">ASC</span><span class="pun">|</span><span class="pln">DESC</span><span class="pun">]);</span> |
23.alter table语句在已经存在的表上创建索引
|
1 |
<span class="pln">alter table </span><span class="pun">表名</span><span class="pln"> add </span><span class="pun">[</span><span class="pln">unique</span><span class="pun">]|</span><span class="pln">fulltext</span><span class="pun">|</span><span class="pln">spatial</span><span class="pun">]</span><span class="pln"> index </span><span class="pun">索引名(字段名</span> <span class="pun">[长度]</span> <span class="pun">[</span><span class="pln">ASC</span><span class="pun">|</span><span class="pln">DESC</span><span class="pun">]);</span> |
24.删除索引
|
1 2 |
<span class="pln">alter table </span><span class="pun">表名</span><span class="pln"> drop index </span><span class="pun">索引名;</span><span class="pln"> drop index </span><span class="pun">索引名</span><span class="pln"> on </span><span class="pun">表名;</span> |
25.添加数据（insert语句中指定所有字段名）
|
1 |
<span class="pln">insert </span><span class="kwd">into</span> <span class="pun">表名(字段名</span><span class="lit">1</span><span class="pun">,字段名</span><span class="lit">2</span><span class="pun">,…….)</span><span class="pln"> values </span><span class="pun">(值</span><span class="lit">1</span><span class="pun">,值</span><span class="lit">2</span><span class="pun">);</span> |
26.使用select语句查看student表中的数据
|
1 |
<span class="kwd">select</span> <span class="pun">*</span> <span class="kwd">from</span> <span class="pun">表名;</span> |
27.insert语句中不指定字段名
|
1 |
<span class="pln">insert </span><span class="kwd">into</span> <span class="pun">表名</span><span class="pln"> values</span><span class="pun">(值</span><span class="lit">1</span><span class="pun">，值</span><span class="lit">2</span><span class="pun">，……);</span> |
28.为表的指定字段添加数据
|
1 |
<span class="pln">insert </span><span class="kwd">into</span> <span class="pun">表名(字段</span><span class="lit">1</span><span class="pun">,字段</span><span class="lit">2</span><span class="pun">,……)</span><span class="pln"> values</span><span class="pun">(值</span><span class="lit">1</span><span class="pun">,值</span><span class="lit">2</span><span class="pun">,……);</span> |
29.insert语句为表中指定字段或者全部字段添加数据
|
1 |
<span class="pln">insert </span><span class="kwd">into</span> <span class="pun">表名</span> <span class="kwd">set</span> <span class="pun">字段名</span><span class="lit">1</span><span class="pun">=值</span><span class="lit">1</span><span class="pun">,字段名</span><span class="lit">2</span><span class="pun">=值</span><span class="lit">2</span><span class="pun">,字段名</span><span class="pln">n</span><span class="pun">=值</span><span class="pln">n</span><span class="pun">;</span> |
30.同时添加多条记录
|
1 2 3 4 |
<span class="pln">insert </span><span class="kwd">into</span> <span class="pun">表名(字段名</span><span class="lit">1</span><span class="pun">,字段名</span><span class="lit">2</span><span class="pun">,……)</span><span class="pln"> values </span><span class="pun">(值</span><span class="lit">1</span><span class="pun">,值</span><span class="lit">2</span><span class="pun">,…),</span> <span class="pun">(值</span><span class="lit">1</span><span class="pun">,值</span><span class="lit">2</span><span class="pun">,…),</span> <span class="pun">(值</span><span class="lit">1</span><span class="pun">,值</span><span class="lit">2</span><span class="pun">,…);</span> |
31.update语句更新表中的数据
|
1 2 |
<span class="pln">update </span><span class="pun">表名</span> <span class="kwd">set</span> <span class="pun">字段名</span><span class="lit">1</span><span class="pun">=值</span><span class="lit">1</span><span class="pun">,字段名</span><span class="lit">2</span><span class="pun">=值</span><span class="lit">2</span><span class="pun">,……</span> <span class="pun">[</span><span class="kwd">where</span> <span class="pun">条件表达式];</span> |
32.select查看数据表中的数据
|
1 |
<span class="kwd">select</span> <span class="pun">*</span> <span class="kwd">from</span> <span class="pun">表名</span> <span class="kwd">where</span> <span class="pun">条件表达式；</span> |
33.删除数据
|
1 |
<span class="kwd">delete</span> <span class="kwd">from</span> <span class="pun">表名</span> <span class="pun">[</span><span class="kwd">where</span> <span class="pun">条件表达式];</span> |
常见问题FAQ
- 免费下载或者VIP会员专享资源能否直接商用？
- 本站所有资源版权均属于原作者所有，这里所提供资源均只能用于参考学习用，请勿直接商用。若由于商用引起版权纠纷，一切责任均由使用者承担。更多说明请参考 VIP介绍。
- 提示下载完但解压或打开不了？
- 找不到素材资源介绍文章里的示例图片？
- 欧资源网
