[大数据]数据库基础

数据库是用来做什么的
用来存储数据，管理数据
数据库端口号：3306
java连接数据库用gdbc

SQL语句

• 查询数据库selsct
• 创建数据库create 为防止中文乱码，设置成utf8
• 删除数据库delete,tuncate
• 查询所有表 show tables;
• 创建表create table a(字段)；一个表里最多个有个字段：无上限，二十多个字段好操作 字段：需要描述字段的名称，类型，长度
• 修改表alter table a;
• 删除表drop table;
• 查询所有记录：select* from a; （表里的记录最多可以达到千万，亿条，无上限，单表保证性能最多八百万到一千万）
• 查询某一字段：select 字段名称 from 表名；
• 插入数据：insert into a values( ,’ '," ");(表里有几个字段，就有几个values) 单引号，双引号里面的是字符串，sql语句对单引号双引号不敏感
• 修改数据：update 表名 set 字段名称=字段的值；
• 删除记录：delete from 表名；（删除了表中所有的记录）
• 伪删除：update from 历史记录；（改字段的值和名字）

约束

• 关键字约束：primary Key
• 外关键字约束: Foreign Key
• 非空约束：notnull 不能为null
• 唯一约束：Unique constraint 不能重复
• 检查约束: check constraint
• 缺省约束: default constraint
• 自动递增：auto_increment(+1递增)

函数
#查询部门表中部门名称
#select 部门名称 from 表名
select * from dept
select dname from dept #查询时使用字段代替了*
select dname,loc from dept #查询多个字段的值时用逗号隔开
#基本函数
#upper(a)把a的值变大写，lower(ABC)把a的值变小写
select dname,upper(dname),lower(‘ABC’),lower(dname) from dept

#length(a)把a的值求长度，一个字母/数字长度为1，一个汉字长度为3（utf8）
select ename,length(ename),job,length(job) from emp

#substr(a,b,c)-a时字段名b是截取的开始位置c是截取长度
select ename,substr(ename,2),substr(ename,2,3) from emp

#concat(a,b,c)-a是字段名b是想要拼接的内容c是想要拼接的内容
select ename,concat(ename,123,‘abc’) from emp

#replace(a,b,c)-a是字段名b是要被替换字符c是新数据
select dname,replace(dnam,‘o’,‘666’) from dept

#ifnull(a,b)-a是字段名b是要把null替换成值
select comm,ifnull(comm,1000) from emp

#对小数的处理：round四舍五入取整 ceil 向上取整 floor向下取整
select comm,round(comm),floor(comm) from emp

#对日期的处理：now获取当前系统时间：year获取年，month获取月，day获取天
select now(),year(now()),month(now()),day(now())

#对日期的处理：hour获取时，minute获取分，second获取秒
select now(),hour(now()),minute(now()),second(now())

条件查询

distinct把数据去重：
select distinct loc from
where用来引导判断条件
#eg1:查询部门编辑号=40的数据： select * from dept where depton=40 ; selsct from dept where depton=40000;
#eg2:查询部门编号>10的数据： select * from dept where depton>10; select * from dept where 1=1 #条件永远成立
#eg3:查询部门编号>10并且地址在二区的数据 ： select * from dept where deptno>10 and loc=‘二区’
#eg4:查询部门编号>10或者地址在二区的数据： select * from dept where deptno>10 or loc=‘二区’
#eg5:查询部门编号等于10的或者等于20的或者等于30的数据： select * from dept where depton=10 or depton = 20 or depton=30
同时的需求，被简化–in子句： select * from dept where depton in(10,30) #查询depton=10 or depton=30d的 select * feom dept where depton not in(10,20,30)
like模糊查询 %是通配符，通配0~n个字符
#eg1:查询按照员工姓名以l开头的员工信息： select * from emp where ename like’l%’ #以l开头 (中间包含o:’%o%’ 以a结尾：‘a%’ 这两种写法会使索引失效)
null的处理
#eg1:查询没有奖金的员工信息： select * from emp where comm is null
#eg2:查询有奖金的员工信息： select * from emp where comm is not null
between…and在区间范围内的数据
#eg1:查询工资范围在5000~10000内的员工信息 select * from emp where sal 5000 and sal<10000 #(5000,10000) select * from emp where sal between 5000 and 10000 #[5000,10000]

聚合函数
常见的聚合函数：count() max() min() avg() sum()
#聚合函数：把一系列的所有值聚集在一起
selsct sal from emp#查询员工工资
select max(sal) from emp#查询最高工资
select min(sal) from emp#查询最低工资
#sum查询工资总和，avg平均工资
select sum(sal),avg(sal) from emp
#count()统计总记录数
select count(sal) from emp
select count(comm) from emp #不统计null元素
select count(*) from emp #低效
select count(1) from emp #高效

分组
就是把数据按照一些维度分成组，然后再把这一组数据继续分析

#1,分组:group by,把数据按照维度分组,后,数据分析
#口诀:
#什么时候要分组?查询时出现了混合列
#按照什么分组?按照非聚合列分组
#eg1:统计每个岗位的平均薪资
select job,avg(sal) from emp group by, job
#eg2:统计每个部门的员工人数
select deptno,count(1) from emp group by, deptno
#eg3:统计每年入职的人数
select year(hiredate),count(1)from emp group by,year(hiredate)
#eg4:统计每个部门的最高薪
select deptno,max(sal) from emp group by, deptno

#2.分组后过滤having
#eg1:统计每个部门的最高薪,只要>10000的记录
select max(sal) a,deptno from emp group by, deptno #按照非聚合列分组(省去as a)
having a>10000 #group by后的过滤必须用having
#eg2:统计每年入职的人数,只要人数>1的记录
select count(1),year(hiredate) a from emp
#where count(1)>1
#where里不能用别名,不能出现聚合函数,比having高效group by,a # 按照非聚合列a分组
having count(1)>1 # 按照人数b过滤
#having和where能互换吗?效率谁高?

事务
Transaction,保证多条操作要么全成功,要么全失败.（eg:转账）
事务的四大特性:（ACID）
1,原子性: 把多条SQL,看做是一个原子,密不可分,要么全成功,要么全失败.
2,隔离性: 数据库为了保证性能也支持高并发,但是有安全隐患.保证多个操作之间是隔离的
3,持久性: 对数据库的操作是持久生效影响的
4,一致性: 保证数据在多个系统中是一致的
隔离级别: 从上至下,安全性越来越好,但是性能越来越差
1, 读未提交: 性能最好,但是安全性最差
2, 读已提交: 性能较差,安全性较高,Oracle数据库的默认隔离级别
3, 可重复读: 性能较差,安全性较高,MySQL数据库的默认隔离级别
4, 串行化: 性能太差

MySQL数据库会为每条SQL提供事务管理.每条SQL执行前MySQL会自动开启事务,SQL执行结束MySQL会自动结束事务.
如果想要自己管理事务: 开启事务/结束事务(commit/rollback)

手动管理事务CUD:模拟向c表中插入数据
START TRANSACTION; #开启事务
INSERT INTO c VALUES(NULL,‘654321’);
INSERT INTO c VALUES(NULL,‘123456’);
COMMIT; #结束事务

字段约束
#字段约束:非空约束/唯一约束/主键约束/默认约束…
#1.默认约束:给字段使用default添加默认值
create table e(
id int primary key auto_increment,
sex varchar(10) default ‘男’ #默认约束,设置默认值
)
#2.检查约束:给字段使用check添加合法性的检查
creeate table f(
id int primary key auto_increment,
age int,
check(age<100)#检查约束,age录入不合法数据时无法保存
)
#3.外键约束:防止了冗余的数据,通过外键来描述两张表的关系
#特点是:当子表中添加数据时,子表的主键值 必须 取自主表!
#当主表删除数据时,子表没有相关的记录
create table tb_user(
id int primary key auto_increment,
name varchar(20),
password varchar(20)
)
create table tb_user_addr(
user_id int primary key,
address varchar(100) ,
#描述和tb_user表的关系,外键
#语法:foreign key(本表的主键名) foreign key对方表名(对方表的主键)
foreign key(user_id) references tb_user(id) #创建外键
)

数据库优化
1.创建索引：create index ename_index on emp(ename)
单值索引: 一个索引只包含一个字段
唯一索引: 一个索引只包含一个字段,但字段的值不能重复
复合索引: 一个索引可以包含着多个字段
使用步骤:
创建索引(经常按照指定字段查询) + 使用索引
#1.索引:好处是:提高查询效率 坏处是:索引需要单独的一张表
#1.1 查看索引:show index from 表名
SHOW INDEX FROM emp #主键自带索引
#1.2 创建单值索引:一个索引只包含一个字段
CREATE INDEX ename_index ON emp(ename)
#1.3 创建唯一索引:一个索引只包含一个字段,索引列值不能重复
#create unique index uni_index on emp(job) #失败,因为job的值有重复的
CREATE UNIQUE INDEX uni_index ON emp(sal)
#1.4 创建复合索引:一个索引包含着多个字段,遵循最左特性
CREATE INDEX fuhe_index ON emp(job,hiredate,comm)
#1.5 使用索引:背后的故事,按照索引列去查
SELECT * FROM emp WHERE ename=‘jack’ #使用单值索引
SELECT * FROM emp WHERE sal=10000 #使用唯一索引
#使用复合索引,遵循最左特性
EXPLAIN SELECT * FROM emp WHERE job=‘总监’ #生效
EXPLAIN SELECT * FROM emp WHERE job='总监’AND hiredate=‘2019-1-1’ #生效
EXPLAIN SELECT * FROM emp WHERE hiredate=‘2019-1-1’ #失效
EXPLAIN SELECT * FROM emp WHERE hiredate='2019-1-1’AND comm=100 #失效
EXPLAIN SELECT * FROM emp WHERE hiredate=‘2019-1-1’ AND job=‘总监’ #生效
#1.6 查询SQL的执行计划/性能(看看用没用索引)
EXPLAIN #possible_keys用到的索引
SELECT * FROM emp WHERE ename=‘jack’
EXPLAIN
SELECT * FROM emp WHERE sal=10000
#1.7 删除索引
ALTER TABLE emp DROP INDEX fuhe_index
2.创建视图(存结果)：create view name_view as select * from emp where ename like ‘%a%’
和索引一样,都是对数据库优化的有效方案.
特点: 可以把视图当做表来使用；视图里存的数据是 SQL查询到的结果 ；SQL无法优化,要合理的使用视图
#2.视图:缓存了SQL语句的执行结果,当做表来用
#好处是:
#1.简化了查询的SQL(相同的SQL需求不必再写SQL了,直接查视图)
#2.视图可以被共享,视图屏蔽了真实业务表的复杂性
#坏处是:视图一旦创建,SQL无法被优化
#2.1 创建视图
#语法:create view 视图名 as 查询的SQL语句
CREATE VIEW name_view AS
SELECT * FROM emp WHERE ename LIKE ‘%a%’
#2.2 使用视图
SELECT * FROM name_view
3.多表联查:笛卡尔积，连接查询，子查询
准备数据：
CREATE TABLE courses
(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL,
PRIMARY KEY (cno)
)

CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno)
);
CREATE TABLE students(
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5),
PRIMARY KEY (sno)
);
CREATE TABLE teachers(
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10),
PRIMARY KEY (tno)
)

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,‘曾华’ ,‘男’ ,‘1977-09-01’,95033);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,‘匡明’ ,‘男’ ,‘1975-10-02’,95031);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,‘王丽’ ,‘女’ ,‘1976-01-23’,95033);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,‘李军’ ,‘男’ ,‘1976-02-20’,95033);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,‘王芳’ ,‘女’ ,‘1975-02-10’,95031);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,‘陆君’ ,‘男’ ,‘1974-06-03’,95031);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,‘易天’,‘男’,‘1958-12-02’,‘副教授’,‘计算机系’);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,‘王旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,‘李萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,‘陈冰’,‘女’,‘1977-08-14’,‘助教’,‘电子工程系’);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘3-105’ ,‘计算机导论’,825);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘3-245’ ,‘操作系统’ ,804);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘6-166’ ,‘模拟电路’ ,856);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘6-106’ ,‘概率论’ ,831);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES (‘9-888’ ,‘高等数学’ ,831);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,‘3-245’,86);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,‘3-245’,75);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,‘3-245’,68);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,‘3-105’,92);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,‘3-105’,88);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,‘3-105’,76);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,‘3-105’,64);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,‘3-105’,91);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,‘3-105’,78);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,‘6-166’,85);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,‘6-106’,79);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,‘6-166’,81);

#多表联查:
#方式1:笛卡尔积,通过逗号连接表名
#eg1:查询部门编号是1的部门信息和员工信息
SELECT * FROM dept,emp
#表名.字段名=表名.字段名
WHERE dept.deptno=emp.deptno#描述两个表的关系
AND dept.deptno=1#查deptno=1的数据
#eg2:查询员工姓名叫jack的部门信息
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno #描述了两张表的关系
AND emp.ename=‘jack’ #业务条件
#eg3:查询岗位是总监所在的部门信息
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了两张表的关系
AND emp.job=‘总监’ #业务条件
#方式2:连接查询,分为三种:
#内连接inner join:取两张表的交集
#左连接left join:取左表的所有和右表满足条件的
#右连接right join:取右表的所有和左表满足条件的
#eg1:列出java开发部 部门下的所有员工的信息
#笛卡尔积方式
SELECT emp.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了两张表的关系
AND dept.dname=‘java开发部’ #业务条件
#连接查询方式
SELECT emp.* FROM dept JOIN emp
ON dept.deptno=emp.deptno#描述了两张表的关系
WHERE dept.dname=‘java开发部’ #业务条件
#eg2:查询岗位是总监所在的部门信息
SELECT dept.* FROM dept JOIN emp
ON dept.deptno=emp.deptno#描述了两张表的关系
WHERE emp.job=‘总监’ #业务条件
#eg3:查询员工姓名叫jack的部门信息
SELECT * FROM emp JOIN dept #只写join是inner join的简写形式
ON emp.deptno=dept.deptno#描述了两张表的关系
WHERE emp.ename=‘jack’ #业务条件
#三种连接查询:inner join/left join/right join
SELECT * FROM dept INNER JOIN emp #可以简写成join
#取两张表都满足条件后的交集
ON emp.deptno=dept.deptno

