Up and Running with MySQL

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

Main features:

– 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

Main features:

– 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)

Main features:

– 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)

Main features:

– 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)

Main features:

– Single Record of data
Examples: “Adrian”, “Prieto”, “[email protected]”, “secret”

What is a field?

A field is a data structure for a single piece of data.

Main features:

– 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

Main features:

– 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:

mysql --version

User

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

exit or 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

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant Privileges

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

Database Manipulation

SHOW TABLES

Lists the non-temporary tables in a given database.

CREATE TABLE

Use a CREATE TABLE statement to specify the layout of your table.

CREATE TABLE table_name (
  column_name1 definition,
  column_name2 definition,
  column_name3 definition,
  options
);

SHOW COLUMNS

Returns a table containing the column names, types, etc

SHOW COLUMNS FROM table_name;

DROP TABLE

To remove a table from a MySQL database, and remove all of its data

DROP TABLE table_name;

 

CRUD & MySQL

SELECT (read)

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 (create)

INSERT INTO statement is used to insert new records in a table.

INSERT INTO table (column1, column2, column3) VALUES (val1, val2, val3);

UPDATE (update)

The UPDATE statement is used to update existing records in a table.

UPDATE table SET column1 = 'some_text' WHERE id = 1;

DELETE (delete)

The 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:

Have fun!

Resources

You may also like