Day25、数据库

#创建数据库

CREATE DATABASE IF NOT EXISTS `Java2217Study`;

#使用数据库

USE Java2217Study;

#创建表

DROP TABLE IF EXISTS stu;
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
 
CREATE TABLE `stu`(
`sid` CHAR(6),
`sname` VARCHAR(50),
`age` INT,
`gender` VARCHAR(50)
);

#向stu表中插入数据

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

#创建表emp

CREATE TABLE emp(
`empno` INT,
`ename` VARCHAR(50),
`job` VARCHAR(50),
`mgr` INT,
`hiredate` DATE,
`sal` DECIMAL(7,2),
`comm` DECIMAL(7,2),
`deptno` INT 
);

#向表emp中插入数据

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

#创建表dept

CREATE TABLE dept(
`deptno` INT,
`dname` VARCHAR(14),
`loc` VARCHAR(13)
);

#向dept表中插入数据

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

#查询语句

#基础查询

#查询表中某个指定列

#select 字段名 from 表名;

#查询学生表stu中的学生姓名 字段sname

SELECT sname FROM `stu`;

#查询表中的多个字段

#select 字段名1,字段名2,...,字段名n from 表名;

#查询学生表stu中学生姓名sname、学生年龄age、学生性别gender三个字段

SELECT sname,age,gender FROM stu;

#查询表中的所有字段：第一种方式将所有的字段一一写出来，第二种，使用*表示所有的字段

#select * from 表名

#查询学生表中的所有字段信息

SELECT * FROM stu;

#条件查询：对查询出来的结果进行筛选，使用WHERE进行过滤
#查询性别为女，并且年龄小于50的记录（记录表示查询所有的字段）

SELECT * FROM stu WHERE gender='female' AND age<50;

#查询学号为s_1001，或者名字为1iSi的记录

SELECT * FROM stu WHERE sid='S_1001' OR sname='lisi';

#查询学号为s_1001，s_1002，s_1003的记录

SELECT * FROM stu WHERE sid='S_1001' OR sid='S_1002' OR sid='S_1003';

#使用IN关键字实现上述需求

SELECT * FROM stu WHERE sid IN('S_1001','S_1002','S_1003');

#查询学号不是S_1001，S_1002，S_1003的记录

SELECT * FROM stu WHERE sid!='S_1001' AND sid!='S_1002' AND sid!='S_1003';
SELECT * FROM stu WHERE sid NOT IN('S_1001','S_1002','S_1003');

#查询年龄为NULL的记录

SELECT * FROM stu WHERE age IS NULL;

#查询年龄在20到40之间的学生记录

SELECT * FROM stu WHERE age>=20 AND age<=40; 

#使用BETWEEN AND 关键字来实现

SELECT * FROM stu WHERE age BETWEEN 20 AND 40;#包含20和40两个临界值

#查询性别非男的学生记录
#非男就是女，查询性别为女的学生记录

SELECT * FROM stu WHERE gender='female';
SELECT * FROM stu WHERE gender!='male';
SELECT * FROM stu WHERE gender<>'male';
SELECT * FROM stu WHERE NOT gender='male'; #注意：NOT 需要写在字段名前面

#查询姓名不为null的学生记录

SELECT * FROM stu WHERE sname IS NOT NULL;
SELECT * FROM stu WHERE NOT sname IS NULL;

#模糊查询，需要使用关键字like，配合两个通配符来使用，

        _：表示任意一个字符；

        %：表示任意0~n个字符

#查询名字由5个字母构成的学生记录

SELECT * FROM stu WHERE sname LIKE '_____';

#查询姓名由5个字母构成，并且第五个字母为“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '____i';

#查询名字以“z”开头的学生记录

SELECT * FROM stu WHERE sname LIKE'z%';

#查询名字中第二个字母为“i”的学生记录

SELECT * FROM stu WHERE sname LIKE '_i%';

#查询姓名中包含“a”字母的学生记录

SELECT * FROM stu WHERE sname LIKE '%a%';

#字段控制查询
#去除重复记录
#通过emp表来查询一下员工所在的部门有哪些

SELECT deptno FROM emp;

#使用DISTINCT关键字去除重复记录

SELECT DISTINCT deptno FROM emp;

#一个数与null进行算术运算，结果为NULL
#查询员工的所有信息以及薪水和佣金之和

