One place for hosting & domains


      How To Create a Laravel Contact Form and Send Emails with SendGrid

      The author selected the Open Source Initiative to receive a donation as part of the Write for DOnations program.


      Laravel is a free, open source PHP framework, based on Symfony and used for creating web applications. SendGrid is a cloud-based SMTP provider that allows you to send email without having to maintain email servers.

      Having a contact form on your website makes it easier for your visitors to contact you directly. For your contact form to work correctly and send out emails, you need an SMTP server. This tutorial will use SendGrid and their free SMTP service to deliver the emails sent out from the website contact form to an email inbox.

      In this tutorial, you’ll add a contact form to an existing Laravel application and configure it to send emails via SMTP using SendGrid.


      If you don’t already have a Laravel application set up, you will need the following:

      After you have set up your Laravel application, you’ll also need the following:

      • A SendGrid account. You can visit the SendGrid registration page to sign up for a free SendGrid account.
      • A fully registered domain name pointed to your server. This tutorial will use your_domain throughout. You can purchase a domain name on Namecheap, get one for free on Freenom, or use the domain registrar of your choice. For DigitalOcean, you can follow this introduction to DigitalOcean DNS for details on how to add them.

      Step 1 — Creating the Sender Identity

      SendGrid requires you to verify the ownership of your domain name before allowing you to start sending emails. In order to verify your domain name, go to your SendGrid account, then go to the Dashboard and click on Authenticate your Domain.

      This will take you to a page where you will need to specify your DNS host and choose if you would like to brand the links for your domain. Email link branding allows you to set all of the links used for click-tracking in your emails to your domain instead of from

      SendGrid choose DNS host

      Then click Next and on the next page, specify your domain name.

      SendGrid specify your domain name

      Finally, you will need to add the DNS records provided by SendGrid to complete their verification process. For more information on how to manage your DNS records, you can read this tutorial on How to Create, Edit, and Delete DNS Records.

      SendGrid verify DNS records

      Once you have added the DNS records to your DNS zone, go back to SendGrid and hit the Verify button.

      With your SendGrid Identity verified, you need to generate an API key, which you will use in your Laravel .env file.

      From the menu on the left, click on API Keys and then click on the Create API Key button. For security, set the API Key Permissions to Restricted Access.

      SendGrid Create API Key

      After that, scroll down and add the Mail Send permissions.

      SendGrid send mail permissions

      Finally, click on the Create & View button to get your API key. The API key will only be visible once, so be sure to take note of it in a secure place.

      Now that you’ve configured your domain with SendGrid and generated your API key, you’ll configure the SMTP details for your Laravel application.

      Step 2 — Configuring the SMTP Details

      The .env file in Laravel is used to store various configuration options for your application environment. Since there is usually some sensitive information in the .env file, like your database connection details, you should not commit the .env file to source control.

      If you completed the prerequisite tutorial, you’ll need to be in the /var/www/travellist directory to access your .env file:

      After that, use your favorite text editor open the .env file:

      There are many configuration variables in the .env file—in this tutorial you’ll only change the MAIL variables.

      To do so, find the MAIL_ settings and configure the variables as following, adding in your copied API key to sendgrid_api_key and updating the other highlighted fields as necessary:


      . . .
      . . .

      The following list contains an overview of the variables that have to be updated in order for your Laravel application to start using the SendGrid SMTP server:

      • MAIL_HOST: The SendGrid SMTP hostname, which will be used for sending out emails.
      • MAIL_PORT: The SendGrid secure TLS SMTP port.
      • MAIL_USERNAME: Your SendGrid username. By default, it is apikey for all accounts.
      • MAIL_PASSWORD: Your SendGrid API Key.
      • MAIL_ENCRYPTION: The mail encryption protocol. In this case you will use TLS as it secures the email content during the transfer between the servers.

      Save and exit the file.

      With your SMTP settings in place, you are ready to proceed and configure your contact controller.

      Step 3 — Creating the Controller

      Next you’ll create a controller that will handle your POST and GET requests for your contact form page.

      You’ll use the GET route to return the HTML page containing your contact form, and the POST route will handle the contact form submissions.

      In order to create a controller called ContactController in Laravel, use the following artisan command:

      • php artisan make:controller ContactController

      After running the command, you will get the following output:


      Controller created successfully.

      This command will create a new controller at app/Http/Controllers/ContactController.php.

      Run the following to edit the ContactController.php file:

      • nano app/Http/Controllers/ContactController.php

      First, you’ll include the Laravel Mail facade so that you can use the mail functionality in your new controller. A facade in Laravel is a class that provides access to different Laravel features. For more information about Laravel facades, take a look at the official Laravel Facades documentation.

      To include the Laravel Mail facade add the following:


      namespace AppHttpControllers;
      use IlluminateHttpRequest;
      use Mail;
      . . .

      Then add the method that will handle your GET requests and return the contact page view:


      . . .
      class ContactController extends Controller
              public function contact(){
                      return view('contact');

      Finally, let’s add a method that will handle the POST requests and send out the emails:


      class ContactController extends Controller
              public function contact(){
                      return view('contact');
          public function contactPost(Request $request){
              $this->validate($request, [
                              'name' => 'required',
                              'email' => 'required|email',
                              'comment' => 'required'
              Mail::send('email', [
                      'name' => $request->get('name'),
                      'email' => $request->get('email'),
                      'comment' => $request->get('comment') ],
                      function ($message) {
                              $message->to('youremail@your_domain', 'Your Name')
                              ->subject('Your Website Contact Form');
              return back()->with('success', 'Thanks for contacting me, I will get back to you soon!');

      Within the highlighted lines, you’ll need to change some of the variables, like so:

      • $message->from('youremail@your_domain');: Change the youremail@your_domain with your actual email address.

      • $message->to('youremail@your_domain', 'Your Name'): The $message->to and the $message->from do not necessarily need to match. You can also change the $message->to value with another email address to which you would like to receive all of your contact form inquiries.

      • subject('Your Website Contact Form');: You can also change the email subject by editing the message inside the subject method.

      Note: the $message->from('youremail@your_domain'); address needs to match the domain name that you used with SendGrid.

      Once you’ve finished these edits, the following will be your full ContactController.php file:


      namespace AppHttpControllers;
      use IlluminateHttpRequest;
      use Mail;
      class ContactController extends Controller
          public function contact(){
              return view('contact');
          public function contactPost(Request $request){
              $this->validate($request, [
                              'name' => 'required',
                              'email' => 'required|email',
                              'comment' => 'required'
              Mail::send('email', [
                      'name' => $request->get('name'),
                      'email' => $request->get('email'),
                      'comment' => $request->get('comment') ],
                      function ($message) {
                              $message->to('youremail@your_domain', 'Your Name')
                                      ->subject('Your Website Contact Form');
              return back()->with('success', 'Thanks for contacting me, I will get back to you soon!');

      Save and exit your file once you’ve finished your edits.

      Your Contact Controller has two methods:

      • contact(): This method returns your contact Blade view template, which will hold your HTML page that has the HTML layout for your contact form. Blade is the templating engine that comes with Laravel. In your Blade template views, you can add your HTML structure along with PHP code and Blade syntax.
      • contactPost(): This method handles all of the contact form submissions—where you handle the input validation and send out the emails.

      You handle the validation inside the contactPost() method with the $this->validate() method. Inside the validation method, you specify that the name, email, and comment are required. That way, your users will not be able to submit empty or incomplete contact form inquiries. For more information on how the Laravel validation works, take a look at the official Laravel Validation documentation.

      When validation is successful, the Mail::send() method constructs your email body and subject and then sends out the email.

      Finally, if the email was sent successfully, you return a success message that displays to your users.

      You’ve set up your contact controller and can now move on to GET and POST routes.

      Step 4 — Creating the Routes

      Laravel routes allow you to create SEO-friendly URLs for your application. By using Laravel routes, you can route your application requests to specific controllers, where you handle your application logic.

      You’ll create two routes in your routes/web.php file to use the methods you set up in the previous step.

      You will first create a GET route that maps to your contact method in your ContactController. This method only returns your contact Blade view. Open routes/web.php with the following command:

      Add the GET route at the bottom of the file:

      Note: If you followed the prerequisites, you’ll have different content in you routes/web.php file. You can add your routes to the end of this file as per the instructions in this tutorial.


      use IlluminateSupportFacadesRoute;
      | Web Routes
      | Here is where you can register web routes for your application. These
      | routes are loaded by the RouteServiceProvider within a group which
      | contains the "web" middleware group. Now create something great!
      Route::get('/contact', 'ContactController@contact')->name('contact');

      You’ll now add a POST route and map it to your contactPost method, which will handle your user contact form submissions:


      use IlluminateSupportFacadesRoute;
      | Web Routes
      | Here is where you can register web routes for your application. These
      | routes are loaded by the RouteServiceProvider within a group which
      | contains the "web" middleware group. Now create something great!
      Route::get('/contact', 'ContactController@contact')->name('contact');
      Route::post('/contact', 'ContactController@contactPost')->name('contactPost');

      Once you have your Controller and Route ready, you can save and exit your file then proceed to the next step where you will prepare your Blade views.

      Step 5 — Creating the Blade Views

      In this step you will start by creating a view in the application that will hold your HTML contact form. It will have three input fields:

      • Input field with type text for the email address of the user
      • Input field with type text for the name of the user
      • Text area for the comment

      Create a file called resources/views/contact.blade.php:

      • nano resources/views/contact.blade.php

      Then add the following content:


      <!DOCTYPE html>
      <html lang="en">
          <meta charset="UTF-8">
          <meta name="viewport" content="width=device-width, initial-scale=1.0">
          <title>Contact Form with Laravel and SendGrid</title>
          <link rel="stylesheet" href=""
              integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
          <div class="container">
              <div class="alert alert-success">
                {{ session('success') }}
              <form method="POST" action="/contact">
                  <div class="form-group {{ $errors->has('name') ? 'has-error' : '' }}">
                      <label for="email">Email address</label>
                      <input name="email" type="email" class="form-control" id="email" aria-describedby="emailHelp"
                          placeholder="Enter your email">
                      <span class="text-danger">{{ $errors->first('email') }}</span>
                  <div class="form-group {{ $errors->has('name') ? 'has-error' : '' }}">
                      <label for="name">Name</label>
                      <input name="name" type="text" class="form-control" id="name" aria-describedby="name" placeholder="Your name">
                      <span class="text-danger">{{ $errors->first('name') }}</span>
                  <div class="form-group {{ $errors->has('name') ? 'has-error' : '' }}">
                      <label for="exampleInputPassword1">Comment</label>
                      <textarea name="comment" class="form-control" id="exampleFormControlTextarea1" rows="3"></textarea>
                      <span class="text-danger">{{ $errors->first('comment') }}</span>
                  <button type="submit" class="btn btn-primary">Submit</button>

      This is an HTML form with a POST method to the /contact route. When someone fills out the contact form, it’ll be handled by your contactPost method.

      The <link> tag inside the <head> tag is used to include Bootstrap. You’re using some styling for the HTML form. You can change the style of the form so that it matches the design of your website. For more information on how to style your website, you can take a look at our CSS resources page.

      The form is wrapped up in different <div> tags with classes from Bootstrap. You’re using the <div> tags to create the structure of the contact form. For more information on how the <div> tags work, check out the How To Use a <div>, the HTML Content Division Element tutorial.

      Save and exit this file.

      The next view that you’ll create is your email view.

      Open the resources/views/email.blade.php file:

      • nano resources/views/email.blade.php

      Then add the following content:


      Inquiry from: {{ $name }}
      <p> Email: {{ $email }} </p>
      <p> Message: {{ $comment }} </p>

      This contains the email content that will be sent to users that complete your contact form. Save and exit the file.

      With the styling and views complete, you’re ready to go ahead and test the contact form.

      To test the contact form, visit http://your_domain/contact via your browser.

      You’ll see the Bootstrap HTML form that you created in the previous step.

      Complete all of the required fields and hit the Submit button. You will receive a green notification that the message was sent successfully.

      Laravel contact form message

      You can test the form by submitting it without filling any of the fields. The validation that you added in your controller will catch that and it’ll inform you that the fields must not be empty.

      Laravel contact form validation

      Finally, you can check your email account and make sure that you’ve received the test email and you can see it in your inbox.


      You have now successfully added a contact form to your existing Laravel website.

      You can also find more information in the official Laravel documentation.

      To make sure that your contact form is secure, you can install an SSL certificate for your website by following our guide on How To Secure Nginx with Let’s Encrypt.

      To learn more about Laravel, check out our collection of Laravel resources.

      Source link

      How To Create a calibre Ebook Server on Ubuntu 20.04

      The author selected the COVID-19 Relief Fund to receive a donation as part of the Write for DOnations program.


      calibre is a free and open source ebook manager that’s well known for its cross-platform desktop application. You can use calibre to manage your ebook library on a single device, but the application also includes a powerful server component. Setting up an ebook server allows you to:

      • Access your books from anywhere in the world
      • Easily transfer your books to mobile devices
      • Share books with your friends and family

      The calibre Content server includes a backend for serving your books and a basic front end for searching through your library, downloading individual titles, or even reading your books directly through a web browser. The server also offers a basic mobile interface that works with a wide variety of devices, including the basic browsers that ship with many e-ink readers such as Kindle and Kobo.

      In this tutorial, you’ll set up and use the calibre Content server on Ubuntu 20.04. Specifically, you will download and install the calibre server, configure calibre as a service so that it starts automatically when your server reboots, and add books to your library using command line tools. You will also encounter options to set up a cron job to automatically add new books to your library from a specific directory, add authentication to your server so that only you can access it, and add a free SSL/TLS certificate to serve your library over HTTPS for extra security.

      Warning: As with all creative content, it is important to respect the copyright licenses associated with materials created by others. This tutorial uses public domain books legally downloaded from Project Gutenberg, which holds over 60,000 free ebooks.


      • A server running Ubuntu 20.04 with a sudo user and a firewall. You can follow our Initial Server Setup Guide for instructions.
      • Step 7 includes the option to secure your calibre Content server with an SSL certificate. If you wish to do so, you will require a domain name with an available A record. If you are using a DigitalOcean Droplet, you can follow our guide on [How To Point to DigitalOcean Nameservers From Common Domain Registrars]( for instructions.

      Step 1 — Downloading and Installing the calibre Content Server

      Although calibre exists in Ubuntu’s software repositories, the version there often lags behind the latest release. Therefore, the official calibre documentation recommends that you install it from a binary hosted on their site instead.

      First, install some necessary dependencies:

      • sudo apt update && sudo apt install -y libfontconfig libgl1-mesa-glx

      Now download and install the calibre server.

      • wget

      Inspect the contents of the script:

      You can scroll up and down with the k and j keys and hit q to get back to your terminal when you are done.

      Now execute the script to install calibre:

      • sudo sh

      calibre expects a desktop environment but it will not find one on a headless server, so you will see some warnings about desktop integration failing. It is safe to ignore these because we will control calibre entirely via its command line tools and web interface.

      Now that we have installed calibre, we can begin to explore its functionality.

      Step 2 — Creating a Library and Adding Your First Book

      To explore calibre’s functionality, let’s download an .epub or .mobi ebook. We’ll use A Christmas Carol by Charles Dickens from Project Gutenberg as an example. Project Gutenberg maintains a massive repository of free, public domain literature and is a great resource for ebooks.

      Run the following command to download this book to your server:

      • wget -O

      Now create a directory that calibre can use as your ebook library:

      And add the book you just downloaded to your new library using the calibredb command:

      • calibredb add *.mobi --with-library calibre-library/

      You will see the following output:


      Added book ids: 1

      With a book added to your library, you can now start calibre and explore the application.

      Step 3 — Running the calibre Content Server and Viewing Your Library

      With calibre installed and a book downloaded, we are ready to explore the application’s user interface. But before we access the calibre Content server in a web browser, we need to make sure that our server can accept traffic on port 8080, which is the default port for calibre. If you followed the initial server setup guide in the prerequisites section, then you enabled ufw, or Uncomplicated Firewall. You now need to allow port 8080 through the firewall.

      Use the following command to open port 8080:

      Check the status of ufw to make sure the port is open:

      You will see an output like this:


      Status: active To Action From -- ------ ---- OpenSSH ALLOW Anywhere 8080 ALLOW Anywhere OpenSSH (v6) ALLOW Anywhere (v6) 8080 (v6) ALLOW Anywhere (v6)

      Now run the following command to start the calibre content server:

      • calibre-server calibre-library

      calibre-server is the command used to start our server and calibre-library is the name of the directory we created earlier and told calibre to use as our library.

      You will see an output like this:

      calibre server listening on
      OPDS feeds advertised via BonJour at: your_server_ip port: 8080

      From your local machine, visit your_server_ip:8080 (substituting your server’s IP address) and you will see the default calibre screen. Click on calibre-library and you will see the book that you added in the previous step.

      calibre server menu

      Hit Ctrl+C to stop the manual server process that you started.

      This method of running the calibre Content server works well, but you probably want your library to work all of the time, even after you close the SSH connection to your server. To make sure it always runs, even after rebooting your server, let’s turn the calibre Content server into a service.

      Step 4 — Creating a Service for the calibre Content Server

      To improve the usability of the calibre Content server, let’s replace our manual server process with a service that will start on boot.

      First, create a file called calibre-server.service in the directory /etc/sytemd/system/:

      • sudo nano /etc/systemd/system/calibre-server.service

      Now add the following configurations, which will start the calibre Content server on boot. Make sure to replace the highlighted text with your user and group:


      ## startup service
      Description=calibre content server
      ExecStart=/opt/calibre/calibre-server /home/sammy/calibre-library --enable-local-write

      Here we tell our service to use the --enable-local-write flag when starting the server. When the server is running, you can’t use the calibredb command to add books as we did for A Christmas Carol directly. Instead, you have to do this “through” the running server, as we’ll see in a bit, and this means that the server needs permission to write new files to disk. This flag allows it to do so as long as it receives the request locally.

      Save and close the file.

      Now enable the service and start it:

      • sudo systemctl enable calibre-server
      • sudo systemctl start calibre-server

      Reboot your server:

      Wait a few minutes and then visit http://your_server_ip:8080 again in your local web browser to ensure that the calibre Content server booted automatically.

      Now let’s add some authentication to our application.

      Step 5 — (Optional) Adding User Authentication to the calibre Content Server

      You now have a fully functioning calibre Content server that you can access from any device. Currently, however, anyone who knows your server’s IP address can access your ebooks. You might not want this. Instead, let’s configure calibre’s built-in user management system so you can force visitors to authenticate with a username and password.

      First, SSH back into your server and stop calibre. This will allow us to manipulate calibre’s database directly:

      • sudo systemctl stop calibre-server

      Now start calibre’s user management script:

      • calibre-server --manage-users

      When prompted, choose to add a new user. Then select a username and strong password. You will see a final output message like this:


      User mycalibreuser added successfully!

      Now we need to make one small edit to our service.

      Reopen calibre-server.service:

      • sudo nano /etc/systemd/system/calibre-server.service

      To enable authentication, add the --enable-auth flag to the end of the line starting ExecStart. It should look like this:

      . . .
      ExecStart=/opt/calibre/calibre-server /home/sammy/calibre-library --enable-local-write --enable-auth
      . . .

      Save and close the file.

      Refresh the services daemon to rescan the services files, and start the calibre server again with:

      • sudo systemctl daemon-reload
      • sudo systemctl start calibre-server

      If you visit your library again, it should now prompt you for a username and password before allowing you to access it.

      There are more ways to add functionality to our calibre Content server. In the next step, we will add a cron job to automatically add downloaded books to our calibre library.

      Step 6 — (Optional) Automatically Adding Books to Your calibre Library

      It can be useful to set up a cron job that watches a specific directory and adds any books it finds to your calibre library. This way, you can download or upload books to this folder and they’ll automatically become available via the calibre web interface.

      Create a folder called books-to-add in your home directory and navigate inside:

      • mkdir ~/books-to-add
      • cd ~/books-to-add

      Download a new book into this directory. Use the following command to download Alice in Wonderland from Project Gutenberg:

      • wget -o alice.epub

      Now open your crontab:

      Here we will set up a script to add all files in this directory to calibre and then delete them (adding books to calibre creates a copy of the files in your library directory, so we can remove the originals once they are added.)

      Add the following content:


      */5 * * * * calibredb add /home/sammy/books-to-add/ -r --with-library http://localhost:8080#calibre-library --username mycalibreuser --password StrongPassword! && rm -r /home/sammy/books-to-add/*

      Save and close the file.

      This will run every 5 minutes, so you shouldn’t have to wait long for your new book to show up in the web interface. Wait a few minutes and then reload the library in your local web browser. Your new book will appear next to A Christmas Carol.

      Step 7 — (Optional) Installing Apache2 and Serving Your Library over HTTPS

      You need a username and password to access your library, but it’s not really secure because you are serving it over HTTP and sending your username and password unencrypted every time you authenticate. A more secure option is to ensure all traffic gets encrypted using HTTPS. If you own a domain name you can point this to your server and install a free SSL certificate using Let’s Encrypt. (You can also use a subdomain like ebooks.your_domain.)

      Installing and Configuring Apache2

      We’ll use the webserver Apache2 as a reverse proxy for calibre. Previously we had to append the port number :8080 when we visited our library because that’s the default port that calibre runs on. We’ll now set up Apache2 to listen to requests on port 80 (the default port for HTTP traffic), proxy requests to the locally running calibre server on :8080, and serve these to the end user transparently so that they won’t need to worry about specifying the port number. We will then secure all our traffic on port 443 with an SSL certificate.

      Install Apache2 and enable the proxy modules we need with the following commands:

      • sudo apt install -y apache2
      • sudo a2enmod proxy proxy_http

      Now make sure that your server allows traffic on ports 80 and 443. Close port 8080, too:

      • sudo ufw allow 'Apache Full'
      • sudo ufw delete allow 8080

      Next, create and open a file at /etc/apache2/sites-available/your_domain.conf:

      • sudo nano /etc/apache2/sites-available/your_domain.conf

      Add the following configurations, which will link your domain to the calibre server:


      LoadModule proxy_module modules/
      LoadModule proxy_http_module modules/
      <VirtualHost *:80>
          ServerName your_domain
          AllowEncodedSlashes On
          ProxyPreserveHost On
          ProxyPass "/"  "http://localhost:8080/"

      Finally, enable this new site configuration:

      • sudo a2ensite your_domain.conf
      • systemctl reload apache2

      Visit http://your_domain in a browser to verify that the calibre Content server loads. Apache is now serving your site.

      Installing Certbot and Configuring an SSL Certificate

      As a final step, let’s secure all our traffic using Certbot.

      First, install Certbot:

      • sudo snap install --classic certbot

      Now run Certbot:

      The Certbot program will take you through a series of prompts to install a certificate.

      • Enter your email address when prompted
      • Enter A to agree to the terms and conditions when prompted
      • Choose Y or N when prompted to share your email address with the EFF
      • Choose 1 when prompted about which domain you want the certificate for (there should only be one)

      Upon completion, you will see a congratulations message.

      You can now access your library securely at https://yourdomain.


      In this tutorial, you set up a calibre ebook server. You turned it into a service so that it would start when your server boots, added a cron job to automatically find and add new books to your library, and set up authentication and an SSL certificate to secure it.

      To extend the project, you can add more books from your personal library or from Project Gutenberg, Standard Ebooks, or elsewhere. Remember to always respect any copyright laws associated with your content library.

      Source link

      How To Create and Manage Tables in SQL


      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.


      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:


      Query OK, 1 row affected (0.01 sec)

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


      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
      • );


      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
      • );


      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)
      • );


      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:

      • name varchar(30),
      • squareFootage int,
      • designer varchar(30)
      • );


      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:


      | 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;


      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);


      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.


      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