MySql复习之SQL语句

MySql复习之SQL语句

结构化查询语言，sql语句不区分大小写

DDL(Data Definition Language):数据定义语言 create drop alter
DML(Data Manipulation Language)： insert delete update
DQL(Data Query Language)： select
DCL(Data Control Language)：commit rollback

DDL语句

create：创建

drop：丢弃

alter：改变

1，创建一个表 tb_stu，SQL中多个单词使用_下划线分隔

• sql语句不区分大小写 ; 多个单词使用_下划线分隔

-- 学生信息表
CREATE TABLE tb_stu(
	id INT,
	sname VARCHAR(255),
	sgender CHAR(1),
	age INT,
	score FLOAT(4,2),
	birthday datetime,
	-- 最后一个字段没有逗号
	createtime date
)

2，alter 修改表结构(添加字段，删除字段，修改字段数据类型，修改表名)

-- 新增一个字段
ALTER TABLE t_stu ADD updatetime DATE;
-- 删除一个字段
ALTER TABLE t_stu DROP updatetime;
-- 修改表字段的数据类型
ALTER TABLE t_stu MODIFY sgender VARCHAR(1);
-- 修改表名
ALTER TABLE t_stu RENAME to stu;

3，drop

-- 删除数据库的命令
DROP DATABASE db_test; 
-- 删除数据表
DROP TABLE tb_test;

DML语句 insert delete update

1,insert 插入一行（记录）

-- 对所有字段依次赋值 VALUES和VALUE一样
insert INTO stu VALUES(1,'anne','m',16,6.6,'2020-10-22 11:33:20','2020-10-22');
-- 一次插入多条记录
insert INTO stu VALUE(2,'tom','m',16,6.6,'2020-10-22 11:33:20','2020-10-22'),(3,'jason','m',16,6.6,'2020-10-22 11:33:20','2020-10-22');
-- 指定字段插入
INSERT INTO stu(sid,sname) VALUES(2,'tom');

2,delete 删除 行/记录

-- 指定条件进行删除记录
DELETE FROM stu WHERE sid=2;

3,update 更新记录 修改，多个字段之间使用逗号

-- 修改表记录内容
UPDATE stu SET age=19,score=99.99 WHERE sid=1;

DQL语句（query）select

0.基本语法

select selection_list /*要查询的字段，多个字段用逗号隔开*/
from table_list /*要查询的表名称*/
[where condition /*筛选记录的条件*/
 group by grouping_clounms /*对结果进行分组*/
 having condition /*对分组后的记录进行条件筛选*/
order by cloumns /*对结果进行排序*/
 limit  /*对记录总数进行限定*/]

• select后面可以是表中的字段，常量值，表达式，函数；查询的结果是一个虚拟的表格；

1，基础查询，查询所有的列

-- 
SELECT * FROM t_stu;
-- 指定字段进行查询
SELECT sid,sname FROM t_stu;

2，条件查询

• 按条件表达式筛选，> < = !=( <>不等于) >= <=
• 逻辑表达式筛选 && || ！（ 等价于 and ,or,not）
• 模糊查询 like，between and（not between and 范围） ,in(列表中的值不支持通配符), is null(is not null)
  • null使用is

-- 指定某一个条件进行查询
SELECT * FROM t_stu WHERE courseid=2 
-- AND 是两个条件都要满足
SELECT * FROM t_stu WHERE courseid=2 AND score>60;
-- OR是条件只要满足一个就行
SELECT * FROM t_stu WHERE courseid=2 OR score>60;
-- 查询sid为 1，6，8的记录，属于某个集合
SELECT * FROM t_stu WHERE sid IN(1,6,8);
-- 查询sid不属于 1，6，8的记录
SELECT * FROM t_stu WHERE sid  NOT IN(1,6,8);
-- 查询记录某个字段为null 
SELECT * FROM t_stu WHERE sgender is null;
SELECT * FROM  tb_stu WHERE updatetime IS NOT null;
--  查询成绩在70到90区间范围内的记录
SELECT * FROM t_stu WHERE score BETWEEN 70 AND 90;
-- 性别非男的记录 <>和!= 都是 不等于
select * FROM t_stu WHERE sgender <> 'm';
select * FROM t_stu WHERE sgender != 'm';

