Session 3 BIZ (1).pptx - Data Resources Management Lecturer Yong Liu Contact me at [email protected] Objectives for class 3 HeidiSQL Import csv file to

Session 3 BIZ (1).pptx - Data Resources Management Lecturer...

This preview shows page 1 - 12 out of 79 pages.

Data Resources Management Lecturer: Yong Liu Contact me at: [email protected]
Image of page 1

Subscribe to view the full document.

Objectives for class 3 - HeidiSQL: Import csv file to MySQL table and export data to a database file (sql file) - Key and index: foreign key - Understanding the basics of Entity- Relationship Diagram (ERD) - MySQL Keyword: Select 2
Image of page 2
Import CSV File Into MySQL Table 1. Download ‘ Chile.csv ’ from MyCourse 2. Open the csv file to check its structure. What if the csv file is very large? R? 3. Create a new table in the DB with a structure that is consistent with the structure of the csv file. 4. Import the csv file to the new table: Tools Import CSV file 11/15/2019 3
Image of page 3

Subscribe to view the full document.

11/15/2019 4 ENCLOSED BY " One; two; "three; tres; trois" ; four ; fiv ESCAPED BY \\ One; two; "In France, one would say \"trois\""; four ; five - csv-import-query-terms-mean
Image of page 4
Clean the data of a table If the data is not imported correctly, you can drop the problematic data. Please using the following comment to clean the table delete from table_name; Outcome : a empty table will be returned 11/15/2019 5
Image of page 5

Subscribe to view the full document.

Export data to be a database file Tools Export database as SQL 11/15/2019 6
Image of page 6
Section 2: Key and Index 11/15/2019 7 - content/uploads/2014/05/iStock_000018288212Small.jpg A database without index! “In database systems, an index (IDX) is a data structure defined on columns in a database table to significantly speed up data retrieval operations. An index is a small copy of a database table sorted by key values. Without an index, query languages like SQL may have to scan the entire table from top to bottom to choose relevant rows.” Index of MySQL is similar to the index of a dictionary, or the address of a person in Finland when you want to find the person.
Image of page 7

Subscribe to view the full document.

Index Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially. 11/15/2019 8
Image of page 8
Index of MySQL 11/15/2019 9
Image of page 9

Subscribe to view the full document.

11/15/2019 10
Image of page 10
Operation on big data Performing a command in a table with 384,243 rows of data 11/15/2019 11 Commands Query duration select * from MyTable where id = 7278409 Duration for 1 query: 0,000 sec. select * from MyTable where title like '%This is a new product%‘ Duration for 1 query: 10,765 sec. select * from MyTable where title like '%This is a new product%‘ and id = 7278409 Duration for 1 query: 0,000 sec.
Image of page 11

Subscribe to view the full document.

Image of page 12
  • Fall '19

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern

Ask Expert Tutors You can ask You can ask ( soon) You can ask (will expire )
Answers in as fast as 15 minutes