SQL database syntax learning notes

Recently I came across a project , Need to use SQL Access data in the database area , I found that I can only use some basic instructions and can't freely operate the results I want in the project , then , I went online again and sorted out the instruction set syntax of the database .

1. The most basic addition, deletion, modification and query instructions

Instructions	effect
insert	insert data
delete	Delete data
select	Find data
update	modify / Update data

Basically, most database operations are composed according to the above four instructions .

2.Select Search instructions

select I personally think instruction is the most important , So first analyze this point .

Operation action	Instructions
Traverse all the values	select *（ All columns ） from tablename( The name of the table )
Specifies to traverse a column or columns	select colunm_name( Name ) from tablename
Through conditions （where） To filter the data that meets the conditions	select *（ All columns ） from tablename where colunm_name = ‘xxxx’（ Text ） perhaps select *（ All columns ） from tablename where colunm_name = xxxx（ Integers ）
Multiple conditions (and perhaps or) To filter data	select *（ All columns ） from tablename where （colunm_name = ‘xxx’ or colunm1_name = ’ xxx’）and colunm_name=‘xxxx’
Through wildcards (Like) Maladjustment (%,_,[])	select *（ All columns ） from tablename where colunm_name=’%xx’
Arrange the data table order by( Ascending ) perhaps order by xxx desc( Descending )	select * from tablename order by Name / order by Name desc
Get different options in the column	select distinct colunm_name from tablename
Specify that a column satisfies multiple values (in)	select *（ All columns ） from tablename where colunm_name in(value1,value2,value3,…)
Insert data from one table into another	select *（ All columns ） into newtablename from oldtable （where xxx）
Retrieve two tables at the same time （ Usually the primary key primary key And foreign keys ）	select *tableonename.xxx( Name ),tabletwoname.xxx from tableonename( Table name ),tabletwoname where tableoneonename.number = tabletwoname.number

The above table is just a template for instructions , But many times, we need to pass some examples to make some examples understand , To draw inferences from one instance , So next, I will demonstrate the above template through some simple examples .

First create a table

create table if not exists studenttable( ID integer primary key,Name vchar(20) not null,Class vchar(10) not null,Score int)"
 //primary key  Primary key 
 //autoincrement  Auto increment 
 //not null  Can't be empty 

1. Print out everything in the table

 select * from studenttable

Student number	full name	class	achievement
164001	Xiao Ming	3 class	80
164002	Hill	2 class	60
164003	Little ni	2 class	73
164004	Small	4 class	40

2. Just print out the student number and name

select ID,Name from studenttable

Student number	full name
164001	Xiao Ming
164002	Hill
164003	Little ni
164004	Small

3. Satisfying the class is 2 Class

select * from studenttable where Class = '2 class '

Student number	full name	class	achievement
164002	Hill	2 class	60
164003	Little ni	2 class	73

4. Satisfying the class is 2 The grade of the class at the same time is greater than 60

select * from studenttable where Class = '2 class ' and Score > 60

Student number	full name	class	achievement
164003	Little ni	2 class	73

5. Filter names with ‘ Ni ’ Students of Chinese characters

select * from studenttable where Name=’% Ni ’

Student number	full name	class	achievement
164003	Little ni	2 class	73

6. Satisfying the class is 3 Class or 4 My classmates

select * from studenttable where Class in('3 class ','4 class ')

Student number	full name	class	achievement
164003	Little ni	2 class	73
164004	Small	4 class	40

7. By specifying the student number in one table to retrieve the information in another table ( important )
First create another table

create table if not exists awardstable( ID integer,Awards vchar(10) not null)"

Student number	prize
164003	The first prize
164006	The second prize
164008	The third prize
164002	The third prize

select studenttable.Name,awardstable.Awards from studenttable,awardstable where studenttable.ID = awardstable.ID

Student number	prize
Little ni	The first prize
Hill	The third prize

8、 Get how many classes there are through classes

select distinct Class from studenttable

class
2 class
3 class
4 class

delete Delete instruction

Operation action	Instructions
Delete all data	delete from tablename（ Table name ） /delete * from tablename
Delete a column in the table equal to a specific value	delete from tablename( Table name ) where colunms_name = ‘xxxx’

Follow the example of the student form above
1. Empty the student list ( Just clear the data , Data and structures still exist )

delete from studenttable

2. Delete... From the table 2 My classmates

delete from studenttable where Class = '2 class '

Student number	full name	class	achievement
164003	Little ni	2 class	73
164004	Small	4 class	40

To be continued ......