This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
databases [2013/06/03 09:44] sandeep created |
databases [2018/03/24 11:13] (current) |
||
---|---|---|---|
Line 38: | Line 38: | ||
- Update the email of a student in the 'students' table to '[email protected]' where the name is 'john' (Create a row with name = john for this.) | - Update the email of a student in the 'students' table to '[email protected]' where the name is 'john' (Create a row with name = john for this.) | ||
- Find the number of students in students table. | - Find the number of students in students table. | ||
+ | |||
+ | ===== Reference ===== | ||
+ | === Create === | ||
+ | Replace tablename with your table's name, the fieild1,field2 with your field's names' and the type1,type2 with your respective types. | ||
+ | <code sql> | ||
+ | CREATE table tablename (field1 type1,field2 type2); | ||
+ | </code> | ||
+ | === Select === | ||
+ | == Selecting all columns == | ||
+ | <code sql> | ||
+ | SELECT * from tablename; | ||
+ | </code> | ||
+ | |||
+ | == Selecting some columns == | ||
+ | <code sql> | ||
+ | SELECT field1,field2 from tablename; | ||
+ | </code> | ||
+ | |||
+ | == Selecting with conditions == | ||
+ | <code sql> | ||
+ | SELECT * from tablename where field == value; | ||
+ | </code> | ||
+ | |||
+ | == Supported Conditions == | ||
+ | <code sql> | ||
+ | field > number (Eg : age > 10 ) | ||
+ | field < number | ||
+ | field == number | ||
+ | field >= number | ||
+ | field <= number | ||
+ | field in (v1,v2,v3) (Eg: name in ('ganesh','divya')) | ||
+ | field not n (v1,v2,v3) | ||
+ | field like '%' (Eg: name like 'g%' [Matches all names starting with g] or name like '%g' [matches all names ending with g]) | ||
+ | field not like '%' | ||
+ | |||
+ | </code> | ||
+ | == Multiple constraints == | ||
+ | We can use and,or in SQL to support multiple constraints | ||
+ | <code sql> | ||
+ | SELECT * from tablename where n > 10 and b < 20; # Executes only if both a > 10 and b < 20 | ||
+ | SELECT * from tablename where n > 10 or b < 20; # Executes if any is true | ||
+ | </code> | ||
+ | |||
+ | == Getting Count== | ||
+ | <code sql> | ||
+ | SELECT count(*) from tablename; | ||
+ | </code> | ||
+ | |||
+ | === Update === | ||
+ | <code sql> | ||
+ | UPDATE tablename set field = value where field2 = value2; | ||
+ | </code> | ||
+ | |||
+ | Multiple constraints apply here also. | ||
+ | === Insert === | ||
+ | <code sql> | ||
+ | INSERT INTO tablename values ( value1, value2, value3 ); | ||
+ | </code> | ||
+ | |||