测试sql掌握

1.插入表数据：
insert into 表名1 (字段1，字段2) values(字段1值，字段2值);
2.删除表数据：
delete：delete from 表名1 where 范围（删除表内符合条件的内容）
3.修改表数据：
update 表名1 set 字段名 = ‘新值’ where 范围
4.查询表数据：
查询数据：select * from table1 where 范围
总数：select count (*) from table1 where 范围
select count (distinct(字段1) from table1 where 范围（distinct可去重）
求和：select sum (字段1) from table1 where 范围
平均：select avg (字段1) from table1 where 范围
最大：select max (字段1) from table1 where 范围
最小：select min (字段1) from table1 where 范围
排序：select * from table1 where 范围 order by 排序字段名 desc（desc逆序排序。默认是正序排序asc）
5.多表连接查询 ，通常省略OUTER关键字
（1）内联查询（inner join……on……）
select * from table1 a inner join table2 b on a.id=b.id
（2）左外联（left outer join……on……）
select * from table1 a left outer join table2 b on a.id=b.id
以left关键字左边的边为主，结果会将左表所有的查询信息列出，而右表只列出on后条件与左表满足的部分，那么右表不满足的字段用null表示
将left关键字右边的表中不满足条件的数据用null表示
（3）右外联（right outer join……on……）
select * from table1 a right outer join table2 b on a.id=b.id
将right关键字右边的表为主，将right左边的表中不满足条件的数据过滤掉
（4）orcle全外联（full outer join……on……）
select * from table1 a full outer join table2 b on a.id=b.id
（5）mysql全外连接
两个表连接起来
select 字段A，字段B from table1 union all
select 字段C，字段D from table2

select distinct t.Sid,t.Sname from stu_db.student t
union all
select t2.sno,t2.sname from stu_db.test t2;

Union和union all区别
UNION 并集，表中的所有数据，并且去除重复数据（工作中主要用到的是这个）；
UNION ALL，表中的数据都罗列出来,并不会去重；

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。需要满足以下条件：
1、相同数量的列；
2、列也必须拥有相似的数据类型；
3、同时，每条 SELECT 语句中的列的顺序必须相同。

6.分页查询
分页查询格式：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
注意： **对ORWNUM 只能使用 < 或<=, 而用=，>,>= 都将不能返回任何数据。
对 oracle 分 页 必须使用 ROWNUM“伪列”。

7.group by和order by和having
注：group by中字段（如，c_name）都必须在 group by分组里 否则会出现 不是group by 表达式的错误
where字句中不能包含组函数，因此要用到HAVING。
having的用法：同where用法，having与group by连用。where是筛选单个记录，having是筛选分组记录（先分组，后筛选）
order by的列，必须出现在group by子句里的列（整个sql里出现的列）
1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。 2.where肯定在group by 之前。
3.where后的条件表达式里不允许使用聚合函数，而having可以。
– 按班级查询总成绩
select classno,sum(score) from stu_db.test group by classno
having sum(score) > 90
order by sum(score) ;

– if函数的使用 if(判断条件，条件成立返回结果1，条件不成立返回结果2)
select distinct classno ,if (classno in(101,102),“主课”,“非主课”) as cc
from stu_db.test;

select score,if(score>=90,“优秀”,“有待进步”)as level
from stu_db.test order by score desc;
– if与聚合函数
– 统计大于90分的总数和小于90分的总数
select count(if(score>=90,1,null)) as ‘优秀的数量’,
count(if(score<90,1,null))as ‘有待进步的数量’
from stu_db.test;
– case when函数的使用
select score, case when score<60 then ‘不及格’
when score < 70 then ‘一般’
when score < 80 then ‘中等’
when score < 90 then ‘良好’
else ‘优秀’ end as s_level
from stu_db.test;
– case when 与聚合函数的使用
select case when
score<60 then ‘不及格’
when score<70 then ‘一般’
when score < 80 then ‘中等’
when score < 90 then ‘良好’
else '优秀’end as s_level,
count(1) as cnt
from stu_db.test group by s_level;

8.Exists用于只能用于子查询，可以替代in，若匹配到结果，则退出内部
查询，并将条件标志为true，传回全部结果资料,in不管匹配到匹配不到都
全部匹配完毕，使用exists可以将子查询结果定为常量，不影响查询效果，
一、触发器简介
触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。
因此触发器不需要人为的去调用，也不能调用
例如:
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。

——索引的优点—–
–第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
–第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
–第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
–第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
–第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1） 在经常需要搜索的列上，可以加快搜索的速度；
6） 在经常使用在WHERE 子句中的列上面创建索引，加快条件的判断速度。
一、临时表的概念
 在我们操作的表数据量庞大而且又要关联其他表进行查询的时候或者我们操作的数据是临时性数据且在短期内会有很多DML操作(比如购物车)的时候或者我们做查询时需要连接很多个表的时候，如果直接操作数据库的业务表可能效率很低，这个时候我们就可以借助临时表来提升效率。