05.数据库基础

guan_xfeng

于 2022-04-28 23:00:00 发布

阅读量966

点赞数

分类专栏： java基础文章标签： mysql java

本文链接：https://blog.csdn.net/guan_xfeng/article/details/124452245

版权

java基础专栏收录该内容

5 篇文章 0 订阅

订阅专栏

数据库:

数据库:专门用来存储数据和管理数据的一个仓库,英文叫database

分类:关系型和非关系型

关系型数据库:存的数据都是有关系的,代表是: mysql Oracle DB2

非关系型数据库:存的数据之间有着松散的关系,代表是 Redis

数据库的结构:

数据库->表(字段1,字段2…) -> 数据/记录

操作数据库:SQL语句

全称是结构化查询语言,专门用业操作数据库的语言

而且是一个标准语言,可以操作任何数据库

SQL的分类:

DML（Data Manipulation Language）:数据操纵语言,就是对数据的操作(增,删,改,查简称CRUD)

DDL（Data Definition Language）:数据库的定义语言,比如说创建表…

DCL（Data Control Language）:数据库控制语言,比如说数据库的权限管理

DQL（Data Query Language）:数据库查询语言,比如进行复制的查询语句

数据库操作:

net start mysql; 启动数据库服务(必须以管理员身份运行)

show databases; 显示数据库所有表格

create database cgb2111; 新建数据库

create database cgb2111 default character set utf8; 新建数据库设置编码集,防止中文乱码!!!

drop database cgb2111; 删除数据库

use cgb2111; 使用数据库

set names gbk; 临时解决中文乱码方案,只在本次命令窗口有效!!!

数据库表格操作;

show tables; 显示数据库所有的表格

create table student(id int(3),name varchar(10),age int(3)); 新增表格

create table emp(id int,name varchar(50),eno int); 新增表格

desc student; 查看表结构

alter table student add column adress varchar(100); 表格student增加新的列adress

alter table student add adress varchar(100); 表格student增加新的列adress

alter table student drop adress; 删除表格字段adress

alter table student modify adress varchar(80); 修改字段adress的类型

alter table student change adress addr varchar(100); 修改表格字段名称

drop table student ; 删除表

表格的增删改查:

select * from student; 查询学生表格的数据

insert into student values(100,‘jack’,18,‘大佛山’); 向student表灌数据,插入所有字段数据.

insert into student(id,name) values(101,“小狗”); 向student表格指定字段添加数据!!!

update student set name = “小狗”; student表的name全部改为小狗

update student set name = “小狗” where id=1; student表中id=1的行,name修改为小狗

delete from student; 删除表格的全部数据

delete from student where id=1; 删除表格id为1这一行的数据

是通过不同的方式,给不同的字段添加不同的约束,实现不同效果
常见的字段约束: 非空约束/唯一约束/主键约束…
通常在设计表时,就已经设计好了表里一些字段的约束情况

哪个字段,添加了非空约束,哪个字段的值,从此,必须有值不能为空. 使用not null来实现

create table student(id int(3) not null,name varchar(10),age int(3)); 新建表格student,并约束id非空

create table student(id int(3) unique,name varchar(10),age int(3)); 新建表格student,并约束id字段的值唯一

create table student(id int primary key,name varchar(10),age int(3)); 设置表格student的id为主键,主键约束必须非空且唯一

主键约束
主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

添加主键约束，例如将id设置为主键：

主键自增策略当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO_INCREMENT值，都会自增1. AUTO_INCREMENT=1

create table student(id int primary key auto_increment,name varchar(10)); 设置主键而且主键自增

insert into student values(null,“jack”); id因为设置了自增,数据库会自动维护主键的值,自增++,id直接写null就可以的

sql基础函数:mysql像java一样提供一些函数

