1.(SQL43)将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01
- 表更新语句结构:UPDATE 表名 SET 字段 = 值 WHERE 过滤条件
UPDATE titles_test
SET to_date = NULL, from_date = '2001-01-01'
WHERE to_date = '9999-01-01';
2.(SQL8)请你找出所有员工具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示:
- 常规解法,使用DISTINCT和ORDER BY:
SELECT DISTINCT salary
FROM salaries
WHERE to_date='9999-01-01'
ORDER BY salary DESC
- 也可以使用GROUP BY:按照salary进行分组
SELECT salary
FROM salaries
WHERE to_date='9999-01-01'
GROUP BY salary
ORDER BY salary DESC
- 对于distinct与group by的使用:
1.当对系统的性能高并且数据量大时使用group by
2.当对系统的性能不高时或者使用数据量少时两者借口
3.尽量使用group by
3.(SQL53)按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
- 聚合函数group_concat(X,Y)
- 其中X是要连接的字段,Y是连接时用的符号,可省略,默认为逗号
select dept_no, group_concat(emp_no)
as employees
from dept_emp
group by dept_no;
4.(SQL37)对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
1. sqlite创建单列索引语法为:
CREATE INDEX index_name
ON table_name (column_name);
CREATE UNIQUE INDEX index_name
on table_name (column_name);
CREATE UNIQUE INDEX uniq_idx_firstname on actor (first_name);
CREATE INDEX idx_lastname ON actor (last_name);
2. mysql中:
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
ALTER TABLE tbl_name ADD INDEX index_name (col_list);
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
alter table actor add unique uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name);
5.(SQL15)将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat,请用||实现,mysql支持concat)
select concat(last_name,"'",first_name) as name
from employees