基本SQL与实战
1. 基本SQL语句
- 选择：select * from table1 where 范围
- 插入：insert into table1(field1,field2) values(value1,value2)
- 删除：delete from table1 where 范围
- 更新：update table1 set field1=value1 where 范围
- 查找：select * from table1 where field1 like '%value1%' ---like的语法很精妙，查资料!
- 排序：select * from table1 order by field1,field2 [desc]
- 总数：select count as totalcount from table1
- 求和：select sum(field1) as sumvalue from table1
- 平均：select avg(field1) as avgvalue from table1
- 最大：select max(field1) as maxvalue from table1
- 最小：select min(field1) as minvalue from table1
2. 基本SQL语句实例
1. 简单查询(select,from)
语法：
select 函数; (查看系统函数的方法"? functions")
select 字段1,字段2,...
from 表名
应用案例：
1.查询当前登录用户
select user();
2.查看当前数据库
select database();
3.查看当前时间;
select now();
4.查询部门表中所有数据？
select * from departments
;其中"*"代表所有字段
5.查询所有部门id,名字？
select department_id,department_name from departments
或者select department_id as id,department_name from departments d;
其中"id"为列别名，"d"为表别名，中间的"as" 可以省略
6.查询雇员薪水加300以后的值？
select first_name,salary+300 newSalary from employees;
--------------------------------------- ------------------
2. 限制查询(limit,where)
1.查询所有雇员的名字，薪水， 只显示前5条记录。
select first_name,salary
from employees
limit 5;
2.查询所有雇员的名字，薪水， 只显示第3条到第5条数据。
select first_name,salary
from employees
limit 2,3; (从>2位置取3条)
limit子句通常应用于分页查询 现有一需求：10条记录，每页最多显示3条，总计分多少页？4页 公式： 10%3==0?10/3:10/3+1
如何取出第二页的数据？(以雇员为例) select first_name,salaryfrom employees limit 3,3;
公式： limit (页码-1)*3,3;(其中3为每页最多显示的记录数)
说明：假如一个查询语句中有limit子句， 那么此子句要放在所有语句的最后。
limit 子句的另外一种形式： select first_name,salary from employees limit 3 offset 2; (从>2位置取3条)
3.查询薪水大于10000的雇员的名字和薪水
select first_name,salary
from employees
where salary>10000;
其中"where"子句后是查询条件，假如 有where子句，where子句一定是跟在 from子句后。
4.查询薪水在10000到20000之间的雇员 的名字和薪水
select first_name,salary
from employees
where salary>=10000 && salary<=20000;
5.查询薪水在10000到20000之间的雇员的名字和薪水,只显示前两条记录。
select first_name,salary
from employees
where salary>=10000 && salary<=20000
limit 2;
6.查询1999年入职的所有员工的名字，薪水。
方法1：
select first_name,salary,hire_date
from employees
where hire_date like '1999%'
其中"%" 代表任意的0个或多个字符。
方法2：
select first_name,salary,hire_date
from employees
where year(hire_date)='1999'
7.查询所有2月份入职的员工的名字，薪水?具体入职日期？
select first_name,salary,hire_date
from employees
where month(hire_date)='02';
或者
select first_name,salary,hire_date
from employees
where hire_date like '%-02-%'
8.查询所有佣金为null雇员的名字，薪水?
select first_name,salary
from employees
where commission_pct is null
9.查询名字中第二个字母为a的雇员的名字?
select first_name
from employees
where first_name like '_a%';
其中"_"表示任意的1个字符；
----------------------------------------- --------------------
3. 排序查询(order by 列名 [ASC|DESC])
1.查询所有雇员信息，按入职日期升序排序。
select first_name,hire_date
from employees
order by hire_date asc;
说明：假如是升序asc可以省略。
2.查询所有薪水大于8000的雇员的名字和薪水, 并按薪水降序排序。
select first_name,salary
from employees
where salary>8000
order by salary desc;
假如在此查询语句中没有limit子句，
那么order子句就是最后。
3.查询所有薪水大于8000的雇员的名字和薪水-并按薪水降序排序，薪水相同按入职日期, 升序排序。
select first_name,salary sal,hire_date
from employees
where salary>8000
order by sal desc,3 asc;
4.查询所有雇员中薪水最高的雇员的名字和薪水
select first_name,salary,hire_date
from employees
order by salary desc
limit 1;
------------------------------------------ -----------------
4.分组查询(group by ,having )
1.求每个部门的平均薪水？
select department_id,avg(salary)
from employees
where department_id is not null
group by department_id
其中"group by"子句用于分组。
2.求每个部门的平均薪水,只显示平均薪水
大于5000的记录。
select department_id,avg(salary)
from employees
where department_id is not null
group by department_id
having avg(salary)>5000;
假如有having子句，一定要跟在group by
子句之后。用于对分组以后的结果进行限制。
3.求每个工种(job_id)的平均薪水,并平均
薪水降序排序。
select job_id,avg(salary)
from employees
group by job_id
order by 2 desc;
4.求每年入职的人数？(按年份进行分组)
提示：人数统计用"count(*)";
select year(hire_date),count(*)
from employees
group by year(hire_date);
5.求每个部门的薪水的总和，
薪水的最大值，最小值？
select sum(salary),max(salary),min(salary)
from employees
where department_id is not null
group by department_id;
6.求雇员的总数，薪水的总和
select count(*),sum(salary),avg(salary)
from employees;
----------------------------------------- --------------------
5. 嵌套查询(查询中还有查询)
1.查询比雇员102的薪水还要高的雇员的
名字和薪水？
select first_name,salary
from employees
where salary>(
select salary
from employees
where employee_id=102);
2.求比部门80平均薪水高的部门的id和平均薪水。
select department_id,avg(salary)
from employees
where department_id is not null
group by department_id
having avg(salary)>(
select avg(salary)
from employees
where department_id=80);
3.求人数最多的那个部门的部门名称。
1)求每个部门的人数
2)求部门人数的最大值
3)求人数等于这个最大值的部门id
4)根据部门id求得部门名称
select department_name
from departments
where department_id=(
select department_id
from employees
where department_id is not null
group by department_id
having count(*)=(
select max(cmax)
from (
select department_id,count(*) cmax
from employees
where department_id is not null
group by department_id) e1));
说明："from"子句后有子查询，子查询后
一定要有一个别名(表别名)。
----------------------------------------- ---------------------
6. 多表查询(inner join,outer join)
1.求雇员102所在的部门的部门名称？
嵌套查询：
select department_name
from departments
where department_id=(
select department_id
from employees
where employee_id=102);
多表查询：
select employee_id,department_name
from employees e,departments d
where e.department_id=d.department_id
and e.employee_id=102;
或者
select employee_id,department_name
from employees e inner join departments d
on e.department_id=d.department_id
where e.employee_id=102;
其中"inner join" 就是内连接,"inner"可以
省略,"on"后写连接条件，where 后写查询
条件。
2.求雇员102所在的部门的部门名称以及 这个部门所在的城市。
select employee_id,e.department_id,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and e.employee_id=102;
当select子句后出现了from后的表中
的一个公共字段，此字段要进行标识，
要么使用表名，要么使用表别名。
或者
select employee_id,department_name
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
where e.employee_id=102;
3.求每个雇员及这个雇员的部门信息？
select first_name,department_name
from employees e left outer join departments d
on e.department_id=d.department_id;
其中"left outer join"为左外连接,
其含义为：左边表满足记录的和不满足
记录的都显示，右边表只显示满足记录的。
"outer"可以省略
- 求每个部门及这个部门中的雇员的信息？
select first_name,department_name
from employees e right outer join departments d
on e.department_id=d.department_id;
其中"right outer join"为右外连接,
其含义为：右边表满足记录的和不满足
记录的都显示，左边表只显示满足记录的。
5.笛卡尔积
多张表查询时，没写连接条件。
select first_name,salary,department_name
from employees,departments;
通常借助此种形式的查询返回的结果，做为
测试数据用。
例如：快速建表
create table temp
as
select first_name,salary,department_name
from employees,departments
========================================================
