mysql 笛卡尔去重_Mysql数据库操作

Mysql数据库操作

1、概述

Mysql是一个关系型数据库,一个保存关系型信息的数据库,比如我们可以在这个数据库中保存用户信息,这里我们主要将如何进行Mysql操作(增、删、改、查)

2、创建数据表

2-1、简单创建

CREATE TABLE `employees` (

`employee_id` char(6) NOT NULL,

`full_name` varchar(25) NOT NULL,

`email` varchar(25),

`phone_number` varchar(25) ,

`department_id` char(4) ,

`manage_id` char(6),

`hire_date` date NOT NULL,

`on_the_job` tinyint(1) NOT NULL DEFAULT 1 ,

PRIMARY KEY (`employee_id`),

);

2-2、完整创建

-- 员工表

CREATE TABLE `employees` (

`employee_id` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '员工工号',

`full_name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '员工姓名',

`email` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '员工邮箱',

`phone_number` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '员工手机号码',

`department_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '部门id',

`manage_id` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '上级领导工号',

`hire_date` date NOT NULL COMMENT '入职日期',

`on_the_job` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否在职(离职)',

PRIMARY KEY (`employee_id`, `manage_id`) USING BTREE,

INDEX `employees_ibfk_1`(`department_id`) USING BTREE,

CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE SET NULL ON UPDATE SET NULL

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = COMPACT;

-- 薪资表

CREATE TABLE `salarys` (

`employee_id` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '员工id',

`salary` float(10, 2) NULL DEFAULT NULL COMMENT '员工薪资',

`start_time` date NULL DEFAULT NULL COMMENT '薪资的起始时间',

`end_time` date NULL DEFAULT NULL COMMENT '薪资的结束时间',

INDEX `employee_id`(`employee_id`) USING BTREE,

CONSTRAINT `salarys_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

-- 部门表

CREATE TABLE `departments` (

`department_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门id',

`department_name` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门名',

`manage_id` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '部门主管工号',

`used` tinyint(1) NOT NULL DEFAULT 1 COMMENT '部门是否被弃用',

PRIMARY KEY (`department_id`) USING BTREE,

INDEX `manage_id`(`manage_id`) USING BTREE,

CONSTRAINT `departments_ibfk_1` FOREIGN KEY (`manage_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL ON UPDATE SET NULL

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

3、简单操作之插入数据

INSERT INTO `departments` VALUES ('1', '财务部', NULL, 1);

INSERT INTO `departments` VALUES ('2', '后勤部', NULL, 1);

INSERT INTO `departments` VALUES ('3', '保安部', NULL, 1);

INSERT INTO `departments` VALUES ('4', '客服部', NULL, 1);

INSERT INTO `departments` VALUES ('5', '运营部', NULL, 1);

INSERT INTO `departments` VALUES ('6', '技术部', NULL, 1);

INSERT INTO `employees` VALUES ('1', 'whether1', '12345@qq.com', '123456', '1', '', '2020-11-11', 1);

INSERT INTO `employees` VALUES ('2', 'whether2', '12345@qq.com', '123456', '1', '1', '2020-11-11', 1);

INSERT INTO `employees` VALUES ('3', 'whether3', '987896@qq.com', '123456', '2', '1', '2020-10-11', 1);

INSERT INTO `employees` VALUES ('4', 'whether4', '8970589@qq.com', '123456', '3', '2', '2020-09-11', 1);

INSERT INTO `employees` VALUES ('5', 'whether5', 'ajklshd@qq.com', '123456', '4', '2', '2010-11-11', 1);

INSERT INTO `employees` VALUES ('6', 'whether6', 'kb123@qq.com', '123456', '5', '3', '2012-11-11', 1);

INSERT INTO `salarys` VALUES ('1', 5000.00, '2020-11-11', '2999-01-01');

INSERT INTO `salarys` VALUES ('2', 5000.00, '2020-11-11', '2999-01-01');

INSERT INTO `salarys` VALUES ('3', 8000.00, '2020-10-11', '2999-01-01');

INSERT INTO `salarys` VALUES ('4', 10000.00, '2020-09-11', '2999-01-01');

INSERT INTO `salarys` VALUES ('6', 3000.00, '2012-11-11', '2999-01-01');

INSERT INTO `salarys` VALUES ('5', 2000.00, '2010-11-11', '2015-01-01');

INSERT INTO `salarys` VALUES ('5', 2000.00, '2015-01-02', '2999-01-01');

4、简答操作之查找数据

4-1、查找所有、查找某个字段

SELECT * FROM employees;

SELECT full_name FROM employees;

4-2、数据表、字段起别名

SELECT e.employee_id,e.full_name as name FROM employees AS e;

4-3、查询去重

-- 注意这里去重是去重复的employee_id,如果查询的字段增加,则需要满足同时多个字段都相同时才会进行去重

-- 即数据的重复条件看的是结果要显示的字段,结果之显示employee_id,则满足employee_id不重复即可

SELECT DISTINCT employee_id FROM salarys;

4-4、条件查询,查询满足某个条件的数据

-- 查询employee_id大于3的数据

SELECT * FROM employees WHERE employee_id>3;

5、运算符、函数的使用

5-1、算术运算符 +、-、*、/

/*

就是算术运算符,

只能用于数字的运算,如果是字符串型数字,也可以实现相加,如果遇到null，则结果必定为null

*/

-- 注意这个操作完全没有意义,这里只用于举例

SELECT employee_id+salary FROM salarys;

5-2、比较运算符 >、=、<>、!=、is

-- 大于、小于、等于(不能判断NULL的情况)、小于等于、大于等于、不等于、不等于、null的判断需要使用is null/is not null来判断

SELECT * FROM employees WHERE employee_id>3;

5-2、逻辑运算符 and、or、not

SELECT * FROM employees WHERE employee_id>3 AND employee_id<5

5-3、between、in

SELECT * FROM employees WHERE employee_id BETWEEN 3 AND 5;

-- 相当于SELECT * FROM employees WHERE employee_id>=3 AND employee_id<=5

SELECT * FROM employees WHERE employee_id IN (3,4);

-- 相当于SELECT * FROM employees WHERE employee_id=3 OR employee_id=4

5-4、安全等于<=>

-- 即可以当做 = 来使用,也可以当做 is 使用,不建议使用

SELECT * FROM employees WHERE employee_id <=> 3;

SELECT * FROM employees WHERE employee_id <=> NULL;

5-5、内置字符串连接函数---CONCAT

-- 将id与'_'与full_name进行拼接,比如其中一个结果为1_whether1

SELECT CONCAT(employee_id,'_',full_name) FROM employees;

5-6、聚合函数求和---SUM

聚合函数就是将查询结果聚合成一条结果

-- 只输出求和后的结果

SELECT SUM(salary) FROM salarys;

5-7、聚合函数求平均---AVG

SELECT AVG(salary) FROM salarys;

5-8、聚合函数求最大值---MAX

-- 求这一列最大的数据

SELECT MAX(salary) FROM salarys;

5-9、聚合函数求最小值---MIN

-- 求这一列最小的数据

SELECT MIN(salary) FROM salarys;

5-10、聚合函数统计数量---COUNT

-- 建议使用*而不是某一列名,因为COUNT不会统计NULL值的那一列,同时*的效率可能会高一点

SELECT COUNT(*) FROM salarys;

5-11、内置是否为NULL判断函数---IFNULL

-- 如果salary为NULL,则将其替换成1000进行计算

SELECT SUM(IFNULL(salary,1000)) FROM salarys;

5-12、内置判断函数---IF

-- 如果salary为NULL,则将其替换成"没有奖金",否则便输出salary

SELECT employee_id,IF(salary IS NULL,"没有奖金",salary) FROM salarys;

6、模糊查询Like

6-1、通配符 _、%

-- _表示任意一个字符,%表示任意多个字符,包括0个字符

-- 查询以email以123开头的数据

SELECT * FROM employees WHERE email LIKE '123%';

7、排序查询

7-1、简单排序查询

-- 默认升序排序查询

SELECT * FROM salarys ORDER BY salary;

-- 指定升序/降序排序查询

SELECT * FROM salarys ORDER BY salary ASC;

SELECT * FROM salarys ORDER BY salary DESC;

-- 指定规则排序

SELECT * FROM salarys ORDER BY FIELD(employee_id,5,4,3,1,2);

8、分组查询

分组函数的作用就是将数据以某个(或某几个)字段进行分组

8-1、分组查询的简单使用

-- 一个企业有多个部门,每个部门有多个人,以下分组查询将以部门为分组条件进行部门id以及部门人数查询

SELECT department_id,COUNT(*) FROM employees GROUP BY department_id;

8-2、分组查询搭配聚合函数使用

-- 使用内置函数并DISTINCT搭配使用,查询不同工资的数量

SELECT COUNT(DISTINCT salary) FROM salarys;

8-3、分组查询添加分组后条件---HAVING

能用WHERE进行筛选的也一定能用HAVING进行筛选，反之则不行，建议能用WHERE进行筛选的就用WHERE进行筛选，不能使用WHERE筛选的再使用HAVING进行筛选(WHERE筛选在HAVING之前，越早筛选掉不合适的数据越好，提高查询效率)，使用聚合函数后求得的列只能使用HAVING进行筛选。

-- 使用聚合函数后的字段(列),在后面只能根据分组后的条件进行约束

-- 添加分组条件,显示部门人数大于1的部门以及部门人数

SELECT department_id,COUNT(*) AS department_number FROM employees GROUP BY department_id HAVING department_number > 1;

9、多表连接查询

9-1、内连接

使用...... INNER JOIN ...... ON(sql99标准，99年)或者...... JOIN ...... ON(sql99标准，99年)或者...... table1,table2 FROM(sql92标准，92年)连接两表为内连接

特点：所有不满足连接条件的数据都不会出现

比如某一个员工在工资表中没有工资信息，那么在结果中将不会存在这个员工信息

9-1-1、笛卡尔乘积

-- 将employees中的每一项数据与departments中的每一项数据进行拼接,比如employees中有6条数据,departments中也有6条数据,那查询结果有6*6=36条数据。

SELECT * FROM employees, departments;

-- 或者

SELECT * FROM employees CROSS JOIN departments;

9-1-2、等值连接查询

-- 将两表连接,同时需要满足条件employees.employee_id = salarys.employee_id

SELECT * FROM employees, salarys WHERE employees.employee_id = salarys.employee_id;

-- 表起别名

SELECT * FROM employees AS e, salarys AS s WHERE e.employee_id = s.employee_id;

-- 或者

SELECT * FROM employees AS e JOIN salarys AS s ON e.employee_id = s.employee_id;

9-1-3、自连接

SELECT e1.employee_id,e1.full_name,e2.full_name AS leader_name FROM employees AS e1, employees AS e2 WHERE e1.manage_id = e2.employee_id;

-- 或者

SELECT e1.employee_id,e1.full_name,e2.full_name AS leader_name FROM employees AS e1 INNER JOIN employees AS e2 ON e1.manage_id = e2.employee_id;

9-2、外连接

特点：与内连接不同，特殊情况下，也会根据连接情况使用NULL进行连接

比如如果使用左外连接(左边的表为主表，其数据必定存在)，如果右边表没有符合条件的数据，将会使用NULL值填充右部分数据。

9-2-1、左外连接

SELECT e1.employee_id,e1.full_name,e2.full_name AS leader_name FROM employees AS e1 LEFT JOIN employees AS e2 ON e1.manage_id = e2.employee_id;

9-2-1、右外连接

SELECT e1.employee_id,e1.full_name,e2.full_name AS leader_name FROM employees AS e1 RIGHT JOIN employees AS e2 ON e1.manage_id = e2.employee_id;

10、子查询

10-1、标量(单行)子查询---子查询的结果必须是单一值的标量(一行一列)

-- 查询工资比1号员工工资多的员工信息

-- 1.先查询1号员工的工资

SELECT salary from salarys WHERE employee_id = 1;

-- 2.将第一步的结果使用括号括起来并将其作为一个值

SELECT e.*,salary FROM employees AS e,salarys as s

WHERE e.employee_id=s.employee_id AND s.salary>(

SELECT salary from salarys WHERE employee_id = 1

);

10-2、列(多行)子查询---子查询的结果可以是多行，但必须还是一列

10-2-1、子查询与IN的联合使用

-- IN:一组值中等于其中任意一个即可

-- 查询与1号员工或者3号员工工资相同的员工信息

-- 1.先查询1号员工或者3号员工的工资

SELECT salary FROM salarys WHERE employee_id IN(1,3);

-- 2.将第一步的结果使用括号括起来并作为一组值

SELECT e.*,salary FROM employees AS e,salarys as s

WHERE e.employee_id=s.employee_id AND s.salary IN(

SELECT salary FROM salarys WHERE employee_id IN(1,3)

);

10-2-2、子查询与ANY/SOME的联合使用

-- ANY/SOME:需要与比较操作符一起使用,满足一组值中的任意一个即可

-- 查询工资高于1号员工或者3号员工工资员工信息

SELECT e.*,salary FROM employees AS e,salarys as s

WHERE e.employee_id=s.employee_id AND s.salary>ANY(

SELECT salary FROM salarys WHERE employee_id IN(1,3)

);

10-2-3、子查询与ALL的联合使用

-- ALL:与ANY类似,但是需要满足一组值中的所有数值

-- 查询工资同时高于1号员工与3号员工工资员工信息

SELECT e.*,salary FROM employees AS e,salarys as s

WHERE e.employee_id=s.employee_id AND s.salary>ALL(

SELECT salary FROM salarys WHERE employee_id IN(1,3)

);

10-3、单行子查询与多行子查询的总结

子查询的结果可以是单行的也可以是多行的，如果结果是单行的，直接可以使用运算符进行比较即可，但如果是多行的，则需要使用IN、ANY、ALL来判断多行值与条件的关系(是满足其中的一项还是需要满足其中的所有项)。

10-4、相关子查询

-- 在EXISTS内部嵌套一个查询,用于判断子查询有无结果,如果查到值,返回1,反之没有值,返回0

SELECT EXISTS(SELECT employee_id FROM employees WHERE employee_id = 1);

11、分页查询

-- 分页查询语法:SELECT 查询列表 FROM 查询表名 WHERE 查询条件 LIMIT (offset,)size

-- offset为数值,表示查询的起始索引,size表示查询的数据个数

-- 非常需要注意的是,起始索引从0开始

SELECT * FROM employees LIMIT 0,5;

-- 后端使用的sql语句,page表示第几页,size表示一页显示多上行数据

SELECT * FROM employees LIMIT (page-1)*size,size;

12、联合查询

-- 将两个查询结果进行拼接,结果类似于使用OR或者IN

-- SELECT * FROM employees WHERE employee_id=1 OR employee_id=1

-- 相同表查询

SELECT * FROM employees WHERE employee_id=1

UNION

SELECT * FROM employees WHERE employee_id=2

-- 或者查询两个不同的表,但是查询的列数必须是一样的,这个案例不合适,应该查询两张相似功能的表,但是其字段名不同(存放的内容相同)

-- 比如一张是中国员工表,外国员工表

SELECT employee_id,full_name FROM employees WHERE employee_id=1

UNION

SELECT employee_id,salary FROM salarys WHERE employee_id=2

-- UNION默认去重,可以使用UNION ALL进行不去重