文章目录
SQL语法
1. 排序 (order by)
2. 聚合函数
语句执行顺序
3. 分组 (group by)(条件只能用having)
语句执行顺序
4. 分页显示(limit  [***])
多表关系
多表查询
1.交叉连接查询
2. 内连接(inner join  条件用on)
3. 外连接（outer join）
4. 子查询

SQL语法
1. 排序 (order by)
SELECT * FROM 表名 ORDER BY 排序字段 ASC(升序)|DESC(降序);


数据库的查询(排序：order by)  默认是按升序排列的，但是升序ASC最好写上，代码可读性！
distinct去掉重复 select distinct * from 表名 order by column1,column2,column3 desc;（去重）
如果排序列的值相同，通过添加条件，可以再加一个来进行排序


select * from 表名 order by column1,column2,column3 desc;

按照降序查找，首先按照column1列名排序，如果column1的值相同，再按照column2排序，类推

特点：


指定列(一个条件一个列，多个条件多个列，排在前面的优先级高)


指定升序或者降序


order by 排序只对数字和英文字符串有效,排序对汉字无效，符合字典顺序(字母、数字顺序)


  排序对汉字无效的解决办法 - > 通过汉字生成拼音，然后按照拼音(字典)顺序




2. 聚合函数
聚合函数(类似于Java中的方法: 函数名())

五个常用的聚合函数




聚合函数名
描述




count
统计指定列不为NULL的记录行数(如果有NULL值,则不统计)


sum
计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0


max
计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算


min
计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算


avg
计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0


语句执行顺序

SELECT COUNT(*) FROM product WHERE price > 200;

执行顺序：from -> where -> 聚合函数





3. 分组 (group by)(条件只能用having)
SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件; 


将数据按照某个规则或者某个特征分成N组（一般不对id进行分组，因为id唯一，分组就只有一个或零个，没有意义）
只起到分组的作用，没有统计作用(一般配合聚合函数count来统计结果)
如果是对分组之后的结果再进行过滤,则必须使用having 不能使用where

语句执行顺序

from -> group by -> 聚合函数(count) -> having -> select





4. 分页显示(limit  [***])
SELECT 字段1，字段2... FROM 表名 LIMIT M,N 


SELECT 字段1，字段2… FROM 表名 LIMIT M,N

M：表示从哪一行的索引(从0开始)开始显示

N：表示要显示几行

第1页： M=N*(1-1)  第2页： M=N*(2-1)

第page页： M=N*(page-1) 开始(page一般会显示在url地址上，方便读取)

数据库分页也是在数据库里写查询语句，不同的是查询的都是指定条数到指定条数的数据，不是一次性把数据全查出来。






多表关系
  在实际的开发中,项目一定是有多张表组成的,这些表之间有着对应关系。

  表与表之间的关联关系主要是：


一对一关联 (A表的一行对应B表的一行，反之也成立，此时，两张表可以合并成一张表)
一对多关联 (A表的一行对应B表的多行，反之不成立)

一对多关联，A表的一行对应B表的多行，在B表中要有外键对应A表的主键
多对多关联 (A表的一行对应B表的多行,B的一行对应A表的多行)

多对多关联，要创建中间表，分别对应A、B两表的主键作为外键。






多表查询
1.交叉连接查询
  交叉连接查询是将两张表相乘，就是就笛卡尔集。(A和B —> A * B)

  多表查询可以理解成在笛卡尔集的基础上进行条件筛选


2. 内连接(inner join  条件用on)
  求的是多张表的交集 - 本质就是在笛卡尔集上加条件
两种实现方法：


隐式内连接：select * from A,B where 条件;


显式内连接：select * from A inner join B on 条件;（效率更高） (因为用了inner join关键字，能够直接识别是内连接查询，所以叫显式内连接)


两者区别：

隐式内连接在笛卡尔集的基础上筛选（需要先求出笛卡尔集）

显式内连接是直接在原始表上筛选，效率更高。



3. 外连接（outer join）
select * from tableA left outer join tableB tableA.id = tableB.id_fk;

  外连接就是两个表中选一个表将数据全部输出，另一个表没有对应数据则输出NULL
有两种外连接方式：

