SQLite easy to get started

The content of this article source: Https://www.dataquest.io/mission/129/introduction-to-sql

Data source used in this article: Https://github.com/fivethirtyeight/data/tree/master/college-names

Summary: The main introduction of SQLite some simple operation (increase and revise)

Raw data presentation (the database facts stores the following table, the main field description:

Code-country code, name-country name, area-size of land, created_at-time to create this table)

Manipulating Data queries

#数据展示 SELECT [ColumnA, COLUMNB, ... ] from tableName; #如: Querying data for code and name two columns in the database SELECT  Code, namefrom  Facts; #条件查询, use where to define query conditions, for example, to query country codes and names with a population greater than 100 million SELECT code , namefrom  FactsWHERE population>100000000;

Comparison symbols that can be used in the WHERE statement:

• Less than: <
• Less than or equal to: <=
• Greater than: >
• Greater than or equal to: >=
• equals: =
• Not equal to:! =

You can compare date data directly in SQLite, for example, by querying for data created before 2015-11-01 14:00

SELECT *  from  WHERE< ---£ º xx"

Note: In double quotes, data in date format must be in the format of YYYY-MM-DD HH:MM:SS

Limit the number of data returned

# sometimes just want to see the first few of the returned data, use limit, such as: Query the first five data

Select *  from 5;

logical operators

You can use logical operators and and or to combine multiple query conditions

SELECT [column1, Column2,...]  from [table1]WHERE [Condition1]  and [Condition2]#如, countries with a population greater than 100 million and land area greater than 100000SelectName fromFactswherePopulation> 100000000  andArea_land> 100000LimitTen;

Combining multiple query criteria

#如: Find data with birth rates greater than 20 or mortality less than 10 in countries with an excessive population Select name  from Facts where > 100000000  and (Birth_rate  >  - or < ( ten);

Sort

Specify the sorted column by the ORDER BY statement, ASC is ascending, desc is descending

SELECT [column1, Column2,...]  from [table1]WHERE [conditions]..ORDER  byColumn1[ASC or DESC]# Sort by country name in descending orderSelectName fromFactsOrder  byNamedescLimitTen; #多列排序, for example, in a table that stores people's names, first sort the last name, and then sort the names in the same last nameSelect [column1, Column2.] fromtable_nameOrder  byColumn1 (ASC or desc), Column2 (ASC or desc)

Query the data type of a table , SQLite mainly has the following data types:

• integer-Similar to an integer type in python
• REAL-Similar to the type of floating-point numbers in Python
• Float-similar to the type of floating-point numbers in Python
• TEXT-Similar to string types in Python
• VARCHAR (255)-Similar to string types in Python

The same type has different names because SQLite is used for compatibility with other databases, querying the data type of a table, using the pragma statement

PRAGMA Table_info (tableName); #结果会返回一个列表 (interception): [[0, "id", "INTEGER", 1, NULL, 1][1, "code", "varchar (255)", 1, NULL, 0  ][2, "name", "varchar (255)", 1, NULL, 0]]

The contents of the list are as follows, and PK = 1 indicates that the field is a primary key:

Insert

Insert data into the database, using the INSERT statement, note that date data must conform to the format: Yyyy-mm-dd HH:MM:SS

INSERT  intoTableNameVALUES(value1, value2, ...); #例子:INSERT  intoFactsVALUES(262, "DQ", "Dataquestland",60000,40000,20000,500000, -, -,Ten, -, " .- Geneva- -  A:xx:xx", " .- Geneva- -  A:xx:xx");

If there are null values in the data to be inserted, use NULL instead.

Update

 update   tableName  set  column1=  value1, Column2=  value2, ...  where  column1=  value3, Column2=  value4, ... # For example, change the "states" to Dataquestland  update   facts  set  name=   "  Dataquestland   " where  Name=   " united states   "; 

Delete

DELETE  from TableName WHERE column1=value1, column2=value2, ...; # For example, remove all data named Canada Delete from   Factswhere name='  Canada';

Interacting with Python and SQLite

SQLite database does not need to open a separate server process, and all the data are stored in a file on the hard disk, starting from python2.5, SQLite is integrated in the Python language, so there is no need for an additional installation library to operate SQLite

Connecting to a database

= sqlite3.connect (' countries.db ') #使用connect () method to connect to the database, which specifies the name of the database

Python uses the cursor object to manipulate SQL statements, and the cursor object can perform the following functions:

• Querying the database
• Parse the results returned from the database
• Convert the returned results of a database to a Python object
• Save the cursor's value as a local variable

Note: The cursor object saves the returned results to a list of tuples, as follows

Import Sqlite3conn=sqlite3.connect ("countries.db") # Connect to databasecursor =Conn.cursor() # Returns a Cursor object query= 'select name from facts;'# Save the query statement as a string formatcursor.ExecuteExecute Query statement names= cursor. Fetchall () # Save all returned resultsPrint(Names[0:2]# show top two data # results: A list, each element in the list is a tuple[(' Afghanistan, '), (' Albania ',)]

Sometimes it may be necessary to return the results of a query, that is, with Fetchone (), you need to return n results at a time, that is, with Fetchmany (n). Because an internal counter is saved in the Cursor object, the counter is incremented each time a return result is obtained

# The above query statement is equivalent to this import sqlite3conn=sqlite3.connect ("countries.db") # Connect to databasecursor =Conn.cursor() # Returns a Cursor object query= 'select name from facts;'# Save the query statement as a string formatcursor.ExecuteExecute Query statement names= cursor. Fetchmany (2) # Save the first 2 resultsPrint(names)

When a process is connected to a SQLite database, other processes cannot access the database, so the connection needs to be closed after the database is finished, and note that changes to the database are automatically saved and effective when the connection is closed.

= sqlite3.connect ("Countries.db") Conn. Close ()

Operation Table Structure

All of this is done with the data in the table, and now you want to manipulate the structure of the table, such as creating a new table, adding a column, etc.

Add columns

# Add a new column to a single table ALTER TABLE TableNAme ADD ColumnName datatype;# For example, add a column named Leader to the table with the data format text Alter Table Add text;

Create a new table

CREATE TABLE dbname.tablename (   PRIMARYKEY,   column2 dataType2,   column3 dataType3,   ...); # For example, create a new table in database Factbook to hold information about the country's leaders createtable  factbook.leaders (    integer PRIMARY KEY ,  # Specifies the primary   key text,   text);

To create a table with a foreign key

CREATE TABLE factbook.leaders (   integerPRIMARYKEY,   text,    integer ,    float ,    FOREIGN KEY REFERENCES Facts (ID)  # The foreign key specifies the table to connect to);

Multi-Table Union query

SELECT [column1, Column2, ...]  fromtableName1INNER JOINtableName2 # Specify a different table onTablename1.column3==tablename2.column4;# For example, find the same number of data in two tablesSELECT *  fromLandmarksINNER JOINFacts onLandmarks.country==facts.id;# except for inner join and left .OUTER JOIN(Left outer connection)

In the example above, landmarks is on the left table, facts is on the right table, and when the join is executed, it becomes

The red line to the left is the value of the Landmarks table, the right side is the value of the facts table, because the ID and name two fields are duplicated, so the table on the right is prefixed with the suffix 1

• INNER JOIN-Displays only those values that match the criteria of the query, in which the country value on the left equals the value of the right id_1
• Left OUTER JOIN-the other fields in the merged table are displayed as NULL when there are mismatched data in the table.

For more information on join, refer to: http://www.yiibai.com/sqlite/sqlite_using_joins.html

SQLite easy to get started