数据库教程（一）-MySQL的DDL

发表于 | 分类于 ，

1. 概述

DDL 的英文全称是 Data Definition Language，中文是数据定义语言。它定义了数据库的结构和数据表的结构。在 DDL 中，我们常用的功能是增删改，分别对应的命令是 CREATE、DROP 和 ALTER。

2. 操作数据库

  • 创建数据库
    • 创建一个名为 test的数据库。如果这个数据已经存在，那么会报错
      • $ CREATE DATABASE test;
    • 如果不存在创建，存在就不创建
      • $ create database if not exists test;
  • 删除数据库
    • 删除一个名为test的数据库
      • $ DROP DATABASE test;
    • 如果存在删除，不存在就不删除
      • $ drop database if exists test;
  • 修改数据库编码
    • 修改数据库test的编码为utf8。注意，在MySQL中所有的UTF-8编码都不能使用中间的“-”，即UTF-8要书写为UTF8
      • $ alter database test character set utf8;

3. 操作表

  • 创建表结构的语法
    • $ CREATE TABLE [table_name](字段名 数据类型，……)
    • 例如，我们想创建一个球员表，表名为 player，里面有两个字段，一个是 player_id，它是 int 类型，另一个 player_name 字段是varchar(255)类型。这两个字段都不为空，且 player_id 是递增的；数据类型中 int(11) 代表整数类型，显示长度为 11 位，括号中的参数 11 代表的是最大有效显示长度，与类型包含的数值范围大小无关。varchar(255)代表的是最大长度为 255 的可变字符串类型。NOT NULL表明整个字段不能是空值，是一种数据约束。AUTO_INCREMENT代表主键自动增长。
1
2
3
4
CREATE TABLE player (
player_id int(11) NOT NULL AUTO_INCREMENT,
player_name varchar(255) NOT NULL
);
  • 查看当前数据库中所有表名称
    • $ show tables;
  • 修改表结构
    • 添加列，比如我在数据表中添加一个 age 字段，类型为int(11)
      • $ ALTER TABLE player ADD (age int(11));
    • 修改字段名，将 age 字段改成player_age
      • $ ALTER TABLE player change age player_age int(11);
    • 修改字段的数据类型，将player_age的数据类型设置为float(3,1)
      • $ ALTER TABLE player MODIFY player_age float(3,1);
    • 删除字段, 删除刚才添加的player_age字段
      • $ ALTER TABLE player DROP player_age;
  • 删除表
    • $ dorp table test;
  • 查看表结构
    • $ desc test;

4. 数据表的常见约束

  • 主键约束
    • 主键起的作用是唯一标识一条记录，不能重复，不能为空
  • 外键约束
    • 外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。
  • 字段约束
    • 唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。
    • NOT NULL 约束。对字段定义了 NOT NULL，即表明该字段不应为空，必须有取值。
    • DEFAULT，表明了字段的默认值。
    • CHECK 约束，用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE，比如我们可以对身高 height 的数值进行 CHECK 约束，必须≥0，且＜3，即CHECK(height>=0 AND height<3)。

