MySQL基础笔记

笔记网址 https://blog.csdn.net/wangjx92/article/details/79333599

mysql默认端口3306 本机设置端口3306 本地地址 127.0.0.1

C:\Program Files\MySQL\MySQL

C:\Program Files\MySQL\MySQL Workbench 8.0 CE

DQL:数据查询语句,select

DML:数据

操作语句,insert/delete/update

DDL:数据库定义语句,create/drop/alter

TCL:事务控制语言,commit/rollback

//常用命令

show databases; 显示有哪些数据库

use + 数据库名 ; 进入数据库

查看有哪些数据库:

show databases;

查看有哪些表:

show tables;

行表示记录

列表示字段:字段名称,类型,长度,字段约束

desc + 表名;

查看自己在哪个数据库下:

select database();

直接查看其他数据库中有哪些表:

show tables from 数据库名;

退出数据库:

exit;

创建数据库

create databese +新数据库名;

导入数据库脚本

数据库脚本:以.sql后缀结尾的文件

导入数据库脚本:

  1. 选定数据库

  2. source命令

进入要导入脚本的数据库

source + 脚本绝对路径

查看表的字段

desc +表名;

字段,字段类型,字段长度,字段约束

进入到某个数据库之后,查询指定表的某个字段数据

select 字段名 from 表名;

查询多个字段

select 字段名,字段名 from 表名

查询表中的所有字段

select * from 表名 //写道java代码时,不易读懂,不推荐使用。效率比前一个方法要低,因为要把*转成所有具

​ 体字段,而且语义不明确

数字类型的字段可以进行数学运算

select ename,sal*12 from 表名 //查询年薪

给运算后的字段起别名

select ename,sal*12 as yearsal from 表名

//条件查询的运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e2jgpCO5-1600565587832)(E:\笔记\1574313925525.png)]

**!!! in会自动忽略空值

​ not in 不会自动忽略空值,如果有null参与运算 ,结果为空值

where条件查询

查询工资为5000的员工

select ename,sal from emp where sal =5000;

mysql有自动类型转换,但为了兼容其他数据库和语言,不建议使用

字符串应该用单引号包围 ,用双引号容易于java冲突

不等于运算符 <> !=

select ename form emp where sal <> 5000; //推荐

​ where sal != 5000;

is null is not null

18、动力节点_MySQL_018_条件查询_区间操作运算符

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

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

select ename,sal from emp where sal in (800,1600);

select ename,sal from emp where sal not in (800,1600);

select ename,sal from empwhere job in (‘MANAGER’,SALESMAN’);

select ename,sal from empwhere job not in (‘MANAGER’,SALESMAN’);

//and运算符优先级大于or

例子:查询工资大于1800,且编号为20或者30的员工

错误 sal>1800 会和deptno先结合

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

正确

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

like模糊查询 & 任意符 _占位符

slelect ename from emp where ename like ‘%s%’ 所有带有s名字

​ ‘s%’ 以s开头的名字

​ ‘_s%’ 第二个字母为s的名字

order by 字段名 +asc 升序排序

aeder by 字段名 + desc 降序排序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LrFjU2pr-1600565587834)(E:\笔记\图片\1574407256764.png)]

//字段下标

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3nfzcCMr-1600565587836)(E:\笔记\图片\1574407926215.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G5aYLfuv-1600565587838)(E:\笔记\图片\1574408584403.png)]

27、动力节点_MySQL_027_数据处理函数_单行处理函数_转换大小写函数

select lower(ename) as lowername from emp;

select upper(ename) as uppername from emp;

28、动力节点_MySQL_028_数据处理函数_单行处理函数_substr截取字符串函数

substr(字段名,开始下标,截取长度)

select substr(ename,1,1) as firstchar from emp;

mysql 起始下标从一开始

起始下标是从1开始的!

取3,4,5个字符:

select substr(ename,3,3) as ename from emp;

//返回字段的长度

select length(ename) as enameLength from emp;

去掉前后空格:一般在用户输入的时候,查询条件里面会用到(MySQL会自动去掉后面的空格)

select * from emp where ename=trim(‘king ‘);

30、动力节点_MySQL_030_数据处理函数_单行处理函数_round四舍五入函数

select round(123.56);

select round(123.56,0);

上面两个输出都是124

select round(123.56,1);

输出是123.6

select round(123.56,-1);

输出是120

动力节点_MySQL_031_数据处理函数_单行处理函数_rand随机数函数

select rand();

输出0到1闭区间的随机数

select round(rand()*122,0);

输出0到122闭区间的随机数

32、动力节点_MySQL_032_数据处理函数_单行处理函数_case_when_then_else_end函数

匹配工作岗位,为MANAGER时,薪水上调10%,为SALESMAN的时候,薪水上调50%

select job,ename,(case job

when 'MANAGER' then sal*1.1

when 'SALEMAN' then sal*1.5

else sal
end) as newsal from emp;


在这里插入图片描述
在这里插入图片描述
update case when then where
33、动力节点_MySQL_033_数据处理函数_单行处理函数_ifnull空值处理函数

comm是补助得意思

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

统计计算了补贴的所有员工年收入,如果没有补贴,则comm为0

与null 计算的数据都会变成null,ifnull可以把null转换为一个具体的数,这里转换成了0

//日期

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W0C17tl1-1600565587840)(E:\笔记\图片\1574412024429.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yCwBmwQj-1600565587842)(E:\笔记\图片\1574412093337.png)]

35、动力节点_MySQL_035_数据处理函数_单行处理函数_str_to_date函数_总结

1、日期是数据库本身的特使,也是数据库本身机制中的一个重要内容;

2、每一个数据库处理日期采用的机制都不一样,所以在实际开发中将日期字段定义为DATE类型的情况很少

3、如果使用日期类型,java程序则不能通用,实际开发中会使用“日期字符串”来表示日期

36、动力节点_MySQL_036_数据处理函数_单行处理函数_dateformat函数

日期类型转换成特定格式的日期字符串

data_format(日期类型数据,’日期格式’);

例子:

1、 查询员工的入职日期

select ename,data_format(hiredate,’%m-%d-%Y’) as newhiredate from emp;

select ename,data_format(hiredate,’%m/%d/%Y’)as newhiredate from emp;

单行处理函数和多行处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SZ9G3DCn-1600565587843)(E:\笔记\图片\1574413904753.png)]

37、动力节点_MySQL_037_数据处理函数_多行处理函数概述

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2A9E46Yo-1600565587844)(E:\笔记\图片\1574414926903.png)]

38、动力节点_MySQL_038_数据处理函数_多行处理函数_sum求和函数

例子:

薪水总和:

select sum(sal) as sumsal from emp;

select sum(sal+ifnull(comm,0)) as sumsal from emp;

每个月的补助总和:

select sum(ifnull(comm,0)) as sumcomm from emp;

或者

select sum(comm) as sumcomm from emp;

空值无需做处理,但是还是建议加上ifnull

39、动力节点_MySQL_039_数据处理函数_多行处理函数_avg求平均值函数

求工资平均值:

select avg(sal) as avgsalfrom emp;

selectavg(sal+ifnull(comm,0)) as avgsal from avg;

40、动力节点_MySQL_040_数据处理函数_多行处理函数_max和min求最大最小值函数

select max(sal) as maxsal from emp;

select min(sal) as minsal from emp;

41、动力节点_MySQL_041_数据处理函数_多行处理函数_count取得记录数函数

select count(*) from emp where comm is null;

count(*)表示多行记录

如果写成:

select count(comm) from emp where comm is null;

结果为0,因为count()会自动忽略空值,多行处理函数自动忽略空值

count(*)统计的是结果集的总条数,count(字段名)统计的是该字段值不为null的总条数

select count(comm) from emp where comm is not null;

等价于

select count(comm) from emp;

42、动力节点_MySQL_042_数据处理函数_distinct去除重复记录概述

distinct:将查询结果中的某一个字段的重复记录去除

用法:distinct 字段名或distinct字段名1,字段名2 …

distinct A 区处于字段名A相同的记录

distinct A,B 去除与字段名A和字段名B相同的记录

注意:DISTINCT只能出现在所有字段的最前面,后面接多个字段为多字段联合去重

43、动力节点_MySQL_043_数据处理函数_distinct去除重复记录_通过例子讲解

select distinct job from emp;

select count (distinct job) from emp;

select distinct deptno,job from emp;

44、动力节点_MySQL_044_数据处理函数_分组查询_01

group by

语句格式:

select

字段

from

表名

group by

字段;

例子:

select

job,max(sal) as maxsal

from

emp

group by

job;

查找每个职业中,最高的薪水

select job,sal from emp group by job;

这种语句,只取拿到的第一个记录

45、动力节点_MySQL_045_数据处理函数_分组查询_02

例子:

select

​ job,ename,max(sal) as maxsal

from

​ emp

group by

​ job;

查找每个职业中,最高的薪水,同时会取出每个职业的第一个名字(没有意义,SCOTT不一定是3000收入的人)

在ORACLE中会直接报错

//正确代码
SELECT e.ename,t.maxsal,t.job
  from
	   emp e
  join
	  (SELECT b.job,max(sal) as maxsal
	     FROM 
			    emp b
				GROUP BY
			   job	)t
	 on
	    e.job = t.JOB and e.SAL=t.maxSAL;

比较新得mysal版本也会报错

46、动力节点_MySQL_046_数据处理函数_分组查询_03

例子:

计算每个工作岗位的最高薪水,并且按照由低到高排序

先按照工作分组,然后排序

select job,max(sal) as maxsal from emp group by job order by maxsal;

例子:

计算每个部门的平均薪水

按照部门编号分组,对每一组薪水求平均值

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

select job,avg(sal) as avgsal from emp group by job;

例子:

计算出不同部门不同岗位的最高薪水

select deptno,job,max(sal)as maxsal from emp group by deptno,job;

用法和distinct类似,多个字段联合

例子:

找出每个工作岗位的最高薪水,除了MANAGER之外

select job,max(sal) as maxsal from emp where job not in (‘MANAGER’) group by job;

select job,max(sal) as maxsal from emp where job !=‘manager’ group by job;

47、动力节点_MySQL_047_数据处理函数_having数据过滤用法

例子:找出每个工作岗位的平均薪水,要求显示平均薪水大于2000的

select job,avg(sal) as avgsal from emp group by job;

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

having 必须和 groupby搭配使用,分组后再筛选

having写在 group by 之后 where写在group之前

//完成得select语句排序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kVe0MGFI-1600565587845)(E:\笔记\图片\1574565357307.png)]

 1、 from 将硬盘上的表文件加载到内存

2、 where 将符合条件的数据行摘取出来,生成一张临时表

3、 group by 根据列中的数据种类,将当前临时表划分成若干个新的临时表

4、 having 可以过滤掉group by生成的不符合条件的临时表

5、 select 对当前表进行整列读取

6、 order by 对select生成的临时表,进行重新排序,生成新的临时表

7、 limit 对最终生成的临时表的数据行进行截取

分表查询

笛卡尔积现象

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HLdhhbwt-1600565587846)(E:\笔记\图片\1574578768586.png)]

select ename,dname fromemp,dept;

这个语句没有加条件限制,因此结果是两个表的乘积

比如表A

1

2

3

表B

4

5

6

结果是

1 4

1 5

1 6

2 4

2 5

2 6

3 4

3 5

3 6

称为 笛卡尔积

52、动力节点_MySQL_052_跨表查询_分类介绍

跨表查询:

年代划分:

SQL92

SQL99

连接方式划分:

内连接

等值连接

非等值连接

自连接

外连接

左外连接

右外连接

全连接

53、动力节点_MySQL_053_跨表查询_根据年代分类_SQL92语法

例子:

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

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

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

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

这个语句的匹配次数依然是笛卡尔积

54、动力节点_MySQL_054_跨表查询_根据年代分类_SQL99语法

例子:

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

select e.ename,d.dname from emp as e join dept as d on e.deptno = d.deptno;

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

sleect

​ e.ename , d.dname

from

​ emp e

inner join

​ dept d

on

e.dept = d.dept;

where

​ 数据过滤条件 ;

55、动力节点_MySQL_055_跨表查询_根据连接方式分类_内连接_等值连接

例子:

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

select e.ename,d.dname from emp as e inner join dept as d on e.deptno = d.deptno;

56、动力节点_MySQL_056_跨表查询_根据连接方式分类_内连接_非等值连接

查询员工薪水对应的薪水等级

selecte.ename,e.sal,s.grade from emp as e inner join salgrade as s on e.sal between s.losal and s.hisal;

总共执行了14*5=70次

57、动力节点_MySQL_057_跨表查询_根据连接方式分类_内连接_自连接

例子:

查询员工所对应的领导名称,显示员工名称和领导名称

select a.ename as empename,b.ename as mgrename from emp a join emp b on a.mgr = b.empno;

这个语句相当于把emp表做了两次别名处理,一张表看成两张表

58、动力节点_MySQL_058_跨表查询_根据连接方式分类_外连接概述

A表和B表能够完全匹配的记录查询出来之外,将其中一张表的记录无条件的完全查询出来,对方表没有匹配的记录时,会自动模拟出null值与之匹配

注意:外连接的查询结果条数>=内连接的查询结果条数

59、动力节点_MySQL_059_跨表查询_根据连接方式分类_外连接_右外连接

包含右边表的全部行(不管左边表是否存在与它们匹配的行),以及左边表中全部匹配的行

例子:

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

等价于:省略了outer

select e.ename,d.dnamefrom emp as e right outer join dept as d on e.deptno = d.deptno;

60、动力节点_MySQL_060_跨表查询_根据连接方式分类_外连接_左外连接

包含左边表的全部行(不管右边表是否存在与它们匹配的行),以及右边表中全部匹配的行

例子:

select a.ename, b.ename as leadername from emp a left outer join emp b on a.mgr = b.empno;

这里KING是没有领导的 显示为NULL

了解:全连接

61、动力节点_MySQL_061_跨表查询_根据连接方式分类_通过哪此关键字区分内外连接

通过 right 或者 left 区分

例子:查询员工部门名称,领导名称和薪水等级

ename -> emp

dname -> dept

grade -> salgrade

select 

  d.dname,

  e.ename,

  b.ename as leadername,

  s.grade

from

      emp e 

join  

     dept d

on 

    e.deptno = d.deptno

join

     emp b

on

     e.mgr = b.empno

join

    salgrade s

on

e.sal between  s.losal and s.hisal;

63、动力节点_MySQL_063_子查询_where关键字后的嵌套查询

子查询:select语句嵌套select语句

注意:select子句可以出现在select/from/where关键字后面,例如

select… (select) … [很少时候用,了解即可]

from …(select) …

where …(select) …

例子:

找出薪水比公司平均薪水高的员工,要求显示员工名和薪水

错误语句:分组函数不能直接使用在where关键字后面

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

正确语句:

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

