数据库与表的操作（一）

Mr.he...大约 9 分钟database table

1. 前言

此文旨在汇总从建立数据库到联接查询等绝大部分SQL语句。SQL语句虽不能说很多，但稍有时间不写就容易出错。

SQL是 Structured Query Language 即“结构化查询语言”的简称，它是用来管理关系型数据库的。

其包括：

  1. 数据控制语言（DCL）(Data Control Language)【使用最少】

    是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

  2. 数据定义语言（DDL）(Data Definition Language)

    DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

  3. 数据查询语言（DQL）(Data Query Language)【使用最多，其中的联合查询内容最重要】

    DQL允许用户查询数据，这也是通常最频繁的数据库日常操作

  4. 数据操作语言（DML）(Data Manipulation Language)

    DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

2. 语法特点

SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

所以，本教程约定：SQL关键字总是大写，以示突出，表名和列名均使用小写。

3. 对数据库的操作

3.1. 创建数据库

CREATE DATABASE 数据库名;

数据库名命名规则：

  • 第一个字符必须为下列之一：

  • 后续字符由组成。

  • 不能为的保留关键字。

  • 不允许。

本文以SQLyog软件为例，创建数据库如下：

CREATE DATABASE test_sql;

此图为SQLyog的左侧目录栏，前三个为本地自带数据库。将SQL语句全部选中运行（F8）后，F5刷新目录栏，出现了我们创建的数据库。

3.2. 查看数据库

查看当前有几个数据库可供连接。

SHOW DATABASES;

3.3. 连接数据库

USE test_sql;

3.4. 查看当前连接数据库

SELECT DATABASE();

3.5. 删除数据库

DROP DATABASE test_sql;

4. MySQL数据类型

接下来就开始创建表了，在创建表之前先列出几种常用的数据类型

4.1. 整数数据类型

名称类型说明
INT整型4字节整数类型，范围约+/-21亿
BIGINT长整型8字节整数类型，范围约+/-922亿亿

4.2. 浮点数据类型

名称类型说明
FLOAT或REAL浮点型4字节浮点数，范围约+/-1038，精度7位小数
DOUBLE浮点型8字节浮点数，范围约+/-10308，精度15位小数

因为不精准，因此不能使用在货币这类严格要求准确的位置，通常我们更经常使用整数类型用来计算货币。

4.3. 高精度小数

名称类型说明
DECIMAL(M,N)高精度小数由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算

4.4. 字符串数据类型

名称类型说明
CHAR(N)定长字符串存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)变长字符串存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串

4.5. 布尔类型

名称类型说明
BOOLEAN布尔类型存储True或者False

4.6. 枚举类型

名称类型说明
ENUM枚举类型只能存指定的值，一般用于表单的多选框，如：男，女，未知，SQL可存为：1，2，0

使用示例：

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active'
);

说明

status 字段只能取 'active' 或 'inactive' 两个选项值。默认值被设置为 'active'。

4.7. 日期数据类型

名称类型说明
DATE日期类型存储日期，例如，2018-06-22
TIME时间类型存储时间，例如，12:20:59
DATETIME日期和时间类型存储日期+时间，例如，2018-06-22 12:20:59

5. 对表的操作(DDL)

也就是前面说的 数据定义语言（DDL）(Data Definition Language)。

数据库中对表的操作有：

  • 创建表；
  • 查看表的数量；
  • 查看表的结构;
  • 删除表；
  • 复制表；
    • 复制表的结构及内容
    • 只复制表的结构
  • 修改表；
    • 添加列
    • 更改列
    • 删除列

5.1. 创建表

CREATE TABLE < 表名 > (< 列名 > < 列的数据类型 > [<列的约束>]);

如：

CREATE TABLE t_student(
  # utf8字符集下中文一个字占三个字符
  student_name VARCHAR(20),
  student_birthday DATETIME,
  student_phone INT,
  student_score FLOAT
);

上表中我们创建了一张学生表，并定义了姓名、生日、电话、得分四列，列名后加上数据类型。其中要注意的是VARCHAR需要在括号内设置字符串的最大长度。

刷新之后就可以看到我们创建的数据库中多了一张表：

选择打开表可以直观的看到内容：

之后我们可以通过SQL语句也可以通过手动为表添加内容。

5.2. 查看表的数量

SHOW TABLES;

5.3. 查看表的结构

如果你想要知道一个表的结构，可以使用DESCRIBE命令；它显示表中每个列的信息：

DESCRIBE t_student;

5.4. 删除表

DROP TABLE t_student;

5.5. 复制表

5.5.1. 同时复制表的结构和内容

CREATE TABLE copy_student
SELECT
  *
FROM t_student;

如此我们便复制了一张名为copy_student的表，它包括t_student表中的内容与结构。

注意

复制表的同时表的约束并不能复制过来。

5.5.2. 只复制表结构而不复制表内容

CREATE TABLE copy_student
SELECT
  *
FROM t_student
WHERE
  1 = 0;

