MySQLデータベース操作『下』
8216 ワード
一、
二、
三、
四、
2、
3、
五、
( )
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) # , ( )
);