mysql基础经典版示例
一、使用数据库的优点
将数据存入内存中：数组 、集合（内存的缺点：易失性----断电数据就没了）
永久存储：文件（缺点：查询比较困难）
. . .
2.数据库的好处
-
实现数据持久化
-
使用完整的管理系统统一管理，易于查询
二、数据库的相关概念
1.DB
数据库（database）:存储数据的“仓库”。是保存一组有组织的数据的容器。
2.DBMS（指数据库软件：比如mysql软件）
数据库管理系统（Database Management System），又称为数据库软件（产品）。用于管理DB中的数据
DB与DBMS的关系：
3.SQL
结构化查询语言（Structure Query Language）:专门用来与数据库（DBMS）通信的语言。
SQL的优点：
1、不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL
2、简单易学
3、虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。
三者的关系：
三、数据库存储数据的特点
-
将数据放到表中，表再放到库中
-
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
-
表具有一些特性，这些特性定义了数据在表中如何存储，类似java中“类”的设计
-
表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似于java中的“属性”。
-
表中的数据是按行存储的，每一行类似于java中的“对象”。
四、MySQL产品的特点
-
MySQL数据库隶属于MySQL AB公司，总部位于瑞典，后被oracle收购。
-
优点：
-
成本低：开放源代码，一般可以免费使用
-
性能高：执行很快
-
简单：很容易安装和使用
-
-
DBMS分为两类：
-
基于共享文件系统的DBMS（Access）
ps：只需安装服务端即可
-
基于客户机-------服务器的DBMS（MySQL、Oracle、SqlServer）
-
ps：需安装两端
MySQL的版本
-
社区版（免费）
-
企业版（收费） ------多了些功能
五、MySQL服务的启动与停止（☆）
-
方式一：
计算机------右击管理----服务
-
方式二：通过管理员身份运行
net start 服务名（启动服务）
net stop 服务名（停止服务）
六、MySQL服务端的登录与退出（☆）
-
方式一：通过mysql自带的客户端
只限于root用户
-
方式二：通过windows自带的客户端
登录：
mysql 【-h主机名 -p端口号】 -u用户名 -p密码
退出：
exit 或 ctrl+c
七、MySQL的常见命令
1.查看当前所有的数据库
• show databases;
2.打开指定的库
• use 库名
3.查看当前库的所有表
• show tables；
4.查看其它库的所有表
• show tables from 库名；
5.创建表
• create table 表名（
• 列名 列类型，
• 列名 列类型，
• 。。。
）；
6.查看表结构
desc 表名；
7.查看服务器的版本
方式一：登录到mysql服务端
• select version（）；
方式二：没有登录到mysql服务端
• mysql --version
• 或
• mysql -V
八、MySQL的语法规范
1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释
-
单行注释：#注释文字
-
单行注释： -- 注释文字（ps：中间有空格，不然不认这是注释）
-
多行注释： /* 注释文字 */
九、DQL语言的学习（☆）
1.基础查询（☆）
-
语言：
SELECT 查询列表 FROM 表名;
类似于：System.out.println(打印列表)
-
特点：
1.查询列表可以是：表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格
eg：
#1.查询表中的单个字段
SELECT name FROM employees;#2.查询表中的多个字段
SELECT name,email,salary FROM employees;#3.查询表中的所有字段
**方式一：**
SELECT
name,
salary,
email,
phone,
hiredate
FROM
employees;
**方式二：**
SELECT * FROM employees；注意：
1.在查询表中所有的字段时，方式二的缺陷是打印字段的顺序与表中原有的顺序一样，如果想改变打印顺序则需用方式一。
2.在执行查询语句时，如果查询的字段和关键字一致，则可以在字段上使用着重号来与关键字区分，着重号在键盘上 “1” 的左边。
#4.查询常量值
SELECT 100;
SELECT 'john';注意：查询常量值时，会把常量值作为字段名
#5.查询表达式
SELECT 100+21；
SELECT 100%98；
#6.查询函数
SELECT VERSION(); -----查询版本
#7.起别名 方式一：使用AS SELECT 100%98 AS 结果; SELECT last_name AS 姓,first_name AS 名 FROM employees; 方式二：使用空格 SELECT last_name 姓,first_name 名 FROM employees; 案例：查询salary，显示结果为out put （别名中有特殊符号：空格等等,需要将别名引号起来，单双都可，mysql建议使用双引） SELECT salary AS "out put" FROM employees;
ps:优点
-
便于理解
-
如果要查询的字段有重名的情况，使用别名可以区分开来
#8.去重 （去掉重复的结果，加入关键字distinct） #案例：查询员工表中涉及到的所有的部门编号 SELECT DISTINCT department_id FROM employees;
#9. +号的作用
-----java中的+号：
1.运算符：两个操作数都为数值型
2.连接符：只要有一个操作数为字符串
-----mysql中的+号：
仅仅只有一个功能：运算符
select 100+90; ---两个操作数都为数值型，则做加法运算
select '123'+90; ---只要其中一方为字符型，试图将字符型数值转换成数值型，如果 转换成功，则继续加法运算。
select 'john'+90; ---如果转换失败，则将字符型数值转换为0;
select null+10; ---只要其中一方为null，则结果肯定为null。
#案例：查询员工名和姓连接成一个字段，并显示为姓名
(不可使用+连接，而应使用concat函数)
SELECT CONCAT('a','b','c') AS 结果;
eg:
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
employee
2.条件查询（☆）
-
语法：
select
查询列表
from
表名
where
筛选条件;
-
分类：
一、按条件表达式筛选
条件运算符： > < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符：
&& || !
and or not
三、模糊查询
like
between and
in
is null
#1.按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary > 12000
#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <>90;
#2.按逻辑表达式筛选
#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary >= 10000 AND salary <=20000;
#3.模糊查询
#3.1 like
#案例1：查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
#案例2：查询员工名中第二个字符为_的员工名(需转义，_是特殊字符)
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
like特点：
1.一般和通配符搭配使用
-
通配符：
%： 任意多个字符，包含0个字符
_ : 任意单个字符
#3.2 between and
#案例1：查询员工编号在100到120之前的员工信息
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;
ps:
1.使用between and可以提高语句的简洁度
2.包含临界值
3.两个临界值不要调换顺序
#3.3 in
#案例：查询员工的工种编号是 IT_PROG、AD_VP中的一个员工和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN('IT_PROG','AD_VP');
-
含义：判断某字段的值是否属于in列表中的某一项
-
特点：
-
使用in提高语句的简介度
-
in列表的值类型必须一致或兼容
-
in列表的值不支持通配符
-
#3.4 is null
#案例：查询没有(有)奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS (NOT) NULL;
#安全等于 <=> ------拓展
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=>NULL;
-
= 或者 <> 不能用于判断null值
-
is null 或is not null 可以判断null值
ps: is null pk <=>
-
IS NUll: 仅仅可以判断NULL值，可读性较高，建议使用
-
<=> : 既可以判断NULL值，又可以判断普通的数值，可读性较低
3.排序查询（☆）
-
语法：
select 查询列表 from 表 【where 筛选条件】 order by 排序列表 【 asc | desc】
-
特点：
1.asc代表的是升序，desc代表的是降序
2.如果不写，默认是升序
3.order by子句可以支持单个字段、多个字段、表达式、函数、别名
4.order by子句一般是放在查询语句的最后面，limit字句除外
#案例1：查询员工信息，要求工资从高到低排序 SELECT * FROM employees ORDER BY salary DESC; #案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】 SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC; #案例3：按姓名的长度显示员工的姓名和工资【按函数排序】 SELECT LENGTH(last_name) 字节长度，last_name,salary FROM employees ORDER BY LENGTH(last_name) DESC; #案例4：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】 SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;
4.常见函数（☆）
-
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
-
好处：1.隐藏了实现的细节 2.提高代码的重用性
-
调用：select 函数名（实参列表） 【from表】;
-
特点：
-
叫什么（函数名）
-
干什么（函数功能）
-
-
分类：
-
单行函数
如：concat、length、ifnull等
-
分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
-
-
常见函数：
-
字符函数
length、 concat、 substr、instr、 trim、 upper、lower 、lpad、rpad、replace、
-
数学函数
round、ceil、floor、truncate、mod
-
日期函数
now、curdate、curtime、year、month、monthname、day、hour、minute、second、
str_to_date、date_format
-
其他函数
version、database、user
-
流程控制函数
-
if 、case
单行函数
一、字符函数
#1.length ---获取参数值的字节个数
SELECT LENGTH('John');
#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;
#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;
SELECT TRIM('a' FROM 'aaaa张翠山aaaaa') AS out_put;
#7.lpad ---用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*') AS out_put;
#8.rpad ---用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
#9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若','周芷若','赵敏') AS out_put;
二、数学函数
#1.round ---四舍五入 SELECT ROUND(-1.55); SELECT ROUND(1.566,2); #2.ceil ---向上取整,返回>=该参数的最小整数 SELECT CEIL(1.00); #3.floor ----向下取整，返回<=该参数的最大整数 SELECT FLOOR(-9.99); #4.truncate ---截断 SELECT TRUNCATE(1.69999,1); #5.mod ----取余 mod(a,b) ==> a-a/b*b mod(-10,-3) ==> -10- (-10)/(-3)*(-3)=-1 SELECT MOD(10,3);
三、日期函数
#1.now --返回当前系统日期+时间
SELECT NOW();
#2.curdate --返回当前系统日期，不包含时间
SELECT CURDATE();
#3.curtime --返回当前时间，不包含日期
SELECT CURTIME();
#4.可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1999-1-1') 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月; ---以英文展示
#5.str_to_date --将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#6.date_format ---将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
四、其他函数
SELECT VERSION(); SELECT DATABASE(); SELECT USER();
五、流程控制函数
#1.if函数 ---if else的效果
SELECT IF(10<5,'大','小');
#2.1case函数的使用一： ----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;
#2.2case函数的使用二：类似于 多重if
java中：
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 'B'
ELSE 'D'
END AS 工资级别
FROM employees;
分组函数
-
功能 ：用作统计使用，又称为聚合函数或统计函数或组函数
-
分类：sum求和、avg平均值、max最大值、min最小值、count计算个数
-
特点：
-
sum、avg一般用于处理数值型
-
max、min、count可以处理任何类型
-
以上分组函数都忽略null值
-
可以和distinct搭配实现去重的运算
-
一般使用count（*）用作统计行数
-
和分组函数一同查询的字段要求是group 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; #5.count函数的详细介绍 SELECT COUNT(salary) FROM employees; --计算总行数 SELECT COUNT(*) FROM employees; --计算总行数 SELECT COUNT(1) FROM employees; --计算总行数 效率： MYISAM存储引擎下，count(*)的效率高 INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些
5.分组查询（☆）
-
语法：
SELECT 分组函数，列（要求出现在group by的后面） FROM 表 【where筛选条件】 group by 分组的列表 【order by 子句】
-
注意：
查询列表比较特殊，要求是分组函数和group by后出现的字段
-
特点：
1.分组查询中的筛选条件分为两类：
数据源 位置 关键字
分组前筛选 原始表 group by子句前 WHERE
分组后筛选 分组后的结果集 group by子句后 HAVING
ps：
-
分组函数做条件肯定是放在having子句中
-
能用分组前筛选的，就优先考虑使用分组前筛选
2.group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的较少）
3.也可以添加排序（排序放在整个分组查询的最后）
#简单的分组查询 #案例1：查询每个工种的最高工资 SELECT MAX(salary),job_id FROM employees GROUP BY job_id; #案例2：查询每个位置上的部门个数 SELECT COUNT(*),location_id FROM departments GROUP BY lacation_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的结果进行筛选，查询哪个部门的员工个数>2 SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2; #案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 #1.查询每个工种有奖金的员工的最高工资 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的领导编号是哪个，以及其最低工资 #1.查询每个领导手下的员工固定最低工资 #2.添加筛选条件，编号>102 #3.添加筛选条件:最低工资>5000 SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000; #按表达式或函数分组 #案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些 #1.查询每个长度的员工个数 SELECT COUNT(*),LENGTH(last_name) len_name FROM employees GROUP BY LENGTH(last_name) #2.添加 SELECT COUNT(*),LENGTH(last_name) len_name FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5; #按多个字段分组 #案例：查询每个部门每个工种的员工的平均工资 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY job_id,department_id; #添加排序 #案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY job_id,department_id; ORDER BY AVG(salary) DESC;
6.连接查询（☆）
含义：又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行 ， 表2 有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
-
按年代分类：
-
sql92标准：仅仅支持内连接
-
sql99标准：【推荐】：支持内连接+外连接（左外和右外）+交叉连接
-
-
按功能分类
-
内连接：
等值连接
非等值连接
自连接
-
外连接
左外连接
右外连接
全外连接
-
交叉连接
#一、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=department.department_id; #2.为表起别名 ps：提高语句的简介度，区分多个重名的字段 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定 #查询员工名、工种号、工种名 SELECT e.last_name,e.job_id,j.job_title FROM employees e,jobs j WHERE e.job_id=j.job_id; #案例：查询城市名第二个字符为o的部门名和城市名 SELECT department_name,city FROM departments d,location l WHERE d.location_id=l.location_id ADN city LIkE '_o%'; #2.非等值连接 #案例1：查询员工的工资和工资级别 SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.lowest_sal AND g.highest_sal; #3.自连接 #案例：查询员工名和上级的名称 SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employee e,employees m WHERE e.manager_id = m.employee_id;二、sql99语法 语法： select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】 【group by 分组】 【having 筛选条件】 【order by 排序列表】 分类： 内连接（*）：inner 外连接 左外（*）：left 【outer】 右外（*）：right【outer】 全外：full【outer】 交叉连接：cross #一、内连接 语法： select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件; 分类： 等值 非等值 自连接 特点： 1.添加排序、分组、筛选 2.inner可以省略 3.筛选条件放在where后面，连接条件放在on后面，提高分类性，便于阅读 4.inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集 #1.等值连接 #案例1：查询员工名、部门名 SELECT last_name,department_name FROM department d INNER JOIN employee e ON e.department_id=d.department_id; #案例2：查询名字中包含e的员工名和工种名（添加筛选） SELECT last_name,job_title FROM employee e INNER JOIN jobs j ON e.job_id=j.job_id WHERE e.last_name LIKE '%e%'; #案例3：查询部门个数>3的城市名和部门个数（添加分组+筛选） #1.先查询每个城市的部门个数 #2.在1的基础上筛选满足条件的 SELECT city,COUNT(*) 部门个数 FROM department d INNER JOIN location l ON d.location_id=l.location_id GROUP BY city HAVING COUNT(*)>3; #案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序） #1.查询每个部门的员工个数 SELECT COUNT(*),department_name FROM employee e INNER JOIN department d ON e.department_id=d.department_id GROUP BY department_name #2.在1结果上筛选员工个数>3的记录，并排序 SELECT COUNT(*),department_name FROM employee e INNER JOIN department d ON e.department_id=d.department_id GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC; #案例5：查询员工名、部门名、工种名，并按部门名降序（添加三表连接） SELECT last_name,department_name,job_title FROM employees e INNER JOIN departments d ON e.department_id=d.department_id INNOR JOIN jobs j ON e.job_id=j.job_id ORDER BY department_name DESC; #2.非等值连接 #查询员工的工资级别 SELECT salary,grade_level FROM employees e JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal; #查询每个工资级别的个数>20的个数，并且按工资级别降序 SELECT COUNT(*),grade_level FROM employees e JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level DESC; #3.自连接 #查询姓名中包含字符k的员工的名字、上级的名字 SELECT e.last_name,m.last_name 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.全外连接=内连接的结果+表1中有单表2没有的+表2中有但表1没有的 **/ #引入：查询男朋友不在男神表的女神名 #左外连接 SELECT b.name FROM beauty b LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.id IS NULL; #右外连接 SELECT b.name FROM boys bo RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bo.id WHERE bo.id IS NULL; #案例1：查询哪个部门没有员工 #左外 SELECT d.*,e.employee_id FROM departments d LEFT OUTER JOIN employees e 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 ON d.department_id = e.department_id WHERE e.employee_id IS NULL; #全外连接（mysql不支持，可以使用union关键字将左外连接和右外连接的结果拼接起来） SELECT b.* FROM beauty b FULL OUTER JOIN boys bo ON b.boyfriend_id = bo.id; #交叉连接（99语法实现笛卡尔乘积）(99语法用cross关键字实现，92语法用“，”实现) SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo; -
#sql92 和 sql99 pk
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
ps：注意下图中去除交集部分就是用is null ，因为交集部分的主键是不为null的，选择is null就是去除了交集的部分
7.子查询（√）
含义：出现在其他语句中的select语句，称为子查询或内查询；内部嵌套其他select语句的查询（外部的查询语句），称为主查询或外查询。
示例：
select first_name from employees where
department_id in(
select department_id from departments
where location_id=1700
)
分类：
-
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面 ---------重点
标量子查询（单行） --------重点
列子查询（多行） --------重点
行子查询
exists后面（相关子查询）
表子查询
-
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
#一、where或having后面
1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多行多列）
特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配着单行操作符使用
单行操作符：> < >= <= = <>
列子查询，一般搭配着多行操作符使用
多行操作符：in、any/some、all
4.子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果
#1.标量子查询
#案例1：谁的工资比Abel高？
# 1.查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';
#2.查询员工的信息，满足salary>1结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
)
#案例2：返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资
#1.查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141
#2.查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
#3.查询员工的姓名，job_id和工资，要求job_id = 1并且salary > 2
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和salary
#1.查询公司的最低工资
SELECT MIN(salary)
FROM employees
#2.查询last_name,job_id和salary，要求salary=1
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#1查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
#2.查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
#3.在2基础上筛选，满足min（salary）>1
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#非法使用标量子查询
ps：子查询的结果不是标量子查询（比如：不是单行单列的结果，没有结果）
#2.列子查询（多行子查询）
操作符 含义
IN/NOT IN 等于列表中的任意一个
ANY/SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
ps：any和all不常用，因为语义化没有min和max强，可以使用min和max代替
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#1.查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
#2.查询员工姓名，要求部门号是1列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
)
#案例2：返回其它部门中比job_id为IT_PROG部门任一工资低的员工的员工号、姓名、job_id以及salary
#1.查询job_id为IT_PROG部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
#2.查询员工名、姓名、job_id以及salary，salary<(1)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
或(等价于小于最大值)
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
#案例3：返回其它部门中比job_id为IT_PROG部门所有工资低的员工的员工号、姓名、job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN(salary)
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
#3.行子查询(结果集一行多列或多行多列)
#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#二、select后面
/*
仅仅支持标量子查询
*/
#案例1：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
)个数
FROM departments d;
#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
#1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2.连接1的结果集和job_grade表，筛选条件平均工资between lowest_sal and highest
SELECT ag_dep.*,g.grade_level
FROM(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
#四、exists后面（相关子查询）
/*
语法：
exists(完整的查询语句)
结果：
1或者0
*/
#案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department_id
)
#使用in
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees
)
#案例2：查询没有女朋友的男神信息
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);
#使用in
SELECT bo.*
FROM boys
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
)
8.分页查询（☆）
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
特点：
1.limit语句放在查询语句的最后
2.公式
要显示的页数page，每页的条目数size
select 查询列表 from 表 limit (page - 1) *size , size;
语法：
执行顺序 select 查询列表 7 from 表 1 【join type join 表2 2 on 连接条件 3 where 筛选条件 4 group by 分组字段 5 having 分组后的筛选 6 order by 排序的字段】 8 limit 【offset】,size; 9 offest：要显示条目的起始索引（起始索引从0开始） size：要显示的条目个数 #案例1：查询前5条员工信息 SELECT * FROM employees LIMIT 0,5; #案例2：查询第11条-第25条 SELECT *FROM employees LIMIT 10,15; #案例3：有奖金的员工信息，并且工资较高的前10名显示出来 SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 0,10; #试说出查询语句中涉及到的所有的关键字，以及执行先后顺序 select 查询列表 7 from 表 1 连接类型 join 表2 2 on 连接条件 3 where 筛选条件 4 group by 分组列表 5 having 分组后的筛选 6 order by 排序列表 8 limit 偏移，条目数 9
9.联合查询（√）
union（联合）合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1 union 查询语句2 union ... 应用场景： 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时 特点： 1.要求多条查询语句的查询列表数是一致的 2.要求多条查询语句的查询的每一列的类型和顺序最好一致 3.union关键字默认去重，如果使用union all可以包含重复项
#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息 SELECT id,cname,csex FROM t_ca WHERE csex='男' UNION SELECT t_id,tName,tGender FROM t_ua WHERE tGender='male';
十、DML语言（☆）
数据操作语言：
插入：insert
修改：update
删除：delete
1、插入语言
#方式一：
语法：
insert into 表名(列名,...) values(值1,...);
#1.插入的值的类型要与列的类型一致或兼容（可以转换成类型一致）
INSERT INTO stu(id,name,sex,phone)
VALUES(12,'唐艺昕','女','13278987678');
#2.不可以为null的列必须插入值，可以为null的列如何插入值？
#方式一(写入列的时候值写null)：
INSERT INTO stu(id,name,sex,phone)
VALUES(12,'唐艺昕','女',null);
#方式二(省略列的时候不写值)：
INSERT INTO stu(id,name,sex)
VALUES(12,'娜扎','女');
#3.列的顺序可以调换
INSERT INTO stu(name,id,sex)
VALUES('蒋欣',12,'女');
#4.列数和值的个数必须一致
#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
INSERT INTO stu
VALUES(12,'娜扎',NULL);
#方式二：
语法：
insert into 表名
set 列名=值,列名=值,...
#案例
INSERT INTO beauty
SET id=19,NAME='刘涛';
#两种方式大pk
1.方式一支持插入多行,方式二不支持
INSERT INTO stu
VALUES(12,'娜扎1','女'),
(22,'娜扎2','女'),
(32,'娜扎','女');
2.方式一支持子查询，方式二不支持
INSERT INTO stu(id,NAME,sex)
SELECT 22,'宋茜','女'
2、修改语句
1.修改单表的记录* 语法： update 表名 set 列=新值,列=新值,... where 筛选条件; 2.修改多表的记录【补充】 语法： sql92语法： update 表1 别名,表2 别名 set 列=值 where 连接条件 and 筛选条件 sql99语法： update 表1 别名 inner | left | right | join 表2 别名 on 连接条件 set 列=值,.... where 筛选条件; #1.修改单表的记录 #案例：修改beauty表中姓唐的女神的电话为123345 UPDATE beauty SET phone='123345' WHERE NAME_LIKE '唐%'; #2.修改多表的记录 #案例1：修改张无忌的女朋友的手机号为114 UPDATE boys bo INNER JOIN beauty b ON bo.id=b.boyfriend_id SET b.phone ='114' WHERE bo.boyName = '张无忌'; #案例2：修改没有男朋友的女神的男朋友编号都为2号 UPDATE boys bo RIGHT JOIN beauty b ON bo.id=b.boyfriend_id SET b.boyfriend_id=2 WHERE b.id IS NULL;
3、删除语句
方式一：delete 语法： 1.单表的删除(*) delete from 表名 where 筛选条件 2.多表的删除【补充】 sql92语法： delete 表1的别名,表2的别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件; sql99语法： delete 表1的别名，表2的别名 from 表1 别名 inner | left | right join 表2 别名 on 连接条件 where 筛选条件; 方式二：truncate 语法： truncate table 表名; #方式一：delete #1.单表的删除 #案例1：删除手机号以9结尾的女神信息 DELECT FROM beauty WHERE phone LIKE '%9'; #2.多表的删除 #案例：删除张无忌的女神的信息 DELECT b FROM beauty b INNER JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.boyName = '张无忌'; #案例：删除黄晓明以及女朋友的信息 DELECT b,bo FROM beauty b INNER JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.boyName = '黄晓明'; #方式二：truncate语句(删除所有的信息) TRUNCATE TABLE boys; #delete pk truncate【面试题】 1.delete 可以加where条件，truncate不能加 2.truncate删除，效率高一丢丢 3.假如要删除的表中有自增长列,如果用delete删除后， 再插入数据，自增长列的值从断点开始，而truncate删 除后，再插入数据，自增长列的值从1开始 4.truncate删除没有返回值，delete删除有返回值（在可视化软件中是否可看出几行受影响） 5.truncate删除不能回滚，delete删除可以回滚
十一、DDL语言
数据定义语言（库和表的管理）
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除
创建：create
修改：alter
删除：drop
一、库的管理（√）
#1.库的创建 语法： create database [if not exists]库名; #案例：创建库books CREATE DATABASE IF NOT EXISTS books; #2.库的修改 #更改库的字符集 ALTER DATABASE books CHARACTRE SET gbk; #3.库的删除 DROP DATABASE IF EXISTS books;
二、表的管理（√）
##1.表的创建（*）
语法：
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
#案例：创建表book
CREATE TABLE book(
id INT, #编号
bName VARCHAR(20), #图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME #出版日期
);
##2.表的修改
/*
语法：
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/
#1.修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
#2.修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#3.添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
#4.删除列
ALTER TABLE author DROP COLUMN annual;
#5.修改表名
ALTER TABLE author RENAME TO book_author;
##3.表的删除
DROP TABLE IF EXISTS book_author;
#通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
##4.表的复制
#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
#2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
#3.复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
#4.仅仅复制某些字段(mysql中0表示false，1表示true)
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
###
十二、常见的数据类型（√）
数值型：
整型
小数：
定点数
浮点型
字符型
较短的文本：char 、varchar
较长的文本：text、blob（较长的二进制数据）
日期型
一、整型
分类：
tinyint smallint mediumint int/integer bigint
所占字节： 1 2 3 4 8
特点：
1.如果不设置无符号还是有符号，默认是有符号的，如果想设置无符号的，需要添加unsigned关键字
2.如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
3.如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用
#1.如何设置无符号和有符号
CREATE TABLE tab_int(
t1 INT UNSIGNED #无符号
t2 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，则会根据插入的数值的精度来决定精度
3.定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
原则：
所选择的类型越简单越好，能保存数值的类型越小越好
三、字符型
较短的文本：
char
varchar
其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：
text
blob（较大的二进制）
特点：
写法 M的意思 特点 空间的耗费 效率
char char(M) 最大的字符数， 固定长度的字符 比较耗费 高
可以省略，默认为1
varchar varchar（M） 最大的字符数，不可以省略 可变长度的字符 比较节省 低
四、日期型
分类
date只保存日期
time只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间
特点：
字节 范围 时区等的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 受
十三、常见约束（√）
含义：
一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
-
NOT NUll：非空，用于保证该字段的值不能为空
比如姓名、学号等
-
DEFAULT：默认，用于保证该字段有默认值
比如性别
-
PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
-
UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
-
CHECK：检查约束【mysql中不支持】
比如年龄、性别
-
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：
1.创建表时
2.修改表时
约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果
表级约束：
除了非空、默认，其他的都支持
主键和唯一的大对比：
保证唯一性 是否允许为空 一个表中可以有多个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐
外键：
-
要求在从表设置外键关系
-
从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
-
主表的关联列必须是一个key（一般是主键或唯一）
-
插入数据时，先插入主表，再插入从表，
删除数据时，先删除从表，再删除主表
CREATE TABLE 表名（
字段名 字段类型 列级约束，
字段名 字段类型，
表级约束
）
#一、创建表时添加约束
#1.添加列级约束
/*
语法：
直接在字段名和类型后面追加约束类型即可。
只支持：默认、非空、主键、唯一
*/
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuName VARCHAR(20) NOT NULL, #非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查 --mysql不支持
seat INT UNIQUE, #唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERNCES major(id) #外键 ---mysql不支持
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
#2.添加表级约束
/*
语法：在各个字段的最下面
【constraint 约束名】 约束类型（字段名）
*/
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id), #主键
CONSTRAINT uq UNIQUE(seat), #唯一键
CONSTRAINT ck CHECK(gender = '男' OR gender='女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);
#通用的写法
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 约束名】 约束类型(字段名) 【外键的引用】;
*/
CREATE TABLE IF NOT EXISTS stuinfo(
id INT ,
stuname VARCHAR(20) ,
gender CHAR(1);
seat INT UNIQUE,
majorid iNT,
);
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
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);
#4.添加唯一
#列级约束#
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#表级约束#
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
#三、修改表时删除约束 #1.删除非空约束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL; #2.删除默认约束 ALTER TABLE stuinfo MODIFY COLUMN age INT; #3.删除主键 ALTER TABLE stuinfo DROP PRIMARY KEY; #4.删除唯一 ALTER TABLE stuinfo DROP INDEX seat; #5.删除外键 ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
位置 支持的约束类型 是否可以起约束名
列级约束： 列的后面 语法都支持，但外键没有效果 不可以
表级约束： 所有列的下面 默认和非空不支持，其他支持 可以（主键没有效果）
标识列
#标识列
/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
#特点：
1.标识列必须和主键搭配吗？不一定，但要求是一个key
2.一个表可以有几个标识列？至多一个！
3.标识列的类型只能是数值型
4.标识列可以通过SET auto_increment_increment=3;设置步长
可以通过手动插入值，设置起始值
*/
#一、创建表时设置标识列
CREATE TABLE tab_identity(
id iNT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20);
)
#二、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY
十四、TCL语言的学习
Transaction Control Language 事务控制语言
事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
案例：转账
事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据
事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句
显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
set autocommit=0;
步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句（select insert update delete）
语句1；
语句2；
...
步骤3：结束事务
commit;提交事务
rollback;回滚事务
#开启事务
set autocommit=0;
#编写一组事务的语句
update account set balance = 1500 where name='henry';
update account set balance = 500 where name='tom';
#提交事务
commit;
#rollback;
savepoint 节点名;设置保存点
演示：
set autocommit=0;
start transaction;
delect from account where id=25;
savepoint a; #设置保存点
delect from account where id=28;
rollback to a;#回滚到保存点
数据库隔离级别
-
每启动一个mysql程序，就会获得一个单独的数据库连接。每个数据库连接都有一个全局变量@@tx_isolation，表示当前的事务隔离级别
-
查看当前的隔离级别：SELECT@@tx_isolation;
-
设置当前mysql连接的隔离级别：
set transaction isolation level 隔离级别;
-
设置数据库系统的全局的隔离级别：
set global transaction isolation level 隔离级别;
事务的隔离级别：
脏读 不可重复读 幻读
read uncommitted: √ √ √
read committed: × √ √
repeatable read: × × √
serializable: × × ×
mysql中默认：第三个隔离级别 repeatable read
oracle中默认：第二个隔离级别 read committed
十五、视图的讲解（√）
#视图
/* 含义：虚拟表，和普通表一样使用 mysql5.1版本出现的新特性，行和列的数据来自于定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果 应用场景： 1.多个地方用到同样的查询结果 2.该查询结果使用的sql语句较复杂 */
视图的优点
-
重用sql语句
-
简化复杂的sql操作，不必知道它的查询细节
-
保护数据，提高安全性
#一、创建视图
/*
语法：
create view 视图名
as
查询语句;
*/
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#1.创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN department d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id= e.job_id;
#2.使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#二、视图的修改
#方式一：
语法：
/*
create or replace view 视图名
as
查询语句;
*/
#方式二：
语法：
/*
alter view 视图名
as
查询语句;
*/
#三、删除视图
语法：
/*
drop view 视图名，视图名，...;
*/
#四、查看视图
DESC 视图名;
#五、视图的更新
#1.插入
INSERT INTO myv1 VALUES('张飞','aw@11.com');
#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
#3.删除
DELECT FROM myv1 WHERE last_name ='张无忌';
#具备以下特点的视图不允许更新
#1.包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
#2.常量视图
#3.select中包含子查询
#4.join
#5.from一个不能更新的视图
#6.where子句的子查询引用到了from子句中的表
视图与表的比较
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改
表 create table 保存了数据 增删改查
delete和truncate在事务使用时的区别
#演示delete SET autocommit=0; START TRANSACTION; DELECT FROM account; ROLLBACK; #演示truncate SET autocommit=0; START TRANSACTION; TRUNCATE TABLE account; ROLLBACK; ps:在事务结束时使用rollback，delete可以回滚，而truncate不可回滚
十六、变量
-
系统变量
全局变量
会话变量
-
自定义变量
用户变量
局部变量
一、系统变量 说明：变量由系统提供，不是用户定义，属于服务器层面 注意： 如果是全局变量，则需要加GLOBAL，如果是会话级别，则需要加SESSION，如果不写，则默认SESSION 使用的语法： 1.查看所有的系统变量 show glob|【session】 variables; 2.查看满足条件的部分系统变量 show global|【session】 variables like '%char%'; 3.查看指定的某个系统变量的值 select @@global|【session】.系统变量名; 4.为某个系统变量赋值 方式一： set global|【session】 系统变量名; 方式二： set @@global|【session】.系统变量名=值; #1.全局变量 /* 作用域：服务器每次启动将为所有的全部变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启 */ #1.查看所有的全局变量 SHOW CLOBAL VARIABLES; #2.查看部分的全局变量 SHOW GLOBAL VARIABLES LIKE '%char%'; #3.查看指定的全局变量的值 SELECT @@global.autocommit; #4.为某个指定的全局变量赋值 SET @@global.autocommit=0; #2.会话变量 /* 作用域：仅仅针对于当前会话（连接）有效 */ #1.查看所有的会话变量 SHOW VARIABLES; #2.查看部分的会话变量 SHOW VARIABLES LIKE '%char%'; #3.查看指定的某个会话变量 SELECT @@tx_isolation; #4.为某个会话变量赋值 方式一： SET @@session.tx_isolation='read-uncommitted'; 方式二: SET SESSION tx_isolation='read-committed';
#二、自定义变量
说明：变量是用户自定义的，不是由系统提供的
使用步骤：
声明
赋值
使用（查看、比较、运算等）
#1.用户变量
作用域：针对于当前会话（连接）有效，同于会话变量的作用域
#1.声明并初始化
SET @用户变量名 = 值;或
SET @用户变量名 :=值;或
SELECT @用户变量名 :=值;
#2.赋值（更新用户变量的值）
方式一：通过SET或SELECT
SET @用户变量名 = 值;或
SET @用户变量名 :=值;或
SELECT @用户变量名 :=值;
方式二：通过SELECT INTO
SELECT 字段 INTO 变量名
FROM 表;
#3.使用(查看用户变量的值)
SELECT @用户变量名;
#2、局部变量
作用域：仅仅在定义它的begin end中有效
应用在 begin end 的第一句
#1.声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
#2.赋值
方式一：通过SET或SELECT
SET 局部变量名 = 值;或
SET 局部变量名 :=值;或
SELECT @局部变量名 :=值;
方式二：通过SELECT INTO
SELECT 字段 INTO 局部变量名
FROM 表;
#3.使用
SELECT 局部变量名;
对比用户变量和局部变量
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何地方 必须加@符号，不用限定类型
局部变量 BEGIN END中 只能在BEGIN END中， 一般不用加@符号，需要限定类型
且为第一句话
#案例：声明两个变量并赋初始值，求和，并打印 #1.用户变量 SET @m=1; SET @n=2; SET @sum= @m+@n; SELECT @sum;
十七、存储过程和函数
存储过程和函数：类似于java中的方法
好处：
-
提高代码的重用性
-
简化操作
1.存储过程
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
-
提高代码的重用性
-
简化操作
-
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
#一、创建语法
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
存储过程体（一组合法的SQL语句）
END
注意：
1.参数列表包含三部分
参数模式 参数名 参数类型
举例：
IN stuname VARCHAR(20)
参数模式：
IN ------该参数可以作为输入，也就是该参数需要调用方法入值
OUT ------该参数可以作为输出，也就是该参数可以作为返回值
INOUT ------该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2.如果存储过程体仅仅只有一句话，BEGIN END可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号
存储过程的结尾可以使用 DELIMITER 重新设置
语法：
DELIMITER 结束标记
案例：
DELIMITER $
#二、调用语法
CALL 存储过程名(实参列表);
#1.空参列表
#案例：插入到admin表中三条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('john1','0000'),('tom','0000'),('jack','1111');
END $
#调用
CALL myp1()$
#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
CALL myp2('柳岩')$
#2.案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) INTO result #赋值
FROM admin
WHERE admin.username=username
AND admin.password=password;
SELECT IF(result>0,'成功','失败'); #使用
END $
#调用
CALL myp3('张飞','333')$
#3.创建带out模式的存储过程
#案例1：根据女神名，返回对应的男神名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
CALL myp5('小昭',@bName)$
SELECT @bName$
#案例2：根据女神名，返回对应的男神名和男神魅力值
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,userCP
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
#调用
CALL myp6('小昭',@bName,@usercp)$
SELECT @bName,@usercp$
#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
#二、删除存储过程 #语法：drop procedure 存储过程名 #三、查看存储过程的信息 SHOW CREATE PROCEDURE myp2;
2.函数
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
-
提高代码的重用性
-
简化操作
-
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
存储过程和函数的区别：
存储函数：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果
#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意：
1.参数列表 包含两部分
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议
return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记
#二、调用语法
SELECT 函数名(参数列表)
#--------------------案例演示---------------------
#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义变量
SELECT COUNT(*) INTO c; #赋值
FROM employees;
RETURN c;
END $
#调用
SELECT myf1()$
#2.有参有返回
#案例1：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; #定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END $
SELECT myf2('king') $
#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20))
BEGIN
DECLARE sal DOUBLE;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id=d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
SELECT myf3('IT')$
#三、查看函数
SHOW CREATE FUNCTION 函数名;
#四、删除函数
DROP FUNCTION 函数名;
#案例
#一、创建函数，实现传入两个float，返回两者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT)
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM = num1+num2;
RETURN SUM;
END $
SELECT test_fun1(1,2)$
十八、流程控制结构
顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路劲中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码
#一、分支结构
#1.if函数
功能：实现简单的双分支
语法：
if（表达式1，表达式2，表达式3）
执行顺序：
如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值
应用：任何地方
#2.case结构
情况1：类似于java中的switch语句，一般用于实现等值判断
语法：
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1或语句1;
WHEN 要判断的值 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END CASE;
情况2：类似于java中的多重IF语句，一般用于实现区间判断
语法：
CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END CASE;
特点：
1、
可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，BEGIN END中或BEGIN END的外面
可以作为独立的语句去使用，只能放在BEGIN END中
2、
如果WHEN中的值满足或条件成立，则执行对应的THEN后面的语句，并且结束CASE
如果都不满足，则执行ELSE中的语句或值
3、
ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足，则返回NULL
#案例：
#创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A,80-90，显示B，60-80，显示c，否则，显示D
CREATE PROCEDURE test_case(IN score INT)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT 'A';
WHEN score>=80 THEN SELECT 'B';
WHEN score>=60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END $
CALL test_case(95)$
#3.if结构
功能：实现多重分支
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;
应用场合：应用在begin end中
#案例1：根据传入的成绩，来显示等级，比如传入的成绩：90-100，返回A,80-90，返回B，60-80，返回c，否则，返回D
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END $
#二、循环结构
分类：
while 、 loop、 repeat
循环控制：
iterate类似于continue，继续，结束本次循环，继续下一次
leave类似于 break，跳出，结束当前所在的循环
#1.while
语法：
【标签:】while 循环条件 do
循环体;
end while【标签】;
联想:
while(循环条件){
循环体;
}
#2.loop
【标签:】loop
循环体;
end loop【标签】;
可以用来模拟简单的死循环
#3.repeat
语法：
【标签:】repeat
循环体;
until 结束循环的条件
end repeat【标签】;
#--------------------------案例---------------------
#案例1：批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHERE i<=insertCount DO
INSERT INTO admin(username,password)VALUES(CONCAT('Rose'+i),'666');
SET i=i+1;
END WHILE;
END $
CALL pro_while1(100)$
#2.添加循环leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20就停止
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('henry',i),'1111');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while(100)$
#3.添加iterate语句
#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('henry',i),'1111');
END WHILE a;
END $
CALL test_while(100)$
浙公网安备 33010602011771号