Querying mySQL database Select, Delete, Insert and Update using SQLyog Step 6
There are basically 4 functions you will want to do to your database using your mySQL Enterprise Manager. You need to know how to insert data, how to delete data, how to retrieve data, and how to update data.
First, we need to insert something. Our scenerio goes like this:
There is a user named Bob Anthony Jones.
He enjoys reading science fiction books and one book is called Dragon Lance. The author of the book is called Tracy, and the publisher is White House Publishing.
We first should enter a category called Science Fiction.
You should enter your queries into the text area of your Enterprise Manager called query. You will run your query by pressing the green button in the top left corner of the SQLyog Enterprise Manager.
To insert Science Fiction into your bookcategory database, run this query:
insert into bookcategory (category) values ("Science Fiction")
To view that your data was inserted correctly, you can run the following select statement to view that the row is inserted correctly.
select * from bookcategory
In the insert statement, you will first have the name of the columns that you are putting data, and then the values that you are inserting. You do not have to insert an id because the id is auto incremented. mySQL will assign an id to the row.
The select statement retrieves the data from bookcategory and displays it. The * is a wild card and means that it will retrieve all the columns for this table. If you want to select a specific column, you can enter it instead of the * and seperate it by commas like this:
select Id,Category from bookcategory
insert into book(BookName,BookAuthor,BookPublisher,BookCategoryId) values ('Dragon Lance','Tracy','White House Publishing',1)
Again you can check that the row was inserted correctly by typing:
select * from book
The id for BookCategoryId is the id from the bookcategory table. That is the foreign key to reference the relationship to the bookcategory table. That key will tell you that the book has a category of science fiction.
Now we should insert our user like this:
insert into users(FirstName,LastName,MiddleName,Email) values ('Bob','Jones','Anthony','')
Again you can view the data by doing a select statement. To input the email address, you can enter '' for empty or if the row allows nulls, you can leave the column blank and the system will insert null. This statement will insert null into the column Email:
insert into users(FirstName,LastName,MiddleName) values ('Bob','Jones','Anthony')
We have to insert one row into the userbook table that will give the relationship between books and users.
insert into userbook (bookid,userid) values (1,1)
This query might give you an error. My user table inserted a 0 for the id in the first row. If that is the case, you need to insert it with the proper id.
insert into userbook (bookid,userid) values (1,0)
(by the way, only put single quotes around something when it is a string. If the value is an integer, do not put single quotes around it.)
Now you have data in all the tables with their proper relationships.
If you wanted to delete something, you can write the delete command like this:
delete from userbook where userid=0
You need to be careful about the delete command because if there is more then one row with a userid of 0, then all the rows will be deleted. In this case the id is unique so we don't have to worry about deleting more than one row.
The update command would be something like this:
update userbook set userid=1 where bookid=1
If you didn't put the where clause there, it would update userid on the entire table.
Now I am going to show you how to get all the data that you are going to want to view.
Run this query:
select u.firstname,u.lastname,u.middlename,u.email,b.BookName,b.BookAuthor,b.BookPublisher,bc.category from book b inner join bookcategory bc on bc.id=b.bookcategoryid inner join userbook ub on ub.bookid=b.id inner join users u on u.id=ub.userid
This query will display all of the relevant data that a user might want to see from this database. They can view the first, middle,last name and the user's email address. They can also see the book name, author, publisher and category.
If you notice I have u.firstname instead of firstname. The u is called an alias. I renamed the table in the query from user to u so it is easier to type. You use the inner join command which combines the tables by their keys. That is the purpose of putting the keys in the tables so I can easily get the tables together by their primary and foreign keys. Inner Join will retrieve the data only if the keys match. If the userbook table had a 1 for UserId but there aren't any users in the users table with an id of 1, then the query will display nothing.
Other ways to join:
The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.
The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).
Now you should have a basic knowledge of setting up a database in mySQL. Now it is time for you to properly set up a project in Eclipse.
0 comments:
Post a Comment