数据库笔记

day1

1.创建数据表
Create table 表名(参数
属性1 数据类型 是否为空(默认为空),
属性2 数据类型 是否为空(默认为空),
ID int,
Name varchar(10),
Sex nchar(1)
)
varchar(n)与nchar(n)的区别：varchar(n)如果字符长度没有超过n，字符长度会根据所输入的长度变化。nchar则会用空格补全。

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

2.删除表
drop table 表名

drop table student

3.修改表
Alter table 表名
–增加属性 add 属性名 属性值
–删除属性 drop column 属性名
–修改属性 alter column 属性名 属性类型 是否允许为空
修改属性类型一般情况只允许向范围大的类型修改，如果需要添加多个属性，则需要放在一个add语句里面，中间用逗号隔开，删除修改也是如此。
如果既需要添加有需要删除，则不能放到一个alter里面，要分开写

alter table student
add nation nchar(10),sex varchar(2);--增加民族,性别属性
alter table student
alter column nation varchar(10);--修改民族属性
alter table student
drop column nation;--删除民族属性

4.数据的添加

insert[into] 表名
values(属性值列表)

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

insert into student--添加数据
values('0922221326','李杰','男','1988-2-3','090501','754','13456734522','Lijie@sina.com');

alter table student
add nation nchar(10)

alter table student
alter column nation varchar(10)

alter table student
drop column nation

查询表中所有行，所有列
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(key,answer)如果key为空，则返回answer

表的链接

自连接：

--自连接：所有属性访问时必须加表名
--查询与韩吟秋性别相同的同学学号，姓名
Select sno,sname
From student a,student b
Where b.sname = '韩吟秋'
and a.sex = b.sex

--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
--等值连接，自连接，不等值连接：内连接

--聚集函数:自动忽略空值
--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 属性

--修改+子查询
--在北京工作的员工工资增加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')

修改/删除+子查询

--修改+子查询
--在北京工作的员工工资增加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')