Mysql基础2

清空一个表，自增id从1开始
truncate table 表名;

查询
select 列名 from 表名 where 条件 order by 列名 [desc|asc] limit 跳过条数,查多少条

AS 别名
列名 as 新列名 注意as可以省掉不写
NULL值查询
select * from table1 where 字段 is null;
组合列
select concat(‘No.‘,id) from stu;
去重复
select distinct 列名 from 表名; 注意：列名，只能跟一个
排序
select * from 表名 order by 列名1 asc,列名2 desc;
加工之后的信息排序
select concat(‘No.‘,id) from stu order by concat(‘No.‘,id);
常用函数
select concat(‘My‘,‘S‘,‘QL‘); 连接
select lower(‘PHP‘); 转小写
upper() 转大写
select replace(‘www.mysql.com‘,‘www‘,‘http://www‘); 替换
update stu set name = replace( replace(name,‘o‘,‘0‘) ,‘i‘ ,‘1‘); 将name字段所有的o换为0，所有的i换为1

trim()
ltrim()
rtrim()
select abs(-5) 绝对值
select adddate(‘2013-02-28‘,1); 2013-03-01
select now();

通配符 "%" 任意 "_"单个
select * from stu where name like ‘张_‘;

正则 不支持 \w ...
select * from stu where name regexp ‘^a[a-z][a-z]$‘;

区间操作 [not] between ... and ...
select * from stu where age >=18 and age <=19;
select * from stu where age between 18 and 19;
select * from stu where age not between 18 and 19;

[not] in (值1,值2,...)

sum 求和
avg 平均
count(*) 计数
括号中可以是*，也可以列名
如果括号中是列名，则不会统计该列数据为null值的
max 最大值
select max(age) from stu;
min 最小值

*分组查询
一条语句得到男生和女生分别是多少人?
select sex,count(*) from stu group by sex; #分组后统计
注意：字段只能是用于分组的字段(group by 后的字段) 或聚合函数

按科目分组统计平均成绩

分组带排序
select sex,avg(age) as avg from stu group by sex order by avg desc;

*分组后筛选 having
按性别分组，求平均年龄大于等于6
select sex,avg(age) as avg from stu group by sex having avg>=6;
按性别分组，求平均年龄大于等于6，按平均年龄从大到小排列
select sex,avg(age) as avg from stu group by sex having avg>=6 order by avg desc;

where 与having可以同时出现，where在筛选之前就执行了，having是在分组之后才执行

显示人数超过15人的班级，按班级人数排序
select grade_id,count(*) from stu group by grade_id having count(*)>15 order by count(*) desc;
select grade_id,count(*) total from stu group by grade_id having total>15 order by total desc;

WHERE -> GROUP BY -> HAVING ->ORDER BY


成绩表
create table score (
id int auto_increment primary key,
stu_id int,
subject varchar(50) ,
score int
) engine=myisam default charset=utf8;

INSERT INTO score (stu_id,subject,score) values (1,‘php‘,65),(1,‘mysql‘,95),(1,‘linux‘,80),(2,‘php‘,50),(2,‘mysql‘,70),(2,‘linux‘,70);

统计及格总人数和及格人数的平均分
select subject, count(*),avg(score) from score where score>=70 group bysubject;
统计及格总人数和平均分在80以上


多个学员不低于90分的班级
select 班级 from 表名 where score >=90 group by 班级 having count(*)>1


显示：id,姓名,班级名称
select stu.id,stu.name,grade.name from stu,grade where stu.grade_id=grade.id;
显示：id,姓名,班级名称,age大于10的
select stu.id,stu.name,grade.name,age from stu,grade where stu.grade_id=grade.id and age>10;

select stu.id,stu.name,grade.name,age from stu
left join grade on grade.id=stu.grade_id
where age>10;

左联接
左表中的数据，全部出来，就算右表没，没有对应的数据
select stu.id,stu.name,grade.name,age from stu
left join grade on grade.id=stu.grade_id

右联接
select stu.id,stu.name,grade.name,age from stu
right join grade on grade.id=stu.grade_id

用右连接，实现和前面一样的效果
select stu.id,stu.name,grade.name,age from grade
right join stu on grade.id=stu.grade_id

内联 inner可以省掉
select stu.id,stu.name,grade.name,age from stu
inner join grade on grade.id=stu.grade_id
内联与这样结果相同： select * 表1,表2 where 条件


显示所有参加考试的男生的成绩
select score.*,stu.name from stu join score on score.stu_id=stu.id where sex=1;
select score.*,stu.name from stu ,score where stu.id=score.stu_id and sex=1;
先查男生的id，然后通过id列表，去成绩表中查询
select id from stu where sex=1; select * from score where stu_id in (1,6,8);
select * from score where stu_id in (select id from stu where sex=1);


查询科目及格人数和不及格人数：
科目 及格人数 不及格人数
phy 20 1
mysql 26 2

select subject,sum(case when score<60 then 1 else 0 end) as no,
sum(case when score>=60 then 1 else 0 end) pass
from score group by subject;