What is MySQL?
MySQL, is the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation. – MySQL Reference Manual
– Open Source, Free
– Easy to use
– Good introduction to many database concepts
– Can define and traverse relationships between tables
What is a database?
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications. – MySQL Reference Manual
– Is a set of tables.
– 1 application => 1 database
– Access permissions are granted at database level
What is a table?
A table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows. – Table (database)
– Set of columns and rows
– Represent a single concept (a noun)
– Examples: products, customers, orders
What is a column?
In the context of a relational database, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed. – Column (database)
– Set of data of a single type
– Column have types: strings, integers, etc
– Examples: first_name, last_name, email, password
What is a row?
A row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields. – Row (database)
– Single Record of data
– Examples: “Adrian”, “Prieto”, “email@example.com”, “secret”
What is a field?
A field is a data structure for a single piece of data.
– Intersection of a column and a row
– Example: first_name: “Adrian”
What is an index?
Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
– Data structure on a table to increase look up speed
– Example: The index at the back of a book
What is a foreign key?
A foreign key is a column (or columns) that references a column (most often the primary key) of another table. The purpose of the foreign key is to ensure referential integrity of the data. – Keydata
– Table column whose values reference rows in another table
– The foundation of relational databases
What is CRUD?
CRUD is an acronym for the four basic types of SQL commands: Create , Read , Update , Delete . Most applications have some kind of CRUD functionality, and we can assume that every programmer had to deal with CRUD at some point. A CRUD application is one that uses forms to get data into and out of a database. – jboss.org
How to use MySQL
To check your MySQL version, just type in your terminal:
To start using MySQL you need to log in with a user, root is the default user and most powerful one in MySQL, just type in your terminal:
mysql -u root -p
Enter your password and press enter:
MySQL Most Used Commands
quit to exit.
SHOW DATABASES; to see databases.
CREATE DATABASE db_name; to create a database.
USE db_name; to use a database.
DROP DATABASE db_name; to get rid of a database.
Creating users & assigning privileges
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON db_name.* --> TO 'username'@'localhost' --> IDENTIFIED BY 'password';
You can see grants by typing:
mysql> SHOW GRANT FOR 'username'@'localhost';
Logging in and using a database
$ mysql -u username -p db_name
Lists the non-temporary tables in a given database.
CREATE TABLE statement to specify the layout of your table.
CREATE TABLE table_name ( column_name1 definition, column_name2 definition, column_name3 definition, options );
Returns a table containing the column names, types, etc
SHOW COLUMNS FROM table_name;
To remove a table from a MySQL database, and remove all of its data
DROP TABLE table_name;
CRUD & MySQL
SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries.
SELECT * FROM table WHERE column1 = 'some_text' ORDER BY column1 ASC;
INSERT INTO statement is used to insert new records in a table.
INSERT INTO table (column1, column2, column3) VALUES (val1, val2, val3);
UPDATE statement is used to update existing records in a table.
UPDATE table SET column1 = 'some_text' WHERE id = 1;
DELETE statement is used to delete a single record or multiple records from a table.
DELETE FROM table WHERE id = 1;
This covers the basics of MySQL, go ahead and create a database, some tables and manipulate data, until you get familiar with the commands.
Here an example: