SQL Server学习日志（3）

哈喽，大家好呀！这里是码农后端。

1、数据定义

1）SQL主要动词：

SQL功能	动词
数据定义	CREATE(创建)，DROP(删除)，ALTER(修改)
数据查询	SELECT
数据操纵	INSERT(插入)，UPDATE(更新)，DELETE(删除)
数据控制	Grant(授权)，REVOKE(收回权限)，DENY(撤销并拒绝权限)

2）SQL程序语言有4种类型，如下

DDL（Data Definition Language）：数据定义语言
DQL（Data Query Language）：数据查询语言
DML（Data Manipulation Language）：数据操纵语言
DCL（Data Control Language）：数据控制语言

一、DDL(数据定义语言)

1、表的定义与删除

1.1 定义表

新建一个DB数据库，在该数据库下建表。
1）首先创建一个部门表(Department)，如下

USE DB; --切换当前数据库为DB
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Department' AND type = 'U')
    DROP TABLE dbo.Department;
--创建表
CREATE TABLE Department --部门表
(
	departmentID INT PRIMARY KEY IDENTITY(1,1), --IDENTITY(1,1)代表主键自增，从1开始步长为1自增长
	departmentName VARCHAR(50) NOT NULL,
	departmentRemark TEXT NULL --部门描述，可以为空，test为长文本类型
)

其中，第2句代码含义是判断数据库中是否已存在名为’Department’的对象，如果存在，则删除，因为sql server数据库中不允许创建重复的表；type=’U’判断对象类型为用户定义表类型

IDENTITY(1,1)代表主键自增，从1开始，步长为1自增长

2）再创建一个职级表([Rank])

由于rank是关键字，为了区别开，将该字段加上中括号[]表示

CREATE TABLE [Rank] --职级表
(
	rankID INT PRIMARY KEY IDENTITY(1,1), --IDENTITY(1,1)代表主键自增，从1开始步长为1自增长
	rankName VARCHAR(50) NOT NULL,
	rankRemark TEXT NULL --职级描述，可以为空，test为长文本类型
)

3）最后创建一个员工表

CREATE TABLE Employee --员工表
(
	employeeId INT PRIMARY KEY IDENTITY(1,1), --IDENTITY(1,1)代表主键自增，从1开始步长为1自增长
	employeeName VARCHAR(20) NOT NULL,
	employeeSex CHAR(2) DEFAULT('男') CHECK(employeeSex = '男' OR employeeSex = '女') NOT NULL, --CHECK限定列的取值范围
	employeeBirth DATETIME NOT NULL, --出生年月
	employeeSalary DECIMAL(12,2) CHECK(employeeSalary >= 1000 AND employeeSalary <= 1000000) NOT NULL, --总长度为12，小数点后两位
	employeePhone VARCHAR(20) UNIQUE NOT NULL, --每个人的电话号码是唯一的
	employeeAddress VARCHAR(50),
	employeeAddTime DATETIME DEFAULT(GETDATE()), --添加时间
	-- 添加部门外键约束，部门（引用外键）。外键：将其他表的主键作为字段
	departmentID INT REFERENCES dbo.Department(departmentID) NOT NULL, 
	-- 添加职级外键约束，职级（引用外键）
	rankID INT REFERENCES dbo.[Rank](rankID) NOT NULL 
)

简单分析，上表使用到的约束有：
a、使用CHECK约束，限制员工性别列的取值范围为’男’或’女’；限制员工工资列的取值范围在1000-1000000之间(包含)
b、使用UNIQUE约束，限制员工号码唯一不重复
c、NOT NULL非空约束
d、PRIMARY KEY主键约束
e、使用DEFAULT默认约束，指定添加员工时间的默认值为当前时间
f、外键约束
可以说，基本上的约束都使用到了。

1.2 删除表

使用drop，如下

1	DROP TABLE IF EXISTS dbo.Course;

表示如果当前数据库中存在Course表，则将其删除

2、修改表结构

1）添加列：alter table 表名 add 新列名数据类型
如为员工表增加一个年龄列，可以为空

1	ALTER TABLE dbo.Employee ADD age INT NULL;

2）删除列：alter table 表名 drop column 列名
如删除员工表的年龄列

1	ALTER TABLE dbo.Employee DROP COLUMN age;

3）修改列：alter table 表名 alter column 列名数据类型
如修改员工表的员工姓名字段，类型不变，将其长度改为50

1	ALTER TABLE dbo.Employee ALTER COLUMN employeeName VARCHAR(50);

4）维护约束（添加/删除约束）

4.1）添加约束：alter table 表名 add constraint 约束名约束定义

如下列举了一些常见约束（如check、主键等）的添加

