mysql章节(二)（MySQL的事务与存储精讲）

最新推荐文章于 2023-08-31 09:24:16 发布

武侠㿝卄清

最新推荐文章于 2023-08-31 09:24:16 发布

阅读量339

点赞数

分类专栏： java web开发编程语言文章标签： mysql

本文链接：https://blog.csdn.net/qq_30347133/article/details/83685262

版权

web开发同时被 3 个专栏收录

611 篇文章 2 订阅

订阅专栏

java

574 篇文章 4 订阅

订阅专栏

编程语言

272 篇文章 1 订阅

订阅专栏

/豪华的分割线*********/
– DQL(数据库查询语句),专门从数据库表表查询相关数据的
/*
查询语句的语法
SELECT 字段列表
FROM 表名;

在cmd窗口中,SQL语句的末尾必须要用分号结束
但是在窗口客户端中,SQL语句的末尾可有有分号,也可以没有分号,为了语句的完整,语句末尾加上分号
SQL语句不区分大小写
*/

– 从表中出现单个的字段
SELECT EMPNO
FROM EMP;

select empno
from emp;

– 从表中出现多个字段,多个字段之间使用逗号分隔,最后一个字段后面没有逗号
SELECT EMPNO,ENAME,JOB,HIREDATE
FROM EMP;

– 从表中查询全部的字段
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM EMP;

– 查询全部字段的时候,可以用* 来替代查询列表.但是为了代码的可读性,最好还是写清楚字段列表,也就是上面的方式
SELECT *
FROM EMP;

DESC EMP;

– 在查询列表中可以使用算术表达式
– 查询员工的年薪(年薪 = SAL * 12)
SELECT EMPNO,ENAME ,SAL ,SAL*12
FROM EMP;

– 我们可以给查询字段起别名
– 起别名的时候可以使用AS,也可以不使用AS;可以使用"",也可以使用’’,也可以不使用引号
SELECT EMPNO AS “员工编号”,ENAME “员工姓名”,SAL ‘月薪’,SAL*12 年薪
FROM EMP;

/豪华的分割线*********/
– 条件查询语句,也就是在查询语句中加入适当的条件,只有满足查询条件的结果才会被显示
– 条件查询语句需要使用WHERE子句;(什么是子句,也就是可有可无的语句)
/*
条件查询语句的语法
SELECT 字段列表
FROM 表明
[WHERE 查询条件]

语句执行的顺序:
1.SELECT FROM 查询出全部的数据
2.使用WHERE对数据进行过滤
*/

– 数据库中的三种主要类型
– 1.字符型,可以表示一个或多个字符
– 2.数值型,可以有小数,也可以没有小数
– 3,日期型,表示日期型的数据
– 注意:在数据库中使用字符型数据和日期型数据的时候,必须要使用""或’’

– 1.使用=作为查询条件,适用于数据库中的三种主要类

– 查询员工名字是"SMITH"的员工信息
SELECT *
FROM EMP
WHERE ENAME = “SMITH”;

SELECT *
FROM EMP
WHERE ENAME = ‘SMITH’;

– 查询职位是"MANAGER"的员工信息
SELECT *
FROM EMP
WHERE JOB = “MANAGER”;

– 查询员工编号7566的信息
SELECT *
FROM EMP
WHERE EMPNO = 7566;

– 查询雇佣日期是"1981-02-22"的员工信息
SELECT *
FROM EMP
WHERE HIREDATE = “1981-02-22”;

– SQL语句不区分大小写,但是表中的字符型的数据是区分大小写的
– (1)Oracle在任何的平台上都是严格区分字符型数据大小写的
– (2)MySql在Linux/Unix平台上是严格区分字符型数据大小写的
– (3)MySql在Window平台上不区分字符型数据的大小写
SELECT *
FROM EMP
WHERE ENAME = ‘SMITH’;

SELECT *
FROM EMP
WHERE ENAME = ‘smith’;

– 我们如何让MySql在Window平台上也严格的区分字符型数据的大小写呢,可以使用binary关键字
SELECT *
FROM EMP
WHERE BINARY ENAME = ‘smith’;

– 2,使用<> 或 != 作为查询条件,适用于三种主要类型

– 查询名字不是"SMITH"的员工信息
SELECT *
FROM EMP
WHERE ENAME <> “SMITH”;

SELECT *
FROM EMP
WHERE ENAME != “SMITH”;

– 查询工资不是3000的员工信息
SELECT *
FROM EMP
WHERE SAL <> 3000;

– 查询雇佣日期不是"1981-02-22"的员工信息
SELECT *
FROM EMP
WHERE HIREDATE != “1981-02-22”;

– 3.使用 <,<=,>,>=作为查询条件

– 查询工资大于等于3000的员工的信息
SELECT *
FROM EMP
WHERE SAL >= 3000;

SELECT *
FROM EMP
WHERE SAL > 3000;

SELECT *
FROM EMP
WHERE SAL <= 3000;

SELECT *
FROM EMP
WHERE SAL < 3000;

– 4.使用BETWEEN AND 作为查询条件,在特定的范围之内

– (1)BETWEEN AND 作用于数值型的数据和日期型的数据的时候,是包含两个边界值的
– 查询工资在1600~3000之间的员工信息
SELECT *
FROM EMP
WHERE SAL BETWEEN 1600 AND 3000;

– BETWEEN AND的功能也可以使用AND来完成
SELECT *
FROM EMP
WHERE SAL >= 1600 AND SAL <=3000;

– 查询雇佣日期在"1981-02-20"~"1981-12-03"的员工信息
SELECT *
FROM EMP
WHERE HIREDATE BETWEEN “1981-02-20” AND “1981-12-03”;

– (2)BETWEEN AND 对于字符型的数据比较特殊,包含第一个值,但是不包含第二个值
– 查询名字从A~F之间的员工信息
SELECT *
FROM EMP
WHERE ENAME BETWEEN “A” AND “F”;

– 所以需要查询"A"~“F"的员工,正确的语句如下
– MySql中的字符编码也是采用Unicode的,我们要查询"A”~"Z"的员工,找"Z"的下一个字符即可
SELECT *
FROM EMP
WHERE ENAME BETWEEN “A” AND “G”;

– 查询工资在1600~3000之间的员工信息

– 5.使用IS NULL作为查询条件
– NULL是数据库中一种特殊的数据,表示没有数据;三种主要类型都支持NULL
– 对于NULL的判断,不能使用=;而是需要使用 IS NULL进行判断

– 查询津贴(COMM)是NULL的员工
– 对于NULL的判断,不能使用=;下面的语句查询不到数据
SELECT *
FROM EMP
WHERE COMM = NULL;

– 而是需要使用 IS NULL进行判断
SELECT *
FROM EMP
WHERE COMM IS NULL;

– 查询津贴不为NULL的员工信息
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;

– 6,使用AND连接多个查询条件; 要求多个查询条件必须要同时成立,相当于Java中的&& ,也就是并且的意思

– 查询工资大于1600 并且部门编号是20的员工信息
SELECT *
FROM EMP
WHERE SAL >1600 AND DEPTNO = 20;

– AND 也可以使用 && 来代替
SELECT *
FROM EMP
WHERE SAL >1600 && DEPTNO = 20;

– 7.使用OR连接多个查询条件,多个查询条件中只有满足任一个条件即可,相当于Java中的 || ,也就是或者的意思

– 查询工资大于1600 或者部门编号是20的员工信息
SELECT *
FROM EMP
WHERE SAL > 1600 OR DEPTNO = 20;

– OR也可以使用|| 来代替
SELECT *
FROM EMP
WHERE SAL > 1600 || DEPTNO = 20;

– 查询部门编号是20 或部门编号是30的员工信息
SELECT *
FROM EMP
WHERE DEPTNO=20 || DEPTNO = 30;

– 8.注意:AND 和 OR是有优先级的,首先要执行AND ,后执行OR [这个顺序必须要记住]
– 为了语法的清晰,把不同的条件用()扩起来

– 查询薪水大于1800，并且部门编号为20或30的员工
SELECT *
FROM EMP
WHERE SAL > 1800 AND DEPTNO = 20 OR DEPTNO = 30;

– 仔细分析上面的查询条件,工资大于1600是一个条件;部门编号为20或30是另一个条件,两个条件要同时成立
– 上面语句的查询结果时错误的,这是因为首先执行的是and,后执行的是or,相等于如下的语句
SELECT *
FROM EMP
WHERE (SAL > 1800 AND DEPTNO = 20) OR (DEPTNO = 30);

– 正确的语句如下
SELECT *
FROM EMP
WHERE (SAL > 1800) AND (DEPTNO = 20 OR DEPTNO = 30);

– 9.使用IN作为查询条件,IN表示包含的意思,也可以使用OR来完成

– 查询职位是"MANAGER" 或 "ANALYST"的员工信息
SELECT *
FROM EMP
WHERE JOB IN (“MANAGER”,“ANALYST”);

– IN 也可以使用OR来完成
SELECT *
FROM EMP
WHERE JOB = “MANAGER” OR JOB = “ANALYST”;

– 查询薪水是1600 或 3000的员工信息
SELECT *
FROM EMP
WHERE SAL IN (1600,3000);

SELECT *
FROM EMP
WHERE SAL =1600 OR SAL = 3000;

– 查询薪水不是(1600,3000)的员工
SELECT *
FROM EMP
WHERE SAL NOT IN (1600,3000);

– 另一种方式:薪水既不是1600,并且薪水也不是3000
SELECT *
FROM EMP
WHERE SAL <> 1600 AND SAL <> 3000;

– 10.使用not作为查询条件,NOT是取反的意思

– 查询出薪水不是1600和薪水不是3000的员工
– 方式1:
SELECT *
FROM EMP
WHERE SAL NOT IN (1600,3000);

– 方式2:
SELECT *
FROM EMP
WHERE SAL <> 1600 AND SAL <> 3000;

– 方式3:
SELECT *
FROM EMP
WHERE NOT(SAL =1600 OR SAL =3000);

– 查询津贴不是NULL的员工
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;

– 11.使用LIKE执行模糊查询
– 什么是模糊查询?模糊是专门用字符型数据作为查询条件的,但是字符型的查询条件又不是明确的
– 和LIKE 配套的两个通配符
– % :表示0个或多个任意的字符
– _ :表示一个任意的字符

– 查询名字中有"A"的员工
SELECT *
FROM EMP
WHERE ENAME LIKE “%A%”;

– 查询名字以A开头的员工
SELECT *
FROM EMP
WHERE ENAME LIKE “A%”;

– 查询名字的第二个字符是A的员工
SELECT *
FROM EMP
WHERE ENAME LIKE “_A%”;

– 查询名字的倒数第三个字符是A的员工
SELECT *
FROM EMP
WHERE ENAME LIKE “%A__”;

– 更新语句
UPDATE EMP SET ENAME = “SMI%TH” WHERE EMPNO =7369;
UPDATE EMP SET ENAME = “SMI_TH” WHERE EMPNO =7369;

– 恢复数据
UPDATE EMP SET ENAME = “SMITH” WHERE EMPNO =7369;

– 查询名字中有% 或 _ 的员工,必须要使用转义符,也就是要查询\后面的字符
SELECT *
FROM EMP
WHERE ENAME LIKE “%%%”;

SELECT *
FROM EMP
WHERE ENAME LIKE “%_%”;

/豪华的分割线*********/
– 对查询结果进行排序,需要使用ORDER BY子句
/*
语法
SELECT 自动列表
FROM 表名
[WHERE 查询条件]
[ORDER BY 排序字段1,排序字段2,…]

语句执行的顺序
1.SELECT FROM 查询出全部的数据
2.使用WHERE对数据进行过滤
3.过滤后的数据使用ORDER BY进行排序
*/

– 排序的时候,可以按照单个字段排序,也可以按照多个字段排序

– =============================================================================================================
– 1.排序的时候,可以按照单个字段排序

SELECT *
FROM EMP
WHERE DEPTNO = 20;

– 查询出来的结果默认是没有顺序的,我们如果需要对查询结果排序,就需要使用ORDER BY 子句对结果进行排序
SELECT *
FROM EMP
WHERE DEPTNO = 20
ORDER BY SAL;

– 按照字段排序的时候,默认的是按照字段升序排列,我们可以指定字段的排序顺序
SELECT *
FROM EMP
WHERE DEPTNO = 20
ORDER BY SAL ASC; – 指定按照sal升序排列

SELECT *
FROM EMP
WHERE DEPTNO = 20
ORDER BY SAL DESC; – 指定按照sal降序排列

– =============================================================================================================
– 2.排序的时候,可以按照多个字段排序,每个字段可以使用不同的排序规则

– 查询工资大于1600的员工信息,并且按照部门编号升序排列,工资降序排序
SELECT *
FROM EMP
WHERE SAL > 1600
ORDER BY DEPTNO ASC ,SAL DESC;

– =============================================================================================================
– 3.排序的时候,可以使用字段别名对结果进行排序

– 查询员工的年薪信息,并且按照年薪降序排列
SELECT EMPNO ,ENAME ,SAL ,SAL *12 “年薪”
FROM EMP
ORDER BY 年薪 DESC;

– =============================================================================================================
– 4.按照字段在查询列表中的位置进行排序,
– 在下面的查询结果中,SAL字段的位置是6.排序的时候 ORDER BY 6,也就是按照SAL进行排序
– 但是这种方式不要使用;因为表中的字段会随时发生变化
SELECT *
FROM EMP
ORDER BY 6;

/豪华的分割线*********/
– 数据库函数,类似于Java的方法,可以接收一个或多个参数,在函数内部完成计算,并且最终返回结算结果

– 数据库函数的分类(Java中没有这种方式)
– 1.单行函数:每次将单条的数据当做参数,输入给函数,得到这行数据对应的结果
– 2.多行函数:每次将多条的记录当做参数,输入给函数,得到多行记录对应的单行结果
– 例如:查询所有员工的最高工资,我们需要把14个SAL都输入给函数,最终得到一条结果

– 注意:
– 1.使用函数的时候我们是把查询结果当做参数输入给函数的,函数的执行结果并不会影响表中原来的数据
– 2.数据库函数是有方言的;也就是有可能在A数据库中可以执行的函数,在B数据库中无法执行

– 单行函数

– =============================================================================================================
– 1.LOWER()函数,将字符型的数据转换为小写的字符
– 使用方式: LOWER(字符型的的字段或字符型的数据)

