MySQL Workbench Tutorial: SQL Database Creation and Management

hey what's going on everybody it's your bro hope you're doing well and in this video I'm going to show you how we can

start writing SQL using the MySQL workbench so sit back relax and enjoy the show

all right let's get started everybody SQL it's an acronym meaning structured query language SQL is used to create

retrieve update and delete data from a database suppose we own a business like the Krusty Krab from SpongeBob if we had

to manually track all of the transactions by pen and paper well that would be a lot of extra work if we had a

database we could keep track of these records electronically and save ourselves a lot of trouble right there's

two types of databases I'll discuss relational and non-relational a table in a relational database it resembles an

Excel spreadsheet there's rows and columns tables within a relational database can form well relationships

with one another and that is done by this concept of keys which I'll explain in a further topic then there's

non-relational databases that's where our data is organized in any format but a table this could include Json files

key value pairs graph data structures entities of that nature to utilize data in a relational database we would use

SQL then with a non-relational database we would use a different language named nosql meaning not only SQL but since

this is an SQL series we will be working with SQL and relational databases not non-relational databases to write SQL

statements we would need the help of a special piece of software known as a database management system people

shorten this to Simply dbms it's a workspace for us to write SQL statements and generally just work

with our database it'll make our lives easier there are different dbms systems you can use one of which is MySQL

Microsoft SQL Server Oracle and postgres SQL but there's still many more out there each of these database Management

Systems all use SQL but there's subtle nuances between these syntax of each database management system if you're

familiar with one database management system transitioning to another will take little to no effort at all in this

series we will be working with the mySQL database management system and I'll show you how to download that for both

Windows and Mac OS hey everybody in this topic I'm going to explain how we can download MySQL using

the Windows operating system first of all head to this website mysql.com then go to the downloads tab

scroll down look for MySQL Community downloads we'll click on that click on MySQL installer for Windows

make sure that we have our Windows operating system selected then download the first installer

no thanks just start my download we will open this download once it's complete

there are various setup types depending on what packages you need for this series all we need is the server and the

workbench we'll select the custom radio button click next

we will need the most recent MySQL server add that to products to be installed

open applications go to mySQL workbench we will add the most recent workbench we can close out of that there is a

shell if you're interested in using that but I will be sticking with the workbench in this series once we have

our server and our workbench I will click next then execute

looks like there's an error downloading the workbench I'm going to try again and it worked this time for some reason

let's click next execute give it some time once the installation

status for the server and the workbench is complete we can click on next next

I'll keep these default configurations next use strong password encryption for

authentication let's click next here we're going to set the root password to access the server think of

some password you would like I'm just going to set mine to be password I'll keep it simple

yeah of course the password strength is weak you can add user accounts but that'll be outside the scope of this

series click next you can start the MySQL server at system

startup if you would like I'll keep that on click next

then execute then finish then next

yeah we might as well start the MySQL workbench after setup if this window doesn't pop up you can

easily just search for it just look for MySQL workbench now we should have a local instance

we can click on this to access our server let's pretend that this wasn't here I'm going to right click delete

connection if you need to set up a connection hit this plus button

we'll need a connection name I'll name this Local Host connection method should be standard TCP IP

the host name is 127.0.0.1 at Port 3306 then press ok

so now that we have our connection set up we can click on it type in the password you set for the server mine was

simply password you could save the password if you want I might as well

then okay and here we are within the MySQL workbench

all right everybody in this topic I'm going to explain how we can download MySQL using the Mac operating system

first head to this URL mysql.com we will go to the downloads tab scroll down look for MySQL Community

downloads we'll need both the server and the workbench but let's start with the

server click on this file Mac OS 12 DMG archive click the download button

click on this link no thanks just start my download when this DMG archive is finished

downloading we can double click on it just give it a second double click on the DMG archive

allow click continue you can read the license agreement I'm

going to pretend I did hit continue install type in your computer's password if this

prompt comes up use strong password encryption hit next then we'll need a password for our

server type in whatever password you would like to keep it simple for this lesson I'm just going to set my password

to be well password by checking this box MySQL server will start once the installation is complete

you might as well keep this checked but for demonstration purposes I'm going to show you how to start the server

manually then close I'll go ahead and move the installer to

the trash bin we have the server downloaded next we need to download the workbench

again head to mysql.com go to downloads scroll down to mySQL Community downloads

click on MySQL workbench then hit the blue download button no thanks just start my download

then we can double click on this DMG archive when it's finished downloading so double click

drag and drop the workbench icon into your applications folder before we access the workbench let's be

sure that the MySQL server is running click on the top left Apple logo go to system preferences at the bottom

left corner look for MySQL click on it then start MySQL server if it's not

started already and you can check this checkbox so that your computer starts up with it running

all right you may need to type in your password the server is now running

to run the workbench we can go to finder then applications look for the MySQL workbench icon

double click on it and here we are within the MySQL workbench

to access the server you can click on this local instance connection then type in the password that you originally set

for the server but if you're missing this connection you can hit the plus button

then create a new connection name I'll name this Local Host use standard tcpip for the connection

method hostname should be 127.0.0.1 and Port 3306 then press ok

but I already have my connection set up click on your connection type in the password you set for the server

then press OK and here we are within MySQL workbench hey everybody in this topic I'm going to

show you how we can create alter and drop a database what is a database exactly think of it as a folder it acts

as a container tables on the other hand would be the files found within the folder in this topic I'm going to show

you how we can create the database itself but we won't be working with any tables until the next topic we'll need

to begin by heading to our query window which is this large window right here we're going to write a statement to

create a database we will type create there is no case sensitivity in MySQL these keywords such as create they can

be all uppercase just the first letter could be capital or you could do all lowercase

with the keywords I like to make them all uppercase but that's just me create database

and then we'll need a unique database name what about my DB then it's important with all SQL statements to end

each statement with a semicolon it's like the period at the end of a sentence that's how we know that the statement is

complete so you'll need these three words create database then a database name we just named our database mydb hit

this lightning bolt button to execute this statement and in my output window unfortunately I

can't increase the font size but I can zoom in this action was successful we have created database mydb if you

head to your schemas tab then hit the refresh button you should see a database in here named mydb

there's also a database for sys sys is the internal database that MySQL uses we'll want to be sure that we're using

the database that we just created to use a database we can either right click on the database

