SQL Server学习笔记

2024-05-02 00:02:15 浏览数 (1)

哈喽,大家好呀!这里是码农后端。本篇复习一下SQL Server数据库的数据定义DDL和数据操纵DML的相关知识。

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),如下

代码语言:sql复制
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是关键字,为了区别开,将该字段加上中括号[]表示

代码语言:sql复制
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)最后创建一个员工表

代码语言:sql复制
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,如下

代码语言:sql复制
DROP TABLE IF EXISTS dbo.Course;

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

2、修改表结构

1)添加列:alter table 表名 add 新列名 数据类型

如为员工表增加一个年龄列,可以为空

代码语言:sql复制
ALTER TABLE dbo.Employee ADD age INT NULL; 

2)删除列:alter table 表名 drop column 列名

如删除员工表的年龄列

代码语言:sql复制
ALTER TABLE dbo.Employee DROP COLUMN age; 

3)修改列:alter table 表名 alter column 列名 数据类型

如修改员工表的员工姓名字段,类型不变,将其长度改为50

代码语言:sql复制
ALTER TABLE dbo.Employee ALTER COLUMN employeeName VARCHAR(50); 

4)维护约束(添加/删除约束)

4.1)添加约束:alter table 表名 add constraint 约束名 约束定义

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

代码语言:sql复制
--添加约束(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设置了主键自增,所以可以不用插入。

代码语言:sql复制
INSERT INTO dbo.Department(departmentName,departmentRemark) VALUES('研发部','个个都是技术大佬')
INSERT INTO dbo.Department(departmentName,departmentRemark) VALUES('市场部','开拓市场舍我其谁')

也可以简写如下(省略了字段名,容易出错,不推荐)

代码语言:sql复制
INSERT INTO dbo.Department VALUES('研发部','个个都是技术大佬')
INSERT INTO dbo.Department VALUES('市场部','开拓市场舍我其谁')

一次插入多行数据(union:将每行连接起来)

1)方法1:使用union

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

2)方法2:values后面批量

代码语言:sql复制
INSERT INTO dbo.Department(departmentName,departmentRemark) VALUES('aa','aaa'),('bb','bbb') 
2.2 职级表插入
代码语言:sql复制
INSERT INTO dbo.[Rank](rankName,rankRemark) 
VALUES('初级','1-3年初级职级'),('中级','3-5年中级职级'),('高级','10年以上高级职级')
2.3 员工表插入
代码语言:sql复制
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条件

代码语言:sql复制
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)批量删除

代码语言:sql复制
DELETE FROM dbo.Department WHERE departmentID IN (2,7,8)

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

代码语言:sql复制
DELETE FROM dbo.Employee WHERE departmentID = 6 AND employeeSalary >10000

3)删除的3种实现(drop,truncate,delete)

重点掌握,如下

代码语言:sql复制
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)

我正在参与2024腾讯技术创作特训营最新征文,快来和我瓜分大奖!

0 人点赞