sql 常用经典实例_利用视图计算超过3000元的女雇员的平均工资

一、嵌套查询

• 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

方法一：select name,age from sc where cs<>'计算机科学系'  and age < ANY(select age from sc where cs='计算机科学系')；

方法二：select name,age from sc where cs<>'计算机科学系'  and age < ALL(select age from sc where cs='计算机科学系')；

方法三：select name,age from sc where cs<>'计算机科学系'  and age < select min(age) from sc where cs='计算机科学系'；

• 查询所有选修了1号课程的学生姓名

SELECT Sname
FROM Student
WHERE EXISTS
    (SELECT *
     FROM SC
     WHERE Sno=Student.Sno AND Cno='1');

• 查询没有选修1号课程的学生姓名

SELECT Sname
FROM Student
WHERE NOT EXISTS
    (SELECT *
     FROM SC
     WHERE Sno=Student.Sno AND Cno='1');

• 查询选修了全部课程的学生姓名

  由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。

SELECT Sname
FROM Student
WHERE NOT EXISTS
    (SELECT *
     FROM Course
     WHERE NOT EXISTS
        (SELECT *
         FROM SC
         WHERE Sno=Student.Sno
            AND Cno=Course.Cno));

• 找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno,Cno
FROM SC X
WHERE Grade >=(SELECT AVG(Grade)
               FROM SC y
               WHERE y.Sno=x.Sno);

• 查询与“刘晨”在同一个系学习的学生

  SELECT Sno,Sname,Sdept
  FROM Student
  WHERE Sdept IN
      (SELECT Sdept
       FROM Student
       WHERE Sname='刘晨');

• 关键字

• distinct
• 列出每个部门最高薪水的结果

SELECT DEPT, MAX(SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
查询结果如下：
      DEPT  MAXIMUM 
      开发部 2500
      设计部 2600
      销售部 3500

• sum

• 查询每个部门的总的薪水数
  SELECT DEPT, sum( SALARY ) AS total
  FROM STAFF
  GROUP BY DEPT
  查询结果如下：
  DEPT  total 
  开发部 4500
  设计部 7000
  销售部 9600

• group by

• 查询公司2010年入职的各个部门每个级别里的最高薪水
  SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM
  FROM staff
  WHERE HIREDATE > '2010-01-01'
  GROUP BY DEPT, EDLEVEL
  ORDER BY DEPT, EDLEVEL
  查询结果如下：
    DEPT  EDLEVEL  MAXIMUM 
        设计部 4 2300
        设计部 5 2600
        销售部 5 3000
        销售部 7 3500

• having count(*)

• 寻找雇员数超过2个的部门的最高和最低薪水：
  SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
  FROM staff
  GROUP BY DEPT
  HAVING COUNT( * ) >2
  ORDER BY DEPT
  查询结果如下：
    DEPT  MAXIMUM  MINIMUM 
        设计部 2600 2100
        销售部 3500 3000

• HAVING AVG( SALARY )

• 寻找雇员平均工资大于3000的部门的最高和最低薪水：
  SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
  FROM staff
  GROUP BY DEPT
  HAVING AVG( SALARY ) >3000
  ORDER BY DEPT
  查询结果如下：
    DEPT  MAXIMUM  MINIMUM 
        销售部 3500 3000

以上参考：https://www.cnblogs.com/xp796/p/5262187.html

二、理论知识

• delete和truncate的区别和联系

1）是否有日志，是否可回滚：delete 过程如果出现错误，事务是可以回滚的，但是truncate操作时是不会造成回滚的，因此更需要小心，所以才需要授予drop的权限

2）truncate执行的时候要比delete性能高很多：因为delete需要一行一行的去删除数据，truncate通过删除表，然后再重建实现的

3）范围：delete的使用范围更广，因为它可以删除符合条件的数据行，而不一定是整体；但是truncate只能删除整体

• group by 和having

0）HAVING子句：对分组结果进行过滤

1）GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

2）必须在group by子句之前指定where子句

3）可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。因此，在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE（唯一的差别是WHERE过滤行，HAVING过滤组）HAVING支持所有WHERE操作符。

4）例子

having子语句与where子语句区别：

　　where子句在分组前对记录进行过滤；

　　having子句在分组后对记录进行过滤

mysql> select salary,count(*) from salary_tab
    -> where salary>=2000
    -> group by salary
    -> having count(*)>=0;

• GROUP_CONCAT()

1）函数的值等于属于一个组的指定列的所有值，以逗号隔开，并且以字符串表示。

2）对于每个球队，得到其编号和所有球员的编号

select teamno,group_concat(playerno)
    -> from MATCHES
    -> group by teamno;
+--------+------------------------+
| teamno | group_concat(playerno) |
+--------+------------------------+
|      1 | 6,6,6,44,83,2,57,8     |
|      2 | 27,104,112,112,8       |
+--------+------------------------+

得到所有的罚款编号列表

mysql> select group_concat(paymentno)
    -> from PENALTIES;
+-------------------------+
| group_concat(paymentno) |
+-------------------------+
| 1,2,3,4,5,6,7,8         |
+-------------------------+

• MYSQL性能优化的最佳经验

1)某些查询语句会让MySQL不使用缓存

上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用

2)EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

3)当只要一行数据时使用 LIMIT 1

4)为搜索字段建索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧

5)避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载

6)永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。

就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。

7)使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR

8)尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL

不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂

9)把IP地址存成 UNSIGNED INT

10)拆分大的 DELETE 或 INSERT 语句

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

• 锁

MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

• InnoDB锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

1、事务（Transaction）及其ACID属性 
事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

• 原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2、并发事务带来的问题

“脏读”、“不可重复读”和“幻读”

3、事务隔离级别

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

4、InnoDB实现了以下两种类型的行锁。

• 共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
• 排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁

事务可以通过以下语句显式给记录集加共享锁或排他锁：

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
• 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

参考：https://www.cnblogs.com/geaozhang/p/6745147.html