SQL语句	表达的含义
SELECT dname FROM dept;	查询部门表的所有部门名称
SELECT * FROM dept;	查询部门表的所有数据(*为通配符)
SELECT dname,loc FROM dept;	查询部门表的所有部门名称和地址
SELECT dname a FROM dept;	查询部门表的所有部门名称,并且设置为别名a
SELECT UPPER(dname) FROM dept ;	查询部门表所有部门名称,并且以全大写显示
SELECT LOWER(dname) aa,dname bb FROM dept;	查询部门表所有部门名称,以全小写显示,并且设置别名aa,bb显示
SELECT UPPER(ename) FROM emp;	查询员工表的员工名字,并以大写显示
SELECT LENGTH(loc),loc FROM dept;	Length函数,取字段内字符串的长度
SELECT SUBSTR(dname,2,3),dname FROM dept;	substr(dname,2,3)函数为取字符串dname内的字符串,从第2位开始,取长度为3
SELECT SUBSTR(dname,2),dname FROM dept;	substr(dname,2)函数为取字符串dname内的字符串,从第2位开始至最后
SELECT CONCAT(dname,“hello”),dname FROM dept;	concat(dname,“hello”)函数,在dname后面拼接"hello"
SELECT REPLACE(dname,‘a’,“666”),dname FROM dept;	replace(dname,‘a’,‘666’)函数,把dname里面的a字符替换成’666’
SELECT comm,IFNULL(comm,0) FROM emp;	ifnull(comm,0)函数,如果comm字段的值为null,则用0替换
select comm,ceil(comm) from emp;	ceil向上取整,意思就是只要小数部分不为0,就直接向上进1
SELECT comm,FLOOR(comm) FROM emp	floor(comm)向下取整,意思就是直接舍弃小数部分
select comm,round(comm,1) from emp;	四舍五入并保留一位小数
select comm,round(comm) from emp;	直接四舍五入取整
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW())	获取当前时间的年月日
SELECT YEAR(‘2006-01-01’)	获取指定日期中的年
SELECT NOW()	获取当前时间的年月日时分秒
SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())	获取当前时间的时分秒
SELECT ‘xi’an’	特殊符号转义字符
SELECT “xi’an”	特殊符号也可以用双引号包含在里面不产生歧义

条件语句查询 :

Sql语句
SELECT DISTINCT loc FROM dept	distinct关键字,主要作用,去重,去除重复项
SELECT * FROM dept WHERE deptno=1	查询部门表里面deptno为1的数据
SELECT dname FROM dept WHERE loc=“二区”	查询办公地址在二区的部门名称
SELECT * FROM emp WHERE sal>8000	查询工资>8000的员工信息
SELECT ename FROM emp WHERE sal>3000 AND job=“员工”	查询岗位是员工并且工资>3000的员工姓名
SELECT * FROM emp WHERE sal=3000 OR sal=8000	查询工资sal=3000或sal=8000的员工信息
SELECT * FROM emp WHERE sal IN(3000,8000)	查询工资sal=3000或sal=8000的员工信息
SELECT * FROM emp WHERE ename LIKE ‘%a%’	查询员工表名字里包含a的员工信息
SELECT * FROM emp WHERE job LIKE “%总%”	查询工作岗位中有总字的员工姓名
SELECT ename FROM emp WHERE job LIKE ‘总%’	查询工作岗位以总开头的员工姓名
select * from emp where ename like ‘%a’	以a结束的
SELECT * FROM emp WHERE comm IS NULL	查询没有奖金的员工的信息
SELECT * FROM emp WHERE comm IS NOT NULL	查询有奖金的员工的信息
SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000	查询工资在 [5000,10000] 的员工信息
SELECT * FROM emp WHERE sal>=5000 AND sal<=10000	查询工资在 [5000,10000] 的员工信息
SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN ‘2015’ AND ‘2019’	查询2015年至2019年入职的员工信息
UPDATE dept SET dname=“java YYDS”,loc=“Beijing” WHERE deptno=1	修改1号部门的名称和地址
DELETE FROM dept WHERE deptno=3	删除3号部门的数据
SELECT * FROM emp ORDER BY ename	员工表按照名字排序(默认升序)
SELECT * FROM emp ORDER BY sal	员工表按照工资排序(默认升序)
SELECT * FROM emp ORDER BY hiredate	员工表按照入职时间排序 (默认升序)
SELECT * FROM emp ORDER BY sal DESC	员工表按照工资排序,降序排序
SELECT * FROM emp ORDER BY sal ASC	员工表按照工资排序,升序规则,可以不写,默认升序
SELECT * FROM emp LIMIT 2	LIMIT 限制,只展示查询结果的前2条数据
SELECT * FROM emp LIMIT 2,3	展示查询结果从第3条数据开始,总展示3条数据记录
SELECT * FROM emp ORDER BY sal DESC LIMIT 1	查询员工表工资最高的员工信息
SELECT * FROM emp ORDER BY sal DESC LIMIT 0,1	查询员工表工资最高的员工信息
SELECT sal FROM emp WHERE YEAR(hiredate)=2019	统计2019年入职的员工的工资
SELECT comm FROM emp WHERE job=“员工”	统计员工岗位的奖金
SELECT (sal+IFNULL(comm,0))*12 FROM emp	统计员工的年薪
SELECT AVG(sal) FROM emp	查询员工的平均工资
SELECT SUM(sal)/COUNT(sal) FROM emp	查询员工的平均工资

