数据库常用sql

查询完整的语法：
SELECT [ALL|DISTINCT] TOP n[PERCENT] WITH TIES select_list
[INTO[new table name]]
[FROM{table_name|view_name}[(optimizer_hints)]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]

1、查找多表指定列的数据
select pay_job_level,budget_year
from mbs_base_pay,mbs_budget

2、DISTINCT关键字，指定字段唯一
select DISTINCT pay_job_level
from mbs_base_pay

3.WHERE子句
select pay_job_level
from mbs_base_pay
where pay_post_type=‘策划类‘

select pay_job_level
from mbs_base_pay
where pay_post_type like ‘策划%‘

4.ORDER BY子句 升序 DESC 降序
select column_list
from table_name
order by [(order_by_expression[ASC|DESC])]
select last_name,job_id,salary
from employees
where salary>2000
order by job_id,salary desc;

5.GROUP BY 子句 分组查询
select job_id,salary
from employees
order by job_id;

6.统计函数
select job_id,avg(salary),sum(salary),count(job_id)
from employees
group by job_id;

7.HAVING 子句 和GROUP BU子句一起使用
select job_id,avg(salary),sum(salary),max(salary),count(*)
from employees
group by job_id
having avg(salary)>10000;

8.多表链接查询
1)简单查询
select employees_id,last_name,department_name
from employees,department
where employees.department_id=departments.department_id
and departments.department_name=‘Shipping‘;

2)表别名
select em.employees_id,em.last_name,dep.department_name
from employees em,department dep
where em.department_id=dep.department_id
and dep.department_name=‘Shipping‘;

9.集合操作
1)UNION 合集
select employee_id,last_name
from employees
where last_name like ‘C%‘ or last_name like ‘S%‘
union
select employee_id,last_name
from employees
where last_name like ‘S%‘ or last_name like ‘T%‘;

2)INTERSECT 交集
select employee_id,last_name
from employees
where last_name like ‘C%‘ or last_name like ‘S%‘
intersect
select employee_id,last_name
from employees
where last_name like ‘S%‘ or last_name like ‘T%‘;

3)MINUS 差集
select employee_id,last_name
from employees
where last_name like ‘C%‘ or last_name like ‘S%‘
minus
select employee_id,last_name
from employees
where last_name like ‘S%‘ or last_name like ‘T%‘;

10.子查询
1)IN 关键字
select employee_id,last_name,department_id
from employees
where department_id in(
select department_id
from departments
where location_id=1700);

2)比较运算符
select employee_id,last_name,job_id,salary
from employees
where job_id=‘PU_MAN‘ and
salary>=(sekect avg(salary) from employees
where job_id=‘PU_MAN‘);

11.INSERT 语句
1)一般INSERT语句
insert into jobs(job_id,job_title,min_salary,max_salary)
values(‘IT_TEST‘,‘测试员‘,3000,8000);

2)批量INSERT语句
insert into IT_EMPLOYEES（
employee_id,first_name,last_name,email,phone_number,job_salary,manager_id）
select em.employee_id,em.first_name,em.last_name,em.email,em.phone_name,em.job_id,em.salary,em.manager_id
from employees em,department dep
where em.department_id=dep.department_id
and dep.department_name=‘IT‘;

12.UPDATE语句
update employees
set salary = salary * 1.15
where job_id=‘IT_PROG‘;

update employees
set salary=
(select avg(salary)
from employees
where job_id=‘IT_PROG‘)
where employee_id=104;

13.DELETE语句 删除后可恢复
delete from it_employees
where employee_id=107;

14.TRUNCATE语句 删除后不能恢复，但表结构保留
truncate table it_employees;

数据库常用sql

标签：删除   type   链接   com   ber   res   测试   sql   ase