–多表联合查询
—当需要获取的数据分布在多张表中使用多表联合查询
—SQL92方式
—笛卡尔积:将多个表的数据进行一一对应,所得到结果为多表的笛卡尔积.
—结果数量为所有表数量的乘积
seelct * from emp,dept;
—等值链接
----先做表的笛卡尔积,然后筛选,筛选条件为等值筛选.
—查询员工的姓名,工作,薪资,部门名称
select * from emp,dept where dept.deptno=emp.deptno;—等值连接查询
—可以直接在select子句中使用字段直接获取数据,但是效率低,建议字段前加上表名
—注意:如果是公共字段必须声明表名
select * from emp e,dept d where d.deptno=e.deptno;
—不等值链接
----查询员工的姓名,工作,工资,工资等级
select * from emp e,salgrade s where e.sal>=s.losal and e.sal<= s.hisal;
—自连接
----相当于自己和自己做笛卡尔积
----查询员工姓名,工作,薪资,以及上级领导姓名
select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
—外链接
----左外链接
—查询员工姓名,工作,薪资,部门名称以及没有部门的员工信息
select * from emp e,dept d where e.deptno=d.deptno(+);
----右外链接
—查询员工姓名,工作,薪资,部门名称以及没有员工的部门
select * from emp e,dept d where e.deptno(+)=d.deptno;

64、动力节点_MySQL_064_子查询_from关键字后的嵌套查询

例子:找出每个部门的平均薪水,并且要求显示平均薪水的薪水等级

select e.deptno,avg(sal)as avgsal from emp e group by e.deptno;

把上面作为临时表t

select e.deptno,avg(sal)as avgsal from emp e group by e.deptno;

select t.avgsal,s.grade from t join salgarde s on t.avgsal between s.losal and s.hisal;

最终语句:

select e.deptno,avg(e.sal) as avgsal
from 
     emp e
group by
     e.deptno;
--------------------------------------

select  t.deptno,d.dname,t.avgsal,s.grade

from  
      (select e.deptno,avg(e.sal) as avgsal
            from 
                     emp e
             group by
                e.deptno) t
join 
      dept d
on 
   t.deptno = d.deptno
join 
    salgrade s
on 
    t.avgsal between s.losal and hisal;

65、动力节点_MySQL_065_UNION合并查询结果集

例子:查询出工作岗位是manager和salesman的员工

select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;

select ename,job from empwhere job in (‘MANAGER’,’SALESMAN’);

//如果是not in的话,括号里的值是and的关系

使用UNION

select ename,job from emp where job = ‘MANAGER’

union

select ename,job from emp where job = ‘SALESMAN’;

注意:

两个查询子句的字段要一样,ename/job

如果第二个ename改成empno

输出结果是一串数字,没有意义

66、动力节点_MySQL_066_limit用法概述

limit只在MySQL中有效,获取一个表前几行或者中间某几行数据

用法:

limit起始下标m,长度n

m从0开始,表示第一行记录

n表示从第m+1条开始,取N行记录

下标从0开始,但是长度是从1开始

67、动力节点_MySQL_067_limit实例讲解

例子:取得前5个员工信息

select * from emp limit0,5;

或者

select * from emp limit5;

例子:取薪水前3名的员工

select ename,sal from emporder by sal desc limit 3;

例子:取薪水排名第5到第8

select ename,sal from emporder by sal desc limit 4,4;

69、动力节点_MySQL_069_表_表的定义

表:数据库基本组成单元,行和列组成,行是记录,列是字段,字段包括:字段名称,类型,长度,约束。

70、动力节点_MySQL_070_表_创建表的语法

属于数据库定义语言:

create table 表名(

字段1 字段类型(长度) 字段约束,

字段2 字段类型(长度) 字段约束,

字段3 字段类型(长度) 字段约束,

字段N 字段类型(长度) 字段约束,

primary key(字段)

)engine=InnoDB default charset=utf8;

commit;

CREATE TABLE IF NOT EXISTS runoob_tbl(

runoob_idINT UNSIGNED AUTO_INCREMENT,

runoob_title VARCHAR(100) NOT NULL,

runoob_author VARCHAR(40) NOT NULL,

submission_date DATE,

PRIMARY KEY( runoob_id )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析:

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。

AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。

PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键,列间以逗号分隔。

ENGINE 设置存储引擎,CHARSET 设置编码。

double

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Iy9Kj03F-1600565587848)(E:\笔记\图片\1574678311011.png)]

72、动力节点_MySQL_072_表_实例讲解创建表及删除表

例子:创建学生表

create table t_student(

no int(4) not null,

name varchar(32) not null,

gender char(1) not null,

birth date,

email varchar(128),

primary key(no)

);

查看建表语句:

show create tablet_student;

删除表:

drop table t_student;

如果存在就删除表:

drop table if existst_student;

73、动力节点_MySQL_073_表_复制表语法

create table emp_bak as select * from emp;

查看创建表语句:

show create table emp_bak;

74、动力节点_MySQL_074_表_数据操作语言DML语句_insert语法

DML:insert/update/delete

insert into 表名(字段1,字段2,…,字段N) values(字段1的值,字段2 的值,…,字段N的值);

插入日期到 date 类型的字段时 ,要用 str_to_date 如果插入的日期格式是mysql默认的 格式‘1999-10-01’ 则可以省略str_to_str

76、动力节点_MySQL_076_表_数据操作语言DML语句_update语法

update 表名 set 字段名称 = 字段值,字段名称 = 字段值 where 限制条件;

必须加限制条件!!否则就是全表修改

update t_student set birth =str_to_date(‘1951-10-10’,’%Y-%m-%d’), email = ‘lilei@126.com’ where name =‘lilei’;

77、动力节点_MySQL_077_表_数据操作语言DML语句_delete语法

语法:

delete from t_student; //删除所有

delete from t_studentwhere no = 4; //加限制条件

注意:这属于物理删除,无法恢复

78、动力节点_MySQL_078_表_设置表中字段默认值

关键字:default

字段名 字段类型 default +值

80、动力节点_MySQL_080_表_数据操作语言DML语句_快速向表中插入数据

insert

insert into emp_bak select * from emp where job = ‘MANAGER’;

81、动力节点_MySQL_081_表_修改表的结构 DDL

ALTER关键字

1、新增:ALTER TABLE 表名 ADD 字段名 字段类型(长度);

2、修改:ALTER TABLE 表名 MODIFY 字段名 字段类型(长度);

3、删除:ALTER TABLE 表名 DROP 字段名;

drop table if existst_student;

create table t_student(

no int(10),

name varchar(32)

);

alter table t_student addemail varchar(128);

alter table t_studentmodify no int(8);

alter table t_studentdrop email;

不常用的修改字段名称方法:

alter table t_studentchange name username varchar(32);

82、动力节点_MySQL_082_约束的概述

目的:保证表中数据的完整和有效

英语:constraint

定义:对表中数据的限制条件

83、动力节点_MySQL_083_约束_非空约束

not null

表明字段必须由具体数据,不能为NULL

84、动力节点_MySQL_084_约束_唯一性约束_列级写法

unique

表明字段必须不能重复,保持唯一

列级约束:写在字段后面,保证邮箱唯一性

create table t_student(

no int(10),

name varchar(32) not null,

email varchar(128) unique

);

85、动力节点_MySQL_085_约束_唯一性约束_表级写法

表级约束:

create table t_student(

no int(10),

name varchar(32) notnull,

email varchar(128),

unique(email)

);

效果和84一样。

表级支持多个字段联合约束

