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);