MySQL基础入门学习笔记(一)
- 1、SQL、DB、DBMS分别是什么,它们之间的关系
- 2、什么是表?
- 3、SQL语句的分类
- 4、查看“演示数据”的表结构
- 5、常用命令
- 6、简单的查询 关键字:select
- 7、条件查询 关键字:where
- 8、排序数据 关键字:order by
- 9、分组函数/聚合函数/多行处理函数
- 10、单行处理函数
- 11、分组查询 group by 和 having
- 12、总结一个完整的DQL语句以及执行顺序
- 13、查询结果集的去重:distinct
- 14、连接查询:内连接和外连接
- 15、子查询:where子句中使用子查询、from后面嵌套子查询、select后面嵌套子查询
- 16、union:可以将查询结果集相加
- 17、limit:取结果集中的部分数据,可用于分页查询
1、SQL、DB、DBMS分别是什么,它们之间的关系
-
DB:DataBase(数据库,数据库实际上在硬盘上以文件的形式存在)
-
DBMS:DataBase Management System(数据库管理系统,常见的有:MySQL Oracle DB2 Sybase SqlServer…)
-
SQL:结构化查询语言,是一门标准通用的语言。标准的SQL适合于所有的数据库产品。
SQL属于高级语言。只要能看懂英语单词的,写出来的SQL语句,可以读懂什么意思。
SQL语句在执行的时候,实际上内部也会先进行编译,然后再执行SQL(SQL语句的编译由DBMS完成。) -
关系:DBMS负责执行SQL语句,通过执行SQL语句来操作DB当中的数据。
DBMS --(执行)–> SQL --(操作)–> DB
2、什么是表?
- 表:table是数据库的基本组成单元,所有的数据都以表格的形式组织,目的是可读性强。
- 一个表包括行和列:
行:被称为数据/记录(data)
列:被称为字段(column [ˈkɑːləm]) - 每一个字段包含的属性
字段名、数据类型、相关的约束。
3、SQL语句的分类
- DQL(数据查询语言):查询语句,凡是select语句都是DQL。
- DML(数据操作语言):insert delete update,对表当中的数据进行增删改。
- DDL(数据定义语言):create drop alter,对表结构的增删改。
- TCL(事务控制语言):commit提交事务,rollback回滚事务(TCL中的T是Transaction)
- DCL(数据控制语言):grant授权、revoke撤销权限等。
4、查看“演示数据”的表结构
- 登陆数据库:mysql -u<username> -p<password>
- 查看有哪些数据库:show databases;
- 创建数据库:create database <数据库名>;
- 指定当前缺省数据库:use <数据库名>;
- 查看当前使用的数据库中有哪些表:show tables;
- 删除数据库:drop database <数据库名>;
- 查看表结构:desc <表名>;
- 使用source命令导入sql文件:source <sql脚本路径> (可以直接将文件拖到DOS窗口)
5、常用命令
- 查看当前使用的是哪个数据库:select database();
- 查看mysql的版本号:select version();
- 结束一条语句:\c
- 退出mysql:exit
6、简单的查询 关键字:select
- 语法格式:
select
字段名1,字段名2,字段名3,…
from
表名; - 提示:
(1)任何一条SQL语句以分号结尾。
(2)SQL语句不区分大小写。 - 查询一个字段:select ename from emp;
- 查询多个字段:select empno, ename from emp;
- 查询全部字段:select * from emp;
- 字段可以参与数学运算:select ename,sal * 12 from emp;
- 给查询结果的列重命名:select ename,sal * 12 as yearsal from emp; //as可以省略
- 将查询出来的字段显示为中文:select ename,sal * 12 as ‘年薪’ from emp; //单引号更通用
7、条件查询 关键字:where
-
语法格式:
select
字段1,字段2…
from
表名
where
条件; -
执行顺序:先from,然后where,最后select
-
等号操作符:select ename from emp where sal = 5000; //工资等于3000的员工
-
<>操作符:select ename,sal from emp where sal <> 3000; //工资不等于3000的员工
-
!=操作符:select ename,sal from emp where sal != 3000; //工资不等于3000的员工
-
> < >= <= 操作符:select ename,sal from emp where sal > 3000; //工资高于3000的员工
-
between … and …操作符:select ename,sal from emp where sal between 1100 and 3000;
等同于select ename,sal from emp where sal >= 1100 and sal <= 3000;
between and除了可以使用在数字方面之外,还可以使用在字符串方面。
如:select ename from emp where ename between ‘A’ and ‘C’;(左闭右开,不包含’C’)
between用于数字为左闭右闭,用于字符串为左闭右开 -
is null/is not null:select ename,sal,comm from emp where comm is null;
-
and操作符:select * from emp where job=‘MANAGER’ and sal > 2500;
-
or操作符:select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
-
in操作符:select * from emp where job in (‘manager’,‘salesman’); //等同于上面的or语句(有点像python中的元组)
-
not操作符:select * from emp where sal not in (1600, 3000); //薪水不包含1600和薪水不包含3000的员工
select * from emp where not (sal = 1600 or sal = 3000); -
Like可以实现模糊查询:like支持%和下划线匹配,%代表任意多个字符,_代表任意1个字符。
select ename from emp where ename like ‘_A%’; //名字中第二个字母是A的
8、排序数据 关键字:order by
-
单一字段排序:select * from emp order by sal; //默认升序
-
手动指定排序顺序:
升序 asc(升序 ascending order ):select * from emp order by sal asc;
降序 desc(降序 descending order):select * from emp order by sal desc; -
多个字段排序:select * from emp order by job desc, sal desc;
如果第一个字段排序重复,会根据第二个字段排序 -
使用字段的位置来排序:select * from emp order by 6;
不建议使用此种方式,采用数字含义不明确,程序不健壮
9、分组函数/聚合函数/多行处理函数
- 5个分组函数:count、sum、avg、max、min
count 取得记录数: select count(*) from emp;
sum 求和: select sum(comm) from emp;
avg 取平均: select avg(sal) from emp;
max 取最大的数: select max(sal) from emp;
min 取最小的数: select min(sal) from emp; - 多行处理函数的特点:输入多行,最终输出的结果是1行。
- 分组函数自动忽略NULL,不需要手动加where条件来排除空值。
- count(*)和count(字段)的区别:统计记录数和统计字段中非null数据数
count(*):不是统计某个字段中数据的个数,而是统计总记录条数(和某个字段无关)
count(comm):表示统计comm字段中不为NULL的数据总数量
10、单行处理函数
- 重点:所有数据库都是这样规定的,只要有NULL参与的运算结果一定是NULL。
- ifnull() 空处理函数:
语法:ifnull(可能为NULL的数据,被当做什么处理)
属于单行处理函数。
示例用法:select ename,ifnull(comm,0) as comm from emp;
11、分组查询 group by 和 having
-
group by:按照某个字段或者某些字段进行分组。
-
having:having是对分组之后的数据进行再次过滤。
-
注意:分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的原因。
任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话,整张表的数据会自成一组。 -
记住一个规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段。
select ename,max(sal),job from emp group by job;
以上在mysql当中,查询结果是有的,但是结果没有意义,在Oracle数据库当中会报错:语法错误。
Oracle的语法规则比MySQL语法规则严谨。 -
多个字段联合起来一起分组:
示例:找出每个部门不同工作岗位的最高薪资。
select
deptno,job,max(sal)
from
emp
group by
deptno,job;
查询结果:
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
- 建议能够使用where过滤的尽量使用where,这样效率会比较高:
示例:找出每个部门的最高薪资,要求显示薪资大于2900的数据
这种方式效率低:select max(sal),deptno from emp group by deptno having max(sal) > 2900;
应该使用:select max(sal),deptno from emp where sal > 2900 group by deptno; - 但是where后面不能使用分组函数,某些情况只能使用having过滤(因为group by在where执行后才会执行)
示例:找出每个部门的平均薪资,要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
12、总结一个完整的DQL语句以及执行顺序
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
13、查询结果集的去重:distinct
- distinct关键字去除重复记录
- distinct只能出现在所有字段的最前面
示例:select distinct job from emp;
14、连接查询:内连接和外连接
-
什么是连接查询:
多张表联合查询取出最终的结果(如果数据全部存储在一张表中,会存在大量的数据冗余,因此实际开发中一般都是多表连接查询) -
连接查询的分类:
根据表的连接方式来划分,包括:
内连接 join … on …(等值连接、非等值连接、自连接)
外连接 right/left join … on …(左外连接、右外连接)
全连接(使用较少,暂不学习)
根据语法出现的年代来划分,包括:
SQL92(一些老的DBA可能还在使用这种语法。DBA:DataBase Administrator,数据库管理员)
SQL99(比较新的语法) -
笛卡尔积现象(笛卡尔乘积现象):
当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。 -
关于表的别名:
select e.ename,d.dname from emp e,dept d; -
表的别名有什么好处?
第一:执行效率高。
第二:可读性好。 -
避免笛卡尔积现象:加条件进行过滤
加条件进行过滤可以避免笛卡尔积现象,但是并不会减少记录的匹配次数,只不过显示的是有效记录。 -
内连接:
语法:
…
A表
join
B表
on
连接条件
where
…
等值连接:条件是等量关系。
示例:查询每个员工的部门名称,要求显示员工名和部门名。
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
非等值连接:连接条件中的关系是非等量关系。
示例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
自连接:一张表看做两张表。自己连接自己。
示例:找出每个员工的上级领导,要求显示员工名和对应的领导名。
select
a.ename as ‘员工名’,b.ename as ‘领导名’
from
emp a
inner join
emp b
on
a.mgr = b.empno;
// inner可以省略的,带着inner目的是可读性好一些。
注意:这里的话其实只能查出13条记录而非14条,因为其中一个的员工领导为null,要想显示所有的员工记录需要使用外连接。 -
内连接和外连接的区别:
内连接无主副表之分,查询两表匹配的结果,不匹配的数据则不显示
外连接存在主表和副表,主表中数据始终会全部查询出来,如果副表没有数据与其匹配则匹配null值 -
外连接:
外连接最重要的特点:主表的数据无条件的全部查询出来。
左外连接:表示左边的这张表是主表 left join … on …
示例:找出每个员工的上级领导,要求显示员工名和对应的领导名。(显示所有的员工)
select
a.ename ‘员工’, b.ename ‘领导’
from
emp a
left outer join //outer可省略不写
emp b
on
a.mgr = b.empno;
右外连接:表示右边的这张表是主表 right join … on …
示例:找出哪个部门没有员工?
select
d.*, e.*
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
e.empno is null; -
多张表的连接查询:
语法:
…
A表
join
B表
on
…
join
C表
on
…
说明:A表和B表先进行表连接,连接之后A表继续和C表进行连接。
示例1:找出每一个员工的部门名称以及工资等级。
select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;
示例2:找出每一个员工的部门名称、工资等级、以及上级领导。
select
e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr = e1.empno;
15、子查询:where子句中使用子查询、from后面嵌套子查询、select后面嵌套子查询
- 什么是子查询:
select语句当中嵌套select语句,被嵌套的select语句是子查询。 - 子查询可以出现的位置
select
…(select).
from
…(select).
where
…(select). - where子句中使用子查询
示例:找出高于平均薪资的员工信息
select * from emp where sal > (select avg(sal) from emp); - from后面嵌套子查询
示例1:找出每个部门平均薪水的等级
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
示例2:找出每个部门平均的薪水等级。
使用from后面嵌套子查询语句也可以实现,但是会比较臃肿,需要看一下下面的思路
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
from后面嵌套子查询语句的方法:
select
deptno,avg(grade)
from
(select e.*, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) e
group by
deptno;
- select后面嵌套子查询
示例:找出每个员工所在的部门名称,要求显示员工名和部门名。
使用内连接的方法:
select
e.ename, d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
使用select后面嵌套语句的方法(这个思路确实很难想到):
select
e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
16、union:可以将查询结果集相加
- 两张不相干的表中的数据拼接在一起显示
- 用法:
select ename from emp
union
select dname from dept;
17、limit:取结果集中的部分数据,可用于分页查询
- limit是MySQL特有的,其他数据库中没有,不通用(Oracle中有一个相同的机制,叫做rownum)
- 语法机制:
limit startIndex, length
示例:取出工资前5名的员工(思路:降序取前5个)
select ename,sal from emp order by sal desc;
取前5个:
select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5; - limit是sql语句最后执行的一个环节:
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
limit 7
...;