xianxi6.sql

SQL数据库的一些基本的操作源码 包括创建存储过程,表,触发器的创建

select *FROM employees

select empid as 员工编号,name as 姓名,department as 部门编号,memo as 备注,
birthdate as 出生日期, salary as 工资 from employees


select * from departments

select employees.empid, employees.name,departments.depname,employees.birthdate,
    employees.salary
from departments inner join employees
on departments.depid=employees.department

select employees.empid,employees.name,departments.depname,employees.birthdate,
    employees.salary
from departments inner join employees
on departments.depid=employees.department
order by employees.department

select *from employees where salary>2000

select employees.empid,employees.name,departments.depname,employees.birthdate,
    employees.salary
from departments inner join employees
on employees.department=departments.depid
where departments.depname='开发部' and employees.salary>1500

select*from employees where name like '王%'

select *from employees where year(birthdate) like '196[0-9]'

select * from employees where birthdate is null


select sum(employees.salary) as 工资和,departments.depname as 部门
from departments inner join 
employees on departments.depid=employees.department
where(departments.depname='开发部')
group by departments.depname

select e.empid,e.name,e.birthdate,departments.depname as 部门
from departments inner join employees as e on departments.depid=e.department
where not(departments.depname='开发部')

select top 3 employees.*
from employees
order by employees.salary DESC

select top 3 employees.*
from employees
where birthdate is not null
order by birthdate


select *from employees where salary>=2000 and salary<3000

select * from employees where memo like '%经理%'


select max(salary) as '最高工资',min(salary) as '最低工资',avg(salary) as '平均工资'
from employees

select departments.depname as '部门',count(*) as '人数'
from departments inner join 
employees on departments.depid=employees.department
group by departments.depname


select count(*) as '人数'
from employees 
where salary>3000


select departments.depname,avg(salary) as '平均工资'
from employees inner join departments on employees.department=departments.depid
group by departments.depname  order by avg(salary)

insert into employees 
values('0010','赵月',4,NULL,'1982-12-10',NULL)

update employees 
set salary=600
where salary is NULL

update employees 
set salary=salary+200
where salary<1500

update employees 
set salary =salary*1.1
from employees inner join departments on employees.department=departments.depid
where departments.depname='开发部'


delete from employees where year(getdate())-year(birthdate)>50


Delete employees from  emlpoyees inner join departments on employees.department=departments.depid
where departments.depname ='销售部'