Перейти к содержимому

Db browser for sqlite как пользоваться

  • автор:

Using DB Browser for SQLite

In Windows the installation of DB Browser does not create a desktop icon. To explicitly launch the application after installing it, use the windows button (bottom left of screen) and type in ‘DB Browser’ in the search bar and selecting the application when it appears.

DB Browser run

For Mac users, launch the spotlight search bar (press “command” + the space bar on your keyboard) and enter ‘DB Browser.’ Select the application when it appears.

DB Browser run

The Initial screen

The initial screen of DB Browser will look something like this, the panes may be in a different configuration;

DB Browser initial screen

A small menu system consisting of File, Edit, View and Help. Below the menu system is a toolbar with four options; New Database, Open Database, Write Changes and Revert Changes. Below the toolbar is a 4-tabbed pane for; Database Structure, Browse Data, Edit Pragmas and Execute SQL. Initially these will be quite empty as we haven’t created or opened a database yet. In general we will see how each of these are used as we go through the lesson with the exception of the Edit Pragmas tab which deals with system wide parameters which we won’t want to change.

On the right hand side there are two further panes, at the top is the Edit Database Cell pane which is grayed out. Below it is a 3-tabbed pane for DB Schema, SQL log and Remote. We are only really interested in the DB Schema tab.

Initial changes to the layout.

The overall layout of DB Browser is quite flexible. The panes on the right-hand side can be dragged and dropped into any position, the individual tabs on the bottom pane closed directly from the pane and re-opened from the menu View item.

We will make a couple of initial changes to the layout of the screen. These will be retained across sessions.

  1. From the View menu item un-select the ‘Edit Database Cell’ icon to the left of the text. This will make the pane close and the bottom pane will be expanded automatically to fill the space.
  2. a) On Windows, From the ViewEdit menu item select ‘preferences’ and select the Data Browser tab.
  3. b) On Mac, From the “DB Browser for SQLite” menu item select ‘preferences’ and select the Data Browser tab.

Data Browser Preferences

Towards the bottom there is a section dealing with Field colors. You will see three bars below the word Text, to the right there are in fact three invisible bars for the Background. Click in the area for the Background color for NULL. A colour selector window will open, select Red. The bar will turn Red. This is now the default background cell colour that will be used to display NULL values in you tables. We will discuss the meaning of NULL values in a table in a later episode.

You can now close the preference window by clicking OK.

Opening a database

One of the datasets we will be using this lesson is the SQL_SAFI database, a sample of a farm survey. If you do not already have a copy of this database you can download it from here.

To open the database in DB Browser do the following;

  1. Click on the ‘open database’ button in the toolbar.
  2. Navigate to where you have stored the database file on your local machine, select it and click open.

When you open the database, the ‘Database Structure’ tab on the left and the ‘DB Schema’ pane on the right will look very similar. However the ‘DB Schema’ pane is only there to allow you to see the details of the schema for the tables. In particular what tables are in the database and the fields and their types which are in each table.

The ‘Database Structure’ tab on the left allows you to initiate actions on the tables. If you right click on a table name in the ‘DB Schema’ pane, nothing happens. However, if you do the same in the ‘Database Structure’ menu you will be given a set of possible actions. These are the same actions that are available from the toolbar at the top of the tab.

Table Actions

If you select ‘Browse Table’, the data from the table is loaded into the ‘Browse Data’ pane from where it can be examined or filtered. You can also select the table you wish to Browse directly from here.

There are options for ‘New Record’ and ‘Delete Record’. As our interest is in analysing existing data not creating or deleting data, it is unlikely that you will want to use these options.

Running SQL Queries

We will be running queries extensively in future episodes. For now we will just provide an outline of the environment.

In the left hand pane if you select the Execute SQL tab, you will be presented with a three paned window and a small toolbar. The top pane is itself tabbed with the initial tab labeled ‘SQL 1’. This is the SQL editor pane into which you will type your queries.

Below is a simple example query and the results.

SQL Query results

