MySQL之DDL&DML语言

一、DDL语言

1.1 定义

数据定义语言，用于库和表的管理。

1.2 库的管理

1.2.1 库的创建

语法：

create database 【if not exists】 库名;

1.2.2 库的修改

rename database 库名 to 新库名;（以前可以）

更改库的字符集：

alter database books character set 要修改的字符集;

1.2.3 库的删除

语法：

drop database [if exists] 库名;

1.3 表的管理

1.3.1 语法：

alter table  表名 add|drop|modify|change|column 列名 【列类型 约束】;

1.3.2 表的创建★

语法：

create table 【if not exists】 表名(

        列名 列的类型【（长度） 约束】,

        列名 列的类型【（长度） 约束】,

        ...

        列名 列的类型【（长度） 约束】）；

1.3.3 表的修改

①修改列名

alter table 表名 change column(可以不加column) 旧列名 新列名 类型;

②修改列的类型或约束

alter table 表名 modify column 列名 新类型 【新约束】;

③添加新列

alter table 表名 add column 列名 类型【first|after 字段名】;

④删除列

alter table 表名 drop column 列名;

⑤修改表名

alter table 表名 rename 【to】 新表名;

1.3.4 表的删除

语法：

drop table 【if exists】 表名;

1.3.5 表的复制

①仅仅复制表的结构

create table 表名 like 要复制的表名;

②复制表的结构+数据

create table 表名

select * from 要复制的表名:

③只复制部分数据

在②的基础上加上where筛选条件。

④仅仅复制某些字段

create table 表名

select 复制的字段

from 要复制的表名

where 0;

1.4 常见的数据类型

1.4.1 数值型

数值型又分为整型和浮点型。

整型特点:

①如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字；

②如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值，如果不够用会用0在做左边填充，但必须搭配zerofill使用。

浮点型：

浮点型又分为定点数（decimal(M,D)）和浮点数（float(M,D)和double(M,D)）。

其特点：

①M:整数部位+小数部位，   D：小数部位，如果超过范围，则报out of range异常，并且插入临界值；

②M和D都可以省略，如果是decimal，则M默认是10，D默认是0，如果是float和double，则会根据插入的数值的精度来决定精度；

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算则考虑使用定点型。

1.4.2 字符型

①较短的文本：char（固定长度）、varchar（可变长度）。

            写法                    M的意思                                         特点                   空间的耗费         效率

     char（M）      最大的字符数，可以省略，默认为1      固定长度的字符      比较耗费           高

    varchar（M）      最大的字符数，不可以省略              可变长度的字符       比较节省           低

②较长的文本：text、blob（较长的二进制数据） 。

③binary和varbinary（保存较短的二进制），Enum类型和Set类型。

1.4.3 日期型

分为：

year：年，date：日期，time：时间，datetime：日期+时间，timestamp：日期+时间。

datetime和timestamp的特点：

                      字节          范围          时区等的影响

datetime             8          1000-9999          不受

timestamp          4          1970-2038          受

1.5 常见约束

1.5.1 含义

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。

1.5.2 分类：六大约束

①NOT NULL：非空，用于保证该字段的值不能为空，比如姓名、学号等。

②DEFAULT：默认，用于保证该字段有默认值，比如性别。

③PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空，比如学号、员工编号等。

④UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号。

⑤CHECK:检查约束【mysql中不支持】，比如说年龄、性别。

⑥FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值，比如学生表的专业编号，员工表的部门编号，员工表的工种编号。

1.5.3 添加约束时机

分为创建表时和修改表时。

1.5.4 约束添加分类

①列级约束——六大约束语法上都支持，但外键约束没有效果。

②表级约束——除了非空、默认，其他的都支持。

1.5.5 创建表时添加约束

①添加列级约束（不包括检查约束和外键约束）

语法：

直接在字段名和类型后面追加约束类型即可。

②添加表级约束

语法：

在各个字段的最下面

【constraint 约束名】 约束类型（字段名）

1.5.6 修改表时添加约束

①添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束;

②添加表级约束 

alter table 表名 add 【constraint 约束名】 约束类型（字段名） 【外键的引用】;

1.5.7 修改表时删除约束

①删除非空、默认约束

alter table 表名 modify column 字段名 字段类型 null/不写;

②删除主键、唯一和外键

 alter table 表名 drop 约束类型 key_name;

1.6 标识列

①含义

又称为自增长列，可以不用手动的插入值，系统提供默认的序列值。

②创建表时设置标识列

特点：

标识列不一定要和主键搭配，但要求是一个key；、

一个表至多有一个标识列；

标识列的类型只能是数值型；

标识列可以通过set auto_increment_increment=所需的数值;设置步长，也可以通过手动插入值，设置起始值。

 ③修改表时设置标识列

alter table 表名 modify column 字段名 类型 约束名 auto_increment;

④修改表时删除标识列 

alter table 表名 modify column 字段名 类型 ;

1.7 通用写法

drop database  if exsits 旧库名;

create database 库名;

drop table  if exsits 旧表名;

create table 表名;

二、DML语言

2.1 定义

数据操作语言，用于插入、修改、删除数据。

2.2 插入语句

2.2.1 方式一：经典的插入

语法：

insert into 表名（列名，...） values (值1,...)；

①插入的值的类型要与列的类型的一致或兼容 

示例：insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)

         values(13,'唐艺昕','女','1990-4-23','18923919238',null,2);

②不可以为null的列必须插入值，可以为null的列如何插入值？

方式一：

示例：insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)

         values(13,'唐艺昕','女','1990-4-23','18923919238',null,2);

方式二：

示例：insert into beauty(id,name,sex,borndate,phone,boyfriend_id)

          values(13,'唐艺昕','女','1990-4-23','18923919238',2);

③列的顺序可以调换。

④列和值的个数必须一致。

⑤可以省略列明，默认所有列，而且列的顺序和表中列的顺序一致。

2.2.2 方式二

语法：

insert into 表名

set 列名=值,列名=值,...;

示例：insert into beauty

          set id=19,name='朱宇星'，phone='15628910281';

2.2.3 两种方式PK 

①方式一支持插入多行，方式二不支持；

②方式一支持子查询，方式二不支持，所以一般来说，我们用方式一较多。

2.3 修改语句

2.3.1 修改单表的记录★

语法：

update 表名

set 列名=新值,列名=新值...

where 筛选条件; 

2.3.2 修改多表的记录【补充】

语法：

sql92语法：

update 表1 别名,表2 别名

set 列=值,...

where 连接条件

and 筛选条件;

sql99语法：

update 表1 别名

inner|left|right join 表2 别名

on 连接条件

set 列=值,...

where 筛选条件;

2.4 删除语句

2.4.1 方式一：delete

语法：

①单表的删除★

delete from 表名 where 筛选条件;

②多表的删除【补充】 

sql92语法：

delete 表1的别名,表2的别名【limit 条目数】

from 表1 别名,表2 别名

where 连接条件

and 筛选条件;

sql99语法：

delete 表1的别名,表2的别名

from 表1 别名

inner|left|right join 表2 别名 on 连接条件

where 筛选条件

2.4.2 方式二：truncate（也叫清空数据）

语法：

truncate tables 表名;

注意：不允许加where语句。

2.4.3 delete PK truncate【面试题★】

①delete可以加where条件，truncate不能加；

②truncate删除，效率高一丢丢；

③假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始；

④truncate删除没有返回值（没有提示几行成功了），delete删除有返回值（则会提示几行成功）；

⑤truncate删除不能回滚，delete删除可以回滚。