then click on set as default schema or you could use a statement use

then the name of the database my DB semicolon execute the statement

we are now using my database to drop a database type drop database

then the name of the database and our database is gone unfortunately we do need a database for this series so

let's go ahead and recreate our database create database mydb refresh

then just to be sure we're using this database we can either use that keyword use

or you can right click on the database and set as default schema all right so we can create use and drop

a database how about alter there's two features for beginners I'll mention setting a database to read only the

other is enabling encryption let's set our database to be read only type alter database

the name of the database read only equals one this statement would

make our database read only if a database is in read-only mode we can't make any modifications to it but

we can still access the data Within let's attempt to drop our database drop

database my DB then we have a red X right here that means we could not complete this action

drop database mydb schema my DB is in read-only mode to disable read-only mode you would set

read only equal to zero now we would be able to drop this database alright everybody that is how

to create use drop and alter a database think of a database as just a folder a folder can hold files the files will be

the tables that we'll create which will store within the database and in the next topic I'll show you how we can

create some tables hey welcome back everybody in this video I'm going to show you how we can make

some tables in MySQL a table in a relational database they consist of rows and columns kind of like an Excel

spreadsheet in this topic we're going to create the table and the columns but we'll be populating the rows in the next

video to create a table you'll type create table than the name of the table I'll

create a table named employees then add a set of parentheses semicolon at the end within the set of parentheses

we will list the columns for employees let's have an employee ID comma

each column is separated with a comma then a first name last name

how about hourly pay that'd be good higher date which date were they hired there's one thing that's missing after

each column we need to set the data type of what we're storing within each column exactly is the data going to be whole

integers decimals text a timestamp those are data types an employee ID that could be a whole integer the data type will be

int add int after the column name a first name that's a series of characters the data type for some text

would be VAR Char then within parentheses the maximum amount of characters I think 50 characters is

enough for a first name last name varchar

50 is good hourly pay maybe our hourly pay is in dollars and cents we'll need a decimal

portion there is a data type that is decimal add a set of parentheses we'll add the maximum amount of digits

for our decimal I think 5 should be good then a Precision two for two decimal

places the reason that I set the maximum digits of my hourly pay to be five is that I

don't anticipate anybody's hourly pay being over 999.99 per hour that's pretty absurd I

think five is enough then we have higher date there is a data type which is date if

you need to work with dates otherwise there's date time if you need to include the time just the date is fine let's

execute the script we have created our table employees if I were to refresh my schemas window

underneath tables we have our employees table now I'll show you how we can select a

table if you need to select your table you can type select

asterisk from the name of the table employees let's take a look

here's our table so far there's no rows but there are columns we have employee ID first name last name hourly pay and

hire date if you need to rename a table you could type rename

table the original name employees two whatever the new name is

perhaps workers let's try that rename table employees to workers was successful if I refresh my schemas

window the table employees is now known as workers but I think that's stupid let's

go back and change it rename table workers to employees to drop a table I discussed this earlier

you would type drop table the name of the table I don't want to drop this so I'm not going to execute the statement

but that's how you would drop a table drop table the name of the table if you need to alter a table

there is the alter keyword let's add a phone number alter

table employees I'm going to write this next part of the

statement on a new line what do we want to add uh what about a phone number

add phone number

then we'll need to list the data type of this column varchar is good

Max size of 15 characters then we will end this statement execute the script all right we have

altered our table let's take a look at our table select asterisk asterisk means all I don't know

if I explained that select all from employees we have employee ID first name last name

hourly pay higher date then our column phone number let's rename phone number and change it

to something else we will again use the altar keyword alter table

employees rename column

phone number to let's say email okay then let's take a look again select

all from employees yeah employee ID first name last name hourly pay higher date then email

however the data type has not changed for email I'm going to refresh my schemas then

underneath information underneath employees our email column has a max size of 15 characters let's change that

alter table employees

modify column email then the new data type varchar

maybe a hundred characters for an email let's execute this statement refresh our schemas yeah now our email

column has a size of 100 characters maybe we need to change the position of our email column if you need to move

columns around this is how you can do so let's move our email column so it's after our last name column

to do that we would type alter table the name of the table modify the name of the column to be

moved email in this case then the data type

this email column has a data type of varchar100 then after then the name of the column

we would like our email column to follow I need my email column to come after my last name column I will type after

last name then afterwards I might as well just display everything select all from

employees you can write more than one SQL statement here we are employee ID first name last

name email hourly pay higher date if you're modifying a column and need it to be first

you could just say first email employee ID first name last name

hourly pay higher date you can use the after keyword then a column or first if you need the column to be first

now to drop a column you would type alter table employees drop

column the name of the column to be dropped email

our email column is no longer there all right everybody that's how we can create a table and work with the columns in the

next video we're either going to talk about data types or learn about inserting rows into our tables I haven't

decided yet but we'll probably cover one of those two hey everybody in this topic I'm going to

show you how we can insert rows into a table we have a table named employees I will select everything from my table

employees here are the columns we have an employee ID first name last name hourly pay and

higher date to insert a row into a table we would type insert into the name of the table

followed by values parentheses semicolon between this set of parentheses we will add all of the data for a row we will

follow this order beginning with employee ID first name last name so on and so forth but we do have to pay

attention to the data types too each piece of data will be separated with a comma let's begin with an employee ID

the first employee will be Mr Krabs he's the first employee I'll give him an employee ID of one then we'll need a

first name I'll put that within quotes because the data type of our first name is varchar basically speaking that's

text Mr crab's first name is Eugene according to SpongeBob lore

then we have a last name last name crabs hourly pay this is a decimal

Mr Krabs hourly pay will be 25.50 I'm just making up numbers here higher date here's the format for a date

with anesthetic quotes we will list the year followed by the month then the day I'll set Mr Krabs hired date to be

January 2nd 2023 now we can execute these statements yeah here we go here's the first row

employee ID one first name Eugene last name crabs hourly pay 2550 higher date January 2nd 2023 it

can be somewhat tedious to manually insert each row one statement at a time let's insert multiple rows at once to do

that after my set of parentheses I will add another set of parentheses each separated with a comma depending on

how many rows I would like to enter so I have four employees I would like to enter

I will add four sets of parentheses each separated with the combo and let me just do some formatting to

make this look pretty each set of parentheses corresponds to a new row so let's enter all the data that