3，模糊查询，处理字符类型

%：表示0或多个字符

_:任意一个字符

-- 名字由3个字母组成
SELECT * FROM t_stu WHERE sname LIKE '___';
-- 名字由j 开头
SELECT * FROM t_stu WHERE sname LIKE 'j%';
-- 第二个字母为a 的学生记录
SELECT * FROM t_stu WHERE sname LIKE '_a%';
-- 查询姓名中包含字母a的记录
SELECT * FROM t_stu WHERE sname LIKE '%a%';

-- 模糊查询 address是地址字段
SELECT * FROM t_stu WHERE address LIKE '北京市%';
SELECT * FROM t_stu WHERE address LIKE '北京市__';
SELECT * FROM t_stu WHERE sname LIKE '张%';

4，去重查询&起别名

• 表或者字段还可以起别名，起别名是便于理解，如果查询的字段有重名情况使用别名可以区分，
• AS可省略 使用空格

--  查询学生表中的所有性别
SELECT DISTINCT sgender FROM t_stu ;
-- DISTINCT去重 后面有多个字段 当这多个字段的值都相同的时候认为是重复的
SELECT DISTINCT sgender,age FROM t_stu;
-- 给查询出的字段起别名 AS是可以省略的 使用空格
SELECT age AS 年龄,sname AS 姓名 FROM  tb_stu;
SELECT sid a,sname b,sgender gender,score c FROM t_stu;

5，排序 order by子句可以跟单个字段，多个字段，表达式，函数，别名

-- 查询所有学生记录，按成绩进行降序排序
-- 缺省是ASC升序
SELECT * FROM t_stu ORDER BY score DESC;
-- 查询所有学生记录，首先先按成绩进行降序排序，如果成绩相同，按名字进行升序排序
SELECT * FROM t_stu ORDER BY score DESC, sname ASC;

6，组函数/聚合函数/分组函数

用作统计使用，又称为聚合函数或者统计函数或者组函数

• 聚合函数是用来做纵向运算的函数：
• COUNT(字段)：统计指定列不为NULL的记录行数；一般使用count（*）统计行数
• MAX(字段)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• MIN(字段)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
• SUM(字段)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
• AVG(字段)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

sum,avg一般处理数值型,

max，min,count可以处理任意数据类型

分组函数都忽略了null值，可以和distinct搭配使用

注意点：组函数可以出现多个，但是不能嵌套；如果没有group by 子句，结果集中所有行数作为一组

-- 查询emp表中有佣金的人数,统计指定列不为NULL的记录行数
SELECT count(comm) a FROM emp;
-- SELECT 200+null;
SELECT count(empno) 总人数 FROM emp;
SELECT count(*) 总人数 FROM emp;
SELECT count(1) 总人数 FROM emp;
-- 查询emp表中有佣金的人数
SELECT count(comm) from emp;
-- 查询emp表中月薪大于2500的人数：
SELECT * FROM emp WHERE sal>2500;
-- 统计月薪与佣金之和大于2500元的人数： ifnull(表达式1，表达式2)  如果表达式1为null那么取表达式2的值，否则取表达式1的值
SELECT * FROM emp WHERE sal+ifnull(comm,0) >2500;
-- 查询有佣金的人数，以及有领导的人数：
SELECT count(comm) FROM emp WHERE mgr is not null;
-- 查询所有雇员月薪和：
SELECT sum(sal) 总薪资  FROM emp;
-- 查询所有雇员月薪和，以及所有雇员佣金和：
SELECT sum(sal) 总薪资,SUM(comm) 总佣金 FROM emp;
-- 查询所有雇员月薪+佣金和
SELECT sum(sal+ifnull(comm,0)) 总佣金 FROM emp;
SELECT sum(sal)+sum(ifnull(comm,0)) 总佣金 FROM emp;
-- 统计所有员工平均工资
SELECT avg(sal) 平均薪资 FROM emp;
-- 查询最高工资和最低工资
SELECT max(sal),min(sal) FROM emp;

7，分组查询 group by

查询出来的字段要求是group by后的字段，查询字段中可以出现组函数

select 后面的字段 是分组字段