Notice that the query has been written over multiple lines. This is commonly done to aid readability. The second pane has the tabular results, and the bottom pane has a message indicating how many rows were returned, how long it took and a copy of the SQL statement that was executed.

On the toolbar at the top there are eight buttons. Left to right they are:

  • Open Tab (creates a new tab in the editor)
  • Open SQL file (allows you to load a prepared file of SQL into the editor — the tab takes the name of he file)
  • Save SQL file (allows you to save the current contents of the active pane to the local file system)
  • Execute SQL (Executes all of the SQL statements in the editor pane)
  • Execute current line (Actually executes whatever is selected)
  • Save Results (Either to a CSV file or as a database view. We will look at views in a later episode)
  • Find (Text in the editor window)
  • Find & Replace (Text in the editor window)

Because it is possible to have and execute multiple SQL statements in the same editor pane, each must be terminated with a ‘;’. If you only have a single statement you don’t need it, but it might be considered best practice to always include it.

The pane below the editor is the Results pane. The results of running your query will appear here in a simple tabular format. The bottom pane is for messages about the execution, either an error message or an indication of how many rows were returned by the query.

Explore another Table

    1. What would be the SQL query to select all the entries from «crops_rice_old»? 2. How many entries are in the «crops_rice_old» table? 3. What information do you think «Plot_Id» represents?

Solution

Creating a database

As well as opening (connecting) to existing databases it is also possible to create new SQLite databases and tables using DB Browser. To create a database click the New Database button from the main toolbar (also available from the File menu). You will initially be asked for a name for the database and where you want to save it. It is saved as a single file. You can choose your own extension but ‘sqlite’ is recommended. If you do not provide a default, then a ‘.db’ extension will be used. Although the new database is empty, in that there are no tables in it, the .sqlite file itself is not empty.

Once you have saved the database file the Create Table wizard will open allowing you to create a table. You can cancel this as we will be going through the create table process in a later episode.

Write Changes & Revert Changes

Much of our SQL work involves looking at existing data using SQL queries and possibly writing out the results to a CSV file, in general we will not be changing the contents of the database.

However if, during your DB Browser session, you were to create or delete a table or create a view, then the changes are not automatically written to the database file.

Создание SQLite базы данных в DB Browser for SQLite

Базы данных SQLite очень удобны для работы с небольшими объемами данных как в Windows приложениях, так и в Android приложениях. В статье говорится, как создавать и подготавливать базы данных заранее.

Особенно SQLite удобен тем, что не нужно поднимать сервер СУБД перед использованием в приложениях.

Установка DB Browser for SQLite

Итак, скачиваем версию без установщика DB Browser for SQLite ( SQLiteBrowser ): http://sqlitebrowser.org:

Скачивание установщика

Просто распаковываем архив. В нем будет два EXE файла:

Файлы в распакованной папке

Файл DB Browser for SQLite.exe представляет обычную версию программы, а файл DB Browser for SQLCipher.exe позволяет работать с зашифрованными базами данных. И во второй программе есть вот такой пункт:

Возможность шифрования базы данных

Окно программы

Создание базы данных

Создадим простую базу данных database с одной таблицей и тремя столбцами:

Создание новой базы данных

Выбор имени файла новой базы данных

Появится окно создания таблицы в программе:

Открытая база данных

Создадим таблицу People :

Создание таблицы People

Ввиду того, что вдруг база данных будет использоваться в Android, то первым полем задавайте идентификатор по имени _id :

Создание поля _id

Добавим, например, поля Name и Age :

Создание полей базы данных

Структура базы данных

Во вкладке Данные можем вносить данные:

Вкладка Данные

Ввод первой строчки данных

Сохранение базы данных

У вас есть готовая база данных SQLite, которую можете использовать, где вам нужно:

Сохраненный файл базы данных

Статья обновлена 2019-08-05

  • SQLite
  • Базы данных
  • blog
  • it
  • programming

Базы данных SQLite очень удобны для работы с небольшими объемами данных как в Windows приложениях, так и в Android приложениях. В статье говорится, как создавать и подготавливать базы данных заранее.

