SQL Server基础（一）

SQL Server基础（一）

SQL Server（关系型数据库）

一、数据库创建

1.Create Database

create database DBtest on
(
	name = ‘DBtest’, --logic name
	filename = ‘D:\DATA\DBtest.mdf’,--physical path and name
	size = 5MB, --the size of file
	filegrowth = 2MB --the growth of the file, size or percentage will be ok 
)
log on --log file
(
	name = ‘DBtest_log’, --logic name
	filename = ‘D:\DATA\DBtest_log.ldf’, —physical path and name
	size = 5MB, --the size of file
	filegrowth = 2MB --the growth of the file, size or percentage will be ok 
)
--Simplified case：
create database DBtest -- The parameters are default values.

2. Check if database exists

if exists (select * from sys.databases where name = ’DBtest’)
Drop database DBtest

3. Change database

use DBtest1

二、表的创建

1.Creat table

Attachment:
The type of String:
(1) char: 定长，例：char(10)：无论存储数据是否到了10字节，都要占用10字节；
(2) varchar: 变长，最多占用10个字节，真实占用字节数视情况而定；
(3) text: 长文本，效率较低；
(1)、(2)、(3)前面加’n’表示存储unicode字符，对中文友好，具体来说
varchar(100): 最大存储100个字母或50个汉字
nvarchar(100)： 存储100个字母或100个汉字

--部门表
create table Department
(
	--The ID of the department, data type, primary key, auto increment (initial value, step)
	DepartmentId int primary key identity (1,1),
	--The name of the department, data type(maximum number of characters), constraint
	DepartmentName nvarchar(50) not null,
	--The remark of the department, datatype
	DepartmentRemark text
)
--职级表
create table [Rank]
(
	RankId int primary key identity (1,1),	
	RankName nvarchar(50) not null,	
	RankRemark text
)
--员工表
create table employee
(
	EmployeeId int primary key identity (1,1),
	--The key of 部门表  外键 reference: 引用外键，检查添加的ID是否在部门表中存在
	DepartmentId int references Department(DepartmentId) not null,
	--The key of 职级表  外键: 将其他表的主键作为该表的外键
	RankId int references [Rank](RankId) not null，
	EmployeeName nvarchar(50) not null,
	--the default sex of Employee is male, check the input is legal or not
	EmployeeSex nvarchar(1) default('男')check(EmployeeSex='男' or EmployeeSex = '女'),
	EmployeeRemark text,
	--Type 'date' only stores year,month and day.'datetime' can store  year,month,day and Hours, minutes, seconds.
	EmployeeBirth datetime not null,
	--float浮点数有误差;decimal(12,2) represents the length of this data is 12, and 2 decimal places down
	EmployeeSalary decimal(12,2) check(EmployeeSalary>=1000 and EmployeeSalary <= 10000000) not null,
	--unique：the unique key
	EmployeePhoneNum varchar(20) unique not null,
	EmployeeAddress varchar(300),
	--smalldatetime：from 1900-01-01 to 2079-06-06
	EmployeeAddTime smalldatetime default(getdate())
)

Attachment:
The type of String:
(1) char: 定长，例：char(10)：无论存储数据是否到了10字节，都要占用10字节；
(2) varchar: 变长，最多占用10个字节，真实占用字节数视情况而定；
(3) text: 长文本，效率较低；
(1)、(2)、(3)前面加’n’表示存储unicode字符，对中文友好，具体来说
varchar(100): 最大存储100个字母或50个汉字
nvarchar(100)： 存储100个字母或100个汉字

2. Check if table exists

--type=’U’ represents the user-defined fields
if exists(select * from sys.objects where name = ‘Department’ and type = ‘U’) 
drop table Department 

三、数据增删改查

*以部门表为例
1.数据插入

insert into Department(DepartmentName,DepartmentRemark)
values('Marketing Department'，'...')
insert into Department(DepartmentName,DepartmentRemark)
values('Software Department'，'...')
insert into Department(DepartmentName,DepartmentRemark)
values('Planning Department'，'...')
--insert Multirow data
insert into Department(DepartmentName,DepartmentRemark)
select 'Testing Department'，'...' union
select 'Product Department'，'...' union
select 'Security Department'，'...'

2.修改表
修改表的结构：
（1）添加列

--         table name   new column name data type
alter table employee add employeeMail varchar(200)

（2）删除列

alter table employee drop column employeeMail

（3）修改列

--修改地址的varchar(300) to varchar(200)
alter table employee alter column EmployeeAddress varchar (200) 

3.维护约束（删除、添加）
（1）删除约束

--删除约束
--                                   约束名
alter table employee drop constraint CK__employee__Employ__693CA210

（2）添加约束

--添加check约束
alter table employee add constraint constraint1 check(EmployeeSalary>=1000 and EmployeeSalary <= 10000000)
--添加主键约束
--alter table 表名 add constraint 约束名 primary key(列名)
--添加唯一约束
--alter table 表名 add constraint 约束名 unique(列名)
--添加默认约束
--alter table 表名 add constraint 约束名 default 默认值 for (列名)
--添加外键约束
--alter table 表名 add constraint 约束名 foreign key(列名)
--references 关联表名(列名(主键))

4. 修改数据
(1) 单字段更新

update employee set EmployeeSalary = EmployeeSalary + 1000
--有条件地更新
update employee set EmployeeSalary = EmployeeSalary + 500 
where EmployeeId = 7
--Exmp2
update employee set EmployeeSalary = 6666 
where DepartmentId = 1 and EmployeeSalary <1000

(2)多字段更新

update employee set EmployeeSalary =  EmployeeSalary*2,EmployeeAddress = '北京'
where DepartmentId =1