2.1.3 常用的SQL语法（上篇）_轻松学大数据挖掘：算法、场景与数据产品-QQ阅读男生武侠网

书名：轻松学大数据挖掘：算法、场景与数据产品
作者名：汪榕
本章字数：1301字
更新时间：2020-08-28 01:52:16

2.1.3 常用的SQL语法（上篇）

本节围绕数据库经典的四个字：增、删、改和查进行介绍。下面主要介绍在做数据清洗和Java Web底层业务表管理过程中常用的操作。

导入数据以后，在test数据库中有一张默认表input，它共有6列，列名称分别为id、mid、sex、age、degree和create_ymd，相应的数据如图2-7所示。

图2-7 input表的前6行数据

为了进行接下来的测试，创建一张同结构的备份表input_base供学习时使用。代码如下。

      CREATE TABLE IF NOT EXISTS input_base(
       id bigint(20) NOT NULL AUTO_INCREMENT ,
       mid BIGINT(20) COMMENT ’用户id',
       sex varchar(50) COMMENT ’性别’,
       age int(10) COMMENT ’年龄’,
       degree varchar(50) COMMENT ’学位’,
       create_ymd varchar(50) ,
       PRIMARY KEY (id),
       INDEX mid_index(mid)
      );

注：这里以id为自增主键，mid为索引，而主键和索引基本都是标配，除了方便管理，还能增加提升效率。

1．增

增，顾名思义就是插入数据，可以细分为全表插入数据、具体列插入数据，插入数据源可以为第三方表，也可以是简单的初始化语句。

1）从input查询数据并将数据插入到input_base中

（1）插入所有列（可以省略写列名的操作）

      insert into input_base select ＊ from input

（2）插入部分列（未插入的列，值为NULL）

      insert into input_base (id, mid, sex) select id, mid, sex from input

2）从初始化语句插入到input_base中

（1）插入所有列（可以省略写列名操作）

      INSERT  INTO  input_base    VALUES  ('0', '120', ' 男 ', '45', ' 本科
  ', '2016-12-19');
      INSERT  INTO  input_base    VALUES  ('0', '130', ' 女 ', '35', ' 初中
  ', '2016-12-19');
      INSERT  INTO  input_base   VALUES  ('0', '140', ' 男 ', '20', ' 博士后
  ', '2016-12-19');

（2）插入部分列（未插入的列，值为NULL）

      INSERT INTO input_base (id, MID, sex)    VALUES ('0', '128485', ’男’);
      INSERT INTO input_base (id, MID, age)    VALUES ('0', '128495', '35');
      INSERT INTO input_base (id, MID, degree)  VALUES ('0', '128505', ’博士
  后’);

2．删

删，也是围绕数据而言的，可以细分为DROP、TRUNCATE和DELETE，具体的理解如下。

· 相同点

➢ 它们都能删除表中的数据。

➢ DROP、TRUNCATE都是DDL语句（数据定义语言Data Definition Language），执行后会自动提交。

· 差异性

➢ 功能：TRUNCATE和DELETE只删除数据，不删除表的结构，而DROP还会删除表结构和相关的依赖（索引等）。

➢ 效率：DROP效率 > TRUNCATE效率 > DELETE效率。

➢ 安全性：在没有备份前，小心使用DROP和TRUNCATE。如果涉及事务处理，最好采用DELETE。

➢ 适用性：场景1，想删除部分数据，使用DELETE...WHERE..．结构；场景2，想删除表，使用DROP来操作；场景3，想保留表结构，删除所有数据，使用TRUNCATE来操作；

➢ 效果性：DELETE不影响表所占用的extent，高水线（high watermark）保持原位置不动；DROP将表所占用的空间全部释放；TRUNCATE将空间释放到minextents个extent。

使用DROP来删表，代码如下。

      DROP TABLE input_base;

使用DELETE来删除部分数据，代码如下。

      DELETE FROM input_base WHERE sex=’男’;

使用TRUNCATE来清空表数据，代码如下。

      TRUNCATE TABLE  input_base;

3．改

改，是使用最为频繁的操作，如在表结构上的修改、在数据上的修改，以及在数据类型上的修改等，具体使用说明如下。

1）对表结构的修改

· 新增列

➢ 首位。

➢ 末尾。

➢ 指定位置。

      ALTER TABLE input_base ADD uuid  varchar(50)COMMENT’唯一标识’first;
      ALTER TABLE input_base ADD num  int(10) COMMENT ’文章数量’;
      ALTER TABLE input_base ADD amount  INT(20) COMMENT ’总额’AFTER mid;

· 删除列

      ALTER TABLE input_base  DROP update_ymd;

说明：删除input_base表中的update_ymd列。

2）对数据的修改

      UPDATE input_base SET num = 5  WHERE sex="女";

说明：将性别是女的数据中num（文章数量）的值更新为5。

3）对数据类型的修改

      ALTER TABLE input_base MODIFY  COLUMN degree VARCHAR(100)
      ALTER TABLE input_base CHANGE degree degree VARCHAR(100)；

4）对字段名的修改

      ALTER TABLE input_base CHANGE degrees degree VARCHAR(100)；

说明：将degree的数据类型由VARCHAR(50)修改为VARCHAR(100)。而MODIFY与CHANGE的差异性主要体现在写法的简洁性与应用场景上。

4．查

对于查，比较常见的操作主要细分为对表结构、全表数据，特定列数据的查询，具体的使用说明如下。

（1）查询表结构：DESC input_base，如图2-8所示。

图2-8 查询表结构

（2）全表查询（取前10条数据）。

      SELECT ＊ FROM input_base LIMIT 10;

（3）特定列查询。

      SELECT id, mid, sex  FROM input_base LIMIT 10;

（4）条件查询。

      SELECT id, mid, sex FROM input_base  WHERE sex="女" LIMIT 10;