we'll need for this table employee ID number two will be Squidward

tentacles Squidward's hourly pay will be maybe a solid fifteen dollars

Squidward's higher date the year 2023 January I think January 3rd is good

all right so that is the next row let's work on employee number three employee number three will be a

SpongeBob last name will be Square Pants

SpongeBob's hourly pay will be 12.50 SpongeBob's higher date is the year 2023 January 4th

employee for will be Patrick star

Patrick's hourly pay will be 12.50 as well higher day

2023 January 5th okay last employee

employee ID number five first name Sandy last name

cheeks Sandy's hourly pay will be 17.25 she'll be an assistant manager

Sandy's start date will be the year 2023 January 6th we can execute this statement to insert multiple rows all at

once yep here's our table we have five rows each corresponding to

a different employee we have Mr Krabs Squidward SpongeBob Patrick and Sandy I'm going to demonstrate what happens

when we attempt to insert a row with missing data I will insert employee number six

their first name is Sheldon last name Plankton but we will not insert an hourly rate of pay or a higher

date here's what will happen so we have an error column count doesn't match the value at

count at Row one we're missing data for our hourly pay and higher date you could insert just select columns that is done

by adding after the name of the table add a set of parentheses then name the columns you would like to insert data

into all we're adding is an employee ID first name

then last name okay now we've inserted a new employee but there is data missing and that's

okay we plan on hiring Mr Plankton but we have not yet negotiated an hourly pay or start date we still need to run a

background check on him but we want to add him to the system at least so if you ever need to insert data into a row but

you need to Omit certain columns you can simply just list the name of the columns you would like to sort data into

all right everybody that is how to insert rows into a table in MySQL hey everybody in this video I'm going to

show you how we can select data from a table to query all of the data from the table we would type select asterisk

meaning all from the name of the table in my case we have table employees this statement select all from employees will

give me all columns and all rows but sometimes you may not want all of the data here's a scenario your boss gives

you a task of retrieving the full name of every employee well we don't need employee ID hourly pay or higher date

for that right we just need a list of the first and last names of our employees table in place of the asterisk

meaning all we can select specific columns such as first name and last name we have all of the first and last names

from our employees table you can change up the order of the columns too this time I would like last name

then first name with our query this returned all of the last and first names of all the

employees point being you can select specific columns depending on what you're looking for or everything with

the asterisk there is a clause known as the where Clause if we're looking for something specific

let's add where then what are we looking for exactly we can write some criteria let's select all

from employees where employee ID equals one then end your statement with a semicolon

this will give us a specific employee the employee that has an ID of one if I set employee ID to B2 that would give us

Squidward three would be SpongeBob 4. Patrick I think you get the idea here's an

exercise let's find all the data from employees where the first name equals SpongeBob

where first name equals Sponge Bob this query will return employee ID3 the

first name last name hourly pay and higher date here's another example let's find all employees that have an hourly

pay greater than or equal to 15. our criteria will be where hourly

pay is greater than we'll use the greater than operator

or equal to 15. this returns three employees

Mr Krabs Squidward and Sandy all three of them have an hourly pay of 15 per hour or greater it is possible

your query can return multiple results let's find anybody that has a higher date

less than or equal to perhaps the year 2023 January 3rd

this query returns two results Eugene Krabs and Squidward Tentacles they both match this criteria and in

this case we use the less than or equals to operator another operator you should know about is the not comparison

operator it's an exclamation point and an equal sign we will check if something is not equal

let's find where employee ID does not equal one who are all the employees that have an

ID not equal to one that gives a Squidward SpongeBob Patrick Sandy and Plankton

on the other hand if I set this to one that would give us Mr Krabs that is the not comparison operator if you need to

check if something is not equal in this next example let's pretend that we're human resources we need to find

any employees that do not have a higher date and then begin the paperwork for them currently Plankton's hourly pay and

higher date are both set to null null meaning no value in place of saying you know higher date

equals null in place of using the equal sign we would type is select all from employees where higher date is null

that would return Sheldon Plankton because his higher date is null not equals null that doesn't work

it would be is null you could even say is not null that would return every employee that

does have a higher date all right everybody so that is how to query data from a table select whatever you're

looking for it can be everything or specific columns from a table of your choosing where the

results equal some criteria that you set it really depends on what you're looking for and that is how to query data from a

table hey everybody in this video I'm going to show you how we can update and delete

data from a table in my example we have a table of employees however Sheldon Plankton is

missing some information and hourly pay and a higher date let's update those fields to update some data in a table we

would use the update keyword the name of the table employees in my example

then set which column would we like to interact with first let's interact with hourly pay set

hourly pay let's pay Plankton 10 and 25 cents per

hour we should probably add a where Clause I need to specifically select Plankton where

let's select his employee ID where employee ID equals six then semicolon 10 the statement

you could also select Plankton by his first name or last name as well when I execute the script

Plankton's hourly pay is now 10.25 cents per hour to update multiple columns you can

change more than one field at once after your first change you can add a comma then change another field

let's change Plankton's higher date to be the following higher date equals 10

23-0 Dash zero seven I'll make his hourly pay 10.50 I'm

feeling generous here we are Plankton's hourly pay is now ten dollars fifty cents his higher date

is January 7th 2023. to set a field to null meaning no value you would just say equals null for

example let's take Plankton's higher date set the higher date equal to null

Plankton's higher date is now null so maybe we're gonna fire him or something

in this next example I'll give you a demonstration but you don't want to follow along to update all of the rows

within a column you would exclude the where clause if I were to set hourly pay equal to

10.25 that would affect all of the rows after executing this statement the hourly pay for everybody is now

10.25. that's an example of how you could set a column to be one consistent value

to delete a row from a table you would type delete from the name of the table

now don't write just the statement it will delete all rows in your table here's an example do not do this I will

delete from employees there my whole table is gone do not do this be sure to add a where Clause when

deleting from a table otherwise this will delete all your rows where employee ID

equals six yep and Plankton is no longer there all right everybody that is a super

quick video on how to update and delete data from a table hey everybody in this topic I'm going to

explain Auto commit commit and rollback Auto commit is a mode by default Auto commit is set to on whenever you execute

a transaction within MySQL that transaction is saved what if we were to make a transaction and we need to undo

that transaction for example what if we accidentally delete all of the rows of this table don't do what I do but I'll