group by后面可以跟聚合函数 可以起别名

-- 	查询每个部门的部门编号和每个部门的工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门的人数
SELECT deptno,SUM(sal),COUNT(1) FROM emp GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门员工工资大于1500的人数：
SELECT deptno,COUNT(1) FROM emp WHERE sal>1500 GROUP BY deptno;

按多个字段分组，后面字段一致的为一组

-- 按job进行分类
SELECT COUNT(*),job FROM emp GROUP BY job;
-- 按job和mgr进行分类
SELECT COUNT(*),job,mgr FROM emp GROUP BY job,mgr;

8，having子句

where是对分组前进行过滤；having是对分组后进行过滤

where中不能出现分组/聚合函数，having中可以出现

where是比分组先执行的，having是在分组之后执行的；

having后面可以跟别名

-- 查询工资总和大于9000的部门编号以及工资和：
SELECT deptno,sum(sal)  FROM emp GROUP BY deptno HAVING sum(sal)>9000;
-- having中使用别名
SELECT deptno,sum(sal) 总薪资 FROM emp GROUP BY deptno HAVING 总薪资>9000;

-- 查询部门员工个数大于3的，having中使用了别名
SELECT COUNT(1) cc,deptno FROM emp GROUP BY deptno HAVING cc>3;

9，limit 第一位表示起始索引位置，第二位表示总的长度(总的记录数)；在分页中会使用

-- 第一位表示起始索引位置，第二位表示总的长度(总的记录数)；在分页中会使用
SELECT * FROM emp LIMIT 1,5;
SELECT * FROM emp LIMIT 5; 等价与 SELECT * FROM emp LIMIT 0, 5;

select完整语法使用

SELECT deptno,count(1),sum(sal) allsum FROM emp WHERE sal>1500 GROUP BY deptno HAVING sum(sal)>7000 ORDER BY allsum desc LIMIT 2;

10，多表查询/关联查询

内连接

• 多表等值连接的结果是多表的交集部分，N表连接，至少需要N-1个连接条件，没有顺序要求，一般起别名
• 非等值连接，只要不是等号连接的都是非等值连接

外连接，有主表有从表，主表肯定会显示完整的内容

• 左外连接，以左表为主
• 右外连接，以右表为主

-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称
-- 笛卡尔积 （a, b） (1,2,3) --(a,1) (a,2) (a,3) (b,1) (b,2) (b,3)--》会生成一个中间表
-- 多表查询，关联条件使用的是等号
-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称  等值连接
SELECT empno,ename,sal,emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno;
-- 给表起别名
SELECT empno,ename,sal,a.deptno,dname FROM emp a,dept b WHERE a.deptno=b.deptno;
-- 非等值连接
-- 查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别
SELECT empno,ename,sal,GRADE FROM emp e,salgrade sa WHERE e.sal BETWEEN sa.LowSAL AND sa.HISAL; 

-- 外连接
-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称 使用内连接和等值连接等同
SELECT empno,ename,sal,emp.deptno,dname FROM emp INNER JOIN dept on emp.deptno=dept.deptno;
-- 左外连接（左连接） 左边的表内容全部显示，右边的表没有的以null进行填充
SELECT empno,ename,sal,emp.deptno,dname FROM emp LEFT JOIN dept on emp.deptno=dept.deptno;
-- 右外连接，右表内容全部显示，左表没有的以null进行填充
SELECT empno,ename,sal,emp.deptno,dname FROM emp RIGHT JOIN dept on emp.deptno=dept.deptno;

ON后面的条件（ON条件）和WHERE条件的区别：

​ ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。

​ WHERE条件：在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连 接中是限制连接形成最终中间表的返回结果的约束。
建议：
​ ON只进行连接操作，WHERE只过滤中间表的记录

11，自连接，通过别名，将同一张表视为多张表；

什么情况下使用自连接；同一张表中某个字段要去关联另外一个字段

-- 查询员工姓名和员工的老板的名称
SELECT e1.empno,e1.ename,e2.empno,e2.ename  FROM emp e1, emp e2 WHERE e1.mgr=e2.empno;

12，子查询

-- 查询工资为20号部门平均工资的员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp GROUP BY deptno HAVING deptno=20);