牛客网数据库SQL实战（二）

SQL32 将employees表的所有员工的last_name和first_name拼接起来作为Name

SELECT CONCAT(last_name, ' ', first_name) AS name
FROM employees

SQL33 创建一个actor表，包含如下列信息

CREATE TABLE actor (
    `actor_id` SMALLINT(5) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(45) NOT NULL,
    `last_name` VARCHAR(45) NOT NULL,
    `last_update` DATE NOT NULL,
    PRIMARY KEY(`actor_id`)
)

SQL34 批量插入数据

INSERT INTO actor VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')

SQL35 批量插入数据，不使用replace操作

INSERT IGNORE INTO actor VALUES (3, 'ED', 'CHASE', '2006-02-15 12:34:33')

SQL36 创建一个actor_name表

CREATE TABLE actor_name (
    `first_name` VARCHAR(45) NOT NULL COMMENT '名字',
    `last_name` VARCHAR(45) NOT NULL COMMENT '姓氏'
);

INSERT INTO actor_name
SELECT first_name, last_name
FROM actor;

SQL37 对first_name创建唯一索引uniq_idx_firstname

ALTER TABLE actor ADD UNIQUE INDEX uniq_idx_firstname(`first_name`);
ALTER TABLE actor ADD INDEX idx_lastname(`last_name`);

SQL38 针对actor表创建视图actor_name_view

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor

SQL39 针对上面的salaries表emp_no字段创建索引idx_emp_no

SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no = '10005'

SQL40 在last_update后面新增加一列名字为create_date

ALTER TABLE actor ADD COLUMN (
    `create_date` DATETIME NOT NULL DEFAULT '2020-10-01 00:00:00'
)

SQL41 构造一个触发器audit_log

CREATE TRIGGER audit_log
AFTER INSERT ON employees_test
FOR EACH ROW
INSERT INTO audit(EMP_no, NAME) VALUES (NEW.id, NEW.name)

SQL42 删除emp_no重复的记录，只保留最小的id对应的记录。

DELETE FROM titles_test
WHERE id IN (
    SELECT *
    FROM (
        SELECT t1.id
        FROM titles_test AS t1
        WHERE t1.id > (
            SELECT MIN(id)
            FROM titles_test AS t2
            WHERE t2.emp_no = t1.emp_no
        )
    ) temp
)

SQL43 将所有to_date为9999-01-01的全部更新为NULL

UPDATE titles_test
SET from_date = '2001-01-01', to_date = NULL

SQL44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

REPLACE INTO titles_test
SELECT id, '10005', title, from_date, to_date
FROM titles_test
WHERE id = '5'

SQL45 将titles_test表名修改为titles_2017

ALTER TABLE titles_test RENAME titles_2017

SQL46 在audit表上创建外键约束，其emp_no对应employees_test表的主键id

ALTER TABLE audit ADD FOREIGN KEY (`emp_no`) REFERENCES employees_test(`id`)

SQL48 将所有获取奖金的员工当前的薪水增加10%

UPDATE salaries SET salary = 1.1 * salary
WHERE to_date = '9999-01-01'

SQL50 将employees表中的所有员工的last_name和first_name通过引号连接起来。

SELECT CONCAT(last_name, '\'', first_name)
FROM employees

SQL51 查找字符串 10,A,B 中逗号,出现的次数cnt

SELECT CHAR_LENGTH('10,A,B') - CHAR_LENGTH(REPLACE('10,A,B', ',', ''))

SQL52 获取Employees中的first_name

SELECT first_name
FROM employees
ORDER BY SUBSTR(first_name, -2) ASC

SQL53 按照dept_no进行汇总

SELECT dept_no, GROUP_CONCAT(emp_no)
FROM dept_emp
GROUP BY dept_no
ORDER BY dept_no

SQL54 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

SELECT AVG(salary) AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01' AND salary <> (
    SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01'
) AND salary <> (
    SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01'
)

SQL55 分页查询employees表，每5行一页，返回第2页的数据

SELECT *
FROM employees
LIMIT 5 OFFSET 5

SQL57 使用含有关键字exists查找未分配具体部门的员工的所有信息。

SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT r.dept_no FROM dept_emp r
    WHERE r.emp_no = e.emp_no
)

SQL59 获取有奖金的员工相关信息。

SELECT
    e.emp_no,
    e.first_name,
    e.last_name,
    b.btype,
    s.salary,
    (CASE btype
     WHEN '1' THEN 0.1 * s.salary
     WHEN '2' THEN 0.2 * s.salary
     ELSE 0.3 * s.salary
     END) AS bouns
FROM
    employees e INNER JOIN emp_bonus b ON e.emp_no = b.emp_no
    INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE s.to_date = '9999-01-01'

SQL60 统计salary的累计和running_total

SELECT emp_no, salary, SUM(salary) OVER (ORDER BY emp_no) AS running_total
FROM salaries
WHERE to_date = '9999-01-01'