聚合函数:

SQL语句
SELECT AVG(sal) FROM emp	查询员工的平均工资
SELECT SUM(sal)/COUNT(sal) FROM emp	查询员工的平均工资
SELECT MAX(sal) FROM emp	统计员工的最高工资
SELECT AVG(sal) FROM emp WHERE job=“员工”	统计员工岗位的平均工资
SELECT MAX(sal) FROM emp WHERE job=“员工”	统计员工岗位的最高工资
SELECT MIN(sal) FROM emp	统计员工的最低工资
SELECT MIN(sal) FROM emp WHERE YEAR(hiredate)=2019	统计2019年入职的员工的最低工资
SELECT SUM(sal) FROM emp WHERE YEAR(hiredate)=2019	统计2019年入职员工的总工资
SELECT COUNT(*) FROM emp	统计员工的总人数 ( 统计总记录条数 )
SELECT COUNT(*) FROM emp WHERE deptno=2	统计2号部门的员工总人数
SELECT COUNT(*) FROM emp WHERE YEAR(hiredate)<2019	统计2019年以前入职的员工总人数

分组group : 用group by 分组执按照合理的维度分组

什么时候需要用分组?

查询的结果中出现了混合列

分组后的数据过滤:group by … having

SQL语句
SELECT job,COUNT(*) FROM emp GROUP BY job	统计每个岗位的员工人数
SELECT job,AVG(sal) FROM emp GROUP BY job	统计每个岗位的平均薪资
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno	统计每个部门的员工人数
SELECT COUNT(*) a,deptno b FROM emp GROUP BY b	统计每个部门的员工人数,并且设置列的别名
SELECT YEAR(hiredate),COUNT(*) FROM emp GROUP BY YEAR(hiredate)	统计每年入职的员工人数
SELECT YEAR(hiredate) a,COUNT(*) b FROM emp GROUP BY a HAVING a>2015	统计每年入职的员工人数,只要2015年以后的数据,并且起别名
SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>5000	统计每个部门的平均薪资,只要展示大于5000的
SELECT AVG(sal) a,deptno FROM emp GROUP BY deptno HAVING a>5000	统计每个部门的平均薪资,只要展示大于5000的,且设别名

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

事务主要作用是用来保证多条sql语句,要么成功,要么失败

事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

同一个事务里的多条sql语句,是一个原子密不可分,要不全成功,要不全失败

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

保证多台服务器里的数据是一致的(分布式系统)

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

数据库为了提高操作的效率允许高并发访问,并采用了隔离性保证了数据的安全性(采用锁机制,同步锁的概念)

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

是指,我们对数据库的操作(增删改)是持久性的

事务隔离分为不同级别，包括

读未提交（Read uncommitted）安全性最差,可能发生并发数据问题,性能最好

读已提交（read committed）安全性有所提高,但是效率降低一些,Oracle默认的隔离级别
可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
串行化（Serializable）表级锁，读写都加锁，效率低下,安全性高,不能并发

