数据库-mysql

最新推荐文章于 2024-07-12 14:26:59 发布

ZJH_12138

最新推荐文章于 2024-07-12 14:26:59 发布

阅读量831

点赞数

分类专栏： mysql 文章标签：数据库 mysql database

本文链接：https://blog.csdn.net/qq_46111881/article/details/121324793

版权

mysql 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

一、数据库的介绍

1.1 目前市场上的数据库种类

MYSQL：开源免费的数据库，小型的数据库，已经被Oracle收购了。MYSQL6.x版本也开费；
Orcale：收费的大型数据库，Orcale公司的产品。Oracle收购SUN公司，收购了MYSQL。
DB2：IBM公司的数据库产品，收费的。常应用与银行系统中。
SQLServer：微软公司收费的中型的数据库。C#、.net等语言常使用。
SyBase：已经逐渐退出历史舞台。
SQLite：嵌入式的小型数据库，应用于手机端。
常用的数据库：MYSQL、Orcale

1.2 数据库的基本概念

数据库的英语单词：DataBase，简称DB；
数据的作用：用于存储和管理数据的仓库；
数据库的特点：
- 持久化存储数据。其实数据库就是一个文件系统；
- 方便存储和管理数据；
- 使用了统一个方式操作数据库；

1.3 数据的启动、关闭、登录、退出

数据库的打开关闭
- 使用管理员权限打开cmd
  - net start mysql：开启mysql的服务；
  - net stop mysql：关闭mysql服务；
mysql的登录：
- （1）mysql -u数据库名称 -p数据库密码；（连接的是自己本机的数据库）；
- （2）mysql -u数据库名称 -p 【回车】，输入密码（连接的是自己本机的数据库）；
- （3）mysql -u数据库名称 -h【目标ip】 -p目标的密码（连接的是别人的数据库）
mysql的退出：
- exit
- quit

二、SQL

2.1 什么是SQL

SQL：结构化查询语言；

2.1.1 数据类型

timestamp：时间错类型，包含年月日时分秒；yyyy-MM-dd HH：mm：ss；
- 如果将来不给这个字段赋值，或者赋值为null，则默认使用当前的系统时间，来自动赋值；

2.2 SQL通用语法

2.2.1 书写格式

SQL语句可以单行或者多行书写，用分号结尾；
可以使用空格和缩进来增强语句的可读性；
MySQl数据库的SQL语句不区分大小写。

2.2.2 SQL的注释

单行注释：
- –【空格】注释内容；
- #注释内容；
多行注释：和C语言一样；

2.3 SQL的分类

2.3.1 DDL数据定义语言

用来定义数据库对象：数据库、表、列等；
关键字：create、drop、alter等；

2.3.2 DML数据操作语言

用来对数据库中的表的数据进行增删改。
关键字：insert、delete、update等；

2.3.3 DQL数据查询语言

用来查询数据库中表的记录。
关键字：select、where等

2.3.4 DCL数据控制语言

用来定义数据库的访问权限和安全级别，及创建用户。
关键字：GRANT、REVOKE等；

2.4 DDL：操作数据库、表

2.4.1 操作数据库【CRUD】

C(create)：创建数据库；
- 创建数据库
  - create database 数据库名称；
- 创建数据库，判断不存在，再创建；
  - create database if not exists 数据库名称；
- 创建数据库，并且指定字符集；
  - create database 数据名称 character set 字符集名称；
- 【练习】：创建数据库db，判断是否存在，并指定字符集为gbk；
  - create database if not exists db character set gbk;
R（retrieve）：查询；
- 查询所有数据库的名称
  - SHOW DATABASES;
- 查询某个数据库的字符集；查看某个数据的创建语句；
  - SHOW CREATE DATABASE 数据库名称；
U（update）：修改；
- 修改数据库的字符集
  - alter database 数据库名称 character set 字符集名称；
D（delete）：删除；
- 删除数据库；
  - drop database 数据库名称；
- 判断数据库是否存在，存在则删除；
  - drop database if exists 数据库名称；
使用数据库
- 查询正在使用的数据库；
  - select database();
- 使用数据库
  - use 数据库名称；

2.4.2 操作表

C(create)：创建表
- 格式：
  - create table 表名 (
    
    列名1 数据类型，
    
    列名2 数据类型，
    
    ……………………
    
    列名n 数据类型**【最后一行不要逗号】**
    
    );