SELECT EMPNO ,ENAME “原来的名字” ,LOWER(ENAME) “小写的名字”
FROM EMP;

– 我们在数据库严格区分大小写的情况下,让数据库忽略大小写
SELECT *
FROM EMP
WHERE BINARY LOWER(ENAME) = ‘smith’; – 把ENAME转换为小写的字符之后,再去和"smith"进行比较

– =============================================================================================================
– 2.UPPER()函数,将字符型的数据转换为大写的字符
– 使用方式: UPPER(字符型的的字段或字符型的数据)

– 我们在数据库严格区分大小写的情况下,让数据库忽略大小写
SELECT *
FROM EMP
WHERE BINARY ENAME = UPPER(‘smith’); – 把小写的"smith"转换为大写的,也就是"SMITH",然后再去和EANME进行比较

– =============================================================================================================
– 3.SUBSTR()截取字符串
– 使用方式:SUBSTR(被截取字段名称,起启下标,截取长度) – 下标从1开始

– 截取员工的名字,从第二个字符开始截取,截取一个长度
SELECT EMPNO ,ENAME “原来的名字” ,SUBSTR(ENAME,2,1) “截取的名字”
FROM EMP ;

– 查询名字的第二个字符是A的员工
SELECT EMPNO ,ENAME “原来的名字” ,SUBSTR(ENAME,2,1) “截取的名字”
FROM EMP
WHERE SUBSTR(ENAME,2,1) = “A”;

– 也可以使用模糊查询完成上面的功能
SELECT *
FROM EMP
WHERE ENAME LIKE “_A%”;

– =============================================================================================================
– 4.LENGTH()函数,返回字符型数据的长度,也就是字符的个数
– 使用方式: LENGTH (字符型的字段或字符型的数据)

– 查询员工的名字及其名字的长度
SELECT EMPNO,ENAME ,LENGTH(ENAME) “名字的长度”
FROM EMP;

– =============================================================================================================
– 5.TRIM() 去除字符型数据首尾的空格,但是不会去除中间的空格
– MySql中也是采用Unicode编码的," "空格也是一个有效的字符

– 更新数据
UPDATE EMP SET ENAME = " SMI TH " WHERE EMPNO =7369;

– 单行函数是可以嵌套的! 嵌套的时候首先执行里面的函数,把里面函数的结果当做外面函数的参数
SELECT EMPNO ,ENAME “原来的名字” ,LENGTH(ENAME) “原来名字的长度” , TRIM(ENAME) “去除首尾空格之后的名字” ,LENGTH( TRIM(ENAME) ) “去除首尾空格之后名字的长度”
FROM EMP
WHERE EMPNO =7369 ;

– =============================================================================================================
– 6.ROUND函数：四舍五入函数,对数值的数据进行四舍五入
– 用法：ROUND(要四舍五入的数字,四舍五入到哪一位)，默认保留整数位

– 查询EMP表的结构,SAL和COMM字段的类型是double(7,2) ,表示带小数的数值,数据的有效位数是7位,小数点后占2位
– 测试double(7,2)这种类型
UPDATE EMP SET ENAME=“SMITH”, SAL = 753.368 WHERE EMPNO =7369;
SELECT * FROM EMP;

– 恢复数据
UPDATE EMP SET ENAME=“SMITH”, SAL = 800 WHERE EMPNO =7369;

– 查询7369的工资,精确到不同的位数
SELECT EMPNO,ENAME ,SAL “原来的工资” ,ROUND(SAL) “精确到个位的工资” ,ROUND(SAL,0) “精确到个位的工资”,ROUND(SAL,1) “精确到小数点后1位的工资”
,ROUND(SAL,-1) “精确到小数点后-1位(也就是十位)的工资”
FROM EMP
WHERE EMPNO =7369;

– 把175.3654这个常数精确到不同的位数
– 下面的查询语句中没有FROM,实际上这是从数据库的系统表中进行相关的查询,表名可以忽略
– 可以使用这种方式处理常数
SELECT 175.3654 “原来的数值” ,ROUND(175.3654 ,0) “精确到个位的数值” ,ROUND(175.3654 ,1) “精确到小数点后1位的数值” ,
ROUND(175.3654 ,-1) “精确到十位的数值” ;

– =============================================================================================================
– 7.RAND() 产生随机小数(0~0.9999999)的函数

– 产生1~100的随机整数
SELECT ROUND(RAND()*99 +1 ) ; – 0~0.99999 *99 -> 0~98.9999 +1-> 1~99.999 -> 四舍五入 -> 1~100

– =============================================================================================================
– 8.IFNULL()函数,专门用来处理NULL数据的

– NULL是数据库中一种特殊的数据,三种主要类型都支持NULL
– 注意:在算术表达式中如果出现NULL,则结果肯定是NULL

– 出现员工的年收入 (年收入 = (SAL + COMM) * 12)
SELECT EMPNO,ENAME ,SAL ,COMM ,(SAL+COMM) * 12 “年收入”
FROM EMP ;

– 正确的处理方式:如果COMM是NULL,就把COMM替换成0,这就是IFNULL()的使用方式
SELECT EMPNO,ENAME ,SAL ,COMM ,(SAL+ IFNULL(COMM,0)) * 12 “年收入”
FROM EMP ;

– 查询员工的年收入,如果COMM是NULL,就替换为100
SELECT EMPNO,ENAME ,SAL ,COMM ,(SAL+ IFNULL(COMM,100)) * 12 “年收入”
FROM EMP ;

– =============================================================================================================
– 9.CASE WHEN 函数,执行分支语句的功能

– 用法：匹配工作岗位，当为MANAGER时，薪水上调10%，当为SALESMAN时，薪水上调50%，其它岗位薪水不变
– 如果职位是"MANAGER",则返回SAL1.1; 如果职位是"SALESMAN",则返回SAL1.5; 否则直接返回SAL;
SELECT EMPNO,ENAME,JOB ,SAL ,(CASE JOB WHEN “MANAGER” THEN SAL1.1 WHEN “SALESMAN” THEN SAL1.5 ELSE SAL END) “NEWSAL”
FROM EMP;

SELECT * FROM EMP;

– =============================================================================================================
– 10.STR_TO_DATE(str,format) 函数,把字符型的数据转换为日期型的数据

– 查询雇佣日期是"1981-02-22"的员工的信息
SELECT *
FROM EMP
WHERE HIREDATE = “1981-02-22”;

– 查询条件中使用的 HIREDATE = “1981-02-22”, 实际上"1981-02-22"是一个字符型的数据,而HIREDATE字段的类型是DATE类型
– 字符型的数据和日期型的数据进行有效的比较,说明数据库中自动的字符型的数据转换为日期型的数据
– 使用的就是STR_TO_DATE()函数进行转换的.

– 上面语句的实际运行过程如下
SELECT *
FROM EMP
WHERE HIREDATE = STR_TO_DATE(“1981-02-22”,"%Y-%m-%d");

– 注意:使用STR_TO_DATE()函数的时候,日期字符串的格式必须要和模板的格式一致,转换才能成功
SELECT *
FROM EMP
WHERE HIREDATE = STR_TO_DATE(“1981/22/02”,"%Y/%d/%m");

– 关于日期型的数据,后面我们会继续讲解

– =============================================================================================================
– 11.DATE_FORMAT(date,format) 将日期型的数据转为为特定格式的字符串

– 查询员工表的信息
SELECT * FROM EMP;

– 在上面的查询结果中,HIREDATE 字段显示的数据是"1980-12-17"等,实际上这是一个字符型的数据
– MySql会自动的把日期型的数据转换为字符型的数据,默认的格式就是"年-月-日"
– 上面语句的实际执行过程如下
SELECT EMPNO,ENAME,HIREDATE ,DATE_FORMAT(HIREDATE,"%Y-%m-%d")
FROM EMP;

– 我们可以使用DATE_FORMAT()把字符型的数据转换为特定格式的字符串
SELECT EMPNO,ENAME,HIREDATE ,DATE_FORMAT(HIREDATE,"%Y-%m-%d") HIREDATE1, DATE_FORMAT(HIREDATE,"%Y/%m/%d") HIREDATE2
,DATE_FORMAT(HIREDATE,"%Y-%m/%d") HIREDATE3
FROM EMP;

– 关于日期型的数据,后面我们会继续讲解

/豪华的分割线*********/
– =============================================================================================================
– =============================================================================================================
– =============================================================================================================
– =============================================================================================================

1.drop database bjpowernode删除数据库
2.查询EMP表的建表语句show create table emp
3.选择我们正在执行的数据库
select database();
4.查询表的结构
desc emp;
5.再次查询数据库中的表
show tables
show databases
create database bjpowernode
use bjpowernode
在数据库中，通过一个个的表来保存信息，表中行也有列，列也称作为字段

俩种登陆方式，远程登陆：mysql -uroot -proot -h192.168.148.8 P3306

	本地登陆：mysql -uroot -proot

在cmd中输入net start mysql
或者net stop mysql判断mysql命令是否能用

将字符型的数据转化为特定格式的字符串：date_format(HIREDATE,"%Y-%M-%D");

where hiredate = str _ to_date(“1981/02/03”,"%y/%m/%d");

ifnull(comm,100);

/豪华的分割线*********/
– 数据库函数

– 多行函数:每次将多条记录当做参数,输入给函数,得到多行记录对应的单行结果
– 多行函数也称为组函数,分组函数,聚合函数

– 多行函数的注意事项:
– 1.多行函数会自动的忽略NULL,不需要手动的用WHERE排除NULL
– 2.多行函数不能出现在WHERE子句中

SELECT * FROM EMP;

– =============================================================================================================
– 1.计算数值型数据总和的SUM()函数,只能针对数值型的数据

– 出现员工的工资总和,也就是把14个SAL累计在一起
SELECT SUM(SAL) SUMSAL FROM EMP;

– 出现员工的津贴总和;把4个不为NULL的comm进行累计的,因为组函数会自动的忽略空值
SELECT SUM(COMM) SUMCOMM FROM EMP;

– 查询员工的月收入总和 (月收入 = SAL + COMM)
– 下面的语句是错误的!因为:这是把4个sum+comm 不为null的数据进行累计的
SELECT SUM(SAL + COMM) SUMSAL FROM EMP;

– 正确的语句:需要使用IFNULL对COMM进行处理,
SELECT SUM(SAL + IFNULL(COMM,0)) SUMSAL1 ,(SUM(SAL) + SUM(COMM))SUMSAL2 FROM EMP;

– =============================================================================================================
– 2.查询记录总和的count()函数

– count()函数有两种用法
– (1)count(*) 查询表中所有记录总数
– (2)count(字段) 查询表中指定字段不为null的记录总数

– 查询EMP表的记录总数
SELECT COUNT(*) TOTALNUM FROM EMP;

– 查询comm不为null的记录总数
SELECT * FROM EMP WHERE COMM IS NOT NULL;
SELECT COUNT(COMM) FROM EMP;

– =============================================================================================================
– 3.查询平均值的AVG()函数,只能针对数值型的数据

– 查询所有员工的平均工资
SELECT AVG(SAL) AVGSAL1 , SUM(SAL)/COUNT(*) AVGSAL2 FROM EMP;

– 查询员工的平均津贴
SELECT AVG(COMM) AVGCOMM1 , SUM(COMM)/COUNT(COMM) AVGCOMM1 FROM EMP;

– =============================================================================================================
– 4.查询最大值得MAX()函数,可以适用于三种主要的类型

– 更新数据
UPDATE EMP SET SAL = 5000 WHERE EMPNO =7369;

SELECT * FROM EMP ORDER BY SAL DESC;

– 查询员工的最大工资
SELECT MAX(SAL) MAXSAL FROM EMP;

– 查询最大的名字;字符型数据也是有大小的.MySql中字符型数据的大小和Java中字符大小的规则是一样的!
– MySql和Java中字符的大小是由其Unicode编码来决定的,例如:a的编码是97,b的编码是98,所以a 小于 b
SELECT MAX(ENAME) MAXENAME FROM EMP;

– 查询最大的雇佣日期,也就是最晚的入职日期
– 日期型的数据也是有大小的,日期类型数据的大小可以由其字面值来决定,例如"1987"小于"2018"
SELECT MAX(HIREDATE) MAXHIREDATE FROM EMP;

– =============================================================================================================
– 5.查询最小中的MIN()函数,适用于三种主要类型

– 更新数据
UPDATE EMP SET SAL = 800 WHERE EMPNO =7369;

– 查询最小的工资
SELECT MIN(SAL) MINSAL FROM EMP;

– 查询最小的名字
SELECT MIN(ENAME) MINENAME FROM EMP;

– 查询最小的雇佣日期,也就是最早的雇佣日期
SELECT MIN(HIREDATE) MINHIREDATE FROM EMP;

– =============================================================================================================
– 6.多个多行函数可以在一起使用;
SELECT MAX(SAL) MAXSAL ,MIN(SAL) MINSAL ,AVG(SAL) AVGSAL ,SUM(SAL)SUMSAL ,SUM(COMM) SUMCOMM
FROM EMP

– 多行函数不能嵌套
SELECT MAX(AVG(SAL)) FROM EMP;

– =============================================================================================================
– 7.多行函数不能出现在WHERE子句中

– 查询工资大于平均工资的员工信息
SELECT *
FROM EMP
WHERE SAL > AVG(SAL);

/豪华的分割线*********/
– 使用 DISTINCT 去除查询结果中重复的记录
– (1)DISTINCT只能出现在SELECT之后,查询列表之前
– (2)DISTINCT可以去除单列重复的数据,也可以去除多列重复的数据

– =============================================================================================================
– 1.DISTINCT可以去除单列重复的数据

– 查询所有员工所从事的职位
SELECT DISTINCT JOB FROM EMP;

– 上面的查询结果中有大量的重复数据,这时候我们就需要使用DISTINCT可以去除单列重复的数据
SELECT JOB FROM EMP;

– =============================================================================================================
– 2.DISTINCT可以去除多列重复的数据

– 查询员工的职位信息和部门编号信息
SELECT DEPTNO ,JOB FROM EMP;

– 上面的查询结果中,deptno + job ,两列数据的组合有重复的数据,需要使用DISTINCT去除重复的数据
SELECT DISTINCT DEPTNO ,JOB FROM EMP ORDER BY DEPTNO;

/豪华的分割线*********/
– 分组查询,也就是把总的数据按照不同的条件划分为多组,然后使用组函数进行查询,把数据分成多少组,就会得到多少条结果
– 例如:把所有的员工按照部门编号分组,将分成三组,然后查询每组的最高工资,将得到三个结果
SELECT * FROM EMP ORDER BY DEPTNO;

– 分组查询需要使用GROUP BY子句(从句)
/*
分组查询的语法
SELECT 查询列表
FROM 表名
GROUP BY 分组字段1 [,分组字段2]

分组查询的规则:
(1)查询在查询列表中的字段,要么出现在组函数中,要么出现在GROUP BY子句中.这是分组查询的首先方式
(2)分组字段只出现在GROUP BY子句中;这种方式的查询结果不明确,所以尽量不要使用这种方式
*/

– =============================================================================================================
– 1,按照单个字段进行分组

– 按照部门编号分组,查询最大工资
– 分析:出现在查询列表中有两个字段,是DEPTNO和SAL; SAL出现在组函数中,DEPTNO出现在GROUP BY子句中,所以下面语句是符合规则的
SELECT DEPTNO ,MAX(SAL) MAXSAL MAXSAL
FROM EMP
GROUP BY DEPTNO ;

– 校验数据
SELECT *
FROM EMP
ORDER BY DEPTNO ,SAL DESC;

– 分析,分组字段DEPTNO只出现在GROUP BY子句中,这个语句也是符合规则的
– 但是这种方式的查询结果不明确,所以尽量不要使用这种方式
SELECT MAX(SAL) MAXSAL
FROM EMP
GROUP BY DEPTNO;

– 按照职位分组,查询员工的平均工资
SELECT JOB ,AVG(SAL) AVGSAL
FROM EMP
GROUP BY JOB;

– 按照部门编号分组,查询最大工资及其最大工资的员工姓名
– 分析,出现在查询列表中有三个字段(ENAME,SAL,DEPTNO);SAL出现在组函数中,DEPTNO出现在GROUP BY子句中
– 但是,ENAME既没有出现在组函数中,也没有出现在GROUP BY子句中,所以不符合规则的!
– 这样的语句在Oracle中根本无法执行;在MySql中可以执行,但是查询结果是错误的!
SELECT ENAME, DEPTNO,MAX(SAL) MAXSAL
FROM EMP
GROUP BY DEPTNO;

– 分析,出现在查询列表中的字段有三个(DEPTNO ,SAL,COMM) ,SAL和COMM出现在组函数中,DEPTNO出现在GROUP BY子句中
– 符合规则的
SELECT DEPTNO, MAX(SAL) MAXSAL ,MIN(SAL) MINSAL ,AVG(SAL) AVGSAL ,SUM(SAL)SUMSAL ,SUM(COMM) SUMCOMM
FROM EMP
GROUP BY DEPTNO;

– =============================================================================================================
– 2.按照多个字段进行分组

– 按照部门编号和职位分组,查询最高工资
– 分析:出现在查询列表中有三个字段(DEPTNO,JOB ,SAL) ;SAL出现在分组函数中,DEPTNO和JOB出现在GROUP BY子句中,这个语句符合规则
SELECT DEPTNO,JOB ,MAX(SAL) MAXSAL
FROM EMP
GROUP BY DEPTNO ,JOB;

– =============================================================================================================
– 3.对分组后的数据进行过滤

– 按照部门编号分组,查询平均工资,并且显示平均工资大于2000的数据
– 按照我们以前讲的内容,要对查询结果进行过滤,需要使用WHERE子句
– 但是下面的语句是错误的,因为多行函数不能出现在WHERE子句中
SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP
GROUP BY DEPTNO
WHERE AVGSAL > 2000;

– 如果我们需要对分组后的数据进行过滤,是不能使用WHERE子句的,而是要使用HAVING子句
– HAVING子句就是专门对分组之后的数据进行过滤的
SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP
GROUP BY DEPTNO
HAVING AVGSAL > 2000;

SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > 2000;

/豪华的分割线*********/
– 简单出现语句的完整语句
/*
SELECT 字段列表
FROM 表名
[WHERE 查询条件]
[GROUP BY 分组字段]
[HAVING 查询条件]
[ORDER BY 排序字段]

以上语句执行的顺序
1.SELECT FROM 查询出全部的数据
2.使用WEHRE对查询结果进行过滤
3.过滤后的数据使用GROUP BY进行分组
4.分组后的数据使用HAVING再次过滤
5.再次过滤后的数据使用ORDER BY进行排序

注意:
1.以上语句的顺序是固定的
2.需要对数据进行过滤,优先使用WHERE进行过滤,这样的效率比较高
*/

– 把COMM为NULL的员工按照部门编号分组,查询平均工资,并且显示平均工资大于2000的数据,然后按照平均工资升序排序
SELECT DEPTNO, ROUND(AVG(SAL)) AVGSAL
FROM EMP
WHERE COMM IS NULL
GROUP BY DEPTNO
HAVING AVGSAL > 2000
ORDER BY AVGSAL ASC;

/豪华的分割线*********/
– 多表查询,也称为跨表查询,也称为联合查询,也就是从多个表中查询相关的数据

– 多表查询的分类
– 1按照语法的年代划分:
– (1).SQL1992标准:FROM后面是多个表,多个表通过逗号分隔
– (2).SQL1999标准:FROM 后面是一个表,通过JOIN方式连接其他的表

– 2.按照连接的方式分:
– (1)内连接(INNER JOIN):可以查询满足一一对应关系的数据;例如这个员工有所属的部门,这个部门有对应的员工,这样的数据满足
– 一一对应的关系,可以使用内连接查询出来;换一个角度,也就是查询两个表中有重复数据的记录
– (2)外连接(OUTER JOIN):可以查询不满足一一对应关系的数据;例如有的员工没有部门,有的部门没有员工,这样的数据不满足一一
– 对应的关系,使用内连接是查询不到的,但是可以使用外连接查询出来;例如7369这个员工没有对供应的部门,40这个部门没有对应
– 的员工,这样的数据用内连接是查询不到的,但是可以使用外连接查询出来

– 3.内连接的分类
– (1)等值连接
– (2)非等值连接
– (3)自连接

– 4.外连接的分类
– (1)左外连接(LEFT OUTER JOIN)
– (2)右外连接(RIGHT OUTER JOIN)
– (3)全外连接(FULL OUTER JOIN)

– =============================================================================================================
– 内连接的分类
– (1)等值连接:建立在父子表关系上,用等号来连接两个表
– (2)非等值连接:两个表之间没有父子关系,用非等号来连接两个表
– (3)自连接:通过别名将一个表虚拟成两个表(父子表),然后再这两个表上面做等值连接

– (1)等值连接:建立在父子表关系上,用等号来连接两个表

– 查询员工信息及其部门信息
SELECT * FROM EMP;
SELECT * FROM DEPT;

– SQL1992标准

– 当两个表中有相同名称的字段的时候,为了区分不同表中的字段,需要使用表名作为前缀
SELECT EMP.EMPNO,EMP.ENAME,EMP.JOB ,EMP.MGR ,EMP.HIREDATE ,EMP.SAL,EMP.COMM ,EMP.DEPTNO,DEPT.DEPTNO ,DEPT.DNAME,DEPT.LOC
FROM EMP ,DEPT;

– 上面的查询结果中有56条件记录;EMP表中有14条记录,DEPT表中有4条记录 14*4 = 56;
– 这样的结果是错误的!这种现象称为笛卡尔乘积.
– 笛卡尔乘积产生的原因:就是因为在连接查询的时候没有加入正确的连接条件
– 如何避免笛卡尔乘积呢?需要加入正确的连接条件
SELECT EMP.EMPNO,EMP.ENAME,EMP.JOB ,EMP.MGR ,EMP.HIREDATE ,EMP.SAL,EMP.COMM ,DEPT.DEPTNO ,DEPT.DNAME,DEPT.LOC
FROM EMP ,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

– 使用表名作为前缀的时候,如果表名很长,写起来也忒麻烦了,可以被表起个简单的别名
SELECT E.EMPNO,E.ENAME,E.JOB,E.HIREDATE,E.SAL,E.COMM,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E ,DEPT D
WHERE E.DEPTNO = D.DEPTNO;

– 可以在等值连接的基础上使用AND加入其它的查询条件
– 查询部门编号是20的员工信息及其部门信息
SELECT E.EMPNO,E.ENAME,E.JOB,E.HIREDATE,E.SAL,E.COMM,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E ,DEPT D
WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = 20;

– 查询工资在1600~3000之间的员工信息及其部门信息
SELECT E.EMPNO,E.ENAME,E.JOB ,E.SAL ,E.COMM,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E ,DEPT D
WHERE (E.DEPTNO = D.DEPTNO) AND (E.SAL BETWEEN 1600 AND 3000);

– SQL1999标准:FROM 后面是一个表,通过JOIN方式连接其他的表

– 查询员工信息及其部门信息
SELECT E.EMPNO,E.ENAME,E.JOB ,E.SAL ,E.COMM,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
INNER JOIN DEPT D ON( E.DEPTNO = D.DEPTNO);

– 在等值连接的基础上可以使用AND加入其他的查询条件
SELECT E.EMPNO,E.ENAME,E.JOB ,E.SAL ,E.COMM,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
INNER JOIN DEPT D ON( E.DEPTNO = D.DEPTNO)
AND D.DEPTNO = 30;

– 在等值连接的基础上,也可以使用WHERE加入其他的查询条件
SELECT E.EMPNO,E.ENAME,E.JOB ,E.SAL ,E.COMM, D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
INNER JOIN DEPT D ON( E.DEPTNO = D.DEPTNO)
WHERE D.DEPTNO = 30;

– 使用内连接的时候INNER可以忽略
SELECT E.EMPNO,E.ENAME,E.JOB ,E.SAL ,E.COMM, D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
JOIN DEPT D ON( E.DEPTNO = D.DEPTNO)
WHERE D.DEPTNO = 30;

– (2)非等值连接:两个表之间没有父子关系,用非等号来连接两个表
SELECT * FROM EMP;
SELECT * FROM SALGRADE;

– 查询员工信息及其工资级别信息

– SQL1992标准
SELECT E.* ,S.*
FROM EMP E ,SALGRADE S
WHERE (E.SAL BETWEEN S.LOSAL AND S.HISAL) AND (S.GRADE = 2)

– SQL1999标准
SELECT E.* ,S.*
FROM EMP E
JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE E.DEPTNO = 20;

– (3)自连接:通过别名将一个表虚拟成两个表(父子表),然后再这两个表上面做等值连接
SELECT * FROM EMP;

– 查询员工信息及其经理信息

– SQL1992标准
SELECT E.EMPNO “员工编号”,E.ENAME “员工姓名”,M.EMPNO “经理编号”,M.ENAME “经理姓名”
FROM EMP E ,EMP M
WHERE (E.MGR = M.EMPNO) AND (E.EMPNO = 7369);

– SQL1999标准
SELECT E.EMPNO “员工编号”,E.ENAME “员工姓名”,M.EMPNO “经理编号”,M.ENAME “经理姓名”
FROM EMP E
JOIN EMP M ON (E.MGR = M.EMPNO)
WHERE E.EMPNO =7902;

– =============================================================================================================
– 外连接的分类:外连接(OUTER JOIN):可以查询不满足一一对应关系的数据;例如有的员工没有不买,有的部门没有员工,这样的
– 数据不满足一一对应的关系,使用内连接是查询不到的,但是可以使用外连接查询出来;例如7369这个员工没有对供应的部门,
– 的40这个部门没有对应员工,这样的数据用内连接是查询不到的,但是可以使用外连接查询出来
– 外连接查询数据的条数大于等于内连接查询数据的条数;
– 数据库会自动把不匹配的数据设置为NULL;

UPDATE EMP SET DEPTNO= NULL WHERE EMPNO =7369;
SELECT * FROM EMP;
SELECT * FROM DEPT;

– 使用内连接之等值连接是无法查询到7369和40这个部门的信息的
SELECT E.EMPNO,E.ENAME,E.JOB ,E.SAL ,E.COMM,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E ,DEPT D
WHERE (E.DEPTNO = D.DEPTNO);

– 外连接的分类:
– (1)左外连接(LEFT OUTER JOIN)
– (2)右外连接(RIGHT OUTER JOIN)
– (3)全外连接(FULL OUTER JOIN)

– (1)左外连接(LEFT OUTER JOIN):我们用 LEFT OUTER JOIN 作为分界线,EMP表就是左表,左外连接可以把左表中不满足对应
– 关系的数据查询出来;例如7369这个员工没有对应的部门,可以被查询出来
SELECT E.EMPNO,E.ENAME ,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
LEFT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

– 左表的位置是相对的,我们也可以把DEPT表当做左表
SELECT E.EMPNO,E.ENAME ,D.DEPTNO,D.DNAME,D.LOC
FROM DEPT D
LEFT OUTER JOIN EMP E ON (E.DEPTNO = D.DEPTNO);

– 使用外连接的时候,OUTER可以忽略
SELECT E.EMPNO,E.ENAME ,D.DEPTNO,D.DNAME,D.LOC
FROM DEPT D
LEFT JOIN EMP E ON (E.DEPTNO = D.DEPTNO);

– (2)右外连接(RIGHT OUTER JOIN):用RIGHT OUTER JOIN作为分解线,可以把右表中不满足对应关系的数据查询出来
– 例如DEPT表是右表,40这个部门没有对应的员工,可以被查询出来
SELECT E.EMPNO,E.ENAME ,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
RIGHT JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

– 右表的位置是相对的,我们也可以把EMP表当做右表
SELECT E.EMPNO,E.ENAME ,D.DEPTNO,D.DNAME,D.LOC
FROM DEPT D
RIGHT JOIN EMP E ON (E.DEPTNO = D.DEPTNO);

– 左外连接的功能也可以用右外连接来实现
– 例如我们要查询dept表中不满足对应关系的数据,我可以使用左外连接,也可以使用右外连接
SELECT E.EMPNO,E.ENAME ,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
RIGHT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

SELECT E.EMPNO,E.ENAME ,D.DEPTNO,D.DNAME,D.LOC
FROM DEPT D
LEFT OUTER JOIN EMP E ON (E.DEPTNO = D.DEPTNO);

– (3)全外连接(FULL OUTER JOIN):用FULL OUTER JOIN 作为分界线,可以把左右两边表中不满足对应关系的数据都查询出来
– Oracle支持全外连接;但是MySql不支持全外连接
SELECT E.EMPNO,E.ENAME ,D.DEPTNO,D.DNAME,D.LOC
FROM DEPT D
FULL OUTER JOIN EMP E ON (E.DEPTNO = D.DEPTNO);

