数据库DataBase

数据库笔记

数据库DataBase

• 学习数据库主要学习的就是如何对数据进行增删改查操作

• 平时所提到的数据库(DBMS数据库管理系统)实际上就是款软件, 电脑上安装了这个软件,电脑就可以成为数据库服务器

• 常见的DBMS(数据库软件):

  • MySQL: Oracle公司产品, MySQL在08年被Sun公司收购,09年Sun公司被Oracle收购, 目前市占率第一, 开源软件, 原MySQL创始人从Oracle离职创办MariaDB
  • Oracle: Oracle公司产品,闭源, 性能最强价格最贵, 市占率第二 , 拉里埃里森
  • SQLServer: 微软公司产品, 市占率第三 , 闭源 . 主要应用在微软的整套解决方案中(开发网站需要用到的所有= 编程语言+web服务软件+操作系统+数据库软件)
  • DB2: IBM公司产品,闭源 主要应用在IBM的整套解决方案中
  • SQLite: 轻量级数据库, 安装包只有几十k, 功能只具备最基础的增删改查 .

• 开源和闭源

  • 开源: 开放源代码, 有技术大拿无偿维护升级, 产品免费 靠卖服务盈利
  • 闭源: 不开放源代码, 有黑客或技术大拿攻击,但是闭源公司有钱 雇人维护升级, 靠卖产品和卖服务盈利

• 学习数据库主要是学习一门语言: SQL(Structured Query Language) 结构化查询语言 , 用于程序员和数据库软件之间进行交流

• 执行SQL语句需要先连接数据库软件

  • window: 开始->所有程序->MySQL或MariaDB->MySQL Client 点击 运行输入密码后回车

• Linux系统: 在桌面上右键 打开终端, 输入 mysql -uroot -p 回车 输入密码再回车.

• 如何检查MySQL服务是否开启?

  在我的电脑上右键->管理->服务->MySQL 查看是否启动 如果没有,右键启动

SQL语句分类:

• DDL: 数据定义语言,
• DML: 数据操作语言
• DQL: 数据查询语言
• TCL: 事务控制语言
• DCL: 数据控制语言

DDL数据定义语言

• 包括数据库相关和表相关的SQL语句
• 使用数据库软件保存数据需要先建库再建表

数据库相关的SQL

1. 查询所有数据库

   • 格式: show databases;

2. 创建数据库

   • 格式: create database 数据库名;

   • 举例:

     create database db1;

     create database db2 character set utf8;

     create database db3 character set gbk;

3. 查询数据库详情

   • 格式: show create database 数据库名;

   • 举例:

     show create database db1;

4. 删除数据库

   • 格式: drop database 数据库名;

   • 举例:

     drop database db3;

5. 使用数据库

   • 格式: use 数据库名;

   • 举例:

     use db1;

数据库相关回顾

1. 查询所有 show databases;
2. 创建 create database db1 character set utf8/gbk;
3. 查询详情 show create database db1;
4. 删除数据库 drop database db1;
5. 使用数据库 use db1;

数据库相关练习

1. 分别创建mydb1和mydb2数据库 字符集第一个是utf8第二个是gbk

   create database mydb1;

   create database mydb2 character set gbk;

2. 查询所有数据库检查是否创建成功

   show databases;

3. 分别查询两个数据库的字符集 检查是否成功

   show create database mydb1;

   show create database mydb2;

4. 先使用mydb1再使用mydb2

   use mydb1;

   use mydb2;

5. 删除两个数据库

   drop database mydb1;

   drop database mydb2;

表相关的SQL

• 操作表相关的SQL语句一定要保证使用了某个数据库, 不然会报错

1. 创建表

   • 格式: create table 表名(字段1名 类型,字段2名 类型);

   • 格式: create table 表名(字段1名 类型,字段2名 类型) charset=utf8/gbk;

   • 举例:

     create table student(name varchar(20),age int);

     create table hero(name varchar(20),type varchar(20), money int)charset=gbk;

2. 查询所有表

   • 格式: show tables;

3. 查询表详情

   • 格式: show create table 表名;
   • 举例: show create table hero;

4. 查询表字段

   • 格式: desc 表名;

5. 删除表

   • 格式: drop table 表名;

6. 修改表名

   • 格式: rename table 原名 to 新名;

     rename table student to stu;

练习题

1. 创建数据库db5 字符集为gbk 并使用

   create database db5 character set gbk;

   use db5;