只需在WHERE条件中加入一个永不为真的值即可。

5.6. 修改表

5.6.1. 添加新列

ALTER TABLE t_student
ADD
  student_address VARCHAR(50);

5.6.2. 更改列

ALTER TABLE t_student
CHANGE
  student_birthday student_age INT;

这里我们把学生生日列（student_birthday）改为学生年龄列（student_age），CHANGE后第一个为旧列名，第二个为新列名。

5.6.3. 删除列

ALTER TABLE t_student
DROP COLUMN
  student_score;

6. 数据库完整性

保证数据库的完整性是为了防止垃圾数据的产生，以免影响数据库的执行效率。这里简要说一些，因为毕竟不是理论类的文章，这里主要是整理汇总SQL语句。

分类：

  1. 实体完整性（主键约束，唯一约束） 保证一行数据是有效的

  2. 域完整性（非空约束，默认约束） 保证一列数据是有效的

  3. 引用完整性（外键约束） 保证引用的编号是有效的

  4. 用户自定义完整性 保证自定义规则

6.1. 实体完整性--主键约束

关键字：PRIMARY KEY

主键列不能为空也不能重复，通常加在表的id列中。

CREATE TABLE t_student(
  student_id INT PRIMARY KEY,
  student_name VARCHAR(10),
  student_birthday DATETIME,
  student_phone INT,
  student_score FLOAT
);

6.2. 实体完整性--唯一约束

关键字：UNIQUE

唯一约束是指给定列的值必须唯一，与主键约束不同的是它可以为空。通常加在表中不能重复的信息中，如电话号码。

CREATE TABLE t_student(
  student_id INT PRIMARY KEY,
  student_name VARCHAR(10),
  student_birthday DATETIME,
  student_phone INT UNIQUE,
  student_score FLOAT
);

6.3. 域完整性--非空约束

关键字：NOT NULL

非空约束可以加在诸如姓名等列上。

CREATE TABLE t_student(
  student_id INT PRIMARY KEY,
  student_name VARCHAR(10) NOT NULL,
  student_birthday DATETIME,
  student_phone INT UNIQUE,
  student_score FLOAT
);

6.4. 域完整性--默认约束

关键字：DEFAULT

设定默认值后，可以在添加此列时不指定值,数据库会自动填充设定的默认值。

现给学生表加入性别列，默认值设为“男”，这样添加新的学生信息时如果没有填写具体的性别均会默认为男性：

CREATE TABLE t_student(
  student_id INT PRIMARY KEY,
  student_name VARCHAR(10) NOT NULL,
  student_sex VARCHAR(5) DEFAULT '男',
  student_birthday DATETIME,
  student_phone INT UNIQUE,
  student_score FLOAT
);

6.5. 引用完整性--外键约束

外键约束是指在外键关联主键上强制加上一个约束，如果违反该约束，则不允许该条数据的修改。可以通过两种方式创建，第一种是在创建表的同时创建约束，第二种是已经创建表后，再来创建约束。

6.5.1. 1.创建表时，同时创建约束

创建主表--班级表：

CREATE TABLE t_class(
  class_id INT PRIMARY KEY,
  class_name VARCHAR(20) UNIQUE NOT NULL
);

创建从表--学生表，并设置外键约束：

CREATE TABLE t_student(
  student_id INT PRIMARY KEY,
  s_c_id INT,
  student_name VARCHAR(10) NOT NULL,
  student_sex VARCHAR(5) DEFAULT '男',
  student_birthday DATETIME,
  student_phone INT UNIQUE,
  student_score FLOAT,
  CONSTRAINT fk_class_id FOREIGN KEY(s_c_id) REFERENCES t_class(class_id);

上面的第8行代码就是创建外键约束的方法，个人认为也是SQL语句中最难记的。

提示

创建约束后，那么在创建学生时，必须关联到对应的班级，不能独立创建没有班级的学生。

6.5.2. 2.已创建表后，追加外键约束

ALTER table t_student
ADD
  CONSTRAINT fk_class_id FOREIGN KEY(s_c_id) REFERENCES t_class(class_id);

这里值得注意的是，在追加约束时，如果我们已创建好的表中有不符合约束条件的数据，那么将会报错约束冲突，可以通过添加语句WITH NOCHECK解决，这样就只会对后面新加的数据进行校验，而前面已有的数据忽略不管。

上述语句可修改为如下：

ALTER table t_student WITH NOCHECK
ADD
  CONSTRAINT fk_class_id FOREIGN KEY(s_c_id) REFERENCES t_class(class_id);

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，s_c_id仅仅是一个普通的列，只是它起到了外键的作用而已。

6.5.3. 3.删除外键约束

ALTER TABLE t_student drop FOREIGN KEY fk_class_id;

注意

删除外键约束并没有删除外键这一列，只是去除外键将这一列（s_c_id）变为一个普通的列。删除列是通过DROP COLUMN ...实现的。

默认页脚
Copyright © 2024 Mr.he