mysql 基础

连接查询

2020-4-20

显示所有员工姓名，编号和部门名称

select e.last_name, d.department_id, d.department_name from departments d, employees e where e.department_id = d.department_id; 

查询90号员工的job_id和90号部门的location_id

select job_id, location_id from employees e, departments d where e.department_id = d.department_id and e.department_id = 90 

查询所有有奖金的员工信息

select e.last_name, d.department_name, l.location_id, l.city from employees e, departments d, locations l where e.department_id = d.department_id and d.location_id = l.location_id and e.commission_pct is not null 

查询每个工种，部门名和最低工资

select department_name, job_title, min(salary) from departments d, jobs j, employees e where d.department_id = e.department_id and e.job_id = j.job_id group by department_name, job_title 

查询每个国家下的部门个数大于2的国家编号

select count(*) 部门个数, country_id from departments d, locations l where d.location_id = l.location_id group by country_id having 部门个数>2 

查询指定员工的姓名和员工号以及对应的管理者的姓名和员工号

select e.last_name employees, e.employee_id "Emp#", m.last_name manager, m.employee_id "Mgr#" from employees e, employees m where e.manager_id = m.employee_id 

经典案例

2020-4-21

1.查询工资最低的员工信息:last_name, salary

    select last_name, salary     from employees e     where e.salary = (         select min(salary)         from employees     ) 

2.查询平均工资最低的部门信息

select e.department_id from employees e group by e.employee_id order by avg(salary) limit 1 

3.查询平均工资最低的部门信息和该部门的平均工资

select d.*, ag from departments d join (     select avg(salary) ag, department_id     from employees     group by department_id     order by avg(salary)     limit 1 ) ag_dep on ag_dep.department_id = d.department_id 

4.查询平均工资最高的job信息

select avg(salary), job_id from employees group by employee_id order by avg(salary) desc limit 1 

5.查询平均工资高于公司平均工资的部门信息

select avg(salary), department_id from employees group by department_id having avg(salary) > (     select avg(salary)     from employees ) 

6.查询出公司中所有manager的详细信息

select      *  from     employees where employee_id = any(     select distinct manager_id     from employees     where manager_id is not null ) 

7.各个部门中 最高工资中最低的那个部门的 最低工资是多少

select min(salary), department_id from employees where department_id = (     select department_id     from employees     group by department_id     order by max(salary)     limit 1 ) 

8.查询平均工资最高的部门的manager的详细信息：last_name, department_id, email, salary

• 方法1

    select        last_name, department_id, email, salary   from        employees   where employee_id = any(       select manager_id       from employees       where department_id = (           select department_id           from employees           group by employee_id           order by avg(salary) desc           limit 1       )   ) 

• 方法2

    select        last_name, e.department_id, email, salary   from        employees e   inner join       departments d    on d.manager_id = e.employee_id   where d.department_id = (       select department_id       from employees       group by employee_id       order by avg(salary) desc       limit 1   ) 

事务

隔离等级

1. READ UNCOMMITED : 出现脏读，不可重复，幻读
2. READ COMMITED ： 避免脏读， 出现不可重复，幻读
3. REPETABLE READ ： 避免脏读和不可重复，出现幻读
4. SERIAUZABLE ： 避免脏读，不可重复，幻读，但效率低

常用命令

查看隔离级别： select @@tx_ioslation

修改隔离级别： set session|global transaction ioslation level 隔离级别

脏读

比如说我们同时进行两个事务并对同一个表进行处理， 当我们修改表中的内容但未提交事务时，在另外一个事务中查看表内容时，会发现数据被修改了，这就是脏读

不可重复读

类似于脏读，但是是在另一个事务提交后才会出现，而脏读则是只要在另外一个事务中修改，当前事务的表数据就会发生改变

幻读

当我们在当前表对数据的所有行进行修改时，此时另一事务正在添加一行数据，这时，我们的当前表处理就会多出一行，这就是幻读

视图

视图创建案例

