mysql数据库查询语句面试

Mysql

• Mysql注释
• 数据库类型
• 四大类

• １．DDL(Data Definition Language)数据定义语言
• ２．DML(Data Manipulation Language)数据操作语言
• ３．DQL(Data Query Language)数据查询语言
• ４．DCL(Data Control Language)数据控制语言

• 约束
• 数据库的设计－ 多表之间关系
• 数据库的设计－ 范式
• 数据库备份还原
• 多表查询
• 事务
• 事务的四大特性ACID:
• 事务的隔离级别：
• 练习：

Mysql注释

• 单行注释：
  show databases; 　–　(空格) 查询所有数据库
  show databases;　 #查询所有数据库
• 多行注释：
  show databases; /* 查询所有数据库*/

数据库类型

• int :整数类型 //age int
• double :小数类型 //score double(5,2)
• date :日期，只包含年月日,yyyy-MM-dd
• datetime :日期,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
• timestamp :时间错类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss()//若不给这个字段赋值，默认使用当前系统时间．
• varchar :字符串, name varchar(20);最大长度为20

四大类

１．DDL(Data Definition Language)数据定义语言

用来定义数据库对象：数据库，表，列等．关键字：create,drop,alter等．

• 操作数据库(CRUD)
  C:create创建 create database if not exists db1;(如果不存在则创建)
  R:retrieve查询 showdatabases;/show tables;/show create database fly;(查看字符集)
  U:update修改 alter database db1 character set gbk;(修改字符集)
  D:delete删除 drop database db1;(删除数据库)/drop database if exists db1;(若存在则删除)
  use database db1;(使用数据库)
• 操作表：
  C:create创建 create table student(列名 数据类型，列名 数据类型)/create table s1 like s2(复制表) R:retrieve查询 show tables; /desc student;(查询表结构)
  U:update修改

1.修改表名：

alter table 表名 rename to 新的表名;

2.修改表的字段:

alter table 表名 character set utf8(修改字符集)

3.添加一列 ：

alter table 表名 add 列名  数据类型;

4.修改列名称　类型 ：

alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;

5.删除列：
alter table 表名 drop 列名;
D:delete删除 drop table if exists student;/drop table student;创建表：

create table Student(
    id int,
    name varchar(32),
    age int,
    score double(4,1),
    birthday date,
    insert_time timestamp);

２．DML(Data Manipulation Language)数据操作语言

用来对数据库中表的数据进行增删改.关键字：insert,delete,update等；

• 1.添加数据

insert into 表名(列名1,列名2, ... ,列名n) values(值1,值2,... 值n);

注意:
1.列名和值一一对应
2.表名后,不定义列名,则默认给所有值赋值
3.除了数字类型,其他类型则需要引号(单双都可以)

• 2.删除数据

delete from 表名 [where id = ?];

注意:如果不加条件,则删除表中所有元素.
truncate table 表名; //删除表,然后创建一个一模一样的空表

• 3.修改数据

update 表名 set 列名1 = 值1,列名2=值2,...... [where id=?]

如果不加条件,则将表中所有数据修改

３．DQL(Data Query Language)数据查询语言

用来查询数据库表中的记录(数据).关键字：select,where等

1.语法:

• select 字段列表
• from 表名列表
• where 条件列表
• group by 分组字段
• having 分组之后的条件
• order by 排序
• limit 分页限定

2.基础查询

• 1.多个字段的查询
• 2.去除重复
• 3.计算列
• 4.起别名
  例:

insert into student3(id,name,age,sex,address,math,english)
values(1,'张三',55,'nan','hangzhou',66,77),
(2,'李四',22,'nan','shenzhen',88,86),
(3,'王五',34,'nan','xianggang',55,32),
(4,'刘柳',22,'nv','hunan',88,88),
(5,'德玛',65,'nan','xianggang',99,99),
(6,'卡密尔',22,'nv','sea',88,77),
(7,'克列',11,'nan', 'cloud',21,27);

1.多字段查询
查询姓名年龄:

select
　name, -- 姓名
　age -- 年龄
　from student3;   -- 学生表

2.去除重复结果集

select distinct address from student3;

3.计算math 和english　之和

select name,math,english,math+english from student3;

如果有null参与的运算，结果都为null

select name,math,english,math + ifnull(english,0) from student3;

4.起别名

select name,math,english,math + ifnull(english,0) as(可省略) sum from student3;

