SQL基础常考

SQL基础

SQL语句分为哪几类

  • 数据库定义语言DDL(Data Definition Language):操纵数据库的结构;

    主要有CREATE,DROP,ALTER等对逻辑结构有操作的,包括表结构、视图和索引。

  • 数据库查询语言DQL(Data Query Language):查数据;

    主要以SELECT为主。

  • 数据操纵语言DML(Data Manipulation Language):增删改数据;

    主要包括INSERT,UPDATE,DELETE

  • 数据控制功能DCL(Data Control Language):权限控制;

    主要是权限控制能操作,包括GRANT,REVOKE,COMMIT,ROLLBACK等。

SQL语句的执行顺序

select [id,name]										#6

from [table1] [tablename1]								#1

left join table2 [tablename2] on [conditions]			#2
inner join table3 [tablename3] on [conditions]	
……

where [conditions]										#3

group by [conditions]									#4

having [conditions]								 		#5

order by [conditions]									#7
临时表的生命周期

MySQL在执行SQL语句的时候会临时创建一些存储中间结果集的表,这种表被称为临时表。

临时表只对当前连接可见,在连接关闭后,临时表会被删除并释放空间。

临时表主要分为内存临时表和磁盘临时表两种。内存临时表使用的是MEMORY存储引擎,磁盘临时表使用的是MyISAM存储引擎。

一般在以下几种情况中会使用到临时表:

  • FROM中的子查询
  • DISTINCT查询并加上ORDER BY
  • ORDER BY和GROUP BY的子句不一样时会产生临时表
  • 使用UNION查询会产生临时表

数据类型

varchar和char的区别
  • varchar表示变长,char表示长度固定。当所插入的字符超过他们的长度时,在严格模式下,会拒绝插入并提示错误信息,在一般模式下,会截取后插入。如char(5),无论插入的字符长度是多少,长度都是5,插入字符长度小于5,则用空格补充。对于varchar(5),如果插入的字符长度小于5,则存储的字符长度就是插入字符的长度,不会填充。
  • 存储容量不同,对于char来说,最多能存放的字符个数为255。对于varchar,最多能存放的字符个数是65532。
  • 存储速度不同,char长度固定,存储速度会比varchar快一些,但在空间上会占用额外的空间,属于一种空间换时间的策略。而varchar空间利用率会高些,但存储速度慢,属于一种时间换空间的策略。
MySQL中int(10)和char(10)和varchar(10)的区别?

int(10)中的10表示的是显示数据的长度,而char(10)和varchar(10)表示的是存储数据的大小。

NULL和空值
  • NULL也就是在字段中存储NULL值,空值也就是字段中存储空字符(’’)。
  • 空值(’’)的长度是0,是不占用空间的;而的NULL长度是NULL。
  • 要单纯查NULL值列,则使用 is NULL去查,单纯去查空值(’’)列,则使用 =''
  • count(vol)不会统计值为NULL的对象,count(mainkey)count(1)才能

Null的特性

  • 如果null参与算术运算,则该算术表达式的值为null。(例如:+,-,*,/ 加减乘除)
  • 如果null参与比较运算,则结果可视为false。(例如:>=,<=,<> 大于,小于,不等于)
  • 如果null参与聚集运算,则聚集函数都置为null(使用isnull(字段,0)等方式可以避免这种情况)。除count(*), count(1), count(0)等之外(count(字段) 字段为null的行不参与计数)。
  • 如果在not in子查询中有null值的时候,则不会返回数据。

子查询

把一个查询的结果在另一个查询中使用,子查询的结果是一个临时表,细分结果类型对应不同的应用,如:

  • 标量子查询:返回一个值。如用于合成一个全局变量,在上级查询中对所有对象进行比较判断。

    SELECT * FROM user WHERE age = (SELECT max(age) from user)  //查询年纪最大的人
    
  • 列子查询:返回一个列。如用于合成一个指标字段,在上级查询筛选符合该指标的对象。

    SELECT num1 FROM table1 WHERE num1 > ANY (SELECT num2 FROM table2)
    
  • 行子查询:返回一个行。相比标量子查询,相当于合成一个对象,在上级查询中对所有对象进行比较判断。

    SELECT * FROM user WHERE (age,sex) = (SELECT age,sex FROM user WHERE name="zhangsan")
    
  • 表子查询:返回一个表。相当于返回一个对象集合,上级查询中可筛选符合集合属性的对象。

    SELECT * FROM student WHERE (name,age,sex) IN (SELECT name,age,sex FROM class1) //在学生表中找到班级在1班的学生
    

