mysql 使用案例必会

必会
ddl:
1、create 创建库、表
2、alter 修改表，包括创建、修改、字段和索引
3、group by 聚合查询
4、distinct 结果集去重
5、union|union all合并结果集

多表联合查询：
1、多表关联修改表记录
update emp a, dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;

2、多表关联删除表记录
delete a,b from emp a, dept b where a.deptno=b.deptno and a.deptno=3;

3、把条件的内容用文字表达出来
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;

常用函数
字符串函数
concat\insert\lower\upper\left\right\lpad\rpad\ltrim\rtrim\repeat\repleace\strcmp(比较ascii码)\trim\substring\
数值函数
abs\ceil\floor\mod\rand\round(四舍五入)\truncate(不四舍五入)
日期和时间函数
curdate()\curtime()\now()\unix_timestamp()\from_unixtime()\week\year\hour\minute\monthname\date_format(date,fmt)\date_add()\datediff(expr,expr2)\
流程函数
select if(salary>2000, 'high', 'low') from salary;
select ifnull(salary, 0) from salary;
select case when salary<=2000 then 'low' else 'high' end as sal from salary;

select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;

//创建一个管理员表(id,admin_name,admin_pwd,admin_email,is_del)
create table admin(
id int(11) unsigned not null auto_increment comment '管理员id',
admin_name varchar(30) not null comment '管理员名称',
admin_pwd char(32) not null comment '密码',
admin_email varchar(100) default null comment '邮箱',
is_del tinyint(1) not null default '0' comment '是否删除 1为删除 0正常',
primary key(id),
key admin_name(admin_name))
engine=Innodb auto_increment=2 default charset=utf8 comment '管理员表';

//给admin表添加一个管理员手机号字段
alter TABLE admin add admin_tel char(11) default null comment '管理员手机号';

//给admin表添加一个管理员性别字段 指定位置在admin_name后
alter TABLE admin add admin_sex tinyint default '0' comment '管理员性别 0为男 1为女' after admin_name;

//把admin表的admin_sex字段修改为sex
alter TABLE admin change admin_sex sex tinyint(1) default '0' comment '管理员性别 0为男 1为女';

//修改表名 把表名admin改为admin_user
alter table admin rename admin_user;

//group by
//查询每个班人数
select count(*),class_name from student GROUP BY class_name;
//查询每个班语文成绩平均分
select avg(chinese),class_name from student GROUP BY class_name;
//查询语文成绩平均分大于90的班级
select avg(chinese)as c,class_name from student GROUP BY class_name HAVING c>=90;
//查询英语成绩平均分大于90的班级 倒序
select avg(English)as e,class_name from student GROUP BY class_name HAVING e>90 ORDER BY e desc;
//查询英语成绩平均分最低的班级
select avg(English)as e,class_name from student GROUP BY class_name ORDER BY e asc LIMIT 1;
//查询每个班英语成绩最低分
select class_name,min(english) from student GROUP BY class_name;
//查询每个班英语成绩最高分
select class_name,max(english) from student GROUP BY class_name;
//查询英语成绩平均分大于90班级的个数
select count(1) from (select class_name,avg(english)as e from student GROUP BY class_name HAVING e>90)as num;