Effective PyCharm Transcripts
Chapter: Databases
Lecture: Querying data in the SQL console
Login or
purchase this course
to watch this video and the rest of the course contents.
0:01
Now that we have an understanding of our data from the visualizations,
0:05
let's talk about querying it, let's focus just on the album, pick any real table. So we saw that we can expand it and see what it's made of,
0:14
we could right click and say jump to console so this is going to open up a way in which we can run queries against any of the tables
0:22
so let's write a real simple one, select * from, hit space, watch this I love it, so that album, we got track
0:31
and it also gives us like group by and whatnot we're going to say select * from album and that's probably what you want to do to just see the data
0:39
but maybe you'll ask, some way you want to order it so you could say order by, or you could say where, name, now look at this,
0:47
this is auto completion inside the table schema, that's pretty awesome we could say name is LIKE --it's like a substring search-- snake
0:58
one of the albums has snake in its name, so let's go and run this and down here, you can see we get our one output Year of The Snake
1:06
because the name has snake in it. Now, let's take away this, this where clause, because while it's great, it doesn't show us all the data.
1:13
Here you can see we get just all the pieces and is really cool at the top like hey look prices index
1:20
you can tell that right from the column right there this sort of visual tells you whether you are going to do a query
1:27
based on an index or you are going to do it just— has preview there is no index, so that might be super slow if you had lots of data,
1:34
it didn't really matter if you have like two records. Now we can also use this to modify data
1:39
but it's not 100% obvious like let's change the price here run it again, see the price is 9.99 we come over here and let's say we want this to be 19.99
1:49
so I double clicked it and I hit enter, awesome, let's run it again, it's 9.99 again, what's wrong with this thing, it's broken.
1:56
Not really. So you can edit this data, we can go down here like check has preview, things like that, but unless we push this button,
2:05
save changes back to the database, nothing's going to happen; but if I do, notice little grayness and whatnot of those elements went away,
2:16
run it again now that changes are sticking. That's pretty awesome isn't it? Come over here you can actually see what's running
2:25
select * like, so you can see these down here, that pretty cool this little output there, we've got to change this,
2:32
now the final thing to look at over here on the right, this is actually pretty powerful, so see this tab here
2:38
so we get tab separated, what does this mean, there's all these different things we can get out of here,
2:44
I could get comma separated values sort of export this as that, I could go over here and actually say, I would like the sql statements
2:55
that will insert these records into the database, check this out. Now to make anything interesting happen, we have to press this button
3:03
and you could save it to a file probably you will do that a lot, but I'm just going to do the clipboard. Now I'll just dump those up here, like so
3:13
and notice, insert into album id such and such, such and such values— what they are, right this actually generates the statements that we would need
3:27
if we wanted to take that data and reinsert it into another database, what else over here, we have updates, we have HTML table,
3:34
all sorts of cool stuff that we can do. Now, the DDL is data definition/description language, if we click this, it's actually going to show us
3:44
the sql script that we would need to run to recreate exactly what this table is so what the columns are, what types they are,
3:52
whether they are nullable, whether they are primary keys, as well as the uniqueness constraints and indexes and all that stuff.
4:00
So if you want to generate it, DDL, that's a pretty awesome feature as well, so you can really do a lot of stuff with scripts over here
4:08
and also if we wanted to do comma separated values we could do that to a clipboard and just put it like so,
4:14
now we have csv version rather than insert version of the data.