Web day1 MySQL查询

mysql卸载

编码配置

打开服务
services.msc

以管理员身份 打开关闭服务

SQL语言

数据库命令

数据查询

基本查询

# 查询所有的员工信息
SELECT * FROM t_employees;
# 查询部分列
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees;
# 算数运算符 + - * / % (%既可以用于模运算，又可作为占位符)

SELECT 10+5;
# 结果是小数
SELECT 10/4;
# 查询年薪
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY*12 FROM t_employees;

字符串拼接

# 字符串拼接concat
SELECT CONCAT(employee_id,'的名字是',first_name) FROM t_employees;

缺点：distinct只能返回它的目标字段，而无法返回其它字段，若想要返回多列字段，就要对多列同时distinct，但针对多列列去重时，要每列都相同才算作重复

# 使用别名查询
SELECT EMPLOYEE_ID AS '编号',FIRST_NAME  AS '名字' FROM t_employees;
# 去重查询 dintinct 多条件时全部相等才算作重复
SELECT DISTINCT SALARY,COMMISSION_PCT FROM t_employees;
SELECT DISTINCT SALARY,FIRST_NAME EMPLOYEE_ID FROM t_employees;

多列排序时条件越前优先级越高，先依据前边的决定，相同时才比较后边的

# 排序查询  默认升序 asc  降序desc
SELECT EMPLOYEE_ID,SALARY FROM t_employees ORDER BY SALARY DESC;
# 多列排序时条件越前优先级越高，先依据前边的决定，相同时才比较后边的
SELECT EMPLOYEE_ID,SALARY FROM t_employees ORDER BY EMPLOYEE_ID DESC ,SALARY ASC;

# 条件查询 逻辑判断
SELECT FIRST_NAME,SALARY FROM t_employees WHERE FIRST_NAME='Luis';
SELECT FIRST_NAME,SALARY FROM t_employees WHERE SALARY<10000 AND SALARY>8888;
# 确保小值在前，大值在后，不推荐使用这个
SELECT FIRST_NAME,SALARY FROM t_employees WHERE SALARY BETWEEN 6000 AND 10000;

# NULL值判断
SELECT FIRST_NAME,COMMISSION_PCT FROM t_employees WHERE COMMISSION_PCT IS NULL;
# 枚举查询
SELECT FIRST_NAME,DEPARTMENT_ID FROM t_employees WHERE DEPARTMENT_ID IN (70,80);

# 模糊查询
SELECT FIRST_NAME,SALARY FROM t_employees WHERE FIRST_NAME LIKE 'L%';
SELECT FIRST_NAME,SALARY FROM t_employees WHERE FIRST_NAME LIKE 'L___';

时间查询

# 当前时间
SELECT SYSDATE();
SELECT NOW();
# 当前日期
SELECT CURDATE();
# 当前时间
SELECT CURRENT_TIME;
# 获取周
SELECT WEEK(NOW());
SELECT WEEK('2020-08-19');
# 获取年
SELECT YEAR(NOW());
# 获取两个时间相差的天数
SELECT DATEDIFF('2020-08-19','2020-07-19');
# 在指定日期增加或减少指定的天数
SELECT ADDDATE(now(),-30);

字符串查询

对数据操作，会根据数据形成一列多行的虚拟表

# 字符串函数
# 合并 concat
SELECT CONCAT(FIRST_NAME,SALARY) as '一览' FROM t_employees;
SELECT CONCAT('wo','ai');
# 替换
SELECT INSERT('我爱java',3,4,'php');
# 截取
SELECT SUBSTRING('我爱java',3,4);

聚合函数

count函数，如果使用的某一列，不计算null列，可查询有效值数量

SELECT SUM(SALARY),max(SALARY),min(SALARY),AVG(SALARY) FROM t_employees;
# count函数，如果使用的某一列，不计算null列
SELECT COUNT(*) FROM t_employees;
SELECT COUNT(SALARY) FROM t_employees;
SELECT COUNT(COMMISSION_PCT) FROM t_employees;
# count 函数，可以使用数字 开辟一列都为1的，对其进行统计
SELECT COUNT(1) FROM t_employees;

SELECT max(degree),sno,cno FROM score;

分组查询

若显示其他列，只能显示该组之中的第一个对象，聚合函数用于对分出每组的信息进行统计，想获得该组对象，可以使用WHERE

限定查询

