mysql语言的学习

2022-11-15 12:42:15

语言的学习

DQL(Data Query Lanuage ):数据查询语言(查select)
DML(Data Manipalution Lanuage):数据操作语言(增删改)
DDL(Data Define Lanuage):数据定义语言(drop,truncate,create,alter)
TCL(Transaction control Lanuage):事务控制语言

数据查询语言DQL

基础查询

语法:

select 查询列表 from 表名；

类似于：System.out.println(打印东西);
特点：
1. 查询列表可以是：表中的字段，常量值，表达式，函数
2. 查询的结果是一个虚拟的表格
查询表中单个字段

select bid from book;

查询多个字段：

select bid,bname from book;

查询表中所有字段：

select bid,bname,bwriter,bput from book;

select * from book;
着重号：( ` ) 用于区分关键字和字段
查询常量值：

select 100；

select ‘join’;(字符类型常量)
查询表达式：

select 100%98；
查询函数：

select version();
起别名方式一：使用as

SELECT 100%98 AS 结果;

SELECT last as 姓，first as 名 from emp;

好处：
1. 便于理解
2. 如果查询的字段有重名的，可以起别名区分开来
起别名方式二：使用空格

select last 姓，first 名 from emp;

案例：查询salary，显示结果为out put

select salary as “out put” from emp;(out是关键字，而且不能有空格，所有可以用引号引起来)
去重：

查询员工表中涉及到的所有部门编号

select department_id from emp;(部门有很多重复的)

select distinct department_id from emp;
+号的作用

Java中的+号：
1. 运算符：两个操作数都为数值型
2. 连接符：只要有一个操作数为字符串
mysql中的+号：
1. 仅仅只有一个功能：运算符
  
  select 100+90；
  
  两个操作数都为数值型，则做加法运算。
  
  select ‘123’+90；
  
  其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则做加法运算。
  
  select ‘jonn’+90；
  
  如果转换失败，则将字符型数值转换成0
  
  select null+0;
  
  只要其中一方为null，则结果肯定为null。
案例：查询员工名和姓连接成一个字段并显示为姓名

select last_name+first_name as 姓名 from emp;

(并没有显示出来结果，有错误)

select concat(‘a’,‘b’,‘c’) as 结果；结果是：abc

select concat(last_name,first_name) as 姓名 from emp；

(这个时侯名和姓会作为姓名显示出来)

条件查询

语法：

select 查询列表 from 表名 where 筛选条件;
分类：
1. 按条件表达式筛选:
  
  (1)条件运算符：>,<,=,<>,>=,<=
2. 按逻辑表达式筛选：
  
  (1)逻辑运算符：&&, ||,! and or not
  
  &&和and:
  
  两个条件都为true，结果为true，否则false
  
  ||或or:
  
  只要有一个条件为true，结果为true，否则false
  
  ！或not：
  
  如果连接的条件本身为false，结果为true，否则false
3. 模糊查询：
  
  like特点：
  
  （1）一般和通配符搭配使用
  
  通配符：
  
  %：任意多个字符,包含0个字符
  
  _：单个字符
  
  between and
  
  in
  
  is null /is not null
按条件表达式筛选

案例一：查询员工工资大于12000的员工信息

select * from emp where salary>12000;
按逻辑表达式筛选

案例一：查询工资在10000到20000之间的员工名，工资

select last_name,salary from emp where salary>=10000 and salary<20000;

案例二：查询部门编号不在90-110之间的或者工资高于15000的员工信息

select * from emp where departmet_id<90 or departmet_id>110 or salary>15000;

select * from emp where not(departmet_id>=90 and departmet_id<=110 )or salary>15000;
模糊查询
1. like
  
  案例一：查询员工名中包含字符a的员工信息
  
  select * from emp where last_name like ‘%a%’;
  
  案例二：查询员工名中第二个字符为_的员工名
  
  select last_name from emp where last_name like
  
  ’ _ $ _%’ escape ‘$’;(可以任意一个符号为转义字符，需要escape来转义)
2. between and
  
  (1)between and 提高简洁度
  
  (2)包含临界值
  
  (3)临界值不要调换顺序
  
  案例一：查询员工编号在100到200之间的员工信息
  
  select * from emp where emp_id between 100 and 120;
3. in
  
  含义：判断某字段的值是否属于in列表中的某一项
  
  特点：
  
  （1）提高简洁度
  
  （2）in列表的值类型必须统一或兼容
  
  案例一：查询员工的工种编号是 it,ad中的一个员工名和工种编号
  
  select last_name,job_id from emp where job_id in(‘it’,’’,ad);
4. is null
  
  （1）=或<>不能判断null值
  
  （2）is null或者is not null可以判断null值
  
  案例一：查询没有奖金的员工名和奖金率
  
  select last_name commission_pct from emp where commission_pct is null
  
  案例一：查询有奖金的员工名和奖金率
  
  select last_name commission_pct from emp where commission_pct is not null
5. 安全等与<=>
  
  （1）可读性较低
  
  （2）既可以判断null值，又可以判断普通数值
  
  案例一：查询没有奖金的员工名和奖金率
  
  select last_name commission_pct from emp where commission_pct <=>null
  
  案例二：查询工资为12000的员工信息
  
  select * from emp where salary <=>12000;
6. concat函数【补充】
  
  功能：拼接字符
  
  select concat（字符1，字符2，字符3，…）;
7. ifnull函数【补充】
  
  判断某字段或者表达式是否为null，如果为null返回指定值，否则返回原本值
  
  select ifnull(com,0) from emp;
  
  如果com为null返回0
8. isnull函数【补充】
  
  判断字段是否为null，如果是返回1，否则返回0
  
  select isnull(com),com from emp;

排序查询

select 查询列表 from 表 where 筛选条件 order by 排序列表 asc/desc
默认asc，asc可省略
特点：

asc：升序

desc：降序
按筛选条件排序

按表达式排序

按函数排序

按多个字段排序

按别名排序

常见函数

一、基本介绍

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

1.隐藏了实现细节

2.提高代码重用性
特点：

函数名（叫什么）

函数功能（干什么）
分类：

1.单行函数

如：concat，length，ifnull

2.分组函数

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

二、函数使用

字符函数
1. length获取参数值的字节个数
  
  utf8：英文1个字节，汉字3个字节
  
  gdk：汉字2个字节
  
  select length(‘syz’);
2. concat拼接字符串
  
  select concat(sc,’/’,sd) 姓名 from emp;
3. upper,lower
  
  案例：将姓变大写，名变小写在拼接
  
  select concat(upper(last_name),lower(first_name)) 姓名 from emp;
4. substr,substring
  
  注意：索引从1开始
  
  案例一:显示陆展元,截取指定索引后的所有字符
  
  select substr(‘李莫愁爱上了陆展元’,7) out_put;
  
  案例二:显示李莫愁,截取指定字符长度的字符
  
  select substr(‘李莫愁爱上了陆展元’,1,3) out_put;
5. instr：返回子串第一次出现的索引，如果找不到返回0
6. trim：去掉前后的东西
  
  select trim(‘a’,‘aaa孙aa育卓aa’) as out_put;
  
  结果：孙aa育卓
7. lpad用指定的字符左填充指定长度
  
  案例一：select lpad(‘孙育卓’，2，’*’) as out_put;
  
  结果：孙育
  
  案例二：select lpad(‘孙育卓’，5，’*’) as out_put;
  
  结果：**孙育卓
8. rapd用指定的字符右填充指定长度
  
  案例一：select rpad(‘孙育卓’，5，’*’) as out_put;
  
  结果：孙育卓**
9. replace替换
  
  select replace(‘电脑坏了’，‘坏’,‘好’) as out_put;
  
  结果：电脑好了
数学函数
1. round四舍五入
  
  select round(1.45); 结果：1
  
  select round(-1.45); 结果：-2
  
  select round(1.567,2); 结果：1.57
2. ceil向上取整，返回大于等于该参数的最小整数
  
  select ceil(1.00); 结果：1
  
  select ceil(1.02); 结果：1
  
  select ceil(-1.02); 结果：-1
3. floor向下取整，返回<=参数的最大整数
4. truncate截断
  
  select truncate(1.65,1); 结果：1.6
  
  select truncate(1.699,1); 结果：1.6
5. mod取余
6. rand：获取随机数，默认返回0-1之间小数
日期函数
1. now返回当前系统日期+时间
  
  select now();
2. curdate 返回系统当前日期
  
  select curdate();
3. curtime 返回当前时间
  
  selectcurtime();
4. 获取指定部分，比如年，月，日，小时，分钟，秒
  
  select year(now()) 年;
  
  select year(‘1998-1-1’) 年; 结果：1998
  
  select monthname(now()) 月; 结果：October
5. str_to_date 将字符通过指定格式转换成日期
  
  select str_to_date(‘1998-3-2’,’%Y-%c-%d’) as out_put;
6. date_format 将日期转换成字符
  
  select date_format(now(),’%y年%m月%d日’) as out_put;
7. datediff：返回日期相差的天数
8. monthname:以英文形式返回月
其他函数
1. version版本号
2. database 查看当前库
3. user 当前用户
4. password(‘字符’):返回字符的密码形式
5. md5(‘字符’)：返回md5加密形式
流程控制函数
1. if函数：if else 效果
  
  select if(10>5,‘大’,‘小’); 结果：大
2. case函数的使用一：switch 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 salary 原始工资，depart_id,
  
  case depart_id
  
  when 30 then salary*1.1
  
  when 40 then salary*1.2
  
  when 50 then salary*1.3
  
  else salary
  
  end as 新工资
  
  from emp ;
3. case函数的使用二：类似多重if
  
  case
  
  when 条件1 then 要显示的值1或语句1;
  
  when 条件2 then 要显示的值2或语句2;
  
  …
  
  end 要显示的值n或语句n;
  
  案例：查询员工的工资情况
  
  如果工资大于20000，显示A级别
  
  如果工资大于15000，显示B级别
  
  如果工资大于10000，显示C级别
  
  否则，D级别
  
  select salary,
  
  case
  
  when salary>20000 then ‘A’,
  
  when salary>15000 then ‘B’,
  
  when salary>10000 then ‘C’,
  
  else ‘D’
  
  end as 工资级别
  
  from emp;

二、分组函数

功能：用作统计，又称为聚合函数或组函数
分类：sum求和，avg平均值，max最大值，min最小值，count计数
特点：
1. sum和avg一般处理数值型
  
  max，min，count可以是任何类型
2. 分组函数都忽略null值
3. 可以和distinct搭配，去重
4. 一般使用count(*)统计函数
5. 和分组函数一同查询的字段要求是group by 字段
简单使用
1. select sum(salary) from emp;
2. select avg(salary) from emp;
是否忽略null值
count函数单独介绍
1. select count(salary) from emp;
2. select count(*) from emp; 经常用来统计行数
3. select count(1) from emp; 相当于加了一列1，统计一的个数
4. 效率：
  
  myisam存储引擎下，count(*) 的效率高
  
  innodb存储引擎下，count(*)和count(1)的效率差不多，比count(字段)高一些
和分组函数一同查询的字段有限制

select avg（salary），emp_id from emp;

avg(salary)就一行值，但是emp_id有很多行

分组查询

语法：

select 分组函数，分组后的字段（要求出现在group by的后面）（5）

from 表（1）

【where 筛选条件】（2）

group by 分组列表（3）

【having 分组后的筛选】（4）

【order by 子句】（6）

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段
分组前的筛选

关键字：where

筛选的表：原始表

位置：group by的前面
分组后的筛选

关键字：having

筛选的表：分组后的结果

位置：group by的后面
查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
1. 查询每个工种有奖金的员工的最高工资
  
  select max(salary),job_id from emp where com is not null group by job_id;
2. 根据a结果继续筛选，最高工资
  
  select max(salary),job_id
  
  from emp
  
  where com is not null
  
  group by job_id
  
  having max(salary)>12000;
特点：
1. 分组查询中的筛选条件分为两类:
  
  分组前筛选，原始表，group by子句前边，连接关键字where
  
  分组后筛选，分组后的结果集，group by子句后边，连接关键字having
2. 分组函数做条件肯定是放在having子句中
3. 优先考虑使用分组前筛选的
4. group by子句支持多个字段分组，也可以支持表达式或者函数
5. 也可以添加排序，排序放在整个分组查询最后
按表达式或者函数分组
1. 案例一：按员工姓名的长度分组，查询每一组员工个数，筛选员工个数>5的
  
  （1）查询每个长度的员工个数
  
  select count(*),length(last_name) len_name from emp group by length(last_name);
  
  （2）添加筛选条件
  
  select count( * ),length(last_name) len_name from emp
  
  group by len_name
  
  having count( * )>5;
按多个字段分组
1. 案例：查询每个部门每个工种的员工平均工资
  
  select avg(salary),dep_id,job_id
  
  from emp
  
  group by job_id,dep_id;
添加排序
1. 案例：查询每个部门每个工种的员工平均工资，并且按平均工资的高低显示出来
  
  select avg(salary),dep_id,job_id
  
  from emp
  
  group by job_id,dep_id
  
  order by avg(salary) desc;
select,from,where,group by,having,order by顺序

连接查询

含义：又称为多表连接，当查询的字段涉及多个表就用到多表查询。
笛卡尔积现象：表1有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
连接查询分类：

按年代分类：

（1）sql92标准：仅仅支持内连接

（2）sql99标准【推荐】：支持内连接，外连接（左外，右外），交叉连接

按功能分类：

（1）内连接（等值连接，非等值连接，自连接），

（2）外连接（左外连接，右外连接，全外连接你），

（3）交叉连接

一、sql92标准

等值连接

等值连接语法：

select 查询列表

from 表1 别名，表2 别名

where biao1.key = 表2.key

【and,group by,having,order by】

等值连接特点：

（1）多表连接结果为交集

（2）n表连接，至少需要n-1个连接条件

（3）多表的顺序没有要求

（4）一般需要为表起别名

（5）可以搭配排序，分组，筛选使用

等值连接（为表起别名）

案例一：查询女神名对应的男神名

select name,boyname from boys b ,beauty bt where bt.b_id = b.bid;
两个表的顺序可以调换
可以加筛选

案例一：查询有奖金的员工名，部门名

select last_name,dep_name,com from emp e,dep d where e.dep_id = d.dep_id and e.com is not null;

案例二：查询城市名中第二个字符为o的部门名和城市名

select dep_name,city from dep d,loc l

where d.loc_id = l.loc_id

and city like ‘%a%’;
可以加分组

案例一：查询每个城市的部门个数

select count(*) 个数，city

from dep d , loc l

where d.loc_id = l.loc_id

group by city;
可以加排序

案例一：查询每个工种的工种名和员工个数，并且按员工个数降序

select job_title,count(*)

from emp e,job j

where e.job_id = j.job_id

group by job_title

order by count(*) desc;
实现三表连接

案例：查询员工名，部门名，所在城市

select last_name,dep_name,city

from emp e,dep d,loc l

where e.dep_id = d.dep_id

and d.loc_id = l.loc_id

非等值连接

案例一：查询员工的工资和工资级别

select salary,grade_level

from emp e,job_grades g

where salary between g.‘lowest_sal’ and g.‘highest_sal’

自连接

案例：查询员工名以及上级的名字

Select e.emp_id,e.last_name,m.emp_id,m.last_name

from emp e,emp m

where e.ma_id = m.emp_id;

二、sql99标准

语法：

select 查询列表

from 表1 别名【连接类型】

jion 表2 别名

on 连接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序列表】
分类

内连接：inner

外连接：
1. 左外连接:left【outer】
2. 右外连接：right【outer】
交叉连接：cross
特点：

（1）添加分组，排序，筛选

（2）inner可以省略

（3）筛选条件放在where后面，连接条件放在on后面

（4）inner join连接和sql92语法中的等值连接效果一样，都是多表的交集
连接

（1）内连接
1. select 查询列表
  
  from 表1 别名
  
  inner jion 表2 别名
  
  on 连接条件；
  1. 等值连接
    
    案例一：查询员工名，部门名
    
    select last_name,dep_name
    
    from emp e
    
    inner join dep d
    
    on e.dep_id = d.dep_id;
  2. 非等值连接
    
    案例：查询员工的工资级别
    
    select salary，gra_level from emp e join on job_grade g
    
    on e.salary between g.lowest_sal and g.highest_sal;
  3. 自连接
    
    案例：查询姓名包含字符k的员工和上级的名字
    
    Select e.last_name,m.last_name
    
    from emp e
    
    join emp m
    
    on e.emp_id = m.emp_id;
    
    where e.last_name like ‘%k%’;
（2）外连接
1. 应用场景：用于查询一个表中有另一个表中没有的记录
2. 特点：
  （1）外连接查询结果为主表所有记录，
  如果从表有和他匹配的，则显示匹配值
  如果从表中没有和他匹配的，则显示null
  ! 外连接查询的结果=内连接结果+主表有从表没有记录
  
  （2）左外连接：left join左边是主表
  右外连接：right join右边是主表
  
  （3）左外和右外交换两个表的顺序，可以实现同样的效果
  （4）全外连接=内连接+表1中有但表2中没有+表2中有但表1中没有
  #引用：查询男朋友不在男神表的女神名
  左外连接：
  select b.name,bo.*
  from beauty b
  left outer join boyfriends bo
  on b.boyfriends_id = bo.id
  where bo.id is null
  右外连接：
  select b.name,bo.星
  from boys bo
  right outer join beauty b
  on b.boyfriends_id = bo.id
  where bo.id is null;
  案例：查询哪个部门没有员工：
  #左外
  select d.星，e.employee_id
  from departments d
  left outer join employees e
  on d.department_id = e.department_id
  where e.department_id is null;
  #右外：
  select d.星，e.employee_id
  from employees e
  right outer join departments d
  on d.department_id = e.department_id
  where e.department_id is null;
  （3）全外连接
  select b.星，bo.星
  from beauty b
  full outer join boys bo
  on b.boyfriemd_id = bo.id
  （4）交叉连接 (使用99标准实现笛卡尔乘积)
  select b.星，bo.星
  from beauty b
  cross join boys bo;
  #总结
  1.功能：sql99支持的较多
  2.可读性：sql99实现连接条件和筛选条件的分离，可读性

码农公寓