Placeholder Image

Subtitles section Play video

  • All right!

  • Great!

  • One thing youll notice when studying programming languages is that the concepts you encounter

  • are interrelated.

  • This means focusing on a single topic can hardly deliver the content that must be explained

  • in its entirety.

  • To this story, SQL makes no exception.

  • Despite that, we must start from somewhere, right?

  • My working experience tells me you will be much faster in learning and writing efficient

  • queries if you go through a brief introduction to databases.

  • It is the best thing to begin with, so here’s what I would like to share with you.

  • The table you see here contains data about the customer sales of a furniture store.

  • And, this is how we should read this information: Purchase number 1 was registered on the 3rd

  • of September 2016.

  • Then, customer number 1 bought the item with code A 1.

  • Each of the four elements has a specific meaning.

  • We call each one a data value.

  • All four data values make up one record.

  • A record is each entry that exists in a table.

  • It corresponds to a row of the table.

  • Therefore, these four data values form one record, and these data values regarding purchase

  • number 2 form another record.

  • You could use the terms row and record interchangeably.

  • Besides the rows, you can see the data is separated into four columns or fields.

  • A field is a column in a table containing specific information about every record in

  • the table.

  • This means all the records in a table, regardless if they are 10, 10 thousand, or 10 million,

  • will have a certain purchase number, date of purchase, customer ID, and item information.

  • When the data you have is organized into rows and columns, this means you are dealing with

  • stored tabular data.

  • This is important to mention, because you will often see in the literature that database

  • management relates to data stored in tabular form.

  • Great!

  • Here comes the more interesting part.

  • In this table, we know nothing about a customer besides her ID.

  • The information about customers is stored in another table, calledCustomers”.

  • There are several fields, such as first and last names, e-mail addresses, and the number

  • of times customers have filed a complaint in our store.

  • So, what would the logic of that structure be?

  • Every time we have a customer with an ID number 1 in theSalestable, we can refer to

  • the customer with ID number 1 in theCustomerstable and see her name, email, and number

  • of complaints filed.

  • Same goes for theItemstable.

  • It contains the item code, product description, its unit price, ID, and name of the Company

  • that has delivered it, and the company’s headquarters phone number.

  • Here, the connection between theSalestable and theItemstable is not the

  • customer ID, but theitem code”.

  • Well, we could stuff this information into one table, and it would look like this.

  • Ouch!

  • It is huge!

  • I would not want to imagine what the table would look like if we had registered over

  • 10 rows!

  • My point isthere are too many columns, and it is hard to understand what type of

  • information is contained in the larger table.

  • Relational algebra allows us to use mathematical logic and create a relation between a few

  • tables in a way that allows us to retrieve data efficiently.

  • Namely, these three tables – “Sales”, “Customers”, andItems”– are related

  • through the customer ID or theitem codecolumns and form a relational database.

  • And, importantly, each one bears a specific meaning and contains data characterizing it.

  • One of the tables contains information about sales, the other about customers, and the

  • third about the items.

  • To recap, remember the data values in a row form a record in a table, and each column

  • represents a field that carries specific information about every record.

  • A few related tables form a relational database.

  • And, for those of you who are interested in slightly more technical definitions, remember

  • the smallest unit that can contain a meaningful set of data is called an entity.

  • Therefore, the rows represent the horizontal entity in the table, the columnsits vertical

  • entity.

  • The table is a bigger data entity on its own.

  • It can also be referred to as a database object.

  • A single row of a table, being a single occurrence of that entity, can be also called an entity

  • instance.

  • Ok.

  • Great!

  • We will gradually build the theoretical preparation you need before you begin coding.

  • Now that you know what a relational database is and have an idea about how it works, it

  • is much easier to understand how SQL fits the whole picture.

  • SQL is the programming language you need to execute commands that let you create and manipulate

  • a relational database.

  • We will not delve into strict and detailed technical definitions to explain how it works.

  • What you need to know is there are a few types of programming out thereprocedural (imperative),

  • object-oriented, declarative, and functional.

  • Although with some procedural elements, SQL is mainly regarded as a declarative programming

  • language, it is nonprocedural.

  • This means, while coding, you will not be interested in how you want the job done.

  • The focus is on what result you want to obtain.

  • An abstract example would best clarify what we mean here.

  • When using a procedural language, such as C or Java, you must explicitly divide the

  • solution of a certain problem into several steps.

  • For instance: 1.

  • Please, open the door.

  • 2.

  • Go outside.

  • 3.

  • Take the bucket I forgot there.

  • 4.

  • Bring it back to me.

  • In a declarative language, this would sound like:

  • 1.

  • Fetch the bucket, please.

  • And you wouldn’t have to go through the process step by step.

  • The algorithms are built-in, and there is an optimizer, which will separate your task

  • into smaller steps and do the magic to bring the desired output.

  • Why is this important?

  • When using SQL, you should concentrate on what you want to retrieve from your database.

  • Unless you are an advanced user, don’t bother with the algorithms explaining how your data

  • can be obtained.

  • Acknowledging SQL is principally a declarative language, now we can go through the main components

  • of its syntax.

  • It comprises a data definition language (known as DDL), a data manipulation language (abbreviated

  • DML), a data control language (DCL), and a transaction control language (TCL).

  • Ok, as you can see here, the central part of your screen is where you can create queries

  • or SQL objects.

  • In simple terms, it will be the area where you will be typing code.

  • For example, I can type a line of code that will select all records of a table contained

  • in a database.

  • Ok!

  • Please don’t pay too much attention to the code used in this video!

  • For the moment, however, concentrate on the interface of MySQL workbench.

  • Here, you can see a small set of icons executing various functions.

  • By clicking on the first one, you can get to a window from which you can select and

  • open an existing SQL script.

  • The second icon allows you to save the script on your computer and so on.

  • The functionalities of most of the remaining icons in this set will be explored later in

  • the course.

  • An important icon to remember is the one depicting a lightning.

  • By pressing it, you can execute or run the code youve written.

  • Let’s try this.

  • Bingo!

  • A new block appeared in the middle of the screen.

  • It is called theresult grid”, although more often, youll hear people referring

  • to its content as theresult set”.

  • Obviously, here, you can see the data obtained after running the code weve written.

  • It is accurate to say that, in the middle part of the screen, you can see the results

  • obtained after executing your query.

  • Finally, to close the result set, you must press the cross sign on the tab indicated

  • down here.

  • Alright!

  • At the bottom of the screen, we can see the output section.

  • It keeps track of all successfully or unsuccessfully executed operations in MySQL in a given session.

  • For instance, we obtained an output last time, didn’t we?

  • That’s why we see a little green circle with a tick mark over here and the number

  • and time of the operation executed.

  • You can see the exact action undertaken, a message from Workbench regarding this operation,

  • and the time it took the server to reply to your query with an output.

  • Lovely!

  • On the left part of the screen is theNavigatorsection.

  • It is relevant for advanced analysis and for more advanced database maintenance sessions.

  • The subsection we will care about most in our course is the schemas section.

  • It represents all available databases, their tables, and other related SQL tools and features.

  • The upper right part of the screen contains three little squares that will allow you to

  • hide or show the navigator, the output section, or the SQL Additions section where we can

  • find more advanced features if necessary.

  • You can use these buttons to adjust the program’s interface according to your preferences.

  • Great!

  • Finally, beneath the section with connections tabs, we can see a few small icons.

  • They allow us to add various types of files and objects.

  • When you hover over an icon, workbench displays an explanation of what it does.

  • As it says here, it will create a new SQL tab for executing queries.

  • So, let’s press this icon.

  • You see?

  • A new SQL tab opened.

  • This is the place to start a new SQL script from scratch.

  • Now that you have more than one SQL tab open, youll need a single click over a tab’s

  • name to jump from one SQL script to the other.

  • Easy, right?

  • The second icon takes you to a window that allows you to select and then open existing

  • SQL scripts.

  • Throughout the course, you will often need to use this icon.

  • Whenever we ask you to load a certain SQL script, you must click on that icon, go to

  • the directory where youve stored the respective SQL script, select it, and then press the

  • Openbutton.

  • Amazing!

  • This was an introduction to the main characteristics of MySQL Workbench.

  • Please, play around with its interface.

All right!

Subtitles and vocabulary

Operation of videos Adjust the video here to display the subtitles

B1 sql table data database id entity

MySQL IN 10 MINUTES (2020) | Introduction to Databases, SQL, & MySQL

  • 0 0
    林宜悉 posted on 2020/03/09
Video vocabulary