当前位置:17727 > 数据库信息 > Oracle基本数据类型总结【www.17727.com】,SERVER中

Oracle基本数据类型总结【www.17727.com】,SERVER中

文章作者:数据库信息 上传时间:2019-12-27

一同事在写脚本时,遇到一个关于LIKE里面使用不同的变量类型导致查询结果不一致的问题,因为这个问题被不同的人问过好几次,索性总结一下,免得每次都要解释一遍,直接丢一篇博客岂不是更方便!其实看似有点让人不解的现象背后实质跟数据类型的实现有关。

ORACLE基本数据类型(亦叫内置数据类型 built-in datatypes)可以按类型分为:字符串类型、数字类型、日期类型、LOB类型、LONG RAW& RAW类型、ROWID & UROWID类型。

1、什么是数据库?数据库特点?

  1. /+NOCACHE(TABLE)/
    当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
    例如:
    SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

  2. /+APPEND/
    直接插入到表的最后,可以提高速度.
    insert /+append/ into test1 select * from test4 ;

  3. /+NOAPPEND/
    通过在插入语句生存期内停止并行模式来启动常规插入.

 

在讲叙字符串类型前,先要讲一下编码。字符串类型的数据可依编码方式分成数据库字符集(CHAR/VARCHAR2/CLOB/LONG)和国际字符集(NCHAR/NVARCHAR2/NCLOB)两种。数据库中的字符串数据都通过字符集将字符转换为数字后(二进制),才存储到数据块中。通过不同的编码集转换,即便是相同的字符,也可能会转换成不同的二进制编码。这也是产生乱码的原因。数据库的编码格式一般是在创建数据库时指定的。当然也可以修改数据库的编码。

数据库就是存放数据的仓库

insert /+noappend/ into test1 select * from test4 ;

下面我们构造这样一个类似的简单案例。如下所,

查看数据库视图所包含的数据类型:SELECT * FROM DBA_TYPES WHERE OWNER IS NULL.具体细节情况参见Oracle® Database SQL Language Quick Reference 10/11g 或官方文档

海量存储、查找速度快、并发性问题控制、安全性、数据完整性(保存在数据库中的数据是正确的,真是的)

ORACLE内部函数篇

 

 

 

  1. 如何得到字符串的第一个字符的ASCII值?
    ASCII(CHAR)
    SELECT ASCII('ABCDE') FROM DUAL;
    结果: 65

  2. 如何得到数值N指定的字符?
    CHR(N)
    SELECT CHR(68) FROM DUAL;
    结果: D

  3. 如何连接两个字符串?
    CONCAT(CHAR1,CHAR2)
    SELECT CONCAT('ABC','DEFGH') FROM DUAL;
    结果: 'ABCDEFGH'

  4. 如何将列中的数值代替为字符串?
    DECODE(CHAR,N1,CHAR1,N2,CHAR2...)
    SELECT DECODE(DAY,1,'SUN',2,'MON') FROM DUAL;

  5. INITCAP(CHAR)
    将字符串CHAR的第一个字符为大写,其余为小写.
    SELECT INITCAP('ABCDE') FROM DUAL;

  6. LENGTH(CHAR)
    取一字符串CHAR的长度.
    SELECT LENGTH('ABCDE') FROM DUAL;

  7. LOWER(CHAR)
    将字符串CHAR全部变为小写.
    SELECT LOWER('ABCDE') FROM DUAL;

  8. LPAD(CHAR1,N,CHAR2)
    用字符串CHAR2包括的字符左填CHAR1,使其长度为N.
    SELECT LPAD('ABCDEFG',10'123') FROM DUAL;
    结果: '123ABCDEFG'

  9. LTRIM(CHAR,SET)
    从字符串CHAR的左边移去字符串SET中的字符,直到第一个不是SET中的字符为止.
    SELECT ('CDEFG','CD') FROM DUAL;
    结果: 'EFG'

  10. NLS_INITCAP(CHAR)
    取字符CHAR的第一个字符大写,其余字符为小写.
    SELECT NLS_INITCAP('ABCDE') FROM DUAL;

  11. NLS_LOWER(CHAR)
    将字符串CHAR包括的字符全部小写.
    SELECT NLS_LOWER('AAAA') FROM DUAL;

  12. NLS_UPPER(CHAR)
    将字符串CHAR包括的字符全部大写.
    SELECT NLS_UPPER('AAAA') FROM DUAL;

  13. REPLACE(CHAR1,CHAR2,CHAR3)
    用字符串CHAR3代替每一个列值为CHAR2的列,其结果放在CHAR1中.
    SELECT REPLACE(EMP_NO,'123','456') FROM DUAL;

  14. RPAD(CHAR1,N,CHAR2)
    用字符串CHAR2右填字符串CHAR1,使其长度为N.
    SELECT RPAD('234',8,'0') FROM DUAL;

  15. RTRIM(CHAR,SET)
    移去字符串CHAR右边的字符串SET中的字符,直到最后一个不是SET中的字符为止.
    SELECT RTRIM('ABCDE','DE') FROM DUAL;

  16. SUBSTR(CHAR,M,N)
    得到字符串CHAR从M处开始的N个字符. 双字节字符,一个汉字为一个字符的.
    SELECT SUBSTR('ABCDE',2,3) FROM DUAL;

  17. SUBSTRB(CHAR,M,N)
    得到字符串CHAR从M处开始的N个字符. 双字节字符,一个汉字为二个字符的.
    SELECT SUBSTRB('ABCDE',2,3) FROM DUAL;

  18. TRANSLATE(CHAR1,CHAR2,CHAR3)
    将CHAR1中的CHAR2的部分用CHAR3代替.
    SELECT TRANSLATE('ABCDEFGH','DE','MN') FROM DUAL;

  19. UPPER(CHAR)
    将字符串CHAR全部为大写.

  20. ADD_MONTHS(D,N)
    将N个月增加到D日期.
    SELECT ADD_MONTHS(SYS2003-10-17,5) FROM DUAL;

  21. LAST_DAY(D)
    得到包含D日期的月份的最后的一天的日期.
    SELECT LAST_DAY(SYS2003-10-17) FROM DUAL;

  22. MONTH_BETWEEN(D1,D2)
    得到两个日期之间的月数.
    SELECT MONTH_BETWEEN(D1,D2) FROM DUAL;

  23. NEXT_DAY(D,CHAR)
    得到比日期D晚的由CHAR命名的第一个周日的日期.
    SELECT NEXT_DAY(TO_2003-10-17('2003/09/20'),'SATDAY') FROM DUAL;

  24. ROUNT(D,FMT)
    得到按指定的模式FMT舍入到的最进的日期.
    SELECT ROUNT('2003/09/20',MONTH) FROM DUAL;

  25. SYS2003-10-17
    得到当前系统的日期和时间.
    SELECT SYS2003-10-17 FROM DUAL;

  26. TO_CHAR(D,FMT)
    将日期D转换为FMT的字符串.
    SELECT TO_CHAR(SYS2003-10-17,'YYYY/MM/DD') FROM DUAL;

  27. TO_2003-10-17(CHAR,FMT)
    将字符串CHAR按FMT的格式转换为日期.
    SELECT TO_2003-10-17('2003/09/20','YYYY/MM/DD') FROM DUAL;

  28. ABS(N)
    得到N的绝对值.
    SELECT ABS(-6) FROM DUAL;

  29. CEIL(N)
    得到大于或等于N的最大整数.
    SELECT CEIL(5.6) FROM DUAL;

  30. COS(N)
    得到N的余弦值.
    SELECT COS(1) FROM DUAL;

  31. SIN(N)
    得到N的正弦值.
    SELECT SIN(1) FROM DUAL;

  32. COSH(N)
    得到N的双曲余弦值.
    SELECT COSH(1) FROM DUAL;

  33. EXP(N)
    得到N的E的N次幂.
    SELECT EXP(1) FROM DUAL;

  34. FLOOR(N)
    得到小于或等于N的最小整数.
    SELECT FLOOR(5.6) FROM DUAL;

  35. LN(N)
    得到N的自然对数.
    SELECT LN(1) FROM DUAL;

  36. LOG(M,N)
    得到以M为底N的对数.
    SELECT LOG(2,8) FROM DUAL;

  37. MOD(M,N)
    得到M除以N的余数.
    SELECT MOD(100,7) FROM DUAL;

  38. POWER(M,N)
    得到M的N幂.
    SELECT POWER(4,3) FROM DUAL;

  39. ROUND(N,M)
    将N舍入到小数点后M位.
    SELECT (78.87653,2) FROM DUAL;

  40. SIGN(N)
    当N<0时,得到-1;
    当N>0时,得到1;
    当N=0时,得到0;
    SELECT SIGN(99) FROM DUAL;

  41. SINH(N)
    得到N的双曲正弦值.
    SELECT SINH(1) FROM DUAL;

  42. SORT(N)
    得到N的平方根,N>=0
    SELECT SORT(9) FROM DUAL;

  43. TAN(N)
    得到N的正切值.
    SELECT TAN(0) FROM DUAL;

  44. TANH(N)
    得到N的双曲正切值.
    SELECT TANH(0) FROM DUAL;

  45. TRUNC(N,M)
    得到在M位截断的N的值.
    SELECT TRUNC(7.7788,2) FROM DUAL;

  46. COUNT()
    计算满足条件的记录数.
    SELECT COUNT(*) FROM TABLE1 WHERE COL1='AAA';

  47. MAX()
    对指定的列求最大值.
    SELECT MAX(COL1) FROM TABLE1;

  48. MIN()
    对指定的列求最小值.
    SELECT MIN(COL1) FROM TABLE1;

  49. AVG()
    对指定的列求平均值.
    SELECT AVG(COL1) FROM TABLE1;

  50. SUM()
    计算列的和.
    SELECT SUM(COL1) FROM DUAL;

  51. TO_NUMBER(CHAR)
    将字符转换为数值.
    SELECT TO_NUMBER('999') FROM DUAL;

