数据查询

1. 单表查询

1.1 语法

select distinct 字段1,字段2,字段3,... from 库名.表名
                    where 约束条件
                    group by 分组依据
                    having 过滤条件
                    order by 排序的字段
                    limit 限制显示的条数

1.2 执行优先级

from
where
group by
having
distinct
order by
limit
步骤：1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

1.3 简单查询

(1)
SELECT * FROM employee;
SELECT name,salary FROM employee;
(2)避免重复distinct
SELECT DISTINCT post FROM employee; 
#指定一个字段，显示去重后的结果
(3)通过四则运算查询
    SELECT name, salary*12 FROM employee;
    SELECT name, salary*12 AS Annual_salary FROM employee;
    SELECT name, salary*12 Annual_salary FROM employee;
(4)定义显示的格式concat 
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
   FROM employee;
   
(5) 查询字段中的字符
# 查询article_management表中content列中1至5的字符
select mid(content,1,5) from article_management;

1.4 where 约束

1. 比较运算符：> < >= <= <> !=

2. between 1 and 100 匹配1至100之间
SELECT name FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;
        
3. in(80,90,100) 值是80或90或100 #可加not 匹配除括号内以外的情况
SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

4. like 'eg%'(以eg开头);  like'%eg%(包含eg)',可在like前加not进行取反
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符 
SELECT * FROM employee 
            WHERE name LIKE 'eg%';
SELECT * FROM employee 
            WHERE name LIKE 'al__';
参考地址：https://www.runoob.com/sql/sql-wildcards.html   
            
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');

6.空值判断  is null
Select * from emp where comm is null;

1.5 group by 分组查询

• 作用：当我们要以组为单位进行统计时就必须分组，分组的目的是为了以组为单位进行统计的，
  再去考虑单条记录毫无意义；按照所有记录相同的部分进行归类，一定区分度低的字段；
• 单独使用group by 关键字分组

SELECT post FROM employee GROUP BY post;
    注意：按照post字段分组，那么select查询的字段只能是post(每个部门的第一条数据)，想要获取组内的其他相关信息，需要借助函数

• 聚合函数

max
min
avg
sum
count
select post,count(id) from employee group by post;
select post,max(salary) from employee group by post;
select post,avg(salary) from employee group by post;
select sex,count(sex) from employee group by sex;
-----------------------------------------
group_concat 定义输出格式
1、select post,group_concat(name,':',age) from emp group by post;
2、SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名

例：
#统计出每个部门年龄30以上的员工的平均薪资
select post,avg(salary) from emp where age >= 30 group by post;

• 严格模式开启与关闭

注：打开严格模式
1.可以通过执行SQL语句来开启，但是只对当前连接有效，下面是SQL语句：
set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
2.通过修改MySQL的配置文件，在配置文件中查找sql-mode，将此行修改成为：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

严格模式应用场景：
不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值

取消严格模式：sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

1.6 having 过滤条件

1、where是在分组之前的过滤，即在分组之前做了一次整体性的筛选
2、having是在分组之后的过滤，即在分组之后专门针对聚合的结果进行进一步的筛选
例：
#查看平均薪资大于1万的部门和薪资
select post,avg(salary) from employee group by post having avg(salary) > 10000;

1.7 order by 查询排序

1、select * from emp order by age asc;   # 默认asc升序-》从小到大

2、select * from emp order by age desc;   # desc降序-》从大到小

3、select * from emp order by age asc,salary desc; # 先按照age升序排列，如果age相同则按照salary降序排
例：
select post,avg(salary) from emp group by post order by avg(salary);

1.8 limit 限制查询的记录数

例：
SELECT * FROM employee ORDER BY salary DESC LIMIT 3;           #默认初始位置为0 
    
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

1.9 正则表达式查询

SELECT * FROM employee WHERE name REGEXP '^ale';
SELECT * FROM employee WHERE name REGEXP 'on$';

#匹配m出现两次的员工姓名
SELECT * FROM employee WHERE name REGEXP 'm{2}';

#查看所有员工中名字是jin开头，n或者g结尾的员工信息
select * from employee where name regexp '^jin.*[ng]$;'

2. 多表连接查询

• 语法

select 字段 from 表一 inner|left|right 表二 on 表一.字段=表二.字段
注：可用as 将要查出的字段重命名

• 交叉连接：不适用任何匹配条件
  select * from employee,department;
• 内连接：只连接匹配的行,找出表中共用的数据
  select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
• 左连接，优先显示左表的全部记录，即在内连接的基础上增加左表有，右表没有的数据

select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

• 右连接，增加右表有左表没有的数据

select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

• 全外连接：显示左右两个表全部记录

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
注：union会去掉相同的记录

• 复制一张表中的数据到另外一张表

# 将apps表中id为1的app_name,country字段的值,复制到websites表中
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;