1.创建视图emp_v1, 要求查询电话号码以’011’开头的员工姓名，工资，还有邮箱

create or replace view emp_v2 as select last_name, salary, email from employees where phone_number like '011%' 

2.创建视图emp_v2, 要求查询部门的最高工资高于12000的部门信息

create or replace view emp_v3 as select max(salary), department_id from employees group by department_id having max(salary)>12000  select d.* from departments d inner join emp_v3 e on d.department_id = e.department_id 

视图修改操作

1. 修改

    updata 视图名 set 

2. 删除

    delect from 视图名 

3. 添加

    insert into 视图名 value() 

六种不可更新视图

1. 包含以下关键字的sql语句, distinct, group by, having, union, union all

3. 常量视图

    create or replace view emp_v     as      select "张三" as name 

4. select中包含子查询

    create or replace view emp_v     as     select (select last_name from employees) 

5. join一个不能更新的视图
6. from一个不能更新的视图
7. where字句的子查询用了from字句中的表

    create or replace view emp_v     as     select e.*     from employees     where employees_id=(         select manager_id         from employees         where manager_id is not null     ) 

truncate 和 delect在事务中的区别

truncate不能回滚

delect可以回滚

视图测试题

1.创建表Book表， 字段如下

bid 整形， 要求主键
bname 字符型， 要求设置唯一键， 并非空
price 浮点型， 要求有默认值 10
btypeId 类型编号， 要求引用bookType表的id字段

create table Book( bid int primary key, bname varchar(20) unique not null, price float default 10, btypeld int, foreign key(btypeld) references bookType(id)  )    

已知bookType表(不用创建)， 字段如下：
id
name
2, 开启事务
向表中插入1行数据，并结束

set autocommit = 0 inset into book(bid, bname, price, btypeId) values(...) commit|rockback; 

3.创建视图， 实现查询价格大于100的书名和类型名

create or replace view myv1 as select bname, name from book b inner join bookType bt on bt.id = b.btypeId where price>100 

4.修改视图，实现查询价格在90-120之间的书名和价格

create or replace view myv1 as select bname, price from book where price between 90 and 120 

5.删除刚才建的视图

drop view myv1 

存储过程案例题

一. 创建存储过程实现传入用户名和密码，插入到admin表中

delimiter $ create procedure my_pro1(in username varchar(20), in password int) begin     insert into admin(admin.username, admin.password)     values(username, password); end $  set names gbk$ call my_pro1("..", "..") 

二.创建存储过程或函数实现传入女神编号，返回女神名称和女神电话

delimiter $ create procedure my_pro2(in id int, out name varchar(20), out phone varchar(20)) begin     select b.name, b.phone into name, phone     from beauty b     where b.id = id; end $ call my_pro2(1, @name, @phone) 

三.创建存储过程或函数实现传入两个女神的生日，返回大小

delimiter $ create procedure my_pro3(in birth1 datetime, in birth2 datetime, out result int) begin     select datediff(birth1, birth2) into result; end $ 

四.创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回

delimiter $ create procedure my_pro4(in mydate datetime, out strDate varchar(20)) begin     select date_format(mydate, "%y年%m月%d日") into strDate; end $ 

五. 创建存储过程或函数实现传入女神名称，返回：女神AND男神 格式的字符串

delimiter $ create procedure my_pro5(in name varchar(20), out cpname varchar(50)) begin     select concat(b.name,"AND", ifnull(bo.boyname, "null")) into cpname     from boys bo     right join     beauty b     on bo.id = b.boyfriend_id     where b.name = name; end $ 

六.创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录

delimiter $ create procedure my_pro6(in size int, in startindex int) begin     select * from beauty limit startindex, size; end $ 

函数案例

1.创建一个函数，实现传递两个float, 返回两者之和

delimiter $ create function my_fun1(num1 float, num2 float) returns float begin     declare sum float default 0;     set sum=num1+num2;     return sum; end $  select my_fun1(1,3)