One place for hosting & domains

      Tables

      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

      How To Create and Customize Tables in HTML



      Part of the Series:
      How To Build a Website With HTML

      This tutorial series will guide you through creating and further customizing this website using HTML, the standard markup language used to display documents in a web browser. No prior coding experience is necessary but we recommend you start at the beginning of the series if you wish to recreate the demonstration website.

      At the end of this series, you should have a website ready to deploy to the cloud and a basic familiarity with HTML. Knowing how to write HTML will provide a strong foundation for learning additional front-end web development skills, such as CSS and JavaScript.

      HTML tables are useful for arranging content in rows and columns. This tutorial will teach you how to add a table to your webpage and how customize it by adding a desired amount of rows and columns.

      A <table> element requires the use of one or more table row <tr> elements, which create table rows, and one or more table data <td> elements, which insert content into table columns. Each of these elements require an opening and closing tag. The table data <td> elements go inside the table row <tr> elements, and the table row <tr> elements go inside the <table> elements. For example, here is a table that has two rows and three columns:

      <table>
      <tr> 
      <td> Column 1 </td>
      <td> Column 2 </td>
      <td> Column 3 </td>
      </tr>
      <tr> 
      <td> Column 1 </td>
      <td> Column 2 </td>
      <td> Column 3 </td>
      </tr>
      <table>
      

      Clear your index.html file and paste the code snippet above into the document. (If you have not been following the tutorial series, you can review instructions for setting up an index.html file in our tutorial Setting Up Your HTML Project.)

      Save and reload the file in the browser to check your results. (For instructions on loading the file in your browser, please visit this step of our tutorial on HTML Elements.)

      Your webpage should now have a table with three columns and two rows:

      3 columns, 2 rows table

      To add an additional row, add another <tr> element like so:

      <table>
      <tr> 
      <td> Column 1 </td>
      <td> Column 2 </td>
      <td> Column 3 </td>
      </tr>
      <tr> 
      <td> Column 1 </td>
      <td> Column 2 </td>
      <td> Column 3 </td>
      </tr>
      <tr> 
      <td> Column 1 </td>
      <td> Column 2 </td>
      <td> Column 3 </td>
      </tr>
      <table> 
      

      Save your results and check them in your browser. You should receive something like this:

      3 Columns and 3 Rows Table

      To add another column, try adding another table data <td> element inside each of the table row <tr> elements like so:

      <table>
      <tr> 
      <td> Column 1 </td>
      <td> Column 2 </td>
      <td> Column 3 </td>
      <td> Column 4 </td>
      </tr>
      <tr> 
      <td> Column 1 </td>
      <td> Column 2 </td>
      <td> Column 3 </td>
      <td> Column 4 </td>
      </tr>
      <tr> 
      <td> Column 1 </td>
      <td> Column 2 </td>
      <td> Column 3 </td>
      <td> Column 4 </td>
      </tr>
      <table>
      

      Save your results and check them in your browser. You should receive something like this:

      4 Columns, 3 Rows Table

      Tables can be styled by adding attributes to the <table> element. For example, you can add a border to the table with the border attribute like so:

      <table border=”1”>
      <tr> 
      <td> Row 1 </td>
      <td> Row 2 </td>
      <td> Row 3 </td>
      </tr>
      <tr> 
      <td> Row 1 </td>
      <td> Row 2 </td>
      <td> Row 3 </td>
      </tr>
      <table>
      

      Try adding the border attribute to your table and checking your results in the browser. (You can clear your index.html file and paste in the HTML code snippet above.) Save your file and load it in the browser. You should receive something like this:

      Table with Border

      You should now understand how to add tables to your webpage and how to customize the amount of rows and columns a table contains. Tables can also be used to arrange content on your web page. There are also many other attributes you can use to style your table. We’ll return to tables as a layout tool and explore their styling possibilities later on in this tutorial series when we begin building our website.



      Source link

      How To Fix Corrupted Tables in MySQL



      Part of the Series:
      How To Troubleshoot Issues in MySQL

      This guide is intended to serve as a troubleshooting resource and starting point as you diagnose your MySQL setup. We’ll go over some of the issues that many MySQL users encounter and provide guidance for troubleshooting specific problems. We will also include links to DigitalOcean tutorials and the official MySQL documentation that may be useful in certain cases.

      Occasionally, MySQL tables can become corrupted, meaning that an error has occurred and the data held within them is unreadable. Attempts to read from a corrupted table will usually lead to the server crashing.

      Some common causes of corrupted tables are:

      • The MySQL server stops in middle of a write.
      • An external program modifies a table that’s simultaneously being modified by the server.
      • The machine is shut down unexpectedly.
      • The computer hardware fails.
      • There’s a software bug somewhere in the MySQL code.

      If you suspect that one of your tables has been corrupted, you should make a backup of your data directory before troubleshooting or attempting to fix the table. This will help to minimize the risk of data loss.

      First, stop the MySQL service:

      • sudo systemctl stop mysql

      Then copy all of your data into a new backup directory. On Ubuntu systems, the default data directory is /var/lib/mysql/:

      • cp -r /var/lib/mysql /var/lib/mysql_bkp

      After making the backup, you’re ready to begin investigating whther the table is in fact corrupted. If the table uses the MyISAM storage engine, you can check whether it’s corrupted by running a CHECK TABLE statement from the MySQL prompt:

      A message will appear in this statement's output letting you know whether or not it's corrupted. If the MyISAM table is indeed corrupted, it can usually be repaired by issuing a REPAIR TABLE statement:

      Assuming the repair was successful, you will see a message like the following in your output:

      Output

      +--------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------+--------+----------+----------+ | database_name.table_name | repair | status | OK | +--------------------------+--------+----------+----------+

      If the table is still corrupted, though, the MySQL documentation suggests a few alternative methods for repairing corrupted tables.

      On the other hand, if the corrupted table uses the InnoDB storage engine, then the process for repairing it will be different. InnoDB is the default storage engine in MySQL as of version 5.5, and it features automated corruption checking and repair operations. InnoDB checks for corrupted pages by performing checksums on every page it reads, and if it finds a checksum discrepancy it will automatically stop the MySQL server.

      There is rarely a need to repair InnoDB tables, as InnoDB features a crash recovery mechanism that can resolve most issues when the server is restarted. However, if you do encounter a situation where you need to rebuild a corrupted InnoDB table, the MySQL documentation recommends using the "Dump and Reload" method. This involves regaining access to the corrupted table, using the mysqldump utility to create a logical backup of the table, which will retain the table structure and the data within it, and then reloading the table back into the database.

      With that in mind, try restarting the MySQL service to see if doing so will allow you access to the server:

      • sudo systemctl restart mysql

      If the server remains crashed or otherwise inaccessible, then it may be helpful to enable InnoDB's force_recovery option. You can do this by editing the mysqld.cnf file:

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

      In the [mysqld] section, add the following line:

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

      . . .
      [mysqld]
      . . .
      innodb_force_recovery=1
      

      Save and close the file, and then try restarting the MySQL service again. If you can successfully access the corrupted table, use the mysqldump utility to dump your table data to a new file. You can name this file whatever you like, but here we'll name it out.sql:

      • mysqldump database_name table_name > out.sql

      Then drop the table from the database. To avoid having to reopen the MySQL prompt, you can use the following syntax:

      • mysql -u user -p --execute="DROP TABLE database_name.table_name"

      Following this, restore the table with the dump file you just created:

      • mysql -u user -p < out.sql

      Note that the InnoDB storage engine is generally more fault-tolerant than the older MyISAM engine. Tables using InnoDB can still be corrupted, but because of its auto-recovery features the risk of table corruption and crashes is decidedly lower.



      Source link