һ ()
09.03.04
-3
-
:
λ
____________________________________________________ ..
-6.1301 140736
____________________________________________________________
2016
, Ȼ ()
09.03.04
-3
-
.. -6.1301 130349
1 : 軻
2 : 28 2015
3 : 49
4 :
_____________________________________________________________
----------- ..
2017
充...4
腅...8
11
...13
.......18
......42
ⅅ..44
. (). , .
() , .
, , , .
. : , , , , -.
|
|
|
() , , . . , , , .
, . , , , , , .
, , , . , , , , , . , . , , .
- , . - .
. : , . .
:
, ;
, : , .
SQL (Structured Query Language) .
. , . - ( , , ) , .
, .
, . , , , . . , .
|
|
|
, , . , .
, , . , , . . . , :
;
;
, , ;
.
, , , ( , ).
(1) , .
. , , , 1.
, ( ) .
, , .
- , MS SQL Server ( Oracle), . ( ) , .. , . :
1. , , ;
2. , ( )
a. , CASE-;
b. ( VIEW), ;
c. , SELECT, FROM WHERE ( );
d. , , , HAVING;
e. , ANY, SOME ALL;
3. ( ) ;
|
|
|
4. ( ) , , , . , , ().
5. , ( ) ; ;
6. , , ;
7. ;
8. ( 2 ) , ( ) .
9. : . .
, . , .
, , , ( ).
( ) .
| . | ( ) | ( ) | ||
| - . . . |
CREATE TABLE test123.dbo.katalog (
id_katalog INT NOT NULL
,name VARCHAR(50) NOT NULL
,raspologenie VARCHAR(50) NOT NULL
,CONSTRAINT FK_katalog PRIMARY KEY CLUSTERED (id_katalog)
) ON [PRIMARY]
GO
CREATE TABLE test123.dbo.soderganie (
id_soderg INT NOT NULL
,dir VARCHAR(50) NOT NULL
,[file] VARCHAR(50) NOT NULL
,CONSTRAINT FK_soderg PRIMARY KEY CLUSTERED (id_soderg)
,CONSTRAINT FK_soderganie FOREIGN KEY (id_soderg) REFERENCES dbo.katalog (id_katalog)
) ON [PRIMARY]
GO
CREATE TABLE test123.dbo.tip (
id_tip INT IDENTITY
,CONSTRAINT FK_tip PRIMARY KEY CLUSTERED (id_tip)
,CONSTRAINT FK_tip2 FOREIGN KEY (id_tip) REFERENCES dbo.katalog (id_katalog)
) ON [PRIMARY]
GO
CREATE TABLE test123.dbo.vladelec (
id_vladelec INT IDENTITY
,name VARCHAR(50) NULL
,otch VARCHAR(50) NULL
,CONSTRAINT FK_vladelec PRIMARY KEY CLUSTERED (id_vladelec)
,CONSTRAINT FK_vladelec_katalog_id_katalog FOREIGN KEY (id_vladelec) REFERENCES dbo.katalog (id_katalog)
) ON [PRIMARY]
GO
2. , ( )
a) , CASE-;
b) , SELECT, FROM WHERE ( );
|
|
|
SELECT katalog.name
FROM katalog
WHERE raspologenie = 'C:'
c) , , , HAVING;
-- , 400
SELECT katalog.name id_tip FROM katalog,tip
GROUP BY id_tip, id_katalog,katalog.name
HAVING COUNT(id_katalog) =id_tip;
d) , ANY, SOME ALL;
SELECT katalog.name FROM katalog
WHERE id_katalog = ANY (SELECT id_katalog FROM soderganie);
4. ( ) , , , . , , ().
CREATE TRIGGER Triggerkatalog
ON katalog
INSTEAD OF DELETE
AS
BEGIN
DECLARE @katalogId int
SELECT @katalogId = id_katalog FROM deleted;
if @katalogId IN (SELECT id_katalog FROM katalog)
BEGIN
ROLLBACK
print(' , ')
END
END
5. , ( ) ; ;
CREATE PROCEDURE updatekatalog (@updateId int, @updatekatalogId INT, @updatenameId NVARCHAR(50), @updateraspologenieId NVARCHAR(MAX), @updatedirId NVARCHAR(MAX), @updatefileId NVARCHAR(MAX), @updatetip NVARCHAR(MAX), @updatefam NVARCHAR(MAX), @updatevladelecId NVARCHAR(MAX))
AS
BEGIN
UPDATE katalog
SET id_katalog = @updatekatalogId, name = @updatenameId, raspologenie = @updateraspologenieId, dir = @updatedirId, [FILE] = @updatefileId, id_tip=@updatetip, id_vladelec = @updatevladelecId
WHERE id_katalog = @updateId;
END
6. , , ;
CREATE PROCEDURE soderg(@id_soderg INT, @dir nvarchar(50), @file nvarchar(50))
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO soderganie(id_soderg, dir,[file]) VALUES(@id_soderg, @dir, @file);
IF @dir=@file
ROLLBACK
COMMIT TRANSACTION
END
7. ;
CREATE PROCEDURE updatekatalog (@updateId int, @updatename nvarchar(50), @updateraspologenie NVARCHAR(50))
AS
BEGIN
DECLARE my_cursor CURSOR
FOR SELECT ID FROM Tariffs;
OPEN my_cursor
DECLARE @id INT;
FETCH NEXT FROM my_cursor INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @updateId = @id
UPDATE katalog
SET id_katalog = 0
,name = ''
,raspologenie = ''
WHERE id_katalog = @updateId;
FETCH NEXT FROM my_cursor INTO @id;
END
CLOSE my_cursor;
DEALLOCATE my_cursor;
END
8. : . .
CREATE LOGIN [Admin] WITH PASSWORD=N'''1234321123321',
DEFAULT_DATABASE=[test123], DEFAULT_LANGUAGE=[], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC sys.sp_addsrvrolemember @loginame = N'Admin', @rolename = N'sysadmin'
GO
ALTER LOGIN [Admin] DISABLE
GO
CREATE LOGIN [USER] WITH PASSWORD=N'123456',
DEFAULT_DATABASE=[test123], DEFAULT_LANGUAGE=[], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [User] DISABLE
GO
Form1
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace kursachlesko
{
public partial class Form1: Form
{
public Form1()
{
InitializeComponent();
}
private void label1_Click(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
ActiveForm.Hide();
Form2 frm = new Form2();
frm.ShowDialog();
Close();
}
private void button2_Click(object sender, EventArgs e)
{
ActiveForm.Hide();
Form3 frm = new Form3();
|
|
|
frm.ShowDialog();
Close();
}
}
}
Form2
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace kursachlesko
{
public partial class Form2: Form
{
string ConnectStr = @"Data Source=WIN-U8C7MPAU391;Initial Catalog = test123; Integrated Security = True;
user=Admin; database=test123; password=1234321123321;";
SqlConnection Connect = null;
public Form2()
{
InitializeComponent();
}
public void DoSql(string sql_query)
{
try
{
SqlConnection conn = new SqlConnection(ConnectStr);
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(sql_query, conn);
DataTable dt = new DataTable();
SqlCommand comm = new SqlCommand();
sda.Fill(dt);
}
catch
{
MessageBox.Show(@"Error");
}
}
private void button25_Click(object sender, EventArgs e)
{
ActiveForm.Hide();
Form4 frm = new Form4();
frm.ShowDialog();
Close();
}
private void button2_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO katalog (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox1.Text);
}
private void button1_Click(object sender, EventArgs e)
{
var Sqlcmd =new SqlCommand("INSERT INTO katalog (name) VALUES (@name)",Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox1.Text);
}
private void button3_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO katalog (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox1.Text);
}
private void button6_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO katalog (raspologenie) VALUES (@raspologenie)", Connect);
Sqlcmd.Parameters.AddWithValue("@raspologenie", textBox2.Text);
}
private void button5_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO katalog (raspologenie) VALUES (@raspologenie)", Connect);
Sqlcmd.Parameters.AddWithValue("@raspologenie", textBox2.Text);
}
private void button4_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO katalog (raspologenie) VALUES (@raspologenie)", Connect);
Sqlcmd.Parameters.AddWithValue("@raspologenie", textBox2.Text);
}
private void button9_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO soderganie (dir) VALUES (@dir)", Connect);
Sqlcmd.Parameters.AddWithValue("@dir", textBox3.Text);
}
private void button8_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO soderganie (dir) VALUES (@dir)", Connect);
Sqlcmd.Parameters.AddWithValue("@dir", textBox3.Text);
}
private void button7_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO soderganie (dir) VALUES (@dir)", Connect);
Sqlcmd.Parameters.AddWithValue("@dir", textBox3.Text);
}
private void button12_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO soderganie (file) VALUES (@file)", Connect);
Sqlcmd.Parameters.AddWithValue("@file", textBox4.Text);
}
private void button11_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO soderganie (file) VALUES (@file)", Connect);
Sqlcmd.Parameters.AddWithValue("@dir", textBox4.Text);
}
private void button10_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO soderganie (file) VALUES (@file)", Connect);
Sqlcmd.Parameters.AddWithValue("@file", textBox4.Text);
}
private void button15_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO tip (tip) VALUES (@tip)", Connect);
Sqlcmd.Parameters.AddWithValue("@tip", textBox5.Text);
}
private void button14_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO tip (tip) VALUES (@tip)", Connect);
Sqlcmd.Parameters.AddWithValue("@tip", textBox5.Text);
}
private void button13_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO tip (tip) VALUES (@tip)", Connect);
Sqlcmd.Parameters.AddWithValue("@tip", textBox5.Text);
}
private void button18_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO vladelec (fam) VALUES (@fam)", Connect);
Sqlcmd.Parameters.AddWithValue("@fam", textBox6.Text);
}
private void button17_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO vladelec (fam) VALUES (@fam)", Connect);
Sqlcmd.Parameters.AddWithValue("@fam", textBox6.Text);
}
private void button16_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO vladelec (fam) VALUES (@fam)", Connect);
Sqlcmd.Parameters.AddWithValue("@fam", textBox6.Text);
}
private void button21_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO vladelec (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox7.Text);
}
private void button20_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO vladelec (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox7.Text);
}
private void button19_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO vladelec (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox7.Text);
}
private void button24_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO vladelec (otch) VALUES (@otch)", Connect);
Sqlcmd.Parameters.AddWithValue("@otch", textBox8.Text);
}
private void button23_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO vladelec (otch) VALUES (@otch)", Connect);
Sqlcmd.Parameters.AddWithValue("@otch", textBox8.Text);
}
private void button22_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO vladelec (otch) VALUES (@otch)", Connect);
Sqlcmd.Parameters.AddWithValue("@otch", textBox8.Text);
}
}
}
Form 3
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace kursachlesko
{
public partial class Form3: Form
{
string ConnectStr = @"Data Source=WIN-U8C7MPAU391;Initial Catalog = test123; Integrated Security = True;
user=Admin; database=test123; password=1234321123321;";
SqlConnection Connect = null;
public string ConnectionString { get; private set; }
public Form3()
{
InitializeComponent();
}
public void DoSql(string sql_query)
{
try
{
SqlConnection conn = new SqlConnection(ConnectStr);
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(sql_query, conn);
DataTable dt = new DataTable();
SqlCommand comm = new SqlCommand();
sda.Fill(dt);
}
catch
{
MessageBox.Show(@"Error");
}
}
private void button2_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO katalog (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox1.Text);
}
private void button1_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO katalog (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox1.Text);
}
private void button3_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO katalog (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox1.Text);
}
private void button6_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO katalog (raspologenie) VALUES (@raspologenie)", Connect);
Sqlcmd.Parameters.AddWithValue("@raspologenie", textBox2.Text);
}
private void button5_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO katalog (raspologenie) VALUES (@raspologenie)", Connect);
Sqlcmd.Parameters.AddWithValue("@raspologenie", textBox2.Text);
}
private void button4_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO katalog (raspologenie) VALUES (@raspologenie)", Connect);
Sqlcmd.Parameters.AddWithValue("@raspologenie", textBox2.Text);
}
private void button9_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO soderganie (dir) VALUES (@dir)", Connect);
Sqlcmd.Parameters.AddWithValue("@dir", textBox3.Text);
}
private void button8_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO soderganie (dir) VALUES (@dir)", Connect);
Sqlcmd.Parameters.AddWithValue("@dir", textBox3.Text);
}
private void button7_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO soderganie (dir) VALUES (@dir)", Connect);
Sqlcmd.Parameters.AddWithValue("@dir", textBox3.Text);
}
private void button12_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO soderganie (file) VALUES (@file)", Connect);
Sqlcmd.Parameters.AddWithValue("@file", textBox4.Text);
}
private void button11_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO soderganie (file) VALUES (@file)", Connect);
Sqlcmd.Parameters.AddWithValue("@dir", textBox4.Text);
}
private void button10_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO soderganie (file) VALUES (@file)", Connect);
Sqlcmd.Parameters.AddWithValue("@file", textBox4.Text);
}
private void button15_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO tip (tip) VALUES (@tip)", Connect);
Sqlcmd.Parameters.AddWithValue("@tip", textBox5.Text);
}
private void button14_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO tip (tip) VALUES (@tip)", Connect);
Sqlcmd.Parameters.AddWithValue("@tip", textBox5.Text);
}
private void button13_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO tip (tip) VALUES (@tip)", Connect);
Sqlcmd.Parameters.AddWithValue("@tip", textBox5.Text);
}
private void button18_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO vladelec (fam) VALUES (@fam)", Connect);
Sqlcmd.Parameters.AddWithValue("@fam", textBox6.Text);
}
private void button17_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO vladelec (fam) VALUES (@fam)", Connect);
Sqlcmd.Parameters.AddWithValue("@fam", textBox6.Text);
}
private void button16_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO vladelec (fam) VALUES (@fam)", Connect);
Sqlcmd.Parameters.AddWithValue("@fam", textBox6.Text);
}
private void button21_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO vladelec (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox7.Text);
}
private void button20_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO vladelec (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox7.Text);
}
private void button19_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO vladelec (name) VALUES (@name)", Connect);
Sqlcmd.Parameters.AddWithValue("@name", textBox7.Text);
}
private void button24_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("INSERT INTO vladelec (otch) VALUES (@otch)", Connect);
Sqlcmd.Parameters.AddWithValue("@otch", textBox8.Text);
}
private void button23_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("UPDATE INTO vladelec (otch) VALUES (@otch)", Connect);
Sqlcmd.Parameters.AddWithValue("@otch", textBox8.Text);
}
private void button22_Click(object sender, EventArgs e)
{
var Sqlcmd = new SqlCommand("DELETE INTO vladelec (otch) VALUES (@otch)", Connect);
Sqlcmd.Parameters.AddWithValue("@otch", textBox8.Text);
}
}
}
