数据库基本操作小摘（代码由mysql-essential-5.1.50-win32测试通过）

[此文于2010年8月22日被重新编辑]

 

      本篇所有的内容都为本人在学习SQL中的学习札记，其中参考了《SQL Cookbook》与《SQL HACKS》这两本书，其余的资源来自于网络，通过学习后发现其实SQL中的很多技巧都是关键字的多种组合，所以了解关键字的用法才是重中之重。以下知识点只是起到一个摘抄作用，如果想了解更多详细的内容可以去查看这两本书，书中包含很多不同数据库的内置函数（本文没有涉及此方面），想要深入的学习只能多加应用才是。

[插入、更新与删除]

#创建数据库：CREATE DATABASE menagerie

#插入默认值：CREATE TABLE D(id integer default 0)

#查看当前状态： STATUS

#切换字符集：SET character_set_connection=gb2312     （参考更多：http://chenboqiang.blog.51cto.com/1068057/268639）

• #从一个表向另外的表中复制行：

• INSERT INTO dept1（deptno,dname,loc）
   SELECT deptno,dname,loc
     FROM dep2
     WHERE loc IN(‘New’,‘BOS’,)
  

• #复制表定义：CREATE TABLE dep_2 LIKE dept 或者

• CREATE TABLE dept_2
  AS
  SELECT *
     FROM dept
     WHERE 1=0
  
  #一次向多个表插入记录：目前(2007.10.1)Oracle和DB2支持

# 阻止对某列插入：

CREATE VIEW new_emps as
SELECT empno,ename,job
   FROM emp

#表中编辑记录，保留原始内容不变：

SELECT depno,
       ename,
       sal as orig_sal
       sal*.10 as new_sal
       FROM emp
       WHERE deptno=20
       ORDER BY 1,5

#当相应行存在时更新：

UPDATE emp
   SET sal=sal*1.20
   WHERE empno in(SELECT empno FROM emp_bonus)

#用其他表中的值更新

UPDATE emp e SET (e.sla,e.comm)=(SELECT ns.sal,ns.sal/2
                                 FROM new_sal ns
                                 WHERE ns.deptno=e.deptno)
WHERE EXISTS( SELECT NULL
              FROM  new_sal ns
              WHERE ns.deptno=e.deptno)

#合并记录：

MERGE INTO emp_commission ec
USING (SELECT * FROM emp) emp
   ON(ec.empno=emp.empno)
WHEN matched THEN
   UPDATE SET ec.comm=1000
   DELETE WHRER(sal<2000)
WHEN NOT matched THEN
   INSERT(ec.empno,ec.ename,ec.deptno,ec.coomm)
   VALUES(ec.empno,ec.ename,ec.deptno,ec.coomm)

[检索记录]

#连接列值：

select concat(ename, ' WORKS AS A ',job) as msg
     from
     where deptno=10

#限制返回行数：

select *
      from emp limit 5

#查找空值：

select *
     from emp
     where comm is null

#按模式搜索：

1select ename, job
    from emp
    where deptno in (10,20)
    and (ename like '%I%' or job like '%ER')

[查询结果排序]

#以指定次序返回结果

select ename,job,sal
    from emp
    where deptno = 10
    order by sal asc

    （ asc升序  desc降序，order by优先次序从左到右）

#按字串排序：substr或者substring函数

[操作多个表]

#记录集的叠加：UNION 操作符用于合并两个或多个 SELECT 语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

#组合相关行：

select e.ename, d.loc
      from emp e inner join dept d
        on (e.deptno = d.deptno)
     where e.deptno = 10

#在一个表中查找与其他表不匹配的记录：

select e.ename, e.deptno as emp_deptno, d.*
      from dept d left join emp e
        on (d.deptno = e.deptno)

[使用数字]

#平均值：avg

#最大最小值：max, min

#总和：sum

#行数：count(*)

#某列值非NULL的个数：count(comm)

#乘机：sum over

#计算不包含最大值和最小值的均值：

select avg(sal)
    from emp
    where sal not in (
      (select min(sal) from emp),
      (select max(sal) from emp)
    )

未完待续……

 

参考文献：《SQL Cookbook》——清华大学出版社，Antbony Molinaro著，王强&王晓娟等译——ISBN 978-7-302-15493-8

               《SQL HACKS》——清华大学出版社，Andrew Cumming&Gordon Russell著，张长富等译——ISBN 978-7-302-17484-4