create table t_student(

no int(10),

name varchar(32) notnull,

email varchar(128),

unique(name,email)

);

只有当name和email都一样的时候,才是重复值,只要有一个值不一样,就认为不是重复记录

约束起别名:

create table t_student(

no int(10),

name varchar(32) notnull,

email varchar(128),

constraint t_user_name_email_unique unique(name,email)

);

查询约束:

select CONSTRAINT_NAME from TABLE_CONSTRAINTS where table_name =‘t_user’;

86、动力节点_MySQL_086_约束_非空约束与唯一性约束联合使用

例子:手机号,不为空,不能重复

not null unique

87、动力节点_MySQL_087_约束_主键约束概述及相关概念

primary key 简称PK

例子:身份证

字段为:ID/NAME/GENDER/AGE

ID就是身份证号,作为主键约束,不能重复

三个术语:

主键约束、主键字段、主键值

表中某个字段添加主键约束之后,该字段成为主键字段,主键字段中出现的每一个数据都成为主键值。

88、动力节点_MySQL_088_约束_主键约束的作用

1、添加了主键 primary key的字段“不能重复也不能为空”,而且会自动添加“索引-index”,提高检索效率。

2、一张表必须有主键,否则这张表就是无效的(数据库设计第一范式),主键值是当行数据的唯一标识,就是表中两行数据完全相同,但是由于主键不同,也可以认为是两行完全不同的数据。

89、动力节点_MySQL_089_约束_主键约束_根据个数分类_单一主键和复合主键

单一主键:给一个字段添加主键约束;

列级: id int(10) primary key

表级: 在创建数据库表的时候,最后一个加primary key(id)

上面两种写法效果一样

复合主键:

constraint t_user_id_name_pk primarykey(id,name)

只有当id和name都一致的时候,才认为是重复的数据

无论是单一主键还是复合主键,一张表中有且只能有一个主键约束

90、动力节点_MySQL_090_约束_主键约束_根据性质分类_自然主键和业务主键

按照业务性质分类:

自然主键:主键值是一个自然数,与业务没有任何关系

业务主键:主键值和表中业务紧密相关,如果业务发生变化,则主键也会受到影响,所以业务主键使用较少,大多数情况下使用自然主键,比如身份证号位数扩展

91、动力节点_MySQL_091_increment函数_自动生成主键值

auto_increment

自增数字auto_increment,用来自动生成主键值,是MySQL独有的函数,默认从1开始,步进1递增

例子:

id int(4) primary keyauto_increment

使用insert的时候就不需要填写id的值了

93、动力节点_MySQL_093_约束_外键约束概述及相关概念

foreign key简称FK

外键约束,外键字段,外键值

给某个字段添加外键约束之后,这个字段就是外键字段,字段中的数据就是外键值。

单一外键:给一个字段添加外键约束

复合外键:给多个字段联合添加外键

同一张表中可以有多个外键存在

94、动力节点_MySQL_094_约束_外键约束_根据个数分类_单一外键和复合外键

95、动力节点_MySQL_095_约束_外键约束_实例创建外键约束

需求:设计数据库表用来存储学生和班级信息(给出两种方案)

需求分析:

学生表t_student包含:sno,sname,classno,cname

学生和班级的关系,一个班级有多个学生,一个学生只能属于一个班级,属于一对多关系

第一种解决方案:

把学生和班级信息都存储到一张表中

问题:高三一班重复出现,数据冗余

第二种解决方案:

创建1张学生表,1张班级表

 create table c_class(
    cno int(4) primary key,
    cname  varchar(32)
 );
 //先创建父表,在创建子表
create table t_student (
   sno int(4) primray key auto_increment,
   sname varchar(32),
   classno int(4),
   constraint t_student_classno_fk foreign key(clsaano) references t_class(cno)
);
 

为了保证t_student表中的cno字段的数据必须来之t_class中的cno,需要给t_student的cno字段添加外键约束,cno成为外键字段,100、200、300就是外键值,cno此处为单一外键

完整语句:

注意:

1、 外键字段可以为NULL,空外键值为孤儿数据

2、 被引用的字段必须unique约束(就是父表的主键)

3、 外键引用之后,就可以区分父表和子表,t_class为父表,t_student为子表

​ 创建表时先创建父表,插入数据先插入父表数据

4删除表时,要先删除子表,才可以删除父表;

注意:MySQL中没有提供修改外键约束的语法,如果要修改外键约束 ,只能删除外键约束再创建

98、动力节点_MySQL_098_约束_外键约束_级联更新与级联删除概述

用法:添加级联更新与级联删除的时候,需要在外键约束后添加关键字

注意:级联更新与极限删除操作谨慎使用,因为级联操作会导致数据改变或者删除

99、动力节点_MySQL_099_约束_外键约束_级联更新与级联删除_级联删除的操作

级联删除:on delete cascade

先删除外键约束:

ALTER TABLE 表名 DROP FOREIGN KEY 外键字段;

alter table t_studentdrop foreign key t_student_classno_fk;

添加外键约束:

alter table t_student add constraint t_student_classno_fk foreign key(classno) references t_class(cno) on delete cascade;

100、动力节点_MySQL_100_约束_外键约束_级联更新与级联删除_级联更新的操作

级联删除:on update cascade

先删除外键约束:

ALTER TABLE 表名 DROP FOREIGN KEY 外键字段;

alter table t_studentdrop foreign key t_student_classno_fk;

添加外键约束:

alter table t_student addconstraint t_student_classno_fk foreign key(classno) references t_class(cno) on update cascade;

外键约束 on delete on update

MYSQL:外键约束(On Delete和On Update)的使用
外键约束(On Delete和On Update)都有Restrict,No Action, Cascade,Set Null属性。

外键约束1–ON DELETE
A.restrict(约束):当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除。
B.no action:意思同restrict.即如果存在从数据,不允许删除主数据。
C.cascade(级联):当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则也删除外键在子表(即包含外键的表)中的记录。
D.set null:当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)

外键约束2–ON UPDATE
A.restrict(约束):当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许更新。
B.no action:意思同restrict.
C.cascade(级联):当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则也更新外键在子表(即包含外键的表)中的记录。
D.set null:当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)。

注:NO ACTION和RESTRICT的区别:只有在及个别的情况下会导致区别,前者是在其他约束的动作之后执行,后者具有最高的优先权执行。

例子

alter table epidemics add constraint FK_Reference_2 foreign key (user_id)
references user (user_id) on delete restrict on update restrict

101、动力节点_MySQL_101_存储引擎的概述

存储引擎是MySQL特有,其他数据库没有

本质:

1、 通过采用不同的技术将数据存储在文件或内存中

2、 每一种技术都有不同的存储机制,提供不同的功能,具备不同的能力

3、 选用不同的技术,可以获得额外的速度和功能,改善应用

查看存储引擎命令

show engines\G;

一共9种,默认是InnoDB

102、动力节点_MySQL_102_存储引擎_存储引擎的基本操作

1、创建表时,可以使用ENGINES=InnoDB指定引擎类型

create tabletable_name(

no int(2)

)engine =InnoDB;

2、如果创建表时没有指定存储引擎,会使用默认的存储引擎

3、默认的存储引擎可以在安装目录的my.ini中配置default-storage-engin指定

4、修改表的存储引擎:altertable table_name engine = new_engine_name;

5、查看表使用的存储引擎

show createtable emp\G;

或者

show tablestatus like ‘emp’\G;

103、动力节点_MySQL_103_存储引擎_常用的存储引擎_MyISAM

1、是MySQL数据库最常用的

