1.SQL执行顺序

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)        ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list> 
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
select * from table
where  A #单个条件
where  A and B #和
where  A or B  #或
where  (A and B) or (C and D)  #优先级
where  in (" "," “," ”)  #指定针对某个列的多个可能值
where  A> 、>=、< 、<=、!=10000  #单个数值
where A between 10000 and 20000 #在某个范围内
where A like ‘%B%’  #模糊查询/搜索某种模式
where A not in、not like、not null  #否定
sign--符号，power--幂
select * from table
group by A  #将数据按组／维度划分
select * from table
group by A ,B #将数据按多维形式聚合
select A,count(*) as 客户数量 
from table
group by A  #统计数量
除了count，还有max，min，sum，avg等函数，也叫做聚合函数
select A,count(distinct Id) from table
group by A #按A分组（不重复）
select if(A like ‘%B%’,1,0) from table  #逻辑判断
4.想统计各个城市中有多少数据分析职位，其中，电商领域的职位有多少，在其中的占比
select city,
count(distinct Id),
count(distinct(if(industryField like ‘%电子商务%’,Id,null)))
from table
group by city  #占比，count函数对0,1都计数，因此用null
5.想找出各个城市，数据分析师岗位数量在500以上的城市有哪些
第一种，是使用having语句，它对聚合后的数据结果进行过滤。
WHERE 子句对被选择的列施加条件，而 HAVING 子句则对 GROUP BY 子句所产生的组施加条件。
在 SELECT 查询中，HAVING 子句必须紧随 GROUP BY 子句，并出现在 ORDER BY 子句（如果有的话）之前。
select city,count(distinct Id) from table
group by city 
having count(distinct Id) >= 500
上面的代码用了一个子查询，主要是因为在SQL Server中GROUP BY分组后，在SELECT后面显示的列里面只能显示分组的列，比如我们这里对城市分组了，那么子查询里面就只能显示城市列，显示不了其他列
第二种，是利用嵌套子查询。

子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用。

返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。

通常情况下子查询都与 SELECT 语句一起使用；但还可以用在 INSERT 语句中。INSERT 语句可以将子查询返回的数据插入到其他表中。子查询中选取的数据可以被任何字符、日期或者数值函数所修饰；也可以用在 UPDATE 语句中。当子查询同 UPDATE 一起使用的时候，既可以更新单个列，也可更新多个列；还可以同 DELETE 语句一起使用。
select * from (
    select city,count(distinct Id) as counts from table
    group by city ) as table1
where counts >= 500
select * from table
group by A  
order by B  #将数据升序
select * from table
group by A  
order by B desc  #将数据降序
多列排序，按 "省份" 升序排列，并按 "姓名" 列降序排列
ORDER BY 省份 ASC, 姓名 DESC;
GETDATE()函数用于返回当前数据库系统的日期和时间
SELECT GETDATE()
GETUTCDATE()函数返回当前UTC(世界标准时间)日期值，即格林尼治时间（GMT） 
SELECT GETUTCDATE()
YEAR()函数以int数据类型的格式返回特定日期的年度信息
SELECT YEAR(GETDATE())
select now()  #当前的系统时间，精确到秒
select date(now())   #当前日期
week函数获得当前第几周，month函数获得当前第几个月。其余还包括，quarter，year，day，hour，minute
时间加减法
select date_add(date(now()) ,interval 1 day)
改变1为负数，达到减法的目的
求两个时间的间隔
datediff(date1,date2)或者timediff(time1,time2)
substr（字符串，从哪里开始截，截取的长度）
薪水上限的开始位置是「-」位置往后推一位。截取长度是整个字符串减去「-」所在位置，刚好是后半段我们需要的内容，不过这个内容是包含「K」的，所以最后结果还得再减去1。
select left(salary,locate("k",salary)-1) as bottomsalary,
       substr(salary,locate("-",salary)+1,length(salary)-locate("-",salary)-1) as topsalary,
       salary from table
where salary not like "%以上%"
然后计算不同城市不同工作年限的平均薪资。
select city,workyear,avg((bottomsalary+topsalary)/2) as avgsalary from(
      select left(salary,locate("k",salary)-1) as bottomsalary,
            substr(salary,locate("-",salary)+1,length(salary)-locate("-",salary)-1) as topsalary,
            city,Id,workyear from table
       where salary not like "%以上%") as table1
group by city,workyear
order by city,avgsalary
inner join等价于join

left join返回A表所有的行，B表中所有数据对应横向放在A表后面，若A表中数据行较多，那么对于B表中的字段若在A表中不存在对应数据行，则用 NULL来填充。

