sql不基础操作

历史

Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE.
Data Definition Language (DDL): Schema definition.
Data Control Language (DCL): Security, access controls.
SQL-92 is the minimum that a DBMS（data bas manipulate system，数据库操作系统） has to support in order to claim they support SQL

Aggregates

COUNT() , AVG(), SUM() , FIRST(), LAST(),MAX() , MIN(), 这之前的都是基础操作不赘述。 GROUP BY , HAVING 也属于Aggregates 但用起来不太基础

一个错误例子

这是因为avg_gpa需要先被 calculate，所以不能用作 where里面的比较，可以理解为where里面先执行。
应该这么写。

SELECT AVG(s.gpa) AS avg_gpa, e.cid
FROM enrolled AS e, student AS s
WHERE e.sid = s.sid
GROUP BY e.cid
HAVING avg_gpa > 3.9;

String Operations

The SQL standard says that strings are case sensitive and single-quotes only.
不同数据库对双引号和单引号string的敏感度，

不同数据库对string拼接的不同，

NESTED QUERIES

ALL→ Must satisfy expression for all rows in subquery
ANY→ Must satisfy expression for at least one row in sub-query.
IN→ Equivalent to ‘=ANY()’ .
EXISTS→ At least one row is returned.

例子找到所有学生名字在 ‘15-445’里面

SELECT name FROM student
WHERE sid = ANY(
SELECT sid FROM enrolled
WHERE cid = '15-4'

错误例子找到最高的学生id，这个学生至少enroll一门课。

有趣的是，上面的操作在 SQL-92不行. 但在SQLite里面可以, 在Postgres or MySQL不行 (v5.7 with strict mode).
底下是正确操作

1
2
3

SELECT sid, name FROM student
WHERE sid => ALL(
SELECT sid FROM enrolled)

或者

1
2
3

SELECT sid, name FROM student
WHERE sid IN (
SELECT MAX(sid) FROM enrolled)

更或者

SELECT sid, name FROM student
WHERE sid IN (
SELECT sid FROM enrolled
ORDER BY sid DESC LIMIT 1)

例子找到所有的课，没有一个学生注册的课。

SELECT * FROM course
WHERE NOT EXISTS(
SELECT * FROM enrolled
WHERE course.cid = enrolled.cid
)

触发器，trigger

触发器，只能在commind line里面操作。

触发器例子

这个trigger(my_trigger2)会在往employee插入值之前，先判定一下，然后往trigger_test 这张表里插入一些值。

DELIMITER $$   这个符号是为了在command line 里面多输入几行，因为trigger方程中间会有; 
CREATE
TRIGGER my_trigger2 BEFORE(AFTER也可以) INSERT(delete,update 也可以)
ON employee
FOR EACH ROW BEGIN
    IF NEW.sex ='m' THEN
          INSERT INTO trigger_test VALUES('add male employee')
     ELSEIF 另外一个条件 THEN
            触发条件后的执行
      ELSE 其他所有的情况
      END IF ;
INSERT INTO trigger_test VALUES(NEW.first_name);
END$$
DELIMITER ;

window function

例子，找到每门课成绩最高的学生

ACS是升序，DECS是降序

table expression

例子找到最高的id的学生，这个学生必须注册了至少一门课程

Find student record with the highest id that is enrolled in at least one course

WITH cteSource (maxId) AS (
SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student, cteSource
WHERE student.sid = cteSource.maxId ;

例子

其他 pring 1-10

WITH RECURSIVE cteSource (counter) AS (
(SELECT 1)
UNION ALL
(SELECT counter + 1 FROM cteSource
WHERE counter < 10)
)
SELECT * FROM cteSource;

1 .on delete set null和on delete cascade 主外键关联删除会有用处
2 . join有关详细操作参考 https://www.cnblogs.com/fudashi/p/7491039.html。

3 . UNION 可以一起显示俩个select的结果，上下拼接
4 regex: _单个任意字符， %任意长度任意字符。
5 不同DBMS的获取现在时间的方式不一样，请具体查阅。
6 mysql 会有traditional 模式和 strict模式， strict模式更严格。

历史