SELECT *,sal+comm FROM emp;#由于有的comm值为null，所以在于sal相加的时候，结果也为NULL。此时我们需要将NULL转换为0

#使用IFNULL（字段名，数据）函数将某个字段值为NULL的值转换为你指定的数据

SELECT *,sal+IFNULL(comm,0) FROM emp;

#给字段名取别名，使用AS关键字来实现，AS关键字可以省略

SELECT empno AS '员工编号',ename AS '员工姓名'FROM emp;
SELECT sal+IFNULL(comm,0) '薪水和佣金之和' FROM emp;

#排序，我们将数据按照要求查询出来后，可能需要按照某个字段进行升序或者降序排序，需要使用ORDER BY关键字进行排序，升序关键字ASC（升序可以省略不写），降序关键字DESC
#查询员工的所有信息，并按照员工的sal进行升序排序

SELECT *FROM emp ORDER BY sal ASC;
SELECT * FROM emp ORDER BY sal;#升序关键词DESC可以不写，默认是升序排序

#查询员工的所有信息，并按照员工的sal进行降序排序

SELECT * FROM emp ORDER BY sal DESC;

#按照某个字段进行排序后发现，有的字段值是一样，这个时候数据按照数据表中的顺序进行排序，我们也可以再原作一个字段进行排序
#查询员工的所有信息并按照sal进行升序排序，当sal值相同时，按照员工编号empon降序排序

SELECT * FROM emp ORDER BY sal ASC,empno DESC;

#查询所有学生记录，按照年龄升序排序

SELECT * FROM stu ORDER BY age ASC;
SELECT * FROM stu ORDER BY age;

#查询所有学生记录，按照年龄降序排序

SELECT * FROM stu ORDER BY age DESC;

#聚合函数：用来对查询结果进行纵向统计的
#COUNT（）：统计指定列不为null的记录行数
#统计emp表中有佣金的人数

SELECT COUNT(comm) AS '有佣金的人数' FROM emp;

#统计一共有多少员工

SELECT COUNT(*) '公司员工人数' FROM emp;

#统计多少员工有领导

SELECT COUNT(mgr) FROM emp;

#查询emp表中月薪大于2500的人数

SELECT COUNT(*) AS '月薪大于2500的人数' FROM emp WHERE sal>2500;

#统计月薪与佣金之和大于2500元的人数
#查询出月薪和佣金之和大于2500的员工记录

SELECT * FROM emp WHERE sal+IFNULL(comm,0)>2500;
SELECT COUNT(*) '月薪与佣金之和大于2500元的人数' FROM emp WHERE sal+IFNULL(comm,0)>2500;

#查询有佣金的人数，以及有领导的人数

SELECT COUNT(comm) AS '有佣金的人数',COUNT(mgr) AS '有领导的人数' FROM emp;

#sum()和avg()函数
#查询所有雇员月薪和：

SELECT SUM(sal) AS '所有雇员月薪和' FROM emp;

#查询所有雇员月薪和，以及所有雇员佣金和

SELECT SUM(sal) AS '所有雇员月薪和',SUM(comm) AS '所有雇员佣金和' FROM emp;

#查询所有雇员月薪+佣金和

SELECT SUM(sal+IFNULL(comm,0)) AS ' 所有雇员月薪+佣金和' FROM emp;

#统计所有员工平均工资

SELECT AVG(sal) FROM emp;

#查询最高工资和最低工资

SELECT MAX(sal) AS '最高工资',MIN(sal) AS '最低工资' FROM emp;

#MySQL数据库中，除了提供上述聚合函数外，还有很多的函数提供给我们使用，比如：

SELECT CONCAT('Hello','World','Welcome','To','China');
SELECT LOWER('QWERTYUIOP');#转换成小写
SELECT UPPER('qwertyuiop');#转换成大写
SELECT RAND();
SELECT ADDDATE("2022-80-31",INTERVAL 25 DAY);#
SELECT CURDATE();
SELECT CURRENT_TIME();
SELECT DATEDIFF('1992-04-07','2022-08-31');
SELECT BIN(2);#二进制

#分组查询：按照某个字段来分组，分组后可以进行数据统计，分组查询需要使用group by 关键字实现
#凡是和聚合函数同时出现的列名，一点要写在group by后面
#查询每个部门的部门编号和每个部门的工资和

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;

