首页 > 数据库 >2023_10_14_MYSQL_DAY_05_笔记

2023_10_14_MYSQL_DAY_05_笔记

时间：2023-10-14 21:57:26浏览次数：52  
标签：ename 10 14 05 sal emp deptno WHERE SELECT

2023_10_14_MYSQL_DAY_05_笔记

https://www.cnblogs.com/tdskee/p/16536166.html
{
   MySQL的优化多种方法（至少15条）
}

#查看触发器
show triggers;
#删除触发器
drop trigger 触发器名;
#建立触发器
drop trigger if exists dept_del;
create trigger dept_del after delete on dept for each row
begin
    delete from emp where deptno=old.deptno;
end;

#删除外键约束
ALTER TABLE emp DROP FOREIGN KEY  fk_deptno ; 

#存储过程调用
CALL `getsalbydeptno`(20,@aaa);
SELECT @aaa;

#存储过程创建
DELIMITER $$
USE `sjkxtgl3`$$
DROP PROCEDURE IF EXISTS `getsalbydeptno`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getsalbydeptno`(IN dno INT,OUT salsum DECIMAL(7,2))
    BEGIN
       SELECT SUM(sal) INTO salsum FROM emp WHERE deptno=dno;
    END$$
DELIMITER ;


#建立索引
CREATE INDEX diseasename_index2 ON disease_back2(diseasename);

# 88185条疾病名称数据 索引前用时:0.080sec  索引后用时:0.001sec 
SELECT * FROM disease_back2 WHERE diseaseName='轻度酒精中毒' 

测试索引

CREATE TABLE disease_back AS 
SELECT * FROM disease;

CREATE TABLE disease_back2 AS 
SELECT * FROM disease_back;

INSERT INTO disease_back 
SELECT * FROM disease_back2

INSERT INTO disease_back2 
SELECT * FROM disease_back

SELECT * FROM disease WHERE diseaseName='轻度酒精中毒'

SELECT * FROM disease_back WHERE diseaseName='轻度酒精中毒'

SELECT * FROM disease_back2 WHERE diseaseName='轻度酒精中毒'

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) 
);

# DEFAULT 使用
CREATE TABLE student9(
    id  int  PRIMARY KEY,
    sno  int ,
    name varchar(10),
    city varchar(20) DEFAULT '沈阳'
);
INSERT INTO student9(id,sno,name) VALUES(10,20220101,'张三');

#检查性约束
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);

#建立表时加入 外键约束
CREATE TABLE student7 (
  id INT PRIMARY KEY,
  sno INT,
  sname VARCHAR(10),
  classid INT,
  FOREIGN KEY(classid) REFERENCES class(id)
);

#先建立父表
CREATE TABLE class (
   id INT PRIMARY KEY,
   cname VARCHAR(20)
);


#建立表时加入 非空约束
CREATE TABLE student6(
    id  int PRIMARY KEY ,
    sno  int ,
    sname varchar(10) NOT NULL
);

ALTER TABLE student6 MODIFY sno int NOT NULL;

#建立表时加入 唯一性约束
CREATE TABLE student4(
    id  INT PRIMARY KEY AUTO_INCREMENT  ,
    sno  INT ,
    sname VARCHAR(10),
    idCard CHAR(18) UNIQUE
);
# 唯一性约束 可以为空
INSERT INTO student4(id,sno,sname,idcard) VALUES(3,20220103,'王五',NULL);
INSERT INTO student4(id,sno,sname,idcard) VALUES(4,20220104,'赵六',NULL);

# 自增长类型 默认从1开始 每次+1
AUTO_INCREMENT

#删除主键约束
ALTER TABLE student2 DROP PRIMARY KEY;

#建立表时无主键约束 追加主键约束
CREATE TABLE student2 (
   id INT,
   sno INT,
   sname VARCHAR(10)
);
#追加主键
ALTER TABLE student2 ADD PRIMARY KEY(id);

#建立表时加入 主键约束
CREATE TABLE student1 (
   id INT PRIMARY KEY,
   sno INT,
   sname VARCHAR(10)
);


#截断表
TRUNCATE TABLE dept_copy202;

#修改表名
RENAME TABLE dept_copy2 TO dept_copy202;

#删除列
ALTER TABLE dept_copy2 DROP interest;

#修改列名
ALTER TABLE dept_copy2 CHANGE hobby interest VARCHAR(100);

#改变表 修改一个字段
ALTER TABLE dept_copy2 MODIFY hobby VARCHAR(200);

#改变表 加一个字段
ALTER TABLE dept_copy2 ADD hobby VARCHAR(100);

#删除表
DROP TABLE dept_copy;
DROP TABLE IF EXISTS dept_copy;

#复制表结构和数据
CREATE TABLE dept_copy2 SELECT * FROM dept;

#复制表结构
CREATE TABLE dept_copy LIKE dept;

#查看表结构
DESC emp;

#建表语句
CREATE TABLE student(
  sno int,
  sname varchar(10),
  gender char(1),
  birthday date
)

#查看当前数据库中的表
SHOW TABLES;

#切换数据库
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答案
SET autocommit=0;
SHOW VARIABLES LIKE 'autocommit';

INSERT INTO copy_emp VALUES(6789,'WANGWU','2000-1-1',50,NULL);
COMMIT;

INSERT INTO copy_emp 
SELECT empno,ename,hiredate,deptno,sal FROM emp WHERE deptno=10;

UPDATE copy_emp SET sal=sal*1.2 WHERE deptno=10;
ROLLBACK;

UPDATE copy_emp SET sal=sal*1.2 WHERE deptno=10;


#课堂练习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;

#查看事务提交状态
SHOW VARIABLES LIKE 'autocommit';
#关闭事务的自动提交
SET autocommit=0;

事务：是由一个或多个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
#删除语句 多列子查询  ***????  mysql不支持 需要建立临时表

DELETE FROM emp_back
WHERE empno IN (SELECT empno 
FROM emp a ,(SELECT deptno,AVG(sal) Sa FROM emp GROUP BY deptno) b
WHERE a.`deptno`= b.deptno AND a.`sal` > b.Sa);

DELETE FROM emp z WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=z.deptno);

SELECT empno FROM emp z WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=z.deptno);

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

标签：ename,10,14,05,sal,emp,deptno,WHERE,SELECT
From： https://www.cnblogs.com/Qinyyds/p/17764794.html

相关文章

  • 14.最长公共前缀
    1.题目描述编写一个函数来查找字符串数组中的最长公共前缀。如果不存在公共前缀，返回空字符串""。示例1：输入：strs=["flower","flow","flight"]输出："fl"示例2：输入：strs=["dog","racecar","car"]输出：""解释：输入不存在公共前缀。......
  • 2023_10_14_MYSQL_DAY_06_MYSQL优化的种类
    MYSQL优化的种类MYSQL的优化，是每一个程序员在做数据查询处理的时候，经常有的步骤那么SQL的优化有很多种，它可以是在硬件方面的，可以是在代码层面的，可以是在数据库方面的优化。下面就详细整理一下30种优化MYSQL的方案：1.在读表的时候，尽可能的避免全表扫描，合理的根据业务需求，在wher......
  • css 10-13
    1.背影样式 backgroud-color          背景颜色backgroud-color:red backgroud-image         背景图片backgroud-image backgroud-position        背景图片位置backgroud-positiontop  left ......
  • 2023-2024-1 20231424 《计算机基础与程序设计》第3周学习总结
    作业信息作业课程2022-2023-1-计算机基础与程序设计作业要求2022-2023-1计算机基础与程序设计第一周作业这个作业的目标自学《计算机科学概论》第2章，第3章和《C语言程序设计》第2章作业正文链接https://www.cnblogs.com/2004lby/p/17764649.html教材学习内......
  • 10.14模拟赛
    我觉得这个不要叫作赛后总结了，改成挂分日报吧。（T1虽然很离谱11:40才修改题面，然后11:55结束考试，但是虽然一眼出了正解（就是很简单的一个二分），但是没有开double（（（（直接挂了，然后读入还写错了。（（（尬（（T2一个二阶前缀和和二阶差分，酸菜鱼还不会这个怎么用，一会儿大概会更新一个学习笔记......
  • 10.14 模拟赛小记
    传送门感觉我已经是半个废人了。A.P1118[USACO06FEB]BackwardDigitSumsG想到的是预处理杨辉三角，然后dfs找。我的预处理写的三维。原因是听大家打键盘的声音太吵了（指机械键盘），然后就不会写二维的了。然后只会写三维的。然后就被同学嘲讽为什么不写二维的。据说next_pe......
  • 2023-2024-1 20231416 《计算机基础与程序设计》第三周学习总结
    计算机科学概论第二章学习了二进制、八进制、十进制、十六进制的计算和转化，二进制与八进制采用“三合一”转化，即三位二进制数按权展开为一位八进制数，二进制与十六进制数采用“四合一”转化法，即四位二进制数按权展开得到一位十六进制数。例如：二进制→八进制010=0+12^1+0=2二进制......
  • 学期2023-2024-1 20231401 《计算机基础与程序设计》第三周学习总结
    学期2023-2024-120231401《计算机基础与程序设计》第三周学习总结作业信息这个作业属于哪个课程2023-2024-1-计算机基础与程序设计这个作业要求在哪里2023-2024-1计算机基础与程序设计第三周作业这个作业的目标自学教材：计算机科学概论第2章，第3章，C语言程序设计第......
  • 面试必刷TOP101：3、链表中的节点每k个一组翻转
    一、题目将给出的链表中的节点每k 个一组翻转，返回翻转后的链表如果链表中的节点数不是k的倍数，将最后剩下的节点保持原样你不能更改节点中的值，只能更改节点本身。二、题解publicclassSolution{/****@paramheadListNode类*@paramkint整型......
  • 2023-2024-1 20231416 《计算机基础与程序设计》第三周学习总结
    计算机概论第二章中书里主要讲述了二进制八进制以及十六进制的运算以及十进制如何转化为不同的进制学习二进制计算是学习计算机程序的重中之重在经过不懈学习后掌握了二进制的我对于学习计算机更有了一份自信第三章中学习了补码反码等概念以及关键字编码行程长度编码......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99