Mysql

MySQL 中定义数据字段的类型对数据库的优化是非常重要的。MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

image.png

日期和时间类型

image.png

字符串类型

image.png

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

在 MySQL 里，创建和删除数据库是常见操作。下面为你介绍具体步骤及示例：

创建数据库

创建数据库时，要使用 CREATE DATABASE 语句，其语法如下：

CREATE DATABASE database_name;

示例

下面是一个创建名为 Students的数据库的示例：

CREATE DATABASE Students

在创建数据库时，还能设置字符集和排序规则，示例如下：

CREATE DATABASE Students
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

删除数据库

删除数据库需要用到 DROP DATABASE 语句，其语法为：

DROP DATABASE database_name;

示例

下面是一个删除名为 Students的数据库的示例：

DROP DATABASE Students;

创建表并添加约束

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,                -- 主键 + 自增
    username VARCHAR(50) UNIQUE NOT NULL,                 -- 唯一约束
    email VARCHAR(100) UNIQUE,                            -- 唯一约束
    age TINYINT UNSIGNED DEFAULT 18,                     -- 默认约束
    role ENUM('user', 'admin', 'moderator') DEFAULT 'user', -- 默认约束
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,      -- 默认约束
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 自动更新时间戳
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'pending')), -- 检查约束
    country_code CHAR(2) CHECK (country_code REGEXP '^[A-Z]{2}$'), -- 检查约束
    parent_user_id INT,
    FOREIGN KEY (parent_user_id) REFERENCES users(user_id) -- 外键约束（自关联）
);

约束说明：
主键约束：user_id INT PRIMARY KEY
唯一标识表中的每一行
自动创建隐式的唯一索引
不允许 NULL 值
自增约束：AUTO_INCREMENT
自动为新记录生成连续的整数值
通常与主键配合使用
唯一约束：UNIQUE
确保列中的所有值唯一
允许 NULL 值（但每个 NULL 被视为唯一）
默认约束：DEFAULT value
为列指定默认值
当插入记录未提供值时使用
检查约束：CHECK (condition)
确保列中的值满足特定条件
MySQL 5.7 及以下版本会解析但忽略 CHECK 语法
MySQL 8.0 开始正式支持 CHECK 约束
外键约束：FOREIGN KEY REFERENCES
建立表之间的关联关系
确保引用的记录存在于关联表中
支持级联更新 / 删除操作

在 MySQL 中，修改表结构（添加、修改或删除列）是常见操作。以下是具体的 SQL 语法和示例：

ALTER TABLE table_name
ADD COLUMN column_name data_type [约束条件] [FIRST | AFTER existing_column];

示例

-- 在 users 表中添加 phone 列（VARCHAR 类型，允许 NULL）
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) AFTER email;

-- 添加创建时间戳列（默认值为当前时间）
ALTER TABLE products
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 添加外键列并设置约束
ALTER TABLE orders
ADD COLUMN customer_id INT,
ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

. 修改列（MODIFY COLUMN / CHANGE COLUMN）

修改列的数据类型（MODIFY COLUMN）语法：

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type [约束条件];

示例

-- 将 age 列的数据类型从 TINYINT 改为 INT
ALTER TABLE users
MODIFY COLUMN age INT UNSIGNED;

-- 将 email 列改为 NOT NULL
ALTER TABLE users
MODIFY COLUMN email VARCHAR(100) NOT NULL;

