1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
create table `test` (
`Pno` char(03) NOT NULL PRIMARY KEY,
`Pname` varchar(16) NOT NULL,
`Color` varchar(16) NOT NULL,
`Weight` decimal(5,1) NULL,
`City` varchar(16) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `test` (`Pno`,`Pname`,`Color`,`Weight`,`City`) values
('P1', 'Nut', 'Red', 12.0, 'London'),
('P2', 'Bolt', 'Green', 17.0, 'Paris'),
('P3', 'Screw', 'Blue', null, 'Oslo'),
('P4', 'Screw', 'Red', 14.0, 'London'),
('P5', 'Cam', 'Blue', 12.0, 'Paris'),
('P6', 'Cog', 'Red', 19.0, 'London')
--------------
--------------
select * from test
--------------
+-----+-------+-------+--------+--------+
| Pno | Pname | Color | Weight | City |
+-----+-------+-------+--------+--------+
| P1 | Nut | Red | 12.0 | London |
| P2 | Bolt | Green | 17.0 | Paris |
| P3 | Screw | Blue | NULL | Oslo |
| P4 | Screw | Red | 14.0 | London |
| P5 | Cam | Blue | 12.0 | Paris |
| P6 | Cog | Red | 19.0 | London |
+-----+-------+-------+--------+--------+
--------------
SELECT *
FROM test as t
WHERE t.Weight NOT IN (SELECT x.Weight
FROM test as x
WHERE x.City = 'Oslo')
--------------
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager