添加列

ALTER TABLE 表名 ADD 新列名 数据类型;

删除列

ALTER TABLE 表名 DROP COLUMN 列名;

删除邮箱列（修正拼写错误：colunm → COLUMN）

ALTER TABLE People DROP COLUMN PeopleMail;

修改列

ALTER TABLE 表名 ALTER COLUMN 列名 数据类型;

修改地址 varchar(300) 为 varchar(200)（修正拼写错误：colunmn → COLUMN）

ALTER TABLE People ALTER COLUMN PeopleAddress varchar(200);

维护约束(删除，添加)

删除约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

删除月薪约束（修正拼写错误：constralint → CONSTRAINT）

ALTER TABLE People DROP CONSTRAINT CK_People_Salary;

添加约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (表达式);

添加工资约束在 1000-100000（修正符号错误：< = → <=）

ALTER TABLE People ADD CONSTRAINT CK_People_Salary CHECK (PeopleSalary >= 1000 AND PeopleSalary <= 100000);

添加约束(主键)

ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (列名);

添加约束(唯一)

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (列名);

添加日志文件（修正路径引号和语法）

ALTER DATABASE xs
MODIFY FILE
(
    NAME = 'XS_LOG',
    FILENAME = 'D:\code\XS_LOG.ldf',
    SIZE = 5MB,
    MAXSIZE = 10MB,
    FILEGROWTH = 1MB
);
GO

ALTER DATABASE xs
ADD FILEGROUP MyGroup;
GO

sp_helpfilegroup;
GO

查找月薪在 1000-2000 之间的员工信息（修正范围和符号错误）

SELECT * FROM People WHERE PeopleSalary >= 1000 AND PeopleSalary <= 2000;

查找员工在武汉或者北京的员工信息（修正拼写错误和中文逗号）

SELECT * FROM People WHERE PeopleAddress = ‘武汉’ OR PeopleAddress = ‘北京’;

或者

SELECT * FROM People WHERE PeopleAddress IN (‘武汉’, ‘北京’);

排序

根据工资排序（默认升序，可省略 ASC）

SELECT * FROM People ORDER BY PeopleSalary ASC; SELECT * FROM People ORDER BY PeopleSalary DESC;

根据名字长度排序

SELECT * FROM People ORDER BY LEN(PeopleName) DESC;

查询出工资最高的 5 个人信息

SELECT TOP 5 * FROM People ORDER BY PeopleSalary DESC;

查询地址没有填写信息的员工（修正表名错误：frome → FROM）

SELECT * FROM People WHERE PeopleAddress IS NULL;

查询地址已经填写好的员工信息（修正表名错误：frome → FROM）

SELECT * FROM People WHERE PeopleAddress IS NOT NULL;

查询 80 后的人（修正拼写错误和空格问题）

SELECT * FROM People WHERE PeopleBirth >= ‘1980-01-01’ AND PeopleBirth <= ‘1989-12-31’; SELECT * FROM People WHERE PeopleBirth BETWEEN ‘1980-01-01’ AND ‘1989-12-31’; SELECT * FROM People WHERE YEAR(PeopleBirth) BETWEEN 1980 AND 1989;

查询工资比赵云高的

SELECT * FROM People WHERE PeopleSalary > (SELECT PeopleSalary FROM People WHERE PeopleName = ‘赵云’);

查询出和赵云在一个城市的人

SELECT * FROM People WHERE PeopleAddress = (SELECT PeopleAddress FROM People WHERE PeopleName = ‘赵云’);