give you a demonstration I accidentally type in delete from employees but I forget to add a where Clause well all my

rows are now gone how do I undo these changes well what we're going to do is set auto commit

to equal off with this setting set to off our transactions will not save automatically

we would need to manually save each transaction it creates a save point so to say

I'm going to create a save point by typing commit then execute now I'm going to select my

table select all from the name of my table then I'm going to go ahead and delete

all the rows delete from employees oh shoot but I forgot to add a where

Clause my whole table is gone now luckily we created a safe point with that commit statement

to undo these changes I can execute the roll back statement this will restore my current transaction

back to the previous save point where we used commit now if I were to select all from the name of my table

our table is back to what it previously was and again to save any changes

let's say I delete my whole table again delete from employees

if I want to save this change I would commit then again I will select my table

and that last change is now saved my whole table is gone so yeah that is auto commit commit and

roll back by default Auto commit is set to be on any transactions after executing are saved if that mode is set

to off you can commit any changes manually to undo any changes use that rollback statement so yeah that is auto

commit commit and roll back in MySQL hey everybody in this topic I have a super quick video on getting the current

date and time in MySQL let's create a temporary table at the end of this topic we'll delete it

create table I'll name this table test we will have three columns the current date I'll name the column my

date the data type is date my time the data type is time then we'll combine both

my date time the data type is date time let's create this table it was created

successfully then I will select all from our table test we have three columns

a date a time and a date time which combines both so how do we get the current date maybe we need to create a

timestamp of when some event happened maybe a higher date for our employees so I'm going to insert

into the name of my table test some values for the current date there is a built-in

function current underscore date then add a set of parentheses this function will return

the current date for the current time there is a current time function add a set of parentheses

to the end of that function for the date time you could just say now the net a set of parentheses

after inserting this row let's select all from our table test here's the current date of me filming

this video October 21st 2022 the time is seven in the morning the current date time is October 21st

2022 7 in the morning a date time would probably be good for some sort of transaction you need to record for the

time being I'm going to set the current time to be null as well as the date time to be null now if you were to take your

current date then add plus one this part of my statement would refer to tomorrow

October 22nd if you were to take your current date minus one that would technically be yesterday October 20th

you could do something similar with your time and date time you can add or subtract seconds but I think that's

pretty self-explanatory we don't need this test table anymore so we can delete it

drop table test and it's gone all right everybody so that was a super

quick topic but I thought I needed to cover it that is how to get the current date time and date time in MySQL

hey everybody I have a quick video on the unique constraint the unique constraint ensures that all values in a

column are all different that's basically it we can add this constraint when we create a table or after let's

create a new table create table

let's create a table of products what products do we offer we'll list the columns

let's add a product ID the data type will be int product name the data type will be

varchar maybe 25 characters then a price the data type will be decimal our price will have a Max size of four

digits and a Precision of two decimal places to add the unique constraint select a

column after the data type add the keyword unique and that's all you have to do with this

keyword unique we can't insert any product names that are the same they all have to be well unique let's create this

table let's say that you forget the unique keyword then you create the table what

you could do instead is type alter table the name of the table

products which we just created add constraint

unique the name of the column within parentheses

product name if you forget to add a unique constraint to a column after you already create the

table this is what you can write however when I run this I do have a warning I already have a

unique constraint on that column but yeah that's how you would add a unique constraint to a column in a table you

have already created let's select our table select all from products

we have a product ID column product name and a price let's insert all of our values

insert into the name of our table which is products values will insert maybe four rows

we'll need a product ID name then a price for a product name we have a hamburger and the product ID I'm just

making up a number is 100. the name is hamburger the price will be 3.99

fries will be our next item then we have prize the price will be 189.

102 will be soda the price is one dollar even

103 will be ice cream the price will be 149. Let's test the unique constraint that we have added to

the product name column suppose that I forget that I have fries on the menu already

and I will add another row 104 frize 189 actually let me change the price to maybe something different like

175. here's what happens when I attempt to add two values that are the same duplicate entry fries for Key Products

dot product name MySQL is telling us that we have a duplicate under the product name column we have fries twice

since we added the unique constraint all values in this column must be different if I were to remove the last column

where we have that duplicate well then this would run just fine now let's select everything

from products yeah there we go here are the products we have a hamburger fries soda and ice

cream all on the menu so yeah that's the unique constraint you can add that constraint when you create a table or

after whatever column has that unique constraint all the values need to be different so yeah that's the unique

constraint in MySQL hey everybody in this topic I'm going to explain the not null constraint the

notch null constraint can be added to a column when you create a table whenever we enter a new row the value within that

column can't be null for example I have table products we have a hamburger fries soda ice cream if I were to recreate

this table I would type something like this create table

products I would list the columns we have product ID the data type is integer

product name the data type is varchar I believe I set this to be 25 last time then price

that is a decimal we gave this column a Max size of four digits and a Precision of two any column

that I do not want to have null values I will simply just add not no this is a constraint now I've already created this

table so I'm not going to execute the statement but that's how you would create a table that has the notch null

constraint after the column name you just type not null to add the not null constraint to a

table that already exists you would instead type alter table the name of the table

modify the name of the column the data type of the column

in this case decimal four comma two then not null

it's a little bit different than the unique constraint in the last topic so let's execute the statement

that seemed to work let's add a new item to the menu I will select everything from my products table

select from products let's add a new item to the menu and we will test that not null constraint

insert into products values we have 104 product ID 104 will be

how about a cookie I'm not going to set a price I will say null for the price maybe I'm not decided

on what the price is yet after executing the statement well we have an error column price cannot be null

since we said that not null constraint on the product price we can't enter a null value we could set this to be zero

that's acceptable but it can't be null so yeah everybody that's the not null constraint it's a useful constraint to

verify input if there's any column that you don't want to have any null values just add that constraint and yeah that

is the not null constraint in MySQL hey everybody in this topic I'm going to explain the check constraint the check

constraint is used to limit what values can be placed in a column for example I live in the United States depending on

which state you live in there is a minimum hourly wage that employers have to pay in this example let's set an

hourly pay to our employees table every employee needs to be paid at least the minimum wage in that region and we can

do that with the check constraint we have our table of employees if I were to recreate this table it would look like

this I think that was pretty close to add a check to a column at the end of our

