你的位置:首页 > 数据库

[数据库]SSMS For Beginner Part 1 to 10


Part 1 Connecting to SQL Server using SSMS

note that,SSMS is a client tool and not the server by itself,it is a developer machines connects to SQL Server.

Part 2 Creating altering and dropping a database

you cannot drop a database , if it is currently in use you will get an error stating. so , if other users are connected, you need to put the database in single user node and then drop the database.system database cannot be dropped.

Part 3 Creating and working with tables

for example:add a foreignkey relation.

table:Student:ID,GenderID;

        Gender:ID,StudentID;

Alter table Student Add Constraint Student_GenderID_FKFOREIGN KEY (GenderID) references Gender (ID)

Syntax :

Alter table 外键表名 add constraint 外键约束名

FOREIGN KEY (外键名) references 主表名 (主键名)

Note:Foreign Keys are used to enforce(强制) database integrity(完整) . In layman's terms(一般来说), A foreign key in one table points to a primary key in another table. The foreign key constraint prevents invalid data form being inserted into the foreign key column. The values that you enter into the foreign key column, has to be one of the values contained in the table it points to.

Part 4 Adding a default constraint

Altering an existing column to add a default constraint:

ALTER TABLE 表名

ADD CONSTRAINT 约束名

DEFAULT 默认值 FOR 列名

Adding a new column with default value, to an existing table:

ALTER TABLE 表名

ADD 列名 数据类型 是否允许null

CONSTRAINT 约束名 DEFAULT 默认值

Dropping a constraint:

ALTER TABLE 表名

DROP CONSTRAINT 约束名

Part 5 Cascading referential integrity constraint

 Part 6 Adding a check constraint

Part 7 Identity Column in SQL Server

Part 8 How to get the last generated identity column value in SQL Server

Part 9 Unique key constraint

Part 10 Select statement in sql server

Select specific or all columns

select * from 表名

select 列名,列名... from 表名

Distinct rows

select distinct 列名 from 表名

Filtering with where clause(子句)

Wild Cards in SQL Server

Joining multiple conditions using AND and OR operators

Sorting rows using order by

Selecting top n or top n percentage of rows