数据库-Mysql

2023-08-06 22:13:22

Mysql

基本语法

# 查看数据库
show database；
# 选择test库
use test；
# 查看当前库的所有表
show tables;
# 查看其他库的所有表
show tables form 库名;
# 创建表
create table 表名(

	列名 列类型,
	列名 列类型，
	。。。
);
# 查看表结构
desc 表名;

基础查询

语法：

# SELECT 查询列表 FORM 表名；
# 查询所有字段
SELECT * FORM user；
# 查询单个、多个字段
SELECT name,salary FROM user;
# 字段可以加反引号来区分是字段还是关键字
SELECT `select` FROM user;

起别名

# 方式一:使用AS
SELECT 10%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
# 方式二：使用空格
SELEC1 last_name 姓,first_name 名 FROM employees;
### 去重`DISTINCT`
```sql
# 案例 查询字段中不重复的值
SELEC1 DISTINCT department_id FROM employees;

+号 和 CONCAT()

没有拼接字符串的作用，只能是运算符（如果非数值型强转为数值型做加法运算）

# 不是将字符串拼接
SELECT
  last_name+first_name AS 姓名
FROM
  employees;
# CONCAT字符串拼接
SELECT
  CONCAT(last_name,first_name) AS 姓名
FROM
  employees;
# 其中一方为字符
SELECT '123'+2; # 125（转换成功）
SELECT 'john'+2; # 2（转换失败的按0计算）
SELECT NULL+2; # NULL（有一个为MULL则输出MULL）

IFNULL()判断是否为空

# 如果是NULL，转为0
SELECT IFNULL(NULL,0) # 0
# 如果不是是NULL，输出原值
SELECT IFNULL(300,0) # 300

条件查询

语法：

# SELECT 查询列表 FORM 表名 WHERE 筛选条件；

按条件表达式筛选

> < = != <> >= <=

#案例1：查询工资>12000的员工信息  大于>
SELECT * FROM employees WHERE salary>10000;
#案例2：查询部门编号不等于90号的员工名和部门名  不等于 <> !=
SELECT last_name,department_id FROM employees WHERE department_id<>90;
SELECT last_name,department_id FROM employees WHERE department_id!=90;

按逻辑表达式筛选

&& || ！
and or not

# 查询工资在10000到20000之间的员工名、工资以及奖金  && and 条件都为真时才执行
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 AND salary <= 20000;
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 && salary <= 20000;
# 查询部门编号不是在90到110之间，或者工资高于15000的员工信息 
# (不在什么区间)可以用 not( * and * ) 或 !( * and * )
SELECT
    *
FROM
    employees
WHERE
    NOT(department_id>=90 AND department_id<=110) OR salary>15000;
# !( * and * )
SELECT
    *
FROM
    employees
WHERE
    !(department_id>=90 AND department_id<=110) OR salary>15000;

模糊查询

  • like
    特点：
    一般和通配符使用：
    % 任意多个字符,包含0个
    _ 任意单个字符(精准匹配)

  • between and
    特点：
    提高语句简洁度
    包含两个临界值，两个临界值不要调换顺序

  • in()
    特点：
    提高语句简洁度
    in列表的值类型必须一致或兼容
    in列表中不支持通配符

  • is null和is not null
    特点：
    =或<>不能用于判断null值
    is null或is not null 可以判断null值

# like
#案例1：查询员工名包含字符a的员工信息
SELECT last_name FROM employees WHERE last_name LIKE "%a%";

#案例2：查询员工名中包含第三个字符为u，第五个字符为o的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '__u_o%';

#案例3：查询员工名中第二个字符为_的员工名 可以用转义 \ ,也可以用ESCAPE $把 $当成转义符号.
SELECT last_name FROM employees WHERE last_name LIKE "_$_%" ESCAPE "$";
SELECT last_name FROM employees WHERE last_name LIKE "_\_%";

# between and
#案例1：查询员工编号在100到120之间的员工信息
#第一种方法 >= and <=
SELECT
    *
FROM
    employees
WHERE
    employee_id>=100 AND employee_id<=120;


#第二种方法 between and
SELECT
    *
FROM
    employees
WHERE
    employee_id BETWEEN 100 AND 120; # 临界值不能调换

# in()
#案例：查询员工的工种编号是IT——PROG、AD_VP、AD_PRES中的一个员工和工种编号
SELECT
    last_name,
    job_id
FROM
    employees
WHERE
    job_id = "IT_PROG" OR job_id="AD_VP" OR job_id="AD_PRES";

SELECT 
    last_name,
    job_id
FROM
    employees
WHERE
    job_id IN("IT_PROG","AD_VP","AD_PRES");

# is null 和 is not null
#案例1：查询没有奖金的员工名和奖金率
SELECT
    last_name,salary,commission_pct
FROM
    employees
WHERE
    commission_pct IS NULL;

#案例2：查询有奖金的员工名和奖金率,奖金
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

安全等于<=>和is null

is null：仅仅可以判断NULL值，可读性高，推荐使用
<=>：既可以判断NULL值，又可以判断普通数值，可读性低

#案例3：查询没有奖金的员工名和奖金率
SELECT
    last_name,salary,commission_pct
FROM
    employees
WHERE
    commission_pct <=> NULL; # 相当于commission_pct is NULL

#案例4：查询工资为12000的员工信息
SELECT * FROM employees WHERE salary <=>12000;
#IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
#<=>    :既可以判断NULL值，又可以判断普通的数值，可读性较低

#案例5：查询员工为176的员工的姓名和部门名和年薪
SELECT
    last_name,employee_id,department_id,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
    employees
WHERE
    employee_id = 176;
    #employee_id <=>176

SELECT job_id FROM employees;
SELECT DISTINCT job_id FROM employees;

排序查询

SELECT 查询列表 FORM 表名
	[WHERE 筛选条件]
    ORDER BY 排序列表[asc|desc]；

特点：
asc代表的是升序，可以省略

​ order by子句可以支持 单个字段、别名、表达式、函数、多个字段

​ order by子句在查询语句的最后面，除了limit子句

  1. 对单个字段排序

    #这里不写默认是asc代表升序,后面添加desc代表降序
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary DESC;

  2. 筛选+排序

    #案例：查询部门编号>=90的员工信息，并按员工编号降序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;

  3. 按表达式排序

    #案例：查询员工信息  按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

  4. 按别名排序

    #案例：查询员工信息  按年薪排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;

  5. 按函数排序

    #案例：查询员工名，并且按名字的长度排序
SELECT LENGTH(last_name) AS 名字长度,last_name
FROM employees
ORDER BY 名字长度 DESC;

SELECT LENGTH(last_name) AS 名字长度,last_name
FROM employees
ORDER BY LENGTH(last_name) ASC;

  6. 按多个字段排序

    #案例： 查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：

1. 隐藏了实现细节
2. 提高代码的重用性

调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）

分类：

  1. 单行函数

    如 concat、length、ifnull等

  2. 分组函数

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

单行函数

字符函数

函数 描述
length 获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
concat 拼接字符串
substr|substring 截取字符串
instr 返回字符串第一次出现的索引，如果找不到返回0,默认索引是从1开始的
trim 去掉前后的空格或指定其他的字符（默认只能去掉空格，不能去掉制表符/t,也可以指定去掉的字符）
upper 小写变大写
lower 大写变小写
lpad 用于指定字符串实现左填充指定长度
rpad 用于指定字符串实现右填充长度
replace 替换

  • substr截取字符串

    # substr 索引从1开始
# 截取从指定索引出到末尾
SELECT SUBSTR('1234567890',6) out_put; # 结果：67890
# 从指定索引处截取指定长度（从索引2截取5个字符）
SELECT SUBSTR('1234567890',2,5) out_put; # 结果：23456

  • trim去掉前后的空格或指定其他的字符（默认只能去掉空格，不能去掉制表符/t,也可以指定去掉的字符）

    SELECT TRIM('    张翠   山    '); # 结果：张翠   山
SELECT TRIM('a' FROM 'aaaaa张aaaaa翠山aaaaaaaaaaaaaaaaaaa'); # 结果:张aaaaa翠山

  • lpad和rpad填充长度

    SELECT LPAD("殿速速", 6, "$"); # 结果：$$$殿速速
SELECT RPAD("你是谁",6,"$"); # 结果：你是谁$$$
# 如果左填充长度小于字段长度,会被截断（右填充截前面的）
SELECT LPAD("殿速速", 2, "$"); # 结果：殿速

数学函数

函数 描述
round 四舍五入
ceil 向上取整
floor 向下取整
truncate 截断小数点后几位
mod 取余

  • round四舍五入

    SELECT ROUND(-132.633511) result; # 结果：-133
# 第二个参数保留几位小数
SELECT ROUND(-132.233511,2) result; # 结果：132.23
SELECT ROUND(431.5345) result; # 结果：432
SELECT ROUND(431.2345,2) result;# 结果：431.23

  • truncate截断小数点后几位

    SELECT TRUNCATE(1.45646,1); # 结果：1.4

日期函数

函数 描述
now 返回当前系统日期+时间
curdate 返回当前系统的日期(不包含时间)
[year|month|day|hour|minute|second] 获取指定的部分,年,月,日,小时,分钟,秒
str_to_date 将指定字符通过指定格式转换成日期
date_format 将日期转成字符 
#now 返回当前系统日期+时间
SELECT NOW();

#curdate 返回当前系统的日期(不包含时间)
SELECT CURDATE();

#也可以获取指定的部分,年,月,日,小时,分钟,秒 (YEAR,MONTH,DAY,minute,second)
SELECT YEAR(NOW());
SELECT YEAR("1893-10-12");
SELECT YEAR(hiredate) FROM employees;

SELECT  MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

#str_to_date 将指定字符通过指定格式转换成日期: "%Y-%c-%d"
SELECT STR_TO_DATE("1952_3_5","%Y_%m_%d"); #按照它指定字符格式去取日期
SELECT STR_TO_DATE("1952_3_5","%Y_%c_%d"); #按照它指定字符格式去取日期

#查询日志日期为1992-4-3的员工信息
SELECT hiredate,last_name FROM employees; #1992-04-03 00:00:00  K_ing 
SELECT hiredate,last_name FROM employees WHERE hiredate="1992-4-3";
SELECT hiredate,last_name FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format 将日期转成字符
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日");
SELECT DATE_FORMAT(NOW(),"%y年%m月%d日");
SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日");
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日%W%H时%i分%s秒");
SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日-星期%w-%H时%i分%s秒");

#查询有奖金的员工名和入职日期
SELECT last_name,DATE_FORMAT(hiredate,"%Y年%m月%d日") 日期,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

其他函数

函数 描述
VERSION 版本号
DATABASE 当前数据库
USER 当前用户

流程控制函数

函数 描述
if 类似三元运算符
case 类似switch-case 
# IF
SELECT IF(10<3,'小','大');
SELECT last_name,commission_pct 奖金,IF(commission_pct IS NULL,"没奖金 呵呵","有奖金,嘻嘻") 备注
FROM employees
ORDER BY commission_pct DESC;
# CASE
/*
mysql中
#case第一种写法
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/

/*案例：查询员工的工资，要求

部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

*/

SELECT last_name,salary 原工资,department_id,
CASE department_id  #用法是相等
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END 新工资
FROM employees;

#case：第二种写法
/*
mysql中：
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/
SELECT last_name 姓名,salary 工资,
CASE
WHEN salary>20000 THEN "A"
WHEN salary>15000 THEN "B"
WHEN salary>10000 THEN "C"
ELSE "D"
END 工资登记
FROM employees
ORDER BY salary DESC;

分组函数

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

分类：

​ sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点：

  1. sum、avg一般用于处理数值型

    max、min、count可以处理任何类型

  2. 以上分组函数都忽略null值

  3. 可以和distinct搭配实现去重的运算

  4. count函数的单独介绍
    一般使用count(*)用作统计行数

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

#1.简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 总和,TRUNCATE(AVG(salary),2) 平均值取两位,MAX(salary) 最大值,MIN(salary) 最小值,COUNT(salary) 个数
FROM employees;

#2.参数支持哪些类型
SELECT SUM(last_name) FROM employees;

SELECT AVG(last_name) FROM employees;

SELECT MIN(last_name) FROM employees;

SELECT COUNT(last_name) FROM employees;

#3. 是否会忽略null不计
SELECT MAX(last_name) FROM employees;   #null会忽略不计
SELECT MIN(commission_pct) FROM employees; #null会忽略不计
SELECT COUNT(commission_pct) FROM employees; #null会忽略不计

#4.和distinct去重搭配
SELECT SUM(DISTINCT(salary)),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT(salary)),COUNT(salary) FROM employees;

#5. count合并函数的详细介绍
SELECT COUNT(salary) FROM employees;
# 统计数据表的行数
SELECT COUNT(*) FROM employees; #所需时间慢些在inndob引擎慢，在myisam引擎下快
# 统计数据表的行数（可以将* 换为1，2等，或者字符）
SELECT COUNT(1) FROM employees; #所需时间快些在inndob引擎快，在myisam引擎下慢
#扩展：查看当前什么存储引擎(利用系统变量和函数进行查询或模糊匹配)
SHOW TABLE STATUS FROM myemployees WHERE NAME="employees";
SHOW TABLE STATUS FROM mysql WHERE NAME="db";
SHOW VARIABLES LIKE "%storage_engine%";

#查看创建的表示什么引擎,
USE myemployees;
SHOW CREATE TABLE jobs; #这条命令不仅可以查看表示用什么引擎，还可以看到怎么创建表的
USE mysql;
SHOW CREATE TABLE db;

#desc table只能查看表的一部分结构
DESC jobs;
SELECT VERSION(); #查看版本号
SHOW ENGINES; #查看系统默认支持的存储引擎
USE mysql;
SHOW TABLES;

#6.和分组函数一同查询的字段有限制
USE myemployees;
SELECT AVG(salary),employee_id FROM employees; #可以执行但无意义，所查询字段要有同样的行数，否则必有缺失

分组查询

语法：

select 查询列表
from 表
【where 分组前筛选条件】
group by 分组的字段
【having 分组后筛选条件】
【order by 排序的字段】;

特点：

  1. 和分组函数一同查询的字段必须是group by后出现的字段

  2. 筛选分为两类：分组前筛选和分组后筛选

    针对的表 位置 连接的关键字
    分组前筛选 原始表 group by 前 where
    分组后筛选 group by后的结果集 group by 后 having
    1. 分组函数做条件肯定是放在having字句后
    2. 能用分组前筛选的，就优先考虑使用分组前筛选

  3. group by分组可以按单个字段也可以按多个字段(多个字段之间使用逗号隔开没有顺序要求),也支持表达式或函数(用得比较少)

  4. 可以搭配着排序order by使用,放在整个分组查询之后的

简单分组

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个位置的部门个数
SELECT COUNT(*),department_name
FROM departments
GROUP BY department_name;

分组前筛选

分组前筛选：按照分组字段分组后，每组按照筛选条件过滤

SELECT 列名 FROM 表名 WHERE 筛选条件 GROUP BY 分组字段

#案例1：查询邮箱中包含a字符的每个部门的最高工资
SELECT department_id,MAX(salary)
FROM employees
WHERE email LIKE "%a%"
GROUP BY department_id;

SELECT DISTINCT(department_id)
FROM employees;

#案例2： 查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

SELECT DISTINCT(manager_id)
FROM employees
WHERE commission_pct IS NOT NULL;

分组后筛选

分组后筛选：将筛选后的数据按字段分组

SELECT 列名 FROM 表名 GROUP BY 分组字段 HAVING 筛选条件

#案例1：查询部门的员工个数大于5个的部门
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING 员工个数 > 5;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
# ①查询每个工种有奖金的员工的最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE comission_pct IS NOT NULL
GROUP BY job_id

# ②根据①的结果继续筛选最高工资>12000
SELECT job_id,MAX(salary)
FROM employees
WHERE comission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
#①查询每个领导手下的员工固定最低工资
SELECT MIN(salary),management_id
FROM employees
GROUP BY manager_id;

#②添加筛选条件领导标号 > 102（分组前筛选）
SELECT MIN(salary),management_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id;

#③添加筛选条件最低工资>5000（分组后筛选）
SELECT MIN(salary),management_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

TODO:待更新！！。。。

上一篇：Mysql基础部分总结


下一篇：mysql之分组函数10