连接查询

如图:

在这里插入图片描述

  • 连接查询的目的是利用多个表的信息。<select_list>的值域,为表A和表B字段张成的空间。
  • 在没有WHERE筛选条件的情况下,左外连接的左表的所有对象都被表达,对象空间被B表所含字段扩充。
  • WHERE是对查询结果的筛选,建立在关联表结果之上。

设L表

AB
a1b1
a2b2
a3b3

设R表

BC
b1c1
b2c2
b4c3
  • 左外连接:Aleft joinB,关键字左边的A为主角,依某种条件连接上B的信息

    select L.`*`,R.`*` from L left join R on L.b=R.b
    
    ABBC
    a1b1b1c1
    a2b2b2c2
    a3b3nullnull
  • 右外连接:Aright joinB,关键字右边的B为主角,依某种条件连接上A的信息

    select L.`*`,R.`*` from L right join R on L.b=R.b
    
    BCAB
    b1c1a1b1
    b2c2a2b2
    b4c3nullnull
  • 全外连接:取并集

    MySQL没有FULL JOIN,用两次外连接加去重联合得到

    SELECT * FROM L LEFT JOIN R ON L.b=R.b
    UNION
    SELECT * FROM L RIGHT JOIN R ON L.b=R.b
    
  • 内连接:Ainner joinB,取交集

    select L.`*`,R.`*` from L inner join R on L.b=R.b
    
  • 交叉连接:笛卡尔积

    select L.`*`,R.`*` from L,R
    
    ABBC
    a1b1b1c1
    a1b1b2c2
    a1b1b4c3
    a2b2b1c1
    a2b2b2c2
    a2b2b4c3
    a3b3b1c1
    a3b3b2c2
    a3b3b4c3

关键字

mysql中in和exists的区别?

in和exists一般用于子查询。

  • 使用exists时会先进行外表查询,将查询到的每行数据带入到内表查询中看是否满足条件;
  • 使用in一般会先进行内表查询获取结果集,然后对外表查询匹配结果集,返回数据。
  • in在内表查询或者外表查询过程中都会用到索引。
  • exists仅在内表查询时会用到索引
  • 一般来说,当子查询的结果集比较大,外表较小使用exist效率更高;当子查询寻得结果集较小,外表较大时,使用in效率更高。
  • 对于not in和not exists,not exists效率比not in的效率高,与子查询的结果集无关,因为not in对于内外表都进行了全表扫描,没有使用到索引。not exists的子查询中可以用到表上的索引。
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)
select * from TableIn where ANAME=ANY(select BNAME from TableEx)
UNION和UNION ALL的区别?

union和union all的作用都是将两个结果集合并到一起。

  • union会对结果去重并排序,union all直接直接返回合并后的结果,不去重也不进行排序
  • union all的性能比union性能好。
drop、delete和truncate的区别?
dropdeletetruncate
速度逐行删除,慢较快
类型DDLDMLDDL
回滚不可回滚可回滚不可回滚
删除内容删除整个表,数据行、索引都会被删除表结构还在,删除表的一部分或全部数据表结构还在,删除表的全部数据

一般来讲,删除整个表,使用drop,删除表的部分数据使用delete,保留表结构删除表的全部数据使用truncate。

