Mysql实战中常用sql的分析

1.查询具体的最高、最低、平均值、大于平均值、小于平均值

-- 查询Score表中的最高分的学生学号和课程号。
SELECT * FROM students s, scores c WHERE s.sno = c.sno AND c.degree = 
(SELECT MAX(degree) FROM scores);

• 套路：1.求出最大值;2.学生表和成绩表连表或者子查询

2.查询每个分类中最大、最小的数量

-- 查询每个职位的最高工资以及人数
SELECT job.jname, res.jid, res.salary, COUNT(1) FROM 
(SELECT * FROM emp, 
	(SELECT MAX(salary) as max, job_id as jid FROM emp GROUP BY jid) as max_table
WHERE emp.job_id = max_table.jid AND emp.salary = max_table.max) as res, 
job WHERE res.jid = job.id GROUP BY jid;

• 套路：1.分组求出每个职位的最高工资;2.和职工表连表查询出结果集;3.对结果集连表、分组求出人数、职位、工资

3.从表中复制一条数据并插入(主键自增)

从表中复制一条数据，并插入
insert into student ( Sname,Sage,Ssex ) select Sname,Sage,Ssex from student where Sid =1;

4.查询一条数据，以别名展示

-- 查询出1990年以后出生的学生，并升序排列，
显示字段gender,性别为则显示为'male',女则显示为'female'，非男或女则显示为'other'
SELECT Sname, Sage, Ssex,
(CASE Ssex
	WHEN '男' THEN 'male'
	WHEN '女' THEN 'female'
	ELSE 'other'
END
) as gender
FROM student WHERE SUBSTRING(Sage, 1, 4) > 1990 ORDER BY Sage;

• 套路：使用case...when...

5.查询存在和不存在的情况

查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from
(select * from sc where sc.CId = '01') as t1
left join
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;

套路：1.使用条件查询查出存在01课程的情况;2.根据需求使用内连接或者左连接、右连接查出需要的结果

6.根据数据库名获取数据库所有表字段信息

SELECT a.table_name 表名, 
	a.table_comment 表说明, 
	b.COLUMN_NAME 字段名,
	b.column_comment 字段说明,
	b.column_type 字段类型,
	b.column_key 约束
	FROM information_schema.tables a LEFT JOIN information_schema.COLUMNS b ON a.table_name = b.TABLE_NAME 
	WHERE a.table_schema = 'droi'

7.mysql行转列

select substring_index(substring_index('82,83,84,85,86',',',help_topic_id+1),',',-1) as Id
 
from mysql.help_topic
 
where help_topic_id<(length('82,83,84,85,86')-length(replace('82,83,84,85,86',',',''))+1);

转载于:https://my.oschina.net/chinahufei/blog/3067997