[大数据]数据库笔记

day1

1.创建数据库
Create database 数据库名(参数)

2.创建数据表
Create table 表名(参数
属性1 数据类型,
属性2 数据类型,
ID int,
Name varchar(10),
Sex nchar(1)
)

3.删除表
drop table 表名

4.修改表
Alter table 表名
–增加属性 add 属性名 属性值
–删除属性 drop column 属性名
–修改属性 alter column 属性名 属性类型 是否允许为空
(修改属性类型一般情况只允许向范围大的类型修改)

5.数据的添加

数据的添加方法1
insert[into] 表名 values(属性值列表)
（数据的添加是以元组为单位添加的，一加一行）
将属性值存入表中对位的属性中。

数据的添加方法2
insert[into] 表名(属性列表)
values(属性值列表)

属性列表表示要进行赋值的属性，属性值列表中的属性值个数、顺序、类型要与属性类表中的属性名相同。在添加时没有出现在属性列表中的属性去取null。不允许为空的属性必须出现在属性名列表中。

create table student(
  sno nchar(10) not null,
  sname nvarchar(8),
  sex nchar(1),
  birthday datetime,
  clno nchar(6),
  point smallint,
  phone nchar(11),
  email nvarchar(30)
);

create table course(
  cno nchar(6) not null,
  cname nvarchar(20),
  type nvarchar(8),
  period tinyint,
  credit numeric(4,1)
);
create table score(
  sno nchar(10) not null,
  cno nchar(6) not null,
  usually numeric(6,2),
  final numeric(6,2)
);
create table teacher(
  tno nchar(6) not null,
  tname nvarchar(8),
  major nvarchar(10),
  prof nvarchar(10),
  department nvarchar(12)
);
create table class(
  clno nchar(6) not null,
  classname nvarchar(12),
  department nvarchar(12),
  monitor nvarchar(8)
);
create table teach_class(
  tno nchar(6) not null,
  clno nchar(6) not null,
  cno nchar(6) not null
);
alter table student
add nation nchar(10);--增加民族属性
alter table student
alter column nation varchar(10);--修改民族属性
alter table student
drop column nation;--删除民族属性
insert into student--添加数据
values('0922221326','李杰','男','1988-2-3','090501','754','13456734522','Lijie@sina.com');

day2

查询：select
Select子句用于实现投影操作

查询表中所有行，所有列
Select *
From 表名

查询指定的列，结果中显示顺序与select子句中的属性名列表相同 distinct
Select 属性名1,属性二,······
From 表名

distinct：用于消除查询结果中的重复元组，卸载select关键词后，属性名列表之前
Select + distinct = 投影
–员工的编号、工资、年工资
Select id,salary,salary*12
From emp
(已知月工资，导出年工资)

属性的别名：在查询结果中可以通过属性别名更改结果中列的名称，定义别名只需在属性名之后空格隔开，直接定义属性别名。定义别名只需在属性名之后空格隔开，直接定义属性别名。
Select 属性名 属性别名
例如：
Select id salary,salary*12 year_salary
From emp

结果排序
Order by 属性名 ：将属性名结果按照指定的属性进行排序
Order by 后可以加多个属性，按照属性从左到右的顺序进行排序，当前一个属性相同时，按照后一个属性进行排序。
例子：查询员工的姓名，职位，工资，并将结果按照工资降序排列。
Select id,name,title,salary
From emp
Order by salary desc
语法：order by 关键字 desc(降序)/asc(升序) 默认为升序。

代码：

SELECT DISTINCT sno,sname,clno,point,isnull('无',email)
FROM student
SELECT DISTINCT cno,sno,usually*0.3+final*0.7 finals
FROM score
ORDER BY cno ASC,finals DESC
--isnull(A,B)如果为空返回A，否则返回B

day3

--自连接：所有属性访问时必须加表名
--每个员工的名字及其领导人名字
Select e.name,l.name
From emp e,emp l
Where e.manager_id = l.id

--mark的领导是谁
Select e.name,l.name
From emp e,emp l
Where e.manager_id = l.id and
		e.name= 'Mark'
--carmen是谁的领导
Select e.name,l.name
From emp e,emp l
Where e.manager_id = l.id
and l.name= 'Carmen'

--谁是领导
Select distinct l.name
From emp e,emp l
Where e.manager_id = l.id

Select e.id,e.name,l.name
From emp e,emp l
Where e.manager_id = l.id
--等值连接，自连接，不等值连接：内连接
--外连接 = 内连接 + 悬浮元组
Select e.id,e.name,l.name
From emp e left outer join emp l on e.manager_id = l.id

Select e.id,e.name,l.*
From emp e left outer join emp l on e.manager_id = l.id

Select e.id,e.name,l.*
From emp l right outer join emp e on e.manager_id = l.id

Select e.id,e.name,l.*
From emp l full outer join emp e on e.manager_id = l.id

Select e.id,e.name, l.name
From emp e right outer join emp l on e.manager_id = l.id

--不是领导的人名字
--查询：不是…………，没有…………，而且是多表的查询，一般用外连接+ is null处理
Select e.id,e.name, l.name
From emp e right outer join emp l on e.manager_id = l.id 
Where e.id is null

--没有员工的部门名称
Select d.name
From  dept d left join  emp e 
  on e.dept_id = d.id
Where e.id is null

--标准写法
Select e.name,l.name
From emp e inner join emp l
 on e.manager_id = l.id inner join dept d on e.dept_Id = d.id
	inner join  region r on  d.region_id  = r.id