– =============================================================================================================
– 从N个表中查询相关的数据,需要N-1个INNER JOIN 句(这就是多表查询的原理)

– 查询员工信息及其部门信息及其工资级别信息

– SQL1992标准
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL ,E.COMM,D.DEPTNO,D.DNAME,D.LOC,S.GRADE,S.LOSAL,S.HISAL
FROM EMP E ,DEPT D,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO = D.DEPTNO AND D.DEPTNO = 20;

– SQL1999标准
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL ,E.COMM,D.DEPTNO,D.DNAME,D.LOC,S.GRADE,S.LOSAL,S.HISAL
FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE D.DEPTNO = 20;

/豪华的分割线*********/
– 子查询:用来给主查询提供查询条件而首先执行的一个查询,然后主查询使用子查询的查询结果
– 为了语法的清晰,要把子查询放到()里面

– 子查询的分类
– 1.出现在WHERE后面的子查询,用来给主查询提供查询条件的
– 2.出现在FROM后面的子查询,用来给主查询提供查询数据的
– 3.出现在查询列表中的子查询,功能类似于外连接的效果;这种方式知道就可以了

– =============================================================================================================
– 1.出现在WHERE后面的子查询,用来给主查询提供查询条件的

– 查询工资比平均工资大的员工的信息
– 思路:
– (1)查询员工表的平均工资
SELECT AVG(SAL) FROM EMP;

– (2) 把上面的查询结果当做条件
SELECT *
FROM EMP
WHERE SAL > 2073;

– (3)把上面的两个查询语句合并到一起
SELECT *
FROM EMP
WHERE SAL > ( SELECT AVG(SAL) FROM EMP );

– =============================================================================================================
– 2.出现在FROM后面的子查询,用来给主查询提供查询数据的
– 语句执行的过程:把子查询的查询结果当做一个临时表T,然后主查询从这个临时表中查询相关的数据

SELECT T.*
FROM ( SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL ,E.COMM,D.DEPTNO,D.DNAME,D.LOC,S.GRADE,S.LOSAL,S.HISAL
FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)) T
WHERE T.EMPNO = 7499;

– =============================================================================================================
– 3.出现在查询列表中的子查询,功能类似于外连接的效果;这种方式知道就可以了;
SELECT E.EMPNO,E.ENAME ,E.SAL ,E.DEPTNO ,(SELECT D.DNAME FROM DEPT D WHERE E.DEPTNO = D.DEPTNO)
FROM EMP E ;

/豪华的分割线*********/
– 分组查询,多表查询,子查询综合应用

– 案例1:按照部门编号分组查询最大工资,及其最大工资的员工姓名
– 思路:
– (1)按照部门编号分组查询最高工资
SELECT DEPTNO ,MAX(SAL) MAXSAL
FROM EMP
GROUP BY DEPTNO;

– (2)把上面的查询结果当做一个临时表T,和EMP表进行连接查询,两个表的连接条件是(E.DEPTNO = T.DEPTNO AND E.SAL = T.MAXSAL)
SELECT E.EMPNO,E.ENAME,T.MAXSAL ,T.DEPTNO
FROM EMP E
INNER JOIN ( SELECT DEPTNO ,MAX(SAL) MAXSAL
FROM EMP
GROUP BY DEPTNO) T ON (E.DEPTNO = T.DEPTNO AND E.SAL = T.MAXSAL)
ORDER BY T.DEPTNO;

– (3)校验数据
SELECT *
FROM EMP
ORDER BY DEPTNO , SAL DESC;

– 案例2:哪些人的薪水在部门平均薪水之上
– 思路:
– (1)按照部门编号分组查询平均工资
SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP
GROUP BY DEPTNO;

– (2)把上面的查询结果当做一个临时表T,和EMP表进行连接.连接的条件是(E.DEPTNO = T.DEPTNO AND E.SAL > T.AVGSAL)
SELECT E.EMPNO,E.ENAME,E.SAL ,T.AVGSAL ,T.DEPTNO
FROM EMP E
JOIN ( SELECT DEPTNO ,AVG(SAL) AVGSAL
FROM EMP
GROUP BY DEPTNO ) T ON ( T.DEPTNO = E.DEPTNO AND E.SAL > T.AVGSAL )
ORDER BY T.DEPTNO;

/豪华的分割线*********/
– DML语句(数据库操作语句) ,包括INSERT(向表中插入数) ,DELETE(从表中删除数据) ,UPDATE(更新表中的数据)
– 我们执行DML语句的时候,数据库返回的是收到DML语句影响的数据的条数,称为更新计数器

– =============================================================================================================
– 1.INSERT语句,向表中插入数据
/*
插入语句的语法
INSERT INTO 表名(字段列表) VALUES (值列表)
字段列表必须要和值列表一一对应
*/

– 向表中指定的字段插入数据
– 下面的语句在cmd客户端中插入失败!这是因为MySql数据库的编码格式是UTF-8,而cmd窗口的编码格式是GBK
– 但是下面的语句在窗口客户端中可以执行成功.因为窗口客户端的编码格式就是UTF-8
INSERT INTO EMP (EMPNO,ENAME ) VALUES (9000,‘张0’);

– 向表中全部的字段插入数据
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (9001,“张1”,“软件工程师”,7902,‘1982-10-9’,2000,NULL,30);

– 向表中全部字段插入数据的时候,可以忽略字段列表
– 也就是说,插入数据的时候如果没有指明字段列表,也就是向表中全部字段插入数据,必须要按照字段的顺序给出足够的值
INSERT INTO EMP VALUES (9002,“张2”,“软件工程师”,7902,‘1982-10-9’,2000,NULL,30);

– 下面的语句错误,因为没有指明字段列表,也即是向表中全部字段插入数据,但是只给出了7个值
INSERT INTO EMP VALUES (9003,“张2”,“软件工程师”,7902,‘1982-10-9’,2000,NULL);

– 关于日期型的数据

INSERT INTO EMP VALUES (9003,“张3”,“软件工程师”,7902,‘1982-10-9’,2000,NULL,30);

– EMP表的HIREDATE字段的类型是DATE类型,HIREDATE字段插入的值是"1982-10-9",而这个值是字符类型的数据
– MySql会自动的调用STR_TO_DATE()函数将字符类型的数据转换为日期类型的数据,上面语句的实际运行过程如下
INSERT INTO EMP VALUES (9003,“张3”,“软件工程师”,7902, STR_TO_DATE(‘1982-10-9’,"%Y-%m-%d"),2000,NULL,30);

– STR_TO_DATE()这个函数只能在MySql中有效,并且每种数据库默认的日期格式都是不同的
– MySql默认的日期格式是"年-月-日",而Oracle默认的日期格式是"日-月-年"
– 这样就造成一个问题,在不同的数据库中插入日期数据的SQL语句都是不同的,给SQL语句的移植带来问题
– 所以:在实际的开发中,本来应该是DATE类型的字段,通常会被设计成字符型的字段,增加SQL语句的移植性

– 在MySql中提供了一个NOW()函数,可以获取当前时间点的日期(也就是日期型的数据) ,EMP表的HIREDATE就是date类型的
INSERT INTO EMP VALUES (9004,“张4”,“软件工程师”,7902,NOW(),2000,NULL,30);

– =============================================================================================================
– 2.使用UPDATE 更新表中的数据
– (1)在更新的时候可以更新单个的字段,也可以同时更新多个字段,多个字段之间使用逗号分隔
– (2)在更新表中的数据的时候,可以用WHERE加入更新条件,这样会更新满足条件的数据;如果没有加入更新条件,则更新所有的数据

UPDATE EMP SET JOB = “高级软件工程师” ,SAL = 2500 ,COMM=200 WHERE EMPNO IN (9000,9001);

UPDATE EMP SET JOB = “项目经理” ,SAL = 2500 ,COMM=200;

SELECT * FROM EMP;

– =============================================================================================================
– 3,删除表中的数据,要使用DELETE语句
– (1)删除表中的数据的时候,可以加入条件,这样只会删除满足条件的数据;如果没有加入条件则是删除所有的数据
DELETE FROM EMP WHERE EMPNO >= 9000;

DELETE FROM EMP;

/豪华的分割线*********/
– DDL语句(数据库定义语句) ,包括CREATE(创建表) ,DROP(删除表) ,ALTER(修改表)
– 我们执行DDL语句的时候,数据库返回的是0
– 在数据库表中是一个个的字段,字段所需要的信息(四个信息):字段名称, 字段的数据类型, 字段的长度,字段的约束
– DDL和DML的区别:DML操作的是表中的数据;DDL操作的是表

– =============================================================================================================
– 创建表的语句
CREATE TABLE STUS(
SID INT(4), – 字段后面以逗号间隔,
SNAME CHAR(4),
SEX CHAR(1),
BIRTHDAY DATE,
EMAIL VARCHAR(50) – 最后的字段后面没有逗号
) /我们可以在这里指明编码格式,如果没有指明编码格式就是数据库默认的编码格式/;

– 说明:
– INT(4)在Oracle中表示4位的整数,返回从0~9999; 不能超过位数限制;
– INT(4)在MySql中表示的是建议长度是4位

DESC STUS;

– 插入测试数据
INSERT INTO STUS VALUES (1,‘张1’,‘男’,‘1992-10-9’,‘zhangyi@163.com’);
INSERT INTO STUS VALUES (2,‘张2’,‘男’,‘1992-10-9’,‘zhangyi@163.com’);
INSERT INTO STUS VALUES (3,‘张3’,‘男’,‘1992-10-9’,‘zhangyi@163.com’);
INSERT INTO STUS VALUES (4,‘张4’,‘男’,‘1992-10-9’,‘zhangyi@163.com’);
INSERT INTO STUS VALUES (5,‘张5’,‘M’,‘1992-10-9’,‘zhangyi@163.com’);
INSERT INTO STUS VALUES (6,‘张6’,‘女的’,‘1992-10-9’,‘zhangyi@163.com’); – 错误:SEX字段的值超过了一个字符的长度限制
INSERT INTO STUS VALUES (10000,‘张6’,‘女’,‘1992-10-9’,‘zhangyi@163.com’);

– 查询结果
SELECT * FROM STUS;

– 删除表的语句 ;删除表的时候回连同表中的数据一起删除掉
DROP TABLE STUS;

– 如果在没有表的情况下执行删除表的语句,会导致数据库保存,我们可以用如下的语句避免错误
– 如果STUS表存在,就删除;如果不存在,就不删除了,这样可以避免错误
– 注意:这种语句是MySql特有的,其他的数据库都不支持这种语句
DROP TABLE IF EXISTS STUS;

– 在创建表的时候,我们可以给字段提供缺省值
DROP TABLE IF EXISTS STUS;
CREATE TABLE STUS(
SID INT(4),
SNAME CHAR(4),
SEX CHAR(1) DEFAULT ‘男’, /给sex字段提供了缺省值:男/
AGE INT(2)
);

– 在插入数据的时候,如果字段没有提供相应的数据
– (1)没有提供缺省值字段会被插入NULL
– (2)有缺省值的字段会被默认的插入缺省值
INSERT INTO STUS (SID ,SNAME) VALUES (1,“张1”);

– 当然,提供了缺省值的字段,也可以被插入其他的值
INSERT INTO STUS (SID ,SNAME,SEX) VALUES (2,“张2”,‘女’);

– 查询数据
SELECT * FROM STUS;

– 我们可以把建表语句和查询语句结合起来,也就是把查询语句中的字段当做新建表的字段,并且把查询语句中的结果插入到新表中
DROP TABLE IF EXISTS EMP_BAK;
CREATE TABLE EMP_BAK
AS
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL ,COMM
FROM EMP
WHERE SAL BETWEEN 1600 AND 3000;

– 从新表中查询数据
SELECT * FROM EMP_BAK;

– 任何有效的查询语句都可以和建表语句集合
DROP TABLE IF EXISTS EMP_INFO;

CREATE TABLE EMP_INFO
AS
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL ,E.COMM,D.DEPTNO,D.DNAME,D.LOC,S.GRADE,S.LOSAL,S.HISAL
FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

SELECT * FROM EMP_INFO;

– 如果出现语句中没有出现结果,则仅仅是把查询语句中的字段当做新建表的字段
CREATE TABLE EMP_BAK
AS
SELECT * FROM EMP WHERE SAL > 21000;

SELECT * FROM EMP_BAK;

– 我们可以把一个插入语句和一个查询语句结合起来,注意:查询语句的结果必须要和表的结构一致
– 也就是把查询语句的结果插入到表中
INSERT INTO EMP_BAK SELECT * FROM EMP WHERE SAL BETWEEN 1600 AND 3000;

– 删除表
DROP TABLE IF EXISTS EMP_BAK;

– =============================================================================================================
– ALTER,修改表的语句;对表的修改主要是对字段的修改,包括ADD(添加字段),DROP(删除字段) ,MODIFY(修改字段)
– 使用ALTER语句可以让我们在不影响表中数据的情况下,对表的结构进行修改

DROP TABLE IF EXISTS STUS;
CREATE TABLE STUS(
SID INT(4),
SNAME CHAR(4),
AGE INT(2),
BIRTHDAY DATE – 注意:DATE类型没有数据长度
);

– 插入测试数据
INSERT INTO STUS VALUES (1,“张1”,21,‘1992-12-8’);
INSERT INTO STUS VALUES (2,“张2”,21,‘1992-12-8’);
INSERT INTO STUS VALUES (3,“张3”,21,‘1992-12-8’);
INSERT INTO STUS VALUES (4,“张4”,21,‘1992-12-8’);
INSERT INTO STUS VALUES (5,“张5”,21,‘1992-12-8’);

– 查询数据
SELECT * FROM STUS;

– 使用ALTER语句给表中添加TEL字段,新的字段会被添加到表的末尾
ALTER TABLE STUS ADD TEL BIGINT(11);

– 查询表的结构
DESC STUS;

– 使用ALTER语句修改表中的字段
– (1)当该字段中没有数据的时候,字段的类型和字段的长度都是可以随便修改的
ALTER TABLE STUS MODIFY TEL CHAR(11);
ALTER TABLE STUS MODIFY TEL DATE; – 仅仅是演示语法而已
ALTER TABLE STUS MODIFY AGE INT(3);

– (2)当该字段中有数据的时候,增大字段的长度总是可以的
ALTER TABLE STUS MODIFY SNAME CHAR(10);