table this is what we would write check then within parentheses what sort of

condition do we want let's check to see if our hourly pay column is greater than or equal to

maybe ten dollars per hour that will be the minimum wage in this region this would work if we were to create this

table however it's typically a good idea to give your check a name just in case you need to drop it later you can easily

identify it so precede check with constraint then a constraint name what about

chk meaning check underscore hourly pay

this check will be known as check hourly pay so we can identify it then we can drop it or make any changes to it if we

need to I do already have an employees table so I'm not going to execute this statement but to add a check constraint

to a table you would just add a row like this to the bottom if you instead need to add a check constraint to a table

that already exists this would be the syntax alter table the name of the table

add constraint then we need a unique name for this constraint

check hourly pay that's fine then the check within parentheses

hourly pay is greater than or equal to 10. ten dollars per hour

okay let's run this yeah that appears to have worked I'm going to select all from employees

let's take a look at our table then we will attempt to add a new employee

insert into employees values

we need an employee ID first name last name hourly pay then a higher date employee ID will be six

first name Sheldon plankton

for hourly pay let's attempt to pay Plankton maybe five dollars per hour then a higher date

[Music] 2023-01-07 let's see if that check constraint kicks in we're paying

Plankton under ten dollars per hour yeah it doesn't appear that we can do so check constraint check hourly pay is

violated now let's pay Plankton ten dollars per hour yeah that worked just fine if you need

to delete a check you would type alter table the name of the table drop check then the name that you gave

that check in my case it was check hourly pay and that check has been dropped yeah

everybody that is the check constraint it's used to limit what values can be placed in a column it's another useful

method for checking input and that is the check constraint in MySQL hey everybody in this topic I'm going to

explain the default constraint when inserting a new row if we do not specify a value for a column by default we can

add some value that we set here's an example let's select all from our products table select all from products

in an earlier example we have a table of products a few items on the menu for sale are hamburgers fries soda and ice

cream let's add a few items on the menu that would be free this could include napkins straws forks and spoons

something you would commonly see at a fast food restaurant if we're not using the default constraint we would have to

enter in those prices manually so let's insert into our table products our values

product ID 104 will be a straw the price will be zero dollars and zero cents

let's do this all together we have 105. 106

107. product number 105 will be a napkin the napkin is zero dollar zero cents

106 is a plastic fork again zero dollars and zero cents 107 is a plastic spoon

the price is zero dollars and zero cents then I will select all from my products table

here's our new products table we have our four items that actually cost something then our free items which are

included with whatever we buy in place of explicitly stating a price one way in which we can make our lives easier if

we're adding a bunch of free items to the menu we could set a default constraint where if we don't explicitly

set a price the price will default to be zero it will be free so I'm going to undo everything that we just did

delete from products where product ID is greater than or equal to 104.

and then we can execute that statement if we were recreating this table with the default constraint we would type

create table the name of the table products in this case add our columns we have a product ID

the data type is integer we have a product name

the data type is VAR chart and I forgot what the size was looks like 25.

than a price which is a decimal Max digit size of four Precision of two

now let's use the default constraint after the column you would like to add that constraint to type default then

some value I will set the default value to be zero dollars and zero cents or you could just say zero too that'd be fine

you would just add this constraint when creating a new table however I do already have a products table so this

technically wouldn't work because that table already exists now to alter a table to include that constraint you

would instead type alter table the name of the table alter the name of the column price

set default constraint then the value so zero

and that appears to have worked let's select our products table select all from

products then we will insert some new rows insert

into products than our values

so we had four rows 104. that was a straw

105 was a napkin 106. was a fork

107. is a spoon then finished the statement with a semicolon so since we set that

default constraint we don't need to explicitly list the price but we are missing one thing though

when I execute this statement the column count doesn't match so when we insert into products

we will list what we're inserting explicitly the product ID and the product name after our table

name within parentheses type product ID then product name now this should work

yeah and here's our products table with our four inserts all of the prices were set to zero and we did not need to

explicitly state that this time if you don't specify a value you'll use whatever the default is here's another

example I think you'll like this one let's say we have a table of transactions after inserting a new

transaction we will insert a timestamp of when that transaction took place and that time stamp will be the default

create table transactions at the end of this topic we will delete

this table so don't get too attached to it we will have three columns

transaction ID which will be of the data type integer

a transaction amount let's just say amount the data type will be decimal

five digits Precision of two is good then a transaction date

the data type will be date actually let's make this date time I'd like to include the time of when this

transaction happened now for the default constraint after the date time let's add default then the Now function we don't

need to explicitly add the date and time that will be done automatically which is kind of cool

so I'm going to create this table let's select all from transactions if we're going to insert some values

we'll need at least a transaction ID and amount the transaction date will be included automatically

so let's insert into transactions some values let's do one at a time

the first transaction of the day will have a transaction ID of one I suppose a customer came in and bought a hamburger

and a soda for a total of 4.99 then we do need to list the transaction ID then the amount

after our table name add transaction ID then amount so let's run this

yeah cool so there's our transaction ID this was order number one the total amount was 4.99 and we do have a time

stamp of when this transaction occurred so the second order of the day will be for a total of 2.89 and that

happened at this time then the third order of the day will have a total price of eight dollars

37 cents yep there we are so we don't need this table anymore I'm going to drop table

transactions we will recreate this table in the future but I'd rather start fresh all right everybody that is the default

constraint when you create a table or alter a table you can set a default value of some value you specify then

whenever you insert a row that value will be included automatically which is pretty useful but yeah that is the

default constraint in MySQL hey everybody in this topic I'm going to explain the primary key constraint in

MySQL the primary key constraint can be applied to a column where each value in that column must both be unique and not

null it's typically used as a unique identifier for example I live in the United States each citizen within the

United States has a unique social security number there is a strong possibility that two citizens in the

United States share the same first name and last name John Smith for example if we're trying to find John Smith well

which one are we referring to we could instead search for a citizen with a unique social security number then we

know for sure we have the right person that's kind of the same concept with the primary key also a table can only have

one primary key constraint here's an example let's create a temporary table of transactions create table

transactions there will only be two columns a transaction

ID the data type is int I will set the transaction ID to be the primary key there can be no duplicate transaction

IDs with the same value and none of them can be null then we will also have an amount how much was each transaction for

