The 1. 0 Most Common My. SQL Queries Database. Journal. com. My. SQL queries are usually written once and then wrapped in class functions to minimize code repetition. This article lists the 1. My. SQL. 1. Create Table. You would not use the create table query every time the script executes in normal scenarios. However, when you start building an application, you need to create database tables. To do so with a primary key, you can use the following query. CREATE TABLE emp. INTEGER UNSIGNED NOT NULL AUTOINCREMENT. VARCHAR4. 5 NOT NULL. VARCHAR4. 5. dept VARCHAR4. DEFAULT sales. PRIMARY KEY id. ENGINE Inno. DB The above query creates a table emp in the selected database. We will have. an id column as auto increment and with a PRIMARY KEY constraint, which ensures. Add edit delete rows dynamically using jquery and php using javascript animation i demonstrated how to add and delete records dynamically to the table. Convert MySQL to JSON using PHP. Learn how to convert the mysql query result set to json format or file with PHP Programming Language. Easily export mysql to json. The column dept is populated. You can specify the Engine to. Otherwise, the default engine will be used. We used Inno. DB here because it allows FOREIGN KEY and transactions. Insert Query. Now that you have a table in your database, let us see how you can insert. You might have used insert query many times. AngularJS-Insert-Update-Delete-Using-PHP-MySQL.jpg' alt='Php Mysql Edit Delete Update File' title='Php Mysql Edit Delete Update File' />This is achieved by using the keyword on DUPLICATE KEY. Here. Simple insert queryinsert into emp name,lastname,dept valuesnew name,new last name,Accounts Insert and Update queryinsert into emp id,name,lastname,dept valueskey that already exists, new name,new last name,Accounts on duplicate KEY UPDATE namedefault name, lastnamedefault last name In the above query, if the value of id passed already exists, then we can update the value of the row instead of inserting a new row. This is more useful when you need to check columns other than auto incremented ones. JOINSJoins are supposed to be complicated because there is so much variety in them. Joins are known by different names in different databases self join, outer join, inner join, natural join, and so many others. We will use a join between two tables, which is generally used to pull data. Let us assume we have another table called departments, which has a list of all the departments. If you want to pull employee data including departments, the query would be Regular Joinselect emp. Left Joinselect emp. Right Joinselect emp. The regular join will get all the records that match the condition in both tables. The left join will match records that match the condition as above, but it will also get all the unmatched records from the left table. On the other hand, the right join will get all the unmatched records from the right table. JqGrid supports creating a form on the fly to view, add, edit, delete, or search grid data. A screenshot of an Add Record form is shown below. Edit Article wiki How to Create a Secure Session Management System in PHP and MySQL. Three Methods Configure mySQL Database Create session. Creating. PHPMySQL, free course, tutorials code and lessons, learn how to use PHP programming language and MySQL database. In this article we have explained you step by step insert, update, select and delete using asp. This article is useful for beginners who want. Java2s. com Emailinfo at java2s. Best Streaming Software For Windows 7. Demo Source and Support. All rights reserved. Create and Drop Constraints. Often times you need to editadddelete constraints applied on a table. You can do so using the following queries Add a primary key ALTER TABLE TABLENAME DROP PRIMARY KEY, ADD PRIMARY KEY COLUMNNAME Drop a Primary key ALTER TABLE TABLENAME DROP PRIMARY KEY While adding a primary key, we first drop the already added keys and then add the key to a new column. While Microsoft SQL Server 2008 is the recommended database to use when hosting PHP applications on an Internet Information Services 7 IIS 7 and above. Otherwise it wont allow you to add the key. Order By, Having Clauses. Order by is used to arrange data by a specific column and then having is used to filter that result set. These are really useful when you need some specific information from a large database. Select from TABLENAME groupby dept having salary 1. In the above query the result set would contain data organized by departments and those who have salaries greater than 1. Its worth noting that when we use the having clause without order by it works more like the where clause. To use load data local infile function from mysql at mysql 4. CLIENTLOCALFILES128, which based on. Adding Indexing and Search Queries. Creating indexes for a column undoubtedly increases incoming queries to the database, and indexes are created on a column basis. For example, in our sample table emp, if most of the queries fetch data using the column id, then it would be a wise decision to create an index for the id column. Upon creating indexes, My. SQL will first search for ID in the created index and if none is found it will run the query on the table level. So, that speeds up the process if indexes are created wisely. To create an Index, the following query is used. Create index at time of creating table. CREATE TABLE emp. Name varchar4. 5. INT, INDEXID. Create Index for an existing table. Create Index idindex on empID 7. Aggregate Function Queries. Aggregate functions are used to perform mathematical operations on the data. These types of functions include sum, count. For example, if you want to calculate the number of employees working. DESC The above query will return the name of the employee and count in the department, and it is then arranged in descending order of the count returned. Alter and Update Columns of a Table. Tables are created and then edited quite often. In the following few lines we will see how to addchange columns of an existing table. Add a column. Alter table emp add column salary varchar4. Edit a column. Salary should be in integer instead of having varchar, so lets change its datatype to int. Note that there may be loss of data if there is a compatibility issue. Alter table emp modify column salary int1. Rename a column. Alter table emp change salary salary. Drop a column. Alter table emp drop column salary 8. Create a New Table with Non Duplicate Values. Suppose you have thousands of records in your database table and when you. Now you plan to clean up all the junk and create a new table. Lets see how. we do it. So it will create a new table emp. You can sort your table with a different approach. Creating Dump of Your Database and Optimizing the Table. There may be times when your application needs to schedule backups and send them over email or keep them somewhere safe. You can write a script to do so and set a cron for the same thing. That will save a lot of work with backing up your work and avoid running into some unanticipated trouble. Lets see how you can create a backup of your database as well as optimize the table. This will create a sql dump file, which can be then imported again to restore the database at some level. Optimizing a Table. Its pretty straightforward to optimize a table in My. SQL, as the name indicates Optimize table TABLENAMESummary. You have learned to use the most common queries in My. SQL ,which are used in day to day coding. You can enhance these queries to suit your particular scenario.