How should I use MySQL functions

MySQL - Tutorials, Tips and Tricks

MySQL is a database management system that can be obtained as open source software, is platform-independent and can be used universally. At the same time, it can compete with any other database system in terms of its performance. So it's no wonder it has spread incredibly quickly in recent years.

What is mySQL

mySQL is a database management system that is particularly suitable for use on the Internet. MySQL has been used on the Internet and in thousands of companies since 1996. It is comparatively quick and reliable. The data can be managed with the internal SQL command set. No other database has expanded as quickly as MySQL in recent years. It is multi-user and multi-tasking capable, can also handle large amounts of data quickly and is comparatively stable. It can be operated on large networks, but also on a single PC. Like other database systems, MySQL consists of databases that contain tables and auxiliary data. There are also tools for editing the data. This includes the MySQL monitor and phpMyAdmin. MySQL also has an instruction set that is very similar to the standard database query language SQL. MySQL can also be obtained from the Internet as an open source distribution and used accordingly.

What can mySQL do?

mySQL can manage, display, save and change data in tables. It can do that very well, but nothing else. On the other hand, other tasks, such as the graphic display and preparation of the data, cannot be solved with mySQL, there is other software that can be linked well with MySQL, e.g. PHP is very suitable.

How do you use mySQL?

There are various ways of executing the commands: command line-oriented in the MySQL monitor, as a script (by starting a * .sql file) or with the help of the graphical user interface php MySQL Admin (more on this below). However, it is more effective to embed the commands in high-level programming languages. PHP, which can be used to create dynamic websites, is predestined for this. PHP contains a number of special functions for MySQL databases. Another possibility is to link Microsoft Access applications with mySQL tables. Then the full functionality of Access is available. This method is suitable for users who are familiar with MS Access but want to use the higher speed of mySQL.
The installation software for mySQL is available for download, e.g. from http://www.mysql.com/downloads/.

How does mySQL work?

MySQL works as a client-server system. The respective database is the server. The software on the client side (e.g. the MySQL monitor or a PHP application) establishes the connection to the server. You send jobs to the database in the form of commands, the syntax of which is largely identical to ANSI-SQL. ANSI-SQL is the standard version of SQL. SQL (Structured Query Language) is a set of instructions for manipulating data in relational databases. The database translates the commands internally into executable code and then executes this - if possible. Then it sends the generated information back to the client. This information can be the extracts from a table or the message that a command was executed or an error message if the command could not be executed. SQL always refers to tables. SQL commands can display, change, add, or delete rows in tables. In addition, SQL can also change the structure of the tables, create or delete relationships and indexes, and manage users. That's all SQL can do.
The client perceives the database as just a collection of tables. Physically, however, MySQL databases are stored as a collection of binary-encrypted files that can only be processed meaningfully with the help of MySQL tools. This also ensures the necessary security. Any number of separate databases can be stored in a MySQL environment. In addition, MySQL has practically all the features of a modern database management system, including, for example, multiuser and multithread capability, a flexible but easily manageable authorization concept at host, DB, user, table and even column level, indices, primary and Foreign keys, password protection and - with suitable configuration - high performance even with large amounts of data are part of this.

syntax

Communication with the MySQL database takes place via SQL commands. The following chapters describe which commands are available (at least the most important ones), according to which rules they work and what they do.

edit data

The following describes the commands that can be used to select, view, change, add, or delete data in MySQL: SELECT, UPDATE, INSERT, REPLACE, DELETE. Each of these commands has more or less variations. Describing all of them is not possible in this context. We therefore limit ourselves to the most important ones.

Manage data

So that the data in the tables can be edited, they must be in the appropriate structure. Sometimes it has to be changed. This chapter describes which commands MySQL offers for this.

Functions

MySQL has a number of standard functions that are included in the scope of delivery. The result of the function is listed like the content of a field. The value is always the result of the calculation using the data records that belong to this group.

Indices

Database queries are a common reason for slow loading times. If a website is just starting up, the databases are usually not that full. Little by little, new entries are added to the database and the database queries become slower. After all, more data records now have to be searched for SELECTs.

Database modeling

MySQL is a database management system (DBMS). Like other DBMS, it can be thought of as a collection of tables that are related to one another. Then there are tools and auxiliary data that we will describe later. Spreadsheets are familiar to most Excel computer users. Tables have columns and rows - in MySQL and any other relational database, the tables are logically structured in the same way. A table line is also called a data record here (because there several data are combined into one set of data). record. The columns are called fields and they have names, not just A, B, C ... as in Excel, but descriptive names such as customer number or invoice date.

Tools for MySQL

To do anything with a MySQL database, you need tools. The best known are the MySQL monitor and phpMyAdmin. The MySQL monitor is a simple console for the input dialog of SQL commands. phpMyAdmin is a comfortable interface for extensive administration, but requires PHP.

MySQL optimization

With large amounts of data, a MySQL query can take time. In addition, the memory requirements of a database can increase.

Administration of MySQL databases

In MySQL, the administrator can assign authorizations for users on completely different levels. To do this, he can enter commands, but also directly maintain the corresponding system tables.

Backup copies, restoration, export and import

Every reputable database system offers the possibility to create backup copies and to “retrieve” data from a backup copy into the database, if data is accidentally lost due to a system crash or incorrect deletion or modification. The same procedures that are suitable for backup copies can also be used for data exchange with other programs / data formats.
MySQL offers the following variants: dump files, CSV files and copies via ODBC in other formats (e.g. Microsoft Access).

What's new with MySQL 6

The first test versions of the MySQL server version 6 are now available for download. As is known from MySQL AB, it is common practice in a new major version to fix a large number of bugs with different priorities and to add new features. In this context, the new storage engine Falcon is important. A major disadvantage during development is that the documentation is often revised and is available in a few languages.

other topics

Read more: ⯈ MySQL Syntax

about us