One place for hosting & domains

      Database

      How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04


      Introduction

      As your application or website grows, there may come a point where you’ve outgrown your current server setup. If you are hosting your web server and database backend on the same machine, it may be a good idea to separate these two functions so that each can operate on its own hardware and share the load of responding to your visitors’ requests.

      In this guide, we’ll go over how to configure a remote MySQL database server that your web application can connect to. We will use WordPress as an example in order to have something to work with, but the technique is widely applicable to any application backed by MySQL.

      Prerequisites

      Before beginning this tutorial, you will need:

      • Two Ubuntu 18.04 servers. Each should have a non-root user with sudo privileges and a UFW firewall enabled, as described in our Initial Server Setup with Ubuntu 18.04 tutorial. One of these servers will host your MySQL backend, and throughout this guide we will refer to it as the database server. The other will connect to your database server remotely and act as your web server; likewise, we will refer to it as the web server over the course of this guide.
      • Nginx and PHP installed on your web server. Our tutorial How To Install Linux, Nginx, MySQL, PHP (LEMP stack) in Ubuntu 18.04 will guide you through the process, but note that you should skip Step 2 of this tutorial, which focuses on installing MySQL, as you will install MySQL on your database server.
      • MySQL installed on your database server. Follow “How To Install MySQL on Ubuntu 18.04” to set this up.
      • Optionally (but strongly recommended), TLS/SSL certificates from Let’s Encrypt installed on your web server. You’ll need to purchase a domain name and have DNS records set up for your server, but the certificates themselves are free. Our guide How To Secure Nginx with Let’s Encrypt on Ubuntu 18.04 will show you how to obtain these certificates.

      Step 1 — Configuring MySQL to Listen for Remote Connections

      Having one’s data stored on a separate server is a good way to expand gracefully after hitting the performance ceiling of a one-machine configuration. It also provides the basic structure necessary to load balance and expand your infrastructure even more at a later time. After installing MySQL by following the prerequisite tutorial, you’ll need to change some configuration values to allow connections from other computers.

      Most of the MySQL server’s configuration changes can be made in the mysqld.cnf file, which is stored in the /etc/mysql/mysql.conf.d/ directory by default. Open up this file with root privileges in your preferred editor. Here, we’ll use nano:

      • sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

      This file is divided into sections denoted by labels in square brackets ([ and ]). Find the section labeled mysqld:

      /etc/mysql/mysql.conf.d/mysqld.cnf

      . . .
      [mysqld]
      . . .
      

      Within this section, look for a parameter called bind-address. This tells the database software which network address to listen for connections on.

      By default, this is set to 127.0.0.1, meaning that MySQL is configured to only look for local connections. You need to change this to reference an external IP address where your server can be reached.

      If both of your servers are in a datacenter with private networking capabilities, use your database server’s private network IP. Otherwise, you can use its public IP address:

      /etc/mysql/mysql.conf.d/mysqld.cnf

      [mysqld]
      . . .
      bind-address = db_server_ip
      

      Because you’ll connect to your database over the internet, it’s recommended that you require encrypted connections to keep your data secure. If you don’t encrypt your MySQL connection, anybody on the network could sniff sensitive information between your web and database servers. To encrypt MySQL connections, add the following line after the bind-address line you just updated:

      /etc/mysql/mysql.conf.d/mysqld.cnf

      [mysqld]
      . . .
      require_secure_transport = on
      . . .
      

      Save and close the file when you are finished. If you’re using nano, do this by pressing CTRL+X, Y, and then ENTER.

      For SSL connections to work, you will need to create some keys and certificates. MySQL comes with a command that will automatically set these up. Run the following command, which creates the necessary files. It also makes them readable by the MySQL server by specifying the UID of the mysql user:

      • sudo mysql_ssl_rsa_setup --uid=mysql

      To force MySQL to update its configuration and read the new SSL information, restart the database:

      • sudo systemctl restart mysql

      To confirm that the server is now listening on the external interface, run the following netstat command:

      • sudo netstat -plunt | grep mysqld

      Output

      tcp 0 0 db_server_ip:3306 0.0.0.0:* LISTEN 27328/mysqld

      netstat prints statistics about your server’s networking system. This output shows us that a process called mysqld is attached to the db_server_ip at port 3306, the standard MySQL port, confirming that the server is listening on the appropriate interface.

      Next, open up that port on the firewall to allow traffic through:

      Those are all the configuration changes you need to make to MySQL. Next, we will go over how to set up a database and some user profiles, one of which you will use to access the server remotely.

      Step 2 — Setting Up a WordPress Database and Remote Credentials

      Even though MySQL itself is now listening on an external IP address, there are currently no remote-enabled users or databases configured. Let's create a database for WordPress, and a pair of users that can access it.

      Begin by connecting to MySQL as the root MySQL user:

      Note: If you have password authentication enabled, as described in Step 3 of the prerequisite MySQL tutorial, you will instead need to use the following command to access the MySQL shell:

      After running this command, you will be asked for your MySQL root password and, after entering it, you'll be given a new mysql> prompt.

      From the MySQL prompt, create a database that WordPress will use. It may be helpful to give this database a recognizable name so that you can easily identify it later on. Here, we will name it wordpress:

      • CREATE DATABASE wordpress;

      Now that you've created your database, you next need to create a pair of users. We will create a local-only user as well as a remote user tied to the web server’s IP address.

      First, create your local user, wordpressuser, and make this account only match local connection attempts by using localhost in the declaration:

      • CREATE USER 'wordpressuser'@'localhost' IDENTIFIED BY 'password';

      Then grant this account full access to the wordpress database:

      • GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost';

      This user can now do any operation on the database for WordPress, but this account cannot be used remotely, as it only matches connections from the local machine. With this in mind, create a companion account that will match connections exclusively from your web server. For this, you'll need your web server's IP address.

      Please note that you must use an IP address that utilizes the same network that you configured in your mysqld.cnf file. This means that if you specified a private networking IP in the mysqld.cnf file, you'll need to include the private IP of your web server in the following two commands. If you configured MySQL to use the public internet, you should match that with the web server's public IP address.

      • CREATE USER 'wordpressuser'@'web-server_ip' IDENTIFIED BY 'password';

      After creating your remote account, give it the same privileges as your local user:

      • GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'web_server_ip';

      Lastly, flush the privileges so MySQL knows to begin using them:

      Then exit the MySQL prompt by typing:

      Now that you've set up a new database and a remote-enabled user, you can move on to testing whether you're able to connect to the database from your web server.

      Step 3 — Testing Remote and Local Connections

      Before continuing, it's best to verify that you can connect to your database from both the local machine — your database server — and from your web server with each of the wordpressuser accounts.

      First, test the local connection from your database server by attempting to log in with your new account:

      • mysql -u wordpressuser -p

      When prompted, enter the password that you set up for this account.

      If you are given a MySQL prompt, then the local connection was successful. You can exit out again by typing:

      Next, log into your web server to test remote connections:

      You'll need to install some client tools for MySQL on your web server in order to access the remote database. First, update your local package cache if you haven't done so recently:

      Then install the MySQL client utilities:

      • sudo apt install mysql-client

      Following this, connect to your database server using the following syntax:

      • mysql -u wordpressuser -h db_server_ip -p

      Again, you must make sure that you are using the correct IP address for the database server. If you configured MySQL to listen on the private network, enter your database's private network IP. Otherwise, enter your database server's public IP address.

      You will be asked for the password for your wordpressuser account. After entering it, and if everything is working as expected, you will see the MySQL prompt. Verify that the connection is using SSL with the following command:

      If the connection is indeed using SSL, the SSL: line will indicate this, as shown here:

      Output

      -------------- mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper Connection id: 52 Current database: Current user: wordpressuser@203.0.113.111 SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu) Protocol version: 10 Connection: 203.0.113.111 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 3 hours 43 min 40 sec Threads: 1 Questions: 1858 Slow queries: 0 Opens: 276 Flush tables: 1 Open tables: 184 Queries per second avg: 0.138 --------------

      After verifying that you can connect remotely, go ahead and exit the prompt:

      With that, you've verified local access and access from the web server, but you have not verified that other connections will be refused. For an additional check, try doing the same thing from a third server for which you did not configure a specific user account in order to make sure that this other server is not granted access.

      Note that before running the following command to attempt the connection, you may have to install the MySQL client utilities as you did above:

      • mysql -u wordpressuser -h db_server_ip -p

      This should not complete successfully, and should throw back an error that looks similar to this:

      Output

      ERROR 1130 (HY000): Host '203.0.113.12' is not allowed to connect to this MySQL server

      This is expected, since you haven't created a MySQL user that's allowed to connect from this server, and also desired, since you want to be sure that your database server will deny unauthorized users access to your MySQL server.

      After successfully testing your remote connection, you can proceed to installing WordPress on your web server.

      Step 4 — Installing WordPress

      To demonstrate the capabilities of your new remote-capable MySQL server, we will go through the process of installing and configuring WordPress — the popular content management system — on your web server. This will require you to download and extract the software, configure your connection information, and then run through WordPress's web-based installation.

      On your web server, download the latest release of WordPress to your home directory:

      • cd ~
      • curl -O https://wordpress.org/latest.tar.gz

      Extract the files, which will create a directory called wordpress in your home directory:

      WordPress includes a sample configuration file which we'll use as a starting point. Make a copy of this file, removing -sample from the filename so it will be loaded by WordPress:

      • cp ~/wordpress/wp-config-sample.php ~/wordpress/wp-config.php

      When you open the file, your first order of business will be to adjust some secret keys to provide more security to your installation. WordPress provides a secure generator for these values so that you do not have to try to come up with good values on your own. These are only used internally, so it won't hurt usability to have complex, secure values here.

      To grab secure values from the WordPress secret key generator, type:

      • curl -s https://api.wordpress.org/secret-key/1.1/salt/

      This will print some keys to your output. You will add these to your wp-config.php file momentarily:

      Warning! It is important that you request your own unique values each time. Do not copy the values shown here!

      Output

      define('AUTH_KEY', 'L4|2Yh(giOtMLHg3#] DO NOT COPY THESE VALUES %G00o|te^5YG@)'); define('SECURE_AUTH_KEY', 'DCs-k+MwB90/-E(=!/ DO NOT COPY THESE VALUES +WBzDq:7U[#Wn9'); define('LOGGED_IN_KEY', '*0kP!|VS.K=;#fPMlO DO NOT COPY THESE VALUES +&[%8xF*,18c @'); define('NONCE_KEY', 'fmFPF?UJi&(j-{8=$- DO NOT COPY THESE VALUES CCZ?Q+_~1ZU~;G'); define('AUTH_SALT', '@qA7f}2utTEFNdnbEa DO NOT COPY THESE VALUES t}Vw+8=K%20s=a'); define('SECURE_AUTH_SALT', '%BW6s+d:7K?-`C%zw4 DO NOT COPY THESE VALUES 70U}PO1ejW+7|8'); define('LOGGED_IN_SALT', '-l>F:-dbcWof%4kKmj DO NOT COPY THESE VALUES 8Ypslin3~d|wLD'); define('NONCE_SALT', '4J(<`4&&F (WiK9K#] DO NOT COPY THESE VALUES ^ZikS`es#Fo:V6');

      Copy the output you received to your clipboard, then open the configuration file in your text editor:

      • nano ~/wordpress/wp-config.php

      Find the section that contains the dummy values for those settings. It will look something like this:

      /wordpress/wp-config.php

      . . .
      define('AUTH_KEY',         'put your unique phrase here');
      define('SECURE_AUTH_KEY',  'put your unique phrase here');
      define('LOGGED_IN_KEY',    'put your unique phrase here');
      define('NONCE_KEY',        'put your unique phrase here');
      define('AUTH_SALT',        'put your unique phrase here');
      define('SECURE_AUTH_SALT', 'put your unique phrase here');
      define('LOGGED_IN_SALT',   'put your unique phrase here');
      define('NONCE_SALT',       'put your unique phrase here');
      . . .
      

      Delete those lines and paste in the values you copied from the command line.

      Next, enter the connection information for your remote database. These configuration lines are at the top of the file, just above where you pasted in your keys. Remember to use the same IP address you used in your remote database test earlier:

      /wordpress/wp-config.php

      . . .
      /** The name of the database for WordPress */
      define('DB_NAME', 'wordpress');
      
      /** MySQL database username */
      define('DB_USER', 'wordpressuser');
      
      /** MySQL database password */
      define('DB_PASSWORD', 'password');
      
      /** MySQL hostname */
      define('DB_HOST', 'db_server_ip');
      . . .
      

      And finally, anywhere in the file, add the following line which tells WordPress to use an SSL connection to our MySQL database:

      /wordpress/wp-config.php

      define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);
      

      Save and close the file.

      Next, copy the files and directories found in your ~/wordpress directory to Nginx's document root. Note that this command includes the -a flag to make sure all the existing permissions are carried over:

      • sudo cp -a ~/wordpress/* /var/www/html

      After this, the only thing left to do is modify the file ownership. Change the ownership of all the files in the document root over to www-data, Ubuntu's default web server user:

      • sudo chown -R www-data:www-data /var/www/html

      With that, WordPress is installed and you're ready to run through its web-based setup routine.

      Step 5 — Setting Up WordPress Through the Web Interface

      WordPress has a web-based setup process. As you go through it, it will ask a few questions and install all the tables it needs in your database. Here, we will go over the initial steps of setting up WordPress, which you can use as a starting point for building your own custom website that uses a remote database backend.

      Navigate to the domain name (or public IP address) associated with your web server:

      http://example.com
      

      You will see a language selection screen for the WordPress installer. Select the appropriate language and click through to the main installation screen:

      WordPress install screen

      Once you have submitted your information, you will need to log into the WordPress admin interface using the account you just created. You will then be taken to a dashboard where you can customize your new WordPress site.

      Conclusion

      By following this tutorial, you've set up a MySQL database to accept SSL-protected connections from a remote WordPress installation. The commands and techniques used in this guide are applicable to any web application written in any programming language, but the specific implementation details will differ. Refer to your application or language's database documentation for more information.



      Source link

      How to Manage an SQL Database


      Introduction

      SQL databases come installed with all the commands you need to add, modify, delete, and query your data. This cheat sheet-style guide provides a quick reference to some of the most commonly-used SQL commands.

      How to Use This Guide:

      • This guide is in cheat sheet format with self-contained command-line snippets
      • Jump to any section that is relevant to the task you are trying to complete
      • When you see highlighted text in this guide’s commands, keep in mind that this text should refer to the columns, tables, and data in your own database.
      • Throughout this guide, the example data values given are all wrapped in apostrophes ('). In SQL, it is necessary to wrap any data values that consist of strings in apostrophes. This isn’t required for numeric data, but it also won’t cause any issues if you do include apostrophes.

      Please note that, while SQL is recognized as a standard, most SQL database programs have their own proprietary extensions. This guide uses MySQL as the example relational database management system (RDBMS), but the commands given will work with other relational database programs, including PostgreSQL, MariaDB, and SQLite. Where there are significant differences between RDBMSs, we have included the alternative commands.

      Opening up the Database Prompt (using Socket/Trust Authentication)

      By default on Ubuntu 18.04, the root MySQL user can authenticate without a password using the following command:

      To open up a PostgreSQL prompt, use the following command. This example will log you in as the postgres user, which is the included superuser role, but you can replace that with any already-created role:

      Opening up the Database Prompt (using Password Authentication)

      If your root MySQL user is set to authenticate with a password, you can do so with the following command:

      If you've already set up a non-root user account for your database, you can also use this method to log in as that user:

      The above command will prompt you for your password after you run it. If you'd like to supply your password as part of the command, immediately follow the -p option with your password, with no space between them:

      Creating a Database

      The following command creates a database with default settings.

      • CREATE DATABASE database_name;

      If you want your database to use a character set and collation different than the defaults, you can specify those using this syntax:

      • CREATE DATABASE database_name CHARACTER SET character_set COLLATE collation;

      Listing Databases

      To see what databases exist in your MySQL or MariaDB installation, run the following command:

      In PostgreSQL, you can see what databases have been created with the following command:

      Deleting a Database

      To delete a database, including any tables and data held within it, run a command that follows this structure:

      • DROP DATABASE IF EXISTS database;

      Creating a User

      To create a user profile for your database without specifying any privileges for it, run the following command:

      • CREATE USER username IDENTIFIED BY 'password';

      PostgreSQL uses a similar, but slightly different, syntax:

      • CREATE USER user WITH PASSWORD 'password';

      If you want to create a new user and grant them privileges in one command, you can do so by issuing a GRANT statement. The following command creates a new user and grants them full privileges to every database and table in the RDBMS:

      • GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

      Deleting a User

      Use the following syntax to delete a database user profile:

      • DROP USER IF EXISTS username;

      Note that this command will not by default delete any tables created by the deleted user, and attempts to access such tables may result in errors.

      Selecting a Database

      Before you can create a table, you first have to tell the RDBMS the database in which you'd like to create it. In MySQL and MariaDB, do so with the following syntax:

      In PostgreSQL, you must use the following command to select your desired database:

      Creating a Table

      The following command structure creates a new table with the name table, and includes two columns, each with their own specific data type:

      • CREATE TABLE table ( column_1 column_1_data_type, column_2 column_2_data_taype );

      Deleting a Table

      To delete a table entirely, including all its data, run the following:

      • DROP TABLE IF EXISTS table

      Inserting Data into a Table

      Use the following syntax to populate a table with one row of data:

      • INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_A', 'data_B', 'data_C' );

      You can also populate a table with multiple rows of data using a single command, like this:

      • INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_1A', 'data_1B', 'data_1C' ), ( 'data_2A', 'data_2B', 'data_2C' ), ( 'data_3A', 'data_3B', 'data_3C' );

      Deleting Data from a Table

      To delete a row of data from a table, use the following command structure. Note that value should be the value held in the specified column in the row that you want to delete:

      • DELETE FROM table WHERE column='value';

      Note: If you do not include a WHERE clause in a DELETE statement, as in the following example, it will delete all the data held in a table, but not the columns or the table itself:

      Changing Data in a Table

      Use the following syntax to update the data held in a given row. Note that the WHERE clause at the end of the command tells SQL which row to update. value is the value held in column_A that aligns with the row you want to change.

      Note: If you fail to include a WHERE clause in an UPDATE statement, the command will replace the data held in every row of the table.

      • UPDATE table SET column_1 = value_1, column_2 = value_2 WHERE column_A=value;

      Inserting a Column

      The following command syntax will add a new column to a table:

      • ALTER TABLE table ADD COLUMN column data_type;

      Deleting a Column

      A command following this structure will delete a column from a table:

      • ALTER TABLE table DROP COLUMN column;

      Performing Basic Queries

      To view all the data from a single column in a table, use the following syntax:

      • SELECT column FROM table;

      To query multiple columns from the same table, separate the column names with a comma:

      • SELECT column_1, column_2 FROM table;

      You can also query every column in a table by replacing the names of the columns with an asterisk (*). In SQL, asterisks act as placeholders to represent “all”:

      Using WHERE Clauses

      You can narrow down the results of a query by appending the SELECT statement with a WHERE clause, like this:

      • SELECT column FROM table WHERE conditions_that_apply;

      For example, you can query all the data from a single row with a syntax like the following. Note that value should be a value held in both the specified column and the row you want to query:

      • SELECT * FROM table WHERE column = value;

      Working with Comparison Operators

      A comparison operator in a WHERE clause defines how the specified column should be compared against the value. Here are some common SQL comparison operators:

      Operator What it does
      = tests for equality
      != tests for inequality
      < tests for less-than
      > tests for greater-than
      <= tests for less-than or equal-to
      >= tests for greater-than or equal-to
      BETWEEN tests whether a value lies within a given range
      IN tests whether a row's value is contained in a set of specified values
      EXISTS tests whether rows exist, given the specified conditions
      LIKE tests whether a value matches a specified string
      IS NULL tests for NULL values
      IS NOT NULL tests for all values other than NULL

      Working with Wildcards

      SQL allows the use of wildcard characters. These are useful if you're trying to find a specific entry in a table, but aren't sure of what that entry is exactly.

      Asterisks (*) are placeholders that represent “all,” this will query every column in a table:

      Percentage signs (%) represent zero or more unknown characters.

      • SELECT * FROM table WHERE column LIKE val%;

      Underscores (_) are used to represent a single unknown character:

      • SELECT * FROM table WHERE column LIKE v_lue;

      Counting Entries in a Column

      The COUNT function is used to find the number of entries in a given column. The following syntax will return the total number of values held in column:

      • SELECT COUNT(column) FROM table;

      You can narrow down the results of a COUNT function by appending a WHERE clause, like this:

      • SELECT COUNT(column) FROM table WHERE column=value;

      Finding the Average Value in a Column

      The AVG function is used to find the average (in this case, the mean) amongst values held in a specific column. Note that the AVG function will only work with columns holding numeric values; when used on a column holding string values, it may return an error or 0:

      • SELECT AVG(column) FROM table;

      Finding the Sum of Values in a Column

      The SUM function is used to find the sum total of all the numeric values held in a column:

      • SELECT SUM(column) FROM table;

      As with the AVG function, if you run the SUM function on a column holding string values it may return an error or just 0, depending on your RDBMS.

      Finding the Largest Value in a Column

      To find the largest numeric value in a column or the last value alphabetically, use the MAX function:

      • SELECT MAX(column) FROM table;

      Finding the Smallest Value in a Column

      To find the smallest numeric value in a column or the first value alphabetically, use the MIN function:

      • SELECT MIN(column) FROM table;

      Sorting Results with ORDER BY Clauses

      An ORDER BY clause is used to sort query results. The following query syntax returns the values from column_1 and column_2 and sorts the results by the values held in column_1 in ascending order or, for string values, in alphabetical order:

      • SELECT column_1, column_2 FROM table ORDER BY column_1;

      To perform the same action, but order the results in descending or reverse alphabetical order, append the query with DESC:

      • SELECT column_1, column_2 FROM table ORDER BY column_1 DESC;

      Sorting Results with GROUP BY Clauses

      The GROUP BY clause is similar to the ORDER BY clause, but it is used to sort the results of a query that includes an aggregate function such as COUNT, MAX, MIN, or SUM. On their own, the aggregate functions described in the previous section will only return a single value. However, you can view the results of an aggregate function performed on every matching value in a column by including a GROUP BY clause.

      The following syntax will count the number of matching values in column_2 and group them in ascending or alphabetical order:

      • SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2;

      To perform the same action, but group the results in descending or reverse alphabetical order, append the query with DESC:

      • SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2 DESC;

      Querying Multiple Tables with JOIN Clauses

      JOIN clauses are used to create result-sets that combine rows from two or more tables. A JOIN clause will only work if the two tables each have a column with an identical name and data type, as in this example:

      • SELECT table_1.column_1, table_2.column_2 FROM table_1 JOIN table_2 ON table_1.common_column=table_2.common_column;

      This is an example of an INNER JOIN clause. An INNER JOIN will return all the records that have matching values in both tables, but won't show any records that don't have matching values.

      It's possible to return all the records from one of two tables, including values that do not have a corresponding match in the other table, by using an outer JOIN clause. Outer JOIN clauses are written as either LEFT JOIN or RIGHT JOIN.

      A LEFT JOIN clause returns all the records from the “left” table and only the matching records from the “right” table. In the context of outer JOIN clauses, the left table is the one referenced in the FROM clause, and the right table is any other table referenced after the JOIN statement. The following will show every record from table_1 and only the matching values from table_2. Any values that do not have a match in table_2 will appear as NULL in the result-set:

      • SELECT table_1.column_1, table_2.column_2 FROM table_1 LEFT JOIN table_2 ON table_1.common_column=table_2.common_column;

      A RIGHT JOIN clause functions the same as a LEFT JOIN, but it prints the all the results from the right table, and only the matching values from the left:

      • SELECT table_1.column_1, table_2.column_2 FROM table_1 RIGHT JOIN table_2 ON table_1.common_column=table_2.common_column;

      Combining Multiple SELECT Statements with UNION Clauses

      A UNION operator is useful for combining the results of two (or more) SELECT statements into a single result-set:

      • SELECT column_1 FROM table UNION SELECT column_2 FROM table;

      Additionally, the UNION clause can combine two (or more) SELECT statements querying different tables into the same result-set:

      • SELECT column FROM table_1 UNION SELECT column FROM table_2;

      Conclusion

      This guide covers some of the more common commands in SQL used to manage databases, users, and tables, and query the contents held in those tables. There are, however, many combinations of clauses and operators that all produce unique result-sets. If you're looking for a more comprehensive guide to working with SQL, we encourage you to check out Oracle's Database SQL Reference.

      Additionally, if there are common SQL commands you'd like to see in this guide, please ask or make suggestions in the comments below.



      Source link