2、管理的表具备以下特性

   a)格式文件 存储表的结构 mytable.frm

b) 数据文件 存储表的数据mytable.MYD

c) 索引文件 存储表的索引mytable.MYI

3、可压缩、只读表,节省空间

104、动力节点_MySQL_104_存储引擎_常用的存储引擎_InnoDB

1、是MySQL默认的存储引擎

2、管理的表具备以下特性

a) 每个InnoDB表在数据库目录中以.frm格式文件表示

b) InnoDB表空间tablespace被用于存储表的内容

c) 提供一组用来记录事务性活动的日志文件

d) 用commit/savepoint/roolback支持事务处理

e) 提供全部ACID兼容

f)在MySQL服务器崩溃后提供自动回复

g)多版本(MVCC)和行级锁定

h)支持外键及引用的完整性,包括级联更新和删除

105、动力节点_MySQL_105_存储引擎_常用的存储引擎_MEMORY

1、 数据存储在内存中,且行的长度固定,因此非常快

2、 管理的表具备以下特性:

   a)在数据库目录中,每个表以.frm格式文件表示

b) 表数据及索引被存储在内存中

c) 表级锁机制

d) 字段属性不能包含TEXT或者BLOB字段

3、 旧名HEAP引擎

106、动力节点_MySQL_106_存储引擎_如何选择合适的存储引擎

1、MyISAM表适合于大量数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表

2、如果查询中包含较多的数据更新操作,应该使用InnoDB,其行级锁机制和多版本的支持为数据读取和更新的混合提供了良好的并发机制(由事务控制)

3、使用MEMORY存储引擎存储非永久需要的数据,或者是能够从基于磁盘的表中重新生成测数据。

107、动力节点_MySQL_107_索引的概述

index

相当于一本字典目录,提高程序的检索/查询效率,表中的每一个字段都可以添加索引

主键自动添加索引,能通过主键查询的尽量通过主键查询,效率较高

索引也是存储在磁盘文件中

1、 索引和表相同,是一个对象,表示存储在硬盘文件中,索引是表的一部分,因此也存放在硬盘文件中

108、动力节点_MySQL_108_索引_索引的检索方式及什么情况下创建索引

MySQL数据库有2中检索方式:

1、 全表扫描(效率较低)

例子:查询ename=’KING’

select * from emp where ename=’KING’;

如果ename没有添加索引,那么通过ename过滤数据的时候,ename字段会全表扫描

2、 通过索引检索(提高查询效率)

创建索引的情况:

1、 该字段的数据量庞大

2、 该字段很少使用DML操作(索引需要维护,DML操作太多的时候,影响检索效率)

3、 该字段经常出现在筛选条件where中

实际开发中根据项目需求或客户需求综合调整

109、动力节点_MySQL_109_索引_索引的应用

1、创建索引

语法:

create index 索引名 。on 表名(列名);

create unique index 索引名 on 表名( 列名);

注:添加unique表示在该表中的该列添加一个唯一性约束

例如:create index dept_dname_index on dept(dname);

2、查看索引

   show index from 表名;

3、删除索引

   drop index 索引名on 表名;

110、动力节点_MySQL_110_视图概述及应用

view

视图在数据库管理系统中也是一个对象,以文件形式存在

视图也对应了一个查询结果,只是从不同的角度查看数据

语法:

create view 视图名称 as查询语句;

例如:create view myview as select * from emp;

show tables;

可以查看到新建的myview视图

视图底层也是表,并且如果原表的数据发生改变,myview的数据也会改变

查看视图的语句

show create view myview;

删除:drop view if exists myview;

111、动力节点_MySQL_111_视图的作用_隐藏表的实现细节

隐藏表的实现细节

例子

create view myview as select empno as a, enameas b from emp;

select * from myview;

如果限制语句 show create view myview; 就能实现对表字段的隐藏

112、动力节点_MySQL_112_视图的作用_提高检索效率

提高检索效率

例子

create view myview2 as

select e.ename,d.dname from emp e join deptd on e.deptno = d.deptno;

对于经常使用的查询语句 ,可以做成试视图 ,以后直接查询视图 slect * from 视图名;

113、动力节点_MySQL_113_DBA简单的介绍

1、新建用户

createuser username identified by ‘password’;

例子:

create user p361 identified by ‘123’;

登录后只能看见information_schema一个库

2、授权

grant all privileges on dbname.tbname to ‘username’ @ ’login ip’ identified by ‘password’ with grant option

dbname = 数据库,*表示所有数据库

tbname = 表,*表示所有表

login ip = 登录IP,%表示任意IP

password = 登录密码,空表示不需要密码

with grant option:表示这个用户可以授权其他用户

细粒度授权

(1) root登录mysql

(2) grant select,insert,update,delete on . to p361 @localhost identified by ‘123’;

(3) localhost改为%表示可以再任何机器上登录mysql

3、回收授权

4、导入导出

114、动力节点_MySQL_114_数据库设计三范式_第一范式

主键、字段不能再分

要求有主键,数据库中不能出现重复记录,每一个字段是原子性不能再分

实例:不符合第一范式:

上面存在的问题:

数据存在重复记录,数据不唯一,没有主键

联系方式可以再分为邮箱和手机号,不是原子性

修改方案:

结论:

1、 每一行必须唯一,也就是每个表必须有主键,这是设计数据库的最基本要求

2、 主键主要通常采用数值型或者定长字符串表示

3、 关于列不可再分,应该根据具体的情况来决定,如联系方式,为了开发商的遍历有坑呢就采用一个字段,不分为邮箱和手机号

115、动力节点_MySQL_115_数据库设计三范式_第二范式

非主键字段完全依赖主键

第二范式在第一范式的基础上,要求数据库中所有非主键字段完全依赖主键(严格意义上说,尽量不要使用联合主键)

示例1,数据仍然可能重复

示例2,确定主键,学生编号,教师编码,出现冗余

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s7Ttn8ns-1600565587850)(E:\笔记\图片\1574827334377.png)]

分析

1、 上面例子虽然确定了主键,但是表会出现大量的冗余,主要涉及到的冗余字段是学生姓名 和教师姓名

2、 出现冗余的原因是,学生姓名部分依赖了主键的一个字段学生编号,而没有依赖教师编号这个主键

教师姓名部分依赖了主键的一个字段教师编号,而没有依赖学生编号这就是第二范式部分依赖

解决方案:

老师和学生是多对多的关系

学生信息表:学生编号PK,学生姓名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-38SeDmI8-1600565587851)(E:\笔记\图片\1574828036325.png)]

教师信息表:教师编号PK,教师姓名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7uRbN7pK-1600565587852)(E:\笔记\图片\1574828059657.png)]

多对多的关系,需要有1张关系表:

教师-学生关系表:

学生编号PK fk(学生表的学生编号),教师编号PK fk(教师表的教师编号)

结论:典型的多对多关系 (一个学生有多个不同科目的老师 一个老师对应多个学生)

116、动力节点_MySQL_116_数据库设计三范式_第三范式

简历在第二范式基础上,要求非主键字段不能产生传递依赖与主键字段

例子1,学生信息表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mPdra1tR-1600565587852)(E:\笔记\图片\1574831674331.png)]

班级名称存在冗余,因为班级名称没有直接依赖主键(学生编号)

班级名称依赖于班级编号,班级编号依赖于学生编号(学生调班之后班级编号就变了),这就是传递依赖

一对多的设计:在多的一方添加外键

解决方案:

学生信息表:学生编号PK,学生姓名,班级编号FK