2. 创建表t_hero英雄表 有名字和年龄字段 默认字符集

   create table t_hero(name varchar(20),age int);

3. 修改表名为 hero

   rename table t_hero to hero;

4. 查询表字段

   desc hero;

5. 删除表 drop table hero;

6. 删除数据库 drop database db5;

表相关续

1. 给表添加字段

   • 最后面添加格式: alter table 表名 add 字段名 类型;

   • 最前面添加格式: alter table 表名 add 字段名 类型 first;

   • 在xxx字段后面添加格式: alter table 表名 add 字段名 类型 after xxx;

   • 举例:

     create table emp(name varchar(20));

     alter table emp add age int;

     alter table emp add id int first;

     alter table emp add gender varchar(10) after name;

2. 删除表字段

   • 格式: alter table 表名 drop 字段名;

     alter table emp drop gender;

3. 修改表字段

   • 格式: alter table 表名 change 原名 新名 新类型;

   • 举例: alter table emp change age salary int;

表相关回顾

1. 创建表 create table t1(name varchar(20),age int)charset=utf8/gbk;
2. 查询所有表 show tables;
3. 查询表字段 desc t1;
4. 查询表详情 show create table t1;
5. 删除表 drop table t1;
6. 修改表名 rename table t1 to t2;
7. 添加表字段 alter table t1 add age int first/after xxx;
8. 删除表字段 alter table t1 drop age;
9. 修改表字段 alter table t1 change 原名 新名 新类型;

DML数组操作语言

• 包括对数据的增删改查

create database db4 character set utf8;
use db4;
create table person(name varchar(20),age int)charset=utf8;

1. 插入数据

   • 全表插入格式: insert into 表名 values(值1,值2,值3);

   • 指定字段插入格式: inert into 表名(字段1名,字段2名) values(值1,值2);

   • 举例:

     insert into person values(“Tom”,18);

     insert into person(name) values(“Jerry”);

   • 批量插入格式: insert into 表名 values(值1,值2,值3),(值1,值2,值3),(值1,值2,值3);

     insert into person values(“Lucy”,20),(“Lily”,21);

     insert into person(name) values(“Lilei”),(“Hanmeimei”);

   • 插入中文数据:

     insert into person values(“传奇哥”,18);

     如果执行带有中文的SQL语句报以下错误, 解决方案: set names gbk;

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9zLIRjxQ-1614837239199)(C:\Users\PC\AppData\Roaming\Typora\typora-user-images\image-20201106151424743.png)]

   

• 查询

  • 格式: select 字段信息 from 表名 where 条件;

  • 举例:

    select name from person;

    select name,age from person;

    select * from person;

    select * from person where age=18;

    select * from person where name=“Lucy”;

• 修改

  • 格式: update 表名 set 字段1名=值,字段2名=值 where 条件;

  • 举例:

    ​ update person set age=50 where name=‘Jerry’;

    ​ 把Tom名字改成汤姆年龄改成50

    ​ update person set age=50,name=“汤姆” where name=“Tom”;

• 删除

  • 格式: delete from 表名 where 条件;

  • 举例:

    delete from person where name=“Lucy”;

    delete from person where age<50;

    delete from person;

数据相关SQL回顾

1. 插入数据: insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2);
2. 查询数据: select 字段信息 from t1 where 条件;
3. 修改数据: update t1 set xxx=xxx,xxx=xxx where 条件;
4. 删除数据: delete from t1 where 条件;

综合练习

1. 创建数据库mydb3 字符集utf8 并使用
create database mydb3 character set utf8;
use mydb3;
2. 创建hero表 字段:id,name,type varchar(10)
create table hero(id int,name varchar(10),type varchar(10));
3. 表中插入以下数据: 
	insert into hero values
	(1, '李白', '刺客'),(2, '诸葛亮' , '法师'),   (3, '刘备', '战士'),
	(4, '孙尚香', '射手'),   (5, '关羽', '战士'),  (6, '刘禅', '辅助');
4. 修改李白的type为诗人   update hero set type='诗人' where name='李白';
5. 给表添加money字段	 alter table hero add money int;
6. 修改id小于5的money为6888	update hero set money=6888 where id<5; 
7. 修改诗人为打野  update hero set type="打野" where type="诗人";	 
8. 修改刘禅名字为阿斗价格为50  update hero set name="阿斗",money=50 where name="刘禅";
9. 删除id大于5的数据   delete from hero where id>5;
10. 修改所有英雄的价格为13888    update hero set money=13888;
11. 修改表名为heros     rename table hero to heros;       
12. 删除money字段  alter table heros drop money;
13. 删除所有数据   delete from heros;
14. 删除表   drop table heros;         15. 删除数据库  drop database mydb3;

