How is SQL different from C.


24.3 Crash Course (My) SQL

In order to be able to work with MySQL, you will now get to know some basic language elements. As a C programmer, there are a lot of things that will not be difficult for you anyway. Of course, the whole range of SQL commands should not be used here, but only a selected number that enables you to set up a database structure and formulate the associated queries.


24.3.1 What is SQL?

To get straight to the point: SQL is not a programming language. SQL stands for Structured Query Language and is divided into two parts:

  • Data Definition Language (DDL) statements - These statements are used to set up the database structure.
  • Data Manipulation Language (DML) instructions - These instructions relate to manipulating data.

24.3.2 The data types of (My) SQL

As in C, MySQL has data types. These are divided into numeric data types, data types for strings and data types for date and time. We begin with a brief overview of the data types for integers (integer data types) with their range specification:


Data typeMemory requirementsExplanation

1 byte (8 bit)

–128 to 127 with UNSIGNED from 0 to 255

2 bytes (16 bit)

–32768 to 32767 with UNSIGNED from 0 to 65535

3 bytes (24 bits)

–83886008 to 83886007 with UNSIGNED from 0 to 16777215

4 bytes (32 bit)

–2147483648 to 2147483647 with UNSIGNED from 0 to 4294967296

8 bytes (64 bit)

–9223372036854775808 to 9223372036854775807 with UNSIGNED from 0 to 18446744073709551616


As in C, you can also set the sign bit in MySQL with the UNSIGNED keyword. The following data types are available for floating point numbers:


Data typeMemory requirementsExplanation

4 bytes (32 bit)

-3.402823466E + 38 to 1.175494351E-38 and 1.175494351E-38 to 3.402823466E + 38

8 bytes (64 bit)

-1.7976931348623157E + 308 to -2.2250738585072014E-308 and 2.2250738585072014E-308 to 1.7976931348623157E + 308

M byte (M * 8 bit)

Depending on M and D


You can use several data types for strings, which - apart from the name - mainly differ in the number of characters they can contain:


Data typeMemory requirementsExplanation

x byte

Fixed length character string with x bytes up to a maximum of 255 characters

x + 1 byte

Character string with variable length with x-byte up to max. 255 characters

Number of characters + 1 byte

Character string with variable length up to a maximum of 255 characters

Number of characters + 2 bytes

Character string with variable length up to max. 65535 characters

Number of characters + 3 bytes

Character string with variable length up to a maximum of 224 characters

Number of characters + 4 bytes

Character string with variable length up to a maximum of 232 characters


The BLOP data types are intended for binary data. Finally, there are some MySQL data types for date and time:


Data typeMemory requirementsExplanation

3 bytes (24 bits)

Date from 1000-01-01 to 9999-12-31; Format YYYY-MM-DD

8 bytes (64 bit)

Date and time from 1000-01-01 00:00:00 to 9999-12-31 23:59:59; Format YYYY-MM-DD HH: MM: SS

4 bytes (32 bit)

UNIX timestamp of date and time from 1970-01-01 00:00:00 to 2036-12-31 23:59:59; Format YYYY-MM-DD HH: MM: SS. Format M = 6 YYMMDD; M = 8 YYYYMMDD; M = 12 YYMMDDHHMMSS; M = 14 YYYYMMDDHHMMSS

3 bytes (24 bits)

Time from -838: 59: 89 to 838: 59: 59; Format HH: MM: SS

1 byte (8 bit)

Year (4 digits) from 1901 to 2155; Format YYYY


An example is intended to introduce you to the SQL language. First start the client mysql again with:

mysql -u root -h localhost

24.3.3 Create a database

Before you can work with a database, you must first create a new one. You can do this with the following command:

CREATE DATABASE dvd_archiv;

Figure 24.3 A database has been created.

With this SQL command you create a new database with the name dvd_archiv. As you already know from C, the command ends with a semicolon.


Note

Incidentally, it does not matter whether you write the SQL statement in uppercase, lowercase or a mixture of uppercase and lowercase letters. For example, you can use the following notations: Create Database or create database.