5. 检索数据

  • SELECT 的作用是从一个表或多个表中检索出想要的数据行。
  • 查询列
    • 想要对数据表中的某一列进行检索，在 SELECT 后面加上这个列的字段名
      • 比如我们想要检索数据表中都有哪些英雄
      • $ SELECT name FROM heros
    • 也可以对多个列进行检索，在列名之间用逗号 (,) 分割
      • 比如我们想要检索有哪些英雄，他们的最大生命、最大法力、最大物攻和最大物防分别是多少
      • $ SELECT name, hp_max, mp_max, attack_max, defense_max FROM heros
    • 使用 SELECT * 帮我们检索出所有的列
      • $ SELECT * FROM heros
  • 起别名
    • 使用 SELECT 查询的时候，还有一些技巧可以使用，比如你可以给列名起别名
      • 例如，在进行检索的时候，可以给英雄名、最大生命、最大法力、最大物攻和最大物防等取别名
      • $ SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_max AS am, defense_max AS dm FROM heros
  • 查询常数
    • SELECT 查询还可以对常数进行查询
    • 比如说，我们想对 heros 数据表中的英雄名进行查询，同时增加一列字段platform，这个字段固定值为“王者荣耀”
    • $ SELECT ‘王者荣耀’ as platform, name FROM heros
  • 去除重复行
    • 从结果中去掉重复的行。使用的关键字是 DISTINCT。
      • 比如我们想要看下 heros 表中关于攻击范围的取值都有哪些
      • $ SELECT DISTINCT attack_range FROM heros
    • DISTINCT 需要放到所有列名的前面，如果写成SELECT name, DISTINCT attack_range FROM heros会报错
    • DISTINCT 其实是对后面所有列名的组合进行去重
  • 排序检索数据
    • 当我们检索数据的时候，有时候需要按照某种顺序进行结果的返回，比如我们想要查询所有的英雄，按照最大生命从高到底的顺序进行排列，就需要使用 ORDER BY 子句
    • 排序的列名：ORDER BY 后面可以有一个或多个列名，如果是多个列名进行排序，会按照后面第一个列先进行排序，当第一列的值相同的时候，再按照第二列进行排序，以此类推。
    • 排序的顺序：ORDER BY 后面可以注明排序规则，ASC 代表递增排序，DESC 代表递减排序。如果没有注明排序规则，默认情况下是按照 ASC 递增排序。我们很容易理解 ORDER BY 对数值类型字段的排序规则，但如果排序字段类型为文本数据，就需要参考数据库的设置方式了
    • 非选择列排序：ORDER BY 可以使用非选择列进行排序，所以即使在 SELECT 后面没有这个列名，你同样可以放到 ORDER BY 后面进行排序。
    • ORDER BY 的位置：ORDER BY 通常位于 SELECT 语句的最后一条子句，否则会报错。
    • 假设我们想要显示英雄名称及最大生命值，按照最大生命值从高到低的方式进行排序
      • $ SELECT name, hp_max FROM heros ORDER BY hp_max DESC
  • 约束返回结果的数量
    • 约束返回结果的数量，使用 LIMIT 关键字
      • 比如我们想返回英雄名称及最大生命值，按照最大生命值从高到低排序，返回 5 条记录即可
      • $ SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5
  • 执行顺序
    • 关键字的顺序是不能颠倒的
      • $ SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …
    • SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）
      • $ ROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

6. 数据过滤

  • WHERE 子句的基本格式是：SELECT ……(列名) FROM ……(表名) WHERE ……(子句条件)
    • 比如我们想要查询所有最大生命值大于 6000 的英雄
      • $ SELECT name, hp_max FROM heros WHERE hp_max > 6000
    • 想要查询所有最大生命值在 5399 到 6811 之间的英雄
      • $ SELECT name, hp_max FROM heros WHERE hp_max BETWEEN 5399 AND 6811
    • 第一部分是关于主要定位和次要定位的条件过滤，使用的是role_main in (‘法师’, ‘射手’) OR role_assist in (‘法师’, ‘射手’)。这里用到了 IN 逻辑运算符，同时role_main和role_assist是 OR（或）的关系。第二部分是关于上线时间的条件过滤。NOT 代表否，因为我们要找到不在 2016-01-01 到 2017-01-01 之间的日期，因此用到了NOT BETWEEN ‘2016-01-01’ AND ‘2017-01-01’。同时我们是在对日期类型数据进行检索，所以使用到了 DATE 函数，将字段 birthdate 转化为日期类型再进行比较。
      • $ SELECT name, role_main, role_assist, hp_max, mp_max, birthdate FROM heros WHERE (role_main IN (‘法师’, ‘射手’) OR role_assist IN (‘法师’, ‘射手’)) AND DATE(birthdate) NOT BETWEEN ‘2016-01-01’ AND ‘2017-01-01’ ORDER BY (hp_max + mp_max) DESC
    • 通配符就是我们用来匹配值的一部分的特殊字符
      • 如果我们想要匹配任意字符串出现的任意次数，需要使用（%）通配符。比如我们想要查找英雄名中包含“太”字的英雄
      • $ SELECT name FROM heros WHERE name LIKE ‘%太%’

7. 参考