Basic operation of SQLite database

Details: 1, what is the primary key:           is used to uniquely identify one piece of data in a table, all tables must have their own primary key, the primary key can be integral type, generally use self-increment, It can also be a string type,  if it is a string type, the UUID is generally used (generated in the Java language). A primary key is typically used to uniquely query a record, or an update deletes a record.  2,order by is always put to the last side, syntax specification!  3, Functions           functions count: Used to count the number of rows in general statistics                  functions Max:       is used to calculate the maximum value in a column, The type of the column must be an integral type (in general, a similar age, a score, and so on, the maximum value may be calculated in the future, or it is a number, the type is set to integer).            function sum:       Similar to Max, the field type of the column must be integer  4, left connection: The left table is all displayed, The right side shows a successful match. 5, Full connection: the left and right sides are perfectly matched successfully. 6,sqlite------------INSERT INTO students (Id,name,age,sex) VALUES (' 3 ', ' Mike ') with no right connection   -------------------table insertion ', ' 21 ', ' Female ');--addinsert into students (id,name,age,sex) VALUES (' 4 ', ' Lisa ', ' 21 ', ' Female ');--addinsert into students (ID, Name,age,sex) VALUES (' 5 ', ' Raro ', ' 21 ', ' Female '),--addinsert into students (ID,NUM,NAME,AGE,SEX,C_ID) VALUES (6, ' 201107014303 ', ' Jame ', ' 21 ', ' Female ', ')------------conditions are removed---------------------Delete from students where name= ' Jame ';--deleteselect *from students;select *from students where name= ' Mike ' and age= '; select * from students where name like ' s% ' collate nocase;------------Modify table name------------ALTER TABLE STUD ENT Rename to students--modify table name alter tables Students {Rename to student|add column course text}--unrecognized {}: represented as optional, but failed to run  --- ---------add Column---------------------ALTER TABLE students ADD column tel text not null default ' collate nocase -------- ----Conditions Modify Data-----------Update students set tel= ' 18500111111 ' where name= ' Lisa ' collate nocaseupdate students set num= ' 201107014301 ' Update students set age= ' where name= ' Mike '  ----------created table, id,course two columns CREATE TABLE course (ID Integer primary key AutoIncrement, course text);------------Table Delete--only the table can be deleted, the column cannot be deleted----------drop table course;-- Delete a table (no associated relationship)  -----------If you want to ignore case, that is, case-insensitive, you need to use COLLATE nocase:-----------Update students set age= ' 12 ' Where Name= ' Mike ' collate nocase; ----------execute the following statement to see all usersUsername and age: Select Name,age from Students;select *from cqx_db where type= ' table ' and name= ' teacher ';  --view a table Field name does not duplicate entry select DISTINCT name from Students; select * from students group BY age have count (*) >1; ----- -------- limit a offset B or limit B, a---indicates that item B has been transferred, returns a data--------SELECT * from students where num like ' _011% ' ORDER by age ASC, name limit 1,2;--if same, then sort by first letter of first name S Elect * from students where num like ' 2011% ' ORDER BY age ASC--num ends with 2011 for all students Ascending, default to ascending select * from students where num Like ' 2011% ' ORDER BY age desc, ages limit 2 offset 1;--based on aging sort select * from students where num like ' 2011% ' limit 2 offset 1; --------------- -----'%a ': End With A (_a), ' a% ': Start with a '%a% ': contains a-------------------------------SELECT * FROM students where num like '%01 ' ORDER by age Desc; Matches with a 01 end of SELECT * from students where NUM is not a like '%02% '; select num with no 02-- function Upper (name) uppercase, lower (name) lowercase, COUNT (*) statistics size, sum (age) summation, Max (age) asks maximum valueSelect upper (name), length (name) from the students where num like ' 2011% ' select lower (name), length (name) from students where num like ' 2011% ' select upper (name), length (name) from students where num like '%01 ' and length (name) <5---------- Grouping group by-and using functions to calculate rent size---------SELECT *, COUNT (*) from the students group by num;--groups by NUM SELECT COUNT (*) from students where num like '%01 ';------ ------------------- Remove Duplicate distinct-----------------------SELECT distinct num from students; --------------- Multi-table join join-------------------------insert INTO teacher (Id,t_name,t_course) VALUES (1, ' a ', ' language ') insert INTO teacher (id,t_name,t_ Course) VALUES (2, ' a ', ' language ') insert into teacher (Id,t_name,t_course) VALUES (3, ' a ', ' language ') Update teacher set c_id= ' 1 ' update Students set c_id=12 where num like '%01 '; update teacher set c_id=1; --Add column NOT NULL when default ' ALTER TABLE teacher Add column c_id text not NULL default '; ALTER TABLE teacher drop column C_course;select Teacher.t_nam E,students.name from teacher, students where teacher.c_id=students.c_id; ------------------------connections inner Join-------------- select * FROM teacher inner joins students on Teacher.c_id=students.c_id;alter table course add col Umn ID integer;insert into course values (1, 1, ' languages '); INSERT into course values (1, 2, ' languages '); select * FROM students inner JOIN c Ourse on students.c_id=course.c_id;---------------Cross Connect-----------------select * from Students,course;----------Left outer connection Failed to match left base, right null--, no right connection------------select * FROM students ieft outer join teacher on students.c_id=teacher.c_id; & nbsp;--------------------aliases-similar to table names. The same column name, from + original alias------------------------Select S.name,t.name from students S, Teacher T where s.c_id=t.c_id limit 3;   --------------null is not equal to a placeholder for the exact information of any value--three logical operations select NULL is null;- -Result 1 No value of 0 is represented as true select NULL is not null;--result 0 not 0 any value is represented asTrue  -------------The COALESCE function is expressed as entering a set of values and returning the first non-null value------Select COALESCE (null,7,null,4);-The result 7  --- ----------the Nullif function, which is represented as 2 value input, returns null if the same, otherwise returns the first parameter----select Nullif;--results nullselect nullif;--Results 1   -------Sub-query-------------Select Teacher.name from teacher where teacher.c_id in (select students.c_id from students where num like ' 2011% ') selects count (name) from students where students. [C_ID] In (select teacher.c_id from Teacher) SELECT * FROM students GROUP by num like '%01 '     -------- -================== Assessment Exercise ================================--------Query select * FROM students where name= ' Lisa ';  select name,sex from students where name is like '%am% '; --Query All name,sex-----groupings that contain AM-------Select Sex, COUNT (ID) from students group by sex; --Query the sex and the number of uses based on the gender sex subgroup Select Sex,age,count (ID) from students group by sex, age; --according to gender and age group, query gender and age as well as the number of select Name,age from students the order by age Desc; --Query display name and age   -----------------------------based on age descending-function Max: Used to calculate the maximum value in a column, the type of the column must be an integral type (in general, a similar age, a score, and so on, the maximum value may be calculated in the future, or it is a number, type is set to integral type). -------------------------------------------select Max (age) from students; --Select the age maximum from the student table  --the name and corresponding age of the oldest from the student's table select name, and ages from students where the age of (select Max (age) from students) &nbs p;--calculates the age in the Student table and select sum (aged) from students;  --groups by gender and displays gender and corresponding and select Sex, COUNT (ID) from students GROUP by SEX;&NB sp;--is all connected by alias, alias rule is from+ original table name alias, other places use to table name alias Select T.t_name,s.name from teacher t,students s where t.c_id=s.c_id;& nbsp;--left connection through student and teacher table query student and teacher name SQLite no right connection select Students.name,teacher.t_name from students ieft join teacher on STUDENTS.C _id=teacher.c_id;--to the left by means of an alias select S.name,t.t_name from students s Ieft join teacher T on s.c_id=t.c_id;-- Query the gender for female and age greater than 21 by left connection S.name,t.t_name,s.age from students s Ieft join teacher T on s.c_id=t.c_id where sex= ' women ' and ages >21;   

Basic operations of the SQLite database