Mysql学习笔记day3 --三天学完mysql所有知识点

mysql

---

一.练习

1. 列出至少有4个员工的部门名称

   select
   	d.dname 部门名称,
   	count(e.empno) 部门人数
   from
   	emp e join dept d
   on
   	e.deptno = d.deptno
   group by
   	d.deptno
   having
   	count(e.empno) > 4;
   

2. 列出薪金比"SMITH"多的所有员工

   ---查询smith工资是多少？
   select sal from emp where ename = 'smith';
   
   select
   	*
   from
   	emp
   where
   	sal > (select sal from emp where ename = 'smith');
   

3. 列出所有员工的姓名以及其直接上级的姓名

   select
   	e.ename,
   	m.ename
   from
   	emp e, emp m
   where
   	e.mgr = m.empno;
   

4. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称

   select
   	e.empno 员工的编号,
   	e.ename 姓名,
   	d.dname 部门名称,
   	e.hiredate 雇员入职日期,
   	m.hiredate 领导入职日期
   from
   	emp e, emp m,dept d
   where
   	e.mgr = m.empno
   and
   	e.deptno = d.deptno
   and
   	e.hiredate < m.hiredate;
   

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

   select
   	d.dname,
   	e.*
   from
   	dept d left join emp e
   on
   	d.deptno = e.deptno;
   

6. 列出所有从事"CLERK"工作的雇员姓名及其部门名称、部门人数

   ---查询每个部门的部门编号及人数
   select d.deptno,count(e.empno) count from emp e,dept d where e.deptno = d.deptno group by d.deptno;
   
   
   select
   	e.ename 雇员姓名,
   	d.dname 部门名称,
   	temp.count 部门人数
   from
   	emp e join dept d
   on
   	e.deptno = d.deptno
   join
   	(
           select 
        		d.deptno,
        		count(e.empno) count 
        	from 
        		emp e,dept d 
        	where 
        		e.deptno = d.deptno 
       	 group by 
        		d.deptno
       ) temp
   on
   	d.deptno = temp.deptno
   where
   	e.job = 'CLERK';
   

7. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

   select
   	job,
   	count(empno)
   from
   	emp
   group by
   	job
   having
   	min(sal) > 1500;
   

8. 列出在部门"sales"（销售部）工作的员工的姓名，假定不知道销售部的部门编号

   ---查询出销售部的部门编号
   select deptno from dept where dname = "sales";
   
   select
   	deptno,
   	ename
   from
   	emp
   where
   	deptno = (select deptno from dept where dname = "sales");
   

9. 列出薪金高于公司平均薪金的雇员姓名、所在部门名称、领导姓名、雇员的工资等级求出公司平均薪金

   ---查询出公司的平均薪资是多少？
   select avg(sal) from emp;
   
   select
   	e.ename 雇员姓名,
   	d.dname 部门名称,
   	m.ename 领导姓名,
   	s.grade
   from
   	emp e join dept d
   on
   	e.deptno = d.deptno
   join
   	emp m
   on
   	e.mgr = m.empno
   join
   	salgrade s
   on
   	e.sal
   between s.losal and s.hisal
   where
   	e.sal > (select avg(sal) from emp);	
   

10. 列出与"SMITH"从事相同工作的所有员工及部门名称

    ---查询SMITH从事什么工作
    select job from emp where ename = 'SMITH';
    
    select
    	e.*,
    	d.dname
    from
    	emp e,dept d
    where
    	e.deptno = d.deptno
    and
    	job = (select job from emp where ename = 'SMITH');
    

11. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

    ---部门30的员工的工资是多少？
    select sal from emp where deptno = 30;
    
    select	
    	ename,
    	sal
    from	
    	emp
    where
    	sal in(select sal from emp where deptno = 30);
    

12. 列出薪金高于部门30所有员工薪金的员工姓名、薪金及部门名称

    ---部门30的员工的工资是多少？
    select sal from emp where deptno = 30;
    
    select	
    	ename,
    	sal,
    	d.dname
    from	
    	emp e,dept d
    where
    	sal >all(select sal from emp where deptno = 30)
    and
    	e.deptno = d.deptno;
    

