MySQl数据库查询语句基础

首先我们建立两个表格，用于后续查询使用

#创建雇员表
CREATE TABLE emp2(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
#添加数据
INSERT INTO emp2 values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp2 values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp2 values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp2 values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp2 values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp2 values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp2 values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp2 values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp2 values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp2 values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp2 values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);


CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
#添加数据
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');


 #创建表stu
CREATE TABLE stu (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
);
#添加数据
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);

-- 查询所有列 在考虑sql优化的情况下不建议使用* 直接使用字段
SELECT * FROM stu;
-- 查询指定的列 各字段之间用,隔开
SELECT sname,age,gender FROM stu;
-- 按条件查询
SELECT * FROM tb_2 WHERE name='关羽';

-- 条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字:=、!=、<>、<、<=、>、>=;BETWEEN.AND;IN(Set);IS NULL;AND;OR; NOT;
SELECT * FROM stu;
-- 并且年龄50以内查询性别为女
SELECT * FROM stu WHERE gender='female' and age< 50;
-- 查询学号为s1001，或者姓名为1isi的记录
SELECT * FROM stu where sid = 'S_1001' OR sname='liSi';
-- 查询学号为（s_1001,S_1002,S_1003)
SELECT * FROM stu WHERE sid IN('S_1001','S_1002','S_1003');
-- 查询年龄为空的记录
SELECT * FROM stu WHERE age is NULL
-- 年龄20-40
-- SELECT * FROM stu WHERE 20<age<40; 不对
SELECT * FROM stu WHERE  age>20 AND age<40;
SELECT * FROM stu WHERE age BETWEEN 20 and 40;
-- sex!=男
SELECT * FROM stu WHERE gender!='male';
SELECT * FROM stu WHERE gender<>'male';
SELECT * FROM stu WHERE not gender  ='male';