# GROUP BY 
SELECT DEPARTMENT_ID,AVG(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID HAVING AVG(SALARY)>10000;
SELECT AVG(degree) FROM score WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(*)>=5;
# LIMIT
SELECT FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC LIMIT 0,5;

基础查询总结

子查询

查询中的嵌套查询，先执行子查询，在执行父查询（有时相反）

1.一列一行

2.多行单列

3.多行单列

4.临时表

子查询作为临时表，必须要给一个临时表名，如作为FROM的对象时

# 子查询
# 工资大于Bruce 一行一列
SELECT FIRST_NAME,SALARY FROM t_employees
WHERE SALARY>(SELECT SALARY FROM t_employees WHERE FIRST_NAME='Bruce');
# 查询高于60部门所有人的工资
SELECT FIRST_NAME,SALARY FROM t_employees
WHERE SALARY>ALL(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID=60 );
# 与姓为 King 同一部门的员工
SELECT DEPARTMENT_ID,FIRST_NAME FROM t_employees
WHERE DEPARTMENT_ID in (SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME='King');
# 获得工资排名前五的员工信息
SELECT FIRST_NAME,SALARY FROM 
(SELECT FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC)
as temp LIMIT 0,5;

合并查询

可选择相同的列数使得列数相同，联合表的列名为第一个表的列名，合并时按照参数对应位置进行合并
每列都相同才被视为相同

SELECT DEPARTMENT_ID,LOCATION_ID FROM t_departments UNION 
SELECT DEPARTMENT_ID,LOCATION_ID FROM t_departments;

连接查询

交叉连接

# 交叉连接（笛卡尔积） 结果为两者成绩，表1的一条记录与表二的记录相乘
# 传统写法  
SELECT * FROM t_employees;# 107
SELECT * FROM t_departments;#27
SELECT * FROM t_employees,t_departments;#2889
# 标准写法
SELECT * FROM t_employees CROSS JOIN t_departments;

内连接（表的关系评级，谁前谁后对数据没有影响）

会删除连接条件为空值的行

# 内连接
# 传统写法
SELECT * FROM t_employees AS E,t_departments AS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;
# 标准写法
SELECT * FROM t_employees AS E INNER JOIN t_departments AS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;

当连接条件列名相同时，可使用USING去重复列

SELECT * FROM student  LEFT JOIN score  USING(sno);

外连接
查询出左边所有数据，右边进行匹配，如果右边的没有匹配的填充null

右边补上null

查询出右边所有数据，左边进行匹配，如果左边的没有匹配的填充null

左边补上null

# 外连接
# 左外连接
SELECT * FROM t_employees AS E LEFT JOIN t_departments AS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;
# 右外连接
SELECT * FROM t_employees AS E RIGHT JOIN t_departments AS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;

注意

表连接问题

当使用连接查询时，虚拟表中会出现两个相同的列名，，若此时查询该列，将会报错 1052 - Column ‘sno’ in field list is ambiguous 此字段是模糊的，虽然两个表连接时仍可使用 “*” 来查询，但当多个表连接时若再使用 "*"来查询将会报错

两表连接 sno为重复列

当连接条件列名相同时，可使用USING去重复列
当重复列不为连接条件时，可使用 表名.列名（t2.age） 的方式访问，明确访问某个表的字段，不会出现重复列问题

SELECT * FROM student  LEFT JOIN score  USING(sno);

别名问题

列的别名 ：主要用于将查询到的虚拟表中的的列名换为新列名
原列名 as 新列名

表的别名：在一条sql语句中，一旦为表确定了别名，就不能再使用原表名，推测是因为from语句最先执行，虚拟表名为新表明

select id,user.students,t2.age,weight from user  left join user_ext as t2 using(id) where age >9

user_ext已有新列t2，不能在使用原表名查询其内容类

去重问题

distinct去重
缺点：distinct只能返回它的目标字段，而无法返回其它字段，若想要返回多列字段，就要对多列同时，但针对多列列去重时，要每列都相同才算作重复

SELECT DISTINCT LOCATION_ID FROM t_departments;

group去重，获得不重复的行与每组的个数

SELECT count(1) FROM t_departments GROUP BY LOCATION_ID;

获得某列不重复值个数

SELECT count(DISTINCT LOCATION_ID) FROM t_departments;
子查询
SELECT count(1) FROM (SELECT 1 FROM t_departments GROUP BY LOCATION_ID) as s;

实现差别：
distinct需要将某列中的全部内容都存储在一个内存中，可以理解为一个hash结构，key为该列的值，最后计算hash结构中有多少个key即可得到结果。
group by的方式是先将col排序，再计数，时间复杂度较高

当数据离散时，选用group by 发挥空间复杂度低优势
当梳理集中时，选用distinct 发挥时间复杂度优势

两个极端：
1.数据列的所有数据都一样，即去重计数的结果为1时，用distinct最佳
2.如果数据列唯一，没有相同数值，用group 最好