the data type will be decimal Max size of five digits Precision of two and that is good enough

then I will select all from our table transactions here's our table now to add a primary

key constraint to a table that already exists this is the syntax alter table the name of your table

add constraint the name of the constraint primary key

within parentheses the column we would like to apply the primary key to transaction ID in this example then

semicolon however we have already applied the primary key constraint to our transaction ID column so this isn't

going to work but that's how you would add a primary key constraint to a table that already exists Let's test that

theory of that limit of one primary key per table I'll attempt to add a primary key to our amount column

and it does not appear we can do so multiple a primary key to find so like I said you can only have one primary key

per table and that is typically used as the identifier let's take a look at our

transactions let's populate this table with a few rows we will insert into our table

transactions some values all we need is a transaction ID and an amount

for our transaction ID I'll just make up a number all transactions will start in the thousands

this will be our first transaction so the amount a customer bought a hamburger and a soda the total was 4.99

so let's run this there is our first row for the second transaction the transaction ID will be

1001 the customer bought fries and a soda for 289.

so that appears to have worked as well now the next customer buys fries and ice cream for 3.38 cents Let's test that

theory of each transaction ID having to be unique I'll attempt to insert this row with the same transaction ID as the

previous row then let's see what happens duplicate entry 1001 for key

transactions.primary yeah it appears that we cannot have two rows with the same primary key they each need to be

unique let's change the transaction ID to 1002. that appears to have worked now the next transaction will be a hamburger

and soda for 4.49 I will attempt to set the transaction ID to be null which we can't do either column

transaction ID cannot be null each value that's set as a primary key can't be null and it needs to be unique so this

transaction will have a transaction ID of 1003 two rows can share similar data we have

two customers that bought both a hamburger and a soda for the same price but each transaction is uniquely

identified by the transaction ID which we set as the primary key let's say that somebody comes in to request a refund

and they give us a transaction ID well we can easily look that up and refund them whatever the amount was

select amount from transactions

where transaction ID equals 1003.

so we are going to refund to the customer 4.99 well all right then everybody that is

the primary key constraint it's most commonly used as a unique identifier you usually want a primary key in every

table you make each value within a column that is set as the primary key cannot be no and it needs to be unique

there can only be one primary key per table typically it's used as a unique identifier for each row and yeah

everybody that is the primary key constraint in MySQL hey everybody in today's topic I'm going

to explain the auto increment attribute in MySQL the auto increment attribute can be applied to a column that is set

as a key whenever we insert a new row our primary key can be populated automatically then each subsequent row

is auto incremented I'm going to recreate this table transactions but first I need to drop it

drop table transactions then we will recreate this table but apply that auto increment feature

create table transactions there were two columns a transaction ID the data type was int and an amount

the data type was decimal five digits Precision of two the auto increment feature can only be

applied to a column that is set as a key our transaction ID will set to be the primary key to apply the auto increment

feature add the keyword Auto increment Now by default the primary key is going

to be set to 1. so after creating this table I will select all from transactions

okay we have successfully created our table then I will insert some values insert into

transactions R values so we don't need to explicitly insert a transaction ID that'll be done

for us automatically but we still need an amount I will insert 4.99 for the first amount

since we're not inserting all of the values into a row explicitly we would need to specify what column this value

is for that is for the amount okay let us execute this and take a look

yeah so the transaction ID was set to one even though we did not manually insert that value

let's insert the next amount so we have 289 yep transaction ID 2 is 289

3 38 transaction ID3 4.99

transaction ID of four we could set our primary key to begin at a different value

to do that we would type alter table the name of the table Auto

increment let's begin at 1000 instead of one then I'm going to go ahead and drop all

the rows we'll start fresh delete from transactions

select all from transactions okay then let's insert those rows again

and see what happens insert into transactions

R values 4.99 was the first amount this value is for our amount column

now this transaction ID it begins at one thousand then it should Auto increment

the next value was 289 our next transaction ID is 1001 338

1002 then 4.99 1003

all right everybody that is the auto increment feature it's a keyword that can be applied to a column that is set

as a key whenever we insert a new row our key will be incremented by one for each row that we insert but yeah that is

auto increment in MySQL hey everybody so in this topic I'm going to explain what the foreign key

constraint is a foreign key think of it as a primary key from one table that can be found within a different table using

a foreign key we can establish a link between two tables there's two primary benefits to this in my transactions

table if I were to take a look at the customer ID of who initiated this transaction I could refer to the

customers table then find the first and last name of that customer we can do that when we reach the topic of joins

but in this video we're going to focus more or less on just creating foreign Keys another benefit when you create a

foreign key constraint that would create a link between two tables which prevents any actions that would destroy that link

between them so here's an example I have three tables employees products and transactions I'm going to create a new

table of customers I'm going to create this table and speed it up feel free to pause the video if you need to

here's my table we have a customer ID which is the primary key a first name and a last name let's populate our

customers table we have three customers Fred fish Larry Lobster bubble bass each has a unique

customer ID we're going to create a link between our customers table and our transactions table via our customer ID

I'm going to drop our table transactions drop table transactions we will recreate this table but apply

that foreign key constraint so again I'll speed up the footage now what I'm going to do is create a

third column to hold our customer ID and the data type isn't I would like to add a foreign key constraint to our

customer ID column on the next line I will type

foreign key list the column we're applying this foreign key constraint too within parentheses customer ID

references our second table the customers table customers

then the column of the primary key which was customer ID and that's all you need to do

then I will select all from my table transactions here is our table

we have a transaction ID this is the primary key the amount of the transaction then the customer ID of who

initiated that transaction our customer ID column is the foreign key it points to the customers table based on what the

customer ideas we can find the first and last name of that customer to find any active foreign keys

go to your table in this case transactions underneath foreign keys this section will display any foreign

keys that are applied to this table we currently have one transactions underscore ibfk underscore

one if you need to drop a foreign key you would type alter table the name of the table that has the foreign key

constraint that would be transactions drop foreign key then the name of this

foreign key transactions underscore ibfk underscore one

and that foreign key should be gone you could also give your foreign key a unique name

alter table the name of the table add constraint

then some unique name let's rename that constraint as FK underscore customer

ID foreign key

the name of the column customer ID references the name of the second table

customers then the column that has the primary key customer

ID so to apply a foreign key to a table that already exists this would be the

syntax you don't necessarily need to name the constraint but if you would like to just add that

line add constraint then some unique name let's add this foreign key constraint to

a table that already exists it worked if I were to refresh my navigator window we now have a foreign

key and it's the one that we gave a name to foreign key customer ID what we're going to do is drop the rows

from our transactions table then reinsert them but we will add a value for each foreign key

so let's get rid of this delete from transactions then select all from

transactions here we are okay so let's insert some new rows

I do want to set auto increment to be a thousand so I'm going to do that before we insert some rows alter table

transactions Auto increment equals one thousand

so let's take a look at transactions then we will insert some new rows insert into

transactions R values we had four transactions

we will need an amount and the customer ID of who initiated the transaction the first order is for 4.99

customer number three initiated this transaction the second order is 289

customer number two initiated that transaction 338

this is also customer three they returned back the same day 4.99

customer number one initiated that transaction we have our primary key from the

transactions table as well as our foreign key each of these customer IDs references

the customer ID column from the customers table we won't get to explore that until we reach the topic on joins

so the other benefit of a foreign key constraint is that we now have a link between our transactions table and to

the customers table MySQL will prevent any actions that would destroy that link between them unless we explicitly drop

that foreign key constraint I'm going to delete some of our customers delete from customers

where customer ID equals three

we were right it doesn't appear that we can do so cannot delete or update a parent row a foreign key constraint

fails so yeah that's the foreign key constraint it's a primary key from one

table that's found also within a different table but when we're working with that different table we would refer

to that key as a foreign key we'll have more practice with this when we reach the topic on joins but yeah that is the

foreign key constraint in MySQL hey everybody welcome back again in today's video I'm going to be explaining

joins in MySQL a join is a clause that is used to combine rows from two or more tables based on a related column between

them such as a foreign key here's an example I have two tables a table of transactions and a table of customers

think of these two tables as a Venn diagram transactions will be the table on the left customers will be the table

on the right whatever data they have in common is the middle part of our Venn diagram for my demonstration to make

more sense I will need to add a few extra rows feel free to pause the video if you need to catch up I will insert

into transactions a new row the amount is one dollar the customer ID is null so not all transactions can have a customer

ID that foreign key here's a scenario suppose that somebody comes in pays for a soda with cash well we wouldn't have a

customer ID right if a customer instead paid with a credit card we could track who that customer was there may be a

customer ID who initiated that credit card charge I'm going to insert this row and here's our new transactions table

not all rows have a customer ID then let's add one more customer insert into customers first name last

name will be poppy Puff let's select all of our customers now not all transactions have a customer ID

and if not all customers have ever initiated a transaction they could be registered as a customer but they have

never actually bought anything yet using joins let's take a look at what data these two tables have in common we'll

discuss inner joins left joins and right joins let's begin with an inner join to create an inner join between these two

tables you would type select all from which table would you like to be on the left

think of that Venn diagram our transactions table will be on the left from transactions inner join

whatever table you would like to be on the right in this case customers on we're going to join these two tables

together by the foreign key from transactions we'll take transactions.the name of the foreign key

column which was customer ID equals the table on the right dot the name of the primary key column which was

customer ID then we will execute the statement and here's our new table

we have created an inner join from these two tables based solely on what they have in common if you remember we don't

have that Row for that transaction for one dollar that doesn't have a customer ID as well as our customer poppy puff

there's no role for her in this table what we're telling MySQL is to select all rows from these two tables that have

matching customer ideas so that's why some data was excluded one thing you could do with a join this applies to

left joins and right joins as well you don't necessarily need to display every single column from both tables you can

select specific columns let's select our transaction ID the amount

the first name then the last name this would make it a lot easier to find

the first and last name of who initiated a transaction at a given time we know who bought which order so that is an

inner join join together any matching rows based on some Link in this case we're joining these two tables together

by their customer ID now with the left join we are going to display everything from the table on the

left but let's select all with the left join we will display everything from the table on the left

our transactions however if there is a matching customer ID pull in any relevant data from the table on the

right even though there's no customer ID with this latest row we're still going to display it with a left join but

there's no data to pull in from the right table because there's no registered customer ID

with the right join we will display the entire table on the right if there's any matches we will

pull in any matching rows from the left we are displaying all of our customers if any of these customers ever initiated

a transaction we will include the data from those transactions we still have poppy puff in our table but she has no

relevant transactions she never initiated one so yeah everybody those are joins a join is used to combine rows

from two or more tables based on a related column between them such as a foreign key like customer ID and that is

a quick introduction to joins in MySQL hello again everyone today I will be explaining functions in MySQL a function

is a stored program that you can pass parameters into to return a value if you look on mysql's main website there is a

huge comprehensive list of functions in this video I'll only be showing you a few of the more useful ones for

beginners but just so you know there's a lot of functions out there that do all sorts of things what if I would like to

count how many transactions took place on a certain date to do that we can use the count function

select count parentheses within my set of parentheses I can place a column then calculate how many rows are within that

column let's calculate amount we will count how many rows are within the column amount

then we will need a table from our table transactions so you could add a where Clause 2 like where transaction date is

on a certain date but I want to keep this video as simple as possible this function will return all of the

rows within this column amount which is five we have five transactions within this table now this column header is

kind of ugly the column name is count the amount column you could give a column an alias by adding as then some

nickname let's say count it's not necessary but if you want to make the column header look pretty or rename it

as something you could do that count five we could even rename this column as

today is transactions that works too do whatever you want to

do let's find you the maximum value of our column amount by using the max function

within the set of parentheses pass in your amount column I will give this column an alias of

maximum and that is the maximum amount the largest order was 4.99 there's also Min

to find the minimum as minimum the smallest order was for one dollar

you could do average which is AVG as average the average order is three dollars and 45 cents

we can find the sum of a column by using the sum function as some

the sum of all of our transactions was 17 and 25 cents for this next example we're going to concatenate the first and

last name of our employees select all from employees

we have two columns one for a first name the other for a last name we're going to combine these two columns together with

the concat function then create a new column named full name here's how to do that

select concat first name

last name then I'll add an alias as ball name here's what we have so far so we are

concatenating the first and last names of our employees uh however we should separate each name with the space

within our concat function I'll add a space character surrounded with commas to separate each

argument there that's much better so that is the concat function we can

concatenate values together such as if you need a column for somebody's full name but yeah those are just a few