– (3)当该字段中有数据的时候,减少字段的长度,要根据该字段的最大数据来决定
– 例如:SNAME字段的最大数据是"张小发动机",其长度是5个字符,所以我们把SNAME从char(10)改为char(5)是可以的
– 但是如果我们想要把SNAME 改成char(4)就会报错
UPDATE STUS SET SNAME=“张小发动机” WHERE SID=1;
ALTER TABLE STUS MODIFY SNAME CHAR(5);
ALTER TABLE STUS MODIFY SNAME CHAR(4); – 报错

– (4)当该字段中有数据的时候,要修改字段的类型,要看该字段的数据能否转换为新的类型
– 如果数据可以转换为新的类型,则字段的类型就是可以修改的
– 例如:SID字段原来的类型是int(4),该字段的数据是1,2,3,4 ;1,2,3,4这样的数据可以被转换为字符型的数据吗?当然可以了
– 所以我们可以把SID从原来的int(4)改成char(4)
ALTER TABLE STUS MODIFY SID CHAR(4);

– 例如:age字段的类型是int(3),该字段的数据是21,22等;21,22这样的数据能否转换为DATE类型呢?是不能转换的
– 所以:age字段是不能从int(3)改成Date类型的
ALTER TABLE STUS MODIFY AGE DATE;

– 查询数据
SELECT * FROM STUS;

– 使用ALTER语句删除表中的字段
ALTER TABLE STUS DROP TEL;

/豪华的分割线*********/
– 使用UNION把两个结果集合并成一个结果集,也就是合并查询结果
– 合并两个结果集的时候,要求两个结果集的结构必须要一致(字段的个数,字段的类型,字段的顺序必须要一致,才能合并)

– UNION有两种使用方式
– 1.UNION : 会合并相同的数据;
– 2.UNION ALL : 不会合并相同的数据;

– =============================================================================================================
– 1.UNION : 会合并相同的数据;
SELECT *
FROM EMP
WHERE SAL > 2000
UNION
SELECT *
FROM EMP
WHERE SAL > 3000;

– =============================================================================================================
– 2.UNION ALL : 不会合并相同的数据;
SELECT *
FROM EMP
WHERE SAL > 2000
UNION ALL
SELECT *
FROM EMP
WHERE SAL > 3000;

– =============================================================================================================
– 3.如果两个查询结果的结构不一致,是无法合并的
SELECT EMPNO,ENAME,JOB ,MGR
FROM EMP
WHERE SAL > 2000
UNION
SELECT EMPNO,ENAME,JOB ,MGR ,HIREDATE
FROM EMP
WHERE SAL > 2000;

– =============================================================================================================
– 4.我们可以使用UNION实现全外连接的效果
UPDATE EMP SET DEPTNO = NULL WHERE EMPNO =7369;

SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR, E.HIREDATE ,E.SAL,E.COMM ,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
LEFT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
UNION
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR, E.HIREDATE ,E.SAL,E.COMM ,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E
RIGHT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

– =============================================================================================================

/豪华的分割线*********/
– 使用limit查询表中最前面的几条记录或中间某几条记录
– LIMIT的使用方式: limit 开始的下标(从0开始),连续的数据的条数

– =============================================================================================================
– 1.查询最前面的几条记录

– 查询工资最高的前4个员工的信息
– 思路:
– (1)查询所有员工的工资,按照工资降序排列
SELECT * FROM EMP ORDER BY SAL DESC;

– (2)会用LIMIT取得前4条记录
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 0,4; – 从0开始,连续的4条记录

– (3)上面的语句有可以用如下的方式
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 4; – 默认的就是从0开始的

– 查询雇佣最早的前5个员工的信息
SELECT * FROM EMP ORDER BY HIREDATE ASC LIMIT 5;

– =============================================================================================================
– 2.使用LIMIT查询中间某几条记录

– 查询工资最高的3~8位的员工信息
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 2,6 ; – 从2开始,连续的6条记录

– =============================================================================================================
– 3.使用LIMIT实现分页查询
– 所谓的分页查询:数据库中的数据忒多了,每次从数据库中查询固定条数的数据,分多次查询

/*
分页查询的公式:
pagesize: 表示每页显示的数据的条数; 例如:每页显示5条数据
pageno:页号,从1开始
limit的计算方式: Limit (pagesize -1) * pageno , pagesize;
例如:
第1页: limit 0,5;
第2页: limit 5,5;
第3页: limit 10,5;
*/

– 查询员工信息,按照sal降序排列,每次显示5条数据
– 第1页:
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 0,5;

– 第2页:
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 5,5;

– 第3页: 如果剩余的条数不够5条,则有多少就显示多少
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 10,5;

– 注意:使用分页查询的时候必须要给出明确的起始位置和连续的数据条数,不能使用表达式
SELECT * FROM EMP ORDER BY SAL DESC LIMIT (1-1)*5,5;

/豪华的分割线*********/
– 数据库约束
– 什么是约束:是加在字段上面的检测条件,只有符合条件的数据才能被操作(增删改,也就是DML操作)

– 数据库约束和数据库表一样,也是一个数据库对象
– 数据库表和数据库约束都一个数据库对象,分别保存在系统数据库中(INFORMATION_SCHEMA)的

– 约束的分类(按照约束的范围分)
– 1.列级约束:如果一个约束只能作用在单列字段上面,这就是一个列级约束; 也就是一个约束只检测一个字段的数据
– 2.表级约束:如果一个约束可以同时作用在多列字段上面,就是一个表级约束;也就是一个约束可以同时检测多个字段的数据

– 创建约束的时间
– 1.可以在创建表的时候一起创建约束
– 2.可以在创建好表之后,使用ALTER(修改表结构的数据,数据DDL语句)语句给表中添加约束

– 约束的具体分类:
– 1.非空约束(NOT NULL)
– 2.唯一约束(UNIQUE)
– 3.主键约束(PRIMARY KEY )
– 4.外键约束(FOREIGN KEY)

– 注意:在一个表中,可以同时有多个非空约束,多个唯一约束,多个非空且唯一约束,多个外键约束;但是只能有一个主键约束

– =============================================================================================================
– 1.非空约束.这是唯一的一个列级约束; 保证进入字段的数据不能为NULL

– 创建表,并且给字段添加非空约束
DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(3),
SNAME CHAR(4) NOT NULL , – 给SNAME字段添加非空约束
AGE INT(2)
);

– 插入测试数据
INSERT INTO STUS VALUES (1,‘张1’,21); – 成功
INSERT INTO STUS VALUES (2,‘张2’,NULL); – 成功
INSERT INTO STUS VALUES (3,‘NULL’,NULL); – 成功 ,'NULL’是一个非空的字符
INSERT INTO STUS VALUES (4,NULL,NULL); – 失败,SNAME不能为NULL

– 查询数据
SELECT * FROM STUS;

– 在一个表中可以同时有多个非空约束

DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(3),
SNAME CHAR(4) NOT NULL , – 给SNAME字段添加非空约束
AGE INT(2),
TEL CHAR(11) NOT NULL – 给TEL字段添加非空约束
);

– =============================================================================================================
– 2.唯一约束(UNIQUE)
– 一个唯一约束可以作用在单列字段上面,也可以同时作用在多列字段上面,所以:唯一约束是一个表级约束
– 唯一约束可以保证字段的数据不能重复,或字段组合的数不能重复;但是唯一约束可以为NULL

– 1.唯一约束作用在单列字段上面

– 创建唯一约束的时候可以使用简化方式
DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4),
SNAME CHAR(4),
TEL BIGINT(11) UNIQUE – 使用简化方式给TEL字段添加唯一约束
);

– 插入测试数据
INSERT INTO STUS VALUES (1,‘张1’,18611001001); – OK
INSERT INTO STUS VALUES (2,‘张1’,18611001002); – OK
INSERT INTO STUS VALUES (3,‘张3’,18611001001); – 失败,TEL字段数据重复
INSERT INTO STUS VALUES (4,‘张4’,NULL); – OK
INSERT INTO STUS VALUES (5,‘张5’,NULL); – OK ;NULL表示没有数据;NULL和NULL是不重复的

– 查询数据
SELECT * FROM STUS;

– 创建唯一约束的时候可以使用标准方式,也就是给约束命名

DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4),
SNAME CHAR(4),
TEL BIGINT(11) ,
– 创建约束的标准方式: 约束的关键字 + 约束的名称 + 约束的类型 + 作用字段
CONSTRAINT TEL_UNI UNIQUE (TEL)
);

– 数据库约束和数据库表一样,也是一个数据库对象
– 数据库表和数据库约束都一个数据库对象,分别保存在系统数据库中(INFORMATION_SCHEMA)的
– 切换到 INFORMATION_SCHEMA 数据库
USE INFORMATION_SCHEMA;

– 我们的表保存在INFORMATION_SCHEMA 数据库的 TABLES 表中
SELECT * FROM TABLES WHERE TABLE_SCHEMA = “bjpowernode”;

– 我们的约束保存在INFORMATION_SCHEMA 数据库的 TABLE_CONSTRAINTS 表中
SELECT * FROM TABLE_CONSTRAINTS WHERE TABLE_NAME = “stus”;
SELECT * FROM TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = “bjpowernode”;

– 重新切换到bjpowernode数据库
USE bjpowernode;

– 2.一个约束,可以同时作用在多列字段上面,这种方式只能使用标准方式创建
DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4),
SNAME CHAR(4),
TEL BIGINT(11) ,
– 创建约束的标准方式: 约束的关键字 + 约束的名称 + 约束的类型 + 作用字段
– NAME_TEL_UNI 这个唯一约束同时作用在SNAME和TEL字段上面,也就是两个字段的组合数据不能重复
CONSTRAINT NAME_TEL_UNI UNIQUE (SNAME,TEL)
);

– 插入测试数据
INSERT INTO STUS VALUES (1,‘张1’,18611001001) ; – OK
INSERT INTO STUS VALUES (1,‘张2’,18611001001) ; – OK
INSERT INTO STUS VALUES (1,‘张2’,18611001001) ; – 失败,name+tel的组合数据是重复的
INSERT INTO STUS VALUES (1,NULL ,18611001001) ; – OK
INSERT INTO STUS VALUES (1,NULL ,18611001001) ; – OK

– 查询数据
SELECT * FROM STUS;

– 3.在一个表中可以同时有多个唯一约束

DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4),
SNAME CHAR(4),
TEL BIGINT(11) ,
– 创建约束的标准方式: 约束的关键字 + 约束的名称 + 约束的类型 + 作用字段
CONSTRAINT TEL_UNI UNIQUE (TEL) ,
CONSTRAINT NAME_UNI UNIQUE (SNAME)
);

– =============================================================================================================
– 3.非空且唯一约束,保证字段的数据非空且唯一
– 一个约束可以同时作用在多个字段上面,在一个字段上面也可以有多个约束

DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4),
SNAME CHAR(4),
TEL BIGINT(11) NOT NULL , – 给TEL字段添加非空约束
– 创建约束的标准方式: 约束的关键字 + 约束的名称 + 约束的类型 + 作用字段
– 给TEL字段添加唯一约束,在TEL字段上面同时有两个约束,也就是非空且唯一
CONSTRAINT TEL_UNI UNIQUE (TEL)
);

– 插入测试数据
INSERT INTO STUS VALUES (1,‘张1’,18611001001) ; – OK
INSERT INTO STUS VALUES (1,‘张2’,18611001001) ; – 失败,TEL重复
INSERT INTO STUS VALUES (1,‘张2’,NULL) ; – 失败,TEL不能为空

– 查询数据
SELECT * FROM STUS;

– =============================================================================================================
– 4.主键约束(PRIMARY KEY ),从功能上来说:相当于非空且唯一
– 主键约束可以作用在单列字段上面,也可以同时作用在多列字段上面(称为联合主键),所以主键约束是一个表级约束

– 主键约束和手动的非空且唯一的区别
– (1)在一个表中,只能有唯一的一个主键约束;但是在一个表中可以有多个手动的非空且唯一
– (2)数据库会自动的为主键约束创建索引;但是数据库不会为手动的非空且唯一创建索引

– 主键约束的相关名称
– 主键约束:也就是这个约束;
– 主键字段:主键约束所在的字段;例如:EMPNO就是主键字段
– 主键值:也就是主键字段的具体的数据;例如:7369,7499等这些就是主键值

– 主键约束的作用:通过主键中可以确定唯一的一条记录;例如:通过7369这个主键值,可以确定唯一的一条记录

– 一个表中什么的字段应该作为主键字段呢?
– 我们不应用和数据有紧密联系的业务数据来当做主键,因为业务数据是有可能变化的
– 我们应该用和数据没有联系的非业务数据来当做主键,例如一个流水号(也就是一个整形的数值)或一个定长的字符串

– 1.主键约束作用在单列字段上面

– (1)使用简化方式创建主键约束
DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4) PRIMARY KEY, – 用简化方式给SID字段添加主键约束
SNAME CHAR(4),
AGE INT(2)
);

– 插入测试数据
INSERT INTO STUS VALUES (1,‘张1’,21); – 成功
INSERT INTO STUS VALUES (2,‘张2’,22); – 成功
INSERT INTO STUS VALUES (1,‘张3’,23); – 失败,主键值重复
INSERT INTO STUS VALUES (NULL,‘张4’,24); – 失败,主键值不能为NULL

– 查询数据
SELECT * FROM STUS;

– (2)使用标准方式创建主键约束,也就是给主键约束命名
DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4) ,
SNAME CHAR(4),
AGE INT(2),
– 创建约束的标准方式: 约束的关键字 +约束的名称 +约束的类型 + 作用字段
CONSTRAINT PK_STU PRIMARY KEY (SID)
);

– 2.一个主键约束作用在多列字段上面,称为联合主键;保证字段的组合数据不能重复,且不能为NULL
– 创建联合主键,必须要是用标准方式
DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4) ,
SNAME CHAR(4),
AGE INT(2),
– 创建约束的标准方式: 约束的关键字 +约束的名称 +约束的类型 + 作用字段
– 主键约束同时作用在SID和SNAME字段上面,也就是联合主键
CONSTRAINT PK_STU PRIMARY KEY (SID,SNAME)
);

– 插入测试数据
INSERT INTO STUS VALUES (1,‘张1’,21); – 成功
INSERT INTO STUS VALUES (1,‘张2’,22); – 成功
INSERT INTO STUS VALUES (1,‘张1’,23); – 失败,字段的组合重复
INSERT INTO STUS VALUES (NULL,‘张1’,24); – 失败. 主键值不能为NULL
INSERT INTO STUS VALUES (2,NULL,24); – 失败.主键值不能为NULL