--聚集函数:自动忽略空值
--count（属性），max（属性），min（属性）：数值，字符，日期
--sum（属性），avg（属性）：数值
--属性前加distinct 表示统计时去除重复值，否则默认为all，即统计所有值
--count(*)

select salary
from emp

--员工工资的平均值
Select avg(salary)
From emp

Select max(comm_pct)
From emp

Select avg(comm_pct)
From emp

Select count(comm_pct)
From emp
where comm_pct is not null

--提成有多少种值
Select count(distinct comm_pct)
From emp

select count(*)
from emp

--分组统计
--group by 属性1，属性2，……：表示将元组按指定的属性进行分组
--如果group by后有多个属性，按照属性出现的先后顺序分组，
--各个部门的平均工资
select dept_id,avg(salary)
From emp
group by dept_id

--各个部门各个职位的平均工资
select dept_id,title,avg(salary)
From emp
group by dept_id, title

--2号部门的平均工资
--如果select子句出现了聚集函数，那么其他属性要么出现在group by中
--要么也作为聚集函数的参数出现
select avg(salary)
From emp
Where dept_id = 2

select dept_Id,avg(salary)
From emp
Where dept_id = 2
--第一种
select dept_Id,avg(salary)
From emp
Where dept_id = 2
group by dept_id
--第二种
select dept_Id,avg(salary)
From emp
Where dept_id = 2

select avg(dept_Id),avg(salary)
From emp
Where dept_id = 2

select max(dept_Id),avg(salary)
From emp
Where dept_id = 2

--部门平均工资大于1500的部门编号和平均工资
--having 条件表达式：对分组做条件限定，在分组之后筛选，不能单独使用
--必须跟在group by之后
--Where 条件表达式：对元组做条件限定，在分组之前筛选
select dept_id,avg(salary)--错误
From emp
Where avg(salary)> 1500
group by dept_id

select dept_id,avg(salary)
From emp
group by dept_id
having avg(salary)> 1500

--完整select
--Select 
--From 数据来源
--Where 条件表达式  对元组的筛选
--Group by 属性 用于分组
	--Having 条件表达式 对分组的筛选
--Order by 属性

day4

--修改+子查询
--在北京工作的员工工资增加500
update emp
set salary = salary + 500
where id in(
select e.id
From emp e,region r,dept d
Where e.dept_id= d.id and d.region_id = r.id and
   city = 'beijing')

--删除+子查询
--删除在北京工作的员工
delete
from emp
where id in(
select e.id
From emp e,region r,dept d
Where e.dept_id= d.id and d.region_id = r.id and
   city = 'beijing')

嵌套查询

select *
from emp
where salary = (select min(salary) from emp)

--查询与molly职位相同的员工，不包含molly本人
select * 
from emp
where title = (select title from emp where name = 'molly')
and name <> 'molly'

--查询  in,相等时用in
insert into emp(id,name,manager_id,dept_id,title,salary)
values(25,'molly',1,2,'Stock clerk',1300)

select * 
from emp
where title = in (select title from emp where name = 'molly')
and name <> 'molly'

--查询比molly工资高的员工,代词all代表任意，any代表存在
select *
from emp
where salary > all
(
	select salary
	from emp
	where name = 'molly'
)
--第二种写法：all的代替写法，比最大值大就比所有人大
select *
from emp > 
(
select max(salary)
from emp
where name = 'molly'
)
--谁是领导
select distinct l.name
from emp e,emp l
where e.manager_id = l.id
--嵌套的写法 谁是领导
select *
from emp
where id in
(
	select manager_id
	from emp
)
--谁不是领导
select distinct l.name
from emp e right join emp l on e.manager_id = l.id
where e.id is null
--嵌套的写法，子查询在返回空值时要特殊处理，再返回多值时也要特殊处理
select *
from emp
where id not in
(
	select manager_id
	from emp
	where manager_id is not null
)
--部门的平均工资比2好部门平均工资高的部门编号
select dept_id
from emp
Group by dept_id
having avg(salary)>
(
	select avg(salary)
	from emp
	where dept_id = 2
)

--关联子查询:先执行父查询,子查询要执行多次
--工资比平均工资高的员工
select *
from emp
where salary>
(
	select avg(salary)
	from emp
)
--查询工资比本部门平均工资高的员工
select *
from emp me
where salary>
(
	select avg(salary)
	from emp
	where dept_id = me.dept_id
)
--关联子查询:先执行父查询,子查询要执行多次
--子查询要使用父查询的表,所以父查询中的表都是要有别名的

--Exists 子查询：子查询返回空值，则为假，否则为真
--有员工的部门
select *
from dept d
where exists
(
	select *
	from emp e
	where e.dept_id = d.id
)
--没有员工的部门
select *
from dept d
where not exists
(
	select *
	from emp e
	where e.dept_id = d.id
)
--是领导的员工
select *
from emp l
where exists
(
	select *
	from emp e
	where e.manager_id = l.id
)
--选修了所有课程的学生姓名
--不存在一门课程我没有选修
select sname
from student my
where not exists 
(
	select *
	from course c
	where not exists 
	(
		select *
		from score
		where score.cno = c.cno
		and score.sno = my.sno
	)
)

修改/删除+子查询

--修改+子查询
--在北京工作的员工工资增加500
update emp
set salary = salary + 500
where id in(
select e.id
From emp e,region r,dept d
Where e.dept_id= d.id and d.region_id = r.id and
   city = 'beijing')

--删除+子查询
--删除在北京工作的员工
delete
from emp
where id in(
select e.id
From emp e,region r,dept d
Where e.dept_id= d.id and d.region_id = r.id and
   city = 'beijing')