软件测试技术沉淀之常用SQL语句

涉及工具NavicatSQL语句(CRUD)一、增insert into 表名字段名 values 内容列表insert into student values (S0013,男,18) insert into SC(Sno,Cno) values(S0013,C005)二、删delete from 表名 where 条件delete from CS from SC join Student on SC.Sno Student.Sno where sdept 计算机 and Grade 60三、改update 表名 set 字段 表达式 where 条件update student set Sage Sage 1 where sdept 计算机四、查select 字段 from 表名 where 条件基本查询distinct消除重复行select distinct Cno from SC2、top查询限定行数--SQL Server select top 2 * from student --显示student表前2行 select top 50 percent * from student --显示student表前50%的数据 --MySQL SELECT * FROM table LIMIT 5 --检索前 5 个记录行 SELECT * FROM table LIMIT a-1,b-(a-1) --检索记录行a到b3、as使用别名select Sname as 姓名,出生年份为,2025-Sage as 出生年份 from student4、between···and···如果列或表达式的值在下限值和上限值内【包边界值】则结果为trueselect sno,cno,grage from sc where grage between 60 and 70 --查询60到70成绩的信息 --not between···and··· 同理5、and / or串联/并联select Sname from student where sdept计算机 and sdept数学 --and select Sname from student where sdept计算机 or sdept数学 --or --and优先级高于or但可以用括号改变优先级6、like模糊查询select Sname,Sno from student where Sname like _张% -- %:代表任意长度字符 _:代表任意单个字符 not like则同理7、null空值查询select Cno from student where pcno is null8、order by排序select * from student order by sdept,sage desc --asc升序可省略 desc降序聚合函数查询9、count( )统计个数select count(*) as 学生人数 from student10、max( )/min( )最高值和最低值select max(grade) as 最高分,min(grade) as 最低分 from sc where cno C00211、sum( )总和/avg( )平均select sum(grade) as 总分,avg(grade) as 平均分 from SC where Sno S0001分组统计12、group by···having···分组查询select sdeptcount(Sno) as 人数 from student where Ssex 男 group by sdept having count(Sno) 2多表查询13、内连接select Student.Sno,SC.Sno FROM Student join SC on Student.Sno SC.Sno14、外连接左外连接--含义查询A的所有数据和满足某一条件的B的数据 select * from person left join dept on person.dept_id dept.did --含义查询A中的所有数据减去与B满足同一条件的数据然后得到的A剩余数据 select * from person left join dept on person.dept_id dept.did where dept.did is null右外连接--含义B的所有数据和满足某一条件的A的数据 select * from person right join dept on person.dept_id dept.did三表连接SELECT last_name, first_name, dept_name FROM employees a LEFT JOIN dept_emp b ON a.emp_nob.emp_no LEFT JOIN departments c ON b.dept_noc.dept_no; where ···嵌套查询select s#,sn from s where s# in (select s# from sc join c on c.c# sc.c# where c.cn 税收基础) select sn,sd from s where s# in (select s# from sc where c# C2) select sn,sd from s where s# not in (select s# from sc where c# C5) select s#,sd from s where s# in (select s# from sc group by s# having count(distinct c#)5)