linux+sql面试常见基础八股文

linux篇

conda env list 查看镜像
source deactivate 关闭虚拟环境
source activate wfy 激活 虚拟环境
conda create -n your_env_name python=x.x Python创建虚拟环境
conda remove --name $your_env_name $package_name 删除虚拟环境
最简单的 SSH 命令只需要指定用户名和主机名参数即可. 主机名可以是 IP 地址或者域名. 命令格式如下:
ssh #######.comssh #######.com -p 35000
.sh 文件 在linux 系统中 使用 sh ####.sh 文件就可以运行任务
ps u 查看当前用户的进程
lsof -i //显示所有打开的端口
lsof -i:80 //显示所有打开80端口的进程
ps -ef | grep python 查看 python进程
kill 进程号 杀死进程
lsof -i:8018查看使用端口号是8081的进程
运行命令：
nohup python main.py >/dev/null 2>&1 &
nvidia-smi 查看显卡使用量和信息
pwd显示当前的目录
ls是显示当前目录下的文件
ll会列出该文件下的所有文件信息，包括隐藏的文件
ls | wc -w 查看有多少个文件夹
ls | wc -c
wc 命令 - c 统计字节数 - l 统计行数 - w 统计单词数。
cd空格.. 返回上级目录
rm空格ceshi.txt 删除当前目录下这个文件
rmdir空格ceshi 删除当前这个ceshi文件夹
rm -rf 文件夹 强制删除这个文件 无论空不空
mkdir project 在当前目录新建一个 project的文件夹
cd project/ 到达这个目录文件下
head 文件 显示一个文件 文件头
touch文件名 新建有个文件名
文件复制 : cp 源文件名 新的文件名
目录复制 : cp 源目录名 新的文件名
mv ceshi4 ../ceshi 将 ceshi4文件移动到 上一层目录 ceshi 中
mv source_file(文件) dest_directory(目录)将文件 source_file 移动到目标目录 dest_directory 中
把 textfile1 和 textfile2 的文档内容加上行号（空白行不加）之后将内容附加到 textfile3 文档里：
cat -b textfile1 textfile2 >> textfile3
把 textfile1 的文档内容加上行号后输入 textfile2 这个文档里：
cat -b textfile1 > textfile2
将 file1 file2 并成 file 新文件
cat file1 file2 > file
cat 文件 查看文件内容显示文件的内容打印
cp 原文件 新文件 # 拷贝文件
cp -r 源文件夹 新文件夹 # 拷贝文件夹
显示每个文件的大小 wc -l *
head 文件名 显示文件的前十行
tail 文件名 显示 文件的后十行
文件查找 语法 find 搜索路径 -name "文件名关键词" #
find / -name "passwd"
find / -name "ifcfg-*"
修改文件所有者 chown 用户名 文件名
修改文件所属组 chown :组名 文件名
压缩语法：tar -zcvf 压缩后文件名 被压缩文件
解压缩语法 tar -zxvf 压缩文件名 -C 解压后文件所在目录
vim 文件是打开文件 可以直接进行编辑 按 i 键 是进行编辑模型 按esc键是退出编辑模式
：wq 是保存退出 dd 删除所在的行
Vim是从 vi 发展出来的一个文本编辑器。代码补完、编译及错误跳转等方便编程的功能特别丰富，在程序员中被广泛使用。简单的来说， vi 是老式的字处理器，不过功能已经很齐全了，但是还是有可以进步的地方。 vim 则可以说是程序开发者的一项很好用的工具。
git clone https://git.xiaojukeji.com/gaozhiqiang/wujie_dtp.git
从git上下载代码
git branch
不带参数：列出本地已经存在的分支，并且在当前分支的前面用"*"标记
git checkout master
将分支切换到master
conda activate dstc36
激活这个环境
cd命令
（1）cd 进入用户主目录
（2）cd ~ 进入用户主目录
（3）cd - 返回进入此目录之前所在目录
（4）cd .. 返回上一级目录
（5）cd ../..返回上两级目录
（6）cd !$ 把上个命令的参数作为cd 参数使用
export PYTHONPATH=/nfs/project/liuhao/athena
功能说明：设置或显示环境变量。（比如我们要用一个命令，但这个命令的执行文件不在当前目录，这样我们每次用的时候必须指定执行文件的目录，麻烦，在代码中先执行export，这个相当于告诉程序，执行某某东西时，需要的文件或什么东东在这些目录里