修改列名和数据类型(CHANGE COLUMN）语法：

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_data_type [约束条件];

示例

-- 将 username 列改为 user_name，并增加长度限制
ALTER TABLE users
CHANGE COLUMN username user_name VARCHAR(100) UNIQUE;

-- 将 birth_date 列改为出生日期并设置默认值
ALTER TABLE employees
CHANGE COLUMN birth_date date_of_birth DATE DEFAULT '1970-01-01';

. 删除列（DROP COLUMN）

语法：

ALTER TABLE table_name
DROP COLUMN column_name;

示例

-- 删除 users 表中的 phone 列
ALTER TABLE users
DROP COLUMN phone;

-- 删除带有外键约束的列（需先删除外键）
ALTER TABLE orders
DROP FOREIGN KEY orders_ibfk_1;  -- 先删除外键约束（名称可能不同）
ALTER TABLE orders
DROP COLUMN customer_id;

注意事项
数据类型兼容性：
修改列的数据类型时，需确保现有数据能兼容新类型（例如，VARCHAR → INT 可能导致数据丢失）。
使用 ALTER TABLE 可能会锁表，大表操作需谨慎。
约束管理：
添加 / 修改列时可同时设置约束（如 NOT NULL、UNIQUE、DEFAULT）。
删除列时，相关约束（如外键）会自动删除，但需先删除外键约束本身。

多操作合并：

可在一条 ALTER TABLE 语句中执行多个操作：

ALTER TABLE users
ADD COLUMN address VARCHAR(255),
MODIFY COLUMN age INT,
DROP COLUMN phone;

操作示例

-- 添加部门列
ALTER TABLE employees ADD COLUMN department VARCHAR(50) AFTER name;

-- 修改工资列的数据类型和默认值
ALTER TABLE employees MODIFY COLUMN salary INT DEFAULT 0;

-- 将 hire_date 改为入职时间戳
ALTER TABLE employees CHANGE COLUMN hire_date入职时间 TIMESTAMP;

-- 删除部门列
ALTER TABLE employees DROP COLUMN department;

在Students数据库中，创建 StuInfo表，StuMarks表

USE Students
CREATE TABLE StuInfo
(
    StuID int NOT NULL PRIMARY KEY,
    StuName varchar(30) NOT NULL,
    StuSex char(2) DEFAULT('男')
)

USE Students
CREATE TABLE StuMarks
(
    ExamNO int NOT NULL PRIMARY KEY ,
    StuID int NOT NULL ,
    Subject varchar(30) NOT NULL,
    Score int NOT NULL CHECK(Score >= 0 and Score <=100) ，
    FOREIGN KEY (StuID ) REFERENCES StuInfo(StuID)  
)

在StuInfo中插入数据

USE Students
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(1,'张三',DEFAULT)
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(2,'李四',DEFAULT)
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(3,'王五','女')
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(4,'马六','女')
INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(5,'赵七',DEFAULT)

在StuMarks中插入数据

USE Students
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(1,1,'java',98)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(2,1,'html',80)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(3,2,'java',70)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(4,3,'java',60)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(5,4,'java',55)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(6,5,'java',66)
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(7,5,'html',48)

--或者
INSERT INTO StuMarks(ExamNO,StuID,Subject,Score) VALUES(1,1,'java',98),(2,1,'html',80),(3,2,'java',70),(4,3,'java',60),(5,4,'java',55),(6,5,'java',66),(7,5,'html',48)

`>SELECT语法
SELECT column1, column2, columnN FROM table_name;

select StuID, StuName,StuSex FROM StuInfo

选取全部字段查询

SELECT * FROM StuInfo

选取部分查询

SELECT StuID,Subject,Score FROM StuMarks

设置字段别名

USE Students
SELECT StuID as '学号' FROM StuInfo

字段的计算

USE Students
SELECT StuSex+'-'+StuName as '性别-学员姓名' FROM StuInfo

使用ALL关键字返回全部记录

SELECT ALL StuID,StuName,StuSex FROM StuInfo

使用DISTINCT关键字过滤重复记录

USE Students
SELECT  StuID FROM StuMarks
SELECT DISTINCT StuID FROM StuMarks

使用TOP关键字仅显示前面若干条记录

USE Students
SELECT TOP 4 * FROM StuInfo
SELECT TOP 40 * FROM StuInfo

对查询结果筛选 where

1.比较运算符（大小比较，包括>,>=,=,<,<=,<>,!>,!<）

USE Students
SELECT * FROM StuInfo WHERE StuSex='男'
SELECT * FROM StuMarks WHERE Score >90
SELECT * FROM StuInfo WHERE StuName <> '李四'

2.范围运算符（表达式值是否在指定的范围）

• BETWEEN...AND...
• NOT BETWEEN...AND...

USE Students
SELECT * FROM StuMarks WHERE Score BETWEEN 80 AND 100
SELECT * FROM StuMarks WHERE Score NOT BETWEEN 80 AND 100

3.列表运算符（判断表达式是否为列表中的制定项）

• IN(项1，项2...)
• NOT IN(项1，项2....)

USE Students
SELECT * FROM StuInfo WHERE StuID IN(1,3,5)
SELECT * FROM StuInfo WHERE StuID NOT IN(1,3,5)

4.空值判断符（判断表达式是否为空）

• IS NULL
• IS NOT NULL

USE Students
SELECT * FROM StuMarks WHERE Score IS NULL
SELECT * FROM StuMarks WHERE Score IS NOT NULL

5.逻辑运算符（用于多条件的逻辑连接）

• AND
• OR

USE Students
SELECT * FROM StuMarks 
SELECT * FROM StuMarks WHERE Score > 80 AND Subject ='java'
SELECT * FROM StuMarks WHERE Score > 80 OR Subject ='html'
SELECT * FROM StuMarks WHERE StuID = 1 OR StuID =3 OR StuID=5

6.模式匹配符（判断是否与指定的字符通配格式相符）

• LIKE
• NOT LIKE

USE Students
SELECT * FROM StuMarks
SELECT * FROM StuInfo WHERE StuName LIKE '%张%' 
SELECT * FROM StuMarks WHERE Subject LIKE '[a-z]ava'
SELECT * FROM StuMarks WHERE Subject LIKE '[^i-z]tml'
SELECT * FROM StuInfo WHERE StuName LIKE '_六'
SELECT * FROM StuInfo WHERE StuName LIKE '%张%' 
SELECT * FROM StuInfo WHERE StuName NOT LIKE '%张%' 

对查询结果排序

1.使用ORDER BY 子句

USE Students
SELECT * FROM StuMarks
SELECT * FROM StuMarks WHERE Subject='java' ORDER BY Score ASC
SELECT * FROM StuMarks WHERE Subject='java' ORDER BY Score DESC

2.使用ORDER BY子句对多列进行排序

USE Students
SELECT * FROM StuMarks
SELECT * FROM StuMarks WHERE Subject='java' ORDER BY Score DESC,StuID DESC

3.ORDER BY子句与TOP关键字一起使用

USE Students
SELECT * FROM StuMarks
SELECT TOP 3 * FROM StuMarks WHERE Subject='java' ORDER BY Score DESC

对查询结果计算

1.使用SUM函数计算字段的累加和

USE Students
SELECT * FROM StuMarks
-- 查询java考试成绩的总和
SELECT SUM(Score) as java成绩的总和 FROM StuMarks WHERE Subject='java' 

2.使用AVG函数计算字段的平均值

USE Students
SELECT * FROM StuMarks
-- 查询java考试成绩的平均分
SELECT AVG(Score) as java成绩的平均分 FROM StuMarks WHERE Subject='java' 

3.使用MAX和MIN 函数计算字段的最大值和最小值

USE Students
SELECT * FROM StuMarks
-- 查询java考试成绩的最高分和最低分
SELECT MAX(Score) as java成绩的最高分,MIN(Score) as java成绩的最低分 FROM StuMarks WHERE Subject='java' 

4.使用COUNT函数统计记录行数

USE Students
SELECT * FROM StuInfo
-- 查询学员总人数
SELECT COUNT(*) as 学员总数 FROM StuInfo

SELECT * FROM StuMarks
--查询StuMarks中有几门科目
SELECT COUNT(DISTINCT Subject) as 科目数量 FROM StuMarks

对查询结果分组

1.使用GROUP BY 子句

USE Students
SELECT * FROM StuMarks
--分别统计参加每个科目考试的人数
SELECT Subject  as 科目,COUNT(*) AS 人数  FROM StuMarks GROUP BY subject

2.使用HAVING子句

USE Students
SELECT * FROM StuMarks
--统计平均分大于65分的科目
SELECT Subject  as 科目,AVG(Score) AS 平均分  FROM StuMarks GROUP BY subject HAVING AVG(Score)>65

INSERT语法

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);

INSERT INTO StuInfo(StuID,StuName,StuSex) VALUES(1,'张三'，‘男’)
INSERT INTO StuInfo VALUES (2,'王五','女')

UPDATE语法

UPDATE table_name 
SET column1 = value1, column2 = value2...., columnN = valueN 
WHERE [condition];

DELETE语法

DELETE FROM table_name 
WHERE [condition]; 

TRUNCATE 语法

TEUNCATE TABLE table_name