MySQL基本知识及练习(5)
1.求一个班级数学平均分。
(1). select sum(math) / count(math) as 数学平均分
from student;
(2). select avg(math) as 数学平均分
from student;
(3).select avg(name) as 小明平均分
from student;//0
2.求一个班级总分平均分。
(1).select (sum(chinese)+sum(math)+sum(english)) / count(*)
from student;
(2).select avg(chinese+math+english)
from student;
3.求班级语文最高分和最低分。
select max(name),min(name)
from student;
drop table if exists teacher;
create table teacher(
id int,
name varchar(20),
birthday date
);
insert into teacher(id,name,birthday) values(1,'jack','2011-1-1');
insert into teacher(id,name,birthday) values(2,'marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');
select max(birthday),min(birthday)
from teacher;
4.对订单表中商品归类后，显示每一类商品的总价
select product as 类别名,sum(price) as 商品类别总价
from orders
group by product;
5.查询购买了几类商品。而且每类总价大于100的商品
select product as 类别名,sum(price) as 商品类别总价
from orders
group by product
having sum(price) > 100;
6.where v.s. having差别：
where主要用于行过滤器
having主要用于类别过滤器，通常有having就一定出现group by,但有group by的地方。不一定出现having。hving能够说是针对结果集在进行查询的。
drop table if exists teacher;
create table teacher(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday date
);
insert into teacher(name,birthday) values(NULL,'2011-1-1');
insert into teacher(name,birthday) values('marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');
select max(birthday),min(birthday)
from teacher;
7.删除主键，主键在表中仅仅有一个，要么是一列，要么是多列
alter table teacher drop primary key;
8.一对一关系(方案一)：
drop table if exists card;
drop table if exists person;
create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');
create table card(
id int primary key auto_increment,
location varchar(20) not null,
pid int,
constraint pid_FK foreign key(pid) references person(id)
);
insert into card(location,pid) values('BJ',1);
insert into card(location,pid) values('GZ',2);
insert into card(location,pid) values('CS',NULL);
insert into card(location,pid) values('NJ',3);//出错
//删除person表的某记录
delete from person where name = 'jack';
9.一对一关系(方案二)：
drop table if exists card;
drop table if exists person;
create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');
create table card(
id int primary key auto_increment,
location varchar(20) not null,
constraint id_FK foreign key(id) references person(id)
);
insert into card(location) values('BJ');
insert into card(location) values('GZ');
insert into card(location) values('CS');//出错
insert into card(location) values(NULL);
10.一对多/多对一关系：
drop table if exists employee;
drop table if exists department;
create table department(
id int primary key auto_increment,
name varchar(20) not null
);
insert into department(name) values('软件部');
insert into department(name) values('销售部');
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
did int,
constraint did_FK foreign key(did) references department(id)
);
insert into employee(name,did) values('jack',1);
insert into employee(name,did) values('marry',1);
11.问题？查询"软件部"的全部员工（组合式）
select d.name as 部门名,e.name as 员工名
from department as d,employee as e
where d.name = '软件部';
思考：还有没有其他方法？
分解：
(1)select id from department where name='软件部';
(2)select name from employee where did = 1;
(总)嵌入式SQL
select name as 员工
from employee
where did = (
select id
from department
where name='软件部'
);
12.多对多关系：
drop table if exists middle;
drop table if exists student;
drop table if exists teacher;
create table if not exists student(
id int primary key auto_increment,
name varchar(20) not null
);
insert into student(name) values('jack');
insert into student(name) values('marry');
create table if not exists teacher(
id int primary key auto_increment,
name varchar(20) not null
);
insert into teacher(name) values('赵');
insert into teacher(name) values('蔡');
create table if not exists middle(
sid int,
tid int,
constraint sid_FK foreign key(sid) references student(id),
constraint tid_FK foreign key(tid) references teacher(id),
primary key(sid,tid)
);
insert into middle(sid,tid) values(1,1);
insert into middle(sid,tid) values(1,2);
insert into middle(sid,tid) values(2,1);
insert into middle(sid,tid) values(2,2);
13.问题？查询"赵"所教过的全部学员
select t.name as 老师, s.name as 学员
from teacher as t,student as s,middle as m
where t.name = '赵'and m.sid=s.id and m.tid=t.id;
14.模式：
select 列出须要显示的字段
from 列出所涉及到的全部表。建议写别名
where 业务条件 and 表关联条件
15.使用MySQL特有函数：
到年底还有几少天？
select datediff('2011-12-31',now());
16.截取字符串
select substring('mysql',1,2); //从1開始
17.保留小数点后2位(四舍五入)
select format(3.1415926535657989,3);
18.向下取整(截取)
select floor(3.14);
select floor(-3.14);
select floor(3.54);
select floor(-3.54);
19.取随机值
select format(rand(),2);
20.取1-6之间的随机整数值
select floor(rand()*6) + 1;
21.MySQL扩展知识：
查MySQL文档，利用MySQL的函数：随机产生'a'-'z'之间的随机字符。
随机产生'a'-'z'之间的随机字符
(1)查询'a'-'z'相应的Unicode值
select ascii('a');//97
select ascii('z');//122
(2)产生97-122之间的随机整数
select floor(rand()*26)+97;
(3)产生97-122相应的字符
select char(floor(rand()*26)+97);
22.查MySQL文档。利用MySQL的函数：对password'123456'进行MD5加密。
select md5('123456');
drop table user;
create table user(
id int primary key auto_increment,
name varchar(20),
gender varchar(6),
salary float
);
insert into user(name,gender,salary) values('jack','male',4000);
insert into user(name,gender,salary) values('marry','female',5000);
insert into user(name,gender,salary) values('jim','male',6000);
insert into user(name,gender,salary) values('tom','male',7000);
insert into user(name,gender,salary) values('soso','female',NULL);
insert into user(name,gender,salary) values('haha','female',3500);
insert into user(name,gender,salary) values('hehe','female',4500);
select * from user;
23.MySQL特有流程控制函数：
1) if(value,第一值,第二值);
value为真。取第一值，否则取第二值
将5000元（含）以上的员工标识为"高薪"，否则标识为"起薪"
类似于Java中的三目运算符
select if(salary>=5000,'高薪','起薪')
from user;
2) ifnull(value1,value2)
value1为NULL，用value2替代
将薪水为NULL的员工标识为"无薪"
select name as 员工,ifnull(salary,'无薪') as 薪水情况
from user;
3) case when [value] then [result1] else [result2] end;
当value表达式的值为true时，取result1的值，否则取result2的值(if...else...)
将5000元（含）以上的员工标识为"高薪"，否则标识为"起薪"
select
case when salary>=5000 then '高薪'
else '起薪' end
from user;
4) case [express] when [value1] then [result1] when [value2] then [result2] else [result3] end;
当express满足value1时，取result1的值。满足value2时，取result2的值，否则取result3的值(switch...case..)
将7000元的员工标识为"高薪"，6000元的员工标识为"中薪",5000元则标识为"起薪"，否则标识为"低薪"
select
case salary
when 7000 then '高薪'
when 6000 then '中薪'
when 5000 then '起薪'
else '低薪' end
from user;
25.查询同样性别的员工总人数>2的工资总和。并按工资总和降序排列
select count(*) as 员人数,gender as 性别,sum(salary) as 工资和
from user
group by gender
having count(*)>2
order by sum(salary) desc;
26.将性别为男的员工工资-1000，性别为女的员工工资+1000,在一条SQL上完毕
select if(gender='female',salary+1000,salary-1000) as 工资 from user;
27.经常使用函数举例
select now();
select year(now());
select month(now());
select day(now());
select floor(datediff(now(),‘1999-01-01’)/365);//间隔年
select format(rand(),2);
select floor(rand()*5)+1;[1-5]随机值
select length(trim(' jack '));
select strcmp('a','w');
总结：
1 .关系的完整性
(1)实体(行)完整性：每条记录有一个唯一标识符，通经常使用无不论什么业务含义的字段表示(2)參照完整性：一张（A）表的某个字段必须引用还有一张（B）表的某个字段值，并且B表 的字段必须先存在。
(3)域(列)完整性：域即单元数据，域中的数值必须符合一定的规则，比如字段的值域、字 段的类型等的约束。
2 键的概念
(1)主键：仅仅有唯一字段
(2)组合主键：由多个字段组合起来，形成唯一字段
(3)外键：针对多张表之间的关联
3 主键的特点
(1)主键不能反复
(2)主键不能为NULL
(3)auto_increment是MySQL特有的，默认从1開始。该ID值与表同生亡
(4)多人项目中。通常使用UUID来生成唯一的主键值，便于多个合并数据时依旧保持实体完整性
4 唯一约束的特点
(1)非NULL值不能反复
(2)能够插入多个NULL值
(3)'NULL'空串和NULL是不同的概念
5 非空约束特点
(1)不能插入NULL值
(2)主键约束=非NULL约束+唯一约束
6 外健特点
(1)外键值必须来源于所引用别一个表主键值。或NULL
7 关联关系
(1)一对一(外健根业务有关)
(2)一对多或多对一(外键放置在多方)
(3)多对多(外健放置在关联表中，即将一个多对多拆分成二个一对多关系)
8.经常使用函数:
（1）.日期函数：
2.数学函数：
3.字符串函数
