1

Tematy:

•Operacje aktualizujące bazy danych operacje: insert, delete,
update.
•Techniki programowania aplikacji baz danych. Architektury i
protokoły komunikacyjne ODBC, ADO, JDBC
•Elementy aktywne baz danych: wyzwalacze, serwer bazy
danych jako serwer aplikacji.

2

insert into [Order Details] values (10248 ,17 ,39 ,1 , 0)

insert into [Order Details]
(OrderID, ProductID, UnitPrice, Quantity,Discount)

values (10248, 17, 39, 1, 0)

Najprostsza posta

ć

polecenia:

Wymienione wprost nazwy pól:

Podzbiór pól:

insert into [Order Details](OrderID,ProductID)
values (10248, 18)

3

insert into [Order Details](OrderID,ProductID,UnitPrice)

values (10248, 19, default)

insert into [Order Details](OrderID,ProductID,Discount)

values (10248, 20, 0.0)

insert into

Northwind.dbo.Shippers

(CompanyName,Phone)
values

(N'Snowflake Shipping', N'[phone]')

Przykład z systemu pomocy MS SQL:

4

national character(n)

Synonym:nchar(n)
Fixed-length Unicode data with a maximum length of 4000 characters.
Default length = 1. Storage size, in bytes, is two times the number of
characters entered.

Komentarz MSDN dot. narodowego typu znakowego.

5

Klucz główny automatycznie generowany:

create table Shippers
(

ShipperID int identity (1, 1) not null ,
CompanyName nvarchar (40) not null,
Phone nvarchar(24),
constraint [PK_Shippers] primary key (ShipperID)

)

select @@identity
set identity_insert Shippers off

Dost

ę

p do warto

ś

ci klucza:

6

Warto

ś

ci domy

ś

lne:

create table beta
([id] int identity(1,1),

process_id smallint default @@SPID,
date_ins datetime default getdate()

)

insert into beta(process_id) values(default)

insert into beta(process_id,date_ins) values(default,default)
insert into beta(process_id,date_ins) values(0,default)
set identity_insert beta on
insert into beta([id]) values(19)
insert into beta(id,date_ins) values(20,'2007-01-13')

7

Modyfikacja struktury BD w zakresie wi

ę

zów

create table beta
([id] int identity(1,1),

process_id smallint default @@SPID,
date_ins datetime default getdate()

)

ALTER TABLE [dbo].[beta] ADD DEFAULT (getdate())

FOR [date_ins]

ALTER TABLE [dbo].[beta]

DROP CONSTRAINT [DF__beta__date_ins__7F60ED59]

Dodawanie warto

ś

ci domy

ś

lnych do istniej

ą

cej tabeli

8

USE [gamma]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[beta](

[id] [int] IDENTITY(1,1) NOT NULL,
[process_id] [smallint] NULL,
[date_ins] [datetime] NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[beta] ADD DEFAULT (@@spid) FOR [process_id]

ALTER TABLE [dbo].[beta] ADD DEFAULT (getdate()) FOR [date_ins]

Definicja tabeli odzyskana z BD (script table as)

9

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Przykłady tzw. opcji bazy danych tutaj 2 spośród ponad setki.

•

Wynik porównania 2 wartości NULL jest UNKNOWN
(logika 3-wartościowa,)

•

Użycie podwójnych cudzysłowów jako oznacznika
identyfikatora.

10

Dostęp do indentyfikatorów obiektów
MS SQL Management Studio –metadane (1)

11

Dostęp do indentyfikatorów obiektów
MS SQL Management Studio –metadane (2)

select name,parent_obj from sysobjects where type='D''

name

parent_obj

DF__beta__process_id__7E6CC920

2105058535

DF__beta__date_ins__7F60ED59

2105058535

select name,parent_obj from sysobjects where
type='D' and
parent_obj=(select id from sysobjects where name='beta')

12

name

type

PK_Orders

K

DF_Orders_Freight

D

FK_Orders_Customers

F

FK_Orders_Employees

F

FK_Orders_Shippers

F

name

type

CK__Employees__xyz__619B8048 C
PK_Employees

K

FK_Employees_Employees

F

CK_Birthdate

C

select name,type from sysobjects where
parent_obj=(select id from sysobjects where name='Employees')

select name,type from sysobjects where
parent_obj=(select id from sysobjects where name='Orders')

Temat pracy inżynierskiej (?): narzędzie zarządzania strukturą BD

13

Wstawianie wierszy b

ę

d

ą

cych wynikiem zapytania:

create table totals
(TotalID int identity(1,1),

[year] int,

Value money

)

select year(OrderDate),sum(Quantity*UnitPrice) from

[Order Details] join Orders

on Orders.OrderID=[Order Details].OrderID

group by year(OrderDate)

14

insert into totals([year],Value)

select year(OrderDate),sum(Quantity*UnitPrice) from

[Order Details] join Orders

on Orders.OrderID=[Order Details].OrderID

group by year(OrderDate)

select * from totals

select EmployeeID,sum(Quantity*UnitPrice) from

[Order Details] join Orders

on Orders.OrderID=[Order Details].OrderID

group by EmployeeId

15

INSERT [ INTO]

{ table_name WITH ( < table_hint_limited > [ ...n ] )

| view_name
| rowset_function_limited

}

{

[ ( column_list ) ]

{ VALUES

( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement

}

}

Składnia polecenia INSERT

16

delete - Usuwanie wierszy z bazy danych

delete from totals

Najprostsza posta

ć

:

delete from totals where TotalID=1

Najcz

ę

stsza posta

ć

:

Inna cz

ę

sta posta

ć

delete from totals where

CurrentTimeStamp=0x00000000000004b1

and TotalID=1

17

alter table totals add CurrentTimeStamp TimeStamp

declare @ts TimeStamp
declare @id int
select @id=5
select @ts=CurrentTimeStamp from totals where TotalID=@id
-- some interesting processing
delete from totals where TotalID=@id and CurrentTimeStamp=@ts

18

Bardziej zło

ż

one postacie polecenia delete:

delete from [Order Details] from Orders
where

Orders.OrderID=[Order Details].OrderID and
year(OrderDate)=1998

create view Candidate2Fire as
select EmployeeID from Orders,[Order Details]
where Orders.OrderID=[Order Details].OrderID
group by EmployeeID
having sum(UnitPrice*Quantity)=
(select min(value) from
(select sum(UnitPrice*Quantity) value from Orders,[Order Details]
where

Orders.OrderID=[Order Details].OrderID

group by EmployeeID) t)

19

Zwalniamy pracownika!

1) Usuwamy pozycje zamówie

ń

obsługiwanych przez Mr.

Stevena Buchanana (EmployeeID=5)

delete from [Order Details] from Orders
where

[Order Details].OrderID=Orders.OrderID and
EmployeeID=5

2)

