SQL（CREATE、SELECT、DISTINCT、IN BETWEEN、NULL、ORDER BY DESC、模糊查询、几个函数max() count() lower() ..）

CREAT 

CREATE TABLE students(
	sid INT PRIMARY KEY,
	sname VARCHAR(20) NOT NULL,
	sex VARCHAR(5) DEFAULT	'男',
	age INT CHECK	(age >= 10 AND age <= 100),
	idcard VARCHAR(255) UNIQUE
);

INSERT INTO students(sid,sname) 
VALUES (20160101,'bbb');

INSERT INTO students 
VALUES (20160102,'YY','男',20,'370304****');

外键、多个列当主键

CREATE TABLE sailors(
	sid VARCHAR(20) PRIMARY KEY,
	sname VARCHAR(20),
	rating INT,
	age REAL
);


CREATE TABLE boats(
	bid VARCHAR(20) PRIMARY KEY,
	bname VARCHAR(20),
	color VARCHAR(20)
);

CREATE TABLE reverses(
	sid VARCHAR(20), 
	bid VARCHAR(20), 
	day TIMESTAMP,
	
	CONSTRAINT fk_sid FOREIGN KEY (sid) REFERENCES sailors(sid),
	CONSTRAINT fk_bid FOREIGN KEY (bid) REFERENCES boats(bid),
	CONSTRAINT pk PRIMARY KEY (sid,bid)
);

SELECT

AS

SELECT * 
FROM emp;

SELECT empno, sal AS '月薪', sal * 12 AS '年薪' 
FROM emp;

DISTINCT 

-- 10 10 20 20 30 
-- 只输出10 20 30，会过滤掉重复的，输出不同的
SELECT DISTINCT deptno 
FROM emp;


-- 第n行与第n+1行不同
-- A B
-- A C
-- A D
-- B D 
SELECT DISTINCT deptno, sal
FROM emp;

-- 错误写法！！！
-- 写错了N次！！！一定要注意！！！！！！
-- ！！！！
SELECT  deptno, DISTINCT sal
FROM emp;

BETWEEN 

SELECT sal
FROM emp
WHERE sal >= 10 AND sal <= 1200;
 
SELECT sal
FROM emp
WHERE sal BETWEEN 10 AND 1200;

 
SELECT *
FROM emp
WHERE sal NOT BETWEEN 10 AND 1200;

IN

SELECT empno,ename,sal
FROM emp
WHERE sal = 1600 OR sal = 1250 OR sal = 3000;

SELECT empno,ename,sal
FROM emp
WHERE sal IN (1600,1250,3000);

SELECT empno,ename,sal
FROM emp
WHERE sal != 1600 AND sal != 1250 AND sal != 3000;

SELECT empno,ename,sal
FROM emp
WHERE sal NOT IN (1600,1250,3000);

NULL

SELECT *
FROM emp
WHERE comm is NULL;

SELECT *
FROM emp
WHERE comm is NOT NULL;

ORDER BY

order by a, b           -- a和b都是升序
order by a, b desc      -- a升序b降序
order by a desc, b      -- a降序b升序
order by a desc, b desc -- a和b都是降序

 

-- 默认从低到高
SELECT *
FROM emp
ORDER BY sal;

SELECT *
FROM emp
ORDER BY deptno, sal;

-- 从高到低 descent
SELECT *
FROM emp
ORDER BY deptno, sal DESC;
-- desc之作用前面的一个，deptno是按照升序排列的，如果no 相同，sal按照降序排列

SELECT *
FROM emp
ORDER BY deptno DESC, sal;
--- deptno 降序排列， 如果no相通，sal升序

模糊查询

聚合函数

-- 变大小写， 最终返回 14行
SELECT LOWER(ename)
FROM emp;

SELECT UPPER(ename)
FROM emp;

-- 最终返回 1行 （其实就是一个数）
SELECT MAX(sal)
FROM emp;