Базы данных SQLite очень удобны для работы с небольшими объемами данных как в Windows приложениях, так и в Android приложениях. В статье говорится, как создавать и подготавливать базы данных заранее.

Takeaways

Congrats! If you made it here, you now have a pretty good idea of how to use SQLite Browser. This page is also a great reference to keep handy in case you forget how to do something like change the data type, or import tables.

Filed under: SQL by Boxplot Analytics
No Comments »

Understanding JOINs in SQL

Joins in SQL

Joins are one of the most important (if not THE most important) concepts in SQL. If you take the time to solidly understand how joins work, you’ll be in an excellent place for writing queries. So, let’s dive in!

Join Definitions

Joining tables in SQL is a way of combining them. It is directly comparable to a VLOOKUP in Excel, so if you are familiar wtih VLOOKUPs you already understand how joins work. For example, let’s say you have a table that contains orders placed by your customers (each row in that table represents a unique order). Then you have a second table that is only items that have been returned by your customers (each row in that table represents a unique order too, but not all orders are returned so the table has fewer rows).

Orders Table

order_id product cost_per_unit total_amount profit shipping_method
1 A 5 70 5 Air
2 B 10 120 97 Air
3 C 15 150 135 Air
4 A 5 20 10 Air
5 B 10 110 96 Air
6 C 15 30 5 Air
7 A 5 45 9 Air
8 B 10 150 64 Air
9 C 15 210 63 Air
10 A 5 65 44 Air
11 B 10 120 76 Air
12 C 15 30 18 Air
13 A 5 20 19 Ground
14 B 10 100 59 Ground
15 C 15 30 14 Ground
16 A 5 55 15 Ground
17 B 10 10 1 Ground
18 C 15 105 28 Ground
19 A 5 75 37 Ground
20 B 10 50 41 Ground
21 C 15 15 9 Ground
22 A 5 30 22 Ground
23 B 10 120 117 Ground
24 C 15 195 166 Ground
25 A 5 20 1 Ground

Returns Table

id amount_returned Reason
15 10 Broken
2 115 No Reason Given
24 85 Unsatisfied
7 15 Unsatisfied
12 20 Unsatisfied
9 130 Unsatisfied
17 10 No Reason Given
21 15 No Reason Given
18 70 Broken
17 10 Broken

You can’t just copy and paste these tables next to each other to figure out what was returned, because the rows won’t match up. You need a way of comparing each row in the orders table to the returns table to see if a match exists, and then bring back the information from the returns table that you’re interested in. Enter joins!

Let’s take a look at join syntax first:

Notice the ON statement, which is required for every join. You need to pick a column from each table that you want SQL to compare in order to join them together, and that’s what the ON statement is specifying. This example says compare the id column in table_1 to the id column in table_2 when trying to match up rows between the two tables during the join.

How do you choose which colunns to use for the ON statement? Generally you want to pick a column that uniquely identifies each row, like an id number. The two columns don’t have to have the exact same name, but they need to be the same type (for example, integer or text) and represent the same thing. The order_id columnn from the orders table above is the same thing as the id column in the returns table – they both represent the id of the order.

There are four types of joins: left, right, inner and outer. The right join functionally does the same thing as the left join, so many SQL programs are choosing not even to support it anymore. For that reason, we won’t be covering it in this article.

As usual, we’ll start with a basic example. Say you have a database with the following two tables in it:

nutrition1

id name calcium serving_size weight
1 apples 10 1 ea 138
2 asparagus 22 1/2 cup 90
3 avocado 19 1 ea 173
4 bamboo shoots 10 1 cup 131
5 banana 7 1 ea 114
6 beets 9 1/2 cup 85
7 blackberries 46 1 cup 144
8 Blueberries raw 9 1 cup 145
9 broccoli 205 1 spear 180
10 brussels sprouts 56 1 cup 156

nutrition2

