概述
DB:数据库
DBMS:数据库管理系统(软件)
SQL:用于 DB 和 DBMS 通信的语言(此为通用语言)
这里主要学习 MySQL 数据库
1.开源免费
2.性能高
3.简单
4.可移植性好(可在windows,linux下运行)
数据库是一个柜子
柜子里放了多张表格
字段: 每行表格的 每类
属性: 每竖
DBMS在非共享状态下需要安装 客户端 与 服务端
卸载 MySQL 除 程序 卸载 之外
还需要找到安装路径 删除整个文件夹
还需找到 c盘 根目录 隐藏文件夹 ProgramData
里面还有一个 MySQL文件夹 删除它
如果你重新安装依然有问题 使用360 粉碎
清理注册表
登录时的账户密码
root 密码 1234
启动数据库软件服务
计算机 右键 管理 服务 服务
找到mysql0815
右键 启停
属性 修改手启 自启
或
以管理员 进入 命令提示符
net stop mysql0815 停止名字为mysql0815的服务
net start mysql0815 启动名字为mysql0815的服务
登录方式1(开始 mysql 自带的黑窗口 )
直接输入 root账户 之前设置的 密码 1234
退出登录
exit 回车即可
或
Ctrl + c 退出(只限于root用户)
登录方式2(windows原本的命令提示符)
mysql -h localhost -P 3306 -u root -p
-host 本地 -port端口号 用户名 密码
回车 会提示输入密码1234 输入即可
或
mysql -h localhost -P 3306 -u root -p1234
执行结果是一样的
此时密码为显示状态
前一种只显示****
也可以 这样写
mysql -hlocalhost -P3306 -uroot -p1234
默认连接本地 3306 端口 默认可省略 -hlocalhost -P3306
mysql -uroot -p1234
登录后
继续输入
即为 SQL 语言
show databases; 查看所有数据库 ; 可换为 /g 推荐 ;
会展示4个数据库
Database 头
1.information_schema 保存 元数据
2.mysql 保存 管理员信息
3.performance_schema 搜集的一些性能信息参数
4.test 测试用的(为空)
use test; 进入某个数据库
库名
show tables; 展示某个库的所有表格信息
身处当前数据库(术语) 想看其它数据库(术语)时
并不会改变身处位置
show tables from mysql; 展示其它库存在的所有表格信息
展示 表格(术语) 库名
select database(); 身处何处
create table stuinfo( 在当前位置建表
新建 表格 名字称为stuinfo
回车 继续输入 表头 和 表头这一列数据类型
id int,
表头 数值
回车
继续追加 列
name varchar(20));
表头 字符串 分号代表此次sql命令结束
desc stuinfo; 查看 表格属性 查询 显示表结构
进入 表格名字
select * from stuinfo; 展示表格(术语)
要展示的表格名字
insert into stuinfo (id,name) values(1,'john'); 编辑表格
(术语) 插入
要编辑的表格名字 里面的两列 表头的值(其中之一行)
insert into stuinfo (id,name) values(2,'rose'); 再来一行
id name
1 john
2 rose
断电重启 此数据库(术语)依然存在
update stuinfo set name='lilei' where id=1;
将当前所处位置(test数据库(术语)中)
stuinfo 表格(术语)中id为 1 的 name 修改为 lilei
id name
1 lilei
2 rose
delete from stuinfo where id=1;
将当前所处位置(test数据库(术语)中)
stuinfo 表格(术语)中id为 1 的 那一行 删除
id name
2 rose
查看 版本号
select version();
或
在非登录状态下 原本命令行窗口下
mysql --version
mysql -V 简写
SQL 命令语法规范
1. 实际上不区分大小写
2. ; 或 /g 结尾 推荐;
3. 不写结尾 即使回车 也只是换行了 并没有执行
4.注释 单行:#注释文字
或
-- 注释文字
多行: /* 注释 */ 和js一样
f9 运行
SHOW DATABASES; 要分号
USE test 不能写分号
DQL 查询 相关操作
DML 增删改 相关操作
DDL 创建/删除 库/表
TCL 事物控制
DCL 数据控制语言
#进阶1: 基础查询
/*
语法:
select 查询列表 from 表名;
类似于:system.out.println(打印东西);
特点:
1.查询列表可以是 多个/一个:表中的字段, 常量,表达式,函数
2.查询的结果是一个虚拟的表格
*/
USE `myemployees` #1.查询表中的单个字段
SELECT
last_name
FROM
employees ;
#查询 last_name 从 employees 数据库(术语)
#2.查询表中的多个字段
SELECT
last_name,
salary,
email
FROM
employees ;
#3.查询表中的所有字段
SELECT
`hiredate`,
`department_id`,
`manager_id`,
`commission_pct`,
`salary`,
`job_id`,
`phone_number`,
`email`,
`last_name`,
`first_name`,
`employee_id`
FROM
`employees` ;
#方式二:
SELECT
*
FROM
employees ;
#4.查询常量值 查看 打印
SELECT 100;
SELECT 'john';
#后面不加 from 表名 你要加也不报错 只不过没什么意义(只不过
表有多少行 显示多少结果 不加只显示一种结果)
#注意:字符型和日期型的常量值必须用引号,数值型不需要引号
#5.查询表达式
SELECT 100%98;
支持 + - * / %(取模)
不支持 ++ -- *=
#6.查询函数
SELECT VERSION();
mysql的函数调用前面必须加 select
而且mysql函数的调用是必须有返回值的
# select 解释为查看更为具体
#7.起别名(as 和 空格(相当于省略了as))
/*
显示自定义 替换原本表头的名字
*/
#方式一:
SELECT 100%98 AS 结果;
SELECT
last_name AS 姓,
first_name AS 名
FROM
employees ;
#方式二:
SELECT 100%98 结果;
SELECT
last_name 姓,
first_name 名
FROM
employees ;
#案例:查询salary,显示结果为 out put
SELECT
salary AS 'out put'
FROM
employees ;
#8.去重 DISTINCT (一次查询(SELECT )同时只可去一个重(去多
个重 出来的结果 使表不规则 从道理上就说不通))#案例:查询员
工表中涉及到的所有的部门编号`department_id`
SELECT DISTINCT
department_id
FROM
employees ;
#9. + 号仅有 加法 运算功能
SELECT
100+90 ;
# 190
SELECT
'123' + 90 ;
#213 (会试图将字符转换为数值)
SELECT
'john' + 90 ;
# 0(转换失败)+90 90
SELECT
NULL + 90 ;
# 有 null 为 null
#即使在下列表头合并 CONCAT(中有null) 也会全部为null
#10. 表头拼接 合并表头
#案例:查询员工名和姓,连接成一个字段,并显示为 姓名
如果字段为数值型 会转换成 字符型
SELECT
CONCAT(
`last_name`,
`first_name`,
IFNULL(`commission_pct`, 0)
) AS 姓名
FROM
employees ;
#11. ifnull(可能为null的表头,为null时返回这里)
select ifnull(commission_pct,0) from employees;
判断某字段 或 表达式是否为null
如果为null 返回指定的值 否则返回原本的值
#12.[补充] isnull 函数(判断某字段或表达式是否为null.如果是
查询结果1,否则为0)
select isnull(commission_pct) from employees;
如果commission_pct 为null isnull(commission_pct)结果为
1(true 真)
如果commission_pct不为null isnull(commission_pct)结果为
0(false假)
进阶条件查询 2
#进阶2:条件查询
/*
语法 :
select
查询列表(第2步)
from
表名(第1步)
where(子句)
筛选条件;(第3步)
分类:
一.按条件表达式筛选
简单的条件运算符: > < = >= <=
!=或<>
<=>安全等于即使判断目标null也不会报错
二.按逻辑表达式筛选
逻辑运算符:
作用:用于连接条件表达式
&& || !
或 and or not
三.模糊查询
like:一般搭配通配符使用,用于判断 字符/数值 型数值
SELECT
*
FROM
employees
WHERE department_id like '1__';
#展示表头为 department_id 1某某 的查询结果
通配符:%任意多个字符,_任意单个字符
between and 提高语言简洁度与可读性
in 提高语言简洁度与可读性
is null / is not null 用于判断null值
is null 不能判断普通类型的数值 只能判断null值 可
读性高
<=> 能判断普通类型的数值 能判断null值 可
读性较差
*/
#一.按条件表达式筛选
#案例1:查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE salary > 12000 ;
#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE department_id <> 90 ;
#where department_id != 90 ;
#二.按逻辑表达式筛选
#案例1:查询工资在10000到20000之间的员工名.工资以及奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE salary >= 10000
AND salary <= 20000 ;
#案例2:查询部门编号不在90到110之间,或者工资高于15000的员工
信息
#编号大于110 或 小于90 或 工资大于 15000
# not 是 非 也就是 !
SELECT
*
FROM
employees
WHERE NOT (
department_id >= 90
AND department_id <= 110
) #不等于 90到110这个区间
OR salary > 15000 ;
SELECT
*
FROM
employees
WHERE department_id < 90
OR department_id > 110
OR salary > 15000 ;
/*
like
特点:
1.一般和通配符搭配使用
通配符:
% 任意多个字符,包含0个字符
_ 热议单个字符
between and
in
is null | is not null
*/
#1.like
#案例1:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE last_name LIKE '%a%' ;
#字符串必须用单引号 %代表a前后还有东西的通配符
#二.案例2:查询员工名中第三个字符为e的,第5个字符为a的员工名
和工资
SELECT
last_name,
salary
FROM
employees
WHERE last_name LIKE '__n_l%' ;#第三个n 5为l
#案例3:查询员工名第二个字符为_的员工名
SELECT
last_name
FROM
employees
WHERE last_name LIKE '_$_%' ESCAPE '$' ;
#指定$(可任意) 为转义字符
#where last_name like '__%'; #自带转义 _
#2.between and 在...之内(包括自己且必须前者小后者大)
/*
1.使用我们的between and 可以提高语句的简洁度
2.包含临界值
3.两个临界值不要调换顺序 必须前者小后者大
*/
#1.查询员工编号100到120之间的员工信息
SELECT
*
FROM
employees
WHERE employee_id BETWEEN 100
AND 120 ;
#where employee_id >= 100
#and employee_id <= 120 ;
#3.=表头 in(表头其中一个内容,表头其中一个内容,表头其中一个
内容 )
/*
含义 : 用于判断某字段的值是否属于in列表中的某一项
特点:
1.使用in提高语言简洁度
2.in列表的值类型必须一致或兼容
3.不能使用通配符 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') ;
#4.is null
/*
不能使用 = <> 识别 null
必须使用专门的 is null或 is not null 判断 值(可能含null)
不可使用 salary is 12000
但可使用 <=> 安全等于
*/
#案例1:查询没有奖金的员工名和奖金率
# 为null
#IS NULL 代替 = 判断是否 为null
#IS NOT NULL 代替 != 判断是否 不为null
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NOT NULL ;
#安全等于 <=> 是否等于如果等于返回true
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct <=> NULL ;
#案例2:查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE salary <=> 12000 ;
#和等于没什么区别
/*
is null 与 <=> 真的一样吗
is null:仅仅可以判断null值
<=> :既可以判断null值,又可以判断普通的数值
*/
#题目 查询员工号为176的员工的姓名,部门号与年薪
/*月薪*12月+年终奖(0.几*年薪) 别名 查询结果的
as 别名
ifnull 为null时为其显示0不报错
commission_pct 年终奖 0.几 实际值为 年薪*0.几
last_name 姓名
department_id 编号
salary 月薪
*/
SELECT
last_name,
department_id,
salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees ;
#题目 查询没有奖金,且工资小于18000的salary,last_name
# commission_pct IS NULL且 AND salary < 18000
SELECT
salary,
last_name
FROM
employees
WHERE commission_pct IS NULL
AND salary < 18000 ;
#题目 查询employees表中,job_id 不为'IT'或者工资为12000的
员工信息
SELECT
*
FROM
employees
WHERE job_id <> 'IT'
#WHERE job_id != 'IT'
OR salary = 12000 ;
#题目 查看部门 departments 表的结构,查询效果如下
DESC departments ;
#题目 查询部门 departments 表中涉及到了哪些 location_id
位置编号
# DISTINCT 去重
SELECT DISTINCT
location_id
FROM
departments ;
#题目 理解问题
/*
请问:
select * from employees 与
select * from employees where commission_pct like '%%'
and last_name like '%%'
结果是否一样
不一样
由于 commission_pct(奖金)有的没有奖金为null
如果判断的字段有null的情况
第一种 commission_pct 会显示为 null 的结果
第二种 commission_pct 不会显示为 null 的结果
如果判断的字段没有null值 两者一样
都不会显示为 null 的结果
所以结果不一样
*/
SELECT
# 第一种 commission_pct 有 null
*
FROM
employees ;
SELECT
#第二种 commission_pct 没有 null
*
FROM
employees
WHERE commission_pct LIKE '%%'
AND last_name LIKE '%%' ;
/*
但是将
select * from employees where commission_pct like '%%'
and last_name like '%%'
更改为
select * from employees where commission_pct like '%%'
or last_name like '%%'
后面继续写满 employees 表所有标题
这里省略
这个时候
结果 就一样了
都会显示为 null 的结果
这样看来 null 代表为空 and/&& 也就是false 是不显示的
但是 || / or 为false 的 null 也是null 是会显示的
*/
/*
总结:
1.数据库的好处
1.可以持久化数据到本地
2.结构化查询
2.数据库的常见概念 (较为重要)
1.DB: 按照一定规定排列的表格所组成的数据库(容器/大
仓库)
2.DBMS: 数据库管理 软件(系统)
3.SQL: 上面两者之间的 通信的一种通用语言(结构化查
询语言)
3.数据库存储数据的特点
1.数据存放到表中,表再存放到库中
2.一个库中可以有多张表,每张表具有唯一的表名(废话)
3.表中有一个或者多个列,列又称为'字段',相当于js中的
'属性'
4.表中的每一行数据,相当于js中的一个对象
4.常见的数据库
mysql(oracle公司) ,
oracle(oracle公司) ,
db2(IBM公司) ,
sqlserver(微软公司) ,
*/
一.mysql的背景,优点,安装,服务的启动/停止,登录/退出
mysql安装后 属于C/S架构 既有服务端 也有客户端
一般来讲安装服务端 安装路径 非中文 没有空格
企业版 社区版 5.5, 5.6, 5.7, 8.0(beta测试版)
#进阶3: 排序查询
/*
引入:
SELECT
*
FROM
employees ;
语法:
select 查询列表(表头) (第三步)
from 表(大表) (第一步)
[where 筛选条件] (第二步)
order by 排序列表 [asc|desc] (第四步)
升序|降序
特点:
1.asc代表的是升序,desc代表降序
如果不写默认是升序
2.order by 子句中可以支持单个字段,
多个字段,表达式,函数,别名
3.order by子句一般是放在查询语句的最后面,
limit子句除外
*/
#案例1: 查询员工信息,要求工资从高到低排序
SELECT
*
FROM
employees
ORDER BY salary DESC ;
#降序 DESC
SELECT
*
FROM
employees
ORDER BY salary ;
#ORDER BY salary asc ;
#升序 asc 升序可省略不写 默认升序排序
#案例2:查询(department_id)部门编号>=90的员工信息 ,按
(hiredate)入职先后进行排序[添加筛选条件]
SELECT
*
FROM
employees
WHERE department_id >= 90
ORDER BY hiredate ;
#ORDER BY hiredate asc;
#升序 asc 升序可省略不写 默认升序排序
/*
案例3: 按年薪的高低显示员工的(*)信息和年薪[按表达式排序]
(salary*12*(1+commission_pct))年薪 月薪*12(1+年终奖(年薪
的0.几))
(commission_pct)年终奖 有的员工无年终奖 数值为 null
(salary*12*(1+ifnull (commission_pct,0))) 年薪
ifnull(可能为null的值,代替null的值)
*/
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0))
FROM
employees
ORDER BY salary * 12 * (1+ IFNULL(commission_pct, 0))
DESC ;
# 表达式为结果的 降
序
#表达式太长 我们试试按别名排序 表头后+ as 或 空格 +你取的
别名
#案例3: 按年薪的高低显示员工的(*)信息和年薪[按别名排序]
#所谓别名 就是给表头重命名 不是其它地方 请注意
#别名亦是变量 可以用来给 ORDER BY 排序
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 DESC ;#降序
#案例:查询 根据(last_name)姓名字节长度进行排序,显示员工的
姓名和工资
#姓名的长度 用到一个函数 length(表头) 查询结果为表头的长度
#所以实际上是[按函数排序]
SELECT
LENGTH(last_name) 字节长度,
last_name,
salary
FROM
employees
ORDER BY 字节长度 DESC ;
#降序
/*
最后一个案例6:查询(*)员工信息,要求先按(salary)工资(asc)升
序,再按(employee_id)员工编号(desc)降序
前面都只有 一项 排序
这次既有工资升序 又有编号降序 两项排序
先按 工资升序 工资相同的 再按 编号降序
假设工资相同 编号采用降序(不写降序,默认随机排列)
后面还可以接多个
*/
SELECT
*
FROM
employees
ORDER BY salary ASC,#升序
employee_id DESC ;#降序
/*
1.题目:查询员工的(last_name)姓名和(department_id)部门号和
年薪,按年薪降序,按姓名升序
(salary*12*(1+ifnull (commission_pct,0))) 年薪
*/
SELECT
last_name,
department_id,
salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY 年薪 DESC,
#降序
last_name ASC ;
#升序
#年薪按降序派 年薪相同按 姓名 升序排
/*
2.题目选择工资不在8000到17000的员工的(last_name)姓名和
(salary)工资,按工资降序
*/
SELECT
last_name,
salary
FROM
employees
WHERE salary NOT BETWEEN 8000
AND 17000 #where salary<=8000 and salary>=17000
ORDER BY salary DESC ;
#降序 4个字母升序 3个字母 升3降4
#3.查询(email)邮箱中包含e的员工信息,并先按邮箱的字节数降序
,再按(department_id)部门号升序
#字节数 length()
SELECT
*
FROM
employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,
# 降 邮箱字节数(长度)
department_id ASC ;
#升 部门号
#进阶4:常见函数
/*
概念:类似于js的方法,将一组逻辑语句封装在方法体中,对外暴露
方法名
好处:1.隐藏了实现细节 2.提高代码的重用性
调用: select 函数(实参列表(表头)) [from 表](有就有,不从这
获取数据可以不写);
特点:
1.叫什么 (函数名)
2.干什么 (函数功能)
分类:
1.单行函数(肯定会有返回值(查询结果))
如 concat(表头拼接),length(长度),ifnull(判断,替换
null)
字符函数 ;传入字符型 并处理
数学函数 :传入数值型 并处理
日期函数 :传入日期型 并处理
其它函数[补充] :稍后补充
流程控制函数[补充] :可以实现一些分支结构类似 if
else,while case循环语句
2.分组函数
功能:做统计使用(又称为 统计/聚合/组 函数)
*单行函数传一个值返回一个值
*多行函数传一组值返回一个值
常见函数:
字符函数:
length 获得字节(当前版本utf8下汉字=3字节)
长度
concat字符拼接
substr 截取字符串
instr(母串,子串) 返回子串在母串第一次出现的起始索
引,如果找不到返回0
trim 去空格
upper 变大写
lower 变小写
lpad 左填充
rpad 右填充
replace 替换
数学函数:
round 四舍五入
ceil 向上取整
floor 向下取整
truncate 保留小数点后几位
mod 取模
补充 rand :获得随机数,反回0-1(不包括1)之
间的小数
日期函数
now 获得现在时间
curdate 获取现在日期 不包含时间
curtime 获取现在时间 不包含日期
year 获取now的年
month 获取now的月
monthname 以英文形式反回月
day 获取now的日
hour 小时
minute 分钟
second 秒
str_to_data 将字符串按格式转换成日期
date_format 将日期格式转换成字符串
datediff 反回两个日期相差的天数
其它函数:
version 当前数据库服务器的版本
database 当前打开的数据库
user 当前登录使用mysql的用户
password('字符') 反回该字符的密码形式 自
动加密
select password('张三') 张三会变成一堆字符串
select md5('张三') 张三也会变成一堆字符串
控制函数
if (条件表达式,表达式1,表达式2)
意思是:如果条件表达式成立,反回表达式1,否则返回表达
式2
case情况1
case 变量/表达式/字段(表头)
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
case情况2
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
*/
#一. 字符函数
#1.length 获取参数的字节个数
SELECT
LENGTH('haha') ;
#查询结果 haha的长度为 4
SELECT
LENGTH('张三丰hahaha') ;
#查询结果 张三丰hahaha 的长度为 15
#一个汉字占三个字节
SHOW VARIABLES LIKE '%char%' ;
#character_set_client 的字符集(格式)为 utf8
#在utf8下 一个英文字母占一个字节 一个汉字占3个字节
#在gbk 下 一个汉字占两个字节
#2.concat 拼接字符串
SELECT
CONCAT(last_name, '_', first_name) 姓和名
FROM
employees ;
#3.upper,lower
SELECT UPPER('john');#转换为大写
SELECT LOWER('john');#转换为小写(本身小写不变 有大有小全
变小)
#示例:将姓变大写,名变小写,然后拼接
SELECT
CONCAT(UPPER(last_name), LOWER(first_name)) 姓名
FROM
employees ;
#我们发现 函数可以嵌套函数 (我从 ifnull()就发现啦)
#4.substr/substring ,截取字字(非字节(在utf8下 汉字=3个英
文字母字节))
#注意:整个sql中索引都是从1开始的
SELECT
SUBSTR('我在学习mysql', 7) 看截取的第几位 ;
#截取 第7位后(包括7)所有字符 sql
SELECT
SUBSTR('我在学习mysql', 2,4) 看截取的第几位 ;
#截取从指定索引处指定字符长度的字符 结果 在学习m (非字节(
在utf8下 汉字=3个英文字母字节))
#案例:姓名中首字符大写,其它字符小写然后用_拼接 W_ang
#SUBSTR(last_name,1,1) 首字符
#CONCAT(要拼接的字符串,要拼接的字符串)
#SUBSTR(last_name,2)首字母后面的所有字符
#upper(要大写的字符)
#lower(要小写的字符)
#select 表达式/表头+空格/as+别名 取别名
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
) 姓
FROM
employees ;
#5.instr()返回子串第一次出现的起始索引,如果找不到返回0
SELECT
INSTR(
'我在mysql学习mysql',
'mysql'
) AS 输出内容 ;
#执行结果 5 '我在学习mysql'第5个字符处 是 'mysql'
#6.trim 去除左右两边空格/指定字符串
#length(返回字节数 字节(在utf8下 汉字=3个英文字母字节))
SELECT
LENGTH(TRIM(' 我在学习mysql ')) AS 输出内容 ;
# 执行结果 3+3+3+3+1+1+1+1+1=3*4+1*5=17 不含空格
SELECT
LENGTH(
TRIM(
'a' FROM 'aaaa我在学习mysqlaaaa'
#'aa' 时可能会出现 单数 剩下一个a的情况
)
) AS 输出内容 ;
# 执行结果 3+3+3+3+1+1+1+1+1=3*4+1*5=17 不含'a'
#7.lpad 用指定的字符实现左填充指定长度(为数值2时显示 my 有
截断的功能)
SELECT LPAD('mysql',10,'*') AS 输出内容;
#执行结果 *****mysql
#8.rpad 用指定的字符实现右填充指定长度(为数值2时还是显示
my 有截断的功能)
SELECT RPAD('mysql',10,'ab') AS 输出内容;
#执行结果 mysqlababa
#9.replace 替换
SELECT
REPLACE('jsjsjs我在学习js', 'js', 'mysql') AS 输出内容
;
#执行结果 mysqlmysqlmysql我在学习mysql
#二.数学函数
#round 四舍五入
SELECT ROUND(1.65);#执行结果 2
SELECT ROUND(1.45);#执行结果 1
/*
注: 这里的四舍五入 是 保留整数
所以小数后一位(后两位三位与这次计算无关) >=5 进1 <5舍去
所以 1.45 是 1 而不是 2 保留1位小数那也是 1.5
如果先保留一位小数 再保留整数进行两次四舍五入 就有可能是2
*/
SELECT ROUND(1.567,2);#执行结果 1.57
#小数点后保留 2位 的 四舍五入
#ceil(释义:屋顶) 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.002);#执行结果 2
SELECT CEIL(-1.002);#执行结果 -1
#floor(释义:地板) 向下取整 返回<=该参数的最大整数
SELECT FLOOR(9.99);#执行结果 9
SELECT FLOOR(-9.99);#执行结果 -10
/*
2 ↑
1 往上
0 大小
-1 往下
-2 ↓
*/
#truncate 保留小数点后几位
SELECT TRUNCATE(1.65,1);#保留小数点后一位 1.6
#mod(a,b)取余/取模(%) a-a/b*b
/*
除号 / 两边如果都是整数 取整
假设 a为10 b为3 10/3 就是3 而不是3.3333
3*b=3*3=9
a-9=10-9=1
*/
SELECT MOD(10,3);#10/3 取余为 1
SELECT 10%3;# 和上面结果是一样的
SELECT MOD(-10,-3);#-1
/*
a-a/b*b
-10-(-10)/(-3)*(-3)
(-10)/(-3) 取整 为3 而不是 3.33
因此
-10-3*(-3)
-10+9
-1
*/
SELECT MOD(10,-3);# 1
SELECT MOD(-10,3);#-1
#三.日期函数
#now 返回当前系统日期+时间
SELECT NOW();# 当前 年-月-日 时-分-秒
#curdate 返回当前系统日期,不包含时间
SELECT CURDATE();# 当前 年-月-日
#curtime 返回当前系统时间 不包含日期
SELECT CURTIME();# 当前 时-分-秒
#可以获取指定的部分,年-月-日 时-分-秒
SELECT YEAR(NOW() ) 年;#执行结果 2021
SELECT YEAR('1998-1-1') 年;#执行结果 1998
SELECT YEAR(hiredate) 年#hiredate 员工的入职时间 年-月-日
时-分-秒
FROM employees;
SELECT MONTH(NOW() ) 年;#执行结果 2(代表月)
SELECT MONTHNAME(NOW() ) 年;#执行结果 February 英文版2(代
表月)
/*
str_to_date 将日期格式的字符 转换成指定格式的日期
str_to_date('9-13-1999','%m-%d-%Y')
date_format:将日期转换成字符
date_format('2018/6/6','%Y年%m月%d日')
%Y 四位年份 2018
%y 两位的年份 18
%m 月份(双) 01,02,...12
%c 月份 1,2,...12
%d 日 01,02,...12,...
%H 小时 24小时制
%h 小时 12小时制
%i 分钟 00,01,...59
%s 秒 00,01,...59
*/
#str_to_date 将日期格式的字符 转换成指定格式的日期
#str_to_date('9-13-1999','%m-%d-%Y')
SELECT STR_TO_DATE('1998-3-2','%Y-%m-%d');
#执行结果 1998-03-02
#查询(hiredate)入职日期为1992-4-3的员工信息
SELECT
*
FROM
employees
WHERE hiredate = '1992-4-3' ;
#如果获得的字符串 时间颠倒 就需要进行
# STR_TO_DATE 转换
SELECT
*
FROM
employees
WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y') ;
#注: 可以顺序颠倒
#date_format:将日期转换成字符 与上面的STR_TO_DATE相反
#date_format('2018/6/6','%Y年%m月%d日')
SELECT
DATE_FORMAT(NOW(),'%y年%m月%d日') AS 输出日期;
#执行结果 21年02月24日
#查询(is not null)有(commission_pct)奖金的(last_name)员工
名和(hiredate)入职日期(xx月/xx日 xx年)
#DATE_FORMAT(日期,要转换的字符串格式(结果是字符串))
#STR_TO_DATE(日期,重排日期(结果非字符串)的顺序)
SELECT
last_name,
DATE_FORMAT(hiredate, '%m月/%d日 %y年') 入职日期
FROM
employees
WHERE commission_pct IS NOT NULL ;
#四. 其它函数
SELECT VERSION();#版本号
SELECT DATABASE();#查看当前的数据库当前数据库 一般指一个
SELECT USER();#查询当前使用mysql服务器所登录的账户
# 五. 流程控制函数
#1.if函数: if else 的效果
SELECT IF(10<5,'大','小');
# ture/false true,false
#(last_name)员工名有(commission_pct)奖金 提示(IS NULL)有(
返回1true) 没有(返回0false)奖金提示没有
SELECT
last_name,
commission_pct,
IF(# if前有逗号 因为它是查询表达式
commission_pct IS NULL,
'true 有null没奖金',
'false没null有奖金'
) 是否有奖金
FROM
employees ;
#2.case函数 的使用一:switch case 的效果
/*
java 中 这样写 case函数
switch(变量或表达式){
case 常量1: 语句1;
break;
...
default:语句n;
break;
}
mysql 中 这样写 case函数
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
}
*/
#案例 : 查询员工的(salary)工资,要求
#(department_id)部门号=30, 显示工资为1.1倍
#部门号=40, 显示工资为1.2倍
#部门号=50, 显示工资为1.3倍
#语句不能 接在select 查询 后面 所以此处暂用 显示的值
#后期通过学习 可以把整个case跟在 select 后面
#
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 新工资 #as 别名
FROM
employees ;
#3.case 函数的使用二:类似于 多重if
/*
java中:if 条件判断的写法
if(条件1){
语句1;
}else if(条件2){
语句2;
}
...
else{
语句n;
}
mysql 中:
case
when 条件1 then 要显示的值1或语句1(如果是语句要加 ;后期会
说到注意点)
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n 或语句n
end
*/
# 案例: 查询员工的工资的情况
#如果工资>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 'C'
END 工资情况#因为此处case作为查询表达式出现所以要加 , 且
尽量取别名
FROM
employees ;
########################################################
##########
#题目1.显示系统时间(注:日期+时间)
SELECT NOW();
#题目2. 查询员(employee_id )工号,(last_name)姓名,(salary)
工资,以及工资提高百分之20%后的结果(new salary)
#
SELECT
employee_id,
last_name,
salary,
salary * 1.2 'new salary' #别名中有空格使用双引号
FROM
employees ;
#题目3.将员工的(last_name)姓名按SUBSTR(last_name,1,1)首字
母(ORDER BY )排序,并写出姓名的(LENGTH(last_name))长度
SELECT
LENGTH(last_name) 长度,
SUBSTR(last_name, 1, 1) 首字符,
last_name
FROM
employees
ORDER BY 首字符 DESC ;
#按 last_name 的首字母排序 其实和 按 last_name 排序是差不
多的
#差别主要体现在 按 last_name 排序 时 首字母相同 会按第二个
字母进行排序
#不写默认升序排序 asc 升(3字母) desc 降(4字母)
#题目4. 做一个查询,产生下面的结果
/*
<last_name>earns<salary> monthly but wants <salary*3>
示例结果:
别名表头 Dream Salary(释义:梦想工资)
内容拼接的 King earns 24000 monthly but wants 72000
*/
SELECT
CONCAT(
last_name,
'earns',
salary,
'monthly but wants',
salary * 3
) AS "Dream Salary"
FROM
employees
WHERE salary = 24000 ;#条目太多 加个筛选条件
#题目5. 使用case-when,按照下面的条件
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
*/
SELECT
last_name,
job_id,
CASE
job_id
WHEN 'AD_PRES'
THEN 'A'
WHEN 'ST_MAN'
THEN 'B'
WHEN 'IT_PROG'
THEN 'C'
WHEN 'SA_REP'
THEN 'D'
WHEN 'ST_CLERK'
THEN 'E'
END AS Grade
FROM
employees
WHERE job_id = 'AD_PRES' ;
# 二.分组函数
/*
功能:用做统计使用,
又称为聚合函数或统计函数或组函数
(传一组值 拿到的是一个值)
分类:
sum 求和.avg 平均值,max最大值 , min最小值,count计算个数
*/
#1,简单 的使用
SELECT
SUM(salary) #这会计算表头salary(工资)下所有的值的总和
FROM
employees ;
SELECT
AVG(salary) #这会计算表头salary(工资)下所有的值中的平均
值
FROM
employees ;
SELECT
MIN(salary) #这会计算表头salary(工资)下所有的值的最小值
FROM
employees ;
SELECT
MAX(salary) #这会计算表头salary(工资)下所有的值的最大值
FROM
employees ;
SELECT
COUNT(salary) #这会计算表头salary(工资)下一共有几个值
FROM
employees ;
SELECT
SUM(salary) 求和, #只支持 number
ROUND(AVG(salary), 2) 平均,#只支持 number
#平均值有小数点 使用(ROUND)保留两位小数
MAX(salary) 最大值,
MIN(salary) 最小值,
COUNT(salary) 总个数 #(非空(非null)的值的个数)
FROM
employees ;
#2.分组函数 特点
#参数支持哪些类型
/*
1.sum.avg一般用于处理数值型
max,min,count可以处理任何类型
2.以上分组函数全部忽略(不计算)null值
sum ,COUNT,AVG,MAX,MIN
3.可以和(distinct)去重 搭配实现去重的运算
*/
SELECT
SUM(DISTINCT salary),
#去重求和
SUM(salary),
#普通求和
COUNT(DISTINCT salary) #去重累计个数 (有几种工资
(salary))
FROM
employees ;
#4.count 函数的单独介绍
/*
*/
SELECT
COUNT(salary)
FROM
employees ;
#经常用来统计一共多少行
#一个表头可能有 null 可能会少统计一行
#所以统计所有表头 除非有一行全部为null
#否则不会漏行
SELECT
COUNT(*) #所有表头的 所有非空(null)字段 都统计上(总行数
)
FROM
employees ;
SELECT
COUNT('kk')
#相当于额外加了个表头为1(任意字符串均可只要创造了新的列)
的列
# 这列的总行数就是表的总行数
FROM
employees ;
/*
效率
innodb (默认存储引擎)
(count(*)和count(1)差不多)
但是比count(字符串)要高一些
5.5版本之前默认的版本是 myisam(count(*)效率高)
因为其内部有一个计数器它直接就返回了个数
综上所述不管哪个存储引擎下
count(*)用的比较多
案例"查询每个部门的员工个数"
id 名字 部门
1 xx 10 1组 每一组员工出现的个数有几行就
是每组员工有几个
2 dd 20 2组 所以就是统计他的行数
3 mm 20 2组 所以用count(*)就行
4 aa 40 3组 为什么呢
5 hh 40 3组 因为经过 where等 筛选之后 的
结果集
正好是我们想要的行数
#5.和分组函数一同查询的字段要求是group by(等会要讲的)后
的字段
AVG(得出一组数据的平均值)
employee_id(员工编号)
*/
SELECT #查询结果出来 必须是一个规则的表格
AVG(salary),#这家伙只有一行
employee_id #就算这家伙有很多行 导致查询结果也只显示一行
FROM
employees ;
#题目:查询公司员工(salary)工资的(max)最大值,(min)最小值,
(AVG)平均值,(sum)总和
SELECT
MAX(salary) 工资的最大值,
MIN(salary) 工资的最小值,
ROUND(AVG(salary), 2) 工资的平均值,
#(保留两位小数)
SUM(salary) 工资的总和
FROM
employees ;
#2.查询员工表中的最大(hiredate)入职时间和最小入职时间的相
差的天数
#并且起一个别名叫(DIFFRENCE)
#datediff('2017-10-1','2017-9-29');
#用来计算日期的新函数
# 执行结果 前面日期 - 后面日期 得到的天数
SELECT
DATEDIFF(MAX(hiredate), MIN(hiredate)) DIFFRENCE
FROM
employees ;
#3.查询部门编号为90的员工个数
SELECT
COUNT(*) 部门编号为90的员工个数
FROM
employees
WHERE department_id = 90 ;
#进阶5:分组查询
#引入:查询每个部门的(AVG)平均(salary)工资
SELECT
AVG(salary)
FROM
employees ;
#所谓分组查询 就是将2车间(总表)
#总装的平均工资(支表)
#初权的平均工资(支表)
#包纸的平均工资(支表)
#拆装的平均工资(支表)
#使用 GROUP BY 子句将表中的数据分成若干组
/* 结构示例:
select column,group_function(column)
from table
[where condition]
[GROUP BY group_by_expression]
[ORDER BY column];
语法示例:
select 分组函数,列(要求出现在group by的后面) 第5步
from 表 第1步
[where 筛选条件] 第2步
group by 分组的列表 第3步
[having 分组后的筛选] 第4步
[order by 子句] 第6步
注意:
查询列表比较特殊,要求是分组函数和group by后出现的字段
*/
#引入:查询平均工资(不分部门)
SELECT
AVG(salary)
FROM
employees ;
#简单的分组查询
#案例1 :查询每个(job_id)工种的最高工资
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY job_id ;
#案例2:查询每个(location_id)位置编号上的(departments)部门
(COUNT(*))个数
SELECT
COUNT(*),
location_id
FROM
departments #部门表 之前查询的一直是员工表employees
GROUP BY location_id ;
#代表所有的部门信息
#(department_name部门名称) 装配车间
#(location_id位置编号) 2
#这个表是总表(`myemployees`)的子表
#与前面一直用的`employees` 同级
#添加筛选条件
#案例1: 查询(email)邮箱中(LIKE)包含a字符的,每个
(department_id)部门的平均工资
SELECT
AVG(salary),
department_id
FROM
employees #员工表
WHERE email LIKE '%a%'
GROUP BY department_id ;
#案例2: 查询(IS NOT NULL)有(commission_pct)奖金的每个
(manager_id)领导手下员工的最高(salary)工资
SELECT
MAX(salary),
manager_id #此处与GROUP BY 呼应 对应后为分组查询
#除了表头是 manager_id 外
#表头下 每一行都是一组
#manager_id salary 都在一个表中 并没有联合查询
FROM
employees #查询的员工表
WHERE commission_pct IS NOT NULL
GROUP BY manager_id ;
#添加复杂的筛选条件
#案例1:查询那个部门的员工个数>2
#1.查询每个(employees)部门的(department_id)员工(count(*))
个数
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_id ;
#2.根据 #1 的结果筛选, 查询哪个部门的员工个数大于2
/*
我们需要筛选出COUNT(*)>2的
但是我们在from 后 group by 前加入 where COUNT(*)>2 筛选条
件时
由于 表结构不一致一边多一边少 会报错
这里我们在最后使用一个新的关键词
having COUNT(*)>2
having #分组后查询
where #分组前查询
示例如下
*/
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_id
HAVING COUNT(*) > 2 ;
#显示出以COUNT(*)>2为主 偏少的一列的表结构
#舍弃 偏多的表结构的行数
/*
我们再做几个案例进行一下巩固
#案例2:查询每个工种有奖金的员工
的最高工资>12000
的(job_id)工种编号和 MAX最高(salary)工资
(像这种条件(这里是分组条件查询较多,分成两步)比较多的
我们一般应该分步来做更方便一点)
*/
#第一步.查询每个(job_id)工种(IS NOT NULL)有
(commission_pct)奖金的员工的(MAX)最高(salary)工资
SELECT
MAX(salary),
job_id
FROM
employees
WHERE commission_pct IS NOT NULL #GROUP BY分组前筛选
GROUP BY job_id ;
#第二步.根据第一步的结果继续筛选
#(MAX)最高(salary)工资>12000
#此筛选必定为分组后筛选(其实根据语义理解很容易判断出来)
SELECT
MAX(salary),
job_id
FROM
employees
WHERE commission_pct IS NOT NULL #GROUP BY分组前筛选
GROUP BY job_id
HAVING MAX(salary) > 12000 ;#分组后筛选
#案例3:查询领导编号>102的每个领导手下的最低工资>5000的
#查询条件较多(较绕的)分步进行更简单(此处分三步)
# 第一步.查询(manager_id)领导编号>102的每个领导手下的
(MIN)最低(salary)工资
SELECT
MIN(salary),
manager_id
FROM
employees #员工表
GROUP BY manager_id ;
#按(manager_id)领导编号分组(或许解释为
#表头下相同的 行合并成一行也不错?
#与之对应的 表其它行使用分组函数(处理一组数据变为1个数据)
#成为新的一行 1个数据 合并的行 )
#第二步 添加筛选条件:(manager_id)领导编号>102
SELECT
MIN(salary),
manager_id
FROM
employees #员工表
WHERE manager_id > 102
#简单准确辨别 分组前 分组后 查询的方法
#因为(manager_id)领导编号在employees #员工表里
#所以是分组前查询 在此处使用 WHERE 筛选
GROUP BY manager_id ;
#如果(manager_id)领导编号不在employees #员工表里
#则是分组后查询 在此处使用 HAVING 筛选
#此方法不准确
#比如下面 最低工资是在employees #员工表里
#但使用的是 分组后查询 HAVING
#还是要根据语义来判断
#第三步 添加筛选条件:(min)最低(selary)工资>5000
SELECT
MIN(salary),
manager_id
FROM
employees #员工表
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000 ;
/*
此处接上文
注意:
查询列表比较特殊,要求是分组函数和group by后出现的字段
特点
总结:
1.分组查询中的筛选条件分为两类
数据源不一样 位置也不一样 关
键字也不一样
分组前筛选 原始表 GROUP BY子句前
where
分组后筛选 分组后的结果集 GROUP BY子句后
having
1.1 分组后筛选 判断法
当需要使用 (GROUP BY)分组函数后的结果 做条件
肯定是 分组后筛选 放在having子句中
(这... 感觉总结了一句废话)
1.2 特殊情况
如果筛选的是 GROUP BY 这里
且并不需要使用 (GROUP BY)分组函数后的结果 做条件
那么 这里 先筛选 和后筛选 没有区别(结果是一样的)
能分组前筛选的,就优先考虑使用分组前筛选
1.3
GROUP BY manager_id(在此以前的案例 放的都是单个的字段)
实际上也支持 按表达式 或函数分组
*/
#案例:按员工姓名的长度分组,查询每一组员工的个数,筛选员工个
数大于5的有哪些
#(比较绕 建议分步 此处分为两步)
#第一步查询每种(LENGTH)长度(指(last_name)名字的长度)的员
工个数
SELECT
COUNT(*),
LENGTH(last_name) 有几种员工的名字长度
FROM
employees
GROUP BY LENGTH(last_name)
#第二步添加筛选条件 员工个数大于5
HAVING COUNT(*) > 5 ;
#如果都使用别名 当变量 修改如下
SELECT
COUNT(*) 员工个数,
LENGTH(last_name) 有几种员工的名字长度
FROM
employees
GROUP BY 有几种员工的名字长度
#第二步添加筛选条件 员工个数大于5
HAVING 员工个数 > 5 ;
#按多个字段分组 (展示多个分组)
#案例:查询各(department_id)部门各(job_id)工种所有员工的
(avg)平均(salary)工资
#按工种展示? 还是 按部门分组展示
#按工种展示 部门也展示不过有重复 加上平均工资共 3个表头
SELECT
AVG(salary),
department_id,
job_id
FROM
employees #员工表
GROUP BY department_id,
job_id ;
# 在此之上追加排序功能
#按平均工资高低显示
SELECT
AVG(salary),
department_id,
job_id
FROM
employees #员工表
WHERE department_id IS NOT NULL#筛选 部门不为null的
GROUP BY department_id,
job_id
ORDER BY AVG(salary) DESC ;#默认升序asc 降序为desc
#ORDER BY 后可跟别名变量(好像就没有不能使用别名变量的吧)
/*
特点
总结:
2.group by子句支持单个字段/多个字段/函数/表达式
3.也可以添加排序(ORDER BY放在GROUP BY后)
*/
#题目1.查询(job_id)工种的员工(salary)工资的(max)最大值,
(min)最小值,(avg)平均值.(sum)总和并按job_id排序
SELECT
MAX(salary) 最大值,
MIN(salary) 最小值,
AVG(salary) 平均值,
SUM(salary) 求和,
job_id 工种
FROM
employees
GROUP BY job_id
ORDER BY 工种 ;#默认升序asc 降序为desc
#题目2.查询员工最高工资和最低工资的差距(DIFFERENCE)
#DIFFERENCE即是计算日期差距函数 释义亦是 差距的意思
SELECT
MAX(salary) - MIN(salary) DIFFERENCE
FROM
employees ;
#题目3.查询各管理者手下员工的最低工资,其中最低工资不能低于
6000
#没有管理者的员工不计算在内
SELECT #查询
MIN(salary),#最低工资
manager_id #管理者
FROM#数据来自
employees #员工表
WHERE manager_id IS NOT NULL #没有管理者的员工不计算在内
GROUP BY manager_id #各管理者
HAVING MIN(salary) >= 6000 ;#其中最低工资不能低于6000
#题目4. 查询部门所有部门的(department_id)编号,员工(COUNT
(*))数量和工资平均值
#并按平均工资降序
SELECT
department_id,
COUNT(*),
AVG(salary)
FROM
employees
GROUP BY department_id #以部门为主 各部门员工数量为COUNT
(*)
ORDER BY AVG(salary) ;
#题目5.查询各个(job_id)工种的员工人数
SELECT
COUNT(*) 员工个数,
job_id 工种
FROM
employees
GROUP BY 工种 ;
#进阶6:连接查询
/*
含义:又称多表查询,当查询的字段来自于多个表时
就会用到
*/
SELECT * FROM beauty ;
#女生表
SELECT * FROM boys ;
#男生表
#迪卡尔集的错误情况:
SELECT
NAME,boyname
FROM
boys,beauty ;
/*
迪卡尔集的错误情况:
select count(*) from beauty;
假设输出12行
select count(*) from boys;
假设输出4行
最终结果 为12*4=48行
第一列的每一行
会匹配完第二列的每一行
每匹配一行都算新的一行
所以有 48行
此现象 为 迪卡尔乘积现象
发生原因:没有有效的链接条件
如何避免: 添加上有效的连接条件
怎么添加呢
我们链接查询有一个分类
按年代分类(不同的年代sql语言推出了不同的标
准)
sql 92标准(1992年推出的标准) 不推荐
仅仅支持 内连接 外连接(部分)
sql 99标准(1999年推出的标准) 推荐
支持内连接 + 外连接(左外右外) + 交叉连接
也就是说仅仅不支持 全外
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉链接
(就只有交叉连接)
*/
SELECT
NAME,
boyname
FROM
boys,
beauty
WHERE beauty.boyfriend_id = boys.id ;
#一.sql 92 标准 仅仅支持 内连接 外连接(部分)
#1.等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]
特点:
/*
1.1 多表等值连接的结果为多表的交集部分
1.2 n表链接,至少需要n-1个链接条件
1.3 多表的顺序没有要求
1.4 一般需要为表起别名
1.5 可以搭配前面介绍的所有子句使用,
比如排序,分组,筛选
*/
#案例1:查询女神名和对应的男神名
SELECT
NAME,
boyname
FROM
boys,
beauty
WHERE beauty.boyfriend_id = boys.id ;
#案例2:查询员工名和对应的部门名
#员工名 来自于 employees 员工表的 last_name
#部门名 来自于 departments 部门表的 department_name
#连接 员工表 和 部门表 的 是 department_id
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE employees.`department_id` =
departments.department_id ;
#`是着重号 并不是单引号 着重号是双击左边表自动添加的产生的
#2. 可以为 from 表名(以前一直是表头)起别名
#查询 (last_name)员工名 (job_id)工种号 (job_title)工种名
#员工名 来自于 employees 员工表的 last_name
#工种号 来自于 employees 员工表的 job_id
#工种名 来自于 jobs 工种表的 job_title
#工种代号 与 工种名 分开存放的形式
#节省了字节空间(省下了重复工种名的空间字节数)
SELECT
last_name,
e.job_id,#两个表中都有job_id 需要区分
job_title
FROM
jobs j,
employees e
WHERE e.job_id = j.job_id ;
#当对form 后的表名 使用了别名时
#后面的WHERE子句必须使用别名(否则报错)
/*
提高语句的简洁度
区分多个重名的字段(一般指表头)
*/
#3.form 后面两个表 可以调换顺序吗
#可以上面已经调换了
#4.加筛选 用 因为链接俩表时使用了where 再加AND就可以了
#案例:查询有*(commission_pct)奖金的(last_name)员工名
(department_name)部门名
SELECT
last_name,
department_name ,
commission_pct
FROM
employees e,
departments d #为表起别名 后只能用别名
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL ;
#案例2:查询城市名第二个字符为 o 的部门名和城市名
#城市名 来自于 locations 位置表的 city
#部门名 来自于 departments 部门表的 department_name
#城市与部门俩表之间使用 location_id 连接
#其中一张表 使用的location_id(只有代号) 与
#另一张表橙色钥匙使用的 location_id 相对应 中文名字
#并且橙色钥匙使用的 location_id (也含有代号)
SELECT
department_name,
city
FROM
departments d,
locations l #为表起别名 后只能用别名
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%'; #城市名第二个字符为 o
#5.加分组 GROUP BY
#案例1:查询 各个城市 的部门个数(COUNT(*))
#城市名 来自于 locations 位置表的 city
#部门名 来自于 departments 部门表的 department_name
#链接 两张表的是 城市名对应的代号 location_id
SELECT
COUNT(*) 个数,
l.city
FROM
departments d,
locations l
WHERE l.`location_id`=d.`location_id`
GROUP BY l.city ;#按城市分组
#案例2:查询部门表中有奖金的
#的部门名称 department_name 和
#部门表的 领导编号和 `manager_id`
#该部门中员工的最低工资 MIN(salary)
#部门名/领导编号 来自部门表 `departments`
#工资 来自于 员工表 `employees`
SELECT
department_name,#部门名
d.manager_id,#部门表里的领导编号
MIN(salary) #部门中最低工资为
#sum(salary)
FROM
employees e,#员工表
departments d #部门表
WHERE d.`department_id` = e.`department_id` #部门代号对
应
# AND commission_pct IS NOT NULL #有奖金的部门只有一个
GROUP BY department_name;#部门名
#6. 加排序
#筛选+where链接查询/筛选+分组函数+having筛选 后面还可以 加
排序
#案例:查询工种表中不同工种的员工数量
#并且按每组的员工数量 降序
#链接条件 员工数量 来自于 employees e,#员工表
#工种名称 来自于 jobs j #工种表
SELECT
job_title,#工种名称
COUNT(*) #计算每个分组的数量,没有分组计算全部
FROM
employees e,#员工表
jobs j #工种表
WHERE e.`job_id`=j.`job_id`#只能通过`job_id`链接
#主要应该一般是按啥分组 就用啥链接
GROUP BY e.job_id
#这里按工种(job_id)分组 id是代号
#我们使用工种表 对应的 job_title 中文名称
#使用job_id也没关系 显示的依旧是 查询的job_title
#因为 job_id 与 job_title 有一一对应关系
#它俩是一样的
ORDER BY COUNT(*) DESC #排序 升3asc(不写默认) 降4desc
;
#7. 链接超过两表
#WHERE 表1.k_id=表2.k_id 中再使用 and 表2.j_id=表3.j_id
#案例: 查询员工表中每位员工的部门名和城市名
SELECT
last_name,#员工名来自员工表
department_name,#部门名来自部门表
city #城市名来自于位置表
FROM
employees e,#员工表
`departments` d,#部门表
`locations` l #位置表
WHERE e.`department_id`=d.department_id
AND d.`location_id`=l.`location_id`;
#2,非等值连接
/*
在等值链接的基础上
select 查询列表
from 表1 别名,表2 别名
{where 表1.key=表2.key} -> 此行更改为{where 非等
值的连接条件}
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]
*/
#案例1:查询出员工的工资和工资级别
SELECT
*
FROM
job_grades ;#工资等级表
/*
lowest_sal highest_sal grade_level
1000 -2999 A
3000 -5999 B
6000 -9999 C
10000 -14999 D
15000 -24999 E
25000 -40000 F
*/
#题目:查询出员工表中每位员工的工资和
#(job_grades)等级表的工资等级
SELECT
salary,#工资
grade_level #工资等级
FROM
employees e,#员工表
job_grades g #工资级别表
WHERE salary BETWEEN g.`lowest_sal`
AND g.`highest_sal` ;
#员工表中每位员工的工资 在工资级别表中
# 大于最低(lowest_sal) 小于最高(highest_sal)
#的工资级别为其最低最高区间所对应的 (grade_level)等级
#追加功能 我只想看 工资级别为A的
SELECT
salary,#工资
grade_level #工资等级
FROM
employees e,#员工表
job_grades g #工资级别表
WHERE salary BETWEEN g.`lowest_sal`
AND g.`highest_sal` AND g.grade_level='A';
#后面亦可加 排序功能 (就没遇到不能的)
#3.自链接(自己链接自己)
/*
语法:
在等值列表的基础上
select 查询列表
{from 表1 别名,表2 别名} -> 此行更改为{from 表 别
名1,表 别名2}
{where 表1.key=表2.key} -> 此行更改为{where 别名
1.key1=别名2.key2}
[and 筛选条件]
[group by 分组字段]
[having 分组后的筛选]
[order by 排序字段]
*/
#案例:查询员工表中 员工名称 以及 其上级名称
#由于员工表中的员工id(上级领导也算员工 需要分开使用)
#其实相当于一张表里放了 两张表 但是属性共通了某一列
SELECT
e.last_name,#员工表头的 员工名称
e.`employee_id`,#员工表头中的 员工id
m.last_name,#领导表头的 员工名称
m.`employee_id` #领导表头中的 领导id
FROM
`employees` e,#员工表(剥离出员工表头 与其员工id)
`employees` m #员工表(剥离出领导表头 与其员工id)
WHERE e.manager_id = m.employee_id ;
#题目:1.显示员工表中员工的最大工资 工资平均值
SELECT MAX(salary),AVG(salary)
FROM employees;
#题目:2.查询员工表的 employee_id,job_id,last_name,
#按 department_id 降序后,当department_id相同,再按salary升
序
SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_id DESC,SALARY;
#升3asc(默认) 降4desc
#题目3.查询员工表的job_id中包含 a和e的,并且a在e的前面
SELECT job_id
FROM employees
WHERE job_id LIKE '%a%e%';
#注意 LIKE后面不跟= 我竟然写了等于...
#题目4.已知表 student ,里面有 id(学号),name(姓名) grade(
年级编号)
#已知表 grade ,里面有id(年级编号),name(年级名)
#已知表 result , 里面有 score(成绩),studentNo(学号)
#要求查询姓名,年级名,成绩
/*
select s.name,g.name,score
from
student s,grade g,result r
where s.id = r.studentNo
and g.id = s.gradeid
*/
#题目5.显示当前日期,以及去前后空格,截取子字符串的函数
/*
select now();
select trim(要去的字符 from '');不写默认去空格字符
select substr(str,1,2);1开始(mysql中下标从1开始) 截取两位
*/
SELECT SUBSTR( TRIM( NOW()),1,9);
#题目1.显示所有员工的姓名,部门号和部门名称.
USE myemployees; #使用myemployees 大表作为数据库
SELECT
last_name,
d.department_id,
department_name
FROM
employees e,
departments d
WHERE e.department_id = d.department_id ;
#题目2.查询员工表中员工id为103的员工的(job_id)工种和
#员工表中员工id为103的员工的部门id(department_id) 相通的
#部门表的 location_id
SELECT employee_id,
job_id,#来自员工表的 (job_id)工种
location_id #来自部门表的 (location_id)位置编号
FROM
employees e,#员工表
`departments` d#部门表
WHERE e.`department_id` = d.department_id
#来自员工表, 部门表 相通的 department_id部门编号
AND `employee_id`=103;
#题目3. 查询所有员工中有奖金的人其所在的
#(department_name)部门名称
#,location_id,city位置编号
#以及其(last_name),名字
SELECT
last_name,#来自于员工表 姓名
department_name,#来自于部门表 部门名称
d.location_id,#来自于部门表/位置表 均可 位置编号
city #来自于位置表
FROM
employees e,#员工表
`departments` d,#部门表
`locations` l #位置表
WHERE `commission_pct` IS NOT NULL
#筛选出 (不为null)有奖金
AND e.`department_id`=d.`department_id`
#用于匹配 有奖金员工的部门名称
AND d.`location_id`=l.`location_id`;
#用于确认 有奖金员工的位置编号
#题目4.查询 city 在 toronto 工作的员工的
#last_name,`job_id`,`department_id`,来自员工表
#`department_name`来自部门表
SELECT
last_name,
`job_id`,
d.`department_id`,
department_name,
city ,#来自于位置表
d.`location_id` #部门表与 位置表通过location_id相连
FROM
`departments` d,
#部门表
`employees` e,
#员工表
`locations` l #位置表
WHERE d.`location_id` = l.`location_id` #确认部门所在
city位置
AND d.`department_id` = e.`department_id` #确认员工所
在部门
AND city LIKE 'toronto' ;
#筛选出 city 为 toronto 的
#题目5.查询每个工种,每个部门的部门名,工种名和每个工种最低
工资
#工种id`job_id` 来自于员工表`employees`/工种表
#工种名(job_title) 来自于 工种表 jobs
#部门名(`department_name`)来自于 部门表`departments`
#工资(salary) 来自于部门表`employees`
SELECT e.job_id,job_title,department_name,MIN
(salary),COUNT(*)
FROM employees e,jobs j,departments d
WHERE j.job_id=e.job_id#匹配员工所在工种
AND d.`department_id` = e.`department_id` #确认员工所在
部门
#链接整合数据(3表相关联)
GROUP BY e.job_id;
#题目6.查询 部门个数 大于2的 国家(这里没有国家名 只有编号
名)
#国家 来自 位置表`locations` 的国家编号`country_id`
#部门 来自 部门表`departments`的 部门编号`department_id`/
部门名称`department_name`
#共两表相连 通过 `location_id`位置编号
SELECT country_id,COUNT(*) 部门个数
FROM
locations l,#位置表
departments d#部门表
WHERE l.location_id=d.location_id
#将两表链接
GROUP BY country_id
#按国家分组
HAVING 部门个数>2
#分组后筛选 HAVING 也支持别名(好像就没有不支持别名的)
;
#题目7.查询 员工的姓名,员工号,以及它的管理者的姓名和员工号
#结果示例
/*
表头的别名 employees Emp# manager Mgr#
查询的结果 kochhar 101 king 100
*/
#不管是 管理者/员工 的姓名`last_name`和员工号
`employee_id`
#都是出自员工表 employees
#因此这是 自连接 共两张表(两张都是自己)
#注意 两张表 分别取别名 当做两张不同的表
SELECT e.`last_name` employees,e.`employee_id`
'Emp#',m.`last_name` manager,m.`employee_id` 'Mgr#'
FROM
employees e,#员工表副本1
employees m#员工表副本2
WHERE e.`manager_id`=m.`manager_id`
#通过 管理者`manager_id`使两表连接
;
#进阶6连接查询之 二sql 99语法
#二.sql 99 语法
/*
其中包含
内连接(重要) [连接类型]: inner
(等值连接,非等值连接,自连接连接)
外连接
左外(重要) [连接类型]: left [outer]
右外(重要) [连接类型]: right [outer]
全外 [连接类型]: full [outer]
交叉连接 cross join
语法:
select 查询列表
from 表1 别名
[连接类型(不写默认inner内连)] join 表2 别名
on 连接条件
#(92中用的是 where 容易混淆
#这里单独用on 提高了可读性)
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序]
[limit 子句]
*/
#一.内连接 (等值连接,非等值连接,自连接连接)
/*
语法
select 查询列表
from 表1 别名
inner(默认) join 表2 别名
on 连接条件;
后面可继续跟 分组 筛选 排序等
分类
等值连接
特点
1.也可以添加排序,分组,筛选
2.inner可以省略
3.筛选条件放在where后面,连接条件放在on后面,提高可
读性
4.inner join连接和sql92语法中的等值连接效果是一样
的
非等值连接
自连接连接
*/
#1.等值连接
#题目1.查询员工名的部门名
#员工名`last_name` 来自于`employees`员工表
#部门名 `department_name` 来自于`departments`部门表
#两者以`department_id`部门编号 相连
#现在的sql(99)语法
SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
ON e.department_id=d.department_id
;
#原来的sql(92)语法
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
;
#案例2.查询名字中包含e的员工名和工种名(添加筛选)
#员工名 来自于员工表`employees`的`last_name`
#工种名 来自于工种表`jobs` 的`job_title`
#共两表 以`job_id`相连
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j#连接表2(inner)可以省略
ON e.job_id=j.job_id#连接条件
WHERE last_name LIKE '%e%';
#这里WHERE仅有筛选的功能
#案例3.查询部门个数>3的城市名和部门个数,
#(添加分组+筛选)
#部门个数 来自于 departments 部门表的
#部门名称`department_name`/部门编号`department_id`的
COUNT(*)
#城市名 来自于位置表`location_id`的 city
#两表连接条件 为`location_id`
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN `locations` l#连接表2(inner)可以省略
ON d.location_id=l.location_id#连接条件
GROUP BY city#按城市分组 显示城市的部门个数
HAVING 部门个数>3;
#案例4.查询哪个部门的部门员工>3的部门名和员工个数
#并按个数降序(添加排序)
#员工个数 来自于 demployees 员工表的
#员工名称`last_name`/员工编号`employee_id`的COUNT(*)
#部门名 来自于 `departments`部门表的 `department_name`
#按部门 分组 部门名称`department_name`/部门编号
`department_id`
#共两表 连接条件为 `department_id`
SELECT COUNT(*) 员工个数,department_name
FROM `employees` e
INNER JOIN departments d#连接 表2(inner)可以省略
ON e.department_id=d.department_id#连接条件
GROUP BY department_name#按部门分组 显示部门的员工个数
HAVING 员工个数>3
ORDER BY 员工个数 DESC;#升3 asc(默认) 降4 desc
#案例5.查询员工名的部门名和工种名,并按部门名降序
#员工名 来自于`employees`员工表的`last_name`
#部门名 来自于`departments`部门表的`department_name`
#工种名 来自于`jobs`工种表的`job_title`
#累计 3表连接
#员工表 与 部门表的 连接条件为 `department_id`
#员工表 与 工种表的 连接条件为 `job_id`
SELECT last_name,department_name,job_title
FROM employees e#这表作为连接另外两表的中间层
#不能写在最后面 否则 1表2表没有关系会连接失败
INNER JOIN departments d#连接 表2(inner)可以省略
ON e.department_id=d.department_id#连接条件
INNER JOIN jobs j#连接 表3 (inner)可以省略
ON e.job_id=j.job_id#连接条件
ORDER BY department_name DESC;#升3 asc(默认) 降4 desc
#二.非等值连接
#题目1.查询员工的工资级别
#员工工资 来自于 employees 员工表的 salary
#工资级别 来自于 job_grades 工资等级表的 `grade_level`
#共两表连接 连接类型 为 非等值
#连接条件 通过 工资区间 判断 得到相应的等级
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND
g.`highest_sal`;
#通过 工资区间 判断 得到相应的等级
#题目2.按工资级别分组并筛选出其组内元素大于2的,并按工资级
别降序排序
#工资级别 来自于 job_grades 工资等级表的 `grade_level`
#员工个数 来自于`employees`员工表的
#`employee_id`员工编号/`last_name`员工名字
#累计出的 count(*) 员工个数
#共两表连接 连接类型 为 s非等值
#连接条件 通过 工资区间 判断 得到相应的等级
#按工资级别 grade_level 分组
SELECT grade_level,COUNT(*) 员工个数
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING 员工个数>20
ORDER BY grade_level DESC;#升3 asc(默认) 降4 desc
#三.自连接
#查询员工的名字和他上级的名字
SELECT
e.last_name,e.`manager_id`,m.last_name,m.manager_id
FROM `employees` e
JOIN employees m
ON e.manager_id=m.`employee_id`;
#查询姓名中包含字符k的员工的名字和他上级的名字
SELECT
e.last_name,e.`manager_id`,m.last_name,m.manager_id
FROM `employees` e
JOIN employees m
ON e.manager_id=m.`employee_id`
WHERE e.last_name LIKE '%k%'
;
#二.外连接
/*
应用场景:用于查询一个表中有,另一个表没有的记录
特点:
1.外连接的查询结果为 主表 依次逐行 匹配 从表
如果从表中有和它匹配的,则显示匹配的值
如果从表中匹配不到的,则使用null作为匹配的值
外连接查询结果=内连接结果+主表中有而从表没有的记录
2.左外连接,left join左边的是主表
右外连接,right join 右边的是主表
3.左外 和 右外 其实就是
交换两个表的顺序,可以实现同样的效果
内连接(指 等值/非等值/自连接)
4.全外连接=内连接的结果+ full join 两边都是可以是主表
表1中有单表2中没有的+
表2中有但表1没有的
*/
#引入:查询没有男朋友的女神名字
SELECT * FROM beauty;
SELECT * FROM `boys`;
SELECT * FROM `admin`;
#以下是左外连接
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
#带小黄钥匙的绝对有值且不为 null
UPDATE boys SET usercp = NULL WHERE id=3;
#修改 boys 表 id=3(黄晓明) 的usercp值为NULL
#以下是右外连接 和左外连接一样的结果 操作上调换了主从表的
顺序
SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
#假如我还用左外连接 但是对调了主从表的顺序
SELECT b.*,bo.*
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`;
#变成了查询没有女朋友的男神
#左外连接就是查询 没有雪糕的小朋友
#是个排查系统
#案例1:查询那个部门没有员工
#使用左外 答题
SELECT d.*,e.employee_id
FROM departments d#部门表
LEFT OUTER JOIN employees e#左外连接 主表在 lefr 左边
ON d.`department_id`=e.`department_id`#连接条件
WHERE e.`employee_id` IS NULL;
#使用右外 答题
SELECT d.*,e.employee_id
FROM employees e#部门表
RIGHT OUTER JOIN departments d#右外连接 主表在 RIGHT 右边
ON d.`department_id`=e.`department_id`#连接条件
WHERE e.`employee_id` IS NULL;
#全外(当前版本不支持)
USE girls;#使用girls大表
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`;
#查询结果为两表全部对应出现
#未出现的 两表均会使用 null 匹配
#不分主从表 因此没有顺序
#交叉连接(也无先后顺序关系 指from 表) 造成迪卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
#sql 92语法中用的 , 号隔开造成了迪卡尔乘积
#sql 99语法(也就是当前这个例子)
#使用 CROSS JOIN 代替 , 号造成迪卡尔乘积
#所有(包括92/99语法)的连接到此介绍完毕
#建议使用后面学的 99 语法
#功能: sql 99支持的较多
#可读性:sql 99实现连接条件和筛选条件的分离,可读性较高
#题目1.查询编号>3的女神的男朋友信息,如果有则列出详细,
#如果没有,用null填充
#编号来自于 `beauty`女神表的`id`
#男朋友信息来自于 `boyName`
#共两表 两表使用外连接的左外 连接
#链接条件为 `beauty.boyfriend_id`
#与 boys.`id` 连接
SELECT *
FROM
beauty b
LEFT JOIN `boys` bo #outer 可省略
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3
AND bo.boyName IS NOT NULL;
#二.查询那个城市没有部门
#城市来自于 `locations`位置表的city
#部门来自于 `departments` 部门表的`department_name`
#共两表连接 为 外连接 左外连接(城市在连接左边)
#连接条件为
departments.`location_id`=`locations`.`location_id`
SELECT city,d.*# d.* 表后面还可以跟*号
FROM locations l
LEFT JOIN departments d
ON d.`location_id`=l.`location_id`
WHERE d.`location_id` IS NULL;
#三,查询部门名为 sal 或 it 的员工信息
#部门名来自 `departments`部门表的`department_name`
#员工信息来自 `employees`员工表的 *
#共两个连接 为外连接的左外连接(部门表在左边)
# 连接条件为
`departments`.`department_id`=`employees`.`department_id
`
#附加操作为 筛选字段
SELECT department_name,e.*
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE department_name='sal'
OR department_name='it';
#进阶7:子查询(相比前面有些难度)
/*
含义:
出现在其他语句中的select语句,称为子查询或内查询
select 不仅仅如我们之前所见 他还可以使用在别的地方
外部的语句可以是
我们尚未学过的 insert update delete select(较多)等
外部的查询语句, 称为 主查询 或 外查询
概念:出现在其他语句内部的select语句,称为子查询或内查询
内部嵌套其他select语句的查询称为外查询或主查询
示例:
select first_name from employees where
department_id in(#查询条件内又用到了一个查询语句
#括号里面(这里)是一个完整的select 它单独就可执行
select depqrtment_id from departments
where location_id=1700
)
如何分类?
1.按子查询出现的位置:(主要按这种分类来讲解)
① select 后面:
仅支持标量子查询(结果集只有一行一列)
② from 后面
支持表子查询(结果集 只要是个查询结果就行 )
③ where 或 having 后面(重点讲它)
支持 标量/单行 子查询(结果集只有一行一列)
支持 列子查询(结果集只有多行一列)
(使用较少的)
支持 行子查询(结果集有 多行多列)
④ exists (未学 释义:存在与否,相关子查询) 后面
支持 所有 子查询(结果集 只要是个查询结果就
行 )
2.按结果集的行列数不同:
①常用 标量/单行 子查询(结果集只有一行一列)
②常用 列子查询(结果集只有一列多行)
③行子查询(结果集有 多/一 行多列)
④表/嵌套 子查询(结果集 只要是个查询结果就行 )
*/
#示例1.where或having后面 包含3种查询方法
#1.标量 子查询(单行子查询)
#2.列 子查询(多行子查询)
#3.行 子查询(多列多行)
/*
特点:
1.子查询都会放在小括号内
2.子查询一般放在条件的右侧
3.标量 子查询,一般搭配着单行操作符使用
(单行操作符:> < = >= <= <>(不等于))
列子查询,一般搭配着多行操作符使用
(多行操作符 IN,any/some,all )
4.子查询 的执行优先于 主查询的执行,
主查询的条件用到了子查询的结果
*/
#1.标量 子查询
#1.查询 Abel 的工资
SELECT salary
FROM employees
WHERE last_name='Abel';
#2查询员工的信息,满足 salary > Abel 的工资
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
#案例2 筛选后面的标量子查询:返回job_id与141号员工相
同,salary比143号员工多的员工
#姓名,job_id和工资
#第一步 查询 141 号员工的 job_id
SELECT job_id
FROM employees
WHERE employee_id=141;
#第二步 查询 143 号员工的 salary
SELECT salary
FROM employees
WHERE employee_id=143;
#第三步 查询 姓名,job_id 和 (salary)工资
#要求 job_id= 第一步中141 号员工的 job_id
#要求 salary> 第二步中143 号员工的 salary
SELECT last_name,job_id, salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);
#案例3:返回公司工资最少的员工的last_name,job_id和saary
#第一步查询公司的最低工资
SELECT MIN(salary)
FROM employees;
#第二步查询员工的last_name,job_id 和 salary,
#要求salary=第一步的查询结果
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例4:查询最低工资 大于50号部门最低工资的部门的部门id和该
部门最低工资(第二低)
#第一步,查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id =50;
#第二步查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY `department_id`;
#第三步 在第二步结果中筛选 满足 min(salary) >第一步结果
(2100$)
#的部门id和该部门最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY `department_id`
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id =50
);
#非法使用子查询的情况
SELECT department_id,MIN(salary)
FROM employees
GROUP BY `department_id`
HAVING MIN(salary)>(# > < 这种单行操作符里面只能搭配标量
子查询
SELECT MIN(salary)
#此处改为SELECT salary会报错 成了多列子查询
#min()只返回一组数据的一个结果(一列一行)标量查询
FROM employees
WHERE department_id =50
#此处改为WHERE department_id =250(为空不是一行一
列)
#最终虽然不会报错且结果为空 但是也是非法使用
);
#示例第2点 筛选后跟 单列/多行 子查询 (where或having后面)
#且使用 ()形式包起来 不管什么样的子查询 都放在小括号里面
/*
操作符 含义
in/not in 等于一行列表中的任意一个
any|some 和子查询返回的某一个值比较
(两者同一个意思 有歧义可读性低 )
比如 a>all(10,20,30)等同于 a>min(10,20,30)
再比如 a<all(10,20,30)等同于 a<max(10,20,30)
而max和min属于标量子查询 而 all 却是列/多行 子查询
all 和子查询返回的所有值比较
(all(指()内所有条件满足)和any(指()内单个条件满足)经常容易
搞混)
*/
#筛选后跟 单列/多行 子查询
#案例1:返回 location_id 是1400或1700的部门中所有员工的姓
名
#location_id 来自于 位置表 locations/departments 部门表
的位置编号
#部门 来自于 `departments`部门表 的
#员工姓名 来自于 `employees`员工表的 `last_name`
#感觉是2表连接 部门 `location_id` 确定部门 再确定
`department_id`员工
#第一步查询 location_id 是14000或1700的部门编号
SELECT DISTINCT department_id#假如说有重复的最好去一下重
仅提高效率
FROM departments
WHERE location_id IN(1400,1700)
#WHERE location_id=1400
#OR location_id=1700
;
#查询结果为 单列多行 子查询
#第二步查询员工姓名,要求部门编号是 第一步中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(#把in完整替换为=any结果相同
# in 完整替换为not in不是这个里面的
# in 完整替换为<>all同上依旧不是这个里面的意思
#此处这样做 居然省去了两表连接的步骤(其实就算两表连接也未
必查的出来)
SELECT DISTINCT department_id#假如说有重复的最好去一下重
仅提高效率
FROM departments
WHERE location_id IN(1400,1700)
#WHERE location_id=1400
#OR location_id=1700
);
#案例2.返回 比 (job_id为'IT_PROG'的部门) 任一工资低的员工
的
#员工号,姓名,job_id 以及salary
#员工号来自于 `employees`员工表的 `employee_id`
#姓名 `last_name`
#job_id 工种
#salary 工资
#部门 `department_id`
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
#任一工资低的
#job_id为'IT_PROG'的部门
SELECT DISTINCT salary#去重DISTINCT
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';#排除自己
#以上可以分为两步 先做子查询
#再做题目后 把子查询放里面去
#或者
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
#任一工资低的
#job_id为'IT_PROG'的部门
#SELECT DISTINCT salary#去重DISTINCT
#改为
SELECT MAX(salary)
#执行结果还是一样的76行
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';#排除自己
#案例3. 把任意 改为 所有 如下
#返回 比 (job_id为'IT_PROG'的部门) 所有 工资低的员工的
#员工号,姓名,job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
#任一工资低的
#job_id为'IT_PROG'的部门
#SELECT DISTINCT salary#去重DISTINCT
#改为
SELECT MIN(salary)#max改成min
#执行结果是44行
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';#排除自己
#或
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(#any改成all
#执行结果还是44行
#任一工资低的
#job_id为'IT_PROG'的部门
SELECT DISTINCT salary#去重DISTINCT
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';#排除自己
#3.行子查询(结果集 一/多 行多列 较少使用 )
#案例:查询员工编号最小并且工资最高的员工编号的员工信息
#解 子查询 题目是 分步先进行子查询
#第一步查询最小的员工编号
SELECT MIN(`employee_id`)
FROM employees;
#第二步查询最高工资
SELECT MAX(`salary`)
FROM employees;
#第三步
SELECT *
FROM employees
WHERE employee_id=(
#第一步中的查询最小的员工编号
SELECT MIN(`employee_id`)
FROM employees
)AND salary=(
#第二步中的查询最高工资
SELECT MAX(`salary`)
FROM employees
);
#我们发现查询的结果只有一行
#如果为一行子查询 我们可以精简为 以下代码
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#二,第二大种 前面都是放在where或having后的子查询
#现在讲 放在select 后的子查询
#案例:查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.`department_id`=d.`department_id`
) 部门员工数量
FROM `departments` d
;
#用以前两表连接也可以 这种方式也可以
#案例2:查询员工号=102的部门名Exe
#不许用连接查询 其实就是用了连接查询
SELECT (
SELECT `department_name`
FROM `departments` d
WHERE d.`department_id`=e.department_id
#里面的结果只能有 1列 1行 一个结果
) 部门名
FROM
employees e
WHERE e.`employee_id`=102;
#三,第三大种 前面都是放在where/having/select后 的子查询
#现在讲 放在 from 后的子查询
/*
将子查询结果充当一张表,要求必须起别名
*/
#案例:查询每个部门的平均工资的工资等级
#第一步 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM `employees`
GROUP BY department_id;
#第二步 查询工资等级
SELECT * FROM job_grades;#工资等级表
#使用 工资等级表 连接 第一步查询的各部门平均工资
#共两表 相连 为内连接的非等值相连
#连接条件 为平均工资
SELECT 部门平均工资与部门编号表.*,g.grade_level 工资等级
#查询ag_dep表中所有内容 等级表中的等级
FROM (
#第一步 查询每个部门的平均工资
SELECT AVG(salary) 部门平均工资,department_id 部
门编号
FROM `employees`
GROUP BY department_id
#各部门平均工资的 (工资 与 部门编号)表
) 部门平均工资与部门编号表#必须起别名否则这个表没名字找不
到
INNER JOIN job_grades g
ON 部门平均工资与部门编号表.部门平均工资 BETWEEN
lowest_sal AND highest_sal;
#四,exists后面(相关子查询)
#exists前面没讲过
SELECT EXISTS(#EXISTS是否存在的意思
#这里面的子查询
#有结果对外部返回数字1
#没结果对外部返回数字0
SELECT employee_id
FROM employees
);
/*
语法:
exists(完整的查询语句)
结果:
1或0
*/
#案例1:查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(#有部门员工返回1 通过WHERE筛选
#没有部门员工返回1 通不过WHERE筛选
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
#两表连接
);
#使用 in 的方法也可以做出来 用连接查询也可以做出来
SELECT department_name
FROM `departments` d
WHERE d.department_id IN(
SELECT e.department_id
FROM employees e
);
#案例2:查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
);#都有女朋友结果为空
#exists
SELECT bo.*
#查询 男神表的所有信息
FROM boys bo
#男神表
WHERE NOT EXISTS(
# 筛选没有女朋友的男神
SELECT boyfriend_id#查询女神的boyfriend_id
FROM beauty b
WHERE bo.id=b.`boyfriend_id`#连接条件
);#都有女朋友结果为空
#题目1.查询和 员工名为 Zlotkey 的 相同部门(80)的员工姓名和
工资
#第一步查部门编号
SELECT `department_id`
FROM `employees`
WHERE `last_name`='Zlotkey';
#第二步查询在 部门编号为80 的员工的名字和工资
SELECT `last_name`,`salary`,department_id
FROM employees
WHERE `department_id`= (
SELECT `department_id`
FROM `employees`
WHERE `last_name`='Zlotkey'
);
#题目2.查询工资比公司平均工资高的员工的员工号,姓名和工资
#第一步查询所有员工的平均工资 6461.682243
SELECT AVG(salary)
FROM `employees`;
#第二步查询比平均工资高的员工的 编号,姓名,工资
SELECT `employee_id`,`last_name`,`salary`
FROM `employees`
WHERE salary>(
SELECT AVG(salary)
FROM `employees`
);
#题目3.查询当前部门中工资比当前部门平均工资高的员工的员工
号,姓名,工资
#约三步
#第一步 查询各部门的平均工资
SELECT AVG(salary),department_id
FROM`employees`
GROUP BY `department_id`;
#第二步 连接第一步结果集(当表写在from后)和employees表
SELECT salary,e.department_id,`employee_id`,`last_name`
FROM (
SELECT AVG(salary) 平均工资,department_id
FROM`employees`
GROUP BY `department_id`
) 各部门的工资
INNER JOIN employees e
ON 各部门的工资.department_id=e.department_id
#此结果是 每个部门每个员工的工资
#在此基础上进行筛选
WHERE salary > 各部门的工资.平均工资 ;
#题目4.查询和 姓名中包含字母u的员工 在相同部门的员工的 员
工号,姓名
#第一步查询 姓名中包含字母u的员工 的部门编号
SELECT DISTINCT `department_id`#去重
FROM `employees`
WHERE `last_name` LIKE '%u%';
#第二步 查询第一步结果 部门编号 其含有的员工
SELECT `employee_id`,`last_name`
FROM `employees`
WHERE `department_id` IN(#字结果集多行一列选择用in
SELECT DISTINCT `department_id`#去重
FROM `employees`
WHERE `last_name` LIKE '%u%'
);
#题目5.查询在部门的location_id为1700的部门工作的员工的员工
号
#①查询 location_id 为 1700 的部门有哪些
SELECT DISTINCT department_id#最好去下重
FROM `departments`
WHERE `location_id`=1700;
#②查询部门号=①中的任意一个的员工号
SELECT `employee_id`
FROM `employees`
WHERE `department_id`=ANY(#=any和in在此处都可以
SELECT DISTINCT department_id#最好去下重
FROM `departments`
WHERE `location_id`=1700
);
#6.查询管理者是 K_ing 的员工姓名和工资
#子查询 有着类似于连接的效果 但又属于两种不同的方式
#①查询姓名为 K_ing 的员工编号
SELECT `employee_id`
FROM `employees`
WHERE last_name='K_ing';#结果100和156
#②查询哪个员工的manager_id=①
SELECT `last_name`,salary
FROM `employees`
WHERE manager_id=ANY(#=any和in在此处都可以
#假设你不知道子查询结果是一行还是多行全用in准没事
SELECT employee_id
FROM `employees`
WHERE last_name='K_ing'
);
#题目7.查询工资最高的员工的姓名,
#要求first_name和last_name显示为一列,
#列名为姓名
#①查询最高工资
SELECT MAX(salary)
FROM employees;
#②查询工资=①的姓.名
SELECT CONCAT(first_name,last_name) '姓.名'
FROM `employees`
WHERE `salary` =(#最高工资就一个用=此处也可用in
SELECT MAX(salary)
FROM employees
);
#进阶8:分页查询(重点但是比较简单)
/*
应用场景:当要显示的数据,一页显示不全,
需要分页提交 sql 请求
语法: select 查询列表 ⑦
from 表 ①
[join type] join 表2 ②
on 连接条件 ③
where 筛选条件 ④
group by 分组字段 ⑤
having 分组后的筛选 ⑥
order by 排序的字段 ⑧
limit offset,size; ⑨
#分页 条目数
offset要显示条目数的起始索引(这里的索引从0开始)
size 要显示的条目个数
特点:
①limit语句(不管是从执行上从语法上)
都是放在查询语句的最后
②公式
要显示的页数 page,每页的条目数size
select 查询列表
from 表
limt (page-1)*size,size;
#页数公式
size=10
page
1 0
2 10
3 20
*/
#案例1:查询前五条员工信息
SELECT * FROM employees
LIMIT 5;#默认offset的起始索引为0
#limit 0,5;
#limit offset,size;
#案例2:查询第 11 条--第 25 条
SELECT * FROM employees
LIMIT 10,15;#第十条条目开始 显示15条
#案例3:有奖金的员工信息,并且工资较高的前10名显示出来
SELECT * FROM employees
WHERE `commission_pct` IS NOT NULL
ORDER BY salary DESC#升3asc(默认)降4dese
LIMIT 10;
#题目
/*
已知表 stuinfo
id 学号
name 姓名
email 邮箱 john@126.com
gradeId 年级编号
sex 性别 男 女
age 年龄
已知表 grade
id 年级编号
gradeName 年级名称
一,查询 所有学员的邮箱的用户名(注:邮箱中 @前面的字符)
通过年级编号 两表相连
再使用 substr 简写/substring全名 截取邮箱字符串
substr(要截取的字符串,开始索引(从1开始),长度)
instr(被查找的字符串,'要查找的字符串') 返回第一个找到的下
标
instr(email.'@')-1 才为正确的截取长度值
select substr(email,1,instr(email.'@')-1) 用户名
from stuinfo;
二.分别查询男生和女生个数
select count(*)个数,sex
from stuinfo
group by sex;
三.查询年龄>18岁的所有学生的姓名和年级名称
select name,gradeName
from stuinfo s
inner join grade g
on s.gradeId=g.id
where age>18;
四.查询哪个年级的学生最小年龄>20岁
1.每个年级的最小年龄
select min(age),gradeid
from stuinfo
group by gradeid;
min(age) gradeid
5 1
6 2
7 3
2.在1的结果上筛选
select min(age),gradeid
from stuinfo
group by gradeid
having min(age)>20;
五,试说出查询语句中涉及到的所有关键字,以及执行先后顺序
select 查询列表 ⑦
from 表 ①
连接类型 join 表二 ②
on 连接条件 ③
where 筛选条件 ④
group by 分组列表 ⑤
having 分组后的筛选 ⑥
order by 排序列表 ⑧
limit 偏移,条目数; ⑨
*/
#1.查询工资最低的员工信息:last_name,salary
#①查询最低的工资
SELECT MIN(salary)
FROM employees;
#②查询last_name,salary,要求salary=①
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#2.查询平均工资最低的部门信息
#①各部门的平均工资
SELECT AVG(salary),`department_id`
FROM `employees`
GROUP BY department_id;
#② 查询①结果上最低平均工资
SELECT MIN(平均工资)
FROM (
SELECT AVG(salary) 平均工资,`department_id`
FROM `employees`
GROUP BY department_id
) 各部门的平均工资
;
#③查询哪个部门的平均工资=②
SELECT AVG(salary) 平均工资,`department_id` 各部门
FROM `employees`
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(平均工资)
FROM (
SELECT AVG(salary) 平均工资,`department_id`
FROM `employees`
GROUP BY department_id
) 各部门的平均工资
);
#④查询部门信息
SELECT d.*
FROM `departments` d
WHERE d.`department_id`=(
SELECT `department_id` 各部门
FROM `employees`
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(平均工资)
FROM (
SELECT AVG(salary) 平均工资,`department_id`
FROM `employees`
GROUP BY department_id
) 各部门的平均工资
)
);
#方式二
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)#默认升序
LIMIT 1;#0页第一行
#取得平均工资最低的那行
#③查询部门信息
SELECT *
FROM `departments`
WHERE `department_id`=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)#默认升序
LIMIT 1
)
# 3. 查询平均工资最低的部门信息和该部门的平均工资
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#②求出最低平均工资的部门编号
SELECT AVG(salary) department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)#默认升序
LIMIT 1;#0页第一行
#取得平均工资最低的那行
#③查询部门信息
SELECT d.*,平均工资
FROM `departments` d
JOIN (
SELECT AVG(salary) 平均工资,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)#默认升序
LIMIT 1
) 最低平均工资和部门编号
ON d.`department_id`=最低平均工资和部门编
号.`department_id`
;
/*
#③方式二 失败
select d.*,(
这里的子查询只能放置标量子查询 单列一个
无法放 department_id
但后面又需要用到 department_id
所以此方法不可行
SELECT AVG(salary) 平均工资
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)#默认升序
LIMIT 1
) 最低平均工资和部门编号
from `departments` d
where d.`department_id`=最低平均工资和部门编
号.`department_id`
;
*/
# 4. 查询平均工资最高的 job 信息
#①查询每个job的平均工资
SELECT AVG(salary),job_id
FROM `employees`
GROUP BY job_id;
#②查询平均工资最高
SELECT AVG(salary),job_id
FROM `employees`
GROUP BY job_id
ORDER BY AVG(salary) DESC#降序
LIMIT 1
;
#③平均工资最高 job 信息
SELECT j.*,平均工资最高的工资和工种.*
FROM `jobs` j
JOIN (
SELECT AVG(salary),job_id
FROM `employees`
GROUP BY job_id
ORDER BY AVG(salary) DESC#降序
LIMIT 1
) 平均工资最高的工资和工种
WHERE j.`job_id`=平均工资最高的工资和工种.job_id
;
# 5. 查询平均工资高于公司平均工资的部门有哪些
#①查询公司的平均工资
SELECT AVG(salary)
FROM employees;
#②查询每个部门的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY `department_id`;
#③ 筛选②结果集,满足平均工资>①
SELECT AVG(salary),`department_id`
FROM employees
GROUP BY `department_id`
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees#6461
);
# 6. 查询出公司中所有 manager 的详细信息.
#①查询所有 (领导)manager 的员工编号
SELECT DISTINCT manager_id#去重
FROM `employees`;
#②查询详细信息,满足`employee_id`=①
SELECT e.*
FROM `employees` e
WHERE e.`employee_id` =ANY(#=ANY可完全替换为in
SELECT DISTINCT manager_id#去重
FROM `employees`
);
# 7. 按各部门工资最高的员工排列
#找出其中哪个部门最低
#①查询各部门其员工的最高工资
SELECT MAX(salary) ,department_id
FROM employees
GROUP BY `department_id`;
#② 在①找出其中哪个部门最低
SELECT department_id
FROM employees
GROUP BY `department_id`
ORDER BY MAX(salary)#默认升序
LIMIT 1#0页第一行最低工资的那个
;
#③ 查询②结果部门(10)的最低工资
SELECT MIN(salary) ,department_id
FROM employees
GROUP BY `department_id`
HAVING department_id=(
SELECT department_id
FROM employees
GROUP BY `department_id`
ORDER BY MAX(salary)#默认升序
LIMIT 1#0页第一行最低工资的那个
);
# 8. 查询平均工资最高的部门的 manager 的详细信息:
#`last_name`,`department_id`,`email`,`salary`
#①查询平均工资最高的部门
SELECT department_id
FROM employees
GROUP BY `department_id`
ORDER BY AVG(salary) DESC#降序
LIMIT 1#0页第一行最低工资的那个部门90
;
#② 查询①的结果部门中的 员工 信息
SELECT *
FROM employees e
WHERE e.`department_id`=(
SELECT department_id
FROM employees
GROUP BY `department_id`
ORDER BY AVG(salary) DESC#降序
LIMIT 1#0页第一行最低工资的那个部门90
)
;
#③ 查询②的结果中,为 (领导)manager的员工信息
SELECT *
FROM (
SELECT *
FROM employees e
WHERE e.`department_id`=(
SELECT department_id
FROM employees
GROUP BY `department_id`
ORDER BY AVG(salary) DESC#降序
LIMIT 1#0页第一行最低工资的那个部门90
)
) 表一,(
SELECT DISTINCT `manager_id`
FROM employees e
WHERE e.`department_id`=(
SELECT department_id
FROM employees
GROUP BY `department_id`
ORDER BY MAX(salary) DESC#降序
LIMIT 1#0页第一行最低工资的那个部
门90
)
) 表二
WHERE 表一.`employee_id`=表二.`manager_id`;
#1.查询每个专业的学生人数
#新表student的内容
SELECT * FROM `major`;#专业 主键`majorid`
SELECT * FROM `result`;#成绩 主键id 外键`studentno`
SELECT * FROM `student`;#学生 主键`studentno`学号 外键
`majorid`
SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;
#2.查询参加考试的学生中,每个学生的平均分,最高分
SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;
#3. 查询姓张的每个学生的最低分大于60的学号.姓名
#姓名 来自`student` 学员表的 `studentname`
#分数 来自`result` 成绩表的 `score`
#共两表 为 内连接的等值连接
#等值条件为 `studentno`
SELECT s.studentno,s.`studentname`,MIN(score)
FROM `student` s
JOIN result r
ON s.studentno=r.studentno
WHERE s.`studentname` LIKE "张%"
GROUP BY s.studentno
HAVING MIN(score)>60
;
# 4. 查询生日在'1988-1-1'后的学生姓名,专业名称
#生日来自于 `student`学生表的`borndate`
#姓名来自于 `studentname`
#专业名称 来自于 `major`专业表的`majorname`
#共两表 为 内连接的等值连接
#等值条件为 `majorid`
SELECT studentname,majorname,borndate
FROM student s
JOIN major m
ON s.majorid=m.majorid
WHERE DATEDIFF( borndate,'1988-1-1') > 0;
# 日期做差 这里日期- 这里的 > 0
# 5. 查询每个专业的男生人数和女生人数
#专业 来自于 `student`学生表的 `majorid`
#男生人数来自于 `sex`=男
#女生人数来自于 `sex`=女
SELECT COUNT(*) 个数,sex 性别,majorid 专业
FROM student
GROUP BY sex,majorid;
#分组可以按两个分组,号隔开...我居然用and再写一个分组
#方式二:
SELECT majorid,(
SELECT COUNT(*)
FROM student
WHERE sex='男'
AND s.majorid=majorid
) 男,(
SELECT COUNT(*)
FROM student
WHERE sex='女'
AND s.majorid=majorid
) 女
FROM student s
GROUP BY majorid;
# 6. 查询专业和张翠山一样的学生的最低分
#先达成题目后面的条件 一般使用子查询
#① 查询张翠山的专业
SELECT `majorid`
FROM `student`
WHERE `studentname`='张翠山';#专业为1
#② 专业为1的 学生的最低分(70)
SELECT MIN(`score`),majorid
FROM `result` r
JOIN `student` s
ON r.`studentno`=s.studentno
WHERE `majorid`=(
SELECT `majorid`
FROM `student`
WHERE `studentname`='张翠山'#专业为1
);
# 7. 查询大于60分的学生的姓名,密码,专业名
SELECT `studentname`,`loginpwd`,`majorname`
FROM `student` s
JOIN `result` r
ON s.`studentno`=r.`studentno`
JOIN `major` m
ON s.`majorid`=m.`majorid`
WHERE `score`> 60;
#三表内连接的等值连接 加筛选
# 8. 按邮箱位数分组,查询每组的学生个数
SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);
# 9. 查询学生名,专业名,分数
SELECT `studentname`,`majorname`,`score`
FROM `student` s
JOIN `result` r
ON s.`studentno`=r.`studentno`
JOIN `major` m
ON s.`majorid`=m.`majorid`
;
#三表内连接的等值连接 加筛选
# 10.查询哪个专业没有学生,分别用左连接和右连接实现
#专业 来自`major`专业表
#学生 来自 `student`学生表
#两表连接 使用外连接的左外/右外均可
#连接条件 `majorid`
#左外
SELECT `majorname`,`studentname`
FROM major m
LEFT JOIN student s
ON m.majorid=s.majorid
WHERE studentname IS NULL;
#右外(只是调换位置并更改连接名字)
SELECT `majorname`,`studentname`
FROM student s
RIGHT JOIN major m
ON m.majorid=s.majorid
WHERE studentname IS NULL;
# 11. 查询没有成绩的学生人数
#成绩 `result`成绩表的 `score`
#学生 来自于`student`学生表的 `studentname`
#共两表连接 sql 99语法外连接中的左外连接
#连接条件为 studentno
SELECT s.*,r.*
#SELECT COUNT(*)#9个
FROM student s
LEFT JOIN result r
ON s.`studentno`=r.`studentno`
WHERE score IS NULL;
#进阶9:联合查询(没表)
/*
union 联合 合并: 将多条查询语句的结果合并成一个结果
*/
#引入的案例:查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR
department_id>90;
#使用联合查询
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
/*
语法:
查询语句1
union
查询语句2
union
...
应用场景
*/
#案例:查询中国用户男性的信息
# 以及外国用户男性的信息
SELECT id,cname,csex
FROM t_ca
WHERE csex='男'
UNION
SELECT t_id,tName,tGender FROM t_ua
WHERE tGender='male';
#要查询的结果来自于多个表,
#且多个表没有直接的连接关系
#但查询的信息一致时
意义:
1.将一条比较复杂的查询语句拆分成多条语句
2.适用于查询多个表的时候,查询列基本是一致
#联合查询的特点:
#1.要求多条查询语句的查询列数是一致的
#2.要求多余查询语句的查询的每一列的类型和顺序最好
一致
#3.union关键字默认去重,如果使用union all 可以包含
重复项
#DQL 查询总结
语法:
select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by 排序列表 ⑧
linit 起始条目索引,条目数 ⑨
#DML语言(常用)
/*
数据操纵语言:
插入:insert
修改:update
删除:delete
*/
#1.插入语句 方式一
/*
语法:
insert into 表名(列名,...)
values(值1,...)
表名
列名
新值
字符类型 int 数字
char 字符串双引号
datetime 日期单引号
blob 保存图片的
Nullable 可以为空null
*/
#1.插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty
(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-
23','18988888888',NULL,2);
SELECT * FROM beauty;
#2.不可以为null的列必须插入值.可以为null的列是如何插入值?
#方式2:
INSERT INTO beauty
(id,NAME,sex,borndate,phone,boyfriend_id)
VALUES(14,'金星','女','1990-4-23','13888888888',9);
#方式3:
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','13888888888');
#设计表的时候有默认值 没有默认值为null
#要么列名和值都省略, 要么值写null
#3.在保持列名与值一一对应的情况下,列的顺序可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('匠心','女',16,'110');
#4.列数和值的个数必须一致
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');
#5.可以省略列名,默认所有列,而且列的顺序和表中列的顺序一致
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);
#2. 插入语句 方式二
/*
语法:
insert into 表名()
set 列名=值,列名=值,...(可以为null/有默认值的不写就行了)
*/
#1.
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';
SELECT * FROM beauty
ORDER BY id DESC;
#两种方式大pk
#1.方式1支持插入多行
INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL),
(19,'张飞','男',NULL,'119',NULL,NULL),
(20,'张飞','男',NULL,'119',NULL,NULL);
#2.方式1支持子查询,方式2不支持
INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋健','11809866';
INSERT INTO beauty(id,NAME,phone)
SELECT id+100,boyname,'11809866'
FROM boys WHERE id<3;
#2.修改语句
/*
1.修改单表的记录[重点]
语法:
update 表名 ①
set 列=新值,列=新值,... ③
where 筛选条件; ②
2.修改多表的记录[扩充知识]
语法:
update 表1 别名
left/right/inner join 表2 别名
on 连接条件
set 表头=值,表头=值...
where [筛选条件];
*/
#1.修改单表的记录
#案例1:修改beauty表中姓唐的女神的电话为13899888899
UPDATE beauty SET phone='13899888899'
WHERE NAME LIKE '唐%';
#案例2.修改boys表中的id号为2的名称改为张飞, 魅力值为10
UPDATE boys SET boyname='张飞',usercp='10'
WHERE id=2;
SELECT * FROM boys
ORDER BY id DESC;
#2.修改多表的记录
#案例1:修改张无忌的女朋友的手机号为114
UPDATE boys bo#要修改的表
INNER JOIN beauty b#要连接的表
ON bo.id=b.boyfriend_id#确定表(生成虚拟表)
SET b.`phone`='114'#要修改的数据
WHERE bo.boyName='张无忌';#要修改的内容
SELECT * FROM beauty;#查询被修改的内容
#案例2:修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo#找到男神表
INNER JOIN beauty b#连接女神表
ON bo.id=b.boyfriend_id#生成虚拟表
SET b.boyfriend_id=2#要修改的数据
WHERE bo.id IS NULL#要修改的项
SELECT * FROM boys;
#三.删除语句
/*
方式一:delete
语法:
1..单表的删除[常用]
delete from 表名
[where 筛选条件];
2..多表的删除[补充了解知识]
sql 92语法:
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
sql 99语法:
delete 表1的别名,表2的别名
from 表1 别名
inner/left/right join 表2 别名
on 连接条件
[where 筛选条件];
方式二:truncate
语法:truncate table 表名;
*/
#delete
#1.单表的删除
#案例1:删除手机号以9结尾的女神
DELETE#删除
FROM beauty#表
WHERE phone LIKE '%9';#要删除的内容
SELECT *FROM beauty;#查询修改后的内容
#2.多表的删除
#案例:删除张无忌的女朋友的信息
DELETE b,bo#要删除原生哪个表的信息
FROM beauty b#表一
INNER JOIN boys bo#内连接等值连接表二
ON b.boyfriend_id=bo.id#生成虚拟表
WHERE bo.`boyName`='张无忌';#要删除的内容
#案例:删除黄晓明的信息以及他女朋友的信息
DELETE b,bo#要删除原生哪个表的信息
FROM beauty b#表一
INNER JOIN boys bo#内连接等值连接表二
ON b.boyfriend_id=bo.`id`#生成虚拟表
WHERE bo.`boyName`='黄晓明';#要删除的内容
SELECT *
FROM beauty; #黄晓明对应id anglebaby 也没了
SELECT *
FROM boys;#黄晓明没了
#方式二:truncate语句(不允许加WHERE以及连接条件)
#案例:将魅力值>100的男神信息删除
TRUNCATE TABLE boys ;
# 两种方式的 区别(常用) delete pk truncate[面试题]
/*
1.delete 可以加 where 筛选条件,TRUNCATE不能加
2.truncate 删除,效率高一丢丢
3.假如要删除的表中有自增长列,
如果
用delete删除后,再插入数据,自增长列的值从断点开始,
而 truncate 删除后,在插入数据,自增长值从1开始
4.truncate删除没有返回值(共0行受到影响),
delete删除有返回值(共几行受到影响)
5.truncate删除不能回滚(事物的问题),delete删除可以回滚
*/
SELECT * FROM boys;#查询boys表
DELETE FROM boys;#删除boys表(断点自增)
TRUNCATE TABLE boys;#删除boys表(完全重置)
INSERT INTO boys (boyName,usercp)#一次插入多行
VALUES('张飞',100),('刘备',100),('关羽',100);
#1.运行以下脚本创建表my_employees
USE myemployees;
CREATE TABLE my_employees(#创建表 字段:字段值
Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
salary DOUBLE(10,2)
);
CREATE TABLE users(#创建表 字段:字段值
Id INT,
Userid VARCHAR(10),
department_id INT
);
#在`myemployees`下创建了`my_employees`和`users`两个空表
#2. 显示表my_employees的结构
DESC my_employees;#查看某个表和降序同一个单词desc
#3.向my_employees表中插入下列数据
/*
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
*/
#解:一
INSERT INTO my_employees#多行插入 默认对应所有表头
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
#解:二
INSERT INTO my_employees
#插入 联合查询结果(相当于多行插入)
SELECT 1,'patel','Ralph','Rpatel',895
UNION
SELECT 2,'Dancs','Betty','Bdancs',860
UNION
SELECT 3,'Biri','Ben','Bbiri',1100
UNION
SELECT 4,'Newman','Chad','Cnewman',750
UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
DELETE FROM my_employees;#删除表
SELECT * FROM my_employees;#查看表
#4.向users表中插入数据
/*
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
*/
INSERT INTO users#表插入(多行)数据
VALUES(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);
#上面也能使用联合查询 插入 此处不再演示
SELECT * FROM users;#查询表
#5.将3号员工的last_name修改为'drelxer'
UPDATE my_employees#修改表
SET last_name='drelxer'#修改的数据
WHERE id=3;#要修改的内容
SELECT * FROM my_employees;#查询表
#6.将所有工资少于900的员工的工资修改为1000
UPDATE my_employees#修改表
SET salary=1000#修改的数据
WHERE salary<900;#要修改的内容
#7.将 userid 为 Bbiri 的 users 表和
# my_employees 表的记录全部删除
DELETE m,u#要删除原生哪个表的信息
FROM users u#表一
INNER JOIN my_employees m#内连接等值连接表二
ON m.id=u.`id`#生成虚拟表
WHERE m.`userid`='Bbiri';#要删除的内容
SELECT * FROM my_employees;#查询表
SELECT * FROM users;#查询表
#8.删除所有数据
DELETE FROM my_employees;#删除表(不会重置断点)
DELETE FROM users;#删除表
#9.检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;
#10.清空表 my_employees
TRUNCATE TABLE my_employees;#删除表(重置断点)
#DDL
/*
数据定义语言
库和表的管理(了解)
一.库的管理
创建.修改,删除
二.表的管理
创建,修改,删除
创建: Create
修改: alter
删除: drop
*/
#一.库的管理
#1.库的创建
/*
语法:
create database [if not exists] 库名 [character set 字符
集名];
*/
#案例:创建库Book
CREATE DATABASE books;
#创建库 mysql 不区分大小写
#但是 数据表的内容 字符串 应该是区分的
#创建库执行第二遍是会报错的(重名)
CREATE DATABASE IF NOT EXISTS books;
#如果有那就别建了,如果没有那就创建上
#点评:这功能可有可无,报错不一样的效果
#2.库的修改
/*
库一般不修改
失效语句
rename database books to 新库名;
如果要修改库名
直接修改
c/programData/mysql/mysql server5.5/data/books
文件夹的名字 就是库名的名字
sql语句只能修改库的字符集(数据库的编码)
alter database books character set
查看字符集 books/右键/改变数据库
默认utf8
*/
ALTER DATABASE books CHARACTER SET gbk;
#将字符集改为gbk
如果是的你的之前的数据库没有设置好：
对于已经存在了的数据库，可以如下操作使其改变编码方式：
a. 修改MYSQL数据库编码，
如果是MYSQL数据库编码不正确:
可以在MYSQL执行如下命令: ALTER DATABASE test DEFAULT
CHARACTER SET utf8;
以上命令就是将MYSQL的test数据库的编码设为utf8
b. 修改MYSQL表的编码：ALTER TABLE category DEFAULT
CHARACTER SET utf8;
以上命令就是将一个表category的编码改为utf8
c. 修改字段的编码：
ALTER TABLE `test` CHANGE `dd` `dd`
VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin NOT
NULL
以上命令就是将MYSQL数据库test表中 dd的字段编码改为utf8
在命令行下插入汉字时如下代码：
set names gb2312;
有时候这一句很关键！
insert into charset values('lmj');
然后再重启服务就可以 了。
如果出现无法启动服务的情况，
就以管理员身份运行cmd先进入到mysql安装目录下，
执行
mysqld -remove
移除服务
然后
mysqld -install
安装服务
就可以了
#3.库的删除 DROP DATABASE [IF EXISTS] books;
DROP DATABASE books;
#删除名字为books的库
#重复删除会报错
DROP DATABASE IF EXISTS books;
#把重复删除会报错变成警告
#二.表的管理
#1.表的创建(经常)
/*
语法:
create table 表名(
列名 列的类型[(宽度) 约束],
列名 列的类型[(宽度) 约束],
列名 列的类型[(宽度) 约束],
...
列名 列的类型[(宽度) 约束]
)
*/
#案例:创建表Book
CREATE TABLE book(#创建空表表头
id INT,#编号
bname VARCHAR(20),#书名的最大长度
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
);
SELECT * FROM book;#查询表 表的内容列
DESC book;#查看表 表头类型
#案例:创建表author
CREATE TABLE author(
#CREATE TABLE if not exists author(
id INT,
au_name VARCHAR(20),#作者名的最大长度
nation VARCHAR(20)#国籍名的最大长度
);
DESC author;#查看表 表头类型
#2.修改表
/*
1.添加到最前面或者某个列的后面
alter table 表名
add column 列名 [列类型 约束]
[frist/(after 字段名)];
2.修改列的类型或约束
alter table 表名
modify column 列名 新类型 [新约束]
3.修改列名(可顺道改类型)
alter table 表名
change column 旧列名 新列名 [列类型 约束];
4.删除列
alter table 表名
drop column 列名;
5.修改表名
alter table 表名
rename [to] 新表名;
*/
/*
alter table 表名
add/drop/modify/change column 列名 [列类型 约束];
新增/删除/修改/修改(全) 列
*/
#①修改列名(表头)
ALTER TABLE book#要修改的表
CHANGE COLUMN publishdate pubDate DATETIME;
# 可省略 原列名(表头) 新名字 必须定义类型
# 仅CHANGE后
DESC book;#查看表 表头类型
#②修改列的类型或约束#作者名的最大长度/默认值
ALTER TABLE book#要修改的表
MODIFY COLUMN pubDate TIMESTAMP;
# 列名 新类型
# 不区分大小写
#③添加新列
ALTER TABLE author#要修改的表
ADD COLUMN annual DOUBLE;
DESC author;#查看表 表头类型
#新增 列 列名(表头)
#④删除列
ALTER TABLE author#要删除的表
DROP COLUMN annual;#要删除的列名
#此处不能使用 IF EXISTS 把报错变为警告
#⑤修改表名
ALTER TABLE author#要修改的表
RENAME TO book_author;#为表重命名
#(之前说的是库重命名时有可能崩溃,这里是表)
DESC book_author;#查看表 表头类型
#3.删除表
/*
语法:
drop table [IF EXISTS] 表名;
*/
DROP TABLE book_author;
DROP TABLE IF EXISTS book_author;
#删除表失败不报错,报警告
#仅仅在 创建/删除 库/表 时候有用
#IF EXISTS 在删除列是无效的
#if not exists 创表
SHOW TABLES;#查看当前库的所有表
#通用的写法:(如果有数据不想被删掉不建议这么干)
#先删一遍再创建库
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
#先删一遍再创建表
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
#4.复制表
/*
语法:
1.复制表的结构
create table 表名 like 旧表;
2.复制表的结构+数据(可跨库 库.表 即可)
create table 表名
select 查询列表 from 旧表 [where 筛选]
*/
INSERT INTO author#选中表插入多行数据
VALUES(1,'王小波','中国'),
(2,'道乾','中国'),
(3,'罗素','英国'),
(4,'李银河','中国');
SELECT * FROM author;#查询表
#1.仅仅复制表的结构不复制内容(名字为copy)
CREATE TABLE copy LIKE author;
SELECT * FROM copy;#查询表
#2.完全复制(名字为copy2)
CREATE TABLE copy2#创建表copy2
SELECT * FROM author;
#数据来自查询author表的结果
SELECT * FROM copy2;#查询表
#部分复制(可复制任意查询结果的数据)
CREATE TABLE copy3#创建表copy3
SELECT id,au_name FROM author
#数据来自查询author表的结果
WHERE nation='中国';
#仅仅复制某些字段(列名/表头)
CREATE TABLE copy4#创建表copy4
SELECT id,au_name FROM author
#数据来自查询author表的结果
WHERE 0;
#WHERE 1=2;
#由于设置了谁都不满足
#所以会返回一个只有表头的空表
#又由于表头是自己写的查询结果
#所以可以自由复制表的表头
#题目
/*1.创建表dept1
name null type
id int(7)
name varchar(25)
*/
USE test;
CREATE TABLE dept1(#创建表
id INT(7),
NAME VARCHAR(25)
);
SELECT * FROM dept1;#查询表
#2.垮库 将表 departmentments 中的数据插入新表 dept2 中
CREATE TABLE dept2#创建表
SELECT department_id,department_name#要复制的表头
#from departments;#数据的来源
#由于 跨库了 执行以上语句会出现找不到的情况
# 需要添加非当前库的库名
FROM `myemployees`.departments;#数据的来源
SELECT * FROM dept2;#查询表
#3.创建表emp5
/*
name unll? type
id int(7)
first_name varchar(25)
last_name varchar(25)
Deptid int(7)
*/
CREATE TABLE emp5(#创建表
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
Deptid INT(7)
#表头 类型
);
#4.将列last_name的长度增加到50
#修改 库中表的 表头和类型
ALTER TABLE emps
MODIFY COLUMN last_name VARCHAR(50);
#MODIFY修改类型
#change修改表头和类型
#5.根据表 employees 创建 employees2
#复制表结构
CREATE TABLE employees2#创建表
LIKE `myemployees`.employees;#垮库获取表结构
#6.删除表 emp5
DROP TABLE emp5;
#DROP TABLE if exists emp5;
# 如果 存在
#7.将表 employees2 重命名为emps
#表重命名
ALTER TABLE employees2 RENAME TO emp5;
#表/库 名 使用中文在不同环境下可能乱码 维护困难
#8.在表dept和emp5中添加新列test_column,
#并检查所做的操作
ALTER TABLE emp5#创建表
ADD COLUMN test_column INT(7);
#添加表头 必须写上类型(除非表设置了默认值)
#9.直接删除表 emp5 中的列 department_id
DESC emp5;#查看列
ALTER TABLE emp5#要删除的表
DROP COLUMN department_id;3要删除的列
#常见的数据类型
/*
数值型:
整
型:tinyint,smallint,mediumint,int/integer,bigint
小数:
定点数
浮点数
字符型:
较短的文本:char,varchar
较长的文本:text,blob(较长的二进制数据(图片))
日期型:
*/
#一.整型
/*
分类:
tinyint,smallint,mediumint,int/integer,bigint
1 2 3 4 8(字节大小)
特点:
① 如果不设置无符号还是有符号(负数),默认是有符号
如果想设置无符号(正数),需要添加 unsigned 关键字
②如果插入的数值超出了整型的范围,会提示警告
并且插入最接近的临界值
③如果不设置长度,会自带默认的长度
长度代表了显示的最大宽度,
搭配zerofill使用时
如果不够会用0在左边填充
并且默认变为无符号的整型
*/
#案例1.如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(#创建表
t1 INT(7) ZEROFILL,#7代表不满7位数的使用0填充且
#表头 类型 默认开启 UNSIGNED 不再支持负数
符号-
t2 INT UNSIGNED
);
DESC tab_int;#查看表头
INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123789101456,-123789101456);
INSERT INTO tab_int VALUES(123789101456,123789101456);
INSERT INTO tab_int VALUES(123,123);
SELECT * FROM tab_int;#查询表内容
#二.小数(浮点型)
/*
分类:
1.浮点数
float(M,D)
double(M,D)
2.定点数
dec(M,D)
decimal(M,D)
特点:
①
M: 整数部位+小数部位
D: 小数部位
如果超过范围,则插入最接近的临界值
②
M和D都可以省略
如果是 DECIMAL,则M默认为10,D默认为0
如果是 FLOAT/DOUBLE,则会根据插入的数值的精度来决定精度
③定点型的精确度较高,如果要求插入数值的精度较高
如货币运算等则考虑使用
*/
#测试M和D
DROP TABLE tab_float;#删除表
CREATE TABLE tab_float(#创建表
#f1 float(5,2),#共5位数且小数点保留两位 xxx.x0
f1 FLOAT,
f2 DOUBLE,
f3 DECIMAL
);
SELECT * FROM tab_float;#查询表
DESC tab_float;#查看表头
INSERT INTO tab_float VALUES(123.45,123.45,123.45);
INSERT INTO tab_float VALUES
(111423.456,123.456,123.456);
INSERT INTO tab_float VALUES(123.45,123.45,13.4);
#原则:
/*
所选择的类型越简单越好,能保存数值的类型越小越好
*/
#三.字符型
/*
较短的文本
①char
固定长度的字符,
写法为char(M),
最大长度不能超过M
其中M可以省略,
默认为1
②varchar
可变长度的字符
写法为varchar(M)
最大长度不能超过M
其中M不可以省略
其它:
③binary 和 ④varbinary 用于保存较短的二进制
⑤enum 用于保存枚举
⑥set 用于保存集合
较长的文本:
⑦text
⑧biob(较大的二进制)
特点:
空间 效率 写法 M的意思
特点
char(比较耗费) 高 char(M) 最大的字符数(可省略默认为
1) 固定长度的字符
varchar(比较节省) 低 varchar(M) 最大的字符数(不可以省略
) 可变长度的字符
*/
CREATE TABLE tab_char(#创建表
c1 ENUM('a','b','c')#这个表头 只能插入 a/b/c
#enum 用于保存枚举
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');#超出了ENUM列表值
INSERT INTO tab_char VALUES('A');
SELECT * FROM tab_char;
CREATE TABLE tab_set(#创建表
s1 SET('a','b','c','d')#set 用于保存集合
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,b');
INSERT INTO tab_set VALUES('a,c,d');
SELECT * FROM tab_set;
#四.日期型
/*
分类:
date 只保存日期
time 只保存时间
year 只保存年
datetime 保存日期加时间
TIMESTAMP 保存日期加时间
区别
特点: 字节 范围 时区等的影响
DATETIME 8 1000-9999年 不受
TIMESTAMP 4 1970-2038年 受
*/
CREATE TABLE tab_date(#创建表
t1 DATETIME,
t2 TIMESTAMP#时间戳受时区和mysql版本影响
);
INSERT INTO tab_date#为表插入行数据
VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone';#显示时区
SET time_zone='+9:00';#定义时区为东九区
#常见约束
/*
含义: 一种限制,用于限制表中的数据
为了保证表中的数据的准确和可靠性
分类:六大约束
NOT NULL:非空,用于保证该字段的值不能为空
比如姓名,学号等
default:默认,用于保证该字段有默认值
比如性别
primary key:主键,用于保证该字段的值具有唯一性
并且非空 比如学号,员工编号等
unique:唯一,用于保证该字段的值具有唯一性
可以为空,比如座位号
check:检查约束[mysql中不支持(语法不报错但是没效果
)]
比如年龄,性别
foreign key:外键,用于限制两个表的关系,
用于保证该字段的值
必须来自于主表的关联列的值
在从表添加外键约束
用于引用主表中某列的值
比如学生表的专业编号,
员工表的部门编号
员工表的工种编号
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束:
除了非空.默认,其它的都支持
主键和唯一的大对比:
保证唯一性 是否允许为空 一个表
中可以有多少个 是否允许组合
主键 √ ×(一列中不允许出现null) 至
多一个 √,但不推荐
唯一 √ √(一列中只能有一行为null) 可
以有多个 √,但不推荐
外键:
1.要求在从表设置外键关系
2.从表的外键列的类型和
主表的关联列的类型要求一致
名称无要求
3.主表的关联列必须是一个key(一般是主键 或 唯一也行
)
4.插入数据时,先插入主表,再插入从表
删除数据时,先删除从表,再删除主表
insert into major values(1,'java');
insert into major values(2,'h5');
delete from stuinfo;#删除表的数据
insert into stuinfo values
(1,'john','男',null,19,1);
insert into stuinfo values
(1,'lily','男',null,19,2);
DROP TABLE IF EXISTS major;#删除表头
CREATE TABLE major(#创建表
id INT PRIMARY KEY,#主键
majorName VARCHAR(20)#专业名称
);
DROP TABLE IF EXISTS stuinfo;#删除表
CREATE TABLE stuinfo(#创建表
id INT ,
stuName VARCHAR(20) ,
gender CHAR(1),
seat INT ,
age INT ,
majorId INT,
#PRIMARY KEY(id),#主键
PRIMARY KEY(id,stuname),#这两个列组合成一个主键
UNIQUE(seat,age),#组合唯一键
#这两个组合只要有一个不同 即不会报错
CHECK(gender='男' OR gender='女'),#检查约束
FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;#查看表的索引
语法:
CREATE TABLE 表名(
字段名 字段类型 约束,
字段名 字段类型,
表级约束
)
*/
#一.创建表时添加约束
#1.添加列级约束
/*
语法:
直接在字段名和类型后面追加 约束类型 即可
只支持:
默认,非空,主键,唯一
不支持
check 检查
REFERENCES 外键
*/
CREATE DATABASE students;#创建库
USE students;
TRUNCATE TABLE major;#删除表(重置断点)
CREATE TABLE stuinfo(#创建表
id INT PRIMARY KEY,#主键
#此处不只可以添加一个列级约束(主键除外 唯一除外)
可以添加多个 无顺序要求
stuName VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男' OR
gender='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键
);
DROP TABLE IF EXISTS major;
CREATE TABLE major(#创建表
id INT PRIMARY KEY,#主键
majorName VARCHAR(20)#专业名称
);
DESC stuinfo;#查看表头类型
SHOW INDEX FROM stuinfo;
#查看索引 包括 主键 外键 唯一
#2.添加表级约束
/*
语法:在各个字段的最下面
[constraint 约束名] 约束类型(字段名)
*/
#通用的写法:(常用)
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid)
REFERENCES major(id)
#外键
);
#二.修改(关于修改表时添加的表头(列)约束)
/*
1.添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束
2.添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名)
[外键的引用];
*/
DROP TABLE IF EXISTS stuinfo;#删除表
CREATE TABLE stuinfo(#创建表
id INT ,
stuName VARCHAR(20) ,
gender CHAR(1),
seat INT ,
age INT ,
majorId INT
);
#1.添加非空约束(为stuName加非空约束)
ALTER TABLE stuinfo
MODIFY COLUMN stuname
VARCHAR(20) NOT NULL;
#VARCHAR(20);#要改时 不写即可
DESC stuinfo;#查看表头(列)约束
#1.添加默认约束(为age加默认约束)
ALTER TABLE stuinfo
MODIFY COLUMN age
INT DEFAULT 18;
#3.添加主键(只要支持列级约束的都可以这么写)
#①列级约束
ALTER TABLE stuinfo
MODIFY COLUMN id
INT PRIMARY KEY;
#或
#②表级约束 两种写法
ALTER TABLE stuinfo
ADD PRIMARY KEY(id);
DESC stuinfo;#查看表头(列)约束
#4.添加唯一键(seat表头)
#①列级约束
ALTER TABLE stuinfo
MODIFY COLUMN seat
INT UNIQUE;
#或
#②表级约束 两种写法
ALTER TABLE stuinfo
ADD UNIQUE(seat);
#5.添加外键(只有列级 表级无效)表头majorid
ALTER TABLE stuinfo
ADD
#constraint fk_stuinfo_major
#可加名字(fk_stuinfo_major) 也可不加 不加则使用系统默认的
名字
FOREIGN KEY(majorid)
REFERENCES major(id);
DESC stuinfo;#查看表头(列)约束
#三.修改表时删除约束
#1.删除非空约束(整个表的约束不是表头)
ALTER TABLE stuinfo
MODIFY COLUMN stuname
VARCHAR(20) NULL;#可以不写null 默认就是null
#2.删除默认约束(整个表的约束不是表头)
ALTER TABLE stuinfo
MODIFY COLUMN age INT;
#3.删除主键(整个表的约束不是表头)
ALTER TABLE stuinfo DROP PRIMARY KEY;
#或
ALTER TABLE stuinfo MODIFY COLUMN id INT;
#4.删除唯一(整个表的约束不是表头)
ALTER TABLE stuinfo DROP INDEX seat;
SHOW INDEX FROM stuinfo;#查看索引
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorId;
#题目
#1.向表emp2的id列中添加 primary key 约束 (my_emp_id_pk)
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
#此处将主键添加到列级约束上(不支持起名字)
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id PRIMARY KEY
(id);
#为表级约束添加主键 可起名字 但是没效果
#2.向表dept2的id列中添加 primary key 约束 (my_empt_id_pk)
#同上
#3.向表emp2中添加列 dept_id,并在其中定义 foreign key 约束
# 与之相关联的列是 dept2 表中的 id 列
ALTER TABLE emp2
ADD COLUMN dept_id
INT;
ALTER TABLE emp2
ADD CONSTRAINT fk_emp_dept2
FOREIGN KEY(dept_id)
REFERENCES dept2(id);
# 位置 支持的约束类型
是否可以起约束名
#列级约束: 列的后面 语法都支持,但外键没有效果
不可以
#表级约束: 所有列的下面 默认和非空不支持,其他都支持
可以(主键没有效果)
# 检查约束谁都不支持
#标识列
/*
又称为自增长列
含义:可以不用手动的插入值,
系统提供默认的序列值
(从1开始每次都增长1)
特点:
1.标识列必须和主键搭配吗?不一定,
但要求是一个key(主键/唯一键UNIQUE)
2.一个表中可以有几个标识列?
至多一个!
3.标识列的类型只能是数值型(int/float/douable)
4.标识列可以通过
#SET auto_increment_increment=3;
设置步长值
还可以通过手动插入第一行数据的方法
设置起始值
*/
#一.创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;#删除表(包括表头)
CREATE TABLE tab_identity(#创建表
id INT ,
#id int unique auto_increment,
#添加标识列 自动从1开始自增
#name varchar(20) unique auto_increment,
#name 无法作为自增列
NAME FLOAT,
seat INT
#seat INT UNIQUE AUTO_INCREMENT
#加两个自增长(报错)
);
TRUNCATE TABLE tab_identity;
#删除表的全部数据(除了表头)并重置标识自增断点
INSERT INTO tab_identity
VALUES(NULL,'john');
SELECT * FROM tab_identity;#查看表
SHOW VARIABLES LIKE '%auto_increment%';
#展示 约束 类似 自增长的 约束
#Variable_name Value
#auto_increment_increment 1 步长值 自增偏移量 设置
有效
#auto_increment_offset 1 自增起始值 设
置无效
SET auto_increment_increment=3;
#重新设置标识的自增数步长 为3
#二.修改表时设置标识列
ALTER TABLE tab_identity #选择表
MODIFY COLUMN id#表头
INT #数值型
PRIMARY KEY#主键
AUTO_INCREMENT;#标识自增
#二.修改表时删除标识列
ALTER TABLE tab_identity #选择表
MODIFY COLUMN id#表头
INT #数值型
#PRIMARY KEY#主键#避免和上一个重复主键
;#去掉标识自增即可
TRUNCATE TABLE tab_identity;
#删除表的全部数据(除了表头)并重置标识自增断点
INSERT INTO tab_identity
VALUES(NULL,'john');
SELECT * FROM tab_identity;#查看表
SHOW VARIABLES LIKE '%auto_increment%';
#展示 约束 类似 自增长的 约束
创建表时
COMMENT 注释 表头名字
SELECT UNIX_TIMESTAMP();查询时间戳
//开始 增删改查 实例
<?php
//1.连接 mysql
$a = @mysqli_connect('localhost', 'root', 'root',
'xinWen', 3306);
// ip/dns域名 用户名 密码 数据库 端口号(
默认)
// var_dump($a);
//连接成功 返回 object(mysqli)
//连接失败 语法报错 且返回 bool(false)
// 使用@ 抑制 语法报错 (错误抑制符只对表达式有效)
// 连接失败 专用函数 mysqli_connect_error()
// 可以返回 错误信息(英文)
//2.设置字符编码
if (mysqli_connect_error()) { //如果有错
echo '错误号 : ' . mysqli_connect_errno(), '<br>';
echo '错误信息 : ' . mysqli_connect_error();
} else {
//连接好了要设置字符编码
mysqli_set_charset($a, 'utf8');
echo '连接好了并设置字符编码为utf8';
}
//等价写法 以上还能这么写 (效果是一致的)
/*$a = @mysqli_connect('localhost', 'root', 'root',
'xinWen', 3306) or die('错误信息 :
'.mysqli_connect_error());
mysqli_query($link,'SET NAMES utf8');
*/
//set names utf8指定了客户端和服务器之间传递字符的编码规则
为UTF8。
//3. 执行 mysql 语句
//3.1 新增 插入行
$b = mysqli_query($a, "INSERT INTO news VALUES(NULL,'静
夜思','窗前明月光',UNIX_TIMESTAMP());");
//在php 中使用 mysql 插入一行测试数据
// 失败 返回 false
// 成功 返回 true (例外: select,show,describe,explain 返
回查询结果(对象))
if ($b != false) {
echo '<br>mysql语句 执行新增成功 新插入行自增id为' .
mysqli_insert_id($a);
// var_dump($b);//bool(true)
} else {
echo 'mysql语句 执行失败';
var_dump($b);
}
//3.2 修改 更新数据 把 id 为5 的 content 改为 凝似地上霜
$b2 = mysqli_query($a, "UPDATE news set content='凝似地
上霜3' where id=6; ");
//在php 中使用 mysql 插入一行测试数据
// 失败 返回 false
// 成功 返回 true (例外: select,show,describe,explain 返
回查询结果(对象))
if ($b2 != false) {
echo '<br>mysql语句 执行修改成功 受影响的记录数为' .
mysqli_affected_rows($a);
// var_dump($b);//bool(true)
} else {
echo '<br>mysql语句 执行失败 错误代码' .
mysqli_errno($a) . '<br>' . mysqli_error($a);
//与上面 连接错误 是两个 函数
//错误结果展示: mysql语句 执行失败 错误代码1064
// Unknown column 'ids' in 'where
clause'
// var_dump($b2);//bool(false)
}
//3.3 删除数据 把 id 为5 的 那一行数据删除
$b3 = mysqli_query($a, "DELETE FROM news where id=6; ");
//在php 中使用 mysql 插入一行测试数据
// 失败 返回 false
// 成功 返回 true (例外: select,show,describe,explain 返
回查询结果(对象))
if ($b3 != false) {
echo '<br>mysql语句 执行删除成功 受影响的记录数为' .
mysqli_affected_rows($a);
// var_dump($b);//bool(true)
} else {
echo '<br>mysql语句 执行失败 错误代码' .
mysqli_errno($a) . '<br>' . mysqli_error($a);
//与上面 连接错误 是两个 函数
//错误结果展示: mysql语句 执行失败 错误代码1064
// Unknown column 'ids' in 'where
clause'
// var_dump($b2);//bool(false)
}
//3.4 查询数据 查询 id 为9 的 那一行数据
$b4 = mysqli_query($a, "SELECT * FROM news ; ");
//在php 中使用 mysql 插入一行测试数据
// 失败 返回 false
// 成功 返回 true (例外: select,show,describe,explain 返
回查询结果(对象))
if ($b4 != false) {
echo '<br>mysql语句 执行查询成功 查询结果为';
// var_dump($b4); //查询的结果集(对象)
echo '<pre>';
//3.4.1 获取查询 结果集 为 索引数组
print_r(mysqli_fetch_row($b4)); //获取查询 结果集 为
索引数组
//只能一次获取一条
print_r(mysqli_fetch_row($b4)); //获取查询 结果集 为
索引数组
//第二次获取第二条
//因此 的确可以 通过while 循环来做 直到 函数获取为空
跳出循环
/**Array( 一条结果样例
[0] => 9
[1] => 静夜思
[2] => 窗前明月光
[3] => 1617955631) */
//3.4.2 获取查询结果集 为关联数组
echo '<pre>';
print_r(mysqli_fetch_assoc($b4));
//也是 只能一次获取一条
//第二次获取第二条
/**结果样例展示
*
Array
(
[id] => 3
[title1] => 静夜思
[content] => 窗前明月光
[createtime] => 1617954801
)
*/
//3.4.3 获取查询结果集 既有 关联数组 又有索引数组
echo '<pre>';
print_r(mysqli_fetch_array($b4));
//还是 只能一次获取一条
//第二次获取第二条
/**结果样例展示
*
Array
(
[0] => 5
[id] => 5
[1] => 静夜思
[title1] => 静夜思
[2] => 凝似地上霜3
[content] => 凝似地上霜3
[3] => 1617955114
[createtime] => 1617955114
)
*/
//3.4.4 获取查询结果集 的 列和行
echo '有几行' . mysqli_num_rows($b4) . '<br>';
echo '有几列' . mysqli_num_fields($b4) . '<br>';
/**
* 结果样例展示
* 有几行34
* 有几列4(专业术语 指每一个表头)
*/
//3.4.5 获取查询结果集 获取全部 数组(推荐 常用)
echo '<pre>';
print_r(mysqli_fetch_all($b4, MYSQLI_NUM)); //获取全
部索引数组(默认)
print_r(mysqli_fetch_all($b4, MYSQLI_ASSOC)); //获取
全部关联数组
print_r(mysqli_fetch_all($b4, MYSQLI_BOTH));
//获取全部 索引和关联 数组
/*
结果样例展示
Array
(
[0] => Array
(
[0] => 7
[1] => 静夜思
[2] => 窗前明月光
[3] => 1617955503
)
[1] => Array
(
[0] => 8
[1] => 静夜思
[2] => 窗前明月光
[3] => 1617955574
)
...
[30] => Array
(
[0] => 37
[1] => 静夜思
[2] => 窗前明月光
[3] => 1618016159
)
)
*/
// 最终. 销毁 结果集(你就算不销毁计算机也会帮你销毁)
mysqli_free_result($b4); //销毁 结果集
mysqli_close($a); //关闭 连接数据库通道
//(你就算不关闭计算机也会帮你关闭)
} else {
echo '<br>mysql语句 执行失败 错误代码' .
mysqli_errno($a) . '<br>' . mysqli_error($a);
//与上面 连接错误 是两个 函数
//错误结果展示: mysql语句 执行失败 错误代码1064
// Unknown column 'ids' in 'where
clause'
// var_dump($b2);//bool(false)
}
//3.2 修改 更新数据 把 id 为5 的 content 改为 凝似地上霜
$b2 = mysqli_query($a, "UPDATE news set content='凝似地
上霜3' where id=6; ");
//在php 中使用 mysql 插入一行测试数据
// 失败 返回 false
// 成功 返回 true (例外: select,show,describe,explain 返
回查询结果(对象))
if ($b2 != false) {
echo '<br>mysql语句 执行修改成功 受影响的记录数为' .
mysqli_affected_rows($a);
// var_dump($b);//bool(true)
} else {
echo '<br>mysql语句 执行失败 错误代码' .
mysqli_errno($a) . '<br>' . mysqli_error($a);
//与上面 连接错误 是两个 函数
//错误结果展示: mysql语句 执行失败 错误代码1064
// Unknown column 'ids' in 'where
clause'
// var_dump($b2);//bool(false)
}
//3.3 删除数据 把 id 为5 的 那一行数据删除
$b2 = mysqli_query($a, "DELETE FROM news where id=6; ");
//在php 中使用 mysql 插入一行测试数据
// 失败 返回 false
// 成功 返回 true (例外: select,show,describe,explain 返
回查询结果(对象))
if ($b2 != false) {
echo '<br>mysql语句 执行删除成功 受影响的记录数为' .
mysqli_affected_rows($a);
// var_dump($b);//bool(true)
} else {
echo '<br>mysql语句 执行失败 错误代码' .
mysqli_errno($a) . '<br>' . mysqli_error($a);
//与上面 连接错误 是两个 函数
//错误结果展示: mysql语句 执行失败 错误代码1064
// Unknown column 'ids' in 'where
clause'
// var_dump($b2);//bool(false)
}
?>
//结束 增删改查实例
2.操作数据
增,删,改 语句没有结果集的返回
需要手动查询
因此我们干脆
增,删,改 执行成功 返回true
执行失败 返回false
通常
我们通过
mysqli_query()
在php 中 执行 mysql 语句
由于 增删改查都要连数据库
所以
我们
连接数据库的代码
最好封装到一个包含文件里面去
在站点下 新建inc文件夹
在inc下创建 conn.php文件
连接数据库的代码写在这里
然后使用 包含
类似于 js 的import
下面重复介绍前面的包含
require './2.php'; //路径错误 停止执行当前页面的 往后代码
和
include './2.php'; //路径错误 依旧执行当前页面的 往后代码
require_once './2.PHP';
require_once './2.PHP';
//包含两次也只显示一次
最后
以上就是专注白开水为你收集整理的mysql全套理解以及在php中的应用的全部内容，希望文章能够帮你解决mysql全套理解以及在php中的应用所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
![[bzoj1225][DP][奇技淫巧]求正整数](/uploads/reation/bcimg8.png)
发表评论 取消回复