Linux系统中的文件传输
1.scp命令
scp 本地文件 远程主机用户@远程主机ip：远程主机目录的绝对路径
scp 远程主机用户@远程主机ip：远程主机文件的绝对路径 本地文件

<2> 把本地文件复制到远程主机（上传）
scp file root@192.168.2.66:/root/Desktop

SQL篇

Select id, device_id, gender, age, university, province
From user_profile;
查询结果去重
SELECT distinct university from user_profile
查询结果限制返回行数
SELECT device_id AS user_infos_example FROM user_profile LIMIT 2;
select device_id from user_profile where id between 1 and 2;
将查询后的列重新命名
SELECT device_id AS user_infos_example FROM user_profile LIMIT 2;
查找后排序
SELECT device_id,age FROM user_profile ORDER BY age
查找后降序排列
SELECT device_id,gpa,age FROM user_profile ORDER BY gpa DESC,age DESC;
查找学校是北大的学生信息
SELECT device_id , university from user_profile where university = '北京大学'
查找年龄大于24岁的用户信息
SELECT device_id,gender,age,university FROM user_profile WHERE age >24
查找某个年龄段的用户信息
select device_id,gender,age from user_profile where age between 20 and 23;
查找除复旦大学的用户信息
select device_id, gender, age, university from user_profile where not university ="复旦大学";
用where过滤空值练习
SELECT device_id,gender,age,university FROM user_profile where age!=0
现在运营想要找到学校为北大、复旦和山大的同学进行调研，
select device_id, gender, age, university, gpa
from user_profile
where university in ('北京大学','复旦大学','山东大学');
现在运营想查看所有大学中带有北京的用户的信息
SELECT device_id,age,university from user_profile where university like '%北京%'
运营想要知道复旦大学学生gpa最高值是多少
方法一 desc
select gpa from user_profile where university = '复旦大学' order by gpa desc limit 1;
方法二：使用max(gpa)
select max(gpa) gpa
from user_profile
where university='复旦大学'
查找后多列排序，先按照gpa升序，在按照age升序 (升序—ASC；降序—DESC)
SELECT device_id,gpa,age FROM user_profile order by gpa ASC,age ASC
计算男生人数以及平均GPA
ROUND() 函数用于把数值字段舍入为指定的小数位数。
AVG() 函数返回数值列的平均值。
select count(gender) as male_num,
round(avg(gpa),1) as avg_gpa
from user_profile where gender='male'
下面的 SQL 语句从 "Websites" 表中选取头两条记录：
SELECT * FROM Websites LIMIT 2;
下面的 SQL 语句选取 alexa 介于 1 和 20 之间的所有网站：
SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

SQL join 用于把来自两个或多个表的行结合起来。
请注意，"Websites" 表中的 "id" 列指向 "access_log" 表中的字段 "site_id"。上面这两个表是通过 "site_id" 列联系起来的。
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

INNER JOIN 关键字在表中存在至少一个匹配时返回行。
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。
创建 Websites 的备份复件：
SELECT *
INTO WebsitesBackup2016
FROM Websites;
只复制一些列插入到新表中：
SELECT name, url
INTO WebsitesBackup2016
FROM Websites;
INSERT INTO SELECT 从一个表复制数据，然后把数据插入到一个已存在的表中，目标表中任何已存在的行都不会受影响。
复制 "apps" 中的数据插入到 "Websites" 中：
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;