数据库数据类型

• 整数: int(m) 和bigint(m) , m代表显示长度, m=10 18 0000000018 , 需要和zerofill关键字结合使用

  create table t1 (id int,age int(10) zerofill);

  insert into t1 values(1,18);

  select * from t1;

• 浮点数: double(m,d) m代表总长度, d代表小数长度 54.234 m=5 d=3 , decimal(m,d)是超高精度浮点数, 只有涉及到超高精度运算时使用

• 字符串:

  • char(m): 固定长度字符串, m=10 存abc 长度占10, 执行效率高于可变长度, 最大长度为255
  • varchar(m): 可变长度字符串,m=10 存abc 长度占3, 节省空间, 最大长度65535 建议保存255以内长度的数据
  • text(m):可变长度字符串 最大长度也是65535 建议保存长度大于255的数据

• 日期

  • date: 只能保存年月日

  • time: 只能保存时分秒

  • datetime: 年月日时分秒 , 默认值是null, 最大值9999-12-31

  • timestamp: 时间戳 年月日时分秒, 默认值为当前系统时间,最大值2038-1-19

  • 测试日期类型:

    create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);

    insert into t_date values(“2020-10-10”,null,null,null);

    insert into t_date values(null,“17:12:11”,“2018-11-12 11:11:11”,null);

建议方向

1. 卖油漆的
2. 摄影的
3. 家乡旅游景点介绍
4. 培训机构
5. 卖家具的

综合练习题

1. 创建数据库mydb2 字符集utf8并使用
create database mydb2 character set utf8;
use mydb2;
2. 在数据库中创建员工表emp 字段:id,name,sal,deptId(部门id) 字符集utf8
create table emp(id int,name varchar(20),sal int,deptId int)charset=utf8;
3. 创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8
create table dept(id int,name varchar(20),loc varchar(20))charset=utf8;
4. 部门表插入以下数据: 1 神仙部 天庭       2 妖怪部  盘丝洞
insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盘丝洞');
5. 员工表插入一下数据:  1 悟空 5000 1    ,   2 八戒  2000  1   ,
					 3 蜘蛛精 8000  2  ,  4 白骨精 9000  2
insert into emp values(1,'悟空',5000,1),(2,'八戒',2000,1),(3,'蜘蛛精',8000,2),(4,'白骨精',9000,2);
6. 查询工资6000以下的员工姓名和工资  select name,sal from emp where sal<6000;
7. 修改神仙部的名字为取经部 update dept set name='取经部' where name='神仙部';
8. 给员工添加奖金comm字段 alter table emp add comm int;
9. 修改员工表中部门id为1的 奖金为500  update emp set comm=500 where deptId=1;
10. 把取经部的地址改成五台山  update dept set loc='五台山' where name='取经部';  
11. 删除两个表   12. 删除数据库  drop table emp;  drop table dept; drop database mydb2;

主键约束

• 什么是约束： 约束是创建表字段时添加的限制条件

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

• 主键约束: 限制主键的值 唯一且非空

• 如何使用：

  ​ create table t1(id int primary key,name varchar(20));

  ​ insert into t1 values(1,‘aaa’); //正确

  ​ insert into t1 values(1,‘bbb’); //报错 Duplicate entry 重复数据

  ​ insert into t1 values(null,‘ccc’); //报错 Cannot be null 不能为空

自增 auto_increment

• 自增规则：从历史最大值+1

• 如何使用：

  create table t2(id int primary key auto_increment,name varchar(10));

  insert into t2 values(null,‘aaa’); 1

  insert into t2 values(null,‘bbb’); 2

  insert into t2 values(10,‘ccc’); 10

  insert into t2 values(null,‘ddd’); 11

  delete from t2 where id>=10;

  insert into t2 values(null,‘eee’); 12

导入*.sql 批处理文件

• 下载emp.zip 解压得到emp.sql 把文件复制到d盘根目录 其它系统放到桌面

• 在终端中执行以下指令 导入emp.sql里面的数据 到MySQL中

  source d:/emp.sql;