SELECT * FROM dept LEFT JOIN emp
#取左表的所有,右边满足条件的取到不满足条件的用null填充
ON dept.deptno=emp.deptno

SELECT * FROM emp RIGHT JOIN dept
#取右表的所有,左边满足条件的取到不满足条件的用null填充
ON dept.deptno=emp.deptno

#综合练习1:查询陈冰老师能讲解的课程的名称(teachers/courses)
#笛卡尔积方式
SELECT courses.cname FROM teachers,courses
WHERE teachers.tno=courses.tno#描述两张表的关系
AND teachers.tname=‘陈冰’ #业务条件
#连接查询方式
SELECT courses.cname FROM teachers INNER JOIN courses
ON teachers.tno=courses.tno#描述两张表的关系
WHERE teachers.tname=‘陈冰’ #业务条件
#查询学员李军的总得分(students/scores)
#笛卡尔积方式
SELECT SUM(degree) FROM students s1,scores s2
WHERE s1.sno=s2.sno #描述两张表的关系
AND s1.sname=‘李军’ #业务条件
#连接查询方式
SELECT SUM(degree) FROM students s1 INNER JOIN scores s2
ON s1.sno=s2.sno #描述两张表的关系
WHERE s1.sname=‘李军’ #业务条件

#方式3:子查询/嵌套查询:把上次的查询结果作为这次的查询条件
#eg1:查询学员李军的总得分(students/scores)
#先查询学生表,根据学员名字查编号
SELECT sno FROM students WHERE sname=‘李军’#101
#再根据编号查得分表里的分数
SELECT SUM(degree) FROM scores WHERE sno=101
#子查询
SELECT SUM(degree) FROM scores WHERE sno=(
SELECT sno FROM students WHERE sname=‘李军’
)
#eg2:查询陈冰老师能讲解的课程的名称(teachers/courses)
#先查老师表,根据名字查编号
SELECT tno FROM teachers WHERE tname=‘陈冰’ #831
#再根据编号查课程名称
SELECT cname FROM courses WHERE tno=831
#子查询
SELECT cname FROM courses WHERE tno<(
SELECT tno FROM teachers WHERE tname=‘陈冰’
)

