?????????????????????????(attribute)

/*????????:????USER_INFO??GRADE??????????RANK*/
sp_rename  'USER_INFO.[GRADE]'??'RANK'??'COLUMN';
/*?????ε?????????*/
alter table USER_INFO modify JOB_ID tinyint;
/*?????????*/
alter table USER_INFO ADD DELETED tinyint;
/*??????*/
alter table USER_INFO drop STATE;

??????????????????????????(record)

/*??????*/
insert into USER_INFO
values('000001'??'Funny'??5055??18??3??1);
insert into USER_INFO
values('000002'??'John'??5056??19??3??1);
/*?????:??????where??????????????????м??*/
update USER_INFO set NAME='Fantasy' where USER_NO=
'000001';
/*??????:??????where??????????????????м??*/
delete from USER_INFO where USER_NO='000001';

?????壯?????????(query analysis)

1. ??????????????????????
/*????????Σ??????????????????????(?????????е??????????)*/
select count(*) from DEPARTMENT;
/*??????????PARENT_DEPT_ID??????????:
PARENT_DEPT_ID????????????*/
select count(PARENT_DEPT_ID) from DEPARTMENT;
/*??????????PARENT_DEPT_ID??????????(???????????):
PARENT_DEPT_ID??????????????*/
select count(distinct PARENT_DEPT_ID) from DEPARTMENT;
/*???????????????????8??????
DEPT_ID??????????(8?????????????)*/
select count(DEPT_ID) from DEPARTMENT where PARENT_DEPT_ID=8;

2.??????????????????
/*?????USER_INFO?е????м??*/
select * from USER_INFO
/*?????DEPARTMET?е????м??PARENT_DEPT_ID????????:
????NULL??????*/
select  PARENT_DEPT_ID  from DEPARTMENT;
/*?????DEPARTMET?е????м??PARENT_DEPT_ID????????:
????NULL????????????*/
select  distinct PARENT_DEPT_ID  from DEPARTMENT;
/*?????DEPARTMET?е????м??PARENT_DEPT_ID????????:
??????NULL??????*/
select  distinct PARENT_DEPT_ID  from DEPARTMENT where PARENT_DEPT_ID is not null;

3.??????
/*????????????NULL????????????*/
select  PARENT_DEPT_ID  from DEPARTMENT group by PARENT_DEPT_ID;
/*??????????????NULL????????????*/
select  PARENT_DEPT_ID  from DEPARTMENT where PARENT_DEPT_ID is not null
group by PARENT_DEPT_ID;
/*??????з?????????????????????*/
select  PARENT_DEPT_ID??count(*)  from DEPARTMENT where PARENT_DEPT_ID is not null
group by PARENT_DEPT_ID;
---- ????????(???????????Сд) ----
/*?????:?????????l|L???????????*/
select * from USER_INFO where NAME like 'l%'
/*?????:?????????ry|Ry|rY|RY??β????????*/
select * from USER_INFO where NAME like '%rY';
/*????:????????а???n|N????????*/
select * from USER_INFO where NAME like '%n%'

4.????? where???????????
/*??????????????????????*/
select USER_INFO.*??DEPARTMENT.*
from   USER_INFO ?? DEPARTMENT
where  USER_INFO.DEPT_ID=DEPARTMENT.DEPT_ID
/*????????????6???????????????????in??exists????not??? */
--????????1
select USER_NO??NAME from USER_INFO
where DEPT_ID in
(
select DEPT_ID from DEPARTMENT
where PARENT_DEPT_ID=6
)
--????????2
select USER_NO??NAME from USER_INFO
where 6 IN
(
select PARENT_DEPT_ID from   DEPARTMENT
where  DEPARTMENT.DEPT_ID=USER_INFO.DEPT_ID
)
--????????3
select USER_NO??NAME from USER_INFO
where exists
(
select * from   DEPARTMENT
where  DEPARTMENT.DEPT_ID=USER_INFO.DEPT_ID
AND PARENT_DEPT_ID=6
)

5.??????
/*??????????????5?????7?????????????(??????)*/
(select  USER_NO??NAME from  USER_INFO where DEPT_ID=5)
union
(select  USER_NO??NAME from  USER_INFO where DEPT_ID=7);
--Ч???????????1????????
select USER_NO??NAME from USER_INFO
where DEPT_ID=some
(
select DEPT_ID from DEPARTMENT
where PARENT_DEPT_ID=6
);