MySQL的学习记录

mysql的常见命令

1.查看当前所有的的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables；
4.查看其他库的所有表
show tables from 库名；
5.创建表
create table 表名（

      列名 列类型，
      列名 列类型，
      。。。

）；
6.查看表结构
desc 表名；
7.查看服务器的版本
方式一: 登录到MySQL的服务端
select version；
方式二:没有登录到mysql的服务端
mysql – version；
或
mysql --V；

MySQL的语法规范

1.不区分大小写，但建议关键字大写，表名列名小写
2.每条命令最好用分号结尾
3.每条命令可以根据需要，可以进行缩进和换行
4.注释
单行注释:#注释文字
单行注释:-- 注释文字
多行注释:/* 注释文字 */

查询

进阶一:基础查询

语法

select 查询列表 from 表名；

类似于：System.out.printf(打印东西);

特点：
1.查询列表可以是:表中的字段，常量值，表达式。，函数。
2.查询的结果是一个虚拟性的表格

#补充
查询打开库时，在上方先写上:USE 库名；

起别名

优点
1.便于理解
2.如果要查询的字段有重名的情况，使用别名可以区分开来。
例：
方式一： 使用AS
SELECT 100/50 AS 结果；
SELECT last_name AS 姓，first-name AS 名 FROM emloyees；

方式二:使用空格
SELECT last_name 姓，first-name 名 FROM emloyees；

有的别名是含有特殊符号的情况下，加上’ '区别，避免歧义

去重

案例:查询员工表中涉及的所有的部门编号

SELECT DISTINCT department_id FROM employees;

+的作用

mysql中的+的作用仅仅只有一个功能那就是运算符。

案例：查询员工的名和姓连接成一个字段，并显示为 姓名
SELECT
CONCat（last_name，first_name） AS 姓名
FROM
employees；

进阶二 条件基础查询

语法：
select
查询列表
from
表名
where
筛选条件；

分类:
一：按照条件表达式筛选
简单运算符:
> < = != <> >= <=

          二：按照逻辑表达式筛选
           逻辑运算符：作用：用于连接条件表达式
              &&    ||      !
              and  or     not
              
           三：模糊查询
                  like
                  betwween and
                  in
                  is null

1.按照条件表达式筛选
案例一：查询工资大于12000的员工信息
SELECT
*
FROM
employees
WHERE
salary > 120000；

2.按照逻辑表达式筛选
案例一：查询工资在10000到20000之间的员工名，工资，及奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary>=10000 AND salary<=20000;

3.模糊查询
like
in
is null
between and

补充：安全等于 <=>

注意:字符型和日期型常量值必须用单引号引起来

排序查询

语法:
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】

案例一; 查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC；

   特点
   		1.asc表示升序，desc表示降序
   		如果不写默认的是升序
   		2.order by 子句中可以支持单个字段，多个字段，表达式，函数，别名
   		3.order by 子句一般放在查询语句的最后面，limit子句除外

进阶四

常见函数的学习

概念：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处： 1.隐藏了实现的细节 2 .提高了代码的重用性
调用：select 函数名（实参列表）【from 表】；
特点：
1.叫什么(函数名)
2.干什么(函数功能)
分类
1.单行函数
如 concat ifnull length等
2.分组函数

	功能：做统计使用，又称为统计函数，聚合函数，组函数。

字符函数

length

作用：获取参数值的字节个数

SHOW VARIABLES LIKE ‘%cahr%’ 查看字符集

concat

作用：拼接字符串
SELECT CONCAT（last_name+’_’+first_name）姓名 FROM employees;

upper,lower

作用：大小写转换

SELECT UPPER(‘jhon’);
SELECT LOWER(“jhon”);

substr，substring

注意：索引重1开始

1.截取从索引以后的字符
SELECT SUBTER（‘我就是最棒的’，4）out put；

2.截取从指定索引处指定长度的字符
SELECT SUBSTR（‘我是最棒的那是不可否认的，2，5’）out put；

inster

作用:返回子串第一次出现的索引，如果找不到就返回0；

trim

SELECT TRIM（’a’ FROM ‘aaaaaaaaaa大笨aaaaaaaaa蛋aaaa’）；

只会去掉前后两端的a、

lpad，rpad

做用：用指定的字符实现左/右填充指定长度

replace 替换

SELECT REPLACE（‘张无忌爱上了周芷若’，‘周芷若’，‘赵敏’）；

数学函数

round 四舍五入

ceil 向上取整，返回>=该参数的最小整数

floor 向下取整，返回<=该参数的最大整数

truncate 截断

mod 取余

日期函数

now 返回当前系统日期+时间

SELECT NOW();

curdate 返回当前系统时间，不包含时间

SELECT CURDATE（）；

curtime 返回当前时间，不包含日期

SELECT CURTIME （）；

str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE（‘1998-3-18’，‘%Y-%C-%D’）；

date_format 将日期转换成字符

其他函数

流程控制函数

if函数 if else 效果