id percent_water energy protein food_type
1 84 80 0.3 FRUITS
2 92 22 2.3 VEGETABLES AND LEGUMES
3 73 305 4 FRUITS
4 94 25 2.3 VEGETABLES AND LEGUMES
5 74 105 1.2 FRUITS
11 90 94 2.4 FRUITS
12 88 31 0.7 VEGETABLES AND LEGUMES
13 92 15 1.2 VEGETABLES AND LEGUMES
14 95 6 0.3 VEGETABLES AND LEGUMES
15 81 49 0.8 FRUITS

The nutrition1 table contains information about foods, and the nutrition2 table contains different information about foods. We’re going to join on the id column (this will be our ON statement) because it’s the unique identifier for each row in both of the tables. So, take note that nutrition1 has id numbers 1-10, but nutrition2 has id numbers 1-5 and then skips to 11-15.

Inner Joins

When you inner join two different tables together, SQL will compare the columns you specify in the ON statement and only keep rows that those columns have in common. Looking at our nutrition tables, the only id numbers that the two have in common are 1 through 5. So when we run this query:

we’ll get this result:

id name calcium serving_size weight id percent_water energy protein food_type
1 apples 10 1 ea 138 1 84 80 0.3 FRUITS
2 asparagus 22 1/2 cup 90 2 92 22 2.3 VEGETABLES AND LEGUMES
3 avocado 19 1 ea 173 3 73 305 4 FRUITS
4 bamboo shoots 10 1 cup 131 4 94 25 2.3 VEGETABLES AND LEGUMES
5 banana 7 1 ea 114 5 74 105 1.2 FRUITS

Cool! Now we have more information than we previously did about apples, asparagus, etc. Notice that the result displays 1-5 from nutrition1 , lined up next to those columns 1-5 from nutrition2 , and everything else is gone . No other rows appear in the result because inner joins only keep what the two tables have in common, and nothing else.

Left Joins

When you perform a left join, SQL keeps everything from the left table, no matter what, and then only pulls in information from the right table that matches the left based on the columns you specify in the ON statement. So when we run this query:

we’ll get this result:

id name calcium serving_size weight id percent_water energy protein food_type
1 apples 10 1 ea 138 1 84 80 0.3 FRUITS
2 asparagus 22 1/2 cup 90 2 92 22 2.3 VEGETABLES AND LEGUMES
3 avocado 19 1 ea 173 3 73 305 4 FRUITS
4 bamboo shoots 10 1 cup 131 4 94 25 2.3 VEGETABLES AND LEGUMES
5 banana 7 1 ea 114 5 74 105 1.2 FRUITS
6 beets 9 1/2 cup 85 NULL NULL NULL NULL NULL
7 blackberries 46 1 cup 144 NULL NULL NULL NULL NULL
8 Blueberries raw 9 1 cup 145 NULL NULL NULL NULL NULL
9 broccoli 205 1 spear 180 NULL NULL NULL NULL NULL
10 brussels sprouts 56 1 cup 156 NULL NULL NULL NULL NULL

For this result, we have the entire nutrition 1 table (id numbers 1-10) and lined up next to it the rows from the nutrition2 table that matched base on the id (just 1-5). Notice the NULL values. The nutrition2 table didn’t have rows for id numnbers 6-10, but we had to keep those rows because the definition of a left join tells us to keep everything from the LEFT table, so the nutrition2 columns for id numbers 6-10 get filled in with NULLs.

Outer Joins

Finally, let’s take a look at what an outer join does. Here’s the query:

id name calcium serving_size weight id percent_water energy protein food_type
1 apples 10 1 ea 138 1 84 80 0.3 FRUITS
2 asparagus 22 1/2 cup 90 2 92 22 2.3 VEGETABLES AND LEGUMES
3 avocado 19 1 ea 173 3 73 305 4 FRUITS
4 bamboo shoots 10 1 cup 131 4 94 25 2.3 VEGETABLES AND LEGUMES
5 banana 7 1 ea 114 5 74 105 1.2 FRUITS
6 beets 9 1/2 cup 85 NULL NULL NULL NULL NULL
7 blackberries 46 1 cup 144 NULL NULL NULL NULL NULL
8 Blueberries raw 9 1 cup 145 NULL NULL NULL NULL NULL
9 broccoli 205 1 spear 180 NULL NULL NULL NULL NULL
10 brussels sprouts 56 1 cup 156 NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL 11 90 94 2.4 FRUITS
NULL NULL NULL NULL NULL 12 88 31 0.7 VEGETABLES AND LEGUMES
NULL NULL NULL NULL NULL 13 92 15 1.2 VEGETABLES AND LEGUMES
NULL NULL NULL NULL NULL 14 95 6 0.3 VEGETABLES AND LEGUMES
NULL NULL NULL NULL NULL 15 81 49 0.8 FRUITS