班级信息表:班级编号PK,班级名称

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8mcuB8kn-1600565587853)(E:\笔记\图片\1574831704701.png)]

117、动力节点_MySQL_117_数据库设计_数据库表关系_一对一关系如何设计

三范式总结

1、 一对一 一个丈夫只能有一个妻子

方案一

wifeno 设置唯一性约束 并且是 引用wno的外键 (外键唯一)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Tu5JTafj-1600565587854)(E:\笔记\图片\1574832757911.png)]

方案2:分两张表存储,共享主键

t_husband t_wife两张表

t_husband

hno(PK),hname

t_wife

wno(PK,同时也是外键FK,引用t_husband的主键),wname

118、动力节点_MySQL_118_数据库设计_实际开发中如何做

1、尽量遵循三大范式

2、根据实际需求进行取舍,有时候冗余换速度,最终目的,满足需求

119、动力节点_MySQL_119_事务_什么是事务

transaction

  1. 一个最小的不可再分的工作单元

  2. 通常一个事务对应一个完整的业务(如:银行转账业务)

  3. 一个完整的业务需要批量的DML语句共同完成

  4. 事务只和DML语句有关系

  5. 以上所描述的额批量DML语句,数量和业务逻辑相关

例子:银行转账

示例,账户转账

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bThBVqGy-1600565587855)(E:\笔记\图片\1574836676267.png)]

分析:

  1. 上面两条DML语句必须都执行成功,要么就不执行

  2. 第一条DML执行成功之后,不能修改数据库,而只是记录操作,这个记录是在内存中完成的

  3. 第二条DML也成功之后,底层数据库文件的数据此时完成同步

  4. 如果第二条执行失败,会清空所有的历史操作记录,数据库不变

结论:上面的功能要借助事务完成

120、动力节点_MySQL_120_事务_事务的四个特性

ACID

原子性(atomicity),事务是最小单位,不可再分,所有语句,要么同时成功,要么同时失败

一致性(consistency)一致性:事务执行前和执行后必须处于一致性状态,

例:用户A和用户B的前加起来一共是5000; 无论AB用户之间是如何相互转换的,事务结束后两个用户的钱加起来还是5000,这就是事务的一致性。

隔离性(isolation),一个事务不会影响其他事务

持久性(durability),事务完成之后,对数据库做的修改会持久的保存在数据库中,不会被回滚

1、动力节点_MySQL_121_事务_事务的相关概念

开启事务:start transaction

结束事务:end transaction

提交事务:commit transaction

回滚事务:rollback transaction

重点:

MySQL默认是自动提交的

操作:

手动开始事务

start transaction;

DML

commit;

事务操作得两条 语句

commit ; 提交 成功的结束 ,将所有DML语句操作记录和底层硬盘文件中数据进行一次同步

rollback; 回滚 失败的结束 ,将所有DML语句操作记录全部清空

设置不要自动提交:

show variables like ‘%commit%’;

set autocommit = OFF;

122、动力节点_MySQL_122_事务_事务隔离级别概述

四个隔离级别

read uncommitted 读未提交,级别最低

事务A和事务B,事务A未提交的数据,事务B可以读取

这里读取到的数据可以叫做脏数据,或者,脏读 Dirty Read

这种级别一般只是理论上存在,数据库默认的隔离级别都高于这个

read committed 读已提交

事务A每一次提交,事务B读取到的数据库都会变化

会导致:B一直都在读取变化的数据库

Oracle数据库管理系统的默认隔离级别为可重复读

repeatable read 可重复读(MySQL的默认级别)

事务A每次提交都不影响事务B读取的数据库内容,也就是事务B读取到的数据库一直都不变化

会导致:B感知不到数据库变化,要等事务B结束后,再事务b窗口可以查到数据库的变化

serializable 串行化

事务排队,一个完成后才能继续下一个

这种级别很少使用,吞吐量小,用户体验不好

123、动力节点_MySQL_123_事务_演示事务隔离级别

包括会话级别,全局级别

查看当前会话级隔离级别

select @@tx_isolation;

select @@session.tx_isolation;

全局隔离级别

select @@global.tx_isolation;

设置服务器缺省隔离级别

第一种方法:修改my.ini配置文件

第二种方法,通过命令方式设置事务隔离级别

set transaction isolation levelisolation-level;

isolation-level可选4种隔离级别

设置作用范围:

session

global

set global transaction isolation level READCOMMITED; //只对当前会话有效

set session transaction isolation levelREAD COMMITED; //全局有效

34道mysql经典试题

在这里插入图片描述

在这里插入图片描述

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

select b.ename,t.deptno,t.deptmaxsal
from
     emp b
join 
     (select e.deptno,max(e.sal)as deptmaxsal from emp e group by deptno) t
on    
   b.sal = t.deptmaxsal;

2哪些员工的薪水在部门工资平均水平之上

select b.ename,b.deptno,b.sal
from 
    emp b 
join 
    (select 
       e.deptno,avg(sal) as avgsal  
    from 
       emp e
    group by 
      e.deptno) t
on 
    b.deptno = t.deptno
where 
    b.sal > t.avgsal;

3取得部门中(所有人的)平均薪水等级

3.1取得部门中所有人的平均薪水的等级
select 
    t.deptno,t.deptavgsal,s.grade
 from(select 
           e.deptno,avg(e.sal) as deptavgsal 
      from 
            emp e 
      group by 
           e.deptno)t
join 
    salgrade s
where 
    t.deptavgsal between s.losal and hisal;




3.2取得部门中所有人的平均的薪水等级
第一步求出每个人的薪水等级

select  
     e.deptno,e.ename,s.grade
from 
    emp e 
join 
    salgrade s
on
    e.sal between  s.losal and s.hisal
order by 
    e.deptno;

 求出部门的人的平均的薪水等级
select 
       t.deptno,avg(t.grade) as avggrade
from
     (select  
          e.deptno,e.ename,s.grade
      from 
         emp e 
      join 
         salgrade s
      on
         e.sal between  s.losal and s.hisal
      )t
group by 
      t.deptno;

4 不准用max函数 ,取得最高薪水(给出两种解决方案)

1 
 select sal from emp order by sal desc limit 1;
 
2 
  //首先取出表中除去最高薪水的工资
  select distinct a.sal 
  from 
      emp a 
  join 
      emp b
   on 
     a.sal <b.sal;
 
 //用not in取出最高工资
 select sal from emp where sal not in(  select distinct a.sal 
                                        from 
                                             emp a 
                                        join 
                                             emp b
                                        on 
                                             a.sal <b.sal);

5取得平均薪水最高的部门的部门编号



select 
        e.deptno,avg(sal) as avgsal
from 
      emp e
group by 
       e.deptno
having 
       avgsal =(select   
                     max(t.avgsal) as maxavgsal
                from 
                     (select 
                            e.deptno,avg(e.sal) as avgsal
                      from 
                            emp e
                      group by 
                            e.deptno) t);
                     
6取出平均薪水最高的部门的名称
select 
        e.deptno,avg(sal) as avgsal,d.dname
from 
      emp e
join 
    dept d
on 
     d.deptno=e.deptno
group by 
       e.deptno,d.dname
having 
       avgsal =(select   
                     max(t.avgsal) as maxavgsal
                from 
                     (select 
                            e.deptno,avg(e.sal) as avgsal
                      from 
                            emp e
                      group by 
                            e.deptno) t);
 

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

select t.deptno,t.dname,t.avgsal,s.grade
from
(select e.deptno,d.dname,avg(e.sal) as avgsal 
from 
   emp e 
join 
   dept d
on 
   d.deptno =e.deptno
group by 
    e.deptno,d.dname)t

