标签：10 12 04 ename sal emp deptno WHERE SELECT
2023_10_12_MYSQL_DAY_04_笔记
14章课后作业
CREATE TABLE xi(
xid INT PRIMARY KEY AUTO_INCREMENT,
xname VARCHAR(10) UNIQUE,
xhead VARCHAR(10) NOT NULL,
xloc VARCHAR(30) DEFAULT '浑南区'
);
CREATE TABLE class02(
cno INT PRIMARY KEY AUTO_INCREMENT ,
cname VARCHAR(10) UNIQUE,
ctype VARCHAR(10) CHECK (ctype IN ('Java班' , 'UI班') ),
xid INT,
FOREIGN KEY(xid) REFERENCES xi(xid)
);
INSERT INTO class02 VALUES(1,'12','12',1);
#建表时 加入检查性约束--5
CREATE TABLE student8(
id INT PRIMARY KEY ,
sno INT ,
NAME VARCHAR(10),
gender CHAR(2) CHECK (gender IN ('男','女')),
age INT CHECK (age >= 15 AND age <=30)
);
INSERT INTO student8(id,sno,NAME,gender,age) VALUES(10,20220101,'张三','F',70);
#8.0版本以上才有
INSERT INTO student7(id,sno,sname,classid) VALUES(10,20220101,'张三',1);
#建表时 加入外键约束--4
CREATE TABLE student7(
id INT PRIMARY KEY ,
sno INT ,
sname VARCHAR(10),
classid INT ,
FOREIGN KEY(classid) REFERENCES class(id)
);
#建表时 加入非空约束--3
CREATE TABLE student5(
id INT PRIMARY KEY AUTO_INCREMENT,
sno INT ,
sname VARCHAR(10) NOT NULL
);
#验证非空约束作用
INSERT INTO student5(id,sno,sname) VALUES(1,20220101,'张三');
INSERT INTO student5(id,sno,sname) VALUES(2,20220102,NULL);
#删除非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型 NULL
#追加 唯一性元素
CREATE TABLE student5(
id INT PRIMARY KEY ,
sno INT ,
NAME VARCHAR(10),
idCard CHAR(18)
);
#追加 唯一性元素
ALTER TABLE student5 ADD UNIQUE(idCard);
#建表时 加入唯一性元素--2
CREATE TABLE student4(
id INT PRIMARY KEY AUTO_INCREMENT ,
sno INT ,
NAME VARCHAR(10),
idCard CHAR(18) UNIQUE
);
#唯一性约束可以为空
INSERT INTO student4(id,sno,NAME,idcard) VALUES(1,20220101,'张三','210102199901012345');
INSERT INTO student4(id,sno,NAME,idcard) VALUES(2,20220102,'李四','210102199901012345');
INSERT INTO student4(id,sno,NAME,idcard) VALUES(3,20220102,'王五',NULL);
INSERT INTO student4(id,sno,NAME,idcard) VALUES(4,20220102,'赵六',NULL);
#主键自增策略 默认从1开始， 每次+1
CREATE TABLE student3(
id INT PRIMARY KEY AUTO_INCREMENT ,
sno INT ,
NAME VARCHAR(10)
);
#语法:
列名 数据类型 PRIMARY KEY AUTO_INCREMENT
INSERT INTO student3(sno,NAME) VALUES(20220101,'张三');
INSERT INTO student3(sno,NAME) VALUES(20220102,'李四');
#删除主键约束
ALTER TABLE student2 DROP PRIMARY KEY ;
DESC student2;
INSERT INTO student1(id,sno,sname) VALUES(1,20220101,'张三');
INSERT INTO student1(id,sno,sname) VALUES(2,20220102,'李四');
INSERT INTO student1(id,sno,sname) VALUES(NULL,20220102,'李四');
#1.每个表都要有主键，且一个表最多只能有一个主键。
#2、主键所定义的列，不允许插入NULL值。
#追加主键
ALTER TABLE student2
ADD PRIMARY KEY (id) ;
CREATE TABLE student2 (id INT, sno INT, sname VARCHAR (10)) ;
#建表时加入 主键约束--1
CREATE TABLE student1 (
id INT PRIMARY KEY,
sno INT,
sname VARCHAR (10)
) ;
#截断表的作用是把原来的表摧毁，重新创建一个结构和原来一模一样的新表，语法如下：
TRUNCATE TABLE table;
#TRUNCATE和DELETE区别
#1、TRUNCATE是DDL命令，使用ROLLBACK不可以回滚。而DELETE是DML命令，使用ROLLBACK可以回滚。
#2、DELETE可以通过指定删除条件实现部分删除，TRUNCATE不能指定条件。
#修改字符集
ALTER TABLE 表名 character set 字符集;
#修改表名
RENAME TABLE 表名 TO 新表名;
RENAME TABLE dept_copy2 TO dept_copy1;
#删除列
ALTER TABLE 表名 DROP 列名;
ALTER TABLE dept_copy2 DROP interest;
#修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
ALTER TABLE dept_copy2 CHANGE hobby interest VARCHAR(100);
#改变表 修改一个字段
ALTER TABLE 表名 MODIFY 列名 新的类型;
ALTER TABLE dept_copy2 MODIFY hobby VARCHAR(200);
#改变表 加一个字段
ALTER TABLE 表名 ADD 列名 类型;
ALTER TABLE dept_copy2 ADD hobby VARCHAR(100);
#直接删除表，语法如下：
DROP TABLE 表名;
DROP TABLE dept_copy;
DROP TABLE IF EXISTS dept_copy;
#同时复制表结构和数据，语法如下：
CREATE TABLE 新表名 查询语句;
CREATE TABLE dept_copy2 SELECT * FROM dept ;
#仅复制表结构，语法如下：
CREATE TABLE 新表名 LIKE 旧表名;
CREATE TABLE dept_copy LIKE dept;
#查看创建表的SQL语句
SHOW CREATE TABLE 表名;
#查看表结构
DESC 表名;
#查看某个数据库中的所有表
SHOW TABLES;
#MySQL8.0版本开始，数据库默认的字符集是utf8mb4
#使用/切换数据库
USE db1;
#查看正在使用的数据库
SELECT DATABASE();
#删除数据库
DROP DATABASE db2;
#注意：数据库删除后，将无法恢复，因此要谨慎操作。
#修改数据库字符集
ALTER DATABASE DB1 DEFAULT CHARACTER SET utf8mb4;
#查看某个数据库的定义信息
SHOW CREATE DATABASE DB1;
#显示存在的数据库
SHOW DATABASES;
#创建数据库命令
CREATE DATABASE DB1;
CREATE DATABASE IF NOT EXISTS DB1 ;
11章 作业题01答案
CREATE TABLE copy_emp (
empno INT(4),
ename VARCHAR(20),
hiredate DATE,
deptno INT(2),
sal DOUBLE(8,2)
)
#依次写出下列SQL语句:
#1) 开启一个查询窗口1，设置事务手动提交
SET autocommit = 0 ;
SHOW VARIABLES LIKE 'autocommit';
#2) 在表copy_emp中插入数据，员工编号6789 ，员工姓名WANGWU，sal字段插入空值，部门号50，入职时间为2000年1月1日，并提交事务
INSERT INTO copy_emp VALUES(6789,'WANGWU','2000-1-1',50,NULL);
COMMIT;
#3) 把emp表中部门号为10号部门的员工信息插入到copy_emp表，不做结束事务的操作。
INSERT INTO copy_emp AS SELECT empno ,ename, hiredate ,deptno,sal FROM emp WHERE deptno = 10;
#4) 修改copy_emp表中数据，要求10号部门所有员工涨20%的工资，将题3和题4的SQL语句回滚并查看数据结果
UPDATE copy_emp SET sal=sal*1.2 WHERE deptno = 10 ;
ROLLBACK;
#5) 修改copy_emp表中6789员工的工资更新为3500，不做结束事务的操作。
UPDATE copy_emp SET sal = 3500 WHERE empno = 6789;
#6) 开启一个查询窗口2，设置事务手动提交。
SET autocommit = 0 ;
SHOW VARIABLES LIKE 'autocommit';
SELECT * FROM copy_emp ;
#7) 在查询窗口2中，查看copy_emp表中6789员工工资。
SELECT sal FROM copy_emp WHERE empno = 6789;
#8) 在查询窗口2中，修改copy_emp表中6789员工的工资更新为1500，不做结束事务的操作。
UPDATE copy_emp SET sal = 1500 WHERE empno = 6789;
#10) 在查询窗口1中，commit提交事务，回到查询窗口2中查看结果
COMMIT;
#课堂练习51
START TRANSACTION ;
UPDATE emp
SET sal = sal-500
WHERE ename="SCOTT";
UPDATE emp
SET sal = sal+200
WHERE ename="SMITH";
UPDATE emp
SET sal = sal+300a
WHERE ename="ALLEN";
ROLLBACK;
#COMMIT;
START TRANSACTION ;
INSERT INTO dept(deptno,dname,loc)
VALUES(18, 'dept1','loc1');
SELECT *
FROM dept;
ROLLBACK;
SELECT *
FROM dept;
ROLLBACK;
#查看事务的提交状态
SHOW VARIABLES LIKE 'autocommit';
#关闭事务的自动提交
SET autocommit = 0 ;
#打开事务的自动提交
SET autocommit = 1 ;
事务：英文单词Transaction，是由一个或多个SQL语句所组成的操作集合，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。
10章 作业题01答案
INSERT INTO class(classid,cname) VALUES(1,'Java1班');
INSERT INTO class(cname,classid) VALUES('Java2班',2);
INSERT INTO class VALUES(3,'Java3班',NULL);
10章 作业题02 03 04答案
INSERT INTO student VALUES('A001','张三','男','2005-5-1',100,1);
INSERT INTO student VALUES('A002','MIKE','男','1905-05-06',10,NULL);
INSERT INTO student(xh,xm,sex) VALUES('A003','JOHN','女' );
10章 作业题05答案
UPDATE student
SET sex='女'
WHERE xh='A002';
10章 作业题06答案
UPDATE student
SET sex='男',birthday='1980-04-01'
WHERE xh='A003';
10章 作业题07答案
做法1
UPDATE student
SET studentcid=3
WHERE studentcid IS NULL;
做法2
UPDATE student
SET studentcid=(SELECT classid FROM class WHERE cname='Java3班')
WHERE studentcid IS NULL;
#课堂练习50
#删除语句 多列子查询 ***
DELETE FROM emp_back
WHERE (deptno,sal) IN (SELECT deptno,AVG(sal) FROM emp GROUP BY deptno);
#课堂练习49
#删除语句 使用了子查询
DELETE FROM emp_back
WHERE deptno = (SELECT deptno FROM dept WHERE loc='NEW YORK');
#课堂练习48
DELETE FROM emp_back
WHERE mgr=7566;
#删除语句
DELETE FROM emp_back
WHERE job='CLERK';
#课堂练习48
#更新语句 使用了子查询
UPDATE manager
SET sal=sal+500
WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NEW YORK' OR loc='CHICAGO');
#课堂练习47
UPDATE manager
SET comm=0.0
WHERE comm IS NULL;
#课堂练习46
UPDATE manager
SET job='CLERK'
WHERE deptno=20;
#更新语句
UPDATE emp
SET deptno=20,comm=1200
WHERE empno=8888;
#课堂练习45
CREATE TABLE emp_back AS
SELECT * FROM emp WHERE 1=0;
INSERT INTO emp_back
SELECT * FROM emp WHERE hiredate > '1982-1-1'
#通过子查询插入多行数据
INSERT INTO manager
SELECT * FROM emp WHERE job='MANAGER';
#利用查询语句建立表
CREATE TABLE manager
SELECT * FROM emp WHERE 1=0;
#课堂练习44
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (8888,'BOB','CLEAK',7788,SYSDATE(),3000,NULL,NULL);
#使用日期函数插入数据
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (7196,'GREEN','SALESMAN',7782,SYSDATE(),2000,NULL,10);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (7197,'GREEN2','SALESMAN',7782,'2023-9-1',2000,NULL,10);
#获取当前时间的系统函数
SELECT SYSDATE();
#课堂练习43
INSERT INTO dept(deptno,dname) VALUES(90,'MARKET');
#课堂练习42
INSERT INTO dept(loc,deptno,dname) VALUES('SY',80,'HR');
#插入语句 方式2
INSERT INTO dept VALUES(201,'宣传部','上海');
#空值的插入
INSERT INTO dept VALUES(203,'后勤部',NULL);
#插入语句 方式1
INSERT INTO dept(loc,deptno,dname) VALUES('河南新乡',200,'研发部');
#空值的插入
INSERT INTO dept(loc,deptno,dname) VALUES(NULL,202,'服务部');
INSERT INTO dept(deptno,dname) VALUES(205,'小麦部');
06章04题作业答案
SELECT e.`ename` ,d.`dname`,e.`sal`,s.`grade`
FROM emp e LEFT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`), salgrade s
WHERE e.`sal` BETWEEN s.`losal` AND s.`hisal` AND s.`grade` > 4
#在 FROM 子句中使用子查询
SELECT a.ename, a.sal, a.deptno, b.salavg
FROM emp a, (SELECT deptno, AVG(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno AND a.sal > b.salavg;
#子查询里的空值处理
SELECT ename
FROM emp
WHERE empno NOT IN (SELECT IFNULL (mgr,0) FROM emp);
#多列子查询
SELECT deptno,ename,hiredate
FROM emp
WHERE (deptno,hiredate) IN (SELECT deptno ,MIN(hiredate)
FROM emp
GROUP BY deptno);
#课堂练习41
SELECT ename,job
FROM emp
WHERE job = ANY (SELECT job FROM emp WHERE deptno=10)
AND deptno <> 10;
#课堂练习40
SELECT ename,hiredate
FROM emp
WHERE hiredate > ALL (SELECT hiredate FROM emp WHERE deptno=10)
AND deptno <> 10;
#课堂练习39
SELECT ename,hiredate
FROM emp
WHERE hiredate > ANY (SELECT hiredate FROM emp WHERE deptno=10)
AND deptno <> 10;
#多行子查询 all操作符
SELECT empno, ename, job, sal
FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno= 20)
AND deptno <> 20;
SELECT empno, ename, job, sal
FROM emp
WHERE sal < ALL (SELECT sal FROM emp WHERE deptno= 20)
AND deptno <> 20;
#多行子查询 any操作符
SELECT ename,sal,empno
FROM emp
WHERE empno < ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
SELECT ename,sal,empno
FROM emp
WHERE empno > ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
SELECT ename,sal,empno
FROM emp
WHERE empno = ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
#多行子查询 in操作符
SELECT ename,sal
FROM emp
WHERE empno IN (SELECT mgr FROM emp);
#课堂练习38
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=10);
#课堂练习37
SELECT ename,sal,dname
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
AND sal > (SELECT sal FROM emp WHERE ename='SMITH')
AND d.`loc`='CHICAGO';
#课堂练习36
SELECT ename,hiredate
FROM emp
WHERE hiredate = (SELECT MIN(hiredate) FROM emp);
# HAVING子句中使用子查询
SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 20);
#子查询中使用组函数
SELECT ename,job,sal
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp);
#单行子查询语句
SELECT ename,job
FROM emp
WHERE job = (SELECT job FROM emp WHERE empno=7369)
AND sal > (SELECT sal FROM emp WHERE empno=7876);
#子查询 单行子查询
SELECT ename
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
#课堂练习35
SELECT ename,hiredate,deptno
FROM emp
LIMIT 0,5; #第1页
SELECT ename,hiredate,deptno
FROM emp
LIMIT 5,5; #第2页
SELECT ename,hiredate,deptno
FROM emp
LIMIT 10,5; #第3页
#课堂练习34
SELECT e.deptno,dname,COUNT(*),AVG(sal)
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY e.deptno,dname
HAVING COUNT(*) > 2 AND AVG(sal) > 2000
ORDER BY COUNT(*);
#七个子句的查询
SELECT job,SUM(sal)
FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal)
LIMIT 0,2;
#分页查询
SELECT empno,ename,deptno,sal
FROM emp
ORDER BY empno
LIMIT 5,5; #第2页
SELECT empno,ename,deptno,sal
FROM emp
ORDER BY empno
LIMIT 0,5; #第1页
#课堂练习33
SELECT e.deptno,dname,COUNT(*)
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY e.deptno,dname
HAVING COUNT(*) > 2;
总结:
1、WHERE子句用来过滤分组之前的记录，不能使用组函数
2、HAVING子句用来过滤分组之后的记录，可以使用组函数
#6个子句的查询
SELECT job,SUM(sal)
FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal);
查询语句执行过程：
1、通过FROM子句中找到需要查询的表；
2、通过WHERE子句进行非分组函数筛选判断；
3、通过GROUP BY子句完成分组操作；
4、通过HAVING子句完成组函数筛选判断；
5、通过SELECT子句选择显示的列或表达式及组函数；
6、通过ORDER BY子句进行排序操作。
#HAVING 子句
SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 3;
SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno
HAVING MAX(sal) > 2900;
#课堂练习32
SELECT COUNT(*),MAX(sal),MIN(sal),d.`loc`
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND d.`loc`='CHICAGO'
GROUP BY d.`loc`;
#课堂练习31 自连接
SELECT e2.`empno` 经理编号,e2.`ename` 经理姓名,COUNT(*)
FROM emp e,emp e2
WHERE e.`mgr`=e2.`empno`
GROUP BY e2.`empno`,e2.`ename`;
#课堂练习30
SELECT d.`deptno`,d.`dname`,e.`job`,COUNT(empno),MAX(sal),MIN(sal)
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY d.`deptno`,d.`dname`,e.`job`;
#多表查询分组查询
SELECT d.`deptno`,d.`dname`,COUNT(empno),MAX(sal)
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY d.`deptno`,d.`dname`;
#按多列分组查询
SELECT deptno,job,SUM(sal)
FROM emp
GROUP BY deptno,job;
#分组子句
SELECT job,COUNT(empno)
FROM emp
GROUP BY job;
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;
#课堂练习29
SELECT COUNT(*),MAX(sal),MIN(sal)
FROM emp
WHERE deptno=30;
#课堂练习28
SELECT (sal+IFNULL(comm,0))*12 年收入
FROM emp
#课堂练习27
SELECT COUNT(job), COUNT(DISTINCT job)
FROM emp
#课堂练习26
SELECT SUM(sal),AVG(sal)
FROM emp
WHERE deptno=20;
#除COUNT(*)之外，其它所有分组函数都会忽略列中的空值，然后再进行运算; IFNULL==空值处理函数
SELECT AVG(comm),AVG(IFNULL(comm,0))
FROM emp;
#空值处理函数 IFNULL(comm,0)
SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+IFNULL(comm,0) 总收入
FROM emp;
SELECT COUNT(deptno),COUNT(DISTINCT deptno)
FROM emp
#五个组函数--聚合函数
SELECT MIN(hiredate),MAX(hiredate),MIN(sal),MAX(sal),AVG(sal),SUM(sal),COUNT(*)
FROM emp;
#课堂练习25
SELECT e.empno,e.ename,e.`job`,d.deptno,d.`loc`
FROM emp e,dept d
WHERE e.deptno = d.deptno AND (d.`loc`='CHICAGO' OR job='MANAGER');
#联合查询 UNION 去除重复数据
SELECT e.empno,e.ename,d.deptno,d.dname
FROM emp e LEFT OUTER JOIN dept d ON(e.deptno = d.deptno)
UNION
SELECT e.empno,e.ename,d.deptno,d.dname
FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno);
#联合查询 UNION ALL 保留重复数据
SELECT e.empno,e.ename,d.deptno,d.dname
FROM emp e LEFT OUTER JOIN dept d ON(e.deptno = d.deptno)
UNION ALL
SELECT e.empno,e.ename,d.deptno,d.dname
FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno);
#课堂练习24 右外连接 题目? 没有下属的也要查出来
SELECT e.`ename` ,e2.`ename`
FROM emp e RIGHT OUTER JOIN emp e2 ON(e.`mgr`=e2.`empno`);
#课堂练习23 左外连接 没有领导的也要查出来
SELECT e.`ename` ,e2.`ename`
FROM emp e LEFT OUTER JOIN emp e2 ON(e.`mgr`=e2.`empno`);
#课堂练习22
SELECT ename,dname,hiredate
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND e.`hiredate` > '1980-5-1'
#课堂练习21 笛卡尔积数据
SELECT ename,dname
FROM emp,dept
#右外连接
SELECT e.`ename` ,e.`deptno`,d.`loc`
FROM emp e RIGHT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`);
#左外连接
SELECT e.`ename` ,e.`deptno`,d.`loc`
FROM emp e LEFT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`);
#等值连接 自然连接
SELECT e.`ename` ,e.`deptno`,d.`loc`
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
#课堂练习20 自连接
SELECT e.`ename` 员工姓名,e.`empno` 员工编号,e2.`ename` 经理姓名,e2.`empno` 经理编号
FROM emp e,emp e2,dept d
WHERE e.`mgr`=e2.`empno` AND e.`deptno`=d.`deptno` AND (d.`loc`='NEW YORK' OR d.`loc`='CHICAGO');
#自连接
SELECT e.`ename` 员工姓名,e2.`ename` 上级姓名
FROM emp e,emp e2
WHERE e.`mgr`=e2.`empno`
#课堂练习19
SELECT e.`empno`,e.`ename`,e.`sal`,s.`grade`,d.`loc`
FROM emp e,dept d,salgrade s
WHERE e.`deptno`=d.`deptno` AND e.`sal` BETWEEN s.`losal` AND s.`hisal`
ORDER BY s.`grade` DESC;
#多于两个表的连接
SELECT e.`ename`,e.`sal`,d.`dname`,s.`grade`
FROM emp e,dept d,salgrade s
WHERE e.`deptno`=d.`deptno` AND e.`sal` BETWEEN s.`losal` AND s.`hisal`;
#非等值连接
SELECT ename,sal,grade
FROM emp e,salgrade s
WHERE e.`sal` BETWEEN s.`losal` AND s.`hisal`;
#综合练习4
SELECT ename,e.deptno,dname,sal
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND dname = 'RESEARCH' AND sal < 1500;
#综合练习3
SELECT ename,loc
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND ename LIKE '%A%';
#综合练习2
SELECT ename,comm,loc
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND loc='CHICAGO' AND comm IS NOT NULL;
#综合练习1
SELECT ename,e.deptno,dname
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
#多表等值连接查询
SELECT empno,ename,dept.deptno,dname
FROM emp,dept
WHERE emp.`deptno`=dept.`deptno`
#使用表的别名
SELECT empno,ename,d.deptno,dname
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
#课堂练习18
SELECT ename,hiredate,job
FROM emp
WHERE hiredate BETWEEN '1982-1-1' AND '1983-12-31' AND (job LIKE 'SALES%' OR job LIKE 'MAN%')
ORDER BY hiredate DESC
#课堂练习17
SELECT ename,deptno,sal
FROM emp
WHERE deptno NOT IN (10) AND sal BETWEEN 2000 AND 3000
ORDER BY deptno ASC,sal DESC
#课堂练习16
SELECT ename,deptno,sal
FROM emp
WHERE deptno IN (20,30)
ORDER BY sal
SELECT ename,deptno
FROM emp
WHERE deptno IN (20,30)
ORDER BY sal
#同时按多列排序
SELECT ename,deptno,sal
FROM emp
ORDER BY deptno ASC,sal DESC;
#DESC 降序 从大到小 == 空值小
SELECT ename,job,deptno,hiredate
FROM emp
ORDER BY hiredate DESC
#按三种方式排序
SELECT ename,job,deptno,hiredate
FROM emp
ORDER BY hiredate
SELECT ename,job,deptno,hiredate 入职日期
FROM emp
ORDER BY 入职日期
SELECT ename,job,deptno,hiredate 入职日期
FROM emp
ORDER BY 4
可以按照3种方式进行排序：分别是按列名排序、按列别名排序、按列序号排序。
ASC表示按升序排序(默认值), DESC表示按降序排序。
可以同时按照多个列名进行排序
空值在升序排列中排在最前面，在降序排列中排在最后 == 空值小
4种特殊比较运算符 BETWEEN..AND.. , IN, LIKE, IS NULL
#课堂练习15
SELECT ename,job,deptno
FROM emp
WHERE job IN ('SALESMAN','MANAGER') AND deptno IN (10,20) AND ename LIKE '%A%';
SELECT ename,job,deptno
FROM emp
WHERE (job ='SALESMAN' OR job='MANAGER') AND (deptno=10 OR deptno=20) AND ename LIKE '%A%';
#课堂练习14
SELECT ename,hiredate,job
FROM emp
WHERE hiredate BETWEEN '1981-1-1' AND '1981-12-31' AND job NOT LIKE 'SALES%';
SELECT ename,hiredate,job
FROM emp
WHERE hiredate >= '1981-1-1' AND hiredate <='1981-12-31' AND job NOT LIKE 'SALES%';
#课堂练习13 写法1 使用特殊比较运算符
SELECT ename,deptno,sal
FROM emp
WHERE deptno IN (10,20) AND sal BETWEEN 3000 AND 5000;
#写法2 使用逻辑运算符
SELECT ename,deptno,sal
FROM emp
WHERE (deptno = 10 OR deptno=20) AND (sal >= 3000 AND sal <= 5000);
#课堂练习12
SELECT ename,job,sal
FROM emp
WHERE sal > 2000 AND (job='MANAGER' OR job='SALESMAN');
#运算符的优先级
SELECT ename, job, sal
FROM emp
WHERE ( job='SALESMAN'
OR job='PRESIDENT')
AND sal>1500;
SELECT ename, job, sal
FROM emp
WHERE job='SALESMAN'
OR job='PRESIDENT'
AND sal>1500;
SELECT ename,comm
FROM emp
WHERE comm IS NOT NULL;
SELECT ename,sal
FROM emp
WHERE sal NOT BETWEEN 3000 AND 5000;
NOT BETWEEN .. AND .. :不在某个区间
NOT IN (集合）：不在某个集合内
NOT LIKE ：不像.....
IS NOT NULL: 不是空
#课堂练习11
SELECT ename,comm
FROM emp
WHERE comm IS NULL;
#课堂练习10
SELECT *
FROM emp
WHERE ename LIKE '%T_';
#课堂练习9
SELECT *
FROM emp
WHERE ename LIKE 'W%';
# IS NULL
SELECT ename,mgr
FROM emp
WHERE mgr IS NULL;
SELECT ename,comm
FROM emp
WHERE comm IS NULL;
#Like运算符
SELECT ename
FROM emp
WHERE ename LIKE 'S%';
SELECT ename
FROM emp
WHERE ename LIKE 'S_';
SELECT ename
FROM emp
WHERE ename LIKE '%A%';
SELECT ename
FROM emp
WHERE ename LIKE '__A%';
#课堂练习8
SELECT ename,sal
FROM emp
WHERE sal BETWEEN 3000 AND 5000;
#课堂练习7
SELECT ename,hiredate
FROM emp
WHERE hiredate BETWEEN '1982-1-1' AND '1985-12-31';
#IN运算符
select empno,ename,deptno
from emp
where deptno in (10,20);
#使用BETWEEN .. AND.. 可以查询出某列的值在某个范围内(包括边界值)的数据行
SELECT empno,ename,sal
FROM emp
WHERE sal BETWEEN 1250 AND 1600;
#课堂练习6
SELECT empno,ename,deptno
FROM emp
WHERE deptno <> 10;
SELECT empno,ename,deptno
FROM emp
WHERE deptno != 10;
#课堂练习5
SELECT empno,ename,hiredate
FROM emp
WHERE hiredate < '1985-12-31';
#课堂练习4
SELECT empno,ename,job
FROM emp
WHERE job='SALESMAN';
#带条件查询2 非等值情况
SELECT empno,ename,hiredate
FROM emp
WHERE hiredate > '1985-01-01';
#带条件查询2
SELECT empno,ename,job
FROM emp
WHERE job='CLERK';
SELECT empno,ename,job
FROM emp
WHERE job="CLERK";
#带条件查询1
SELECT empno,ename,deptno
FROM emp
WHERE deptno=20;
#课堂练习3
SELECT DISTINCT job FROM emp
#排除重复数据的查询
SELECT DISTINCT deptno FROM emp
#课堂练习2 ==?
SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+comm 总收入 FROM emp;
#课堂练习1
SELECT ename,sal 涨薪前,sal*1.2 涨薪后 FROM emp;
#空值参与算术运算，运算后的结果仍为NULL ==待解决?
SELECT ename,sal,comm,sal+comm 月总收入 FROM emp
#数值类型的字段可以做数学运算
SELECT empno,ename,sal,sal*12 年薪 FROM emp
#按字段别名查询 单双引号的使用
SELECT empno 编号,ename "姓 名",sal '工 资',comm "奖,金" FROM emp
#按字段别名查询
SELECT empno 编号,ename 姓名,sal 工资,comm 奖金 FROM emp
#查询指定字段
SELECT empno,ename,sal,comm FROM emp
#查询所有字段
SELECT * FROM emp
标签：10,
12,
04,
ename,
sal,
emp,
deptno,
WHERE,
SELECT
From： https://www.cnblogs.com/Qinyyds/p/17758996.html