If a database already exists, an error message is also issued. If you want to create a condition when creating a new database, you can proceed as follows:

CREATE DATABASE IF NOT EXISTS dvd_archiv;

IF NOT EXISTS first checks whether a database with the name dvd_archiv does not yet exist. If so, the CREATE DATABASE command is executed.


Note

The name of the database cannot be longer than 64 characters. The same rules apply to characters as to the assignment of names to variables in C, except that the name can begin with any character. In contrast to Windows, Linux distinguishes between upper and lower case, at least when it comes to the name of the database.



24.3.4 Delete a database

Next, the newly created database should be deleted again. If you have forgotten the name of the newly created database, it is best to have an overview of all databases. To do this, use the SQL command:

Figure 24.4 View all existing databases

So now it's time again to delete the dvd_archiv database:

DROP DATABASE dvd_archiv;

If you also want to intercept an error message with a condition here, you can also write the deletion as follows:

DROP DATABASE IF EXISTS dvd_archiv;

This will only attempt to delete the database if it actually exists. Recreate the dvd_archiv database so that you can continue with the following examples.


24.3.5 Change database

If you want to switch between different databases, you can use the USE command. Example:


24.3.6 Create a table

Now that you've created a database, you'll want to store data in it too. But first you have to create a table. You create a table with the following SQL statement:

CREATE TABLE film data (title CHAR (255), main role CHAR (255), rotated YEAR);

With this SQL command you create the column title in the table film data with the data type CHAR (255), main role with the data type CHAR (255) and rotated with the data type YEAR. The table (data record) with the name filmdaten (a maximum of 64 characters are allowed) is created in the database dvd_archiv - provided you have switched to the relevant database with USE. Three variables were declared as the content of the table: two data types for strings with 255 characters each, which contain the title (title) and the main actor (main role) of a particular film, as well as a data type that can store the year of the film.

The notation used here is not necessarily that prescribed. The table can also be created with one line at a time:

CREATE TABLE film data (title CHAR (255), main role CHAR (255), rotated YEAR);

The syntax is always composed as follows:

CREATE TABLE tbl (column name data type of column, column name data type of column, column name data type of column);

In contrast to C, the identifier is written here before the data type.


24.3.7 Display the table

You can display an overview of the table that you created with CREATE with the SQL command SHOW:


24.3.8 Check table definition

If you no longer know exactly what you have specified in the table, you can get an overview with the EXPLAIN command:


Note

In Figure 24.5 you can see the value NULL in the default column. This is not to be equated with the value NULL, which you know from C. NULL in MySQL simply means: There is no value for it. However, if you use the double quotation marks (""), it means in MySQL that the value is unknown. It may seem the same at first glance, but it isn't. This is a common MySQL mistake.


Figure 24.5 Check the table definition


24.3.9 Delete table

If you no longer need a table, you can remove it with the same SQL command as for deleting a database:

DROP TABLE IF EXISTS movie data;

Whereby IF EXISTS is optional here again. You can see how easy it is to delete something with MySQL. It is therefore often useful to regularly back up a database. If you want to create a backup, you can have a look at the client mysqldump, which is also included in a normal MySQL installation. For example, if you want to back up the entire MySQL database, enter the following at the prompt:

mysqldump -u root -p -opt -all -databases> my_backup.sql

After this entry, you have to quickly enter your password and the data is secured. If you want to restore the backup, you can do this with the mysql client:

mysql -u root -p

24.3.10 Change the structure of a table

Now you want to change the structure of a table. You can do this with the SQL command ALTER, regardless of whether this table already contains data. In the film data table, the age restrictions for the individual films are still missing. This should now be inserted behind the row main role:

ALTER TABLE film data ADD fsk TINYINT AFTER main role;

With a certain knowledge of English, this line can be read almost like a sentence: "Change table film data and add fsk of type TINYINT after main role." With the SQL command ALTER you can do a lot more. For example, if you want to remove an element in the table, write:

ALTER TABLE film data DROP fsk;

For example, the fsk column would be removed from the filmdata table. You can change a column name with the following command:

ALTER TABLE film data CHANGE fsk alter INT;

This would rename the column fsk to old. In addition, the new element is now of the data type INT. If you want to enter more than 255 characters for the title of the film data table, write:

ALTER TABLE movie data MODIFY title CHAR (300);

The ALTER command can be used in a wide variety of ways, so it would be worthwhile to examine this command in more detail.


24.3.11 Enter data records

You can insert data into the table with the INSERT command. There are three ways INSERT can be used. The simplest variant follows in this section. Now add your first data to the film data table:

INSERT INTO film data (title, main role, fsk, shot) VALUES (’The Patriot’, ’Mel Gibson’, 16, 2001);

It is important that the content in the second parentheses must match the order of the first parentheses in the table. You could also read this data into the table in a different order:

INSERT INTO film data (main role, title, shot, fsk) VALUES (’Mel Gibson’, ’The Patriot’, 2001, 16);

You must therefore always ensure that the values ​​specified in VALUES match those in the first brackets. Of course, you can also insert several data into a table at once:

INSERT INTO film data (title, main role, fsk, shot) VALUES (’Braveheart’, ’Mel Gibson’, 16, 1998), (’Hello Mr. President’, ’Michael Douglas’, 6, 1995);

The individual data records in the table are separated by commas. The strings were placed between single quotes, but with MySQL they can also be placed between double quotes, as you know from C.


24.3.12 Select data sets

Now we come to the most important and most widely used command in SQL: SELECT. With SELECT you can collect information from one or more tables. First, all data in the film data table should be displayed using SELECT:

Figure 24.6 Select data records

The asterisk stands here as a placeholder for all columns in the table. This placeholder has the same meaning as if you were to write:

SELECT title, main role, fsk, shot FROM film data;

This also outputs all the data in a table. With FROM you specify the table to be selected. If you are only interested in the titles of the films, you can use SELECT as follows:

SELECT title FROM film data;

If, on the other hand, you only want to output the data of the films in which Mel Gibson is the main actor, use the SQL command WHERE as well:

SELECT * FROM film data WHERE main role = ’Mel Gibson’;

In case you don't remember exactly how the main character is written, use the following:

SELECT * FROM film data WHERE main role LIKE ‘Mel%’;

The fuzzy operator LIKE was used here. If you want to display all films sorted by date, you can do this with:

SELECT * FROM film data ORDER BY rotated ASC;

With the ORDER BY SQL command, you tell the server to output the data in an orderly manner; The order in which this happens is specified with ASC (ascending) or DESC (descending). You can of course also use the comparison operators that you already know from C.


3/24/13 An advanced scenario

A look at your dvd_archiv database shows that you have rented out some films. Unfortunately, you no longer know to whom. Now that you know a little about MySQL, you solve the problem with the database. First create a new table contact in the database dvd_archiv:

CREATE TABLE contact (nname CHAR (30), vname CHAR (30), phone BIGINT);

Next, enter the data for the contact table:

INSERT INTO contact (nname, vname, phone) VALUES (’Meier’, ’Hans’, 12345676), (’Müller’, ’Franz’, 3691215), (’Schiller’, ’Johann’, 48121620);

Now you need a table that shows the films that you have rented together with the information about who they were awarded to:

CREATE TABLE granted (name CHAR (30), title CHAR (255));

You will now learn about another method how you can read in data records with INSERT:

INSERT INTO awarded (name, title) SELECT contact.nname, filmdaten.titel FROM contact, filmdaten WHERE filmdaten.titel = ’Der Patriot’ AND contact.nname = ’Meier’;

Use SELECT to add data to the loaned table. With the lines kontakt.nname and filmdaten.titel you have created a link between two tables and have had contact with the relational model for the first time. Because although both tables are physically separated from each other, they can be linked with SELECT. Another new feature is the logical AND link, which you as a C programmer know as &&. If you now take a look at the table granted, it now actually contains the title of the film and the name of the person to whom you have awarded the film:

Now you want to find out the person's phone number so that you can get in touch with them. Try to put it into words yourself. Example: "Find the phone number of someone I loaned a movie to." To do this in MySQL:

SELECT phone FROM contact, loaned WHERE loaned.name = contact.nname;

A real MySQL guru is sure to do it even more elegantly. But that is not the aim of this chapter now. The purpose of this example is to show you why you would be well advised to write your own client programs in C.


24.3.14 Delete data record

Once your friend has brought the film back to you, it is time to delete it from the table on loan. You can do this with the SQL command DELETE:

DELETE FROM awarded WHERE title = 'The Patriot';

24.3.15 Change data record

You can use the UPDATE SQL command to change individual or multiple data records in a table. The SET command is also used for this purpose. For example, if you want to change the age limit for a film, you can proceed as follows:

UPDATE film data SET fsk = 12 WHERE fsk = 6;

This changes all data fsk with the value 6 to the value 12.


Note

Please do not delete the dvd_Archive database. It will be needed again later for the C-API.



24.3.16 Access rights in MySQL

The access rights of MySQL will only be discussed briefly here. MySQL's access rights are also managed in tables. Go to the database and look at the tables in it:

Six tables are listed. They have the following meaning:


tableimportance

This contains the access rights of the users who can access all databases and tables.

This contains the access rights of the users who can access a specific database.

This contains the access rights of the users who can access a certain table.

This contains the access rights of the users who can access a specific column.

This contains the access rights for certain computers from which the database can be accessed.

This table has not yet been documented.In the future, the access rights of individual user-defined functions should be entered there.


This table can be used to assign the following access levels for assigning user rights:

  • global rights - Refers to all databases.
  • Database - Refers to a specific database.
  • Table - Refers to a specific table in a database.
  • Column - Refers to a specific column in a table.

You can assign user rights with the SQL command GRANT, and you can revoke user rights with REVOKE. Since the topic is quite extensive, please refer to the MySQL documentation.


Note

When installing MySQL for the first time, the root password is not set. That is logical, otherwise you would not be able to start the client. In practice it is advisable to use a password. The listings you create in the following sections assume that no password has yet been assigned for root. If you have already changed the password for root, change this in the listings as well.



24.3.17 Overview of some SQL commands

So that you don't have to page back to the individual sections again and again, here is a brief overview of the SQL commands that you have come to know so far and their respective meanings:


SQL commandCauses
CREATE DATABASE database;

create a database

view a database

delete a database

Change database

CREATE TABLE table (column name data type, column name data type, column name data type);

create a table

Show table

Check table definition

DROP TABLE IF EXISTS table;

Delete table

ALTER TABLE table ADD identifier_new data type AFTER identifier;

Insert data into the table

ALTER TABLE table -> DROP column names;

Remove data from the table

ALTER TABLE table CHANGE old_identifier new_identifier Data type;

change the identifier

ALTER TABLE table MODIFY identifier data type_new;

change the data type of the identifier

INSERT INTO table (identifier1, identifier2, identifier3, identifier4) VALUES ('foo', 'baa', 99, 66);

Enter records in table

output all data records of a table

SELECT identifier FROM table;

output individual data records of a table

SELECT * FROM film data WHERE main role = 'foo';

Output data records of a table with specific content

SELECT * FROM table ORDER BY identifier ASC;

Output data records sorted (ascending)

SELECT * FROM table ORDER BY identifier DESC;

Output data records sorted (descending)

DELETE FROM table WHERE identifier = 'foo';

Delete data record from table with certain content

UPDATE table SET identifier = 12 WHERE identifier = 6;

Change data record from table with certain content

GRANT ALL ON table TO username

Assign user rights

REVOKE ALL ON table TO username

Revoke user rights




your opinion

How did you like the Openbook? We always look forward to your feedback. Please send us your feedback as an e-mail to [email protected]