Outer joins keep everything, no matter what, and line up rows that match. Here, we have the entire nutrition1 table, and next to it the nutrition2 table but broken up so that it matches the id numbers of nutrition1 properly.

Takeaways

Understanding the definitions of the joins and how each fundamentally works is very important in SQL. Usually, you’ll be working with so much data that you have to join the tables together in SQL and can’t just pull everything into Excel and do a VLOOKUP. Excel can only handle so many rows (a little over a million) and so many columns (a little over 16,000). That sounds like a lot but SQL databases can hold millions and with more and more data being generated and available every day, there’s a good chance that even at a small company you’ll be dealing with more information than can fit into Excel. Sometimes too, your data will fit into Excel but the VLOOKUP will be so computationally taxing that Excel will simply crash or go so slow it isn’t worth it. SQL is built for stuff like this, so it will compute joins much faster. Check out my understanding SQL blog post for a more in-depth discussino of what SQL is and when to use it.

Understanding Multiple JOINs in SQL

Multiple joins are one of the toughest SQL concepts – in this post we’ll decode them and review some common pitfalls.

One of the best ways to learn is with an example. If you’d like to follow along, you can download this zip file that contains the three tables as .csv files here, and import them into DB Browser for SQLite. Read my post on how to use SQLite Browser here.

Say you work on the marketing team at a software company. Your database has the following tables.

ad_info

Each row represents a unique campaign your company has run. All campaigns are included in this table.

fb_info

Each row represents a unique Facebook campaign. Only Facebook campaigns will show up in this table!

ad_results

Each row represents a unique campaign your company has run. All campaigns are included in this table.

Your boss asks you: what percentage of ad campaigns that were launched by the Europe or Australia teams and had success scores above 4 were Facebook campaigns?

To answer this question, we need to do a few things. Let’s break it down:

Filter results so we are only seeing European and Australian campaigns that had success scores above 4. We’ll do this in the WHERE clause. Don’t pay too much attention to this part for this example – we’re doing this to establish why we need all three tables, but the point of this example is to understand the joins. I’m not trying to trick anyone with the WHERE clause ��

Join the tables together so all the information is in one place.

Get the percentage of these specific campaigns that were Facebook campaigns. To do that, we’re going to count the number of rows in our result that have any information in the fb_information columns, and divide by the total number of rows in the result.

Okay, now we have all of the information we need to get started. You go back to your desk and you think “hmmmm…. I’m not sure how to do this.” We’re going to go through a few incorrect answers first before we look at the correct one so you can understand why the correct one is the right way to approach the problem.

Attempt 1: All Inner Joins

So, first you try all inner joins. Here’s a graphical representation of the joins:

A box represents the table
—- a line represents an inner join
and an arrow represents a left outer join.

And this is what the query looks like. I chose only to select the columns we need to answer the question, plus a few extras in the SELECT statement because if I selected all the columns, the table would be too large to fit easily into this webpage. But, for your own practice, you could do SELECT * if you want to see all the columns.

And finally, this is the result it produces:

Result:

ad_id launching_team fb_id impressions unique_reach unique_clicks engagement_rate success_score
9 Europe fb-7647 5346 3713 3054 0.822515486 7
15 Europe fb-8982 9603 9508 5075 0.533761043 6
16 Australia fb-3763 9997 6854 5107 0.745112343 5
22 Australia fb-2011 3636 2464 1614 0.655032468 6
27 Europe fb-7448 2677 1702 1394 0.819036428 7
33 Europe fb-1526 7135 4185 4143 0.989964158 5
39 Europe fb-6513 1018 862 699 0.810904872 7
40 Australia fb-3680 8755 5785 5212 0.900950735 8
52 Australia fb-1584 4808 4409 2750 0.6237242 5
124 Australia fb-3866 8283 8209 4372 0.532586186 6
130 Australia fb-2977 2964 2878 2556 0.888116748 10
135 Europe fb-8359 6316 5308 3758 0.707987943 8
136 Australia fb-9569 2174 2078 1813 0.872473532 7
142 Australia fb-1790 7966 5743 5186 0.903012363 8
147 Europe fb-2933 4202 3030 2887 0.952805281 5

Remember, we are looking for the number of rows with data in the Facebook columns ( fb_id , impressions , unique_reach , unique_clicks , engagement_rate ) divided by the total number of rows in the result. This result looks like 100% of the European and Australian campaigns with a success_score greater than 4 were Facebook campaigns because all of the rows in the result have values in the Facebook columsn.

That should set off a red flag! Whenever you get something like this (100%, 0 rows, etc.) you should double-check your work. Remember, the scariest thing in SQL is not an error, but an incorrect result that we think is correct! What actually happened here is we’ve eliminated all of the non-Facebook European and Australian campaigns with success scores greater than 4.

Before I explain why, there are a few very important things to understand.

When you are doing all inner joins or all outer joins, whether you are joining 3 tables or 300, the order of the joins does not matter. Think back to the definition of the joins (check out this blog post for a refresher). Inner joins only keep what the tables have in common. So it doesn’t matter if I switch the order of the tables, I’ll still get a result that only contins rows that all tables have in common. An outer join will keep all rows from all tables, no matter what. So again, it doesn’t matter if I switch the order of the tables because SQL will always keep all rows.

It’s not the way it looks. First, it takes the entire ad_info table and inner joins it to the facebook_info table, and gets that result. Then it takes THAT RESULT and inner joins it to the ad_results table! So it’s not ad_info joined to facebook_info , and then facebook_info joined to ad_results . It’s the RESULT of ad_info -Inner-Join- facebook_info joined to ad_results .

It’s like you’re creating a cumulative table (but not always one that’s getting larger, it dependso n the type of JOINs you are doing). This makes multiple joins more difficult, because SQL will not show you the result of each join. You have to have an idea of what each result looks like at each

Therefore, the reason we got 100% is because the only thing these tables have in common is Facebook campaigns, since that is all that is in the facebook_info table. So, all inner joins is not the way to go.

Attempt 2: Mixed Joins Starting with facebook_info

Now let’s say we start with the facebook_info table, do a left outer join on the ad_info table next, and then an inner join to the ad_results table.

Here’s the query:

Result:

ad_id launching_team fb_id impressions unique_reach unique_clicks engagement_rate success_score
9 Europe fb-7647 5346 3713 3054 0.822515486 7
15 Europe fb-8982 9603 9508 5075 0.533761043 6
16 Australia fb-3763 9997 6854 5107 0.745112343 5
22 Australia fb-2011 3636 2464 1614 0.655032468 6
27 Europe fb-7448 2677 1702 1394 0.819036428 7
33 Europe fb-1526 7135 4185 4143 0.989964158 5
39 Europe fb-6513 1018 862 699 0.810904872 7
40 Australia fb-3680 8755 5785 5212 0.900950735 8
52 Australia fb-1584 4808 4409 2750 0.6237242 5
124 Australia fb-3866 8283 8209 4372 0.532586186 6
130 Australia fb-2977 2964 2878 2556 0.888116748 10
135 Europe fb-8359 6316 5308 3758 0.707987943 8
136 Australia fb-9569 2174 2078 1813 0.872473532 7
142 Australia fb-1790 7966 5743 5186 0.903012363 8
147 Europe fb-2933 4202 3030 2887 0.952805281 5

