Mysql学习笔记03

最新推荐文章于 2024-10-08 15:07:49 发布

等待救赎的波

最新推荐文章于 2024-10-08 15:07:49 发布

阅读量191

点赞数

文章标签： java

本文链接：https://blog.csdn.net/qq_46110710/article/details/124334465

版权

Mysql学习笔记03

多表查询

多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集

-- 显示雇员名，雇员工资及所在部门的名字
select ename, sal, dname, emp.deptno
from emp,
     dept
where emp.deptno = dept.deptno;
-- 显示部门号为10的部门名、员工名和工资
select ename, sal, dname, emp.deptno
from emp,
     dept
where emp.deptno = dept.deptno
  and emp.deptno = 10;
-- 显示各个员工的姓名，工资，及其工资的级别
select ename, sal, grade
from emp,
     salgrade
where sal between losal and hisal;
-- 显示雇员名，雇员工资及所在部门的名字，并按部门排序
select ename,sal,dname from emp,dept
where emp.deptno = dept.deptno
order by dname desc

自连接

在同一张表的连接查询

example

-- 显示公司员工和他的上级的名字
select worker.ename,boss.ename  from emp as worker,emp as boss
where worker.mgr = boss.empno;

子查询

嵌套在其他sql语句中的select语句，也叫嵌套查询

单行子查询

只返回一行数据的子查询语句

-- 显示与smith同一部门的所有员工
select *
from emp
where deptno = (select deptno
                from emp
                where ename = 'smith');

多行子查询

多行子查询指返回多行数据的子查询使用in

-- 查询和部门10的工作相同的雇员的名字，岗位，工资，部门号，但是不含10自己的
select ename, job, sal, deptno from emp
where job in(select distinct job
             from emp
             where deptno = 10) and deptno != 10;

子查询当作临时表使用

-- 查询ecshop中各个类别中，价格最高的商品
-- 先得到各个类别中，价格最高的商品
select goods_id, ecs_goods.cat_id, goods_name, shop_price
from (select cat_id, max(shop_price) as max_price
      from ecs_goods
      group by cat_id) temp,
     ecs_goods
where temp.cat_id = ecs_goods.cat_id
  and temp.max_price = ecs_goods.shop_price;
-- emp中各个部门工资最高的员工信息

select emp.ename, sal, emp.deptno
from (select max(sal) as max_sal, deptno
      from emp
      group by deptno) temp,
     emp
where temp.deptno = emp.deptno
  and temp.max_sal = emp.sal;

all

-- 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno
from emp
where sal > all (select sal from emp where deptno = 30);

select ename, sal, deptno
from emp
where sal > (select max(sal) from emp where deptno = 30);

any

-- 显示工资比部门30的任一员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno
from emp
where sal > any (select sal from emp where deptno = 30);

select ename, sal, deptno
from emp
where sal > (select min(sal) from emp where deptno = 30);

多列子查询

查询返回多个列数据的子查询语句

-- 查询和ALLEN的部门和岗位完全相同的所有雇员，不包含ALLEN
select * from emp
where (deptno,job)=(select deptno, job
                    from emp
                    where ename = 'ALLEN') and ename != 'ALLEN';

自我赋值数据

为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

-- 1.先把emp表的记录复制到my_tab01
insert into my_tab01(id, name, sal, job, deptno)
select empno, ename, sal, job, deptno
from emp;
-- 2.自我复制
insert into my_tab01
select *
from my_tab01;

如何删除掉一张表重复记录

-- 去重
-- 1.先创建一张临时表temp与my_tab02一样
-- 2.把my_tab02的记录通过distinct处理，然后把记录复制到my_temp
-- 3.清除掉my_tab02记录
-- 4.temp表的记录复制到my_tab02
-- 5.drop掉temp
create table temp like my_tab02;
insert into temp select distinct * from my_tab02;
delete from my_tab02;
insert into my_tab02 select * from temp;
drop table temp;

合并查询

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。

select ename, sal, job
from emp
where sal > 2500
union all
select ename, sal, job
from emp
where job = 'MANAGER';

union

去重

select ename, sal, job
from emp
where sal > 2500
union
select ename, sal, job
from emp
where job = 'MANAGER';

外连接

左外连接(如果左侧的表完全显示我们就说是左外连接)
右外连接(如果右侧的表完全显示我们就说是右外连接)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xFqBmZJl-1650554763437)(java基础.assets/image-20220421141016801.png)]
在这里插入图片描述

-- 显示所有人的成绩，没有成绩显示姓名和id(左外连接)
select `name`, stu.id,grade
from stu
         left join exam on stu.id = exam.id;
-- 显示所有成绩，没有名字就匹配空(右外连接)
select `name`, stu.id,grade
from stu
         right join exam on stu.id = exam.id;
-- 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门名
select dept.dname,ename,job from dept left join emp  on emp.deptno = dept.deptno;
select dept.dname,ename,job from emp right join dept  on emp.deptno = dept.deptno;

mysql约束

primary key(主键)

用于唯一的标识表行的数据，当定义主键约束后，该列不能重复
主键不能重复，不能为空
一张表最多只能有一个主键，但是可以是复合主键

-- 复合主键id+name 为主键
create table t13
(
    id     int,
    `name` varchar(32),
    email  varchar(32),
    primary key (id, `name`)
);
insert into t13
values (1, 'jack', 'jack@sohu.com');
insert into t13
values (1, 'tom', 'jack@sohu.com');-- 可以进去
insert into t13
values (1, 'jack', 'jack@sohu.com');-- 进不去

主键的指定方式

字段名 primary key
primary key(列名)

create table t12
(
    id     int primary key, -- 主键，不能重复
    `name` varchar(32),
    email  varchar(32)
);
create table t12
(
    id     int primary key, -- 主键，不能重复
    `name` varchar(32),
    email  varchar(32),
    primary key (id)
);

desc 表名可以看到primary key 的情况

not null(非空)

如果在列上定义了not null，那么当插入数据时，必须为列提供数据

unique(唯一)

当定义了唯一约束后，该列是不能重复的

create table t13
(
    id     int unique ,-- 不可以重复
    `name` varchar(32),
    email  varchar(32),
    primary key (id, `name`)
);
insert into t13
  values (1, 'jack', 'jack@sohu.com');
insert into t13
values (1, 'tom', 'jack@sohu.com');-- 添加失败

如果没有指定not null，可以多个null
指定了not null 使用效果类似于primary key
一张表可以有多个unique字段

foreign key (外键)

用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或外键列数据为null

-- 创建主表
create table my_class(
    id int primary key ,
    `name` varchar(32) not null default ''
);
-- 创建从表
create table my_stu(
    id int primary key ,
    `name` varchar(32) not null default '',
    class_id int,
    foreign key (class_id) references my_class(id)
);
insert into my_class values (100,'java');
insert into my_class values (200,'go');
select * from my_class;
insert into my_stu values (1,'java',100);
insert into my_stu values (2,'go',200);
insert into my_stu values (3,'python',300);-- 失败
insert into my_stu values (3,'python',null);-- 成功

外键指向的表的字段,要求是primary key或者是unique
表的类型是innodb,这样的表才支持外键
外键字段的类型要和主键字段的类型一致(长度可以不同)
外键字段的值，必须在主键字段中出现过，或者为null[前
提是外键字段允许为null]
一旦建立主外键的关系,数据不能随意删除了，从表无法乱加
先删关联的从表，就可以删掉主表的相关的

check

用于强制行数据必须满足的条件

create table t14
(
    id     int primary key,
    `name` varchar(23),
    sex    varchar(6) check (sex in ('man', 'woman')),
    sal    double check ( sal > 1000 and sal < 2000 )
);
insert into t14
values (1, 'jack', 'mid', 1);-- 插入失败

mysql5.7不支持

create table t14
(
    id     int primary key,
    `name` varchar(23),
    sex    varchar(6) check (sex in ('man', 'woman')),
    sal    double check ( sal > 1000 and sal < 2000 )
);
insert into t14
values (1, 'jack', 'mid', 1);-- 插入失败

example

create database shop_db;
create table goods
(
    goods_id   int primary key,
    goods_name varchar(32) not null default '',
    unit_price decimal(10,2) not null default 0 check ( unit_price >= 1.0 and unit_price <= 9999.99 ),
    category int not null default 0,
    provider varchar(32) not null default ''
);
create table customer
(
    customer_id char(8) primary key,
    `name` varchar(32) not null default '',
    address varchar(32) not null default '',
    email varchar(32) unique not null,
    sex varchar(6) check ( sex in('男','女') ),-- sex enum('男','女') not null
    card_id char(18)
);
create table purchase(
    order_id int unsigned primary key ,-- 范围变大不能为-
    customer_id char(8) not null default '',
    goods_id int not null default 0,
    nums int not null default 0,
    foreign key (customer_id) references customer(customer_id),
    foreign key (goods_id) references goods(goods_id)
);
desc goods;

自增长

字段名 整型 primary key auto_increment

create table t12(
    id int primary key auto_increment,
    email varchar(32) not null default '',
    `name` varchar(32) not null default ''
);
insert into t12 values (null,'jack@qq.com','jack');
insert into t12 (email, `name`) values ('jack@qq.com','jack');
insert into t12 (id, email, name)values (null,'jack@qq.com','jack');
alter table t13 auto_increment = 100;-- 从100开始

一般来说自增长是和primary key 配合使用的
自增长也可以单独使用[但是需要配合一个unique]
自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
自增长默认从1开始,你也可以通过如下命令修改altertable表名auto_increment = xx;
添加数据时给自增长字段(列)指定了值，以指定的值为准
一般不会修改自增长的值

索引

-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp表的 empno列创建索引
CREATE INDEX empno_index ON emp (empno);

没有索引为什么会慢?
因为全表扫描.
使用索引为什么会快?
形成一个索引的数据结构,比如二叉树

索引的代价

磁盘占用
对dml(update delete insert)语句的效率影响
以空间换时间

索引的类型

主键索引,主键自动的为主索引(类型Primary key)
唯一索引(UNIQUE)
普通索引 (INDEX)
全文索引(FULLTEXT)[适用于MyISAM]

一般不使用mysql的索引开发中考虑使用:全文搜索Solr和 ElasticSearch (ES)
建表时候写了primary key或者unique就有索引

-- mysql索引的使用
create table t15
(
    id int,
    `name` varchar(32)
);
-- 查询是否有索引
show indexes from t15;
-- 添加唯一索引，列不会重复
create unique index id_idex on t15(id);
-- 添加普通索引，列可以重复
create index id_idex on t15(id);
-- 添加普通索引方式2
alter table t15 add index id_idex (id);
-- 添加主键索引
alter table t15 add primary key (id);
-- 删除索引
drop index id_idex on t15;
-- 删除主键索引
alter table t15 drop primary key;

--修改索引
--先删除，再添加

--查询索引
show index from t15;
-- 方式一
show index from t15;
-- 方式二
show indexes from t15;
-- 方式三
show keys from t15;
-- 方式四
desc t15;

什么时候需要索引

较频繁的作为查询条件字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合创建索引
不会出现在WHERE句中字段不该创建索引

事务

事务用于保证数据的一致性它由一组相关的dml语句组成,该组的dml语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。

start transaction -- 开始一个事务
savepoint 保存点名 -- 设置保存点
rollback to 保存点名 -- 回退事务
rollback -- 回退全部事务
commit -- 提交事务，所有的操作生效，不能回退
create table t27
(
    id     int,
    `name` varchar(32)
);
-- 开启事务
start transaction;
-- 设置保存点
savepoint a;
-- 执行dml操作
insert into t27 values (100,'tom');
select * from t27;
savepoint b;
insert into t27 values (200,'jack');
-- 回退到b
rollback to b;
-- 回退全部事务
rollback

事务细节

1．如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2．如果开始一个事务，你没有创建保存点.你可以执行 rollback，默认就是回退到你事务开始的状态
3．你也可以在这个事务中(还没有提交时)，创建多个保存点
4. 你可以在事务没有提交前，选择回退到哪个保存点
5. mysql的事务机制需要innodb的存储引擎才可以使用,myisam不可以
6. 开始一个事务start transaction, set autocommit=off;

事务的acid特性

原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性(Consistency)

事务必须使数据库从一个一致性状态变换到另外一个一致性状态

隔离性(lsolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性(Durability)

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

储存引擎

-- innodb 存储引擎
-- 1.支持事务2.支持外链3.支持行级锁
-- myisam 存储引擎
create table t28
(
    id int,
    `name` varchar(32)
) engine myisam;
-- 1. 添加速度快2.不支持外键和事务3.支持表级锁
-- memory 存储引擎
-- 1.数据存储在内存中2.执行速度很快(没有io读写)3.默认支持索引(hash表)
-- 修改存储引擎
alter table t29 engine = innodb;

1．如果你的应用不需要事务，处理的只是基本的CRLD操作，那么MylSAM是不二选择,速度快
2. 如果需要支持事务，选择lnnoDB。
3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，
速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态)

