Databases, as options for concentration of information, are vital, and even an insignificant project cannot do without them. How efficiently the structure is thought out, the tables are correctly designed, the connections are set up - the speed of information sampling / recording, the reliability of data storage, the guarantee of the impossibility of accidentally causing damage, the availability of rollback, the completeness of each transaction, and the success as a whole depend on it.
The database, first of all, is a conceptual idea, the presentation of information and the logic of its use in a specific task.
Simple Relationships = Effective Implementation
Relational ideas for organizing data are definitely not the best, but they have confidently held their position for many decades. As factories producing goods and essentials for consumer goods, they are certainly necessary.
Revolutionary ideas in the information business have repeatedly stated their right to life, but to this day they have not been able to provide significant evidence of the existence of such a right. As always, simplicity is the key to success. However, the topic of relations and connections between information objects - points and systems in the information space - is still full of white spots and interesting surprises.
Using a simple MySQL create table construct leads to the formation of dozens of simple and easy-to-use tables that formalize the scope of the task. It is very simple to start using the “muscle”, but after the first ten tables, relational relations give rise to a different attitude to information, its presentation, storage and use.
Temporary, controlled and permanent
A characteristic moment: the “fundamental construction” of the database concept did without MySQL, which came to life with Internet programming and became an essential part of it. Dynamic modern technologies have allowed this database to become fast, reliable and efficient, easy to learn and use.
In addition to the main tables, the create temporary table construction MySQL provides the programmer with the ability to create temporary data structures that exist during the current session, the visit of a specific user, or the appearance of a specific event.
If earlier tables and relations between them were treated as something unshakable, today it is no longer accepted even to assign powerful databases to fundamental objects. This is just a step towards understanding the information structure needed to solve the problem.
Providing a tool to create temporary tables create temporary table, MySQL makes it possible to divide the database into static and dynamic components. By indicating the types of table fields , the programmer can automatically control the correctness of its formation. Using the MySQL create table check construct, the developer can specify the conditions for filling in the fields. Although this option is rather an opportunity to facilitate the migration of SQL constructs from other dialects.
Information Space: Points and Systems
Each project, whether it is a whole site, one page or one simple algorithm, is located in a certain place of a certain information space. The “address” of this place is a specific task, the conditions for the existence of the algorithm: the information structure at the input and output, the form of transformation in the process.
However, it is not necessary to follow the rules of MySQL; create table may not create at all what is provided by its syntax.
In this example, the code_back, owner_code, and session_code fields define the keys for return, client, and session, respectively. Not only the use of the non-standard version of MySQL create table, the key here does not lie in its field of competence, but is written according to the meaning: return code in case of transaction completion, the active client code and session code, which may be several for this client, but depends on it, where control returns to the return code.
The use of the h_code, a_contents, and a_surprise fields is also non-standard here. Here in the create table MySQL example of how a single field can contain not data, but semantics. In any situation where you need to have a client table, it is quite reasonable to create a cache of the active part of this table. Moreover, it is not at all necessary to do this adequately to the original data structure. So, for example, the a_contents field will receive the contents: name; password; e-mail, and the a_surprise field - the current state of the user.
The idea of a_contents and / or a_surprise
This option does not require access to the main table, which can be large and cumbersome, but will allow you to restrict yourself to small, containing only relevant information. Here the create table MySQL is an example of how you can make a client login by name and password, or password and e-mail in a simple condition: (a_contents like '{$ cUserName}; {$ cUserPass}%') or (a_contents like '% {$ cUserPass}; {$ cUserName} ').
This example uses its own function to check for the presence of a table: scTableExists ('rm_cache') since if there is no table, you will need to not only create it, but also record, for example, several initial records with the data of the resource administrators.
The standard create table if not exists version of MySQL offers a limited version to check for the presence of a table and its creation in case of absence. This is usually not enough. It is necessary not only to check the availability of the table, but also to fill it with initial information.
Simple cell
The specificity of the languages used by MySQL, in particular PHP, traditionally minimizes the amount of popular data: first of all, by the non-typing of its variables. Most often, strings are used, as necessary - numbers and dates, sometimes logical data. A wider scope for describing data in MySQL is rarely created by table. Although the latter is determined by the problem being solved. It is possible to use the entire range of data through create table MySQL, an example is any modern site content management system.
What and how to use is determined by the developer. At the moment, according to Wikipedia, the bulk and speed capabilities of MySQL can satisfy the most capacious fantasies of the customer, taking into account the experience of the developer. When used on MySQL, create table default constructs allow you to create clean (empty tables) with variable-length cells and generate dynamic content in the process.
Complex data structures
Orientation towards simple data types, primarily strings, has led to an increase in interest in the construction of complex information objects, which when working with a database turns into storing mostly strings, numbers (indexes - most often), dates and "big data" - pictures , sounds, other capacious content. Here, the dialect of create table SQL MySQL is practically unlimited. Despite the simplicity and accessibility of the “muscle”, the programmer has at his disposal all the power of a real SQL language.
An externally simple cell from the point of view of the database, a char or varchar cell turns in the algorithm when reading into a full-fledged object - a complex data structure (properties and methods, syntax and semantics), and when it is written back into a compact form, the essence of which is determined by the developer.
Elementary table
Only columns, only lines, and only one element at their intersection, and only one type - a cell. This is the basic rule of the MySQL "create table" construct. No MySQL data type is original: numbers, strings of characters, dates ... This does not follow from the syntax, but it turns out in practice.
The programmer, interpreting a cell, can admit an object of any type in it, due to the requirements of the task. From the database point of view, this "any type" is just a string.
However, it does not matter what and how the developer prefers to use. It is important that at the current time (according to Wikipedia), the volume and speed parameters of MySQL can satisfy the most capacious fantasies of the customer, taking into account the experience of the developer used.
The simplest tables, arranged as simple as possible, turn out to be “tenacious”. If the developer puts the appropriate meaning in each field, wrapping it in a char or varchar type within the framework of the established MySQL create table syntax, then the table will work.
Integer table
It is worth talking separately about indexes in MySQL. Create table allows you to immediately describe and use them to access large amounts of data. This approach is very explainable, familiar and worked out to such trifles that optimization depends on processor speed, caching samples, tricky counting algorithms, often selected data, editing php.ini, my.ini and httpd.conf.
Meanwhile, no one bothers the developer to create his own idea of “about data” - “keys to data”, for simplicity, integer - not a single row in the table, only numbers. The microproject create table int MySQL will provide all the necessary other tables: quick access, simple operations, full functionality at ideal speed.
Integers, organized as a map of real data in large and bulky tables, without problems will speed up the selection and modification of information. It is not necessary to create such a key access option for the entire time of work; you can use the create temporary table construct, MySQL will create a temporary table before using it. Or, for example, for each user - their own key table.
In this context, the syntax not specified use of create table if not exists MySQL allows you to create the necessary tables even if they are unexpectedly absent: for example, there was an incorrect input / output or computer failure, and indexing was broken.
Hosting Realities - MySQL +
Historically, the database has been a meaningful data structure. The design of M ySQL create table initially adhered to the classical concepts of data and their place on disk space. Internet programming, and above all hosting options and the logic of popular site management systems, made a strange, at first glance, bias: one database can be a combination of several. Separation follows prefixes of table names.
Some hosting providers have imposed their requirements on database names, for example, the database name begins with the name of the hosting owner.
By and large, it does not matter how to name the database and how to name its tables. However, the current situation began to represent not just the MySQL syntax, but the hosting requirements plus the syntax, that is, MySQL +.
The smaller the table, the simpler it is arranged, and the fewer records in it, the denser it is implanted into the programming language, through which the necessary actions are performed with the data.
In principle, it is easy to paint any task into elementary components, the only question is how much such simplicity will cost.
If you need pure MySQL, then this is your own hosting, your own server, where there will be no restrictions on the number of databases, nor on the rules for their naming and their tables.
Variety of Apache / MySQL / PHP Versions
Dependence on hosting conditions is not so much a disaster as an important condition for modern secure programming. You can write in PHP 7.0.13, use MySQL 5.7.16, but the customer will have a hosting that allows only Perl and the muscle version 4.1.1 or an outdated version of PHP.
Basically (which, however, is not a fact) hosting is provided on Linux, FreeBSD, UBUNTU and other variations of unicos-like systems. All of them work fine, but each of them has its own peculiarities, its own versions of AMP assemblies (Apache, MySQL, PHP). Not always and not every team will work in the next version, although it worked in the previous one.
Any version of MySQL does such a simple operation as exporting a database without problems, both from phpMyAdmin and from the command line. The reverse import operation may not take place. Most often, you will have to change something in the settings of PHP, MySQL server or Apache. If the changes do not help, you will have to look for alternatives.
An obvious fact, another operation can be performed by reinstalling the database, programming language or server. This is not the best solution, but, as a rule, effective.
Database create / check table
The syntax of MySQL create table is rarely applicable in its standard form. If you do not consider it in the context of popular site management systems: there will always be a classic of the genre. In daily work, it is easiest to consider it in the context of practice: what fields are needed in the table, what type and size should they have. The rest will still have to be finalized during the development process: character set, sort order, keys.
Optimal is a variant of the algorithm in which even the lack of a database does not prevent the site from starting work. You can write your own function for checking the availability of a database and / or table when it is necessary to form not only information structures, but also fill them with initial information.
Often it is enough to use a series of constructs Create table if not exists, MySQL then will not surprise the site owner or its visitor by unexpectedly reporting: there is no database, or the structure of the tables is broken.
In modern programming, the general rule “trust, but verify” does not work, or rather, it will use the logic “check and use”.
Create table: MySQL example user table
The registration of new visitors and the entrance / exit of site users have been developed numerous times, but there is no limit to perfection in this important part of any site.
In many ways, the entrance / exit and registration are determined by the competence of the site and / or the content management system used by the site, but more often - by the desire to develop something more secure, protect user accounts, and prevent hacking of the site.
Usually, the MySQL database is more secure than the site, but in the event of a failure, it is possible that the browser displays error messages in the configuration code of the site containing the names and passwords for the database.
It’s good practice to split the data into static and dynamic components. Using the user table as an example, you can have a large users table and active cache_users. The first table contains a large number of users, the second - only those who have worked on the last day (for example). Any new user that is not in the second table comes there after login / registration and is stored for some time.
But dividing into two tables here gives not only the speed of data sampling (a small table is always faster than bulky and large), but also that it contains only the information that is needed most often when working with any client of a particular site. To select or modify data, of course, the main table is used.
Memory, Information and Databases
In the field of information technology, it somehow did not work out to imagine a programming language with memory. Language is variables, constants, and constructs that allow you to manipulate something and somehow. A database is something like that storing. From it you can always choose something; you can write something into it.
The language and the base are combined for the purpose of working with information, but the logic of interconnections in this triad is not so perfect, even as in a simple AMP bundle: Apache, PHP, MySQL. Everything is clear here: the server, the programming language and the database are all that is needed for the vast majority of tasks.
SQL is the same programming language, and indeed the database has its own logic of operations, its own programming concept. Here we can see the desire of the database towards programming. PHP is a good tool, but to say that it is adequately striving towards databases is still difficult. However, its designs and functions are being developed and improved.
If you combine PHP and MySQL into a single whole, you get a tool with memory. The combination option gives the idea of object-oriented programming: the object described in the language is in a compact state in the memory (in the database) for the time being unnecessary. When a need arises in him, he is restored to a full-fledged object and acts in accordance with his competence.