Placeholder Image

字幕列表 影片播放

  • Databases are amazing creatures.

  • They are obedient guardians of your data.

  • They don’t weigh very much, and they survive on a simple diet of electrons.

  • But they are not very interesting unless they are filled with data.

  • So how do you stuff a database with numbers and dates and other information?

  • The answer, my friends, is the INSERT command.

  • Today, we will learn how to insert data into a table one row at a time and multiple rows

  • at a time.

  • Insert final line here before we film the video

  • Today we will create a database for a social network calledChitter.”

  • This lean startup will be an innovative cloud based platform that will disrupt the social

  • network industry by using big data and machine learning to find a synergy between influencers

  • and thought leaders.

  • There will be targeted ads...

  • So how will we accomplish this?

  • By letting people impulsively post their thoughts online.

  • We will differentiate ourselves by adjusting the font size of each post according to the

  • length of the post.

  • The longer the post, the smaller the font.

  • The database behind Chitter will consist of several tables: a “chitter_usertable,

  • posttable, and a “followertable.

  • Thechitter_usertable will have the following columns: user_id .. username .. encrypted_password

  • .. email .. and date_joined.

  • The user_id column will be the auto-generated primary key of this table..

  • Theposttable will have the following columns: post_id .. user_id .. post_text .. and

  • posted_on.

  • The post_id column will also be an auto-generated primary key.

  • Finally, thefollowertable will identify who follows a particular user.

  • It will have two columns: user_id, and follower_id.

  • These 3 tables should be enough for us to begin improving the world through polite,

  • constructive online discourse.

  • Before we start, let us see how much data we have in our user table.

  • A clean slate.

  • Our start up really is just starting up

  • Now to add data.

  • To begin, let us insert data into thechitter_usertable.

  • To do this, use theINSERT INTOstatement followed by the table name.

  • Next, list the columns in parentheses for which you have data

  • Then the VALUES keyword

  • And then the data in parentheses.

  • The order of the data MUST match the order of the columns.

  • This is important.

  • Notice that we used theDEFAULTkeyword as the user_id.

  • This is because our database will generate this value for us.

  • Execute

  • To see that the data was successfully added, we will SELECT the user data once more.

  • Our data is there.

  • And notice that the user_id was created for us.

  • Let us add another user.

  • This time we will not specify values for all fields.

  • We start withINSERT INTOand specify the table.

  • This time, we will only insert values for the username and encrypted password.

  • Execute

  • And SELECT the rows to confirm success.

  • Success confirmed

  • As before, the user_id was generated for us.

  • And the email and date_joined fields are NULL because we did not provide values.

  • seconduserbetter not forget their password...

  • Can it get any better than this?

  • Yes, yes it can

  • Because with theINSERT INTOstatement

  • you can add more than one row in a single query.

  • The trick is to use commas.

  • Fortunately, most keyboards come equipped with a comma button.

  • Let us see how this is done.

  • This time we will create two posts.

  • As before, start with the statementINSERT INTOfollowed by the table name: post.

  • I have configured theposttable to automatically generate thepost_idand

  • theposted_onvalues.

  • All we need to do is provide theuser_idand the contents of the post.

  • The first row will beHello World!”.

  • Add a comma, and then provide the values for the next row.

  • And execute...

  • And confirm

  • And celebrate...

  • Surprise.

  • It is me again, and it is NOT the end of the video.

  • I’ve negotiated more screen time with the producers.

  • The INSERT INTO statement enables you to add one or more rows into a table.

  • Ask yourself this - which is better: A single query inserting 6 rows?

  • Or 6 queries each inserting a single row?

  • Or maybe multiple queries adding 1 plus 2 plus 2 plus 1 rows?

  • Or perhaps 1 plus 2 plus 1 plus 1 … plus 1.

  • We had better check.

  • To determine which is faster, we will insert 10000 posts into theposttable in two

  • different ways.

  • For this demonstration, we will use Python with a Postgres database.

  • Do not panic if you are unfamiliar with Python..

  • Or Postgres.

  • Focus on the SQL...

  • Our objective is to see which is faster: inserting 10000 rows separately,

  • or in a single big query.

  • In order to talk to the database, we will use the popularpsyco PG2” module.

  • To time our work, we need to import the time module.

  • Let N be the number of rows we add in each batch.

  • Next, let us construct a list of “N” INSERT queries.

  • Each query will insert a single sentence into theposttable.

  • The other way to do this is with a single, big query that inserts N rows separated by commas.

  • To tidy up this big query, we need to remove the trailing comma and end it with a semicolon.

  • Next, load your super secret password

  • connect to the database

  • and create a cursor

  • First, we will insert 10000 rows individually.

  • Record the starting time.

  • Execute the query 10000 times.

  • Commit them to the database.

  • Record the stop time

  • And print a message with the time it took to complete all of the queries.

  • Now for the BIG query.

  • Again, record the start time.

  • Execute the query

  • Commit it to the database

  • Record the stop time

  • And print the results..

  • And finally, we demonstrate our responsible nature and close both the cursor

  • and database connection.

  • Now run

  • Inserting the data one row at a time is more than 20 times slower than using one, big INSERT

  • query with 10,000 values.

  • This is a “Shiningexample of how you

  • can optimize your queries

  • And as a “sanitycheck, you can confirm the data is in theposttable….

  • It most definitely is...

  • The INSERT INTO statement makes it a snap to add one or more rows to a table

  • in a single query.

  • But exercise caution.

  • When inserting large volumes of data, they way you word your queries can make a big impact

  • on how quickly they will run.

  • The example we saw was very similar to the story of the Tortoise and the Hare, except

  • it was completely different.

  • Instead of animals, we worked with SQL queries.

  • And while slow and steady may win a race in the forest, databases are different places

  • - all together

  • Databases are different places.

Databases are amazing creatures.

字幕與單字

單字即點即查 點擊單字可以查詢單字解釋

B1 中級

SQL INSERT INTO 語句 |¦| SQL教程 |¦| SQL for Beginners (SQL INSERT INTO Statement |¦| SQL Tutorial |¦| SQL for Beginners)

  • 14 0
    林宜悉 發佈於 2021 年 01 月 14 日
影片單字