mysql基础

最新推荐文章于 2024-06-21 14:09:29 发布

fwstart

最新推荐文章于 2024-06-21 14:09:29 发布

阅读量5.9k

点赞数 3

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_43030011/article/details/126430462

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1、将查询出来的字段显示为中文

 select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from  emp;

注意：字符串必须添加单引号 | 双引号

可以采用 as 关键字重命名表字段，其实 as 也可以省略，如：

 select  empno "员工编号", ename "员工姓名", sal*12 "年薪" from emp;

2、条件查询

条件查询需要用到 where 语句，where 必须放到 from 语句表的后面支持如下运算符

运算符	说明
=	等于
<> 或！=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between… and ….	两个值之间，等同于 >= and <=
is null	为 null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个 o（r not in 不在这个范围中）
not	not 可以取非，主要用在 is 或 in 中
like	like 称为模糊查询，支持 % 或下划线匹配 % 匹配任意个字符下划线，一个下划线只匹配一个字符

2.1 、等号操作符

查询薪水为 5000 的员工

  select empno, ename, sal from  emp where sal=5000;

查询 job 为 MANAGER 的员工

select empno, ename from emp  where job=manager;

以上查询出现错误，因为 job 为字符串，所以出现了以上错误

 select empno, ename from emp  where job="manager";

  select  empno, ename from emp where job=’manager’;

也可以使用单引号

select empno, ename from emp  where job='MANAGER';

以上输出正确，Mysql 默认情况下大小写是不敏感的。

注意：

MySQL 在 windows 下是不区分大小写的，将 script 文件导入 MySQL 后表名也会自动转化为小写，结果再想要将数据库导出放到 linux 服务器中使用时就出错了。因为在 linux 下表名区分大小写而找不到表，查了很多都是说在 linux 下更改 MySQL 的设置使其也不区分大小写，但是有没有办法反过来让 windows 下大小写敏感呢。其实方法是一样的，相应的更改 windows 中 MySQL 的设置就行了。

具体操作：

在 MySQL 的配置文件 my.ini 中增加一行：

lower_case_table_names = 0

其中 0：区分大小写，1：不区分大小写

MySQL 在 Linux 下数据库名、表名、列名、别名大小写规则是这样的：

数据库名与表名是严格区分大小写的；
表的别名是严格区分大小写的；
列名与列的别名在所有的情况下均是忽略大小写的；
变量名也是严格区分大小写的； MySQL 在 Windows 下都不区分大小写

2.2 、 <> 操作符

查询薪水不等于 5000 的员工

  select  empno, ename, sal from emp where sal <> 5000;

一下写法等同于以上写法，建议使用第一种写法

  select empno, ename, sal from  emp where sal != 5000;

数值也可以采用单引号引起来，如一下语句是正确的 (不建议这么写)：

  select  empno, ename, sal from emp where sal <> '5000';

查询工作岗位不等于 MANAGER 的员工

select  empno, ename from emp where job <> 'MANAGER';

2.3 、between … and … 操作符

查询薪水为 1600 到 3000 的员工 (第一种方式，采用>= 和 <=)

 select empno, ename, sal from  emp where sal >= 1600 and sal <= 3000;

查询薪水为 1600 到 3000 的员工 (第一种方式，采用 between … and …)

  select empno, ename, sal from  emp where sal between 1600 and 3000;

关于 between … and …，它是包含最大值和最小值的

2.4 、is null

Null 为空，但不是空串，为 null 可以设置这个字段不填值，如果查询为 null 的字段，采用 is null
查询津贴为空的员工

 select * from emp where  comm=null;

以上也无法查询出符合条件的数据，因为 null 类型比较特殊，必须使用 is 来比较

  select  * from emp where comm is null;

以上查询正确

2.5 、 and

and 表示并且的含义，表示所有的条件必须满足

工作岗位为 MANAGER, 薪水大于 2500 的员工

  select * from emp where  job='MANAGER' and sal > 2500;

2.6 、or

or，只要满足条件即可，相当于包含

查询出 job 为 manager 或者 job 为 salesman 的员工

select  * from emp where job='MANAGER' or job='SALESMAN';

2.7 、表达式的优先级

查询薪水大于 1800，并且部门代码为 20 或 30 的员工（错误的写法）

 select * from emp where sal  > 1800 and deptno = 20 or deptno = 30;

以上输出不是预期结果，薪水小于 1800 的数据也被查询上来了，原因是表达式的优先级导致的，首先过滤 sal > 1800 and deptno = 20，然后再将 deptno = 30 员工合并过来，所以是不对的

查询薪水大于 1800，并且部门代码为 20 或 30 的（正确的写法）

 select  * from emp where sal > 1800 and (deptno = 20 or deptno = 30);

关于运算符的问题：不用记，没有把握尽量采用括号

2.8 、in

in 表示包含的意思，完全可以采用 or 来表示，采用 in 会更简洁一些

查询出 job 为 manager 或者 job 为 salesman 的员工

 select * from emp where job in  ('manager','salesman');

查询出薪水包含 1600 和薪水包含 3000 的员工

select  * from emp where sal in(1600, 3000);

2.9 、not

查询出薪水不包含 1600 和薪水不包含 3000 的员工（第一种写法）

  select  * from emp where sal <> 1600 and sal <> 3000;

查询出薪水不包含 1600 和薪水不包含 3000 的员工（第二种写法)

 select * from emp where not  (sal = 1600 or sal = 3000);

查询出薪水不包含 1600 和薪水不包含 3000 的员工（第三种写法）

  select  * from emp where sal not in (1600, 3000);

查询出津贴不为 null 的所有员工

  select * from emp where comm  is not null;

2.10 、like

Like 可以实现模糊查询，like 支持 % 和下划线匹配
查询姓名以 M 开头所有的员工

 select  * from emp where ename like 'M%';

