与MySQL的第一次亲密接触
1 数据库的相关介绍
1.1 数据库的好处
1、可以持久化数据到本地 2、结构化查询
1.2 数据库的常见概念 ★
? Web网页<<<服务器<<<数据库
1、DB(database):数据库,存储数据的容器
2、DBMS(DatabaseManagementSystem):数据库管理系统,又称为数据库软件或数据库产品,用于创建或管理DB【MySQL、Oracle(贵)、DB2(适合海量数据)、SqlServer(微软的)】
3、SQL(StructureQueryLanguage):结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,简单易学,功能强大,而是几乎所有的主流数据库软件通用的语言
4、DBA:专门用来管理数据库的人
1.3 数据库存储数据的特点
1、数据存放到表中,然后表再放到库中 2、一个库中可以有多张表,每张表具有唯一的表名用来标识自己 3、表中有一个或多个列,列又称为“字段”,相当于java中“属性” 4、表中的每一行数据,相当于java中“对象”
1.4 常见的数据库管理系统
mysql、oracle、db2、sqlserver
2 MySQL的介绍
2.1 MySQL的背景
前身属于瑞典的一家公司,MySQL AB
08年被sun公司收购,09年sun被oracle收购
2.2 MySQL的优点
1、开源、免费、成本低
2、性能高、移植性也好
3、体积小,便于安装
2.3 MySQL的安装
DBMS分为两类:
—基于共享文件系统的DBMS(Access)
—基于客户机——服务器的DBMS(mysql、oracle、sqlserver)
属于c/s架构的软件,一般来讲安装服务端
企业版(收费)
社区版(免费)
2.4 MySQL服务的启动和停止
方式一:通过命令行
net start 服务名
net stop 服务名
方式二:计算机**——右击——管理——**服务
2.5 MySQL服务的登录和退出
登录:mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码
退出:exit或ctrl+C
3 DQL语言(查询语言)
3.1 最基础
3.1.1 MySQL常见命令:
3.1.1.1 查看当前所有的数据库
show database;
3.1.1.2 打开指定的库
Use 库名;
3.1.1.3 查看当前库的所有表
Show tables;
3.1.1.4 查看其它库的所有表
Show tables from 库名;
3.1.1.5 创建表
Create table 表名(
列名 列类型,
列名 列类型,
...
);
3.1.1.6 查看表结构
Desc 表名;
3.1.1.7 查看服务器版本
方式一:登录到MySQL服务端
Select version();
方式二:没有登录到MySQL服务端
MySQL –version
或
Mysql –v
3.1.2 MySQL语法规范
1、 不区分大小写,但是建议关键字大写,表名、列名小写
2、 每条命令最好用分号结尾
3、 每条命令根据需要,可以伸缩
4、 注释:
单行注释:#文字、 --文字
多行注释:/注释文字/
3.2 基础查询
3.2.1 语法
select 查询列表
from 表名;
3.2.2 特点
3.2.3 进阶1:基础查询
语法:
SELECT 要查询的东西
【FROM 表名】;
? 类似于Java中 :System.out.println(要打印的东西);
特点:
3.2.3 示例
1、查询单个字段
select 字段名 from 表名;
SELECT last_name FROM employees;
2、查询多个字段
select 字段名,字段名 from 表名;
SELECT last_name,`first_name` FROM employees;(列名:着重号)
3、查询所有字段
select * from 表名
4、查询常量值
select 常量值;
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要
Select 100;
Select John;
5、查询函数
select 函数名(实参列表);
select version();
6、查询表达式
select 100/1234;
7、起别名
- as
Select 100%98 as 结果;
Select last_name as 姓,frist_name as 名 from employees;
- 空格
Select last_name 姓,frist_name 名 from employees;
案例:查询salary, 显示结果为 out put(这里有个空格)
Select salary as “out put” from employees;
8、去重
select distinct 字段名 from 表名;
案例:查询员工表中涉及的部门编号
Select distinct department_id from employees;
9、+
作用:做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值,
如果转换成功,则继续运算;(select ‘123’+90;)
否则转换成0,再做运算 (select ‘jhon’+90;)
select null+值;结果都为null
10、【补充】concat函数
功能:拼接字符
select concat(字符1,字符2,字符3,...);
concat(‘a’, ‘b’, ’c’) as 名称;
11、【补充】ifnull函数
功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
select ifnull(commission_pct,0) from employees;
12、【补充】isnull函数
**功能:**判断某字段或表达式是否为null,如果是,则返回1,否则返回0
**案例:**显示出表employees的全部列,各个列之间用逗号连接,列头显示成out_put
Select ifnull(commission_pct,0) as 奖金率, commission_pct from employees;
Select concat(frist_name, ’,’,’last_name’, ‘,’, ifnull(commission_pct,0) )as out_put;
3.3 条件查询
3.3.1 语法
3.3.1.1 进阶2:条件查询
? 条件查询:根据条件过滤原始表的数据,查询到想要的数据
? 语法:
select
? 要查询的字段|表达式|常量值|函数
? from
? 表
? where
? 条件 ;
select 查询列表(third step)
from 表名(frist step)
where 筛选条件(second step)
3.3.2 筛选条件的分类
1 简单条件运算符
\> 、 < 、 = 、!=(<>) 、>= 、 <= 、 <=> (安全等于)
案例: Select * from employees where department_id > 90;
2 逻辑运算符
&& 或and : 两个结果都为true,结果为true,反之为false
Select last_name, salary, commission_pct from employees where salary>= 10000 and salary <= 20000;
|| 或or : 只要一个结果为true,结果为
案例:
Select * from employees where department_id < 90 or department_id > 110 or salary >= 15000;
Select * from employees where not(department_id >= 90 and department_id <= 110) or salary >= 15000;
! 或not
3 模糊查询
-
like :一般搭配通配符使用,可以判断字符型或数值型 案例1:查询员工名中名称有a的员工信息 Select * from employees
where lastname
like ‘%a%’( %a%:%表示还有其他字符)
-
通配符:%任意多个字符,_任意单个字符 案例2:查询员工名中第三个字符为e的员工信息 Select * from employees
where lastname ‘__e_1’(第三个为e,第五个为l)
案例3:查询员工名称中含有第二个字符为_的员工名 Select * from employees
where lastname ‘_$_’
escape ‘&’(转译$_为一体,表示第二个是特殊符号)
-
between and (提高简洁度) 案例1:查询员工编号在100和120之间 SELECT * FROM employees
WHERE employee_id >= 100 AND employee_id <= 120;
简化为如下: SELECT * FROM employees
WHERE employee_id
BETWEEN 100 AND 120;(包含临界值,两个临界值不可随意调换)
-
in 含义:判断某字段的值是否属于in列表中的某一项 特点:
1、使用可以提高简洁读 2、in列表值类型要一致或兼容
案例:查询员工编号是it1、ad、ads中的一个员工和工种编号 Select lastname, jobid form employees
where jobid = ‘it1’ or jobid = ‘ad’ or jobid =’ads’;
可以简化为: Select lastname, jobid form employees
where jobid in (‘it1’ , ‘ad’ , ’ads’);
5、 is null /is not null:用于判断null值
案例1:查询没有奖金的员工名和奖金率
Select lastname, commission_pct from employees where commission_pct is null;
Select lastname, commission_pct from employees where commission_pct <=> null;
is null PK **<=>****(**安全等于)(推荐is null)
? 普通类型的数值 null****值 可读性
is null × √ √
<=> √ √ ×
3.33 作业
3.4 排序查询
3.4.1 语法
引入:
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;#降序
SELECT * FROM employees ORDER BY salary ASC;#升序
#案例2:查询员工编号>=90的员工信息,按入职时间的先后进行排序
SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;
#案例3:按年薪的高低显示员工的信息和年薪【安表达式排序】
SELECT ,salary12*(1 + IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary12(1 + IFNULL(commission_pct,0)) DESC;
#案例4:按年薪的高低显示员工的信息和年薪 【按别名排序】
SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROM employees ORDER BY 年薪 DESC;
#案例5:按姓名的长度来查询显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) 字节长度, last_name, salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
#案例6:查询员工信息,要求先按工资升序,在按员工编号降序【安多个字段排序】
SELECT *
FROM employees
ORDER BY salary ASC, employee_id DESC;
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc}desc】
3.4.2 特点
1、asc :升序,如果不写默认升序
desc:降序
2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后(除limit语句之外)
3.4.3 作业
#问题1:查询员工的姓名、部门号和年薪,【按年薪降序,按姓名升序】
SELECT last_name, department_id, salary12(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary DESC , last_name ASC;
#问题2:选择工资不在8000到17000的员工的姓名和工资 【按工资降序】
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#问题3:查询邮箱中包含e的员工信息,并按邮箱的字节数来降序,在按部门号升序
SELECT *
FROM employees
WHERE email LIKE ‘%e%’
ORDER BY LENGTH(email) DESC, department_id ASC;
3.5 常见函数
3.5.1 概述
#-----------进阶4:常见函数----------------
/*
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1、隐藏了实现细节 2、提高代码的重用性
调用:select函数名(实参列表)【from 表】;
特点:1:函数叫什么(函数名)
2:干什么(函数功能)
分类:1:单行函数
如:concat、length、ifnull等
2、分组函数
功能:做统计使用,又称为统计函数、聚合函数、组函数
*/
功能:类似于java中的方法
好处:提高重用性和隐藏实现细节
调用:select 函数名(实参列表);
3.5.2 单行函数
1 字符函数
#1、length 获取参数值的字节个数
SELECT LENGTH(‘jhon’);#4
SELECT LENGTH(‘张三丰hahaha’);#15,一个汉字3个字节,一个英语1个字节
#2、concat 拼接字符串
SELECT CONCAT(last_name,’_’,first_name) 姓名 FROM employees;
#3、upper、lower
SELECT UPPER(‘jhon’);
SELECT LOWER(‘jhon’);
#示例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name), LOWER(first_name)) 姓名 FROM employees;
#4、substr、substring
注意:索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR(‘李莫愁爱上了路张源’, 7) out_put;#路张源
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR(‘李莫愁爱上了路张源’, 1, 3) out_put;#李莫愁
#案例:姓名中首字母大写,其他字母小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)), LOWER(SUBSTR(last_name, 2))) out_put FROM employees;
#5、instr 返回字符串第一次出现的索引,如果找不到返回0
SELECT INSTR(‘杨不悔爱上了段柳霞’, ‘段柳霞’) AS out_put;
#6、trim 去除两边的空格,还可是字母
SELECT LENGTH(TRIM(’ 张翠山 ')) AS out_put;9#张翠山
SELECT TRIM(‘a’ FROM ‘aaaaaaa张aaaaaaaa翠aaaaaaaaaaaaa山’) AS out_put;#张aaaaaaaa翠aaaaaaaaaaaaa山
#7、lpad 用指定的字符实现左填充指定长度,使两者达到第二个数的长度
SELECT LPAD(‘殷素素’, 10, ‘*’) AS out_put;#*******殷素素
SELECT LPAD(‘殷素素’, 2, ‘*’) AS out_put;#殷素
#8、rpad 用指定的字符实现右填充指定长度,使两者达到第二个数的长度
SELECT RPAD(‘殷素素’, 10, '’) AS out_put;#殷素素******
#9、replace 第三个替换第二个字符
SELECT REPLACE(‘周芷若周芷若张无忌爱上了周芷若’,‘周芷若’, ‘赵明’) AS out_put;
2 数学函数
#1、round 代表四拾伍入,正负都可以
SELECT ROUND(1.65);#2
SELECT ROUND(1.657, 2);#1.66,小数点保留两位
#2、ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.02);#1
#3、floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);#-10
#4、truncate 截断
SELECT TRUNCATE(1.69999,1);#1.6,小数点保留1位
#5、mod 取余
SELECT MOD(10,3);#1==10%3,如果后面为负数,那最后结果只与第一个数有关
3 日期函数
#1、now 返回当前日期+时间
SELECT NOW();#2021-03-17 13:28:46
#2、curdate 返还当前系统日期,不包含时间
SELECT CURDATE();#2021-03-17
#3、可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR(‘1988-1-1’) 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
#4、str_to_date 将字符通过指定的格式装换为日期
SELECT STR_TO_DATE(‘1999-12-1’, ‘%Y-%c-%d’) AS out_put;# 1999-12-01
#5、查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = ‘1992-4-3’;
SELECT * FROM employees WHERE hiredate = STR_TO_DATE(‘4-3 1992’, ‘%c-%d %Y’);
#6、date_fromat 将日期转换成字符
SELECT DATE_FORMAT(NOW(), ‘%y年%m月%d日’) AS out_put;
#7、查询有奖金的员工姓名和入职日期(xx月/xx日 xx年)
SELECT last_name, DATE_FORMAT(hiredate, ‘%y年%m月 %d日’) FROM employees
WHERE commission_pct IS NOT NULL;
4 其他函数
version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password(‘字符’):返回该字符的密码形式
md5(‘字符’):返回该字符的md5加密形式
SELECT VERSION();
SELECT DATABASE();
SELECT USER();#root@localhost
5 流程控制函数
①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、if函数:实现if else 的效果
SELECT IF(10<5, ‘大’,‘小’);
SELECT last_name, commission_pct, IF(commission_pct IS NULL, ‘没奖金,呵呵’, ‘有奖金,嘻嘻’) AS 备注
FROM employees;
#2、case函数的使用一: switch case 效果
/*
JAVA中:
switch(变量或表达式){
case 常量1: 语句1;
break;
…
default: 语句n;
break;
}
mysql中:
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 原始工资, 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 最终工资
FROM employees;
#3、case函数的使用二: 类似于 多重 if
/*
java中
if(){
}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 ‘D’
END AS 工资级别
FROM employees;
3.5.3 分组函数
1 分类
/*
功能: 用于统计使用,又称为聚合函数或统计函数或组函数
分类:
sum 求和、 avg 平均数、 max 最大数、 min 最小数、 count 计算个数
特点:
1、sum、 avg一般用于处理数值型
2、以上分组函数是忽略null值
3、可以使用distinct关键字
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by 后的字段
*/
\select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
#1、简单函数的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和, AVG(salary), MIN(salary), MAX(salary), COUNT(salary)
FROM employees;(多个函数)
SELECT SUM(salary) 和, ROUND(AVG(salary),2), MIN(salary), MAX(salary), COUNT(salary) FROM employees;(嵌套函数)
#2、参数支持哪些类型
SELECT SUM(last_name)【没有意义】, AVG(last_name)【没有意义】 FROM employees;
#3、忽略null
SELECT SUM(commission_pct)
#4、和distinct搭配
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;
#5、count函数的详细介绍
SELECT COUNT(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT COUNT(2(1也可以,可以加常量值)) FROM employees;#类似于加了一列1到新的一行,然后对1的个数求和,最终得出总行数
#效率
MYISAM 存储引擎下, COUNT(*)的效率更高点
INNODB 存储引擎下, COUNT(1)和 COUNT(*)的效率差不多,比 COUNT(字段)更高一点
#6、和分组函数一同查询的字段有限制
SELECT AVG(salary), employee_id FROM employees;
Avg(salary) employees
6461.682243(单用avg也是如此) 100
max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数
2 特点
①语法
select max(字段) from 表名;
②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
③以上分组函数都忽略null
④都可以搭配distinct使用,实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count(字段):统计该字段非空值的个数
count(*):统计结果集的行数
案例:查询每个部门的员工个数
1 xx 10
2 dd 20
3 mm 20
4 aa 40
5 hh 40
count(1):统计结果集的行数
效率上:
MyISAM存储引擎,count(*)最高
InnoDB存储引擎,count(*)和count(1)效率>count(字段)
⑥ 和分组函数一同查询的字段,要求是group by后出现的字段
3.5.4 作业
#问题1:显示系统时间(注:日期+时间)
SELECT NOW();
#问题2:查询员工号,姓名,工资,以及工资提高百分之20后的结果(new salary)
SELECT employee_id,last_name, salary, salary*1.2 “new salary”
FROM employees;
#问题3:将员工的姓名按字母排序,并写出姓名的长度(length)
SELECT LENGTH(last_name),SUBSTR(last_name, 1, 1) 首字符, last_name
FROM employees
ORDER BY 首字符;
#问题4:做一个查询,产生下面结果
<last_name> earns 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 )
FROM employees;
#问题5:使用case-when,按照下面的条件
使用 case-when,按照下面的条件:
job grade A
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 jobid job,
case jobid
when ‘AD_PRES’ then ‘A’
when ‘ST_MAN’ then ‘B’
when ‘IT_PROG’ then ‘C’
END AS GRADE
FROM EMPLOYEE
WHERE JOBID = ‘A’;
3.6 分组查询
3.6.1 语法
#进阶5:分组查询
/*
语法:
? select 分组函数, 列(要求出现在group by的后面)
? from 表
? 【where 筛选条件】
? group by 分组的列表
? 【order by 子句】
注意:
? 查询表格必须特殊,要求是分组函数和group by 后面出现的字段
特点:
? 1、分组查询的筛选条件分为两类
? 筛选源 位置 关键字
? 分组前筛选 原始源 group by子句前面 where
? 分组后筛选 分组后的结果源 group by子句后面 having
注意:
? 1、分组函数做条件肯定是放在having子句后面
? 2、能用分组前筛选就用分组前(考虑到性能问题)比如一开始在manager_id,最后又使用manager_id
? 3、group by 子句支持单个子段,多个字段分组(多个子段之间用逗号隔开没有顺序要求),表达式或函数(用的较少)
? 4、也可以添加排序(排序放在整个分组排序的最后)
*/
#引入:查询每个部门的平均工资
SELECT AVG(salary) FROM employees;
#案例1:查询每个工种的最高工资
SELECT MAX(salary), job_id(按照什么查询,就可以出现什么)
FROM employees
GROUP BY job_id;
这是为了查询每一组的最大值
#案例2:查询每个位置上的部门个数
SELECT COUNT(*), location_id
FROM departments
GROUP BY location_id;
#添加筛选条件
#案例1:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
WHERE email LIKE “%a%”(先进行)
GROUP BY department_id;
#案例2:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary) , manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#添加复杂的筛选条件
#案例1:查询那个部门的员工个数>2
#1、查询每个部门的员工个数
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id;
#2、根据1的查询结果筛选,查选那个部门的员工个数
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#1、查询每个工种有奖金的员工的最高工资
commission_pct IS NOT NULL
SELECT MAX(salary) , job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
#2根据1的结果继续筛选,最高工资>12000
SELECT MAX(salary) , job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;
#案例3:查询领导编号>102的每一位领导手下的最低工资>5000是哪个,以及其最低工资
#查询领导编号>102的每一位领导手下的最低工资
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;
SELECT salary, manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;
#按表达式或函数分组
#案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
#1、查询每个长度的员工个数
SELECT COUNT(*), LENGTH(last_name) 长度
FROM employees
GROUP BY 长度(别名Oracle和其他不支持,不怎么常用)
HAVING COUNT(*) > 5;
#按多个字段分组
#案例:查询每个部门每个工种的员工的平均工资
SELECT AVG(salary), department_id, job_id
FROM employees
GROUP BY job_id,department_id;(前后颠倒也是一样)
#添加排序
#案例:查询每个部门每个工种的员工的平均工资,并按平均工资的高低来排序
SELECT AVG(salary), department_id, job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY job_id,department_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary) DESC;(降序是低的在下面)
3.6.2 特点
使用关键字 筛选的表 位置
分组前筛选 where 原始表 group by****的前面
分组后筛选 having 分组后的结果 group by 的后面
3.6.3 作业
#问题1:查询公司的工资的最大值、最小值、平均值和总和
SELECT MIN(salary), MAX(salary) , AVG(salary), SUM(salary) FROM employees;
#问题2:查询员工表中最大入职时间和最小入职时间的相差天数(difference)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate) ) difference FROM employees;
#问题3:查询部门编号为90的员工个数
SELECT COUNT(*) 个数
FROM employees
WHERE department_id = 90;
#问题:
#1、查询各job_id的员工工资的最大值,最小值,平均和,总和,并按job_id升序
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;
#2、查询员工最高工资和最低工资的差距(difference)
SELECT MAX(salary)-min(salary) difference from employees
Group by manager_id;
#3、查询各个管理者手下员工的最低工资,其中最低工资不能低于6000, 没有管理者的员工不计算在内
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >=6000;
#4、查询所有部门的编号,员工数量和平均工资,并按平均工资降序
Select depa
#5、选择具有各个job_id的员工人数
3.7 连接查询
3.7.1 含义
#进阶6:连接查询
/*
含义:多表查询,当查询的字段来自于多个表时,就会有连接查询
? 笛卡尔乘积现象:表1有m行,表2有n行,所以最终结果为m*n行
? 发生原因:没有有效的连接条件
? 如何避免:添加有效的连接条件
? 分类:
? 按年代分类:
? sql92标准 仅仅支持内连接
? sql99标准【推荐】 支持内连接+外连接(左外和右外)+交叉连接
?
? 按功能分类:
? 内分类:
? 等值分类
? 非等值连接
? 自连接
? 外连接:
? 左外连接
? 右外连接
? 全外连接
? 交叉连接:
*/
SELECT * FROM beauty;
SELECT NAME, boyName;(结果发生错误)
##迪卡尔成积现象(未添加任何筛选条件,就进行筛选,导致其目标数据库内所有数据都符合条件。所以,要添加有效条件)
#笛卡尔的错误现象:
SELECT COUNT(*) FROM beauty;
#假设输出12行
SELECT COUNT(*) FROM boys;
#假设输出4行
#最终结果为:12*4=48
#上面的正解
SELECT NAME, boyName FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;
----------------------------------------------------------------------------------------71P
#一、sql92标准
##---------------------------------1、等值连接
#------------------------------
? /*
? 1、多表等值连接的结果为多表的交集部分
? 2、n表连接,至少需要n-1个连接条件
? 3、多表的顺序没有要求
? 4、一般需要为表取别名
? 5、可以搭配前面介绍的所有子句使用,比如:排序、分组、筛选
? */
#------------------------------
#案例1:查询男女两方的配对关系、
SELECT NAME, boyName FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;
#案例2:查询员工名和对应的部门名
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id =departments.department_id ;
#2、为表起别名
/*
? 1、提高语句简洁度
? 2、区分多个重名的字段
? 注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定它
? 重复命名
*/
#查询员工名、工种号、工种名
SELECT last_name,employees.job_id, job_title
FROM employees, jobs
WHERE employees.job_id = jobs.job_id ;
#3、两个表的顺序是否可以调换 ==== 可以
SELECT last_name,employees.job_id, job_title
FROM employees, jobs
WHERE jobs.job_id = employees.job_id ;
#4、可以加筛选吗?
#案例:查询有奖金的员工名、部门号
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:查询城市名中第二个字符为0的部门号和城市名
SELECT department_name, city
FROM departments d, locations l
WHERE d.location_id = l.location_id
AND city LIKE ‘_o%’;
#5、可以加分组?
#案例1:查询每个城市的部门个数
SELECT COUNT(*) 个数, city
FROM departments d, locations l
WHERE d.department_id = l.location_id
GROUP BY city;
SELECT * FROM departments;
SELECT * FROM locations;
#案例2:查询有奖金的每个部门的部门号和部门的领导编号和该部门的最低工资
SELECT department_name, d.manager_id,MIN(salary)
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name;
#6、可以加排序吗?
#案例:查询每个工种的工种名和员工的个数,并按照员工个数来降序
SELECT job_title, COUNT(*)
FROM employees e , jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#7、是否实现三表连接
#案例:查询员工名、部门名和所在城市
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
AND city LIKE ‘s%’;
##---------------------------------2、非等值查询
#案例1:查询员工工资和工资级别
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 e.employee_id,e.last_name,m.manager_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id ;
#问题1:显示员工表的最大工资,平均工资
? SELECT MAX(salary), AVG(salary)
? FROM employees;
#问题2:查询工资表的employee_id, job_id ,last_name,按照department_id降序,salary升序
? SELECT employee_id, job_id , last_name
? FROM employees
? ORDER BY department_id DESC, salary ASC;
#问题3:查询员工表job_id中包含a和e的,并且a在e前面的
SELECT job_id FROM employees WHERE job_id LIKE ‘%a%e%’;
#问题4:
SELECT NAME,
#问题5:显示当前日期,以及去除前后空格,截取子字符的函数
SELECT NOW()
SELECT TRIM(字符 FROM ‘’);
SELECT SUBSTR(str, startindex);
SELECT SUBSTR(srt, startIndex, LENGTH);\
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2
from 表1,表2,…;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
如何解决:添加有效的连接条件
3.7.2 分类
按年代分类:
sql92:
等值
非等值
自连接
也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
sql99【推荐使用】
内连接
? 等值
? 非等值
? 自连接
外连接
? 左外
? 右外
? 全外(mysql不支持)
交叉连接
3.7.3 SQL92语法
1 等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点:
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
2 非等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3 自连接
语法:
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
4 作业
#问题1:显示员工表的最大工资,平均工资
SELECT MAX(salary), AVG(salary)
FROM employees;
#问题2:查询工资表的employee_id, job_id ,last_name,按照department_id降序,salary升序
SELECT employee_id, job_id , last_name
FROM employees
ORDER BY department_id DESC, salary ASC;
#问题3:查询员工表job_id中包含a和e的,并且a在e前面的
SELECT job_id FROM employees WHERE job_id LIKE ‘%a%e%’;
#问题5:显示当前日期,以及去除前后空格,截取子字符的函数
SELECT NOW()
SELECT TRIM(字符 FROM ‘’);
SELECT SUBSTR(str, startindex);
SELECT SUBSTR(srt, startIndex, LENGTH);\
#…又一次作业
#1、显示所有员工的姓名,部门号和部门名称
USE myemployees;
SELECT last_name, d.department_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id ;
#2、查询90号部门员工的job_id和90号部门的location——id
SELECT job_id, location_id
FROM employees e, departments d
WHERE e.department_id = 90 = d.department_id
AND e.department_id = 90;
#3、选择所有奖金的员工的last_name、city、 location_id, department_name
SELECT last_name, department_name ,location_id, city
FROM employees e, departments d,location_id l
Where e.department_id = d.department_id
And d.department_id = l.location_id
And e.commission_pct is not null;
#4、选择city在多伦多工作的员工的last_name、 departmemnt_id、job_id、department_name
SELECT last_name, job_id, d.department_id, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city = ‘toronto’;
#5.查询每一个工种、每一个部门部门号、工种名和最低工资
SELECT department_name, job_title, MIN(salary) 最低工资
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY department_name, job_title;
#6.查询下每个国家部门下个数大于2的国家编号
SELECT country_id, COUNT(*) 部门个数
FROM departments d, locations l
WHERE d.department_id = l.location_id
GROUP BY country_id
HAVING 部门编号>2;
#7.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果如下面的格式
employees Emp# manager mgr#
kochhar 101 king 100
SELECT e.last_name, e.employee_id “Emp#”, m.last_name manager, m.employee_id “mgr#”
FROM employees e, employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = ‘kochhar’;
3.7.4 SQL99语法
1 内连接
语法:
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
分类:
等值连接
非等值连接
自连
2 外连接
语法:
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
②left join 左边的就是主表,right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行
3 交叉连接
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
特点:
类似于笛卡尔乘积
3.8 子查询
3.8.1 含义
嵌套在其他语句内部的select语句称为子查询或内查询,
外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多
外面如果为select语句,则此语句称为外查询或主查询
3.8.2 分类
1、按出现位置
select后面:
仅仅支持标量子查询
from后面:
表子查询
where或having后面:
标量子查询
列子查询
行子查询
exists后面:
标量子查询
列子查询
行子查询
表子查询
2、按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为多行多列
表子查询:结果集为多行多列
3.8.3 示例
where或having后面
1、标量子查询
案例:查询最低工资的员工姓名和工资
①最低工资
select min(salary) from employees
②查询员工的姓名和工资,要求工资=①
select last_name,salary
from employees
where salary=(
select min(salary) from employees
);
2、列子查询
案例:查询所有是领导的员工姓名
①查询所有员工的 manager_id
select manager_id
from employees
②查询姓名,employee_id属于①列表的一个
select last_name
from employees
where employee_id in(
select manager_id
from employees
);
3.9 分页查询
3.9.1 应用场景
当要查询的条目数太多,一页显示不全
3.9.2 语法
select 查询列表
from 表
limit 【offset,】size;
注意:
offset代表的是起始的条目索引,默认从0卡死
size代表的是显示的条目数
公式:
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;
3.10 联合查询
3.10.1 含义
union:合并、联合,将多次查询结果合并成一个结果
3.10.2 语法
查询语句1
union 【all】
查询语句2
union 【all】
…
3.10.3 意义
1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候,查询的列基本是一致
3.10.4 特点
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重,union all包含重复项
查询总结
语法:
select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引,条目数; ⑨
4DML语言
插入
一、方式一
语法:
insert into 表名(字段名,…) values(值,…);
特点:
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段,注意可以通过以下两种方式插入null值
①字段和值都省略
②字段写上,值使用null
4、字段和值的个数必须一致
5、字段名可以省略,默认所有列
二、方式二
语法:
insert into 表名 set 字段=值,字段=值,…;
两种方式 的区别:
1.方式一支持一次插入多行,语法如下:
insert into 表名【(字段名,…)】 values(值,…),(值,…),…;
2.方式一支持子查询,语法如下:
insert into 表名
查询语句;
修改
一、修改单表的记录 ★
语法:update 表名 set 字段=值,字段=值 【where 筛选条件】;
二、修改多表的记录【补充】
语法:
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;
删除
方式一:使用delete
一、删除单表的记录★
语法:delete from 表名 【where 筛选条件】【limit 条目数】
二、级联删除[补充]
语法:
delete 别名1,别名2 from 表1 别名
inner|left|right join 表2 别名
on 连接条件
【where 筛选条件】
方式二:使用truncate
语法:truncate table 表名
两种方式的区别【面试题】★
1.truncate删除后,如果再插入,标识列从1开始
delete删除后,如果再插入,标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚
4.1 库的管理
一、创建库
create database 【if not exists】 库名【 character set 字符集名】;
二、修改库
alter database 库名 character set 字符集名;
三、删除库
drop database 【if exists】 库名;
4.2 表的管理
一、创建表 ★
create table 【if not exists】 表名(
字段名 字段类型 【约束】,
字段名 字段类型 【约束】,
。。。
字段名 字段类型 【约束】
)
二、修改表
1.添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;
三、删除表
drop table【if exists】 表名;
四、复制表
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧表【where 筛选】;
4.3 数据类型
一、数值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特点:
①都可以设置无符号和有符号,默认有符号,通过unsigned设置无符号
②如果超出了范围,会报out or range异常,插入临界值
③长度可以不指定,默认会有一个长度
长度代表显示的最大宽度,如果不够则左边用0填充,但需要搭配zerofill,并且默认变为无符号整型
2、浮点型
定点数:decimal(M,D)
浮点数:
float(M,D) 4
double(M,D) 8
特点:
①M代表整数部位+小数部位的个数,D代表小数部位
②如果超出范围,则报out or range异常,并且插入临界值
③M和D都可以省略,但对于定点数,M默认为10,D默认为0
④如果精度要求较高,则优先考虑使用定点数
二、字符型
char、varchar、binary、varbinary、enum、set、text、blob
char:固定长度的字符,写法为char(M),最大长度不能超过M,其中M可以省略,默认为1
varchar:可变长度的字符,写法为varchar(M),最大长度不能超过M,其中M不可以省略
三、日期型
year年
date日期
time时间
datetime 日期+时间 8
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响,更能反映当前时区的真实时间
4.4 常见的约束
一、常见的约束
NOT NULL:非空,该字段的值必填
UNIQUE:唯一,该字段的值不可重复
DEFAULT:默认,该字段的值不用手动插入有默认值
CHECK:检查,mysql不支持
PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null
FOREIGN KEY:外键,该字段的值引用了另外的表的字段
主键和唯一
1、区别:
①、一个表至多有一个主键,但可以有多个唯一
②、主键不允许为空,唯一可以为空
2、相同点
都具有唯一性
都支持组合键,但不推荐
外键:
1、用于限制两个表的关系,从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求
3、主表的被引用列要求是一个key(一般就是主键)
4、插入数据,先插入主表
删除数据,先删除从表
可以通过以下两种方式来删除主表的记录
方式一:级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
方式二:级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
二、创建表时添加约束
create table 表名(
字段名 字段类型 not null,#非空
字段名 字段类型 primary key,#主键
字段名 字段类型 unique,#唯一
字段名 字段类型 default 值,#默认
constraint 约束名 foreign key(字段名) references 主表(被引用列)
)
注意:
? 支持类型 可以起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以,但对主键无效
列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求
三、修改表时添加或删除约束
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;
四、自增长列
特点:
1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1
auto_increment_increment
如果要更改起始值:手动插入值
如果要更改步长:更改系统变量
set auto_increment_increment=值;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key
例外:
一、创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
)
二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment
三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束
5 TCL语言
一、含义
事务:一条或多条sql语句组成一个执行单位,一组sql语句要么都执行要么都不执行
二、特点(ACID)
A 原子性:一个事务是不可再分割的整体,要么都执行要么都不执行
C 一致性:一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性:一个事务不受其他事务的干扰,多个事务互相隔离的
D 持久性:一个事务一旦提交了,则永久的持久化到本地
三、事务的使用步骤 ★
了解:
隐式(自动)事务:没有明显的开启和结束,本身就是一条事务可以自动提交,比如insert、update、delete
显式事务:具有明显的开启和结束
使用显式事务:
①开启事务
set autocommit=0;
start transaction;#可以省略
②编写一组逻辑sql语句
注意:sql语句支持的是insert、update、delete
设置回滚点:
savepoint 回滚点名;
③结束事务
提交:commit;
回滚:rollback;
回滚到指定的地方:rollback to 回滚点名;
四、并发事务
1、事务的并发问题是如何发生的?
多个事务 同时 操作 同一个数据库的相同数据时
2、并发问题都有哪些?
脏读:一个事务读取了其他事务还没有提交的数据,读到的是其他事务“更新”的数据
不可重复读:一个事务多次读取,结果不一样
幻读:一个事务读取了其他事务还没有提交的数据,只是读到的是 其他事务“插入”的数据
3、如何解决并发问题
通过设置隔离级别来解决并发问题
4、隔离级别
? 脏读 不可重复读 幻读
read uncommitted:读未提交 × × ×
read committed:读已提交 √ × ×
repeatable read:可重复读 √ √ ×
serializable:串行化 √ √ √
6 其他
6.1 视图
一、含义
mysql5.1版本出现的新特性,本身是一个虚拟表,它的数据来自于表,通过执行时动态生成。
好处:
1、简化sql语句
2、提高了sql的重用性
3、保护基表的数据,提高了安全性
二、创建
create view 视图名
as
查询语句;
三、修改
方式一:
create or replace view 视图名
as
查询语句;
方式二:
alter view 视图名
as
查询语句
四、删除
drop view 视图1,视图2,…;
五、查看
desc 视图名;
show create view 视图名;
六、使用
1.插入
insert
2.修改
update
3.删除
delete
4.查看
select
注意:视图一般用于查询的,而不是更新的,所以具备以下特点的视图都不允许更新
①包含分组函数、group by、distinct、having、union、
②join
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图
七、视图和表的对比
关键字 是否占用物理空间 使用
视图 view 占用较小,只保存sql逻辑 一般用于查询
表 table 保存实际的数据 增删改查
6.2 变量
分类
一、系统变量
说明:变量由系统提供的,不用自定义
语法:
①查看系统变量
show 【global|session 】variables like ‘’; 如果没有显式声明global还是session,则默认是session
②查看指定的系统变量的值
select @@【global|session】.变量名; 如果没有显式声明global还是session,则默认是session
③为系统变量赋值
方式一:
set 【global|session 】 变量名=值; 如果没有显式声明global还是session,则默认是session
方式二**:**
set @@global.变量名=值;
set @@变量名=值;
1**、全局变量**
服务器层面上的,必须拥有super权限才能为系统变量赋值,作用域为整个服务器,也就是针对于所有连接(会话)有效
2、会话变量
服务器为每一个连接的客户端都提供了系统变量,作用域为当前的连接(会话)
二、自定义变量
说明:
1、用户变量
作用域:针对于当前连接(会话)生效
位置**:begin end里面,**也可以放在外面
使用**:**
①声明并赋值:
**set @变量名=值;**或
**set @变量名:=值;**或
select @变量名:=值;
②****更新值
方式一**:**
**set @变量名=值;**或
**set @变量名:=值;**或
select @变量名:=值;
方式二**:**
select xx into @变量名 from 表;
③****使用
select @变量名;
2**、局部变量**
作用域**:仅仅在定义它的begin end****中有效**
位置**:只能放在begin end中,**而且只能放在第一句
使用**:**
①****声明
declare 变量名 类型 **【default 值】**;
②****赋值或更新
方式一**:**
set 变量名**=值;**或
set 变量名:=值;或
select @变量名:=值;
方式二:
select xx into 变量名 from 表;
③使用
select 变量名;
6.3 存储关系和函数
说明:都类似于java中的方法,将一组完成特定功能的逻辑语句包装起来,对外暴露名字
好处:
1、提高重用性
2、sql语句简单
3、减少了和数据库服务器连接的次数,提高了效率
存储过程
一、创建 ★
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
存储过程体
end
注意:
1.参数模式:in、out、inout,其中in可以省略
2.存储过程体的每一条sql语句都需要用分号结尾
二、调用
call 存储过程名(实参列表)
举例:
调用in模式的参数:call sp1(‘值’);
调用out模式的参数:set @name; call sp1(@name);select @name;
调用inout模式的参数:set @name=值; call sp1(@name); select @name;
三、查看
show create procedure 存储过程名;
四、删除
drop procedure 存储过程名;
函数
一、创建
create function 函数名(参数名 参数类型) returns 返回类型
begin
函数体
end
注意:函数体中肯定需要有return语句
二、调用
select 函数名(实参列表);
三、查看
show create function 函数名;
四、删除
drop function 函数名;
6.4 流程控制结构
说明:
顺序结构:程序从上往下依次执行
分支结构:程序按条件进行选择执行,从两条或多条路径中选择一条执行
循环结构:程序满足一定条件下,重复执行一组语句
分支结构:
特点:
1、if函数
功能:实现简单双分支
语法:
if(条件,值1,值2)
位置:
可以作为表达式放在任何位置
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 [case];
位置:
可以放在任何位置,
如果放在begin end 外面,作为表达式结合着其他语句使用
如果放在begin end 里面,一般作为独立的语句使用
3、if结构
功能:实现多分支
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;
位置:
只能放在begin end中
循环结构
位置:
只能放在begin end中
特点:都能实现循环结构
对比:
①这三种循环都可以省略名称,但如果循环中添加了循环控制语句(leave或iterate)则必须添加名称
②
loop 一般用于实现简单的死循环
while 先判断后执行
repeat 先执行后判断,无条件至少执行一次
1、while
语法:
【名称:】while 循环条件 do
循环体
end while 【名称】;
2、loop
语法:
【名称:】loop
循环体
end loop 【名称】;
3、repeat
语法:
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;
二、循环控制语句
leave:类似于break,用于跳出所在的循环
iterate:类似于continue,用于结束本次循环,继续下一次
|