full join只要A表和B表中有一个关键字存在匹配，则返回两个表的所有行，没有匹配的地方自动用NULL填充。

A Full Join B = A Left Join B + A Right Join B – A Inner Join B
select a.customer_id,a.rental_date,a.return_date,b.first_name,b.last_name 
from rental a inner join customer b
on  a.customer_id=b.customer_id;
select a.customer_id,a.rental_date,a.return_date,b.first_name,b.last_name 
from rental a,customer b
where  a.customer_id=b.customer_id;
11.仅在指定的列中插入数据
INSERT INTO Customers (姓名, 城市, 省份)
VALUES ('武松', '邢台', '河北省');
15.UNION运算符用于组合两个或更多SELECT语句的结果集
UNION使用前提：UNION中的每个SELECT语句必须具有相同的列数

这些列也必须具有相似的数据类型，每个SELECT语句中的列也必须以相同的顺序排列
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION 运算符选择一个不同的值，即不允许重复值
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
允许重复值
16.ALTER TABLE 语句用于在已有的表中添加、修改或删除列
若要向表中添加列，请使用以下语法：
ALTER TABLE table_name
ADD column_name datatype
若要删除表中的列，请使用以下语法：
ALTER TABLE table_name
DROP COLUMN column_name
若要更改表中列的数据类型，请使用以下语法：
ALTER TABLE table_name
CHANGE COLUMN column_name datatype
17.约束
约束是作用于数据表中列上的规则，用于限制表中数据的类型。约束的存在保证了数据库中数据的精确性和可靠性。
约束有列级和表级之分，列级约束作用于单一的列，而表级约束作用于整张数据表。
NOT NULL 约束：保证列中数据不能有 NULL 值
DEFAULT 约束：提供该列数据未指定时所采用的默认值
UNIQUE 约束：保证列中的所有数据各不相同
主键约束：唯一标识数据表中的行/记录
外键约束：唯一标识其他表中的一条行/记录
CHECK 约束：此约束保证列中的所有值满足某一条件
索引：用于在数据库中快速创建或检索数据
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
CREATE TABLE table_name 
column_name1 data_type(size) constraint_name, 
column_name2 data_type(size) constraint_name, 
column_name3 data_type(size) constraint_name, 
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;
18.索引
索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。简单说来，索引就是指向表中数据的指针。数据库的索引同书籍后面的索引非常相像。
索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。
CREATE INDEX index_name ON table_name;
单列索引基于单一的字段创建

CREATE INDEX index_name ON 
table_name (column_name);
唯一索引不止用于提升查询性能，还用于保证数据完整性。唯一索引不允许向表中插入任何重复值

CREATE UNIQUE INDEX index_name ON
table_name (column_name);
聚簇索引在表中两个或更多的列的基础上建立

CREATE INDEX index_name ON 
table_name (column1, column2);
创建单列索引还是聚簇索引，要看每次查询中，哪些列在作为过滤条件的 WHERE 子句中最常出现。
如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列，那么聚簇索引就是最好的选择。
19.事务
事务是在数据库上按照一定的逻辑顺序执行的任务序列，既可以由用户手动执行，也可以由某种数据库程序自动执行。
事务实际上就是对数据库的一个或者多个更改。当你在某张表上创建更新或者删除记录的时，你就已经在使用事务了。控制事务以保证数据完整性，并对数据库错误做出处理，对数据库来说非常重要。
事务的属性

事务具有以下四个标准属性，通常用缩略词 ACID 来表示：

原子性：保证任务中的所有操作都执行完毕；否则，事务会在出现错误时终止，并回滚之前所有操作到原始状态。

一致性：如果事务成功执行，则数据库的状态得到了进行了正确的转变。

隔离性：保证不同的事务相互独立、透明地执行。

持久性：即使出现系统故障，之前成功执行的事务的结果也会持久存在。
有四个命令用于控制事务：

COMMIT：提交更改；

ROLLBACK：回滚更改；

SAVE：在事务内部创建一系列可以 ROLLBACK 的还原点；

SET TRANSACTION：命名事务；

20.锁机制
共享锁：由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写。
排它锁：由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁，典型是mysql事务中的

锁的范围:

行锁: 对某行记录加上锁

表锁: 对整个表加上锁

这样组合起来就有,行级共享锁,表级共享锁,行级排他锁,表级排他锁。
加锁与解锁

LOCK TABLES tablename WRITE;

LOCK TABLES tablename READ;

Insert INTO assignment VALUES (1,7513,'0000-00-00',5),(1,7513,'2003-01-20',8.5);

UNLOCK TABLES;
不同锁的优缺点及选择

行级锁的优点及选择：

1）在很多线程请求不同记录时减少冲突锁。

2）事务回滚时减少改变数据。

3）使长时间对单独的一行记录加锁成为可能。
行级锁的缺点：

1）比页级锁和表级锁消耗更多的内存。

2）当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的所资源。

3）当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕。

4）使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了。

5）可以用应用程序级锁来代替行级锁，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它们是劝告锁（原文：These are advisory locks），因此只能用于安全可信的应用程序中。

6）对于 InnoDB 和 BDB 表，MySQL只有在指定用 LOCK TABLES 锁表时才使用表级锁。在这两种表中，建议最好不要使用 LOCK TABLES，因为 InnoDB 自动采用行级锁，BDB 用页级锁来保证事务的隔离。
表锁的优点及选择：

1）很多操作都是读表。

2）在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;DELETE FROM tbl_name WHERE unique_key_col=key_value;

3）SELECT 和 INSERT 语句并发的执行，但是只有很少的 UPDATE 和 DELETE 语句。

4）很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。
表锁的缺点：

1）一个客户端提交了一个需要长时间运行的 SELECT 操作。

2）其他客户端对同一个表提交了 UPDATE 操作，这个客户端就要等到 SELECT 完成了才能开始执行。

3）其他客户端也对同一个表提交了 SELECT 请求。由于 UPDATE的优先级高于 SELECT，所以 SELECT 就会先等到 UPDATE 完成了之后才开始执行，它也在等待第一个 SELECT操作。
21.常用字符处理函数
ASCII(字符串)

返回字符串表达式中最左侧的ASCII代码值
SELECT ASCII('S'),ASCII('SQL')
REPLACE与TRANSLATE的区别

https://blog.csdn.net/u013361361/article/details/21126279

UPPER(需要转换为大写的字符串)

把传递给它的字符串转换为大写
SELECT UPPER('sql学习之路')
主要有基于规则的优化（rules）、基于成本的优化（cost）

1）基于规则的优化

通常是基于表查询的优化，方法有：选择最有效率的表顺序，避免select *的出现，用TRUNCATE替代DELETE等

2）基于成本的优化

建立索引；

建立分区表；

视图、函数的适当使用；

返回更少数据（不必要的字段）；

数据库的表越少越好，表的字段越少越好；

适当的冗余，增加计算列，提高数据库的运行效率；

存储过程，如果采用存储过程你可以将整个业务逻辑封装在存储过程里，然后在客户端直接调用存储过程处理，这样可以减少数据库交互的成本；
23.事务型数据库与分析型数据库的特征及二者区别？
事务型数据库主要是实时的，面向应用的数据库，响应及时性要求很高，只关注最近一段时间的数据。就是平时搭建的服务都叫事务型数据库。

分析型数据库主要是用于在大量数据中分析规律的，一般存储的数据时间跨度长，数据量大，对实时性要求不高，通过查询分析规律趋势，用于产品决策等，如数据仓库。
24.谓词EXISTS
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS 指定一个子查询，检测 行 的存在。
语法： EXISTS subquery
参数： subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。
结果类型： Boolean 如果子查询包含行，则返回 TRUE ，否则返回 FLASE 。
与集合成员资格运算符IN的区别

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

①exists:存在，后面一般都是子查询，当子查询返回行数时，exists返回true。

select * from class where exists (select'x"form stu where stu.cid=class.cid)

当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率

exists(xxxxx)后面的子查询被称做相关子查询, 他是不返回列表的值的.

只是返回一个ture或false的结果
其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果存在，返回ture则输出,反之返回false则不输出,再根据主查询中的每一行去子查询里去查询.
执行顺序如下：

首先执行一次外部查询

对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。

使用子查询的结果来确定外部查询的结果集。
②in：包含

查询和所有女生年龄相同的男生

select * from stu where sex='男' and age in(select age from stu where sex='女')

in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,

然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.
查询所有选修了1号课程的学生姓名
思路分析：
   本查询涉及Student和SC关系
   在Student中依次取每个元组的Sno值，用此值去检查SC关系
   若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= '1'，则取此Student.Sname送入结果关系
   用嵌套查询
     SELECT Sname
     FROM Student
     WHERE EXISTS
                   (SELECT *
                    FROM SC
                    WHERE Sno=Student.Sno AND Cno= ' 1 ')； 
   用连接运算
    SELECT Sname
    FROM Student, SC
    WHERE Student.Sno=SC.Sno AND SC.Cno= '1';
25.谓词any、all
这两个都是用于子查询的

any 是任意一个

all 是所有
any表示有任何一个满足就返回true，all表示全部都满足才返回true
select * from student where 班级=’01’ and age > all (select age from student where 班级=’02’); 
就是说，查询出01班中，年龄大于 02班所有人的同学 
select * from student where 班级=’01’ and age > (select max(age) from student where 班级=’02’);
select * from student where 班级=’01’ and age > any (select age from student where 班级=’02’); 
就是说，查询出01班中，年龄大于 02班任意一个 的 同学 
select * from student where 班级=’01’ and age > (select min(age) from student where 班级=’02’);
查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
SELECT Sname，Sage
FROM    Student
WHERE Sage < ANY (SELECT  Sage
                                     FROM    Student
                                     WHERE Sdept= ' CS ')
           AND Sdept <> ‘CS ' ;     
SELECT Sname，Sage
FROM   Student
WHERE Sage < (SELECT MAX(Sage)
                            FROM Student
                            WHERE Sdept= ‘CS ')
           AND Sdept <> ' CS ’ ;
查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄
用ALL谓词
    SELECT Sname，Sage
    FROM Student
    WHERE Sage < ALL
                           (SELECT Sage
                            FROM Student
                            WHERE Sdept= ' CS ')
           AND Sdept <> ' CS ’;
用聚集函数
        SELECT Sname，Sage
        FROM Student
        WHERE Sage < 
                               (SELECT MIN(Sage)
                                FROM Student
                                WHERE Sdept= ' CS ')
              AND Sdept <>' CS ’;
26.视图
视图正如其名字的含义一样，是另一种查看数据的入口。

常规视图本身并不存储实际的数据，而仅仅是由SELECT语句组成的查询定义的虚拟表 。

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。

视图最多可以有 1024 列
视图的特点

视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作。

视图不能被修改，表修改或者删除后应该删除视图再重建。

视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。

视图可以被嵌套，一个视图中可以嵌套另一个视图。

视图不能索引，不能有相关联的触发器和默认值，sql server不能在视图后使用order by排序
视图的功能

1.简化用户操作

2.能以不同的角度观察同一个数据库

3.对重构数据库提供了逻辑独立性：

利用视图将需要的数据合并或者筛选，但是不影响原表的数据和结构

3.对机密数据提供安全保护：

可以建立不同的视图对用不同的用户，以达到安全的目的。
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
CREATE VIEW actor_name_view(first_name_v,last_name_v) AS
SELECT first_name,last_name
FROM actor
27.触发器
何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。

常见的触发器有三种：分别应用于Insert , Update , Delete 事件。
CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
  T-SQL语句
--with encryption 表示加密触发器定义的sql文本
--delete,insert,update指定触发器的类型</pre>
     Create Trigger truStudent 
       On Student                         --在Student表中创建触发器 
       for Update                          --为什么事件触发 
     As                                        --事件触发后所要做的事情 
       if Update(StudentID)            
       begin 
         Update BorrowRecord 
           Set StudentID=i.StudentID 
           From BorrowRecord br , Deleted   d ,Inserted i      --Deleted和Inserted临时表 
           Where br.StudentID=d.StudentID 
一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。
2)如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。
   对于2，创建一个Delete触发器 
     Create trigger trdStudent 
       On Student 
       for Delete 
       Delete BorrowRecord 
         From BorrowRecord br , Delted d 
         Where br.StudentID=d.StudentID 
28.SQL优化问题
https://mp.weixin.qq.com/s/6gqDy8VsMWUSgvc8zhnB6Q
29.ifnull函数与coalesce()函数
1）ifnull

ifnull函数用于在表达式是NULL时，从另外一个表达式获得值,它可以用于大多数数据类型，但值与替代值必须是同一数据类型
IFNULL(expr1,expr2)
如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。
2）coalesce函数用指定值代替NULL,其不同点在于，它可以接受一个数据集，一次检查其中的每一个值，知道发现一个非null值，如果没有找到非null值，它会返回一个值

将空值替换成其他值

返回第一个非空值
②表达式：

COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
30.instr函数
INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1    被搜索的字符串

C2    希望搜索的字符串

I     搜索的开始位置,默认为1

J     出现的位置,默认为1

SQL> select instr("abcde",'b');
结果是2，即在字符串“abcde”里面，字符串“b”出现在第2个位置。如果没有找到，则返回0
31.decode函数
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
  该函数的含义如下:
  IF 条件=值1 THEN
  RETURN(翻译值1)
  ELSIF 条件=值2 THEN
  RETURN(翻译值2)
  ......
  ELSIF 条件=值n THEN