13. 列出在每个部门工作的员工数量、平均工资

    select
    	d.deptno 部门编号,
    	count(e.empno) 员工数量,
    	avg(e.sal) 平均工资 
    from
    	dept d left join emp e
    on
    	d.deptno = e.deptno
    group by
    	d.deptno;
    

14. 列出所有员工的姓名、部门名称和工资

    select
    	e.ename,
    	d.dname,
    	e.sal
    from
    	emp e,dept d
    where
    	e.deptno = d.deptno;
    

15. 列出所有部门的详细信息和部门人数

    select
    	d.*,
    	count(e.empno) 部门人数
    from
    	dept d left join emp e
    on
    	d.deptno = e.deptno
    group by
    	d.deptno;
    

16. 列出每种工作的最低工资以及从事此工作的雇员姓名

    ---每个工作的最低工资是多少？
    select job,min(sal) from emp group by job;
    
    select
    	temp.job,
    	e.ename,
    	temp.min
    from
    	emp e,(select job,min(sal) min from emp group by job) temp
    where
    	e.job = temp.job
    and
    	e.sal = temp.min;
    

17. 列出各个部门的经理的最低薪金

    select
    	min(sal) 最低薪金
    from
    	emp e
    where
    	job = 'manager'
    group by
    	e.deptno;
    

18. 列出所有员工的年工资，按年薪从低到高排序

    select
    	(sal + ifnull(comm,0)) * 12 年薪
    from
    	emp
    order by
    	年薪;
    

19. 查询雇员的领导信息，要求领导的薪水要超过3000

    #distinct 是去重的意思。
    
    select
    	distinct m.*
    from
    	emp e, emp m
    where
    	e.mgr = m.empno
    and
    	m.sal > 3000;
    

20. 求出部门名称中，带’S’字符的部门员工的工资总和 、部门人数

    select
    	sum(e.sal),
    	count(e.empno),
    	d.dname
    from
    	emp e,dept d
    where
    	e.deptno = d.deptno
    and
    	d.dname
    like
    	'%S%'
    group by
    	d.dname;
    

二.函数

函数是由Mysql提供的内置函数。大致分为一下几类：
1.字符串
2.数值
3.日期
4.流程控制
5.系统

1.字符串函数

1.concat(s1,s2,s3...)	拼接字符串
select
	concat('编号为:',empno,' 他/她的名字是：',ename,' 工资是：',sal)
from
	emp;

2.lower()	转换成小写
    select lower(ename) from emp;

3.upper()	转换成大写
	select upper(ename) from emp;

4.length()	字符串长度
	select length(ename) from emp;

5.reverse()	字符串反转
	select reverse(ename) from emp;

6.trim()	去除前后两端空白
	select trim(ename) from emp;
	
	select length('   hello   ') from dual;
	select length(trim('   hello   ')) from dual;

dual是Mysql提供的专门用于测试用的虚表。

7.ltrim()	去除左边的空白
	select ltrim(ename) from emp;

8.rtrim()	去除左边的空白
	select rtrim(ename) from emp;

9.replace(s1,s2,s3) 将s1中的s2替换成s3
	select replace(ename,'S','*') from emp;

10.repeat(s,n) 将s重复n次然后输出
	select repeat(ename,2) from emp;

11.substr(s,i,len)	将s从i的位置截取len个
	select substr(ename,1,2) from emp;		下标从1开始

2.数值函数

1.ceil(n)	向上取整
	select ceil(9.01) from dual;

2.floor(n)	向下取整
	select floor(9.99) from dual;

3.round(n,y)	将n保留y位小数，并且四舍五入
	select round(avg(sal),3) from emp;
	
	select round(9.9999999,2) from emp;

4.truncate(n,y)	将n保留y位小数，不四舍五入
	select truncate(avg(sal),3) from emp;
	
	select truncate(9.9999999,2) from emp;

5.rand()	返回0-1的随机数
	select rand() * 100 from dual;

3.日期函数

1.now()	返回当前系统函数
	select now() from dual;

2.curdate()	返回当前年月日
	select curdate() from dual;

3.curtime()	返回时分秒
	select curtime() from dual;

4.year()	返回日期中的年份
	select year(hiredate) from emp;

5.month()	返回日期中的月份
	select month(hiredate) from emp;

