SQL编程：基本操作（CRUD）

最新推荐文章于 2024-07-15 10:51:55 发布

信仰..

最新推荐文章于 2024-07-15 10:51:55 发布

阅读量509

点赞数

分类专栏： SQL编程

本文链接：https://blog.csdn.net/haut_ykc/article/details/110121954

版权

SQL编程专栏收录该内容

13 篇文章 1 订阅

订阅专栏

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

列表	类型	是否为NULL	含义
actor_id	smallint(5)	not null	主键id
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏
last_update	date	not null	日期

CREATE table actor
(
    actor_id smallint(5) NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update date NOT NULL
)

请你对于表actor批量插入如下数据(不能有2条insert语句哦!)

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

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');

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

actor_id	first_name	last_name	last_update
'3'	'ED'	'CHASE'	'2006-02-15 12:34:33'

insert ignore into
actor
values
(3,'ED','CHASE','2006-02-15 12:34:33');

对于如下表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表结构如下：

列表	类型	是否为NULL	含义
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏

create table 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;

针对如下表actor结构创建索引：

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，

mysql支持ALTER TABLE创建索引)

CREATE TABLE actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update datetime NOT NULL);

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname。

sqlite创建索引：

create unique index 
uniq_idx_firstname
on
actor (first_name);
create index
idx_lastname 
on
actor (last_name);

MySQL创建索引：

alter table actor
add unique
uniq_idx_firstname (first_name);
alter table actor
add index
idx_lastname (last_name);

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

CREATE TABLE actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update datetime NOT NULL);

create view actor_name_view
as 
select 
first_name first_name_v,
last_name last_name_v
from 
actor;

存在actor表，包含如下列信息：

CREATE TABLE actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update datetime NOT NULL);

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'

alter table actor
add
create_date datetime not null default '2020-10-01 00:00:00';

将所有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);

update
titles_test
set
to_date=null,
from_date='2001-01-01'
where
to_date='9999-01-01';

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错了。

CREATE TABLE 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
titles_test
set
emp_no=replace(emp_no,10001,10005)
where
id=5;

将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');

alter table
titles_test
rename to
titles_2017;

请你写出更新语句，将所有获取奖金的员工当前的(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 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
salaries.to_date='9999-01-01'
and
emp_no in
(
    select emp_no from emp_bonus
);

将employees表中的所有员工的last_name和first_name通过(')连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)
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`));

select 
concat(last_name,'''',first_name)
as name 
from 
employees;

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
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`));

select first_name
from
employees
order by
substr(first_name,-2,2)
asc;

使用MySQL中的right，表示取右边第几位的意思。

select first_name
from
employees
order by
right(first_name,2)
asc;

查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。
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`));

select
avg(a.salary) as avg_salary
from
salaries as a
where
a.to_date='9999-01-01'
and
a.salary not in
(
    select
    min(a1.salary)
    from 
    salaries as a1
    where
    a1.to_date='9999-01-01'
)
and
a.salary not in
(
    select
    max(a2.salary)
    from 
    salaries as a2
    where
    a2.to_date='9999-01-01'
)