Cross-platform database design _ MySQL

Cross-platform database design developers should know that using non-standard SQL commands (such as Oracle, Microsoft, MySQL, and other database systems) is highly risky. That is to say, from the perspective of cross-platform and compliance with standards, you should try to adopt ansi SQL, which is a database language unrelated to the platform. No matter which database system you are using, if it fully supports SQL, it should support ANSI SQL-92 standards. However, "should support" and "true support" are totally different. Not all systems fully support the ansi SQL standard during cross-platform DBMS programming. This article focuses on the risks and related suggestions when using non-standard SQL statements.

Do not use stored procedures

In some programming environments, stored procedures become the only way for programmers to develop database applications for efficiency and security reasons. Visual Basic, C, and Java programmers do not need to understand SQL. However, in some other programming environments, stored procedures are completely disabled. MySQL is usually used in combination with Apache Web servers because these two software are free, reliable, and powerful. However, MySQL does not support stored procedures.

--------------------------------------------------------------------------------

Definition of stored procedure
A stored procedure is a set of SQL statements that constitute a logical unit and execute special tasks. Stored procedures are used to encapsulate a complete set of operations or queries for database servers.

--------------------------------------------------------------------------------

Both Oracle and SQL Server include "built-in" stored procedures, which are convenient to use. However, if wise, never count on them. you cannot ensure that the database administrator has locked, deleted, or modified these stored procedures. If you find that some of the available stored procedures are exactly what you need, you can use them in your own code.

Generally, you can use stored procedures on Oracle or SQL Server, but cannot use them on MySQL. If you have used them, you must ensure that you have not used commands related to a specific platform (for further discussion ).

Do not use connection
Connection is a headache between Oracle and SQL Server DBMS because the two systems have different connection concepts. Basically, the connection principle of each DBMS is not always the same, and you may eventually get an unexpected result set. Before talking about the concept differences of connections between systems and how to solve these problems, you should understand the basic connection clauses:

Connection
Join is the SQL data table creation command for retrieving data from multiple data sources.
And other connections
Equi-Join obtains data from two independent data sources and combines them into a large table.
Internal/external connections
Inner Join connects the internal columns of two tables. The outer join connects the external columns of the two tables.
Left/Right join
Left Join joins the columns on the Left of the two tables. Right join connects the columns on the right of the two tables.
Composite/complex connection
Other connections include left/inside, left/outside, right/inside and right/outside.
Remember, if you want your applications to work on a variety of database servers, do not use connections.

Ansi SQL: CREATE, DROP, ADD, UPDATE, DELETE, INSERT, SELECT
In short, the only command you should use is as follows:

CREATE and DROP in the data definition language (DDL.
ADD, UPDATE, DELETE, and INSERT in the data operation language (DML.
SELECT to obtain data.
If you use case-sensitive table or field names in these commands, or these names contain spaces or other special characters, you 'd better enclose the names in square brackets. This helps prevent non-standard characters from causing abnormal DBMS responses.

Integrity of reference: Key and data type

The decisive reason for using relational databases is to clarify the relationships between data and maintain the integrity of those relationships. In this way, developers can store data in the most efficient and minimal redundancy mode. The key defines the link. Data types define rules for storing and operating data. These are the basic aspects of a database, but the specific syntax may vary depending on the database.