查询姓名以 N 结尾的所有的员工

select  * from emp where ename like '%N';

查询姓名中包含 O 的所有的员工

 select  * from emp where ename like '%O%';

查询姓名中第二个字符为 A 的所有员工

  select * from emp where ename  like '_A%';

Like 中 % 和下划线的差别？

% 匹配任意字符出现的个数下划线只匹配一个字符

Like 中的表达式必须放到单引号中 | 双引号中，以下写法是错误的：

 select * from emp  where ename like _A%

3、排序数据

3.1 、单一字段排序

排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by 默认采用升序，如果存在 where 子句那么 order by 必须放到 where 语句的后面

按照薪水由小到大排序 (系统默认由小到大)

  select  * from emp order by sal;

取得 job 为 MANAGER 的员工，按照薪水由小到大排序 (系统默认由小到大)

select  * from emp where job='MANAGER' order by sal;

如果包含 where 语句 order by 必须放到 where 后面，如果没有 where 语句 order by 放到表的后面以下写法是错误的：

select * from emp order by sal where job='MANAGER';

按照多个字段排序，如：首先按照 job 排序，再按照 sal 排序

select  * from emp order by job,sal;

3.2 、手动指定排序顺序

手动指定按照薪水由小到大排序

select * from emp order by sal  asc;

手动指定按照薪水由大到小排序

 select * from emp order by sal  desc;

3.3 、多个字段排序

按照 job 和薪水倒序

 select * from emp order by job  desc, sal desc;

如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序

3.4 、使用字段的位置来排序

按照薪水升序

select  * from emp order by 6;

不建议使用此种方式，采用数字含义不明确，程序不健壮

4、数据处理函数 / 单行处理函数

Lower	转换小写
upper	转换大写
substr	取子串（substr (被截取的字符串，起始下标，截取的长度)）
length	取长度
trim	去空格

str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
Ifnull	可以将 null 转换成一个具体值

4.1 、lower

l 查询员工，将员工姓名全部转换成小写

 select  lower(ename) from emp;

4.2 、upper

查询 job 为 manager 的员工

  select * from emp where  job=upper('manager');

4.3 、substr

查询姓名以 M 开头所有的员工

 select * from emp where  substr(ename, 1, 1)=upper('m');

4.4 、length

取得员工姓名长度为 5 的

 select  length(ename), ename from emp where length(ename)=5;

4.5 、trim

trim 会去首尾空格，不会去除中间的空格

取得工作岗位为 manager 的所有员工

select  * from emp where job=trim(upper('manager '));

4.6 、str_to_date (必须严格按照标准输出)

查询 1981-02-20 入职的员工（第一种方法，与数据库的格式匹配上）

 select  * from emp where HIREDATE='1981-02-20';

查询 1981-02-20 入职的员工（第二种方法，将字符串转换成 date 类型）

select * from emp where  HIREDATE=str_to_date('1981-02-20','%Y-%m-%d');  
select * from emp where   HIREDATE=str_to_date('02-20-1981','%m-%d-%Y');

str_to_date 可以将字符串转换成日期，具体格式 str_to_date (字符串，匹配格式)

4.7 、date_format

查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss

select  empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from  emp;

select date_format(now(),'%Y-%m-%d %H %i %s');

now () 获得当前时间

日期格式的说明

%Y：代表 4 位的年份
%y：代表 2 位的年份
%m：代表月, 格式为(01……12) 
%c：代表月, 格式为(1……12)  

%H：代表小时,格式为(00… …23 ) 
%h： 代表小时,格式为(01……12)   

%i： 代表分钟, 格式为(00……59)  

%r：代表 时间,格式为 12 小时(hh:mm:ss [AP]M) 
%T ：代表 时间,格式为 24 小时(hh:mm:ss)  

%S：代表 秒,格式为(00……59) 
%s：代表 秒,格式为(00……59)

4.8 、format

查询员工薪水加入千分位

  select empno, ename,  Format(sal, 0) from emp;

查询员工薪水加入千分位和保留两位小数

  select empno, ename,  Format(sal, 2) from emp;

4.9 、round

四舍五入

 select round(123.56);

4.10 、rand()

生成随机数

select  rand();

随机抽取记录数

select  * from emp order by   rand()  limit 2;

order by 必须写上。

4.11 、case … when … then ……else …end

如果 job 为 MANAGERG 薪水上涨 10%, 如果 job 为 SALESMAN 工资上涨 50%

select  empno, ename, job, sal, case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then  sal*1.5  end as newsal from emp;

其他的工资不动，需要添加 else

select e.*,sal ,case job when 'salesman' then  sal*1.1 when 'clerk' then sal*1.2 else sal end as new_sal from emp e; 
e.*:取 emp 表所有的字段 , emp as e 是表的别名可以省略as emp e

4.12 、ifnull

select  ifnull(comm,0)  from emp;  
如果 comm 为 null 就替换为 0
在 SQL 语句当中若有 NULL 值参与数学运算，计算结果一定是 NULL
为了防止计算结果出现NULL，建议先使用 ifnull 空值处理函数预先处理。以下 SQL 是计算年薪的：
select  empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;

数据处理函数又被称为单行处理函数，特点：输入一行输出一行

5、分组函数 / 聚合函数 / 多行处理函数

count	取得记录数
sum	求和

avg	取平均
max	取最大的数
min	取最小的数

注意：分组函数自动忽略空值，不需要手动的加where 条件排除空值。
select count(*) from emp where xxx; 符合条件的所有记录总数。
select count(comm) from emp; comm 这个字段中不为空的元素总数。

注意：分组函数不能直接使用在 where 关键字后面。

mysql> select ename,sal from emp where sal > avg(sal); ERROR 1111 (HY000): Invalid use of group function

