（1）等值连接 案例： 查询女神名和对应男生名 SELECT b.name,bs.boyName FROM beauty b, boys bs WHERE b.boyfriend_id = bs.id;
查询员工名和对应的部门名 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id;
查询有奖金的员工名，部门名 SELECT e.`last_name`, d.`department_id`, e.`commission_pct` FROM employees e,departments d WHERE e.`department_id`=d.`department_id` AND commission_pct IS NOT NULL;
查询每个工种的工种名和员工的个数，并且按员工个数降序 SELECT j.job_title, COUNT(*) FROM employees e, jobs j WHERE e.`job_id`=j.`job_id` GROUP BY j.`job_title` ORDER BY COUNT(*) DESC;
（2）非等值连接 案例： 查询员工的工资和工资级别 SELECT e.`salary`, j.`grade_level` FROM employees e, job_grades j WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` AND j.`grade_level`='E';
（3）自连接 查询员工名和上级的名称 SELECT e1.last_name, e2.`last_name` FROM employees e1, employees e2 WHERE e1.`manager_id`=e2.`employee_id`;
三表连接：查询员工名，部门名和所在的城市 SELECT e.last_name, d.`department_name`, l.`city` FROM employees e, departments d, locations l WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;
sql99语法：Inner
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
查询员工名，部门名 SELECT e.last_name, d.`department_name` FROM employees e INNERJOIN departments d ON e.`department_id`=d.`department_id`;
查询名字中只能包含e的员工名和工种名 SELECT e.last_name, j.`job_title` FROM employees e INNERJOIN jobs j ON e.`job_id`=j.`job_id` WHERE e.`last_name` LIKE'%e%';
查询部门个数>3的城市名和部门个数 SELECT l.`city`, COUNT(*) FROM locations l INNERJOIN departments d ON l.`location_id` = d.`location_id` GROUPBY l.`city` HAVINGCOUNT(*)>3;
查询工资级别的个数>2的个数，并按工资级别降序 SELECT j.`grade_level`, COUNT(*) 个数 FROM job_grades j INNERJOIN employees e ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` GROUPBY j.`grade_level` HAVING 个数>2 ORDERBY 个数 DESC;
查询没有男朋友的女神名 SELECT*FROM beauty b LEFTJOIN boys bo ON b.`boyfriend_id`=bo.`id` WHERE bo.`id` ISNULL;
查询哪个部门没有员工 SELECT d.`department_id`, d.`department_name` FROM departments d LEFTJOIN employees e ON d.`department_id`=e.`department_id` WHERE e.`employee_id` ISNULL GROUPBY d.`department_id`;
（2）右连接 语法：Right 【outer】
1 2 3 4 5 6 7 8 9 10
#查询没有男朋友的女神名 SELECT*FROM boys bo RIGHTJOIN beauty b ON b.`boyfriend_id`=bo.`id` WHERE bo.`id` ISNULL;
查询哪个部门没有员工 SELECT d.`department_id`, d.`department_name` FROM employees e RIGHTJOIN departments d ON e.`department_id`=d.`department_id` WHERE e.`employee_id` ISNULL GROUPBY d.`department_id`;
（3）全外（mysql不支持） 语法：Full 【outer】
交叉连接
语法：Cross
1 2 3
女神男神表交叉连接 SELECT b.*, bo.*FROM beauty b CROSSJOIN boys bo;
谁的工资比 Abel 高？ SELECT last_name, salary FROM employees WHERE salary>( SELECT salary FROM employees WHERE last_name='Abel' );
返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资 SELECT last_name,job_id,salary FROM employees WHERE job_id =( SELECT job_id FROM employees WHERE employee_id=141 ) AND salary>( SELECT salary FROM employees WHERE employee_id=141 );
案例3：返回公司工资最少的员工的last_name,job_id和salary SELECT last_name, job_id, salary FROM employees WHERE salary = ( SELECTMIN(salary) FROM employees );
In（常用）： 作用：可以是子查询中的任一一个 返回location_id是1400或1700的部们中的所有员工姓名 SELECT last_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id IN(1400, 1700) );
Any（不常用）： 作用：任一一个
返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号，姓名，job_id,以及salasy SELECT employee_id, last_name, job_id,salary FROM employees WHERE salary<ANY( SELECTDISTINCT salary FROM employees WHERE job_id='IT_PROG' ) AND department_id<>'IT_PROG';
All（不常用）： 作用：所有的
返回其他部门中比job_id为‘IT_PROG’部门所有工资低的员工的员工号，姓名，job_id,以及salasy SELECT employee_id, last_name, job_id,salary FROM employees WHERE salary<ALL( SELECTDISTINCT salary FROM employees WHERE job_id='IT_PROG' ) AND department_id<>'IT_PROG';
分页查询（常用）
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求。
语法：
select 参数列表 from 表 left join 表名 on 连接条件 where 筛选条件 group by 分组字段 having 分组后筛选 order by 排序字段 limit 【offset,】 size；
转账成功提交：执行后可以看到数据库数据已经改变了 set autocommit=0; start transaction; update account set money = money-500 where acc_name=’欧阳荣’; update account set money = money+500 where acc_name=’罗总’; commit;
转账失败回滚：执行后会发现数据并没有改变 set autocommit=0; start transaction; update account set money = money-500 where acc_name=’欧阳荣’; update account set money = money+500 where acc_name=’罗总’; rollback;
set autocommit=0;#设置不自动提交 start transaction;#开启事物 delete form account where id=1; savepoint a;#保存点 delete from account where id=2; rollback to a;#回滚到保存点 这上面的sql意思是只回滚到保存点，其实就是删除了id=1的数据，id=2的数据并没有被删除。
1.查询邮箱中包含a字符的员工名，部门名，工种信息 – 创建视图 create view view_test1 AS SELECT CONCAT(e.first_name, e.last_name) 员工名, dept.department_name 部门名, j.job_title 工种信息 FROM employees e LEFT JOIN departments dept ON dept.department_id = e.department_id LEFT JOIN jobs j ON j.job_id = e.job_id where email LIKE ‘%a%’ – 查询视图 select * from view_test1
2.查询各部门的平均工资级别 create view view_test2 as SELECT dept.department_name dept_name, AVG(e.salary) ag FROM departments dept LEFT JOIN employees e ON dept.department_id = e.department_id GROUP BY dept.department_name
SELECT vt.dept_name, jb.grade_level FROM view_test2 vt LEFT JOIN job_grades jb ON vt.ag BETWEEN jb.lowest_sal AND jb.highest_sal
3.查询平均工资最低的部门 create view view_test3 as SELECT dept.department_name dept_name, AVG(e.salary) ag FROM departments dept LEFT JOIN employees e ON dept.department_id = e.department_id GROUP BY dept.department_name having ag is not null order by ag asc limit 0, 1
4.查询平均工资最低的部门名和工资 create view view_test4 as SELECT dept.department_name dept_name, AVG(e.salary) ag FROM departments dept LEFT JOIN employees e ON dept.department_id = e.department_id GROUP BY dept.department_name having ag is not null order by ag asc limit 0, 1
create procedure myp1() begin insert into admin(name, money) values(‘z1’, 11), (‘z2’, 22), (‘z3’, 33), (‘z4’, 44), (‘z5’, 55); end;
#调用存储过程 CALL myp1();
带in模式参数的存储过程： 根据女神名获取男神信息
创建存储过程
create procedure myp1(in beauty_name varchar(25)) begin select bs.* from beauty b right join boys bs on b.boyfriend_id = bs.id where b.name = beauty_name; end;
调用
call myp1(‘赵敏’)
根据用户名和密码判断是否能登录成功 – 创建存储过程 create procedure myp2(in username varchar(25), in password varchar(25)) begin declare result int default ‘0’; select count(*) INTO result from admin a where a.username = username and a.password = password; select if(result > 0, ‘成功’, ‘失败’); end; – 调用 call myp2(‘john’, ‘8888’)
（2）带out模式的存储过程：
根据女神名，返回对应的男神名 #创建 create procedure myp3(in beauty_name varchar(25), out boy_name varchar(25)) begin select bs.boyName into boy_name from beauty b right join boys bs on b.boyfriend_id = bs.id where b.name = beauty_name; end; – 调用 set @boy_name=’’; call myp3(‘赵敏’, @boy_name); select @boy_name;
根据女神名，返回对应的男神名和男神魅力值 创建 create procedure myp4(in beauty_name varchar(25), out boy_name varchar(25), out user_cp int) begin select bs.boyName, bs.userCP into boy_name,user_cp from beauty b right join boys bs on b.boyfriend_id = bs.id where b.name = beauty_name; end;
– 调用 set @boy_name=’’; set @user_cp=0; call myp4(‘赵敏’, @boy_name, @user_cp); select @boy_name, @user_cp;
（3）带inout模式参数的存储过程
传入a和b两个值，最终a和b都翻倍并返回 #创建 create procedure myp5(inout x int, inout y int) begin set x = x2; set y = y2; end; #调用 set @x = 10; set @y = 20; call myp5(@x, @y); select @x, @y;
创建语法： create function 函数名(参数列表) returns 返回类型 begin 函数体 end
注意： 1.参数列表包含两部分：参数名 参数类型 2.函数体：肯定会有return语句，如果没有会报错。 如果return语句没有放在函数体的最后也不报错，但不建议。 3.函数体重仅有一句话，则可以省略begin end 4.使用delimiter语句设置结束标记
调用语法： select 函数名（参数列表）
查看语法： show create function 函数名
删除语法： dorp function 函数名
9.2.2.函数实战
（1）无参有返回
返回公司的员工个数： #创建 create function myf1() returns int begin declare count int default 0; select COUNT(*) into count from employees; return count; end; #调用 select myf1();
（2）有参返回
根据员工名返回工资： #创建 create function myf2(username varchar(25)) returns double begin declare money double default 0; select salary into money from employees where last_name = username; return money; end; #调用 select myf2(‘Kochhar’);
根据部门名，发挥该部门的平均工资 #创建 create function myf3(dept_name varchar(25)) returns double begin declare ave_salary double default 0; select avg(e.salary) into ave_salary from departments dept left join employees e on dept.department_id = e.department_id where dept.department_name = dept_name group by dept.department_id; return ave_salary; end; #调用 select myf3(‘Adm’);
实现传入两个float，返回两者之和：
#创建 create function myf1(x float, y float) returns float begin declare sum float default 0; set sum = x + y; return sum; end; #调用 select myf1(1, 5.1);
create procedure show_grade(in grade int) begin declare result varchar(2); CASE WHEN grade>=90 and grade<=100 THEN set result = ‘A’; WHEN grade>=80 THEN set result = ‘B’; WHEN grade>=60 THEN set result = ‘C’; ELSE set result = ‘D’; END CASE; select result; end;
CALL show_grade(99);
#根据传递的数据库类型，显示对应的数据库名 create procedure show_database_type(in val int) begin declare result varchar(20); CASE val WHEN 1 THEN set result = ‘mysql’; WHEN 2 THEN set result = ‘oracle’; WHEN 3 THEN set result = ‘sql server’; ELSE set result = ‘不认识的类型，滚啊。。。’; END CASE; select result; end;
call show_database_type(1);
（3）if结构 功能：实现多重分类 语法： if 条件1 then 语法1; esleif 条件2 then 语法2; … [else 语句n;] end if;
create function show_grade(grade int) returns char(1) begin declare result char(1); if grade>=90 and grade<=100 THEN set result = ‘A’; elseif grade>=80 THEN set result = ‘B’; elseif grade>=60 THEN set result = ‘C’; else set result = ‘D’; end if; return result; end;
select show_grade(55);
#批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止 create procedure pro_while_insert2(in count int) begin declare i int default 1; a:while i<=count do insert into admin(username, password) values(CONCAT(‘user’, i), CONCAT(‘psw’, i)); if i>=20 then leave a; end if; set i=i+1; end while a; end;
（3）repeat [标签:] repeat 循环体 until 结束循环的条件 end repeat [标签]
循环实战操作：
#批量插入，根据次数插入到amdin表中多条数据 create procedure pro_while_insert(in count int) begin declare i int default 1; while i<=count do insert into admin(username, password) values(CONCAT(‘user’, i), CONCAT(‘psw’, i)); set i=i+1; end while; end;
call pro_while_insert(10);
while VS repeat VS loop while：先判断后执行 repeat：先执行后判断 loop：没有条件的死循环
语法： create trigger triggerName after/before insert/update/delete on 表名 for each row #这句话是固定的 begin #需要执行的sql语句 end 注意1:after/before: 只能选一个 ,afte表示后置触发, before表示前置触发 注意2:insert/update/delete:只能选一个
创建一个视图： create trigger tag1 after insert on order_table for each row begin update goods set num=num-3 where id=1; end;
我们如何在触发器引用行的值，也就是说我们要得到我们新插入的订单记录中的gid或much的值。 对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。 所以现在我们可以这样来改我们的触发器: create trigger tg1 after insert on order_table for each row BEGIN update goods set num = num-new.much where id=new.gid; END;
当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？ 对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示旧表中的值，old.列名可以引用原(旧)表中的值。 create trigger tg2 after delete on order_table for each ROW BEGIN update goods set num = num+old.much where id=old.gid; END;