SELECT * FROM stu WHERE gender is  NULL;
-- -- 模糊查询 LIKE _ %
-- 查询学生由三个字组成的
select * from stu where sname LIKE '___';
-- 姓名以z开头
SELECT * FROM stu WHERE sname LIKE 'z%';
-- 第二个字母为i
SELECT * FROM stu WHERE sname LIKE '_i%';
-- 查 名字中包含"a"的人信息
SELECT * FROM stu WHERE sname LIKE '%a%';
create user test123@localhost IDENTIFIED by 'test123'
-- 去重  
SELECT * FROM emp;
SELECT DISTINCT sal from emp;
-- as 别名 IFNULL(a,b) a如果为空 则值为b
SELECT * ,sal+IFNULL(comm,0) as 总和 FROM emp;
-- 排序  order by  asc升序(默认） desc降序
SELECT * FROM stu ORDER BY age ASC;
SELECT * FROM stu ORDER BY age DESC;
-- 按月薪asl降序，相同按编号empno升序,主要排序，次要排序
SELECT * FROM emp ORDER BY sal DESC ,empno ASC;

-- 聚合函数--
-- 统计 count    max最大值 min最小值 sum总和 avg平均
SELECT COUNT(*) as 总数  FROM emp;
-- 有佣金的
SELECT COUNT(comm) as 有佣金的人数 FROM emp;
-- 月薪大于2500的
SELECT COUNT(*) as 月薪大于2500的人 FROM emp WHERE sal>2500;
-- 查询有佣金人数，以及有领导的人数
SELECT COUNT(comm),COUNT(mgr) FROM emp;
-- 月薪和
SELECT SUM(sal)  月薪和 FROM emp;
SELECT SUM(sal) 月薪和,SUM(comm) 佣金和 FROM emp;
-- 月薪和+佣金和
SELECT SUM(sal+IFNULL(comm,0))FROM emp;
-- 平均工资
SELECT AVG(sal) 平均工资 FROM emp;
-- 工资最大值最小值
SELECT MAX(sal) 最高工资 ,MIN(sal) 最低工资 FROM emp;


-- 分组查询 GROUP BY 
-- 查询每个部门编号和每个部门的工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
-- 查询每个部门编号和每个部门的人数
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
-- 查询每个部门编号和每个部门工资大于1500的人数
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
-- HAVING 子句
-- 查询工资总和大于9000的部门编号以及工资
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>8000
-- 分页查询 显示从第三条开始的5条信息
SELECT * FROM emp LIMIT 3,5;

我们再创建两个新表用于新的查询功能

CREATE TABLE student (
	studentno INT PRIMARY KEY auto_increment,
	loginPwd VARCHAR ( 20 ) NOT NULL DEFAULT '123456',
	studentname VARCHAR ( 50 ) NOT NULL,
	sex CHAR ( 20 ) NOT NULL,
	gradeid INT NOT NULL,
	phone VARCHAR ( 255 ) NOT NULL,
	address VARCHAR ( 255 ) DEFAULT '学生宿舍',
	borndate DATETIME,
	email VARCHAR ( 50 ) 
);
		
-- 多表查询
-- UNION ALL不去重复
-- UNION 去重复
CREATE TABLE dept1(
    deptno int primary key,
    dname varchar(14),
    loc varchar(13)
);
insert into dept1 values(10,'服务部','北京');
insert into dept1 values(20,'研发部','北京');
insert into dept1 values(30,'销售部','北京');
insert into dept1 values(40,'主管部','北京');
CREATE TABLE emp1(
    empno int,
    ename varchar(50),
    job varchar(50),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
);
insert into emp1 values(1001,'张三','文员',1006,'2019-1-1',1000,2010,10);
insert into emp1 values(1002,'李四','程序员',1006,'2019-2-1',1100,2000,20);
insert into emp1 values(1003,'王五','程序员',1006,'2019-3-1',1020,2011,20);
insert into emp1 values(1004,'赵六','销售',1006,'2019-4-1',1010,2002,30);
insert into emp1 values(1005,'张猛','销售',1006,'2019-5-1',1001,2003,30);
insert into emp1 values(1006,'谢娜','主管',1006,'2019-6-1',1011,2004,40);

接下来我们学习外连接

-- 表名1 INNER JOIN 表名2 ON 条件 as 给表设置别名
SELECT * FROM dept1 as d INNER JOIN emp1 as e ON e.deptno=d.deptno;
-- 外连接 左外，右外 OUTER JOIN （OUTER 可省略）
insert into emp1 values(1007,'何炅','主管',1006,'2019-6-1',1011,2004,50);
-- 左外连接查询 以左表为主 右表满足条件显示，否则显示null
SELECT * FROM emp1 e LEFT JOIN dept1 d ON e.deptno=d.deptno;
SELECT * FROM emp1 e RIGHT JOIN dept1 d ON e.deptno=d.deptno;

insert into dept1 values(60,'颜值部','成都');

SELECT * FROM emp1 e RIGHT OUTER JOIN dept1 d ON e.deptno=d.deptno;

我们可以看到外连接查询时，左外连接以左表为主 右表满足条件显示，否则显示null

右外连接反之

子查询：

子查询非常简单啊，我们来看一下代码

SELECT * FROM emp;
-- 查询工资高于Jones的员工
SELECT * FROM emp WHERE sal>(SELECT sal from emp WHERE ename='JONES');
-- 查询与scott同一个部门的员工
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT');
-- 查询工资高于30号部门所有人的员工信息
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);

子查询就是一个查询套着另一个查询，实现跨表筛选数据

视图：

-- 创建视图 
CREATE VIEW view_shitu as SELECT * FROM stu;
SELECT * FROM view_shitu;
-- 修改视图
ALTER VIEW view_shitu as select * from stu;
-- 查看视图
SHOW tables;
-- 查看创建视图的过程
SHOW CREATE VIEW view_shitu;
-- 删除视图
DROP VIEW view_shitu;
-- 查询视图数据
SELECT * FROM view_shitu WHERE sid='S_1002';
-- 视图中插入数据
INSERT INTO view_shitu(sid,sname,age,gender) VALUES ('S_1012','zy',20,'male');
-- 通过查看原表数据已经被插入到原表当中了
SELECT * FROM stu;
-- 做一个连表视图
CREATE view view_shitu2 as SELECT emp1.empno , emp1.ename,dept1.dname,dept1.loc FROM emp1,dept1 WHERE dept1.deptno=emp1.deptno;
-- 更新修改视图中的数据
UPDATE view_shitu set sname='mc' WHERE sid= 'S_1012';
SELECT * FROM view_shitu;
-- 删除视图的数据
DELETE FROM view_shitu WHERE sid='S_1012';

-- 视图2的更新数据和查询
UPDATE view_shitu2 set ename='法外狂徒张三' WHERE empno=1006;
SELECT * FROM view_shitu2;

有些同学不了解视图是干什么的，我们简单比喻一下

想象你在一个大型商店工作，这个商店有许多仓库存放各种商品。每个仓库都有详细的记录和标签，显示存放在其中的商品种类、数量和位置。现在，你是销售部门的员工，你需要经常查看销售部门的产品清单，以便了解有哪些产品可以销售，它们的名称和库存量。

在这个情景中：

• 实际数据库表 就像是整个商店的仓库，包含了详细的产品信息和库存。
• 视图 就像是一个定制的销售部门清单，只显示销售部门感兴趣的产品信息，例如产品名称和库存量。这个清单不需要包含所有仓库的信息，只显示特定的数据，而且随着仓库存货变动，清单会自动更新。

因此，视图就像是从庞大的数据库中提取并组织数据的一种方式，为特定用户或部门提供了一个简化和定制的数据视角，使得他们能够更有效地访问和使用数据，而不需要了解底层的复杂结构。

视图在数据库中有几个重要的意义和用途：

1. 简化数据访问和操作：

   • 视图通过将复杂的查询逻辑封装在一个虚拟的表结构中，使得用户可以通过简单的查询语句来访问和操作数据。这样，用户无需了解复杂的数据模型和表关系，而只需关注他们需要的数据部分。

2. 数据安全性和访问控制：

   • 视图可以用于控制用户或应用程序对数据的访问权限。通过向用户提供访问视图而不是直接访问底层表，可以限制用户只能查看或操作视图定义的数据范围，从而确保数据的安全性和保密性。

3. 简化复杂查询：

   • 当数据库中存在多个表，并且需要频繁地进行联合查询或者聚合操作时，视图可以极大地简化这些复杂查询的编写和维护。通过将常用的查询逻辑封装在视图中，可以提高查询的重用性和可维护性。

4. 逻辑数据独立性：

   • 视图提供了逻辑数据独立性，即可以在不影响应用程序的情况下修改底层表结构。如果视图的定义不变，即使底层表结构发生变化，用户和应用程序也可以继续使用视图而不需要修改其代码。

5. 性能优化：

   • 在某些情况下，数据库系统可以通过对视图进行优化，例如使用索引来提高查询性能。此外，视图可以减少数据重复存储和冗余，从而提高数据库的整体性能和效率

存储过程

存储过程是在数据库中预先编译和存储的一组 SQL 语句和逻辑，它们作为一个单元来执行特定的任务或操作。存储过程可以看作是数据库中的一种扩展，允许用户定义、保存和重复使用一系列的 SQL 查询和命令。

特点和用途：

1. 封装性：

   • 存储过程将一系列的 SQL 语句组合在一起，形成一个逻辑单元。这些语句可以包括查询、数据操作、流程控制语句（如条件和循环）、异常处理等，可以实现复杂的业务逻辑。

2. 代码复用：

   • 存储过程可以被多个应用程序或用户重复调用，从而提高了代码的重用性。它们可以减少在多个地方编写相同逻辑的需要，并简化了代码维护和更新的工作量。

3. 提高性能：

   • 存储过程通常比单独执行多个 SQL 查询更高效，因为它们可以减少数据库和应用程序之间的通信次数。数据库系统可以优化和缓存存储过程的执行计划，以提高查询的性能。

4. 安全性：

   • 存储过程可以用于实现数据访问控制和权限管理。通过存储过程，可以控制用户对数据的访问方式，避免直接暴露底层表结构和数据。

5. 事务管理：

   • 存储过程可以包含事务处理逻辑，确保多个操作要么全部成功执行，要么全部失败回滚，从而保持数据的一致性和完整性。

我们来看一下简单的创建、调用存储过程的语法

DELIMITER $$
-- 创建一个无参存储过程
CREATE PROCEDURE myprocedure01()
-- 从此开始
BEGIN
SELECT * FROM emp1;
SELECT * FROM dept1;
END $$
-- 将结束符换回来
DELIMITER ;
-- 调用存储过程
CALL myprocedure01();

