MySQL Common query statement review

A note book that lets you review your sentences quickly.

CREATE TABLE Users (
Username varchar (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 rows all columns

SELECT * FROM Stu
--Some rows all columns
SELECT * from Stu where sid>3
--Some rows some columns
Select Sname from Stu where sid>=3
--All rows in a column
Select Sname from Stu

--mysql limiting 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

--Grouping
Select COUNT (*), age from the Users group by age
--After grouping the query using having
Select COUNT (*), age from the Users group by age has count (*) >=2

---null query
SELECT * from users where userpwd are NOT null

SELECT * from the users where age between and 30
SELECT * from users where username between ' a ' and ' l '

-In sub-query
SELECT * from Stu where SID in (1,2,3,4,7,6,8,9)

--Inner connection
SELECT * FROM Stu INNER join CJ as C on stu.sid=c. ' Sid '
where stu.sid=1

--Equivalent connection
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

---sub-query
SELECT * from Stu where age> (
Select age from Stu where sname= ' Lisi '
)

Reprinted from: http://blog.csdn.net/supera_li/article/details/9303709

MySQL Common query statement review