一、SQL简介
- 结构化查询语言（Structured Query Language）简称 SQL；
- 一种特殊目的的编程语言；
- 一种数据库查询和程序设计语言；
- 用于存取数据以及查询、更新和管理关系数据库系统。
1.1 通用语法
- 可以单行或者多行书写，以分号
;结尾 ； - 可以使用空格和缩进来增加语句的可读性；
- 不区分大小写，一般关键字大写，数据库名 表名列名小写；
- 注释方式：
# 单行注释，MySQL 特有的单行注释
-- 单行注释
/*
多行注释
*/
1.2 分类
- 数据定义语言（DDL）：用来定义数据库对象，比如数据库，表，列等；
- 数据操作语言（DML）：用来对数据库中表的记录进行更新；
- 数据查询语言（DQL）：用来查询数据库中表的记录；
- 数据控制语言（DCL）：用来定义数据库的访问权限和安全级别及创建用户。
二、DDL数据库操作
2.1 简介
-
DDL（Data Definition Language）被称为数据库定义语言，主要包含三个方面的内容：
- 对数据库的常见操作；
- 对表结构的常见操作；
- 修改表结构。
-
DDL允许用户创建新表，指定表的列、数据类型、约束条件等；修改现有表的结构，例如添加新列、删除列、修改列的数据类型等；删除不再需要的表，以释放数据库资源。
-
DDL还用于创建和删除索引，创建和删除视图，定义约束条件（如主键、外键、唯一约束等）以及管理数据库中的模式。
-
DDL主要负责对数据库和表的整体结构进行操作，但不涉及数据本身。
2.2 使用场景
-
- 数据建模和设计：在数据库开发过程中，DDL被广泛应用于定义和创建数据库结构。这包括创建表、视图、索引、约束和其他数据库对象。
-
- 数据完整性维护：通过定义主键、外键、唯一性约束等，DDL有助于维护数据的完整性。这确保了数据的准确性和可靠性，防止了无效或不完整的数据被存储在数据库中。
-
- 修改现有结构：当现有数据库结构需要更改时，例如添加、删除或修改列，添加或删除约束等，DDL允许数据库管理员进行这些更改，而无需直接操作数据。
-
- 简化数据管理：通过使用DDL，数据库管理员可以定义复杂的对象关系映射，使数据存储、检索和使用更为简单和高效。
-
- 备份和迁移：通过创建数据库结构和定义，DDL也可以用于创建备份和迁移数据。这使得在不同系统或平台之间迁移数据变得简单和可靠。
2.3 优点
-
- 结构化查询：DDL提供了结构化的查询语句，使得数据库操作更加规范和一致。
-
- 数据完整性：通过使用约束、触发器等机制，DDL有助于确保数据的准确性和完整性。
-
- 简化操作：通过定义数据库结构，DDL简化了数据的存储、检索和使用。
-
- 可移植性：在不同的数据库管理系统中，DDL具有很高的可移植性，使得数据管理更为灵活。
-
- 安全性：通过定义用户权限和角色，DDL可以控制对数据库的访问，增强数据的安全性。
2.4 创建数据库
-
在MySQL中，可以使用
CREATE DATABASE语句和CREATE SCHEMA语句来创建数据库。 -
IF NOT EXISTS：可选项，创建前先判断，未存在时才执行创建语句。 -
数据库名：必须指定，在文件系统中，MySQL的数据存储区将以目录方式表示MySQL数据库，不区分大小写。
-
CHARACTER SET [=] 字符集：可选项，用于指定数据库的字符集。
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS]数据库名
[
CHARACTER SET [=] 字符集
]
2.4.1 创建指定字符集的数据库
-- 创建名为test_db1的普通数据库
CREATE DATABASE test_db1
-- 创建名为 test_db2 的数据库，并指定字符集为 utf8
CREATE DATABASE test_db2 CHARACTER SET utf8;
2.4.2 创建数据库前，判断是否存在同名数据库
-- 如果数据库 test_db3 不存在，则创建名为 test_db3 的数据库
CREATE DATABASE IF NOT EXISTS test_db3 CHARACTER SET utf8;
2.4.3 创建数据库注意事项
-
不能与其他数据库重名，否则将发生错误；
-
名称可以由任意字母、阿拉伯数字、下划线（_）和“$”组成，可以使用上述的任意字符开头，但不能使用单独的数字，否则会与数值相混淆；
-
名称最长可为64个字符，而别名最长为256个字符；
-
不能使用MySQL关键字作为数据库名；
-
默认情况下，在Windows下的数据库名、表名的大小写不敏感，而在Linux下的大小写是敏感的。为了便于数据库在平台间进行移植，建议采用小写来定义数据库名。
2.5 查看数据库
- 连接到MySQL数据库后，可以使用SHOW命令查看MySQL服务器中的所有数据库信息：
-- 查看所有数据库
SHOW DATABASES;
- DATABASE：表示必须有一个是必选项，用于列出当前用户权限范围内所能查看到的所有数据库名称。
2.5.1 选择数据库
-- 选择数据库为当前数据库
USE 数据库名;
2.5.2 查看数据库的定义信息
-- 查看数据库定义信息
SHOW CREATE DATABASE 数据库名;
2.6 修改数据库
-
修改数据库可以使用
ALTER DATABASE或者ALTER SCHEMA语句来实现； -
DATABASE：必选项； -
数据库名：可选项，如果不指定要修改的数据库，就表示修改当前（默认）的数据库；
-
CHARATER SET [=] 字符集：可选项，用于指定数据库的字符集。如果不想指定数据库所使用的字符集，那就可以不使用该项，这时 MySQL 会根据 MySQL 服务器默认使用的字符集来创建该数据库。这里的字符集可以是 GB2312 或者 GBK（简体中文）、UTF8（针对 Unicode 的可变长度的字符编码，也称万国码）、BIG5（繁体中文）、Latin1（拉丁文）等。其中最常用的就是 UTF8。
-- 修改数据库相关参数
ALTER {DATABASE} [数据库名]
CHARACTER SET [=] 字符集
- 注意：在使用
ALTER DATABASE或者ALTER SCHEMA语句时，用户必须具有数据库的修改权限。
2.6.1 修改数据库字符集
-- 创建数据库 db1，指定字符集为 GBK
CREATE DATABASE db1 CHARACTER SET GBK;
-- 将数据库 db1 的字符集修改为 utf8
ALTER DATABASE db1 CHARACTER SET utf8;
2.7 删除数据库
-
在MySQL中，可以通过使用
DROP DATABASE语句来删除已存在的数据库。使用该命令删除数据库的同时，该数据库中的表、表中的数据也将永久删除。因此，在使用该语句删除数据库时一定要小心，以免误删有用的数据库。 -
DATABASE：必选项； -
IF EXISTS：用于指定在删除数据前，先判断该数据是否已经存在，只有已存在时，才会执行删除操作，以避免删除不存在的数据库时产生异常。
-- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
2.7.1删除某个数据库
-- 查看当前所有数据库
SHOW DATABASES;
-- 删除某个数据库
DROP DATABASE test_db;
-- 如果某个数据库存在，则删除这个数据库
DROP DATABASE IF EXISTS test_db2;
2.7.2 注意事项
- MySQL安装后，系统会自动创建两个名称分别为performance_schema和mysql的系统数据库，MySQL把数据库相关的信息存储在这两个系统数据库中，如果删除了这两个数据库，则MySQL无法正常工作。
2.8 MySQL的数据类型
2.8.1 数字类型
| 数据类型 | 说明 |
|---|---|
| TINYINT | 0~255或-128~127，1字节，最小的整数 |
| SMALLINT | 0~65535或-32768~32767,2字节，小整数型 |
| MEDIUMINT | 0~16777215或-8388608~8388607,3字节，中型整数 |
| INT | 0～4294967295 或 -2147683648～2147683647，4字节，标准整数 |
| BIGINT | 8字节，大整数 |
| FLOAT | 单精度浮点值 |
| DOUBLE | 双精度浮点值 |
| BOLLEAN | 布尔值 |
2.8.2 字符串类型
| 数据类型 | 说明 |
|---|---|
| CHAR | 1～255 个字符，固定长度字符串 |
| VARCHAR | 长度可变，最多不超过 255 个字符 |
| TEXT | 最大长度为 64K 的变长文本 |
| TINYTEXT | 与 TEXT 相同，但最大长度为 255 字节 |
| MEDIUMTEXT | 与 TEXT 相同，但最大长度为 16K |
| LONGTEXT | 与 TEXT 相同，但最大长度为 4GB |
2.8.3 日期和时间类型
| 数据类型 | 说明 |
|---|---|
| DATE | 日期，格式 YYYY-MM-DD |
| TIME | 时间，格式 HH:MM:SS |
| DATETIME | 日期和时间，格式 YYYY-MM-DD HH:MM:SS |
| TIMESTAMP | 时间标签，功能和 DATETIME 相同，但范围较小 |
| YEAR | 年份可指定两位数字和四位数字的格式 |
2.8.4 常用数据类型
-
INT：整型；
-
DOUBLE：浮点型；
-
VARCHAR：字符串型；
-
DATE：日期类型。
三、DDL数据库表的操作
3.1 创建表语法
-- 创建表
CREATE TABLE 数据表名(
列名1 属性，
列名2 属性···
);
3.2 列属性
- NOT NULL|NULL：该列是否允许时空值；
- DEFAULT：表示默认值；
- AUTO_INCREMENT：表示是否是自动编号；
- PRIMARY KEY：表示是否为主键。
列表 数据类型 [NOT NULL | NULL] [DEFAULT 默认值] [AUTO_INCREMENT] [PRIMARY KEY] [注释]
- 在 db1 数据库中创建：
- 表名为 student
- 包含两个字段
- 学员 id
- 学员姓名
3.3 复制表语法
- 数据表名：表示新创建的数据表的名；
- LIKE 源数据表名：必选项，指定依照哪个数据表来创建新表。
复制表
CREATE TABLE数据表名
{LIKE 源数据表名 | (LIKE 源数据表名)}
3.4 复制结构相同的表
-- 创建一个表结构与 student 相同的 s2 表
CREATE TABLE s2 LIKE student;
3.5 查看表语法
3.5.1 查看表名语法
-- 查看当前数据库中所有的表名
SHOW TABLES;
3.5.2 查看表结构语法
-- 查看表结构
DESCRIBE 数据表名;
DESCRIBE 数据表名 列名;
-- 查看表结构简写
DESC 数据表名;
DESC 数据表名 列名;
示例：
-- 选择数据库
USE db1;
-- 查看数据库中的表
SHOW TABLES;
-- 查看学员表的表结构
DESC student;
-- 查看学员表中 name 列的信息
DESC student name;
3.6 修改表语法
3.6.1 添加新列
-- 添加新列
ALTER TABLE 表名 ADD 列名 列属性;
示例：
-- 选择数据库 db1
USE db1;
-- 添加新列
ALTER TABLE student ADD email varchar(50) NOT NULL;
-- 查看表结构
DESC student;
3.6.2 修改列定义
-- 修改列定义
ALTER TABLE 表名 MODIFY 列名 列属性;
示例：
-- 添加分数列，先定义为字符类型
ALTER TABLE student ADD score varchar(10);
-- 修改字段类型
ALTER TABLE student modify score int;
-- 查看表结构
DESC student;
3.6.3 修改列名
-- 修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;
示例：
-- 修改列名并指定列的默认值
ALTER TABLE student
CHANGE COLUMN name stu_name VARCHAR(30) DEFAULT NULL;
-- 查看表结构
DESC student;
3.6.4 删除列
-- 删除列
ALTER TABLE 表名 DROP 列名;
# 实例
-- 将数据表 student 中的列 score 删除
ALTER TABLE student DROP score;
-- 查看表结构
DESC student;
3.6.5 修改表名
-- 修改表名方式一
ALTER TABLE 旧表名 RENAME AS 新表名;
-- 修改表名方式二
RENAME TABLE 旧表名 To 新表名;
# 实例
-- 将数据表 student 更名为 stu
ALTER TABLE student RENAME AS stu;
-- 将数据表 stu 更名为 stu_table
RENAME TABLE stu To stu_table;
-- 查看表名
SHOW TABLES;
3.7 删除表语法
DROP TABLE [IF EXISTS] 数据表名;
-
IF EXISTS：可选项，先判断是否存在要删除的表，存在时才执行删除操作。 -
数据表名：用于指定要删除的数据表名。
示例：
-- 切换到数据库 db1
USE db1;
-- 创建 student 表
CREATE TABLE student(
id INT,
name VARCHAR(20)
);
-- 直接删除 student 表
DROP TABLE student;
-- 先判断再删除 student 表
DROP TABLE IF EXISTS student;