操作数据库

PPG007 ... 2024-3-29 About 2 min

# 操作数据库

创建数据库：

CREATE DATABASE testDB;

删除数据库：

DROP DATABASE testDB;

创建表：

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

1
2
3
4
5
6
7

根据查询结果创建表：

CREATE TABLE testTable AS
    (
        SELECT CustomerName, ContactName FROM customers
    )

1
2
3
4

删除表：

DROP TABLE testTable

删除表中的数据但是保留表：

TRUNCATE TABLE testTable

修改表：

-- 增加一列
ALTER TABLE testTable ADD newColumn int;
-- 删除一列
ALTER TABLE testTable DROP COLUMN newColumn
-- 重命名列
ALTER TABLE testTable RENAME COLUMN productID to id
-- 修改数据类型
ALTER TABLE testTable MODIFY Unit text;
-- 重命名表
ALTER TABLE testDB RENAME testTable

1
2
3
4
5
6
7
8
9
10

字段限制：

-- 不为 NULL
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);
ALTER TABLE Persons MODIFY COLUMN Age int NOT NULL
-- 字段唯一
CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);
ALTER TABLE Persons
ADD UNIQUE (ID);
-- 多字段唯一
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
-- 主键
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
-- 多字段主键
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
-- 删除主键
ALTER TABLE Persons
DROP PRIMARY KEY;
-- 外键
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
-- 命名外键
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
-- 删除外键
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
-- check
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);
ALTER TABLE Persons
ADD CHECK (Age>=18);
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
-- 删除 check
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
-- 默认值
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);
-- 删除默认值
ALTER TABLE Persons
ALTER City DROP DEFAULT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

创建索引：

CREATE INDEX idx_lastname
ON Persons (LastName);
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
-- 唯一索引
CREATE UNIQUE INDEX idx_lastname
ON Persons (LastName);

1
2
3
4
5
6
7

删除索引：

ALTER TABLE Persons
DROP INDEX idx_lastname;

1
2

字段自增：

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

1
2
3
4
5
6
7

设置自增步长：

ALTER TABLE Persons AUTO_INCREMENT=100;

MySQL 支持四种日期类型：

DATE：YYYY-MM-DD
DATETIME：YYYY-MM-DD HH:MM:SS
TIMESTAMP：YYYY-MM-DD HH:MM:SS
YEAR：YYYY 或 YY。

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

创建视图：

CREATE VIEW BrazilCustomers AS
(SELECT CustomerName, ContactName
FROM customers
WHERE Country = 'Brazil')

1
2
3
4

修改视图：

CREATE OR REPLACE VIEW BrazilCustomers AS
(SELECT CustomerName, ContactName, City
FROM customers
WHERE Country = 'Brazil')

1
2
3
4

删除视图：

DROP VIEW BrazilCustomers

删除