SQL基础总结

SQL基础知识总结

一、SQL命令类别

DDL：数据定义语言，用来创建、修改或删除数据库及数据库中的表等对象。

            create 、drop、alter、truncate、rename

DML：数据操纵语言，用来查询或修改表中的记录。

            select、insert、delete、update、merge

DCL：数据控制语言，用来设置权限。

           grant、revoke

TCL：事物控制语言，用来确认或取消对数据的变更。

           commit、rollback、savepoint

    有的资料将命令分为三类，DDL、DML、DCL ，其中DCL是上述DCL和TCL的合并。

二、基本语法

SQL语句不区分大小写，但插入到表中的数据是区分大小写的。

1、创建表：

        create table 表名

        (列名1 数据类型 该列所需约束，

         列名2 数据类型 该列所需约束，

         列名3 数据类型 该列所需约束，

         ·····

         该表的约束1，该表的约束2，·····)；

        每一列的数据类型是必须要指定的，还要为需要的列设置约束，约束可以在定义列的时候设置，也可以在语句末尾进行设置。

        括号中是一条或多条表定义，表定义包括字段定义和约束定义两种，一张表中至少要有一个字段定义，而约束定义是可选的，约束定义包括主键约束，外键约束，唯一约束，非空约束等。

        

2、创建已有表的表结构

        

        同一个数据库中不能创建两个名称相同的表，同一个表也不能创建两个名称相同的列。

3、定义主键

        在oracle中定义主键的时候，通过primary key关键字，定义的位置是所有的字段之后。

        

        联合主键：两个或多个字段作为联合主键，定义的时候在primary key后面括号列出联合主键的各个字段。（考虑到缺点，一般都在特殊场合使用，一般都使用唯一主键）

4、定义外键

        外键定义在所有字段的后面，使用foreign key关键字定义外键，使用references关键字来定义目标表名以及目标表中被关联的字段，格式如下：

        foreign key 外键名称 references 目标表名（被关联的字段）

        如创建一张部门表，包含部门主键id,部门名称等：

        

        接着创建员工信息表，表中记录工号，姓名，所属部门等信息，通过在员工表中保存部门表中的主键，来建立员工表和部门表之间的联系，这个保存主键的字段叫做外键。

        

三、数据类型

四种基本数据类型：字符型(char,varchar,varchar2,nchar)、数值型(number,int,float)、日期型(date,timestamp)、大对象型(bfile,blob,clob,nlob)

1、char和varchar的区别：char是固定长度，字符串不到最大长度的部分用空格补足，varchar是可变长度字符串。

        

2、char和nchar的区别：char一个汉字占三个字节，nchar一个汉字占一个字节。

       

        当指定列的数据类型为字符型时，根据选择的类型不同，其括号中的数值表示的含义由数据库参数NLS_LENGTH_SEMANTICS来定义（BYTE或CHAR，默认值为BYTE）。比如默认情况下，varchar2(6),char(6) 中的6表示6个字节。但NCHAR（6），NVARCHAR2（6）中的6则总是表示字符数量。

            根据数据库所选用的字符集不同，同样的字符，其存储时占用的字节数有可能是不同的。比如中文字符，在ZHS16GBK字符集中，其占用2个字节，但在AL32UTF8字符集中，则大部分占用3个字节。因此，虽然同样定义VARCHAR2（6），在不同的字符集的情况下，可以存储的字符数量是不同的。

           NCHAR和NVARCHAR2中存储的字符，全部用国家字符集（通常是使用AL16UTF16字符集）来表示。国家字符集是ORACLE早期，为了解决其不支持多字节字符（比如中文）存储的一个解决方案。当向该数据类型的列中存储数据时，一律使用国家字符集来存储，当数据库设置的国家字符集是AL16UTF16时，其总是用2个字节表示一个字符。

           因此，假设一个列是VARCHAR2(6)，则其可以最多使用6个字节的存储空间，如果是AL32UTF8字符集（基本上是3个字节表示一个中文字符），则最多只能保存2个字符。再假设一个列是NVARCHAR2(6)，则其可以最多存储6个字符，注意是字符，不是字节。

   注：数据库字符集由参数NLS_CHARACTERSET指定，而国家字符集则由参数NLS_NCHAR_CHARACTERSET指定。

3、number(p,s)：包含小数的数据类型，number(10,2)代表小数点前最多有8位，小数点后最多有2位。

四、基本SQL语句

1、三种设置别名的方法：as  空格  双引号。where中不可以使用列别名。

2、distinct（去重）

       a、使用distinct时，null也被视为一类数据，去重结果中会有null

       b、distinct后面可以跟多列，此时是对distinct后面的多列进行组合后去重

       c、distinct关键字只能用在第一个列名之前，一个select语句中不能出现多个distinct关键字

3、 null

       空值既不是数值0,也不是字符" ", null表示不确定。

       分组函数忽略空值。

       所以包含null的计算，结果都是null，用null除以0也是一样。数字除以0会发生错误，null除以0不会出错，结果是null。

        

       在oracle中，from子句是必须的。若没有表可以使用临时表DUAL。

       在排序中默认把null看做是无限大。

        

        非空字段与空值字段做“||”时，空值转为字符“”，合并后的字符类型为varchar2。

        

        is null和is not null 来判断是否为空。in 和not in无法取出null数据。

        not in 遇到空值，如果子查询结果集中有空值，那么主查询结果集也是空值。

        

4、去空

5、count(*)返回表中行的总数，包括重复行和空值行。

     count(1)和count(*)的结果一样，都包含对空值的统计。

     count(column)返回非空行。

        

6、sum、avg会将null排除在外，但count不会排除null。

        

7、group by

        a、group by不能使用别名。

        

        b、group by子句的聚合结果是无序的。

        c、where子句不能使用聚合函数，而使用having。having要在group by后面。

        

        d、当 group by后的列包含null时，也会将null作为一组。

        

        e、使用group by子句时，select子句中不能出现聚合键之外的列。

8、聚合函数可以在select、having、order by句中使用。

     where子句指定行所对应的条件，having子句指定组所对应的条件，在where和having都可以使用的条件，最好写在where子句中，因为where子句处理速度快。

9、order by

        a、order by 缺省默认升序，desc降序。有null时默认null无限大。

        

        

        b、order by子句可以指定多个排序键。规则是先使用左边的，如果该列存在相同值的话，在接着参考右边的列。

        c、order by可以使用别名，group by不能使用别名。因为select子句的执行顺序是group by之后，order by之前。

        

        d、order by可以使用聚合函数和select子句中未使用的列。

        

        e、order by可以使用列编号。

10、insert

        a、insert 原则上一次插入一行，但有的RDBMS支持一次插入多行。将多条子句通过逗号隔开。

            形如insert into A values((1,2,3),(1,2,3),(1,2,3));

            上面一次插入多行不适用于oracle，oracle使用insert来插入多行。

     

        b、插入默认值

            默认值的使用方法通常有两种，显式和隐式两种。

            显式是在values子句中指定default关键字。在创建表的时候设置了default值，插入的时候只是输入default就可以。

            隐式是插入的values子句中不使用default关键词，只是在values中省略默认值的列就行。即省略列和值。

        如果省略的是没有设置默认值的列，该列的值就被设为null。若该列设置了not null约束，就会出错。

11、delete

        drop table会将表完全删除，delete语句会删除数据，但表结构会留下来。删除全部 数据用delete from table；

        delete语句删除的是对象不是表和列，而是行。

        delete与truncate：truncate是删除表中的所有数据，而不能像delete一样通过where按指定条件删除。因此truncate处理速度比delete速度快得多。