# 数据库
- 学习数据库阶段内容,主要学习的就是如何对数据进行增删改查操作
# DBMS
- DataBaseManagementSystem,数据库管理系统(数据库软件)
- 常见的DBMS:
- MySQL: Oracle公司产品 08年被Sun收购 09年Sun被Oracle , 原MySQL团队从Oracle离职又创建了MariaDB. 市占率第一
- Oracle: Oracle公司当家产品 市占率第二 闭源产品
- SqlServer: 微软公司产品 , 市占率第三 闭源产品 .net 编程语言+web服务软件+操作系统+数据库软件
- DB2: IBM公司产品 闭源产品
- SQLite: 轻量级数据库, 安装包只有几十k , 只有最基础的增删改查功能
### SQL
- Structured Query Language: 结构化查询语言.
- 通过这门语言让程序员和数据库软件进行交流
- 举例: insert into person values("刘德华",18);
### 和数据库软件建立连接
1. 通过命令行/终端 和数据库软件建立连接
- 首先检查MySQL/MariaDB服务是否开启: 在我的电脑/此电脑上右键->管理 参见下图
- 从开始菜单中找到MySQL/MariaDB->MySQL Client 打开客户端 , 输入自己的密码(如果用的是达内电脑尝试root或没密码直接回车) 显示Welcome ..... 说明建立好了连接
- 退出指令: exit
- 建立连接指令: mysql -uroot -p 回车后输入密码 再回车
3. 通过三方的可视化工具和数据库软件建立连接
### 数据库相关
- 在MySQL数据库软件中保存数据需要先建库再建表,然后再往表里面添加数据
1. 查询所有数据库
- 格式: show databases;
2. 创建数据库
- 默认字符集格式: create database 数据库名;
- 指定字符集格式: create database 数据库名 charset=utf8/gbk;
- 举例:
create database db1;
create database db2 charset=utf8;
create database db3 charset=gbk;
show databases;
3. 查询数据库信息
- 格式: show create database 数据库名;
- 举例:
show create database db1;
show create database db2;
show create database db3;
4. 删除数据库
- 格式: drop database 数据库名;
- 举例:
drop database db3;
drop database db2;
5. 使用数据库
- 在执行表相关和数据相关的SQL语句之前必须使用了某个数据库,
- 格式: use 数据库名;
- 举例:
use db1;
### 数据库相关练习题
1. 创建 mydb1和mydb2 数据库 字符集分别为utf8和gbk
create database mydb1 charset=utf8;
create database mydb2 charset=gbk;
2. 查询所有数据库检查是否创建成功
show databases;
3. 检查两个数据库的字符集是否正确
show create database mydb1;
show create database mydb2;
4. 先使用mydb2 再使用 mydb1
use mydb2;
use mydb1;
5. 删除两个数据库
drop database mydb1;
drop database mydb2;
### 表相关
- 执行表相关的SQL之前必须使用了某个数据库.
create database newdb1 charset=utf8;
use newdb1;
1. 创建表
- 格式: create table 名字(字段1名 类型,字段2名 类型,.....)charset=utf8/gbk;
- 举例:
create table person(name varchar(50),age int)charset=utf8;
create table student(name varchar(50),chinese int,math int,english int);
2. 查询所有表
- 格式: show tables;
3. 查询表信息
- 格式: show create table 表名;
- 举例:
show create table person;
4. 查询表字段
- 格式: desc 表名;
- 举例:
desc student;
5. 删除表
- 格式; drop table 表名;
- 举例:
drop table student;
6. 修改表名
- 格式: rename table 原名 to 新名;
- 举例:
rename table person to per;
### 表相关练习题
1. 创建数据库mydb3 字符集gbk 并使用
create database mydb3 charset=gbk;
use mydb3;
2. 创建t_hero英雄表, 有名字和年龄字段 默认字符集
create table t_hero(name varchar(50),age int);
3. 修改表名为hero
rename table t_hero to hero;
4. 查看表的字符集
show create table hero;
5. 查询表字段
desc hero;
6. 删除表
drop table hero;
7. 删除数据库
drop database mydb3;
### 表相关(续)
1. 添加表字段
create database newdb2;
use newdb2;
create table emp(name varchar(30));
- 最后面添加格式: alter table emp add 字段名 类型;
- 最前面添加格式: alter table emp add 字段名 类型 first;
- 某字段后面添加格式: alter table emp add 字段名 类型 after 字段名;
- 举例:
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;
alter table emp drop id;
3. 修改表字段
- 格式: alter table 表名 change 原名 新名 新类型;
- 举例:
alter table emp change age gender varchar(10);
### 表相关SQL语句回顾:
1. 创建 create table 表名(name varchar(20),age int)charset=utf8/gbk;
2. 查询所有表 show tables;
3. 查询表信息 show create table 表名;
4. 表字段: desc 表名;
5. 删除表: drop table 表名;
6. 修改表名: rename table 原名 to 新名;
7. 添加表字段; alter table 表名 add 字段名 类型 first/after xxx;
8. 删除表字段: alter table 表名 drop 字段名;
9. 修改表字段; alter table 表名 change 原名 新名 新类型;
### 表相关练习题:
1. 创建数据库mydb4 字符集utf8并使用
create database mydb4 charset=utf8;
use mydb4;
2. 创建teacher表 有名字字段
create table teacher(name varchar(20));
3. 添加表字段: 最后添加age 最前面添加id , age前面添加salary工资
alter table teacher add age int;
alter table teacher add id int first;
alter table teacher add salary int after name;
4. 删除age字段 alter table teacher drop age;
5. 修改表名为t rename table teacher to t;
6. 删除表 drop table t;
7. 删除数据库 drop database mydb4;
### 数据相关
create database mydb5 charset=utf8;
use mydb5;
create table person(name varchar(50),age int);
1. 插入数据
- 全表插入格式: insert into 表名 values(值1,值2);
- 指定字段插入格式: insert into 表名(字段1名,字段2名) values(值1,值2);
- 举例:
insert into person values("tom",18);
insert into person(name) values("jerry");
- 批量插入格式:
- 全表插入格式: insert into 表名 values(值1,值2),(值1,值2),(值1,值2);
- 指定字段插入格式: insert into 表名(字段1名,字段2名) values(值1,值2),(值1,值2);
- 举例:
insert into person values("lucy",20),("lily",21);
insert into person(name) values("lilei"),("hanmeimei");
- 中文问题:
insert into person values("刘德华",50);
- 执行上面SQL语句报错的话 执行以下指令
set names gbk;
2. 查询数据
- 格式: select 字段信息 from 表名 where 条件;
- 举例:
select name from person;
select name,age from person;
select * from person;
select * from person where age>20;
select age from person where name="tom";
3. 修改数据
- 格式: update 表名 set 字段名=值,字段名=值 where 条件;
- 举例:
update person set name='汤姆' where name="tom";
update person set name="杰瑞",age=15 where name="jerry";
update person set age=10 where age is null;
4. 删除数据
- 格式: delete from 表名 where 条件;
- 举例:
delete from person where name="汤姆";
delete from person where age>20;
delete from person;
### 数据相关SQL回顾
1. 插入数据: insert into 表名(字段1名,字段2名) values(值1,值2),(值1,值2);
2. 查询数据: select 字段信息 from 表名 where 条件;
3. 修改数据: update 表名 set 字段名=值,字段名=值 where 条件;
4. 删除数据; delete from 表名 where 条件;
### 综合练习题
1. 创建数据库day1db 字符集utf8并使用
create database day1db charset=utf8;
use day1db;
2. 创建t_hero表, 有name字段 字符集utf8
create table t_hero(name varchar(20))charset=utf8;
3. 修改表名为hero
rename table t_hero to hero;
4. 最后面添加价格字段money, 最前面添加id字段, name后面添加age字段
alter table hero add money int;
alter table hero add id int first;
alter table hero add age int after name;
5. 表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888
insert into hero values(1,'李白',50,6888),(2,'赵云',30,13888),(3,'刘备',25,6888);
6. 查询价格为6888的英雄名
select name from hero where money=6888;
7. 修改刘备年龄为52岁
update hero set age=52 where name='刘备';
8. 修改年龄小于等于50岁的价格为5000
update hero set money=5000 where age<=50;
9. 删除价格为5000的信息
delete from hero where money=5000;
10. 删除表, 删除数据库
drop table hero;
drop database day1db;
### 综合练习题2
1. 创建数据库newdb1, 字符集utf8并使用
2. 在数据库中创建员工表emp 字段:id,name,sal(工资),deptId(部门id) 字符集utf8
3. 创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8
4. 部门表插入以下数据: 1 神仙部 天庭 2 妖怪部 盘丝洞
5. 员工表插入一下数据: 1 悟空 5000 1 , 2 八戒 2000 1 , 3 蜘蛛精 8000 2 , 4 白骨精 9000 2
6. 查询工资6000以下的员工姓名和工资
7. 修改神仙部的名字为取经部
8. 给员工添加奖金comm字段
9. 修改员工表中部门id为1的 奖金为500
10. 把取经部的地址改成五台山
11. 修改奖金字段为性别gender字段 类型为varchar
12. 修改孙悟空和猪八戒性别为男
13. 删除没有性别的员工(null不能用= 要用is)
14. 删除性别字段
15. 删除表 和 删除数据库
### 常见错误提示:
1. You have an error in your SQL; 你的SQL语句中有个错 (检查SQL拼写)
2. database exists; 数据库已存在
3. Column count doesn't match value count 插入的值的数量和列(表字段)的数量不一致
### idea中配置数据库步骤 参考苍老师文档服务器
- 推荐使用三方的可视化工具, 像SQLYog
### 课程回顾:
1. 数据库相关SQL
- 查询所有 show databases;
- 创建 create database db1 charset=utf8/gbk;
- 查看数据库信息: show create database db1;
- 删除数据库: drop database db1;
- 使用: use db1;
2. 表相关SQL
- 创建表: create table t1(name varchar(20),age int) charset=utf8/gbk;
- 查询所有: show tables;
- 查询表信息: show create table t1;
- 表字段: desc t1;
- 删除表: drop table t1;
- 修改表名: rename table t1 to t2;
- 添加表字段: alter table t1 add gender varchar(5) first/after xxx;
- 删除表字段: alter table t1 drop gender;
- 修改表字段: alter table t1 change 原名 新名 新类型;
3. 数据相关SQL
- 插入: insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2);
- 查询: select name,age from t1 where 条件;
- 修改: update t1 set xxx=xxx,xxx=xxx where 条件;
- 删除: delete from t1 where 条件;
### 综合练习题2
1. 创建数据库newdb1, 字符集utf8并使用
create database newdb1 charset=utf8;
use newdb1;
2. 在数据库中创建员工表emp 字段:id,name,sal(工资),deptId(部门id) 字符集utf8
create table emp(id int,name varchar(50),sal int,deptId int)charset=utf8;
3. 创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8
create table dept(id int,name varchar(20),loc varchar(50))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. 修改奖金字段为性别gender字段 类型为varchar
alter table emp change comm gender varchar(5);
12. 修改孙悟空和猪八戒性别为男
update emp set gender='男' where deptId=1;
13. 删除没有性别的员工(null不能用= 要用is)
delete from emp where gender is null;
14. 删除性别字段
alter table emp drop gender;
15. 删除表 和 删除数据库
drop table emp;
drop table dept;
drop database newdb1;
### SQL分类
- DDL: 数据定义语言, 包括: 数据库向和表相关的SQL
- DML: 数据操作语言, 包括: 增删改查
- DQL: 数据查询语言, 只包括查询 select
- TCL: 事务控制语言, 包含和事务相关的内容
- DCL: 数据控制语言, 包含用户管理,权限分配相关SQL
### *.sql 批处理文件
- 把老师的emp.zip解压出来得到一个emp.sql文件, 把这个文件放到某个盘的根目录 比如 D盘根目录, 然后在命令行执行以下指令
- 格式: source 路径; 举例: source d:/emp.sql;
- 测试:
show databases; 检查里面是否多了一个empdb;
show tables; 会出现两个表 emp 和dept
select * from emp; 检查里面的数据
- 如果出现乱码执行 set names utf8;
### 数据类型
- 整数: int(m)和bigint(m) , bigint相当于Java中的long, m代表显示长度,m=5 存18 得到00018, 需要结合zerofill关键字使用
举例: create table t1(age int(5) zerofill);
insert into t1 values(18);
select * from t1;
- 浮点数: double(m,d) m代表总长度, d代表小数长度 m=5,d=3 23.123
举例: create table t2(price double(5,3));
insert into t2 values(54.321);
insert into t2 values(54.332211);
insert into t2 values(5433.2211); //会报错或出现99.999
- 字符串:
- char(m): 固定长度, m=5 存"abc" 占5个字符长度, 执行效率略高,当存储内容长度固定时使用,比如:性别 m最大值255
- varchar(m):可变长度, m=5 存"abc" 占3个字符长度,更节省存储空间,m最大值65535, 长度在255以内的建议使用
- text(m):可变长度, m最大值65535 , 建议长度大于255时使用
- 日期:
- date:保存年月日
- time:保存时分秒
- datetime:保存年月日时分秒,默认值null
- timestamp(时间戳举例1970年1月1日的毫秒数):保存年月日时分秒,默认值为当前系统时间
测试:
create table t3(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t3 values("2021-11-22","10:38:20",null,null);
insert into t3 values(null,null,"2021-11-22 10:38:20",null);
### 主键约束
- 主键: 表示数据唯一性的字段称为主键
- 约束: 创建表时,给表字段添加的限制条件
- 主键约束:限制主键的值 唯一且非空.
- 举例: create table t4(id int primary key,name varchar(20));
insert into t4 values(1,'aaa');
insert into t4 values(1,'bbb'); 报错 重复数据
insert into t4 values(null,'ccc'); 报错 不能为null
### 主键约束+自增
- 自增规则:从历史最大值+1
create table t5(id int primary key auto_increment,name varchar(20));
insert into t5 values(null,'aaa');
insert into t5 values(null,'bbb');
insert into t5 values(10,'ccc');
insert into t5 values(null,'ddd');
delete from t5 where id>=10;
insert into t5 values(null,'eee');
- truncate table t5; //把表删除 并重新创建 此时计数器才会清零
### 去重distinct
1. 查询员工表中有哪几种不同的工作?
select distinct job from emp;
2. 查询员工表中有哪几个不同的部门id?
select distinct dept_id from emp;
### is null 和 is not null
1. 查询没有上级领导的员工姓名
select name from emp where manager is null;
2. 查询有上级领导的员工姓名
select name from emp where manager is not null;
### and和or
- 多个条件同时满足时使用and
- 多个条件满足一个就可以时 使用or
1. 查询1号部门工资高于2000的员工信息
select * from emp where dept_id=1 and sal>2000;
2. 查询3号部门的员工或者工资等于5000的员工信息
select * from emp where dept_id=3 or sal=5000;
3. 查询孙悟空和猪八戒的员工信息
select * from emp where name="孙悟空" or name="猪八戒";
### 比较运算符 > < >= <= = !=和<>
1. 查询不是程序员的姓名和工资
select name,sal from emp where job!="程序员";
select name,sal from emp where job<>"程序员";
### 两者之间between x and y 包含x和y
1. 查询工资在2000到3000之间的员工信息
select * from emp where sal between 2000 and 3000;
select * from emp where sal not between 2000 and 3000; 范围取反
### in关键字
- 当查询某个字段的值为多个值的时候使用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. 查询孙悟空,猪八戒和白骨精的信息
select * from emp where name in('孙悟空','猪八戒','白骨精');
### 综合练习题
1. 查询1号部门有哪几种不同的工作?
select distinct job from emp where dept_id=1;
2. 查询1号部门中有上级领导的员工信息
select * from emp where dept_id=1 and manager is not null;
3. 查询工作是程序员,销售和人事的员工信息
select * from emp where job in('程序员','销售','人事');
4. 查询有奖金的员工信息
select * from emp where comm>0;
### 模糊查询like
- %: 代表0或多个未知字符
- _:代表1个未知字符
- 举例:
以x开头 x%
以x结尾 %x
包含x %x%
第二个字符是x _x%
以x开头以y结尾 x%y
第二个是x倒数第三个是y _x%y__
1. 查询姓孙员工信息
select * from emp where name like "孙%";
2. 查询名字以精结尾的员工姓名
select name from emp where name like "%精";
3. 查询工作第二个字是售的员工姓名和工作
select name,job from emp where job like "_售%";
5. 查询名字中包含僧并且工资大于2000的员工姓名和工资
select name,sal from emp where name like "%僧%" and sal>2000;
### 排序 order by
- 格式: order by 字段名 asc(默认升序)/desc降序;
1. 查询员工姓名和工资,按照工资升序
select name,sal from emp order by sal;
2. 查询员工姓名和工资,按照工资降序
select name,sal from emp order by sal desc;
3. 查询1号部门的姓名和工资并且按照工资降序排序
select name,sal from emp where dept_id=1 order by sal desc;
4. 查询每个员工的姓名,工资和部门id按照部门id升序排序,如果部门id相同则按照工资降序排序
select name,sal,dept_id from emp order by dept_id,sal desc;
### 分页查询 limit
- 格式: limit 跳过的条数,请求的条数(每页的条数)
- 举例: 跳过的条数=(请求页数-1)*每页条数
第一页的5条数据 limit 0,5
第一页的10条 limit 0,10
第2页的10条 limit 10,10
第5页的10条 limit 40,10
第8页的9条 limit 63,9
第15页的3条 limit 42,3
1. 查询工资最高的前三个人的信息
select * from emp order by sal desc limit 0,3;
2. 查询员工的id和名字,请求第二页的3条数据
select id,name from emp limit 3,3;
3. 查询工资最低的员工信息
select * from emp order by sal limit 0,1;
### 综合练习题
1. 查询员工表中3号部门工资高于1500的员工信息
select * from emp where dept_id=3 and sal>1500;
2. 查询2号部门员工或者没有领导的员工信息
select * from emp where dept_id=2 or manager is null;
3. 查询有领导的员工姓名,工资按照工资降序排序
select name,sal from emp where manager is not null order by sal desc;
4. 查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序
select name,hiredate from emp where dept_id in(2,3) order by hiredate desc;
5. 查询名字中包含僧和包含精的员工姓名
select name from emp where name like "%僧%" or name like "%精%";
6. 查询工资高于2000的工作有哪几种?
select distinct job from emp where sal>2000;
7. 查询工资升序第4页的2条数据
select * from emp order by sal limit 6,2;
### 别名
select name from emp;
select name as "名字" from emp;
select name "名字" from emp;
select name 名字 from emp;
### 数值计算
1. 查询每个员工的姓名,工资和年终奖(3个月的工资)
select name,sal,3*sal 年终奖 from emp;
2. 给3号部门的员工每人涨薪5块钱
update emp set sal=sal+5 where dept_id=3;
### 聚合函数
- 可以对查询的多条数据进行统计查询, 统计方式包括: 求平均值,最大值,最小值,求和,计数
1. 平均值avg(字段名)
- 查询2号部门的平均工资
select avg(sal) from emp where dept_id=2;
- 查询销售的平均工资
select avg(sal) from emp where job="销售";
2. 最大值max(字段名)
- 查询1号部门的最高工资
select max(sal) from emp where dept_id=1;
3. 最小值min(字段名)
- 查询1号部门的最低工资
select min(sal) from emp where dept_id=1;
4. 求和sum(字段名)
- 查询1号部门的工资总和
select sum(sal) from emp where dept_id=1;
5. 计数count(*)
- 查询工资高于2000的员工人数
select count(*) from emp where sal>2000;
- 查询2号部门的平均工资,最高工资,最低工资,工资总和,人数
select avg(sal) 平均工资,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,count(*) 人数 from emp where dept_id=2;
### 综合练习
1. 查询1号部门名字中包含僧的员工信息
select * from emp where dept_id=1 and name like "%僧%";
2. 查询2号和3号部门中工资高于1500的员工人数
select count(*) from emp where dept_id in(2,3) and sal>1500;
3. 查询名字里面包含精的最高工资
select max(sal) from emp where name like "%精%";
4. 查询程序员的平均工资
select avg(sal) from emp where job="程序员";
5. 查询销售的最低工资
select min(sal) from emp where job="销售";
6. 查询有领导的员工人数
select count(*) from emp where manager is not null;
7. 查询3号部门的人数和平均工资,起别名
select count(*) 人数,avg(sal) 平均工资 from emp where dept_id=3;
### 分组查询group by
- 将某个字段相同值的数据划分为一组, 然后以组为单位进行统计查询
1. 查询每个部门的平均工资
select dept_id,avg(sal) from emp group by dept_id;
2. 查询每种工作的平均工资
select job,avg(sal) from emp group by job;
3. 查询每个部门的最高工资
select dept_id,max(sal) from emp group by dept_id;
4. 查询每种工作的人数
select job,count(*) from emp group by job;
5. 查询每个部门工资高于2000的人数
select dept_id,count(*) from emp where sal>2000 group by dept_id;
6. 查询每个部门有领导的员工的人数
select dept_id,count(*) from emp where manager is not null group by dept_id;
### having关键字
- where后面只能写普通字段的条件,不能写聚合函数函数.
- having关键字 作用和where类似都是用来添加条件的, 但是having后面专门写聚合函数条件,而且having要和group by分组查询结合使用, 写在分组关键字的后面
1. 查询每个部门的平均工资要求平均工资大于2000
select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000 ;
别名用法
select dept_id,avg(sal) a from emp group by dept_id having a>2000 ;
2. 查询每种工作的人数,只查询人数大于1 的
select job,count(*) c from emp group by job having c>1;
3. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;
5. 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的
select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;
### 各个关键字的顺序
- select * from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 desc limit 跳过条数,请求条数;
### 晚课练习题:
1. 查询工资大于等于3000的员工姓名和工资
2. 查询1号部门的员工姓名和工作
3. 查询不是程序员的员工姓名和工作(两种写法)
4. 查询奖金等于300的员工姓名,工资和工作
5. 查询1号部门工资大于2000的员工信息
6. 查询3号部门或工资等于5000的员工信息
7. 查询出CEO和项目经理的名字
8. 查询工资为3000,1500和5000的员工信息
9. 查询工资不等于3000,1500和5000的员工信息
10. 查询工资在1000到2000之间的员工信息
11. 查询工资在1000到2000以外的员工信息
12. 查询有领导的员工姓名和领导id
13. 查询没有领导的员工姓名和领导id
14. 查询员工表中出现了哪几种不同的工作
15. 查询员工表中出现了那几个部门的id
16. 查询姓孙的员工姓名
17. 查询名字最后一个字是精的员工信息
18. 查询工作中包含销售的员工信息
19. 查询工作中第二个字是售的员工信息
20. 查询名字中包含僧的员工并且工资高于2000的员工信息
21. 查询1号和2号部门中工作以市开头的员工信息
22. 查询所有员工的姓名和工资 按照工资升序排序
23. 查询所有员工的姓名和工资 按照工资降序排序
24. 查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序
25. 查询员工表中3号部门工资高于1500的员工信息
26. 查询2号部门员工或者没有领导的员工信息
27. 查询有领导的员工姓名,工资按照工资降序排序
28. 查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序
29. 查询名字中包含僧和包含精的员工姓名
30. 查询工资高于2000的工作有哪几种?
31. 查询工资最高的前三个员工
32. 查询员工表按照id排序, 第2页的5条数据
33. 查询员工表按照id排序, 第3页的4条数据
34. 查询3号部门工资最低的员工姓名和工资
35. 查询工作不是人事的员工中工资降序第二页的3条数据
36. 查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)
37. 给3号部门所有员工涨薪5块钱
38. 查询没有领导的员工和3号部门的员工,工资降序取前三条
39. 查询2号部门的最高工资
40. 查询有领导的员工中工资在1000到2000之间的人数
41. 查询3号部门的工资总和
42. 查询程序员和销售的总人数
43. 查询1号部门有领导的员工的平均工资
44. 查询1号部门的最低工资和最高工资
45. 查询和销售相关的工作人数
46. 查询工资不是1500和3000的员工人数
47. 查询1号部门出现了哪几种工作
48. 查询每个部门的平均工资
49. 查询每种工作的人数
50. 查询每个部门的最高工资
51. 查询每种工作的最低工资
52. 查询每个部门的工资总和
53. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
### 课程回顾
- 主键约束+自增 primary key auto_increment
- 去重 distinct
- is null 和 is not null
- and 和 or
- 比较运算符 > < >= <= = !=和<>
- 两者之间 between x and y
- in
- 模糊查询 like _一个未知 %0或多个未知字符
- 排序 order by 字段名 asc(升序默认)/desc降序
- 分页 limit 跳过的条数,请求的条数
- 别名 select name 名字 from emp;
- 数值计算 select sal*5 from emp;
- 聚合函数: 平均值avg() 最大值max 最小值min 求和sum 计数count(*)
- 分组查询: group by 字段名
- having: 用于写聚合函数条件
- 各个关键字的顺序: select * from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数;
### 子查询(嵌套查询)
use empdb;
1. 查询工资大于2号部门平均工资的员工信息
select avg(sal) from emp where dept_id=2;
select * from emp where sal>(select avg(sal) from emp where dept_id=2);
2. 查询工资高于程序员最高工资的员工信息
select max(sal) from emp where job="程序员";
select * from emp where sal>(select max(sal) from emp where job="程序员");
3. 查询工资最高的员工信息
select * from emp where sal=(select max(sal) from emp);
4. 查询和孙悟空相同工作的员工信息
select * from emp where job=(select job from emp where name='孙悟空') and name!='孙悟空';
5. 查询拿最低工资员工的同事们的信息(同事指同一部门)
select min(sal) from emp;
select dept_id from emp where sal=(select min(sal) from emp);
select * from emp where dept_id=(select dept_id from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
### 关联关系
- 指一个项目中创建的表和表之间存在的业务关系
- 有哪几种关系?
- 一对一:有AB两张表,A表中一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条.
- 一对多:有AB两张表,A表中一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条.
- 多对多: 有AB两张表,A表中一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条.
### 表和表之间如何建立关系
- 一对一: 在AB 任意一张表里面添加一个建立关系的字段 指向另外一张表的主键
- 一对多: 在一对多的两张表中, 在"多"的表里面添加建立关系的字段 指向另外一张表的主键
- 多对多: 创建一个单独的关系表, 表里面有两个字段指向另外两个表的主键
- 多对多举例: 苍老师: 小红,小花,小绿 传奇哥: 小花,小绿
create table t(id int primary key auto_increment,name varchar(20));
create table s(id int primary key auto_increment,name varchar(20));
create table t_s(tid int ,sid int);
insert into t values(null,'苍老师'),(null,'传奇哥');
insert into s values(null,'小红'),(null,'小花'),(null,'小绿');
insert into t_s values(1,1),(1,2),(1,3),(2,2),(2,3);
老师表:t
学生表:s
关系表:t_s
1. 查询每个老师名字和对应的学生名字
select t.name,s.name
from t join t_s ts on t.id=ts.tid join s on s.id=ts.sid;
2. 查询苍老师的学生姓名
select s.name
from t join t_s ts on t.id=ts.tid join s on s.id=ts.sid where t.name="苍老师";
### 关联查询
- 查询存在关联关系的表的查询方式称为关联查询
- 关联查询的方式包括: 等值连接, 内连接, 外连接
### 等值连接
- 格式: select * from A,B where 关联关系 and 其它条件;
1. 查询工资高于2000的员工的姓名和对应的部门名
select e.name,d.name
from emp e,dept d where e.dept_id=d.id and sal>2000;
2. 查询程序员的姓名,工资,部门名,部门地点
select e.name,sal,d.name,loc
from emp e,dept d where e.dept_id=d.id and job='程序员';
### 内连接
- 等值连接和内连接查询到的是一样的数据, 推荐使用内连接
- 格式: select * from A join B on 关联关系 where 其它条件;
1. 查询工资高于2000的员工的姓名和对应的部门名
select e.name,d.name
from emp e join dept d on e.dept_id=d.id where sal>2000;
2. 查询程序员的姓名,工资,部门名,部门地点
select e.name,sal,d.name,loc
from emp e join dept d on e.dept_id=d.id where job='程序员';
### 外连接
- 等值连接和内连接查询到的是两个表的交集数据
- 外连接查询到的是一张表的全部和另外一张表的交集
- 格式: select * from A left/right join B on 关联关系 where 其它条件;
insert into emp(name) values("灭霸");
1. 查询所有员工姓名和对应的部门名
select e.name,d.name
from emp e left join dept d on e.dept_id=d.id;
2. 查询所有部门的名字,地址, 和对应的员工姓名,工资.
select d.name,loc,e.name,sal
from emp e right join dept d on e.dept_id=d.id;
### 关联查询总结
1. 如果查询的数据是两个表的交集数据,使用等值连接或内连接(推荐)
2. 如果查询的是一张表的全部和另外一张表的交集则使用外连接.
### JDBC
- Java DataBase Connectivity: Java数据库连接
- 学习JDBC主要学习的就是如何通过Java语言和数据库软件进行连接并执行SQL语句.
- JDBC是Sun公司提供的一套用于Java语言和数据库软件进行连接的API (Application Programma Interface)
- 为什么Sun公司定义JDBC系列接口?
Sun公司为了避免Java程序员,每一种数据库软件都学习一套全新的方法,通过JDBC接口将方法名定义好, 让各个数据库厂商根据此接口中方法名写各自的实现类(驱动),这样Java程序员只需要掌握JDBC接口中方法的调用即可访问任何数据库软件.
- 如何通过JDBC连接MySQL
1. 创建Maven工程
2. 从苍老师文档服务器中复制以下MySQL驱动的依赖
```
<!-- 连接MySQL数据库的依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
```
3. 刷新maven
4. 检查工程目录中 external Libraries 里面 是否出现了 mysql相关的内容
5. 添加Demo01.java 并在main方法中添加以下代码
```
//1. 获取数据库连接 导包java.sql 异常抛出 参数:数据库连接地址,用户名,密码
Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai",
"root","root");
System.out.println("连接对象:"+conn);
//2. 创建执行SQL语句的对象
Statement s = conn.createStatement();
//3. 执行SQL语句 execute执行
s.execute("create table jdbct1(name varchar(20))");
//4. 关闭连接
conn.close();
System.out.println("创建完成!");
```
### Statement执行SQL语句的对象
- execute(sql); 此方法可以执行任意SQL语句,推荐执行DDL(数据库相关和表相关的SQL语句)
- int row = executeUpdate(sql); 此方法执行增删改相关的SQL语句 ,方法返回值是一个整数 表示生效的行数
- ResultSet rs = executeQuery(sql); 此方法执行查询相关的SQL语句 ,方法的返回值为结果集对象, 里面装着查询回来的所有数据
### 表设计面试题： 晚课7:30公布答案
2021年过年时小明在这些天都收到了许多亲戚\朋友还有同事的红包,也发出了一些红包,有的是微信,有的是支付宝也有现金,请参考下面的题目帮小明设计表格保存红包的信息
(至少包含一张流水表) 先列出需要保存的数据有哪几种
1. 统计2021年2月15号到现在的所有红包收益
2. 查询2021年2月15号到现在 金额大于100 所有女性亲戚的名字和金额
3. 查询三个平台(微信,支付宝,现金)分别收入的红包金额
DBCP
- DataBaseConnectionPool: 数据库连接池
- 作用: 可以将数据库连接重用,避免频繁开关连接导致的资源浪费
- 如何使用数据库连接池?
- 从苍老师文档服务器中找到连接池的依赖
注册功能:
- 创建user表
use empdb;
create table user(id int primary key auto_increment,username varchar(20),password varchar(20),nick varchar(20));
- 登录的SQL语句:
select count(*) from user where username='tom' and password='123456';
SQL注入
select count(*) from user
where username='abcd' and password='' or '1'='1'
- 什么是SQL注入?
用户往传值的地方传递进来了SQL语句导致原有SQL语句的逻辑发生改变,从而达到一些非法目的.这个过程叫做SQL注入.
PreparedStatement
- 带有预编译效果的执行SQL语句的对象
- 通过此对象可以解决SQL注入的问题:
将编译SQL语句的时间点从执行时提前到了创建时, 在创建PreparedStatement对象时将SQL语句进行编译,此时用户输入的内容还没有放到SQL语句里面, 这时编译的好处是将SQL语句业务逻辑锁死, 之后再将用户输入的内容添加进去,这样原有SQL语句的逻辑就不会被用户输入的内容改动,从而避免了SQL注入的问题.
版权声明：本文为m0_68517939原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。