事务管理的方式

开启事务sql语句: start transaction;

提交事务sql语句:

commit; 提交 rollback 回滚

1.使用mysql数据库为我们提供的,自动事务管理,默认会为每条sql提供事务

2.手动管理事务:必须有两个过程,必须有两个过程,开启事务…结束事务(commit/rollback)

3.采用方式2来模拟事务的管理过程

事务处理

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
MySQL默认数据库的事务是开启的，执行SQL后自动提交。
MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

MySQL单表数据不超过千万,否则影响执行效率

表强化:6约束 constraints

1.非空约束not null

2.唯一约束unique

3.主键约束 primary key

4.默认约束 default

5.检查约束 check:

检查字段的值的合理性

6.外键约束foreign key

为了省内存,使用对方表的主键来描述两张表的关系

#外键约束建表示例

CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT ,
phone VARCHAR(11)
)

CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY,#不能自增!!!!
address VARCHAR(100),
#创建外键FK,描述和1号表的关系
#foreign key(本表的主键) references 对方表名(对方表的主键)
    #情况1:子表里的主键的值,必须取自于主表
FOREIGN KEY(user_id) REFERENCES tb_user(id)
)

多表联查 (笛卡尔职):

本质上就是把多张表,联合查询,要求,多张表用逗号隔开

select * from dept,emp; 查询两张表的全部信息

#准备表和数据进行测试
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:计算机导论课程所得的总分

SELECT SUM(scores.degree) FROM courses,scores WHERE courses.cname=“计算机导论” AND courses.cno=scores.cno

练习2:查询学员陆君的总得分

SELECT SUM(scores.degree) FROM scores,students WHERE scores.sno=students.sno AND students.sname=“陆君”

笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

练习3:统计陈冰老师能讲的课程名称

SELECT courses.cname FROM teachers JOIN courses ON teachers.tno=courses.tno WHERE teachers.tname=“陈冰”

练习4:统计操作系统课程的总得分

SELECT SUM(scores.degree) FROM scores JOIN courses ON scores.cno=courses.cno WHERE courses.cname=“操作系统”

三种连接:

内连接 inner join

SELECT * FROM emp a INNER JOIN dept b ON a.deptno=b.deptno

左（外）连接 left join

SELECT * FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno

右（外）连接 right join

SELECT * FROM emp a RIGHT JOIN dept b ON a.deptno=b.deptno

子查询 subquery

又叫嵌套查询,是指,把第一次的查询结果,作为第二次的查询条件,继续发起查询

#练习1:查询research部门的员工信息
#第一次:查部门表,根据部门名称查部门编号
SELECT deptno FROM dept WHERE dname="research"   #查询后结果为2
#第二次:查员工表,根据部门编号查询员工信息
SELECT * FROM emp WHERE deptno=2
#整合在一起的子查询语句:
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname="research"

#练习2:查询tony所在的部门名称
#第一次,查询tony所在部门的编号
SELECT deptno FROM emp WHERE ename="tony"            #查询后结果为2
#第二次,查询根据查出来的部门编号,查询部门名称
SELECT dname FROM dept WHERE deptno=2
#整合在一起的子查询语句:
SELECT dname FROM dept WHERE deptno=(SELECT deptno FROM emp WHERE ename="tony")
                                
#练习3:查询java开发部和research部门的员工姓名
#第一次:先查询部门编号
SELECT deptno FROM dept WHERE dname="java开发部" OR dname="research"      #查询后结果为1和2       
#第二次:根据编号查名称
SELECT ename FROM emp WHERE deptno=1 or deptno=2                            
#整合在一起的子查询语句:                                
SELECT ename FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname="php" OR dname="research")

#综合练习:查询可以讲高等数学的老师的名字
#方式一:笛卡尔积
SELECT tname FROM teachers,courses WHERE teachers.tno=courses.tno AND courses.cname="高等数学"