3.条件查询

• 1.where字句后跟条件
• 2.运算符 <, > ,<=, >=, = ,<>

between...and
   in  ,  like  ,is  null  ,and ,&& ,or , ||  ,not , !

查询年龄大于20
select *from student3 where age>20;
查询年龄不等于20
select *from student3 where age != 20;
select *from student3 where age <>20;
查询年龄大于20小于30
select *from student3 where age>20 && age<30;
select *from student3 where age>20 and age<30;
select *from student3 where age between 20 and 30;
查询年龄22岁，19岁，25岁
select *from student3 where age=22 || age=19 || age=25;
select *from student3 where age=22 or age=19 or age=25;
select *from student3 where age in(22,19,25);

select *from student3 where english = null; //null不能使用=判断，应该用is
select *from student3 where english is (not) null; 
模糊查询：查询姓ma的
    like 占位符：( _：单个任意字符 )(%: 多个任意字符)
 select *from student3 where name like 'ma%';
查询第二个字为a
select *from student3 where name like '_a%';
查询姓名为三个字的
select *from student3 where name like '___';
查询姓名中包含a的
select *from student3 where name like '%a%';

4.排序查询

order by  排序字段 排序方式
select *from student3 order by math; //默认从小到大
ASC:升序(默认)
DESC:降序
select *from student3 order by math desc(asc);

按照数学成绩排名，若数学一样，则按照英语排名
 select *from student3 order by math asc, english asc;
如果有多个排序条件，当前边条件值一样时，才判断第二条件

5.聚合查询 (将一列数据作为一个整体，进行纵向的计算)

查询平均分等(纵向计算)
1.count 计算个数
select count(math) m_num from student3;
2.max 计算最大值
select max(math) m_max from student3;
3.min 计算最小值
select min(math) m_min from student3;
4.sum 计算和
select sum(math) m_sum from student3;
5.avg 计算平均数
select avg(math) m_avg from student3;
注意：聚合函数会排出非空数据(null)
解决：
1.选择不包含非空的列进行计算  (多用)
2.使用 IFNull
select count(ifnull(english,0)) m_num from student3;

6.分组查询

group by 分组字段
注意：分组后查询的字段：分组字段或者聚合字段
男，女分组看每组平均分
select sex,avg(math) from student3 group by sex;
select sex,avg(math),count(id) from student3 group by sex;
男，女分组看每组平均分，分数低于70,不参与分组
select sex,avg(math),count(id) from student3 where math>70 group by sex;
男，女分组看每组平均分，分数低于70,不参与分组,分组之后人数大于2人
select sex,avg(math),count(id) num from student3 where math>70 group by sex having num>2;

注意：where和having的区别？
１．where在分组之前进行限定，若不满足，则不参与分组,having在分组之后进行限定，若不满足，则不会被查询
２．where后不能跟聚合函数，having可以跟聚合函数

7.分页查询

语法：limit  开始的索引 , 每页查询的条数
每页显示三条记录
select *from student3 limit 0,3;  显示0-3
select *from student3 limit 3,3;  显示3-6
公式：开始的索引= (当前的页码-1) * 每页显示的条数;
limit操作是一个"方言"．只能在mysql用limit.每个数据库的分页查询字段不一样;

４．DCL(Data Control Language)数据控制语言

用来定义数据库的访问权限和安全级别，及创建用户．关键字：grant,revoke等．

管理用户：
１．查询用户

use mysql; --　切换数据库
select *from user;  --　查询

通配符：%可以在任意主机访问数据库
２．创建记录

create user '用户名'@'localhost' identfied by '密码';

３．删除用户

drop user '用户名'@'localhost';

４．修改用户密码

update user set password =password('新密码') where user = '用户名';
    set password for '用户名'@'localhost' = password('新密码');

注：若忘记了root用户的密码?

• １．net stop mysql 停止服务
• ２．启动无验证方式启动mysql 服务: mysql --skip-grant-tables;
• ３．关闭服务

授权：
１．查询权限

show grant for '用户名'@'主机名';

２．授予权限

grant 权限列表 on 数据库.表名 to '用户名'@'localhost';
 grant select,update,delete on test.account to 'fly'@'localhost';
 grant all on *.* to 'lisi'@'localhost';  //授予所有表的所有权限

３．撤回权限

revoke 权限列表 on 数据库.表名 from '用户名'@'localhost';