CREATE TABLE TEST

(ID        INT IDENTITY(1,1),

 NAME    VARCHAR(32)

)

 

INSERT INTO dbo.test

SELECT 'abc32'

 

INSERT INTO dbo.test

SELECT 'abd32'

 

INSERT INTO dbo.test

SELECT 'abe32' 

 

 

 

 

DECLARE @name  VARCHAR(32);

SET @name='ab%';

SELECT * FROM TEST WHERE NAME LIKE @name;

 

 

DECLARE @name1 CHAR(32);

SET @name1='ab%';

SELECT * FROM dbo.TEST WHERE NAME LIKE @name1;

 

2、主键的作用?选择主键的注意事项?

ORACLE内部函数篇

 

一 字符串类型

唯一标识表中的一条记录

  1. CHARTOROWID(CHAR)
    将包含外部语法ROWID的CHAR或VARCHAR2数值转换为内部的二进制语法,参数CHAR必须是包含外部语法的ROWID的18字符的字符串.
    SELECT NAME FROM BSEMPMS WHERE ROWID=CHARTOROWID('AAAAfZAABAAACp8AAO');
    NAME : LEIXUE

  2. CONVERT(CHAR,DEST_CHAR_SET,SOURCE_CHAR_SET)
    CONVERT将字符串CHAR中的字符从SOURCE_CHAR_SET标识的字符集转换为由DEST_CHAR_SET标识的字符集

 

    字符串数据类型还可以依据存储空间分为固定长度类型(CHAR/NCHAR) 和可变长度类型(VARCHAR2/NVARCHAR2)两种.

尽量选择单列作为主键:

SELECT CONVERT('GroB','US7ASCII','WE8HP') 'CONVERSION' FROM PUBS;
CONVERSION: Gross

www.17727.com 1

    所谓固定长度:是指虽然输入的字段值小于该字段的限制长度,但是实际存储数据时,会先自动向右补足空格后,才将字段值的内容存储到数据块中。这种方式虽然比较浪费空间,但是存储效率较可变长度类型要好。同时还能减少数据行迁移情况发生。

     1>必须唯一(不能有重复)

  1. HEXTORAW(CHAR)
    将包含十六进制的CHAR转换为一个RAW数值.
    INSERT INTO BSEMPMS(RAW_COLUMN) SELECT HEXTORAW('7D') FROM TEST;

  2. RAWTOHEX(RAW)
    将RAW数值转换为一个包含十六进制的CHAR值.
    SELECT RAWTOHEX(RAW_COLUMN) 'CONVERSION' FROM BSEMPMS;
    CONVERSION: 7D

  3. ROWIDTOCHAR(ROWID)
    将一个ROWID数值转换为VARCHAR2数据类型.
    SELECT ROWID FROM BSEMPMS WHERE ROWIDTOCHAR(ROWID) LIKE '%BR1AAB%';

  4. TO_MULTI_BYTE(CHAR)
    将CHAR中的单字节转换为等价的多字节字符.
    SELECT TO_MULTI_BYTE('ASFDFD') FROM TEST;

  5. TO_SINGLE_BYTE(CHAR)
    将CHAR中的多字节转换为等价的单字节字符.
    SELECT TO_SINGLE_BYTE('ASFDFD') FROM TEST;

  6. TRANSLATE USING(TEXT USING {CHAR_CS|NCHAR_CS})
    将文本TEXT按照指定的转换方式转换成数据库字符集和民族字符集.
    其中TEXT是待转换的.
    USING CHAR_CS参数转换TEXT为数据库字符集,输出数据类型是VARCHAR2.
    USING NCHAR_CS参数转换TEXT为数据库字符集,输出数据类型是NVARCHAR2.
    CREATE TABLE TEST(CHAR_COL CHAR(20),NCHAR_COL NCHAR(20));
    INSERT INTO TEST VALUES('HI,N'BYE');
    SELECT * FROM TEST;

  7. DUMP(EXPR,RETURN_FORMAT,START_POSITION,LENGTH)
    返回一个包含数据类型代码,字节长度等内部表示信息的VARCHAR2值.返回结果是当前数据库字符集,数据类型按照下面规定的内部数据类型的编码作为一个数字进行返回:
    代码 数据类型
    0 VARCHAR2
    1 NUMBER
    8 LONG
    12 2003-10-17
    23 RAW
    24 LONG RAW
    69 ROWID
    96 CHAR
    106 MSSLABEL
    参数RETUEN_FORMAT指定按照下面的基数表示返回的数值.
    RETURN_FORMAT RESULT
    8 8进制
    10 10进制
    16 16进制
    17 单字符表示
    如果参数RETURN_FORMAT没有指定,则按十进制表示返回.
    如果参数START_POSITION和LENGTH被指定,则从START_POSITION开始的长为LENGTH的字节将被返回,缺省是返回整数表示.
    SELECT DUMP('ABC',1016) FROM TEST;
    select dump(ename,8,3,2) 'example' from emp where name='ccbzzp';

  8. empty_b|clob()
    返回一个空的LOB定位符,用在初始化LOB变量,或用在INSERT及UP2003-10-17声明去初始化LOB列或将其属性置为空.
    INSERT INTO TABLE1 VALUES(EMPTY_BLOB());
    UP2003-10-17 TABLE1 SET CLOB_COL=EMPTY_BLOB();

  9. BFILENAME('DIRECTORY','FILENAME')
    返回一个BFILE定位符,相关的二进制LOB物理文件在服务器的文件系统上.目录DIRECTORY是指在服务器的文件系统上实际搜索路径全名的别名. FILENAME是指服务器的文件系统的文件名.
    INSERT INTO FILE_TAB VALUES(BFILENAME('LOB_DIR','IMAGE1.GIF'));

  10. GREATEST(EXPR,EXPR,...)
    GREATEST返回参数的最大值.
    SELECT GREATEST('HARRY','HARRIOT','HAROLD') 'SAMPLE' FROM TABLE1;

  11. LEAST(EXPR,EXPR,...)
    LEAST返回参数的最小值.
    SELECT LEAST('HARRY','HARRIOT','HAROLD') 'SAMPLE' FROM TABLE1;

  12. NLS_CHARSET_DECL_LEN(BYTECNT,CSID)
    返回一个NCHAR列的宽度.
    SELECT NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EEFDFDF')) FROM TABLE1;

  13. NLS_CHARSET_ID(TEXT)
    返回相应于NLS字符集名字的NLS字符集ID数.
    SELECT NLS_CHARSET_D('JADFDFFDF') FROM TABLE1;

  14. NLS_CHARSET_NAME(N)
    返回相应于ID数N的NLS字符集名称.
    SELECT NLS_CHARSET_NAME(2) FROM TABLE1;

  15. NVL(EXPR1,EXPR2)
    若EXPR1是NULL,则返回EXPR2,否则返回EXPR1.
    SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1;

  16. UID
    返回唯一标识当前数据库用户的整数.
    SELECT UID FROM TABLE1;

  17. USER
    用VARCHAR2数据类型返回当前ORACLE用户的名称.
    SELECT USER,UID FROM TABLE1;

  18. USERENV(OPTION)
    返回当前的会话信息.
    OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.
    OPTION='LANGUAGE'返回数据库的字符集.
    OPTION='SESSIONID'为当前会话标识符.
    OPTION='ENTRYID'返回可审计的会话标识符.
    OPTION='LANG'返回会话语言名称的ISO简记.
    OPTION='INSTANCE'返回当前的实例.
    SELECT USERENV('LANGUAGE') FROM DUAL;

  19. VSIZE(EXPR)
    返回EXPR内部表示的字节数.
    SELECT NAME,VSIZE(NAME) FROM TABLE1;

  20. DEREF(E)
    返回参数E的对象引用.
    SELECT DEREF(C2) FROM TABLE1;

  21. REFTOHEX(R)
    将参数R转换为16进制.
    SELECT REFTOHEX(C2) FROM TABLE1;

  22. MAKE_REF(TABLE,KEY,KEY...)
    通过把给定的键作为主键来创建给定视图对象中一行的引用.
    CREATE TYPE T1 AS OBJECT(A NUMBER,B NUMBER);
    CREATE TABLE TB1(C1 NUMBER,C2 NUMBER,PRIMARY KEY(C1,C2));
    CREATE VIEW V1 OF T1 WITH OBJECT OID(A,B) AS SELECT * FROM TB1;
    SELECT MAKE_REF(V1,1,3) FROM PUBS;

  23. STDDEV(DISTINCT|ALL X)
    STDDEV给出一组行值的标准差.
    SELECT STDDEV(SALARY) AS EXAMPLE FROM EMPLOYEE;

  24. VARIANCE(DISTINCT|ALL X)
    VARIANCE返回一组行中所有VALUE的方差.
    SELECT VARIANCE(SALARY) AS EXAMPLE FROM EMPLOYEE;

 

所谓可变长度:是指当输入的字段值小于该字段的限制长度时,直接将字段值的内容存储到数据块中,而不会补上空白,这样可以节省数据块空间。

     2>该列不能为空值

9I管理的V$视图。

 

1.1:CHAR类型 CHAR(size [BYTE | CHAR])

     3>比较稳定的列(不经常更新的,最好是建好以后再也不更新)

  1. V$ACCESS

如上截图所示,当变量使用VARCHAR类型与CHAR类型时,两种的输出结果完全不一样。如果对SQL SERVER数据类型了解不透彻的话,估计真的对这个问题感到相当困惑。但是对SQL Server数据类型了解比较深入的人来说,这真的是一个简单到不能再简单的问题。

CHAR类型,定长字符串,会用空格填充来达到其最大长度。非NULL的CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。如果创建表时,不指定CHAR长度,则默认为1。另外你可以指定它存储字节或字符,例如 CHAR(12 BYTYE) CHAR(12 CHAR).一般来说默认是存储字节,你可以查看数据库参数

     4>选择主键列的时候,推荐使用“逻辑主键”(例如:自动编号、guid等),不推荐“业务主键”(选择有实际意义的列作为主键(例如:身份证号,工号、学号等。))

显示当前被锁定的数据库中的对象及正在访问他们的会话.

 

NLS_LENGTH_SEMANTICS的值。

 

  1. V$ACTIVE_INSTANCES

如下所示,我们在SQL语句中加入两句SQL,用DATALENGTH返回任何表达式的字节数,你会发现VARCHAR类型的变量返回的字节数为3,但是CHAR类型的变量的字节数为32,其实原因就在于CHAR类型是定长的,也就是当你输入的字符小于你指定的数目时,例如char(32),你输入的字符小于32时,它会在后面补空值。当你输入的字符大于指定的数时,它会截取超出的字符. 所以下面两种LIKE的逻辑意义不一样。LIKE 'ab%'  与 LIKE 'abc%       '的逻辑完全不同。

SQL Code

3、下表,分析为什么要有外键?

为当前安装的数据库中出现的所有实例建立从实例名到实例号的映射.

 

  1. SQL> show parameter nls_length_semantics;
  2.  
  3. NAME                   TYPE           VALUE
  4. ------------------   -----------

  5.  
  6. nls_length_semantics   string          BYTE
  7.  
  8. eg:
  9.  
  10. CREATE TABLE TEST
  11.  
  12. (
  13.  
  14. NAME_OLD CHAR(10),
  15.  
  16. NAME_NEW CHAR(10 CHAR)
  17.  
  18. )
  19.  
  20. INSERT INTO TEST
  21.  
  22. ( NAME_OLD, NAME_NEW)
  23.  
  24. SELECT 'ABCDEFGHIJ' , '你清除字节与字符' FROM DUAL;
  25.  
  26. COMMIT;
  27.  
  28. INSERT INTO TEST
  29.  
  30. ( NAME_OLD, NAME_NEW)
  31.  
  32. SELECT '你清除字节与字符' , 'ABCDEFGHIJ' FROM DUAL;
  33.  
  34. ORA-12899: 列 "SYS"."TEST"."NAME_OLD" 的值太大 (实际值: 24, 最大值: 10) 

 

  1. V$ACTIVE_SESS_POOL_MTH

 

注意:数据库的NLS_CHARACTERSET 为AL32UTF8,即一个汉字占用三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK,则一个字符占用两个字节。

数据冗余-数据重复出现,占用空间多,想修改厂家信息得修改很多行,每次录入新的货物的话必须把厂家地址、厂家电话等信息重新录入一次。

所有活动的会话池资源分配方法.

www.17727.com 2

如果串的长度小于或等于250(0x01~0xFA), Oracle 会使用1 个字节来表示长度。对于所有长度超过250 的串,都会在一个标志字节0xFE 后跟有两个字节来表示长度。因此,如果有一个包含“Hello World”的VARCHAR2(80),则在块中可能如图12.-1 所示

使用外键:可以降低数据冗余量~不用太多字符串占硬盘空间~之间用整形数据代表编号就行~

  1. V$AQ

 

www.17727.com 3

假设两张表中都有主键,A表,B表。A表中的主键为AId。B表中的主键为Bid.

当前数据库中的队列的统计量.

 

1.2: NCHAR类型

这时,A表中引用了B表中的Bid作为一列,这时,我们就叫A表为B表的外键表,而B表叫做主键表。

  1. V$ARCHIVE

其实你想从侧面印证一下也很简单,如下脚本对比所示,仔细理解一下,也许你就想明白了!

这是一个包含UNICODE格式数据的定长字符串。NCHAR字段最多可以存储2,000字节的信息。它的最大长度取决于国家字符集。另外查询时,如果字段是NCHAR类型,则需要如下书写

当创建了主外键关系后,如果在外键表中有任何记录引用了主键表中的某条记录,则在主键表中不能删除该记录。同时主键表也不能删除。

归档所需的重做日志文件中的信息.

 

SELECT translated_description FROM product_descriptions

 

  1. V$ARCHIVE_DEST

 

WHERE translated_name = N'LCD Monitor 11/PM';

 

当前实例的所有归档日志目的文件及它们的当前值,模式,状态.

DECLARE @name  CHAR(32);

1.3 VARCHAR类型

4、登陆数据库的方式及区别是什么?

  1. V$ARCHIVE_PROCESSES

SET @name='ab%';

不要使用VARCHAR数据类型。使用VARCHAR2数据类型。虽然VARCHAR数据类型目前是VARCHAR2的同义词,VARCHAR数据类型将计划被重新定义为一个单独的数据类型用于可变长度的字符串相比,具有不同的比较语义。

用户名验证:       通过用户名和密码登陆,在互联网使用较多

为一个实例提供不同的ARCH进程状态的信息.

SELECT * FROM TEST WHERE NAME LIKE @name;

1.4: VARCHAR2类型

Windows身份验证:当前操作系统的身份来验证,一般在局域网访问系统使用

  1. V$ARCHIVE_LOG

 

变长字符串,与CHAR类型不同,它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。

5、、char和varchar的区别是什么?    最长长度8000

控制文件中的归档日志信息.

 

1.5: NVARCHAR2类型

英文和数字都是占一个字节,其它字符占两个字节(中文)

  1. V$BACKUP

DECLARE @name1 CHAR(3);

这是一个包含UNICODE格式数据的变长字符串。 NVARCHAR2最多可以存储4,000字节的信息。

a)Char声明无论多少字节,它都会自动补齐,空格补齐(声明4,输入22,则会有两个空格补齐)~

