mysql 学习记录-技术圈

数据库简介

l 之前已经学习过通过流操作文件存储数据，相当于自己写了一个简单版的数据库软件，存在很多弊端：

a) 效率低

b) 一般只能保存小量数据

c) 只能保存文本类型

什么是DB

l DataBase数据库，代表保存到磁盘中的文件集

什么是DBMS

l DataBaseManagementSystem 数据库管理系统，是用于管理数据库文件的软件系统，常见的DBMS：MySQL、Oracle、DB2、SQLServer、SQLite等

数据库的分类（了解）

2. 关系型数据库：经过数学理论验证可以保存现实生活中存在的任何关系，关系型数据库以表为存储单位。

3. 非关系型数据库：用于处理某些特殊需求，比如：解决数据缓存问题，redis数据库就是一款解决数据缓存的数据库，保存数据的方式是以键值对

主流数据库软件介绍

4. MySQL：Oracle公司产品， 08年被Sun公司收购，09年Sun公司又被Oracle收购，开源数据库，原MySQL在面临MySQL闭源的情况下离开Oracle又创建了一个新的数据库叫MariaDB。市场占有率排名第一

5. Oracle：闭源收费，性能最高收费最贵，拉里。埃里森排名第二

6. SQLserver：微软公司产品，闭源产品，市场排名第三，主要应用在微软的整体解决方案中

7. DB2：IBM公司产品，闭源产品，主要应用在IBM的整体解决方案中

8. SQLite：轻量级数据库，只具备基础的增删改查功能，

开源和闭源

9. 开源：开放源代码，免费盈利方式：卖服务

10. 闭源：不开放源代码，盈利方式：通过卖产品+卖服务

SQL

l 什么是SQL：Structured Query Language 结构化查询语言，用于程序员和数据库管理系统进行交流的语言

在终端或命令行中和数据库软件建立连接

mysql -uroot -p

数据库相关SQL

11. 查看所有数据库

12. 格式: show databases;

13. 创建数据库

14. 格式：create database 数据库名; create database db1;

15. 查看数据库详情

16. 格式：show create databse 数据库名; show createdatabase db1;

17. 创建数据库指定字符集

18. 格式：create database 数据库名 character set utf8/gbk;create database db2 character set gbk;

19. 删除数据库

20. 格式：drop database 数据库名; drop database db1;

21. 使用数据库

22. 格式：use 数据库名; use db1;

表相关的SQL 前提需要使用了某个数据库

23. 查询所有表 show tables;

24. 创建表

25. 格式：create table 表名(字段1名字段1类型,字段2名字段2类型); createtable person(name varchar(10),age int);

26. 练习：创建一个学生表student有学号id 姓名name 语文chinese数学math英语englishcreate table student(id int,name varchar(10),chinese int,math int,english int);

27. 查看表详情 show create table person;

28. 表引擎：

a) innodb：支持数据库的高级操作包括：事务、外键等

b) myisam：只支持数据基础的增删改查操作

29. 创建表指定引擎和字符集

30. 格式：create table 表名(字段1名字段1类型,字段2名字段2类型) engine=myisam/innodbcharset=utf8/gbk; create table t1(name varchar(10),age int) engine=myisamcharset=gbk;

31. 练习：创建2个数据库分别是 mydb1和mydb2在第一个数据库里面创建员工表emp 里面有姓名name年龄age工资sal字段，在mydb2里面创建英雄表hero，字段有名字name 年龄age英雄类型type 字符串类型并且指定此表的引擎为myisam字符集为gbk create database mydb1; use mydb1; create tableemp(name varchar(10),age int,sal int); create database mydb2; use mydb2; createtable hero(name varchar(10),age int,type varchar(10))engine=myisam charset=gbk;

32. 查看表字段

33. 格式：desc 表名; desc hero;

34. 删除表

35. 格式：drop table 表名; drop table hero;

36. 修改表名

37. 格式：rename table 原名 to 新名; create table t1(name varchar(10));

renametable t1 to t2;

38. 修改表引擎和字符集

39. 格式：alter table 表名 engine=myisam/innodbcharset=utf8/gbk; alter table t2 engine=myisam charset=gbk;

40. 添加表字段

41. 最后面添加格式：alter table 表名 add 字段名字段类型;

42. 最前面添加格式：alter table 表名 add 字段名字段类型 first;

43. xxx后面添加格式：altertable 表名 add 字段名字段类型 afterxxx; alter table t2 add age int; alter table t2 add sal int first; alter tablet2 add id int after name;

44. 删除表字段

45. 格式：alter table 表名 drop 字段名; alter table t2 drop id;

46. 修改字段名和类型

47. 格式：alter table 表名 change 原名新名新类型; alter table t2 change sal salary varchar(10);

48. 修改字段类型和位置

49. 格式：alter table 表名 modify 字段名新类型 first/after xxx; alter table t2 modify salary int after age;

50. 练习：

51. 创建数据库mydb3 指定字符集utf8 并使用create database mydb3 character set utf8; use mydb3;

52. 创建temp表只有id字段指定引擎为myisam 字符集为gbk create table temp(id int) engine=myisam charset=gbk;

53. 修改表名为emp rename table t_emp to emp;

54. 修改引擎为innodb 修改字符集为utf8 alter table empengine=innodb charset=utf8;

55. 在最后面添加name字段 alter table emp add name varchar(10);

56. 在name前面添加age字段 altertable emp add age int after id;

57. 在age后面添加工资sal字段 altertable emp add sal int after age;

58. 修改sal字段名称为salary alter table emp change salsalary int;

59. 修改age字段到最后面 alter table emp modify age intafter name;

60. 删除salary字段 alter table emp drop salary;

61. 删除表 drop table emp;

62. 删除数据库mydb3 drop database mydb3;

数据相关的SQL

63. 插入数据