• 测试： select * from emp; //如果有乱码 执行 set names gbk;

is null 和 is not null

• 查询字段的值为null时 使用 is null 反之使用 is not null

  • 查询没有上级领导的员工信息

    select * from emp where mgr is null;

  • 查询有上级领导的员工信息

    select * from emp where mgr is not null;

去重 distinct

• 查询员工表中出现的不同的工作

  select distinct job from emp;

• 查询员工表中出现的部门编号 去掉重复

  select distinct deptno from emp;

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

1. 查询工资小于等于3000的员工姓名和工资

   select ename,sal from emp where sal<=3000;

2. 查询工作不是程序员的员工姓名和工作 （两种写法）

   select ename,job from emp where job!=“程序员”;

   select ename,job from emp where job<>“程序员”;

and和or

• and类似Java中的 && ，有多个条件同时满足时使用
• or类型java中的|| ， 有多个条件满足一个就行的时候使用

1. 查询1号部门工资小于3000的员工信息

   select * from emp where deptno=1 and sal<3000;

2. 查询3号部门或工作是程序员的员工信息

   select * from emp where deptno=3 or job=‘程序员’;

3. 查询有上级领导中工资小于2000的员工姓名，工资和领导编号

   select ename,sal,mgr from emp where mgr is not null and sal<2000;

in关键字

1. 查询工资值为3000,1500,5000的员工信息

   select * from emp where sal=3000 or sal=1500 or sal=5000;

   select * from emp where sal in(3000,1500,5000);

2. 查询工作是程序员和CEO的员工姓名和工作

   select ename,job from emp where job in(“程序员”,“CEO”);

between x and y 在两者之间 包含 x和y

1. 查询工资在1000-2000之间的员工信息

   select * from emp where sal>=1000 and sal<=2000;

   select * from emp where sal between 1000 and 2000;

2. 查询工资在1000-2000以外的员工信息

   select * from emp where sal not between 1000 and 2000;

综合练习

1. 查询有上级领导并且是3号部门的员工信息

   select * from emp where mgr is not null and deptno=3;

2. 查询2号部门工资在1000到2000之间的员工姓名 工资和部门编号

   select ename,sal,deptno from emp where deptno=2 and sal between 1000 and 2000;

3. 查询1号部门工资为800和1600的员工信息

   select * from emp where deptno=1 and sal in(800,1600);

4. 查询1号和2号部门工资高于2000的员工信息

   select * from emp where deptno in(1,2) and sal>2000;

模糊查询 like

• _： 代表1个未知字符
• %：代表0或多个未知字符
• 举例：
  • 以x开头 x%
  • 以x结尾 %x
  • 以x开头y结尾 x%y
  • 包含x %x%
  • 第二个字符是x _x%
  • 第二个是x倒数第三个是y x%y_

1. 查询姓孙的员工姓名

   select ename from emp where ename like “孙%”;

2. 查询工作中第二个字是售 的员工信息

   select * from emp where job like “_售%”;

3. 查询名字中以精结尾的员工姓名

   select ename from emp where ename like “%精”;

4. 查询名字包含僧字的员工姓名和工资

   select ename,sal from emp where ename like “%僧%”;

排序 order by

• 格式: order by 字段名 asc升序(默认)/desc降序

1. 查询每个员工的姓名和工资，按照工资升序排序

   select ename,sal from emp order by sal;

2. 查询每个员工的姓名和工资，按照工资降序序排序

   select ename,sal from emp order by sal desc;

3. 查询3号部门的员工信息 按照工资降序排序

   select * from emp where deptno=3 order by sal desc;

4. 查询每个员工的姓名，工资和部门编号按照部门编号升序排序(多字段排序)

   select ename,sal,deptno from emp order by deptno,sal desc;

分页查询 limit

• 格式： limit 跳过的条数,请求的条数（每页的条数）

• 举例： limit (页数-1)*条数，条数

  • 请求1页的10条数据 limit 0,10
  • 请求第三页的5条数数据 limit 10,5
  • 请求第5页的8条数据 limit 32,8
  • 请求15页的10条数据 limit 140,10

1. 查询员工表中第一页的5条数据

   select * from emp limit 0,5;

2. 查询员工表中第2页的3条数据

   select * from emp limit 3,3;

3. 查询员工表中第3页的2条数据

   select * from emp limit 4,2;

4. 查询工资高于1000的员工信息 按照工资降序排序查询前三条

   select * from emp where sal>1000 order by sal desc limit 0,3;

