Queries
INSERT:
we had created our db, then created our table, now it is time to insert records to the table.
•Records: in our table employee, a record is an employee information.
•To insert record into our table, or in other words to add an employee to the table. We use the query: INSERT INTO.
•Syntax:
INSERT INTO table_name (col1,col2,…) VALUES (val1,val2,…)
•Notes:
•INSERT INTO instructs your db to add a new record.
•table_name specifies the table you want to add a new record inside.
•(col1,col2,…) specifies attributes you want to add values to it.
•VALUES(val1,val2,…) specifies values of attributes you want to add.
•The order here is very important.
Example:
INSERT INTO employee
(name, socialNo, department, jobTitle, salary, telephone)
VALUES (“Anas”, 1234, ”IT”, ”Programmer”, 1000, ”07x-xxxxxxx”)
Notes:
•NOT NULL fields must be filled on each record insertion.
•NULL fields are optional, you might assign a value to it or not.
•AUTO_INCREMENT: it is optional to assign a value for an AUTO_INCREMENT field, if you want to assign its value manually, then be careful, value must be unique. I prefer leaving it to the db to assign it a suitable value.
•Previous query, will add a new row to table employee, and will look like this:
SELECT:
•Till now, we completed the first inquiry of our customer, which is storing employees information.
•Now, we are going to execute second inquiry which is accessing stored information.
•To get access for stored records in MySql table, we use the SELECT query.
Syntax:
SELECT * FROM table name
Example:
SELECT * FROM employee
• The * means get all table columns values.
•Executing this query will print all records in the table employee with all data stored inside.
WHERE Statement:
often, we want to access specific record, and get data belongs to it. Our previous query selects all records, and this will make it hard to view and get information from it.
•To access specific record/s in a table, we add some properties to our query, and restrict it in a number of records.
Example:
•To get information about all employees in IT department we might run this query:
SELECT * FROM employee
WHERE department = “IT”
•To get information about all employees whose salary greater than 1000, query looks like this:
SELECT * FROM employee
WHERE salary > 1000
Operators used with WHERE statement:
= Equal.
<> Not equal.
> Greater than.
< Less than.
>= Greater than or equal.
<= Less than or equal.
BETWEEN: between an inclusive range.
LIKE: search for a pattern.
ORDER BY:
used to order selected records ascending or descending.
Syntax:
•SELECT * FROM table_name ORDER BY column_name ASC| DESC
•table_name: is the table we want to select records from it.
•column_name: data to be sorted.
•ASC | DESC: method for sorting, by default it is sorted ascending.
Example:
SELECT * FROM employee
WHERE salary >1000
ORDER BY salary DESC
•This query will display all employees whose salary greater than 1000 and order them descending.
UPDATE:
used to update record values.
•Sometimes we need to update the value of a specific field, for example in our table employee, suppose one employee his job title has been upgraded from head-department to manager, or has a bonus 10% on his salary. So we want to modify this employee information.
Syntax:
•UPDATE table_name
SET column1=val1, column2= val2
WHERE column3 = val3
Example:
UPDATE employee
SET salary=1200
WHERE id=1
Notes:
•If we want to update more than one attribute we separate each one except the last with a comma.
•If you want to modify an attribute, and assign the same value to all records, then omit WHERE from your query.
DELETE:
Sometimes, you’ll notice that some records are not needed in your table, for example, an employee resigned from the company. Then there is no need to keep their information in your db.
Syntax:
•DELETE FROM table_name WHERE column=val
Example:
DELETE FROM employee
WHERE id=1
•This query will delete the employee whose id is 1.
•If we omit WHERE from the DELETE query, then all records in the table will be deleted
•When executing DELETE query AUTO_INCREMENT field will not be decremented.
End Of Lesson 4, To download this lesson as PDF click here: PHP Tutorial–Lesson 4: Introduction to PHP- Part IV
Best Wishes,
Anas Jaghoub
Comments
Post a Comment