约束

对表中的数据进行限定，保证数据的正确性，有效性和完整性．
分类：

• 主键约束 primary key
  非空值且唯一！！一张表只能有一个字段为主键．
  主键就是表中记录的唯一标识

create table stu(
    id int primary key ,-- 添加主键约束
    name varchar(20)
);

删除主键：alter table stu drop primary key; 自动增长：Auto_increment //仅限于数值
配合int 类型的主键一起使用.
删除自动增长：alter table stu modify id int;

• 非空约束 not null
  某一列的值不能为null
  １．创建表添加：

create table stu(
　　id int primary key,
　　name varchar(20) not null);

２．修改属性:

alter table stu modify name varchar(20) not null;

• 唯一约束 unique
  某一列的值不能重复

insert into stu(id,iphone_number) values(1,123456),(2,123456);
ERROR 1062 (23000): Duplicate entry '123456' for key 'phone_number'
 insert into stu(id,iphone_number) values(1,123456),(2,1234561);　//可以
注意：唯一约束可以为null ，但只能有一条记录为null
//不能通过这样的方式操作
alter table stu modify phone_number varchar(20);
//正确方法
alter table stu drop index phone_number;
atter table stu modify phone_number varchar(20) unique; //若已有重复数据，则操作失败

• 外键约束 foreign key
  １．在创建表时可以添加外键

create table 表(
     ．．．
     外键列
     constraint 外键名称 foreign key 外键列名称　references  主表名称(主表列名称)
);

２．删除外键

alter table employee drop froeign key emp_dept_id;

３．创建表之后，添加外键

alter table employee add constraint emp_dept_id foreign key (dep_id) references department(id);

４．级联操作(谨慎使用)
在添加外键时设置级联更新，设置级联删除

alter table employee add constraint emp_dept_id foreign key (dep_id)
     references department(id)  on update cascade(更新)  on delete cascade(删除);

例：

创建emp表
create table emp(
id int primary key auto_increment,
name varchar(30),
age int,
dep_name varchar(30),  -- 部门名称
dep_location varchar(30));  -- 部门地址

添加数据：

insert into emp(name,age,dep_name,dep_location)
values('zhangsan',20,'yanfabu','guangzhou'),
('lisi',21,'yanfabu','guangzhou'),
('wangwu',20,'yanfabu','shenzhen'),
('laowang',20,'xiaoshoubu','shenzhen'),
('dawang',22,'xiaoshoubu','shenzhen'),
('xiaowang',18,'xiaoshoubu','shenzhen');

发现数据有冗余．
解决：表的拆分 员工表　　部门表
部门表:

create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20));

员工表;

create table employee(
id int primary key auto_increment,
age int,
name varchar(20),
dep_id int ,  -- 外键对应的主键 
constraint emp_dept_id foreign key (dep_id) references department(id)
);

添加部门：

insert into department(dep_name,dep_location)
values('yanfabu','guangzhou'),('xiaoshoubu','shenzhen');

添加员工:

insert into employee(name,age,dep_id)
values('zhangsan',20,1),
('lisi',21,1),
('wangwu',20,1),
('laowang',20,2),
('dawang',22,2),
('xiaowang',18,2);

数据库的设计－ 多表之间关系

一对一：人和身份证
一对多(多对一)：老师对学生（一个老师教多个学生）
多对多：学生和课程(一个学生可以选多门课程，一个课程可被多个学生选)

实现关系：
一对多(多对一)：在多的一方建立外键，指向一的一方的主键
多对多：建立中间表

一对一：在任意一方添加外键(使外键唯一 :unique)，对应另一方主键

数据库的设计－ 范式

设计数据库遵循的规范，范式越高，数据冗余越小
第一范式： 每一列都是不可分割的原则数据项
问题：1.可能存在严重的数据冗余
2.数据添加和删除存在问题

第二范式： 在第一范式的基础上，非码属性必须完全依赖主码(在1NF基础消除非主属性对主码的部分依赖)
１．函数依赖：通过a属性(属性组) 的值确定b ,则b依赖a 例：通过学号+课程名称 —>分数
２．完全函数依赖：A—>B　A是一个属性组，B属性的值需要通过A属性组的所有值 例：通过学号+课程名称 —>分数
３．部分函数依赖：A—>B　A是一个属性组，B属性的值需要通过A属性组的某些值 例：通过学号或者课程 —>姓名
４．传递函数依赖：A—>B—>C A属性确定B,Ｂ确定C，则A确定C. C传递函数依赖于A
５．码 ：在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称该属性(属性组)为该表的码
主属性：码属性组的所有属性
非主属性：除过码中的属性