5.1 、count

取得所有的员工数

select count(*) from emp;

Count (*) 表示取得所有记录，忽略 null，为 null 的值也会取得

取得津贴不为 null 员工数

  select count(comm) from emp;

采用 count (字段名称)，不会取得为 null 的记录

取得工作岗位的个数

select count(distinct job ) from emp;

5.2 、sum

Sum 可以取得某一个列的和，null 会被忽略
取得薪水的合计

select  sum(sal) from emp;

取得津贴的合计

select sum(comm) from emp;

null 会被忽略

取得薪水的合计（sal+comm）

select sum(sal+comm) from emp;

从以上结果来看，不正确，原因在于 comm 字段有 null 值，所以无法计算，sum 会忽略掉，正确的做法是将 comm 字段转换成 0

 select sum(sal+IFNULL(comm,  0)) from emp;

5.3 、avg

取得某一列的平均值

取得平均薪水

  select  avg(sal) from emp;

5.4 、max

取得某个一列的最大值

取得最高薪水

select  max(sal) from emp;

取得最晚入职得员工

select  max(str_to_date(hiredate, '%Y-%m-%d')) fromemp;

5.5 、min

取得某个一列的最小值

取得最低薪水

select  min(sal) from emp;

取得最早入职得员工（可以不使用 str_to_date 转换）

  select  min(str_to_date(hiredate, '%Y-%m-%d')) from emp;

5.6 、组合聚合函数

可以将这些聚合函数都放到 select 中一起使用

select  count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

6、分组查询

分组查询主要涉及到两个子句，分别是：groupby 和 having

6.1 、group by

取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

select  job, sum(sal) from emp group by job;

如果使用了 order by，order by 必须放到 group by 后面

按照工作岗位和部门编码分组，取得的工资合计
- 原始数据
- 分组语句

  select job,deptno,sum(sal)  from empgroup by job,deptno;

mysql> select empno,deptno,avg(sal) from emp group by deptno;

+——-+ +– +

| empno | deptno | avg(sal) |

+——-+ +– +

| 7782 | 10 | 2916.666667 |

| 7369 | 20 | 2175.000000 |

| 7499 | 30 | 1566.666667 |

+——-+ +———– +

以上 SQL 语句在 Oracle 数据库中无法执行，执行报错。

以上 SQL 语句在 Mysql 数据库中可以执行，但是执行结果矛盾。

在 SQL 语句中若有 group by 语句，那么在 select 语句后面只能跟分组函数 + 参与分组的字段。

6.2 、having

如果想对分组数据再进行过滤需要使用 having 子句取得每个岗位的平均工资大于 2000

  select job, avg(sal) from emp group by job having  avg(sal) >2000;

分组函数的执行顺序：

根据条件查询数据

分组

采用 having 过滤，取得正确的数据

6.3 、select 语句总结

一个完整的 select 语句格式如下

select 字段

from 表名

where ......

 group by ......

having....... (就是为了过滤分组后的数据而存在的—不可以单独的出现)  

order by ......

以上语句的执行顺序

首先执行 where 语句过滤原始数据
执行 group by 进行分组
执行 having 对分组数据进行操作
执行 select 选出数据
执行 order by 排序

原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤的。

7、连接查询

7.1 、SQL92 语法

连接查询：也可以叫跨表查询，需要关联多个表进行查询

显示每个员工信息，并显示所属的部门名称

 SQL> select ename, dname from emp, dept;

ENAME    DNAME                                                                                        
---------- --------------

    SMITH    ACCOUNTING                                
    ALLEN    ACCOUNTING                                
    WARD    ACCOUNTING                    
    JONES    ACCOUNTING                        
    MARTIN    ACCOUNTING                    
    BLAKE    ACCOUNTING                    
    CLARK    ACCOUNTING                
    SCOTT    ACCOUNTING                
    KING    ACCOUNTING                    
    TURNER    ACCOUNTING                
    ADAMS    ACCOUNTING                
    JAMES    ACCOUNTING                
    FORD    ACCOUNTING        
    MILLER    ACCOUNTING    
    SMITH    RESEARCH            
    ALLEN    RESEARCH                
    WARD    RESEARCH    
    JONES    RESEARCH            
    MARTIN    RESEARCH    
    BLAKE    RESEARCH        
    CLARK    RESEARCH    
    SCOTT    RESEARCH    
    KING    RESEARCH    
    TURNER    RESEARCH    
    ADAMS    RESEARCH    
    JAMES    RESEARCH

以上输出，不正确，输出了 56 条数据，其实就是两个表记录的成绩，这种情况我们称为：“笛卡儿乘积”，出现错误的原因是：没有指定连接条件

指定连接条件

select  emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno;  
也可以使用别名  select  e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

以上结果输出正确，因为加入了正确的连接条件

以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）

取得员工和所属的领导的姓名

select  e.ename, m.ename from emp e, emp m where e.mgr=m.empno;
SQL>  select * from emp;（普通员工）
EMPNO ENAME    JOB            MGR  HIREDATE    SAL       COMM  DEPTNO
------------------------------------------------------------

7369 SMITH    CLERK        7902 17-12 月-80    800             20
7499 ALLEN    SALESMAN    7698 20-2 月 -81    1600    300     30
7521 WARD    SALESMAN    7698 22-2 月 -81    1250    500     30
7566 JONES    MANAGER        7839 02-4 月 -81    2975         20
7654 MARTIN    SALESMAN    7698 28-9 月 -81    1250    1400 30
7698 BLAKE    MANAGER        7839 01-5 月 -81    2850         30
7782 CLARK    MANAGER        7839 09-6 月 -81    2450         10
7788 SCOTT    ANALYST        7566 19-4 月 -87    3000         20
7839 KING    PRESIDENT    17-11 月-81    5000             10
7844 TURNER    SALESMAN    7698 08-9 月 -81    1500    0     30
7876 ADAMS    CLERK        7788 23-5 月 -87    1100         20
7900 JAMES    CLERK        7698 03-12 月-81    950             30
7902 FORD    ANALYST        7566 03-12 月-81    3000         20
7934 MILLER    CLERK        7782 23-1 月 -82    1300         10