DELIMITER $$
-- 创建一个有参存储过程 参数目前为无参 此处为形参（形参用于接受实际参数）
CREATE PROCEDURE myprocedure02(IN sename VARCHAR(50),sdname VARCHAR(14))
-- 从此开始
BEGIN
SELECT * FROM emp1 WHERE ename=sename;
SELECT * FROM dept1 WHERE dname=sdname;
END $$
-- 将结束符换回来
DELIMITER ;
-- 调用存储过程
CALL myprocedure02('张三','服务部');

这里我们可以看到，存储过程分为有参存储过程和无参存储过程

他们的区别就是有参存储过程可以传入参数到语句中，更灵活通用

这里我们讲一下DELIMITER $$这条语句

DELIMITER 是将分隔符（通常默认为' ; '）替换为 $$ ，防止将语句中的；误认为是存储过程结束 

而是等到$$才认为存储过程结束

编写完成后要将分隔符换回来：DELIMITER ;

函数

DELIMITER $$

CREATE FUNCTION calculate_sum(a INT, b INT)
RETURNS INT
BEGIN
    DECLARE sum INT;
    SET sum = a + b;
    RETURN sum;
END$$

DELIMITER ;

• DELIMITER $$：修改分隔符为 $$，这样在函数体内可以使用 ; 而不会导致语法错误。
• CREATE FUNCTION calculate_sum(a INT, b INT)：创建名为 calculate_sum 的函数，接收两个整数参数 a 和 b。
• RETURNS INT：声明函数返回整数类型。
• BEGIN...END：定义函数体，计算两个参数的和，并将结果存储在变量 sum 中。
• RETURN sum;：返回变量 sum 的值作为函数的结果。

在SQL语句中，可以像调用普通的内置函数一样调用自定义函数：

SELECT calculate_sum(5, 3);  -- 返回结果为 8

 事务

事务（Transaction）是数据库管理系统（DBMS）中的一个重要概念，用于管理对数据库的访问和操作。事务确保了数据库操作的原子性、一致性、隔离性和持久性，通常被简称为 ACID 特性：

1. 原子性（Atomicity）：

   • 原子性指事务是不可分割的单位，要么全部执行成功，要么全部失败回滚。如果事务执行过程中发生错误或者中断，所有的修改都会被撤销，数据库回滚到事务开始前的状态，保持数据的一致性。

2. 一致性（Consistency）：

   • 一致性指事务执行前后，数据库从一个一致性状态转变到另一个一致性状态。事务中的操作不会破坏数据库的完整性约束和业务规则，保证数据的逻辑正确性。

3. 隔离性（Isolation）：

   • 隔离性指多个事务并发执行时，每个事务的操作都应该被隔离开来，不会互相干扰。即使多个事务在同一时间并发执行，也不能相互影响，每个事务应该感觉不到其他事务存在。

4. 持久性（Durability）：

   • 持久性指一旦事务提交，其所做的修改将会永久保存在数据库中，即使系统崩溃或者重启，数据库也能恢复到事务提交后的状态。

事务的特点和使用场景：

• 原子性保证了操作的完整性：当需要执行一系列操作时，要么全部成功，要么全部失败回滚，避免了部分操作成功、部分失败的情况。

• 一致性保证了数据的有效性：在修改数据时，确保不会违反数据库的约束条件，如主键唯一性、外键关联等。

• 隔离性解决了并发问题：多个事务并发执行时，隔离性确保每个事务感受不到其他事务的存在，避免了脏读、不可重复读、幻读等问题。

• 持久性保证了数据的永久性：一旦事务提交，其修改将持久保存在数据库中，即使系统发生故障也能恢复。

比如假设有一个银行转账操作，需要保证转账操作的原子性，一致性和持久性：

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

在上面的示例中，BEGIN TRANSACTION 开始了一个事务，两个 UPDATE 语句分别扣除账户1的金额并增加账户2的金额。如果在操作过程中发生了错误或者中断，可以通过 ROLLBACK 回滚事务，确保转账操作是一个不可分割的整体，保证了数据的一致性和完整性。

总结来说，事务是数据库操作的一个重要机制，通过事务可以确保数据操作的完整性和一致性，同时处理并发访问和系统故障时的数据保护。