Showing entries 11 to 20 of 1180
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: sql (reset)
AlmaLinux MySQL+Perl

A quick primer on Perl programs connecting to the MySQL database. It’s another set of coding examples for the AlmaLinux instance that I’m building for students. This one demonstrates basic Perl programs, connecting to MySQL, returning data sets by reference and position, dynamic queries, and input parameters to dynamic queries.

  1. Naturally, a hello.pl is a great place to start:
    #!/usr/bin/perl
    
    # Hello World program.
    print "Hello World!\n";
    

    After setting the permissions to -rwxr-xr-x. with this command:

    chmod 755 hello.pl
    

    You call it like this from the Command-Line Interface (CLI):

    ./hello.pl
    

    It prints:

    Hello World!
    
  2. Next, a connect.pl program lets us test the Perl::DBI connection to the MySQL database.
    #!/usr/bin/perl
    
    # Import libraries.
    use strict;
    use warnings;
    use v5.10;     # for …
[Read more]
INTERSECT and EXCEPT commands in MySQL

MySQL now supports the INTERSECT and EXCEPT set operators. Set operators work on the results of multiple SELECT statements. In this post, we will see example queries using INTERSECT and EXCEPT commands for a better understanding

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

While MySQL has supported UNION and UNION ALL commands, there were no INTERSECT or EXCEPT commands in the language. This often involved work-around queries. However, MySQL now does support the INTERCEPT and EXCEPT set operators.

For the example queries, I am using 2 simple tables with arbitrary data.

The names …

[Read more]
INSERT Using the WITH Clause in MySQL

As of MySQL v8, we have Common Table Expressions (CTEs) or the WITH clause available. I recently learned how to use the WITH clause in an INSERT statement, similar to INSERT with SELECT. Like always, I enjoy sharing what I learn so let’s all learn together…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

For the sake of simplicity (and my sanity) I’m using this arbitrary sample data:

SELECT *
FROM auto_test;

We can use the WITH clause to generate a …

[Read more]
MySQL Metadata Queries – Column Structure and Definition With the COLUMNS Table

The INFORMATION_SCHEMA database is full of information and metadata about your database(s). Columns are a necessity and their definition ensures sound storage and data integrity. Learn all about them with the COLUMNS table.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by Gerd Altmann from  …

[Read more]
MySQL Metadata Queries – Find all tables in a Schema

Of course, there are other ways to determine what tables are present in a particular MySQL Database or Schema. You can also find this information if your database user account has permissions for the INFORMATION_SCHEMA database. Continue reading and follow along with an example query…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by Clker-Free-Vector-Images from  …

[Read more]
Filter Data in the MySQL WHERE Clause With Less Than and Greater Than Comparisons

While equality and inequality filter conditions are very common, many times you wish to filter the FROM clause table rows based on values that are less than or greater than another value. MySQL (and SQL in general) supports the less than (<) and greater than (>) operators. Continue reading and see examples of each…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by 준원 서 from  …

[Read more]
MySQL WHERE Clause Inequality Comparison Operators

Just as you want to filter the rows of data returned in a SELECT query with the equality comparison operator (=), you can also create a conditional filter to test if 2 values are not equal to one another. Learn more in the following article.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by Peggy und Marco Lachmann-Anke from  …

[Read more]
MySQL Query from JSON

One of my students asked how you could get JSON data out in tabular format. I said they should look at Øystein Grøvlen’s JSON_TABLE – Best of Both Worlds blog post from 2018. Unfortunately, the student wanted another example with the Video Store model that we use in class.

For clarity, all path definitions start with a $ followed by other selectors:

  • A period followed by a name, such as $.website
  • [N] where N is the position in a zero-indexed array
  • The .[*] wildcard evaluates all members of an object
  • The [*] wildcard evaluates all members of an array
  • The prefix and suffix wildcard, **, evaluates to all paths that begin with the named prefix and end with the named suffix

So, here’s a quick supplement to what’s already there. It assumes you created an …

[Read more]
MySQL Backslashes

Yesterday, I wrote a blog post that showed you how to write a query returning a JSON structure for a 1:many relationship. The relationship was between the member and contact table. It returns one account_number from the member table and a list of first_name and last_name columns from the contact table in a JSON structure.

One of my students asked why I choose to strip the backslashes with Python, and my reply was the SQL was already complex for most blog readers. The student asked but how would you do it in SQL. OK, that’s a fair question for two reasons. First, you don’t need to do in your local programs because it’ll run faster on the server. Second, if you strip the backslashes you can insert it into a standard JSON column. This blog post will show you how to do both.

You would use three REGEXP_REPLACE function calls, like:

[Read more]
MySQL JSON Tricks

Are they really tricks or simply basic techniques combined to create a solution. Before writing these mechanics for using native MySQL to create a compound JSON object, let me point out that the easiest way to get one is to use the MySQL Node.js library, as shown recently in my “Is SQL Programming” blog post.

Moving data from a relational model output to a JSON structure isn’t as simple as a delimited list of columns in a SQL query. Let’s look at it in stages based on the MySQL Server 12.18.2 Functions that create JSON values.

Here’s how you return single row as a JSON object, which is quite straightforward:

SELECT JSON_OBJECT('first_name',c.first_name,'last_name',c.last_name) AS json_result
FROM   contact c
WHERE …
[Read more]
Showing entries 11 to 20 of 1180
« 10 Newer Entries | 10 Older Entries »