case函数
使用一：
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
…
else 要显示的值n或者语句n
end
使用二：
case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
。。。
else 要显示的值n或者语句n
end

分组函数

功能:用于统计功能‘

分类:
sum 求和， avg 平均值 ， max 最大值， min最小值， count计算个数

SELECT SUM（单行字段）FROM 表；

分组函数的使用特点

参数支持那种类型

1.sum，avg一般用于处理数值型
max，min，count可以处理任意的类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算

4.count函数的详细介绍:
注：一般使用COUNT(*)统计行数

         SELECT COUNT(*) FROM employees;  通常用*来统计行数
         SELECT COUNT（1）FROM employees；
         效率:
         MYISAM存储引擎下，count（*）的效率高
         INNODB存储引擎下，count（*）与count（1）的效率差不多，比count（字段）要高一些

5.和分组函数一同查询的字段要求是group by后面的字段

进阶五:分组查询

分组数据： GROUP BY 字句语法
可以使用GROUP BY 子句将表中的数据分成若干组

    SELECT     column，group_function(column)
    FROM        table
    [WHERE    condition]
    [GROUP  BY       group_by_xpression]
    [ORDER  BY       column];
    语法:
        select  分组函数，列（要求出现在group by 的后面）
        from 表
        【where 筛选条件】
        group up 分组的列表
        【order by 子句】
    注意;
          查询列表必须特殊，要求是分组函数和group by 后面出现的字段
 
 特点：
        1.分组查询中的筛选条件分为两类
        分组前筛选   数据源为原始表   位置 group by 子句的前面 关键字是where
        分组后筛选   数据源为分组后的结果集  位置 group by 子句的后面  关键字是having

  1. 分组函数做条件肯定放在having子句中
  2. 能用分组前筛选的，就优先使用分组前筛选
  3. group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或者函数（用的较少）
  4. 也可以添加排序，（排序放在分组查询的最后）

明确:WHERE一定放在FROM后面

简单的分组查询

案例一:查询每个工种的最高工资
SELECT MAX（salary），job_id
FROM employees
GROUP BY job_id;

案例二:查询每个位置上的部门个数
SELECT COUNT（*），location_id
FROM departments
GROUP BY location_id;

添加分组后查询

案例一：查询那个部门的员工个数大于2
1.查询每个部门的员工个数
SELECT COUNT（），department_id
FROM employees
GROUP BY department_id
2.根据1的结果进行筛选，查询那个部门的员工个数>2
HAVING COUNT()；

进阶六:连接查询

含义:又称多表查询，当查询的字段来自多个表时，就会用到连接查询
笛卡尔乘积现象：表一有m行 表二有n行，结果m*n行

发生原因:没有有效的连接条件
如何避免:添加有效的连接条件

分类:
       按年代分类：
       sql92标准：仅仅支持内连接
       sql99标准【推荐】：支持内连接+外连接（左，右外连接）+交叉连接
  
       按功能分类：
       内连接：
                 等值连接
                 非等值连接
                 自连接
       外连接：
        	 左外连接
        	 右外连接
        	 全外连接
       交叉连接

sql92标准

1.等值连接：
案例一：查询员工名和对应的部门名
SELECT last_name,department_id
FROM employees,departments
WHERE employees.‘department_id’=department.‘department_id’;

注：可为表起别名，
1.提高代码的简洁度
2.区分多个重名的字段
注;如果为表起了别名，则查询的字段就不能使用原来的表名去限定
3.自连接
案例一:查询员工名和上级的名称
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’;

sql99语法标准

 语法：
         select 查询列表
         from 表1 别名 【连接类型】
         join   表2 别名 
         on 连接条件
       【where 筛选条件】
       【group by 分组】
       【having 筛选条件】
       【order by 排序列表】

分类：
内连接(❤)：inner
外连接
左外（❤）：left 【outer】
右外（❤）：right 【outer】
全外：full 【outer】
交叉连接： cross

内连接

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；

分类：
等值
非等值
自连接

1.等值连接
案例一：查询员工名部门名
SELECT last_name,department_name
FROM emplyees e
INNER JOIN departments d
ON e.‘department_name’=d.‘department_name’;

案例二： 查询部门个数>3的城市名和部门个数（添加分组+筛选）

SELECT city,COUNT()
FROM department d
INNER JOIN location l
ON d.‘location_id’=l."locatiom_id’
GROUP BY city
HAVING count()>3;
在这里插入图片描述

非等值连接

自连接

外连接

应用场景：查询一个表中有，另一个表中没有的记录

特点：

		1.外连接的查询结果为主表中的所有记录
		如果从表中有和他它匹配的，则显示匹配的值
		如果从表中没有和他匹配的，则显示为null
		外连接的查询结果=内连接结果+主表中有而从表中没有的记录
		
		2. 左外连接，left join 左边的是主表
		3. 右外连接，right on 右边的是主表
		4. 左外和右外交换两个表的顺序，可以实现相同的效果