SQL INSERT函数

结构化查询语言中的INSERT字符串函数允许您在给定字符串的固定位置插入字符串或字符。

INSERT字符串函数的语法

语法1： 此语法使用SQL表的列名和INSERT函数：

SELECT INSERT(Column_Name, Position, Number, Sub-string) AS Alias_Name FROM Table_Name;

在这个语法中，我们必须指定要修改其值的列的名称。

语法2: 此语法使用给定的字符串和INSERT函数。

SELECT INSERT(String, Position, Number, Sub-String) AS Alias_Name;

INSERT字符串函数的示例

示例1： 下面的SELECT查询将’Tpoint’字符串插入到’Java Excel’字符串的第五个位置：

SELECT INSERT('Java Excel', 5, 6, 'Tpoint') AS Tpoint_5_6;

输出:

Tpoint_5_6  
---  
Java Tpoint 

示例2： 下面的SELECT查询将字符’C’插入到给定单词的第一个位置：

SELECT INSERT('ongratulations', 1, 1, 'C') AS C_1_1;

输出:

C_1_1  
---  
Cngratulations  

示例3： 下面的SELECT查询将’Capital’子字符串插入到’of’单词之前：

SELECT INSERT('New Delhi is the of India', 17, 8, 'Capital ') AS Capital_17_8;

输出:

Capital_17_7  
---  
New Delhi is the Capital  

示例4： 下面的SELECT查询在”Java Excel”字符串的第五个位置插入了”Tpoint”字符串。

SELECT INSERT('JavaTpoint.com', 1, 10, 'Example') AS Example_1_10;

输出:

Capital_17_7  
---  
Example.com  

示例5：此示例使用结构化查询语言（SQL）中的INSERT函数与表一起使用。

现在，我们在SQL中创建新表，以帮助理解每个字符串函数。在SQL数据库中创建新表的语法如下：

CREATE TABLE table_name
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
...  

Last_Column_of_table Data Type (character_size of the Last column)
);  

下面的CREATE语句创建了Worker_Grade表：

CREATE TABLE Worker_Grade
(
Worder_ID Varchar(50) PRIMARY KEY,  
First_Name VARCHAR (100),  
Last_Name VARCHAR (100), 
First_City Varchar(120),
Second_City Varchar(120),
New_City Varchar(120),
Attendance_Remarks Varchar(50), 
Worker_Salary Varchar(50), 
Grade Varchar (80)
);

以下的INSERT查询将工人的记录插入到Worker_Grade表中，包括等级、工资和备注：

INSERT INTO Worker_Grade (Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade) VALUES (10, Aman, Sharma, Lucknow Chandigarh, Ghaziabad, 88, 9598, A2);

INSERT INTO Worker_Grade 
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade) 
VALUES ( 02, Vishal, Sharma, Chandigarh, Ghaziabad, Delhi, 95, 8200, A1 );

INSERT INTO Worker_Grade 
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade) 
VALUES (07, Raj, Gupta, Delhi, Ghaziabad, Lucknow, 91, 9512, A1);

INSERT INTO Worker_Grade 
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade)  
VALUES (04, Yash, Singhania, Ghaziabad, Delhi, Lucknow, 85, 8225, A2);

INSERT INTO Worker_Grade 
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade) 
VALUES (11, Vinay, Roy, Delhi, Kanpur, Ghaziabad, 95, 9712, A1);

INSERT INTO Worker_Grade 
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade) 
VALUES (16, Manoj, Gupta, Ghaziabad, Meerut, Chandigarh, 95, 9025, B1);


INSERT INTO Worker_Grade 
(Worder_ID, First_Name, Last_Name, First_City, Second_City, New_City, Attendance_Remarks, Worker_Salary, Grade) 
VALUES (19, Ram, Gupta, Lucknow, Ghaziabad, Chandigarh, 89, 9512, A2);

以下SELECT语句显示了上述 Worker_Grade 表中插入的记录：

SELECT * FROM Worker_Grade;