Summary of statements commonly used by MySQL in projects

Let you quickly review the notes of the statement.

Create Table users (
Username varchar (20) primary key,
Userpwd varchar (20)
)

Alter table users add age int

Insert into Stu (sname) values ('sdfdsfdsfeeeeee ')
Update CJ set Cj = 60 Where sid = 1

Delete from CJ where Sid = 2

-- All columns in all rows

Select * from Stu
-- All columns of some rows
Select * from Stu where Sid> 3
-- Some rows and columns
Select sname from Stu where Sid> = 3
-- All rows in a column
Select sname from Stu

-- MySQL limits M Stu limit 3

Select * from Stu limit 3, 2

-- Sort
Select * from Stu order by SID ASC

-- Fuzzy search
Select * from Stu where sname like '% Aa %'

-- Aggregate function usage
Select sum (C. CJ) from CJ as C

Select count (*) from Stu

Select count (userpwd) from users

-- Group
Select count (*), age from Users Group by age
-- Use having for grouping queries
Select count (*), age from Users Group by age having count (*)> = 2

--- Null Query
Select * from users where userpwd is not null

Select * from users where age between 20 and 30
Select * from users where username between 'a 'and 'l'

-- In subquery
Select * from Stu where Sid in (1, 2, 3, 4, 7, 6, 8, 9)

-- Internal Connection
Select * from Stu inner join CJ as C on Stu. Sid = C. 'sid'
Where Stu. Sid = 1

-- Equijoin
Select * from Stu, CJ as C where Stu. Sid = C. 'sid'

-- External Connection
Select * from CJ as C right join Stu on Stu. Sid = C. Sid

--- Subquery
Select * from Stu where age> (
Select age from Stu where sname = 'lisi'
)

Select * from Stu
Select * from CJ

Select * from Stu where sid not in (
Select Sid from CJ
)

Select * from Stu inner join CJ as C on Stu. Sid = C. Sid

Select * from Stu left join CJ as C on Stu. Sid = C. Sid
Where C. 'sid 'is null