数值计算 + - * / %

• 对查询的某个字段的值 进行计算

1. 查询每个员工的姓名，工资和年终奖（年终奖=5个月工资）

   select ename,sal,sal*5 from emp;

2. 修改1号部门的员工工资 每人涨薪5块钱

   update emp set sal=sal+5 where deptno=1;

综合练习

1. 查询有领导并且工资在1000到3000之间的员工信息
select * from emp where mgr is not null and sal between 1000 and 3000;
2. 查询1号部门有几种不同的工作
select distinct job from emp where deptno=1;
3. 查询名字中包含精或者工作中包含序的员工姓名和工作
select ename,job from emp where ename like "%精%" or job like "%序%";
4. 查询3号部门工资最高的员工姓名和工资
select ename,sal from emp where deptno=3 order by sal desc limit 0,1;
5. 查询上级领导编号是8工资最高的3个人的信息
select * from emp where mgr=8 order by sal desc limit 0,3;
6. 查询1号部门和2号部门的员工信息工资升序，查询第3页的2条数据
select * from emp where deptno in(1,2) order by sal limit 4,2;

聚合函数

• 聚合函数就是对查询的多条数据进行统计查询
• 统计方式： 1. 平均值 2. 最大值 3. 最小值 4. 求和 5. 计数

1. 平均值avg(字段名)

   • 查询所有员工的平均工资

     select avg(sal) from emp;

   • 查询2号部门的平均工资

     select avg(sal) from emp where deptno=2;

2. 最大值max(字段名)

   • 查询1号部门的最高工资

     select max(sal) from emp where deptno=1;

   • 查询工资高于2000块钱的员工中资历最小的员工是什么时候入职的

     select max(hiredate) from emp where sal>2000;

3. 最小值min(字段名)

   • 查询2号部门的最低工资

     select min(sal) from emp where deptno=2;

4. 求和sum(字段名)

   • 查询1号部门的工资总和

     select sum(sal) from emp where deptno=1;

5. 计数count(字段名)

   • 查询1号部门工资大于2000的员工数量

     select count(*) from emp where deptno=1 and sal>2000;

别名

​ select ename as “姓名” from emp;

select ename "姓名" from emp;

​ select ename 姓名 from emp;

1. 查询1号部门的平均工资，最高工资，最低工资，工资总和，人数 起别名

   select avg(sal) 平均工资, max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,count(*) 人数 from emp where deptno=1;

综合练习题

1. 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第二页的2条数据(每页2条)

   select ename,sal from emp where sal>2000 order by sal limit 2,2;

2. 查询和销售相关工作的工资总和

   select sum(sal) from emp where job like “%销售%”;

3. 查询程序员的人数 select count(*) from emp where job=“程序员”;

4. 查询1号部门中有领导的员工中的最高工资

   select max(sal) from emp where deptno=1 and mgr is not null;

5. 查询2号部门的最高工资和最低工资并起别名

   select max(sal) 最高工资,min(sal) 最低工资 from emp where deptno=2;

6. 查询1号部门的里面名字中包含空的员工姓名

   select ename from emp where deptno=1 and ename like “%空%”;

分组查询 group by

• 分组查询可以将某个字段相同值的数据划分成一组 然后进行统计查询

1. 查询每个部门的平均工资

   select deptno,avg(sal) from emp group by deptno;

2. 查询每个部门的最高工资

   select deptno,max(sal) from emp group by deptno;

3. 查询每种工作的工资总和

   select job,sum(sal) from emp group by job;

4. 查询每个部门工资高于2000的员工人数

   select deptno,count(*) from emp where sal>2000 group by deptno;

各个关键字的顺序

​ select * from 表名 where 条件 group by 分组字段名 order by 排序字段 limit 跳过条数，请求条数

1. 查询每个部门工资高于1000的员工人数，按照人数降序排序取前两条数据

   select deptno,count(*) from emp where sal>1000 group by deptno order by count(*) desc limit 0,2;
   

综合练习题

