{"id":71096,"date":"2019-05-13T11:00:22","date_gmt":"2019-05-13T08:00:22","guid":{"rendered":"http:\/\/examples.javacodegeeks.com\/?p=71096"},"modified":"2021-11-07T23:29:37","modified_gmt":"2021-11-07T21:29:37","slug":"sql-data-types-tutorial","status":"publish","type":"post","link":"https:\/\/examples.javacodegeeks.com\/sql-data-types-tutorial\/","title":{"rendered":"SQL Data Types Tutorial"},"content":{"rendered":"

<\/a>1. Introduction<\/h2>\n

SQL<\/strong> is the language used to communicate with Relational Databases<\/strong> such as MySQL, Oracle, MS SQL Server, PostgreSQL, etc. In this post, we will examine the various SQL Data Types<\/strong> and provide examples for MySQL, which is the most widely used open-source database. This post requires some knowledge in SQL and a tutorial for a complete understanding of SQL is found here<\/a>.<\/p>\n

\n

Table Of Contents<\/h3>\n
\n
1. Introduction<\/a><\/dt>\n
2. Prerequisites<\/a><\/dt>\n
3. SQL Basics<\/a><\/dt>\n
4. SQL Data Types<\/a><\/dt>\n
\n
\n
4.1 Numeric<\/a><\/dt>\n
4.2 Character<\/a><\/dt>\n
4.3 Date and Time<\/a><\/dt>\n
4.4 JSON \/ XML<\/a><\/dt>\n<\/dl>\n<\/dd>\n
5. Best Practices<\/a><\/dt>\n
6. Conclusion<\/a><\/dt>\n
7. Download the SQL commands<\/a><\/dt>\n<\/dl>\n<\/div>\n

<\/a>2. Prerequisites<\/h2>\n

Install MySQL<\/h3>\n

To run the examples we will have to install the MySQL Community Server<\/strong> and use the MySQL Command-Line Client<\/strong> to interact with it. The MySQL version we will use is 5.7.26. From this<\/a> link, download the MySQL Community Server 5.7.26 for your operating system and follow the installation instructions. Once MySQL is installed, you should be prompted to change the root password. Do not forget that password as it will be the only way to login to MySQL. Finally, MySQL should automatically start as a service.<\/p>\n

Create Test Database<\/h3>\n

The MySQL Command-Line Client comes with the MySQL Community Server so you won’t have to install any additional application. To run the examples of this post, we will execute SQL commands through the MySQL Command-Line Client. Before running any example, we would have to login to MySQL and create a new test database and table, in which we will run all our examples. <\/p>\n

Login to MySQL<\/h4>\n

Open a terminal window and run the following command to login to MySQL:<\/p>\n

Login to MySQL<\/em><\/span><\/p>\n

$ mysql -u root -p\n<\/pre>\n
This login command uses the root<\/strong> user, which is the default root user for MySQL. After that, you will be prompted to enter the password you set when you installed MySQL. Upon successful login you will see the following welcome message:<\/p>\n
Output<\/em><\/span><\/p>\n
Welcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 4\nServer version: 5.7.26 MySQL Community Server (GPL)\n\nCopyright (c) 2000, 2019, Oracle and\/or its affiliates. All rights reserved.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n<\/pre>\n
Congratulations, you successfully logged in to MySQL and you can use the MySQL Command-Line Client to execute SQL commands. The next step would be to create a test database.<\/p>\n
Create a Test Database<\/h4>\n
The test database will be used to run the examples of this post. In the MySQL Command-Line Client run:<\/p>\n
Create new empty database<\/em><\/span><\/p>\n
mysql> create database test;\nQuery OK, 1 row affected (0.00 sec)\n<\/pre>\n
The above command will create a database named test<\/strong> and output that the operation was successful. To confirm that the database was created run:<\/p>\n
Show all databases<\/em><\/span><\/p>\n
mysql> show databases;\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\n| test               |\n+--------------------+\n5 rows in set (0.00 sec)\n<\/pre>\n
The show databases<\/strong> command shows all the databases created for MySQL. The output shows that our test<\/strong> database was successfully created. Finally, to use the test database we need to run the following command:<\/p>\n
Switch to test database<\/em><\/span><\/p>\n
mysql> use test;\nDatabase changed\n<\/pre>\n
We have now switched to the test database and we are ready to start running examples in it.<\/p>\n
<\/a>3. SQL Basics<\/h2>\n
Before we dive into the SQL Data Types let’s see some basics for SQL. SQL stands for Structured Query Language and allows you to do CRUD<\/strong> (Create, Read, Update, Delete) operations on database tables<\/strong>. A table is a collection of data and is made up of rows<\/strong> and columns<\/strong>. The rows are used to store data and the columns hold the data type. In this post, we will focus on the data types which are essentially the column part of a table. Each table must have a unique identifier for the rows, also called the primary key<\/strong>.<\/p>\n
Creating a table<\/h4>\n
Let’s now create the table that we will use in the examples below. The table simply represents the articles<\/em> of a blog. In the MySQL Command-Line Client run:<\/p>\n
Create a test table<\/em><\/span><\/p>\n
mysql> CREATE TABLE articles (\n    id INT AUTO_INCREMENT,\n    title VARCHAR(64) NOT NULL,\n    posted_by VARCHAR(64) NOT NULL,\n    posted_date DATE,\n    active BIT(1) NOT NULL,\n    last_update_date DATETIME,\n    rating_percentage DOUBLE,\n    views BIGINT,\n    no_of_comments SMALLINT,\n    category ENUM('JAVA', 'QUARTZ'),\n    body TEXT,\n    metadata JSON,\n    PRIMARY KEY (id)\n);\n<\/pre>\n
In the table above, the id<\/strong> is the primary key which auto increments during an insert. We will examine the rest of the columns in the following section.<\/p>\n
View the table structure<\/h4>\n
To view the structure of the articles<\/em> table we just created, run the following command:<\/p>\n
Show articles table structure<\/em><\/span><\/p>\n
mysql> describe articles;\n+-------------------+-----------------------+------+-----+---------+----------------+\n| Field             | Type                  | Null | Key | Default | Extra          |\n+-------------------+-----------------------+------+-----+---------+----------------+\n| id                | int(11)               | NO   | PRI | NULL    | auto_increment |\n| title             | varchar(64)           | NO   |     | NULL    |                |\n| posted_by         | varchar(64)           | NO   |     | NULL    |                |\n| posted_date       | date                  | YES  |     | NULL    |                |\n| active            | bit(1)                | NO   |     | NULL    |                |\n| last_update_date  | datetime              | YES  |     | NULL    |                |\n| rating_percentage | double                | YES  |     | NULL    |                |\n| views             | bigint(20)            | YES  |     | NULL    |                |\n| no_of_comments    | smallint(6)           | YES  |     | NULL    |                |\n| category          | enum('JAVA','QUARTZ') | YES  |     | NULL    |                |\n| body              | text                  | YES  |     | NULL    |                |\n| metadata          | json                  | YES  |     | NULL    |                |\n+-------------------+-----------------------+------+-----+---------+----------------+\n<\/pre>\n
This command is very useful when we want to view the data type for a table.
 
<\/div> <\/div><\/p>\n
Inserting test data into table<\/h4>\n
Finally, we have to insert some articles into the table we created in the previous section. Open the MySQL Command-Line Client and run:<\/p>\n
Insert data into articles table<\/em><\/span><\/p>\n
mysql> insert into articles(title, posted_by, posted_date, active, last_update_date, rating_percentage, views, no_of_comments, category, body, metadata) values \n('Java Microservices', 'bob', '2019-04-01', 1, '2019-03-29 05:10:23', 85.23, 453, 5, 'JAVA', 'blah', '{\"total_author_posts\": 15, \"tags\": [\"Java\", \"Microservices\"]}'),\n('Quartz Best Practices', 'tom', '2018-11-05', 1, '2018-11-04 15:43:00', 76.3, 7834, 28, 'QUARTZ', 'blah', '{\"total_author_posts\": 4, \"tags\": [\"Quartz\"]}'),\n('Java HashMap', 'tom', '2015-04-24', 0, '2015-04-20', 34, 6543, 2, 'JAVA', 'blah', '{\"tags\": [\"Java\"]}');\n<\/pre>\n
We added three rows to the test table. Notice that we didn’t add any value in the id<\/strong> column, as it auto increments as we said before. To confirm that run:<\/p>\n
View articles data<\/em><\/span><\/p>\n
mysql> select id, title from articles;\n+----+-----------------------+\n| id | title                 |\n+----+-----------------------+\n|  1 | Java Microservices    |\n|  2 | Quartz Best Practices |\n|  3 | Java HashMap          |\n+----+-----------------------+\n3 rows in set (0.00 sec)\n<\/pre>\n
That returns 3 rows that have an auto-incremented id. We have finished creating a test table and insert some test data into it. In the following section, we will see the SQL Data Types and use the table we created in our examples.<\/p>\n
<\/a>4. SQL Data Types<\/h2>\n
\n
\"SQL
SQL Data Types<\/figcaption><\/figure>\n<\/div>\n
Like any programming language, SQL also has many data types. If we could divide the SQL data types into categories then those would be:<\/p>\n