mysql学习（一）

最新推荐文章于 2023-03-05 04:16:00 发布

codetimev

最新推荐文章于 2023-03-05 04:16:00 发布

阅读量3.6k

点赞数 5

文章标签： mysql

本文链接：https://blog.csdn.net/qq_44017091/article/details/123856569

版权

1.SQL语句的分类

DQL:数据库查询语言（凡是带有select关键字的都是查询语言）
DML:数据操作语言（凡是对表中的数据进行增删改的都是DML）
insert :增 delete:删 update:改
DDL:数据定义语言
凡是带有 create 、drop、alert的都是DDL
DDL主要操作表的结构。不是操作表中的数据
create:新建 drop:删除 alert：修改
TCL:事务控制语言事务提交事务回滚
DCL：数据控制语言例如：授权 grant 、撤销权revoke…

查看表的结构： desc 表明
查看数据库的版本号：SELECT VERSION()

2.建立三张表
dept(部门表）

SET FOREIGN_KEY_CHECKS=0;

– Table structure for dept

DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
DEPTNO int NOT NULL AUTO_INCREMENT,
DNAME varchar(14) CHARACTER SET utf8 COLLATE utf8_croatian_ci DEFAULT NULL,
LOC varchar(13) CHARACTER SET utf8 COLLATE utf8_croatian_ci DEFAULT NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_croatian_ci;

– Records of dept

INSERT INTO dept VALUES (‘1’, ‘销售部’, ‘东门’);
INSERT INTO dept VALUES (‘2’, ‘市场部’, ‘西门’);
INSERT INTO dept VALUES (‘3’, ‘开发部’, ‘中心’);
INSERT INTO dept VALUES (‘4’, ‘财务部’, ‘中南’);

在这里插入图片描述 empty(员工表)

SET FOREIGN_KEY_CHECKS=0;

– Table structure for emp

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
EMPNO int NOT NULL AUTO_INCREMENT,
ENAME varchar(10) COLLATE utf8_croatian_ci DEFAULT NULL,
JOB varchar(10) COLLATE utf8_croatian_ci DEFAULT NULL,
MGR int DEFAULT NULL COMMENT ‘上级领导’,
HIREDATE date DEFAULT NULL COMMENT ‘入职日期’,
SAL double DEFAULT NULL COMMENT ‘月薪’,
COMM double DEFAULT NULL COMMENT ‘补助’,
DEPTNO int DEFAULT NULL COMMENT ‘部门编号’,
PRIMARY KEY (EMPNO)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_croatian_ci;

– Records of emp

INSERT INTO emp VALUES (‘1’, ‘张三’, ‘采购员’, ‘5’, ‘2022-03-01’, ‘4400’, ‘200’, ‘2’);
INSERT INTO emp VALUES (‘2’, ‘李四’, ‘采购员’, ‘5’, ‘2022-03-01’, ‘4000’, ‘200’, ‘2’);
INSERT INTO emp VALUES (‘3’, ‘王五’, ‘采购员’, ‘5’, ‘2022-02-28’, ‘3000’, null, ‘2’);
INSERT INTO emp VALUES (‘4’, ‘赵六’, ‘会计’, ‘9’, ‘2021-12-28’, ‘7000’, ‘1000’, ‘4’);
INSERT INTO emp VALUES (‘5’, ‘盖伦’, ‘主管’, ‘100’, ‘2021-01-06’, ‘15000’, ‘2000’, ‘1’);
INSERT INTO emp VALUES (‘6’, ‘张四’, ‘采购员’, ‘7’, ‘2022-03-31’, ‘4300’, ‘200’, ‘2’);
INSERT INTO emp VALUES (‘7’, ‘jack_son’, ‘主管’, ‘100’, ‘2022-03-09’, ‘14000’, ‘1000’, ‘1’);
INSERT INTO emp VALUES (‘8’, ‘woke’, ‘主管’, ‘100’, ‘2022-03-01’, ‘14000’, ‘1000’, ‘1’);
INSERT INTO emp VALUES (‘9’, ‘SEee’, ‘会计’, ‘9’, ‘2022-02-28’, ‘7000’, ‘500’, ‘4’);
INSERT INTO emp VALUES (‘10’, ‘haodeme’, ‘程序员’, ‘9’, ‘2022-04-02’, ‘12000’, ‘1000’, ‘3’);

在这里插入图片描述
salgrade(工资等级表)

SET FOREIGN_KEY_CHECKS=0;

– Table structure for salgrade

DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade (
GRADE int NOT NULL AUTO_INCREMENT COMMENT ‘等级’,
LOSAL int DEFAULT NULL COMMENT ‘最低薪资’,
HISAL int DEFAULT NULL COMMENT ‘最高薪资表’,
PRIMARY KEY (GRADE)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_croatian_ci;

– Records of salgrade

INSERT INTO salgrade VALUES (‘1’, ‘1000’, ‘5000’);
INSERT INTO salgrade VALUES (‘2’, ‘5000’, ‘10000’);
INSERT INTO salgrade VALUES (‘3’, ‘10000’, ‘20000’);
在这里插入图片描述
然后自己在表中填入数据即可
数据表内容根据学习后加入，可供参考
3.条件查询
查询员工的名字和薪资
SELECT ENAME,SAL FROM emp
d840a6e0b.png)
查询年薪（字段可以使用数学表达式）
SELECT ENAME,SAL12 as YEARSAL FROM emp
或者
SELECT ENAME ,SAL12 as ‘年薪’ from emp
//别名是中文需要用单引号
查询工资等于4000的员工姓名和编号
SELECT EMPNO, ENAME FROM emp where SAL=4000
查询工资不等于4000的员工姓名和编号
SELECT EMPNO,ENAME FROM emp where SAL!=4000
或者
SELECT EMPNO,ENAME FROM emp where SAL<>4000
查询员工工资小于或等于2000的姓名和编号
SELECT EMPNO,ENAME FROM emp where SAL<2000
查询王五的编号和薪资
SELECT EMPNO,SAL FROM emp where ENAME=‘王五’
查询员工薪资大于或者等于3000，小于或者等于5000的编号，姓名和薪资
SELECT EMPNO,ENAME,SAL FROM emp where SAL>=3000 and SAL <=5000
或者
SELECT EMPNO ,ENAME,SAL FROM emp where SAL BETWEEN 3000 AND 5000
查询那些员工的补助为null
SELECT EMPNO,ENAME,SAL,COMM from emp where COMM is null
查询那些员工的补助不为null
SELECT EMPNO,ENAME,SAL,COMM from emp where COMM is NOT null
查询工作岗位为采购员并且工资大于三千的员工信息
SELECT EMPNO ,ENAME, SAL,JOB FROM emp where JOB=‘采购员’ AND SAL >3000
查询工作岗位为会计和采购员的员工
SELECT EMPNO,ENAME,SAL,JOB from emp WHERE JOB IN (‘采购员’,‘会计’)
或者
SELECT * from emp WHERE JOB IN (‘采购员’,‘会计’)
工资大于3000并且部门编号为20或者21
SELECT * from emp where SAL >3000 AND (DEPTNO=22 OR DEPTNO=23)
//记得加括号and 优先级大于or
查询工资为3000和4000的员工
SELECT ENAME,SAL FROM emp WHERE SAL=3000 OR SAL=4000
或者
SELECT ENAME,SAL FROM emp where SAL IN (3000,4000)

like：成为模糊查询，支持%或者下划线
%匹配任意多个字符
下划线：任意一个字符
查询名字里面有’张’员工
SELECT * from emp WHERE ENAME LIKE ‘%张%’
查询名字以‘王’字开始的
SELECT * from emp where ENAME like ‘王%’
查询名字以’五’字结束的
SELECT * from emp WHERE ENAME like ‘%五’
找出第二个字母是‘A’的
SELECT * from emp WHERE ENAME like ‘—A%’
找出名字带有下划线的
SELECT * from emp WHERE ENAME like ‘%_%’

4.排序
查询所有员工薪资：（默认是升序）
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
找出工资在4000–10000的员工，并进行降序排列
SELECT ENAME,SAL FROM emp WHERE SAL>=4000 AND SAL<=10000 ORDER BY SAL DESC

4.数据处理函数
单行处理函数：一个输入对应一个输出
多行处理函数：多个输入对应一个输出
计算所有员工的工资
SELECT SUM(SAL) FROM emp
常见的单行处理函数：
lower:转成小写
upper:转成大写
SELECT UPPER(ENAME) FROM emp
substr 取子串
SELECT SUBSTR(ENAME,1,1)AS ENAME FROM emp
concat字符串拼接
SELECT CONCAT(EMPNO,ENAME) FROM emp
lentgth取字符串长度
SELECT length(ENAME)FROM emp
trim 去空格
SELECT * FROM emp where ENAME=TRIM( ’ woke’)
ROUND四舍五入
SELECT ROUND(123.22,0) FROM emp
rand生成随机数
SELECT RAND()100 FROM emp
ifnull可以将null转换成一个具体值
ifnull空处理函数
在所有数据库之中
ifnull函数用法 :ifnull(数据，被当做那个值)
SELECT ENAME ,(SAL+IFNULL(COMM,0))12 as yearsal FROM emp
case … when … then …when…else …end
4.分组函数
多行处理函数的特点:输入多行，最终输出一行
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
分组函数在使用的时候需要注意那些：
分组函数忽略null
count（）和count(具体字段)有什么区别
count(具体字段):表示统计该字段下所有不为null的元素的总数
count()：统计表的总行数
分组函数不能用在where语句之中
找出比最低工资高的员工信息:
SELECT * FROM emp where SAL >MIN(SAL)
[SQL] – SELECT COUNT(ENAME) FROM emp
SELECT * FROM emp where SAL >MIN(SAL)
[Err] 1111 - Invalid use of group function
分组函数不能在where后面，分组函数使用的时候必须先分组在使用
where执行的时候，还没有分组，所以where函数后面不能出现分组函数
所有分组函数可以组合一起用
select sum(SAL),min(SAL)… from emp;

5.分组查询
什么是分组查询
在实际的应用之中，可有这样能的需求，先进行操作，然后对每一组数据
进行操作，这个时候需要进行分组查询
执行顺序
1.from
2.where
3.group by
4.select
5.order by

找出每个工作岗位的工资和。
SELECT JOB,SUM(SAL) FROM emp GROUP BY JOB;
在一条select语句，如果有group by的语句话，select后面只能跟：参加分组的字段，以及分组函数，其他的一律不能跟
找出每个部门的最高薪资
SELECT DEPTNO,MAX(SAL) FROM emp GROUP BY DEPTNO
找出每个部门不同工作岗位的最高薪资
SELECT DEPTNO,JOB,MAX(SAL) FROM emp GROUP BY DEPTNO,JOB

找出每个部门的最高薪资，要求最高的薪资大于3000的？
使用having可以对分完组，having必须跟group by一起用
SELECT DEPTNO,MAX(SAL) FROM emp GROUP BY DEPTNO HAVING MAX(SAL)>5000
这个sql语句执行效率低
SELECT DEPTNO,MAX(SAL) FROM emp where SAL>5000 GROUP BY DEPTNO
优化策略：where 和having 优先选择where, where完成不了的用having

找出每个部门的平均工资，要求显示平均工资高于5000的
SELECT DEPTNO,AVG(SAL) FROM emp GROUP BY DEPTNO HAVING AVG( SAL)>5000

总结
select…
from…
where…
group by…
having…
order by…
执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by
从某张表中查询数据
先经过where条件筛选有价值的数据
对这些有价值的数据进行分组
分组之后可以用having继续筛选

找出每个岗位的平均工资，要求显示平均工资大于5000，像程序员这个岗位除外，其他按照平均工资进行降序
SELECT JOB,AVG(SAL) AS avgsal FROM emp WHERE JOB <>‘程序员’ GROUP BY JOB HAVING AVG(SAL)>5000 ORDER BY avgsal DESC

除出重复记录
例如：SELECT DISTINCT JOB FROM emp
distinct只能出现所有数据的前方
SELECT COUNT(DISTINCT (JOB)) FROM emp

连接查询
当两张表进行连接查询的时候，没有任何条件限制，最终查询的条数，会是两条表的乘积，这种现象被称为：笛卡尔积。
查询员工表和部门表
SELECT * FROM emp,dept where emp.DEPTNO=dept.DEPTNO
最终查询的条数是表的条数，但是匹配的次数没有减少
通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数

内连接
等值连接
查询每个员工所在的部门名称，显示员工工名和部门名称
SELECT e.ENAME,d.DNAME FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO
inner可以省略的

非等值连接
找出员工的工资的等级，显示员工的工名，薪资，薪资等级
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 JOIN emp b ON a.MGR=b.EMPNO
内连接的自连接
内连接特点，完全匹配上这个条件的数据查询出来

外连接
//右外连接
SELECT e.ENAME,d.DNAME FROM emp e RIGHT JOIN dept d ON e.DEPTNO=d.DEPTNO
出来没有匹配不上的数据 ,外连接会有主次关系，内连接没有主次关系

左外连接
SELECT e.ENAME,d.DNAME FROM emp e LEFT JOIN dept d ON e.DEPTNO=d.DEPTNO
任何一个右连接都会有一个左连接的写法
任何一个左连接都会有一个右连接的写法

查询每个员工的上级领导，要求显示所有员工的名字和领导名？
SELECT a.ENAME AS ‘员工表’ ,b.EMPNO AS ‘领导表’ FROM emp a LEFT JOIN emp b ON a.MGR=b.EMPNO

三张表或者四张表怎么连接
语法:
select … from a jion b on a 和b的连接条件
jion c on a和c的连接条件
jion d on a 和d 的连接条件

找出每个员工的部门名称，以及工资等级，要求显示员工名，部门名称，薪资，薪资等级。
SELECT e.ENAME,e.SAL,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

子查询
什么子查询
select语句之中嵌套select语句，被嵌套的select语句称为子查询
select
…(select)
from
… (select)
where
…(select)
where语句之中出现子查询
案例：找出比最低工资高的员工姓名和工资？
SELECT ENAME,SAL FROM emp WHERE SAL>(SELECT MIN(SAL) FROM emp)

from语句之中子查询
注意：from子查询可以将子查询当做一张临时表
找出每个岗位的平均工资的等级
SELECT t.*,s.GRADE FROM (SELECT JOB,AVG(SAL) AS avgsal FROM emp GROUP BY JOB) t JOIN salgrade s ON t.avgsal BETWEEN s.LOSAL AND s.HISAL

select语句之中的子查询
找出每个员工的部门名称，要求显示员工名
SELECT e.ENAME,(SELECT d.DNAME FROM dept d WHERE e.DEPTNO=d.DEPTNO ) AS dname FROM emp e
select语句的子查询智能返回一条结果

union合并查询结果集
案例查询工作岗位为’采购员’ ‘会计’的员工
SELECT ENAME,JOB FROM emp where JOB=‘采购员’
UNION
SELECT ENAME,JOB FROM emp WHERE JOB=‘会计’

union的效率会高一些，减少表的匹配次数

limit startIndex length
startIndex是起始下标 ,length是长度
起始下标从零开始

取出工资排名在5-9名员工
SELECT ENAME,SAL FROM emp ORDER BY SAL DESC LIMIT 4,5

分页
每页显示3条数据
每页显示pagesize条记录
pageno页: limit (pageNo-1)*pagesize ,pagesize

执行顺序
1.from 2.where 3. group by 4.having 5.select 6.order by 7.limit…