MySql笔记（一）

视频地址：https://www.bilibili.com/video/av59623481?from=search&seid=15894338323446762893

1、 查看当前所有的数据库
show databases;
2、 打开指定的库
use 库名
3、 查看当前库的所有表
show tables;
在当前库下查看其他库的所有表
show tables from 库名；
4、 创建表
create table 表名（
列名 type,
列名 type）;
5、 查看表结构
desc 表名;
6、 查看sql版本
select version();

一、基础查询

select 查询列表 from 表名；
1、查询列表可以是：表中字段、常量、表达式、函数
2、查询结果为虚拟列表

1、 查询单个或多个字段
select 属性名称， from 表名;
2、 查询表达式
select 100*99;
3、 查询函数
select 函数名；
4、 起别名
select … as 别名；
select last_name 姓， first_name 名 from 表名；
5、 去重
Select distinct 字段名 from表名；
6、 + 号的作用
仅仅只有一个功能：运算符

二、条件查询

语法：select 查询列表 from 表名 where 筛选条件； # 类似 if （ ） statement;
分类：
1、按条件表达式筛选 >, <, =, <>
2、按逻辑表达式： && || ! and or not
3、模糊查询：
like
between and
in
is null
1、like : where column like ‘%a%’; # like效率比较低，但查询到结果集的数据量少的时候
like耗时会增加，即总数据量不变，结果越少耗时约大

查询带有a字符的字段， 一般和通配符搭配使用；
通配符：

 % 任意多个字符（包括0个）
		_ 任意单个字符
         _\_   # 第二个字符为_， 支持转义字符
‘_$_%’  ESCAPE  ‘$’;  # 自定义转义字符

Not like # 加上not， 表示与like意思相反

2、 between and
可以提高语句的简洁；

3、 in # 用于判断某字段的值是否属于in列表中的某一项
Where job_id in (‘IT_PROT’, ‘AD_VP’);
----1、 提高语句简洁度
----2、 In列表的值类型必须一致或兼容

4、 is null # = 或者<>不能判断null值
Where column is null;
Where column is not null;

5、 安全等于 <=>
Where column <=>NULL;

Is null 和 <=>
Is null : 仅仅可以判断null值， 可读性较高
<=> : 既可以判断null值，也可以判断普通的数值

例子

select student_name, age, ifnull(salary, 0)*0.1 as nianxin from stu where student_name like '%p%';

三、排序查询

Select 查询列表
 From 表
 Where 筛选条件
 Order by 排序列表 【asc|desc】(默认asc,升序)

Select * from 表 where de_id >= 90 order by age asc; #查询部门编号大于90的员工，按年龄升序排序

Select  *， salary * 12 * (1+ifnull(salary, 0)) 年薪 from stu  order by  (1+ifnull(salary, 0)) desc;

按姓名长度排序 length()

查询员工信息，先按工资排序，再按员工编号排序【按多个字段排序】
 Select * from stu order by salary asc, employ_id desc;

四、常见函数

调用：
select 函数名（实参列表）【from 表】;
分类：
1、单行函数 concat length ifnull
2、分组函数 做统计使用，又称为统计函数，聚合函数，组函数

字符函数
数学函数
日期函数
其他函数
流程控制函数

单行函数
一、 字符函数
1、Length（） # 获取参数值的字节个数
Select length(‘john’); # 一个英文字符占一个字节
Select length(‘中’)； # 一个utf-8占3个字节

2、Concat()
Select concat(last_name, ‘_’, first_name) from stu;

3、upper(), lower()
Select upper(‘join’);
Select lower(‘join’);

4、substr=substring()
Select substr(‘abcdefg’, 5); =>> ‘efg’ # 索引从1开始
Select substr(‘abcdefg’, 1, 3); =>>’abc’ # 1为索引开始位置，3为字符长度

5、instr()
Select instr(‘杨不悔爱上了殷梨亭’,’殷梨亭’)； =>> 7 # 返回子串在str1中索引第一次开始的地方， 找不到就返回0

