How to create correct SQL query to get data from two tables

I am new to SQL and databases.. I need to create an SQL query to find

all posts that have tags with tagname='t1'

relation between posts and tags is many-to-many

So, I created the schema and some insert statements as below:

create table Posts(
p_id int ,
p_name varchar 
);

create table Tags(
t_id int ,
t_name varchar 
);

Since many-to-many relation needs a join table, I created that as well

create table Posts_Tags(
p_id int,
t_id int 
);

insert into Posts values('1','P1' );
insert into Posts values('2','P2' );
insert into Posts values('3','P3' );

insert into Tags values ('1','t1');
insert into Tags values ('2','t2');
insert into Tags values ('3','t3');
insert into Tags values ('4','t4');
insert into Tags values ('5','t5');

insert into Posts_Tags values('1','开发者_JAVA技巧1' );
insert into Posts_Tags values('1','2' );
insert into Posts_Tags values('2','1' );
insert into Posts_Tags values('2','3' );
insert into Posts_Tags values('3','5' );

Now, how should I make the SQL query to get all posts that have tags with t_name='t1'? Is it possible to query only the two tables Posts and Tags and get the correct result? Or should I use Posts_Tags table also?

Forgive me but I have little knowledge of rdbms and SQL.

No - you need the three tables:

select p.*
from Posts p
join Posts_Tags pt on pt.p_id = p.p_id
join Tags t on t.t_id = pt.t_id
where t.t_name = 't1';

This table layout is the correct way to create a mant-to-many relationship between Posts and Tags. Posts_Tags is called an associative table.

You need to join tags with post_tags to find the ID's of posts with a t_name of "t1". i.e.

SELECT 
    p_id 
FROM 
    Post_Tags INNER JOIN Tags ON Post_Tags.t_id=Tags.t_id 
WHERE 
    t_name='t1'

If you want more detail than just the post ID you will need to join all 3 tables like:

SELECT 
    p.p_id, p.p_name
FROM 
    Posts p INNER JOIN Post_Tags ON p.p_id=Post_Tags.p_id
       INNER JOIN Tags ON Post_Tags.t_id=Tags.t_id 
WHERE 
    t_name='t1'

You need the three tables. Here's the query you should use (untested) :

SELECT p_name
FROM Posts AS p
INNER JOIN Posts_Tags AS pt ON pt.p_id = p.p_id
INNER JOIN Tags AS t ON pt.t_id = t.t_id
WHERE t.t_name = 't1';