Mercurial > pub > bltoolkit

comparison Data/Create Scripts/MySql.sql @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
1
2 DROP TABLE Doctor
3 GO
4 DROP TABLE Patient
5 GO
6
7 -- Person Table
8
9 DROP TABLE Person
10 GO
11
12 CREATE TABLE Person
13 (
14 PersonID int AUTO_INCREMENT NOT NULL,
15 FirstName varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
16 LastName varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
17 MiddleName varchar(50) NULL,
18 Gender char(1) NOT NULL,
19 CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID)
20 )
21 GO
22
23 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M')
24 GO
25 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M')
26 GO
27
28 -- Doctor Table Extension
29
30 CREATE TABLE Doctor
31 (
32 PersonID int NOT NULL,
33 Taxonomy varchar(50) NOT NULL,
34 CONSTRAINT PK_Doctor PRIMARY KEY CLUSTERED (PersonID),
35 CONSTRAINT FK_Doctor_Person FOREIGN KEY (PersonID)
36 REFERENCES Person(PersonID)
37 )
38 GO
39
40 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry')
41 GO
42
43 -- Patient Table Extension
44
45 CREATE TABLE Patient
46 (
47 PersonID int NOT NULL,
48 Diagnosis varchar(256) NOT NULL,
49 CONSTRAINT PK_Patient PRIMARY KEY CLUSTERED (PersonID),
50 CONSTRAINT FK_Patient_Person FOREIGN KEY (PersonID)
51 REFERENCES Person (PersonID)
52 )
53 GO
54
55 INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution')
56 GO
57
58
59 -- GetPersonById
60
61 DROP Procedure GetPersonById
62 GO
63
64 CREATE Procedure GetPersonById(_ID INT)
65 BEGIN
66
67 SELECT * FROM Person WHERE PersonID = _ID;
68
69 END
70 GO
71
72 -- GetPersonByName
73
74 DROP Procedure GetPersonByName
75 GO
76
77 CREATE Procedure GetPersonByName
78 (
79 _firstName varchar(50),
80 _lastName varchar(50)
81 )
82 BEGIN
83
84 SELECT * FROM Person WHERE FirstName = _firstName AND LastName = _lastName;
85
86 END
87 GO
88
89 -- Person_SelectByKey
90
91 DROP Procedure Person_SelectByKey
92 GO
93
94 CREATE Procedure Person_SelectByKey(id int)
95 BEGIN
96
97 SELECT * FROM Person WHERE PersonID = id;
98
99 END
100 GO
101
102 -- Person_SelectAll
103
104 DROP Procedure Person_SelectAll
105 GO
106
107 CREATE Procedure Person_SelectAll()
108 BEGIN
109
110 SELECT * FROM Person;
111
112 END
113 GO
114
115 -- Person_SelectByName
116
117 DROP Procedure Person_SelectByName
118 GO
119
120 CREATE Procedure Person_SelectByName
121 (
122 firstName varchar(50),
123 lastName varchar(50)
124 )
125 BEGIN
126
127 SELECT
128 *
129 FROM
130 Person
131 WHERE
132 FirstName = firstName AND LastName = lastName;
133
134 END
135 GO
136
137 -- Person_SelectListByName
138
139 DROP Procedure Person_SelectListByName
140 GO
141
142 CREATE Procedure Person_SelectListByName
143 (
144 firstName varchar(50),
145 lastName varchar(50)
146 )
147 BEGIN
148
149 SELECT
150 *
151 FROM
152 Person
153 WHERE
154 FirstName like firstName AND LastName like lastName;
155
156 END
157 GO
158
159 -- Person_Insert
160
161 DROP Procedure Person_Insert
162 GO
163
164 CREATE Procedure Person_Insert
165 (
166 FirstName varchar(50),
167 LastName varchar(50),
168 MiddleName varchar(50),
169 Gender char(1)
170 )
171 BEGIN
172
173 INSERT INTO Person
174 (LastName, FirstName, MiddleName, Gender)
175 VALUES
176 (LastName, FirstName, MiddleName, Gender);
177
178 SELECT LAST_INSERT_ID() AS PersonID;
179
180 END
181 GO
182
183 -- Person_Insert_OutputParameter
184
185 DROP Procedure Person_Insert_OutputParameter
186 GO
187
188 CREATE Procedure Person_Insert_OutputParameter
189 (
190 FirstName varchar(50),
191 LastName varchar(50),
192 MiddleName varchar(50),
193 Gender char(1),
194 OUT PersonID int
195 )
196 BEGIN
197
198 INSERT INTO Person
199 (LastName, FirstName, MiddleName, Gender)
200 VALUES
201 (LastName, FirstName, MiddleName, Gender);
202
203 SET PersonID = LAST_INSERT_ID();
204
205 END
206 GO
207
208 -- Person_Update
209
210 DROP Procedure Person_Update
211 GO
212
213 CREATE Procedure Person_Update
214 (
215 PersonID int,
216 FirstName varchar(50),
217 LastName varchar(50),
218 MiddleName varchar(50),
219 Gender char(1)
220 )
221 BEGIN
222
223 UPDATE
224 Person
225 SET
226 LastName = LastName,
227 FirstName = FirstName,
228 MiddleName = MiddleName,
229 Gender = Gender
230 WHERE
231 PersonID = PersonID;
232
233 END
234 GO
235
236 -- Person_Delete
237
238 DROP Procedure Person_Delete
239 GO
240
241 CREATE Procedure Person_Delete
242 (
243 PersonID int
244 )
245 BEGIN
246
247 DELETE FROM Person WHERE PersonID = PersonID;
248
249 END
250 GO
251
252 -- Patient_SelectAll
253
254 DROP Procedure Patient_SelectAll
255 GO
256
257 CREATE Procedure Patient_SelectAll()
258 BEGIN
259
260 SELECT
261 Person.*, Patient.Diagnosis
262 FROM
263 Patient, Person
264 WHERE
265 Patient.PersonID = Person.PersonID;
266
267 END
268 GO
269
270 -- Patient_SelectByName
271
272 DROP Procedure Patient_SelectByName
273 GO
274
275 CREATE Procedure Patient_SelectByName
276 (
277 firstName varchar(50),
278 lastName varchar(50)
279 )
280 BEGIN
281
282 SELECT
283 Person.*, Patient.Diagnosis
284 FROM
285 Patient, Person
286 WHERE
287 Patient.PersonID = Person.PersonID
288 AND FirstName = firstName AND LastName = lastName;
289
290 END
291 GO
292
293 -- BinaryData Table
294
295 DROP TABLE BinaryData
296 GO
297
298 CREATE TABLE BinaryData
299 (
300 BinaryDataID int AUTO_INCREMENT NOT NULL,
301 Stamp timestamp NOT NULL,
302 Data varbinary(1024) NOT NULL,
303 CONSTRAINT PK_BinaryData PRIMARY KEY CLUSTERED (BinaryDataID)
304 )
305 GO
306
307 -- OutRefTest
308
309 DROP Procedure OutRefTest
310 GO
311
312 CREATE Procedure OutRefTest
313 (
314 ID int,
315 OUT outputID int,
316 OUT inputOutputID int,
317 str varchar(50),
318 OUT outputStr varchar(50),
319 OUT inputOutputStr varchar(50)
320 )
321 BEGIN
322
323 SET outputID = ID;
324 SET inputOutputID = ID + inputOutputID;
325 SET outputStr = str;
326 SET inputOutputStr = str + inputOutputStr;
327
328 END
329 GO
330
331 -- OutRefEnumTest
332
333 DROP Procedure OutRefEnumTest
334 GO
335
336 CREATE Procedure OutRefEnumTest
337 (
338 str varchar(50),
339 OUT outputStr varchar(50),
340 OUT inputOutputStr varchar(50)
341 )
342 BEGIN
343
344 SET outputStr = str;
345 SET inputOutputStr = str + inputOutputStr;
346
347 END
348 GO
349
350 -- ExecuteScalarTest
351
352 DROP Procedure Scalar_DataReader
353 GO
354
355 CREATE Procedure Scalar_DataReader()
356 BEGIN
357
358 SELECT
359 12345 AS intField,
360 '54321' AS stringField;
361
362 END
363 GO
364
365 DROP Procedure Scalar_OutputParameter
366 GO
367
368 CREATE Procedure Scalar_OutputParameter
369 (
370 OUT outputInt int,
371 OUT outputString varchar(50)
372 )
373 BEGIN
374
375 SET outputInt = 12345;
376 SET outputString = '54321';
377
378 END
379 GO
380
381 -- Data Types test
382
383 DROP TABLE DataTypeTest
384 GO
385
386 CREATE TABLE DataTypeTest
387 (
388 DataTypeID int AUTO_INCREMENT NOT NULL,
389 Binary_ binary(50) NULL,
390 Boolean_ bit NOT NULL,
391 Byte_ tinyint NULL,
392 Bytes_ varbinary(50) NULL,
393 Char_ char(1) NULL,
394 DateTime_ datetime NULL,
395 Decimal_ decimal(20,2) NULL,
396 Double_ float NULL,
397 Guid_ varbinary(50) NULL,
398 Int16_ smallint NULL,
399 Int32_ int NULL,
400 Int64_ bigint NULL,
401 Money_ decimal(20,4) NULL,
402 SByte_ tinyint NULL,
403 Single_ real NULL,
404 Stream_ varbinary(50) NULL,
405 String_ varchar(50) NULL,
406 UInt16_ smallint NULL,
407 UInt32_ int NULL,
408 UInt64_ bigint NULL,
409 Xml_ varchar(1000) NULL,
410 CONSTRAINT PK_DataType PRIMARY KEY CLUSTERED (DataTypeID)
411 )
412 GO
413
414 INSERT INTO DataTypeTest
415 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
416 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
417 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
418 VALUES
419 ( NULL, 0, NULL, NULL, NULL, NULL, NULL,
420 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
421 NULL, NULL, NULL, NULL, NULL, NULL, NULL)
422 GO
423
424 INSERT INTO DataTypeTest
425 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
426 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
427 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
428 Xml_)
429 VALUES
430 ( UUID(), 1, 127, UUID(), 'B', CurDate(), 12345.67,
431 1234.567, UUID(), 32767, 32768, 1000000, 12.3456, 127,
432 1234.123, UUID(), 'string', 32767, 32768, 200000000,
433 '<root><element strattr="strvalue" intattr="12345"/></root>')
434 GO
435
436
437
438 DROP TABLE Parent
439 GO
440 DROP TABLE Child
441 GO
442 DROP TABLE GrandChild
443 GO
444
445 CREATE TABLE Parent (ParentID int, Value1 int)
446 GO
447 CREATE TABLE Child (ParentID int, ChildID int)
448 GO
449 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int)
450 GO
451
452
453 DROP TABLE LinqDataTypes
454 GO
455
456 CREATE TABLE LinqDataTypes
457 (
458 ID int,
459 MoneyValue decimal(10,4),
460 DateTimeValue datetime,
461 DateTimeValue2 datetime NULL,
462 BoolValue boolean,
463 GuidValue char(36),
464 BinaryValue varbinary(5000) NULL,
465 SmallIntValue smallint,
466 IntValue int NULL,
467 BigIntValue bigint NULL
468 )
469 GO
470
471 DROP TABLE TestIdentity
472 GO
473
474 CREATE TABLE TestIdentity (
475 ID int AUTO_INCREMENT NOT NULL,
476 CONSTRAINT PK_TestIdentity PRIMARY KEY CLUSTERED (ID)
477 )
478 GO