One place for hosting & domains

      Tables

      How To SELECT Rows FROM Tables in SQL


      Introduction

      One of the most fundamental parts of working with databases is the practice of retrieving information about the data held within them. In relational database management systems, any operation used to retrieve information from a table is referred to as a query.

      In this guide, we will discuss the syntax of queries in Structured Query Language (SQL) as well as some of their more commonly used functions and operators.

      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 writing queries. 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 database server prompt. If you’re using MySQL, do so by running the following command, making sure to replace sammy with the name of your MySQL user account:

      From the prompt, create a database named queries_db:

      • CREATE DATABASE queries_db;

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

      Output

      Query OK, 1 row affected (0.01 sec)

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

      Output

      Database changed

      After selecting queries_db, create a few tables within it.

      To follow along with the examples used in this guide, imagine that you run a public parks cleanup initiative in New York City. The program is made up of volunteers who commit to cleaning up a city park near their homes by regularly picking up litter. Upon joining the initiative, these volunteers each set a goal of how many trash bags of litter they’d like to pick up each week. You decide to store information about the volunteers’ goals in an SQL database with a table that has five columns:

      • vol_id: each volunteer’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
      • name: each volunteer’s name, expressed using the varchar data type with a maximum of 20 characters
      • park: the name of the park where each volunteer will pick up litter, expressed using the varchar data type with a maximum of 20 characters. Note that multiple volunteers can clean up litter in the same park
      • weekly_goal: each volunteer’s goal for how many bags of litter they’d like to pick up in a week, expressed with the int type
      • max_bags: each volunteer’s personal record for the most bags of litter they picked up in a single week, expressed as an int

      Run the following CREATE TABLE statement to create a table named volunteers that has these five columns:

      • CREATE TABLE volunteers (
      • vol_id int UNIQUE,
      • name varchar(20),
      • park varchar(30),
      • weekly_goal int,
      • max_bags int,
      • PRIMARY KEY (vol_id)
      • );

      Then load the volunteers table with some sample data. Run the following INSERT INTO operation to add seven rows of data representing seven of the program’s volunteers:

      • INSERT INTO volunteers
      • VALUES
      • (1, 'Gladys', 'Prospect Park', 3, 5),
      • (2, 'Catherine', 'Central Park', 2, 2),
      • (3, 'Georgeanna', 'Central Park', 2, 1),
      • (4, 'Wanda', 'Van Cortland Park', 1, 1),
      • (5, 'Ann', 'Prospect Park', 2, 7),
      • (6, 'Juanita', 'Riverside Park', 1, 4),
      • (7, 'Georgia', 'Prospect Park', 1, 3);

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

      Required Query Components: the SELECT and FROM Clauses

      In SQL, a statement is any operation sent to the database system that will perform some sort of task, like creating a table, inserting or deleting data, or changing the structure of a column or table. A query is an SQL statement that retrieves information about data held in a database.

      On its own, a query will not change any existing data held in a table. It will only return the information about the data which the author of the query explicitly requests. The information returned by a given query is referred to as its result set. Result sets typically consist of one or more columns from a specified table, and each column returned in a result set can hold one or more rows of information.

      Here’s the general syntax of an SQL query:

      • SELECT columns_to_return
      • FROM table_to_query;

      SQL statements are made up of various clauses, which consist of certain keywords and the information that these keywords require. At a minimum, SQL queries only require you to include two clauses: the SELECT and FROM clauses.

      Note: In this example syntax, both clauses are written on their own line. However, any SQL statement can alternatively be written on a single line, like this:

      • SELECT columns_to_return FROM table_to_query;

      This guide will follow the common SQL style convention of separating statements onto multiple lines so each line contains only one clause. This aimed to make each example more readable and understandable, but be aware that as long as you don’t include any syntax errors you can write any query on a single line or on as many lines as you’d like.

      Every SQL query begins with a SELECT clause, leading some to refer to queries generally as SELECT statements. After the SELECT keyword comes a list of whatever columns you want returned in the result set. These columns are drawn from the table specified in the FROM clause.

      In SQL queries, the order of execution begins with the FROM clause. This can be confusing since the SELECT clause is written before the FROM clause, but keep in mind that the RDBMS must first know the full working data set to be queried before it starts retrieving information from it. It can be helpful to think of queries as SELECT-ing the specified columns FROM the specified table. Lastly, it’s important to note that every SQL statement must end with a semicolon (;).

      As an example, run the following query. This will retrieve the name column from the volunteers table:

      • SELECT name
      • FROM volunteers;

      Here’s this query’s result set:

      Output

      +------------+ | name | +------------+ | Gladys | | Catherine | | Georgeanna | | Wanda | | Ann | | Juanita | | Georgia | +------------+ 7 rows in set (0.00 sec)

      Even though this operation looked at the entire volunteers table, it only returns the specified column, name.

      You can retrieve information from multiple columns by separating each one’s name with a comma, as in the following query. This will return the vol_id, name, and park columns from the volunteers table:

      • SELECT park, name, vol_id
      • FROM volunteers;

      Output

      +-------------------+------------+--------+ | park | name | vol_id | +-------------------+------------+--------+ | Prospect Park | Gladys | 1 | | Central Park | Catherine | 2 | | Central Park | Georgeanna | 3 | | Van Cortland Park | Wanda | 4 | | Prospect Park | Ann | 5 | | Riverside Park | Juanita | 6 | | Prospect Park | Georgia | 7 | +-------------------+------------+--------+ 7 rows in set (0.00 sec)

      Notice that this result set returns the park column first, followed by the name column and then vol_id. SQL databases will generally return columns in whatever order they’re listed in the SELECT clause.

      There may be times when you want to retrieve every column from a table. Rather than writing out the name of every column in your query, you can instead enter an asterisk (*). In SQL, this is shorthand for “every column.”

      The following query will return every column from the volunteers table:

      • SELECT *
      • FROM volunteers;

      Output

      +--------+------------+-------------------+-------------+----------+ | vol_id | name | park | weekly_goal | max_bags | +--------+------------+-------------------+-------------+----------+ | 1 | Gladys | Prospect Park | 3 | 5 | | 2 | Catherine | Central Park | 2 | 2 | | 3 | Georgeanna | Central Park | 2 | 1 | | 4 | Wanda | Van Cortland Park | 1 | 1 | | 5 | Ann | Prospect Park | 2 | 7 | | 6 | Juanita | Riverside Park | 1 | 4 | | 7 | Georgia | Prospect Park | 1 | 3 | +--------+------------+-------------------+-------------+----------+ 7 rows in set (0.00 sec)

      Notice how this result set’s columns are listed in the same order in which they were defined in the CREATE TABLE statement from the previous Connecting to MySQL and Setting up a Sample Database section. This is how most relational database systems will order columns in the result set when running a query that uses an asterisk in place of individual column names.

      Be aware that you can retrieve information from multiple tables in the same query with the JOIN keyword. We encourage you to follow our guide on How To Use Joins in SQL for details on how to do this.

      Removing Duplicate Values with DISTINCT

      By default, RDBMSs will return every value from a column returned by a query, including duplicate values.

      As an example, run the following query. This will return the values from the volunteers table’s park column:

      • SELECT park
      • FROM volunteers;

      Output

      +-------------------+ | park | +-------------------+ | Prospect Park | | Central Park | | Central Park | | Van Cortland Park | | Prospect Park | | Riverside Park | | Prospect Park | +-------------------+ 7 rows in set (0.00 sec)

      Notice how this result set includes two duplicated values: Prospect Park and Central Park. This makes sense, since multiple volunteers can clean up litter in the same park. There may be times, though, when you only want to know what unique values are held in a column. You can issue queries that remove duplicate values by following SELECT with the DISTINCT keyword.

      The following query will return every unique value in the parks column, removing any duplicates. It’s identical to the previous query except that it includes the DISTINCT keyword:

      • SELECT DISTINCT park
      • FROM volunteers;

      Output

      +-------------------+ | park | +-------------------+ | Prospect Park | | Central Park | | Van Cortland Park | | Riverside Park | +-------------------+ 4 rows in set (0.00 sec)

      This query’s result set has three fewer rows than that of the previous one, since it removed one of the Central Park values and two of the Prospect Park values.

      Note that SQL treats every row of a result set as an individual record, and DISTINCT will only eliminate duplicates if multiple rows share identical values in each column

      To illustrate this, issue the following query that includes the DISTINCT keyword but returns both the name and park columns:

      • SELECT DISTINCT name, park
      • FROM volunteers;

      Output

      +------------+-------------------+ | name | park | +------------+-------------------+ | Gladys | Prospect Park | | Catherine | Central Park | | Georgeanna | Central Park | | Wanda | Van Cortland Park | | Ann | Prospect Park | | Juanita | Riverside Park | | Georgia | Prospect Park | +------------+-------------------+ 7 rows in set (0.00 sec)

      The duplicate values in the park column — three occurrences of Prospect Park and two of Central Park — appear in this result set, even though the query included the DISTINCT keyword. Although individual columns in a result set may contain duplicate values, an entire row must be an exact duplicate of another for it to be removed by DISTINCT. In this case, every value in the name column is unique so DISTINCT doesn’t remove any rows when that column is specified in the SELECT clause.

      Filtering Data with WHERE clauses

      There may be times when you want to retrieve more granular information from tables in your database. You can filter out certain rows by including a WHERE clause in your query after the FROM clause, as in:

      • SELECT columns_to_return
      • FROM table_to_query
      • WHERE search_condition;

      Following the WHERE keyword in this example syntax is a search condition, which is what actually determines which rows get filtered out from the result set. A search condition is a set of one or more predicates, or expressions that can evaluate one or more value expressions. In SQL, a value expression — also sometimes referred to as a scalar expression — is any expression that will return a single value. A value expression can be a literal value (like a string or numeric value), a mathematical expression, or a column name.

      Predicates in a WHERE clause search condition can take many forms, but they typically follow this syntax:

      . . .
      WHERE value expression OPERATOR value_expression
      . . .
      

      After the WHERE keyword, you provide a value expression followed by one of several special SQL operators used to evaluate the column’s values against the value expression (or value expressions) that comes after the operator. There are several such operators available in SQL and this guide will briefly highlight a few of them later in this section, but for illustration purposes it will focus only on one of the most commonly used operators: the equals sign (=). This operator tests whether two value expressions are equivalent.

      Predicates always return a result of either “true,” “false,” or “unknown.” When running SQL queries that contain a WHERE clause, the DBMS will apply the search condition sequentially to every row in the table defined in the FROM clause. It will only return the rows for which every predicate in the search condition evaluates to “true.”

      To illustrate this idea, run the following SELECT statement. This query returns values from the volunteers table’s name column. Instead of evaluating values from one of the table’s columns, however, this WHERE clause tests whether two value expressions — (2 + 2) and 4 — are equivalent:

      • SELECT name
      • FROM volunteers
      • WHERE (2 + 2) = 4;

      Because (2 + 2) is always equal to 4, this search condition evaluates to “true” for every row in the table. Consequently, every row’s name value gets returned in the result set:

      Output

      +------------+ | name | +------------+ | Gladys | | Catherine | | Georgeanna | | Wanda | | Ann | | Juanita | | Georgia | +------------+ 7 rows in set (0.00 sec)

      Because this search condition always returns a result of “true,” it isn’t very useful. You may as well not include a WHERE clause at all, since SELECT name FROM volunteers; will produce the same result set.

      Rather than comparing two literal values like this, you’ll typically use a column name as one of the value expressions in a WHERE clause’s search condition. By doing so, you’re telling the database management system to use each row’s value from that column as a value expression for that row’s iteration of the search condition.

      The following query’s WHERE clause applies a more exclusive search condition to each row. It will return the name and max_bags values from any row whose max_bags value is equal to 4:

      • SELECT name, max_bags
      • FROM volunteers
      • WHERE max_bags = 4;

      Only one volunteer’s max_bags value is exactly equal to 4, so the query only returns that volunteer’s record:

      Output

      +---------+----------+ | name | max_bags | +---------+----------+ | Juanita | 4 | +---------+----------+ 1 row in set (0.00 sec)

      You can also evaluate character string values in search condition predicates. The following query returns the vol_id and name values of every row whose name value is equal to 'Wanda':

      • SELECT vol_id, name
      • FROM volunteers
      • WHERE name="Wanda";

      Because there’s only one volunteer named Wanda, the query only returns the information from that row:

      Output

      +--------+-------+ | vol_id | name | +--------+-------+ | 4 | Wanda | +--------+-------+ 1 row in set (0.00 sec)

      To reiterate, this section’s examples all use the same search condition operator — the equals sign — to filter data. However, there are a number of other types of operators that allow you to write a variety of predicates, offering a high level of control over what information your queries return.

      The SQL standard defines 18 different types of predicates, though not all of them are supported on every RDBMS. Here are five of the most commonly used search condition predicate types and the operators they use:

      Comparison: Comparison predicates compare one value expression with another; in queries, it’s almost always the case that at least one of these value expressions is the name of a column. The six comparison operators are:

      • =: tests whether the two values are equivalent
      • <>: tests whether two values are not equivalent
      • <: tests whether the first value is less than the second
      • >: tests whether the first value is greater than the second
      • <=: tests whether the first value is less than or equal to the second
      • >=: tests whether the first value is greater than or equal to the second

      Null: Predicates that use the IS NULL operator test whether values in a given column are Null
      Range: Range predicates use the BETWEEN operator to test whether one value expression falls between two others
      Membership: This type of predicate uses the IN operator to test whether a value is a member of a given set
      Pattern Match: Pattern matching predicates use the LIKE operator to test whether a value matches a string pattern containing wildcard values

      It’s beyond the scope of this tutorial to go into each of these predicate types in greater detail. If you’d like to learn more about them, though, we encourage you to check out the following guides:

      To learn more about WHERE clauses generally, please see our guide on How To Use WHERE Clauses in SQL.

      Sorting Query Results with ORDER BY

      Sometimes queries will return information in ways that may not be intuitive, or may not suit your particular needs. You can sort query results by appending an ORDER BY clause to the end of your query statement.

      Here’s the general syntax of a query with an ORDER BY clause:

      • SELECT columns_to_return
      • FROM table_to_query
      • ORDER BY column_name;

      To illustrate how this works, say you wanted to know which of your volunteers has the highest max_bags value. You could run the following query which returns the name and max_bags values from the volunteers table:

      • SELECT name, max_bags
      • FROM volunteers;

      However, this query sorts the result set in the order in which each row was added:

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Gladys | 5 | | Catherine | 2 | | Georgeanna | 1 | | Wanda | 1 | | Ann | 7 | | Juanita | 4 | | Georgia | 3 | +------------+----------+ 7 rows in set (0.00 sec)

      For a relatively small data set like this, the order of a result set isn’t that important and you could just scan this result set’s max_bags values to find the highest one. However, this can quickly become tedious when working with larger amounts of data.

      Instead, you could run the same query but add an ORDER BY clause that sorts the result set based each row’s max_bags value:

      • SELECT name, max_bags
      • FROM volunteers
      • ORDER BY max_bags;

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Georgeanna | 1 | | Wanda | 1 | | Catherine | 2 | | Georgia | 3 | | Juanita | 4 | | Gladys | 5 | | Ann | 7 | +------------+----------+ 7 rows in set (0.00 sec)

      As this output indicates, the default behavior of SQL queries that include an ORDER BY clause is to sort the specified column’s values in ascending (increasing) order. You can change this behavior and sort them in descending order by appending the DESC keyword to the ORDER BY clause:

      • SELECT name, max_bags
      • FROM volunteers
      • ORDER BY max_bags DESC;

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Ann | 7 | | Gladys | 5 | | Juanita | 4 | | Georgia | 3 | | Catherine | 2 | | Georgeanna | 1 | | Wanda | 1 | +------------+----------+ 7 rows in set (0.00 sec)

      Conclusion

      By reading this guide, you learned how to write basic queries, as well as filter and sort query result sets. 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 Use Migrations to Create and Manage Database Tables in Laravel



      Part of the Series:
      How To Build a Links Landing Page in PHP with Laravel and Docker Compose

      Laravel is an open-source PHP framework that provides a set of tools and resources to build modern PHP applications. In this project-based tutorial series, you’ll build a Links Landing Page application with the Laravel framework, using a containerized PHP development environment managed by Docker Compose.

      At the end, you’ll have a one-page website built with Laravel and managed via Artisan commands where you can share relevant links to an audience on social channels and presentations.

      Laravel database migrations allow developers to quickly bootstrap, destroy, and recreate an application’s database, without the need to log into the database console or run any SQL queries.

      In this guide, you’ll create a database migration to set up the table where you’ll save the application links. In order to do that, you’ll use the Artisan command-line tool that comes with Laravel by default. At the end, you will be able to destroy and recreate your database tables as many times as you want, using only artisan commands.

      To get started, first make sure you’re in the application’s root directory and your Docker Compose development environment is up and running:

      • cd ~/landing-laravel
      • docker-compose up -d

      Output

      landing-laravel_app_1 is up-to-date landing-laravel_nginx_1 is up-to-date landing-laravel_db_1 is up-to-date

      Next, create a database migration to set up the links table. Laravel Migrations allow developers to programmatically create, update, and destroy database tables, working as a version control system for your database schema.

      To create a new migration, you can run the make:migration Artisan command and that will bootstrap a new class on your Laravel application, in the database/migrations folder. This class will contain a default boilerplate code.

      Remember to use docker-compose exec app to run the command on the app service container, where PHP is installed:

      • docker-compose exec app php artisan make:migration create_links_table

      Output

      Created Migration: 2020_11_18_165241_create_links_table

      Notice that the migration name is generated based on the current date and time, and the name provided as argument to the make:migration command. For that reason, your migration file name will differ slightly.

      Open the generated migration class using your editor of choice:

      • nano database/migrations/2020_11_18_165241_create_links_table

      Next, update the up method to include the table columns you’ll need to store the app data.

      Replace the current content of your migration class with the following code. The highlighted values are the only lines that need adding, so if you prefer, you can also only copy those highlighted lines and include them into your Schema::create definition:

      database/migrations/2020_10_12_171200_create_links_table.php

      <?php
      
      use IlluminateDatabaseMigrationsMigration;
      use IlluminateDatabaseSchemaBlueprint;
      use IlluminateSupportFacadesSchema;
      
      class CreateLinksTable extends Migration
      {
          /**
           * Run the migrations.
           *
           * @return void
           */
          public function up()
          {
              Schema::create('links', function (Blueprint $table) {
                  $table->id();
                  $table->string('url', 200);
                  $table->text('description');
                  $table->boolean('enabled')->default(true);
                  $table->timestamps();
              });
          }
      
          /**
           * Reverse the migrations.
           *
           * @return void
           */
          public function down()
          {
              Schema::dropIfExists('links');
          }
      }
      

      In addition to the default fields that are included in the table definition that is automatically generated with the Artisan command, you’re including three new fields in this table:

      • url : A string field to save the link URL.
      • description : A text field to save the link description.
      • enabled : A field to store the state of the link, whether it’s enabled or not. The boolean Schema type will generate a tinyint unsigned field to store a value of either 0 of 1.

      Save your migration file when you’re done adding these fields. Next, run the migration with:

      • docker-compose exec app php artisan migrate

      Output

      Migration table created successfully. Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (152.46ms) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (131.12ms) Migrating: 2019_08_19_000000_create_failed_jobs_table Migrated: 2019_08_19_000000_create_failed_jobs_table (101.06ms) Migrating: 2020_11_18_165241_create_links_table Migrated: 2020_11_18_165241_create_links_table (60.20ms)

      You’ll notice that other migrations were also executed along with the create_links_table. That is because the default Laravel installation comes with migrations for users (with a users table and a password_resets table) and for queued jobs (with a failed_jobs table). Because our demo application won’t use these features, it is safe to remove those migrations now; however, you may also opt to leave them in place if you are working on an application of your own and you plan on developing it further. All migration files are located at database/migrations in the app’s root folder.

      For more detailed information on database migrations, please refer to our guide on How To Use Database Migrations and Seeders to Abstract Database Setup in Laravel.

      In the next part of this series, you’ll create a custom Artisan command to list, insert, and delete entries in the app’s links table.



      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