MySQL 1st Notes.

Helpful Links:

  • MySQL Documentation: Link
  • Direct Link to MySQL Docs String Functions: Link
  • TutorialsPoint MySQL: Link

Now lest dive in MySQL Sea.


MySQL in “Cloud 9”

In this class we will be using ” Cloud-9 ” and latter we will be installing in Linux.

Note: read the following Article-Link, there explains that some of the commands that you will use in “Cloud 9” when using MySQL, are not real MySQL command but some commands that they program in the dev-environment just remember that when using MySQL in your Local-Machine few commands could be different.

Cloud-9: MySQL Starting Commands:

 

MySQL Commands:

Tables:

  • Columns = ( headers );
  • Rows = ( the actual data );

 

Data Types: for a complete list see Docs, but for this moment we will use only two;

  • INT: For whole numbers no decimals.
  • VARCHAR: For strings with max 255 characterers.

 

How to create a TABLE:

Table Commands

In the following example I create a new table call players and then delete it.

Inserting data in to the Tables:

For this example I recreated the TABLE players now lest put data inside, the command to do so is:

Now this is real example:

You can insert and delete by BULK, example in our players table.

if you ever get a feed back with the word “warning” you can request the computer to show you the warnings with:  $ SHOW WARNINGS;

 

What’s Up with NULL in MySQL?

I will add a player to the table but I will not specify the Goals.

This happen because the TABLE in the NULL header, each row has “YES” as a valid default value if value is not provided at the moment of the Data Entry.

Ok good to know, so how can I prevent it from happen, well when creating the table we need to specify that we do NOT ALLOW NULL to be the DEFAULT VALUE, in the next example we create a new Table but we state NOT NULL ALLOW.

NOTE: NOT NULL will make it a require value and can not be left empty.

Same table with data and pushing more incomplete rows.

In the last section about null basically we were telling the computer if we allow it to use the Null if the default value is === NULL. in the next section we will actually tell the computer what is the DEFAULT VALUES THAT WE WANT, by default the TABLE have NULL, but you can change that at the moment of the TABLE creation.

SET DEFAULT VALUES:

In the last example if we did not specify the name or age we have some default values, but nothing is stopping us for deliberate put a NULL with something like:

INSERT INTO cats (name, age) VALUES ( ‘Candy’, NULL);

NOT NULL

If you really do not want NULL  you can specify it with the following syntax at the moment of the TABLE CREATION.

 

The KEYS ?

In the TABLE header KEY is empty. this is an space for the “PRIMARY KEY”

PRIMARY KEY AUTO_INCREMENT

If you do not want to provide the PRIMARY KEY, because is hard to keep track of what was the last, you can tell the computer to doit automatically. and you DO NOT need to specify it when INSERTING INTO the TABLE.

One more example:

 

 

C-RUD

This acronym stands for: Create, Read, Update, Delete.

We already create data with the command: INSER INTO <db_name> (a, b) VALUES (a, b);

Now here are some examples in how to to review and search for data:

SELECT

A quick example:

But this is not very specific, to do that we need new tools;

the WHERE clause

In the following we want to display Name and Age of the cats with breed, “Tabby”

Here we want to know the ID & the Age for those cats that the cat_id is the same as it’s age or in other words, cat_id = name;

fro a detail of the commands:

Aliases

Easier to read results, this means that if the Header of the Table data is not descriptive or you just want a different Header, here is a simple way to do it:

in the following example I will request all the data of the table but the Header “breed“, I want to change it for “Raza

Yo can also add ‘ ‘ to add spaces on the headers:

UPDATE

How to update data that is already in a table.

But if you want to be more specific to target just one line:

DELETE

Remember once something is deleted is gone and NO WAY to get it BACK:

Syntax Example:

Use of example:

 

Another example:

If you want to delete all the data in the cats table but keep the table:

STRING FUNCTIONS

The first thing the will learn to do is how to run the SQL COMMANDS from a source file, so we do not have to type all the commands in the terminal, in order to do that we need to:

Create a new file with extension “file_name.sql

Write the SQL code on the file, and save it.

Start the MySQL interactive Shell.

USE the database where you want to create or manipulate TABLE.

Example Code:

When running the scripts, the script must be on the same directory than the DATA-BASE or you need to stipulate the phat of the script.

BOOKS TABLE EXERCISE

Will will start by creating a new data base call: “book_shop“:

Now I will create a file named: “insert-1.sql” in a folder named: “scripts” with the data for the table:

Then in the console we execute the file:

<———————————->

String Functions

—>  CONCAT

Example:

Here we can also use the command “AS” to give better names:

With string function you can use more than 1 at the time

—>  CONCAT_WS

Here you provide a string separator in case that you wan to concatenate may elements so you do not need to type the separator multiple times:

 

 

 

.



Copyright 2017. All rights reserved.

Posted June 25, 2017 by Edmundo in category "Database