6.day()	返回日期中的日
	select day(hiredate) from emp;

7.week() 返回一年中的星期
	select week('2022-07-01') from emp;

8.timestampdiff(interval,datetime1,datetime2) 根据datetime1返回与datetime2的interval
interval取值：
	year年	month月	day日		hour时	minute分		second秒	
	select timestampdiff(minute,'2010-02-13',now()) from dual;

9.date_format(partern)
格式：
	%Y	四位年
	%m	两位月
	%d	两位日
	%H	24小时制的时
	%i	分
	%s	秒
select date_format(now(),'%Y年%m月%d日 %H时:%i分:%s秒') from dual;

4.流程控制

1.if(k,v1,v2)	如果k为真取v1否则取v2;
	select if(sal > 3000,'中等收入','低收入人群') from emp;

2.ifnull(k,v)	如果k为null 则取v
	select ifnull(comm,0) from emp;

5.系统函数

1.database()	查看当前使用的数据库
	select database() from dual;

2.user()
	select user() from dual;

3.version()
	select version() from dual;

三.约束

1.什么是约束？
	约束是用于对数据库的数据进行合法性校验的一种手段。
2.约束的分类
	a)主键约束
	b)非空约束
	c)唯一约束
	d)外键约束

1.主键约束

a)主键自增长

主键约束本身不能为空且唯一。
被设定为主键的列会作为数据的主要标识。


在创建表的时候设定主键,并且自增长：
	create table my_primary(
		id int,
		name varchar(20)
	);
关键字：
	primary key 设定 id 字段为主键。
	auto_increment 主键自增长。 mysql自动维护主键。
	
插入数据：
	insert into my_primary(name) values('张三');
	insert into my_primary(name) values('李四');
	
查询数据：
	select * from my_primary;
	

创建表之后设定主键，并且自增长：
	create table my_primary2(
		id int,
		name varchar(20)
	);
设定主键：
	alter table my_primary2 modify id int auto_increment primary key;
	
插入数据：
	insert into my_primary2(name) values('张三');
	insert into my_primary2(name) values('李四');
	
查询数据：
	select * from my_primary2;
	
	
---设定主键自增长的起始位置。
	alter table my_primary2 auto_increment = 100;
---设定主键自增长的不长。
	set auto_increment_increment = 2;
	
注意：
	如果主键数据被删除，则该主键永不可用。

b)非自增长主键

在创建表时增加主键：
create table my_not_increment_pimary(
	id varchar(50) primary key,
    name varchar(20)
);

插入数据：
insert into my_not_increment_pimary values('1','aaa');

insert into my_not_increment_pimary values(uuid(),'bbb');

insert into my_not_increment_pimary values(uuid(),'ccc');


在创建表之后增加主键：
create table my_not_increment_pimary2(
	id varchar(50),
    name varchar(20)
);
设置主键：
alter table my_not_increment_pimary2 modify id varchar(50) primary key;


插入数据：
insert into my_not_increment_pimary2 values(uuid(),'aaa');

insert into my_not_increment_pimary2 values(uuid(),'bbb');

insert into my_not_increment_pimary2 values(uuid(),'ccc');

2.非空约束

不允许数据为null就是非空。

create table t_my_not_null(
	id varchar(50) primary key,
	cardNo char(18) not null,
	name varchar(20)not null
);

插入数据：
	insert into t_my_not_null values(uuid(),'3403021998xxxxxx','张三');

创建表后添加非空约束：
create table t_my_not_null2(
	id varchar(50) primary key,
	cardNo char(18),
	name varchar(20)
);


alter table t_my_not_null2 modify cardNo char(18) not null;
alter table t_my_not_null2 modify name varchar(20) not null;

3.唯一约束

不允许重复数据。
create table t_my_unique(
	id varchar(50) primary key,
	cardNo char(18) not null,
	name varchar(20)not null,
	tel varchar(20)unique
);

插入数据：
	insert into t_my_unique values(uuid(),'3403021998xxxxxx','张三','13914767897');
	
	insert into t_my_unique values(uuid(),'3403021998xxxxxx','张三','13914767898');


