One place for hosting & domains

      How To Update Data in SQL


      Introduction

      When working with a database, there may be times when you need to change data that’s already been inserted into it. For example, you may need to correct a misspelled entry or perhaps you have new information to add to an incomplete record. Structured Query Language — more commonly known as SQL — provides the UPDATE keyword which allows users to change existing data in a table.

      This guide outlines how you can use SQL’s UPDATE syntax to change data in one or more tables. It also explains how SQL handles UPDATE operations that conflict with foreign key constraints.

      Prerequisites

      To follow this guide, you’ll need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

      Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.

      • You’ll also need a database with some tables loaded with sample data which you can use to practice updating SQL data. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

      Connecting To MySQL and Setting Up a Sample Database

      If your SQL database system runs on a remote server, SSH into your server from your local machine:

      Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

      Create a database named updateDB:

      • CREATE DATABASE updateDB;

      If the database was created successfully, you’ll receive output like this:

      Output

      Query OK, 1 row affected (0.01 sec)

      To select the updateDB database, run the following USE statement:

      Output

      Database changed

      After selecting the updateDB database, create a couple tables within it. For the examples used in this guide, imagine that you run a talent agency and have decided to begin tracking your clients and their performances in an SQL database. You plan to start off with two tables, the first of which will store information about your clients. You decide this table needs four columns:

      • clientID: each client’s identification number, expressed with the int data type. This column will also serve as the table’s primary key, with each value functioning as a unique identifier for its respective row
      • name: each client’s name, expressed using the varchar data type with a maximum of 20 characters
      • routine: a brief description of each client’s primary performance genre, again expressed using the varchar data type but with a maximum of 30 characters
      • performanceFee: a column to record each client’s standard performance fee, it uses the decimal data type with any values in this column limited to a maximum of five digits in length with two of those digits to the right of the decimal point. Thus, the range of values allowed in this column goes from -999.99 to 999.99

      Create a table named clients that has these four columns:

      • CREATE TABLE clients
      • (clientID int PRIMARY KEY,
      • name varchar(20),
      • routine varchar(30),
      • standardFee decimal (5,2)
      • );

      The second table will store information about your clients’ performances at a local venue. You decide this table needs five columns:

      • showID: similar to the clientID column, this column will hold a unique identification number for each show, expressed with the int data type. Likewise this column will serve as primary key for the shows table
      • showDate: the date of each performance. This column’s values are expressed using the date data type which uses the 'YYYY-MM-DD' format
      • clientID: the ID number of the client performing at the show, expressed as an integer
      • attendance: the number of attendees at each performance, expressed as an integer
      • ticketPrice: the price of an individual ticket at each show. This column uses the decimal data type with any values in this column limited to a maximum of four digits in length with two of those digits to the right of the decimal point, so the range of values allowed in this column is -99.99 to 99.99

      To ensure that the clientID column only holds values that represent valid client ID numbers, you decide to apply a foreign key constraint to the clientID column that references the clients table’s clientID column. A foreign key constraint is a way to express a relationship between two tables by requiring that values in the column on which it applies must already exist in the column that it references. In the following example, the FOREIGN KEY constraint requires that any value added to the clientID column in the shows table must already exist in the client table’s clientID column.

      Create a table named clients that has these five columns:

      • CREATE TABLE shows
      • (showID int PRIMARY KEY,
      • showDate date,
      • clientID int,
      • attendance int,
      • ticketPrice decimal (4,2),
      • CONSTRAINT client_fk
      • FOREIGN KEY (clientID)
      • REFERENCES clients(clientID)
      • );

      Note that this example provides a name for the foreign key constraint: client_fk. MySQL will automatically generate a name for any constraint you add, but defining one here will be useful when we need to reference this constraint later on.

      Next, run the following INSERT INTO statement to load the clients table with five rows of sample data:

      • INSERT INTO clients
      • VALUES
      • (1, 'Gladys', 'song and dance', 180),
      • (2, 'Catherine', 'standup', 99.99),
      • (3, 'Georgeanna', 'standup', 45),
      • (4, 'Wanda', 'song and dance', 200),
      • (5, 'Ann', 'trained squirrel', 79.99);

      Then run another INSERT INTO statement to load the shows table with ten rows of sample data:

      • INSERT INTO shows
      • VALUES
      • (1, '2019-12-25', 4, 124, 15),
      • (2, '2020-01-11', 5, 84, 29.50),
      • (3, '2020-01-17', 3, 170, 12.99),
      • (4, '2020-01-31', 5, 234, 14.99),
      • (5, '2020-02-08', 1, 86, 25),
      • (6, '2020-02-14', 3, 102, 39.5),
      • (7, '2020-02-15', 2, 101, 26.50),
      • (8, '2020-02-27', 2, 186, 19.99),
      • (9, '2020-03-06', 4, 202, 30),
      • (10, '2020-03-07', 5, 250, 8.99);

      With that, you’re ready to follow the rest of the guide and begin learning about how to update data with SQL.

      Updating Data in a Single Table

      The general syntax of an UPDATE statement looks like this:

      • UPDATE table_name
      • SET column_name = value_expression
      • WHERE conditions_apply;

      Following the UPDATE keyword is the name of the table storing the data you want to update. After that is a SET clause which specifies which column’s data should be updated and how. Think of the SET clause as setting values in the specified column as equal to whatever value expression you provide.

      In SQL, a value expression — sometimes known as a scalar expression — is any expression that will return a single value for every row to be updated. This could be a string literal, or a mathematical operation performed on existing numeric values in the column. You must include at least one value assignment in every UPDATE statement, but you can include more than one to update data in multiple columns.

      After the SET clause is a WHERE clause. Including a WHERE clause in an UPDATE statement like in this example syntax allows you to filter out any rows that you don’t want to update. A WHERE clause is entirely optional in UPDATE statements, but if you don’t include one the operation will update every row in the table.

      To illustrate how SQL handles UPDATE operations, start by taking a look at all the data in the clients table. The following query includes an asterisk (*) which is SQL shorthand representing every column in the table, so this query will return all the data from every column in the clients table:

      Output

      +----------+------------+------------------+-------------+ | clientID | name | routine | standardFee | +----------+------------+------------------+-------------+ | 1 | Gladys | song and dance | 180.00 | | 2 | Catherine | standup | 99.99 | | 3 | Georgeanna | standup | 45.00 | | 4 | Wanda | song and dance | 200.00 | | 5 | Ann | trained squirrel | 79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

      Say, for example, that you notice Katherine’s name is misspelled — it should begin with a “K” but in the table it begins with a “C” — so you decide to change that value by running the following UPDATE statement. This operation updates values in the name column by changing the name value of any row with the name Catherine to Katherine:

      • UPDATE clients
      • SET name="Katherine"
      • WHERE name="Catherine";

      Output

      Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

      This output indicates that only one row was updated. You can confirm this by running the previous SELECT query again:

      Output

      +----------+------------+------------------+-------------+ | clientID | name | routine | standardFee | +----------+------------+------------------+-------------+ | 1 | Gladys | song and dance | 180.00 | | 2 | Katherine | standup | 99.99 | | 3 | Georgeanna | standup | 45.00 | | 4 | Wanda | song and dance | 200.00 | | 5 | Ann | trained squirrel | 79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

      This output shows that the value formerly reading Catherine has indeed been changed to Katherine.

      This example updated only one value in the name column. However, you can update multiple values with a less exclusive WHERE clause.

      To illustrate, imagine that you negotiate standard performance fees for all your clients who perform stand-up comedy or song and dance routines. The following statement will update values in the standardFee column by setting them equal to 140.

      Note that this example’s WHERE clause includes a LIKE operator so it only updates the performanceFee value for each client whose routine value matches the specified wildcard pattern, 's%'. In other words, it will update the performance fee of any performer whose routine starts with the letter “s”:

      • UPDATE clients
      • SET standardFee = 140
      • WHERE routine LIKE 's%';

      Output

      Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0

      If you query the contents of the clients table once again, the result set will confirm that four of your clients now have the same performance fee:

      Output

      +----------+------------+------------------+-------------+ | clientID | name | routine | standardFee | +----------+------------+------------------+-------------+ | 1 | Gladys | song and dance | 140.00 | | 2 | Katherine | standup | 140.00 | | 3 | Georgeanna | standup | 140.00 | | 4 | Wanda | song and dance | 140.00 | | 5 | Ann | trained squirrel | 79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

      If any columns in your table hold numeric values, you can update them using an arithmetic operation in the SET clause. To illustrate, say that you also negotiate a forty percent increase for each of your clients’ performance fees. To reflect this in the clients table, you could run an UPDATE operation like this:

      • UPDATE clients
      • SET standardFee = standardFee * 1.4;

      Output

      Query OK, 5 rows affected, 1 warning (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 1

      Note: Notice that this output indicates the update resulted in a warning. Oftentimes, MySQL will issue a warning when it’s forced to make a change to your data because of a column or table’s definition.

      MySQL provides the SHOW WARNINGS shortcut that can help explain any warnings you receive:

      Output

      +-------+------+--------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------+ | Note | 1265 | Data truncated for column 'standardFee' at row 5 | +-------+------+--------------------------------------------------+ 1 row in set (0.00 sec)

      This output informs us that the database system issued the warning because it had to truncate one of the new standardFee values so it would conform to the decimal format — five digits with two to the right of the decimal point — defined previously.

      Query the clients table once more to confirm that each of the clients’ performance fees have increased by forty percent.

      Output

      +----------+------------+------------------+-------------+ | clientID | name | routine | standardFee | +----------+------------+------------------+-------------+ | 1 | Gladys | song and dance | 196.00 | | 2 | Katherine | standup | 196.00 | | 3 | Georgeanna | standup | 196.00 | | 4 | Wanda | song and dance | 196.00 | | 5 | Ann | trained squirrel | 111.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

      As mentioned previously, you can also update data in multiple columns with a single UPDATE statement. To do this, you must specify every column you want to update, following each with the respective value expression, and then separate each column and value expression pair with a comma.

      For example, say that you learn that the venue where your clients perform had misreported the number of attendees for all of Georgeanna and Wanda’s shows. By coincidence, you also happened to enter the wrong ticket price for each of their performances.

      Before updating the data in the shows table, run the following query to retrieve all the data currently held within it:

      Output

      +--------+------------+----------+------------+-------------+ | showID | showDate | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ | 1 | 2019-12-25 | 4 | 124 | 15.00 | | 2 | 2020-01-11 | 5 | 84 | 29.50 | | 3 | 2020-01-17 | 3 | 170 | 12.99 | | 4 | 2020-01-31 | 5 | 234 | 14.99 | | 5 | 2020-02-08 | 1 | 86 | 25.00 | | 6 | 2020-02-14 | 3 | 102 | 39.50 | | 7 | 2020-02-15 | 2 | 101 | 26.50 | | 8 | 2020-02-27 | 2 | 186 | 19.99 | | 9 | 2020-03-06 | 4 | 202 | 30.00 | | 10 | 2020-03-07 | 5 | 250 | 8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.01 sec)

      To reflect the actual numbers and prices, you update the table to add twenty attendees to each of their performances and increase each of their ticketPrice values by fifty percent. You could do so with an operation like the following:

      • UPDATE shows
      • SET attendance = attendance + 20,
      • ticketPrice = ticketPrice * 1.5
      • WHERE clientID IN
      • (SELECT clientID
      • FROM clients
      • WHERE name="Georgeanna" OR name="Wanda");

      Output

      Query OK, 4 rows affected, 1 warning (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 1

      Notice that this example uses a subquery in the WHERE clause to return Georgeanna and Wanda’s clientID values from the clients table. Oftentimes, abstract values like identification numbers can be hard to remember, but this method of using a subquery to find a value can be helpful when you only know certain attributes about the records in question.

      After updating the shows table, query it once again to confirm that the changes went through as expected:

      Output

      +--------+------------+----------+------------+-------------+ | showID | showDate | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ | 1 | 2019-12-25 | 4 | 144 | 22.50 | | 2 | 2020-01-11 | 5 | 84 | 29.50 | | 3 | 2020-01-17 | 3 | 190 | 19.49 | | 4 | 2020-01-31 | 5 | 234 | 14.99 | | 5 | 2020-02-08 | 1 | 86 | 25.00 | | 6 | 2020-02-14 | 3 | 122 | 59.25 | | 7 | 2020-02-15 | 2 | 101 | 26.50 | | 8 | 2020-02-27 | 2 | 186 | 19.99 | | 9 | 2020-03-06 | 4 | 222 | 45.00 | | 10 | 2020-03-07 | 5 | 250 | 8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.00 sec)

      Once again, this output indicates that the UPDATE statement completed successfully.

      Using JOIN Clauses to Update Data in Multiple Tables

      Up to this point, this guide has only shown how to update data in one table at a time. However, some SQL implementations allow you to update multiple columns in multiple tables by temporarily combining the tables with a JOIN clause.

      Here’s the general syntax you can use to update multiple tables with a JOIN clause:

      • UPDATE table_1 JOIN table_2
      • ON table_1.related_column = table_2.related_column
      • SET table_1.column_name = value_expression,
      • table_2.column_name = value_expression
      • WHERE conditions_apply;

      This example syntax begins with the UPDATE keyword followed by the names of two tables, separated by a JOIN clause. Following that is the ON clause, which describes how the query should join the two tables together.

      In most implementations, you can join tables by finding matches between any set of columns that have what the SQL standard refers to as “JOIN eligible” data types. This means that, in general, you can join any column holding numeric data with any other column that holds numeric data, regardless of their respective data types. Likewise, you can join any columns that hold character values with any other column holding character data.

      Note that because JOIN clauses compare the contents of more than one table, this example syntax specifies which table to select each column from by preceding the name of the column with the name of the table and a period. This is known as a fully qualified column reference. You can specify which table a column should be selected from like this for any operation, although it’s typically only used to improve clarity when working with multiple tables.

      To illustrate with the sample tables created previously, run the following UPDATE statement. This will join the clients and shows tables on their respective clientID columns, and then update the routine and ticketPrice values for Gladys’s record in the clients table and each of her performances listed in the shows table:

      • UPDATE clients JOIN shows
      • USING (clientID)
      • SET clients.routine="mime",
      • shows.ticketPrice = 30
      • WHERE name="Gladys";

      Output

      Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0

      Notice that this example joins the tables with the USING keyword instead of the ON keyword used in the previous example syntax. This is possible because both tables have a clientID column that share a similar data type.

      For a more in-depth tutorial on JOIN operations, see our guide on How To Use Joins in SQL.

      Changing Foreign Key UPDATE Behavior

      By default, any UPDATE statement that would cause a conflict with a FOREIGN KEY constraint will fail.

      Recall from the Connecting to MySQL and Setting up a Sample Database section of the Prerequisites that the clientID column of the shows table is a foreign key that references the clientID column of the clients table. This means that any value entered into the shows table’s clientID column must already exist in that of the clients table.

      If you attempt to update the clientID value of a record in the clients table that also appears in the clientID column of the shows table, it will cause an error:

      • UPDATE clients
      • SET clientID = 9
      • WHERE name="Ann";

      Output

      ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

      You can avoid this error by replacing the existing foreign key constraint with one that treats UPDATE operations differently.

      Note: Not every relational database management system or engine allows you to add or remove a constraint from an existing table as outlined in the following paragraphs. If you’re using an RDBMS other than MySQL, you should consult its official documentation to understand what limitations it has for managing constraints.

      To replace the current constraint, you must first remove it with an ALTER TABLE statement. Recall that in the CREATE TABLE statement for shows, we defined client_fk as the name for the table’s FOREIGN KEY constraint:

      • ALTER TABLE shows
      • DROP FOREIGN KEY client_fk;

      Output

      Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

      Following that, create a new foreign key constraint that’s configured to treat UPDATE operations in a way that makes sense for the given use case. Aside from the default setting which prohibits UPDATE statements that violate the foreign key, there are two other options available on most RDBMSs:

      • ON UPDATE SET NULL: This option will allow you to update records from the parent table, and will reset any values in the child table that reference them as NULL.
      • ON UPDATE CASCADE: When you update a row in the parent table, this option will cause SQL to automatically update any records that reference it in the child table so that they align with the new value in the parent table.

      For the purposes of this example, ON UPDATE SET NULL doesn’t make sense. After all, if you change one of your clients’ identification numbers but don’t remove them from the clients table, they should still be associated with their performances in the shows table. Their new identification number should be reflected in their performances’ records, so the ON UPDATE CASCADE option makes more sense for our purposes.

      To add a FOREIGN KEY constraint that follows the ON UPDATE CASCADE behavior, run the following ALTER TABLE statement. This creates a new constraint named new_client_fk which replicates the previous constraint definition, but includes the ON UPDATE CASCADE option:

      • ALTER TABLE shows
      • ADD CONSTRAINT new_client_fk
      • FOREIGN KEY (clientID)
      • REFERENCES clients (clientID)
      • ON UPDATE CASCADE;

      Output

      Query OK, 10 rows affected (0.02 sec) Records: 10 Duplicates: 0 Warnings: 0

      This output tells you that the operation impacted all ten rows in the shows table.

      Note: Instead of altering a table’s definition to change how a foreign key handles UPDATE operations, you can define this behavior from the start in the CREATE TABLE statement like this:

      • CREATE TABLE shows
      • (showID int PRIMARY KEY,
      • showDate date,
      • clientID int,
      • attendance int,
      • ticketPrice decimal (4,2),
      • CONSTRAINT client_fk
      • FOREIGN KEY (clientID)
      • REFERENCES clients(clientID)
      • ON UPDATE CASCADE
      • );

      Following that, you’ll be able to update the clientID value of any record in the clients table, and those changes will cascade down to any rows in the shows table that reference it:

      • UPDATE clients
      • SET clientID = 9
      • WHERE name="Ann";

      Output

      Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

      Although this output says it only affected one row, it will have also updated the clientID value of any performance records in the shows table associated with Ann. To confirm this, run the following query to retrieve all the data from the shows table:

      Output

      +--------+------------+----------+------------+-------------+ | showID | showDate | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ | 1 | 2019-12-25 | 4 | 144 | 22.50 | | 2 | 2020-01-11 | 9 | 84 | 29.50 | | 3 | 2020-01-17 | 3 | 190 | 19.49 | | 4 | 2020-01-31 | 9 | 234 | 14.99 | | 5 | 2020-02-08 | 1 | 86 | 30.00 | | 6 | 2020-02-14 | 3 | 122 | 59.25 | | 7 | 2020-02-15 | 2 | 101 | 26.50 | | 8 | 2020-02-27 | 2 | 186 | 19.99 | | 9 | 2020-03-06 | 4 | 222 | 45.00 | | 10 | 2020-03-07 | 9 | 250 | 8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.00 sec)

      As expected, the update made to the clientID column in the clients table cascaded down to the associated rows in the shows table.

      Conclusion

      By reading this guide, you learned how to change existing records in or more tables using SQL’s UPDATE statement. You also learned how SQL handles UPDATE operations that conflict with foreign key constraints, and how to change that default behavior.

      The commands outlined here should work on any database management system that uses SQL. Keep in mind that every SQL database uses its own unique implementation of the language, so you should consult your DBMS’s official documentation for a more complete description of how it handles UPDATE operations and what options are available for them.

      If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.



      Source link

      How To Use Joins in SQL


      Introduction

      Many database designs separate information into different tables based on the relationships between certain data points. Even in cases like this, it’s likely that there will be times when someone will want to retrieve information from more than one table at a time.

      A common way of accessing data from multiple tables in a single Structured Query Language (SQL) operation is to combine the tables with a JOIN clause. Based on join operations in relational algebra, a JOIN clause combines separate tables by matching up rows in each table that relate to one another. Usually, this relationship is based on a pair of columns — one from each table — that share common values, such as one table’s foreign key and the primary key of another table that the foreign key references.

      This guide outlines how to construct a variety of SQL queries that include a JOIN clause. It also highlights different types of JOIN clauses, how they combine data from multiple tables, and how to alias column names to make writing JOIN operations less tedious.

      Prerequisites

      In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

      Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.

      • You’ll also need a database with some tables loaded with sample data which you can use to practice using JOIN operations. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

      Connecting to and Setting up a Sample Database

      If your SQL database system runs on a remote server, SSH into your server from your local machine:

      Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

      Create a database named joinsDB:

      If the database was created successfully, you’ll receive output like this:

      Output

      Query OK, 1 row affected (0.01 sec)

      To select the joinsDB database, run the following USE statement:

      Output

      Database changed

      After selecting joinsDB, create a few tables within it. For the examples used in this guide, imagine that you run a factory and have decided to begin tracking information about your product line, employees on your sales team, and your company’s sales in an SQL database. You plan to start off with three tables, the first of which will store information about your products. You decide this first table needs three columns:

      • productID: each product’s identification number, expressed with the int data type. This column will serve as the table’s primary key, meaning that each value will function as a unique identifier for its respective row. Because every value in a primary key must be unique, this column will also have a UNIQUE constraint applied to it
      • productName: each product’s name, expressed using the varchar data type with a maximum of 20 characters
      • price: the price of each product, expressed using the decimal data type. This statement specifies that any values in this column are limited to a maximum of four digits in length with two of those digits to the right of the decimal point. Thus, the range of values allowed in this column goes from -99.99 to 99.99

      Create a table named products that has these three columns:

      • CREATE TABLE products (
      • productID int UNIQUE,
      • productName varchar(20),
      • price decimal (4,2),
      • PRIMARY KEY (productID)
      • );

      The second table will store information about the employees on your company’s sales team. You decide this table also needs three columns:

      • empID: similar to the productID column, this column will hold a unique identification number for each employee on the sales team expressed with the int data type. Likewise, this column will have a UNIQUE constraint applied to it and will serve as the primary key for the team table
      • empName: the name of each salesperson, expressed using the varchar data type with a maximum of 20 characters
      • productSpecialty: each member of your sales team has been assigned a product as their specialty; they can sell any product your company makes, but their overall focus will be on whatever product they specialize in. To indicate this in the table, you create this column which holds the productID value of whatever product each employee specializes in

      To ensure that the productSpecialty column only holds values that represent valid product ID numbers, you decide to apply a foreign key constraint to the column that references the products table’s productID column. A foreign key constraint is a way to express a relationship between two tables by requiring that values in the column on which it applies must already exist in the column that it references. In the following CREATE TABLE statement, the FOREIGN KEY constraint requires that any value added to the productSpecialty column in the team table must already exist in the products table’s productID column.

      Create a table named team with these three columns:

      • CREATE TABLE team (
      • empID int UNIQUE,
      • empName varchar(20),
      • productSpecialty int,
      • PRIMARY KEY (empID),
      • FOREIGN KEY (productSpecialty) REFERENCES products (productID)
      • );

      The last table you create will hold records of the company’s sales. This table will have four columns:

      • saleID: similar to the productID and empIDcolumns, this column will hold a unique identification number for each sale expressed with the int data type. This column will also have a UNIQUE constraint so it can serve as the primary key for the sales table
      • quantity: the number of units of each product sold, expressed with the int data type
      • productID: the identification number of the product sold, expressed as an int
      • salesperson: the identification number of the employee who made the sale

      Like the productSpecialty column from the team table, you decide to apply FOREIGN KEY constraints to both the productID and salesperson columns. This will ensure that these columns only contain values that already exist in the products table’s productID column and the team table’s empID columns, respectively.

      Create a table named sales with these four columns:

      • CREATE TABLE sales (
      • saleID int UNIQUE,
      • quantity int,
      • productID int,
      • salesperson int,
      • PRIMARY KEY (saleID),
      • FOREIGN KEY (productID) REFERENCES products (productID),
      • FOREIGN KEY (salesperson) REFERENCES team (empID)
      • );

      Following that, load the products table with some sample data by running the following INSERT INTO operation:

      • INSERT INTO products
      • VALUES
      • (1, 'widget', 18.99),
      • (2, 'gizmo', 14.49),
      • (3, 'thingamajig', 39.99),
      • (4, 'doodad', 11.50),
      • (5, 'whatzit', 29.99);

      Then load the team table with some sample data:

      • INSERT INTO team
      • VALUES
      • (1, 'Florence', 1),
      • (2, 'Mary', 4),
      • (3, 'Diana', 3),
      • (4, 'Betty', 2);

      Load the sales table with some sample data as well:

      • INSERT INTO sales
      • VALUES
      • (1, 7, 1, 1),
      • (2, 10, 5, 4),
      • (3, 8, 2, 4),
      • (4, 1, 3, 3),
      • (5, 5, 1, 3);

      Lastly, imagine that your company makes a few sales without the involvement of anyone on your sales team. To record these sales, run the following operation to add three rows to the sales table that don’t include a value for the salesperson column:

      • INSERT INTO sales (saleID, quantity, productID)
      • VALUES
      • (6, 1, 5),
      • (7, 3, 1),
      • (8, 4, 5);

      With that, you’re ready to follow the rest of the guide and begin learning about how to join tables together in SQL.

      Understanding the Syntax of JOIN Operations

      JOIN clauses can be used in a variety of SQL statements, including UPDATE and DELETE operations. For illustration purposes, though, the examples in this guide use SELECT queries to demonstrate how JOIN clauses work.

      The following example shows the general syntax of a SELECT statement that includes a JOIN clause:

      • SELECT table1.column1, table2.column2
      • FROM table1 JOIN table2
      • ON search_condition;

      This syntax begins with a SELECT statement that will return two columns from two separate tables. Note that because JOIN clauses compare the contents of more than one table, this example syntax specifies which table to select each column from by preceding the name of the column with the name of the table and a period. This is known as a fully qualified column reference.

      You can use fully qualified column references like these in any operation, but doing so is technically only necessary in operations where two columns from different tables share the same name. It’s good practice to use them when working with multiple tables, though, as they can help make JOIN operations easier to read and understand.

      After the SELECT clause comes the FROM clause. In any query, the FROM clause is where you define the data set that should be searched in order to return the desired data. The only difference here is that the FROM clause includes two tables separated by the JOIN keyword. A helpful way to think of writing queries is to remember that you SELECT which columns to return FROM which table you’d like to query.

      Following that is an ON clause, which describes how the query should join the two tables together by defining a search condition. A search condition is a set of one or more predicates, or expressions that can evaluate whether a certain condition is “true,” “false,” or “unknown.” It can be helpful to think of a JOIN operation as combining every row from both tables, and then returning any rows for which the search condition in the ON clause evaluates to “true”.

      In an ON clause, it usually makes sense to include a search condition that tests whether two related columns — like one table’s foreign key and the primary key of another table that the foreign key references — have values that are equal. This is sometimes referred to as an equi join.

      As an example of how equi joins match data from multiple tables, run the following query using the sample data you added previously. This statement will join the products and team tables with a search condition that tests for matching values in their respective productID and productSpecialty columns. It will then return the names of every member of the sales team, the name of each product they specialize in, and the price of those products:

      • SELECT team.empName, products.productName, products.price
      • FROM products JOIN team
      • ON products.productID = team.productSpecialty;

      Here is this query’s result set:

      Output

      +----------+-------------+-------+ | empName | productName | price | +----------+-------------+-------+ | Florence | widget | 18.99 | | Mary | doodad | 11.50 | | Diana | thingamajig | 39.99 | | Betty | gizmo | 14.49 | +----------+-------------+-------+ 4 rows in set (0.00 sec)

      To illustrate how SQL combines these tables to form this result set, let’s take a closer look at this process. To be clear, the following isn’t exactly what happens when a database management system joins two tables together, but it can be helpful to think of JOIN operations as following a procedure like this.

      First, the query prints every row and column in the first table in the FROM clause, products:

      JOIN Process Example

      +-----------+-------------+-------+ | productID | productName | price | +-----------+-------------+-------+ | 1 | widget | 18.99 | | 2 | gizmo | 14.49 | | 3 | thingamajig | 39.99 | | 4 | doodad | 11.50 | | 5 | whatzit | 29.99 | +-----------+-------------+-------+

      Then, it looks at each of these rows and matches any row from the team table whose productSpecialty is equal to the productID value in that row:

      JOIN Process Example

      +-----------+-------------+-------+-------+----------+------------------+ | productID | productName | price | empID | empName | productSpecialty | +-----------+-------------+-------+-------+----------+------------------+ | 1 | widget | 18.99 | 1 | Florence | 1 | | 2 | gizmo | 14.49 | 4 | Betty | 2 | | 3 | thingamajig | 39.99 | 3 | Diana | 3 | | 4 | doodad | 11.50 | 2 | Mary | 4 | | 5 | whatzit | 29.99 | | | | +-----------+-------------+-------+-------+----------+------------------+

      Then, it cuts any rows that don’t have a match and rearranges the columns based on their order in the SELECT clause, drops any columns that weren’t specified, resorts the rows, and returns the final result set:

      JOIN Process Example

      +----------+-------------+-------+ | empName | productName | price | +----------+-------------+-------+ | Florence | widget | 18.99 | | Mary | doodad | 11.50 | | Diana | thingamajig | 39.99 | | Betty | gizmo | 14.49 | +----------+-------------+-------+ 4 rows in set (0.00 sec)

      Using equi joins is the most common way to join tables, but it’s possible to use other SQL operators such as <, >, LIKE, NOT LIKE, or even BETWEEN in ON clause search conditions. Be aware, though, that using more complicated search conditions can make it difficult to predict what data will appear in the result set.

      In most implementations, you can join tables with any set of columns that have what the SQL standard refers to as “JOIN eligible” data type. This means that, in general, it’s possible to join a column that holds numeric data with any other column that holds numeric data, regardless of their respective data types. Likewise, it’s usually possible to join any column that holds character values with any other column holding character data. As stated previously, though, the columns you match to join two tables will typically be ones that already signify a relationship between the tables, like a foreign key and the primary key of another table that it references.

      Many SQL implementations also allow you to join columns that have the same name with the USING keyword instead of ON. This is how the syntax for such an operation might look:

      • SELECT table1.column1, table2.column2
      • FROM table1 JOIN table2
      • USING (related_column);

      In this example syntax, the USING clause is equivalent to ON table1.related_column = table2.related_column;.

      Because sales and products each have a column named productID, you can join them by matching these columns with the USING keyword. The following command does this, and returns the saleID of each sale, the quantity of units sold, the name of each product sold, and its price. Also, it sorts the result set in ascending order based on the saleID value:

      • SELECT sales.saleID, sales.quantity, products.productName, products.price
      • FROM sales JOIN products
      • USING (productID)
      • ORDER BY saleID;

      Output

      +--------+----------+-------------+-------+ | saleID | quantity | productName | price | +--------+----------+-------------+-------+ | 1 | 7 | widget | 18.99 | | 2 | 10 | whatzit | 29.99 | | 3 | 8 | gizmo | 14.49 | | 4 | 1 | thingamajig | 39.99 | | 5 | 5 | widget | 18.99 | | 6 | 1 | whatzit | 29.99 | | 7 | 3 | widget | 18.99 | | 8 | 4 | whatzit | 29.99 | +--------+----------+-------------+-------+ 8 rows in set (0.00 sec)

      When joining tables, the database system will sometimes rearrange rows in ways that aren’t easy to predict. Including an ORDER BY clause like this can help make result sets more coherent and readable.

      Joining More than Two Tables

      There may be times when you need to combine data from more than just two tables. You can join any number of tables together by embedding JOIN clauses within other JOIN clauses. The following syntax is an example of how this can look when joining three tables:

      • SELECT table1.column1, table2.column2, table3.column3
      • FROM table1 JOIN table2
      • ON table1.related_column = table2.related_column
      • JOIN table3
      • ON table3.related_column = table1_or_2.related_column;

      This example syntax’s FROM clause starts by joining table1 with table2. After this joining’s ON clause, it starts a second JOIN that combines the initial set of joined tables with table3. Note that the third table can be joined to a column in either the first or second table.

      To illustrate, imagine that you want to know how much revenue your employee’s sales have brought in, but you only care about sales records that involve an employee selling the product they specialize in.

      To get this information, you could run the following query. This query starts by joining the products and sales tables together by matching their respective productID columns. It then joins the team table to the first two by matching each row in the initial JOIN to its productSpecialty column. The query then filters the results with a WHERE clause to only return rows where the matched employee was also the person who made the sale. This query also includes an ORDER BY clause that sorts the final results in ascending order based on the value in the saleID column:

      • SELECT sales.saleID,
      • team.empName,
      • products.productName,
      • (sales.quantity * products.price)
      • FROM products JOIN sales
      • USING (productID)
      • JOIN team
      • ON team.productSpecialty = sales.productID
      • WHERE team.empID = sales.salesperson
      • ORDER BY sales.saleID;

      Note that among the columns listed in this query’s SELECT clause is an expression that multiplies the values in the sales table’s quantity column by the products table’s price values. It returns the products of these values in the matched rows:

      Output

      +--------+----------+-------------+-----------------------------------+ | saleID | empName | productName | (sales.quantity * products.price) | +--------+----------+-------------+-----------------------------------+ | 1 | Florence | widget | 132.93 | | 3 | Betty | gizmo | 115.92 | | 4 | Diana | thingamajig | 39.99 | +--------+----------+-------------+-----------------------------------+ 3 rows in set (0.00 sec)

      All the examples so far have featured the same type of JOIN clause: the INNER JOIN. For an overview of INNER joins, OUTER joins, and how they differ, continue reading the next section.

      Inner vs. Outer JOIN Operations

      There are two main types of JOIN clauses: INNER joins and OUTER joins. The difference between these two types of joins has to do with what data they return. INNER join operations return only matching rows from each joined table, while OUTER joins return both matching and non-matching rows.

      The example syntaxes and queries from the previous sections all used INNER JOIN clauses even though none of them include the INNER keyword. Most SQL implementations treat any JOIN clause as an INNER join unless explicitly stated otherwise.

      Queries that specify an OUTER JOIN combine multiple tables and return any rows that match as well as rows that do not match. This can be useful for finding rows with missing values, or in cases where partial matches are acceptable.

      OUTER join operations can be further divided into three types: LEFT OUTER joins, RIGHT OUTER joins, and FULL OUTER joins. LEFT OUTER joins, or just LEFT joins, return every matching row from the two joined tables, as well as every non-matching row from the “left” table. In the context of JOIN operations, the “left” table is always the first table specified immediately after the FROM keyword and to the left of the JOIN keyword. Likewise, the “right” table is the second table, or the one immediately following JOIN, and RIGHT OUTER joins return every matching row from the joined tables along with every non-matching row from the “right” table. A FULL OUTER JOIN returns every row from both tables, including any rows from either table that don’t have matches.

      To illustrate how these different types of JOIN clauses return data, run the following example queries on the tables created in the previous Connecting to and Setting up a Sample Database subsection. These queries are identical except that each specifies a different type of JOIN clause.

      This first example uses an INNER JOIN to combine the sales and team tables together by matching their respective salesperson and empID columns. Again, the INNER keyword is implied even though it’s not explicitly included:

      • SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
      • FROM sales JOIN team
      • ON sales.salesperson = team.empID;

      Because this query uses an INNER JOIN clause, it only returns matching rows from both tables:

      Output

      +--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName | +--------+----------+-------------+----------+ | 1 | 7 | 1 | Florence | | 4 | 1 | 3 | Diana | | 5 | 5 | 3 | Diana | | 2 | 10 | 4 | Betty | | 3 | 8 | 4 | Betty | +--------+----------+-------------+----------+ 5 rows in set (0.00 sec)

      This version of the query uses a LEFT OUTER JOIN clause instead:

      • SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
      • FROM sales LEFT OUTER JOIN team
      • ON sales.salesperson = team.empID;

      Like the previous query, this one also returns every matching value from both tables. However, it also returns any values from the “left” table (in this case, sales) that don’t have matches in the “right” table (team). Because these rows in the left table don’t have matches in the right, the unmatched values are returned as NULL:

      Output

      +--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName | +--------+----------+-------------+----------+ | 1 | 7 | 1 | Florence | | 2 | 10 | 4 | Betty | | 3 | 8 | 4 | Betty | | 4 | 1 | 3 | Diana | | 5 | 5 | 3 | Diana | | 6 | 1 | NULL | NULL | | 7 | 3 | NULL | NULL | | 8 | 4 | NULL | NULL | +--------+----------+-------------+----------+ 8 rows in set (0.00 sec)

      This next version of the query instead uses a RIGHT JOIN clause:

      • SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
      • FROM sales RIGHT JOIN team
      • ON sales.salesperson = team.empID;

      Notice that this query’s JOIN clause reads RIGHT JOIN instead of RIGHT OUTER JOIN. Similarly to how the INNER keyword isn’t required to specify an INNER JOIN clause, OUTER is implied any time you write LEFT JOIN or RIGHT JOIN.

      This query’s result is the opposite of the previous one in that it returns every row from both tables, but only the unmatched rows from the “right” table:

      Output

      +--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName | +--------+----------+-------------+----------+ | 1 | 7 | 1 | Florence | | NULL | NULL | NULL | Mary | | 4 | 1 | 3 | Diana | | 5 | 5 | 3 | Diana | | 2 | 10 | 4 | Betty | | 3 | 8 | 4 | Betty | +--------+----------+-------------+----------+ 6 rows in set (0.00 sec)

      Note: Be aware that MySQL doesn’t support FULL OUTER JOIN clauses. To illustrate what data this query would return if it used a FULL OUTER JOIN clause, here’s what the result set would look like on a PostgreSQL database:

      • SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
      • FROM sales FULL OUTER JOIN team
      • ON sales.salesperson = team.empID;

      Output

      saleid | quantity | salesperson | empname --------+----------+-------------+---------- 1 | 7 | 1 | Florence 2 | 10 | 4 | Betty 3 | 8 | 4 | Betty 4 | 1 | 3 | Diana 5 | 5 | 3 | Diana 6 | 1 | | 7 | 3 | | 8 | 4 | | | | | Mary (9 rows)

      As this output indicates, the FULL JOIN returns every row in both tables including the unmatched ones.

      Aliasing Table and Column Names in JOIN Clauses

      When joining tables with long or highly descriptive names, having to write multiple fully qualified column references can become tedious. To avoid this, users sometimes find it helpful to provide table or column names with shorter aliases.

      You can do this in SQL by following any table definition in the FROM clause with the AS keyword, and then following that with an alias of your choice:

      • SELECT t1.column1, t2.column2
      • FROM table1 AS t1 JOIN table2 AS t2
      • ON t1.related_column = t2.related_column;

      This example syntax uses aliases in the SELECT clause even though they aren’t defined until the FROM clause. This is possible because, in SQL queries, the order of execution begins with the FROM clause. This can be confusing, but it’s helpful to remember this and think of your aliases before you begin writing the query.

      As an example, run the following query which joins the sales and products tables and provides them with the aliases S and P, respectively:

      • SELECT S.saleID, S.quantity,
      • P.productName,
      • (P.price * S.quantity) AS revenue
      • FROM sales AS S JOIN products AS P
      • USING (productID);

      Notice that this example also creates a third alias, revenue, for the product of the values in the sales table’s quantity column and their matching values from the products table’s price column. This is only apparent in the column name in the result set, but providing aliases like this can be helpful for conveying the meaning or purpose behind query results:

      Output

      +--------+----------+-------------+---------+ | saleID | quantity | productName | revenue | +--------+----------+-------------+---------+ | 1 | 7 | widget | 132.93 | | 2 | 10 | whatzit | 299.90 | | 3 | 8 | gizmo | 115.92 | | 4 | 1 | thingamajig | 39.99 | | 5 | 5 | widget | 94.95 | | 6 | 1 | whatzit | 29.99 | | 7 | 3 | widget | 56.97 | | 8 | 4 | whatzit | 119.96 | +--------+----------+-------------+---------+ 8 rows in set (0.00 sec)

      Note that when defining an alias the AS keyword is technically optional. The previous example could also be written like this:

      • SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue
      • FROM sales S JOIN products P
      • USING (productID);

      Even though the AS keyword isn’t needed to define an alias, it’s considered a good practice to include it. Doing so can help keep the query’s purpose clear and improve its readability.

      Conclusion

      By reading this guide, you learned how to use JOIN operations to combine separate tables into a single query result set. While the commands shown here should work on most relational databases, be aware that every SQL database uses its own unique implementation of the language. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

      If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.



      Source link

      How To Create and Manage Tables in SQL


      Introduction

      Tables are the primary organizational structure in SQL databases. They comprise a number of columns that reflect individual attributes of each row, or record, in the table. Being such a fundamental aspect of data organization, it’s important for anyone who works with relational databases to understand how to create, change, and delete tables as needed.

      In this guide, we’ll go over how to create tables in SQL, as well as how to modify and delete existing tables.

      Prerequisites

      In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

      Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.

      You’ll also need a database and table loaded with some sample data with which you can practice using wildcards. If you don’t have these, you can read the following Connecting to MySQL and Setting up a Sample Database section for details on how to create a database and table which this guide will use in examples throughout.

      Connecting To MySQL and Setting Up a Sample Database

      If your SQL database system runs on a remote server, SSH into your server from your local machine:

      Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

      Create a database named tablesDB:

      • CREATE DATABASE tablesDB;

      If the database was created successfully, you’ll receive output like this:

      Output

      Query OK, 1 row affected (0.01 sec)

      To select the tablesDB database, run the following USE statement:

      Output

      Database changed

      With that, you’re ready to follow the rest of the guide and begin learning about how to create and manage tables in SQL.

      Creating Tables

      To create a table in SQL, use the CREATE TABLE command, followed by your desired name for the table:

      Be aware that, as with every SQL statement, CREATE TABLE statements must end with a semicolon (;).

      This example syntax will create an empty table that doesn’t have any columns. To create a table with columns, follow the table name with a list of column names and their corresponding data types and constraints, bracketed by parentheses and separated by commas:

      • CREATE TABLE table_name (
      • column1_name column1_data_type,
      • column2_name column2_data_type,
      • . . .
      • columnN_name columnN_data_type
      • );

      As an example, say you wanted to create a table to record some information about your favorite parks in New York City. After deciding what attributes you’d like to record about each park, you would then decide on column names for each of those attributes as well as the appropriate data type for each one:

      • parkName: The name of each park. There is a wide variance in the length of park names, so the varchar data type with a maximum length of 30 characters would be appropriate.
      • yearBuilt: The year the park was built. Although MySQL has the year data type, this only allows values from 1901 to 2155. New York City has several parks built before 1901, so you might instead use the int data type.
      • firstVisit: The date of your first visit to each park. MySQL has the date data type which you might use for this column. It stores data in the format of YYYY-MM-DD.
      • lastVisit: The date of your most recent visit to each park. Again, you could use the date type for this.

      To create a table named faveParks with columns that have these names and data types, you would run the following command:

      • CREATE TABLE faveParks (
      • parkName varchar(30),
      • yearBuilt int,
      • firstVisit date,
      • lastVisit date
      • );

      Output

      Query OK, 0 rows affected (0.01 sec)

      Keep in mind that this only creates the table’s structure, as you haven’t added any data to the table.

      You can also create new tables out of existing ones with the CREATE TABLE AS syntax:

      • CREATE TABLE new_table_name AS (
      • SELECT column1, column2, . . . columnN
      • FROM old_table_name
      • );

      Instead of following the new table’s name with a list of columns and their data types, you follow it with AS and then, in parentheses, a SELECT statement that returns whatever columns and data from the original table you’d like to copy over to the new table.

      Note that if the original table’s columns hold any data, all that data will be copied into the new table as well. Also, for clarity, this example syntax includes a SELECT query that only has the requisite FROM clause. However, any valid SELECT statement will work in this place.

      To illustrate, the following command creates a table named parkInfo from two columns in the faveParks table created previously:

      • CREATE TABLE parkInfo AS (
      • SELECT parkName, yearBuilt
      • FROM faveParks
      • );

      Output

      Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

      If the faveParks table had held any data, the data from its parkName and yearBuilt columns would have been copied to the parkInfo table as well, but in this case both tables will be empty.

      If you try creating a table using the name of an existing table, it will cause an error:

      • CREATE TABLE parkInfo (
      • name varchar(30),
      • squareFootage int,
      • designer varchar(30)
      • );

      Output

      ERROR 1050 (42S01): Table 'parkInfo' already exists

      To avoid this error, you can include the IF NOT EXISTS option in your CREATE TABLE command. This will tell the database to check whether a database with the specified name already exists and, if so, to issue a warning instead of an error:

      • CREATE TABLE IF NOT EXISTS parkInfo (
      • name varchar(30),
      • squareFootage int,
      • designer varchar(30)
      • );

      Output

      Query OK, 0 rows affected, 1 warning (0.00 sec)

      This command will still fail to create a new table, since the table named parkInfo still exists. Notice, though, that this output indicates that the CREATE TABLE statement led to a warning. To view the warning message, run the diagnostic SHOW WARNINGS statement:

      Output

      | Level | Code | Message | +-------+------+---------------------------------+ | Note | 1050 | Table 'parkInfo' already exists | +-------+------+---------------------------------+ 1 row in set (0.00 sec)

      As this output indicates, the same error you received previously has been registered as a warning because you included the IF NOT EXISTS option. This can be useful in certain cases, like when running transactions; an error will cause the entire transaction to fail, while a warning will mean only the statement that caused it will fail.

      Altering Tables

      There are times when you may need to change a table’s definition. This is different from updating the data within the table; instead, it involves changing the structure of the table itself. To do this, you would use the ALTER TABLE syntax:

      • ALTER TABLE table_name ALTER_OPTION sub_options . . . ;

      After beginning the ALTER TABLE statement, you specify the name of the table you want to change. Then, you pass whichever options are available in your RDBMS to perform the alteration you have in mind.

      For example, you may want to rename the table, add a new column, drop an old one, or change a column’s definition. You can continue reading to practice these examples on the faveParks table created previously in the Creating Tables section.

      To change the name of the faveParks table, you could use the RENAME TO syntax. This example changes the faveParks table’s name to faveNYCParks:

      Warning: Be careful when renaming a table. Doing so can cause problems if an application uses the table or other tables in the database reference it.

      • ALTER TABLE faveParks RENAME TO faveNYCParks;

      Output

      Query OK, 0 rows affected (0.01 sec)

      To add a new column, you’d pass the ADD COLUMN option. The following example adds a column named borough, which holds data of the varchar type, but with a maximum length of 20 characters, to the faveNYCParks table:

      • ALTER TABLE faveNYCParks ADD COLUMN borough varchar(20);

      Output

      Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

      To delete a column and any data it holds from a table, you could use the DROP TABLE syntax. This example command drops the borough column:

      • ALTER TABLE faveNYCParks DROP COLUMN borough;

      Many SQL implementations allow you to change a column’s definition with ALTER TABLE. The following example uses MySQL’s MODIFY COLUMN clause, changing the yearBuilt column to use the smallint data type rather than the original int type:

      • ALTER TABLE faveNYCParks MODIFY COLUMN yearBuilt smallint;

      Be aware that every RDBMS has different options for what you can change with an ALTER TABLE statement. To understand the full scope of what you can do with ALTER TABLE, you should consult your RDBMS’s official documentation to learn what ALTER TABLE options are available for it.

      Here’s the official documentation on the subject for a few popular open-source databases:

      Deleting Tables

      To delete a table and all of its data, use the DROP TABLE syntax:

      Warning: Be careful when running the DROP TABLE command, as it will delete your table and all its data permanently.

      You can delete multiple tables with a single DROP statement by separating their names with a comma and a space, like this:

      • DROP TABLE table1, table2, table3;

      To illustrate, the following command will delete the faveNYCParks and parkInfo tables created earlier in this guide:

      • DROP TABLE IF EXISTS faveNYCParks, parkInfo;

      Note that this example includes the IF EXISTS option. This has the opposite function of the IF NOT EXISTS option available for CREATE TABLE. In this context, IF EXISTS will cause the DROP TABLE statement to return a warning instead of an error message if one of the specified tables doesn’t exist.

      Conclusion

      By reading this guide, you learned how to create, change, and delete tables in SQL-based databases. The commands outlined here should work on any database management system that uses SQL. Keep in mind that every SQL database uses its own unique implementation of the language, so you should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

      If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.



      Source link