1. 1. The relational model
Think about your favourite app. Whenever you navigate into it, regardless of the device or browser, some kind of database must be running on the server in order for the data to be present. A relational database is a tool that stores data and lets you create, select, update and delete that data:
DATABASE
Atrributes (Columns)
ID
NAME
SURNAME
EMAIL
-Heading
1
John Lara J.K.
Doe jd@email.com Croft tr@email.com Rowling jkr@email.com
TABLE
2
-Body
3
Row
Tables are spreadsheet like structures with columns and rows. Each database table has a name and there are different naming conventions (singular or plural form, t_name, name_table). A column contains a collection of data related to the name of the column A row contains a collection of data related to that table's single item.
Feel comfortable with the relational model concepts? Head to the next section and accept the challenge!
2.MySQL
The MySQL command line interface tools
The terminal window is equipped with MySQL. MySQL is an open source implementation of a relational database management system which contains the database server and client command line application that let us run SQL commands directly on the command line interface. Use the MySQL client by executing the following command in the terminal window:
mysql You should then see this: Welcome to the MysQL monitor. Commands end with ; or \g
mysql> From the client, SQL commands allow us to (among other things):
Define where and how data is stored (CREATE, ALTER, DROP, RENAME) Manipulate the data (SELECT, INSERT, UPDATE, DELETE) Control the access to the data (GRANT, REVOKE)
Head to the next section to understand more about SQL server and client.
2. 1. MySOL server and client
The MySQL server is used to persist the data (store the data) and provide a query interface for it (SQL). The MySQL client's purpose is to allow you to use that query interface with SQL commands. As a summary, you can use the MySQL client to send commands to any MySQL server in any part of the world from a remote computer if you have the access information, such as:
The database name The hostname where the database is hosted. (Databases in your local computer use localhost for example.) The database user The database password
Let's list the existing databases in the next section.
3. Listing databases
We've created a DATABASE called: people List the existing databases in your current mysq1> prompt by executing sHOw DATABASES ; to then see:
Database
information_schema directory mysql people schema
3 rows in set (0.00 sec)
SQL commands capitalization is not mandatory
However, it is a good practice and a convention to use capitalized SQL commands Please note that we are going to use capitalized SQL commands for our examples as well as for the challenges.
Semicolons are part of the SQL syntax
Whenever you complete a SQL statement, be sure to append a semicolon ; . If you forget to type an ending semicolon, a new