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'(503)555-7233')
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