#查询每个部门的部门编号和每个部门的人数

SELECT deptno AS '部门编号',COUNT(*) AS '部门人数' FROM emp GROUP BY deptno;

#查询每个部门的部门编号以及每个部门工资大于1500的人数

SELECT deptno AS '部门编号',COUNT(*) AS '部门工资大于1500的人数' FROM emp WHERE sal>1500 GROUP BY deptno;

#查询stu表中男生和女生各有多少人

SELECT gender,COUNT(*) FROM stu GROUP BY gender;

#HAVING子句：对分组以后的数据再次进行过滤
#查询工资总和大于9000的部门编号以及工资和

#查询部门编号以及每个部门的工资和

SELECT deptno ,SUM(sal) FROM emp GROUP BY deptno;

#查询工资总和大于9000的部门编号以及工资和

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

#LIMT:对查询出来的结果进行显示的限定

SELECT * FROM emp;

#查询emp表中的数据，显示前三条

SELECT * FROM emp LIMIT 0,3;

#查询emp表中的数据，从第三条（记录的下标为2）开始显示，一共显示三条

SELECT * FROM emp LIMIT 2,3;

#数据完整性
#实体完整性：确保表中每一行数据不重复
#主键：主键具有唯一性，不能为NULL

CREATE TABLE `student1`(
`id` INT PRIMARY KEY,
`name` VARCHAR(50)
);
 
CREATE TABLE `student2`(
`id` INT,
`name` VARCHAR(50),
PRIMARY KEY (`id`)
);

#第二种方式创建主键可以创建联合主键

CREATE TABLE `student3`(
`classid` INT,
`stuid` INT,
`name` VARCHAR(50),
PRIMARY KEY (`classid`,`stuid`)
);

#表创建的时候没有添加主键，可以通过修改来添加主键

CREATE TABLE `student4`(
`id` INT,
`name` VARCHAR(50)
);
ALTER TABLE student4 ADD PRIMARY KEY (`id`);

#唯一约束UNIQUE，使用UNIQUE修饰的字段，里面的值具有唯一性，只能出现一次

CREATE TABLE `student5`(
`Id` INT PRIMARY KEY,
`Name` VARCHAR(50) UNIQUE
);

#引用完整性-外键_一个表中某个字段里的数据来源于另一个表中某个字段的数据

CREATE TABLE `student6`(
`sid` INT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
`sex` VARCHAR(10) DEFAULT '男' 
);
 
CREATE TABLE `score1`(
`id` INT,
`score` INT,
`sid` INT,-- 外键列的数据类型一定要与主键的类型一致
CONSTRAINT fk_score1_sid FOREIGN KEY (sid) REFERENCES student6(sid)
);

#添加外键的第二种方式：两个表在创建的时候没有关联外键，可以通过修改表来创建外键

CREATE TABLE `student7`(
`sid` INT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
`sex` VARCHAR(10) DEFAULT '男'
);
 
CREATE TABLE `score2`(
`id` INT,
`score` INT,
`sid` INT -- 外键列的数据类型一定要与主键的类型一致
);

#将表score2里的sid字段引用外键，关联student7表中的sid字段

ALTER TABLE score2 ADD CONSTRAINT fk_student7_score2 FOREIGN KEY(sid) REFERENCES student7(sid);

#合并结果集：合并结果集就是把两个select语句的查询结果合并到一起
#合并结果集的要求：被合并的两个结果：列数、列类型必须相同
#UNION：去除两个表中重复的数据

SELECT * FROM t1 
UNION 
SELECT * FROM t2

#UNION ALL：合并结果集，不除去重复记录

SELECT * FROM t1
UNION ALL
SELECT * FROM t2

#连接查询

SELECT * FROM t1,t2;

#员工表emp表中有14条数据，部门表dept中有4条数据，这两个表进行连接查询，会产生14*4=56条数据

SELECT * FROM emp,dept;

#emp表和dept表中有一个相同的字段deptno，可以通过这个相同的字段来进行数据过滤

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

#查询指定列

SELECT empno,ename,dept.deptno,loc FROM emp,dept WHERE emp.deptno=dept.deptno;

#给表取别名：当表的名称比较长的时候，书写起来比较费事，这个时候就可以给表取个简单的别名

