SQL基础知识

分为增删改查和面试题

增

创建数据库：create database 数据库名称

create database taobao;

创建表：create table 表名（列 格式 null设置，·····），主键设置：primary key (主键列)

CREATE TABLE ProductIns(
product_id char(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER ,
regist_date DATE,
PRIMARY KEY (product_id));

根据查询创建新表：create table 表名 as select ·············

create table user_lc AS select count(DISTINCT user_id) as "第一天新增用户数"
from userbehavior
where dates = '2017-11-25';

创建新列：alter table 表名 add column 列名 格式

alter table lc_copy add column ListingId  varchar(20);

删

删除数据库：drop database 数据库名称

drop database school;

删除数据表：drop table 表名

drop table product;

只删数据不删表：delete from 表名（指定条件：+ where）

delete from product
where sale_price >=400

改

修改表名：alter table 旧表名 rename to 新表名

alter table score rename to score2;

向表中插入数据：insert into 表名（列名···）values（‘数据’，····）

INSERT INTO productins(id,name,type)
VALUES('001','T恤衫','衣服');

从其他表复制数据插入：insert 新表 (列名···）select 列名··from 旧表

insert into producttype(pr_type,sum_sale,sum_purchase)
select pr_type,sum(sale),sum(purchase)
from product
group by type;

修改列值：update 表名 set 列名 = 表达式
指定条件：+ where

多列更新：set 列名1 = 表达式，列名2= 表达式

update product set regist_date ='2009-10-10'
where product_id ='008';

查（重点）

基本select

某列：select 列名 from 表名

select id from score;

全表：select * from 表名

select * from score;

去重：select distinct 列名 from 表名
–查询不同ID

select distinct id from score;

–统计不同ID个数

select count(distinct id) from score;

–优化写法

select count(*) from
(select distinct id from score) as a

聚合

count/sum/avg/max/min

-- 统计最大/最小/平均薪水
select  max(salary), min(salary),avg(salary) 
from employee

分组

group by

--查询每个班平均成绩
select avg(score) from student
group by class;

筛选

where：指定行对应条件，不可用聚合函数

select score from student
where id ='001';

having：指定组对应条件，可用聚合函数

select avg(score) from student
group by class
having avg(score)>80;

排序

order by
升序：Asc 降序：Desc

多个排序：order by 列名1，列名2（从左到右优先原则）

SELECT 学号,成绩 from score2
ORDER BY 成绩 Desc;

limit：从查询结果中取出指定行
进阶：

limit N：返回 N 条记录

offset M：跳过 M 条记录，M 默认为 0

limit M,N：跳过M条记录，返回 N 条记录

SELECT 学号,成绩 from score2
ORDER BY 成绩 Desc
LIMIT 1,2;

表连接（重点）

两张学生表：

join/ inner join：内连接，左表右表都匹配的记录

SELECT a.id,年龄,性别
from student1 a join student2 b
on a.id=b.id;

left join ：左连接，以左表位置准，向右表匹配对应字段，有多条则逐次列出，无匹配，则显示NULL。

SELECT a.id,年龄,性别
from student1 a LEFT JOIN student2 b
on a.id=b.id;

right join :右连接，以右表位置准，向左表匹配对应字段，有多条则逐次列出，无匹配，则显示NULL。

SELECT b.id,年龄,性别
from student1 a RIGHT JOIN student2 b
on a.id=b.id;

outer / full join：全连接，包含两个表全部连接结果，左、右表缺失的数据会显示为NULL。

cross join：交叉连接 / 笛卡尔积，生成3X3结果的表格

SELECT a.id,年龄,性别
from student1 a CROSS JOIN student2 b;

表合并

合并两表相同字段：

union：去重合并
union all：非去重合并

-- 去重合并
select * from course
UNION
SELECT * from course2;

– 非去重合并

select * from course
UNION ALL
SELECT * from course2;

视图
视图是不保存实际数据的临时表，相当于直接调用由select语句查询结果，在实际工作中，视图可以减少写sql语句时的嵌套。

create view 视图名称 as + select··········

示例：如下图，创建一个名为“用户行为”的临时表

create view 用户行为 as 
select user_id,count(behavior)as 用户行为总数,
sum(if(behavior = 'pv',1,0))as 点击量,
sum(if(behavior = 'fav',1,0))as 收藏数,
sum(if(behavior = 'cart',1,0))as 加购数,
sum(if(behavior = 'buy',1,0))as 购买量
from userbehavior
group by user_id;

后续可以直接写sql语句调用该表

select
sum(点击量) as "总点击量",
sum(收藏数) as "总收藏数",
sum(加购数) as "总加购数",
sum(购买量) as "总购买量"
from 用户行为;

复杂查询
子查询：也就是常说的嵌套，即将定义视图的select语句直接用于from子句中。子查询层数无上限。

score

select 学号
from (select * from score GROUP BY 学号)as a

标量子查询：返回1行1列结果的子查询（可在select和where中使用）
– 查询高于平均成绩的学号及对应信息

select *
from score
where 成绩>(select avg(成绩) from score);

关联子查询：where中使用子查询

SELECT 学号,课程号,成绩
from score as s1
WHERE 成绩>(
SELECT avg(成绩)
FROM score as s2
-- 关联条件，按课程号来对表分组，同一组的数据，与这一组的平均成绩进行比较
WHERE s1.课程号 = s2.课程号 
GROUP BY 课程号);

理论部分

三个范式是什么
第一范式：字段是最小的的单元不可再分
学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的
第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键。
其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的
学号为1024的同学，姓名为Java3y，年龄是22岁。姓名和年龄字段都依赖着学号主键。
第三范式：满足第二范式，非主键外的所有字段必须互不依赖

就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖
比如，我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段。

什么是视图？
视图是一种基于数据表的一种虚表
（1）视图是一种虚表
（2）视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
（3）向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句
（4）视图向用户提供基表数据的另一种表现形式
（5）视图没有存储真正的数据，真正的数据还是存储在基表中
（6）程序员虽然操作的是视图，但最终视图还会转成操作基表
（7）一个基表可以有0个或多个视图
值得注意的是：使用视图可以让我们专注与逻辑，但不提高查询效率

drop、delete与truncate分别在什么场景之下使用？

我们来对比一下他们的区别：

drop table

1)属于DDL
2)不可回滚
3)不可带where
4)表内容和结构删除
5)删除速度快

truncate table

1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
5)删除速度快

delete from

1)属于DML
2)可回滚
3)可带where
4)表结构在，表内容要看where执行的情况
5)删除速度慢,需要逐行删除
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

索引是什么？有什么作用以及优缺点？
什么是索引【Index】
（1）是一种快速查询表中内容的机制，类似于新华字典的目录
（2）运用在表中某个些字段上，但存储时，独立于表之外

rowid的特点

（1）位于每个表中，但表面上看不见，例如：desc emp是看不见的
（2）只有在select中，显示写出rowid，方可看见
（3）它与每个表绑定在一起，表亡，该表的rowid亡，二张表rownum可以相同，但rowid必须是唯一的
（4）rowid是18位大小写加数字混杂体，唯一表代该条记录在DBF文件中的位置
（5）rowid可以参与=/like比较时，用’'单引号将rowid的值包起来，且区分大小写
（6）rowid是联系表与DBF文件的桥梁
索引特点
（1）索引一旦建立, Oracle管理系统会对其进行自动维护, 而且由Oracle管理系统决定何时使用索引
（2）用户不用在查询语句中指定使用哪个索引
（3）在定义primary key或unique约束后系统自动在相应的列上创建索引
（4）用户也能按自己的需求，对指定单个字段或多个字段，添加索引
需要注意的是：Oracle是自动帮我们管理索引的，并且如果我们指定了primary key或者unique约束，系统会自动在对应的列上创建索引…

什么时候【要】创建索引
（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广
（3）列名经常在 WHERE 子句或连接条件中出现
什么时候【不要】创建索引
（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中
索引优缺点：

1 索引加快数据库的检索速度
2 索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新
3数据的性能下降，因为大部分数据更新需要同时更新索引)
4 唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
5 索引需要占物理和数据空间

索引分类：
唯一索引：唯一索引不允许两行具有相同的索引值
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

什么是事务？事物详解
事务简单来说：一个Session中所进行所有的操作，要么同时成功，要么同时失败

包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。
ACID
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务隔离级别
数据库定义了4个隔离级别：

Serializable【可避免脏读，不可重复读，虚读】
Repeatable read【可避免脏读，不可重复读】
Read committed【可避免脏读】
Read uncommitted【级别最低，什么都避免不了】

分别对应Connection类中的4个常量

TRANSACTION_READ_UNCOMMITTED
TRANSACTION_READ_COMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE

脏读：一个事务读取到另外一个事务未提交的数据

例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。

不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改

注：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】

虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

注：和不可重复读类似，但虚读(幻读)会读到其他事务的插入的数据，导致前后读取不一致

简单总结：脏读是不可容忍的，不可重复读和虚读在一定的情况下是可以的【做统计的肯定就不行】。

数据库的乐观锁和悲观锁是什么？
确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
在查询完数据的时候就把事务锁起来，直到提交事务
实现方式：使用数据库中的锁机制
缺点 性能低
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
在修改数据的时候把事务锁起来，通过version的方式来进行锁定
实现方式：使用version版本或者时间戳

超键、候选键、主键、外键分别是什么？
超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键(候选码)：是最小超键，即没有冗余元素的超键。
主键(主码)：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。
候选码和主码：

例子：邮寄地址（城市名，街道名，邮政编码，单位名，收件人）

它有两个候选键:{城市名，街道名} 和 {街道名，邮政编码}
如果我选取{城市名，街道名}作为唯一标识实体的属性，那么{城市名，街道名} 就是主码(主键)

SQL 约束有哪几种？

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。

数据库运行于哪种状态下可以防止数据的丢失？
在archivelog mode(归档模式)只要其归档日志文件不丢失，就可以有效地防止数据丢失。

Mysql存储引擎
常用的存储引擎有以下：

Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎),不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。
如果一个表修改要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

MyIASM和Innodb两种引擎所使用的索引的数据结构是什么？
答案:都是B+树!

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

varchar和char的区别
Char是一种固定长度的类型，varchar是一种可变长度的类型

mysql有关权限的表都有哪几个
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

数据表损坏的修复方式有哪些？
使用 myisamchk 来修复，具体步骤：

1）修复前将mysql服务停止。
2）打开命令行方式，然后进入到mysql的/bin目录。
3）执行myisamchk –recover 数据库所在路径/*.MYI

使用repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表 REPAIR TABLE 用于修复被破坏的表。
OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）

MySQL中InnoDB引擎的行锁是通过加在什么上完成
InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,

如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起

数据库优化的思路
SQL优化
在我们书写SQL语句的时候，其实书写的顺序、策略会影响到SQL的性能，虽然实现的功能是一样的，但是它们的性能会有些许差别。

因此，下面就讲解在书写SQL的时候，怎么写比较好。

①选择最有效率的表名顺序

数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理

在FROM子句中包含多个表的情况下：

如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推
也就是说：选择记录条数最少的表放在最后
如果有3个以上的表连接查询：

如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推。
也就是说：被其他表所引用的表放在最后
例如：查询员工的编号，姓名，工资，工资等级，部门名

emp表被引用得最多，记录数也是最多，因此放在form字句的最后面

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)          

②WHERE子句中的连接顺序

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

emp.sal可以过滤多条记录，写在WHERE字句的最右边

select emp.empno,emp.ename,emp.sal,dept.dname
from dept,emp
where (emp.deptno = dept.deptno) and (emp.sal > 1500)

③SELECT子句中避免使用*号

我们当时学习的时候，“*”号是可以获取表中全部的字段数据的。

但是它要通过查询数据字典完成的，这意味着将耗费更多的时间
使用*号写出来的SQL语句也不够直观。
④用TRUNCATE替代DELETE

这里仅仅是：删除表的全部记录，除了表结构才这样做。

DELETE是一条一条记录的删除，而Truncate是将整个表删除，保留表结构，这样比DELETE快

⑤多使用内部函数提高SQL效率

例如使用mysql的concat()函数会比使用||来进行拼接快，因为concat()函数已经被mysql优化过了。

⑥使用表或列的别名

如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了。。。

⑦多使用commit

comiit会释放回滚点…

⑧善用索引

索引就是为了提高我们的查询数据的，当表的记录量非常大的时候，我们就可以使用索引了。

⑨SQL写大写

我们在编写SQL 的时候，官方推荐的是使用大写来写关键字，因为Oracle服务器总是先将小写字母转成大写后，才执行

⑩避免在索引列上使用NOT

因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描

①①避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢

①②用 >= 替代 >

  低效：
  SELECT * FROM EMP WHERE DEPTNO > 3   
  首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
  高效：
  SELECT * FROM EMP WHERE DEPTNO >= 4  
  直接跳到第一个DEPT等于4的记录

①③用IN替代OR

  select * from emp where sal = 1500 or sal = 3000 or sal = 800;
  select * from emp where sal in (1500,3000,800);

①④总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引。 当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引

 
  create index emp_sal_job_idex
  on emp(sal,job);
  ----------------------------------
  select *
  from emp  
  where job != 'SALES';    
  
  
  上边就不使用索引了。

数据库结构优化
1）范式优化： 比如消除冗余（节省空间。。）
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表： 垂直拆分和水平拆分
服务器硬件优化