Placeholder Image

Subtitles section Play video

  • Let's practice inserting a few records in theEmployeestable.

  • We can extract 10 records from theEmployeestable to see how the information is organized

  • there.

  • Please allow me to make the following remark: we increased the limit of rows displayed in

  • an output to infinity.

  • So, in the queries in this lecture, I will place limits through the LIMIT clause.

  • Great!

  • So

  • Execute, and

  • A-ha!

  • Nice!

  • We retrieved 10 rows, all containing information in the following columns: “employee number”,

  • birth date”, “firstandlast name”, “gender”, andhire date”.

  • A total of six columns.

  • Ok.

  • Let's see how we create a record in theEmployeestable.

  • After the keyword phrase INSERT INTO, we must specify the name of the table where we want

  • to add information.

  • Then, within parentheses, we must indicate the column names where data will be inserted.

  • Please pay attentionyou don't have to use the names of all six columns!

  • You can designate only those in which you would like to insert data.

  • What follows is intuitivethe VALUES keyword, succeeded by the same number of data values

  • as the number of columns indicated in the parentheses after the INSERT INTO statement.

  • Note that the data values should be typed within parentheses, too.

  • You are already acquainted with the syntax we just discussed.

  • Now let's put it into practicewe'll create a record of an individual named John

  • Smith.

  • We'll type INSERT INTOEmployeesand then, within parentheses, specify the columns

  • we want to add information into, separating them by a comma.

  • These columns areemployee number”, “birth datе”, “first name”, “last name”,

  • gender”, andhire date”.

  • Then, after the keyword VALUES, and within parentheses, type the data values that will

  • be part of these columns, again separating them by a comma.

  • To see the data types of the values you will insert, you must check the data types of this

  • table's fields.

  • You can do this by going to the information section and looking at the data types in the

  • Columnstab.

  • The employee number is an integer; then you have two date values, two VARCHAR values,

  • and an ENUM.

  • Therefore, as you insert the new records, all values must be written in single quotes,

  • besides the employee number.

  • Great!

  • Let's assign the number 9-9-9-9-0-1, and a birth date of the 21st of April 1986.

  • This record will refer to John Smith, a male, who was hired on the 1st of January 2011.

  • To register this row in the data table, we'll need to execute the INSERT query.

  • All right, we see that we have one row affected.

  • Now, let's select the 10 employees with the largest employee numbers, to see if our

  • record will appear there.

  • Awesome!

  • Here he is!

  • John Smith is on the top!

  • Let me comment on this bizarre number, 9-9-9-9-0-1.

  • Obviously, this is not the next consecutive number in our super-long list of employees,

  • is it?

  • Remember that using such an employee number is a neat professional trick.

  • When data is being created to test the database system, the value inserted should be quite

  • high.

  • This way, it is obvious where the new data has been placed.

  • For this reason, we saw John Smith on the top of the employees list, with the highest

  • employee number.

  • Ok.

  • Great!

  • Let's move on.

  • Now, let me share another MySQL feature with you.

  • Contrary to anything we've said so far, it turns out that integers can be written

  • within quotes, too.

  • If you wish, you can try inserting another record with the employee number within quotes;

  • you'll see the data still registered as an integer.

  • The explanation why is that MySQL will automatically (or, as some professionals would say, transparently)

  • convert the string into an integer.

  • The best practice is to avoid writing integers within quotes, since this conversion consumes

  • time and prevents the smooth operation of more advanced MySQL features.

  • To conclude, the idea is that, yes, you could write an integer within quotes, but this is

  • not considered a best practice.

  • That's why we would like to give you the following piece of advice.

  • Please remember to type integers as plain numbers, without using quotes.

  • Ok.

  • Perfect.

  • Let's go ahead with the INSERT clause.

  • Usually, database administrators follow the order of columns in terms of the way they

  • appear in a given data table.

  • however, on certain occasions they prefer inserting data values in a different order.

  • MySQL allows for such types of data insertion.

  • For instance, should we create a data record about Patricia Lawrence, we could insert her

  • birth date first, then her employee number, and then cite the rest of the values in the

  • designated order.

  • Let's see if it works

  • One hundred percent!

  • The last row we created contains information about Patricia Lawrence!

  • So, please keep in mind that we must put the VALUES in the exact order we have listed the

  • column names.

  • Fantastic!

  • Ok.

  • Let's take a look at an interesting feature of the INSERT syntax.

  • Technically, the first pair of parentheses, along with the column names between them,

  • can be omitted.

  • Only INSERT INTO, the table name, and the VALUES parts are mandatory.

  • If you omit it, in the VALUES section you will have to specify as many data values as

  • there are columns in the data table.

  • Furthermore, you will have to add them in the same order in which they appear in the

  • table.

  • For example, if we use this structure and try to create a record in theEmployees

  • table with only four instead of all six columns, MySQL will display an error.

  • If we try to add six data values to the new record, all of them in the correct order,

  • the query should work fine.

  • Let's check.

  • Good.

  • The new piece of information is there.

  • Right, soIn this lesson, we will show you another way

  • to insert data into a table.

  • The relevant syntax is: INSERT INTO, table name, and column names

  • in parentheses.

  • Then comes the new part.

  • You can use a classical SELECT statement to retrieve information from some other table

  • and insert it into this table here.

  • It doesn't have to be the entire data from that other tableby using WHERE, you should

  • be able to set conditions that would refine the data to be copied.

  • Let's see an example.

  • First, check the columns of thedepartmentstable.

  • We have two columnsdepartment code and department name.

  • So, the next thing to do is create another table, calleddepartments duplicate”,

  • shortening it todepartments underscore D.U.P.”

  • It will be a replica of thedepartmentstable we just saw here.

  • Our task is to import all the data fromdepartmentsinto its duplicate.

  • Ok!

  • The code we'll need to create the copy table is CREATE TABLEDepartments Duplicate”,

  • open parentheses, “department number”, CHAR type of 4, NOT NULL constraint added,

  • thendepartment name”, VARCHAR of 40, NOT NULL constraint again, close parentheses.

  • Executeand then refresh the schemas section in Workbench.

  • Here we are!

  • The new table has been added to the list.

  • Furthermore, thedepartment numberanddepartment namecolumns are visible,

  • too.

  • Great!

  • To verify that this is still an empty table, we'll have to select all the information

  • from it.

  • You see?

  • There is no information.

  • Now is the time to implement the structure we introduced in this lecture.

  • We will INSERT INTO thedepartment numberanddepartment namecolumns from the

  • Departments duplicatetable everything we can select from theDepartmentstable.

  • Please note that sinceDepartmentscontains the same number and type of columns, it is

  • not necessary to add specific conditions to the data retrieved from that table.

  • Hence, the star wildcard character will do the job properly.

  • Right!

  • So, let's run this query.

  • Ok!

  • The code was executed with no errors.

  • Let's see if the operation was performed correctly.

  • It was.

  • Most definitely!

  • We see that the newly createddepartments duplicatetable has been filled with information

  • from thedepartments table”.

  • That's awesome!

  • In other words, our job is done!

  • I'd like to wrap up this lecture with a reminder about MySQL constraints.

  • We barely mentioned them in this section.

  • However, you know that unless the new data you are inserting satisfies the constraints

  • that have already been set in the database, MySQL will always show an error.

  • For simplicity, we carefully designed our code, and such errors were avoided.

  • In other words, we ensured we did not insert data that does not satisfy the existing constraints.

  • Nevertheless, keep in mind that complying with constraints is essentialthey will

  • always play a major role when inserting data.

  • Thank you for watching!

Let's practice inserting a few records in theEmployeestable.

Subtitles and vocabulary

Click the word to look it up Click the word to find further inforamtion about it