MySQL for a user

On the World Wide Web you can find a lot of information on how to configure MySQL-server, but almost no information on how to use it for an ordinary user.

This is probably because applications that are based on MySQL are written by qualified programmers, and the user does not need to know what and how it works. But knowing it still does not hurt, for example, you want to know how this or that SQL query is executed.

A handy program to view the structure of a database is mysqlshow. Enter the following command:

mysqlshow -p mysql

You will see a list of tables that are in the mysql database.

Database: mysql
+--------+
| Tables |
+--------+
| db |
| host |
| user |
+--------+

The mysqlshow program can be called with the additional parameters listed below.

• -host=hostname => Sets the name of the host you want to connect to
• -port=port_number => Specifies the port number for the MySQL server.
• -socket=socket => Specifies a socket
• -user=username => With this parameter you can specify the desired user name
• -p => Asks for a password

The mysql program is used for the same data operations. It is the client of the server. You can use the same options as the mysqlshow in this program. Among the many options, mysql has one very important parameter “-s”. I recommend that you always use it. This parameter suppresses most unwanted messages that the client outputs. On slow links this should improve performance. And you do not want to watch all frames and unnecessary messages.

You can create a database using the mysqladmin program. Naturally, only an administrator can use this program, for example:

mysqladmin -u admin -p create my_db

How does an SQL Server work?

How does an SQL Server work? A client sends a query in which it indicates what information it wants to receive from the server or what data operation it wants to perform, the server sends a response to the client, which indicates whether the server has executed the client’s query and, if the server has executed the query, the result of the query.

An entire language – SQL (Structured Query Language) – has been developed to describe the client’s queries. With the help of SQL queries you can:

• Create databases and tables
• Add information to the tables
• Delete information
• Modify information
• Get the data you need

Naturally, the admin user, in addition to what should exist, must have the appropriate rights. Each MySQL query must end with a semicolon. If you enter SELECT * FROM test, the mysql client will wait for the semicolon to be entered:

->

Let’s agree that we will write queries according to the SQL standard, that is, to improve the perception we will split them into parts. The program mysql allows you to enter a query in the whole line. For example, a query written in the SQL standard

SELECT *
FROM S
WHERE Q > 10

in the mysql program, you can write it down like this:

SELECT * FROM S WHERE Q > 10

Now let’s create three tables – Goods, Clients and Orders.

CREATE TABLE CLIENTS
(
C_NO int NOT NULL,
FIO char(40) NOT NULL,
ADDRESS char(30) NOT NULL,
CITY char(15) NOT NULL,
PHONE char(11) NOT NULL
);

Table CLIENTS contains fields C_NO (client number), FN (Full Name), Address, City and Phone. All these fields may not contain an empty value (NOT NULL).

CREATE TABLE PRODUCT
(
T_NO int NOT NULL,
DSEC char(40) NOT NULL,
PRICE numeric(9,2) NOT NULL,
QTY numeric(9,2) NOT NULL
);

This table will contain data on goods. Type numeric(9,2) means that 9 characters are attributed to the whole part and two to the fractional part. QTY is the amount of goods in stock.

CREATE TABLE ORDERS
(
O_NO int NOT NULL,
DATE date NOT NULL,
C_NO int NOT NULL,
T_NO int NOT NULL,
QUANTITY numeric(9,2) NOT NULL,
AMOUNT numeric(9,2) NOT NULL
);

This table contains order information – order number (O_No), order date (DATE), customer number (C_NO), product number (T_NO), quantity (QUANTITY) and the sum of the total AMOUNT order (i.e. AMOUNT = T_NO * PRODUCT.PRICE).

Now let’s add the data to our tables. You can add data using the INSERT operator. Let us consider using the INSERT operator:

INSERT INTO CLIENTS
VALUES (1, 'Smith P.', '425 Creek Ave', 'NY', '80522111111');

The added values must match the order in which the fields are listed in the CREATE operator. If you want to add information in a different order, you must specify this order in the INSERT statement:

INSERT INTO CLIENTS
(FN,ADDRESS,C_NO,PHONE,CITY) VALUES ('Johnson M.', '930 Mill Rd',2,'-','NY');

With INSERT, we can add data to specific fields such as C_NO and FullName:

INSERT INTO CLIENTS (C_NO, FN) VALUES (1,'Johnson M.');

But the server will not execute our request, because all other fields are equal to NULL (empty value), and our table does not accept empty values. You can add data to other tables in the same way. Let’s add data to the PRODUCT table:

INSERT INTO PRODUCT VALUES (1,'Monitor LG',550.74);

Note that we haven’t specified the primary table keys yet, so no one prevents us from adding the same entries to the table. You can add a date to the DATE field using the TO_DATE function:

