SQL for SQLite

Grammar

Verb + subject + predicate

Commannds (command)

SQL is made up of commands, ending with semicolons. The order is made up of tokens and tokens are separated by white space, including spaces, tabs, and line breaks.

Literals

There are three kinds

1. String. Surrounded by single quotes ' . If the character is to have ' , use two consecutive ' . Like what'kenny''s chicken'
2. Numerical. Integer, fractional, scientific counting method
3. Binary. x'0000'. The binary value length must be a multiple of 8bit.

Keywords and identifiers

In this case, SQL is not sensitive to capitalization.

SELECT * from foo;SeLeCt * FrOm FOO:

is the same. is case-sensitive to the string. Mikeand mike not the same.

Comments

• Single line: -- beginning
• MultiRow/**/

Create DATABASE CREATE TABLE

SQL is made up of several parts.

• DDL (data definition Language). Used to create and Destory database objects.
• DML (Data manipulation language). Used to manipulate the object.

The creation database belongs to the DDL. The syntax is as follows:

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

Temp means that the database created will disappear at the end of the session. (Database connection does not exist)

column_definitionsConsists , of a comma-delimited column definition. Each column definition includes a list of name, domain, and comma-delimited series of column constraints.

Domain, also known as type, is also called "storage Class". There are five types integer, real, text, blob, null .

ALTER TABLE

You can rename the table or add columns.

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

Querying Database Relationship Operations

• Basic operations (basic in Set theory)
  • Constraints
  • Projected
  • Cartesian product
  • Joint
  • Poor (difference)
  • Renaming
• Additional operations (for ease of use, some common operations)
  • Cross
  • Natural joins
  • Assign value
• Extended operations
  • General (generalized) projection
  • Left outer
  • Right out of the Union
  • Full-Outreach
    

SQLite supports relational operations that are defined by all ANSI SQL except right and all outside.

Select and operational Pipeline

select [distinct] headingfrom tableswhere predicategroup by columnshaving predicateorder by columnslimit count, offset

Filter

Executes a where statement for each row.

Values

There are many ways to express.

• Literal values
• Variable
• An expression
• The value of the function
• ...

Operation

• Like. Used to match a string and a given pattern. Wildcard characters % and_
• Glob. It's like the likes. Case sensitive, wildcard characters * and ? # # #限制和排序
• limit. Not in ANSI Standard
• offset. Not in ANSI Standard
• order by, ASC default.

Simultaneous use limit and offset when, can be used , instead ofoffset

can use limit no offset , use offset must use limit

Functions and Totals (aggregate) categories (grouping)

group by. Executes between the WHERE statement and the SELECT statement. The input is the output of the where statement and divides it into groups. Each group enters a SELECT statement, outputting a total (aggregate) result.
havingFilter group by the output group. expressed in statistical form.

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


Remove Duplicates

distinct

Joining tables

foreign Key: The value in one column of a table is the primary value in another table, a relationship called a foreign key.

Use a foreign key to join the table.

sqlite> select foods.name, food_types.name   ...>         from foods, food_types   ...>         where foods.type_id=food_types.id limit 10;name        name----------  ----------Bagels      BakeryBagels, ra  BakeryBavarian C  BakeryBear Claws  BakeryBlack and   BakeryBread (wit  BakeryButterfing  BakeryCarrot Cak  BakeryChips Ahoy  BakeryChocolate   Bakery

Inline

Inline: Two tables are connected by the relationships of the columns in the table, most commonly and most importantly.

Inline uses the _ 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 Bakery2 1 Bagels, RA 2 Cereal3 1 Bavarian C 3 chicken/f           O4 1 Bear Claws 4 CONDIMENTS5 1 Black and 5 Dairy6 1 Bread (Wit 6 DIP7 1 butterfing 7 Drinks8 1 Car          Rot Cak 8 Fruit9 1 Chips Ahoy 9 Junkfood10 1 Chocolate 10 Meat11 1 Chocolate rice/pasta12 1 Chocolate Sand          Wiches13 1 Cinnamon B Seafood14 1 Cinnamon S SOUP15   1 cookies   Vegetables 

Cross Connect

Connecting each column of table A with each column in table B produces a lot of meaningless results.

Outreach

Outreach : The outreach selects all rows inline and some rows that do not meet the relationship criteria.

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

Foods is the left table, and for each row of foods, try to establish a connection and pass the relationship condition. Qualifying lines are displayed, non-conforming will be shown, and episodes columns appear null.

Full Outreach: A collection of left-and right-hand outreach.

Natural connection

The special case of the inline. Connect through the same column name. If the name of the column changes, the result will be different, so try not to use it.

Recommended syntax

Use an explicit join syntax.

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

This is the old-fashioned syntax.

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

This is a new kind of grammar.

Names and aliases

select f.name as food, e1.name, e1.season, e2.name, e2.seasonfrom episodes e1, foods_episodes fe1, foods f,     episodes e2, foods_episodes fe2where  -- 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;   

Sub-query (Subquey)

The result of the select is used as input from the From and to clause.

Federated queries (compound query)

A bit like the opposite of a subquery. Use union , intersect except to process the results of multiple queries.

Pre-conditions

• The same number of columns as the result to be processed
• There can be only one order by statement at the end of a union query.

Conditional results (conditional results)

• Using 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 descriptionfrom foodswhere description is not nullorder by namelimit 10;

• Using an expression in when

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) frequencyfrom foods fwhere frequency like '%High';

Processing NULL

SQL for SQLite