Usuwamy zamówienia

delete from Orders where EmployeeID=5

3)

Usuwamy człowieka

delete from Employees where EmployeeID=5

20

DELETE

[ FROM ]

{ table_name WITH ( < table_hint_limited > [ ...n ] )

| view_name
| rowset_function_limited

}
[ FROM { < table_source > } [ ,...n ] ]

[ WHERE

{ < search_condition >
| { [ CURRENT OF

{ { [ GLOBAL ] cursor_name }

| cursor_variable_name

}

] }

}

] [ OPTION ( < query_hint > [ ,...n ] ) ]

21

--Queso Cabrales
update Products set UnitPrice=25.0 where ProductID=11

update Products set UnitPrice=UnitPrice*0.1

where ProductID=11

--Beverages
update Products set UnitPrice=UnitPrice*0.9

where CategoryID=1

Klucz jako argument selekcji:

Argument nie b

ę

d

ą

cy kluczem:

update Products set Discontinued=0

Najprostsza posta

ć

:

22

update Products
set
UnitPrice=

(select avg(UnitPrice) from Products

where CategoryID=1)

where CategoryID=1

update Products
set

UnitPrice=26.0,

UnitsInStock=UnitsInStock-1

where ProductID=11

Zagnie

ż

d

ż

one zapytanie:

Jednoczesna aktualizacja wielu pól:

23

update Products set UnitPrice=UnitPrice*1.1 from Suppliers
where

Products.SupplierID=Suppliers.SupplierID and
Country in ('USA','UK')

U

ż

ycie klauzuli from:

update Products set UnitsInStock=UnitsInStock-od.Quantity from

Products p, Orders o,[Order Details] od

where

od.OrderID=o.OrderID and
p.ProductID=od.ProductID and
o.OrderDate=(select max(OrderDate) from Orders)

Na podstawie przykładu z systemu pomocy MS SQL:

24

If the object being updated is the same as the object in the FROM
clause and there is only one reference to the object in the FROM
clause, an object alias may or may not be specified. If the object
being updated appears more than one time in the FROM clause,
one, and only one, reference to the object must not specify a table
alias. All other references to the object in the FROM clause must
include an object alias.

Zamieszanie wynika z faktu, że tablica może być użyta jako
obiekt modyfikowany (raz) oraz jako źródło danych
(wielokrotnie). Ta modyfikowana instancja użyta bez aliasu.

Cenne uwagi MSDN o klauzuli from w update

25

update Products set UnitsInStock=UnitsInStock-

(

select sum(Quantity) from

Orders o,[Order Details] od

where od.OrderID=o.OrderID and

p.ProductID=od.ProductID and
o.OrderDate=

(select max(OrderDate) from Orders)

)

from Products p

Wnioski:
•SQL j

ę

zyk przetwarzania danych

•Operacje poszukiwania danych – j

ę

zyk zapyta

ń

•Operacje modyfikacji, usuwania oraz wstawiania
•Komunikacja z serwerem tylko w SQL

26

BD

SQL

