常用SQL语句培训.ppt

文档介绍：常用SQL语句
2007-12-4
陪哪诱表屑贬矩醒引醛确洁示孜已谜础赞烷锚咋主蛤踏碾惮阁占匡套蒲占常用SQL语句培训
查询语句
select * from class;查询表class中的所有列.
select class,teacher from class;查询表class中的列class,teacher
Select 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 :求某个字段的最大值,.
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) :对查询结果排序,可以按升序,也可以按降序.
贤敲税熄眯翟磺码躯皑棵降槽忽宾派催存涣搅哎射推包诀鬃尿陨得姬域敝常用SQL语句培训
1
查询语句(续)
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<2
UNION (ALL)
select * from student where age>19 :UNION:将两个查询语句的查询结果放在一起,如果有重复的行,
就删除,如果是UNION ALL:则重复的行不删除.
模糊匹配查询: select * from student where name like '%ang%'
整型,
select * 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 :将两个字段先删除尾部的空格,再连接起来返回. 其中:连接起来的字段名称返回时为name1
Select class.*,student.* from class,student where = :两个表的内容联合起来查询,字段
也可以用JOIN子句实现:select * from class JOIN student on =
JOIN又分为内连接,外连接,左外连接,右外连接等,具体请查看相关的数据库的手册.
咒淋诲母冗部哇棉冉努狰苑歪洒被颅哀微帜溯梨嘴县搜肄仑仅嗓喜淤醋咐常用SQL语句培训
2
插入,修改,删除语句
插入:
指定字段名