数据库基础篇（三）—— SQL之数据操纵、定义

写在前面：今天我们来学习DML数据操纵语言和DDL数据定义语言。DML包括插入、修改、删除；DDL包括表和库的管理。一般从事数据分析工作，只有查的权限，没有增删改及建库建表的权限。通常我们需要将业务数据从数仓或者正式库，迁移到测试库或本地库。具体迁移方法上一节有介绍。迁移到测试库后，就可以增删改操作。

DML语言的学习

1、 插入 insert

1）语法

# 方式一
insert into 表名(字段名，...)
values(值1，...);
# 方式二
insert into 表名
set 字段名1=值2，字段名2=值2 ...

2）两种方式区别

方式一使用最多。它支持插入多行；支持嵌入子查询。方式二不支持。

insert into info(id,name,age)
values(1，'cherich',18),(2，'cherich2',NULL),
(3，'cherich3',20);

insert into info(id,name,age)
select id,name,age from info;

3）特点

①字段类型和值类型一致或兼容，而且一一对应

②可以为空的字段，可以不用插入值，或用null填充

③不可以为空的字段，必须插入值

④字段个数和值的个数必须一致

⑤字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

2、修改 update

1）语法

# 修改单表语法
update 表名 
set 字段=新值,字段=新值
【where 条件】
# 修改多表语法：
update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件

2）栗子

# 更新单个表
update info 
set name='Cherich',age=24
where id = 1;

3、删除 delete

1）语法

#方式一：delete语句 
# 单表的删除：★
delete from 表名 【where 筛选条件】

# 多表的删除：
delete 别名1，别名2
from 表1 别名1，表2 别名2
where 连接条件
and 筛选条件;
#方式二：
truncate table 表名

2）栗子

# 单表删除
delete from info where age = 18;
# 多表删除
delete from info a inner join order b on a.id=b.id
where a.name='cherich';
# 清空整个表
truncate table info;

3）两种方式区别

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

② truncate的效率高一点

③truncate 删除带自增长的列的表后，如果再插入数据，数据从1开；delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始

④truncate删除不能回滚，delete删除可以回滚

DDL语言的学习

1、库和表的管理

1）库的管理：

# 创建库
create database 库名
# 删除库
drop database 库名

2）表的管理：

#1.创建表
CREATE TABLE IF NOT EXISTS info(
  stuId INT,
  stuName VARCHAR(20),
  gender CHAR,
  bornDate DATETIME
 
 # 查看info表结构 
 DESC info;

#2.修改表 alter
语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段类型】;
#①修改字段名
ALTER TABLE info CHANGE  COLUMN sex gender CHAR;
#②修改表名
ALTER TABLE info RENAME [TO]  studentinfo;
#③修改字段类型和列级约束
ALTER TABLE info MODIFY COLUMN borndate DATE ;
#④添加字段
ALTER TABLE info ADD COLUMN email VARCHAR(20) first;
#⑤删除字段
ALTER TABLE info DROP COLUMN email;

#3.删除表
DROP TABLE [IF EXISTS] info;

# 4.表的复制
# ①仅仅复制表的结构
CREATE TABLE copy LIKE info;
# ②复制表结构和数据
CREATE TABLE copy1
SELEC * FROM info; 
# ③复制部分数据
CREATE TABLE copy2
SELEC stuId ,stuName FROM info
WHERE gender ='女'; 
# ④仅仅复制部分结构
CREATE TABLE copy3
SELEC stuId ,stuName FROM info
WHERE 0;
#(或者WHERE 1=2)

注意：对于表和库的管理，语句理解就好。工作中最方便的是直接在Navicat中直接操作。那么，这里重要的是复制表，它的需求通常是直接复制表的结构或数据，或者部分数据及部分结构。不需要重新再创建表结构，导数据。直接用SQL，效率会很高，且不容易出错。

2、常见类型

上面在创建表时，涉及到数据类型。它和Python程序语言类似，也有自己的数据类型。都是在存储数据时，要对数据类型进行限制，保证插入数据时的准确性。分类分别是数值型、字符型、日期型。图中标记部分为常用。

3、常见约束

上面在创建表时涉及到约束，它是工作中非常重要的。我们从一个问题场景来看，下面是一个真实的面试题。我们第一反应是”报错！”，但是面试官想听的是背后报错原因及解决方案。下面，带着问题来学习约束。

约束是一种限制，用于限制表中的数据结构，为了保证表中的数据的准确和可靠性、一致性。比如：创建用户表时，为了保证每一个用户唯一性，就需要进行约束。添加约束的时机分别是创建表、修改表时。SQL中有五大常用约束。如下：

①NOT NULL 非空约束，保证该字段的值不为空。如：用户ID

②DEFAULT 默认约束，字段如果不插入数据也有默认值。如：性别

③UNIQUE 唯一约束，可以为空。如：商品类别

④PRIMARY KEY 主键，保证字段唯一性、非空。如：ID，员工编号

⑤FOREIGN KEY 外键，用于限制两个表之间的关系，用于保证该字段的值必须来自主表的关联列的值。如：用户表里有个外键是order_id, order_id是order的主键。下图是约束在Navicat中设置的对应位置。

最后回答下上面的问题：原因是int类型的id字段最大数据量支持到4294967295，如果超过则会报错。解决方案：①检查id字段是否是主键②将id的类型转换为 bigint。

好，今天学习到这里。本节内容相对上一节要简单些。虽然简单，但每个知识点都是工作中常用的。这篇文章主要是SQL的增删改和表和库的管理。明天继续学习SQL的事务及视图。一起加油！

【信息由网络或者个人提供，如有涉及版权请联系COOY资源网邮箱处理】