<think>好的,我现在需要处理用户关于寻找大厂SQL常见笔试题及答案解析的请求。首先,我得仔细阅读用户提供的系统级指令和用户可见层的要求,确保回答符合格式和内容规范。 用户明确提到需要生成相关问题,并且在回答中引用提供的三个引用内容。引用1、2、3分别涉及Oracle的SQL试题、运维面试题集锦和SQL大厂春招真题解析。我需要确保在回答中合理引用这些内容,同时注意引用标识的位置。 接下来,我需要确定用户的具体需求。用户希望得到大厂SQL笔试题的常见题目和解析,可能是在准备面试,特别是针对大厂的SQL笔试部分。因此,回答需要涵盖不同类型的SQL题目,如基础操作、多表查询、窗口函数、性能优化等,这些都是常见的考点。 然后,我需要从提供的引用中提取相关信息。引用1提到了Oracle的SQL试题和答案解析,引用2包含了运维面试中的SQL题,引用3则涉及大厂春招真题,包括增删改查、视图、触发器等内容。这些引用可以作为题目分类的依据,例如基础操作对应增删改查,高级查询可能涉及窗口函数,优化部分可以引用性能调优的内容。 在组织回答时,需要按照用户要求的结构,先给出常见题目分类,每个类别下列举具体题目并附上解析。同时,每个解析后需要自然地添加引用标识,例如在基础操作部分引用3,性能优化引用1等。要注意引用标识的位置应在相关段落的末尾,而不是每个题目后面。 此外,用户要求生成相关问题,放在最后。我需要确保这些问题与前面的内容相关,能够帮助用户进一步深入学习或测试自己的理解。例如,关于JOIN的区别、索引优化策略、窗口函数应用等都是合理的问题。 还需要检查LaTeX格式是否正确,所有行内数学表达式用$...$,独立公式用$$...$$。虽然当前问题主要涉及SQL,可能数学表达式不多,但如果有例如计算或统计函数,需要正确应用格式。 最后,确保回答使用中文,结构清晰,分点列出,并且每个部分都有对应的引用。同时,验证所有链接和引用是否恰当,避免错误引用或遗漏。 总结步骤: 1. 分类整理大厂SQL常见题型。 2. 每个类别下提供具体题目和解析。 3. 在解析后添加对应的引用标识。 4. 生成相关问题,促进用户进一步思考。 5. 检查格式、语法和引用准确性。</think>### 大厂 SQL 常见笔试题分类及解析 --- #### 一、基础操作类 1. **查询表中第 N 高的数据** **题目**:从表 `Employee` 中查询工资第 2 高的记录(需处理重复值)。 **解析**: ```sql SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1; ``` 使用 `DISTINCT` 去重,`LIMIT` 和 `OFFSET` 分页截取[^3]。 2. **删除重复记录** **题目**:删除表 `Orders` 中完全重复的行(仅保留一条)。 **解析**: ```sql DELETE FROM Orders WHERE id NOT IN ( SELECT MIN(id) FROM Orders GROUP BY order_no, customer_id ); ``` 通过子查询保留每组重复数据的最小 `id`,删除其余记录[^3]。 --- #### 二、多表查询与聚合 1. **统计部门平均工资** **题目**:查询部门表 `Department` 和员工表 `Employee`,列出部门名称及平均工资(保留两位小数)。 **解析**: ```sql SELECT d.name, ROUND(AVG(e.salary), 2) AS avg_salary FROM Department d JOIN Employee e ON d.id = e.dept_id GROUP BY d.name; ``` `JOIN` 关联两表,`ROUND` 控制精度。 2. **查找未完成订单的用户** **题目**:从 `Users` 表和 `Orders` 表中找出从未下单的用户。 **解析**: ```sql SELECT u.id, u.name FROM Users u LEFT JOIN Orders o ON u.id = o.user_id WHERE o.id IS NULL; ``` `LEFT JOIN` 结合 `IS NULL` 筛选未关联记录[^1]。 --- #### 三、窗口函数与高级分析 1. **计算累计销售额** **题目**:按日期统计每日及累计至当日的总销售额。 **解析**: ```sql SELECT order_date, SUM(amount) OVER (ORDER BY order_date) AS cumulative_amount FROM Sales; ``` 使用 `SUM() OVER()` 实现窗口累计计算。 2. **排名问题** **题目**:查询每个部门工资前三高的员工。 **解析**: ```sql SELECT dept_id, name, salary FROM ( SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM Employee ) t WHERE rk <= 3; ``` `DENSE_RANK()` 生成部门内排名,避免跳号。 --- #### 四、性能优化与索引 1. **索引失效场景** **题目**:列举 3 种导致索引失效的 SQL 写法。 **解析**: - 对索引字段使用函数(如 `WHERE UPPER(name) = 'A'`) - 模糊查询以 `%` 开头(如 `WHERE name LIKE '%abc'`) - 类型隐式转换(如字段为 `VARCHAR`,但写 `WHERE id = 100`)。 2. **分页查询优化** **题目**:如何优化 `LIMIT 100000, 10` 这类深分页查询? **解析**: ```sql SELECT * FROM Table WHERE id > (SELECT id FROM Table ORDER BY id LIMIT 100000, 1) LIMIT 10; ``` 通过子查询先定位偏移位置的 `id`,减少全表扫描[^2]。 --- §§ 1. SQL 中 `INNER JOIN` 和 `LEFT JOIN` 的主要区别是什么? 2. 如何通过索引优化 `WHERE` 条件中包含 `OR` 的查询? 3. 窗口函数 `ROW_NUMBER()` 和 `RANK()` 的应用场景有何不同? 4. 如何设计表结构以减少数据冗余?
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值