join
  salgrade s
on 
  t.avgsal between losal and hisal
where
   s.grade =( select min(t.grade) as mingrade from (select t.deptno,t.dname,t.avgsal,s.grade
from
(select e.deptno,d.dname,avg(e.sal) as avgsal 
from 
   emp e 
join 
   dept d
on 
   d.deptno =e.deptno
group by 
    e.deptno,d.dname)t

join
  salgrade s
on 
  t.avgsal between losal and hisal) t);
 
 
 //改进
 
			SELECT t.deptno,t.avgsal,s.GRADE
			     from 
					salgrade s
				 JOIN
			 (SELECT 
						e.deptno ,avg(e.sal) as avgsal 
			  FROM
						emp e 
				GROUP BY
				    e.DEPTNO)t
					on 
					 t.avgsal BETWEEN s.LOSAL and s.HISAL
					 ORDER BY
					     GRADE asc
							 LIMIT 1;
//查询最低的部门等级
select min(t.grade) as mingrade from (select t.deptno,t.dname,t.avgsal,s.grade
from
(select e.deptno,d.dname,avg(e.sal) as avgsal 
from 
   emp e 
join 
   dept d
on 
   d.deptno =e.deptno
group by 
    e.deptno,d.dname)t

join
  salgrade s
on 
  t.avgsal between losal and hisal) t;

9取得比普通员工(员工代买没有在mgr上出现的)的最高薪水还要高的管理人员姓名

 //1先选出普通员工信息
 //in 会自动忽略控制
 //not in 不会自动忽略控制
  select * from emp where empno not in (select mgr from emp where mgr is not null );
 
 //2、找出普通员工最高薪水
  select max(sal)as maxsal from emp where empno not in (select mgr from emp where mgr is not null ); 
 
 3 取得工资高于普通员工最高工资的姓名
 select 
       e.ename 
 from 
          emp e 
 where 
       e.sal>=(select 
                  max(sal)as maxsal 
               from
                   emp 
              where 
              empno not in (select mgr from emp where mgr is not null ));

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

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

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

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

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

select * from emp e order by e .hiredate desc limit 0,5;

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

//1先取得所有员工的薪水等级
select e.ename,s.grade
from 
    emp e 
join 
    salgrade s 
on 
   e.sal between losal and hisal;

// 把上面当中临时表 ,计算每个等级有多少员工
 select 
        t.grade,count(t.ename) as total
 from (select 
           e.ename,s.grade
       from 
           emp e 
        join 
           salgrade s 
         on 
          e.sal between losal and hisal)t
  group by t.grade ;

13题

有3个表S(学生表) ,C(课程表) , SC(学生选课表)
S(sno,sname) 学号 姓名
C(cno , cname,cteacher) 课号 课名 教师
SC(sno ,cno, grade)  学号,课号,成绩

//建表
create table s(

       sno int(4) primary key unique auto_increment,

       sname varchar(32)

       );
 insert into s(sname)values('zhangsan');

insert into s(sname)values('lisi');

insert into s(sname)values('wangwu');

insert into s(sname)values('zhaoliu');
 -----------------------------------------------------------------
 create table c(

       cno int(4) primary key unique auto_increment,

       cname varchar(32),

       cteacher varchar(32)

       );
insert into c(cname,cteacher) values('java','wu');

insert into c(cname,cteacher) values('c++','wang');

insert into c(cname,cteacher) values('c#','zhang');

insert into c(cname,cteacher) values('mysql','guo');

insert into c(cname,cteacher) values('oracle','liming');
--------------------------------------------------------------------- 
 create table sc(
         sno int(4),
         cno int(4),
         scgrade double(3,1),
         constraint sc_sno_cno_pk primary key (sno,cno),
         constraint sc_sno_fk foreign key(sno) references s(sno),
         constraint sc_cno_fk foreign key(cno) references c(cno)
         ); 

insert into sc(sno,cno,scgrade) values(1,1,30);

insert into sc(sno,cno,scgrade) values(1,2,50);

insert into sc(sno,cno,scgrade) values(1,3,80);

insert into sc(sno,cno,scgrade) values(1,4,90);



 

insert into sc(sno,cno,scgrade) values(2,1,30);



insert into sc(sno,cno,scgrade) values(2,3,80);

insert into sc(sno,cno,scgrade) values(2,4,60);

insert into sc(sno,cno,scgrade) values(2,5,60);

 

insert into sc(sno,cno,scgrade) values(3,1,30);

insert into sc(sno,cno,scgrade) values(3,2,50);

insert into sc(sno,cno,scgrade) values(3,4,70);



 

insert into sc(sno,cno,scgrade) values(4,1,30);

insert into sc(sno,cno,scgrade) values(4,2,50);

insert into sc(sno,cno,scgrade) values(4,3,80);

insert into sc(sno,cno,scgrade) values(4,5,70);

13.1找出所有没有选过liming 老师的学生姓名

select s.sname 
 from 
     s
 where s.sno not in ( select  
                              sc.sno 
                       from 
                               sc 
                        where
                              cno = (select cno from c where cteacher ='liming'));

13.2列出两门以上(含两名) 不及格学生姓名及平均成绩

select t.sno,t.failtotal,s.sname,g.avggrade

from
(select sno,count(*) as failtotal 
 from 
    sc
 where 
    scgrade<60 
 group by 
    sc.sno
  having 
     failtotal>=2)t
join 
   s
on 
  s.sno=t.sno
join 
    (select
           sc.sno,avg(sc.scgrade) as avggrade
      from 
          sc
       group by 
       sno
     )g
on
   t.sno=g.sno;
 
  

!!13.3 即学过一号课程,也学过二号课程的学生

 select t.sno,s.sname
from
 (select sno from sc where cno = 1 and sc.sno in(select sno from sc where cno = 2))t 
join 
   s
on 
 s.sno = t.sno;

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

select e.ename,b.ename as leadername 
 from 
    emp e 
 left join 
    emp b
 on
  e.mgr = b.empno;
    

15 例出受雇日期早于其直接上级的所有员工编号,姓名

select e.empno,e.ename,e.hiredate
from 
 emp e
join 
  emp b
 on 
   e.mgr = b.empno
  where 
    e.hiredate< b.hiredate;

16列出部门名称和这些部门的员工信息,同时列出没有与员工的部门

select 
      d.dname,
      e.*
from 
    emp e
right join 
    dept d
on 
   e.deptno = d.deptno;

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

 select t.deptno,d.dname,t.total
 from 
(select e.deptno,count(*) as total
 from 
     emp e
 group by 
 e.deptno)t
join 
   dept d
on 
   d.deptno=t.deptno
where
   t.total >=5;

18列出薪水比 smith多的所有员工信息

select * from emp e where e.sal>(select sal from emp where ename='smith'); 

19 列出 所有‘clerk’(办事员职业) 的性名,及其部门名称,部门人数

select e.ename,e.deptno,d.dname,t.total 
  from 
     emp e
  join 
     (select deptno,count(*) as total
       from 
          emp 
        group by 
         deptno)t
   on
   e.deptno = t.deptno
   join 
       dept d
   on 
     d.deptno=t.deptno 
   where
       e.job = 'clerk';

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

select e.job,min(e.sal)as minsal,count(e.ename) as total
from 
 emp e
group by 
 job
having
 minsal > 1500;

21列出在部门‘sales’(销售部)工作的员工的姓名,假定不知道销售部门的部门编号

select e.ename
from 
   emp e
join
(select d.deptno 
from
dept d
where 
   dname = 'sales')t
on
  e.deptno =t.deptno;
  
  简化:
  select e.ename from emp e where e.deptno=(select d.deptno from dept d where d.dname='sales');

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

select e.deptno,e.ename,d.dname,b.ename as leadername,e.sal 
from 
    emp e
 join 
    dept d
 on 
   e.deptno = d.deptno
 left join 
    emp b
 on 
    e.mgr=b.empno
 join 
    salgrade s
 on
   e.sal between s.losal and s.hisal
where
   e.sal>(select avg(sal)as avgsal from emp);

23列出与‘scott’从事相同工作的所有员工及部门姓名

select e.ename,d.dname
from 
   emp e
join 
   dept d
on 
   e.deptno= d.deptno
where 
    e.job=(select job from emp where ename='scott')  
having 
   e.ename != 'scott';

24列出薪水等于部门30中员工的薪 的其他员工姓名和其薪水(不包括30 部门的员工)

select e.ename,e.sal 
 from 
    emp e
 where 
   e.sal in (select sal from emp where deptno = 30) and e.deptno!=30;
   

25列出薪水高于在部门30工作的所有员工的薪水,员工姓名,部门名称

select e.ename,e.sal,d.dname
from 
  emp e
join 
   dept d
on
  e.deptno = d.deptno
where
   e.sal > (select max(sal) from emp where deptno = 30 group by deptno);

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

to_days(日期类型) 返回到 公元0年到输入日期的天数

获得数据库的系统当前时间函数是 now()

员工工资天数

to_days(now()) - to_days(hiredate)

select e.deptno,count(*) as total,avg(e.sal) as avgsal,
avg(( to_days(now()) - to_days(e.hiredate)  )/365) as avgserviceyear
from emp e group by deptno; 

27列出所有员工的姓名、部门名称和工资(有一个部门没有员工)

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

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

select d.deptno,count(e.ename) as depttotal,d.dname,d.loc 
from 
   emp e
right join 
  dept d
on 
  e.deptno =d.deptno
group by
   d.deptno,d.dname,d.loc;

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

select t.job,t.minsal,e.ename
 from 
     emp e
 join 
 (select e.job, min(e.sal) as minsal
 from 
    emp e
 group by 
   e.job)t
 on 
  e.job = t.job
 where 
    e.sal = t.minsal;
+-----------+---------+--------+
| job       | minsal  | ename  |
+-----------+---------+--------+
| CLERK     |  800.00 | SMITH  |
| SALESMAN  | 1250.00 | WARD   |
| SALESMAN  | 1250.00 | MARTIN |
| MANAGER   | 2450.00 | CLARK  |
| ANALYST   | 3000.00 | SCOTT  |
| PRESIDENT | 5000.00 | KING   |
| ANALYST   | 3000.00 | FORD   |
+-----------+---------+--------+

30列出各个部门manager的最低薪水

select e.deptno,min(e.sal) as minmanagersal
from 
   emp e
where 
    e.job = 'manager'
group by 
   e.deptno;

31列出所有员工年工资,排序

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal desc;

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

select e.ename,b.ename as leadername
  from 
 emp e
 join 
   emp b
 on 
   e.mgr = b.empno
 where 
    b.sal >3000;

33求部门名称中带‘s’字符的部门员工的工资合计 和部门人数

select d.dname,sum(e.sal) as sumsal,count(e.ename)as total
from 
   emp e
join 
   dept d
on 
  e.deptno = d.deptno
where
   d.dname like '%s%'
group by
   d.dname;

34给在职日期超过30年的员工加薪水

 select  ((to_days(now()) - to_days(hiredate))/365) as year ,e.sal *1.1
  from 
    emp e
  having
   year>30;
   
   //where 只能根据源表emp e中的字段进行过滤
   //having  可以根据信新表进行条件筛选

//update 方法 真正修改


 update emp_bak set sal = sal*1.1 where (to_days(now()) - to_days(hiredate))/365>30;

//sql注入


 select * from t_user where username='dddd' and password='111' or 'aa'='aa';
 
 上式等于
 select *from t_user;

’从事相同工作的所有员工及部门姓名

select e.ename,d.dname
from 
   emp e
join 
   dept d
on 
   e.deptno= d.deptno
where 
    e.job=(select job from emp where ename='scott')  
having 
   e.ename != 'scott';

24列出薪水等于部门30中员工的薪 的其他员工姓名和其薪水(不包括30 部门的员工)

select e.ename,e.sal 
 from 
    emp e
 where 
   e.sal in (select sal from emp where deptno = 30) and e.deptno!=30;
   

25列出薪水高于在部门30工作的所有员工的薪水,员工姓名,部门名称

select e.ename,e.sal,d.dname
from 
  emp e
join 
   dept d
on
  e.deptno = d.deptno
where
   e.sal > (select max(sal) from emp where deptno = 30 group by deptno);

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

to_days(日期类型) 返回到 公元0年到输入日期的天数

获得数据库的系统当前时间函数是 now()

员工工资天数

to_days(now()) - to_days(hiredate)

select e.deptno,count(*) as total,avg(e.sal) as avgsal,
avg(( to_days(now()) - to_days(e.hiredate)  )/365) as avgserviceyear
from emp e group by deptno; 

27列出所有员工的姓名、部门名称和工资(有一个部门没有员工)

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

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

select d.deptno,count(e.ename) as depttotal,d.dname,d.loc 
from 
   emp e
right join 
  dept d
on 
  e.deptno =d.deptno
group by
   d.deptno,d.dname,d.loc;

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

select t.job,t.minsal,e.ename
 from 
     emp e
 join 
 (select e.job, min(e.sal) as minsal
 from 
    emp e
 group by 
   e.job)t
 on 
  e.job = t.job
 where 
    e.sal = t.minsal;
+-----------+---------+--------+
| job       | minsal  | ename  |
+-----------+---------+--------+
| CLERK     |  800.00 | SMITH  |
| SALESMAN  | 1250.00 | WARD   |
| SALESMAN  | 1250.00 | MARTIN |
| MANAGER   | 2450.00 | CLARK  |
| ANALYST   | 3000.00 | SCOTT  |
| PRESIDENT | 5000.00 | KING   |
| ANALYST   | 3000.00 | FORD   |
+-----------+---------+--------+

30列出各个部门manager的最低薪水

select e.deptno,min(e.sal) as minmanagersal
from 
   emp e
where 
    e.job = 'manager'
group by 
   e.deptno;

31列出所有员工年工资,排序

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal desc;

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

select e.ename,b.ename as leadername
  from 
 emp e
 join 
   emp b
 on 
   e.mgr = b.empno
 where 
    b.sal >3000;

33求部门名称中带‘s’字符的部门员工的工资合计 和部门人数

select d.dname,sum(e.sal) as sumsal,count(e.ename)as total
from 
   emp e
join 
   dept d
on 
  e.deptno = d.deptno
where
   d.dname like '%s%'
group by
   d.dname;

34给在职日期超过30年的员工加薪水

 select  ((to_days(now()) - to_days(hiredate))/365) as year ,e.sal *1.1
  from 
    emp e
  having
   year>30;
   
   //where 只能根据源表emp e中的字段进行过滤
   //having  可以根据信新表进行条件筛选

//update 方法 真正修改


 update emp_bak set sal = sal*1.1 where (to_days(now()) - to_days(hiredate))/365>30;

//sql注入


 select * from t_user where username='dddd' and password='111' or 'aa'='aa';
 
 上式等于
 select *from t_user;
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页