SQL alapok

SQL nyelvi elemek

• DDL (Data Definition Language): Adatszerkezetek definiálása (táblák, séma, nézetek, indexek).
  • CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): Adatok manipulálása (beszúrás, módosítás, törlés).
  • INSERT, UPDATE, DELETE, MERGE
• QL (Query Language / DQL – Data Query Language): Adatok lekérdezése.
  • SELECT

---

Példa kódok

Figyelem!

Minden DML művelet után (INSERT, UPDATE, DELETE) szükséges a COMMIT utasítást kiadni, különben a változtatások nem kerülnek mentésre! A DDL műveletek (CREATE, ALTER, DROP) esetében implicit (automatikus) COMMIT hajtódik végre.

drop table people;

create table people (
 -- auto increment id
 -- id         integer generated always as identity (start with 1 increment by 1),
    id         integer not null primary key,
    name       varchar2(250) not null,
    birth_date date default sysdate,
    is_male    char(1) not null,
    constraint ck_is_male check(is_male in ('I', 'N'))
);


alter table people add height integer not null;
alter table people add constraint ck_height check(height > 0);
alter table people drop column height;
alter table people add constraint uq_name unique(name);
alter table people add city varchar2(250) not null;


insert into people (id, name, birth_date, is_male, city) 
    values (1, 'mark', TO_DATE('2003-02-13', 'YYYY-MM-DD'), 'I', 'hort');

-- Default value for birth_date
insert into people (id, name, is_male, city) 
    values (2, 'mark2', 'I', 'hort');

insert into people values (3, 'mark3', TO_DATE('2003-02-13', 'YYYY-MM-DD'), 'I', 'hort');

commit;

update people set name = 'mark1' where id = 1;
commit;

-- Basic queries
select * from people;
select name, height from people;
select * from people where height > 20;
select * from people where height between 10 and 20;
select * from people order by height;

-- Advanced queries
select max(age) from people;
select sum(age) from people;
select count(*), city from people group by city;
select count(*), city from people group by city having count(*) > 1;
select name from people where height = (select max(height) from people);
-- Select people whose name starts with 'r'
select * from people where name like 'r%';


delete from people where id = 3;
commit;


-- Foreign keys
create table table1 (
    id   integer not null primary key,
    name varchar2(250) not null
);

create table table2 (
    id   integer not null primary key,
    table1_id integer not null,
    name varchar2(250) not null,
    constraint fk_table2_table1_id foreign key(table1_id) references table1(id)
);

select * from table1 t1 inner join table2 t2 on t1.id = t2.table1_id;