#方式二:连接查询
#join
SELECT teachers.tname FROM teachers JOIN courses ON teachers.tno=courses.tno
WHERE courses.cname="高等数学"
#inner join
SELECT tname FROM teachers a INNER JOIN courses b ON a.tno=b.tno WHERE b.cname="高等数学"
#left join
SELECT tname FROM teachers a LEFT JOIN courses b ON a.tno=b.tno WHERE b.cname="高等数学"
#right join
SELECT tname FROM teachers a RIGHT JOIN courses b ON a.tno=b.tno WHERE b.cname="高等数学"

#方式三:子查询
SELECT tname FROM teachers WHERE tno IN(SELECT tno FROM courses WHERE cname="高等数学")

#综合练习:查询学员曾华的总得分
#笛卡尔积
SELECT SUM(degree) FROM scores a,students b WHERE a.sno=b.sno AND b.sname="曾华"
#连接查询
SELECT SUM(a.degree) FROM scores a JOIN students b ON a.sno=b.sno WHERE b.sname="曾华"
#子查询
SELECT SUM(degree) FROM scores WHERE sno IN(SELECT sno FROM students WHERE sname="曾华")

#查询emp表高于平均工资的员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)

SQL的执行顺序:(sql优先思路)

(1) FROM [left_table] 选择表

(2) ON <join_condition> 链接条件

(3) <join_type> JOIN <right_table> 链接

(4) WHERE <where_condition> 条件过滤

(5) GROUP BY <group_by_list> 分组

(6) AGG_FUNC(column or expression),… 聚合

(7) HAVING <having_condition> 分组过滤

(8) SELECT (9) DISTINCT column,… 选择字段、去重

(9) ORDER BY <order_by_list> 排序

(10) LIMIT count OFFSET count; 分页

索引 index (数据库优化手段)

主要用来提高数据库的查询效率

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用B+Tree树方式构

索引的分类:

单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：本身也是一个单值索引,但是索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列

索引主要是提高查询效率,建议给经常用来查询的字段加索引

1.查看索引(表格的主键自带索引)

固定命令: SHOW INDEX FROM dept

2.创建普通索引

创建单值索引

CREATE INDEX index_name ON dept(deptno)

CREATE INDEX index_name ON students(sname)

创建唯一索引:找到合适的列,值要唯一

CREATE UNIQUE INDEX index_sno ON students(sno)

主键索引会自动添加,不要手动重复给主键添加索引,否则两个索引都会失效

创建复合索引:

CREATE INDEX index_max ON emp(ename,job,deptno)

使用复合索引要注意,最左特性,就是始终要包含最左边的元素查询,索引才生效,也即是ename必须得出现才生效

例如:

SELECT * FROM emp WHERE ename=“jack” 复合索引生效
SELECT * FROM emp WHERE job=“总监” 复合索引失效
SELECT * FROM emp WHERE deptno=2 复合索引失效
SELECT * FROM emp WHERE ename=“jack” AND job=“总监” 复合索引生效
SELECT * FROM emp WHERE job=“总监” AND ename=“jack” 复合索引生效
SELECT * FROM emp WHERE ename=“jack” OR job=“总监” 复合索引失效

EXPLAIN
SELECT * FROM students WHERE sname LIKE "%王%"    #索引失效

EXPLAIN
SELECT * FROM students WHERE sname LIKE "%王"     #索引失效

EXPLAIN
SELECT * FROM students WHERE sname LIKE "王%"     #索引生效


#模糊查询,只有最后情况,才有高效的效果,索引才会生效

也就是必须包含最左边的元素ename,条件查询就要用and连接,而or由于查询的数据多,效率低会导致索引失效

3.使用索引(按照索引列去查)

SELECT * FROM students WHERE sname=“陆君”

SELECT * FROM students WHERE sno=105

虽然查询语句还是一样,但是效率不一样,设置了索引后,查询效率会提高

4.查询SQL的执行性能/计划(关注SQL是否用到了索引)

EXPLAIN SELECT * FROM students WHERE sname=“陆君”

EXPLAIN SELECT * FROM students WHERE sno=105

5.删除索引

ALTER TABLE emp DROP INDEX index_max

