SQL补充2：数据库的增操作

数据库的增操作

数据库的增操作主要涉及数据库的增加、数据表的增加、表记录增加以及表字段增加等：

• 数据库的增加非常简单，就是新创建一个数据库；
• 表记录的增加指的就是新增表的数据行，可以是在已有表的基础上增加记录，也可以是将查询结果保存为新的表；
• 字段的增加是指在原有表的基础上新增一些字段。

新建数据库

CREATE DATABASE <database_name>;

新建表

CREATE TABLE table_name_ (column_name column_type);

在原有表的基础上增加记录

INSERT INTO stu_info( iname,department) VALUES
("十九","管理系"),
("小北","生物系");

将查询返回的结果插入到新表中

尝试将之前的商品表goods_orders中支付方式为"1,2,3"的记录插入到新表orders_sub(表结构与goods_orders表一致)中

-- 新建orders_sub表，并且表结构与goods_orders表一致
CREATE TABLE orders_sub LIKE goods_orders;

-- 将查询结果插入到数据表orders_sub中
INSERT INTO orders_sub
SELECT * FROM goods_orders
WHERE Pay_Type IN (1,2,3);

将查询结果直接生成一张新表

将学生信息表stu_info和学生成绩表stu_score合并之后的结果生成一张新表student

CREATE TABLE student AS
SELECT t1.* , t2.Excel,t2.Tableau,t2.MySQL
FROM stu_info AS t1
LEFT JOIN stu_score AS t2
ON t1.id = t2.id;

增加表字段

在上述表student中增加一个新字段Python，并设置为整型，默认值为0

ALTER TABLE student ADD COLUMN Python INT DEFAULT 0

数据库的改操作

数据库的改操作主要是指:

• 修改表中错误记录
• 修改字段类型
• 修改表名称、字段名称
• …

修改表名称

将学生表student的名称修改为stu_summary

ALTER TABLE student RENAME TO stu_summary;

修改字段名

查看学生表stu_summary所有字段类型,再将学生表stu_summary中gender改成sex

ALTER TABLE stu_summary CHANGE gender sex varchar(10);

修改字段类型

将学生表stu_summary 年龄字段age的字段类型修改为VARCHAR(10)

-- 查看学生表stu_summary所有字段类型
DESC stu_summary;

-- 将学生表stu_summary 年龄字段age的字段类型修改为VARCHAR(10)
ALTER TABLE stu_summary MODIFY COLUMN age varchar(10);
-- 也可以使用CHANGE关键词
-- ALTER TABLE stu_summary CHANGE age age varchar(10);

修改数据表中的某个记录

将学生表student中张勇的邮箱地址改为[email protected]

SELECT * FROM student;

UPDATE student SET email = "[email protected]"
WHERE iname = "张勇";

如果学生表中有多个名字叫“张勇”的学生，那么所有满足条件的记录都会被修改

数据库的删操作 (慎用！！！ )

数据库的删操作主要包含数据库的删除、数据表的删除、表记录删除、字段删除三种类型:

• 数据库的删除就是直接删掉整个数据库；
• 表记录的删除是指按照某些条件删除数据表中的记录，或者直接清空数据表的所有记录；
• 字段的删除与字段的增加恰好相反，就是根据实际情况将表中某些字段删掉。

数据库的删除

DROP DATABASE <database_name>;

在实际工作中，大部分数据分析师是没有权限对整个数据库进行删除的，公司进行权限管理也是为了数据的安全，防止有人删库跑路，给公司带来经济损失。

数据表的删除

DROP TABLE <table_name_>;

按条件删除表记录

删除学生表student中，名为“十九”和“小北”的记录

DELETE FROM student
WHERE iname IN ("十九","小北");

如果出现报错Error Code: 1175 ,这是Workbench的安全设置导致的，可以通过降低安全等级解决

set sql_safe_updates =0;
SELECT * FROM student; # 查看删除后的数据

删除表字段

删除学生表student中的email字段

ALTER TABLE student DROP email;

清空数据表

对于数据表的清空操作，这里会涉及到两个关键词：DELETE关键词 和 TRUNCATE 关键词。这两个关键词都可以清空数据表，但是两者之间还是有一些差异：

• DELETE不会清空自增变量的记忆，也就是再次更新数据时，自增变量的序号不是从1开始的
• TRUNCATE可以根本性删除表记录，新增数据时，自增变量的序号从1开始
• 在运行速度方面，TRUNCATE要比 DELETE快

数据准备

# 新建数据表
CREATE TABLE user_info(
    id INT AUTO_INCREMENT PRIMARY KEY,
    iname VARCHAR(10),
    gender TINYINT,
    age TINYINT
);

# 手工插入记录
INSERT INTO user_info(iname,gender,age) VALUES
('张三',1,22),
('李四',1,27),
('王二',0,25),
('丁一',0,32),
('赵五',0,28);

SELECT * FROM user_info;

尝试用DELECT 清空数据表，并增加三条记录

# 使用DELETE关键词清空数据表
DELETE FROM user_info;

# 插入记录
INSERT INTO user_info(iname,gender,age) VALUES
('张三',1,22),
('李四',1,27),
('王二',0,25);

# 查看数据表
SELECT * FROM user_info;

尝试用TRUNCATE 清空数据表，并增加三条记录

# 使用TRUNCATE关键词清空数据表
TRUNCATE TABLE user_info;

# 插入一条记录
INSERT INTO user_info(iname,gender,age) VALUES
('张三',1,22),
('李四',1,27),
('王二',0,25);

# 查看数据表
SELECT * FROM user_info;

CATE 清空数据表，并增加三条记录

# 使用TRUNCATE关键词清空数据表
TRUNCATE TABLE user_info;

# 插入一条记录
INSERT INTO user_info(iname,gender,age) VALUES
('张三',1,22),
('李四',1,27),
('王二',0,25);

# 查看数据表
SELECT * FROM user_info;