题目一:批量插入数据
题目要求:
对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
actor_id first_name last_name last_update 1 PENELOPE GUINESS 2006-02-15 12:34:33 2 NICK WAHLBERG 2006-02-15 12:34:33
思路:
MySQL中批量添加数据的语法格式:
INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), (值1, 值2, 值3, ...),...;
运行代码示例:
INSERT INTO actor(actor_id,
first_name,
last_name,
last_update)
VALUES(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');
题目二:删除emp_no重复的记录,只保留最小的id对应的记录
题目要求:
删除emp_no重复的记录,只保留最小的id对应的记录。
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);
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
删除后titles_test表为(注:最后会select * from titles_test表来对比结果)
思路:
删除语句:DELETE FROM 表名 WHERE 条件;
注意本题不能直接使用:
delete from titles_test where (emp_no,id) not in ( select emp_no,min(id) from titles_test group by emp_no )
删除,因为MySQL中不允许在子查询的同时删除表数据。
运行代码示例:
delete from titles_test where (emp_no,id) not in (
select * from (
select emp_no,min(id) from titles_test group by emp_no
) t
)
题目三:将titles_test表名修改为titles_2017
题目要求:
将titles_test表名修改为titles_2017。
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);
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
思路:
修改表名可以用:
RENAME TABLE 旧表名 TO 新表名;
运行代码示例:
RENAME TABLE titles_test TO titles_2017
题目四:将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005
题目要求:
将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);
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
思路:
更新语句:UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
WHERE 条件;
运行代码示例:
UPDATE titles_test
SET emp_no = REPLACE(emp_no, 10001, 10005)
WHERE id = 5;
题目五:将所有to_date为9999-01-01的全部更新为NULL
题目要求:
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
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 ); insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'), ('2', '10002', 'Staff', '1996-08-03', '9999-01-01'), ('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'), ('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'), ('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'), ('6', '10002', 'Staff', '1996-08-03', '9999-01-01'), ('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');更新后的值:
titles_test 表的值:
id emp_no title from_date to_date 1 10001 Senior Engineer 2001-01-01 NULL 2 10002 Staff 2001-01-01 NULL 3 10003 Senior Engineer 2001-01-01 NULL 4 10004 Senior Engineer 2001-01-01 NULL 5 10001 Senior Engineer 2001-01-01 NULL 6 10002 Staff 2001-01-01 NULL 7 10003 Senior Engineer 2001-01-01 NULL 后台会执行下面SQL语句得到输出,判断正确:
select count(*) from titles_test where from_date='2001-01-01' and to_date is NULL;
思路:
更新语句:UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
WHERE 条件;
运行代码示例:
UPDATE titles_test
SET to_date = NULL, from_date = '2001-01-01'
WHERE to_date = '9999-01-01';
题目六:创建一个actor表,包含如下列信息
题目要求:
创建一个actor表,包含如下列信息
列表 类型 是否为NULL 含义 actor_id smallint(5) not null 主键id first_name varchar(45) not null 名字 last_name varchar(45) not null 姓氏 last_update timestamp not null 最后更新时间,默认是系统的当前时间
思路:
建表语句:
CREATE TABLE 表名 ( 列名1 数据类型 [约束], -- 示例: id INT NOT NULL AUTO_INCREMENT 列名2 数据类型 [约束], -- 示例: name VARCHAR(100) NOT NULL 列名3 数据类型 [约束], -- 示例: email VARCHAR(255) UNIQUE 列名4 数据类型 [约束], -- 示例: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 列名5 数据类型 [约束], -- 示例: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (列名1), -- 设置主键 UNIQUE (列名3), -- 设置唯一约束 FOREIGN KEY (外键列) REFERENCES 外表名(外表列) -- 设置外键约束 );
数据类型:表示列的数据类型,比如
INT
、VARCHAR(n)
、TIMESTAMP
等约束:
NOT NULL
:列不允许为空。AUTO_INCREMENT
:自动增长,用于主键等标识列。DEFAULT
:为列提供默认值。UNIQUE
:确保列中的值是唯一的。PRIMARY KEY
:指定该列为主键,表中的每一行都需要唯一标识。FOREIGN KEY
:设置外键约束,确保引用的列存在于另一张表中。
运行代码示例:
CREATE TABLE actor (
actor_id SMALLINT(5) NOT NULL, -- 主键id
first_name VARCHAR(45) NOT NULL, -- 名字
last_name VARCHAR(45) NOT NULL, -- 姓氏
last_update DATE NOT NULL , -- 最后更新时间,默认是系统当前时间
PRIMARY KEY (actor_id) -- 设置 actor_id 为主键
);
题目七:创建一个actor_name表
题目要求:
对于如下表actor,其对应的数据为:
actor_id first_name last_name last_update 1 PENELOPE GUINESS 2006-02-15 12:34:33 2 NICK WAHLBERG 2006-02-15 12:34:33 请你创建一个actor_name表,并且将actor表中的所有first_name以及last_name导入该表.
actor_name表结构如下,题目最后会查询actor_name表里面的数据来对比结果输出:
列表 类型 是否为NULL 含义 first_name varchar(45) not null 名字 last_name varchar(45) not null 姓氏
思路:
先用 CREATE TABLE语句创建actor_name表,包含first_name与last_name字段,然后用 INSERT INTO ... SELECT... 语句向actor_name表插入另一张表中的数据
运行代码示例:
create table if not exists actor_name(
first_name varchar(45) not null,
last_name varchar(45) not null); -- 创建表
insert into actor_name
select first_name,last_name
from actor; -- 插入查询结果