索引的优缺点:

优点:
大提高了数据库的查询效率

本质上数据库会为索引列的数据进行排序,快速查询

缺点:

本身索引是一个单独的表,也需要占空间的

索引适合查询的业务,但是,也需要同步更新修改一些新的数据,需要一定的时间维护

原则:

什么时候加索引?

频繁的按照一个规则去查询的数据,就应该考虑添加索引

给谁加索引?

给那些经常作为查询条件的字段添加索引

加什么索引?

索引是有分类的,单值索引/唯一索引/复合索引,看实际业务选择不同的索引类型

如何查看SQL的执行计划/性能?

使用EXPLAIN 关键字

视图:就是一张特殊的表,用来缓存查询的结果

#视图:就是一张特殊的表,用来缓存查询的结果
#1.创建
#语法:create view 视图名 as 查询的SQL语句

CREATE VIEW empview AS 
SELECT * FROM emp WHERE ename LIKE "%a%"

#2.使用
SELECT * FROM empview
#好处是:提高SQL的复用性
#屏蔽了真实的业务表的复杂性
#被所有人共享
#坏处是:一旦创建成功,就无法被优化
#视图中存了和业务中大量重复的数据,"浪费空间"
#视图通常用来查询,不便于更新这个动作

SQL优化!!!

1, 查询语句中的*尽量换成字段名称
2, 查询条件where中,尽量用and不用or. 尽量把查询条件的范围控制到最小
3, 查询条件中,尽量用= 别用!= <> , 尽量别用in
4, 表设计时,字段类型尽量用varchar代替char, 尽量用数字代替字符串
5, 索引: 单表中的索引最好控制在5个以内,
模糊查询时,只有一种可以让索引生效: 确定开始元素 ‘abc%’
写SQL时注意字符串的标准写法,
where name=123,name的索引会失效
where name=‘123’,name的索引会生效
注意复合索引的最左特性,如果不遵循会导致复合索引失效
6, 多表联查: 可以使用三种方式笛卡尔积/连接查询/子查询
尽量不要有太多表联查,阿里的开发规范规定了最多三张表
连接查询相对高效,最高效的是内连接,因为内连接只是查交集
但是,在工作中,使用左连接是最多的,也会使用子查询

范式:

对于数据库的设计人员,必须要遵循的规范,就叫范式

第一范式,第二范式,第三范式,巴斯-科德范式,第四范式,第五范式

常见的范式有三范式:1NF,2NF,3NF

好处是:可以让数据减少冗余

三范式的规则:

1NF:是指表里的字段值不能再分割了

2NF:是指遵循了1NF的基础上,再遵循2NF,是指表里都应该设计主键字段(主属性),

其它的非主属性,都应该围绕主属性展开描述

3NF:是指遵循了前两范式的基础上,字段之间的依赖性是直接的,而不是还要间接的产生依赖,导致查询关系变得复杂

JDBC:

全称叫 java database connectivity,专门用来完成,利用一段java程序来操作数据库的事情.

是一套标准,是一套规范.

本质上就是一个jar包(一堆的工具类)

使用步骤:

1.找到jar包,并拷贝进项目

2.利用工具类,通过java连接数据库(用户名 root 密码 root 数据库的库名数据库的端口号3306)

3.通过java程序,发起SQL语句

4.数据库把执行结果,返回给java程序

JDBC入门案例:

1.找到jar包,并拷贝进项目

导入jar包:找到你的jar包,复制…选中idea里的项目,粘贴…

编译:选中jar包,右键,add as library…