We get the exact same result as the inner joins. Why? Again, think back to the definition of the joins. A left join keeps everything from the left table (no matter what) and then only pulls in information from the right table that matches. So here, we kept everything from the facebook_info table, and only pulled in information from the ad_info table that matched as our first join. And of course, as we saw from the first example, the only rows from ad_info that match facebook_info are Facebook campaigns. Then when we take that result of the first join (which only contains Facebook campaigns) and join it to the ad_results table, we still remove the non-Facebook campaigns because the inner join only keeps what’s common between the result of the first join and the ad_results table.

Attempt 3: Mixed Joins Starting with ad_info

Finally, let’s try starting with the ad_info table, left outer joining the facebook_info table, and then inner joining the ad_results table.

Result:

ad_id launching_team fb_id impressions unique_reach unique_clicks engagement_rate success_score
9 Europe fb-7647 5346 3713 3054 0.822515486 7
15 Europe fb-8982 9603 9508 5075 0.533761043 6
16 Australia fb-3763 9997 6854 5107 0.745112343 5
22 Australia fb-2011 3636 2464 1614 0.655032468 6
27 Europe fb-7448 2677 1702 1394 0.819036428 7
33 Europe fb-1526 7135 4185 4143 0.989964158 5
39 Europe fb-6513 1018 862 699 0.810904872 7
40 Australia fb-3680 8755 5785 5212 0.900950735 8
52 Australia fb-1584 4808 4409 2750 0.6237242 5
63 Europe NULL NULL NULL NULL NULL 6
69 Europe NULL NULL NULL NULL NULL 8
70 Australia NULL NULL NULL NULL NULL 10
75 Europe NULL NULL NULL NULL NULL 7
81 Europe NULL NULL NULL NULL NULL 8
82 Australia NULL NULL NULL NULL NULL 10
87 Europe NULL NULL NULL NULL NULL 6
88 Australia NULL NULL NULL NULL NULL 10
94 Australia NULL NULL NULL NULL NULL 10
99 Europe NULL NULL NULL NULL NULL 6
100 Australia NULL NULL NULL NULL NULL 7
105 Europe NULL NULL NULL NULL NULL 5
111 Europe NULL NULL NULL NULL NULL 8
124 Australia fb-3866 8283 8209 4372 0.532586186 6
130 Australia fb-2977 2964 2878 2556 0.888116748 10
135 Europe fb-8359 6316 5308 3758 0.707987943 8
136 Australia fb-9569 2174 2078 1813 0.872473532 7
142 Australia fb-1790 7966 5743 5186 0.903012363 8
147 Europe fb-2933 4202 3030 2887 0.952805281 5

That gets us the result we want! We can see that only 15 out of the 28 European and Australian campaigns with success rates greater than 4 were Facebook campaigns. That’s 53.57%, not 100%!

Takeaways

Okay, let’s review!

If you have all inner joins or all outer joins, the order doesn’t matter. But as soon as you start mixing and matching join types, the order can change your result. It’s important to understand the definitions of the joins, and also imagine the result of each join as you do it, because that result is what’s being joined to the next table. Think about what rows may be lost or gained with each join.

What if you didn’t know that the facebook_info table only contained rows Facebook ad compaigns? You might have stopped at the first attempt (which is incorrect!). You could be SQL Syntax Expert of the Universe, but if you don’t know the data, you’ll produce wrong answers.

It’s critically important to know what type of information can show up in any given table, any given column, and if you’re working across multiple servers, any given database or server! Think of another example: let’s say you’re looking at geographic data and there’s a STATE column. Throughout the history of your company, some people coded Pennsylvania as “PA”; others coded it as “Pa.”; still others coded it as “Penn.”, etc. SQL doesn’t know these are all Pennsylvania! So if you are tryign to query the database for all customers living in Pennsylvania and you don’t incorporate this knowledge into your query, you’ll get the wrong answer. Your query may be syntactically perfect, but you’ll still get the wrong answer.