functions there's still many more we haven't covered but these are a few of the beginner ones you may be interested

in but yeah those are functions in MySQL why hello again everybody guess who it's me again today I will be explaining

logical operators in MySQL I would think of them as keywords that are used to combine more than one condition for my

examples to make more sense I will add one more column a job column so let's alter our table of employees alter table

employees add column job the data type will be varchar25 I'll add this column after our

hourly pay column then select all from my table employees here is our job column

let's add some jobs to our employees let's update employees set job equal to Let's select Mr Krabs

he will be a manager then be sure to add a where Clause employee ID equals one

Mr Krabs is a manager Squidward will be a cashier employee ID equals two

SpongeBob employee ID3 he will be a cook Patrick will also be a cook Sandy will be an assistant manager

let's abbreviate the word assistant manager Plankton Plankton will be a janitor

how is this related to logical operators you're wondering well using logical operators we can check more than one

condition suppose that our boss wants us to find any Cooks that were hired before January 5th how can we write a query

that can satisfy those two conditions here's how we will use the and logical operator

select all from our table employees using a where Clause we will write two

conditions find any employees where the higher date is less than the year 2023

January 5th so far this would give us three results Mr Krabs Squidward and SpongeBob from

these three employees how do we find any Cooks well we can add the and logical operator

and we can write some other condition job is equal to cook

there we have SpongeBob he is a cook that was hired before January 5th

so that's the and logical operator you will return any results that match these two criteria another logical operator is

or you can check more than one condition as long as one of those conditions is true that row will be true let's find

any cooks or cashiers where job is equal to cook that gives us both SpongeBob and Patrick

they're both Cooks or job is equal to cashier

that would also include Squidward he's not a cook but he is a cashier with the or logical operator only one condition

needs to be true whereas with the andological operator both conditions must be true so that's the main

difference between the and as well as or logical operators with the not logical operator not is a

little different not basically reverses anything you say let's find any employees that are not a manager

where not job equals manager

we have all of our employees besides Mr Krabs we have cashiers Cooks assistant managers and janitors

you can combine logical operators too let's find anybody that's not a manager and not an assistant manager let's add

and not job equals assistant manager we have everybody besides a manager and

besides an assistant manager Mr Krabs and Sandy are not within the results so that is the not logical operator it

basically reverses whatever condition you write there's also between

is used within a single column it's very similar to the andalogical operator except people prefer to use the

between logical operator when working with the same column just because of its readability let's find any employees

where the higher date is between January 4th and January 7th where higher date between two values

2023 0 1 0 4

and 2023 0 1-07

so we have SpongeBob Patrick Sandy Sheldon there's also the in logical operator we

can find any values that are within a set let's find where job is in cook

cashier or a janitor yep we have a cashier a cook a cook and

a janitor there is more than one way to write a query where you need to match more than one condition really the best

way depends on your data set basically speaking logical operators are used to check more than one condition but

they're done so in different ways depending on the logical operator that you're using but yeah those are logical

operators in MySQL hello again people today I'm going to be explaining Wild Card characters there

are two the percent and the underscore each of these is used to substitute one or more characters in a string here's an

example I have a table of employees what if I need to find any employees whose first name begins with an S well you

would think you could write something like this where first name

is equal to s now this technically doesn't work here what we're doing is telling MySQL to

find any first names that are equal to the character s if I instead need to find any first names that begin with the

character S I can type S then the percent wildcard operator the percent wildcard operator represents any number

of random characters but there's one more change we need to make still replace the equals operator with the

like operator like when used with an aware Clause searches any patterns we will return any first names that begin

with the letter s so the results are Squidward SpongeBob Sandy Sheldon their first names all begin with the letter s

we could do this with dates too let's find any higher dates in the year 2023

technically that would return everything in my example because all the higher dates are in 2023. we can even find if

something ends with a character let's find any last names that end with the character r

that would return Patrick star star with an r at the end you could add more than one character let's find any first names

that start with SP then change last name to first name that would return one result SpongeBob

SP on the other hand we have the underscore wildcard character the underscore wildcard character represents

one random letter let's find any jobs that have one random character followed by oh okay

that would return any Cooks the underscore kind of reminds me of that game Wheel of Fortune MySQL is going to

attempt to fill in any blanks with a matching character if we had two underscores

well there technically wouldn't be any matches MySQL would technically be looking for a five-letter word where the

middle three characters are oh okay but there's no matches here's another example find any higher

dates in January higher date is like the year is going to be random we have four digits one two

three four a dash character zero one Dash then a day of the month so two

underscores again that would return pretty much all of our employees they were all hired in

January maybe we don't care about the year or the month but we care about the day of the month

so let's replace the month with underscores zero two

that would return Mr Krabs zero three that would be Squidward now you could combine these two

different wild cards together too let's take a look at our jobs let's find any jobs where the second

character is an a I know it's kind of a weird example but hopefully it'll make sense

select all from employees where job is like now we're going to combine these two wild characters the second character

has to be an a we could use the underscore for the first character a then any amount of

random characters afterwards that would return a manager cashier or janitor

so yeah those are wild card characters you can use these to substitute one or more characters in a string it's pretty

helpful if you need to find if a string starts with some value or ends with some value and those are wild card characters

and MySQL hey everyone I have a super quick video on the order by Clause the order by

Clause sorts the results of a query in either ascending or descending order based on which column we list here's an

example I have a list of employees how can I list all of these employees alphabetically currently they are listed

by their employee ID well at the end of my statement I can add an order by Clause order buy then a column to order

our Rose by let's order all of these employees by their last name Now by default they will be ordered in

ascending order or alphabetical order if you're working with characters for descending order or reverse alphabetical

order just add desc for descending now these employees are listed in Reverse alphabetical order or descending

order for us sending order that would be ASC but that is the default so you don't explicitly need ASC

let's order these employees by their first name first name

these employees are listed alphabetically by their first name let's do reverse alphabetical order

yeah there we are let's order these employees by their higher date higher date and ascending order

you don't even technically need that keyword ascending it would still work just fine now descending

I think we have the hang of it now let's select all from transactions there's one more thing I want to show you

select all from transactions we have two transactions with the same amount

let's order by amount when ordering a column if two values share the same amount we can add an

additional column to compare by order by amount but if two amounts are the same order by maybe