64. 全表插入格式：insert into 表名 values (值1,值2,值3); insertinto person values(1,'Tom',20);

65. 指定字段格式：insert into 表名 (字段1,字段2) values(值1,值2);insert into person (id,name) values(2,'Jerry');

66. 批量插入：insert into 表名 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3),(值1,值2,值3); insertinto 表名 (字段1,字段2) values(值1,值2),(值1,值2),(值1,值2); insert into person values(3,'a1',25),(4,'a2',26); insert intoperson (id,name) values(5,'b1'),(6,'b2');

67. 查询数据

68. 格式：select 字段信息 from 表名where 条件; select * from person; select name fromperson; select name,age from person; select * from person where name='Tom';

69. 修改数据

70. 格式：update 表名 set 字段名=值,字段名=值 where 条件; update person set age=18 where name='Jerry'; -修改id大于4的年龄为35 updateperson set age=35 where id>4;

71. 删除数据

72. 格式：delete from 表名 where 条件; delete from person where name='a2'; -删除年龄大于25岁的数据 delete from person where age>25; -删除所有数据 delete from person;

中文乱码问题

//以上代码个别同学会出现执行出错的情况通过在终端中执行 set names gbk; 解决 select *from person; - 如果出现的不是报错而是查询数据时出现乱码把现有的数据库和现有的表删除掉重新创建数据库创建表保证字符集全部为utf8

主键约束

l 什么是主键：用于表示数据唯一性的字段称为主键

l 什么是约束：就是创建表的时候给字段添加的限制条件

l 主键约束：插入数据必须是唯一且非空的

l 格式：create tablet1(id int primary key,name varchar(10)); insert into t1 values(1,'刘备'); insert into t1 values(1,'关羽'); //报错不能重复 insert into t1 values(null,'关羽');//报错不能为null

主键约束+自增

l 自增数值只增不减

l 从历史最大值基础上+1

l 格式：create tablet2(id int primary key auto_increment, name varchar(10)); insert into t2values(null,'悟空'); //1 insert into t2 values(null,'八戒'); //2 insert into t2 values(10,'八戒'); //10insert into t2 values(null,'沙僧');//11 delete from t2id>=10; insert into t2 values(null,'沙僧');//12

注释

l 对表的字段进行描述 createtable t3(id int primary key auto_increment comment '主键字段',name varchar(10) comment '这是姓名');

'和`的区别

l ' 是用来修饰字符串的

