MySQL

最新推荐文章于 2024-03-20 23:36:26 发布
最新推荐文章于 2024-03-20 23:36:26 发布
阅读量1k 收藏 1
点赞数
分类专栏： msb1024 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/abc_123_linbin/article/details/121460153
版权
本文详细介绍了MySQL数据库的基本操作，包括创建、删除和查询表，以及数据的插入、更新和删除。强调了完整性约束的重要性和使用，如主键、外键、唯一性约束、检查约束等。此外，还探讨了不同的外键删除策略，如级联、设为空等。最后，讲解了SQL查询语句、函数、多表查询和事务处理，展示了如何在实践中确保数据的完整性和一致性。
摘要由CSDN通过智能技术生成

1.创建表

--创建表
CREATE TABLE if not EXISTS `t_student`  (
  `sno` int DEFAULT NULL,
  `sname` varchar(10) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(15) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--删除表
drop table t_student;
--查询表结构
desc  t_student;
--查看建表语句
show create table t_student;
-- 查询
select * from t_student;

--插入
insert into t_student values (101,"张三",'男',18,'2021-11-11','高三(1)','wangjl@126.com');
insert into t_student values (102,"张三",'男',18,'2021-11-11','高三(1)','wangjl@126.com');
--修改表中数据
update t_student set sex='女' where sno='102';
update t_student set email='WANGJL@126.com' where sno='102';

update t_student set sex='男' where email='wangjl@126.com'; --注意：影响两行 

#注意 
1.关键字，表名、字段名不区分大小写
2.默认情况下，内容不区分大小写
3.删除操作from关键字不可缺少
4.修改、删除数据别忘记加限制条件

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、字段内容默认情况下是大小写不敏感的。


--删除操作 
delete from t_student where sno='102';

--修改表结构
alter table t_student add score double(5,2);  --总数，小数
--删除列
alter table t_student drop score;
--添加一列放在前面
alter table t_student add score double(5,2) first ; 
alter table t_student add score double(5,2) after age ;  --没起作用
 
update t_student set score=99.5 where sno='101';

2.表的完整性约束

#表的完整性约束
-- check 检查约束mysql8之后

drop table if exists t_student;
-- 创建表
CREATE TABLE if not EXISTS `t_student`  (
  `sno` int(6) primary key auto_increment,
  `sname` varchar(10) not null,
  `sex` char(1) default '男' check(sex='男'||'女'),
  `age` int(3) check(age>=8 and age<=50) ,
  `enterdate` date ,
  `classname` varchar(15) ,
  `email` varchar(20) unique 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 添加数据
insert into t_student values (null,'张三','男',18,'2003-09-01','高三(1)','wangjl@126.com');

insert into t_student values (null,'刘邦','男',18,'2003-09-01','高三(1)','liubang@126.com');

insert into t_student values (null,'关羽','男',18,'2003-09-01','高三(1)','guanyu@126.com');
 
 -- 一条没插入成功 主键会自增一次 浪费一次主键?
 
 insert into t_student values (default,'毛爷爷','男',18,'2003-09-01','高三(1)','mao@126.com');
 -- 主键 null 或default 
 
select * from t_student;

3.修改或添加表的完整性约束

-- 3.表的完整性约束
drop table t_student;
CREATE TABLE if not EXISTS `t_student`  (
  `sno` int(6) ,
  `sname` varchar(10) ,
  `sex` char(1) ,
  `age` int ,
  `enterdate` date ,
  `classname` varchar(15) ,
  `email` varchar(20) ,
	constraint pk_stu primary key(sno), -- pk_stu 主键约束
	constraint ck_stu_sex check(sex='男'|| sex='女'),
	constraint ck_stu_age check(age>=8 and age <=50),
	constraint ck_stu_email unique(email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建表之后添加约束
CREATE TABLE if not EXISTS `t_student`  (
  `sno` int(6) ,
  `sname` varchar(10) ,
  `sex` char(1) ,
  `age` int ,
  `enterdate` date ,
  `classname` varchar(15) ,
  `email` varchar(20) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table t_student add constraint pk_stu primary key (sno);
alter table t_student modify sno int(6) auto_increment;
alter table t_student add constraint ck_stu_sex check(sex='男'|| sex='女');
alter table t_student add constraint ck_stu_age check(age>=8 and age <=50);
alter table t_student add constraint ck_stu_email unique(email);

-- 查看表结构
desc t_student;

4.外键约束

-- 4.外键约束
-- 父表：班级表
create table if not exists t_class(
   cno int(4) primary key auto_increment,
	 cname varchar(20) not null,
	 room char(10)
);

-- 添加班级表数据
insert into t_class values(null,'高三(1)','r001'),(null,'高三(2)','r002'),(null,'高三(3)','r003');

drop table t_student;

-- 学生表
CREATE TABLE if not EXISTS `t_student`  (
  `sno` int(6) primary key auto_increment,
  `sname` varchar(10) not null,
  `sex` char(1) default '男' check(sex='男'||'女'),
  `age` int(3) check(age>=8 and age<=50) ,
  `enterdate` date ,
  `classno` int(4) ,
  `email` varchar(20) unique ,
	constraint fk_stu_classno foreign key (classno) references t_class(cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 添加学生信息
insert into t_student(sno,sname,classno)  values (null,'刘邦',1),(null,'项羽',2),(null,'嬴政',3);

-- 添加外键约束
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno);

-- 外键删除 
-- 策略1： no action 不允许操作
-- 把班级2对应学生的班级 改为null
update t_student set classno =null where classno=2;
-- 删除班级2
delete from t_class where cno =2 ;
select * from t_class;

-- 策略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(cno) on update cascade on delete cascade;

-- 更新
update t_class set cno=5 where cno=3;

-- 策略3 set null 置空操作
-- 删除之前的外键约束
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键  都可以设置null  或者随更新而更新
alter table t_student add constraint fk_stu_classno foreign key(classno) references t_class(cno) on update cascade on delete set null;

-- 试试删除
delete from t_class where cno=1;

-- 2应用场合
-- 1).朋友圈删除 点赞 留言 都删除 -- 级联删除
-- 2).解散班级  对应的学生 职位班级null 就可以  set null

5.DDL和DML的一些其他操作

create table t_student_bak as select * from t_student;

-- 只要表结构
create table t_student_bak2 as select * from t_student where 1=2;

-- 指定条件
create table t_student_bak3 as select * from t_student where sno=4;

-- 删除数据操作：清空数据 保留表结构
delete from t_student;
truncate table t_student;

delete 和 truncate 区别

  • 1.delete 为数据操作语言DML truancate 为数据定于语言DDL
  • 2.delete 操作是将表中所有记录一条一条删除直到删除完，truncate操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，truncate操作的效率更高。
  • 3.delete 操作可以回滚，truncate操作会导致隐式提交，因此不能回滚
  • 4.delete 操作执行成功后返回已删除的行数；截断操作不会返回已删除的行量，结果通常是“Affected rows:0”。delete 操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；truncate操作则会重新从1开始自增。

6.DQL查询

6.1数据准备

仿oracle 自带的表

- 部门表
create table DEPT(
   DEPTNO int(2) not null,
	 DNAME varchar(14),
	 LOC varchar(13)
);
-- 员工表
alter table DEPT add constraint pk_dept primary key (DEPTNO);

create table EMP(
  EMPNO int(4) primary key,
	ENAME varchar(10),
	JOB varchar(9),
	MGR int(4),
	HIREDATE DATE,
	SAL DOUBLE(7,2),
	COMM double(7,2),
	DEPTNO int(2)
);

alter table EMP add constraint fk_deptno foreign key (DEPTNO) references DEPT(DEPTNO);

-- 薪资等级
create table SALGRADE(
  GRADE int primary key,
	LOSAL double(7,2),
	HISAL double(7,2)
);

-- 奖金
create table BONUS(
  ENAME varchar(10),
	JOB varchar(9),
	SAL double(7,2),
	COMM double(7,2)
);

-- 部门表
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

-- 工资等级
INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

INSERT INTO EMP VALUES(7369, 'SMITH',  'CLERK',     7902,DATE_FORMAT('1980-12-17', '%Y-%m-%d'),  800, NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN',  'SALESMAN',  7698,DATE_FORMAT('1981-02-20', '%Y-%m-%d'), 1600,  300, 30);
INSERT INTO EMP VALUES(7521, 'WARD',   'SALESMAN',  7698,DATE_FORMAT('1981-02-22', '%Y-%m-%d'), 1250,  500, 30);
INSERT INTO EMP VALUES(7566, 'JONES',  'MANAGER',   7839,DATE_FORMAT('1981-04-2', '%Y-%m-%d'),  2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN',  7698,DATE_FORMAT('1981-09-28', '%Y-%m-%d'), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE',  'MANAGER',   7839,DATE_FORMAT('1981-05-1', '%Y-%m-%d'),  2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK',  'MANAGER',   7839,DATE_FORMAT('1981-06-9', '%Y-%m-%d'),  2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT',  'ANALYST',   7566,DATE_FORMAT('1982-12-09', '%Y-%m-%d'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING',   'PRESIDENT', NULL,DATE_FORMAT('1981-11-17', '%Y-%m-%d'), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN',  7698,DATE_FORMAT('1981-09-8', '%Y-%m-%d'),  1500,    0, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS',  'CLERK',     7788,DATE_FORMAT('1983-05-12', '%Y-%m-%d'), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES',  'CLERK',     7698,DATE_FORMAT('1981-12-3', '%Y-%m-%d'),   950, NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD',   'ANALYST',   7566,DATE_FORMAT('1981-12-3', '%Y-%m-%d'),  3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK',     7782,DATE_FORMAT('1982-01-23', '%Y-%m-%d'), 1300, NULL, 10);

6.2 查询语句

-- 对emp表查询
select * from EMP;
-- 显示部分列
select empno,ename,sal from EMP ;
-- where 子句
select * from EMP where sal > 2000;
select  empno,ename,sal from EMP where sal > 2000;

-- 起别名 
select  empno 员工编号,ename as 姓名 ,sal as "工 资" from EMP where sal > 2000;

-- 算术运算符
select empno ,ename,sal ,sal+1000 as '涨薪后'  from EMP where sal > 2000;

select empno ,ename,sal ,comm ,sal+comm  from EMP ;

-- 去重操作
select job from EMP;
select distinct jobfrom EMP;
select job ,deptno from EMP;
-- 组合去重
select distinct job ,deptno from EMP;

-- 排序
select * from EMP order by sal asc ; -- 默认升序
select * from EMP order by sal desc; -- 降序
select * from EMP order by sal desc,deptno desc; -- 组合排序 

-- where 子句
select * from EMP where deptno=10;
select * from EMP where deptno>10;
select * from EMP where deptno>=10;
select * from EMP where deptno<10;
select * from EMP where deptno<>10;
select * from EMP where deptno!=10;
select * from EMP where job='CLERK';
select * from EMP where job='clerk';

select * from EMP where binary job='clerk'; -- binary 区分大小写

select * from EMP where HIREDATE < '1981-12-25';

select * from EMP where sal > 1500 and sal < 3000;
select * from EMP where sal between 1500 and 3000 order by sal;

select * from EMP where deptno =10 or deptno=20;
select * from EMP where deptno =10 || deptno=20;
select * from EMP where deptno in(10,20);
-- 模糊查询
select * from EMP where ename like "%A%";

select * from EMP where ename like '__A%';

-- 关于null判断
select * from EMP where comm is null ;
select * from EMP where comm is not  null ;

-- 小括号使用
select * from EMP where job='SALESMAN' OR job ='CLERK' AND sal >=1500;
select * from EMP where job='SALESMAN' OR (job ='CLERK' AND sal >=1500);
select * from EMP where (job='SALESMAN' OR job ='CLERK') AND sal >=1500;

7.函数

-- 7.函数
-- 单行函数
select empno,ename,lower(ename),upper(ename),sal from EMP;

-- 多行函数 （分组函数）
select max(sal),min(sal) ,count(sal),sum(sal),avg(sal) from EMP;

-- 除了多行函数（仅5个），都是单行函数

-- 单行函数
-- 1.字符串函数  
-- subst
select ename ,length(ename),substring(ename,2,6) from EMP;
-- 2.数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) ,mod(10,3) from dual; -- dual 实际是一个伪表
-- 没有where条件 from dual 可以省略 

-- 3.日期与时间函数
select * from EMP;
SELECT CURDATE(),CURTIME();
SELECT NOW(),sysdate(),sleep(3),now() ,sysdate() from dual;

-- 4.流程函数
-- if相关
select empno,ename,sal ,if(sal>=2500,'高薪','底薪') as '薪资等级' 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

-- case相关 
-- case 等值判断
select empno,ename ,job ,
case job 
  when 'CLERK' THEN '店员'
	when 'SALESMAN' THEN '销售'
	when 'MANAGER' THEN '经理'
	else '其他'
end '岗位'
,sal from EMP;

-- case 区间判断

select empno ,ename ,sal ,
case 
  when sal<=1000 then 'A'
	
-- 5.json函数
-- 6.其他函数
select database(),user(),version() from dual;

select PASSWORD('123456') from dual ;

-- 多行函数自动忽略null值 
select max(comm),min(comm) ,count(comm),sum(comm),avg(comm) from EMP;

-- group by 分组
select deptno,avg(sal) from EMP ; -- 字段和多行函数不可以同时使用

select deptno,avg(sal) from EMP group by deptno;  -- 字段和多行函数使用，除非这个字段属于分组

-- 统计每个岗位的平均工资
select job,avg(sal) from EMP group by job;


-- having 
-- 分组之后 筛选
select deptno, avg(sal)  平均工资 from EMP group by deptno having avg(sal) > 2000;
select deptno, avg(sal)  平均工资 from EMP group by deptno having avg(sal) > 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在分组后过滤 

-- 单表查询练习 
-- 1.列出工资最小值小于2000的职位 

select job,avg(sal)   from EMP group by job;
select job,avg(sal)   from EMP group by job having (avg(sal) < 2000);

-- 2.列出平均工资大于1200元的部门和工作搭配组合 
select deptno ,job ,avg(sal) from EMP group by deptno,job ;
select deptno ,job ,avg(sal) from EMP group by deptno,job having avg(sal) >1200 order by avg(sal);

-- 3.统计人数小于4的部门的平均工资 
select deptno ,count(1) from EMP group by deptno ;
select deptno ,count(1),avg(sal) from EMP group by deptno;
select deptno ,count(1),avg(sal) from EMP group by deptno having count(1) <4;

-- 4.统计各部门的最高工资，排除最高工资小于3000的部门 

select  deptno ,avg(sal) , max(sal) from EMP group by deptno having max(sal) <3000;

8.多表查询

-- 查询员工编号 姓名 部门编号 
select empno ,ename, deptno from EMP ;

-- 查询员工编号 姓名 部门编号 部门名称 
select t1.empno ,t1.ename, t1.deptno, t2.DNAME from EMP t1 , DEPT t2 where t1.DEPTNO=t2.DEPTNO ;

-- 交叉连接 cross 可以省略  oracle中不可以省略 
select * from EMP cross join DEPT ;-- 笛卡尔乘积 没有实际意义 有理论意义 

-- 自然连接 
-- 优点 自动匹配所有的同名词，同名列只展示一次，简单
select * from EMP natural join DEPT ;

-- 查询字段的时候，没有指定字段所属的数据库表，效率低 
select empno ,ename ,sal,dname , loc from EMP natural join DEPT ;
-- 解决  
select EMP.empno ,EMP.ename ,EMP.sal,DEPT.dname , DEPT.loc from EMP natural join DEPT ;
-- 表起别名 

-- 自然连接 缺点 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列。
-- 解决 内连接 using 子句
-- inner join  inner 可以不写 
select * from EMP e join DEPT d USING(DEPTNO); -- 这里不能写natural join，实际是内连接 

-- using 关联的字段必须是同名的  
-- 解决： 内连接  on 子句 
select * from EMP e inner join DEPT d on (e.DEPTNO =d.DEPTNO);


-- 多表连接查询的类型 1 交叉连接 cross join 2 .自然连接 natural join 
-- 3.内连接 using 4.内连接 on子句

-- 条件
-- 1.筛选提交: where having 
-- 2.连接条件 on,using natural 
-- 3.SQLL99语法 筛选条件和连接条件是分开的 

-- inner join  -- on 子句 

select * 
from EMP e 
inner join DEPT d 
on e.DEPTNO = d.DEPTNO;

select * from EMP ;
SELECT * FROM DEPT ;

-- 问题 
-- 1.40号部门没有员工 但是没有显示在查询结果中 
-- 2.员工scott没有部门，没有显示在结果中 

-- 外连接 除了显示匹配的数据之外 还可以显示不匹配的数据 

-- 左外连接  left outer join  左表
select * 
from EMP e 
left outer  join DEPT d 
on e.DEPTNO = d.DEPTNO;

-- 右外连接 
select * 
from EMP e 
right outer  join DEPT d 
on e.DEPTNO = d.DEPTNO;

-- 全外连接 MySQL 不支持 oracle支持 
select * 
from EMP e  
full  outer  join DEPT d 
on e.DEPTNO = d.DEPTNO;

-- 解决MySQL不支持全外连接  并集 
select * 
from EMP e  
left   outer  join DEPT d 
on e.DEPTNO = d.DEPTNO
union -- 并集 
select * 
from EMP e  
right  outer  join DEPT d 
on e.DEPTNO = d.DEPTNO;

-- union all  不去重 效率高 
select * 
from EMP e  
left   outer  join DEPT d 
on e.DEPTNO = d.DEPTNO
union all-- 并集 
select * 
from EMP e  
right  outer  join DEPT d 
on e.DEPTNO = d.DEPTNO;


-- mysql中对接好操作支持比较弱，只支持并集操作 差集不支持 



-- 查询员工的编号 姓名 薪水 部门编号 部门名称 薪水等级 

select * from EMP;
SELECT * FROM DEPT ;
SELECT * FROM SALGRADE;

SELECT e.ename ,e.ename,e.SAL,e.DEPTNO,d.DNAME,e.SAL FROM EMP e 
right outer join DEPT d 
on e.DEPTNO = d.DEPTNO
inner join SALGRADE s 
on e.SAL BETWEEN s.LOSAL and s.HISAL ;


-- 自连接 
select * from EMP;

-- 左外连接
select e1.EMPNO 员工编号 ,e1.ENAME 员工姓名 ,e1.MGR 经理编号 ,e2.ename 经理名称 from EMP e1
left outer join EMP e2 
on e1.MGR=e2.EMPNO;

-- 子查询
-- 查询所有比“CLARK” 工资高的员工的信息
select sal from EMP where ename ="CLARK" ;
select * from EMP where sal> 2450.00;
select * from EMP where sal >(select sal from EMP where ename ="CLARK");

-- 单行子查询 
-- 查询工资高于平均工资的雇员名字和工资 
select ename,sal 
from EMP 
where sal > (select avg(sal) from EMP );

-- 查询和clark同一部门且比他工资低的雇员名字和工资 

select ename ,sal from EMP 
where deptno =(select deptno from EMP where ename='CLARK') 
AND sal <(select sal from EMP where ename ="CLARK" );
 
-- 查询职务和scott相同 比scott雇佣的时间早的雇员信息。

select * from EMP 
WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT') 
and hiredate <(select hiredate from EMP where ename="SCOTT");

-- 多行子查询 
-- 1.查询【部门20中职务同部门10的雇员一样的】雇员信息
select JOB from EMP where DEPTNO=10;
select * from EMP where DEPTNO=20;

SELECT * FROM EMP WHERE DEPTNO=20
AND JOB IN (SELECT JOB FROM EMP WHERE DEPTNO=10);



-- 2.查询工资比所有的“SALESMANE”都高的雇员的编号，姓名和工资 
select ename ,EMPNO ,sal from EMP 
WHERE SAL >(SELECT max(SAL) FROM EMP where job='SALESMAN' );

-- 3.查询工资低于任意一个clerk的工资的雇员信息 
select * from EMP 
WHERE sal <(SELECT MAX(sal) FROM EMP WHERE job ='CLERK') 
and 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)
UNION 
select * from EMP WHERE DEPTNO =20 AND SAL=(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20)
UNION  
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 e.deptno;

-- 查询工资高于其所在岗位的平均工资的那些员工 
SELECT * FROM EMP WHERE JOB ='CLERK' AND SAL>=(SELECT AVG(SAL) WHERE JOB ='CLERK');

SELECT * FROM EMP e WHERE SAL>=(SELECT AVG(SAL) from EMP WHERE JOB=e.JOB);

9.事物

-- 事物的特性 
   原子性 要么都成功 要么都失败
   一致性 从一个一致性状态，变成另一个一致性状态
   隔离性 各个事物互不干扰
   持久性 事物一旦提交，对数据所作的改变，都要记录存储器中 
	 
 -- 创建账户表 
 create table account(
   id int primary key auto_increment,
	 uname varchar(10) not null,
	 balance double 
 );
 select * from account;
 insert into account values( null,'a',2000),(null,'b',2000);
 
 update account set balance =balance -200 where id=2;
 update account set balance =balance +200 where id=2;
 
 -- 手动开启事物
 start TRANSACTION ;
  update account set balance =balance -200 where id=1;
 update account set balance =balance +200 where id=2;
 
 -- 手动回滚 
 ROLLBACK ;
 -- 手动提交 
 commit ;
 
 -- 事物并发问题 
    脏读       一个事物读到了另一个事物还未提交的数据。
		不可重读读 一个事物多次读到的数据不一致。数据修改 
		幻读       插入一条记录 或删除一条记录  读到数据条数不同 
		
-- 事物隔离级别                   脏读 不可重读读 幻读
   read uncommitted   读未提交   √    √          √
   read committed     读已提交   X    √          √
   repeatable read    可重复读   X    X          √
   serializable       序列化     X    X          X 
	 
-- 查看事物隔离级别 
select @@transaction_isolation;

-- 设置事物的隔离级别 （设置当前会话的隔离级别）
set session transaction isolation level read uncommitted ;
set session transaction isolation level read committed ;
set session transaction isolation level repeatable read ;
set session transaction isolation level serializable  ;

10.视图

select * from EMP; 

-- 创建单表的视图
create view myview1 
as 
select EMPNO,ENAME ,JOB,DEPTNO from EMP 
WHERE DEPTNO =20;
-- 查看视图
SELECT * FROM myview1;
-- 插入数据 实际表中也增加一行数据
insert into myview1 values(9999,'Lucy','CLERK',20);
-- 视图中没有 但是实际表中插入一条数据
insert into myview1 values(8888,'James','CLERK',30);

-- 删除视图
drop view myview1;

-- 创建视图-校验
create view myview1
as 
select EMPNO ,ENAME,JOB,DEPTNO 
FROM EMP 
where deptno=20
with check  option; 

-- 插入一条数据
insert into myview1 values(9999,'Lucy','CLERK',20);
-- CHECK OPTION failed 'mysqldb.myview1'  校验不通过
insert into myview1 values(8888,'James','CLERK',30);

-- 创建多表视图
create or replace view myview02
as 
select e.empno ,e.ename,e.sal,d.deptno,d.dname 
from EMP e join DEPT d 
on e.deptno = d.deptno 
where e.sal>2000;

select * from myview02;
-- 创建统计视图 
create or replace view myview03
as 
select e.deptno,d.dname ,avg(e.sal),min(e.sal),count(1)
from EMP e
join DEPT d 
using(deptno)
group by e.deptno;

select * from myview03;
-- 创建基于视图的视图
create or replace view myview04
as 
select * from myview03
where deptno =20;

select * from myview04;

11.存储过程

select * from EMP where ename like "%A%";

create procedure mypro01( name varchar(10))
begin 
  if name is null or name ="" then 
	   select * from EMP ;
	else 
	    select * from EMP where ename like concat('%',name,'%');
	end if;
end ;
		 
-- 删除 存储过程
drop procedure mypro01;

-- 调用存储过程
call mypro01(null);

call mypro01('A');

-- 定义一个 有返回值的存储过程 
create procedure mypro2(in name varchar(10),out num int(3))
begin 
   if name is null or name ="" then 
	    select * from EMP;
	 else 
	    select * from emp where ename like concat('%',name,'%');
		end if ;
		select found_rows() into num;
end;

call mypro2(null,@num);

select @num;
_wjl_
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL权限管理
不再疯要傻
11-25 1万+
用户和权限管理： 语法 grant 权限 on 数据库.数据表 to '用户' @ '主机名'; 例：给 xiaogang 分配所有的权限 grant all on *.* to 'xiaogang'@'%'; 这个时候 xiaogang 就拥有了 所有权限了   权限列表 权限 说明 举例 usage 连接（登陆）权
MySQL中Truncate用法详解
justlpf的专栏
05-09 3188
当我们想要清空某张表时，往往会使用truncate语句。大多时候我们只关心能否满足需求，而不去想这类语句的使用场景及注意事项。本篇文章主要介绍truncate语句的使用方法及注意事项。
Truncate用法详解
Data & Analysis
04-08 4882
前言： 当我们想要清空某张表时，往往会使用truncate语句。大多时候我们只关心能否满足需求，而不去想这类语句的使用场景及注意事项。本篇文章主要介绍truncate语句的使用方法及注意事项。 1.truncate使用语法 truncate的作用是清空表或者说是截断表，只能作用于表。truncate的语法很简单，后面直接跟表名即可，例如：truncate table tb...
oracle数据库赋某表truncate权限
开发者导航
08-28 4006
oracle中truncate其他用户的表必须要有drop any table权限，如果没有此权限，可以采用下面的方法：一、ins用户下创建存储过程createorreplaceprocedurep_trun_link_odsois sqlstrvarchar2(2000); begin sqlstr:='truncatetab...
mysql truncate 授权_Oracle给用户授权truncatetable的实现方案
weixin_28687807的博客
01-19 762
1，对其它用户下的表执行trundate table操作开发说在用dwetl下执行调用shop用户下的表的时候提示没有权限操作，google了查了下，发现oracle账户没法直接赋予对某个表的truncate权限，那要怎么来实现呢？在shop用户下面，准备测试数据SQL> create table Z_TRUNCATE_T(ID number);Table created.SQL> i...
MySQL实践——MySQL权限
JC的博客
10-11 1142
MySQL的mysql 系统库提供了user、db、tables_priv、columns_priv、procs_priv、proxies_priv几个表，用于存放不同权限范围的用户账号相关数据，这些表共同组成了MySQL的访问权限控制系统。MySQL访问权限控制系统的主要功能是对从给定主机连接到MySQL服务器的用户进行身份验证，并校验该用户在该服务器中的数据库对象访问权限（如SELECT、INSERT、UPDATE和DELETE）。
MySql 8.0.37
最新发布
05-30
MySql 8.0.37
mysql8.0升级版本
12-12
MySQL 8.0 升级版本 在本文中，我们将详细介绍 MySQL 8.0 升级版本的步骤和注意事项。MySQL 是一个流行的开源关系数据库管理系统，广泛应用于各种Web 应用程序中。升级 MySQL 版本可以提高数据库性能、修复 bugs 和...
深入浅出MySQL全文
06-29
深入浅出MySQL全文 MySQL 是一种关系型数据库管理系统，广泛应用于各种行业和领域。本资源将从基础篇开始，详细讲解 MySQL 的安装、配置、启动和关闭服务、SQL 基础等知识点。 MySQL 的安装与配置 MySQL 的安装是...
全国城市商圈经纬度MYSQL版
05-08
全国城市商圈经纬度MYSQL版，全国城市商圈经纬度MYSQL版，全国城市商圈经纬度MYSQL版，全国城市商圈经纬度MYSQL版，全国城市商圈经纬度MYSQL版，全国城市商圈经纬度MYSQL版，全国城市商圈经纬度MYSQL版，全国城市...
MySQL5.5.62
11-06
MySQL 5.5.62 是 MySQL 数据库管理系统的一个重要版本，主要针对 Windows 操作系统提供了32位和64位的安装包。MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由 Oracle 公司维护。它以其高效、稳定和...
oracle给表赋清空权限,Oracle数据库之oracle赋某表truncate权限
weixin_39931390的博客
04-03 1503
本文主要向大家介绍了Oracle数据库之oracle赋某表truncate权限，通过具体的内容向大家展现，希望对大家学习Oracle数据库有所帮助。一、ins用户下创建存储过程二、mobapp用户下赋权：执行存储过程的权限三、odso_insert用户下执行存储过程，即可truncate表查看odso_insert用户拥有的权限createorreplaceprocedurep_trun_...
mysql revoke 列权限_MySQL权限操作：Grant、Revoke
weixin_27330481的博客
01-20 385
数据库操作：创建数据库、创建表——CREATE删除数据库、删除表——DROP删除表内容——TRUNCATE、DELETE(后者效率低、一行一行地删除记录)查询数据库、查询表——SELECT插入、更新、删除——INSERT INTO、UPDATE、DELETE(上面有提到)修改表——ALTER(添加、修改或删除列)MySQL各种权限(27种)：参考http://www.cnblogs.com/sub...
MySQL权限详解
Daturexx的博客
05-19 3370
MySQL权限级别介绍 MySQL权限级别 全局性的管理权限，作用于整个MySQL实例级别 数据库级别的权限，作用于某个指定的数据库上或者所有的数据库上 数据库对象级别的权限，作用于指定的数据库对象上(表、视图等)或 者所有的数据库对象上 权限存储在mysql库的user, db, tables_priv, columns_priv, and procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中 MySQL权限级别介绍 对比root用户在几个权限系统表中的数据
mysql 授权truncate_有没有办法在MySQL中的TRUNCATE或DROP TABLE上有效地进行GRANT？
weixin_31313071的博客
02-07 1346
我最近在MySQL 5.5.x中试过这个：GRANTSELECT, INSERT, UPDATE, DELETE, TRUNCATE ON crawler.*TO 'my_user'@'localhost' WITH GRANT OPTION;这会导致错误：ERROR 1064 (42000): You have an error in your SQL syntax; check the man...
MySQL数据库基本操作（增删改查）与用户授权
qq_64612585的博客
03-20 2054
grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [identifided by '密码'];# 权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert, update”。使用"all"表示所有权限，可授权执行任何操作。# 数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。例如，使用"school.*"表示授权操作的对象为school数据库中的所有表。
mysql truncate命令
liuzhilong的博客
03-19 6219
truncate命令结果跟delete很像，但是他们的执行原理完全不一样。truncate本质还是ddl语句 1.truncate先使用create命令创建表，然后drop源表，最后rename新表。 2drop只是删除元数据，所以比delete快很多，特别是大表 3 truncate本质是ddl，需要ddl权限。ddl本身是自提交的，所以truncate也不能rollback回滚 4 因为是truncate是重建表，所以truncate是可以整理表碎片的（delete不可以） 5 trunca.
SQL中Truncate的用法（转）
weixin_30263073的博客
09-18 3913
转自：http://www.studyofnet.com/news/555.html 本文导读：删除表中的数据的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行，而不记录单个行删除操作。TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似；但是，TRUNCATE TABLE 速度更快，使用的系统资源和事务日志资源更少。下面介...
完整卸载MySQL指南
"本文将详细介绍如何彻底卸载MySQL，包括停止服务、卸载程序、删除相关文件夹以及清理注册表，以确保完全移除MySQL并为重新安装做好准备。" 彻底卸载MySQL的过程是一个系统性的操作，需要确保所有与MySQL相关的组件...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值