[Database] database Basics

I. SQL statement (1) Select query statement Syntax: SELECT [ALL | DISTINCT] Target column expression [AS column name] [, target column expression [AS column name]...] FROM table name [, table name…] [WHERE condition expression [AND | OR condition expression...] [GROUPBY column name [HAVING condition expression] [ORDERBY column name [ASC | DESC]

I. SQL statement (1) Select query statement Syntax: SELECT [ALL | DISTINCT] Target column expression [AS column name] [, target column expression [AS column name]...] FROM table name [, table name…] [WHERE condition expression [AND | OR condition expression...] [group by column name [HAVING condition expression] [order by column name [ASC | DESC]

I. SQL statements

(1) Select query statement

Syntax: SELECT [ALL | DISTINCT] <目标列表达式> [AS column name]
[, <目标列表达式> [AS column name]...] FROM <表名> [, <表名> …]
[WHERE <条件表达式> [AND | OR <条件表达式> ...]
[Group by column name [HAVING <条件表达式> ]
[Order by column name [ASC | DESC]
Explanation: [ALL | DISTINCT] ALL: ALL

DISTINCT: duplicate rows not included
<目标列表达式> AVG, COUNT, SUM, MIN, MAX, and operator can be used for fields.
<条件表达式>
Query condition predicates
Comparison =,>, <, >=, <= ,! =, <>,
Confirm range Between and, NOT BETWEEN AND
Determine the set IN and NOT IN
Character matching LIKE ("%" matches any length, "_" matches one character), NOT LIKE
Null is null, IS NOT NULL
Subquery ANY, ALL, EXISTS
UNION (UNION), INTERSECT (intersection), and MINUS (difference)
Multiple conditions AND, OR, NOT
Query Result grouping
[HAVING <条件表达式> ] Grouping filtering Conditions
[Order by column name [ASC | DESC] sorts query results; ASC: ascending DESC: Descending

Example:

Example 1: query the student's student ID, name, course name, and score from the table score and student.

Select student. sno as student ID, student. name as name, course as course name, score as score fromscore, student where student. sid = score. sid andScore. sid =: sid
Example 2:

Select student. sno as student ID, student. name as name, AVG (score) as average score from score, studentwhere student. sid = score. sid and student. class =: class and (term = 5 or term = 6) group by student. sno, student. name having count (*)> 0 order by average DESC

Example 3: Fuzzy search

Select * from score where sid like '123'

Example 4: Set Query

Select * from student where class in (select class from student where name = 'chen xiao ')

(2) INSERT statement
Syntax: INSERT <表名> [( <字段名1> [, <字段名2> ,...])] VALUES ( <常量1> [, <常量2> ,...])

Example 1: insert into borrow table (rid, bookidx, bdate) VALUES (edit1.text, edit2.text, date)

Syntax: INSERT <表名> [( <字段名1> [, <字段名2> ,...])] Subquery
Example 2: insert into score1 (sno, name) SELECT sno, name FROM student WHERE class = '20170101'

(3) UPDATE-SQL
Syntax: UPDATE <Table Name> 〉

SET column name 1 = constant expression 1 [, column name 2 = constant expression 2...]
WHERE <条件表达式> [AND | OR <条件表达式> ...]

Example: update score set credithour = 4 where course = 'database'

(4) DELETE-SQL (delete records)
Syntax: delete from <Table Name> [WHERE <条件表达式> [AND | OR <条件表达式> ...]
Example: Delete from student where sid = '000000'

(5) CREATE TABLE
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]
[NOCPTRANS]
[, FieldName2...]
[, Primary key eExpression2 TAG TagName2
|, UNIQUE eExpression3 TAG TagName3]
[, Foreign key eExpression4 TAG TagName4 [NODUP]
REFERENCES TableName3 [TAG TagName5]
[, CHECK lExpression2 [ERROR cMessageText2])
| From array ArrayName

(6) ALTER TABLE
Alter table TableName1
ADD | ALTER [COLUMN] FieldName1
FieldType [(nFieldWidth [, nPrecision])]
[NULL | not null]
[CHECK lExpression1 [ERROR cMessageText1]
[DEFAULT eExpression1]
[Primary key | UNIQUE]
[REFERENCES TableName2 [TAG TagName1]
[NOCPTRANS]

(7) DROP TABLE
Drop table [path name.] TABLE Name

(8) CREATE INDEX
Create index index-name ON table-name (column [, column…])
Example: create index uspa ON password table (user, password)

(9) DROP INDEX
Drop index table-name.index-name | PRIMARY
Example: drop index password table. uspa

2. Use static SQL statements in the program
In the program design stage, the SQL command text is set as the SQL attribute value of the TQuery component.

3. Use dynamic SQL statements in the program
A dynamic SQL statement is an SQL statement that contains a parameter variable (for example, select * from student where class =: class). A value can be assigned to a parameter in a program. The following methods assign values to parameters:

1. assign values to parameters using the parameter Editor
Select the TQuery component, select the Params item in the object monitor OI, and set the parameter value in the pop-up parameter editing window.
Example: SELECT bookidx AS book number, collection table. bookname AS book name, bdate AS borrow date FROM borrowing table, collection table where borrow table. bookidx = collection table. bookidx and rid =: rid

2. assign values to parameters in the program running
(1) assign values to parameters using the Params attribute of TQuery based on the order in which parameters appear in SQL statements;

For example, insert a record in the borrow table
With Query1 do
Begin
SQL. clear;
SQL. add ('insert Into borrow table (bookidx, rid, rdate )');
SQL. add ('values (: bookidx,: rid,: rdate )');
Params [0]. AsString: = bookidxEdit. Text;
Params [1]. AsString: = ridEdit. Text;
Params [2]. AsDate: = date;
ExecSQL;
End;

(2) Call the ParamByName Method Based on the parameter name in the SQL statement to assign values to the parameter;
ParamByName ('bookidx'). AsString: = bookidxEdit. Text;
ParamByName ('rid '). AsString: = ridEdit. Text;
ParamByName ('rdate'). AsDate: = date;
ExecSQL;
Conversion functions include AsString, AsSmallInt, AsInteger, AsWord, AsBoolean, AsFloat, AsCurrency, AsBCD, AsDate, AsTime, and AsDateTime.

3. assign values to parameters using the data source
Set the DataSource attribute of TQuery to another data source (T DataSource name). Delphi compares unassigned parameters with fields in the specified data source, the value of the matched field is assigned to an unassigned parameter, which can be applied in the master table-Detail table.

4. Modify the dataset returned by TQuery
Generally, the dataset returned by TQuery is read-only and cannot be modified;
For single-Table SELECT queries that do not contain set operations (such as SUM and COUNT), if the RequsetLive attribute of TQuery is set to True, the dataset returned by TQuery can be modified.

varI: Integer;ListItem: string;beginfor I := 0 to Query1.ParamCount - 1 dobeginListItem := ListBox1.Items[I];case Query1.Params[I].DataType offtString:Query1.Params[I].AsString := ListItem;ftSmallInt:Query1.Params[I].AsSmallInt := StrToIntDef(ListItem, 0);ftInteger:Query1.Params[I].AsInteger := StrToIntDef(ListItem, 0);ftWord:Query1.Params[I].AsWord := StrToIntDef(ListItem, 0);ftBoolean:beginif ListItem = 'True' thenQuery1.Params[I].AsBoolean := TrueelseQuery1.Params[I].AsBoolean := False;end;ftFloat:Query1.Params[I].AsFloat := StrToFloat(ListItem);ftCurrency:Query1.Params[I].AsCurrency := StrToFloat(ListItem);ftBCD:Query1.Params[I].AsBCD := StrToCurr(ListItem);ftDate:Query1.Params[I].AsDate := StrToDate(ListItem);ftTime:Query1.Params[I].AsTime := StrToTime(ListItem);ftDateTime:Query1.Params[I].AsDateTime := StrToDateTime(ListItem);end;end;end; 

I. Opening and disabling a dataset table
Open: Set the Active attribute of the dataset component to True or call the Open method of the dataset component.
Close: Set the Active attribute of the dataset component to False or call the Close method of the dataset component.

2. Create a database application
Create using the Wizard: use the Database menu/Form Wizard option;
Create a master-slave table: Set the MasterSource and MasterField attributes of the slave table;
Create a query table: Use the TQuery component;

Iii. Positioning of database table records
Use the TDBNavigator component;
Call the First, Next, Prior, and Last methods of the dataset component;
The EOF attribute (or BOF attribute) of the dataset component is used to determine whether the Record Pointer Points to the first record (or the last record );
Use the BookMark of the dataset (GetBookMark: Get the BookMark of the current record; GotoBookMark: transfer the current record directly to the record of the specified BookMark; FreeBookMark: release a BookMark)
Use the GotoKey and FindKey methods to locate records;

Iv. Use of database table field objects

(1) create a permanent field object
Double-click or right-click the TTable (TQuery) object to open the field editor, use the pop-up menu to add a Field object, delete a Field object, and define a New field object (the New Fields option in the pop-up menu of the field editor allows you to create a calculated field );

(2) attributes, methods, and events of field objects
Field object name: such as Table1Name and Query1Sid
Attributes: Alignment, Calculated, DisplayLabel, DisplayWidth, and DisplayFormat) editMask, FieldName, ReadOnly, and Visible)
Event: OnChange (triggered when the field value changes), OnGetText (triggered when the field object obtains the field value), OnSetText (triggered when the field object is set the field value) onValiData (triggered when modification, insertion, and validation are performed)

(3) type conversion of field objects
Conversion functions include AsString, AsSmallInt, AsInteger, AsWord, AsBoolean, AsFloat, AsCurrency, AsBCD, AsDate, AsTime, and AsDateTime.
For example, Edit1.Text: = Table1Name. Value;
Table1Bdate. AsString: = DateToStr (DATE );

(4) access to field objects
Access to the dynamic field object: Table1.Fields [0]. DisplayLabel: = 'student number'
Table1.FieldByName ('sid '). DisplayLabel: = 'student No.' Table1.Fields [0]. Assignment: = taCenter
Edit1.Text: = Table1.FieldByName ('sid '). AsString
Permanent Field object access: Query1Sid. DisplayLabel: = 'student number'
Query1Sid. DisplayWidth: = 12

V. Operations on database table data

(1) how to access the data of a field in the table:
Table1.FieldByName ('bookidx'). AsString
Table1.Field [0]. AsInteger
Table1.Fieldvalues ['bookidx']

(2) database table attributes:
Current record number: Table1.Recno
Total number of records: Table1.RecordCount
The field name of the table is Table1.GetFieldNames (ListBox1. Items)

(3) data maintenance methods:
Edit Method: Set the dataset to Edit status;
Append method: sets the dataset to the insert status (last );
Insert method: sets the dataset to the Insert status (after the current record );
Post method: Write the modified records back to the dataset;
Cancel Method: cancels the current operation;
Delete: Delete the current record in the table;
AppendRecord method:
InsertRecord method: table1.InsertRecord (['20170901', NIL, NIL, 'test']);
SetRecords method:
Abort method: cancels the call of various methods;

(4) validity verification of input data
Establish a Validity verification mechanism for database tables (such as setting the Validity Check, Table Lookup, Referential Integrity, and other attributes of the Table in DBD );
In the field editing table Fields Editor (double-click the Ttable object), select a field and write its OnValidate event. If it is not empty, set its Required attribute to True;
Prevent invalid input in the Program (for example, TDBcombobox object and TDBlookupcombobox object );

6. Data Retrieval
(1) sort by index
For example, TABLE1.IndexName: = 'upa' or TABLE1.IndexFieldNames: = 'user _ id'
(2) Use the GotoKey method to find records in the database
An index is required for the search field. For non-primary indexes, you must set the IndexName attribute of the Ttable object.
Call the SetKey method to set the Ttable object to be searched to the search module;
Sends the search value to the Search Buffer of the queried Field;
Call the GotoKey method of the Ttable object and test the return value of the method to determine whether the search is successful;
(3) Use the FindKey method to find records in the database
Pass the search value as a parameter to the FindKey function. Multiple Search values are allowed. You must assign the index names of multiple fields to the IndexName attribute of the Ttable object;
(4) inaccurate search
GotoNearest Method
FindNearest Method
(5) use the Locate method to find records in the database (no index required)
Table1.locate ('field name 1; field name 2', VarArroyof (['value 1', 'value 2']), [LoCaseInsensitive, LoPartialKey])
LoCaseInsensitive: Case Insensitive; IoPartialKey: inaccurate search
(6) how to set the search range
SetRangeStart, SetRangeEnd, EditRangeStart, EditRangeEnd, SetRange ([Start Values], [End Value]), ApplyRange, CancelRange
(7) search using dynamic SQL statements of the TQuery component

7. Modify records in the database
You can perform operations on database records in a program according to the following steps:
(1) Move the data pointer to the record to be modified;
(2) Call the Edit Method to Edit the Ttable component;
(3) modify the field value; (Table1.Fieldvalues ['field name']: = value, Table1.Field [0]. AsString: = value)
(4) Assign null values to fields using Nil;
(5) call the Post method to write the modified records to the database;

8. insert and delete records
Delete: Move the pointer to the corresponding record and call the Delete method;
Insert: Call the Insert and InsertRecord methods (Insert at the current record) or Append and InsertRecord methods (Insert at the end of the table );


How to select a good database (comparison of the three major databases)
[Openness]

SQL Server: it can only run on windows without any openness. The stability of the operating system is very important to the database. The Windows9X series products focus on desktop applications, and the NT server is only suitable for small and medium enterprises. In addition, the reliability, security, and scalability of the windows platform are very limited. It is not as tested as unix, especially when dealing with key businesses with large data volumes.

Oracle: it can run on all major platforms (including windows ). Fully supports all industrial standards. Adopt a fully open policy. Customers can select the most suitable solution. Provide full support to developers.

DB2: it can run on all major platforms (including windows ). It is most suitable for massive data volumes. DB2 is the most widely used in enterprise-level applications. Of the 500 largest enterprises in the world, almost 85% of them use DB2 database servers, and about 5% in China to 97 years.


[Scalability and parallelism]

SQL server: the parallel implementation and coexistence model is not mature. It is difficult to process the increasing number of users and data volumes. Limited scalability.

Oracle: the Parallel Server expands the function of the mongonets by sharing the work of a group of nodes in the same cluster, providing high availability and high scalability cluster solutions. If windowsNT cannot meet your needs, you can move the database to UNIX.

DB2: DB2 has good concurrency. DB2 expands database management to a parallel, multi-node environment. Database partitions are a part of the database, including its own data, indexes, configuration files, and transaction logs. Database partitions are sometimes called nodes or database nodes.


[Security]

SQL server: no security certificate is obtained.

Oracle Server: gets the highest level of ISO certification.

DB2: gets the highest level of ISO certification.


[Performance]

SQL Server: poor performance when multiple users

Oracle: Highest performance, world record for TPC-D and TPC-C under windowsNT.

DB2: Suitable for data warehouses and online transaction processing with high performance.


[Client support and application mode]

SQL Server: C/S structure. It only supports windows users. You can use ADO, DAO, OLEDB, and ODBC for connection.

Oracle: multi-layer network computing, supporting a variety of industrial standards, can be connected by network customers such as ODBC, JDBC, OCI

DB2: cross-platform, multi-layer structure, supporting ODBC, JDBC, and other customers


Easy to operate]

SQL Server: simple operation, but only graphic interface.

Oracle: complicated. provides GUI and command lines at the same time and operates the same way in windowsNT and unix.

DB2: Easy to operate. It also provides GUI and command lines. The operations are the same in windowsNT and unix.


[Use risk]

SQL server: the code that is completely rewritten has been tested for a long time and is constantly delayed. Many features require time to prove. Not very compatible with earlier products. Use requires a certain risk.

Oracle: it has long development experience and is fully backward compatible. It is widely used. There is no risk at all.

DB2: It is widely used in giant enterprises and has good downward compatibility. Low risk.