SELECT e.empno,e.ename,e.deptno,d.dname,d.loc FROM emp e, dept d WHERE e.deptno=d.deptno;

#上述SQL语句就是内连接查询，不是标准的内连接查询，是MySQL中的内连接查询

#标准内连接查询

SELECT * FROM emp e INNER JOIN dept d ON e.`deptno`=d.`deptno`
SELECT e.`empno`,e.`ename`,e.`deptno`,d.`dname` FROM emp e INNER JOIN dept d NO e.`deptno`=d.`deptno`;

#内连接查询的特点：不满足条件的数据不会显示出来，比如emp表中有一个员工zhangsan是50号部门，但是部门表中没有50号部门，所以zhangsan这条数据不会显示出来

#外连接：外连接可以解决上述内连接中查询不出不满足条件的数据
#外连接分为左外连接和右外连接
#左外连接：先将左边表emp的数据全部查询出来，然后再去右边表dept中查询数据，右边表满足条件的数据全部显示出来，不满足条件的数据所有字段全部显示为null

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.`deptno`=dept.`deptno`;

#右外连接：先将右边表dept表中的数据全部查询出来，然后再去左边表emp中查询数据，左边表满足条件的数据全部显示出来，不满足条件的数据所有字段全部显示为null

SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.`deptno`=dept.`deptno`;

#自然连接：不需要我们主动给出两个表中的过滤条件，而是通过MySQL自动给出，要求进行连接查询的两个表中有相同名称和类型的字段
#自然内连接

SELECT * FROM emp NATURAL JOIN dept;

#自然左外连接

SELECT * FROM emp NATURAL LEFT JOIN dept;

#自然右外连接

SELECT * FROM emp NATURAL RIGHT JOIN dept;

#子查询：一个select语句中包含另一个select语句（select语句嵌套查询）

#查询工资高于2500的员工信息

SELECT * FROM emp WHERE sal>2500;

#1.工资高于JONES的员工
#第一步：我们需要知道JONES员工的工资是多少

SELECT sal FROM emp WHERE ename='JONES'; #2975

#第二步：查询工资高于JONES的员工 -->查询工资高于2975的员工信息

SELECT * FROM emp WHERE sal>2975;

#将2975替换成查询语句

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='JONES');

#查询与SCOTT同一个部门的员工
#第一步：我们需要知道SCOTT这个员工是哪一个部门的

SELECT deptno FROM emp WHERE ename ='SCOTT'; #20

#第二步：查询与SCOTT同一部门的员工 -->查询20号部门的员工信息

SELECT * FROM emp WHERE deptno=20;
 
SELECT * FROM emp WHERE deptno =(SELECT deptno FROM emp WHERE ename = 'SCOTT');

#工资高于30号部门所有人的员工信息
#第一步：我们需要知道30号部门工资最大值

SELECT MAX(sal) FROM emp WHERE deptno =30; #2850

#工资高于30号部门所有人的员工信息 --->工资高于2850的员工信息

SELECT * FROM emp WHERE sal>2850;
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);

#第二种写法

SELECT sal FROM emp WHERE deptno=30;
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

#查询工作和工资与MARTIN（马丁）完全相同的员工信息
#第一步：查询MARTIN的工资和工资

SELECT job FROM emp WHERE ename ='MARTIN'; #SALESMAN
SELECT sal FROM emp WHERE ename ='MARTIN'; #1250

#第二步：查询工作为SALESMAN，工资为1250的员工信息

SELECT * FROM emp WHERE job='SALESMAN' AND sal=1250;
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename ='MARTIN') AND sal=(SELECT sal FROM emp WHERE ename='MARTIN');

#上面写法可以实现需求，但是比较复杂，可以换一个简单一点的写法

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN');

#有2个以上直接下属的员工信息 ---->领导也是员工，领导和普通员工都要员工编号
#第一步：查询mgr字段，显示mgr字段值出现过两次以上的

SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2; #7566 7698 7839这三个人

#第二步：有2个以上直接下属的员工信息 --->查询员工编号7566 7698 7839的员工信息、

SELECT * FROM emp WHERE empno =7566 OR empno=7698 OR empno=7839;
SELECT * FROM emp WHERE empno IN(7566,7698,7839);

#合并上述语句类写

SELECT * FROM emp WHERE empno IN(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);