| |
|
|
| |
для: beckham
(17.05.2013 в 01:18)
|
| |
SQL]create database firma;
use firma;
create table users<id_user int<15> ,FI0 varchar<40> NOT NULL,PRIMARY KEY< id_user>>;
describe users;
create table propusk
-> <id int<15> AUTO_INCREMENT,
-> id_user int<15> NOT NULL,
-> kpp int<1> NOT NULL,
-> data DATE,
-> vremya TIME,
-> PRIMARY KEY<id>,
-> FOREIGN KEY<id_user> REFERENGES users<id_user>>;
describe propusks;
INSERT INTO users VALUES<'23','Андрей Митрофанович Кижеватов'>;
INSERT INTO users VALUES<'17','Колчак Александр Василиевич'>;
INSERT INTO users VALUES<'32','Хрущев Никикта Сергеевич'>;
INSERT INTO users VALUES<'71','Анна Василиевна Темерева');
INSERT INTO propusk VALUES<NULL,'23','0','2013-03-04','10:05:00'>;
INSERT INTO propusk VALUES<NULL,'17','1','2013-03-04','10:06:30'>;
INSERT INTO propusk VALUES<NULL,'32','1','2013-03-04','10:16:40'>;
INSERT INTO propusk VALUES<NULL,'71','1','2013-03-04','10:46:43'>;
select * from users;
select * from propusk;
select users.*,propusk.data,vremya from firma.users left join firma.propusk on users.id_user=propusk.id_user where kpp=1;
SELECT COUNT<id> FROM propusk WHERE kpp=1 ;
SELECT COUNT<id> FROM propusk WHERE kpp=0 ;
SELECT SUM<KPP> FROM propusk WHERE id_user=1 ;
CREATE PROCEDURE ktotutest<k int<15>>
->begin
->select SUM<kpp> from propusk where id_user=k;
->end
->!
call ktotutest<2>!
create table kto< odin varchar<20>, dva varchar<20>>!
INSERT INTO kto VALUES <'ПРИШЕЛ' , ' УШЕЛ'>!
describe kto!
drop proceure kto!
CREATE PROCEDURE kto<k int<15>>
->begin
->SELECT SUM<kpp> from propusk where id_user=k;
->end
->!
call kto<17>!
drop table summ!
drop table kto!
CREATE PROCEDURE ktotyt<k int<15>>
-> begin
-> INSERT INTO summ<id,sum> select SUM<kpp),id_user from propusk WHERE id_user=k;
-> end
-> !
create table summ<id int<15>,sum int<15>>!
call ktotut<17>!
call ktotutC17>f
select * from summ!
drop procedure ktotam!
drop procedure ktotam<l int<15>>
->begin
->select SUM<kpp> from propusk WHERE id_user=l
->end
->!
call ktotam<23>!
|
вот что получилось ошибки доработки | |
| |
|