gdbc
gdbc是简称,全称是java database connectivity,专门用来完成 java程序 和 数据库 的连接的技术.
使用步骤
1,导入jar包(使用JDBC提供了丰富的工具类)
2,提供连接数据库的参数(用户名root 密码root 端口号3306)
3,在java程序中,发起SQL语句操作数据库
4,如果数据库有查到的结果,返回给java程序

入门案例
1,创建project: File - New - Project - 选择java - next - next - 输入工程名称 - Finish
2,导入jar包:
找到磁盘里的mysql-connector-java-5.1.32.jar复制,粘贴到Project里.
在IDEA里,选中jar包,右键编译(add as library…),ok
检查是否编译成功:看到IDEA里的jar包可以被点开了
3,编写java代码

package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//JDBC的入门案例:
//JDBC是java连接数据库的一个标准,本质上就是一堆的工具类
public class Test1 {
public static void main(String[] args) throws Exception {
//1,注册驱动
Class.forName(“com.mysql.jdbc.Driver”);//全路径
//2,获取数据库的连接(用户名/密码)
//jdbc连接mysql数据库的协议//本机:端口号/数据库的名字
String url=“jdbc:mysql://localhost:3306/数据库名称” ;
Connection c = DriverManager.getConnection(
url,“root”,“root”);


（调用方便，省内存，提供工具类解释说明尽量使用文档注释）

    //3,获取传输器
    Statement s = c.createStatement();
    //4,执行SQL,并返回结果集
    String sql="select * from dept" ;//查询dept表的所有数据
    ResultSet r = s.executeQuery(sql);//executeQuery执行查询的SQL,executeUpdate执行增删改查的SQL
    //5,处理数据库返回的结果
    while(r.next()){//next()判断resultset中有数据吗
        //getXxx()获取resultset中的数据
        int a = r.getInt(1);//获取第1列的 整数值
        String b = r.getString(2);//获取第2列的 字符串值
        String c1 = r.getString(3);//获取第3列的 字符串值
        System.out.println(a+b+c1);
    }
    //6,释放资源
    r.close();//释放结果集
    s.close();//释放传输器
    c.close();//释放连接器
}

}