Extras din laborator
DIN VFP HELP
Structured Query Language (SQL) commands.
Visual FoxPro supports the following SQL commands:
SELECT - SQL
You can create a SELECT command query in these areas:
- In the Command window.
- In a Visual FoxPro program (like any other Visual FoxPro command).
- In the Query Designer.
ALTER TABLE - SQL
Modifies an existing table. You can modify the name, type, precision, scale, null value support, and referential integrity rules for each field in the table.
CREATE CURSOR - SQL
Creates a temporary table. Each field in the temporary table is defined with a name, type, precision, scale, null value support, and referential integrity rules. These definitions can be obtained from the command itself or from an array.
CREATE TABLE -SQL
Creates a table. Each new table field is defined with a name, type, precision, scale, null value support, and referential integrity rules. These definitions can be obtained from the command itself or from an array.
DELETE - SQL
Marks records in a table for deletion using SQL syntax.
INSERT - SQL
Appends a new record to the end of an existing table. The new record contains data listed in the INSERT command or from an array.
UPDATE - SQL
Updates records in a table. The records can be updated based on the results of a SELECT - SQL statement.
INTRODUCERE
Astfel pentru MANIPULAREA DATELOR sint utilizate comenzile
- SELECT Extragerea datelor din BD
- INSERT Adaugarea de noi linii/inscrieri in BD
- DELETE Stergerea de linii dintr-o tebela
- UPDATE Modificarea valorilor unor atribute
Pentru DEFINREA BAZEI DE DATE
- CREATE TABEL Adaugarea unei noi tabele in BD
- DROP TABLE Stergerea unei tabele din BD
- ALTER TABLE Modificarea structurii unui tabel
- CREATE VIEW Crearea unei tabele virtuale
- CREATE CURSOR Crearea unei tabele temporare
Pentru CONTROLUL TRANZACTIILOR
- BEGIN TRANZACTION Marcheaza inceputul unei tranzactii
- END TRANZACTION Marcheaza sfirsitul unei tranzactii
- ROLLBACK Abandoneaza tranzactia in curs
1. CREAREA TABELELOR SI DECLARAREA RESTRICTIILOR
Se efectuiaza cu ajutorul comenzii CREATE TABLE iar pentru modificarea structurii tabelului cu ALTER TABLE
Syntaxa
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
(FieldName1 FieldType [(nFieldWidth [, nPrecision])]
[NULL | NOT NULL]
[CHECK lExpression1 [ERROR cMessageText1]]
[DEFAULT eExpression1]
[PRIMARY KEY | UNIQUE]
[REFERENCES TableName2 [TAG TagName1]] && Tablename2 tabela-parinte a legaturii
[NOCPTRANS]
[, FieldName2 ...]
[, PRIMARY KEY eExpression2 TAG TagName2
|, UNIQUE eExpression3 TAG TagName3] && Se creaza un index candidat
[, FOREIGN KEY eExpression4 TAG TagName4 [NODUP] && Se creaza un index regular
REFERENCES TableName3 [TAG TagName5]]
[, CHECK lExpression2 [ERROR cMessageText2]])
| FROM ARRAY ArrayName
Exemplu din Materialul HELP al VFP.
The following example creates a new database named Mydata1. CREATE TABLE is used to create three tables (Salesman, Customer, and Orders). The FOREIGN KEY and REFERENCES clauses in the second CREATE TABLE command create a persistent one-to-many relationship between the Salesman and Customer tables. The DEFAULT clauses in the third CREATE TABLE command establish default values, and the CHECK and ERROR clauses establish business rules for entering data into specific fields. The MODIFY DATABASE is used to display the relationship between the three tables.
CLOSE DATABASES
CLEAR
* Create mydata database in the current directory or folder
CREATE DATABASE mydata1
* Create a salesman table with a primary key
CREATE TABLE salesman ;
(SalesID c(6) PRIMARY KEY, ;
SaleName C(20))
* Create a customer table and relate it to the salesman table.
CREATE TABLE customer ;
(SalesID c(6), ;
CustId i PRIMARY KEY, ;
CustName c(20) UNIQUE, ;
SalesBranch c(3), ;
FOREIGN KEY SalesId TAG SalesId REFERENCES salesman)
* Create an orders table related to customer with its own primary
* key and some business rules such as defaults & checks.
CREATE TABLE orders ;
(OrderId i PRIMARY KEY, ;
CustId i REFERENCES customer TAG CustId, ;
OrderAmt y(4), ;
OrderQty i ;
DEFAULT 10 ;
CHECK (OrderQty > 9) ;
ERROR "Order Quantity must be at least 10", ;
DiscPercent n(6,2) NULL ;
DEFAULT .NULL., ;
CHECK (OrderAmt > 0) ERROR "Order Amount Must be > 0" )
* Display new database, tables, and relationships
MODIFY DATABASE
* Delete example files
SET SAFETY OFF && To suppress verification message
CLOSE DATABASES && Close database before deleting
DELETE DATABASE mydata1 DELETETABLES
2. UTILIZAREA INSTRUCTIUNILOR SQL PENTRU CREAREA BD SI A TABELELOR
******program care prezinta crearea si manipularea cu datele a unei BD prin intermediul SQL - ******comenzilor
CLOSE DATA ALL
CLOSE TABLES ALL
CLOSE ALL
Preview document
Conținut arhivă zip
- Lucrul cu SQL in VFP.doc