Alter与UPdate区别：
1.Alter:修改表结构
(1). 增加表的字段 Alter table test Add(id,int).
(2).修改表的字段 Alter table change(id,varchar(10))
2.UPdate:修改表数据
(1).修改字段id列的值 update test set id=2;
Alter示例：
ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
现在，我们想在 "Persons" 表中添加一个名为 "DateOfBirth" 的列。
ALTER TABLE Persons
ADD DateOfBirth date
接下来，我们想要删除 "Person" 表中的 "DateOfBirth" 列。
ALTER COLUMN命令用于更改表中列的数据类型。
ALTER TABLE Persons
DROP COLUMN DateOfBirth
添加列/字段
alter table 表名 add 字段 数据类型;
Update 示例：
把 "菜鸟教程" 的 alexa 排名更新为 5000，country 改为 USA。
UPDATE Websites SET alexa='5000', country='USA' WHERE name='菜鸟教程';

UPDATE 语句用于更新表中已存在的记录。
UPDATE Websites SET alexa='5000', country='USA' WHERE name='菜鸟教程';
DELETE 语句用于删除表中的记录。
DELETE FROM Websites WHERE name='Facebook' AND country='USA';

1 drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。DROP TABLE 语句用于删除表。 DROP TABLE table_name
2 delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行
删除student表中姓名为张三丰的学生信息-delete from studentwhere name=‘张三丰’；
truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。TRUNCATE TABLE table_name

sql中视图与表的区别：
1、视图：视图不是一个独立的文件，是由一个或几个基本表导出形成的 虚表。
2、表：表是数据库中用来存储数据的对象，是有结构的数据的集合，是整个数据库系统的基础。
1、视图：视图存储的内容是来自本地数据源或远程数据源的定义，不存放实际数据。
2、表：表存储的内容是本地数据源的数据。
1、视图：操作者只可以对视图进行查操作，不能进行增删改操作。
2、表：操作者可以对表进行增删改查四种操作。
视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。

创建视图：在 SQL 查询语句的基础上封装了视图VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。其中 view_name 为视图名称，column1、column2 代表列名，condition 代表查询过滤条件。
CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition
我们以 NBA 球员数据表为例。我们想要查询比 NBA 球员平均身高高的球员都有哪些，显示他们的球员 ID 和身高。假设我们给这个视图起个名字 player_above_avg_height，那么创建视图可以写成：
CREATE VIEW player_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player)
比如我们想更新视图 player_above_avg_height，增加一个 player_name 字段，可以写成：
ALTER VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) from player)
嵌套视图
当我们创建好一张视图之后，还可以在它的基础上继续创建视图，比如我们想在虚拟表 player_above_avg_height 的基础上，找到比这个表中的球员平均身高高的球员，作为新的视图 player_above_above_avg_height，那么可以写成：
CREATE VIEW player_above_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player_above_avg_height)
创建表 CREATE TABLE
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255));
PersonID 列的数据类型是 int，包含整数。
LastName、FirstName、Address 和 City 列的数据类型是 varchar，包含字符，且这些字段的最大长度为 255 个字符。
创建表的约束：
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

