☺☺☺
文章目录
- 六道题
- 考查知识点
- 一、 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现
- 二、将titles_test表名修改为titles_2017
- 三、在audit表上创建外键约束,其emp_no对应employees_test表的主键id(audit已经创建,需要先drop)
- 四、请你写出更新语句,将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%
- 五、针对库中的所有表生成select count(*)对应的SQL语句
- 六、将employees表中的所有员工的last_name和first_name通过(')连接起来
六道题
考查知识点
题号 | 知识点 |
---|---|
1 | 数据表中数据的替换:UPDATE <表名> SET <列1>=REPLACE(<要被替换的列名>, <要被替换的数据>, <替换的数据>) |
2 | 数据表的表名修改:ALTER TABLE <旧表名> RENEME TO <新表名> |
3 | 给表格添加外键:ALTER TABLE <表名> ADD FOREIGN KEY(<内键>) REFERENCES <外键> |
4 | 修改数据表中的数据:UPDATE <表名> SET <列名> = <某表达式或者某数值> [WHERE限制条件需要时可加上] |
5 | 数据表中的数据的字符串链接用mysql用concat ,sqlite用双竖杠"||" |
6 | 数据表中数据的字符串链接用concat(,,,) 函数 |
总而言之SQL语言对数据库的操作分为: 对数据库的创建及使用、对数据表的创建及使用、对数据表中的数据的操作,这么三中类型。其中对数据表中数据的操作,比如INSERT INTO
/ UPDATE
/ DELETE
就不需要加这个TABLE关键字。
一、 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现
- 题目描述:
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);
- 分析:
考察的是SQL中的REPLACE()替换函数的使用,语法如下:
REPLACE(<字段名>, <要被替换的内容>, <替换的内容>)
然后对数据表中数据的修改用的是UPDATE <表名> SET <列1>=<表达式1或值1>,<列2>=<表达式2或值2>
- 代码:
UPDATE titles_test
SET emp_no = REPLACE(emp_no, 10001, 10005)
WHERE id = 5
【重要】知识点:SQL中替换函数replace()的使用
REPLACE(String,from_str,to_str)
即:将String中所有出现的from_str替换为to_str。
二、将titles_test表名修改为titles_2017
- 代码:
ALTER TABLE titles_test
RENAME TO titles_2017
【重要】知识点:考察随数据表中标名字的修改
对数据表内容的修改ALTER TABLE ...
对表明字的修改用RENAME..TO...
三、在audit表上创建外键约束,其emp_no对应employees_test表的主键id(audit已经创建,需要先drop)
- 题目描述:
在audit表上创建外键约束,其emp_no对应employees_test表的主键id。
(audit已经创建,需要先drop)
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
);
(注:创建表的时候,字段的顺序不要改变)
- 分析:
本题考察给表中的键添加外键 ,在mysql中直接用ALTER实现即可,但在牛客系统的sqlite中得在新建表格的时候同时添加外键 - 代码:
DROP TABLE audit;
CREATE TABLE audit
(
EMP_no INT NOT NULL,
create_date datetime NOT NULL,
FOREIGN KEY(EMP_no) REFERENCES employees_test(ID)
);
【重要】知识点:考察给表添加外键
MySQL使用ALTER添加外键的语句表达式为:
ALTER TABLE tablename
ADD FOREIGN KEY (<现键>) REFERENCES <外键>
四、请你写出更新语句,将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%
- 题目描述:
请你写出更新语句,将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
create table emp_bonus(
emp_no int not null,
btype smallint not null);
CREATE TABLEsalaries
(
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实现对数据表中的数据的修改,语法如下:
UPDATE <表名> #注意这里没有TABLE
SET <列名>=<数据或表达式>
[WHERE <限制条件>]
- 代码:
UPDATE salaries
SET salary = salary*1.1
WHERE emp_no IN
(SELECT emp_no FROM emp_bonus)
AND to_date = '9999-01-01'
- 问题:这个代码为什么没有通过?是不对吗?
UPDATE salaries
SET salary = salary *1.1
WHERE emp_no IN
(SELECT s.emp_no
FROM salaries s, emp_bonus e
WHERE s.to_date = '9999-01-01'
AND s.emp_no = e.emp_no
)
五、针对库中的所有表生成select count(*)对应的SQL语句
- 题目描述:
针对库中的所有表生成select count(*)对应的SQL语句,如数据库里有以下表,
(注:在 SQLite 中用 “||” 符号连接字符串,无法使用concat函数)
employees
departments
dept_emp
dept_manage
salaries
titles
emp_bonus
那么就会输出以下的样子:
- 分析:
1、在 SQLite 系统表sqlite_master
中可以获得所有表的索引,其中字段name
是所有表的名字,而且对于自己创建的表而言,字段type
永远是'table'
2、在 SQLite 中用 “||” 符号连接字符串
3、mysql中使用concat(a,b,c)
链接
select concat("select count(*) from "," ",table_name,";") as cnts
from (select table_name from information_schema.tables) as new;
- 代码:
SELECT "select count(*) from" || name || ";" AS cnts
FROM sqlite_master
WHERE type = 'table'
六、将employees表中的所有员工的last_name和first_name通过(’)连接起来
- 题目描述:
将employees表中的所有员工的last_name和first_name通过(’)连接起来。(不支持concat,请用||实现)
CREATE TABLEemployees
(
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
));
输出格式:
- 分析:
还是考察字符串的链接,直接用SELECT查询到要连接的字段,然后在sqlite中是用双竖杠进行字符串连接,mysql中仍然是用concat()函数即可。 - 代码:
SELECT last_name || "'" || first_name
FROM employees