已选择 14 行。                
SQL> select * from emp;（管理者）

以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表 emp e 代码了员工表，emp m 代表了领导表，相当于员工表和部门表一样

7.2 、SQL99 语法

（内连接）显示薪水大于 2000 的员工信息，并显示所属的部门名称

采用 SQL92 语法：

select e.ename, e.sal, d.dname from emp e, dept d where  e.deptno=d.deptno and  e.sal > 2000;

采用 SQL99 语法：

select e.ename, e.sal, d.dname from emp e join dept d  on e.deptno=d.deptno where  e.sal>2000;  
或  
select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where  e.sal>2000;  
在实际中一般不加inner 关键字 
SQL92 语法和 SQL99 语法的区别：99 语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比 SQL92更清晰。

（外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来

右连接：
select  e.ename, e.sal, d.dname from emp e right join  dept d on e.deptno=d.deptno; 
左连接：
select  e.ename, e.sal, d.dname from dept d left joinemp  e on e.deptno=d.deptno;  
以上两个查询效果相同

连接分类：

内链接：

* 表 1 inner join 表 2 on 关联条件

* 做连接查询的时候一定要写上关联条件

* inner 可以省略

外连接：

左外连接

* 表 1 left outer join 表 2 on 关联条件

* 做连接查询的时候一定要写上关联条件

* outer 可以省略

右外连接

* 表 1 right outer join 表 2 on 关联条件

* 做连接查询的时候一定要写上关联条件

* outer 可以省略

左外连接（左连接）和右外连接（右连接）的区别：

左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示
右连接恰恰相反，以上左连接和右连接也可以加入 outer 关键字，但一般不建议这种写法<，如：

 select e.ename, e.sal, d.dname  from emp e right outerjoin dept d on  e.deptno=d.deptno;  
select  e.ename, e.sal, d.dname from dept d left outer join  emp e on e.deptno=d.deptno;

左连接能完成的功能右连接一定可以完成

8、子查询

子查询就是嵌套的 select 语句，可以理解为子查询是一张表

8.1 、在 where 语句中使用子查询，也就是在 where 语句中加入 select 语句

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名实现思路：

1、首先取得管理者的编号，去除重复的

select  distinct mgr from emp where mgr is not null;    
distinct 去除重复行

2、查询员工编号包含管理者编号的

select empno, ename from  emp where empno in(select mgr from emp where  mgr is not null);

查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水实现思路

1、取得平均薪水

  select avg(sal) from emp;

2、取得大于平均薪水的员工

select empno, ename, sal from emp where sal > (select  avg(sal) from emp);

11.2 、在 from 语句中使用子查询，可以将该子查询看做一张表

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名首先取得管理者的编号，去除重复的

  select distinct mgr from emp where mgr is not null;

将以上查询作为一张表，放到 from 语句的后面

使用 92 语法：

select e.empno, e.ename from emp e, (select  distinct mgr from emp where mgr is not null) m where e.empno=m.mgr;

使用 99 语法：

select e.empno, e.ename from emp e join (select distinct mgr from emp where  mgr is not null) m on e.empno=m.mgr;

查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号实现思路

1、首先取得各个部门的平均薪水

select deptno, avg(sal)  avg_sal from emp group by deptno;

2、将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级

select deptno,avg(sal) avg_sal from emp group by deptno;
select * from salgrade;
select a.deptno,a.avg_sal,g.grade from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g on a.avg_sal between g.losal and hisal;

8.3 、在 select 语句中使用子查询

查询员工信息，并显示出员工所属的部门名称第一种做法，将员工表和部门表连接

  select e.ename, d.dname from  emp e, dept d where e.deptno=d.deptno;

第二种做法，在 select 语句中再次嵌套 select 语句完成部分名称的查询

 select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as  dname from emp e;

9、union

9.1、union 可以合并集合（相加）

1、查询 job 包含 MANAGER 和包含 SALESMAN 的员工

  select  * from emp where job in('MANAGER', 'SALESMAN');

2、采用 union 来合并

 select * from emp where  job='MANAGER' union  select * from emp where job='SALESMAN'

合并结果集的时候，需要查询字段对应个数相同。在Oracle中更严格，不但要求个数相同，而且还要求类型对应相同*。

10、limit 的使用

mySql 提供了 limit ，主要用于提取前几条或者中间某几行数据

select * from table limit m,n

其中 m 是指记录开始的 index，从 0 开始，表示第一条记录
n 是指从第 m+1 条开始，取 n 条。

select * from tablename limit 2,4
即取出第 3 条至第 6 条，4 条记录

10.1 、取得前 5 条数据

  select * from emp   limit 5;

10.2 、从第二条开始取两条数据

  select * from emp   limit 1,2;

10.3 、取得薪水最高的前 5 名

select  * from emp e order by e.sal desc limit 5;

11、表

11.1 、创建表

语法格式

create table 
    tableName( columnName dataType(length),  ………………..  
    columnName  dataType(length)  
    );  
set  character_set_results='gbk';  
show variables like '%char%';

创建表的时候，表中有字段，每一个字段有：

字段名
字段数据类型
字段长度限制
字段约束

MySql 常用数据类型

类型	描述
Char (长度)	定长字符串，存储空间大小固定，适合作为主键或外键
Varchar (长度)	变长字符串，存储空间等于实际数据空间
double (有效数字位数，小数位)	数值型
Float (有效数字位数，小数位)	数值型
Int (长度)	整型
bigint (长度)	长整型
Date	日期型
BLOB	Binary Large OBject（二进制大对象）
CLOB	Character Large OBject（字符大对象）
其它…………………

建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识

create table  
    t_student( student_id  int(10), 
    student_name   varchar(20), 
    sex  char(2),  
    birthday  date, 
    email   varchar(30), 
    classes_id  int(3))

向 t_student 表中加入数据，（必须使用客户端软件，我们的 cmd 默认是 GBK 编码 , 数据中设置的编码是 UTF-8）

insert  into t_student(student_id, student_name, sex, birthday, email, classes_id)  values(1001,'zhangsan', 'm','1988-01-01', 'qqq@163.com', 10)

向 t_student 表中加入数据（使用默认值）

 drop table if exists t_student;  
 create table t_student(
     student_id    int(10), 
     student_name     varchar(20),  
     sex   char(2) default 'm',  
     birthday date,  
     email  varchar(30),  
     classes_id  int(3))     
 insert into t_student(student_id, student_name, birthday, email,classes_id) 
 values  (1002,'zhangsan','1988-01-01','qqq@163.com',10)

11.2 、增加 / 删除 / 修改表结构

采用 alter table 来增加 / 删除 / 修改表结构，不影响表中的数据

11.2.1 、添加字段

如：需求发生改变，需要向 t_student 中加入联系电话字段，字段名称为：contatct_tel 类型为 varchar (40)

alter  table t_student add contact_tel varchar(40);

11.2.2 、修改字段

如：student_name 无法满足需求，长度需要更改为 100

alter table t_student modify  student_name varchar(100) ;

如 sex 字段名称感觉不好，想用gender 那么就需要更爱列的名称

11.2.3 、删除字段

如：删除联系电话字段

alter table t_student drop  contact_tel;

11.3 、添加、修改和删除

11.3.1 、insert

添加、修改和删出都属于 DML，主要包含的语句：insert、update、delete

Insert 语法格式

Insert into 表名(字段，。。。。) values(值,................. )

省略字段的插入

insert into emp  values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到 insert 语句

指定字段的插入 (建议使用此种方式)

 insert   into    emp           (empno,ename,job,mgr,hiredate,sal,comm,deptno)  values(9999,'zhangsan','MANAGER',  null, null,3000, 500, 10);

出现了主键重复的错误，主键表示了记录的唯一性，不能重复

如何插入日期：

第一种方法，插入的日期格式和显示的日期格式一致

insert  into  emp(empno,  ename, job, mgr, hiredate, sal,        comm, deptno)  
values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10);

第二种方法，采用 str_to_date

insert  into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) valu  es(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000,  500, 10);

第三种方法，添加系统日期（now ()）

 insert    into    emp(empno,     ename,    job,   mgr,           hiredate,                  sal,        comm,           deptno)  values(9995,'zhangsan','MANAGER',null,now()  ,3000, 500, 10);

表复制

create  table emp_bak as select empno,ename,sal from emp;

以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中

如何将查询的数据直接放到已经存在的表中，可以使用条件

insert  into emp_bak select * from emp where sal=3000;

11.3.2 、update

可以修改数据，可以根据条件修改数据

语法格式：

update  表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where …….

将 job 为 manager 的员工的工资上涨 10%

update emp set sal=sal+sal*0.1  where job='MANAGER';

11.3.3 、delete

可以删除数据，可以根据条件删除数据

语法格式：

 Delete from 表名 where 。。。。。

删除津贴为 500 的员工

  delete  from emp where comm=500;

删除津贴为 null 的员工

delete from emp where comm is  null;

11.4 、创建表加入约束

常见的约束

a) 非空约束，not null

b) 唯一约束，unique

c) 主键约束，primary key

d) 外键约束，foreign key

e) 自定义检查约束，check（不建议使用）(在 mysql 中现在还不支持)

11.4.1 、非空约束，not null

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

drop table if exists t_student;  
create table t_student(  
    student_id    int(10),  
    student_name          varchar(20) not null, 
    sex          char(2)          default 'm',  
    birthday date,  email         varchar(30),  
    classes_id     int(3)  )     
    insert into t_student(student_id, birthday, 
    email,  classes_id) 
    values  (1002, '1988-01-01', 'qqq@163.com', 10)

以上错误为加入的学生姓名为空。

11.4.2 、唯一约束，unique

唯一性约束，它可以使某个字段的值不能重复，如：email 不能重复：

drop table if exists t_student;  
create table t_student(  
    student_id    int(10), 
    student_name          varchar(20) not null, 
    sex          char(2)          default 'm',  
    birthday date,  email         varchar(30) unique,  
    classes_id     int(3)  
)  
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)  
values  
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)

以上插入了重复的email，所以出现了“违反唯一约束错误”，所以 unique 起作用了同样可以为唯一约束起个约束名

我们可以查看一下约束

mysql> use information_schema;
mysql> select * from table_constraints where table_name = 't_student';

关于约束名称可以到 table_constraints 中查询以上约束的名称我们也可以自定义。

drop table if  exists t_student;  
create table t_student(student_id  int(10), 
    student_name     varchar(20) not null, 
    sex  char(2)   default 'm', 
    birthday date,  
    email   varchar(30) ,
    classes_id     int(3) , 
constraint email_unique unique(email)/*表级约束*/      )

11.4.3 、主键约束，primary key

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的

  drop table if exists t_student;  
  create table t_student(
  student_id  int(10) 
  primary key,/*列级约束*/ 
  student_name varchar(20) not null,  
  sex  char(2)  default 'm',  
  birthday date,  
  email  varchar(30), 
  classes_id int(3))  
  insert into t_student(
  student_id, student_name, sex, birthday, email, classes_id)
  values (1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);