1. 查询没有上级领导的员工编号empno，姓名，工资
2. 查询有奖金的员工姓名和奖金
3. 查询名字中包含精的员工姓名和工资
4. 查询名字中第二个字是八的员工信息
5. 查询1号部门工资大于2000的员工信息
6. 查询2号部门或者工资低于1500的员工信息
7. 查询工资为3000，1500，5000的员工信息按照工资升序排序
8. 查询3号部门的工资总和
9. 查询每个部门工资大于1000的员工人数，按照人数升序排序
10. 查询每种工作中有领导的员工人数按照人数降序排序
11. 查询所有员工信息，按照部门编号升序排序，如果部门编号一致则工资降序
12. 查询有领导的员工，每个部门的编号和最高工资
13. 查询有领导的员工，按照工资升序排序，第3页的2条数据
14. 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第二页的2条数据
15. 查询和销售相关的工作的工资总和
16. 查询程序员人数 
17. 查询1号部门中有领导的员工中的最高工资
18. 查询2号部门的最高工资和最低工资 起别名
19. 查询1号部门里面名字中包含空字的员工姓名
20. 查询每个部门工资低于2000的人数

• 使用newdb3数据库

  use newdb3;

having关键字

• where后面只能写普通字段条件， having后面写聚合函数条件
• having要和group by 结合使用，并且写在group by的后面

1. 查询每个部门的平均工资，要求平均工资大于2000

   select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

   select deptno,avg(sal) a from emp group by deptno having a>2000;

2. 查询每种工作的人数，只查询人数大于1的

   select job,count(*) c from emp group by job having c>1;

3. 查询每个部门的工资总和，只查询有领导的员工，并且要求工资总和高于5400

   select deptno,sum(sal) s from emp where mgr is not null group by deptno having s>5400;

4. 查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的部门

   select deptno,avg(sal) a from emp

   where sal between 1000 and 3000 group by deptno having a>=2000;

5. 查询每种工作的人数要求人数大于1个,并且只查询1号部门和2号部门的员工, 按照人数降序排序

   select job,count(*) c from emp where deptno in(1,2) group by job having c>1 order by c desc;

各个关键字的顺序

select 字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数

子查询(嵌套查询)

1. 查询工资高于1号部门平均工资的员工信息

   select avg(sal) from emp where deptno=1;

   select * from emp where sal>2330;

   • 合并上面两条SQL

     select * from emp where sal>(select avg(sal) from emp where deptno=1);

2. 查询工资最高的员工信息

   select max(sal) from emp;

   select * from emp where sal=(select max(sal) from emp);

3. 查询工资高于2号部门最低工资的员工信息

   select * from emp where sal>(select min(sal) from emp where deptno=2);

4. 查询和孙悟空相同工作的其它员工信息

   select job from emp where ename=“孙悟空”;

   select * from emp where job=(select job from emp where ename=“孙悟空”) and ename!=“孙悟空”;

5. 查询拿最低工资员工的同事们的信息(同事指相同部门)

   • 得到最低工资

     select min(sal) from emp;

   • 得到最低工资的员工的部门编号

     select deptno from emp where sal=(select min(sal) from emp);

   • 得到上面部门编号的员工 并且排除掉最低工资的员工

     select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

6. 查询白骨精的部门信息(需要用到部门表dept)

   select deptno from emp where ename=“白骨精”;

   select * from dept where deptno=(select deptno from emp where ename=“白骨精”);

关联关系

• 创建表时,表和表之间存在的业务关系

• 有哪几种关系:

  • 一对一: 有AB两张表,A表中一条数据对应B表中的一条数据,同时B表中的一条数据也对应A表中的一条数据.
  • 一对多:有AB两张表,A表中一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的一条数据.
  • 多对多:有AB两张表,A表中一条数据对应B表中的多条数据,同时B表中的一条数据也对应A表中的多条数据.

• 表和表之间如何建立关系

  通过一个单独的字段指向另外一张表(或自己表)的主键, 这个字段称为外键

关联查询

• 同时查询多张存在关联关系的表时 使用的查询方式称为关联查询
• 关联查询的查询方式有三种: 1. 等值连接 2. 内连接 3. 外连接

关联查询之等值连接

• 格式: select * from A,B where A.x=B.x(关联关系) and A.age>30;

1. 查询每个员工的姓名和对应的部门名

   select ename,dname from emp e,dept d where e.deptno=d.deptno;

2. 查询2号部门员工的姓名,工资和部门地址

   select ename,sal,loc from emp e, dept d where e.deptno=d.deptno and e.deptno=2;

3. 查询孙悟空和刘备的部门信息

   select d.*

   from emp e, dept d

   where e.deptno=d.deptno

   and ename in(“孙悟空”,“刘备”);

关联查询之内连接