视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列,其数据来自对应的真实表(基表)

1. create view视图名as select语句
2. alter view视图名as select语句
3. show create view 视图名
4. drop view视图名1,视图名2

-- 创建一个视图，只能查询emp表的(emptno,ename,job,deptno)
create view emp_view01
as
select empno, ename, job, deptno
from emp;
-- 查看视图
desc emp_view01;
select * from emp_view01;
select empno,job from emp_view01;
-- 查看创建视图的指令
show create view emp_view01;
create view emp_view02
as
select empno, ename
from emp_view01;

视图细节

创建视图后，到数据库去看，对应视图只有一个视图结构文件
视图的数据变化会影响到基表,基表的数据变化也会影响到视图
视图中可以再使用视图

视图最佳实践

1．安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这
时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段,不能查看保密的字段。
2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,
数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3．灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

-- 针对emp , dept ,和salgrade三张表.创建一个视图，可以显示雇员编号，雇员名，雇员部门名称和薪水级别
create view emp_view03
as
select empno, ename, dname, grade
from emp,
     dept,
     salgrade
where emp.deptno = dept.deptno
  and emp.sal between losal and hisal;
desc emp_view03;
select * from emp_view03;

练习

-- 佣金高于薪金的员工
select * from emp where ifnull(comm,0) >sal;
-- 各月倒数第三天受雇的员工
-- last_day()可以返回所在月份的最后一天
select * from emp where last_day(hiredate) -2 = hiredate;
select * from emp where month(date_add(hiredate,interval 3 day)) > month(hiredate);
-- 早于12年前受雇的员工
select * from emp where date_add(hiredate,interval 12 year) < now();
-- 以首字母小写的方式显示员工姓名
select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;
-- 不带R的员工姓名
select * from emp where ename not like '%R%';
select * from emp where instr(ename,'R') = 0;
-- 满10年服务年限
select ename,hiredate from emp where date_add(hiredate,interval 10 year) <= now();
-- 显示员工信息，按工作降序排序，若工资相同按薪金排序
select ename,job,sal from emp order by job desc,sal;
-- 显示员工信息，按受雇日期月份排序,月份相同将最早年份的员工排在前面
select ename,concat(year(hiredate),'-',month(hiredate)) from emp order by month(hiredate),year(hiredate);
select ename,concat(year(hiredate),'-',month(hiredate)) from emp order by month(hiredate),year(hiredate);
-- 显示一个月为30天的员工的日薪，忽略余数
select floor(sal/30) from emp where year(hiredate) = 30;
-- 显示一个月为30天的员工的日薪，不忽略余数
select round(sal/30) from emp where year(hiredate) = 30;
-- 显示加入公司天数
select datediff(now(),hiredate) from emp;
-- 姓名有A的员工
select ename from emp where instr(ename,'A') != 0;
select ename from emp where ename like '%A%';
-- 以年月日显示所有员工的服务年限
select ename,floor(datediff(now(),hiredate)/365) as '年',
       floor(datediff(now(),hiredate)%365/31) as '月'
,datediff(now(),hiredate)%31 as'天' from emp;
-- 列出至少有一个员工的部门
select count(*) as c,deptno from emp group by deptno having c>1;
-- 列出受雇日期晚于其直接上级的所有员工
-- 自连接
select work.ename as '员工名', leader.ename as '上级名' from emp work,emp leader where work.hiredate > leader.hiredate
and work.mgr = leader.empno;
-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
-- 外连接
select dname,emp.* from dept left join emp on emp.deptno = dept.deptno;
-- 列出薪金高于公司平均薪金的所有员工
select * from emp where sal>(select avg(sal) from emp);
-- 列出每个部门的员工数量、平均工资和平均服务期限
select count(*),avg(sal),avg(datediff(now()-hiredate)) from emp group by deptno;
-- 列出所有部门的详细信息和部门人数，临时表
select dept.*,tmp.c  from dept,(select count(*) as c,deptno from dept group by deptno) tmp
where dept.deptno = tmp.deptno;
-- 列出所有员工的年工资，按照年薪升序排列
select (sal+ifnull(comm,0))*12 as year_sal from emp order by year_sal asc;