MySQLデータベース操作『下』

8216 ワード

一、 ( )

SELECT * FROM emp,dept;	
	

二、
SELECT * FROM emp t1,dept t2 WHERE  t1.dept_id = t2.id;
SELECT * FROM emp t1 (INNER) JOIN  dept t2 ON  t1.dept_id = t2.id;


三、
--    
SELECT * FROM  emp t1 LEFT  (OUTER) JOIN dept t2 ON t1.dept_id = t2.id;

--    
SELECT * FROM  emp t1 RIGHT (OUTER) JOIN dept t2 ON t1.dept_id = t2.id;


四、 1、 :A B
--               
SELECT
	 * 
FROM 
	emp 
WHERE 
	emp.`salary` = (SELECT MAX(salary) FROM emp);


2、 : IN
--   '   ' '   '       
SELECT
	 * 
FROM 
	emp 
WHERE 
	dept_id 
IN 
	(SELECT id FROM dept WHERE NAME = '   ' OR NAME = '   ');
	

3、
--          2011-11-11             
SELECT
	* 
FROM
	dept t1 ,
	(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2  
WHERE
	t1.id = t2.dept_id;
	

五、
--    
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('   '),('   '),('   ');

--     
CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1),      #   
	salary DOUBLE,       #   
	join_date DATE,      #     
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) #   ,     (      )
);