Mysql数据库
1.概念:
关系模型中常用的概念:
关系:可以理解为一张二维表,每个关系都具有一个关系名,就是通常说的表名
元组:可以理解为二维表中的一行,在数据库中经常被称为记录
属性:可以理解为二维表中的一列,在数据库中经常被称为字段
域:属性的取值范围,也就是数据库中某一列的取值限制
关键字:一组可以唯一标识元组的属性,数据库中常称为主键,由一个或多个列组成
关系模式:指对关系的描述。其格式为:关系名(属性1,属性2, ... ... ,属性N),在数据库
中成为表结构
2.scott用例表
3.SQL-DQL语句
3.1 格式
select 列名*N from 表名 where 查询条件1 and/or 查询条件2 group by 列
Having 分组
条件 Order by 排序
3.2 规则
sql在书写的时候除了查询条件之外,大小写都可以
案例:
select * from user where uname ='zs';
SELECT * FROM USER WHERE UNAME = 'zs';
注意:1.--/## 都属于属于SQL语句的注释
2.所有的查询条件为字符串时,需要用''进行修饰,否则就会当做列名去处理
3.3 select查询列和别名
案例:
--查询所有员工信息(*通配符,默认查询所有的列)
select * from emp;
--查询员工的姓名
select ename from emp;
--查询员工的薪资
select sal from emp;
--查询员工的姓名和薪资,并且设置别名(推荐使用''单引号)
select ename '员工姓名',sal '薪资' from emp;
--查询到的数据可以直接进行运算(查询员工的月薪和年薪)
select ename,sal,sal*12 from emp;
3.4 select的条件查询
3.4.1普通条件查询 =,>,<,!= ,<>,>= ,<=
案例:
--查询员工编号为7369的员工信息
select * from emp where EMPNO=7369;
--查询员工姓名叫做SMITH的员工信息
select * from emp where ename="smith";
--查询薪资大于2000的员工姓名和薪资
select ename,sal from emp where sal>2000;
--查询工作为SALESMAN的所有信息
select * from emp where job="SALESMAN";
--查询部门在20的员工信息
select * from emp where deptno=20;
--查询薪资不等于2000的员工信息
select * from emp where sal<>2000;
select * from emp where sal!=2000;
3.4.2 in: 在某个范围中查找
案例:
--查询员工编号为 7369 7788 7881的员工信息
select * from emp where empno in (7369,7788,7881);
--查询员工编号除了 7369 7788 7881之外的所有员工信息(not)
select * from emp where empno not in (7369,7788,7881);
--查询除了10,20部门之外的所有员工
select * from emp where deptno not in(10,20);
3.4.3 null 值查询
案例:
--查询不发放津贴的员工信息
select * from emp where comm is null; --(is)
--查询发放津贴的员工信息
select * from emp where comm is not null;--(not)
3.4.4 范围比较
案例:
--查询薪资范围在1000-4000之间的员工信息 [1000.4000]
select * from emp where sal between 1000 and 4000; --(between and)
3.4.5 模糊查询 % _
案例:
--查询名字中有S的员工信息
select * from emp where ename like '%s%';
--查询名字最后一个字符是S
select * from emp where ename like '%S';
--查询名字第一个字符是S
select * from emp where ename like 'S%';
--查询名字第二个字符是A
select * from emp where ename like '_A%';
--查询名字倒数第二个字符是A
select * from emp where ename like '%A_';
--查询名字中有%的员工
select * from emp where ename like '%\%%';
注意:1.查询名字第8 188个字符是A,这是需要一些特殊的手段-》函数
2. % 代表任意字符的任意次数 _任意字符的一次
3.4.6 多条件联合查询 and or
and 必须前后同时满足条件才能返回结果
or前后有一个满足条件就能返回结果
案例:
--查询在20部门并且薪资大于2000的员工信息
select * from emp where deptno=20 and sal>2000;
--查询在20部门或者薪资大于2000的员工
select * from emp where deptno=20 or sal>2000;
--查询不在20部门并且薪资小于2000的员工
select * from emp where deptno !=20 and sal<2000;
3.5 select结果排序 order by
使用asc是升序排列(默认),使用desc可以降序排序
4.1案例(单列):
--按照薪资进行排序(默认升序)
select sal from emp order by sal;
--按照薪资进行排序(升序)
select sal from emp order by sal asc;
--按照薪资进行排序(降序)
select sal from emp order by sal desc;
--按照津贴进行排序(null排在最前面)
select comm from emp order by comm;
4.2案例(多列):
--多个排序的列
select deptno,sal from emp order by deptno,sal;
--多个排序的列(部门升序 薪资降序)
select deptno,sal from emp order by deptno,sal desc;
3.6 select结果分页
--查询前4行员工信息
select * from emp limit 4;
--查询从第4行开始,接下来3行的员工信息
select * from emp limit 3,3; --第一行为0
--查询薪资大于1000的逆序排列,然后显示前5条记录
select * from emp where sal>1000 order by sal desc limit 5;
4 单行函数
函数都是数据库提前给我们准备好的,所以我们可以直接调用,使用函数可以让指定的列计算出我
们需要的数据
4.1 概念:指的是操作一行数据返回一行数据,操作10行数据返回10行数据
4.2.字符串函数
案例:
-- 计算员工名字的长度
- select ename,length(ename) from emp;
-- 截取员工名字的前三位字母
select ename,substr(ename,1,3) from emp;
-- 对员工名进行 大小写
select ename,upper(ename),lower(ename);
-- 将员工编号和姓名进行拼接
select concat(empno,'=',ename) from emp;
--将员工姓名中的'T'字母替换成'-'
select ename,REPLACE(ename,'T','-') from emp;
4.3 日期函数
案例:
-- 获取当前系统时间
select now() from emp; --获取年月日,时分秒
select CURDATE() from emp; --获取年月日
select CURTIME() from emp; --获取时分秒
-- 日期转换
select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s')
select DATE_FORMAT(sysdate(),'%Y年%m月%d日 %H时%i分%s秒')
-- 分别获取 年月日 时分秒 星期 --year month day hour minit second week
select year(now()) from dual ; --dual 是一个自带的虚拟的表
-- 日期的加减操作
select hiredate,ADDDATE(hiredate,9),ADDDATE(hiredate,-9) from emp;
select DATE('2023-12-19') from dual;
4.4数字函数
案例:
-- 向上取整 向下取整
select ceil(12.1),floor(12.9) from dual;
-- 保留多少位有效数字
select round(1.4999999,2),round(1.4999999),round(25,-1)from dual; --结果:1.50,1,30
4.5转换函数
-- 日期--》字符串
select now(),DATE_FORMAT(now(),'%Y-%m-%d') from dual;
-- 字符串--》日期
- 要注意字符串和格式的匹配
- select STR_TO_DATE('2020-4-16 17:15:24','%Y-%c-%d %H:%i:%s') from dual;
-- 数字--》字符串
- 直接拼接一个字符串即可,可以自动转换
select length(10+'') from dual;
-- 字符串--》数字
select '10' +20 from dual;
4.6其他函数
案例:
--4.6.1 空值的处理: 使用函数 IFNULL(列名,默认值) :如果列为NULL则给与默认值,不为NULL则使用自己本身的值
select sal,comm,ifnull(comm,0),sal+ifnull(comm,0) from emp;
--4.6.2 加密解密算法
select MD5('123456') from dual;
加密:
select AES_ENCRYPT('123456','abcd') from dual;
--abcd是密钥
解密:
select AES_DECRYPT(AES_ENCRYPT('123456','abcd'),'abcd') from dual;
5练习
##01找出各月倒数第3天受雇的所有员工.
select * from emp where HIREDATE = LAST_DAY(HIREDATE)-2;
##02找出早于12年前受雇的员工.
select * from emp where DATEDIFF(NOW(),HIREDATE)>365*12;
##03以首字母大写的方式显示所有员工的姓名
select ename,CONCAT(SUBSTR(ename,1,1),SUBSTR(LOWER(ename),2)) from emp
##04显示正好为5个字符的员工的姓名.
select ename from emp where LENGTH(ename)=5;
##05显示所有员工姓名的前三个字符.
select ename,SUBSTR(ename,1,3) from emp
##06显示所有员工的姓名,用a替换所有"A"
select ename,REPLACE(ename,'A','a') from emp
##07显示满10年服务年限的员工的姓名和受雇日期.
select ename,HIREDATE from emp where DATEDIFF(NOW(),HIREDATE)>365*10;
##08显示员工的详细资料,按姓名排序.
select * from emp order by ename
##9显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select ename,HIREDATE from emp order by DATEDIFF(now(),HIREDATE) desc;
##10显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
select ename,job,sal from emp order by job desc,sal
##11显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面
select ename,year(hiredate),MONTH(HIREDATE) from emp ORDER BY MONTH(HIREDATE),HIREDATE ;
##12找出在(任何年份的)2月受聘的所有员工.
select * from emp where MONTH(HIREDATE) =2;
##13对于每个员工,显示其加入公司的天数.
select ename,DATEDIFF(now(),HIREDATE) from emp;
##14显示姓名字段的任何位置包含"A"的所有员工的姓名.
select ename from emp where ename like '%A%';