l 是用来修饰表名和字段名的可以省略 create tablet4(idint,name`varchar(10));

数据冗余

l 如果数据库中的表设计不够合理，随着数据量的增长出现大量的重复数据，这种重复数据的现象称为数据冗余，通过拆分表的形式解决此问题

l 练习：请设计表保存以下两条数据

a) 集团总部下的教学研发部下的Java一部的员工苍老师，年龄18岁，工资100，性别男

b) 人事部下的员工小明工资5000，年龄30 -创建员工表 empid,name,age,salary,gender,deptid createtable emp(id int primary key autoincrement,name varchar(10),age int,salaryint,gender varchar(5),deptid int); -创建部门表 dept id,name,parentidcreate table dept(id int primary key autoincrement,name varchar(10),parentid int); -往两个表插入数据 insert into deptvalues(null,'集团总部',null),(null,'教学研发部',1),(null,'Java一部',2),(null,'人事部',1); insert into emp values(null,'苍老师',18,100,'男',3),(null,'小明',30,5000,'男',4);

l 练习2：设计表保存以下数据

c) 家电分类下电视机分类下的小米电视价格(price)2588,库存(num)500

d) 办公用品分类下的打印机分类下的惠普打印机价格1500,库存100 创建商品表分类表create table item(id int primary key autoincrement,namevarchar(10),price int,num int,categoryid int); create table category(id intprimary key autoincrement,namevarchar(10),parentid int); -插入数据 insert intocategory values(null,'家电',null),(null,'电视机',1),(null,'办公用品',null),(null,'打印机',3); insert into item values(null,'小米电视',2588,500,2),(null,'惠普打印机',1500,100,4);

事务

l 什么是数据库中的事务？事务是数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL语句全部执行成功或者全部执行失败

l 事务相关指令：

l 开启事务 begin;

l 提交事务 commit;

l 回滚事务 rollback;

createtable user(id int primary key auto_increment,name varchar(10),money int,statevarchar(5));

insertinto user values(null,'钢铁侠',5000,'正常'),(null,'绿巨人',500,'正常'),(null,'超人',100,'冻结');

l 钢铁侠给绿巨人转账1000update user set money=money-1000 where id=1 and state='正常'; update user set money=money+1000 where id=2 and state='正常';

l 钢铁侠给超人转账1000update user set money=money-1000 where id=1 and state='正常'; update user set money=money+1000 where id=3 and state='正常';

l 在事务保护下执行:钢铁侠给超人转账1000 begin; //开启事务 update user setmoney=money-1000 where id=1 and state='正常'; -在这个时间点再开一个窗口检查数据库里面的数据是否改变 update user set money=money+1000 where id=3 and state='正常'; rollback; //转账失败回滚事务

l 在事务保护下执行:钢铁侠给绿巨人转账1000 begin; //开启事务 update user setmoney=money-1000 where id=1 and state='正常'; update userset money=money+1000 where id=2 and state='正常'; commit;//转账成功提交事务

l savepoint; 保存回滚点 begin; update user set money=2001 where id=1; savepoint s1; updateuser set money=2002 where id=1; savepoint s2; update user set money=2003 whereid=1; rollback to s2;

SQL分类

73. DDL：Data DefinitionLanguage 数据定义语言，包括：create 、drop、alter、 truncate ,不支持事务

74. truncate table 表名：删除表并创建新表自增数值清零

75. DML：DataManipulation Language 数据操作语言，包括：insert、delete、update、select(DQL)，支持事务

76. DQL: Data Query Language 数据查询语言，包括：select

77. TCL：TransactionControl Language 事务控制语言，包括：begin、commit、rollback、savepointxxx、rollback to xxx

78. DCL：Data ControlLanguage 数据控制语言，分配用户权限相关SQL

数据类型

79. 整数：常用类型int(m) 和 bigint(m), m代表显示长度，需要结合zerofill关键字使用 create table tint(id int(5) zerofill); insert into tintvalues(18);

80. 浮点数：常用类型 double(m,d) m代表总长度 d代表小数长度 25.321 m=5 d=3 decimal超高精度浮点数，当涉及超高精度运算时使用

81. 字符串：char(m)固定长度执行效率高最大长度255 varchar(m)可变长度节省资源最大65535 超高255建议使用text， text可变长度最大65535

82. 日期：date 只能保存年月日，time 只能保存时分秒，datetime 最大值 9999-12-31 默认值为null，timestamp 最大值2038-1-19默认值当前的系统时间 create table t_date(t1 date,t2 time,t3datetime,t4 timestamp);

insertinto tdate values('2019-2-20',null,null,null);insert into tdate values(null,'16:32:20','2019-2-20 16:32:22',null);

83. 其它类型

导入*.sql文件

84. windows系统把下载的文件放在d盘下面在终端中执行以下指令 source d:/tables.sql;

85. linux 系统把文件放在桌面在终端中执行以下指令 source /home/soft01/桌面/tables.sql;

86. 导入后执行show tables; 查看是否有四张表有说明搞定

isnull 和 is not null

87. 查询奖金为null的员工信息 select * from emp where comm isnull;

88. 查询mgr不为null值得员工姓名select ename from emp where mgr is not null;

别名

selectename as '姓名',sal as '工资'from emp; select ename '姓名',sal '工资' from emp; select ename 姓名,sal 工资 from emp;

去重 distinct

select distinct job from emp;

比较运算符 > < = >= <= !=和<>

89. 查询工资高于2000的所有员工编号empno,姓名ename，职位job，工资sal select empno,ename,job,sal fromemp where sal>2000;

90. 查询工资小于等于1600的所有员工的编号，姓名，工资 select empno,ename,salfrom emp where sal<=1600;

91. 查询部门编号是20的所有员工姓名、职位、部门编号deptno selectename,job,deptno from emp where deptno=20;

92. 查询职位是manager的所有员工姓名和职位 select ename,job from empwhere job='manager';

93. 查询不是10号部门的所有员工编号，姓名，部门编号（两种写法） selectempno,ename,deptno from emp where deptno!=10; select empno,ename,deptno fromemp where deptno<>10;

94. 查询titem表单价price等于23的商品信息 select * from titem where price=23; select * from t_item where price=23 \G;

95. 查询单价不等于8443的商品标题title和商品单价select title,price from t_item where price!=8443;

and和or

l and 并且&& 需要同时满足多个条件时使用

l or 或|| 需要满足多个条件中的某一个条件时使用

l 查询20号部门工资大于2000的员工信息 select * from emp where deptno=20and sal>2000;

l 查询10号部门奖金为null的员工信息 select * from emp where deptno=10and comm is null;

l 查询有上级领导mgr并且职位是manager的员工信息 select * from emp where mgr isnot null and job='manager';

l 查询20号部门或者工资小于1000的员工信息 select * from emp where deptno=20or sal<1000;

l 查询名字为king和james的员工信息 select * from emp whereename='king' or ename='james';

in 和 not in

96. 查询工资为5000，950，3000的员工信息 select * from emp where sal=5000 or sal=950 or sal=3000; select *from emp where sal in (5000,950,3000);

97. 查询James、king、ford的工资和奖金 select sal,comm from emp where ename in('james','king','ford');

98. 查询工资不是5000，950，3000的员工信息 select * from emp where sal not in (5000,950,3000);

betweenx and y 包括xy

99. 查询工资在2000到3000之间的员工信息select * from emp where sal>=2000 and sal<=3000; select * from emp wheresal between 2000 and 3000;

100.查询商品表单价在50到100之间的商品名称和商品单价select title,price from t_item where price between 50 and 100;

101.查询工资小于2000并且大于3000的员工信息select * from emp where sal not between 2000 and 3000;

模糊查询 like

l _代表单个未知字符

l %代表0或多个未知字符 -举例：以a开头 a% 以b结尾 %b 包含c %c% 第一个字符是a 倒数第二个字符是b a%b_ 匹配163邮箱%@163.com 任意邮箱 %@%.com

l 案例：

l 查询员工姓名以k开头的员工信息 select * from emp where ename like 'k%';

l 查询标题包含记事本的商品标题和商品单价 select title,price from t_item where title like '%记事本%';

l 查询单价低于100的记事本 select * from t_item where price<100 and title like '%记事本%';

l 查询有赠品的dell商品(卖点sellpoint包含赠字,标题包含dell) select * from titem wheresell_point like '%赠%' and title like '%dell%';

l 查询单价在100到200之外的联想商品 select * from t_item where pricenot between 100 and 200 and title like '%联想%';

l 查询分类categoryid为238和917的齐心商品 select * from titem where category_idin(238,917) and title like '%齐心%';

l 查询商品标题中不包含得力的商品信息select * from t_item where title not like '%得力%';

l 查询员工姓名包含a并且工资低于3000的员工姓名和工资 select ename,sal from emp whereename like '%a%' and sal<3000;

l 查询员工姓名不是以k开头并且有奖金的员工信息 select * from emp where ename not like 'k%' and comm>0;

l 查询30号部门职位包含man的员工姓名、职位、部门编号 select ename,job,deptnofrom emp where deptno=30 and job like '%man%';

排序

l order by 字段名 asc/desc;

l 查询员工姓名和工资降序select ename,sal from emp order by sal desc;

l 查询30号部门的员工信息工资降序排序 select * from emp where deptno=30 order by sal desc;

l 查询名字中包含a并且工资大于1000的员工信息按照工资升序排序 select * from emp whereename like '%a%' and sal>1000 order by sal;

l 查询所有员工信息按照部门编号升序排序 select * from emp order by deptno,sal desc;

l 查询带燃字的商品单价升序排序select * from t_item where title like '%燃%' order byprice;

l 查询所有dell商品标题，分类categoryid，单价按照分类升序排序，单价降序排序 selecttitle,categoryid,price from titem where title like '%dell%' order by categoryid,price desc;

分页查询

l limit 跳过的条数,请求的条数(每页的条数)

l 查询员工表工资最高的前五条数据select * from emp order by sal desc limit 0,5; -以上数据的第二页数据 select * from emp order by sal desc limit 5,5;

l 查询商品表单价升序第三页每页四条数据 select * from t_item order by price limit 8,4;

l limit (页数-1)*每页的条数,每页的条数

课程回顾

102.比较运算符 > < >= <= = != <>

103.and 和 or

104.in 和 not in

105.between x and y not between x andy

106.like not like _单个未知 %0或多个未知

107.order by 字段1 asc/desc,字段2;

108.limit 跳过的条数,请求的条数

concat()函数

l 可以将字符串进行拼接

l 查询员工表每个员工的姓名和工资要求工资显示单位元 select ename,concat(sal,'元') from emp;

l 查询商品表，显示商品名称，单价（价格：25元） select title,concat('价格：',price,'元') from t_item;

l select concat('a','b'); select'helloworld';

数值计算 + - * / % 7%2 = mod(7,2)

109.查询员工的姓名，工资，年终奖（年终奖=工资5） select ename,sal,sal5 年终奖 from emp;

110.查询商品名称，单价，库存，总价值（单价库存） selecttitle,price,num,numprice 总价值 from t_item;

日期相关函数

111.获取当前日期+时间 select now();

112.获取当前年月日和时分秒 select curdate(),curtime();

113.从完整的年月日时分秒中提取年月日和提取时分秒 select date(now()); select time(now());

114.从完整的年月日时分秒中提取时间分量 extract select extract(year/month/day/hour/minute/second fromnow());

n 查询每个员工的姓名和入职的年份入职时间字段：hiredate select ename,extract(year from hiredate) from emp;

115.日期格式化 date_format()

n date_format(时间,格式);

n 格式：%Y 四位年 %y 两位年 %m 两位月 %c 一位月 %d 日 %H 24小时 %h 12小时 %i 分 %s 秒

n 把默认的时间格式转成年月日时分秒select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');

116.把非标准时间格式转成标准格式 strtodate()

n strtodate(字符串时间,格式) 14.08.2019 08:00:00 select strtodate('14.08.201908:00:00','%d.%m.%Y %H:%i:%s');

ifnull()

l age = ifnull(x,y) 如果x值为null则age=y 如果x不为null则age=x

l 把emp表中奖金为null的改成0 update emp set comm=ifnull(comm,0);

聚合函数

l 对多行数据进行统计查询：求和平均值最大值最小值计数

l 求和：sum(求和的字段) 统计20号部门的工资总和select sum(sal) from emp where deptno=20;

l 平均值：avg(字段) 统计所有员工的平均工资 select avg(sal) from emp;

l 最大值：max(字段) 查询30号部门的最高工资select max(sal) from emp where deptno=30;

l 最小值：min(字段) 查询30号部门的最低工资select min(sal) from emp where deptno=30;

l 计数：count(字段) 一般写count() 只有涉及null值时才使用字段名查询员工表中10号部门的员工数量 select count() from emp wheredeptno=10; 查询所有员工中有上级领导的员工数量 select count(mgr) fromemp;

字符串相关函数

l charlength(str)获取字符串的长度 select ename,charlength(ename)from emp;

l instr(str,substr) 获取substr在str中出现的位置从1开始 select instr('abcdefg','d');

l insert(str,start,length,newstr)select insert('abcdefg',3,2,'m');

l lower(str) upper(str) selectlower('NBa'),upper('NBa');

l trim(str) 去两端空白 select trim(' a b ');

l left(str,index) 从左边截取

l right(str,index) 从右边截取

l substring(str,index,?length) 从指定位置截取 select left('abcdefg',2); select right('abcdefg',2); selectsubstring('abcdefg',3,2); select substring('abcdefg',3);

l repeat(str,count) 重复 select repeat('ab',3);

l replace(str,old,new) 替换 select replace('This is mysql','my','your');

l reverse() 反转 select reverse('abc');

数学相关的函数

l floor(num) 向下取整 select floor(3.84);

l round(num) 四舍五入 select round(3.8);

l round(num,m) m代表保留几位小数 select round(3.8679,2); 3.87

l truncate(num,m) 非四舍五入 select truncate(3.8679,2); 3.86

l rand() 随机数0-1 获取 0-5的随机整数select floor(rand()6); 获取3-6的随机整数 0-3 + 3 select floor(rand()4)+3;

数学相关的函数

117.向下取整 floor(num) select floor(3.14);

118.四舍五入 round(num) select round(23.8);

119.round(num,m) select round(23.879,2);

120.非四舍五入 truncate(num,m) select truncate(23.879,2);

121.随机数 rand() 0-1 select rand();

122.获取0-5的整数随机数 select floor(rand()*6);

123.3-5的随机数

分组查询 group by

124.查询每个部门的平均工资 select deptno,avg(sal) from emp group by deptno;

125.查询每个部门的工资总和 select deptno,sum(sal) from emp group by deptno;

126.查询每种职业的最高工资 select job,max(sal) from emp group by job;

127.查询每个领导下的人数 select mgr,count(*) from emp where mgr is not null group by mgr;

128.查询每个部门工资大于1000的员工数量 select deptno,count(*) from empwhere sal>1000 group by deptno;

129.多字段分组查询只需要在group by后面写多个字段名通过逗号分隔

130.每个部门每个主管的手下人数 select deptno,mgr,count(*) from emp where mgr is not null group bydeptno,mgr;

53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。select deptno,count(),sum(sal)from emp group by deptno order by count(),sum(sal) desc; -别名写法 select deptno,count(*) c,sum(sal) s from emp group by deptno orderby c,s desc;

54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。select deptno,avg(sal) a,min(sal),max(sal) from emp where salbetween 1000 and 3000 group by deptno order by a; 55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列 select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp wheremgr is not null group by job order by c desc,a;

having

l where后面只能写普通字段的条件不能写聚合函数的条件

l having和where类似都是用于添加条件的，having后面可以写普通字段的条件也可以写聚合函数的条件，但是建议写聚合函数的条件，而且要结合group by 使用

l 查询每个部门的平均工资，要求平均工资大于2000 -错误写法 where后面不能写聚合函数 select deptno,avg(sal) from emp where avg(sal)>2000 group bydeptno; -正确写法：select deptno,avg(sal) a from emp groupby deptno having a>2000;

l 查询商品表中每个分类的平均单价，要求平均单价小于100 select categoryid,avg(price)a from titem group by category_id having a<100;

l 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资降序排序 select deptno,sum(sal),avg(sal) a from emp where sal between 1000and 3000 group by deptno having a>=2000 order by a desc;

l 查询emp表中平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序 selectdeptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by adesc;

l 查询emp表中不是以s开头的职位，每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位，根据人数升序，如果人数一致则根据工资总和降序 select job,count(*) c,sum(sal) s,max(sal) from emp where job notlike 's%' group by job having avg(sal)!=3000 order by c, s desc;

l 查询每年入职的人数(扩展) select extract(year from hiredate) year,count(*) from emp group byyear;

l 查询最高平均工资的部门编号（扩展）（并列第一的问题不能解决） select deptno from emp group by deptno order by avg(sal) desc limit0,1;

子查询（嵌套查询）

l 可以在查询语句中嵌套另一条sql语句

l 可以嵌套n层

l 查询emp表中工资最高的员工信息 select max(sal) from emp; select * from emp where sal=(selectmax(sal) from emp);

l 查询emp表中工资大于平均工资的员工信息 select avg(sal) from emp; select * from emp where sal>(selectavg(sal) from emp);

l 查询工资高于20号部门最高工资的员工信息 select max(sal) from emp where deptno=20; select * from emp wheresal>(select max(sal) from emp where deptno=20);

l 查询和jones相同工作的其它员工信息 select job from emp where ename='jones'; select * from emp wherejob=(select job from emp where ename='jones') and ename!='jones';

l 查询工资最低的员工的同事们的信息(同事=相同job) -得到最低工资select min(sal) from emp;

n 得到拿最低工资员工的职位select job from emp where sal=(select min(sal) from emp);

n 通过职位得到此职位的员工信息排除最低工资那个哥们儿 select * from emp where job=(select job from emp where sal=(selectmin(sal) from emp)) and sal!=(select min(sal) from emp);

l 查询最后入职的员工信息select max(hiredate) from emp; select * from emp where hiredate=(selectmax(hiredate) from emp);

l 查询员工king所属的部门编号和部门名称（需要用到部门表dept） select deptno from emp whereename='king'; select deptno,dname from dept where deptno=(select deptno fromemp where ename='king');

l 查询有员工的部门信息(查询在员工表中出现的部门的信息) -得到员工表中的部门编号 select distinct deptno fromemp; -查询上面结果对应的部门详情 select * from dept where deptno in(select distinct deptno from emp);

l 查询平均工资最高的部门信息(难度最高需要考虑并列第一问题) -查询最高的平均工资 select avg(sal)a from emp group by deptno order by a desc limit 0,1; -通过最高的平均工资找到对应的部门编号 select deptno from emp group by deptno having avg(sal)=(selectavg(sal) a from emp group by deptno order by a desc limit 0,1); -通过部门编号查询部门信息 select * from dept where deptno in(上面一坨);

l 子查询总结：

l 嵌套在sql语句中的查询语句称为子查询

l 子查询可以嵌套n层

l 子查询可以写在什么位置？

l 写在where和having的后面当做查询条件的值

l 写在创建表的时候 -格式：create table 表名 as (子查询) create table newemp as (select * from emp where deptno=10);

l 写在from后面当成一个虚拟表 必须有别名 select ename from (select * from emp where deptno=10)newtable;

关联查询

l 同时查询多张表的查询方式称为关联查询

l 查询每一个员工的姓名和其对应的部门名称 select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

l 查询在new york工作的所有员工信息 select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='newyork';

笛卡尔积

l 关联查询必须写关联关系，如果不写则得到两张表的乘积，这个乘积称为笛卡尔积

l 工作中不允许出现因为超级耗内存有可能直接崩溃

等值连接和内连接

l 等值连接和内连接都是关联查询的查询方式

l 等值连接和内连接查询到的结果一样，都为两张表的交集数据

l 等值连接：select *from A,B where A.x=B.x and A.age=18;

l 内连接：select * fromA join B on A.x=B.x where A.age=18;

l 查询每个员工的姓名和对应的部门名称select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

l 查询在new york工作的所有员工信息 select e.* from emp e join dept d on e.deptno=d.deptno whered.loc='new york';

外链接

l 格式：select * fromA left/right join B on A.x=B.x where A.age=18;

l 查询部门表的全部名称和对应的员工姓名 select d.dname,e.ename from emp e right join dept d one.deptno=d.deptno;

关联查询总结：

131.关联查询的查询方式包括三种：等值连接、内连接和外链接

132.如果查询两张表的交集数据使用等值连接和内连接，推荐使用内连接

133.如果查询一张表的全部数据和另外一张表的交集数据使用外连接，外链接只需要掌握一种即可

表设计之关联关系

一对一

l 什么是一对一：有AB两张表，A表中一条数据对应B表中的一条数据，同时B表中一条数据也对应A表中的一条数据

l 应用场景：用户表和用户信息扩展表，商品表和商品信息扩展表

l 如何建立关系：在从表中添加外键字段指向主表的主键

l 练习：创建用户表user（id,username,password）和扩展表userinfo(user_id,nick,loc)并且保存以下数据

l 建表：create tableuser(id int primary key autoincrement,usernamevarchar(10),password varchar(10)); create table userinfo(userid int,nickvarchar(10),loc varchar(10));

libaiadmin 李白建国门外大街 liubei 12345 刘皇叔荆州 liudehua aabbcc 刘德华香港 insert into uservalues(null,'libai','admin'),(null,'liubei','12345'),(null,'liudehua','aabbcc');insert into userinfo values(1,'李白','建国门外大街'),(2,'刘皇叔','荆州'),(3,'刘德华','香港');

l 查询libai的密码是什么 select password from user where username='libai';

l 查询每个用户的用户名和昵称select u.username,ui.nick from user u join userinfo ui on u.id=ui.user_id;

l 查询刘德华的用户名 selectu.username from user u join userinfo ui on u.id=ui.user_id where ui.nick='刘德华';

一对多

l 什么是一对多：有AB两张表：A表中一条数据对应B表的多条数据，同时B表中的一条数据对应A表的一条数据

l 场景：员工表和部门表商品表和商品分类表

l 如何建立关系：在多的表中添加外键指向另外一张表的主键

l 练习：创建temp(id,name,deptid)和tdept(id,name) create tabletemp(id int primary key autoincrement,name varchar(10),deptid int); createtable tdept(id int primary key autoincrement,namevarchar(10));

l 保存以下数据：神仙部门的孙悟空和猪八戒，妖怪部门的蜘蛛精和白骨精 insert into tdeptvalues(null,'神仙'),(null,'妖怪'); insert into temp values(null,'孙悟空',1),(null,'猪八戒',1),(null,'蜘蛛精',2),(null,'白骨精',2);

l 查询每个员工的姓名和对应的部门名称select e.name,d.name from temp e join tdeptd on e.dept_id=d.id;

l 查询猪八戒的所在的部门名select d.name from temp e join tdeptd on e.dept_id=d.id where e.name='猪八戒';

l 查询妖怪部的员工都有谁select e.name from temp e join tdeptd on e.dept_id=d.id where d.name='妖怪';

多对多

l 什么是多对多：有AB两张表，A表中一条数据对应B表的多条数据，同时B表的一条数据对应A表的多条，称为多对多

l 应用场景：老师和学生

l 如何建立关系：创建第三张关系表，在关系表中有两个字段指向另外两个表的主键

l 练习：创建老师表teacher(id,name)和学生表student(id,name)保存以下数据关系表t_s(tid,sid)

l 创建表 create tableteacher(id int primary key autoincrement,namevarchar(10)); create table student(id int primary key autoincrement,namevarchar(10)); create table ts(tid int,sidint); 苍老师:小米、小红、小绿传奇哥：小白、小绿 insert into teacher values(null,'苍老师'),(null,'传奇哥'); insert into studentvalues(null,'小米'),(null,'小红'),(null,'小绿'),(null,'小白'); insert into ts values(1,1),(1,2),(1,3),(2,3),(2,4);

l 查询每个老师的姓名和对应的学生姓名select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student son s.id=ts.sid;

l 查询苍老师的学生都有谁select s.name from teacher t join t_s ts on t.id=ts.tid join student s ons.id=ts.sid where t.name='苍老师';

l 查询小绿的老师都有谁 selectt.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sidwhere s.name='小绿';

自关联

l 当前表的外键指向当前表的主键称为自关联

l 应用场景：需要保存如：上级领导、上级分类、上级部门

l 查询方式：把一张表当成两张表

连接方式和关联关系

l 连接方式指内连接和外链接

l 关联关系是指两张表之间存在逻辑关系包括：一对一、一对多、多对多

表设计案例：权限管理

l 需要创建三张主表：用户表user(id,name)角色表role(id,name) 权限表module(idname)，还需要两张关系表：用户角色关系表 ur(uid,rid) 、角色权限关系表 rm(rid,mid) create table user(id intprimary key autoincrement,namevarchar(10)); create table role(id int primary key autoincrement,namevarchar(10)); create table module(id int primary key autoincrement,namevarchar(10)); create table ur(uid int,rid int); create table r_m(ridint,mid int);

l 保存以下数据：用户表：刘德华、张学友、王菲角色表：男游客、男会员、女游客、女管理员权限表：男浏览、男发帖、男删帖、女浏览、女发帖、女删帖关系数据：刘德华(男会员，女游客) 张学友(男游客，女游客) 王菲(女管理员，男会员) 男游客(男浏览) ，男会员（男浏览，男发帖），女游客（女浏览），女管理员（女浏览、女发帖、女删帖）insert into user (name) values('刘德华'),('张学友'),('王菲'); insert into role (name) values('男游客'),('男会员'),('女游客'),('女管理员'); insert into module (name) values('男浏览'),('男发帖'),('男删帖'),('女浏览'),('女发帖'),('女删帖'); insert into ur values(1,2),(1,3),(2,1),(2,3),(3,4),(3,2);insert into rm values(1,1),(2,1),(2,2),(3,4),(4,4),(4,5),(4,6);

l 查询每个用户的名字和对应的权限名字select u.name,m.name from user u join urur on u.id=ur.uid join rm rm on ur.rid=rm.rid join module m on rm.mid=m.id;

l 查询刘德华拥有的权限 selectm.name from user u join ur ur onu.id=ur.uid join rm rm on ur.rid=rm.rid join module m on rm.mid=m.id whereu.name='刘德华';

l 查询有男发帖权限的用户都有谁select u.name from user u join ur ur on u.id=ur.uidjoin rm rm on ur.rid=rm.rid join module m on rm.mid=m.id where m.name='男发帖';

面试题

时间金额关系性别红包类型姓名

l 流水表trade: id timemoney type pid create table trade(id int primary key auto_increment,timedate,money int,type varchar(5),pid int);

l 人员表person：id name gender rel create table person(id int primary keyauto_increment,name varchar(10),gender varchar(5),rel varchar(5));

l 插入人员表数据：insertinto person values(null,'刘德华','男','亲戚'),(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友');

insertinto trade values(null,'2018-03-20',1000,'现金',1),(null,'2018-04-14',500,'现金',2),(null,'2018-04-14',-50,'现金',2),(null,'2018-03-11',20000,'支付宝',3),(null,'2018-03-11',-5,'支付宝',1),(null,'2018-05-14',2000,'微信',4),(null,'2018-06-25',-20000,'微信',5);

134.统计春节（2018年2月15号）到现在的红包收益 select sum(money) from trade where time>strtodate('2018年2月15号','%Y年%c月%d号');

135.查询春节到现在金额大于100所有女性亲戚的名字和金额 selectp.name,t.money from trade t join person p on t.pid=p.id where time>strtodate('2018年2月15号','%Y年%c月%d号') and t.money not between -100 and 100and p.gender='女' and p.rel='亲戚';

136.查询三个平台分别收入的红包金额 select type,sum(money) from trade where money>0 group by type;

视图

l 什么是视图：视图和表都是数据库中的对象，视图可以理解成是一张虚拟的表，视图本质就是取代了一段SQL查询语句。

l 为什么使用视图：使用视图可以起到SQL语句重用的作用，提高开发效率，还可以隐藏敏感信息

l 创建视图格式：createview 视图名 as (子查询);

a) 创建10号部门员工的视图 create view vemp10 as (select * from emp where deptno=10);

b) 创建没有工资的视图 create view vempnosal as(select ename,deptno,mgr from emp);

c) 创建一个显示每个部门编号、平均工资、最高工资、最低工资、工资总和、员工人数的视图 create view vempinfo as(select deptno,avg(sal),max(sal),min(sal),sum(sal),count(*) from emp group bydeptno);

l 视图的分类

d) 简单视图：创建视图时的子查询不包含：去重、函数、分组、关联查询创建的视图称为简单视图，可以对简单视图进行增删改查操作

e) 复杂视图：和简单视图相反，只能对复杂视图进行查询操作

l 对简单视图进行增删改操作操作方式和table一样

f) 插入数据 insert into vemp10(empno,ename,deptno) values(10010,'Tom',10);//成功 insertinto vemp10 (empno,ename,deptno)values(10011,'Jerry',20); //成功数据污染

g) 往视图中插入一条视图中不可见但是在原表中可见的数据称为数据污染，通过with check option 关键字避免出现数据污染现象 create viewvemp20 as(select * from emp wheredeptno=20) with check option; insert into vemp20(empno,ename,deptno) values(10012,'Lucy',20);//成功 insert into vemp20(empno,ename,deptno) values(10013,'Lily',30); //失败数据污染插入不进去

h) 修改和删除只能操作视图中存在的数据 update vemp20 setsal=1000 where empno=10010;//修改失败 delete from vemp20 where empno=10010;//删除失败 delete from vemp10 where empno=10010;//删除成功

l 创建或修改视图 create orreplace view vemp20 as (select enamefrom emp where deptno=20);

l 删除视图 drop view 视图名;

l 创建视图时如果子查询中使用了别名则之后对视图进行操作只能使用别名 create view vemp30 as(select ename name,sal from emp where deptno=30); select ename from vemp30; //报错找不到ename

约束

l 什么是约束：约束就是给字段添加的限制条件

非空约束 not null

l 字段的值不能为nullcreate table t1(id int,age int not null); -测试：insertinto t1 values(1,20); //成功 insert into t1values(2,null); //失败不能为null值

唯一约束 unique

l 字段的值不能重复 createtable t2(id int,age int unique); -测试：insert into t2values(1,20);//成功 insert into t2 values(2,20);//失败不能重复

默认约束 default

l 给字段设置默认值 createtable t3(id int,age int default 20); -测试：insert intot3 (id) values(1); //触发默认值生效 insert into t3 values(2,50);//不触发 insert into t3 values(3,null);//不触发

主键约束 primary key

l 主键：表示数据唯一性的字段称为主键

l 主键约束：唯一且非空

外键约束

l 外键：用于建立关系的字段

l 外键约束：为了保证两张表之间建立正确的关系，外键字段的值可以为null，可以重复，不能是另外一张表中不存在的数据，建立好关系后被依赖的数据不能先删除，被依赖的表不能先删除

l 如何使用外键约束

i) 先创建部门表 create table dept(id int primary key auto_increment,namevarchar(10));

j) 创建员工表 create table emp(id int primary key autoincrement,name varchar(10),deptid int,constraint fkdept foreignkey(deptid) references dept(id)); -格式介绍：constraint 约束名称 foreign key(外键字段名) references 被依赖的表名(被依赖的字段名)

-测试：insert into dept values(null,'神仙'),(null,'妖怪'); insert into emp values(null,'悟空',1);//成功 insert into emp values(null,'赛亚人',3);//报错 delete from dept where id=1;//报错因为有依赖数据drop table dept;//报错因为有依赖数据 drop table emp;//成功 drop table dept;//成功再次删除成功因为没有依赖表和数据了

索引

l 什么是索引：索引是数据库中用于提高查询效率的技术，工作原理类似于目录

l 为什么使用索引：如果不使用索引，数据会零散的保存到每一个磁盘块中，查找数据时需要逐个遍历每一个磁盘块直到找到数据为止，使用索引后磁盘块会以树桩结构保存，查找数据时可以大大减低磁盘块的访问量，从而提高查询效率

l 有索引就一定好吗？不是，如果数据量比较小，有索引反而会降低查询效率 -测试没有索引的查询效率 select * from item2 wheretitle='100'; //1.17秒

创建索引

l 格式：create index 索引名 on 表名(字段名(?字段长度)); create index iitemtitleon item2(title); -再次测试：select * from item2 wheretitle='100'; //0.03秒

l 索引是越多越好吗？不是，只针对常用的查询字段创建索引,因为索引会占磁盘空间

查看索引

l 格式：show indexfrom 表名; show index from item2;

删除索引

l 格式: drop index 索引名 on 表名; drop index iitemtitle on item2;

复合索引

l 通过多个字段创建的索引称为复合索引create index iitemtitle_price onitem2(title,price);

事务

l 数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL全部执行成功或全部执行失败

l 事务的ACID特性，此特性是保证事务正确执行的四大基本要素

l Atomicity：原子性，最小不可拆分，保证全部成功或全部失败

l Consistency：一致性，保证从一个一致状态到另一个一致状态

l Isolation：隔离性，多个事务直接互不影响

l Durability：持久性，事务提交后数据持久保存到磁盘中

l 事务相关指令：begin,commit,rollback,savepoint xxx,rollback to xxx;

JDBC

l Java DataBase Connecivity,java数据库连接，Sun公司提供的一套Java与数据库连接的API（application program interface应用程序编程接口）

l 为什么使用JDBC？如果没有JDBC，则每个数据库厂商都会定制自己的一套API，java程序员连接不同的数据库时需要学习多种数据库的API，学习成本太高，使用JDBC接口后，各个数据库厂商针对此接口写实现类(驱动)，这样java程序员只需要掌握JDBC里面的一套方法就可以访问各种数据库

l 如何使用JDBC和数据库建立连接

k) 创建maven工程

l) 从maven.tedu.cn网站中搜索mysql 找到5.1.6版本的坐标复制到工程的pom.xml中

m) 创建Demo01.java 在main方法中写如下代码：//1. 注册驱动Class.forName("com.mysql.jdbc.Driver"); //2. 获取连接对象 Connection conn =

DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3", "root","root"); System.out.println(conn); //3. 创建执行SQL语句的对象 Statement stat =conn.createStatement(); //4. 执行SQL String sql ="create table jdbct1" + "(id int primary keyauto_increment," + "name varchar(10))"; stat.execute(sql);System.out.println("执行完成！"); //5. 关闭资源 conn.close();

Statement

l execute(sql) 执行sql，返回值为布尔值，返回值true代表有结果集 false代表没有结果集，此方法可以执行任意SQL，但是只推荐执行DDL(数据定义语言)

l executeUpdate(sql) 此方法执行insert、update、delete，返回值int，返回值代表生效行数

l executeQuery(sql) 此方法执行select 返回值为ResultSet(结果集)

ResultSet

l 获取数据时数据库类型和Java类型对比 mysql java int getInt() varchar getString() float/doublegetFloat()/getDouble() datetime/timestamp getDate()

l 两种获取数据的方式

l rs.getString("查询结果中字段的名称");

l rs.getString(查询结果中字段的位置); 位置从1开始

连接池DBCP

l Database Connection Pool 数据库连接池

l 为什么使用连接池：将连接重用，避免资源浪费，提高执行效率

l 如何使用连接池

PreparedStatement

l 预编译sql执行对象

l 好处：

l 代码结构整齐，可读性高

l 带有预编译功能，在创建对象时就把sql逻辑固定，可以避免sql注入

l 因为带有预编译功能，如果批量插入多条数据时，可以只编译一次sql，从而提高执行效率

l 当sql语句中没有变量时使用Statement，如果有变量时使用PreparedStatement

批量操作

l 可以将多条sql语句的多次数据传输合并成一次，从而提高执行效率

l Statement：//添加到批量操作 stat.addBatch(sql1);stat.addBatch(sql2); stat.addBatch(sql3); //执行批量操作stat.executeBatch();

l PreparedStatementPreparedStatement ps = conn.prepareStatement(sql1); ps.setString(1, "悟空"); ps.setInt(2, 500); ps.addBatch();

ps.setString(1, "悟能"); ps.setInt(2,300); ps.addBatch(); ps.setString(1, "吴京"); ps.setInt(2,200); ps.addBatch(); //执行批量操作 ps.executeBatch();

分页查询

String sql ="select name from jdbcperson " + "limit?,?"; PreparedStatement ps= conn.prepareStatement(sql); ps.setInt(1, (page-1)*count); ps.setInt(2, count); //执行 ResultSet rs =ps.executeQuery(); while(rs.next()) { String name = rs.getString(1); System.out.println(name); }

事务

137.关闭事务自动提交 conn.setAutoCommit(false)

138.提交事务 conn.commit();

139.回滚事务 conn.rollback();

实现转账流程

createtable person(id int primary key auto_increment,name varchar(10),money int); insertinto person values(null,'超人',500),(null,'蝙蝠侠',5000);

获取自增主键的值

createtable team(id int primary key autoincrement,namevarchar(10)); create table player(id int primary key autoincrement,namevarchar(10),team_id int);

获取元数据

//数据库元数据 DatabaseMetaData dbmd =conn.getMetaData(); System.out.println("驱动版本：" +dbmd.getDriverVersion()); System.out.println("用户名：" +dbmd.getUserName()); System.out.println("数据库厂商：" +dbmd.getDatabaseProductName()); //获取表的元数据 String sql = "select * from emp"; Statement stat =conn.createStatement(); ResultSetrs = stat.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); //获取表字段数量 int count =rsmd.getColumnCount(); //获取每个字段的名称 for(inti=0;i