件部';
(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的函数：对密码'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 |