package cn.tedu.jbbc;
import java.sql.*;
/**利用jdbc,操作数据库*/
//需求:查询部门表的所有数据
public class Test1 {
    public static void main(String[] args)  {
        Connection c = null;
        Statement s = null;
        ResultSet r = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取数据库的连接
            //String url = "协议://数据库的服务器的ip地址:端口号/数据库名";
            String url ="jdbc:mysql://localhost:3306/cgb2111";
            c = DriverManager.getConnection(url, "root", "root");
            //3.获取传输器
            s = c.createStatement();
            //4.执行SQL
            r = s.executeQuery("select * from dept");//执行查询的SQL
            System.out.println("连接成功");
            //5.处理结果--遍历结果
            //r.next()判断,只要r里有数据就返回true,没数据就返回false
            while (r.next()){
                //获取不同类型的数据getXxx()
                //获取 第一列的 一个整数值
                int deptno = r.getInt(1);
                String dname = r.getString(2);
                String loc = r.getString(3);
//                int deptno = r.getInt("deptno");
//                String dname = r.getString("dname");
//                String loc = r.getString("loc");
                System.out.println(deptno+"\t"+dname+"\t"+loc);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6.关闭资源close()
            //关闭结果集ResultSet
            try {
                r.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            //关闭传输器Statement
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            //关闭连接Connection
            try {
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package cn.tedu.jbbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//#1.去数据库里,创建user表(id,name,pwd)并且插入数据
//#2.去idea写jdbc的代码,查询user表里的所有数据
public class Test2 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/cgb2111";
        Connection c = DriverManager.getConnection(url, "root", "root");
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("select * from user");
        while (r.next()){
            int id = r.getInt(1);
            String name = r.getString(2);
            int pwd = r.getInt(3);
            System.out.println(id+"\t"+name+"\t"+pwd);
        }
        r.close();
        s.close();
        c.close();
    }
}

常规的传输器 Statement

会产生SQL攻击:

package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/**写jdbc的代码,查询登录*/
public class TestJDBC {
    public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.建立连接
        String url = "jdbc:mysql://localhost:3306/cgb2111";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //3.获取传输器
        Statement s = c.createStatement();
        //s.executeUpdate("IN
        // SERT INTO USER VALUES (NULL,'tony',12)");
        //4.执行sql
        System.out.println("请输入帐号:");
        String user = new Scanner(System.in).nextLine();
        System.out.println("请输入用户密码:");
        int password = new Scanner(System.in).nextInt();
        String sql = "select * from user where name='"+user+"' and pwd= "+ password;
        ResultSet r = s.executeQuery(sql);
        //5.处理结果集(根据用户名和密码去查,只会查出来一条记录)
        if(r.next()){//如果查到数据,才表示库里有这个用户信息
            System.out.println("登录成功");
        }else {//如果没有数据
            System.out.println("登录失败");
        }
        r.close();
        s.close();
        c.close();
        /*
        1.问题:当用户输入特殊值:jack'#时,甚至不需要密码也能登录
        2.产生的原因:#在SQL中表示注释的意思,相当于后面的条件被注释掉了...
        SELECT * FROM USER WHERE NAME='JACK'#' AND PWD='123456'
        现象叫SQL攻击/SQL注入,本质上就是因为SQL语句中出现了特殊符号#,#SQL中#是注释的意思
        后面的内容就全部给注释掉了
        导致了,SQL语义发生改变
         */
    }
}

预处理传输器 PreparedStatement

package cn.tedu.jdbc;
import java.sql.*;
import java.util.Scanner;
/**写jdbc的代码,查询登录*/
public class TestJDBC {
    public static void main(String[] args) throws Exception {
        /*
        1.问题:当用户输入特殊值:jack'#时,甚至不需要密码也能登录
        2.产生的原因:#在SQL中表示注释的意思,相当于后面的条件被注释掉了...
        SELECT * FROM USER WHERE NAME='JACK'#' AND PWD='123456'
        现象叫SQL攻击/SQL注入,本质上就是因为SQL语句中出现了特殊符号#,#SQL中#是注释的意思
        后面的内容就全部给注释掉了
        导致了,SQL语义发生改变
         */
        //2.1 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.2 建立连接
        String url = "jdbc:mysql://localhost:3306/cgb2111";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //2.3 获取 新的传输器--安全,高效
        System.out.println("请输入帐号:");
        String name = new Scanner(System.in).nextLine();
        System.out.println("请输入密码:");
        String pwd = new Scanner(System.in).nextLine();
        //sql的骨架
        String sql = "select * from user where name=? and pwd=?";
        PreparedStatement s = c.prepareStatement(sql);
        //2.4 给SQL绑定参数
        s.setString(1,name);//给第一个问号,设置a的值
        s.setString(2,pwd);
        ResultSet r = s.executeQuery();
        //2.5 处理结果集(根据用户名和密码去查,只会查出来一条记录)
        if(r.next()){//如果查到数据,才表示库里有这个用户信息
            System.out.println("登录成功");
        }else {//如果没有数据
            System.out.println("登录失败");
        }
        r.close();
        s.close();
        c.close();
    }
}

利用新的传输器,查询部门编号为1的数据

利用多态,直接用getObject和setObject

package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**利用新的传输器,查询部门编号为1的数据*/
public class Test003 {
    public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.建立连接
        String url = "jdbc:mysql://localhost:3306/cgb2111?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //sql骨架
        String sql = "select * from dept where deptno=?";
        //4.获取转输器
        PreparedStatement s = c.prepareStatement(sql);
        //设置sql参数
        s.setObject(1,1);
        //5.执行sql
        ResultSet r = s.executeQuery();
        while (r.next()){
            Object aa = r.getInt(1);
            Object bb = r.getString(2);
            Object cc = r.getString(3);
            System.out.println(aa+"\t"+bb+"\t"+cc);
        }
        r.close();
        s.close();
        c.close();
    }
}

jdbc的工具类,抽取一些共性代码,充当JDBC的工具类:

package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
/**jdbc的工具类,抽取一些共性代码*/
public class JDBCUtil {
    public static Connection get() throws Exception {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.建立连接
        String url = "jdbc:mysql://localhost:3306/cgb2111?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //3.把获取到的数据库的连接,返回给调用者
        return c;
    }
}

使用工具类建立连接:

package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**利用新的传输器,查询部门编号为1的数据*/
public class Test003 {
    public static void main(String[] args) throws Exception {
        //1.加载驱动建立连接
        Connection c = JDBCUtil.get();
        //sql骨架
        String sql = "select * from dept where deptno=?";
        //2.获取转输器
        PreparedStatement s = c.prepareStatement(sql);
        //设置sql参数
        s.setObject(1,1);
        //3.执行sql
        ResultSet r = s.executeQuery();
        //4.获取数据
        while (r.next()){
            Object aa = r.getInt(1);
            Object bb = r.getString(2);
            Object cc = r.getString(3);
            System.out.println(aa+"\t"+bb+"\t"+cc);
        }
        r.close();
        s.close();
        c.close();
    }
}

处理数据连接的异常:

package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test06 {
    public static void main(String[] args){
        Connection c = null;
        PreparedStatement s = null;
        try{
            c = JDBCUtil.get();
            String sql = "insert into user values(null,?,?)";
            s = c.prepareStatement(sql);
            s.setObject(1,"kucy");
            s.setObject(2,"12345");
            s.executeUpdate();
        }catch (Exception e){
            System.out.println(e.getStackTrace());
        }finally {
            if(s!=null){
                try {
                    s.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(c!=null){
                try {
                    c.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } 
            }
        }
    }
}

封装关数据库关闭流方法:

public  static void close(ResultSet r,PreparedStatement s,Connection c) {
        if (r != null) {
            try {
                r.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (s != null) {
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (c != null) {
            try {
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(PreparedStatement s,Connection c) {
        if (s != null) {
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (c != null) {
            try {
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

guan_xfeng

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
05.数据库基础

数据库:数据库:专门用来存储数据和管理数据的一个仓库,英文叫database分类:关系型和非关系型关系型数据库:存的数据都是有关系的,代表是: mysql Oracle DB2非关系型数据库:存的数据之间有着松散的关系,代表是 Redis数据库的结构:数据库->表(字段1,字段2…) -> 数据/记录操作数据库:SQL语句全称是结构化查询语言,专门用业操作数据库的语言而且是一个标准语言,可以操作任何数据库SQL的分类:DML（Data Manipulation Lan
复制链接

扫一扫