- 【练习】：创建一张表
  
  create table student(
  
  id int,
  
  name varchar(32),
  
  age int,
  
  score double(4,1),
  
  birthday date,
  
  insert_time timestamp
  
  );
R（retrieve）：查询表
- 查询某个数据库里面所有的表；
  - show tables；
- 查询表的结构；
  - desc 表名；
U（update）：修改
- 修改表名；
  - alter table 表名 rename to 新的表名；
- 查看表的字符集；
  - show create table 表名；
- 修改表的字符集；
  - alter table 表名 character set 字符集名称；
- 添加一列；
  - alter table 表名 add 列名数据类型；
- 修改列的名称、类型；
  - 即修改列名，也修改该列的数据类型
    - alter table 表名 change 列名新的列名新的数据类型；
  - 只修改列的数据类型；
    - alter table 表名 modify 列名新的数据类型；
- 删除列；
  - alter table 表名 drop 列名；
D（delete）：删除
- 删除表
  - drop table 表名；
- 判断表是否存在，若存在则删除；
  - drop table if exists 表名称；
复制表（复制的是格式，而不是内容）
- create table 表名 like 已存在的表的名称；

2.5 DML增删改表中的数据

2.5.1 添加数据

语法
- insert into 表名(列名1，列名2，……列名n) values(值1，值2，……，值n)；
- 注意：
  - 列名和表名要一一对应。
  - 如果表名后面不定义列名，则默认给所有列添加值；
    - insert into 表名 values(值1，……值n)；【有多少列，就得给多少个值】
  - 除了数字类型。其他类型都需要使用引号（单双都可以）括起来；

2.5.2 删除数据

删除满足条件的信息
- delete from 表名 where 条件；
删除所有的信息
- delete from 表名；
- truncate table 表名；
  - 删除表，然后再创建一个一模一样的表。
  - 这个删除表中所有信息方式的效率要比第一个删除表中所有信息方式的效率高；

2.5.3 修改数据、

语法
- update 表名 set 列名1 = 值1，列名2 = 值2，……，列名n = 值n where 条件；
- 注意：
  - 如果不加条件，则表中被修改的列的值都会被修改；

2.6 DQL：查询表中的记录

2.6.1 语法;

select

字段列表

from

表名列表

where

条件列表

group

分组字段

having

分组之后的条件

order by

排序

limit

分页限定

2.6.2 基础查询

多个字段的查询
- select 字段1，字段2，…… from 表名；
去除重复
- select distinct 字段名 from 表名；
- 【练习】：select 地址 from stu；
  - 结果就是会在查询到的结果集中，重复的地址名只保留一个。
- 【练习】：select distinct 姓名，地址 from stu；
  - 只有当姓名和地址都重复时，才会将该重复的值只保留一个。
计算列
- 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）；
- ifnull(表达式1，表达式2)：null参与的运算结果都为null；
  - 表达式1：判断该字段的值是否为null
  - 如果该字段为null，则用表达式2将表达式2的null替换掉；
起别名
- 【练习】：计算数学和英语的和值；
  - select name，math ， English， ifnull（math，0）+ ifnull（English，0） from stu；
  - 注意事项：
    - 如果有null参与计算，那么结果都为null。
    - 解决方案：ifnull，该语句的作用就是，如果判断出该列的某个值为null，则将该列的值改为指定的值。例如ifnull(math,0)，意思就是，如果某个人的数学成绩为null，那么我们就将null改为0，再进行计算，从而避免加null参与运算。
    - 这样到时会将math+English当做列名，名称表达的意思不明确。因此需要改名称。
- 【练习】：计算数学和英语的和值，并将和值所在列的名称改为”总分“；
  - name，math ， English， ifnull（math，0）+ ifnull（English，0）as 总分 from stu；
  - as可以省略；
条件查询
- where子句后面跟条件
- 运算符
  - 大于、小于、>=、<=、=、<>(不等于)
  - between…and
  - in(集合)
  - like：模糊查询
    - 占位符
      - _：可以代替任意一个字符；
      - %：可以代替任意多个字符；
  - and 或 &&
  - or 或 ||
  - not 或！
