FeaturesPhpStorm

PhpStorm provides us the possibility to connect to Windows Azure SQL Database right from within the IDE. In this post, we’ll explore several options that are available for working with Windows Azure SQL Database (or database systems like SQL Server, MySQL, PostgreSQL or Oracle, for that matter):

• Setting up a database connection
• Creating a table
• Inserting and updating data
• Using the database console
• Generating a database diagram
• Database refactoring

If you are familiar with Windows Azure SQL Database, make sure to configure the database firewall correctly so you can connect to it from your current machine.

Setting up a database connection

PhpStorm is an innovative, Java-based integrated development environment (IDE) engineered by JetBrains for PHP and web developers. It supports PHP 5.3/5.4/5.5/5.6/7.0/7.1/7.2/8.0, provides on-the-fly error prevention, best auto-completion and code refactoring, zero-configuration debugging, and an extended HTML, CSS and JavaScript editor. PhpStorm Web Help for offline use. 23 978 downloads. Top Rated for IntelliJ Platform. The Key Promoter X helps you to learn essential shortcuts while you are working. 2 147 930 downloads. Legacy Icon Pack for 2018.2+.

Database support can be found on the right-hand side of the IDE or by using the Ctrl+Alt+A (Cmd+Alt+A on Mac) and searching for “Database”.

Opening the database pane, we can create a new connection or Data Source. We’ll have to specify the JDBC database driver to be used to connect to our database. Since Windows Azure SQL Database is just “SQL Server” in essence, we can use the SQL Server driver available in the list of drivers. PhpStorm doesn’t ship these drivers but a simple click (on “Click here”) fetches the correct JDBC driver from the Internet.

Next, we’ll have to enter our connection details. As the JDBC driver class, select the com.microsoft.sqlserver.jdbc driver. The Database URL should be a connection string to our SQL Database and typically comes in the following form:

The username to use comes in a different form. Due to a protocol change that was required for Windows Azure SQL Database, we have to suffix the username with the server name.

After filling out the necessary information, we can use the Test Connection button to test the database connection.

Congratulations! Our database connection is a fact and we can store it by closing the Data Source dialog using the Ok button.

Creating a table

If we right click a schema discovered in our Data Source, we can use the New | Table menu item to create a table.

We can use the Create New Table dialog to define columns on our to-be-created table. PhpStorm provides us with a user interface which allows us to graphically specify columns and generates the DDL for us.

Clicking Ok will close the dialog and create the table for us. We can now right-click our table and modify existing columns or add additional columns and generate DDL which alters the table.

Inserting and updating data

After creating a table, we can insert data (or update data from an existing table). Upon connecting to the database, PhpStorm will display a list of all tables and their columns. We can select a table and press F4 (or right-click and use the Table Editor context menu).

We can add new rows and/or edit existing rows by using the + and – buttons in the toolbar. By default, auto-commit is enabled and changes are committed automatically to the database. We can disable this option and manually commit and rollback any changes that have been made in the table editor.

Using the database console

Sometimes there is no better tool than a database console. We can bring up the Console by right-clicking a table and selecting the Console menu item or simply by pressing Ctrl+Shift+F10 (Cmd+Shift+F10 on Mac).

We can enter any SQL statement in the console and run it against our database. As you can see from the screenshot above, we even get autocompletion on table names and column names!

If we have multiple tables with foreign keys between them, we can easily generate a database diagram by selecting the tables to be included in the diagram and selecting Diagrams | Show Visualization… from the context menu or using the Ctrl+Alt+Shift+U (Cmd+Alt+Shift+U on Mac).

As you can see from the above screenshot, PhpStorm will generate a database diagram for the selected tables, displaying how they relate to each other.

Database refactoring

Renaming a table or column often is tedious. PhpStorm includes a Rename refactoring (Shift-F6) which generates the required SQL code for renaming tables or columns.

As we’ve seen in this post, working with Windows Azure SQL Database is pretty simple from within PhpStorm using the built-in database support.

Develop with pleasure!

– JetBrains Web IDE Team

Summary: in this tutorial, you will learn how to use the PostgreSQL LIKE and ILIKE operators to query data using pattern matchings.

Phpstorm Postgresql Free

Introduction to PostgreSQL LIKE operator

Suppose that you want to find a customer but you do not remember her name exactly. However, you just remember that her name begins with something like Jen.

How do you find the exact customer from the database? You may find the customer in the customer table by looking at the first name column to see if there is any value that begins with Jen. It will be time-consuming if the customer table has many rows.

Fortunately, you can use the PostgreSQL LIKE operator to match the first name of the customer with a string like this query:

Notice that the WHERE clause contains a special expression: the first_name, the LIKE operator and a string that contains a percent sign (%). The string 'Jen%' is called a pattern.

The query returns rows whose values in the first_name column begin with Jen and may be followed by any sequence of characters. This technique is called pattern matching.

You construct a pattern by combining literal values with wildcard characters and use the LIKE or NOT LIKE operator to find the matches. PostgreSQL provides you with two wildcards:

• Percent sign ( %) matches any sequence of zero or more characters.
• Underscore sign ( _) matches any single character.

The syntax of PostgreSQL LIKE operator is as follows:

The expression returns true if the value matches the pattern.

To negate the LIKE operator, you use the NOT operator as follows:

The NOT LIKE operator returns true when the value does not match the pattern.

If the pattern does not contain any wildcard character, the LIKE operator behaves like the equal ( =) operator.

PostgreSQL LIKE operator – pattern matching examples

Let’s take some examples of using the LIKE operator

PostgreSQL LIKE examples

See the following example:

How it works.

• The first expression returns true because the foopattern does not contain any wildcard character so the LIKE operator acts like the equal (=) operator.
• The second expression returns true because it matches any string that begins with the letter f and followed by any number of characters.
• The third expression returns true because the pattern ( _o_) matches any string that begins with any single character, followed by the letter o and ended with any single character.
• The fourth expression returns false because the pattern b_ matches any string that begins with the letter b and followed by any single character.

It’s possible to use wildcards at the beginning and/or end of the pattern.

For example, the following query returns customers whose first name contains er string like Jenifer, Kimberly, etc.

You can combine the percent ( %) with underscore ( _) to construct a pattern as the following example:

The pattern _her% matches any string that:

• Begin with any single character (_)
• And is followed by the literal string her.
• And is ended with any number of characters.

Phpstorm Postgresql Database

The returned first names are Cheryl, Sherri, Sherry, and Therasa.

PostgreSQL NOT LIKE examples

The following query uses the NOT LIKE operator to find customers whose first names do not begin with Jen:

Phpstorm Postgresql Download

PostgreSQL extensions of LIKE operator

PostgreSQL supports the ILIKE operator that works like the LIKE operator. In addition, the ILIKE operator matches value case-insensitively. For example:

The BAR% pattern matches any string that begins with BAR, Bar, BaR, etc. If you use the LIKE operator instead, the query will not return any row.

PostgreSQL also provides some operators that act like the LIKE, NOT LIKE, ILIKE and NOT ILIKE operator as shown below:

In this tutorial, you have learned how to use the PostgreSQL LIKE and ILIKE operators to query data using pattern matching.