社区
MySQL
帖子详情
MySql 是不是不支持Minus、连接啊?
hlh2002
2007-08-07 11:07:53
用Minus也出错,用外连接也出错,非得用SQL嵌套吗?速度慢啊!
...全文
945
5
打赏
收藏
MySql 是不是不支持Minus、连接啊?
用Minus也出错,用外连接也出错,非得用SQL嵌套吗?速度慢啊!
复制链接
扫一扫
分享
转发到动态
举报
写回复
配置赞助广告
用AI写文章
5 条
回复
切换为时间正序
请发表友善的回复…
发表回复
打赏红包
WWWWA
2011-08-23
打赏
举报
回复
不支持,用LEFT JOIN、RIGHT JOIN解决
Angus_pei
2011-08-23
打赏
举报
回复
同楼上 求解!
hlh2002
2007-08-07
打赏
举报
回复
各位Mysql大侠们,请赐教!
懒得去死
2007-08-07
打赏
举报
回复
看一下这篇文章:
http://database.e800.com.cn/articles/2007/729/1185653061660437741_1.html
懒得去死
2007-08-07
打赏
举报
回复
你的SQL语句?
SQL语句生成及分析器
该工具的主要特色: 1、
支持
几乎所有类型的数据库, 包括小型(桌面)数据库:Fox DBF、Microsoft Execl、Text、Borland Paradox、Sybase SQL anywhere 中型数据库:Microsoft Access 大型数据库:Microsoft SQL Server、Sybase、Oracle 准备提供对DB2,informix,ingress,
MySQL
的更好
支持
2、数据库的
连接
采取ADO方式,无需安装和卸载 3、
支持
几乎所有的SQL语法: 内
连接
(inner join)和外
连接
(left join/right join/full join) 排序(Order By) 条件(Where) 分组(Group By) 分组条件(Having) 计算字段 SQL查询表 SQL查询子句 丰富的函数 表别名 字段别名 联合(Union,Union All,
Min
us,Intersect) 4、SQL语句反向分析 无论多么复杂的语句,都能分析出来(包括SQL各子句中嵌套的SQL语句) 5、数据库视图定义和重建 6、
支持
将SQL查询语句,替换为插入(Insert into)和更新(Update)语句 7、附属工具内嵌入Delphi IDE(
支持
Delphi 5和Delphi 6) 8、文件拖放(SQL和TXT文件)
SQL语句生成及分析器(中文绿色)
1、
支持
绝大部分数据库,包括 大型数据库Oracle,Sybase(包括SQL AnyWhere),DB2,MS_SQL 中型数据库MS_Access,
MySQL
桌面型数据库Paradox,DBF系列数据库,MS_Execl,Text 其他
支持
SQL 92标准的数据库 2、数据库的
连接
采用ADO
连接
,因此无需安装和卸载 3、
支持
SQL查询语句绝大部分语法 3.1 选择字段 3.1.1 Select [All] [Distinct]; 3.1.2 特殊语法,记录条数限定Top(MS_SQL),Rownum(Oracle), Limit(
MySQL
),Set RowCount(Sybase)) 3.1.3 字段表达式或者计算字段 3.2 选择表 (From) 3.3 条件筛选 (Where) 3.4 分组 (Group By) 3.5 组内条件 (Having) 3.6 排序 (Order By) 3.7 数据表的内
连接
、外
连接
3.7.1 SQL 92 标准:Inner Join、Left Join、Right Join、Full Join, 3.7.2 特殊语法:*=、=*、*=*(MS_SQL,Sybase),(+)(Oracle) 3.8 联合 (Union [All],
Min
us,Intersect) 3.9 字段别名,数据表别名 3.10 SQL子查询表,内嵌SQL子句 4、粘贴字段、系统函数 5、SQL查询语句反向分析, 无论多么复杂的语句,都能分析得出来 包括上面提到的所有SQL语法 6、SQL文件拖放,然后反向分析 7、数据库视图创建和重建(目前仅
支持
Oracle,Sybase,MS_SQL) 8、附加Delphi 5和Delphi 6的控件包,
支持
Delphi 5,6开发环境 9、SQL语句智能换行 10、其他功能 10.1 SQL文件保存、打开 10.2
支持
多个数据库
连接
10.3 SQL语句关键词高亮度显示 10.4 简单SQL查询语句转换为Delete,Update,Insert语句
sql语句生成与分析器.rar
1、
支持
绝大部分数据库,包括 大型数据库Oracle(包括Oracle Lite),Sybase(包括SQL AnyWhere),DB2,MS_SQL 中型数据库MS_Access,
MySQL
桌面型数据库Paradox,DBF系列数据库,MS_Execl,Text 其他
支持
SQL 92标准的数据库 2、数据库的
连接
采用ADO
连接
,因此无需安装和卸载 3、
支持
SQL查询语句绝大部分语法 3.1 选择字段 3.1.1 Select [All] [Distinct]; 3.1.2 特殊语法,记录条数限定Top(MS_SQL),Rownum(Oracle), Limit(
MySQL
),Set RowCount(Sybase)) 3.1.3 字段表达式或者计算字段 3.2 选择表 (From) 3.3 条件筛选 (Where) 3.4 分组 (Group By) 3.5 组内条件 (Having) 3.6 排序 (Order By) 3.7 数据表的内
连接
、外
连接
3.7.1 SQL 92 标准:Inner Join、Left Join、Right Join、Full Join, 3.7.2 特殊语法:*=、=*、*=*(MS_SQL,Sybase),(+)(Oracle) 3.8 联合 (Union [All],
Min
us,Intersect) 3.9 字段别名,数据表别名 3.10 SQL子查询表,内嵌SQL子句 4、粘贴字段、系统函数 5、SQL查询语句反向分析, 无论多么复杂的语句,都能分析得出来 包括上面提到的所有SQL语法 6、SQL文件拖放,然后反向分析 7、数据库视图创建和重建(目前仅
支持
Oracle,Sybase,MS_SQL) 8、附加Delphi 5和Delphi 6的控件包,
支持
Delphi 5,6开发环境 9、SQL语句智能换行 10、多语言版本 11、其他功能 11.1 SQL文件保存、打开 11.2
支持
多个数据库
连接
11.3 SQL语句关键词高亮度显示 11.4 简单SQL查询语句转换为Delete,Update,Insert语句 11.5 复制为字符串(
支持
对Java、C#、Delphi、VB、PowerBuilder开发语言的
支持
) 11.6 灵活的拖放功能 11.7 在线版本更新 11.8 查询结果输出为SQL脚本文件及Excel文件
SQL培训第一期
1 SQL基础 1.1 基本概念 结构化查询语言(Structured Query Language)简称SQL,是一种关系数据库查询语言,用于存取数据以及查询、更新和管理关系数据库系统。 1.2 语句结构 1.2.1 数据查询语言(DQL) 对数据库进行的信息查询,select。 1.2.2 数据操作语言(DML) 用于操作关系型数据库对象内部的数据,insert、update、delete。 1.2.3 数据定义语言(DDL) 用来建立及定义数据表、字段以及索引等数据库结构,create、alter、drop 。 1.2.4 数据控制语言(DCL) 用于控制对数据库里数据的访问,通常用于创建与用户访问相关的对象以及控制用户的权限,grant、revoke(撤销)。 1.2.5 事务控制命令(TPL) 用于管理数据库事务,commit、rollback、savepoint(在一组事务里创建标记点以用于回退)。 1.3 表的构成 1.3.1 字段 字段是表里的一列,用于保持每条记录的特定信息 1.3.2 记录 记录,也被成为一行数据,是表里的每一行 1.4 完整性的约束条件 1.4.1 实体完整性 关系模型的实体完整性在create table中用primary key约束实现,primary key约束用于定义主键,它保证主键的唯一性和非空性。 1.4.2 参照完整性 关系模型的参照完整性可以通过在create table中用foreign key (<外键>) references <被参照表名> (<与外键对应的主键名>)进行约束定义。 1.4.3 用户定义完整心 在create table语句中可以根据应用要求,定义属性以及元组上的约束。 常见的用户定义的完整性约束有: not null或null约束。 unique约束:唯一性约束。 default约束:默认值约束。 check约束:检查约束,check约束通过约束条件表达式设置列值应该满足的条件。 1.5 范式 1.5.1 第一范式 1.5.1.1 规范 无重复的列,确保每列保持原子性,即数据库表中的所有字段值都是不可分解的原子值。 1.5.1.2 举例 姓名 年龄 联系电话 地址 省 市 详细地址 1.5.2 第二范式 1.5.2.1 规范 属性完全依赖于主键,确保表中每列都与主键相关。 1.5.2.2 举例 订单表 订单Id 商品Id 总金额 商品名称 001 1 10 苹果 001 2 10 橘子 联合主键订单Id、商品Id => 商品表 商品Id 商品名称 单价 订单表 订单Id 总金额 1.5.3 第三范式 1.5.3.1 规范 属性不依赖于其它非主属性,确保数据表中的每一列数据都和主键直接相关,而不能间接相关,即要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 1.5.3.2 举例 党员表 党员Id 党员姓名 组织Code 符合3NF 党员表 党员Id 党员姓名 组织名称 不符合3NF 组织表 组织Code 组织名称 1.6 外
连接
1.6.1 准备 create table student_A( uuid varchar2(32), name varchar2(100)); create table student_B( uuid varchar2(32), name varchar2(100)); insert into student_A values('1','小黄'); insert into student_A values('2','小黑'); insert into student_A values('3','小红'); insert into student_B values('1','大黄'); insert into student_B values('2','大黑'); insert into student_B values('4','大红'); insert into student_B values('4','大紫'); 1.6.2 左
连接
(left join) 1.6.2.1 说明 查询指定的左表的所有行,而不仅仅是联接列所匹配的行;如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。 1.6.2.2 语法 select A.*,B.* from student_A A left join student_B B on A.Uuid = B.Uuid; 1.6.2.3 结果 1.6.2.4 (+)表示 select A.*,B.* from student_A A,student_B B where A.Uuid = B.Uuid(+) 1.6.3 右
连接
(right join) 1.6.3.1 说明 查询指定的右表的所有行,而不仅仅是联接列所匹配的行;如果右表的某行在左表中没有匹配行,则在相关联的结果集行中左表的所有选择列表列均为空值。 1.6.3.2 语法 select A.*,B.* from student_A A right join student_B B on A.Uuid = B.Uuid; 1.6.3.3 结果 1.6.3.4 (+)表示 select A.*,B.* from student_A A,student_B B where A.Uuid(+) = B.Uuid 1.6.4 全外
连接
(full outer join) 1.6.4.1 说明 完整外部联接返回左表和右表中的所有行;当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值;如果表之间有匹配行,则整个结果集行包含基表的数据值。 1.6.4.2 语法 select A.*, B.* from student_A A full outer join student_B B on A.Uuid = B.Uuid; 1.6.4.3 结果 1.6.4.4 全外
连接
不
支持
(+)写法 1.6.5 (+) + 表示补充,即哪个表有加号,这个表就是匹配表。 1.7 运算符 1.7.1 比较 =、>,<,>=,<=,!=,<>, 1.7.2 确定范围 between and 、not between and 1.7.3 确定集合 in、not in 1.7.4 字符匹配 like(“%”匹配任何长度,“_”匹配一个字符) 1.7.5 转义字符 1.7.5.1 常规转义字符 “\”转义字符,“\%”则表示单纯的字符“%” 1.7.5.2 escape escape关键字经常用于使某些特殊字符,如通配符:'%','_'转义为它们原来的字符的意义,被定义的转义字符通常使用'\',但是也可以使用其他的符号。 select * from Student_a where name like '%$%%' escape '$'; 1.7.6 空值 is null、is not null 1.7.7 集合查询 union(并)、intersect(交)、
min
us(差) 1.7.8 多重条件 and、or、not 1.7.9 对查询结果分组
1.7.10 分组筛选条件 [having <条件表达式>] 1.7.11 字符串拼接 select 'A' || 'B' from dual; // || 拼接 1.8 函数 1.8.1 聚集函数 count、sum、avg、max、
min
1.8.2 case when 1.8.2.1 语法 select t.uuid, t.score, case when t.score > 90 then '优秀' when t.score > 60 then '及格' else '不及格' end from exam_user_exam t 1.8.3 decode 1.8.3.1 语法 select decode(x,1,'x is 1', 2 , 'x is 2','others') from dual 1.8.3.2 说明 当x等于1时,则返回‘x is 1’,当x等于2时,则返回‘x is 2’,否则,返回‘others’。 1.8.4 nulls first(nulls last)排序 1.8.4.1 语法 select * from dy_info t order by t.degree nulls first 1.8.4.2 说明 控制null显示行位置 1.8.5 Nvl 1.8.5.1 语法 select nvl(t.sap,'空') from dy_info t; 1.8.5.2 说明 如果sap号为空,则返回‘空’,否则返回sap号。 1.8.6 递归查询 1.8.6.1 语法 select t.* from g_organ t start with t.organcode = '080' connect by prior t.parentcode = t.organcode; //递归查询父节点 select t.* from g_organ t start with t.organcode = '080' connect by t.parentcode = prior t.organcode; //递归查询子节点 1.8.7 union 和 union all 1.8.7.1 语法 select * from dual union all select * from dual 1.8.7.2 说明 union 会对查询数据进行去重并排序,union all只是简单的将两个结果合并。 1.8.8 wm_concat 1.8.8.1 语法 select wm_concat(t.role_name) from g_role t where t.role_name like '%书记%' 1.8.8.2 说明 拼接字符串,结果为:‘党工委副书记,总支副书记,党支部书记,党委书记,党委副书记,党总支书记,党工委书记,党总支副书记,党支部副书记’ 1.8.9 相似度 1.8.9.1 语法 select utl_match.edit_distance_similarity('aaaaa','bbaaaa') from dual; 1.8.10 去格式 1.8.10.1 oracle正则表达式:去除<>>格式 select REGEXP_REPLACE(title,'<[^>]*>','') title from exam_question 1.8.11 rank() over (partition by …) 1.8.11.1 语法 select organcode,score,ranknum from ( select t.organcode, t.score, rank() over (partition by t.organcode order by t.score desc) ranknum from exam_user_exam t) where ranknum < 4 1.8.11.2 说明 获取每个组织,考试成绩前三名。 1.9 存储过程 1.9.1 定义 存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 1.9.2 准备 create table t_user ( username varchar2(20), password varchar2(20) ); create table t_user_temp ( username varchar2(20), password varchar2(20) ); insert into t_user(username,password) values('小王','1111'); insert into t_user(username,password) values('小李','1111'); 1.9.3 创建存储过程 create or replace procedure adduser as begin insert into t_user_temp(username,password) select username,password from t_user t where t.username = '小李'; end adduser; 1.9.4 执行 begin adduser; end; 1.9.5 验证 select * from t_user; select * from t_user_temp 2 SQL深入 2.1 常用 2.1.1 表空间 2.1.1.1 创建表空间 create tablespace TS_DJY datafile 'd:/software/oracle/tablespace/ts_djy.dat' size 1024M autoextend on next 100M maxsize 2048M; 2.1.1.2 指定用户表空间 alter user cssdj default tablespace TS_DJY; 2.1.1.3 指定表的表空间 create table t_student( uuid varchar2(32) )tablespace TS_DJY; 2.1.2 自增sequence 2.1.2.1 创建 create sequence seq_student_uuid
min
value 1 maxvalue 999999999999999 start with 1 increment by 1 nocache; 2.1.2.2 使用 select seq_student_uuid.nextval from dual; //获取下一个 select seq_student_uuid.currval from dual; //获取当前 2.1.3 批量插入 2.1.3.1 查询结果批量插入 insert into tablea(cola,colb) select cola,colb from tableb; 2.1.3.2 创建相同的表结构并插入数据(备份表数据) create tab_new as select * from tab_old; 2.1.3.3 创建相同的表结构不插入数据 create tab_new as select * from tab_old where rownum=0; 2.1.4 伪列伪表 2.1.4.1 伪列 伪列不是表的真实列,但是你可以象使用真实列一样使用伪列,常用伪列:rowid行的绝对物理编号,每一行是唯一的。rownum序号,返回查询结果的每行序号。系统时间sysdate,返回系统当前时间。 2.1.4.2 伪表 select * from dual; 2.1.5 系统表 2.1.5.1 user_tables 当前用户表信息 2.1.5.2 user_tab_columns 当前用户表所有列信息,搜索列所在的表: select * from user_tab_columns t where t.column_name like '%ORGANCODE%'; 2.1.5.3 user_tablespaces 当前用户表空间 2.1.5.4 dba_users 数据库所有用户 2.1.5.5 dba_tables 数据库所有表 2.1.5.6 dba_tablespaces 数据库所有表空间 2.1.6 锁表解锁 2.1.6.1 查看锁表信息 select l.OBJECT_ID,s.SID,s.SERIAL#,s.USERNAME, s.MACHINE from V$locked_Object l,V$session s where l.SESSION_ID = s.SID; 2.1.6.2 解锁 alter system kill session 'sid,serial#'; 2.1.7 备份与恢复 2.1.7.1 导出 导出用户: exp cssdj/cssdj@zr owner=(cssdj,cssdj_zsy) file=d:/cssdj.dmp log=d:/cssdj.log 导出用户表: exp cssdj/cssdj@zr tables=(g_dict,g_dict_item) file=d:/cssdj.dmp log=d:/cssdl.log 2.1.7.2 导入 imp cssdj/cssdj@zr fromuser=cssdj_zsy touser=cssdj file=d:/cssdj.cmp log=d:/cssdj.log 2.1.7.3 数据泵 2.1.8 Dblink 2.1.8.1 创建 create database link orcllink connect to cssdj identified by cssdj using '(DESCRIPTION = (ADDRESS_LIST= (ADDRESS=(PROTOCOL = TCP)(HOST = 219.239.110.65)(PORT = 1521))) (CONNECT_DATA=(SID = orcl)))'; 2.1.8.2 删除 drop database link orcllink; 2.1.8.3 使用 select * from dy_info@orcllink; 2.2 Oracle与
Mysql
差异 2.2.1 Group by 2.2.1.1 Oracle select后面出现的列,如果没有使用集合函数,必须出现在group by 中。 select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select sno,sname,sum(grade) from student group by sno; //非法写法 select sno,
min
(sname),sum(grade) from student group by sno; //建议用这种写法,效率高些 2.2.1.2
Mysql
select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select sno,sname,sum(grade) from student group by sno; //合法写法 2.2.2 分页 2.2.2.1 Oracle 使用rownum来表明分页位置,而且rownum只能小于某值,不能大于某值,故而rownum和where联用才能完成数据范围的控制 2.2.2.2
Mysql
mysql
的分页可以用limit startNum,pageNum 2.3 了解 2.3.1 事务 2.3.1.1 Rollback start transaction; --开始事务 insert into g_dict values('test','测试','1',''); --执行数据操作语言(DML) select * from g_dict where code = 'test'; --可以查看是否执行正确 rollback; --错误执行rollback操作 commit; --正确执行commit操作 2.3.1.2 Savepoint start transaction; --开始事务 insert into g_dict values('t1','测试','1',''); --执行数据操作语言(DML) savepoint pointA; insert into g_dict values('t2','测试','1',''); --执行数据操作语言(DML) select * from g_dict where code = 'test33'; --可以查看是否执行正确 rollback to savepoint pointA; 2.3.2 利用执行计划评估SQL语句的性能 2.3.2.1 工具 在PL/SQL Developer中写好一段SQL代码后,按F5,PL/SQL Developer会自动打开执行计划窗口,显示该SQL的执行计划。 2.3.2.2 查看总COST,获得资源耗费的总体印象 一般而言,执行计划第一行所对应的COST(即成本耗费)值,反应了运行这段SQL的总体估计成本,单看这个总成本没有实际意义,但可以拿它与相同逻辑不同执行计划的SQL的总体COST进行比较,通常COST低的执行计划要好一些。 2.3.2.3 了解执行计划的执行步骤 按照从左至右,从上至下的方法,了解执行计划的执行步骤; 执行计划按照层次逐步缩进,从左至右看,缩进最多的那一步,最先执行,如果缩进量相同,则按照从上而下的方法判断执行顺序,可粗略认为上面的步骤优先执行。每一个执行步骤都有对应的COST,可从单步COST的高低,以及单步的估计结果集(对应ROWS/基数),来分析表的访问方式,
连接
顺序以及
连接
方式是否合理。 2.3.2.4 分析表的访问方式 表的访问方式主要是两种:全表扫描(TABLE ACCESS FULL)和索引扫描(INDEX SCAN),如果表上存在选择性很好的索引,却走了全表扫描,而且是大表的全表扫描,就说明表的访问方式可能存在问题;若大表上没有合适的索引而走了全表扫描,就需要分析能否建立索引,或者是否能选择更合适的表
连接
方式和
连接
顺序以提高效率。 2.3.2.5 分析表的
连接
方式和
连接
顺序 表的
连接
顺序:就是以哪张表作为驱动表来
连接
其他表的先后访问顺序。 表的
连接
方式:简单来讲,就是两个表获得满足条件的数据时的
连接
过程。主要有三种表
连接
方式,嵌套循环(NESTED LOOPS)、哈希
连接
(HASH JOIN)和排序-合并
连接
(SORT MERGE JOIN)。我们常见得是嵌套循环和哈希
连接
。 嵌套循环:最适用也是最简单的
连接
方式。类似于用两层循环处理两个游标,外层游标称作驱动表,Oracle检索驱动表的数据,一条一条的代入内层游标,查找满足WHERE条件的所有数据,因此内层游标表中可用索引的选择性越好,嵌套循环
连接
的性能就越高。 哈希
连接
:先将驱动表的数据按照条件字段以散列的方式放入内存,然后在内存中匹配满足条件的行。哈希
连接
需要有合适的内存,而且必须在CBO优化模式下,
连接
两表的WHERE条件有等号的情况下才可以使用。哈希
连接
在表的数据量较大,表中没有合适的索引可用时比嵌套循环的效率要高。 2.3.3 优化器 Oracle优化器分为基于规则的优化器(RBO)和基于代价的优化器(CBO)。 2.3.3.1 规则的优化器(RBO) RBO的优化方式,依赖于一套严格的语法规则,只要按照规则写出的语句,不管数据表和索引的内容是否发生变化,不会影响PL/SQL语句的"执行计划"。 2.3.3.2 基于代价的优化器(CBO) CBO计算各种可能"执行计划"的"代价",即cost,从中选用cost最低的方案,作为实际运行方案。各"执行计划"的cost的计算根据,依赖于数据表中数据的统计分布,ORACLE数据库本身对该统计分布是不清楚的,须要分析表和相关的索引,才能搜集到CBO所需的数据。 2.3.4 表分析analysis 2.3.4.1 说明 analyze table,一般可以指定分析表、所有字段、所有索引字段、所有索引,若不指定则全部都分析。 2.3.4.2 表分析 analyze table dy_info compute statistics; 2.3.4.3 删除分析数据 analyze table dy_info delete statistics; 2.3.5 oracle添加强制索引 如果使用的是CBO的话,可能SQL不执行索引,则可以添加强制索引执行索引。 2.3.5.1 语法 /*+index(tablename indexname)*/ 2.3.5.2 举例 select /*+index(t INDEX_SAP)*/* from dy_info t where t.sap = 'T6000890'
2009达内SQL学习笔记
先登陆服务器: telnet 192.168.0.23 公帐号: openlab-open123 tarena-tarena 再进入SQL:sqlplus sd0807/sd0807 帐号:sd0807-密码同样 公帐号:openlab-open123 设置环境变量: ORACLE_SID=oral10g\ --变局部变量 export ORACLE_SID --变全局变量 unset ORACLE_SID --卸载环境变量 ORACLE_HOME=... --安装路径;直接用一句语句也可以,如下 export ORACLE_HOME=/oracledata/.../bin: 一、注意事项: 大小写不敏感,即不区分大小写。提倡关键字大写,便于阅读和调式。 “!”在SQL环境下执行Unix命令。 SQL语句是由简单的英语单词构成;这些英语单词称为关键字/保留字,不做它用。SQL由多个关键字构成。 SQL语句由子句构成,有些子句是必须的,有些是可选的。 在处理SQL语句时,其中所有的空格都被忽略(空格只用来分开单词,连续多个空格当一个用)。 SQL语句可以在一行上写出,建议多行写出,便于阅读和调试。 多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号,但特定的DBMS可能必须在单条SQL语句后加分号。 SQL语句的最后一句要以 “;”号结束 二、写子句顺序 Select column,group_function From table [Where condition] [Group by group_by_expression] [Having group_condition] …… [Order by column]; --最后 三、常用简单语句: clear screen:清屏 edit:编辑刚才的一句。 desc/describe:(列出所有列名称) 用法: DESCRIBE [schema.]object[@db_link] dual:亚表,临时用。如:desc dual;/from dual; rollback:回溯,回溯到上次操作前的状态,把这次事务操作作废,只有一次(DDL和DCL语句会自动提交,不能回溯)。 可以用commit语句提交,这样就回溯不回了。 set pause on\off :设置分屏(设置不分屏) set pause "please put an enter key" 且 set pause on:设置带有提示的分屏 oerr ora 904 :查看错误 set head off :去掉表头 set feed off :去掉表尾 保存在oracle数据库中的所有操作细节: spool oracleday01.txt :开始记录 spool off :开始保存细节 四、SELECT语句:选择操作、投影操作。 select:从一个或多个表中检索一个或多个数据列。包含信息:想选择什么表,从什么地方选择。必须要有From子句。(最常用) 当从多张表里查询的时候,会产生笛卡尔积;可用条件过滤它。 当两个表有相同字段时必须加前缀,列名前需加表名和“.”,如“s_emp.id”。 1、用法:SELECT columns,prod2,prod3<列> FROM Table1,table2<表名> 分号结束 如: select id from s_emp; select last_name,name from s_emp,s_dept where s_emp.dept_id=s_dept.id;--列表每人所在部门 SELECT * FROM Products; --检索所有列。 数据太多时,最好别使用上句,会使DBMS降低检索和应用程序的性能。(*通配符) 2、对数据类型的列可进行运算(如加减乘除)。 3、对列起别名:有直接起别名,加AS起别名,用双引号起别名等三种方法 (单引号,引起字符串;双引号,引起别名。起别名有符号,或者区分大小写时,必须用双引号) 多表查询时,可给表起别名。(给列起别名,列<空格>列别名;给表起别名,表<空格>表别名;)。 如:Select first_name EMPLOYEES, 12*(salary+100) AS MONEY, manager_id "ID1" From s_emp E; 4、字段的拼接,可用双竖线(双竖线只能用于select语句里)。不同的DBMS可能使用不同的操作符;拼接的字段同样可以起别名。 如:Select first_name ||' '|| last_name || ', '|| title "Employees" From s_emp; 排他锁:Select id,salary From s_emp where id=1 For Update; 可以阻止他人并发的修改,直到你解锁。 如果已有锁则自动退出:Select id,salary From s_emp where id=1 For Update NoWait; FOR UPDATE :可以再加 OF 精确到某格。如: ... For Update OF salary ... 注意要解锁。 五、ORDER BY 子句,排序 Order by:按某排序列表(默认升序 asc,由低到高;可加 desc,改成降序由高到低) 检索返回数据的顺序没有特殊意义,为了明确地排序用 SELECT 语句检索出的数据,可使用 ORDER BY 子句。 ORDER BY 子句取一个或多个列的名字。 对空值,按无穷大处理(升序中,空值排最后;降序中排最前)。 1、用法:Select prod_id,prod_price,prod_name From Products Order By prod_price,prod_name; (从左到右执行排序,先排price) ORDER BY子句中使用的列将是为显示所选择的列,但是实际上并不一定要这样,用非检索的列排序数据是完全合法的。 为了按多个列排序,列名之间用逗号分开。 2、
支持
按相对列位置进行排序。 输入 SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3 --(2指price,3指name) 3、升序、降序。默认是升序(asc,从小到大排序),想降序时用desc。 如:SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC; 注意:DESC 关键字只应用到直接位于其前面的列名。如果想在多个列上进行排序,必须对每个列指定DESC关键字。 升序是默认的,可不写,但降序必须写。 六、WHERE子句,选择、过滤 其后只能跟逻辑语句,返回值只有ture或false 如: select last_name,salary from s_emp where salary=1000;--找出工资1000的人 WHERE子句操作符: 1、逻辑比较运算符 = 等于 != 不等于,还有(<> ^= 这两个同样表示不等于) > 大于 >= 大于等于 < 小于 <= 小于等于 2、SQL 比较运算符 between…and… :在两者之间。(BETWEEN 小值 AND 大值) 如:select last_name,salary from s_emp where salary between 1000 and 1500; --工资1000到1500的人,包括1000和1500。 in(列表):在列表里面的。 如:select last_name,dept_id from s_emp where dept_id in(41,42);第41、42部门的人 like : 包含某内容的。模糊查询 可以利用通配符创建比较特定数据的搜索模式,通配符只能用于文本,非文本数据类型不能使用通配符。 通配符在搜索模式中任意位置使用,并且可以使用多个通配符。 通配符%表示任何字符出现任意次数;还能代表搜索模式中给定位置的0个或多个字符。下划线匹配单个任意字符。 如:select table_name from user_tables where table_name like 'S\_%' escape'\'; ' 找出“S_“开头的,由于下划线有任意字符的含义,故需另外定义转移符。 但习惯用“\”,为方便其他程序员阅读和检测,一般不改用其他的。 like 'M%':M开头的 like '_a%':第二个字符是a的 like '%a%'所有含a的 (“_”表示一个任意字符;“%”表示任意多个任意字符。) 单引号里面的内容,大小写敏感。单引号用来限定字符串, 如果将值与串类型的列进行比较,则需要限定引号;用来与数值列进行比较时,不用引号。 is null:是空。(NULL表示不包含值。与空格、0是不同的。) 如:SELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL; 七、高级检索(逻辑运算符): 通常我们需要根据多个条件检索数据。可以使用AND或OR、NOT等
连接
相关的条件 计算次序可以通过圆括号()来明确地分组。不要过分依赖默认计算次序,使用圆括号()没有坏处,它能消除二义性。 and:条件与 如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 AND vend_id=‘DELL’ or:条件或 (注: and 的优先级比 or 更高,改变优先级可用括号) 如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 OR vend_id=‘DELL’ not:条件非。否定它之后所跟的任何条件 否定的SQL 比较运算符: NOT BETWEEN; NOT IN; NOT LIKE; IS NOT NULL: (注意,按英语习惯用 is not,而不是 not is) NOT 与 IN 在一起使用时,NOT 是找出与条件列表不匹配的行。 IN 列表里有 NULL 时不处理,不影响结果;用 NOT IN 时,有 NULL 则出错,必须排除空值再运算。 in :选择列表的条件 使用IN操作符的优点: 在长的选项清单时,语法直观; 计算的次序容易管理; 比 OR 操作符清单执行更快;最大优点是可以包含其他 SELECT 语句,使用能够动态地建立 WHERE 子句。 如 SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id IN(‘DELL’,’RBER’,’TTSR’); 八、单行函数: 函数一般在数据上执行,它给数据的转换和处理提供了方便。不同的DBMS提供的函数不同。 函数可能会带来系统的不可移植性(可移植性:所编写的代码可以在多个系统上运行)。 加入注释是一个使用函数的好习惯。 大多数SQL实现
支持
以下类型的函数: 文本处理, 算术运算, 日期和时间, 数值处理。 Null:空值 空值当成无穷大处理,所有空值参与的运算皆为空。 空值与空值并不相等,因为空值不能直接运算。 如:prod_price="" 这种写法是错的(不要受到corejava的影响) prod_price=NULL 这种写法是错的(不要受到corejava的影响) prod_price IS NULL 这种写法才是对的 NVL:处理空值,把空值转化为指定值。可转化为日期、字符、数值等三种(注意:转化时,两参数必须要同类型) 如:NVL(date, '01-JAN-95') NVL(title,'NO Title Yet') NVL(salary,0) 错误写法: Select last_name,title,salary*commission_pct/100 COMM From s_emp;--没提成的人没法显示工资 正确写法: Select last_name,title,salary*NVL(commission_pct,0)/100 COMM From s_emp;--把提成是空值的转化为0 DISTINCT:过滤重复 把重复的行过滤掉;多个字段组合时,只排除组合重复的。 DISTINCT必须使用列名,不能使用计算或者表达式。 所有的聚合函数都可以使用。如果指定列名,则DISTINCT只能用于COUNT(列名),DISTINCT不能用于COUNT(*)。 如:Select Distinct name From s_dept; Select Distinct dept_id,title From s_emp; 文本处理: TRIM()/LTRIM()/RTIRM():去空格。只能去掉头和尾的空格,中间的不理。 trim(' heo Are fdou ') --> heo Are fdou 输入:select trim(' heo Are fdou ') from dual; -->:heo Are fdou LOWER:转小写 lower('SQL Course') --> sql course UPPER:转大写 upper(' SQL Course') --->SQL COURSE INITCAP:首字母转大写,其余转小写 initcap(SQL Course') '--> Sql Course CONCAT:合成。双竖线只能在select语句里面用,这个可用于任何语句。 Concat('Good','String') --> GoodString SUBSTR:截取。 Substr('String', 1 ,3) --> Str 第一个数字“1”,表示从第几个开始截取;若要从倒数第几个开始,用负数,如“-2”表示倒数第2个。 上式中第2个数字“3”表示截取多少个。 LENGTH:统计长度。 Length('String') --> 6 NVL:转换空值 日期和时间处理: Oracle日期格式:DD-MMM-YYYY (D代表日期date,M代表月month,Y代表年year) 如:SELECT prod_name (DAY表示完整的星期几,DY显示星期的前三个字母) FROM Products WHERE prod_time BETWEEN to_date(’01-JAN-2008’) AND to_date(’31-DEC-2008’); 日期可以进行加减,默认单位是1天。日期与日期可以相减,得出天数;日期与日期但不能相加。 sysdate -> 系统的当天 Months_Between('01-Sep-95','11-Jan-94') --> 19.774194 相差多少个月,Between里面也可以填函数。 Add_months('11-Jan-94',6) --> 11-Jul-94 增加多少个月 Next_day('01-Sep-95','Friday') --> '08-Sep-95' 下一个星期五。其中的'Friday'可用6替代,因为星期日=1 Last_day('01-Sep-95') --> '30-Sep-95' 这个月的最后一天 数值处理:可以运用于代数,三角,几何 ROUND:四舍五入 Round(45.925,2) -> 45.93 Round(45.925,0) -> 46 Round(45.925,-1) -> 50 逗号前一个数是要处理的数据源,后一个参数表示保留多少位小数。 后一参数是负数时,表示舍去小数点前的几位,例3是舍去个位及其后的。不写后一参数时,默认不保留小数。 TRUNC:舍去末位。直接舍去,不会进位。 Trung(45.925,2) -> 45.92 Trung(45.925,2) -> 45.92 Trung(45.925,2) -> 45.92 日期的舍取: 常用的数值处理函数有: ABS() 绝对值 ABS(-5741.5854) --> 5741.5854 PI() 圆周率 注意:oracle中不
支持
PI()函数;
MYSql
支持
PI()函数。 SIN() 正统值 Oracle还
支持
COS()、ASIN()、ACOS()函数 SQRT() 平方根 转化: TO_CHAR(number,'fmt'):把数值转换成字符串 显示数字的命令 9:正常显示数字; 0:显示包括0的数值形式,空位强制补0; $:以美元符号显示货币; L:按当前环境显示相关的货币符号; . 和,:在固定位置出现“.”点 和“,”逗号;不够位时,四舍五入。 例题:SQL> select 'Order'||To_char(id)|| 2 'was filled for a total of' 3 ||To_char(total,'fm$9,999,999') 4 from s_ord 5 where ship_date ='21-SEP-92'; TO_NUMBER(char):把字符转换成数字 九、链接 内链接:严格匹配两表的记录。 外链接分左链接和右链接: 会使用一方表中的所有记录去和另一格表中的记录按条件匹配,空值也会匹配,这个表中的所有记录都会显示, 数据库会模拟出记录去和那些不匹配的记录匹配。 左链接 加号在右面 如:有 TABLE1 TABLE2 1的一条记录在2里面没有匹配上,那么1里面的记录保留 2的一条记录在1里面没有匹配上 ,那么2丢弃 右链接正好相反 --例题:哪些人是领导。 select distinct b.id,b.last_name manager from s_emp a,s_emp b where a.manager_id=b.id(+); 左右顺序有区别,这是另外新建一个表,要显示的是第二个表格的内容。 +放在没有匹配行的表一侧,令表格能完整显示出来。 标准写法:内
连接
用INNER,左
连接
用LEFT,右
连接
用RIGHT。 select distinct b.id,b.last_name manager from s_emp a LEFT join s_emp b ON a.manager_id=b.id; 十、组函数: 分组允许将数据分为多个逻辑组,以便能对每个组进行聚集计算。 Group:分组 Group by:分组。(默认按升序对所分的组排序;想要降序要用 order by)可以包括任意数目的列。 如果嵌入了分组,数据将在最后规定的分组上进行汇总。 GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式,但不能是聚集函数。 *如果在SELECT 中使用表达式,则必须在GROUP BY子句中指定相同的表达式,不能使用别名。 除聚合计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出。 如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL,它们将分为一组。 Having:过滤。分组之后,不能再用where,要用having 选择过滤。Having不能单独存在,必须跟在group by后面。 WHERE在数据分组前进行过滤,HAVING在数据分组后过滤。 可以在SQL中同时使用 WHERE和HAVING,先执行WHERE,再执行HAVING。 聚合函数: AVG:平均值 (忽略值为NULL的行,但不能用 AVG(*)) COUNT:计数 (Count(列)不计算空值;但 COUNT(*)表示统计表中所有行数,包含空值) MAX:最大值 (忽略列值为 NULL 的行。但有些DBMS还允许返回文本列中的最大值, 在作用于文本数据时,如果数据按照相应的列排序,则 MAX()返回最后一行。)
MIN
:最小值 (忽略值为 NULL 的行。不能用
MIN
(*)。一般是找出数值或者日期值的最小值。 但有些DBMS还允许返回文本列中的最小值,这时返回文本最前一行) SUM:求和 (忽略值为 NULL 的值。SUM 不能作用于字符串类型,而 MAX(),
MIN
()函数能。也不能 SUM(*)) 子查询:查询语句的嵌套 可以用于任意select 语句里面,但子查询不能出现 order by。 子查询总是从内向外处理。作为子查询的SELECT 语句只能查询单个列,企图检索多个列,将会错误。 如:找出工资最低的人select
min
(last_name),
min
(salary) from s_emp; 或者用子查询select last_name,salary from s_emp where salary=(select
min
(salary) from s_emp); E-R图:属性: E(Entity) -R(Relationship) * (Mandatory marked 强制的) 强制的非空属性 o (Optional marked 可选的) 可选属性(可以有值也可以没有) #* (Primary marked ) 表示此属性唯一且非空 约束:针对表中的字段进行定义的。 PK:primary key (主键约束,PK=UK+NN)保证实体的完整性,保证记录的唯一 主键约束,唯一且非空,并且每一个表中只能有一个主键,有两个字段联合作为主键, 只有两个字段放在一起唯一标识记录,叫做联合主键(Composite Primary Key)。 FK:foreign key (外建约束)保证引用的完整性,外键约束,外键的取值是受另外一张表中的主键或唯一值的约束,不能够取其他值, 只能够引用主键会唯一键的值,被引用的表,叫做parent table(父表),引用方的表叫做child table(子表); child table(子表),要想创建子表,就要先创建父表,后创建子表,记录的插入也是如此,先父表后子表, 删除记录,要先删除子表记录,后删除父表记录, 要修改记录,如果要修改父表的记录要保证没有被子表引用。要删表时,要先删子表,后删除父表。 U:unique key(唯一键 UK),值为唯一,不能重复。 在有唯一性约束的列,可以有多个空值,因为空值不相等。 NN:NOT NULL,不能为空。 index(索引)是数据库特有的一类对象,实际应用中一定要考虑索引,view(示图) 数量关系: 一对一关系 多对一关系 一对多关系 多对多关系 范式: 好处:降低数据冗余;减少完整性问题;标识实体,关系和表 第一范式(First normal form:1Nf),每一个属性说一件事情。所有的属性都必须是单值,也就是属性只表示单一的意义。 (记录可以重复,会有大量冗余,没有任何限制) 第二范式(2N范式),最少有一个属性要求唯一且非空PK,其他跟他有关联(记录不可重复,但是数据可能会出现冗余)。 第三范式(3N范式),非主属性只能依赖于主属性,不能依赖于其他非主属性。(解决数据冗余问题,不能存在推理能得出的数据) 一般情况会做到第三范式。 创建表: Create Table 表名 (字段名1 类型(数据长度)(default ...) 约束条件, 字段名2 类型(数据长度) 约束条件 ); 建表的名称: 必须字母开头;最多30字符;只能使用“A~Z、a~z、0~9、_、$、#”; 同一目录下不能有同名的表;表名不能跟关键字、特殊含意字符同样。 如:create table number_1 (n1 number(2,4), n2 number(3,-1), n3 number); create table t_sd0808(id number(12) primary key,name varchar(30) not null);
MySQL
的: create table student (oid int primary key, ACTNO varchar(20) not null unique, BALANCE double); --
MySQL
的number类型分小类了,Oracle只有number,且
MySQL
的数值型不用定大小 Oracle的: create table t_ad (oid number(15) primary key, ACTNO varchar(20) not null unique,BALANCE number(20)); INSERT:插入(或添加)行到数据库表中的关键字。 插入方式有以下几种:插入完整的行;插入行的一部分;插入某些查询的结果。 对于INSERT操作,可能需要客户机/服务器的DBMS中的特定的安全权限。 插入行(方式一) INSERT INTO products VALUES(2008,’TV’,222.22,’US’); 依赖于表中定义的顺序,不提倡使用。有空值时需要自己补上。 插入行(方式二) INSERT INTO products(id,name,price,vend_name) VALUES(2008,’TV’,222.22,’US’); 依赖于逻辑顺序,会自动补上空值,提倡使用。 插入检索出的数据:可以插入多条行到数据库表中 INSERT INTO products(*,*,*,*) SELECT *,*,*,* FROM products_copy; 如果这个表为空,则没有行被插入,不会产生错误,因为操作是合法的。 可以使用WHERE加以行过滤。 复制表: 将一个表的内容复制到一个全新的表(在运行中创建,开始可以不存在) CREATE TABLE 新表名 AS SELECT * FROM 表名; INSERT INTO 与 CREATE TABLE AS SELECT 不同,前者是导入数据,而后者是导入表。 任何SELECT选项和子句都可以使用,包括WHERE和GROUP BY。 可利用联接从多个表插入数据。不管从多少个表中检索数据,数据都只能插入到单个表中。 更新数据 UPDATE 语句 需要提供以下信息:要更新的表;列名和新值;确定要更新的哪些行的过滤条件。 UPDATE 表名 SET vend_name = ‘HP’, prod_name = ‘NEWCOMPUTER’ WHERE vend_name = ‘IBM’; --UPDATE 语句中可以使用子查询,使得能用SELECT语句检索出的数据更新列数据。也可以将一个列值更新为 NULL。 删除数据 DELETE 语句 DELETE FROM products WHERE prod_name = ‘COMPUTER’; 全行删除,不要省略WHERE,注意安全。 DELETE不需要列名或通配符。删除整行而不是删除列。DELETE是删除表的内容而不是删除表。 如果想从表中删除所有内容,可以使用TRUNCATE TABLE语句(清空表格),它更快。 数字字典表: Sequence:排列。存储物理地址 Index:索引。依附于表,为提高检索速度。 View:视图。看到表的一部分数据。 限制数据访问。简化查询。数据独立性。本质上是一个sql查询语句。 Create[or Relace][Force|noForce] View 视图名 [(alias[,alias]…)] 别名列表 As subquery [With Check Option [Constraint ……]] [With Read Only] 注意:有些DBMS不允许分组或排序视图,不能有 Order by 语句。可以有 Select 语句。 删除视图: DROP VIEW 视图名 Rownum:纬列。内存里排序的前N个。 在where语句中,可以用=1,和<=N 或
N。 因为这是内存读取,没有1就丢弃再新建1。只能从1开始。需要从中间开始时,需二重子rownum语句需取别名。 经典应用: Top-n Analysis (求前N名或最后N名) Select [查询列表], Rownum From (Select [查询列表(要对应)] From 表 Order by Top-N_字段) Where Rownum <= N 分页显示: --取工资第5~10名的员工(二重子rownum语句,取别名) select rn,id,last_name,salary From ( select id,last_name,salary,Rownum rn From (Select id,last_name,salary from s_emp order by salary desc) where rownum <= 10) where rn between 5 and 10; Union:合并表 Select … Union Select… 把两个Select语句的表合并。 要求两表的字段数目和类型按顺序对应。合并后的表,自动过滤重复的行。 Intersect:交。 同上例,把两个Select表相交。
Min
us:减。 把相交的内容减去。 not exists 除运算。 添加字段(列): Alter Table 表名 Add (column dataype [Default expr][Not Null] [,column datatype]…); 添加有非空限制的字段时,要加Default语句 字段名字不可以直接改名,需要添加新字段,再复制旧字段后删除旧字段。 添加约束: Alter Table 表名 Add [CONSTRAINT constraint] type (column); 添加非空约束时,要用Modify语句。 查看约束名时,可以违反约束再看出错提示;或者查看约束字典desc user_constraints 减少字段: Alter Table 表名 Drop (column [,column]…); 删除约束: Alter Table 表名 Drop CONSTRAINT column; 或: Alter Table 表名 Drop Primary Key Cascade; 暂时关闭约束,并非删除: Alter Table 表名 Disable CONSTRAINT column Cascade; 打开刚才关闭的约束: Alter Table 表名 Enable CONSTRAINTcolumn; 修改字段: Alter Table 表名 Modify (column dataype [Default expr][Not Null] [,column datatype]…); 修改字段的类型、大小、约束、非空限制、空值转换。 删除表: 会删除表的所有数据,所有索引也会删除,约束条件也删除,不可以roll back恢复。 Drop Table 表名 [Cascade Constraints]; 加 [Cascade Constraints] 把子表的约束条件也删除;但只加 [Cascade]会把子表也删除。 改表名: Rename 原表名 To 新表名; 清空表格: TRUNCATE TABLE 表名; 相比Delete,Truncate Table清空很快,但不可恢复。清空后释放内存。 Delete 删除后可以roll back。清空后不释放内存。
MySQL
56,687
社区成员
56,710
社区内容
发帖
与我相关
我的任务
MySQL
MySQL相关内容讨论专区
复制链接
扫一扫
分享
社区描述
MySQL相关内容讨论专区
社区管理员
加入社区
获取链接或二维码
近7日
近30日
至今
加载中
查看更多榜单
社区公告
暂无公告
试试用AI创作助手写篇文章吧
+ 用AI写文章