- 【练习】：
  - 查询年龄大于等于20
    - select * from stu where age >= 20;
  - 查询年龄不等于20
    - select * from stu where age <> 20;
    - select * from stu where age != 20;
  - 查询年龄大于等于20，小于等于30的；
    - select * from stu where age between 20 and 30；
    - select * from stu where age >= 20 && age <= 30;
  - 查询年龄22岁、19岁、25岁；
    - select * from stu where age = 22 or age = 19 or age = 25；
    - select * from stu where age in (22, 19, 25);
  - 查询英语成绩为null；
    - select * from stu where English is null；
    - 注意事项：不能写=null；
  - 查询英语成绩不为null；
    - select * from stu where English is not null；
    - 注意事项：不能写!=null;
  - 查询性马的人：
    - select * from stu where name like ‘马%’；
  - 查询名字里面第二个字是【化】的人；
    - select * from stu where name like ‘_化%’；
  - 查询名字是三个字的人；
    - select * from stu where name like ‘_ _ _’;
  - 查询名字里面包含马的人；】
    - select * from stu where name like ‘%马%’；

2.6.3 排序查询

语法：order by 子句；
排序方式：
- ASC：升序（默认就是升序）；
- DESC：降序
注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件；
【练习】：按照数学成绩升序排序；
- select * from stu order by math；
- select * from stu order by math ASC;
【练习】：按照数学成绩升序排名，如果数学成绩一样，则按照英语成绩升序排名；
- select * from stu order by math ASC，English ASC；

2.6.4 聚合查询

count：计算个数；
- 一般选择非空的列：主键
- count(*)：不建议使用；
max：计算最大值；
min：计算最小值；
sum：计算和；
avg：计算平均值；
- 如果一个人的成绩为null，那么使用该函数就不会将这个人参与与计算，也就是相当于班里没人这个人。这是不正确的，因为，即使该人的成绩为null，但是在计算平均分时，他也应该算在总人数里面
注意事项：
- 聚合函数的计算是排除null值的。
- 解决方案：
  - 选择不包含非空列计算；
  - 使用ifnull函数；
【练习】：计算学生人数；注意null值
- select count(ifnull(id,0)) from stu;
【练习】：计算英语成绩的最大值和最小值；
- select max(english) from stu;
- select min(english) from stu;
【练习】：计算所有人的英语成绩的平均值注意null值
- select avg((ifnull(english,0)) from stu;
【练习】：计算所有人的英语成绩总和；
- select sum(english) from stu;

2.6.5 分组查询

语法：group by 子句；
注意事项：
- 分组之后的查询字段：分组字段、聚合函数；
- where 和having 的区别：
  - where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来；
  - where后面不能跟聚合函数，having可以跟聚合函数的判断；
【练习】：按照性别分组，分别查询男生和女生的平均成绩；注意null值
- select sex，avg(ifnull(english,0)) from stu group by sex;
【练习】：按照性别分组，分别查询男生和女生的平均成绩，以及人数；注意null值
- select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu group by sex;
【练习】：按照性别分组，分别查询男生和女生的平均成绩，以及人数。要求：分数低于70分的人不参与分组；注意null值
- select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu where english > 70 group by sex;
【练习】：按照性别分组，分别查询男生和女生的平均成绩，以及人数。要求：分数低于70分的人不参与分组,且分组之后，该组人数要大于2；注意null值
- select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu where english > 70 group by sex having count(ifnull(id,0)) > 2;
- select sex, avg(ifnull(english,0))，count(ifnull(id,0)) as 人数 from stu where english > 70 group by sex having 人数 > 2;

2.6.6 分页查询

语法：limit 开始索引，每页查询的条数；
公式：开始索引 = （当前页码 - 1）*每页查询的条数；
【练习】：每页显示3条；
- select * from stu limit 0,3; --第一页
- select * from stu limit 3,3; --第二页
- ……
limit是MySQL的一个【方言】

2.7 约束

概念：对表中的数据进行限定，保证数据的正确性、有效性、完整性。

2.7.1 非空约束

关键字：not null；
作用：某一列的值不能为null；

创建表时添加约束；

create table stu(
	name varchar(4) not null
);

表创建好后，添加非空约束；

--创建好一张表
create table stu(
	name varchar(4)
);

--添加约束
alter table stu  modify  name varchar(4) not null;

删除约束
- 【练习】：将姓名的非空约束删除；
```
alter table stu modify name varchar(4);
```

2.7.2 唯一约束

关键字：unique；
作用：某一列的值不能重复；

创建表时添加唯一约束

create table stu(
	phone_number varchar(11) unique
);

表创建好后，添加唯一约束；

alter table stu modify phone_number varchar(11)  unique;

删除唯一约束

alter table stu drop index phone_number;

注意事项;
- 唯一约束可以有null值，但是只能有一条记录为null。通俗来讲，也就是说null值也不能重复出现；

2.7.3 主键约束

关键字：primary key；
作用：非空且唯一；

创建表时添加主键约束；

create table stu(
	id int primary key
);

表创建好后添加主键约束；

alter table stu modify id int primary key;

删除主键
```
alter table stu drop primary key;
```
注意事项：
- 一张表只能有一个字段为主键；
- 但是可以设置为多个字段为主键，也即联合主键；
- 主键就是表中记录的唯一标识；

2.7.4 自动增长

关键字：auto_increment；
作用：如果某一列是数值型的，可以完成值的自动增长。

在创建表时添加自动增长；

create table stu (
	id int auto_increment
);

表创建好后添加自动增长

alter table stu modify id int auto_increment;

删除自动增长

alter table stu modify id int;
--这样不会删除掉主键约束

注意事项：
- 这个值的增长，是按照上一条的数据进行增长。如果上一条数据是5，那么下一条就是6。
- 一般情况下，自动增长和主键一起使用；

2.7.5 外键约束

关键字：foreign key；
作用

在创建表时添加外键约束

create table emp(
	dep_id int
    constraint  起一个新的名称  foreign  key  (外键名称) references 主表名称(主表列名称);
);

【练习】：添加外键

--主表
CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);

CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_id INT,--外键名称
	CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);

创建表后添加外键；

alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id);

删除外键；

alter table employee  drop foreign key emp_dep_fk;

级联操作

添加外键，设置级联更新；

alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) on update  cascade;

添加外键，设置级联更新，设置级联删除；

alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) on update  cascade on delete cascade;

2.7 DCL：管理用户、授权

2.7.1 管理用户

2.7.1.1 添加用户

-- 语法：create user '用户名'@'主机名' identified by '密码'；
create user '周佳辉' @ 'localhost' identified by '123';

2.7.1.2 删除用户

-- 语法：drop user '用户名' @ '主机名'；
drop user '周佳辉' @ 'localhost';

2.7.1.3 修改用户密码

-- 方法一：
update user set password = password('新密码') where user = '用户名';

-- 方法二：
set password for '用户名'@'主机名' = password('新密码');

2.7.1.4 查询用户

先切换到mysql数据库
```
use mysql;
```
查询user表
```
select * from user;
```

2.7.1.5 在忘掉数据库密码的情况下修改用户密码

三、数据库的设计

3.1 多表之间的关系

3.1.1 一对一

【例子】
- 人和身份证：
- 分析：一个人只有一个身份证，一个身份证只能对应一个人。
- 实现方式：在任意一方设置唯一约束的外键指向另一方的主键；
- 实现方式的图解

3.1.2 一对多（多对一）

【例子】
- 部门和员工：
- 分析：一个部门有多个员工，一个员工只能对应一个部门；
实现方式：在多的一方建立外键，指向一的一方的主键。
实现方式的图解

3.1.3 多对多

【例子】
- 学生和课程：
- 分析：一个学生可以选择很多门课，一个课程也可以被很多学生选择；
实现方式：多对多需要借助第三张中间表。
实现方式的图解

3.2 数据库设计的范式

3.2.1 第一范式（1NF）

每一列都是不可分割的原子数据；
第一范式的举例，以及第一范式存在的问题；

3.2.2 第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于候选码；（在1NF基础上消除非主属性对主码的部分函数依赖）
几个基本概念
- 函数依赖：A–>B，如果通过A属性(属性组)的值，可以确定唯一B属性的值，则称B依赖于A；
  - 【例如】：学好–>姓名。（学号，课程名称）–>分数；
- 完全函数依赖：A–>B，如果A是一个属性组，则B属性的值确定需要依赖于A属性组中所有的属性值；
  - 【例如】：（学号，课程名称）–>分数；
- 部分函数依赖：A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可；
  - 【例如】：（学号，课程名称）–>姓名；