创建表之后设置唯一约束：
create table t_my_unique2(
	id varchar(50) primary key,
	cardNo char(18) not null,
	name varchar(20)not null,
	tel varchar(20)
);

alter table t_my_unique2 modify tel varchar(20) unique;

4.外键约束

从表【有外键的表】中的某一列引用主表【被引用的表】中的某一列，让两张以上的表产生关联和约束。
//从表
create table student_foreign(
	id varchar(50) primary key,
	name varchar(20),
	sex int,
	weight double,
	cid varchar(50),
	foreign key(cid) references class_primary(id)
);


//主表
create table class_primary(
	id varchar(50) primary key,
	name varchar(20),
	info varchar(20)
);

插入数据：
insert into class_primary values(uuid(),'Java班','java就业班级');


insert into student_foreign values(uuid(),'张三',1,75.2,'002ffed8-f905-11ec-b824-a85e45a0cbb0');
insert into student_foreign values(uuid(),'李四',0,55.2,'002ffed8-f905-11ec-b824-a85e45a0cbb0');

删除数据：
---不能删除，因为数据被引用。
delete from class_primary where id = '002ffed8-f905-11ec-b824-a85e45a0cbb0';
---可以删除，因为删除的是从表的数据。
delete from student_foreign where id = '63bbae66-f905-11ec-b824-a85e45a0cbb0';


创建表之后如何添加外键：
create table student_foreign2(
	id varchar(50) primary key,
	name varchar(20)
);
---新建一个列
alter table student_foreign2 add cid varchar(50);
---将新建的列设置为外键。
alter table student_foreign2 add foreign key(cid) references class_primary(id);

四.事务

事务是用来保证数据的完整性和操作性的。

一个业务由若干个一次性的操作组成，
这操作要么都成功，要么都失败。


事务就是用于解决在一个业务中需要操作
多条sql的时候，sql没有全部被执行成功。
那么就需要将执行成功的sql语句设置成未操作的形态。
这种方式我们成为：回滚。
如果所有sql都执行成功则将数据持久化
这种当时我们成为：提交。


Mysql的存储行为是，先将数据存储在内存，然后当用户
进行了commit操作之后，才会将数据从内存的缓存区刷新
到磁盘，进行持久化。

Mysql的提交方式是默认开启的。

查看自动提交状态：
show variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |	自动提交开启状态
+---------------+-------+

关闭自动提交：
set autocommit = off;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |	自动提交关闭状态
+---------------+-------+

commit;	提交		将数据从缓存区刷新到磁盘
rollback; 回滚	将数据从缓存区更新。

比如：
	转账
	1.从A账户中减去转账额度。
	2.从B账户中增加转账额度。

事务的特性：

称为事务的ACID：

• Atomcity【原子性】

  原子性是指事务在操作中，所有的操作要么全部成功，要么全部失败。
  事务一旦成功后，必须完全应用到数据库，如果操作失败则不能对数据库有任何的影响。
  

• Consistency【一致性】

  一致性是指事务从一个状态转换到另一个状态。
  也就说事务从执行前到执行后都在一致性的状态。
  通俗的说，不管操作失败还是成功，数据是保持一致的。
  

• Isolation【隔离性】

  隔离性是指当多个用户发送并发访问数据库时，操作了同一张表，
  数据库会为每一个用户开启一个事务，事务与事务之间不能相互影响。
  多个并发的事务要相互隔离。
  

• Durability【持久性】

  事务一旦被提交，那么对于数据来说就是永久性的。
  哪怕服务器宕机，或者损坏。数据都不会发生改变。
  

五.存储引擎【了解】

1.什么是存储引擎？
	表的最终形式生成一个文件保存在磁盘上，那么这个表的数据结构就
	由存储引擎来决定。
	存储引擎指的就是表的存储机制，索引方案，不同的存储引擎由于处理的方式
	不同，会带来不同的功能。

2.查看mysql的存储引擎
	show engines;

3.常用的存储引擎

• InnoDB

  该引擎是Mysql首选引擎，支持事务安全表，支持行多和表锁定。
  该引擎是默认使用的引擎。主要特性有：
  1.具有提交，回滚和崩溃恢复能力。
  2.为处理巨大数据量大最大性能而设计的。
  3.支持外键完整性约束。
  4.经常被部署在众多需要高性能的大型站点上。
  使用该引擎创建的表会在数据库文件夹下创建一个以.frm为结尾的文件，该文件用于存储数据和数据结构。
  其它数据库引擎都不支持事务，只有它支持。
  

