Simple MySQL Queries

This note may be useful for those who are just starting to use the MySQL.

Everything happens for the first time. For example, you got a job, or wanted to work in a project where this system was used.

Reading manuals is of course helpful. But the return on reading will be higher. If he already knows what will come of it.

Well, where to start? Here we have a console window (Linux or cmd in Windows). We know that someone has already installed and built a MySQL server, and even put different databases and bases there. And even created a user for you and gave you a password. Let’s see what’s there.

Try to connect to the mysql server, run on this computer, open a session, so to speak:

mysql -u username --password=passwd

Here usename is your username, passwd is your password. Usually written in this combination: -u for the username and -password= for the password. In theory, you can do --user=username or --ppasswd, but then you need to remember that the password is written without a space after the -p switch. It is also possible not to make a space after the -u switch. In general, there are many options.

When entering commands, remember about; at the end, otherwise MySQL will display the -> icon, requiring the banquet to continue, i.e. commands. If you are still in a hurry and press Enter, you can put a punctuation mark ; and after the -> icon and press Enter again.

Next, let’s see what databases we have.

show databases;

This query will list all the databases available to your user like this:

+-----------------+
| Database        |
+-----------------+
| db_test         |
| db_test_1       |
| db_not_test     |
+-----------------+

Select a database with the use db_test; command. In response, we will receive Database changed. With the same command, you can change the base from the current one to some other one.

You can select the database immediately upon opening

mysql -u username --password=passwd db_test

Find out what tables are in the database. Why do we run show tables;

+--------------------+
| Tables_in_db_test  |
+--------------------+
| person             |
| address            |
| interests          |
+--------------------+

Let’s look at the description of the table – what fields it has, what type they are, which of the fields are indexed (for beginners: indexing means that operations like searching on these fields will be faster). command desc person; will give us information. The Key field just means indexing.

+------------+--------------+------+-----+---------------------+----------------+
| Field      | Type         | Null | Key | Default             | Extra          |
+------------+--------------+------+-----+---------------------+----------------+
| person_no  | int(7)       |      | PRI | NULL                | auto_increment |
| name       | varchar(25)  |      |     |                     |                |
| last name  | varchar(50)  |      |     |                     |                |
| age        | int(3)       |      |     | 0                   |                |
| address_no | int(7)       |      |     | 0                   |                |
+------------+--------------+------+-----+---------------------+----------------+

Now we know what fields are in our tables and we can perform different queries. Below are some examples of queries with a brief explanation:

1. Find out the number of records in the table

SELECT count(*) FROM table_name;

2. Select all records from table

SELECT * FROM table_name;

3. Select multiple records from a table, for example 5. This is useful when you need to know how the data in the table looks like.

SELECT * FROM table_name LIMIT 5;

4. Select all records from person, sorted in ascending order of person_no number.

SELECT * FROM person ORDER BY person_no;

5. Select all records from person sorted in descending order of person_no number.

SELECT * FROM person ORDER BY person_no DESC;

6. Select multiple (12) records from person, sorted in ascending order of person_no number.

SELECT * FROM person ORDER BY person_no LIMIT 12;

7. Select all records from person where name field is Anna

SELECT * FROM person WHERE name='Anna';

8. Select all records from person where name field starts with An

SELECT * FROM person WHERE name LIKE 'An%';

9. Select all records from person ordered by person_no where the name field ends with na

SELECT * FROM person WHERE name LIKE '%na' ORDER BY person_no;

10. Select all occurring name-last_name pairs from the table.

SELECT name, last_name FROM person;

11. Select all distinct (unique) occurring name-last_name pairs from a table. Duplicates will be omitted from the results.

SELECT DISTINCT name, last_name FROM person;

Oh, how much you can ask MySQL 😀 I think these queries for the first time will be enough for one table. You can also query multiple tables at the same time.

Display all the unique last names of people living in a city with a name beginning with the letter L. (We assume that the address table has fields address_no, city).

SELECT DISTINCT last_name FROM person p, address adr WHERE p.adress_no = adr.address_no AND city LIKE 'L%';

If two tables have columns with the same name, then MySQL cannot guess from which table we needed the field. You can explicitly specify the table name by putting it before the field name and connecting them with a dot (address.address_no). Or you can give the tables names directly in the query to make it shorter or clearer (in the example, this is p for person and adr for address). You can select the desired field by specifying it also through a dot after the short table name (adr.address_no).