SQL语句
当输出全表时，可以有两种写法，如下：
（1）select id,gender,age,university,province
from user_profile;
（2）select *
from user_profile;
使用select +（所有列名）from 表名 然后结尾，这样读取效率最高；
select * from 表名，只适合自己看一眼，使劲开发者效率低（因为需要将*转化为每一个列名）
2、查询结果去重
二种方式：
（1）distinct 关键字去重，放在列的前面使用
select distinct university
from user_profile;
（2）分组，以分组来筛选出去重的结果
select university
from user_profile
group by university;
3、查询结果限制返回行数
select device_id from user_profile limit 0,2 运行效率更高
select device_id from user_profile limit 2 运行效率低
limit offset 一起使用时，limit表示要取的数量，offset表示跳过的数量
select device_id from user_profile limit 2 offset 0 运行效率居中
//跳过0条，从第一条数据开始取，取两条数据
4、查询后的列重新命名
起别名关键字as,其中as可以省略
select device_id as user_infors_example from user_profile;
as写不写都可以，别名加不加引号（单双）都可以，加引号，别名就是引号内的内容，不加引号，别名如果为小写，会解析为大写，别名实际上为大写，以上两点在调用别名时要注意，易报错；找不到对应的列（大小写对应的是不同的列）
5、查找学校是北大的学生的信息
select device_id,university from user_profile where university = ‘北京大学’;
select device_id,university from user_profile where university like ‘%北京%’;
6、查找年龄大于24岁的用户信息
select device_id,gender,age,university from user_profile where age is not null and age > 24;
null是大于所有数值型还是小于所有数值型是由DBMS决定的，严谨起见还是加上age is not null的条件。
7、查找某个年龄段的用户信息
select device _id,gender,age from user_profile where age >= 20 and age <= 23;
或者 age between 20 and 23;
between后面指定范围的下限，and后面指定范围的上限，包括边界值。
8、查询所有班名是“电子信息工程技术”、“电子声像”或“电子组装技术”的班名和班号。
select 号，班名 from 班级表 where 班名 in (‘电子信息工程技术’，‘电子声像’，‘电子组装技术’)
等价于
select 班号，班名 from 班级表 where 班名=‘电子信息工程技术’or 班名=‘电子声像’or 班名=‘电子组装技术’；
9、查询班名既不是“电子信息工程技术”、也不是“电子声像”和“电子组装技术”的班号和班名。
select 班号，班名 from 班级表 where 班名 not in (‘电子信息工程技术’，‘电子声像’，‘电子组装技术’)；
等价于
select 班号，班名 from 班级表 where 班名！=‘电子信息工程技术’and 班名!=‘电子声像’and 班名!=‘电子组装技术’；
10、用where过滤空值
（1）select device_id,age from user_profile where age is not null;运行效率高
（2）select device_id,age from user_profile age !=‘’；运行效率低
（3）select device_id,age from user_profile age != ‘null’;
11、查询所有系号大于1且以“电子”开头的班号和班名
select 班号，班名 from 班级表 where 系号>1 and 班名 like ‘电子%’；
12、查询112P班和114D班所有男生的学号、姓名、性别和班号
select 学号，姓名，性别，班号 from 学生表 where (班号=‘112P’or 班号 =‘114D’ ) and 性别=‘男’；
等价于
select 学号，姓名，性别，班号 from 学生表 where 班号 in(‘112P’，‘114D’) and 性别 =‘男’；
13、查看所有大学中带有北京的用户的信息
select device_id,age,university from user_profile where university like ‘%北京%’;
14、聚合函数
count(*):统计表中元组个数；
count([distinct] 列名):统计本列列值个数；
sum(列名):计算列值总和；
avg(列名):计算列值平均值;
max(列名):求列值最大值;
min(列名):求列值最小值;
上数函数除count(*)外，其他函数在计算过程中均忽略null值。
统计函数不能出现在where子句中。
例如，查询成绩最高的学生的学号，如下写法是错误的：
select 学号 from 成绩表 where 成绩=max(成绩);
15、统计学生总人数
select count(*) from 学生表；
16、统计选修了课程的学生的人数
select count(distinct 学号) from 成绩表；
17、计算学号为“1124D”的学生的考试总成绩之和。
select sum(成绩) from 成绩表 where 学号=‘1124D’;
18、计算“M01F011”课程的学生的考试平均分
select avg(成绩) from 成绩表 where 课程号=‘M01F011’；
19、选修了“M01F011”课程的最高分和最低分
select max(成绩) 最高分，min(成绩) 最低分 from 成绩表 where 课程号=‘M01F011’；
20、round()函数
round函数用于把数值字段舍入为指定的小数位数。
select round(column_name,decimals) from table_name;
column_name要舍入的字段，decimals要返回的小数位数
保留一位小数
select round(avg(gpa),1) as avg_gpa from user_profile where gender=’male’;
21、分组语句的一般形式：
[group by]
[having]
group by 子句中的分组依据列必须是表中存在的列名，不能使用as子句指派的结果集列的别名。带有group by 子句的select语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。
22、查询每名学生的选课门数和平均成绩
select 学号，count(*) 选课门数，avg(成绩) 平均成绩 from 成绩表 group by 学号；
23、使用having
having 子句用于对分组后的结果再进行过滤，它的功能有点像where子句，但它用于组而不是单个记录。在having 子句中可以使用统计函数，但在where子句中则不能。having 通常与group by子句一起使用。
select 班号，count(*) 人数 from 学生表 group by 班号 having count(*)>=3;
24、having 与where 的区别
当聚合条件作为筛选结果时，不可以使用where函数，要使用having函数
假设有goods_name,goods_price,goods_type三个字段，表为:goods
（1）筛选出价格>10元的商品所有信息
select * from goods where goods_price > 10;
select * from goods having goods_price>10;
这个时候having和where 都可以使用
（2）筛选出价格>10元的商品名字和商品类型
select goods_name,goods_type from goods where goods_price >10;
select goods_name,goods_type from goods having goods_price >10(会报错！！！);
这时候会发现having 会报错，原因是因为having是针对前面提到的字段，然后进行条件限制。但是where是针对数据库里的原始字段做条件限制
25、不去重：union all
select device_id,gender,age,gpa from user_profile where university =’山东大学’；
union all
select device_id,gender,age,gpa from user_profile where gender=’male’;
26、查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。
select 学号，课程号，
case
when 成绩 >= 90 then ‘优’
when 成绩 between 80 and 89 then ‘良’
when 成绩between 70 and 79 then ‘中’
when 成绩between 60 and 69 then ‘及格’
when 成绩 < 60 then ‘不及格’
end 成绩
from 成绩表
where 课程号=‘M01F011’；
27、统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。
select 班号，count(case when 性别=‘男’then ‘男’ end) 男生数，
count(case when 性别=‘女’then ‘女’ end) 女生数
from 学生表
group by 班号；
28、判断成绩的等级，85~100为“优”，70~84为“良”，60~69为“及格”，60以下为“不及格”，并统计每一个等级的人数。
select case
when grade between 85 and 100 then ‘优’
when grade between 70 and 84 then ‘良’
when grade between 60 and 69 then ‘及格’
else ‘不及格’
end 等级，count(*) 人数
from sc
group by
case
when grade between 85 and 100 then ‘优’
when grade between 70 and 84 then ‘良’
when grade between 60 and 69 then ‘及格’
else ‘不及格’
end;
29、将用户划分为20岁以下，20-24岁，25岁以上三个年龄段，分别查看不同年龄段用户的明细情况。
select device_id,gender,case
when age <20 then ‘20岁以下’
when age>=20 and age <25 then ‘20-24’
when age>=25 then ‘25岁以上’
else ’其他’
end age_cut
from user_profile;
30、多表查询方式
连接n个表，至少需要n-1个连接条件。
多表连接查询时，若两个表有同名的列，必须使用表的别名对列名进行引用，否则出错！
按照department_id查询employees(员工表)和departments(部门表)信息。
（1）通用型：select … from … where …
select e.last_name,e.department_id,d.department_name
from employees e,departments d
Where e.department_id = d.department_id;
（2）select … from … natural join …
有局限性：会自动连接两个表中相同的列（可能有多个：department_id和manager_id）
select last_name,department_id,department_name
from employees natural join departments;
（3）select … from… join… using…
有局限性：好于（2），但是若多表的连接列列名不同，此法不合适。
select last_name,department_id,department_name
from employees join departments using(department_id);
（4）select… from … join… on…
常用方式，较（1），更容易实现外连接（左、右、满）
select last_name,department_id,department_name
from employees e join departments d on e.department_id=d.department_id;
31、查询平均工资最低的部门信息和该部门的平均工资
select d.*,(select avg(salary) from employees where department_id= d.department_id)
from departments d
where department_id = (select department_id
from employees
having avg(salary)=(select min(avg(salary))
from employees
group by department_id
)
group by department_id
)
32、各个部门中 最高工资中最低的那个部门的 最低工资是多少
select min(salary)
from employees
where department_id =(select department_id
from employees
having max(salary)=(select min(max(salary))
from employees
group by department_id
)
group by department_id
)
33、查询各部门中工资比本部门平均工资高的员工的员工号，姓名，工资
select employee_id,last_name,salary
from employees e1
where salary>(select avg(salary)
from employees e2
where e1.department_id = e2.department_id
group by department_id
)
34、创建表
（1）create table emp1(
id number(10),
name varchar2(20),
salary number(10,2),
hire_date date)
（2）create table emp5
as
select employee_id id,last_name name,hire_date,salary
from employees
where 3=2；
(3=2，不相等，创建出来的是一个空表,如果去掉where 3=2则会把employees表中的信息导入emp5表)
35、
| 语句 | 描述 |
| create table | 创建表 |
| alter table | 修改表结构 |
| drop table | 删除表 |
| rename to | 重命名表 |
| truncate table | 删除表中的所有数据，并释放存储空间 |
以上这些DDL(数据定义语言)的命令，操作外，皆不可回滚！ DML(数据操纵语言)
36、对现有的表进行修改操作
（1）添加一个新列
alter table myemp
add(age number(3));
（2）修改现有列的类型
alter table myemp
modify(name varchar2(30));
（3）修改现有列的名字
alter table myemp
rename column sal to salary;
（4）删除现有列
alter table myemp
drop column age;
37、从其他表中拷贝数据
（1）insert into emp2
select *
from employees
where department_id=90;
（2）insert into sales_reps(id,name,salary,commission_pct)
select employee_id,last_name,salary,commission_pct
from employees
where job_id like ‘%REP%’;
（3）insert into course
values(‘CS-437’,’Database Syetem’,’Comp.Sci.’,4);
insert into course(course_id,title,dept_name,credits)
values(‘CS-437’,’Database Syetem’,’Comp.Sci.’,4);//顺序一一对应
37、删除
（1）从instructor关系中删除属于Finance系的教师的所有元组。
delete from instructor
where dept_name=‘Finance’;
（2）从instructor关系中删除所有这样的教师元组：他们在位于Watson大楼的系里工作。
delete from instructor
where dept_name in (select dept_name
from department
where building=’Watson’);
38、更新
（1）所有教师的工资将增长5%。
update instructor
set salary=salary*1.05;
（2）只给工资低于70000美元的教师涨工资。
update instructor
set salary = salary*1.05
where salary<70000;
（3）交换所有的‘f’和‘m’,即将所有‘f’变为‘m’,反之亦然，仅使用单个update语句，且不产生中间零时表。
update salary
set sex= case sex when “m” then “f” else “m” end;
39、索引
create index <索引名> on <关系名>（<属性列表>）;
在instructor关系上定义以dept_name为搜索码的、名为dept_index的索引。
create index dept_index on instructor (dept_name);
40、查询员工的last_name，department_id,salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,department_id相同即可。
select last_name,department_id,salary
from employees
where (salary,department_id) in (select salary,department_id
from employees
where commission_pct is not null
)
41、选择所有没有管理者的员工的last_name
select last_name
from employees e1
where not exists(select ‘A’
from employees e2
where e1.manager_id=e2.employee_id
)
42、分析函数
rank() over(order by 排序字段 顺序)
rank() over(partition by 分组字段 order by排序字段 顺序 )
（1）值相同，排名相同，序号跳跃
select rank() over(order by usenum desc) ‘排名’,t.*
from t_account t;
（2）值相同，排名相同，序号连续
select dense_rank() over(order by usenum desc) ‘排名’,t.*
from t_account t;
（3）序号连续，不管值是否相同
select row_number() over(order by usenum desc) ‘排名’,t.*
from t_account t;
（4）查询各科学生排名（分区排名）
select student_id,student_name,sub_name,score,rank() over(partition by sub_name order by score desc) ‘名次’
from t_score;
desc 降序 asc升序(不写默认升序) 
1229
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
