Mysql基础(网安)
MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度。显示宽度并不限制可以在列内保从的值的范围,也不限制超过列的指定宽度的值的显示
主键自增:不使用序列,通过auto_increment,要求是整数类型
与整数类型不一样的是,浮点数类型的宽度不会自动扩充。 score double(4,1)
score double(4,1)--小数部分为1位,总宽度4位,并且不会自动扩充。
CHAR和VARCHAR类型相似,均用于存于较短的字符串,主要的不同之处在于存储方式。CHAR类型长度固定,VARCHAR类型的长度可变。
因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小,所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型,这样可以大大地节约磁盘空间、提高存储效率。
CHAR和VARCHAR表示的是字符的个数,而不是字节的个数
写入当前时间 now(),sysdate(),CURRENT_DATE()
insert into t_student values (1,'zhangsan','nan',18,'200-5-8','ruanjian','123@qq.com');
char varchar是字符的个数,不是字节的个数,可以使用binary,varbinary表示定长和不定长的字节个数
insert into t_student (sno,sname,enterdate) values (10,'lisi','2023-7-5');
update t_student set sex = 'nv' where sno=10;
delete from t_student where sno = 2;
alter table t_student add score double(5,2);//5:总位数 2:小数位数
alter table t_student drop score;
alter table t_student modify score float(4,1);//modify修改的是类型的定义,但是不会改变列的名字
alter table t_student change score score1 double(5,1);//change修改列名和列的类型的定义
主键可以是单个字段,也可以是多个字段组合。对于单字段主键的添加可使用表级约束,也可以使用列级约束;而对于多字段主键的添加只能使用表级约束。
规定了一张表中指定的某个字段的值不能为空(NULL)。设置了非空约束的字段,在插入的数据为NULL时,数据库会提示错误,导致数据无法插入。
无论是单个字段还是多个字段非空约束的添加只能使用列级约束(非空约束无表级约束)
alter table student8 modify stu_sex varchar(1) not null;
alter table student8 modity stu_sex varchar(1) null;
它规定了一张表中指定的某个字段的值不能重复,即这一字段的每个值都是唯一的。如果想要某个字段的值不重复,那么就可以为该字段添加为唯一约束。
无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束
用来限制某个字段的取值范围,可以定义为列级约束,也可以定义为表级约束。MySQL8开始支持检查约束。
默认值约束(DEFAULT)用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值,那么该字段的值将会被默认值填充。
由于自增约束会自动生成唯一的ID,所以自增约束通常会配合主键使用,并且只适用于整数类型。一般情况下,设置为自增约束字段的值会从1开始,每增加一条记录,该字段的值加1。
alter table student11 modify stu_id int(10) auto_increment;
alter table student11 modify stu_id int(10);
cno int(4) primary key auto_increment,
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
sno int(6) primary key auto_increment,
constraint fk_stu_classno foreign key (classno) references t_class(cno)
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)
update t_student set classno = null where classno = 2;
delete from t_class where cno = 2;
2.cascade级联操作:操作主表的时候影响从表的外键信息
alter table t_student drop foreign key fk_stu_classno;
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class
update t_class set cno = 5 where cno = 3;
delete from t_class where cno = 5;
alter table t_student drop foreign key fk_stu_classno;
update t_class set cno = 8 where cno = 1;
解散班级,对应的学生 置为班级为null就可以了,-- set null
从最终的结果来看,虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录,但是两者还是有很多区别的,其区别主要体现在以下几个方面:
(1)DELETE为数据操作语言DML;TRUNCATE为数据定义语言DDL。
(2) DELETE操作是将表中所有记录一条一条删除直到删除完;TRUNCATE操作则是保留了表的结构,重新创建了这个表,所有的状态都相当于新表。因此,TRUNCATE操作的效率更高。
(3)DELETE操作可以回滚;TRUNCATE操作会导致隐式提交,因此不能回滚(在第十章中会讲解事务的提交和回滚)。
select empno,ename,,sal from emp;
select * from emp where sal > 2000;
select empno,ename,job,mgr from emp where sal > 2000;
select empno c,ename b,sal a from emp;
select empno as c,ename as b,sal as a from emp;
select empno '1',ename '2',sal '3' from emp;
select empno,ename,sal,sal+1000,deptno from emp where sal < 2500;
select empno,ename,sal,comm,sal+comm from emp;
select * from emp order by sal; //默认情况下是按照升序排列的
select * from emp order by sal desc; //desc降序
select *from emp order by sal asc, deptno desc; //在sal升序的情况下,按deptno降序
select * from emp where deptno = 10;
select * from emp where sal > 1500 and sal < 3000; -- (1500,3000)
select * from emp where deptno = 10 or deptno = 20;
select * from emp where ename like '%A%' ;
select * from emp where comm is null;
小括号的使用 :因为不同的运算符的优先级别不同,加括号为了可读性
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or and > or
函数的功能:分装了一定的一些功能,直接拿过来使用,可以实现一定的功能
注意:函数没有改变数据自身的值,而是在真实数据的上面进行加工处理,展示新的结果而已
单行函数是指对每一条记录输入值进行计算,并得到相应的计算结果,然后返回给用户,也就是说,每条记录作为一个输入参数,经过函数计算得到每条记录的计算结果。
常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。
select ename,length(ename),substring(ename,2,3) from emp;
substring字符串截取,2:从字符下标为2开始,3:截取长度(下标从1开始)
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual;
select abs(-5) "1",ceil(5.3) '2',floor(5.9) '3',round(3.14) '4';
select now(),sysdate(),sleep(3),now(),sysdate() from dual;
insert into emp values (9999,'lili','SALASMAN',6532,now(),1000,null,30);
select empno,ename,sal,if(sal>=2500,"gao","di") as "dengji" from emp;
select empno,ename,sal,comm,sal+ifnull(comm,0) from emp;
select nullif(1,1),nullif(1,2) from dual; -- 如果value1等于value2,则返回null,否则返回value1
select database(),user(),version() from dual;
多行函数是指对一组数据进行运算,针对这一组数据(多行记录)只返回一个结果,也称为分组函数。
除了多行函数(max,min,count,sum,avg)都是单行函数
select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
max(),min(),count()针对所有类型 sum(),avg() 只针对数值型类型有效
select deptno,avg(sal) from emp; -- 字段和多行函数不可以同时使用
select deptno,avg(sal) from emp group by deptno; -- 字段和多行函数不可以同时使用,除非这个字段属于分组
select deptno,avg(sal) from emp group by deptno order by deptno desc;
select job,avg(sal) from emp group by job;
select job,lower(job),avg(sal) from emp group by job;
统计各个部门的平均工资 ,只显示平均工资2000以上的 - 分组以后进行二次筛选 having
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;
select job,avg(sal) from emp where job != 'MANAGER' group by job;
select job,avg(sal) from emp group by job having job != 'MANAGER' ;
where在分组前进行过滤的,having在分组后进行后滤。
select column, group_function(column)
[group by group_by_expression]
from--where -- group by– select - having- order by
select deptno,count(1),avg(sal)
-- 统计各部门的最高工资,排除最高工资小于3000的部门。
实际开发中往往需要针对两张甚至更多张数据表进行操作,而这多张表之间需要使用主键和外键关联在一起,然后使用连接查询来查询多张表中满足要求的数据记录。
一条SQL语句查询多个表,得到一个结果,包含多个表的数据。效率高。在SQL99中,连接查询需要使用join关键字实现。
提供了多种连接查询的类型: cross natural using on
交叉连接(CROSS JOIN)是对两个或者多个表进行笛卡儿积操作,所谓笛卡儿积就是关系代数里的一个概念,表示两个表中的每一行数据任意组合的结果。
select empno,ename,deptno from emp;
cross join dept; -- 14*4 = 56条 笛卡尔乘积 : 没有实际意义,有理论意义
join dept; -- cross 可以省略不写,mysql中可以,oracle中不可以
-- 优点:自动匹配所有的同名列 ,同名列只展示一次 ,简单
select empno,ename,sal,dname,loc
-- 缺点: 查询字段的时候,没有指定字段所属的数据库表,效率低
select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno
select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
-- 自然连接 natural join 缺点:自动匹配表中所有的同名列,但是有时候我们希望只匹配部分同名列:
inner join dept d -- inner可以不写
using(deptno) -- 这里不能写natural join了 ,这里是内连接
-- 多表连接查询的类型: 1.交叉连接 cross join 2. 自然连接 natural join
-- 3. 内连接 - using子句 4.内连接 - on子句
inner join - on子句: 显示的是所有匹配的信息
-- 外连接:除了显示匹配的数据之外,还可以显示不匹配的数据
-- 左外连接: left outer join -- 左面的那个表的信息,即使不匹配也可以查看出效果
-- 右外连接: right outer join -- 右面的那个表的信息,即使不匹配也可以查看出效果
-- 全外连接 full outer join -- 这个语法在mysql中不支持,在oracle中支持 -- 展示左,右表全部不匹配的数据
-- mysql中对集合操作支持比较弱,只支持并集操作,交集,差集不支持(oracle中支持)
-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级
select e.ename,e.sal,e.empno,e.deptno,d.dname,s.*
on e.sal between s.losal and s.hisal
where sal > (select avg(sal) from emp);
-- 查询和CLARK同一部门且比他工资低的雇员名字和工资。
where deptno = (select deptno from emp where ename = 'CLARK')
sal < (select sal from emp where ename = 'CLARK')
-- 查询职务和SCOTT相同,比SCOTT雇佣时间早的雇员信息
where job = (select job from emp where ename = 'SCOTT')
hiredate < (select hiredate from emp where ename = 'SCOTT')
-- 【1】查询【部门20中职务同部门10的雇员一样的】雇员信息。
select * from emp where deptno = 20;-- CLERK,MANAGER,ANALYST
select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK
and job in (select job from emp where deptno = 10)
-- > Subquery returns more than 1 row
and job = any(select job from emp where deptno = 10)
-- 【2】查询工资比所有的"SALESMAN"都高的雇员的编号、名字和工资。
select empno,ename,sal from emp
select sal from emp where job = 'SALESMAN'
-- 查询工资比所有的"SALESMAN"都高的雇员的编号、名字和工资。
where sal > all(select sal from emp where job = 'SALESMAN');
where sal > (select max(sal) from emp where job = 'SALESMAN');
-- 【3】查询工资低于任意一个"CLERK"的工资的雇员信息。
where sal < any(select sal from emp where job = 'CLERK')
where sal < (select max(sal) from emp where job = 'CLERK')
不相关的子查询:子查询可以独立运行,先运行子查询,再运行外查询。
相关子查询:子查询不可以独立运行,并且先运行外查询,再运行子查询
好处:简单 功能强大(一些使用不相关子查询不能实现或者实现繁琐的子查询,可以使用相关子查询实现)
select * from emp where sal = (select max(sal) from emp)
select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30)
select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno
-- 【3】查询工资高于其所在岗位的平均工资的那些员工 (相关子查询)
select * from emp where job = 'CLERK' and sal >= (select avg(sal) from emp where job = 'CLERK')
select * from emp e where sal >= (select avg(sal) from emp e2 where e2.job = e.job)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析