5天学MySQL_创建数据库mydb1和2

day01

数据库简介：

数据库**：就是数据的仓库，它是长期存储在计算机内，有组织的、可共享的数据的集合。

**数据库管理系统（DBMS:** 用来对数据进行存储、管理等操作的软件
1
2
3

mariadb -- MySQL的分支 
1

SQL (Structured Query Language)

结构化查询语言，
1

连接数据库

mysql -uroot -p
1

mysql指令

数据库相关sql

1:查看所有数据库
show databases;

2:创建数据库
create database 数据库名称
- create database db1;

3:查看数据库详情
show create database 数据库名;
- show create database db1;

4:创建数据库指定字符集
create database 数据库名 character set utf8/gbk;
- create database db1 character set utf8;

5:删除数据库
drop database 数据库名;
- drop database db1;

6:使用/打开数据库
use 数据库名
- use db1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

表相关sql(1)

1:创建表(以后一般用工具 sqlyog)
create table 表名(字段1名 字段1类型,字段2名 字段2类型);
-create table person(name varchar(10) , age int);
练习 ；创建学生表student
 create table student(
		id int,name varchar(10),....	

	);

2:查看所有表
show tables;

3:查看表详情
show create table 数据库表名; 

-表引擎
	1：InnoDB(默认)：支持事务和外键等高级操作
	2：myisam：只支持基础的增删改查操作

4：创建表指定引擎和字符集
create table 表名(字段1名 字段1类型,字段2名 字段2类型)
engine=myisam/innoDB  charset=utf8/gbk;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SQL相关练习

1：创建2个数据库，分别是mydb1和mydb2(mydb2字符集为GBK)
	create database mydb1;
	create database mydb2 charset set gbk;

