华清远见-重庆中心-数据库知识点梳理

数据库分类

关系型数据库 ： MySQL , sql server , oracle , db2 ...        数据以二维表的方式存储        实体之间的关联关系        支持SQL(结构化查询语言)语言 （近期学习的重点） 非关系型数据库： redis (后期要使用) ， MongoDB ， Hbase .....        数据按不同的数据类型存储。        不支持SQL

ER模型

ER模型，就是将数据库中的table之间的关系，以图形的方式展示出来。 ER模型，就是创建数据库表的依据。 举行代表实体 ， 菱形代表关系 ， 椭圆代表实体的属性。 实体之间的关系：        1对1 ： 一个国家有一个总统        1对多：一个班级有多个学生        多对多： 课程和学生

mysql命令行操作

连接到mysql服务器：

mysql -u root - p

使用mysql的命令

-- 是mysql的注释
show databases; -- 显示所有的数据库
use hello; -- 选择要使用的数据库的名字
show tables; -- 显示hello数据库中的所有的表
desc stu; -- 查看stu表结构
-- Field , 字段
-- Type , 字段的类型
-- key , 键
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
insert into stu(name ,age , gender) values('邹麟',18,'女'); -- 插入数据的sql语
句
-- 插入成功之后，数据库的提示信息： Query OK, 1 row affected (0.01 sec)
select * from stu; -- 查询数据
+------+------+--------+
| name | age | gender |
+------+------+--------+
| 洪涵 | 21 | 男 |
| 林涛 | 20 | 男 |
| 刘思 | 20 | 女 |
| 邹麟 | 18 | 女 |
+------+------+--------+
update stu set age = 22 where name='洪涵'; -- 修改数据
-- 修改成功之后的提示信息： Query OK, 1 row affected (0.01 sec)
delete from stu ; -- 删除数据
-- 删除成功之后的提示信息： Query OK, 4 rows affected (0.01 sec)
drop table stu; -- 删除名字叫stu的表
drop database hello; -- 删除名字叫hello的数据库
create database cms; -- 创建名字教cms的数据库
create table test(id int , name varchar(6) , pwd varchar(8)); -- 创建一个
名字叫test的表
exit -- 退出mysql

SQL：结构化查询语言

sql语言中，字符串可以使用单引号或者双引号 ， 保证单双引号匹配 注释 ： -- 注释内容 ，/* */ sql语言中，关键字不区分大小写 ， 字段名不区分大小写 ， 数据内容有大小写之分 比如： select 同SELECT , NAME 字段 ， 也可以是name , 数据 “ALICE”和‘alice’就有区别 sql的使用： 1. 创建数据库： create database 数据库名字； create database 数据库名字 default character set utf8； -- 指定了数据库的编码 删除数据库 ： drop database 数据库名字； 2. 创建表： create table 表名（字段1 字段类型 约束条件 ， 字段2 ....） 如果字段名，表名和关键字重复了，那么就在字段名或表名上添加引号。 尽量避免和关键字重名 。 一般表可以教前缀 ： cms_stu --表示学生表, s_name -- 表示 学生表的字段名 约束条件： not null ,表示字段值不能是null . int类型的数据，可以设置为自动增加 ： auto_increment 主键约束： primary key，主键的特征就是非空且不重复 ， 一般用于实现表中的每行数 据不重复。

mysql中常见的数据类型

约束

INSERT——添加

给所有字段赋值

表名之后，没有指定字段名 ，说明添加的所有字段的值，values中按表结构的顺序，填写上 每个字段对应的值。 遇到自增字段，不能省略不写，在values中对应的顺序位置用0 ， null 或default让其自动填 充 遇到有默认值的字段，不能省略不写， 在values中对应的顺序位置用default占位 遇到允许为空的字段， 不能省略不写，在values中对应的顺序位置用null占位

insert into 表名 values(字段值1 ， 字段值2 ......)
-- insert into 表名(sc_code ,sc_name ,sc_birth, sc_address ,sc_total
,sc_area) values(字段值1 ， 字段值2 ......) , 繁琐
insert into school values('1002' , '重庆大学' , '1947-2-15' , '沙坪坝' , 12200
, 1230.56)