--添加约束(check约束) 
--ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(表达式)
--添加约束(主键)
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.2）删除约束：alter table 表名 drop constraint 约束名

注：constraint是约束的意思，用其定义约束

二、DML(数据操作语言)

建完数据库和表后，就可以往表中插入数据了。

注：设置了主键自增或默认值的字段可以不用手动插入

1、插入数据

基本语法：insert into 表名(列名表) values(值列表)

以下为往各表中插入一些测试数据。

2.1 部门表插入

由于departmentID设置了主键自增，所以可以不用插入。

1
2

INSERT INTO dbo.Department(departmentName,departmentRemark) VALUES('研发部','个个都是技术大佬')
INSERT INTO dbo.Department(departmentName,departmentRemark) VALUES('市场部','开拓市场舍我其谁')

也可以简写如下（省略了字段名，容易出错，不推荐）

1 2	INSERT INTO dbo.Department VALUES('研发部','个个都是技术大佬') INSERT INTO dbo.Department VALUES('市场部','开拓市场舍我其谁')

一次插入多行数据（union：将每行连接起来）
1）方法1：使用union

INSERT INTO dbo.Department(departmentName,departmentRemark) 
SELECT '测试部','负责软硬件的测试' UNION 
SELECT '人事部','负责人员考核任用' UNION
SELECT '销售部','负责销售公司产品'

2）方法2：values后面批量

1	INSERT INTO dbo.Department(departmentName,departmentRemark) VALUES('aa','aaa'),('bb','bbb')

2.2 职级表插入

1 2	INSERT INTO dbo.[Rank](rankName,rankRemark) VALUES('初级','1-3年初级职级'),('中级','3-5年中级职级'),('高级','10年以上高级职级')

2.3 员工表插入

INSERT INTO dbo.Employee
(
    employeeName,
    employeeSex,
    employeeBirth,
    employeeSalary,
    employeePhone,
    employeeAddress,
    departmentID,
    rankID
)
VALUES
('张三', '男','2002-4-15',5500,'18702509838','南昌',5,1),
('李四', '女','2003-3-15',6000,'18702502233','广州',1,3),
('王五', '男','2001-5-15',5000,'18702509866','深圳',6,2);

INSERT INTO dbo.Employee(employeeName,employeeSex,employeeBirth,employeeSalary,employeePhone,employeeAddress,departmentID,rankID) 
VALUES('赵六', '女','2002-5-15',7000,'18702509888','南昌',6,2)
INSERT INTO dbo.Employee(employeeName,employeeSex,employeeBirth,employeeSalary,employeePhone,employeeAddress,departmentID,rankID)
VALUES('王八', '女','2002-9-15',7000,'18702506666','广州',1,2)
INSERT INTO dbo.Employee(employeeName,employeeSex,employeeBirth,employeeSalary,employeePhone,employeeAddress,departmentID,rankID)
VALUES('小美', '女','2003-1-15',9000,'18700006666','深圳',3,1)

2、修改数据

基本语法：update 表名 set 字段1 = 值1,字段2 = 值2 where条件

1
2
3

UPDATE dbo.Employee SET employeeSalary = employeeSalary+1000 --工资调整，每个员工加薪1000元
UPDATE dbo.Employee SET employeeSalary = employeeSalary+1000 WHERE employeeId = 10 --针对个人调薪
UPDATE dbo.Employee SET employeeSalary = employeeSalary*2,employeeAddress = '上海' WHERE employeeName = '王五'

3、删除数据

基本语法：delete from 表名 where条件
1）批量删除

1	DELETE FROM dbo.Department WHERE departmentID IN (2,7,8)

2）删除销售部（部门编号6）工资大于10000的员工

1	DELETE FROM dbo.Employee WHERE departmentID = 6 AND employeeSalary >10000

3）删除的3种实现（drop,truncate,delete）
重点掌握，如下

1
2
3

drop table dbo.Employee --删除表对象，比较危险，如要删除建议先做好备份；推荐以下两种。
truncate table dbo.Employee --删除数据（清空数据），表对象即表结构依然存在
DELETE from dbo.Department --删除所有数据，表对象即表结构依然存在

truncate和delete区别：
（1）truncate清空所有数据,不能有条件, delete可以删除所有数据也可以带条件,删除符合条件的数据
（2）自动编号:
假设表中自动编号为1, 2, 3, 4, 5

1、使用truncate清空数据之后在添加数据，编号仍然是1, 2, 3, 4, 5
2、使用delete删除数据，删除的自动编号将永远不存在了（即使用delete删除了所有数据之后再添加数据，编号变成了6, 7, 8, 9, 10）