JDBC ODBC ADO BDE

delphi

java

ms office

27

var

Conn,Query,Cursor:Variant;

begin

Conn:=CreateOleObject('ADODB.Connection');
Conn.ConnectionString:='Provider=SQLOLEDB.1;

Integrated Security=SSPI;Initial Catalog=CJDate';

Conn.Open;
Query:=CreateOleObject('ADODB.Command');
Query.ActiveConnection:=Conn;
Query.CommandText:=

'select count(*) from S inner loop join SPJ on S.S#<>SPJ.S#';

Cursor:=Query.Execute;
Cursor.MoveFirst;
Label1.Caption:=Cursor.Fields[0];
Cursor.Close;
Conn.Close;

end;

Aplikacje Baz Danych

28

var

Conn:Connection;
Query:Command;
Cursor:RecordSet;

i:integer; Yield:OLEVariant;

begin

Conn:=CreateOleObject('ADODB.Connection') as Connection;
Conn.set_ConnectionString('Provider=SQLOLEDB.1;

Integrated Security=SSPI;Initial Catalog=CJDate');

Conn.Open('','','',0);
Cursor:=

Conn.Execute('select S# from (select * from S where status >10) sx',

Yield,adOptionUnspecified);

Cursor.MoveFirst;
while not Cursor.EOF do
begin

ListBox1.Items.Add(Cursor.Fields[0].get_Value);
Cursor.MoveNext;

end;
Conn.Close;

end;

29

<SCRIPT>
var

Connection=new ActiveXObject('ADODB.Connection');

Query=new ActiveXObject('ADODB.Command');
...
Query.ActiveConnection=Connection;

Query.CommandText="select Country,CompanyName from Customers";
Cursor=Query.Execute;
Cursor.MoveFirst;
while (!Cursor.Eof)
{ row=dbgrid1.insertRow();

cell=row.insertCell();

cell.innerText=Cursor.Fields(0); cell.bgColor='AntiqueWhite';
cell.style.borderColor='black';

cell.style.width='5cm';

cell=row.insertCell();

cell.innerText=Cursor.Fields(1);

cell.style.width='10cm';

Cursor.MoveNext;

}

...

30

import java.sql.*;
import sun.jdbc.odbc.JdbcOdbcDriver;
public class Application
{

public static void main(String[] args) throws Exception

{

JdbcOdbcDriver driver=new JdbcOdbcDriver();

Connection conn= DriverManager.getConnection("Jdbc:Odbc:northwind","","");
Statement stmt= conn.createStatement();

ResultSet rs=stmt.executeQuery("select * from [order details]");
int i=0;
while (rs.next())

{ i++;

System.out.println(rs.getString(2)+" "+rs.getString(3));
}

System.out.println(i);

}

}

31

Epoka c# i ADO.NET, przykłady z materiałów lab. (1)

using

System;

using

System.Collections.Generic;

using

System.Linq;

using

System.Text;

using

System.Data.OleDb;

namespace

ConsoleApplication3

{

class

Program

{

static void

Main(

string

[] args)

{

OleDbConnection

conn =

new

OleDbConnection

(

@"Provider=SQLNCLI.1;Data Source=MIROSŁAW-PC\SQLEXPRESS;Integrated Security=SSPI"

);

conn.Open();

conn.Close();

}

}

}

Lab4:

32

Epoka c# i ADO.NET, przykłady z materiałów lab. (2)

Lab.4 c.d.

using

System;

using

System.Collections.Generic;

using

System.Linq;

using

System.Text;

using

System.Data.OleDb;

namespace

ConsoleApplication3

{

class

Program

{

static void

Main(

string

[] args)

{

OleDbConnection

conn =

conn.Open();

OleDbCommand

cmd =

new

OleDbCommand

(

"select * from Employees"

,conn);

Console

.WriteLine(

"Employees"

);

OleDbDataReader

rdr=

cmd.ExecuteReader();

while

(rdr.Read())

{

for

(

int

i = 0; i < rdr.FieldCount; i++)

{

Console

.Write(rdr.GetValue(i).ToString());

Console

.Write(

"\t"

);

}

}

conn.Close();

}

}

}

Parametry połączenia,
por. poprzednia plansza

33

OleDbConnection

conn =

new

OleDbConnection

(

@"Provider=SQLNCLI.1;Data Source=MIROSŁAW-PC\SQLEXPRESS;Integrated Security=SSPI"

);

SqlConnection

conn =

new

SqlConnection

(

@"Data Source=p10;Integrated Security=SSPI; Initial Catalog=Northwind"

);

Epoka c# i ADO.NET, przykłady z materiałów lab. (3)

Komponenty baz danych OleDB (ogólne) oraz wyspecjalizowane

Zalety wyspecjalizowanego zestawu:

•

Prawdopodobnie większa wydajność

•

Prostsza konfiguracja

•

Bogatsza funkcjonalność

34

Prosta aplikacja okienkowa Desktop