6、trim() 过滤指定字符串
Select Trim ([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

mysql> SELECT TRIM(’ bar '); -> ‘bar’
mysql> SELECT TRIM(LEADING ‘x’ FROM ‘xxxbarxxx’); --删除指定的首字符 x -> ‘barxxx’
mysql> SELECT TRIM(BOTH ‘x’ FROM ‘xxxbarxxx’); --删除指定的首尾字符 x -> ‘bar’
mysql> SELECT TRIM(TRAILING ‘xyz’ FROM ‘barxxyz’); --删除指定的尾字符 x -> ‘barx’

7、 Lpad() 用指定的字符实现左填充 指定长度
select lpad(‘ab’，5, ‘*’ ); ***ab
rpad() 右填充

8、 replace()
select replace(‘I love apple’, ‘apple, ‘huawei’) =>> I love huawei

二、 数学函数
Round() 四舍五入 round（1.753，2）=>> 1.75 保留两位
Ceil() 向上取整,返回>=该参数的最小整数
Floor() 向下取整
Truncate(x, d) 截断小数点后d位
Mod(10, 3) = 10%3 取余 a-a/b*b

三、 日期函数
Select now(); 返回当前系统日期+时间
Curdate() 返回当前系统日期， 不包括时间
Curtime()
Select year(now()) 年；
Select month(now()) 月；
Str_to_date(‘9-13-1999’, ‘%m-%d-%Y’) 将日期格式的字符转换为指定格式的日期
Date_format(‘2018/6/6’, ‘%Y年%m月%d日’) 日期转化为字符 =>> 2018年06月06日

四、 其他函数
Select version()
Select database();
Select user();

五、 流程函数

1、if 函数： if else
Select if (expr1, expr2, expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 if()的返回值为expr2; 否则返回值则为 expr3。if() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
2、case
(1) case 要判断的字段或表达式
When 常量1 then 要显示的值1或语句1；
When 常量2 then 要显示的值2或语句2；
。。。
Else 要显示的值n 或语句n;
End
例子：
select 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 as 新工资 form employees;
(2) case 函数使用二， 类似于多重if
/* java中，if(条件1){语句1；}
else if(条件2) {语句2；}
…
else{语句n;}
/* mysql
Case
When 条件1 then 要显示的值1或语句1；
When 条件2 then …
Else 要显示的值n或语句n;
End

例子：如果工资>20000, 显示级别A
如果工资>15000, 显示级别B
如果工资>10000, 显示级别C
否则，显示级别D
Select salary,
Case
When salary > 20000, then ‘A’
…
Else ‘D’
End as 工资级别 from employees;

分组函数
功能： 用作统计
1、Sum select sum(salary) from employees; null值不参与运算
Avg …
Max …
Min
Count select coount(salary) from employees; salary字段非空值有几个

2、sum,ave 一般用于数值型， null值不参与运算
Max, min, count 任何类型， null值不参与运算

3、可以和distinct搭配实现去重的运算
Sum(distinct salary)

4、 count 函数
Select count(*) from employees; 统计行数
Select count(1) from employees; 统计行数 ， 参数可以是常量值

5、和分组函数一同查询的字段有限制

六、 分组查询

Select column, group_function(column)
From table
[where] condition]
[group by group_by_expression]
[order by column]

例1、 查询每个工种的最高工资
Select max(salary), job_id
From employees
Group by job_id
例2、 查询邮箱中包含a字符，每个部门的平均工资
Select avg(salary), department_id
From employees
Where email like ‘%a%’
Group by department_id
例3、 查询有奖金的每个领导手下员工的最高工资
Select max(salary), manager_id
From employeees
Where commission_pct is not null
Group by manager_id
例4、 添加复杂的筛选条件(分组后的筛选)
（1） 查询哪个部门的员工个数>2
一． 查询每个部门的员工个数
二． 根据一的结果进行筛选
Select count(), depardentment_id
from employees
group by depardentment_id
having count()>2;
例5、 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
（1） 查询每个工种有奖金的员工的最高工资
（2） 根据1的结果继续筛选
select max(salary), job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;

小节：
1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by 子句的前面 where
分组后筛选 分组后的结果 group by 字句的后面 having

2、group by 子句支持单个字段分组，也可以多个字段分组（多个字段之间用逗号隔开，没有顺序要求），表达式或者函数

3、也可以添加排序

按表达式或函数分组
案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
Select count(), length(last_name) len_name
From employees
Group by length(last_name)
Having count()>5;
按多个字段分组
案例：查询每个部门每个工种的员工的平均工资
Select avg(salary), department_id, job_id
From employees
Group by department_id, job_id; # department_id和job_id必须一致才算一组

添加排序
案例：查询每个部门每个工种的员工的平均工资，平均工资高于10000，并且按平均工资的高低显示
Select avg(salary), department_id, job_id
From employees
Where department_id is not null
Group by department_id, job_id # department_id和job_id必须一致才算一组
Having avg(salary) >10000
Order by avg(salary) desc; # 在最后