CREATE DATABASE mydatabasename;
to create a DDBBUSE mydatabasename;
start working on a specific DDBBCREATE TABLE mytablename (colname1 char(5), colname2 varchar(30));
to create a table with specific columnsALTER TABLE mytablename ADD COLUMN colname varchar(30);
to add a new column to an existing tableALTER TABLE mytablename RENAME colname TO newcolname;
to rename a column from an existing tableALTER TABLE mytablename DROP colname;
remove a column from an existing tableINSERT INTO mytablename VALUES ("first column value", "second column value");
to add data to a table in the predefined orderINSERT INTO mytablename (colname1) VALUES ("first column value");
to add data to a table changing the order or omiting valuesSELECT * FROM mytablename;
get all registries from a tableSELECT * FROM mytablename LIMIT 1;
get just the first register (combine with sorting)SELECT colname3, colname4 FROM mytablename WHERE colname2 = colvalue;
get specific columns for rows that match a criteriaSELECT * FROM mytablename WHERE colname IS NULL;
empty values. Always use with is
, never with =
UPDATE mytablename SET colname = newvalue, colname2 = newvalue2 WHERE colname3 = colvalue;
update specific data in a rowSELECT DISTINCT colname1 FROM mytablename;
get only unique valuesSELECT * FROM mytablename ORDER BY colname1, colname2;
get sorted registries. Optionally ORDER BY colname DESC;
to change the directionDELETE FROM mytablename WHERE colname = colvalue
delete entries matching the criteria. ImportantDROP TABLE mytablename
delete a tableDROP DATABASE mydatabasename
delete a DDBBLIKE
allows partial selectors combined with %
. For example SELECT * FROM mytablename WHERE colname LIKE "val%";
will select everything starting with “val”
Connectors for searchs: AND
, OR
, BETWEEN
, IN
, NOT
Aggregated funcs: MAX
, MIN
, AVG
, SUM
, COUNT
. Ej: SELECT MAX(colname), COUNT(*) from mytablename
Functions to modify the output: LOWER
, UPPER
, LENGTH
, SUBSTR
, REPLACE
, TRIM
, CONCAT
. Ej: SELECT LENGTH(colname), LOWER(*) from mytablename
Grouping:
SELECT colname1, MAX(colname2) FROM mytablename GROUP BY colname1
. Once you use grouping, you can only show the value of the grouped column or aggregated functions (over any column).sqlite> SELECT colname1, COUNT(colname2) from mytablename GROUP BY colname1 HAVING COUNT(*) >= 2
. Add conditions over aggregated functions resultsExport a DDBB into a file:
.open mydatabasename
.output myfile.sql
.dump
.exit