数据库练习（一）

联合查询

SELECT e.name,s.salary
FROM employee e -- employee 员工信息表
JOIN salaries s -- salaries 工资表
ON s.emp_no = e.emp_no -- emp_no 表示员工id

给成绩排序，分数相同排名并列

SELECT stu_id,score,DENSE_RANK() OVER(ORDER BY s.score) DESC rank
FROM scores -- scores 学生成绩表

获取工资第二多的员工id

SELECT emp_id
FROM salaries s
ORDER BYsalary DESC
LIMIT 1,1 -- 也即 LIMIT 1 OFFSET 1

查询迟到次数>=3次的学生的迟到次数

SELECT stu_id,sum(stu_id) AS times
FROM t_late
GROUP BY stu_id
HAVING times >= 3

获取员工其当前的薪水比其manager当前薪水还高的相关信息
地址：https://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef

将姓和名用空格拼接起来

-- Sqlite
SELECT first_name||' '||last_name
FROM info -- 信息表
-- MySql
SELECT CONCAT(first_name,' ',last_name)
FROM info

批量插入数据，如果存在重复数据，忽略(不报错)

-- Sqlite
insert or ignore into info values
(1,'smartgiel',18),
(2,'zhangsan',3)
-- MySql
insert ignore into info values
(1,'smartgiel',18),
(2,'zhangsan',3)

从一个表获取数据放到另一个表

replace into table1(first_name,last_name) select first_name,last_name from table2
-- 用法类似 insert into, 可以如果是全部插入，可以不写字段
replace into table1 select first_name,last_name from table2

添加索引

-- Sqlite
--- 创建普通索引
CREATE INDEX index_name ON table_name(col_name)
--- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name)
--- 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name(col_name)

-- MySql
--- 创建普通索引
ALTER TABLE info ADD INDEX idx_name(col_name)
--- 创建唯一索引
ALTER TABLE info ADD UNIQUE INDEX(col_name)
--- 创建主键索引
ALTER TABLE info ADD PRIMARY KEY (col_name)
--- 创建全文索引
ALTER TABLE info ADD FULLTEXT(col_name)

创建视图

CREATE VIEW v_stu_info AS
SELECT first_name,last_name
FROM stu_info -- 学生信息表

使用强制索引搜索数据

-- Sqlite
select *
from stu_info
indexed by idx_firstname  -- idx_firstname: first_name字段索引名
where id = 1
-- MySql
select *
from stu_info
force index(idx_firstname)
where id = 1

在last_name后增加列phone

ALTER TABLE stu_info ADD [COLUMN] phone VARCHAR(11) NOT NULL AFTER last_name

构造一个触发器：每插入一条迟到信息，更新scores表，使该学生score -10

CREATE TRIGGER punishment
AFTER INSERT ON late_for_class -- late_for_class :学生迟到信息表
FOR EACH ROW
UPDATE scores SET score = score - 10 WHERE stu_id = new.id and score > 0;