Create advanced connections for MySQL and mysql connections

Create advanced connections for MySQL and mysql connections

SQL allows an alias for a table. The two main reasons for doing so are:

• Shorten SQL statements;
• The same table can be used multiple times in a single SELECT statement;

We use aliases to represent a table that corresponds to the previous one:

Auto join

Problem: If you know that the ID of a product produced by a vendor is DTNTR, you need to find out all other products produced by the vendor from the database.

The following SELECT statement can be used to find all products produced by the vend_id:

Now let's look at the same query using the join:

Create a foreign key connection in Mysql

Note the following when creating a foreign key:
1. innodb table engine must be used
2. The character encoding of the two tables must be consistent.
3. The referenced typeid, that is, the typeid in the parent table must be a primary key or unique

To meet the preceding requirements, the SQL statement is as follows:
--------------------------------
Create table father (typeid int primary key, name varchar (10) engine = innodb;

Create table son (id int, typeid int, foreign key (typeid) references father (typeid) engine = innodb;

If you only mean that two tables are joined, your SQL statement can be written like this.
Select * from father, son where son. typeid = father. typeid

How to establish a connection to the MYSQL database?

After installing the mysql odbd driver, how should I establish an ASP file to connect to the database? We have two methods: one is to create a DSN In the ODBC data source. The specific method is as follows: 1. Open the ODBD data source in the control panel; 2. Select the system DSN and add a new DSN driver; 3. Select MyODBD. A dialog box is displayed for entering MySQL: (1) Windows DSN name: name of the DSN to be created; (2) MySQL Host (name or ip): name or IP address of the MySQL server. Generally, enter LocalHost; (3) MySQL database name: Enter the database name. 4. Create a database in the MySQL management program. Take the database chunfeng as an example. There is a user data table with two fields: username and password. We can try to add several records: (1) user: user name used to connect to the database. You can enter the root Super User; (2) password: password used to connect to the database. Optional; (3) Port (if not 3306): MySQL Port on the server. The default value is 3306. (4) SQL command on connect: use SQL commands to connect to the database. Optional. After entering all the information, press OK to save. Okay, Let's connect to the database. The ASP code is as follows: <% strConnection = "dsn = chunfeng; driver = {mydbd driver}; server = localhost; uid = root; pwd =; database = chunfeng "Set adoDataConn = Server. createObject ("ADODB. connection ") adoDataConn. open strConnection strQuery = "SELECT * FROM user" Set rs = adoDataConn. execute (strQuery) If Not rs. BOF Then %>