– 查询数据
SELECT * FROM STUS;

– 3.在MySql/SqlServer中,为了维护主键值,有一种特殊的整形数据,也就是自增字段(auto_increment)
– 在MySql/SqlServer中,可以使用自增字段来维护主键值

DROP TABLE IF EXISTS STUS;

CREATE TABLE STUS (
SID INT(4) AUTO_INCREMENT , – 只能给整形的字段指定为自增字段,默认的从1开始,按1递增
SNAME CHAR(4),
AGE INT(2),
– 创建约束的标准方式: 约束的关键字 +约束的名称 +约束的类型 + 作用字段
CONSTRAINT PK_STU PRIMARY KEY (SID)
);

– 由于SID字段使用了自增字段,所有在插入数据的的时候就不需要我们手动的插入SID的值了,数据库会自动维护这个值
INSERT INTO STUS (SNAME,AGE) VALUES (‘张1’,21);
INSERT INTO STUS (SNAME,AGE) VALUES (‘张2’,22);
INSERT INTO STUS (SNAME,AGE) VALUES (‘张3’,23);
INSERT INTO STUS (SNAME,AGE) VALUES (‘张4’,24);

– 查询数据
SELECT * FROM STUS;

– 现在自增字段使用到了4,下一个值应该是5;我们手动的插入5
INSERT INTO STUS (SID,SNAME,AGE) VALUES (5,‘张5’,25);

– 插入数据的时候,数据库会自动的判断自增字段的值,如果要插入的值已经被占用,则自增字段自动+1
INSERT INTO STUS (SNAME,AGE) VALUES (‘张6’,26);

INSERT INTO STUS (SID,SNAME,AGE) VALUES (25,‘张25’,25);
INSERT INTO STUS (SNAME,AGE) VALUES (‘张26’,26);

– 现在自增字段使用到了26,下一个值应该是27,我们手动的删除26,自增字段的值并不会回退,已经是27
DELETE FROM STUS WHERE SID =26;
INSERT INTO STUS (SNAME,AGE) VALUES (‘张27’,26);

– =============================================================================================================
– 5.外键约束(FOREIGN KEY)
– 数据库中的表是从现实中的实体和实体之间抽象而来的,实体和实体之间是有关系的
– 在数据库表中,通过外键来实现实体和实体之间的关系,所以这种数据库被称为关系型数据库(Oracle,MySql,SqlServer,DB2)

– 外键约束的作用:
– (1)子表的外键值必须要在父表的主键值的范围之内
– (2)子表的外键值可以重复,可以为NULL

– 父表中的主键字段或唯一键字段,才能被子表引用为外键;
– 在一个表中,可以同时有多个外键约束

– 在数据库中使用外键约束,可以保证数据的安全,但是会降低数据库的效率.所以对于外键的使用有两种使用方式
– 1.在数据库中设置外键,通过外键来保证数据的安全;
– 2.在数据库中不设置外键,而是在Java程序中通过逻辑控制,来保证数据的安全

– 例如:在DEPT表和EMP表之间,应该设置外键约束的;但是并没有设置外键
UPDATE DEPT SET DEPTNO = 100 WHERE DEPTNO = 10;

– (1)在外键约束下,创建表的时候:需要先创建父表,后创建子表

CREATE TABLE DEPT1(
DEPTNO INT(4) PRIMARY KEY ,
DNAME VARCHAR(10),
LOC VARCHAR (20)
);

CREATE TABLE EMP1 (
EMPNO INT(4) PRIMARY KEY ,
ENAME VARCHAR(10),
JOB VARCHAR(10),
MGR INT(4),
DEPTID INT(4),
– 创建外键约束的时候,需要引用父表中的主键字段或唯一键字段(只有先创建出父表,子表才能引用父表)
– 子表的外键字段名称和父表的主键字段名称可以不同
CONSTRAINT FK_EMP FOREIGN KEY (DEPTID) REFERENCES DEPT1 (DEPTNO)
);

– (2)在外键约束下,在插入数据库的时候,需要先插入父表的数据,后插入子表的数据
– 外键约束的作用:子表的外键值必须要在父表的主键值的范围之内;子表的外键值可以重复,可以为NULL

– 先插入父表数据
INSERT INTO DEPT1 VALUES (10,“开发部”,“北京”);
INSERT INTO DEPT1 VALUES (20,“销售部”,“北京”);
INSERT INTO DEPT1 VALUES (30,“生产部”,“廊坊”);

– 后插入子表数据
INSERT INTO EMP1 VALUES (1001,‘张1’,‘软件工程师’,1004,10); – 外键值可以重复
INSERT INTO EMP1 VALUES (1002,‘张2’,‘软件工程师’,1004,10);
INSERT INTO EMP1 VALUES (1003,‘张3’,‘软件工程师’,1004,10);
INSERT INTO EMP1 VALUES (1004,‘张4’,‘软件经理’,NULL,10);
INSERT INTO EMP1 VALUES (2001,‘王1’,‘销售员’,2004,20);
INSERT INTO EMP1 VALUES (2002,‘王1’,‘销售员’,2004,20);
INSERT INTO EMP1 VALUES (2003,‘王1’,‘销售员’,2004,20);
INSERT INTO EMP1 VALUES (2004,‘王1’,‘销售经理’,NULL,20);
INSERT INTO EMP1 VALUES (4001,‘孙1’,‘储备经理’,NULL,NULL); – 外键值可以为NULL

– 下面的语句插入失败
– 错误:子表的外键值必须要在父表的主键值的范围之内
– 因为在父表中就没有40这个部门,也就不能有40部门的员工
INSERT INTO EMP1 VALUES (5001,‘赵1’,‘维修’,NULL,40);

– 查询数据
SELECT * FROM EMP1;
SELECT * FROM DEPT1;

– (3)在外键约束下,修改数据的时候
– 不能直接修改父表的主键值(但是可以修改父表的非主键值)
UPDATE DEPT1 SET DEPTNO = 100 WHERE DEPTNO = 10;

– 正确的修改方式:页就是先切断父子关系,然后才能更新父表数据
– (a)先把子表的相关数据的外键值设置为null
UPDATE EMP1 SET DEPTID = NULL WHERE DEPTID = 10;
– (b)把父表的10部门更新为100部门
UPDATE DEPT1 SET DEPTNO = 100 WHERE DEPTNO = 10;
– ©把子表的相关数据的外键值设置为100
UPDATE EMP1 SET DEPTID =100 WHERE (EMPNO > 1000 AND EMPNO <2000);

– (4)在外键约束下,删除数据的时候,先删除子表数据,后删除父表数据
– 不能直接删除父表数据
DELETE FROM DEPT1 WHERE DEPTNO = 100;

– 正确的方式是:先删除子表的相关数据(或把子表的相关数据的外键值设置为NULL),后删除父表数据
DELETE FROM EMP1 WHERE DEPTID = 100;
DELETE FROM DEPT1 WHERE DEPTNO = 100;

– (5)在外键约束下,删除表的时候,要先删除子表,后删除父表
DROP TABLE IF EXISTS EMP1;
DROP TABLE IF EXISTS DEPT1;

– =============================================================================================================
– 6.级联删除( ON DELETE CASCADE ):也就是在删除父表数据的时候回连同子表的相关数据一起删除
– 级联删除必须要建立在外键约束之上,也就是先建立外键约束,然后才能指定级联删除
– 切记:级联操作在使用的是必须要慎重,我们就是通过外键约束保证数据的安全;级联操作通常只会使用在系统测试阶段

DROP TABLE IF EXISTS EMP1;
DROP TABLE IF EXISTS DEPT1;

CREATE TABLE DEPT1(
DEPTNO INT(4) PRIMARY KEY ,
DNAME VARCHAR(10),
LOC VARCHAR (20)
);

CREATE TABLE EMP1 (
EMPNO INT(4) PRIMARY KEY ,
ENAME VARCHAR(10),
JOB VARCHAR(10),
MGR INT(4),
DEPTID INT(4),
– 级联删除必须要建立在外键约束之上,也就是先建立外键约束,然后才能指定级联删除
CONSTRAINT FK_EMP FOREIGN KEY (DEPTID) REFERENCES DEPT1 (DEPTNO) ON DELETE CASCADE
);

– 查询数据
SELECT * FROM EMP1;
SELECT * FROM DEPT1;

– 由于我们在外键约束之上设置了级联删除,这样的话我们可以直接删除父表的数据,会连同子表的相关数据一起删除掉
DELETE FROM DEPT1 WHERE DEPTNO= 10;

– =============================================================================================================
– 7.级联更新(ON UPDATE CASCADE):也就是在更新父表数据的时候回连同子表的相关数据一起更新
– 级联更新必须要建立在外键约束之上,也就是先建立外键约束,然后才能指定级联更新

DROP TABLE IF EXISTS EMP1;
DROP TABLE IF EXISTS DEPT1;

CREATE TABLE DEPT1(
DEPTNO INT(4) PRIMARY KEY ,
DNAME VARCHAR(10),
LOC VARCHAR (20)
);

CREATE TABLE EMP1 (
EMPNO INT(4) PRIMARY KEY ,
ENAME VARCHAR(10),
JOB VARCHAR(10),
MGR INT(4),
DEPTID INT(4),
– 级联更新必须要建立在外键约束之上,也就是先建立外键约束,然后才能指定级联更新
CONSTRAINT FK_EMP FOREIGN KEY (DEPTID) REFERENCES DEPT1 (DEPTNO) ON UPDATE CASCADE
);

– 查询数据
SELECT * FROM EMP1;
SELECT * FROM DEPT1;

– 由于我们在外键约束之上指定了级联更新,我们可以直接更新父表的数据,会连同子表的相关数据一起更新
UPDATE DEPT1 SET DEPTNO = 100 WHERE DEPTNO = 10;

– =============================================================================================================
– 8.删除约束
– 数据库约束也是一个独立的数据库对象,和数据库表是分别保存的
– 我们删除表的时候,会连同约束一起删除掉
– 但是我们删除约束的时候,并不会影响表的结构和表中的数据

– 1.删除主键约束
– 在一个表中只能有唯一的主键约束,所以我们删除主键约束的时候不需要指明主键约束的名称
DROP TABLE IF EXISTS DEPT1;

CREATE TABLE DEPT1(
DEPTNO INT(4) PRIMARY KEY ,
DNAME VARCHAR(10),
LOC VARCHAR (20)
);

– 先插数据
INSERT INTO DEPT1 VALUES (10,“开发部”,“北京”);
INSERT INTO DEPT1 VALUES (20,“销售部”,“北京”);
INSERT INTO DEPT1 VALUES (30,“生产部”,“廊坊”);

– 查询数据
SELECT * FROM DEPT1;

– 由于主键约束的存在,下面的语句插入失败
INSERT INTO DEPT1 VALUES (30,“维修”,“廊坊”);

– 删除主键约束
ALTER TABLE DEPT1 DROP PRIMARY KEY ;

– 由于我们删除了外键约束,所以下面的语句可以执行成功
INSERT INTO DEPT1 VALUES (30,“维修”,“廊坊”);

– 2.删除外键约束,在一个表中可以同时有多个外键约束,所以删除外键约束的时候必须要指明外键约束的名称

DROP TABLE IF EXISTS EMP1;
DROP TABLE IF EXISTS DEPT1;

CREATE TABLE DEPT1(
DEPTNO INT(4) PRIMARY KEY ,
DNAME VARCHAR(10),
LOC VARCHAR (20)
);

CREATE TABLE EMP1 (
EMPNO INT(4) PRIMARY KEY ,
ENAME VARCHAR(10),
JOB VARCHAR(10),
MGR INT(4),
DEPTID INT(4),
CONSTRAINT FK_EMP FOREIGN KEY (DEPTID) REFERENCES DEPT1 (DEPTNO)
);

– 查询数据
SELECT * FROM EMP1;
SELECT * FROM DEPT1;

– 由于外键约束的存在,下面的语句执行失败
DELETE FROM DEPT1 WHERE DEPTNO = 10;

– 删除外键约束,必须要指明外键约束的名称
ALTER TABLE EMP1 DROP FOREIGN KEY FK_EMP;

– 由于删除了外键约束,下面的语句可以执行成功
DELETE FROM DEPT1 WHERE DEPTNO = 10;

– 3.删除唯一约束.在一个表中可以有多个唯一约束,所以必须要指明唯一约束的名称(也就是要使用标准方式创建唯一约束)
– 注意要使用DROP INDEX 方式来删除唯一约束
DROP TABLE IF EXISTS DEPT1;

CREATE TABLE DEPT1(
DEPTNO INT(4) PRIMARY KEY ,
DNAME VARCHAR(10),
LOC VARCHAR (20),
CONSTRAINT UNI_DNAME UNIQUE (DNAME)
);

– 先插数据
INSERT INTO DEPT1 VALUES (10,“开发部”,“北京”);
INSERT INTO DEPT1 VALUES (20,“销售部”,“北京”);
INSERT INTO DEPT1 VALUES (30,“生产部”,“廊坊”);

– 查询数据
SELECT * FROM DEPT1;

– 由于唯一约束的存在,下面的语句执行失败
INSERT INTO DEPT1 VALUES (40,“生产部”,“廊坊”);

– 删除唯一约束,注意要使用DROP INDEX 方式来删除唯一约束
ALTER TABLE DEPT1 DROP INDEX UNI_DNAME;

– 由于删除了唯一约束,下面的语句可以执行成功
INSERT INTO DEPT1 VALUES (40,“生产部”,“廊坊”);

– 4.删除非空约束,不能使用DROP方式,而是要使用MODIFY(修改)方式

DROP TABLE IF EXISTS DEPT1;

CREATE TABLE DEPT1(
DEPTNO INT(4) PRIMARY KEY ,
DNAME VARCHAR(10) NOT NULL,
LOC VARCHAR (20)
);

– 先插数据
INSERT INTO DEPT1 VALUES (10,“开发部”,“北京”);
INSERT INTO DEPT1 VALUES (20,“销售部”,“北京”);
INSERT INTO DEPT1 VALUES (30,“生产部”,“廊坊”);

– 查询数据
SELECT * FROM DEPT1;

– 由于非空约束的存在,下面的语句执行失败
INSERT INTO DEPT1 VALUES (40,NULL,“廊坊”);