给指定字段赋值

表名之后，指定字段名，values中只需要设置表名之后指定的字段的value值就可以了（一 一 对应）。 没有默认值的非空字段，必须要设置数据值 表名之后的字段顺序必须和values的值的顺序一致

insert into 表名(字段a , 字段b , 字段c) values(字段a值 ， 字段值b值 ,字段c值)
insert into school(sc_code , sc_name , sc_total) values('1003' , '重庆交通大
学' , 8200)

批量添加

用insert into 语句，一次添加多个记录 一次如果需要添加多条数据，使用批量添加，效率更高 批量添加，也可以在表名之后指定需要添加的字段

isnert into 表名 values(值1,值2......) ,(值1， 值2......) ，........
insert into school values('1004' , '重庆工商大学' , '1987-2-15' , '五公里'
, 7200 , 730.56),
('1005' , '重庆文理学院' , '1997-2-15' , '永川' , 6200 , 930.56),
('1006' , '重庆人文科技学院' , '2005-2-15' , '合川' , 4200 , 1030.56)

UPDATE——修改

update 表名 set 字段名 = 新值
-- update school set sc_total=100 ,不合理，这表示把所有记录中的sc_total都修改为
100了。

根据条件修改数据（where）

update 表名 set 字段名 = 新值 where 条件
update 表名 set 字段名=新值 , 字段名2=新值2 ........ where 条件

update 表名 set 字段名 = 新值 where 字段名=字段值
update school set sc_total=6100 where sc_code=1003 -- 修改编号是1003的学
校的人数
update school set sc_total=5100 , sc_area = 4100.5 where sc_name='重庆文
理学院' -- 修改重庆文理学院的面积和总人数

-- 1. > , < , >= , <= 表示范围 ， and , or 将多个条件进行关联
update school set sc_total=6100 where sc_code < 1004
-- 2. 使用between ... and ,设置要给区间之内
update school set sc_total=6100 where sc_code between 1004 and 1006 -
- [1004 , 1006]
-- 3. 使用!= 或<>不等于
update school set sc_total=6100 where sc_code <>1004 -- 不是1004的就修改

-- 1 .在某个集合中 - in
update 表名 set 字段名 = 新值 where 字段 in(值1 ， 值2 ，.....)
update school set sc_total=6100 where sc_code in(1003 ,1005 , 1007) --
修改的是符合sc_code等于1003，或1005 或1007
update school set sc_total=6100 where sc_code=1003 or sc_code=1005 or
sc_code=1007 -- 繁琐
-- 2. 不在某个集合中 - not in
update 表名 set 字段名 = 新值 where 字段 in(值1 ， 值2 ，.....)
update school set sc_total=6100 where sc_code not in(1003 ,1005 ,
1007) -- 不修改1003，1005，1007 ，其他都修改

模糊匹配

-- 1. '-'代表一个任意字符（1个）
-- 2. '%'代表任意个字符（1个或多个）
-- 以什么开始
sc_name like '重庆%'
-- 以什么结尾
sc_name like "%大学"
-- 包含什么
sc_name like "%交通%"
-- 只能包含4个字符
sc_name like '__大学' -- 两个字符 + 大学， 一共四个字符
-- 倒数第二个是大
sc_name like '%大_'
update school set sc_total = sc_total+1000 where sc_name like '%大_' --
匹配成功的行，人数增加1000

DELETE——删除

根据主键删除

delete from 表名 where 主键字段 = 值
delete from book_info where book_id = 1006 -- 删除主键值为1006的书

SELECT——查询

全表查询

select * from 表名 -- * 表示的是所有字段名
select * from school -- 查询school表的所有数据
select 列名1 ， 列名2 .... from 表名 -- 直接把所有列名写出
select sc_code ,sc_name , sc_birth ,sc_address ,sc_total, sc_area from
school --- 等同于select * from school

查询指定的列

select 列名1 ， 列名2 from 表名 -- 只查找出指定的列
select sc_name ,sc_address from school --- 只显示学校的名字和学校的地址

条件查询

like

-- 模糊查询是针对字符类型(char ,varchar)
-- % , 任意个字符串
-- _ , 一个字符
select * from 表名 where 字段名 like 字符串
-- 练习
-- 1. 查询地址是五公里的学校
select * from school where sc_address like '五公里'
-- 2. 查询学校名字包含“交通”的学校信息
select * from school where sc_name like '%交通%'
-- 3. 查询学校地址以’川‘结尾的学校信息,并且是两个字的
select * from school where sc_address like '_川'
-- 4. 查询所有"四川"开始的学校
select * from school where sc_name like "四川%"

数据的条件查询

-- > , < . >= , <= , = , != , <>
select * from 表名 where 字段名[> , < . >= , <= , = , != , <>]数据值
--练习： 查询出所有占地面积大于等于1000的
select * from school where sc_area >= 1000
-- 查询出编号是1004的学校
select * from school where sc_code=1004
-- 查询出学生人数小于5000的学校
select * from school where sc_total<5000
-- 查询出不是1004的学校信息
select * from school where sc_code<>1004

去掉重复的行 - distinct

select distinct * from 表名 where 条件
-- 查询出学校的地址列，去掉重复的数据
select sc_address from school -- 出现行数据有重复
select distinct sc_address from school -- 去掉重复的行

分页查询

-- limit begin , rows 【数据表的第一行为0 ， 第二行为1 ...... 】
select * from 表名 [where 条件] limit 起始行 ,行数 --- 通过limit关键字，限
定查询的时候从那行开始查， 一共查多少行。
select * from school limit 3 , 4
select * from school where sc_area > 800 limit 2, 3
-- 分页查询的需求： 把shcool表中的数据（8条），按每页显示3行的方式查询出来。
select * from school limit 0 , 3 -- 第一页
select * from school limit 3 , 3 -- 第二页
select * from school limit 6 , 3 -- 最后一页没有3条，那么有多少，查询多少即可。
-- 怎么计算总页码 ， 每页的起始值怎么算 ， 规定了每页显示的行数
select count(*) from school -- 查询整个表的总行数 , 把查询结果取出来保存在变量
中，作为总行数
int maxrow = 8 ;
int rows = 3 ;
int page = ? ;// 页码
if(maxrow % rows == 0 ){
page = maxrow / rows ;
}else {
page = maxrow/ rows + 1;
}
int currentPage = 2 ;// 当前页
int begin = (currentPage - 1) * rows ; // begin = 0 , 3 , 6

查询排序——order by

-- 排序规则： asc 升序 ， desc 降序 排序是针对某个字段进行排序
-- order by 字段名1 asc/desc ， 字段名2 asc/desc ,如果是你希望升序排序， asc可
以省略。
select * from 表名 (where 条件) order by 字段名 排序方案 ,......
select * from school order by sc_code desc --按sc_code 降序排序

分组查询——group by

-- group by 字段名 , 根据某个字段分组
-- where 条件 group by 字段名 ， 先根据where条件，查询到结果，然后按指定字段分组
-- [where 条件] group by 字段名 having 条件 ， 按指定字段分组之后，然后可以使用
having子句对分组的结果进行筛选
select 分组的字段 ， 统计函数 from 表名 [where 条件] group by 字段名 [having
条件]
-- 把学校按地址分组
select sc_address from school group by sc_address -- 把school表中的全部数据，按
地址进行分组
-- 把学校按地址分组之后，可以统计出每组的学校个数
select sc_address , count(sc_address) as 学校个数 from school group by
sc_address order by 学校个数 desc
-- 把学校按地址分组之后，可以统计出每组的学校个数 ,值显示学校个数多余1的地址
select sc_address , count(sc_address) as 学校个数 from school
group by sc_address
having 学校个数 > 1
order by 学校个数 desc

多表查询

将两张表中的数据两两组合，得到的结果就是交叉连接的结果，也称为笛卡尔积

select * from 表1 ， 表2
select * from 表1 cross join 表2 -- cross
select * from 表1 inner join 表2 -- inner
-- 两张表数据组成一张表，其中有很多无效数据
SELECT * from t_stu , t_class
SELECT * from t_stu CROSS JOIN t_class
SELECT * from t_stu INNER JOIN t_class

内连接

通过主表主键字段和从表的外键字段进行等值判断 主表和从表字段名一样，使用”表名.字段名“进行区分，可以给表名取别名 如果用where进行内连接，后续还有其他条件的查询，使用and 拼接后续条件 如果用inner join ... on ,后续有其他条件，使用where 进行条件拼接 内连接只显示两张表中有关联的数据

select * from 表1 ， 表2 where 表1.字段 = 表2.字段 -- 判断相等的字段就是有外键约
束的字段
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 -- on后面的相等字段的
判断，也是有外键约束的字段
-- 查询学生的学生信息和班级信息
select * from t_stu , t_class where t_stu.c_id = t_class.c_id -- *代表的是两
个表的所有字段
select * from t_stu s , t_class c where s.c_id = c.c_id -- 给表取别名
select * from t_stu s , t_class c where s.c_id = c.c_id and s.s_money >
5000 -- 使用and 拼接查询条件
SELECT * FROM t_stu s INNER JOIN t_class c ON s.c_id = c.c_id where
s.s_money > 5000 -- 加上关键字where之后，拼接条件

左连接

保证左表数据完全显示 ，关联右表中的数据，右表中没有和左表关联的数据，右表用null表示

select * from 左表 left join 右表 on 左表.字段 = 右表.字段 -- 左连接
-- 查询学生和班级信息
select * from t_class c left join t_stu s on c.c_id = s.c_id -- 左表内容全
显示
select * from t_stu s left join t_class c on c.c_id = s.c_id

右连接

保证右表的数据全部显示，关联左表中的数据，没有关联的数据就用null表示

select * from 左表 right join 右表 on 左表.字段 = 右表.字段 -- 右连接
-- 查询学生和班级信息
select * from t_class c right join t_stu s on c.c_id = s.c_id -- 右表内容全
显示
select * from t_stu s right join t_class c on c.c_id = s.c_id -- 右表内容全
显示

子查询

子查询是在查询语句中，嵌套了另一个查询语句， 也可以嵌套查询 。

-- 查询出班级名字为”软件技术11班“的所有学员
select * from t_stu where c_id = ( select c_id from t_class where c_name='软
件技术11班')
-- 查询出没有学生的班级
select * from t_class where c_id not in (select c_id from t_stu) -- 查询出学生
的班级c_id ,然后不在这些c_id中的班级就没有学生
或者
select c.c_id ,c_name from t_class c left join t_stu s on c.c_id = s.c_id
where s.s_id is null

如果把子查询的结果作为一张临时表，必须要把子查询的结果，设置一个别名。

-- 查询出所有男同学，然后在找工资大于5000的
-- 先用一个查询， 查询出所有男生，然后在这个结果中，查询出工资大于等于5000的
select * from
(select * from t_stu where s_sex='男' ) as stuboy
where s_money >= 5000

JDBC - JAVA Database Connection

jdbc: java 连接数据库 ， 进行数据库中数据的操作： insert , update , delete ,select. jdk中提供了一套操作数据库的接口： java.sql.* , 每个一数据库厂商，实现这些对应的接口 ， 把这些 实现类打包为.jar文件。 比如： 使用的mysql服务器，下载mysql对应的 jar包， 使用的oracle服务器，下载对应的oracle的 jar包。 jar包： 以.jar为后缀的文件 ， 称为java的归档文件，保存的是java的字节码文件(.class) , 在我们的 java工程中，导入.jar文件，就可以使用这些.class

核心接口      Connection , 用于设置连接数据库的地址，账号，密码      PreparedStatement , 用于预处理，执行sql语句      ResultSet , 用于接收查询后的数据

查询

public static void main(String[] args) throws ClassNotFoundException,
SQLException {
// 1. 加载mysql的驱动
Class.forName("com.mysql.cj.jdbc.Driver"); // ** mysql server 是
8.0 ， mysql的jar包也是8.0， 这里写的是-com.mysql.cj.jdbc.Driver
// ** mysql server 是
5.0 ， mysql的jar包也是5.0， 这里写的是-com.mysql.jdbc.Driver
// 2. url ： 连接mysql数据库的协议
// **** "jdbc:mysql://ip:port/数据库?serverTimezone=Asia/Shanghai"
String url = "jdbc:mysql://localhost:3306/cms?
serverTimezone=Asia/Shanghai";
// 3. mysql server的账号
String user = "root";
String pwd ="123456";
// 4. 获取连接
Connection con = DriverManager.getConnection(url ,user ,pwd);
System.out.println("连接：" + con);
// 5. 查询book_info表
String sql = "select * from book_info"; // -- sql语句
PreparedStatement ps = con.prepareStatement(sql) ;// -- 预处理sql
ResultSet rs = ps.executeQuery() ;// -- 执行查询
// -- 循环获取结果 : 先用rs.next判断有没有下一行，有就获取这一行的数据。
while (rs.next()){
// rs.getInt(字段名) 或者 rs.getInt(columnIndex) , columnIndex 从1
开始， 表示第一列 ,,,,,
// System.out.println(rs.getInt("book_id") + "--" +
rs.getString("book_name"));
// System.out.println(rs.getInt(0)); //Column Index out of range,
0 < 1.
//System.out.println(rs.getInt(1));
int bid = rs.getInt(1);
String bname = rs.getString(2);
String bauthor = rs.getString(3);
double bprice = rs.getDouble(4);
Date bdate = rs.getDate(5);
int bnum = rs.getInt(6);
int tid = rs.getInt(7);
// 把数据库中的一行数据，转换为了一个对象。
BookInfo bookInfo = new BookInfo(bid , bname ,
bauthor ,bprice , bdate , bnum , tid);
System.out.println(bookInfo);
}
// 6. 释放资源
rs.close();
ps.close();
con.close();
}

增加

public static void main(String[] args) throws ClassNotFoundException,
SQLException {
// 1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. url地址 , 用户名， 密码
String url = "jdbc:mysql://localhost:3306/cms?
serverTimezone=Asia/Shanghai";
String user = "root";
String pwd = "123456";
// 3. 获取连接
Connection con = DriverManager.getConnection(url , user ,pwd);
// 4. 增加(insert ....)
String sql = "insert into book_info values(0,'一个人的朝圣','alice' ,
20.5, '2000-12-12',125,1)";
PreparedStatement ps = con.prepareStatement(sql);
int i = ps.executeUpdate();// i -- 返回值就是行数
if(i > 0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
// 5. 释放资源
ps.close();
con.close();
}

删除

public static void main(String[] args) throws ClassNotFoundException,
SQLException {
// 1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. url地址 , 用户名， 密码
String url = "jdbc:mysql://localhost:3306/cms?
serverTimezone=Asia/Shanghai";
String user = "root";
String pwd = "123456";
// 3. 获取连接
Connection con = DriverManager.getConnection(url , user ,pwd);
// 4. 增加(insert ....)
String sql = "delete from book_info where book_id=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, 1005);
int i = ps.executeUpdate();
if(i > 0 ){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
// 5. 释放资源
ps.close();
con.close();
}

修改

public static void main(String[] args) throws ClassNotFoundException,
SQLException {
// 1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. url地址 , 用户名， 密码
String url = "jdbc:mysql://localhost:3306/cms?
serverTimezone=Asia/Shanghai";
String user = "root";
String pwd = "123456";
// 3. 获取连接
Connection con = DriverManager.getConnection(url , user ,pwd);
// 4. 修改(update ....)
// -- 把id为1004 的书的价格修改为56.5 ,数量修改为130 ，作者修改为tom.
String sql = "update book_info set book_price=? , book_num=? ,
book_author=? where book_id=? "; // 4个问号
PreparedStatement ps = con.prepareStatement(sql);
// ** 赋值函数的个数和问号的个数及其类型要一一对应
ps.setDouble(1, 56.5); // 表示给第1个问号赋值
ps.setInt(2, 130);// 表示给第2个问号赋值
ps.setString(3,"tom");// 表示给第3个问号赋值
ps.setInt(4,1004);// 表示给第4个问号赋值
int i = ps.executeUpdate();// i -- 返回值就是行数
if(i > 0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
// 5. 释放资源
ps.close();
con.close();
}