sql通讯录数据库

-- 创建联系人表
CREATE TABLE Contacts (
    ContactID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    PhoneNumber VARCHAR(15),
    Email VARCHAR(100)
);

-- 创建分组表
CREATE TABLE Groups (
    GroupID INT PRIMARY KEY AUTO_INCREMENT,
    GroupName VARCHAR(50) NOT NULL,
    Description TEXT
);

-- 创建联系人分组关联表
CREATE TABLE Contact_Groups (
    ContactID INT,
    GroupID INT,
    PRIMARY KEY (ContactID, GroupID),
    FOREIGN KEY (ContactID) REFERENCES Contacts(ContactID),
    FOREIGN KEY (GroupID) REFERENCES Groups(GroupID)
);

-- 插入联系人数据
INSERT INTO Contacts (Name, PhoneNumber, Email) VALUES
('Alice', '1234567890', 'alice@example.com'),
('Bob', '0987654321', 'bob@example.com');

-- 插入分组数据
INSERT INTO Groups (GroupName, Description) VALUES
('Family', 'Family members'),
('Friends', 'Close friends');

-- 插入联系人分组关联数据
INSERT INTO Contact_Groups (ContactID, GroupID) VALUES
(1, 1), -- Alice 属于 Family 分组
(2, 2); -- Bob 属于 Friends 分组

-- 查询所有联系人
SELECT * FROM Contacts;

-- 查询特定分组下的联系人
SELECT c.*
FROM Contacts c
JOIN Contact_Groups cg ON c.ContactID = cg.ContactID
JOIN Groups g ON cg.GroupID = g.GroupID
WHERE g.GroupName = 'Family';

-- 更新联系人电话号码
UPDATE Contacts
SET PhoneNumber = '1112223333'
WHERE Name = 'Alice';

-- 更新分组名称
UPDATE Groups
SET GroupName = 'Relatives'
WHERE GroupName = 'Family';

-- 删除联系人
DELETE FROM Contacts
WHERE Name = 'Bob';

-- 删除分组
DELETE FROM Groups
WHERE GroupName = 'Friends';

-- 删除联系人分组关联
DELETE FROM Contact_Groups
WHERE ContactID = 1 AND GroupID = 1;