sql不基础操作

历史

  1. Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE.
  2. Data Definition Language (DDL): Schema definition.
  3. 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里面先执行。
应该这么写。

1
2
3
4
5
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’里面

1
2
3
4
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)

更或者
1
2
3
4
SELECT sid, name FROM student
WHERE sid IN (
SELECT sid FROM enrolled
ORDER BY sid DESC LIMIT 1)

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

1
2
3
4
5
SELECT * FROM course
WHERE NOT EXISTS(
SELECT * FROM enrolled
WHERE course.cid = enrolled.cid
)

触发器,trigger

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

触发器例子

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

1
2
3
4
5
WITH cteSource (maxId) AS (
SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student, cteSource
WHERE student.sid = cteSource.maxId ;

例子

其他 pring 1-10

1
2
3
4
5
6
7
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模式更严格。