INSERT INTO ORDERS VALUES (1,TO_DATE('01/01/20,'DD/MM/YY'),1,1,1,550.74);

This entry means that on January 1, 2020 Smith P. (C_NO=1) ordered one (QUANTITY=1) LG Monitor (T_NO=1).

Suppose that we need to update the record, for example, Ivanov’s client moved to another city. This is done as follows:

UPDATE CLIENTS
SET CITY = 'WASHINGTON'
WHERE C_NO = 1;

Now we’ll delete all clients whose numbers exceed 10:

DELETE FROM CLIENTS WHERE C_NO > 10;

Using the DELETE command, you can delete all table records by specifying a condition that is suitable for all records, for example:

DELETE FROM CLIETNS;

If the second part of the DELETE operator – WHERE – is not specified, it means that the operator action covers all records at once.

Adding, modifying, and deleting records are certainly very important commands, but most often you will use the SELECT operator, which selects data from the table. For example, to output all records from the CLIENTS table, type:

SELECT * FROM CLIENTS;

As a result, you will get this response from the server:

C_NO FIO ADDRESS CITY PHONE
1 Smith P. 425 Creek Ave NY 80522111111
1 Smith P. 425 Creek Ave NY 80522111111
2 Johnson M. 930 Mill Rd NY 80522112111

Pay attention to the first two records – they are the same. Theoretically, adding the same records is possible – we have not specified the primary table key. If you want to exclude identical records from the server response (but not from the table!), enter the query:

SELECT DISTINCT *
FROM CLIENTS;

Suppose you want to print only the client’s name and phone number, then enter the request:

SELECT DISTINCT FN,
PHONE FROM CLIENTS;

Now let’s make our requests more complicated. Withdraw all goods whose price exceeds 500 Dollars.

SELECT *
FROM PRODUCT
WHERE PRICE > 500;

You can use other signs of a relationship:

<,>,=,<>,<=,>=.

If your company serves several namespaces and you want to display information about all Smith, use the LIKE template:

SELECT *
FROM CLIENTS
WHERE FN LIKE '%Smith%';

The request reads as follows: print all information about clients whose names are similar to ‘Smith’. If you want to select data from different tables, you must specify the table name before the field name. The next query will display the names of all customers who have bought from us at least once:

SELECT DISTINCT CLIENTS.FN
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ODREDS.C_NO;

The SELECT operator allows the use of nested requests. The next operator is similar to the previous one:

SELECT DISTINCT CLIENTS.FN
FROM CLIENTS
WHERE CLIENTS.C_NO IN (SELECT C_NO
FROM ORDERS);

When working with a SELECT operator you have several useful functions that calculate the number of elements (COUNT), the sum of elements (SUM), the maximum and minimum value (MAX and MIN) and the average value (AVG).

The following operators will display respectively the number of entries in the CLIENTS table, the most expensive item and the sum of all items in stock.

SELECT COUNT(*)
FROM CLIENTS;

SELECT MAX(PRICE)
FROM PRODUCT;

SELECT SUM(PRICE)
FROM PRODUCT;

The SELECT operator allows grouping the returned values. For example, Smith client (C_NO=1) has ordered a product from us several times. It means that its number appears in the ORDERS table several times.

Let’s display the names of all clients, as well as the order amount of each client.

SELECT CLIENTS.FN, SUM(ORDERS.AMOUNT) AS TOTALSUM
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ORDERS.C_NO
GROUP BY ORDERS.C_NO;

Grouping is performed by the GROUP BY operator, which is part of the SELECT operator. The GROUP BY operator can be restricted with HAVING. This operator is used to select the lines returned by GROUP BY. HAVING can be considered equivalent to WHERE, but only for GROUP BY:

HAVING <condition>

For example, we are interested only in customers who have ordered goods with a total value exceeding 1000.

SELECT CLIENTS.FIO, SUM(ORDERS.AMOUNT) AS TOTALSUM
FROM CLIENTS, ORDERS
WHERE CLIENTS.C_NO = ORDERS.C_NO
GROUP BY ORDERS.C_NO
HAVING TOTALSUM > 1000;

In this query we used the column alias TOTALSUM. Some SQL servers do not need to write the AS service word to define an alias, and some require an equal sign: SUM(ORDERS.AMOUNT) TOTALSUM or TOTALSUM = SUM(ORDERS.AMOUNT).

Until we set the primary key, the sorting of our table is not performed. The data will be displayed in the order they are entered in the table. The following operator is used to sort the result of the CLIENTS table output by C_NO field (the table itself is not sorted):

SELECT *
FROM CLIENTS
ORDER BY C_NO;

Suppose someone added a record to the CLIENTS table

1 Williams 47 Helen Ave NY 80522345111

It turns out that the same number is mapped to different clients. Then which of them ordered the LG monitor? To avoid this kind of confusion, you need to use primary keys:

ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_NO);

After this query, the C_NO field can only contain unique values. A field allowing NULL value cannot be used as a primary key. It is easier to create a primary key when creating a table. It is done so:

CREATE TABLE CLIENTS
(
C_NO int NOT NULL,
FN char(40) NOT NULL,
ADDRESS char(30) NOT NULL,
CITY char(15) NOT NULL,
PHONE char(11) NOT NULL,
PRIMARY KEY (C_NO);
);

The ORDERS table contains information about orders. The C_NO field in this table identifies the customer. Suppose that someone entered a value in the ORDERS table that is not in the CLIENTS table. Who ordered the product? We need to prevent this situation, so enter the following request:

ALTER TABLE ORDERS
ADD FOREIGN KEY(C_NO) REFERENCES CLIENTS;

C_NO client numbers entered in the ORDERS table must exist in the CLIENTS table. Similarly, a foreign key must be added in the T_NO field. This feature is called declarative integrity.

The ALTER command is not only used to add keys. It is intended to reorganize the table as a whole. Do you want to add another field? Or set a list of acceptable values for each of the fields. You can do all this with the ALTER command:

ALTER TABLE CLIENTS
ADD ZIP char(6) NULL;

This operator adds a new ZIP field of char type to the CLIENTS table. Note that you cannot add a new field with a NOT NULL value to a table that already has data. Our company works with clients only from WASHINGTON and NY, so it is advisable to enter a list of acceptable values for the table CLIENTS:

ALTER TABLE CLIENTS
ADD CONSTRAINT INVALID_STATE SHECK (CITY IN ('WASHINGTON','NY'));

Are you tired of working with this database already? Then disconnect yourself from it with the DISCONNECT query and connect to another database with the CONNECT query. In some SQL servers the DISCONNECT query does not work and instead of CONNECT you need to use the USE operator.

Now that you are already familiar with the basics of SQL, let’s go a little deeper. We already know how to add a primary key, now let’s add a foreign key when creating a table:

CREATE TABLE T
(
/* Description of fields in the table */
FOREIGN KEY KEY_NAME (LIST)
REFERENCES ANOTHER_TABLE [(LIST2)]
[ON DELETE OPTION]
[ON UPDATE OPTION]
);

Here, KEY_NAME is the key name. A name is not required, but I strongly recommend that you always give the name of the key – if you don’t give the name of the key, you can’t remove it later. Is there anything that can happen that maybe you won’t need it anymore?

LIST is a list of fields included in a foreign key. The list is separated by commas. ANOTHER_TABLE is another table that installs the foreign key, and the optional element LIST2 is the list of fields in that table. The types of fields in the LIST list must match those in the LIST2 list.

Suppose that in the first table we have two fields – NO and NAME – of integer and character types respectively. In the second table, we have fields with the same names and types. Defining the foreign key

FOREIGN KEY KEY_NAME (NO, NAME)
REFERENCES ANOTHER_TABLE (NAME, NO)

It’s not correct, because the field types NO and NAME don’t match. You have to use that definition:

FOREIGN KEY KEY_NAME (NO, NAME)
REFERENCES ANOTHER_TABLE (NO, NAME)

If the fields have the same names, as in our case, the LIST2 list should not be specified at all, so as not to create unnecessary problems.

Optional parameters ON DELETE and ON UPDATE determine the action to update information in the database, when removing information from the table and when updating it. Remember our example with clients? I mean that there is a C_NO (Client NO) field in the order table, the values of which should be in the client table.

In fact, how do we know the name and other data of a client number 99999 that is not in the client table? By setting a foreign key, we link the two tables by the C_NO field. You can sleep in peace (I wanted to say database administration), until one fine moment when the girl operator will remove some client from the client table. What to do with records in the order table?

With the ON DELETE parameter we can tell the server how to respond to the deletion of such data:

ON DELETE OPTION

OPTION can take one of four values: CASCADE, NO ACTION, SET DEFAULT, SET NULL.

CASCADE means that the deleted client number will be removed from all linked tables. For example, if you delete a customer with the number 10 from a table of customers, all orders of that customer will be deleted from the order table.

The NO ACTION option does not allow you to delete a client as long as it is in a linked table. This means that the girl operator must first remove all order information from the order table.

With the SET_DEFAULT parameter you can specify a default value. For example, if you specify SET DEFAULT 1, then when you delete a client from any number, its orders will be assigned to a client with number 1, which of course is always in the CLIENTS table.

Parameter SET NULL sets the value of NULL as the number of the client, if it is removed from the table CLIENTS. Remember that in our case the C_NO field does not allow the value of NULL! And how do I delete the field? The SQL standard does not provide for removing columns, but in MySQL we can still do it:

ALTER TABLE CLIENTS
DROP ZIP;

Deleting the table is even easier:

DROP ORDERS;

MySQL Tutorial – How to Create a MySQL Database, User and Grant Permissions (video)