所有联机数据文件的备份状态.

SET @name1='ab%';

 

不可变的~就是说声明多少输入少了它依然补齐声明存放的大小

  1. V$BACKUP_ASYNC_IO

SELECT * FROM dbo.TEST WHERE NAME LIKE @name1;

 

b)Varchar声明多少字节,存多少数据就是多少,不会空格补齐~

从控制文件中显示备份集的信息.

 

二. 数字类型

可变的~就是说声明多少就存多少~

  1. V$BACKUP_CORRUPTION

 

 

 

从控制文件中显示数据文件备份中有关损坏的信息.

www.17727.com 4

2.1 NUMBER类型

6、nchar和nvchar区别是什么?   最长长度4000

  1. V$BACKUP_DATAFILE

NUMBER(P,S)是最常见的数字类型,可以存放数据范围为10^130~10^126(不包含此值),需要1~22字节(BYTE)不等的存储空间。

nchar(4):Unicode编码~实际一个字符(任何字符)要两个字节存 固定长度

从控制文件中显示备份数据文件和备份控制文件的信息.

P 是Precison的英文缩写,即精度缩写,表示有效数字的位数,最多不能超过38个有效数字

nvchar(4)Unicode编码~实际一个字符(任何字符)要两个字节存 可变长度

  1. V$BACKUP_DEVICE

S是Scale的英文缩写,可以使用的范围为-84~127。Scale为正数时,表示从小数点到最低有效数字的位数,它为负数时,表示从最大有效数字到小数点的位数

 

显示关于支持备份设备的信息.

下面是官方文档的示例

7、len函数和datalength函数返回的是什么?

  1. V$BACKUP_PIECE

Actual Data

Specified As

Stored As

123.89

NUMBER

123.89

123.89

NUMBER(3)

124

123.89

NUMBER(6,2)

123.89

123.89

NUMBER(6,1)

123.9

123.89

NUMBER(3)

124

123.89

NUMBER(4,2)

exceeds precision

123.89

NUMBER(6,-2)

100

.01234

NUMBER(4,5)

.01234

.00012

NUMBER(4,5)

.00012

.000127

NUMBER(4,5)

.00013

.0000012

NUMBER(2,7)

.0000012

.00000123

NUMBER(2,7)

.0000012

1.2e-4

NUMBER(2,5)

0.00012

1.2e-5

NUMBER(2,5)

0.00001

字符数         字节数

从控制文件中显示备份块的信息.

2.2 INTEGER类型

select len(‘a’),datalength(‘a’)        输出都是1

  1. V$BACKUP_REDOLOG

