哈喽,大家好呀!这里是码农后端。本篇复习一下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)
代码语言:sql复制由于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)最后创建一个员工表
代码语言: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条件
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腾讯技术创作特训营最新征文,快来和我瓜分大奖!