新手小白学MySQL
- 记住:DQL语句中的执行顺序
- DB、DBMS、SQL的关系
- 表table
- SQL语句的分类
- 导入初始化数据
- SQL脚本: xx.sql,可以使用source命令来执行此脚本
- MYSQL常用命令
- SQL语句
- 分组函数(count,sum,avg,max,min),又称多行处理函数:对某一组数据进行操作;多行输入,单行输出
- 单行处理函数:单行输入,单行输出
- group by (按照某个或者某些字段分组) 和 having(对分组后的数据进行再一次过滤)
- 总结一个完整DQL语句的编写
- 连接查询:从多张表中联合查询,取出最终的查询结果
- union:可以将查询结果集相加
- 表的创建
- 插入数据insert
- 修改表中数据:update
- 删除数据:delete
- 对于表结构的修改(alter):在实际开发中很少发生,建议使用navicat等工具手动改即可
- 约束Constraint:
- 存储引擎:即表的存储方式
- 事务:只有DML语句(insert/delete/update)与事务相关,若不设置,默认一条语句是一个事务
- 索引index:主键和具有unique约束的字段会自动添加索引
- 视图view
- DBA命令:主要是数据库数据的导入导出
- 数据库设计三范式(重点!!!!):为了解决数据冗余
- 总结 表的设计:一对一,一对多,多对多
- 作业题
- 学习视频参考:B站MySQL从入门到精通视频教程 链接:https://www.bilibili.com/video/BV1fx411X7BD?p=1
记住:DQL语句中的执行顺序
分组函数的执行在group by(可缺省)之后。
DB、DBMS、SQL的关系
表table
SQL语句的分类
导入初始化数据
SQL脚本: xx.sql,可以使用source命令来执行此脚本
MYSQL常用命令
创建数据库 create database 库名;
使用数据库 use 库名;
查询当前使用的数据库 select database();
终止一条语句:\c
查看其它库中的表:show tables from 其它库名;
查看表结构:desc 表名;
查看表的创建语句: show create table 表名;
SQL语句
提示:sql语句的注意事项
简单查询语句DQL:select 字段名1,字段名2,…from 表名;
比如:查询员工年薪(字段可以参与数学运算):
再比如把年薪的查询结果那个字段重命名为年薪yearsal:
若想用中文年薪,那必须在年薪上加单引号:‘年薪’
甚至as 可以省略!
注意:实际开发中,我们要用java开发,因此不能用select * 查询,仅供自己查询时使用即可
查询全部字段:select * from 表名;
条件查询:select 字段 from表名 where 条件;(先执行from,再执行where,最后执行select)包括:=,<>,<,>,<=,>=,between and, is null, is not null, and, or, in, not, like
查询工资为5K的员工姓名:
查询名字为SMITH员工的工资(注意字符串类型(varchar)查询要加单引号‘ ’):
找出工资大于3k的员工:
找出工资不等于3k的员工(不等于为:<>):
select ename from emp where sal <> 3000;
找出工资在1100和3000之间的员工:
select enma ,sal from emp where sal>=1100 and sal <= 3000;
或者:
select ename , sal from emp where sal between 1100 and 3000;
但是注意:between and中的这两个数据,大小不能颠倒,前小后大,否则会找不到数据。。。
注意:between and 用在数字,可以看做是闭区间;用在字符上,是左闭右开区间
查询名字在between ‘A’ and 'C’的员工:
select ename from emp where ename between ‘A’ and ‘C’;
查询名字在between ‘A’ and 'D’的员工:
select ename from emp where ename between ‘A’ and ‘D’;
找出哪些津贴为空(不是为0,是null)的员工:
select ename from emp where comm is null;
找出津贴为0的员工:
select ename from emp where comm = 0;
找出津贴为null以及为0的员工:
select ename from emp where comm is null and comm = 0;
找出津贴不为null以及为0的员工:
select ename from emp where comm is not null;
找出工作岗位为manager和salesman的员工:
select ename , job from emp where job = ‘manager’ or job = ‘salesman’;
and 和 or的联合使用:and的优先级高,因此当sql语句中同时出现and和or时,会先执行and,因此为了业务逻辑正确,可以使用小括号
找出薪资大于1000并且部门编号为20或者30部门的员工:
select ename , sal, deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);
in有点等同于or,比如之前的select ename , job from emp where job = ‘manager’ or job = ‘salesman’;等同于select ename, job from emp where job in (‘manager’ , ’ salesman’);
模糊查询like中两个特殊的符号:%和下划线_;其中%:代表任意多个字符;_代表任意一个字符
找出名字中含有o的员工:
select ename from emp where ename like ‘%o%’;
找出名字中第二个字母是A的员工:
select ename from emp where ename like ‘_A%’;
找出名字带有下划线_的员工:使用转义字符\来表示:
select ename from emp where ename like ‘%_%’;
排序(升序asc(默认)、降序desc)order by
按照工资升序,找出员工名和薪资:
select ename , sal from emp order by sal;
按照工资降序,找出员工名和薪资:
select ename , sal from emp order by sal desc;
注意:越靠前的字段,越能起到主导作用,只有当前面的字段无法完成排序时,才会启用后面的字段
按照工资降序排列,当工资相同时,按照名字升序排列:
select ename , sal from emp order by sal desc , ename asc;
注意:还可以指定按照第几列的字段进行排序(不建议使用,java中不能用)
按照第二列的大小进行排序:
select ename , sal from emp order by 2;
注意:from/where/order by 同时使用时,先执行from,再执行where,最后是order by
找出工作岗位是salesman,并且要求薪资按照降序排列:
select ename ,job , sal from emp where job = ‘salesman’ order by sal desc;
分组函数(count,sum,avg,max,min),又称多行处理函数:对某一组数据进行操作;多行输入,单行输出
找出工资高于平均公司的员工:
错误范例:
select ename , sal from emp where sal >avg(sal);
###注意 :SQL语句的语法规则中,分组函数不可以直接使用在where子句中,因此上述代码出错。
正确格式:
方案一:可以分成两步:首先求出平均,然后sal>平均sal即可
方案二:即将上述两步合并即可(语句的嵌套,又称为子查询)
select ename ,sal from emp where sal >(select avg(sal) from emp );
count(某一字段)和count(*)的区别
具体到某个字段,count记录的是该字段不为空的记录数;
而count(*),统计的是总记录条数,与具体的某个字段无关。
分组函数也可以组合在一起使用
比如:
select count(*) , sum(sal) , avg(sal) , min(sal) , max(sal) from emp;
注意:分组函数会自动忽略null
select count(comm) from emp;
单行处理函数:单行输入,单行输出
规定:所有数据库都规定,只要有null所参与的运算,其结果一定为null!!!!
比如计算每个员工的年薪(月薪+月补助相加再乘12):
select ename , (sal + comm) * 12 as yearsal from emp;
null参与运算的处理:ifnull()函数:空处理函数,属于单行处理函数
仍以上述的计算每个员工的年薪(月薪+月补助相加再乘12):
select ename , (sal + ifnull(comm,0) * 12) as yearsal from emp;
如果使用ifnull,此时再次对comm进行计数:
select count( ifnull(comm , 0) ) from emp;
group by (按照某个或者某些字段分组) 和 having(对分组后的数据进行再一次过滤)
注意:group by 一般都会和分组函数联合使用。且先执行group by,后执行分组函数。
如果sql语句中没有group by,那么整张表会自成一组。(即可缺省)
因此这也能解释为什么分组函数不能出现在where子句中,因为:group by在where子句之后执行,而分组函数在group by之后执行。
比如找出每个工作岗位的最高薪资:
错误范例:
select ename, sal group by ‘salesman’ having max(sal);
正确代码:
select job, max(sal) from emp group by job;
错误示例:
注意:select ename , job ,max(sal) from emp group by job;在mysql中可以执行,但是其结果毫无意义,因为在表中ename有14个字段,而按照工作岗位分组之后只有4个,这样一来ename和分组后的数据对应不上,输出的结果毫无意义。此外,在oracle数据库中执行此sql语句肯定会报错的。
但实际上:SMITH的工资是800块
结论:当sql语句含有group by时,只允许select后面出现分组函数以及参与分组的字段。
每个工作岗位的平均薪资:
select job , avg(sal) from emp group by job;
找出每个部门不同工作岗位的最高薪资:
select max(sal), job , deptno from emp group by deptno , job;
找出每个部门的最高薪资,同时要求显示薪资大于2500的数据:
方式一:select ename, max(sal) from emp group by deptno having max(sal) > 2500;
但是这种效率比较低
方式二:
select deptno , max(sal) from emp where sal > 2900 group by deptno;
这种方式相比第一种效率高,因为先过滤掉sal>2900的,再按照部门求;而第一种先求出各个部门的最高薪资,然后部门编号30的因为最高薪资2850小于2900被过滤掉了,那么寻找部门编号30的最高薪资的工作相当于白干了。
找出每个部门的平均薪资,要求显示薪资大于2000的数据:
错误范例:
select deptno, avg(sal) from emp where (select avg(sal) from emp) > 2000 group by deptno;
以上并没有把小于2000的过滤掉,why???
正确代码:
select deptno, avg(sal) from emp group by deptno having avg(sal) >2000;
查询结果集的去重distinct关键字
比如:
查询公司的工作岗位:
select distinct job from emp;
注意:distinct只能出现在所有字段的最前方,用于将后面所选的所有字段全部去重
比如:
select ename, distinct job from emp;
会报语法错误。从逻辑上也很好分析:distinct去重之后,剩下的job岗位(5个)肯定少于表中的总记录数14条,而ename是公司每个员工的记录表,14名员工的ename和5种工作岗位是无法在表中正确显示的。
再比如:
select distinct deptno , job from emp;
统计岗位的数量:
select count(distinct job) from emp;
总结一个完整DQL语句的编写
连接查询:从多张表中联合查询,取出最终的查询结果
连接查询的分类:内连接/外连接/全连接
笛卡尔积现象:当两张表连接查询,如果不加任何条件限制,最终查询结果条数会是两张表记录条数的乘积
比如:
select ename , dname from emp , dept;或者
select e.ename , d,dname from emp as e, dept as d;(结果一样,只是可读性和执行效率提高了)
表长完整图略
如何避免笛卡尔积现象
注意:避免了笛卡尔积现象,并不会减少记录的匹配次数,只是输出的结果少了(因为通过添加限制,得出我们想要的有效记录,去除无效或者无用的记录)
找出每一个员工的部门名称,要求显示员工名和部门名:
方法一(sql92旧方法):
select
e.ename , d.dname
from
emp as e , dept as d
where e.deptno = d.deptno;
内连接之等值连接:最大特点:条件是等量关系
方法二(sql99常用):主要是把连接条件和过滤条件分开了,连接条件用join on,过滤条件用where,更加清晰
select
e.ename , d.dname
from
emp as e
join
dept as d
on
e.deptno = d.deptno;
内连接之非等值连接:连接条件中的关系是非等量关系
找出每个员工的工资等级,要求显示员工名、工资、工资等级:
select
e.ename,e.sal,s.grade
from
emp as e
inner join
salgrade as s
on
e.sal between s.losal and s.hisal;
内连接之自连接:一张表看成两张表
找出每个员工的上级领导,要求显示员工名和对应的领导命:
即在同一张emp表中进行查找,但是在第一张表中所有人都是员工,在第二张表中全为领导,然后进行联合查询
select
e1.name ,e2.name
from
emp as e1
inner join
emp as e2
on
e1.mgr = e.empno;
更清晰一点:
select
e1.name as '员工名' ,e2.name as '领导名'
from
emp as e1
inner join
emp as e2
on
e1.mgr = e.empno;