维护SQL Server数据库的一些常用SQL
来源:互联网 发布:mac adobe premiere 编辑:程序博客网 时间:2024/05/17 02:33
维护SQL Server数据库的一些常用SQL
1.如何创建数据库
CREATE DATABASE student
2.如何删除数据库
DROP DATABASE student
3.如何备份数据库到磁盘文件
BACKUP DATABASE student to disk='c:/1234.bak'
4.如何从磁盘文件还原数据库
RESTORE DATABASE studnet FROM DISK = 'c:/1234.bak'
5.怎样创建表?
CREATE TABLE Students (
ID int IDENTITY ( 1, 1), --自增字段,基数1,步长1
StudentID char (4) NOT NULL ,
Name char (10) NOT NULL ,
Age int NULL ,
Birthday datetime NULL,
CONSTRAINT PK_Students PRIMARY KEY (StudentID) --设置主键
)
CREATE TABLE Subjects (
ID int IDENTITY ( 1, 1), --自增字段,基数1,步长1
ClassID char (4) NOT NULL ,
ClassName char (10) NOT NULL,
CONSTRAINT PK_Subjects PRIMARY KEY (ClassID) --设置主键
)
CREATE TABLE Scores (
ID int IDENTITY ( 1, 1), --自增字段,基数1,步长1
StudentID char (4) NOT NULL ,
ClassID char (4) NOT NULL ,
Score float NOT NULL,
CONSTRAINT FK_Scores_Students FOREIGN KEY (StudentID) REFERENCES Students(StudentID), --设置外键
CONSTRAINT FK_Scores_Subjects FOREIGN KEY (ClassID) REFERENCES Subjects(ClassID), --设置外键
CONSTRAINT PK_Scores PRIMARY KEY (StudentID,ClassID) --设置主键
)
6.怎样删除表?
DROP TABLE Students
7.怎样创建视图?
CREATE VIEW s_s_s
AS
SELECT Students.Name, Subjects.ClassName, Scores.Score
FROM Scores INNER JOIN
Students ON Scores.StudentID = Students.StudentID INNER JOIN
Subjects ON Scores.ClassID = Subjects.ClassID
8.怎样删除视图?
DROP VIEW s_s_s
9.如何创建存储过程?
CREATE PROCEDURE GetStudent
@age INT,
@birthday DATETIME
AS
SELECT *
FROM students
WHERE Age = @age AND Birthday = @birthday
GO
10.如何删除存储过程?
DROP PROCEDURE GetStudent
11.如何创建触发器?
CREATE TRIGGER reminder
ON Students
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO
12.如何删除触发器?
DROP TRIGGER reminder
13.如何创建索引?
CREATE UNIQUE INDEX IX_Students ON Students (Name)
14.如何删除索引?
DROP INDEX Students.IX_Students
15.怎样给表添加字段?
ALTER TABLE Students ADD Address varchar (50) NULL
16.怎样删除表中某个字段?
ALTER TABLE Students DROP COLUMN Address
17.如何设置列的标识属性?
没找到办法
18.如何去掉列的标识属性?
没有找到好的方法,只能是先添加一列,然后把标识列的值更新到新加入的列,删除标识列,再用与标识列相同的名字类型添加一列,用前面加入的列更新该列.如果该标识列是其他表的外键,还要先删除外键约束,很麻烦.谁有好的办法,还请告诉我.
19.如何重设标识列的标识种子?
DBCC CHECKIDENT (Student, RESEED, 1)
20.怎样给表加上主键?
ALTER TABLE Scores ADD CONSTRAINT PK_Scores PRIMARY KEY (StudentID,ClassID)
21.怎样删除表的主键?
ALTER TABLE Scores DROP CONSTRAINT PK_Scores
22.怎样给表添加一个外键?
ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Students FOREIGN KEY (StudentID) REFERENCES Students (StudentID) ON DELETE CASCADE
23.怎样删除表的一个外键?
ALTER TABLE Scores DROP CONSTRAINT FK_Scores_Students
24.怎样给字段加上CHECK约束?
ALTER TABLE Students ADD CONSTRAINT CK_Students CHECK (Age > 0)
25.怎样去掉字段上的CHECK约束?
ALTER TABLE Students DROP CONSTRAINT CK_Students
26.怎样给字段设置默认值?
ALTER TABLE Students ADD CONSTRAINT DF_Students_Age DEFAULT (18) FOR Age
27.怎样移去字段的默认值?
ALTER TABLE Students DROP CONSTRAINT DF_Students_Age
28.修改字段的类型及非空约束
ALTER TABLE Students ALTER COLUMN Age char (10) null
ALTER TABLE Students ALTER COLUMN Age int not null
1.如何创建数据库
CREATE DATABASE student
2.如何删除数据库
DROP DATABASE student
3.如何备份数据库到磁盘文件
BACKUP DATABASE student to disk='c:/1234.bak'
4.如何从磁盘文件还原数据库
RESTORE DATABASE studnet FROM DISK = 'c:/1234.bak'
5.怎样创建表?
CREATE TABLE Students (
ID int IDENTITY ( 1, 1), --自增字段,基数1,步长1
StudentID char (4) NOT NULL ,
Name char (10) NOT NULL ,
Age int NULL ,
Birthday datetime NULL,
CONSTRAINT PK_Students PRIMARY KEY (StudentID) --设置主键
)
CREATE TABLE Subjects (
ID int IDENTITY ( 1, 1), --自增字段,基数1,步长1
ClassID char (4) NOT NULL ,
ClassName char (10) NOT NULL,
CONSTRAINT PK_Subjects PRIMARY KEY (ClassID) --设置主键
)
CREATE TABLE Scores (
ID int IDENTITY ( 1, 1), --自增字段,基数1,步长1
StudentID char (4) NOT NULL ,
ClassID char (4) NOT NULL ,
Score float NOT NULL,
CONSTRAINT FK_Scores_Students FOREIGN KEY (StudentID) REFERENCES Students(StudentID), --设置外键
CONSTRAINT FK_Scores_Subjects FOREIGN KEY (ClassID) REFERENCES Subjects(ClassID), --设置外键
CONSTRAINT PK_Scores PRIMARY KEY (StudentID,ClassID) --设置主键
)
6.怎样删除表?
DROP TABLE Students
7.怎样创建视图?
CREATE VIEW s_s_s
AS
SELECT Students.Name, Subjects.ClassName, Scores.Score
FROM Scores INNER JOIN
Students ON Scores.StudentID = Students.StudentID INNER JOIN
Subjects ON Scores.ClassID = Subjects.ClassID
8.怎样删除视图?
DROP VIEW s_s_s
9.如何创建存储过程?
CREATE PROCEDURE GetStudent
@age INT,
@birthday DATETIME
AS
SELECT *
FROM students
WHERE Age = @age AND Birthday = @birthday
GO
10.如何删除存储过程?
DROP PROCEDURE GetStudent
11.如何创建触发器?
CREATE TRIGGER reminder
ON Students
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO
12.如何删除触发器?
DROP TRIGGER reminder
13.如何创建索引?
CREATE UNIQUE INDEX IX_Students ON Students (Name)
14.如何删除索引?
DROP INDEX Students.IX_Students
15.怎样给表添加字段?
ALTER TABLE Students ADD Address varchar (50) NULL
16.怎样删除表中某个字段?
ALTER TABLE Students DROP COLUMN Address
17.如何设置列的标识属性?
没找到办法
18.如何去掉列的标识属性?
没有找到好的方法,只能是先添加一列,然后把标识列的值更新到新加入的列,删除标识列,再用与标识列相同的名字类型添加一列,用前面加入的列更新该列.如果该标识列是其他表的外键,还要先删除外键约束,很麻烦.谁有好的办法,还请告诉我.
19.如何重设标识列的标识种子?
DBCC CHECKIDENT (Student, RESEED, 1)
20.怎样给表加上主键?
ALTER TABLE Scores ADD CONSTRAINT PK_Scores PRIMARY KEY (StudentID,ClassID)
21.怎样删除表的主键?
ALTER TABLE Scores DROP CONSTRAINT PK_Scores
22.怎样给表添加一个外键?
ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Students FOREIGN KEY (StudentID) REFERENCES Students (StudentID) ON DELETE CASCADE
23.怎样删除表的一个外键?
ALTER TABLE Scores DROP CONSTRAINT FK_Scores_Students
24.怎样给字段加上CHECK约束?
ALTER TABLE Students ADD CONSTRAINT CK_Students CHECK (Age > 0)
25.怎样去掉字段上的CHECK约束?
ALTER TABLE Students DROP CONSTRAINT CK_Students
26.怎样给字段设置默认值?
ALTER TABLE Students ADD CONSTRAINT DF_Students_Age DEFAULT (18) FOR Age
27.怎样移去字段的默认值?
ALTER TABLE Students DROP CONSTRAINT DF_Students_Age
28.修改字段的类型及非空约束
ALTER TABLE Students ALTER COLUMN Age char (10) null
ALTER TABLE Students ALTER COLUMN Age int not null
- 维护SQL Server数据库的一些常用SQL
- 数据库设计及维护的一些心得 -- SQL Server篇
- 常用的一些Oracle维护SQL
- sql server的一些常用 sql 语句
- 使用sql维护数据库(SQL SERVER)
- Sql Server 数据库结构的常用Sql
- 一些常用的SQL(05版以上)数据库维护脚本(来自他人)
- SQL Server数据库常用SQL
- SQL Server索引的维护
- SQL Server 数据库维护计划 日志作业失败的解决
- SQL Server数据库维护计划的实施步骤(转帖)
- SQL Server-高效维护数据库的关键技巧
- SQL Server的一些常用語句
- Oracle数据库维护常用的SQL代码示例
- Oracle数据库维护常用的SQL代码示例
- 解SQL Server“数据库维护计划”
- 维护Sql Server数据库之:日志清理
- 解SQL Server“数据库维护计划
- 都不是什么好鸟
- 比较简单实用的命令行运行java程序的脚本.
- 俺是这样修改ms06-040的
- WEB项目总结-完全分页组件
- 从数据表中取出第n条到第m条的记录的方法
- 维护SQL Server数据库的一些常用SQL
- Request 对象
- JAVA域对象持久化技术的比较
- 342~345
- 346~355
- 356~357
- 数据库操作问题之select
- Book Review about Head First Design Pattern 7
- 当年明月致新浪朋友的信