2024. március 19., kedd

Gyorskeresés

SQLite szösszenetek

Írta: |

[ ÚJ BEJEGYZÉS ]

Főbb tudnivalók az SQLite -ról. Magamnak, hogy meglegyen és ne kelljen mindig googlizni

SQLite.org

SQLite rules

1. Each command ends with a semi-colon (parancs végén pontos vessző)
2. There are 3 types of values
a. String literals, which are characters surrounded almost always with single quotes
b. Numeric literals, which are numbers of any type without quotes
c. Binary literals are numbers represented as hexidecimals

3. There are 5 types in SQLite
a. integer
b. real
c. text
d. blob
e. null

4. To use single quotes in a string add 2 in a row rather then one ''
5. Single line comments start with 2 lines --
6. Multiline comments start with /* and end with */
7. SQL is case insensitive
8. Each table can have only one primary key

Collate
Három féle képpen hasonlithatjuk össze az eltárolt értékeket.
- BINARY: összehasonlitja a string adatokat a memcmp() függvény segitségével, függetlenül a karakter kódolástól
- NOCASE: hasonló a BINARY-hoz, de csak az ASCII karaktereket fogja megcsinálni,
- RTRIM: white spaceket figyelmenkívűl hagyja

select * // The action / verb
from general_power // The subject of your query
where power_type='Invulnerability'; // The predicate which describes the subject

sqlite
.mode column -- oszlopokba rendezi
.headers on -- mutatja a header-eket
sqlite> .mode column
sqlite> .headers on
sqlite> select * from tbl_team_table;
id_team team_name id_league
---------- ---------- ----------
1 West Ham 2
2 QPR 2
3 Chelsea 2
4 Arsenal 2
5 Tottenham 2


.width 2 20 -- az első oszlop 2 a második 20 char széles
id team_name id
-- --------------------- --
1 West Ham 2
2 QPR 2
3 Chelsea 2
4 Arsenal 2
5 Tottenham 2

sqlite> .tables -- az adatbazisban elerheto tablazatok
tbl_leagues tbl_meccs_summary tbl_team_table
tbl_meccs_detail tbl_season

sqlite> .mode line -- sorokban
sqlite> select * from tbl_team_table;
id_team = 1
team_name = West Ham
id_league = 2

id_team = 2
team_name = QPR
id_league = 2

sqlite> .schema -- az adatbazisban levo tablak schemajat kapjuk meg
CREATE TABLE tbl_season(id_season INTEGER primary key ,season TEXT NOT NULL);
CREATE TABLE tbl_leagues(id_league INTEGER primary key ,legaue_name TEXT NOT NU
LL);
CREATE TABLE tbl_team_table(id_team INTEGER primary key ,team_name TEXT NOT NUL
L,id_league INTEGER);

sqlite> .show --show current settings
echo: off
eqp: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
stats: off
width: 2 21 2

sqlite> .nullvalue 'NULL'
sqlite> .show
echo: off
eqp: off
explain: off
headers: on
mode: column
nullvalue: "NULL"
output: stdout
separator: "|"
stats: off
width: 2 21 2

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tbl_season(id_season INTEGER primary key ,season TEXT NOT NULL);
INSERT INTO "tbl_season" VALUES(1,'2014/2015');
CREATE TABLE tbl_leagues(id_league INTEGER primary key ,legaue_name TEXT NOT NU
LL);
INSERT INTO "tbl_leagues" VALUES(1,'NB1');
INSERT INTO "tbl_leagues" VALUES(2,'Premier_Liga');
INSERT INTO "tbl_leagues" VALUES(3,'Bundes_Liga');
CREATE TABLE tbl_team_table(id_team INTEGER primary key ,team_name TEXT NOT NUL
L,id_league INTEGER);
INSERT INTO "tbl_team_table" VALUES(1,'West Ham',2);
INSERT INTO "tbl_team_table" VALUES(2,'QPR',2);
INSERT INTO "tbl_team_table" VALUES(3,'Chelsea',2);
INSERT INTO "tbl_team_table" VALUES(4,'Arsenal',2);
COMMIT;