INTEGER是NUMBER的子类型,它等同于NUMBER(38,0),用来存储整数。若插入、更新的数值有小数,则会被四舍五入。

select len(‘没’),datalength(‘没’)      输出1,2

从控制文件中显示关于备份集中归档日志的信息.

例如:

 

  1. V$BACKUP_SET

CREATE TABLE TEST

8、创建数据库的关键字是什么?

从控制文件中显示备份集的信息.

(

----创建 数据库  数据库名称

  1. V$BACKUP_SYNC_IO

ID INTEGER

CREATE DATABASE GuangzhouBlog

从控制文件中显示备份集的信息.

)

   ON  PRIMARY  --默认就属于PRIMARY主文件组,可省略

  1. V$BGPROCESS

查看表TEST的DDL(如何查看创建表的DDL语句)定义如下所示

(

描述后台进程.

CREATE TABLE "SYS"."TEST"

 NAME='GuangzhouBlog',  --主数据文件的逻辑名

  1. V$BH

( "ID" NUMBER(*,0)

 FILENAME='E:Itcast广州广州Dotnet一期dbGuangzhouBlog_data.mdf',  --主数据文件的物理名

是9I的实时应用集群视图.为系统全局区中的每一个缓冲区给出了状态和探查次数.

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

 SIZE=3mb,  --主数据文件初始大小

  1. V$BSP

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

 MAXSIZE=10mb,  --主数据文件最大的值

显示用在缓存中块服务器后台进程上的统计信息.

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

 FILEGROWTH=15%   --主数据文件的增长率

TABLESPACE "SYSTEM" ;

)

INSERT INTO TEST

LOG ON-- 日志文件

SELECT 12.34 FROM DUAL;

(

INSERT INTO TEST

  NAME='GuangzhouBlog_Log',

SELECT 12.56 FROM DUAL;

  FILENAME='E:Itcast广州广州Dotnet一期dbGuangzhouBlog_log.ldf',

SQL> SELECT * FROM TEST;

  SIZE=3mb,  --日志文件初始大小

ID

  MaxSize=20mb,


  FILEGROWTH=1MB

12

)

13

 

2.3 浮点数

9、怎么指定一个数据库来创建数据表?

www.17727.com ,浮点数可以有一个十进制数点任何地方从第一个到最后一个数字,或者可以在所有有没有小数点。指数可能(可选) 用于以下数量增加的范围 (例如, 1.777e-20)。刻度值不适用于浮点数字,因为可以显示在小数点后的位数的数量不受限制。

USE GuangzhouBlog -- 设置当前使用的数据库,即数据表要创建到哪个数据库

二进制浮点数不同数量的值由 Oracle 数据库内部存储的方式。使用小数精度数存储值。完全相同号码存储范围和数量由支持的精度内的所有文本。正是因为使用小数精度(数字 0 到 9) 表示文本存储文本。使用二进制精度 (数字 0 和 1) 存储二进制浮点数。这种存储方案不能代表所有确切地使用小数精度的值。频繁地,将值从十进制转换为二进制的精度时出现的错误时撤消值回从二进制转换为十进制精度。在字面 0.1 是一个这样的例子。

--创建  表    表名

Oracle 数据库提供了专为浮点数的两种数值数据类型:

CREATE  TABLE  Score

BINARY_FLOAT

(

BINARY_FLOAT 是 32 位、 单精度浮点数字数据类型。可以支持至少6位精度,每个 BINARY_FLOAT 的值需要 5 个字节,包括长度字节。

   ScoreId INT IDENTITY(1,1),

BINARY_DOUBLE

   SId  INT  NOT  NULL , 

BINARY_DOUBLE 是为 64 位,双精度浮点数字数据类型。每个 BINARY_DOUBLE 的值需要 9 个字节,包括长度字节。

   English  INT  NOT  NULL, 

在数字的列中,浮点数有小数精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中,浮点数有二进制的精度。二进制浮点数支持的特殊值无穷大和 NaN (不是数字)。

   Math  INT  NOT  NULL

您可以指定列在表 2-4 范围内的浮点数。"数字文本"中定义了用于指定浮点数的格式。

)

Table 2-3 Floating Point Number Limits

 

Value

Binary-Float

Binary-Double

Maximum positive finite value

3.40282E+38F

1.79769313486231E+308

Minimum positive finite value

1.17549E-38F

2.22507485850720E-308

10、bit数据类型在sql语句和设计器里面分别用什么表示?

2.5 FLOAT类型

bit数据类型在写SQL语句时,用1和0表示,设计器里面就是用true和false表示

FLOAT类型也是NUMBER的子类型。

 

Float(n),数 n 指示位的精度,可以存储的值的数目。N 值的范围可以从 1 到 126。若要从二进制转换为十进制的精度,请将 n 乘以 0.30103。要从十进制转换为二进制的精度,请用 3.32193 乘小数精度。126 位二进制精度的最大值是大约相当于 38 位小数精度。

11、插入数据的sql语句是什么?

 

--a.不指定列,直接新增所有列的值

三. 日期类型

INSERT INTO Classes VALUES ('黑豹训练营一期',38,getdate(),0)

日期类型用于存储日期数据,但是并不是使用一般的格式(2012-08-08)直接存储到数据库的。

 

3.1 DATE类型

--b.指定列新增         insert  into  表(列值1,列值2…列值n)  values()

DATE是最常用的数据类型,日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息,但是日期数据类型具有特殊关联的属性。为每个日期值,Oracle 存储以下信息: 世纪、 年、 月、 日期、 小时、 分钟和秒。一般占用7个字节的存储空间。

INSERT INTO Classes(className,count) VALUES ('黑豹训练营二期',138)

3.2 TIMESTAMP类型

 

这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同,因为TIMESTAMP可以包含小数秒,带小数秒的TIMESTAMP在小数点右边最多可以保留9位

--c.insert into talbe select from table

3.3 TIMESTAMP WITH TIME ZONE类型

从自己表里复制一些到自己表     insert  into  数据表(列值1,列值2….列值n) select  列值1,列值2….列值n  数据表

这是TIMESTAMP类型的变种,它包含了时区偏移量的值

INSERT INTO Classes(className,count) SELECT classname,count FROM classes

3.4 TIMESTAMP WITH LOCAL TIME ZONE类型

 

3.5 INTERVAL YEAR TO MOTH

--c.2INTO select    (前提是自己先建好Classes2表,列名相同)      insert  into  目标表(列值1,列值2….列值n) select  列值1,列值2….列值n  数据源表

3.6 INTERVAL DAY TO SECOND

--   从Classes复制一些数据到Classes2表

 

--   先执行后面的select语句,获得一个结果集(两列),然后循环结果集里的行,每次取出行里的两个值,

四. LOB类型

--   然后再执行insert 语句,生成values语法,并将循环到的两个值赋进去,最后,完成新增

内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE(外部存储)的大型化和非结构化数据,如文本、图像、视屏、空间数据存储。BLOB、CLOB、NCLOB类型

INSERT INTO Classes2(className,count) --values('班级名',40)

 

     SELECT classname,count FROM classes

4.1 CLOB 数据类型

 

   它存储单字节和多字节字符数据。支持固定宽度和可变宽度的字符集。CLOB对象可以存储最多 (4 gigabytes-1) * (database block size) 大小的字符

--c.3 insert select 常量union

4.2 NCLOB 数据类型

INSERT INTO Score (SId,English,Math)

   它存储UNICODE类型的数据,支持固定宽度和可变宽度的字符集,NCLOB对象可以存储最多(4 gigabytes-1) * (database block size)大小的文本数据。

SELECT 1,10,99 UNION

4.3 BLOB 数据类型

SELECT 2,66,77 UNION

   它存储非结构化的二进制数据大对象,它可以被认为是没有字符集语义的比特流,一般是图像、声音、视频等文件。BLOB对象最多存储(4 gigabytes-1) * (database block size)的二进制数据。

SELECT 2,66,88 UNION

4.4 BFILE 数据类型

SELECT 1,77,54 UNION

   二进制文件,存储在数据库外的系统文件,只读的,数据库会将该文件当二进制文件处理

SELECT 1,88,99

五. RAW & LONG RAW类型

 

 

--d.SELECT INTO

5.1 LONG类型

 复制一张表(是在复制的同时创建一张新表)      select  列值1,列值2,…..列值n  into  新表   from   数据源表

 

SELECT id,classname,count,adddate,isdel --(指定从数据源表中查询列出来,然后直接作为新表的列名)

它存储变长字符串,最多达2G的字符数据(2GB是指2千兆字节, 而不是2千兆字符),与VARCHAR2 或CHAR 类型一样,存储在LONG 类型中的文本要进行字符集转换。ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。 LONG类型的限制如下:

     INTO Classes4 -- 这个是新表的名称,此时表并不存在

1.一个表中只有一列可以为LONG型。(Why?有些不明白)

     FROM  classes -- 数据源表

2.LONG列不能定义为主键或唯一约束,

 

3.不能建立索引

12、更新数据表的sql语句是什么?         

4.LONG数据不能指定正则表达式。

update  表名称   set  要改变的列值   where   修改的条件

5.函数或存储过程不能接受LONG数据类型的参数。

--1.普通更新

6.LONG列不能出现在WHERE子句或完整性约束(除了可能会出现NULL和NOT NULL约束)

UPDATE classes SET count=45 WHERE id=3--将id=3的行的count列的值设置成45

官方文档描叙如下:

 

The use of LONG values is subject to these restrictions:

--2.更新多个列

A table can contain only one LONG column.

UPDATE classes SET count=55,className='黑豹训练营二.五期' WHERE  id=3

You cannot create an object type with a LONG attribute.

 

LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

--3.更新多行

LONG columns cannot be indexed.

UPDATE classes SET COUNT=COUNT+2 WHERE count<50

LONG data cannot be specified in regular expressions.

 

A stored function cannot return a LONG value.

--4.或or

You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

UPDATE classes SET COUNT=COUNT+10 WHERE count=55 OR count<50

Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

 

LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.

--5.与and

If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

UPDATE classes SET COUNT=COUNT+2 WHERE count=56 and className='广州传智播客.Net训练营一期'

In addition, LONG columns cannot appear in these parts of SQL statements:

 

GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

13、删除数据表的sql语句是什么?

The UNIQUE operator of a SELECT statement

--1.删除整个表的数据

The column list of a CREATE CLUSTER statement

DELETE FROM classes4 --DELETE classes4

The CLUSTER clause of a CREATE MATERIALIZED VIEW statement

 

SQL built-in functions, expressions, or conditions

--2.删除满足条件的数据

SELECT lists of queries containing GROUP BY clauses

DELETE FROM classes3 WHERE COUNT<50 --and or

SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators

 

SELECT lists of CREATE TABLE ... AS SELECT statements

--3.删除多条ID记录

ALTER TABLE ... MOVE statements

DELETE FROM classes WHERE id=4 OR id=5 OR id=6

SELECT lists in subqueries in INSERT statements

DELETE FROM classes WHERE id in(4,5,6)

5.2 LONG RAW 类型,能存储2GB 的原始二进制数据(不用进行字符集转换的数据)

 

5.3 RAW类型

--4.TRUNCATE 能够清空表数据,并且重置主键ID使ID从1开始,而且还不会为每行删除的记录生成删除的日志,而仅仅生成一条日志,不会触发删除触发器

用于存储二进制或字符类型数据,变长二进制数据类型,这说明采用这种数据类型存储的数据不会发生字符集转换。这种类型最多可以存储2,000字节的信息

TRUNCATE TABLE classes2

 

--DELETE TABLE classes2

六. ROWID & UROWID类型

truncate只能操作表,不能操作数据库

在数据库中的每一行都有一个地址。然而,一些表行的地址不是物理或永久的,或者不是ORACLE数据库生成的。

 

例如,索引组织表行地址存储在索引的叶子,可以移动。

14、查询数据表的sql语句是什么?

例如,外部表的ROWID(如通过网关访问DB2表)不是​​标准的ORACLE的rowid。

--1.查询表里所有的行和列数据*代表查询所有列

ORACLE使用通用的ROWID(UROWIDs)的存储地址的索引组织表和外表。索引组织表有逻辑urowids的,和国外表的外urowids,。UROWID这两种类型的存储在ROWID伪(堆组织的表的物理行id)。

--                      (尽量不要使用*,因为会先到系统表里去查询classes表里的所有列名,然后再去查询classes表列数据)

创建基于逻辑的rowid在表中的主键。逻辑的rowid不会改变,只要主键不改变。索引组织表的ROWID伪UROWID数据类型。你可以访问这个伪列,你会堆组织表的ROWID伪(即使用一个SELECT ...ROWID语句)。如果你想存储的rowid索引组织表,那么你就可以定义一列的表型UROWID到列检索值的ROWID伪。

SELECT * FROM classes

 

SELECT id,classname,count,adddate,isdel FROM classes--尽量使用指定列名的查询方式

 

 

作者:潇湘隐者

--2.为列取别名

出处:

SELECT id AS 编号,classname 班级名,count,adddate,isdel FROM classes

如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨小小打赏一下吧,如果囊中羞涩,不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!

 

本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.

--3.通过where 来筛选查询结果集的行

SELECT * FROM classes WHERE [count]>50

 

--4.直接使用select 显示常量和执行表达式

SELECT 1,'我爱北京天安门'

SELECT 1+1

SELECT getdate()

 

--5.使用top选择筛选行

SELECT * FROM classes

--5.1获得前几行

SELECT TOP 3 * FROM classes

     --获得人数最多的前三个班

SELECT TOP 3 * FROM Classes ORDER BY [count] DESC --降序排列 asc 升序排列

--5.2根据百分比来取行

SELECT TOP 50 PERCENT * FROM Classes ORDER BY [count]

 

--6.distinct去除重复项,先把数据表里的行都获取,然后把列逐个的取出放入结果集,在放之前,先检查结果集中是否已存在相同值,如果有,就不添加

SELECT DISTINCT [count] FROM Classes

SELECT DISTINCT [count],classname FROM Classes

 

 

 

15、有哪些聚合函数?

--1.Max求最大值

SELECT * FROM classes

SELECT max(id) from classes

 

--2.Sum求和

SELECT sum(id) from classes

 

--3.Min 求最小值

SELECT min(id) from classes

 

--4.Count 求行数

SELECT COUNT(id) from classes

 

--5.求平均数

SELECT avg(id) from classes

SELECT sum(id)/COUNT(id) from classes

 

16、类型转换

--1.Cast

SELECT * FROM classes

SELECT cast('2012-08-04' AS DATETIME) + 1

--SELECT '2012-08-04' + 1

 

--2.Convert

SELECT convert(DATETIME,'2012-08-04')+1

SELECT cast(adddate AS VARCHAR(10)) FROM Classes c

 

17、查询

--查询----------------------------------------------

--1.Between and

SELECT * FROM Students

SELECT * FROM Students s WHERE s.age BETWEEN 21 AND 26

 

--2. in

SELECT * FROM Students s WHERE age in(20,21,22)

SELECT * FROM Students s WHERE s.age IN (SELECT age FROM Students s WHERE gender=1)

 

18、什么是数据库约束?数据库约束有哪些?

数据库约束:是为了保证数据的完整性(正确性)而实现的一套机制

非空约束:设计器里面的表格数据不允许为空

主键约束:设置主键,唯一约束,值不能重复

唯一约束:设计器选定表格变量右键,索引键,添加一个列名,选择列名,选择是唯一

默认约束:找到某列的列属性有默认值绑定,添加默认值

检查约束:设计器变量右键添加Check约束,添加年龄一个表达式age>0 and age<100

外键约束:就是主外键,右键变量关系,删除一个关系,添加新的关系,选择主外键

 

19、什么是级联删除?

设置级联删除,能够将两个表中的主外键联系的数据删掉,如果没有设置级联删除,那么删除主外键有关系的id就不能删除

表中设计器打开-右键变量-关系=删除规则-级联

 

20、模糊查询的关键字是什么?4个通配符的含义 是什么?  

LIKE 关键字

a)%   包含零个或多个字符的任意字符串。