– 删除非空约束,不能使用DROP方式,而是要使用MODIFY(修改)方式
– 把DNAME字段修改为可以为NULL,也就是删除了非空约束
ALTER TABLE DEPT1 MODIFY DNAME VARCHAR(10) NULL;

– 由于删除了非空约束,下面的语句可以执行成功
INSERT INTO DEPT1 VALUES (40,NULL,“廊坊”);

/豪华的分割线*********/
– 事务

– 什么是事务?把多个相关的DML操作捆绑成一个整体,要么全都成功,要么全都失败!
– 在数据库中,只有DML语句才会引起事务;DDL/DQL是不会引起事务的
– 没有事务保护的DML语句是不安全的

– MySql管理事务的默认方式是:自动提交
– MySql认为每个DML语句都会引起一个单独的事务;每当执行一个DML的时候,都会引起一个事务
– 这个DML语句执行结束的时候,MySql会自动的使用提交方式结束事务
– 事务用提交方式结束之后,事务中对数据库的修改被永久的保存到数据库中,所有与的会话都可以查询到这些修改
– 什么是会话?客户端和服务器端之间一个完整的连续不断的交互过程

SELECT * FROM DEPT;

– MySql自动提交演示
INSERT INTO DEPT VALUES (50,"","");

– =============================================================================================================
– MySql默认的自动提交方式是不好的,我们需要关闭MySql默认的自动提交,有两种方式
– 方式1,执行语句: START TRANSACTION;
– 方式2,执行语句: SET AUTOCOMMIT = OFF;

– 1.当我们关闭MySql的自动提交之后,我们执行的第一个DML语句会引起一个事务
– 2.在事务开始之后,只要事务没有结束,无论我们执行多少个DML语句,这些DML语句都是隶属于同一个事务的
– 在事务中对数据库的修改被保存在内存中,只有当前事务可以查询到这些修改,其他的事务是无法查询这些修改的
– 3.结束事务的方式有两种
– (1)提交结束事务:事务中对数据库的修改被永久的保存到数据库中,所有的事务都可以查询到这些修改
– (2)回滚结束事务:事务中对数据库的修改被统统的放弃,数据库恢复到事务开始之前的状态

SELECT * FROM DEPT;
DELETE FROM DEPT WHERE DEPTNO > 40;

– 关闭自动提交之后,用提交的方式来结束事务:

– 关闭自动提交
START TRANSACTION;

– 执行第一个DML语句引起一个事务
INSERT INTO DEPT VALUES (50,"","");

– 事务开始之后,无论我们执行多少个DML语句,都是隶属于同一个事务的
INSERT INTO DEPT VALUES (60,"","");
INSERT INTO DEPT VALUES (70,"","");
INSERT INTO DEPT VALUES (80,"","");
UPDATE DEPT SET DNAME = “开发部”,LOC = “北京” WHERE DEPTNO >40;

– 在事务中对数据库的修改被保存在内存中,只有当前事务可以查询到这些修改
SELECT * FROM DEPT;

– 用提交的方式结束事务,事务中对数据库的修改被永久的保存到数据库中,所有的事务都可以查询到这些修改
COMMIT;

– 关闭自动提交之后,用回滚的方式来结束事务:

– 关闭自动提交
START TRANSACTION;

– 执行第一个DML语句引起一个事务
INSERT INTO DEPT VALUES (50,"","");

– 在事务中对数据库的修改被保存在内存中,只有当前事务可以查询到这些修改
SELECT * FROM DEPT;

– 用回滚方式结束事务,事务中对数据库的修改被统统的放弃,数据库恢复到事务开始之前的状态
ROLLBACK;

– 关闭自动提交的第二种方式: 执行语句: SET AUTOCOMMIT = OFF; 这种方式默认的是关闭会话级别的自动提交
– 在新的会话中,MySql又会恢复到默认的自动提交方式; SET AUTOCOMMIT = OFF; 等效语句 SET SESSION AUTOCOMMIT = OFF;

– 关闭会话级别的自动提交
SET AUTOCOMMIT = OFF;

– 关闭自动提交之后,我们可以查询数据库的提交方式
SHOW VARIABLES LIKE ‘%COMMIT%’;

– 恢复MySql默认的自动提交方式
SET AUTOCOMMIT = ON;

DELETE FROM DEPT WHERE DEPTNO > 40;
SELECT * FROM DEPT;

– =============================================================================================================
– 事务的隔离性有四个级别,从低到高;在数据库中通过事务的隔离性来解决数据库的读并发问题,也就是多个事务同时修改相同的数据
– 事务的隔离性有四个级别,从低到高

– 1.读未提交:一个事务,读取到另一个事务尚未提交的数据; 读取到的数据称为脏数据,这种形式称为脏读.脏读的后果很严重
– 这种隔离级别只在理论上存在,在实际中没有数据库会设置这种隔离级别

– 2.读已提交:一个事务读取另一个事务已经提交的数据;这种方式可以避免脏读;
– 读已提交可以避免脏读,但是会导致另一个问题,也就是"不可重复读"
– 什么是不可重复读:在一个事务中,每次读取数据库的条数都不一致
– Oracle默认的隔离级别就是:读已提交

– 3.可重复读:一个事务提交之后的数据,另一个事务仍然读取不到
– 可重复读方式避免了不可重复读,但是会导致另一个问题,也就是幻读
– 什么是幻读:读取到的数据的条数和数据库中实际的条数不一致了
– MySql默认的隔离级别就是:可重复读

– 4.串行化:一个事务在操作一个表的时候,会把这个表锁住(表级锁),其他的事务是无法查询这个表的.
– 只有第一个事务结束之后,其他的事务才能操作这个表
– 串行化的时候读取的数据用于都是数据中真实的状态,避免了幻读
– 这种方式导致数据库的处理能力大大降低,所以实际开发中也没有人采用这种方式

– 和隔离级别相关的SQL语句

– 事务事务的隔离级别有两种范围
– 1.设置会话级别的隔离级别,只对当前会话有效
– 2.设置全局级别的隔离级别,对所有的会话都是有效的 .这是我们要使用的方式
– 注意:设置全局级别的隔离级别之后,必须要重启客户端,也就是开启新的会话.新设置的隔离级别在新的会话中生效

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; – 设置全局级隔离级别为:读未提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; – 设置全局级隔离级别为:读已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; – 设置全局级隔离级别为:可重复读
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; – 设置全局级隔离级别为:串行化

– 查询全局级别额隔离级别的语句
SELECT @@GLOBAL.TX_ISOLATION;

mysql -uroot -proot

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

use bjpowernode;

start transaction;

– =============================================================================================================
– 数据库中原计划是通过隔离级别来解决数据库的读并发问题,但是四种隔离级别都有问题,所以通过隔离级别解决读并发问题是不行的
– 那么在数据库中如何解决数据的读并发问题呢? 可以使用行家锁来解决数据库的读并发问题

– 行级锁的使用
– 1.关闭自动提交
– 2.使用语句SELECT * FROM EMP WHERE EMPNO = 7369 FOR UPDATE;来启动行级锁,锁住7369这条记录
– 注意:启动行级锁的查询语句必须要使用主键作为查询条件,启动的才是行级锁,否则依旧是表级锁
– 3.启动行级的目的是锁住7369这条记录,只有当前的事务可以修改7369这条记录
– 4.结束事务,释放行级锁

– 使用行家锁的过程

– 关闭自动提交
START TRANSACTION;

– 使用语句SELECT * FROM EMP WHERE EMPNO = 7369 FOR UPDATE;引起一个事务,启动行级锁,锁住7369这条记录
SELECT * FROM EMP WHERE EMPNO = 7369 FOR UPDATE;

– 注意:启动行级锁的查询语句必须要使用主键作为查询条件,启动的才是行级锁,否则依旧是表级锁
SELECT * FROM EMP WHERE ENAME = “SMITH” FOR UPDATE;

– 启动行级的目的是锁住7369这条记录,只有当前的事务可以修改7369这条记录
UPDATE EMP SET SAL=2200,COMM=100 WHERE EMPNO =7369;

– 结束事务,释放行级锁
COMMIT;

– 如何实现转账的功能呢? 转账的时候需要所两条记录啊
START TRANSACTION;

– 使用语句SELECT * FROM EMP WHERE EMPNO IN ( 7369,7499) FOR UPDATE;引起一个事务,启动行级锁,锁住7369,7499这两条记录
SELECT * FROM EMP WHERE EMPNO IN (7369,7499) FOR UPDATE;

– 启动行级的目的是锁住7369这条记录,只有当前的事务可以修改7369/7499这条记录
UPDATE EMP SET SAL=2200,COMM=100 WHERE EMPNO =7369;
UPDATE EMP SET SAL=3200,COMM=200 WHERE EMPNO =7499;

– 结束事务,释放行级锁
COMMIT;

/豪华的分割线*********/
– 数据库索引的使用:索引类似于数中的目录,可以加快数据的查询速度!
– 索引的原理:就像书中的目录,这条数据保存在哪个位置,那条数据保存在哪个位置

– 数据库查询数据的方式有两种
– 1.全盘扫描,效率低
SELECT * FROM EMP WHERE ENAME = “FORD”;
– 2.通过索引查询数据,效率高

– 创建索引的方式有两种
– 1.数据库自动的创建索引,数据库会自动的为主键字段创建索引
– 2.我们手动的创建索引

– 表中什么样的字段应该创建索引呢?
– 如果一个字段经常出现在查询条件中(也就是WHERE子句),我们就应该给这样的字段创建索引
– 例如:我们给EMP 表的ENAME字段创建索引
CREATE INDEX ENAME_INDEX ON EMP (ENAME);

– 数据库索引和数据库表一样,都是一个独立的数据库对象,我们可以查询我们创建的索引
SHOW INDEX FROM EMP;

– 创建索引之后,我们使用ENAME进行查询的时候,就会加快数据库的查询速度
SELECT * FROM EMP WHERE ENAME = “FORD”;

– 删除索引的时候,并不会影响表中的数据和表中的字段
DROP INDEX ENAME_INDEX ON EMP;

/豪华的分割线*********/
– 视图的使用(在数据库中经常的要使用到)
– 视图实际上就是一个命名的查询,也就是把一个查询语句的查询结果做出一个命名的数据库对象,保存到数据库中
– 视图都是和一个有效的查询语句绑定在一起的

/*
创建视图的语句
CREATE VIEW 视图的名称
AS
有效的查询语句
*/

– =============================================================================================================
– 创建一个视图

CREATE VIEW V_EMP_INFO
AS
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,D.DEPTNO,D.DNAME,D.LOC,S.GRADE,S.LOSAL,S.HISAL
FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);

– 视图可以被看做是一个表,我们可以查询视图的结构
DESC EMP;
DESC V_EMP_INFO;

– 视图可以被看做是一个表,我们可以从视图中查询数据,就像从表中查询数据一样方便
SELECT *
FROM EMP
WHERE EMPNO =7369;

SELECT *
FROM V_EMP_INFO
WHERE EMPNO =7369;

– 删除视图的语句和删除表的语句非常类似
DROP TABLE IF EXISTS EMP;
DROP VIEW IF EXISTS V_EMP_INFO;

– =============================================================================================================
– 再创建一个视图
CREATE VIEW V_EMP
AS
SELECT * FROM EMP WHERE SAL > 2000;

– 从视图中查询数据
– 我们从视图中查询数据,实际上是先执行视图绑定的查询语句,把查询结果做出一个临时表,然后从这个临时表中查询数据
– 这种方式也就是出现在FROM后面的子查询
SELECT *
FROM V_EMP
WHERE EMPNO =7566;

– 我们甚至可以对视图进行DML操作
– 视图实际上是一个虚表,在视图中是不能保存数据的,我们对视图的DML操作,实际上是对源表的操作
UPDATE V_EMP SET SAL=3000 ,COMM = 300 WHERE EMPNO =7566;
DELETE FROM V_EMP WHERE EMPNO=7566;
INSERT INTO V_EMP VALUES (9000,‘张1’,‘软件工程师’,7902,‘1985-2-16’,1800,200,30);

– 视图的作用:
– 1.可以隐藏数据的来源
– 2.可以提供开发的速度,但是不能提高查询的速度

SELECT *
FROM V_EMP_INFO
WHERE EMPNO =7369;

SELECT T.*
FROM ( SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,D.DEPTNO,D.DNAME,D.LOC,S.GRADE,S.LOSAL,S.HISAL
FROM EMP E
INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)) T
WHERE T.EMPNO = 7369;

– =============================================================================================================
– 注意:创建视图的SELECT语句中,不能有出现在FROM后面的子查询
CREATE VIEW V_EMP
AS
SELECT E.EMPNO,E.ENAME,E.DEPTNO,E.DNAME,E.LOC
FROM(
SELECT E.*, D.DNAME,D.LOC
FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) ) E

/豪华的分割线*********/
– 存储过程
– 存储过程是一个数据库对象,保存在数据库中,在存储过程中可以使用我们以前讲的SQL内容,并且可以使用编程语言的特性(循环判断分支)
– 使用存储过程可以提高数据库的处理能力
– 在存储过程中使用DDL/DML/TCL,和我们以前讲的一样使用
– 在存储过程中使用DQL,和我们以前讲的略有区别,要使用SELECT … INTO … 格式
– 存储过程的缺点:在不同的数据中,存储过程的语法都是不同的;只要我们使用了存储过程,就相当于放弃了数据库的移植性

/*
创建存储过程的语法
CREATE PROCEDURE 存储过程的名称( [参数列表])
BEGIN
SQL语句
END;

参数列表的规范: 参数的输入/输出类型参数名称参数的数据类型
存储过程可以有多个参数,多个参数通过逗号分隔
参数的输入/输出类型有三种类型
1.IN 类型,可以把外界的数据传递给存储过程 ;如果没有指明参数的输入/输出类型,默认的就是IN类型
2.OUT 类型,可以把存储过程中的计算结果返回给调用者
3.INOUT类型,既可以向过程中传递数据,又可以把过程中的计算结果返回给调用者
注意:参数的数据类型就是SQL中讲解的数据类型,但是只需要声明参数的数据类型,不需要声明参数的数据类型长度
*/

– =============================================================================================================
– 声明一个存储过程,查询EMP表中的数据总数并且返回结果
CREATE PROCEDURE PROC1(OUT NUM INT)
BEGIN
– 把查询结果赋值给OUT类型的参数NUM,通过out类型的参数将结果返回给调用者
SELECT COUNT(*) INTO NUM FROM EMP;
END;

