A query to obtain phone number of users from users table through regular expression matching:

SELECT phone_number FROM users WHERE phone_number REGEXP '^[0-9]{10}$'

The above is a query to match 10 digit number. 0 is allowed as first digit of number, this is quite not useful in case of mobile number. The regular expression can be modified as '^98[0-9]{8}$' for mobile numbers like 9800456745

There are three kinds of variables in MySQL

1. System variables
2. Status variables
3. User variables

System variables

These variables store the configuration data required for the MySQL. They have a default value. They can be viewed by the statement:

SHOW VARIABLES;

Complete information on system variables can be found here.

Status variables

These variables show the global status of all the connections and the session status of a section. They can be viewed by the statement:

SHOW STATUS;

Complete information on status variables can be found here.

User variables

These variables have session scope. They exist for a specific session to a specific client. They start with the '@' sign. e.g. @var. Their values can be set as follows:

SET @var=5;

They are when we require a little beyond just querying the database. i.e. The user variables allow to introduce programming in the MySQL.

Creating log of MySQL Command-line Sessions, Creating log in MySQL, Logging your MySQL Command-line sessions, MySQL tee Command Example, MySQL notee Command Example, MySQL tee Saving our output to a file


Sometimes we may require to review our commands that are executed in MySQL. So, if we have a log of our commands, it may be helpful to see what changes we have made. There's an important MySQL command that enables the log of commands executed in MySQL.

The command is as:

tee path_of_file_for_storing_logs

e.g   tee C:\logs\log1.txt

Now each command executed is tracked and the log of them is maintained in the log file.

There may be a need to end the logging process. This is done by a command called:

notee 

This is very useful command if we need to revert some changes we have made. Database administrators require this to administer every changes made to the database. If there are some errors, they can be traced easily.

We have developed an application that uses the MySQL database. It's fine until we are in the development machine. But when the application is needed to deploy in other machines, then here are steps to do so.

1. First download the MySQL package (.zip) package that requires no installation. The (.msi) installer package can also be used for the installation. If (.msi) installer is used then it is not necessary to install the MySQL service as the installation does. i.e. skip step 2, 3, 4 and 5.
2. Unzip the package to a location like C:\Program Files\ and go the directory bin inside the MySQL package.
3. Install the MySQL service by the command
   
   mysqld --install
4. This installs a MySQL service named MySQL on your computer. To start the service, type the following command
   
   sc start MySQL
5. This starts the MySQL service and it is set to automatic start-up by default.
6. Export the database from the development machine to a sql file.
7. Now create a user same as that in the development machine. Grant all privileges to this user.
8. Create a database that was exported to the sql file from the development machine.
9. Now go to the database by command
   
   USE database_name
10. Now import the sql file. There are a number of executions and finally you are ready to execute your application.

This is how you can deploy the MySQL database from development environment to production environment.