b)_    任何单个字符。

c)[ ]   指定范围(例如 [a-f])或集合(例如 [abcdef])内的任何单个字符。

d)[^]  不在指定范围(例如 [^a - f])或集合(例如 [^abcdef])内的任何单个字符。请将通配符和字符串用单引号引起来

 

如:把所有姓王的人查出来

-- %代表任意长度的任意字符

SELECT * FROM Students WHERE NAME LIKE '王%'

 

如:取姓王的,有两个、三个字符

-- _代表长度的任意字符

SELECT * FROM Students WHERE NAME LIKE '王_'  

SELECT * FROM Students WHERE NAME LIKE '王_ _' 

 

如:取一位、两位数,[]范围只能是[0-9]或[a-z]

-- []代表长度的[范围]内字符

SELECT * FROM Students WHERE id LIKE '[0-9]'    只能查出一位数

SELECT * FROM Students WHERE id LIKE '[0-9][0-9]'   能查两位数

SELECT * FROM Students WHERE name LIKE '[a-z]'

SELECT * FROM Students WHERE name LIKE '[a-z][a-z]'

 

-- ^ 代表取反,常结合[范围]通配符使用(只有MSSQLSERVER支持)

SELECT * FROM Students WHERE name LIKE '[^a-b]' --查询名字为一个字符,但不在a-b之间。

 

-- not 取反几乎所有数据库都支持

SELECT * FROM Students WHERE NAME not LIKE '[a-b]' --查询不是单个的a-b之间的字符的所有名字

 

21、数据库中的null表示什么?怎么进行空值判断?

数据库中的null表示“不知道”,而不是表示没有。因此select null+1结果是null,因为“不知道”加1的结果还是“不知道”

is null、is not null

 

SELECT * FROM Students WHERE cid is NULL

SELECT * FROM Students WHERE cid IS NOT NULL

--isnull函数isnull(值,为空默认值)--------------------------------

SELECT isnull(null,'默认值')

SELECT id,isnull(cid,0),name,age,gender,birthdate,regdate,isdel FROM Students

 

22、数据排序的关键字是什么?一般放在语句的什么位置?

order  by 一般要放到所有语句的后面

 

--order by 排序(升序-asc 降序-desc)---------------------------------

SELECT * FROM Students ORDER BY id--默认为asc 升序排列

SELECT * FROM Students ORDER BY id DESC --倒序排列

 

--按照字符排序

SELECT * FROM students ORDER BY NAME --如果排序列的值是字符类型,则按照字母的先后次序排列,若果有中文,则按照中文的拼音字母排列

 

--按照多个列排序

SELECT * FROM students ORDER BY name,age--当name相等时,按照年龄升序排列

SELECT * FROM students ORDER BY name,age DESC --当name相等时,按照年龄降序排列

 

23、数据分组的关键字是什么?必须放在语句的什么位置?

group  by必须放到WHERE语句之后

 

--分组(结果集不再是表数据,而是分组的组数据,每一行代表一个组)--------

--SELECT * FROM students GROUP BY cid

--再次强调:分组的结果集是组数据

SELECT cid,count(id) AS 人数 FROM students GROUP BY cid

 

--分组查询的结果集里的列,必须是group by 的列名或者用聚合函数求的的列值

SELECT age,count(id) AS 人数,max(id) as 每个组id最大的值 FROM students GROUP BY age

 

--分组:最后一次强调,查询的结果是组信息,不是表信息。组信息是程序员自己求出来的!!!

--     也就是说,查询的列,必须是组的聚合(行数,最大值,最小值,平均值)信息。

 

--按照多个条件分组(就可以看成是按照多个列的组合值来分组--也就是看组合值是否有相同的,如果有,就算成一个组)

--按照年龄和性别分组

SELECT age,gender FROM students GROUP BY age,gender

SELECT * FROM students

 

 

 

--按照班级和年龄分组

SELECT cid FROM students GROUP BY cid

SELECT cid,age FROM students GROUP BY cid,age

 

--先查询满足条件的人,再分组       一般不用这个方法!!!

--SELECT age FROM students WHERE age>22 GROUP BY age

 

24、having和where的区别是什么?

where是先查询满足条件,再分组;having是先分组再筛选

 

a)注意Having中不能使用未参与分组的列,Having不能替代where。作用不一样,Having是对组进行过滤。

b)Having 是Group By的条件对分组后的数据进行筛选(与Where类似,都是筛选,只不过having是用来筛选分组后的组的。)

c)在Where中不能使用聚合函数,必须使用Having,Having要位于Group By之后。

d)Having的使用几乎是与where一样的,也可以用in。

Having count(*) in (5,8,10)

--having 分组之后筛选条件

--先分组,然后再筛选满足条件的组

SELECT age,count(id)AS 总人数 FROM students GROUP BY age HAVING age>22 and COUNT(id) >3

--注意:常见错误

--此举报错,因为having是在select之前执行,也就是说执行having的时候压根儿就没有总人数这个列!

--SELECT age,count(id)AS 总人数FROM students GROUP BY age HAVING age>22 and 总人数>3

SELECT age,count(id)AS 总人数 FROM students GROUP BY age HAVING age>22 and COUNT(id) >3 order by  总人数 desc

 

25、SQL语句的执行顺序

5>…Select 5-1>选择列,5-2>distinct,5-3>top

1>…From 表

2>…Where 条件

3>…Group by 列

4>…Having 对组来做筛选条件

6>…Order by 列

 

26、类型转换

--类型转换

SELECT '您的班级编号'+ 1         报错!!!

SELECT convert(INT,'123')+ 1    不会报错!!!因为转成的是数字!!!

SELECT cast('123' as int)+ 1

--补充截取方法

SELECT left('abcd',2)

SELECT right('abcd',2)

--convert来转日期

SELECT getdate()

SELECT CONVERT(VARCHAR,GETDATE(),110)

SELECT CONVERT(VARCHAR,GETDATE(),102)

SELECT CONVERT(VARCHAR,GETDATE(),113)

 

27、Union集合运算符的基本原则是什么?

每个结果集必须有相同的列数;每个结果集的列必须类型相容

 

c)联合:将多个结果集合并成一个结果集。union(去除重复,相当于默认应用了distinct)、union all

d)Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复行,那么就用UNION ALL

 

--Union联合结果集

--联合结果集时要注意列的数量和数据类型必须一致或兼容

--union会自动合并相同数据行

SELECT * FROM Classes     有7条内容

UNION

SELECT * FROM Classes2    有1条内容

输出显示为合并成8条

 

--union all 联合结果集,不会去除重复行

SELECT * FROM Classes

UNION ALL

SELECT * FROM Classes2

 

SELECT * FROM students

UNION

sELECT NULL,NULL,'年龄统计',sum(age),null,null,null,null FROM students

--Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复行,那么就用UNION ALL

www.17727.com 5www.17727.com 6

SELECT [sid]
      ,[name]
      ,[age]
      ,[sex]
      ,[address]
  FROM [TestDB].[dbo].[UserInfo]
  UNION ALL
SELECT [sid]
      ,[name]
      ,[age]
      ,[sex]
      ,[address]
  FROM [TestDB].[dbo].[UserInfoTest]

View Code

 

28、len和datalength求出的是什么?

len-求字符个数  datalength-求字节数

 

SELECT len('james'),datalength('james')

都是输出5因为都是英文字母

SELECT len('中国第一'),datalength('中国第一')

第一个输出4,第二个输出8,中文一个字符占两个字节

 

LTRIM():字符串左侧的空格去掉

RTRIM () :字符串右侧的空格去掉

 

SELECT '         bb        '

select LTRIM(RTRIM('         bb        ')) AS '1'

LEFT()、RIGHT()  截取取字符串

 

29、日期函数

a)GETDATE() :取得当前日期时间

b)DATEADD (datepart , number, date ),计算增加以后的日期。参数date为待计算的日期;参数number为增量;参数datepart为计量单位。DATEADD(DAY, 3,date)为计算日期date的3天后的日期,而DATEADD(MONTH ,-8,date)为计算日期date的8个月之前的日期 c)DATEDIFF ( datepart , startdate , enddate ) :计算两个日期之间的差额。 datepart 为计量单位,可取值参考DateAdd。

d)DATEPART (datepart,date):返回一个日期的特定部分

Month()、year()、day()来代替

 

--日期函数

--获得当前日期

select getdate()

--日期操作dateadd(单位,数值,日期)

SELECT dateadd(MONTH,2,getdate())     加2个月

SELECT dateadd(MONTH,-2,getdate())    减2个月

SELECT dateadd(YEAR,-2,getdate())

 

SELECT * FROM classes ORDER BY adddate

--求日期差

SELECT datediff(DAY,GETDATE(),'2012-08-04')

--查询昨天和前天的所有添加的数据

SELECT * FROM Classes WHERE datediff(day,addDate,GETDATE())in(2,1)

--DELETE FROM classes WHERE datediff(day,addDate,GETDATE())in(2,1)

 

--求日期部分

SELECT datepart(year,getdate())

SELECT year(getdate())

SELECT MONTH(getdate())

SELECT DAY(getdate())

--练习:统计学员生日的日的个数

SELECT * FROM students

SELECT day(birthdate) AS 出生日,COUNt(*) FROM students GROUP BY day(birthdate)

SELECT DATEDIFF(minute,'2012-08-06','2012-08-05')

 

30、case的两种用法是什么?

--1.case语法。要注意,then后的数据类型要一致

--1.1等值判断:

SELECT id, case cid

                   WHEN 1 THEN '一班'

                   WHEN 2 THEN '二班'

                   ELSE '未知'

              END AS cid

,name,age FROM Students

 

--1.2区间判断

SELECT id, case

                   WHEN age>0 AND age <=20 THEN '小女孩'

                   WHEN age>20 AND age <=30 THEN '少女'

                   WHEN age>30 AND age<=40 THEN '阿姨'

                   WHEN age>40 AND age<=100 THEN '大妈'

                   ELSE '未知'

              END AS 怪物等级

,name,cid FROM Students

 

31、绝对值函数语法是什么?

SELECT ABS(-1)

 

32、子查询的概念什么?分哪两类?

a)概念:把一个查询的结果在另一个查询中使用就叫子查询。(将一个查询语句做为一个结果集供其他SQL语句使用)

b)子查询基本分类:

独立子查询

子查询可以独立运行

相关子查询

子查询中引用了父查询中的结果

 

--2.子查询(就是查询另一个sql语句的结果集)***************************************

SELECT * FROM (SELECT * FROM Area WHERE ar_uid=0) AS tempTable   //一定要别名!!!

 

--2.2使用子查询搜索外键表数据(单列多行)

SELECT * FROM Students WHERE cid in(--然后再根据黑猫班的id查询学员

    SELECT ID FROM dbo.Classes WHERE classNAME LIKE '%黑猫%'--先查出黑猫班的id

)

 

--2.3使用子查询的=、!=、<、<=、>、>= 符号时,子查询的结果里只能有一个值,不能有多个列和行

SELECT * FROM Students WHERE age > (SELECT age FROM Students WHERE name='地球超人')

 

SELECT * FROM Students WHERE Students.cid in(36,37)

 

--2.3.1Any 集合:=、!=、<、<=、>、>= 符号使用,相当于or

SELECT * FROM Students WHERE Students.cid >= Any(SELECT 36 UNION select 37)

SELECT * FROM Students WHERE Students.cid >= 36 or Students.cid >=37

 

--2.3.2All 集合:=、!=、<、<=、>、>= 符号使用,相当于and

SELECT * FROM Students WHERE Students.cid >= All(SELECT 36 UNION select 37)

