¡Descarga SQLite3 - Introducción y más Resúmenes en PDF de Programación de Bases de Datos solo en Docsity!
/home/pina_ek/Documents/BDBI/UPC%20-%20BBI/Y2S1_DB/SQL/02_RelationalModel/03_BIOPSYDB/notes/SQLite3_…
SQLite3 SYNTAX - Notes
Estíbaliz Pina Carrasco Data Bases - SQL (part 2) BD Bioinformatics
Pre-syntax: SQLite3 CORE-COMMANDS
Usage of " ; " at the end of each statement or code line. .open ARCHIVE_NAME.db | To open a .db file already used .tables | To see all the tables that exist in the DB .schema TABLE_NAME | To see all the columns of a table "TO_DATE" & "TO_CHAR" functions ONLY WORK in Oracle, not SQLite To format the output of a query commands: sqlite>.mode column sqlite>.header on sqlite>.nullvalue NULL Input: sqlite> SELECT CT_DATA, typeof(CT_Data) AS Type_Var FROM CTESTUDI LIMIT 5; Output: CT_DATA Type_Var
2015-10-01 text 2015-10-08 text 2015-09-17 text 2015-10-01 text 2015-11-05 text typeof(COL_NAME) | To see the type of variable stored on this column.
.output path/File_Name.txt | To save the resoults of all the subsequent queries || Used WITHOUT arguments, it turns back to the standard output. .once path/File_Name.txt | To save ONLY the next query .read path/File_Name.txt | To output the resoults stored.
OPERATORS & FUNCTIONS' SYNTAX
> SELECT … FROM … ;
| Selects a colum from a table. sqlite> SELECT Nodul_ID, LocSeg FROM NODUL;
> … AS …
| Used with "SELECT col_name AS Data_Name" makes possible formatting the column name on the output. sqlite> SELECT PACIENT_ID AS Conejitos_felices FROM BIOPSIPLAN LIMIT 3; Conejitos_felices
3 4 11
> SELECT DISTINCT…
| To avoid duplicate values from a column. sqlite> SELECT DISTINCT PACIENT_ID FROM NODUL;
> FROM … WHERE … ;
| States a condition =, <, > + int/float or "char". | It is possible to use AND & OR operators: "WHERE var_1="city" AND var_2>5" sqlite> SELECT Nodul_ID, LocSeg FROM NODUL WHERE PACIENT_ID = 2; Search NULL values : | "where column = NULL" or "where column is NULL" or "where column IS NOT NULL"
sqlite> select X_EXIT from COCO_MILK WHERE X_EXIT LIKE "20/%" ESCAPE "/";
It can be used other escape character such: , /, @, $
It is possible to combine %, _ and ESCAPE.
> GLOB
| CASE_SENSITIVE==TRUE
| Similar to LIKE operator, but uses different characters (more Bash-based): OPERATOR char & nums single char NEGATIVE LIKE % _ NOT LIKE GLOB ? * ^pattern Positive match: sqlite> SELECT DISTINCT APARELL FROM CTESTUDI WHERE APARELL GLOB "Tosh*"; APARELL
Toshiba Aquilion ONE Toshiba Sant Boi Negative match: sqlite> SELECT DISTINCT APARELL FROM CTESTUDI WHERE APARELL GLOB " [^Tosh]*"; APARELL
GE Optima CT
> BETWEEN … AND … ;
| Indicates 2 values between search through. Also: NOT BETWEEN.
sqlite> select PACIENT_ID from BIOPSIPLAN WHERE PACIENT_ID BETWEEN 5 AND 20; PACIENT_ID
11
> ORDER BY … ;
| States the order in which we want the output be returned. ASC ( default ) & DESC | To specify in which order we want the output. NULLS FIRST ( default ) & NULLS LAST | If NULL values, specifies the position when sorted. sqlite> select PACIENT_ID from BIOPSIPLAN ORDER BY PACIENT_ID DESC; PACIENT_ID
33 32 31 30 26
> LIMIT… OFFSET… ;
| "SELECT … FROM … LIMIT (int) OFFSET (int)", used to limit the output to a determinated number of rows. Offset sets the nth row from you want output to start (not included). LIMIT sqlite> select PACIENT_ID from BIOPSIPLAN WHERE PACIENT_ID LIMIT 3; PACIENT_ID
3 4 11 LIMIT + OFFSET sqlite> select PACIENT_ID from BIOPSIPLAN WHERE PACIENT_ID LIMIT 3 OFFSET 5;
FROM
BIOPSIPLAN
WHERE
CT_ID IN (
SELECT
CT_ID
FROM
CTESTUDI
WHERE
CT_DATA = '2016-02-09'
1st) Executes NESTED-QUERY 2nd) Executes PARENT-QUERY NOT IN | To select everything BUT whatever is IN.
> JOIN
| Query data from 2 or more tables and join it. | Different types of JOIN determine how to use from one table to match with rows in another table. ALIAS: Give an alias to the tables of interest: SELECT t1.color, t2.refseq FROM table_1 t1 JOIN table_2 t2 ON t1.conejo=t2conejo; | "color" belongs to table_1 and "refseq" to table_2. Both have "conejo" in common. >> INNER JOIN | "SELECT … FROM … INNER JOIN … ON …;" SELECT color, refseq FROM tab_1 INNER JOIN tab_2 ON tab_2.conejo = tab_1.conejo; Which is the same as: SELECT t1.color, t2.refseq FROM table_1 t1 INNER JOIN table_2 t2 USING conejo;
| If want to join 2 tables by the same column, " ON tab_2.conejo = tab_1.conejo; " is EQUIVALENT to " USING conejo; " >> LEFT JOIN | Selects data from the left table and joins the data as specified with right table rows. >> RIGHT JOIN | >> CROSS JOIN |
> CAST(exp as name_var)
| To fo
> CAST(exp as name_var)
| To fo
