常用的SQL例句 数据库开发所需知识
2022-11-12 09:22:31
内容摘要
这篇文章主要为大家详细介绍了常用的SQL例句 数据库开发所需知识，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！--查看学生表的全部数据s
文章正文
这篇文章主要为大家详细介绍了常用的SQL例句 数据库开发所需知识，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
--查看学生表的全部数据select * from studio--插入一个新的学生信息insert into studio(st_name,st_sex,st_age,st_add,st_tel) values("黄兰淇",0,36,'南充','13943943334')--查看class全部数据select * from class--向class表增加两条条数据insert into class(cl_class,cl_coding,cl_o_time,cl_remark) values('新电实训班','GXA-ncs-001','2008-03-11','都是很优秀的朋友')insert into class(cl_class,cl_coding,cl_o_time) values('阿坝师专实训班','GXA-ABSZ-001','2008-03-11')--更新一条的数据 条件的重要性update class set cl_remark='真的是不错' where cl_id=5--删除一条数据 条件的重要性delete from class where cl_id=7--修改列标题select cl_id as '班级主键',cl_class as '班级名称' from classselect 名字=st_name from studio--使用文字串select '名字是：',st_name from studio--=============条件稍微复杂点的查增删改==============--主要涉及到 or and not between in like > < = !> !< != <> () <= >= is null is not null--查询cl_id 大于 1 的所有信息select * from class where cl_id>1--使用 orselect * from class where cl_id<>10 or cl_class='百杰一班'--使用andselect * from class where cl_id<>10 and cl_class='百杰一班'--使用like 和 %select * from class where cl_class like '百杰%'select * from class where cl_remark like '%上午%'--使用 betweenselect * from class where cl_id between 3 and 5--使用 between 配合上 notselect * from class where cl_id not between 3 and 5--使用 is not nullselect * from class where cl_remark is not null--使用 inselect * from class where cl_class in('千星一班','百杰二班')--=================使用数学运算符======================--主要涉及到 + = * \--查询Java相关课程分别要上多少周 按照每周5天，每天6节课来计算select '结果'=co_num/5/6 from course where co_name in ('Java基础','Java项目入门')--==================使用汇总函数 ========================--涉及到COUNT SUM AVG MAX MIN--查询课时数小于50的课程一共有多少门select count(*) from course where co_num<50--查询所有课程一共多少课时select sum(co_num) from course--计算全部课时费，假设每节课50块钱select sum(co_num)*50 from course--查询课时最少的课程select min(co_num) from course--查询课时最多的课程select max(co_num) from course--查询平均每门课多少课时select avg(co_num) from course--=================使用数学函数=============================--包括求绝对值函数ABS函数、求圆周率函数PI()、求正玄值SIN()函数、求指数函数EXP()等。--查询每门课的正弦值select sin(co_num) from course--查询每门课的绝对值select abs(co_num) from course--查询每门课课时数 乘以 圆周率 ，具体有什么用我也不知道，反正这好像绝对是8.5杆子都打不到的select pi()*co_num from course--查询每门课的指数select exp(co_num) from course--随机返回5个随机生成的数（返回的是0~1之间的随机float值）declare @i tinyintset @i=1while @i<=5beginselect rand(@i) as '随机生成的数' , @i as '当前值'set @i=@i+1end--返回数字表达式并四舍五入为指定的长度或精度 - ROUNDselect round(345.456,-1) as '参数为-1', round(345.456,-2,1) as '参数为-2', round(345.456,0) as '参数为0', round(345.456,1) as '参数为1', round(345.456,2) as '参数为2'--================使用日期函数======================--DAY()、MONTH()、YEAR()——返回指定日期的天数、月数、年数；select day(cl_s_time) as '日' from class --返回天select '月'=month(cl_s_time) from class --返回月select '年'=year(cl_s_time) from class --返回年--DATEADD(datepart,number,date)——在日期上增加给定日期类型的数量；select dateadd(yyyy,4,cl_s_time) as '增加4年后' from class --datepart - 年份yy、yyyyselect dateadd(q,2,cl_s_time) as '增加2季度后' from class--datepart - 季度qq、qselect dateadd(mm,3,cl_s_time) as '增加3月度后' from class--datepart - 月份mm、m--datepart - 每年的某一日dy、y--datepart - 日期dd、d--datepart - 星期wk、ww--datepart - 小时hh--datepart - 分钟mi、n--datepart - 秒ss、s--datepart - 毫秒ms--DATEDIFF(datepart,date1,date2)——获取两个日期之间给定的日期类型的数量差（整个函数结果是date2-date1）；select datediff(mm,cl_s_time,cl_o_time) as '共持续月' from class--datepart(datepart,date)——在给定日期基础上返回指定日期类型的值（整数）；--其实这个等同于DAY、MONTH、和 YEAR 函数select datepart(dd,cl_s_time) as '日期' from class--GETDATE()——返回当前日期和时间。我们在设计数据库的时候，通常也可能把他作为默认值update class set cl_s_time=getdate() where cl_id=6select * from classSelect CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 1): 05/16/06Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AMSelect CONVERT(varchar(100), GETDATE(), 10): 05-16-06Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16Select CONVERT(varchar(100), GETDATE(), 12): 060516Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AMSelect CONVERT(varchar(100), GETDATE(), 23): 2006-05-16Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 101): 05/16/2006Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AMSelect CONVERT(varchar(100), GETDATE(), 110): 05-16-2006Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16Select CONVERT(varchar(100), GETDATE(), 112): 20060516Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AMSelect CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM--=============使用字符串函数=====================--字符串链接运算符select '结果显示' = '班级名称是：' + cl_class + '，班级编号是：' + cl_coding from class--使用SUBSTRING函数截取字符串select substring(cl_class,1,4) from class--从字符串的左边开始返回3个字符select left(cl_class,3) from class--同理，返回右边的select right(cl_class,3) from class--返回值的字符数select len(cl_class) from class--替换select replace(cl_class,'实训','强化') from class--==============使用系统函数====================select host_id()--返回工作站标识号select host_name()--返回工作站所运行的计算机名称select db_id()select db_name()select object_id('Stu_course_ADD')--通过名称得到这个服务器对象的服务器IDselect object_name(151671588)--同上相反--=======学 云 网-天轰穿-[url]www.ixueyun.co m[/url]===使用其他子句====学 云网- 天轰 穿-[url]www.ixueyun.com[/url]=========--首先是 order by 功能 - 排序select * from studio order by st_name--多排序条件select * from studio order by st_name DESC,st_age DESC,st_sex DESC--有条件,主要是看下条件和子句的位置select * from studio where cl_id=1 order by st_name--GROUP BY 子句 功能 - 分组统计select cl_id as '班级编号',count(*) as '人数' from studio group by cl_id--按宿舍统计年龄平均值select ho_id as '宿舍编号',avg(st_age) as '平均年龄' from studio group by ho_id--多分组select ho_id as '宿舍编号',cl_id as '班级编号',avg(st_age) as '平均年龄' from studio group by ho_id,cl_id--有条件,主要是看下条件和子句的位置select ho_id as '宿舍编号',avg(st_age) as '平均年龄' from studio where cl_id=1 group by ho_id--使用 having 子句 功能 - 指定组或者聚合的搜索条件，通常与group by 子句一起使用，完成分组查询后再进步筛选select ho_id as '宿舍编号',avg(st_age) as '平均年龄' from studio group by ho_id having avg(st_age)>35--多条件select ho_id as '宿舍编号',avg(st_age) as '平均年龄' from studio group by ho_id having avg(st_age)>35 and ho_id>2--===========联合查询=======学 云 网-天轰穿-[url]www.ixueyun.com[/url]======--使用union子句的查询称为联合查询，功能：将两个以上的查询结果集组合为一个单个结果集，该集中包括所有集中的全部行数据--下面我们尝试将多个查询联合起来select * from studio where cl_id=1unionselect * from studio where ho_id=1unionselect * from studio where st_age>=30--下面我们继续利用上面的例题，增加上 All 看下效果select * from studio where cl_id=1union allselect * from studio where ho_id=1union allselect * from studio where st_age>=30--再继续利用，给他加上排序select * from studio where cl_id=1union allselect * from studio where ho_id=1union allselect * from studio where st_age>=30order by st_id--===========连接查询==================--连接查询，功能 - 将多个表中的数据查询出来放在一起--内连接：使用比较运算符=><....等进行表间某些数据库的比较操作，并列出这些表中与连接条件相匹配的数据行--等值连接，当然就是用等号了，毛病，这也要问select * from studio inner join class on studio.cl_id = class.cl_id--指明要查询的列（江湖上又称自然连接），并排序select st_id as '编号',st_name as '学生姓名',cl_class as '班级名称' from studio inner join class on studio.cl_id = class.cl_id order by st_id--使用表别名select st.st_name as '学生姓名',st.cl_id as '班级编号',cl.cl_class as '班级名称' from studio as st inner join class as cl on st.cl_id = cl.cl_id--不等连接，这个问题很好笑，既然使用等号的是等值连接，那么不等值你说是不是应该是非等于以外的呢？--下面我们再连接第三个表，看下是怎么搞滴select st.st_name as '学生姓名',st.cl_id as '班级编号',cl.cl_class as '班级名称' ,ho.ho_coding as '所在宿舍编号'from studio as st inner join class as clon st.cl_id = cl.cl_idinner join hostel as hoon st.ho_id=ho.ho_id--我们再给他加个条件看下--where st.cl_id>2--再给他个排序--order by st.st_id--外连接：--与内连接不同的是，内连接至少要有一个同属于两个表的行符合连接条件时才会返回行，外连接会返回符合任意条件的行--他的表有主从之分，他用主表中的每行去匹配从表中的，与内连不同的是，他不会丢弃没有匹配的行，而是填充null给从结果集--左外连接select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'from studio as st left outer join class as clon st.cl_id=cl.cl_idwhere cl.cl_id>2--多表select tka.te_co_id as '课程安排编号',cl.cl_id as '班级编号',cl.cl_class as '班级名称',co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数',te.te_name as '老师姓名'from te_kc_ap as tka left outer join class as clon tka.cl_id=cl.cl_idleft outer joincourse as coon tka.co_id=co.co_idleft outer jointeacher as teon tka.te_id=te.te_id--====================右外连结 =======================select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'from studio as st right outer join class as clon st.cl_id=cl.cl_idwhere cl.cl_id>2--多表select tka.te_co_id as '课程安排编号',cl.cl_id as '班级编号',cl.cl_class as '班级名称',co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数',te.te_name as '老师姓名'from te_kc_ap as tkaright outer join class as clontka.cl_id=cl.cl_idright outer join teacher teontka.te_id=te.te_idright outer join course coontka.co_id=co.co_id--========完全连接==============select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'from studio as st full outer join class as clon st.cl_id=cl.cl_idorder by st.st_id--多表select tka.te_co_id as '课程安排编号',cl.cl_id as '班级编号',cl.cl_class as '班级名称',co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数',te.te_name as '老师姓名'from te_kc_ap as tkafull outer join class as clontka.cl_id=cl.cl_idfull outer join teacher teontka.te_id=te.te_idfull outer join course coontka.co_id=co.co_id--==========交叉连接================--该方式在不带where子句时，返回的是两个表中所有数据行的笛卡尔积（第一个表中的行乘以第二个表中的行）--用学生和班级表做交叉查询select st_name,cl_class from studio cross join classselect st_name,cl_class from studio,classselect st_name,cl_class from studio cross join class--=========自连接===-----------------先临时创建一个表-------------create table zone(id int primary key identity(1,1) not null,z_zone varchar(30),z_id int references zone(id))--大家试下，这里是否可以给个默认值select * from zoneinsert into zone(z_zone) values('北京')insert into zone(z_zone,z_id) values('北京',4)insert into zone(z_zone) values('四川')insert into zone(z_zone,z_id) values('成都',6)insert into zone(z_zone,z_id) values('绵阳',6)insert into zone(z_zone) values('江苏')insert into zone(z_zone,z_id) values('南京',10)insert into zone(z_zone,z_id) values('苏州',10)insert into zone(z_zone,z_id) values('无锡',10)insert into zone(z_zone,z_id) values('常州',10)------------------------------------------------看下自连接的一般用处select a.z_zone,b.z_zone from zone as a inner join zone as b on a.z_id=b.id--扩展应用下select b.z_zone,count(a.z_zone) as '辖区数' from zone as a inner join zone as b on a.z_id=b.id group by b.z_zone--简单说就是自己连接自己，换言之对同一个表进行连接操作select a.st_name,a.st_add,b.st_name,b.st_add from studio as a inner join studio as b on a.st_add=b.st_add--我们发现有人等于自己，那么增加一个条件select a.st_name,a.st_add,b.st_name,b.st_add from studio as a inner join studio as b on a.st_add=b.st_add and a.st_name!=b.st_name--====学 云网-天轰穿-[url]www.ixueyun.com[/url]==子查询============--在一个SQL语句中镶入另一个SQL语句教镶套查询，而被镶入的这个SQL语句就被江湖人称子查询。是处理多表操作的附加方法--子查询也称内部查询，而包含子查询的Select语句被诚为外部查询，子查询自身可以包括一个或者多个子查询，也可以镶套任意数量的子查询--使用in的子查询select * from studio where cl_id in (select cl_id from class where cl_id>2)--使用 not inselect * from studio where cl_id not in (select cl_id from class where cl_id>2)--使用比较运算符的子查询 -- any 表示子查询中任意的值 all 表示子查询中的每个值--使用anyselect * from class where cl_id>any(select cl_id from studio where st_age>30)--使用allselect * from class where cl_id>all(select cl_id from studio where st_age>30)--============一个分页的SQL语句========select top 3 * from studiowhere st_id>all(select top 3 st_id from studio order by st_id)order by st_id--使用 exists ,该关键字引入一个子查询的时候基本上是对数据进行一次是否存在的测试--我们查询那些人所在的班级是编号为 1 的select * from studio where exists(select cl_id from class where studio.cl_id=class.cl_id and class.cl_id=1)--使用 not existsselect * from studio where not exists(select * from class where studio.cl_id=class.cl_id and class.cl_id=1) order by st_id--基于查询生成新的表select st_name into class_3 from studio where cl_id=3--将数据批量插入一个表中insert into class_3 select st_name from studio where cl_id=4-----------------------sql 编程--------------declare @max int;--申明一个变量@maxset @max=1;--为变量@max赋值while @max<10--如果@max小于10就进入循环beginset @max=@max+1;--每次循环就给@max加1print @max;--打印当前@max的值endprint '终于循环完了';注：关于常用的SQL例句 数据库开发所需知识的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
