Simple SQL Queries
- INSERT queries - All database start empty, so you need to put data in. These calls add data to the an existing database
- SELECT queries - Once data is in, you need to use it. Select queries retrieve data from the server, potentially filtering it and performing other logic in the process.
- DELETE queries - Once data is no longer needed, you may want to remove it from your records. These calls removes informatio from a server.
- UPDATE queries - Sometimes information is outdated, these calls modify existing records
The following queries assume you have a table users defined as follows:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100),
firstname VARCHAR(100),
lastname VARCHAR(100),
url VARCHAR(100)
);
Insert queries
Whenever you need to insert data into a database, you use an insert query. Assuming you you are connected to your database and you have a table called MyTable, the syntax is the followingINSERT INTO MyTable (field1,field2,field3) VALUES (VALUE1,VALUE2, VALUE3)
Please note that sometimes, you will need to insert more than one row at the time. For performance reasons, rather than using multiple insert statements, you can specify more than one row at the time: INSERT INTO users (username,first,last) VALUES ('jstewart','Jon', 'Stewart')Select Queries
Queries on recordsInsert a row | insert into tablename (field1,field2) VALUES (field1_content,field2_content) |
List all record | select * from tablename |
find on a criteria | select * from tablename where numericfield=value [and stringfield=’value’] |
Delete records | delete [LOW_PRIORITY] FROM tablename [where field(s)=criteria] [limit maxrows] |
Update a row (s) | UPDATE SET field=’value’[,field2=’value’..] [where clause] [order clause] [limit #] |
Record: 100 Duplicates: 0 Warnings: 0
Warnings: when null placed in not null column (reverts to default), out of range or illegal value (0 or stripped) Duplicates: Errors due to attempted insertion of duplicate value in unique field Select query Syntax SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] [select_expression,... [fieldname] [, table.field] [concat(last_name, ‘, ‘, First_Name) as fieldalias] [, math expression] [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references [tablename [AS tablealiasforclarity] [, database2.table2] ] [WHERE where_definition] [GROUP BY {unsigned_integer | col_name | formula}] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] ] Table JoinsLeft Outer Join | select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name; |
Natural [left] join | All Columns with the same name are assumed equal |
RIGHT JOIN | Similar to left join, but not recommended to keep queries portable |
The USING clause | A LEFT JOIN B USING (Column1, Column2, Column3,...) |
STRAIGHT_JOIN | The left table is explicitely loaded before the right, to make sure the right order is respected |