• 等值连接和内连接的作用是一样的, 查询到的是两个表的交集数据

• 格式: select * from A join B on A.x=B.x(关联关系) where A.age>30;

1. 查询每个员工的姓名和对应的部门名

   select ename,dname from emp e join dept d on e.deptno=d.deptno;

2. 查询1号部门有领导的员工姓名,工资,部门名和部门地址

   select ename,sal,dname,loc

   from emp e join dept d

   on e.deptno=d.deptno

   where e.deptno=1 and e.mgr is not null;

关联查询之外连接

• 查询的是一张表的全部和另外一张表的交集

• 格式: select * from A left/right join B on A.x=B.x(关联关系) where A.age>30;

  • 准备一个员工表中的交集以外的数据

    insert into emp (empno,ename) values(100,‘灭霸’);

  1. 查询所有员工的姓名和对应的部门名

     select ename,dname

     from emp e left join dept d

     on e.deptno=d.deptno;

  2. 查询所有部门的名称,地址和对应的员工姓名

     select dname,loc,ename

     from emp e right join dept d

     on e.deptno=d.deptno;

关联查询总结

• 如果查询的数据是两个表的交集数据使用等值连接或内连接(推荐)
• 如果查询的是一张表的全部数据和另外一张表的交集数据则使用外连接

JDBC

• Java DataBase Connectivity: Java数据库连接

• 学习JDBC主要学习的就是如何通过Java代码执行SQL语句

• JDBC是Sun公司提供的一套 JAVA语言用于和各种数据库软件进行操作的API(Application Programma Interface 应用程序编程接口)

• 为什么使用JDBC?

  如果没有JDBC接口, Java程序员每一种数据库都需要学习一套对应的方法,Sun公司为了避免出现这种情况,定义出了JDBC接口,JDBC里面是一堆的方法声明,各个数据库厂商根据方法声明写各自的实现类, 这样Java程序员就可以只学习JDBC里面的一套方法 就可以访问任何数据库,只要按照了JDBC的标准写的代码将来换了数据库 代码也不用改变.

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lskIlGWe-1614837239202)(C:\Users\PC\AppData\Roaming\Typora\typora-user-images\image-20201110141507020.png)]

• 如何使用JDBC连接数据库

  1. 创建maven工程

  2. 将MySQL驱动(实际上就是MySQL数据库厂商提供的实现类) 复制到pom.xml中

     <!-- 连接MySQL数据库的依赖 -->
     <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.15</version>
     </dependency>
     

  3. 创建Demo01.java 添加main方法

  4. 在main方法中添加以下代码:

     //1. 注册驱动 告诉编译器使用的数据库是什么
     Class.forName("com.mysql.cj.jdbc.Driver");
     //2. 获取数据库连接
     Connection conn =
             DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true","root","root");
     System.out.println("连接对象:"+conn);
     //3. 创建执行SQL语句的对象
     Statement s = conn.createStatement();
     //4. 执行SQL语句
     String sql = "create table jdbct1(id int,name varchar(10))";
     s.execute(sql);
     System.out.println("创建完成!");
     //5. 关闭资源
     conn.close();
     

Statement执行SQL语句的对象

• execute(sql); 可以执行任意SQL语句, 但是推荐执行DDL(数据库相关和表相关的SQL语句)
• executeUpdate(sql); 执行DML(包括增删改查)
• executeQuery(sql); 执行DQL(只包含查询)

晚课任务: 7-8点做题 8:00公布答案

1. 查询人数最多的工作名称及人数
2. 查询每种工作的平均工资取前三种
3. 查询每种工作的人数,只查询3个人以内的工作
4. 查询最高工资的部门有多少人
5. 查询工资高于2000的每个员工的姓名,工资和对应的部门名
6. 查询所有的部门名和对应的员工姓名和工资 
7. 查询平均工资最高的部门编号
8. 创建maven工程 08jdbc02  把MySQL依赖添加到pom.xml 
	Demo01.java  创建hero表  有id,name,type字段
	Demo02.java  插入 1,诸葛亮,法师    2,孙尚香,射手
	Demo03.java  查询hero表里面的英雄名和类型 在控制台输出

DBCP数据库连接池

• DataBaseConnectionPool

• 作用:将连接重用,从而提高执行效率

• 如何使用: 需要在工程的pom.xml中添加dbcp相关的jar包依赖

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9RgolruC-1614837239204)(C:\Users\PC\AppData\Roaming\Typora\typora-user-images\image-20201111094220989.png)]

