SQL

sql介绍    SQL被称为结构化查询语言(structured query language)    SQL是操作和检索关系型数据库的标准语言        是一个标准，所有的数据库生产厂商需要遵循，但不同的数据库厂商具有不同的特性。    sql分类        DDL，Data Definition Language,数据定义语言 (结构：create创建，drop移除，alter修改)        DML，Data Manipulation Language,数据操作语言 (数据：insert录入，delete删除，update 更新)        DQL，Data Queries Language,数据查询语言 (查询：select)        DTL，Data Transaction Language,事务处理语言 (事务： )        DCL，Data Control Language,数据控制语言 (权限： grant revoke)SQL 语法    SQL 对大小写不敏感.    字符串数据内容,mssql,mysql默认不区分大小写,oracle区分    mysql,oracle要求在每条 SQL 命令的末端使用分号。    单行注释:--     注意,--后要有一个空格,mysql才会识别    多行注释:/* */    字符串:mssql,oracle只能用单引号,mysql可以用单引号和双引号    转义字符:mysql \,  mssql和oracle在语句末尾使用 escape 转义字符 规定自定义转义字符    区分关键字:mssql用方括号[] mysql用重音符` oracle用双引号"(oracle尽量避免关键字)    字符串连接:mssql:str1+str2  mysql:concat(str1,str2)  oracle:||,concat(str1,str2)数据类型    字符串        char(n), 固定长度，声明可以允许n长度的字符串。例如：char(5) ,录入数据 "abc" , 长度为5 (右侧默认填充空格)        varchar(n), 可变长度，声明可以允许n长度的字符串。例如：varchar(5) ,录入数据 "abc" ,长度为3.(oracle有varchar2)    数字        int ,整形        decimal(m,d) ,双精度，m为整个数字的个数，d为小数的位数。例如：double(5,2)  最大值：999.99        oracle双精度为number(m,d),oracle中的number为可变长数值数据,可以省略m和d    日期        mssql            smalldatetime   日期时间(4字节,没有毫秒)            datatime        日期时间(8字节,有毫秒)            timestamp       时间戳,不是实际时间,不能手动赋值.用来判断数据是否发生过更改        mysql            date            日期            time            时间            datatime        日期时间            timestamp       时间戳,是当前时间,可以手动赋值        oracle            date            日期时间(精确到秒)            timestamp       时间戳,是当前时间,可以手动赋值数据库操作(除oracle)    创建库  create database 数据库名    使用库  use 数据库名    删除库  drop database 数据库名表操作    创建表  create table 表名(列描述1,列描述2...)        列描述            格式：列名称 数据类型 [约束]            多个列描述之间使用逗号","分隔，最后一个没有逗号        例如：            create table book(                id varchar(32),                title varchar(50),                price double(5,2)            );            注意:要先use 数据库;    删除表  drop table 表名    修改表  alter table 表名        添加列      add 列描述        添加约束    add [constraint 约束名称] 约束语句约束    添加约束的三种方式        在创建表时,列描述中添加     [constraint 约束名称] 约束语句        在创建表时,最后添加(列同级) [constraint 约束名称] 约束语句        在修改表时添加              alter table add [constraint 约束名称] 约束语句        注意:            联合主键(多个列组合起来作为主键)不能使用第一种方式            非空约束,默认约束,标识列 只能使用第一种方式            mysql的外键约束不能用第一种方式            mysql没有检查约束,oracle没有标志列,有序列.    主键约束        主键：表中的一个或多个字段，它的值用于惟一地标识表中的某一条记录        要求：不能重复，不能为null(唯一约束和非空约束)        语法    primary key [(列名,...)]    外键约束        外键：在从表添加一个字段，对主表主键的数据进行引用。主键所在的表称为主表,外键所在的表称为从表        要求：数据必须是主表主键中存在的数据,也可以为null        类型：必须与主表主键的类型一致        语法    [foreign key(列名)] references 主表名(引用列名)        注意                添加数据时,从表不能添加主表中不存在的数据,可以为null            删除数据时,主表不能删除被外键引用着的数据    唯一约束        语法    unique[(列名)]    非空约束        语法    not null    检查约束        mssql和oracle有,mysql没有            语法    check(表达式)            例      check(列名='男' or 列名='女')    默认约束(默认值)        语法    default 值        注意            oracle中默认约束应在其它约束前    标识列(自动增长列)        mssql   identity[(初始值,增量)]        mysql   auto_increment      --不能在这设置初始值,可以通过ALTER TABLE tbl AUTO_INCREMENT = 100设置        oracle  没有标识列,可使用序列        注意            标识列的类型必须是整形,mssql不能手动赋值,mysql可以手动赋值    启用/禁用/删除约束        mssql            启用约束    check constraint 约束名称[,...]            禁用约束    nocheck constraint 约束名称[,...]            删除约束    drop constraint 约束名称[,...]数据操作    录入数据    insert into 表名[(字段名称列表)] values(数据列表)    更新数据    update 表名 set 字段=值,... [where 条件]    删除数据    delete [from] 表名 [where 条件];    --mysql必须加from    录入多条数据(使用子查询方式)        普通            insert into 表名[(字段名称列表)]                 select 数据列表 union                select 数据列表        oracle使用序列时(序列不能和union一块使用),例:            insert into t_user(id,username)                select my_seq.nextVal,u.* from (                        select '杭三' from dual union                        select '杭三1' from dual                    ) u    删除数据同时重置标识列的初始值  truncate table 表名  --对oracle没用,因为oracle没有标识列,使用的是序列查询数据    基本语法:        SELECT [ ALL | DISTINCT ] <select_list>        [ FROM table_source ]         [ WHERE search_condition ]         [ GROUP BY group_by_expression]         [ HAVING search_condition]         [ ORDER BY order_expression [ ASC | DESC ] ]         注意:            mssql有top n语法,mysql有limit语法,oracle的筛选前几条记录通过rownum伪列            mssql的having中不能使用列别名            oracle中select子句后必须要有from子句,当没有要查询的表时,可以使用伪表dual,如select 3+2 from dual;        说明:            ALL                 指定在结果集中可以包含重复行(默认)            DISTINCT            指定在结果集中只能包含唯一行。对于 DISTINCT 关键字来说，Null 值是相等的。distinct 作用于后面所有多列的组合            select_list         可以是 列名,*号 等,还可以是 表名.列名 或 表名.*            table_source        可以是 表,视图 等            group_by_expression 分组列            search_condition    分组条件            order_expression    排序列            ASC                 升序排列(默认)            DESC                降序排列    对列使用别名        select 列 [as] 别名 from 表        注意:别名加不加引号都行,加引号可以包含关键字和空格    对表使用别名        select 列 from 表 [as] 别名    group by子句(分组)        语法    group by 列名        注意            select子句中查询的列如果不在聚合函数中,就必须出现在group by子句中    having子句(分组条件)        语法    group by 列名 having 条件        注意            分组完判断组是否满足条件,满足的显示,不满足的不显示            和where子句的区别:where子句中不能使用组函数            mssql和oracle的having中不能使用列别名    order by子句(排序)        语法    order by 列名 [asc|desc]        注意    也可以按顺序将列编号        例      select a,b,c from 表名 order by 3 desc,2 desc    多表联接查询(一次查询多张表中的数据)        sql99            语法    select 列名,... from 表1 [联接类型] join 表2 on 链接条件 [where 查询条件]            联接条件                内联接      inner   --返回在另一张表有对应记录的记录                左外联接    left    --返回左表所有记录,和右表在左表中有对应记录的记录                右外联接    right   --返回右表所有记录,和左表在右表中有对应记录的记录                交叉联接    cross   --返回两张表的笛卡尔积,总记录数=左表记录数*右表记录数.不用写on和联接条件            例  select 列名 form 表1 inner join 表2 on 表1.列名=表2.列名            注意                内联接的另一种写法 select 列名 from 表1,表2 where 表1.列名=表2.列名                交叉联接的另一种写法 select 列名 from 表1,表2                交叉联接不用写on和联接条件                mssql支持完全外部联接full,返回两张表所有记录        oracle            内联接(等值查询和不等值查询),语法同sql99            左外连接: where A=B(+)  包含左表中A列为null的内容 ,例: where e.deptno=d.deptno(+);            右外连接: where A(+)=B  包含右表中B列为null的内容 ,例: where e.deptno(+)=d.deptno;            自连接:通过表的别名，将同一张表视为多张表进行查询 ,例: select e.ename 员工姓名,b.ename 老板姓名 from emp e,emp b where e.mgr=b.empno;            层次查询:适合结果为树状结构的查询,如查询员工及相应的老板,老板为树状结构.层次查询适合单张表查询,不会产生笛卡尔积                select level,empno,ename,mgr from emp       /*level为伪列,表明结果位于层次查询的第几层*/                connect by prior empno=mgr      /*connect by表明为层次查询, prior为关键字,表示上一层. 条件为:上一层的员工号=下一层的老板号*/                start with mgr is null          /*层次查询需要有一个开始节点 通过start with指定*/            注意:                自连接会造成笛卡尔积,效率很低,不适合操作大表                层次查询实质为单表查询,适合结果为树状结构的查询,不会产生笛卡尔积,效率高,但显示结果不直观                自连接是一种编程技巧,在别的数据库中也可以使用    子查询        当查询是一个操作的一部分时，称之为子查询        例,查询工资比SCOTT高的员工信息            select * from emp where sal > (select sal from emp where ename='SCOTT');        注意            可以在主查询的where select having from后面放置子查询,不可以在group by后面放置子查询                mysql和mssql的子查询不能放在from后面                select 后面的子查询只能返回一条记录                from 后面的子查询相当于把 子查询的结果当作一张表查询                使用子查询时,所有的列别名不能在where后使用            单行子查询只能使用单行操作符(=,>,<等),多行子查询只能使用多行操作符(in,any,all)                单行子查询:结果为一条记录                多行子查询:结果为多条记录                多行比较操作符                     in 等于列表中的任何一个 (in相当于=any, not in相当于!=all)                    any 和子查询返回的任意一个值比较 where sal > any (select sal from emp) 相当于 where sal > (select min(sal) from emp)                    all 和子查询返回的所有值比较 where sal > all (select sal from emp) 相当于 where sal > (select max(sal) from emp)                    注意:                        使用含有null的表达式不能使用not in 和 all,结果永远为false.                        可以在子查询中添加条件先过滤null值 where sal > all (select sal from emp where sal is not null)            一般先执行子查询,再执行主查询.但相关子查询例外        相关子查询            将主查询中的某个值,作为参数传递给子查询            例:找到员工表中薪水大于本部门平均薪水的员工            普通解法                select e.empno,e.ename,e.sal,d.avgsal                from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d                where e.deptno=d.deptno and e.sal>d.avgsal            相关子查询                select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal                from emp e                where sal>(select avg(sal) from emp where deptno=e.deptno)            在相关子查询中,需要给主查询的表起一个别名,子查询中的e.deptno是从主查询自动传递进去的    集合运算        对多个查询结果集进行处理,取并集,交集或差集            并集(union/union all) union对相交部分取一次,union all对相交部分取两次            交集(intersect) 在两个结果集中都存在的数据            差集(minus) 在第一个结果集中存在,在第二个结果集中不存在的数据        注意:            mysql只支持union/union all            mssql中的差集关键字为except        例: select * from emp where deptno=10            union            select * from emp where deptno=20;        注意事项:            1. select语句中参数类型和个数要一致            2. 可以使用括号改变集合执行的顺序            3. 如果有order by子句,必须放到最后一句查询语句后面            4. 集合运算采用第一个语句的表头作为表头    汇总        mssql            位置    select语句最后            语法    compute {arg|count|max|min|sum}(列名) [by 列名[,...]]            例      select classes,[count] from users order by classes compute sum([count]) by classes            注意                compute子句中指定的列必须包含在select子句中,且不能使用别名                compute by子句对指定列进行分组,然后对每一组进行汇总                compute by子句前必须先使用order by子句                compute [by]子句不能同时使用into子句        oracle            位置    group by子句后,是group by子句的增强            语法    group by rollup(分组列1,分组列2,...);            例      select deptno,job,sum(sal) from emp group by rollup(deptno,job);            相当于  select deptno,job,sum(sal) from emp group by deptno,job                    union                    select deptno,to_char(null),sum(sal) from emp group by deptno                    union                    select to_number(null),to_char(null),sum(sal) from emp    分页        mssql: 利用top语句            select top 每页显示个数 * from t_user where id not in (select top 开始索引 id from t_user order by id asc ) order by id asc        mysql: 利用limit语句            select ... from ... limit 开始索引,每页显示个数        oracle: 利用rowNum伪列            select *            from (select rownum r,e1.*                from (select * from emp order by sal desc) e1                where rownum<=开始索引+每页显示个数) e2            where r>=开始索引    自我理解        select语句的执行顺序从前往后(除top语句)        查询所有结果,判断where条件,筛选出符合条件的结果,然后进行分组,        判断是否符合having条件,满足的留下,然后进行排序,最后返回结果关键字    and关键字        where 条件1 and 条件2    or关键字        where 条件1 or 条件2        优先级: and 先执行,or 后执行    not关键字        取反 where is not null    like关键字        where 列名 like '李%'   --返回所有姓李开头的人        说明:            %代表任意长度的字符串,_代表任意一个字符            mssql中[]代表某个字符的取值范围,[^]与[]相反    between关键字        where 列名 between 200 and 300        相当于 列名>=200 and 列名<=300    in关键字(not in)        where 列名 in ('值1','值2')        相当于 列名='值1' or 列名='值2'        注意:存在null时不能使用not in,可以使用in    null关键字        where 列名 is [not] null聚合函数(多行函数,组函数)    特点:对多行结果进行处理,返回一个结果    count([distinct|all] {*|列名})  求个数(使用列名统计会忽略该列为null的行)    avg([distinct|all] 列名)        求平均值    sum([distinct|all] 列名)        求和    max(列名)                       求最大值    min(列名)                       求最小值    例  select count(*) from 表名获得当前时间    mssql: getdate()    mysql: now()    oracle: sysdate 或 systimestamp        select to_char(sysdate,'yyyy-mm-dd hh24') from dual;sql中的null    1. 包含null的表达式结果都为null    2. a=null  和 a!=null 永远为false(或报错),不论a是不是null , 应使用 a is null 和 a is not null    3. 如果集合中含有null,不能使用not in,可以使用in. 例: where empno in (10,20,null),原因:not in相当于!=all (一个值!=null 永远为false) in相当于=any    4. 组函数会自动滤空(不会把空值计算在内,如sum,avg,count),可以使用count(相关函数(comm,0))来屏蔽多行函数的滤空功能    相关函数或关键字:        oracle:            nvl(a,b):如果a不为null,则返回a;如果a为null,则返回b        mssql:            isnull(a,b):如果a不为null,则返回a;如果a为null,则返回b        is null 或 is not null,不能用 a=null 或 a!=null    对于存在null值的正整数列降序排列,null值排到最后        oracle:            select * from emp order by nvl(comm,-1) desc;            或 select * from emp order by comm desc nulls last;表与表之间的关系    一个表中的数据，与另一个表中的数据，形成一种联系。    一对多        例子: 学生 和 图书,一个学生可以借多本书,一本书一段时间内只能被一个学生借走        处理一对多的思想: 通过主外键关系描述一对多        数据库关系描述: 在图书表中添加stu_id列(外键),与学生表的主键，形成主外键关系        查询数据: 两表联接 select s.name,b.name where t_stu s,t_book b where s.id=b.stu_id;        java中的关系描述:             在图书类中添加一个属性,类型为学生类            在学生类中添加一个属性,类型为集合,其中存放的元素类型为图书对象    多对多        例子: 学生 和 课程,一个学生可以学多门课程,一个课程可以同时被多个学生学习        处理多对多的思想: 将多对多关系 通过中间表 拆分两个1对多(中间表是从表)        数据库关系描述:            建立中间表t_stu_course,提供两个外键stu_id,course_id，分别与两个主表的主键，形成主外键关系            一般可以将两个外键声明成一个联合主键(不是必须的)        查询数据: 三表联接 select s.name,c.name where t_stu s,t_stu_course sc,t_course c where s.id=sc.stu_id and c.id=sc.course_id;        java中的关系描述:             对应于数据库,创建中间表t_stu_course对应的javabean学生课程类            在学生类中添加属性,类型为集合,其中存放的元素类型为 学生课程类            在学生课程类中添加属性,类型为集合,其中存放的元素类型为 课程类        jdbc查询示例            要求                查询用户的所有订单(订单中包含订单项,订单项中包含宠物信息)            关系                用户表 和 订单表的关系是一对多                订单表 和 订单项表的关系是一对多(订单项表 和 用户表的关系是多对多,订单表是中间表)                订单项表 和 宠物表之间的关系是一对一            代码                public List<Order> findOrderByUserId(String userId) {                    DataSource dataSource = JdbcUtils.getDataSource();                    //查询订单                    String sql = "select * from t_order where user_id=? order by createDate desc";                    List<Order> allOrder = DbUtils.queryList(dataSource, sql, Order.class, userId);                    //查询订单项                    for(Order order : allOrder){                        sql = "select * from t_orderItem where order_id=?";                        List<OrderItem> allOrderItem = DbUtils.queryList(dataSource, sql, OrderItem.class, order.getId());                        //查询订单项中的宠物                        for(OrderItem orderItem : allOrderItem){                            sql = "select top 1 t_pet.* from t_pet,t_orderItem where t_pet.id=t_orderItem.pet_id and t_orderItem.id=?";                            Pet pet = DbUtils.queryBean(dataSource, sql, Pet.class, orderItem.getId());                            orderItem.setPet(pet);                        }                        order.getAllOrderItem().addAll(allOrderItem);                    }                    return allOrder;                }    一对一        例子: 人 和 身份证        处理一对多的思想: 看作特殊的一对多事务    事务：逻辑的一组操作，各个单元，要么全部成功，要么全部不成功。    事务的特性：ACID        原子性：将事务当成一个整体看待，不能进行分割。        一致性：事务前后的数据保持一致。        隔离性：多个事务并发性        持久性：结果    事务并发访问问题        脏读(Drity Read)            某个事务已更新一份数据，但没有提交，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。        不可重复读(Non-repeatable read)            在一个事务的两次查询中数据不一致，因为两次查询过程中间另一个事务修改了原有的数据。        幻读(Phantom Read)            在一个事务的两次查询中数据不一致，因为两次查询过程中间另一个事务插入了新的数据。    数据库的隔离级别4种        read uncommitted,读未提交，一个事务可以读到另一个事务没有提交的数据。存在3种问题        read committed,读已提交，一个事务可以读到另一个事务已经提交的数据。解决了脏读问题，存在2种问题。        repeatable read ,可重复读，一个事务中重复的读到相同的数据。解决脏读、不可重复读问题，存在1种问题        serializable ,串行化，单事务。解决所有问题    对比        性能：read uncommitted > read committed > repeatable read > serializable        安全：read uncommitted < read committed < repeatable read < serializable    数据库的默认隔离级别        mysql 默认 repeatable read        oracle 默认 read committed    演示--数据的隔离级别(加深隔离级别的理解)        读未提交 -- read uncommitted            A 隔离级别 读未提交            AB 都开启事务            A 先查询            B 更新数据，B没有提交            A 再次查询 -- 读到B没有提交的数据        读已提交 -- read committed            A 隔离级别 读已提交            AB 都开启事务            A 先查询            B 更新数据，B没有提交            A 再次查询 -- 没有查询到B没有提交的数据            B 提交数据            A 再次查询 -- A读到了B提交的数据        可重复读 -- repeatable read            A 隔离级别 可重复读            AB 都开启事务            A 先查询            B 更新数据 ， B没有提交事务            A 再次查询 -- 没有查询到B没有提交的数据            B 提交事务            A 再次查询 -- A没有查询到B提交的数据 （A 当前事务所有的数据都是一致的）            A 提交事务 -- 当前事务已经结束            A 再次查询 -- 查询到B提交的数据，这将是一个新的事务。        串行化 -- serializable            A 隔离级别 串行化            AB 都开启事务            A 先查询            B 更新数据 -- 等待（阻塞）