ToB企服应用市场:ToB评测及商务社交产业平台

标题: 第十二单元 `T-SQL` 编程 [打印本页]

---

作者: 汕尾海湾    时间: 2024-2-26 11:38
标题: 第十二单元 `T-SQL` 编程

1. create database step2_unit12;
2. go
3. use step2_unit12;
4. go
5. -- 部门表
6. CREATE TABLE [dbo].[Department](
7.     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
8.     [Name] [varchar](20) NULL
9. );
10. -- 职位表
11. CREATE TABLE [dbo].[Job](
12.     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
13.     [Name] [varchar](20) NULL
14. );
15. -- 人员表
16. CREATE TABLE [dbo].[person](
17.     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
18.     [Name] [varchar](50) NULL,
19.     [Sex] [varchar](10) NULL,
20.     [Age] [int] NULL
21. );
22. -- 工资表
23. CREATE TABLE [dbo].[Salary](
24.     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
25.     [Sal] [money] NULL,
26.     [Comm] [money] NULL,
27.     [StaffId] [int] NULL
28. );
29. -- 员工表
30. CREATE TABLE [dbo].[Staff](
31.     [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
32.     [Name] [varchar](20) NOT NULL,
33.     [Hiredate] [datetime] NOT NULL,
34.     [DepartmentId] [int] NULL,
35.     [JobId] [int] NULL
36. );
37. SET IDENTITY_INSERT [dbo].[Department] ON
38. ​
39. INSERT [dbo].[Department] ([Id], [Name]) VALUES (1, N'技术部')
40. INSERT [dbo].[Department] ([Id], [Name]) VALUES (2, N'人资部')
41. INSERT [dbo].[Department] ([Id], [Name]) VALUES (3, N'市场部')
42. SET IDENTITY_INSERT [dbo].[Department] OFF
43. SET IDENTITY_INSERT [dbo].[Job] ON
44. ​
45. INSERT [dbo].[Job] ([Id], [Name]) VALUES (1, N'经理')
46. INSERT [dbo].[Job] ([Id], [Name]) VALUES (2, N'组长')
47. INSERT [dbo].[Job] ([Id], [Name]) VALUES (3, N'员工')
48. SET IDENTITY_INSERT [dbo].[Job] OFF
49. SET IDENTITY_INSERT [dbo].[person] ON
50. ​
51. INSERT [dbo].[person] ([Id], [Name], [Sex], [Age]) VALUES (1, N'高圆圆', N'女', 28)
52. INSERT [dbo].[person] ([Id], [Name], [Sex], [Age]) VALUES (2, N'王佳佳', N'女', 32)
53. INSERT [dbo].[person] ([Id], [Name], [Sex], [Age]) VALUES (3, N'杨硕', N'男', 25)
54. SET IDENTITY_INSERT [dbo].[person] OFF
55. SET IDENTITY_INSERT [dbo].[Salary] ON
56. ​
57. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (1, 15000.0000, 10000.0000, 1)
58. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (2, 15000.0000, 9000.0000, 2)
59. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (3, 12000.0000, 8000.0000, 3)
60. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (4, 8000.0000, 6000.0000, 4)
61. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (5, 4000.0000, 3000.0000, 5)
62. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (6, 9000.0000, 8000.0000, 6)
63. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (7, 15000.0000, 12000.0000, 7)
64. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (8, 12000.0000, 9000.0000, 7)
65. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (9, 7000.0000, 2000.0000, 9)
66. INSERT [dbo].[Salary] ([Id], [Sal], [Comm], [StaffId]) VALUES (10, 9000.0000, 8000.0000, 10)
67. SET IDENTITY_INSERT [dbo].[Salary] OFF
68. SET IDENTITY_INSERT [dbo].[Staff] ON
69. ​
70. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (1, N'王鑫', CAST(N'2014-02-01 00:00:00.000' AS DateTime), 1, 1)
71. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (2, N'张磊', CAST(N'2015-09-03 00:00:00.000' AS DateTime), 2, 1)
72. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (3, N'李鹏', CAST(N'2016-08-01 00:00:00.000' AS DateTime), 3, 2)
73. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (4, N'王洋', CAST(N'2019-02-03 00:00:00.000' AS DateTime), 1, 3)
74. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (5, N'吴潘', CAST(N'2018-10-01 00:00:00.000' AS DateTime), 2, 3)
75. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (6, N'贾丹', CAST(N'2018-07-01 00:00:00.000' AS DateTime), 2, 2)
76. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (7, N'李露', CAST(N'2016-09-01 00:00:00.000' AS DateTime), 3, 1)
77. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (8, N'杨腾', CAST(N'2018-11-08 00:00:00.000' AS DateTime), 1, 2)
78. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (9, N'孙艺', CAST(N'2018-12-26 00:00:00.000' AS DateTime), 3, 3)
79. INSERT [dbo].[Staff] ([Id], [Name], [Hiredate], [DepartmentId], [JobId]) VALUES (10, N'赵帅', CAST(N'2019-01-03 00:00:00.000' AS DateTime), 1, 3)
80. SET IDENTITY_INSERT [dbo].[Staff] OFF

复制代码

 
1. T-SQL 命名规则

1，开头不能是数字，空格或特殊字符
2，首字符后可以包含 字母 数字 汉字 _ @ #
3，不能是系统保留字（关键字）
 
2. 变量

全局变量

整个系统都可以使用

1. @@ERROR   返回执行的上一个T-SQL语句的错误号,0 代表没有错误，非0 代表有错误
2. begin tran
3.     declare @a int = 1;
4.     select @a/0
5.     select @@ERROR
6. commit
8. @@IDENTITY 返回插入到表的IDENTITY列的最后一个值
9. @@LANGUAGE返回当前所用语言的名称
10. @@MAX_CONNECTIONS返回Sql Server实例允许同时进行的最大用户连接数
11. @@ROWCOUNT返回上一次语句影响的数据行的行数
12. @@SERVERNAME返回运行Sql Server的本地服务器的名称
13. @@SERVICENAME返回Sql Server正在其下运行的注册表项的名称
14. @@TIMETICKS返回每个时钟周期的微秒数
15. @@TRANCOUNT返回当前连接的活动事务数
16. @@VERSION 返回当前安装的日期、版本和处理器类型

复制代码

 
局部变量

注意:定义局部变量必须以@开头。只能在当前连接（会话）下使用

定义变量

1. --declare @变量名 变量类型= 变量值;
2. --如
3. declare @name varchar(20)= '任我行';

复制代码

 
局部变量赋值

• 1. select 赋值
  3. declare @age int= 0; -- 定义变量
  5. -- 直接赋值
  6. select @age = 20;
  8. -- 也可通过查询语句进行赋值
  9. select @age=Age from person where Name='高圆圆';
  10. set 赋值
  12. set @age = 20; -- 不可通过查询语句进行赋值
  13. declare @username nvarchar(50)='任我行'; --定义局部变量,并赋初始值
  14. set @username='张三'; --通过set赋值
  15. select @username='aaa'; --通过select 赋值
  16. select @username as 用户名; --通过select 输出并取别名
  17. print @username; --print 打印输出

  复制代码
   
  

案例
[code]-- 查询高圆的年龄,如果年龄是1-10岁,则输出儿童,10-25 输出少年,25-40输出青年,40-60输出中年,其他老年​declare @age int=0;select @age=Age from person where Name='高圆圆';set @age=30; --给局部变量赋值​if @age

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4