SELECT * FROM Students WHERE Students.cid >= 36 and Students.cid >=37

 

--exists 表示是否存在

SELECT * FROM Students where exists (SELECT * FROM Classes WHERE classNAME LIKE '%黑猫%' and Classes.id=Students.cid)

SELECT * FROM Students where cid in (SELECT id FROM Classes WHERE classNAME LIKE '%黑猫%' and Classes.id=Students.cid)

 

 

33、分页的三种方式是什么?

--3.1 top+orderby 来实现分页(页容量:)效率低,不用

--获得第一页数据

SELECT TOP 10 * FROM Area

--获得第二页数据

SELECT TOP 10 * FROM

     (SELECT TOP 20 * FROM Area ORDER BY ar_id ASC) AS tempTable

ORDER BY ar_id DESC

 

--3.2 使用not in 分页

SELECT top 10 * FROM Area WHERE ar_id NOT IN (SELECT TOP 30 ar_id FROM Area)

 

--3.3使用and / between and /row_number()

SELECT * FROM Area

SELECT * FROM Area WHERE ar_id>=10 AND ar_id<=19--直接使用id来作为分页的条件不好,因为中间有id被删除了

 

--所以应该使用Row_Number() 生成一个连续的序号列over告诉Row_Number是按照某个列的顺序来生成序号

SELECT Row_Number() OVER (ORDER BY ar_id ASC) AS 序号,* FROM Area

SELECT Row_Number() OVER (ORDER BY ar_name ASC) AS 序号,* FROM Area

 

--使用row_number分页(微软推荐分页方法)

SELECT * FROM

     (SELECT Row_Number() OVER (ORDER BY ar_id) AS 序号,* FROM Area) as tempTable

WHERE  序号>=10 AND 序号<=19

 

 

SELECT * FROM Students

SELECT * FROM classes

 

 

34、连接查询有哪几种?

--4.1内连接-根据条件将两个表的指定列合成一行数据(查询出满足条件的行) 例:查询学员及其所在班级信息

SELECT * FROM Students INNER JOIN Classes ON students.cid=classes.id

--相当于如下多表查询语句:

SELECT * FROM Students,Classes WHERE students.cid=classes.id

--左右两张表中任何一行不满足条件,就都不输出

SELECT * FROM Students INNER JOIN Classes2 on students.cid=classes2.id

 

--4.2左外连接-保证左表中每行记录都显示,右表中如果有满足on条件的,就显示,没有就不显示

SELECT * FROM Students LEFT JOIN Classes2 on students.cid=classes2.id

 

--4.3右外连接-保证右表中每行记录都显示,左表中如果有满足on条件的,就显示,没有就不显示

SELECT * FROM Students RIGHT JOIN Classes3 on students.cid=classes3.id

 

--4.4交叉连接- 将左表的每一行与右表的每一行都连接一次,总行数= 左表行数* 右表行数(笛卡尔乘ji)

SELECT * FROM Students CROSS JOIN Classes3

 

 

35、什么是视图?视图和数据表的本质区别是什么?

a)视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上

b)视图在操作上和数据表没有什么区别,但两者的差异是其本质是不同:数据表是实际存储记录的地方,然而视图并不保存任何记录。

c)相同的数据表,根据不同用户的不同需求,可以创建不同的视图(不同的查询语句)

d)视图的目的是方便查询,所以一般情况下不对视图进行增改,不能删

优点:

  • 筛选表中的行
  • 防止未经许可的用户访问敏感数据
  • 降低数据库的复杂程度

e)普通视图

  • 并不存储数据(虚拟表),访问的是真实表中的数据

f)使用视图注意事项:

  • 1.视图中的查询不能使用order by ,除非指定了top语句。

视图被认为是一个虚拟表,表是一个集合,是不能有顺序的。而order by 则返回的是一个有顺序的,是一个游标。

在视图中使用select top percent + order by 问题。

  • 如果指定列名,则列名必须唯一(使用*不考虑)
  • create view vw_name as 后不能跟begin end.

g)(*)索引视图

  • 在视图上创建唯一聚集索引

数据会保存在数据库中而不是引用表中的数据

 

--视图内部储存了一个查询sql语句,那么当我们查询视图时,就相当于是查询了视图里的sql语句的结果集(子查询)

--视图内的列名不能重复

SELECT * FROM vwStudentClass

SELECT * FROM vwcontact_group

 

--5.1新增视图数据

--错误:视图或函数'vwcontact_group' 不可更新,因为修改会影响多个基表

--INSERT INTO vwcontact_group(uname,cellphone,homephone,groupName,gid)

                    --VALUES ('jam',123123123,123123123,'小三们',1)

--通过:新增的列都来源于一张基表,所以可以新增

INSERT INTO vwcontact_group(uname,cellphone,homephone,groupName,groupId)

                    VALUES ('jam',123123123,123123123,'小三们',1)

                   

SELECT * FROM vwcontact_group

--5.2修改视图数据

--通过:因为只更新了视图里一张基表的列

UPDATE vwcontact_group SET uName='bobo' WHERE id=7

--错误:修改会影响多个基表。

UPDATE vwcontact_group SET uName='网秦',name='情网' WHERE id=7

 

SELECT * FROM vwcontact_group

--5.3删除视图数据

--错误:因为删除会影响多个基表

DELETE FROM vwcontact_group WHERE id=7

--通过:如果视图里只包含一个基表,那么就可以使用删除

SELECT * FROM vwTeacher

DELETE FROM vwTeacher WHERE id=4

 

--5.4视图中的查询不能使用order by ,除非指定了top语句

SELECT * FROM Teacher ORDER BY salary

 

--5.5代码创建视图

CREATE VIEW vwArticle_Cate

AS

SELECT a.id AS aid,a.cid,a.title,a.content,ac.id AS cateid,ac.name,ac.parentId FROM Article a INNER JOIN ArticleCate ac ON a.cid=ac.id

 

SELECT * FROM vwArticle_Cate

 

 

36、局部变量的声明?赋值?

a)声明

    DECLARE @变量名  数据类型

b)赋值
SET @变量名 =值      --set用于普通的赋值
SELECT @变量名 = 值  --用于从表中查询数据并赋值,可以一次给多个变量赋值

 

--6.1变量的声明

DECLARE @name VARCHAR(40),@age int

--6.2赋值

SET @name='我爱北京天安门~~~天安门前武警手里有灭火器!'

SELECT @age=age FROM Teacher --如果该列中有多个值,则将最后一个值给变量

--6.3显示变量

SELECT @name,@age--在结果集中显示

print @name --在消息框中打印

 

SELECT *,ac FROM Article a

SELECT @@version

 

备注:全局变量(系统变量):

全局变量必须以标记@@作为前缀,如@@version

全局变量由系统定义和维护,我们只能读取,不能修改全局变量的值

@@error变量,在每次执行完SQL语句后,都会为@@error变量赋值,如果上次执行的SQL语句有错,则将@@errro赋值为一个不为0的值,否则(执行没错),则将@@error赋值为0.

@@Identity最后一次插入的标识值

 

 

37、if  else  和  while

a)格式

IF(条件表达式)

  BEGIN --相当于C#里的{

    语句1
  ……

  END --相当于C#里的}

ELSE

 BEGIN

    语句1

    ……

  END

 

DECLARE @name VARCHAR(40)

SET @name='你很优秀很不错~好英俊!你信吗?!'

if(len(@name)>20)

     BEGIN

         SELECT '文字长度太长了'

     END

ELSE

     BEGIN

         SELECT '你的文字太短了'

     END

 

--练习:

--计算平均分数并输出,如果平均分数超过分输出成绩最高的三个学生的成绩,否则输出后三名的学生

DECLARE @avgScore INT

SELECT @avgScore = avg(math+english) FROM Score

if(@avgScore > = 120)

BEGIN

     SELECT TOP 3 * FROM Score ORDER BY math,english desc

END

ELSE

BEGIN

     SELECT TOP 3 * FROM Score ORDER BY math,english

END

 

--练习:

--计算平均分数并输出,如果平均分数超过分输出成绩最高的三个学生的成绩,否则输出后三名的学生

DECLARE @avgScore INT

SELECT @avgScore = avg(math+english) FROM Score

if(@avgScore > = 120)

BEGIN

     SELECT TOP 3 * FROM Score ORDER BY math,english desc

END

ELSE

BEGIN

     SELECT TOP 3 * FROM Score ORDER BY math,english

END

 

b)格式

WHILE(条件表达式)

  BEGIN --相当于C#里的{

    语句

    ……

      continue --退出本次循环

    BREAK    --退出整个循环

  END --相当于C#里的}

 

--计算-100之间所有奇数的和

DECLARE @totalNum INT =0,@times int=0 --声明变量同时给初始值

WHILE(@times<100)--循环

BEGIN

     if(@times%2!=0)--取模

     BEGIN

         set @totalNum+=@times

     END

     set @times+=1

END

SELECT @totalNum

 

--练习:如果english不及格的人超过半数(考试题出难了),则给每个人增加分,循环加,直到不及格的人数少于一半

 

DECLARE @failNum float--不及格人数

DECLARE @stuNum float--参加考试的总人数

WHILE(1=1)