- 传递函数依赖：A–>B，B–>C，如果A属性(属性组)的值，可以确定唯一B属性的值，再通过B属性的(属性组)的值可以确定唯一C属性的值，则称C传递依赖于A。
  - 【例如】：学号–>系名，系名–>系主任
- 码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性(属性组)为该表的码。
  - 【例如】：该表中的码为：(学号，课程名称)；
    - 主属性：码属性组中的所有属性；
    - 非主属性：除过码属性组的属性；
解决的问题：解决了第一范式中存在的的第一个问题；（问题在上述截图中）

3.2.3 第三范式（3NF）

在2NF的基础上，任何非主属性不依赖于其他非主属性。（在2NF基础上消除传递依赖）
解决的问题：解决了第一范式中存在的第二个和第三个问题；（问题在上述截图中）

3.3 数据库的备份和还原

命令行运行：
- 语法：
  - 备份：mysqldump -u用户名 -p密码要备份的数据库名称 > 保存路径；
  - 还原：
    - 登录数据库：-u用户名称 -p密码；
    - 创建数据库：create database 数据库名称；
    - 使用数据库：use 数据库名称；
    - 执行文件：source 文件路径；
图形化工具还原；
- 【百度就行】

四、多表查询

4.1 笛卡尔积

有两个集合A、B，取这两个集合的所有组成情况。
要完成多表查询，需要消除无用的数据。

4.2 多表查询的分类

4.2.1 内连接

4.2.1.1 隐式连接

使用where条件消除无用数据；
【练习】：查询所有员工信息和对应的部门信息；
```
select * from emp, dept where emp.dept_id = dept.id;
```

【练习】：查询员工表的姓名，性别、部门表的名称；

select emp.name, emp.sex, dept.name from emp, dept where emp.dept_id = dept.id;

--简化,通过对表起个别名进行简化；
--标准SQL语句写法；

select 
	t1.name,
    t1.sex,
    t2.name
from
	emp t1,
    dept t2
where
	t1.dept_id = t2.id;

4.2.1.2 显示内连接

语法：select 字段列表 from 表名1 inner join 表名2 on 条件；

【练习】：查询所有员工信息和对应的部门信息；

select * from emp inner join dept on emp.dept_id = dept.id;

-- inner可以省略，并且这个也可以像上面那样起别名；

使用内连接需要明确的东西：
- 从那些表中查询；
- 条件是什么；
- 查询哪些字段；

4.2.2 外连接

4.2.2.1 左外连接

语法：select 字段列表 from 表1 left outer join 表2 on 条件；【注意：outer可以省略不写】
查询的范围：查询的是左表所有的信息，以及其与右表的交集部分；

【练习】：

select t1.*, t2.name  from  emp t1 left join dept  t2 on t1.dept = t2.id;

4.2.2.2 右外连接

语法：select 字段列表 from 表1 right outer join 表2 on 条件；【注意：outer可以省略不写】
查询的范围：查询的是右表所有的信息，以及其与左表的交集部分；
```
select t1.* t2.name from emp t1 right join dept t2 on t1.dept = t2.id;
```

4.2.3 子查询

概念：查询中嵌套查询，称嵌套查询为子查询。

【练习】：查询工资最高的员工信息；

-- 传统写法
-- 首先查询最高工资是多少
select max(工资) from emp; -- 假设查询出来最高工资是9000；
-- 然后将查询出来的信息作为条件在进行查询；
select * from emp where  emp.工资 = 9000;



-- 子查询方式
select * from emp where emp.工资 = (select max(工资) from emp);

4.2.3.1 子查询的不同情况

子查询的结果是单行单列的；
- 子查询可以作为条件，使用条件运算符去判断；
- 【练习】：查询工资小于平均工资的员工信息；
```
select * from emp where emp.工资 < (select avg(ifnull(工资, 0)) from emp);
```
子查询的结果是多行单列的；
- 子查询可以作为条件，使用条件运算符去判断；
- 【练习】：查询市场部、销售部所有的员工信息；
```
select * from emp where emp.dept_id in (select dept_id  from dept where  name in ("市场部", "销售部"));
```

子查询的结果是多行多列的；

子查询可以作为一张虚拟的表参与查询；

【练习】：查询员工入职日期是2011-11-11日之后的员工信息和部门信息；

select * from dept t1, (select * from emp where emp.date > "12-11-11") t2 where t1.id = t2.dept_id; 


-- 普通查询
select * from emp t1, dept t2 where t1.id = t2.dept_id and t1.date > "2011-11-11";

4.3 练习

4.3.1 练习代码

CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(20),
	loc VARCHAR(20)
);

CREATE TABLE job(
	id INT PRIMARY KEY AUTO_INCREMENT,
	jname VARCHAR(10),
	description INT
);

CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	job_id INT,
	FOREIGN KEY (job_id) REFERENCES job(id),
	joindate DATE,
	salary VARCHAR(10),
	bonus INT,
	dep_id INT,
	FOREIGN KEY (dep_id) REFERENCES dept(id)	
);

CREATE TABLE sala(
	grade INT PRIMARY KEY AUTO_INCREMENT,
	losalary INT,
	hisalary INT
);

4.3.2 练习题

查询所有的员工信息。查询员工编号、姓名、工资、职务名称、职务描述；
- 分析：
  - 员工编号、姓名、工资、需要查询emp表。职务名称、职务描述需要查job表。
  - 查询条件：emp.job_id = job.id;
- 实现代码：
```
select
	t1.id,
	t1.ename,
	t1.salary,
	t2.jname,
	t2.description
from
	emp t1, job t2
where
	t1.job_id = t2.id;
```
查询员工编号、姓名、工资、职务名称、职务描述、部门名称、部门位置；
- 分析：
  - 员工编号、姓名、工资查询emp表，职务名称、职务描述查询job表，部门名称、部门位置需要查询dept表；
  - 条件：emp.job_id = job.id and emp.dep_id = dept.id
- 代码：
```
select
	t1.id,
	t1.ename,
	t1.salary,
	t2.jname,
	t2.description,
	t3.dname,
	t3.loc
from
	emp t1, job t2, dept t3
where
	t1.job_id = t2.id and t1.dep_id = t3.id;
```
查询员工姓名、工资、工资等级；
- 分析：
  - 员工姓名、工资、查询emp表，工资等级查询salary表；
  - 条件：emp.salary >= salary.losalary and emp.salary <= salary.hisalary
- 代码
```
select
	t1.ename,
	t1.salary,
	t2.grade
from
	emp t1, salary t2
where
	t1.salary >= t2.losalary and t1.salary <= t2.hisalary;
```
查询部门编号、名称、位置、人数；
- 分析：
  - 部门编号、名称、位置、查询dept表，人数查询emp表；
  - 使用分组查询。按照emp》dep_id完成分组，查询count(id)；
  - 使用子查询将第二部的查询结果和dept表进行关联查询；
- 代码：
```
select
	t1.id,
	t1.dname,
	t1.loc,
	t2.total
from
	dept t1, 
	(select
     	dep_id, count(id) total
     from
     	emp
     group by
     	dep_id
    ) t2
where t1.id = t2.dep_id;
```

五、事物

5.1 事物的基本介绍

概念：如果一个包含多个步骤的业务操作，被事物管理，那么这些操作要么同时成功，要么同时失败；

5.1.1 事物的操作

操作步骤：
- 开启事物：start transaction;
- 回滚：rollback;
- 提交：commit;
MySQL数据库中事物默认自动提交；
- 事务提交的两种方式：
  - 自动提交
    - mysql就是自动提交的；
    - 一条DML（增删改）语句会自动提交一次事物；
  - 手动提交：
    - 需要先开启事物，再提交；
- 修改事务的提交方式：
  - 查看事物的提交方式
```
select @@autocommit;
-- 1代表自动提交；
-- 0代表手动提交；
```
  - 修改默认提交方式：
```
-- 修改成手动
set @@autocommit = 0;

-- 修改成手动提交后，需要写上commit。不然只会临时改变数据。不会持久更改；
```

5.2 事物的四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败；
持久性：当事务提交或回滚后，数据库会持久化的保存数据；
隔离性：多个事物之间，相互隔离；
一致性：事务操作前后，数据总量不变；

5.3 事务的隔离级别（了解）

概念：多个事物之间隔离的、相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在的问题：
- **脏读：**一个事务，读取到另一个事务中没有提交的数据；
- **不可重复度(虚读)：**在一个事务中，两次读取到的数据不一样；
- **幻读：**一个事务操作(DML)数据表中的所有记录，另一个事务添加了一条数据，则第一个事物查询不到自己的修改。
隔离级别：
- read uncommitted : 读未提交
  - 产生的问题：脏读、不可重复读、幻读；
