-
List item
MySQL的基础完结篇
1、数据库、数据库管理系统、sql三者之间的关系?
数据库:DataBase,简称DB;按照一定的格式来存储数据的一些文件的组合。
数据库管理系统:DataBaseManagement,简称 DBMS。数据库管理系统是专门管理数据库中数据的,数据库管理系统可以对数据库当中的数据进行增删改查。
常见的数据库管理系统:
Mysql 、Oracle、MS、SqlServer、DB2、sybase等。
sql:结构化查询语言
程序员需要学习sql语句,通过编写sql语句,然后DBMS负责执行sql语句,最终来完成数据库中数据的增删改查操作。
== 三者的关系==
DBMS----执行-----sql----操作-----DB
2、端口号:端口号port是任何一个软件/应用都会有的,端口号是应用的唯一代表。端口号通常和IP地址在一块,IP地址用来定位计算机的,端口号port是用来定位计算机上某个服务/某个应用的。
在一台计算机上,端口号不能重复,具有唯一性;
mysql数据库启动时,这个服务占有的默认端口号是3306;
3、字符编码方式
设置MySQL数据库的字符编码方式为utf8;
4、mysql的常用命令:
- 退出mysql:exit;
- 查看mysql中有哪些数据库:show databases;mysql默认自带了四个数据库
- 选择使用某个数据库:use 数据库名;
- 创建数据库:create database 库名;
- 查看数据库下有哪些表:show tables;
- 导入数据:source;
- 查看表:show tables;
- 查看表中的数据:select *from 表名;
- 不看表中的数据,只看表的结构,有一个命令: desc 表名;
- 查看mysql数据库的版本号:select version();
- 查看当前使用的是哪个数据库:select database();
- \c用来终止一条命令的输入;
5、数据库当中最基本的单元是表:table;
数据库当中是以表格的形式表示数据的,因为表比较直观;
任何一张表都有行和列;
行(row):被称为数据/记录;
列(column):被称为字段;
每一个字段都有:字段名,数据类型,约束等属性;
6、关于sql语句的分类:
sql语句有很多种,分为:
DQL:数据查询语言(凡是带有select关键字的都是查询语句)例如;select …
DML:数据操作语言(凡是对表中的数据进行增删改的都是DML)
例如:insert(增) delete(删) 改(update)
DDL:数据定义语言:凡是带有create、drop、alter的都是DDL;
DDL主要操作的是表的结构,不是表的数据。
TCL:事物控制语言:
包括:事物提交:commit; 事物回滚:rollback;
DCL:数据控制语言:
授权:grant 撤销权限: revoke
7、简单查询
- 查询一个字段:select 字段名 from 表名;
注意:select和from都是关键字;字段名和表名都是标识符;
例如:查询部门的名字:select dname from dept; - 查询两个字段或者多个字段:使用“,”隔开;
例如:查询部门编号和部门名:select deptno,dname from dept; - 查询所有字段:第一种方式:可以把每个字段都写上去;
例如:select a,b,c…from tablename;
第二种方式:可以使用 *;
select * from dept;这种方式的缺点:效率低,可读性差 。 - 给查询的列起别名:select deptno ,dname as deptname from dept ;
使用as关键字起别名,注意:只是将显示的查询结果列名显示为deptname,原表列名还是叫:dname
记住:select 语句是永远不会进行修改操作的。(因为只负责查询)as关键字可以省略;
假设起别名的时候,别名里面有空格,可以用单引号或者双引号引起来。
例如:select deptno ,dname ‘dept name’ from dept ;
select deptno ,dname “dept name” from dept ;
注:在所有的数据库中,字符串统一使用单引号括起来,双引号在Oracle数剧库中用不了,但是在mysql中可以使用。 - 字段可以使用数学表达式;别名是中文用单引号括起来;
8、条件查询
-
条件查询是查询出来符合条件的;语法格式:select 字段1,字段2,字段3…from 表名 where 条件;
-
条件分类:=等于 查询薪资等于800的员工姓名和编号:
select empno ,enamel from emp where sal = 800;
查询SMITH 的编号和薪资:
select empno ,ename from emp where ename = 'SMITH’;//字符串使用单引号
<>或!= 不等于:查询薪资不等于800的员工姓名和编号;
例如:select empno ,ename from emp where sal != 800;
select empno ,ename from emp where sal <> 800;
< 小于:查询薪资小于2000的员工姓名和编号;
例如:select empno ,ename ,sal from emp where sal < 2000;
<= 小于等于;
>= 大于等于;
between…and…两个值之间,等同于>= and <=;
例如:查询薪资在2450和3000之间的员工信息?包括2450和3000:
第一种方式:>= and<=:
select empno ,ename ,sal from emp where sal>=2450 and sal <=3000;
第二种方式:between… and…
select empno ,ename ,sal from emp where sal between 2450 and 3000;
注意:使用between…and…,必须遵循左小右大;
betwwen…and…是闭区间,包括两端的值;
is null为 null(is not null不为空)
例如:查询那些员工的津贴/补助为null?
select empno ,ename ,sal ,comm from emp where comm is null ;
注意:在数据库当中null不能使用等号进行衡量。需要使用is null,因为数据库中的null代表什么也没有,它不是一个值,所有不能使用等号来衡量。
例如:查询那些员工的津贴/补助不为null?
select empno ,ename ,sal ,comm from emp where comm is not null ;
and 并且:
例如 :查询工作岗位是manager并且工资大于2500的员工信息?
select empno ,ename ,job,sal from emp where job =‘manager’ and sal >2500;
or 或者:
查询工作岗位是manager或者salesman的员工?
select empno ,ename,job from emp where job =‘manager’ or job =‘salesman’;
and 和or 同时出现的时候,and的优先级比or高,如果先要or先执行,需要加“小括号”,如果不确定优先级,就加小括号就可以
例如:找出工资大于2500并且部门编号为10或者20部门的员工:
select * from emp where sal > 2500 and (deptno =10 or deptno =20);
in 包含,相当于多个or(not in不在这个范围中):
查询工作岗位是manager和salesman的员工?
select empno ,ename ,job from emp where job in (‘manager’,‘salesman’);
注意:in不是一个区间。in后面跟的是具体的值
查询薪资是800和5000的员工信息?
select ename ,sal from emo where sal in (800,5000);
not in 表示不在这几个值当中的数据。
select ename ,sal from emp where sal not in (800,5000,3000);
not not 可以取非,主要用在 is 或 in中
is null is not null
in not in
like 模糊查询,支持%或下划线匹配,%匹配任意字符,下划线,一个下划线只匹配一个字符。%是一个特殊的符号,_也是一个特殊符号;
例如;找出名字中含有o的?
select enam from emp where ename like ‘%0%’;
找出名字中以T结尾的?
select ename from emp where ename like '%T’;
找出名字中以K开始的?
select ename from emp where ename like ‘T%’;
找出第二个字母是A的?
select ename from emp where ename like '-TA%’;
找出第三个字母是R的?
select ename from emp where ename like ‘__R%’;
找出名字中含有“-”的?
select name from t_student where name like ‘%_%’;// \转义字符;
9、排序
- 查询所有员工薪资,排序?例如: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 asc;//指定升序
- 可以两个字段排序或者多个字段排序吗? 例如:查询员工名字和薪资,要求按照薪资升序,如果薪资一样的话,在按照名字升序排列。select ename ,sal from emp order by sal asc,ename asc;
- 根据字段的位置也可以排序。例如 :select ename,sal from emp order by 2;// 2 表示第二列。按照查询结果的第二列sal排序。不建议开发使用。
10、综合
找出工资在1250到3000之间的员工信息,要求按照薪资降序排列。
select ename ,sal from emp where sal between 1250 and 3000 order by sal desc;
关键字的顺序不能变:
select
…
from
…
where
…
order by(排序总是在最后执行!)
…
==以上语句的执行顺序必须要掌握:
第一步:from
第二步:where
第三步:select
第四步:order by
11、数据处理函数
- 数据处理函数又被称为单行处理函数,单行处理函数的特点:一个输入对应一个输出。
和单行处理函数的特点:一个输入对应一个输出;
和单行处理函数相对的是:多行处理函数。(多行处理函数特点:多个输入,对应1个输出!) - 单行处理函数常见的有哪些?
lower 转换小写
例如:select lower(ename) as ename from emp ;
upper 转换大写
例如:select upper(name)as name from emp;
substr 取子串(substr(被截取的字符串,起始下标,截取的长度))
例如:select substr(ename,1,1) as ename from emp;
注意:起始下标从1开始,没有0.
找出员工名字第一个字母是A的员工信息?
第一种方式:模糊查询
select ename from emp where ename like ‘A%’;
第二种方式:substr函数
select ename from emp where substr(ename,1,1)=‘A’;
concat 函数进行字符串拼接:
例如:select concat(empno ,ename)from emp;
length 取长度:
例如:select length(ename ) enamelength from emp;
trim 去空格:
例如:select *from emp where ename =trim(‘ KING’);
round 四舍五入:
例如:select 后面可以跟某个表的字段名(可以等同看做变量名),也可以跟字面值/字面量(数据)
select round (1236.567,0) as result from emp ;//结果是1237,保留整数位
select round(1236,567,1) as result from emp;//结果是1236,6 保留一个小数
select round(1236,567,2) as result from emp;//结果是1236,57 保留两个小数
select round(1236,567,-1) as result from emp;//结果是1240 保留到十位
select round(1236,567,-2) as result from emp;//结果是1200 保留到百位
rand() 生成随机数:
select rand() from emp;
select round(rand()*100,0) from emp;//100以内的随机数
ifnull 可以将null转换成一个具体值,ifnull是空处理函数,专门处理空的。在所有数据库当中,只要有null参与的数学运算,最终结果就是null。
select ename,sal+camm as salcom from emp;
例如:计算每个员工的年薪:年薪=(月薪+月补助)12;
注意:Null只要参与运算,最终结果一定是Null,为了避免这个现象,需要使用ifnull函数。
ifnull函数用法:ifnull(数据,被当做那个值)如果“数据”为null的时候,把这个数据结构当做那个值,补助为null的时候,将补助当做0;
select name ,(sal+ifnull(comm,0)12 as yearsal from emp;
format 设置千分位
case…when…then…when…then…else…end
例如:当员工的工作岗位是manager的时候,工资上调10%,当工作岗位是salesman的时候,工资上调50%;
(不修改数据库,只是将查询结果显示为工资上调)
select ename ,job,sal as oldsal,(case job when ‘manager’ then sal1.1 when ‘salesman’ then sal1.5 else sal end) as newsal from emp;
12、分组函数(多行处理函数)
多行处理函数的特点:输入多行,最终输出一行。
5个:
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注意:分组函数在使用的时候必须先进行分组,然后才能使用。
如果你没有对数据进行分组,整张表默认为一组。
例如:找出最高工资?
select max(sal) from emp;
找出最低工资?
select min(sal) from emp;
计算工资和:
select sum(sal) from emp;
计算平均工资:
select avg(sal) from emp;
计算员工数量?
select count(ename) from emp;
分组函数在使用的时候需要注意那些?
select sum(comm) from emp ;分组函数自动忽略null,你不需要提前对null进行处理
分组函数中count() 和count(具体字段)有什么区别?
count(具体字段):表示统计字段下所有不为null的元素的总数。
count():统计表当中的总行数。(只要有一行数据count,则++)
注意:分组函数不能够直接使用在where子句中
所有的分组函数可以组合起来一起用
select sum(sal) ,min(sal),max(sal) ,avg(sal),count(*) from emp;
13、分组查询
- 什么是分组查询?
在实际的应用中,可能有这样的要求,需要先进行分组,然后对每一组的数据进行操作。这个时候我们需要使用分组查询,怎么进行分组查询呢?
select
…
from
…
group by
…
计算每个部门的工资和?
计算每个工作岗位的平均薪资?
找出每个工作岗位的最高薪资?
2、将之前的关键字全部组合在一起,来看一下他们的执行顺序?
select
…
from
…
where
…
group by
…
order by
…
以上关键字的顺序不能颠倒,需要记忆。
执行顺序是什么?
1、from
2、where
3、group by
4、select
5、order by
为什么分组函数不能直接使用在where后面?
select ename ,sal from emp where sal >min(sal);//报错
因为分组函数在使用的时候必须先分组之后才能使用。where执行的时候,还没有分组。所以where后面不能出现分组函数。
3、找出每个工作岗位的工资和?
实现思路:按照工作岗位分组,然后对工资求和。
select job,sum(sal) from emp group by job;
在一条select语句当中,如果有group by语句的话,select后面只能跟:参加分组的字段,以及分组函数。其他一律不能跟
找出每个部门的最高薪资:
实现思路:按照部门编号分组,求每一组的最大值
select deptno,max(sal) from emp group by deptno;
找出“每个部门,不同工作岗位”的最高薪资?
技巧:两个字段联合成1个字段看。
select deptno ,job,max(sal) from emp group by deptno,job;
4、使用having可以对分完组之后的数据进一步过滤。having不能单独使用,having不能代替where,having必须和group by联合使用。
例如:找出每个部门最高薪资,要求显示最高薪资大于3000的?
select deptno ,max(sal) from emp group by deptno having max(sal) >3000;
select deptno ,max(sal) from emp where sal>3000 group by deptno;
优化策略:where 和having。优先选择where。
5、总结
select
…
from
…
group by
…
having
…
order by
…
执行顺序:
1、from
2、where
3、group by
4、having
5、select
6、order by
例如:找出每个岗位的平均薪资,要求显示平均薪资大于1500的,出manager之外,要求按照平均薪资降序排。
select job,avg(sal) as avgsal from emp where job <> ‘manager’ group by job having avg(sal) >1500 order by avgsal desc;