create view show_lists_user_about_id as select users.user_id as user_id1,user_name,user_pass,posts.post_id,posts.user_id from users left join posts on users.user_id = posts.user_id where posts.user_id = users.user_id;
SELECT * FROM show_lists_user_about_id;
https://dev.mysql.com/doc/refman/5.7/en/join.html
https://dev.mysql.com/doc/refman/5.7/en/create-view.html
Edit:
create view who_wrote_how_much_posts as
select count(*),posts.user_id
from posts,users
where posts.user_id = users.user_id
group by posts.user_id;
select * from who_wrote_how_much_posts;
Result:
Jeśli chcesz mieć inną nazwę zamiast count(*), to zastosuj alias, np. taki: (...)SELECT count(*) as `quantity`(...).
https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html