2：在mydb1里面创建员工表emp ,字段 姓名 年龄 工资(sal)
3：在mybd2里面创建英雄表 hero 字段 ：姓名 年龄 类型(type) 引擎为myisam 字符集为gbk;
	use mydb2;
	create table hero(name varchar(10),age int,type varchar(10) engine=myisam charset=gbk;
4：删除刚刚创建的两个数据库
	drop database mydb1;
	drop database mydb2;
1
2
3
4
5
6
7
8
9
10
11

表相关sql(2)

1：查看表字段
desc 表名
- desc student

2:删除表
drop table 表名
- drop table student;

3:修改表名
rename table 原名 to 新名;
- rename table student to studengtest;

4:修改引擎和字符集
alter table 表名 engine=myisam/InnoDB charset=utf8/gbk;
- alter table tt engine= InnoDB charset=gbk;

5:添加表字段 
alter table 表名 and 字段名 字段类型;//在最后添加
alter table 表名 and 字段名 字段类型 first;//在最前面添加
alter table 表名 and 字段名 字段类型 after xxx;//在xxx的后面添加

6：删除表字段
alter table 表名 drop 字段名;

7：修改表字段和类型
alter table 表名 change 原字段名 姓名 新类型;

8： 修改字段类型和位置
alter table 表名 modify 字段名 新类型 first/after xxx ; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

​

数据相关SQL(1)

1:保存数据(增)
- 全表插入
- insert into 表名 values(值1,值2,值3);

-指定字段插入
-insert into 表名(字段1,字段2,字段。。。) values(值1,值2,值。。。);

-批量插入
-insert into 表名 values(字段1,字段2,字段。。。)
1
2
3
4
5
6
7
8
9

中文乱码问题

在Windows10操作系统中，插入中文后乱码，执行以下命令
set names gbk/utf8;
1
2

SQL数据相关(2)

1：查询数据
SELECT 字段信息 FROM 表名 WHERE 条件;

2：修改数据
update 表名 set 字段名=xxx where 条件;
- update student set age=19 where name ='tom';

3:删除数据
delete from 表名 where 条件;
1
2
3
4
5
6
7
8
9

练习题

1：创建数据库mydb2，字符集为utf8 并使用
create database mydb2 character set uft8; 
use mydb2;	
2:创建员工表(包含id name字段 )
create table emp (id int,name varchar(10));
1
2
3
4
5

day02

主键约束

-什么是主键？表示数据唯一性的字段称为主键。
-什么是约束？是创建表时给表字段添加的限制条件
-主键约束？让该字段的数据唯一且非空(不能重复 不能null)
-create table t1(in int primary key ,name varchar(10 ));
1
2
3
4

主键约束+自增

自增数值只增不减,从历史最大值基础上+1
-create table t2(id int primary key auto_increment,name varchar(10));
1
2

注释 commment’注释’

create table t3(id int primary key auto_increment comment '主键注释',name varchar(10) comment '名字注释')
1

（`）符号的作用

-用于修饰表名和字段名，可以省略
create table `t4`(`id` int ,`name` varchar(10));
1
2

冗余

由于表设计不够合理导致的大量重复数据，称为数据冗余
1

数据库 事务

什么是事务
-事务是指数据库中执行同一业务多条SQL语句的工作单元,可以保证全部执行成功或全部执行失败。
事务相关指令
1：开启事务：begin
2：提交事务：commit
3：回滚事务：rollback
-验证转帐流程
create table user(id int primary key auto_increment,name varchar(10),money int,status varchar(5));
insert into user values('null','美队','50','冻结'),('null','蝙蝠侠','5000','正常'),('null','灭霸','20','正常');
- 转账的SQL
update user set money=money-2000 where id=2 and status='正常';
update user set money=money+2000 where id=1 and status='正常';
--有事务保护的情况下 回滚流程。(操作步骤如下)
1：开启事务
- begin 
2：蝙蝠侠 -2000
- update user set money=money-2000 where id=2 and status='正常';
3：此时在当前终端查询数据时，数据已经改变(因为查询到的时内存中的改动),开启另一个终端查询数据发现数据是没有改变的(因为新的终端查询到的是磁盘的数据)
4：美队 +2000
- update user set money=money+2000 where id=1 and status='正常';
5：此时从执行结果中发现一条成功一条失败，应该执行回滚操作
- rollback

--有事务保护的情况下 提交流程。(操作步骤如下)
1： 开启事务
- begin
2：蝙蝠侠 - 2000
- update user set money=money-2000 where id=2 and status='正确';
3：此时仍然是在内存中改动，磁盘中的数据没有发生改变
4 灭霸 +2000
- update user set money=money+2000 where id=3 and status='正常';
5：此时两次改动都是在内存中改完，所以执行提交
- commit

保存回滚点 
- begin  //开启事务
update user set maney=1 where id=2;
- savepoint s1; // savepoint --保存回滚点 s1;回滚点名称
update user set maney=2 where id=2;
- savepoint s2; // savepoint --保存回滚点 s1;回滚点名称
update user set maney=3 where id=2;
- savepoint s3; // savepoint --保存回滚点 s1;回滚点名称

rollback; //回滚到事务开启前状态
rollback to s1;//回滚到s1节点
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

经典面试题目

事务的特性 ACID

保证事务正确执行的四大基本要素
A:(Atomicity) -原子性：最小不可拆分，保证全部执行成功或全部执行失败
C:(Consistency) -一致性：从一个一致状态到另一个一致状态
I:(Isolation) -隔离性： 多个事务之间互相隔离互不影响
D:(Durability) -持久性：当事务提交后数据保存到磁盘中持久生效
1
2
3
4
5

SQL分类

DDL(Date Definition language)数据定义语言

删除表并创建新表（清空表数据）
- truncate table 表名
	
-包括 create、drop、alter、truncate
- 不支持事务
1
2
3
4
5

DML(Date Manipulation Language)数据操作语言

-包括 insert、update、delete、select
1

DQL(Date Query Language)数据查询语言

-只包括 select
1

TCL(Transaction Control Language)事务控制语言

-包括 begin、commit、rollback、savepotion xxx、rollback to xxx;
1

DCL(Date Control Language) 数据控制语言

-负责分配用户权限相关的SQL
1

SQL数据类型

1：整数 
-int(m) 和 bigint(m)  //m：代表的是显示长度，需要结合zerofill使用
create table t_int(id int ,age int(10) zerofill);

2:浮点数
- double(m,d)   //m:代表是总长度，d:代表小数长度，超高精密的浮点数 decimal(m,d)

3：字符串
- char(m)  固定长度 最大长度255; varchar(m) 可变长度 ，好处是节省空间 最大长度65535(但是超过255建议使用text(m)); 
- text(m) 可变长度，最大长度65535

4：日期时间
- date：保存年月日
- time：保存时分秒 
- datetime：保存年月日时分秒	,默认值为null,最大9999—12—31
- timestamp：(时间戳)保存年月日时分秒，默认值为当前系统时间,最大值 2038-1-19
例子 
	create table t_date(t1 date,t2 time,t3 datetime ,t4 timestamp)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

导入 *.sql 文件

导入文件： source  文件路径
导入后如果乱码：执行 set names gbk/utf8;
1
2

day03

查询数据(表：newdb3) ---- 单表查询

is null(为空) 和 is not null(不为空);

1:从员工表中查询 没有上级领导的员工姓名，工资
-select ename , sal from emp where mgr is null;
2：查询有领导的员工姓名和上级领导的编号
-select ename ,mgr from emp where mgr is not null;
1
2
3
4

别名

- select ename as '姓名' from emp;
- select ename '姓名' from emp;
- select ename 姓名 from emp;
1
2
3

比较运算符 > < >= <= = != 和 <>(不等)

1：查询工资在2000以下的员工姓名和工资
-SELECT ename,sal FROM emp WHERE sal<2000;
2：查询职位(job)是manager的员工姓名、工资、职位
-select * from emp where job=manager;//查询错误
-select ename,sal,job from emp where job='manager';
3：查询工资小于等于1600的员工姓名、职位、工资
-select ename ,job,sal from emp where sal<1600;
4：查询不是10号部门的员工姓名和部门编号(两种写法)
- select ename ,empno,deptno from emp where deptno!=10;
5：查询商品表(t_item)中单价为23的商品信息
- SELECT * FROM t_item WHERE price=23 \G;
6：查询单价不是8843的商品标题(title)
- SELECT title FROM t_item WHERE price != 8843;
1
2
3
4
5
6
7
8
9
10
11
12
13

去重(distinct )

1： 查询有哪些职位
-select distinct job from emp;

2:查询员工表中出现了那些部门编号
-select distinct deptno from emp;
1
2
3
4
5

and 和 OR

- 如果查询数据时需要同时满足多个条件则使用 and
- 如果查询数据时只需要满足条件中得某一个则使用 or
1:查询10号部门中工资低于2000 得员工信息
- select * from emp where deptno=10 and sal<2000;
2: 查询部门是30号或者工资高于3000的员工信息
- select * from emp where deptno=30 or sal>3000;
1
2
3
4
5
6

模糊查询 like

- %  代表0或 多个 未知字符
- _  代表 单个  未知字符

举例： 第一个字符是X     x%;
	   最后一个字符是y   %y;
	   包含   			%x%;
	   倒数第二个字符是x  %x_;
	   x开头y结尾		 x%y;
		第二个字符是x 倒数第三字符是y   _x%y_
1:查询名字以 j 开头的所有员工新名
- select ename from emp where ename like 'j%';

2:查询第二个字符是 l 的员工新名和工资
- select ename sal from emp where ename like '_l%';

3：查询t_item表中，标题中包含记事本的商品标题
- SELECT title from t_item where title like '%记事本%';

4：查询职位中包含an并且工资高于1500的员工姓名、工资、职位。
- select ename ,sal ,job from emp where job like '%an%' and sal>1500;
5：查询有赠品的dell商品详情(卖点sell_point中包含‘赠’，并且title包含dell)
- select * from t_item where sell_point like '%赠%' and title like '%dell%';
6：查询单价低于100块钱的笔记本标题和价格(提示：title包含笔记本)
- select title,price from t_item where price<100 and title like '%笔记本%';
7:查询有图片的得力商品信息(image字段不为null,title 包含得力)
- select * from t_item where image is not null and title like '%得力%';
8：查询不包含a的员工姓名
- select ename from emp where ename not like '%a%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

between x and y (between x and y : 在xxx之间)(not between x and y : 在xxx之外)

1：查询工资在2000到3000之间的员工姓名和工资(要求包含2000 和 3000)
- select ename,sal from emp where sal>=2000 and sal<=3000;
 **between x and y:方式如下**
- select ename ,sal from emp where sal between 2000 and 3000;
2：查询单价在50到100之间的商品标题和单价
- select title , price from t_item where price between 50 and 100;
3：查询工资在1000 到 2000 之外的员工姓名和工资
- select ename , sal from emp where sal not between 1000 and 2000;
1
2
3
4
5
6
7
8

in (当查询字段值为多个值的时候使用)

1：查询员工工资在800，1300，1500的员工信息
- select * from emp where sal=800 or sal=1300 or sal=1500;
- select * from emp where sal in(800,1300,1500);
2：查询商品价格为56，58，89的商品标题和单价
- select title,price from t_item where price in(56,58,89);	
3：查询工资不等于3000，5000，1500的员工姓名和工资
- select ename,sal from emp where sal not in(3000,5000,1500);
1
2
3
4
5
6
7

综合练习

1：查询分类id为 238，917的商品信息;
- select * from t_item where category_id in(238,917);
2:查询价格在50-200之间的得力商品标题和价格
- select title ,price from t_item where price between 50 and 200 and title like '%得力%';
3：查询有上级领导并且工资小于2000的员工姓名、工资和领导编号
- select ename,sal,mgr from emp where mgr is not null and sal<2000;
4：查询有奖金并且有上级领导的员工姓名、奖金、领导编号
- select ename,comm,mgr from emp where comm>0 and mgr is not null; 
5：查询名字中包含a并且工资在3000以内的员工从事的职业有哪几种;
- select distinct job from emp where ename like '%a%' and sal<3000;
1
2
3
4
5
6
7
8
9
10

排序 order by

格式 ： order by 字段名 asc/desc 	asc:升序     desc:降序
- order by : 写在条件的后面
select ename,sal from emp order by sal;
- select ename,sal from emp where sal<3000 order by sal;  
1：查询工资小于300的员工姓名和工资 ，要求按照工资降序排序
- select ename,sal from emp where sal<3000 order by sal desc;
2: 查询10号部门每个员工的姓名和工资，按照工资升序排序
- select ename ,sal,deptno from emp where deptno=10 order by sal;
3:查询有奖金的员工姓名，工资，奖金 ，按照奖金降序排序
- select ename,sal ,comm from emp where comm >0 order by comm desc;
4:查询单价低于100的商品标题和单价并按照单价升序排序
- select title,price from t_item where price<100 order by price asc;
5：查询每个员工的姓名，工资，和部门编号按照部门编号降序排序
- select ename,sal,deptno from emp  order by deptno desc;
--多字段排序：order by 字段1 asc/desc ,字段2 asc/desc--
查询每个员工的姓名、工资和部门编号，按照部门编号降序排序，如果部门一致则按照工资升序排序
-select ename ,sal ,deptno from emp order by deptno desc,sal;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

分页查询 limit 跳过的条数，请求的条数

-请求第一页的10条数据  limit 0,10;
-请求第三页的10条数据  limit 20,10
-公式 limit(页数-1)*数量,数量
请求第四页的7条数据 （4-1）*7,7	:21,7
1:查询每个员工的姓名和工资，按照工资降序排序 请求第三页的3条数据（获取第几条数据）
- select ename,sal from emp order by sal desc limit 6,3;
2：请求员工表中工资最高的前三名员工的信息
- select * from emp order by sal desc limit 0,3;
3：查询商品标题和单价 按照单价升序排序，第3页的5条数据
- select title,price from t_item order by price limit 10,5; 
4：查询30号部门中工资最高的员工信息
- select * from emp where deptno=30 order by sal desc limit 0,1;
1
2
3
4
5
6
7
8
9
10
11
12

数值计算 + - * / %

1：查询员工姓名，工资和年终奖(年终奖=工资*5)
- select ename,sal ,sal*5 年终奖 from emp;
2：查询商品标题，商品单价，库存和总价值(单价*库存)
- select title ,price ,num,price*num 总价值 from t_item;
3：查询每个员工的姓名和加薪5块钱之后的工资
- select ename,sal+5,from emp; 
1
2
3
4
5
6

日期相关SQL

1：获取系统时间  now()
- create table t_date(name varchar(10),birthday datetime);
   insert into t_date values('刘德华',now());

2：获取当前的年月日 和 当前的时分秒
- select now();//获取当前时间
- select curdate(),年月日 Curtime();时分秒

3：从年月日时分秒中提取年月日 和 提取时分秒
- select date(now());
- select time(now());
查询商品的上传信息 只查询年月日
select created_time from t_item;//查询出上传时间的年月日时分秒
-select date(created_time) from t_item; //上传时间的年月日

4：从年月日时分秒中提取时间分量
-select extract(year from now());	//年
-select extract(month from now());	//月
-select extract(day from now());	//日	
-select extract(hour from now());	//时
-select extract(minute from now()); //分
-select extract(second from now());  //秒
查询员工入职的年份
-select extract(year from hiredate) from emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

日期格式化 date_format(时间,格式)

格式规则：
%Y ：四位年  2020  %y 两位年 20
%m  2位月  06     %c  1为月  6
%d  日
%H   24小时 		%h    	12小时
%i    分钟
%s	   秒
- 测试 把now() 2020-03-04 22:00:30  转换成2020年03月02号 22点 1分 30秒
- select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');
1
2
3
4
5
6
7
8
9

反向格式化 str_to_date(时间,格式)

04.03.2020  22点 06分10秒 转回 2020-03-04 22：06：20
select str_to_date('04.03.2020 22点07分20秒 ','%d.%m.%Y %H点%i分%s秒');
1
2

ifnull(x,y)

- age=ifnull(x,y)  如果x值为null,则age=y 否则age=x;
练习：修改奖金为null的值为0,不为null则不变
- update emp set comm=ifunll(comm,0);
1
2
3

聚合函数

-对查询的多条数据进行统计查询 ：平均值、最大值 、最小值 、求和、计数
1：平均值  avg(字段名)
 查询员工的平均工资
- select avg(sal) from emp;
2: 最大值 MAX(字段名)
 查询20号部门的最高工资
- select max(sal) from emp where deptno=10; 
3： 最小值 min(字段名)
 查询名字里面包含a的员工的最低工资
- select min(sal) from emp where ename like %a%;
4: 求和 sum(字段)
  查询10号部门的工资总和
- select sum(sal) from emp where deptno=10;
5:计数 count(字段)
 查询工资高于2000的员工的数量
- select count(*) from emp where sal>2000;
查询20号部门 的平均工资，最高工资，最低工资，工资总和，员工数量
select avg(sal),max(sal),min(sal),sum(sal),count(*) from emp where deptno=20;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

字符串相关

1：字符串拼接  concat(s1,s2)  s1s2 
select concat('aa','bb');
查询每个员工的姓名和工资，要求工资以元为单位
- select ename ,concat(sal,'元') from emp;
2：获取字符串长度 char_length(str);
	select char_length('abc');
  查询每个员工的姓名和名字长度
- select ename,char_length(ename) from emp;

3：获取字符串出现的位置 instr(str,substr)
- select instr('abvdefghijk' , 'd');

4：转大写和转小写
- select upper('abc'),lower('NBA');
5:截取字符串
  - 左边截取	select left('abcdefg',2);//ab
  - 右边截取 select right('abcdefg',2);//fg
  - 自由截取 select substring('abcdefg',2,3);//bcd //3:代表长度
6:去空白 trim()
	select trim('  a b   ');// a b //只去除两端空白
7：重复	repeat()
	select repeat('ab',2); //2:重复次数	
8：替换 replace()
 	select replace('abcd abc','b','m');// 将全部b替换成m
9：反转 reverse()
	delect reverse('abc');//cba
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

day04

数学相关

1：向下取整 floor(num)
- select floor(3.339);//3
2:四舍五入	round(num)
- select round(3.94);//4
  四舍五入	round(num,m)//m代表小数位数
- select round(3.9487,2);//3.95 
3:非四舍五入 truncate(num,m);//m代表小数位数
- select truncate(23.789,2);//23.78
4:随机数 rand()   获得0-1的随机数
- select rand();
1
2
3
4
5
6
7
8
9
10

分组查询

-group by 
1:查询每个部门的平均工资
- select deptno 部门 ,avg(sal) 平均工资 from emp group by deptno;
2:查询每种工作的最高工资
-select max(sal),job from emp group by job;
3:查询每个部门的人数？
-select deptno 部门,count(deptno) 部门人数 from emp group by deptno; 
4:查询每个部门工资高于1500的人数
- select deptno 部门,count(*) from emp where sal>1500 group by deptno;
5:查询每个主管mgr的手下人数
- select mgr 主管,count(*)手下 from emp where mgr is not null group by mgr;
1
2
3
4
5
6
7
8
9
10
11

多字段分组

- group by 字段1,字段2
1:查询每个部门下每种职业的平均工资
select deptno,job ,avg(sal) from emp group by deptno , job; 
1
2
3

练习

1：查询emp表中每个部门的编号、人数、工资总和，根据人数进行升序排序，如果人数一致根据工资总和降序排序。
- select deptno,count(*) c ,sum(sal) s from emp group by deptno order by c,s desc;
2:查询工资在1000-3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排序
- select deptno,avg(sal),min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by avg(sal);
3:查询有上级领导的员工，每个职业的人数，工资总和，平均工资，根据人数进行降序排序，如果人数一致则根据平均工资进行升序排序；
- select job,count(*) c,sum(sal),avg(sal) a from emp where mgr is not null group by job order by c desc,a;
1
2
3
4
5
6

过滤 having

-having
-where 后面只能写普通字段的条件
-having 后面写聚会函数的条件，和分组查询结合使用
-各个关键字的顺序
select .... from 表名 where .... group by .... having .... order by .... limit....;

1：查询每个部门的平均工资要求平均工资大于2000
- select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
2：查询商品表中每个分类的平均单价，要求平均单价低于100
- select category_id, avg(price) a from t_item group by category_id having a<100;
3：查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
- select deptno ,count(*) , avg(sal) a from emp group by deptno having a>2000 order by a desc;
4：查询238，917 这两个分类下的商品的平均单价
- select category_id, acg(price) from t_item where category_id in(238,917) group by category_id;
5：查询emp表中工资在1000-3000之间的员工，每个部门按编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，最后按照平均工资进行升序排序
- select deptno,sum(sal),avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>=2000 order by avg(sal);
6：查询每年入职的人数
- select extract(year from hiredate) y ,count(*) from emp group by y;
7：查询平均工资最高的部门和平均工资
- select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

子查询(嵌套查询)

1:查询工资高于平均工资的员工信息
- select avg(sal) from emp;
- select * from emp where sal>(select avg(sal) from emp);
2:查询员工表中工资最高的员工信息
- select * from emp order by sal desc limit 0,1;//方法1
//子查询
- select max(sal) from emp;
- select * from emp where sal=(select max(sal) from emp);

3:查询工资高于20号部门最高工资的员工信息
- select max(sal) from emp where deptno=20;
- select * from emp where sal>(select max(sal) from emp where deptno=20);

4:查询和Jones相同工作的其他员工信息
- select job from emp where ename='jones';
- select * from emp where job=(select job from emp where ename='jones') and ename!='jones';//注意： and :两边都要满足条件，or:满足其中一边条件

5：查询工资最低员工的部门的同事们的信息(同事指同一部门)
- select min(sal) from emp;//查询最低工资
- select deptno from emp where sal=(select min(sal) from emp);//查询最低工资的部门
- select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal !=(select min(sal) from emp);

6：查询最后入职的员工的信息
- select max(hiredate) from emp;
- select * from emp where hiredate=(select max(hiredate) from emp);

7：查询king的部门的信息(需要用到dept表)
- select * from emp e, dept d where e.ename='king' and e.deptno=d.deptno;//多表查询
=子查询= 
-select deptno from emp where ename='king';
-select * from dept where deptno=(select deptno from emp where ename='king');

8:查询员工表中出现过的部门信息(部门表里面有个40号部门，需要过滤它)
- select distinct deptno from emp;//查询员工表中的部门编号
- select * from dept where deptno in(select distinct deptno from emp);

9:查询平均工资最高的部门信息
- 先查询平均工资最高的值
select avg(sal) a from emp group by deptno order by a desc limit 0,1;
- 通过最高的平均工资 查询 部门编号
select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
-通过部门编号查询 部门信息
select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

子查询可以写的位置

1：写在 where 和 having 后面，当做查询条件的值
2：写在创建表的时候,把查询结果保存到新的表中
- create table emp_10 as (select * from emp where deptno=10);
3：写在 from 后面 **一定要有别名**
- select * from emp where deptno=10;
- select ename from (select * from emp where deptno=10) t;
1
2
3
4
5
6

关联查询

-同时查询多张表的查询方式称为关联查询
- 关联查询必须写关联关系，如果不写则会得到两张表的乘积(笛卡尔积 ，这个一个错误的查询结果)
1：查询每一个员工的姓名和对应得部门名
- select ename ,dname from emp e,dept d where e.deptno=d.deptno;	
1
2
3
4

关联查询的查询方式之 等值连接

1：等值连接
- select * from A,B where A.x=B.x and a.y>2000;
1
2

关联查询的查询方式之 内连接

2：内连接 [inner] join  **重点使用**
- select * from A [?inner] join B on A.x=B.x where a.y>2000;

查询每一个员工的姓名和对应得部门
- select e.ename ,d.dname from emp e JOIN dept d ON e.deptno=d.deptno;
- select e.ename ,d.dname from emp e join dept d on e.deptno=d.deptno;

查询工资高于2000得每个员工的姓名和对应的部门地点
- select e.ename,e.sal,d.loc from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
查询在 new york 工作的员工的姓名和工资
- select e.ename ,e.sal from emp e join dept d on e.deptno=d.deptno where d.loc='new york';
查询James的部门和地点
- select e.ename,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno where e.ename='james';
1
2
3
4
5
6
7
8
9
10
11
12
13

关联查询的查询方式之 外连接

-等值连接和内连接查询的时两张表的交集数据
-外连接查询的时一张表的全部数据和另外一张表的交集数据
格式：
 内连接
- select * from A join b on a.x=b.x where a.y>xxx;
 外连接
- select * from A left/right join b on a.x=b.x where a.y>xxx;
查询所有的部门名和对应的员工姓名
- select dname,ename from dept d left join emp e on e.deptno=d.deptno;
1
2
3
4
5
6
7
8
9

• 总结：如果查询的数据是两张表的交集数据使用等值连接或内连接(推荐)
  • 如果查询下的是一张表的全部数据和另外一张表的交集数据则使用外连接

练习题

1：每个部门的人数，根据人数进行降序排序
- select d.dname ,count(*) c from emp e join dept d on e.deptno=d.deptno group by dname order by c desc;
 select deptno ,count(*) c from emp group by deptno order by c desc;
2：每个部门中，每个主管的手下人数
- select deptno,mgr ,count(*) from emp where mgr is not null group by deptno,mgr;
3：每种工作的平均工资
- select job,avg(sal) from emp group by job;
4：每年入职人数
- select extract(year from hiredate) e,count(*) from emp group by e;
5：拿最低工资的员工信息
- select * from emp where sal=(select min(sal) from emp);

6:少于等于3个人的部门信息(考虑40号部门)
- select d.dname ,count(*) from emp e right join dept d on e.deptno=d.deptno where count(*)<=3; //error
(不考虑40号部门写法)
- select deptno, count(*) from emp group by deptno having count(*)<=3;//查询出部门少于3个人部门
- select * from dept where deptno in(select deptno from emp group by deptno having count(*)<=3);
（考虑40号部门）
select d.deptno,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.deptno having count(e.ename)>=3;//查找出部门编号
- select d.* from emp e right join dept d on e.deptno=d.deptno group by d.deptno having count(e.ename)>=3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

day05

数据的关系

1:一对一
2:一对多
3:多对多

** 多级关联：利用 自关联关系实现多级分类 **
1
2
3
4
5

view (视图)

利用一个查询语句构建一个虚拟表，如果查询就可以通过查询视图获得结果

利用视图可以重用复杂的查询功能，简化二次查询工作	

利用视图可以简化查询工作 
创建视图格式： create view viewName(视图名) as (查询语句);

视图不是表，视图中不存储数据，数据是通过内部封装的SQL语句动态查询得到  
1
2
3
4
5
6
7
8

管理view

-创建 create view 视图名 as (查询);
-查询全部视图 show full tables where table_type like 'view';
-删除视图 drop view 视图名
-视图可以嵌套使用
1
2
3
4