And a series of six on databases.
The topic of this module is Advanced SQL.
In the last module we talked about
the basics of SQL syntax that you
use to talk to relational databases.
We went through select and joins,
aggregate functions, we talked about
creating databases and tables.
Showing and describing tables and
databases that are available,
how to insert data both
from an individual row and
also from a bulk perspective and
then updating and deleting.
So, those were from the, the basic
operations that you need to use to,
to know to use a relational database.
This time, in this module, we are talking
more about some of the advanced features.
First one is what if I want to
alter the structure of a table
that I've already created?
It may be that this is simple enough
just to delete the whole thing and
create a new table and
reload the data, but
it may be that I've, I've done a lot of
ref operations on this table already, or
that there's a lot of data in it and
therefore it's simpler to alter the table.
The syntax for
this is the Alter SQL command.
Alter table, table name and
then the operations you want
to make to change the table.
Let's say that I want to add
a column called B magnitude to
my Star table that we
introduced last time.
And I want to put it after my V magnitude.
But the purpose of this
first command there.
So I'd alter table star, add column,
the column that I'm adding, B magnitude.
The data type of that column,
a double in this case.
And I'm adding it after the V magnitude.
If I want to remove that B magnitude
column at a, at a later date, then I can
do it simply by doing ALTER TABLE star
DROP COLUMN and then the column name.
There're other operations that
I can do with the ALTER TABLE.
Changing names, changing data types,
that sort of thing.
You can look those up.
Now often when you're working with a
table, there's a particular operation that
you're going to be doing on the table that
is repetitive a particular type of query,
particular query for a certain subset
of it that you're going to use, and
you could put that information
into a separate table.
But one thing you can do with a relational
database is what's called CREATE a VIEW,
which is a,
a specification of a subset of a table,
a set of tables, which you're going to
treat as a table in its own right.
But you don't want to go through all
the hassle of creating that as a table in
its own right because it may be that the,
that's repeating data you don't
necessarily need to do that.
So a view is very useful, and for
that context the syntax is CREATE VIEW and
the viewName AS, and
then you specify a select statement
which defines the the, the data
that is associated with that view.
So let's say I just,
there's a particular region of, of,
of sky which I'm going to be using for
a lot of operations and
I can define of that as a view,
through select star from star.
How, how,
where I then define the positional
constraints defining that region.
I can then do a regular select statement
and instead of using the table name,
I can use the, the name of my view,
in this case region one view, as the table
and it will only use the data that
is defined by that particular view.
So it will use the data within
the positional constraints on
the sky that I've defined as the first
data set that it's working on.
And then apply the additional constraints
that I'm using in the WHERE statement and
my SELECT statement.
Similarly I can create a view
as the result of an inter join in this
particular case with more information.
And because I've specified that in a join
there will be those column names in
the view that inner join rule look
as though it's a table tool intended
purposes and I can query it
as if it were a table in its own right,
even though from a programmatic
perspective it's been created on
the flight by the relational database.
So views can be incredibly powerful ways
of making your life more manageable.
Next indexes when you're
working with a database and
you're doing querying, sometimes
the performance can be very slow.
This can be because of the way that
the data is arranged on the disk and
ways around this are to
create specific lookup tables
to allow fast access or specific lookup.
Data structures to allow our
fast access to, for data.
Syntax for this is Create Index,
Index Name, or
a Table Name and
the columns that you're eating in that.
So, it may be that I'm going to be
running a number of queries against the V
magnitude column in my table.
And I'm finding that they're
being particularly slow.
I would hope that by
creating an index on them,
any queries running against
that will actually be faster.
There're two types of
queries that you can,
two types of indexes that
you can have on a table.
You have what's called a clustered index,
first of all.
And this is one in which the ordering of
data entry is, is actually the same as
the ordering of the data records as
they are physically put on to disc.
I've already said in
the previous talks that if you
have a primary key on a table that
is automatically a clustered index.
Since you, there can only be
one clustered index per table,
if you already have a primary key on
your table, any other indexes you
create on that table will be
unclustered and will make use therefore
of specific structures in the relational
database for doing their lookup.
However, you can have multiple
unclustered indexes on the same table.
They are typically implemented
as B plus trees which are a very
efficient data look up structure,
but there are alternate types, for
implementation depending on the type
of data that you're working with, and
the relational database
system that you're using.
It may be that there is a,
a collection of operations that I'm
constructing that I'm,
I'm carrying out on my database or
my database table that I'm going
to be repeating a number of times.
And in the same way that if I was
writing doing this programmatically,
I might actually write this up as
a as a function in the database,
well if you can write this
as a stored procedure.
It is the set of operations
that you can neglect.
The, the set of select statements a, and,
and joins and, and such, that you're
going to work to give you the, the answer
that you want for a particular operation.
Syntax is CREATE PROCEDURE,
the procedureName, and then the parameters
that the procedure can take.
The arguments of the function and then
the declaration of what it's required.
In this particular case,
example given here, I'm creating a,
a procedure called
findNearestNeighbour to a star.
Where I'm going to just
take that as the name.
I'm assuming that there is already
an existing function in my database,
an existing procedure,
which will give me the nearest neighbor
giving, given a position on the sky.
In writing this procedure, the idea is
that it, the procedure will do a lookup
to get the position of the starName
I've given, and then pass the position
information to the existing procedure and
return the result of that.
So what I'd first of all do,
is CREATE PROCEDURE,
name of the procedure the argument.
It's going to be type of the argument
is varchar, variable character,
of up to the length 20.
And then I declare the internal
parameters for this procedure.
I'm, those are den, denoted by the @ sign.
I'm declaring an RA in the deck.
Variables, these are of type float.
I am declaring a name variable that of
varchar 20 and then I say Select and
then I set the RA variable to
the value of the RA column.
The deck variable to the value of
the Deck column from my table star,
where the name column is like
the star name that I've given
as the argument to my procedure and
then I select
name from getNearestNeighbour
with theRA deck and end.
And then to execute this, I use the exact
statement, exact FindNearestNeighbor,
Sirius, in quotes and that would then
give me the nearest neighbor star.
The name of the nearest neighbor
star to Sirius in my database.
cursors, are a way of
actually physically doing
going through a table an op, op, doing an
operation on a table, one row at a time.
And there is a,
a particular set of syntax for them.
The next slide we will give an example of,
of exactly how to do that.
And it has to be said though that
cursors are the slowest way of
accessing data in a relational
database there's operation of
physically going through one row at a time
in a table is, is very inefficient.
And not utilizing the,
the, the joining power of,
of the database rationally to this.
But sometimes it's necessary for
the type of operation that you want to do,
that you want to make use of
this sort of functionality.
In this particular case, for
each row in our data set scenarios, we
want to update a particular StellarModel
that we have that exists elsewhere.
And for some reason we can't do
this as the result of a drawing.
So syntaxes we first of all
declare a number of variables.
We declare a name variable and
the data type on that, varchar 20,
we declare a magnitude variable and
it's of the float data type, and
then we declare our cursor, this is the
thing that's going to go through each row.
We say it's called starCursor and then we
say what it does is it selects the name
and the average V magnitude from our table
star when I'm grouping by stellarType.
so, that's going to give me those
two pieces of information for
my from my star table and
then I open my star cursor.
What I say is that I, I fetch into
my cursor that information and
the name and the magnitude and
then I have some stored procedure
already called Update Stellar Model.
And that I'm going to run that based
on the values of, of the, the name and
magnitude that I've loaded from running
my cursor on my particular operations.
So in this particular way,
I'm going to be going through
the different types of stellar
type that I have in my star table.
I'm getting their average magnitudes, and,
and the name of that type of stellar type
and I'm updating my stellar model with
the values of those average V magnitudes.
yeah.
Triggers are a way of having particular
operations in the database happen
depending on, on other things that
you've done, so it may be that if I
insert data into one table, there's
another table which needs to be updated or
if I update data one table, an operation
has to have been formed on another table.
Or if I delete data, another
operation has to be, has to be done.
So this, in the syntax here I'm creating
a trigger called star trigger on my table
star for an update,
saying that if if the V magnitude
column is updated in any way through
an update statement, then I would execute
this a stalled procedure that I've called
refreshed models which would presumably do
something to other tables on the basis
of that information that I've updated.
So other operations in
the database are triggered by
an operation I've created on that table.
finally, I'll give an example
of how you can work with a,
a relational database from
a programmatic perspective.
A lot of relational databases
have web-based interfaces, or
command-line-based interfaces for
you to specifically type SQL
statements in, but often it's
very useful to actually be able to access
the database through an interface layer.
So that you can write code to do it.
And this little code snippet here is
an example of how to do that with Python.
So I am, there's a particular
Python module, in this case called,
MySQLdb, which I'm importing,
and then I specify
the connection details for
my code to be able to
connect to a database server somewhere
which is listing on a particular port.
I give it my, my username for the
relational database that I'm accessing,
my password and the name of
the database that I'm accessing.
I then construct a cursor in
this particular case this is
just the data structure that is
defined in the MySQL statement.
It's not a database cursor.
I then define the SQL statement.
In this case I'm just wanted to return
everything from my table staff,
I execute that so the Python codes
sends the appropriate SQL command
over the wire to the database and the
information is piped back, and then I can
fetch all the results, or I could fetch
the results one at a time, or, or do, or
all manner of operations to retrieve
the data and work with it appropriately.
And those are specified in
the MySQLDB documentation.
But, there you have an example
of how you can access a relation
base programatically,
making use of your your,
your SQL but
you're not using the interface that's been
provided by the DBMS you're doing it
through a standard programmatic interface.
And such interfaces, such modules are,
are defined for any manner of computer
languages and most databases.
And that is the end of this module.