第三范式： 在第二范式的基础上，任何非主属性不依赖　其他非主属性．(在2NF基础消除依赖传递)

数据库备份还原

１．命令行：
备份： mysqldump -uroot -p123 db1(需要备份的数据库) > 保存的路径
还原：１．登录数据库
２．创建数据库 create database db1;
３．使用数据库 use db1;
４．执行文件　source 文件目录
２．图形化工具：
右键：备份
右键：执行sql脚本 还原

多表查询

数据准备

create table dept(  -- 部门表
id int primary key auto_increment,
name varchar(20)
);
insert into dept(name) values('开发部'),('市场部'),('财务部');

create table emp(  -- 员工表
id int primary key auto_increment,
name varchar(20),
gender char(1), -- 性别
salary double, -- 薪水
join_date date, -- 入职日期
dept_id int, 
foreign key(dept_id) references dept(id) -- 外键
);
insert  into emp(name,gender,salary,join_date,dept_id)
values('孙悟空','1',7200,'2013-4-1',1),
('猪八戒','1',3600,'2010-3-22',3),
('唐僧','1',9000,'2019-5-9',2),
('白骨精','0',5000,'2020-8-8',2),
('蜘蛛精','0',4000,'2004-2-13',1),
('老沙','1',6000,'1990-2-12',2);

笛卡尔积：
A,B集合，取两个集合的并集．

多表查询的分类：

• １．内连接查询

• 1.隐式: where条件消除无用数据

select *from dept,emp where dept.id = emp.dept_id;
select
     emp.name, --员工表姓名
     emp.gender,  -- 员工表性别
     dept.name --　部门表名称
from
     dept,
     emp
where    
     dept.id = emp.dept_id;
     
+-----------+--------+-----------+
| name | gender | name |
+-----------+--------+-----------+
| 孙悟空 | 1 | 开发部 |
| 蜘蛛精 | 0 | 开发部 |
| 唐僧 | 1 | 市场部 |
| 白骨精 | 0 | 市场部 |
| 老沙 | 1 | 市场部 |
| 猪八戒 | 1 | 财务部 |
+-----------+--------+-----------+

• 2.显式: select 字段列表 from 表名1 innert join 表名2 on 条件

select *from emp inner join dept where emp.dept_id= dept.id;
select
     emp.name,
     emp.gender,
     dept.name
from
     emp
（inner） join
     dept
where
     emp.dept_id= dept.id;

内连接查询
要确定：从哪些表中查询，条件是什么，查询哪些字段．

• ２．外连接查询
  左外连接：查询的是左表(emp)的所有数据和(dept)交集部分

select 字段列表 from 表1 left (outer) join 表2 on 条件;
select e.*,d.name from emp e left join dept d on e.dept_id=d.id;
+----+-----------+--------+--------+------------+---------+-----------+
| id | name | gender | salary | join_date | dept_id | name |
+----+-----------+--------+--------+------------+---------+-----------+
| 1 | 孙悟空 | 1 | 7200 | 2013-04-01 | 1 | 开发部 |
| 5 | 蜘蛛精 | 0 | 4000 | 2004-02-13 | 1 | 开发部 |
| 3 | 唐僧 | 1 | 9000 | 2019-05-09 | 2 | 市场部 |
| 4 | 白骨精 | 0 | 5000 | 2020-08-08 | 2 | 市场部 |
| 6 | 老沙 | 1 | 6000 | 1990-02-12 | 2 | 市场部 |
| 2 | 猪八戒 | 1 | 3600 | 2010-03-22 | 3 | 财务部 |
| 7 | 小白龙 | 1 | 3000 | NULL | NULL | NULL |
+----+-----------+--------+--------+------------+---------+-----------+

右外连接：查询的是右表(dept)的所有数据和(emp)交集部分
select 字段列表 from 表1 right (outer) join 表2 on 条件;

