SQL for SQLite
Huahuahu? 2021-07-20 04:25:07

 

grammar

verb + subject + predicate

commannds( command )

SQL It consists of commands , It ends with a semicolon . Command has token form ,token from white space Separate , Including Spaces 、tab、 Line break .

literals

There are three kinds of

  1. character string . By single quotes ' Surround . If the character has to have ', Use two consecutive '. such as 'kenny''s chicken'
  2. The number . integer 、 decimal 、 Scientific enumeration
  3. Binary system .x'0000'. The length of the binary value must be 8bit Multiple .

Keywords and identifiers

In this ,SQL It's case insensitive .

SELECT * from foo;
SeLeCt * FrOm FOO:

It's the same . The string , Case sensitive .Mike and mike Dissimilarity .

notes

  • A single :-- start
  • Multiple lines ./**/

Create database

establish table

SQL It consists of several parts .

  • DDL(data definition language). Used to create and destory Database objects .
  • DML(data manipulation language). For operands .

Creating a database belongs to DDL. The grammar is as follows :

creat [temp] table table_name (column_definitions[, constraints])

temp It means that the database will be created in session At the end, it's gone .( Database connection does not exist )

column_definitions Comma , Separated column definitions consist of . The definition of each column includes name、domain、 A series of column constraints separated by commas .

domain, Also called type, Also called "storage class". There are five types integer, real, text, blob, null.

alter table

You can rename table Or add columns .

alter table table {rename to name | add column column_def}  

Query the database

Relationship operation

  • Basic operation ( There are foundations in set theory )
    • constraint
    • Project
    • The cartesian product
    • union
    • Bad (difference)
    • rename
  • additional operations( For ease of use , Some common operations )
    • cross
    • Natural Join
    • assignment
  • Extended operation
    • commonly (generalized) Project
    • Left outreach
    • Right outreach
    • Full outreach

SQLite Support all but right and full extras ANSI SQL Defined relational operations .

select and Operational Pipeline

select [distinct] heading
from tables
where predicate
group by columns
having predicate
order by columns
limit count, offset

Filter

Execute for each line where sentence .

values

There are many ways of expression .

  • literal values
  • Variable
  • expression
  • The value of a function
  • ...

operation

  • like. Used to match a string with the given pattern. wildcard % and _
  • glob. and like It's like . Case sensitive , wildcard * and ?### Limit and sort
  • limit. be not in ANSI In the standard
  • offset. be not in ANSI In the standard
  • order by, asc default.

Use at the same time limit and offset when , It can be used , Instead of offset

have access to limit no need offset, Use offset You have to use limit  

Functions and sums (aggregate)

classification (grouping)

group by. Execute on where Statement and select Between sentences . Input is where Statement output , Divide it into groups . Each set of inputs select sentence , Output a total (aggregate) result .
having Filter group by Output group. In statistical form .

select type_id, count(*) from foods group by type_id having count(*) < 20;

Remove duplication

distinct

joining tables

Foreign keys : The value in one column of one table is the principal value in another table , This relationship is called foreign key .

Use foreign keys to join tables .

sqlite> select foods.name, food_types.name
   ...>         from foods, food_types
   ...>         where foods.type_id=food_types.id limit 10;
name        name
----------  ----------
Bagels      Bakery
Bagels, ra  Bakery
Bavarian C  Bakery
Bear Claws  Bakery
Black and   Bakery
Bread (wit  Bakery
Butterfing  Bakery
Carrot Cak  Bakery
Chips Ahoy  Bakery
Chocolate   Bakery

inline

inline : The two tables are connected by the relationship between the columns in the table , Most commonly used and most important .

Inline uses _ intersection (interdsection)_ operation .

sqlite> Select *
   ...> From foods inner join food_types on foods.id = food_types.id;
id          type_id     name        id          name
----------  ----------  ----------  ----------  ----------
1           1           Bagels      1           Bakery
2           1           Bagels, ra  2           Cereal
3           1           Bavarian C  3           Chicken/Fo
4           1           Bear Claws  4           Condiments
5           1           Black and   5           Dairy
6           1           Bread (wit  6           Dip
7           1           Butterfing  7           Drinks
8           1           Carrot Cak  8           Fruit
9           1           Chips Ahoy  9           Junkfood
10          1           Chocolate   10          Meat
11          1           Chocolate   11          Rice/Pasta
12          1           Chocolate   12          Sandwiches
13          1           Cinnamon B  13          Seafood
14          1           Cinnamon S  14          Soup
15          1           Cookie      15          Vegetables

Cross connect

hold A Each column of the table and B Each column of the table is connected , There are a lot of meaningless results .

Outreach

Outreach : Outreach selects all rows that are inlined and some that do not meet the relationship conditions .

select *
from foods left outer join foods_episodes on foods.id=foods_episodes.food_id;

foods It's the left table , about foods Each line , Trying to make a connection , And through relational conditions . Qualified lines are displayed , Those that don't match will also be shown ,episodes Column is shown as null.

Full outreach : A collection of left and right extras .

Natural join

Special case of inline . Connect through the same column name . If the name of the column changes , The results will be different , So try not to use .

Recommended Syntax

Use explicit join grammar .

select * from foods, food_types where foods.id=food_types.food_id;

This is old-fashioned grammar .

select * from foods inner join food_types on foods.id=food_types.food_id;

This is a new grammar .

names and aliases

select f.name as food, e1.name, e1.season, e2.name, e2.season
from episodes e1, foods_episodes fe1, foods f,
     episodes e2, foods_episodes fe2
where
  -- Get foods in season 4
  (e1.id = fe1.episode_id and e1.season = 4) and fe1.food_id = f.id
  -- Link foods with all other epsisodes
  and (fe1.food_id = fe2.food_id)
  -- Link with their respective episodes and filter out e1's season
  and (fe2.episode_id = e2.id AND e2.season != e1.season)
order by f.name;

Subquery (subquey)

hold select As a result of from、orderby Statement input .

The joint query (compound query)

It's a bit like the opposite of a subquery . Use union,intersect,except To process the results of multiple queries .

Prerequisite

  • The number of columns of the results to be processed is the same
  • There can only be one order by sentence , At the end of the union query .

Conditional results (conditional results)

  • Use static values
select name || case type_id
                 when 7  then ' is a drink'
                 when 8  then ' is a fruit'
                 when 9  then ' is junkfood'
                 when 13 then ' is seafood'
                 else null
               end description
from foods
where description is not null
order by name
limit 10;
  • stay when Using expressions in
select name (select
               case
                 when count(*) > 4 then 'Very High'
                 when count(*) = 4 then 'High'
                 when count(*) in (2,3) then 'Moderate'
                 else 'Low'
               end
             from foods_episodes
             where food_id=f.id) frequency
from foods f
where frequency like '%High';

Handle NULL


Please bring the original link to reprint ,thank
Similar articles

2021-08-09

2021-08-09