This doesn’t apply 100% of the time, but it’s a good rule of thumb to get started if this is the first time you are working with multiple joins. As we saw in Attempt 2, when you start with the smaller table, it’s impossbile to get that data back without doing right or outer joins. Right joins are not supported by many SQL programs anymore since it is the same as the left join but backwards, and outer joins often will bring back more information than you need. In my experience, left and inner joins are the most common types of joins and what you’ll mostly use in practice. But, you *could* do this exact problem differently using outer or right joins.

People get frustrated when they see errors in SQL, but getting an error is WAY BETTER than getting an incorrect result and thinking it’s correct. Just because SQL produces a result, doesn’t mean it’s what you originally wanted. You have to understand all of the things mentioned above (the definitions of the joins, how multiple joins work, what your data looks like. etc.) to ensure you are getting the correct answer.

Phew! You made it to the end, congratulations! Comments are welcome, let me know if you have questions, spot a typo, want to suggest a correction, or simply post your thoughts!

Db browser for sqlite как пользоваться

Одним из популярных графических клиентов для SQLite является программа DB Browser for SQLite , которая доступна по адресу https://sqlitebrowser.org/.

Для установки графического клиента перейдем на страницу загрузок https://sqlitebrowser.org/dl/, где мы можем найти различые варианты пакетов для разных операционных систем и архитектур:

Графический клиент DB Browser for SQLite

Например, в моем случае ОС — Windows 64x, поэтому я выбираю пакет DB Browser for SQLite — Standard installer for 64-bit Windows , который представляет установщик программы для 64-битной Windows.

После установки запустим программу:

Программа установки DB Browser for SQLite

Нам откроется следующее окно

DB Browser for SQLite

В рамках данной программы для управления базой данных мы можем использовать как графические возможности, так и запросы SQL. Так, создадим базу данных. Для этого нажмем на кнопку New Database (или через меню File -> New Database ). Далее нам будет предложено указать место и имя новой бд. Например, в моем случае файл базы данных будет называться people.db

создание базы данных в DB Browser for SQLite

Впоследствии созданную базу данных можно будеть открыть с помощью кнопки Open Database или через меню File -> Open Database .

После этого нам будет предложено создать первую таблицу

создание таблицы в DB Browser for SQLite

На этом окне в поле Table введем в качестве названия таблицы Users .

На вкладке Fields , которая содержит определения столбцов, нажмем на кнопку Add . И после нажатия в таблицу чуть ниже кнопки будут добавлять определения столбцов таблицы. Для первого столбца укажем в качестве имени Name , а в качестве типа — TEXT . Для второго столбца укажем в качестве имени Age , а в качестве типа — INTEGER .

То есть у нас будет таблица User со столбцами Name и Age.

После определения столбцов нажмем на кнопку OK, и программа сгенерирует новую таблицу:

управления таблицей в DB Browser for SQLite

Для управления данными перейдем на вкладку Browse Data . По умолчанию у нас нет никаких данных

данные таблицы в DB Browser for SQLite

Добавим в таблицу одну строку. Для этого на панели инструментов нажмем на кнопку и в добаленную строку введем какие-нибудь данные:

добавление данных в таблицу в DB Browser for SQLite

После добавления данных (впрочем как и после их изменения и удаления) для подтверждения изменений нажмем на панели инструментов на кнопку Write Changes (или на пункт меню File -> Write Changes ). И данные будут записаны в базу данных.

Выполнение запросов SQL

Также для упавления таблицами и данными можно использовать запросы SQL. Для написания и выполнения запроса SQL перейдем на вкладку Execute SQL

Execute SQL в DB Browser for SQLite

После этого в центральной части программы откроется окно для ввода скрипта SQL. Введем в него следующую команду:

Здесь создается таблица users2, которая фактически аналогична ранее созданной таблице users. Она также имеет два столбца name и age. И в нее добавляюся две строки.

Для выполнения этого кода нажмем на синюю стрелку на панели инструментов. И после запуска кода SQL мы сможем увидеть отчет о его выполнения

Выполнение скрипта SQL в DB Browser for SQLite

После добавления мы можем получить данные. Для этого введем следующий код:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *