200723 SQL面试常考题

🚩 本渣个人向整理（每天积累一点）

1. count(*)和count(1)的区别

# count函数详细介绍
SELECT COUNT(*) FROM TABLE; #统计行数
SELECT COUNT(1) FROM TABLE; #新建n行1，统计1的行数，其他常量值也可以
# 推荐 count(*)

引申：count(*) 和 count(1)和count(列名)区别

（1）执行效果：
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了所有的列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，当某个字段值为NULL时忽略，不统计

（2）执行效率：
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（*）最优
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！

（3）执行计划:
count(1)和count(*)的效果是一样的。 但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。 如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。 因为count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*),sql会帮你完成优化的。

因此：count(1)和count(*)基本上是没有差别！ sql调优功能

2. 如何复制表中多个字段的结构，但不复制数据

# 复制employees表中的last_name,department_id,salary字段到新表emp表中，但不复制数据
CREATE TABLE emp 
AS
SELECT last_name,department_id,salary
FROM employees
WHERE 1=2; #恒不成立

CREATE TABLE new_table LIKE old_table;

3. unique和primary key的区别

key	保证唯一性	是否允许非空	是否允许组合
主键	是	否	是
唯一	是	是	是

一个表至多有一个主键，但可以有多个唯一
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。

4. delete和truncate和drop的区别

（1） delete可以添加where条件，truncate不能添加where条件，一次性删除所有数据
（2） truncate的效率较高
（3） 如果删除带自增长列的表，使用delete删除后，重新插入数据，记录从断点处开始，使用truncate删除后，重新插入数据，记录从1开始
（4） delete删除数据，会返回受影响的行数，而truncate不返回
（5） delete删除数据，可以支持事务回滚，而truncate不支持

# 演示delete
SET autocommit = 0;
START TRANSACTION;
DELETE FROM Chars;
ROLLBACK;

# 演示truncate
SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
# 区别 truncate在事务执行的过程中，哪怕进行了rollback，但操作已经提交到磁盘，所以删除了表；delete可以回滚，表依然在

（1）drop是完全删除表+表结构
DROP TABLE table_name;
（2） delete是删除（部分）表数据，保留表的结构，而且可以加where,只删除一行或者多行
DELETE FROM table_name [WHERE];
（3） truncate 只能删除全部表数据，会保留表结构，而且不能加where
TRUNCATE TABLE table_name;
（4）delete不影响所用extent，高水线保持原位置不动；trustcate会将高水线复位
（5）处理效率：drop>truncate>delete

5. 判断

 select * from employees; 

select * from employees where commission_pct like '%%' and last_name like '%%'

两个代码的结果是否一样？说明原因
不一样，%不匹配NULL，但判断的字段commission_pct中有NULL