Sql&sqlite

Registered Blog Park for more than a year, every time is to find some information, never wrote something, prompting me to start blogging reasons mainly have two points

First, in the process of finding information, often need to repeat the search for a knowledge point, a knowledge point of time long after always forget, so the process of repetition is a waste of a lot of time, put things down on the one hand is to facilitate their own query, on the one hand to deepen their understanding.

The second is to improve their writing skills, often encountered in the work of the preparation of materials, and their own writing ability is very general, it is necessary to do this exercise.

Nothing to say, go into the subject.

1. What is SQL and SQLite

SQL (Structured Query Language) is a "Structured Query Language", which is the operating language of a relational database. It can be applied to all relational databases, such as MySQL, Oracle, SQL Server, and so on.

SQLite is a software library that implements a self-contained, server-free, 0-configured, transactional SQL database engine. SQLite is one of the fastest growing database engines, which is growing in popularity, regardless of its size. SQLite source code is not subject to copyright restrictions.

2. Syntax requirements for SQL and SQLite

SQL statements can be written one or more lines, end with a semicolon, use spaces and indents to enhance the readability of the statement, the keyword is case-insensitive, and lowercase is recommended;

All SQLite statements can start with any keyword, such as SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, and so on, all of the statements end with a semicolon (;). One important point to note is that SQLite is case insensitive, but some commands are case-sensitive, such as GLOB and GLOB have different meanings in SQLite's statements.

3, the SQL language can be divided into four categories

DDL (data definition Language): The definition language used to define database objects: libraries, tables, columns, etc.;

DML (Data Manipulation Language): An operational language for defining database records (data);

DCL (Data Control Language): The language used to define access rights and security levels;

DQL (data query Language): The Language for Querying records (data).

4. SQL and SQLite data types

Sql

int: integral type

Double: a floating-point type, such as double (5,2), that represents up to 5 bits, which must have 2 decimal places, that is, a maximum value of 999.99;

Decimal: Generic, use this type in the form of money, because there is no problem of lack of precision;

Char: fixed-length string type;

VARCHAR: variable-length string type; 255

Text: String type;

BLOB: Byte type;

Date: The format of the type: YYYY-MM-DD;

Time: The format of the type: HH:MM:SS

Timestamp: timestamp type; YYYY-MM-DD hh:mm:ss

Sqlite

Null:null value

Integer: Signed integers stored in 1, 2, 3, 4, 6, and 8 bytes, depending on the value

REAL: Floating-point value, an IEEE floating-point number stored as 8 bytes

Text: literal string, stored using database encoding (UTF-8, utf-16be, or Utf-16le)

Blob:blob data, which is stored completely according to its input

5, the following details about the various commands of SQLite

The SQLite command is similar to SQL. Commands include CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP.

To create a database:

$sqlite 3 databasename.db

CREATE TABLE: (NOT NULL is a constraint that indicates that these fields cannot be null when records are created in the table)

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Delete a table (to determine if the table exists, otherwise it will be an error):

sqlite>.tables
COMPANY       test.COMPANY

sqlite>DROP TABLE COMPANY;
sqlite>

sqlite>.tables
sqlite>

Insert data:

There are two ways to insert data into the following table

 
sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Way One:

VALUES (1, ' Paul ', +, ' California ', 20000.00);

Way two:

INSERT into company VALUES (7, ' James ', +, ' Houston ', 10000.00);

Delete data:

Suppose the company table has the following records

 
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Delete a customer with an ID of 7

Sqlite> DELETE from company WHERE ID = 7;

Delete all data

Sqlite> DELETE from company;

Update data:

Suppose the company table has the following records

 
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Here is an instance that updates the customer address with ID 6:

Sqlite> UPDATE company SET ADDRESS = ' Texas ' WHERE ID = 6;

If you want to modify all the values of the ADDRESS and SALARY columns in the company table, you do not need to use the WHERE clause, as follows:

Sqlite> UPDATE company SET ADDRESS = ' Texas ', SALARY = 20000.00;

Query data:

Suppose the company table has the following records

 
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Here is an instance that uses the SELECT statement to get and display all of these records. Here, the first three commands are used to set the correct formatted output.

Sqlite>. Header onsqlite>. Mode columnsqlite> SELECT * from company;

Finally, you will get the following results:

 
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Sql&sqlite