Fundamentos de las Bases de Datos, grados I. Informática e I.

Multimedia 1

ONLINE STORE DATABASE

province ( article ( pack (
codp varchar(2), cod varchar(7), cod varchar(7) )
name varchar(25) ) name varchar(45), PK (cod)
PK (codp) rrp decimal(7,2), FK (cod) —> article
brand varchar(15),
locality ( image blob, part_of (
codm varchar(4), urlimage varchar(100), pack varchar(7),
town varchar(50), specifications text) article varchar(7))
province varchar(2)) PK (cod) PK (pack,article)
PK (codm,province) FK (brand) —> brand FK (article) —> article
FK (province) —> province FK (pack) —> pack
NNV (town) camera (
cod varchar(7), stock (
uuser ( resolution varchar(15), article varchar(7),
email varchar(50), sensor varchar(45), available int(11),
name varchar(35), type varchar(45), delivery set( 'Unlisted', 'Shortly', '24
surnames varchar(55), factor varchar(10), hours', '3/4 days', '1/2 weeks' ) )
dni varchar(12), lens varchar(15), PK (article)
telephone varchar(15), screen varchar(20), FK (article) —> article
street varchar(45), zoom varchar(40))
street2 varchar(45), PK (cod) basket (
postcode varchar(5), FK (cod) —> article article varchar(7),
town varchar(4), user varchar(50),
province varchar(2), tv ( date datetime )
birthday date) cod varchar(7), PK (article,user)
PK (email) panel varchar(45), FK (article) —> article
AltK (dni) screen smallint(6), FK (user) —> user
FK (town, province) —> locality NNV resolution varchar(15),
NNV (surnames) hdreadyfullhd varchar(6), orrder (
NNV (name) dtt tinyint(1) ) numOrder int(11),
PK (cod) user varchar(50),
deliveryaddress ( FK (cod) —> article date datetime )
email varchar(50), PK (numOrder)
street varchar(45), mmemory ( FK (user) —> user NNV
street2 varchar(45), cod varchar(7), NNV (date)
postcode varchar(5), type varchar(30) )
town varchar(4), PK (cod) linorder (
province varchar(2)) FK (cod) —> article numOrder int(11),
PK (email) line int(11),
FK (town, province) —> locality NNV article varchar(7),
FK (email) —> usermarca ( lens ( price decimal(9,2),
cod varchar(7), amount int(11) )
marca varchar(15),
type varchar(15), PK (line,numOrder)
empresa varchar(60), frame varchar(15), FK (article) —> article NNV
logo blob ) focal varchar(10), FK (numOrder) —> order
CP (marca) aperture varchar(10), NNV (price)
specials varchar(35) )
brand ( PK (cod)
brand varchar(15), FK (cod) —> article
company varchar(60),
logo blob )
PK (brand)
 Fundamentos de las Bases de Datos, grados I. Informática e I. Multimedia 2

DEFINITION AND MANIPULATION DATE AND TIME FUNCTIONS

column nombre type [restriction] NOW() System date and time
CREATE [ TEMPORARY ] type char(n), varchar(n), int, decimal(d,c), date, datetime DATE_FORMAT(d,f) Convert date d to string with format f
TABLE [IF NOT EXISTS] restriction [ not ] null STR_TO_DATE(d,f) Convert string d with format f to date datatype
table DAY(d), DAYOFWEEK(d), DAYNAME(d),
primary key (x)
MONTH(d), YEAR(d), HOUR(d), MINUTE(d), Returns parts of a date d
(columns, restrictions) foreign key (x) references T(y) SECOND(d)
[ engine=innodb ] [ on {update|delete} {set null|cascade} ] DATEDIFF(d1,d2) Number of days between dates d1 and d2
unique (x) Adds to date d the amount x of years, months, etc.
DROP [ TEMPORARY ] TABLE [IF EXISTS] table DATE_ADD(d,INTERVAL x intrl) (intrl = YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND)
INSERT INTO table [ (columnList) ] VALUES (valueList) select date_add('2019-01-01', INTERVAL 3 DAY)
INSERT INTO table [ (columnList) ] ordenSelect DATE FORMATS
DELETE FROM table [ WHERE condition ]
UPDATE table SET assignments [ WHERE condition ] wildcard description
%a Week day (Sun..Sat) %l hour (1..12)
SELECT [ DISTINCT ] columnList %b month (Jan..Dec) %M Month name (January..December)
FROM tableList %c month (0..12) %m Month number (00..12)
[ [ LEFT | RIGHT | NATURAL ] JOIN table ON condition ] %d Day of the month (0..31) %s Seconds (00..59)
[ WHERE condition ] %H hora (0..23) %u week (00..53), monday first day of the week
%h hour (1..12) %W Week day name (Sunday..Saturday)
[ GROUP BY columnList [ HAVING condition ] ]
%I hour (1..12) %w weekcday (0=Sunday..6=Saturday)
[ ORDER BY column [ ASC | DESC ][, column [ ASC | DESC ] ]
%i minutes (0..59) %Y Year, four digits
%k hour (0..23) %y Year two digits
Operators and comparison
MORE FUNCTIONS
= AND BETWEEN LIKE %,_ UNION op ALL +, -, *, /
> OR IN UNION ALL op SOME DIV ROUND( expr,n ), TRUNCATE( expr,n ) redondea/trunca expr a n decimales
< NOT EXISTS op ANY %, MOD ABS( expr ) valor absoluto de expr
>= (op=comparación) MOD(N,M) residuo de N/M
<= CONCAT(str1,str2,...) concatena strings de caracteres
< >, !=
IS NULL EXAMPLES
column IS [ NOT ] NULL create table T (c1 int, c2 varchar(25) not null, select t1.x, y, date_format(now(),'%d/%m/%Y')
expresión [ NOT ] BETWEEN expresión AND expresión primary key (c1), foreign key (c2) references from table1 t1, table2 t2 where t1.x=t2.x
expresión [ NOT ] IN { (valueList) | consulta } V(x) on update cascade; select x, count(*) from T where y=a
column [ NOT ] LIKE pattern drop table T; group by x having sum(z) = 100;
insert into T (c1, c2) values (1,'Hello'); select x from T where z = select max(y) from T2;
Patter uses % and _ wildcards , any string and any character. insert into T values (1,'Hello'); select x from T union select y from T2;
insert into T (c1, c2) (select x, y from V);
AGGREGATES delete from T where c1 = 1; select x, funcion(b) from T group by x
update T set c1=2, c2='Goodbye' where c3>30; having sum(z) >= ALL (select ...);
COUNT( * ) Number of rows select x, funcion(b) from T where exists (select ...);
COUNT( [DISTINCT] expr ) Number of (disctinct) not null values in expr select x from T where z like 'V_Z%';
select x,y from T order by x DESC,y DESC; select x from T left join S on (T.a = S.b)
SUM( DISTINCT expr ) Sum of values in expr
select x,y from T order by 2;
AVG( DISTINCT expr ) Average of values in expr
select col1 alias1,col2 alias3 from table t;
MIN( expr ) Mínimum of values in expr select 'Hello',x, 'goodbye',y from T;
MAX( expr ) Máximum of values in expr