dump készitése file-ba (a kimenetet átirányitjuk a file-ba, majd vissza a kijelzőre)
sqlite> .output /tmp/kimenet.sql
sqlite> .dump
sqlite> .output stdout

dump visszaállitása üres db-be
sqlite> .read /tmp/kimenet.sql

Mode - megjelenitési formák (file ba irányitásnál is működik)
.mode csv Comma-separated values
.mode column Left-aligned columns.
.mode html HTML <table> code
.mode insert SQL insert statements for TABLE
.mode line One value per line
.mode list Values delimited by .separator string
.mode tabs Tab-separated values
.mode tcl TCL list elements

CREATE TABLE origin_issue (id INTEGER PRIMARY KEY,
comic_name TEXT NOT NULL COLLATE NOCASE,
issue_number INTEGER NOT NULL DEFAULT '0',
comic_issn INTEGER UNIQUE CHECK(comic_issn>0),
character_id integer,
FOREIGN KEY(character_id) REFERENCES identity(id));

// primary key means this will auto increment by 1 and remain unique
// NOT NULL means it can't contain a null value
// COLLATE NOCASE converts upper case characters to lowercase characters when comparing
// COLLATE BINARY if 2 strings are compared, they are compared using the exact characters
// COLLATE RTRIM compares just like BINARY except trailing white space is ignored
// DEFAULT defines a default value to use
// UNIQUE makes sure that the same number isn't used in this column
// CHECK sets a rule that all data in the column must obey
// A FOREIGN KEY in one table references a PRIMARY KEY in another table

// To add a column
// It Can't be UNIQUE, PRIMARY KEY
// It can't have a default value CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, // or an expression in parentheses
// If NOT NULL it can't have a NULL value

ALTER TABLE origin_issue
ADD COLUMN writer TEXT;

// You can rename a table
ALTER TABLE origin_issue RENAME TO first_issue;

// You can't delete columns in any way
// SELECT is used to query your database
// When using SELECT you define many clauses that narrow down to a specific type // of data

SELECT id, character FROM identity;
select * from superhuman;
.schema superhuman

select identity_id from superhuman where power_id_1 = 6;
select identity_id from superhuman
where power_id_2 = 14 and power_id_3 = 14;

select identity_id from superhuman
where power_id_2 = 14 or power_id_3 = 14;

// LIKE can be used with % to match a series of characters and zero or more
// characters there after

select power_type from general_power
where power_type LIKE 'Superhuman%';

// Return all matches for Superhuman followed by an s word
select power_type from general_power
where power_type LIKE 'Superhuman%s%';

// Return all matches for Superhuman followed by an s word
select power_type from general_power
where power_type LIKE 'Superhuman%s%'
AND power_type NOT LIKE '%strength%';

// _ can be used to represent any 1 character or space
select power_type from general_power
where power_type LIKE 'Power_________';

// ORDER BY allows you to define sorting either DESC or ASC
// LIMIT allows you to limit your results
// OFFSET will skip the first number or results

select power_type, general_power_desc
from general_power
where general_power_desc LIKE '%control%'
ORDER BY power_type ASC, power_type LIMIT 10 OFFSET 2;

// You can also use a comma after LIMIT offset_number, limit_number
select power_type, general_power_desc
from general_power
where general_power_desc LIKE '%control%'
ORDER BY power_type ASC, power_type LIMIT 2, 10;

// You can change the title of columns with AS like this
SELECT power_type AS 'Common Power'
FROM general_power WHERE power_type LIKE 'Superhuman%';

// Fix Captain Americas Last Name
select * from identity;
UPDATE identity SET secret_identity='Steve Rogers'
WHERE id=2;

// Use DISTINCT to output the first result and then ignore duplicates

INSERT INTO identity (secret_identity, character)
VALUES ('Scott Lang', 'Ant-Man');

select * from identity;
SELECT DISTINCT character FROM identity;

// DELETE a row in a table
DELETE FROM identity WHERE id=7;

select * from identity;

// SQLites SELECT can also be used to perform numerous Arithmetic, Boolean,
// Bitwise, Relational and other Operations
SELECT (1+2) / (6-3) * 10;
SELECT 15 % 10;

// You can perform boolean operations in which 0 is false and any other number
// is true
SELECT 1 AND 0, 1 OR 0, NOT 1;

// Relational Operators all work as well
// <, <=, >, >=, =, ==, !=, <>
SELECT 1 < 2, 3 >= 2, 1 = 1, 1 <> 2;

// Other Operators
SELECT 'Super' || 'hero'; // String concatenation
SELECT 'Paul' IN ('Mike', 'Phil', 'Paul');
SELECT * FROM identity WHERE character IN ('Iron Man', 'Hulk');

// GLOB is like LIKE, but it is case sensitive and it uses the UNIX file
// globbing syntax

// Find results that contain super, but not Super

SELECT * FROM general_power
WHERE general_power_desc
GLOB '*super*';

// Find any match that has 9 characters
SELECT * FROM general_power
WHERE power_type
GLOB '?????????';

// BETWEEN can be used to make comparisons as well
SELECT * FROM power;

SELECT * FROM power
WHERE power_type_id
BETWEEN 1 AND 40;

//Random SQLite Functions

SELECT random(); // Generate random number
SELECT ABS(RANDOM() % 100); // Random number between 0 and 100
SELECT ABS(RANDOM() % (200-100)); // Random number between 200 and 100

// Generate minimum and maximum values from a result

SELECT min(id), max(id)
FROM identity;

SELECT LOWER(secret_identity),
UPPER(character)
FROM identity;

SELECT total_changes(); // Returns the total number of changes made to the
// database since it was last opened

SELECT LENGTH('Iron Man'); // Returns the number of characters in a string
SELECT COUNT(*) FROM identity; // Number of rows in the table
SELECT date(); // Return the current date
SELECT time(); // Return the current time
SELECT datetime(); // Return the current date and time
SELECT date('now', '-30 days'); // Get the date 30 days ago
SELECT date('now', '-20 months'); // Get the date 30 days ago
SELECT date('now', 'weekday 0'); // Get the date of the next Sunday
SELECT time('now', '-1000 minutes');
SELECT time('now', '-1000 seconds');
SELECT strftime('%m-%d-%Y'); // You can modify the date format

// Find Thanksgiving day
SELECT date('now', 'start of year', '10 months', '21 days', 'weekday 4');

----------------------------------------------------------------------

SQLite manager - sqlitemanager.org

Firefox alá egyszerűen add-onként telepítsük (tehát gyakorlatilag minden platformon fut!) , majd Tools->SQLite Manager

Tetszetős felület gyakorlatilag minimális gépeléssel, kattintgatással.

sqliteman sqliteman.yarpen.cz

Install:
- Win: kattingatás
- Linux: sudo apt-get install sqliteman

Gyakorlatilag ez is tud mindent amire szükség van. (Amit meg nem arra meg ott a parancssor)

Hozzászólások

(#3) sonar válasza #32839680 (#2) üzenetére


sonar
addikt

Szia,

A C/C++ nem az én vonalom, de ha python-os segitség kell akkor abban tudok segíteni. ;)

Másik kérdésedre, én az sqlite-ot nem használnám abban az esetben ha több felhasználó dolgozik bele. (maximum ha csak olvasnak) Nem igazán erre lett kitalálva.

A tudást mástól kapjuk, a siker a mi tehetségünk - Remember: Your life – Your choices!

(#5) sonar válasza #32839680 (#4) üzenetére


sonar
addikt

A shared excel az tényleg halál. Bár ha egy excelt akarunk kiváltani akkor a MySQL simán elég lehet egy átlagos gép bőven ki tud szolgálni annyi usert, mint amennyit az excel.

A tudást mástól kapjuk, a siker a mi tehetségünk - Remember: Your life – Your choices!

További hozzászólások megtekintése...
Copyright © 2000-2024 PROHARDVER Informatikai Kft.