select e.*,d.name from emp e right join dept d on e.dept_id = d.id;
+------+-----------+--------+--------+------------+---------+-----------+
| id | name | gender | salary | join_date | dept_id | name |
+------+-----------+--------+--------+------------+---------+-----------+
| 1 | 孙悟空 | 1 | 7200 | 2013-04-01 | 1 | 开发部 |
| 5 | 蜘蛛精 | 0 | 4000 | 2004-02-13 | 1 | 开发部 |
| 3 | 唐僧 | 1 | 9000 | 2019-05-09 | 2 | 市场部 |
| 4 | 白骨精 | 0 | 5000 | 2020-08-08 | 2 | 市场部 |
| 6 | 老沙 | 1 | 6000 | 1990-02-12 | 2 | 市场部 |
| 2 | 猪八戒 | 1 | 3600 | 2010-03-22 | 3 | 财务部 |
+------+-----------+--------+--------+------------+---------+-----------+

• ３．子查询
  查询中嵌套查询(称嵌套查询为子查询).
  查询工资最高的 员工信息.

select *from emp where salary =(select max(salary) from emp);

子查询的不同情况：
(１．子查询结果单行单列(子查询作为条件，使用运算符判断：< > <= >= =)
条件：查询工资小于平均工资的员工

select *from emp where salary<(select avg(salary) from emp);
+----+-----------+--------+--------+------------+---------+
| id | name | gender | salary | join_date | dept_id |
+----+-----------+--------+--------+------------+---------+
| 2 | 猪八戒 | 1 | 3600 | 2010-03-22 | 3 |
| 4 | 白骨精 | 0 | 5000 | 2020-08-08 | 2 |
| 5 | 蜘蛛精 | 0 | 4000 | 2004-02-13 | 1 |
| 7 | 小白龙 | 1 | 3000 | NULL | NULL |
+----+-----------+--------+--------+------------+---------+

(２．子查询结果多行单列(子查询作为条件，使用运算符判断)

查询＇开发部＇和＇财务部＇所有员工信息

select *from emp where dept_id in(select id from dept where name='开发部' or name='财务部');
+----+-----------+--------+--------+------------+---------+
| id | name | gender | salary | join_date | dept_id |
+----+-----------+--------+--------+------------+---------+
| 1 | 孙悟空 | 1 | 7200 | 2013-04-01 | 1 |
| 5 | 蜘蛛精 | 0 | 4000 | 2004-02-13 | 1 |
| 2 | 猪八戒 | 1 | 3600 | 2010-03-22 | 3 |
+----+-----------+--------+--------+------------+---------+

(３．子查询结果多行多列(子查询可以作为一张虚拟表来进行表查询)
查询员工入职日期是2011-11-11之后的员工信息和部门信息

1.select *from dept t1,(select *from emp where join_date >'2011-11-11') t2 where t1.id=t2.dept_id;
2.select *from emp t1,dept t2 where t1.dept_id=t2.id and t1.join_date >'2011-11-11'; 
+----+-----------+----+-----------+--------+--------+------------+---------+
| id | name | id | name | gender | salary | join_date | dept_id |
+----+-----------+----+-----------+--------+--------+------------+---------+
| 1 | 开发部 | 1 | 孙悟空 | 1 | 7200 | 2013-04-01 | 1 |
| 2 | 市场部 | 3 | 唐僧 | 1 | 9000 | 2019-05-09 | 2 |
| 2 | 市场部 | 4 | 白骨精 | 0 | 5000 | 2020-08-08 | 2 |
+----+-----------+----+-----------+--------+--------+------------+---------

事务

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败！

操作：

• 开启事务：start transaction;
• 回滚事务：rollback;
• 提交事务：commit;

Mysql数据库中事务默认为自动提交
一条DML语句(增删改)语句会自动提交一次事务．
自动提交：mysql就是自动提交的
手动提交：需要手动开启事务 (Oracle默认)

查看默认提交方式：
          select @@autocommit;  ----1
    修改提交方式：
          set @@autocommit = 0;

事务的四大特性ACID:

A原子性：
不可分割的最小单位，要么同时成功，要么同时失败．
C一致性：
事务操作前后，数据总量不变．
I隔离性：
多个事务之间，相互独立．
D持久性：
当事务结束后，数据库会持久化的保存数据．

事务的隔离级别：

• １．read uncommittted： 读未提交
• ２．read committed：读已提交 (Oracle默认)
• ３．repeatable read：可重复读 (Mysql默认)
• ４．serializable：序列化

注意：隔离级别从小到大安全性越来越高，但是效率越来越低
数据库查询隔离级别：

select @@tx_isolation;

数据库设置隔离级别：

set global transaction isolation level 级别;

例：

开启事务１：
　start transaction;  －１
业务逻辑１： 
    update account set money = money-50 where id =1;　　－２
    update account set money = money+50 where id =2;　　－３
提交事务１：
    commit;　　－４

开启事务２：
　start transaction;　　－１
业务逻辑２： 
    select *from account;　　－２
提交事务２：
    commit;　－３

隔离级别为read uncommitted：
     事务１执行完123步骤但未提交，事务２可以查询到数据发生变化,出现了脏读.
隔离级别为read committed：
     事务１在未提交事务时，事务２不可以查询到数据发生变化,事务１提交之后，事务２查询到数据变化，但两次查询数据不一 致，产生了不可重复读．
隔离级别为repeatable read：
     事务１提交完事务，事务２未提交时查询到数据不会发生变化，只有在事务２提交之后才能查询到数据变化．
隔离级别为serializable： 
     在事务１树表进行操作时，事务２进行的操作会被阻塞，直到事务１commit之后才会执行．(采用了表锁)

练习：

create table dept(
id int primary key auto_increment, -- 部门id
dname varchar(50),  -- 部门名称
loc varchar(50) -- 部门所在地
);
insert into dept(id,dname,loc) values
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

create table job( -- 职务表，职务名称，职务描述
id int primary key,
jname varchar(20),
description varchar(50)
);
insert into job(id,jname,description) values
(1,'董事长','管理整个公司,接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客户推销产品'),
(4,'文员','使用办公软件');

create table emp(
id int primary key, -- 员工id
ename varchar(20), -- 员工姓名
job_id int, -- 职务id
mgr int, -- 上级领导
join_date date, -- 入职时间
salary decimal(7,2), -- 工资
bonus decimal(7,2), -- 奖金
dept_id int, -- 所在部门id
constraint emp_jobid_ref_job_id_fk foreign key (job_id) references job(id),
constraint emp_deptid_ref_dept_id_fk foreign key (dept_id) references dept(id)
);
insert into emp(id,ename,job_id,mgr,join_date,salary,bonus,dept_id) values
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',null,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',null,20),
(1005,'李逵',4,1006,'2000-09-28','12500.00','14000.00',30),
(1006,'宋江',4,1009,'2001-05-01','2850.00',null,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',null,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',null,20),
(1009,'罗贯中',1,null,'2001-11-17','50000.00',null,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',null,20),
(1012,'李逵',4,1006,'2001-12-103','9500.00',null,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',null,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',null,10);

create table salarygrade( -- 工资等级表
grade int primary key, -- 级别
losalary int, -- 最低工资
hisalary int -- 最高工资
);

insert into salarygrade(grade,losalary,hisalary) values
(1,7000,12000),
(2,12010,14000)
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

需求：
１．查询所有员工信息．查询员工编号，员工姓名，工资，职务名称，职务描述
分析：分析列信息(分别来自两个表emp 和 job) 连接查询

select t1.id,t1.ename,t1.salary,t2.jname,t2.description from emp t1,job t2 where t1.job_id=t2.i;
+------+-----------+----------+-----------+---------------------------+
| id | ename | salary | jname | description |
+------+-----------+----------+-----------+---------------------------+
| 1009 | 罗贯中 | 50000.00 | 董事长 | 管理整个公司,接单 |
| 1004 | 唐僧 | 29750.00 | 经理 | 管理部门员工 |
| 1007 | 刘备 | 24500.00 | 经理 | 管理部门员工 |
| 1002 | 卢俊义 | 16000.00 | 销售员 | 向客户推销产品 |
| 1003 | 林冲 | 12500.00 | 销售员 | 向客户推销产品 |
| 1010 | 吴用 | 15000.00 | 销售员 | 向客户推销产品 |
| 1001 | 孙悟空 | 8000.00 | 文员 | 使用办公软件 |
| 1005 | 李逵 | 12500.00 | 文员 | 使用办公软件 |
| 1006 | 宋江 | 2850.00 | 文员 | 使用办公软件 |
| 1008 | 猪八戒 | 30000.00 | 文员 | 使用办公软件 |
| 1011 | 沙僧 | 11000.00 | 文员 | 使用办公软件 |
| 1012 | 李逵 | 9500.00 | 文员 | 使用办公软件 |
| 1013 | 小白龙 | 30000.00 | 文员 | 使用办公软件 |
| 1014 | 关羽 | 13000.00 | 文员 | 使用办公软件 |
+------+-----------+----------+-----------+---------------------------+

２．查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

select t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc from emp t1,job t2,dept t3 where t1.job_id=t2.id and t1.dept_id=t3.id desc;
+------+-----------+----------+-----------+---------------------------+-----------+--------+
| id | ename | salary | jname | description | dname | loc |
+------+-----------+----------+-----------+---------------------------+-----------+--------+
| 1009 | 罗贯中 | 50000.00 | 董事长 | 管理整个公司,接单 | 教研部 | 北京 |
| 1007 | 刘备 | 24500.00 | 经理 | 管理部门员工 | 教研部 | 北京 |
| 1014 | 关羽 | 13000.00 | 文员 | 使用办公软件 | 教研部 | 北京 |
| 1004 | 唐僧 | 29750.00 | 经理 | 管理部门员工 | 学工部 | 上海 |
| 1001 | 孙悟空 | 8000.00 | 文员 | 使用办公软件 | 学工部 | 上海 |
| 1008 | 猪八戒 | 30000.00 | 文员 | 使用办公软件 | 学工部 | 上海 |
| 1011 | 沙僧 | 11000.00 | 文员 | 使用办公软件 | 学工部 | 上海 |
| 1013 | 小白龙 | 30000.00 | 文员 | 使用办公软件 | 学工部 | 上海 |
| 1002 | 卢俊义 | 16000.00 | 销售员 | 向客户推销产品 | 销售部 | 广州 |
| 1003 | 林冲 | 12500.00 | 销售员 | 向客户推销产品 | 销售部 | 广州 |
| 1010 | 吴用 | 15000.00 | 销售员 | 向客户推销产品 | 销售部 | 广州 |
| 1005 | 李逵 | 12500.00 | 文员 | 使用办公软件 | 销售部 | 广州 |
| 1006 | 宋江 | 2850.00 | 文员 | 使用办公软件 | 销售部 | 广州 |
| 1012 | 李逵 | 9500.00 | 文员 | 使用办公软件 | 销售部 | 广州 |
+------+-----------+----------+-----------+---------------------------+-----------+--------+

３．查询员工姓名，工资，工资等级
条件：emp.salary >= salarygrade.losalary and emp.salary <=salarygrade.hisalary
emp.salary between salarygrade.losalary and salarygrade.hisalary

select t1.ename,t1.salary,t2.grade from emp t1,salarygrade t2 where t1.salary between t2.losalary and t2.hisalary;
+-----------+----------+-------+
| ename | salary | grade |
+-----------+----------+-------+
| 孙悟空 | 8000.00 | 1 |
| 卢俊义 | 16000.00 | 3 |
| 林冲 | 12500.00 | 2 |
| 唐僧 | 29750.00 | 4 |
| 李逵 | 12500.00 | 2 |
| 刘备 | 24500.00 | 4 |
| 猪八戒 | 30000.00 | 4 |
| 罗贯中 | 50000.00 | 5 |
| 吴用 | 15000.00 | 3 |
| 沙僧 | 11000.00 | 1 |
| 李逵 | 9500.00 | 1 |
| 小白龙 | 30000.00 | 4 |
| 关羽 | 13000.00 | 2 |
+-----------+----------+-------+

４．查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

select t1.ename,t1.salary,t2.*,t3.*,t4.grade from emp t1,job t2,dept t3,salarygrade t4 where t1.dept_id=t3.id and t1.job_id=t2.id and  t1.salary between t4.losalary and t4.hisalary;
+-----------+----------+----+-----------+---------------------------+----+-----------+--------+-------+
| ename | salary | id | jname | description | id | dname | loc | grade |
+-----------+----------+----+-----------+---------------------------+----+-----------+--------+-------+
| 孙悟空 | 8000.00 | 4 | 文员 | 使用办公软件 | 20 | 学工部 | 上海 | 1 |
| 沙僧 | 11000.00 | 4 | 文员 | 使用办公软件 | 20 | 学工部 | 上海 | 1 |
| 李逵 | 9500.00 | 4 | 文员 | 使用办公软件 | 30 | 销售部 | 广州 | 1 |
| 关羽 | 13000.00 | 4 | 文员 | 使用办公软件 | 10 | 教研部 | 北京 | 2 |
| 林冲 | 12500.00 | 3 | 销售员 | 向客户推销产品 | 30 | 销售部 | 广州 | 2 |
| 李逵 | 12500.00 | 4 | 文员 | 使用办公软件 | 30 | 销售部 | 广州 | 2 |
| 卢俊义 | 16000.00 | 3 | 销售员 | 向客户推销产品 | 30 | 销售部 | 广州 | 3 |
| 吴用 | 15000.00 | 3 | 销售员 | 向客户推销产品 | 30 | 销售部 | 广州 | 3 |
| 刘备 | 24500.00 | 2 | 经理 | 管理部门员工 | 10 | 教研部 | 北京 | 4 |
| 唐僧 | 29750.00 | 2 | 经理 | 管理部门员工 | 20 | 学工部 | 上海 | 4 |
| 猪八戒 | 30000.00 | 4 | 文员 | 使用办公软件 | 20 | 学工部 | 上海 | 4 |
| 小白龙 | 30000.00 | 4 | 文员 | 使用办公软件 | 20 | 学工部 | 上海 | 4 |
| 罗贯中 | 50000.00 | 1 | 董事长 | 管理整个公司,接单 | 10 | 教研部 | 北京 | 5 |
+-----------+----------+----+-----------+---------------------------+----+-----------+--------+-------+

５．查询出部门编号，部门名称，部门位置，部门人数

查询部门人数: select dept_id,count(id) from emp group by dept_id;
select *from dept t1,( select dept_id,count(id) from emp group by dept_id) t2 where t1.id =t2.dept_id;
select *from dept t1,( select dept_id,count(id) from emp group by dept_id) t2 where t1.id =t2.dept_id;
+----+-----------+--------+---------+-----------+
| id | dname | loc | dept_id | count(id) |
+----+-----------+--------+---------+-----------+
| 10 | 教研部 | 北京 | 10 | 3 |
| 20 | 学工部 | 上海 | 20 | 5 |
| 30 | 销售部 | 广州 | 30 | 6 |
+----+-----------+--------+---------+-----------+
６．查询所有员工的姓名以及其直接上级的姓名，没有领导的员工也要查询．
select t2.ename,t2.mgr,t1.ename,t1.id from emp t1,emp t2 where t1.id=t2.mgr;
+-----------+------+-----------+------+
| ename | mgr | ename | id |
+-----------+------+-----------+------+
| 孙悟空 | 1004 | 唐僧 | 1004 |
| 卢俊义 | 1006 | 宋江 | 1006 |
| 林冲 | 1006 | 宋江 | 1006 |
| 唐僧 | 1009 | 罗贯中 | 1009 |
| 李逵 | 1006 | 宋江 | 1006 |
| 宋江 | 1009 | 罗贯中 | 1009 |
| 刘备 | 1009 | 罗贯中 | 1009 |
| 猪八戒 | 1004 | 唐僧 | 1004 |
| 吴用 | 1006 | 宋江 | 1006 |
| 沙僧 | 1004 | 唐僧 | 1004 |
| 李逵 | 1006 | 宋江 | 1006 |
| 小白龙 | 1004 | 唐僧 | 1004 |
| 关羽 | 1007 | 刘备 | 1007 |
+-----------+------+-----------+------+
使用左外连接
select  t1.ename,t1.mgr,t2.ename,t2.id from emp t1 left join emp t2 on t1.mgr = t2.id;
+-----------+------+-----------+------+
| ename | mgr | ename | id |
+-----------+------+-----------+------+
| 孙悟空 | 1004 | 唐僧 | 1004 |
| 卢俊义 | 1006 | 宋江 | 1006 |
| 林冲 | 1006 | 宋江 | 1006 |
| 唐僧 | 1009 | 罗贯中 | 1009 |
| 李逵 | 1006 | 宋江 | 1006 |
| 宋江 | 1009 | 罗贯中 | 1009 |
| 刘备 | 1009 | 罗贯中 | 1009 |
| 猪八戒 | 1004 | 唐僧 | 1004 |
| 罗贯中 | NULL | NULL | NULL |
| 吴用 | 1006 | 宋江 | 1006 |
| 沙僧 | 1004 | 唐僧 | 1004 |
| 李逵 | 1006 | 宋江 | 1006 |
| 小白龙 | 1004 | 唐僧 | 1004 |
| 关羽 | 1007 | 刘备 | 1007 |
+-----------+------+-----------+------+