侧边栏壁纸
博主头像
Angel博主等级

行动起来，活在当下

  • 累计撰写 20 篇文章
  • 累计创建 8 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录
mysql

mysql数据库查询

Angel
Angel
2023-07-15 / 0 评论 / 0 点赞 / 66 阅读 / 36522 字
温馨提示：
本文最后更新于 2023-07-16，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

***根据命令创建数据库表

##创建数据库表
create table t_student(
  sno int(6),
  sname varchar(5),
  sex char(1),
  age int(3),
  enterdate date,
  classname varchar(10),
  email varchar(15)
)

##查看表的结构：展示表的字段详细信息
desc t_student;

##查看表中数据
select * from t_student;

##查看建表语句
show create table t_student;

/*CREATE TABLE `t_student` (
  `sno` int DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

***数据库中插入数据

-- 查看表记录：
select * from t_student;

-- 在数据库中插入数据
insert into t_student values (1, '张三','男',18,'2018-9-11','软件一班','123@163.com');
## 时间的方式多样
insert into t_student values (1, '张三','男',18,'2018/9/11','软件一班','123@163.com');
insert into t_student values (1, '张三','男',18,'2018.9.11','软件一班','123@163.com');

## 写入当前的时间  now(), sysdate(), CURRENT_DATE()
insert into t_student values (1, '张三','男',18,now(),'软件一班','123@163.com');
insert into t_student values (1, '张三','男',18,current_date(),'软件一班','123@163.com');

## char varchar 是字符的个数,不是字节的个数，可以使用binary， varbinary表示定长和不定长的字节个数
-- 报错 1406 - Data too long for column 'sname' at row 1
insert into t_student values (2, '张三三三三三','男',18,current_date(),'软件一班','123@163.com'); -- 插入不成功

## 插入指定字段值
insert into t_student (sno, sname, enterdate, email) values (3, '张三','2022-9-1','123@163.com');

***修改删除数据库数据

-- 查看表中记录
select * from t_student;

-- 修改表中数据
update t_student set sex = '女' where sno = 1;

-- 删除表中数据
delete from t_student where sno = 3;

***增加 修改 删除 数据库表

-- 查看数据：
select * from t_student;

-- 修改表的结构
## 增加一列：
alter table t_student add score double(5,2); -- 5：总位数  2：小数位数
##  数据库插入一条数据
insert into t_student (sno, sname, enterdate) values (2, '李四', now())
## 将 son = 2 的数据的score数据进行修改
update t_student set score = 123.5678 where sno = 2;


-- 删除一列：
alter table t_student drop score;
## 增加一列：(放在最前面)
alter table t_student add score double(5,2) first;
## 增加一列：(放在age后面)
alter table t_student add score double(5,2) after age;
update t_student set score = 123 where sno = 1;

-- 修改一列： 
alter table t_student modify score float(4,1); -- modify 修改的是列的类型定义，但是不会改变列的名字
alter table t_student change score score1 double(5,1); -- change 修改列明和列的类型定义

-- 删除表
drop table t_student;

***约束

-- 主键约束 
## 主键的作用： 1. 可以通过主键查找到为一期的一条记录  2. 主键是整数型 需要自增
##创建数据库表
create table t_student(
  sno int(6) primary key auto_increment, -- primary key 唯一; auto_increment  自增
  sname varchar(5) not null, -- not null 不能为空
  sex char(1) default '男' check(sex='男' || sex='女'), -- default默认值  check 检查
  age int(3) check(age>=18 and age<=50),
  enterdate date,
  classname varchar(10),
  email varchar(15) unique -- unique 唯一约束
)


-- 外键约束
create table t_class(
  c_id int(4) primary key auto_increment,
  c_name varchar(10) not null,
  room char(5)
)

alter table t_class modify room char(5);

insert into t_class values (default, '1班', 'r101'),(default, '2班', 'r102'),(default, '3班', 'r103');

select * from t_class;

create table t_student(
  s_id int(4) primary key auto_increment,
  sname varchar(5) not null,
  age int not null,
  classid int(4),
  constraint fk_stu_classno foreign key (classid) references t_class (c_id)
)

insert into t_student values (default,'张三',18, 1),(default,'李四',19, 1),(default,'王五',21, 2);
insert into t_student values (default, '丽丽', 20, 3)

select * from t_student;
drop table t_student;

delete from t_class where c_id=1;

***外键策略

create table t_class(
  c_id int(4) primary key auto_increment,
  c_name varchar(10) not null,
  room char(5)
)

insert into t_class values (default, '1班', 'r101'),(default, '2班', 'r102'),(default, '3班', 'r103');

create table t_student(
  s_id int(4) primary key auto_increment,
  sname varchar(5) not null,
  age int not null,
  classid int(4),
  constraint fk_stu_classno foreign key (classid) references t_class (c_id)
)

insert into t_student values (default,'张三',18, 1),(default,'李四',19, 1),(default,'王五',21, 2);
insert into t_student values (default, '丽丽', 22, 3)

-- 查看班级表和学生表
select * from t_class;
select * from t_student;


drop table t_student;
drop table t_class;



-- 删除班级2： 如果直接删除的话会报错  因为有外键约束
-- 加入外键策略：
-- 策略一： no action 不允许操作
## 1.先把2班对应的学生班级改为null
update t_student set classid=null where classid=2;
## 2.在删除班级2
delete from t_class where c_id=2;

-- 策略二：cascade 级联操作 操作主表影响从表
## 1. 先删除外键约束
alter table t_student drop foreign key fk_stu_classno;
## 2. 在重新添加外键约束
alter table t_student add constraint fk_stu_classno foreign key (classid) references t_class (c_id) on update cascade on delete cascade;
-- 试试更新
update t_class set c_id=5 where c_id=3;
update t_class set c_id=3 where c_id=5;
-- 试试删除
delete from t_class where c_id=5;


-- 策略三 set null 置空操作
## 1. 先删除外键约束
alter table t_student drop foreign key fk_stu_classno;
## 2. 在重新添加外键约束
alter table t_student add constraint fk_stu_classno foreign key (classid) references t_class (c_id) on update set null on delete set null;

-- 试试更新
update t_class set c_id=8 where c_id=1;

***快速添加

-- 快速添加和上一个表一样的结构和数据
create table t_student2
as
select * from t_student;


-- 快速添加和上一个表结构一致但是不包括数据
create table t_student3
as
select * from t_student where 1=2; ## 1不等于2 所以数据不会复制  只会复制结构


-- 快速添加只有部分列  部分数据
create table t_student4
as
select s_id, sname, age from t_student where sno=2;

***删除数据

-- 删除数据操作： 清空数据
delete from t_student;   -- 数据是一条一条的删  执行效率低
truncate table t_student; -- 把表里的数据全部删除  重新创建一个结构相同的表
  • 过程中添加的表
    -- 部门表
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 (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK'),(20, 'RESEARCH', 'DALLAS'),(30, 'SALES', 'CHICAGO'),(40, 'OPERATIONS', 'BOSTON');

insert into EMP values (7369, 'SMITH','CLERK',7902 , '1980-12-17',800 ,null,20),
(7499, 'ALLEN','SALESMAN',7698 , '1981-02-20',1600 ,300,30),
(7521, 'WARD','SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES','MANAGER', 7839, '1981-04-02',2975 ,null ,20 ),
(7654, 'MARTIN','SALESMAN', 7698, '1981-09-28',1250 ,1400 ,30 ),
(7698, 'BLAKE','MANAGER', 7839, '1981-05-01', 2850, null, 30),
(7782, 'CLARK','MANAGER', 7839, '1981-06-09', 2450,null ,10 ),
(7788, 'SCOTT','ANALYST', 7566, '1987-04-19', 3000,null ,20 ),
(7839, 'KING','PRESIDENT', null, '1981-11-17', 5000, null ,10),
(7844, 'TURNER','SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS','CLERK', 7788, '1987-05-23', 1500, null, 20),
(7900, 'JAMES','CLERK', 7698, '1981-12-03', 1500, null, 30),
(7902, 'FORD','ANALYST', 7566, '1981-12-03', 1500, null, 20),
(7934, 'MILLER','CLERK', 7782, '1982-01-23', 1300, null, 10);


insert into SALGRADE values (1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999);

-- 查看表：
## 部门表 
select * from dept;
## 员工表 mgr：上级领导编号  hiredate 入职如期
select * from emp;

select * from salgrade;

***单表查询

-- 对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 as 员工编号, ename as 姓名, sal as 工资 from emp;
select empno as "员工 编号", ename as 姓名, sal as 工资 from emp; --  如果别名有特殊符号就必须加引号

-- 算术运算符 
select empno, ename, sal, sal+1000 as '涨薪后', deptno from emp where sal<2500;
select empno, ename,sal,comm, sal+comm from emp;

-- 去重操作
select job from emp;
select distinct job from emp; -- 去重 distinct
select distinct job, deptno from emp; -- 对后面的所有列组合去重，不是单独的某一列排列

-- 排序操作
select * from emp order by sal; -- 默认情况下是按照升序排列
select * from emp order by sal asc; -- asc 升序 可以默认不写
select * from emp order by sal desc;  -- desc 降序
select * from emp order by sal asc, deptno desc; -- 在sal升序的情况下，deptno为降序排列

***where子句

-- 查看emp表
select * from emp;

-- where子句： 将过滤条件放在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 job = 'CLERK';
select * from emp where job = 'clerk'; -- 默认情况不区分大小写
select * from emp where binary  job = 'CLERK'; -- binary 区分大小写

-- where子句 + 逻辑运算符： and
select * from emp where sal > 1500 and sal < 3000; -- (1500, 3000) 不包含左右两边的
select * from emp where sal > 1500 and sal < 3000 order by sal; -- 将得到的按照sal升序排列
select * from emp where sal > 1500 && sal < 3000;
select * from emp where sal > 1500 between sal < 3000; -- [1500, 3000] 包含左右两边的
select * from emp where sal > 1500 && sal < 3000;

-- where子句 + 逻辑运算符： or
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 job in ('MANAGER', 'CLERK', 'ANALYST');

-- 模糊查询：
## 查询名字中带A的员工
select * from emp where ename like '%A%'; -- % 代表任意多个字符（只要有A就能查到）
select * from emp where ename like '_A%'; -- _ 代表任意一个字符 （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; -- 先and 在 or （and优先级高）
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500);//

*** 分组

关键字：group by

-- 统计平均工资
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;

*** 分组后进行二次筛选

关键字： having

-- 统计各部门的平均工资，只显示大于2000以上的 分组以后进行二次筛选  having 
select deptno,avg(sal) from emp group by deptno where avg(sal) > 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 avg(sal) > 1000 order by deptno desc;

-- 统计各个岗位的平均工资  除了 MANAGER
## 1. 方法一
select job, avg(sal) 平均工资 from emp where job != 'MANAGER' group by job;
## 2. 方法二 
 select job, avg(sal) 平均工资 from emp group by job having job != 'MANAGER';
-- where 在分组进行时过滤  having 在分组后过滤

***select语句的执行顺序

from — where — group by — select — having — order by

***单表查询总结

-- 列出工资最小值小于2000的职位
select job, min(sal)
from emp 
group by job 
having min(sal) < 2000;  -- 在最小值中查小于2000的

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

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

-- 统计各部门的最高工资，排除最高工资小于3000的部门 
select deptno, max(sal) 
from emp 
group by deptno 
having max(sal) < 3000;

***多表查询

交叉连接 自然连接 内连接

-- 多表查询： cross  join（交叉连接）
select *
from emp
cross join dept; -- 14*4 = 56条 笛卡尔乘积 没有实际意义 有理论意义


--  natural  join (自然连接)
-- 优点：自动匹配所有的同名列 ，同名列只展示一列
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;
-- 缺点：表明太长
-- 解决：表起别名
select e.empno, e.ename, e.sal, d.dname, d.loc
from emp e
natural join dept d;

-- 自然链接 natural join  
-- 缺点：自动匹配表中所有的同名列，但是有时候只希望只匹配部分同名列

-- 解决：内连接 -using 子句
select *
from emp e
inner join dept d -- inner 可以不写
using(deptno);
-- 缺点：关联的字段必须是同名的
-- 解决：内连接 - 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
-- SQL99语法：筛选条件和连接条件是分开的

***外连接

-- 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;

-- 右外连接：  right outer join   右边那个表的信息 即使不匹配也可以展示出来
select *
from emp e
right outer join dept d
on e.deptno = d.deptno;

-- 全外连接 full outer join -- 这个语法在mysql中 不支持   在oracle中支持 （展示左右表全部不匹配的数据）
-- scott，40 号部门都可以看到

-- 解决mysql中不支持 全外连接  union  union all 
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;

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对集合的操作较弱  只支持并集  交集差集不支持 （oracle中支持）
-- outer  可以省略不写

***三表查询

-- 查询员工的编号 姓名 薪水 部门编号 部门名称 薪水等级
select * from emp;
select * from dept;
select * from salgrade;


select e.ename, e.sal, e.empno,e.deptno, d.dname, s.*
from emp e
inner join dept d
on e.deptno = d.deptno
inner join salgrade s
on e.sal between s.losal and s.hisal;

***自连接查询

把一张表看做两张表 把问题转化 就比较简单了 e1.mgr = e2.empno

-- 自连接查询员工编号 姓名 上级编号 上级姓名
select * from emp;

select e1.empno 员工编号,e1.ename 员工姓名, e1.mgr 领导编号,e2.ename 员工领导姓名
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

-- 左外连接： 没有领导的员工也要展示出来
select e1.empno 员工编号,e1.ename 员工姓名, e1.mgr 领导编号,e2.ename 员工领导姓名
from emp e1
left outer join emp e2
on e1.mgr = e2.empno;

***子查询

***单行子查询

-- 引入子查询
-- 查询所有比‘CLARK’工资高的员工信息

## 1. 查询‘CLARK’的工资
select sal from emp e where e.ename = 'CLARK'
## 2. 查询所有员工比他工资（2450）高的
select * from emp where sal > 2450;

-- 将两个命令结合
select * from emp where sal > (select sal from emp e where e.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 * from emp;
--  查询部门20的雇员信息
select job from emp where deptno = 20;
-- 查询部门10的职务信息
select job from emp where deptno = 10;
-- 合并起来
select *
from emp
where deptno = 20
  and
  job =  any (select job from emp where deptno = 10);
  -- 还可以写成
  -- job in (select job from emp where deptno = 10);
  
-- 2. 查询工资比所有的‘SALESMAN’都高的雇员的编号，名字和工资
-- 方法一
select empno, ename, sal
from emp
where sal > (select max(sal) from emp  where job = 'SALESMAN');
-- 方法二
select empno, ename, sal
from emp
where sal >  all  (select sal from emp  where job = 'SALESMAN');


-- 3. 查询工资低于任意一个‘CLERK’的工资的雇员信息
select *
from emp
where sal < any (select sal from emp where job = 'CLERK')
and job != 'CLERK';

select *
from emp
where sal < (select max(sal) from emp where job = 'CLERK')
and job != 'CLERK';
0
mysql
版权归属： Angel
本文链接： https://luckynet.site/archives/1689401493878
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区