SQL SPACE 函数
一、SQL SPACE 函数 语法
SQL 的 SPACE 函数 在两个字符或一组字符串之间添加给定数量的空格。
语法1:此语法使用带有 SQL 表列名的 SPACE 函数:
SELECT Column_Name1 + SPACE(Number) + Column_Name2 AS Alias_Name FROM Table_Name As Alias_Name ;
在语法中,我们必须指定要在其上使用 SPACE 函数的那些列的名称。
语法2:此语法使用带有两个或多个字符串的 SPACE 函数:
SELECT String1 + SPACE(Number) + String2 + SPACE(Number) + …… StringN As Alias_Name;
语法3:此语法使用带有两个或多个单字符的 SPACE 函数:
SELECT Character1 + SPACE(Number) + Character2 + SPACE(Number) + …… CharacterN As Alias_Name ;
语法4:我们也可以使用给定的语法来指定空格:
SELECT SPACE(Number) As Alias_Name;
二、SQL SPACE 函数 示例
示例 1:以下 SELECT 查询在结果中显示 15 个空格:
SELECT SPACE(15) AS 15_Space;
输出结果为:
15_Spaces |
' ' |
示例 2:以下 SELECT 查询在 Yiidian Word 之后添加 10 个空格:
SELECT 'Yiidian' + SPACE(10) AS 10_Space;
输出结果为:
'Yiidian ' |
10 |
示例 3:以下 SELECT 查询在两个单个字符之间添加 20 个空格:
SELECT 'S' + SPACE(20) + 'K';
输出结果为:
20_Spaces |
'S K' |
示例 4:以下 SELECT 查询在多个给定字符串之间添加 15 个空格:
SELECT 'New' + SPACE(15) + 'Delhi' + SPACE(15) + 'is' + SPACE(15) + 'the' + SPACE(15) + 'Capital' + SPACE(15) + 'of' + SPACE(15) + 'India';
输出结果为:
20_Spaces |
'New' 'Delhi' 'is' 'the' 'Capital' 'of' 'India' |
示例 5:此示例将 SPACE 函数与 SQL 表一起使用
在此示例中,我们将创建一个新表,以帮助执行带有字符串列的空间函数。
在 SQL 数据库中创建新表的语法如下:
CREATE TABLE table_name
(
First_Column_of_SQLtable Data Type (character_size of First Column),
Second_Column_of_SQLtable Data Type (character_size of the Second column ),
Third_Column_of_SQLtable Data Type (character_size of the Third column),
...
Last_Column_of_SQLtable Data Type (character_size of the Last column)
);
以下 CREATE 语句创建Student_Grade表:
CREATE TABLE Student_Grade
(
Roll_No INT PRIMARY KEY,
First_Name VARCHAR (100),
Last_Name VARCHAR (100),
First_City Varchar(120),
Second_City Varchar(120),
New_City Varchar(120),
Hindi_Marks INT,
Maths_Marks INT,
Grade Varchar (80)
);
下面的 INSERT 语句在Student_Grade表中插入学生的成绩和分数记录:
INSERT INTO Student_Grade (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade) VALUES (10, Aman, Sharma, Lucknow Chandigarh, Ghaziabad, 88, 95, A2);
INSERT INTO Student_Grade
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)
VALUES ( 02, Vishal, Sharma, Chandigarh, Ghaziabad, Delhi, 95, 82, A1 );
INSERT INTO Student_Grade
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)
VALUES (07, Raj, Gupta, Delhi, Ghaziabad, Lucknow, 91, 95, A1);
INSERT INTO Student_Grade
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)
VALUES (04, Yash, Singhania, Ghaziabad, Delhi, Lucknow, 85, 82, A2);
INSERT INTO Student_Grade
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)
VALUES (11, Vinay, Roy, Delhi, Kanpur, Ghaziabad, 95, 97, A1);
INSERT INTO Student_Grade
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)
VALUES (16, Manoj, Gupta, Ghaziabad, Meerut, Chandigarh, 95, 90, B1);
INSERT INTO Student_Grade
(Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)
VALUES (19, Ram, Gupta, Lucknow, Ghaziabad, Chandigarh, 89, 95, A2);
以下 SELECT 语句显示上述Student_Grade表的插入记录:
SELECT * FROM Student_Grade;
输出结果为:
Roll_No | First_Name | Last_Name | First_City | Second_City | New_City | Hindi_Marks | Maths_Marks | Grade |
---|---|---|---|---|---|---|---|---|
10 | Aman | Sharma | Lucknow | Chandigarh | Ghaziabad | 88 | 95 | A2 |
02 | Vishal | Sharma | Chandigarh | Ghaziabad | Ghaziabad | 95 | 82 | A1 |
07 | Raj | Gupta | Delhi | Ghaziabad | Lucknow | 91 | 95 | A1 |
04 | Yash | Singhania | Ghaziabad | Delhi | Lucknow | 85 | 82 | A2 |
11 | Vinay | Roy | Delhi | Kanpur | Ghaziabad | 95 | 97 | A1 |
16 | Manoj | Gupta | Ghaziabad | Meerut | Chandigarh | 95 | 90 | B1 |
19 | Ram | Gupta | Lucknow | Ghaziabad | Chandigarh | 89 | 95 | A2 |
以下 SELECT 查询将 SPACE 函数与上述 Student_Grade 表的 First_Name 和 Last_Name 列一起使用:
SELECT First_Name, Last_Name, First_Name + SPACE(10) + Last_Name AS 10_space FROM Student_Grade;
此 SQL 语句在每个学生的名字和姓氏之间添加 10 个空格。
输出结果为:
First_Name | Last_Name | 10_space |
---|---|---|
Aman | Sharma |
Aman Sharma |
Vishal | Sharma |
Vishal Sharma |
Raj | Gupta |
Raj Gupta |
Yash | Singhania |
Yash Singhania |
Vinay | Roy |
Vinay Roy |
Manoj | Gupta |
Manoj Gupta |
Ram | Gupta |
Ram Gupta |
以下 SELECT 查询将 SPACE 函数与上述 Student_Grade 表中 Roll_No 大于 2 的学生的 First_Name 和 Grade 列一起使用:
SELECT Roll_No, First_Name + SPACE(5) + Grade FROM Student_Grade WHERE Roll_No > 2;
输出结果为:
Roll_No | First_Name + SPACE(5) + Grade |
---|---|
07 |
Raj A1 |
04 |
Yash A2 |
11 |
Vinay A1 |
16 |
Manoj B1 |
19 |
Ram A2 |
热门文章
优秀文章