MySQLの各種select命令

CREATE DATABASE lianxi CHARSET utf8;
#      stu       id         sname sage ssex
CREATE TABLE stu (
	id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
	sname VARCHAR(32) UNIQUE,
	sage INT,
	ssex BOOL
)

#    
SELECT * FROM stu;

#        
INSERT INTO stu (sname,sage,ssex) VALUE('czh',24,1),('xkm',23,0);
INSERT INTO stu (sname,sage,ssex) VALUE('xyz',17,1),('abc',20,1);
INSERT INTO stu (sname,sage,ssex) VALUE('jj',22,1),('kg',13,0);

#             
SELECT sname FROM stu;
#       
#             
SELECT sname AS sn FROM stu;
#          ，      (           )
SELECT sname AS '  ',ssex AS '  ' FROM stu;
#                ，     .    
SELECT s.sname FROM stu AS s;



#                  ？
#         18  
SELECT * FROM stu WHERE sage >18;
#         18   
SELECT * FROM stu WHERE sage >18 AND ssex=1;
#        c    /    
SELECT * FROM stu WHERE sname LIKE 'c%';
SELECT * FROM stu WHERE sname LIKE '%c';
#           
SELECT * FROM stu WHERE ssex <> 1;
#  id （1,2,4,7）     
SELECT * FROM stu WHERE id IN (1,2,4,7);
#  id 2 4    （  2 4）
SELECT * FROM stu WHERE id BETWEEN 2 AND 4;
#  id 1 2 5   
SELECT * FROM stu WHERE id IN (1,2,5);
#         
SELECT * FROM stu WHERE ssex IS NOT NULL;
#       
SELECT * FROM stu ORDER BY sage;
#       
SELECT * FROM stu ORDER BY sage DESC;

#    
#      
SELECT COUNT(*) FROM stu;

#    ,   ,   ,  
SELECT MAX(sage) FROM stu;
SELECT MIN(sage) FROM stu;
SELECT AVG(sage) FROM stu;
SELECT SUM(s_ge) FROM stu;
# id  3           
SELECT AVG(sage) FROM stu WHERE id>3 AND ssex=1;


#      
SELECT sage FROM stu GROUP BY sage;
#          
SELECT sage,GROUP_CONCAT(sname) AS '  ' FROM stu GROUP BY sage;
 
#  
LIMIT       +1                            
SELECT * FROM stu WHERE ssex=1 LIMIT 1,2;

CREATE TABLE person (
	id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
	pname VARCHAR(32) UNIQUE,
	page INT
)

CREATE TABLE goods (
	id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
	gname VARCHAR(32) UNIQUE,
	gprice INT,
	g_p_user_id INT
)


#             id      id             
SELECT * FROM goods WHERE g_p_user_id=(SELECT id FROM person WHERE pname='  ');
#           
SELECT SUM(gprice) FROM goods WHERE g_p_user_id=(SELECT id FROM person WHERE pname='  ');
#    
SELECT * FROM goods INNER JOIN person ON goods.g_p_user_id=person.id;
#    
SELECT * FROM goods LEFT JOIN person ON goods.g_p_user_id=person.id;
#    
SELECT * FROM goods RIGHT JOIN person ON goods.g_p_user_id=person.id;

#    
SELECT * FROM goods,person WHERE goods.g_p_user_id=person.id;

#    ：     ，            
SELECT * FROM goods,person;

SELECT * FROM stu;
#       
SELECT sage AS '  ' FROM stu GROUP BY sage;
#group by      where    （ having       ）
SELECT * FROM stu GROUP BY sage HAVING ssex=0;


SELECT g_p_user_id AS a FROM goods GROUP BY a;
#        
SELECT * FROM person WHERE id IN (SELECT g_p_user_id AS a FROM goods GROUP BY a);