Database Connection sorting and database sorting

Database Connection sorting and database sorting

Connections can be divided into internal connections, external connections, cross connections, and self-connections.

1. Cross join

1) supported databases: ORACLE, MYSQL, SQL Server, and DB2.

2) Function Description: The result returned by the cross-Join Operation contains a combination of records in the connected two tables. The number of records contained in the returned results is the product of the number of rows that meet the condition of the two tables. After cross join, only the WHERE clause can be used and the ON clause cannot be used.

3) underlying implementation and performance: cross join first produces the Cartesian Product tables of the two tables. If the table contains the WHERE clause, the results are filtered out from the Cartesian Product table (temporary table) based on the constraints, if the WHERE clause is not included, the Cartesian Product table is directly returned. Because cross join requires the production of cartesian products, the query speed is slow and has no practical significance. Therefore, it is often replaced by external connections and internal connections.

2. left join/right join, FULL JOIN

1) supported databases: ORACLE, MYSQL, SQL Server, and DB2.

2) Function Description: left join/right join and full join are external connections. In many databases, you can also write left outer join/right outer join and full outer join, left join (right join) returns results no matter whether the LEFT (RIGHT) Table record meets the conditions, it will return records in the LEFT (RIGHT) Table, LEFT (RIGHT) the connection contains all the records that meet the conditions in the two tables and the records that do not meet the conditions in the left (right) table. If no corresponding record exists in the right (left) Table, NULL is displayed. The return result of full join contains all the records that meet the conditions in the two tables and the records that do not meet the conditions in the two tables. The corresponding vacancy is displayed as NULL.

3) underlying implementation: The left and right connections generate temporary tables based on the left and right tables.

3. NATURAL JOIN

1) supported databases: ORACLE.

2) Function Description: if the field names and Data Types in the two tables are the same, natural join will automatically JOIN the two tables. If the field names are the same and the types are different, an error is returned.

4. INNER JOIN

1) supported databases: ORACLE, MYSQL, SQL Server, and DB2.

2) Function Description: In general databases, the default connection is inner join, and only records meeting both conditions are returned.

3) underlying implementation and performance: inner join and on are used together to produce temporary tables that meet the conditions. Therefore, innner join has a high performance.

5. Self-connection

1) supported databases: ORACLE, MYSQL, SQL Server, and DB2.

2) Function Description: self join connects itself to itself through a table to obtain special functions. In fact, self join is used for other connections, but only one table is used for objects. You can use aliases to connect to the same table.

 

The example is not complete. It needs to be improved ......

Which mysql check connection is selected when php creates a database in phpmyadmin? MySQL localhost new database

Generally, the UTF-8 format is utf8_general_ci to see what format your website is! Just select the format!

How does asp connect to the database?

1. Access database DSN-less connection method:
Set adocon = Server. Createobject ("adodb. connection ")
Adoconn. Open "Driver = {Microsoft Access Driver (*. mdb)}; DBQ =" & Server. MapPath ("database path ")

2. Access ole db connection method:
Set adocon = Server. Createobject ("adodb. connection ")
Adocon. open "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" & Server. MapPath ("database path ")

3. SQL server connection method:
Set adocon = server. createobject ("adodb. recordset ")
Adocon. Open "Driver = {SQL Server}; Server = (Local); UID = ***; PWD = ***; database = database name ;"

4. SQL server OLE DB connection method:
Set adocon = Server. Createobject ("adodb. connection ")
Adocon. open "provider = SQLOLEDB.1; Data Source = RITANT4; user ID = ***; Password = ***; in1_catalog = database name"
　　
5. Oracle connection method:
Set adocon = Server. Createobject ("adodb. connection ")
Adocon. open "Driver = {microsoft odbc for oracle}; server = oraclesever. world; uid = admin; pwd = pass ;"

6. Oracle ole db connection method:
Set adocon = Server. Createobject ("adodb. connection ")
Adocon. open "Provider = OraOLEDB. Oracle; data source = dbname; user id = admin; password = pass ;"

7. dBase connection method:
Set adocon = Server. Createobject ("adodb. connection ")
Adocon. open "Driver = {microsoft dbase driver (*. dbf)}; driverid = 277; dbq = ------------;"

8. mySQL connection method:
Set adocon = Server. Createobject ("adodb. connection ")
Adocon. open "Driver = {mysql}; database = yourdatabase;
Uid = username; pwd = yourpassword; option = 16386 ;"

9. Visual Foxpro connection... the remaining full text>