实现注册功能

create table user(id int primary key auto_increment,username varchar(20),password varchar(20));

select count(*) from user where username=‘tom’ and password=‘123456’;

SQL注入

select count(*) from user where username=‘asdf’ and password=’’ or ‘1’=‘1’

• 往填写值得地方写进去了SQL语句,导致原有的SQL逻辑发生改变, 这个过程称为SQL注入

PreparedStatement预编译SQL执行对象

• 是如何解决SQL注入问题的?

  预编译SQL执行对象,将编译SQL语句的时间点从执行SQL语句 提前到创建执行SQL语句对象的时间, 在创建执行对象时进行编译 将SQL语句的业务逻辑锁死, 这样就不会被用户输入的内容所影响,从而解决了SQL注入问题

• 注入代码: ’ or ‘1’='1

• 使用预编译SQL执行对象 避免了拼接字符串, 提高了开发效率

• 如果SQL语句中没有变量 则使用statement 如果有变量则使用PreparedStatement

批量操作

• 可以将多次网络数据传输 合并成一次 从而提高执行效率

获取自增主键值

create table team(id int primary key auto_increment,name varchar(20));

create table player(id int primary key auto_increment,name varchar(20),team_id int);

元数据

• 数据库元数据: 保存了一些和数据库相关的信息
• 表元数据: 表相关的一些信息

代码介绍

• Demo01-Demo03 昨天晚课作业
• Demo04 数据库连接池
• Demo05 注册练习
• Demo06 登录 SQL注入 以及通过预编译SQL执行对象解决SQL问题
• Demo07 Statement批量操作
• Demo08 PreparedStatement 批量操作
• Demo09 分页查询练习
• Demo10 获取自增主键值
• Demo11 球队和球员练习
• Demo12 球队和球员练习 解决了球队重复问题
• Demo13 元数据

晚课作业:

1. 创建jdbc04 工程, 把MySQL和数据库连接池的依赖添加到工程的pom.xml里面
2. 创建Demo01 添加main方法在里面创建学生表student 字段: id自增 , name , age ,gender
3. 创建Demo02 添加main方法,运行时提示输入学生姓名,学生性别,学生年龄, 回车后把学生信息保存到student表中
4. 创建Demo03 添加main方法,通过批量操作往学生表中插入进去100条数据 内容不限
5. 创建Demo04 添加main方法, 实现分页查询功能 输入页数和条数 得到查询的内容

对象,将编译SQL语句的时间点从执行SQL语句 提前到创建执行SQL语句对象的时间, 在创建执行对象时进行编译 将SQL语句的业务逻辑锁死, 这样就不会被用户输入的内容所影响,从而解决了SQL注入问题

• 注入代码: ’ or ‘1’='1

• 使用预编译SQL执行对象 避免了拼接字符串, 提高了开发效率

• 如果SQL语句中没有变量 则使用statement 如果有变量则使用PreparedStatement

批量操作

• 可以将多次网络数据传输 合并成一次 从而提高执行效率

获取自增主键值

create table team(id int primary key auto_increment,name varchar(20));

create table player(id int primary key auto_increment,name varchar(20),team_id int);

元数据

• 数据库元数据: 保存了一些和数据库相关的信息
• 表元数据: 表相关的一些信息

代码介绍

• Demo01-Demo03 昨天晚课作业
• Demo04 数据库连接池
• Demo05 注册练习
• Demo06 登录 SQL注入 以及通过预编译SQL执行对象解决SQL问题
• Demo07 Statement批量操作
• Demo08 PreparedStatement 批量操作
• Demo09 分页查询练习
• Demo10 获取自增主键值
• Demo11 球队和球员练习
• Demo12 球队和球员练习 解决了球队重复问题
• Demo13 元数据

晚课作业:

1. 创建jdbc04 工程, 把MySQL和数据库连接池的依赖添加到工程的pom.xml里面
2. 创建Demo01 添加main方法在里面创建学生表student 字段: id自增 , name , age ,gender
3. 创建Demo02 添加main方法,运行时提示输入学生姓名,学生性别,学生年龄, 回车后把学生信息保存到student表中
4. 创建Demo03 添加main方法,通过批量操作往学生表中插入进去100条数据 内容不限
5. 创建Demo04 添加main方法, 实现分页查询功能 输入页数和条数 得到查询的内容