SQLite notes (akaedu)

1. Create a SQL table
CREATE table student (ID integer primary key, name text, score integer);

2. Insert a record
INSERT into student (score, ID, name) VALUES (1, ' xiaoming ');
INSERT into student values (2, "Xiaozhang", 90);
If the primary key is not available, it will automatically assign a record to you, and the other fields without the NOT NULL constraint are nullable (NULL) by default.
INSERT into student (name) VALUES ("Xiaoliu");

3. Simple query statements
Select ID, name from student;
SELECT * from student;

4. Modify a record (where clause is important, there is no where to modify all records)
Update student set score=80, name= "Xiaowu" where id=3;

5. Delete a record
Delete from student; No WHERE clause deletes all records
Delete from student where id=3;

6. Bulk import of data
This is not an SQL statement, it is a command of the Sqlite3 tool
. Import 1.txt Student

7. Modify the structure of the table
ALTER TABLE student add Score2 integer;
You can use the command. Schema student view the student table structure.
ALTER TABLE student Rename to newstudent;
However, modifying an existing field is not supported. (No modify operation)

8. Back up the contents of a table (back up the contents of the table, but there may be differences in the table structure)
Back up all the contents of the student table to the new table Newstudent
CREATE TABLE Newstudent as SELECT * from student;
Back up the first three columns of the student table into the new table newstudent
CREATE TABLE newstudent as select ID, name, score from student;

9. Delete a table
drop table student; Delete Student table

10. Complex query Statements
SELECT * FROM student where score>80; query for students with a score greater than 80
SELECT * FROM student where score>87 and score<100;
SELECT * from student where score between and 100;
Where score between and 100;
Equivalent to where score>=87 and score<=100;

Fuzzy query
SELECT * FROM student where score like "9%";
SELECT * from student where name is like "%g";
SELECT * FROM student where score like "87"; equivalent to select * from student where score=87;

Sort output
SELECT * FROM student order BY score Desc; Descending
SELECT * FROM student order by score ASC; ascending
Order By default is in ascending order

Find two students with the lowest score of 80 points:
SELECT * FROM student where score>=80 order by score ASC limit 2;

Looking for a third-place student in the class:
SELECT * FROM student ORDER BY score DESC LIMIT 1 offset 2;

To find one or several students who have the highest performance in a class:
SELECT * FROM student where score= (select score from student ORDER BY score DESC LIMIT 1);

GROUP BY clause (having a conditional clause of GROUP BY)
Select DEP, SUM (salory) from the employee where salory>4000 group by DEP; The sum of each department's salary by department per month
Select name from the employee group by name, Salory, DEP has count (*) >1;//to find out the name of the person who has repeated data entry

Connect the contents of two sheets:
Sqlite> select * from student;
1| Xiaoming|21
2| Xiaozhang|22
3| Xiaowu|19
Sqlite> select * from score;
1|100
2|96

1.where clause connection two sheets
Select a.ID, A.name, A.age, b.score from student A, score b where a.id=b.id;
1| xiaoming|21|100
2| xiaozhang|22|96

2. Natural connection (requires a field with the same name in both tables, records with the same field value are connected to the output)
Select ID, name, age, score from student natural join score;
1| xiaoming|21|100
2| xiaozhang|22|96
If two tables do not have the same name field (student's Id,score ID name is the same), the connection cannot be successful, the output of the Cartesian product of the two tables
Select ID, name, age, NID, score from student natural join NewsCore;
1| xiaoming|21|1|100
1| xiaoming|21|2|96
2| xiaozhang|22|1|100
2| xiaozhang|22|2|96
3| xiaowu|19|1|100
3| xiaowu|19|2|96

Left OUTER join (the table in the left side, even if there are no successful entries in the right table, the output will be.) ）
Select a.ID, name, age, score from student A to outer join score B on a.id=b.id;
1| xiaoming|21|100
2| xiaozhang|22|96
3|     xiaowu|19| = = This one is output due to the left outer connection, note the comparison with the following

Select a.ID, name, age, score from score B to outer join student A on a.id=b.id;
1| xiaoming|21|100
2| xiaozhang|22|96

SQLite notes (akaedu)