数据库
数据维护变迁
从手工管理到文件管理，最后到数据库，效率逐渐加快
DB和DBMS
数据库和数据库管理系统
- 数据库：按照数据结构存储数据的仓库
- 数据库管理系统：管理数据库的软件（mysql, mariadb, …）
库与表的关系
- 表
- 数据的集合，一组具有相同属性的数据保存在一张表上
- 表的行row时横排数据，称为记录record
- 表的列colum是纵列数据，称为字段field
- 表和表之间存在关联关系
- 库
- 表的集合
- 不同的项目都可以创建一个不同的库
- MariaDB中可以管理多个不同的库
SQL
- 结构化查询语言
- SQL执行SQL92标准
- 所有数据库均支持该标准，因此SQL是数据库中的普通话
- SQL92标准中不是所有的操作均标准化了，有些特定的操作各个数据库并不相同
- 在关系型数据库上执行数据操作、检索及维护
SQL分类
-
数据定义语言DDL
-
数据操纵语言DML
-
数据查询语言DQL
-
事务控制语言TCL
-
数据控制语言DCL
DDL
不可回滚
CREATE, ALTER, DROP, TRUNCATE …
用于建立、修改、删除数据库对象，不关心数据本身，操作的是库对象
常用方法
CREATE DATABASE peng CHARSET=UTF8/GBK; # 创建数据库并指定字符集
DROP DATABASE peng1; #删除数据库，不可逆
DESC student; #查看表结构
CREATE TABLE student( # 创建表
id INT,
name VARCHAR(32),
age INT
);
RENAME TABLE student TO studentinfo; #重命名表
DROP TABLE studentinfo; #删除表
ALTER TABLE teacher ADD teacher_age INT(3); #添加表字段
ALTER TABLE teacher ADD teacher_id INT; #添加表字段
ALTER TABLE teacher ADD salary INT FIRST; #在首位添加表字段
ALTER TABLE teacher DROP COLUMN teacher_age; #删除表字段
#TRUNCATE：清空表或者说是截断表，只能作用于表。绕过了删除数据的DML方法，因此，它不能回滚
TRUNCATE TABLE tbl_name
原则：
- 若表中已经存在数据，尽量不要修改字段类型，否则容易导致原有数据不满足新类型而报错
- 若表中已经存在数据，字段长度不要缩短，否则原数据可能不满足新长度
- 若表中已经存在数据，字段添加新约束时，要保证表中该字段原有的数据不违背该约束要求
DML
可回滚
INSERT, UPDATE, DELETE
常用方法
INSERT INTO person(name, age) VALUES ('张三',15); #插入
INSERT INTO teacher VALUES(DEFAULT, 20); #使用字段默认值插入
INSERT INTO teacher VALUES(null, 20); #插入null
INSERT INTO teacher VALUES('lisi', 25),('wangwu',22),('老6',6);
CREATE TABLE person(
id INT,
age INT UNSIGNED #无符号整数，只能插入正数，否则报错
);
INSERT INTO person VALUES(1,20);
INSERT INTO person VALUES(1.1,20);#舍弃小数
INSERT INTO person VALUES(1,-20);#插入负数报错
ALTER TABLE person ADD salary DOUBLE(5,2);#整数加小数最多5位，小数部分最多2位
INSERT INTO person(salary) VALUES(99999.997);#小数超出范围会进位，进位后整数超出5位，会报错
UPDATE person SET age=20 WHERE name='张三';
DELETE FORM person; #删除person中所有内容
DELETE FORM person WHERE name='张三'; #删除单挑数据
DQL
SELECT
语句执行顺序
| 子句 | 执行顺序 |
|---|---|
| SELECT | 6 |
| FROM | 1 |
| JOIN…ON… | 2 |
| WHERE | 3 |
| GROUP BY | 4 |
| HAVING | 5 |
| ORDER BY | 7 |
| LIMIT | 8 |
常用方法
SELECT `name`,salary,gender,title
FROM teacher
WHERE `name`<>'刘苍松'; #查询除了'刘苍松'以外的其他老师信息
SELECT `name`,floor
FROM class
WHERE floor>=2;
#AND/OR：与的优先级大于或
SELECT `name`,age,gender,job
FROM student
WHERE job='大队长' AND age=7;
SELECT `name`,title,salary
FROM teacher
WHERE title='一级讲师' OR title='三级讲师';
SELECT `name`,age,gender,job
FROM student
WHERE class_id<=6 AND (job='大队长' OR job='中队长');
SELECT `name`,gender,age,job
FROM student
WHERE job IN('大队长','中队长','小队长');
SELECT `name`,gender,age,salary
FROM teacher
WHERE salary BETWEEN 2000 AND 5000;
SELECT `name`,gender,age
FROM student
WHERE age NOT BETWEEN 7 AND 10;
去重
DISTINCT
- DISTINCT用在SELECT关键字后，要查询的字段之前
- 用于给要查询的字段去重后输出
SELECT DISTINCT title
FROM teacher;
模糊查询
LIKE
- LIKE模糊查询使用两个通配符
- %：表示任意个数字符
- _：表示一个字符
LIKE '%X%' #字符串中含有X
LIKE '_X%' #字符串中第二个字符是X
LIKE 'X%' #字符串以X开始
示例
SELECT `name`,gender,age
FROM student
WHERE `name` LIKE '张%';
判空查询
IS NULL，IS NOT NULL
- null表示不存在，为空
- 不是一个特定的数值，不能用=进行比较
SELECT `name`,comm
FROM teacher
WHERE comm=NULL;
SELECT `name`,comm
FROM teacher
WHERE comm IS NULL;
SELECT `name`,comm
FROM teacher
WHERE comm IS NOT NULL;
排序
ORDER BY
- ORDER BY 字段 ASC：升序排序，按照指定字段将结果集排序，默认就是升序，所以ASC可省
- ORDER BY 字段 DESC：按照指定字段将结果集降序排序
- 不同数据库对null值的规定不同，可能最大可能最小
- 日期的排序规则：远小近大，即离当下越远越小、越近越大
SELECT `name`,comm
FROM teacher
ORDER BY comm DESC;
SELECT `name`,comm
FROM teacher
ORDER BY comm;
SELECT `name`,birth
FROM student
ORDER BY birth DESC;#生日降序排列，年龄升序排序
多字段排序
- order by后可以用逗号相隔多个字段
- 按照字段顺序依次进行排序，第一个字段重复的部分会按照第二个字段进行排序，否则不会使用第二个字段，依此类推
- 不写排序规则的字段为默认升序排序
SELECT `name`,comm,salary
FROM teacher
ORDER BY comm, salary DESC;#先排奖金升序，相同的部分再排工资降序
分页查询
LIMIT
-
LIMIT常用在ORDER BY后，先排序后分页
-
使用LIMIT需要传入两个参数
-
m：跳过条数（从1开始算），计算公式：（当前页数-1）* 每页条数
例：每页显示3条，显示第5页，m=(5-1)*3=12
-
n：每页显示的条数
-
SELECT `name`,salary
FROM teacher
ORDER BY salary DESC
LIMIT 0,5; #查看工资前5名
函数和表达式
-
在select子句中添加表达式
SELECT `name`,salary,salary*12 FROM teacher; -
在where子句中添加表达式
SELECT `name`,salary,salary*12 FROM teacher WHERE salary*12>60000 ORDER BY salary DESC -
函数
-
IFNULL(arg1，arg2)
-
若arg1不为null，则返回arg1；
-
若arg1为null，则返回arg2
作用：将一个null值转换为非null值
SELECT `name`,salary,comm,salary+IFNULL(comm,0) FROM teacher; -
-
聚合函数
MIN, MAX, AVG, SUM, COUNT
- 用于查询字段上
- WHERE子句中不可使用聚合函数
- 对查出的字段数据进行终端运算后输出结果
SELECT MIN(salary) FROM teacher; SELECT MAX(salary) FROM teacher; SELECT AVG(salary) FROM teacher; SELECT SUM(salary) FROM teacher; SELECT COUNT(id) #count是对记录数的统计，不关心记录值 FROM teacher WHERE age>30; SELECT COUNT(*) FROM teacher; #COUNT(*)为内置统计表记录总数的写法-
聚合函数是忽略null值的，在计算平均值AVG时除的个数是非null记录的个数，会导致平均数计算错误
SELECT AVG(IFNULL(comm,0)) FROM teacher; -
SELECT中有聚合函数时，若还有其他字段，则其他字段必须存在于GROUP BY中，否则不可出现其他字段
SELECT AVG(salary),title FROM teacher GROUP BY title;
-
别名
AS
-
在字段后使用as关键字给该字段起别名，字段取别名通常是因为字段包含函数或表达式，使用别名可增强可读性
-
可在表后给表取别名，增强可读性
-
格式：
-
可直接在字段后写别名，省略AS，别名不加引号、单引号、双引号均可
SELECT `name`,salary,salary*12 annusal #将salary*12命名为annusal FROM teacher WHERE salary*12>60000 ORDER BY salary DESC; -
不省略AS，别名不加引号、单引号、双引号均可
SELECT `name`,salary,salary*12 AS 'annusal' FROM teacher WHERE salary*12>60000 ORDER BY salary DESC; -
若别名中有空格，则必须使用引号，不使用引号会报错
SELECT `name`,salary,salary*12 AS 'annu sal' FROM teacher WHERE salary*12>60000 ORDER BY salary DESC; -
当别名中有sql关键字时，要使用引号
-
分组
GROUP BY
- GROUP BY必须搭配聚合函数使用
- GROUP BY 字段：按字段值相同的记录进行分组
- 聚合函数作用在GROUP BY分组后的一组内部
SELECT AVG(salary),title
FROM teacher
GROUP BY title;
-
多字段分组
规则：当两个字段的组合相同时为一组
SELECT COUNT(*),class_id,gender
FROM student
GROUP BY class_id,gender;
- 分组后可使用ORDER BY进行排序
SELECT AVG(salary),title
FROM teacher
GROUP BY title
ORDER BY AVG(salary) DESC;
HAVING子句
- 在GROUP BY之后发挥作用
- GROUP BY确定分组，HAVING利用聚合函数对分组进行过滤
- 筛选分组后，最终查询SELECT要求的组内记录的信息
SELECT AVG(salary) AS 'avg_sal',subject_id
FROM teacher
GROUP BY subject_id;
SELECT AVG(salary) AS 'avg_sal',subject_id
FROM teacher
GROUP BY subject_id
HAVING avg_sal>6000;
子查询SubQuery
子查询要用（）括起来
-
单行单列子查询
- 结果集只有一个值
- 常用于DQL、DML
- 常用在WHERE子句中作为条件
- 可以进行>,>=,<,<=,<>等判断
SELECT `name` FROM teacher WHERE subject_id=(SELECT id FROM `subject` WHERE `name`='语文'); -
多行单列子查询
-
结果集有多个值
-
常用于DML、DQL、DDL
-
用在WHERE子句中
-
进行等值运算时配合IN, NOT IN使用
-
关系运算
>ANY(列表)：大于列表里其中之一（大于最小值即可） <ANY(列表)：小于列表里其中之一（小于最大值即可） >ALL(列表)：大于列表里所有值（大于最大值即可） <ALL(列表)：小于列表里所有值（小于最小值即可）
SELECT `name`,age,gender,class_id FROM student WHERE class_id IN (SELECT class_id FROM student WHERE `name` IN ('祝雷','李费水'));-
DDL语句中使用，可以将查出的表结构完整存储新创建的表中，新表的字段为所查表字段的别名
CREATE TABLE t_teacher_salary_info AS SELECT subject_id, MIN(salary) AS min_salary, MAX(salary) AS max_salary, SUM(salary) AS sum_salary, AVG(salary) AS avg_salary FROM teacher GROUP BY subject_id;
-
-
多行多列子查询
- 结果集是一个表
- 常用于DQL、DDL
视图
视图是表的部分数据，如一个字段
- 视图（VIEW）被称为虚表，即虚拟的表，是一组数据的逻辑表示
- 视图对应一条select语句，结果集被赋予名字，即为视图
- 视图本身不含数据，而是映射一条select语句的结果集，当基表的数据发生变化，视图也随之变化
视图的作用
- 若需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询
- 视图本质上就是一条select语句，所以访问视图只能访问到对应select语句查询涉及的列，对基表起到保密作用，限制数据访问
关联查询
联合多张表查询数据，意味着查询的结果集中字段来自多张表
连接条件
- 连接条件用于关联两张表中数据的对应关系
- 注：在关联查询中通常两张表之间要指定连接条件，否则会产生笛卡尔积，这通常是一个无意义的结果集
- 连接条件的数据：N张表关联查询，至少要N-1个连接条件
笛卡尔积
- 关联查询时，若不指定连接条件则会产生笛卡尔积，由于没有连接条件，因此两表对应时比对条件会全部成功，即A表的一条记录对应B表的所有记录
- 笛卡尔积的查询结果记录数为两表记录数的乘积
- 笛卡尔积通常是无意义的集合
语法格式
使用表别名对表进行命名，增加代码可读性
SELECT teacher.`name` AS 'teacher_name',
`subject`.`name` AS 'subject_name'
FROM teacher,`subject`
WHERE teacher.subject_id=`subject`.id;
# 使用表别名
SELECT t.`name` AS 'teacher_name',
s.`name` AS 'subject_name'
FROM teacher t,`subject` s
WHERE t.subject_id = s.id;
连接条件与过滤条件要同时满足，即连接条件要与过滤条件用AND连接
SELECT t.`name`,c.`name`
FROM teacher t,class c
WHERE t.id=c.teacher_id AND t.`name`='王克晶';
关联查询中使用聚合函数
SELECT COUNT(*)
FROM teacher t, class c, student s
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND t.`name`='范传奇';
SELECT AVG(t.salary)
FROM teacher t,`subject` s
WHERE t.subject_id=s.id
AND s.`name`='语文';
案例
- 查询全校最小的同学的班主任是谁
SELECT DISTINCT t.`name`
FROM student s,teacher t, class c
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND s.birth = (SELECT MAX(birth) FROM student);
- 查询全校最小的学生来自哪个城市，所在哪个班及班主任是谁？
SELECT s.`name`,
l.`name`,
c.`name`,
t.`name`
FROM student s,location l, class c, teacher t
WHERE s.class_id=c.id
AND s.location_id=l.id
AND c.teacher_id=t.id
AND s.birth = (SELECT MAX(birth) FROM student);
多对多关系
- 当两张表双向都是一对多时，就是多对多关系
- 多对多关系无法在两张表中实现，多对多关系需要引入关联关系表
SELECT stu.`name` AS 'student_name',
sub.`name`AS 'subject_name',
tss.score
FROM student stu, t_stu_subject_score tss, `subject` sub
WHERE tss.stu_id=stu.id
AND tss.subject_id=sub.id
AND sub.`name`='语文';
内连接
内连接时关联查询的另一种写法
- 多张表关联时，连接的表写在JOIN子句中并在ON中指定连接条件
- WHERE子句仅用来添加过滤条件
- 相较于关联查询写法可读性更好
SELECT s.`name`, s.age, c.`name`
FROM student s
JOIN class c ON s.class_id = c.id
WHERE c.`name` = '1年级1班'
SELECT c.`name`, t.`name`
FROM teacher t
INNER JOIN class c ON c.teacher_id = t.id
GROUP BY c.`name`;
外连接
- 外连接也是关联查询
- 关联查询仅会将满足连接的条件的记录查询出来
- 外连接的特点是将不满足连接条件的记录也查询出来
左外连接
以JOIN左侧表作为驱动表，该表记录不管是否满足条件，均查出，不满足连接条件时右侧表的字段补NULL
SELECT c.`name`, t.`name`
FROM class c
LEFT JOIN teacher t ON c.teacher_id = t.id;
SELECT c.`name`, t.`name`
FROM class c
LEFT OUTER JOIN teacher t ON c.teacher_id = t.id;
右外连接
以JOIN右侧表作为驱动表，该表记录不管是否满足条件，均查出，不满足连接条件时左侧表的字段补NULL
SELECT c.`name`,t.`name`
FROM class c
RIGHT JOIN teacher t ON c.teacher_id = t.id;
SELECT c.`name`,t.`name`
FROM class c
RIGHT OUTER JOIN teacher t ON c.teacher_id = t.id;
全外连接
Oracle中右全外连接（FULL OUTER JOIN），MySQL没有
MySQL可以使用并集（UNION）实现全外连接操作
- UNION可以将多个查询结果集并在一起
- select子句中指定的字段个数、顺序、类型必须完全一致
- 不同结果集中的重复数据仅保留一次
- 将左外连接和右外连接的结果集并到一起即可达到全外连接的效果
SELECT c.`name`, t.`name`
FROM class c
LEFT JOIN teacher t ON c.teacher_id = t.id;
UNION
SELECT c.`name`,t.`name`
FROM class c
RIGHT JOIN teacher t ON c.teacher_id = t.id;
# 查看姓张和姓李的学生各有多少人
SELECT COUNT(*)
FROM student s
WHERE s.name LIKE '张%'
UNION
SELECT COUNT(*)
FROM student s
WHERE s.`name` LIKE '李%';
自连接
- 当一张表中的一条记录可以对应其自己的其他多条记录时，就是自连接
- 自连接时发生在同一张表内
- 当一组具有相同属性的属性又存在上下级关系时（树状结构数据），可使用自连接
- 该表中存在一个外键，记录了本表的主键字段
查询时将一张表当作两张表查即可
SELECT t.`name` AS '老师',
m.`name` AS '领导'
FROM teacher t, teacher m
WHERE t.manager = m.id;
主键和外键
主键PK：
- 表中可以唯一标识该表的字段
- 唯一且非空，可以使用主键约束 PRIMARY KEY 对其定义
外键FK：
- 表中某一字段是另一张表的主键，则该字段为外键
- 设计有外键字段值的表在关联关系中处于关联关系中一对多中多的一方
数据类型
NULL
- null表示不存在，为空
- 不是一个特定的数值
- null和任何值相加均为null
- null也不能进行关系运算，即不能用><比较
小数类型
DOUBLE(int a, int b)
- a：整数加小数最多a位
- b：小数位数最多b位
定长字符串
长度固定，查询数据快，当存储的数据不足字符串长度时会自动补满
缺点：浪费空间
- CHAR：默认1字符，最大255字符
- TEXT：无需指定长度，最高可保存64kb的字符数据
变长字符串
优点：不浪费空间，根据数据大小分配
缺点：查询速度慢
- VARCHAR：最大长度65535
日期类型
-
DATE：可以保存年、月、日
-
TIME：可以保存时、分、秒
-
DATETIME：可以保存年月日时分秒
可以省略时分秒（默认自动补充00:00:00），不能省略年月日
yyyy-MM-dd hh:mm:ss -
TIMESTAMP：时间戳，保存UTC时间，可以精确到毫秒
约束
主键约束
PRIMARY KEY
- 主键字段的值用来唯一标识表中的一条记录
- 非空且唯一
- 一张表中只能由一个主键约束
CREATE TABLE person(
id INT PRIMARY KEY,
age INT UNSIGNED
); #建表时添加主键
ALTER TABLE person ADD PRIMARY KEY (id); #修改已经建好的表的字段为主键
自增约束
AUTO_INCREMENT
- 只能设置在主键上，所有也只能有一个
- 标注的字段在添加新记录时，会自动加一
- 主键设置自增后，插入数据时则不需要再手动插入该字段数据
- 主键设置自增后插入null值也不会生效，主键依然会自增
CREATE TABLE person1(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT UNSIGNED
);
ALTER TABLE person CHANGE id id INT auto_increment;
非空约束
NOT NULL
- 可以用在任意字段上
- 标注该约束后，该字段在添加数据时不能为空
- 也不能直接传入null值
CREATE TABLE person(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT UNSIGNED NOT NULL
);
ALTER TABLE person CHANGE age age INT(10) NOT NULL;
唯一性约束
UNIQUE
- 可以用在任意字段上
- 标注该约束后，该字段在添加数据时不能重复
- 注：若在非空字段上加唯一性约束时需要将UNIQUE和NOT NULL同时添加，否则UNIQUE会将非空约束打开
- 多个null不算重复
- 对已存在字段添加唯一性约束时，要保证该字段的现有数据没有重复，否则报错Duplicate entry ‘’ for key '字段 ’
CREATE TABLE person1(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT UNSIGNED UNIQUE #无符号整数，只能插入正数，否则报错
);
ALTER TABLE person ADD name VARCHAR(10) UNIQUE AFTER id;
检查约束
CHECK
- 检查约束可以用在任意字段上
- 运行自定义一些检查规则
CREATE TABLE person1(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT UNSIGNED NOT NULL UNIQUE CHECK(age>0 AND age<120)
);
外键约束
FOREIGN KEY
- 外键约束要求外键字段必须保存主键字段含有的值或NULL
- 删除主键记录时若有外键存有该主键的值，则不允许删除，只有无外键绑定时该主键才可删除
- 级联删除：删除主键字段，会将与其关联的外键值全部删除
- 实际开发中经常不适用外键约束
JDBC
JDBC定义接口
数据库厂商实现接口，依赖包
程序员调用接口
- JAVA访问数据库解决方案
- jdbc定义一套标准接口，使用一种方式访问不同的数据库
JDBC工作流程
-
加载驱动，建立连接
- Driver时JDBC定义的接口，不同数据库有不同的驱动
- 使用
Class.forName("驱动类完全限定名")加载驱动 - Connection类继承了AutoCloseable类，所以可以放在try(…){}中进行自动关闭
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.15</version> </dependency>Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/tedu?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true", "root", "root"); -
创建语句执行对象
- Statement类继承了AutoCloseable类，所以可以放在try(…){}中进行自动关闭
Statement statement = connection.createStatement(); -
执行SQL语句
execute方法的返回值为boolean
- true if the first result is a ResultSet object;
- false if it is an update count or there are no results
String sql = "CREATE TABLE student1(\n" + " id INT AUTO_INCREMENT PRIMARY KEY ,\n" + " name VARCHAR(30),\n" + " age INT(3),\n" + " gender VARCHAR(10),\n" + " job VARCHAR(30),\n" + " birth DATE,\n" + " location_id INT,\n" + " team_leader INT,\n" + " class_id INT\n" + ");"; statement.execute(sql); -
处理结果集
-
关闭连接
connection.close(); statement.close();
预编译SQL语句
PreparedStatement
- 预编译SQL使用占位符
?来表示使用的值 - 占位符内容仅需表达需要用到的值，不影响SQL语义
- 使用预编译SQL语句可以防止SQL注入，恶意sql
?只能替换值，不能使用?代替表、关键字等会影响SQL语句语义的部分- 优势：提高性能，执行sql语句之后会在数据库中生成针对该sql语义的执行计划，不同的sql语句会重新生成执行计划，而对于同样的sql语句则会使用之前的执行计划，加快访问速度，预编译sql可以代表一类sql语句的语义，当数据库遇到这类的sql时均会使用预编译sql当时创建的执行计划，所以访问速度会加快
try (
Connection connection = DBUtil.getConnection();
){
User user = InputUtil.getInputObject(new User(), "请登录", "登录");
String sql = "select name,password,nickname,age from student1 where name = ? and password = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, user.getUsername());
preparedStatement.setString(2, user.getPassword());
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
} catch (Exception e) {
e.printStackTrace();
}
430
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