聚合函数:返回汇总值 （它对其应用的每个行集返回一个值）
AVG(表达式) 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。
COUNT(表达式) 返回表达式中非NULL值的数量。可用于数字和字符列。
COUNT(*) 返回表中的行数(包括有NULL值的列)。
MAX(表达式) 返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。
MIN(表达式) 返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期时间列。
SUM(表达式) 返回表达式中所有的总和,忽略NULL值。仅用于数字列。
转型函数:将一种数据类型转换为另外一种（有CONVERT和CAST两种）
CONVERT(data_type[(length)], expression [, style])
例: Select convert(varchar(10) ,stuno) as stuno,stuname from student
CAST( expression AS data_type )
例: Select cast(stuno as varchar(10)) as stuno,stuname from student
Cast和Convert的区别
Cast 和Convert都是用来将一种数据类型的表达式转换为另一种数据类型的表达式。CAST 和 CONVERT 提供相似的功能，只是语法不同。在时间转化中一般用到convert,因为它比cast多加了一个style,可以转化成不同时间的格式。
数据库的三大范式
第1范式
确保数据库表字段的原子性
第2范式
首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。
第3范式
首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
什么是索引？
索引是存储引擎用于提高数据库表的访问速度的一种「数据结构」。
查询一个表所有的索引：show index from test_excel
查询一条语句是否走了索引：explain select * from test_excel，加上explain关键字即可；
优点：
加快数据查找的速度
为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
加快表与表之间的连接
缺点：
建立索引需要占用物理空间
会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长
索引的作用？
数据库表存在大量数据的情况下，索引能极大的加快表数据的查询速度，减少IO操作次数，降低资源的消耗，是一种典型的以空间换时间的处理方法；
数据是存储在磁盘上的，查询数据时。
如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。
有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。
索引的数据结构主要有「B+树」和「哈希表」
哈希索引
哈希索引是基于「哈希表」实现的
对于每一行数据，存储引擎会对索引列进行哈希计算得到「哈希码」
并且哈希算法要尽量保证不同的列值计算出的「哈希码值」是不同的，将哈希码的值作为哈希表的key值
将指向数据行的「指针」作为哈希表的value值。这样查找一个数据的时间复杂度就是「O(1)」，一般多用于精确查找。

共享锁和排他锁是什么
共享锁
例子：我们进入洗手间只是想洗手的话，我们一般不会锁门。而其他人也可以进来洗手、化妆等。但是，其他人是不可以进来上厕所的。这就是共享锁，也叫 读锁。
就是只读不写。
排他锁
例子：如果我们进入洗手间是为了上厕所，那么任何人不能再进来做任何事。这就是排他锁，也叫 写锁。

事务四大特性：
事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
原子性：指事务包含的所有操作要么全部成功，要么全部失败回滚。
一致性：指一个事务执行之前和执行之后都必须处于一致性状态。 比如a与b账户共有1000块，两人之间转账之后无论成功还是失败， 它们的账户总和还是1000。
隔离性：跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
持久性：指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。

truncate、delete与drop区别？
相同
truncate和不带where子句的delete、以及drop都会删除表内的数据。
drop、truncate都是DDL语句（数据定义语言），执行后会自动提交。
不同
truncate 和 delete 只删除数据不删除表的结构；
drop 语句将删除表的结构被依赖的约束、触发器、索引；
一般来说，执行速度: drop > truncate > delete。

having 和 where区别？
二者作用的对象不同，where子句作用于「表和视图」，having作用于「组」。
where在数据「分组前」进行过滤，having在数据「分组后」进行过滤。
having的用法 ：
大白话就是先通过sql语句把所有数据查询出来，再用 group by 进行分组，然后把分完组的数据用聚合函数进行统计，只不过查询语句和聚合函数之间需要用having连接；（group by 、having、聚合函数通常一起使用）
结构：查询语句+group by+ having +聚合函数统计
SELECT emp_no, count(salary) t #在工资表里查找员工编号、统计发工资的次数（取别名t）
from salaries
where creat_date between '2020-01-01' and '2020-12-31'
group by emp_no #通过员工编号编组
HAVING t>15 #查询发工资次数大于15次的
语句执行的顺序
select 语句执行顺序大致是：
where（数据查询） -> group by（数据编组） -> having（结果过滤） -> order by（排序）

查询执行流程：
查询语句的执行流程如下： 权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。
举个例子，查询语句如下：
select * from user where id > 1 and name = '温大大';
首先检查权限，没有权限则返回错误；
MySQL8.0以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
两种执行方案，先查 id > 1 还是 name = '大彬'，优化器根据自己的优化算法选择执行效率最好的方案；
校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。
更新执行流程：
更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）
举个例子，更新语句如下：
update user set name = '温大大' where id = 1;
先查询到 id 为1的记录，有缓存会使用缓存。
拿到查询结果，将 name 更新为大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 prepare状态。
执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为commit状态。
更新完成。