• MyISAM

  它是在web、数据仓储和其它应用环境下使用的存储引擎之一，
  它拥有较高的插入，查询速度，但是不支持事务。
  主要特性：
  1.在支持大文件的文件系统和操作系统上被支持。
  2.适合读多写少的应用场景。
  3.非事务引擎。
  
  使用该引擎创建的表会在数据库文件夹下创建三个以.frm【数据结构】 .myd【数据】 .myi【索引】 文件。
  create table t_myisam(
  	id int,
      name char(2)
  )engine = MyISAM;
  

• MEMORY

  查询速度极快。
  数据不能被持久化。
  所有的数据都放在内存中。
  不支持blob 和 text类型。
  
  create table t_memory(
  	id int,
      name char(2)
  )engine = MEMORY;
  

六.索引

1.什么是索引

索引是对数据库表中的一列或者多列的值进行排序的一种结构，
使用索引可以快速访问数据库中的数据。

本质上就是数据的目录。

2.索引的原理

在没有对数据添加索引的时候，Mysql默认是全表扫描。
一行一行的对数据进行匹配。有多少条数据就扫描多少次。
然后找到将匹配到数据放入结果集知道全表扫描完毕。、

而建立索引之后，会将建立索引的 'key' 值放入一个B+Tree上。
因为B树的特点就是适合找东西。

每次以索引进行条件查找时，会去树上根据key值直接进行搜索。

3.索引的优点

1.建立索引可以有效缩短数据的检索时间。
2.建立索引可以加快表与表之间的连接
3.建立索引可以加快对表中数据的排序和查找。

4.索引的缺点

1.创建索引和维护索引需要时间成本，这个成本会随着数据量的增大而增大。
2.创建索引需要空间成本，每一条索引都需要占用数据库物理存储空间，数据量越大，占用空间越大。
3.索引会降低表的增删改的效率，因为每次数据的状态发生改变，都需要维护索引，导致时间变长。

5.索引的分类

1.普通索引【单列索引】
2.复合索引【组合索引】
3.唯一索引
4.主键索引

6.创建普通索引

---直接创建
create index empnoIndex on emp(empno);
---查看表中有哪些索引。
show index from emp;

7.索引的演示

1.导入t100w.sql
2.添加索引
	alter table t100w add index numIndex(num);
3.删除索引
	drop index numIndex on t100w;

七.视图

1.什么是视图？
	视图本质上就是一张虚表，是一组数据的逻辑展示。
	实质是对应一条复杂的SQL语句，它是一个映射到基表的一个查询语句。
	当基表的数据发生编号，视图的数据也会随之变化。
	
	可以理解为视图就是封装了一条非常复杂的查询语句。

2.创建视图
create view v_emps_empm
as
select
	e.ename 雇员姓名,
	d.dname 部门名称,
	m.ename 领导姓名,
	s.grade
from
	emp e join dept d
on
	e.deptno = d.deptno
join
	emp m
on
	e.mgr = m.empno
join
	salgrade s
on
	e.sal
between s.losal and s.hisal
where
	e.sal > (select avg(sal) from emp);

八、练习

1. 以首字母大写，其他字母小写的方式显示所有员工的姓名

2. 将员工的职位用小写字母显示

3. 显示员工姓名超过5个字符的员工名 

4. 用#来填充员工职位job的结尾处，按10个字符长度输出。

5. 去除字符串'  hello world  '两边的空格，并将单词间的空格改为','逗号。

6. 以指定格式显示员工的奖金（格式：allen's comm is 300) 注：如果奖金为null显示为null，如smith 's comm is null。

7. 显示在一个月为30天的情况所有员工的日薪，忽略余数

8. 显示员工在此公司工作了几个月

9. 显示所有12月份入职的员工

10. 显示员工的年薪

11. 显示所有员工的姓名、加入公司的年份和月份，并且按照年份升序排列

12. 查询任职日期超过30年的员工，显示时将月薪加10%后显示

```