左外连接以左表为主,左表中的数据全部输出,右表中如果没有同等的数据则补NULL（left outer join）
右外连接以右表为主,右表中的数据全部输出,左表中如果没有同等的数据则补NULL(right outer join)





4. 子查询
子查询就是select语句的嵌套：一个select的查询结果作为另一个select查询语法的一部分
select *from User where cid = (select cid from category where cname='电子')

外键
可以明确的声明表和表之间的字段的关系，使数据库帮我们维护这种关系，这就是外键。如果一个操作破坏这种外键约束，则数据库会阻止这个操作。
为表添加外键
创建表的时候声明外键
FOREIGN KEY(ordersid) REFERENCES ORDERS(id);

--例如：为职员表添加外键关联部门表
create table dep(
    id int primary key auto_increment,
    name varchar(40)
);

create table emp(
    id int primary key auto_increment,
    name varchar(40),
    dept_id int,
    foreign key(dept_id) references dep(id)
);

添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT FK_ID FOREIGN KEY(字段名) REFERENCES 外表表名(主键字段名);

--FK_ID指该外键的名称，
--例：为员工表(emp)添加外键为部门表(dept)的id
alter table emp add constraint aaa_fk foreign key(dep_id) references dept(id);

删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

--例一：删除员工表的外键
alter table emp drop foreign key aaa_fk;

关联表
关联关系

多对一：例：员工与部门的关系，将外键建立在多的一方
多对多：例：学生与课程之间的关系，需要定义一张中间表，该表存在两个外键分别连接两张表，这两个外键也是表的联合主键
一对一：例：人和省份证的关系，在任一张表中建立外键即可

添加数据

多对一：先添加少的一方，再添加多的一方
多对多：先添加两个表，在添加关系表
一对一：先添加有外键的表，在添加无外键的

删除数据

多对一：先删除多，再删一
多对多：先删关系，再删实体
一对一：先删无外键的表的数据，再删有外键的

连接查询（多表查询）
跨越多张表查询数据
笛卡尔积查询：是两张表相乘的结果
--假设有员工表(emp)的dept_id和部门表(dept)的id对应
--查询两张表
SELECT * FROM dept,emp;

内连接查询：查询出左边表也有且右边表也有的记录
SELECT * FROM dept,emp WHERE dept.id=emp.dept_id;
--或者
SELECT * FROM dept INNER JOIN emp ON dept.id=emp.dept_id;

外连接查询
左外连接查询：在内连接的基础上增加上左边表有而右边表没有的记录
SELECT * FROM dept LEFT JOIN emp ON dept.id=emp.dept_id;

右外连接查询
SELECT * FROM dept RIGHT JOIN emp ON dept.id=emp.dept.id;

全外连接查询：MySQL不支持全外连接
SELECT * FROM dept FULL JOIN ON dept.id=emp.dept_id;

--合并查询结果（union）
SELECT * FROM dept LEFT JOIN emp ON dept.id=emp.dept_id;
UNION
SELECT * FROM dept RIGHT JOIN emp ON dept.id=emp.dept.id;

--例一：查询4号部门的名称和其中员工的姓名
select dept.name 部门名称,emp.name 员工名称 from dept inner join emp on dept.id=emp.dept_id where dept.id=4;

子查询

指一个查询语句嵌套在另一个查询语句内部的查询。
带IN关键字的子查询：内层查询语句仅仅返回一个数据列，这个数据列中的值供外层查询语句进行比较操作

--例一：查询存在年龄为20岁的员工的部门
SELECT * FROM dept WHERE emp_id IN(SELECT emp_id FROM emp WHERE age=20);
--例二：使用NOT IN查询没有20岁的员工的部门
SELECT * FROM dept WHERE emp_id NOT IN(SELECT emp_id FROM emp WHERE age=20);


带EXISTS关键字的子查询：该关键字后面参数可以是任意子查询，相当于测试，返回TRUE或者FALSE，若为TRUE则执行外查询。

--例一：查询emp表中是否存在大于21岁的员工，存在则查询dept表中所有的记录
SELECT * FROM dept WHERE EXISTS (SELECT * FROM emp WHERE age>21);


带ANY关键字的子查询：只要满足子查询中的任意一个就返回结果