BEGIN

     SELECT @failNum=count(*) FROM Score  WHERE English<60

     SELECT @stuNum=count(*) FROM Score  WHERE English IS NOT NULL

     --if(@failNum*2 > @stuNum)

     if(@failNum >= (@stuNum/2))

     BEGIN

         UPDATE Score SET English+=2

         PRINT '不及格人数超过了总人数一半'

     END

     ELSE

     BEGIN

         PRINT '不及格人数没有超过总人数一半'

         BREAK--退出循环

     END

END

SELECT @failNum AS 不及格人数

 

SELECT 5.0/2

 

SELECT * FROM Score

 

 

38、事务

a)事务:同生共死

b)指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成,必须作为一个整体执行

c)这些sql语句作为一个整体一起向系统提交,要么都执行、要么都不执行

d)语法步骤:

  • 开始事务:BEGIN TRANSACTION
  • 事务提交:COMMIT TRANSACTION
  • 事务回滚:ROLLBACK TRANSACTION

e)判断某条语句执行是否出错:

  • 全局变量@@ERROR;
  • @@ERROR只能判断当前一条T-SQL语句执行是否有错,为了判断事务中所有T-SQL语句是否有错,我们需要对错误进行累计;

 

 

为什么需要事务?

如,转账问题:

    假定钱从A转到B,至少需要两步:

A的资金减少

然后B的资金相应增加     

Update  bank  set  balance=balance-1000    where cid='0001'

Update  bank  set  balance=balance + 1000  where cid='0002'

--查看结果。

    SELECT * FROM bank

   注意约束:金额不能小于10

假设cid='0001'的balance为1000,cid='0002'的balance为10,则会出现第一个更新语句不能通过,因为金额不能小于10,但是第二条更新语句通过,增加了1000,这就是漏洞!

--8.1创建表,准备数据

create table bank

(

     cId char(4) primary key,

     balance money,           --余额

)

 

alter table bank

add constraint CH_balance check(balance >=10)

 

DELETE FROM bank

insert into bank values('0001',1000)

insert into bank values('0002',10)

 

 

--8.2事务:将多条语句作为一个执行单元,单元中任意语句出错的话,其他语句对数据库造成的影响都要取消掉

SELECT * FROM bank

 

DECLARE @errCount INT =0 --声明变量,用来累计错误号

---------------------------------事务开始------------------------

BEGIN TRANSACTION--开启事务***

 

UPDATE bank SET balance = balance - 1000 WHERE cId='0001'

SET @errCount = @errCount + @@error  --统计错误号

 

UPDATE bank SET balance = balance + 1000 WHERE cId='0002'

SET @errCount = @errCount + @@error  --统计错误号

 

if(@errCount>0)--如果统计的错误号大于,说明之前某语句执行时有错误,必须回滚事务

     BEGIN

         ROLLBACK TRANSACTION --一旦***回滚事务,那么事务中的任何对数据库的影响都会被还原

         PRINT '事务回滚完毕'

     END

ELSE

     BEGIN

         COMMIT TRANSACTION --一旦***提交事务,那么事务中所有的影响都会保存到数据库中

         PRINT '事务提交完毕'

     END

 

39、存储过程

a)存储过程---就像数据库中运行方法(函数)

b)和C#里的方法一样,由存储过程名/存储过程参数组成/可以有返回结果。

c)前面学的if else/while/变量/insert/select 等,都可以在存储过程中使用

优点:

执行速度更快 – 在数据库中保存的存储过程SQL语句都是编译过的

允许模块化程序设计 – 类似方法的复用

提高系统安全性 – 防止SQL注入

减少网络流通量 – 只要传输 存储过程的名称

l  系统存储过程

  • 由系统定义,存放在master数据库中

名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头

 

--9.1创建一个简单的存储过程

CREATE PROC up_justforfun

AS

BEGIN

     SELECT * FROM Students

     SELECT * FROM classes

END

--9.2调用不带参数的存储过程

EXEC up_justforfun

 

--9.3修改存储过程alert(警报)  alter(修改)

ALTER PROC up_justforfun

@age INT =20 --定义参数,并设置默认值

AS

BEGIN

     SELECT * FROM Students WHERE age>@age

     SELECT * FROM classes

END

--9.2调用带输入参数的存储过程

EXEC up_justforfun 30

EXEC up_justforfun

 

--9.3创建带多个参数的存储过程

create PROC up_justforfunWithParas

@age INT =20,

@name VARCHAR(20)

AS

BEGIN

     SELECT * FROM Students WHERE age>@age AND NAME LIKE @name

     SELECT * FROM classes

END

--9.4调用带多个参数的存储过程

EXEC up_justforfunWithParas 20,'%a%'

--9.5显示的传参(可以不为有默认值的参数传参了)

EXEC up_justforfunWithParas @age=30,@name='%a%'

EXEC up_justforfunWithParas @name='%a%'

 

--9.6创建带输出参数的存储过程

create PROC up_justforfunWithOutPutParas

@age INT =20,

@name VARCHAR(20),

@stuCount INT OUTPUT --定义:输出类型的参数

AS

BEGIN

     SELECT * FROM Students WHERE age>@age AND NAME LIKE @name

     SELECT @stuCount=COUNT(*) FROM Students--将总行数赋值给输出参数@stuCount

END

--9.7调用带输出参数的存储过程

DECLARE @studentCount int--先在外面定义一个变量

EXEC up_justforfunWithOutPutParas 30,'%a%',@studentCount OUTPUT --将变量作为输出参数传入存储过程

SELECT @studentCount AS 学生总人数 --将存储过程修改后的变量显示

 

 

 

SQL语句:两个单引号相当于一个单引号

 

 

 

 

 

 

 

 

习题:

练习:

--输出所有数据中通话时间最长的条记录。orderby datediff

select top 5* ,datediff(second,startdatetime,enddatetime) 时长 from callrecords

order by 时长 desc

--输出所有数据中拨打长途号码(对方号码以开头)的总时长。like、sum

select sum(datediff(second,startdatetime,enddatetime)) 长途号码总时长 from callrecords

where telnum like '0%'

--输出本月通话总时长最多的前三个呼叫员的编号。

select top 3 callernumber 呼叫员编号,sum(datediff(second,startdatetime,enddatetime)) 通话时长 from callrecords 呼叫员编号

 where month(startdatetime)=7

 group by callernumber

 order by 通话时长 desc

--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)

select top 3 callernumber 呼叫员编号 ,count(id) 打电话次数最多 from callrecords

where month(startdatetime)=7

group by callernumber 

order by count(id) desc

--按照月份分组。

SELECT month(startdatetime) 月份 ,count(id) 月份个数 FROM callrecords

GROUP BY month(StartDateTime)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

--练习

--按照班级人数排序

SELECT cid AS 班级,count(id) as 总人数 FROM students GROUP BY cid ORDER BY count(id)

--请统计总年龄超过的班级名称和总年龄,并按总年龄排序

SELECT cid AS 班级,sum(age) as 总年龄 FROM students GROUP BY cid having sum(age)>100 ORDER BY sum(age)

--统计各个性别在cid=2的班的总人数,并按照总人数排序

SELECT cid AS 班级,count(id) as 总人数 FROM students where cid=2 GROUP BY cid ORDER BY count(id)

SELECT cid AS 班级,count(id) as 总人数 FROM students GROUP BY cid having cid=2 ORDER BY count(id)

 

--练习题:

--显示学生表的所有列和班级表的班级名称列

SELECT s.*,c.classname FROM Students s INNER JOIN Classes c ON s.cid=c.id WHERE s.age>20

--询学生姓名、年龄、班级及成绩(忽略没有参加考试和没有所在班级的学员)

SELECT * FROM Students s INNER JOIN Classes c ON s.cid=c.id inner join Score sc ON s.id=sc.SID

--查询所有学生(参加及未参加考试的都算)及成绩

SELECT * FROM Students s left JOIN Score sc ON s.id=sc.SID

--请查询出所有没有参加考试(在成绩表中不存在的学生)的学生信息

SELECT * FROM Students s WHERE s.id NOT in(SELECT SID FROM Score)

 

--练习题:

--练习:查询所有英语及格的学生姓名、年龄及成绩

SELECT s.name,s.age,sc.english,sc.math FROM Students s INNER JOIN Score sc ON s.id=sc.SID WHERE sc.english>=60

--练习:查询所有参加考试的(english分数不为null)学生姓名、年龄及成绩

SELECT s.name,s.age,sc.english,sc.math FROM Students s INNER JOIN Score sc ON s.id=sc.SID WHERE sc.english IS NOT NULL

--练习:查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩,如果没有参加考试显示缺考,如果小于english&math60分显示不及格

SELECT s.name,s.age,CASE

                       WHEN  sc.english IS NULL THEN '缺考'

                       WHEN sc.english<60 THEN'不及格'

                       ELSE cast(sc.english AS VARCHAR)--因为case是生成一个列,这个列的数据类型必须统一

                    end as 英语考试,

                   

                    CASE

                       WHEN  sc.math IS NULL THEN '缺考'

                       WHEN sc.math<60 THEN'不及格'

                       ELSE cast(sc.math AS VARCHAR)--因为case是生成一个列,这个列的数据类型必须统一

                    end as 数学考试

FROM Students s left JOIN Score sc

ON s.id=sc.SID

 

本文由17727发布于数据库信息,转载请注明出处:Oracle基本数据类型总结【www.17727.com】,SERVER中

关键词: