1. , . 3. 10 (, , .).
2. , , , , . :
| / | |||||
| 1. | ID | INT | |||
| 2. | POST | CHAR | |||
| 3. | RANK | CHAR | |||
| 4. | FAM | CHAR | |||
| 5. | NAME | CHAR | |||
| 6. | OTCH | CHAR | |||
| 7. | DATAR | DATE | |||
| 8. | GODPR | INT | |||
| 9. | LN | CHAR | |||
| 10. | OKLAD | DECIMAL(9,2) |
-- mysql
mysql -uroot -ppassword
-- ,
DROP DATABASE voenchast;
-- voenchast utf8
CREATE DATABASE voenchast COLLATE utf8_general_ci;
-- , voenchast
USE voenchast;
--
CREATE TABLE upravlenie
(
id INT(3) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
post VARCHAR(31) NOT NULL DEFAULT '' COMMENT ' ',
zvan VARCHAR(17) NOT NULL DEFAULT '' COMMENT ' ',
fam VARCHAR(15) NOT NULL DEFAULT '' COMMENT '',
name VARCHAR(10) NOT NULL DEFAULT '' COMMENT '',
otch VARCHAR(15) NOT NULL DEFAULT '' COMMENT '',
datar DATE NOT NULL DEFAULT '1000-01-01' COMMENT ' ',
godpr INT(4) NOT NULL DEFAULT 1000 COMMENT ' ',
nomer VARCHAR(8) NOT NULL DEFAULT '' COMMENT ' ',
oklad DECIMAL(9,2) NOT NULL DEFAULT 0 COMMENT '',
PRIMARY KEY (id)
) COMMENT ' ';
--
DESCRIBE upravlenie;
3. , . . . .
-- upravlenie
INSERT INTO upravlenie (post, zvan, fam, name, otch, datar, godpr, nomer, oklad)
VALUES (' ','',,,,1972-05-12,1990,-875432,76120.54),
(' ','',,,,1975-09-23,1996,-574962,68500.10),
(' ','',,,,1976-12-10,1996,-798778,68500.10),
(' ','',,,,1980-02-15,2001,-848787,65050.25),
|
|
|
(' ','',,,,1985-07-27,2006,-256954,52000.80);
-- upravlenie
SELECT * FROM upravlenie;
4. ( ), :
) ,
)
) .
, ( ) (). , , .
-- upravlenie
SELECT * FROM upravlenie ORDER BY fam;
-- upravlenie
SELECT * FROM upravlenie ORDER BY oklad;
-- upravlenie
SELECT * FROM upravlenie ORDER BY datar DESC;
5. . .
-- upravlenie
INSERT INTO upravlenie (post, zvan, fam, name, otch, datar, godpr, nomer, oklad)
VALUES (' ',' ',,,,1984-02-21,2004,-586545,52000.80),
(' ',' ',,,,1981-09-14,2001,-889874,63800.20),
(' ','',,,,1977-04-18,1997,-873547,66050.10),
(' ','',,,,1986-08-08,2007,-654591,53000.80),
(' ','',,,,1984-08-14,2005,-213456,48056.75);
-- upravlenie
SELECT * FROM upravlenie;
6. SQL , ( ). .
-- upravlenie
SELECT * FROM upravlenie WHERE zvan=;
7. 6 , . () SQL-.
-- upravlenie
SELECT * FROM upravlenie WHERE post= ;
-- upravlenie 1996
SELECT * FROM upravlenie WHERE godpr=1996;
-- upravlenie
SELECT * FROM upravlenie WHERE zvan= OR zvan=;
8. ( SQL) :
- ;
- (.. );
|
|
|
- .
, , .
.
-- upravlenie 1996
SELECT * FROM upravlenie WHERE zvan= AND godpr=1996;
-- upravlenie , , 2006 50000 70000
SELECT * FROM upravlenie WHERE zvan IN (, ,) AND godpr=2006 AND (oklad>50000 AND oklad<70000);
9. (), .
id.
-- upravlenie,
CREATE TABLE spvoen SELECT id, fam, zvan FROM upravlenie;
--
SELECT * FROM spvoen;
-- upravlenie,
CREATE TABLE sppost SELECT id, post FROM upravlenie;
--
SELECT * FROM sppost;
-- upravlenie,
CREATE TABLE spoklad SELECT id, oklad FROM upravlenie;
--
SELECT * FROM spoklad;
10.
SHOW TABLES
:
SHOW TABLES [{FROM | IN} db_name] [LIKE 'pattern']
SHOW TABLES . mysqlshow db_name.
.
:
SHOW TABLES;
, .
SHOW TABLES .
FROM IN , MySQL .
, mysql:
SHOW TABLES FROM mysql;
, , , .
LIKE, .
, mysql help:
SHOW TABLES FROM mysql LIKE 'help%';
% .
11. SQL :
‑ ( , , , );
‑ , 4-5 , , . , . , SQL, .
-- mysql
mysql -uroot -ppassword
-- ,
DROP DATABASE hospital;
-- hospital
CREATE DATABASE IF NOT EXISTS hospital;
-- hospital
USE hospital;
-- patient()
CREATE TABLE IF NOT EXISTS patient
(
tab_n INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
fam CHAR(20) NOT NULL,
name CHAR(10) NOT NULL,
otch CHAR(20) NOT NULL,
PRIMARY KEY (tab_n)
);
-- patient
INSERT INTO patient (fam, name, otch) VALUES
(,,),
(,,),
(,,),
(,,),
(,,),
(,,);
-- state ()
CREATE TABLE IF NOT EXISTS state
(
tab_n INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,
INDEX par_ind (tab_n),
FOREIGN KEY (tab_n) REFERENCES patient(tab_n) ON DELETE CASCADE,
tm DECIMAL(3,1) NOT NULL DEFAULT 0,
dg CHAR(15) NOT NULL,
dat DATE NOT NULL DEFAULT '1000-01-01',
PRIMARY KEY (tab_n)
);
-- patient
INSERT INTO state (tm, dg, dat) VALUES
(36.8,,1999-12-12),
(38.5,,2000-01-03),
(36.6,,2000-01-04),
(37.8,,2000-02-18),
(36.9,,2000-06-06),
(36.5,,2000-06-06);
-- doctor ( )
CREATE TABLE IF NOT EXISTS doctor
(
tab_n INTEGER(3) UNSIGNED NOT NULL AUTO_INCREMENT,
INDEX par_ind (tab_n),
FOREIGN KEY (tab_n) REFERENCES patient(tab_n) ON DELETE CASCADE,
vrach CHAR(20) NOT NULL
);
-- doctor
INSERT INTO doctor (vrach) VALUES
( ..),
( ..),
( ..),
( ..),
( ..),
( ..);
:
SELECT
patient.tab_n AS ,
fam AS ߒ,
dat AS ߒ,
tm AS ,
dg as ǒ,
doctor.vrach AS ג
FROM patient, state, doctor
WHERE patient.tab_n=state.tab_n and patient.tab_n=doctor.tab_n
ORDER BY patient.tab_n ASC;
:
FROM patient, state, doctor
WHERE patient.tab_n=state.tab_n and patient.tab_n=doctor.tab_n
ORDER BY patient.tab_n ASC patient.tab_n
