sql语句的练习

一、sql表格的创建

例：

CREATE TABLE department(
id INT,
NAME VARCHAR(50)
);

图片：

二、UNIAN和UNIAN ALL的用法

UNIAN是将两个查询的结果合在一起显示
UNIAN ALL是在显示的时候将重复的数据剔除然后再显示结果。
例：

SELECT * FROM employee_chain UNION SELECT * FROM employee_usa;

SELECT * FROM employee_chain UNION ALL SELECT * FROM employee_usa;

三、内部链接，左右链接

内部链接的普通代码：

SELECT e.id,e.name AS '员工名',d.name AS '部门' FROM employee e,department d WHERE  e.depno = d.id  ;

内部链接的正确代码：

SELECT e.id,e.name AS '员工名',d.name AS '部门' FROM employee e INNER JOIN department d ON e.depno = d.id  ;

图片：

左连接代码：

SELECT e.id,e.name AS '员工名',d.name AS '部门'  FROM employee e  LEFT OUTER JOIN department d ON e.depno = d.id  ;

右链接的代码：

SELECT e.id,e.name AS '员工名',d.name AS '部门'  FROM employee e  RIGHT OUTER JOIN department d ON e.depno = d.id 

WHERE e.id=1 ;

四、多表之间的联系

新建两个表

CREATE  TABLE depart(
depno INT PRIMARY KEY,
NAME VARCHAR(50),
location  VARCHAR(50),
setuptime TIMESTAMP
);

CREATE TABLE emp(
	empno INT PRIMARY KEY,
	ename VARCHAR(50),
	job VARCHAR(50),
	mgr INT,
	hiredate DATE,
	sal DECIMAL(7,2),
	comm DECIMAL(7,2),
	depno INT,
	CONSTRAINT fk_emp_depart FOREIGN KEY(depno) REFERENCES depart(depno)
);

CONSTRAINT fk_emp_depart FOREIGN KEY(depno) REFERENCES depart(depno)
这句语句是指只能选择选择表depart里的depno有的值。

子查询代码：

SELECT * FROM emp WHERE sal> (SELECT sal FROM emp WHERE ename='林同学' )

结果：

两个表之中的联系：

SELECT empno,sal,NAME,location FROM emp ,depart WHERE emp.depno=depart.depno AND emp.ename='刘同学';

图片：