- read committed : 读已提交
  - 产生的问题：不可重复读、幻读；
- repeatable : 可重复读（Oracle 默认）
  - 产生的问题：幻读；
- serializable : 串行化
  - 产生的问题：可以解决所有的问题；
- **注意：**隔离级别从小到大安全性越来越高，但是效率越来越低；
- 查询数据隔离级别的语句：
```
selece @@tx_isolation;
```
- 数据库隔离等级的设置语句：
```
set global transation isolation level 级别字符串；
```

5.4 权限的管理

5.4.1 查询权限

语法：show grants for ‘用户名’@‘主机名’;
show grants for ‘list’@‘localhost’;

5.4.2 授予权限

语法：grant 权限列表 on 数据库.表名 to ‘用户名’@‘主机名’;
一次授予所有权限

grant all on * . * to ‘用户名’@‘主机名’；

5.4.3 撤销权限

revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’；

六、JDBC（数据库连接）

6.1 JDBC基本概念

jdbc：java database connectivity ，java数据库连接，使用java语言来操作数据库；
jdbc的本质：是sun公司定义的一套操作所有数据库的规则，即接口。各个数据库厂商实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（jdbc）编程，真正执行的代码是驱动jar包中的实现类。

6.2 jdbc的使用

6.2.1 使用步骤

导入驱动jar包；
注册驱动；
获取数据库连接对象；Connection
定义SQL；
获取执行SQL语句的对象；Statement
执行SQL，接收返回结果；
处理结果；
释放资源；

6.2.2 详解各个对象

DirverManager：驱动管理对象；
- 功能：
  - 注册驱动：告诉程序该使用哪一个数据库驱动jar；
```
static void registerDriver(Dirver dirver):注册与给定的驱动程序DriverManager。
写代码使用：Class.forName("com.mysql.jdbc.Dirver");
通过查看原码发现：在com.mysql.jdbc.Driver类中存在静态代码块：
static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
    注意：mysql5之后的驱动jar包可以省略注册驱动的步骤。
```
  - 获取数据库连接
    - 方法：getConnection(String url, String user, String password);
    - 参数：
      - URL：指定连接路径
        语法jdbc:mysql://ip地址（域名):端口号/数据库名称；
        例子：jdbc:mysql://127.0.0.1:3306/db3;
        细节：如果连接的是本机mysql服务器，并且mysql服务器默认是端口3306，则URL可以简写为：jdbc:mysql:///数据库名称；
      - user：用户名；
      - password：密码；
Connection：数据库连接对象；
- 功能：
  - 获取执行SQL的对象
    - Statement createStatement();
    - PreparedStatement preparestatement(String sql);
  - 管理事物：
    - 开启事务：setAutoCommit(boolean autoCommit)：调用该方法设置参数为false，即开启事务；
    - 提交事务：commit();
    - 回滚事务：rollback();
Statement：执行SQL的对象；
- 功能SQL：
  - boolean execute(String sql)：可以执行任意的SQL（了解就行）；
  - int executeUpdate(String sql)：执行DML（insert、update、delete）语句、DDL（create、alter、drop）语句
    - 返回值代表的是：影响的行数（比如执行之后修改了表的两行数据，则会返回2）。可以通过这个影响的行数判断DML语句是否执行成功，返回值大于0的则执行成功，反之失败；
  - ResultSet executeQuery(String sql)：执行DQL（select）语句；
ResultSet：结果集对象；
- next()：游标向下移动一行；
- getXxx(参数)：获取数据；
  - Xxx：代表数据类型：如：int getInt()，String getString();
  - 参数：
    - int 代表列的编号，从1开始；eg：getString(1);
    - String：代表列名称。eg：getInt(“name”);
PreparedStatement：执行SQL的对象；（继承上面的个statement）
- SQL注入问题：在拼接SQL时，有一些SQL的特殊关键字参与字符串的拼接。会造成安全性的问题；
  - 随意输入用户名，输入密码：a’ or ‘a’ = 'a
  - sql : select * from interface where name = ‘ahagsg’ and password = ‘a’ or ‘a’ = ‘a’;
- 解决SQL注入问题：使用PreparedStatement对象来解决；
- 预编译的SQL：参数使用？作为站位符；
- 步骤：
  - 导入驱动jar包；
  - 注册驱动；
  - 获取数据库连接对象；Connection
  - 定义SQL；
    - 注意：sql的参数使用，？作为占位符。如：select * from interface where name = ？ and password = ？；
  - 获取执行SQL语句的对象；PreparedStatement
  - 给？赋值
    - 方法：setXxx(参数1，参数2)
      - 参数1：？的位置（从1开始）；
      - 参数2：？的值；
  - 执行SQL，接收返回结果，不需要传递SQL语句；
  - 处理结果；
  - 释放资源；
- 注意：后期都会使用PreparedStatement来完成增删改查的所有操作；
  - 可以防止SQL注入；
  - 效率更高；

6.2.2 练习题【登录】

存在SQL漏洞

6.3 JDBC控制事务

事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。
操作
- 开启事务：setAutoCommit(boolean autoCommit)：调用该方法，设置参数为false，即开启事务；
- 提交事务：commit();
- 回滚事务：rollback();

七、数据库连接池

7.1 概念

其实就是一个容器，存放数据库连接的容器；（当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完后，会将对象归还给容器）
好处：
- 节约资源；
- 用户访问高效；
实现：
- 标椎接口：DataSoure javax.sql包下的；
  - 方法：
    - 获取连接
      - getConnection()
      - 归还连接：Connection.close()。如果连接对象Connection是从连接池中获取的，那么调用Conn.close()方法，则不会再关闭连接了，而是归还连接；
- 一般我们不去实现它，有数据库厂商来实现；
  - C3P0 ：数据库连接池技术；
  - Druid：数据库连接池实现技术，由阿里巴巴提供；

7.2 C3P0：数据库连接池技术

7.2.1 步骤

导入jar包：c3p0和mchange-commons-java(别忘记导入数据库驱动jar包)
定义配置文件：
- 名称：c3p0.properties或者c3p0-config.xml
- 路径：直接将文件放在src目录下即可。
创建核心对象，数据库连接池对象：ComboPoolDataSource
获取连接：getConnection

7.3 Druid数据库连接池

7.3.1 步骤

导入jar包：druid
定义配置文件：
- 是properties形式的；
- 可以叫任意的名字，可以放在任意目录下；
加载配置文件：Properties；
获取数据库连接池对象：通过工厂来获取：DruildDtaSourceFactory;
获取连接：getConnection;

7.3.2 定义工具类

定义一个类：JDBCUtils
提供静态代码块加载配置文件，初始化连接池对象；
提供方法；
- 获取连接方法：通过数据库连接池获取连接；
- 释放资源；
- 获取连接池方法；

7.4 Spring JDBC

介绍：Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发；

7.4.1 步骤

导入jar包；
创建jdbcTemplate对象。依赖于数据源DataSource
- JdbcTemplate template = newJdbcTemplate(ds);
调用JdbcTemplate的方法来完成CRUD的操作；
- update()：执行DML语句。增、删、改语句；
- queryForMap()：查询结果将结果封装为map集合；
- queryForList()：查询结果将结果封装为list集合；
- query()：查询结果，将结果封装为JavaBean()对象；
- queryForObject()：查询结果，将结果封装为对象；
练习
- 需求：
  - 修改1号数据的salary 为 10000
  - 添加一条记录；
  - 删除刚才添加的记录；
  - 查询id为1的记录，将其封装为，map集合；（将列名封装为key，值为value，将这个记录封装为一个map集合）
    - 这个方法查询的结果集长度只能是1。
  - 查询所有记录，将其封装为list
    - 将每一条查询结果封装为一个map集合，再将map集合装载到list里面；
  - 查询所有记录，将其封装为Emp对象的list集合；
  - 查询总记录数；

ZJH_12138

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库-mysql

一、数据库的介绍1.1 目前市场上的数据库种类MYSQL：开源免费的数据库，小型的数据库，已经被Oracle收购了。MYSQL6.x版本也开费；Orcale：收费的大型数据库，Orcale公司的产品。Oracle收购SUN公司，收购了MYSQL。DB2：IBM公司的数据库产品，收费的。常应用与银行系统中。SQLServer：微软公司收费的中型的数据库。C#、.net等语言常使用。SyBase：已经逐渐退出历史舞台。SQLite：嵌入式的小型数据库，应用于手机端。常用的数据库：MYSQL、.
复制链接

扫一扫