软件工程-SQL--sql语言基础

1、1 sql语言基础1.1两种sql语言1.1.1 ddl表71部分d d l语句列表s q l命令功能alter procedure ie编译存储过程alter table增加表列、重定义表列、更改存储分配analyze收集数据库对彖的性能统计值并送入基于代价的优化器alter table add constraint在已有的表上增加约束create table 创建表create index创建索引drop index删除索引drop table删除表grant将权限或角色授了用户或jt他角色truncate删除表中所有行revoke从用户或数据库角色冋收权限1.1.2 dml表72部分d

2、m l语句列表s q l命令功能insert增加数据行到表delete从农中删除数据行update更改表中数据select从表或视图中检索数据行commit work把当前事务所做的更改永久化（写入磁盘）rollback作废上次提交以来的所有更改1.2 sql* plus 入门进入 dos 窗口,输入命令 sqlplus polly/gone1.2.1创建语句create table customer（last_name varchar2(30) not null,state_cd varchar(2),sales number)tablespace custspace - tablespac

3、e 了句将表存放到指定的数据库表空间中。storage(initial 25k next 25k minextents 1); - storage 子句用于定义表空间create table state(state_cd varchar(2) not null,state_name varchar2(30)；create table x(col varchar2(30)；create table y(col varchar2(30)；表73部分数据类型数据类型说明char(size)存放立长字符数据，最长2 0 0 0个字符nchar(size)与char型类似，不过其最人长度由数据库所用的字

4、符集(如american english) 决定varchar2(size)存放町变长字符数据，最大长度为4000字符nvarchar(size)类似丁，varchar2varchar 口前等同于charnumber(l,d)存放数值型数据，i代表总位数，d代表小数点后位数(4,2)的最大值是99.99。blob二进制大对象，其最大长度为4gbraw(size)纯(raw)二进制数据，最大长度为2000字节date存放日期，范围从公元前4712年的1 h 1日到公元后9999年的12月31 hlong存放可变长字符数据，最人为2gb(gigabytes)在每条ddl语句的最厉都有一个分号(；)

5、，表示sql语句输入完毕，可以开始执行。1.2.2描述语句describe customer -得到表customer 的相关信息在sql* plus中,只有describe命令可简写为desc。1.2.3插入语句insert into customer values(5teplow,ma, 23445.67)；insert into customer values(,abbev, 'ga； 6969.96)；oracle 数据库响应 1 row created.或者：in sert into st at e( st ate _n ame,state_cd) values(kmassa

6、chusetts1/ma,);1.2.4查询语句使用select语句操作名为user_tables的数据字典视图select * from user table where table name = “customer'1.2.5 where 子句表75常用比较操作符操作符作用样例=相等 select * from state where state_cd= 'ma5；i =不相等 select * from state where state_cd!= *ma*;八=同！ =： select * from state where state_cda = 'ma'

7、;；v > 同！ = select * from state where state_cdv >'ma'；v 小于 select * from customer where sales< 100：> 大于 select ” from customer where sales> 1 00；v = 小于或等于 select * from customer where sales< = 1 000；> =大于或等于 select * from customer where sales> = 1000；in 等于括号内任一成员 selec

8、t * from customer where state_cd in ('ma','nj')；not in不等于括号内任一成员 select * from customer where state_cd not in ('ma','nj')；between a and b人于等于a与小于等于b select * from customer where sales between 1 and 50；not between a and b不大于等于a与小于等于b select * from customer where sales

9、not between 1 and 50；like '%tin%'包括给定字串 select * from customer where last_name like '%tin%'；1.2.6 order by 子句降序：select * from customer order by last_name desc;升序：select * from customer order by last_name;在order by子句中未指尬升序或降序时，oracle按升序排列1.3数值型数据sql语句中，若无法指定具体的数据表用于该语句，就可以使用dual表。表77数

10、值型常用函数惭数返冋值样例显示ceil(n)大于等于数值 n 的最小整数 select ceil(10.6) from dual； 11floor(n)小于等于数值 n 的最大整数 select floor(1 0.6) from dual； 1 0mod(m,n) m 除以 n 的余数，若 n = 0,则返回 m select mod(7,5) from dual； 2power(m,n) m 的 n 次方 select power(3,2) from dual； 9round(n,m)将n四舍五入，保留小数点后m位 select round(14.5678,2) from dual； 14

11、.57sign(n) n= 0 返冋 0;n>0 返冋 1; nvo 返冋select sign(12) from dual； 1sqrt(n) n 的平方根 select sqrt(25) from dual； 5sql* plus的帮助实用工具存放在oracle documentation内。sql> help functions1.4字符型数据表78常用字符函数函数返冋值样例显示initcap(char)把每个字符串的第一个字符换成大写 select initcap(,mr.teplow,) from dual； m r. te p i o w lower(char)整个字符

12、串换成小写 select lower('mr.frank towns on') from dual； mr.terry beaker replace(char,str1 ,str2)字符串中所有 str1 换成str2 select replace('scott',s,'boy') from dual； boycottsoundex(char)字符串的语音农示，常用于名字的模糊查询，m查找发音相似拼写不同的字符 串 select last_name from employee where abbey soundex(last_name)= sou

13、ndex(, smythe')；substr(char,m,n)取岀从m字符开始的n个字符的子串 select substr(,abcdef,2,1) from dual； cdlength(char)求字符串的长度 select length(,anderson') from dual； 8用两竖线表示并置运算符。select ,abc，h，def from dual;,返回文本abcdefo若 last_name 值为 john, select 'dea门 |last_name| | from customer；将返回文本“dear john:”1.5 口期型数据1

14、.5.1 口期型函数表79常用口期型函数函数返回值样例显示sysdate当前日期和时间 select sysdate from dual； 28-feb-02 on february 28,2002last_day本月最后一x select last_day(sysdate) from dual； 31 mar-02 on march 12,2002add_months(d,n)当前日期d后推n个月 select add_months(sysdate,2) from dual； 1 8 m ay-02 on march 18,2002months_between(f,s)日期f和s间相差月数

15、select months_between(sysdate,?12 mar,-02,)from dual； 13 in april 2003 next_day(d,day) d 后第一周指定的 dayi期 select n ext_day(sysdate/m on day*) from dual； 03-jan-02 on december30,20011.5.2特殊格式的口期型数据 7-10常用日期数据格式格式返冋值样例显示 丫或 yy或 yyy 年的最后一位，两位或三位 select to_char(sysdate,yyy,) from dual； 002 表示2002年syear或yea

16、r syear使公元前的年份前加一负号 select to_char(sysdate,syear,) from dual； -1112 表示公元前 1112 年q季度，13月为第一季度select to_char(sysdate,q,) from dual； 2表示第二季度mm 月份数 select to_char(sysdate,mm,) from dual； 12 表示 1 2 月rm 月份的罗马表示 select to_char(sysdate,'rm,) from dual； iv 表示 4 月month 用 9 个字符长度表示的月份名 select to_char(sysda

17、te,month,) from dual； may后跟6个空格表示5月ww 当年第儿周 select to_char(sysdate,ww,) from dual； 24 农示 2002 年 6 al 13 h为第24周w 木月第儿周 select to_char(sysdate,w,) from dual； 2002 年 10 月 1 门为第 1 周ddd当年第几，1刀1 口为001, 2刀1门为032 select to_char(sysdate,ddd,) from dual； 363 2002 年 1 2 月 2 9 口为第 363 天dd 当月第儿夭 select to_char(s

18、ysdate,dd,) from dual； 04 10 月 4 日为第 4 天d 周内第儿天 select to_char(sysdate,'d,) from dual； 5 2002 年 3 刀 14 口为星期一dy 周内第几天缩写 select to_char(sysdate,'dy') from dual； sun 2002 年 3 月 24 日为星期天hh 或 hh12 12 进制小时数 select to_char(sysdate/hh*) from dual； 02 午夜 2 点过 8 分为02hh24 24 小时制 select to_char(sysd

19、ate；hh24,) from dual； 14 下午 2 点 08 分为 14ml 分钟数(059) select to_char(sysdate/mr)from dual； 17 下午 4 点 17 分ss 秒数(059) select to_char(sysdate,ss,) from dual； 22 11 点 3 分 22 秒提示注意不要将mm格式用于分钟(分钟应该使用ml)。mm是用于月份的格式，将它用于分钟 也能工作，但结果是错误的。1.5.3 口期运算oracle特别注意月和年的界限问题。如语句select to_char(sysdate+14) from dual；月各在原d

20、ec2002上加1 4天,得岀06jan03。1.6表列数据类型转换 用八常常会用遇到需耍将数据衣列从一种类型转换为另一种类型的情况(如数值型转换成口期型，字符型转换成数值型)。or acl e提供三种主要的转换函数。1. to.char将任意类型的数据转换成字符类型。语句select to_char(8897) from dual；返 回一个包含字符串8897的字符类型的数据。2. to_number将一组合法的数字字符串(如字符数据8897)转换成数值。语句select to_number(,8897,) from dual；返冋一个包括数值8897的数值类型的数据。3. to_date将

21、适合格式的字符串数据转换成门期型数据，语句select to_date('12-dec-02') from dual；语句select to_date(,20021227,j,yyyymmdd,) from dual；将返回口期 27-dec-021.7 update、delete 和 alter 等语句1.7.1 update 语句u update jn跟一个或多个要修改的表，这部分是必不可少的。u set后跟一个或多个要修改的表列，这也是必不可少的。u where后跟更新限定条件，这是可选的。例如，若用户准备把customer表屮的所h sales值改为0,只要执行命令up

22、date customer set sales=0；即可。oracle将修改所有的数据行，并返冋被修改的行-数。 如果只想把state_cd为ma的客户的sales改为0,那么可用sql语句update customer set sales=0 where state_cd='ma'；可见 update 命令的功能十分重 要。1.7.2 delete 语句u关键字delete from后跟准备从中删除数据的农名，这是必不可少的。u关键字where 跟删除条件，为可选的。如果用户想删除customer表的所有记录,可用sql语句delete from customer；。如果用户

23、准备删除state_cd为ca的客户记录,可用sql语句delete from customer where state_cd='ca'；1.7.3 alter 语句u 语句 alter table customer add(sale_date date):将成功地把表列 sale_date 加到表customer中。在多数情况下,用户都是用alter增加表列到表中的。语句alter table x modify(col1 date)：用來改变一个己存在表列的数据类型。(执行alter命令时, 如呆耍改变含有数据的列的数据类型，那么oracle8i对alter命令所执行的操作将

24、有很严格的 限制)u alter table x modify (coll date,col5 number(3,1)；的作用和两条独立的 alter 语句一 样。u oracle8i的新内容:如果想把customer表中的一个新增加的sale_date列删除，可使用如 下语句：alter table customer drop column sale_date；这将立即从表中删除这列，冋收 sale_date列所用的空间。1.8连接两个表1.9格式化输出利用sql* plus命令show all,用户能知道显示格式的当前设置。1.9.1页和行的人小为设置行宽为8 0,可输入命令set lin

25、esize 80。可以用命令set pagesize 30设置页长为3 0行。1.9.2页头标ttitle 'database techologies| customer report'使oracle居中放置文木“ database technologies0为第一个头标行，然后将文本acustomer report”居中放置在第二行。1.9.3页脚标btitle -sample.sql-1使sql* plus把文本“sample.sql”放于每一页底部屮央。用户还可以使用left或right关键字将btitle命令中的文字放到和应的位置。1.9.4将sql* plus结果写到

26、文件中1. spool c:reportout.liso在大多数操作系统中，如果用户未指定后缀，系统会自动在文件 名后增加后缀“.1st”。例如，命令spool report会白动产生一个名为report.1st的文件。2. 为停止假脱机(spooling),可用命令spool off或spool out后者关闭输出文件并打印输 出。1.9.5格式化输出表列1. column last_name format a8 wrap heading *last| name*告诉sql* plus在last_name表列只显示8个字符。8代衣last_name的显示宽度，a表示 每个位过只能是字符。其屮

27、wrap部分说明如果last_name多于8个字符，多余的字符显示 在下一行的对应位逍。heading部分告诉sql* plus把“last name”分两行打印在报告的头上。2. column state_cd format a8 heading 'statel code5ii: sql* plus以8个字符位置显示state_cd,并且在state_cd表列的顶部放一个两行的列标 题"state code”。3. column sales format 999,999,999,999.00 heading 'sales'使 sql* plus 显示1 2位

28、数字，两位小数，用逗号作分隔符。4. col sales format 99999999 格式化了 sales 表列，因为 sales 表列是用 99999999 格 式化的，因此显示结果中没冇小数。1.10格式化所有表列的输出1. 断开逻辑(break logic)执行表列格式化命令col sales format 999999.00然后sql査询语句selects tate_cd, last_name, sales from customer order by state_cd,last_name； 执 行断开命令break on state_cd: state_cd为c a的在第1行显示

29、后，第2行至第5行都不 显示(因为从第一行后state.cd没有改变)，然后第6行和第7行的值不同，乂再显示。2. 断开和跳过(break and skip)用命令 col sales format $999,999,999.99 heading 'ytd| sales'重新格式化 sales 表列, 再执行断开命令break on state_cd skip 1 现在,语句select state_cd, last_name, sales from customer oderby state_cd, last_name；请注 意oracle是怎样控制打卬state_cd表列的

30、。利用这一点町以很容易地通知oracle计算每个 断开部分的总销售(sales)值。3. 在断开点计算表列值命令compute sum of sales on report是在报表结尾处，计算报表的合计值。report在此处 用于他发数值域求和，并显示在报农的结尾处。而命令“ compute sum of sales on state_cd”使在打印新的state_cd时打印合计。为同时打印报 表合计和断开点合计，需要用命令break on report skip 1 on state_cd skip 1巫置断开条 件。修改sql语句以便用state_cd排序数据。对于断开报表，必须用brea

31、k语句中同样的表 列排序数据。公司培训文档混淆的基本概念1) varchar 和 nvarchar 的区别unicode字符数据和非unicode字符数据的区别。(2) char、varchar、text 和 nchar、nvarchar、ntextchar和varchar的长度都在1到8000 z间，它们的区别在于char是定长字符数据，而varchar 是变长字符数据。所谓定长就是长度固定的，当输入的数据长度没冇达到指定的长度吋将自动以 英文空格在其后而填充，使长度达到相应的长度；而变长字符数据则不会以空格填充。text存 储町变长度的非unicode数据，最大氏度为"31-1(

32、2,147,483,647)个字符。后面三种数据类型和前面的相比，从名称上看只是多了个字母f“，它农示存储的是unicode 数据类型的字符。写过程序的朋友对unicode应该很了解。字符中，英文字符只需要一个字节 存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，unicode 字符集就是为了解决字符集这种不兼容的问题而产生的，它所冇的字符都用两个字节表示，即英 文字符也是用两个字节表示。nchar> nvarchar的长度是在1到4000之j'日j。和char> varchar 比较：nchar、nvarchar则最多存储4000个字符，不论

33、是英文还是汉字；而char> varchar 最多能存储8000个英文，4000个汉字。可以看岀使用nchar、nvarchar数据类型时不用担 心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。字串与字元文字字串是一个相当基本且经常被使用到的资料型态，然而在java屮字串不彖与float 样是个基本资料型态，而是使川java.lang.string类别來加以衣示，该类别定 义了许多有用的方法來操作字串ostring物件是固定不变的(immutable)： 旦一个string物 件被建立了，则没有任何方法可以改变它所代表的文字，因此，毎个运作字串的方法会

34、传回一个 新的string物件，而所修正过后的字串便是储存在此新物件里。以下的程式码展示了你可以对字串所执行的运作：/建立字串string s = “now”;/ string物件有个特殊的写法string t = s + ” is the time."/使用+运算子來串连字串string t1 = s + 23.4;/ +将其它值转换为字串t1 = string.valueof(*c');/从字元值获得对应的字串t1 = string.valueof(42);/获得整数或其他任何数值的字串版本t1 = object.tostring();/ 使用 tostring()将物件

35、转换为字串/字串长度int len = t.length();/ 字串屮的字元数：16字串中的子字串string sub = t.substring(4);/ 传回从 char 4 到最后的子字串："is the time.sub = t.substring(4, 6);/ 传回 chars 4 与 5： "is"sub = t.substring(o, 3);/ 传回 chars 0 到 2： "now"sub = t.substring(x, y);/传回从位置x到y-1间的子字串int numchars = sub.length();/ 子字串的长度永远是(y-x)/从一个字串中撷取(extract)出字元char c = t.charat(2);/取得t的第三个字元：wchar ca = t.tochararray();/将字串转换为一个字元阵列t.getchars(o, 3, ca, 1);/将t中的前三个字元放到ca1到ca3中/人小写转换string caps = t.tollpperca