Detailed Notes
We start our series on SQL scripting and development.
Topics 1. SQL Overview 2. Create Database 3. Create Tables 4. Insert/Select 5. Update 6. Delete 7. Stored Procedures 8. Views 9. Users and permissions 10. MySQL/MariaDB 11. SQL differences
Transcript Text
[Music] well hello and welcome back and welcome to if you haven't aren't coming back but welcome to a series on sql we're going to get into database development really focused on the database side and we're going to yes start with some very simple sort of general basic kinds of rules and we're going to work our way up to some more complicated things i haven't seen a whole lot of good tutorials other than the ones that are really more like a straight not as much a tutorial as they like a reference it says you know hey these are all the different ways you can use this command and so we decided this would be a great way to do a new series and it's also a foundational piece of so many other applications that we've dealt with or will deal with particularly when you go to for example the django stuff that we did there's a lot of database knowledge within there and i think it makes sense to just sort of you know take a step back and let's talk about some database stuff so in this series we are going to go through from installation to whatever you know backups and maintenance and stuff like that uh this first episode is really focused on just it's really an overview of the class what are we gonna order this series i guess and what kinds of things are we going to talk about now in general i want to talk about what's a database a database is really just a way to store data i know it seems very simplistic but you have it and the terminology by the way will change a little bit depending on which vendor you're dealing with they have different ways that they refer to the various things we're going to be dealing with as far as users and databases and tables and things like that the naming can change a little but the key here will be to get to the concepts and to try to keep them as generic as possible even though our focus is going to be on the my sequel maria database and that's a slash because it used to be my sequel now oracle owns it and has been renaming it more or less to maria but you'll find for example just running the command line stuff is still going to have all those my sequel references so don't let it confuse you if you haven't messed around with database for a while and you see this mariadb stuff coming up instead of mysql and we're going to we're going to go through all kinds of fun things we're going to start with we're going to create databases we're going to create users we're going to set some permissions we're going to talk about creating tables and inserting data updating data deleting data now from those concepts a table is if you can think of a spreadsheet like excel or something like that you've got rows and columns that's a table in a database you have rows of data and then you have these columns and within those columns they have things constraints which are like a type something like that so your column may have all the data in there maybe a number it may be a character of some sort it may be a time time stamp we will talk about those as we get further into it you can insert data which basically you think could be this you've got columns and rows you can add a new row put some data into it and that is your insert update is you go select a row that has data in it change some of that data and that is the update of course delete if you go to that row and you want to remove it completely not clear it out but actually delete that row so that now there's one less row that's a delete and we're going to get into stored procedures now databases typically are queries or commands and this sql is actually structured query language and it's primary purpose is to ask something of the database or to tell it to do something that's more or less like a anatomic task you know like insert this record update these values delete this record stored procedures allow us to group multiples of those commands together so instead of saying give me the data from this table starts where this field starts with a and then that's it now it's give me that data and then i want you to look at each of those records and update them if this other column is equal to you know it's greater than zero or something like that and so now instead of just a command at a time stored procedures allow us to do multiple commands sort of you know stack them up cue them up and then generate those and it works like another language is a function or something or procedure subroutine depending on what it's called in another language where you can send values in to this list of commands and then generate all you know do all the commands and then kick out some sort of result now it's a database so it may be that the results come at the end or you know like return back as part of the stored procedure or it may be that those results are really just side effects the updates and changes made to data and maybe even the database itself when that runs and we're also going to talk about views a a table typically we talk about these commands so we can query a table and say give me all the like i said all the records that start with a okay great what a view does is it takes a query it takes results of a query and gives you that as a table in itself so if you think of a spreadsheet if you've got a spreadsheet and you've got a hundred records on it and then you take that and you turn around and take 50 of those records and i'm going to do a quick example i'm here if this will come up while i'm talking then you can bait so let's say this is a database and you've got you know you've got your columns a b c d e and you've got your values so we're just going to keep it very simple here two two two two and we've got that so we've got this little table and so right now what i'm doing is like here would be insert a row whoops let me do that let's update this row so i'm going to go through i update these values that would be an update i can delete it that would be a delete now this is not a database but i'm seeing i'm showing you roughly what this stuff would look like now in a view what i can do is let me do this is i could say let me i'm going to often ask give me all the values greater than 2. and so this would be the result set and so if i add another sheet that would be the result set now what i can do is go back to sheet 1 table 1. and that's my primary results that's my source but now i could have this view which i'm going to call view one and its data is based on what's in table 1. i will get deeper into views later but that's roughly your view so you have stored procedures so you can stack up commands you've got views so you can essentially store solution or result sets you have users and permissions this is your typical security kind of stuff is that you have people that have access to the overall database people are users that can have access to either sometimes it's called a schema or a that's sometimes referred to as a database within the overall database engine and then those permissions may be limits to oops that's not what i want maybe the limits to things like can they insert can they update can they delete then they create another database can they create a table and so you will have users and permissions which we will explore and we'll set one up because much like security in general you have a what's in most databases known as a is the owner or the root user that has access to everything and then you're going to have users that have limited access depending on what your needs are and ideally you don't use that root or super user or owner account basically ever all of your stuff should be all your work instead should be a user created and then assign permissions for that type of work and you'll see this a lot in a database where you may have a like a developer account that a person can connect to and they can create tables they can delete tables they can insert update delete records and all kinds of stuff separately you may have a reporting user that can only do queries that return results they can't do inserts they can't do updates they can't do deletes and i guess that's the one command i didn't really talk about is the select would be your other is let's say oh i don't have that so let's just put that in there just because and we'll just put here insert select it's not really the same but that's okay so we can go in we we select our data and we can't do anything with it because we are in this case instead of that that reporting user and then you may have some that are call them power users or something like that you may have this other permissions set up where maybe they can insert and update records but they can't delete or maybe they can't directly do any of that but they can call stored procedures and store procedures can be built in a way that can provide spot permissions basically so for example i could say that no user is allowed to none of my users are allowed to update data however i can create my stored procedures so that they can update data so there's a very specific now a controlled way to deal with it another value for stored procedures and functions and so within that we can grant a user or users access to a procedure and then that procedure will do the updates and then i want to i don't want to leave this first little step without mentioning that we're going to go through examples that are essentially standard sql you may find differences depending on what database you use now if you use what we're using you're probably going to have essentially the same but depending on how you configure things you may even see a few differences for example sometimes you're going to find that database queries and databases in general what they display back are case sensitive and sometimes they're not so if you wanted to write select star from my table that may be fine even if the table is called capital my capital t table and it may be that this works no matter what or if this is the name of your table my table it may be that when it's uh when you try to do this query it says no my table doesn't exist because it doesn't with that case sensitivity so something to consider if you're going through examples and it's not quite right just sort of a heads up one make sure that they're saying things like uh spacing and uh case sensitivity and if there's things like a semicolon or not make sure those things are you double check those and then when in doubt you can always do a google query for that or a google search or whatever your favorite search engine is for that query and see where there may be some issues and particularly if you have that like a query and then whatever the error is you can probably find a lot of ways to get you know pointers to the different ways you can break a query i think that's going to do it for this one i just really wanted to give sort of an overview talk about some of the things we're going to get into give you just sort of like a you know crash course here's a database here's what we're going to talk about and then we're going to come back in the next episode and get started now before i wrap this one up though i do want to say this is not going to be intended this is not intended to be a database engine or version specific so while i am going to be personally i'm going to be while we're going through these working in a mysql database and specifically i'm going to get you this real quick so you can see what the version is uh specifically the one i'm working on is version 10.3.34 of mariadb or you may want the whole server version and there may be some variations in that but most of what we should we're doing you should be able to i don't know if you've got a sql server microsoft sql server if you're working in that if you're working in oracle if you're working in db2 you are going to see some differences particularly when we get into creating databases a little bit of creating tables because data types can be a little different how they're handled and then when we get into we do any of them the administrative type stuff we're going to talk about like database backups and things like that that may be a little different but hopefully you'll see that the the bulk of what we're covering is applicable to whatever engine you're using so in leaving this one you may want to go ahead and go search for download maria db latest version something like that or go search for this version how to download it for whatever platform you're working on this one is actually a um this is a centos version 10 operating system on the back end don't know that's going to matter much but just so you've got a little bit of context for what we're going to be sort of the foundational tools we're using as we go forward with that that being said we'll wrap this one up let you get back to it and come back next time around and we're gonna start diving into our database creation have a great day you
Transcript Segments
[Music]
well hello and welcome back and welcome
to
if you haven't aren't coming back but
welcome to
a series on
sql
we're going to get into database
development
really focused on the database side and
we're going to
yes start with some very simple sort of
general basic kinds of rules
and we're going to work our way up to
some more complicated things
i haven't seen a whole lot of good
tutorials other than
the ones that are really more like a
straight not as much a tutorial as they
like a reference
it says you know hey these are all the
different ways you can use this
command
and so we decided this would be a great
way to
do a new series and it's also a
foundational piece of so many other
applications that we've dealt with or
will deal with
particularly when you go to for example
the django stuff that we did
there's a lot of database
knowledge within there
and i think it makes sense to just sort
of you know take a step back and let's
talk about some database stuff
so in this series
we are going to go through
from
installation to
whatever you know backups and
maintenance and stuff like that uh this
first episode is really focused on just
it's really an overview of the class
what are we gonna order this series i
guess
and
what kinds of things are we going to
talk about
now in general i want to talk about
what's a database a database is really
just a way to store
data i know it seems very simplistic but
you have it and the terminology by the
way will change a little bit depending
on which vendor you're dealing with they
have different ways that they refer to
the various things we're going to be
dealing with as far as users and
databases and tables and things like
that
the naming can change a little but
the key here will be to get to the
concepts
and to try to keep them as generic as
possible
even though our focus is going to be
on the my sequel maria database
and that's a slash because it used to be
my sequel now oracle owns it and has
been renaming it more or less to maria
but you'll find
for example just
running the command line stuff is still
going to have all those my sequel
references so
don't let it confuse you if you haven't
messed around with database for a while
and you see this mariadb stuff coming up
instead of mysql
and we're going to we're going to go
through
all kinds of fun things we're going to
start with we're going to create
databases we're going to create users
we're going to set some permissions
we're going to talk about creating
tables
and inserting data updating data
deleting data
now from those concepts a table is
if you can think of a spreadsheet like
excel or something like that you've got
rows and columns
that's a table in a database you have
rows of data and then you have these
columns
and within those columns they have
things constraints which are like a type
something like that so your column may
have
all the data in there maybe a number it
may be a character of some sort it may
be a time time stamp
we will talk about those as we get
further into it
you can insert data which basically you
think could be this
you've got columns and rows you can add
a new row put some data into it
and that is your insert
update is you go select a row that has
data in it change some of that data and
that is the update
of course delete if you go to that row
and you want to remove it completely not
clear it out but actually delete that
row
so that now there's one less row
that's a delete
and we're going to get into stored
procedures
now databases typically are
queries or commands and this sql is
actually structured query language
and it's
primary purpose is to ask something of
the database or to tell it to do
something that's
more or less like a
anatomic task you know like
insert this record update these values
delete this record
stored procedures
allow us to group multiples
of those commands together so instead of
saying
give me the data from this table
starts where this field starts with a
and then that's it now it's give me that
data and then i want you to look at each
of those records and update them if this
other column is equal to you know it's
greater than zero or something like that
and so now instead of just a command at
a time stored procedures allow us to do
multiple commands sort of you know stack
them up cue them up
and then generate those
and it
works like
another language is a function or
something or procedure subroutine
depending on what it's called in another
language
where you can send
values in
to this list of commands
and then generate all you know do all
the commands and then kick out some sort
of result
now it's a database
so it may be that the results come at
the end or you know like
return back as part of the stored
procedure or it may be that those
results are really just side effects the
updates and changes made to data and
maybe even the database itself
when that runs
and we're also going to talk about
views a a table typically we talk about
these
commands so we can query a table and say
give me all the like i said all the
records that start with a
okay great
what a view does
is it takes a query it takes results of
a query
and gives you
that as
a table in itself
so if you think of a spreadsheet if
you've got a spreadsheet and you've got
a hundred records on it
and then you take that
and you turn around and take 50 of those
records
and i'm going to do a quick example i'm
here
if this will come up while i'm talking
then
you can bait so let's say this is a
database and you've got you know you've
got your columns a b
c d e
and you've got your values so we're just
going to keep it very simple here
two two two two
and we've got that so we've got this
little
table
and so right now what i'm doing is like
here would be insert a row
whoops let me do that let's update this
row so i'm going to go through i update
these values that would be an update
i can delete it
that would be a delete now this is not a
database but i'm seeing i'm showing you
roughly what this stuff would look like
now in a view
what i can do is let me do this
is i could say let me i'm going to often
ask give me all the values greater than
2.
and so this would be the result set
and so
if i add another sheet
that would be the result set now what i
can do
is go back to sheet 1
table 1. and that's my
primary results that's my source but now
i could have this view which i'm going
to call
view one
and its data is based on what's in table
1.
i will get deeper into views later but
that's roughly your view
so you have stored procedures so you can
stack up commands you've got views so
you can essentially store
solution or result sets
you have users and permissions this is
your typical security kind of stuff is
that you have people that have
access to the overall database
people are users that can have access to
either sometimes it's called a schema or
a that's sometimes referred to as a
database within
the overall database engine
and then those permissions may be limits
to
oops that's not what i want maybe the
limits to things like can they insert
can they update can they delete then
they create another database can they
create a table
and so you will have users and
permissions which we will explore and
we'll set one up because
much like security in general
you have a
what's in most databases known as a is
the owner or the root user that has
access to everything
and then you're going to have users that
have
limited access depending on what your
needs are
and ideally you don't use that root or
super user or owner account
basically ever
all of your stuff should be all your
work instead should be
a user created and then assign
permissions for
that type of work
and you'll see this a lot in a database
where
you may have a like a developer account
that a person can connect to and they
can create tables they can delete tables
they can insert update delete records
and all kinds of stuff
separately you may have a reporting user
that can only do queries that return
results they can't do inserts they can't
do updates
they can't do deletes
and i guess that's the one command i
didn't really talk about is the select
would be your other
is let's say
oh i don't have
that so let's just put that in there
just because
and we'll just put here insert select
it's not really the same but that's okay
so
we can
go in we we select our data
and
we can't do anything with it because we
are in this case instead of that that
reporting user and then you may have
some that are
call them power users or something like
that you may have this other
permissions set up where
maybe they can insert and update records
but they can't delete
or maybe
they can't directly do any of that but
they can call stored procedures and
store procedures can be built in a way
that can
provide spot permissions basically so
for example i could say that no user is
allowed to none of my users are allowed
to update data however
i can create my stored procedures
so that they can update data so there's
a very specific now a controlled way to
deal with it another value for stored
procedures and functions
and so within that
we can grant a user or users
access to a procedure and then that
procedure will do the updates
and then
i want to i don't want to leave this
first little step without mentioning
that we're going to go through examples
that are
essentially standard sql
you may find differences depending on
what database you use now if you use
what we're using you're probably going
to have essentially the same
but depending on how you configure
things you may even see a few
differences
for example
sometimes you're going to find that
database queries and databases in
general what they display back are case
sensitive
and sometimes they're not
so if you wanted to write
select star from
my table
that may be fine even if the table is
called
capital my capital t table
and it may be that this works no matter
what or if this is the name of your
table my table it may be that when it's
uh when you try to do this query
it says no my table doesn't exist
because it doesn't with that case
sensitivity
so something to consider if you're going
through examples and it's not quite
right
just sort of a heads up
one make sure that they're saying things
like
uh spacing and
uh case sensitivity and if there's
things like a semicolon or not make sure
those things are you double check those
and then when in doubt you can always do
a google query for that or a google
search or whatever your favorite search
engine is for
that query and see where there may be
some issues and particularly if you have
that like a query and then
whatever the error is
you can probably find a lot of ways to
get you know pointers to the different
ways you can break
a query
i think that's going to do it for this
one i just really wanted to give sort of
an overview talk about some of the
things we're going to get into give you
just sort of like a you know crash
course here's a database here's what
we're going to talk about
and then we're going to come back in the
next episode and get started now
before i wrap this one up though i do
want to say
this is not going to be intended this is
not intended to be
a database engine or version specific
so while i am going to be personally i'm
going to be while we're going through
these working in a mysql database
and specifically
i'm going to
get you this real quick
so you can see what the version is
uh specifically the one i'm working on
is version
10.3.34 of mariadb
or
you may want the whole server version
and
there may be some variations in that but
most of what we should we're doing you
should be able to i don't know if you've
got a sql server microsoft sql server if
you're working in that if you're working
in oracle if you're working in db2
you are going to see some differences
particularly when we get into
creating databases a little bit of
creating tables because data types can
be a little different how they're
handled
and then when we get into
we do any of them the administrative
type stuff we're going to talk about
like database backups and things like
that
that may be a little different
but
hopefully you'll see that the the bulk
of what we're covering
is applicable to whatever engine you're
using
so in leaving
this one
you may want to go ahead and go search
for
download maria db latest version
something like that
or go search for this version how to
download it for whatever
platform you're working on
this one is actually a um
this is a centos version 10
operating system on the back end
don't know that's going to matter much
but just so you've got a little bit of
context for what we're going to be
sort of the foundational tools we're
using as we go forward with that
that being said
we'll wrap this one up
let you get back to it and come back
next time around and we're gonna start
diving into our database creation
have a great day
you