– 声明好存储过程之后,我们必须要显示的调用存储过程,这个存储过程才会被执行
– 调用PROC1,并且用@num传递OUT类型的参数
CALL PROC1(@num);

– 存储过程调用结束之后,out类型的参数带回存储过程中的结束结果,查询out类型的参数
SELECT @num;

– 删除存储过程
DROP PROCEDURE PROC1;

– =============================================================================================================
– 创建以存储过程,返回两个参数相加的结果
CREATE PROCEDURE PROC2 (NUM1 INT ,NUM2 INT , OUT RES INT)
BEGIN
– 把NUM1 + NUM2的结果赋值给OUT类型的参数RES;通过out类型的参数将结果返回给调用者
SET RES = NUM1 + NUM2;
END;

– 调用存储过程
CALL PROC2(10,20,@res);
SELECT @RES;

/豪华的分割线*********/
/*
关于PowerDesigner工具的使用：

在实际开发当中专门完成数据库表的设计，应用广泛
Sybase公司的，Sybase公司被SAP公司收购
安装的时候只有一个注意事项：选择HongKong，其它一律是下一步，然后完成，
最后破解采用xxx.dll文件拷贝粘贴的方式,把pdflm16.dll直接拷贝到pd的安装目录中C:\Program Files (x86)\Sybase\PowerDesigner 16中
覆盖原来的文件即可
使用步骤：
- 第一步：Create Model
- 第二步：Model Types – Physical Data Model – Physical Diagram – Model Name: user_login – DBMS : MySQL5.0 – OK
- 第三步：按住ctrl，滚动鼠标滑轮，网格变大或者变小，每一个网格就是项目中的一个模块
- 第四步：在Physical Diagram窗口当中找到table，点中table，在网格的任意位置单击创建一张表
- 第五步：双击网格当中的table，开始进行table的设计
- 第六步：设计数据库表的名字，设计数据库表当中的字段名称，以及字段的数据类型
- 第七步：将生成的SQL语句保存
  – 设计好ER模型(实体关系模型)之后,点击Database菜单->Generate Database 弹出设置窗口,在目录中选择脚本的保存位置
- 第八步：保存最终的xxx.pdm文件
  */

//
/ DBMS name: MySQL 5.0 /
/ Created on: 2018/6/26 10:20:17 /
//

drop table if exists STUDENTS;

drop table if exists STU_TEAC_REL;

drop table if exists TEACHERS;

//
/ Table: STUDENTS /
//
create table STUDENTS
(
SID INT(4) not null,
SNAME CHAR(4),
AGE INT(2),
SEX CHAR(1),
ADDR VARCHAR(30),
primary key (SID)
);

//
/ Table: STU_TEAC_REL /
//
create table STU_TEAC_REL
(
SID INT(4) not null,
TID INT(4) not null,
primary key (SID, TID)
);

//
/ Table: TEACHERS /
//
create table TEACHERS
(
TID INT(4) not null,
TNAME VARCHAR(10),
AGE INT(2),
primary key (TID)
);

alter table STU_TEAC_REL add constraint FK_Reference_1 foreign key (SID)
references STUDENTS (SID) ;

alter table STU_TEAC_REL add constraint FK_Reference_2 foreign key (TID)
references TEACHERS (TID) ;

1.MySQL中有一种机制叫做存储引擎，存储引擎的作用是什么*

    作用是  底层采用不同的方式来存储数据

2.建表的时候在哪里可以指定这张表的存储引擎*
在创建表时，可使用ENGINES选项为CREATE TABLE语句显示指定存储引擎
CREATE TABLE table_name = MyISAM;
3.MyISAM存储引擎特点以及优缺点*

	 –使用三个文件表示每个表：
	格式文件 — 存储表的结构（mytable.frm）
	数据文件 — 存储表的数据（mytable.MYD）
	索引文件 — 存储表的索引（mytable.MYI）
	–灵活的AUTO_INCREMENT字段处理
	–可被转换为压缩、只读表来节省空间

4.MySQL默认的存储引擎是什么*

innodb;

5.MEMORY存储引擎特点以及优缺点*

    使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快；
MEMORY存储引擎管理的表具有下列特征：
–在数据库目录内，每个表均以.frm格式文件表示；
–表数据及索引被存储在内存中；
–表级锁机制；
–不能包含TEXT或BLOB字段；
MEMORY存储引擎以前被称为HEAP引擎；
数据容易更新。

6.InnoDB存储引擎特点以及优缺点*

 InnoDB存储引擎是MySQL数据库的缺省引擎；
–每个InnoDB表在数据库目录中以.frm格式文件表示
–InnoDB表空间tablespace被用于存储表的内容
–提供一组用来记录事务性活动的日志文件
–用COMMIT（提交）、SAVEPOINT及ROLLBACK（回滚）支持事务处理
–提供全ACID兼容
–在MySQL服务器崩溃后提供自动恢复
–多版本（MVCC）和行级锁定
–支持外键及引用的完整性，包括级联更新和删除

7.数据库的事务机制是什么，它的作用是什么*

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说，事务保证批量的DML要么全成功，要么全失败。

8.事务的ACID特性分别是什么（面试题）
a）原子性（Atomicity）
事务是最小单元，不可再分；
b）一致性（Consistency）
事务要求所有的DML语句操作的时候，必须保证同时成功或同时失败；
c）隔离性（Isolation）
一个事务不会影响其它事务的运行；
d）持久性（Durability）
在事务完成之后，该事务对数据库所作的更改将持久地保存在数据库中，并不会被回滚；

9.事务提交commit;事务回滚rollback;无论提交还是回滚，事务都会结束。*

理解

10.事务隔离性当中的4个隔离级别分别是什么，每个隔离级别会有什么不同的特点。*

 read uncommitted;读未提交
       读未提交隔离级别最低，这种级别一般只在理论上存在，数据库默认隔离级别一般都高于该隔离级别；

 read committed;读已提交
   该隔离级别能够导致“不可重复读取”

 repeatable read;可重复的

可重复读到数据的，虽然可以达到“可重复读”效果，但是会导致“幻象读”
 serializable;串行化
  这种事务隔离级别一般很少使用，吞吐量太低，用户体验不好；
   可以避免读取数据并发问题。
 
   以上我们都不用，我们使用表级锁。

11.MySQL中事务是自动提交，演示事务之前需要执行start transaction;来关闭自动提交机制。*

      成功用法：start transaction；commit;
	start transaction;		手动开启事务
	DML语句…..			批量DML语句
	commit;				手动提交事务【事务成功结束】

12.事务的隔离级别是可以通过set transaction isolation level …设置的。*

    并发事务与隔离级别
      read uncommitted                                      
  read committed         
  repeatable read       
  serializable ;
 1.set global transaction   isolation level reade committed;
 2.退出dos窗口
 3. use bjpowernode;
 4. start transaction	
 5. insert into emp values("king");
  1.set global transaction isolation leval read committed
  2,退出DOS窗口;
  3.use bjpowernode;
  4.start transaction ;
  5.delete from emp 
  6.commit;

   1.set global transaction isolation leval   repeatable read   
  2,退出DOS窗口;
  3.use bjpowernode;
  4.start transaction ;
  5.delete from emp 
  6.commit;

   1.set global transaction isolation leval  serializable;
  2,退出DOS窗口;
  3.use bjpowernode;
  4.start transaction ;
  5.delete from emp 
  6.commit;

13.索引的作用是什么*

  相当于一本字典目录，提高程序的检索 / 查询效率；表中每一个字段都可添加索引

14.索引的实现原理*

索引和表相同，都是一个对象，表是存储在硬盘文件中的，那么索引也是表的一部分，索引也存储在硬盘文件中；通过索引检索

15.创建索引，删除索引的SQL语句*//索引都是对表操作的

drop/ create index 索引名 on 表名（列名）

eg.create index dept_dname_index on dept(dname);

    eg  create index emp_ename_index on emp(ename);

16.满足什么条件之后会给字段添加索引*

    1）该字段数据量庞大
2）该字段很少的DML操作（由于索引也需要维护，DML操作多的话，也影响检索效率）
3）该字段经常出现在where条件中

17.主键字段和唯一性字段会自动添加索引，所以查询时尽可能根据主键查询*

理解

18.视图有什么用*

面向视图查询，可以提高查询效率

19.创建视图，删除视图的SQL语句*

create view 视图名称 as 查询语句；

create view myview as select ename,empno,sal from emp;
drop view if exits myview;

20.只能将DQL语句创建为视图*

知道

21.对视图中的数据进行增删改的时候，视图指向的原表数据也会被增删改。*

理解

22.建表时一般会给字段添加约束，约束主要使用用来保证表中数据的完整性，有效性，常见的约束包括非空约束，唯一性约束，主键约束，外键约束*

	 1.非空约束(NOT NULL) 唯一的一个列级约束，保证进入字段的数据不为null。
	 2.唯一约束(UNIQUE)
	 3.主键约束(PRIMARY KEY )在一个表中只能有一个primary key
	 4.外键约束(FOREIGN KEY)

			非空约束

	第一步，创建表，添加非空约束。
	  drop table if EXISTS stus ;
	CREATE table stus(
	  SID  INT(3),
	  SNAME  CHAR(4)  NOT NULL ,
	   AGE INT(2),
	    TEL  VARCHAR(11)NOT NULL
	);

              第二步： 插入测试数据
	  INSERT INTO STUS VALUES(1,"张一"，21,18136432609)；
	INSERT INTO STUS VALUES (1,'张1',21);    -- 成功
	INSERT INTO STUS VALUES (2,'张2',NULL);  -- 成功 
	INSERT INTO STUS VALUES (3,'NULL',NULL); -- 成功 ,'NULL'是一个非空的字符
	INSERT INTO STUS VALUES (4,NULL,NULL);   -- 失败,SNAME不能为NULL

               第三部 ；查询数据
               SELECT * FROM STUS;
					唯一约束unique
                               DROP TABLE IF EXISTS STUS;

			  CREATE TABLE STUS (
			   SID       INT(4),
			   SNAME     CHAR(4),
			   TEL       BIGINT(11) ,
			   -- 创建约束的标准方式: 约束的关键字 + 约束的名称 + 约束的类型 + 作用字段
			     CONSTRAINT TEL_UNI UNIQUE (TEL)
			);

					非空且唯一约束,保证字段的数据非空且唯一
	-- 一个约束可以同时作用在多个字段上面,在一个字段上面也可以有多个约束

	DROP TABLE IF EXISTS STUS;

	CREATE TABLE STUS (
	   SID       INT(4),
	   SNAME     CHAR(4),
	   TEL       BIGINT(11) NOT NULL , -- 给TEL字段添加非空约束
	   -- 创建约束的标准方式: 约束的关键字 + 约束的名称 + 约束的类型 + 作用字段  
	   -- 给TEL字段添加唯一约束,在TEL字段上面同时有两个约束,也就是非空且唯一
	   CONSTRAINT TEL_UNI UNIQUE (TEL)   
	);
	
		-- 插入测试数据
		INSERT INTO STUS VALUES (1,'张1',18611001001) ;  -- OK
		INSERT INTO STUS VALUES (1,'张2',18611001001) ;  -- 失败,TEL重复
		INSERT INTO STUS VALUES (1,'张2',NULL) ;         -- 失败,TEL不能为空

		-- 查询数据
		SELECT * FROM STUS;

我们应该用和数据没有联系的非业务数据来当做主键,例如一个流水号(也就是一个整形的数值)或一个定长的字符串

				DROP TABLE IF EXISTS STUS;

				CREATE TABLE STUS (
				   SID         INT(4) PRIMARY KEY,  -- 用简化方式给SID字段添加主键约束
				   SNAME       CHAR(4),
				   AGE         INT(2)
				);


				-- 插入测试数据
				INSERT INTO STUS VALUES (1,'张1',21); -- 成功
				INSERT INTO STUS VALUES (2,'张2',22); -- 成功
				INSERT INTO STUS VALUES (1,'张3',23); -- 失败,主键值重复
				INSERT INTO STUS VALUES (NULL,'张4',24); -- 失败,主键值不能为NULL

				-- 查询数据
				SELECT * FROM STUS;

	外键约束(FOREIGN KEY)
	数据库中的表是从现实中的实体和实体之间抽象而来的,实体和实体之间是有关系的
        在数据库表中,通过外键来实现实体和实体之间的关系,所以这种数据库被称为关系型数据库

				-- -------------------------------------------------------------------------------------------------------------
		-- (1)在外键约束下,创建表的时候:需要先创建父表,后创建子表

		CREATE TABLE DEPT1(
		   DEPTNO     INT(4) PRIMARY KEY ,
		   DNAME      VARCHAR(10),
		   LOC        VARCHAR (20)
		);

		CREATE TABLE EMP1 (
		   EMPNO      INT(4)  PRIMARY KEY ,
		   ENAME      VARCHAR(10),
		   JOB        VARCHAR(10),
		   MGR        INT(4),
		   DEPTID     INT(4),
		   -- 创建外键约束的时候,需要引用父表中的主键字段或唯一键字段(只有先创建出父表,子表才能引用父表)
		   -- 子表的外键字段名称和父表的主键字段名称可以不同
		   CONSTRAINT FK_EMP FOREIGN KEY (DEPTID) REFERENCES DEPT1 (DEPTNO)
		);






不理解

23.在一张表中，主键存在的意义*

理解

24.在一张表中，外键存在的意义*

保证表的安全。

25.实际的开发中，我们可能很少给字段添加外键约束，因为添加外键约束会让检索效率降低，数据的有效性，一般会有别的方式来保证。*

理解

存储过程是一个数据库对象，保存在

数据库设计三范式

truncate可以删除表结构 delete不行

查询员工的职位是” SALESMAN” 或” MANAGER”的查询语句
SELECT * FROM EMP WHERE JOB IN (“MANAGER”,‘SALESMAN’);

武侠㿝卄清

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql章节(二)（MySQL的事务与存储精讲）

/豪华的分割线*********/– DQL(数据库查询语句),专门从数据库表表查询相关数据的/*查询语句的语法SELECT 字段列表FROM 表名;在cmd窗口中,SQL语句的末尾必须要用分号结束但是在窗口客户端中,SQL语句的末尾可有有分号,也可以没有分号,为了语句的完整,语句末尾加上分号SQL语句不区分大小写*/– 从表中出现单个的字段SELECT EMPNOFRO...
复制链接

扫一扫