三、MySQL基础系列笔记之SQL DDL语句
DDL是对数据库内部的对象进行创建、删除、修改等操作的语言。
DML与DDL的区别在于，DML是操作数据表内部的数据，并不涉及表的定义、结构的修改。
连接数据库
语法
mysql -u用户名 -p密码 -h主机名 -P端口号
案例
mysql -uroot -p123456
1、创建数据库
语法
CREATE DATABASE 数据名;
案例：创建demo数据
<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">CREATE</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">DATABASE</span> demo;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">1</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">row</span> affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.00</span> sec)</span>
命令执行完后，提示的一行信息解释：
'Query OK'表示命令执行成功；
'1 row affected'表示数据库操作中1行受到影响；
'0.00 sec'表示执行该语句用于多长时间。
查看所有数据库
语法
show databases;
选择数据库
语法
use 数据库名;
案例
use demo;
2、删除数据库
语法
drop database 数据库名;
案例
drop database demo;
3、创建数据表
语法
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">create</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> [<span style="box-sizing: border-box;color: rgb(219, 45, 32)">if</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">not</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">exists</span>] 表名(</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> 字段名1 字段类型 [约束条件] [<span style="box-sizing: border-box;color: rgb(219, 45, 32)">comment</span> <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'字段说明'</span>],</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> 字段名2 字段类型 [约束条件] [<span style="box-sizing: border-box;color: rgb(219, 45, 32)">comment</span> <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'字段说明'</span>],</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> ...</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">) <span style="box-sizing: border-box;color: rgb(219, 45, 32)">engine</span>=table_type;</span>
案例：创建emp表
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">create</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">if</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">not</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">exists</span> emp(</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> name <span style="box-sizing: border-box;color: rgb(56, 138, 163)">varchar</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">20</span>) <span style="box-sizing: border-box;color: rgb(219, 45, 32)">comment</span> <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'姓名'</span>,</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> sal <span style="box-sizing: border-box;color: rgb(56, 138, 163)">decimal</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">10</span>,<span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span>) <span style="box-sizing: border-box;color: rgb(219, 45, 32)">comment</span> <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'薪水'</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">) <span style="box-sizing: border-box;color: rgb(219, 45, 32)">engine</span>=<span style="box-sizing: border-box;color: rgb(219, 45, 32)">innodb</span>;</span>
查看表结构
语法
desc 表名;
案例
<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">desc</span> emp;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-------+---------------+------+-----+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Field</span> | Type | <span style="box-sizing: border-box;color: rgb(163, 78, 143)">Null</span> | <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Key</span> | <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Default</span> | Extra |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-------+---------------+------+-----+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| name | <span style="box-sizing: border-box;color: rgb(56, 138, 163)">varchar</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">20</span>) | YES | | <span style="box-sizing: border-box;color: rgb(163, 78, 143)">NULL</span> | |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| sal | <span style="box-sizing: border-box;color: rgb(56, 138, 163)">decimal</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">10</span>,<span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span>) | YES | | <span style="box-sizing: border-box;color: rgb(163, 78, 143)">NULL</span> | |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-------+---------------+------+-----+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span> rows <span style="box-sizing: border-box;color: rgb(219, 45, 32)">in</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">set</span> (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">desc</span> emp <span style="box-sizing: border-box;color: rgb(1, 160, 228)">\G</span>;</span>
4、删除表
语法
drop table 表表;
案例
drop table emp;
5、修改表
5.1、修改表类型-modify
modify主要用于修改表中字段的数据长度、数据类型及字段的约束条件。
语法
alter table 表名 modify 字段名 字段类型 [first | alter 字段名];
案例
<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">desc</span> emp;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-------+---------------+------+-----+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Field</span> | Type | <span style="box-sizing: border-box;color: rgb(163, 78, 143)">Null</span> | <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Key</span> | <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Default</span> | Extra |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-------+---------------+------+-----+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| name | <span style="box-sizing: border-box;color: rgb(56, 138, 163)">varchar</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">20</span>) | YES | | <span style="box-sizing: border-box;color: rgb(163, 78, 143)">NULL</span> | |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| sal | <span style="box-sizing: border-box;color: rgb(56, 138, 163)">decimal</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">10</span>,<span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span>) | YES | | <span style="box-sizing: border-box;color: rgb(163, 78, 143)">NULL</span> | |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+-------+---------------+------+-----+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span> rows <span style="box-sizing: border-box;color: rgb(219, 45, 32)">in</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">set</span> (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 案例一：修改sal类型为int</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">modify</span> sal <span style="box-sizing: border-box;color: rgb(56, 138, 163)">int</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.11</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 案例二：修改name类型长度为50且非空</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">modify</span> name <span style="box-sizing: border-box;color: rgb(56, 138, 163)">varchar</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">50</span>) <span style="box-sizing: border-box;color: rgb(219, 45, 32)">not</span> <span style="box-sizing: border-box;color: rgb(163, 78, 143)">null</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.12</span> sec)</span>
5.2、增加表字段-add
语法
alter table 表名 add 字段名 字段类型 [first | alter 字段名];
案例
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># emp表中增加age字段</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">add</span> age <span style="box-sizing: border-box;color: rgb(56, 138, 163)">tinyint</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">not</span> <span style="box-sizing: border-box;color: rgb(163, 78, 143)">null</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">default</span> <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">comment</span> <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'年龄'</span>;</span>
5.3、删除表字段-drop
语法
alter table 表名 drop 字段名;
案例
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 删除emp中的age字段</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">drop</span> age;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.13</span> sec)</span>
5.4、字段改名-change
语法
alter table 表名 change 旧字段名 新字段名 字段类型 [first | alter 字段名];
案例
<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">change</span> name new_name <span style="box-sizing: border-box;color: rgb(56, 138, 163)">varchar</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">20</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.04</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">change</span> sal salary <span style="box-sizing: border-box;color: rgb(56, 138, 163)">decimal</span>(<span style="box-sizing: border-box;color: rgb(163, 78, 143)">10</span>,<span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.04</span> sec)</span>
MySQL modify与change的区别：
modify只能更改字段属性,如 name varchar(50)改为name char(40)
change可以更改字段名与字段类型，如name varchar(5)改为name int
5.5、更改表名-rename
语法
alter table 旧表名 rename [to] 新表名;
案例
mysql> alter table emp rename to emp1;
命令总结
<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 1、连接数据库</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql -uroot -p</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 2、创建数据库</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">create</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">database</span> 表名;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 3、选择数据库</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">use</span> 数据库名;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 4、删除数据库</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">drop</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">database</span> 数据库名;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 5、创建表</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">create</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> 表名(</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"> 字段名 字段类型 [约束条件]</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 6、查看表结构</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">desc</span> 表名;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 7、删除表</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">drop</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> 表名;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 8、修改表类型</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> 表名 <span style="box-sizing: border-box;color: rgb(219, 45, 32)">modify</span> 字段名 字段类型;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 9、增加字段</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> 表名 <span style="box-sizing: border-box;color: rgb(219, 45, 32)">add</span> 字段名 字段类型;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 10、删除表字段</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> 表名 <span style="box-sizing: border-box;color: rgb(219, 45, 32)">drop</span> 字段名;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 11、修改字段名</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> 表名 <span style="box-sizing: border-box;color: rgb(219, 45, 32)">change</span> 旧字段名 新字段名 字段类型;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 12、修改表名</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">alter</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">table</span> 旧表名 <span style="box-sizing: border-box;color: rgb(219, 45, 32)">rename</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">to</span> 新表名;</span>
我是小白，期待和优秀的你一起同行!
小白
2020年10月09日
请登录后再评论