sql练习-DML

sql练习

DML

建表

33--创建一个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 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 date not null 
);

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

--mysql
create table if not exists `actor`(
    actor_id smallint(5) primary key not null comment '主键id',
    first_name varchar(45) not null comment '名字',
    last_name varchar(45) not null comment '姓氏',
    last_update date not null comment '日期'
)engine=innodb default charset=utf8;

--36对于如下表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 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;

插入数据

--34请你对于表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')

--35对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
actor_id	first_name	last_name	last_update
'3'     	'ED'	   'CHASE'	   '2006-02-15 12:34:33'

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

视图

--38针对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 as first_name_v, last_name last_name_v
from actor;

删除

--42删除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);

delete from titles_test
where id not in
(select min(id) from titles_test group by emp_no);

修改表名

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

alter table titles_test rename to titles_2017;

--

修改新列

--40存在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');

更新

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

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

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

--emp_bonus里面的emp_no不都是当前获奖的所有员工

索引

--37针对如下表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

CREATE UNIQUE INDEX uniq_idx_firstname ON ACTOR(first_name);-- 唯一索引
CREATE INDEX idx_lastname ON ACTOR(last_name);   -- 普通索引
-- CREATE INDEX Index_name on Table_name(col_name);

--

--

外键

--46在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 if exists audit;

create table audit(
    EMP_no int not null,
    create_date datetime not null,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID)
);

alter table audit 
add foreign KEY(emp_no) 
references employees_test(id)

--

--

触发器

--41构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
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,
NAME TEXT NOT NULL
);

create trigger audit_log
after insert on employees_test
for each row 
begin
insert into audit values(new.id,new.name);
end

--