一:SQL语言分类
1) SELECT语句:检索数据库数据,最灵活,最复杂
2) 操纵语言DML:改变数据库数据,insert,delete,update
3) 事物控制TCL:保持数据库的一致性,commit,savepoint,rollback
4) 定义语言DDL:建立,修改,删除数据库对象,会自动提交事物,alter,create,drop
5) 控制语言DCL:执行权限授予和收回操作,会自动提交事物,grant,revoke
二:SQL语句的使用
SQL语句规则
列名和关键字不区分大小写,字符值区分大小写。
select am.message_content from AP_MESSAGE am WHERE am.message_content='neirong' select am.message_content from AP_MESSAGE am WHERE am.message_content='NEIRONG' |
select语句
SELECT message_title 标题, message_content 内容from ap_message |
where语句
SELECT message_title 标题, message_content 内容from ap_message where message_title='通知' |
一:where列名和列值之间可以使用比较运算符
查询一:<> SELECT message_content 内容from ap_message where message_title<>'通知'
查询二:like模糊查询 SELECT message_content 内容from ap_message where message_title LIKE'通%'
查询三:t后面4个_,必须匹配完 SELECT message_title,message_content from ap_message where message_title LIKE't____'
查询四:or SELECT message_title, message_content from ap_message where message_title = 'title' or message_title = '通知'
查询五:and SELECT message_title, message_content from ap_message where message_title = 'title' and message_content = 'neirong'
查询六:not SELECT message_title, message_content from ap_message wherenot message_title = 'title' andnot message_content = 'neirong'
查询七:between…and select menu_name,action from ap_menu where b_enable='1'and sql_id between0and60
查询八:in SELECT message_title, message_content from ap_message where message_title in ('title', '通知') and message_content = 'neirong' |
order by 子句
默认升序排列,按照前面的排序条件优先排序。
查询一: select message_title, message_content, send_begin_time, message_status from ap_message orderby send_begin_time desc,message_status
查询二: select message_title, message_content, send_begin_time, message_status from ap_message where message_title = '通知' and send_begin_time > sysdate - 2 orderby send_begin_time desc, message_status |
distinct 标识
查询一: selectdistinct message_title 主题, count(message_title) 主题数量 from ap_message groupby message_title
|
算术运算符
selectdistinct message_title 主题, count(message_title)*2主题数量 from ap_message groupby message_title |