At the lowest level, PHP can do anything other CGI programs can do, such as collecting table data, generating dynamic page content or sending and receiving cookies. Perhaps the most powerful and meaningful feature is that PHP supports a wide range of databases. Writing a web page that supports a database is very simple.
The following databases are currently supported:
Adabas d interlayer solid
Database mSQL Sybase
Queen MySQL Velocis
Oracle Unix database management system
Informix PostgreSQL
PHP also supports "dialogue" with other services through protocols, such as IMAP, SNMP, NNTP, POP3 and even HTTP. You can also open fuzzy network interfaces to interact with other protocols.
A brief history of PHP
1In the autumn of 994, Rasmus Lerdorf began to conceive PHP. Early unpublished versions were used on his home page to track who was reading his online resume. The first edition was released at the beginning of 1995. At that time, PHP was only considered as a personal homepage development tool. It consists of a very simple analysis engine, and can only understand some special macros and some common tools used at the back end of the home page, such as guest books, counters and other things. This parser was rewritten in the middle of 1995 and named PHP/FI version 2. FI comes from another package written by Rasmus. Data used to interpret html format. He combined the personal homepage tool script with the form parser and added mSQL support. Thus, PHP/FI came into being. PHP/FI develops at an amazing speed, and people begin to contribute their own codes to it.
It is difficult to give its hard statistics, but it can be estimated that by the end of 1996, at least 15000 websites in the world were using PHP/FI. By the middle of 1997, this number has exceeded 50. 000. At this time, the development of PHP also changed. The project developed by Rasmus himself and several people has become the result of a more organized group. Zeev Suraski and Andi Gutmans rewrote the parser. This new parser became the basis of PHP version 3. Many useful codes are inherited from PHP/FI to PHP3, and many of them are completely rewritten.
Today (65438+mid-0999), PHP/FI and PHP3 are bundled with many commercial products, such as C2-class Web servers and Red Hat Linux. According to the data provided by NetCraft, it is conservatively estimated that there are more than150,000 websites using PHP in the world. From this point of view, it is more than the websites running Netscape flagship enterprise servers on the Internet.
PHP is a server-side scripting language for making dynamic web pages. You create pages through PHP and HTML. When a visitor opens a webpage, the server will process the PHP instructions and then send the processing results to the visitor's browser, just like ASP or ColdFusion. However, unlike ASP or ColdFusion, PHP is a cross-platform open source code. PHP can be executed in Windows NT and many different Unix versions. It can also be compiled into Apache modules or CGI binaries. It is very light to compile PHP into Apache module. It doesn't have any burden brought by tedious procedures, and it can return results quickly, and it doesn't need to adjust mod_perl to keep a small server memory image.
1. Introduction
What is SQL?
* SQL (structured query language) stands for structured query language.
* When a user sends a query, he can get some information from the database file. The query is a search based on the conditions provided by the user. SQL is a query language that allows users to list conditions. In this way, users only need to specify the query conditions, without actually knowing the relevant retrieval methods.
* Common database management software system (DBMS) includes SQL function.
The concept of SQL
* Through SQL instruction, the user first lists the database files and query conditions, and the SQL program will check whether each record in the database file meets the conditions and display relevant information. This process is called retrieval. (See Example 2)
* In addition to directly querying the data of each record, users can also query several statistical items, such as maximum, minimum, sum and average.
* The query results will be displayed in tabular form, and the user can also instruct the SQL program to store the results as a database file.
How to use SQL in FoxPro
* To use SQL, you must first open the relevant database files.
* Users can use the command window to directly input commands, or use the dialog box to input commands.
* If users choose a complete pairing of strings, they must enter SET ANSI ON.
2. Basic structure of 2.SQL retrieval instruction
General syntax SELECT, ALL/DISTINCT, *,
Where did you come from?
Compare IN, BETWEEN, like "%_"
Group by, own,
Count (), Sum (), AVG (), Maximum (), Minimum ()
Display order order basis, ASC/DESC
Logical operatORs AND, or, NOT
Output to table/cursor.
File [additive], printer, screen
United trade union
Abbreviations:
Expr = expression, groupexpr = group expression.
Col = column, comcol = common column.
Colname = column name columnname
Nullval = null value nullvalue
Example: Students' Personal Information
Example: Consider the following database file STUDENT. DBF for storing student data:
(i) hcode indicates the names of students' clubs (namely, four clubs of red, yellow, blue and green).
R = red, Y = yellow, B = blue, G = green.
(2) dcode indicates the area code of the student's residence.
E.g. TST = Tsim Sha Tsui), MKK = Mong Kok.
(3) The remission indicates whether the students are entitled to tuition remission:
. T. = Enjoy tuition fee remission. F. = No tuition fee remission.
(4) mtest stores students' math test scores, with full score 100.
Column Name Type Column Width Content
ID number 4 student number
Name Character 10 Student Name
Date of birth 8 Date of birth
Gender characteristics 1 gender: male/female
Category Character 2 Category
Hcode character 1 social name: r, y, b, g.
Dcode character 3 area code
Reduction logic 1 tuition fee reduction
Score of the second math test
I. General grammar
Pick ... from ... where? ......
SELECT[ALL/DISTINCT]expr 1[AS col 1]，expr 2[AS col 2]；
FROM tablename WHERE condition
–The -SQL program will select eligible rows from the database file tablename and display them in tabular format.
–the expression expr 1, expr2 can be a (1) field, or (2) an expression consisting of a function and a field.
–and col1,and col2 is the column name of the expression expr 1, expr2 in the output result table.
–The option DISTINCT will delete duplicate rows (that is, it will only be displayed once), while the option ALL will keep all duplicate rows.
–Conditional conditions can be (1) equations or inequalities, or (2) string comparison, using logical operators and, or, NOT.
Before using SQL, please open the database file:
Use students
Example 1 Find the data of all students.
Choose from students *
Note: 1) This instruction does not put forward any conditions, so the WHERE part is omitted.
2) Use * in the selection section to indicate all columns in the picking source table.
3) The query results will be stored in a temporary table.
Identification (identification)
Name, date of birth, gender category mtest hcode dcode mitigation
Peter 06/04/86 m1a70rssp.f.
9802 Mary 0110/86 f1a92yhhmf
9803 Johnny 03/16/86m 1A 9 1 g ssp.t.
9804 Wendy 07/09/86 F 1B 84b YMT F
YMT, zip code: 9805
: : : : : : : : :
Example 2 Find out the names and club names of students in 1A class.
Select the name, code and class from the students;
Where class=" 1A "
Note: this example uses the condition class=" 1A "to select students from class 1A: the SQL program will check whether this condition is met from each row in the source table one by one. Then the SQL program will keep three columns in these columns according to the option of SELECT, namely name, hcode and class. Finally, the program will store the results in a temporary table.
name
Hcode class
Peter R 1A
Mary Y 1A
Johnny G 1A
Luke G 1A
Bobby B 1A
Aaron R 1A
: : :
Example 3 Find out where the members of the Red Cross live. (hcode="R ")
Choose different data codes from students;
Where hcode="R "
Note: If two or more students live in the same area, use the DISTINCT option to ignore duplicate results.
data code
HHM
KWC
MKK
Statutory sick pay
Test (test)
Taiwan Province shanye
Example 4 Find out the names and ages of the girls in Class1B. (accurate to one decimal place)
Select names from students, and ROUND((DATE( )-dob)/365, 1) is the age;
Where class=" 1B "and sex="F"
Note:1) The condition of "1b class girl" includes two parts: class=" 1B "and sex="F". AND this condition must be met at the same time, so the logical operator and is used.
2) There is no column directly storing the age in this database file, so we need to use the student's date of birth dob to calculate. First, DATE( )-dob means the student's "year old", and then divided by 365, it becomes "year old". Then use the option AS age to explain the name of the column.
name
age
Wendy 12. 1
Kitten 1 1.5
Janet 12.4
Sandy 12.3
Mimi 12.2
Example 5 Find out the names and numbers of students in class 1A who have no tuition fee remission.
Select the name, id and class from the students.
Among them, class=" 1A "and it is not relieved.
Note: 1) There are two conditions here: students must be in 1A class and do not enjoy tuition remission. Therefore, add the operator AND to the WHERE section.
2) As reminder is a logical field, it can be directly used in logical expressions. Adding NOT before the reminder reverses the meaning.
name
Id category
Peter 980 1 1A
Mary 9802 1A
Luke 98 10 1A
Bobby 98 1 1 1A
Aaron 98 12 1A
Ron 98 13 1A
Gigi 9824 1A
: : :
Two comparisons
Expression input (value 1, value 2, value 3)
Expression between the value 1 and the value 2.
An expression similar to "%_"
–In the WHERE section, you can use the above comparison terms:
1) If the value of expr is equal to one of value 1, value 2 and value 3, the clause exprin (value 1, value2, value3) will return a logical value. Expr can be a numeric value or a string.
2) If expr is between value 1 and value2, the clause
Expr between the value 1 and the value 2 will return a logical value.
3) If the string expr conforms to the pattern of "%_", the clause exprlike "%_" will return a logical value. In the style, "%"represents a string of any length, while "_" represents any single character.
Example 6 Find out all the students born on Wednesday or Saturday.
Select the name, class and CDOW (date of birth) from the students as the date.
DOW(dob) in (4, 7)
Note: If students are born on Wednesday and Saturday, DOW(dob) will send back the value of 4 or 7, so IN (4 4,7) will be used for checking.
name
Class b date
Peter 1A Wednesday
Wendy 1B Wednesday
Saturday
Luke 1A Wednesday
Aaron 1A Saturday
: : :
Example 7 Find out all the students who were not born in January, March, June or September.
Select the name, class and date of birth from the students;
Where the month (date) is not in (1, 3, 6, 9).
Note: We don't want MONTH(dob) = 1, 3, 6 or 9, so we use NOT IN (1, 3, 6, 9) to check.
name
Dob class
Wendy 1B 07/09/86
Tobe 1B 10
Eric1C1May 5, 987
Patty 1C 08/ 13/87
Kevin1c11/21/87
Bobby 1A 02/ 16/86
Aaron1A1February 8, 986
: : :
Example 8 Find out the names of the students in class 1A, and the scores in the math exam are between 80 and 90.
SELECT name，mtest FROM student
In which class=" 1A "and mtest are between EN 80 and 90.
Note: 1) Two conditions are used here: the first is class=" 1A ",and the second is that the test score is between 80 and 90. These two conditions must be true at the same time, so you must use AND.
2) the test scores are between 80 and 90, which can be expressed by mtest BETWEEN 80 and 90.
name
mtest
Luke chapter 86
Aaron 83
Gigi 84
Example 9 Find all the students whose names begin with "T".
Choose names and classes from students;
Names like "T%"
Note: The style used here is "T%", which means that the first character must be "t", and then it can be any string.
name
classes
Toby 1B
Teddy 1B
Tim 2a
Example 10 Find out that the second letter of all members of the Red Cross is "A".
Select the name, class and code from the students;
Where the names are "_a%" and hcode="R "
Note: The style used here is "_a%", where the "_" symbol represents any single character, that is, the first character is arbitrary. The second character must be "a", followed by "%"for any string.
name
Hcode class
Aaron 1A R
Janet 1B R
Paula 2a R.
Third groups
Where are the conditions for selecting ... from ...?
GROUP BY GROUP expr[ on request]
Grouping functions: COUNT (), SUM (), AVG (), MAX (), MIN ()
--GROUP BY GROUP expr lists the expressions that make up the group. It is usually a column in a database file.
–-where condition lists the conditions that each row must meet, while HAVING requirement lists the conditions that each group must meet.
–You can use the group function to calculate statistics:
COUNT (): records the number of items that appear.
SUM (): sum
AVG (): average value
MAX (): maximum value
MIN (): minimum value
Example 1 1 Find the number of people in each class.
Choose a class and count from the students (*)
Group by category
Note: 1) With GROUP BY class, the SQL program will first arrange the rows in the table according to the class.
2) Then the SQL program will group the connected rows according to the class.
3) Finally, count the number of each group with the grouping function COUNT(*).
classes
(cannot) can't
1A 10
1B 9
1C 9
2A 8
2B 8
2C 6
Example 12 Find the average math test score of each class.
Select the class from the student group according to the class, AVG(mtest)
Note: the SQL program will first group by class, and then calculate the average mtest of the groups in each group.
classes
Average average test time
1A 85.90
1B 70.33
1C 37.89
2A 89.38
2B 53. 13
2C 32.67
Example 13 Find the number of girls in each community.
SELECT dcode，COUNT(*)FROM student；
Where sex="F "is grouped by data code.
Note: the query condition is sex="F ". The SQL program will first select the rows that meet this condition, and then group them according to dcode.
data code
(cannot) can't
HHM 6
KWC 1
MKK 1
SSP 5
TST 4
YMT 8
Example 14 Find the highest score and the lowest score of a student in the math exam in each district.
Choose maximum (mtest), minimum (mtest) and dcode from students.
In which classes such as "1_" are grouped by dcode.
Maximum number of tests
Minimum test code
92 36 HHM
9 1 19 MKK
9 1 3 1 SSP
92 36 TST
75 75 TSW
88 38
Note: In this case, a conditional class like "1_" is used to select senior one students in each district.
Example 15 lists the average scores of boys' math exams in each class, but classes with fewer than 3 boys are not counted.
Choose AVG(mtest) from students, class;
Where sex = "m" group by class having count (*) >; = 3
Note: The SQL program will first sort out the lines that meet the individual conditions of sex="M ",and then group them by class to calculate the average mtest of each group. Finally, the condition count of the SQL program inspection team (*)= 3. (In this case, the number of boys in Class 2C is less than 3, so it is not shown in the results. )
Average average test time
classes
86.00 1A
77.75 1B
35.60 1C
86.50 2A
56.50 2B
IV display order
Select ... from ... where ... grouping basis ...;
Order by colname ASC/DESC.
–-order by colname controls the order in which the results are displayed. Colname represents a column in the result table. ASC = rising power, DESC = falling power.
Example 16 List the names of boys in class 1A, in order of their names.
Select the name and id from the students.
Where sex="M "and class=" 1A" are sorted by name.
Name id name id
Peter (male name)
980 1 Aaron 98 12
Johnny 9803 Bobby 98 1 1
Luke 98 10 Johnny 9803
Bobby 98 1 1 Luke 98 10
Aaron 98 12 Peter 980 1
Ron 98 13 Ron 98 13
Example 17 lists the student data of Class 2A, and displays them in order of residence.
Select name, id, class and data code from students.
Where class="2A "is sorted by dcode.
Name id class dcode
2A·HHM
2A·HHM
Samur 97 14 2A Time
Rosa 9703 2A SSP
Helen 9702 2A TST
Joseph 97 15 2A TSW
Paula 970 1 2A YMT
Susan 9704 2A YMT
Example 18 Find out the number of students living in each district and display them in descending order.
SELECT COUNT(*) as cnt and dcode FROM student.
According to the code order of DESC center
(cannot) can't
Document code
1 1 YMT
10 HHM
10 SSP
9 MKK
5 TST
2 TSW
1 KWC
1 MMK
1 shilling
Example 19 lists the names of male members of various societies and displays them in class order. (that is, the two-level order of clubs and classes)
Select the name, code and class from the students;
Where sex="M" ORDER BY hcode, class.
Note: these lines are arranged by hcode first (that is, the first layer is arranged); And display the same hcode in class order.
Named hcode class
police
B 1A
Teddy B 1B
Joseph 2a
Zion 2b
Leslie B 2C
Johnny G 1A
Luke G 1A
Kevin G 1C
George G 1C
: : :
: : :
Five outputs
INTO table tablename
Store the query results in a database file.
Enter cursor temperature
Temporarily store the query results in the working memory of the computer.
To File Name [Attach]
Store the query results as a text file. (additive = additional)
Print the output to a printer.
Output the screen to the screen.
Example 20 lists the data of all students in descending order according to their names, and stores the results as the database file name. DBF.
SELECT * FROM student
ORDER BY name DESC is put into table name.dbf
Note: 1) Importing the table name. dbf requires an SQL program to save the result as a file.
2) This instruction is equivalent to the database instruction SORT.
The SQL program stores the results as a new database file name. dbf.
Identification (identification)
Name, date of birth, gender category mtest hcode dcode mitigation
9707 Zion 07/29/85m 2B 5 1 B MKK.
9709 yvonne 08/24/85 F 2C 10 R TST.
9804 Wendy 07/09/86 F 1B 84b YMT F
MKK, zip code: 9865438
YMT, zip code: 9805
2A 9 1 R HHM。
98 16 teddy 01/30/86m1b64bssp.f.
：
: : : : : : : :
Example 2 1 Print out the information of Red Cross members in order of rank, gender and name.
Select the class, name and gender from the students;
Where hcode = "R
Sort by class, gender, DESC and printer name.
Note: 1) This instruction requires the program to find out the members of the green society first, and then arrange these columns in order of class, gender and name.
2) The SQL program first temporarily stores the query results in the working memory, and then outputs the results to the printer.
classes
Tell the gender
1A
Aaron m
1A
Peter m
1A Ron M
1B is m.
Janet F.
1B Kitty F
1B
Mimi f
: : :
3. Database merging, intersection and difference
Consider two database files A and B with the same structure.
Combination of a and b
(1) Trade unions
Retrieves all rows that belong to a or b.
The intersection of a and b
crossroads
Check the rows shared by a and b.
The difference between a and b
(a–b) difference gets rows that only belong to a but not to b. (That is, a part of B is discharged from A)
Choose ... from ... where ...;
Trade unions;
Pick ... from ... where? ......
Select ... from table1;
Where col IN (col is selected from table 2)
Select ... from table1;
Where the column is not present (select a column from Table 2)
For example: bridge club and chess club.
Consider the members of the school bridge club and the chess club, whose data are stored in the same structural database file Bridge. DBF and chess. Dbfs are:
Column Name Type Column Width Content
ID number 4 student number
Name Character 10 Student Name
Gender characteristics 1 gender: male/female
Category Character 2 Category
Bridge chess
ID card name gender category ID card name gender category
1 98 12 aaron M 1A 1 9802 Mary F 1A
2 980 1 Peter M 1A 2 980 1 Peter M 1A
3 98 14 Kenny M 1B 3 98 15 Eddie M 1B
4 9806 kitty f1b 4 9814 Kenny M 1B
Edmund M 18 George M 1C
: : : : : : : :
Before using SQL, please open these two database files:
choose one
Use the bridge
Option b
Use chess
Our school plans to hold a "chess bridge activity", and all members of the chess club and the bridge club must participate. Try to list the members of the two associations in order of class and name. (that is, the joint of the two sessions)
SELECT * FROM bridge
Trade unions;
SELECT * FROM chess
Sort by class, name into the table party
Note: What is needed is all members of the two associations, not a combination of the two associations.
gathering
Id name gender category
1 98 12 aaron M 1A
2 9802 Mary F 1A
3 980 1 Peter M 1A
4 98 15 Aidi M 1B
5 98 14 Kenny M 1B
6 9806 Kitty F 1B
7 98 18 Edmund M 1C
8 98 17 George M 1C
: : : :
Example 23 Print ordinary members of NPC and CPPCC. (that is, the intersection of the two sessions)
SELECT * FROM bridge
Where id IN (select id from chess);
To printer
Note: In this case, we must find out the common members between the two associations. The SQL program must check whether each member of the bridge club also belongs to the chess club. If yes, this person meets this requirement.
ordinary
Id name gender category
1 980 1 Peter M 1A
2 98 14 Kenny M 1B
: : : :
Find out the list of members who only participate in the bridge club. (that is, the difference between the two sessions)
SELECT * FROM bridge
Where the id is not present (choose ID from chess);
INTO table difference
Note: 1) In this case, people who do not belong to the chess club must choose from the bridge club. Therefore, the SQL program must use FROM bridge, that is, check whether the members of the bridge club belong to the chess club one by one. If not, this person meets this requirement.
2) The "difference" is asymmetric: if you want to find out the "list of members who have only participated in the chess club", the result will be completely different.
differential mechanism
Id name gender category
1 98 12 aaron M 1A
2 9806 Kitty F 1B
3 98 18 Edmund M 1C
: : : :
4. Multiple databases
When the queried data is stored in two database files, a connection is needed. The function of join is to join a row in one database file with a row in another database file, thus listing all the different combinations. (Mathematics: Cartesian product)
Natural joint
* Add a joint condition in the joint, requiring the values of the common columns of the two gears to be the same. This is called a natural joint. The purpose of this paper is to merge the related information of these two files into a large integrated table, and then perform the query work from this table.
Consider the following two database files T 1 and T2:
SELECT a.comcol，a.col 1，b.col2，expr 1，expr2
From table 1 a, table 2b;
Where a.comcol = b.comcol
–In a natural join, one column in two database files must be the same, which is called a common column. The SQL program will first list all the combinations of these two files, and then select the rows with the same common column values.
–A and B are the code names of table 1 and table2 respectively, which are used to indicate the files to which each column belongs.
–The expression expr 1, expr2 can use the columns in table 1 and table 2.
For example: musical instruments
The school stipulates that every student should learn a musical instrument. Now, the database files music. DBF is used to store the names of musical instruments that students have studied (while other materials of students are stored in STUDENT. DBF)。
Column Name Type Column Width Content
ID number 4 student number
Type character 10 instrument name
Example 25 lists the names of all the students and the names of the instruments they have learned.
Choose s.class, s.name, s.id, m.type from students s and music m;
Where s.id=m.id ORDER BY class, name.
Note: 1) where s stands for student.dbf and m stands for music.dbf
2) The meshing condition is s.id=m.id, that is, the rows of two gears must be meshed according to id.
classes
Name id type
1A Aaron 98 12 Piano
1A bobby 98 1 1 flute
1A gigi 9824 recorder
1A Jill 9820 piano
1A Johnny 9803 violin
Luke 98 10 piano
1A Mary 9802 flute
: : : :
Find out the number of students studying piano in each class.
SELECT s.class，COUNT(*) FROM student s，music m；
Where s.id=m.id, m.type = "Piano.
Group by class Sort by class
Note: in 1), firstly, two gears are naturally engaged, and the engagement condition is s.id=m.id
2) Then, the SQL program selects the line that meets the condition m.type="Piano "from the linked results (in tabular form).
3) Finally, the SQL programs are grouped by classes and the number of classes is counted.
Category count
1A 4
1B 2
1C 1
External joint
* External engagement is natural engagement plus non-matching parts.
SELECT a.common，a.column 1，b.column2，expr 1，expr2
From table 1 a, table 2b;
Where a.com col = b.com col;
Trade unions;
SELECT comcol，col 1，nullval，nullval，null val；
From table1;
Where a.comcol is not available (select comcol from Table 2).
–The description of the external joint consists of two selected parts, which are combined by the joint.
–The first part is naturally joined (that is, successfully joined), and the second part is not joined (that is, the line that the first part cannot be naturally joined).
–In order to make the structure of the second part the same as that of the first part, the positions without numerical values in the second part must be filled with null values, that is, empty string "",numerical value zero and logical value. F or empty date {//}.
Example 27 lists the names of students who have not yet chosen musical instruments. (i.e. mismatch)
Select the class, name and id from the students.
Where the id is not there (choose ID from music);
Sort by category and name
Note: 1) This command will select those lines from student.dbf that do not match in another file, music.dbf.
2) (Select id from Music) Select those ids that appear in the database file music.dbf, so unpaired rows can be retrieved if the condition ID is not (...).
classes
Name id
1A mandy9821
1bKenne9814
1B becomes 9805.
1C edmond9818
George 98 17
: : :
Make a list and check the musical instruments that all students learn. The list must include the names of students who have not yet attended the musical instrument class. (i.e. external participation)
SELECT s.class，s.name，s.id，m . type；
Stu gave it to me.