常用SQL語句收藏 <一>，sql語句收藏

常用SQL語句收藏 <一>，sql語句收藏
MSSQL 查詢

select * from class;查詢表class中的所有列.

select * from class; 查詢表class中的所有列.select class,teacher from class;查詢表class中的列class,teacherSelect count(*) as count_all from class;返回表class中的總行數給結果集.select sum(studentnumber) as all_student_number from class :返回class表中studentnumber的總數給結果集select avg(studentnumber) as avg_student_number  from class :返回class中studentnumber的平均值給結果集Select max(studentnumber)  as max_studentnumber from class :求某個欄位的最大值,最小值.min是最小值.select * from class where studentnumber=(Select max(studentnumber) from class):求的最大值可以作為條件被引用.Select * from class where studentnumber=50(>50,>=50,<50,<=50,<>50):返回studentnumber=50 (>50,>=50,<50,<=50,不等於50):的記錄Select * from class where studentnumber<>50 and teacher='li‘ 兩個查詢條件用and表示與,or表示或.Select * from class where studentnumber in (44,55) : in表示studentnumber為括弧中所列的可能值.Select * from class where class in (select class from student) :in中的內容可以是另一個查詢語句的結果.Select distinct class from student :查詢欄位的值不重複select * from class order by studentnumber (asc,desc) :對查詢結果排序,可以按升序,也可以按降序.

續一

select class,count(*) from student group by class :查詢結果根據group by 分組.select class,count(*) from student group by class having count(*)=5 :對分組的結果再用條件過濾select * from student where id<2UNION (ALL)select * from student where age>19 :UNION:將兩個查詢語句的查詢結果放在一起,如果有重複的行,就刪除,如果是UNION ALL:則重複的行不刪除.模糊比對查詢: select * from student where name like '%ang%'整型,日期類型的欄位可以指定範圍.用betweenselect * from student where born between '1980-05-04' and '2983-10-18'select * ,12 from student 返回結果中增加一列,且值均為12.select RTRIM(class)+RTRIM(teacher) AS name1,studentnumber from class :將兩個欄位先刪除尾部的空格,再串連起來返回. 其中:串連起來的欄位名稱返回時為name1Select class.*,student.* from class,student where class.class=student.class :兩個表的內容聯合起來查詢,欄位也可以用JOIN子句實現:select * from class JOIN student on class.class=student.classJOIN又分為內串連,外串連,左外串連,右外串連等,具體請查看相關的資料庫的手冊.

增刪改操作

插入:指定欄位名稱及值的插入Insert into class (class,studentnumber,teacher) values('gaoer',55,'abc');不指定欄位名稱的插入Insert into class values('chuyi','abc',55);一次插入多條記錄:只能執行多條insert語句.從另一個表中讀出資料插入當前的表先建立一個新表:select * into class_bak from class where 1=2insert into class_bak select * from class where class='gaoer‘修改:Update class set class='gaoerer' where class='gaoer‘刪除:Delete from class where class='gaoerer'

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。