SQL知识点总结

1.where与having的区别：

WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。   

HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

2.相同字段值显示一次，则使用SELECT DISTINCT可去除重复值

3.方法一：使用NOT IN选出在employees但不在dept_manager中的emp_no记录

会用not in，in，会用子查询

4 由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

group by 后面一定要接having,而不是where

5.left join 可以接多个表

a

left join b

on ...a.s=b.s

left join c

on  b.p=c.p

6.group by 可以同时对两个字段。例如

group by d.dept_no,t.title

7.在SQLite数据库中，可以用 "EXPLAIN" 关键字或 "EXPLAIN QUERY PLAN" 短语，用于描述表的细节，

获取select * from employees对应的执行计划

explain显示了MySQL如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句。

8.CONCAT只支持MySQL、SQL Server、Oracle等数据库，SQLite数据库连接字符串要用连接符号"||"

9.last_update timestamp not null default (datetime('now','localtime')),

10.批量插入：

方法一：利用VALUES(value1, value2, ...), (value1, value2, ...), ...(value1, value2, ...),

11.对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用

insert or ignore into actor  values(3,'ED','CHASE','2006-02-15 12:34:33');

12.先select再insert

insert into actor_name select first_name,last_name from actor ;

13.创建索引

先用 CREATE UNIQUE INDEX ... ON ... 对first_name创建唯一索引值，再用 CREATE INDEX ... ON ... 对last_name创建普通索引值

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

14.创建视图

方法一：注意 CREATE VIEW ... AS ... 的 AS 是创建视图语法中的一部分，而后面的两个 AS 只是为字段创建别名

1 2 3

CREATE VIEW actor_name_view AS SELECT first_name AS fist_name_v, last_name AS last_name_v FROM actor

方法二：直接在视图名的后面用小括号创建视图中的字段名

1 2

CREATE VIEW actor_name_view (fist_name_v, last_name_v) AS SELECT first_name, last_name FROM actor 15.强制使用索引: 使用 INDEXED BY 语句进行强制索引查询 select * from salaries indexed by idx_emp_no   where emp_no=10005  16.在表后面增加一列 用 ALTER TABLE ... ADD ... 语句可以向已存在的表插入新字段，并且能够与创建表时一样，在字段名和数据类型后加入NOT NULL、DEFAULT等限定 17.构造一个触发器 构造触发器时注意以下几点： 1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER 2、触发器执行的内容写出 BEGIN与END 之间 3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录 CREATE TRIGGER audit_log AFTER INSERT ON employees_test BEGIN      INSERT INTO audit VALUES (NEW.ID, NEW.NAME); END; 18. 题目描述 删除emp_no重复的记录，只保留最小的id对应的记录。 CREATE TABLE IF NOT EXISTS titles_test ( id int(11) not null primary key, emp_no int(11) NOT NULL, title varchar(50) NOT NULL, from_date date NOT NULL, to_date date DEFAULT NULL); insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'), ('2', '10002', 'Staff', '1996-08-03', '9999-01-01'), ('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'), ('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'), ('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'), ('6', '10002', 'Staff', '1996-08-03', '9999-01-01'), ('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'); delete from titles_test  where id not in (select min(id) from titles_test group by emp_no) 19.更新表 update table_name set ..=.., ..=.., 另外要注意若干列 to_date = NULL 和 from_date = '2001-01-01' 之间只能用逗号连接，切勿用 AND 连接。 set后面接的是= 20.replace两种用法 1.REPLACE INTO titles_test VALUES 2. 运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过。 update titles_test  set emp_no=replace(emp_no,10001,10005) where id=5 21. RENAME TO 语句更改表名的基本运用 22.在audit表上创建外键约束，其emp_no对应employees_test表的主键id foreign key(EMP_no) references employees_test(ID) 23. substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。 24. group_concat(X,Y) 10007,10008,10010 SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y） 25 case when then else end 语句 select e.emp_no,e.first_name,e.last_name, eb.btype,s.salary, (     case eb.btype     when 1 Then s.salary*0.1     when 2 Then s.salary*0.2     else s.salary*0.3     end ) as bonus from employees as e, salaries as s , emp_bonus as eb where e.emp_no=s.emp_no and  e.emp_no=eb.emp_no and s.to_date='9999-01-01'