Introduction to SQL command syntax
In this episode we will start working with SQL, a special-purpose programming language used for managing data held in MySQL. We will show you the SQL syntax and some basic commands: how to create a database, how to create a table, and how to create a user.
SQL commands syntax
MySQL uses a standard form of the well-known SQL data language. The great thing about SQL is that the code is very easy to read, as opposed to harder programming languages, such as C or C++. SQL statements are made up of plain English terms. These terms are called keywords, and every SQL statement is made up of one or more keywords. The SQL statement that you’ll probably use most frequently is the SELECT statement, for example:
SELECT * FROM customers;
All SQL commands and keywords are case-insensitive. However, most people write SQL commands (such as SELECT, FROM, LIMIT, etc.) in uppercase.
To separate or to end SQL commands, the semicolon is used. This allows you to issue more than one command at a time by placing a semicolon after each command:
mysql> USE employees; SELECT * FROM employees LIMIT 10;
Database changed
+––—+––––+––––+–––—+––—+––––+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+––—+––––+––––+–––—+––—+––––+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
+––—+––––+––––+–––—+––—+––––+
10 rows in set (0.00 sec)
{NOTE - table names are case-insensitive on Windows, but case-sensitive on Linux and OS X. It is recommended to use lowercase characters for tables.}
Create a database
To create an MySQL database, the CREATE DATABASE name statement is used. For example, to create a database named testdb, we can use the following command:
mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.01 sec)
We got the message indicating that our query was OK and the time it took to complete the command. We should see our new database in the output of the SHOW databases command:
mysql> SHOW databases;
+––––––—+
| Database |
+––––––—+
| information_schema |
| employees |
| mysql |
| performance_schema |
| sakila |
| sys |
| testdb |
| world |
+––––––—+
8 rows in set (0.00 sec)
To work with our new database, we need to issue the USE testdb command:
mysql> USE testdb;
Database changed
Create a user
You should not do all your work in MySQL with the root user; instead, it is recommended to create users with custom permissions. To create a user, the CREATE USER ‘user’@‘hostname’IDENTIFIED BY ‘password’ command is used. Here is an example:
mysql> CREATE USER ‘testuser’@‘localhost’ IDENTIFIED BY ‘verysecret’;
Query OK, 0 rows affected (0.00 sec)
The command above creates a new user called testuser with the password of verysecret (localhost refers to the local computer MySQL is running on).
When the user is created, it has no permissions to do anything with the databases. The GRANT command is used to grant privileges. Here is the syntax:
GRANT PRIVILEGES ON database.object TO ‘username’@‘hostname’ IDENTIFIED BY ‘password’
The database.object keywords refer to the database itself and the objects it contains (e.g. tables). Here are some values of these keywords and their meaning:
- *.* - all databases and all their objects.
- database.* - the database specified and all its objects.
- database.object - the database specified and its specified objects.
For example, to grant the full access to the user testuser@localhost on the testdb database, the following command can be used:
mysql> GRANT ALL PRIVILEGES ON testdb.* TO ‘testuser’@‘localhost’;
Query OK, 0 rows affected (0.00 sec)
{NOTE - you can grant only privileges that you already have, and you must have the privilege to issue the GRANT command.}
We can rerun MySQL and log in as testuser to test whether the command above worked:
C:\>mysql -u testuser -p
Enter password: **********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> SHOW databases;
+––––––—+
| Database |
+––––––—+
| information_schema |
| testdb |
+––––––—+
2 rows in set (0.00 sec)
mysql>
As you can see from the output above, we’ve successfully logged in as testuser and displayed the testdb database.
Create a table
Tables are subcontainers within a database that store the actual data. The database is created without any tables. To create a table, the following command is used:
CREATE TABLE name(
column_list
) ENGINE name
Here is the explanation of the syntax:
- name - specifies the name of the table.
- column_list - specifies the list and data types of columns, separated by commas.
- ENGINE name - specifies the type of database engine to use for this table. If you don’t specify this parameter explicitly, MySQL will use InnoDB by default.
Here is an example. Let’s say we want to specify a table called testtable with three colums - name, surname, and birth year. We can use the following command:
mysql> USE testdb;
Database changed
mysql> CREATE TABLE testtable (name VARCHAR(128), surname VARCHAR(128), year CHAR(4)) ENGINE MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW tables;
+––––––+
| Tables_in_testdb |
+––––––+
| testtable |
+––––––+
1 row in set (0.00 sec)
To check how our new table looks like, we can use the following command:
mysql> DESCRIBE testtable;
+–––+––––—+––+–—+–––+––-+
| Field | Type | Null | Key | Default | Extra |
+–––+––––—+––+–—+–––+––-+
| name | varchar(128) | YES | | NULL | |
| surname | varchar(128) | YES | | NULL | |
| year | char(4) | YES | | NULL | |
+–––+––––—+––+–—+–––+––-+
3 rows in set (0.03 sec)
Note how each column has a specific data type and the size (e.g.,VARCHAR(255)). This data type is very useful, as it serves a guideline for MySQL to understand what type of data is expected inside of each column, and it also identifies how MySQL interacts with the stored data.. For example, the column year has predictable values, so instead of VARCHAR we have used the more efficient CHAR(4) data type. This parameter of 4 allows for 4 bytes of data, supporting all years from –999 to 9999.
Data types
A data type defines what kind of value a column in a database table can contain. This parameter tells MySQL the type of data that will be stored inside each table column, so MySQL can plan the size of databases and perform lookups and searches more effectively.
In the previous chapter we’ve defined the VARCHAR and CHAR data types. MySQL offers many additional data types, including string, numeric, and spatial data types. Here is a description of the most important ones:
- CHAR - a fixed-length string.
- VARCHAR - a variable-length string.
- BINARY - a fixed-length binary string. Used to store strings of full bytes that do not have an associated character set (such as GIFs).
- BLOB - a Binary Large OBject. Used for binary data in excess of 65,536 bytes in size.
- INT - a standard integer numerical.
- FLOAT - a single-precision floating point number.
- DATE - a date value in YYYY-MM-DD format.
- TIME - a time value in HH:MM:SS format.
- DATETIME - a date and time value in YYYY-MM-DD HH:MM:SS format.
Share This Post