向以上表中加入学号为 1001 的两条记录，出现如下错误，因为加入了主键约束

我们也可以通过表级约束为约束起个名称：

drop table if exists t_student;  
create table t_student
  (student_id int(10), 
  student_name varchar(20) not null,  
  sex char(2) default 'm', 
  birthday date,  
  email varchar(30) ,  
  classes_id int(3),  
  CONSTRAINT p_id PRIMARY key (student_id)  
)  
  insert into t_student(student_id, student_name ,sex,  birthday, email,  classes_id) 
  values 
  (1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);

11.4.4 、外键约束，foreign key

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必

须来源于参照的表的主键，如：emp 中的 deptno 值必须来源于 dept 表中的 deptno 字段值。建立学生和班级表之间的连接

首先建立班级表 t_classes

 drop table if exists t_classes;  
 create table t_classes(
 classes_id int(3), 
 classes_name  varchar(40),
 constraint pk_classes_id primary key(classes_id))

在 t_student 中加入外键约束

drop table if exists t_student; 
create table t_student(
student_id int(10), 
student_name varchar(20),  
sex  char(2),
birthday  date,  
email varchar(30),  
classes_id int(3),
constraint student_id_pk primary key(student_id),  constraint fk_classes_id foreign key(classes_id) references  t_classes(classes_id))

向 t_student 中加入数据

insert into t_student (student_id, student_name, sex, birthday, email, classes_id)  values(1001,'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)

出现错误，因为在班级表中不存在班级编号为 10 班级，外键约束起到了作用

存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表

以上成功的插入了学生信息，当时 classes_id 没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空

drop table if exists t_student;  
create table t_student(  student_id    int(10),  
    student_name          varchar(20), sex       char(2),  
    birthday date,  
    email              varchar(30), 
    classes_id int (3)  not null, 
    constraint       student_id_pk primary  key(student_id),          constraint     fk_classes_id foreign key(classes_id) references t_classes(classes_id)  
 ) 
 insert into t_student(student_id, student_name, sex, birthday, email, cla  sses_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null);

再次插入班级编号为 null 的数据

添加数据到班级表，添加数据到学生表，删除班级数据，将会出现如下错误：

  insert  into t_classes (classes_id,classes_name) 
  values (10,'366');     
  insert  into t_student(  student_id,  
  student_name, sex, birthday, email, classes_id  )  
  values(  1001,  'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10  )  mysql> update t_classes set classes_id = 20 where classes_name = '366';

因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes）中的主键，所以先删除子表中的引用记录，再修改父表中的数据。 我们也可以采取以下措施 级联更新。
mysql> delete from t_classes where classes_id  = 10;

因为子表（t_student）存在一个外键 classes_id，它参照了父表（t_classes)中的主键，所以先删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据， 再删除父表中的数据，采用 drop table 也不行，必须先drop 子表，再 drop 父表 我们也可以采取以下措施 级联删除。

11.4.5 、级联更新与级联删除

11.4.5.1 、on update cascade;

mysql 对有些约束的修改比较麻烦，所以我们可以先删除，再添加     
alter  table t_student drop foreign key fk_classes_id;     
alter table t_student add constraint fk_classes_id_1 foreign  key(classes_id) references  t_classes(classes_id)  on update cascade;

我们只修改了父表中的数据，但是子表中的数据也会跟着变动。`

11.4.5.2 、on delete cascade;

 mysql 对有些约束的修改时不支持的，所以我们可以先删除，再添加 
 alter table t_student drop foreign key  fk_classes_id; 
 alter table t_student add constraint fk_classes_id_1  foreign key(classes_id) references t_classes(classes_id) on delete cascade;  
delete from t_classes where  classes_id = 20;

我们只删除了父表中的数据，但是子表也会中的数据也会删除。

11.5 、t_student 和 t_classes 完整示例

drop table if exists t_classes;  
create table t_classes( 
    classes_id     int (3),  
    classes_name varchar(30)   not null,  
    constraint     pk_classes_id  primary key(classes_id)  
)     
drop table if exists t_student;  
create table t_student( 
    student_id    int(10),  
    student_name          varchar(50) not null,  
    sex  char(2)  not null,  birthday date not null,  
    email              varchar(30) unique, 
    classes_id     int (3) not  null,  
    constraint     pk_student_idprimary key(student_id),              constraint     fk_classes_id foreign key(classes_id)  references t_classes(classes_id)  )

11.6 、增加 / 删除 / 修改表约束

11.6.1 、删除约束

将 t_student

删除外键约束 alter table 表名 drop foreign key 外键（区分大小写）;

alter table t_student drop foreign key fk_classes_id;

删除主键约束：alter table 表名 drop primary key ;

alter table t_student drop primary key;

删除约束约束：alter table 表名 drop key 约束名称；

alter table t drop key uk;

11.6.2 、添加约束

将 t_student 中的约束

添加外键约束：alter table 从表 add constraint 约束名称 foreign key 从表 (外键字段) references 主表 (主键字段);

alter  table t_student add constraint fk_classes_id_1 foreign key(classes_id)  references t_classes(classes_id);

添加主键约束：alter table 表 add constraint 约束名称 primary key 表 (主键字段);

alter  table t_student add constraint pk primary key(student_id);

添加唯一性约束：alter table 表 add constraint 约束名称 unique 表 (字段);

alter  table t_student add constraint uk unique(email);

11.6.3 、修改约束，其实就是修改字段

alter  table t_student modify student_name varchar(30) unique;

mysql 对有些约束的修改时不支持，所以我们可以先删除，再添加

练习题

1、取得每个部门最高薪水的人员名称

说明：

先查出每个部门最高薪水，作为一张临时表 b；
b 表和 a 表连接。
条件：部门编号相同，并且 EMP 的 sal 大于 b 表 maxsal。

select a.ename, b.* from EMP a join (select deptno, max(sal) as maxsal from EMP group by deptno) b on a.deptno = b.deptno and b.maxsal = a.sal;

2、哪些人的薪水在部门的平均薪水之上？

说明：

先查出每个部门平均薪水，作为一张临时表 b；
b 表和 a 表连接，再查哪些人的薪水在部门的平均薪水之上。
条件：部门编号相同，并且 EMP 的 sal 大于 b 表 avgsal。

select a.ename, a.sal from EMP a join (select deptno, avg(sal) avgsal from EMP group by deptno) b on b.deptno = a.deptno and a.sal > avgsal;

3、取得部门中（所有人的）平均的薪水等级，如下：

说明：

先找出每个人的薪资等级

select a.ename, a.deptno, b.grade from EMP a join SALGRADE b on a.sal between b.losal and b.hisal order by deptno;

 select a.deptno, avg(b.grade) from EMP a join SALGRADE b on a.sal between b.losal and b.hisal group by deptno;

4、不准用组函数（Max），取得最高薪水（给出两种解决方案）

(1) limit

select sal from EMP order by sal desc limit 1;

(2) 表自连接

select sal from EMP  where sal not in (select distinct a.sal from EMP a join EMP b on a.sal < b.sal);

5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

(1) limit

select deptno from EMP group by deptno order by avg(sal) desc limit 1;

(2)

找出每个部门平均薪水：

select deptno, avg(sal) from EMP group by deptno;

找出最大值：

select b.deptno, max(b.avgsal) from (select deptno, avg(sal) as avgsal from EMP group by deptno) b;

或者：select deptno,avg(sal) as maxsal from EMP group by deptno having maxsal= (select max(b.avgsal) from (select avg(sal) as avgsal from EMP group by deptno) b);

6、取得平均薪水最高的部门的部门名称

说明：根据上题得出答案：

 select c.dname from (select b.deptno, max(b.avgsal) from (select deptno, avg(sal) as avgsal from EMP group by deptno) b) a join DEPT c on a.deptno = c.deptno;

或者：

select c.dname from (select deptno from EMP group by deptno order by avg(sal) desc limit 1) a join DEPT c on a.deptno = c.deptno;

7、求平均薪水的等级最低的部门的部门名称

说明：

找出部门平均工资：

select d.dname, avg(sal) as avgsal from EMP a join DEPT d on a.deptno = d.deptno group by d.dname;
或者：
select deptno, avg(sal) as avgsal from EMP group by deptno;

找出平均最小值：

select min(b.avgsal) from EMP a join (select avg(sal) as avgsal from EMP group by deptno) b;

找出最低值部门名称：

select a.deptno, c.dname, avg(sal) as avgsal from EMP a join DEPT c on a.deptno=c.deptno group by deptno having avgsal = (select min(b.avgsal) from EMP a join (select avg(sal) as avgsal from EMP group by deptno) b);

薪资最低部门等级：

select d.grade from SALGRADE d join (select min(b.avgsal) minsal from EMP a join (select avg(sal) as avgsal from EMP group by deptno) b) t on t.minsal between d.losal and d.hisal;

8、取得比普通员工 (员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名

找出员工代码没有在 mgr 的普通员工：

select max(sal) from EMP where empno not in(select distinct mgr from EMP where mgr is not null);

找出薪资大于 1600 的员工名，薪水：

select ename, sal from EMP where sal> (select max(sal) from EMP where empno not in(select distinct mgr from EMP where mgr is not null));

9、取得薪水最高的前五名员工

select ename, sal from EMP order by sal desc limit 5;

10、取得薪水最高的第六到第十名员工

select ename, sal from EMP order by sal desc limit 5,5;

11、取得最后入职的 5 名员工

select ename, HIREDATE from EMP order by HIREDATE desc limit 5;

12、取得每个薪水等级有多少员工

说明：

找出每个员工薪水等级：

select a.ename, a.sal, b.grade from EMP as a join SALGRADE as b on a.sal between b.losal and b.hisal;

按 grade 分组统计

select b.grade, count(*) from EMP as a join SALGRADE as b on a.sal between b.losal and b.hisal group by b.grade;

或者：

select b.grade, count(*) from EMP a,SALGRADE b where sal between losal and hisal group by grade;

13、面试题

有 3 个表：

S (学生表)、C（课程表）、SC（学生选课表）

S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：

1，找出没选过 “黎明” 老师的所有学生姓名。

2，列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。

3，即学过 1 号课程又学过 2 号课所有学生的姓名。

请用标准 SQL 语言写出答案，方言也行（请说明是使用什么方言）。

-—————————————————————————-

CREATE TABLE SC
(
SNO    VARCHAR(200),
CNO    VARCHAR(200),
SCGRADE    VARCHAR(200)
);

CREATE TABLE S
(
SNO VARCHAR(200 ),
SNAME VARCHAR(200)
);

CREATE TABLE C
(
CNO    VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);

INSERT INTO C (CNO, CNAME, CTEACHER) VALUES ( '1','语文', '张'); 
INSERT INTO C ( CNO, CNAME, CTEACHER) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER) VALUES ( '3', '英语', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER) VALUES ( '4', '数学', '赵'); 
INSERT INTO C ( CNO, CNAME, CTEACHER) VALUES ( '5', '物理', '黎明'); commit;

INSERT INTO S ( SNO, SNAME) VALUES ( '1', '学生 1');
INSERT INTO S ( SNO, SNAME) VALUES ( '2', '学生 2');
INSERT INTO S ( SNO, SNAME) VALUES ( '3', '学生 3');
INSERT INTO S ( SNO, SNAME) VALUES ( '4', '学生 4');
commit;

INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '1', '5', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '3', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '4', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '2', '5', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '3', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE) VALUES ( '3', '3', '80'); commit;

问题 1. 找出没选过 “黎明” 老师的所有学生姓名。

问题 2: 列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。

问题 3: 即学过 1 号课程又学过 2 号课所有学生的姓名。

14、列出所有员工及领导的姓名

select a.ename '员工', ifnull(b.ename, '没有上级') '领导' from EMP a left join EMP b on a.mgr = b.empno;

15、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

说明：

select a.empno, a.ename, d.dname from EMP a join EMP b on a.mgr = b.empno join DEPT d on a.deptno = d.deptno where a.hiredate < b.hiredate;

16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门.

select b.dname, a.* from EMP a right join DEPT b on a.deptno = b.deptno;

17、列出至少有 5 个员工的所有部门

不知道

select b.dname, count(empno) from EMP a left join DEPT b on a.deptno = b.deptno where (select count(empno) from EMP group by a.deptno) > 5 group by  a.deptno;

18、列出薪金比”SMITH” 多的所有员工信息.

select * from EMP where sal> (select sal from EMP where ename like 'smith');

19、列出所有”CLERK”(办事员) 的姓名及其部门名称，部门的人数.

说明：

列出所有 “CLERK” 办事员所在部门名称及姓名

select a.ename, b.dname from EMP a join DEPT b on a.deptno = b.deptno where a.job = 'clerk';

统计所有部门人数：

select deptno, count(*) deptcount from EMP group by deptno;

把以上 2 张表做表连接：

select t1.*, t2.deptcount
from
(select a.deptno, a.ename,a.job, b.dname from EMP a join DEPT b on a.deptno = b.deptno where a.job = 'clerk') t1
join
(select deptno, count(*) as deptcount from EMP group by deptno) t2
on t1.deptno = t2.deptno;

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数.

select job, count(*) as '人数统计' from EMP a group by job having min(sal)>1500 ;

21、列出在部门”SALES”<销售部> 工作的员工的姓名，假定不知道销售部的部门编号.

select a.ename from EMP a join DEPT b on a.deptno = b.deptno where b.dname='sales';

或者：

select ename from EMP where deptno = (select deptno from DEPT where dname="sales");

22、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级.

说明：

先查公司平薪资
高于平均薪资员工
列出表连接和表关系结构

select a.ename, b.dname, l.ename, c.grade
from 
    EMP a
join 
    DEPT b
on 
    a.deptno = b.deptno
left join EMP l
on
    a.mgr = l.empno
join SALGRADE c
on
    a.sal between c.losal and c.hisal
    where a.sal >  (select avg(sal) from EMP);

23、列出与”SCOTT” 从事相同工作的所有员工及部门名称。

select a.ename, b.dname from EMP a join DEPT b on a.deptno = b.deptno where a.job = (select job from EMP where ename
='SCOTT')
and a.ename<>'scott';

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金。

select ename,sal from EMP where sal in (select sal+ifnull(comm,0) from EMP where deptno = 30) and deptno <>30;

答案：空

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。部门名称。

高于平均薪资：
select a.ename, a.sal+ifnull(comm,0) sumsal, b.dname from EMP a join DEPT b on a.deptno = b.deptno where sal > (select avg(sal) from EMP where deptno=30);

高于最高薪资：
select a.ename, a.sal+ifnull(comm,0) sumsal, b.dname from EMP a join DEPT b on a.deptno = b.deptno where sal > (select max(sal) from EMP where deptno=30);

26、列出在每个部门工作的员工数量，平均工资和平均服务期限.

说明

select b.deptno, count(a.ename), b.dname, ifnull(avg(a.sal),0) avgsal, ifnull(avg(timestampdiff(YEAR, hiredate,now())), 0) avgserviceYear
from 
    EMP a right join DEPT b 
on 
    a.deptno = b.deptno 
group by 
    a.deptno,b.dname,b.loc;

27、列出所有员工的姓名、部门名称和工资。

select a.ename, b.dname, a.sal+ifnull(comm,0) sumsal from EMP a join DEPT b on a.deptno = b.deptno;

28、列出所有部门的详细信息和人数

select b.DEPTNO, b.DNAME, b.LOC, count(a.ename) '人数' from EMP a right join DEPT b on a.deptno = b.deptno group by b.deptno, b.dname, b.loc;

29、列出各种工作的最低工资及从事此工作的雇员姓名

select a.ename, t.*
    from EMP a
    join (select 
    job, min(sal) minsal
from 
    EMP
group by
    job) t
on a.job = t.job and a.sal = t.minsal;

30、列出各个部门的 MANAGER (领导) 的最低薪金

select a.ename, a.deptno, min(sal) from EMP a where job like 'MANAGER' group by deptno;

31、列出所有员工的年工资，按年薪从低到高排序

select ename, (sal+ifnull(comm,0))*12 '年薪' from EMP order by '年薪' asc;

32、求出员工领导的薪水超过 3000 的员工名称与领导名称

select a.ename, b.ename as '领导名' from EMP a join EMP b on a.mgr = b.empno where b.sal >3000;

33、求出部门名称中，带’S’字符的部门员工的工资合计、部门人数。

select b.dname, sum(a.sal+ifnull(a.comm,0)), count(a.ename) from EMP a right join DEPT b on a.deptno=b.deptno where b.dname like '%s%' group by a.deptno, b.dname, b.loc;

34、给任职日期超过 30 年的员工加薪 10%.

select ename '姓名', sal '现在月薪', sal*1.1 '调整后月薪' from EMP where timestampdiff(YEAR,hiredate, now()) >= 30;

fwstart

关注

3
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
mysql基础

mysql操作
复制链接

扫一扫

专栏目录