--例一：查询部门表中id大于员工表中任意一的id的结果
select * from dept where id>ANY(select dept_id from emp);


带ALL关键字的子查询：需要同时满足所有子查询结果。

--例一：查询部门表中id大于员工表中所有id的结果
select * from dept where id>ALL(select id from emp);


带比较运算符的子查询，即：>，<，=，<=，>=，=，!=。

--例一：查询员工赵四所在的部门
select name from dept where id=(select dept_id from emp where name='赵四');

MySQL入门 DQL语言之六：sql92语法值连接查询（多表查询）

#6：连接查询

/*

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔集乘积现象：表1 有n行，表2有m行，结果等于n*m行

发生原因：

    ①没有有效的连接条件

    ②省略连接条件

    ③所有表中的所有行相互连接

如何避免：添加有效的连接条件

分类：

    按年代分类：

    sql92标准：仅仅支持内连接

    sql99标准【推荐】：支持内连接+外链接（左外和右外）+交叉链接

    

    按功能分类：

        内连接：

            等值连接

            非等值连接

            自连接

        外链接：

            左外连接

            右外链接

            全连接

        交叉链接

*/
SELECT * FROM beauty;
SELECT * FROM boys;
#这样会产生笛卡尔集，即是两个表的总行数相乘
SELECT NAME,boyName FROM boys,beauty;
#添加有效连接条件
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` =  boys.`id`;

#一：sql92标准

#1、等值连接

/*

①多表等值连接的结果为多表的交集部分

②N表连接，至少需要n-1个连接条件

③多表连接的顺序没要求

④一般需要为表起别名

⑤可以搭配前面介绍的所有子句的使用（排序、分组等）

*/
#案1：查询女神名对应的男神名
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` =  boys.`id`;

#案例2：查询部门名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#2、为表起别名

/*

①提高语句的简洁度

②区分多个重名的字段

注意：如果为表起了别名，则查询字段就不能用原来的表名去限定

*/

#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs j
WHERE e.`job_id` = j.`job_id`;

#3、两个表顺序是否可以调换

#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM jobs j,employees AS e
WHERE j.`job_id` = e.`job_id` ; 

#4、可以加筛选

#案例：查询有奖金的员工名、和部门名
SELECT last_name ,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id` 
AND e.`commission_pct` IS NOT NULL;

#案例2：查询城市民中第二个字符为0的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE l.`location_id` = d.`location_id`
AND city LIKE '_o%'

#5、可以加分组

#案例1:查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

#案例2：查询出有奖金的每个部门的部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND commission_pct IS NOT NULL 
GROUP BY department_name,d.`manager_id`;
#6、可以加排序

#案例：每个工种的工种名和员工个数并且按员工个数降序
SELECT j.`job_title`,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id` = e.`job_id`
GROUP BY j.`job_title`
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接

#案例:查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`;

#二、非等值连接

#案例1：查询出员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
AND grade_level = 'A';

#二、自连接

#案例1：查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id` = m.`employee_id`;

-- 给表里面的某一列添加注释
comment on column emp.job is '工作';

--  DML 中的Select
-- 如果想查询表中的某一列  
select ename from emp;
-- 查询表中的多列
select empno,ename,job from  emp;
-- 将表中所有的列都查出来
select * from emp;

--给列取别名
select job "工 作" ,ename "姓名" from emp;

-- 给表取别名
select e.* from emp e;

-- 去除重复数据
--distinct 后面跟的是判断是否为重复数据的列 如果全部列重复才为重复数据 则distinct 后面加*
select distinct * from emp;

-- where 条件筛选数据
-- oracle 中判断数据是否为null 使用 is null 或者is not null
select  * from  emp where comm is null; 

-- in (list)
select ename,sal from emp where sal in (800,1250,3000,2500);
-- not in (list)
select ename,sal from emp where sal not in (800,1250,3000,2500);
-- between  and   查询一个范围内的数据
select * from emp where sal between 1500 and 3000;
-- <>  不等于
select * from emp where sal <>3000;

-- not and or

select * from emp where sal > 3000  and deptno = 10;

-- 列出来 部门为20 或者30 并且薪水大于3000 的所有员工
select * from emp where (deptno = 10 or deptno = 100)  and sal >1000;