@(跃迁之路)专栏

叨叨两句

1. 技术的精进不能只是简单的刷题，而应该是不断的“刻意”练习
2. 该系列改版后正式纳入【跃迁之路】专栏，持续更新

刻意练习——MySQL

2018.04.02

题目描述

DROP TABLE IF EXISTS test1;

CREATE TABLE test1 (id int(11) NOT NULL AUTO_INCREMENT,username varchar(20) NOT NULL,course varchar(20) NOT NULL,score bigint(20) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO test1 VALUES ('1', '张三', '数学', '34');

INSERT INTO test1 VALUES ('2', '张三', '语文', '44');INSERT INTO test1 VALUES ('3', '张三', '英语', '54');INSERT INTO test1 VALUES ('4', '李四', '数学', '134');INSERT INTO test1 VALUES ('5', '李四', '语文', '144');INSERT INTO test1 VALUES ('6', '李四', '英语', '154');INSERT INTO test1 VALUES ('7', '王五', '数学', '234');INSERT INTO test1 VALUES ('8', '王五', '语文', '244');INSERT INTO test1 VALUES ('9', '王五', '英语', '254');

查出以下结果

法1

SELECT    A.username,A.score as '数学',B.score as '语文',C.score as '英语'FROM (select username,course,score from test1 where course = '数学') A,(select username,course,score from test1 where course = '语文') B,(select username,course,score from test1 where course = '英语') CWHERE    A.username = B.usernameand B.username = C.username

法2【推荐】

select    username,sum(case course when '数学' then score else 0 end ) as '数学',sum(case course when '语文' then score else 0 end ) as '语文',sum(case course when '英语' then score else 0 end ) as '英语'FROM    test1group by username

2018.04.03

题目描述

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

CREATE TABLE employees_test(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);

CREATE TABLE audit(

EMP_no INT NOT NULL,create_date datetime NOT NULL);

DROP TABLE audit;CREATE TABLE audit(    EMP_no INT NOT NULL,    create_date datetime NOT NULL,    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

2018.04.04

由于视图 emp_v 的记录是从 employees 中导出的，所以要判断两者中相等的数据，只需要判断emp_no相等即可。方法一：用 WHERE 选取二者 emp_no 相等的记录SELECT em.* FROM employees AS em, emp_v AS ev WHERE em.emp_no = ev.emp_no方法二：用 INTERSECT 关键字求 employees 和 emp_v 的交集可参考：http://www.sqlite.org/lang_select.htmlSELECT * FROM employees INTERSECT SELECT * FROM emp_v方法三：仔细一想，emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录SELECT * FROM emp_v【错误方法：】用以下方法直接输出 *，会得到两张表中符合条件的重复记录，因此不合题意，必须在 * 前加表名作限定SELECT * FROM employees, emp_v WHERE employees.emp_no = emp_v.emp_no

2018.04.05

题目描述

将所有获取奖金的员工当前的薪水增加10%。create table emp_bonus(emp_no int not null,recevied datetime not null,btype smallint not null);CREATE TABLE salaries (emp_no int(11) NOT NULL,salary int(11) NOT NULL,from_date date NOT NULL,to_date date NOT NULL, PRIMARY KEY (emp_no,from_date));

UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN(SELECT s.emp_no FROM salaries AS s INNER JOIN emp_bonus AS eb ON s.emp_no = eb.emp_no AND s.to_date = '9999-01-01')

2018.04.06

题目描述

针对库中的所有表生成select count(*)对应的SQL语句CREATE TABLE employees (emp_no int(11) NOT NULL,birth_date date NOT NULL,first_name varchar(14) NOT NULL,last_name varchar(16) NOT NULL,gender char(1) NOT NULL,hire_date date NOT NULL,PRIMARY KEY (emp_no));create table emp_bonus(emp_no int not null,recevied datetime not null,btype smallint not null);CREATE TABLE dept_emp (emp_no int(11) NOT NULL,dept_no char(4) NOT NULL,from_date date NOT NULL,to_date date NOT NULL,PRIMARY KEY (emp_no,dept_no));CREATE TABLE dept_manager (dept_no char(4) NOT NULL,emp_no int(11) NOT NULL,from_date date NOT NULL,to_date date NOT NULL,PRIMARY KEY (emp_no,dept_no));CREATE TABLE salaries (emp_no int(11) NOT NULL,salary int(11) NOT NULL,from_date date NOT NULL,to_date date NOT NULL,PRIMARY KEY (emp_no,from_date));

输出格式:

cntsselect count(*) from employees;select count(*) from departments;select count(*) from dept_emp;select count(*) from dept_manager;select count(*) from salaries;select count(*) from titles;select count(*) from emp_bonus;

本题主要有以下两个关键点：1、在 SQLite 系统表 sqlite_master 中可以获得所有表的索引，其中字段 name 是所有表的名字，而且对于自己创建的表而言，字段 type 永远是 'table'，详情可参考：http://blog.csdn.net/xingfeng0501/article/details/78043782、在 SQLite 中用 “||” 符号连接字符串SELECT "select count(*) from " || name || ";" AS cntsFROM sqlite_master WHERE type = 'table'3.mysql使用concat进行字符串拼接