mysql - 输入带有前缀年份mysql的默认月份和日期
问题描述
嗨,我有这个代码,有人问我这个问题:演员的姓氏,按字母顺序排列,包括他们参与的电影的标题,以及他们参与电影制作时的年龄(通常为 6 月 30 日);
我已经以各种方式尝试了 datediff 函数,但它一直给我错误,据我所知,我不接受以这种方式编写的生产年份。如何将月份和日期设置为查询,然后使用 datediff?非常感谢那些会帮助我的人,我在浪费时间胡说八道。对不起,我还处于起步阶段
create database cinema;
use cinema;
create table participation(film varchar (3) not null ,actor varchar(5) not null);
create table actor (id_actor varchar(5) not null,name varchar(30), surname varchar(30) not null);
create table film (id_film varchar(3) not null,title varchar(30) not null, kind varchar (30) not null, producer varchar (5) not null, production_year year(4));
create table producer( id_producer varchar (5) not null, name varchar (30) ,surname varchar(30) not null);
alter table producer add primary key (id_producer);
alter table film add primary key (id_film);
alter table participation add primary key (film,actor);
alter table actor add primary key (id_actor);
alter table participation add constraint fk_pfilm foreign key (film) references film(id_film);
alter table participation add constraint fk_pactor foreign key (actor) references actor(id_actor);
alter table film add constraint fk_fproducer foreign key (producer) references producer (id_producer);
insert into producer(id_producer,name,surname) values ("0000A","Steven","Spielberg"),("0000B","Stanley","Kubrick"),("0000C","Ridley","Scott");
insert into actor(id_actor,name,surname) values ("000AA","Sylvester","Stallone"),("000AB","Brad","Pitt"),
("000AC","George","Clooney"),("000AD","Demi","Moore"),("000AE","Bruce","Willis"),
("000AF","Monica","Bellucci");
insert into film(id_film,title,kind,production_year,producer) values ("00A","Jurassic Park","avventura",'2000',"0000A"),("00B","Matrix","fantascienza",'2001',"0000B"),
("00C","Star Wars","fantascienza",'2000',"0000A"),("00D","Indiana Jones","avventura",'2002',"0000B"),("00E","Rambo","avventura",'2002',"0000A"),
("00F","Rocky I","sportivo",'2001',"0000A"),("00G","Rocky II","sportivo",'2004',"0000B");
insert into participation (film,actor) values ("00A","000AA"),("00B","000AB"),("00C","000AC"),("00D","000AD"),("00E","000AA"),
("00F","000AA"),("00G","000AB"),("00A","000AC"),("00B","000AA"),("00C","000AB"),("00D","000AB");
select * from participation;
select * from actor;
select * from film;
select * from producer;
alter table actor add column datebirth date;
update actor set datebirth='1946-07-06' where id_actor="000AA";
update actor set datebirth='1963-12-18' where id_actor="000AB";
update actor set datebirth='1961-05-06' where id_actor="000AC";
update actor set datebirth='1962-11-11' where id_actor="000AD";
update actor set datebirth='1955-03-19' where id_actor="000AE";
update actor set datebirth='1964-09-30' where id_actor="000AF";
create view vista as select * from (actor inner join participation on id_actor=participation.actor);
create view vista2 as select * from (vista inner join film on vista.film=film.id_film);
select surname,title,datediff(datebirth,production_year-06-30) as età from vista2;
解决方案
尝试替换anno_produzione-06-30
为:
concat(anno_produzione, '-06-30')
引擎可以将 ISO 日期表示形式转换为date
.
而且您不应该在字符串文字周围使用双引号。改用单引号,这是他们在 SQL 中的工作。
推荐阅读
- java - 为什么paintComponent多次绘制圆圈?
- java - Flink:java.io.NotSerializableException:redis.clients.jedis.JedisCluster
- c# - 如何在 SQL Server 2016 中安装全文搜索?
- android-studio - 仿真器错误:x86 仿真当前需要硬件加速
- javascript - 当多个样式附加到html中的给定单词时,如何使用范围API单击一次单词
- angular - 如何修复primeNG日历中的“未捕获(承诺)”错误
- python - Python 覆盖没有找到我自己的模块
- c# - 将 liteDB 文档存储在云(天蓝色)blob 存储中
- wolfram-mathematica - 无法使用 Solve[expression, variable] 函数求解变量
- java - 从java中的特定位置提取文本