Java, SQL

Basic SQL commands part 1

Contents

Basic SQL commands

We will study the basic SQL commands to query and manipulate data in tables. From now on, we will work exclusively with the test database. At first we have to open the CMD window (or the terminal in Linux/MacOs) and type:

mysql -u testuser -p

Just enter password and we are good to go. Then in the MySQL command prompt, just type:

mysql> use test;

show tables

This command is used to show all the tables in the test database:

mysql> show tables;

The output:

Empty set (0.00 sec)

Clearly, we do not have any tables yet. We will create one – very soon.

create table

This command is used to create a table:

mysql> create table Customers 
       (CustomerId int not null auto_increment primary key, 
       FirstName varchar(50), 
       LastName varchar(50)
       );

Explanation:

– create table are the keywords, we use them to create a table. We need to specify the table name after these keywords.

– inside the brackets are table columns. In each column, the first is the name of the column, and next is the type of the column.

– The first column’s name is CustomerId, its type is int (which is an integer number).

– The not null keyword means that this column cannot be null.

– The auto_increment keyword means that MySQL will automatically increment the column value by 1 each time a new row is inserted into the table. The first row’s value will be 0.

– The primary key keywords means that MySQL will mark this column as the primary key.

– FirstName second column’s name. Its type is varchar which is a string, varchar(50) means that the string’s max length is 50.

– The same for the third column.

– Each SQL statements end with a semi-conlon ;

Copy the lines into the terminal and press Enter:

Query OK, 0 rows affected (0.02 sec)

insert into

This command is used to insert rows into Customer table:

mysql> insert into Customers 
       (FirstName, LastName) 
       values
       ('Tom', 'Rex'),
       ('Higie', 'Roniek'),
       ('Ken', 'Star');

Explanation:

– insert into are the keywords, we use them to insert a row into a table. The table that we want to modify stays right after these keywords.

– values is the keyword to specify that the values of each rows that we want to inset will come right after 😀

– the value set of each row must be inside the brackets, the string is marked by single quotes like ‘Tom’, each value in the value set is separated by a comma. The order of each value must be the same as the order of the column in the table.

– each row’s value set is separated by the comma.

Copy and run the code:

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

select * from

This command query the data in Customers table:

mysql> select * from Customers;

Explanation:

– select is the keyword, we use it to query the content of a table. The asterisk * means we want to query all the columns of the table.

– from is the keyword, we use it to specify which table we want to query.

Copy and run the code and we have:

+------------+-----------+----------+
| CustomerId | FirstName | LastName |
+------------+-----------+----------+
|          1 | Tom       | Rex      |
|          2 | Higie     | Roniek   |
|          3 | Ken       | Star     |
+------------+-----------+----------+

Perfect 😀

drop

This command is used to delete a table:

First we should create a dummy table named Test (we don’t want to delete our Customers table 😀 ):

mysql> create table Test (
	Id int, Dummy varchar(10)
	);

Ok, now we can delete this table with this line:

mysql> drop table Test;

Run the code and we have:

Query OK, 0 rows affected (0.01 sec)

The Test table has been deleted.

select as from

This command is used to query only some columns:

We use the select keyword again:

mysql> select CustomerId, LastName from Customers;

Explanation:

– we name each column that we want to query, each is separated by the comma.

– the from keyword specifies which table we want to query.

Now run the code:

+------------+----------+
| CustomerId | LastName |
+------------+----------+
|          1 | Rex      |
|          2 | Roniek   |
|          3 | Star     |
+------------+----------+
3 rows in set (0.00 sec)

– query with the result from concatenation of two or more columns:

Say we want there are two columns in the result, one is the Id, the other is the Full Name which consists of FirstName and LastName:

mysql> select CustomerId, 
       concat(FirstName, ' ', LastName) as "Full Name"
       from Customers;

Explanation:

– we use select at the start and from Customers at the end.

– the first column we want to query is the CustomerId, separated with the next column by a comma.

– the second column is the concat function (the same as a method in Java). The concat will concatenate three parameters:

+ FirstName

+ a space (actually a string, so we put it in single quotes)

+ LastName

– as “Full Name” means that the newly created column will be called “Full Name”.

Now, run the code:

+------------+--------------+
| CustomerId | Full Name    |
+------------+--------------+
|          1 | Tom Rex      |
|          2 | Higie Roniek |
|          3 | Ken Star     |
+------------+--------------+
3 rows in set (0.00 sec)

Leave a Reply